Steps for Migrating database from Normal file system to ASM

Steps Migration of database from Normal filesystem to ASM:
Set the environment variable to proper database : export ORACLE_SID=testdb;
Run the /home/oracle/db_data_files.sql to get the list of files used by database which are on NAS filesystems and save the output
Make sure enable archivelog of the database
SQL> archive log list;
SQL>shutdown immediate;
Edit the init.ora parameter with the following
*.log_archive_dest_1='+FRA_01'
*.log_archive_format='testdb_%t_%r_%s.arc' (make sure change to corresponding db name)
SQL> startup mount
SQL> alter database archivelog;
Edit the init.ora parameter with the following
*.db_create_file_dest = '+DATA_01'
*.db_recovery_file_dest = '+FRA_01'
*.db_recovery_file_dest_size = 8G
*.control_files = '+DATA_01', '+FRA_01'
( make sure backup old parameter file and comment old controlfile parameter)

Shudown the database

SQL> Shutdown immediate

Startup database with nomount
SQL> STARTUP NOMOUNT

1. 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:
RMAN > connect target /
RMAN> restore controlfile from '/data/8ps/test/data/testdb/control_01.dbf';
2. From an RMAN or SQL*Plus session, mount the database. This will mount the database using the controlfile stored in ASM:
RMAN> ALTER DATABASE MOUNT;
3. From an RMAN session, copy the database files from the local file system to ASM:
RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA1_01';

NOTE: Since this step takes longtime run the following script in nohup mode

cat /home/oracle/copy_to_asm.ksh

#!/bin/ksh
export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'

rman << EOF
connect target /
run {
CONFIGURE DEVICE TYPE DISK PARALLELISM 4;
backup as copy database format '+DATA_01';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1;
}

exit;
EOF

nohup /home/oracle/copy_to_asm.ksh &

4. From an RMAN session, update the control file / data dictionary so that all database files point to the RMAN copy made in ASM:
rman target /
Rman > SWITCH DATABASE TO COPY;


5. From a SQL*Plus session, perform incomplete recovery and open the database using the RESETLOGS option:
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

NOTE: here you may not have anything to recover if changes are not many

SQL> ALTER DATABASE OPEN RESETLOGS;

6. 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=+DATA_01:
SQL> select tablespace_name, file_name, bytes from dba_temp_files;

SQL> alter database tempfile '/data/8ps/test/data/testdb/ts_temp_01.dbf' drop including datafiles;
SQL> alter tablespace temp add tempfile size 512M;



7. From a SQL*Plus session, re-create any online redo logfiles that are still currently on the local file system to ASM. This is done by simply dropping the logfiles from the local file system and re-creating them in ASM. This example relies on the initialization parameters db_create_file_dest=+DATA_01 and db_recovery_file_dest=+FRA_01:

a. Determine the current online redo logfiles to move to ASM by examining the file names (and sizes) from V$LOGFILE:
SQL> col member format a50
select a.group#, a.member, b.bytes
from v$logfile a, v$log b where a.group# = b.group#;
b. Force a log switch until the last redo log is marked "CURRENT" by issuing the following command:
SQL> select group#, status from v$log;
SQL> alter system switch logfile;
SQL> alter system switch logfile;

SQL> select group#, status from v$log;

c. After making the last online redo log file the CURRENT one, drop the first online redo log:
SQL> alter database drop logfile group 1;

d. Re-create the dropped redo log group in ASM (and a different size if desired):
SQL> alter database add logfile group 1 size 250m;

e. After re-creating the first online redo log group, loop back to drop / re-create the next online redo logfile until all logs are rebuilt in ASM.
Verify all online redo logfiles have been created in ASM:
SQL> col member format a50
select a.group#, a.member, b.bytes
from v$logfile a, v$log b where a.group# = b.group#;

8. Verify all the database files have been migrated to ASM

SQL> @/home/oracle/db_data_files_all.sql




9. Shutdown the database and edit the init.ora file to change controlfiles parameter to add both files

SQL> shutdown immediate

*.control_files = '+DATA_01/testdb/controlfile/current.257.754062863','+FRA_01/TESTDB/CONTROLFILE/current.259.754067365'




10. Startup the database

SQL> startup