Examples of Oracle Flashback database operations

Back up the database before doing the operation

rman> backup Database;

1. Check if the Flash recovery area is started:

Sql> Show Parameter Db_recovery_file

NAME TYPE VALUE

------------------------------------  ----------- ------------------------------

Db_recovery_file_dest Tring D:\oracle/flash_recovery_area

Db_recovery_file_dest_size Big Integer 1G

2. Check that the archive is enabled

sql> archive log list;

Database log Mode archive mode

AutoArchive enabled

Archive Endpoint Use_db_recovery_file_dest

The oldest online log sequence 9

Next Archive log sequence 11

Current Log Sequence 11

3. Check that the Flashback database is enabled

Sql> select flashback_on from V$database;

flashback_on

------------------

YES

4. Querying the current SCN

Sql> SELECT CURRENT_SCN from V$database;

Current_scn

-----------

947921

5. Query the current time

Sql> Select To_char (sysdate, ' yy-mm-dd hh24:mi:ss ') time from dual;

Time

-----------------

09-10-14 14:37:05

6. Delete Table A

Sql> select * from A;

ID NAME

---------- ----------

1 Tianle

2 Dave.

sql> drop table A;

The table has been deleted.

Sql> commit;

7. Flashback database is actually an incomplete recovery operation for the databases because the database needs to be shut down and restarted to the Mount state.

sql> shutdown Immediate

The database has been closed.

The database has been unloaded.

The ORACLE routine has been closed.

Sql> Startup Mount

The ORACLE routine has started.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

Total System Global area 209715200 bytes

Fixed Size 1248116 bytes

Variable Size 79692940 bytes

Database buffers 121634816 bytes

Redo buffers 7139328 bytes

Database loading complete.

8. Perform recovery: Divide timestamp or SCN two kinds

Sql> Flashback database to timestamp to_timestamp (' 09-10-14 14:37:05 ', ' yy-mm-dd

Hh24:mi:ss ');

Flash back complete.

Or:

Sql> Flashback database to SCN 947921;

Flash back complete.

9. Open the database:

After executing the Flashback database command, Oracle provides two ways for you to repair your database:

1. The direct ALTER DATABASE open resetlogs the databases and, of course, the data that was generated after the SCN or timestamp point in time was lost.

2. Execute the ALTER DATABASE open Read Only command to open the database in read-only mode, and then immediately export the error to the table data by logical export, then execute the Recover database command to reapply the database-generated redo, fix the database to the state before the flashback DB operation, and then re-import the previously-manipulated table in a logical import, which would have minimal impact on existing data and no data loss.

Demo here, open in Resetlogs:

sql> ALTER DATABASE open resetlogs;

The database has changed.

Validating data:

Sql> select * from A;

ID NAME

---------- ----------

1 Tianle

2 Dave.

Author: 51cto Blog Oracle Little Bastard