Dataguard搭建問題小記

由於希望測試一個DG問題，所以在Vmware上搭建測試環境，primary和standby放在同一台主機上。  
搭建過程就不贅述，但是在最後，遇到了一些問題，這裡記錄一下，以備後續查閱。

----搭建過程中的一些命令-----  

1. backup database format '/tmp/bk_%U';2. backup current controlfile for standby format '/tmp/stdbyctl.bkp';3. catalog start with '/tmp/';4. set controlfile autobackup format for device type disk to '/tmp/%F';    restore standby controlfile from '/tmp/stdbyctl.bkp';  5. RMAN> CONNECT TARGET SYS/oracle@db;RMAN> CONNECT AUXILIARY SYS/oracle@stddb;

6. 最會std的pfile檔案內容    

db_file_name_convert= '+data/db/','+reco/stddb/'  log_file_name_convert= '+RECO/db/archivelog/','+RECO/stddb/archivelog_std/'  log_archive_format=%t_%s_%r.arc  standby_file_management=auto  compatible='11.2.0.0.0'  control_files='+RECO/stddb/controlfile/current.260.834947597'    <<<<<<在恢複control之前，先設定為control_files='+RECO'恢複之後，根據實際位置更改  db_block_size=8192  processes=150  remote_login_passwordfile='EXCLUSIVE'  undo_tablespace='UNDOTBS1'  undo_management=auto  log_archive_config='dg_config=(primdb,stbdb)'  log_archive_dest_1='location=+RECO/stddb/archivelog_std/  valid_for=(all_logfiles,all_roles)  db_unique_name=stbdb'

問題一：

在設定listener的過程中，由於設定靜態監聽，在standby啟動之後，同時又會註冊一個動態監聽，目前有兩個監聽，其中動態監聽狀態為blocked。  
遇到的問題就是，在duplicate串連的時候，就會發生ORA-12528錯誤，而不能正常串連到standby。

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ [oracle@OEL ~]$ lsnrctl statusLSNRCTL for Linux: Version 11.2.0.1.0 - Production on 30-DEC-2013 10:46:03Copyright (c) 1991, 2009, Oracle.  All rights reserved.Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER  ------------------------  Alias                     LISTENER  Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production  Start Date                27-NOV-2013 12:40:26  Uptime                    32 days 22 hr. 5 min. 37 sec  Trace Level               off  Security                  ON: Local OS Authentication  SNMP                      OFF  Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora  Listener Log File         /u01/app/oracle/diag/tnslsnr/OEL/listener/alert/log.xml  Listening Endpoints Summary...    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=OEL.localdomain)(PORT=1521)))  Services Summary...  Service "+ASM" has 1 instance(s).    Instance "+ASM", status READY, has 1 handler(s) for this service...  Service "PRIMDB" has 1 instance(s).    Instance "db", status READY, has 1 handler(s) for this service...  Service "dbXDB" has 1 instance(s).    Instance "db", status READY, has 1 handler(s) for this service...  Service "stbdb" has 1 instance(s).    Instance "stbdb", status UNKNOWN, has 1 handler(s) for this service...    <<<<<<<<<<<<<<<<<<<<<<  Service "stddb" has 1 instance(s).    Instance "stddb", status READY, has 1 handler(s) for this service...     <<<<<<<<<<<<<<<<<<<<<<  The command completed successfully  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

通過設定tnsnames.ora，可以解決這個問題。  

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~  stddb =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = OEL.localdomain)(PORT = 1521))    )   (CONNECT_DATA = (SERVICE_NAME = stddb)(UR=A)) <--------------------In order to avoid error ORA-12528 )~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

問題二：

在執行下面命令之後，直接primary就crash掉，經過查詢alert發現，是redo broken導致的  
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

查詢standby alert，發現如下資訊：  
在ALTER DATABASE RECOVER...開始之後，第一件事情就是clear redo log，這樣就直接導致primary crash）    

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~  # tail -f alert_stddb.log  Clearing online redo logfile 1 complete  Clearing online redo logfile 2 +DATA/db/redo02.log  Clearing online log 2 of thread 1 sequence number 5  Clearing online redo logfile 2 complete  Clearing online redo logfile 3 +DATA/db/redo03.log  Clearing online log 3 of thread 1 sequence number 3  Tue Dec 24 14:32:49 2013  Clearing online redo logfile 3 complete  Tue Dec 24 14:32:49 2013  Media Recovery Waiting for thread 1 sequence 4  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

在重建幾次之後，問題依然。  
由於datafile和archivelog都使用convert參數轉換了，但是redo是無法轉換的，如何解決這個問題呢？    
經過分析，發現在duplicate的最後，有一些警告資訊：

~~~~~~~~~duplicate the standby databsae~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~  RMAN> CONNECT TARGET SYS/oracle@db;  RMAN> CONNECT AUXILIARY SYS/oracle@stddb;connected to auxiliary database: DB (not mounted)RMAN> duplicate target database for standby;Starting Duplicate Db at 24-DEC-13 allocated channel: ORA_AUX_DISK_1  channel ORA_AUX_DISK_1: SID=13 device type=DISKcontents of Memory Script: {     restore clone standby controlfile;  }  executing Memory ScriptStarting restore at 24-DEC-13 using channel ORA_AUX_DISK_1channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: restoring control file  channel ORA_AUX_DISK_1: reading from backup piece /tmp/stdbyctl.bkp  channel ORA_AUX_DISK_1: piece handle=/tmp/stdbyctl.bkp tag=TAG20131224T133449  channel ORA_AUX_DISK_1: restored backup piece 1  channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08  output file name=+RECO/stddb/controlfile/current.259.835018533  Finished restore at 24-DEC-13contents of Memory Script: {     sql clone 'alter database mount standby database';  }  executing Memory Scriptsql statement: alter database mount standby databasecontents of Memory Script: {     set newname for tempfile  1 to   "+RECO/stddb/temp01.dbf";     switch clone tempfile all;     set newname for datafile  1 to   "+RECO/stddb/system01.dbf";     set newname for datafile  2 to   "+RECO/stddb/sysaux01.dbf";     set newname for datafile  3 to   "+RECO/stddb/undotbs01.dbf";     set newname for datafile  4 to   "+RECO/stddb/users01.dbf";     restore     clone database     ;  }  executing Memory Scriptexecuting command: SET NEWNAMErenamed tempfile 1 to +RECO/stddb/temp01.dbf in control fileexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEStarting restore at 24-DEC-13 using channel ORA_AUX_DISK_1channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set  channel ORA_AUX_DISK_1: restoring datafile 00001 to +RECO/stddb/system01.dbf  channel ORA_AUX_DISK_1: restoring datafile 00002 to +RECO/stddb/sysaux01.dbf  channel ORA_AUX_DISK_1: restoring datafile 00003 to +RECO/stddb/undotbs01.dbf  channel ORA_AUX_DISK_1: restoring datafile 00004 to +RECO/stddb/users01.dbf  channel ORA_AUX_DISK_1: reading from backup piece /tmp/bk_01osanei_1_1  channel ORA_AUX_DISK_1: piece handle=/tmp/bk_01osanei_1_1 tag=TAG20131224T132953  channel ORA_AUX_DISK_1: restored backup piece 1  channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:04:27  Finished restore at 24-DEC-13contents of Memory Script: {     switch clone datafile all;  }  executing Memory Scriptdatafile 1 switched to datafile copy input datafile copy RECID=1 STAMP=835019033 file name=+RECO/stddb/system01.dbf  datafile 2 switched to datafile copy  input datafile copy RECID=2 STAMP=835019033 file name=+RECO/stddb/sysaux01.dbf  datafile 3 switched to datafile copy  input datafile copy RECID=3 STAMP=835019033 file name=+RECO/stddb/undotbs01.dbf  datafile 4 switched to datafile copy  input datafile copy RECID=4 STAMP=835019033 file name=+RECO/stddb/users01.dbf  ORACLE error from auxiliary database: ORA-01511: error in renaming log/data files      <<<<<<<<<<<<<<<<<<<<<<<<  ORA-01275: Operation RENAME is not allowed if standby file management is automatic.    <<<<<<<<<<<<<<<<<<<<<<<<RMAN-05535: WARNING: All redo log files were not defined properly. ORACLE error from auxiliary database: ORA-01511: error in renaming log/data files  ORA-01275: Operation RENAME is not allowed if standby file management is automatic.RMAN-05535: WARNING: All redo log files were not defined properly. ORACLE error from auxiliary database: ORA-01511: error in renaming log/data files  ORA-01275: Operation RENAME is not allowed if standby file management is automatic.RMAN-05535: WARNING: All redo log files were not defined properly. Finished Duplicate Db at 24-DEC-13  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

        後手動修改當standby_file_management為MANUAL模式，可以duplicate並且rename成功，並rename正broken0~2.  
這個是我在pfile中有這個standby_file_management=auto）

  checkpoint is 977553   last deallocation scn is 959057    Undo Optimization current scn is 974128  Tue Dec 24 15:16:57 2013  Switch of datafile 1 complete to datafile copy    checkpoint is 977553  Switch of datafile 2 complete to datafile copy    checkpoint is 977553  Switch of datafile 3 complete to datafile copy    checkpoint is 977553  Switch of datafile 4 complete to datafile copy    checkpoint is 977553  alter database rename file '+DATA/db/redo01.log' to 'broken0'  Completed: alter database rename file '+DATA/db/redo01.log' to 'broken0'   <<<<<<<<  alter database rename file '+DATA/db/redo02.log' to 'broken1'  Completed: alter database rename file '+DATA/db/redo02.log' to 'broken1'  alter database rename file '+DATA/db/redo03.log' to 'broken2'  Completed: alter database rename file '+DATA/db/redo03.log' to 'broken2'  RFS connections are allowed

     在執行ALTER DATABASE RECOVER...的時候，依然需要去清空redo，只不過，應經將redo rename了，所以不會影響主庫的redo log了

~~~~~~~~~~~~~~~~~~~~~ Tue Dec 24 15:45:37 2013  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION  Attempt to start background Managed Standby Recovery process (stddb)  Tue Dec 24 15:45:37 2013  MRP0 started with pid=21, OS id=21343  MRP0: Background Managed Standby Recovery process started (stddb)   started logmerger process  Tue Dec 24 15:45:43 2013  Managed Standby Recovery not using Real Time Apply  Parallel Media Recovery started with 2 slaves  Waiting for all non-current ORLs to be archived...  All non-current ORLs have been archived.  Errors in file /u01/app/oracle/product/11.2.0/dbhome_1/log/diag/rdbms/stddb/stddb/trace/stddb_mrp0_21343.trc:  ORA-00313: open failed for members of log group 1 of thread 1  ORA-00312: online log 1 thread 1: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/broken0'     <<<<<<<<<<<<<<<<  ORA-27037: unable to obtain file status  Linux-x86_64 Error: 2: No such file or directory  Additional information: 3  ......  Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION  Clearing online redo logfile 1 complete  Errors in file /u01/app/oracle/product/11.2.0/dbhome_1/log/diag/rdbms/stddb/stddb/trace/stddb_mrp0_21343.trc:  ORA-00313: open failed for members of log group 2 of thread 1  ORA-00312: online log 2 thread 1: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/broken1'  ORA-27037: unable to obtain file status  Linux-x86_64 Error: 2: No such file or directory  Additional information: 3  ......  Tue Dec 24 15:45:48 2013  Clearing online redo logfile 2 complete  Errors in file /u01/app/oracle/product/11.2.0/dbhome_1/log/diag/rdbms/stddb/stddb/trace/stddb_mrp0_21343.trc:  ORA-00313: open failed for members of log group 3 of thread 1  ORA-00312: online log 3 thread 1: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/broken2'  ORA-27037: unable to obtain file status  Linux-x86_64 Error: 2: No such file or directory  Additional information: 3  ......  Clearing online redo logfile 3 complete  Media Recovery Waiting for thread 1 sequence 4

        總結：由於之前的DG搭建，都是在兩個不同的主機上實施，也沒有關心過這個問題，因為standby清空primary redo的動作一定是失敗的。但是現在是在一個主機上，standby能發現原primary的redo，並且由於rename失敗，所以就直接clear primary redo導致primary 無法啟動。    
       而root cause就是我在編輯pfile的時候，多寫了standby_file_management=auto，其實不寫的話，預設值是manual的，就不會出現這個問題。

本文出自 “小小狗窩” 部落格，請務必保留此出處http://hsbxxl.blog.51cto.com/181620/1346635