How To Convert A UFS Database To ASM

BACKGROUND

The guide below was created to show how to migrate from a UFS filesystem based database to an ASM based one.

For the purposes of the example the following values are used:

Database Name: SID
Data ASM Disk Group: +DATA
Recovery Area ASM Disk Group: +REC

ASSUMPTIONS & PRE-REQUISITES

The guide assumes the following:

  • All required RAW devices have been pre-configured.
  • All required software has been downloaded from Oracle.
  • RMAN has been configured for use with the database.
  • The tasks are carried out by a qualified DBA.
  • A sound backup of the database being migrated has been taken…just in case.

STEP-BY-STEP GUIDE

1. Determine the names of the database files.

  • Save names of the control files, datafiles, and online redo logs for the database.
      sqlplus / as sysdba
      ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
      EXIT

2. Generate RMAN command file to undo ASM migration.
  • Should it be necessary to migrate your database back to non-ASM storage later, this process will be simplified if an RMAN command file is generated now with the necessary commands to perform this migration.
  • Even if making changes to the database later, such as adding datafiles, the command file created now will serve as a useful starting point.
  • There is an Oracle supplied script in the manual which is reproduced here:
      sqlplus / as sysdba
      set serveroutput on;
      declare
      cursor df is select file#, name from v$datafile;
      begin
        dbms_output.put_line('run');
        dbms_output.put_line('{');
        for dfrec in df loop
         dbms_output.put_line('set newname for datafile ' ||
              dfrec.file# || ' to ''' || dfrec.name ||''' ;');
         end loop;
         dbms_output.put_line('restore database;');
         dbms_output.put_line('switch all;');
         dbms_output.put_line('}');
      end;
      /
      exit

3. Create an ASM instance.
  • Use the database install software to create the ASM instance and an initial diskgroup.

4. Create additional ASM diskgroups.

  • Create an ASM diskgroup for the data if not created in the previous step.
      sqlplus / as sysdba
      CREATE DISKGROUP +DATA EXTERNAL REDUNDANCY DISK '/dev/rdsk/<device name 1>',...'/dev/rdsk/<device name n>';
      EXIT
  • Create an ASM diskgroup for the Flashback/Recovery area.
      sqlplus / as sysdba
      CREATE DISKGROUP +REC EXTERNAL REDUNDANCY DISK '/dev/rdsk/<device name 1>',...'/dev/rdsk/<device name n>'; 
      EXIT

5. Back up database files as copies to the ASM disk group.
  • Back up datafiles to disk as image copies using multiple channels to improve performance
      RMAN> run{
                allocate channel ch1 type disk;
                allocate channel ch2 type disk;
                allocate channel ch3 type disk;
                BACKUP AS COPY INCREMENTAL LEVEL 0  
                DATABASE FORMAT '+DATA' TAG '<meaningful tag name>';
                sql 'alter system archive log current;';
                release channel ch1;
                release channel ch2;
                release channel ch3;
                }

6. Minimise subsequent recovery time by taking an incremental backup.
  • The full backup may have taken a long time, depending upon the size of the database. Consequently, if there has been a lot of activity on the database during the time the backup was created, it may be beneficial to use the following procedure to create an incremental backup of the database afterwards. This will be used to refresh the copy with any changes since the migration process started.
      RMAN> connect target /
      RMAN> backup  incremental level 1 for recover of copy with tag '<meaningful tag name>' database;
      RMAN> recover copy of database with tag '<meaningful tag name>';

7. Create a copy of the spfileSID.ora in the new ASM disk group.
      sqlplus / as sysdba
      CREATE PFILE='/tmp/initSID.ora' FROM SPFILE;
      CREATE SPFILE='+DATA/spfileSID.ora' FROM PFILE='/tmp/initSID.ora';
      EXIT
  • Alternatively, use RMAN to do it like this:
      RMAN> connect target /
      RMAN> backup as backupset spfile;
      RMAN> restore spfile to "+DATA/spfileSID.ora";

8. Perform a consistent shutdown of the database instance.
      sqlplus / as sysdba
      SHUTDOWN IMMEDIATE;
      EXIT

9. Back up the current control file and redo logs to a non-ASM location, in case there is a need to return to UFS later.
  • In order to make the option of easily returning the database to non-ASM storage later, make copies of the current control file and all online logs like this:
  • Back up the current control file to a non-ASM location:
      RMAN> connect target /
      RMAN> startup mount
      RMAN> backup as copy current controlfile 
            format '/<ufs file location>/pre_migration_controlfile_backup.ctl';            
      RMAN> shutdown immediate

10. Back up the online redo logs to a non-ASM location:
  • As RMAN cannot be used to back up online redo logs, use the operating-system commands to copy them.
      cd <redo log location>
      mkdir <redo log copy location>
      cp redo*.dbf <redo log copy location>

11. Create an initSID.ora specifying the location of the new spfileSID.ora
      cd $ORACLE_HOME/dbs
      rm initSID.ora link                       # If it exists
      mv spfileSID.ora OLD_spfileSID            # If it exists       
      Create a new initSID.ora containing the text SPFILE=+DATA/spfileSIDora

12. Start the database instance in NOMOUNT mode using the new spfileSID.ora via the pfile initSID.ora created in the previous step.
      sqlplus / as sysdba
      STARTUP NOMOUNT PFILE='$ORACLE_HOME/dbs/initSID.ora';
      SHOW PARAMETER PFILE                      # This is to confirm the ASM spfile is being used
      EXIT

13. Migrate the control files to ASM.
      sqlplus / as sysdba
      ALTER SYSTEM SET control_files='+DATA/ctrl_01.dbf','+REC/ctrl_02.dbf' scope=spfile sid='*';
      EXIT

14. if required, create a flash recovery area (FRA).
      sqlplus / as sysdba
      ALTER SYSTEM SET db_recovery_file_dest_size=<appropriate size> SID='*';
      ALTER SYSTEM SET db_recovery_file_dest='+REC' SID='*';
      EXIT

15. restart the database so that the changed parameters take effect.
  • Use RMAN to create the new control files in ASM.
    • NOTE: The control file to restore from is one of the original database control files.
      RMAN> connect target /
      RMAN> shutdown immediate;
      RMAN> startup nomount;                     # This is now using the ASM spfile (via the pfile)
      RMAN> restore controlfile from '<original control file location>/ctrl_01.ct1';
      RMAN> alter database mount;
      RMAN> switch database to copy;
      RMAN> recover database;

16. Migrate the tempfiles to ASM.
  • Identify the tempfiles.
      sqlplus / as sysdba
      SELECT file#,name FROM v$tempfile ORDER BY 1;
      EXIT
  • Migrate the tempfiles.
    • NOTE: A SET NEWNAME statement is needed for each tempfile. The tempfile numbers may not start from 1 or be sequential so use the file# from the previous step. The new tempfiles will be created when the database is next opened.
      RMAN> connect target /
      RMAN> run {
                      set newname for tempfile 1 to '+DATA'
                      set newname for tempfile 2 to '+DATA';
                       ...
                      switch tempfile all;
                }

17. Enable the FRA on ASM, if required.
      sqlplus / as sysdba
      ALTER DATABASE FLASHBACK ON;
      EXIT

18. Enable block change tracking in ASM, if required.
      sqlplus / as sysdba
      ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '+DATA';
      EXIT

19. Open the database.
      sqlplus / as sysdba
      ALTER DATABASE OPEN;
      EXIT

20. Migrate the online redo logs.
  • Migrating the online redo logs is performed by adding new log group members in ASM, and then dropping the old members from the original UFS location.
  • Below is an Oracle supplied script from the manual:
      sqlplus / as sysdba

      ALTER SYSTEM ARCHIVE LOG ALL;

      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 || ' ''+<disk group 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 || ' ''+<disk group 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;
      /

      SELECT L.GROUP#, L.SEQUENCE#, L.MEMBERS, L.STATUS , substr(LF.MEMBER,1,50) MEMBER
      FROM V$LOG L, V$LOGFILE LF
      WHERE L.GROUP#=LF.GROUP#
      /

      EXIT
© copyright 2001-2014 ABCdba.com | all rights reserved