DEV Community

Cover image for Change Database name using NID (DBNEWID utility)
Project-42
Project-42

Posted on

Change Database name using NID (DBNEWID utility)

I got inspired by this post Do you NID to rename an Oracle DB on ASM? by Rodrigo Mufalani to try DBNEWID Utility and rename some Databases at home.

These are the different actions I will perform:

System Details

This is the System I want to change the name to:

[oracle@rac1-node1 ~]$ srvctl  config database -d wrong19
Database unique name: wrong19
Database name: wrong19
Oracle home: /u01/app/oracle/product/19.3.0/dbhome_1
Oracle user: oracle
Spfile: 
Password file: +DATA/WRONG19/PASSWORD/pwdwrong19.381.1070281297AC
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: 
Disk Groups: DATA
Mount point paths: 
Services: 
Type: RAC
Start concurrency: 
Stop concurrency: 
OSDBA group: oinstall
OSOPER group: oinstall
Database instances: wrong191,wrong192
Configured nodes: rac1-node1,rac1-node2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services: 
Database is administrator managed
[oracle@rac1-node1 ~]$ 


DB_NAME   DB_UNIQUE_NAME     INSTANCE_NAME        CDB HOST_NAME           STARTUP              DATABASE_ROLE  OPEN_MODE      STATUS
--------- ------------------------------ -------------------- --- ----------------------------------- ---------------------------------------- ---------------- -------------------- ------------
WRONG19   wrong19      wrong192       YES rac1-node2.raclab.local       19-APR-2021 12:59:55           PRIMARY    READ WRITE       OPEN
WRONG19   wrong19      wrong191       YES rac1-node1.raclab.local       19-APR-2021 12:59:53           PRIMARY    READ WRITE       OPEN

   INST_ID     CON_ID NAME       OPEN_MODE  OPEN_TIME              STATUS
---------- ---------- -------------------- ---------- ---------------------------------------- ----------
   1      2 PDB$SEED       READ ONLY  19-APR-21 01.00.23.804 PM +01:00         NORMAL
   2      2 PDB$SEED       READ ONLY  19-APR-21 01.00.24.742 PM +01:00         NORMAL
   1      3 WRONGPDB       READ WRITE 19-APR-21 01.01.12.770 PM +01:00         NORMAL
   2      3 WRONGPDB       READ WRITE 19-APR-21 01.01.23.094 PM +01:00         NORMAL

wrong191 - SQL>select DBID from v$database;

      DBID
---------------
3626868850


[oracle@rac1-node1 ~]$ cat /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

WRONG19 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = wrong19)
    )
  )
Enter fullscreen mode Exit fullscreen mode

Remove Cluster options

To get DBNEWID to work, we need to disable the Cluster options of the system and start it as mounted.
We will create a pfile (wrong19_nocluster.ora) and comment any cluster or Instance2 reference in order to start the system as Mounted

wrong191 - SQL>create pfile='/tmp/wrong19_nocluster.ora' from spfile;

File created.

wrong191 - SQL>


[oracle@rac1-node1 ~]$ egrep -i 'db_name|unique|wrong192|cluster' /tmp/wrong19_nocluster.ora 
#*.cluster_database=true
*.db_name='wrong19'
#wrong192.instance_number=2
#wrong192.thread=2
#wrong192.undo_tablespace='UNDOTBS2'
[oracle@rac1-node1 ~]$ 
Enter fullscreen mode Exit fullscreen mode

Let's now restart the system using that pfile we have created

[oracle@rac1-node1 ~]$ srvctl stop database -d wrong19
[oracle@rac1-node1 ~]$ sqlplus / as sysdba

Connected to an idle instance.

wrong191 - SQL>startup mount pfile='/tmp/wrong19_nocluster.ora'
ORACLE instance started.

Total System Global Area 5804914312 bytes
Fixed Size        9148040 bytes
Variable Size    1056964608 bytes
Database Buffers   4731174912 bytes
Redo Buffers        7626752 bytes
Database mounted.
wrong191 - SQL>show parameter cluster

NAME             TYPE  VALUE
----------------------------------------- ----------- ------------------------------
cdb_cluster          boolean   FALSE
cdb_cluster_name         string
cluster_database         boolean   FALSE
cluster_database_instances       integer   1
cluster_interconnects        string
wrong191 - SQL>
Enter fullscreen mode Exit fullscreen mode

DBNEWID Utility Execution

Once we have the system with no cluster option and Mounted, we can execute the DBNEWID Utility.

In this example, I will use DBNEWID to change DBNAME and a new DBID will be also generated.
This is an important detail since we will need to open the Database using RESETLOGS and any previous Database Backup will be useless.

If you just want to change the DBNAME and keep same DBID, you can use "SETNAME=YES"
nid / DBNAME=NEW_NAME SETNAME=YES

For more details I recommend to have a look to DBNEWID Utility Documentation as well

The command itself will take just few seconds per Datafile

[oracle@rac1-node1 ~]$ nid target=/ dbname=right19 

DBNEWID: Release 19.0.0.0.0 - Production on Mon Apr 19 13:14:22 2021

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

Connected to database WRONG19 (DBID=3626868850)

Connected to server version 19.7.0

Control Files in database:
    +DATA/WRONG19/CONTROLFILE/current.373.1070281523

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

Proceeding with operation
Changing database ID from 3626868850 to 4146569967
Changing database name from WRONG19 to RIGHT19
    Control File +DATA/WRONG19/CONTROLFILE/current.373.1070281523 - modified
    Datafile +DATA/WRONG19/DATAFILE/system.408.107028132 - dbid changed, wrote new name
    Datafile +DATA/WRONG19/DATAFILE/sysaux.406.107028140 - dbid changed, wrote new name
    Datafile +DATA/WRONG19/DATAFILE/undotbs1.400.107028145 - dbid changed, wrote new name
    Datafile +DATA/WRONG19/86B637B62FE07A65E053F706E80A27CA/DATAFILE/system.297.107028232 - dbid changed, wrote new name
    Datafile +DATA/WRONG19/86B637B62FE07A65E053F706E80A27CA/DATAFILE/sysaux.382.107028232 - dbid changed, wrote new name
    Datafile +DATA/WRONG19/DATAFILE/users.345.107028145 - dbid changed, wrote new name
    Datafile +DATA/WRONG19/86B637B62FE07A65E053F706E80A27CA/DATAFILE/undotbs1.294.107028232 - dbid changed, wrote new name
    Datafile +DATA/WRONG19/DATAFILE/undotbs2.392.107028271 - dbid changed, wrote new name
    Datafile +DATA/WRONG19/C05313C53D985AE0E0530B01A8C0CBAE/DATAFILE/system.389.107028363 - dbid changed, wrote new name
    Datafile +DATA/WRONG19/C05313C53D985AE0E0530B01A8C0CBAE/DATAFILE/sysaux.404.107028363 - dbid changed, wrote new name
    Datafile +DATA/WRONG19/C05313C53D985AE0E0530B01A8C0CBAE/DATAFILE/undotbs1.379.107028363 - dbid changed, wrote new name
    Datafile +DATA/WRONG19/C05313C53D985AE0E0530B01A8C0CBAE/DATAFILE/undo_2.292.107028368 - dbid changed, wrote new name
    Datafile +DATA/WRONG19/C05313C53D985AE0E0530B01A8C0CBAE/DATAFILE/users.290.107028369 - dbid changed, wrote new name
    Datafile +DATA/WRONG19/TEMPFILE/temp.403.107028154 - dbid changed, wrote new name
    Datafile +DATA/WRONG19/C052C5A234A446E6E0530B01A8C0C312/TEMPFILE/temp.310.107028236 - dbid changed, wrote new name
    Datafile +DATA/WRONG19/C05313C53D985AE0E0530B01A8C0CBAE/TEMPFILE/temp.380.107028366 - dbid changed, wrote new name
    Control File +DATA/WRONG19/CONTROLFILE/current.373.1070281523 - dbid changed, wrote new name
    Instance shut down

Database name changed to RIGHT19.
Modify parameter file and generate a new password file before restarting.
Database ID for database RIGHT19 changed to 4146569967.
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.

[oracle@rac1-node1 ~]$ 
Enter fullscreen mode Exit fullscreen mode

Complete DBNAME and DB_UNIQUE_NAME change

Before we start the system and open it using RESETLOGS, we will create a new pfile (right19.ora) with the new DBNAME and adding back the Cluster option we commented earlier.
Additionally, we can change the DB_UNIQUE_NAME and audit_file_dest (remember to create the new folder destination for every node)

[oracle@rac1-node1 ~]$ cp -pr /tmp/wrong19_nocluster.ora /tmp/right19.ora
[oracle@rac1-node1 ~]$ nano /tmp/right19.ora

[oracle@rac1-node1 ~]$ egrep -i 'db_name|unique|wrong|right|cluster' /tmp/right19.ora 
*.audit_file_dest='/u01/app/oracle/admin/right19/adump'
*.cluster_database=true
*.control_files='+DATA/WRONG19/CONTROLFILE/current.373.1070281523'
*.db_name='right19'
*.db_unique_name='rac1_right19'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=right19XDB)'
right192.instance_number=2
right191.instance_number=1
right192.thread=2
right191.thread=1
right191.undo_tablespace='UNDOTBS1'
right192.undo_tablespace='UNDOTBS2'
[oracle@rac1-node1 ~]$ 

[oracle@rac1-node1 ~]$ mkdir -p /u01/app/oracle/admin/right19/adump
[oracle@rac1-node1 ~]$ 
Enter fullscreen mode Exit fullscreen mode

I recommend to modify oratab and tnsnames.ora files with the new names (remember to do it for every node)

[oracle@rac1-node1 ~]$ grep right /etc/oratab
right191:/u01/app/oracle/product/19.3.0/dbhome_1:N
[oracle@rac1-node1 ~]$ 

[oracle@rac1-node1 ~]$ cat /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

[....]

RIGHT19 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = right19)
    )
  )

Enter fullscreen mode Exit fullscreen mode

Restart now the system with the new pfile (right19.ora) and open it using RESETLOGS

[oracle@rac1-node1 ~]$ . oraenv
ORACLE_SID = [wrong191] ? right191
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac1-node1 ~]$ 


[oracle@rac1-node1 ~]$ sqlplus / as sysdba

Connected to an idle instance.

right191 - SQL>startup mount pfile='/tmp/right19.ora';
ORACLE instance started.

Total System Global Area 5804914312 bytes
Fixed Size        9148040 bytes
Variable Size    1107296256 bytes
Database Buffers   4680843264 bytes
Redo Buffers        7626752 bytes
Database mounted.
right191 - SQL>alter database open RESETLOGS;

Database altered.

right191 - SQL>

DB_NAME   DB_UNIQUE_NAME     INSTANCE_NAME        CDB HOST_NAME           STARTUP              DATABASE_ROLE  OPEN_MODE      STATUS
-------------- ------------------------------ -------------------- --- ----------------------------------- ---------------------------------------- ---------------- -------------------- ------------
RIGHT19   rac1_right19       right191       YES rac1-node1.raclab.local       19-APR-2021 13:32:07           PRIMARY    READ WRITE       OPEN


   INST_ID     CON_ID NAME       OPEN_MODE  OPEN_TIME              STATUS
--------------- ---------- -------------------- ---------- ---------------------------------------- ----------
   1      2 PDB$SEED       READ ONLY  19-APR-21 01.32.44.701 PM +01:00         NORMAL
   1      3 WRONGPDB       MOUNTED    19-APR-21 01.32.49.459 PM +01:00         NORMAL

right191 - SQL>select DBID from v$database;

      DBID
---------------
4146569967
Enter fullscreen mode Exit fullscreen mode

We also need to create a new password file as mentioned in DBNEWID output

[oracle@rac1-node1 ~]$ orapwd file='+DATA/RAC1_RIGHT19/PASSWORD/pwdRIGHT19' dbuniquename='rac1_right19'

Enter password for SYS: 
[oracle@rac1-node1 ~]$ 
Enter fullscreen mode Exit fullscreen mode

Add Database to Clusterware

Now that we have the system with the DBNAME and DB_UNIQUE_NAME changed, lets add it back to the Cluster
First, create a spfile using the current pfile, but rmemeber to use the new Location system created automatically in the Diskgroup (+DATA in our case):

ASMCMD [+data] > ls
ASM/
CDB19/
DB19/
NOCDB19/

RAC1_RIGHT19/ <<<<<<

TEST/
UPG19/
WRONG19/
orapwasm
orapwasm_backup
rac1-cluster/
ASMCMD [+data] >
ASMCMD [+data] > cd rac1_right19
ASMCMD [+data/rac1_right19] > ls
ONLINELOG/

right191 - SQL>create spfile='+DATA/RAC1_RIGHT19/PARAMETERFILE/spfileRIGHT19.ora' from pfile='/tmp/right19.ora';

File created.

right191 - SQL>
Enter fullscreen mode Exit fullscreen mode

Let's now add the Database to the Cluster:

[oracle@rac1-node1 ~]$ srvctl add database -db rac1_right19 -oraclehome /u01/app/oracle/product/19.3.0/dbhome_1 -spfile '+DATA/RAC1_RIGHT19/PARAMETERFILE/spfileRIGHT19.ora'
[oracle@rac1-node1 ~]$ srvctl modify database -d rac1_right19 -pwfile '+DATA/RAC1_RIGHT19/PASSWORD/pwdRIGHT19'
[oracle@rac1-node1 ~]$ srvctl add instance -d rac1_right19 -i right191 -n rac1-node1
[oracle@rac1-node1 ~]$ srvctl add instance -d rac1_right19 -i right192 -n rac1-node2
[oracle@rac1-node1 ~]$ 
[oracle@rac1-node1 ~]$ srvctl config database -d rac1_right19
Database unique name: rac1_right19
Database name: 
Oracle home: /u01/app/oracle/product/19.3.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/RAC1_RIGHT19/PARAMETERFILE/spfileRIGHT19.ora
Password file: +DATA/RAC1_RIGHT19/PASSWORD/pwdRIGHT19
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: 
Disk Groups: DATA
Mount point paths: 
Services: 
Type: RAC
Start concurrency: 
Stop concurrency: 
OSDBA group: oinstall
OSOPER group: oinstall
Database instances: right191,right192
Configured nodes: rac1-node1,rac1-node2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services: 
Database is administrator managed
[oracle@rac1-node1 ~]$ 
Enter fullscreen mode Exit fullscreen mode

We should also create a 'init.ora' file inside the "ORACLE_HOME/dbs" folder pointing to the current spfile, so we can start the system using sqlplus without issues (make sure we do the same for every node)

[oracle@rac1-node1 ~]$ echo "SPFILE='+DATA/RAC1_RIGHT19/PARAMETERFILE/spfileRIGHT19.ora'" > /u01/app/oracle/product/19.3.0/dbhome_1/dbs/initright191.ora
[oracle@rac1-node1 ~]$ scp -pr /u01/app/oracle/product/19.3.0/dbhome_1/dbs/initright191.ora rac1-node2:/u01/app/oracle/product/19.3.0/dbhome_1/dbs/initright192.ora
initright191.ora                                                                                                            100%   60    85.8KB/s   00:00    
[oracle@rac1-node1 ~]$ 
Enter fullscreen mode Exit fullscreen mode

Let's restart the Database now:

right191 - SQL>shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
right191 - SQL>exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0

[oracle@rac1-node1 ~]$ srvctl start database -d rac1_right19
[oracle@rac1-node1 ~]$ srvctl status database -d rac1_right19 -v
Instance right191 is running on node rac1-node1. Instance status: Open.
Instance right192 is running on node rac1-node2. Instance status: Open.
[oracle@rac1-node1 ~]$ 

DB_NAME   DB_UNIQUE_NAME     INSTANCE_NAME        CDB HOST_NAME           STARTUP              DATABASE_ROLE  OPEN_MODE      STATUS
-------------- ------------------------------ -------------------- --- ----------------------------------- ---------------------------------------- ---------------- -------------------- ------------
RIGHT19   rac1_right19       right192       YES rac1-node2.raclab.local       19-APR-2021 14:03:51           PRIMARY    READ WRITE       OPEN
RIGHT19   rac1_right19       right191       YES rac1-node1.raclab.local       19-APR-2021 14:03:46           PRIMARY    READ WRITE       OPEN

right191 - SQL>right191 - SQL>right191 - SQL>  2    3    4  

   INST_ID     CON_ID NAME       OPEN_MODE  OPEN_TIME              STATUS
--------------- ---------- -------------------- ---------- ---------------------------------------- ----------
   1      2 PDB$SEED       READ ONLY  19-APR-21 02.04.17.666 PM +01:00         NORMAL
   2      2 PDB$SEED       READ ONLY  19-APR-21 02.04.18.477 PM +01:00         NORMAL
   1      3 WRONGPDB       MOUNTED                 NORMAL
   2      3 WRONGPDB       MOUNTED                 NORMAL

right191 - SQL>
Enter fullscreen mode Exit fullscreen mode

Move Controlfile and Datafiles

We have now a system with new name, but all parameters and files are still located in the old folders, so lets move them to the right location.
A good start would be relocating crontrolfile to the new Diskgroup location:

right191 - SQL>show parameter control_files

NAME             TYPE  VALUE
----------------------------------------- ----------- ------------------------------
control_files          string  +DATA/WRONG19/CONTROLFILE/current.373.1070281523
right191 - SQL>

Enter fullscreen mode Exit fullscreen mode

Start changing the current location to the new Location. In this case, I also added a second control file

right191 - SQL>alter system set CONTROL_FILES='+DATA/RAC1_RIGHT19/CONTROLFILE/control01.ctl','+DATA/RAC1_RIGHT19/CONTROLFILE/control02.ctl' scope=spfile;

System altered.

right191 - SQL>
Enter fullscreen mode Exit fullscreen mode

We need to start the system as nomount from RMAN and "restore" the current controlfile so the system will create new control file in the new location:

[oracle@rac1-node1 ~]$ srvctl stop database -d rac1_right19
[oracle@rac1-node1 ~]$ 

[oracle@rac1-node1 ~]$ rman target /
connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area    5804914312 bytes

Fixed Size                     9148040 bytes
Variable Size               1107296256 bytes
Database Buffers            4680843264 bytes
Redo Buffers                   7626752 bytes

RMAN> restore controlfile from '+DATA/WRONG19/CONTROLFILE/current.373.1070281523';

Starting restore at 19-APR-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=9 instance=right191 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA/RAC1_RIGHT19/CONTROLFILE/control01.ctl
output file name=+DATA/RAC1_RIGHT19/CONTROLFILE/control02.ctl
Finished restore at 19-APR-21

RMAN> 
Enter fullscreen mode Exit fullscreen mode

We can now start the system to confirm all is fine

RMAN> shutdown immediate;

Oracle instance shut down

RMAN> 

[oracle@rac1-node1 ~]$ srvctl start database -d rac1_right19
[oracle@rac1-node1 ~]$ sqlplus / as sysdba

DB_NAME   DB_UNIQUE_NAME     INSTANCE_NAME        CDB HOST_NAME           STARTUP              DATABASE_ROLE  OPEN_MODE      STATUS
-------------- ------------------------------ -------------------- --- ----------------------------------- ---------------------------------------- ---------------- -------------------- ------------
RIGHT19   rac1_right19       right192       YES rac1-node2.raclab.local       19-APR-2021 14:25:27           PRIMARY    READ WRITE       OPEN
RIGHT19   rac1_right19       right191       YES rac1-node1.raclab.local       19-APR-2021 14:25:28           PRIMARY    READ WRITE       OPEN

right191 - SQL>
Enter fullscreen mode Exit fullscreen mode

We can now move the datafiles using the Move Online datafiles option we have available since 12c:

right191 - SQL>alter pluggable database wrongpdb open instances=all;

Pluggable database altered.

right191 - SQL>
set lines 500
set pages 50
col file_id for 99999
col File_name for a80

select file_id, tablespace_name, File_name
from cdb_data_files
order by 1 asc
/
FILE_ID TABLESPACE_NAME          FILE_NAME
------------ ------------------------------ --------------------------------------------------------------------------------
      1 SYSTEM             +DATA/WRONG19/DATAFILE/system.408.1070281327
      3 SYSAUX             +DATA/WRONG19/DATAFILE/sysaux.406.1070281409
      4 UNDOTBS1           +DATA/WRONG19/DATAFILE/undotbs1.400.1070281455
      7 USERS            +DATA/WRONG19/DATAFILE/users.345.1070281457
      9 UNDOTBS2           +DATA/WRONG19/DATAFILE/undotbs2.392.1070282719
     10 SYSTEM             +DATA/WRONG19/C05313C53D985AE0E0530B01A8C0CBAE/DATAFILE/system.389.1070283637
     11 SYSAUX             +DATA/WRONG19/C05313C53D985AE0E0530B01A8C0CBAE/DATAFILE/sysaux.404.1070283639
     12 UNDOTBS1           +DATA/WRONG19/C05313C53D985AE0E0530B01A8C0CBAE/DATAFILE/undotbs1.379.1070283637
     13 UNDO_2             +DATA/WRONG19/C05313C53D985AE0E0530B01A8C0CBAE/DATAFILE/undo_2.292.1070283687
     14 USERS            +DATA/WRONG19/C05313C53D985AE0E0530B01A8C0CBAE/DATAFILE/users.290.1070283697

10 rows selected.

right191 - SQL>alter database move datafile 1;
Database altered.

right191 - SQL>alter database move datafile 3;
Database altered.

right191 - SQL>alter database move datafile 4;
Database altered.

right191 - SQL>alter database move datafile 7;
Database altered.

right191 - SQL>alter database move datafile 9;
Database altered.

right191 - SQL>select file_id, tablespace_name, File_name
from cdb_data_files
order by 1 asc
/  2    3    4  

FILE_ID TABLESPACE_NAME          FILE_NAME
------------ ------------------------------ --------------------------------------------------------------------------------
      1 SYSTEM             +DATA/RAC1_RIGHT19/DATAFILE/system.386.1070289845
      3 SYSAUX             +DATA/RAC1_RIGHT19/DATAFILE/sysaux.408.1070289891
      4 UNDOTBS1           +DATA/RAC1_RIGHT19/DATAFILE/undotbs1.406.1070289925
      7 USERS            +DATA/RAC1_RIGHT19/DATAFILE/users.400.1070289961
      9 UNDOTBS2           +DATA/RAC1_RIGHT19/DATAFILE/undotbs2.345.1070289997
     10 SYSTEM             +DATA/WRONG19/C05313C53D985AE0E0530B01A8C0CBAE/DATAFILE/system.389.1070283637
     11 SYSAUX             +DATA/WRONG19/C05313C53D985AE0E0530B01A8C0CBAE/DATAFILE/sysaux.404.1070283639
     12 UNDOTBS1           +DATA/WRONG19/C05313C53D985AE0E0530B01A8C0CBAE/DATAFILE/undotbs1.379.1070283637
     13 UNDO_2             +DATA/WRONG19/C05313C53D985AE0E0530B01A8C0CBAE/DATAFILE/undo_2.292.1070283687
     14 USERS            +DATA/WRONG19/C05313C53D985AE0E0530B01A8C0CBAE/DATAFILE/users.290.1070283697

10 rows selected.

right191 - SQL>
Enter fullscreen mode Exit fullscreen mode

Remember to change to the PDB (in case you have any) to move the rest of the files

right191 - SQL>alter session set container=wrongpdb;
Session altered.

right191 - SQL>alter database move datafile 10;
Database altered.

right191 - SQL>alter database move datafile 11;
Database altered.

right191 - SQL>alter database move datafile 12;
Database altered.

right191 - SQL>alter database move datafile 13;
Database altered.

right191 - SQL>alter database move datafile 14;
Database altered.

right191 - SQL>alter session set container=CDB$ROOT;
Session altered.

right191 - SQL>select file_id, tablespace_name, File_name
from cdb_data_files
order by 1 asc
/  2    3    4  

FILE_ID TABLESPACE_NAME          FILE_NAME
------------ ------------------------------ -------------------------------------------------------------------------------------
      1 SYSTEM             +DATA/RAC1_RIGHT19/DATAFILE/system.386.1070289845
      3 SYSAUX             +DATA/RAC1_RIGHT19/DATAFILE/sysaux.408.1070289891
      4 UNDOTBS1           +DATA/RAC1_RIGHT19/DATAFILE/undotbs1.406.1070289925
      7 USERS            +DATA/RAC1_RIGHT19/DATAFILE/users.400.1070289961
      9 UNDOTBS2           +DATA/RAC1_RIGHT19/DATAFILE/undotbs2.345.1070289997
     10 SYSTEM             +DATA/RAC1_RIGHT19/C05313C53D985AE0E0530B01A8C0CBAE/DATAFILE/system.392.1070290129
     11 SYSAUX             +DATA/RAC1_RIGHT19/C05313C53D985AE0E0530B01A8C0CBAE/DATAFILE/sysaux.389.1070290143
     12 UNDOTBS1           +DATA/RAC1_RIGHT19/C05313C53D985AE0E0530B01A8C0CBAE/DATAFILE/undotbs1.404.1070290157
     13 UNDO_2             +DATA/RAC1_RIGHT19/C05313C53D985AE0E0530B01A8C0CBAE/DATAFILE/undo_2.379.1070290165
     14 USERS            +DATA/RAC1_RIGHT19/C05313C53D985AE0E0530B01A8C0CBAE/DATAFILE/users.292.1070290175

10 rows selected.
Enter fullscreen mode Exit fullscreen mode

For the TEMP files, we need to do it a bit differently though.
Remove the current temp file and create a new one (we are assuming you don't have sessions accessing the TEMP tablespace)

right191 - SQL>
set lines 500
set pages 50
col file_id for 99999
col File_name for a85
select file_id, tablespace_name, File_name
from cdb_temp_files
order by 1 asc
/
FILE_ID TABLESPACE_NAME          FILE_NAME
------------ ------------------------------ --------------------------------------------------------------------------------
      1 TEMP             +DATA/WRONG19/TEMPFILE/temp.403.1070281545
      3 TEMP             +DATA/WRONG19/C05313C53D985AE0E0530B01A8C0CBAE/TEMPFILE/temp.380.1070283669

right191 - SQL>alter database tempfile 1 drop including datafiles;
Database altered.

right191 - SQL>ALTER TABLESPACE TEMP ADD TEMPFILE SIZE 46137344 AUTOEXTEND ON;
Tablespace altered.

right191 - SQL>alter session set container=wrongpdb;
Session altered.

right191 - SQL>alter database tempfile 3 drop including datafiles;
Database altered.

right191 - SQL>ALTER TABLESPACE TEMP ADD TEMPFILE SIZE 46137344 AUTOEXTEND ON;
Tablespace altered.

right191 - SQL>alter session set container=CDB$ROOT;
Session altered.

right191 - SQL>set lines 500
set pages 50
col file_id for 99999
col File_name for a85
select file_id, tablespace_name, File_name
from cdb_temp_files
order by 1 asc
/right191 - SQL>right191 - SQL>right191 - SQL>right191 - SQL>  2    3    4  

FILE_ID TABLESPACE_NAME          FILE_NAME
------------ ------------------------------ -------------------------------------------------------------------------------------
      1 TEMP             +DATA/RAC1_RIGHT19/TEMPFILE/temp.403.1070290653
      3 TEMP             +DATA/RAC1_RIGHT19/C05313C53D985AE0E0530B01A8C0CBAE/TEMPFILE/temp.380.1070290685

right191 - SQL>

Enter fullscreen mode Exit fullscreen mode

Finally, remember to do the same for the PDB$SEED
In order to do that, we will need to alter our session to be able to access PDB$SEED and open in READ WRITE

right191 - SQL>ALTER SESSION SET CONTAINER = PDB$SEED;

Session altered.

right191 - SQL>show con_name;
CON_NAME
-----------------------------------
PDB$SEED

right191 - SQL>alter session set "_oracle_script" = true;
Session altered.

right191 - SQL>alter pluggable database pdb$seed close instances=all;
Pluggable database altered.

right191 - SQL>alter pluggable database pdb$seed open read write;
Pluggable database altered.

right191 - SQL>
set lines 500
set pages 50
col file_id for 99999
col File_name for a85

select file_id, tablespace_name, File_name
from cdb_data_files
order by 1 asc
/

select file_id, tablespace_name, File_name
from cdb_temp_files
order by 1 asc
/

FILE_ID TABLESPACE_NAME          FILE_NAME
------------ ------------------------------ -------------------------------------------------------------------------------------
      5 SYSTEM             +DATA/WRONG19/86B637B62FE07A65E053F706E80A27CA/DATAFILE/system.297.1070282327
      6 SYSAUX             +DATA/WRONG19/86B637B62FE07A65E053F706E80A27CA/DATAFILE/sysaux.382.1070282327
      8 UNDOTBS1           +DATA/WRONG19/86B637B62FE07A65E053F706E80A27CA/DATAFILE/undotbs1.294.1070282327

FILE_ID TABLESPACE_NAME          FILE_NAME
------------ ------------------------------ -------------------------------------------------------------------------------------
      2 TEMP             +DATA/WRONG19/C052C5A234A446E6E0530B01A8C0C312/TEMPFILE/temp.310.1070282367

right191 - SQL>

Enter fullscreen mode Exit fullscreen mode

The execution is the same we did earlier for CDB$ROOT and WRONGPDB.

right191 - SQL>alter database move datafile 5;
Database altered.

right191 - SQL>alter database move datafile 6;
Database altered.

right191 - SQL>alter database move datafile 8;
Database altered.

right191 - SQL>alter database tempfile 2 drop including datafiles;
Database altered.

right191 - SQL>ALTER TABLESPACE TEMP ADD TEMPFILE SIZE 46137344 AUTOEXTEND ON;
Tablespace altered.

right191 - SQL>
select file_id, tablespace_name, File_name
from cdb_data_files
order by 1 asc
/

select file_id, tablespace_name, File_name
from cdb_temp_files
order by 1 asc
/

FILE_ID TABLESPACE_NAME          FILE_NAME
------------ ------------------------------ -------------------------------------------------------------------------------------
      5 SYSTEM             +DATA/RAC1_RIGHT19/C052C5A234A446E6E0530B01A8C0C312/DATAFILE/system.383.1070291291
      6 SYSAUX             +DATA/RAC1_RIGHT19/C052C5A234A446E6E0530B01A8C0C312/DATAFILE/sysaux.297.1070291309
      8 UNDOTBS1           +DATA/RAC1_RIGHT19/C052C5A234A446E6E0530B01A8C0C312/DATAFILE/undotbs1.382.1070291327
     15 UNDO_2             +DATA/RAC1_RIGHT19/C052C5A234A446E6E0530B01A8C0C312/DATAFILE/undo_2.290.1070291011

right191 - SQL>right191 - SQL>  2    3    4  

FILE_ID TABLESPACE_NAME          FILE_NAME
------------ ------------------------------ -------------------------------------------------------------------------------------
      2 TEMP             +DATA/RAC1_RIGHT19/C052C5A234A446E6E0530B01A8C0C312/TEMPFILE/temp.310.1070291355

right191 - SQL>

right191 - SQL>alter pluggable database pdb$seed close instances=all;
Pluggable database altered.

right191 - SQL>alter pluggable database pdb$seed OPEN READ ONLY instances=all;
Pluggable database altered.

right191 - SQL>alter session set "_oracle_script"=FALSE;
Session altered.

right191 - SQL>alter session set container=CDB$ROOT;
Session altered.


DB_NAME   DB_UNIQUE_NAME     INSTANCE_NAME        CDB HOST_NAME           STARTUP              DATABASE_ROLE  OPEN_MODE      STATUS
-------------- ------------------------------ -------------------- --- ----------------------------------- ---------------------------------------- ---------------- -------------------- ------------
RIGHT19   rac1_right19       right192       YES rac1-node2.raclab.local       19-APR-2021 14:25:27           PRIMARY    READ WRITE       OPEN
RIGHT19   rac1_right19       right191       YES rac1-node1.raclab.local       19-APR-2021 14:25:28           PRIMARY    READ WRITE       OPEN

right191 - SQL>right191 - SQL>right191 - SQL>  2    3    4  

   INST_ID CON_ID NAME           OPEN_MODE  OPEN_TIME          STATUS
--------------- ------ -------------------- ---------- ---------------------------------------- ----------
   1  2 PDB$SEED         READ ONLY  19-APR-21 03.11.20.106 PM +01:00     NORMAL
   2  2 PDB$SEED         READ ONLY  19-APR-21 03.11.20.110 PM +01:00     NORMAL
   1  3 WRONGPDB         READ WRITE 19-APR-21 02.34.25.067 PM +01:00     NORMAL
   2  3 WRONGPDB         READ WRITE 19-APR-21 02.34.25.063 PM +01:00     NORMAL

right191 - SQL>
Enter fullscreen mode Exit fullscreen mode

Change PDB name

In some cases, you may want to also change the PDB name.
For that, we can use this Mike's Post about it

right191 - SQL>alter pluggable database wrongpdb close instances=all;
Pluggable database altered.

right191 - SQL>alter pluggable database wrongpdb open restricted;
Pluggable database altered.

right191 - SQL>alter session set container=wrongpdb;
Session altered.

right191 - SQL>alter pluggable database rename global_name to rightpdb;
Pluggable database altered.

right191 - SQL>alter pluggable database rightpdb close instances=all;
Pluggable database altered.

right191 - SQL>alter pluggable database rightpdb open instances=all;
Pluggable database altered.

right191 - SQL>show pdbs

    CON_ID CON_NAME       OPEN MODE  RESTRICTED
--------------- ------------------------------ ---------- ----------
   3 RIGHTPDB       READ WRITE NO


[oracle@rac1-node1 ~]$ lsnrctl status |grep -i pdb -A1
Service "rightpdb" has 1 instance(s).
  Instance "right191", status READY, has 1 handler(s) for this service...
[oracle@rac1-node1 ~]$ 
Enter fullscreen mode Exit fullscreen mode

Cleaning up

Now that all the files have been relocated and we don't need anything from the original location, we can delete anything left mentioning the old Database name
I didn't add anything on this post, but remember to modify your tnsnames.ora files in all nodes, just to avoid confusion

[oracle@rac1-node1 ~]$ srvctl config
cdb121
cdb122
cdb18
cdb19
db112
db121
db122
db18
db19
rac1_right19

wrong19 <<<<<<<<<<

ASMCMD [+data] > ls
ASM/
CDB19/
DB19/
NOCDB19/
RAC1_RIGHT19/
TEST/
UPG19/

WRONG19/ <<<<<<<<<<

orapwasm
orapwasm_backup
rac1-cluster/
ASMCMD [+data] > 
Enter fullscreen mode Exit fullscreen mode

We can use DBCA to delete the Database from the cluster and delete old audit and diagnostic locations

[oracle@rac1-node1 ~]$ dbca -silent  -deleteDatabase -sourceDB wrong19 -SysPassword Welcome1
[WARNING] [DBT-11503] The instance (wrong191) is not running on the local node. This may result in partial delete of Oracle database.
   CAUSE: A locally running instance is required for complete deletion of Oracle database instance and database files.
   ACTION: Specify a locally running database, or execute DBCA on a node where the database instance is running.
[WARNING] [DBT-19202] The Database Configuration Assistant will delete the Oracle instances and datafiles for your database. All information in the database will be destroyed.
Prepare for db operation
32% complete
Connecting to database
39% complete
42% complete
[WARNING] ORA-12528: TNS:listener: all appropriate instances are blocking new connections

45% complete
49% complete
52% complete
55% complete
58% complete
[WARNING] The data files for database with DB_NAME "wrong19" could not be determined because the database could not be started. DBCA will proceed with the database deletion.
ORA-12528: TNS:listener: all appropriate instances are blocking new connections

65% complete
Updating network configuration files
68% complete
Deleting instances and datafiles
77% complete
87% complete
97% complete
100% complete
Database deletion completed.
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/wrong19/wrong194.log" for further details.
[oracle@rac1-node1 ~]$ 

[oracle@rac1-node1 ~]$ srvctl config
cdb121
cdb122
cdb18
cdb19
db112
db121
db122
db18
db19
rac1_right19
[oracle@rac1-node1 ~]$ 

[oracle@rac1-node1 admin]$ ls -lrth /u01/app/oracle/admin/wrong19/
ls: cannot access /u01/app/oracle/admin/wrong19/: No such file or directory
[oracle@rac1-node1 admin]$ ls -lrth /u01/app/oracle/diag/rdbms/wrong19/
ls: cannot access /u01/app/oracle/diag/rdbms/wrong19/: No such file or directory
[oracle@rac1-node1 admin]$ 
Enter fullscreen mode Exit fullscreen mode

The only thing remaining would be to delete the old folder from ASM, which should only contain the old parameter, control and password files:

ASMCMD [+data] > ls WRONG19/
CONTROLFILE/
PARAMETERFILE/
PASSWORD/
ASMCMD [+data] > ls wrong19/controlfile
Current.373.1070281523
ASMCMD [+data] > ls wrong19/parameterfile
spfile.363.1070283487
ASMCMD [+data] > ls wrong19/password
pwdwrong19.381.1070281297
ASMCMD [+data] > 

ASMCMD [+data] > rm -r wrong19
You may delete multiple files and/or directories. 
Are you sure? (y/n) y
ASMCMD [+data] > 
Enter fullscreen mode Exit fullscreen mode

Hope this post helps you in case you are thinking of changing a Database name, and remember that every system will have some differences that you may need to review first

Top comments (0)