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数据库从物理备为转换为了逻辑备库。