TO FIND THE CURRENT SCN
========================
select current_scn from v$database;
TO FIND THE SCN FROM TIMESTAMP
===============================
select timestamp_to_scn(to_timestamp(’05-09-2018 12:46:21′,’dd-mm-yyyy hh24:mi:ss’)) scn from dual;
TO FIND THE TIMESTAMP FROM SCN
===============================
select scn_to_timestamp(1601485) from dual;
FOR EXAMPLE TAKE SCOTT.EMP TABLE
1.Note the current SCN =1601485
2.Make a update to emp table and note the SCN
SCN= 1601543
3.Make a delete to emp table and note the SCN
SCN= 1601583
4.drop the emp table and note the SCN
SCN= 1601870
We can remap the table, remap the tablespace if needed during recover.
Note:
1.If you are recovering table without remaping make sure the table not exist.
2.There should be space for the auxiliary instance.From 12.2 it will check for the space availability before the recover operation.
To Recover the emp table as emp_old into TEST tablespace.
rman target /
RMAN> RECOVER TABLE SCOTT.EMP
UNTIL SCN 1601485
AUXILIARY DESTINATION ‘/u01/aux’
REMAP TABLE ‘SCOTT’.’EMP’:’EMP_OLD’
REMAP TABLESPACE ‘USERS’:’TEST’;
The auxiliary instance will be created in the /u01/aux location and the table will be exported and imported .Finally the auxiliary instance will be dropped.
All the activity will be taken care by rman.
To recover the table before the delete operation use the appropriate scn before the delete operation.
RMAN> RECOVER TABLE SCOTT.EMP
UNTIL SCN 1601543
AUXILIARY DESTINATION ‘/u01/aux’
REMAP TABLE ‘SCOTT’.’EMP’:’EMP_OLD_1′
REMAP TABLESPACE ‘USERS’:’TEST’;
We can also use TIMESTAMP instead of SCN
===================================
RMAN> RECOVER TABLE SCOTT.EMP
UNTIL TIME “TO_DATE(’02-JUN-2020 01:00′, ‘DD-MON-YYYY HH24:MI’)”
AUXILIARY DESTINATION ‘/u01/aux’
REMAP TABLE ‘SCOTT’.’EMP’:’EMP_OLD’;
For PDB database use
=================
RMAN> RECOVER TABLE SCOTT.EMP OF PLUGGABLE DATABASE pdb1
UNTIL SCN 1853267
AUXILIARY DESTINATION ‘/u01/aux’
REMAP TABLE ‘SCOTT’.’EMP’:’EMP_OLD’;
Recent Posts
