修改数据库的oracle_sid/instance name ,db_name

雨点打透心脏的1/2处 2022-08-06 08:15 179阅读 0赞

修改db\_name需要重建控制文件

参见如下文档（文档虽然很老但修改步骤很全）

<table> 
 <tbody> 
  <tr> 
   <td align="left"><span style="font-size:14px; text-align:right; padding:0px 6px 0px 0px; font-weight:bold">How to Rename Database/Change DB_NAME or ORACLE_SID/Instance Name (文档 ID 15390.1)</span></td> 
   <td align="right">&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
 </tbody> 
</table>

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

![t.gif][]

<table style="table-layout:fixed; overflow:hidden"> 
 <tbody> 
  <tr> 
   <td style="overflow:hidden; vertical-align:top"> 
    <div style="overflow:auto"> 
     <span style="font-size:12px"></span> 
     <p><strong>In this Document</strong><br> </p> 
     <table> 
      <tbody> 
       <tr> 
        <td></td> 
        <td><a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=3qze7g3xu_9&amp;_afrLoop=265765132412251#PURPOSE" rel="nofollow">Purpose</a></td> 
       </tr> 
      </tbody> 
     </table> 
     <table> 
      <tbody> 
       <tr> 
        <td>&nbsp;</td> 
        <td><a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=3qze7g3xu_9&amp;_afrLoop=265765132412251#SCOPE" rel="nofollow">Scope</a></td> 
       </tr> 
      </tbody> 
     </table> 
     <table> 
      <tbody> 
       <tr> 
        <td>&nbsp;</td> 
        <td><a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=3qze7g3xu_9&amp;_afrLoop=265765132412251#BODYTEXT" rel="nofollow">Details</a></td> 
       </tr> 
      </tbody> 
     </table> 
     <table> 
      <tbody> 
       <tr> 
        <td>&nbsp;</td> 
        <td><a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=3qze7g3xu_9&amp;_afrLoop=265765132412251#aref_section31" rel="nofollow">Find the current DB_NAME and ORACLE_SID:</a></td> 
       </tr> 
      </tbody> 
     </table> 
     <table> 
      <tbody> 
       <tr> 
        <td>&nbsp;</td> 
        <td><a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=3qze7g3xu_9&amp;_afrLoop=265765132412251#aref_section32" rel="nofollow">Modify a database to run under a new ORACLE_SID:</a></td> 
       </tr> 
      </tbody> 
     </table> 
     <table> 
      <tbody> 
       <tr> 
        <td>&nbsp;</td> 
        <td><a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=3qze7g3xu_9&amp;_afrLoop=265765132412251#aref_section33" rel="nofollow">Change the "db_name" for a Database</a></td> 
       </tr> 
      </tbody> 
     </table> 
     <table> 
      <tbody> 
       <tr> 
        <td>&nbsp;</td> 
        <td><a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=3qze7g3xu_9&amp;_afrLoop=265765132412251#REF" rel="nofollow">References</a></td> 
       </tr> 
      </tbody> 
     </table> 
     <hr> 
     <p></p> 
     <h2 style="border-bottom-width:1px; border-bottom-style:solid; border-bottom-color:rgb(196,209,230); font-size:16px; background-color:rgb(119,119,115); color:rgb(255,255,255); text-transform:uppercase; padding-top:6px; padding-bottom:6px; padding-left:8px"> APPLIES TO:</h2> Oracle Database - Enterprise Edition - Version 7.0.16.0 and later 
     <br> Generic UNIX 
     <br> Checked for relevance on 06-Nov-2007 
     <br> 
     <br> 
     <br> 
     <h2 style="border-bottom-width:1px; border-bottom-style:solid; border-bottom-color:rgb(196,209,230); font-size:16px; background-color:rgb(119,119,115); color:rgb(255,255,255); text-transform:uppercase; padding-top:6px; padding-bottom:6px; padding-left:8px"> <a rel="nofollow"></a>PURPOSE</h2> 
     <p>This document describes how to find and change the "db_name" for a database, or the ORACLE_SID for an instance, without recreating the database.</p> 
     <p>This Note is not intented for cloning from existing database, That will be covered in the&nbsp;<a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&amp;sourceId=15390.1&amp;id=562556.1" title="Note 562556.1 How to Manually Clone a Database to Another Node" rel="nofollow">Note&nbsp;562556.1</a></p> 
     <p>To change the DB Name or DBD using NID Utility , Refer&nbsp;<a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&amp;sourceId=15390.1&amp;id=863800.1" rel="nofollow">Doc ID 863800.1</a></p> 
     <p></p> 
     <h2 style="border-bottom-width:1px; border-bottom-style:solid; border-bottom-color:rgb(196,209,230); font-size:16px; background-color:rgb(119,119,115); color:rgb(255,255,255); text-transform:uppercase; padding-top:6px; padding-bottom:6px; padding-left:8px"> <a rel="nofollow"></a>SCOPE</h2> 
     <p>For DBAs requiring to either find or change the db_name or ORACLE_SID.</p> 
     <h2 style="border-bottom-width:1px; border-bottom-style:solid; border-bottom-color:rgb(196,209,230); font-size:16px; background-color:rgb(119,119,115); color:rgb(255,255,255); text-transform:uppercase; padding-top:6px; padding-bottom:6px; padding-left:8px"> <a rel="nofollow"></a>DETAILS</h2> 
     <a rel="nofollow"></a> 
     <h3 style="font-size:14px; color:rgb(51,51,51); padding-top:0.5em">Find the current DB_NAME and ORACLE_SID:</h3> 
     <p>Query the views v$database and v$thread.<br> <br> V$DATABASE gives DB_NAME<br> V$THREAD gives ORACLE_SID<br> <br> If ORACLE_SID = DB_SID and db_name = DBNAME:<br> <br> To find the current value of ORACLE_SID:<br> (For Oracle9i/Oracle10g, must use SQL*Plus)&nbsp;<br> <br> SVRMGR&gt; select instance from v$thread;<br> <br> INSTANCE<br> ----------------<br> DB_SID<br> <br> To find the current value of DB_NAME:<br> <br> SVRMGR&gt; select name from v$database;<br> <br> NAME<br> ---------<br> DBNAME</p> 
     <a rel="nofollow"></a> 
     <h3 style="font-size:14px; color:rgb(51,51,51); padding-top:0.5em">Modify a database to run under a new ORACLE_SID:</h3> 
     <p>1. Shutdown the instance&nbsp;<br> <br> The database must be shutdown with SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE.<br> It must not be shutdown abnormally using SHUTDOWN ABORT.<br> <br> 2. Backup all control, redo, and data files.&nbsp;<br> <br> 3. Go through the environment settings and redefine the ORACLE_SID environment variable to a new value.<br> For Unix, you can check for instance .profile, .cshrc, .login, oratab.<br> On Unix, you can also search through disks and do a grep ORACLE_SID *&nbsp;<br> For Windows use the 'set' command in a command window and make corrections via the System Control Panel applet (Advanced tab &gt; Environment Variables).<br> For all Operating systems, check the tnsnames.ora.<br> <br> 4. Change locations to the "dbs" directory (or "database" directory for Windows)&nbsp;<br> <br> $ cd $ORACLE_HOME/dbs&nbsp;<br> <br> and rename the following files:</p> 
     <ul> 
      <li>init&lt;sid&gt;.ora (or use pfile to point to the init file)</li> 
      <li>control file(s). This is optional if you do not rename any of the controlfiles, and the control_files parameter is used. The "control_files" parameter is set in the "init&lt;SID&gt;.ora" file or in a file it references with the ifile parameter. Make sure that the control_file parameter does not point to old file names, if they have been renamed.&nbsp;</li> 
      <li>"crdb&lt;sid&gt;.sql" &amp; "crdb2&lt;sid&gt;.sql", This is optional, these are only used at database creation.</li> 
     </ul> 
     <p><br> NOTE: for Oracle9i and higher, if using an SPFILE: You must rename it (i.e. spfile&lt;sid&gt;.ora) along with changing parameter values in the SPFILE. Please review the following<br> for more information on SPFILE.<br> <a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&amp;sourceId=15390.1&amp;id=162491.1" rel="nofollow">Note 162491.1</a>&nbsp;Spfile and Init.ora Parameter File Startup of an Oracle9i Instance<br> <a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&amp;sourceId=15390.1&amp;id=137483.1" rel="nofollow">Note 137483.1</a>&nbsp;How to Modify the Content of an SPFILE Parameter File<br> <br> 5. To rename the database files and redo log files, follow the&nbsp; instructions in&nbsp;<a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&amp;sourceId=15390.1&amp;id=9560.1" rel="nofollow">Note 9560.1</a>.&nbsp;<br> <br> 6. Change the ORACLE_SID environment variable to the new value.&nbsp;<br> <br> 7. Check in the "$ORACLE_HOME/dbs" directory to see if the password&nbsp; file has been enabled. If enabled, the file "orapw&lt;OLD_SID&gt;" will exist and a new password file for the new SID must be created&nbsp; (renaming the old file will not work). If "orapw&lt;OLD_SID&gt;" does not&nbsp; exist, skip to step 8. To create a new password file, issue&nbsp; the following command as oracle owner:&nbsp;<br> <br> orapwd file=orapw&lt;NEWSID&gt; password=?? entries=&lt;number of users to be&nbsp;<br> granted permission to start the database instance&gt;&nbsp;<br> <br> 8. Start up the database and verify that it works. Once this is done,&nbsp; shutdown the database and take a final backup of all control, redo,&nbsp; and data files.&nbsp;<br> <br> The database must be shutdown with SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE.<br> It must not be shutdown abnormally using SHUTDOWN ABORT.<br> <br> 9. When the instance is started, the control file is updated with the&nbsp; current ORACLE_SID.</p> 
     <a rel="nofollow"></a> 
     <h3 style="font-size:14px; color:rgb(51,51,51); padding-top:0.5em">Change the "db_name" for a Database</h3> 
     <p>1. Login to Server Manager (Oracle7 through Oracle8i)/ SQL*Plus for Oracle9i/Oracle10g<br> <br> % svrmgrl&nbsp;<br> SVRMGR&gt; connect internal&nbsp;<br> <br> NOTE: For Oracle9i/Oracle10g, must use SQL*Plus (svrmgrl is obsolete after Oracle8i)<br> % sqlplus "/ as sysdba"<br> <br> 2. Type<br> <br> SVRMGR&gt; alter system switch logfile;<br> <br> to force a checkpoint.<br> <br> 3. Type&nbsp;<br> <br> SVRMGR&gt; alter database backup controlfile to trace resetlogs;&nbsp;<br> <br> This will create a trace file containing the "CREATE CONTROLFILE"&nbsp; command to recreate the controlfile in its current form.&nbsp;<br> <br> 4. Shutdown the database and exit SVRMGR&nbsp;<br> <br> SVRMGR&gt; shutdown<br> <br> SVRMGR&gt; exit&nbsp;<br> <br> The database must be shutdown with SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE.<br> It must not be shutdown abnormally using SHUTDOWN ABORT.<br> <br> 5. Change locations to the directory where the trace files are located.&nbsp; They are usually in the "$ORACLE_HOME/rdbms/log" directory. If&nbsp; "user_dump_dest" is set in the "init&lt;SID&gt;.ora" file, then go to the&nbsp; directory listed in the "user_dump_dest" variable. The trace file will&nbsp; have the form "ora_NNNN.trc with NNNN being a number.&nbsp;<br> <br> NOTE: for Oracle9i/Oracle10g if using an SPFILE: Please review the following for changing parameter values in the SPFILE.&nbsp;&nbsp;<a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&amp;sourceId=15390.1&amp;id=137483.1" rel="nofollow">Note 137483.1</a>&nbsp;How to Modify the Content of an SPFILE Parameter File<br> <br> 6. Copy the contents of the trace file starting from the line with STARTUP NOMOUNT down to the end of the trace file and put it in a new file called something like "ccf.sql".<br> <br> 7. Edit the "ccf.sql" file&nbsp;<br> <br> FROM: CREATE CONTROLFILE REUSE DATABASE "olddbname" RESETLOGS ...&nbsp;<br> TO: CREATE CONTROLFILE set DATABASE "newdbname" RESETLOGS ...&nbsp;<br> <br> Change the word 'REUSE' to 'set' and the 'olddbname' to 'newdbname'.<br> <br> It is possible to recreate the controlfile using the syntax:<br> CREATE CONTROLFILE REUSE set DATABASE "newdbname" RESETLOGS ...<br> <br> But this syntax will allow the existing controlfiles to be overwritten without giving an error.<br> <br> FROM:<br> # Recovery is required if any of the datafiles are restored backups,<br> # or if the last shutdown was not normal or immediate.<br> RECOVER DATABASE USING BACKUP CONTROLFILE<br> TO:<br> # Recovery is required if any of the datafiles are restored backups,<br> # or if the last shutdown was not normal or immediate.<br> # RECOVER DATABASE USING BACKUP CONTROLFILE<br> <br> The last command in ccf.sql should be:<br> alter database open resetlogs<br> <br> NOTE : In cases of Oracle 9i, the script must to be modified and&nbsp; #(as comments) changed to -- as "#" is not recognized as a comment&nbsp; in SQL*Plus&nbsp;<br> <br> 8. Save and exit the "ccf.sql" file&nbsp;<br> <br> 9. Rename the old control files for backup purposes and so that they do not exist when creating the new ones.&nbsp;<br> <br> 10. Edit the "init&lt;SID&gt;.ora" file so that db_name="newdb_name" .&nbsp;<br> <br> NOTE: for Oracle9i/Oracle10g if using an SPFILE: Please review the following for&nbsp; changing parameter values in the SPFILE.&nbsp;<br> <a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&amp;sourceId=15390.1&amp;id=137483.1" rel="nofollow">Note 137483.1</a>&nbsp;How to Modify the Content of an SPFILE Parameter File<br> <br> 11. Login to Server Manager&nbsp;<br> <br> % svrmgrl<br> SVRMGR&gt; connect internal<br> <br> NOTE: For Oracle9i/Oracle10g, must use SQL*Plus (svrmgrl is obsolete after Oracle8i)<br> % sqlplus "/ as sysdba"<br> <br> 12. Run the "ccf.sql" script&nbsp;<br> <br> SVRMGR&gt; @ccf&nbsp;<br> <br> This will issue a startup nomount, and then recreate the controlfile.<br> <br> If, at this point, an error stating that a file needs media recovery is reported, then the database was not shutdown normally as specified&nbsp; in step 4. Try recovering the database using the redo in the current&nbsp; logfile, by issuing:<br> <br> SVRMGRL&gt; recover database using backup controlfile until cancel;<br> <br> This will prompt for an archived redologfile. It may be possible to&nbsp; open the database after applying the current logfile. BUT this is not&nbsp; guaranteed.&nbsp;<br> <br> To apply the necessary redo, check the online logfiles and apply the&nbsp; one with the same sequence number as reported in the message. This&nbsp; usually is the logfile with status=CURRENT. If not apply, the logfiles&nbsp; in turn until the logfile with status=CURRENT has been applied. If,&nbsp; after applying the current logfile, the database will not open then it&nbsp; is highly likely that the operation must be restarted having shutdown&nbsp; the database normally.<br> <br> To find a list of the online logfiles:<br> <br> SVRMGR&gt; select group#, seq#, status from v$log;<br> GROUP# SEQUENCE# STATUS<br> ---------- --------- ----------------<br> 1 123 CURRENT &lt;== this redo needs to be applied<br> 2 124 INACTIVE<br> 3 125 INACTIVE<br> 4 126 INACTIVE<br> 5 127 INACTIVE<br> 6 128 INACTIVE<br> 7 129 INACTIVE<br> <br> 7 rows selected.<br> <br> SVRMGR&gt; select member<br> from v$logfile<br> where GROUP# = 1;<br> <br> Member<br> ------------------------------------<br> /u02/oradata/V815/redoV81501.log<br> <br> After applying the current online log file the following prompt should be displayed:<br> <br> Log Applied<br> Media Recovery Complete<br> <br> At this point the database can be opened with:<br> <br> SVRMGR&gt; alter database open resetlogs;&nbsp;<br> </p> 
     <p>13. After the 'alter database open (resetlogs)' command in the controlfile create script, there are commands to add the necessary temp files.<br> <br> Next example is from 11.2:<br> <br> ...<br> ALTER DATABASE OPEN RESETLOGS;<br> -- Commands to add tempfiles to temporary tablespaces.<br> -- Online tempfiles have complete space information.<br> -- Other tempfiles may require adjustment.<br> ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/v1120/tempfile/temp.271.786464435'<br> &nbsp;&nbsp;&nbsp;&nbsp; SIZE 288358400&nbsp; REUSE AUTOEXTEND ON NEXT 655360&nbsp; MAXSIZE 32767M;<br> -- End of tempfile additions.<br> <br> Please make sure, the required temp files are indeed added.<br> </p> 
     <p>14. The global database name may also need to be changed:<br> <br> alter database rename global_name to &lt;newdb_name&gt;.&lt;domain&gt;<br> <br> See&nbsp;<a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&amp;sourceId=15390.1&amp;id=1018634.102" rel="nofollow">Note 1018634.102</a>&nbsp;for further detail.</p> 
     <p>15. Make sure the database is working.</p> 
     <p>16. Shutdown and backup the database.<br> <br> The database must be shutdown with SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE.<br> It must not be shutdown abnormally using SHUTDOWN ABORT.<br> </p> 
     <div style="padding:10px; background-color:rgb(255,249,215); border:1px solid rgb(193,169,13)">
       NOTE: 
      <br> There are DBID and INSTANCE_NAME columns in DBSNMP.BSLN_BASELINES.&nbsp; You may need to verify that these values have changed as well.&nbsp; 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

[t.gif]: /images/20220806/6e37d97d5bad4667b65a3ced0be2b678.png