Do you NID to rename an Oracle DB on ASM?

Hello, my friends, it’s been a while since my last post. By the end of 2020, there were some very exciting days, with new projects and new challenges, and also I had some time off, dedicated to my family and to me. As 2021 is in the very beginning, I would like to wish you, reading this, a Happy new year, if I didn’t do yet, through my social media.

Well, let’s get into the subject of this post!!! Sometimes, we create a database, deliver it to the customer and people start to use it immediately and after some days or months of usage, they discover the database must be named in another way and request to change. This is a normal thing and that’s why Oracle provides the NID utility. The title of this post was not a typo.

The utilization of NID utility is pretty simple and straightforward, basically we will put the database on MOUNT MODE and issue the utility connecting to the database we intend to change the name and as a parameter a new name using DBNAME parameter. Just to get more familiar with NID utility you can issue the help as I issued below:

[oracle@oel82 ~]$ nid help=y




DBNEWID: Release 19.0.0.0.0 - Production on Wed Jan 20 20:13:31 2021




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




Keyword     Description                    (Default)

----------------------------------------------------

TARGET      Username/Password              (NONE)

DBNAME      New database name              (NONE)

LOGFILE     Output Log                     (NONE)

REVERT      Revert failed change           NO

SETNAME     Set a new database name only   NO

APPEND      Append to output log           NO

HELP        Displays these messages        NO

We gonna see in the future steps of this blogpost the utilization of NID and also how to perform this situation when your database is running on ASM. We can use two approaches if we created the database using DBCA and not created before the directories on ASM. Note: If the directory is created by system you cannot rename it!!!

I’ve checked for that on 19c documentation and still true. For those who already read this post Renaming directory on ASM. So, my advice is, create the directories on ASM manually with the name of the database as I will do later here to allows you to rename the directory, which would be the third approach for this current post. So, let’s do it for a case where we didn’t create the directory manually and DBCA flagged on ASM the directories as SYSTEM created. Like the picture below:

As you noticed, I have created a database named orcl and we will rename it to prdorcl (don’t judge me, I was running out of good names). My database name will be change from orcl to prdorcl.

As I mentioned before, the database must be on MOUNT mode to use NID

[oracle@oel82 ~]$ sqlplus "/as sysdba"




SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 20 20:26:25 2021

Version 19.8.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.




Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.8.0.0.0




SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup mount

ORACLE instance started.




Total System Global Area 2415917880 bytes

Fixed Size                  8899384 bytes

Variable Size             520093696 bytes

Database Buffers         1879048192 bytes

Redo Buffers                7876608 bytes

Database mounted.

SQL>

With the database mounted, we can apply the NID using this command line

[oracle@oel82 ~]$ nid target=/ dbname=prdorcl logfile=prdorcl.log







[oracle@oel82 ~]$ cat prdorcl.log




DBNEWID: Release 19.0.0.0.0 - Production on Wed Jan 20 20:29:57 2021




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




Connected to database ORCL (DBID=1590110414)




Connected to server version 19.8.0




Control Files in database:

    +DATA/ORCL/CONTROLFILE/current.261.1062308079

    +DATA/ORCL/CONTROLFILE/current.260.1062308079




Changing database ID from 1590110414 to 2783918790

Changing database name from ORCL to PRDORCL

    Control File +DATA/ORCL/CONTROLFILE/current.261.1062308079 - modified

    Control File +DATA/ORCL/CONTROLFILE/current.260.1062308079 - modified

    Datafile +DATA/ORCL/DATAFILE/system.256.106230789 - dbid changed, wrote new name

    Datafile +DATA/ORCL/DATAFILE/sysaux.257.106230794 - dbid changed, wrote new name

    Datafile +DATA/ORCL/DATAFILE/undotbs1.258.106230797 - dbid changed, wrote new name

    Datafile +DATA/ORCL/DATAFILE/users.259.106230798 - dbid changed, wrote new name

    Datafile +DATA/ORCL/TEMPFILE/temp.268.106230810 - dbid changed, wrote new name

    Control File +DATA/ORCL/CONTROLFILE/current.261.1062308079 - dbid changed, wrote new name

    Control File +DATA/ORCL/CONTROLFILE/current.260.1062308079 - dbid changed, wrote new name

    Instance shut down




Database name changed to PRDORCL.

Modify parameter file and generate a new password file before restarting.

Database ID for database PRDORCL changed to 2783918790.

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.

We saw the process was easy and quick to apply, don’t be afraid, but NID utility will shutdown your database and later on we will need to open using resetlogs.
Now as I haven’t created a pfile or a spfile with the new name I will fail to start the instance

[oracle@oel82 ~]$ export ORACLE_SID=prdorcl

[oracle@oel82 ~]$ sqlplus "/as sysdba"




SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 20 20:32:09 2021

Version 19.8.0.0.0




Copyright (c) 1982, 2020, Oracle.  All rights reserved.




Connected to an idle instance.




SQL> startup mount

ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/u01/app/oracle/product/19.8.0/db_1/dbs/initprdorcl.ora'

This orcl database was created using DBCA, so the spfile is stored on ASM by default

[oracle@oel82 dbs]$ srvctl config database -d orcl

Database unique name: orcl

Database name: orcl

Oracle home: /u01/app/oracle/product/19.8.0/db_1

Oracle user: oracle

Spfile: +DATA/ORCL/PARAMETERFILE/spfile.269.1062310177

Password file:

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Disk Groups: DATA

Services:

OSDBA group: oinstall

OSOPER group: oinstall

Database instance: orcl

We could identify the path of spfile from above command, so now I can create a pfile, edit it and start my new instance with name prdorcl.

[oracle@oel82 dbs]$ sqlplus "/as sysdba"




SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 20 20:34:22 2021

Version 19.8.0.0.0




Copyright (c) 1982, 2020, Oracle.  All rights reserved.




Connected to an idle instance.




SQL> create pfile='/u01/app/oracle/product/19.8.0/db_1/dbs/initprdorcl.ora' from spfile='+DATA/ORCL/PARAMETERFILE/spfile.269.1062310177';




File created.

So, just as reference, the original pfile looks like this… except because I removed dynamic memory parameter from the top of the file. There are some parameters do change, audit_file_dest, control_files, db_name and dispatchers. We will see the new file later on (wait for it).

*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'

*.audit_trail='db'

*.compatible='19.0.0'

*.control_files='+DATA/ORCL/CONTROLFILE/current.261.1062308079','+DATA/ORCL/CONTROLFILE/current.260.1062308079'

*.db_block_size=8192

*.db_create_file_dest='+DATA'

*.db_name='orcl'

*.db_recovery_file_dest='+DATA'

*.db_recovery_file_dest_size=8256m

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.local_listener='LISTENER_ORCL'

*.open_cursors=300

*.pga_aggregate_target=767m

*.processes=300

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=2301m

*.undo_tablespace='UNDOTBS1'

As I mentioned prior, in the beginning of the post. It’s strongly recommendable to create the path on ASM manually.

[oracle@oel82 dbs]$ . oraenv <<< +ASM

ORACLE_SID = [+ASM] ? The Oracle base remains unchanged with value /u01/app/oracle

[oracle@oel82 dbs]$




[oracle@oel82 dbs]$ asmcmd

ASMCMD> cd DATA

mkdir PRDORCL

mkdir PRDORCL/CONTROLFILE

mkdir PRDORCL/DATAFILE

mkdir PRDORCL/PARAMETERFILE

mkdir PRDORCL/ONLINELOG

mkdir PRDORCL/TEMPFILE

Now I have the new path created by me and not flagged as “SYSTEM”

It’s time to edit my new pfile to reflect the new database name and configurations

*.audit_file_dest='/u01/app/oracle/admin/prdorcl/adump'

*.audit_trail='db'

*.compatible='19.0.0'

*.control_files='+DATA/PRDORCL/CONTROLFILE/control01.ctl','+DATA/PRDORCL/CONTROLFILE/control02.ctl'

*.db_block_size=8192

*.db_create_file_dest='+DATA'

*.db_name='prdorcl'

*.db_recovery_file_dest='+DATA'

*.db_recovery_file_dest_size=8256m

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=prdorclXDB)'

*.local_listener='LISTENER_PRDORCL'

*.open_cursors=300

*.pga_aggregate_target=767m

*.processes=300

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=2301m

*.undo_tablespace='UNDOTBS1'

Create the folder to store audit files on OS

[oracle@oel82 dbs]$ mkdir -p /u01/app/oracle/admin/prdorcl/adump

Change your tnsnames to reflect the new names

from:




LISTENER_ORCL =

  (ADDRESS = (PROTOCOL = TCP)(HOST = oel82.fritz.box)(PORT = 1521))




ORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = oel82.fritz.box)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )




to:




LISTENER_PRDORCL =

  (ADDRESS = (PROTOCOL = TCP)(HOST = oel82.fritz.box)(PORT = 1521))







PRDORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = oel82.fritz.box)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = prdorcl)

    )

  )

Edit the /etc/oratab file to reflect the new name

+ASM:/u01/app/19.8.0/grid:N             # line added by Agent

prdorcl:/u01/app/oracle/product/19.8.0/db_1:N           # line added by Agent

I’m about to start the instance and restore the controlfiles to the destination I put on parameter controlfiles and then open the database with resetlogs.

SQL> show parameter control_files




NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

control_files                        string      +DATA/PRDORCL/CONTROLFILE/cont

                                                 rol01.ctl, +DATA/PRDORCL/CONTR

                                                 OLFILE/control02.ctl

Exit from sqlplus and login on RMAN to restore the controlfiles and open the database

[oracle@oel82 dbs]$ export ORACLE_SID=prdorcl

[oracle@oel82 dbs]$ rman target /




Recovery Manager: Release 19.0.0.0.0 - Production on Wed Jan 20 20:57:02 2021

Version 19.8.0.0.0




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




connected to target database (not started)




RMAN> startup nomount




Oracle instance started




Total System Global Area    2415917880 bytes




Fixed Size                     8899384 bytes

Variable Size                520093696 bytes

Database Buffers            1879048192 bytes

Redo Buffers                   7876608 bytes




RMAN> restore controlfile from '+DATA/ORCL/CONTROLFILE/current.261.1062308079';




Starting restore at 20-JAN-21

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=261 device type=DISK




channel ORA_DISK_1: copied control file copy

output file name=+DATA/PRDORCL/CONTROLFILE/control01.ctl

output file name=+DATA/PRDORCL/CONTROLFILE/control02.ctl

Finished restore at 20-JAN-21




RMAN> alter database open resetlogs;




Statement processed

The controlfiles were restored where we desired and we can double check it on asmcmd as well.

[oracle@oel82 dbs]$ . oraenv <<< +ASM

ORACLE_SID = [prdorcl] ? The Oracle base remains unchanged with value /u01/app/oracle

[oracle@oel82 dbs]$

[oracle@oel82 dbs]$ asmcmd

ASMCMD> cd data

ASMCMD> ls

ORCL/

PRDORCL/

ASMCMD> cd PRDORCL

ASMCMD> cd CONTROLFILE

ASMCMD> ls -l

Type         Redund  Striped  Time             Sys  Name

CONTROLFILE  UNPROT  FINE     JAN 20 06:00:00  N    control01.ctl => +DATA/PRDORCL/CONTROLFILE/current.270.1062312847

CONTROLFILE  UNPROT  FINE     JAN 20 06:00:00  N    control02.ctl => +DATA/PRDORCL/CONTROLFILE/current.271.1062313075

CONTROLFILE  UNPROT  FINE     JAN 20 06:00:00  Y    current.270.1062312847

CONTROLFILE  UNPROT  FINE     JAN 20 06:00:00  Y    current.271.1062313075

And you probably is questioning yourself now. OK, good, the controlfiles are there, but. What about the datafiles?

ASMCMD> cd ..

ASMCMD> cd datafile

ASMCMD> ls

ASMCMD>

ASMCMD>

ASMCMD> pwd

+data/PRDORCL/datafile

ASMCMD> ls -l

ASMCMD>

Approach Number #1 – Aliases

Nothing is on datafiles folder. Do you rememer when I said we could use two approaches on this case? Well, I will use the approach documented on Doc Id 564993.1 using aliases as the first approach. In this approach, the files still on the same place, but we create aliases and then we can safely rename the existing datafiles to the folder with the correct name (PRDORCL). This way is recommended when you don’t care about having multiple folders on ASM and you cannot afford a huge downtime.

I don’t need to remember you, we cannot remove the original folder without deleting all your datafiles.

Let’s take a look on the situation now. I have my controlfiles on the correct path and also redologs, when I issued open resetlogs it already created new logs for me on the correct path.

[oracle@oel82 dbs]$ . oraenv <<< prdorcl

ORACLE_SID = [prdorcl] ? The Oracle base remains unchanged with value /u01/app/oracle

[oracle@oel82 dbs]$

[oracle@oel82 dbs]$ sqlplus "/as sysdba"




SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 20 21:05:48 2021

Version 19.8.0.0.0




Copyright (c) 1982, 2020, Oracle.  All rights reserved.







Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.8.0.0.0




SQL> select name from v$datafile;




NAME

--------------------------------------------------------------------------------

+DATA/ORCL/DATAFILE/system.256.1062307897

+DATA/ORCL/DATAFILE/sysaux.257.1062307943

+DATA/ORCL/DATAFILE/undotbs1.258.1062307979

+DATA/ORCL/DATAFILE/users.259.1062307981




SQL> select name from v$tempfile;




NAME

--------------------------------------------------------------------------------

+DATA/ORCL/TEMPFILE/temp.268.1062308105







SQL> select member from v$logfile;




MEMBER

--------------------------------------------------------------------------------

+DATA/PRDORCL/ONLINELOG/group_3.267.1062313129

+DATA/PRDORCL/ONLINELOG/group_3.265.1062313131

+DATA/PRDORCL/ONLINELOG/group_2.266.1062313127

+DATA/PRDORCL/ONLINELOG/group_2.264.1062313129

+DATA/PRDORCL/ONLINELOG/group_1.263.1062313125

+DATA/PRDORCL/ONLINELOG/group_1.262.1062313127




6 rows selected.




SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

I brought the database down to rename the datafiles, but first we need to create the aliases connected on ASM instance via sqlplus “/ as sysasm”

[oracle@oel82 dbs]$ . oraenv <<< +ASM

ORACLE_SID = [prdorcl] ? The Oracle base remains unchanged with value /u01/app/oracle

[oracle@oel82 dbs]$

[oracle@oel82 dbs]$ sqlplus "/as sysasm"




SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 20 21:49:27 2021

Version 19.8.0.0.0




Copyright (c) 1982, 2020, Oracle.  All rights reserved.







Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.8.0.0.0




SQL> alter diskgroup DATA add alias '+DATA/PRDORCL/DATAFILE/system01.dbf' for  '+DATA/ORCL/DATAFILE/system.256.1062307897';




Diskgroup altered.




SQL> alter diskgroup DATA add alias '+DATA/PRDORCL/DATAFILE/sysaux01.dbf' for  '+DATA/ORCL/DATAFILE/sysaux.257.1062307943';SQL>




Diskgroup altered.




SQL> alter diskgroup DATA add alias '+DATA/PRDORCL/DATAFILE/undotbs1.dbf' for  '+DATA/ORCL/DATAFILE/undotbs1.258.1062307979';




Diskgroup altered.




SQL> alter diskgroup DATA add alias '+DATA/PRDORCL/DATAFILE/users01.dbf'  for  '+DATA/ORCL/DATAFILE/users.259.1062307981';




Diskgroup altered.




SQL> alter diskgroup DATA add alias '+DATA/PRDORCL/TEMPFILE/temp01.dbf' for '+DATA/ORCL/TEMPFILE/temp.268.1062308105';




Diskgroup altered.

Once the aliases were created, back to the database instance on MOUNT mode and rename all the datafiles to point to the aliases recently created.

SQL> alter database rename file '+DATA/ORCL/DATAFILE/system.256.1062307897'   to '+DATA/PRDORCL/DATAFILE/system01.dbf' ;




Database altered.




SQL> alter database rename file '+DATA/ORCL/DATAFILE/sysaux.257.1062307943'   to '+DATA/PRDORCL/DATAFILE/sysaux01.dbf';




Database altered.




SQL> alter database rename file '+DATA/ORCL/DATAFILE/undotbs1.258.1062307979' to '+DATA/PRDORCL/DATAFILE/undotbs1.dbf' ;




Database altered.




SQL> alter database rename file '+DATA/ORCL/DATAFILE/users.259.1062307981'    to '+DATA/PRDORCL/DATAFILE/users01.dbf';




Database altered.




SQL> alter database rename file '+DATA/ORCL/TEMPFILE/temp.268.1062308105' to '+DATA/PRDORCL/TEMPFILE/temp01.dbf';




Database altered.




SQL> alter database open;




Database altered.

Putting the spfile back to ASM on the correct PATH

[oracle@oel82 ~]$ sqlplus "/as sysdba"




SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 20 22:38:02 2021

Version 19.8.0.0.0




Copyright (c) 1982, 2020, Oracle.  All rights reserved.







Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.8.0.0.0




SQL> create spfile='+DATA/PRDORCL/PARAMETERFILE/spfileprdorcl.ora' from pfile='/u01/app/oracle/product/19.8.0/db_1/dbs/initprdorcl.ora';




File created.

Adding the new database on Oracle Restart

[oracle@oel82 ~]$ srvctl add database -db prdorcl -oraclehome /u01/app/oracle/product/19.8.0/db_1 -spfile '+DATA/PRDORCL/PARAMETERFILE/spfileprdorcl.ora'

[oracle@oel82 ~]$ srvctl start database -db prdorcl

[oracle@oel82 ~]$ crsctl stat res -t

--------------------------------------------------------------------------------

Name           Target  State        Server                   State details

--------------------------------------------------------------------------------

Local Resources

--------------------------------------------------------------------------------

ora.DATA.dg

               ONLINE  ONLINE       oel82                    STABLE

ora.LISTENER.lsnr

               ONLINE  ONLINE       oel82                    STABLE

ora.asm

               ONLINE  ONLINE       oel82                    Started,STABLE

ora.ons

               OFFLINE OFFLINE      oel82                    STABLE

--------------------------------------------------------------------------------

Cluster Resources

--------------------------------------------------------------------------------

ora.cssd

      1        ONLINE  ONLINE       oel82                    STABLE

ora.diskmon

      1        OFFLINE OFFLINE                               STABLE

ora.evmd

      1        ONLINE  ONLINE       oel82                    STABLE

ora.orcl.db

      1        OFFLINE OFFLINE                               STABLE

ora.prdorcl.db

      1        ONLINE  ONLINE       oel82                    Open,HOME=/u01/app/o

                                                             racle/product/19.8.0

                                                             /db_1,STABLE

--------------------------------------------------------------------------------

And finally removing the old reference for the old database on Oracle Restart

[oracle@oel82 ~]$ srvctl remove database -db orcl

Remove the database orcl? (y/[n]) y

[oracle@oel82 ~]$

Adjusting the listener on Grid Home to register my new database name

[oracle@oel82 ~]$ cd $ORACLE_HOME

[oracle@oel82 grid]$ cd network/admin/

[oracle@oel82 admin]$ vi listener.ora




[oracle@oel82 admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/19.8.0/grid/network/admin/listener.ora

# Generated by Oracle configuration tools.




#Backup file is  /u01/app/oracle/crsdata/oel82/output/listener.ora.bak.oel82.oracle line added by Agent




SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = prdorcl)

      (ORACLE_HOME = /u01/app/oracle/product/19.8.0/db_1)

      (SID_NAME = prdorcl)

    )

  )




VALID_NODE_CHECKING_REGISTRATION_LISTENER = ON




LISTENER =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = oel82.fritz.box)(PORT = 1521))

  )




ADR_BASE_LISTENER = /u01/app/oracle




ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON

Then restart the listener

[oracle@oel82 admin]$ srvctl stop listener

[oracle@oel82 admin]$ srvctl start listener

[oracle@oel82 admin]$ lsnrctl status




LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 20-JAN-2021 22:49:46




Copyright (c) 1991, 2020, Oracle.  All rights reserved.




Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel82.fritz.box)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production

Start Date                20-JAN-2021 08:49:20

Uptime                    0 days 0 hr. 0 min. 25 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/app/19.8.0/grid/network/admin/listener.ora

Listener Log File         /u01/app/oracle/diag/tnslsnr/oel82/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel82)(PORT=1521)))

Services Summary...

Service "prdorcl" has 1 instance(s).

  Instance "prdorcl", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

[oracle@oel82 admin]$

Approach Number #2 – Copy

In this approach you have more downtime because you need to copy the datafiles from one folder to another one. This will physically move the data to the destination folder, so we can remove the source folder once we finished. If downtime is not so important and your database is not so big you might have enough space to do it using backup as copy from rman, or you could do it tablespace by tablespace, in this case you don’t need to have the double of your database size in storage to perform this procedure.

I’m gonna show to you how to use copy command and I’m not using archivelog mode for this test database, I will do the commands offline, I will copy datafile per datafile to the destination folder using rman copy command.

Note my commands are poiting to the aliases because I tested the #1 approach before to execute this one

RMAN> copy datafile '+DATA/PRDORCL/DATAFILE/system01.dbf' to '+DATA';




Starting backup at 20-JAN-21

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=+DATA/PRDORCL/DATAFILE/system01.dbf

output file name=+DATA/PRDORCL/DATAFILE/system.272.1062318091 tag=TAG20210120T142128 RECID=1 STAMP=1062339697

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16

Finished backup at 20-JAN-21




Starting Control File Autobackup at 20-JAN-21

piece handle=+DATA/PRDORCL/AUTOBACKUP/2021_01_20/n_1062339305.273.1062318107 comment=NONE

Finished Control File Autobackup at 20-JAN-21




RMAN> copy datafile '+DATA/PRDORCL/DATAFILE/sysaux01.dbf' to '+DATA';




Starting backup at 20-JAN-21

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=+DATA/PRDORCL/DATAFILE/sysaux01.dbf

output file name=+DATA/PRDORCL/DATAFILE/sysaux.274.1062318281 tag=TAG20210120T142439 RECID=2 STAMP=1062339885

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

Finished backup at 20-JAN-21




Starting Control File Autobackup at 20-JAN-21

piece handle=+DATA/PRDORCL/AUTOBACKUP/2021_01_20/n_1062339305.275.1062318289 comment=NONE

Finished Control File Autobackup at 20-JAN-21




RMAN> copy datafile '+DATA/PRDORCL/DATAFILE/undotbs1.dbf' to '+DATA';




Starting backup at 20-JAN-21

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=+DATA/PRDORCL/DATAFILE/undotbs1.dbf

output file name=+DATA/PRDORCL/DATAFILE/undotbs1.276.1062318293 tag=TAG20210120T142453 RECID=3 STAMP=1062339899

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08

Finished backup at 20-JAN-21




Starting Control File Autobackup at 20-JAN-21

piece handle=+DATA/PRDORCL/AUTOBACKUP/2021_01_20/n_1062339305.277.1062318305 comment=NONE

Finished Control File Autobackup at 20-JAN-21




RMAN> copy datafile '+DATA/PRDORCL/DATAFILE/users01.dbf' to '+DATA';




Starting backup at 20-JAN-21

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00007 name=+DATA/PRDORCL/DATAFILE/users01.dbf

output file name=+DATA/PRDORCL/DATAFILE/users.278.1062318311 tag=TAG20210120T142509 RECID=4 STAMP=1062339910

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 20-JAN-21




Starting Control File Autobackup at 20-JAN-21

piece handle=+DATA/PRDORCL/AUTOBACKUP/2021_01_20/n_1062339305.279.1062318313 comment=NONE

Finished Control File Autobackup at 20-JAN-21

Important step is “rename” the files again on your controlfile, we can do that, easily, from RMAN

RMAN> switch database to copy;




datafile 1 switched to datafile copy "+DATA/PRDORCL/DATAFILE/system.272.1062318091"

datafile 3 switched to datafile copy "+DATA/PRDORCL/DATAFILE/sysaux.274.1062318281"

datafile 4 switched to datafile copy "+DATA/PRDORCL/DATAFILE/undotbs1.276.1062318293"

datafile 7 switched to datafile copy "+DATA/PRDORCL/DATAFILE/users.278.1062318311"

As all the files are now stored on the correct folder, on approach 2 we can remove safely the source folder to avoid issues in the future with space.

ASMCMD> ASMCMD> rm -rf ORCL

ASMCMD>

ASMCMD>

ASMCMD> pwd

+DATA

ASMCMD> ls -l

Type  Redund  Striped  Time  Sys  Name

                             N    PRDORCL/

ASMCMD>

One thing about the temporary tablespace, Oracle will recreate it for you when you open the database.

Summary: Keep in mind we saw two different approaches here to solve the same issue and if you created the ASM folder manually you can also apply a 3rd approach describe by this blogpost here: Renaming directory on ASM

Do you NID to rename an Oracle DB on ASM?

As you noticed, I have created a database named orcl and we will rename it to prdorcl (don’t judge me, I was running out of good names). My database name will be change from orcl to prdorcl.

As I mentioned before, the database must be on MOUNT mode to use NID

With the database mounted, we can apply the NID using this command line

We saw the process was easy and quick to apply, don’t be afraid, but NID utility will shutdown your database and later on we will need to open using resetlogs.
Now as I haven’t created a pfile or a spfile with the new name I will fail to start the instance

This orcl database was created using DBCA, so the spfile is stored on ASM by default

We could identify the path of spfile from above command, so now I can create a pfile, edit it and start my new instance with name prdorcl.

So, just as reference, the original pfile looks like this… except because I removed dynamic memory parameter from the top of the file. There are some parameters do change, audit_file_dest, control_files, db_name and dispatchers. We will see the new file later on (wait for it).

As I mentioned prior, in the beginning of the post. It’s strongly recommendable to create the path on ASM manually.

Now I have the new path created by me and not flagged as “SYSTEM”

It’s time to edit my new pfile to reflect the new database name and configurations

Create the folder to store audit files on OS

Change your tnsnames to reflect the new names

Edit the /etc/oratab file to reflect the new name

I’m about to start the instance and restore the controlfiles to the destination I put on parameter controlfiles and then open the database with resetlogs.

Exit from sqlplus and login on RMAN to restore the controlfiles and open the database

The controlfiles were restored where we desired and we can double check it on asmcmd as well.

And you probably is questioning yourself now. OK, good, the controlfiles are there, but. What about the datafiles?

Approach Number #1 – Aliases

I don’t need to remember you, we cannot remove the original folder without deleting all your datafiles.

Let’s take a look on the situation now. I have my controlfiles on the correct path and also redologs, when I issued open resetlogs it already created new logs for me on the correct path.

I brought the database down to rename the datafiles, but first we need to create the aliases connected on ASM instance via sqlplus “/ as sysasm”

Once the aliases were created, back to the database instance on MOUNT mode and rename all the datafiles to point to the aliases recently created.

Putting the spfile back to ASM on the correct PATH

Adding the new database on Oracle Restart

And finally removing the old reference for the old database on Oracle Restart

Adjusting the listener on Grid Home to register my new database name

Then restart the listener

Approach Number #2 – Copy

I’m gonna show to you how to use copy command and I’m not using archivelog mode for this test database, I will do the commands offline, I will copy datafile per datafile to the destination folder using rman copy command.

Note my commands are poiting to the aliases because I tested the #1 approach before to execute this one

Important step is “rename” the files again on your controlfile, we can do that, easily, from RMAN

As all the files are now stored on the correct folder, on approach 2 we can remove safely the source folder to avoid issues in the future with space.

One thing about the temporary tablespace, Oracle will recreate it for you when you open the database.

Summary: Keep in mind we saw two different approaches here to solve the same issue and if you created the ASM folder manually you can also apply a 3rd approach describe by this blogpost here: Renaming directory on ASM

I hope you liked this post, keep posted on my database adventures following me on twitter @mufalani and on linkedin

Best Regards,

Rodrigo Mufalani

Do you NID to rename an Oracle DB on ASM?

As you noticed, I have created a database named orcl and we will rename it to prdorcl (don’t judge me, I was running out of good names). My database name will be change from orcl to prdorcl.

As I mentioned before, the database must be on MOUNT mode to use NID

With the database mounted, we can apply the NID using this command line

We saw the process was easy and quick to apply, don’t be afraid, but NID utility will shutdown your database and later on we will need to open using resetlogs. Now as I haven’t created a pfile or a spfile with the new name I will fail to start the instance

This orcl database was created using DBCA, so the spfile is stored on ASM by default

We could identify the path of spfile from above command, so now I can create a pfile, edit it and start my new instance with name prdorcl.

So, just as reference, the original pfile looks like this… except because I removed dynamic memory parameter from the top of the file. There are some parameters do change, audit_file_dest, control_files, db_name and dispatchers. We will see the new file later on (wait for it).

As I mentioned prior, in the beginning of the post. It’s strongly recommendable to create the path on ASM manually.

Now I have the new path created by me and not flagged as “SYSTEM”

It’s time to edit my new pfile to reflect the new database name and configurations

Create the folder to store audit files on OS

Change your tnsnames to reflect the new names

Edit the /etc/oratab file to reflect the new name

I’m about to start the instance and restore the controlfiles to the destination I put on parameter controlfiles and then open the database with resetlogs.

Exit from sqlplus and login on RMAN to restore the controlfiles and open the database

The controlfiles were restored where we desired and we can double check it on asmcmd as well.

And you probably is questioning yourself now. OK, good, the controlfiles are there, but. What about the datafiles?

Approach Number #1 – Aliases

I don’t need to remember you, we cannot remove the original folder without deleting all your datafiles.

Let’s take a look on the situation now. I have my controlfiles on the correct path and also redologs, when I issued open resetlogs it already created new logs for me on the correct path.

I brought the database down to rename the datafiles, but first we need to create the aliases connected on ASM instance via sqlplus “/ as sysasm”

Once the aliases were created, back to the database instance on MOUNT mode and rename all the datafiles to point to the aliases recently created.

Putting the spfile back to ASM on the correct PATH

Adding the new database on Oracle Restart

And finally removing the old reference for the old database on Oracle Restart

Adjusting the listener on Grid Home to register my new database name

Then restart the listener

Approach Number #2 – Copy

I’m gonna show to you how to use copy command and I’m not using archivelog mode for this test database, I will do the commands offline, I will copy datafile per datafile to the destination folder using rman copy command.

Note my commands are poiting to the aliases because I tested the #1 approach before to execute this one

Important step is “rename” the files again on your controlfile, we can do that, easily, from RMAN

As all the files are now stored on the correct folder, on approach 2 we can remove safely the source folder to avoid issues in the future with space.

One thing about the temporary tablespace, Oracle will recreate it for you when you open the database.

Summary: Keep in mind we saw two different approaches here to solve the same issue and if you created the ASM folder manually you can also apply a 3rd approach describe by this blogpost here: Renaming directory on ASM

I hope you liked this post, keep posted on my database adventures following me on twitter @mufalani and on linkedin

Best Regards,

Rodrigo Mufalani

We saw the process was easy and quick to apply, don’t be afraid, but NID utility will shutdown your database and later on we will need to open using resetlogs.
Now as I haven’t created a pfile or a spfile with the new name I will fail to start the instance