Changing a Database Name Using NEWID

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

SQL> SELECT DECODE(value, NULL, ‘PFILE’, ‘SPFILE’) “Init File Type
“FROM sys.v_$parameter WHERE name = ‘spfile’;

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.

SQL> create 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


[oracle@oracleqa]$ cd $ORACLE_HOME/dbs
[oracle@oracleqa]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@oracleqa]$ is –ls initped.ora
9 –rw-r—– 1 oracle oinstall 1106 Oct 11 20:17 initprd.ora
[oracle@oracleqa]$ cp initprd.ora initqa.ora
[oracle@oracleqa]$

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

[oracle@oracleqa]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 11 20:02:24 2014

Copyright © 1982, 2011, Oracle, All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
…

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

[oracle@oracleqa]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 11 20:02:24 2014Copyright © 1982, 2011, Oracle, All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.
Total System Global Area 535662593 bytes
Fixed Size 1346140 bytes
Variable Size 260048292 bytes
Database Buffers 268435456 bytes
Redo Buffers 5832704 bytes
Database mounted.
SQL>

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

[oracle@oracleqa]$ nid target=SYS dbname=qa
DBNEWID: Release 11.2.0.3.0 – Production on Sat Oct 11 20:05:54 2014

Copyright © 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Password:
Connected to database PRD (DBID=3949194393)

Connect to server version 11.2.0

Control Files in database:
/u01/app/oracle/oradata/prd/control01.ctl
/u01/app/oracle/oradata/prd/control02.ctl

Change database ID and database name PRD to QA? (Y/[N]) => _

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

[oracle@oracleqa]$ nid target=SYS dbname=qa
DBNEWID: Release 11.2.0.3.0 – Production on Sat Oct 11 20:05:54 2014

Copyright © 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Password:
Connected to database PRD (DBID=3949194393)

Connect to server version 11.2.0

Control Files in database:
/u01/app/oracle/oradata/prd/control01.ctl
/u02/app/oracle/oradata/prd/control02.ctl

Change database ID and database name PRD to QA? (Y/[N]) => Y
…

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

Datafile /u02/app/oracle/oradata/prd/dw03.db – dbid changed, wrote new name
Datafile /u02/app/oracle/oradata/prd/dw04.db – dbid changed, wrote new name
Datafile /u02/app/oracle/oradata/prd/dw05.db – dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/prd/temp01.db – dbid changed, wrote new name
Control File /u01/app/oracle/oradata/prd/control01.ctl – dbid changed, wrote new name
Control File /u02/app/oracle/oradata/prd/control02.ctl – dbid changed, wrote new name
Instance shut down

Database name changed to QA.
Modify parameter file and generate a new password file before restarting.
Database ID for database QA changed to 1714809981
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previos backups and archived logs in Recovery Area.
Database hase been shut down, open database with RESETLOGS option.
Successfully changed database name and ID.
DBNEWID – Completed successfully.

[oracle@oracleqa]$
…

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


[oracle@oracleqa]$ cd $ORACLE_HOME/dbs
[oracle@oracleqa]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@oracleqa]$ is –ls initqa.ora
9 –rw-r—– 1 oracle oinstall 1106 Oct 11 20:17 initqa.ora
[oracle@oracleqa]$

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.

…
dbname=”prd”
db_recovery_file_dest_size=64424509440
db_recovery_file_dest=”/u01/app/oracle/flash_recovery_area”
fast_start_mttr_target=120
instance_name=prd
…

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

…
dbname=”qa”
db_recovery_file_dest_size=64424509440
db_recovery_file_dest=”/u01/app/oracle/flash_recovery_area”
fast_start_mttr_target=120
instance_name=qa
…

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

# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
#1120:/u01/app/oracle/product/11.2.0:N
orcl:/u01/app/oracle/product/11.2.0/db_1:Y
#prd:/u01/app/oracle/product/11.2.0/db_1:Y
qa:/u01/app/oracle/product/11.2.0/db_1:Y;

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

(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)QA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.188.1.86)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = qa)
(UR = A)
)
)

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

…
[oracle@oracleqa]$ echo $ORACLE_SID
prd
[oracle@oracleqa]$
…

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

[oracle@oracleqa]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 11 20:02:24 2014
Copyright © 1982, 2011, Oracle, All rights reserved.
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.
Total System Global Area 535662593 bytes
Fixed Size                 1346140 bytes
Variable Size             260048292 bytes
Database Buffers         268435456 bytes
Redo Buffers               5832704 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.

SQL>
…

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

Table of Contents

Related Posts