说明:ORACLE DATAGUARD 简称DG,DG原理可理解为自动化介质恢复。
环境信息:
OS VERSION:Red Hat Enterprise Linux Server release 6.6
ORACLE VERSION:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit
PRIMARY:
- IP:192.168.37.8
- db_unique_name=orcl
- service_name=orcl
STANDBY
- IP:192.168.37.22
- db_unique_name=orcls
- service_name=orcls
DG搭建的主要分三个步骤
- 搭建前准备工作
- 网络配置
- 参数配置
- 数据初始化
准备工作
- 开归档
注意:此操作必须在数据mount阶段执行,如果数据是非归档模式的话,需要停机一小会儿,执行以下操作。如果是归档模式忽略此步骤。
SQL> alter system set log_archive_dest_1='location=/home/oracle/arch' scope=spfile;
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archvielog;
SQL> alter database open;
- 开启强记日志
SQL> alter database force logging;
- 创建standby redo组
–查看redo数据文件路径
SQL> select member from v$logfile;
MEMBER
----------------------------------------
/oradata/orcl/redo01.log
/oradata/orcl/redo02.log
/oradata/orcl/redo03.log
6 rows selected.
添加standby redo组
SQL> alter database add standby logfile group 4 /oradata/orcl/redo04.log size 50m;
SQL> alter database add standby logfile group 5 /oradata/orcl/redo05.log size 50m;
SQL> alter database add standby logfile group 6 /oradata/orcl/redo06.log size 50m;
网络配置(tnsnames.ora)
- 主库tnsnames.ora
orcls =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.37.22)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcls)
)
)
- 备库 tnsnames.ora
orcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.37.8)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
网络配置完后,tnsping 测试一下,看网络是否连通。
注意:这里有个小窍门,如果这里搞不清楚哪个该写哪里。将两个内容同事写入两个库的tnsname.ora,以免混淆。
参数配置(主库操作)
注意:db_unique_name 参数是静态参数,如果要修改需要重启实例,并且修改后会影响当前业务,修改后监听的service会变。不建议修改,按照当前的参数即可。
SQL> alter system set DB_UNIQUE_NAME=orcl scope=spfile;
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcls)' ;
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/home/oracle/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl';
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=orcls LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcls';
SQL> alter system set FAL_SERVER=orcls;
SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO;
主库配置完后生成pfile文件
SQL> create pfile='/home/oracle/initorcl.ora' from spfile;
修改initorcl.ora的内容
修改的内容其实际上是将主备库参数的service别名进行对调,备库db_unique_name 要与tnsname.ora的service_name一直,否则网络不通
*.db_unique_name='orcls'
*.log_archive_config='DG_CONFIG=(orcls,orcl)'
*.log_archive_dest_1='LOCATION=/home/oracle/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcls'
*.log_archive_dest_2='SERVICE=orcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
*.fal_server='ORCL'
*.standby_file_management='AUTO'
初始化数据
备库操作
创建与主库相同的数据目录(包括归档目录 ,dump目录…如果没有的话)
关闭备库数据库
删掉备库的所有数据文件
rm -rf /oradata/orcl/*
主库操作
开启主库备份模式
SQL> alter databae begin backup;
创建standby controlfille
SQL> alter databases create standby controlfile as '/home/oracle/control.ctl';
scp /oradata/orcl/* oracle@192.168.37.22:/oradata/orcl/
scp /home/oracle/control.ctl oracle@192.168.37.22:/home/oracle/
scp /u01/app/oracle/product/11.2/db_1/dbs/orapworc oracle@192.168.37.22:/u01/app/oracle/product/11.2/db_1/dbs/
关闭主库备份模式
SQL> alter database end backup;
备库操作
将主库生产的standby controlfile文件复制成备库的controlfile,具体复制的份数必须与参数文件记录的位置一致。
cp /home/oracle/control.ctl /oradata/orcl/control01.ctl
cp /home/oracle/control.ctl /oradata/orcl/control02.ctl
利用主库传过来的参数文件(pfile)开启备库到mount阶段
这里建议生成spfile,再用spfile启动数据库到mount,以免切换时出错。
SQL> startup mount pfile='/home/oracle/initorcl.ora';
SQL> create spfile from pfile='/home/oracle/initorcl.ora';
SQL> shutdown immediate
SQL> startup mount
开启备库日志应用模式
SQL> alter database recover managed standby database disconnect from session;
查看同步情况
SQL> select to_char(SYSDATE,'yyyymmdd hh24:mi:ss') CTIME,NAME,VALUE from V$DATAGUARD_STATS WHERE NAME LIKE '%lag';
最后:查看备库的alert日志,查看日志应用情况,以及否有报错。如果日志应用不成功,根据报错逐步处理。
日志中出现一下Media Recovery Waiting for …字样表示日志应用成功
Wed Oct 23 10:40:46 2019
Media Recovery Log /home/oracle/arch/1_185_1022349280.dbf
Media Recovery Waiting for thread 1 sequence 186 (in transit)