Oracle ADG
备库添加备库
2
种情况：
1、
主备数据目录一样，无需重启，在线添加备库
2、
主备数据目录设置不一样，需要重启让参数生效
一、
主备数据目录一样在线添加备库
1、
主库备份和创建
standby
控制文件
用常规备份脚本就行，归档可备份可不备份
创建备库控制文件
alter database create standby controlfile as '/home/oracle/xie/208ctl.ctl';
密码文件
拷贝到备库的
dbs
目录下面
把控制文件和备份文件以及需要的归档拷贝到备库
2、
配置监听
主备库，
CRMPOS102
为新添加的备库
3
的监听：
3、
备库开启归档，配置参数文件
主要注意添加这几个参数，用
pfile
更改方便些：
*.db_name='crmpos'
*.db_unique_name='crmpos102'
*.diagnostic_dest='/u01/app/oracle'
*.log_archive_dest_1='location=/u01/app/oracle/arch/crmpos'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_config='dg_config=(crmpos,crmpos01,crmpos02,crmpos102)'
*.log_archive_dest_2='service=crmpos lgwr async NOAFFIRM valid_for=(online_logfiles,primary_role) REOPEN=60 COMPRESSION=ENABLE db_unique_name=crmpos'
*.log_archive_dest_state_2='enable
*.fal_client='crmpos102'
*.fal_server=crmpos
startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initcrmpos.ora';
create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initcrmpos.ora';
shutdown immediate;
startup nomount
4
、恢复
standby
控制文件
RMAN> restore standby controlfile from '/xie/crmpos/175ctl.ctl';
5
、恢复库
SQL> sql 'alter database mount';
RMAN> catalog start with '/xie/crmpos/2022021602';
crosscheck backup;
delete noprompt expired backup;
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
restore database;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
}
6、
添加
srl
alter system set standby_file_management='manual';
alter database add standby logfile group 12 ('/u01/app/oracle/oradata/crmpos/stdred02.log') size 1024M;
alter database add standby logfile group 13 ('/u01/app/oracle/oradata/crmpos/stdred03.log') size 1024M;
alter database add standby logfile group 14 ('/u01/app/oracle/oradata/crmpos/stdred04.log') size 1024M;
alter database add standby logfile group 15 ('/u01/app/oracle/oradata/crmpos/stdred05.log') size 1024M;
alter system set standby_file_management='auto';
7
、主库更改参数
(
注意：log_archive_config
写的是
db_unique_name)
alter system set log_archive_config='dg_config=(crmpos,crmpos01,crmpos02,
crmpos102)';
alter system set log_archive_dest_4='service=crmpos102 lgwr async NOAFFIRM valid_for=(online_logfiles,primary_role) REOPEN=60 COMPRESSION=ENABLE db_unique_name=crmpos102' scope=both;
(
注意：'service
写的是
tns
里的名字
)
alter system set log_archive_dest_4='service=crmpos102 lgwr async NOAFFIRM valid_for=(online_logfiles,primary_role) REOPEN=60 COMPRESSION=ENABLE db_unique_name=crmpos102' scope=both;
8
、备库
open read only
，并开启实时同步
alter database open read only;
alter database recover managed standby database using current logfile disconnect from session;
二、
主备数据目录不一样添加备库
(
需重启
)
uenqs
为新添加的备库
前面步骤一样：备份、创建
standby
控制文件、传输密码文件、配置监听、添加
srl
不一样的地方：
1
、恢复库的时候更改数据目录
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
SET NEWNAME FOR DATAFILE 1 to '/u01/app/oracle/oradata/uenqs/system01.dbf';
SET NEWNAME FOR DATAFILE 2 to '/u01/app/oracle/oradata/uenqs/sysaux01.dbf';
SET NEWNAME FOR DATAFILE 3 to '/u01/app/oracle/oradata/uenqs/undotbs01.dbf';
SET NEWNAME FOR DATAFILE 4 to '/u01/app/oracle/oradata/uenqs/users01.dbf';
SET NEWNAME FOR DATAFILE 5 to '/u01/app/oracle/oradata/uenqs/uenpay_tbs01.dbf';
SET NEWNAME FOR DATAFILE 6 to '/u01/app/oracle/oradata/uenqs/uenagent_tbs01.dbf';
SET NEWNAME FOR DATAFILE 7 to '/u01/app/oracle/oradata/uenqs/ams_tbs01.dbf';
SET NEWNAME FOR DATAFILE 8 to '/u01/app/oracle/oradata/uenqs/ams_tbs02.dbf';
SET NEWNAME FOR DATAFILE 9 to '/u01/app/oracle/oradata/uenqs/uenpay_tbs02.dbf';
SET NEWNAME FOR DATAFILE 10 to '/u01/app/oracle/oradata/uenqs/uenpay_tbs03.dbf';
SET NEWNAME FOR DATAFILE 11 to '/u01/app/oracle/oradata/uenqs/uenpay_tbs04.dbf';
SET NEWNAME FOR DATAFILE 12 to '/u01/app/oracle/oradata/uenqs/uenagent_tbs02.dbf';
SET NEWNAME FOR DATAFILE 13 to '/u01/app/oracle/oradata/uenqs/uenshks_tbs01.dbf';
SET NEWNAME FOR DATAFILE 14 to '/u01/app/oracle/oradata/uenqs/sysaux02.dbf';
SET NEWNAME FOR DATAFILE 15 to '/u01/app/oracle/oradata/uenqs/uenagent_tbs03.dbf';
SET NEWNAME FOR DATAFILE 16 to '/u01/app/oracle/oradata/uenqs/uenpay_tbs05.dbf';
SET NEWNAME FOR DATAFILE 17 to '/u01/app/oracle/oradata/uenqs/uenpay_tbs06.dbf';
SET NEWNAME FOR DATAFILE 18 to '/u01/app/oracle/oradata/uenqs/uenagent_tbs04.dbf';
SET NEWNAME FOR DATAFILE 19 to '/u01/app/oracle/oradata/uenqs/uenpay_tbs07.dbf';
SET NEWNAME FOR DATAFILE 20 to '/u01/app/oracle/oradata/uenqs/uenpay_tbs08.dbf';
SET NEWNAME FOR DATAFILE 21 to '/u01/app/oracle/oradata/uenqs/uenagent_tbs05.dbf';
SET NEWNAME FOR DATAFILE 22 to '/u01/app/oracle/oradata/uenqs/uenagent_tbs06.dbf';
SET NEWNAME FOR DATAFILE 23 to '/u01/app/oracle/oradata/uenqs/UENJSGL_tbs01.dbf';
restore database;
switch datafile all;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
}
2
、更改参数文件
主库的参数：
(UENPAY
为之前另外一套库的，可以不用配置，改convert
参数需要重启
)
alter system set log_archive_config='dg_config=(UENPAY,UENPAY01,UENPAY02,UENQS)';
alter system set log_archive_config='dg_config=(UENPAY,UENPAY01,UENPAY02,UENQS)';
alter system set log_archive_dest_4='service=UENQS lgwr async NOAFFIRM valid_for=(online_logfiles,primary_role) REOPEN=60 COMPRESSION=ENABLE db_unique_name=UENQS' scope=both;
alter system set db_file_name_convert='/u01/app/oracle/oradata/uenqs/','/data/uenpay/data/' scope=spfile;
alter system set log_file_name_convert='/u01/app/oracle/oradata/uenqs/','/data/uenpay/data/','/u01/app/oracle/arch/uenqs/','/data/archivelog/' scope=spfile;
alter system set db_file_name_convert='/data/uenpay/data/','/u01/app/oracle/oradata/uenqs/' scope=spfile;
alter system set log_file_name_convert='/data/uenpay/data/','/u01/app/oracle/oradata/uenqs/','/data/archivelog/','/u01/app/oracle/arch/uenqs/' scope=spfile;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_4=defer scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_4=ENABLE scope=both;
备库的日志转换参数和主库相反，其他一样
其他的一样，然后开启备库，开启实时同步就行
---------------------------
有报错就看日志，然后查 v$archive_dest;
分析
select dest_name, status, error from v$archive_dest;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31098809/viewspace-2856400/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
每天自动统计表空间的使用情况
请登录后发表评论
登录
全部评论
