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

The Flashback Database flashback database function greatly reduces the recovery cost of data loss caused by user errors. This is a solution that trades space for shorter recovery time, and it's worth it.

Here is how to use the flashback database to experience the convenience of this recovery operation.

1. Prerequisites for using Flashback Database

1) flashback database is enabled

2) flash recovery area must be opened. For RAC,flash recovery area, it must be in shared storage.

3) must be in archivelog mode and enable FORCE LOGGIN

two。 Confirm one by one whether the above prerequisites are met

1) verify that flashback database is enabled and confirm that FORCE LOGGIN is enabled

SYS@ora11g > select flashback_on,force_logging from v$database

FLASHBACK_ON FOR

YES NO

If flashback_on is "NO", see "[Flashback] enable Flashback Database flashback database function" (http://space.itpub.net/519536/viewspace-590636) for modification method.

If force_logging is "NO", turn on the following SQL statement.

SYS@ora11g > alter database force logging

Database altered.

SYS@ora11g > select flashback_on,force_logging from v$database

FLASHBACK_ON FOR

YES YES

2) verify whether flash recovery area is enabled

This step was confirmed when the flashback database feature was enabled.

SYS@ora11g > show parameter db_recovery_file

NAME TYPE VALUE

-

Db_recovery_file_dest string / u01/app/oracle/flash_recovery

_ area

Db_recovery_file_dest_size big integer 3852M

3) whether the database is in archivelog mode

SYS@ora11g > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 11

Next log sequence to archive 13

Current log sequence 13

3. Methods to confirm the SCN and Time to which the database can be rolled forward

If the data points that need to be recovered are earlier than this, unfortunately, the flashback database feature will be powerless.

SYS@ora11g > alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss'

Session altered.

SYS@ora11g > select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI

--

1033529 2012-04-02 03:36:40

4. Flashback database function makes a brilliant debut.

1) create test tables fd_1, fd_2 and fd_3

SYS@ora11g > create table fd_1 as select * from dba_objects

Table created.

SYS@ora11g > create table fd_2 as select * from fd_1

Table created.

SYS@ora11g > create table fd_3 as select * from fd_1

Table created.

SYS@ora11g > select count (*) from fd_1

COUNT (*)

-

72465

SYS@ora11g > select count (*) from fd_2

COUNT (*)

-

72465

SYS@ora11g > select count (*) from fd_3

COUNT (*)

-

72465

SYS@ora11g > set time on

21:59:40 SYS@ora11g > select sysdate from dual

SYSDATE

-

2012-04-07 21:59:44

2) truncate table fd_2, drop table fd_3

21:59:44 SYS@ora11g > truncate table fd_2

Table truncated.

22:00:06 SYS@ora11g > drop table fd_3

Table dropped.

3) use the Flashback Database function to restore to the point in time before deletion 2012-04-07 21:59:44

22:00:17 SYS@ora11g > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

22:02:04 SYS@ora11g > startup mount exclusive

ORACLE instance started.

Total System Global Area 313860096 bytes

Fixed Size 1336232 bytes

Variable Size 247467096 bytes

Database Buffers 58720256 bytes

Redo Buffers 6336512 bytes

Database mounted.

22:02:52 SYS@ora11g > Flashback Database to timestamp (to_date ('2012-04-07 21 to_date 59 hh34:mi:ss'))

Flashback complete.

4) one of two ways to repair the database after flashback: open read only

This method is recommended for recovery, because after opening it in read only mode, export the table that needs to be restored by EXP, then restore the database to its original state through recover database, and then IMP the missing data to the database. In this way, the impact on the database can be minimized, and there is no data loss in other tables.

After read only opens, check the status of the three tables:

22:03:57 SYS@ora11g > alter database open read only

Database altered.

22:04:33 SYS@ora11g > select count (*) from fd_1

COUNT (*)

-

72465

22:04:37 SYS@ora11g > select count (*) from fd_2

COUNT (*)

-

72465

22:04:40 SYS@ora11g > select count (*) from fd_3

COUNT (*)

-

72465

The method to cancel the flashback result and return to the pre-flashback state:

22:04:43 SYS@ora11g > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

22:05:09 SYS@ora11g > startup mount

ORACLE instance started.

Total System Global Area 313860096 bytes

Fixed Size 1336232 bytes

Variable Size 247467096 bytes

Database Buffers 58720256 bytes

Redo Buffers 6336512 bytes

Database mounted.

22:06:08 SYS@ora11g > recover database

Media recovery complete.

22:06:18 SYS@ora11g > alter database open

Database altered.

22:06:41 SYS@ora11g > select count (*) from fd_1

COUNT (*)

-

72465

22:06:55 SYS@ora11g > select count (*) from fd_2

COUNT (*)

-

0

22:06:59 SYS@ora11g > select count (*) from fd_3

Select count (*) from fd_3

*

ERROR at line 1:

ORA-00942: table or view does not exist

It can be seen that after passing the recover above, the database is restored to its pre-flashback state.

5) one of the two ways to repair the database after flashback: open resetlogs

After opening the database through open resetlogs, it is obvious that all the data after flashing back to the point in time will be lost, so use it with caution!

SYS@ora11g > alter database open resetlogs

SYS@ora11g > select count (*) from fd_1

COUNT (*)

-

72465

SYS@ora11g > select count (*) from fd_2

COUNT (*)

-

72465

SYS@ora11g > select count (*) from fd_3

COUNT (*)

-

72465

5. Summary

Here is a summary of the syntax for Flashback Database flashback databases. Flashback databases can be used in SQL*Plus and RMAN environments.

Operation method of flashback database based on timestamp:

Flashback Database to timestamp (to_date ('2012-04-07 21 Fringe 59 Flux 44mm hh34:mi:ss'))

Flashback Database to timestamp (sysdate-1/24)

Operation method of flashback database based on SCN:

Flashback Database to 1321427

Good luck.

Secooler

12.04.07

-- The End--

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.