0.Summary
1. 配置DG專用網(wǎng)絡(luò)
. 1.1 Primary添加網(wǎng)絡(luò)和監(jiān)聽
. 1.2 Standby添加網(wǎng)絡(luò)和監(jiān)聽
2. 主庫準(zhǔn)備
3. 備庫配置tnsnames.ora和監(jiān)聽
4. 主庫創(chuàng)建pfile
5. 修改備庫pfile
6. 主庫修改參數(shù)
7. 主庫添加standby日志
8. 同步數(shù)據(jù)
9. 注冊O(shè)CR并打開備庫
10. 備庫應(yīng)用日志
11. 檢查角色和保護(hù)等級
12. 處理遺留問題
1. 配置DG專用網(wǎng)絡(luò)
1.1 Primary添加網(wǎng)絡(luò)和監(jiān)聽
cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.1.27 oratest1
192.168.1.28 oratest2
192.168.2.27 oratest1-priv
192.168.2.28 oratest2-priv
192.168.1.29 oratest1-vip
192.168.1.30 oratest2-vip
192.168.1.26 oradb-scan
192.168.4.27 oratest1-dg
192.168.4.28 oratest2-dg
192.168.4.29 oratest1-dg-vip
192.168.4.30 oratest2-dg-vip
192.168.4.53 oradg1-dg-vip
192.168.4.54 oradg2-dg-vip
# srvctl add network -k 2 -S 192.168.4.0/255.255.255.0/eth6 -w static -v
# crsctl stat res -t | grep network
ora.net1.network
ora.net2.network
# crsctl start res ora.net2.network
# srvctl add vip -n oratest1 -A 192.168.4.29/255.255.255.0 -k 2
# srvctl add vip -n oratest2 -A 192.168.4.30/255.255.255.0 -k 2
$ netca
Oracle Net Services Configuration:
Oracle Net Configuration Assistant is launched from Grid Infrastructure home. Network configuration will be clusterwide.
Configuring Listener:LISTENER_DG
oratest1...
oratest2...
Listener configuration complete.
Oracle Net Listener Startup:
Listener started successfully.
Oracle Net Services configuration successful. The exit code is 0
將數(shù)據(jù)庫實例注冊到新建的監(jiān)聽地址中
alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.29)(PORT = 1521))','(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.29)(PORT = 1555))' scope=both sid='oradb1';
alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.30)(PORT = 1521))','(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.30)(PORT = 1555))' scope=both sid='oradb2';
1.2 Standby添加網(wǎng)絡(luò)和監(jiān)聽
cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.1.51 oradg1
192.168.1.52 oradg2
192.168.2.51 oradg1-priv
192.168.2.52 oradg2-priv
192.168.1.53 oradg1-vip
192.168.1.54 oradg2-vip
192.168.1.50 oradg-scan
192.168.4.51 oradg1-dg
192.168.4.52 oradg2-dg
192.168.4.53 oradg1-dg-vip
192.168.4.54 oradg2-dg-vip
192.168.4.29 oratest1-dg-vip
192.168.4.30 oratest2-dg-vip
# srvctl add network -k 2 -S 192.168.4.0/255.255.255.0/eth3 -w static -v
# crsctl stat res -t | grep network
ora.net1.network
ora.net2.network
# crsctl start res ora.net2.network
# srvctl add vip -n oradg1 -A 192.168.4.53/255.255.255.0 -k 2
# srvctl add vip -n oradg2 -A 192.168.4.54/255.255.255.0 -k 2
$ netca
Oracle Net Services Configuration:
Oracle Net Configuration Assistant is launched from Grid Infrastructure home. Network configuration will be clusterwide.
Configuring Listener:LISTENER_DG
oradg1...
oradg2...
Listener configuration complete.
Oracle Net Listener Startup:
Listener started successfully.
Oracle Net Services configuration successful. The exit code is 0
2. 主庫準(zhǔn)備
alter database force logging;
select log_mode,
supplemental_log_data_min,
supplemental_log_data_pk,
supplemental_log_data_ui,
force_logging
from v$database;
LOG_MODE SUPPLEME SUP SUP FOR
------------ -------- --- --- ---
ARCHIVELOG NO NO NO YES
Best Practices for Corruption Detection, Prevention, and Automatic Repair - in a Data Guard Configuration (Doc ID 1302539.1)
alter system set db_lost_write_protect=typical scope=both sid='*';
alter system set db_block_checksum=full scope=both sid='*';
alter system set db_block_checking=medium scope=both sid='*';
配置主庫的tnsnames.ora和監(jiān)聽
$ cat tnsnames.ora
......
ORADB_P =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.29)(PORT = 1555))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.30)(PORT = 1555))
(LOAD_BALANCE = off)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORADB)
)
)
ORADB_S =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.53)(PORT = 1555))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.54)(PORT = 1555))
(LOAD_BALANCE = off)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORADB)
)
)
注意使用靜態(tài)監(jiān)聽
$ cat listener.ora
......
SID_LIST_LISTENER_DG =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = oradb)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db_1)
(SID_NAME = oradb1)
)
)
$ cat listener.ora
......
SID_LIST_LISTENER_DG =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = oradb)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db_1)
(SID_NAME = oradb2)
)
)
重啟監(jiān)聽
$ srvctl stop listener -l listener_dg
$ srvctl start listener -l listener_dg
3. 備庫配置tnsnames.ora和監(jiān)聽
$ cat tnsnames.ora
ORADB_P =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.29)(PORT = 1555))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.30)(PORT = 1555))
(LOAD_BALANCE = off)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORADB)
)
)
ORADB_S =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.53)(PORT = 1555))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.54)(PORT = 1555))
(LOAD_BALANCE = off)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORADB)
)
)
$ cat listener.ora
......
SID_LIST_LISTENER_DG =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = oradb)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db_1)
(SID_NAME = oradb1)
)
)
$ cat listener.ora
......
SID_LIST_LISTENER_DG =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = oradb)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db_1)
(SID_NAME = oradb2)
)
)
重啟監(jiān)聽
$ srvctl stop listener -l listener_dg
$ srvctl start listener -l listener_dg
備庫創(chuàng)建必要的目錄
cd $ORACLE_BASE
mkdir -p admin/oradb/{adump,dpdump,pfile,hdump}
主庫將pwd文件復(fù)制到備庫
cd $ORACLE_HOME/dbs
scp orapworadb1 oradg1-dg-vip:`pwd`
cd $ORACLE_HOME/dbs
scp orapworadb2 oradg2-dg-vip:`pwd`
4. 主庫創(chuàng)建pfile
create pfile='/home/oracle/init_p.ora' from spfile;
將pfile復(fù)制到備庫
cd /home/oracle
scp init_p.ora oradg1-dg-vip:`pwd`/init_s.ora
5. 修改備庫pfile
oradb1.local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.53)(PORT = 1521))','(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.53)(PORT = 1555))'
oradb2.local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.54)(PORT = 1521))','(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.54)(PORT = 1555))'
*.db_unique_name='oradb_s'
*.log_archive_config='dg_config=(oradb_s,oradb)'
*.log_archive_dest_1='LOCATION=+FRA valid_for=(all_logfiles,all_roles) db_unique_name=oradb_s'
*.log_archive_dest_2='service=oradb_p valid_for=(online_logfiles,primary_role) lgwr async db_unique_name=oradb'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management='auto'
*.fal_server='oradb'
*.db_file_name_convert='+DATADG/ORADB_S','+DATADG/ORADB'
*.log_file_name_convert='+DATADG/ORADB_S','+DATADG/ORADB'
*.remote_listener='oradg-scan:1521'
ORA-19527 reported in Standby Database when starting Managed Recovery (Doc ID 352879.1)
LOG_FILE_NAME_CONVERT目錄一致主要為了解決主備角色切換時辕录，online redo clear的問題挑势。即使路徑一致的情況，如果不設(shè)置這個參數(shù)竖瘾，在rman duplicate也會發(fā)生類似告警零蓉，不過不影響應(yīng)用啟動卿嘲。
備庫創(chuàng)建spfile
SYS@+ASM1>alter diskgroup datadg add directory '+DATADG/ORADB_S';
SYS@oradb1>create spfile='+DATADG/oradb_s/spfileoradb.ora' from pfile='/home/oracle/init_s.ora';
cd $ORACLE_HOME/dbs
echo "SPFILE='+DATADG/oradb_s/spfileoradb.ora'" > initoradb1.ora
cd $ORACLE_HOME/dbs
echo "SPFILE='+DATADG/oradb_s/spfileoradb.ora'" > initoradb2.ora
備庫啟動到nomount狀態(tài)
SYS@oradb1>startup nomount;
ORA-01565: error in identifying file '+DATADG/oradb_s/spfileoradb.ora'
ORA-17503: ksfdopn:2 Failed to open file +DATADG/oradb_s/spfileoradb.ora
ORA-15001: diskgroup "DATADG" does not exist or is not mounted
ORA-15040: diskgroup is incomplete
Converting R12 11g To RAC/ASM using rconfig failed with ORA-19504, ORA-17502, and ORA-15001 errors (Doc ID 1941108.1)
$ ls -ltr $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 oracle oinstall 239839854 Oct 4 13:33 /oracle/app/oracle/product/11.2.0/db_1/bin/oracle
$ /oracle/app/11.2.0/grid/bin/setasmgidwrap o=$ORACLE_HOME/bin/oracle
$ ls -ltr $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 oracle asmadmin 239839854 Oct 4 13:33 /oracle/app/oracle/product/11.2.0/db_1/bin/oracle
SYS@oradb1>startup nomount
6. 主庫修改參數(shù)
主要修改db_unique_name需要重啟，如果不修改主庫的這個參數(shù)淀弹，可以不用重啟丹壕。ASM情況下最好不要修改，默認(rèn)會減到OMF指定DG下的db_unique_name路徑下薇溃，如果修改了那么convert參數(shù)需要注意設(shè)置全至少兩個轉(zhuǎn)換路徑菌赖。
下面這個例子實際上db_unique_name沒變
alter system set db_unique_name='oradb' scope=spfile sid='*';
alter system set log_archive_config='dg_config=(oradb,oradb_s)' scope=spfile sid='*';
alter system set log_archive_dest_1='location=+FRA valid_for=(all_logfiles,all_roles) db_unique_name=oradb' scope=spfile sid='*';
alter system set log_archive_dest_2='service=oradb_s valid_for=(online_logfiles,primary_role) lgwr async db_unique_name=oradb_s' scope=spfile sid='*';
alter system set log_archive_dest_state_1=enable scope=spfile sid='*';
alter system set log_archive_dest_state_2=enable scope=spfile sid='*';
alter system set standby_file_management='auto' scope=spfile sid='*';
alter system set fal_server='oradb_s' scope=spfile sid='*';
alter system set db_file_name_convert='+DATADG/ORADB_S','+DATADG/ORADB' scope=spfile sid='*';
alter system set log_file_name_convert='+DATADG/ORADB_S','+DATADG/ORADB' scope=spfile sid='*';
重啟主庫
srvctl stop database -d oradb
srvctl start database -d oradb
7. 主庫添加standby日志
alter database add standby logfile thread 1 group 7 '+DATADG' size 100M;
alter database add standby logfile thread 1 group 8 '+DATADG' size 100M;
alter database add standby logfile thread 1 group 9 '+DATADG' size 100M;
alter database add standby logfile thread 1 group 10 '+DATADG' size 100M;
alter database add standby logfile thread 2 group 11 '+DATADG' size 100M;
alter database add standby logfile thread 2 group 12 '+DATADG' size 100M;
alter database add standby logfile thread 2 group 13 '+DATADG' size 100M;
alter database add standby logfile thread 2 group 14 '+DATADG' size 100M;
set lines 200 pages 200
col member for a60
select a.group#,b.thread#,a.member, b.status,b.bytes/1024/1024 MB from v$logfile a, v$log b
where a.group#=b.group#
union all
select a.group#,b.thread#,a.member, b.status,b.bytes/1024/1024 MB from v$logfile a, v$standby_log b
where a.group#=b.group#
order by 2,1,3;
8. 同步數(shù)據(jù)
主庫做全備，并將備份集傳到備庫
cd /home/oracle/backup
scp * oradg1-dg-vip:`pwd`
恢復(fù)數(shù)據(jù)庫
RMAN> restore standby controlfile from '/home/oracle/backup/oradb1_ctl_file_10sgbonq_1_1_20171006';
RMAN> sql 'alter database mount standby database';
RMAN> restore database;
RMAN> recover database;
最后出現(xiàn)的報錯可以忽略沐序，因為少的是online redo log琉用。
unable to find archived log
archived log thread=1 sequence=125
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/06/2017 20:39:23
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 125 and starting SCN of 5673763
備庫數(shù)據(jù)文件頭
SYS@oradb1>select file#, status, checkpoint_change#, checkpoint_count from v$datafile_header order by 1;
FILE# STATUS CHECKPOINT_CHANGE# CHECKPOINT_COUNT
---------- ------- ------------------ ----------------
1 ONLINE 5673763 137
2 ONLINE 5673763 137
3 ONLINE 5673763 137
4 ONLINE 5673763 136
5 ONLINE 5673763 136
6 ONLINE 5673763 105
主庫日志
SYS@oradb1>select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------ ------------ ------------
1 1 125 104857600 512 1 NO CURRENT 5673763 06-OCT-17 2.8147E+14
2 1 123 104857600 512 1 YES INACTIVE 5671679 06-OCT-17 5671744 06-OCT-17
3 1 124 104857600 512 1 YES INACTIVE 5671744 06-OCT-17 5673763 06-OCT-17
4 2 52 104857600 512 1 NO CURRENT 5673778 06-OCT-17 2.8147E+14
5 2 50 104857600 512 1 YES INACTIVE 5671725 06-OCT-17 5671860 06-OCT-17
6 2 51 104857600 512 1 YES INACTIVE 5671860 06-OCT-17 5673778 06-OCT-17
另外這里的例子是ASM，默認(rèn)指定了路徑策幼，如果是恢復(fù)到不同路徑邑时，可以使用set newname，如下：
run{
allocate channel t1 type disk ;
allocate channel t2 type disk ;
set newname for datafile '/oradata/bbed/system01.dbf' to '/oradata/test/system01.dbf';
set newname for datafile '/oradata/bbed/sysaux01.dbf' to '/oradata/test/sysaux01.dbf';
set newname for datafile '/oradata/bbed/undotbs01.dbf' to '/oradata/test/undotbs01.dbf';
set newname for datafile '/oradata/bbed/users01.dbf' to '/oradata/test/users01.dbf';
set newname for datafile '/oradata/bbed/example01.dbf' to '/oradata/test/example01.dbf';
restore database;
switch datafile all;
release channel t1;
release channel t2;
}
9. 注冊O(shè)CR并打開備庫
srvctl add database -d oradb -o $ORACLE_HOME
srvctl add instance -d oradb -i oradb1 -n oradg1
srvctl add instance -d oradb -i oradb2 -n oradg2
SYS@oradb1>shutdown immediate
$ srvctl start database -d oradb -o 'read only'
10. 備庫應(yīng)用日志
SYS@oradb1>alter database recover managed standby database using current logfile disconnect from session;
11. 檢查角色和保護(hù)等級
SYS@oradb1>select protection_mode, protection_level, database_role role, switchover_status from v$database;
PROTECTION_MODE PROTECTION_LEVEL ROLE SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PRIMARY TO STANDBY
SYS@oradb1>select protection_mode, protection_level, database_role role, switchover_status from v$database;
PROTECTION_MODE PROTECTION_LEVEL ROLE SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED
12. 處理遺留問題
ASMCMD [+datadg] > cd oradb_s
ASMCMD [+datadg/oradb_s] > ls -l
Type Redund Striped Time Sys Name
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y TEMPFILE/
N spfileoradb.ora => +DATADG/DB_UNKNOWN/PARAMETERFILE/SPFILE.256.956694647
生成PFILE特姐，停日志應(yīng)用
create pfile='/home/oracle/init_s.ora' from spfile;
alter database recover managed standby database cancel;
停庫
srvctl stop database -d oradb
重新創(chuàng)建SPFILE
startup nomount pfile='/home/oracle/init_s.ora';
create spfile='+DATADG/oradb_s/spfileoradb.ora' from pfile='/home/oracle/init_s.ora';
shutdown immediate
srvctl start database -d oradb -o 'read only'
啟用日志應(yīng)用
alter database recover managed standby database using current logfile disconnect from session;
驗證
ASMCMD [+datadg/oradb_s] > ls -l
Type Redund Striped Time Sys Name
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y PARAMETERFILE/
Y TEMPFILE/
N spfileoradb.ora => +DATADG/ORADB_S/PARAMETERFILE/spfile.256.956699423
