Backup & Recovery

Flashback database using guaranteed restore point

• 
• 

User Rating:  / 9

PoorBest 

Details

Parent Category: Articles

Created on Friday, 05 October 2012 16:28

Last Updated on Monday, 08 October 2012 15:40

Published on Friday, 05 October 2012 16:28

Written by Guy Lambregts

Hits: 40869

Flashback database using guaranteed restore point.

 

1. Your database is very big

2. You have a major application schema upgrade, in case of failure you would like to reset your database rapidly

3. You are familiar with a restore from RMAN backups but it takes a long time since your database is very big

4. You are using at least release 10G R2 and have heard about restore points

5. Huge DML avoids you enable flashback database since it generates far too much flashback logs 

 

Here is an example how you create easily a restore point and how you can flashback your database to that restore point

a) WITHOUT the need to enable flashback database

b) WITHOUT the need to restore backups 

 

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup mount;
ORACLE instance started.

Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 473957720 bytes
Database Buffers 142606336 bytes
Redo Buffers 7532544 bytes
Database mounted.


SQL> select log_mode,flashback_on from v$database;

LOG_MODE     FLASHBACK_ON
------------ ------------------
NOARCHIVELOG NO

SQL> create restore point BEFORE_SCHEMA_CHANGES guarantee flashback database;
create restore point BEFORE_SCHEMA_CHANGES guarantee flashback database
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'BEFORE_SCHEMA_CHANGES'.
ORA-38785: Media recovery must be enabled for guaranteed restore point.


SQL> alter database archivelog;

Database altered.

SQL> create restore point BEFORE_SCHEMA_CHANGES guarantee flashback database;

Restore point created.


SQL> alter database open;

Database altered.

SQL> /* an error occurs */ drop user TUNING cascade;

User dropped.


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


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup mount;
ORACLE instance started.

Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 473957720 bytes
Database Buffers 142606336 bytes
Redo Buffers 7532544 bytes
Database mounted.


SQL> flashback database to restore point BEFORE_SCHEMA_CHANGES;

Flashback complete.

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> /* database has been reset */ connect tuning/TUNING
Connected.


SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
...
T_SUPPLIER_SU
T_ORDER_OR
T_CURRENCY_CR

25 rows selected.

SQL> select count(*) from t_order_or;

COUNT(*)
----------------
1000000

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ----------------
CONTROL FILE                          0                         0                0
REDO LOG                              0                         0                0
ARCHIVED LOG                        .74                         0                3
BACKUP PIECE                          0                         0                0
IMAGE COPY                           25                         0                1
FLASHBACK LOG                      1.22                         0                2
FOREIGN ARCHIVED LOG                  0                         0                0

7 rows selected.

SQL> select flashback_on from v$database;

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