Change the DBNAME with NID utility in Oracle

Change the DBNAME with NID utility in Oracle

Nid utility is used to change the dbname if required.

Following are the step to change the database name in Oracle with NID utility

Suppose database is installed on windows Server we are nameing from IC11G to ORCL, let go one by one steps

1. Check the location of datafiles

set line 200 pages 200
column name for a50
column file_name for a50
column member for a50
select name from v$controlfile;
select file_name from dba_data_files;
select file_name from dba_temp_files;
select member from v$logfile;

SQL> select name from v$controlfile;

NAME
------------------------------------------
E:\ORADATA\IC11G\CONTROL01.CTL
E:\ORADATA\IC11G\CONTROL02.CTL

SQL> select file_name from dba_data_files;

FILE_NAME
------------------------------------------
E:\ORADATA\IC11G\USERS01.DBF
E:\ORADATA\IC11G\UNDOTBS01.DBF
E:\ORADATA\IC11G\SYSAUX01.DBF
E:\ORADATA\IC11G\SYSTEM01.DBF

SQL> select file_name from dba_temp_files;

FILE_NAME
------------------------------------------
E:\ORADATA\IC11G\TEMP01.DBF

SQL> select member from v$logfile;

MEMBER
------------------------------------------
E:\ORADATA\IC11G\REDO03.LOG
E:\ORADATA\IC11G\REDO02.LOG
E:\ORADATA\IC11G\REDO01.LOG

Note: In windows under oradata folder, we can alos change folder name IC11g to ORCL
 
2. Shutdown the database for change name with nid process.

Shutdown immediate;

 
3. Startup the database at mount state for using NID utility

startup mount

 
4. Use NID utility to change the dbname
Note: Change dbname from IC11g to ORCL

SET oracle_Sid=IC11G

nid target=sys/sys123 dbname=ORCL

Output:
C:\Users\e3019447>nid target=sys/sys123 dbname=ORCL

DBNEWID: Release 11.2.0.4.0 - Production on Mon Jun 5 01:04:32 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to database IC11G (DBID=231578491)

Connected to server version 11.2.0

Control Files in database:
E:\ORADATA\IC11G\CONTROL01.CTL
E:\ORADATA\IC11G\CONTROL02.CTL

Change database ID and database name IC11G to ORCL? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 231578491 to 1473611297
Changing database name from IC11G to ORCL
Control File E:\ORADATA\IC11G\CONTROL01.CTL - modified
Control File E:\ORADATA\IC11G\CONTROL02.CTL - modified
Datafile E:\ORADATA\IC11G\SYSTEM01.DB - dbid changed, wrote new name
Datafile E:\ORADATA\IC11G\SYSAUX01.DB - dbid changed, wrote new name
Datafile E:\ORADATA\IC11G\UNDOTBS01.DB - dbid changed, wrote new name
Datafile E:\ORADATA\IC11G\USERS01.DB - dbid changed, wrote new name
Datafile E:\ORADATA\IC11G\TEMP01.DB - dbid changed, wrote new name
Control File E:\ORADATA\IC11G\CONTROL01.CTL - dbid changed, wrote new name
Control File E:\ORADATA\IC11G\CONTROL02.CTL - dbid changed, wrote new name
Instance shut down

Database name changed to ORCL.
Modify parameter file and generate a new password file before restarting.
Database ID for database ORCL changed to 1473611297.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

5. After NID utility done, try to startup the database in open state

ALTER SYSTEM SET DB_NAME=ORCL SCOPE=spfile;
startup nomount;
alter database mount;
alter database open resetlogs;

Note: Following error may occurred after nid utility
ORA-01103: database name ‘ORCL’ in control file is not ‘IC11G’

Solution: Alter db_name parameter in SPFile
ALTER SYSTEM SET DB_NAME=ORCL SCOPE=spfile;

When you start database may error occurred so change your environment variable to ORACLE_SID to ORCL
Set ORACLE_SID=ORCL
OR
Change the services name on window platform
oradim -Delete -SID IC11g
oradim -NEW -SID ORCL

ORA-12560: TNS: Protocol adapter error
Go to folder %oracle_home%\database in windows:
Rename the password file from PWDIC11g.ora to PWDORCL.ora
Rename Spfile from SPFILEIC11g.ora to SPFILEORCL.ora

6. If you like to move database as new database name as default setting, it better to rename the folder associated with database for future better handling.
Note: We are renaming all folder associated with old oracle home like oradata has IC11g name and change parameter control_file to move in new destination.

-- Creating Pfile for editing parameters
Create pfile='E:\pfile.txt' from spfile;

-- Spooling rename file from IC11g to ORCL folder
set line 200 pages 200
spool E:\rename.txt
-- rename datafile
select 'Alter database rename file '''||file_name||''' TO '''||replace(file_name,'IC11G','ORCL')||''';' from dba_Data_files;
-- rename temp files
select 'Alter database rename file '''||file_name||''' TO '''||replace(file_name,'IC11G','ORCL')||''';' from dba_temp_files;
-- rename redo log files
select 'Alter database rename file '''||member||''' TO '''||replace(member,'IC11G','ORCL')||''';' from v$logfile;
spool off

7. Shutdown the database

shutdown immediate;

8. Edit the pfile.txt and change the parameter as needed.
I found following parameter using IC11g as default location, Change to ORCL

*.audit_file_dest='C:\Oracle11g\admin\ORCL\adump'
*.control_files='E:\Oradata\ORCL\control01.ctl','E:\Oradata\ORCL\control02.ctl'

9. Now move on to windows and check the location which mentioned is present

Note: Rename the folder from IC11g to ORCL in windows

Rename the folder in admin directory from IC11g to ORCL
Rename the folder under oradata directory from IC11g to ORCL

10. Startup nomount the database from pfile

sqlplus sys as sysdba
create spfile from pfile='E:\pfile.txt'
startup nomount;
alter database mount;

11. Change the location of the database file with rename folder
Note: Fire the alter command created in step 6.

Alter database rename file 'E:\ORADATA\IC11G\USERS01.DBF' TO 'E:\ORADATA\ORCL\USERS01.DBF';
Alter database rename file 'E:\ORADATA\IC11G\UNDOTBS01.DBF' TO 'E:\ORADATA\ORCL\UNDOTBS01.DBF';
Alter database rename file 'E:\ORADATA\IC11G\SYSAUX01.DBF' TO 'E:\ORADATA\ORCL\SYSAUX01.DBF';
Alter database rename file 'E:\ORADATA\IC11G\SYSTEM01.DBF' TO 'E:\ORADATA\ORCL\SYSTEM01.DBF';
Alter database rename file 'E:\ORADATA\IC11G\TEMP01.DBF' TO 'E:\ORADATA\ORCL\TEMP01.DBF';
Alter database rename file 'E:\ORADATA\IC11G\REDO03.LOG' TO 'E:\ORADATA\ORCL\REDO03.LOG';
Alter database rename file 'E:\ORADATA\IC11G\REDO02.LOG' TO 'E:\ORADATA\ORCL\REDO02.LOG';
Alter database rename file 'E:\ORADATA\IC11G\REDO01.LOG' TO 'E:\ORADATA\ORCL\REDO01.LOG';

12. After open the database in normal mode:

alter database open;

13. Change the service name in listener.ora and tnsnames.ora
 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.