Description:-
Oracle Flashback Technology provides a set of features that support viewing and rewinding data back and forth in time. The flashback features offer the capability to query past versions of schema objects, query historical data, analyze database changes, or perform self-service repair to recover from logical corruptions while the database is online.Now let’s see about how to recover user and table using flashback concept.
Step1:Startup the database and check the flashback status.
[oracle@trichy ~]$ sqlplus / as sysdbaSQL*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.
Step 2:Create the new user and table to insert the record.
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.
Step 3:To check the current scn number and then drop the user.
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.
Step 4:Shutdown the database and then startup mount stage to execute the flashback command.
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.
Step 5:Open the resetlogs and now check the user and table.
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
SQL>
SQL>
SQL> conn remo/remo
Connected.
SQL> select * from t5;
ID
———-
1
1
1
SQL>
