DG搭建
DG的三种同步模式:
模式 | 功能 |
---|---|
最大保护模式(Maximum protection) | Redo日志必须在主库和备库写入后才能提交。 |
最大性能(Maximum Performance) | Redo日志发送后,主库就可以提交。 |
最大可用性(Maximum Availability) | 结合两者优点 |
实验环境
配置 | 主库 | 备库 |
---|---|---|
系统版本 | Oracle-Linux-R6-U9 | Oracle-Linux-R6-U9 |
磁盘 | 100G | 100G |
软件 | Oracle Soft + database | Oracle Soft |
数据库属性 | 开启FORCE LOGGING(强制日志) | - |
归档 | 开启归档(见5.3开启归档日志) | - |
主机名 | Primary | Standby |
监听服务名 | Primary | Standby |
sid_name | orcl | orcl |
db_name | orcl | orcl |
instance_name | orcl | orcl |
db_unique_name | orcl | orclst |
service_names | orcl | orclst |
1.主库开启强制日志
--查询是否开启强制日志
SELECT force_logging FROM v$database;
--开启强制日志
ALTER DATABASE FORCE LOGGING;
2.主库添加standby redo log
作用:用于主备切换
要求:1.数量:redo日志组数+1;2.大小:等于redo log大小
--查询redo大小、组数
select GROUP#,BYTES/1024/1024 MB from v$log;
--查询redo日志组路径
select GROUP#,MEMBER from v$logfile;
--添加redo
alter database add standby logfile group 4 '/u01/oradata/orcl/redo04.log' size 50m;
alter database add standby logfile group 5 '/u01/oradata/orcl/redo05.log' size 50m;
alter database add standby logfile group 6 '/u01/oradata/orcl/redo06.log' size 50m;
alter database add standby logfile group 7 '/u01/oradata/orcl/redo07.log' size 50m;
--查看添加日志组信息
select group#,sequence#,status, bytes/1024/1024 from v$standby_log;
其他命令: --删除redo,要自行删除物理文件 alter database drop logfile group 3; --更改redo大小(先添加再删除即可)
3.主库监听配置
vim listener.ora 修改内容如下: SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME=Primary) (SID_NAME = orcl) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) ) ) -- Primary 为监听服务名称,可随意填写。SID_NAME = ORacle_sid 查明后填写
vim tnsnames.ora 修改内容如下: Primary = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.218)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = Primary) ) ) Standby = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.219)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = Standby) ) ) -Primary:该监听服务别名,可以随意填写。 SERVICE_NAME = Primary:根据监听服务名称填写 -Standby:该监听服务别名,可以随意填写。 SERVICE_NAME = Standby:根据监听服务名称填写
4.主库参数配置
#参数说明:orcl为主库db_unique_name,orclst为备库db_unique_name Primary为主库监听服务名称,Standby为备库监听服务名称 alter system set log_archive_config='DG_CONFIG=(orcl,orclst)' scope=spfile; alter system set log_archive_dest_1='location=/u01/oradata/archivelog VALI..D_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' scope=both; alter system set log_archive_dest_2='SERVICE=Standby LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclst' scope=spfile; alter system set log_archive_dest_state_1=enable scope=spfile; alter system set log_archive_dest_state_2=enable scope=spfile; alter system set remote_login_passwordfile=exclusive scope=spfile; alter system set fal_client='Primary' scope=both; alter system set fal_server='Standby' scope=both; alter system set standby_file_management=auto scope=spfile; ---- 如果主库和备库数据路径不相同,则需要设置替换目录,如何设置如下: alter system set db_file_name_convert='/u01/oradata/orcl','/u01/oradata/orcl' scope=spfile; alter system set log_file_name_convert='/u01/oradata/orcl','/u01/oradata/orcl' scope=spfile; 1.主备库名称 2.配置本地归档路径 3.配置远程归档路径 4.开启归档通道1 5.开启归档通道2 6.密码文件独占 7.设置本地库 8.设置远程库 9.设置备库文件自动管理 10.设置数据文件路径 11.设置日志文件路径
5.传输参数文件和密码文件到备库
在主库上生成初始化参数文件 SQL> create pfile from spfile;从主库拷贝初始化参数文件和密码文件 通过scp传输到备库: scp $ORACLE_HOME/dbs/orapworcl 192.168.100.219:/u01/app/oracle/product/11.2.0/db_1/dbs scp $ORACLE_HOME/dbs/initorcl.ora 192.168.100.219:/u01/app/oracle/product/11.2.0/db_1/dbs ---如果主库sid和备库sid不相同,则需要把密码文件和参数文件名进行修改 如:mv orapworcl orapworclst && mv initorcl.ora initorclst.ora
6.备库修改参数文件
vim initorcl.ora 修改以下内容: --------------------------- *.audit_trail='os' *.db_unique_name='orclst' *.fal_client='Standby' *.fal_server='Primary' *.log_archive_dest_1='location=/u01/oradata/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orclst' *.log_archive_dest_2='SERVICE=Primary LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl' --------------------------- 注意:initorclst.ora 涉及的目录 如果没有要手动创建 mkdir -p /u01/oradata/orcl/ mkdir -p /u01/app/oracle/admin/orcl/adump mkdir -p /u01/app/oracle/flash_recovery_area/orcl/ mkdir -p /u01/oradata/archivelog
7.使用初始化参数文件启动备库
--登陆到备库数据库 sqlplus / as sysdba --创建spfile,可在$ORACLE_HOME/dbs下查看 create spfile from pfile; --启动数据库到nomount状态 startup nomount;
8.备库监听配置
vim listener.ora 修改内容如下 SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME=Standby) (SID_NAME = orcl) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) ) ) -- Primary 为监听服务名称,可随意填写。SID_NAME = ORacle_sid 查明后填写vim tnsnames.ora 修改内容如下 Primary = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.218)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = Primary) ) ) Standby = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.219)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = Standby) ) ) -Primary:该监听服务别名,可以随意填写。 SERVICE_NAME = Primary:根据监听服务名称填写 -Standby:该监听服务别名,可以随意填写。 SERVICE_NAME = Standby:根据监听服务名称填写 ------------------------- 测试监听 Primary和Standby监听都要启动 tnsping Primary tnsping Standby
9.备库通过Rman远程登录,开始进行复制
方法一:通过duplicate开始克隆数据库
rman target sys/oracle@Primary auxiliary sys/oracle@Standby nocatalog --------两边目录结构不同 则不加nofilenamecheck duplicate target database for standby from active database nofilenamecheck;
方法二:通过Rman备份数据库,再进行恢复
1.主库备份数据文件、归档日志文件、控制文件 backup database format '/u01/backup/data_%d_%T_%s_%p' tag 'data'; sql 'alter system archive log current'; backup archivelog all format='/u01/backup/arch_%d_%T_%s_%p' tag 'arc'; backup current controlfile for standby format='/u01/backup/ctl_%d_%T_%s_%p' tag 'cur'; 2.远程传输到备库 scp /u01/backup/ root@192.168.100.219:/u01/backup/ 3.备库开始恢复 rman target / alter database nomount restore standby controlfile from '/u01/backup/ctl_ORCL_20240509_23_1'; alter database mount; catalog start with "/u01/backup"; restore database; recover database; [如报错RMAN-06054。执行下面语句:recover database until scn 1012850;] alter database open resetlogs;
10.开启备库,设置主备同步
alter database open; alter database recover managed standby database using current logfile disconnect from session;
11.同步情况检查
---oracle进程检查 sql > select process from v$managed_standby; 主库显示:ARCH、ARCH、ARCH、ARCH 备库显示:ARCH、MRPO和RFS表示正常 ---归档序列号是否相同 sql> select max(sequence#) from v$archived_log where applied = 'YES'
12.日常管理命令
--数据库状态查询 select switchover_status,database_role from v$database; --查看传输延迟 select * from v$dataguard_stats; --关闭同步 alter database recover managed standby database cancel; --开启同步 alter database recover managed standby database using current logfile disconnect from session; --查询GAP(归档丢失) SELECT THREAD#,LOW_SEQUENCE#, HIGH_SEQUENCE# FROM VSARCHIVE_GAP;
13.GAP恢复
备库GAP(归档丢失)文件丢失: 1.从主库复制归档文件到备库 2.备库手动注册恢复 ALTER DATABASE REGISTER LOGFILE '路径/文件.dbf';DG增量恢复: 故障环境:备库同步断开,备库没同步归档日志,同时主库有了增量数据,归档日志删除。 1.备库停止同步 alter database recover managed standby database cancel; 2.查询备库最小SCN号 select f.checkpoint_change#,d.enabled from v$datafile_header f,v$datafile d where f.file# = d.file#; 3.编辑备份脚本 ----------------------------------------------------------- #!/bin/ksh $ORACLE_HOME/bin/rman target/ log=/tmp/backup_dg.log <<EOF run { allocate channel t1 type disk; allocate channel t2 type disk; BACKUP as compressed backupset INCREMENTAL FROM SCN SCN号 DATABASE FORMAT '/oradata/dg_%U' tag 'Forstandby'; release channel t1; release channel t2; } EOF ----------------------------------------------------------- 4.在主库执行脚本进行备份 sh dg.sh 5.备份主库的控制文件 ALTER DATABASE CREATE standby controlfile As '/tmp/standby2.ctl'; 6.把生成的备份的数据文件和控制文件复制到备库 7.关闭备库并启动到mount状态 shutdown immediate; startup mount; 8.Rman备份备库控制文件 rman target / backup current controlfile format '/tmp/standby1.ctl' 9.Rman恢复数据 --备库重新启动到nomount状态 alter database nomount --备库恢复主库控制文件 restore controlfile from '/tmp/standby2.ctl' --注册备份集 CATALOG START WITH '拷贝过来的数据路径' --增量数据恢复 recover database noredo; 11.启动备库开启同步 alter database open; alter database recover managed standby database using current logfile disconnect from session;
14.GD切换
1.查询主备库是否满足切换条件 SQL> select switchover_status,database_role,open_mode from v$database; 主库显示:TO STANDBY/PRIMARY,如果显示SESSION ACTIVE表示还有活动的会话,需要关闭会话再检查 备库显示:NOT ALLOWED/PHYSICAL STANDBY 2.主库切换备库 SQL>alter database commit to switchover to physical standby; SQL>shutdown immediate; SQL>startup mount; 3.备库切换主库 SQL>alter database commit to switchover to physical Primary; SQL>alter database open; 4.新备库开启同步 SQL>alter database recover managed standby database using current logfile disconnect from session;[主库故障,切换备库] 1.取消DG同步 alter database recover managed standby database cancel; 2.备库failover跟换 alter database recover managed standby database finish; 3.查看角色 select name,log_mode,open_mode,database_role,switchover_status,db_unique_name from v$database; 4.备库切换 alter database commit to switchover to primary with session shutdown;