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