1.rac 开启归档

任意节点

1）修改cluster_database 修改为false

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

2）关闭所有实例数据库

  1. srvctl stop database -db racdb

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/olracdb/archivelog'   scope=spfile  sid='*';

6）修改cluster_databae 值为true

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

7）关闭实例启动数据库

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

2.修改主库参数

1启用强制日志

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

2.设置主库初始化参数

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

3.设置转换文件路径

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

  1. alter system set db_file_name_convert='/u01/app/oracle/oradata/olracdg','+DATA/olracdb/datafile','/u01/app/oracle/oradata/olracdg','+DATA/olracdb/tempfile' scope=spfile sid='*';
  2. alter system set log_file_name_convert='/u01/app/oracle/oradata/olracdg','+DATA/olracdb/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='tnsolracdb' scope=both sid='*';
  2. alter system set fal_server='tnsolracdg' scope=both sid='*';

6.设置访问服务

修改tnsname.ora 文件
注意：两边都要设置

  1. TNSOLRACDB =
  2.   (DESCRIPTION =
  3.     (ADDRESS_LIST =
  4.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.131)(PORT = 1522))
  5.     )
  6.     (CONNECT_DATA =
  7.       (SERVICE_NAME = olracdb)
  8.     )
  9.   )
  11. TNSOLRACDG =
  12.   (DESCRIPTION =
  13.     (ADDRESS_LIST =
  14.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.134)(PORT = 1522))
  15.     )
  16.     (CONNECT_DATA =
  17.       (SERVICE_NAME = olracdg)
  18.     )
  19.   )

测试

  1. tnsping 192.168.5.131 1522
  2. tnsping 192.168.5.134 1522

3.备库设置

1.拷贝密码文件

变化:
11g及之前版本数据库中密码文件存储在$ORACLE_HOME/dbs下
orapwSID
12C开始，密码文件存放在ASM共享存储中。

  1. 查询密码文件位置:
  2. srvctl config database -d olracdb
  3.  su - grid
  4. asmcmd   cp  +DATA/OLRACDB/PASSWORD/pwdolracdb.287.1035686333  /home/grid/orapwolracdg

2.创建spfile

touch initolracdg.ora

  1. db_name='olracdb'
  2. db_unique_name='olracdg'
  3. sga_target=3G
  4. pga_aggregate_target=800M
  5. audit_file_dest='/u01/app/oracle/admin/olracdg/adump'
  6. compatible='19.0.0'
  7. remote_login_passwordfile='EXCLUSIVE'
  8. control_files='/u01/app/oracle/oradata/olracdg/control01.ctl','/u01/app/oracle/oradata/olracdg/control02.ctl'
  9. log_archive_config='DG_CONFIG=(olracdb,olracdg)'
  10. log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=olracdg'
  11. log_archive_dest_2='SERVICE=tnsolracdb LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=olracdb'
  12. log_archive_format='%t_%s_%r.arc'
  13. db_file_name_convert='+DATA/OLRACDB/DATAFILE','/u01/app/oracle/oradata/olracdg','+DATA/OLRACDB/TEMPFILE','/u01/app/oracle/oradata/olracdg'
  14. log_file_name_convert='+DATA/OLRACDB/ONLINELOG','/u01/app/oracle/oradata/olracdg'
  15. fal_client='tnsolracdg'
  16. fal_server='tnsolracdb'
  17. standby_file_management='AUTO'
  18. db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/'                                                                                                                                         
  19. db_recovery_file_dest_size=20g  
  20. db_create_file_dest='/u01/app/oracle/oradata'
  1. mkdir    /u01/app/oracle/fast_recovery_area   -p 
  2. mkdir    /u01/app/oracle/admin/olracdg/adump   -p 
  3. mkdir    /u01/app/oracle/oradata/olracdg    -p

3.配置备库监听

su - grid
netca 创建listener_dg
netmgr 配置listener_dg 端口，$ORACLE_HOME路径
lsnrctl start listener_dg
listener.ora

  1. SID_LIST_LISTENER =
  2.   (SID_LIST =
  3.     (SID_DESC =
  4.       (GLOBAL_DBNAME = olracdg)
  5.       (ORACLE_HOME = /u01/app/oracle/product/19.3.0.0/dbhome_1)
  6.       (SID_NAME = olracdg)
  7.     )
  8.   )
  10. LISTENER =
  11.   (DESCRIPTION =
  12.     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.134)(PORT = 1522))
  13.   )
  15. ADR_BASE_LISTENER = /u01/app/oracle

测试

  1.  sqlplus sys/Oracle123@tnsolracdg as sysdba 
  2.  sqlplus sys/Oracle123@tnsolracdb as sysdba

4.设置环境变量

  1. export ORACLE_BASE=/u01/app/oracle                                                                                                    
  2. export ORACLE_HOME=/u01/app/oracle/product/19.3.0.0/dbhome_1                                                                          
  3. export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib                                                                              
  4. export PATH=$ORACLE_HOME/bin:$PATH                                                                                                    
  5. alias sqlplus='rlwrap sqlplus'                                                                                                        
  6. export PATH

4.使用Duplicate创建物理standby

1.duplicate
1.1离线—rman备份集
1.2在线—duplicate,适合局域网
rman target sys/Oracle123@tnsfxdb 必须指定密码
rman target / 最好不要使用

  1. rman target sys/Oracle123@tnsolracdb auxiliary sys/Oracle123@tnsolracdg
  3. duplicate target database for standby from active database nofilenamecheck;

5.启用dg同步

1.备库添加standby 日志组

  1.     注意：日志组大小要跟主库redolog文件的大小一样，文件组的数量是单节点文件组的数量再加1.<br />视图：v$log v$logfile v$standby_log
  1. alter  database add STANDBY LOGFILE THREAD 1 GROUP 11;
  2. alter  database add STANDBY LOGFILE THREAD 1 GROUP 12;
  3. alter  database add STANDBY LOGFILE THREAD 1 GROUP 13;
  5. alter  database add STANDBY LOGFILE THREAD 2 GROUP 14;
  6. alter  database add STANDBY LOGFILE THREAD 2 GROUP 15;
  7. alter  database add STANDBY LOGFILE THREAD 2 GROUP 16;

2.开启同步

  1. startup mount 
  4. 开启redo应用(实时同步)commit
  5. 自Oracle Database 12.1起，USING CURRENT LOGFILE子句已弃用
  6. alter database recover managed standby database using current logfile disconnect from session;弃用
  7. alter database recover managed standby database using current logfile disconnect;弃用
  9. alter database recover managed standby database disconnect;
  11. 停止redo应用
  12. alter database recover managed standby database cancel;
  13. 开启redo应用(日志切换才会同步)
  14. alter database recover managed standby database using archived logfile disconnect;
  15. alter system switch logfile;
  17. 查看状态:
  18. select name,open_mode,database_role,protection_mode,protection_level from v$database;
  20. 更改保护模式:
  21. alter database set standby database to maximize performance;最大性能
  22. alter database set standby database to maximize availability;最大可用
  23. alter database set standby database to maximize protection;最大保护

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;最大保护

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;
  13.  #备库执行查看同步延迟
  14.  select value from v$dataguard_stats where name='apply lag';

6.主备库切换

https://www.yuque.com/yongle-okrsc/kb/bh144i#DPnhS