Restore point plays important role in restore and recovery scenario as it allows to store state of the database to certain point, Lets discuss oracle restore points in details with example
Types of
Restore points
- Restore point is said to be "point" in database which is associated with timestamp or an SCN of the database
- Restore point is used to flash back a table or the whole database to certain point specified as per restore point
- Various RMAN operations such as backup and archival backup also uses these restore point
- Restore point can be name up to 128 characters
- There can be 2048 restore points in database provided database has enough FRA
- Privileges required to create restore points
Normal restore point
|
Guaranteed restore point
|
SELECT ANY DICTIONARY
|
SYSDBA
|
FLASHBACK ANY TABLE
|
SYSBACKUP
|
SYSDBA
|
SYSDG
|
SYSBACKUP
|
|
SYSDG
|
Privileges required to view or use of restore point
SELECT ANY
DICTIONARY
FLASHBACK
ANY TABLE
SYSDBA
SYSBACKUP or
SYSDG or
SELECT_CATALOG_ROLE
Requirements:
- Database must have fast recovery area (FRA) set for guaranteed restore point
- Database is in Archive log mode (guaranteed restore point)
There are
two types of restore points
- Guaranteed restore point
- Normal restore point
Guaranteed
restore point
- A guaranteed restore point enables you to flash the database back to the restore point regardless of DB_FLASHBACK_RETENTION_TARGET initialization parameter setting
- Guaranteed restore point must be dropped explicitly by user using DROP RESTORE POINT command
- Guaranteed restore point never ages out
- A normal restore point enables you to flash the database back to a restore point within time determined by DB_FLASHBACK_RETENTION_TARGET initialization parameter setting
- Normal restore point can be dropped explicitly
- The control files stores name of restore point and the SCN
In below
example, we are going to create guaranteed restore point
Check for
flashback on in the database using below query
SQL> select name,FLASHBACK_ON,open_mode from
v$database;
NAME FLASHBACK_ON OPEN_MODE
---------
------------ --------------------------
TECHNO_DB NO READ
WRITE
Turn the
flashback on using below statement
SQL>
alter database flashback on;
Database
altered.
SQL>
select FLASHBACK_ON from v$database;
FLASHBACK_ON
------------------
YES
Check for
Fast Recovery Area size and location
SQL> show
parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------
db_recovery_file_dest string +FLASH
db_recovery_file_dest_size big integer 100G
SQL>
Check for
any restore point using v$restore_point view
set lines
200
col TIME for
a40
col NAME for
a40
select
inst_id,NAME,TIME,GUARANTEE_FLASHBACK_DATABASE,storage_size/1024/1024/1024 from
v$restore_point;
no rows
selected
Create actual
restore point using CREATE RESTORE POINT command
SQL>
create restore point RESTORE_POINT_20FEB2019 guarantee flashback database;
Restore
point created.
Verify if restore
point is created
set lines
200
col TIME for
a40
col NAME for
a40
select NAME,TIME,GUARANTEE_FLASHBACK_DATABASE,storage_size/1024/1024/1024
from v$restore_point;
NAME
TIME GUA
STORAGE_SIZE/1024/1024/1024
-------------
------------
-------------------------- --- ----------------
RESTORE_POINT_20FEB2019
20-FEB-19 06.20.40.000000000 AM YES
2
DROP Restore Point
Drop restore point can be performed
using DROP RESTORE POINT command and we need turn off flashback_on option
SQL> DROP
RESTORE POINT RESTORE_POINT_20FEB2019;
Restore
point dropped.
SQL>
alter database flashback on;
Database
altered.
0 comments:
Post a Comment