How to Move datafile from ASM to ASM

Today got archiver error issue due to our FRA is full. reason for that is some datafile are created inside the +FRA.
My Task now is to move the datafiles from +FRA to +DATA since we already increased it.

Got permission from application to take below datafiles offline for a short period of time.

 
   FILE_ID TABLESPACE_NAME                FILE_NAME
---------- ------------------------------ --------------------------------------------------
       151 DATA_XXX                       +FRA/xxxclpt2/datafile/data_xxx.1410.908245675
       152 DATA_XXX                       +FRA/xxxclpt2/datafile/data_xxx.1451.908245731
       153 DATA_XXX                       +FRA/xxxclpt2/datafile/data_xxx.1487.908245783
       154 DATA_XXX                       +FRA/xxxclpt2/datafile/data_xxx.380.908245805
       102 OPDB_DATA                      +FRA/xxxclpt2/datafile/opdb_data.1938.911645643
       155 DATA_XXX                       +FRA/xxxclpt2/datafile/data_xxx.2026.916987355
       156 DATA_XXX                       +FRA/xxxclpt2/datafile/data_xxx.1457.916987395
       157 DATA_XXX                       +FRA/xxxclpt2/datafile/data_xxx.1363.916987403
       158 DATA_XXX                       +FRA/xxxclpt2/datafile/data_xxx.1862.916987409
       159 DATA_RGN                       +FRA/xxxclpt2/datafile/data_rgn.2063.916987425
       160 DATA_RGN                       +FRA/xxxclpt2/datafile/data_rgn.1822.916987437
       161 DATA_RGN                       +FRA/xxxclpt2/datafile/data_rgn.2077.916987509
       162 DATA_RGN                       +FRA/xxxclpt2/datafile/data_rgn.1848.916987523
       163 INDEX_KYC                      +FRA/xxxclpt2/datafile/index_kyc.1900.916987543
       164 INDEX_KYC                      +FRA/xxxclpt2/datafile/index_kyc.2035.916987549

15 rows selected.

after moving the datafiles below is the snapshot of my ASM diskgroup.

 
Disk Group                 Sector   Block   Allocation
Name                         Size    Size    Unit Size State       Type   Total Size (GB)    USED_GB Pct. Used
------------------------- ------- ------- ------------ ----------- ------ --------------- ---------- ---------
DATA                          512   4,096    1,048,576 CONNECTED   EXTERN           5,664 3419.50293     60.37
FRA                           512   4,096    1,048,576 CONNECTED   EXTERN             539 8.18457031      1.52

SQL>

Using RMAN created a copy of affected datafiles.

 
    BACKUP AS COPY DATAFILE 151,152,153,154,102,155,156,157,158,159,160,161,162,163,164 FORMAT "+DATA";

    Starting backup at 20-SEP-16
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=629 device type=DISK
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00151 name=+FRA/xxxclpt2/datafile/data_xxx.1410.908245675
    output file name=+DATA/xxxclpt2/datafile/data_xxx.442.923089717 tag=TAG20160920T214836 RECID=46 STAMP=923089785
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00152 name=+FRA/xxxclpt2/datafile/data_xxx.1451.908245731
    output file name=+DATA/xxxclpt2/datafile/data_xxx.443.923089793 tag=TAG20160920T214836 RECID=47 STAMP=923089857
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00153 name=+FRA/xxxclpt2/datafile/data_xxx.1487.908245783
    output file name=+DATA/xxxclpt2/datafile/data_xxx.444.923089867 tag=TAG20160920T214836 RECID=48 STAMP=923089932
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00154 name=+FRA/xxxclpt2/datafile/data_xxx.380.908245805
    output file name=+DATA/xxxclpt2/datafile/data_xxx.445.923089943 tag=TAG20160920T214836 RECID=49 STAMP=923090009
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00155 name=+FRA/xxxclpt2/datafile/data_xxx.2026.916987355
    output file name=+DATA/xxxclpt2/datafile/data_xxx.446.923090017 tag=TAG20160920T214836 RECID=50 STAMP=923090084
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00156 name=+FRA/xxxclpt2/datafile/data_xxx.1457.916987395
    output file name=+DATA/xxxclpt2/datafile/data_xxx.447.923090093 tag=TAG20160920T214836 RECID=51 STAMP=923090159
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00157 name=+FRA/xxxclpt2/datafile/data_xxx.1363.916987403
    output file name=+DATA/xxxclpt2/datafile/data_xxx.448.923090167 tag=TAG20160920T214836 RECID=52 STAMP=923090233
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00158 name=+FRA/xxxclpt2/datafile/data_xxx.1862.916987409
    output file name=+DATA/xxxclpt2/datafile/data_xxx.449.923090243 tag=TAG20160920T214836 RECID=53 STAMP=923090307
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00102 name=+FRA/xxxclpt2/datafile/opdb_data.1938.911645643
    output file name=+DATA/xxxclpt2/datafile/opdb_data.450.923090317 tag=TAG20160920T214836 RECID=54 STAMP=923090384
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00159 name=+FRA/xxxclpt2/datafile/data_rgn.2063.916987425
    output file name=+DATA/xxxclpt2/datafile/data_rgn.451.923090393 tag=TAG20160920T214836 RECID=55 STAMP=923090401
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00160 name=+FRA/xxxclpt2/datafile/data_rgn.1822.916987437
    output file name=+DATA/xxxclpt2/datafile/data_rgn.452.923090407 tag=TAG20160920T214836 RECID=56 STAMP=923090417
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00161 name=+FRA/xxxclpt2/datafile/data_rgn.2077.916987509
    output file name=+DATA/xxxclpt2/datafile/data_rgn.453.923090423 tag=TAG20160920T214836 RECID=57 STAMP=923090432
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00162 name=+FRA/xxxclpt2/datafile/data_rgn.1848.916987523
    output file name=+DATA/xxxclpt2/datafile/data_rgn.454.923090439 tag=TAG20160920T214836 RECID=58 STAMP=923090447
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00163 name=+FRA/xxxclpt2/datafile/index_kyc.1900.916987543
    output file name=+DATA/xxxclpt2/datafile/index_kyc.455.923090453 tag=TAG20160920T214836 RECID=59 STAMP=923090455
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00164 name=+FRA/xxxclpt2/datafile/index_kyc.2035.916987549
    output file name=+DATA/xxxclpt2/datafile/index_kyc.456.923090457 tag=TAG20160920T214836 RECID=60 STAMP=923090458
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
    Finished backup at 20-SEP-16

    Starting Control File and SPFILE Autobackup at 20-SEP-16
    piece handle=/optware/oracle/11.2.0.4/db_1/dbs/xxxclpt2_c-4132649823-20160920-01.rman comment=NONE
    Finished Control File and SPFILE Autobackup at 20-SEP-16

Now its time to take the datafiles offline.

 
    SQL> ALTER DATABASE DATAFILE 151,152,153,154,102,155,156,157,158,159,160,161,162,163,164  offline;

    Database altered.

    SQL>

Using rman perform switch datafile command.

 
    switch datafile 151 to copy;
    switch datafile 152 to copy;
    switch datafile 153 to copy;
    switch datafile 154 to copy;
    switch datafile 102 to copy;
    switch datafile 155 to copy;
    switch datafile 156 to copy;
    switch datafile 157 to copy;
    switch datafile 158 to copy;
    switch datafile 159 to copy;
    switch datafile 160 to copy;
    switch datafile 161 to copy;
    switch datafile 162 to copy;
    switch datafile 163 to copy;
    switch datafile 164 to copy;

    RMAN> switch datafile 151 to copy;

    datafile 151 switched to datafile copy "+DATA/xxxclpt2/datafile/data_xxx.442.923089717"

    RMAN> switch datafile 152 to copy;

    switch datafile 153 to copy;
    switch datafile 154 to copy;
    datafile 152 switched to datafile copy "+DATA/xxxclpt2/datafile/data_xxx.443.923089793"
    switch datafile 102 to copy;
    switch datafile 155 to copy;
    switch datafile 156 to copy;
    switch datafile 157 to copy;
    switch datafile 158 to copy;
    switch datafile 159 to copy;
    switch datafile 160 to copy;
    switch datafile 161 to copy;
    switch datafile 162 to copy;
    switch datafile 163 to copy;
    switch datafile 164 to copy;

    RMAN>
    datafile 153 switched to datafile copy "+DATA/xxxclpt2/datafile/data_xxx.444.923089867"

    RMAN>
    datafile 154 switched to datafile copy "+DATA/xxxclpt2/datafile/data_xxx.445.923089943"

    RMAN>
    datafile 102 switched to datafile copy "+DATA/xxxclpt2/datafile/opdb_data.450.923090317"

    RMAN>
    datafile 155 switched to datafile copy "+DATA/xxxclpt2/datafile/data_xxx.446.923090017"

    RMAN>
    datafile 156 switched to datafile copy "+DATA/xxxclpt2/datafile/data_xxx.447.923090093"

    RMAN>
    datafile 157 switched to datafile copy "+DATA/xxxclpt2/datafile/data_xxx.448.923090167"

    RMAN>
    datafile 158 switched to datafile copy "+DATA/xxxclpt2/datafile/data_xxx.449.923090243"

    RMAN>
    datafile 159 switched to datafile copy "+DATA/xxxclpt2/datafile/data_rgn.451.923090393"

    RMAN>
    datafile 160 switched to datafile copy "+DATA/xxxclpt2/datafile/data_rgn.452.923090407"

    RMAN>
    datafile 161 switched to datafile copy "+DATA/xxxclpt2/datafile/data_rgn.453.923090423"

    RMAN>
    datafile 162 switched to datafile copy "+DATA/xxxclpt2/datafile/data_rgn.454.923090439"

    RMAN>
    datafile 163 switched to datafile copy "+DATA/xxxclpt2/datafile/index_kyc.455.923090453"

    RMAN>
    datafile 164 switched to datafile copy "+DATA/xxxclpt2/datafile/index_kyc.456.923090457"

    RMAN>

    RMAN>

Now we need to recover the datafiles.

 
    RMAN> recover  datafile 151,152,153,154,102,155,156,157,158,159,160,161,162,163,164;

    Starting recover at 20-SEP-16
    allocated channel: ORA_SBT_TAPE_1
    channel ORA_SBT_TAPE_1: SID=915 device type=SBT_TAPE
    channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle - Release 7.5 (2012091610)
    allocated channel: ORA_SBT_TAPE_2
    channel ORA_SBT_TAPE_2: SID=102 device type=SBT_TAPE
    channel ORA_SBT_TAPE_2: Veritas NetBackup for Oracle - Release 7.5 (2012091610)
    allocated channel: ORA_SBT_TAPE_3
    channel ORA_SBT_TAPE_3: SID=247 device type=SBT_TAPE
    channel ORA_SBT_TAPE_3: Veritas NetBackup for Oracle - Release 7.5 (2012091610)
    allocated channel: ORA_SBT_TAPE_4
    channel ORA_SBT_TAPE_4: SID=340 device type=SBT_TAPE
    channel ORA_SBT_TAPE_4: Veritas NetBackup for Oracle - Release 7.5 (2012091610)
    allocated channel: ORA_SBT_TAPE_5
    channel ORA_SBT_TAPE_5: SID=392 device type=SBT_TAPE
    channel ORA_SBT_TAPE_5: Veritas NetBackup for Oracle - Release 7.5 (2012091610)
    allocated channel: ORA_SBT_TAPE_6
    channel ORA_SBT_TAPE_6: SID=435 device type=SBT_TAPE
    channel ORA_SBT_TAPE_6: Veritas NetBackup for Oracle - Release 7.5 (2012091610)
    allocated channel: ORA_SBT_TAPE_7
    channel ORA_SBT_TAPE_7: SID=488 device type=SBT_TAPE
    channel ORA_SBT_TAPE_7: Veritas NetBackup for Oracle - Release 7.5 (2012091610)
    allocated channel: ORA_SBT_TAPE_8
    channel ORA_SBT_TAPE_8: SID=530 device type=SBT_TAPE
    channel ORA_SBT_TAPE_8: Veritas NetBackup for Oracle - Release 7.5 (2012091610)
    using channel ORA_DISK_1

    starting media recovery
    media recovery complete, elapsed time: 00:00:00

    Finished recover at 20-SEP-16

    RMAN>

No let’s take the datafile online.

 
    ALTER DATABASE DATAFILE 151,152,153,154,102,155,156,157,158,159,160,161,162,163,164 online;

    -- validate
    SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files where FILE_NAME like '+FRA%';

    no rows selected

    SQL>

Now we need to delete the files in +FRA using RMAN.

 
    delete datafilecopy "+FRA/xxxclpt2/datafile/data_xxx.1410.908245675";
    delete datafilecopy "+FRA/xxxclpt2/datafile/data_xxx.1451.908245731";
    delete datafilecopy "+FRA/xxxclpt2/datafile/data_xxx.1487.908245783";
    delete datafilecopy "+FRA/xxxclpt2/datafile/data_xxx.380.908245805";
    delete datafilecopy "+FRA/xxxclpt2/datafile/opdb_data.1938.911645643";
    delete datafilecopy "+FRA/xxxclpt2/datafile/data_xxx.2026.916987355";
    delete datafilecopy "+FRA/xxxclpt2/datafile/data_xxx.1457.916987395";
    delete datafilecopy "+FRA/xxxclpt2/datafile/data_xxx.1363.916987403";
    delete datafilecopy "+FRA/xxxclpt2/datafile/data_xxx.1862.916987409";
    delete datafilecopy "+FRA/xxxclpt2/datafile/data_rgn.2063.916987425";
    delete datafilecopy "+FRA/xxxclpt2/datafile/data_rgn.1822.916987437";
    delete datafilecopy "+FRA/xxxclpt2/datafile/data_rgn.2077.916987509";
    delete datafilecopy "+FRA/xxxclpt2/datafile/data_rgn.1848.916987523";
    delete datafilecopy "+FRA/xxxclpt2/datafile/index_kyc.1900.916987543";
    delete datafilecopy "+FRA/xxxclpt2/datafile/index_kyc.2035.916987549";
    
    -- below is the sample output for each datafile.
    RMAN> delete datafilecopy "+FRA/xxxclpt2/datafile/index_kyc.2035.916987549";

    released channel: ORA_DISK_1
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=629 device type=DISK
    List of Datafile Copies
    =======================

    Key     File S Completion Time Ckp SCN    Ckp Time
    ------- ---- - --------------- ---------- ---------------
    75      164  A 20-SEP-16       9553561253 20-SEP-16
            Name: +FRA/xxxclpt2/datafile/index_kyc.2035.916987549


    Do you really want to delete the above objects (enter YES or NO)? yes
    deleted datafile copy
    datafile copy file name=+FRA/xxxclpt2/datafile/index_kyc.2035.916987549 RECID=75 STAMP=923090722
    Deleted 1 objects


    RMAN>

Thanks for Checking.

Advertisements

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