一、 环境说明
开始配置 Oracle Active Data Gurad 之前， 需要事先准备好相应的环境， 根据规划安装主备库的数据库环境，备库只需安装数据库软件，不需要创建实例，本次实施环境全程通过vmware workstation搭建的虚拟机环境进行的测试,，先搭建一个主库环境，不建库，然后通过虚拟机克隆的方式生成备库的环境，再在主库上建库，配置ADG的环境。
1.1、基础环境
1.1.1、操作系统版本
Red Hat Enterprise Linux Server release 6.5 (Santiago) Kernel \r on an \m |
注：主从库尽量保持一致。
1.1.2、数据库版本
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production [oracle@primary OPatch]$ ./opatch lspatches 35574075;Database Patch Set Update : 11.2.0.4.231017 (35574075) OPatch succeeded. |
注：主从库尽量保持一致。
1.1.3、存储规划
项目 | 项目值 | 备注 |
数据库安装目录 | /u01/app/oracle/product/11.2.0/db_1 | 按实际调整 |
数据库数据存储目录 | /u01/app/oracle/oradata/oradb | 按实际调整 |
数据库日志文件目录 | /u01/app/oracle/admin/oradb/adump | 按实际调整 |
数据库归档目录 | /home/oracle/archive |
注：主从库尽量保持一致。
1.1.4、IP地址
服务器 | 主机名 | IP地址 | 端口号 | 备注 |
主库 | Master | 192.168.56.100 | 1521 | 按实际调整 |
从库 | Slave | 192.168.56.101 | 1521 | 按实际调整 |
1.1.5、数据库参数
参数名 | 主库 | 从库 |
db_unique_name | pri | sty |
log_archive_config | DG_CONFIG=(pri,sty) | DG_CONFIG=(pri,sty) |
log_archive_dest_1 | LOCATION=/home/oracle/archive
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pri | LOCATION=/home/oracle/archive
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sty |
log_archive_dest_2 | SERVICE=sty
LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sty | SERVICE=pri
LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pri |
log_archive_dest_state_1 | ENABLE | ENABLE |
log_archive_dest_state_2 | ENABLE | ENABLE |
fal_server | sty | pri |
fal_client | pri | sty |
standby_file_management | AUTO | AUTO |
1.2、操作系统配置
1.2.1、配置域名解析文件
主库：/etc/hosts 192.168.56.100 primary 192.168.56.101 standby 备库：/etc/hosts 192.168.56.100 primary 192.168.56.101 standby |
1.2.2、主备服务器关闭防火墙
[root@primary ~]# service iptables stop iptables: Setting chains to policy
ACCEPT: filter [ OK ] iptables: Flushing firewall rules: [ OK ] iptables: Unloading modules: [ OK ] [root@primary ~]# chkconfig --level 345
iptables off |
1.2.3、备库侧创建必要的目录
mkdir -p u01/app/oracle/oradata/oradb mkdir -p u01/app/oracle/fast_recovery_area/oradb mkdir -p u01/app/oracle/admin/oradb/adump mkdir -p /home/oracle/archive |
二、 ADG部署
2.1、主库侧配置静态监听
listener.ora LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =
primary)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY =
EXTPROC1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME =
/u01/app/oracle/product/11.2.0/db_1) (PROGRAM = extproc) ) (SID_DESC = (SID_NAME = oradb) (ORACLE_HOME =
/u01/app/oracle/product/11.2.0/db_1) ) ) ADR_BASE_LISTENER =
/u01/app/oracle #关闭监听器 lsnrctl stop #启动监听器 lsnrctl start |
2.2、主库侧配置本地服务名
tnsnames.ora STY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS =
(PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = oradb) ) ) PRI = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS =
(PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = oradb) ) ) |
2.3、备库侧配置静态监听、本地服务名
#主库服务器操作 scp listener.ora tnasnames.ora
oracle@standby:$ORACLE_HOME/network/admin #备库服务器上操作 修改监听器文件中host为备机 LISTENER = (DESCRIPTION_LIST = (DESCRIPTION
= (ADDRESS =
(PROTOCOL = TCP)(HOST = standby)(PORT = 1521)) (ADDRESS =
(PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME
= PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) (PROGRAM =
extproc) ) (SID_DESC
= (SID_NAME =
oradb) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) ) ) ADR_BASE_LISTENER = /u01/app/oracle #关闭监听器 lsnrctl stop #启动监听器 lsnrctl start |
2.4、主库侧开启归档、附加日志
#配置归档路径和开启附加日志 startup mount; alter database archivelog; alter database force logging; alter database open; |
2.5、主库侧配置归档删除策略
$ rman target / RMAN> CONFIGURE ARCHIVELOG DELETION
POLICY TO SHIPPED TO ALL STANDBY; RMAN> show all; RMAN configuration parameters for database
with db_unique_name JBDB are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
# default CONFIGURE BACKUP OPTIMIZATION OFF; #
default CONFIGURE DEFAULT DEVICE TYPE TO DISK; #
default CONFIGURE CONTROLFILE AUTOBACKUP OFF; #
default CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR
DEVICE TYPE DISK TO '%F'; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 1
BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE
TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR
DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; #
default CONFIGURE ENCRYPTION FOR DATABASE OFF; #
default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; #
default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS
OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE ARCHIVELOG DELETION
POLICY TO APPLIED ON ALL STANDBY; CONFIGURE SNAPSHOT CONTROLFILE NAME TO
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_jbdb1.f'; # default RMAN> |
2.6、主库侧配置standby redo log
standby redo log的文件大小与primary 数据库online redo log 文件大小相同
standby redo log日志文件组的个数依照下面的原则进行计算：
Standby redo log组数公式>=(每个instance日志组个数+1)*instance个数
假如只有一个节点，这个节点有三组redolog，
所以Standby redo log组数>=(3+1)*1 == 4
所以至少需要创建4组Standby redo log。
#查看 Online redo log 大小及位置： select group#,thread#,members,bytes from v$log; select group#,member from v$logfile; #添加Standby redo log alter database add standby logfile group 4
'/u01/app/oracle/oradata/std_redo04.log' size 50m; alter database add standby logfile group 5 '/u01/app/oracle/oradata/std_redo05.log'
size 50m; alter database add standby logfile group 6
'/u01/app/oracle/oradata/std_redo06.log' size 50m; alter database add standby logfile group 7
'/u01/app/oracle/oradata/std_redo07.log' size 50m; #再次查看 Online redo
log 大小及位置： select group#,thread#,members,bytes from v$log; select group#,member from v$logfile; #查看 standby redo log 信息： SELECT
GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG; |
2.7、主库侧配置ADG参数
#备份spfile文件 create pfile=’/tmp/initbak20211013.ora’ from spfile; #配置ADG相关参数 alter system set db_unique_name=pri scope=spfile; alter system set log_archive_config =
'DG_CONFIG=(pri,sty)' scope=spfile; alter system set log_archive_dest_1 = 'LOCATION=/home/oracle/archive
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pri' scope=spfile; alter system set log_archive_dest_2 =
'SERVICE=sty LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=sty' scope=spfile; alter system set log_archive_dest_state_1 = ENABLE; alter system set log_archive_dest_state_2 = ENABLE; alter system set fal_server=sty scope=spfile; alter system set fal_client=pri scope=spfile; alter system set standby_file_management=AUTO
scope=spfile; #重启数据库生效 shutdown startup |
2.8、将主库侧密码文件发送至备库端相应位置
cd $ORACLE_HOME/dbs scp orapworadb oracle@standby:$ORACLE_HOME/dbs/orapworadb |
2.9、主库侧创建备库初始化参数文件发送至备库侧相应位置
create pfile='/tmp/initoradb.ora' from spfile; scp /tmp/initoradb oracle@standby:/$ORACLE_HOME/dbs/ |
2.10、备库侧修改参数文件
#配置ADG相关参数 sqlplus / as sysdba startup nomount; create spfile from pfile; #重启数据库 shutdown startup nomount alter system set db_unique_name=sty
scope=spfile; alter system set
log_archive_config='DG_CONFIG=(pri,sty)' scope=spfile; alter system set log_archive_dest_1 =
'LOCATION=/home/oracle/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=sty' scope=spfile; alter system set log_archive_dest_2 =
'SERVICE=pri LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=pri' scope=spfile; alter system set fal_server=pri
scope=spfile; alter system set fal_client=sty
scope=spfile; alter system set standby_file_management=AUTO
scope=spfile; #重启数据库生效 shutdown startup nomount |
2.11、初始化备库数据库
本次选用使用duplicate命令创建standby数据库，因为duplicate时间较长，需在后台运行防止过程中断：
vi dup_dataguard.sh rman target sys/system@pri
auxiliary sys/system@sty nocatalog <<EOF duplicate target
database for standby from active database dorecover nofilenamecheck; EOF nohup sh dup_dataguard.sh
> dup_dg.log & tail -200f dup_dg.log #为保证duplicate连接正常，可以先这样测试连接可用： sqlplus sys/system@pri as sysdba sqlplus sys/system@sty as sysdba |
2.12、备库侧开启实时日志应用
shutdown immediate; startup; select
database_role,protection_mode,protection_level,open_mode from v$database; select process,client_process,sequence#,status
from v$managed_standby; recover managed standby database using
current logfile disconnect from session; select
process,client_process,sequence#,status from v$managed_standby; |
2.13、主库侧数据库定时备份及归档日志清理机制
建立rman备份机制，同时清理过期失效的归档日志。(此处省略)
2.14、备库归档日志定期删除
部署定时任务进行历史归档清理 因为备库没有备份任务，为防止后续备库端归档满，需要部署定时任务进行历史归档清理，下面给出一个示例：
mkdir /home/oracle/scripts cd /home/oracle/scripts vi del_arch.sh rman target / <<EOF >>
/home/oracle/scripts/del_arch.log delete noprompt archivelog all
completed before 'sysdate - 1/24'; EOF chmod +x del_arch.sh crontab -e 0 * * * * /bin/sh
/home/oracle/scripts/del_arch.sh |
三、 ADG部署测试
3.1、主库侧执行日志切换
在主库侧执行日志，观测备库是否同步执行了切换操作。
#pri执行日志切换 archive log list; alter system switch logfile; archive log list; #sty观测日志的sequence是否同步变化 archive log list; archive log list; |
3.2、查看备库启动的DG进程
select
process,client_process,sequence#,status from v$managed_standby; |
3.3、查看数据库保护模式
#primary 端查看，我们可以看到数据库的保护模式为最大性能 select
database_role,protection_mode,protection_level,open_mode from v$database; #standby 端查看，也是一样的。 select database_role,protection_mode,protection_level,open_mode from v$database; |
3.4、查看DG的日志信息
select * from
v$dataguard_status; |
3.5、数据库同步LAG确认
#方式一： 查询结果显示，在DATUM_TIME列是接近当前时间的情况下，transport lag和apply lag两列的值均为0或是接近0，一般认定是同步正常。 set lines 1000 col value for a15 select * from v$dataguard_stats; #方式二： 查询备库的SCN，观察是否随着时间不断增长，以此来辅助判断DG实时同步： select current_scn from v$database; |
3.6、数据库同步异常故障排除
主库查询对应dest_id的error信息： select error from v$archive_dest
where dest_id = 2; |
四、 数据同步验证
4.1、主库解锁scott用户
#在primary端创建解锁scott用户并创建测试表，插入10000行数据 #primary 端操作如下内容 SQL> set line 200 SQL> select username,default_tablespace,account_status
from dba_users where username='SCOTT'; USERNAME DEFAULT_TABLESPACE ACCOUNT_STATUS -------------
--------------------------- -------------------------------- SCOTT USERS EXPIRED &
LOCKED SQL> alter user scott account
unlock; User altered. SQL> conn scott/tiger; ERROR: ORA-28001: the password has expired Changing password for scott New password: Retype new password: Password changed Connected. SQL> show user USER is "SCOTT" SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------
------- ---------- BONUS TABLE DEPT TABLE EMP TABLE SALGRADE TABLE |
4.2、主库添加数据，验证数据是否能同步
#在primary端创建解锁scott用户并创建测试表，插入10000行数据 #primary 端操作如下内容 SQL> create table test001 (id
number(10),name varchar2(20)); Table created. SQL> begin for i in 1..10000 loop insert into test001 values
(1,'ww'); end loop; end; / PL/SQL procedure successfully
completed. SQL> commit; Commit complete. |
4.3、备库验证数据是否能同步
#standby端查询scott用户是否解锁，以及test001表是否创建并且插入了10000行数据： SQL> conn scott/tiger; Connected. SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------
------- ---------- BONUS TABLE DEPT TABLE EMP TABLE SALGRADE TABLE TEST001 TABLE SQL> select count(*) from
test001; COUNT(*) ---------- 10000 |
