Problem in creating control file after restoring backup.

[Former Member]

Hi All,

i was doing system copy from PRD to TST server.

restored the brbackup of PRD on TST server,

copied the trace file of PRD to TST

changed the SID, changed owner, & moved the file from .trc to control.sql (/oracle/MDS/saptrace/usertrace)

when trying to create control file am getting error as;

SQL> @/oracle/MDS/saptrace/usertrace/CONTROL.SQL

ORACLE instance started.

Total System Global Area 5855248384 bytes

Fixed Size 2150824 bytes

Variable Size 2948509272 bytes

Database Buffers 2902458368 bytes

Redo Buffers 2129920 bytes

CREATE CONTROLFILE REUSE DATABASE "MDS" RESETLOGS ARCHIVELOG

*

ERROR at line 1:

ORA-01503: CREATE CONTROLFILE failed

ORA-01161: database name MDP in file header does not match given name of MDS

ORA-01110: data file 1: '/oracle/MDS/sapdata1/system_1/system.data1'

ORA-01507: database not mounted

ALTER SYSTEM ARCHIVE LOG ALL

*

ERROR at line 1:

ORA-01507: database not mounted

ALTER DATABASE OPEN

*

ERROR at line 1:

ORA-01507: database not mounted

ALTER TABLESPACE PSAPTEMP ADD TEMPFILE '/oracle/MDS/sapdata2/temp_1/temp.data1'

*

ERROR at line 1:

ORA-01109: database not open

ORA-01081: cannot start already-running ORACLE - shut it down first

CREATE CONTROLFILE REUSE DATABASE "MDS" RESETLOGS ARCHIVELOG

*

ERROR at line 1:

ORA-01503: CREATE CONTROLFILE failed

ORA-01161: database name MDP in file header does not match given name of MDS

ORA-01110: data file 1: '/oracle/MDS/sapdata1/system_1/system.data1'

Kindly suggest.

[Former Member · Accepted solution]

Hi Ashun,

Reset the password of SAPR3 as menioned below.

alter user sapr3 identified by sap;

OR

You can reset it through BRTOOLS also.

Regards,

Ranjith

[former_member204746 · Accepted solution]

you should use brtools:

brconnect -c -u / -p init%ORACLE_SID%.sap -f chpass -o SAPR3 -password Whatever_you_want_here

[Former Member]

Dear All,

Thanks for the information.

Problem was with OPS$ users, i has been resolved now.

Regards!!

[Former Member]

Wait.........

I think you haven't recovered/opened your database yet, they why you are trying to start sap?

If you resotred offline backup, then just use below command from sql

sql>alter database open resetlogs;

then try to start sap

If you restored online backup then you may have to restore all offline redos to target server and need to recover dtabase

Go through service.sap.com/systemcopy

Regards,

Nick Loy

[Former Member]

Hi All,

lsnrctl started but R3trans -x giving error code 12.

when i checked trans.log giving error code as " ORA-01017: invalid username/password; logon denied "

Rgds!!

[peter_dzurov]

You need to recreate OPS$ users as your DB actually contains source OPS$ users, just drop cascade old one OPS$ users an create new one according to Note 361641 - Creating OPS$ users on UNIX

Have you used online or offline backup of PROD?

[Former Member]

Dear Ashun,

Please follow the system copy guide and follow all the steps not one by one, this message will continue until u finish your system copy.

I recommend you to read system copy guide and you will not face these common errors.

Regards

Shailesh

[Former Member]

No need to check env variables at this time..

Just execute below command from ORASID user

lsnrctl start

will starts listener...then start sap

Regards,

Nick Loy

[Former Member]

For your understanding;

STARTUP NOMOUNT CREATE CONTROLFILE REUSE SET DATABASE "MDS" RESETLOGS ARCHIVELOG

Regards,

Nick Loy

[Former Member]

Hi Nick,

I have made changes & database started has been mounted :

ORACLE instance started.

Total System Global Area 5855248384 bytes

Fixed Size 2150824 bytes

Variable Size 2948509272 bytes

Database Buffers 2902458368 bytes

Redo Buffers 2129920 bytes

Database mounted.

Now when am trying to startsap giving message as :

MDECCSAN:mdsadm 21> startsap

Checking MDS Database

-

---

ABAP Database is not available via R3trans

Starting SAP-Collector Daemon

-

---

14:41:58 12.01.2010 LOG: Effective User Id is root

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

• This is Saposcol Version COLL 20.95 700 - V2.7.1 2008-08-11 HP-UX IA64

• Usage: saposcol -l: Start OS Collector

• saposcol -k: Stop OS Collector

• saposcol -d: OS Collector Dialog Mode

• saposcol -s: OS Collector Status

• Starting collector (create new process)

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

saposcol on host MDECCSAN started

Running /usr/sap/MDS/SYS/exe/run/startdb

Trying to start MDS database ...

Log file: /home/mdsadm/startdb.log

No SQL*Net V2 connect to MDS available.

Check that the listener is running: "lsnrctl status".

Start the listener as user oramds: "lsnrctl start".

/usr/sap/MDS/SYS/exe/run/startdb: Terminating with error code 14

DB startup failed

[Former Member]

Just read what you are posting:

Check that the listener is running: "lsnrctl status".
Start the listener as user oramds: "lsnrctl start".

[Former Member]

Please check the below environment variable for user ORASID

dbms_type=ORA
dbs_ora_schema=SAPSR3
dbs_ora_tnsname=MDS
ORACLE_SID=MDS

dbs_ora_tnsname=MDS is required...

you dont need to start database in mount state before startsap...

Just start listner using ORASID and the do startsap from SIDADM...

Regards.

Rajesh Narkhede

[Former Member]

Hi,

Checked the environment variable :

dbms_type=ORA

dbs_ora_tnsname=MDS

dbs_ora_schema=SAPSR3

ORACLE_SID=MDS

DB_SID=MDS

ORACLE_BASE=/oracle

ORACLE_HOME=/oracle/MDS/102_64

NLS_LANG=AMERICAN_AMERICA.WE8DEC

SAPDATA_HOME=/oracle/MDS

wheni checked lsnrctl status :

MDECCSAN:oramds 2> lsnrctl status

LSNRCTL for HPUX: Version 10.2.0.2.0 - Production on 12-JAN-2010 15:21:58

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=MDP.WORLD))

TNS-12541: TNS:no listener

TNS-12560: TNS:protocol adapter error

TNS-00511: No listener

HPUX Error: 2: No such file or directory

Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=MDP))

TNS-12541: TNS:no listener

TNS-12560: TNS:protocol adapter error

TNS-00511: No listener

HPUX Error: 2: No such file or directory

Connecting to (ADDRESS=(COMMUNITY=SAP.WORLD)(PROTOCOL=TCP)(HOST=MDBWQAS)(PORT=1527))

TNS-01189: The listener could not authenticate the user

[Former Member]

Present :

STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "MDS" RESETLOGS ARCHIVELOG

Should be as:

STARTUP NOMOUNT CREATE CONTROLFILE REUSE *SET* DATABASE "MDS" RESETLOGS ARCHIVELOG

correct this one and re-run.....

Regards,

Nick Loy

[Former Member]

CREATE CONTROLFILE REUSE DATABASE "MDS" RESETLOGS ARCHIVELOG

Replace REUSE by SET.

hope this helps

[Former Member]

Dear Ashun,

Your control file should reflect the new structure of your systems interms of filesystem, You should be thinking about SID change also, If you change your SID then this should also refelct in your Control File too.

Or can you just paste your new control file here with previous control file.

Regards

Shailesh

[Former Member]

Hi,

The control file in /oracle/MDS/sapdata1/cntrl/ , /oracle/MDS/origlogA/cntrl & oracle/MDS/origlogB/cntrl is still with the SID of PRD server (cntrlMDP.dbf) , can i rename this file.

control.sql :

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "MDS" RESETLOGS ARCHIVELOG

MAXLOGFILES 255

MAXLOGMEMBERS 3

MAXDATAFILES 254

MAXINSTANCES 50

MAXLOGHISTORY 11680

LOGFILE

GROUP 1 (

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

'/oracle/MDS/mirrlogA/log_g11m2.dbf'

) SIZE 50M,

GROUP 2 (

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

'/oracle/MDS/mirrlogB/log_g12m2.dbf'

) SIZE 50M,

GROUP 3 (

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

'/oracle/MDS/mirrlogA/log_g13m2.dbf'

) SIZE 50M,

GROUP 4 (

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

'/oracle/MDS/mirrlogB/log_g14m2.dbf'

) SIZE 50M

-- STANDBY LOGFILE

DATAFILE

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

'/oracle/MDS/sapdata3/undo_1/undo.data1',

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

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

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

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

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

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

'/oracle/MDS/sapdata2/sr3_6/sr3.data6',

'/oracle/MDS/sapdata2/sr3_7/sr3.data7',

'/oracle/MDS/sapdata2/sr3_8/sr3.data8',

'/oracle/MDS/sapdata2/sr3_9/sr3.data9',

'/oracle/MDS/sapdata2/sr3_10/sr3.data10',

'/oracle/MDS/sapdata3/sr3_11/sr3.data11',

'/oracle/MDS/sapdata3/sr3_12/sr3.data12',

'/oracle/MDS/sapdata3/sr3_13/sr3.data13',

'/oracle/MDS/sapdata3/sr3_14/sr3.data14',

'/oracle/MDS/sapdata3/sr3_15/sr3.data15',

'/oracle/MDS/sapdata4/sr3_16/sr3.data16',

'/oracle/MDS/sapdata4/sr3_17/sr3.data17',

'/oracle/MDS/sapdata4/sr3_18/sr3.data18',

'/oracle/MDS/sapdata4/sr3_19/sr3.data19',

'/oracle/MDS/sapdata4/sr3_20/sr3.data20',

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

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

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

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

'/oracle/MDS/sapdata2/sr3700_5/sr3700.data5',

'/oracle/MDS/sapdata2/sr3700_6/sr3700.data6',

'/oracle/MDS/sapdata2/sr3700_7/sr3700.data7',

'/oracle/MDS/sapdata2/sr3700_8/sr3700.data8',

'/oracle/MDS/sapdata3/sr3700_9/sr3700.data9',

'/oracle/MDS/sapdata3/sr3700_10/sr3700.data10',

'/oracle/MDS/sapdata3/sr3700_11/sr3700.data11',

'/oracle/MDS/sapdata3/sr3700_12/sr3700.data12',

'/oracle/MDS/sapdata4/sr3700_13/sr3700.data13',

'/oracle/MDS/sapdata4/sr3700_14/sr3700.data14',

'/oracle/MDS/sapdata4/sr3700_15/sr3700.data15',

'/oracle/MDS/sapdata4/sr3700_16/sr3700.data16',

'/oracle/MDS/sapdata1/sr3usr_1/sr3usr.data1'

CHARACTER SET WE8DEC

;

-- Commands to re-create incarnation table

-- Below log names MUST be changed to existing filenames on

-- disk. Any one log file from each branch can be used to

-- re-create incarnation records.

-- ALTER DATABASE REGISTER LOGFILE '/oracle/MDS/oraarch/MDSarch1_1_670046591.dbf';

-- Recovery is required if any of the datafiles are restored backups,

-- or if the last shutdown was not normal or immediate.

RECOVER DATABASE

-- All logs need archiving and a log switch is needed.

ALTER SYSTEM ARCHIVE LOG ALL;

-- Database can now be opened normally.

ALTER DATABASE OPEN;

-- Commands to add tempfiles to temporary tablespaces.

-- Online tempfiles have complete space information.

-- Other tempfiles may require adjustment.

ALTER TABLESPACE PSAPTEMP ADD TEMPFILE '/oracle/MDS/sapdata2/temp_1/temp.data1'

SIZE 2000M REUSE AUTOEXTEND ON NEXT 20971520 MAXSIZE 10000M;

-- End of tempfile additions.

--

-- Set #2. RESETLOGS case

--

-- The following commands will create a new control file and use it

-- to open the database.

-- Data used by Recovery Manager will be lost.

-- The contents of online logs will be lost and all backups will

-- be invalidated. Use this only if online logs are damaged.

-- After mounting the created controlfile, the following SQL

-- statement will place the database in the appropriate

-- protection mode:

-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "MDS" RESETLOGS ARCHIVELOG

MAXLOGFILES 255

MAXLOGMEMBERS 3

MAXDATAFILES 254

MAXINSTANCES 50

MAXLOGHISTORY 11680

LOGFILE

GROUP 1 (

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

'/oracle/MDS/mirrlogA/log_g11m2.dbf'

) SIZE 50M,

GROUP 2 (

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

'/oracle/MDS/mirrlogB/log_g12m2.dbf'

) SIZE 50M,

GROUP 3 (

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

'/oracle/MDS/mirrlogA/log_g13m2.dbf'

) SIZE 50M,

GROUP 4 (

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

'/oracle/MDS/mirrlogB/log_g14m2.dbf'

) SIZE 50M

-- STANDBY LOGFILE

DATAFILE

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

'/oracle/MDS/sapdata3/undo_1/undo.data1',

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

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

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

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

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

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

'/oracle/MDS/sapdata2/sr3_6/sr3.data6',

'/oracle/MDS/sapdata2/sr3_7/sr3.data7',

'/oracle/MDS/sapdata2/sr3_8/sr3.data8',

'/oracle/MDS/sapdata2/sr3_9/sr3.data9',

'/oracle/MDS/sapdata2/sr3_10/sr3.data10',

'/oracle/MDS/sapdata3/sr3_11/sr3.data11',

'/oracle/MDS/sapdata3/sr3_12/sr3.data12',

'/oracle/MDS/sapdata3/sr3_13/sr3.data13',

'/oracle/MDS/sapdata3/sr3_14/sr3.data14',

'/oracle/MDS/sapdata3/sr3_15/sr3.data15',

'/oracle/MDS/sapdata4/sr3_16/sr3.data16',

'/oracle/MDS/sapdata4/sr3_17/sr3.data17',

'/oracle/MDS/sapdata4/sr3_18/sr3.data18',

'/oracle/MDS/sapdata4/sr3_19/sr3.data19',

'/oracle/MDS/sapdata4/sr3_20/sr3.data20',

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

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

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

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

'/oracle/MDS/sapdata2/sr3700_5/sr3700.data5',

'/oracle/MDS/sapdata2/sr3700_6/sr3700.data6',

'/oracle/MDS/sapdata2/sr3700_7/sr3700.data7',

'/oracle/MDS/sapdata2/sr3700_8/sr3700.data8',

'/oracle/MDS/sapdata3/sr3700_9/sr3700.data9',

'/oracle/MDS/sapdata3/sr3700_10/sr3700.data10',

'/oracle/MDS/sapdata3/sr3700_11/sr3700.data11',

'/oracle/MDS/sapdata3/sr3700_12/sr3700.data12',

'/oracle/MDS/sapdata4/sr3700_13/sr3700.data13',

'/oracle/MDS/sapdata4/sr3700_14/sr3700.data14',

'/oracle/MDS/sapdata4/sr3700_15/sr3700.data15',

'/oracle/MDS/sapdata4/sr3700_16/sr3700.data16',

'/oracle/MDS/sapdata1/sr3usr_1/sr3usr.data1'

CHARACTER SET WE8DEC

;