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.
Database Name : OMS112
New Database Name : OMSREP
Overview of Process
There are four steps to perform to change an Oracle database name (SID).
- Preliminary prep work
- Use NID application
- Change SID References
- Start database and finalize changes
1. PRELIMINARY PREP WORK
Once 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:
SQL> sho parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/12.1.0 .2/dbhome_1/dbs/spfileOMS112.ora |
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 the following query to see if your database was started with a PFILE or SPFILE:
SQL> create pfile from spfile; File created. |
Figure 1.3 Copying the old PFILE to its new name
[oracle@xxxxx ~]$ cd /u01/app/oracle/product/12.1.0.2/dbhome_1/dbs [oracle@xxxxx dbs]$ ll total 20 -rw-rw----. 1 oracle oinstall 1544 May 2 23:05 hc_OMS112.dat -rw-r-----. 1 oracle oinstall 1271 May 2 23:48 initOMS112.ora -rw-r--r--. 1 oracle oinstall 2992 Feb 3 2012 init.ora -rw-r-----. 1 oracle oinstall 24 May 2 22:31 lkOMS112 -rw-r-----. 1 oracle oinstall 3584 May 2 23:05 spfileOMS112.ora [oracle@xxxxx dbs]$ cp initOMS112.ora initOMSREP.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.
- Shutdown the database
- Start the database in mount mode
- Run the NID command
- Shut the database down
2.1 Shutdown the Database
[oracle@xxxxxxx dbs]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Tue May 2 23:56:16 2017 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 4294967296 bytes Fixed Size 2932632 bytes Variable Size 2466250856 bytes Database Buffers 1811939328 bytes Redo Buffers 13844480 bytes Database mounted. SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options [oracle@xxxxxxx dbs]$ nid target=sys dbname=OMSREP DBNEWID: Release 12.1.0.2.0 - Production on Wed May 3 00:03:32 2017 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Password: Connected to database OMS112 (DBID=187475667) Connected to server version 12.1.0 Control Files in database: /u02/oradata/OMS112/OMS112/control01.ctl /u02/oradata/OMS112/OMS112/control02.ctl Change database ID and database name OMS112 to OMSREP? (Y/[N]) => Y Proceeding with operation Changing database ID from 187475667 to 4204721757 Changing database name from OMS112 to OMSREP Control File /u02/oradata/OMS112/OMS112/control01.ctl - modified Control File /u02/oradata/OMS112/OMS112/control02.ctl - modified Datafile /u02/oradata/OMS112/OMS112/system01.db - dbid changed, wrote new name Datafile /u02/oradata/OMS112/OMS112/sysaux01.db - dbid changed, wrote new name Datafile /u02/oradata/OMS112/OMS112/undotbs01.db - dbid changed, wrote new name Datafile /u02/oradata/OMS112/OMS112/users01.db - dbid changed, wrote new name Datafile /u02/oradata/OMS112/OMS112/mgmt_ecm_depot1.db - dbid changed, wrote new name Datafile /u02/oradata/OMS112/OMS112/mgmt.db - dbid changed, wrote new name Datafile /u02/oradata/OMS112/OMS112/mgmt_deepdive.db - dbid changed, wrote new name Datafile /u02/oradata/OMS112/OMS112/audi - dbid changed, wrote new name Datafile /u02/oradata/OMS112/OMS112/temp01.db - dbid changed, wrote new name Control File /u02/oradata/OMS112/OMS112/control01.ctl - dbid changed, wrote new name Control File /u02/oradata/OMS112/OMS112/control02.ctl - dbid changed, wrote new name Instance shut down Database name changed to OMSREP. Modify parameter file and generate a new password file before restarting. Database ID for database OMSREP changed to 4204721757. All previous backups and archived redo logs for this database are unusable. Database is not aware of previous backups and archived logs in Recovery Area. Database has been shutdown, open database with RESETLOGS option. Succesfully changed database name and ID. DBNEWID - Completed succesfully. |
Output from NID program showing a successful completion.
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.
- Oracle parameter file needs to be renamed and edited.
- Oratab needs to have the SID renamed.
- ora file needs the SID renamed
- 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.
[oracle@xxxxx dbs]$ vi initOMSREP.ora *.db_name='OMSREP' *.db_recovery_file_dest='/u03/fast_recovery_area' *.db_recovery_file_dest_size=19587399680 *.db_unique_name='OMSREP' *.diagnostic_dest='/u01/app/oracle' |
3.2 Oratab needs to have the SID renamed
Edit the file and change the old SID entry to the new SID. stored, the $ORACLE_HOME/dbs folder.
# This file is used by ORACLE utilities. It is created by root.sh # and updated by either Database Configuration Assistant while creating # a database or ASM Configuration Assistant while creating ASM instance. # A colon, ':', is used as the field terminator. A new line terminates # the entry. Lines beginning with a pound sign, '#', are comments. # # Entries are of the form: # $ORACLE_SID:$ORACLE_HOME:<N|Y>: # # The first and second fields are the system identifier and home # directory of the database respectively. The third filed indicates # to the dbstart utility that the database should , "Y", or should not, # "N", be brought up at system boot time. # # Multiple entries with the same $ORACLE_SID are not allowed. # # OMSREP:/u01/app/oracle/product/12.1.0.2/dbhome_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
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.
stored, the $ORACLE_HOME/dbs folder.
[oracle@xxxxx dbs]$ vi /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/tnsnames.ora OMSREP = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = FCISCOPRDOEM.fcicanada.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = OMS112.fcicanada.com) ) ) LISTENER_OMSREP = (ADDRESS = (PROTOCOL = TCP)(HOST = FCISCOPRDOEM.fcicanada.com)(PORT = 1521)) |
4. START DATABASE AND FINALIZE CHANGES
SQL> startup nomount pfile='/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/initOMSREP.ora'; ORACLE instance started. Total System Global Area 4294967296 bytes Fixed Size 2932632 bytes Variable Size 2466250856 bytes Database Buffers 1811939328 bytes Redo Buffers 13844480 bytes SQL> create spfile from pfile; File created. SQL> shutdown immediate; ORA-01507: database not mounted ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 4294967296 bytes Fixed Size 2932632 bytes Variable Size 2466250856 bytes Database Buffers 1811939328 bytes Redo Buffers 13844480 bytes Database mounted. SQL> select name from v$database; NAME --------- OMSREP SQL> alter database open resetlogs; Database altered. |
No comments:
Post a Comment