12c: How to Restore/Recover a Small Table in a Large Database
As a DBA, you will receive requests from developers or users, indicating that they deleted some data in a small table in a large database a few hours prior. They will probably want you to recover the data as soon as possible, and it will likely be a critical production database. Flashback will not be enabled, and the recycle bin will have been purged. Restoring a full database using RMAN might take you over 10 hours, and you will need a spare server with big storage. Looks like it’s going to be a difficult and time consuming task for you.
In Oracle Database 12c, there is a method available which allows us to recover the table more efficiently, and at a lower cost. The method is to create a second database (often called a stub database) using the backup of the first database. In this situation, we restore the SYSTEM, SYSAUX, and UNDO tablespaces and the the individual tablespaces that contain the data that we want to restore. After the restore is complete, we alter any tablespaces that we did not restore offline. We then apply the archived redo logs to the point in time that we want to restore the table to. Having restored the database to the appropriate point in time, we then use Oracle Data Pump to export the objects, and then you import them into the original database, again using Oracle Data Pump. Oracle Database 12c introduces new functionality in RMAN that supports point-in-time restore of individual database tables and individual table partitions.
Here is an example of when I tested this new feature:
1. The database TEST has 9 tablespaces and a schema called Howie. I created a table with 19377 records called TEST1 which is in the tablespace DATA_HOWIE.
SQL> select * from v$instance; INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO CON_ID INSTANCE_MO EDITION FAMILY --------------- ---------------- ---------------------------------------------------------------- ----------------- --------- ------------ --- ---------- ------- --------------- ---------- --- ----------------- ------------------ --------- --- ---------- ----------- ------- -------------------------------------------------------------------------------- 1 TEST 12cServer1 12.1.0.1.0 17-AUG-14 OPEN NO 1 STARTED ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMALNO 0 REGULAR EE SQL> select tablespace_name from dba_tablespaces order by tablespace_name; TABLESPACE_NAME ------------------------------ DATA_HOWIE DATA_TB1 DATA_TB2 DATA_TB3 SYSAUX SYSTEM TEMP UNDOTBS1 USERS 9 rows selected. SQL> conn howie Enter password: Connected. SQL> create table test1 as select * from dba_objects; Table created. SQL> select count(*) from test1; COUNT(*) ---------- 19377 SQL> select table_name,tablespace_name from user_tables where table_name='TEST1'; TABLE_NAME TABLESPACE_NAME -------------------------------------------------------------------------------------------------------------------------------- ------------------------------ TEST1 DATA_HOWIE
2. The database is in archivelog mode, and I took a full backup of the database.
[oracle@12cServer1 RMAN]$ rman target /
Recovery Manager: Release 12.1.0.1.0 - Production on Sun Aug 17 20:16:17 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to target database: TEST (DBID=2146502230)
RMAN> run
{
allocate channel d1 type disk format '/u01/app/oracle/RMAN/rmn_%d_t%t_p%p';
backup
incremental level 0
tag backup_level0
filesperset 1
(database)
plus archivelog ;
release channel d1;
}2> 3> 4> 5> 6> 7> 8> 9> 10> 11>
3. The data in the table howie.test1 has been deleted.
SQL> select sysdate,current_scn from v$database; SYSDATE CURRENT_SCN ------------------- ----------- 08/17/2014 21:01:15 435599 SQL> delete test1; 19377 rows deleted. SQL> commit; Commit complete.
4. I ran following scripts to recover the data to an alternative table howie.test1_temp to the point in time “08/17/2014 21:01:15”
[oracle@12cServer1 RMAN]$ rman target /
Recovery Manager: Release 12.1.0.1.0 - Production on Sun Aug 17 21:01:35 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to target database: TEST (DBID=2146502230)
RMAN> recover table howie.test1
until time "to_date('08/17/2014 21:01:15','mm/dd/yyyy hh24:mi:ss')"
auxiliary destination '/u01/app/oracle/aux'
remap table howie.test1:test1_temp;2> 3> 4>
5. The scripts above will take care of everything and you will see the data has been restored to howie.test1_temp
SQL> select count(*) from TEST1_TEMP; COUNT(*) ---------- 19377 SQL> select count(*) from TEST1; COUNT(*) ---------- 0
Let’s take a look at the log of RMAN recovery and find out how it works.
1. Creation of the auxiliary instance
Creating automatic instance, with SID='ktDA' initialization parameters used for automatic instance: db_name=TEST db_unique_name=ktDA_pitr_TEST compatible=12.1.0.0.0 db_block_size=8192 db_files=200 sga_target=1G processes=80 diagnostic_dest=/u01/app/oracle db_create_file_dest=/u01/app/oracle/aux log_archive_dest_1='location=/u01/app/oracle/aux' #No auxiliary parameter file used
2. Restore of the control file for the auxiliary instance
contents of Memory Script:
{
# set requested point in time
set until time "to_date('08/17/2014 21:01:15','mm/dd/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('08/17/2014 21:01:15','mm/dd/yyyy hh24:mi:ss')";
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 3 online";
sql clone "alter database datafile 2 online";
# recover and open database read only
recover clone database tablespace "SYSTEM", "UNDOTBS1", "SYSAUX";
sql clone 'alter database open read only';
}
contents of Memory Script:
{
# set requested point in time
set until time "to_date('08/17/2014 21:01:15','mm/dd/yyyy hh24:mi:ss')";
# online the datafiles restored or switched
sql clone "alter database datafile 8 online";
# recover and open resetlogs
recover clone database tablespace "DATA_HOWIE", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
4. Export of tables from the auxiliary instance via Oracle Data Pump
Performing export of tables... EXPDP> Starting "SYS"."TSPITR_EXP_ktDA_BAkw": EXPDP> Estimate in progress using BLOCKS method... EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA EXPDP> Total estimation using BLOCKS method: 3 MB EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER EXPDP> . . exported "HOWIE"."TEST1" 1.922 MB 19377 rows EXPDP> Master table "SYS"."TSPITR_EXP_ktDA_BAkw" successfully loaded/unloaded EXPDP> ****************************************************************************** EXPDP> Dump file set for SYS.TSPITR_EXP_ktDA_BAkw is: EXPDP> /u01/app/oracle/aux/tspitr_ktDA_70244.dmp EXPDP> Job "SYS"."TSPITR_EXP_ktDA_BAkw" successfully completed at Sun Aug 17 21:03:53 2014 elapsed 0 00:00:14 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 Script
Oracle instance shut down
Performing import of tables...
IMPDP> Master table "SYS"."TSPITR_IMP_ktDA_lube" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_ktDA_lube":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "HOWIE"."TEST1_TEMP" 1.922 MB 19377 rows
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
IMPDP> Job "SYS"."TSPITR_IMP_ktDA_lube" successfully completed at Sun Aug 17 21:04:19 2014 elapsed 0 00:00:19
Import completed
6. Clean-up of the auxiliary instance
Removing automatic instance Automatic instance removed auxiliary instance file /u01/app/oracle/aux/TEST/datafile/o1_mf_temp_9z2yqst6_.tmp deleted auxiliary instance file /u01/app/oracle/aux/KTDA_PITR_TEST/onlinelog/o1_mf_3_9z2yrkqm_.log deleted auxiliary instance file /u01/app/oracle/aux/KTDA_PITR_TEST/onlinelog/o1_mf_2_9z2yrj35_.log deleted auxiliary instance file /u01/app/oracle/aux/KTDA_PITR_TEST/onlinelog/o1_mf_1_9z2yrh2r_.log deleted auxiliary instance file /u01/app/oracle/aux/KTDA_PITR_TEST/datafile/o1_mf_data_how_9z2yrcnq_.dbf deleted auxiliary instance file /u01/app/oracle/aux/TEST/datafile/o1_mf_sysaux_9z2yptms_.dbf deleted auxiliary instance file /u01/app/oracle/aux/TEST/datafile/o1_mf_undotbs1_9z2yq9of_.dbf deleted auxiliary instance file /u01/app/oracle/aux/TEST/datafile/o1_mf_system_9z2yp0mk_.dbf deleted auxiliary instance file /u01/app/oracle/aux/TEST/controlfile/o1_mf_9z2yos1l_.ctl deleted auxiliary instance file tspitr_ktDA_70244.dmp deleted Finished recover at 17-AUG-14
