Oracle finally announced the much anticipated first ever cloud database – Oracle Database 12c. Since then, there have been so many articles, papers , presentation by several Oracle professions/experts in the Oracle community. This paper will be focused on some of the Oracle database 12c new features, yet very useful, introduced in the area of Recovery Manager (RMAN).
As part of this article, the new features outlined below will be covered thoroughly with some working examples:
Pre 12c, there was no direct method available to recovery a particular table or a table partition using the existing RMAN physical backups. The table should be recovered either using the existing logical backup that has the required table or restore/recover the complete database on the same or new host, extract the required table and import the it in the source database.
Recovery of one or more tables or partitions through an RMAN becomes a reality in 12c. I must say, it was quite a long pending ask from the Oracle community and finally it’s there in 12c. With the new RMAN 'RECOVER TABLE' command in 12c, DBA can perform point-in-time recovery of one or more tables or table partitions. The supported options are, UNTIL TIME, UNTIL SCN and UNTIL SEQUENCE RMAN uses the similar technique of tablespace recovery to perform a table or table partition recovery operations. During the course of recovery, RMAN does the following sequence of action:
The following example covers the instructions to recovery EMP table exist in the SCOTT schema that is either dropped or truncated unintentionally at around 2PM on 9th July 2013:
RMAN> RECOVER TABLE SCOTT.EMP
UNTIL TIME "TO_CHAR('07/09/2013 04:00:00','mm/dd/yyyy hh24:mi:ss')"
AUXILIARY DESTINATION '/u01/tmp/recemp'
DATAPUMP DESTINATION '/u01/DPUMP'
DUMP FILE 'recoveremp.dmp'
REMAP TABLE 'SCOTT'.'EMP':'EMP2';
The preceding example does the following:
In order to perform point-in-time recovery for a table or partition, the following criteria must be met:
Table or partition level recovery is a very useful option in the event of when the table/partition is dropped, truncated unintentionally or corrupted.
Unlike the previous releases which needs SQL prefix to execute SQL statements through an RMAN, in 12c, you can execute most of the SQL statements and PL/SQL procedures straight in RMAN without mentioning the keyword SQL. For example, through RMAN, you can DESCRIBE command to list table structure, can add a datafile to an existing tablespace etc.
The following examples demonstrate adding a datafile to an existing tablespace, how to list table structure and how to use SELECT statement :
RMAN> ALTER TABLESPACE data ADD DATAFILE size 100;
RMAN> DESCE dual;
RMAN> SELECT sysdate FROM dual;
This feature gives the flexibility to execute the SQL statements freely in an RMAN prompt.
Multi-sectional backup functionality was introduced in 11g to break up a large sized data file into multiple sections with the specified size to improve backup and restore operation of large size data files. The functionality extended further in 12c to supports image copies and incremental backups along with the full database backupsets. The added functionality requires COMPATIBLE database initialization parameter to be set to 12.0 or higher.
The below example explains the syntax to use SECTION SIZE clause at various levels:
RMAN> backup tablespace data SECTION SIZE 1g; RMAN> backup as copy SECTION SIZE 1g database; RMAN> backup increment level 1 SECTION SIZE 500M datafile 10;
You can query the MULTI_SECTION column in V$BACKUP_SET dynamic view to determine whether the feature is in use or not.
The new RMAN FROM SERVICE clause in 12c let you copy the data files between primary and standby databases connecting through the network. This feature provides the flexibility to restore full database, specific data file, controlfile, spfile etc from and to primary and standby databases. This feature will be very useful to fill-in the huge gap between primary and standby databases. In fact, this will simplify the roll-forward procedure required to in-sync the standby with the primary database by taking appropriate actions automatically.
Additionally, you can easily restore and recover a data file from standby to primary in the event primary loss a data file. The following examples simulate the following scenario:
In the first example, data file 9 will be restored from standby database to primary database.
In the second example, standby database roll-forward procedure will be kicked off by taking the incremental backup starting from the current SCN of standby on the primary database, these backup sets will be then moved/copied to the standby site over the network and then the backups will be applied automatically to in-sync the standby database.
When data files are copied across the sites through the network, you can also apply the encryption to encrypt the backups, use the SECTION SIZE split a big data files into pieces and also compression.
The ACTIVE DUPLICATE DATABASE option in 11g clone the source database by coping the full image of the data files to the auxiliary instance location through the network. This procedure doesn’t not required any pre backup existence to clone the database.
However, in Oracle 12c, when ACTIVE DUPLICATE DATABASE is initiated, instead of sending the full image copy of the data files, RMAN first takes the backup of data files into backupsets, and these backups are transmit to the auxiliary location and will be restored/recovered subsequently. This functionality will surely reduce the overall impact on the source instance and also help fastening the cloning procedure. You have the flexibility to use backup compression, encryption option with this feature.
RMAN> DUPLICATE TARGET DATABASE to mydb FROM ACTIVE DATABASE SECTION SIZE 500M USING COMPRESSED BACKUPSETS;
In the previous releases, the cloned database will be opened automatically upon the process completion. The additional NOOPEN clause in 12c, will prevent opening database immediately after the cloning process completion. Therefore, the database will remain in MOUNT state after the procedure and you will have to manually open the database.
This will be a good option to use in the following scenario:
Here is the example how to use NOOPEN clause:
RMAN> DUPLICATE TARGET DATABASE to mydb FROM ACTIVE DATABASE SECTION SIZE 500M USING COMPRESSED BACKUPSETS NOOPEN;
Written by Syed Jaffar Hussain
Wonderful article for Oracle database ...............
Related links vox.ulule.com/a-utility-to-repairrecover-not-responding-oracle-d-5960
Great and helpful articles for administrator or users who always deal with Oracle database. Here is another helpful blog - oracle.filerepairtool.net/.../tips-recover-corrupted-oracle-database-file,hope this may help them too.