<<Back to DB Administration Main Page
There are situations when the need arise to change the database name. The requirement could be one of the following
- Change the DBID and Database Name (DB_NAME) both
- Change Only the Database ID
- Change Only the Database Name
The goal of this post is demonstrate you how to change only the database name without affecting the DBID of the database.
Changing Only the Database Name of Oracle Database
Background Info: I want to change the TEST1D database to ORCL1D
Old Database Name: TEST1D
Changed Database Name: ORCL1D
Old Database Name: TEST1D
Changed Database Name: ORCL1D
Step0: Ensure the application is down and no user is connected to database
SQL> select distinct username, count(1) from v$session where username not in ('SYS','SYSTEM','DBSNMP') group by username
no rows selected
Step1: Prepare for full database backup
SQL> alter system checkpoint;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
Step2: Perform full database and archivelog backup
$rman target / catalog rman@catalog
RMAN> backup database plus archivelog all delete input;
Step3: Source DB environment variables
$export ORACLE_HOME=<Path of the Oracle Home>
$export PATH=$ORACLE_HOME/bin:$PATH
Step4: Clean Mount the database
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 3221222272 bytes
Fixed Size 8901504 bytes
Variable Size 838860800 bytes
Database Buffers 2365587456 bytes
Redo Buffers 7872512 bytes
Database mounted.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
TEST1D MOUNTED PRIMARY
Step6: Use NID to Change the database name
$nid TARGET=SYS DBNAME=ORCL1D SETNAME=YES
Step7: Update DB_NAME parameter in pfile/spfile
$sqlplus "/as sysdba"
Connected to an idle instance.
SQL> create pfile='/u01/product/18c/dbd1/dbs/initORCL1D.ora' from spfile;
File created.
SQL> exit
Disconnected
Edit the file /u01/product/18c/dbd1/dbs/initORCL1D.ora in vi and update the parameter DB_NAME
save and quite
Step8: Create New password file
$orapwd file=/u01/product/18c/dbd1/dbs/orapwORCL1D entries=5 sys=y
Enter password for SYS:
ls -lrt /u01/product/18c/dbd1/dbs/orapwORCL1D
-rw-r----- 1 oracle dba 6144 Aug 9 14:50 /u01/product/18c/dbd1/dbs/orapwORCL1D
Step9: Start the database with updated Pfile
$export ORACLE_SID=ORCL1D
$sqlplus "/as sysdba"
Connected to an idle instance.
SQL> startup pfile='/u01/product/18c/dbd1/dbs/initORCL1D.ora';
ORACLE instance started.
Total System Global Area 3221222272 bytes
Fixed Size 8901504 bytes
Variable Size 838860800 bytes
Database Buffers 2365587456 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
Step10: Create spfile and start the database from spfile
SQL> create spfile from pfile;
File created.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup;
ORACLE instance started.
Total System Global Area 3221222272 bytes
Fixed Size 8901504 bytes
Variable Size 838860800 bytes
Database Buffers 2365587456 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /ora_app/product/18c/dbd1/dbs/
spfileORCL1D.ora
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
ORCL1D READ WRITE PRIMARY
Step11: Optionally Register the database in RMAN catalog
$rman target / catalog rman@catalog
RMAN> register database;
Step12: Optionally remove the unwanted files related to old name from $ORACLE_HOME/dbs
Step12: Optionally remove the unwanted files related to old name from $ORACLE_HOME/dbs
$rm hc_TEST1D.dat snapcf_TEST1D.f orapwTEST1D lkTEST1D
Step13: Perform Full Database backup
$rman target / catalog rman@catalog
Step14: Update /etc/oratab for New DB Name
open /etc/oratab using vi and replace the Old DB Name (TEST1D) with New DB Name (ORCL1D)
Step15: Optionally Rename the datafile, controlfile, redolog files etc
SQL> alter database move datafile '/u01_data1/TEST1D/dbfile/pdbseed/TEST1D/SYSTEM_01.DBF' to '/u01_data1/ORCL1D/dbfile/pdbseed/ORCL1D/SYSTEM_01.DBF';
- datafiles you can rename using alter database move datafile command as shown below
SQL> alter database move datafile '/u01_data1/TEST1D/dbfile/pdbseed/TEST1D/SYSTEM_01.DBF' to '/u01_data1/ORCL1D/dbfile/pdbseed/ORCL1D/SYSTEM_01.DBF';
- For controlfiles
SQL>select name from v$controlfile;
Update the control_files parameter in spfile
SQL> ALTER SYSTEM SET control_files= <new controlfile location>/control01.ctl scope=spfile;
Shut down the database
SQL> shut immediate
Move the controlfile from Old location to New location using mv command
start the database
SQL> startup
- Renaming Online Redo Log files
To rename the online redo logfiles just add the new online redo log group in new location and drop the old one.
Comments
Post a Comment