[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>
記錄當(dāng)前的scn(oracle11gR2可以在open狀態(tài)flashback on)
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1395475
截?cái)鄑_zhongwc表
SQL> truncate table t_zhongwc;
Table truncated.
SQL> select count(*) from t_zhongwc;
COUNT(*)
----------
0
開(kāi)始執(zhí)行閃回,關(guān)閉所有實(shí)例，啟動(dòng)zhongwc1到mount,閃回到scn 1395475,以只讀模式打開(kāi)驗(yàn)證
[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打開(kāi)數(shù)據(jù)庫(kù),啟動(dòng)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
也可以基于時(shí)間戳進(jìn)行閃回?cái)?shù)據(jù)庫(kù)
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.