1.通过SQLPLUS关闭Dataguard数据库，查看DGMGRL中备库的状态
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
--发现备库状态为disabled
DGMGRL> show configuration
Configuration - orcl
Protection Mode: MaxPerformance
Databases:
orcl - Primary database
dg - Physical standby database (disabled)
--发现状备库态为OFFLINE和SHUTDOWN
DGMGRL> show database dg
Database - dg
Role: PHYSICAL STANDBY
Intended State: OFFLINE
Transport Lag: (unknown)
Apply Lag: (unknown)
Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
dg
Database Status:
SHUTDOWN
2.启动备库Dataguard到mount状态
--发现备库已经正常
DGMGRL> show configuration
Configuration - orcl
Protection Mode: MaxPerformance
Databases:
orcl - Primary database
dg - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
--这时可以发现备库已经自动应用redo日志，不需要在sqlplus中输入alter database recover managed standby database ...类似的语句
DGMGRL> show database dg
Database - dg
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Apply Rate: 0 Byte/s
Real Time Query: OFF
Instance(s):
dg
Database Status:
SUCCESS
--通过如下语句也可以查看redo日志正在传输中
SQL> select process, status, thread#, sequence#, block#, blocks from v$managed_standby;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 1 95 16384 1610
RFS IDLE 0 0 0 0
RFS IDLE 1 96 272 1
MRP0 APPLYING_LOG 1 96 272 102400
7 rows selected
3.Oracle11g新特性read-time apply
在 Oracle11g之前的版本。物理备库处于日志应用状态时，是无法从备库读取数据的。如果想开库，需停止日志应用，备库可以开到read only状态。如果物理备库从read only状态回到日志应用状态，要先关掉物理备库，再将库启到mount状态，最后重新应用日志。
这样要从备库读数据，日志应用就必须停掉。无法实现边应用日志、边读取数据。11g 可以使用active standby，实现日志应用和查询同时进行。即Real-Time Apply + Real-Time Query.
--直接打开备库，成功
SQL> alter database open;
Database altered.
DGMGRL> show database dg
Database - dg
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Apply Rate: 0 Byte/s
Real Time Query: ON
Instance(s):
dg
Database Status:
SUCCESS
--mount阶段直接open后，可以发现redo日志一样在传输
SQL> select process, status, thread#, sequence#, block#, blocks from v$managed_standby;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 1 95 16384 1610
RFS IDLE 0 0 0 0
RFS IDLE 1 96 696 1
MRP0 APPLYING_LOG 1 96 696 102400
7 rows selected
4.通过一个实验来分析DGMGRL和SQLPLUS中分别修改参数是否能双向同步
--先通过DGMGRL中修改参数，观察SQLPLUS中情况
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
DGMGRL> show database verbose orcl StandbyFileManagement
StandbyFileManagement = 'AUTO'
--DGMGRL修改参数为manual
DGMGRL> edit database orcl set property StandbyFileManagement='manual';
Property "standbyfilemanagement" updated
DGMGRL> show database verbose orcl StandbyFileManagement
StandbyFileManagement = 'manual'
--可以发现修改在dgmgrl下修改参数standby_file_management，同时也修改了spfile文件里面的参数。
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string manual
--反向操作，在SQLPLUS中修改参数，观察DGMGRL中情况
SQL> alter system set standby_file_management=auto scope=both;
System altered.
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
--发现DGMGRL中并没有同步修改过来
DGMGRL> show database verbose orcl StandbyFileManagement
StandbyFileManagement = 'manual'
--继续测试，SQLPLUS修改成manual
SQL> alter system set standby_file_management=manual scope=both ;
System altered.
--DGMGRL中修改成auto
DGMGRL> edit database orcl set property StandbyFileManagement='auto';
Property "standbyfilemanagement" updated
DGMGRL> show database verbose orcl StandbyFileManagement
StandbyFileManagement = 'auto'
--再次查看SQLPLUS中时，发现已经修改成auto，再次证明上面结论
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string auto
--继续测试，修改SQLPLUS中为manual
SQL> alter system set standby_file_management=manual scope=both ;
System altered.
--查看主库状态时，会发现错误，发现参数冲突
DGMGRL> show database orcl
Database - orcl
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
orcl
Warning: ORA-16714: the value of property StandbyFileManagement is inconsistent with the database setting
Database Status:
WARNING
SQLPLUS中修改回来后，一切正常，就不再演示
总结：
可以说明一旦你使用DGMGRL来管理，你必须一直使用它来管理Dataguard，而不要使用SQLPLUS来修改相关参数，除非禁用它。这样会出现一些参数不一致的问题。
--其他命令的学习：
①.暂停和启动dg应用：
DGMGRL> show database dg
Database - dg
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: ON
Instance(s):
dg
Database Status:
SUCCESS
--暂停dg日志应用：
DGMGRL> edit database dg set state="APPLY-OFF";
Succeeded.
DGMGRL> show database dg
Database - dg
Role: PHYSICAL STANDBY
Intended State: APPLY-OFF
Transport Lag: 0 seconds
Apply Lag: 6 seconds
Real Time Query: OFF
Instance(s):
dg
Database Status:
SUCCESS
--启动dg日志应用：
DGMGRL> edit database dg set state="APPLY-ON";
Succeeded.
②.设置dg数据库只读。
DGMGRL> edit database dg set state='read-only';
Succeeded.
DGMGRL> show database dg
Database - dg
Role: PHYSICAL STANDBY
Intended State: READ-ONLY
Transport Lag: 0 seconds
Apply Lag: 36 seconds
Real Time Query: OFF
Instance(s):
dg
Database Status:
SUCCESS
--再修改回来edit database dg set state="APPLY-ON";
③.停止主库到从库的日志传送
DGMGRL> show database orcl
Database - orcl
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
orcl
Database Status:
SUCCESS
DGMGRL> edit database orcl set state='transport-off';
Succeeded.
DGMGRL> show database orcl
Database - orcl
Role: PRIMARY
Intended State: TRANSPORT-OFF
Instance(s):
orcl
Database Status:
SUCCESS
DGMGRL> edit database orcl set state='transport-on';
Succeeded.
DGMGRL> show database orcl
Database - orcl
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
orcl
Database Status:
SUCCESS
④.将主库离线:
DGMGRL> edit database orcl set state='offline';
Operation requires shutdown of instance "orcl" on database "orcl"
Shutting down instance "orcl"...
ORA-01031: insufficient privileges
Warning: You are no longer connected to ORACLE.
Please complete the following steps to finish the operation:
shut down instance "orcl"of database "orcl"
--好像现在并不能关闭数据库。也许要使用shutdown命令。我的测试这个命令无用。
--因为dg还在接收应用日志。即使使用如下命令看：
DGMGRL> show database orcl
Database - orcl
Role: PRIMARY
Intended State: OFFLINE
Instance(s):
orcl
Database Status:
SHUTDOWN
--但是我看到dg还在接收应用日志。
DGMGRL> help shutdown
Shuts down a currently running Oracle database instance
Syntax:
SHUTDOWN [NORMAL | IMMEDIATE | ABORT];
DGMGRL> edit database orcl set state='online';
Succeeded.
DGMGRL> show database orcl
Database - orcl
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
orcl
Database Status:
SUCCESS
⑤.其他命令
--禁用配置
disable configuration
--禁用某个备用库
disable database 'orcl';
--从配置中删除备用库
remove database 'orcl'
--删除配置
remove configuration
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29812844/viewspace-1988861/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/29812844/viewspace-1988861/
扫一扫
6930
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
