8 Replies Latest reply: May 10, 2012 7:27 AM by John-MK RSS

    Moving datafile to ASM

    John-MK
      Hello,

      By mistake I created one data file in the local file system instead of ASM diskgroup. Initially I created the data file with 100MB, autoextensible to 100M,maxsize 2G, but then I stopped autoexntensible, so that less data file size would be better to move to ASM.

      My concern is that now I am thinking if the datafile is full then I will move it to ASM diskgroup (script to move to ASM is already ready). I want to be sure if the data file is full, so that I move it peacefully to ASM, because otherwise datafile is not accessible if it is currently being written and moving to ASM. I dont want that stop of data written to data file, or the data file is full already and there is no risk that data fill be written to it or it will be inaccessible.

      Please have a look and suggest how can I check that the data file is full actually and safe to move to ASM.

      DB=11.2.0.2 EE, OS=RHL
      SQL> select file_id,tablespace_name,bytes/1024/1024 "Bytes in MB",status,autoextensible,maxbytes/1024/1024 "Maxbytes in MB",user_bytes/1024/1024 from dba_data_files where file_id=62;
      
         FILE_ID TABLESPACE_NAME            Bytes in MB STATUS                       AUTOEXTENSIB Maxbytes in MB USER_BYTES/1024/1024
      ---------- ------------------------------ ----------- ------------------------------------ ------------ -------------- --------------------
           62   KKJ                      300 AVAILABLE                   NO                    0               299
      Thank you.

      Regards,
        • 1. Re: Moving datafile to ASM
          userHH0815
          Hi,

          you can see the free space in dba_free_space:
          select tablespace_name, file_id, bytes, blocks from dba_free_space where file_id=62;

          Regards Heike
          • 2. Re: Moving datafile to ASM
            John-MK
            Hi,

            Thanks.

            What does it mean? I'm not able to understand this query

            SQL> select bytes/1024/1024,blocks from dba_free_space where file_id=62;
            BYTES/1024/1024     BLOCKS
            --------------- ----------
                       3        384
                      16       2048
                       1        128
                       1        128
                       1        128
                       1        128
                       1        128
                       1        128
                       1        128
                       8       1024
                       1        128
            Regards
            • 3. Re: Moving datafile to ASM
              713555
              is showing where theres free space in that file.

              but why do you want to know whether its full? what if theres a delete or an update? Are there only inserts in this datafile? how many tables are there? I had to do somethign similar recently, no down time allowed, but only 4 tables. I created new datafile in asm and online redefined the 4 tables so they were located in the new datafile.
              • 4. Re: Moving datafile to ASM
                John-MK
                Hi,

                Because if the data file is not full till now, it means it will allow DML activity on it, right? I dont know how much time is required to move the data file to ASM by using .sh script. Lets assume 1 minute for 300MB data file (not sure about the time it takes, may be less than that or more), during this time data file will not be available for read and write,etc. That of course I dont want in this Production DB. That's my concern right now :(


                Regards,
                • 5. Re: Moving datafile to ASM
                  713555
                  create a 2gb file and test and time the copy. I wouldnt have said minutes. I'd guess seconds.

                  it wont just be dml and it wont be just the datafile, the entire tablespace needs to come offline. have you got the steps?

                  create a test tablespace. create 1 test datafile

                  SQL> select tablespace_name, file_name from dba_data_files where file_id=10;

                  TABLESPACE_NAME FILE_NAME
                  ------------------ ------------------------------
                  TEST /oradata/test01.dbf


                  SQL> alter tablespace TEST offline;

                  -- connect to RMAN and copy the datafile to ASM

                  RMAN> copy datafile 10 to '+DATA';
                  RMAN> switch datafile 10 to copy;


                  -- back to sqlplus


                  SQL> alter tablespace TEST online;
                  • 6. Re: Moving datafile to ASM
                    John-MK
                    Why whole tablespace need to be OFFLINE, and dont need to back and forth from RMAN to SQLPLUS. You said no downtime right? ...you are taking whole tablespace OFFLINE, ok even for seconds... My question was how Can I know how much space exactly is left in the data file to fill? :) I have already creaed this shell script to minimize the time taken to move the data file, instead of writing commands from RMAN and SQLplus.
                    /rman target / nocatalog 
                    sql "ALTER DATABASE DATAFILE 62 OFFLINE";
                    backup as copy datafile 62 format '+DATA01';
                    switch datafile 62 to copy;
                    recover datafile 62;
                    sql "alter database datafile 62 ONLINE";
                    exit;
                    Regards

                    Edited by: 910385 on May 10, 2012 3:42 AM

                    Edited by: 910385 on May 10, 2012 3:43 AM
                    • 7. Re: Moving datafile to ASM
                      713555
                      How to move a datafile from a file system to ASM [ID 390274.1]

                      >

                      In order to move a datafile on a running active database the tablespace where the datafile resides must be placed offline.

                      >

                      SQL> create tablespace test01
                        2  datafile 'c:\test01.dbf' size 2g;
                      
                      Tablespace created.
                      
                      SQL>
                      
                      select file#, name from v$ddatafile
                              
                      36      C:\TEST01.DBF
                      rman target /
                      sql "ALTER DATABASE DATAFILE 36 OFFLINE";
                      backup as copy datafile 36 format '+DATA';
                      switch datafile 36 to copy;
                      recover datafile 36;
                      sql "alter database datafile 36 ONLINE";
                      exit;
                      
                      -- fails with datafile as media recovery needs to be enabled
                      
                      RMAN> sql "ALTER DATABASE DATAFILE 36 OFFLINE";
                      
                      using target database control file instead of recovery catalog
                      sql statement: ALTER DATABASE DATAFILE 36 OFFLINE
                      RMAN-00571: ===========================================================
                      RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
                      RMAN-00571: ===========================================================
                      RMAN-03009: failure of sql command on default channel at 05/10/2012 12:18:46
                      RMAN-11003: failure during parse/execution of SQL statement: ALTER DATABASE DATAFILE 36 OF
                      ORA-01145: offline immediate disallowed unless media recovery enabled
                      -- try again with tablespace, ran in seconds. but thats not the point. tablespace needed to be offline
                      RMAN> sql "ALTER TABLESPACE TEST01 OFFLINE";
                      
                      using target database control file instead of recovery catalog
                      sql statement: ALTER TABLESPACE TEST01 OFFLINE
                      
                      RMAN> backup as copy datafile 36 format '+DATA';
                      
                      Starting backup at 10-MAY-12
                      allocated channel: ORA_DISK_1
                      channel ORA_DISK_1: SID=82 instance=dprd1 device type=DISK
                      channel ORA_DISK_1: starting datafile copy
                      input datafile file number=00036 name=C:\TEST01.DBF
                      output file name=+DATA/dprd/datafile/test01.331.782914827 tag=TAG20120510T122026 RECID=1 S
                      channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
                      Finished backup at 10-MAY-12
                      
                      Starting Control File and SPFILE Autobackup at 10-MAY-12
                      piece handle=+FLASH/dprd/autobackup/2012_05_10/s_782914852.270.782914853 comment=NONE
                      Finished Control File and SPFILE Autobackup at 10-MAY-12
                      
                      RMAN> switch datafile 36 to copy;
                      
                      datafile 36 switched to datafile copy "+DATA/dprd/datafile/test01.331.782914827"
                      
                      RMAN> recover datafile 36;
                      
                      Starting recover at 10-MAY-12
                      using channel ORA_DISK_1
                      
                      starting media recovery
                      media recovery complete, elapsed time: 00:00:00
                      
                      Finished recover at 10-MAY-12
                      
                      RMAN> sql "ALTER TABLESPACE TEST01 ONLINE";
                      
                      sql statement: ALTER TABLESPACE TEST01 ONLINE
                      
                      RMAN>
                      • 8. Re: Moving datafile to ASM
                        John-MK
                        Thanks. I appreciate your effort.

                        Now I have at least two scripts to perform the action. If first doesnt't work then at least second will work by which tablespace will be offline.


                        Regards