ITPub博客

首页 > 数据库 > Oracle > [DGMGRL]Dgmgrl管理Dataguard(2)

[DGMGRL]Dgmgrl管理Dataguard(2)

原创 Oracle 作者:梓沐 时间:2016-02-15 10:21:49 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
擅长PLS/QL开发,SQL调优和改写,数据库设计

注册时间:2014-08-18

  • 博文量
    161
  • 访问量
    1086235