Step By Step Configuring Oracle 10gR2 (10.2.0.5) 3-Nodes RAC to Single Dataguard

最新推荐文章于 2017-01-09 17:17:00 发布

ZhongWeicheng

最新推荐文章于 2017-01-09 17:17:00 发布

阅读量2.8k

点赞数 2

本文链接：https://blog.csdn.net/staricqxyz/article/details/8438014

版权

Oracle 同时被 3 个专栏收录

165 篇文章 0 订阅

订阅专栏

Oracle RAC

47 篇文章 0 订阅

订阅专栏

Oracle Dataguard

12 篇文章 0 订阅

订阅专栏

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	   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搭建测试完毕

ZhongWeicheng

关注

2
点赞
踩
1

收藏

觉得还不错? 一键收藏
1
评论
Step By Step Configuring Oracle 10gR2 (10.2.0.5) 3-Nodes RAC to Single Dataguard

Operating System:Oracle Linux 5.7 x86_64Oracle version:10.2.0.5RAC Primary database192.168.1.51 racnode1192.168.1.151 racnode1-vip172.168.1.51 racnode1-
复制链接

扫一扫