A brief introduction to flashback database
The flashback database method can be performed with either sqlplus or RMAN:
SQL > FLASHBACK DATABASE TO TIMESTAMP ('');-(based on point in time)
SQL > FLASHBACK DATABASE TO SCN;-(based on SCN)
SQL > FLASHBACK DATABASE TO RESTORE POINT;-- (create command create restore point name based on sestore point)
RMAN > FLASHBACK DATABASE TO TIME = "TO_DATE ('2009-05-27 16-015-27 1600 YYYY-MM-DD HH24:MI:SS')";-- (based on time point)
RMAN > FLASHBACK DATABASE TO SCN=23565;-(based on SCN)
RMAN > FLASHBACK DATABASE TO SEQUENCE=223 THREAD=1;-(serial number, you can view the current sequence with list incarnation of database)
Both of the above two methods can realize flashback database. What we need to pay attention to is to use the command to open the database alter database open resetlogs after the operation is completed.
Here is a demonstration of a point-in-time flashback database experiment:
1. View the current archive
SQL > archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 25
Next log sequence to archive 27
Current log sequence 27
two。 Check if the flashback database is open. If not, use the alter database flashback on command to open it.
SQL > select flashback_on from v_$database
FLASHBACK_ON
-
YES
3. Query the current time first
SQL > select to_char (sysdate,'yyyy-mm-dd:hh34:mi:ss') from dual
TO_CHAR (SYSDATE,'YY
-
2017-07-17 purl 16 purl 16 purl 45
4. Switch HR, create a table TMP717 with HR and insert a row of data, then delete the table (plus PURGE), and switch logs with DBA users
SQL > conn hr/123456
Connected.
SQL > create table tmp717 (cons_no varchar2 (10))
Table created.
SQL > insert into tmp717 values ('1')
1 row created.
SQL > commit
Commit complete.
SQL > select to_char (sysdate,'yyyy-mm-dd:hh34:mi:ss') from dual
TO_CHAR (SYSDATE,'YY
-
2017-07-17 purl 16 purl 20 purl 47
SQL > drop table tmp717 purge
2
Table dropped.
SQL > conn / as sysdba
Connected.
SQL > alter system switch logfile
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 26
Next log sequence to archive 28
Current log sequence 28
(the current log becomes 28)
5. Shut down the database and start to MOUNT state (action required to flashback the database)
SQL > shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL > startup mount
ORACLE instance started.
Total System Global Area 820236288 bytes
Fixed Size 1339628 bytes
Variable Size 549457684 bytes
Database Buffers 264241152 bytes
Redo Buffers 5197824 bytes
Database mounted.
SQL > exit
6. Open RMAN for flashback operation
[oracle@station4 ~] $rman target /
Recovery Manager: Release 11.2.0.1.0-Production on Mon Jul 17 16:31:13 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to target database: ORCL (DBID=1359978017, not open)
RMAN > list incarnation of database;-- (check the current avatar first)
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
-
1 1 ORCL 1359978017 PARENT 1 13-AUG-09
22 ORCL 1359978017 CURRENT 754488 22-NOV-13
RMAN > run {
2 > sql "alter session set nls_date_format=''YYYY-MM-DD:HH24:MI:SS''"
3 > set until time '2017-07-17 purge 16 purl 20 purl 47'
4 > restore database
5 > recover database;}
7. Open the database with RESETLOGS and restore the query table successfully
SQL > alter database open resetlogs
Database altered.
SQL > select * from hr.tmp717
CONS_NO
-
one
8. Re-examine the avatar
RMAN > list incarnation of database
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
-
1 1 ORCL 1359978017 PARENT 1 13-AUG-09
22 ORCL 1359978017 PARENT 754488 22-NOV-13
3 3 ORCL 1359978017 CURRENT 1083685 17-JUL-17
The above experiments can also be done with SQL > FLASHBACK DATABASE TO TIMESTAMP ('2017-07-17 16-20 47'); the results are the same
In the experiment, we can see that there is an avatar, the current avatar number is 1083685, and the deleted table has been returned, indicating that it has been successfully executed.
