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 STATUS ACTIVE_ST INSTANCE_ROLE DATABASE_STATUS
---------------- ----------- ----------------- ------------------- ------------ --------- ------------------ -----------------
racdb2 racnode2 10.2.0.5.0 2012-12-25 16:08:08 OPEN NORMAL PRIMARY_INSTANCE ACTIVE
racdb1 racnode1 10.2.0.5.0 2012-12-25 16:08:07 OPEN NORMAL PRIMARY_INSTANCE ACTIVE
racdb3 racnode3 10.2.0.5.0 2012-12-25 16:08:08 OPEN NORMAL PRIMARY_INSTANCE ACTIVE
SQL> 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 racdb
SQL> 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.802972269
7 rows selected.
SQL> select name from v$controlfile;
NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------
+DATADG/racdb/controlfile/current.260.802540703
+FLASHDG/racdb/controlfile/current.256.802540705
SQL> 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.802888337
MEMBER
----------------------------------------------------------------------------------------------------------------------------------------------------------------
+FLASHDG/racdb/onlinelog/group_6.280.802888343
12 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:26
6 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 -ls
Name 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/java
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORACLE_PATH=$ORACLE_BASE/common/oracle/sql:.:$ORACLE_HOME/rdbms/admin
export CV_JDKHOME=/usr/local/java
export ORACLE_SID=standby
export PATH=.:${JAVA_HOME}/bin:${PATH}:$HOME/bin:$ORACLE_HOME/bin:$ORA_CRS_HOME/bin
export PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
export PATH=${PATH}:$ORACLE_BASE/common/oracle/bin
export ORACLE_TERM=xterm
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORA_NLS10=$ORACLE_HOME/nls/data
export NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"
export DISPLAY=192.168.2.224:0.0
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
export CLASSPATH=$ORACLE_HOME/JRE
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib
export THREADS_FLAG=native
export TEMP=/tmp
export 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 start
LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 26-DEC-2012 14:02:11
Copyright (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 - Production
System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standby)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=standby)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.5.0 - Production
Start Date 26-DEC-2012 14:02:11
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening 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-diskb
brw-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 initialized
Adding 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.
standby
CSS is active on all nodes.
Oracle CSS service is installed and running under init(1M)
[root@standby ~]# ps -ef|grep ASM
oracle 6505 1 0 14:32 ? 00:00:00 asm_pmon_+ASM
oracle 6507 1 0 14:32 ? 00:00:00 asm_psp0_+ASM
oracle 6509 1 0 14:32 ? 00:00:00 asm_mman_+ASM
oracle 6511 1 0 14:32 ? 00:00:00 asm_dbw0_+ASM
oracle 6513 1 0 14:32 ? 00:00:00 asm_lgwr_+ASM
oracle 6515 1 0 14:32 ? 00:00:00 asm_ckpt_+ASM
oracle 6517 1 0 14:32 ? 00:00:00 asm_smon_+ASM
oracle 6519 1 0 14:32 ? 00:00:00 asm_rbal_+ASM
oracle 6521 1 0 14:32 ? 00:00:00 asm_gmon_+ASM
oracle 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 sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Dec 26 14:37:19 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter asm
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
asm_diskgroups string
DATADG
asm_diskstring string
/dev/asm*
asm_power_limit integer
1
SQL> show parameter spfile
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
spfile string
/u01/app/oracle/product/10.2.0
/db_1/dbs/spfile+ASM.ora
SQL> select path from v$asm_disk;
PATH
--------------------------------------------------------------------------------
/dev/asm-diskb
/dev/asm-diskc
SQL>
配置主库本地归档路径
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 2012
Copyright (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 catalog
old 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 stored
old 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 stored
old 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 stored
Starting backup at 26-DEC-2012 14:50:36
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=843 instance=racdb1 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=848 instance=racdb2 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=844 instance=racdb3 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=23 recid=45 stamp=803055040
channel ORA_DISK_1: starting piece 1 at 26-DEC-2012 14:50:50
channel ORA_DISK_2: starting archive log backupset
channel ORA_DISK_2: specifying archive log(s) in backup set
input archive log thread=2 sequence=14 recid=44 stamp=803055040
channel ORA_DISK_2: starting piece 1 at 26-DEC-2012 14:50:51
channel ORA_DISK_3: starting archive log backupset
channel ORA_DISK_3: specifying archive log(s) in backup set
input archive log thread=3 sequence=7 recid=43 stamp=803055038
channel ORA_DISK_3: starting piece 1 at 26-DEC-2012 14:50:51
channel ORA_DISK_1: finished piece 1 at 26-DEC-2012 14:50:54
piece handle=+FLASHDG/racdb/backupset/2012_12_26/annnf0_tag20121226t145048_0.283.803055051 tag=TAG20121226T145048 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:05
channel ORA_DISK_2: finished piece 1 at 26-DEC-2012 14:50:54
piece handle=+FLASHDG/racdb/backupset/2012_12_26/annnf0_tag20121226t145048_0.278.803055051 tag=TAG20121226T145048 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:05
channel ORA_DISK_3: finished piece 1 at 26-DEC-2012 14:50:54
piece handle=+FLASHDG/racdb/backupset/2012_12_26/annnf0_tag20121226t145048_0.277.803055051 tag=TAG20121226T145048 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:05
Finished backup at 26-DEC-2012 14:50:54
Starting backup at 26-DEC-2012 14:50:54
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=+DATADG/racdb/datafile/system.269.802972261
channel ORA_DISK_1: starting piece 1 at 26-DEC-2012 14:50:55
channel ORA_DISK_2: starting full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
input datafile fno=00003 name=+DATADG/racdb/datafile/sysaux.265.802972263
input datafile fno=00006 name=+DATADG/racdb/datafile/undotbs2.259.802972265
input datafile fno=00004 name=+DATADG/racdb/datafile/users.257.802972267
channel ORA_DISK_2: starting piece 1 at 26-DEC-2012 14:50:56
channel ORA_DISK_3: starting full datafile backupset
channel ORA_DISK_3: specifying datafile(s) in backupset
input datafile fno=00005 name=+DATADG/racdb/datafile/example.258.802972265
input datafile fno=00002 name=+DATADG/racdb/datafile/undotbs1.256.802972267
input datafile fno=00007 name=+DATADG/racdb/datafile/undotbs3.264.802972269
channel ORA_DISK_3: starting piece 1 at 26-DEC-2012 14:51:05
channel ORA_DISK_1: finished piece 1 at 26-DEC-2012 14:51:36
piece handle=/u01/app/oracle/backup/RACDB_FULLBAK_20121226_0kntr9ef_s20_p1 tag=FULLBAK comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:41
channel ORA_DISK_2: finished piece 1 at 26-DEC-2012 14:51:44
piece handle=/u01/app/oracle/backup/RACDB_FULLBAK_20121226_0lntr9ef_s21_p1 tag=FULLBAK comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:49
channel ORA_DISK_3: finished piece 1 at 26-DEC-2012 14:51:44
piece handle=/u01/app/oracle/backup/RACDB_FULLBAK_20121226_0mntr9eg_s22_p1 tag=FULLBAK comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:48
Finished backup at 26-DEC-2012 14:51:43
Starting backup at 26-DEC-2012 14:51:44
current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=24 recid=47 stamp=803055105
channel ORA_DISK_1: starting piece 1 at 26-DEC-2012 14:51:49
channel ORA_DISK_3: starting archive log backupset
channel ORA_DISK_3: specifying archive log(s) in backup set
input archive log thread=3 sequence=8 recid=48 stamp=803055106
channel ORA_DISK_3: starting piece 1 at 26-DEC-2012 14:51:50
channel ORA_DISK_1: finished piece 1 at 26-DEC-2012 14:51:50
piece handle=+FLASHDG/racdb/backupset/2012_12_26/annnf0_tag20121226t145149_0.275.803055109 tag=TAG20121226T145149 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_3: finished piece 1 at 26-DEC-2012 14:51:51
piece handle=+FLASHDG/racdb/backupset/2012_12_26/annnf0_tag20121226t145149_0.273.803055111 tag=TAG20121226T145149 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=2 sequence=15 recid=46 stamp=803055105
channel ORA_DISK_1: starting piece 1 at 26-DEC-2012 14:51:52
channel ORA_DISK_1: finished piece 1 at 26-DEC-2012 14:51:53
piece handle=+FLASHDG/racdb/backupset/2012_12_26/annnf0_tag20121226t145149_0.272.803055113 tag=TAG20121226T145149 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 26-DEC-2012 14:51:53
Starting Control File and SPFILE Autobackup at 26-DEC-2012 14:51:53
piece handle=/u01/app/oracle/backup/c-800157471-20121226-00 comment=NONE
Finished Control File and SPFILE Autobackup at 26-DEC-2012 14:52:00
RMAN>
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:17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=848 instance=racdb1 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=845 instance=racdb2 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=847 instance=racdb3 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including standby control file in backupset
channel ORA_DISK_1: starting piece 1 at 26-DEC-2012 15:48:21
channel ORA_DISK_1: finished piece 1 at 26-DEC-2012 15:48:24
piece handle=/u01/app/oracle/backup/standby_ctl_0rntrcq3_1_1 tag=TAG20121226T154819 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:05
Finished 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 /nolog
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Dec 26 15:17:31 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
SQL> conn sys@racdb as sysdba
Enter password:
Connected.
SQL> conn sys@racdb1 as sysdba
Enter password:
Connected.
SQL> conn sys@racdb2 as sysdba
Enter password:
Connected.
SQL> conn sys@racdb3 as sysdba
Enter password:
Connected.
SQL> conn sys@zwc as sysdba
Enter password:
Connected.
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and 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 sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Dec 26 15:41:41 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> 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 sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Dec 26 15:53:19 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 599785472 bytes
Fixed Size 2098112 bytes
Variable Size 163580992 bytes
Database Buffers 427819008 bytes
Redo Buffers 6287360 bytes
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With 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 2012
Copyright (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:30
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=871 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
output filename=+DATADG/standby/controlfile/standby_ctl
Finished restore at 26-DEC-2012 15:53:40
restore standby database
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> 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: c1
channel c1: sid=871 devtype=DISK
allocated channel: c2
channel c2: sid=870 devtype=DISK
Starting restore at 26-DEC-2012 15:59:06
channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +DATADG/standby/datafile/system.269.802972261
channel c1: reading from backup piece /u01/app/oracle/backup/RACDB_FULLBAK_20121226_0kntr9ef_s20_p1
channel c2: starting datafile backupset restore
channel c2: specifying datafile(s) to restore from backup set
restoring datafile 00002 to +DATADG/standby/datafile/undotbs1.256.802972267
restoring datafile 00005 to +DATADG/standby/datafile/example.258.802972265
restoring datafile 00007 to +DATADG/standby/datafile/undotbs3.264.802972269
channel c2: reading from backup piece /u01/app/oracle/backup/RACDB_FULLBAK_20121226_0mntr9eg_s22_p1
channel c2: restored backup piece 1
piece handle=/u01/app/oracle/backup/RACDB_FULLBAK_20121226_0mntr9eg_s22_p1 tag=FULLBAK
channel c2: restore complete, elapsed time: 00:00:15
channel c2: starting datafile backupset restore
channel c2: specifying datafile(s) to restore from backup set
restoring datafile 00003 to +DATADG/standby/datafile/sysaux.265.802972263
restoring datafile 00004 to +DATADG/standby/datafile/users.257.802972267
restoring datafile 00006 to +DATADG/standby/datafile/undotbs2.259.802972265
channel c2: reading from backup piece /u01/app/oracle/backup/RACDB_FULLBAK_20121226_0lntr9ef_s21_p1
channel c1: restored backup piece 1
piece handle=/u01/app/oracle/backup/RACDB_FULLBAK_20121226_0kntr9ef_s20_p1 tag=FULLBAK
channel c1: restore complete, elapsed time: 00:00:23
channel c2: restored backup piece 1
piece handle=/u01/app/oracle/backup/RACDB_FULLBAK_20121226_0lntr9ef_s21_p1 tag=FULLBAK
channel c2: restore complete, elapsed time: 00:00:23
Finished restore at 26-DEC-2012 15:59:45
released channel: c1
released channel: c2
[oracle@standby ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Dec 26 16:04:46 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set linesize 200
SQL> 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:38
6 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 YES
12 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 1 0 52428800 512 YES UNASSIGNED 0 0
12 UNASSIGNED 1 0 52428800 512 YES UNASSIGNED 0 0
13 UNASSIGNED 1 0 52428800 512 YES UNASSIGNED 0 0
14 UNASSIGNED 2 0 52428800 512 YES UNASSIGNED 0 0
15 UNASSIGNED 2 0 52428800 512 YES UNASSIGNED 0 0
16 UNASSIGNED 2 0 52428800 512 YES UNASSIGNED 0 0
17 UNASSIGNED 3 0 52428800 512 YES UNASSIGNED 0 0
18 UNASSIGNED 3 0 52428800 512 YES UNASSIGNED 0 0
19 UNASSIGNED 3 0 52428800 512 YES UNASSIGNED 0 0
9 rows selected.
备库开启redo apply
[oracle@standby ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Dec 26 16:14:59 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> 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 selected
SQL> 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.802888343
ORA-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.802888337
ORA-15173: entry 'group_6.271.802888337' does not exist in directory 'onlinelog'
Deleted Oracle managed file +DATADG/standby/onlinelog/group_6.271.802888337
Deleted Oracle managed file +DATADG/standby/onlinelog/group_6.280.802888343
Clearing online redo logfile 6 complete
Media 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> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and 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_config
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string dg_config=(racdb,standby)
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string racdb
SQL>
备库
SQL> show parameter log_archive_config
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string dg_config=(racdb,standby)
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string standby
主库的alert.log
SUCCESS: diskgroup DATADG was mounted
SUCCESS: diskgroup FLASHDG was mounted
Thu Dec 27 08:57:15 CST 2012
Setting recovery target incarnation to 2
Thu Dec 27 08:57:15 CST 2012
Successful mount of redo thread 1, with mount id 800695506
Thu Dec 27 08:57:15 CST 2012
Database mounted in Shared Mode (CLUSTER_DATABASE=TRUE)
Completed: ALTER DATABASE MOUNT
Thu Dec 27 08:57:21 CST 2012
ALTER DATABASE OPEN
Picked broadcast on commit scheme to generate SCNs
Thu Dec 27 08:57:21 CST 2012
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=24, OS id=4436
Thu Dec 27 08:57:21 CST 2012
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=25, OS id=4438
LNSb started with pid=26, OS id=4440
Thu Dec 27 08:57:28 CST 2012
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
LNSb started with pid=26, OS id=4477
Thu Dec 27 08:57:32 CST 2012
LGWR: Standby redo logfile selected for thread 1 sequence 38 for destination LOG_ARCHIVE_DEST_2
Thu Dec 27 08:57:32 CST 2012
Thread 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.802540725
Successful open of redo thread 1
Thu Dec 27 08:57:32 CST 2012
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Dec 27 08:57:32 CST 2012
SMON: enabling cache recovery
Thu Dec 27 08:57:32 CST 2012
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
Thu Dec 27 08:57:32 CST 2012
ARC1: Becoming the heartbeat ARCH
Thu Dec 27 08:57:35 CST 2012
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
Thu Dec 27 08:57:43 CST 2012
Successfully onlined Undo Tablespace 1.
Thu Dec 27 08:57:43 CST 2012
SMON: enabling tx recovery
Thu Dec 27 08:57:43 CST 2012
Database Characterset is ZHS16GBK
Opening with internal Resource Manager plan
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=27, OS id=4663
Thu Dec 27 08:58:05 CST 2012
Completed: ALTER DATABASE OPEN
Thu Dec 27 08:58:14 CST 2012
ARC1: Standby redo logfile selected for thread 1 sequence 37 for destination LOG_ARCHIVE_DEST_2
Thu Dec 27 08:58:18 CST 2012
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
Thu Dec 27 08:59:20 CST 2012
ALTER SYSTEM SET service_names='zwc' SCOPE=MEMORY SID='racdb1';
Thu Dec 27 09:07:14 CST 2012
LGWR: Standby redo logfile selected for thread 1 sequence 39 for destination LOG_ARCHIVE_DEST_2
Thu Dec 27 09:07:14 CST 2012
Thread 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.802540715
Thu Dec 27 09:07:51 CST 2012
LGWR: Standby redo logfile selected for thread 1 sequence 40 for destination LOG_ARCHIVE_DEST_2
Thu Dec 27 09:07:51 CST 2012
Thread 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.802540725
Thu Dec 27 09:08:10 CST 2012
ALTER SYSTEM SET service_names='zwc','racdb' SCOPE=MEMORY SID='racdb1';
Thu Dec 27 09:08:36 CST 2012
Thread 1 cannot allocate new log, sequence 41
Checkpoint 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.802540725
LGWR: Standby redo logfile selected for thread 1 sequence 41 for destination LOG_ARCHIVE_DEST_2
Thu Dec 27 09:08:40 CST 2012
Thread 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.802540715
Thu Dec 27 09:12:24 CST 2012
Thread 1 cannot allocate new log, sequence 42
Checkpoint 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.802540715
LGWR: Standby redo logfile selected for thread 1 sequence 42 for destination LOG_ARCHIVE_DEST_2
Thu Dec 27 09:12:28 CST 2012
Thread 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.802540725
Thu Dec 27 09:13:05 CST 2012
LGWR: Standby redo logfile selected for thread 1 sequence 43 for destination LOG_ARCHIVE_DEST_2
Thu Dec 27 09:13:05 CST 2012
Thread 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 mounted
Thu Dec 27 08:48:50 CST 2012
Successful mount of redo thread 1, with mount id 800697579
Thu Dec 27 08:48:50 CST 2012
Physical Standby Database mounted.
Completed: alter database mount standby database
Thu Dec 27 08:49:09 CST 2012
alter database recover managed standby database using current logfile disconnect from session
Thu Dec 27 08:49:09 CST 2012
Attempt to start background Managed Standby Recovery process (standby)
MRP0 started with pid=19, OS id=15411
Thu Dec 27 08:49:09 CST 2012
MRP0: Background Managed Standby Recovery process started (standby)
Managed Standby Recovery starting Real Time Apply
parallel recovery started with 2 processes
Thu Dec 27 08:49:14 CST 2012
Waiting for all non-current ORLs to be archived...
Media Recovery Waiting for thread 1 sequence 24
Thu Dec 27 08:49:15 CST 2012
Completed: alter database recover managed standby database using current logfile disconnect from session
Thu Dec 27 08:57:03 CST 2012
Using STANDBY_ARCHIVE_DEST parameter default value as /u01/app/oracle/arch
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[1]: Assigned to RFS process 15448
RFS[1]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Primary 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 2012
RFS LogMiner: Client disabled from further notification
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[2]: Assigned to RFS process 15450
RFS[2]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Successfully opened standby log 17: '+DATADG/standby/onlinelog/group_17.270.803059869'
Thu Dec 27 08:57:15 CST 2012
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[3]: Assigned to RFS process 15454
RFS[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 2012
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[4]: Assigned to RFS process 15456
RFS[4]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[5]: Assigned to RFS process 15458
RFS[5]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Primary database is in MAXIMUM PERFORMANCE mode
RFS[5]: Successfully opened standby log 11: '+DATADG/standby/onlinelog/group_11.264.803059801'
Thu Dec 27 08:57:35 CST 2012
Fetching gap sequence in thread 1, gap sequence 24-36
Thu Dec 27 08:57:35 CST 2012
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[6]: Assigned to RFS process 15460
RFS[6]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[7]: Assigned to RFS process 15462
RFS[7]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Primary database is in MAXIMUM PERFORMANCE mode
RFS[7]: Successfully opened standby log 14: '+DATADG/standby/onlinelog/group_14.267.803059831'
Thu Dec 27 08:57:47 CST 2012
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[8]: Assigned to RFS process 15464
RFS[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 2012
RFS[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 2012
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[9]: Assigned to RFS process 15468
RFS[9]: Identified database type as 'physical standby'
Thu Dec 27 08:58:06 CST 2012
RFS[8]: Archived Log: '/u01/app/oracle/arch/1_32_802540708.dbf'
Thu Dec 27 08:58:06 CST 2012
RFS[9]: Successfully opened standby log 15: '+DATADG/standby/onlinelog/group_15.268.803059833'
Thu Dec 27 08:58:07 CST 2012
RFS[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 2012
Media Recovery Log /u01/app/oracle/arch/1_24_802540708.dbf
Media Recovery Waiting for thread 2 sequence 15
Fetching gap sequence in thread 2, gap sequence 15-18
Thu Dec 27 08:58:19 CST 2012
RFS[8]: Archived Log: '/u01/app/oracle/arch/2_15_802540708.dbf'
Thu Dec 27 08:58:33 CST 2012
RFS[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 2012
Media Recovery Log /u01/app/oracle/arch/2_15_802540708.dbf
Media Recovery Waiting for thread 3 sequence 8
Fetching gap sequence in thread 3, gap sequence 8-12
Thu Dec 27 08:59:06 CST 2012
RFS[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 2012
Media Recovery Log /u01/app/oracle/arch/3_8_802540708.dbf
Media Recovery Log /u01/app/oracle/arch/1_25_802540708.dbf
Media Recovery Log /u01/app/oracle/arch/2_16_802540708.dbf
Media Recovery Log /u01/app/oracle/arch/3_9_802540708.dbf
Media Recovery Log /u01/app/oracle/arch/1_26_802540708.dbf
Media Recovery Log /u01/app/oracle/arch/1_27_802540708.dbf
Media Recovery Log /u01/app/oracle/arch/1_28_802540708.dbf
Media Recovery Log /u01/app/oracle/arch/3_10_802540708.dbf
Media Recovery Log /u01/app/oracle/arch/1_29_802540708.dbf
Media Recovery Log /u01/app/oracle/arch/2_17_802540708.dbf
Media Recovery Log /u01/app/oracle/arch/1_30_802540708.dbf
Media Recovery Log /u01/app/oracle/arch/1_31_802540708.dbf
Media Recovery Log /u01/app/oracle/arch/3_11_802540708.dbf
Media Recovery Log /u01/app/oracle/arch/1_32_802540708.dbf
Media Recovery Log /u01/app/oracle/arch/2_18_802540708.dbf
Media Recovery Log /u01/app/oracle/arch/1_33_802540708.dbf
Media Recovery Log /u01/app/oracle/arch/1_34_802540708.dbf
Media Recovery Log /u01/app/oracle/arch/3_12_802540708.dbf
Thu Dec 27 08:59:30 CST 2012
Media Recovery Log /u01/app/oracle/arch/1_35_802540708.dbf
Media Recovery Log /u01/app/oracle/arch/2_19_802540708.dbf
Media Recovery Log /u01/app/oracle/arch/1_36_802540708.dbf
Thu Dec 27 09:00:02 CST 2012
Media Recovery Log /u01/app/oracle/arch/1_37_802540708.dbf
Media Recovery Log /u01/app/oracle/arch/3_13_802540708.dbf
Media Recovery Waiting for thread 3 sequence 14 (in transit)
Thu Dec 27 09:00:04 CST 2012
Recovery of Online Redo Log: Thread 3 Group 17 Seq 14 Reading mem 0
Mem# 0: +DATADG/standby/onlinelog/group_17.270.803059869
Media Recovery Waiting for thread 1 sequence 38 (in transit)
Thu Dec 27 09:00:04 CST 2012
Recovery of Online Redo Log: Thread 1 Group 11 Seq 38 Reading mem 0
Mem# 0: +DATADG/standby/onlinelog/group_11.264.803059801
Media Recovery Waiting for thread 2 sequence 20 (in transit)
Thu Dec 27 09:00:04 CST 2012
Recovery of Online Redo Log: Thread 2 Group 14 Seq 20 Reading mem 0
Mem# 0: +DATADG/standby/onlinelog/group_14.267.803059831
Thu Dec 27 09:07:14 CST 2012
Primary database is in MAXIMUM PERFORMANCE mode
RFS[5]: Successfully opened standby log 11: '+DATADG/standby/onlinelog/group_11.264.803059801'
Thu Dec 27 09:07:15 CST 2012
Media Recovery Log /u01/app/oracle/arch/1_38_802540708.dbf
Thu Dec 27 09:07:26 CST 2012
Media Recovery Waiting for thread 1 sequence 39 (in transit)
Thu Dec 27 09:07:26 CST 2012
Recovery of Online Redo Log: Thread 1 Group 11 Seq 39 Reading mem 0
Mem# 0: +DATADG/standby/onlinelog/group_11.264.803059801
Thu Dec 27 09:07:51 CST 2012
Primary database is in MAXIMUM PERFORMANCE mode
RFS[5]: Successfully opened standby log 11: '+DATADG/standby/onlinelog/group_11.264.803059801'
Thu Dec 27 09:07:52 CST 2012
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Successfully opened standby log 17: '+DATADG/standby/onlinelog/group_17.270.803059869'
Thu Dec 27 09:07:55 CST 2012
Media Recovery Log /u01/app/oracle/arch/3_14_802540708.dbf
Media Recovery Log /u01/app/oracle/arch/1_39_802540708.dbf
Media Recovery Waiting for thread 1 sequence 40 (in transit)
Thu Dec 27 09:07:58 CST 2012
Recovery of Online Redo Log: Thread 1 Group 11 Seq 40 Reading mem 0
Mem# 0: +DATADG/standby/onlinelog/group_11.264.803059801
Media Recovery Waiting for thread 3 sequence 15 (in transit)
Thu Dec 27 09:07:58 CST 2012
Recovery of Online Redo Log: Thread 3 Group 17 Seq 15 Reading mem 0
Mem# 0: +DATADG/standby/onlinelog/group_17.270.803059869
Thu Dec 27 09:08:40 CST 2012
Primary database is in MAXIMUM PERFORMANCE mode
RFS[5]: Successfully opened standby log 11: '+DATADG/standby/onlinelog/group_11.264.803059801'
Thu Dec 27 09:08:41 CST 2012
Media Recovery Log /u01/app/oracle/arch/1_40_802540708.dbf
Thu Dec 27 09:08:43 CST 2012
Primary database is in MAXIMUM PERFORMANCE mode
RFS[7]: Successfully opened standby log 14: '+DATADG/standby/onlinelog/group_14.267.803059831'
Thu Dec 27 09:08:44 CST 2012
Media Recovery Log /u01/app/oracle/arch/2_20_802540708.dbf
Media Recovery Waiting for thread 1 sequence 41 (in transit)
Thu Dec 27 09:08:53 CST 2012
Recovery of Online Redo Log: Thread 1 Group 11 Seq 41 Reading mem 0
Mem# 0: +DATADG/standby/onlinelog/group_11.264.803059801
Media Recovery Waiting for thread 2 sequence 21 (in transit)
Thu Dec 27 09:08:59 CST 2012
Recovery of Online Redo Log: Thread 2 Group 14 Seq 21 Reading mem 0
Mem# 0: +DATADG/standby/onlinelog/group_14.267.803059831
Thu Dec 27 09:12:29 CST 2012
Primary database is in MAXIMUM PERFORMANCE mode
RFS[5]: Successfully opened standby log 11: '+DATADG/standby/onlinelog/group_11.264.803059801'
Thu Dec 27 09:12:30 CST 2012
Media Recovery Log /u01/app/oracle/arch/1_41_802540708.dbf
Thu Dec 27 09:12:42 CST 2012
Media Recovery Waiting for thread 1 sequence 42 (in transit)
Thu Dec 27 09:12:42 CST 2012
Recovery of Online Redo Log: Thread 1 Group 11 Seq 42 Reading mem 0
Mem# 0: +DATADG/standby/onlinelog/group_11.264.803059801
Thu Dec 27 09:13:06 CST 2012
Primary database is in MAXIMUM PERFORMANCE mode
RFS[5]: Successfully opened standby log 11: '+DATADG/standby/onlinelog/group_11.264.803059801'
Thu Dec 27 09:13:08 CST 2012
Media Recovery Log /u01/app/oracle/arch/1_42_802540708.dbf
Thu Dec 27 09:13:08 CST 2012
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Successfully opened standby log 17: '+DATADG/standby/onlinelog/group_17.270.803059869'
Thu Dec 27 09:13:08 CST 2012
Media Recovery Log /u01/app/oracle/arch/3_15_802540708.dbf
Media Recovery Waiting for thread 1 sequence 43 (in transit)
Thu Dec 27 09:13:11 CST 2012
Recovery of Online Redo Log: Thread 1 Group 11 Seq 43 Reading mem 0
Mem# 0: +DATADG/standby/onlinelog/group_11.264.803059801
Thu Dec 27 09:13:23 CST 2012
Media Recovery Waiting for thread 3 sequence 16 (in transit)
Thu Dec 27 09:13:23 CST 2012
Recovery 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 YES
12 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:08
6 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 YES
10 STANDBY +DATADG/racdb/onlinelog/group_10.275.803122477 NO
10 STANDBY +FLASHDG/racdb/onlinelog/group_10.282.803122483 YES
11 STANDBY +DATADG/racdb/onlinelog/group_11.276.803122487 NO
11 STANDBY +FLASHDG/racdb/onlinelog/group_11.281.803122493 YES
12 STANDBY +DATADG/racdb/onlinelog/group_12.277.803122497 NO
12 STANDBY +FLASHDG/racdb/onlinelog/group_12.276.803122501 YES
13 STANDBY +DATADG/racdb/onlinelog/group_13.278.803122527 NO
13 STANDBY +FLASHDG/racdb/onlinelog/group_13.274.803122531 YES
14 STANDBY +DATADG/racdb/onlinelog/group_14.279.803122537 NO
14 STANDBY +FLASHDG/racdb/onlinelog/group_14.270.803122541 YES
15 STANDBY +DATADG/racdb/onlinelog/group_15.280.803122545 NO
15 STANDBY +FLASHDG/racdb/onlinelog/group_15.269.803122551 YES
30 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:08
6 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 1 0 52428800 512 YES UNASSIGNED 0 0
8 UNASSIGNED 1 0 52428800 512 YES UNASSIGNED 0 0
9 UNASSIGNED 1 0 52428800 512 YES UNASSIGNED 0 0
10 UNASSIGNED 2 0 52428800 512 YES UNASSIGNED 0 0
11 UNASSIGNED 2 0 52428800 512 YES UNASSIGNED 0 0
12 UNASSIGNED 2 0 52428800 512 YES UNASSIGNED 0 0
13 UNASSIGNED 3 0 52428800 512 YES UNASSIGNED 0 0
14 UNASSIGNED 3 0 52428800 512 YES UNASSIGNED 0 0
15 UNASSIGNED 3 0 52428800 512 YES UNASSIGNED 0 0
9 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 option
SQL> 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 sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Thu Dec 27 10:24:14 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> create tablespace zhongwc datafile size 10M autoextend on;
Tablespace created.
备库查看是否同步
ASMCMD [+datadg/standby/datafile] > ls -l
Type Redund Striped Time Sys Name
DATAFILE MIRROR COARSE DEC 27 08:00:00 Y EXAMPLE.258.803059147
DATAFILE MIRROR COARSE DEC 27 08:00:00 Y SYSAUX.261.803059163
DATAFILE MIRROR COARSE DEC 27 08:00:00 Y SYSTEM.257.803059147
DATAFILE MIRROR COARSE DEC 27 08:00:00 Y UNDOTBS1.259.803059147
DATAFILE MIRROR COARSE DEC 27 08:00:00 Y UNDOTBS2.262.803059163
DATAFILE MIRROR COARSE DEC 27 08:00:00 Y UNDOTBS3.260.803059149
DATAFILE MIRROR COARSE DEC 27 08:00:00 Y USERS.263.803059163
DATAFILE 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.803125553
8 rows selected.
备库alert.log
Media Recovery Log /u01/app/oracle/arch/1_55_802540708.dbf
Media Recovery Waiting for thread 1 sequence 56 (in transit)
Thu Dec 27 10:17:01 CST 2012
Recovery of Online Redo Log: Thread 1 Group 11 Seq 56 Reading mem 0
Mem# 0: +DATADG/standby/onlinelog/group_11.264.803059801
Thu Dec 27 10:25:53 CST 2012
Successfully added datafile 8 to media recovery
Datafile #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 sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Thu Dec 27 10:31:50 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> 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 ONLY
SQL> 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 sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Thu Dec 27 10:43:07 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> drop tablespace zhongwc including contents and datafiles;
Tablespace dropped.
备库上查看,已经同步
[oracle@standby arch]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Thu Dec 27 10:44:47 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
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
7 rows selected.
ASMCMD [+datadg/standby/datafile] > ls -l
Type Redund Striped Time Sys Name
DATAFILE MIRROR COARSE DEC 27 10:00:00 Y EXAMPLE.258.803059147
DATAFILE MIRROR COARSE DEC 27 10:00:00 Y SYSAUX.261.803059163
DATAFILE MIRROR COARSE DEC 27 10:00:00 Y SYSTEM.257.803059147
DATAFILE MIRROR COARSE DEC 27 10:00:00 Y UNDOTBS1.259.803059147
DATAFILE MIRROR COARSE DEC 27 10:00:00 Y UNDOTBS2.262.803059163
DATAFILE MIRROR COARSE DEC 27 10:00:00 Y UNDOTBS3.260.803059149
DATAFILE 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.803059801
Thu Dec 27 10:43:49 CST 2012
Recovery deleting file #8:'+DATADG/standby/datafile/zhongwc.279.803125553' from controlfile.
Deleted Oracle managed file +DATADG/standby/datafile/zhongwc.279.803125553
Recovery dropped tablespace 'ZHONGWC'
到此为止Configuring Oracle 10gR2 (10.2.0.5) 3-Nodes RAC to Single Dataguard搭建测试完毕