DB shutdown

[Former Member]

Hi Gurus,

While doing some house keeping, accidentally /oracle/SID/saparch/cntrl/cntrlSID.dbf got moved / deleted.

We were not aware of this until after 3 days we were informed by the backoffice team regarding the failed backups.

Upon investigation we found out that the said control file was missing and thus the backups were failing

So we copied the control file from ORIGLOG and kept it in SAPARCH !!

After copying this file, there was an usual system checkpoint and then the DB was shutdown

Now the alert file has no details of the instance shutdown but one of the trace file mentions that after the checkpoint, Oracle found out that the SAPARCH control file's version was different than ORIGLOG's control and thus it terminated the instance

Alert log mentions : Apr 30 11:00:49 2014 opiodr aborting process unknown ospid (15521) as a result of ORA-1092

TRC file mentions :

Error: kccchb_6

Control file sequence number mismatch!

fhgcsq: 890352 high_i: 0

SGA csq: 890361 lseqno: 890352 low_i: 0 hseqno: 890352

*** 2014-04-30 11:00:49.420

USER (ospid: 22482): terminating the instance

My question over here is, does Oracle shutdown the DB if it notices mismatched control file in checkpoint

OR something else has caused the shutdown

R..../-

[Former Member · Accepted solution]

You should have done the copy of the control file in offline.

[Former Member · Accepted solution]

Hi Sunil,

Agreed we should have done the file copy offline but can the DB shutdown after it notices mismatch in the control file versions

Please advice

R..../-

[former_member188883 · Accepted solution]

Hi Sandy,

Oracle keeps controlfile in 3 locations and all these files will have same timestamp when DB is running.

Incase we change/replace the controlfile while DB is online, this replaced control file will have different timestamp than others are result in error.

So to replace a controlfile you need to make DB and SAP down in-order to maintain same timestamp across 3 locations.

Hope this helps.

Regards,

Deepak Kori

[Former Member · Accepted solution]

Hi Deepak,

Thanks for the info

But I think, I should re-phrase my question over here

Agree, this activity should have done in down time and not when the DB is online.

But now this file copying is already done online and then there was an DB shutdown

My question is : Is the automatic shutdown of DB due to this online control file copy, a normal & expected behavior or an abnormal behavior of the DB

R..../-

[former_member188883 · Accepted solution]

Hi Sandy,

 Is the automatic shutdown of DB due to this online control file copy, a normal & expected behavior or an abnormal behavior of the DB

Copying of control file will not initiate DB shutdown. Please check alert_<SID>.log file and other trace files for more details on why DB was shutdown.

Hope this helps.

Regards,

Deepak Kori

[Reagan · Accepted solution]

Hello

My question over here is, does Oracle shutdown the DB if it notices mismatched control file in checkpoint

Control files are multiplexed. If there is a mismatch in the control files and if you issue a startup command then the database will be terminated.

But if you had issued a startup mount then you would have possibly got the control file mismatch error with the time stamp of the control files. I have experienced it.

In that case you will remove the one with the old time stamp and put a copy of the latest one.

Regards

RB

[Former Member · Accepted solution]

Hi Deepak,

Unfortunately alert.log has not registered anything except the mentioned in initial post

The trc file generated at that time mentions :

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORACLE_HOME = /oracle/SID/112_64

System name:    HP-UX

Node name:      EPDBCI

Release:        B.11.31

Version:        U

Machine:        ia64

Instance name: SID

Redo thread mounted by this instance: 1

Oracle process number: 83

Unix process pid: 22482, image: oracle@EPDBCI

R..../-

*** 2014-04-30 11:00:49.418

*** SESSION ID:(1158.11617) 2014-04-30 11:00:49.418

*** CLIENT ID:() 2014-04-30 11:00:49.418

*** SERVICE NAME:(SID) 2014-04-30 11:00:49.418

*** MODULE NAME:(sqlplus@EPDBCI (TNS V1-V3)) 2014-04-30 11:00:49.418

*** ACTION NAME:() 2014-04-30 11:00:49.418

********************* ATTENTION: ********************

The controlfile header block returned by the OS

has a sequence number that is too old.

The controlfile might be corrupted.

PLEASE DO NOT ATTEMPT TO START UP THE INSTANCE

without following the steps below.

RE-STARTING THE INSTANCE CAN CAUSE SERIOUS DAMAGE

TO THE DATABASE, if the controlfile is truly corrupted.

In order to re-start the instance safely,

please do the following:

(1) Save all copies of the controlfile for later

     analysis and contact your OS vendor and Oracle support.

(2) Mount the instance and issue:

     ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

(3) Unmount the instance.

(4) Use the script in the trace file to

     RE-CREATE THE CONTROLFILE and open the database.

*****************************************************

Error: kccchb_6

Control file sequence number mismatch!

fhgcsq: 890352 high_i: 0

SGA csq: 890361 lseqno: 890352 low_i: 0 hseqno: 890352

*** 2014-04-30 11:00:49.420

USER (ospid: 22482): terminating the instance

[Former Member · Accepted solution]

Hi Benjamin,

No startup shutdown or alter commands were issues

The DB came down abruptly with an message "Control file sequence number mismatch!"

R..../-

[Reagan · Accepted solution]

If you have 3 controlfiles and if one is deleted then there is no mismatch issue as the other 2 controlfiles are good.

But you copied the control file from location A to location B when the database is running and in that case the database now has one out of 3 which is not in sync with the other two. For me the database will get terminated as the database will not try to get the copied controlfile in sync with the other two.

The database will be terminated as there is an inconsistency which should be corrected.

Regards

RB