实验-闪回数据库

SELECT CURRENT_SCN FROM V$DATABASE;

create restore point guarantee_rp guarantee flashback database;

list restore point all;

SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

drop table t;

create table a as select * from emp;

SELECT CURRENT_SCN FROM V$DATABASE;

SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;

SHUTDOWN IMMEDIATE; STARTUP MOUNT;

SHOW ALL;

FLASHBACK DATABASE TO SCN 46963;

ALTER DATABASE OPEN READ ONLY;

SHUTDOWN IMMEDIATE STARTUP MOUNT ALTER DATABASE OPEN RESETLOGS;

步骤： 0.启动数据库闪回日志 > alter database flashback on;

Database altered.

1.连接到数据库，创建确认恢复点，并查看当前SCN SQL> conn as sysdba Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 Connected as SYS

> alter database flashback on;

Database altered.

SQL> create restore point guarantee_rp guarantee flashback database;

Done

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,   2  GUARANTEE_FLASHBACK_DATABASE   3  FROM V$RESTORE_POINT   4  WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

NAME                                                                                    SCN TIME                                              DATABASE_INCARNATION# GUARANTEE_FLASHBACK_DATABASE -------------------------------------------------------------------------------- ---------- ------------------------------------------------- --------------------- ---------------------------- GUARANTEE_RP                                                                        4250399 10-5？  -13 04.41.33.000000000 ？？                                   1 YES

2.进行DDL操作，并查看SCN

SQL> create table T as select * from scott.emp;

Table created

SQL> SQL> SELECT CURRENT_SCN   2  FROM V$DATABASE;

CURRENT_SCN -----------     4250638

SQL> create table I as select * from scott.emp;

Table created

SQL> SQL>  SELECT CURRENT_SCN   2  FROM V$DATABASE;

CURRENT_SCN -----------     4250665

SQL> create table N as select * from scott.emp;

Table created

SQL> SQL>  SELECT CURRENT_SCN   2  FROM V$DATABASE;

CURRENT_SCN -----------     4250682

3.查看当前数据SCN和可以恢复到的最早SCN号： SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME   2  FROM V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIME -------------------- ---------------------              4249896 2013-5-10 16:05:08

4.重启数据库到mount状态： > shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. > startup mount; ORACLE instance started.

Total System Global Area  405929984 bytes Fixed Size                  1336848 bytes Variable Size             314575344 bytes Database Buffers           83886080 bytes Redo Buffers                6131712 bytes Database mounted. >

5.rman连接数据库，显示所有参数： [oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri May 10 16:12:36 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TESTDB (DBID=2581100181, not open)

RMAN> show all;

using target database control file instead of recovery catalog RMAN configuration parameters for database with db_unique_name TESTDB are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/ogg/rman_bk/$d%T%t'; CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/oracle/dbs/snapcf_testdb.f'; # default

该步骤主要关注的是通道的配置，即上面内容中的：CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/ogg/rman_bk/$d%T%t';

6.将数据库闪回到需要的SCN点上

[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Fri May 10 16:49:57 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

idle> conn /as sysdba Connected. idle> flashback database to scn 4250638   2  ;

Flashback complete.

idle> alter database open read only;

Database altered.

idle> select table_name from dba_tables where table_name in ('T','I','N') and wner='SYS';

TABLE_NAME ------------------------------ T

这时候，如果我觉得当前这个SCN太旧了，我希望看到删除了I和N表后的结果。可以进行以下操作：

> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. > startup mount ORACLE instance started.

Total System Global Area  405929984 bytes Fixed Size                  1336848 bytes Variable Size             314575344 bytes Database Buffers           83886080 bytes Redo Buffers                6131712 bytes Database mounted. > select current_scn from v$database;

CURRENT_SCN -----------           0

> flashback database to scn 4250665   2  ;

Flashback complete.

> alter database open  read only;

Database altered.

> select table_name from dba_tables where table_name in ('T','I','N') and wner='SYS';

TABLE_NAME ------------------------------ T I

这时，如果我希望再回到N表创建之后的SCN，按照可以执行以下命令：

> recover database until scn 4250682; ORA-00277: illegal option to the UNTIL recovery flag SCN

> recover database ; ORA-00283: recovery session canceled due to errors ORA-01124: cannot recover data file 1 - file is in use or recovery ORA-01110: data file 1: '/u01/oradata/testdb/system01.dbf'

但报错了。因此比较稳妥的方法还是先 shutdown immediate startup mount 再flashback database to SCN xxxx；