[20140420]使用dgmgrl管理dataguard(9).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/
-- 我的测试环境：primary的tnsnames别名test，standby主机的tnsnames别名testdg。数据库版本：
-- 开始学习使用dataguard完成Failover。首先理解Switchover和Failover两个基本概念，摘自：
PacktPub.Oracle.Data.Guard.11gR2.Administration.Beginners.Guide.Jun.2013.pdf
Switchover P43
In a basic Data Guard confguraton with one primary and one standby database, a switchover operaton changes the roles of
these databases, and so the directon of the redo shipping. In a correctly designed confguraton, archived log shipping in
the opposite directon starts immediately afer switchover and clients do not need to change their connecton descriptons
in order to connect the new primary database.
If there is more than one standby database in a Data Guard confguraton, it's possible to perform switchover between the
primary and any of the standby databases. Afer the switchover, the new primary database can contnue to send redo to all
of the standby databases in the configuraton.
Regardless of the confguraton of Data Guard, a switchover operaton always guarantees zero data loss. This brings high
reliability to switchover and thus it's widely used for planned maintenance operatons, such as hardware or operatng
system upgrades, database sofware rolling upgrade, and other infrastructure maintenances. Switchover reduces the
downtime for these maintenance operatons by a signifcant amount of time.
Failover P43
Failover is the operaton of convertng a standby database to a primary database, because of a failure in the original
primary database. If the fashback database is disabled on the primary database, failover is an operaton with no return.
In other words, we have to fashback the failed primary database to a state before failover in order to re-establish the
confguraton. Without fashback, Data Guard confguraton needs to be built from scratch.
A manual database failover may be performed in the case of failure with the initatve of the database owner. However,
this will require extra outage for the decision making. If fast-start failover is used, which is a 10g release 2
feature, the failover operaton will perform automatcally.
--这次测试Failover.Failover主要应用的场所是比如主数据库由于电源或者硬盘损坏,导致数据库无法启动,这时需要备用库转化为主库
--使用.许多情况下,会存在一定的数据丢失.
--我的模拟测试如下:
--在11G以前,failover后,原来的主库需要重建.在11G后可以使用reinstate来重建备用库,这个操作仅仅需要打开flashback功能.
1.为了测试的方便,主备数据库都先打开flashback功能:
SYS@test> alter database flashback on;
Database altered.
SYS@test> select database_role,open_mode,protection_mode,force_logging,FLASHBACK_ON from v$database;
DATABASE_ROLE OPEN_MODE PROTECTION_MODE FOR FLASHBACK_ON
---------------- -------------------- -------------------- --- ------------------
PRIMARY READ WRITE MAXIMUM PERFORMANCE YES YES
SYS@testdg> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active
--备用库报错.
DGMGRL> edit database testdg set state="apply-off";
Succeeded.
SYS@testdg> alter database flashback on;
Database altered.
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 MOUNTED MAXIMUM PERFORMANCE YES YES
DGMGRL> edit database testdg set state="apply-on";
Succeeded.
-- 11G要打开flashback的功能,已经不需要进入到mount状态,随时都可以切换flashback功能.
-- 备用库要启用flashback功能,首先要停止redo-apply.切换后在打开redo-apply.
2.在主库做一些dml操作:
SYS@test> select * from scott.dept1 where deptno=60 ;
DEPTNO DNAME LOC
---------- -------------- -------------
60 MMMM XXXXXX
SYS@test> update scott.dept1 set loc='AAAAAA' where deptno=60;
1 row updated.
SYS@test> commit ;
Commit complete.
--关闭主数据库,模拟机器down或者损坏.
SYS@test> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
DGMGRL> show configuration
Configuration - study
Protection Mode: MaxPerformance
Databases:
test - Primary database
testdg - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
ORA-01034: ORACLE not available
ORA-16625: cannot reach database "test"
DGM-17017: unable to determine configuration status
DGMGRL> connect sys/btbtms@testdg
Connected.
DGMGRL> failover to testdg;
Performing failover NOW, please wait...
Failover succeeded, new primary is "testdg"
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
---------------- -------------------- -------------------- --- ------------------
PRIMARY READ WRITE MAXIMUM PERFORMANCE YES YES
--可以发现testdg变成了primary数据库.
3.很明显如果现在正常开启test数据库,会出现什么情况呢?
SYS@test> startup mount
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 1006636496 bytes
Database Buffers 587202560 bytes
Redo Buffers 7344128 bytes
Database mounted.
SYS@test> select database_role,open_mode,protection_mode,force_logging,FLASHBACK_ON from v$database;
DATABASE_ROLE OPEN_MODE PROTECTION_MODE FOR FLASHBACK_ON
---------------- -------------------- -------------------- --- ------------------
PRIMARY MOUNTED MAXIMUM PERFORMANCE YES YES
--很明显现在test也是primary.
DGMGRL> show configuration
Configuration - study
Protection Mode: MaxPerformance
Databases:
testdg - Primary database
test - Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
--测试首先执行alter database open read only看看.
SYS@test> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16649: possible failover to another database prevents this database from being opened
SYS@test> alter database open;
alter database open
*
ERROR at line 1:
ORA-16649: possible failover to another database prevents this database from being opened
SYS@test> host oerr ora 16649
16649, 0000, "possible failover to another database prevents this database from being opened"
// *Cause: An attempt to open the primary database was made either after
// a failover occurred, or when it was likely to have occurred as
// the result of the primary being isolated from the fast-start
// failover target standby database and from the fast-start failover
// observer.
// *Action: Check if a failover did occur. If fast-start failover is enabled,
// and a failover did not occur, ensure that connectivity exists
// between the primary database and either the observer or the
// target standby database. Then, try opening the database again.
--很明显不能正常打开,估计修改参数dg_broker_start=false,应该可以,不测试了.
DGMGRL> reinstate database test;
Reinstating database "test", please wait...
Operation requires shutdown of instance "test" on database "test"
Shutting down instance "test"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "test" on database "test"
Starting instance "test"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "test" ...
Reinstatement of database "test" succeeded
DGMGRL> show configuration
Configuration - study
Protection Mode: MaxPerformance
Databases:
testdg - Primary database
test - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
--可以发现test依旧能做备用库.
4.切换回来,继续测试.
DGMGRL> switchover to test;
Performing switchover NOW, please wait...
New primary database "test" is opening...
Operation requires shutdown of instance "testdg" on database "testdg"
Shutting down instance "testdg"...
ORACLE instance shut down.
Operation requires startup of instance "testdg" on database "testdg"
Starting instance "testdg"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "test"
--这次模拟一些数据丢失的情况.
DGMGRL> edit database testdg set state="apply-off";
Succeeded.
在主库做一些修改.
SYS@test> select * from scott.dept1 where deptno=60;
DEPTNO DNAME LOC
---------- -------------- -------------
60 MMMM AAAAAA
SYS@test> update scott.dept1 set loc='BBBBBB' where deptno=60;
1 row updated.
SYS@test> commit ;
Commit complete.
--注意修改的相关redo信息并没有传送到备用机,更没有应用redo.
DGMGRL> show database testdg;
Database - testdg
Role: PHYSICAL STANDBY
Intended State: APPLY-OFF
Transport Lag: 0 seconds
Apply Lag: 1 minute 4 seconds
Real Time Query: OFF
Instance(s):
testdg
Database Status:
SUCCESS
--出现了Apply Lag:1 minute 4 seconds.
SYS@test> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
DGMGRL> connect sys/btbtms@testdg
Connected.
DGMGRL> failover to testdg;
Performing failover NOW, please wait...
Failover succeeded, new primary is "testdg"
--在testdg上执行.
RMAN> list incarnation ;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 TEST 2071943378 PARENT 3011113647 2012-11-05 11:58:00
2 2 TEST 2071943378 PARENT 3269610631 2014-04-20 21:11:14
3 3 TEST 2071943378 CURRENT 3269632486 2014-04-20 21:16:26
-- 可以发现failover实际上会出现一次resetlog.
SYS@testdg> select * from scott.dept1 where deptno=60;
DEPTNO DNAME LOC
---------- -------------- -------------
60 MMMM BBBBBB
--?? 奇怪,修改也应用过来,why? 估计仅仅停止应用,但是并没有阻塞传输redo.重新在做一次.基础没有打好???
SYS@test> startup mount
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 1006636496 bytes
Database Buffers 587202560 bytes
Redo Buffers 7344128 bytes
Database mounted.
DGMGRL> reinstate database test;
Reinstating database "test", please wait...
Operation requires shutdown of instance "test" on database "test"
Shutting down instance "test"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "test" on database "test"
Starting instance "test"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "test" ...
Reinstatement of database "test" succeeded
reinstate database test;
4.继续测试:
DGMGRL> show configuration
Configuration - study
Protection Mode: MaxPerformance
Databases:
testdg - Primary database
test - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> edit database testdg set state="TRANSPORT-OFF";
Succeeded.
--在testdg上做一些dml操作看看.
SYS@testdg> select * from scott.dept1 where deptno=60;
DEPTNO DNAME LOC
---------- -------------- -------------
60 MMMM BBBBBB
SYS@testdg> update SCOTT.dept1 set loc='CCCCCC' where deptno=60;
1 row updated.
SYS@testdg> commit ;
Commit complete.
SYS@testdg> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
DGMGRL> connect sys/btbtms@test
Connected.
DGMGRL> failover to test
Performing failover NOW, please wait...
Failover succeeded, new primary is "test"
SYS@test> select database_role,open_mode,protection_mode,force_logging,FLASHBACK_ON from v$database;
DATABASE_ROLE OPEN_MODE PROTECTION_MODE FOR FLASHBACK_ON
---------------- -------------------- -------------------- --- ------------------
PRIMARY READ WRITE MAXIMUM PERFORMANCE YES YES
SYS@test> select * from scott.dept1 where deptno=60;
DEPTNO DNAME LOC
---------- -------------- -------------
60 MMMM BBBBBB
--可以发现修改的信息丢失,总之正常情况下,做failover总会存在一些丢失,多少罢了,估计在生产系统要仔细评估.
--恢复原状:
SYS@testdg> startup open read only
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 939527632 bytes
Database Buffers 654311424 bytes
Redo Buffers 7344128 bytes
Database mounted.
ORA-16649: possible failover to another database prevents this database from being opened
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
---------------- -------------------- -------------------- --- ------------------
PRIMARY MOUNTED MAXIMUM PERFORMANCE YES YES
DGMGRL> reinstate database testdg;
Reinstating database "testdg", please wait...
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.
Continuing to reinstate database "testdg" ...
Reinstatement of database "testdg" succeeded
DGMGRL> show configuration
Configuration - study
Protection Mode: MaxPerformance
Databases:
test - Primary database
testdg - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
总结:
总之做failover要小心,总会有一些信息丢失.另外failover还可以加入IMMEDIATE选项,立即切换.
另外这样做如果主库如果能开启的情况下,要打开flashback功能,才能做reinstate操作,视乎这样情况在生产系统不多见.
DGMGRL> help failover
Changes a standby database to be the primary database
Syntax:
FAILOVER TO [IMMEDIATE];