[DGMGRL]Dgmgrl管理Dataguard(2)

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