Oracle ADG 
備庫新增備庫

2
種情況：

1、
主備資料目錄一樣，無需重啟，線上新增備庫

2、
主備資料目錄設定不一樣，需要重啟讓引數生效

一、
主備資料目錄一樣線上新增備庫

	SID	db_name	db_unique_name	用的通道
主庫	crmpos	crmpos	crmpos
備庫 1	CRMPOS01	crmpos	CRMPOS01	Dest_2
備庫 2	CRMPOS0 2	crmpos	CRMPOS0 2	Dest_3
新新增的備庫 3	CRMPOS102	crmpos	CRMPOS102	Dest_4

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;

Oracle ADG 備庫新增備庫

相關文章