Shulou(Shulou.com)06/01 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.