oracle 逻辑备库跳过,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)内容如下：

TypeB - DefectFixed in Product Version

Severity2 - Severe Loss of ServiceProduct Version10.2.0.4

Status33 - Suspended, Req‘d Info not AvailPlatform226 - Linux x86-64

Created11-Dec-2009Platform VersionRED HAT ENTERPRISE LINUX 5

Updated05-Feb-2015Base BugN/A

Database Version10.2.0.4Affects PlatformsGeneric

Product SourceOracleKnowledge, Patches and Bugs related to this bug

Related Products

LineOracle Database ProductsFamilyOracle Database Suite

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

原文：http://blog.itpub.net/26015009/viewspace-1715438/