Oracle DG 主备切换

最新推荐文章于 2024-09-11 14:32:14 发布

cituo1397

最新推荐文章于 2024-09-11 14:32:14 发布

阅读量313

点赞数

文章标签：数据库运维

谷遇到这种问题，一般是主库异常宕机，一时半会没有办法恢复，且没有有效的监控配置，来将备库自动提升为主库。否则，就是显得蛋疼。
在maximum protection和maximum availability两种模式

点击(此处)折叠或打开

在虚拟机环境下模式主库宕机，主备手动切换的操作如下：
1：主库宕机，所以先在备库操作
查看归档日志文件是否已经完全应用，且没有GAP发生

点击(此处)折叠或打开

SQL> select status,sequence#,thread# from v$managed_standby;

STATUS          SEQUENCE#    THREAD#
------------ ---------- ----------
CONNECTED          0    0
CLOSING          80    1
CONNECTED          0    0
CONNECTED          0    0
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
64 YES
65 YES
66 YES
67 YES
68 YES
69 YES
70 YES
71 YES
72 YES
73 YES
74 YES
SEQUENCE# APPLIED
---------- ---------
75 YES
76 YES
77 YES
79 YES
80 YES
78 YES
17 rows selected.
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

no rows selected
#如果查出又值，为保证数据完整性，则需要从主库拷贝到备库，并且注册。例如
SQL> ALTER DATABASE REGISTER or replace PHYSICAL LOGFILE 'filespec1';

以上应用日志全是 YES 状态则表明传过来的日志已经全部应用完成，且不存在GAP。

-------可选----然后停止 standby 自动管理，添加在线日志文件组（成员）、删除无效日志组（成员）

点击(此处)折叠或打开

SQL> show parameter standby
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string ?/dbs/arch
standby_file_management string AUTO
SQL> alter system set standby_file_management=MANUAL;
System altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE    MEMBER                          IS_
---------- ------- ------- -------------------------------------------------- ---
   1       ONLINE /data/oracle/orcl/redo01.dbf               NO
   2       ONLINE /data/oracle/orcl/redo02.dbf               NO
   3       ONLINE /data/oracle/orcl/redo03.dbf               NO
   4       STANDBY /data/oracle/orcl/standby01.log              NO
//日志组1、2、3是无效的，所以需要添加日志组文件（组）
SQL> alter database add logfile member '/data/oracle/orcl/redo04.dbf' to group 1;

Database altered.

SQL> alter database add logfile member '/data/oracle/orcl/redo05.dbf' to group 2;

Database altered.

SQL> alter database add logfile member '/data/oracle/orcl/redo06.dbf' to group 3;

Database altered.
SQL> alter database add logfile member '/data/oracle/orcl/redo06.dbf' to group 3;

Database altered.
----可选添加

SQL> alter database add standby logfile 5 ('/data/oracle/orcl/standby06.dbf',/data/oracle/orcl/standby06_b.dbf') size 100m;

Database altered.

备库切换：

点击(此处)折叠或打开

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;
Database altered.
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Database altered.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 521936896 bytes
Fixed Size            2214936 bytes
Variable Size          314573800 bytes
Database Buffers      201326592 bytes
Redo Buffers            3821568 bytes
Database mounted.
Database opened.
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE