Configure Oracle Dataguard Primary-ASM to Physical-ASM

  前面整理过Oracle物理备库的配置文档，数据文件是存储在文件系统上的，在生产环境中的DBA，往往都是面对几十上百G的数据，甚至可能是T级别的，文件系统存储数据文件在这些场合下的I/O问题就会逐渐暴露，因而在生产环境中的数据存储一般都使用ASM，或者裸设备，oracle11g开始就不支持裸设备存储数据了，因而本讲主要介绍在ASM环境下配置Dataguard物理备库！

环境介绍：
主库IP：192.168.227.20/24
主库SID: orcl
主库DB_NAME：orcl
主库DB_UNIQUE_NAME:primary
主库SERVICES_NAME: primary.yang.com

备库IP：192.168.227.30/24
备库SID: orcl
备库DB_NAME：orcl
备库DB_UNIQUE_NAME:physical
备库SERVICES_NAME: physical.yang.com

一：主库准备工作

 

1：配置ASM环境 [root@primary ~]#/u01/app/oracle/product/10.2.0/db_1/bin/localconfig add [root@primary ~]# ps -ef |grep css root     4180    1  0 13:07?       00:00:00 /bin/su -l oracle -c sh -c 'cd/u01/app/oracle/product/10.2.0/db_1/log/primary/cssd; ulimit -c unlimited; exec/u01/app/oracle/product/10.2.0/db_1/bin/ocssd ' oracle   4332  4180  0 13:08?       00:00:00/u01/app/oracle/product/10.2.0/db_1/bin/ocssd.bin root     4682  4265  0 13:17pts/1   00:00:00 grep css [root@primary ~]#cd /u01/app/oracle/product/10.2.0/db_1/bin/ [root@primary bin]# ./crsctl check crs CSS appears healthy Cannot communicate with CRS Cannot communicate with EVM [oracle@primary~]# ps -ef |grep -i asm oracle   4459    1  0 13:10?       00:00:00 asm_pmon_+ASM oracle   4461    1  0 13:10?       00:00:00 asm_psp0_+ASM oracle   4463    1  0 13:10?       00:00:00 asm_mman_+ASM oracle   4465    1  0 13:10?       00:00:00 asm_dbw0_+ASM oracle   4467    1  0 13:10?       00:00:00 asm_lgwr_+ASM oracle   4469    1  0 13:10?       00:00:00 asm_ckpt_+ASM oracle   4471    1  0 13:10?       00:00:00 asm_smon_+ASM oracle   4473    1  0 13:10?       00:00:00 asm_rbal_+ASM oracle   4475    1  0 13:10?       00:00:00 asm_gmon_+ASM oracle   4665  5702  0 13:16pts/2   00:00:00 grep -i asm [oracle@primary~]$ export ORACLE_SID=+ASM [oracle@primary ~]$ sqlplus /nolog SQL> conn /as sysdba SQL> select instance_name,status  fromv$instance; INSTANCE_NSTATUS ---------- ------------------------------------ +ASM      STARTED SQL> creatediskgroup data normal redundancy   2  failgroup fg1 disk   3  '/dev/raw/raw1' nameasmdisk1,   4  '/dev/raw/raw2' nameasmdisk2   5  failgroup fg2 disk   6  '/dev/raw/raw3' nameasmdisk3,   7  '/dev/raw/raw4' nameasmdisk4; Diskgroup created. SQL> creatediskgroup fra  external redundancy   2  disk '/dev/raw/raw5' nameasmdisk5; Diskgroup created. SQL> selectname,path,failgroup from v$asm_disk; NAME      PATH                                    FAILGROUP ---------- ------------------------------------------------------------ ASMDISK5  /dev/raw/raw5                           ASMDISK5 ASMDISK4  /dev/raw/raw4                           FG2 ASMDISK3  /dev/raw/raw3                           FG2 ASMDISK2  /dev/raw/raw2                           FG1 ASMDISK1  /dev/raw/raw1                           FG1 SQL> selectname,total_mb,free_mb,usable_file_mb fromv$asm_diskgroup; NAME        TOTAL_MB   FREE_MB USABLE_FILE_MB ---------- ---------- ---------- -------------- DATA           81920     81814         30667 FRA            58368     58318         58318 2：建库 [oracle@primary ~]$ env |grep ORA ORACLE_SID=orcl ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1  SQL> select file_name fromdba_data_files; FILE_NAME ------------------------------------------ +DATA/orcl/datafile/users.259.765985893 +DATA/orcl/datafile/sysaux.257.765985893 +DATA/orcl/datafile/undotbs1.258.765985893 +DATA/orcl/datafile/system.256.765985891 +DATA/orcl/datafile/example.265.765986057 SQL> select member fromv$logfile; MEMBER -------------------------------------------- +DATA/orcl/onlinelog/group_3.263.765986013 +FRA/orcl/onlinelog/group_3.259.765986017 +DATA/orcl/onlinelog/group_2.262.765986005 +FRA/orcl/onlinelog/group_2.258.765986009 +DATA/orcl/onlinelog/group_1.261.765985997 +FRA/orcl/onlinelog/group_1.257.765986003 SQL> show parameterspfile; NAME                                TYPE       VALUE ------------------------------------ ----------------------------------------- spfile                              string     +DATA/orcl/spfileorcl.ora SQL> show parameter control; NAME                                TYPE       VALUE ------------------------------------ ----------------------------------------- control_file_record_keep_time       integer    7 control_files                       string     +DATA/orcl/controlfile/current                                                 .260.765985991, +FRA/orcl/cont                                                 rolfile/current.256.765985991 SQL> archive log list; Database logmode             Archive Mode Automaticarchival            Enabled Archivedestination           +FRA/orcl Oldest online logsequence    3 Next log sequence to archive  5 Current logsequence          5 SQL> show parameter db_recovery_file_dest; NAME                                TYPE       VALUE ------------------------------------ -------------------- db_recovery_file_dest               string     +FRA db_recovery_file_dest_size          big integer 50000M 3：添加standby日志组 SQL> alter database add standby logfile group 4 size50M; SQL> alter database add standby logfile group 5 size50M; SQL> alter database add standby logfile group 6 size50M; SQL> alter database add standby logfile group 7 size50M; SQL> select member,type fromv$logfile; MEMBER                                            TYPE --------------------------------------------------------- +DATA/orcl/onlinelog/group_3.263.765986013        ONLINE +FRA/orcl/onlinelog/group_3.259.765986017         ONLINE +DATA/orcl/onlinelog/group_2.262.765986005        ONLINE +FRA/orcl/onlinelog/group_2.258.765986009         ONLINE +DATA/orcl/onlinelog/group_1.261.765985997        ONLINE +FRA/orcl/onlinelog/group_1.257.765986003         ONLINE +DATA/primary/onlinelog/group_4.268.765996737     STANDBY +FRA/primary/onlinelog/group_4.264.765996743      STANDBY +DATA/primary/onlinelog/group_5.269.765996751     STANDBY +FRA/primary/onlinelog/group_5.265.765996757      STANDBY +DATA/primary/onlinelog/group_6.270.765996763     STANDBY MEMBER                                            TYPE --------------------------------------------------------- +FRA/primary/onlinelog/group_6.266.765996767      STANDBY +DATA/primary/onlinelog/group_7.271.765996775     STANDBY +FRA/primary/onlinelog/group_7.267.765996779      STANDBY 4：配置Data guard相关参数 SQL> alter system setdb_unique_name='primary' scope=spfile; System altered. SQL> alter system setlog_archive_config='DG_CONFIG=(primary,physical)'; System altered. SQL> show parameterremote_login; NAME                                TYPE       VALUE ------------------------------------ ----------------------------------------- remote_login_passwordfile           string     EXCLUSIVE SQL> alter system set log_archive_dest_1='LOCATION=+FRA/orclvalid_for=(all_logfiles,all_roles) db_unique_name=primary'scope=spfile;  System altered.  SQL> alter system setlog_archive_dest_2='service=physical lgwr asyncvalid_for=(online_logfile,primary_role) db_unique_name=physical'scope=spfile;  System altered. SQL> alter system setlog_archive_dest_state_1=enable;  System altered. SQL> alter system setlog_archive_dest_state_2=enable;  System altered. SQL>  alter systemset log_archive_format='%t_%s_%r.arc' scope=spfile; System altered. SQL> alter system setfal_server='physical'; System altered. SQL> alter system setfal_client='primary'; System altered. SQL> alter database forcelogging; Database altered 5：配置listener.ora和tnsnames.ora文件(备库上需要做同样的配置)，重启监听器和数据库，确保数据库连接正常 [oracle@primary ~]$ cat$ORACLE_HOME/network/admin/listener.ora SID_LIST_LISTENER =     (SID_LIST=       (SID_DESC=        (SID_NAME =PLSExtProc)        (GLOBAL_DBNAME =primary_DGMGRL.yang.com)        (ORACLE_HOME =/u01/app/oracle/product/10.2.0/db_1)        (PROGRAM = extproc)      )     )     LISTENER =     (DESCRIPTION_LIST=       (DESCRIPTION=        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.227.20)(PORT =1521))        (ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC0))      )  [oracle@primary ~]$ cat$ORACLE_HOME/network/admin/tnsnames.ora primary =     (DESCRIPTION=      (ADDRESS_LIST =        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.227.20)(PORT =1521))      )      (CONNECT_DATA =        (SERVICE_NAME =primary.yang.com)      )    )   physical =     (DESCRIPTION=      (ADDRESS_LIST =        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.227.30)(PORT =1521))      )      (CONNECT_DATA =        (SERVICE_NAME =physical.yang.com)      )     )  [oracle@primary ~]$ sqlplus/nolog SQL> conn /as sysdba Connected. SQL> shutdown immediate; [oracle@primary ~]$ lsnrctlstop [oracle@primary ~]$ lsnrctl start [oracle@primary ~]$ sqlplus/nolog SQL> conn /as sysdba Connected to an idle instance. SQL> startup SQL>conn sys/123456@primary assysdba Connected. SQL> show parameter name; NAME                                TYPE       VALUE ------------------------------------ --------------------------------- db_file_name_convert                string db_name                             string     orcl db_unique_name                      string     primary global_names                        boolean    FALSE instance_name                       string     orcl lock_name_space                     string log_file_name_convert               string service_names                       string     primary.yang.com 6:备份主库相关文件 SQL> !mkdir -p /home/oracle/dg_backup SQL> create pfile='/home/oracle/dg_backup/initorcl.ora' fromspfile; File created.  [oracle@primary ~]$ rman target/ RMAN> backup incremental level 0 format'/home/oracle/dg_backup/dg_%U' 2> tag 'dg_asm' database plus archivelog; RMAN>  backup format'/home/oracle/dg_backup/ctl_asm_%U' current controlfile forstandby;  [oracle@primary~]$ ll -h /home/oracle/dg_backup/ total 670M -rw-r----- 1 oracle oinstall 6.8M Oct 31 15:32ctl_asm_05mqg6vt_1_1 -rw-r----- 1 oracle oinstall  64M Oct 31 15:28dg_01mqg6o9_1_1 -rw-r----- 1 oracle oinstall 592M Oct 31 15:29dg_02mqg6oj_1_1 -rw-r----- 1 oracle oinstall 6.9M Oct 31 15:29dg_03mqg6qa_1_1 -rw-r----- 1 oracle oinstall  25K Oct 31 15:29dg_04mqg6qh_1_1 -rw-r--r-- 1 oracle oinstall 1.5K Oct 31 15:24initorcl.ora

 

 二：备库上的配置

 

1：配置ASM [oracle@physical ~]$ export ORACLE_SID=+ASM [oracle@physical ~]$ sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Mon Oct 31 17:18:292011 Copyright (c) 1982, 2005, Oracle.  All rightsreserved. SQL> conn /as sysdba Connected. SQL> select name,path,failgroup from v$asm_disk; NAME      PATH                                    FAILGROUP ---------- -------------------------------------------------- ASMDISK5  /dev/raw/raw5                           ASMDISK5 ASMDISK4  /dev/raw/raw4                           FG2 ASMDISK3  /dev/raw/raw3                           FG2 ASMDISK2  /dev/raw/raw2                           FG1 ASMDISK1  /dev/raw/raw1                           FG1 2：复制主库上的备份数据 [oracle@physical ~]$ scp -rp primary:/home/oracle/dg_backup./ [oracle@physical ~]$ cat dg_backup/initorcl.ora （修改如下参数） *.db_unique_name='physical' *.fal_client='physical' *.fal_server='primary' *.log_archive_dest_1='LOCATION=+FRA/orclvalid_for=(all_logfiles,all_roles)db_unique_name=physical' *.log_archive_dest_2='service=primary lgwr asyncvalid_for=(online_logfile,primary_role)db_unique_name=primary' [oracle@physical~]$ cp dg_backup/initorcl.ora $ORACLE_HOME/dbs [oracle@physical ~]$ orapwd file=$ORACLE_HOME/dbs/orapworclpassword=123456 entries=5 [oracle@physical ~]$ sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Mon Oct 31 17:33:302011 Copyright (c) 1982, 2005, Oracle.  All rightsreserved. SQL> conn /as sysdba Connected. SQL> select open_mode,name from v$database; OPEN_MODE NAME ---------- --------- MOUNTED   ORCL SQL> alterdatabase recover managed standby database disconnect fromsession; Database altered Total SystemGlobal Area  167772160 bytes FixedSize                 1218316 bytes VariableSize             83888372 bytes DatabaseBuffers          79691776 bytes RedoBuffers               2973696 bytes [oracle@physical~]$ rman target sys/123456@primary auxiliary/ Recovery Manager: Release 10.2.0.1.0 - Production on Mon Oct 3117:26:18 2011 Copyright (c) 1982, 2005, Oracle.  All rightsreserved. connected to target database: ORCL (DBID=1293766727) connected to auxiliary database: ORCL (not mounted) RMAN> duplicate target database for standbynofilenamecheck; SQL> alterdatabase recover managed standby database disconnect fromsession; Database altered 验证： [oracle@physical ~]$ sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Mon Oct 31 17:43:162011 Copyright (c) 1982, 2005, Oracle.  All rightsreserved. SQL> conn /as sysdba Connected. SQL> selectsequence#,first_time,next_time,applied from v$archived_log order by1;  SEQUENCE#FIRST_TIME         NEXT_TIME          APP ---------- ------------------- ------------------- ---         2 2011-10-31:13:57:04 2011-10-31:14:02:38 NO         3 2011-10-31:14:02:38 2011-10-31:14:24:53 NO         4 2011-10-31:14:24:53 2011-10-31:14:25:57 NO         5 2011-10-31:14:25:57 2011-10-31:15:28:03 NO         6 2011-10-31:15:28:03 2011-10-31:15:29:17 NO         7 2011-10-31:15:29:17 2011-10-31:16:59:50 NO         8 2011-10-31:16:59:50 2011-10-31:17:01:33 YES 7 rows selected. 查看日志信息： [oracle@physical ~]$ cd /u01/app/oracle/admin/orcl/bdump/ [oracle@physical bdump]$ tail -falert_orcl.log  Archivelog restore complete. Elapsed time:0:00:01  Archivelog restore complete. Elapsed time:0:00:00  Archivelog restore complete. Elapsed time:0:00:01  Archivelog restore complete. Elapsed time:0:00:02  Archivelog restore complete. Elapsed time:0:00:03  Archivelog restore complete. Elapsed time:0:00:06  Archivelog restore complete. Elapsed time:0:00:00  Mon Oct 31 17:41:33 2011 Media Recovery Log +FRA/orcl/1_8_765985997.arc Media Recovery Waiting for thread 1 sequence 9 主库上切换日志： SQL> alter system switch logfile; System altered. SQL> altersystem switch logfile; System altered. 备库上再次查询： SQL> select sequence#,first_time,next_time,applied fromv$archived_log where sequence# >= 8 order by 1;  SEQUENCE#FIRST_TIME         NEXT_TIME          APP ---------- ------------------- ------------------- ---         8 2011-10-31:16:59:50 2011-10-31:17:01:33 YES         9 2011-10-31:17:01:33 2011-10-31:17:45:49 YES        10 2011-10-31:17:45:49 2011-10-31:17:49:26 YES        11 2011-10-31:17:49:26 2011-10-31:17:50:42 YES 查看日志信息： [oracle@physical admin]$ tail -f/u01/app/oracle/admin/orcl/bdump/alert_orcl.log  Fetching gap sequence in thread 1, gap sequence 9-10 Mon Oct 31 17:51:14 2011 RFS[1]: Archived Log: '+FRA/orcl/1_9_765985997.arc' RFS[1]: Archived Log: '+FRA/orcl/1_10_765985997.arc' RFS[1]: Archived Log: '+FRA/orcl/1_11_765985997.arc' Mon Oct 31 17:51:48 2011 Media Recovery Log +FRA/orcl/1_9_765985997.arc Media Recovery Log +FRA/orcl/1_10_765985997.arc Media Recovery Log +FRA/orcl/1_11_765985997.arc Media Recovery Waiting for thread 1 sequence 12

 

三：排错

 

1：数据不同步问题排错 SQL> select sequence#,first_time,next_time,applied fromv$archived_log; no rows selected SQL> archivelog list; Database logmode             Archive Mode Automaticarchival            Enabled Archivedestination           +FRA/orcl Oldest online logsequence    0 Next log sequence to archive  0 Current logsequence          0 SQL> select *from v$archive_gap; no rows selected [oracle@physical~]$ cd /u01/app/oracle/admin/orcl/bdump/ [oracle@physical bdump]$ ls alert_orcl.log orcl_arc1_18529.trc  orcl_mrp0_18646.trc [oracle@physical bdump]$ tail -falert_orcl.log  FAL[client]: Failed to request gapsequence   GAP - thread 1 sequence 8-8  DBID 1293766727 branch 765985997 FAL[client]: All defined FAL servers have been attempted. ------------------------------------------------------------- Check that the CONTROL_FILE_RECORD_KEEP_TIMEinitialization parameter is defined to a value that is sufficientlylarge enough to maintain adequate log switch information toresolve archivelog gaps. 主库上查看归档日志信息 SQL> archive log list; Database logmode             Archive Mode Automaticarchival            Enabled Archivedestination           +FRA/orcl Oldest online logsequence    7 Next log sequence to archive  9 Current logsequence          9 在备库上还原归档日志 RMAN> restore archivelog all; channel ORA_DISK_1: reading from backup piece/home/oracle/dg_backup/dg_09mqgc7f_1_1 ORA-19870: error reading backup piece/home/oracle/dg_backup/dg_09mqgc7f_1_1 ORA-19504: failed to create file"+FRA/orcl/1_8_765985997.arc" ORA-17502: ksfdcre:4 Failed to create file+FRA/orcl/1_8_765985997.arc ORA-15173: entry 'orcl' does not exist in directory '/' failover to previous backup RMAN-00571:=========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS=============== RMAN-00571:=========================================================== RMAN-03002: failure of restore command at 10/31/201117:39:52 RMAN-06026: some targets not found - aborting restore RMAN-06025: no backup of log thread 1 seq 8 lowscn 489318 found torestore RMAN-06025: no backup of log thread 1 seq 7 lowscn 485345 found torestore RMAN-06025: no backup of log thread 1 seq 6 lowscn 485300 found torestore RMAN-06025: no backup of log thread 1 seq 5 lowscn 482789 found torestore RMAN-06025: no backup of log thread 1 seq 4 lowscn 482752 found torestore RMAN-06025: no backup of log thread 1 seq 3 lowscn 479773 found torestore RMAN-06025: no backup of log thread 1 seq 2 lowscn 472799 found torestore [oracle@physicaldg_backup]$ export ORACLE_SID=+ASM [oracle@physical dg_backup]$ asmcmd ASMCMD> cd FRA ASMCMD> ls PHYSICAL/ ASMCMD> mkdir orcl ASMCMD> ls PHYSICAL/ orcl/ RMAN> restorearchivelog all; ASMCMD> cd orcl ASMCMD> ls 1_2_765985997.arc 1_3_765985997.arc 1_4_765985997.arc 1_5_765985997.arc 1_6_765985997.arc 1_7_765985997.arc 1_8_765985997.arc 2:关闭主库后，重启物理备库，报错如下,找不到控制文件 SQL> startup mount ORA-00205: error in identifying control file, check alert log formore info [oracle@physicaldbs]$ grep  -i control_files initorcl.ora//该路径需要和asmcmd命令找到的控制文件路径一致 *.control_files='+DATA/physical/controlfile/Current.256.765999025','+FRA/physcial/controlfile/backup.256.765999027' SQL> conn /assysdba Connected. SQL> shutdown abort ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total SystemGlobal Area  167772160 bytes FixedSize                 1218316 bytes VariableSize             83888372 bytes DatabaseBuffers          79691776 bytes RedoBuffers               2973696 bytes Database mounted. SQL> alterdatabase recover managed standby database disconnect fromsession; Database altered. 配置物理备库以spfile方式启动，spfile不使用ASM管理 SQL> create spfile from pfile; File created. SQL> alterdatabase recover managed standby database cancel; Database altered. SQL> shutdownimmediate SQL> startup mount SQL> alterdatabase recover managed standby database disconnect fromsession; Database altered. SQL> showparameter spfile; NAME                                TYPE       VALUE ------------------------------------ ----------------------------------------- spfile                              string     /u01/app/oracle/product/10.2.0                                                 /db_1/dbs/spfileorcl.ora 3：配置ASM报错如下 [root@physical ~]#/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 toinittab  Startup will be queued to init within 90 seconds. Checking the status of new Oracle init process... Expecting the CRS daemons to be up within 600 seconds. Giving up: Oracle CSS stack appears NOT to be running. Oracle CSS service would not start as installed Automatic Storage Management(ASM) cannot be used until Oracle CSSservice is started [root@physical~]# /etc/init.d/init.cssd run >/dev/null 2>&1 [1]16323 [root@physical ~]# ps -ef |grep css root    16323 14445  0 16:22pts/2   00:00:00 /bin/sh /etc/init.d/init.cssd run root    16338 16323  0 16:22pts/2   00:00:00 /bin/sh /etc/init.d/init.cssd startcheck root    16406 14445  0 16:22pts/2   00:00:00 grep css [root@physical~]# cd /u01/app/oracle/product/10.2.0/db_1/bin/ [root@physical bin]# ./crsctl check crs Failure 1 contacting CSS daemon Cannot communicate with CRS Cannot communicate with EVM  [root@physical bin]# ./crsctl start crs Attempting to start CRS stack  The CRS stack will be started shortly [root@physicalbin]# ./crsctl check crs CSS appears healthy Cannot communicate with CRS Cannot communicate with EVM 4：物理备库standby日志组invalid问题排错 主库： SQL> select member,type from v$logfile;   MEMBER                                                      TYPE ---------------------------------------------------------------------- +DATA/orcl/onlinelog/group_3.263.765986013                  ONLINE +FRA/orcl/onlinelog/group_3.259.765986017                   ONLINE +DATA/orcl/onlinelog/group_2.262.765986005                  ONLINE +FRA/orcl/onlinelog/group_2.258.765986009                   ONLINE +DATA/orcl/onlinelog/group_1.261.765985997                  ONLINE +FRA/orcl/onlinelog/group_1.257.765986003                   ONLINE +DATA/primary/onlinelog/group_4.268.765996737               STANDBY +FRA/primary/onlinelog/group_4.264.765996743                STANDBY +DATA/primary/onlinelog/group_5.269.765996751               STANDBY +FRA/primary/onlinelog/group_5.265.765996757                STANDBY +DATA/primary/onlinelog/group_6.270.765996763