Flashback Of DATABASE with Normal Restore Point
Flashback Database enables you to rewind your entire database backward in time, reversing the effects of unwanted database
changes within a given time window. The effects are similar to database point-in-time recovery.
Oracle Flashback Database, accessible from both RMAN (by means of the FLASHBACK DATABASE command) and SQL*Plus
(by means of the FLASHBACK DATABASE statement), lets you quickly recover the entire database from logical data corruptions or user errors.
About Normal Restore Points
Creating a normal restore point assigns the restore point name to a specific point in time or SCN, as a kind of bookmark or alias you can use with commands that recognize a RESTORE POINT clause as a shorthand for specifying an SCN.
Before performing any operation that you may have to reverse, you can create a normal restore point. The name of the restore point and the SCN are recorded in the control file. Then, if you later need to use Flashback Database, Flashback Table, or point-in-time recovery,
you can refer to the target time using the name of the restore point instead of a time expression or SCN. Defining a normal restore point before an operation to be reversed later eliminates the need to manually record an SCN in advance, or investigate the correct SCN after the fact using features such as Flashback Query.
Normal restore points are very lightweight. The control file can maintain a record of thousands of normal restore points with no significant impact upon database performance. Normal restore points eventually age out of the control file if not manually deleted, so they require no ongoing maintenance.
Commands Supporting the Use of Restore Points
Restore points can be used to specify the target SCN in the following contexts:
The RECOVER DATABASE and FLASHBACK DATABASE commands in RMAN
The FLASHBACK TABLE statement in SQL*Plus
==================== PRACTICAL EXAMPLE ========================
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38759: Database must be mounted by only one instance and not open.
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1367343104 bytes
Fixed Size 1302492 bytes
Variable Size 335544356 bytes
Database Buffers 1023410176 bytes
Redo Buffers 7086080 bytes
Database mounted.
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38707: Media recovery is not enabled.
SQL> alter database archivelog;
Database altered.
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38709: Recovery Area is not enabled.
SQL> alter database open;
Database altered.
SQL> show parameter db_recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
SQL>
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination E:\oracle\product\10.2.0\db_1\RDBMS
Oldest online log sequence 23
Next log sequence to archive 25
Current log sequence 25
SQL> alter system set log_archive_dest_10='LOCATION=USE_DB_RECOVERY_FILE_DEST' ;
System altered.
SQL> alter system set db_recovery_file_dest_size=2000M;
System altered.
SQL> alter system set db_recovery_file_dest='E:\oracle\product\10.2.0\flash_recovery_area';
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 23
Next log sequence to archive 25
Current log sequence 25
SQL>
SQL>
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1367343104 bytes
Fixed Size 1302492 bytes
Variable Size 335544356 bytes
Database Buffers 1023410176 bytes
Redo Buffers 7086080 bytes
Database mounted.
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
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 23
Next log sequence to archive 25
Current log sequence 25
SQL>
SQL> alter system switch logfile;
System altered.
SQL> show parameter db_flashback_retention_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
SQL>
SQL>
SQL> create table scott.sales as select * from sh.sales;
Table created.
SQL>
SQL> create restore point b4_change;
Restore point created.
SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
FROM V$RESTORE_POINT;
NAME SCN TIME DATABASE_INCARNATION# GUA STORAGE_SIZE
--------------------- --- ------------ ----------------------------------------------------------------------------------------------------------------------------
B4_CHANGE 1515344 05-AUG-11 11.18.48.000000000 PM 2 NO 0
SQL> alter user scott identified by tiger account unlock;
User altered.
SQL> conn scott/tiger
Connected.
SQL> drop table emp;
Table dropped.
SQL> truncate table sales;
Table truncated.
SQL> select count(*) from sales;
COUNT(*)
----------
0
SQL> select count(*) from emp;
select count(*) from emp
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn / as sysdba
Connected.
SQL> flashback database to restore point b4_change;
flashback database to restore point b4_change
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1367343104 bytes
Fixed Size 1302492 bytes
Variable Size 335544356 bytes
Database Buffers 1023410176 bytes
Redo Buffers 7086080 bytes
Database mounted.
SQL>
SQL>
SQL> flashback database to restore point b4_change;
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> conn scott/tiger
Connected.
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL> select count(*) from sales;
COUNT(*)
----------
918843
SQL> conn / as sysdba
Connected.
SQL> SELECT NAME, SCN, TIME, GUARANTEE_FLASHBACK_DATABASE FROM V$RESTORE_POINT;
NAME SCN TIME GUARANTEE_FLASHBACK_DATABASE
---------- -------------------------------------------------------------------------------------------------------------------------
B4_CHANGE 1515344 05-AUG-11 11.18.48.000000000 PM NO
SQL> drop restore point b4_change;
Restore point dropped.
SQL> SELECT NAME, SCN, TIME, GUARANTEE_FLASHBACK_DATABASE FROM V$RESTORE_POINT;
no rows selected
Flashback Of DATABASE with Normal Restore Point via RMAN
SQL> create restore point b4_change ;
Restore point created.
SQL> truncate table scott.sales;
Table truncated.
Now open new command prompt window
C:\Documents and Settings\Administrator>rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Fri Aug 5 23:47:08 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1285180341)
RMAN>
RMAN> shutdown immediate;
using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down
RMAN> startup mount;
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 1367343104 bytes
Fixed Size 1302492 bytes
Variable Size 335544356 bytes
Database Buffers 1023410176 bytes
Redo Buffers 7086080 bytes
RMAN> flashback database to restore point b4_change;
Starting flashback at 05-AUG-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=542 devtype=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished flashback at 05-AUG-11
RMAN> alter database open resetlogs;
database opened
RMAN>exit
Flashback Of TABLE with Normal Restore Point
SQL> create restore point b4_delrec;
Restore point created.
SQL> alter table scott.sales enable row movement;
Table altered.
SQL> select count(*) from scott.sales;
COUNT(*)
----------
918843
SQL> delete from scott.sales where rownum < 100;
99 rows deleted.
SQL> select count(*) from scott.sales;
COUNT(*)
----------
918744
SQL> commit;
Commit complete.
SQL> flashback table scott.sales to restore point b4_delrec;
Flashback complete.
SQL> select count(*) from scott.sales;
COUNT(*)
----------
918843
SQL>
No comments:
Post a Comment