實驗-閃回資料庫

實驗-閃回資料庫

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.啟動資料庫閃回日誌
sys@TESTDB> alter database flashback on;

Database altered.

1.連線到資料庫，建立確認恢復點，並檢視當前SCN
SQL> conn sys/sys@testdb as sysdba
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as SYS

sys@TESTDB> 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狀態：
sys@TESTDB> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@TESTDB> 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.
sys@TESTDB>

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表後的結果。可以進行以下操作：

sys@TESTDB> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@TESTDB> 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.
sys@TESTDB> select current_scn from v$database;

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

sys@TESTDB> flashback database to scn 4250665
  2  ;

Flashback complete.

sys@TESTDB> alter database open  read only;

Database altered.

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

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

這時，如果我希望再回到N表建立之後的SCN，按照可以執行以下命令：

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

sys@TESTDB> 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；