Changing a database name for Oracle database is a quite simple task, but I had been doing it manually until I have been shown the nid tool. Manually – I mean I had always been doing this by recreating a controlfile with the new database name. I tried the nid tool and I love it. I will show you all the steps you need to do to change db_name for standalone database without ASM.

FIRSTDB – source db_name
SECONDDB – destination db_name

1. Change database name with NID tool

shutdown immediate:
startup mount:
[oracle@server]$ nid target=SYS DBNAME=SECONDDB SETNAME=YES
DBNEWID: Release 12.1.0.2.0 - Production on Fri May 01 15:35:50 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to database FIRSTDB (DBID=2521804150)
Connected to server version 12.1.0
Control Files in database:
/u01/app/oracle/oradata/FIRSTDB/controlfile/o1_mf_bv4sypkc_.ctl
/u01/app/oracle/fast_recovery_area/FIRSTDB/controlfile/o1_mf_bv4syplx_.ctl
Change database name of database FIRSTDB to SECONDDB? (Y/[N]) => Y
Proceeding with operation
Changing database name from FIRSTDB to SECONDDB
Control File /u01/app/oracle/oradata/FIRSTDB/controlfile/o1_mf_bv4sypkc_.ctl - modified
Control File /u01/app/oracle/fast_recovery_area/FIRSTDB/controlfile/o1_mf_bv4syplx_.ctl - modified
Datafile /u01/app/oracle/oradata/FIRSTDB/datafile/o1_mf_system_bv4syqj4_.db - wrote new name
Datafile /u01/app/oracle/oradata/FIRSTDB/1BA280FFFB1D9736E0530C34EB0ADA9B/datafile/o1_mf_system_bv4syryt_.db - wrote new name
Datafile /u01/app/oracle/oradata/FIRSTDB/datafile/o1_mf_sysaux_bv4syw4j_.db - wrote new name
Datafile /u01/app/oracle/oradata/FIRSTDB/1BA280FFFB1D9736E0530C34EB0ADA9B/datafile/o1_mf_sysaux_bv4syx3c_.db - wrote new name
Datafile /u01/app/oracle/oradata/FIRSTDB/datafile/o1_mf_undotbs1_bv4syyb1_.db - wrote new name
Datafile /u01/app/oracle/oradata/FIRSTDB/datafile/o1_mf_users_bv4szftk_.db - wrote new name
Datafile /u01/app/oracle/oradata/FIRSTDB/datafile/o1_mf_audsys_bvk27sx8_.db - wrote new name
Datafile /u01/app/oracle/oradata/FIRSTDB/1C144561FEAC44F9E0530C34EB0AA6ED/datafile/o1_mf_system_bvmq49q6_.db - wrote new name
Datafile /u01/app/oracle/oradata/FIRSTDB/1C144561FEAC44F9E0530C34EB0AA6ED/datafile/o1_mf_sysaux_bvmq49qg_.db - wrote new name
Datafile /u01/app/oracle/oradata/FIRSTDB/1C144561FEAC44F9E0530C34EB0AA6ED/datafile/o1_mf_rman_bvmq4dp7_.db - wrote new name
Datafile /u01/app/oracle/oradata/FIRSTDB/datafile/o1_mf_temp_bv4syysn_.tm - wrote new name
Datafile /u01/app/oracle/oradata/FIRSTDB/1BA280FFFB1D9736E0530C34EB0ADA9B/datafile/o1_mf_temp_bv4syyt7_.tm - wrote new name
Datafile /u01/app/oracle/oradata/FIRSTDB/1C144561FEAC44F9E0530C34EB0AA6ED/datafile/o1_mf_temp_bvmq49qg_.db - wrote new name
Control File /u01/app/oracle/oradata/FIRSTDB/controlfile/o1_mf_bv4sypkc_.ctl - wrote new name
Control File /u01/app/oracle/fast_recovery_area/FIRSTDB/controlfile/o1_mf_bv4syplx_.ctl - wrote new name
Instance shut down
Database name changed to SECONDDB.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.

2. Create spfile based on the current spfile settings

*._ash_size=266338304
*.archive_lag_target=3600
*.audit_file_dest='/u01/app/oracle/admin/SECONDDB/adump
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/u01/app/oracle/oradata/SECONDDB/controlfile/o1_mf_bv4sypkc_.ctl','/u01/app/oracle/fast_recovery_area/SECONDDB/controlfile/o1_mf_bv4syplx_.ctl'
*.db_block_checking='MEDIUM'
*.db_block_checksum='FULL'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_domain=''
*.db_name='SECONDDB'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=10000m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=SECONDDBXDB)'
*.enable_pluggable_database=true
*.fast_start_mttr_target=180
*.global_names=TRUE
*.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.10)(PORT=5521))'
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='NORWEGIAN'
*.nls_territory='NORWAY'
*.open_cursors=300
*.os_authent_prefix=''
*.pga_aggregate_target=1000m
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.sec_protocol_error_trace_action='LOG'
*.session_cached_cursors=300
*.sga_target=5632m
*.sql92_security=TRUE
*.undo_tablespace='UNDOTBS1'
*.use_large_pages='ONLY'
startup nomount pfile='/tmp/spfileSECONDDB.ora';
create spfile from pfile='/tmp/spfileSECONDDB.ora';
File created

3. Rename directories to reflect new db name

mv /u01/app/oracle/admin/FIRSTDB /u01/app/oracle/admin/SECONDB
mv /u01/app/oracle/oradata/FIRSTDB /u01/app/oracle/oradata/SECONDDB
mv /u01/app/oracle/fast_recovery_area/FIRSTDB \ /u01/app/oracle/fast_recovery_area/SECONDB

4. Create password file

[oracle@server dbs]$ orapwd file=orapwSECONDDB password=passHidden entries=15 sysbackup=Y
Enter password for SYSBACKUP:

[oracle@server dbs]$ ls -ltr
total 20
-rw-rw---- 1 oracle oinstall 1544 Aug 12 15:48 hc_SECONDDB.dat
-rw-r----- 1 oracle oinstall 3584 Aug 12 15:53 spfileSECONDDB.ora
-rw-r----- 1 oracle oinstall 10752 Aug 12 16:00 orapwSECONDDB

5. Restart instance to nomount mode with new spfile

shutdown immediate; 
startup nomount;

ORACLE-instance started
Total System Global Area 5905580032 bytes
Fixed Size 6082976 bytes
Variable Size 1845497440 bytes
Database Buffers 4043309056 bytes
Redo Buffers 10690560 bytes

Verify key parameters

NAME TYPE VALUE
---------------------- ------------ ------------------------------
control_files string /u01/app/oracle/oradata/SECONDDB/cont
rolfile/o1_mf_bv4sypkc_.ctl, /
u01/app/oracle/fast_recovery_area/
SECONDDB/controlfile/o1_mf_bv4syp
lx_.ctl
db_recovery_file_dest string /u01/app/oracle/fast_recovery_area
db_create_file_dest string /u01/app/oracle/oradata
db_name string SECONDDB

6. Mount and change datafiles’ path

alter database mount;

How does it look like for FIRSTDB

select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/FIRSTDB/datafile/o1_mf_system_bv4syqj4_.dbf
/u01/app/oracle/oradata/FIRSTDB/1BA280FFFB1D9736E0530C34EB0ADA9B/datafile/o1_mf_syst
em_bv4syryt_.dbf
/u01/app/oracle/oradata/FIRSTDB/datafile/o1_mf_sysaux_bv4syw4j_.dbf
/u01/app/oracle/oradata/FIRSTDB/1BA280FFFB1D9736E0530C34EB0ADA9B/datafile/o1_mf_sysa
ux_bv4syx3c_.dbf
/u01/app/oracle/oradata/FIRSTDB/datafile/o1_mf_undotbs1_bv4syyb1_.dbf
/u01/app/oracle/oradata/FIRSTDB/datafile/o1_mf_users_bv4szftk_.dbf
/u01/app/oracle/oradata/FIRSTDB/datafile/o1_mf_audsys_bvk27sx8_.dbf
/u01/app/oracle/oradata/FIRSTDB/1C144561FEAC44F9E0530C34EB0AA6ED/datafile/o1_mf_syst
em_bvmq49q6_.dbf
/u01/app/oracle/oradata/FIRSTDB/1C144561FEAC44F9E0530C34EB0AA6ED/datafile/o1_mf_sysa
ux_bvmq49qg_.dbf
/u01/app/oracle/oradata/FIRSTDB/1C144561FEAC44F9E0530C34EB0AA6ED/datafile/o1_mf_rman
_bvmq4dp7_.dbf

Rename datafiles

alter database rename file '/u01/app/oracle/oradata/FIRSTDB/datafile/o1_mf_system_bv4syqj4_.dbf' to '/u01/app/oracle/oradata/SECONDDB/datafile/o1_mf_system_bv4syqj4_.dbf';
alter database rename file '/u01/app/oracle/oradata/FIRSTDB/1BA280FFFB1D9736E0530C34EB0ADA9B/datafile/o1_mf_system_bv4syryt_.dbf' to '/u01/app/oracle/oradata/SECONDDB/1BA280FFFB1D9736E0530C34EB0ADA9B/datafile/o1_mf_system_bv4syryt_.dbf';
alter database rename file '/u01/app/oracle/oradata/FIRSTDB/datafile/o1_mf_sysaux_bv4syw4j_.dbf' to '/u01/app/oracle/oradata/SECONDDB/datafile/o1_mf_sysaux_bv4syw4j_.dbf';
alter database rename file '/u01/app/oracle/oradata/FIRSTDB/1BA280FFFB1D9736E0530C34EB0ADA9B/datafile/o1_mf_sysaux_bv4syx3c_.dbf' to '/u01/app/oracle/oradata/SECONDDB/1BA280FFFB1D9736E0530C34EB0ADA9B/datafile/o1_mf_sysaux_bv4syx3c_.dbf';
alter database rename file '/u01/app/oracle/oradata/FIRSTDB/datafile/o1_mf_undotbs1_bv4syyb1_.dbf' to '/u01/app/oracle/oradata/SECONDDB/datafile/o1_mf_undotbs1_bv4syyb1_.dbf';
alter database rename file '/u01/app/oracle/oradata/FIRSTDB/datafile/o1_mf_users_bv4szftk_.dbf' to '/u01/app/oracle/oradata/SECONDDB/datafile/o1_mf_users_bv4szftk_.dbf';
alter database rename file '/u01/app/oracle/oradata/FIRSTDB/datafile/o1_mf_audsys_bvk27sx8_.dbf' to '/u01/app/oracle/oradata/SECONDDB/datafile/o1_mf_audsys_bvk27sx8_.dbf';
alter database rename file '/u01/app/oracle/oradata/FIRSTDB/1C144561FEAC44F9E0530C34EB0AA6ED/datafile/o1_mf_system_bvmq49q6_.dbf' to '/u01/app/oracle/oradata/SECONDDB/1C144561FEAC44F9E0530C34EB0AA6ED/datafile/o1_mf_system_bvmq49q6_.dbf';
alter database rename file '/u01/app/oracle/oradata/FIRSTDB/1C144561FEAC44F9E0530C34EB0AA6ED/datafile/o1_mf_sysaux_bvmq49qg_.dbf' to '/u01/app/oracle/oradata/SECONDDB/1C144561FEAC44F9E0530C34EB0AA6ED/datafile/o1_mf_sysaux_bvmq49qg_.dbf';
alter database rename file '/u01/app/oracle/oradata/FIRSTDB/1C144561FEAC44F9E0530C34EB0AA6ED/datafile/o1_mf_rman_bvmq4dp7_.dbf' to '/u01/app/oracle/oradata/SECONDDB/1C144561FEAC44F9E0530C34EB0AA6ED/datafile/o1_mf_rman_bvmq4dp7_.dbf';

Check if the change has been done as expected

select name from v$datafile;
NAME
------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/SECONDDB/datafile/o1_mf_system_bv4syqj4_.dbf
/u01/app/oracle/oradata/SECONDDB/1BA280FFFB1D9736E0530C34EB0ADA9B/datafile/o1_mf_system_bv4syryt_.dbf
/u01/app/oracle/oradata/SECONDDB/datafile/o1_mf_sysaux_bv4syw4j_.dbf
/u01/app/oracle/oradata/SECONDDB/1BA280FFFB1D9736E0530C34EB0ADA9B/datafile/o1_mf_sysaux_bv4syx3c_.dbf
/u01/app/oracle/oradata/SECONDDB/datafile/o1_mf_undotbs1_bv4syyb1_.dbf
/u01/app/oracle/oradata/SECONDDB/datafile/o1_mf_users_bv4szftk_.dbf
/u01/app/oracle/oradata/SECONDDB/datafile/o1_mf_audsys_bvk27sx8_.dbf
/u01/app/oracle/oradata/SECONDDB/1C144561FEAC44F9E0530C34EB0AA6ED/datafile/o1_mf_system_bvmq49q6_.dbf
/u01/app/oracle/oradata/SECONDDB/1C144561FEAC44F9E0530C34EB0AA6ED/datafile/o1_mf_sysaux_bvmq49qg_.dbf
/u01/app/oracle/oradata/SECONDDB/1C144561FEAC44F9E0530C34EB0AA6ED/datafile/o1_mf_rman_bvmq4dp7_.dbf

7. Change redologs’ paths

select member from v$logfile;
MEMBER
------------------------------------------------------------------------------------------------/u01/app/oracle/oradata/FIRSTDB/onlinelog/o1_mf_1_bv4sypo1_.log
/u01/app/oracle/fast_recovery_area/FIRSTDB/onlinelog/o1_mf_1_bv4sypsy_.log
/u01/app/oracle/oradata/FIRSTDB/onlinelog/o1_mf_2_bv4sypvx_.log
/u01/app/oracle/fast_recovery_area/FIRSTDB/onlinelog/o1_mf_2_bv4syq0o_.log
/u01/app/oracle/oradata/FIRSTDB/onlinelog/o1_mf_3_bv4syq2j_.log
/u01/app/oracle/fast_recovery_area/FIRSTDB/onlinelog/o1_mf_3_bv4syq6n_.log

Rename redologs using new path

alter database rename file '/u01/app/oracle/oradata/FIRSTDB/onlinelog/o1_mf_1_bv4sypo1_.log' to '/u01/app/oracle/oradata/SECONDDB/onlinelog/o1_mf_1_bv4sypo1_.log';
alter database rename file '/u01/app/oracle/fast_recovery_area/FIRSTDB/onlinelog/o1_mf_1_bv4sypsy_.log' to '/u01/app/oracle/fast_recovery_area/SECONDDB/onlinelog/o1_mf_1_bv4sypsy_.log';
alter database rename file '/u01/app/oracle/oradata/FIRSTDB/onlinelog/o1_mf_2_bv4sypvx_.log' to '/u01/app/oracle/oradata/SECONDDB/onlinelog/o1_mf_2_bv4sypvx_.log';
alter database rename file '/u01/app/oracle/fast_recovery_area/FIRSTDB/onlinelog/o1_mf_2_bv4syq0o_.log' to '/u01/app/oracle/fast_recovery_area/SECONDDB/onlinelog/o1_mf_2_bv4syq0o_.log';
alter database rename file '/u01/app/oracle/oradata/FIRSTDB/onlinelog/o1_mf_3_bv4syq2j_.log' to '/u01/app/oracle/oradata/SECONDDB/onlinelog/o1_mf_3_bv4syq2j_.log';
alter database rename file '/u01/app/oracle/fast_recovery_area/FIRSTDB/onlinelog/o1_mf_3_bv4syq6n_.log' to '/u01/app/oracle/fast_recovery_area/SECONDDB/onlinelog/o1_mf_3_bv4syq6n_.log';

Verify the change:

select member from v$logfile;
MEMBER
------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/SECONDDB/onlinelog/o1_mf_1_bv4sypo1_.log
/u01/app/oracle/fast_recovery_area/SECONDDB/onlinelog/o1_mf_1_bv4sypsy_.log
/u01/app/oracle/oradata/SECONDDB/onlinelog/o1_mf_2_bv4sypvx_.log
/u01/app/oracle/fast_recovery_area/SECONDDB/onlinelog/o1_mf_2_bv4syq0o_.log
/u01/app/oracle/oradata/SECONDDB/onlinelog/o1_mf_3_bv4syq2j_.log
/u01/app/oracle/fast_recovery_area/SECONDDB/onlinelog/o1_mf_3_bv4syq6n_.log

8. Change tempfiles’ paths

Check tempfiles before change

select name from v$tempfile;
NAME
------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/FIRSTDB/datafile/o1_mf_temp_bv4syysn_.tmp
/u01/app/oracle/oradata/FIRSTDB/1BA280FFFB1D9736E0530C34EB0ADA9B/datafile/o1_mf_temp_bv4syyt7_.tmp
/u01/app/oracle/oradata/FIRSTDB/1C144561FEAC44F9E0530C34EB0AA6ED/datafile/o1_mf_temp_bvmq49qg_.dbf
alter database rename file '/u01/app/oracle/oradata/FIRSTDB/datafile/o1_mf_temp_bv4syysn_.tmp' to '/u01/app/oracle/oradata/SECONDDB/datafile/o1_mf_temp_bv4syysn_.tmp';
Database changed.
alter database rename file '/u01/app/oracle/oradata/FIRSTDB/1BA280FFFB1D9736E0530C34EB0ADA9B/datafile/o1_mf_temp_bv4syyt7_.tmp' to '/u01/app/oracle/oradata/SECONDDB/1BA280FFFB1D9736E0530C34EB0ADA9B/datafile/o1_mf_temp_bv4syyt7_.tmp';
Database changed.
alter database rename file '/u01/app/oracle/oradata/FIRSTDB/1C144561FEAC44F9E0530C34EB0AA6ED/datafile/o1_mf_temp_bvmq49qg_.dbf' to '/u01/app/oracle/oradata/SECONDDB/1C144561FEAC44F9E0530C34EB0AA6ED/datafile/o1_mf_temp_bvmq49qg_.dbf';
Database changed.

Verify after the change:

select name from v$tempfile;
NAME
------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/SECONDDB/datafile/o1_mf_temp_bv4syysn_.tmp
/u01/app/oracle/oradata/SECONDDB/1BA280FFFB1D9736E0530C34EB0ADA9B/datafile/o1_mf_temp_bv4syyt7_.tmp
/u01/app/oracle/oradata/SECONDDB/1C144561FEAC44F9E0530C34EB0AA6ED/datafile/o1_mf_temp_bvmq49qg_.dbf

9. Open database

ALTER DATABASE OPEN;

Database changed.

That’s all folks!! Pretty simple isn’t it :)

About the author

 
maciej tokar
Maciej Tokar

An Oracle technology geek and crazy long distance runner, DBA24 Owner
Senior Oracle DBA / Consultant / [OCP10g, OCP12c, OCE RAC 10g] / [experience: 9y+]
Currently working for Bluegarden (Oslo Norway) by Miratech Group
Past: Mastercard / Trevica by Britenet, Citi International PLC, PZU

 
View Maciej Tokar's profile on LinkedIn         logoDB4
LinkedIn Auto Publish Powered By : XYZScripts.com