Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 17 June 2012

How to change DBNAME or Oracle SID for a Database Without Recreating ‎the Database

Overview: It describes how to change the DB_NAME for a database or the ORACLE_SID for an instance without recreating the database.  
Use: In case you are requiring to change the db_name or ORACLE_SID
OS & Database Version used in Example: Oracle 9i on Windows 2003 env.
---------------------------------------------------------------------------------------------------------------- 
Modifying a Database to run under new Oracle_SID
Step1: Query to find Current DB_NAME and ORACLE_SID





















Note: Take proper online and offline backup before going to perform actual changing of database name.
Step3: Change the location of dbs directory  and rename the “initSHAAN.ora” sid_name=”new_sid”
Note: 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<SID>.ora" file or in a file it references with the pfile parameter.

In different case if you rename the control file then make sure that control_files parameter does not point to the old control file names.
Note: To rename the database files and redo log files, you would follow the instructions in <Note:9560.1>.

Step4: Create the new password file (renaming the old password file will not work here)
C:\>SET ORACLE_SID=”New Value”;
C:\>orapwd file=orapw<NEWSID> password=oracle entries=5;
or
C:\>orapwd file=D:\oracle\ora92\database\PWDshaan1.ora PASSWORD=oracle entries=5

Step5: Create new service name (use oradim) and check the proper entry in tnsname.ora and listener.ora
C:\>ORADIM -NEW -SID shaan1 -PFILE=’D:\oracle\admin\shaan\pfile\initshaan.ora’;
LISTENER Configuration:
Make the proper entry in TNSname.ora and Listener.ora as either manually such as or better to use "NET Manager"
SHAAN1.AL-SADHAN.COM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ittest.al-sadhan.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = dedicated)
      (SERVICE_NAME = shaan1)
    )
  )
# LISTENER.ORA Network Configuration File: C:\oracle1\network\admin\listener.ora
# Generated by Oracle configuration tools.
LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ittest)(PORT = 1521))
  )


SID_LIST_LISTENER =
  (SID_LIST =
      (SID_DESC =
      (GLOBAL_DBNAME = shaan1)
      (SID_NAME = shaan1)
    )
  )
To check tns and listener in command prompt:
C:\ Tnsping shaan1
If it is responding ok, now check and restart the Listener
C:\lsnrctl
LSNRCTL> status
LSNRCTL>STOP LISTENER
LSNRCTL>START LISTENER
Step6: Start up the database and verify that it works. Once you have done  this, shutdown the database and take a final backup of all CRD files.
Note: When the instance is started, the control file is updated with the  current Oracle_SID. 

Changing the "DB_NAME" for a Database:
Step1: Login with sysdba and force a checkpoint then Create a trace file to recreate the controlfile in its current form.

















Step2: Shutdown the database and exit from SQLplus
Note: The database must be shutdown with SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE. It must not be shutdown abnormally using SHUTDOWN ABORT.

Step3: change the location to the directory where your trace file is located. Check the UDUMP folder. Pick the newly created trace file. Copy it to some other location.
Step4: Get the "CREATE CONTROLFILE" command from the trace file and put it in a  new file like "shaan.sql".
Step5: Edit the "shaan.sql" file  
     FROM: CREATE CONTROLFILE REUSE DATABASE "olddbname" NORESETLOGS
     TO: CREATE CONTROLFILE set DATABASE "newdbname"  RESETLOGS
    FROM:
    # Recovery is required if any of the datafiles are restored backups,
    # or if the last shutdown was not normal or immediate.
    RECOVER DATABASE USING BACKUP CONTROLFILE
    TO:
    # Recovery is required if any of the datafiles are restored backups,
    # or if the last shutdown was not normal or immediate.
    # RECOVER DATABASE USING BACKUP CONTROLFILE

Step6: Save and exit the "shaan.sql" file.
Note: Rename the old control files for backup purposes and so that they do not exist when creating the new ones.

Step7: Edit the "init<SID>.ora or pfile" file so that db_name="newdb_name".
Step8: Connect with Sysdba and run this script (shaan.sql)
C:\ sqlplus /nolog
SQL> connect /
SQL>@d:\shaan.sql
This will issue a startup nomount and then recreate the controlfile. If at this point, you receive the error that a file needs media recovery, the database was not shutdown normally as specified above note. You can try with now recovering the database using the redo in the current logfile:
SQL>  recover database using backup controlfile;
Note: To apply the necessary redo you need to check the online logfiles and apply them on with the same sequence number as reported in the message.
SQL> SELECT GROUP#, SEQUENCE#, STATUS FROM V$LOG ORDER BY STATUS;
    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         2        276 CURRENT   < this redo needs to be applied>
         1        275 INACTIVE
         3        274 INACTIVE
SQL> select member from v$logfile  where GROUP# = 2;
MEMBER
------------------------------------------------------
C:\ORACLE1\ORADATA\SHAAN\REDO02.LOG
Note: The last command in shaan.sql should be “Alter database open resetlogs”;

Step9: Finally you may need to change the global database name
SQL>Alter database rename global_name to <newdb_name>.<domain>
Step10: Check and make sure the database is working properly and finally take the proper backup.

The same concept clearly explains by the Tom:

2 comments:

  1. Can use DBNEWID utility to change database name.

    ReplyDelete
  2. Yes, you can use NID since oracle 9i onward to change DBID and DBNAME. Please check out this link for the same: http://shahiddba.blogspot.com/2012/03/changing-dbid-and-dbname-1.html

    ReplyDelete