House of Brick Senior Consultant

One of the many tasks a DBA will perform is cloning a database. Usually this is done from a production database to a quality assurance or test database to provide production volume data. If the database is on a virtual machine, cloning could occur at the VM level.

Cloning does exactly what it means, creating an exact duplicate of a database. Unfortunately, that ends up with you having two databases with the same name and DBID in your network. Sometimes you can resolve it sufficiently with aliasing at the TNSNAMES level.

Other times, you will need to change the database name and the DBID internally. Oracle has a utility for this, the NEWID application.

This document will detail the steps required to perform the database name change using NEWID.

Terminology and Definitions

The following terminology and definitions will be used in this document.

SID – Oracle Site Identifier. This is the database name that is being changed.
PFILE – Oracle Parameter file. Located in $ORACLE_HOME/dbs folder.
SPFILE – Oracle Server Parameter file. Also located in $ORACLE_HOME/dbs folder.
ORATAB – Oracle configuration file that associates the Oracle SIDs with Oracle homes. Located at /etc/oratab.
TNSNAMES – Oracle network configuration file that associates incoming connections with Oracle SIDs. Located in $ORACLE_HOME/network/admin folder.

Database Name
The database name, or SID, is being changed from prd to qa. This will be referenced when appropriate in the document.

Overview of Process
There are four steps to perform to change an Oracle database name (SID).

1. Preliminary prep work
   
2. Use NID application
3. Change SID References
4. Start database and finalize changes

1.  Preliminary prep work

After the database name change, changes will need to be made to the Oracle parameter file. If the database is using an server parameter file (SPFILE) instead of a parameter file (PFILE), you will need to convert from a SPFILE to a PFILE before the database name change occurs.

1.1  Determining if SPFILE is in Use
Run the following query to see if your database was started with a PFILE or SPFILE:

Figure 1.1 Query to see if SPFILE is in use

Run this query to determine if database starts with a PFILE or SPFILE.

If the database starts with a PFILE, no further prep work is necessary and you can proceed to step 2 – Use NID application. Otherwise, continue with the next step.

1.2  Creating a PFILE from SPFILE
Run the following sql to create a PFILE from an SPFILE:

Figure 1.2  Creating a PFILE from SPFILE.

Run this command to create a PFILE from an SPFILE.

This will create a PFILE in the $ORACLE_HOME/dbs directory. It will use the current SID in the name. Since the current SID of the database is prd, the name will be initprd.ora. Copy the file to have the new SID in its name. For this example, the new name would be initqa.ora. See Figure 1.3 for an example of copying the file.

Figure 1.3  Copying the old PFILE to its new name

Example of copying the old PFILE, initdwprd.ora to the new PFILE, initdwqa.ora.

2. Use NID application

To use the NID application, you will need to log into the database server as the oracle user and perform the following steps.

1. Shutdown the database
2. Start the database in mount mode
3. Run the NID command
4. Shut the database down

2.1  Shutdown the Database
Start a SQLPlus session and enter the command shutdown immediate. Figure 2.1 demonstrates this step.

Figure 2.1 Shutting down the database

Example of output when shutting down the database.

2.2 Starting the Database to Mount Mode

Start a SQLPlus session and enter the command startup mount. Figure 2.2 shows this being done.

Figure 2.2 Starting the database to mount mode

Example of Output when starting the database to mount mode.

Enter exit to return back to the shell prompt.

2.3 Run the NID command

The NID command is the Oracle program that changes the SID of the database. To use it, you will need the SYS account password and the new SID for the database. With the new SID being qa, the command line will be nid target=SYS dbname=qa. After you press enter, you will be prompted for the SYS account password. Enter the password. You will be prompted to confirm that you want to change the SID. See figure 2.3.1 for the screen display.

Figure 2.3.1  Screen display of running NID command

Output from Starting the NID command.

Enter Y to commit to changing the database name as show in figure 2.3.2

Figure 2.3.2  Confirming the Database name change

Answering Y to the confimation.

The program will make the internal changes and display it’s progress. When it is finished, you will be returned to the shell prompt as shown in Figure 2.3.3.

Figure 2.3.3  Output from NID program

Output from NID program showing a successful completion.

2.4 Shut the database down

The database will need to be shut back down. Repeat starting a SQLPlus session and enter the command shutdown immediate as shown in Step 2.1.

3.  Change SID references

Changes will need to be made in the database parameter, tnsnames, oratab, and environment files to reflect the change of the database SID.

1. Oracle parameter file needs to be renamed and edited.
2. Oratab needs to have the SID renamed.
3. ora file needs the SID renamed
4. If the ORACLE_SID is set in the profile, it will need to be changed.

3.1 Oracle parameter file needs to be renamed and edited

The Oracle parameter file will need to be edited and have two lines changed. Change to where the parameter file is stored, the $ORACLE_HOME/dbs folder. See figure 3.1.1 for an example.

Figure 3.1.1  Changing to the parameter file folder

Example of changing to the parameter file folder to prepare for editing parameter file.

Edit the file and locate the two lines that need to be changed. The lines start with dbname and instance_name. See Figure 3.1.2 for a example of how the parameter file would look before the changes.

Figure 3.1.2  Query to see if SPFILE is in use.

Lines that are highlighted need to be changed to new SID name.

You will need to change the SID value for the two lines from old SID, prd, to the new SID, qa. See Figure 3.1.3 for how the lines would look after the changes.

Figure 3.1.3  Query to see if SPFILE is in use

Parameter file showing SID name changes after editing.

Save the file and exit the editor.

3.2  Oratab needs to have the SID renamed

The Oracle ORATAB file, /etc/oratab, will need the have the SID reference changed from the old SID. prd to the new SID. qa. Edit the file and change the old SID entry to the new SID. See Figure 3.2.1 for an example.

Figure 3.2.1  Example of modified ORATAB file /etc/oratab

Example ORATAB file showing changed SID reference from dwprd to dwqa. The changed value is in bold.

3.3 TNSNAMES needs to have the SID renamed

The Oracle TNSNANES file, tnsnames.ora, will need the have the SID reference changed from the old SID. dwprd to the new SID. dwqa. It will also need the HOST address changed to match the new system. TNSNAMES is located in the folder $ORACLE_HOME/network/admin. Edit the file and change the old SID entry to the new SID, and change the HOST address to match the new system. See Figure 3.3.1 for an example.

Figure 3.3.1  Example of modified TNSNAMES file $ORACLE_HOME/network/admin/tnsnames.ora

Example TNSNAMES file showing changed SID reference from dwprd to dwqa and HOST address set to new address. The changed value is in bold.

3.4  If the ORACLE_SID is set in the profile, it will need to be changed.

Usually, the environment variable $ORACLE_SID is set to the SID value in the login profile script, $HOME/.profile or $HOME/.bash_profile. Locate and change the SID setting to the new value. Figure 3.4.1 shows an example of displaying the $ORACLE_SID value from the command line.

Figure 3.4.1  Displaying the $ORACLE_SID value

Using the echo command to display the $ORACLE_SID value.

You will need to log out and log back in after changing the ORACLE_SID value in the profile script to have it set in your environment.

4.  Start database and finalize changes

The last step that needs to be completed is to start the database up to mount level and open it with the resetlogs option. Start a SQLPlus session, start the database up to mount mode by entering startup mount. If you had to create a PFILE in Step 1, the start command is startup pfile=initSID.ora mount. So for our example, it would be startup pfile=initqa.ora mount. Then open the database with the alter database open resetlogs; command. See figure 4.1 for an example of the screen display.

Figure 4.1  Starting up the database and resetting logs

Starting the database up in mount mode and opening with the resetlogs option.

If you had to create a PFILE in Step 1, you will need to convert the PFILE back to an SPFILE. This is done by using the CREATE SPFILE command. At the SQL prompt, enter the command create spfile from pfile.

Conclusion

Changing the Database name of an Oracle database may sound complicated, but can be performed with basic systems skills. Following the procedures outlined in this document, a DBA can perform the task with minimal down time for the database.

References:

DBNEWID Utility – Oracle Documentation