oracle 10g 物理備庫轉換邏輯備庫ORA-19953故障解決方法

操作環境是Red hat Linux 5.4 x86-64 Oracle 10.2.0.5 在將物理備庫轉換為邏輯備庫出現ORA-19953

SQL> alter database recover to logical standby test;
alter database recover to logical standby test
*
ERROR at line 1:
ORA-19953: database should not be open

alert.log檔案內容如下:

Incomplete Recovery applied until change 720500
Sun Jun 28 19:50:45 CST 2015
Media Recovery Complete (test_ldg)
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 720500
Resetting resetlogs activation ID 2174774786 (0x81a06e02)
Online log /u03/app/oracle/oradata/test_ldg/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u03/app/oracle/oradata/test_ldg/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u03/app/oracle/oradata/test_ldg/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 720498
Sun Jun 28 19:50:48 CST 2015
Setting recovery target incarnation to 3
Sun Jun 28 19:50:48 CST 2015
ACTIVATE STANDBY: Complete - Database shutdown required (test_ldg)
Sun Jun 28 19:50:48 CST 2015
ORA-19953 signalled during: alter database recover to logical standby test...

MOS上有一關於這個問題的BUG(Bug ID 9207121)內容如下：

Type	B - Defect	Fixed in Product Version
Severity	2 - Severe Loss of Service	Product Version	10.2.0.4
Status	33 - Suspended, Req'd Info not Avail	Platform	226 - Linux x86-64
Created	11-Dec-2009	Platform Version	RED HAT ENTERPRISE LINUX 5
Updated	05-Feb-2015	Base Bug	N/A
Database Version	10.2.0.4	Affects Platforms	Generic
Product Source	Oracle	Knowledge, Patches and Bugs related to this bug


Related Products

Line	Oracle Database Products	Family	Oracle Database Suite
Area	Oracle Database	Product	5 - Oracle Database - Enterprise Edition

Hdr: 9207121 10.2.0.4 RDBMS 10.2.0.4 DATAGUARD_LSBY PRODID-5 PORTID-226 ORA-19953
Abstract: ORA-19953 CREATING LOGICAL STANDBY

*** 12/11/09 12:35 pm ***

PROBLEM:
--------
ct has a 3-node RAC primary(db_name=TCIP, unique_name=TCIP)
and a single node physical standby db_name=TCIP,unique_name=TCIPvl) using
spfile.

Converting this physical standby to logical standby failed.
When executing on the standby side
SQL> alter database recover to logical standby TCIPvl;
the db_name in the spfile is not changed to TCIPvl.




DIAGNOSTIC ANALYSIS:
--------------------
The following outlines the steps:
- Verified that primary and physical standby are in sync. (around 2009 12/11
12:30)
- stopped recovery at physical standby (Fri Dec 11 12:35:10 2009)
- build dictionary on primary  (Fri Dec 11 12:55:29 2009 log seq 9976)
   SQL> DBMS_LOGSTDBY.BUILD;
- switched logs on primary (all instances 3 times)
- verified on the standby side that the logs containing dictionary
information were archived and arrived (but not applied) on the standby
- executed "alter database recover to logical standby TCIPvl" on standby (Fri
Dec 11 13:05:35 2009)
- the above SQL did not show any errors on the screen. However I noticed the
following:
. the db_name was not changed in spfile.  (verified using pfile create
pfile='/tmp/whatever.ora" from spfile)
. the standby's alert log shows ORA-19953.
. did not see the following message in the alert log.
    *** DBNEWID utility started ***
     DBID will be changed from 3890508598 to new DBID of 70593532 for
database ORCL10
     DBNAME will be changed from ORCL10 to new DBNAME of ORCL10S
     Starting datafile conversion
    ...
- verified that spfile is writable as the changes to archive_dest_3 was
effective in spfile.
- performed "alter system set db_name='TCIPvl' scope=spfile sid='*' ' on
standby
- shutdown standby, then startup mount
  got ORA-1103 "database name '%s' in control file is not '%s' on the command
line.

WORKAROUND:
-----------

RELATED BUGS:
-------------

REPRODUCIBILITY:
----------------
at ct site.

TEST CASE:
----------

STACK TRACE:
------------

SUPPORTING INFORMATION:
-----------------------
- alert logs from primary and standby, as well as the pfile from the standby
after "recover to logical standy.." was excuted.
- The converting physical-> logical work was done between 2009 12/11 12:30 -
13:10

24 HOUR CONTACT INFORMATION FOR P1 BUGS:
----------------------------------------

DIAL-IN INFORMATION:
--------------------

IMPACT DATE:
------------

*** 12/11/09 12:58 pm ***
*** 12/11/09 12:58 pm *** (CHG: Sta->16)
*** 12/11/09 01:00 pm *** (CHG: Sta->10)
*** 01/08/10 12:44 pm ***
*** 01/12/10 10:55 am *** (CHG: Sta->33)
*** 02/04/15 11:54 pm ***
*** 02/04/15 11:54 pm ***
*** 02/04/15 11:54 pm ***

描述是Linux x86-64位的10.2.0.4,但我這是10.2.0.5，與現象與這個BUG相同。上面給出的論斷步驟如下:

The following outlines the steps:
- Verified that primary and physical standby are in sync. (around 2009 12/11
12:30)
- stopped recovery at physical standby (Fri Dec 11 12:35:10 2009)
- build dictionary on primary  (Fri Dec 11 12:55:29 2009 log seq 9976)
   SQL> DBMS_LOGSTDBY.BUILD;
- switched logs on primary (all instances 3 times)

在主庫中執行DBMS_LOGSTDBY.BUILD建立資料字典後，在主庫執行日誌切換三次（因為預設有三組重做日誌組，如果是RAC，每個例項都要執行三次）以確保建立的資料字典傳輸同物理備庫。

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter database recover to logical standby test;

Database altered.

轉換成功，alert.log內容如下：

alter database recover to logical standby test
Sun Jun 28 20:12:29 CST 2015
Media Recovery Start: Managed Standby Recovery (test_ldg)
Sun Jun 28 20:12:29 CST 2015
Managed Standby Recovery not using Real Time Apply
Media Recovery Log /u03/app/oracle/archive/test_ldg/1_71_876665479.dbf
Media Recovery Log /u03/app/oracle/archive/test_ldg/1_72_876665479.dbf
Media Recovery Log /u03/app/oracle/archive/test_ldg/1_73_876665479.dbf
Sun Jun 28 20:12:31 CST 2015
Incomplete Recovery applied until change 722225
Sun Jun 28 20:12:31 CST 2015
Media Recovery Complete (test_ldg)
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 722225
Resetting resetlogs activation ID 2174774786 (0x81a06e02)
Online log /u03/app/oracle/oradata/test_ldg/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u03/app/oracle/oradata/test_ldg/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u03/app/oracle/oradata/test_ldg/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 722223
Sun Jun 28 20:12:34 CST 2015
Setting recovery target incarnation to 3
Sun Jun 28 20:12:34 CST 2015
Converting standby mount to primary mount.
Sun Jun 28 20:12:34 CST 2015
ACTIVATE STANDBY: Complete - Database mounted as primary (test_ldg)
*** DBNEWID utility started ***
DBID will be changed from 2174811906 to new DBID of 2181762994 for database TEST
DBNAME will be changed from TEST to new DBNAME of TEST
Starting datafile conversion
kcv_lh_or_upgrade: 10.2 upgrading 1 incarnations
Setting recovery target incarnation to 1
Datafile conversion complete
Failed to find temporary file: /u03/app/oracle/oradata/test_ldg/temp01.dbf
Database name changed to TEST.
Modify parameter file and generate a new password file before restarting.
Database ID for database TEST changed to 2181762994.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open with RESETLOGS option.
Succesfully changed database name and ID.
*** DBNEWID utility finished succesfully ***
Completed: alter database recover to logical standby test
Sun Jun 28 20:12:44 CST 2015
destination database instance is 'started' not 'mounted'

從上面的Completed: alter database recover to logical standby test可以確認將test資料庫從物理備為轉換為了邏輯備庫。