Oracle Database 12c has new enhancements and additions in Recovery Manager (RMAN). The Recovery Manager continues to enhance and extend the reliability, efficiency, and availability of Oracle Database Backup and Recovery. In this article series, I will be explaining the new features and how they help the Oracle community.
See new features around SYSBACKUP, pluggable databases, and the SQL interface covered in Part 1 – Oracle Database 12c – RMAN New Features: Part 1
See new features around backups, file recovery, snapshots, and duplication covered in Part 2 – Oracle Database 12c – RMAN New Features: Part 2
In this article I will cover:
- Cross-Platform Backup and Restore Improvements
- Recovering Tables and Table Partitions using RMAN Backups
- Unified auditing and RMAN
Cross-Platform Backup and Restore Improvements
Cross-platform transportable tablespace and database were introduced in Oracle 10g. In this method the user must use EXPORT or DATAPUMP to export metadata. The tablespace should be in read-only mode during the process and only image copies are used. This method is more suitable for smaller or low transaction rate applications.
In 18.104.22.168, the incremental backup method was introduced which reduces the tablespace read-only downtime. As users can reduce down time using this feature it is suitable for large databases. But this feature is only supported when destination database is Exadata.
In Oracle 12c, you can transport data across platforms using either full or incremental backups, using image copies or backup sets and added platform support for incremental backup. To perform cross-platform backups using backup sets, the destination database must be Oracle 12c or later. These features simplify the platform migration and minimize read-only down time on the source database using incremental based approach. By using this improvement users can reduce downtime by 8 times versus traditional migration approaches.
If the user performs the conversion at source, CONVERT TABLESPACE command should be used. If the user performs conversion at destination, CONVERT DATAFILE command should be used.
Cross platform transportable tablespace
You can use RMAN to transport tablespace across platforms with the same endian or different endian formats. If transporting tablespace between different endian formats then the user must use CONVERT command to perform conversion. If transporting tablespace between same endian formats then the CONVERT command isn’t needed.
Transportable tablespace is useful under following situations:
- When migrating tablespace across the platforms
- When moving data from large data warehouse to data marts on small servers
- Sharing read-only tablespace across heterogeneous cluster where hosts have same endian format
User can use below query to get the platform name of the connected database
SQL> SELECT PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM WHERE PLATFORM_ID = (SELECT PLATFORM_ID FROM V$DATABASE);
Before you convert the tablespace make sure you run
DBMS_TTS.TRANSPORT_SET_CHECK procedure to check if tablespace is self-contained or not.
For ex: – EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK(‘USER_TBS’, TRUE);
After executing these commands, you can check for violations in TRANSPORT_SET_VIOLATIONS view. User must resolve before proceeding with conversion.
Cross-platform transportable database
You can use RMAN to transport entire database across platforms with same endian format only. When transporting database the user doesn’t need to perform EXPORT/IMPORT as SYSTEM tablespace is part of the database that being copied. You can use CONVERT DATABASE command to convert and automatically transport the database to its destination. You can convert the data files either on source or destination platforms.
When you run the CONVERT DATABASE on the destination platforms, it does not convert the format of the files, rather it generates the two scripts to perform conversion manually.
CONVERT SCRIPT – This script used to convert data files copies in batch mode
TRANSPORT SCRIPT – This script contains SQL Statements to create new databases on the destination platform
Transportable Database is useful under following situations:
- When Evaluation migration path for new platforms
- When you need database on less expensive servers that use different platform
- Distribute data from one source system to multiple targets with different platforms
Before converting the database make sure you run
DBMS_TDB.CHECK_DB function. This function checks for incompatible endian formats, active transactions or incorrect compatibility settings between source and destination.
In Oracle 12c, Oracle 10.2.x and 11g database backups can be restored and recovered cross-platform to Oracle 12c.
Note that cross-platform transportable database is not the same thing as transportable tablespace. For transportable tablespace please refer below documentation link http://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces013.htm#ADMIN11394.
Recovering Tables and Table Partitions using RMAN Backups
RMAN enables you to recover tables and table partitions at a point-in-time without affecting the other objects in the database. Use RECOVER TABLE command to recover tables or table partitions from an RMAN backup.
This feature is useful in the following scenarios:
- When the object has Logical corruption or dropped
- When there is no sufficient undo to perform Flashback table
- When DDL operation modified the structure and you want to recover the data (Flashback cannot rewind the structural changes)
- If you need to recover a small number of tables to a point-in-time
Before you prepare to recover the tables and table partitions, make sure you verify the pre-requisites and determine the point-in-time. RMAN enables you to specify the point-in-time either using SCN, Time or sequence number. In order to perform table/ table Partition recovery these conditions must be met:
- Database must be in ARCHIVELOG mode and read-write mode
- At least one full backup is available along with archived logs
- Enough disk space is available on the database server for auxiliary instance
- If present, any dependent objects to include in recovery
- COMPATIBLE parameter must be set to 11.1.0 or higher to recover table partition
RMAN enables recovery of selected tables without affecting remaining database objects. During the recovery process RMAN creates an auxiliary database, which is used to recover the tables or table partitions to a specified point-in-time. User need to specify the auxiliary database location using AUXILIARY DESTINATION clause in the RECOVERY command or SET NEWNAME command. Please find the steps performed by RMAN during the recovery process:
- Determine the backup which has the tables or table partitions that needs to recover to specified point-in-time
- Create auxiliary database and recovery the tables or table partitions until specified point-in-time
- Take a data dump export with recovered tables or table partitions
- Import the dump into target database
- Rename the recovered tables or table partitions in the target database
Please find an example to recovery TBL1 table.
RECOVER TABLE TESTUSER.TBL1 UNTIL SCN 384840289 AUXILIARY DESTINATION '/tmp/TESTDB/recover' DATAPUMP DESTINATION '/tmp/TESTDB/dumpfiles' DUMP FILE 'testdump.dat';
If source table exists then user can specify NOTABLEIMPORT or REMAP TABLE. Also user can use UNTIL TIME or UNTIL SEQUENCE clause to specify point-in-time recovery.
Please find an example to recovery TBL1 table as TBL1_REC to the state that it was 2 days before the current date.
RECOVER TABLE TESTUSER.TBL1 UNTIL TIME ‘SYSDATE-2’ AUXILIARY DESTINATION '/tmp/TESTDB/recover' REMAP TABLE 'TESTUSER'.'TBL1':'TBL1_REC';
There are some limitations recovering tables and table partitions:
- We cannot recover table and table partitions belonging to SYS schema
- We cannot recover table or table partitions from SYSAUX,SYSTEM tablespace Tables
- We cannot recover tables with named NOT NULL constraint using REMAP option.
- We cannot recover Table/Table partitions ON STANDBY database
- We cannot recovery table partitions if version is prior Oracle Database 11g R1
Note that there are other methods available to recover tables to a point-in-time such as Oracle Flashback and Tablespace Point-in-Time Recovery.
For more information about Flashback and Database Point-in-Time Recovery, see: http://docs.oracle.com/database/121/BRADV/rcmflash.htm#g1016666.
For more about RMAN Tablespace point-in-time Recovery (TSPITR), take a look at: http://docs.oracle.com/database/121/BRADV/rcmtspit.htm#CIHGGAFH
Unified auditing and RMAN
In Unified auditing you can consolidate all audit records into single audit trail. User can view the audit trail by querying UNIFIED_AUDIT_TRAIL data dictionary view for single-instance and GV$UNIFIED_AUDIT_TRAIL for Oracle RAC. In order to query UNIFIED_AUDIT_TRAIL view user must have AUDIT_ADMIN OR audit viewer ROLE.
The UNIFIED_AUDIT_TRAIL view has fields begin with RMAN_ and these fields automatically record RMAN related events.
Please find the Oracle recovery Manager specific columns in UNIFIED_AUDIT_TRAIL data dictionary view.
RMAN_SESSION_RECID – It contains the RMAN session identifier
RMAN_SESSION_STAMP – It contains the timestamp for the RMAN session.
RMAN_OPERATION – It contains the operation executed by RMAN job
RMAN_OBJECT_TYPE – It contains the type of object involved in a RMAN session.
RMAN_DEVICE_TYPE – it contains device type associated with RMAN session. This can be SBT or DISK.
For more information on RMAN unified auditing: https://docs.oracle.com/database/121/DBSEG/audit_config.htm#DBSEG401
For Oracle’s documentatino on how to migrate your database to unified auditing: https://docs.oracle.com/database/121/UPGRD/afterup.htm#UPGRD52810
For more on Oracle Database Backup & Recovery documentation see: http://docs.oracle.com/database/121/BRADV/toc.htm
Oracle Database 12c offers new enhancements and additions in Recovery Manager (RMAN). The features listed in this article will help you transport data across platforms and reduce downtime by 8x versus tradition migration approach, recover table and table partitions to point-in-time without affecting other objects in the database, and audit RMAN-related events using unified auditing. Take advantage of these new features for efficient backup and recovery.