文档课题：部署oracle多租户19c rac to 19c rac adg.
1、架构情况
资源配置：cpu 2c/mem 6g
主备硬件配置：
3个2g投票磁盘asm-diskb/c/d
3个15g数据文件磁盘asm-diske/f/g
1个20g的快速恢复区磁盘asm-diskg项目 | Primary db | physical standby db |
db类型 | rac | rac |
db version | 19.13 | 19.13 |
db存储 | ASM | ASM |
主机IP地址/hosts配置 | 192.168.133.161 hisdb1 | 192.168.133.181 hisdbdg1 |
192.168.133.162 hisdb2 | 192.168.133.182 hisdbdg2 | |
192.168.78.161 hisdb1-priv | 192.168.78.181 hisdbdg1-priv | |
192.168.78.162 hisdb2-priv | 192.168.78.182 hisdbdg2-priv | |
192.168.133.173 hisdb1-vip | 192.168.133.183 hisdbdg1-vip | |
192.168.133.174 hisdb2-vip | 192.168.133.184 hisdbdg2-vip | |
192.168.133.175 hisdb-scan | 192.168.133.185 hisdbdg-scan | |
OS版本 | rhel 7.9 64位 | rhel 7.9 64位 |
OS hostname | hisdb1 hisdb2 | hisdbdg1 hisdbdg2 |
字符集 | AMERICAN_AMERICA.AL32UTF8 | AMERICAN_AMERICA.AL32UTF8 |
归档模式 | Archive Mode | Archive Mode |
oracle_sid | tmis1 tmis2 | tmisdg1 tmisdg2 |
db_name/GLOBAL_DBNAME | tmis | tmis |
db_unique_name | tmis | tmisdg |
TNS_NAME | TMIS | TMISDG |
数据文件所在磁盘组 | +DATA | +DATA |
归档路径 | +FRA | +FRA |
ORACLE_HOME | /u01/app/oracle/product/19.13/db_1 | /u01/app/oracle/product/19.13/db_1 |
PDB情况 | TMISPDB、ORCLPDB | TMISPDB、ORCLPDB |
主库信息
[grid@hisdb1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
ONLINE ONLINE hisdb1 STABLE
ONLINE ONLINE hisdb2 STABLE
ora.chad
ONLINE ONLINE hisdb1 STABLE
ONLINE ONLINE hisdb2 STABLE
ora.net1.network
ONLINE ONLINE hisdb1 STABLE
ONLINE ONLINE hisdb2 STABLE
ora.ons
ONLINE ONLINE hisdb1 STABLE
ONLINE ONLINE hisdb2 STABLE
ora.proxy_advm
OFFLINE OFFLINE hisdb1 STABLE
OFFLINE OFFLINE hisdb2 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
1 ONLINE ONLINE hisdb1 STABLE
2 ONLINE ONLINE hisdb2 STABLE
3 ONLINE OFFLINE STABLE
ora.DATA.dg(ora.asmgroup)
1 ONLINE ONLINE hisdb1 STABLE
2 ONLINE ONLINE hisdb2 STABLE
3 OFFLINE OFFLINE STABLE
ora.FRA.dg(ora.asmgroup)
1 ONLINE ONLINE hisdb1 STABLE
2 ONLINE ONLINE hisdb2 STABLE
3 OFFLINE OFFLINE STABLE
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE hisdb1 STABLE
ora.OCR.dg(ora.asmgroup)
1 ONLINE ONLINE hisdb1 STABLE
2 ONLINE ONLINE hisdb2 STABLE
3 OFFLINE OFFLINE STABLE
ora.asm(ora.asmgroup)
1 ONLINE ONLINE hisdb1 Started,STABLE
2 ONLINE ONLINE hisdb2 Started,STABLE
3 OFFLINE OFFLINE STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
1 ONLINE ONLINE hisdb1 STABLE
2 ONLINE ONLINE hisdb2 STABLE
3 OFFLINE OFFLINE STABLE
ora.cvu
1 ONLINE ONLINE hisdb1 STABLE
ora.hisdb1.vip
1 ONLINE ONLINE hisdb1 STABLE
ora.hisdb2.vip
1 ONLINE ONLINE hisdb2 STABLE
ora.orcl.db
1 OFFLINE OFFLINE Instance Shutdown,ST
ABLE
2 OFFLINE OFFLINE Instance Shutdown,ST
ABLE
ora.qosmserver
1 ONLINE ONLINE hisdb1 STABLE
ora.scan1.vip
1 ONLINE ONLINE hisdb1 STABLE
ora.tmis.db
1 ONLINE ONLINE hisdb1 Open,HOME=/u01/app/o
racle/product/19.13/
db_1,STABLE
2 ONLINE ONLINE hisdb2 Open,HOME=/u01/app/o
racle/product/19.13/
db_1,STABLE
--------------------------------------------------------------------------------
说明：当前需给TMIS搭建rac备库.
2、前期准备
2.1、主库准备
2.1.1、归档及强制记录日志
a、force logging + archivelog
b、创建新用户leo用作测试
[oracle@hisdb1 ~]$ export ORACLE_SID=tmis1
[oracle@hisdb1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jan 23 12:53:30 2024
Version 19.13.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0
SQL> alter session set container=tmispdb;
Session altered.
SQL> create user leo identified by leo;
User created.
SQL> grant dba to leo;
Grant succeeded.
SQL> select name,open_mode,log_mode,force_logging,database_role,switchover_status from gv$database;
NAME OPEN_MODE LOG_MODE FORCE_LOGGING DATABASE_ROLE SWITCHOVER_STATUS
--------- -------------------- ------------ ------------------------- ---------------- --------------------
TMIS READ WRITE NOARCHIVELOG NO PRIMARY NOT ALLOWED
TMIS READ WRITE NOARCHIVELOG NO PRIMARY NOT ALLOWED
SQL> conn / as sysdba
Connected.
SQL> alter database force logging;
Database altered.
SQL> select name,open_mode,log_mode,force_logging,database_role,switchover_status from gv$database;
NAME OPEN_MODE LOG_MODE FORCE_LOGGING DATABASE_ROLE SWITCHOVER_STATUS
--------- -------------------- ------------ ------------------------- ---------------- --------------------
TMIS READ WRITE NOARCHIVELOG YES PRIMARY NOT ALLOWED
TMIS READ WRITE NOARCHIVELOG YES PRIMARY NOT ALLOWED
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 4
Current log sequence 5
SQL> host srvctl stop database -d tmis -o immediate;
SQL> host srvctl status database -d tmis
Instance tmis1 is not running on node hisdb1
Instance tmis2 is not running on node hisdb2
SQL> host srvctl start database -d tmis -o mount
SQL> conn / as sysdba
Connected.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination +FRA
Oldest online log sequence 26
Current log sequence 27
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> alter database archivelog;
Database altered.
SQL> select name,open_mode,log_mode,force_logging,database_role,switchover_status from gv$database;
NAME OPEN_MODE LOG_MODE FORCE_LOGGING DATABASE_ROLE SWITCHOVER_STATUS
--------- -------------------- ------------ ------------------------- ---------------- --------------------
TMIS MOUNTED ARCHIVELOG YES PRIMARY NOT ALLOWED
TMIS MOUNTED ARCHIVELOG YES PRIMARY NOT ALLOWED
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +FRA
Oldest online log sequence 26
Next log sequence to archive 27
Current log sequence 27
SQL> alter database open;
Database altered.
SQL> select name,open_mode,log_mode,force_logging,database_role,switchover_status from gv$database;
NAME OPEN_MODE LOG_MODE FORCE_LOGGING DATABASE_ROLE SWITCHOVER_STATUS
--------- -------------------- ------------ ------------------------- ---------------- --------------------
TMIS READ WRITE ARCHIVELOG YES PRIMARY NOT ALLOWED
TMIS MOUNTED ARCHIVELOG YES PRIMARY NOT ALLOWED
--主库open节点2的tmis实例.
[oracle@hisdb2 ~]$ export ORACLE_SID=tmis2
[oracle@hisdb2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jan 23 14:05:13 2024
Version 19.13.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0
SQL> alter database open;
Database altered.
SQL> select name,open_mode,log_mode,force_logging,database_role,switchover_status from gv$database;
NAME OPEN_MODE LOG_MODE FORCE_LOGGING DATABASE_ROLE SWITCHOVER_STATUS
--------- -------------------- ------------ ------------------------- ---------------- --------------------
TMIS READ WRITE ARCHIVELOG YES PRIMARY NOT ALLOWED
TMIS READ WRITE ARCHIVELOG YES PRIMARY NOT ALLOWED
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +FRA
Oldest online log sequence 3
Next log sequence to archive 4
Current log sequence 4
2.1.2、standby redo log
说明：备库调用RFS进程将从主库接收到的重做日志按顺序写入到standby logfile，主库创建standby logfile旨在主备切换后备用.
创建原则：
a、确保standby redo log大小与主库online redo log的大小一致;
b、若主库为单实例：standby redo log组数=主库日志组总数+1;
c、若主库为rac：standby redo log组数=(每线程的日志组数+1)*线程数;
d、不建议复用standby redo log，避免增加额外的I/O以及延缓重做传输.
示例语句，单实例:
alter database add standby logfile group 4 ('/u01/app/oracle/oradata/oralg/standby_redo04.log') size 50m;
alter database add standby logfile group 5 ('/u01/app/oracle/oradata/oralg/standby_redo05.log') size 50m;
alter database add standby logfile group 6 ('/u01/app/oracle/oradata/oralg/standby_redo06.log') size 50m;
alter database add standby logfile group 7 ('/u01/app/oracle/oradata/oralg/standby_redo07.log') size 50m;
集群：
alter database add standby logfile thread 1 group 5 size 50M ,group 6 size 50M ,group 7 size 50M ;
alter database add standby logfile thread 2 group 8 size 50M ,group 9 size 50M ,group 10 size 50M;
主库节点1实际操作：
SQL> select * from v$standby_log;
no rows selected
SQL> select group#,thread#,members,bytes/1024/1024 as "Size_M" from v$log;
GROUP# THREAD# MEMBERS Size_M
---------- ---------- ---------- ----------
1 1 2 200
2 1 2 200
3 2 2 200
4 2 2 200
SQL> col member for a45
SQL> select group#,status,type,member,con_id from v$logfile;
GROUP# STATUS TYPE MEMBER CON_ID
---------- ------- ------- --------------------------------------------- ----------
2 ONLINE +DATA/TMIS/ONLINELOG/group_2.290.1158719585 0
2 ONLINE +FRA/TMIS/ONLINELOG/group_2.301.1158719585 0
1 ONLINE +DATA/TMIS/ONLINELOG/group_1.291.1158719585 0
1 ONLINE +FRA/TMIS/ONLINELOG/group_1.300.1158719585 0
3 ONLINE +DATA/TMIS/ONLINELOG/group_3.279.1158723299 0
3 ONLINE +FRA/TMIS/ONLINELOG/group_3.302.1158723301 0
4 ONLINE +DATA/TMIS/ONLINELOG/group_4.284.1158723301 0
4 ONLINE +FRA/TMIS/ONLINELOG/group_4.303.1158723301 0
8 rows selected.
SQL> alter database add standby logfile thread 1 group 10 size 200m,group 11 size 200m,group 12 size 200m;
Database altered.
SQL> alter database add standby logfile thread 2 group 20 size 200m,group 21 size 200m,group 22 size 200m;
Database altered.
SQL> col dbid for a15
SQL> select group#,dbid,thread#,sequence#,bytes/1024/1024,blocksize,used,archived,status from v$standby_log;
GROUP# DBID THREAD# SEQUENCE# BYTES/1024/1024 BLOCKSIZE USED ARC STATUS
---------- --------------- ---------- ---------- --------------- ---------- ---------- --- ----------
10 UNASSIGNED 1 0 200 512 0 YES UNASSIGNED
11 UNASSIGNED 1 0 200 512 0 YES UNASSIGNED
12 UNASSIGNED 1 0 200 512 0 YES UNASSIGNED
20 UNASSIGNED 2 0 200 512 0 YES UNASSIGNED
21 UNASSIGNED 2 0 200 512 0 YES UNASSIGNED
22 UNASSIGNED 2 0 200 512 0 YES UNASSIGNED
6 rows selected.
SQL> select group#,status,type,member from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- ---------------------------------------------
2 ONLINE +DATA/TMIS/ONLINELOG/group_2.290.1158719585
2 ONLINE +FRA/TMIS/ONLINELOG/group_2.301.1158719585
1 ONLINE +DATA/TMIS/ONLINELOG/group_1.291.1158719585
1 ONLINE +FRA/TMIS/ONLINELOG/group_1.300.1158719585
3 ONLINE +DATA/TMIS/ONLINELOG/group_3.279.1158723299
3 ONLINE +FRA/TMIS/ONLINELOG/group_3.302.1158723301
4 ONLINE +DATA/TMIS/ONLINELOG/group_4.284.1158723301
4 ONLINE +FRA/TMIS/ONLINELOG/group_4.303.1158723301
10 STANDBY +DATA/TMIS/ONLINELOG/group_10.303.1159021149
10 STANDBY +FRA/TMIS/ONLINELOG/group_10.304.1159021149
11 STANDBY +DATA/TMIS/ONLINELOG/group_11.304.1159021149
GROUP# STATUS TYPE MEMBER
---------- ------- ------- ---------------------------------------------
11 STANDBY +FRA/TMIS/ONLINELOG/group_11.305.1159021151
12 STANDBY +DATA/TMIS/ONLINELOG/group_12.305.1159021151
12 STANDBY +FRA/TMIS/ONLINELOG/group_12.306.1159021151
20 STANDBY +DATA/TMIS/ONLINELOG/group_20.306.1159021187
20 STANDBY +FRA/TMIS/ONLINELOG/group_20.307.1159021187
21 STANDBY +DATA/TMIS/ONLINELOG/group_21.307.1159021187
21 STANDBY +FRA/TMIS/ONLINELOG/group_21.308.1159021187
22 STANDBY +DATA/TMIS/ONLINELOG/group_22.308.1159021189
22 STANDBY +FRA/TMIS/ONLINELOG/group_22.309.1159021191
20 rows selected.
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cdb_cluster_name string
cell_offloadgroup_name string
db_file_name_convert string
db_name string tmis
db_unique_name string tmis
global_names boolean FALSE
instance_name string tmis1
lock_name_space string
log_file_name_convert string
pdb_file_name_convert string
processor_group_name string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string tmis
2.1.3、参数文件
使用如下命令修改主库参数(说明：当下主库使用的是spfile启动的实例)
注意：
a、log_archive_config='dg_config=( db_unique_name, db_unique_name)'，主库与备库端采用相同设置
b、log_archive_dest_n='service=……' ，service: 用于指定备库TNSNAMES描述符
c、db_file_name_convert、log_file_name_convert 参数值为转换路径，可直接写db_unique_name.
若使用ASM，可设置为*.db_file_name_convert=('+DATA','+RECOVERY')
d、fal_server、fal_client为TNSNAMES描述符
主库节点1实际操作：
SQL> alter system set log_archive_config='dg_config=(tmis,tmisdg)' sid='*';
System altered.
SQL> alter system set log_archive_dest_1='location=+fra valid_for=(all_logfiles,all_roles) db_unique_name=tmis' sid='*';
System altered.
SQL> alter system set log_archive_dest_2='service=tmisdg lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=tmisdg' sid='*';
System altered.
SQL> alter system set log_archive_dest_state_1=enable sid='*';
System altered.
SQL> alter system set log_archive_dest_state_2=enable sid='*';
System altered.
SQL> alter system set standby_file_management='AUTO' sid='*';
System altered.
SQL> show parameter standby
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enabled_PDBs_on_standby string *
standby_db_preserve_states string NONE
standby_file_management string AUTO
standby_pdb_source_file_dblink string
standby_pdb_source_file_directory string
说明：当主备切换时，需执行如下语句.
alter system set db_file_name_convert='+data/healdg/datafile/','+data/heal/datafile/','+data/healdg/tempfile/','+data/heal/tempfile/' sid='*' scope=spfile;
alter system set log_file_name_convert='+data/healdg/tempfile/','+data/heal/tempfile/','+data/healdg/onlinelog/','+data/heal/onlinelog/','+data/healdg/controlfile/','+data/heal/controlfile/' sid='*' scope=spfile;
alter system set standby_file_management='AUTO' sid='*';
alter system set fal_server='tmisdg' sid='*';
alter system set fal_client='tmis' sid='*';
--在主库用spfile文件创建pfile文件并传递至备库.
SQL> create pfile='/home/oracle/pfiletmis.ora' from spfile;
File created.
--使用scp命令.
[oracle@hisdb1 ~]$ scp pfiletmis.ora oracle@192.168.133.181:/home/oracle
2.1.4、监听文件
2.1.4.1、主库监听
配置主库和备库的监听文件，整个ADG的redo传输服务都依赖于Oracle Net，因此需要为主备库配置监听，且需要配置静态监听.配置方法可用netmgr、netca，或者直接编辑listener.ora文件.
主库节点1监听文件按如下修改：
[grid@hisdb1 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
LISTENER =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.161)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.173)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = tmis)
(ORACLE_HOME = /u01/app/oracle/product/19.13/db_1)
(SID_NAME = tmis1)
)
)
主库节点2监听文件按如下修改：
[grid@hisdb2 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
LISTENER =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.162)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.174)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = tmis)
(ORACLE_HOME = /u01/app/oracle/product/19.13/db_1)
(SID_NAME = tmis2)
)
)
说明：蓝色高亮为修改部分.
--重启主库监听.
[grid@hisdb1 admin]$ srvctl stop listener -l listener
[grid@hisdb1 admin]$ srvctl start listener -l listener
[grid@hisdb1 admin]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 23-JAN-2024 19:16:12
Copyright (c) 1991, 2021, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 23-JAN-2024 19:12:20
Uptime 0 days 0 hr. 3 min. 52 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/19.13/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/hisdb1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.133.161)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.133.173)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_FRA" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_OCR" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "0f52a0ce51589e1be063a185a8c0fc27" has 1 instance(s).
Instance "tmis1", status READY, has 1 handler(s) for this service...
Service "0f5def08812e1479e063a185a8c0f65d" has 1 instance(s).
Instance "tmis1", status READY, has 1 handler(s) for this service...
Service "86b637b62fdf7a65e053f706e80a27ca" has 1 instance(s).
Instance "tmis1", status READY, has 1 handler(s) for this service...
Service "orclpdb" has 1 instance(s).
Instance "tmis1", status READY, has 1 handler(s) for this service...
Service "tmis" has 2 instance(s).
Instance "tmis1", status UNKNOWN, has 1 handler(s) for this service...
Instance "tmis1", status READY, has 1 handler(s) for this service...
Service "tmisXDB" has 1 instance(s).
Instance "tmis1", status READY, has 1 handler(s) for this service...
Service "tmispdb" has 1 instance(s).
Instance "tmis1", status READY, has 1 handler(s) for this service...
The command completed successfully
注意：rac架构是在grid用户下修改监听文件.
2.1.4.2、备库监听
先搭建rac adg环境，可参照如下博客，此处不再赘述.
https://blog.51cto.com/u_12991611/5376800
注意：
a、备库只安装gi及数据库软件，不安装实例.
b、备库两个节点监听文件配置参考主库.
备库节点1监听文件按如下内容修改：
[grid@hisdbdg1 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
LISTENER =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.181)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.183)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = tmisdg)
(ORACLE_HOME = /u01/app/oracle/product/19.13/db_1)
(SID_NAME = tmisdg1)
)
)
注意：
a、此处global_dbname需为tmisdg.
b、蓝色高亮为修改部分.
备库节点2监听文件按如下内容修改：
[grid@hisdbdg2 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
LISTENER =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.182)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.184)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = tmisdg)
(ORACLE_HOME = /u01/app/oracle/product/19.13/db_1)
(SID_NAME = tmisdg2)
)
)
说明：
a、此处global_dbname需为tmisdg.
b、备库节点2参照备库节点1添加，同时注意LISTENER_SCAN1的信息，有时会没有scan listener信息.
c、蓝色高亮为修改部分.
--备库重启监听.
[grid@hisdbdg1 admin]$ srvctl stop listener -l listener
[grid@hisdbdg1 admin]$ srvctl start listener -l listener
[grid@hisdbdg1 admin]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 23-JAN-2024 19:30:04
Copyright (c) 1991, 2021, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 23-JAN-2024 19:27:05
Uptime 0 days 0 hr. 2 min. 58 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/19.13/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/hisdbdg1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.133.181)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.133.183)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_FRA" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_OCR" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "tmisdg" has 1 instance(s).
Instance "tmisdg1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
注意：ASM信息需1分钟后才会正常显示.
2.1.5、tnsnames.ora
配置主库tnsnames.ora文件.注意：tnsnames.ora文件在oracle用户下，主库两节点均加入如下内容：
TMIS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.161)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.162)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = tmis)
)
)
TMISDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.181)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.182)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = tmisdg)
)
)
--将tnsnames.ora传输到备库两节点对应目录.
[oracle@hisdb1 admin]$ scp tnsnames.ora oracle@192.168.133.181:/u01/app/oracle/product/19.13/db_1/network/admin
[oracle@hisdb1 admin]$ scp tnsnames.ora oracle@192.168.133.182:/u01/app/oracle/product/19.13/db_1/network/admin
2.1.6、密码文件
配置主备库密码文件，要求主备库sys密码相同，此处直接复制主库节点1的密码文件到其它3个节点并修改名称.注意：密码文件名为orapw+ORACLE_SID.
[grid@hisdb1 ~]$ srvctl config database -d tmis
Database unique name: tmis
Database name: tmis
Oracle home: /u01/app/oracle/product/19.13/db_1
Oracle user: oracle
Spfile: +data/spfiletmis.ora
Password file: +DATA/TMIS/PASSWORD/pwdtmis.275.1158719419
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA,FRA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: oper
Database instances: tmis1,tmis2
Configured nodes: hisdb1,hisdb2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed
[grid@hisdb1 ~]$ asmcmd pwcopy +DATA/TMIS/PASSWORD/pwdtmis.275.1158719419 /home/grid/orapwtmis1
copying +DATA/TMIS/PASSWORD/pwdtmis.275.1158719419 -> /home/grid/orapwtmis1
[grid@hisdb1 ~]$ scp orapwtmis1 grid@192.168.133.181:/home/grid
--将密码文件放至备库节点1的dbs目录下.
[root@hisdbdg1 ~]# cp /home/grid/orapwtmis1 /u01/app/oracle/product/19.13/db_1/dbs/
[root@hisdbdg1 ~]# chown oracle:oinstall /u01/app/oracle/product/19.13/db_1/dbs/orapwtmis1
[root@hisdbdg1 ~]# su - oracle
[oracle@hisdbdg1 ~]$ cd /u01/app/oracle/product/19.13/db_1/dbs/
[oracle@hisdbdg1 dbs]$ mv orapwtmis1 orapwtmisdg1
处理备库节点2密码文件.
[oracle@hisdbdg1 dbs]$ scp orapwtmisdg1 oracle@192.168.133.182:/u01/app/oracle/product/19.13/db_1/dbs/
[oracle@hisdbdg2 dbs]$ pwd
/u01/app/oracle/product/19.13/db_1/dbs
[oracle@hisdbdg2 dbs]$ mv orapwtmisdg1 orapwtmisdg2
2.1.7、主机名解析
2.1.7.1、主库解析
--在主库/etc/hosts中添加IP与主机名的对应关系，以便解析.
[root@hisdb1 ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
#Public IP
192.168.133.161 hisdb1
192.168.133.162 hisdb2
#Private IP
192.168.78.161 hisdb1-priv
192.168.78.162 hisdb2-priv
#Virtual IP
192.168.133.173 hisdb1-vip
192.168.133.174 hisdb2-vip
#Scan IP
192.168.133.175 hisdb-scan
######### ADG IP ############
#Public IP
192.168.133.181 hisdbdg1
192.168.133.182 hisdbdg2
#Private IP
192.168.78.181 hisdbdg1-priv
192.168.78.182 hisdbdg2-priv
#Virtual IP
192.168.133.183 hisdbdg1-vip
192.168.133.184 hisdbdg2-vip
#Scan IP
192.168.133.185 hisdbdg-scan
说明：主库两个节点均在/etc/hosts文件中添加蓝色高亮内容.
2.1.7.2、备库解析
--在/etc/hosts中添加IP与主机名的对应关系，以便解析.
[root@hisdbdg1 ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
#Public IP
192.168.133.181 hisdbdg1
192.168.133.182 hisdbdg2
#Private IP
192.168.78.181 hisdbdg1-priv
192.168.78.182 hisdbdg2-priv
#Virtual IP
192.168.133.183 hisdbdg1-vip
192.168.133.184 hisdbdg2-vip
#Scan IP
192.168.133.185 hisdbdg-scan
################# MASTER IP ###############
#Public IP
192.168.133.161 hisdb1
192.168.133.162 hisdb2
#Private IP
192.168.78.161 hisdb1-priv
192.168.78.162 hisdb2-priv
#Virtual IP
192.168.133.173 hisdb1-vip
192.168.133.174 hisdb2-vip
#Scan IP
192.168.133.175 hisdb-scan
说明：分别在备库两个节点的/etc/hosts文件中添加蓝色高亮内容.
2.2、备库操作
2.2.1、修改oratab
备库两个节点均在/etc/oratab添加条目.
备库节点1
tmisdg1:/u01/app/oracle/product/19.13/db_1:N
备库节点2
tmisdg2:/u01/app/oracle/product/19.13/db_1:N
2.2.2、修改参数文件
--备库修改主库传递过来的参数文件.
[oracle@hisdbdg1 ~]$ vi pfiletmis.ora
*.audit_file_dest='/u01/app/oracle/admin/tmisdg/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='19.0.0'
*.control_files='+DATA/TMISDG/CONTROLFILE/control01.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_name='tmis'
*.db_unique_name='tmisdg'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=10000m
*.diagnostic_dest='/u01/app/oracle'
*.enable_pluggable_database=true
tmisdg2.instance_number=2
tmisdg1.instance_number=1
*.log_archive_config='dg_config=(tmis,tmisdg)'
*.log_archive_dest_1='location=+fra valid_for=(all_logfiles,all_roles) db_unique_name=tmisdg'
*.log_archive_dest_2='service=tmis lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=tmis'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=1129m
*.processes=300
*.remote_login_passwordfile='exclusive'
*.sga_target=3385m
*.standby_file_management='AUTO'
*.fal_client='TMISDG'
*.fal_server='TMIS'
tmisdg2.thread=2
tmisdg1.thread=1
*.log_file_name_convert='+DATA','+DATA','+FRA','+FRA'
*.db_file_name_convert='+DATA','+DATA'
2.2.3、创建相应目录
[oracle@hisdbdg1 ~]$ mkdir -p /u01/app/oracle/admin/tmisdg/adump
[oracle@hisdbdg2 ~]$ mkdir -p /u01/app/oracle/admin/tmisdg/adump
[oracle@hisdbdg1 ~]$ su - grid
[grid@hisdbdg1 ~]$ asmcmd
ASMCMD> cd data
ASMCMD> ls
ASMCMD> mkdir tmisdg
ASMCMD> cd tmisdg
ASMCMD> mkdir controlfile
ASMCMD> ls
controlfile/
2.2.4、启动到nomount
配置备库pfile文件，并启动到nomount状态，此处选择备库节点1作为实施节点.
[oracle@hisdbdg1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 24 16:01:52 2024
Version 19.13.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/home/oracle/pfiletmis.ora';
ORACLE instance started.
Total System Global Area 3556768424 bytes
Fixed Size 9140904 bytes
Variable Size 754974720 bytes
Database Buffers 2785017856 bytes
Redo Buffers 7634944 bytes
2.2.5、互通测试
主备库4个节点分别测试.
[oracle@hisdb1 ~]$ tnsping tmis
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 24-JAN-2024 16:21:24
Copyright (c) 1997, 2021, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.161)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.162)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = tmis)))
OK (0 msec)
[oracle@hisdb1 ~]$ tnsping tmisdg
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 24-JAN-2024 16:21:28
Copyright (c) 1997, 2021, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.181)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.182)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = tmisdg)))
OK (0 msec)
说明：此处仅用主库节点1示例.
2.2.6、连接验证
主库验证.
[oracle@hisdb1 ~]$ sqlplus sys/oracle_4U@tmisdg as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 24 16:57:03 2024
Version 19.13.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string tmisdg
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0
[oracle@hisdb1 ~]$ sqlplus sys/oracle_4U@tmis as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 24 16:57:25 2024
Version 19.13.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string tmis
备库验证
[oracle@hisdbdg1 trace]$ sqlplus sys/oracle_4U@tmisdg as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 24 16:59:26 2024
Version 19.13.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0
SQL> set line 200
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_unique_name string tmisdg
[oracle@hisdbdg1 trace]$ sqlplus sys/oracle_4U@tmis as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 24 17:03:43 2024
Version 19.13.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string tmis
2.2.7、活动复制
[oracle@hisdbdg1 trace]$ rman target sys/oracle_4U@192.168.133.161:1521/tmis auxiliary sys/oracle_4U@192.168.133.181:1521/tmisdg
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Jan 24 17:19:42 2024
Version 19.13.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: TMIS (DBID=346738717)
connected to auxiliary database: TMIS (not mounted)
RMAN> run{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
allocate channel c5 device type disk;
allocate channel c6 device type disk;
allocate channel c7 device type disk;
allocate channel c8 device type disk;
allocate auxiliary channel a1 device type disk;
allocate auxiliary channel a2 device type disk;
allocate auxiliary channel a3 device type disk;
allocate auxiliary channel a4 device type disk;
allocate auxiliary channel a5 device type disk;
allocate auxiliary channel a6 device type disk;
allocate auxiliary channel a7 device type disk;
allocate auxiliary channel a8 device type disk;
duplicate target database for standby from active database;
}
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=289 instance=tmis1 device type=DISK
allocated channel: c2
channel c2: SID=53 instance=tmis1 device type=DISK
allocated channel: c3
channel c3: SID=32 instance=tmis1 device type=DISK
allocated channel: c4
channel c4: SID=269 instance=tmis1 device type=DISK
allocated channel: c5
channel c5: SID=68 instance=tmis1 device type=DISK
allocated channel: c6
channel c6: SID=292 instance=tmis1 device type=DISK
allocated channel: c7
channel c7: SID=58 instance=tmis1 device type=DISK
allocated channel: c8
channel c8: SID=310 instance=tmis1 device type=DISK
allocated channel: a1
channel a1: SID=267 instance=tmisdg1 device type=DISK
allocated channel: a2
channel a2: SID=32 instance=tmisdg1 device type=DISK
allocated channel: a3
channel a3: SID=268 instance=tmisdg1 device type=DISK
allocated channel: a4
channel a4: SID=33 instance=tmisdg1 device type=DISK
allocated channel: a5
channel a5: SID=269 instance=tmisdg1 device type=DISK
allocated channel: a6
channel a6: SID=34 instance=tmisdg1 device type=DISK
allocated channel: a7
channel a7: SID=270 instance=tmisdg1 device type=DISK
allocated channel: a8
channel a8: SID=35 instance=tmisdg1 device type=DISK
Starting Duplicate Db at 24-JAN-24
contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format '/u01/app/oracle/product/19.13/db_1/dbs/orapwtmisdg1' ;
}
executing Memory Script
Starting backup at 24-JAN-24
Finished backup at 24-JAN-24
contents of Memory Script:
{
restore clone from service '192.168.133.161:1521/tmis' standby controlfile;
}
executing Memory Script
Starting restore at 24-JAN-24
channel a1: starting datafile backup set restore
channel a1: using network backup set from service 192.168.133.161:1521/tmis
channel a1: restoring control file
channel a1: restore complete, elapsed time: 00:00:04
output file name=+DATA/TMISDG/CONTROLFILE/control01.ctl
Finished restore at 24-JAN-24
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-05529: warning: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
Using previous duplicated file +DATA/TMISDG/DATAFILE/undotbs1.259.1159118809 for datafile 4 with checkpoint SCN of 4468923
Using previous duplicated file +DATA/TMISDG/DATAFILE/users.262.1159118819 for datafile 7 with checkpoint SCN of 4468986
Using previous duplicated file +DATA/TMISDG/DATAFILE/undotbs2.264.1159118857 for datafile 9 with checkpoint SCN of 4469406
Using previous duplicated file +DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/undotbs1.267.1159118983 for datafile 12 with checkpoint SCN of 4469737
Using previous duplicated file +DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/undo_2.268.1159119165 for datafile 13 with checkpoint SCN of 4470474
Using previous duplicated file +DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/users.269.1159119341 for datafile 14 with checkpoint SCN of 4471102
contents of Memory Script:
{
set newname for tempfile 1 to
"+DATA";
set newname for tempfile 2 to
"+DATA";
set newname for tempfile 3 to
"+DATA";
set newname for tempfile 4 to
"+DATA";
switch clone tempfile all;
set newname for datafile 1 to
"+DATA";
set newname for datafile 3 to
"+DATA";
set newname for datafile 4 to
"+DATA/TMISDG/DATAFILE/undotbs1.259.1159118809";
set newname for datafile 5 to
"+DATA";
set newname for datafile 6 to
"+DATA";
set newname for datafile 7 to
"+DATA/TMISDG/DATAFILE/users.262.1159118819";
set newname for datafile 8 to
"+DATA";
set newname for datafile 9 to
"+DATA/TMISDG/DATAFILE/undotbs2.264.1159118857";
set newname for datafile 10 to
"+DATA";
set newname for datafile 11 to
"+DATA";
set newname for datafile 12 to
"+DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/undotbs1.267.1159118983";
set newname for datafile 13 to
"+DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/undo_2.268.1159119165";
set newname for datafile 14 to
"+DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/users.269.1159119341";
set newname for datafile 15 to
"+DATA";
set newname for datafile 16 to
"+DATA";
set newname for datafile 17 to
"+DATA";
set newname for datafile 18 to
"+DATA";
restore
from nonsparse from service
'192.168.133.161:1521/tmis' clone datafile
1, 3, 5, 6, 8, 10, 11, 15, 16, 17, 18 ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to +DATA in control file
renamed tempfile 2 to +DATA in control file
renamed tempfile 3 to +DATA in control file
renamed tempfile 4 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
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
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 24-JAN-24
channel a1: starting datafile backup set restore
channel a1: using network backup set from service 192.168.133.161:1521/tmis
channel a1: specifying datafile(s) to restore from backup set
channel a1: restoring datafile 00001 to +DATA
channel a2: starting datafile backup set restore
channel a2: using network backup set from service 192.168.133.161:1521/tmis
channel a2: specifying datafile(s) to restore from backup set
channel a2: restoring datafile 00003 to +DATA
channel a3: starting datafile backup set restore
channel a3: using network backup set from service 192.168.133.161:1521/tmis
channel a3: specifying datafile(s) to restore from backup set
channel a3: restoring datafile 00005 to +DATA
channel a4: starting datafile backup set restore
channel a4: using network backup set from service 192.168.133.161:1521/tmis
channel a4: specifying datafile(s) to restore from backup set
channel a4: restoring datafile 00006 to +DATA
channel a5: starting datafile backup set restore
channel a5: using network backup set from service 192.168.133.161:1521/tmis
channel a5: specifying datafile(s) to restore from backup set
channel a5: restoring datafile 00008 to +DATA
channel a6: starting datafile backup set restore
channel a6: using network backup set from service 192.168.133.161:1521/tmis
channel a6: specifying datafile(s) to restore from backup set
channel a6: restoring datafile 00010 to +DATA
channel a7: starting datafile backup set restore
channel a7: using network backup set from service 192.168.133.161:1521/tmis
channel a7: specifying datafile(s) to restore from backup set
channel a7: restoring datafile 00011 to +DATA
channel a8: starting datafile backup set restore
channel a8: using network backup set from service 192.168.133.161:1521/tmis
channel a8: specifying datafile(s) to restore from backup set
channel a8: restoring datafile 00015 to +DATA
channel a3: restore complete, elapsed time: 00:01:02
channel a3: starting datafile backup set restore
channel a3: using network backup set from service 192.168.133.161:1521/tmis
channel a3: specifying datafile(s) to restore from backup set
channel a3: restoring datafile 00016 to +DATA
channel a5: restore complete, elapsed time: 00:01:36
channel a5: starting datafile backup set restore
channel a5: using network backup set from service 192.168.133.161:1521/tmis
channel a5: specifying datafile(s) to restore from backup set
channel a5: restoring datafile 00017 to +DATA
channel a8: restore complete, elapsed time: 00:01:37
channel a8: starting datafile backup set restore
channel a8: using network backup set from service 192.168.133.161:1521/tmis
channel a8: specifying datafile(s) to restore from backup set
channel a8: restoring datafile 00018 to +DATA
channel a1: restore complete, elapsed time: 00:01:41
channel a2: restore complete, elapsed time: 00:01:41
channel a4: restore complete, elapsed time: 00:01:40
channel a6: restore complete, elapsed time: 00:01:39
channel a7: restore complete, elapsed time: 00:01:39
channel a5: restore complete, elapsed time: 00:00:10
channel a8: restore complete, elapsed time: 00:00:09
channel a3: restore complete, elapsed time: 00:00:34
Finished restore at 24-JAN-24
sql statement: alter system archive log current
contents of Memory Script:
{
restore clone force from service '192.168.133.161:1521/tmis'
archivelog from scn 4468923;
catalog clone datafilecopy "+DATA/TMISDG/DATAFILE/undotbs1.259.1159118809",
"+DATA/TMISDG/DATAFILE/users.262.1159118819",
"+DATA/TMISDG/DATAFILE/undotbs2.264.1159118857",
"+DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/undotbs1.267.1159118983",
"+DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/undo_2.268.1159119165",
"+DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/users.269.1159119341";
switch clone datafile 4 to datafilecopy
"+DATA/TMISDG/DATAFILE/undotbs1.259.1159118809";
switch clone datafile 7 to datafilecopy
"+DATA/TMISDG/DATAFILE/users.262.1159118819";
switch clone datafile 9 to datafilecopy
"+DATA/TMISDG/DATAFILE/undotbs2.264.1159118857";
switch clone datafile 12 to datafilecopy
"+DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/undotbs1.267.1159118983";
switch clone datafile 13 to datafilecopy
"+DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/undo_2.268.1159119165";
switch clone datafile 14 to datafilecopy
"+DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/users.269.1159119341";
switch clone datafile all;
}
executing Memory Script
Starting restore at 24-JAN-24
channel a1: starting archived log restore to default destination
channel a1: using network backup set from service 192.168.133.161:1521/tmis
channel a1: restoring archived log
archived log thread=1 sequence=33
channel a2: starting archived log restore to default destination
channel a2: using network backup set from service 192.168.133.161:1521/tmis
channel a2: restoring archived log
archived log thread=2 sequence=17
channel a1: restore complete, elapsed time: 00:00:16
channel a2: restore complete, elapsed time: 00:00:15
Finished restore at 24-JAN-24
cataloged datafile copy
datafile copy file name=+DATA/TMISDG/DATAFILE/undotbs1.259.1159118809 RECID=15 STAMP=1159125387
cataloged datafile copy
datafile copy file name=+DATA/TMISDG/DATAFILE/undotbs2.264.1159118857 RECID=16 STAMP=1159125387
cataloged datafile copy
datafile copy file name=+DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/undotbs1.267.1159118983 RECID=17 STAMP=1159125387
cataloged datafile copy
datafile copy file name=+DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/undo_2.268.1159119165 RECID=18 STAMP=1159125387
cataloged datafile copy
datafile copy file name=+DATA/TMISDG/DATAFILE/users.262.1159118819 RECID=19 STAMP=1159125387
cataloged datafile copy
datafile copy file name=+DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/users.269.1159119341 RECID=20 STAMP=1159125388
datafile 4 switched to datafile copy
input datafile copy RECID=15 STAMP=1159125387 file name=+DATA/TMISDG/DATAFILE/undotbs1.259.1159118809
datafile 7 switched to datafile copy
input datafile copy RECID=19 STAMP=1159125387 file name=+DATA/TMISDG/DATAFILE/users.262.1159118819
datafile 9 switched to datafile copy
input datafile copy RECID=16 STAMP=1159125387 file name=+DATA/TMISDG/DATAFILE/undotbs2.264.1159118857
datafile 12 switched to datafile copy
input datafile copy RECID=17 STAMP=1159125387 file name=+DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/undotbs1.267.1159118983
datafile 13 switched to datafile copy
input datafile copy RECID=18 STAMP=1159125387 file name=+DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/undo_2.268.1159119165
datafile 14 switched to datafile copy
input datafile copy RECID=20 STAMP=1159125388 file name=+DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/users.269.1159119341
datafile 1 switched to datafile copy
input datafile copy RECID=21 STAMP=1159125390 file name=+DATA/TMISDG/DATAFILE/system.270.1159125247
datafile 3 switched to datafile copy
input datafile copy RECID=22 STAMP=1159125390 file name=+DATA/TMISDG/DATAFILE/sysaux.258.1159125249
datafile 5 switched to datafile copy
input datafile copy RECID=23 STAMP=1159125390 file name=+DATA/TMISDG/0F5213803EFF3E99E063A185A8C03FF4/DATAFILE/system.265.1159125249
datafile 6 switched to datafile copy
input datafile copy RECID=24 STAMP=1159125390 file name=+DATA/TMISDG/0F5213803EFF3E99E063A185A8C03FF4/DATAFILE/sysaux.271.1159125249
datafile 8 switched to datafile copy
input datafile copy RECID=25 STAMP=1159125391 file name=+DATA/TMISDG/0F5213803EFF3E99E063A185A8C03FF4/DATAFILE/undotbs1.266.1159125249
datafile 10 switched to datafile copy
input datafile copy RECID=26 STAMP=1159125391 file name=+DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/system.261.1159125249
datafile 11 switched to datafile copy
input datafile copy RECID=27 STAMP=1159125391 file name=+DATA/TMISDG/0F52A0CE51589E1BE063A185A8C0FC27/DATAFILE/sysaux.257.1159125251
datafile 15 switched to datafile copy
input datafile copy RECID=28 STAMP=1159125391 file name=+DATA/TMISDG/0F5DEF08812E1479E063A185A8C0F65D/DATAFILE/system.272.1159125253
datafile 16 switched to datafile copy
input datafile copy RECID=29 STAMP=1159125391 file name=+DATA/TMISDG/0F5DEF08812E1479E063A185A8C0F65D/DATAFILE/sysaux.274.1159125349
datafile 17 switched to datafile copy
input datafile copy RECID=30 STAMP=1159125391 file name=+DATA/TMISDG/0F5DEF08812E1479E063A185A8C0F65D/DATAFILE/undotbs1.273.1159125347
datafile 18 switched to datafile copy
input datafile copy RECID=31 STAMP=1159125391 file name=+DATA/TMISDG/0F5DEF08812E1479E063A185A8C0F65D/DATAFILE/undo_2.275.1159125349
Finished Duplicate Db at 24-JAN-24
released channel: c1
released channel: c2
released channel: c3
released channel: c4
released channel: c5
released channel: c6
released channel: c7
released channel: c8
released channel: a1
released channel: a2
released channel: a3
released channel: a4
released channel: a5
released channel: a6
released channel: a7
released channel: a8
3、单机转rac
由于备库是rac，需修改spfile到磁盘组，然后才能启动rac dg的第2个节点.
3.1、生成pfile
[oracle@hisdbdg1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 24 23:03:39 2024
Version 19.13.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> show parameter cluster
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cdb_cluster boolean FALSE
cdb_cluster_name string
cluster_database boolean TRUE
cluster_database_instances integer 2
cluster_interconnects string
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
3.2、修改pfile
--按如下内容修改pfile文件.
[oracle@hisdbdg1 ~]$ cat pfiletmis.ora
*.audit_file_dest='/u01/app/oracle/admin/tmisdg/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='19.0.0'
*.control_files='+DATA/TMISDG/CONTROLFILE/control01.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_name='tmis'
*.db_unique_name='tmisdg'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=10000m
*.diagnostic_dest='/u01/app/oracle'
*.enable_pluggable_database=true
tmisdg2.instance_number=2
tmisdg1.instance_number=1
*.log_archive_config='dg_config=(tmis,tmisdg)'
*.log_archive_dest_1='location=+fra valid_for=(all_logfiles,all_roles) db_unique_name=tmisdg'
*.log_archive_dest_2='service=tmis lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=tmis'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=1129m
*.processes=300
*.remote_login_passwordfile='exclusive'
*.sga_target=3385m
*.standby_file_management='AUTO'
*.fal_client='TMISDG'
*.fal_server='TMIS'
tmisdg2.thread=2
tmisdg1.thread=1
*.log_file_name_convert='+DATA','+DATA','+FRA','+FRA'
*.db_file_name_convert='+DATA','+DATA'
tmisdg1.undo_tablespace='UNDOTBS1'
tmisdg2.undo_tablespace='UNDOTBS2'
注意：蓝色标注内容.
3.3、创建spfile
备库节点1：
--创建spfile文件到磁盘组，并在pfile文件中添加spfile路径.
SQL> create spfile='+data/tmisdg/parameterfile/spfiletmisdg.ora' from pfile='/home/oracle/pfiletmis.ora';
File created.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0
[oracle@hisdbdg1 ~]$ echo "SPFILE='+data/tmisdg/parameterfile/spfiletmisdg.ora'" > $ORACLE_HOME/dbs/inittmisdg1.ora
[oracle@hisdbdg1 ~]$ cat $ORACLE_HOME/dbs/inittmisdg1.ora
SPFILE='+data/tmisdg/parameterfile/spfiletmisdg.ora'
在备库节点2的pfile文件中添加spfile路径.
[oracle@hisdbdg1 ~]$ echo "SPFILE='+data/tmisdg/parameterfile/spfiletmisdg.ora'" > $ORACLE_HOME/dbs/inittmisdg2.ora
[oracle@hisdbdg1 ~]$ cat $ORACLE_HOME/dbs/inittmisdg2.ora
SPFILE='+data/tmisdg/parameterfile/spfiletmisdg.ora'
注意：删除原spfile.
3.4、备库重启
启动备库两个节点后验证.
SQL> startup force
ORACLE instance started.
Total System Global Area 3556768424 bytes
Fixed Size 9140904 bytes
Variable Size 754974720 bytes
Database Buffers 2785017856 bytes
Redo Buffers 7634944 bytes
Database mounted.
Database opened.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TMISPDB MOUNTED
4 ORCLPDB MOUNTED
SQL> alter pluggable database TMISPDB open;
Pluggable database altered.
SQL> alter pluggable database ORCLPDB open;
Pluggable database altered.
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/tmisdg/parameterfile/spf
iletmisdg.ora
SQL> set line 9999
SQL> select name,open_mode,log_mode,force_logging,database_role,switchover_status from gv$database;
NAME OPEN_MODE LOG_MODE FORCE_LOGGING DATABASE_ROLE SWITCHOVER_STATUS
--------- -------------------- ------------ --------------------------------------- ---------------- --------------------
TMIS READ ONLY ARCHIVELOG YES PHYSICAL STANDBY RECOVERY NEEDED
--启动节点2后，查询结果如下.
SQL> set line 200
SQL> col force_logging for a15
SQL> select name,open_mode,log_mode,force_logging,database_role,switchover_status from gv$database;
NAME OPEN_MODE LOG_MODE FORCE_LOGGING DATABASE_ROLE SWITCHOVER_STATUS
--------- -------------------- ------------ --------------- ---------------- --------------------
TMIS READ ONLY ARCHIVELOG YES PHYSICAL STANDBY RECOVERY NEEDED
TMIS READ ONLY ARCHIVELOG YES PHYSICAL STANDBY RECOVERY NEEDED
4、加入crsctl
将备库加入crsctl中，说明：dbca创建的数据库会自动加入crsctl，但通过rman创建的库需手动添加，加入crsctl中后可通过srvctl管理.
4.1、集群状态
--加入crsctl前集群状态.
[grid@hisdbdg1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
ONLINE ONLINE hisdbdg1 STABLE
ONLINE ONLINE hisdbdg2 STABLE
ora.chad
ONLINE ONLINE hisdbdg1 STABLE
ONLINE ONLINE hisdbdg2 STABLE
ora.net1.network
ONLINE ONLINE hisdbdg1 STABLE
ONLINE ONLINE hisdbdg2 STABLE
ora.ons
ONLINE ONLINE hisdbdg1 STABLE
ONLINE ONLINE hisdbdg2 STABLE
ora.proxy_advm
OFFLINE OFFLINE hisdbdg1 STABLE
OFFLINE OFFLINE hisdbdg2 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
1 ONLINE ONLINE hisdbdg1 STABLE
2 ONLINE ONLINE hisdbdg2 STABLE
3 ONLINE OFFLINE STABLE
ora.DATA.dg(ora.asmgroup)
1 ONLINE ONLINE hisdbdg1 STABLE
2 ONLINE ONLINE hisdbdg2 STABLE
3 OFFLINE OFFLINE STABLE
ora.FRA.dg(ora.asmgroup)
1 ONLINE ONLINE hisdbdg1 STABLE
2 ONLINE ONLINE hisdbdg2 STABLE
3 OFFLINE OFFLINE STABLE
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE hisdbdg2 STABLE
ora.OCR.dg(ora.asmgroup)
1 ONLINE ONLINE hisdbdg1 STABLE
2 ONLINE ONLINE hisdbdg2 STABLE
3 OFFLINE OFFLINE STABLE
ora.asm(ora.asmgroup)
1 ONLINE ONLINE hisdbdg1 Started,STABLE
2 ONLINE ONLINE hisdbdg2 Started,STABLE
3 OFFLINE OFFLINE STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
1 ONLINE ONLINE hisdbdg1 STABLE
2 ONLINE ONLINE hisdbdg2 STABLE
3 OFFLINE OFFLINE STABLE
ora.cvu
1 ONLINE ONLINE hisdbdg2 STABLE
ora.hisdbdg1.vip
1 ONLINE ONLINE hisdbdg1 STABLE
ora.hisdbdg2.vip
1 ONLINE ONLINE hisdbdg2 STABLE
ora.qosmserver
1 ONLINE ONLINE hisdbdg2 STABLE
ora.scan1.vip
1 ONLINE ONLINE hisdbdg2 STABLE
--------------------------------------------------------------------------------
4.2、指令说明
[grid@hisdb3 ~]$ srvctl add database -h
Adds a database configuration to the Oracle Clusterware.
Usage: srvctl add database -d <db_unique_name> -o <oracle_home> [-c {RACONENODE | RAC | SINGLE} [-e <server_list>] [-i <inst_name>] [-w <timeout>]] [-m <domain_name>] [-p <spfile>] [-r {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY | SNAPSHOT_STANDBY}] [-s <start_options>] [-t <stop_options>] [-n <db_name>] [-y {AUTOMATIC | MANUAL | NORESTART}] [-g "<serverpool_list>"] [-x <node_name>] [-a "<diskgroup_list>"] [-j "<acfs_path_list>"]
-d <db_unique_name> Unique name for the database
-o <oracle_home> ORACLE_HOME path
-c <type> Type of database: RAC One Node, RAC, or Single Instance
-e <server_list> Candidate server list for RAC One Node database
-i <inst_name> Instance name prefix for administrator-managed RAC One Node database (default first 12 characters of <db_unique_name>)
-w <timeout> Online relocation timeout in minutes
-x <node_name> Node name. -x option is specified for single-instance databases
-m <domain> Domain for database. Must be set if database has DB_DOMAIN set.
-p <spfile> Server parameter file path
-r <role> Role of the database (primary, physical_standby, logical_standby, snapshot_standby)
-s <start_options> Startup options for the database. Examples of startup options are OPEN, MOUNT, or 'READ ONLY'.
-t <stop_options> Stop options for the database. Examples of shutdown options are NORMAL, TRANSACTIONAL, IMMEDIATE, or ABORT.
-n <db_name> Database name (DB_NAME), if different from the unique name given by the -d option
-y <dbpolicy> Management policy for the database (AUTOMATIC, MANUAL, or NORESTART)
-g "<serverpool_list>" Comma separated list of database server pool names
-a "<diskgroup_list>" Comma separated list of disk groups
-j "<acfs_path_list>" Comma separated list of ACFS paths where database's dependency will be set
-h Print usage
4.3、操作过程
4.3.1、主库操作
主库将数据库置于mount状态.
[grid@hisdb1 ~]$ srvctl stop database -d tmis -o immediate
[grid@hisdb1 ~]$ srvctl start database -d tmis -o mount
[grid@hisdb1 ~]$ srvctl status database -d tmis
Instance tmis1 is running on node hisdb1
Instance tmis2 is running on node hisdb2
4.3.2、备库操作
备库节点1操作.
[oracle@hisdbdg1 ~]$ srvctl add database -d tmisdg -c RAC -o /u01/app/oracle/product/19.13/db_1 -p '+data/tmisdg/parameterfile/spfiletmisdg.ora' -r physical_standby -n tmis
注意：以上为一行命令，且需用oracle用户执行，否则报错PRCD-1025、PRKH-1014.
[oracle@hisdbdg1 ~]$ srvctl add instance -d tmisdg -i tmisdg1 -n hisdbdg1
[oracle@hisdbdg1 ~]$ srvctl add instance -d tmisdg -i tmisdg2 -n hisdbdg2
[oracle@hisdbdg1 ~]$ srvctl status database -d tmisdg
Instance tmisdg1 is not running on node hisdbdg1
Instance tmisdg2 is not running on node hisdbdg2
[oracle@hisdbdg1 ~]$ srvctl start database -d tmisdg
[oracle@hisdbdg1 ~]$ srvctl status database -d tmisdg
Instance tmisdg1 is running on node hisdbdg1
Instance tmisdg2 is running on node hisdbdg2
[oracle@hisdbdg1 ~]$ srvctl config database -d tmisdg -a
Database unique name: tmisdg
Database name: tmis
Oracle home: /u01/app/oracle/product/19.13/db_1
Oracle user: oracle
Spfile: +data/tmisdg/parameterfile/spfiletmisdg.ora
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools:
Disk Groups:
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
Database is enabled
Database is individually enabled on nodes:
Database is individually disabled on nodes:
OSDBA group: dba
OSOPER group: oper
Database instances: tmisdg1,tmisdg2
Configured nodes: hisdbdg1,hisdbdg2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed
4.3.3、备库查询
[grid@hisdbdg1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
ONLINE ONLINE hisdbdg1 STABLE
ONLINE ONLINE hisdbdg2 STABLE
ora.chad
ONLINE ONLINE hisdbdg1 STABLE
ONLINE ONLINE hisdbdg2 STABLE
ora.net1.network
ONLINE ONLINE hisdbdg1 STABLE
ONLINE ONLINE hisdbdg2 STABLE
ora.ons
ONLINE ONLINE hisdbdg1 STABLE
ONLINE ONLINE hisdbdg2 STABLE
ora.proxy_advm
OFFLINE OFFLINE hisdbdg1 STABLE
OFFLINE OFFLINE hisdbdg2 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
1 ONLINE ONLINE hisdbdg1 STABLE
2 ONLINE ONLINE hisdbdg2 STABLE
3 ONLINE OFFLINE STABLE
ora.DATA.dg(ora.asmgroup)
1 ONLINE ONLINE hisdbdg1 STABLE
2 ONLINE ONLINE hisdbdg2 STABLE
3 OFFLINE OFFLINE STABLE
ora.FRA.dg(ora.asmgroup)
1 ONLINE ONLINE hisdbdg1 STABLE
2 ONLINE ONLINE hisdbdg2 STABLE
3 OFFLINE OFFLINE STABLE
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE hisdbdg2 STABLE
ora.OCR.dg(ora.asmgroup)
1 ONLINE ONLINE hisdbdg1 STABLE
2 ONLINE ONLINE hisdbdg2 STABLE
3 OFFLINE OFFLINE STABLE
ora.asm(ora.asmgroup)
1 ONLINE ONLINE hisdbdg1 Started,STABLE
2 ONLINE ONLINE hisdbdg2 Started,STABLE
3 OFFLINE OFFLINE STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
1 ONLINE ONLINE hisdbdg1 STABLE
2 ONLINE ONLINE hisdbdg2 STABLE
3 OFFLINE OFFLINE STABLE
ora.cvu
1 ONLINE ONLINE hisdbdg2 STABLE
ora.hisdbdg1.vip
1 ONLINE ONLINE hisdbdg1 STABLE
ora.hisdbdg2.vip
1 ONLINE ONLINE hisdbdg2 STABLE
ora.qosmserver
1 ONLINE ONLINE hisdbdg2 STABLE
ora.scan1.vip
1 ONLINE ONLINE hisdbdg2 STABLE
ora.tmisdg.db
1 ONLINE ONLINE hisdbdg1 Open,Readonly,HOME=/
u01/app/oracle/produ
ct/19.13/db_1,STABLE
2 ONLINE ONLINE hisdbdg2 Open,Readonly,HOME=/
u01/app/oracle/produ
ct/19.13/db_1,STABLE
--------------------------------------------------------------------------------
SQL> set line 200
SQL> select group#,thread#,sequence#,bytes/1024/1024,blocksize,members,status from v$log
GROUP# THREAD# SEQUENCE# BYTES/1024/1024 BLOCKSIZE MEMBERS STATUS
---------- ---------- ---------- --------------- ---------- ---------- ----------------
1 1 0 200 512 2 CURRENT
2 1 0 200 512 2 UNUSED
3 2 0 200 512 2 CURRENT
4 2 0 200 512 2 UNUSED
SQL> col force_logging for a15
SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FORCE_LOGGING OPEN_MODE SWITCHOVER_STATUS
---------- --------- ----------- -------------------- ---------------- --------------- -------------------- --------------------
346738717 TMIS 4499450 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY RECOVERY NEEDED
346738717 TMIS 4499450 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY RECOVERY NEEDED
SQL> col dbid for a15
SQL> select group#,dbid,thread#,sequence#,bytes,blocksize,used,archived,status from v$standby_log
2 ;
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS
---------- --------------- ---------- ---------- ---------- ---------- ---------- --- ----------
10 UNASSIGNED 1 0 209715200 512 0 NO UNASSIGNED
11 346738717 1 37 209715200 512 47114752 YES ACTIVE
12 UNASSIGNED 1 0 209715200 512 0 YES UNASSIGNED
20 UNASSIGNED 2 0 209715200 512 0 NO UNASSIGNED
21 346738717 2 19 209715200 512 88186880 YES ACTIVE
22 UNASSIGNED 2 0 209715200 512 0 YES UNASSIGNED
6 rows selected.
说明：如上所示，数据库tmisdg已成功加入集群管理.
4.3.4、实时同步
--备库开启实时同步
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> col db_unique_name for a20
SQL> col DB_UNIQUE_NAME for a15
SQL> select inst_id,dbid,name,db_unique_name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
INST_ID DBID NAME DB_UNIQUE_NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FORCE_LOGGING OPEN_MODE SWITCHOVER_STATUS
---------- ---------- --------- --------------- ----------- -------------------- ---------------- --------------- -------------------- --------------------
1 346738717 TMIS tmisdg 4660918 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED
2 346738717 TMIS tmisdg 4660918 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED
SQL> col name for a70
SQL> r
1 SELECT dest_id, THREAD#, NAME, sequence#, archived, applied, a.NEXT_CHANGE#
2 FROM v$archived_log a
3 WHERE a.sequence# >= 12
4 AND resetlogs_change# = (SELECT d.RESETLOGS_CHANGE# FROM v$database d)
5* ORDER BY a.THREAD#, a.sequence#, a.dest_id;
DEST_ID THREAD# NAME SEQUENCE# ARC APPLIED NEXT_CHANGE#
---------- ---------- ---------------------------------------------------------------------- ---------- --- --------- ------------
1 1 +FRA/TMISDG/ARCHIVELOG/2024_01_24/thread_1_seq_33.257.1159125383 33 YES NO 4499502
1 1 +FRA/TMISDG/ARCHIVELOG/2024_01_24/thread_1_seq_34.269.1159138997 34 YES YES 4601346
2 1 +FRA/TMISDG/ARCHIVELOG/2024_01_24/thread_1_seq_35.268.1159138987 35 YES YES 4601353
2 1 +FRA/TMISDG/ARCHIVELOG/2024_01_24/thread_1_seq_36.270.1159138997 36 YES YES 4607887
1 2 +FRA/TMISDG/ARCHIVELOG/2024_01_24/thread_2_seq_17.256.1159125383 17 YES YES 4499507
1 2 +FRA/TMISDG/ARCHIVELOG/2024_01_24/thread_2_seq_18.271.1159138997 18 YES YES 4601347
6 rows selected.
5、同步验证
5.1、主库启库
将主库两节点从mount开启到open状态，主库节点1切换日志验证.
[oracle@hisdb1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 25 00:03:17 2024
Version 19.13.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0
SQL> alter database open;
Database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TMISPDB READ WRITE NO
4 ORCLPDB READ WRITE NO
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
备库查询：
SQL> select thread#,sequence#,first_time,next_time,completion_time,applied from v$archived_log order by 1,2;
THREAD# SEQUENCE# FIRST_TIME NEXT_TIME COMPLETION_TIME APPLIED
---------- ---------- ------------------- ------------------- ------------------- ---------
1 33 2024-01-24 08:29:15 2024-01-24 19:16:06 2024-01-24 19:16:26 YES
1 34 2024-01-24 19:16:06 2024-01-24 22:09:27 2024-01-24 23:03:16 YES
1 35 2024-01-24 22:09:27 2024-01-24 22:09:30 2024-01-24 23:03:16 YES
1 36 2024-01-24 22:13:02 2024-01-24 22:13:02 2024-01-24 23:03:16 YES
1 37 2024-01-24 22:13:02 2024-01-25 00:03:32 2024-01-25 00:03:37 YES
1 38 2024-01-25 00:03:32 2024-01-25 00:07:21 2024-01-25 00:07:22 YES
1 39 2024-01-25 00:07:21 2024-01-25 00:07:31 2024-01-25 00:07:32 IN-MEMORY
2 17 2024-01-24 08:31:59 2024-01-24 19:16:06 2024-01-24 19:16:25 YES
2 18 2024-01-24 19:16:06 2024-01-24 22:09:28 2024-01-24 23:03:16 YES
2 19 2024-01-24 22:09:28 2024-01-25 00:04:11 2024-01-25 00:04:25 YES
10 rows selected.
5.2、建表测试
--主库建表.
SQL> alter session set container=orclpdb;
Session altered.
SQL> create table test (id number);
Table created.
SQL> insert into test values (1);
1 row created.
SQL> commit;
Commit complete.
--备库端查询日志应用情况.
SQL> alter session set container=tmispdb;
SQL> col username for a15
SQL> select username from all_users where username='LEO';
USERNAME
---------------
LEO
SQL> alter session set container=orclpdb;
Session altered.
SQL> select * from test;
ID
----------
1
SQL> SELECT dest_id, THREAD#, NAME, sequence#, archived, applied, a.NEXT_CHANGE#
2 FROM v$archived_log a
3 WHERE a.sequence# >= 12
4 AND resetlogs_change# = (SELECT d.RESETLOGS_CHANGE# FROM v$database d)
5 ORDER BY a.THREAD#, a.sequence#, a.dest_id;
DEST_ID THREAD# NAME SEQUENCE# ARC APPLIED NEXT_CHANGE#
---------- ---------- ---------------------------------------------------------------------- ---------- --- --------- ------------
1 1 +FRA/TMISDG/ARCHIVELOG/2024_01_24/thread_1_seq_33.257.1159125383 33 YES YES 4499502
1 1 +FRA/TMISDG/ARCHIVELOG/2024_01_24/thread_1_seq_34.269.1159138997 34 YES YES 4601346
2 1 +FRA/TMISDG/ARCHIVELOG/2024_01_24/thread_1_seq_35.268.1159138987 35 YES YES 4601353
2 1 +FRA/TMISDG/ARCHIVELOG/2024_01_24/thread_1_seq_36.270.1159138997 36 YES YES 4607887
1 1 +FRA/TMISDG/ARCHIVELOG/2024_01_25/thread_1_seq_37.272.1159142617 37 YES YES 4660950
1 1 +FRA/TMISDG/ARCHIVELOG/2024_01_25/thread_1_seq_38.274.1159142841 38 YES YES 4674183
1 1 +FRA/TMISDG/ARCHIVELOG/2024_01_25/thread_1_seq_39.275.1159142853 39 YES YES 4674213
1 2 +FRA/TMISDG/ARCHIVELOG/2024_01_24/thread_2_seq_17.256.1159125383 17 YES YES 4499507
1 2 +FRA/TMISDG/ARCHIVELOG/2024_01_24/thread_2_seq_18.271.1159138997 18 YES YES 4601347
1 2 +FRA/TMISDG/ARCHIVELOG/2024_01_25/thread_2_seq_19.273.1159142663 19 YES YES 4662833
10 rows selected.
SQL> select process,status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CLOSING
DGRD ALLOCATED
DGRD ALLOCATED
ARCH CLOSING
ARCH CLOSING
ARCH CONNECTED
MRP0 APPLYING_LOG
RFS IDLE
RFS IDLE
RFS IDLE
RFS IDLE
PROCESS STATUS
--------- ------------
RFS IDLE
RFS IDLE
13 rows selected.
备库节点2：
SQL> select process,status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CONNECTED
DGRD ALLOCATED
DGRD ALLOCATED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
6 rows selected.
5.3、测试创建pdb
--主库创建pdb.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TMISPDB READ WRITE NO
4 ORCLPDB READ WRITE NO
SQL> create pluggable database healpdb admin user leo identified by leo role=(connect);
Pluggable database created.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TMISPDB READ WRITE NO
4 ORCLPDB READ WRITE NO
5 HEALPDB MOUNTED
SQL> alter pluggable database healpdb open instances=all;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TMISPDB READ WRITE NO
4 ORCLPDB READ WRITE NO
5 HEALPDB READ WRITE NO
--备库查询
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TMISPDB READ ONLY NO
4 ORCLPDB READ ONLY NO
5 HEALPDB MOUNTED
SQL> alter pluggable database healpdb open instances=all;
Pluggable database altered.
结论：备库数据实时同步正常，至此19c 多租户rac到rac的adg搭建完成.
参考文档：https://blog.csdn.net/jycjyc/article/details/104710732
