Re: changing SID

Per Nystrom wrote:
>
> 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?
>
> Two other things...
> 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.
>
> 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
>
> [ Part 2: "Included Message" ]
>
> Date: 26 Oct 95 21:54:18
> From: "Anthony J. Holbrook " <AHOLBROO_at_shell.netmagic.net>
> To: v7inst_at_shell.netmagic.net
> Cc: pnystrom_at_wsitd.com
> Subject: DBA1: How to change database name?
>
> HERE'S MY COMPLETE SMARTY PANTS LIST OF HOW TO CHANGE THE NAME
> OF AN EXISTING DATABASE. (If you care)
>
> Change name of database from DBA37 to DBAPER
>
> o Bring up DBA37
> o Issue ALTER DATABASE BACKUP CONTROLFILE TO TRACE in sqldba
> o shutdown DBA37
> o cp initDBA37.ora initDBAPER.ora
> vi initDBAPER.ora
> 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 copy trace to new name : cp dba37_ora_5363.trc new_name.sql
> o edit new_name.sql
> 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)
> o SQLDBA> RECOVER DATABASE USING BACKUP CONTROLFILE;
> 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
> Specify log: {<RET>=suggested | filename | AUTO | FROM logsource | CANCEL}
>
> 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
> 1 row selected.
>
> ...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

Very interesting !
Thanks for that great info ! I'll try it, if works ! Cheers !

-- 
Lord Raiden
Thunder Deity 
Pinoy Oracle DBA

It is not the weapon that makes a warrior ........
But it is the spirit by which he wields it ........
Your spirit is your true shield ...................

                                           O-Sensei