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
- Remove Cluster options
- DBNEWID Utility Execution
- Complete DBNAME and DB_UNIQUE_NAME change
- Add Database to Clusterware
- Move Controlfile and Datafiles
- Cleaning up
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)
)
)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 ~]$ 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>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 ~]$ 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 ~]$ 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)
)
)
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
----------
4146569967We 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 ~]$ 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>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 ~]$ 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 ~]$ 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>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>
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>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> 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>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>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.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>
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>
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>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 ~]$ 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] > 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]$ 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] > 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
Comments
Awesome Victor.. You are the saint of Oracle DBA who has tremendous knowledge 😉
Thanks!!