I have recently had some very wierd behavior with a standby database under
Oracle 7.3.4 which required me to generate a new controlfile. Metalink
will probably not give me any useful information about this problem, and I
hope that the newsgroup can shed some light.

To start out, I'm not interested in advice on upgrading my database
version, so let's please omit any such discussion. I don't need a lecture
about unsupported releases.

That said, all of these techniques are still in use in later releases, so
I hope that someone here has seen/dealt with these problems before.

I am activating my Oracle 7 standby databases as part of a disaster test
(so the pressure is not quite as intense as a tornado wisking my
production servers away to valhalla).

I am running Oracle 7.3.4 on HP-UX 10.20:

Starting up ORACLE RDBMS Version: 7.3.4.4.1.

Here are the sections of the alert log where I point-in-time-recovered and
activated the standby database (with no problem):

Fri May 13 12:33:34 2005
alter database mount standby database
Fri May 13 12:33:34 2005
Successful mount of redo thread 1.
Fri May 13 12:33:34 2005
Completed: alter database mount standby database
Fri May 13 12:34:09 2005
ALTER DATABASE RECOVER standby database until time '2005-05-10:19:30:00'
Fri May 13 12:34:09 2005
Media Recovery Start
Media Recovery Log
ORA-279 signalled during: ALTER DATABASE RECOVER standby database until ti...
...
Fri May 13 12:41:25 2005
Media Recovery Log /pkg/prdqry/oracle/arch/arch.log1_208380.dbf
Fri May 13 12:42:03 2005
Incomplete recovery done UNTIL CHANGE 6567443488649
Media Recovery Complete
Completed: ALTER DATABASE RECOVER CONTINUE DEFAULT
Shutting down instance (normal)
...
Tue May 17 09:24:05 2005
alter database mount standby database
Tue May 17 09:24:05 2005
Successful mount of redo thread 1.
Tue May 17 09:24:05 2005
Completed: alter database mount standby database
Tue May 17 09:25:01 2005
alter database activate standby database
Tue May 17 09:25:03 2005
RESETLOGS after incomplete recovery UNTIL CHANGE 6567443488649
Tue May 17 09:25:04 2005
Errors in file /pkg/prdqry/oracle/udump/ora_5099.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/pkg/prdqry/data/redo1.log'
ORA-07360: sfifi: stat error, unable to obtain information about file.
HP-UX Error: 2: No such file or directory
Tue May 17 09:25:08 2005
Errors in file /pkg/prdqry/oracle/udump/ora_5099.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/pkg/prdqry/data/redo2.log'
ORA-07360: sfifi: stat error, unable to obtain information about file.
HP-UX Error: 2: No such file or directory
Tue May 17 09:25:16 2005
Errors in file /pkg/prdqry/oracle/udump/ora_5099.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/pkg/prdqry/data/redo3.log'
ORA-07360: sfifi: stat error, unable to obtain information about file.
HP-UX Error: 2: No such file or directory
Tue May 17 09:25:26 2005
Completed: alter database activate standby database
Tue May 17 09:28:59 2005
Shutting down instance (immediate)

However, when I try to startup this database, startup fails with a
controlfile error (ORA-1122). I've included the text of the error:

$ oerr ora 1122
01122, 00000, "database file %s failed verification check"
// *Cause: The information in this file is inconsistent with information
// from the control file. See accompanying message for reason.
// *Action: Make certain that the db files and control files are the correct
// files for this database.


Tue May 17 10:54:03 2005
alter database mount
Tue May 17 10:54:03 2005
Successful mount of redo thread 1.
Tue May 17 10:54:03 2005
Completed: alter database mount
Tue May 17 10:54:07 2005
alter database open
ORA-1122 signalled during: alter database open...

Unfortunately, I did not preserve the full error text that I saw in
svrmgrl, but it was a complaint about the controlfile mismatch with my
INDEX16M tablespace. I have had the standby successfully start in the past
by dropping the offending tablespace, so I tried dropping the datafile
(since everything in it can be recreated easily) but startup then gives
the same complaint about another datafile:

Tue May 17 10:55:34 2005
alter database datafile '/pkg/prdqry/data/index16m-d.dat' offline drop
Tue May 17 10:55:34 2005
Completed: alter database datafile '/pkg/prdqry/data/index16m...
Tue May 17 10:55:44 2005
alter database open
Tue May 17 10:55:44 2005
ORA-1122 signalled during: alter database open...

At this point (dreading the futility of opening a TAR on Metalink), I
decided to generate a new controlfile:

Tue May 17 10:57:53 2005
alter database backup controlfile to trace
Completed: alter database backup controlfile to trace
Tue May 17 11:00:21 2005
Shutting down instance (normal)
...
Tue May 17 11:17:11 2005
CREATE CONTROLFILE REUSE DATABASE "PRDQRY" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 1022
MAXINSTANCES 1
MAXLOGHISTORY 100
LOGFILE
GROUP 1 (
'/pkg/prdqry/oracle/data/redo1.log',
...
'/pkg/prdqry/data/index16m-d.dat',
'/pkg/prdqry/data/salesdss_crtfctn_data_f.dbf'
Tue May 17 11:17:12 2005
Successful mount of redo thread 1.
Tue May 17 11:17:12 2005
Completed: CREATE CONTROLFILE REUSE DATABASE "PRDQRY" NORESET...

At this point, the database can be opened normally (and the datafile that
I previously dropped is now back [after editing the trace and removing
the drop]).

Tue May 17 11:17:17 2005
alter database open
Tue May 17 11:17:17 2005
Thread 1 advanced to log sequence 2
Current log# 1 seq# 2 mem# 0: /pkg/prdqry/oracle/data/redo1.log
Current log# 1 seq# 2 mem# 1: /pkg/prdqry/data/redo1.log
Thread 1 opened at log sequence 2
Current log# 1 seq# 2 mem# 0: /pkg/prdqry/oracle/data/redo1.log
Current log# 1 seq# 2 mem# 1: /pkg/prdqry/data/redo1.log
Successful open of redo thread 1.
Tue May 17 11:17:17 2005
SMON: enabling cache recovery
Tue May 17 11:17:18 2005
Dictionary check beginning
Dictionary check complete
Tue May 17 11:17:19 2005
SMON: enabling tx recovery
Tue May 17 11:17:19 2005
Completed: alter database open

Why is this happening? What was wrong with the standby controlfile that
was activated? Why was the problem so easily rectified with a controlfile
built from a trace? Is this normal behavior?

p.s. About a month ago, I did need to run catalog and catproc on this
instance to resolve a circular compile dependency between dbms_sql
and dbms_utility. This database has been otherwise quiet from a
support perspective.

---------------------------------------------------------------------------
/ Charles J. Fisher | "Fascism should more properly be called /
/ cfisher@rhadmin.org | corporatism, since it is the merger of state /
/ http://rhadmin.org | and corporate power." -- Mussolini /
---------------------------------------------------------------------------

Does the /pkg/prdqry/data patrh exist on the standby server?


Regards,

Steve

Sorry, I just saw that the directory does exist later in the post.


Regards,


Steve