Step By Step Configuring Oracle 10gR2 (10.2.0.5) 3-Nodes RAC to Single Dataguard
来源：互联网 发布：身份证脱壳软件打不开 编辑：程序博客网 时间：2024/05/21 18:45
Operating System:Oracle Linux 5.7 x86_64
Oracle version:10.2.0.5
RAC Primary database
192.168.1.51 racnode1
192.168.1.151 racnode1-vip
172.168.1.51 racnode1-priv
192.168.1.52 racnode2
192.168.1.152 racnode2-vip
172.168.1.52 racnode2-priv
192.168.1.53 racnode3
192.168.1.153 racnode3-vip
172.168.1.53 racnode3-priv
Single Standby database
192.168.1.59 standby
主库是一台3nodes的10g RAC,备库准备使用单实例数据库存储使用ASM
需要在standby机器上安装oracle database software并升级至10.2.0.5,安装过程略
RAC 信息
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';Session altered.SQL> select INSTANCE_NAME,HOST_NAME,VERSION,STARTUP_TIME,STATUS,ACTIVE_STATE,INSTANCE_ROLE,DATABASE_STATUS from gv$INSTANCE;INSTANCE_NAME HOST_NAME VERSION STARTUP_TIME STATUSACTIVE_ST INSTANCE_ROLE DATABASE_STATUS---------------- ----------- ----------------- ------------------- ------------ --------- ------------------ -----------------racdb2 racnode2 10.2.0.5.0 2012-12-25 16:08:08 OPEN NORMAL PRIMARY_INSTANCE ACTIVEracdb1 racnode1 10.2.0.5.0 2012-12-25 16:08:07 OPEN NORMAL PRIMARY_INSTANCE ACTIVEracdb3 racnode3 10.2.0.5.0 2012-12-25 16:08:08 OPEN NORMAL PRIMARY_INSTANCE ACTIVESQL> select dbid,name,created,log_mode,db_unique_name from gv$database; DBID NAME CREATED LOG_MODE DB_UNIQUE_NAME---------- --------- ------------------- ------------ ------------------------------ 800157471 RACDB 2012-12-20 15:58:23 ARCHIVELOG racdb 800157471 RACDB 2012-12-20 15:58:23 ARCHIVELOG racdb 800157471 RACDB 2012-12-20 15:58:23 ARCHIVELOG racdbSQL> select name from v$datafile;NAME----------------------------------------------------------------------------------------------------------------------------------------------------------------+DATADG/racdb/datafile/system.269.802972261+DATADG/racdb/datafile/undotbs1.256.802972267+DATADG/racdb/datafile/sysaux.265.802972263+DATADG/racdb/datafile/users.257.802972267+DATADG/racdb/datafile/example.258.802972265+DATADG/racdb/datafile/undotbs2.259.802972265+DATADG/racdb/datafile/undotbs3.264.8029722697 rows selected.SQL> select name from v$controlfile;NAME----------------------------------------------------------------------------------------------------------------------------------------------------------------+DATADG/racdb/controlfile/current.260.802540703+FLASHDG/racdb/controlfile/current.256.802540705SQL> select member from v$logfile;MEMBER----------------------------------------------------------------------------------------------------------------------------------------------------------------+DATADG/racdb/onlinelog/group_2.262.802540719+FLASHDG/racdb/onlinelog/group_2.258.802540725+DATADG/racdb/onlinelog/group_1.261.802540709+FLASHDG/racdb/onlinelog/group_1.257.802540715+DATADG/racdb/onlinelog/group_3.266.802541097+FLASHDG/racdb/onlinelog/group_3.259.802541105+DATADG/racdb/onlinelog/group_4.267.802541113+FLASHDG/racdb/onlinelog/group_4.260.802541123+DATADG/racdb/onlinelog/group_5.270.802888327+FLASHDG/racdb/onlinelog/group_5.279.802888333+DATADG/racdb/onlinelog/group_6.271.802888337MEMBER----------------------------------------------------------------------------------------------------------------------------------------------------------------+FLASHDG/racdb/onlinelog/group_6.280.80288834312 rows selected.SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- 1 1 23 52428800 2 NO CURRENT 1026686 2012-12-26 11:16:41 2 1 22 52428800 2 YES INACTIVE 1026647 2012-12-26 11:16:24 3 2 13 52428800 2 YES INACTIVE 1026657 2012-12-26 11:16:26 4 2 14 52428800 2 NO CURRENT 1026688 2012-12-26 11:16:42 5 3 7 52428800 2 NO CURRENT 1026664 2012-12-26 11:16:39 6 3 6 52428800 2 YES INACTIVE 1026655 2012-12-26 11:16:266 rows selected.
[oracle@racnode1 ~]$ crs_stat -t Name Type Target State Host ------------------------------------------------------------ora.racdb.db application ONLINE ONLINE racnode2 ora....b1.inst application ONLINE ONLINE racnode1 ora....b2.inst application ONLINE ONLINE racnode2 ora....b3.inst application ONLINE ONLINE racnode3 ora.....zwc.cs application ONLINE ONLINE racnode1 ora....db1.srv application ONLINE ONLINE racnode1 ora....SM1.asm application ONLINE ONLINE racnode1 ora....E1.lsnr application ONLINE ONLINE racnode1 ora....de1.gsd application ONLINE ONLINE racnode1 ora....de1.ons application ONLINE ONLINE racnode1 ora....de1.vip application ONLINE ONLINE racnode1 ora....SM2.asm application ONLINE ONLINE racnode2 ora....E2.lsnr application ONLINE ONLINE racnode2 ora....de2.gsd application ONLINE ONLINE racnode2 ora....de2.ons application ONLINE ONLINE racnode2 ora....de2.vip application ONLINE ONLINE racnode2 ora....SM3.asm application ONLINE ONLINE racnode3 ora....E3.lsnr application ONLINE ONLINE racnode3 ora....de3.gsd application ONLINE ONLINE racnode3 ora....de3.ons application ONLINE ONLINE racnode3 ora....de3.vip application ONLINE ONLINE racnode3 [oracle@racnode1 ~]$ crs_stat -lsName Owner Primary PrivGrp Permission -----------------------------------------------------------------ora.racdb.db oracle oinstall rwxrwxr--ora....b1.inst oracle oinstall rwxrwxr--ora....b2.inst oracle oinstall rwxrwxr--ora....b3.inst oracle oinstall rwxrwxr--ora.....zwc.cs oracle oinstall rwxrwxr--ora....db1.srv oracle oinstall rwxrwxr--ora....SM1.asm oracle oinstall rwxrwxr--ora....E1.lsnr oracle oinstall rwxrwxr--ora....de1.gsd oracle oinstall rwxr-xr--ora....de1.ons oracle oinstall rwxr-xr--ora....de1.vip root oinstall rwxr-xr--ora....SM2.asm oracle oinstall rwxrwxr--ora....E2.lsnr oracle oinstall rwxrwxr--ora....de2.gsd oracle oinstall rwxr-xr--ora....de2.ons oracle oinstall rwxr-xr--ora....de2.vip root oinstall rwxr-xr--ora....SM3.asm oracle oinstall rwxrwxr--ora....E3.lsnr oracle oinstall rwxrwxr--ora....de3.gsd oracle oinstall rwxr-xr--ora....de3.ons oracle oinstall rwxr-xr--ora....de3.vip root oinstall rwxr-xr--
[oracle@racnode1 admin]$ cat tnsnames.ora# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora# Generated by Oracle configuration tools.RACDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1-vip)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = racdb) (INSTANCE_NAME = racdb1) ) )RACDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1-vip)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = racnode2-vip)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = racnode3-vip)(PORT = 1521)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = racdb) ) )LISTENERS_RACDB = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1-vip)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = racnode2-vip)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = racnode3-vip)(PORT = 1521)) )ZWC = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1-vip)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = racnode2-vip)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = racnode3-vip)(PORT = 1521)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = zwc) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5) ) ) )RACDB3 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = racnode3-vip)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = racdb) (INSTANCE_NAME = racdb3) ) )RACDB2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = racnode2-vip)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = racdb) (INSTANCE_NAME = racdb2) ) )
在standby 服务器创建用户用户组
groupadd -g 501 oinstall
groupadd -g 502 dba
groupadd -g 503 oper
useradd -m -u 501 -g oinstall -G dba,oper -d /home/oracle -s /bin/bash -c "Oracle Software Owner" oracle
mkdir -p /u01/app/oracle
chown -R oracle:oinstall /u01/app
chmod -R 775 /u01/app
配置oracle用户环境变量
alias ls="ls -FA"export JAVA_HOME=/usr/local/javaexport ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1export ORACLE_PATH=$ORACLE_BASE/common/oracle/sql:.:$ORACLE_HOME/rdbms/adminexport CV_JDKHOME=/usr/local/java export ORACLE_SID=standbyexport PATH=.:${JAVA_HOME}/bin:${PATH}:$HOME/bin:$ORACLE_HOME/bin:$ORA_CRS_HOME/binexport PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/binexport PATH=${PATH}:$ORACLE_BASE/common/oracle/binexport ORACLE_TERM=xtermexport TNS_ADMIN=$ORACLE_HOME/network/adminexport ORA_NLS10=$ORACLE_HOME/nls/dataexport NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"export DISPLAY=192.168.2.224:0.0export NLS_LANG=AMERICAN_AMERICA.ZHS16GBKexport LD_LIBRARY_PATH=$ORACLE_HOME/libexport LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/libexport LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/libexport CLASSPATH=$ORACLE_HOME/JREexport CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlibexport CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlibexport CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlibexport THREADS_FLAG=nativeexport TEMP=/tmpexport TMPDIR=/tmp安装相关rpm包,配置系统内核参数,这里我使用的是Oracle Linux 5提供的oracle-validated
配置listener和tnsnames
[oracle@standby admin]$ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora# Generated by Oracle configuration tools.SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = standby) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) (SID_NAME = standby) ) )LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521)) )[oracle@standby admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora# Generated by Oracle configuration tools.STANDBY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = standby) ) )[oracle@standby admin]$
[oracle@standby admin]$ lsnrctl startLSNRCTL for Linux: Version 10.2.0.5.0 - Production on 26-DEC-2012 14:02:11Copyright (c) 1991, 2010, Oracle. All rights reserved.Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...TNSLSNR for Linux: Version 10.2.0.5.0 - ProductionSystem parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.oraLog messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener.logListening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standby)(PORT=1521)))Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=standby)(PORT=1521)))STATUS of the LISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version 10.2.0.5.0 - ProductionStart Date 26-DEC-2012 14:02:11Uptime 0 days 0 hr. 0 min. 0 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.oraListener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener.logListening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standby)(PORT=1521)))Services Summary...Service "standby" has 1 instance(s). Instance "standby", status UNKNOWN, has 1 handler(s) for this service...The command completed successfully
配置ASM存储,这里使用Linux的UDEV
[root@standby ~]# cat /etc/udev/rules.d/99-oracle-asmdevices.rules KERNEL=="sd*", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -s %p", RESULT=="36000c29188bfef12f9950965ac870971", NAME="asm-diskb", OWNER="oracle", GROUP="oinstall", MODE="0660"KERNEL=="sd*", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -s %p", RESULT=="36000c292f3719ad39d579ab6427a959c", NAME="asm-diskc", OWNER="oracle", GROUP="oinstall", MODE="0660"[root@standby ~]# start_udev Starting udev: [ OK ][root@standby ~]# ls -l /dev/asm-disk*brw-rw---- 1 oracle oinstall 8, 16 Dec 26 14:24 /dev/asm-diskbbrw-rw---- 1 oracle oinstall 8, 32 Dec 26 14:24 /dev/asm-diskc[root@standby ~]#
使用dbca创建ASM实例
使用root用户执行 /u01/app/oracle/product/10.2.0/db_1/bin/localconfig add
[root@standby ~]# /u01/app/oracle/product/10.2.0/db_1/bin/localconfig add/etc/oracle does not exist. Creating it now.Successfully accumulated necessary OCR keys.Creating OCR keys for user 'root', privgrp 'root'..Operation successful.Configuration for local CSS has been initializedAdding to inittab Startup will be queued to init within 30 seconds.Checking the status of new Oracle init process...Expecting the CRS daemons to be up within 600 seconds.CSS is active on these nodes.standbyCSS is active on all nodes.Oracle CSS service is installed and running under init(1M)
[root@standby ~]# ps -ef|grep ASMoracle 6505 1 0 14:32 ? 00:00:00 asm_pmon_+ASMoracle 6507 1 0 14:32 ? 00:00:00 asm_psp0_+ASMoracle 6509 1 0 14:32 ? 00:00:00 asm_mman_+ASMoracle 6511 1 0 14:32 ? 00:00:00 asm_dbw0_+ASMoracle 6513 1 0 14:32 ? 00:00:00 asm_lgwr_+ASMoracle 6515 1 0 14:32 ? 00:00:00 asm_ckpt_+ASMoracle 6517 1 0 14:32 ? 00:00:00 asm_smon_+ASMoracle 6519 1 0 14:32 ? 00:00:00 asm_rbal_+ASMoracle 6521 1 0 14:32 ? 00:00:00 asm_gmon_+ASMoracle 6525 1 0 14:32 ? 00:00:00 oracle+ASM (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))root 6535 3686 0 14:33 pts/3 00:00:00 grep ASM
访问ASM实例
[oracle@standby ~]$ export ORACLE_SID=+ASM[oracle@standby ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.5.0 - Production on Wed Dec 26 14:37:19 2012Copyright (c) 1982, 2010, Oracle. All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> show parameter asmNAME TYPE------------------------------------ ----------------------VALUE------------------------------asm_diskgroups stringDATADGasm_diskstring string/dev/asm*asm_power_limit integer1SQL> show parameter spfileNAME TYPE------------------------------------ ----------------------VALUE------------------------------spfile string/u01/app/oracle/product/10.2.0/db_1/dbs/spfile+ASM.oraSQL> select path from v$asm_disk;PATH--------------------------------------------------------------------------------/dev/asm-diskb/dev/asm-diskcSQL>
配置主库本地归档路径
SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=racdb' sid='racdb1';System altered.SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=racdb' sid='racdb2';System altered.SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=racdb' sid='racdb3';System altered.
执行主库全备份,控制文件备份,将备份文件scp到standby的/u01/app/oracle/backup下
[oracle@racnode1 ~]$ rman target /Recovery Manager: Release 10.2.0.5.0 - Production on Wed Dec 26 14:50:02 2012Copyright (c) 1982, 2007, Oracle. All rights reserved.connected to target database: RACDB (DBID=800157471)RMAN> run{2> configure channel 1 device type disk connect sys/oracle@racdb1;3> configure channel 2 device type disk connect sys/oracle@racdb2;4> configure channel 3 device type disk connect sys/oracle@racdb3;5> backup database format '/u01/app/oracle/backup/%d_FULLBAK_%T_%u_s%s_p%p' tag 'FULLBAK'6> plus archivelog;7> }using target database control file instead of recovery catalogold RMAN configuration parameters:CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT '*';new RMAN configuration parameters:CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT '*';new RMAN configuration parameters are successfully storedold RMAN configuration parameters:CONFIGURE CHANNEL 2 DEVICE TYPE DISK CONNECT '*';new RMAN configuration parameters:CONFIGURE CHANNEL 2 DEVICE TYPE DISK CONNECT '*';new RMAN configuration parameters are successfully storedold RMAN configuration parameters:CONFIGURE CHANNEL 3 DEVICE TYPE DISK CONNECT '*';new RMAN configuration parameters:CONFIGURE CHANNEL 3 DEVICE TYPE DISK CONNECT '*';new RMAN configuration parameters are successfully storedStarting backup at 26-DEC-2012 14:50:36current log archivedallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=843 instance=racdb1 devtype=DISKallocated channel: ORA_DISK_2channel ORA_DISK_2: sid=848 instance=racdb2 devtype=DISKallocated channel: ORA_DISK_3channel ORA_DISK_3: sid=844 instance=racdb3 devtype=DISKchannel ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=23 recid=45 stamp=803055040channel ORA_DISK_1: starting piece 1 at 26-DEC-2012 14:50:50channel ORA_DISK_2: starting archive log backupsetchannel ORA_DISK_2: specifying archive log(s) in backup setinput archive log thread=2 sequence=14 recid=44 stamp=803055040channel ORA_DISK_2: starting piece 1 at 26-DEC-2012 14:50:51channel ORA_DISK_3: starting archive log backupsetchannel ORA_DISK_3: specifying archive log(s) in backup setinput archive log thread=3 sequence=7 recid=43 stamp=803055038channel ORA_DISK_3: starting piece 1 at 26-DEC-2012 14:50:51channel ORA_DISK_1: finished piece 1 at 26-DEC-2012 14:50:54piece handle=+FLASHDG/racdb/backupset/2012_12_26/annnf0_tag20121226t145048_0.283.803055051 tag=TAG20121226T145048 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:05channel ORA_DISK_2: finished piece 1 at 26-DEC-2012 14:50:54piece handle=+FLASHDG/racdb/backupset/2012_12_26/annnf0_tag20121226t145048_0.278.803055051 tag=TAG20121226T145048 comment=NONEchannel ORA_DISK_2: backup set complete, elapsed time: 00:00:05channel ORA_DISK_3: finished piece 1 at 26-DEC-2012 14:50:54piece handle=+FLASHDG/racdb/backupset/2012_12_26/annnf0_tag20121226t145048_0.277.803055051 tag=TAG20121226T145048 comment=NONEchannel ORA_DISK_3: backup set complete, elapsed time: 00:00:05Finished backup at 26-DEC-2012 14:50:54Starting backup at 26-DEC-2012 14:50:54using channel ORA_DISK_1using channel ORA_DISK_2using channel ORA_DISK_3channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=+DATADG/racdb/datafile/system.269.802972261channel ORA_DISK_1: starting piece 1 at 26-DEC-2012 14:50:55channel ORA_DISK_2: starting full datafile backupsetchannel ORA_DISK_2: specifying datafile(s) in backupsetinput datafile fno=00003 name=+DATADG/racdb/datafile/sysaux.265.802972263input datafile fno=00006 name=+DATADG/racdb/datafile/undotbs2.259.802972265input datafile fno=00004 name=+DATADG/racdb/datafile/users.257.802972267channel ORA_DISK_2: starting piece 1 at 26-DEC-2012 14:50:56channel ORA_DISK_3: starting full datafile backupsetchannel ORA_DISK_3: specifying datafile(s) in backupsetinput datafile fno=00005 name=+DATADG/racdb/datafile/example.258.802972265input datafile fno=00002 name=+DATADG/racdb/datafile/undotbs1.256.802972267input datafile fno=00007 name=+DATADG/racdb/datafile/undotbs3.264.802972269channel ORA_DISK_3: starting piece 1 at 26-DEC-2012 14:51:05channel ORA_DISK_1: finished piece 1 at 26-DEC-2012 14:51:36piece handle=/u01/app/oracle/backup/RACDB_FULLBAK_20121226_0kntr9ef_s20_p1 tag=FULLBAK comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:41channel ORA_DISK_2: finished piece 1 at 26-DEC-2012 14:51:44piece handle=/u01/app/oracle/backup/RACDB_FULLBAK_20121226_0lntr9ef_s21_p1 tag=FULLBAK comment=NONEchannel ORA_DISK_2: backup set complete, elapsed time: 00:00:49channel ORA_DISK_3: finished piece 1 at 26-DEC-2012 14:51:44piece handle=/u01/app/oracle/backup/RACDB_FULLBAK_20121226_0mntr9eg_s22_p1 tag=FULLBAK comment=NONEchannel ORA_DISK_3: backup set complete, elapsed time: 00:00:48Finished backup at 26-DEC-2012 14:51:43Starting backup at 26-DEC-2012 14:51:44current log archivedusing channel ORA_DISK_1using channel ORA_DISK_2using channel ORA_DISK_3channel ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=24 recid=47 stamp=803055105channel ORA_DISK_1: starting piece 1 at 26-DEC-2012 14:51:49channel ORA_DISK_3: starting archive log backupsetchannel ORA_DISK_3: specifying archive log(s) in backup setinput archive log thread=3 sequence=8 recid=48 stamp=803055106channel ORA_DISK_3: starting piece 1 at 26-DEC-2012 14:51:50channel ORA_DISK_1: finished piece 1 at 26-DEC-2012 14:51:50piece handle=+FLASHDG/racdb/backupset/2012_12_26/annnf0_tag20121226t145149_0.275.803055109 tag=TAG20121226T145149 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01channel ORA_DISK_3: finished piece 1 at 26-DEC-2012 14:51:51piece handle=+FLASHDG/racdb/backupset/2012_12_26/annnf0_tag20121226t145149_0.273.803055111 tag=TAG20121226T145149 comment=NONEchannel ORA_DISK_3: backup set complete, elapsed time: 00:00:01channel ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=2 sequence=15 recid=46 stamp=803055105channel ORA_DISK_1: starting piece 1 at 26-DEC-2012 14:51:52channel ORA_DISK_1: finished piece 1 at 26-DEC-2012 14:51:53piece handle=+FLASHDG/racdb/backupset/2012_12_26/annnf0_tag20121226t145149_0.272.803055113 tag=TAG20121226T145149 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 26-DEC-2012 14:51:53Starting Control File and SPFILE Autobackup at 26-DEC-2012 14:51:53piece handle=/u01/app/oracle/backup/c-800157471-20121226-00 comment=NONEFinished Control File and SPFILE Autobackup at 26-DEC-2012 14:52:00RMAN> RMAN> backup device type disk format '/u01/app/oracle/backup/standby_ctl_%U' current controlfile for standby;Starting backup at 26-DEC-2012 15:48:17using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=848 instance=racdb1 devtype=DISKallocated channel: ORA_DISK_2channel ORA_DISK_2: sid=845 instance=racdb2 devtype=DISKallocated channel: ORA_DISK_3channel ORA_DISK_3: sid=847 instance=racdb3 devtype=DISKchannel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetincluding standby control file in backupsetchannel ORA_DISK_1: starting piece 1 at 26-DEC-2012 15:48:21channel ORA_DISK_1: finished piece 1 at 26-DEC-2012 15:48:24piece handle=/u01/app/oracle/backup/standby_ctl_0rntrcq3_1_1 tag=TAG20121226T154819 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:05Finished backup at 26-DEC-2012 15:48:24
创建备库口令文件,也可以拷贝rac1的口令文件rename下
[oracle@standby ~]$ orapwd file=$ORACLE_HOME/dbs/orapwstandby entries=5 force=y password=oracle
配置主备库各实例的tnsnames,内容大致一样,从库中去掉LISTENERS_RACDB配置项
[oracle@standby ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora# Generated by Oracle configuration tools.RACDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.151)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = racdb) (INSTANCE_NAME = racdb1) ) )RACDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.151)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.152)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.153)(PORT = 1521)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = racdb) ) )ZWC = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.151)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.152)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.153)(PORT = 1521)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = zwc) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5) ) ) )RACDB3 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.153)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = racdb) (INSTANCE_NAME = racdb3) ) )RACDB2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.152)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = racdb) (INSTANCE_NAME = racdb2) ) )STANDBY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = standby) ) )
测试从库访问主库
[oracle@standby ~]$ sqlplus /nologSQL*Plus: Release 10.2.0.5.0 - Production on Wed Dec 26 15:17:31 2012Copyright (c) 1982, 2010, Oracle. All Rights Reserved.SQL> conn sys@racdb as sysdbaEnter password: Connected.SQL> conn sys@racdb1 as sysdbaEnter password: Connected.SQL> conn sys@racdb2 as sysdbaEnter password: Connected.SQL> conn sys@racdb3 as sysdbaEnter password: Connected.SQL> conn sys@zwc as sysdbaEnter password: Connected.SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP, Data Miningand Real Application Testing options[oracle@standby ~]$
从主库创建备库的pfile
create pfile='/u01/app/oracle/backup/initstandby.ora' from spfile;
修改参数文件
[oracle@standby backup]$ mkdir -p /u01/app/oracle/admin/standby/udump[oracle@standby backup]$ mkdir -p /u01/app/oracle/admin/standby/cdump[oracle@standby backup]$ mkdir -p /u01/app/oracle/admin/standby/bdump[oracle@standby backup]$ mkdir -p /u01/app/oracle/admin/standby/adump[oracle@standby backup]$ export ORACLE_SID=+ASM[oracle@standby backup]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.5.0 - Production on Wed Dec 26 15:41:41 2012Copyright (c) 1982, 2010, Oracle. All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> alter diskgroup datadg add directory '+DATADG/standby';Diskgroup altered.
[oracle@standby dbs]$ cat initstandby.ora *.audit_file_dest='/u01/app/oracle/admin/standby/adump'*.background_dump_dest='/u01/app/oracle/admin/standby/bdump'*.cluster_database=false*.compatible='10.2.0.5.0'*.control_files='+DATADG/standby/controlfile'*.core_dump_dest='/u01/app/oracle/admin/standby/cdump'*.db_block_size=8192*.db_create_file_dest='+DATADG'*.db_domain=''*.db_file_multiblock_read_count=16*.db_name='racdb'*.db_unique_name='standby'*.job_queue_processes=10*.local_listener=''*.log_archive_format='%t_%s_%r.dbf'*.nls_language='SIMPLIFIED CHINESE'*.open_cursors=300*.pga_aggregate_target=199229440*.processes=800*.remote_login_passwordfile='exclusive'*.sessions=885*.sga_target=597688320*.undo_management='AUTO'*.user_dump_dest='/u01/app/oracle/admin/standby/udump'*.log_archive_config='dg_config=(racdb,standby)'*.log_archive_dest_1='location=/u01/app/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=standby'*.log_archive_dest_2='service=racdb1 lgwr sync valid_for=(online_logfiles,primary_roles) db_unique_name=racdb'*.log_archive_dest_state_1='enable'*.log_archive_dest_state_2='enable'*.thread=1*.undo_tablespace='UNDOTBS1'*.standby_file_management='AUTO'*.fal_server='racdb1','racdb2'*.fal_client='standby'*.service_names='standby'*.db_file_name_convert='+DATADG/racdb/','+DATADG/standby/'*.log_file_name_convert='+DATADG/racdb/','+DATADG/standby/','+FLASHDG/racdb/','+DATADG/standby/'[oracle@standby dbs]$
启动备库到nomount,恢复备库
restore controlfile
[oracle@standby dbs]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.5.0 - Production on Wed Dec 26 15:53:19 2012Copyright (c) 1982, 2010, Oracle. All Rights Reserved.Connected to an idle instance.SQL> startup nomountORACLE instance started.Total System Global Area 599785472 bytesFixed Size 2098112 bytesVariable Size 163580992 bytesDatabase Buffers 427819008 bytesRedo Buffers 6287360 bytesSQL> exitDisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options[oracle@standby dbs]$ rman target /Recovery Manager: Release 10.2.0.5.0 - Production on Wed Dec 26 15:53:26 2012Copyright (c) 1982, 2007, Oracle. All rights reserved.connected to target database: racdb (not mounted)RMAN> restore standby controlfile from '/u01/app/oracle/backup/standby_ctl_0rntrcq3_1_1';Starting restore at 26-DEC-2012 15:53:30using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=871 devtype=DISKchannel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:08output filename=+DATADG/standby/controlfile/standby_ctlFinished restore at 26-DEC-2012 15:53:40
restore standby database
RMAN> alter database mount;database mountedreleased channel: ORA_DISK_1RMAN> run{2> allocate channel c1 device type disk;3> allocate channel c2 device type disk;4> restore database;5> release channel c1;6> release channel c2;7> }allocated channel: c1channel c1: sid=871 devtype=DISKallocated channel: c2channel c2: sid=870 devtype=DISKStarting restore at 26-DEC-2012 15:59:06channel c1: starting datafile backupset restorechannel c1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to +DATADG/standby/datafile/system.269.802972261channel c1: reading from backup piece /u01/app/oracle/backup/RACDB_FULLBAK_20121226_0kntr9ef_s20_p1channel c2: starting datafile backupset restorechannel c2: specifying datafile(s) to restore from backup setrestoring datafile 00002 to +DATADG/standby/datafile/undotbs1.256.802972267restoring datafile 00005 to +DATADG/standby/datafile/example.258.802972265restoring datafile 00007 to +DATADG/standby/datafile/undotbs3.264.802972269channel c2: reading from backup piece /u01/app/oracle/backup/RACDB_FULLBAK_20121226_0mntr9eg_s22_p1channel c2: restored backup piece 1piece handle=/u01/app/oracle/backup/RACDB_FULLBAK_20121226_0mntr9eg_s22_p1 tag=FULLBAKchannel c2: restore complete, elapsed time: 00:00:15channel c2: starting datafile backupset restorechannel c2: specifying datafile(s) to restore from backup setrestoring datafile 00003 to +DATADG/standby/datafile/sysaux.265.802972263restoring datafile 00004 to +DATADG/standby/datafile/users.257.802972267restoring datafile 00006 to +DATADG/standby/datafile/undotbs2.259.802972265channel c2: reading from backup piece /u01/app/oracle/backup/RACDB_FULLBAK_20121226_0lntr9ef_s21_p1channel c1: restored backup piece 1piece handle=/u01/app/oracle/backup/RACDB_FULLBAK_20121226_0kntr9ef_s20_p1 tag=FULLBAKchannel c1: restore complete, elapsed time: 00:00:23channel c2: restored backup piece 1piece handle=/u01/app/oracle/backup/RACDB_FULLBAK_20121226_0lntr9ef_s21_p1 tag=FULLBAKchannel c2: restore complete, elapsed time: 00:00:23Finished restore at 26-DEC-2012 15:59:45released channel: c1released channel: c2[oracle@standby ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.5.0 - Production on Wed Dec 26 16:04:46 2012Copyright (c) 1982, 2010, Oracle. All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> set linesize 200SQL> select dbid,name,switchover_status,db_unique_name,database_role,open_mode,current_scn from v$database; DBID NAME SWITCHOVER_STATUS DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE CURRENT_SCN---------- --------- -------------------- ------------------------------ ---------------- ---------- ----------- 800157471 RACDB SESSIONS ACTIVE standby PHYSICAL STANDBY MOUNTED 1044439
创建备库standby logfile
SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ----------------------- 1 1 25 52428800 2 NO CURRENT 1039974 26-DEC-2012 14:51:44 2 1 24 52428800 2 YES INACTIVE 1039911 26-DEC-2012 14:50:38 3 2 15 52428800 2 YES INACTIVE 1039909 26-DEC-2012 14:50:38 4 2 16 52428800 2 NO CURRENT 1039976 26-DEC-2012 14:51:44 5 3 9 52428800 2 NO CURRENT 1039979 26-DEC-2012 14:51:46 6 3 8 52428800 2 YES INACTIVE 1039906 26-DEC-2012 14:50:386 rows selected.SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_---------- ------- ------- --------------------------------------------------------- --- 2 ONLINE +DATADG/standby/onlinelog/group_2.262.802540719 NO 2 ONLINE +DATADG/standby/onlinelog/group_2.258.802540725 YES 1 ONLINE +DATADG/standby/onlinelog/group_1.261.802540709 NO 1 ONLINE +DATADG/standby/onlinelog/group_1.257.802540715 YES 3 ONLINE +DATADG/standby/onlinelog/group_3.266.802541097 NO 3 ONLINE +DATADG/standby/onlinelog/group_3.259.802541105 YES 4 ONLINE +DATADG/standby/onlinelog/group_4.267.802541113 NO 4 ONLINE +DATADG/standby/onlinelog/group_4.260.802541123 YES 5 ONLINE +DATADG/standby/onlinelog/group_5.270.802888327 NO 5 ONLINE +DATADG/standby/onlinelog/group_5.279.802888333 YES 6 ONLINE +DATADG/standby/onlinelog/group_6.271.802888337 NO 6 ONLINE +DATADG/standby/onlinelog/group_6.280.802888343 YES12 rows selected.
SQL> alter database add standby logfile thread 1 group 11 size 50M,group 12 size 50M,group 13 size 50M;Database altered.SQL> alter database add standby logfile thread 2 group 14 size 50M,group 15 size 50M,group 16 size 50M;Database altered.SQL> alter database add standby logfile thread 3 group 17 size 50M,group 18 size 50M,group 19 size 50M;Database altered.
SQL> select * from v$standby_log; GROUP# DBID THREAD#SEQUENCE# BYTES USED ARC STATUS FIRST_CHANGE# FIRST_TIME LAST_CHANGE# LAST_TIME---------- ---------------------------------------- ---------- ---------- ---------- ---------- --- ---------- ------------- ----------------------- ------------ -----------------------11 UNASSIGNED 10 52428800 512 YES UNASSIGNED 0012 UNASSIGNED 10 52428800 512 YES UNASSIGNED 0013 UNASSIGNED 10 52428800 512 YES UNASSIGNED 0014 UNASSIGNED 20 52428800 512 YES UNASSIGNED 0015 UNASSIGNED 20 52428800 512 YES UNASSIGNED 0016 UNASSIGNED 20 52428800 512 YES UNASSIGNED 0017 UNASSIGNED 30 52428800 512 YES UNASSIGNED 0018 UNASSIGNED 30 52428800 512 YES UNASSIGNED 0019 UNASSIGNED 30 52428800 512 YES UNASSIGNED 009 rows selected.
备库开启redo apply
[oracle@standby ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.5.0 - Production on Wed Dec 26 16:14:59 2012Copyright (c) 1982, 2010, Oracle. All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> alter database recover managed standby database using current logfile disconnect from session;Database altered.SQL> select sequence#,name,first_time,next_time,applied from v$archived_log;no rows selectedSQL> select dbid,name,switchover_status,db_unique_name,database_role,open_mode,current_scn from v$database; DBID NAME SWITCHOVER_STATUS DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE CURRENT_SCN---------- --------- -------------------- ------------------------------ ---------------- ---------- ----------- 800157471 RACDB SESSIONS ACTIVE standby PHYSICAL STANDBY MOUNTED 1044439
SQL> create spfile from pfile;File created.
观察备库的alter.log,发现正在等待rac1 sequence为24的archivelog
[oracle@standby ~]$ tail -f /u01/app/oracle/admin/standby/bdump/alert_standby.log ORA-00312: 联机日志 6 线程 3: '+DATADG/standby/onlinelog/group_6.280.802888343'ORA-17503: ksfdopn: 2 未能打开文件 +DATADG/standby/onlinelog/group_6.280.802888343ORA-15173: entry 'group_6.280.802888343' does not exist in directory 'onlinelog'ORA-00312: 联机日志 6 线程 3: '+DATADG/standby/onlinelog/group_6.271.802888337'ORA-17503: ksfdopn: 2 未能打开文件 +DATADG/standby/onlinelog/group_6.271.802888337ORA-15173: entry 'group_6.271.802888337' does not exist in directory 'onlinelog'Deleted Oracle managed file +DATADG/standby/onlinelog/group_6.271.802888337Deleted Oracle managed file +DATADG/standby/onlinelog/group_6.280.802888343Clearing online redo logfile 6 completeMedia Recovery Waiting for thread 1 sequence 24
配置主库参数
SQL> alter system set log_archive_dest_2='service=standby lgwr sync valid_for=(online_logfiles,primary_role) db_unique_name=standby' sid='*';System altered.SQL> alter system set log_archive_dest_state_2='enable';System altered.SQL> alter system set log_archive_config='dg_config=(racdb,standby)' sid='*';System altered.SQL> alter system set fal_server='standby' sid='*';System altered.SQL> alter system set fal_client='racdb1' sid='*';System altered.SQL> alter system set fal_client='racdb1' sid='racdb1';System altered.SQL> alter system set fal_client='racdb2' sid='racdb2';System altered.SQL> alter system set fal_client='racdb3' sid='racdb3';System altered.
SQL> alter system set standby_file_management=AUTO;System altered.
在主库多次切换日志,发现都没有传输到备库,查看主库alert.log发现如下信息
ORA-16047: 目标设置和备用之间的 DGID 不匹配
Wed Dec 26 16:33:36 CST 2012
PING[ARC1]: Heartbeat failed to connect to standby 'standby'. Error is 16047.
Wed Dec 26 16:35:12 CST 2012
看意思是指log_archive_config参数不对,我检查了主备库的这个参数,发现是没有问题的
我记得在配置single dataguard的时候db_unique_name默认等于db_name,故这里没有在主库配置db_unique_name这个参数。问题就是出在这里
在主库配置db_unique_name参数重启RAC实例
SQL> alter system set db_unique_name='racdb' scope=spfile;System altered.SQL> exitDisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP, Data Miningand Real Application Testing options[oracle@racnode1 backup]$ srvctl stop database -d racdb[oracle@racnode1 backup]$ srvctl start database -d racdb[oracle@racnode1 backup]$ srvctl start service -d racdb -s zwc
主库
SQL> show parameter log_archive_configNAME TYPE VALUE------------------------------------ ----------- ------------------------------log_archive_config string dg_config=(racdb,standby)SQL> show parameter db_unique_nameNAME TYPE VALUE------------------------------------ ----------- ------------------------------db_unique_name string racdbSQL>
备库
SQL> show parameter log_archive_configNAME TYPE VALUE------------------------------------ ----------- ------------------------------log_archive_config string dg_config=(racdb,standby)SQL> show parameter db_unique_nameNAME TYPE VALUE------------------------------------ ----------- ------------------------------db_unique_name string standby
主库的alert.log
SUCCESS: diskgroup DATADG was mountedSUCCESS: diskgroup FLASHDG was mountedThu Dec 27 08:57:15 CST 2012Setting recovery target incarnation to 2Thu Dec 27 08:57:15 CST 2012Successful mount of redo thread 1, with mount id 800695506Thu Dec 27 08:57:15 CST 2012Database mounted in Shared Mode (CLUSTER_DATABASE=TRUE)Completed: ALTER DATABASE MOUNTThu Dec 27 08:57:21 CST 2012ALTER DATABASE OPENPicked broadcast on commit scheme to generate SCNsThu Dec 27 08:57:21 CST 2012LGWR: STARTING ARCH PROCESSESARC0 started with pid=24, OS id=4436Thu Dec 27 08:57:21 CST 2012ARC0: Archival startedARC1: Archival startedLGWR: STARTING ARCH PROCESSES COMPLETEARC1 started with pid=25, OS id=4438LNSb started with pid=26, OS id=4440Thu Dec 27 08:57:28 CST 2012******************************************************************LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2******************************************************************LNSb started with pid=26, OS id=4477Thu Dec 27 08:57:32 CST 2012LGWR: Standby redo logfile selected for thread 1 sequence 38 for destination LOG_ARCHIVE_DEST_2Thu Dec 27 08:57:32 CST 2012Thread 1 advanced to log sequence 38 (thread open)Thread 1 opened at log sequence 38 Current log# 2 seq# 38 mem# 0: +DATADG/racdb/onlinelog/group_2.262.802540719 Current log# 2 seq# 38 mem# 1: +FLASHDG/racdb/onlinelog/group_2.258.802540725Successful open of redo thread 1Thu Dec 27 08:57:32 CST 2012MTTR advisory is disabled because FAST_START_MTTR_TARGET is not setThu Dec 27 08:57:32 CST 2012SMON: enabling cache recoveryThu Dec 27 08:57:32 CST 2012ARC0: Becoming the 'no FAL' ARCHARC0: Becoming the 'no SRL' ARCHThu Dec 27 08:57:32 CST 2012ARC1: Becoming the heartbeat ARCHThu Dec 27 08:57:35 CST 2012Redo Shipping Client Connected as PUBLIC-- Connected User is ValidThu Dec 27 08:57:43 CST 2012Successfully onlined Undo Tablespace 1.Thu Dec 27 08:57:43 CST 2012SMON: enabling tx recoveryThu Dec 27 08:57:43 CST 2012Database Characterset is ZHS16GBKOpening with internal Resource Manager plan replication_dependency_tracking turned off (no async multimaster replication found)Starting background process QMNCQMNC started with pid=27, OS id=4663Thu Dec 27 08:58:05 CST 2012Completed: ALTER DATABASE OPENThu Dec 27 08:58:14 CST 2012ARC1: Standby redo logfile selected for thread 1 sequence 37 for destination LOG_ARCHIVE_DEST_2Thu Dec 27 08:58:18 CST 2012Redo Shipping Client Connected as PUBLIC-- Connected User is ValidRedo Shipping Client Connected as PUBLIC-- Connected User is ValidThu Dec 27 08:59:20 CST 2012ALTER SYSTEM SET service_names='zwc' SCOPE=MEMORY SID='racdb1';Thu Dec 27 09:07:14 CST 2012LGWR: Standby redo logfile selected for thread 1 sequence 39 for destination LOG_ARCHIVE_DEST_2Thu Dec 27 09:07:14 CST 2012Thread 1 advanced to log sequence 39 (LGWR switch) Current log# 1 seq# 39 mem# 0: +DATADG/racdb/onlinelog/group_1.261.802540709 Current log# 1 seq# 39 mem# 1: +FLASHDG/racdb/onlinelog/group_1.257.802540715Thu Dec 27 09:07:51 CST 2012LGWR: Standby redo logfile selected for thread 1 sequence 40 for destination LOG_ARCHIVE_DEST_2Thu Dec 27 09:07:51 CST 2012Thread 1 advanced to log sequence 40 (LGWR switch) Current log# 2 seq# 40 mem# 0: +DATADG/racdb/onlinelog/group_2.262.802540719 Current log# 2 seq# 40 mem# 1: +FLASHDG/racdb/onlinelog/group_2.258.802540725Thu Dec 27 09:08:10 CST 2012ALTER SYSTEM SET service_names='zwc','racdb' SCOPE=MEMORY SID='racdb1';Thu Dec 27 09:08:36 CST 2012Thread 1 cannot allocate new log, sequence 41Checkpoint not complete Current log# 2 seq# 40 mem# 0: +DATADG/racdb/onlinelog/group_2.262.802540719 Current log# 2 seq# 40 mem# 1: +FLASHDG/racdb/onlinelog/group_2.258.802540725LGWR: Standby redo logfile selected for thread 1 sequence 41 for destination LOG_ARCHIVE_DEST_2Thu Dec 27 09:08:40 CST 2012Thread 1 advanced to log sequence 41 (LGWR switch) Current log# 1 seq# 41 mem# 0: +DATADG/racdb/onlinelog/group_1.261.802540709 Current log# 1 seq# 41 mem# 1: +FLASHDG/racdb/onlinelog/group_1.257.802540715Thu Dec 27 09:12:24 CST 2012Thread 1 cannot allocate new log, sequence 42Checkpoint not complete Current log# 1 seq# 41 mem# 0: +DATADG/racdb/onlinelog/group_1.261.802540709 Current log# 1 seq# 41 mem# 1: +FLASHDG/racdb/onlinelog/group_1.257.802540715LGWR: Standby redo logfile selected for thread 1 sequence 42 for destination LOG_ARCHIVE_DEST_2Thu Dec 27 09:12:28 CST 2012Thread 1 advanced to log sequence 42 (LGWR switch) Current log# 2 seq# 42 mem# 0: +DATADG/racdb/onlinelog/group_2.262.802540719 Current log# 2 seq# 42 mem# 1: +FLASHDG/racdb/onlinelog/group_2.258.802540725Thu Dec 27 09:13:05 CST 2012LGWR: Standby redo logfile selected for thread 1 sequence 43 for destination LOG_ARCHIVE_DEST_2Thu Dec 27 09:13:05 CST 2012Thread 1 advanced to log sequence 43 (LGWR switch) Current log# 1 seq# 43 mem# 0: +DATADG/racdb/onlinelog/group_1.261.802540709 Current log# 1 seq# 43 mem# 1: +FLASHDG/racdb/onlinelog/group_1.257.802540715
备库的alert.log
ARC1: Thread not mountedThu Dec 27 08:48:50 CST 2012Successful mount of redo thread 1, with mount id 800697579Thu Dec 27 08:48:50 CST 2012Physical Standby Database mounted.Completed: alter database mount standby databaseThu Dec 27 08:49:09 CST 2012alter database recover managed standby database using current logfile disconnect from sessionThu Dec 27 08:49:09 CST 2012Attempt to start background Managed Standby Recovery process (standby)MRP0 started with pid=19, OS id=15411Thu Dec 27 08:49:09 CST 2012MRP0: Background Managed Standby Recovery process started (standby)Managed Standby Recovery starting Real Time Apply parallel recovery started with 2 processesThu Dec 27 08:49:14 CST 2012Waiting for all non-current ORLs to be archived...Media Recovery Waiting for thread 1 sequence 24Thu Dec 27 08:49:15 CST 2012Completed: alter database recover managed standby database using current logfile disconnect from sessionThu Dec 27 08:57:03 CST 2012Using STANDBY_ARCHIVE_DEST parameter default value as /u01/app/oracle/archRedo Shipping Client Connected as PUBLIC-- Connected User is ValidRFS[1]: Assigned to RFS process 15448RFS[1]: Identified database type as 'physical standby'Primary database is in MAXIMUM PERFORMANCE modePrimary thread 1 already marked as open; setting 'closed'Primary thread 2 already marked as open; setting 'closed'Primary thread 3 already marked as open; setting 'closed'Thu Dec 27 08:57:03 CST 2012RFS LogMiner: Client disabled from further notificationRedo Shipping Client Connected as PUBLIC-- Connected User is ValidRFS[2]: Assigned to RFS process 15450RFS[2]: Identified database type as 'physical standby'Primary database is in MAXIMUM PERFORMANCE modePrimary database is in MAXIMUM PERFORMANCE modeRFS[2]: Successfully opened standby log 17: '+DATADG/standby/onlinelog/group_17.270.803059869'Thu Dec 27 08:57:15 CST 2012Redo Shipping Client Connected as PUBLIC-- Connected User is ValidRFS[3]: Assigned to RFS process 15454RFS[3]: Identified database type as 'physical standby'RFS[3]: Successfully opened standby log 18: '+DATADG/standby/onlinelog/group_18.271.803059871'Thu Dec 27 08:57:24 CST 2012Redo Shipping Client Connected as PUBLIC-- Connected User is ValidRFS[4]: Assigned to RFS process 15456RFS[4]: Identified database type as 'physical standby'Primary database is in MAXIMUM PERFORMANCE modeRedo Shipping Client Connected as PUBLIC-- Connected User is ValidRFS[5]: Assigned to RFS process 15458RFS[5]: Identified database type as 'physical standby'Primary database is in MAXIMUM PERFORMANCE modePrimary database is in MAXIMUM PERFORMANCE modeRFS[5]: Successfully opened standby log 11: '+DATADG/standby/onlinelog/group_11.264.803059801'Thu Dec 27 08:57:35 CST 2012Fetching gap sequence in thread 1, gap sequence 24-36Thu Dec 27 08:57:35 CST 2012Redo Shipping Client Connected as PUBLIC-- Connected User is ValidRFS[6]: Assigned to RFS process 15460RFS[6]: Identified database type as 'physical standby'Primary database is in MAXIMUM PERFORMANCE modeRedo Shipping Client Connected as PUBLIC-- Connected User is ValidRFS[7]: Assigned to RFS process 15462RFS[7]: Identified database type as 'physical standby'Primary database is in MAXIMUM PERFORMANCE modePrimary database is in MAXIMUM PERFORMANCE modeRFS[7]: Successfully opened standby log 14: '+DATADG/standby/onlinelog/group_14.267.803059831'Thu Dec 27 08:57:47 CST 2012Redo Shipping Client Connected as PUBLIC-- Connected User is ValidRFS[8]: Assigned to RFS process 15464RFS[8]: Identified database type as 'physical standby'RFS[8]: Archived Log: '/u01/app/oracle/arch/1_24_802540708.dbf'RFS[8]: Archived Log: '/u01/app/oracle/arch/1_25_802540708.dbf'RFS[8]: Archived Log: '/u01/app/oracle/arch/1_26_802540708.dbf'RFS[8]: Archived Log: '/u01/app/oracle/arch/1_27_802540708.dbf'RFS[8]: Archived Log: '/u01/app/oracle/arch/1_28_802540708.dbf'RFS[8]: Archived Log: '/u01/app/oracle/arch/1_29_802540708.dbf'Thu Dec 27 08:57:59 CST 2012RFS[8]: Archived Log: '/u01/app/oracle/arch/1_30_802540708.dbf'RFS[8]: Archived Log: '/u01/app/oracle/arch/1_31_802540708.dbf'Thu Dec 27 08:58:06 CST 2012Redo Shipping Client Connected as PUBLIC-- Connected User is ValidRFS[9]: Assigned to RFS process 15468RFS[9]: Identified database type as 'physical standby'Thu Dec 27 08:58:06 CST 2012RFS[8]: Archived Log: '/u01/app/oracle/arch/1_32_802540708.dbf'Thu Dec 27 08:58:06 CST 2012RFS[9]: Successfully opened standby log 15: '+DATADG/standby/onlinelog/group_15.268.803059833'Thu Dec 27 08:58:07 CST 2012RFS[8]: Archived Log: '/u01/app/oracle/arch/1_33_802540708.dbf'RFS[8]: Archived Log: '/u01/app/oracle/arch/1_34_802540708.dbf'RFS[8]: Archived Log: '/u01/app/oracle/arch/1_35_802540708.dbf'RFS[8]: Archived Log: '/u01/app/oracle/arch/1_36_802540708.dbf'RFS[8]: Successfully opened standby log 12: '+DATADG/standby/onlinelog/group_12.265.803059803'Thu Dec 27 08:58:18 CST 2012Media Recovery Log /u01/app/oracle/arch/1_24_802540708.dbfMedia Recovery Waiting for thread 2 sequence 15Fetching gap sequence in thread 2, gap sequence 15-18Thu Dec 27 08:58:19 CST 2012RFS[8]: Archived Log: '/u01/app/oracle/arch/2_15_802540708.dbf'Thu Dec 27 08:58:33 CST 2012RFS[9]: Archived Log: '/u01/app/oracle/arch/2_16_802540708.dbf'RFS[9]: Archived Log: '/u01/app/oracle/arch/2_17_802540708.dbf'RFS[9]: Archived Log: '/u01/app/oracle/arch/2_18_802540708.dbf'Thu Dec 27 08:58:49 CST 2012Media Recovery Log /u01/app/oracle/arch/2_15_802540708.dbfMedia Recovery Waiting for thread 3 sequence 8Fetching gap sequence in thread 3, gap sequence 8-12Thu Dec 27 08:59:06 CST 2012RFS[3]: Archived Log: '/u01/app/oracle/arch/3_8_802540708.dbf'RFS[3]: Archived Log: '/u01/app/oracle/arch/3_9_802540708.dbf'RFS[3]: Archived Log: '/u01/app/oracle/arch/3_10_802540708.dbf'RFS[3]: Archived Log: '/u01/app/oracle/arch/3_11_802540708.dbf'RFS[3]: Archived Log: '/u01/app/oracle/arch/3_12_802540708.dbf'Thu Dec 27 08:59:19 CST 2012Media Recovery Log /u01/app/oracle/arch/3_8_802540708.dbfMedia Recovery Log /u01/app/oracle/arch/1_25_802540708.dbfMedia Recovery Log /u01/app/oracle/arch/2_16_802540708.dbfMedia Recovery Log /u01/app/oracle/arch/3_9_802540708.dbfMedia Recovery Log /u01/app/oracle/arch/1_26_802540708.dbfMedia Recovery Log /u01/app/oracle/arch/1_27_802540708.dbfMedia Recovery Log /u01/app/oracle/arch/1_28_802540708.dbfMedia Recovery Log /u01/app/oracle/arch/3_10_802540708.dbfMedia Recovery Log /u01/app/oracle/arch/1_29_802540708.dbfMedia Recovery Log /u01/app/oracle/arch/2_17_802540708.dbfMedia Recovery Log /u01/app/oracle/arch/1_30_802540708.dbfMedia Recovery Log /u01/app/oracle/arch/1_31_802540708.dbfMedia Recovery Log /u01/app/oracle/arch/3_11_802540708.dbfMedia Recovery Log /u01/app/oracle/arch/1_32_802540708.dbfMedia Recovery Log /u01/app/oracle/arch/2_18_802540708.dbfMedia Recovery Log /u01/app/oracle/arch/1_33_802540708.dbfMedia Recovery Log /u01/app/oracle/arch/1_34_802540708.dbfMedia Recovery Log /u01/app/oracle/arch/3_12_802540708.dbfThu Dec 27 08:59:30 CST 2012Media Recovery Log /u01/app/oracle/arch/1_35_802540708.dbfMedia Recovery Log /u01/app/oracle/arch/2_19_802540708.dbfMedia Recovery Log /u01/app/oracle/arch/1_36_802540708.dbfThu Dec 27 09:00:02 CST 2012Media Recovery Log /u01/app/oracle/arch/1_37_802540708.dbfMedia Recovery Log /u01/app/oracle/arch/3_13_802540708.dbfMedia Recovery Waiting for thread 3 sequence 14 (in transit)Thu Dec 27 09:00:04 CST 2012Recovery of Online Redo Log: Thread 3 Group 17 Seq 14 Reading mem 0 Mem# 0: +DATADG/standby/onlinelog/group_17.270.803059869Media Recovery Waiting for thread 1 sequence 38 (in transit)Thu Dec 27 09:00:04 CST 2012Recovery of Online Redo Log: Thread 1 Group 11 Seq 38 Reading mem 0 Mem# 0: +DATADG/standby/onlinelog/group_11.264.803059801Media Recovery Waiting for thread 2 sequence 20 (in transit)Thu Dec 27 09:00:04 CST 2012Recovery of Online Redo Log: Thread 2 Group 14 Seq 20 Reading mem 0 Mem# 0: +DATADG/standby/onlinelog/group_14.267.803059831Thu Dec 27 09:07:14 CST 2012Primary database is in MAXIMUM PERFORMANCE modeRFS[5]: Successfully opened standby log 11: '+DATADG/standby/onlinelog/group_11.264.803059801'Thu Dec 27 09:07:15 CST 2012Media Recovery Log /u01/app/oracle/arch/1_38_802540708.dbfThu Dec 27 09:07:26 CST 2012Media Recovery Waiting for thread 1 sequence 39 (in transit)Thu Dec 27 09:07:26 CST 2012Recovery of Online Redo Log: Thread 1 Group 11 Seq 39 Reading mem 0 Mem# 0: +DATADG/standby/onlinelog/group_11.264.803059801Thu Dec 27 09:07:51 CST 2012Primary database is in MAXIMUM PERFORMANCE modeRFS[5]: Successfully opened standby log 11: '+DATADG/standby/onlinelog/group_11.264.803059801'Thu Dec 27 09:07:52 CST 2012Primary database is in MAXIMUM PERFORMANCE modeRFS[2]: Successfully opened standby log 17: '+DATADG/standby/onlinelog/group_17.270.803059869'Thu Dec 27 09:07:55 CST 2012Media Recovery Log /u01/app/oracle/arch/3_14_802540708.dbfMedia Recovery Log /u01/app/oracle/arch/1_39_802540708.dbfMedia Recovery Waiting for thread 1 sequence 40 (in transit)Thu Dec 27 09:07:58 CST 2012Recovery of Online Redo Log: Thread 1 Group 11 Seq 40 Reading mem 0 Mem# 0: +DATADG/standby/onlinelog/group_11.264.803059801Media Recovery Waiting for thread 3 sequence 15 (in transit)Thu Dec 27 09:07:58 CST 2012Recovery of Online Redo Log: Thread 3 Group 17 Seq 15 Reading mem 0 Mem# 0: +DATADG/standby/onlinelog/group_17.270.803059869Thu Dec 27 09:08:40 CST 2012Primary database is in MAXIMUM PERFORMANCE modeRFS[5]: Successfully opened standby log 11: '+DATADG/standby/onlinelog/group_11.264.803059801'Thu Dec 27 09:08:41 CST 2012Media Recovery Log /u01/app/oracle/arch/1_40_802540708.dbfThu Dec 27 09:08:43 CST 2012Primary database is in MAXIMUM PERFORMANCE modeRFS[7]: Successfully opened standby log 14: '+DATADG/standby/onlinelog/group_14.267.803059831'Thu Dec 27 09:08:44 CST 2012Media Recovery Log /u01/app/oracle/arch/2_20_802540708.dbfMedia Recovery Waiting for thread 1 sequence 41 (in transit)Thu Dec 27 09:08:53 CST 2012Recovery of Online Redo Log: Thread 1 Group 11 Seq 41 Reading mem 0 Mem# 0: +DATADG/standby/onlinelog/group_11.264.803059801Media Recovery Waiting for thread 2 sequence 21 (in transit)Thu Dec 27 09:08:59 CST 2012Recovery of Online Redo Log: Thread 2 Group 14 Seq 21 Reading mem 0 Mem# 0: +DATADG/standby/onlinelog/group_14.267.803059831Thu Dec 27 09:12:29 CST 2012Primary database is in MAXIMUM PERFORMANCE modeRFS[5]: Successfully opened standby log 11: '+DATADG/standby/onlinelog/group_11.264.803059801'Thu Dec 27 09:12:30 CST 2012Media Recovery Log /u01/app/oracle/arch/1_41_802540708.dbfThu Dec 27 09:12:42 CST 2012Media Recovery Waiting for thread 1 sequence 42 (in transit)Thu Dec 27 09:12:42 CST 2012Recovery of Online Redo Log: Thread 1 Group 11 Seq 42 Reading mem 0 Mem# 0: +DATADG/standby/onlinelog/group_11.264.803059801Thu Dec 27 09:13:06 CST 2012Primary database is in MAXIMUM PERFORMANCE modeRFS[5]: Successfully opened standby log 11: '+DATADG/standby/onlinelog/group_11.264.803059801'Thu Dec 27 09:13:08 CST 2012Media Recovery Log /u01/app/oracle/arch/1_42_802540708.dbfThu Dec 27 09:13:08 CST 2012Primary database is in MAXIMUM PERFORMANCE modeRFS[2]: Successfully opened standby log 17: '+DATADG/standby/onlinelog/group_17.270.803059869'Thu Dec 27 09:13:08 CST 2012Media Recovery Log /u01/app/oracle/arch/3_15_802540708.dbfMedia Recovery Waiting for thread 1 sequence 43 (in transit)Thu Dec 27 09:13:11 CST 2012Recovery of Online Redo Log: Thread 1 Group 11 Seq 43 Reading mem 0 Mem# 0: +DATADG/standby/onlinelog/group_11.264.803059801Thu Dec 27 09:13:23 CST 2012Media Recovery Waiting for thread 3 sequence 16 (in transit)Thu Dec 27 09:13:23 CST 2012Recovery of Online Redo Log: Thread 3 Group 17 Seq 16 Reading mem 0 Mem# 0: +DATADG/standby/onlinelog/group_17.270.803059869可以看到日志全部传输过来了
在主库创建standby logfile,为了switchover之后原RAC primary database和single physical standby database角色变换后新的standby database可以正常应用日志
SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_---------- ------- ------- -------------------------------------------------- --- 2 ONLINE +DATADG/racdb/onlinelog/group_2.262.802540719 NO 2 ONLINE +FLASHDG/racdb/onlinelog/group_2.258.802540725 YES 1 ONLINE +DATADG/racdb/onlinelog/group_1.261.802540709 NO 1 ONLINE +FLASHDG/racdb/onlinelog/group_1.257.802540715 YES 3 ONLINE +DATADG/racdb/onlinelog/group_3.266.802541097 NO 3 ONLINE +FLASHDG/racdb/onlinelog/group_3.259.802541105 YES 4 ONLINE +DATADG/racdb/onlinelog/group_4.267.802541113 NO 4 ONLINE +FLASHDG/racdb/onlinelog/group_4.260.802541123 YES 5 ONLINE +DATADG/racdb/onlinelog/group_5.270.802888327 NO 5 ONLINE +FLASHDG/racdb/onlinelog/group_5.279.802888333 YES 6 ONLINE +DATADG/racdb/onlinelog/group_6.271.802888337 NO 6 ONLINE +FLASHDG/racdb/onlinelog/group_6.280.802888343 YES12 rows selected.SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ----------------------- 1 1 43 52428800 2 NO CURRENT 1154512 27-DEC-2012 09:13:05 2 1 42 52428800 2 YES INACTIVE 1154455 27-DEC-2012 09:12:28 3 2 21 52428800 2 NO CURRENT 1154041 27-DEC-2012 09:08:43 4 2 20 52428800 2 YES INACTIVE 1152090 27-DEC-2012 08:57:39 5 3 15 52428800 2 YES INACTIVE 1153073 27-DEC-2012 09:07:52 6 3 16 52428800 2 NO CURRENT 1154577 27-DEC-2012 09:13:086 rows selected.SQL> alter database add standby logfile thread 1 group 7 size 50M,group 8 size 50M,group 9 size 50M;Database altered.SQL> alter database add standby logfile thread 2 group 10 size 50M,group 11 size 50M,group 12 size 50M;Database altered.SQL> alter database add standby logfile thread 3 group 13 size 50M,group 14 size 50M,group 15 size 50M;Database altered.SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_---------- ------- ------- -------------------------------------------------- --- 2 ONLINE +DATADG/racdb/onlinelog/group_2.262.802540719 NO 2 ONLINE +FLASHDG/racdb/onlinelog/group_2.258.802540725 YES 1 ONLINE +DATADG/racdb/onlinelog/group_1.261.802540709 NO 1 ONLINE +FLASHDG/racdb/onlinelog/group_1.257.802540715 YES 3 ONLINE +DATADG/racdb/onlinelog/group_3.266.802541097 NO 3 ONLINE +FLASHDG/racdb/onlinelog/group_3.259.802541105 YES 4 ONLINE +DATADG/racdb/onlinelog/group_4.267.802541113 NO 4 ONLINE +FLASHDG/racdb/onlinelog/group_4.260.802541123 YES 5 ONLINE +DATADG/racdb/onlinelog/group_5.270.802888327 NO 5 ONLINE +FLASHDG/racdb/onlinelog/group_5.279.802888333 YES 6 ONLINE +DATADG/racdb/onlinelog/group_6.271.802888337 NO 6 ONLINE +FLASHDG/racdb/onlinelog/group_6.280.802888343 YES 7 STANDBY +DATADG/racdb/onlinelog/group_7.272.803122431 NO 7 STANDBY +FLASHDG/racdb/onlinelog/group_7.271.803122435 YES 8 STANDBY +DATADG/racdb/onlinelog/group_8.273.803122441 NO 8 STANDBY +FLASHDG/racdb/onlinelog/group_8.268.803122445 YES 9 STANDBY +DATADG/racdb/onlinelog/group_9.274.803122449 NO 9 STANDBY +FLASHDG/racdb/onlinelog/group_9.264.803122453 YES10 STANDBY +DATADG/racdb/onlinelog/group_10.275.803122477 NO10 STANDBY +FLASHDG/racdb/onlinelog/group_10.282.803122483 YES11 STANDBY +DATADG/racdb/onlinelog/group_11.276.803122487 NO11 STANDBY +FLASHDG/racdb/onlinelog/group_11.281.803122493 YES12 STANDBY +DATADG/racdb/onlinelog/group_12.277.803122497 NO12 STANDBY +FLASHDG/racdb/onlinelog/group_12.276.803122501 YES13 STANDBY +DATADG/racdb/onlinelog/group_13.278.803122527 NO13 STANDBY +FLASHDG/racdb/onlinelog/group_13.274.803122531 YES14 STANDBY +DATADG/racdb/onlinelog/group_14.279.803122537 NO14 STANDBY +FLASHDG/racdb/onlinelog/group_14.270.803122541 YES15 STANDBY +DATADG/racdb/onlinelog/group_15.280.803122545 NO15 STANDBY +FLASHDG/racdb/onlinelog/group_15.269.803122551 YES30 rows selected.SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ----------------------- 1 1 43 52428800 2 NO CURRENT 1154512 27-DEC-2012 09:13:05 2 1 42 52428800 2 YES INACTIVE 1154455 27-DEC-2012 09:12:28 3 2 21 52428800 2 NO CURRENT 1154041 27-DEC-2012 09:08:43 4 2 20 52428800 2 YES INACTIVE 1152090 27-DEC-2012 08:57:39 5 3 15 52428800 2 YES INACTIVE 1153073 27-DEC-2012 09:07:52 6 3 16 52428800 2 NO CURRENT 1154577 27-DEC-2012 09:13:086 rows selected.SQL> select * from v$standby_log; GROUP# DBID THREAD#SEQUENCE# BYTES USED ARC STATUS FIRST_CHANGE# FIRST_TIME LAST_CHANGE# LAST_TIME---------- ---------------------------------------- ---------- ---------- ---------- ---------- --- ---------- ------------- ----------------------- ------------ ----------------------- 7 UNASSIGNED 10 52428800 512 YES UNASSIGNED 00 8 UNASSIGNED 10 52428800 512 YES UNASSIGNED 00 9 UNASSIGNED 10 52428800 512 YES UNASSIGNED 0010 UNASSIGNED 20 52428800 512 YES UNASSIGNED 0011 UNASSIGNED 20 52428800 512 YES UNASSIGNED 0012 UNASSIGNED 20 52428800 512 YES UNASSIGNED 0013 UNASSIGNED 30 52428800 512 YES UNASSIGNED 0014 UNASSIGNED 30 52428800 512 YES UNASSIGNED 0015 UNASSIGNED 30 52428800 512 YES UNASSIGNED 009 rows selected.
还需配置standby_file_management,db_file_name_convert,log_file_name_convert这三个参数,谁能告诉我为什么后面两个参数不给修改?
只好生成pfile,通过修改pfile启动实例重新生成spfile
SQL> alter system set standby_file_management='AUTO' sid='*';System altered.SQL> alter system set db_file_name_convert='+DATADG/standby/','+DATADG/racdb/' sid='*' scope=spfile;alter system set db_file_name_convert='+DATADG/standby/','+DATADG/racdb/' sid='*' scope=spfile *ERROR at line 1:ORA-02096: specified initialization parameter is not modifiable with this optionSQL> alter system set log_file_name_convert='+DATADG/standby/','+DATADG/racdb/','+DATADG/standby/','+FLASHDG/racdb/' sid='*' scope=spfile;alter system set log_file_name_convert='+DATADG/standby/','+DATADG/racdb/','+DATADG/standby/','+FLASHDG/racdb/' sid='*' scope=spfile *ERROR at line 1:ORA-02095: specified initialization parameter cannot be modified
修改pfile后使用以下命令创建spfile,然后重启rac实例
create spfile='+DATADG/racdb/spfileracdb.ora' from pfile='/tmp/rac1.ora';
srvctl stop/start database -d racdb
测试dataguard数据同步
主库创建表空间
[oracle@racnode1 ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.5.0 - Production on Thu Dec 27 10:24:14 2012Copyright (c) 1982, 2010, Oracle. All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP, Data Miningand Real Application Testing optionsSQL> create tablespace zhongwc datafile size 10M autoextend on;Tablespace created.
备库查看是否同步
ASMCMD [+datadg/standby/datafile] > ls -lType Redund Striped Time Sys NameDATAFILE MIRROR COARSE DEC 27 08:00:00 Y EXAMPLE.258.803059147DATAFILE MIRROR COARSE DEC 27 08:00:00 Y SYSAUX.261.803059163DATAFILE MIRROR COARSE DEC 27 08:00:00 Y SYSTEM.257.803059147DATAFILE MIRROR COARSE DEC 27 08:00:00 Y UNDOTBS1.259.803059147DATAFILE MIRROR COARSE DEC 27 08:00:00 Y UNDOTBS2.262.803059163DATAFILE MIRROR COARSE DEC 27 08:00:00 Y UNDOTBS3.260.803059149DATAFILE MIRROR COARSE DEC 27 08:00:00 Y USERS.263.803059163DATAFILE MIRROR COARSE DEC 27 10:00:00 Y ZHONGWC.279.803125553
SQL> select name from v$datafile;NAME--------------------------------------------------------------------------------+DATADG/standby/datafile/system.257.803059147+DATADG/standby/datafile/undotbs1.259.803059147+DATADG/standby/datafile/sysaux.261.803059163+DATADG/standby/datafile/users.263.803059163+DATADG/standby/datafile/example.258.803059147+DATADG/standby/datafile/undotbs2.262.803059163+DATADG/standby/datafile/undotbs3.260.803059149+DATADG/standby/datafile/zhongwc.279.8031255538 rows selected.备库alert.log
Media Recovery Log /u01/app/oracle/arch/1_55_802540708.dbfMedia Recovery Waiting for thread 1 sequence 56 (in transit)Thu Dec 27 10:17:01 CST 2012Recovery of Online Redo Log: Thread 1 Group 11 Seq 56 Reading mem 0 Mem# 0: +DATADG/standby/onlinelog/group_11.264.803059801Thu Dec 27 10:25:53 CST 2012Successfully added datafile 8 to media recoveryDatafile #8: '+DATADG/standby/datafile/zhongwc.279.803125553'
在主库创建表,insert几条数据
SQL> conn hr/Enter password: Connected.SQL> create table t_zhongwc (tid number(3) primary key,tname varchar2(20)) tablespace zhongwc;Table created.SQL> insert into t_zhongwc values(1,'rac to single dg');1 row created.SQL> insert into t_zhongwc values(2,'zhongwc');1 row created.SQL> commit;Commit complete.SQL>
备库停止日志应用,打开数据库,可以看见数据已经同步过去
[oracle@standby arch]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.5.0 - Production on Thu Dec 27 10:31:50 2012Copyright (c) 1982, 2010, Oracle. All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> alter database recover managed standby database cancel;Database altered.SQL> alter database open;Database altered.SQL> select open_mode from v$database;OPEN_MODE----------READ ONLYSQL> conn hr/Enter password: Connected.SQL> desc t_zhongwc Name Null? Type ----------------------------------------- -------- ---------------------------- TID NOT NULL NUMBER(3) TNAME VARCHAR2(20)SQL> select * from t_zhongwc 2 / TID TNAME---------- -------------------- 1 rac to single dg 2 zhongwc
测试在主库删除zhongwc表空间
[oracle@racnode1 ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.5.0 - Production on Thu Dec 27 10:43:07 2012Copyright (c) 1982, 2010, Oracle. All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP, Data Miningand Real Application Testing optionsSQL> drop tablespace zhongwc including contents and datafiles;Tablespace dropped.
备库上查看,已经同步
[oracle@standby arch]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.5.0 - Production on Thu Dec 27 10:44:47 2012Copyright (c) 1982, 2010, Oracle. All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select name from v$datafile;NAME--------------------------------------------------------------------------------+DATADG/standby/datafile/system.257.803059147+DATADG/standby/datafile/undotbs1.259.803059147+DATADG/standby/datafile/sysaux.261.803059163+DATADG/standby/datafile/users.263.803059163+DATADG/standby/datafile/example.258.803059147+DATADG/standby/datafile/undotbs2.262.803059163+DATADG/standby/datafile/undotbs3.260.8030591497 rows selected.
ASMCMD [+datadg/standby/datafile] > ls -lType Redund Striped Time Sys NameDATAFILE MIRROR COARSE DEC 27 10:00:00 Y EXAMPLE.258.803059147DATAFILE MIRROR COARSE DEC 27 10:00:00 Y SYSAUX.261.803059163DATAFILE MIRROR COARSE DEC 27 10:00:00 Y SYSTEM.257.803059147DATAFILE MIRROR COARSE DEC 27 10:00:00 Y UNDOTBS1.259.803059147DATAFILE MIRROR COARSE DEC 27 10:00:00 Y UNDOTBS2.262.803059163DATAFILE MIRROR COARSE DEC 27 10:00:00 Y UNDOTBS3.260.803059149DATAFILE MIRROR COARSE DEC 27 10:00:00 Y USERS.263.803059163备库alert.log
Recovery of Online Redo Log: Thread 1 Group 11 Seq 61 Reading mem 0 Mem# 0: +DATADG/standby/onlinelog/group_11.264.803059801Thu Dec 27 10:43:49 CST 2012Recovery deleting file #8:'+DATADG/standby/datafile/zhongwc.279.803125553' from controlfile.Deleted Oracle managed file +DATADG/standby/datafile/zhongwc.279.803125553Recovery dropped tablespace 'ZHONGWC'
到此为止Configuring Oracle 10gR2 (10.2.0.5) 3-Nodes RAC to Single Dataguard搭建测试完毕
- Step By Step Configuring Oracle 10gR2 (10.2.0.5) 3-Nodes RAC to Single Dataguard
- Step By Step of Configuring Oracle 11gR2 (11.2.0.1) RAC to RAC Dataguard:
- Oracle 10gR2 (10.2.0.5) 3-Nodes RAC to Single Dataguard Switchover
- Step by Step oracle database 10gR2 upgrade to 11.2.0.2
- Oracle dataguard配置Step by Step
- Configuring Oracle 11.2.0.3 3-Nodes RAC Primary to 2-Nodes RAC Physical Standby DataGuard on OL
- 11gR2 RAC to Single DataGuard
- Dataguard配置Step by Step
- step by step install Oracle RAC on AIX
- oracle 10g stream step by step
- step by step 清理安装失败的oracle 10g RAC环境
- rac oracle single nodes
- STEP BY STEP RMAN DUPLICATE Database From RAC ASM To RAC ASM (Doc ID 1913937.1)
- Git Step By Step - Step 3: Branching
- mvn step by step 3
- Step by step to create a jQuery tabs plugin - 3
- Step by Step Configuring Forms Based Authentication in SharePoint 2013
- Step by Step of Adding Node to 11g RAC cluster on Linux
- 【Python】字符串处理
- ［学习记录］多媒体音量控制setVolumeControlStream(int streamType)
- FM1702 开始学习
- Spring如何加载XSD文件(org.xml.sax.SAXParseException: Failed to read schema document错误的解决方法)
- Compile、Make和Build的区别
- Step By Step Configuring Oracle 10gR2 (10.2.0.5) 3-Nodes RAC to Single Dataguard
- jQuery 效果函数
- Qt QTextBrowser的一个小例子
- jquery serialize方法中文乱码及解决办法
- C语言调用Lua中的文件路径问题
- 公司职位的英文缩写
- C# WinForm控件、自定义控件整理（大全）
- 构造函数 ，普通函数
- 经典C++开源项目
