临时生成oracle快照,oracle 11g dataguard 使用快照实现临时读写

racle 11g dataguard已经可以临时打开可读写备库，打开后可以进行更改，做压力测试等工作，完成后不用进行重新恢复备库直接可以将角色更改为standby继续应用管理日志。

理备库的初始状态是正在应用日志，我们执行下面的命令生成快照数据库，

alter database convert to snapshot standby

*

ERROR at line 1:

ORA-38784: Cannot create restore point 'SNAPSHOT_STANDBY_REQUIRED_11/23/201210:49:53'.

ORA-01153: an incompatible media recovery is active

看到了报错信息，不过从报错信息中，我们也能看到，实际上所谓的快照数据库，Oracle是利用了闪回数据库的功能，首先他要生成一个restore point，

也就是将来闪回数据库的起点。

正确的操作是停止日志应用，然后把数据库转换成快照数据库

SQL> recover managed standby database cancel;

Media recovery complete.

SQL> alter database convert to snapshot standby;

Database altered.

SQL> select status from v$instance;

STATUS

MOUNTED

SQL> alter database open;

Database altered.

查看数据库状态，是以读写的方式打开的，并且数据库是快照数据库的状态

SQL> select name,open_mode,database_role,flashback_on from v$database;

NAME OPEN_MODE DATABASE_ROLE FLASHBACK_ON

---------

DB01 READ WRITE SNAPSHOT STANDBY RESTORE POINT ONLY

接下来就可以做操作了，如果有压力测试需求在这个状态下可以进行

SQL> create table d(x int,y char(3));

Table created.

SQL> insert into d values(100,'t');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

no rows selected

SQL> select * from d;

X Y

---

100 t

如果要改回原来的物理备库状态，要关闭数据库，在mount状态下执行命令

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 634679296 bytes

Fixed Size 2231032 bytes

Variable Size 411043080 bytes

Database Buffers 218103808 bytes

Redo Buffers 3301376 bytes

Database mounted.

SQL> alter database convert to physical standby;

Database altered.

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

ORACLE instance started.

Total System Global Area 634679296 bytes

Fixed Size 2231032 bytes

Variable Size 411043080 bytes

Database Buffers 218103808 bytes

Redo Buffers 3301376 bytes

Database mounted.

Database opened.

数据库状态已经是物理备库，当然刚才做的操作也看不到了，比如说D表

SQL>select name,open_mode,database_role,flashback_on from v$database;

NAME OPEN_MODE DATABASE_ROLE FLASHBACK_ON

---------

DB01 READ ONLY PHYSICAL STANDBY NO

SQL> select * from d;

select * from d

*

ERROR at line 1:

ORA-00942: table or view does not exist

开启日志应用模式

SQL> recover managed standby database disconnect;

Media recovery complete.

SQL> select name,open_mode,database_role,flashback_on from v$database;

NAME OPEN_MODE DATABASE_ROLE FLASHBACK_ON

---------

DB01 READ ONLY WITH APPLY PHYSICAL STANDBY NO

在主库上：

SQL> create table d(x number);

Table created.

SQL> insert into d values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

回到备库：

SQL> select * from d;

X

1