In previous post, we have talked about how to change DB_UNIQUE_NAME of a single-instance database. In this post, we will talk about how to change both DB_NAME and DB_UNIQUE_NAME of a single-instance database. For a RAC database, you should go for: How to Change Both DB_NAME and DB_UNIQUE_NAME of a RAC Database.
First of all, let's see the current status.
SQL> select dbid, name, open_mode from v$database;
DBID NAME OPEN_MODE
---------- --------- --------------------
3411734329 ORCLCDB READ WRITE
In this case, we would like to change DB_NAME and DB_UNIQUE_NAME from ORCLCDB into TESTCDB.
We restart the database to MOUNT state.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
...
Database mounted.
SQL> exit
Use DBNEWID utility nid to change the database name on all files.
[oracle@test ~]$ nid target=sys/password dbname=TESTCDB
DBNEWID: Release 19.0.0.0.0 - Production on Mon Dec 22 19:18:06 2020
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to database ORCLCDB (DBID=3411734329)
Connected to server version 19.9.0
Control Files in database:
/u01/app/oracle/oradata/ORCLCDB/control01.ctl
/u01/app/oracle/fast_recovery_area/ORCLCDB/control02.ctl
Change database ID and database name ORCLCDB to TESTCDB? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 3411734329 to 2800509118
Changing database name from ORCLCDB to TESTCDB
Control File /u01/app/oracle/oradata/ORCLCDB/control01.ctl - modified
Control File /u01/app/oracle/fast_recovery_area/ORCLCDB/control02.ctl - modified
Datafile /u01/app/oracle/oradata/ORCLCDB/system01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/ORCLCDB/sysaux01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/ORCLCDB/undotbs01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/ORCLCDB/pdbseed/system01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/ORCLCDB/pdbseed/sysaux01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/ORCLCDB/users01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/ORCLCDB/pdbseed/undotbs01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/system01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/users01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/example01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/ORCLCDB/temp01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/ORCLCDB/pdbseed/temp012020-07-14_23-10-41-107-PM.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/temp01.db - dbid changed, wrote new name
Control File /u01/app/oracle/oradata/ORCLCDB/control01.ctl - dbid changed, wrote new name
Control File /u01/app/oracle/fast_recovery_area/ORCLCDB/control02.ctl - dbid changed, wrote new name
Instance shut down
Database name changed to TESTCDB.
Modify parameter file and generate a new password file before restarting.
Database ID for database TESTCDB changed to 2800509118.
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.
If you don't want a new DBID, you can set SETNAME=YES to prevent DBID from changing. For example:
[oracle@test ~]$ nid target=sys/password dbname=TESTCDB setname=YES
Next, we create PFILE for later modification.
[oracle@test ~]$ sqlplus / as sysdba
...
Connected to an idle instance.
SQL> create pfile='/home/oracle/pfile' from spfile;
File created.
SQL> exit
We change DB_NAME from ORCLCDB to TESTCDB in the parameter file.
[oracle@test ~]$ vi /home/oracle/pfile
...
*.db_name='TESTCDB' # Changed by Scott
...
Here we don't add parameter DB_UNIQUE_NAME to the file, this is because DB_UNIQUE_NAME will follow DB_NAME if we don't specify DB_UNIQUE_NAME at startup time.
Modify /etc/oratab.
[oracle@ora19c1 ~]$ vi /etc/oratab
...
#ORCLCDB:/u01/app/oracle/product/19.3.0/dbhome_1:Y
TESTCDB:/u01/app/oracle/product/19.3.0/dbhome_1:Y
Change ORACLE_SID.
[oracle@ora19c1 ~]$ export ORACLE_SID=TESTCDB
[oracle@ora19c1 ~]$ echo $ORACLE_SID
TESTCDB
Create a new SPFILE for startup.
[oracle@test ~]$ sqlplus / as sysdba
...
Connected to an idle instance.
SQL> create spfile from pfile='/home/oracle/pfile';
File created.
We are going to startup the database, but the only different is that we should open the database with RESETLOGS.
SQL> startup mount;
ORACLE instance started.
...
Database mounted.
SQL> alter database open resetlogs;
Database altered.
Please note that, if you have not changed DBID by specifying SETNAME=YES in nid command, then you don't have to open the database with RESETLOGS.
Let's check the final result.
SQL> select dbid, name, open_mode from v$database;
DBID NAME OPEN_MODE
---------- --------- --------------------
2800509118 TESTCDB READ WRITE
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cdb_cluster_name string
cell_offloadgroup_name string
db_file_name_convert string
db_name string TESTCDB
db_unique_name string TESTCDB
global_names boolean FALSE
instance_name string TESTCDB
lock_name_space string
log_file_name_convert string
pdb_file_name_convert string
processor_group_name string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string TESTCDB
Also, we noticed that the new SPFILE has been used.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/19.3.0
/dbhome_1/dbs/spfileTESTCDB.or
a
We did it!
Next, we will talked about how to Change DBID of a RAC database.