Oracle Database Online Migration to ASM

We were recently migrating one of our non-production databases to Oracle ASM. Previously, we used the Linux files system EXT4. We wanted the flexibility of Oracle ASM as well some of the IO performance gains. At a high level, we had already performed the following:

• Provision the disks for the software and Oracle ASM disk groups.
• Install the Grid Infrastructure for a standalone server (with the latest release update patch).
• Create the Oracle ASM diskgroups.

Database Migration

Now that we had Oracle ASM installed and working, we needed to come up with a plan to move the database files. Our database was a 19c pluggable database and about 5 terabytes in size. We knew we could perform most operations online with only a few that needed to have a small downtime. The tasks were split up as follows:

Online Operations

• Datafiles (by container)
• Tempfiles (add new/drop old)
• Redo Logs (add new/drop old)

Offline Operations

• Controlfiles
• Spfile

Examples

Datafiles

ALTER DATABASE MOVE DATAFILE '/u01/app/oradata/MYDB/system01.dbf' TO '+DATA01';

Tempfiles

ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA01' SIZE 1G AUTOEXTEND on NEXT 128M MAXSIZE UNLIMITED;

ALTER DATABASE TEMPFILE '/u01/app/oradata/MYDB/temp01.dbf' OFFLINE;

ALTER DATABASE TEMPFILE '/u01/app/oradata/MYDB/temp01.dbf' DROP INCLUDING DATAFILES;

Redo Logs

ALTER DATABASE ADD LOGFILE MEMBER '+DATA01' to GROUP 1;

ALTER DATABASE ADD LOGFILE MEMBER '+RECO01' to GROUP 1;

ALTER DATABASE CLEAR LOGFILE GROUP 1;

ALTER DATABASE DROP LOGIFLE MEMBER '/u01/app/oradata/MYDB/redo01a.dbf';

ALTER DATABASE DROP LOGIFLE MEMBER '/u01/app/oradata/MYDB/redo01b.dbf';

As a final check, you can issue the command:

SQL> alter database backup controlfile to trace; 

Now you can check the output to confirm all of your files have been moved to Oracle ASM. If any have been missed, you can go back and migrate those.

Finally, we can migrate the control files and update the spfile.

Controlfiles

1. Shutdown the database
2. startup nomount the database
3. rman nocatalog
   • connect target
   • restore controlfile to ‘+DATA01’ from ‘/u01/app/oradata/MYDB/cntrl01.dbf’;
4. Modify spfile to have updated control file and new db_create_online_log_dest1.
   • control_files=’+DATA01/MYDB/CONTROLFILE/current.558.1108761497′
   • db_create_online_log_dest_1=’+DATA01′
5. shutdown immediate
6. startup nomount
7. alter database mount;
8. alter database open;

In conclusion, Oracle makes it easy to migrate a database online. These are high level examples and one way of doing this. There might be slight variations to how you approach it yourself. You should always review the Oracle documentation.