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 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.
No comments:
Post a Comment