Oracle 19c RAC + ADG 手动 failover 角色转换步骤

Oracle 19c RAC + ADG 手动 failover 角色转换步骤

Revision V4.0

No.DateAuthor/ModifierComments
1.02020-03-06谈权初稿:搭建 Oracle MAA: Oracle 19c RAC + ADG
2.02020-03-10谈权增加:16. 手动 switchover 角色转换步骤
3.02020-03-13谈权增加:17. 手动 failover 角色转换步骤
4.02020-03-14谈权完善:「17.3」和「17.4」

接上篇两篇文章(搭建 Oracle MAA: Oracle 19c RAC + ADGOracle 19c RAC + ADG 手动 switchover 角色转换步骤), 本文继续完成 「17. 手动 failover 角色转换步骤」。

17. 手动 failover 角色转换步骤

17.1 Data Guard Side: Standby (single-instance) 进行 failover 切换

如果 data guard 主数据库的情况很糟糕,或者不能用于生产,那么我们可以激活备用数据库作为主生产数据库。

==failover将破坏dataguard模式。需要重新配置dataguard==

Oracle 19c Data Guard Failover Structure-2

Data guard Configuration details:

Environment DetailsPrimary (RAC)Standby (single-instance)
OSCentOS Linux release 7.7.1908 (Core)CentOS Linux release 7.7.1908 (Core)
DB VersionVersion 19.6.0.0.0Version 19.6.0.0.0
DATABASE_ROLEPRIMARYPHYSICAL STANDBY
DB_UNIQUE_NAMEtqdbtqdb_adg

Failover Configuration details:

Environment DetailsPrimary (RAC)Standby (single-instance)
OSCentOS Linux release 7.7.1908 (Core)CentOS Linux release 7.7.1908 (Core)
DB VersionVersion 19.6.0.0.0Version 19.6.0.0.0
DATABASE_ROLEPRIMARYPRIMARY
DB_UNIQUE_NAMEtqdbtqdb_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 架构」)

Oracle 19c Data Guard Renew Structure-1

操作记录:

一、按照先前的方法,利用新主库的备份,将这个原主库重新配置为备库。
生成环境下,一般建议使用「方法一」:将“原主库”重新搭建为“新主库”的`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」

Oracle 19c Data Guard Renew Switchover Structure-2

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 --