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