Oracle DBA – A lifelong learning experience

Moving from ASM storage back to filesystem

Posted by John Hallas on September 17, 2009

Whilst it is not very common to move from ASM to filesystem I needed to prepare a regression plan for the migration of a 450 datafile database in the event of problems during the migration or shortly afterwards.

The example below is from a small database called TEST and I used filestore in /app/oracle whereas normally I would have a /oradata/SID/ filesystem

The details below should be enough to get someone through the migration and the sql scripts can be modified to be more generic if necessary but it is not too hard to use them as they are now. 

List the datafiles and we will add a second datafile to the users tablespace to provide an example of an issue that can occur.


SYS@TEST 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

 mkdir /app/oracle/TEST

Some tablespaces may have multiple datafiles which would end up with the same name using the code above. However as a quick circumvention I manually identified those tablespaces using this sql and then edited the new data file names

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

In my example it only applied to the USERS tablespace and I edited the second ASM datafile to be mapped to users02.dbf not users01.dbf as the script will output. This action would have to be repeated for all tablespaces with multiple datafiles

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

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 the database and check for files still using ASM

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; 

You will need to perform logfile switches or checkpoint before the log groups using ASM are available for removal. 

SQL>

show parameter control

 control_files +DATA/test/controlfile/current .303.697650047, +FRA/test/cont rolfile/current.260.697650047

create a pfile from spfile and edit the pfile to show new placement for the controlfiles

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

startup nomount

$rman target / 

Recovery Manager: Release 11.1.0.7.0 - Production on Wed Sep 16 16:36:15 2009 connected to target database:

TEST (not mounted) using target database control file instead of recovery catalog

 restore controlfile from '+DATA/test/controlfile/current.303.697650047';

Starting restore at 16-SEP-09 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=70 device type=DISK channel ORA_DISK_1: copied control file copy output file name=/app/oracle/TEST/control01.ctl output file name=/app/oracle/TEST/control02.ctl

Finished restore at 16-SEP-09

 exit

Recovery Manager complete.

 

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 

All that is left is to recreate the temp tablespace to use filesystem storage.

startup mount;

RMAN> run { set newname for tempfile 1 to '/app/oracle/TEST/temp01.dbf'; switch tempfile all;}

executing command:

SET NEWNAME renamed tempfile 1 to /app/oracle/TEST/temp01.dbf in control file  rman target /

 TEST (not mounted) using target database control file instead of recovery catalog RMAN> restore controlfile from '+DATA/test/controlfile/current.303.697650047';

Starting restore at 16-SEP-09 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=69 device type=DISK channel ORA_DISK_1:

copied control file copy output file

name=/app/oracle/TEST/control01.ctl output file name=/app/oracle/TEST/control02.ctl

Finished restore at 16-SEP-09

All should be complete now. As I said, this is just a hint on what actions to take and the sql scripts should be useful if you have lots of datafiles.

All that is left is to create a new  block_change_tracking file and the ASM storage can be released.

About these ads

4 Responses to “Moving from ASM storage back to filesystem”

  1. rahul said

    Hi,

    Nice article with very good knowledge sharing. Thanks for same.

  2. chandu said

    here is my link for migrating from File system to ASM disk group :

    http://chandu208.blogspot.com/2011/04/migrate-from-database-file-system-to.html

  3. Alex said

    Hello.

    I had a database with some datafiles on ASM and I was able to move them to FS.
    I needed to do that in order to upgrade the database to 11g without touching the ASM 10g on the server.
    Now, DBCA still complains about this database being ASM.

    Is there any other configuration to be made to say oracle “hey, this database is no longer ASM”.

    BTW, I’ve moved controlfiles and redologs also to FS so there is nothing else on ASM.
    Also, no parameters at all ASM related.

    Thanks for any advice,
    Alex.

  4. Devanathan said

    good document. thanls

    rgds
    Devanathan

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 207 other followers

%d bloggers like this: