FLASHBACK DATABASE command is a fast alternative to performing an incomplete recovery.
Flashback must be enabled before any flashback operations are performed.
If the database is in NO ARCHIVE LOG it must be switched to ARCHIVE LOG mode.
To Turn on Flash back:-
[oracle@trichy ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Oct 10 23:34:25 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 2046820352 bytes
Fixed Size 8622480 bytes
Variable Size 587206256 bytes
Database Buffers 1442840576 bytes
Redo Buffers 8151040 bytes
Database mounted.
Database opened.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/12.2.0.1/db_1/dbs/arch
Oldest online log sequence 14
Next log sequence to archive 16
Current log sequence 16
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT EXCLUSIVE
ORACLE instance started.
Total System Global Area 2046820352 bytes
Fixed Size 8622480 bytes
Variable Size 587206256 bytes
Database Buffers 1442840576 bytes
Redo Buffers 8151040 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-38709: Recovery Area is not enabled.
SQL> alter system set db_recovery_file_dest_size=10g scope=spfile;
System altered.
SQL> alter system set db_recovery_file_dest='/u01/arch' scope=spfile;
System altered.
SQL> Shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT EXCLUSIVE
ORACLE instance started.
Total System Global Area 2046820352 bytes
Fixed Size 8622480 bytes
Variable Size 587206256 bytes
Database Buffers 1442840576 bytes
Redo Buffers 8151040 bytes
Database mounted.
SQL> ALTER DATABASE FLASHBACK ON;
Database altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> set time on;
23:46:53 SQL>
23:46:55 SQL> create user remo identified by remo;
User created.
23:47:12 SQL> grant connect,resource to remo;
Grant succeeded.
23:47:19 SQL> Øgrant all privileges to remo;
Grant succeeded.
23:47:39 SQL> conn remo/remo
Connected.
23:49:41 SQL> CREATE TABLE t5 (id NUMBER(10));
Table created.
23:51:51 SQL> insert into t5 values(1);
1 row created.
23:52:01 SQL> /
1 row created.
23:52:03 SQL> /
1 row created.
23:52:03 SQL> commit;
Commit complete.
23:52:08 SQL> conn / as sysdba
Connected.
23:52:54 SQL> SELECT current_scn,TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;
CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
6373386 2020-10-10 23:53:07
23:53:07 SQL> drop user remo cascade;
User dropped.
23:53:39 SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
23:54:18 SQL> set time off
SQL> STARTUP MOUNT EXCLUSIVE
ORACLE instance started.
Total System Global Area 2046820352 bytes
Fixed Size 8622480 bytes
Variable Size 587206256 bytes
Database Buffers 1442840576 bytes
Redo Buffers 8151040 bytes
Database mounted.
SQL> flashback database to timestamp to_date('2020-10-10 23:52:54','YYYY-MM-DD HH24:MI:SS');
Flashback complete.
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
SQL>
SQL>
SQL> conn remo/remo
Connected.
SQL> select * from t5;
ID
----------
1
1
1
SQL>
Screen Shots
No comments:
Post a Comment