Recovery Manager (RMAN) Table Point In Time Recovery (PITR) in Oracle Database 12c Release 1 and 2 (12.1 and 12.2)
New in
Oracle 12c database Architecture, as well as the ability to restore one table
from an RMAN backup. This blog post explains how to do a restore of one
table in a database.
Setup the test:
1.)
Create a user – tables cannot be restored in the SYS schema
2.)
Create a table for that user
3.)
Take a backup – a backup of the table has to exist; it can’t just be in archive
logs.
4.)
Drop the table
5.)
Restore the table
6.)
Check that the table has been restored
1.)
Create a user:
CONN / AS SYSDBA
CREATE USER test IDENTIFIED BY test
QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE TABLE TO test;
2.) Create a table for that user:
CONN test/test
CREATE TABLE t1 (id NUMBER);
INSERT INTO t1 VALUES (1);
COMMIT;
3.) To Check
the current SCN or TIMESTAMP:
CONN / AS SYSDBA
SELECT DBMS_FLASHBACK.get_system_change_number FROM dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
5678990
SQL>
OR
CONN / AS SYSDBA
Select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
23-04-19 12:31:50.077770 PM +05:30
SQL>
4. Add some more data since the SCN was checked:
CONN test/test
INSERT INTO t1 VALUES (2);
COMMIT;
SELECT * FROM t1;
ID
----------
1
2
SQL>
Exit from SQL*Plus and log in to RMAN as a root
user with SYSDBA or SYSBACKUP privilege.
Table Point In Time Recovery (PITR):
Log in to RMAN as a user with SYSDBA or SYSBACKUP privilege.
$ rman target=/
Issue the
RECOVER TABLE command, giving a suitable AUXILIARY DESTINATION location for the
auxiliary database. The point in time can be specified using UNTIL SCN, UNTIL TIME or UNTIL SEQUENCE. In
the following example the REMAP
TABLE clause is included to give the recovered table a new
name so we can compare the before and after.RECOVER TABLE TEST.T1
UNTIL SCN 1853267
AUXILIARY DESTINATION '/u01/data/aux'
REMAP TABLE 'TEST'.'T1':'T1_PREV';
OR
RECOVER TABLE TEST.T1
UNTIL time "to_date('23-04-19 12:31:50','dd-mm-yy hh24:mi:ss')"
AUXILIARY DESTINATION '/oradbf2/rman_dbfs'
REMAP TABLE 'TEST'.'T1':'T1_PREV';
It's rather long, but it clearly shows the
creation of the clone and the data pump export and import operations. Once the
operation is complete, we can see the T1_PREV table has been created and
contains the data as it was when the SCN was captured.
sqlplus test/test
SELECT * FROM t1_prev;
ID
----------
1
Comments
Post a Comment