0.Summary
1. 配置DG专用网络
. 1.1 Primary添加网络和监听
. 1.2 Standby添加网络和监听
2. 主库准备
3. 备库配置tnsnames.ora和监听
4. 主库创建pfile
5. 修改备库pfile
6. 主库修改参数
7. 主库添加standby日志
8. 同步数据
9. 注册OCR并打开备库
10. 备库应用日志
11. 检查角色和保护等级
12. 处理遗留问题
1. 配置DG专用网络
1.1 Primary添加网络和监听
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
将数据库实例注册到新建的监听地址中
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添加网络和监听
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. 主库准备
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和监听
$ 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)
)
)
重启监听
$ srvctl stop listener -l listener_dg
$ srvctl start listener -l listener_dg
3. 备库配置tnsnames.ora和监听
$ 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)
)
)
重启监听
$ srvctl stop listener -l listener_dg
$ srvctl start listener -l listener_dg
备库创建必要的目录
cd $ORACLE_BASE
mkdir -p admin/oradb/{adump,dpdump,pfile,hdump}
主库将pwd文件复制到备库
cd $ORACLE_HOME/dbs
scp orapworadb1 oradg1-dg-vip:`pwd`
cd $ORACLE_HOME/dbs
scp orapworadb2 oradg2-dg-vip:`pwd`
4. 主库创建pfile
create pfile='/home/oracle/init_p.ora' from spfile;
将pfile复制到备库
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的问题。即使路径一致的情况，如果不设置这个参数，在rman duplicate也会发生类似告警，不过不影响应用启动。
备库创建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状态
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. 主库修改参数
主要修改db_unique_name需要重启，如果不修改主库的这个参数，可以不用重启。ASM情况下最好不要修改，默认会减到OMF指定DG下的db_unique_name路径下，如果修改了那么convert参数需要注意设置全至少两个转换路径。
下面这个例子实际上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. 同步数据
主库做全备，并将备份集传到备库
cd /home/oracle/backup
scp * oradg1-dg-vip:`pwd`
恢复数据库
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;
最后出现的报错可以忽略，因为少的是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
备库数据文件头
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，默认指定了路径，如果是恢复到不同路径，可以使用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. 注册OCR并打开备库
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. 备库应用日志
SYS@oradb1>alter database recover managed standby database using current logfile disconnect from session;
11. 检查角色和保护等级
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，停日志应用
create pfile='/home/oracle/init_s.ora' from spfile;
alter database recover managed standby database cancel;
停库
srvctl stop database -d oradb
重新创建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'
启用日志应用
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
