How to copy controlfile from ASM diskgroup to a cooked filesystem --Moid

How to copy controlfile from ASM diskgroup to disk in 10g?  

One of the 11g new ASM feature is the ability to use the "cp" command in ASM using "asmcmd" which 10g lacks. The following steps will help one understand  how to copy controlfile from ASM to disk.


10.2.0.3 databases on the server called MoidDBServer are:

    MoidDBServer > ps -ef |grep pmon |grep -v 'grep'
    oracle    9305     1  0 Jun03 ?        00:00:05 asm_pmon_+ASM
    oracle    6818     1  0 Jun03 ?        00:00:14 ora_pmon_QADB
    oracle   21648     1  0 16:13 ?        00:00:00 ora_pmon_DEVDB

Database called DEVDB has only two copies of controlfile and both of them are on ASM. My task was to add a copy of controlfile to disk.

These are the steps I did to copy the controlfile from ASM to disk.

First, I found the the current controlfiles locations of DEVDB.

    MoidDBServer > export ORACLE_SID=DEVDB
    MoidDBServer > sqlplus / as sysdba
    SQL> show parameter control;

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    control_file_record_keep_time        integer     7
    control_files                        string      +DG1/DEVDB/controlfile/current.257.686842909,  +DG2/DEVDB/controlfile/current.256.686842909
                            
                            
                           
Now I updated the control_files parameter in spfile to reflect the new controlfile I will be creating next.

    SQL> alter system set control_files = '+DG1/DEVDB/controlfile/current.257.686842909', '+DG2/DEVDB/controlfile/current.256.686842909', '/backup/DEVDB/CONTROLFILE/control03.ctl' scope=spfile;
  


Then I created a pfile in case we need it later:
    SQL> create pfile='/tmp/initDEVDB.ora' from spfile;



Then database was brought down cleanly:
    SQL> shutdown immediate;



Now, to copy controlfile from asm to disk, I logged in as sysdba to any other database (in this case, it will be database called QADB) which is up. Then I created the following temporary directories:

    MoidDBServer > export ORACLE_SID=QADB
    MoidDBServer > sqlplus / as sysdba
  
    SQL> create or replace directory AA as '+DG1/DEVDB/controlfile';
    SQL> create or replace directory BB as '/backup/DEVDB/CONTROLFILE';
  


Then I issued the following to copy the controlfile from ASM to DISK:

    SQL>    BEGIN
              DBMS_FILE_TRANSFER.COPY_FILE(
              source_directory_object => 'AA',
              source_file_name => 'current.257.686842909',
              destination_directory_object => 'BB',
              destination_file_name => 'control03.ctl');
        END;
        /
    SQL> exit;


AT this point, I went back to database I stopped earlier and started it. Since the database is started with spfile, third control file came in full use.

    MoidDBServer > export ORACLE_SID=DEVDB
    MoidDBServer > sqlplus / as sysdba
      
    SQL> startup
    ORACLE instance started.
  
    Total System Global Area  536870912 bytes
    Fixed Size                  1262812 bytes
    Variable Size             260049700 bytes
    Database Buffers          272629760 bytes
    Redo Buffers                2928640 bytes
    Database mounted.
    Database opened.
    SQL> show parameter control;
  
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    control_file_record_keep_time        integer     7
    control_files                        string      +DG1/DEVDB/controlfile/curre
                                                     nt.257.686842909, +DG2/DEVDB
                                                     /controlfile/current.256.68684
                                                     2909, /backup/DEVDB/CONTROLF
                                                     ILE/control03.ctl


Lastly, I cleaned up the temporary directory AA and BB from the second database we used earlier.


Moid