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:
Query temporary undo information
The dictionary views listed below are used to view/query the information/statistics about the temporary undo data:
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:
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.
The following limitations/restrictions are applied on table/partition recovery in RMAN:
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
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.
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 Consultant at K21 Technologies - Oracle Gold Partner specialising in Design, Implement, and Support.