Oracle Data Guard 环境搭建
关于DG主备切换请跳转https://blog.csdn.net/weixin_42774383/article/details/82682503
本文主要涉及到以下内容
一、安装前环境配置
二、主库配置
三、备库配置
四、主库复制数据库到备库
五、开启备库数据库
六、备库应用日志文件
七、查询主备库数据库状态
八、验证数据同步
一、安装前环境配置
1、一台完整可用装有ORACLE数据库的虚拟机作为主库
2、一台干净没有安装ORACLE数据库的虚拟机作为备库,注意是没有安装ORACLE数据库,但是软件要提前安装好
二、配置主库
提前做好IP对应关系需要在主库与备库都配置
cat /etc/hosts
1、alter database force logging;打开数据库强制归档
2、设置数据库归档
(1)startup nomount;
(2)alter database archivelog;
(3)alter database open;
(4)archive log list;
(5)修改归档路径,路径要提前存在
show parameter archive;
alter system set log_archive_dest_1 =’location=/u01/arch’;
alter system set log_archive_format =%t_%s_%r.arc;
3、主库添加备库日志文件,个数为当前日志数+1
alter database add standby logfile '/u01/app/oracle/oradata/orcl/stdredo01.log' size 200M;
alter database add standby logfile '/u01/app/oracle/oradata/orcl/stdredo02.log' size 200M;
alter database add standby logfile '/u01/app/oracle/oradata/orcl/stdredo03.log' size 200M;
alter database add standby logfile '/u01/app/oracle/oradata/orcl/stdredo04.log' size 200M;
注意文件大小要与原日志文件大小差不多。
4、查询添加的standby logfile
SQL> select group#,thread#,sequence#,archived,status from v$standby_log;
GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
4 0 0 YES UNASSIGNED
5 0 0 YES UNASSIGNED
6 0 0 YES UNASSIGNED
7 0 0 YES UNASSIGNED
SQL> select * from v$logfile;
4 STANDBY /u01/app/oracle/oradata/orcl/s NO 0
tdredo01.log
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- ------------------------------ --- ----------
5 STANDBY /u01/app/oracle/oradata/orcl/s NO 0
tdredo02.log
6 STANDBY /u01/app/oracle/oradata/orcl/s NO 0
tdredo03.log
7 STANDBY /u01/app/oracle/oradata/orcl/s NO 0
tdredo04.log
5、配置静态监听参数
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME =/u01/app/oracle/product/12.2.0/dbhome_1)
(SID_NAME = orcl)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
6、配置网络连接名
ORCL_S =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = wyuu)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
ORCL_P =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = wyu)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
配置好需要验证是否可用,我们这里先验证主库,等后续备库配置完成再验证备库
tnsping orcl_p
8、后续备库需要用到原pfile,所以这里先将pfile拷贝到备库一份
scp $ORACLE_HOME/dbs/initorcl.ora wyuu:`pwd`
然后修改主库的pfile加入以下内容
*.db_name='orcl'
*.db_unique_name='orcl_p'
*.log_archive_config='dg_config=(orcl_p,orcl_s)'
*.log_archive_dest_1='location=/u01/arch valid_for=(all_logfiles,all_roles) db_unique_name=orcl_p'
*.log_archive_dest_2='service=orcl_s valid_for=(online_logfiles,primary_role) lgwr affirm sync db_unique_name=orcl_s'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management='auto'
*.fal_server='orcl_s'
*.log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'
*.db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'
三、备库配置
1、配置备库的静态监听参数,直接将主库的监听文件拷贝过来
scp $ORACLE_HOME/network/admin/listener.ora wyuu:`pwd`
2、配置网络连接名,同样将主库的文件拷贝一份
scp $ORACLE_HOME/network/admin/tnsnames.ora wyuu:`pwd`
验证网络连接名是否可用
需要在主库及从库都使用tnsping来验证
3、将之前拷贝的参数文件加入以下内容
*.db_name='orcl'
*.db_unique_name='orcl_s'
*.log_archive_config='dg_config=(orcl_p,orcl_s)'
*.log_archive_dest_1='location=/u01/arch valid_for=(all_logfiles,all_roles) db_unique_name=orcl_s'
*.log_archive_dest_2='service=orcl_p valid_for=(online_logfiles,primary_role) lgwr affirm sync db_unique_name=orcl_p'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management='auto'
*.fal_server='orcl_p'
*.log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'
*.db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'
4、拷贝密码文件
scp $ORACLE_HOME/dbs/orapworcl wyuu:`pwd`
5、创建启动需要的目录
目录要与pfile中路径相同
mkdir -p $ORACLE_BASE/admin/orcl/adump
6、启动数据库到nomount状态
startup nomount
四、主库复制数据库
rman target sys/[email protected]_p auxiliary sys/[email protected]_s
duplicate target database for standby from active database nofilenamecheck dorecover;
五、开启备库数据库
alter database open;
六、备库应用日志文件
alter database recover managed standby database using current logfile disconnect from session;
alter database recover managed standby database cancel;(取消日志文件应用,不需做)
七、查询主备库数据库状态
1、主库
SQL> select log_mode,open_mode,database_role from v$database;
LOG_MODE OPEN_MODE DATABASE_ROLE
------------ -------------------- ----------------
ARCHIVELOG READ WRITE PRIMARY
2、备库
SQL> select log_mode,open_mode,database_role from v$database;
LOG_MODE OPEN_MODE DATABASE_ROLE
------------ -------------------- ----------------
ARCHIVELOG READ ONLY WITH APPLY PHYSICAL STANDBY