Performing Flashback Database
The FLASHBACK DATABASE command is a fast alternative to performing an incomplete recovery. In order to flashback the database you must have SYSDBA privilege and the flash recovery area must have been prepared in advance.
The FLASHBACK_ON column of the V$DATABASE view shows the current status of flashback database.
If the database is in NOARCHIVELOG it must be switched to ARCHIVELOG mode.
With flashback enabled the database can be switched back to a previous point in time or SCN without the need for a manual incomplete recovery. In the following example a table is created, the database is then flashbacked to a time before the table was created.
Flashback database allows you to rollback database to a time in the past.
Flashback database can be issued with 3 different conditions:
1. TO TIMESTAMP
2. TO SCN
3. TO SEQUENCE( LOG ARCHIVE SEQ)
4. RESTORE POINT
How much size we should set the flash recovery area:
After you have enabled the Flashback Database feature and allowed the database to generate some flashback logs, run the following query:
SQL> SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;
ESTIMATED_FLASHBACK_SIZE
------------------------
221061120
SQL>
How far you can flashback database:
To determine the earliest SCN and earliest Time you can Flashback your database, give the following query:
SQL> alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIM
-------------------- --------------------
1614954 26-JAN-2018 00:50:08
SQL>
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
26-JAN-18 09.24.41.474574 AM +05:30
SQL>
SQL> !date
Fri Jan 26 09:24:47 IST 2018
Flashback database using SCN:
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1636591
SQL> create table t1 (id number);
Table created.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$
[oracle@localhost ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 26 09:26:08 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> flashback database to scn 1636591;
flashback database to scn 1636591
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 26 09:29:30 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1620115456 bytes
Fixed Size 2253704 bytes
Variable Size 989858936 bytes
Database Buffers 620756992 bytes
Redo Buffers 7245824 bytes
Database mounted.
SQL> flashback database to scn 1636591;
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 read only;
Database altered.
SQL> conn ducat/ducat
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DAY1 TABLE
EMP TABLE
SYS_TEMP_FBT TABLE
TEST TABLE
TEST1 TABLE
TEST_FLASH TABLE
TEST_RBIN1 TABLE
TEST_RBIN2 TABLE
TEST_RBIN3 TABLE
9 rows selected.
SQL>
If everything looks good
[oracle@localhost ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 26 09:33:20 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1620115456 bytes
Fixed Size 2253704 bytes
Variable Size 989858936 bytes
Database Buffers 620756992 bytes
Redo Buffers 7245824 bytes
Database mounted.
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 name,open_mode ,log_mode,flashback_on from v$database;
NAME OPEN_MODE LOG_MODE FLASHBACK_ON
--------- -------------------- ------------ ------------------
DUCAT READ WRITE ARCHIVELOG YES
SQL>
Flashback database using restore point:
In addition to using SCNs and timestamps, we can create restore points and guaranteed restore points. A restore point is just a text alias representing a SCN. A guaranteed restore point means the database doesn't delete any flashback logs between that point and the current time.
CREATE RESTORE POINT before_upgrade;
CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;
we can use below command to flashback database:
Existing all restore points can be displayed using the V$RESTORE_POINT view.
SQL> create restore point before_upgrade;
Restore point created.
SQL> select * from v$restore_point;
SCN DATABASE_INCARNATION# GUARANTEE_ STORAGE_SIZE TIME RESTORE_PO PRE NAME
---------- --------------------- ---------- ------------ -------------------- ---------- --- ---------------
1637197 6 NO 0 26-JAN-18 09.42.24.0 NO BEFORE_UPGRADE
00000000 AM
SQL>
SQL> create user abc identified by abc;
User created.
SQL> grant create session to abc;
Grant succeeded.
SQL> conn abc/abc
Connected.
SQL> exit
[oracle@localhost ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 26 09:56:29 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1620115456 bytes
Fixed Size 2253704 bytes
Variable Size 989858936 bytes
Database Buffers 620756992 bytes
Redo Buffers 7245824 bytes
Database mounted.
SQL> FLASHBACK DATABASE TO RESTORE POINT before_upgrade;
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> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
DUCAT READ WRITE
SQL> conn abc/abc
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL>
ABC user not exist after flashback database using restore point before_upgrade.
Flashback table using restore_point:
SQL> select * from v$restore_point;
SCN DATABASE_INCARNATION# GUARANTEE_ STORAGE_SIZE TIME RESTORE_PO PRE NAME
---------- --------------------- ---------- ------------ -------------------- ---------- --- ---------------
1637197 6 NO 0 26-JAN-18 09.42.24.0 NO BEFORE_UPGRADE
00000000 AM
SQL>
SQL> select * from emp;
EMPNO NAME
---------- ---------------
111 ABDD
222 BCFF
333 DDGG
444 EEE
555 FFFF
555 FFFF
666 GGGG
666 GGGG
666 GGGG
9 rows selected.
SQL> insert into emp values(999,'LLLL');
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> commit;
Commit complete.
SQL> select * from emp;
EMPNO NAME
---------- ---------------
111 ABDD
222 BCFF
333 DDGG
444 EEE
555 FFFF
555 FFFF
666 GGGG
666 GGGG
666 GGGG
999 LLLL
999 LLLL
999 LLLL
12 rows selected.
SQL> FLASHBACK TABLE emp TO RESTORE POINT before_upgrade; 2
Flashback complete.
SQL> select * from emp;
EMPNO NAME
---------- ---------------
111 ABDD
222 BCFF
333 DDGG
444 EEE
555 FFFF
555 FFFF
666 GGGG
666 GGGG
666 GGGG
9 rows selected.
SQL>
No comments:
Post a Comment