System copy backup restore method

[Former Member]

Hi ,

I'm performing system copy using backup/ restore method (swpm),while target system installation

I have successfully restored backup but after restoring while continuing with SWPM it stops ORA-01503: CREATE CONTROLFILE failed​

but the control file is already created attached the screenshots related to the error  and control.sql &alert_SID .log file below .

And implemented SAP notes :205501 & 1983045​ .

Kindly give your suggestion to fix or alternative method  to build the new system using offline backup .

alert_qec.txt.zip

CONTROL.txt.zip

[Reagan · Accepted solution]

select name, open_mode, log_mode from v$database;

Could you supply the output of this SQL query ?

Regards

RB

[Former Member · Accepted solution]

Hi Regan,

As requested below .

SQL> select name, open_mode, log_mode from v$database;

NAME      OPEN_MODE            LOG_MODE

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

MEC       READ WRITE           ARCHIVELOG

[Reagan · Accepted solution]

The database is already in OPEN state then why are you trying to create the control files again ?

Shutdown the database and start it and see if it starts up alright.

shutdown immediate;

startup;

select name, open_mode, log_mode from v$database;

Regards

RB

[Former Member · Accepted solution]

Hi Reagan ,

I have shutdown the database and started  manually no issues ,

Problem comes only when i resume the SWPM after restore, attached the error screenshot below .

[Reagan · Accepted solution]

Can you supply the script used by SWPM to create the control file ?

If possible modify the script with the statement :

startup mount;

Regards

RB

[Former Member · Accepted solution]

As requested i have attached below, please do the need full .

Previously I have already tried changing the script with statement startup mount faced similar issue .

CONTROL_Script.TXT.zip

[Reagan · Accepted solution]

As you have already opened the database manually then modify the script with the option startup

Make sure you stop the database first.

Which means you will delete everything in the script and will only put

startup;

Regards

RB

[Former Member · Accepted solution]

I have tried changing the script with startup once & second time with removing CREATE CONTROLFILE SET DATABASE 'MEC' RESETLOGS ARCHIVELOG.

Two times i faced the below error

Error message: ORA-01503: CREATE CONTROLFILE failed ORA-01100: database already mounted ORA-01109: database not open Database dismounted. ORACLE instance shut down. ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started. Total System Global Area 2254876672 bytes Fixed Size 2257152 bytes Variable Size 1140854528 bytes Database Buffers 1107296256 bytes Redo Buffers 4468736 bytes Database mounted. Database altered. Database altered. Database closed. Database dismounted. ORACLE instance shut down. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options . SOLUTION: For more information, see ora_sql_results.log and the Oracle documentation.)

[Reagan · Accepted solution]

In that case you are not modifying the right script.

Can you list all the scripts in the location please ?

Regards

RB

[Former Member · Accepted solution]

Only one script Control.sql  in that location no other scripts exits .

[Reagan · Accepted solution]

and what does the log file ora_sql_results.log contain ?

I believe you have modified the script with startup.

In that case try to find out why the tool is trying to create the control files again.

If possible restart sapinst and see if that helps.

[Former Member · Accepted solution]

I have changed the script in SWPM <INST_DIR> now the error message is different  as below .

[Reagan · Accepted solution]

What is the status of the database ?

Run the query provided above and supply the output.

You need to provide the exact details of the path of the script and the modifications you have done to help you better.

[ACE-SAP · Accepted solution]

Hello

You should create that 'dummy' control.sql file in the work folder of SWPM, something like :

"C:\Program Files\sapinst_instdir\BS2005\ERP60SR3\LM\COPY\ORA\SYSTEM\CENTRAL\AS-ABAP"

Regards

[Former Member · Accepted solution]

Based on the previous error attached i have modified the control.sql by adding ALTER DATABASE OPEN RESETLOGS;

now i have overcome the error facing , new error hits now details attached below .

now the status is :

SQL> select name, open_mode, log_mode from v$database;

NAME      OPEN_MODE            LOG_MODE

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

MEC       READ WRITE           ARCHIVELOG

[Reagan · Accepted solution]

The system should not create control file creation when the script is just with startup statement. Maybe it should work with a dummy but I have used startup before.

[Reagan · Accepted solution]

Now recreate the PSAPTEMP tablespace.

[Former Member · Accepted solution]

Reagan,

I used below in the Control.sql it went through previously attached error messaged occurred now .

STARTUP NOMOUNT

CREATE CONTROLFILE SET DATABASE "QEC" RESETLOGS  ARCHIVELOG

----

----

ALTER DATABASE OPEN RESETLOGS;

_________________________

FYI,

the offline backup(which was taken 3 months back) has tablespace PSAPSR3700 , but the control file was taken from source system recently ( which doesn't contain this table PSAPSR3700) .

Do i need to create the table space manually to fix the error facing now

[Former Member · Accepted solution]

Regan ,

I can see the table space PSAPTEMP & PSAPSR3700  attached below, Based on the below please let us know how to fix this current error .

[Reagan · Accepted solution]

PSAPTEMP tablespace is there but there are no tempfiles.

You need to recreate the tempfiles.

Eg: ALTER TABLESPACE PSAPTEMP ADD TEMPFILE 'G:\ORACLE\MEC\SAPDATA1\temp_1\temp.data1' SIZE 30000M REUSE AUTOEXTEND OFF;

STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "QEC" RESETLOGS  ARCHIVELOG
----
----
ALTER DATABASE OPEN RESETLOGS;

To be honest I don't what you are doing now.

Is you database QEC or MEC ?

Regarding the backup, use a valid backup and create the control file script based on that.

Regards

RB

[Former Member · Accepted solution]

Reagan ,

Issue resolved thanks a lot for valuable suggestions.

My database is "MEC" I copied above lines from source  CONTROL.SQL before changing the SID in notepad .

[Former Member]

Hello Krishna,

Control files might me from old backup , rename them as .old (from all locations mentioned in pfile)  and retry the step in SWPM.

Regards,

[Former Member]

Hi Deep,

As you mentioned i have removed the Control files and recreated using Control.sql file and 

alter database open resetlogs;  still the same issue .

[Former Member]

Dont create control file manually , it has to be done using SWPM only , it will run that script   (control.sql) automatically.

If you run this script manually you will get such error.

Regards,

[Former Member]

Hi Deep ,

As you said i have removed the control file and shutdown the DB , rerun the SWPM it gives below error

DIAGNOSIS: Error message: ORA-00205: error in identifying control file, check alert log for more info Control file created. ORA-01109: database not open Database dismounted. ORACLE instance shut down. ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started. Total System Global Area 2254876672 bytes Fixed Size 2257152 bytes Variable Size 1140854528 bytes Database Buffers 1107296256 bytes Redo Buffers 4468736 bytes Database mounted. Database altered. ALTER DATABASE OPEN * ERROR at line 1: ORA-01589: must use RESETLOGS or NORESETLOGS option for database open ORA-01109: database not open Database dismounted. ORACLE instance shut down. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options . SOLUTION: For more information, see ora_sql_results.log and the Oracle documentation.). You can now:

[Former Member]

Hi,

""ALTER DATABASE OPEN * ERROR at line 1: ORA-01589: must use RESETLOGS or NORESETLOGS option for database open ORA-01109: database not open Database dismounted ""

You have to modify control file (control.sql) which is being read by SWPM and maintain alter database open resetlogs there.

As retry will try to create control file again , so kindly rename old control files to .old once again.

Option 2: Start your DB manually using control.sql file and maintain alter database open (only one command in control.sql which is being read by SWPM)

Regards,

[Former Member]

Hi Deep ,

I have tried option 2 running manually  it gives below error

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

Attached the Alert_SID.log below

alert_Mec2.txt.zip

[Former Member]

check if this exist

ORA-00202: control file: 'F:\ORACLE\MEC\SAPDATA1\CNTRL\CNTRLMEC.DBF' ??

Path should be maintained in pfile correctly.

[Former Member]

Hi Deep ,

Yes , the patch exits I gave only to path the below  instead of origlogA & origlogB .

F:\ORACLE\MEC\SAPDATA1\CNTRL\CNTRLMEC.DBF'