1.环境规划

集群1 集群2
主机 rac1,rac2 olrac1,olrac2,olrac3
ip 192.168.5.111-112 192.168.5.131-133
实例名sid orcldg1,orcldg2 orcl1,orcl2,orcl3
scan-ip 192.168.5.200 192.168.5.100
存储 asm asm

1.集群安装

参考
oracle rac 环境搭建：https://www.yuque.com/docs/share/4196ef7e-8cbf-4268-87d5-ae4b775d9322?#
oracle rac 安装：https://www.yuque.com/docs/share/97fd8e3b-a025-4375-8afb-a1ef1eb2f406?#
rac db 安装：https://www.yuque.com/docs/share/5e50fb2f-8a16-4987-9e7d-9d47ec3939ba?#
rac 节点添加：https://www.yuque.com/docs/share/45aa6892-b580-42bb-a455-8c055243a259?#

2.rac 开启归档

任意节点

1）修改cluster_database 修改为false

  1. alter system set cluster_database=false scope=spfile  sid='orcl1';

2）关闭所有实例数据库

  1. srvctl stop database -db orcl

3）启动olracdb1本地实例挂载数据库

  1. startup mount;

4)启动归档

  1. alter database archivelog;

5)修改归档日志文件参数和路径

注意：log_archive_dest_1和log_archive_dest冲突两者只能设置一个

  1. alter  system set  log_archive_format='%t_%s_%r.arc'  scope=spfile  sid='*';
  2. alter  system set log_archive_dest_1='location=+data/orcl/archivelog'   scope=spfile  sid='*';

6）修改cluster_databae 值为true

  1. alter system set cluster_database=true scope=spfile  sid='orcl1';

7）关闭实例启动数据库

  1. shutdown immediate;
  2. srvctl start database -db orcl

2.修改主库参数

1启用强制日志

  1. select   force_logging  from v$database;
  2. alter database force logging;

2.设置主库初始化参数

  1. alter system set db_unique_name='orcl' scope=spfile;
  2. alter system set db_unique_name=orcl scope=spfile;   #不加单引号设置的db_unique_name为大写
  3. alter system set log_archive_config='DG_CONFIG=(orcl,orcldg)' scope=both sid='*';
  4. alter system set log_archive_dest_1='LOCATION=+data/orcl/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' scope=both sid='*';
  5. alter system set log_archive_dest_2='SERVICE=tnsorcldg LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg' scope=both sid='*';

3.设置转换文件路径

注意：如果不做主备切换这个值不用设置
pdb注意pdb_file_name_convert 参数

  1. alter system set db_file_name_convert='+DATA/orcldg/datafile','+DATA/orcl/datafile','+DATA/orcldg/tempfile','+DATA/orcl/tempfile' scope=spfile sid='*';
  2. alter system set log_file_name_convert='+DATA/orcldg/onlinelog','+DATA/orcl/onlinelog' scope=spfile sid='*';

4.设置主备数据文件添加自动同步

  1. alter system set standby_file_management=auto scope=both sid='*';

5.设置切换client/server

注意：如果不做主备切换这个值不用设置

  1. alter system set fal_client='tnsorcl' scope=both sid='*';
  2. alter system set fal_server='tnsorcldg' scope=both sid='*';

3.设置/etc/hosts

注意：两个集群所有的主机都要添加里面

  1. #Public                                                                                                    
  2. 192.168.5.131 olrac1                                                                                         
  3. 192.168.5.132 olrac2                                                                                         
  4. 192.168.5.133 olrac3                                                                                                            
  5. #Virtual                                                                                                   
  6. 192.168.5.141 olrac1-vip                                                                                     
  7. 192.168.5.142 olrac2-vip                                                                                     
  8. 192.168.5.143 olrac3-vip                                                                                                          
  9. #Private                                                                                                   
  10. 10.10.10.21  olrac1-priv
  11. 11.11.11.21  olrac1-priv                                                                                     
  12. 10.10.10.22  olrac2-priv
  13. 11.11.11.22  olrac2-priv                                                                                     
  14. 10.10.10.23  olrac3-priv
  15. 11.11.11.23  olrac3-priv                                                                                                           
  16. #Scan-ip                                                                                                   
  17. 192.168.5.100 olrac-scan
  19. #Public                                                                                                    
  20. 192.168.5.111 rac1                                                                                         
  21. 192.168.5.112 rac2                                                                                         
  22. 192.168.5.113 rac3                                                                                                         
  23. #Virtual                                                                                                   
  24. 192.168.5.101 rac1-vip                                                                                     
  25. 192.168.5.102 rac2-vip                                                                                     
  26. 192.168.5.103 rac3-vip                                                                                                         
  27. #Private                                                                                                   
  28. 10.10.10.11  rac1-priv                                                                                     
  29. 10.10.10.12  rac2-priv                                                                                     
  30. 10.10.10.13  rac3-priv                                                                                                          
  31. #Scan-ip                                                                                                   
  32. 192.168.5.200 rac-scan

4.设置主备库监听和服务

先设置点对点 同步，同步完成之后，再修改到scan-ip
netca
netmgr
主备库oracle目录下所有节点：tnsnames.ora

  1. TNSORCLDG =
  2.   (DESCRIPTION =
  3.     (ADDRESS_LIST =
  4.       (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
  5.     )
  6.     (CONNECT_DATA =
  7.       (SERVICE_NAME = orcl)
  8.     )
  9.   )
  11. TNSORCL =
  12.   (DESCRIPTION =
  13.     (ADDRESS_LIST =
  14.       (ADDRESS = (PROTOCOL = TCP)(HOST = olrac1-vip)(PORT = 1521))
  15.     )
  16.     (CONNECT_DATA =
  17.       (SERVICE_NAME = orcl)
  18.     )
  19.   )

备库gird 目录下所有节点：listener.ora
netca
#注意：备库静态监听指向地址是oracle 账户下$ORACLE_HOME

  1. VALID_NODE_CHECKING_REGISTRATION_LISTENER_DG = SUBNET
  3. LISTENER_DG =
  4.   (DESCRIPTION =
  5.     (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_DG))
  6.   )
  8. ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_DG = ON
  10. SID_LIST_LISTENER_DG =
  11.   (SID_LIST =
  12.     (SID_DESC =
  13.       (GLOBAL_DBNAME = orcl)
  14.       (ORACLE_HOME = /u01/app/oracle/product/19.3.0.0/dbhome_1)
  15.       (SID_NAME = orcldg1)
  16.     )
  17.   )
  19. ADR_BASE_LISTENER_DG = /u01/app/oracle

备库设置

1.拷贝密码文件

注意：拷贝需要执行首次同步的节点

  1. 主库grid账户
  2. asmcmd  
  3. cd +data/orcl/password
  4. pwcopy pwdorcl.287.1040139075 /u01/app/oracle/product/19.3.0.0/dbhome_1/dbs/orapworcl
  6. scp orapworcl oracle@192.168.5.111:/u01/app/oracle/product/19.3.0.0/dbhome_1/dbs/orapworcldg

2.创建备库spfile文件

  1. db_name='orcl'
  2. db_unique_name='orcldg'
  3. sga_target=3G
  4. pga_aggregate_target=800M
  5. audit_file_dest='/u01/app/oracle/admin/orcldg/adump'
  6. compatible='19.0.0'
  7. remote_login_passwordfile='EXCLUSIVE'
  8. control_files='+data'
  9. log_archive_config='DG_CONFIG=(orcl,orcldg)'
  10. log_archive_dest_1='LOCATION=+data/orcldg/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcldg'
  11. log_archive_dest_2='SERVICE=tnsorcl LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
  12. db_file_name_convert='+DATA/orcl/datafile','+DATA/orcldg/datafile','+DATA/orcl/tempfile','+DATA/orcldg/tempfile'
  13. log_file_name_convert='+DATA/orcl/onlinelog','+DATA/orcldg/onlinelog' 
  14. fal_client='tnsorcldg'
  15. fal_server='tnsorcl'
  16. standby_file_management='AUTO'
  17. db_create_file_dest='+data'
  18. db_recovery_file_dest='+data'
  19. db_recovery_file_dest_size=20g

3.启动备库数据库到nomount状态

  1. mkdir /u01/app/oracle/admin/orcldg/adump -p
  2. startup nomount

4.duplicate 同步主库数据

  1. rman target sys/Oracle123@192.168.5.131:1521/orcl auxiliary sys/Oracle123@192.168.5.111:1522/orcl
  3. duplicate target database for standby from active database nofilenamecheck;

5.重建spfile

注意：修改pfile的时候不要关闭数据库，create 完成之后在重启。

  1. 备库节点创建pfile  追加参数
  2. create  pfile  from spfile;
  3. 注意备库有多少个节点就添加多少个instance_number，thread，undo_tablespace
  4. vi initorcldg1.ora
  6. *.cluster_database=TRUE
  7. instance_number=2
  8. orcldg1.instance_number=1
  9. orcldg2.instance_number=2
  10. orcldg1.thread=1
  11. orcldg2.thread=2
  12. orcldg1.undo_tablespace='UNDOTBS1'
  13. orcldg2.undo_tablespace='UNDOTBS2'
  14. ------------------------
  16. create spfile='+data' from pfile='/u01/app/oracle/product/19.3.0.0/dbhome_1/dbs/initorcldg1.ora';
  18.  #修改pfile文件
  19. cd  /u01/app/oracle/product/19.3.0.0/dbhome_1/dbs/
  20. vi initorcldg1.ora
  21. spfile='+data/orcldg/parameterfile/spfile.290.1040221333'

6.创建物理standby

  1. alter  database add STANDBY LOGFILE THREAD 1 size 300m;
  2. alter  database add STANDBY LOGFILE THREAD 1 size 300m;
  3. alter  database add STANDBY LOGFILE THREAD 1 size 300m;
  5. alter  database add STANDBY LOGFILE THREAD 2 size 300m;
  6. alter  database add STANDBY LOGFILE THREAD 2 size 300m;
  7. alter  database add STANDBY LOGFILE THREAD 2 size 300m;
  9. ----------------------------------------------------
  10. select decode(g.THREAD#, null, vs.THREAD#, g.THREAD#) THREAD#,
  11.        decode(g.GROUP#, null, vs.GROUP#, g.GROUP#) GROUP#,
  12.        decode(g.BYTES, null, vs.BYTES / 1024 / 1024, g.BYTES / 1024 / 1024) BYTES,
  13.        decode(g.STATUS, null, vs.STATUS, g.STATUS) STATUS,
  14.        decode(g.ARCHIVED, null, vs.ARCHIVED, g.ARCHIVED) ARCHIVED,
  15.        --decode(g.GROUP#,null,'STANDBY REDO','REDO') type,
  16.        gf.TYPE,
  17.        gf.MEMBER
  18.   from v$logfile gf
  19.   left join v$log g
  20.     on gf.GROUP# = g.GROUP#
  21.   left join v$standby_log vs
  22.     on gf.GROUP# = vs.GROUP#
  23.  order by 1, 2;

7.开启同步:

  1. 查看异常
  2. select dest_name,status,error from v$archive_dest_status where dest_id <5;
  3. select open_mode,database_role,protection_mode,protection_level from v$database;
  7. 开启同步:
  8. alter database open;
  9. 开启实时同步:
  10. alter database recover managed standby database using current logfile disconnect from session;
  11. alter database recover managed standby database using current logfile disconnect;
  12. 开启同步（日志切换才会同步）
  13. alter database recover managed standby database disconnect from session;
  14. 停止同步：
  15. alter database recover managed standby database cancel;
  17. v$database;
  18. 主备库查看:
  19. select open_mode,database_role,protection_mode,protection_level from v$database;
  21. 最大性能->最高可用模式
  22. alter database set standby database to maximize availability;
  24. alter database set standby database to maximize performance;

8.添加备库到srvctl 管理

  1. 1.添加数据库(oracle用户下)
  2. srvctl add database -h  #查看命令说明
  3. crsctl stat res -t
  4. srvctl stop database -d fxdb
  5. srvctl remove database -d fxdb
  6. srvctl add database -db orcldg -o /u01/app/oracle/product/19.3.0.0/dbhome_1  -p +data/orcldg/parameterfile/spfile.290.1040221333  -c rac -r physical_standby
  7. 2.添加实例(oracle用户下)
  8. srvctl add instance -h
  9. srvctl add instance -d orcldg -i orcldg1 -n rac1
  10. srvctl add instance -d orcldg -i orcldg2 -n rac2
  11. crsctl stat res -t
  12. srvctl stop database -d orcldg -o immediate    #注意数据库重启之后需手工开起同步
  13. srvctl start database -d orcldg

9.异常检查视图

  1. select  *  from gv$managed_standby;
  2. select  *  from gv$standby_log;
  3. select dest_name,status,error from v$archive_dest_status where dest_id <5;
  4. select open_mode,database_role,protection_mode,protection_level,db_unique_name from v$database;
  5. 【解决同步故障】
  6. select * from v$managed_standby;
  8. 主库:
  9. select * from v$archive_dest;
  11. select * from v$archive_dest_status;

10.修改tns连接，备库添加remote_listener

主备库oracle目录下备库连接
tnsnames.ora

  2. TNSORCLDG =
  3.   (DESCRIPTION =
  4.     (ADDRESS_LIST =
  5.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.200)(PORT = 1521))
  6.     )
  7.     (CONNECT_DATA =
  8.       (SERVICE_NAME = orcl)
  9.     )
  10.   )
  12. TNSORCL =
  13.   (DESCRIPTION =
  14.     (ADDRESS_LIST =
  15.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.100)(PORT = 1521))
  16.     )
  17.     (CONNECT_DATA =
  18.       (SERVICE_NAME = orcl)
  19.     )
  20.   )

备库添加remote_listener
注意：添加完成这步之后，最好做下同步测试。

  1. alter  system  set remote_listener='rac-scan:1521'  scope=both;

11.asm重建密码文件

  1. pwcopy --dbuniquename orcldg  /u01/app/oracle/product/19.3.0.0/dbhome_1/dbs/orapworcldg2 +data/orcldg/pworcldg
  2. su   - oracle  #注意不能在grid  账户下面执行会提示权限不足
  3. srvctl modify database -db orcldg -pwfile +data/orcldg/password/pwdorcldg.356.1040410249
  4. srvctl config database -db orcldg

开启同步

1.添加Standby日志组

Standby日志组=日志组+1

  1. alter database add standby logfile group 4  size 200m;
  2. alter database add standby logfile group 5  size 200m;
  3. alter database add standby logfile group 6  size 200m;
  4. alter database add standby logfile group 7  size 200m;
  7. alter database drop standby logfile group 4;
  8. alter database drop standby logfile group 5;
  9. alter database drop standby logfile group 6;
  10. alter database drop standby logfile group 7;
  12. 视图:
  13. v$logfile
  14. v$standby_log
  15. --------------------------------------------------------------
  16. select decode(g.THREAD#, null, vs.THREAD#, g.THREAD#) THREAD#,
  17.        decode(g.GROUP#, null, vs.GROUP#, g.GROUP#) GROUP#,
  18.        decode(g.BYTES, null, vs.BYTES / 1024 / 1024, g.BYTES / 1024 / 1024) BYTES,
  19.        decode(g.STATUS, null, vs.STATUS, g.STATUS) STATUS,
  20.        decode(g.ARCHIVED, null, vs.ARCHIVED, g.ARCHIVED) ARCHIVED,
  21.        --decode(g.GROUP#,null,'STANDBY REDO','REDO') type,
  22.        gf.TYPE,
  23.        gf.MEMBER
  24.   from v$logfile gf
  25.   left join v$log g
  26.     on gf.GROUP# = g.GROUP#
  27.   left join v$standby_log vs
  28.     on gf.GROUP# = vs.GROUP#
  29.  order by 1, 2;

2.开起实时同步

  1. 开启redo应用(实时同步)commit
  2. 自Oracle Database 12.1起，USING CURRENT LOGFILE子句已弃用
  3. alter database recover managed standby database using current logfile disconnect from session;弃用
  4. alter database recover managed standby database using current logfile disconnect;弃用
  6. alter database recover managed standby database disconnect;
  8. 停止redo应用
  9. alter database recover managed standby database cancel;
  10. 开启redo应用(日志切换才会同步)
  11. alter database recover managed standby database using archived logfile disconnect;
  12. alter system switch logfile;
  13. 查看状态:
  14. select name,open_mode,database_role,protection_mode,protection_level from v$database;

3.更改保护模式:

主库备库都需要执行 3 选 1

  1. alter database set standby database to maximize performance;最大性能
  2. alter database set standby database to maximize availability;最大可用
  3. alter database set standby database to maximize protection;最大保护 #只能在mount下

4.【DG启停顺序】

  1. 启动:
  2. 先备库，后主库
  4. 关闭:
  5. 先主库，后备库
  7. 前提:干净的关闭

5.解决同步故障

  1. 1.查下主库2节点，备库的配置
  2. 2.检查tns配置
  3. 3.检查fal_client,fal_server
  4. 4.查下备库是否在实时应用模式
  5. 5.查询相关视图
  6. v$managed_standby(备库查询)
  7. RFS：传输进程
  8. MRP0:日志应用进程
  9. v$archive_dest(主库)
  10. v$archive_dest_status(主库)
  11.  select dest_name,status,error from v$archive_dest_status where dest_id = 2;
  12.  select dest_name,status,error from v$archive_dest;

主备切换

1.确认角色

  1. 2个角色:primary和standby
  2. select database_role from v$database;

2.主库添加转换参数和standby 日志组

pdb注意pdb_file_name_convert 参数

  1. alter system set db_file_name_convert='+DATA/orcldg/datafile','+DATA/orcl/datafile','+DATA/orcldg/tempfile','+DATA/orcl/tempfile' scope=spfile sid='*';
  2. alter system set log_file_name_convert='+DATA/orcldg/onlinelog','+DATA/orcl/onlinelog' scope=spfile sid='*';
  4. alter system set db_file_name_convert='+DGDATA','+DATA' scope=spfile sid='*';
  5. alter system set log_file_name_convert='+DGDATA','+DATA' scope=spfile sid='*';
  1. alter  database add STANDBY LOGFILE THREAD 1 size 300m;
  2. alter  database add STANDBY LOGFILE THREAD 1 size 300m;
  3. alter  database add STANDBY LOGFILE THREAD 1 size 300m;
  4. alter  database add STANDBY LOGFILE THREAD 2 size 300m;
  5. alter  database add STANDBY LOGFILE THREAD 2 size 300m;
  6. alter  database add STANDBY LOGFILE THREAD 2 size 300m; 
  7. alter  database add STANDBY LOGFILE THREAD 3 size 300m;
  8. alter  database add STANDBY LOGFILE THREAD 3 size 300m;
  9. alter  database add STANDBY LOGFILE THREAD 3 size 300m;

3.验证是否能正常同步

4.停止业务刷新缓存

  1. alter system switch logfile;
  2. alter system flush buffer_cache;
  3. alter system checkpoint;
  4. shutdown immediate

5.切换

主备库启动单节点进行1→1切换
查询是否允许切换

  1. select name,database_role,switchover_status from v$database; 
  3. 主切备：   主库查询
  4. SWITCHOVER_STATUS:
  5. 主库:to standby:
  6. 备库:NOT ALLOWED
  7. 备库切回主库：
  8. SWITCHOVER_STATUS:
  9. 备库为：to primary
  1. alter database commit to switchover to physical standby;
  2. alter database commit to switchover to physical standby with session shutdown;(会话连接着)

备库到主库

  1. alter database commit to switchover to primary;
  2. alter database commit to switchover to primary with session shutdown;

强制切换

  1. alter database switchover to orcldg verify;
  2. alter database switchover to orcldg ;
  3. alter database switchover to orcldg  force;     #强制切换
  4. 原主库状态为:shutdown
  5. 原备库状态为:mount

6.重新启动数据库

最好最备库都重新启动，
备库切换到主库时为mount 状态，需要手动open
主库切换为备库时会直接shutdown,需要手动startup 之后手动同步。

  1. startup
  2. 查询状态:
  3. select open_mode,database_role from v$database;
  4. 开启实时应用:
  5. alter database recover managed standby database using current logfile disconnect;

7.查看保护模式

  1. select open_mode,database_role,protection_mode,protection_level,db_unique_name from v$database;
  3. 保护模式查看:
  4. select name, protection_mode,protection_level from v$database;