Steps to Migrate Oracle Database From ASM to Filesystem

INTRODUCTION:

       Here I am explaining step by step procedure, how we can migrate/Move oracle database from ASM to Filesystem.

Backup:

  - Make sure you have level0 backup + control file backup;
  - This is for to be in safe side

Start database with ASM

    a) Note down the location of control file by logging to database
        SQL> show parameter control
             +DATA/TEST/CONTROLFILE/Current.310.864775821

Make Changes in the init file:

    a) Create pfile from spfile
        SQL> create pfile='/oracle/oradata/test/initTEST.ora' from spfile;
    b) Change required parameters in the init parameter file:
        *.control_files='/oracle/oradata/test/control_01.ctl','/oracle/oradata/test/control_02.ctl'
        *.db_create_file_dest='/oracle/oradata/test/dest'
        *.db_recovery_file_dest='/oracle/oradata/test/fra'

Shutdown the database

    SQL> shutdown immediate;

Startup the db in nomount

    SQL> startup nomount pfile='/oracle/oradata/test/initTEST.ora';

Use RMAN to copy control file from ASM to NON-ASM

    RMAN> restore controlfile from '+DATA/TEST/CONTROLFILE/Current.310.864775821';

Mount the database:

    SQL> alter database mount;

Use RMAN to copy the database from ASM to Filesystem

    RMAN>
    run
    {
     backup as copy database format '/oracle/oradata/test/datafile/%U';
    }

Switch datafile to copy:

    rman>  switch database to copy;

Perform incomplete recovery and open the database using the RESETLOGS option

    SQL> recover database using backup controlfile until cancel;
    SQL> alter database open resetlogs;

Switch tempfile to Filesystem

    SQL> select tablespace_name, file_name, bytes from dba_temp_files;
    SQL> alter database tempfile  '+DATA/TEST/tempfile/temp.316.864775883' drop including datafiles;
    SQL> alter tablespace temp add tempfile '/oracle/oradata/test/datafile/temp_01.dbf' size 5m autoextend on next 2m maxsize unlimited;
   
    * We need to add required tempfiles in filesystem, above steps is showing just one.

 Re-Arrange redo log files:

        a) Add two new redolog groups
       SQL>  alter database add logfile thread 1 group 3 ('/oracle/oradata/test/datafile/redo03_01') size 20m;

      SQL> alter database add logfile thread 1 group 4 ('/oracle/oradata/test/datafile/redo04_01') size 20m;
    b) Perform some log switches and drop the inactive log groups which are in ASM
        SQL> alter system switch logfile;
        SQL> alter database drop logfile group 1;
        SQL> alter database drop logfile group 2;

Create spfile from pfile and bounce the db to use spfile;

    SQL> create spfile from pfile='/oracle/oradata/test/initTEST.ora';

Verify All database files are in filesystem

 SQL> select file_name from dba_data_files;

 SQL> select file_name from dba_temp_files

 SQL> select name from v$controlfile;

 SQL> select member from v$logfile;

Delete old copy using rman

    RMAN> delete force copy;