Oracle RAC 执行闪回数据库操作

[oracle@zhongwc1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 23 17:02:01 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> set lines 200
SQL> select instance_name,host_name from v$instance;

INSTANCE_NAME	 HOST_NAME
---------------- ----------------------------------------------------------------
zhongwc1	 zhongwc1.oracle.com

SQL> select instance_name,host_name from gv$instance;

INSTANCE_NAME	 HOST_NAME
---------------- ----------------------------------------------------------------
zhongwc1	 zhongwc1.oracle.com
zhongwc2	 zhongwc2.oracle.com

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> show release
release 1102000300
SQL> alter database flashback on;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> conn zwc
Enter password: 
Connected.
SQL> create table t_zhongwc as select * from dba_objects;

Table created.

SQL> select count(*) from t_zhongwc;

  COUNT(*)
----------
     75289

SQL> 

记录当前的scn(oracle11gR2可以在open状态flashback on)

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1395475

截断t_zhongwc表

SQL> truncate table t_zhongwc;

Table truncated.

SQL> select count(*) from t_zhongwc;

  COUNT(*)
----------
	 0

开始执行闪回,关闭所有实例，启动zhongwc1到mount,闪回到scn 1395475,以只读模式打开验证

[oracle@zhongwc1 ~]$ srvctl stop database -d zhongwc
[oracle@zhongwc1 ~]$ su - grid -c "crsctl stat res -t"
Password: 
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.CRS.dg
               ONLINE  ONLINE       zhongwc1                                     
               ONLINE  ONLINE       zhongwc2                                     
ora.DATADG.dg
               ONLINE  ONLINE       zhongwc1                                     
               ONLINE  ONLINE       zhongwc2                                     
ora.FRADG.dg
               ONLINE  ONLINE       zhongwc1                                     
               ONLINE  ONLINE       zhongwc2                                     
ora.LISTENER.lsnr
               ONLINE  ONLINE       zhongwc1                                     
               ONLINE  ONLINE       zhongwc2                                     
ora.asm
               ONLINE  ONLINE       zhongwc1                 Started             
               ONLINE  ONLINE       zhongwc2                 Started             
ora.gsd
               OFFLINE OFFLINE      zhongwc1                                     
               OFFLINE OFFLINE      zhongwc2                                     
ora.net1.network
               ONLINE  ONLINE       zhongwc1                                     
               ONLINE  ONLINE       zhongwc2                                     
ora.ons
               ONLINE  ONLINE       zhongwc1                                     
               ONLINE  ONLINE       zhongwc2                                     
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       zhongwc1                                     
ora.cvu
      1        ONLINE  ONLINE       zhongwc1                                     
ora.oc4j
      1        ONLINE  ONLINE       zhongwc1                                     
ora.scan1.vip
      1        ONLINE  ONLINE       zhongwc1                                     
ora.zhongwc.db
      1        OFFLINE OFFLINE                               Instance Shutdown   
      2        OFFLINE OFFLINE                               Instance Shutdown   
ora.zhongwc1.vip
      1        ONLINE  ONLINE       zhongwc1                                     
ora.zhongwc2.vip
      1        ONLINE  ONLINE       zhongwc2                                     
[oracle@zhongwc1 ~]$ 

[oracle@zhongwc1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 23 17:22:40 2013

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size		    2227984 bytes
Variable Size		  889192688 bytes
Database Buffers	  369098752 bytes
Redo Buffers		    8847360 bytes
Database mounted.
SQL> flashback database to scn 1395475;

Flashback complete.

SQL> alter database open read only;

Database altered.

SQL> conn zwc
Enter password: 
Connected.
SQL> select count(*) from t_zhongwc;

  COUNT(*)
----------
     75289

resetlogs打开数据库,启动zhongwc2

SQL> shutdown immediate
ORA-01031: insufficient privileges
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size		    2227984 bytes
Variable Size		  889192688 bytes
Database Buffers	  369098752 bytes
Redo Buffers		    8847360 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.

SQL> !srvctl start instance -d zhongwc -i zhongwc2

SQL> col host_name format a20
SQL> set lines 300
SQL> select INSTANCE_NAME,HOST_NAME,VERSION,STARTUP_TIME,STATUS,ACTIVE_STATE,INSTANCE_ROLE,DATABASE_STATUS from gv$INSTANCE;

INSTANCE_NAME	 HOST_NAME	      VERSION		STARTUP_TIME		STATUS	     ACTIVE_ST INSTANCE_ROLE	  DATABASE_STATUS
---------------- -------------------- ----------------- ----------------------- ------------ --------- ------------------ -----------------
zhongwc1	 zhongwc1.oracle.com  11.2.0.3.0	23-JAN-2013 17:30:37	OPEN	     NORMAL    PRIMARY_INSTANCE   ACTIVE
zhongwc2	 zhongwc2.oracle.com  11.2.0.3.0	23-JAN-2013 17:34:30	OPEN	     NORMAL    PRIMARY_INSTANCE   ACTIVE

也可以基于时间戳进行闪回数据库

SQL> select to_char(scn_to_timestamp(1395475),'yyyy-mm-dd hh24:mi:ss') time_stamp from dual;

TIME_STAMP
-------------------
2013-01-23 17:14:05

SQL> flashback database to timestamp to_timestamp('2013-01-23 17:14:05','yyyy-mm-dd hh24:mi:ss');
flashback database to timestamp to_timestamp('2013-01-23 17:14:05','yyyy-mm-dd hh24:mi:ss')
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.