Step By Step To Migration ASM to File System

There's More than One Way For this Migration you can use what you want


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

2- We Will Create Our Own Script And Copy our Database File to our new location :

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';
 
 3- This not the finish , there's some file still On A, Check it by :

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

Comments

Popular posts from this blog

Connection refused; No available router to destination On Weblogic Node Manager Log

How to change SGA in Oracle Rac

SGA_MAX_SIZE & SGA_TARGET / MEMORY_TARGET & MEMORY_MAX_TARGET