NON-ASM TO ASM

Overview

Automatic Storage Management (ASM) was introduced in Oracle10g Release 1 and is used to alleviate the DBA from having to manage individual files and drives. ASM is built into the Oracle kernel and provides the DBA with a way to manage thousands of disk drives 24x7 for both single and clustered instances of Oracle. Essentially, ASM is a file system / volume manager for all Oracle physical database files (datafiles, online redo logs, controlfiles, archived redo logs, RMAN backupsets, and SPFILEs). All of the database files (and directories) to be used for Oracle will be contained in a disk group. ASM automatically performs load balancing in parallel across all available disk drives to prevent hot spots and maximize performance, even with rapidly changing data usage patterns.

Configuring an ASM environment (an ASM instance) is a straightforward process and can be done through the Database Configuration Assistant (DBCA) or manually (see Manually Creating an ASM Instance). Once the ASM instance is configured on a node and an ASM Disk Group is created, any database that resides on that node can start taking advantage of it. For example, consider an ASM instance named +ASM with an ASM disk group named TESTDB_DATA1. Creating a tablespace where the datafile will reside in ASM is as easy as:

SQL> CREATE TABLESPACE users2 DATAFILE '+TESTDB_DATA1' SIZE 100M;

Tablespace created.

SQL> SELECT tablespace_name, file_name FROM dba_data_files WHERE tablespace_name = 'USERS2';

TABLESPACE_NAME FILE_NAME

--------------- --------------------------------------------------

USERS2          +TESTDB_DATA1/testdb/datafile/users2.268.598475429

Given the SQL statement above, a new datafile will be created using Oracle Managed Files (OMF) in an ASM disk group named TESTDB_DATA1.

But, what if you already have an existing Oracle database which stores its database files using the local file system on the node you just configured ASM on and now want to relocate the entire database to be stored in ASM? Well, as with most file management tasks that involve ASM, it's RMAN to the rescue!

In this article, I will explain the steps necessary to migrate an existing Oracle database stored on the local file system to ASM. This will include all datafiles, tempfiles, online redo logfiles, controlfiles, and all flash recovery area files. I will then, within a follow-up section, explain how the process works in reverse - migrating a database stored in ASM to a local file system.

Current Configuration

The testing environment that I will be using for this article is best described in the following illustration and table of values:

Migrating Oracle Database from Local File System to ASM

The following query lists database files as they exist on the local file system for the TESTDB database. All of the files listed in this query will be relocated from the local file system to ASM:

$ ORACLE_SID=TESTDB; export ORACLE_SID

$ sqlplus "/ as sysdba"

SQL> @dba_files_all

Tablespace Name /

File Class            Filename                                                              File Size Auto        Next            Max

--------------------- --------------------------------------------------------------- --------------- ---- ----------- --------------

APEX22                /u02/oradata/TESTDB/datafile/o1_mf_apex22_2ft4eswu_.dbf             104,857,600 NO             0              0

EXAMPLE               /u02/oradata/TESTDB/datafile/o1_mf_example_2fb4ccw2_.dbf            157,286,400 YES      655,360 34,359,721,984

FLOW_1                /u02/oradata/TESTDB/datafile/o1_mf_flow_1_2fb4cegw_.dbf              52,494,336 NO             0              0

SYSAUX                /u02/oradata/TESTDB/datafile/o1_mf_sysaux_2fb4cb7z_.dbf             419,430,400 YES   10,485,760 34,359,721,984

SYSTEM                /u02/oradata/TESTDB/datafile/o1_mf_system_2fb4b8s2_.dbf             608,174,080 YES   10,485,760 34,359,721,984

TEMP                  /u02/oradata/TESTDB/datafile/o1_mf_temp_2g17lvcq_.tmp               536,870,912 YES  262,144,000 34,359,721,984

UNDOTBS1              /u02/oradata/TESTDB/datafile/o1_mf_undotbs1_2fb4c2wf_.dbf           209,715,200 YES    5,242,880 34,359,721,984

USERS                 /u02/oradata/TESTDB/datafile/o1_mf_users_2fb4cqf4_.dbf            2,382,888,960 YES    1,310,720 34,359,721,984

[ CONTROL FILE    ]   /u02/oradata/TESTDB/controlfile/o1_mf_8du3s3er_.ctl

[ CONTROL FILE    ]   /u02/oradata/TESTDB/controlfile/o1_mf_y2is93je_.ctl

[ ONLINE REDO LOG ]   /u02/flash_recovery_area/TESTDB/onlinelog/o1_mf_1_2g1g6bq0_.log     262,144,000

[ ONLINE REDO LOG ]   /u02/flash_recovery_area/TESTDB/onlinelog/o1_mf_2_2g1gdgn1_.log     262,144,000

[ ONLINE REDO LOG ]   /u02/flash_recovery_area/TESTDB/onlinelog/o1_mf_3_2g1ghz8z_.log     262,144,000

[ ONLINE REDO LOG ]   /u02/oradata/TESTDB/onlinelog/o1_mf_1_2g1g61bm_.log                 262,144,000

[ ONLINE REDO LOG ]   /u02/oradata/TESTDB/onlinelog/o1_mf_2_2g1gd4pr_.log                 262,144,000

[ ONLINE REDO LOG ]   /u02/oradata/TESTDB/onlinelog/o1_mf_3_2g1ghs0t_.log                 262,144,000

                                                                                     ---------------

sum                                                                                     6,044,581,888

16 rows selected.

Also note that the target database uses an SPFILE on the local file system:

   $ORACLE_HOME/dbs/spfileTESTDB.ora

Use the following steps to fully migrate an existing Oracle database from a local file system to ASM:

• 1. With the target database open, edit the initialization parameter control_files and db_create_file_dest to point to the ASM disk group +TESTDB_DATA1. Also configure db_recovery_file_dest to point to the ASM disk group +FLASH_RECOVERY_AREA:

     1. SQL> ALTER SYSTEM SET control_files='+TESTDB_DATA1' SCOPE=spfile;

     2. System altered.

     3. SQL> ALTER SYSTEM SET db_create_file_dest='+TESTDB_DATA1' SCOPE=spfile;

     4. System altered.

     5. SQL> ALTER SYSTEM SET db_recovery_file_dest='+FLASH_RECOVERY_AREA' SCOPE=spfile;

     6. System altered.

  2. Startup the target database in NOMOUNT mode:

• • 1. SQL> SHUTDOWN IMMEDIATE

    2. Database closed.

    3. Database dismounted.

    4. ORACLE instance shut down.

    5. SQL> STARTUP NOMOUNT

    6. ORACLE instance started.

    7. Total System Global Area  285212672 bytes

    8. Fixed Size                  1260420 bytes

    9. Variable Size             171967612 bytes

    10. Database Buffers          109051904 bytes

    11. Redo Buffers                2932736 bytes

• 3. From an RMAN session, copy one of your controlfiles from the local file system to its new location in ASM. The new controlfile will be copied to the value specified in the initialization parameter control_files:

• • 1. RMAN> RESTORE CONTROLFILE FROM '/u02/oradata/TESTDB/controlfile/o1_mf_8du3s3er_.ctl';

    2. Starting restore at 14-AUG-06

    3. using channel ORA_DISK_1

    4. channel ORA_DISK_1: copied control file copy

    5. output filename=+TESTDB_DATA1/testdb/controlfile/backup.268.598481391

    6. Finished restore at 14-AUG-06

• 4. From an RMAN or SQL*Plus session, mount the database. This will mount the database using the controlfile stored in ASM:

• • 1. RMAN> ALTER DATABASE MOUNT;

    2. database mounted

    3. released channel: ORA_DISK_1

• 5. From an RMAN session, copy the database files from the local file system to ASM:

• • 1. RMAN> BACKUP AS COPY DATABASE FORMAT '+TESTDB_DATA1';

    2. Starting backup at 14-AUG-06

    3. using target database control file instead of recovery catalog

    4. allocated channel: ORA_DISK_1

    5. channel ORA_DISK_1: sid=158 devtype=DISK

    6. channel ORA_DISK_1: starting datafile copy

    7. input datafile fno=00005 name=/u02/oradata/TESTDB/datafile/o1_mf_users_2fb4cqf4_.dbf

    8. output filename=+TESTDB_DATA1/testdb/datafile/users.270.598481673 tag=TAG20060814T205432 recid=36 stamp=598482095

    9. channel ORA_DISK_1: datafile copy complete, elapsed time: 00:07:06

    10. channel ORA_DISK_1: starting datafile copy

    11. input datafile fno=00001 name=/u02/oradata/TESTDB/datafile/o1_mf_system_2fb4b8s2_.dbf

    12. output filename=+TESTDB_DATA1/testdb/datafile/system.269.598482099 tag=TAG20060814T205432 recid=37 stamp=598482206

    13. channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:55

    14. channel ORA_DISK_1: starting datafile copy

    15. input datafile fno=00003 name=/u02/oradata/TESTDB/datafile/o1_mf_sysaux_2fb4cb7z_.dbf

    16. output filename=+TESTDB_DATA1/testdb/datafile/sysaux.267.598482213 tag=TAG20060814T205432 recid=38 stamp=598482292

    17. channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:25

    18. channel ORA_DISK_1: starting datafile copy

    19. input datafile fno=00002 name=/u02/oradata/TESTDB/datafile/o1_mf_undotbs1_2fb4c2wf_.dbf

    20. output filename=+TESTDB_DATA1/testdb/datafile/undotbs1.256.598482299 tag=TAG20060814T205432 recid=39 stamp=598482340

    21. channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45

    22. channel ORA_DISK_1: starting datafile copy

    23. input datafile fno=00004 name=/u02/oradata/TESTDB/datafile/o1_mf_example_2fb4ccw2_.dbf

    24. output filename=+TESTDB_DATA1/testdb/datafile/example.264.598482345 tag=TAG20060814T205432 recid=40 stamp=598482374

    25. channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35

    26. channel ORA_DISK_1: starting datafile copy

    27. input datafile fno=00006 name=/u02/oradata/TESTDB/datafile/o1_mf_apex22_2ft4eswu_.dbf

    28. output filename=+TESTDB_DATA1/testdb/datafile/apex22.263.598482381 tag=TAG20060814T205432 recid=41 stamp=598482399

    29. channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25

    30. channel ORA_DISK_1: starting datafile copy

    31. input datafile fno=00007 name=/u02/oradata/TESTDB/datafile/o1_mf_flow_1_2fb4cegw_.dbf

    32. output filename=+TESTDB_DATA1/testdb/datafile/flow_1.262.598482405 tag=TAG20060814T205432 recid=42 stamp=598482415

    33. channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

    34. channel ORA_DISK_1: starting datafile copy

    35. copying current control file

    36. output filename=+TESTDB_DATA1/testdb/controlfile/backup.261.598482421 tag=TAG20060814T205432 recid=43 stamp=598482423

    37. channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

    38. channel ORA_DISK_1: starting full datafile backupset

    39. channel ORA_DISK_1: specifying datafile(s) in backupset

    40. including current SPFILE in backupset

    41. channel ORA_DISK_1: starting piece 1 at 14-AUG-06

    42. channel ORA_DISK_1: finished piece 1 at 14-AUG-06

    43. piece handle=+TESTDB_DATA1/testdb/backupset/2006_08_14/nnsnf0_tag20060814t205432_0.260.598482425 tag=TAG20060814T205432 comment=NONE

    44. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02

    45. Finished backup at 14-AUG-06

• 6. From an RMAN session, update the control file / data dictionary so that all database files point to the RMAN copy made in ASM:

• • 1. RMAN> SWITCH DATABASE TO COPY;

    2. datafile 1 switched to datafile copy "+TESTDB_DATA1/testdb/datafile/system.269.598482099"

    3. datafile 2 switched to datafile copy "+TESTDB_DATA1/testdb/datafile/undotbs1.256.598482299"

    4. datafile 3 switched to datafile copy "+TESTDB_DATA1/testdb/datafile/sysaux.267.598482213"

    5. datafile 4 switched to datafile copy "+TESTDB_DATA1/testdb/datafile/example.264.598482345"

    6. datafile 5 switched to datafile copy "+TESTDB_DATA1/testdb/datafile/users.270.598481673"

    7. datafile 6 switched to datafile copy "+TESTDB_DATA1/testdb/datafile/apex22.263.598482381"

    8. datafile 7 switched to datafile copy "+TESTDB_DATA1/testdb/datafile/flow_1.262.598482405"

• 7. From a SQL*Plus session, perform incomplete recovery and open the database using the RESETLOGS option:

• • 1. SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

    2. ORA-00279: change 7937583 generated at 08/14/2006 20:33:55 needed for thread 1

    3. ORA-00289: suggestion : +FLASH_RECOVERY_AREA

    4. ORA-00280: change 7937583 for thread 1 is in sequence #36

    5. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

    6. CANCEL

    7. Media recovery cancelled.

    8. SQL> ALTER DATABASE OPEN RESETLOGS;

    9. Database altered.

• 8. From a SQL*Plus session, re-create any tempfiles that are still currently on the local file system to ASM. This is done by simply dropping the tempfiles from the local file system and re-creating them in ASM. This example relies on the initialization parameter db_create_file_dest=+TESTDB_DATA1:

• • 1. SQL> select tablespace_name, file_name, bytes from dba_temp_files;

    2. TABLESPACE_NAME FILE_NAME                                                 BYTES

    3. --------------- ----------------------------------------------------- ---------

    4. TEMP            /u02/oradata/TESTDB/datafile/o1_mf_temp_2g17lvcq_.tmp 536870912

    5. SQL> alter database tempfile

    6.  2  '/u02/oradata/TESTDB/datafile/o1_mf_temp_2g17lvcq_.tmp'

    7.  3  drop including datafiles;

    8. Database altered.

    9. SQL> alter tablespace temp add tempfile size 512m

    10.  2  autoextend on next 250m maxsize unlimited;

    11. Tablespace altered.

    12. SQL> select tablespace_name, file_name, bytes from dba_temp_files;

    13. TABLESPACE_NAME FILE_NAME                                            BYTES

    14. --------------- ------------------------------------------------ ---------

    15. TEMP            +TESTDB_DATA1/testdb/tempfile/temp.261.598485663 536870912