Production DB expdp 导致standby归档恢复时混乱

  • tolywang

    2010-02-22 21:45:34
  • Linux操作系统
  • 原创

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

 

mxrac-sty02<*mxdell1*/home/oracle>$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<*mxdell1*/home/oracle>$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<*mxdell1*/u01/product/admin/mxdell/udump>$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<*mxdell1*/u01/product/admin/mxdell/udump>$

DFMSBACKUPDB<*mxdell1*/u01/product/admin/mxdell/udump>$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

 

请使用浏览器的分享功能分享到微信等