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