使用 Oracle DGMGRL 进行切换

原创 CiciLee 2022-08-27

488

在今天的文章中，我们将学习使用 DGMGRL 进行切换。

我们也可以从 DGMGRL 进行切换。从 DGMGRL 切换更容易。它会自动执行我们使用 SQL 运行的所有命令。

那些需要从实例中关闭的会自行关闭。总之，处理步骤比SQL要少得多，也容易得多。

在 Oracle 12c 中，主数据库和备用数据库是否准备好进行切换可以通过 DGMGRL 中的验证命令来完成。但是 11g 没有这些命令，因为在 SQL 中，这些控制必须手动完成。

如果进行检查并且结果成功，则从 DGMGRL 的切换过程如下。

1.我们从DGMGRL查询Broker配置的状态。

DGMGRL> show configuration

Configuration - Broker_Configuraiton

  Protection Mode: MaxPerformance
  Databases:
    primary - Primary database
    standby - Physical standby database
    logical - Logical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS
复制

2.我们通过SQL命令检查主备数据库是否准备好进行切换。

3.我们从 DGMGRL 执行切换操作。

DGMGRL> switchover to 'standby';
Performing switchover NOW, please wait...
Operation requires a connection to instance "primary1" on database "standby"
Connecting to instance "primary1"...
Connected.
New primary database "standby" is opening...
Operation requires startup of instance "primary1" on database "primary"
Starting instance "primary1"...
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

Please complete the following steps to finish switchover:
        start up instance "primary1" of database "primary"

复制

出现此错误的原因是必须定义静态侦听器，以便代理可以在 SHUTDOWN 后启动数据库。由于这个定义不能完全正确，它不能自动启动。

我们有一个新的物理备用数据库启动，由于未正确设置静态侦听器，它无法自动启动。

[Primary-1] SQL> startup;
ORACLE instance started.

Total System Global Area 6480490496 bytes
Fixed Size                  2265384 bytes
Variable Size            1241517784 bytes
Database Buffers         5217714176 bytes
Redo Buffers               18993152 bytes
Database mounted.
Database opened.

复制

5.我们开始检查。

a.我们查询实例的状态。

[Physical-1] SQL> select status from gv$instance;

STATUS
------------
OPEN
OPEN

复制

[Primary-1] SQL> select status from gv$instance;

STATUS
------------
OPEN
OPEN

复制

[Logical-1] SQL> select status from gv$instance;

STATUS
------------
OPEN
OPEN

复制

b.切换后，我们质疑 EskiPhysical Standby 是否为主。

[Physical-1] SQL> select open_mode, database_role, protection_mode from gv$database;

OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE
-------------------- ---------------- --------------------
READ WRITE           PRIMARY          MAXIMUM PERFORMANCE
READ WRITE           PRIMARY          MAXIMUM PERFORMANCE

复制

[Primary-1] SQL> select open_mode, database_role, protection_mode from gv$database;

OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE
-------------------- ---------------- --------------------
READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE
READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE

复制

[Logical-1] SQL> select open_mode, database_role, protection_mode from gv$database;

OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE
-------------------- ---------------- --------------------
READ WRITE           LOGICAL STANDBY  MAXIMUM PERFORMANCE
READ WRITE           LOGICAL STANDBY  MAXIMUM PERFORMANCE

复制

c.我们查询重做应用进程是否已经启动。

[Primary-1] SQL> select recovery_mode from v$archive_dest_status where dest_id < 2;

RECOVERY_MODE
-----------------------
MANAGED REAL TIME APPLY

复制

[Logical-1] SQL> select recovery_mode from v$archive_dest_status where dest_id < 2;

RECOVERY_MODE
-----------------------
LOGICAL REAL TIME APPLY

复制

c’.我们检查备用数据库中是否存在重做传输和应用 LAG。

[Primary-1] SQL> set linesize 9000
[Primary-1] SQL> column name format a25
[Primary-1] SQL> column value format a20
[Primary-1] SQL> column time_computed format a25
[Primary-1] SQL> SELECT name, value, time_computed FROM v$dataguard_stats;

NAME                      VALUE                TIME_COMPUTED
------------------------- -------------------- -------------------------
transport lag             +00 00:00:00         01/28/2017 12:37:09
apply lag                 +00 00:00:00         01/28/2017 12:37:09
apply finish time         +00 00:00:00.000     01/28/2017 12:37:09
estimated startup time    23                   01/28/2017 12:37:09ö

复制

[Logical-1] SQL> set linesize 9000
[Logical-1] SQL> column name format a25
[Logical-1] SQL> column value format a20
[Logical-1] SQL> column time_computed format a25
[Logical-1] SQL> SELECT name, value, time_computed FROM v$dataguard_stats;

NAME                      VALUE                TIME_COMPUTED
------------------------- -------------------- -------------------------
transport lag             +00 00:00:00         01/28/2017 11:54:45
apply lag                 +00 00:00:00         01/28/2017 11:54:45
apply finish time         +00 00:00:00.000     01/28/2017 11:54:45
estimated startup time    22                   01/28/2017 11:54:45

复制

d.通过这个方法，我们也检查Log Switch操作是否正确，之后是否有Redo和Transport LAG。

[Physical-1] SQL> select max(sequence#),thread# from v$archived_log where first_time  >  to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS')  group by thread#;

MAX(SEQUENCE#)    THREAD#
-------------- ----------
            40          1
            41          2

复制

将 first_time 条件添加到查询中的 WHERE 条件的原因是因为数据库由于之前的测试而吃掉了 RESETLOGS，因此 SEQUENCE# 被重置。

[Primary-1] SQL> select max(sequence#),thread# from v$archived_log where first_time  >  to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS')  group by thread#;

MAX(SEQUENCE#)    THREAD#
-------------- ----------
            40          1
            41          2

复制

[Logical-1] SQL> SELECT max(sequence#),thread#,applied FROM dba_logstdby_log where first_time  >  to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS')  group by thread#, applied;

MAX(SEQUENCE#)    THREAD# APPLIED
-------------- ---------- --------
            40          1 YES
            41          2 YES

复制

[Physical-1] SQL> alter system switch logfile;

System altered.

[Physical-1] SQL> alter system switch logfile;

System altered.

[Physical-1] SQL> select max(sequence#),thread# from v$archived_log where first_time  >  to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS')  group by thread#;

MAX(SEQUENCE#)    THREAD#
-------------- ----------
            42          1
            42          2

复制

[Physical-1] SQL> alter system switch logfile;

System altered.

[Physical-1] SQL> alter system switch logfile;

System altered.

[Physical-1] SQL> select max(sequence#),thread# from v$archived_log where first_time  >  to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS')  group by thread#;

MAX(SEQUENCE#)    THREAD#
-------------- ----------
            42          1
            42          2

复制

[Logical-1] SQL> SELECT max(sequence#),thread#,applied FROM dba_logstdby_log where first_time  >  to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS')  group by thread#, applied;

MAX(SEQUENCE#)    THREAD# APPLIED
-------------- ---------- --------
            42          1 YES
            42          2 YES

复制

原文标题：Switchover Using DGMGRL
原文作者：Onur ARDAHANLI
原文地址：https://dbtut.com/index.php/2022/03/28/switchover-using-dgmgrl/

select 墨力翻译

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

使用 Oracle DGMGRL 进行切换

评论

相关阅读