Oracle-CDB容器数据库主备切换步骤

前言:

        Oracle12c之后推出了CDB容器数据库，CDB容器数据库包含根容器root以及多个PDB数据库，与之前Oracle11g的非容器数据库架构存在不同，那么如何进行CDB容器数据库的主备切换？本文接下来将讲述CDB容器数据库的主备切换步骤

数据库环境：

​db_role	ip	uniq_name	root_db	pdb
primary(19.3)	192.168.146.2	orcl	CDB$ROOT	PDB$SEED pdb pdb1 pdb3
standby(19.3)	192.168.146.3	orcldg	CDB$ROOT	pdb1

注:数据库环境为单实例的DG主备，主备之间只同步CDB$ROOT,pdb1

​

主备切换步骤:

注:切换的命令使用Oracle12c之后新的命令，只需要在主库执行,不需要像11G之前需要分步在主备库执行切换命令

1 在主库(146.2)验证主备是否具备切换条件

ALTER DATABASE SWITCHOVER TO ORCLDG VERIFY;
​
ERROR at line 1:
ORA-16475: succeeded with warnings, check alert log for more details

 2 验证命令执行完成，但有warnings，检查主库alert log

---warnings的内容为备库存在offline的pluggable database，由于主备之间只同步pdb1,所以warning忽略
ALTER DATABASE SWITCHOVER TO ORCLDG VERIFY
2022-08-20T16:16:25.211657+08:00
SWITCHOVER VERIFY WARNING: no standby redo logfiles configured. Standby redo logfiles are recommended configuration for physical standby database.
SWITCHOVER VERIFY: Send VERIFY request to switchover target ORCLDG
SWITCHOVER VERIFY WARNING: switchover target has offline datafiles. Verify that those datafiles should remain offline.
SWITCHOVER VERIFY WARNING: switchover target has offline pluggable databases.
ORA-16475 signalled during: ALTER DATABASE SWITCHOVER TO ORCLDG VERIFY...

3 在主库(146.2)执行主备切换命令

---命令执行完成，就代表主备切换完成
ALTER DATABASE SWITCHOVER TO ORCLDG;
​
Database altered.

4 检查主库(146.2)的alert切换日志

---主库开始切换为备库
TMI: kcv_switchover_to_target convert to physical BEGIN 2022-08-20 16:17:39.750147
2022-08-20T16:17:39.750491+08:00
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 5068] (orcl)
NET  (PID:5068): Waiting for target standby to receive all redo
2022-08-20T16:17:39.752467+08:00
NET  (PID:5068): Waiting for all non-current ORLs to be archived
2022-08-20T16:17:39.752550+08:00
NET  (PID:5068): All non-current ORLs have been archived
2022-08-20T16:17:39.752618+08:00
NET  (PID:5068): Waiting for all FAL entries to be archived
2022-08-20T16:17:39.752661+08:00
NET  (PID:5068): All FAL entries have been archived
2022-08-20T16:17:39.752714+08:00
NET  (PID:5068): Waiting for LAD:2 to become synchronized
2022-08-20T16:17:40.753270+08:00
NET  (PID:5068): Active, synchronized Physical Standby switchover target has been identified
NET  (PID:5068): Preventing updates and queries at the Primary
2022-08-20T16:17:42.755914+08:00
NET  (PID:5068): Generating and shipping final logs to target standby
Switchover End-Of-Redo Log thread 1 sequence 39 has been fixed
Switchover: Primary highest seen SCN set to 0x000000000032f541
NET  (PID:5068): Noswitch archival of T-1.S-39
NET  (PID:5068): End-Of-Redo Branch archival of T-1.S-39
NET  (PID:5068): LGWR is scheduled to archive to LAD:2 after log switch
NET  (PID:5068): SRL selected for T-1.S-39 for LAD:2
NET  (PID:5068): Archiving is disabled due to current logfile archival
Primary will check for some target standby to have received all redo
NET  (PID:5068): Waiting for target standby to apply all redo
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_5068.trc
NET  (PID:5068): Converting the primary database to a new standby database
Clearing standby activation ID 1640651983 (0x61ca5ccf)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 209715200;
Archivelog for thread 1 sequence 39 required for standby recovery
Offline data file 5 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 6 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 8 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 9 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 10 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 11 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 12 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 13 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 14 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 15 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 16 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 28 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 29 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 30 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Switchover: Primary controlfile converted to standby controlfile succesfully.
Switchover: Complete - Database shutdown required
TMI: kcv_switchover_to_target convert to physical END 2022-08-20 16:17:42.953878
NET  (PID:5068): Sending request(convert to primary database) to switchover target ORCLDG
2022-08-20T16:17:48.890626+08:00
NET  (PID:5068): Switchover complete. Database shutdown required
USER (ospid: 5068): terminating the instance
2022-08-20T16:17:49.905701+08:00
Instance terminated by USER, pid = 5068
TMI: dbsdrv switchover to target END 2022-08-20 16:17:49.905742
---主库切换为备库完成

5 检查备库(146.3)的alert切换日志

---备库切换为主库
2022-08-20T16:17:44.564127+08:00
Background Media Recovery process shutdown (orcldg)
2022-08-20T16:17:45.353980+08:00
 rmi (PID:5261): Role Change: Canceled MRP
TMI: kcv_commit_to_so_to_primary wait for MRP to finish END 2022-08-20 16:17:45.354132
TMI: kcv_commit_to_so_to_primary Switchover from physical BEGIN 2022-08-20 16:17:45.354291
 rmi (PID:5261): Killing 2 processes (PIDS:5253,4753) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 5261
2022-08-20T16:17:45.355342+08:00

6 主备切换完成，打开新主库(146.3)

---打开root根容器
alter database open;
---打开pdb1，因为只同步了pdb1，其他pdb没有同步，所以无法打开
alter pluggable database pdb1 open;
---当前为主库
SQL> select database_role from v$database;
​
DATABASE_ROLE
----------------
PRIMARY

7 打开新备库(146.2),主备切换结束

---新备库之前的全部pdb都可以打开，只不过，PDB$SEED,PDB,PDB3新主库没有
SQL> startup  
SQL> alter pluggable database all open;
​
Pluggable database altered.
​
SQL> alter database recover managed standby database using current logfile disconnect from session parallel 8;
​
Database altered.
​
SQL> SQL> SQL> SQL> show pdbs;
​
    CON_ID CON_NAME        OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
   2 PDB$SEED        READ ONLY  NO
   3 PDB          READ ONLY  NO
   4 PDB1         READ ONLY  NO
   6 PDB3         READ ONLY  NO
SQL> select database_role from v$database;
​
DATABASE_ROLE
----------------
PHYSICAL STANDBY
​
SQL>