Create Restore and Drop Oracle Restore Point - Types and Benefits

advertisements

_____________________________________________________________________________________________________________________

Restore Point 

It is a name associated with the SCN in the database. Using this restore point we can revert to the SCN which is pointing to the restore point even if you make some changes to the database.

Oracle use the flashback feature for the db restoration. 

Benefits 

This can be used during patching as a fast backup During load test or any other data manipulation test the database can be reverted to the original position where the test started. 

Types of Restore Points 

1. Normal restore point  

2. Guaranteed restore point 

The difference between these two is that guaranteed restore point allows you to flashback to the restore point regardless of the DB_FLASHBACK_RETENTION_TARGET initialization parameter. Using guaranteed restore points is always possible as long as you have enough space in the flash recovery area. 

Guaranteed restore point never age out like normal restore point. You must explicitly drop the guaranteed restore point.

advertisements

 

Prerequisites:- 

1. Sysdba privilege required 

2. Database must be in archievelog mode 

    Setting up Archivelog Mode 

3. Database must be enabled with flash recovery area. 
    Setup Flashback Recovery Area - FRA 

Steps to create guaranteed restore point

1.	sqlplus / as sysdba;
2.	Make sure db is enabled with flashback and archivelog
select name,database_role,open_mode,flashback_on,log_mode from v$database;
3.	create restore point <restore point name> guarantee flashback database;
4.	select * from v$restore_point;

Demo

SQL> select name,database_role,open_mode,flashback_on,log_mode from v$database;

NAME	  DATABASE_ROLE    OPEN_MODE		FLASHBACK_ON	   LOG_MODE
--------- ---------------- -------------------- ------------------ ----------
CDB	  PRIMARY	   READ WRITE		YES		   ARCHIVELOG

SQL> create restore point Before_test guarantee flashback database;

Restore point created.

set pages 1000 lines 120
col name for a30
select scn, name from v$restore_point


       SCN NAME
---------- ------------------------------
   4508882 BEFORE_TEST

Create a sample table under test user
SQL> connect test/test
ERROR:

Connected.

SQL> create table Objects as select * from tab where rownum<200;

Table created.

SQL> desc objects
 Name			       Null?	Type
 ----------------------------- -------- --------------------
 TNAME			       NOT NULL VARCHAR2(128)
 TABTYPE				VARCHAR2(13)
 CLUSTERID				NUMBER

Steps to recover the database to a Guaranteed restore point.

1.	Sqlplus / as sysdba
2.	shutdown immediate;
3.	startup mount;
4.	select * from v$restore_point;
5.	flashback database to restore point <restore point name>
6.	alter database open resetlogs;

Demo

SQL> conn / as sysdba
Connected.
SQL> shut immediate; 
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount; 
ORACLE instance started.

Total System Global Area  838858176 bytes
Fixed Size		    8902080 bytes
Variable Size		  633339904 bytes
Database Buffers	  192937984 bytes
Redo Buffers		    3678208 bytes
Database mounted.
SQL> select scn, name from v$restore_point ;

       SCN NAME
---------- ------------------------------
   4508882 BEFORE_TEST

SQL> flashback database to restore point BEFORE_TEST;

Flashback complete.


SQL> ALTER DATABASE OPEN RESETLOGS; 

Database altered.

SQL> CONN test/test

Connected.
SQL> desc objects
ERROR:
ORA-04043: object objects does not exist

Drop guaranteed restore point

SQL> conn / as sysdba
Connected.
SQL> drop restore point BEFORE_TEST
SQL> /

_____________________________________________________________________________________________________________________

Website Stats