Configure Oracle Dataguard Primary-ASM to Physical-ASM薦

前面整理過Oracle物理備庫的配置文件，資料檔案是儲存在檔案系統上的，在生產環境中的DBA，往往都是面對幾十上百G的資料，甚至可能是T級別的，檔案系統儲存資料檔案在這些場合下的I/O問題就會逐漸暴露，因而在生產環境中的資料儲存一般都使用ASM，或者裸裝置，oracle 11g開始就不支援裸裝置儲存資料了，因而本講主要介紹在ASM環境下配置Data guard物理備庫！

環境介紹：
主庫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:17 pts/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:16 pts/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 from v$instance;

INSTANCE_N STATUS
———- ————————————
+ASM STARTED

SQL> create diskgroup data normal redundancy
2 failgroup fg1 disk
3 `/dev/raw/raw1` name asmdisk1,
4 `/dev/raw/raw2` name asmdisk2
5 failgroup fg2 disk
6 `/dev/raw/raw3` name asmdisk3,
7 `/dev/raw/raw4` name asmdisk4;
Diskgroup created.

SQL> create diskgroup fra external redundancy
2 disk `/dev/raw/raw5` name asmdisk5;
Diskgroup created.

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

SQL> select name,total_mb,free_mb,usable_file_mb from v$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 from dba_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 from v$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 parameter spfile;

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 log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +FRA/orcl
Oldest online log sequence     3
Next log sequence to archive   5
Current log sequence           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 size 50M;
SQL> alter database add standby logfile group 5 size 50M;
SQL> alter database add standby logfile group 6 size 50M;
SQL> alter database add standby logfile group 7 size 50M;

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      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 set db_unique_name=`primary` scope=spfile;
System altered.

SQL> alter system set log_archive_config=`DG_CONFIG=(primary,physical)`;
System altered.

SQL> show parameter remote_login;

NAME TYPE VALUE
———————————— ———– ——————————
remote_login_passwordfile string EXCLUSIVE

SQL> alter system set log_archive_dest_1=`LOCATION=+FRA/orcl valid_for=(all_logfiles,all_roles) db_unique_name=primary` scope=spfile;
System altered.

SQL> alter system set log_archive_dest_2=`service=physical lgwr async valid_for=(online_logfile,primary_role) db_unique_name=physical` scope=spfile;
System altered.
SQL> alter system set log_archive_dest_state_1=enable;
System altered.

SQL> alter system set log_archive_dest_state_2=enable;
System altered.

SQL> alter system set log_archive_format=`%t_%s_%r.arc` scope=spfile;
System altered.

SQL> alter system set fal_server=`physical`;
System altered.

SQL> alter system set fal_client=`primary`;
System altered.

SQL> alter database force logging;
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 ~]$ lsnrctl stop
[oracle@primary ~]$ lsnrctl start

[oracle@primary ~]$ sqlplus /nolog
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup

SQL> conn sys/123456@primary as sysdba
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` from spfile;
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 for standby;

[oracle@primary ~]$ ll -h /home/oracle/dg_backup/
total 670M
-rw-r—– 1 oracle oinstall 6.8M Oct 31 15:32 ctl_asm_05mqg6vt_1_1
-rw-r—– 1 oracle oinstall 64M Oct 31 15:28 dg_01mqg6o9_1_1
-rw-r—– 1 oracle oinstall 592M Oct 31 15:29 dg_02mqg6oj_1_1
-rw-r—– 1 oracle oinstall 6.9M Oct 31 15:29 dg_03mqg6qa_1_1
-rw-r—– 1 oracle oinstall 25K Oct 31 15:29 dg_04mqg6qh_1_1
-rw-r–r– 1 oracle oinstall 1.5K Oct 31 15:24 initorcl.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:29 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
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/orcl valid_for=(all_logfiles,all_roles) db_unique_name=physical`
*.log_archive_dest_2=`service=primary lgwr async valid_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/orapworcl password=123456 entries=5
[oracle@physical ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 – Production on Mon Oct 31 17:33:30 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> select open_mode,name from v$database;

OPEN_MODE NAME
———- ———
MOUNTED ORCL

SQL> alter database recover managed standby database disconnect from session;
Database altered

Total System Global Area 167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              83888372 bytes
Database Buffers           79691776 bytes
Redo Buffers                2973696 bytes

[oracle@physical ~]$ rman target sys/123456@primary auxiliary /
Recovery Manager: Release 10.2.0.1.0 – Production on Mon Oct 31 17:26:18 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1293766727)
connected to auxiliary database: ORCL (not mounted)
RMAN> duplicate target database for standby nofilenamecheck;

SQL> alter database recover managed standby database disconnect from session;
Database altered

驗證：
[oracle@physical ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 – Production on Mon Oct 31 17:43:16 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.

SQL> select sequence#,first_time,next_time,applied from v$archived_log order by 1;

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 -f alert_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> alter system switch logfile;
System altered.

備庫上再次查詢：
SQL> select sequence#,first_time,next_time,applied from v$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 from v$archived_log;
no rows selected

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +FRA/orcl
Oldest online log sequence     0
Next log sequence to archive   0
Current log sequence           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 -f alert_orcl.log
FAL[client]: Failed to request gap sequence
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_TIME initialization
parameter is defined to a value that is sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.

主庫上檢視歸檔日誌資訊
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +FRA/orcl
Oldest online log sequence     7
Next log sequence to archive   9
Current log sequence           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/2011 17:39:52
RMAN-06026: some targets not found – aborting restore
RMAN-06025: no backup of log thread 1 seq 8 lowscn 489318 found to restore
RMAN-06025: no backup of log thread 1 seq 7 lowscn 485345 found to restore
RMAN-06025: no backup of log thread 1 seq 6 lowscn 485300 found to restore
RMAN-06025: no backup of log thread 1 seq 5 lowscn 482789 found to restore
RMAN-06025: no backup of log thread 1 seq 4 lowscn 482752 found to restore
RMAN-06025: no backup of log thread 1 seq 3 lowscn 479773 found to restore
RMAN-06025: no backup of log thread 1 seq 2 lowscn 472799 found to restore

[oracle@physical dg_backup]$ export ORACLE_SID=+ASM
[oracle@physical dg_backup]$ asmcmd
ASMCMD> cd FRA
ASMCMD> ls
PHYSICAL/
ASMCMD> mkdir orcl
ASMCMD> ls
PHYSICAL/
orcl/

RMAN> restore archivelog 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 for more info

[oracle@physical dbs]$ grep -i control_files initorcl.ora //該路徑需要和asmcmd命令找到的控制檔案路徑一致
*.control_files=`+DATA/physical/controlfile/Current.256.765999025`,`+FRA/physcial/controlfile/backup.256.765999027`

SQL> conn /as sysdba
Connected.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              83888372 bytes
Database Buffers           79691776 bytes
Redo Buffers                2973696 bytes
Database mounted.

SQL> alter database recover managed standby database disconnect from session;
Database altered.

配置物理備庫以spfile方式啟動，spfile不使用ASM管理
SQL> create spfile from pfile;
File created.

SQL> alter database recover managed standby database cancel;
Database altered.

SQL> shutdown immediate
SQL> startup mount

SQL> alter database recover managed standby database disconnect from session;
Database altered.

SQL> show parameter 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 to inittab
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 CSS service is started

[root@physical ~]# /etc/init.d/init.cssd run >/dev/null 2>&1 </dev/null &
[1] 16323
[root@physical ~]# ps -ef |grep css
root     16323 14445 0 16:22 pts/2    00:00:00 /bin/sh /etc/init.d/init.cssd run
root     16338 16323 0 16:22 pts/2    00:00:00 /bin/sh /etc/init.d/init.cssd startcheck
root     16406 14445 0 16:22 pts/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@physical bin]# ./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                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

備庫：
SQL> select member,type from v$logfile;

MEMBER                                                       TYPE
———————————————————— ———-
+DATA/physical/onlinelog/group_3.264.765999293               ONLINE
+FRA/physical/onlinelog/group_3.259.765999299                ONLINE
+DATA/physical/onlinelog/group_2.263.765999285               ONLINE
+FRA/physical/onlinelog/group_2.258.765999291                ONLINE
+DATA/physical/onlinelog/group_1.262.765999279               ONLINE
+FRA/physical/onlinelog/group_1.257.765999283                ONLINE
+DATA                                                        STANDBY
+FRA                                                         STANDBY
+DATA                                                        STANDBY
+FRA                                                         STANDBY
+DATA                                                        STANDBY

MEMBER                                                       TYPE
———————————————————— ———-
+FRA                                                         STANDBY
+DATA                                                        STANDBY
+FRA                                                         STANDBY

在主庫上刪除所有的standby日誌組，並切換日誌組
SQL> alter database drop standby logfile group 4;
SQL> alter database drop standby logfile group 5;
SQL> alter database drop standby logfile group 6;
SQL> alter database drop standby logfile group 7;
SQL> alter system switch logfile;

備庫查詢：
SQL> select member,type from v$logfile;

MEMBER                                                       TYPE
———————————————————— ———-
+DATA/physical/onlinelog/group_3.264.765999293               ONLINE
+FRA/physical/onlinelog/group_3.259.765999299                ONLINE
+DATA/physical/onlinelog/group_2.263.765999285               ONLINE
+FRA/physical/onlinelog/group_2.258.765999291                ONLINE
+DATA/physical/onlinelog/group_1.262.765999279               ONLINE
+FRA/physical/onlinelog/group_1.257.765999283                ONLINE

在主庫上新增新的standby日誌組並切換日誌，發現備庫上依然無法同步
SQL> alter database add standby logfile group 4 size 50M;
SQL> alter database add standby logfile group 5 size 50M;
SQL> alter database add standby logfile group 6 size 50M;
SQL> alter database add standby logfile group 7 size 50M;
SQL> alter system switch logfile;

[oracle@physical admin]$ tail -f /u01/app/oracle/admin/orcl/bdump/alert_orcl.log
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: No standby redo logfiles created
Mon Oct 31 18:05:16 2011
Media Recovery Log +FRA/orcl/1_12_765985997.arc
Media Recovery Waiting for thread 1 sequence 13 (in transit)
Mon Oct 31 18:05:33 2011
Redo Shipping Client Connected as PUBLIC
— Connected User is Valid
RFS[3]: Assigned to RFS process 19621
RFS[3]: Identified database type as `physical standby`

猜想可能是ASM磁碟組下沒有primary目錄的原因，在ASM磁碟組下建立相關的目錄，在主庫上刪除standby日誌組後新增新的日誌組，並切換日誌，發現standby日誌組依然無法同步；後來嘗試在主庫上將standby日誌組放在檔案系統上，切換日誌後，備庫依然無法同步建立！

後來這個問題通過switchover後，在原物理備庫上手動建立standby日誌組搞定！

Configure Oracle Dataguard Primary-ASM to Physical-ASM薦

相關文章