Oracle doesnot start after database refresh

[Former Member]

Hi

I have performed database restore for BI system. After successful restore when i started database im getting following error.

SQL> startup

ORACLE instance started.

Total System Global Area 1996488704 bytes

Fixed Size                  2066816 bytes

Variable Size            1006634624 bytes

Database Buffers          973078528 bytes

Redo Buffers               14708736 bytes

ORA-00205: error in identifying control file, check alert log for more info

Following are the errors in alter log file located at D:\oracle\KPD\saptrace\background\alert_kpd

db_name                  = KPD

  open_cursors             = 800

  _sort_elimination_cost_ratio= 10

  _b_tree_bitmap_plans     = FALSE

  star_transformation_enabled= true

  query_rewrite_enabled    = false

  _index_join_enabled      = FALSE

  _optim_peek_user_binds   = FALSE

  pga_aggregate_target     = 629145600

  _optimizer_mjc_enabled   = FALSE

PMON started with pid=2, OS id=17492

PSP0 started with pid=3, OS id=35420

MMAN started with pid=4, OS id=22992

DBW0 started with pid=5, OS id=26032

LGWR started with pid=6, OS id=2100

CKPT started with pid=7, OS id=21008

SMON started with pid=8, OS id=8964

RECO started with pid=9, OS id=22932

CJQ0 started with pid=10, OS id=22676

MMON started with pid=11, OS id=18976

MMNL started with pid=12, OS id=21448

Mon Oct 08 19:21:45 2012

ALTER DATABASE   MOUNT

Mon Oct 08 19:21:45 2012

ORA-00202: control file: 'D:\ORACLE\KPD\ORIGLOGA\CNTRL\CNTRLKPD.DBF'

ORA-27047: unable to read the header block of file

OSD-04001: invalid logical block size (OS 538984480)

Mon Oct 08 19:21:45 2012

ORA-205 signalled during: ALTER DATABASE   MOUNT...

Kindly help me to solve this issue.

[Former Member]

Hi Kranthi,

Hope you edit the first line in control.sql before creating the CONTROL FILES. It should be like

STARTUP NOMOUNT

CREATE CONTROLFILE SET DATABASE "TARGET SID" RESETLOGS ARCHIVELOG

If yes then login to sqlplus run

SQL> create pfile from spfile;

under $ORACLE_HOME\database find the init<SID>.ora file

check the location of the control file.

Thanks

Ramesh Nair

[Former Member]

Hi Ramesh Nair,

following is output from initsid

*.control_files='D:\oracle\KPD\origlogA\cntrl\cntrlKPD.dbf','D:\oracle\KPD\origlogB\cntrl\cntrlKPD.dbf','D:\oracle\BPD\sapdata1\cntrl\cntrlKPD.dbf'

[JPReyes]

*.control_files='D:\oracle\KPD\origlogA\cntrl\cntrlKPD.dbf','D:\oracle\KPD\origlogB\cntrl\cntrlKPD.dbf','D:\oracle\BPD\sapdata1\cntrl\cntrlKPD.dbf'

You have 2 different SID's there   KPD and BPD.

[Former Member]

Hi Kranthi,

What is your "TARGET SID" KPD or  BPD. Suppose if your SID is KPD, please make the change in initsid

.control_files='D:\oracle\KPD\origlogA\cntrl\cntrlKPD.dbf',

'D:\oracle\KPD\origlogB\cntrl\cntrlKPD.dbf','

D:\oracle\KPD\sapdata1\cntrl\cntrlKPD.dbf'

go to that location and change the SID to KPD (D:\oracle\KPD\sapdata1)

login to sqlplus

create spfile from pfile;

Thanks

Ramesh Nair

[Former Member]

HI Kranthi,

Two scenarios.

1. After Creating control file you might have added datafile and that entry is added in control file.

2.Your datafile is corrupt.

Did you restore from offline backup?

Sid

[Former Member]

Yes, I have restored it from offine backup.

My control files are located at following locations

SQL> show parameter control_files

NAME                                 TYPE

------------------------------------ ---------------------------------

VALUE

------------------------------

control_files                        string

D:\ORACLE\KPD\ORIGLOGA\CNTRL\CNTRLKPD.DBF,

D:\ORACLE\KPD\ORIGLOGB\CNTRL\CNTRLKPD.DBF,

D:\ORACLE\KPD\SAPDATA1\CNTRL\CNTRLKPD.DBF

[Former Member]

What method of restore you are used?

Regards

Roman

[paul_power]

Hello Kranthi,

Please refer to SAP note 491160, point 10 and 11 for more details.

It does seem the control file is corrupt.

regards,

Paul

[Former Member]

Looks like the file  'D:\ORACLE\KPD\ORIGLOGA\CNTRL\CNTRLKPD.DBF' got corrupted.

The control files is located in three different location, you can try to copy from another location. Check init<SID>.ora file for the location of control file.