"Migrating Databases to Oracle ASM: A Step-by-Step Guide"

Why Move to Oracle ASM?

As a Senior Database Administrator, I often face the dilemma of scaling storage, optimizing I/O, and simplifying backup operations for large production databases. Oracle Automatic Storage Management (ASM) addresses these pain points by abstracting raw disks into a logical pool, delivering performance, reliability, and ease of maintenance. In this post, I walk through the exact steps you need to migrate an existing database to ASM, covering environment prep, ASM configuration, migration tools, validation, and post‑migration tuning.

1. Planning and Prerequisites

1.1 Hardware and OS Checks

Verify that all target servers run a supported Oracle Database version (≥12c). ASM is not supported on 10g or earlier.
Check that the storage subsystem meets Oracle ASM best practices – typically at least two disks per ASM disk group for striping and mirroring.
Confirm that the OS has the required packages and that the oracleasm and oracleasmconfig utilities are installed.

1.2 Licensing and Features

ASM is included in the Oracle Enterprise Edition license. Ensure your environment is correctly licensed before proceeding.
If you plan to use Data Guard or RMAN for disaster recovery and backups, ensure that all target nodes have matching ASM configuration.

1.3 Backup Strategy

Before making any changes, take a full database backup. I recommend using RMAN for this task:



RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

RMAN> QUIT;

2. Preparing the ASM Environment

2.1 Installing the ASM Instance

On each node where you want ASM to reside, run:



srvctl add asminst -a $ASM_ADMIN -i $ASM_NAME

Replace $ASM_ADMIN with the ASM administrative group (e.g., asmadmin) and $ASM_NAME with the instance name (e.g., asm01).

2.2 Defining Disk Groups

After starting the ASM instance, create the necessary disk groups:



srvctl config asminst -a $ASM_ADMIN -i $ASM_NAME

Now create a disk group:



asmcmd mkdiskgroup "DATA" "/dev/sd[ab]" -m 2 -s 1T

DATA is the disk group name.
Adjust the disk paths and size based on your hardware.
-m 2 for mirror, -s for stripe count.

3. Migrating the Database to ASM

3.1 Identify Target Tablespaces

Use the following query to list all tablespaces that will be moved:



SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE STATUS='ONLINE';

3.2 Using RMAN to Move Tablespaces

RMAN simplifies the migration process with the MOVE command. Here’s the typical workflow:



RMAN> CONNECT TARGET /;

RMAN> CONFIGURE CHANNEL (TYPE DISK) SIZE 1G;

RMAN> MOVE TABLESPACE "USERS" TO "DATA";

RMAN> QUIT;

Repeat for each tablespace. For large tablespaces, you may want to set a larger channel size or run multiple channels in parallel:



RMAN> CONFIGURE CHANNEL (TYPE DISK) SIZE 2G;

RMAN> CONFIGURE CHANNEL (TYPE DISK) SIZE 2G;

RMAN> MOVE TABLESPACE "USERS" TO "DATA";

3.3 Moving Data Files Directly

If you prefer manual control, you can stop the database, copy the data files to the ASM disk group, and update the init.ora to point to the new location:

Stop the database: srvctl stop database -d <db_name>
Copy files: cp /u01/app/oracle/oradata/<db_name>/* /u01/app/oracle/asm/data/<db_name>/*
Update the initialization parameters: ALTER SYSTEM SET DATAFILE_PATH='ASM:<diskgroup>:<path>' SCOPE=SPFILE;
Restart the database.

3.4 Validating the Migration

After moving, run:



SQL> SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES;

SQL> SELECT COUNT(*) FROM <db_name>..<table_name>;

Cross‑check the sizes and row counts to ensure data integrity.

4. Post‑Migration Tasks

4.1 Adjusting Storage Parameters

ASM allows you to specify STRIPE and MIRROR attributes. After migration, evaluate if the current stripe count suits your I/O workload.

4.2 Enabling Automatic Storage Management Features

Set the ASM_INIT_PARAMS to enable automatic log file placement.
Consider enabling ASM_SAFETYSLOT for extra protection.

4.3 Integrating with Data Guard

If you use Data Guard, ensure that the ASM instance is mirrored on the standby. Use:



RMAN> CONFIGURE CHANNEL (TYPE DISK) SIZE 1G;

RMAN> STARTUP MOUNT;

RMAN> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;

RMAN> QUIT;

4.4 Automating Backups with RMAN and ASM

Configure an RMAN script that references ASM paths:



RUN {

SET UNTIL TIME "<time>";

BACKUP AS BACKUPSET DATABASE PLUS ARCHIVELOG;

};

5. Performance Tuning on ASM

5.1 Monitoring I/O

Use ASM View V$ASM_DISKGROUP and V$ASM_FILE to monitor I/O patterns. For example:



SELECT * FROM V$ASM_DISKGROUP;

SELECT * FROM V$ASM_FILE WHERE DISKGROUP_NAME='DATA';

5.2 Adjusting Striping

High‑throughput workloads benefit from increased stripe count. Use:



ALTER DISKGROUP "DATA" SET STRIPE = 8;

ALTER DISKGROUP "DATA" SET MIRROR = 2;

5.3 Leveraging Oracle RMAN Compression

Enable compression for backup traffic:



RMAN> CONFIGURE COMPRESSION ALGORITHM USING "GZIP";

RMAN> CONFIGURE COMPRESSION LEVEL = 4;

6. Common Pitfalls and Troubleshooting

Data File Location Errors: Verify DATAFILE_PATH and that the ASM disk group is online.
Insufficient Striping: Monitor V$ASM_FILE for disk contention; adjust striping accordingly.
Backup Failures: Check ASM permissions (oracleasmadmin group) and ensure the SPFILE points to the correct ASM path.

7. Next Steps

After migration, schedule regular performance reviews. Integrate ASM metrics into your monitoring stack (e.g., Prometheus, Grafana) and set alerts for disk usage thresholds. Keep your database tuned, backups automated, and always test your disaster recovery plan.

Ready to boost your database reliability and simplify storage? Subscribe to our newsletter for more DBA insights and follow me on LinkedIn for real‑time tips and industry updates.

Person-IT