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 - 6

　　[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.

，