Question on compatible parameter in init.ora

From: Internet <cboulord_at_club-internet.fr>
Date: Wed, 16 Dec 1998 23:52:27 +0100
Message-ID: <367839AA.211744BC@club-internet.fr>

We've upgrade Oracle Database from 7.1.2 to 7.3.0 (and upgrade HPUX and some Telecom Applications).
During this procedure we have disable a parameter in "init.ora" file (#compatible 7.3.0.0).
Now if we uncomment this compatible parameter, we cannot start the Database.

We found the following bug report on Oracle Web Site :

Startup after 'COMPATIBLE' parameter change

This article describes a problem reported in bug# 382576 which may cause a problem starting up a database after changing the 'COMPATIBLE' init.ora parameter and there are active transactions. This bug has been fixed in version 7.3.3.

Description

If the init.ora parameter COMPATIBLE is changed to 7.3.0 or higher it is possible that the user will not be able to open the database if instance recovery is required. The startup will have a problem either with compatible set to 7.3 or with the old, lower version. This happens when the database is shutdown with unrecovered transactions at a compatible version lower than 7.3.0, and an attempt is made to open the database with a compatible version of 7.3.0 or higher. Opening the database will fail.
At this point the database controlfile and logfile version have already been updated to the higher version. Setting the compatible parameter back to a version lower than 7.3.0 and attempting to open the database will therefore fail as well.

This problem can occur regardless of what version of the database you upgrade from to 7.3.0. In addition, a new database created under 7.3.0 will have the default compatible parameter set to 7.0, so changing this parameter under these circumstances can cause the problem to occur.

In other words, regardless of where the database came from, there should be no active transactions when changing the COMPATIBLE parameter in 7.3.0.

Versions Affected

This problem occurs in V7.3.2.2 and is fixed in V7.3.3.

Possible Symptoms

Upon startup of the database after the compatible parameter change, the following errors may arise:

ORA-01592: error converting Version 6 rollback segment (0) to Oracle7 format
ORA-00600: internal error code, arguments: [4043], [10], [3], [], [], [], [],

If you change the compatible parameter back to the original value and restart the database, you may see the following errors:

ORA-00201: control file version 7.3.0.0.0 incompatible with ORACLE version 7.00
ORA-00202: control file: '<PATH>/ctlSID_1.ctl'

In a OPS environment the bug manifests with the following errors: ORA-331 log version 0.0.0.0.0 incompatible with oracle version 7.3.0.0.0

Workaround

The only workaround is to prevent your database from getting into this state where it cannot be opened. To prevent this take the following actions (NOTE: these are recommended steps to ALWAYS take when changing the COMPATIBLE
parameter):

Take a good backup of the database prior to upgrade
Backup the database asap on the 7.3 release Ideally, a cold backup is preferable, if this isn't possible completion of a hot backup should be a high priority. This prevents any problems if recovery on 7.3 is feasible. If old version needs to be restored, then you need to retrieve an old backup and you may also need to do point in time recovery.
Make sure there are no unrecovered transactions in the database. In other words do a shutdown normal of the database and make sure you do not have any in-doubt transactions, before changing the compatible parameter to 7.3.0 or higher.

Transactions are left unrecovered under the following circumstances: 1. a shutdown abort was executed.
2. a shutdown normal or immediate was executed with in-doubt transactions.

To ensure there are no unrecovered transactions in the database, you can take the following steps:

1. connect internal
2. select * from dba_2pc_pending;
3. if the above command returns any rows, force the in-doubt

transactions to commit or abort. (See the description of COMMIT FORCE in the Distributed Systems manual)
4. shutdown normal your database.

Until the last operation, we cannot use some 7.3.0 fonctionality like rezise tablespace.

By the command :
select * from v$compatibility;

You can see which part of your database that was not upgrade to 7.3.0.

Bad

Good                                    Description
___________________     ___________________
_______________________
CONTROLF 7.0.9.0.0            CONTROLF 7.3.0.0.0         Control file
KCBCICT 0.0.0.0.0                 KCBCICT 7.1.5.0.0             Cache
cross-instance protocol
REDODATA 7.0.12.0.0         REDODATA 7.3.0.0.0             Redo contents

UNDODATA 7.0.11.0.0         UNDODATA 7.3.0.0.0             Undo contents

LOGSTRUC 7.0.9.0.0             LOGSTRUC 7.2.0.0.0             Log
structure
RCVREDO 7.0.11.0.0             RCVREDO 7.3.0.0.0
Recovery redo
UNLMTEXT 0.0.0.0.0             UNLMTEXT 7.3.0.0.0
unlimited extents
TRUNCFIX 0.0.0.0.0                 TRUNCFIX 7.2.0.0.0
truncate bug fix
TRUNCNT 0.0.0.0.0                 TRUNCNT 7.3.0.0.0

truncation count

If some of you had this problem or resolved it, I'm very interesting by your feedback.

Thanks a lot. Received on Wed Dec 16 1998 - 16:52:27 CST