Oracle 19c RAC + ADG 手动 failover
角色转换步骤
Revision V4.0
No. | Date | Author/Modifier | Comments |
---|---|---|---|
1.0 | 2020-03-06 | 谈权 | 初稿:搭建 Oracle MAA: Oracle 19c RAC + ADG |
2.0 | 2020-03-10 | 谈权 | 增加:16. 手动 switchover 角色转换步骤 |
3.0 | 2020-03-13 | 谈权 | 增加:17. 手动 failover 角色转换步骤 |
4.0 | 2020-03-14 | 谈权 | 完善:「17.3」和「17.4」 |
目录
接上篇两篇文章(搭建 Oracle MAA: Oracle 19c RAC + ADG 和 Oracle 19c RAC + ADG 手动 switchover 角色转换步骤), 本文继续完成 「17. 手动 failover
角色转换步骤」。
17. 手动 failover
角色转换步骤
17.1 Data Guard Side: Standby (single-instance) 进行 failover
切换
如果 data guard 主数据库的情况很糟糕,或者不能用于生产,那么我们可以激活备用数据库作为主生产数据库。
==failover将破坏dataguard模式。需要重新配置dataguard==
Data guard Configuration details:
Environment Details Primary (RAC) Standby (single-instance) OS CentOS Linux release 7.7.1908 (Core) CentOS Linux release 7.7.1908 (Core) DB Version Version 19.6.0.0.0 Version 19.6.0.0.0 DATABASE_ROLE PRIMARY PHYSICAL STANDBY DB_UNIQUE_NAME tqdb tqdb_adg Failover Configuration details:
Environment Details Primary (RAC) Standby (single-instance) OS CentOS Linux release 7.7.1908 (Core) CentOS Linux release 7.7.1908 (Core) DB Version Version 19.6.0.0.0 Version 19.6.0.0.0 DATABASE_ROLE PRIMARY PRIMARY DB_UNIQUE_NAME tqdb tqdb_adg -- Primary Side -- RAC 节点1 查看 `DATABASE_ROLE` 和 `OPEN_MODE` 21:49:58 sys@TQDB(tqdb21)> select db.INST_ID, db.DBID, inst.INSTANCE_NAME, inst.HOST_NAME, db.OPEN_MODE, db.PROTECTION_MODE, db.DATABASE_ROLE, db.DB_UNIQUE_NAME 21:49:58 2 from gv$database db, gv$instance inst 21:49:58 3 where db.INST_ID = inst.INST_ID 21:49:58 4 ; INST_ID DBID INSTANCE_NAME HOST_NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE DB_UNIQUE_NAME -------- ---------- -------------- ---------- ------------ -------------------- -------------- -------------- 1 3966209240 tqdb1 tqdb21 READ WRITE MAXIMUM PERFORMANCE PRIMARY tqdb 2 3966209240 tqdb2 tqdb22 READ WRITE MAXIMUM PERFORMANCE PRIMARY tqdb 21:49:58 sys@TQDB(tqdb21)> -- Data Guard Side: -- 备库 -- 1. 查看备库端的 `DATABASE_ROLE` 和 `OPEN_MODE` 21:54:04 sys@TQDB(tq1)> select db.INST_ID, db.DBID, inst.INSTANCE_NAME, inst.HOST_NAME, db.OPEN_MODE, db.PROTECTION_MODE, db.DATABASE_ROLE, db.DB_UNIQUE_NAME 21:54:04 2 from gv$database db, gv$instance inst 21:54:04 3 where db.INST_ID = inst.INST_ID 21:54:04 4 ; INST_ID DBID INSTANCE_NAME HOST_NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE DB_UNIQUE_NAME ------- ---------- -------------- ---------- -------------------- -------------------- ---------------- --------------- 1 3966209240 tqdb_adg tq1 READ ONLY WITH APPLY MAXIMUM PERFORMANCE PHYSICAL STANDBY tqdb_adg 21:54:04 sys@TQDB(tq1)> -- 2. Cancel the MRP process [oracle@tq1: ~]$ ps -ef | grep mrp oracle 11719 13271 0 22:07 pts/3 00:00:00 grep --color mrp oracle 25724 1 0 Mar11 ? 00:04:08 ora_mrp0_tqdb_adg [oracle@tq1: ~]$ [oracle@tq1: ~]$ [oracle@tq1: ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Fri Mar 13 22:09:18 2020 Version 19.6.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.6.0.0.0 22:08:16 sys@TQDB(tq1)> 22:08:17 sys@TQDB(tq1)> alter database recover managed standby database cancel; Database altered. 22:08:48 sys@TQDB(tq1)> -- 3. 接下来的命令,将帮助将 standby 作为 primary。 23:22:55 sys@TQDB(tq1)> alter database recover managed standby database finish; Database altered. 23:23:40 sys@TQDB(tq1)> 23:25:15 sys@TQDB(tq1)> col name for a10; 23:25:27 sys@TQDB(tq1)> set lines 200; 23:25:30 sys@TQDB(tq1)> select name, open_mode, database_role from v$database; NAME OPEN_MODE DATABASE_ROLE ---------- -------------------- ---------------- TQDB READ ONLY PHYSICAL STANDBY 23:25:32 sys@TQDB(tq1)> 23:26:30 sys@TQDB(tq1)> 23:26:30 sys@TQDB(tq1)> alter database activate standby database; Database altered. -- Managed recovery process has been stopped between primary and standby database and standby becomes primary database. -- MRP(Managed recovery process)在主数据库和备用数据库之间停止,备用数据库成为主数据库。 23:27:16 sys@TQDB(tq1)> select name, open_mode, database_role from v$database; NAME OPEN_MODE DATABASE_ROLE ---------- -------------------- ---------------- TQDB MOUNTED PRIMARY -- 4. 重启数据库实例,此时「原备库」已经成为 `primary`。 23:27:41 sys@TQDB(tq1)> 23:29:27 sys@TQDB(tq1)> 23:29:27 sys@TQDB(tq1)> conn / as sysdba Connected. 23:29:30 idle(tq1)> 23:29:31 idle(tq1)> 23:29:31 idle(tq1)> shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. 23:30:14 idle(tq1)> 23:30:42 idle(tq1)> 23:30:42 idle(tq1)> startup ORACLE instance started. Total System Global Area 1191181696 bytes Fixed Size 8895872 bytes Variable Size 318767104 bytes Database Buffers 855638016 bytes Redo Buffers 7880704 bytes Database mounted. Database opened. 23:30:57 idle(tq1)> conn / as sysdba Connected. 23:31:11 sys@TQDB(tq1)> 23:31:12 sys@TQDB(tq1)> set lines 200 23:31:22 sys@TQDB(tq1)> col name for a10; 23:31:30 sys@TQDB(tq1)> select name, open_mode, database_role from v$database; NAME OPEN_MODE DATABASE_ROLE ---------- -------------------- ---------------- TQDB READ WRITE PRIMARY 23:31:35 sys@TQDB(tq1)> 23:45:04 sys@TQDB(tq1)> col HOST_NAME for a10; 23:45:04 sys@TQDB(tq1)> -- 23:45:04 sys@TQDB(tq1)> select db.INST_ID, db.DBID, inst.INSTANCE_NAME, inst.HOST_NAME, db.OPEN_MODE, db.PROTECTION_MODE, db.DATABASE_ROLE, db.DB_UNIQUE_NAME 23:45:04 2 from gv$database db, gv$instance inst 23:45:04 3 where db.INST_ID = inst.INST_ID 23:45:04 4 ; INST_ID DBID INSTANCE_NAME HOST_NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE DB_UNIQUE_NAME ---------- ---------- ---------------- ---------- -------------------- -------------------- ---------------- ------------------------------ 1 3966209240 tqdb_adg tq1 READ WRITE MAXIMUM PERFORMANCE PRIMARY tqdb_adg 23:45:05 sys@TQDB(tq1)> -- 此时,「原备库」成为的 `primary`,已经与「原主库RAC」没有 data guard 关系了。 -- 「原备库」与「原主库RAC」是两套独立的 `primary`,之间没有 data guard 关系了。 -- 「原主库RAC」 23:44:49 sys@TQDB(tqdb21)> -- 23:44:49 sys@TQDB(tqdb21)> select db.INST_ID, db.DBID, inst.INSTANCE_NAME, inst.HOST_NAME, db.OPEN_MODE, db.PROTECTION_MODE, db.DATABASE_ROLE, db.DB_UNIQUE_NAME 23:44:49 2 from gv$database db, gv$instance inst 23:44:49 3 where db.INST_ID = inst.INST_ID 23:44:49 4 ; INST_ID DBID INSTANCE_NAME HOST_NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE DB_UNIQUE_NAME ---------- ---------- ---------------- ---------- -------------------- -------------------- ---------------- ------------------------------ 1 3966209240 tqdb1 tqdb21 READ WRITE MAXIMUM PERFORMANCE PRIMARY tqdb 2 3966209240 tqdb2 tqdb22 READ WRITE MAXIMUM PERFORMANCE PRIMARY tqdb 23:44:49 sys@TQDB(tqdb21)>
17.2 「原主库」old Primary (RAC) 恢复为「新主库」的备库的三种方法
在failover之后,如果原主库故障解决,可以重新上线,我们可以看到,在startup以后,它的角色仍然是Primary,很显然,一个dataguard配置中,是不可能有两个主库的。这时,我们可以将这个原主库转换为新主库的备库。
主要有三种方法:
一、按照先前的方法,利用新主库的备份,将这个原主库重新配置为备库。
生成环境下,一般建议使用「方法一」:将“原主库”重新搭建为“新主库”的`PHYSICAL STANDBY`,再进行 `switchover`(切换回“原主库” 为`PRIMARY`,原备库为“PHYSICAL STANDBY”)。 即:重新搭建回 Active Data Guard 架构。 「现主库 tq1」 -->> 「现备库 RAC」 其实,也就是单实例到RAC的Data Guard架构的搭建。(步骤详见: 「17.3 重新搭建回 Active Data Guard 架构」)
二、利用flashback。
-- Flashing Back a Failed Primary Database into a Physical Standby Database 1. 查询原备库转换成主库时的SCN -->> tq1 上操作 SQL> select to_char(standby_became_primary_scn) from v$database; TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) ---------------------------------------- 901719 2. Flash back原主库 -->> 即:RAC节点 SQL> shutdown immediate SQL> startup mount SQL> flashback database to scn 901719; -->> 注意,前提是 `flashback_on` 的特性必须开启,`alter database flashback on;` -->> 在我的事例中,没有开启 `flashback_on` 的特性,也就无法使用`flashback`快速恢复dataguard了。 3. 将原主库转换为备库 -->> RAC节点 上操作 SQL> alter database convert to physical standby; SQL> shutdown immediate SQL> startup 4. 现备库上启用Redo Apply -->> RAC节点 上操作 SQL> alter database recover managed standby database using current logfile disconnect from session; 基本OK!
三、利用rman备份。
-- Converting a Failed Primary into a Standby Database Using RMAN Backups 1. 查询原备库转换成主库时的SCN -->> tq1 上操作 SQL> select to_char(standby_became_primary_scn) from v$database; 2. 恢复原主库 -->> 即:RAC节点 RMAN > run { set until scn
; restore database; recover database; } 3. 将原主库转换为备库 -->> 即:RAC节点 SQL> alter database convert to physical standby; SQL> shutdown immediate SQL> startup mount SQL> alter database open read only; 4. 现备库上启用Redo Apply -->> 即:RAC节点 SQL> alter database recover managed standby database using current logfile disconnect from session; 基本OK!
17.3 重新搭建回 Active Data Guard 架构
在failover之后,如果原主库故障解决,可以重新上线,我们可以看到,在startup以后,它的角色仍然是Primary,很显然,一个dataguard配置中,是不可能有两个主库的。这时,我们可以将这个原主库转换为新主库的备库。
主要有三种方法:
一、按照先前的方法,利用新主库的备份,将这个原主库重新配置为备库。
生成环境下,一般建议使用「方法一」:将“原主库”重新搭建为“新主库”的`PHYSICAL STANDBY`,再进行 `switchover`(切换回“原主库” 为`PRIMARY`,原备库为“PHYSICAL STANDBY”)。 即:重新搭建回 Active Data Guard 架构。 「现主库 tq1」 -->> 「现备库 RAC」 其实,也就是单实例到RAC的Data Guard架构的搭建。(步骤详见: 「17.3 重新搭建回 Active Data Guard 架构」)
操作记录:
一、按照先前的方法,利用新主库的备份,将这个原主库重新配置为备库。 生成环境下,一般建议使用「方法一」:将“原主库”重新搭建为“新主库”的`PHYSICAL STANDBY`,再进行 `switchover`(切换回“原主库” 为`PRIMARY`,原备库为“PHYSICAL STANDBY”)。 -- 「新主库 tq1」: pfile文件不变 [oracle@tq1: /u01/app/oracle/product/19c/dbhome/dbs]$ vim inittqdb_adg.ora *.audit_file_dest='/u01/app/oracle/admin/tqdb/adump' *.db_unique_name='tqdb_adg' *.LOG_ARCHIVE_CONFIG='DG_CONFIG=(tqdb_adg,tqdb)' *.log_archive_dest_1='LOCATION=+DATA/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=tqdb_adg' *.log_archive_dest_2='SERVICE=tqdb ASYNC LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=tqdb' *.log_archive_dest_state_1='enable' *.log_archive_dest_state_2='enable' *.log_archive_format='%t_%s_%r.arc' *.standby_file_management='AUTO' *.fal_server='tqdb' *.fal_client='tqdb_adg' *.control_files='+DATA' *.db_create_file_dest='+DATA' *.db_name='tqdb' *.pga_aggregate_target=379M *.processes=300 *.sga_target=1136M *.db_block_size=8192 *.compatible="19.0.0" *.audit_trail="DB" *.open_cursors=300 *._optimizer_use_auto_indexes="OFF" [oracle@tq1: /u01/app/oracle/product/19c/dbhome/dbs]$ ---- 「新备库 RAC」 pfile=/tmp/init_tqdb21_new_ADG.sql tqdb1.__data_transfer_cache_size=0 tqdb2.__data_transfer_cache_size=0 tqdb1.__db_cache_size=385875968 tqdb2.__db_cache_size=415236096 tqdb1.__inmemory_ext_roarea=0 tqdb2.__inmemory_ext_roarea=0 tqdb1.__inmemory_ext_rwarea=0 tqdb2.__inmemory_ext_rwarea=0 tqdb1.__java_pool_size=0 tqdb2.__java_pool_size=0 tqdb1.__large_pool_size=4194304 tqdb2.__large_pool_size=4194304 tqdb1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment tqdb2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment tqdb1.__pga_aggregate_target=276824064 tqdb2.__pga_aggregate_target=276824064 tqdb1.__sga_target=822083584 tqdb2.__sga_target=822083584 tqdb1.__shared_io_pool_size=33554432 tqdb2.__shared_io_pool_size=33554432 tqdb1.__shared_pool_size=385875968 tqdb2.__shared_pool_size=356515840 tqdb1.__streams_pool_size=0 tqdb2.__streams_pool_size=0 tqdb1.__unified_pga_pool_size=0 tqdb2.__unified_pga_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/tqdb/adump' *.db_unique_name='tqdb' *.log_archive_config='DG_CONFIG=(tqdb,tqdb_adg)' *.log_archive_dest_1='LOCATION=+DATA/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=tqdb' *.log_archive_dest_2='SERVICE=tqdb_adg ASYNC LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=tqdb_adg' *.log_archive_dest_state_1='enable' *.log_archive_dest_state_2='enable' *.log_archive_format='%t_%s_%r.arc' *.standby_file_management='AUTO' *.fal_client='tqdb' *.fal_server='tqdb_adg' *.control_files='+DATA' *.db_create_file_dest='+DATA' *.db_name='tqdb' *.pga_aggregate_target=262m *.processes=300 *.sga_target=783m *.db_block_size=8192 *.compatible="19.0.0" *.audit_trail="DB" *.open_cursors=300 *._optimizer_use_auto_indexes="OFF" *.cluster_database=TRUE *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=tqdbXDB)' family:dw_helper.instance_mode='read-only' tqdb2.instance_number=2 tqdb1.instance_number=1 *.local_listener='-oraagent-dummy-' *.nls_language='AMERICAN' *.nls_territory='AMERICA' *.remote_login_passwordfile='exclusive' tqdb2.thread=2 tqdb1.thread=1 *.undo_tablespace='UNDOTBS1' tqdb2.undo_tablespace='UNDOTBS2' tqdb1.undo_tablespace='UNDOTBS1' [oracle@tqdb21: /tmp]$ scp init_tqdb21_new_ADG.sql oracle@tqdb22:/tmp/init_tqdb22_new_ADG.sql init_tqdb21_new_ADG.sql 100% 2145 2.6MB/s 00:00 [oracle@tqdb21: /tmp]$ -- 「新备库 RAC」 节点1 [oracle@tqdb21: /tmp]$ echo $ORACLE_SID tqdb1 [oracle@tqdb21: /tmp]$ echo $DB_UNIQUE_NAME tqdb [oracle@tqdb21: /tmp]$ -- 「现备库 RAC」: 使用上面的`pfile`启动备库到 `nomount` 状态 -- 「现备库 RAC」 节点1: 先只启动一个实例 tqdb1 [oracle@tqdb21: /tmp]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 14 04:22:02 2020 Version 19.6.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to an idle instance. 04:22:04 idle> startup nomount pfile='/tmp/init_tqdb21_new_ADG.sql' ORACLE instance started. Total System Global Area 822080768 bytes Fixed Size 8901888 bytes Variable Size 390070272 bytes Database Buffers 419430400 bytes Redo Buffers 3678208 bytes 04:22:30 idle> 04:23:14 idle> -- 「现备库 RAC」 节点2: 停止数据库实例 [oracle@tqdb22: /tmp]$ echo $ORACLE_SID tqdb2 [oracle@tqdb22: /tmp]$ echo $DB_UNIQUE_NAME tqdb [oracle@tqdb22: /tmp]$ [oracle@tqdb22: /tmp]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 14 04:24:42 2020 Version 19.6.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to an idle instance. 04:24:48 idle> 04:24:51 idle> startup nomount pfile='/tmp/init_tqdb22_new_ADG.sql'; ORACLE instance started. Total System Global Area 822080768 bytes Fixed Size 8901888 bytes Variable Size 360710144 bytes Database Buffers 448790528 bytes Redo Buffers 3678208 bytes 04:25:38 idle> 04:25:52 idle> 04:25:52 idle> shutdown immeidate; 创建密码文件, SYS密码与主数据库的密码匹配。 4.密码文件 将单实例主库的密码文件orapw<$ORACLE_SID>拷贝至备库所有节点,并改名为`orapwtqdb1`和`orapwtqdb2` -- 「新主库 tq1」 oracle$ orapwd file=/u01/app/oracle/product/19c/dbhome/dbs/orapwtqdb password=Oracle123 entries=10 format=12 ``` [oracle@tq1: /u01/app/oracle/product/19c/dbhome/dbs]$ cp orapwtqdb_adg orapwtqdb_adg.bak [oracle@tq1: /u01/app/oracle/product/19c/dbhome/dbs]$ mv orapwtqdb_adg.bak orapwtqdb_adg.bak_20200314 [oracle@tq1: /u01/app/oracle/product/19c/dbhome/dbs]$ [oracle@tq1: /u01/app/oracle/product/19c/dbhome/dbs]$ [oracle@tq1: /u01/app/oracle/product/19c/dbhome/dbs]$ orapwd file=/u01/app/oracle/product/19c/dbhome/dbs/orapwtqdb_adg password=Oracle123 entries=10 format=12 OPW-00005: File with same name exists - please delete or rename [oracle@tq1: /u01/app/oracle/product/19c/dbhome/dbs]$ rm orapwtqdb_adg [oracle@tq1: /u01/app/oracle/product/19c/dbhome/dbs]$ orapwd file=/u01/app/oracle/product/19c/dbhome/dbs/orapwtqdb_adg password=Oracle123 entries=10 format=12 [oracle@tq1: /u01/app/oracle/product/19c/dbhome/dbs]$ ll -th total 175M -rw-r----- 1 oracle oinstall 2.0K Mar 14 04:40 orapwtqdb_adg -rw-r----- 1 oracle oinstall 2.0K Mar 14 04:39 orapwtqdb_adg.bak_20200314 -rw-r----- 1 oracle asmadmin 9.5K Mar 14 02:59 spfiletqdb_adg.ora -rw-rw---- 1 oracle asmadmin 1.6K Mar 14 02:59 hc_tqdb_adg.dat -rw-r----- 1 oracle asmadmin 44M Mar 13 23:40 c-3966209240-20200313-00 -rw-r----- 1 oracle asmadmin 44M Mar 13 23:40 snapcf_tqdb_adg.f -rw-r----- 1 oracle asmadmin 44M Mar 11 01:20 c-3966209240-20200311-01 -rw-r----- 1 oracle asmadmin 44M Mar 11 00:16 c-3966209240-20200311-00 -rw-r----- 1 oracle asmadmin 24 Mar 7 08:29 lkTQDB_ADG -rw-r--r-- 1 oracle oinstall 784 Mar 7 07:53 inittqdb_adg.ora -rw-rw---- 1 oracle asmadmin 1.6K Mar 7 04:15 hc_tq1.dat -rw-r--r-- 1 oracle asmadmin 941 Feb 6 17:38 inittq1.ora -rw-r----- 1 oracle oinstall 2.0K Jan 17 21:43 orapwtq1 -rw-r----- 1 oracle asmadmin 24 Jan 17 21:27 lkTQ1 -rw-r--r-- 1 oracle oinstall 3.1K May 14 2015 init.ora [oracle@tq1: /u01/app/oracle/product/19c/dbhome/dbs]$ strings orapwtqdb_adg ][Z ORACLE Remote Password file r|qv $3wl`B [oracle@tq1: /u01/app/oracle/product/19c/dbhome/dbs]$ ``` ```将单实例主库的密码文件orapw<$ORACLE_SID>拷贝至备库所有节点,并改名为`orapwtqdb1`和`orapwtqdb2` [oracle@tq1: /u01/app/oracle/product/19c/dbhome/dbs]$ scp orapwtqdb_adg oracle@tqdb21:/u01/app/oracle/product/19c/dbhome/dbs/orapwtqdb1 oracle@tqdb21's password: orapwtqdb_adg 100% 2048 555.7KB/s 00:00 [oracle@tq1: /u01/app/oracle/product/19c/dbhome/dbs]$ scp orapwtqdb_adg oracle@tqdb22:/u01/app/oracle/product/19c/dbhome/dbs/orapwtqdb2 The authenticity of host 'tqdb22 (192.168.6.22)' can't be established. ECDSA key fingerprint is SHA256:QT8z0WN0dmX3S0jnMcLe/MeraabCFvwlYKTmX/kKJ+o. ECDSA key fingerprint is MD5:de:f8:90:99:5d:f1:05:5c:65:4b:fb:8b:0f:bc:63:7d. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added 'tqdb22,192.168.6.22' (ECDSA) to the list of known hosts. oracle@tqdb22's password: orapwtqdb_adg 100% 2048 397.4KB/s 00:00 [oracle@tq1: /u01/app/oracle/product/19c/dbhome/dbs]$ ``` -- 「新主库 tq1」 -- 在做这一步之前确保主库的备份计划已被停止,或rman中ARCHIVELOG DELETION POLICY被设置为applied on standby; -- `CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;` ```配置删除归档策略 [oracle@tq1: ~]$ rman target / Recovery Manager: Release 19.0.0.0.0 - Production on Sat Mar 14 07:01:39 2020 Version 19.6.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: TQDB (DBID=3966209240) RMAN> show all; using target database control file instead of recovery catalog RMAN configuration parameters for database with db_unique_name TQDB_ADG are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP ON; # default CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/19c/dbhome/dbs/snapcf_tqdb_adg.f'; # default RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY; new RMAN configuration parameters: CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY; new RMAN configuration parameters are successfully stored RMAN> show all; RMAN configuration parameters for database with db_unique_name TQDB_ADG are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP ON; # default CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY; CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/19c/dbhome/dbs/snapcf_tqdb_adg.f'; # default RMAN> quit Recovery Manager complete. [oracle@tq1: ~]$ ``` -- 1. 「现备库 RAC」RAC节点1: 增加备库静态监听 ``` [grid@tqdb21: /u01/app/19c/grid/network/admin]$ cat listener.ora LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM)))) # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON # line added by Agent VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=OFF # line added by Agent - Disabled by Agent because REMOTE_REGISTRATION_ADDRESS is set ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET # line added by Agent # 增加备库静态监听 SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (ORACLE_HOME = /u01/app/oracle/product/19c/dbhome) (SID_NAME = tqdb) ) ) [grid@tqdb21: /u01/app/19c/grid/network/admin]$ ``` -- 1. 「现备库」RAC节点2: 增加备库静态监听 ``` [grid@tqdb22: /u01/app/19c/grid/network/admin]$ cat listener.ora LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM)))) # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON # line added by Agent VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=OFF # line added by Agent - Disabled by Agent because REMOTE_REGISTRATION_ADDRESS is set # 增加备库静态监听 SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (ORACLE_HOME = /u01/app/oracle/product/19c/dbhome) (SID_NAME = tqdb) ) ) [grid@tqdb22: /u01/app/19c/grid/network/admin]$ ``` -- 2. 「现备库」RAC节点1 和 RAC节点2 分别重启监听 # srvctl stop listener # srvctl start listener # crsctl stat res -t grid$ lsnrctl status grid$ lsnrctl service -- 「现主库 tq1」和「现备库 RAC」: 主库备库增加`tnsnames`别名 ```「现主库 tq1」 [oracle@tq1: /u01/app/oracle/product/19c/dbhome/network/admin]$ cat tnsnames.ora TQ1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = tq1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = tq1) ) ) # 备库(tq1)添加两个别名 `tqdb` 和 `tqdb_adg` tqdb = (DESCRIPTION = #(ADDRESS = (PROTOCOL = TCP)(HOST = tqdb-cluster-scan)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = tqdb21-vip)(PORT = 1521)) ## 由于前面「新备库 RAC」只 `starup nomount;` 了一个实例 `tqdb1`,所以此时先注释掉 `tqdb22-vip` 。 ## 避免 rman 连接 `auxiliary` 辅助实例时,`LOAD_BALANCE` 负载均衡到实例二 `tqdb2`。 ## 如果 rman 连接 `auxiliary` 辅助实例,连接到实例二 `tqdb2`,状态为 `connected to auxiliary database (not started)`, ## 将无法使用 `duplicate from active database`创建 `standby database` ## 待恢复完 `standby database` 后,再取消 `tqdb22-vip` 的注释。 #(ADDRESS = (PROTOCOL = TCP)(HOST = tqdb22-vip)(PORT = 1521)) (LOAD_BALANCE = yes) (FAILOVER = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = tqdb) (UR=A) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5) ) ) ) tqdb_adg = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = tq1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = tqdb_adg) ) ) [oracle@tq1: /u01/app/oracle/product/19c/dbhome/network/admin]$ ``` -- 「现备库 RAC 节点1」 tnsnames 别名 ```「现备库 RAC 节点1」 [oracle@tqdb21: /u01/app/oracle/product/19c/dbhome/network/admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/19c/dbhome/network/admin/tnsnames.ora # Generated by Oracle configuration tools. #TQDB = # (DESCRIPTION = # (ADDRESS = (PROTOCOL = TCP)(HOST = tqdb-cluster-scan)(PORT = 1521)) # (CONNECT_DATA = # (SERVER = DEDICATED) # (SERVICE_NAME = tqdb) # ) # ) # 「新备库」添加两个别名 `tqdb` 和 `tqdb_adg` tqdb = (DESCRIPTION = #(ADDRESS = (PROTOCOL = TCP)(HOST = tqdb-cluster-scan)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = tqdb21-vip)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = tqdb22-vip)(PORT = 1521)) (LOAD_BALANCE = yes) (FAILOVER = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = tqdb) (UR=A) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5) ) ) ) # 只需添加别名 `tqdb_adg` tqdb_adg = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = tq1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = tqdb_adg) (UR=A) ) ) [oracle@tqdb21: /u01/app/oracle/product/19c/dbhome/network/admin]$ ``` -- 「现备库 RAC 节点2」 tnsnames 别名 ```「现备库 RAC 节点2」 [oracle@tqdb22: /u01/app/oracle/product/19c/dbhome/network/admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/19c/dbhome/network/admin/tnsnames.ora # Generated by Oracle configuration tools. #TQDB = # (DESCRIPTION = # (ADDRESS = (PROTOCOL = TCP)(HOST = tqdb-cluster-scan)(PORT = 1521)) # (CONNECT_DATA = # (SERVER = DEDICATED) # (SERVICE_NAME = tqdb) # ) # ) # 「新备库」添加两个别名 `tqdb` 和 `tqdb_adg` tqdb = (DESCRIPTION = #(ADDRESS = (PROTOCOL = TCP)(HOST = tqdb-cluster-scan)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = tqdb21-vip)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = tqdb22-vip)(PORT = 1521)) (LOAD_BALANCE = yes) (FAILOVER = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = tqdb) (UR=A) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5) ) ) ) # 只需添加别名 `tqdb_adg` tqdb_adg = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = tq1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = tqdb_adg) (UR=A) ) ) [oracle@tqdb22: /u01/app/oracle/product/19c/dbhome/network/admin]$ ``` -- 「现主库 tq1」 oracle 验证登陆 oracle$ sqlplus sys/Oracle123@tqdb as sysdba oracle$ sqlplus sys/Oracle123@tqdb21:1521/tqdb as sysdba oracle$ sqlplus sys/Oracle123@tqdb22:1521/tqdb as sysdba oracle$ sqlplus sys/Oracle123@tqdb_adg as sysdba oracle$ sqlplus sys/Oracle123@tq1:1521/tqdb_adg as sysdba ``` -- 「现主库 tq1」 oracle 验证登陆 [oracle@tq1: ~]$ sqlplus sys/Oracle123@tqdb as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 14 11:41:17 2020 Version 19.6.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.6.0.0.0 11:41:17 idle(tqdb21)> quit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.6.0.0.0 [oracle@tq1: ~]$ [oracle@tq1: ~]$ [oracle@tq1: ~]$ sqlplus sys/Oracle123@tqdb_adg as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 14 11:42:34 2020 Version 19.6.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.6.0.0.0 11:42:34 sys@TQDB(tq1)> quit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.6.0.0.0 [oracle@tq1: ~]$ [oracle@tq1: ~]$ sqlplus sys/Oracle123@tq1:1521/tqdb_adg as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 14 11:42:44 2020 Version 19.6.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.6.0.0.0 11:42:44 sys@TQDB(tq1)> quit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.6.0.0.0 [oracle@tq1: ~]$ ``` -- 3.「现主库 tq1」: 主库准备连接辅助实例 -- 「现主库 tq1」tq1 : 主库准备连接辅助实例 oracle$ rman target / auxiliary sys/Oracle123@tqdb 或者 oracle$ rman target sys/Oracle123@tqdb_adg auxiliary sys/Oracle123@tqdb ```「现主库 tq1」tq1 : 主库准备连接辅助实例 [oracle@tq1: ~]$ rman target / auxiliary sys/Oracle123@tqdb Recovery Manager: Release 19.0.0.0.0 - Production on Sat Mar 14 11:43:54 2020 Version 19.6.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: TQDB (DBID=3966209240) connected to auxiliary database: TQDB (not mounted) RMAN> RMAN> quit Recovery Manager complete. [oracle@tq1: ~]$ [oracle@tq1: ~]$ [oracle@tq1: ~]$ [oracle@tq1: ~]$ rman target sys/Oracle123@tqdb_adg auxiliary sys/Oracle123@tqdb Recovery Manager: Release 19.0.0.0.0 - Production on Sat Mar 14 11:44:55 2020 Version 19.6.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: TQDB (DBID=3966209240) connected to auxiliary database: TQDB (not mounted) RMAN> quit Recovery Manager complete. [oracle@tq1: ~]$ ``` -- 「现主库 tq1」: 使用`DUPLICATE`开始备库创建 ``` -- 执行 RMAN 脚本 run { allocate channel c1 type disk; allocate channel c2 type disk; allocate channel c3 type disk; allocate AUXILIARY channel c4 type disk; allocate AUXILIARY channel c5 type disk; allocate AUXILIARY channel c6 type disk; DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK; release channel c1; release channel c2; release channel c3; release channel c4; release channel c5; release channel c6; } ``` -- 「现主库 tq1」: 使用`DUPLICATE`开始备库创建 ``` [oracle@tq1: ~]$ rman target sys/Oracle123@tqdb_adg auxiliary sys/Oracle123@tqdb Recovery Manager: Release 19.0.0.0.0 - Production on Sat Mar 14 08:47:32 2020 Version 19.6.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: TQDB (DBID=3966209240) connected to auxiliary database: TQDB (not mounted) RMAN> RMAN> RMAN> RMAN> RMAN> run 2> { 3> allocate channel c1 type disk; 4> allocate channel c2 type disk; 5> allocate channel c3 type disk; 6> allocate AUXILIARY channel c4 type disk; 7> allocate AUXILIARY channel c5 type disk; 8> allocate AUXILIARY channel c6 type disk; 9> DUPLICATE TARGET DATABASE 10> FOR STANDBY 11> FROM ACTIVE DATABASE 12> DORECOVER 13> NOFILENAMECHECK; 14> release channel c1; 15> release channel c2; 16> release channel c3; 17> release channel c4; 18> release channel c5; 19> release channel c6; 20> } using target database control file instead of recovery catalog allocated channel: c1 channel c1: SID=64 device type=DISK allocated channel: c2 channel c2: SID=89 device type=DISK allocated channel: c3 channel c3: SID=94 device type=DISK allocated channel: c4 channel c4: SID=423 instance=tqdb1 device type=DISK allocated channel: c5 channel c5: SID=429 instance=tqdb1 device type=DISK allocated channel: c6 channel c6: SID=441 instance=tqdb1 device type=DISK Starting Duplicate Db at 2020-03-14 08:47:52 current log archived contents of Memory Script: { backup as copy reuse passwordfile auxiliary format '+DATA/TQDB/PASSWORD/pwdtqdb.257.1032337993' ; } executing Memory Script Starting backup at 2020-03-14 08:47:53 Finished backup at 2020-03-14 08:47:54 duplicating Online logs to Oracle Managed File (OMF) location duplicating Datafiles to Oracle Managed File (OMF) location contents of Memory Script: { sql clone "alter system set control_files = ''+DATA/TQDB/CONTROLFILE/current.414.1035017275'' comment= ''Set by RMAN'' scope=spfile"; restore clone from service 'tqdb_adg' standby controlfile; } executing Memory Script sql statement: alter system set control_files = ''+DATA/TQDB/CONTROLFILE/current.414.1035017275'' comment= ''Set by RMAN'' scope=spfile Starting restore at 2020-03-14 08:47:54 channel c4: starting datafile backup set restore channel c4: using network backup set from service tqdb_adg channel c4: restoring control file channel c4: restore complete, elapsed time: 00:00:03 output file name=+DATA/TQDB/CONTROLFILE/current.416.1035017275 Finished restore at 2020-03-14 08:47:58 contents of Memory Script: { sql clone 'alter database mount standby database'; } executing Memory Script sql statement: alter database mount standby database contents of Memory Script: { set newname for clone tempfile 1 to new; switch clone tempfile all; set newname for clone datafile 1 to new; set newname for clone datafile 2 to new; set newname for clone datafile 3 to new; set newname for clone datafile 4 to new; set newname for clone datafile 5 to new; set newname for clone datafile 6 to new; restore from nonsparse from service 'tqdb_adg' clone database ; sql 'alter system archive log current'; } executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to +DATA in control file executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting restore at 2020-03-14 08:48:04 channel c4: starting datafile backup set restore channel c4: using network backup set from service tqdb_adg channel c4: specifying datafile(s) to restore from backup set channel c4: restoring datafile 00001 to +DATA channel c5: starting datafile backup set restore channel c5: using network backup set from service tqdb_adg channel c5: specifying datafile(s) to restore from backup set channel c5: restoring datafile 00002 to +DATA channel c6: starting datafile backup set restore channel c6: using network backup set from service tqdb_adg channel c6: specifying datafile(s) to restore from backup set channel c6: restoring datafile 00003 to +DATA channel c6: restore complete, elapsed time: 00:00:03 channel c6: starting datafile backup set restore channel c6: using network backup set from service tqdb_adg channel c6: specifying datafile(s) to restore from backup set channel c6: restoring datafile 00004 to +DATA channel c6: restore complete, elapsed time: 00:00:02 channel c6: starting datafile backup set restore channel c6: using network backup set from service tqdb_adg channel c6: specifying datafile(s) to restore from backup set channel c6: restoring datafile 00005 to +DATA channel c6: restore complete, elapsed time: 00:00:01 channel c6: starting datafile backup set restore channel c6: using network backup set from service tqdb_adg channel c6: specifying datafile(s) to restore from backup set channel c6: restoring datafile 00006 to +DATA channel c6: restore complete, elapsed time: 00:00:01 channel c4: restore complete, elapsed time: 00:00:33 channel c5: restore complete, elapsed time: 00:00:54 Finished restore at 2020-03-14 08:48:58 sql statement: alter system archive log current current log archived contents of Memory Script: { restore clone force from service 'tqdb_adg' archivelog from scn 5829146; switch clone datafile all; } executing Memory Script Starting restore at 2020-03-14 08:48:58 channel c4: starting archived log restore to default destination channel c4: using network backup set from service tqdb_adg channel c4: restoring archived log archived log thread=1 sequence=5 channel c5: starting archived log restore to default destination channel c5: using network backup set from service tqdb_adg channel c5: restoring archived log archived log thread=1 sequence=6 channel c4: restore complete, elapsed time: 00:00:01 channel c5: restore complete, elapsed time: 00:00:01 Finished restore at 2020-03-14 08:49:00 datafile 1 switched to datafile copy input datafile copy RECID=14 STAMP=1035017340 file name=+DATA/TQDB/DATAFILE/system.417.1035017285 datafile 2 switched to datafile copy input datafile copy RECID=15 STAMP=1035017340 file name=+DATA/TQDB/DATAFILE/sysaux.418.1035017285 datafile 3 switched to datafile copy input datafile copy RECID=16 STAMP=1035017340 file name=+DATA/TQDB/DATAFILE/undotbs1.419.1035017285 datafile 4 switched to datafile copy input datafile copy RECID=17 STAMP=1035017340 file name=+DATA/TQDB/DATAFILE/undotbs2.420.1035017289 datafile 5 switched to datafile copy input datafile copy RECID=18 STAMP=1035017340 file name=+DATA/TQDB/DATAFILE/users.421.1035017291 datafile 6 switched to datafile copy input datafile copy RECID=19 STAMP=1035017340 file name=+DATA/TQDB/DATAFILE/tq.422.1035017293 contents of Memory Script: { set until scn 5829367; recover standby clone database delete archivelog ; } executing Memory Script executing command: SET until clause Starting recover at 2020-03-14 08:49:01 starting media recovery archived log for thread 1 with sequence 5 is already on disk as file +DATA/archivelog/1_5_1034983633.arc archived log for thread 1 with sequence 6 is already on disk as file +DATA/archivelog/1_6_1034983633.arc archived log file name=+DATA/archivelog/1_5_1034983633.arc thread=1 sequence=5 archived log file name=+DATA/archivelog/1_6_1034983633.arc thread=1 sequence=6 media recovery complete, elapsed time: 00:00:00 Finished recover at 2020-03-14 08:49:02 contents of Memory Script: { delete clone force archivelog all; } executing Memory Script deleted archived log archived log file name=+DATA/archivelog/1_5_1034983633.arc RECID=1 STAMP=1035017339 Deleted 1 objects deleted archived log archived log file name=+DATA/archivelog/1_6_1034983633.arc RECID=2 STAMP=1035017339 Deleted 1 objects Finished Duplicate Db at 2020-03-14 08:49:06 released channel: c1 released channel: c2 released channel: c3 released channel: c4 released channel: c5 released channel: c6 RMAN> RMAN> RMAN> quit Recovery Manager complete. [oracle@tq1: ~]$ ``` -- 「现备库 RAC」RAC 节点1 : open 数据库,开启MRP ``` 08:52:14 idle(tqdb21)> conn / as sysdba Connected. 08:52:17 idle(tqdb21)> alter database open; Database altered. 08:52:27 idle(tqdb21)> conn / as sysdba Connected. 08:52:44 sys@TQDB(tqdb21)> 08:54:18 sys@TQDB(tqdb21)> -- 查看DG的基本统计信息 @standby 08:54:25 sys@TQDB(tqdb21)> set linesize 200; 08:54:25 sys@TQDB(tqdb21)> col name for a25; 08:54:25 sys@TQDB(tqdb21)> column value format a20; 08:54:25 sys@TQDB(tqdb21)> select * from v$dataguard_stats; SOURCE_DBID SOURCE_DB_UNIQUE_NAME NAME VALUE UNIT TIME_COMPUTED DATUM_TIME CON_ID ----------- -------------------------------- ------------------------- -------------------- ------------------------------ ------------------------------ ------------------------------ ---------- 0 transport lag +00 00:00:00 day(2) to second(0) interval 03/14/2020 08:54:25 03/14/2020 08:54:24 0 0 apply lag day(2) to second(0) interval 03/14/2020 08:54:25 0 0 apply finish time day(2) to second(3) interval 03/14/2020 08:54:25 0 0 estimated startup time 20 second 03/14/2020 08:54:25 0 08:54:25 sys@TQDB(tqdb21)> alter database recover managed standby database disconnect from session; Database altered. 08:55:24 sys@TQDB(tqdb21)> -- 查看DG的基本统计信息 @standby 08:55:48 sys@TQDB(tqdb21)> set linesize 200; 08:55:48 sys@TQDB(tqdb21)> col name for a25; 08:55:48 sys@TQDB(tqdb21)> column value format a20; 08:55:48 sys@TQDB(tqdb21)> select * from v$dataguard_stats; SOURCE_DBID SOURCE_DB_UNIQUE_NAME NAME VALUE UNIT TIME_COMPUTED DATUM_TIME CON_ID ----------- -------------------------------- ------------------------- -------------------- ------------------------------ ------------------------------ ------------------------------ ---------- 0 transport lag +00 00:00:00 day(2) to second(0) interval 03/14/2020 08:55:48 03/14/2020 08:55:46 0 0 apply lag +00 00:00:00 day(2) to second(0) interval 03/14/2020 08:55:48 03/14/2020 08:55:46 0 0 apply finish time day(2) to second(3) interval 03/14/2020 08:55:48 0 0 estimated startup time 20 second 03/14/2020 08:55:48 0 08:55:48 sys@TQDB(tqdb21)> 08:55:48 sys@TQDB(tqdb21)> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID ---------- ---------- ---------- ---------- ---------- ---------- --- --------------- ------------- ------------------- ------------ ------------------- ---------- 1 1 0 209715200 512 1 NO CURRENT 5829146 2020-03-14 08:47:53 9.2954E+18 0 2 1 0 209715200 512 1 YES UNUSED 5826540 2020-03-14 08:29:01 5829146 2020-03-14 08:47:53 0 3 2 0 209715200 512 1 YES UNUSED 5737108 2020-03-13 23:27:13 5737389 2020-03-13 23:30:57 0 4 2 0 209715200 512 1 YES UNUSED 0 0 0 08:56:59 sys@TQDB(tqdb21)> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ CON_ID ---------- --------------- ------- ------------------------------------------------------------ --- ---------- 1 ONLINE +DATA/TQDB/ONLINELOG/group_1.424.1035017343 NO 0 2 ONLINE +DATA/TQDB/ONLINELOG/group_2.423.1035017343 NO 0 3 ONLINE +DATA/TQDB/ONLINELOG/group_3.425.1035017345 NO 0 4 ONLINE +DATA/TQDB/ONLINELOG/group_4.426.1035017345 NO 0 5 STANDBY +DATA/TQDB/ONLINELOG/group_5.427.1035017345 NO 0 6 STANDBY +DATA/TQDB/ONLINELOG/group_6.428.1035017345 NO 0 7 STANDBY +DATA/TQDB/ONLINELOG/group_7.429.1035017345 NO 0 8 STANDBY +DATA/TQDB/ONLINELOG/group_8.430.1035017345 NO 0 9 STANDBY +DATA/TQDB/ONLINELOG/group_9.431.1035017347 NO 0 10 STANDBY +DATA/TQDB/ONLINELOG/group_10.432.1035017347 NO 0 10 rows selected. 08:57:02 sys@TQDB(tqdb21)> -- 数据文件存放路径 08:57:10 sys@TQDB(tqdb21)> col file_name format a70; 08:57:10 sys@TQDB(tqdb21)> set linesize 200; 08:57:10 sys@TQDB(tqdb21)> select tablespace_name, file_id, bytes / 1024 / 1024 as "Size(MB)", file_name 08:57:10 2 from dba_data_files 08:57:10 3 order by file_id; TABLESPACE_NAME FILE_ID Size(MB) FILE_NAME ------------------------------ ---------- ---------- ---------------------------------------------------------------------- SYSTEM 1 700 +DATA/TQDB/DATAFILE/system.417.1035017285 SYSAUX 2 1240 +DATA/TQDB/DATAFILE/sysaux.418.1035017285 UNDOTBS1 3 250 +DATA/TQDB/DATAFILE/undotbs1.419.1035017285 UNDOTBS2 4 200 +DATA/TQDB/DATAFILE/undotbs2.420.1035017289 USERS 5 5 +DATA/TQDB/DATAFILE/users.421.1035017291 TQ 6 20 +DATA/TQDB/DATAFILE/tq.422.1035017293 6 rows selected. -- 检查 `failover` 后,新创建的表 (COPY_DBA_OBJECTS_2) 的数据已经复制过来了。 08:57:10 sys@TQDB(tqdb21)> conn tq/tq Connected. 08:57:23 tq@TQDB(tqdb21)> set lines 200 08:57:28 tq@TQDB(tqdb21)> select * from tab; TNAME TABTYPE CLUSTERID --------------------------------------------- ------------- ---------- COPY_DBA_OBJECTS_2 TABLE COPY_DBA_OBJECTS TABLE 08:57:30 tq@TQDB(tqdb21)> select count(*) from COPY_DBA_OBJECTS; COUNT(*) ---------- 47158 08:57:42 tq@TQDB(tqdb21)> select count(*) from COPY_DBA_OBJECTS_2; COUNT(*) ---------- 47158 09:01:30 sys@TQDB(tqdb21)> set lines 200 09:01:35 sys@TQDB(tqdb21)> select * from v$controlfile; STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS CON_ID -------- -------------------------------------------------- --- ---------- -------------- ---------- +DATA/TQDB/CONTROLFILE/current.416.1035017275 NO 16384 2792 0 09:01:36 sys@TQDB(tqdb21)> 09:04:40 sys@TQDB(tqdb21)> create spfile='+DATA/TQDB/spfiletqdb_standby_20200314.ora' from memory; File created. 09:06:35 sys@TQDB(tqdb21)> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. 09:09:50 sys@TQDB(tqdb21)> quit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.6.0.0.0 [oracle@tqdb21: /tmp]$ ``` ``` 10:56:27 sys@TQDB(tqdb21)> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string +DATA/TQDB/spfiletqdb_standby_ 20200314_1..ora 10:56:50 sys@TQDB(tqdb21)> ``` -- 「新备库」RAC 节点1 pfile ```/tmp/init_tqdb21_new_ADG.sql [oracle@tqdb21: /tmp]$ cat /tmp/init_tqdb21_new_ADG.sql *.audit_file_dest='/u01/app/oracle/admin/tqdb/adump' *.db_unique_name='tqdb' *.log_archive_config='DG_CONFIG=(tqdb,tqdb_adg)' *.log_archive_dest_1='LOCATION=+DATA/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=tqdb' *.log_archive_dest_2='SERVICE=tqdb_adg ASYNC LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=tqdb_adg' *.log_archive_dest_state_1='enable' *.log_archive_dest_state_2='enable' *.log_archive_format='%t_%s_%r.arc' *.standby_file_management='AUTO' *.fal_client='tqdb' *.fal_server='tqdb_adg' *.control_files='+DATA/TQDB/CONTROLFILE/current.416.1035017275' # Restore Controlfile *.db_create_file_dest='+DATA' *.db_name='tqdb' *.pga_aggregate_target=262m *.processes=300 *.sga_target=783m *.db_block_size=8192 *.compatible="19.0.0" *.audit_trail="DB" *.open_cursors=300 *._optimizer_use_auto_indexes="OFF" *.cluster_database=TRUE *.diagnostic_dest='/u01/app/oracle' tqdb2.instance_number=2 tqdb1.instance_number=1 *.nls_language='AMERICAN' *.nls_territory='AMERICA' *.remote_login_passwordfile='exclusive' tqdb2.thread=2 tqdb1.thread=1 *.undo_tablespace='UNDOTBS1' tqdb2.undo_tablespace='UNDOTBS2' tqdb1.undo_tablespace='UNDOTBS1' tqdb1.instance_name=tqdb1 tqdb2.instance_name=tqdb2 tqdb1.instance_number=1 tqdb2.instance_number=2 tqdb1.local_listener='(address=(protocol=TCP)(HOST=192.168.6.23)(PORT=1521))' tqdb1.remote_listener='(address=(protocol=TCP)(HOST=192.168.6.20)(PORT=1521))' tqdb2.local_listener='(address=(protocol=TCP)(HOST=192.168.6.24)(PORT=1521))' tqdb2.remote_listener='(address=(protocol=TCP)(HOST=192.168.6.20)(PORT=1521))' [oracle@tqdb21: /tmp]$ ``` -- 「新备库」RAC 节点2 pfile ``` [oracle@tqdb22: /tmp]$ cat init_tqdb22_new_ADG.sql *.audit_file_dest='/u01/app/oracle/admin/tqdb/adump' *.db_unique_name='tqdb' *.log_archive_config='DG_CONFIG=(tqdb,tqdb_adg)' *.log_archive_dest_1='LOCATION=+DATA/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=tqdb' *.log_archive_dest_2='SERVICE=tqdb_adg ASYNC LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=tqdb_adg' *.log_archive_dest_state_1='enable' *.log_archive_dest_state_2='enable' *.log_archive_format='%t_%s_%r.arc' *.standby_file_management='AUTO' *.fal_client='tqdb' *.fal_server='tqdb_adg' *.control_files='+DATA/TQDB/CONTROLFILE/current.416.1035017275' # Restore Controlfile *.db_create_file_dest='+DATA' *.db_name='tqdb' *.pga_aggregate_target=262m *.processes=300 *.sga_target=783m *.db_block_size=8192 *.compatible="19.0.0" *.audit_trail="DB" *.open_cursors=300 *._optimizer_use_auto_indexes="OFF" *.cluster_database=TRUE *.diagnostic_dest='/u01/app/oracle' tqdb2.instance_number=2 tqdb1.instance_number=1 *.nls_language='AMERICAN' *.nls_territory='AMERICA' *.remote_login_passwordfile='exclusive' tqdb2.thread=2 tqdb1.thread=1 *.undo_tablespace='UNDOTBS1' tqdb2.undo_tablespace='UNDOTBS2' tqdb1.undo_tablespace='UNDOTBS1' tqdb1.instance_name=tqdb1 tqdb2.instance_name=tqdb2 tqdb1.instance_number=1 tqdb2.instance_number=2 tqdb1.local_listener='(address=(protocol=TCP)(HOST=192.168.6.23)(PORT=1521))' tqdb1.remote_listener='(address=(protocol=TCP)(HOST=192.168.6.20)(PORT=1521))' tqdb2.local_listener='(address=(protocol=TCP)(HOST=192.168.6.24)(PORT=1521))' tqdb2.remote_listener='(address=(protocol=TCP)(HOST=192.168.6.20)(PORT=1521))' [oracle@tqdb22: /tmp]$ ``` -- 「现主库 tq1」 tnsnames 别名,恢复节点二 `(ADDRESS = (PROTOCOL = TCP)(HOST = tqdb22-vip)(PORT = 1521))` ``` [oracle@tq1: /u01/app/oracle/product/19c/dbhome/network/admin]$ cat tnsnames.ora TQ1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = tq1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = tq1) ) ) # 备库(tq1)添加两个别名 `tqdb` 和 `tqdb_adg` tqdb = (DESCRIPTION = #(ADDRESS = (PROTOCOL = TCP)(HOST = tqdb-cluster-scan)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = tqdb21-vip)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = tqdb22-vip)(PORT = 1521)) (LOAD_BALANCE = yes) (FAILOVER = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = tqdb) (UR=A) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5) ) ) ) tqdb_adg = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = tq1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = tqdb_adg) ) ) [oracle@tq1: /u01/app/oracle/product/19c/dbhome/network/admin]$ ``` 至此,已重新搭建 Active Data Guard 架构。 「现主库 tq1」 -->> 「现备库 RAC」
17.4 将「17.3」进行 switchover
,恢复 data guard
架构为 「主库 RAC」-->> 「备库 tq1」
将重新搭建 Active Data Guard 架构。
「现主库 tq1」 -->> 「现备库 RAC」
switchover
到初始data guard
关系:「主库 RAC」-->> 「备库 tq1」
Switchover Steps
Primary Side
SQL> alter system archive log current; SQL> alter database commit to switchover to standby with session shutdown; SQL> shutdown immediate; SQL> startup mount;
Data Guard Side
SQL> alter database recover managed standby database cancel; SQL> alter database commit to switchover to primary with session shutdown; SQL> shutdown immediate; SQL> startup;
Primary Side
SQL> alter database recover managed standby database disconnect; If you create standby log files you can use real time apply with below command. SQL> alter database open read only; SQL> alter database recover managed standby database using current logfile disconnect;
操作记录:
-- 0.「现主库 tq1」当前状态 12:12:57 sys@TQDB(tq1)> -- 12:12:57 sys@TQDB(tq1)> set linesize 200; 12:12:57 sys@TQDB(tq1)> col HOST_NAME for a10; 12:12:57 sys@TQDB(tq1)> -- 12:12:58 sys@TQDB(tq1)> select db.INST_ID, db.DBID, inst.INSTANCE_NAME, inst.HOST_NAME, db.OPEN_MODE, db.PROTECTION_MODE, db.DATABASE_ROLE, db.DB_UNIQUE_NAME 12:12:58 2 from gv$database db, gv$instance inst 12:12:58 3 where db.INST_ID = inst.INST_ID 12:12:58 4 ; INST_ID DBID INSTANCE_NAME HOST_NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE DB_UNIQUE_NAME ---------- ---------- ---------------- ---------- -------------------- -------------------- ---------------- ------------------------------ 1 3966209240 tqdb_adg tq1 READ WRITE MAXIMUM PERFORMANCE PRIMARY tqdb_adg 12:12:58 sys@TQDB(tq1)> -- 0.「现备库 RAC」当前状态 12:13:33 sys@TQDB(tqdb21)> -- 12:13:34 sys@TQDB(tqdb21)> set linesize 200; 12:13:34 sys@TQDB(tqdb21)> col HOST_NAME for a10; 12:13:34 sys@TQDB(tqdb21)> -- 12:13:34 sys@TQDB(tqdb21)> select db.INST_ID, db.DBID, inst.INSTANCE_NAME, inst.HOST_NAME, db.OPEN_MODE, db.PROTECTION_MODE, db.DATABASE_ROLE, db.DB_UNIQUE_NAME 12:13:34 2 from gv$database db, gv$instance inst 12:13:34 3 where db.INST_ID = inst.INST_ID 12:13:34 4 ; INST_ID DBID INSTANCE_NAME HOST_NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE DB_UNIQUE_NAME ---------- ---------- ---------------- ---------- -------------------- -------------------- ---------------- ------------------------------ 1 3966209240 tqdb1 tqdb21 READ ONLY WITH APPLY MAXIMUM PERFORMANCE PHYSICAL STANDBY tqdb 2 3966209240 tqdb2 tqdb22 READ ONLY WITH APPLY MAXIMUM PERFORMANCE PHYSICAL STANDBY tqdb 12:13:34 sys@TQDB(tqdb21)> -- 1. 「现主库 tq1」当前状态 12:15:47 sys@TQDB(tq1)> alter system archive log current; System altered. 12:16:00 sys@TQDB(tq1)> 12:18:57 sys@TQDB(tq1)> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID ---------- ---------- ---------- ---------- ---------- ---------- --- --------------- ------------- ------------------- ------------ ------------------- ---------- 1 1 11 209715200 512 1 NO CURRENT 5863546 2020-03-14 12:18:57 9.2954E+18 0 2 1 10 209715200 512 1 YES ACTIVE 5863516 2020-03-14 12:18:43 5863546 2020-03-14 12:18:57 0 3 2 1 209715200 512 1 YES INACTIVE 5737108 2020-03-13 23:27:13 5737389 2020-03-13 23:30:57 0 4 2 0 209715200 512 1 YES UNUSED 0 0 0 12:18:59 sys@TQDB(tq1)> 12:19:23 sys@TQDB(tq1)> 12:19:32 sys@TQDB(tq1)> 12:19:32 sys@TQDB(tq1)> 12:19:32 sys@TQDB(tq1)> alter database commit to switchover to standby with session shutdown; ERROR: ORA-01034: ORACLE not available Process ID: 4758 Session ID: 89 Serial number: 54496 Database altered. 12:20:06 sys@TQDB(tq1)> conn / as sysdba Connected to an idle instance. 12:20:50 idle(tq1)> 12:21:19 idle(tq1)> shutdown immediate; ERROR: ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist Linux-x86_64 Error: 2: No such file or directory Additional information: 4376 Additional information: -1183956957 Process ID: 0 Session ID: 0 Serial number: 0 12:21:27 idle(tq1)> startup mount; ORACLE instance started. Total System Global Area 1191181696 bytes Fixed Size 8895872 bytes Variable Size 318767104 bytes Database Buffers 855638016 bytes Redo Buffers 7880704 bytes Database mounted. 12:21:52 idle(tq1)> 12:23:41 idle(tq1)> set linesize 200; 12:23:41 idle(tq1)> col HOST_NAME for a10; 12:23:41 idle(tq1)> -- 12:23:41 idle(tq1)> select db.INST_ID, db.DBID, inst.INSTANCE_NAME, inst.HOST_NAME, db.OPEN_MODE, db.PROTECTION_MODE, db.DATABASE_ROLE, db.DB_UNIQUE_NAME 12:23:41 2 from gv$database db, gv$instance inst 12:23:41 3 where db.INST_ID = inst.INST_ID 12:23:41 4 ; INST_ID DBID INSTANCE_NAME HOST_NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE DB_UNIQUE_NAME ---------- ---------- ---------------- ---------- -------------------- -------------------- ---------------- ------------------------------ 1 3966209240 tqdb_adg tq1 MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY tqdb_adg 12:23:42 idle(tq1)> -- 2. 「现备库 RAC」 -- 「现备库 RAC」 节点1 12:24:23 sys@TQDB(tqdb21)> -- 12:24:23 sys@TQDB(tqdb21)> set linesize 200; 12:24:23 sys@TQDB(tqdb21)> col HOST_NAME for a10; 12:24:23 sys@TQDB(tqdb21)> -- 12:24:23 sys@TQDB(tqdb21)> select db.INST_ID, db.DBID, inst.INSTANCE_NAME, inst.HOST_NAME, db.OPEN_MODE, db.PROTECTION_MODE, db.DATABASE_ROLE, db.DB_UNIQUE_NAME 12:24:23 2 from gv$database db, gv$instance inst 12:24:23 3 where db.INST_ID = inst.INST_ID 12:24:23 4 ; INST_ID DBID INSTANCE_NAME HOST_NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE DB_UNIQUE_NAME ---------- ---------- ---------------- ---------- -------------------- -------------------- ---------------- ------------------------------ 1 3966209240 tqdb1 tqdb21 READ ONLY WITH APPLY MAXIMUM PERFORMANCE PHYSICAL STANDBY tqdb 2 3966209240 tqdb2 tqdb22 READ ONLY WITH APPLY MAXIMUM PERFORMANCE PHYSICAL STANDBY tqdb 12:24:25 sys@TQDB(tqdb21)> 12:24:56 sys@TQDB(tqdb21)> alter database recover managed standby database cancel; Database altered. 12:25:18 sys@TQDB(tqdb21)> 12:25:18 sys@TQDB(tqdb21)> -- 12:26:24 sys@TQDB(tqdb21)> set linesize 200; 12:26:24 sys@TQDB(tqdb21)> col HOST_NAME for a10; 12:26:24 sys@TQDB(tqdb21)> -- 12:26:24 sys@TQDB(tqdb21)> select db.INST_ID, db.DBID, inst.INSTANCE_NAME, inst.HOST_NAME, db.OPEN_MODE, db.PROTECTION_MODE, db.DATABASE_ROLE, db.DB_UNIQUE_NAME 12:26:24 2 from gv$database db, gv$instance inst 12:26:24 3 where db.INST_ID = inst.INST_ID 12:26:24 4 ; INST_ID DBID INSTANCE_NAME HOST_NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE DB_UNIQUE_NAME ---------- ---------- ---------------- ---------- -------------------- -------------------- ---------------- ------------------------------ 1 3966209240 tqdb1 tqdb21 READ ONLY MAXIMUM PERFORMANCE PHYSICAL STANDBY tqdb 2 3966209240 tqdb2 tqdb22 READ ONLY MAXIMUM PERFORMANCE PHYSICAL STANDBY tqdb 12:26:24 sys@TQDB(tqdb21)> -- 「现备库 RAC」 节点2 12:27:16 sys@TQDB(tqdb22)> -- 12:27:18 sys@TQDB(tqdb22)> set linesize 200; 12:27:18 sys@TQDB(tqdb22)> col HOST_NAME for a10; 12:27:18 sys@TQDB(tqdb22)> -- 12:27:18 sys@TQDB(tqdb22)> select db.INST_ID, db.DBID, inst.INSTANCE_NAME, inst.HOST_NAME, db.OPEN_MODE, db.PROTECTION_MODE, db.DATABASE_ROLE, db.DB_UNIQUE_NAME 12:27:18 2 from gv$database db, gv$instance inst 12:27:18 3 where db.INST_ID = inst.INST_ID 12:27:18 4 ; INST_ID DBID INSTANCE_NAME HOST_NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE DB_UNIQUE_NAME ---------- ---------- ---------------- ---------- -------------------- -------------------- ---------------- ------------------------------ 2 3966209240 tqdb2 tqdb22 READ ONLY MAXIMUM PERFORMANCE PHYSICAL STANDBY tqdb 1 3966209240 tqdb1 tqdb21 READ ONLY MAXIMUM PERFORMANCE PHYSICAL STANDBY tqdb 12:27:18 sys@TQDB(tqdb22)> -- 「现备库 RAC」 节点1 12:28:04 sys@TQDB(tqdb21)> alter database commit to switchover to primary with session shutdown; Database altered. 12:28:44 sys@TQDB(tqdb21)> 12:29:28 sys@TQDB(tqdb21)> -- 12:29:29 sys@TQDB(tqdb21)> set linesize 200; 12:29:29 sys@TQDB(tqdb21)> col HOST_NAME for a10; 12:29:29 sys@TQDB(tqdb21)> -- 12:29:29 sys@TQDB(tqdb21)> select db.INST_ID, db.DBID, inst.INSTANCE_NAME, inst.HOST_NAME, db.OPEN_MODE, db.PROTECTION_MODE, db.DATABASE_ROLE, db.DB_UNIQUE_NAME 12:29:29 2 from gv$database db, gv$instance inst 12:29:29 3 where db.INST_ID = inst.INST_ID 12:29:29 4 ; INST_ID DBID INSTANCE_NAME HOST_NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE DB_UNIQUE_NAME ---------- ---------- ---------------- ---------- -------------------- -------------------- ---------------- ------------------------------ 1 3966209240 tqdb1 tqdb21 MOUNTED MAXIMUM PERFORMANCE PRIMARY tqdb 2 3966209240 tqdb2 tqdb22 MOUNTED MAXIMUM PERFORMANCE PRIMARY tqdb 12:29:29 sys@TQDB(tqdb21)> 12:29:29 sys@TQDB(tqdb21)> conn / as sysdba Connected. 12:30:26 idle(tqdb21)> -- 「现备库 RAC」 节点2 12:30:17 sys@TQDB(tqdb22)> conn / as sysdba Connected. 12:30:50 idle(tqdb22)> -- 「现备库 RAC」 节点1 12:31:16 idle(tqdb21)> shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. 12:31:43 idle(tqdb21)> -- 「现备库 RAC」 节点2 12:30:17 sys@TQDB(tqdb22)> conn / as sysdba Connected. 12:30:50 idle(tqdb22)> shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. 12:33:37 idle(tqdb22)> -- 「现备库 RAC」 节点1 12:34:21 idle(tqdb21)> startup ORACLE instance started. Total System Global Area 822080768 bytes Fixed Size 8901888 bytes Variable Size 289406976 bytes Database Buffers 520093696 bytes Redo Buffers 3678208 bytes Database mounted. Database opened. 12:35:00 idle(tqdb21)> conn / as sysdba Connected. 12:35:11 sys@TQDB(tqdb21)> 12:36:46 sys@TQDB(tqdb21)> -- 12:36:46 sys@TQDB(tqdb21)> set linesize 200; 12:36:46 sys@TQDB(tqdb21)> col HOST_NAME for a10; 12:36:46 sys@TQDB(tqdb21)> -- 12:36:46 sys@TQDB(tqdb21)> select db.INST_ID, db.DBID, inst.INSTANCE_NAME, inst.HOST_NAME, db.OPEN_MODE, db.PROTECTION_MODE, db.DATABASE_ROLE, db.DB_UNIQUE_NAME 12:36:47 2 from gv$database db, gv$instance inst 12:36:47 3 where db.INST_ID = inst.INST_ID 12:36:47 4 ; INST_ID DBID INSTANCE_NAME HOST_NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE DB_UNIQUE_NAME ---------- ---------- ---------------- ---------- -------------------- -------------------- ---------------- ------------------------------ 1 3966209240 tqdb1 tqdb21 READ WRITE MAXIMUM PERFORMANCE PRIMARY tqdb 2 3966209240 tqdb2 tqdb22 READ WRITE MAXIMUM PERFORMANCE PRIMARY tqdb 12:36:47 sys@TQDB(tqdb21)> -- 「现备库 RAC」 节点2 12:34:43 idle(tqdb22)> startup ORACLE instance started. Total System Global Area 822080768 bytes Fixed Size 8901888 bytes Variable Size 293601280 bytes Database Buffers 515899392 bytes Redo Buffers 3678208 bytes Database mounted. Database opened. 12:35:13 idle(tqdb22)> conn / as sysdba Connected. 12:35:35 sys@TQDB(tqdb22)> 12:37:13 sys@TQDB(tqdb22)> -- 12:37:14 sys@TQDB(tqdb22)> set linesize 200; 12:37:14 sys@TQDB(tqdb22)> col HOST_NAME for a10; 12:37:14 sys@TQDB(tqdb22)> -- 12:37:14 sys@TQDB(tqdb22)> select db.INST_ID, db.DBID, inst.INSTANCE_NAME, inst.HOST_NAME, db.OPEN_MODE, db.PROTECTION_MODE, db.DATABASE_ROLE, db.DB_UNIQUE_NAME 12:37:14 2 from gv$database db, gv$instance inst 12:37:14 3 where db.INST_ID = inst.INST_ID 12:37:14 4 ; INST_ID DBID INSTANCE_NAME HOST_NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE DB_UNIQUE_NAME ---------- ---------- ---------------- ---------- -------------------- -------------------- ---------------- ------------------------------ 2 3966209240 tqdb2 tqdb22 READ WRITE MAXIMUM PERFORMANCE PRIMARY tqdb 1 3966209240 tqdb1 tqdb21 READ WRITE MAXIMUM PERFORMANCE PRIMARY tqdb 12:37:14 sys@TQDB(tqdb22)> [grid@tqdb21: ~]$ crsctl stat res -t -------------------------------------------------------------------------------- Name Target State Server State details -------------------------------------------------------------------------------- Local Resources -------------------------------------------------------------------------------- ora.LISTENER.lsnr ONLINE ONLINE tqdb21 STABLE ONLINE ONLINE tqdb22 STABLE ora.chad ONLINE ONLINE tqdb21 STABLE ONLINE ONLINE tqdb22 STABLE ora.net1.network ONLINE ONLINE tqdb21 STABLE ONLINE ONLINE tqdb22 STABLE ora.ons ONLINE ONLINE tqdb21 STABLE ONLINE ONLINE tqdb22 STABLE -------------------------------------------------------------------------------- Cluster Resources -------------------------------------------------------------------------------- ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup) 1 ONLINE ONLINE tqdb21 STABLE 2 ONLINE ONLINE tqdb22 STABLE 3 ONLINE OFFLINE STABLE ora.DATA.dg(ora.asmgroup) 1 ONLINE ONLINE tqdb21 STABLE 2 ONLINE ONLINE tqdb22 STABLE 3 OFFLINE OFFLINE STABLE ora.LISTENER_SCAN1.lsnr 1 ONLINE ONLINE tqdb21 STABLE ora.OCR.dg(ora.asmgroup) 1 ONLINE ONLINE tqdb21 STABLE 2 ONLINE ONLINE tqdb22 STABLE 3 OFFLINE OFFLINE STABLE ora.asm(ora.asmgroup) 1 ONLINE ONLINE tqdb21 Started,STABLE 2 ONLINE ONLINE tqdb22 Started,STABLE 3 OFFLINE OFFLINE STABLE ora.asmnet1.asmnetwork(ora.asmgroup) 1 ONLINE ONLINE tqdb21 STABLE 2 ONLINE ONLINE tqdb22 STABLE 3 OFFLINE OFFLINE STABLE ora.cvu 1 ONLINE ONLINE tqdb21 STABLE ora.qosmserver 1 ONLINE ONLINE tqdb21 STABLE ora.scan1.vip 1 ONLINE ONLINE tqdb21 STABLE ora.tqdb.db 1 ONLINE ONLINE tqdb21 Open,HOME=/u01/app/o racle/product/19c/db home,STABLE 2 ONLINE ONLINE tqdb22 Open,HOME=/u01/app/o racle/product/19c/db home,STABLE ora.tqdb21.vip 1 ONLINE ONLINE tqdb21 STABLE ora.tqdb22.vip 1 ONLINE ONLINE tqdb22 STABLE -------------------------------------------------------------------------------- [grid@tqdb21: ~]$ -- 3. 「现主库 tq1」 12:39:15 idle(tq1)> alter database open; Database altered. 12:39:24 idle(tq1)> 12:39:39 idle(tq1)> alter database recover managed standby database disconnect from session; Database altered. 12:40:05 idle(tq1)> conn / as sysdba Connected. 12:40:12 sys@TQDB(tq1)> 12:40:13 sys@TQDB(tq1)> -- 12:40:19 sys@TQDB(tq1)> set linesize 200; 12:40:19 sys@TQDB(tq1)> col HOST_NAME for a10; 12:40:19 sys@TQDB(tq1)> -- 12:40:19 sys@TQDB(tq1)> select db.INST_ID, db.DBID, inst.INSTANCE_NAME, inst.HOST_NAME, db.OPEN_MODE, db.PROTECTION_MODE, db.DATABASE_ROLE, db.DB_UNIQUE_NAME 12:40:19 2 from gv$database db, gv$instance inst 12:40:19 3 where db.INST_ID = inst.INST_ID 12:40:19 4 ; INST_ID DBID INSTANCE_NAME HOST_NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE DB_UNIQUE_NAME ---------- ---------- ---------------- ---------- -------------------- -------------------- ---------------- ------------------------------ 1 3966209240 tqdb_adg tq1 READ ONLY WITH APPLY MAXIMUM PERFORMANCE PHYSICAL STANDBY tqdb_adg 12:40:19 sys@TQDB(tq1)>
「17.3」将重新搭建 Active Data Guard 架构。
「现主库 tq1」 -->> 「现备库 RAC」
至此,已经将 switchover
到初始 data guard
关系:
「主库 RAC」-->> 「备库 tq1」
总结:我们通过下列几篇文章,演示了从 Oracle 19c RAC 安装 以及 升级 RU 到 搭建 Oracle MAA: Oracle 19c RAC + ADG 再到 Oracle 19c RAC + ADG 手动 switchover 角色转换步骤 以及 Oracle 19c RAC + ADG 手动 failover 角色转换步骤 开始一起接触了 Oracle 19c 。对 19c 的学习和研究就从此开始吧。加油!~
-- The End --