Production DB expdp 導致standby歸檔恢復時混亂

Oracle 10.2.0.4 ,  Linux AS 5.3  64bit  ， RAC ,  3nodes ,  兩臺standby (dataguard) . 

 

mxrac-sty02$sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Feb 22 06:42:43 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options

SQL>
SQL>
SQL> recover  standby database;
ORA-00279: change 2228379664 generated at 02/21/2010 07:04:25 needed for thread
1
ORA-00289: suggestion : /ocfs_data/mxdell/arch/1_9804_703296551.arc
ORA-00280: change 2228379664 for thread 1 is in sequence #9804

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 2228379664 generated at 02/07/2010 22:21:59 needed for thread
3
ORA-00289: suggestion : /ocfs_data/mxdell/arch/3_7082_703296551.arc
ORA-00280: change 2228379664 for thread 3 is in sequence #7082

ORA-00308: cannot open archived log
'/ocfs_data/mxdell/arch/3_7082_703296551.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3 

 

 

 

2/21 週日早上7:04 , 應該是我們分別shutdown 監聽及例項的時候, 我們在做備份之前都會做這個動作,

貌似每次都會出現Standby 歸檔恢復number 錯亂的問題.

目前還不太清楚是分別shutdown 例項導致, 還是後面的expdp 修改了service_name 導致 。 應該和這兩個事情有關 。

我們透過重新在production db上生成controlfile 來修復這個問題 ， 修復之前 trace 出控制檔案的內容：

DFMSBACKUPDB$sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Feb 22 07:16:53 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options

SQL>
SQL>
SQL>                        
SQL> alter database backup controlfile to trace ; 

Database altered.

SQL>
SQL>     

 

DFMSBACKUPDB$ls -alrth
total 68K
drwxr-x---  9 oracle dba 4.0K Dec  7 18:44 ..
-rw-r-----  1 oracle dba 1.6K Feb 22 04:20 mxdell1_ora_17064.trc
-rw-r-----  1 oracle dba 1.6K Feb 22 06:20 mxdell1_ora_18369.trc
drwxr-x---  2 oracle dba 8.0K Feb 22 07:21 .
-rw-r-----  1 oracle dba  25K Feb 22 07:21 mxdell1_ora_19032.trc
DFMSBACKUPDB$

DFMSBACKUPDB$vi   mxdell1_ora_19032.trc 

 

 

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "MXDELL" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 192
    MAXLOGMEMBERS 3
    MAXDATAFILES 2048
    MAXINSTANCES 32
    MAXLOGHISTORY 4096
LOGFILE
  GROUP 25 (
    '/ocfs_ctrl_redo/mxdell/redo25.log',
    '/ocfs_data/mxdell/redo25.log'
  ) SIZE 100M,
  GROUP 26 (
    '/ocfs_ctrl_redo/mxdell/redo26.log',
    '/ocfs_data/mxdell/redo26.log'
  ) SIZE 100M,
  GROUP 27 (
    '/ocfs_ctrl_redo/mxdell/redo27.log',
    '/ocfs_data/mxdell/redo27.log'
  ) SIZE 100M,
  GROUP 28 (
    '/ocfs_ctrl_redo/mxdell/redo28.log',
    '/ocfs_data/mxdell/redo28.log'
  ) SIZE 100M,
  GROUP 29 (
    '/ocfs_ctrl_redo/mxdell/redo29.log',
    '/ocfs_data/mxdell/redo29.log'
  ) SIZE 100M,
  GROUP 30 (
    '/ocfs_ctrl_redo/mxdell/redo30.log',
    '/ocfs_data/mxdell/redo30.log'
  ) SIZE 100M
-- STANDBY LOGFILE
DATAFILE
  '/ocfs_data/mxdell/system01.dbf',
  '/ocfs_data/mxdell/undotbs01.dbf',
  '/ocfs_data/mxdell/sysaux01.dbf',
  '/ocfs_data/mxdell/undotbs05.dbf',
  '/ocfs_data/mxdell/undotbs03.dbf',
  '/ocfs_data/mxdell/users01.dbf',
  '/ocfs_data/mxdell/base_data01.dbf',

 

修復之後：

-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "MXDELL" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 192
    MAXLOGMEMBERS 3
    MAXDATAFILES 2048
    MAXINSTANCES 32
    MAXLOGHISTORY 4096
LOGFILE
  GROUP 31 (
    '/ocfs_ctrl_redo/mxdell/redo31_a.log',
    '/ocfs_data/mxdell/redo31_b.log'
  ) SIZE 300M,
  GROUP 32 (
    '/ocfs_ctrl_redo/mxdell/redo32_a.log',
    '/ocfs_data/mxdell/redo32_b.log'
  ) SIZE 300M,
  GROUP 33 (
    '/ocfs_ctrl_redo/mxdell/redo33_a.log',
    '/ocfs_data/mxdell/redo33_b.log'
  ) SIZE 300M,
  GROUP 34 (
    '/ocfs_ctrl_redo/mxdell/redo34_a.log',
    '/ocfs_data/mxdell/redo34_b.log'
  ) SIZE 300M,
  GROUP 35 (
    '/ocfs_ctrl_redo/mxdell/redo35_a.log',
    '/ocfs_data/mxdell/redo35_b.log'
  ) SIZE 300M,

 

 

redo log 檔案的大小修改， 應該是在 2/18 ， 也不應該到現在才報錯出來 。

-rw-r----- 1 oracle dba 301M Feb 18 11:28 redo31_b.log
-rw-r----- 1 oracle dba 301M Feb 18 11:28 redo32_b.log
-rw-r----- 1 oracle dba 301M Feb 18 11:28 redo33_b.log
-rw-r----- 1 oracle dba 301M Feb 18 11:28 redo34_b.log
-rw-r----- 1 oracle dba 301M Feb 18 11:28 redo35_b.log

 

同時在操作expdp的節點4 看到/var/log/message 的資訊：

Feb 21 04:03:02 mxrac04 syslogd 1.4.1: restart.
Feb 21 04:26:01 mxrac04 auditd[7295]: Audit daemon rotating log files
Feb 21 07:07:38 mxrac04 gconfd (oracle-10779): starting (version 2.14.0), pid 10779 user 'oracle'
Feb 21 07:07:38 mxrac04 gconfd (oracle-10779): Resolved address "xml:readonly:/etc/gconf/gconf.xml.mandatory" to a read-only configuration source at position 0
Feb 21 07:07:38 mxrac04 gconfd (oracle-10779): Resolved address "xml:readwrite:/home/oracle/.gconf" to a writable configuration source at position 1
Feb 21 07:07:38 mxrac04 gconfd (oracle-10779): Resolved address "xml:readonly:/etc/gconf/gconf.xml.defaults" to a read-only configuration source at position 2
Feb 21 07:07:39 mxrac04 hcid[7705]: Default passkey agent (:1.817, /org/bluez/applet) registered
Feb 21 07:07:39 mxrac04 pcscd: winscard.c:304:SCardConnect() Reader E-Gate 0 0 Not Found
Feb 21 07:07:39 mxrac04 last message repeated 4 times
Feb 21 07:07:39 mxrac04 gconfd (oracle-10779): Resolved address "xml:readwrite:/home/oracle/.gconf" to a writable configuration source at position 0
Feb 22 06:10:01 mxrac04 auditd[7295]: Audit daemon rotating log files