Restoring live database datafile from standby
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Restoring live database datafile from standby

  1. #1
    Join Date
    Dec 2001
    Posts
    337

    Restoring live database datafile from standby

    Hi all,

    o/s: rhel 4
    db : 10.2.0.3 RAC/Dataguard config using ASM

    I have a situation where i need to move datafiles on my primary database from one ASM diskgroup to another due to a faulty LUN. However some of moves are not working as the copy of some datafiles is taking too long even the all the datafiles are roughly the same size (most have been successfully moved). As we also have a dataguard config, I was wondering if we could take a backup of the particualar datafile on the standby and restore it to the new diskgroup on LIVE? Is this possibe and if so i would appreciate the steps we need to take using RMAN?

    Any advice would be appreciated,
    Thanks,
    Chucks

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    You need to find out if your disks are slow while copying .

    If your standby database is on the same LAN as the primary then copying the datafile from standby to primary will also work.

    Try
    Code:
    On Primary
    RMAN>sql 'alter database datafile 5 offline'
    
    on Standby
    RMAN>copy datafile '/odb/datafile/my.dbf' to '/tmp/my-copy.dbf'
    copy the datafile my-copy.dbf to primary server at /tmp/my-copy.dbf
    
    
    On Primary
    RMAN>catalog datafilecopy '/tmp/my-copy.dbf'
    RMAN>switch datafile 5 to COPY
    RMAN>recover datafile 5;
    RMAN> sql 'alter database datafile 5 online';

  3. #3
    Join Date
    Dec 2001
    Posts
    337
    Thanks Hrishy!

    On my standby the files in located in a directory which is different from the live db, hence do I need to run a set newname before the switch?

    Thanks in advance,
    Chucks

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Nope since you are copying the file from standby to primary setnewname is not required.

    The catalog command will tell rman where to look for the file on the primary.

    regards
    Hrishy

  5. #5
    Join Date
    Dec 2001
    Posts
    337
    Thanks Hrishy,

    I just realised, the steps you have given me will still mean that the copy of the datafile will reside in the same diskgroup on the live db. Because the LUN is suspect, will be able to move the datafile again to another diskgroup. In your steps can we restore to copy to another diskgroup straight away?

    Thanks in advance

  6. #6
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    I gave you a general outline you can copy the datafile from the standby to anywhere on the primary server.

    For example you are copying the datafile to /tmp/my-copy.dbf using OS commands.

    You can for example do this after you have added the datafile 5 to a filesystem from standby .

    Code:
    RMAN> REPORT SCHEMA;
    RMAN> BACKUP AS COPY DATAFILE 5 FORMAT '+TEST';
    RMAN> switch datafile 5 to COPY;
    RMAN> REPORT SCHEMA;
    At the end of abouve steps you have moved the datafile to ASM diskgroup

    If you want to copy the datafile from standby directly to ASM diskgroup on the primary server then you need to use dbms_file_transfer and copy the datafile over to that diskgroup


    regards
    Hrishy
    Last edited by hrishy; 03-04-2008 at 06:52 AM.

  7. #7
    Join Date
    Dec 2001
    Posts
    337
    Hi Hrishy,

    Thanks for that, just so i am clear the steps then would be:

    Code:
    On Primary
    RMAN>sql 'alter database datafile 5 offline'
    
    on Standby
    RMAN>copy datafile '/odb/datafile/my.dbf' to '/tmp/my-copy.dbf'
    copy the datafile my-copy.dbf to primary server at /tmp/my-copy.dbf
    
    
    On Primary
    RMAN>catalog datafilecopy '/tmp/my-copy.dbf'
    RMAN> BACKUP AS COPY DATAFILE 5 FORMAT '+TEST'; <- new ASM diskgroup for datafile
    RMAN> switch datafile 5 to COPY;
    RMAN>recover datafile 5;
    RMAN> sql 'alter database datafile 5 online';

    Thank you for all your help!

  8. #8
    Join Date
    Jan 2001
    Posts
    2,828
    Hi Chucks

    I havent validated these but my bet is that the steps should be something like this

    Code:
    On Primary
    RMAN>sql 'alter database datafile 5 offline'
    
    on Standby
    RMAN>copy datafile '/odb/datafile/my.dbf' to '/tmp/my-copy.dbf'
    copy the datafile my-copy.dbf to primary server at /tmp/my-copy.dbf
    
    
    RMAN>catalog datafilecopy '/tmp/my-copy.dbf'
    RMAN>switch datafile 5 to COPY;
    RMAN>report schema;
    
    above step would switch the datafile to the copy that is present in the filesystem.
    
    RMAN> BACKUP AS COPY DATAFILE 5 FORMAT '+TEST'; -->copy to the ASM diskgroup
    RMAN>report schema; 
    
    RMAN>switch datafile 5 to COPY;        -->point to the copy in the ASM filesystem
    RMAN>recover datafile 5;
    RMAN> sql 'alter database datafile 5 online';
    
    RMAN>report schema                        -->Verify that file 5 is in the ASM diskgroup
    check it out and let me know how it goes

    The report schema statements are there just to make you feel comfortable that you verify that each step you are on the right track

    regards
    Hrishy
    Last edited by hrishy; 03-04-2008 at 10:24 AM.

  9. #9
    Join Date
    Dec 2001
    Posts
    337
    Hi Hrishy,

    Thank you very much for that. That has worked fine!

    Thanks again,
    Chucks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width