Re: changing SID

Han,

I got this little pearl of wisdom from my DBA II instructor at Oracle. I have used it successfully to make a complete copy of a 2.5 GB production system very rapidly. Just remember to make a complete, COLD backup of your database before you start...

Oh, and by the way - Oracle does not condone or support this method of changing the SID. If you ask their support guys, they'll tell you to do a full export and re-import.

On Wed, 15 May 1996 07:14:24 GMT, Han Weegink <Han.Weegink_at_cmg.nl> wrote:
>
>What do I have to do in order to change the SID for an existing/operational data
> base ?
>I'm using Oracle 7.1.2.6 and SQL*Net 2.x
>
>Thanks,
>
>

• Forwarded message ---------- Date: Fri, 27 Oct 95 19:11:13 EDT From: Anthony J. Holbrook <AHOLBROO_at_us.oracle.com> To: pnystrom_at_wsitd.com Subject: Fwd: DBA1: How to change database name?

	o  Always backup the original database before tying to change its name 
	o  If you omit REUSE from the CREATE CONTROLFILE command, no recovery 
	   will be needed, but you will still need to RESETLOGS when you open 
	   the database. 
 

	o  CHANGE db_name = DBA37 
 	   TO     db_name = DBAPER 
	o  CHANGE control_files=/dbaclass7/dba37/data/cntrlDBA37.ctl 
	   TO     control_files=/dbaclass7/dba37/data/cntrlDBAPER.ctl 

	o  strip out all header info and # comment lines 
	o  change STARTUP NOMOUNT 
	   to     STARTUP NOMOUNT PFILE=$HOME/data/initDBAPER.ora 
	o  change : 
CREATE CONTROLFILE REUSE DATABASE "DBA37" NORESETLOGS NOARCHIVELOG 
	o  to : 
CREATE CONTROLFILE REUSE SET DATABASE DBAPER RESETLOGS NOARCHIVELOG 
	   (notice SET word and  
	    RESETLOGS must be specified to set a new database name) 
	o  remove the following lines altogether : 
	   RECOVER DATABASE 
	   ALTER DATABASE OPEN; 
 

o ORACLE_SID=DBAPER; export ORACLE_SID o SQLDBA> CONNECT INTERNAL
o SQLDBA> _at_new_name.sql

	   (successfully startup in nomount mode  
 	    and create new controlfile via this script) 

ORA-00279: Change 6995 generated at 10/26/95 19:18:30 needed for thread 1  
ORA-00289: Suggestion : /dbaclass7/dba37/data/log_260.arc 
ORA-00280: Change 6995 for thread 1 is in sequence #260   

o This is where things got tricky. Database wasn't in ARCHIVELOG mode.

   Prompt listed above is formed by SQLDBA using LOG_ARCHIVE_DEST and    LOG_ARCHIVE_FORMAT, but doesn't exist. Type in filename of online    redo log from previous DBA37 database.  

o SQLDBA> /dbaclass7/dba37/data/log1a.rdo Applying logfile...
ORA-00310: archived log contains sequence 259; sequence 260 required ORA-00334: archived log: '/dbaclass7/dba37/data/log1a.rdo' Specify log: {<RET>=suggested | filename | AUTO | FROM logsource | CANCEL}  

o ok, so I gave the wrong one...  

o SQLDBA> /dbaclass7/dba37/data/log2a.rdo Applying logfile...
Log applied.
Media recovery complete.
Specify log: {<RET>=suggested | filename | AUTO | FROM logsource | CANCEL}  

o Don't ask me why it prompted me again when media recover was

   complete. It would only take another SQLDBA command anyway. o SQLDBA> ALTER DATABASE OPEN RESETLOGS;

   Statement processed.

o  SQLDBA> SELECT * FROM V$DATABASE                                            
          
NAME      CREATED              LOG_MODE     CHECKPOINT ARCHIVE_CH 
--------- -------------------- ------------ ---------- ---------- 
DBAPER    10/26/95 19:34:49    NOARCHIVELOG       6998          0 

...and there was much rejoicing.    

Anthony J. Holbrook
ORACLE CORPORATION
Senior Instructor
500 Oracle Parkway
Box 659904
Redwood Shores, CA 94065
PHONE : 415.506.9952
FAX : 415.610.0443
Reply to : aholbroo_at_us.oracle.com Received on Tue May 28 1996 - 00:00:00 CEST