Sunday, February 1, 2015

Rename directory on an Automatic Storage Management ASM

Renaming a directory on ASM is valid only when you create directory manualy (i.e issued command "alter diskgroup add directory" on sqlplus of ASM), if these directories was created by DBCA, RMAN these are SYSTEM CREATED directory and cannot be renamed.

You can check it by query V$ASM_ALIAS on column SYSTEM_CREATED if directories are labeled with the value Y in the SYSTEM_CREATED column they cannot be renamed.

Copying the directory using asmcd is a bad choice, because ASM will generate new names to these datafiles and you will need create a list of these new datafiles and rename it on controlfile or re-create your controlfile.

Example 1: Non System created ASM directory can be renamed as below,

ASMCMD [+DATA_DG1] > mkdir test
ASMCMD [+DATA_DG1] > ls
EBSTEST/
test/

SQL> ALTER DISKGROUP DATA_DG1 RENAME DIRECTORY '+DATA_DG1/test' to '+DATA_DG1/test1';
Diskgroup altered.

ASMCMD [+DATA_DG1] > ls
EBSTEST/
test1/
ASMCMD [+DATA_DG1] > rm test1

ASMCMD [+DATA_DG1] > ls
EBSTEST/

Example 2: System created directory cannot be rename so my recommendation is to use parameter db_unique_name of database: Oracle ASM and Oracle Diagnostic Dest create your own directory structure based on parameter DB_UNIQUE_NAME.

So, first you must change parameter db_unique_name (configuring new name to directory) and move your database using rman commands.

See this step by step below,

I will change db_unique_name from "dbrman" to "DBTST"

oracle@edolraclin1:/home/oracle> rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Sep 10 15:05:17 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: DBTST (DBID=3000992386)

RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name DBRMAN

List of Permanent Datafiles
==============
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    720      SYSTEM               ***     +DS8000_DG/dbrman/datafile/system.285.793637499
2    730      SYSAUX               ***     +DS8000_DG/dbrman/datafile/sysaux.286.793637499
3    215      UNDOTBS1             ***     +DS8000_DG/dbrman/datafile/undotbs1.262.793637499
4    5        USERS                ***     +DS8000_DG/dbrman/datafile/users.263.793637513

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    50       TEMP                 32767       +DS8000_DG/dbrman/tempfile/temp.288.793637213
2    100      TEMP2                32767       +DS8000_DG/dbrman/tempfile/temp2.289.793637213

$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Sep 10 15:08:21 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, Oracle Label Security, OLAP,
Data Mining, Oracle Database Vault and Real Application Testing options

SQL> show parameter db_unique_name
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      dbrman

SQL> alter system set db_unique_name='DBTST' scope=spfile;
System altered.

SQL> show parameter control
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      +DS8000_DG/dbrman/controlfile/current.258.793638433

SQL> alter system set control_files='+DS8000_DG' scope=spfile;

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup nomount
Oracle instance started
Total System Global Area     768294912 bytes
Fixed Size                     2225448 bytes
Variable Size                469764824 bytes
Database Buffers             289406976 bytes
Redo Buffers                   6897664 bytes

SQL> show parameter db_unique_name
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------db_unique_name                       string      DBTST
Recreating controlfile with rman:

$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Sep 10 15:10:45 2012
connected to target database: DBTST (not mounted)

RMAN> restore controlfile from '+DS8000_DG/dbrman/controlfile/current.258.793638433';
Starting restore at 10-SEP-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=162 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DS8000_DG/dbtst/controlfile/current.259.793638669
Finished restore at 10-SEP-12
# controlfile recreated.

RMAN> startup mount;
database is already started
database mounted
released channel: ORA_DISK_1

RMAN> CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 3;
new RMAN configuration parameters:
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 3;
new RMAN configuration parameters are successfully stored

RMAN > CONFIGURE DEVICE TYPE DISK PARALLELISM 3 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 3 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored

RMAN> BACKUP AS COPY DEVICE TYPE DISK DATABASE FORMAT '+DS8000_DG';
Starting backup at 10-SEP-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=162 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=194 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=210 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DS8000_DG/dbrman/datafile/sysaux.286.793637499
channel ORA_DISK_2: starting datafile copy
input datafile file number=00001 name=+DS8000_DG/dbrman/datafile/system.285.793637499
channel ORA_DISK_3: starting datafile copy
input datafile file number=00003 name=+DS8000_DG/dbrman/datafile/undotbs1.262.793637499
output file name=+DS8000_DG/dbtst/datafile/sysaux.260.793638757 tag=TAG20120910T151235 RECID=35 STAMP=793638828
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:28
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DS8000_DG/dbrman/datafile/users.263.793637513
output file name=+DS8000_DG/dbtst/datafile/system.291.793638779 tag=TAG20120910T151235 RECID=36 STAMP=793638848
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:01:14
output file name=+DS8000_DG/dbtst/datafile/users.293.793638849 tag=TAG20120910T151235 RECID=38 STAMP=793638849
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
output file name=+DS8000_DG/dbtst/datafile/undotbs1.292.793638823 tag=TAG20120910T151235 RECID=37 STAMP=793638848
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:32
Finished backup at 10-SEP-12

RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile copy "+DS8000_DG/dbtst/datafile/system.291.793638779"
datafile 2 switched to datafile copy "+DS8000_DG/dbtst/datafile/sysaux.260.793638757"
datafile 3 switched to datafile copy "+DS8000_DG/dbtst/datafile/undotbs1.292.793638823"
datafile 4 switched to datafile copy "+DS8000_DG/dbtst/datafile/users.293.793638849"

RMAN >  run {
 set newname for tablespace TEMP to  '+DS8000_DG' ;
 set newname for tablespace TEMP2 to  '+DS8000_DG' ;
 switch tempfile all;
 }
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to +DS8000_DG in control file
renamed tempfile 2 to +DS8000_DG in control file

RMAN> report schema;
Report of database schema for database with db_unique_name DBTST

List of Permanent Datafiles
==================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    720      SYSTEM               ***     +DS8000_DG/dbtst/datafile/system.291.793638779
2    730      SYSAUX               ***     +DS8000_DG/dbtst/datafile/sysaux.260.793638757
3    215      UNDOTBS1             ***     +DS8000_DG/dbtst/datafile/undotbs1.292.793638823
4    5        USERS                ***     +DS8000_DG/dbtst/datafile/users.293.793638849

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    50       TEMP                 32767       +DS8000_DG
2    100      TEMP2                32767       +DS8000_DG

RMAN> startup;
database is already started
database opened

RMAN> report schema;
Report of database schema for database with db_unique_name DBTST

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    720      SYSTEM               ***     +DS8000_DG/dbtst/datafile/system.291.793638779
2    730      SYSAUX               ***     +DS8000_DG/dbtst/datafile/sysaux.260.793638757
3    215      UNDOTBS1             ***     +DS8000_DG/dbtst/datafile/undotbs1.292.793638823
4    5        USERS                ***     +DS8000_DG/dbtst/datafile/users.293.793638849

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    50       TEMP                 32767       +DS8000_DG/dbtst/tempfile/temp.294.793639305
2    100      TEMP2                32767       +DS8000_DG/dbtst/tempfile/temp2.295.793639305

$ sqlplus / as sysdba

SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+DS8000_DG/dbrman/onlinelog/group_2.257.793638261
+DS8000_DG/dbrman/onlinelog/group_1.290.793638259
+DS8000_DG/dbrman/onlinelog/group_4.287.793638255

SQL> declare
   cursor rlc is
      select group# grp, thread# thr, bytes/1024 bytes_k, 'NO' srl
        from v$log
      union
      select group# grp, thread# thr, bytes/1024 bytes_k, 'YES' srl
        from v$standby_log
      order by 1;
   stmt     varchar2(2048);
   swtstmt  varchar2(1024) := 'alter system switch logfile';
   ckpstmt  varchar2(1024) := 'alter system checkpoint global';
begin
   for rlcRec in rlc loop
      if (rlcRec.srl = 'YES') then
         stmt := 'alter database add standby logfile thread ' ||
                 rlcRec.thr || ' ''&DISKGROUP_NAME'' size ' ||
                 rlcRec.bytes_k || 'K';
         execute immediate stmt;
         stmt := 'alter database drop standby logfile group ' || rlcRec.grp;
         execute immediate stmt;
      else
         stmt := 'alter database add logfile thread ' ||
                 rlcRec.thr || ' ''&DISKGROUP_NAME'' size ' || 
                 rlcRec.bytes_k || 'K';
         execute immediate stmt;
         begin
            stmt := 'alter database drop logfile group ' || rlcRec.grp;
            dbms_output.put_line(stmt);
            execute immediate stmt;
         exception
            when others then
               execute immediate swtstmt;
               execute immediate ckpstmt;
               execute immediate stmt;
         end;
      end if;
   end loop;
end;
/

Enter value for diskgroup_name: +DS8000_DG
old  16:                  rlcRec.thr || ' ''&DISKGROUP_NAME'' size ' ||
new  16:                  rlcRec.thr || ' ''+DS8000_DG'' size ' ||
Enter value for diskgroup_name: +DS8000_DG
old  23:                  rlcRec.thr || ' ''&DISKGROUP_NAME'' size ' ||
new  23:                  rlcRec.thr || ' ''+DS8000_DG'' size ' ||
PL/SQL procedure successfully completed.

SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+DS8000_DG/dbtst/onlinelog/group_3.296.793639511
+DS8000_DG/dbtst/onlinelog/group_2.257.793639517
+DS8000_DG/dbtst/onlinelog/group_1.290.793639513

$ rman target /

RMAN> backup database;
Starting backup at 10-SEP-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=162 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=194 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=210 device type=DISK
.
.
Recovery Manager complete.

## Now you can remove the copy:

RMAN> delete copy;
.
.
.
deleted datafile copy
datafile copy file name=+DS8000_DG/dbrman/datafile/system.285.793637499 RECID=39 STAMP=793638899
deleted datafile copy
datafile copy file name=+DS8000_DG/dbrman/datafile/sysaux.286.793637499 RECID=40 STAMP=793638899
deleted datafile copy
datafile copy file name=+DS8000_DG/dbrman/datafile/undotbs1.262.793637499 RECID=41 STAMP=793638899
deleted datafile copy
datafile copy file name=+DS8000_DG/dbrman/datafile/users.263.793637513 RECID=42 STAMP=793638899
Deleted 4 objects


For any queries please don't hesitate to contact me on samiora@gmail.com