Step By Step To Migration ASM to File System

Lets Get The Database Data Files :


1-See your Database Files :

SQL>select file_name from dba_data_files;

 +DATA/test/datafile/undotbs1.301.697649965
+DATA/test/datafile/sysaux.300.697649963
+DATA/test/datafile/system.299.697649963
+DATA/test/datafile/users.302.697649965
+DATA/test/datafile/users.309.697650601

select tablespace_name,count(file_name) from dba_data_files group by tablespace_name ORDER BY 2

Use the above query to see how many data files under tablespace .

Now After see the DataFile , we need to create new Directory to copy the data file to it .
  mkdir /app/oracle/TEST

sqlplus / as sysdba

set lines 120 pages 0

select ‘copy datafile ‘||file_id||’ to ‘||”’/app/oracle/TEST/’||substr(file_name,21,instr(file_name,’.’)-21)||’_01.dbf”’||’;’ from dba_data_files order by 1;

copy datafile 1 to ‘/app/oracle/TEST/system_01.dbf’;

copy datafile 2 to ‘/app/oracle/TEST/sysaux_01.dbf’;

copy datafile 3 to ‘/app/oracle/TEST/undotbs1_01.dbf’;

copy datafile 4 to ‘/app/oracle/TEST/users_01.dbf’;

copy datafile 5 to ‘/app/oracle/TEST/users_02.dbf’;

shutdown immediate;

startup mount;

exit

rman target /

copy datafile 1 to ‘/app/oracle/TEST/system_01.dbf’;

copy datafile 2 to ‘/app/oracle/TEST/sysaux_01.dbf’;

copy datafile 3 to ‘/app/oracle/TEST/undotbs1_01.dbf’;

copy datafile 4 to ‘/app/oracle/TEST/users_01.dbf’;

copy datafile 5 to ‘/app/oracle/TEST/users_02.dbf’;

exit

sql / as sysdba

select ‘alter database rename file ”’||file_name||”’ to ‘||”’/app/oracle/TEST/’||substr(file_name,21,instr(file_name,’.’)-21)||’_01.dbf”’||’;’ from dba_data_files order by 1;

alter database rename file ‘+DATA/test/datafile/sysaux.300.697649963’ to ‘/app/oracle/TEST/sysaux_01.dbf’;

alter database rename file ‘+DATA/test/datafile/system.299.697649963’ to ‘/app/oracle/TEST/system_01.dbf’;

alter database rename file ‘+DATA/test/datafile/undotbs1.301.697649965’ to ‘/app/oracle/TEST/undotbs1_01.dbf’;

alter database rename file ‘+DATA/test/datafile/users.302.697649965’ to ‘/app/oracle/TEST/users_01.dbf’;

alter database rename file ‘+DATA/test/datafile/users.309.697650601’ to ‘/app/oracle/TEST/users_02.dbf’;

open database

select name from v$controlfile
union
select name from v$tempfile
union
select member from v$logfile
union
select filename from v$block_change_tracking;

NAME
——————————————————————————–
+DATA/test/controlfile/current.303.697650047
+DATA/test/onlinelog/group_1.304.697650049
+DATA/test/onlinelog/group_2.305.697650049
+DATA/test/onlinelog/group_3.306.697650049
+DATA/test/tempfile/temp.307.697650095
+FRA/test/controlfile/current.260.697650047
+FRA/test/onlinelog/group_1.261.697650049
+FRA/test/onlinelog/group_2.262.697650049
+FRA/test/onlinelog/group_3.263.697650051

alter database rename file ‘+DATA/test/datafile/sysaux.300.697649963’ to ‘/app/oracle/TEST/sysaux_01.dbf’;

alter database rename file ‘+DATA/test/datafile/system.299.697649963’ to ‘/app/oracle/TEST/system_01.dbf’;

alter database rename file ‘+DATA/test/datafile/undotbs1.301.697649965’ to ‘/app/oracle/TEST/undotbs1_01.dbf’;

alter database rename file ‘+DATA/test/datafile/users.302.697649965’ to ‘/app/oracle/TEST/users_01.dbf’;

alter database rename file ‘+DATA/test/datafile/users.309.697650601’ to ‘/app/oracle/TEST/users_02.dbf’;

ALTER DATABASE ADD LOGFILE GROUP 4 (‘/app/oracle/TEST/redo_04a.dbf’,’/app/oracle/TEST/redo_04b.dbf’) size 50M;

ALTER DATABASE ADD LOGFILE GROUP 5 (‘/app/oracle/TEST/redo_05a.dbf’,’/app/oracle/TEST/redo_05b.dbf’) size 50M;

ALTER DATABASE ADD LOGFILE GROUP 6 (‘/app/oracle/TEST/redo_06a.dbf’,’/app/oracle/TEST/redo_06b.dbf’) size 50M; 

4-the Last Step for Control file and Spfile :

SQL>show parameter control

control_files 

————————–

+DATA/test/controlfile/current .303.697650047

SQL > Create Pfile=” from spfile ;

After Creating Pfile , Open it modify Control File Location to Our New Location /app/oracle/TEST/…

*.control_files=’/app/oracle/TEST/control01.ctl’,’/app/oracle/TEST/control02.ctl’

startup nomount

$rman target /

 restore controlfile from ‘+DATA/test/controlfile/current.303.697650047’;

 exit

create spfile=’/app/oracle/product/11.1.0/db_1/dbs/spfileTEST.ora’ from pfile=’/home/oracle/john.ora’;

File created.

shutdown immediate;
startup

show parameter control

control_files  /app/oracle/TEST/control01.ctl , /app/oracle/TEST/control02.ctl

This Steps Without Using RMAN , I will Post Another Steps Using RMAN

Thank you
Osama mustafa