将数据库实例注册到新建的监听地址中
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 YESBest 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 incompleteConverting 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
