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;

ASM Views & Statements

ASM Views & Statements 

• V$ASM_DISK

• V$ASM_DISKGROUP

• V$ASM_DISK_STAT

• V$ASM_DISKGROUP_STAT

• V$ASM_OPERATION

• V$ASM_CLIENT

• V$ASM_FILE

• V$ASM_ALIAS

• V$ASM_TEMPLATE

• V$ASM_VOLUME

• V$ASM_VOLUME_STAT

• V$ASM_ATTRIBUTE

• V$ASM_DISK_IOSTAT

• V$ASM_FILESYSTEM

• V$ASM_ACFSVOLUMES

• V$ASM_ACFSSNAPSHOTS

Disk Group Information

set pages 40000 lines 120

col NAME for a15

select GROUP_NUMBER DG#, name, ALLOCATION_UNIT_SIZE AU_SZ, STATE,

TYPE, TOTAL_MB, FREE_MB, OFFLINE_DISKS from v$asm_diskgroup;

ASM Disk Information

set pages 4000 lines 120

col PATH for a30

select DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,

PATH FROM V$ASM_DISK;

Combined ASM Disk and ASM Diskgroup information

col PATH for a15

col DG_NAME for a15

col DG_STATE for a10

col FAILGROUP for a10

select dg.name dg_name, dg.state dg_state, dg.type, d.disk_number dsk_no,

d.path, d.mount_status, d.FAILGROUP, d.state 

from v$asm_diskgroup dg, v$asm_disk d

where dg.group_number=d.group_number

order by dg_name, dsk_no;

Monitoring ASM disk operations

select GROUP_NUMBER, OPERATION, STATE, ACTUAL, SOFAR, EST_MINUTES 

from v$asm_operation;

Viewing Disk Group Attribute

SELECT dg.name AS diskgroup, SUBSTR(a.name,1,18) AS name, SUBSTR(a.value,1,24) AS value, read_only 

FROM V$ASM_DISKGROUP dg,V$ASM_ATTRIBUTE a 

WHERE dg.name = 'DATA'

AND dg.group_number = a.group_number;

Viewing Disk Group Clients

SELECT dg.name AS diskgroup, SUBSTR(c.instance_name,1,12) AS instance,SUBSTR(c.db_name,1,12) AS dbname, SUBSTR(c.SOFTWARE_VERSION,1,12) AS software,SUBSTR(c.COMPATIBLE_VERSION,1,12) AS compatible 

FROM V$ASM_DISKGROUP dg, V$ASM_CLIENT c  

WHERE dg.group_number = c.group_number;

Viewing Volume and ACFS Information

SELECT dg.name AS diskgroup, v.volume_name, v.volume_device, v.mountpath 

FROM V$ASM_DISKGROUP dg, V$ASM_VOLUME v 

WHERE dg.group_number = v.group_number and dg.name = 'DATA';

SELECT dg.name AS diskgroup, v.volume_name, v.bytes_read, v.bytes_written

FROM V$ASM_DISKGROUP dg, V$ASM_VOLUME_STAT v 

WHERE dg.group_number = v.group_number and dg.name = 'DATA';

SELECT volume_name,size_mb,state,usage,volume_device,mountpath   

FROM v$asm_volume;

SELECT volume_name,reads,writes,read_errs,bytes_read,bytes_written   

FROM v$asm_volume_stat;

SELECT fs_name, vol_device, primary_vol, total_mb, free_mb 

FROM V$ASM_ACFSVOLUMES;

SELECT fs_name, available_time, block_size, state, corrupt 

FROM V$ASM_FILESYSTEM;

select substr(fs_name,1,20) FS,vol_device device,substr (snap_name,1,30) SNAP_NAME,create_time 

from v$asm_acfssnapshots;

ASM Cluster File System (ACFS)

Automatic Storage Management Cluster File System (ACFS)

When ASM was first introduced in Oracle 10g, it was strictly intended for managing Oracle database-related files only. However, an ASM Cluster File System (ACFS), a new feature in Oracle 11g R2 Grid Infrastructure, extends ASM's capabilities significantly to manage all types of data.

Oracle ACFS is designed as a general purpose, standalone, and cluster-wide filesystem solution, which now supports the data that is maintained outside the Oracle database. Apart from Oracle database datafiles, ACFS can also be used to store Oracle binaries, application files, executables, database trace and log files, BFILEs, video, audio, and other configuration files. The following diagram illustrates the Oracle ASM storage layers:

Practical Usages of ACFS

•        Oracle database installation binaries

-   RAC or single instance ORACLE_HOME directories

•        Database exports

•        Database logs and trace files (Automatic Diagnostic Repository (ADR))

•        File based database objects

-   UTL_FILE_DIR location

-   Directories used by EXTPROC routines

-   Directory objects for external BFILES and external tables

•        Application logs & Report output

•        Middle-tier shared filesystem for Oracle Applications.

Oracle ACFS drivers

The following mandatory drivers are installed as part of the Grid Infrastructure installation and must be loaded into the operating system  to support ACFS and ADVM functionality:

• oracleacfs (oracleacfs.ko): The ACFS filesystem module manages all ACFS filesystem operations 
•  oracleavdm (oracleavdm.ko): The AVDM module provides capabilities to directly interface with the filesystem 
•  oracleoks (oracleoks.ko): The kernel services module provides memory management, and lock and cluster synchronization

To utilize ACFS the ACFS drivers and modules must be loaded into the operating system

•  For Grid Infrastructure installations in a cluster the drivers and modules are loaded automatically
• Single instance Grid Infrastructure installations the ACFS Drivers/Modules must be loaded manually

Load ACFS Drivers/Modules Manually

          -   Log into the host operating system as root

          -   <GRIDHOME>/bin/acfsload start

          -   Place the load command into /etc/rc.d/rc.local for the load to be persistent across node restarts

ACFS Deployment

There are two types of ACFS filesystems: CRS Managed ACFS and General Purpose ACFS. CRS Managed ACFS filesystems have associated Oracle Clusterware resources and generally have defined interdependencies with other Oracle Clusterware resources; e.g., database, ASM disk group, etc. CRS Managed ACFS is specifically beneficial for shared ORACLE_HOME filesystems. General Purpose ACFS are general-purpose filesystems that are completely transparent to Oracle Clusterware and its resources.

Creating an ACFS filesystem for General Purpose FileSystem using ASMCA

•        Run $GRID_HOME/bin/asmca at a command prompt from the console. When ASMCA is initiated, it will take you to the main screen where you need to click on the ASM Cluster File Systems tab and then click on the Create button

•        On the Create ASM Cluster File System screen, select any existing volume from the drop-down list on which you need to configure the ACFS filesystem. In addition, there is also an option available in the drop-down list to create a new volume.

•        You have the option to create a filesystem to use either for Oracle Binaries (shared Oracle Home) or a General Purpose File System (GPFS). When you choose a filesystem type for GPFS, the filesystem is created under $ORACLE_BASE/acfsmounts (non-CRS ORACLE_BASE).

Creating an ACFS filesystem with ASMCMD

ASMCMD is a command-line utility and another way to create and manage an Oracle ACFS filesystem. When you decide to create the ACFS filesystem using ASMCMD, you need to complete the following steps:

•        Before we start creating the ACFS with ASMCMD, ensure the ADVM is already configured. If no ADVM was configured before, then create one.

•        Create a filesystem using the operating system-specific filesystem creation command.

•        Map the mount point through the Oracle ACFS mount registry.

•        Mount the specific mount point using the operating system-specific command, for example, the mount command.

Create a new volume

asmcmd volcreate -G DATA_DG -s 1g advm_vg2

Create a new ACFS filesystem

mkfs -t acfs -b 4k /dev/asm/advm_vg2-74

Mount the filesystem

mount -t acfs /dev/asm/advm_vg2-74 /d01/ahfathi

ACFS Mount Registry

An ACFS Mount Registry is used to provide a persistent entry for each General Purpose ACFS filesystem that needs to be mounted after a reboot. This ACFS Mount Registry is very similar to the /etc/fstab on

Linux, The ACFS Mount Registry can be probed, using the acfsutil command, to obtain filesystem, mount, and file information.

/sbin/acfsutil registry -a -f -n node1,node2 /dev/asm/advm_vg2-74 /d01/ahfathi 

/sbin/acfsutil size +2G /d01/ahfathi 

/sbin/acfsutil info fs /d01/ahfathi