Rename Database When Using ASM

This post has been a bit long in the making… Whilst finishing it off I noted the dates in the output generated when I was first doing this… Just under 12 months to get round to finishing it off… There’s never enough time to do everything I want to do :-)

Anyway, as part of some Data Guard testing, which included backing up from a physical standby, I created a couple of new virtual machines using a Oracle supplied template. When I found that the database in the template was named something other than what I wanted my mind ran ahead to wondering what happens when you use NEWDBID with a database that resides in ASM…

I thought that maybe Oracle just handles everything for you: You run NEWDBID to change the database name and Oracle moves everything inside ASM to reflect the new database name (not that it would really need to “move” anything)… Well, it doesn’t, and maybe that would be Oracle taking a little too much control away from the DBA? Anyway, here is what happened and how I “fixed” it.

The virtual machine template “OVM_EL5U2_X86_64_ORACLE10G_PVM” comes with a preconfigured Oracle 10g database named “orcl”:

[oracle@zinc ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 3 22:49:39 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

SQL> conn / as sysdba
Connected.
SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      orcl
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Which comes configured to use ASM…

[oracle@zinc ~]$ . oraenv
ORACLE_SID = [orcl] ? +ASM
[oracle@zinc ~]$ asmcmd -p
ASMCMD [+] > ls -l
State    Type    Rebal  Unbal  Name
MOUNTED  NORMAL  N      N      DATA/
ASMCMD [+] > cd DATA
ASMCMD [+DATA] > ls -l
Type  Redund  Striped  Time             Sys  Name
                                        Y    DB_UNKNOWN/
                                        Y    ORCL/
ASMCMD [+DATA] >

But, I didn’t want my database to be called “orcl”. Although, there was no reason why I NEEDED to change the database name for my planned Data Guard testing. Once I’d considered renaming the database there was no turning back…

Whilst connected to the ASM instance I checked the location of the spfile:

ASMCMD [+] > ls -l DATA/ORCL
Type           Redund  Striped  Time             Sys  Name
                                                 Y    CONTROLFILE/
                                                 Y    DATAFILE/
                                                 Y    ONLINELOG/
                                                 Y    TEMPFILE/
                                                 N    spfileorcl.ora => +DATA/DB_UNKNOWN/PARAMETERFILE/SPFILE.269.683246731
ASMCMD [+] >

I didn’t like that. “DB_UNKNOWN” is a bit ugly to my eyes. It was time to run NEWDBID (nid) and see what happens to the ASM directory structure.

Resetting my environment:

[oracle@zinc ~]$ . oraenv
ORACLE_SID = [+ASM] ? orcl

Opening the database in mount mode:

[oracle@zinc ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 3 22:58:57 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  2085872 bytes
Variable Size             180358160 bytes
Database Buffers          423624704 bytes
Redo Buffers                6299648 bytes
Database mounted.
SQL> exit

Running the “nid” command:

[oracle@zinc ~]$ nid target=/ setname=true dbname=dg10g

DBNEWID: Release 10.2.0.4.0 - Production on Fri Apr 3 23:00:50 2009

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

Connected to database ORCL (DBID=1192025583)

Connected to server version 10.2.0

Control Files in database:
    +DATA/orcl/controlfile/current.261.664244847
    +DATA/orcl/controlfile/current.260.664244847

Change database name of database ORCL to DG10G? (Y/[N]) => Y

Proceeding with operation
Changing database name from ORCL to DG10G
    Control File +DATA/orcl/controlfile/current.261.664244847 - modified
    Control File +DATA/orcl/controlfile/current.260.664244847 - modified
    Datafile +DATA/orcl/datafile/system.256.664244797 - wrote new name
    Datafile +DATA/orcl/datafile/undotbs1.258.664244797 - wrote new name
    Datafile +DATA/orcl/datafile/sysaux.257.664244797 - wrote new name
    Datafile +DATA/orcl/datafile/users.259.664244797 - wrote new name
    Datafile +DATA/orcl/tempfile/temp.268.664244883 - wrote new name
    Control File +DATA/orcl/controlfile/current.261.664244847 - wrote new name
    Control File +DATA/orcl/controlfile/current.260.664244847 - wrote new name
    Instance shut down

Database name changed to DG10G.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.

Creating a pfile to point to the spfile, which has been PARTIALLY updated with parameters for dg10g as part of running nid:

[oracle@zinc ~]$ cat << EOF > $ORACLE_HOME/dbs/initdg10g.ora
> spfile='+DATA/ORCL/spfileorcl.ora'
> EOF

Creating a backup of the newly created pfile as it will be overwritten as part of later stages:

[oracle@zinc ~]$ cp $ORACLE_HOME/dbs/initdg10g.ora $ORACLE_HOME/dbs/initdg10g.ora.spfile

Creating a password file for dg10g:

[oracle@zinc ~]$ orapwd file=$ORACLE_HOME/dbs/orapwdg10g password=xxxxxxxxxx*

* It strikes me that not being able to run orapwd without the password argument is a bit of security flaw – This as been addressed at 11.1 and above.

Modifying /etc/oratab to reflect the new database name:

Change “orcl:/u01/app/oracle/product/10.2.0/db_1:W” to “dg10g:/u01/app/oracle/product/10.2.0/db_1:W”

Seting environment for new database name and sid:

[oracle@zinc ~]$ . oraenv
ORACLE_SID = [orcl] ? dg10g
[oracle@zinc ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 3 23:35:19 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  2085872 bytes
Variable Size             167775248 bytes
Database Buffers          436207616 bytes
Redo Buffers                6299648 bytes
SQL> create pfile from spfile;

File created.

SQL> shutdown
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> exit

Editing $ORACLE_HOME/dbs/initdg10g.ora

orcl.__db_cache_size=423624704
dg10g.__db_cache_size=436207616
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
dg10g.__large_pool_size=4194304
orcl.__shared_pool_size=171966464
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.background_dump_dest='/u01/app/oracle/admin/orcl/bdump'
*.compatible='10.2.0.3.0'
*.control_files='+DATA/orcl/controlfile/current.261.664244847','+DATA/orcl/controlfile/current.260.664244847'
*.core_dump_dest='/u01/app/oracle/admin/orcl/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.local_listener='(ADDRESS = (PROTOCOL=TCP) (HOST=zinc) (PORT=1521))'
*.open_cursors=300
*.pga_aggregate_target=203423744
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=612368384
*.shared_servers=5
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/orcl/udump'

Note that some parameters have already been set for “dg10g”. In order to achieve what I want in terms of removing all references to orcl from the directory structure, all parameters that contain “orcl” in their value were updated. My perferred approach is to use “:%s/orcl/dg10g/g” in vi, but approaches to this type of task are often down to personal preference :-)

My find and replace approach left me with two entires for __db_cache_size and __large_pool_size, so one of each was removed.

As I would be recreating the control files in the desired location the CONTROL_FILES parameter was further modified to simply provide the ASM disk group that the files are to be created in.

My final parameter file is show below:

dg10g.__db_cache_size=436207616
dg10g.__java_pool_size=4194304
dg10g.__large_pool_size=4194304
dg10g.__shared_pool_size=171966464
dg10g.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/dg10g/adump'
*.background_dump_dest='/u01/app/oracle/admin/dg10g/bdump'
*.compatible='10.2.0.3.0'
*.control_files='+DATA','+DATA'
*.core_dump_dest='/u01/app/oracle/admin/dg10g/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='dg10g'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dg10gXDB)'
*.job_queue_processes=10
*.local_listener='(ADDRESS = (PROTOCOL=TCP) (HOST=zinc) (PORT=1521))'
*.open_cursors=300
*.pga_aggregate_target=203423744
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=612368384
*.shared_servers=5
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/dg10g/udump'

Creating the *_dest directories:

[oracle@zinc ~]$ mkdir -p /u01/app/oracle/admin/dg10g/adump
[oracle@zinc ~]$ mkdir /u01/app/oracle/admin/dg10g/bdump
[oracle@zinc ~]$ mkdir /u01/app/oracle/admin/dg10g/cdump
[oracle@zinc ~]$ mkdir /u01/app/oracle/admin/dg10g/udump

In order to have Oracle automatically update the spfile with the location of the new control files via the = ALTER SYSTEM SET CONTROL_FILES command, an spfile should be created from the above pfile.

[oracle@zinc ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 3 23:42:20 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  2085872 bytes
Variable Size             167775248 bytes
Database Buffers          436207616 bytes
Redo Buffers                6299648 bytes
SQL> create spfile = '+DATA/DG10G/spfiledg10g.ora' from pfile;

File created.

SQL> shutdown
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> exit

The backup (manually created earlier) pfile will now be used to point to the new ASM spfile.

[oracle@zinc dbs]$ mv initdg10g.ora initdg10g.ora.pfile
[oracle@zinc dbs]$ cp initdg10g.ora.spfile initdg10g.ora

Modifying initdg10g.ora to point to the correct spfile:

SPFILE='+DATA/gg02/spfilegg02.ora'

Using the controlfiles within +DATA/ORCL to create a new controlfiles under +DATA/DG10G:

[oracle@zinc ~]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Fri Apr 3 23:45:39 2009

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

connected to target database (not started)

RMAN> startup nomount

Oracle instance started

Total System Global Area     612368384 bytes

Fixed Size                     2085872 bytes
Variable Size                167775248 bytes
Database Buffers             436207616 bytes
Redo Buffers                   6299648 bytes

RMAN> restore controlfile from '+DATA/orcl/controlfile/current.261.664244847';

Starting restore at 03-APR-09
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
output file name=+DATA/dg10g/controlfile/current.270.683250567
output file name=+DATA/dg10g/controlfile/current.271.683250767
Finished restore at 03-APR-09

RMAN> sql "alter system set control_files = ''+DATA/dg10g/controlfile/current.270.683250567'',''+DATA/dg10g/controlfile/current.271.683250767'' scope = spfile";

sql statement: alter system set control_files = ''+DATA/dg10g/controlfile/current.270.683250567'',''+DATA/dg10g/controlfile/current.271.683250767'' scope = spfile

RMAN> shutdown

Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     612368384 bytes

Fixed Size                     2085872 bytes
Variable Size                167775248 bytes
Database Buffers             436207616 bytes
Redo Buffers                   6299648 bytes

Using report schema to see where the datafiles are currently located within ASM and then using BACKUP AS COPY to create a new copy of the files in the DG10G directory.

RMAN> report schema;

Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    600      SYSTEM               ***     +DATA/orcl/datafile/system.256.664244797
2    460      UNDOTBS1             ***     +DATA/orcl/datafile/undotbs1.258.664244797
3    400      SYSAUX               ***     +DATA/orcl/datafile/sysaux.257.664244797
4    5        USERS                ***     +DATA/orcl/datafile/users.259.664244797

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    29       TEMP                 32767       +DATA/orcl/tempfile/temp.268.664244883

RMAN> backup as copy database;

Starting backup at 04-APR-2009 00:35:53
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=+DATA/orcl/datafile/system.256.664244797
output filename=+DATA/dg10g/datafile/system.259.683253353 tag=TAG20090404T003553 recid=17 stamp=683253441
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=+DATA/orcl/datafile/undotbs1.258.664244797
output filename=+DATA/dg10g/datafile/undotbs1.275.683253449 tag=TAG20090404T003553 recid=18 stamp=683253517
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=+DATA/orcl/datafile/sysaux.257.664244797
output filename=+DATA/dg10g/datafile/sysaux.257.683253525 tag=TAG20090404T003553 recid=19 stamp=683253586
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:06
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=+DATA/orcl/datafile/users.259.664244797
output filename=+DATA/dg10g/datafile/users.274.683253589 tag=TAG20090404T003553 recid=20 stamp=683253590
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=+DATA/dg10g/controlfile/backup.258.683253593 tag=TAG20090404T003553 recid=21 stamp=683253595
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 04-APR-2009 00:40:00

RMAN> list copy of database;

List of Datafile Copies
Key     File S Completion Time      Ckp SCN    Ckp Time             Name
------- ---- - -------------------- ---------- -------------------- ----
17      1    A 04-APR-2009 00:37:21 1172322    04-APR-2009 00:26:45 +DATA/dg10g/datafile/system.259.683253353
18      2    A 04-APR-2009 00:38:37 1172322    04-APR-2009 00:26:45 +DATA/dg10g/datafile/undotbs1.275.683253449
19      3    A 04-APR-2009 00:39:46 1172322    04-APR-2009 00:26:45 +DATA/dg10g/datafile/sysaux.257.683253525
20      4    A 04-APR-2009 00:39:50 1172322    04-APR-2009 00:26:45 +DATA/dg10g/datafile/users.274.683253589

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/dg10g/datafile/system.259.683253353"
datafile 2 switched to datafile copy "+DATA/dg10g/datafile/undotbs1.275.683253449"
datafile 3 switched to datafile copy "+DATA/dg10g/datafile/sysaux.257.683253525"
datafile 4 switched to datafile copy "+DATA/dg10g/datafile/users.274.683253589"

RMAN> alter database open;

database opened

RMAN> report schema;

Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    600      SYSTEM               ***     +DATA/dg10g/datafile/system.259.683253353
2    460      UNDOTBS1             ***     +DATA/dg10g/datafile/undotbs1.275.683253449
3    400      SYSAUX               ***     +DATA/dg10g/datafile/sysaux.257.683253525
4    5        USERS                ***     +DATA/dg10g/datafile/users.274.683253589

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    29       TEMP                 32767       +DATA/orcl/tempfile/temp.268.664244883

Next job is to move the tempfile

RMAN> sql 'alter tablespace temp add tempfile';

sql statement: alter tablespace temp add tempfile

RMAN> sql "alter database tempfile ''+DATA/orcl/tempfile/temp.268.664244883'' drop";

sql statement: alter database tempfile ''+DATA/orcl/tempfile/temp.268.66424488'' drop

RMAN> exit

At this point the only files (used by the database/instance) left in +DATA/orcl are the Online logs. As far as I’m aware the only way to move these is to drop and recreate them, but with a little bit of thought and appropriate us of commands this can be done with the database open and in use.

Using the following command to create a new online redo log group. This relies on DB_CREATE_FILE_DEST, or DB_CREATE_ONLINE_LOG_DEST_n, parameters being set.

SQL> alter database add logfile group <a group number that does not already exist>;

Once the new online redo log group has been created in the DG10G directory within the ASM diskgroup, the existing online redo log groups in the ORCL directory were dropped using:

SQL> alter database drop logfile group <a group number that is not in use and is in the ORCL directory>;

Something to note is that, quite obviously, you cannot drop a log file group that is currently in use (ORA-01623), one that is need for crash recovery (ORA-01624) or reduced the number of redo log groups to less than 2 (ORA-01567)… Hence the comment about thought and appropriate commands. If you are trying this or doing something similar then the following commands might be helpful:

SQL> alter system switch logfile;
SQL> alter system checkpoint;
SQL> alter system archive log group <group requiring archiving>;

Anyway, after a bit of juggling I got what I wanted…

SQL> select member from v$logfile
  2  order by member;

MEMBER
--------------------------------------------------------------------------------
+DATA/dg10g/onlinelog/group_1.262.683378145
+DATA/dg10g/onlinelog/group_1.263.683378123
+DATA/dg10g/onlinelog/group_2.264.683377935
+DATA/dg10g/onlinelog/group_2.265.683377913
+DATA/dg10g/onlinelog/group_3.266.683378249
+DATA/dg10g/onlinelog/group_3.267.683378229

6 rows selected.

Whilst finally writing this up it has occurred to me that there are other/better ways of achieving the same result, i.e., just duplicate the database… Nonetheless, I found this a useful exercise and if you read this far then it can’t have been too dull :-)

About these ads

10 thoughts on “Rename Database When Using ASM

  1. Pingback: Blogroll Report 02/04 /2010 – 09/04/2010 « Coskan’s Approach to Oracle

  2. Was looking for renaming 11g database and associated directories in ASM and found this to be exact hit! Quite easy to follow and very helpful, thanks for sharing.

    Was looking for mvdir commands within asm but there is none. it would have made life easier by just re-creating control file.

    anyways, thanks.

  3. Hi ,

    Thank you very much for the post . It really solved my problem of changing the dbname directory in ASM at time of refreshment from prod to dev

  4. Pingback: Rename Database Having Datafiles on ASM – 11gR2 « Anand's Blog

  5. Pingback: Orphaned Files in ASM « ORAganism

  6. HI,
    I tried to follow the step but when I executed the line “backup as copy database;” all files have been copied to my /backup, not in the ASM diskgroup. Any idea what is going on?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s