cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Error during SAP system rename

Former Member

on ‎06-01-2016 10:21 AM

0 Kudos

Hello,

I am facing following error during a system rename activity. I am in phase of rename of database instance. Our database is 11.2.0.4 on Linux x86-64.

<html> <head> </head> <body> <p> An error occurred while processing option <i>System Rename > Distributed System > System Rename for Database Instance( Last error reported by the step: The JVM reports an exception during execution of class ( com.sap.sdt.ins.component.ora_clone_obr.AdaptControlSqlFile ) and function executeStepClass. DIAGNOSIS: The reported error message from JVM is: java.io.FileNotFoundException: File not found: /tmp/sapinst_instdir/NW70/SBC/DISTRIBUTED/DB/CONTROL.SQL

        at com.sap.sdt.ins.filercopy.db.ora.RenameDatabase.adaptControlSqlFile(RenameDatabase.java:2320)

        at com.sap.sdt.ins.component.ora_clone_obr.AdaptControlSqlFile.execute(AdaptControlSqlFile.java:53)

.)</i>. You can now: </p> <ul> <li> Choose <i>Retry</i><br>to repeat the current step. </li> <li> Choose <i>Log Files</i><br>to get more information about the error. </li> <li> Stop the option and continue later. </li> </ul> <p> Log files are written to /tmp/sapinst_instdir/NW70/SBC/DISTRIBUTED/DB. </p> </body></html>

Following is the list of *.sql file

[root@saplgrddb DB]# ls -ltr *.sql

-rwxrwxrwx 1 root   sapinst   20 May 26 08:01 ora_stmt_scr_tmp.sql

-rwxrwxrwx 1 grdadm dba     1319 May 26 08:01 rename.sql

-rwxrwxrwx 1 grdadm dba      476 May 26 08:01 recover.sql

-rwxrwxrwx 1 root   sapinst  125 May 26 08:01 run_rename.sql

-rwxrwxrwx 1 root   sapinst   38 May 26 08:01 run_recover.sql

-rwxrwxrwx 1 root   sapinst  150 May 26 08:01 run_cntrl.sql

-rwxrwxrwx 1 root   sapinst   56 May 26 08:01 run_openResetLogs.sql

-rwxrwxrwx 1 root   sapinst  854 May 26 08:01 checkScript.sql

-rwxrwxrwx 1 root   sapinst   23 May 31 02:38 ora_scr_tmp.sql

[root@saplgrddb DB]#

Any idea how this error can be resolved. From where I can get control.sql?

Thanks,

Dnyandev

Show replies
Show replies
Answer

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
‎06-02-2016 7:23 AM
0 Kudos

Hello Dynandev,

A couple of things some of which have already been covered.

1. Running stuff like SWPM out of /tmp is never a good idea.

2. SWPM can't find the file CONTROL.SQL. As already pointed out this is UPPERCASE.

Try the following to see if you can find the file.

$ cd /tmp

$ find . -name CONTROL.SQL

3. You have tried to start the DB and clearly you have a problem identifying control files which one could reasonably assume would have been created by the CONTROL.SQL.

Try and find any file that contains the statement "create controlfile"

$ cd /tmp

$ find . -type f -exec grep -il "CREATE CONTROLFILE" {} \;

Please come back with the info from the above. In the worst case you'll have to manually create the controlfile.

KR,

Amerjit

Show replies
Show replies
Former Member
‎06-02-2016 8:46 AM
0 Kudos

Hello,

I have created CONTROL.SQL from source MCS and placed in the installation directory. Now I am getting followin error.

<html> <head> </head> <body> <p> An error occurred while processing option <i>System Rename > Distributed System > System Rename for Database Instance( Last error reported by the step: SQL statement or script failed. DIAGNOSIS: Error message: ORA-01503: CREATE CONTROLFILE failed

ORA-01161: database name MCS in file header does not match given name of GRD

ORA-01110: data file 2: '/oracle/GRD/sapdata1/sysaux_1/sysaux.data1'

ALTER DATABASE OPEN RESETLOGS

*

ERROR at line 1:

ORA-01507: database not mounted

ALTER TABLESPACE PSAPTEMP ADD TEMPFILE '/oracle/GRD/sapdata1/temp_1/temp.data1'

*

ERROR at line 1:

ORA-01109: database not open

ORA-01507: database not mounted

ORACLE instance shut down.

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 5829246976 bytes

Fixed Size                  2263736 bytes

Variable Size            2919236936 bytes

Database Buffers         2902458368 bytes

Redo Buffers                5287936 bytes

Database mounted.

Database altered.

ALTER DATABASE OPEN

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery

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

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.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.)</i>. You can now: </p> <ul> <li> Choose <i>Retry</i><br>to repeat the current step. </li> <li> Choose <i>Log Files</i><br>to get more information about the error. </li> <li> Stop the option and continue later. </li> </ul> <p> Log files are written to /tmp/sapinst_instdir/NW70/SBC/DISTRIBUTED/DB. </p> </body></html>

CONTROL.SQL Contents

[root@saplgrddb DB]# more CONTROL.SQL

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE SET DATABASE "GRD" RESETLOGS FORCE LOGGING ARCHIVELOG

    MAXLOGFILES 255

    MAXLOGMEMBERS 3

    MAXDATAFILES 1000

    MAXINSTANCES 50

    MAXLOGHISTORY 1168

LOGFILE

  GROUP 1 (

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

    '/oracle/GRD/mirrlogA/log_g11m2.dbf'

  ) SIZE 200M BLOCKSIZE 512,

  GROUP 2 (

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

    '/oracle/GRD/mirrlogB/log_g12m2.dbf'

  ) SIZE 200M BLOCKSIZE 512,

  GROUP 3 (

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

    '/oracle/GRD/mirrlogA/log_g13m2.dbf'

  ) SIZE 200M BLOCKSIZE 512,

  GROUP 4 (

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

    '/oracle/GRD/mirrlogB/log_g14m2.dbf'

  ) SIZE 200M BLOCKSIZE 512

DATAFILE

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

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

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

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

  '/oracle/GRD/sapdata1/sr3701_1/sr3701.data1',

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

  '/oracle/GRD/sapdata1/sr3db_1/sr3db.data1'

CHARACTER SET UTF8

;

ALTER DATABASE OPEN RESETLOGS;

ALTER TABLESPACE PSAPTEMP ADD TEMPFILE '/oracle/GRD/sapdata1/temp_1/temp.data1'

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

Former Member
‎06-02-2016 10:45 AM
0 Kudos

Hello,

1. Adapt your CONTROL.SQL as follows:

CREATE CONTROLFILE REUSE SET DATABASE "GRD" RESETLOGS  ARCHIVELOG

Remove everything after the below:

CHARACTER SET UTF8 ;

It looks like you will have to perform a recovery after the controlfile has been created

2. Please check your ora files in dbs directory to make sure you have no references to MDS.

Once you have created your controlfiles and as recovery is required then perform a recovery until cancel providing the path of the redo logs (online and/or offline) that need to be replayed.

You can then try and do a open with resetlogs and create your PSAPTEMP.

KR,

Amerjit

Former Member
‎06-02-2016 10:59 AM
0 Kudos

Hi Amerjit,

Still same error after I followed your instructions.

Error message: ORA-01503: CREATE CONTROLFILE failed

ORA-01161: database name MCS in file header does not match given name of GRD

ORA-01110: data file 2: '/oracle/GRD/sapdata1/sysaux_1/sysaux.data1'

THanks,

Dnyandev

Reagan
Advisor
Advisor
‎06-02-2016 11:18 AM
0 Kudos

It should be CREATE CONTROLFILE SET DATABASE "GRD" RESETLOGS  ARCHIVELOG

By the way can you provide the contents of the pfile/spfile you are using to start the database?

Former Member
‎06-02-2016 11:25 AM
0 Kudos

Well spotted. Erronoeous Copy+paste.

Former Member
‎06-02-2016 11:34 AM
0 Kudos

Hi Reagan,

I removed REUSE and used only SET, but still same error.

ERROR      2016-06-02 05:31:23.680 (root/sapinst) [CInstallerCallBackImpl.cpp:259] id=ind-rel.ind-os.ora.sqlStatementFailed errno=CJS-00084

SQL statement or script failed. DIAGNOSIS: Error message: ORA-01503: CREATE CONTROLFILE failed

ORA-01161: database name MCS in file header does not match given name of GRD

ORA-01110: data file 2: '/oracle/GRD/sapdata1/sysaux_1/sysaux.data1'

ORA-01507: database not mounted

FOllowing is the initSID.ora file

saplgrddb:oragrd 53> more initGRD.ora

MCS.__oracle_base='/oracle'#ORACLE_BASE set from environment

*._awr_mmon_deep_purge_all_expired=TRUE

*._B_TREE_BITMAP_PLANS=FALSE

*._fix_control='5099019:ON','5705630:ON','6055658:OFF','6120483:OFF','6399597:ON','6430500:ON','6440977:ON','6626018:ON','6972291:ON','7168184:OFF','8937971:ON','9196440:ON','9495669:ON',

'13077335:ON','13627489:ON','14255600:ON','14595273:ON','18405517:2'#SAP_112043_201408 RECOMMENDED SETTINGS

*._IN_MEMORY_UNDO=FALSE

*._INDEX_JOIN_ENABLED=FALSE

*._ktb_debug_flags=8

*._mutex_wait_scheme=1

*._mutex_wait_time=10

*._OPTIM_PEEK_USER_BINDS=FALSE

*._optimizer_adaptive_cursor_sharing=FALSE

*._optimizer_extended_cursor_sharing_rel='NONE'

*._OPTIMIZER_MJC_ENABLED=FALSE

*._optimizer_use_cbqt_star_transformation=FALSE

*._optimizer_use_feedback=FALSE

*._securefiles_concurrency_estimate=50

*._SORT_ELIMINATION_COST_RATIO=10

*._TABLE_LOOKUP_PREFETCH_SIZE=0

*.audit_file_dest='/oracle/GRD/saptrace/audit'

*.compatible='11.2.0'

*.control_file_record_keep_time=30

*.control_files='/oracle/GRD/origlogA/cntrl/cntrlGRD.dbf','/oracle/GRD/origlogB/cntrl/cntrlGRD.dbf','/oracle/GRD/sapdata1/cntrl/cntrlGRD.dbf'

*.db_block_size=8192

*.db_cache_size=2899102924

*.db_files=1000

*.db_name='GRD'

*.db_recovery_file_dest='/oracle/GRD/oraflash'

*.db_recovery_file_dest_size=30000M

*.diagnostic_dest='/oracle/GRD/saptrace'

*.event='10027','10028','10142','10183','10191','10995 level 2','31991','38068 level 100','38085','38087','44951 level 1024','64000 level 25'#SAP_112047_201508 RECOMMENDED SETTINGS

*.FILESYSTEMIO_OPTIONS='setall'

*.log_archive_dest_1='LOCATION=/oracle/GRD/oraarch/GRDarch'

*.log_archive_format='%t_%s_%r.dbf'

*.log_checkpoints_to_alert=true

*.max_dump_file_size='20000'

*.open_cursors=2000

*.optimizer_index_cost_adj=20

*.parallel_execution_message_size=16384

*.parallel_threads_per_cpu=1

*.pga_aggregate_target=3865470566

*.processes=150

*.query_rewrite_enabled='false'

*.recyclebin='off'

*.remote_login_passwordfile='exclusive'

*.remote_os_authent=true

*.replication_dependency_tracking=false

*.sessions=300

*.shared_pool_size=2899102924

*.star_transformation_enabled='true'

*.undo_tablespace='PSAPUNDO'

saplgrddb:oragrd 54>

Reagan
Advisor
Advisor
‎06-01-2016 11:44 AM
0 Kudos

In fact the system is looking for /tmp/sapinst_instdir/NW70/SBC/DISTRIBUTED/DB/CONTROL.SQL and you are looking for *.sql files in lower case.

First check if the database has been renamed to the target SID. If yes try to start the database and see if the database starts with the new SID.

If yes then create a file called CONTROL.SQL in the /tmp/sapinst_instdir/NW70/SBC/DISTRIBUTED/DB location and add STARTUP; to it

Show replies
Show replies
Former Member
‎06-01-2016 12:43 PM
0 Kudos

Hi Reagan,

I started the database but it gave following error? SHould I still create CONTROL.SQL with STARTUP command?

SQL> startup

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 5829246976 bytes

Fixed Size                  2263736 bytes

Variable Size            2919236936 bytes

Database Buffers         2902458368 bytes

Redo Buffers                5287936 bytes

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

THanks,

Dnyandev

Reagan
Advisor
Advisor
‎06-01-2016 3:10 PM
0 Kudos

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

Check the alert log for more details.

Most likely the system is still pointing to the old control files.

You might need to recreate the control files.

former_member185239
Active Contributor
‎06-02-2016 3:36 AM
0 Kudos

Hi Dnyandev,

- Create the CONTROL.SQL file

- open the CONTROL.SQL file and type  "exit;" (without double quote)

- Retry the steps on SWPM

With Regrads

Ashutosh Chaturvedi

Former Member
‎06-01-2016 10:57 AM
0 Kudos

Hi Dnyandev.

The file is missing or without the correct permissions, please go to the Install_Dir and copy the file.

Please also take a look at this:

http://sapassets.edgesuite.net/sapcom/docs/2015/07/3009f6c0-5b7c-0010-82c7-eda71af511fa.pdf

Regards.

Osvaldo Dias Ferreira

Show replies
Show replies
Former Member
‎06-01-2016 11:29 AM
0 Kudos

Hi Osvaldo,

The file is not present. From where I should copy it? You are asking to copy from Install_Dir but I am already in installation directory  sapinst_instdir which was created by sapinst.

Thanks,

Dnyandev

Ask a Question