[20140422]使用dgmgrl管理dataguard(13).txt
参考链接：
http://blog.itpub.net/267265/viewspace-1142649/
http://blog.itpub.net/267265/viewspace-1143027/
http://blog.itpub.net/267265/viewspace-1143058/
http://blog.itpub.net/267265/viewspace-1143126/
http://blog.itpub.net/267265/viewspace-1143480/
http://blog.itpub.net/267265/viewspace-1144742/
http://blog.itpub.net/267265/viewspace-1145573/
http://blog.itpub.net/267265/viewspace-1145697/
http://blog.itpub.net/267265/viewspace-1145727/
http://blog.itpub.net/267265/viewspace-1146558/
http://blog.itpub.net/267265/viewspace-1146575/
http://blog.itpub.net/267265/viewspace-1147481/
-- 我的测试环境：primary的tnsnames别名test，standby主机的tnsnames别名testdg。数据库版本：11GR2.
-- 今天做一个SNAPSHOT STANDBY的测试.
--有时候开发要做一些测试,测试环境要比较真实的反应生产系统的情况,使用备用库比较合适,但是11G下虽然打开apply read only模式,
--但是往往还包含一些dml语句,必须打开读写模式.这样要求转换备用库到SNAPSHOT STANDBY.完成后在转换回来.
DGMGRL> show configuration
Configuration - study
Protection Mode: MaxPerformance
Databases:
test - Primary database
testdg - (*) Physical standby database
Fast-Start Failover: ENABLED
Configuration Status:
SUCCESS
SYS@testdg> select database_role,open_mode,protection_mode,force_logging,FLASHBACK_ON from v$database;
DATABASE_ROLE OPEN_MODE PROTECTION_MODE FOR FLASHBACK_ON
---------------- -------------------- -------------------- --- ------------------
PHYSICAL STANDBY READ ONLY WITH APPLY MAXIMUM PERFORMANCE YES YES
--使用sqlplus要执行如下:SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;这个测试忽略,使用dgmgrl看看.
DGMGRL> convert database testdg to SNAPSHOT STANDBY;
Converting database "testdg" to a Snapshot Standby database, please wait...
Error: ORA-16668: operation cannot be performed on the fast-start failover target standby database
Failed.
Failed to convert database "testdg"
--不能打开fast-start failover.关闭fast-start failover,继续测试.
DGMGRL> disable fast_start failover ;
Disabled.
DGMGRL> show configuration
Configuration - study
Protection Mode: MaxPerformance
Databases:
test - Primary database
testdg - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> convert database testdg to SNAPSHOT STANDBY;
Converting database "testdg" to a Snapshot Standby database, please wait...
Database "testdg" converted successfully
--OK完成.
SYS@testdg> Select database_role,open_mode,protection_mode,force_logging,FLASHBACK_ON,resetlogs_change#,prior_resetlogs_change# from v$database;
DATABASE_ROLE OPEN_MODE PROTECTION_MODE FOR FLASHBACK_ON RESETLOGS_CHANGE# PRIOR_RESETLOGS_CHANGE#
---------------- -------------------- -------------------- --- ------------------ ----------------- -----------------------
SNAPSHOT STANDBY READ WRITE MAXIMUM PERFORMANCE YES YES 3269794846 3269769399
--已经打开了读写模式.
SYS@testdg> select * from v$restore_point;
SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME RESTORE_PO PRE NAME
---------- --------------------- --- ------------ ------------------------------ ---------- --- --------------------------------------------------
3269794844 8 YES 52428800 2014-04-23 09:46:06.000000000 YES SNAPSHOT_STANDBY_REQUIRED_04/23/2014 09:46:06
--数据库记录了转成SNAPSHOT STANDBY的scn.做一些DML操作.
SCOTT@testdg> select * from scott.dept1 where deptno=60;
DEPTNO DNAME LOC
---------- -------------- -------------
60 MMMM DDDDDx
SCOTT@testdg> update SCOTT.dept1 set loc='EEEEEE' where deptno=60;
1 row updated.
SCOTT@testdg> commit ;
Commit complete.
--转换回来.使用sqlplus的命令是alter database convert to physical standby;这个测试忽略,使用dgmgrl看看.
DGMGRL> show configuration
Configuration - study
Protection Mode: MaxPerformance
Databases:
test - Primary database
testdg - Snapshot standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> convert database testdg to physical STANDBY;
Converting database "testdg" to a Physical Standby database, please wait...
Operation requires shutdown of instance "testdg" on database "testdg"
Shutting down instance "testdg"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "testdg" on database "testdg"
Starting instance "testdg"...
ORACLE instance started.
Database mounted.
Continuing to convert database "testdg" ...
Operation requires shutdown of instance "testdg" on database "testdg"
Shutting down instance "testdg"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "testdg" on database "testdg"
Starting instance "testdg"...
ORACLE instance started.
Database mounted.
Database "testdg" converted successfully
DGMGRL> show configuration
Configuration - study
Protection Mode: MaxPerformance
Databases:
test - Primary database
testdg - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
--查看testdg的情况
SCOTT@testdg> Select database_role,open_mode,protection_mode,force_logging,FLASHBACK_ON,resetlogs_change#,prior_resetlogs_change# from v$database;
DATABASE_ROLE OPEN_MODE PROTECTION_MODE FOR FLASHBACK_ON RESETLOGS_CHANGE# PRIOR_RESETLOGS_CHANGE#
---------------- -------------------- -------------------- --- ------------------ ----------------- -----------------------
PHYSICAL STANDBY READ ONLY WITH APPLY MAXIMUM PERFORMANCE YES YES 3269769399 3269705600
SCOTT@testdg> select * from scott.dept1 where deptno=60;
DEPTNO DNAME LOC
---------- -------------- -------------
60 MMMM DDDDDx
--可以发现dml的信息已经还原.
总结:
--做SNAPSHOT STANDBY,无论是sqlplus还是dgmgrl都是很简单的.命令如下:
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
alter database convert to physical standby;
convert database testdg to SNAPSHOT STANDBY;
convert database testdg to physical STANDBY;