Oracle Restore point and its usage


Database version:- Oracle 11g R2

What is restore point?
A CREATE RESTORE POINT statement creates a restore point, which is a name associated with an SCN of the database pointing to the time of the creation of the restore point.A restore point
can be used to flashback a table or the database back to the time of creation of the restore point without the need to determine the SCN or timestamp.The database can retain up to 2048 restore point. Restore points are retained in the
database for at least the number of days specified for the CONTROL_FILE_RECORD_KEEP_TIME initialization parameter. The default value of that parameter is 7 days. Guaranteed restore
points are retained in the database until explicitly dropped by the user.


There are two types of restore point:

1.Normal restore points: A normal restore point enables you to flash the database back to a restore point within the time period determined by the DB_FLASHBACK_RETENTION_TARGET
initialization parameter. The database automatically manages normal restore points. When the maximum number of restore points is reached the database automatically drops the oldest
restore point. However, you can explicitly drop a normal restore point using the DROP RESTORE POINT statement.The control file stores the name of the restore point and the SCN.


2.Guaranteed restore points:Guaranteed restore points are basically alias’es for SCN’s .A guaranteed restore point enables you to flashback the database back to the restore point
regardless of the DB_FLASHBACK_RETENTION_TARGET initialization parameter setting. The guaranteed ability to flash back depends on sufficient space available in the flash recovery area.

Prerequisites for Guaranteed Restore Points:-
1.The database must be running in ARCHIVELOG mode.
2.A flash recovery area must be configured.

Example of Guaranteed restore point:-
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

NAME      DATABASE_ROLE         OPEN_MODE                FLASHBACK_ON       LOG_MODE
---------      ----------------                  -------------------- -           -----------------             ------------
PRIM         PRIMARY                      READ WRITE                   NO                             NOARCHIVELOG


As mentioned above for guaranteed restore point the database has to be in archivelog mode.


SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             310380536 bytes
Database Buffers           96468992 bytes
Redo Buffers                4308992 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     4
Next log sequence to archive   6
Current log sequence           6

SQL> show parameter DB_RECOVERY_FILE_DEST;



NAME                                     TYPE            VALUE
------------------------------------ -----------     ------------------------------
db_recovery_file_dest                string        /u01/app/oracle/flash_recovery
                                                                     _area

db_recovery_file_dest_size           big integer 3882M

SQL> alter database open;

Database altered.

Check if any guaranteed flashback is enabled or not:-
SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

no rows selected


Now lets create a restore point.

SQL> CREATE RESTORE POINT BEFORE_ACTIVITY GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

NAME                                  SCN TIME                                          DATABASE_INCARNATION# GUA STORAGE_SIZE
------------------------------ ---------- --------------------------------------------- --------------------- --- ------------
BEFORE_ACTIVITY                  1032021 08-JAN-15 10.50.41.000000000 PM                                   2 YES      8192000

So we can see the restore point named "BEFORE_ACTIVITY" has been created with scn no 1032021 at 08-JAN-15 10.50.41.000000000 PM


Now lets make some changes in the database.

SQL> grant connect , resource to soumya identified by soumya;

Grant succeeded.

SQL> conn soumya/soumya
Connected.
SQL> create table test (id number);

Table created.

SQL> insert into test values(1);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

        ID
----------
         1
         1
         1
         1
         1

Now lets flashback the database to the restore point which we created.

SQL> select * from V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
             1032021 08-JAN-15             1440        8192000                 99581952
           
We can see information of flashback data from the above view.Use this view to help estimate the amount of flashback space required for the current workload.


SQL> select * from V$FLASHBACK_DATABASE_LOGFILE;
NAME                                                                                   LOG#    THREAD#  SEQUENCE#      BYTES FIRST_CHANGE# FIRST_TIM
-------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ------------- ---------
/u01/app/oracle/flash_recovery_area/PRIM/flashback/o1_mf_bbxh3b1v_.flb                    1          1          1    8192000       1032021 08-JAN-15

The above view shows information of flashback log file.

SQL> flashback database to restore point BEFORE_ACTIVITY ;
flashback database to restore point BEFORE_ACTIVITY
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.

So in order to flashback the database the db has to be in mount stage.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             310380536 bytes
Database Buffers           96468992 bytes
Redo Buffers                4308992 bytes
Database mounted.

SQL> flashback database to restore point BEFORE_ACTIVITY    ;

Flashback complete.

SQL> select * from V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIME                    RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- ---------------------------------------- ---------------- -------------- ------------------------
             1032021 08-JAN-15                                            1440        8192000                        0
           

SQL> select * from V$FLASHBACK_DATABASE_STAT;

BEGIN_TIM END_TIME  FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
--------- --------- -------------- ---------- ---------- ------------------------
08-JAN-15 08-JAN-15           8192    3768320          0                        0


Now lets open the database.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

Database altered.


SQL> select username from dba_users where username='SOUMYA';

no rows selected

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY


Reference :https://docs.oracle.com/cd/E11882_01/backup.112/e10642/flashdb.htm#BRADV577
           

Please share your ideas and opinions about this topic.

If you like this post, then please share it with others.
Please subscribe on email for every updates on mail.
           


Nenhum comentário:

Postar um comentário

Related Posts Plugin for WordPress, Blogger...