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

更多信息請查看IT技術專欄