整理一份DG的搭建流程,参考了一些教程及文档,环境是Oracle 11gR2 1+1。DG计划整理三篇:搭建、概念、维护。
主库 Primary | 备库 Standby | |
---|---|---|
操作系统 | CentOS Linux release 7.7.1908 (Core) | CentOS Linux release 7.7.1908 (Core) |
数据库版本 | 11.2.0.4 | 11.2.0.4 |
IP | 192.168.10.131 | 192.168.10.151 |
db_name | pxk2 | pxk2 |
instance_name | pxk2 | sbdb |
db_unique_name | pxk2 | sbdb |
tnsname | tnspxk2 | tnssbdb |
服务名 | pxk2 | pxk2 |
提前准备 | 安装数据库软件、DBCA建库、监听 | 安装数据库软件 |
说明:
--查看
select log_mode,force_logging from v$Database;
--启用
alter database force logging;
--查看
archive log list;
select log_mode from v$database;
--启用
shutdown immediate
startup mount
alter database archivelog;
alter database open;
监听:
--监听文件默认路径$ORACLE_HOME/network/admin/listener.ora
--配置静态监听,增加SID_LIST_LISTENER部分,然后lsnrctl reload
--LISTENER是默认监听名称,静态监听配置SID_LIST_LISTENER与其对应
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.131)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = pxk2) --GLOBAL_DBNAME对应监听里的服务名
(SID_NAME = pxk2)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
)
)
配置完成: 状态为UNKONWN代表静态监听
TNS:
--TNS文件默认路径$ORACLE_HOME/network/admin/tnsnames.ora
--方便主备切换服务名设置相同,配置如下:
tnspxk2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.131)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pxk2)
)
)
tnssbdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.151)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pxk2)
)
)
--1.修改归档文件名后缀为.arc(可选)
alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
--2.检查db_unique_name
show parameter db_unique_name
alter system set db_unique_name='pxk2' scope=spfile;
--3.修改log_archive_config参数,括号中为主备库的db_unique_name
alter system set log_archive_config='DG_CONFIG=(pxk2,sbdb)' scope=both;
--4.配置log_archive_dest_1,主库归档位置
alter system set log_archive_dest_1='LOCATION=/oradata/archivelog/pxk2
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pxk2' scope=both;
--5.配置log_archive_dest_2,备库归档参数,采用LGWR异步传输方式
alter system set log_archive_dest_2='SERVICE=tnssbdb LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sbdb' scope=both;
--6.修改归档日志进程的最大数量(可选)
alter system set log_archive_max_processes=30 scope=both;
show parameter log_archive_max_processes;
--7.检查数据库口令文件的使用模式
show parameter remote_login_passwordfile
alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile;
--8.修改/etc/hosts文件,追加主备库IP与主机名对应关系,备库同样配置
--如果 tns文件里的HOST直接配置的是IP则,可以不用维护hosts文件
echo "192.168.10.131 pxk" >> /etc/hosts
echo "192.168.10.151 pxk2sd >> /etc/hosts
--9.修改db_file_name_convert参数,主备库数据文件名称跟路径对应对应关系
--主库跟备库存放文件的路径不一定一样,如果一样的话可以不指定,但是不一样就需要指定,一般来说主库跟备库的实例名是不一样的,所以肯定是需要设置该参数
--前面路径是备库数据文件路径,后面是主库路径
alter system set db_file_name_convert='/oracle/app/oracle/oradata/sbdb','/oradata/datafile/pxk2/'
scope=spfile;
--10.修改log_file_name_convert参数,主备库日志文件名称跟路径对应对应关系
alter system set log_file_name_convert='/oracle/app/oracle/oradata/sbdb','/oradata/datafile/pxk2/'
scope=spfile;
--11.设置standby_file_management为auto
--设置文件管理模式,此项设置为自动,不然在主库创建数据文件后,备库不会自动创建
--有教程要求先保持manual,增加完standby redolog后再改为auto
alter system set standby_file_management=auto;
--12.修改fal_client及fal_server
alter system set fal_client='tnspxk2' scope=both; --本地tns
alter system set fal_server='tnssbdb' scope=both; --备库tns
部分参数修改没有添加说明,放到概念篇
--方式1:使用 orapwd 设置相同密码
orapwd file=orapwsbdb password=
--方式2:scp主库密码文件到备库$ORACLE_HOME/dbs目录下,并修改密码文件名为orapwsbdb
在dbs目录下创建initsbdb.ora并参考主库添加参数
db_name='pxk2'
db_unique_name='sbdb'
pga_aggregate_target=191889408 --设置跟主库一样
sga_target=575668224 --设置跟主库一样
audit_file_dest='/u01/app/oracle/admin/sbdb/adump' --创建这个路径
compatible='11.2.0.4.0' --兼容性参数 主备库要保持一致
log_archive_format='%t_%s_%r.arc'
control_files='/u01/app/oracle/oradata/sbdb/control01.ctl','/u01/app/oracle/oradata/sbdb/control02.ctl' --注意修改文件路径中的实例名
log_archive_config='DG_CONFIG=(pxk2,sbdb)'
log_archive_dest_1='LOCATION=/u01/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sbdb'
log_archive_dest_2='SERVICE=tnspxk2 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pxk2'
db_file_name_convert='/oradata/datafile/pxk2/','/oracle/app/oracle/oradata/sbdb' --前面是对方的,后面是本地的
log_file_name_convert='/oradata/datafile/pxk2/','/oracle/app/oracle/oradata/sbdb'
fal_client='tnssbdb' --该参数与主库设置相反
fal_server='tnspxk2' --该参数与主库设置相反
standby_file_management='AUTO'
用刚才编辑的pfile 创建spfile
startup nomount pfile='/oracle/app/oracle/product/11.2.0/db_1/dbs/initsbdb.ora';
create spfile from pfile;
--关闭数据库正常启动到nomount状态
--查看是否使用spfile启动 show parameter spfile
监听:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.151)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = pxk2)
(SID_NAME = sbdb)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db_1)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
DIAG_ADR_ENABLED_LISTENER = OFF
TNS:
tnspxk2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.131)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pxk2)
)
)
tnssbdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.151)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pxk2)
)
)
主库和备库分别测试: tnsping tnsprod tnsping tnssbdb
sqlplus sys/oracle@tnspxk2 as sysdba sqlplus sys/oracle@tnssbdb as sysdba
注意: 如果出现TNS-12543错误,关闭防火墙再试 如果tnsping出现 ORA-12541 错误,检查一下hosts文件里面是否配置正常,备库也要配置好
备库执行
--连接RMAN并连接辅助(auxiliary )实例,备库目前是nomount状态
rman target sys/oracle@tnspxk2 auxiliary sys/oracle@tnssbdb
--执行复制
--nofilenamecheck不进行文件名检查(如果复制数据库时,备库的路径和原库一致,就需要加 nofilenamecheck,否则会报错。)
duplicate target database for standby from active database nofilenamecheck;
注意:
select * from v$log;
主库添加:
alter database add standby logfile group 5 ('/oradata/datafile/pxk2/stredo05.log') size 200M;
alter database add standby logfile group 6 ('/oradata/datafile/pxk2/stredo06.log') size 200M;
alter database add standby logfile group 7 ('/oradata/datafile/pxk2/stredo07.log') size 200M;
alter database add standby logfile group 8 ('/oradata/datafile/pxk2/stredo08.log') size 200M;
备库添加:
alter database add standby logfile group 5 ('/oracle/app/oracle/oradata/sbdb/stredo05.log') size 200M;
alter database add standby logfile group 6 ('/oracle/app/oracle/oradata/sbdb/stredo06.log') size 200M;
alter database add standby logfile group 7 ('/oracle/app/oracle/oradata/sbdb/stredo07.log') size 200M;
alter database add standby logfile group 8 ('/oracle/app/oracle/oradata/sbdb/stredo08.log') size 200M;
查看standby日志:
select * from v$standby_log;
打开备库:
alter database open;
--1.开启实时同步(启动MRP进程),备库操作
alter database recover managed standby database using current logfile disconnect from session;
alter database recover managed standby database using current logfile disconnect; --简写
日志记录:
--2.开启同步,日志切换才会同步
alter database recover managed standby database disconnect from session;
日志记录:
--3.停止同步(关闭MRP进程)
alter database recover managed standby database cancel;
日志记录:
select open_mode,database_role,protection_mode,protection_level from v$database;
备库显示:
open_mode:备库标识为read only with apply ,表示应用了日志,如果关闭同步则这里只有read only database_role:标识为主库 还是备库,此时为物理备库 protection_mode和protection_level 此时标识都是最大性能模式
本篇整理了Oracle 11gR2环境下DG 1+1搭建过程,日志传输为LGWR ASYNC异步传输,开启real time apply