Control files issue

[Former Member]

Hello,

I am doing system copy (new sandbox) , and I have some troubles with the Oracle start.

SQL> startup

ORACLE instance started.

Total System Global Area 1929379840 bytes

Fixed Size 2097344 bytes

Variable Size 452988736 bytes

Database Buffers 1459617792 bytes

Redo Buffers 14675968 bytes

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

-

---

SQL> show parameter control_files;

NAME TYPE

-

---

-

---

VALUE

-

---

control_files string

/oracle/SID/origlogA/cntrl/cnt

rlSID.dbf, /oracle/SID/origlog

B/cntrl/cntrlSID.dbf, /oracle/

SID/sapdata1/cntrl/cntrlSID.db

f

-

---

but I have no control files in those folders.

/origlogA/cntrl:] ls -lart

total 0

drwxr-xr-x 4 orasid dba 256 Sep 11 10:57 ..

drwxrwxrwx 2 root system 256 Sep 13 19:00 .

-

---

What can I do ?

Should I copy all the cntrlSID.dbf from the old server in the new one and rename the files and all their content with the new SID instead of the old one ?

Please help,

Jordan.

Edited by: Jordy_tof on Sep 14, 2011 9:57 AM

Edited by: Jordy_tof on Sep 14, 2011 10:02 AM

[Former Member · Accepted solution]

Hi,

Which method do you use for the system refresh? As far as I understand, in a system refresh - we copy the data and log files onto the target and have to run the modified control.sql which actually creates the control files on the target system.

Can provide you more details if we know what method have you used.

Regards,

Srikishan

[Former Member · Accepted solution]

Hi,

How to find the control.sql ?

Yes, I have copied the data and the log files on the target system.

but I did not know anything about the control.sql. How can I find ? or where ?

Thanks,

J.

[Former Member · Accepted solution]

Hi,

the control file can be generated from the source system using the 'alter' command. This generates a file which needs to be copied to the target host and modified so as to contain the target SID details.

Detailed steps here: http://www.sap-img.com/basis/system-copy.htm

Please also check http://basissap.blogspot.com/, topic SAP Test Refresh. Pay special attention to step 17.

Regards,

Srikishan

[Former Member]

Hai...

There are two options

1) Recheck the control files from other locations also and if exist then copy it to all locations

i.e. /oracle/<SID>/sapdat1/cntrl

/oracle/<SID>/mirrorlogA/cntrl

/oracle/<SID>/mirrorlogB/cntrl

and then try to restart the database.

2) Your have to recreate the control files by direct or to trace

ALTER DATABASE BACKUP CONTROLFILE TO TRACE, which generates a SQL script in the trace file to re-create the controlfile. If your database contains any read-only or temporary tablespaces, then that SQL script will also contain all the necessary SQL statements to add those files back into the database. Please refer to the ALTER DATABASE "BACKUP CONTROLFILE Clause" for information creating a script based on an existing database controlfile.

i.e. SQL> alter database backup controlfile to trace as '/tmp/ctrl.trc';

rename the trace file to required format, and try to restart.

Cheers

KHS

[Former Member]

Hi all,

I have followed the steps there, and now I am stucked in:

SQL> @/oracle/SID/sapbackup/refresh_SID.sql

CREATE CONTROLFILE SET DATABASE "SID" RESETLOGS NOARCHIVELOG

*

ERROR at line 1:

ORA-01503: CREATE CONTROLFILE failed

ORA-01565: error in identifying file

'/oracle/SID/sapdata1/system_1/system.data1'

ORA-27037: unable to obtain file status

IBM AIX RISC System/6000 Error: 2: No such file or directory

Additional information: 3

-

---

Insides of the script:

root:/oracle/SID/sapbackup:] cat refresh_SID.sql

CREATE CONTROLFILE SET DATABASE "SID" RESETLOGS FORCE LOGGING ARCHIVELOG

MAXLOGFILES 255

MAXLOGMEMBERS 3

MAXDATAFILES 254

MAXINSTANCES 50

MAXLOGHISTORY 1168

LOGFILE

GROUP 1 (

'/oracle/SID/origlogA/log_g11m1.dbf',

'/oracle/SID/mirrlogA/log_g11m2.dbf'

) SIZE 250M,

GROUP 2 (

'/oracle/SID/origlogB/log_g12m1.dbf',

'/oracle/SID/mirrlogB/log_g12m2.dbf'

) SIZE 250M,

GROUP 3 (

'/oracle/SID/origlogA/log_g13m1.dbf',

'/oracle/SID/mirrlogA/log_g13m2.dbf'

) SIZE 250M,

GROUP 4 (

'/oracle/SID/origlogB/log_g14m1.dbf',

'/oracle/SID/mirrlogB/log_g14m2.dbf'

) SIZE 250M

-- STANDBY LOGFILE

DATAFILE

'/oracle/SID/sapdata1/system_1/system.data1',

'/oracle/SID/sapdata1/undo_1/undo.data1',

'/oracle/SID/sapdata1/sysaux_1/sysaux.data1',

'/oracle/SID/sapdata1/sr3_1/sr3.data1',

'/oracle/SID/sapdata1/sr3_2/sr3.data2',

'/oracle/SID/sapdata1/sr3700_1/sr3700.data1',

'/oracle/SID/sapdata1/sr3700_2/sr3700.data2',

'/oracle/SID/sapdata1/sr3usr_1/sr3usr.data1',

'/oracle/SID/sapdata1/sr3700_3/sr3700.data3',

'/oracle/SID/sapdata1/tivoliorts_1/tivoliorts.dbf',

'/oracle/SID/sapdata1/sr3_3/sr3.data3',

'/oracle/SID/sapdata1/sr3_4/sr3.data4',

'/oracle/SID/sapdata1/sr3_5/sr3.data5',

'/oracle/SID/sapdata1/sr3_6/sr3.data6',

'/oracle/SID/sapdata1/sr3_7/sr3.data7',

'/oracle/SID/sapdata1/sr3700_4/sr3700.data4',

'/oracle/SID/sapdata1/sr3700_5/sr3700.data5',

'/oracle/SID/sapdata1/sr3700_6/sr3700.data6',

'/oracle/SID/sapdata1/sr3700_7/sr3700.data7',

'/oracle/SID/sapdata1/sr3_8/sr3.data8'

CHARACTER SET UTF8

;

-

---

Indeed file /oracle/SID/sapdata1/system_1/system.data1 does not exist in my directory.

Should I move all the files from source to target ? and modify the SID ?

Thanks in advance,

J.

Edited by: Jordy_tof on Sep 14, 2011 11:00 AM

[Former Member]

But you said you had already copied the datafiles to the target, right ? Then these files should exist there in target now.

And what is this 'SID' here in .sql file ? Replace it with exact target DB SID. Then oracle will be able to find these files as existing.

Thanks

[andreas_herzog]

Which SID does the system have? i doubt that it uses "SID"...

you have to use the appropriate system id in all the scripts

eg.:

CREATE CONTROLFILE SET DATABASE T01 RESETLOGS NOARCHIVELOG

if your system uses T01 als system id (and so on...)

GreetZ, AH

[Former Member]

Hello,

Of course, I changed SID to the proper one, target system.

I am using brrestore to restore the db,

so I think that these files:

/oracle/SID/sapdata1/system_1/system.data1',

'/oracle/SID/sapdata1/undo_1/undo.data1',

'/oracle/SID/sapdata1/sysaux_1/sysaux.data1',

'/oracle/SID/sapdata1/sr3_1/sr3.data1',

'/oracle/SID/sapdata1/sr3_2/sr3.data2',

'/oracle/SID/sapdata1/sr3700_1/sr3700.data1',

'/oracle/SID/sapdata1/sr3700_2/sr3700.data2',

'/oracle/SID/sapdata1/sr3usr_1/sr3usr.data1',

'/oracle/SID/sapdata1/sr3700_3/sr3700.data3',

'/oracle/SID/sapdata1/tivoliorts_1/tivoliorts.dbf',

'/oracle/SID/sapdata1/sr3_3/sr3.data3',

'/oracle/SID/sapdata1/sr3_4/sr3.data4',

'/oracle/SID/sapdata1/sr3_5/sr3.data5',

'/oracle/SID/sapdata1/sr3_6/sr3.data6',

'/oracle/SID/sapdata1/sr3_7/sr3.data7',

'/oracle/SID/sapdata1/sr3700_4/sr3700.data4',

'/oracle/SID/sapdata1/sr3700_5/sr3700.data5',

'/oracle/SID/sapdata1/sr3700_6/sr3700.data6',

'/oracle/SID/sapdata1/sr3700_7/sr3700.data7',

'/oracle/SID/sapdata1/sr3_8/sr3.data8'

should be created after restore, so I have deleted them from the system.

I am using:

brrestore -p /oracle/new_SID/102_64/dbs/init_oldSID_weekly.sap -b begsxivt.fnr -c force -m full

but I have tens of alerts

-

---

BR0401I BRRESTORE 7.10 (30)

BR0405I Start of file restore: regtquoh.rsb 2011-09-13 15.50.11

BR0484I BRRESTORE log file: /oracle/NEW_SID/sapbackup/regtquoh.rsb

BR0454W Values of oracle_sid are different: current 'NEW_SID', backup begsxivt.fnr 'OLD_SID'

BR0455W Value 'NEW_SID' of oracle_sid will be used for restore

BR0454W Values of oracle_home are different: current '/oracle/NEW_SID/102_64', backup begsxivt.fnr '/oracle/OLD_SID/102_64'

BR0455W Value '/oracle/NEW_SID/102_64' of oracle_home will be used for restore

BR0454W Values of sapdata_home are different: current '/oracle/NEW_SID', backup begsxivt.fnr '/oracle/OLD_SID'

BR0455W Value '/oracle/NEW_SID' of sapdata_home will be used for restore

BR0427I Files from the old sapdata_home /oracle/OLD_SID will be restored into the new sapdata_home /oracle/NEW_SID

BR0564W File /oracle/OLD_SID/sapbackup/cntrlOLD_SID.dbf cannot be restored with other data files using RMAN

BR0564W File /oracle/OLD_SID/origlogA/cntrl/cntrlOLD_SID.dbf cannot be restored with other data files using RMAN

BR0564W File /oracle/OLD_SID/origlogB/cntrl/cntrlOLD_SID.dbf cannot be restored with other data files using RMAN

BR0564W File /oracle/OLD_SID/sapdata1/cntrl/cntrlOLD_SID.dbf cannot be restored with other data files using RMAN

BR0564W File /oracle/OLD_SID/oraarch/OLD_SIDarch1_22148_690367395.dbf cannot be restored with other data files using RMAN

BR0564W File /oracle/OLD_SID/oraarch/OLD_SIDarch1_22149_690367395.dbf cannot be restored with other data files using RMAN

BR0567I These files can be restored in a separate BRRESTORE run

BR0565W Due to running database instance control file cannot be restored during a BRRESTORE run with RMAN

BR0566I Stop the database instance and restore the control file separately

BR0456I Probably the database must be recovered due to restore from online backup

BR0280I BRRESTORE time stamp: 2011-09-13 15.50.11

BR0407I Restore of database: NEW_SID

BR0408I BRRESTORE action ID: regtquoh

BR0409I BRRESTORE function ID: rsb

BR0449I Restore mode: FULL

BR0419I Files will be restored from backup: begsxivt.fnr 2011-09-09 16.52.29

BR0416I 20 files found to restore, total size 182808.141 MB

BR0424I Files will not be old_SIDompressed

BR0421I Restore device type: rman_util

BR0134I Unattended mode with 'force' active - no operator confirmation allowed

BR0280I BRRESTORE time stamp: 2011-09-13 15.50.11

BR0554I Starting restore from full database backup using RMAN...

BR0278E Command output of 'SHELL=/bin/sh /oracle/NEW_SID/102_64/bin/rman nocatalog':

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Sep 13 15:50:11 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

RMAN>

RMAN> connect target *

connected to target database: NEW_SID (not mounted)

using target database control file instead of recovery catalog

RMAN> *end-of-file*

RMAN>

host command complete

RMAN>

sql statement: alter session set optimizer_mode=RULE

RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23> 24>

allocated channel: sbt_1

channel sbt_1: sid=287 devtype=SBT_TAPE

channel sbt_1: Data Protection for SAP(R)

executing command: SET NEWNAME

released channel: sbt_1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of set command at 09/13/2011 15:50:18

ORA-01507: database not mounted

RMAN>

Recovery Manager complete.

BR0200I BR_TRACE: location BrRmanCall-44, commands for RMAN in: /oracle/NEW_SID/sapbackup/.regtquoh.cmd

'@/oracle/NEW_SID/sapbackup/..regtquoh..cmd^'

'host '/usr/sap/NEW_SID/SYS/exe/run/brtools -f delete /oracle/NEW_SID/sapbackup/..regtquoh..cmd';^'

'sql 'alter session set optimizer_mode=RULE';^'

'run { allocate channel sbt_1 device type 'SBT_TAPE'^'

'parms 'ENV=(XINT_PROFILE=/oracle/NEW_SID/102_64/dbs/initNEW_SID_weekly.utl,PROLE_PORT=57323)';^'

'set newname for datafile '/oracle/OLD_SID/sapdata1/sr3_1/sr3.data1' to '/oracle/NEW_SID/sapdata1/sr3_1/sr3.data1';^'

'set newname for datafile '/oracle/OLD_SID/sapdata1/sr3_2/sr3.data2' to '/oracle/NEW_SID/sapdata1/sr3_2/sr3.data2';^'

'set newname for datafile '/oracle/OLD_SID/sapdata1/sr3_3/sr3.data3' to '/oracle/NEW_SID/sapdata1/sr3_3/sr3.data3';^'

'set newname for datafile '/oracle/OLD_SID/sapdata1/sr3_4/sr3.data4' to '/oracle/NEW_SID/sapdata1/sr3_4/sr3.data4';^'

'set newname for datafile '/oracle/OLD_SID/sapdata1/sr3_5/sr3.data5' to '/oracle/NEW_SID/sapdata1/sr3_5/sr3.data5';^'

'set newname for datafile '/oracle/OLD_SID/sapdata1/sr3_6/sr3.data6' to '/oracle/NEW_SID/sapdata1/sr3_6/sr3.data6';^'

'set newname for datafile '/oracle/OLD_SID/sapdata1/sr3_7/sr3.data7' to '/oracle/NEW_SID/sapdata1/sr3_7/sr3.data7';^'

'set newname for datafile '/oracle/OLD_SID/sapdata1/sr3_8/sr3.data8' to '/oracle/NEW_SID/sapdata1/sr3_8/sr3.data8';^'

'set newname for datafile '/oracle/OLD_SID/sapdata1/sr3700_1/sr3700.data1' to '/oracle/NEW_SID/sapdata1/sr3700_1/sr3700.data1';^'

'set newname for datafile '/oracle/OLD_SID/sapdata1/sr3700_2/sr3700.data2' to '/oracle/NEW_SID/sapdata1/sr3700_2/sr3700.data2';^'

'set newname for datafile '/oracle/OLD_SID/sapdata1/sr3700_3/sr3700.data3' to '/oracle/NEW_SID/sapdata1/sr3700_3/sr3700.data3';^'

'set newname for datafile '/oracle/OLD_SID/sapdata1/sr3700_4/sr3700.data4' to '/oracle/NEW_SID/sapdata1/sr3700_4/sr3700.data4';^'

'set newname for datafile '/oracle/OLD_SID/sapdata1/sr3700_5/sr3700.data5' to '/oracle/NEW_SID/sapdata1/sr3700_5/sr3700.data5';^'

'set newname for datafile '/oracle/OLD_SID/sapdata1/sr3700_6/sr3700.data6' to '/oracle/NEW_SID/sapdata1/sr3700_6/sr3700.data6';^'

'set newname for datafile '/oracle/OLD_SID/sapdata1/sr3700_7/sr3700.data7' to '/oracle/NEW_SID/sapdata1/sr3700_7/sr3700.data7';^'

'set newname for datafile '/oracle/OLD_SID/sapdata1/sr3usr_1/sr3usr.data1' to '/oracle/NEW_SID/sapdata1/sr3usr_1/sr3usr.data1';^'

'set newname for datafile '/oracle/OLD_SID/sapdata1/undo_1/undo.data1' to '/oracle/NEW_SID/sapdata1/undo_1/undo.data1';^'

'set newname for datafile '/oracle/OLD_SID/sapdata1/sysaux_1/sysaux.data1' to '/oracle/NEW_SID/sapdata1/sysaux_1/sysaux.data1';^'

'set newname for datafile '/oracle/OLD_SID/sapdata1/system_1/system.data1' to '/oracle/NEW_SID/sapdata1/system_1/system.data1';^'

'set newname for datafile '/oracle/OLD_SID/sapdata1/tivoliorts_1/tivoliorts.dbf' to '/oracle/NEW_SID/sapdata1/tivoliorts_1/tivoliorts.dbf';^'

'restore datafile 4,8,6,16,17,7,3,10,5,19,1,12,15,20,13,14,9,11,18,2 from tag begsxivt force;^'

'release channel sbt_1; }^'

'exit;^'

BR0280I BRRESTORE time stamp: 2011-09-13 15.50.18

BR0279E Return code from 'SHELL=/bin/sh /oracle/NEW_SID/102_64/bin/rman nocatalog': 1

BR0522E 0 of 20 files/save sets processed by RMAN

BR0536E RMAN call for database instance NEW_SID failed

BR0280I BRRESTORE time stamp: 2011-09-13 15.50.18

BR0556E Restore from full database backup using RMAN failed

BR0406I End of file restore: regtquoh.rsb 2011-09-13 15.50.18

BR0280I BRRESTORE time stamp: 2011-09-13 15.50.18

BR0404I BRRESTORE terminated with errors

[Former Member]

Hi,

From the restore logs:

RMAN-03002: failure of set command at 09/13/2011 15:50:18 ORA-01507: database not mounted

BR0522E 0 of 20 files/save sets processed by RMAN BR0536E RMAN call for database instance NEW_SID failed

The restore itself has failed. Hence the files do not exist. Do you use third party backup/restore tools via the backint interface?

Regards,

Srikishan

[Former Member]

Hai...

There are two options

1) Recheck the control files from other locations also and if exist then copy it to all locations

i.e. /oracle/<SID>/sapdat1/cntrl

/oracle/<SID>/mirrorlogA/cntrl

/oracle/<SID>/mirrorlogB/cntrl

and then try to restart the system.

2) Your have to recreate the control files by direct or to trace

ALTER DATABASE BACKUP CONTROLFILE TO TRACE, which generates a SQL script in the trace file to re-create the controlfile. If your database contains any read-only or temporary tablespaces, then that SQL script will also contain all the necessary SQL statements to add those files back into the database. Please refer to the ALTER DATABASE "BACKUP CONTROLFILE Clause" for information creating a script based on an existing database controlfile.

i.e. SQL> alter database backup controlfile to trace as '/tmp/ctrl.trc';

rename the trace file to required format, and try to restart.

Cheers

KHS