Using RMAN To Migrate a Database Into ASM

Using RMAN To Migrate a Database Into ASM

The methods to migrating non-ASM database to ASM:

      -  ASM migration using Data Guard physical standby

• Use this method if your requirement is to minimize downtime during the migration. It is possible to reduce total downtime to just seconds by using the best practices described in the white paper

      - ASM migration using Rman

• A simpler approach, but one that can result in downtime measured in minutes to hours, depending on the method used for migration

-  ASM migration using DBMS_FILE_TRANSFER

One of the ways to migrate a database to ASM storage is to use Rman to make a “Backup as Copy” into ASM and then switching the database to the copy.

Backup Database Into ASM

The first step is to create a backup inside ASM, we will use the following script to do that

# rman target /

run

{allocate channel ch1 type disk;

allocate channel ch2 type disk;

allocate channel ch3 type disk;

allocate channel ch4 type disk;

BACKUP AS COPY INCREMENTAL LEVEL 0 DATABASE FORMAT '+DATA_DG' TAG 'ORA_ASM_MIGRATION;

}

Or BACKUP AS COPY DATABASE FORMAT '+DATA_DG';

Once the backup finished we can check that the datafiles were copied to the DATA_DG ASM diskgroup

# asmcmd ls -l DATA_DG/ORCL/datafile

Type Redund Striped Time Sys Name

DATAFILE MIRROR COARSE FEB 12 12:00:00 Y SYSAUX.257.678198083

DATAFILE MIRROR COARSE FEB 12 12:00:00 Y SYSTEM.258.678198083

DATAFILE MIRROR COARSE FEB 12 12:00:00 Y UNDOTBS1.256.678198083

DATAFILE MIRROR COARSE FEB 12 12:00:00 Y USERS.259.678198083

Spfile Backup into ASM

The next step is to make a backup of the spfile and to restore it into ASM

# rman target /

run

{ BACKUP AS BACKUPSET SPFILE;

RESTORE SPFILE TO '+DATA_DG/ORCL/spfileorcl.ora';

}

# asmcmd ls -l DATA_DG/ORCL/spfileorcl.ora

Type Redund Striped Time Sys Name

  N spfileorcl.ora

Prepare Pfile for the ASM Database

Next step is to prepare a parameter file “initorcl.ora” that will point to the spfile inside ASM

# cd $ORACLE_HOME/dbs

# echo "SPFILE=+DATA_DG/ORCL/PARAMETERFILE/spfilesati.ora" > initorcl.ora

# ls -l initorcl.ora

-rw-r--r-- 1 oracle dba 51 Feb 7 13:35 initorcl.ora

# cat initsati.ora

SPFILE=+DATA_DG/ORCL/PARAMETERFILE/spfileorcl.ora

Start the database in NOMOUNT mode

On the next step we start the database in nomount mode using the pfile that points into the ASM spfile

sqlplus / as sysdba

SQL> startup nomount pfile='/u01/app/oracle/10g_db/dbs/initorcl.ora'

Change Parameters on Spfile to point to ASM

On this step we will prepare the spfile to migrate the controlfile into ASM, an we will set recovery area size and destination, then we will shutdown the database

sqlplus / as sysdba

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

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=2g scope=both;

SQL> alter system set DB_RECOVERY_FILE_DEST='+FRA_DG' scope=both;

SQL> shutdown immediate;

Move the controlfiles into ASM

The controlfiles will be restored to the location we specified on the previous step using the parameter control_files.

sqlplus / as sysdba

SQL> startup nomount pfile='/u01/app/oracle/10g_db/dbs/initorcl.ora';

# rman target /

RMAN> restore controlfile from '/u01/app/oracle/10g_db/dbs/c-1728841273-20090207-04';

Switch the Database from File System to ASM

On this step we will actually point the database to switch from the datafiles located on file system to the datafiles located inside ASM.

From within the same Rman session we were working on the previous step we mount the database and we switch to the ASM datafiles.

RMAN> mount database;

RMAN> switch database to copy;

Migrate the Temporary Datafiles to ASM

RMAN>

run { set newname for tempfile 1 to '+DATA_DG';

switch tempfile all;

}

Move Flashback logs into flash recovery Area

SQL> ALTER DATABASE FLASHBACK OFF;

SQL> ALTER DATABASE FLASHBACK ON;

Move RMAN Change Tracking File Into ASM

SQL> alter database disable block change tracking;

SQL> alter database enable block change tracking using file '+DATA_DG';

Open the Database and Move Online Logs Into ASM

SQL> alter database open;

SQL> select member from v$logfile;

SQL> @movelogs.sql

-- movelogs.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 || ' ''+DATADGNR'' 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 || ' ''+DATADGNR'' 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;

/

SQL> select member from v$logfile;