From 12c, RMAN enables us to recover tables and table partitions at a point-in-time without affecting other objects in the database.
- RECOVER TABLE is the command used to recover table and table partitions from an RMAN Backup.
- The Recovery happens at an Auxiliary location. Uses an automatically created and maintained Auxiliary instance.
- User need to specify the auxiliary database location using AUXILIARY DESTINATION clause in the RECOVERY command.
- Uses Database Backups and Archived Redo Logs for Recovery.
- The database remains online. During Recovery other objects are not disturbed.
Table Recovery is useful in the below scenarios.,
- When the object has Logical corruption or dropped.
- When there is no sufficient undo to perform Flashback table.
- If you need to recover a small number of tables to a point-in-time.
- When DDL operation modified the structure and you want to recover the data (Flashback cannot rewind the structural changes).
Pre-Requisites for Table Recovery using RMAN are,
- Database must be in ARCHIVELOG mode and read-write mode.
- At least one full backup is available along with archived logs.
- If present, any dependent objects to include in recovery.
- COMPATIBLE parameter must be set to 11.1.0 or higher to recover table partition.
- Enough disk space is available on the database server for auxiliary instance
Table Recovery – Steps
- Determine the backup which has the tables or table partitions that needs to recover to specified point-in-time.
- Create auxiliary database and recover the tables or table partitions until specified point-in-time.
- Import the dump into target database.
- Rename the recovered tables or table partitions in the target database.
- Take a data dump export with recovered tables or table partitions.
- Clean-up the Auxiliary Instance.
The below tasks are performed in this example.,
- Create a new table.
- Insert rows in it.
- Take a backup.
- Drop the table.
- recover it using RMAN – Table Recovery.
1. Create a table.
SQL> create table TEST (emp_no number(1) primary key, emp_name varchar2(10));Table created.
2. Insert some rows in it.
SQL> insert into TEST values(1, ‘Veera’);
1 row created.SQL> insert into TEST values(2, ‘Basha’);
1 row created.SQL> insert into TEST values(3, ‘Hari’);
1 row created.SQL> commit;
Commit complete.
1 row created.SQL> insert into TEST values(2, ‘Basha’);
1 row created.SQL> insert into TEST values(3, ‘Hari’);
1 row created.SQL> commit;
Commit complete.
SQL> select * from test; EMP_NO EMP_NAME
————– ———-
1 Veera
2 Basha
3 Hari
————– ———-
1 Veera
2 Basha
3 Hari
3. Now I am performing a full database backup using RMAN.,
RMAN> backup as backupsetincremental level 0 database plus archivelog;
4. Check the current SCN / Timestamp.,
SQL> select to_char(sysdate, ‘DD-MON-YYYY HH24:MI:SS am’),current_scnfrom v$database;SYSDATE CURRENT_SCN
———————————– ————————
24-FEB-2015 00:51:12 am 2017619
———————————– ————————
24-FEB-2015 00:51:12 am 2017619
5. Now I am dropping the table.,
SQL> drop table TEST;Table dropped.
6. Check if the table is dropped.,
SQL> descTEST
ERROR:
ORA-04043: object TEST does not exist
ERROR:
ORA-04043: object TEST does not exist
7. Now connect to the RMAN.,
[oracle@Oracle12c ~]$ rmantarget /Recovery Manager: Release 12.1.0.1.0 -Production on Tue Feb 24 00:52:03 2015
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.connected to target database: RMANDB (DBID=3659344729)
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.connected to target database: RMANDB (DBID=3659344729)
8. For recovering the table use the below command.,
RMAN> RECOVER TABLE SCOTT.”TEST” UNTIL TIME “to_date(’24-FEB-2015 00:51:12′,’DD-MON-YYYY:HH24:MI:SS’)”
AUXILIARY DESTINATION ‘/backup’
DATAPUMP DESTINATION ‘/backup’ DUMP FILE ‘testdump.dmp’;
AUXILIARY DESTINATION ‘/backup’
DATAPUMP DESTINATION ‘/backup’ DUMP FILE ‘testdump.dmp’;
AUXILIARY DESTINATION – Specify the destination where the Auxiliary instance should be created.
DATAPUMP DESTINATION – Specify the destination where the EXPDP dump should be created.
There are SIX steps internally involved in the table recovery. So in this recovery example, I have segregated the recovery in to steps for your clear understandings.
1. It Creates an Auxiliary Instance. Here the Instance name which is automatically with SID=’axEy’.
initialization parameters used for automatic instance:
db_name=RMANDB
db_unique_name=axEy_pitr_RMANDB
compatible=12.1.0.0.0
db_block_size=8192
db_block_size=8192db_files=200
sga_target=1G
sga_target=1G
processes=80
diagnostic_dest=/oracle
db_create_file_dest=/backup
diagnostic_dest=/oracledb_create_file_dest=/backuplog_archive_dest_1=’location=/backup’
#No auxiliary parameter file used
db_name=RMANDB
db_unique_name=axEy_pitr_RMANDB
compatible=12.1.0.0.0
db_block_size=8192
db_block_size=8192db_files=200
sga_target=1G
sga_target=1G
processes=80
diagnostic_dest=/oracle
db_create_file_dest=/backup
diagnostic_dest=/oracledb_create_file_dest=/backuplog_archive_dest_1=’location=/backup’
#No auxiliary parameter file used
2. Restoration of the Controlfile for the Auxiliary Instance.
contents of Memory Script:
{
# set requested point in time
set until time “to_date(’24-FEB-2015 00:51:12′,’DD-MON-YYYY:HH24:MI:SS’)”;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone ‘alter database mount clone database’;
# archive current online log
sql ‘alter system archive log current’;
}
{
# set requested point in time
set until time “to_date(’24-FEB-2015 00:51:12′,’DD-MON-YYYY:HH24:MI:SS’)”;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone ‘alter database mount clone database’;
# archive current online log
sql ‘alter system archive log current’;
}
3. A list of Datafiles that will be restored, followed by their restore and recovery in the auxiliary instance.
contents of Memory Script:
{
# set requested point in time
set until time “to_date(’24-FEB-2015 00:51:12′,’DD-MON-YYYY:HH24:MI:SS’)”;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 3 to new;
set newname for clone tempfile 1 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 4, 3;
switch clone datafile all;
}contents of Memory Script:
{
# set requested point in time
set until time “to_date(’24-FEB-2015 00:51:12′,’DD-MON-YYYY:HH24:MI:SS’)”;
# online the datafiles restored or switched
sql clone “alter database datafile 6 online”;
# recover and open resetlogs
recover clone database tablespace “USERS”, “SYSTEM”, “UNDOTBS1”, “SYSAUX” delete archivelog;
alter clone database open resetlogs;
}
{
# set requested point in time
set until time “to_date(’24-FEB-2015 00:51:12′,’DD-MON-YYYY:HH24:MI:SS’)”;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 3 to new;
set newname for clone tempfile 1 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 4, 3;
switch clone datafile all;
}contents of Memory Script:
{
# set requested point in time
set until time “to_date(’24-FEB-2015 00:51:12′,’DD-MON-YYYY:HH24:MI:SS’)”;
# online the datafiles restored or switched
sql clone “alter database datafile 6 online”;
# recover and open resetlogs
recover clone database tablespace “USERS”, “SYSTEM”, “UNDOTBS1”, “SYSAUX” delete archivelog;
alter clone database open resetlogs;
}
4. Export of tables from the auxiliary instance via Oracle Data Pump.
contents of Memory Script:
{
# create directory for datapump import
sql “create or replace directory TSPITR_DIROBJ_DPDIR as ”
/backup””;
# create directory for datapump export
sql clone “create or replace directory TSPITR_DIROBJ_DPDIR as ”
/backup””;
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ”/backup’‘
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ”/backup’‘
Performing export of tables…
EXPDP> Starting “SYS”.”TSPITR_EXP_axEy_ugii”:
EXPDP> Estimate in progress using BLOCKS method…
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Total estimation using BLOCKS method: 64 KB
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
EXPDP> Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
EXPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
EXPDP> . . exported “SCOTT”.”TEST” 5.539 KB 3 rows
EXPDP> Master table “SYS”.”TSPITR_EXP_axEy_ugii” successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_axEy_ugii is:
EXPDP> /backup/testdump.dmp
EXPDP> Job “SYS”.”TSPITR_EXP_axEy_ugii” successfully completed at Tue Feb 24 00:54:34 2015 elapsed 0 00:00:31
Export completed
{
# create directory for datapump import
sql “create or replace directory TSPITR_DIROBJ_DPDIR as ”
/backup””;
# create directory for datapump export
sql clone “create or replace directory TSPITR_DIROBJ_DPDIR as ”
/backup””;
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ”/backup’‘
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ”/backup’‘
Performing export of tables…
EXPDP> Starting “SYS”.”TSPITR_EXP_axEy_ugii”:
EXPDP> Estimate in progress using BLOCKS method…
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Total estimation using BLOCKS method: 64 KB
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
EXPDP> Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
EXPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
EXPDP> . . exported “SCOTT”.”TEST” 5.539 KB 3 rows
EXPDP> Master table “SYS”.”TSPITR_EXP_axEy_ugii” successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_axEy_ugii is:
EXPDP> /backup/testdump.dmp
EXPDP> Job “SYS”.”TSPITR_EXP_axEy_ugii” successfully completed at Tue Feb 24 00:54:34 2015 elapsed 0 00:00:31
Export completed
5. Import of tables, constraints, indexes, and other dependent objects into the target database from the Data Pump export file.
contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory ScriptOracle instance shut downPerforming import of tables…
IMPDP> Master table “SYS”.”TSPITR_IMP_axEy_Ewrg” successfully loaded/unloaded
IMPDP> Starting “SYS”.”TSPITR_IMP_axEy_Ewrg”:
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported “SCOTT”.”TEST” 5.539 KB 3 rows
IMPDP> Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
IMPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
IMPDP> Job “SYS”.”TSPITR_IMP_axEy_Ewrg” successfully completed at Tue Feb 24 00:54:42 2015 elapsed 0 00:00:02
Import completed
{
# shutdown clone before import
shutdown clone abort
}
executing Memory ScriptOracle instance shut downPerforming import of tables…
IMPDP> Master table “SYS”.”TSPITR_IMP_axEy_Ewrg” successfully loaded/unloaded
IMPDP> Starting “SYS”.”TSPITR_IMP_axEy_Ewrg”:
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported “SCOTT”.”TEST” 5.539 KB 3 rows
IMPDP> Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
IMPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
IMPDP> Job “SYS”.”TSPITR_IMP_axEy_Ewrg” successfully completed at Tue Feb 24 00:54:42 2015 elapsed 0 00:00:02
Import completed
6. Clean-up of the Auxiliary Instance.
Removing automatic instance
Automatic instance removed
auxiliary instance file /backup/RMANDB/datafile/o1_mf_temp_bgpzkdmn_.tmp deleted
auxiliary instance file /backup/AXEY_PITR_RMANDB/onlinelog/o1_mf_3_bgpzl21n_.log deleted
auxiliary instance file /backup/AXEY_PITR_RMANDB/onlinelog/o1_mf_2_bgpzl0q8_.log deleted
auxiliary instance file /backup/AXEY_PITR_RMANDB/onlinelog/o1_mf_1_bgpzkzlr_.log deleted
auxiliary instance file /backup/AXEY_PITR_RMANDB/datafile/o1_mf_users_bgpzky0x_.dbf deleted
auxiliary instance file /backup/RMANDB/datafile/o1_mf_sysaux_bgpzhy1m_.dbf deleted
auxiliary instance file /backup/RMANDB/datafile/o1_mf_undotbs1_bgpzhy13_.dbf deleted
auxiliary instance file /backup/RMANDB/datafile/o1_mf_system_bgpzhy16_.dbf deleted
auxiliary instance file /backup/RMANDB/controlfile/o1_mf_bgpzhq7j_.ctl deleted
auxiliary instance file testdump.dmp deleted
Finished recover at 24-FEB-15
Automatic instance removed
auxiliary instance file /backup/RMANDB/datafile/o1_mf_temp_bgpzkdmn_.tmp deleted
auxiliary instance file /backup/AXEY_PITR_RMANDB/onlinelog/o1_mf_3_bgpzl21n_.log deleted
auxiliary instance file /backup/AXEY_PITR_RMANDB/onlinelog/o1_mf_2_bgpzl0q8_.log deleted
auxiliary instance file /backup/AXEY_PITR_RMANDB/onlinelog/o1_mf_1_bgpzkzlr_.log deleted
auxiliary instance file /backup/AXEY_PITR_RMANDB/datafile/o1_mf_users_bgpzky0x_.dbf deleted
auxiliary instance file /backup/RMANDB/datafile/o1_mf_sysaux_bgpzhy1m_.dbf deleted
auxiliary instance file /backup/RMANDB/datafile/o1_mf_undotbs1_bgpzhy13_.dbf deleted
auxiliary instance file /backup/RMANDB/datafile/o1_mf_system_bgpzhy16_.dbf deleted
auxiliary instance file /backup/RMANDB/controlfile/o1_mf_bgpzhq7j_.ctl deleted
auxiliary instance file testdump.dmp deleted
Finished recover at 24-FEB-15
Note: Obviously views created on the table should be recreated.
We can clearly see that, a normal export dump and import dump is done by the RMAN internally here. The only new things are, it creates a new instance, restores the files required and exports the dump from the restored file and again imports it to the database. Finally the created instance vanishes automatically by RMAN.
Similar to this, we can also recover a particular partition of a table.
Loading ...
Total Users : 1823750