on 06-06-2016 4:17 PM
Hello, i´m doing a system rename of a solution manager 7.1 SP14, oracle DB 12.1.0.2, solaris 10 and we have the following error.
================================================================================
2016-06-03, 17:26:25 SAPINST ORACLE start logging for
CONNECT / AS SYSDBA
SHUTDOWN IMMEDIATE
startup mount pfile=?/dbs/initSMD.ora.mount
@checkScript.sql
@rename.sql SLM SMD
EXIT
exit;
Output of SQL executing program:
SQL*Plus: Release 12.1.0.2.0 Production on Fri Jun 3 17:26:26 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Connected.
ORA-01507: database not mounted
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 2550136832 bytes
Fixed Size 3006832 bytes
Variable Size 1342181008 bytes
Database Buffers 1174405120 bytes
Redo Buffers 30543872 bytes
ORA-00205: error in identifying control file, check alert log for more info
Connected.
FLASHBACK and BLOCK CHANGE TRACKING IS OFF
Connected.
old 14: sourceSid := upper('&&1');
new 14: sourceSid := upper('SLM');
old 15: targetSid := upper('&&2');
new 15: targetSid := upper('SMD');
BEGIN
*
ERROR at line 1:
ORA-01507: database not mounted
ORA-06512: at line 36
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SAPINST: End of output of SQL executing program /oracle/12102/bin/sqlplus.
SAPINST found errors.
SAPINST The current process environment may be found in sapinst_ora_environment.log.
2016-06-03, 17:26:46 SAPINST ORACLE stop logging
I checked the control files with the new user, orasmd, and i have this.
SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
control_files string /u02/sapdata5/ctrl/cntlr1SMD.d
bf, /u03/sapdata5/ctrl/cntlr2S
MD.dbf, /u04/sapdata1/ctrl/cnt
lr3SMD.dbf
the control files are not created by the system rename tool (sapinst)
desasmsap:orasmd 6% ls -ltr /u02/sapdata5/ctrl/cntlr1SMD.dbf /u03/sapdata5/ctrl/cntlr2SMD.dbf /u04/sapdata1/ctrl/cntlr3SMD.dbf
/u02/sapdata5/ctrl/cntlr1SMD.dbf: No such file or directory
/u03/sapdata5/ctrl/cntlr2SMD.dbf: No such file or directory
/u04/sapdata1/ctrl/cntlr3SMD.dbf: No such file or directory
How can i generate te control files with for the new DB SID?
regards.
Hello Aldo,
Can you do two things please.
1. Upload the checkscript.sql and rename.sql files so we can have a look.
2. Look for the SLM controlfiles in the same paths specified for the SMD controlfiles.
If no controlfiles (SLM or SMD) exist then we can walk you through creating the SQL that will create the controfiles.
KR,
Amerjit
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Amerjit, there are the scripts.
checkScript.sql
WHENEVER SQLERROR CONTINUE
connect / as sysdba
set feedback off
spool checkScript.log
set serverout on
set termout on
BEGIN
DECLARE
status varchar2(10);
flashback_on varchar2(18);
statement varchar2(1024);
BEGIN
SELECT flashback_on INTO flashback_on FROM V$DATABASE;
IF flashback_on <> 'NO' THEN
statement := 'ALTER DATABASE FLASHBACK OFF';
execute immediate statement;
dbms_output.put_line('Executed: ALTER DATABASE FLASHBACK OFF');
ELSE
dbms_output.put_line('FLASHBACK is OFF');
END IF;
statement := 'ALTER DATABASE DISABLE BLOCK CHANGE TRACKING';
execute immediate statement;
dbms_output.put_line('Executed: ALTER DATABASE DISABLE BLOCK CHANGE TRACKING');
EXCEPTION
WHEN others then
dbms_output.put_line('FLASHBACK and BLOCK CHANGE TRACKING IS OFF');
END;
END;
/
rename.sql
WHENEVER SQLERROR EXIT SQL.SQLCODE
connect / as sysdba
set feedback off
spool rename.log
set serverout on
set termout on
BEGIN
DECLARE
c number;
r number;
counter number;
member1 varchar2(257);
name1 varchar2(257);
statement varchar2(1024);
sourceSid varchar2(3);
targetSid varchar2(3);
cursor c_member is select member from v$logfile;
cursor c_name is select name from v$datafile;
BEGIN
sourceSid := upper('&&1');
targetSid := upper('&&2');
IF sourceSid <> targetSid THEN
FOR c1_rec IN c_member
LOOP
member1 := regexp_replace(c1_rec.member,'oracle[\/]' || sourceSid,'oracle/' || targetSid,1,1,'i');
IF member1 <> c1_rec.member THEN
statement := 'ALTER DATABASE RENAME FILE ''' || c1_rec.member || ''' TO ''' || member1 || '''';
execute immediate statement;
END IF;
END LOOP;
FOR c2_rec IN c_name
LOOP
name1 := regexp_replace(c2_rec.name,'oracle[\/]' || sourceSid,'oracle/' || targetSid,1,1,'i');
IF name1 <> c2_rec.name THEN
statement := 'ALTER DATABASE RENAME FILE ''' || c2_rec.name || ''' TO ''' || name1 || '''';
execute immediate statement;
END IF;
END LOOP;
END IF;
EXCEPTION
WHEN others then
RAISE;
END;
END;
/
the control files of SLM db (old SID) are here
/u02/sapdata5/ctrl/cntlr1SLM.dbf
/u03/sapdata5/ctrl/cntlr2SLM.dbf
/u04/sapdata1/ctrl/cntlr3SLM.dbf
but the control files of the new SID, SMD, are not created, the tool attempts to start the DB with the new sid (SMD) but fails
root@desasmsap # ls -ltr /u02/sapdata5/ctrl/cntlr1SMD.dbf /u03/sapdata5/ctrl/cntlr2SMD.dbf /u04/sapdata1/ctrl/cntlr3SMD.dbf
/u02/sapdata5/ctrl/cntlr1SMD.dbf: No such file or directory
/u03/sapdata5/ctrl/cntlr2SMD.dbf: No such file or directory
/u04/sapdata1/ctrl/cntlr3SMD.dbf: No such file or directory
Thanks and Regards.
Hello Aldo,
If I may make one remark, using SID=SMD for your SolMan has the potential to lead to a lot of confusion given that SMD means Solution Manager Diagnostics for everyone. Who knows it may even cause problems down the road with some of the SAP tooling.
That said, you can create your controlfile by executing a SQL file that looks similar to the following:
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "SMD" RESETLOGS ARCHIVELOG
MAXLOGFILES 255
MAXLOGMEMBERS 3
MAXDATAFILES 1000
MAXINSTANCES 50
MAXLOGHISTORY 1168
LOGFILE
GROUP 1 (
'/oracle/SMD/origlogA/log_g11m1.dbf',
'/oracle/SMD/mirrlogA/log_g11m2.dbf'
) SIZE 200M BLOCKSIZE 512,
GROUP 2 (
'/oracle/SMD/origlogB/log_g12m1.dbf',
'/oracle/SMD/mirrlogB/log_g12m2.dbf'
) SIZE 200M BLOCKSIZE 512,
GROUP 3 (
'/oracle/SMD/origlogA/log_g13m1.dbf',
'/oracle/SMD/mirrlogA/log_g13m2.dbf'
) SIZE 200M BLOCKSIZE 512,
GROUP 4 (
'/oracle/SMD/origlogB/log_g14m1.dbf',
'/oracle/SMD/mirrlogB/log_g14m2.dbf'
) SIZE 200M BLOCKSIZE 512
DATAFILE
'/oracle/SMD/sapdata1/system_1/system.data1',
'/oracle/SMD/sapdata1/sysaux_1/sysaux.data1',
'/oracle/SMD/sapdata1/undo_1/undo.data1',
'/oracle/SMD/sapdata1/sr3_1/sr3.data1',
'/oracle/SMD/sapdata1/sr3701_1/sr3701.data1',
'/oracle/SMD/sapdata1/sr3usr_1/sr3usr.data1',
'/oracle/SMD/sapdata1/sr3db_1/sr3db.data1'
CHARACTER SET UTF8 ;
Of course replace the datafile paths with your own datafiles. After that you may have to recover the DB using backup controlfile and finally "alter database open resetlogs ;"
Please don't forget to recreate your PSAPTEMP after you have successfully opened and started the DB.
Good luck.
KR,
Amerjit
Amerjit, thank you very much, i can create the control file and finish the rename of the system. The error is strange because after that i had another error and i had to put the old name of the database in the control file. I think the error was in the sapinst tool because no control files was created during the process.
Tranks for the answer!!
Regards..
User | Count |
---|---|
84 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.