Tables point in time recovery 12c
It is now possible to recover a particular table to
a point in time.
When to use table point in time recovery?
Tablespace point in time is not recommend for the
following scenario
Small number of tables to be recovered.
When tablespace is not self contained.
Flashback is not possible
a) When the
drop table is purged from recyclebin.
b) Flashback
table is not possible (Undo not available /
table structural change).
Steps
1)
Connect the target instance.
2)
Recover table command
3)
RMAN creates auxiliary instance in the auxiliary
destination provided.
4)
RMAN recovers the table or table partition to the specified
time.
5)
Exports the table to the auxiliary dump destination.
6)
Imports the table in to the target database if not
NOTABLE IMPORT specified.
7)
Optionally remaps the table to different name.
Eg.
SQL> select count(*) from manzoor.emp;
COUNT(*)
----------
1000
SQL> select current_scn from V$database;
CURRENT_SCN
-----------
6971250
SQL> delete from manzoor.emp where rownum
<500;
499 rows deleted.
SQL> commit;
Commit complete.
[oracle@rhel11gr2rac1 ~]$ mkdir -p /backup/aux_dest
[oracle@rhel11gr2rac1 ~]$ mkdir -p
/backup/aux_dest/dump_dest
The below will create a auxiliary destination and
startup an auxiliary destination, restores the system/sysaux/undo & the
tablespace in which the said objects resides, recovers it until scn, takes
export dump in the below said directory, import into the table with remap
tables clause in the main database.
RMAN> RMAN> run
{
recover table
manzoor.emp
until scn
6971250
auxiliary
destination '/backup/aux_dest'
datapump destination '/backup/aux_dest/dump_dest'
dump file
'manzoor_emp_table.dmp'
remap table
manzoor.emp:emp_reco;
}
2> 3> 4> 5> 6> 7> 8> 9>
Starting recover at 08-SEP-15
using target database control file instead of
recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=57 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=66 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=67 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=61 device type=DISK
RMAN-05026: WARNING: presuming following set of
tablespaces applies to specified Point-in-Time
List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1
Creating automatic instance, with SID='ciah'
initialization parameters used for automatic
instance:
db_name=PURCHASE
db_unique_name=ciah_pitr_PURCHASE
compatible=12.1.0.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/oracle/product
_system_trig_enabled=FALSE
sga_target=1024M
processes=200
db_create_file_dest=/backup/aux_dest
log_archive_dest_1='location=/backup/aux_dest'
#No auxiliary parameter file used
starting up automatic instance PURCHASE
Oracle instance started
Total System Global Area 1073741824 bytes
Fixed Size 2932632 bytes
Variable Size 281018472 bytes
Database Buffers 784334848 bytes
Redo Buffers 5455872 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until scn
6971250;
# 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';
}
executing Memory Script
executing command: SET until clause
Starting restore at 08-SEP-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=24 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=25 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=26 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=27 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set
restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece
/oracle/purchase_bkp/purchase_bkp_4nqgkhhm_1_1.bkp
channel ORA_AUX_DISK_1: piece
handle=/oracle/purchase_bkp/purchase_bkp_4nqgkhhm_1_1.bkp
tag=TAG20150908T085620
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed
time: 00:00:03
output file
name=/backup/aux_dest/PURCHASE/controlfile/o1_mf_bywj44lq_.ctl
Finished restore at 08-SEP-15
sql statement: alter database mount clone database
sql statement: alter system archive log current
contents of Memory Script:
{
# set requested point in time
set until scn
6971250;
# 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;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to
/backup/aux_dest/PURCHASE/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 08-SEP-15
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
channel ORA_AUX_DISK_1: starting datafile backup set
restore
channel ORA_AUX_DISK_1: specifying datafile(s) to
restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to
/backup/aux_dest/PURCHASE/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece
/oracle/purchase_bkp/purchase_bkp_4mqgkhhm_1_1.bkp
channel ORA_AUX_DISK_2: starting datafile backup set
restore
channel ORA_AUX_DISK_2: specifying datafile(s) to
restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00001 to
/backup/aux_dest/PURCHASE/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_2: reading from backup piece
/oracle/purchase_bkp/purchase_bkp_4lqgkhhm_1_1.bkp
channel ORA_AUX_DISK_3: starting datafile backup set
restore
channel ORA_AUX_DISK_3: specifying datafile(s) to
restore from backup set
channel ORA_AUX_DISK_3: restoring datafile 00004 to
/backup/aux_dest/PURCHASE/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_3: reading from backup piece
/oracle/purchase_bkp/purchase_bkp_4kqgkhhl_1_1.bkp
channel ORA_AUX_DISK_3: piece
handle=/oracle/purchase_bkp/purchase_bkp_4kqgkhhl_1_1.bkp
tag=TAG20150908T085620
channel ORA_AUX_DISK_3: restored backup piece 1
channel ORA_AUX_DISK_3: restore complete, elapsed
time: 00:01:17
channel ORA_AUX_DISK_1: piece
handle=/oracle/purchase_bkp/purchase_bkp_4mqgkhhm_1_1.bkp
tag=TAG20150908T085620
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed
time: 00:04:39
channel ORA_AUX_DISK_2: piece handle=/oracle/purchase_bkp/purchase_bkp_4lqgkhhm_1_1.bkp
tag=TAG20150908T085620
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed
time: 00:04:58
Finished restore at 08-SEP-15
datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=889869754 file
name=/backup/aux_dest/PURCHASE/datafile/o1_mf_system_bywj4h8s_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=889869754 file
name=/backup/aux_dest/PURCHASE/datafile/o1_mf_undotbs1_bywj4j2g_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=889869754 file
name=/backup/aux_dest/PURCHASE/datafile/o1_mf_sysaux_bywj4hs2_.dbf
contents of Memory Script:
{
# set requested point in time
set until scn
6971250;
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 4 online";
sql clone "alter database datafile 3 online";
# recover and open database read only
recover clone database tablespace "SYSTEM", "UNDOTBS1",
"SYSAUX";
sql clone 'alter database open read only';
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 1 online
sql statement: alter database datafile 4 online
sql statement: alter database datafile 3 online
Starting recover at 08-SEP-15
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
starting media recovery
archived log for thread 1 with sequence 242 is already
on disk as file
/oracle/product/fast_recovery_area/PURCHASE/archivelog/2015_09_08/o1_mf_1_242_bywgrc1k_.arc
channel ORA_AUX_DISK_1: starting archived log
restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=240
channel ORA_AUX_DISK_1: reading from backup piece
/oracle/purchase_bkp/purchase_bkp_4qqgki95_1_1.bkp
channel ORA_AUX_DISK_2: starting archived log
restore to default destination
channel ORA_AUX_DISK_2: restoring archived log
archived log thread=1 sequence=241
channel ORA_AUX_DISK_2: reading from backup piece
/oracle/purchase_bkp/purchase_bkp_4sqgki96_1_1.bkp
channel ORA_AUX_DISK_2: piece
handle=/oracle/purchase_bkp/purchase_bkp_4sqgki96_1_1.bkp
tag=TAG20150908T090852
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed
time: 00:00:01
channel ORA_AUX_DISK_1: piece
handle=/oracle/purchase_bkp/purchase_bkp_4qqgki95_1_1.bkp
tag=TAG20150908T090852
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed
time: 00:00:03
archived log file
name=/backup/aux_dest/1_240_888427429.dbf thread=1 sequence=240
archived log file
name=/backup/aux_dest/1_241_888427429.dbf thread=1 sequence=241
archived log file
name=/oracle/product/fast_recovery_area/PURCHASE/archivelog/2015_09_08/o1_mf_1_242_bywgrc1k_.arc
thread=1 sequence=242
media recovery complete, elapsed time: 00:00:05
Finished recover at 08-SEP-15
sql statement: alter database open read only
contents of Memory Script:
{
sql clone
"create spfile from memory";
shutdown
clone immediate;
startup
clone nomount;
sql clone
"alter system set control_files =
''/backup/aux_dest/PURCHASE/controlfile/o1_mf_bywj44lq_.ctl'' comment=
''RMAN set''
scope=spfile";
shutdown
clone immediate;
startup
clone nomount;
# mount database
sql clone 'alter database mount clone database';
}
executing Memory Script
sql statement: create spfile from memory
database closed
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1073741824 bytes
Fixed Size 2932632 bytes
Variable Size 285212776 bytes
Database Buffers 780140544 bytes
Redo Buffers 5455872 bytes
sql statement: alter system set control_files =
''/backup/aux_dest/PURCHASE/controlfile/o1_mf_bywj44lq_.ctl'' comment=
''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1073741824 bytes
Fixed Size 2932632 bytes
Variable Size 285212776 bytes
Database Buffers 780140544 bytes
Redo Buffers 5455872 bytes
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until scn
6971250;
# set destinations for recovery set and auxiliary
set datafiles
set newname for datafile 2 to new;
# restore the tablespaces in the recovery set and
the auxiliary set
restore clone datafile 2;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 08-SEP-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=21 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=24 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=25 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=26 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set
restore
channel ORA_AUX_DISK_1: specifying datafile(s) to
restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00002 to
/backup/aux_dest/CIAH_PITR_PURCHASE/datafile/o1_mf_active_d_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece
/oracle/purchase_bkp/purchase_bkp_4lqgkhhm_1_1.bkp
channel ORA_AUX_DISK_1: piece
handle=/oracle/purchase_bkp/purchase_bkp_4lqgkhhm_1_1.bkp
tag=TAG20150908T085620
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed
time: 00:00:16
Finished restore at 08-SEP-15
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=889869857 file
name=/backup/aux_dest/CIAH_PITR_PURCHASE/datafile/o1_mf_active_d_bywjjkt5_.dbf
contents of Memory Script:
{
# set requested point in time
set until scn
6971250;
# online the datafiles restored or switched
sql clone "alter database datafile 2 online";
# recover and open resetlogs
recover clone database tablespace "ACTIVE_DATA", "SYSTEM",
"UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 2 online
Starting recover at 08-SEP-15
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
starting media recovery
archived log for thread 1 with sequence 242 is
already on disk as file
/oracle/product/fast_recovery_area/PURCHASE/archivelog/2015_09_08/o1_mf_1_242_bywgrc1k_.arc
channel ORA_AUX_DISK_1: starting archived log
restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=240
channel ORA_AUX_DISK_1: reading from backup piece
/oracle/purchase_bkp/purchase_bkp_4qqgki95_1_1.bkp
channel ORA_AUX_DISK_2: starting archived log
restore to default destination
channel ORA_AUX_DISK_2: restoring archived log
archived log thread=1 sequence=241
channel ORA_AUX_DISK_2: reading from backup piece
/oracle/purchase_bkp/purchase_bkp_4sqgki96_1_1.bkp
channel ORA_AUX_DISK_1: piece
handle=/oracle/purchase_bkp/purchase_bkp_4qqgki95_1_1.bkp
tag=TAG20150908T090852
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed
time: 00:00:01
archived log file
name=/backup/aux_dest/1_240_888427429.dbf thread=1 sequence=240
channel clone_default: deleting archived log(s)
archived log file
name=/backup/aux_dest/1_240_888427429.dbf RECID=194 STAMP=889869861
channel ORA_AUX_DISK_2: piece
handle=/oracle/purchase_bkp/purchase_bkp_4sqgki96_1_1.bkp
tag=TAG20150908T090852
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed
time: 00:00:02
archived log file
name=/backup/aux_dest/1_241_888427429.dbf thread=1 sequence=241
channel clone_default: deleting archived log(s)
archived log file name=/backup/aux_dest/1_241_888427429.dbf
RECID=193 STAMP=889869860
archived log file
name=/oracle/product/fast_recovery_area/PURCHASE/archivelog/2015_09_08/o1_mf_1_242_bywgrc1k_.arc
thread=1 sequence=242
media recovery complete, elapsed time: 00:00:00
Finished recover at 08-SEP-15
database opened
contents of Memory Script:
{
# create directory for datapump import
sql "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/backup/aux_dest/dump_dest''";
# create directory for datapump export
sql clone "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/backup/aux_dest/dump_dest''";
}
executing Memory Script
sql statement: create or replace directory
TSPITR_DIROBJ_DPDIR as ''/backup/aux_dest/dump_dest''
sql statement: create or replace directory
TSPITR_DIROBJ_DPDIR as ''/backup/aux_dest/dump_dest''
Performing export of tables...
EXPDP>
Starting "SYS"."TSPITR_EXP_ciah_Bmrm":
EXPDP>
Estimate in progress using BLOCKS method...
EXPDP>
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP>
Total estimation using BLOCKS method: 128 KB
EXPDP>
Processing object type TABLE_EXPORT/TABLE/TABLE
EXPDP>
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
EXPDP>
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP>
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
EXPDP> .
. exported "MANZOOR"."EMP" 71.03 KB 1000 rows
EXPDP>
Master table "SYS"."TSPITR_EXP_ciah_Bmrm" successfully
loaded/unloaded
EXPDP>
******************************************************************************
EXPDP>
Dump file set for SYS.TSPITR_EXP_ciah_Bmrm is:
EXPDP>
/backup/aux_dest/dump_dest/manzoor_emp_table.dmp
EXPDP>
Job "SYS"."TSPITR_EXP_ciah_Bmrm" successfully completed at
Tue Sep 8 10:07:52 2015 elapsed 0 00:02:08
Export completed
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_ciah_ygCq" successfully
loaded/unloaded
IMPDP>
Starting "SYS"."TSPITR_IMP_ciah_ygCq":
IMPDP>
Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP>
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> .
. imported "MANZOOR"."EMP_RECO" 71.03 KB 1000 rows
IMPDP>
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
IMPDP>
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
IMPDP>
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
IMPDP>
Job "SYS"."TSPITR_IMP_ciah_ygCq" successfully completed at
Tue Sep 8 10:09:52 2015 elapsed 0 00:01:33
Import completed
Removing automatic instance
Automatic instance removed
auxiliary instance file
/backup/aux_dest/PURCHASE/datafile/o1_mf_temp_bywjg9b7_.tmp deleted
auxiliary instance file
/backup/aux_dest/CIAH_PITR_PURCHASE/onlinelog/o1_mf_3_bywjk935_.log deleted
auxiliary instance file
/backup/aux_dest/CIAH_PITR_PURCHASE/onlinelog/o1_mf_2_bywjk7x9_.log deleted
auxiliary instance file
/backup/aux_dest/CIAH_PITR_PURCHASE/onlinelog/o1_mf_1_bywjk6m1_.log deleted
auxiliary instance file
/backup/aux_dest/CIAH_PITR_PURCHASE/datafile/o1_mf_active_d_bywjjkt5_.dbf
deleted
auxiliary instance file
/backup/aux_dest/PURCHASE/datafile/o1_mf_sysaux_bywj4hs2_.dbf deleted
auxiliary instance file
/backup/aux_dest/PURCHASE/datafile/o1_mf_undotbs1_bywj4j2g_.dbf deleted
auxiliary instance file
/backup/aux_dest/PURCHASE/datafile/o1_mf_system_bywj4h8s_.dbf deleted
auxiliary instance file
/backup/aux_dest/PURCHASE/controlfile/o1_mf_bywj44lq_.ctl deleted
auxiliary instance file manzoor_emp_table.dmp
deleted
Finished recover at 08-SEP-15
RMAN> select count(*) from manzoor.emp_reco;
COUNT(*)
----------
1000
RMAN> drop table manzoor.emp;
Statement processed
RMAN> alter table manzoor.emp_reco rename to emp;
Statement processed
RMAN> select count(*) from manzoor.emp;
COUNT(*)
----------
1000
No comments:
Post a Comment