Next Five New Features in Oracle Database 12c for DBAs : Part II


Oracle Database 12c

This post is series of Oracle Database 12c new features, check out our previous post on Five New Features in Oracle Database 12c for DBAs : Part I here

The Oracle 12C means different things to different people. It all depends on which areas you are looking at, as there are improvements in many areas. Summarized below is the list of Top 5 Features of Oracle 12C . I have summarized below, the top 5 which I found interesting in the area of Database Administration, RMAN, High Availability and Performance Tuning. 

Next Five New Features in Oracle DBA 12C:

1. Temporary Undo

Each Oracle database contains a set of system related tablespaces, such as, SYSTEM, SYSAUX, UNDO & TEMP, and each are used for different purposes within the Oracle database. Pre Oracle 12c R1, undo records generated by the temporary tables used to be stored in undo tablespace, much similar to a general/persistent table undo records. However, with the temporary undo feature in 12c R1, the temporary undo records can now be stored in a temporary table instead of stored in undo tablespace. The prime benefits of temporary undo includes: reduction in undo tablespace and less redo data generation as the information won’t be logged in redo logs. You have the flexibility to enable the temporary undo option either at session level or database level.

Enabling temporary undo

To be able to use the new feature, the following needs to be set:

  • Compatibility parameter must be set to 12.0.0 or higher
  • Enable TEMP_UNDO_ENABLED initialization parameter
  • Since the temporary undo records now stored in a temp tablespace, you need to create the temporary tablespace with sufficient space
  • For session level, you can use: ALTER SESSION SET TEMP_UNDO_ENABLE=TRUE;

Query temporary undo information

The dictionary views listed below are used to view/query the information/statistics about the temporary undo data:

  • V$TEMPUNDOSTAT
  • DBA_HIST_UNDOSTAT
  • V$UNDOSTAT

To disable the feature, you simply need to set the following:

SQL> ALTER SYSTEM|SESSION SET TEMP_UNDO_ENABLED=FALSE;

2. Backup specific user privilege

In 11g R2, SYSASM privilege was introduced to perform ASM specific operations. Similarly, backup and recovery tasks specific privilege SYSBACKUP has been introduced in 12c to execute backup and recovery commands in Recovery Manager (RMAN). Therefore, you can create a local user in the database and grant the SYSBACKUP privilege to perform any backup and recovery related tasks in RMAN without being granting the SYSDBA privilege.

$ ./rman target "username/password as SYSBACKUP"

3. How to execute SQL statement in RMAN

In 12c, you can now execute any SQL and PL/SQL commands in RMAN without the need of a SQL prefix: you can execute any SQL and PLS/SQL commands directly from RMAN. How you can execute SQL statements in RMAN:

RMAN> SELECT username,machine FROM v$session;
	RMAN> ALTER TABLESPACE users ADD DATAFILE SIZE 121m;

4. Table or partition recovery in RMAN

Oracle database backups are mainly categorized into two types: logical and physical. Each backup type has its own pros and cons. In previous editions, it was not feasible to restore a table or partition using existing physical backups. In order to restore a particular object, you must have logical backup. With 12c R1, you can recover a particular table or partition to a point-in-time or SCN from RMAN backups in the event of a table drop or truncate.

When a table or partition recovery is initiated via RMAN, the following action is performed:

  • Required backup sets are identified to recover the table/partition
  • An auxiliary database will be configured to a point-in-time temporarily in the process of recovering the table/partition
  • Required table/partitions will be then exported to a dumpfile using the data pumps
  • Optionally, you can import the table/partitions in the source database
  • Rename option while recovery

An example of a table point-in-time recovery via RMAN (ensure you already have a full database backup from earlier):

RMAN> connect target "username/password as SYSBACKUP";
	RMAN> RECOVER TABLE username.tablename UNTIL TIME 'TIMESTAMP…'
		AUXILIARY DESTINATION '/u01/tablerecovery'
		DATAPUMP DESTINATION '/u01/dpump'
		DUMP FILE 'tablename.dmp'
		NOTABLEIMPORT    -- this option avoids importing the table automatically.
REMAP TABLE 'username.tablename': 'username.new_table_name';    -- can rename table with this option.

Important notes:

  • Ensure sufficient free space available under /u01 filesystem for auxiliary database and also to keep the data pump file
  • A full database backup must be exists, or at least the SYSTEM related tablespaces

The following limitations/restrictions are applied on table/partition recovery in RMAN:

  • SYS user table/partition can’t be recovered
  • Tables/partitions stored under SYSAUX and SYSTEM tablespaces can’t be recovered
  • Recovery of a table is not possible when REMAP option used to recovery a table that contains NOT NULL constraints

5. Restricting PGA size

Pre Oracle 12c R1, there was no option to limit and control the PGA size. Although, you set a certain size toPGA_AGGREGATE_TARGET initialization parameter, Oracle could increase/reduce the size of the PGA dynamically based on the workload and requirements. In 12c, you can set a hard limit on PGA by enabling the automatic PGA management, which requires PGA_AGGREGATE_LIMIT parameter settings. Therefore, you can now set the hard limit on PGA by setting the new parameter to avoid excessive PGA usage.

SQL> ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=2G;
SQL> ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=0; --disables the hard limit

Important notes:

When the current PGA limits exceeds, Oracle will automatically terminates/abort the session/process that holds the most untenable PGA memory.

In part III, you will learn more on new changes on Cluster, ASM, RMAN and database administration areas.

References:

This post is from our Oracle Database 12c Training where We cover Architecture, Installation, File System, Backup and Recovery and difference in 12c from previous version with Hands-On,  If interested in learning Oracle Database 12c you can Register here for Oracle Database 12c Training 

Oracle DBA 12c Training starting from 26th March
We are launching this with 50% Discount. Apply code: DBAOFF for 50% Discount

Click Here to Register for DBA 12c Training

Series Navigation<< Five New Features in Oracle Database 12c for DBAs : Part1

About the Author SeemaYadav

Oracle Consultant at K21 Technologies - Oracle Gold Partner specialising in Design, Implement, and Support.

Leave a Comment:

Not found