Bugra Canbakal's Oracle Blog

  • Home
  • Contact
  • SITE MAP
You are here: Home / Oracle / Moving datafile local disk to ASM

Moving datafile local disk to ASM

11:36 AM By Bugra Canbakal 2 Comments

1.PURPOSE AND SCOPE

In some cases   we accidentally  forget and create datafiles on local disk while using.In this article i am going to explain how to move datafile local disk to ASM.

2.PRACTICE

2.1. I use 2 node RAC system for this practice. And create 2 tablespace on node 1 local disk.

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
SQL> select file_name,tablespace_name from dba_data_files where tablespace_name like 'AHBS%';
 
FILE_NAME        TABLESPACE_NAME
--------------------------------------------------------------------------------
 
/u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/ahbs_data_ts     AHBS_DATA_TS
 
/u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/AHBS_INDEX_TS01.dbf    AHBS_INDEX_TS
 
2 rows selected.

 

2.2. We need to change the status of these tablespace to offline.

Oracle PL/SQL
1
2
3
4
SQL> alter tablespace AHBS_INDEX_TS offline;
Tablespace altered.
SQL> alter tablespace AHBS_DATA_TS offline;
Tablespace altered.

 

2.3. For copying tablespaces to ASM i used rman copy command.

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
[oracle@sba5db07 ~]$ rman
 
Recovery Manager: Release 11.2.0.2.0 - Production on Sat Jun 30 18:10:00 2012
 
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
 
RMAN> connect target;
 
connected to target database: MERGE (DBID=3048845705)
 
RMAN> copy datafile '/u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/ahbs_data_ts' to  '+DATA_SBA5/merge/datafile/ahbs_data_ts_01.dbf';
 
Starting backup at 30-JUN-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=201 instance=MERGE1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00017 name=/u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/ahbs_data_ts
output file name=+DATA_SBA5/merge/datafile/ahbs_data_ts_01.dbf tag=TAG20120630T182127 RECID=4 STAMP=787342893
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 30-JUN-12
 
RMAN> copy datafile  '/u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/AHBS_INDEX_TS01.dbf'  to  '+DATA_SBA5/merge/datafile/AHBS_INDEX_TS01.dbf' ;
 
Starting backup at 30-JUN-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00018 name=/u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/AHBS_INDEX_TS01.dbf
output file name=+DATA_SBA5/merge/datafile/ahbs_index_ts01.dbf tag=TAG20120630T182136 RECID=5 STAMP=787342896
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 30-JUN-12

 

2.4. Okey now we created datafiles on ASM but database doesnt aware of these tablespaces.So we need to rename datafiles local to ASM.

Oracle PL/SQL
1
2
3
4
5
6
7
SQL> alter database rename file   '/u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/ahbs_data_ts' to  '+DATA_SBA5/merge/datafile/ahbs_data_ts_01.dbf' ;
 
Database altered.
 
SQL> alter database rename file  '/u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/AHBS_INDEX_TS01.dbf'  to  '+DATA_SBA5/merge/datafile/AHBS_INDEX_TS01.dbf' ;
 
Database altered.

 

2.5. Lets change tablespaces status to online.

Oracle PL/SQL
1
2
3
4
5
6
7
SQL> alter tablespace AHBS_INDEX_TS online;
 
Tablespace altered.
 
SQL> alter tablespace AHBS_DATA_TS online;
 
Tablespace altered.

 

2.6. Lets check path of tablespaces.

Oracle PL/SQL
1
2
3
4
5
6
7
8
SQL> select file_name,tablespace_name from dba_data_files where tablespace_name like 'AHBS%';
 
FILE_NAME        TABLESPACE_NAME
--------------------------------------------------------------------------------
 
+DATA_SBA5/merge/datafile/ahbs_data_ts_01.dbf     AHBS_DATA_TS
 
+DATA_SBA5/merge/datafile/AHBS_INDEX_TS01.dbf    AHBS_INDEX_TS

Share this:

  • LinkedIn
  • Twitter

Konuyla ilgili diğer yazılar

  • Oracle AMDU
  • Restore işleminde datafile pathlerinin yerinin değiştirilmesi
  • Oracle DUL (Data UnLoader) //Life is DUL without it
  • Incremental backup icin Block Change Tracking dosyası olusturmak
  • Data block corrupt kurtarma işlemi

Filed Under: Oracle, RAC - Real Application Cluster, Single Node Tagged With: asm, move local datafile to asm, oracle, rman

Comments

  1. AvatarAhmet KILINÇ says

    Monday July 9th, 2012 at 11:23 AM

    Buğra hocam eline sağlık bu arlaar ASM üzerinde testler yapıyordum senin paylaşmış olduğun bu senaryoyu denemiş oldum.

    teşekkürler

  2. AvatarBugra Canbakal says

    Thursday July 12th, 2012 at 10:39 AM

    Rica ederim işine yaradığına sevindim.

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

About Me



Language:

  • English
  • Turkish

Categories

  • Oracle
    • RAC – Real Application Cluster
    • RMAN
    • Single Node
    • Standby – Dataguard
  • OS
    • Linux

Blogroll

  • Emre Baransel
  • Gökhan Atıl
  • H. Tonguc Yılmaz
  • Kamil Türkyılmaz
  • Tanel Poder
  • Turkce Oracle
  • Turkish Oracle User Group
  • Uwe Hesse
  • Zekeriya Beşiroğlu

Archives

  • April 2014
  • March 2014
  • August 2013
  • June 2013
  • March 2013
  • January 2013
  • December 2012
  • November 2012
  • July 2012
  • June 2012
  • May 2012
  • January 2012
  • November 2011
  • October 2011
  • August 2011
  • July 2011
  • June 2011
  • February 2011
  • January 2011
  • December 2010
  • November 2010
  • June 2010
OCP
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy

[footer_backtotop]

Copyright © 2010-2014 Bugra Canbakal. All rights reserved.