Oracle 11g 一主多備切換方案
最近公司需要把核心兩節點RAC從虛擬化環境遷移至物理機,考慮停機時間要求比較短並且還有兩個備庫存在,決定使用RAC到RAC的備庫使用switch over的方式進行遷移。原RAC主庫還存在一個單例項的物理standby只讀庫和邏輯standby報表庫,切換後需要對這兩個庫進行處理,可以接收新的RAC主庫的redo。此方案和使用RMAN全備進行異機恢復遷移方式對比,優點是停機時間短,並且遷移後無需重新配置原來的兩個單例項備庫。
一、 環境資訊
當前一主多備架構資料庫環境資訊如下
:
二、 一主多備下的切換步驟
2.1 檢查當前 RAC 主庫是否可以被切換成備用角色
[oracle@ngpdb01 trace]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 7 09:50:57 2020 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS -------------------- SESSIONS ACTIVE
TO STANDBY 或者 SESSIONS ACTIVE 狀態下,主庫可以切換成備庫角色
2.2 原 RAC 主庫啟動 switchover
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN; Database altered.
2.3 shutdown
原
RAC
主庫並且啟動到
mount
狀態
[oracle@ngpdb01 trace]$ srvctl stop database -d ngpdb PRCC-1016 : ngpdb was already stopped [oracle@ngpdb01 trace]$ srvctl start database -d ngpdb -o mount
2.4 查詢 switchover 目標 RAC 備庫是否準備好切換成為新的主庫
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS -------------------- TO PRIMARY
TO PRIMARY 或者 SESSIONS ACTIVE 狀態說明備庫已經準備好切換成主庫角色
2.5 切換目標 RAC 物理備庫成為主庫
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN; Database altered.
2.6 開啟新的 RAC 主庫
節點
1
執行
:
[oracle@ngpdb03 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 7 10:10:14 2020 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> select open_mode from v$database; OPEN_MODE -------------------- MOUNTED SQL> ALTER DATABASE OPEN; Database altered.
節點
2
執行
:
[oracle@ngpdb04 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 7 10:10:41 2020 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> select open_mode from v$database; OPEN_MODE -------------------- MOUNTED SQL> ALTER DATABASE OPEN; Database altered.
2.7 在新的 RAC 物理備庫上執行 redo apply
[oracle@ngpdb01 trace]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 7 10:13:30 2020 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; Database altered.
2.8 在新的 RAC 主庫上配置 crmngpsd 和 ngppdg 的遠端歸檔路徑
alter system set log_archive_dest_2='SERVICE=ngppdg lgwr async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ngppdg' sid='*'; alter system set log_archive_dest_3='SERVICE=crmngpsd lgwr async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=crmngpsd' sid='*'; alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE sid='*'; alter system set LOG_ARCHIVE_DEST_STATE_3=ENABLE sid='*';
2.9 修改 crmngpsd 和 ngppdg 的 FAL_SERVER 指向新的 RAC 主庫
SQL> alter system set fal_server='ngpdbracdg';
三、 主備切換後驗證所有備庫同步狀態
3.1 模擬 rac 主庫進行日誌切換,檢視各個備庫是否能正常接收日誌以及日誌應用狀態
RAC
主庫
:
SQL> alter system archive log current; System altered.
RAC
備庫
:
Fri Aug 21 10:12:48 2020 RFS[2]: Selected log 11 for thread 1 sequence 466 dbid -216519938 branch 1046858879 Fri Aug 21 10:12:50 2020 Media Recovery Waiting for thread 1 sequence 466 (in transit) Recovery of Online Redo Log: Thread 1 Group 11 Seq 466 Reading mem 0 Mem# 0: +NGPDATA01/ngpdb/onlinelog/group_11.811.1047655443 Fri Aug 21 10:12:51 2020 RFS[4]: Selected log 13 for thread 2 sequence 765 dbid -216519938 branch 1046858879 Fri Aug 21 10:12:53 2020 Archived Log entry 275114 added for thread 2 sequence 764 ID 0xf9cbfdcf dest 1: Fri Aug 21 10:12:54 2020 Archived Log entry 275115 added for thread 1 sequence 465 ID 0xf9cbfdcf dest 1: Media Recovery Waiting for thread 2 sequence 765 (in transit) Recovery of Online Redo Log: Thread 2 Group 13 Seq 765 Reading mem 0 Mem# 0: +NGPDATA01/ngpdb/onlinelog/group_13.803.1047655395
ngppdg:
Fri Aug 21 10:12:49 2020 RFS[19]: Selected log 12 for thread 1 sequence 466 dbid -216519938 branch 1046858879 Fri Aug 21 10:12:51 2020 Archived Log entry 155 added for thread 1 sequence 465 ID 0xf9cbfdcf dest 1: Fri Aug 21 10:12:51 2020 Media Recovery Waiting for thread 1 sequence 466 (in transit) Recovery of Online Redo Log: Thread 1 Group 12 Seq 466 Reading mem 0 Mem# 0: /oradata/ngppdg/group_12.802.1047655393 Fri Aug 21 10:12:52 2020 RFS[20]: Selected log 14 for thread 2 sequence 765 dbid -216519938 branch 1046858879 Fri Aug 21 10:12:52 2020 Archived Log entry 156 added for thread 2 sequence 764 ID 0xf9cbfdcf dest 1: Fri Aug 21 10:13:02 2020 Media Recovery Waiting for thread 2 sequence 765 (in transit) Recovery of Online Redo Log: Thread 2 Group 14 Seq 765 Reading mem 0 Mem# 0: /oradata/ngppdg/group_14.804.1047655395
crmngpsd:
Fri Aug 21 10:12:49 2020 RFS[13]: Selected log 11 for thread 1 sequence 466 dbid -216519938 branch 1046858879 Fri Aug 21 10:12:52 2020 RFS[14]: Selected log 14 for thread 2 sequence 765 dbid -216519938 branch 1046858879 Fri Aug 21 10:12:53 2020 RFS LogMiner: Registered logfile [/oradata/crmngpsd/archivelog_stdby/1_465_1046858879.dbf] to LogMiner session id [2] Fri Aug 21 10:12:53 2020 RFS LogMiner: Registered logfile [/oradata/crmngpsd/archivelog_stdby/2_764_1046858879.dbf] to LogMiner session id [2] Fri Aug 21 10:14:13 2020 LSP0: warning -- apply server 4, sid 21 waiting for event (since 448 seconds):
三個備庫都正常接受日誌並進行日誌應用。
3.2 檢視備庫狀態
RAC
備庫
:
[oracle@ngpdb01 trace]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 21 10:21:43 2020 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> set linesize 500 pagesize 10000 SQL> select INST_ID,PROCESS,PID,STATUS,CLIENT_PID from gv$managed_standby; INST_ID PROCESS PID STATUS CLIENT_PID ---------- --------- ---------- ------------ ---------------------------------------- 1 ARCH 21437 CLOSING 21437 1 ARCH 21440 CLOSING 21440 1 ARCH 21442 CLOSING 21442 1 ARCH 21444 CLOSING 21444 1 RFS 21555 IDLE 4687 1 RFS 21499 IDLE 4691 1 RFS 21497 IDLE 14190 1 RFS 21505 IDLE 14856 1 RFS 21507 IDLE 4966 1 MRP0 22035 APPLYING_LOG N/A 1 RFS 21574 IDLE 14860 2 ARCH 25586 CLOSING 25586 2 ARCH 25589 CLOSING 25589 2 ARCH 25591 CONNECTED 25591 2 ARCH 25593 CLOSING 25593 2 RFS 25730 IDLE 4684 2 RFS 25769 IDLE 14862 17 rows selected.
ngppdg:
SQL> col client_pid format a10 SQL> col process format a10 SQL> col status format a10 SQL> set linesize 500 pagesize 10000 SQL> select INST_ID,PROCESS,PID,STATUS,CLIENT_PID from gv$managed_standby; INST_ID PROCESS PID STATUS CLIENT_PID ---------- ---------- ---------- ---------- ---------- 1 ARCH 29541 CLOSING 29541 1 ARCH 29543 CLOSING 29543 1 ARCH 29545 CONNECTED 29545 1 ARCH 29547 CLOSING 29547 1 RFS 13359 IDLE 4684 1 RFS 13363 IDLE 14856 1 RFS 13372 IDLE 5069 1 RFS 13370 IDLE 14337 1 RFS 13367 IDLE 4691 1 RFS 13386 IDLE 21444 1 RFS 13388 IDLE 21437 1 RFS 13390 IDLE 21442 1 MRP0 11897 APPLYING_L N/A OG 1 RFS 13392 IDLE 4687 1 RFS 13403 IDLE 14860 15 rows selected.
crmngpsd:
SQL> col spid format a10 SQL> col type format a15 SQL> set linesize 500 pagesize 10000 SQL> SELECT SID, SERIAL#, SPID, TYPE FROM V$LOGSTDBY_PROCESS; SID SERIAL# SPID TYPE ---------- ---------- ---------- --------------- 770 5 2201 COORDINATOR 23 11 2266 ANALYZER 775 11 2268 APPLIER 1534 11 2270 APPLIER 2291 11 2272 APPLIER 21 11 2274 APPLIER 777 11 2276 APPLIER 18 3 2215 READER 762 61 2217 BUILDER 1529 29 2219 PREPARER 10 rows selected. SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; SELECT APPLIED_TIME, APPLIED_SCN, MINING_TIME, MINING_SCN FROM V$LOGSTDBY_PROGRESS; Session altered. SQL> APPLIED_TIME APPLIED_SCN MINING_TIME MINING_SCN ----------------------------- ----------- ----------------------------- ---------- 21-AUG-2020 09:29:24 3.5747E+10 21-AUG-2020 09:29:26 3.5747E+10 SQL> col state format a10 SQL> SELECT SESSION_ID, STATE FROM V$LOGSTDBY_STATE; SESSION_ID STATE ---------- ---------- 2 APPLYING
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/69982136/viewspace-2715851/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle 11g datagurd主從切換Oracle
- Oracle 單機切換為主備Oracle
- Oracle DataGuard 主備切換 (switchover) oracle11gOracle
- Oracle 11g dg switchover切換操作流程Oracle
- StoneDB 主從切換實踐方案
- openGauss主備切換之switchover與failoverAI
- 【DG】Data Guard主備庫Failove切換AI
- 【DG】Data Guard主備庫Switchover切換
- Mysql 5.6 Master和Slave 主備切換MySqlAST
- 09.redis 哨兵主備切換時資料丟失的解決方案Redis
- Web前端主題切換的幾種方案Web前端
- 基於多種場景DataGuard切換方案
- 【PG流複製】Postgresql流複製主備切換SQL
- mysql主備切換canal出現的問題解析MySql
- 【DATAGUARD】Oracle Dataguard物理備庫切換最佳實踐(sqlplus)OracleSQL
- oracle 11g dg broker 開啟fast-start failover自動故障切換OracleASTAI
- (九)主題切換
- Redis主從切換Redis
- ORACLE 12C DATAGUARD環境搭建和主從切換Oracle
- 12c data guard 使用 sqlplus 主備切換最佳實踐SQL
- 手工切換MySQL主從MySql
- Redis sentinel主從切換Redis
- oracle dg切換操作示例Oracle
- GCC 多版本切換GC
- jdk 多版本切換JDK
- 多螢幕切換
- Oracle 11g單主搭建物理DGOracle
- Spring Boot中自定義註解+AOP實現主備庫切換Spring Boot
- SQLServer2012映象主庫掛掉如何切換到映象備庫SQLServer
- oracle rman備份歸檔日誌需要先切換日誌嗎Oracle
- keepalived配置redis主從切換Redis
- 小程式切換主題配色
- ostgreSQL主從切換-手動SQL
- echarts 主題動態切換Echarts
- Oracle Temp 表空間切換Oracle
- oracle11g dataguard切換Oracle
- Oracle RAC DG手動切換Oracle
- linux svn server搭建、多專案管理及主備方案LinuxServer專案管理