6 Replies Latest reply: Jun 25, 2012 1:53 AM by Talip Hakan Ozturk RSS

    Restore RMAN ASM backup to a regular filesystem server

    InquisitiveDBA
      Hi,

      I have a question about ASM managed backups. We have a 2 node RAC (11g on Windows) and the RMAN backups are stored in ASM (+FRA) location. I also backup one node using regular file system (D:\FRA) .. I have duplicated the database (like a pseudo standby) into another server (TESTSERV) same database (11g) same OS (Windows). We do this for testing purposes. TESTSERV does not have any ASM disks, it is a normal box with regular NTFS disks.

      Now, the management wants to restore NOT the regular file system (D:\FRA) files into the TESTSERV BUT they want to restore the ASM backups (+FRA) into the TESTSERV database. So basically, they just want to prove that the +FRA backups are "restorable" into another machine.

      I advise them that it is not possible.. Since we cannot "copy" and "paste" +FRA files into TESTSERV.

      Am i correct? If not, how can I do their request?

      Thx
        • 1. Re: Restore RMAN ASM backup to a regular filesystem server
          Andy Klock
          Look into asmcmd cp command:

          http://docs.oracle.com/cd/E11882_01/server.112/e18951/asm_util003.htm

          Good luck.
          • 2. Re: Restore RMAN ASM backup to a regular filesystem server
            InquisitiveDBA
            No luck...

            its not working.... got different errors:

            ORA-19505: failed to identify file "C:\RMAN\con.ctl"
            ORA-27040: file create error, unable to create file
            OSD-04002: unable to open file
            O/S-Error: (OS 6) The handle is invalid.
            ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 413
            ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)
            • 3. Re: Restore RMAN ASM backup to a regular filesystem server
              Andy Klock
              Can you provide the command you ran?

              I ran a simple test, but I'm running ASM on an Linux VM guest. Though I don't see how Windows would behave any differently as long as it has permissions on the target directory.
              ASMCMD> cd +DATA/ASM/CONTROLFILE
              ASMCMD> ls 
              current.275.765465817.test.272.771158129
              ASMCMD> cp current.275.765465817.test.272.771158129 /tmp
              copying +DATA/ASM/CONTROLFILE/current.275.765465817.test.272.771158129 -> /tmp/current.275.765465817.test.272.771158129
              ASMCMD> exit
              
              [oracle@bbh ~]$ ls -l /tmp/current.275.765465817.test.272.771158129
              -rw-r----- 1 oracle oinstall 10240000 Jun 23 00:20 /tmp/current.275.765465817.test.272.771158129
              • 4. Re: Restore RMAN ASM backup to a regular filesystem server
                Andy Klock
                Actually, after thinking about your problem a little more. asmcmd does allow you to copy files from ASM to local, but unless you know exactly what you need and script it to run one at a time (cp doesn't understand wildcards), even then it probably isn't the right tool for you.

                RMAN can make copies of backups so you can use that functionality here too (probably a better choice):

                http://docs.oracle.com/cd/E14072_01/backup.112/e10642/rcmcncpt.htm

                I did a simple test on that same VM:
                SQL> sho parameter db_rec
                
                NAME                                 TYPE        VALUE
                ------------------------------------ ----------- ------------------------------
                db_recovery_file_dest                string      +DATA
                db_recovery_file_dest_size           big integer 4G
                db_recycle_cache_size                big integer 0
                SQL> exit
                Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
                With the Partitioning, Automatic Storage Management, OLAP, Data Mining
                and Real Application Testing options
                [oracle@bbh ~]$ rman target / 
                
                Recovery Manager: Release 11.2.0.2.0 - Production on Sat Jun 23 07:29:53 2012
                
                Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
                
                connected to target database: ORA11GR2 (DBID=54333592)
                
                RMAN> backup database plus archivelog tag asm_to_local;
                
                
                Starting backup at 23-JUN-12
                current log archived
                using target database control file instead of recovery catalog
                allocated channel: ORA_DISK_1
                channel ORA_DISK_1: SID=29 device type=DISK
                channel ORA_DISK_1: starting archived log backup set
                channel ORA_DISK_1: specifying archived log(s) in backup set
                input archived log thread=1 sequence=510 RECID=467 STAMP=753118395
                input archived log thread=1 sequence=511 RECID=468 STAMP=753118433
                input archived log thread=1 sequence=679 RECID=472 STAMP=786698940
                input archived log thread=1 sequence=680 RECID=473 STAMP=786699128
                channel ORA_DISK_1: starting piece 1 at 23-JUN-12
                channel ORA_DISK_1: finished piece 1 at 23-JUN-12
                piece handle=+DATA/ora11gr2/backupset/2012_06_23/annnf0_asm_to_local_0.279.786699129 tag=ASM_TO_LOCAL comment=NONE
                channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16
                Finished backup at 23-JUN-12
                
                Starting backup at 23-JUN-12
                using channel ORA_DISK_1
                channel ORA_DISK_1: starting full datafile backup set
                channel ORA_DISK_1: specifying datafile(s) in backup set
                input datafile file number=00001 name=+DATA/ora11gr2/datafile/system.273.765461261
                input datafile file number=00006 name=+DATA/ora11gr2/datafile/undotbs.274.765461261
                input datafile file number=00002 name=+DATA/ora11gr2/datafile/sysaux.293.765461261
                input datafile file number=00004 name=+DATA/ora11gr2/datafile/users.266.765461573
                input datafile file number=00005 name=+DATA/ora11gr2/datafile/apex.265.765461783
                input datafile file number=00003 name=+DATA/ora11gr2/datafile/rep_tbl.289.765461793
                channel ORA_DISK_1: starting piece 1 at 23-JUN-12
                channel ORA_DISK_1: finished piece 1 at 23-JUN-12
                piece handle=+DATA/ora11gr2/backupset/2012_06_23/nnndf0_tag20120623t073225_0.280.786699145 tag=TAG20120623T073225 comment=NONE
                channel ORA_DISK_1: backup set complete, elapsed time: 00:01:35
                Finished backup at 23-JUN-12
                
                Starting backup at 23-JUN-12
                current log archived
                using channel ORA_DISK_1
                channel ORA_DISK_1: starting archived log backup set
                channel ORA_DISK_1: specifying archived log(s) in backup set
                input archived log thread=1 sequence=681 RECID=474 STAMP=786699241
                channel ORA_DISK_1: starting piece 1 at 23-JUN-12
                channel ORA_DISK_1: finished piece 1 at 23-JUN-12
                piece handle=+DATA/ora11gr2/backupset/2012_06_23/annnf0_asm_to_local_0.282.786699241 tag=ASM_TO_LOCAL comment=NONE
                channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
                Finished backup at 23-JUN-12
                
                Starting Control File and SPFILE Autobackup at 23-JUN-12
                piece handle=+DATA/ora11gr2/autobackup/2012_06_23/s_786699242.283.786699243 comment=NONE
                Finished Control File and SPFILE Autobackup at 23-JUN-12
                
                RMAN> 
                RMAN> backup backupset all format '/tmp/%U';
                
                Starting backup at 23-JUN-12
                using channel ORA_DISK_1
                channel ORA_DISK_1: input backup set: count=7, stamp=765461818, piece=1
                channel ORA_DISK_1: starting piece 1 at 23-JUN-12
                skipping backup piece handle +DATA/ora11gr2/autobackup/2011_10_25/s_765460400.291.765461827; already exists
                channel ORA_DISK_1: finished piece 1 at 23-JUN-12
                channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:01
                channel ORA_DISK_1: input backup set: count=9, stamp=786699129, piece=1
                channel ORA_DISK_1: starting piece 1 at 23-JUN-12
                channel ORA_DISK_1: backup piece +DATA/ora11gr2/backupset/2012_06_23/annnf0_asm_to_local_0.279.786699129
                piece handle=/tmp/09ne84rp_1_2 comment=NONE
                channel ORA_DISK_1: finished piece 1 at 23-JUN-12
                channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:26
                channel ORA_DISK_1: input backup set: count=10, stamp=786699145, piece=1
                channel ORA_DISK_1: starting piece 1 at 23-JUN-12
                channel ORA_DISK_1: backup piece +DATA/ora11gr2/backupset/2012_06_23/nnndf0_tag20120623t073225_0.280.786699145
                piece handle=/tmp/0ane84s9_1_2 comment=NONE
                channel ORA_DISK_1: finished piece 1 at 23-JUN-12
                channel ORA_DISK_1: backup piece complete, elapsed time: 00:01:25
                channel ORA_DISK_1: input backup set: count=11, stamp=786699241, piece=1
                channel ORA_DISK_1: starting piece 1 at 23-JUN-12
                channel ORA_DISK_1: backup piece +DATA/ora11gr2/backupset/2012_06_23/annnf0_asm_to_local_0.282.786699241
                piece handle=/tmp/0bne84v9_1_2 comment=NONE
                channel ORA_DISK_1: finished piece 1 at 23-JUN-12
                channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:01
                channel ORA_DISK_1: input backup set: count=12, stamp=786699242, piece=1
                channel ORA_DISK_1: starting piece 1 at 23-JUN-12
                skipping backup piece handle +DATA/ora11gr2/autobackup/2012_06_23/s_786699242.283.786699243; already exists
                channel ORA_DISK_1: finished piece 1 at 23-JUN-12
                channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:00
                Finished backup at 23-JUN-12
                
                Starting Control File and SPFILE Autobackup at 23-JUN-12
                piece handle=+DATA/ora11gr2/autobackup/2012_06_23/s_786700620.259.786700623 comment=NONE
                Finished Control File and SPFILE Autobackup at 23-JUN-12
                
                RMAN> 
                RMAN> list backup;
                
                
                List of Backup Sets
                ===================
                
                
                BS Key  Type LV Size       Device Type Elapsed Time Completion Time
                ------- ---- -- ---------- ----------- ------------ ---------------
                1       Full    9.83M      DISK        00:00:11     25-OCT-11      
                        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20111025T121658
                        Piece Name: +DATA/ora11gr2/autobackup/2011_10_25/s_765460400.291.765461827
                  SPFILE Included: Modification time: 25-OCT-11
                  SPFILE db_unique_name: ORA11GR2
                  Control File Included: Ckp SCN: 11853034     Ckp time: 25-OCT-11
                
                BS Key  Size
                ------- ----------
                2       236.07M
                
                  List of Archived Logs in backup set 2
                  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
                  ---- ------- ---------- --------- ---------- ---------
                  1    510     7870917    06-JUN-11 7929237    06-JUN-11
                  1    511     7929237    06-JUN-11 7929829    06-JUN-11
                  1    679     11935205   12-JAN-12 11960597   23-JUN-12
                  1    680     11960597   23-JUN-12 11961170   23-JUN-12
                
                  Backup Set Copy #1 of backup set 2
                  Device Type Elapsed Time Completion Time Compressed Tag
                  ----------- ------------ --------------- ---------- ---
                  DISK        00:00:10     23-JUN-12       NO         ASM_TO_LOCAL
                
                    List of Backup Pieces for backup set 2 Copy #1
                    BP Key  Pc# Status      Piece Name
                    ------- --- ----------- ----------
                    2       1   AVAILABLE   +DATA/ora11gr2/backupset/2012_06_23/annnf0_asm_to_local_0.279.786699129
                
                  Backup Set Copy #2 of backup set 2
                  Device Type Elapsed Time Completion Time Compressed Tag
                  ----------- ------------ --------------- ---------- ---
                  DISK        00:00:10     23-JUN-12       NO         ASM_TO_LOCAL
                
                    List of Backup Pieces for backup set 2 Copy #2
                    BP Key  Pc# Status      Piece Name
                    ------- --- ----------- ----------
                    6       1   AVAILABLE   /tmp/09ne84rp_1_2
                
                BS Key  Type LV Size
                ------- ---- -- ----------
                3       Full    1.27G
                  List of Datafiles in backup set 3
                  File LV Type Ckp SCN    Ckp Time  Name
                  ---- -- ---- ---------- --------- ----
                  1       Full 11961184   23-JUN-12 +DATA/ora11gr2/datafile/system.273.765461261
                  2       Full 11961184   23-JUN-12 +DATA/ora11gr2/datafile/sysaux.293.765461261
                  3       Full 11961184   23-JUN-12 +DATA/ora11gr2/datafile/rep_tbl.289.765461793
                  4       Full 11961184   23-JUN-12 +DATA/ora11gr2/datafile/users.266.765461573
                  5       Full 11961184   23-JUN-12 +DATA/ora11gr2/datafile/apex.265.765461783
                  6       Full 11961184   23-JUN-12 +DATA/ora11gr2/datafile/undotbs.274.765461261
                
                  Backup Set Copy #1 of backup set 3
                  Device Type Elapsed Time Completion Time Compressed Tag
                  ----------- ------------ --------------- ---------- ---
                  DISK        00:01:29     23-JUN-12       NO         TAG20120623T073225
                
                    List of Backup Pieces for backup set 3 Copy #1
                    BP Key  Pc# Status      Piece Name
                    ------- --- ----------- ----------
                    3       1   AVAILABLE   +DATA/ora11gr2/backupset/2012_06_23/nnndf0_tag20120623t073225_0.280.786699145
                
                  Backup Set Copy #2 of backup set 3
                  Device Type Elapsed Time Completion Time Compressed Tag
                  ----------- ------------ --------------- ---------- ---
                  DISK        00:01:29     23-JUN-12       NO         TAG20120623T073225
                
                    List of Backup Pieces for backup set 3 Copy #2
                    BP Key  Pc# Status      Piece Name
                    ------- --- ----------- ----------
                    7       1   AVAILABLE   /tmp/0ane84s9_1_2
                
                BS Key  Size
                ------- ----------
                4       5.00K
                
                  List of Archived Logs in backup set 4
                  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
                  ---- ------- ---------- --------- ---------- ---------
                  1    681     11961170   23-JUN-12 11961220   23-JUN-12
                
                  Backup Set Copy #1 of backup set 4
                  Device Type Elapsed Time Completion Time Compressed Tag
                  ----------- ------------ --------------- ---------- ---
                  DISK        00:00:00     23-JUN-12       NO         ASM_TO_LOCAL
                
                    List of Backup Pieces for backup set 4 Copy #1
                    BP Key  Pc# Status      Piece Name
                    ------- --- ----------- ----------
                    4       1   AVAILABLE   +DATA/ora11gr2/backupset/2012_06_23/annnf0_asm_to_local_0.282.786699241
                
                  Backup Set Copy #2 of backup set 4
                  Device Type Elapsed Time Completion Time Compressed Tag
                  ----------- ------------ --------------- ---------- ---
                  DISK        00:00:00     23-JUN-12       NO         ASM_TO_LOCAL
                
                    List of Backup Pieces for backup set 4 Copy #2
                    BP Key  Pc# Status      Piece Name
                    ------- --- ----------- ----------
                    8       1   AVAILABLE   /tmp/0bne84v9_1_2
                
                BS Key  Type LV Size       Device Type Elapsed Time Completion Time
                ------- ---- -- ---------- ----------- ------------ ---------------
                5       Full    9.83M      DISK        00:00:03     23-JUN-12      
                        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20120623T073402
                        Piece Name: +DATA/ora11gr2/autobackup/2012_06_23/s_786699242.283.786699243
                  SPFILE Included: Modification time: 23-JUN-12
                  SPFILE db_unique_name: ORA11GR2
                  Control File Included: Ckp SCN: 11961229     Ckp time: 23-JUN-12
                
                BS Key  Type LV Size       Device Type Elapsed Time Completion Time
                ------- ---- -- ---------- ----------- ------------ ---------------
                6       Full    9.83M      DISK        00:00:04     23-JUN-12      
                        BP Key: 9   Status: AVAILABLE  Compressed: NO  Tag: TAG20120623T075700
                        Piece Name: +DATA/ora11gr2/autobackup/2012_06_23/s_786700620.259.786700623
                  SPFILE Included: Modification time: 23-JUN-12
                  SPFILE db_unique_name: ORA11GR2
                  Control File Included: Ckp SCN: 11962776     Ckp time: 23-JUN-12
                
                RMAN> 
                
                
                there they are
                [oracle@bbh ~]$ ls -l /tmp/0*_1_2 -rw-r----- 1 oracle oinstall  247539712 Jun 23 07:55 /tmp/09ne84rp_1_2 -rw-r----- 1 oracle oinstall 1367449600 Jun 23 07:56 /tmp/0ane84s9_1_2 -rw-r----- 1 oracle oinstall       5632 Jun 23 07:56 /tmp/0bne84v9_1_2 [oracle@bbh ~]$
                Good luck.
                • 5. Re: Restore RMAN ASM backup to a regular filesystem server
                  Paul M.
                  Now, the management wants to restore NOT the regular file system (D:\FRA) files into the TESTSERV BUT they want to restore the ASM backups (+FRA) into the TESTSERV database.
                  And what's the difference between them ?
                  • 6. Re: Restore RMAN ASM backup to a regular filesystem server
                    Talip Hakan Ozturk
                    You can copy backup files between ASM environments using dbms_file_transfer package. Check this doc.

                    http://taliphakanozturken.wordpress.com/2012/06/23/how-to-copy-backup-files-from-an-asm-environment-to-another-asm/


                    Talip Hakan Ozturk
                    http://taliphakanozturken.wordpress.com/