oracle11g pysical standby开启临时读写

1、确定和检查相关信息

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED
数据库处于mount状态下，是物理standby
检查当前模式是否在闪回的条件下
SQL> show parameter db_recovery_file_dest;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0

SQL> select FLASHBACK_ON from v$database;

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

通过检查，standby闪回没有开启，在进行切换时需要开启闪回

2、对standby数据库进行操作
--取消日志在standby端的应用
SQL> alter database recover managed standby database cancel;

Database altered.

--设置闪回区域和闪回大小
SQL> alter system set db_recovery_file_dest_size = 40g;

System altered.

SQL> alter system set db_recovery_file_dest = '/u01/db_recover';

System altered.

另外，创建闪回目录
mkdir /u01/db_recover
chown -R oracle:oinstall /u01/db_recover

--切换到snap database状态
SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 8551575552 bytes
Fixed Size                  2270360 bytes
Variable Size            2147486568 bytes
Database Buffers         6392119296 bytes
Redo Buffers                9699328 bytes
Database mounted.

SQL> alter database convert to snapshot standby;
Database altered.

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
SNAPSHOT STANDBY MOUNTED

--打开数据库

SQL> alter database open;
Database altered.

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
SNAPSHOT STANDBY READ WRITE
可以看到数据库已经处理可读写的状态了

4、对snap database进行读写测试

--切换日志     
SQL> alter system switch logfile;

System altered.

--建表测试

SQL> create table mytest as select * from dba_tables;

Table created.

SQL> create table mytest1 as select * from dba_tables;

Table created.

SQL> select count(*) from dba_tables;

  COUNT(*)
----------
      4622

SQL> drop table mytest purge;

Table dropped.

5、测试完成后，还原到physical standby

--关闭数据库并切换到mount状态
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 8551575552 bytes
Fixed Size                  2270360 bytes
Variable Size            2147486568 bytes
Database Buffers         6392119296 bytes
Redo Buffers                9699328 bytes
Database mounted.

--切换到physical standby状态
SQL> alter database convert to physical standby;

Database altered.

--关闭闪回
SQL> alter database flashback off;

Database altered.

--重新开启数据库physical standby模式
SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 8551575552 bytes
Fixed Size                  2270360 bytes
Variable Size            2147486568 bytes
Database Buffers         6392119296 bytes
Redo Buffers                9699328 bytes

SQL> alter database mount standby database;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION NODELAY;
Database altered.

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY

--检查刚才的表
SQL> select count(*) from mytest1;
select count(*) from mytest1
                     *
ERROR at line 1:
ORA-00942: table or view does not exist
刚才创建的mytest1表已经不存在了

6、说明
需要注意的点：
--10g中创建的还原点必须在闪回后进行删除，否则闪回恢复区可能会被撑满，导致DB Hang住。11g中不用担心这个问题，在重新转换为physical standby时会自动进行还原点删除。
--在进行转换时，需要设置闪回区
--物理standby是最高保护模式(maximum protection),是不能转换为snapshot standby的.
--物理standby使用了standby redo log,在create restore point后,要alter system switch logfile，以保证还原点的scn在物理standby库上是归档的,不然可能无法成功闪回到还原点.
--物理standby在切换为快照standby后,如果间隔很长时间,primary数据库产生的大量的重做日志,这样可以在转换为物理standby后,通过对primary数据库的增量备份并recover到物理standby,来加快物理standby的还原速度。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29371470/viewspace-2098425/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29371470/viewspace-2098425/