12 Replies Latest reply: Oct 19, 2012 12:54 AM by Nonuday RSS

    ASM RMAN backup to File System

    Nonuday
      Hi all,

      I have a rman backup (datafile and controlfile) which was took in an ASM instance (not a RAC) ORACLE 11.2.0.2 in a Linux server, now I want restore the backup in a new database in windows/Linux OS using general File System storage (single instance rdbms) instead of ASM.

      Is this possible?

      Can I restrore an ASM rman backup in a file system storage mechanisim in a new server?

      Kindly clarify my question.

      Thanks in Advance..

      Nonuday
        • 2. Re: ASM RMAN backup to File System
          Nonuday
          Hi sunny,

          Thanks for the link and your reply.

          The problem is I have only backup with me and I dont have the ASM isntance here or acsess for that.

          So it would be great if I could know whether the RMAN backup of ASM would be restored in General File System.


          Thanks,
          Nonuday
          • 3. Re: ASM RMAN backup to File System
            Sebastian Solbach -Dba Community-Oracle
            Hi,

            yes you can restore a backup of ASM onto another filesystem.

            Simply use the "set newname" command during restore (set newname for database or set newname for datafile etc.).

            If you can read german see here:

            http://www.oracle.com/webfolder/technetwork/de/community/dbadmin/tipps/RMAN_II_Restore/index.html

            Regards
            Sebastian
            • 4. Re: ASM RMAN backup to File System
              Nonuday
              Hi Sebasatian,

              Thanks for the help and solution, and I too found that using set newname for the datafiles will allow the datafiles to get relocated/renamed.

              After restoring datafiles, when I tried to open database I got redo log error then eventually I renamed the logfiles (more than 3 groups).

              Now am not using this new restored Database in archivelog mode and can I avoid having large number of log groups because the database has around 30 redolog files.

              Thanks,
              Nonuday
              • 5. Re: ASM RMAN backup to File System
                j.a.motta
                Hi Nonuday,

                You must have at least 2 redo log groups, but leave at least three groups.

                To reduce the number of redo logs:
                - Check the redo log groups
                SELECT group#, archived, members, status from V$LOG;
                (the members tell you the number of copies of the redo log)
                - Drop some redo log groups:
                ALTER DATABASE DROP LOGFILE group#;
                (change group# with a group# with STATUS=UNUSED or STATUS=INACTIVE and ARCHIVED=YES)

                Regards,

                Jose.
                • 6. Re: ASM RMAN backup to File System
                  Levi Pereira
                  Hi,
                  Another way to accomplish this work (not using OMF).

                  http://levipereira.wordpress.com/2011/09/16/how-migrate-asm-to-non-asm-unixlinux/
                  • 7. Re: ASM RMAN backup to File System
                    Nonuday
                    Hi Motta,

                    Thanks for the suggestion.. :)
                    • 8. Re: ASM RMAN backup to File System
                      Nonuday
                      Hi Levi,

                      Thanks for your invaluable script and blog.

                      can you clarify me on this query:

                      I have a RMAN backup taken from ASM and the backup is database and controlf file backup which contains datafiles and controlfiles.
                      Now I need to restore this on my system and here I dont use ASM or archive log, I use single instance in no archive log mode database.

                      I have restored the control file from the RMAN controfile backup.
                      Before restoring the control file I have checked the orginal pfile of the backup database which had parameters like
                      'db_create_file_dest',
                      'db_create_online_log_dest',
                      'db_recovery_file_dest_size',
                      'db_recovery_dest',
                      'log_archive_dest'.

                      Since I am not gng to create a DB in no archive log mode, I didnt use any of the above parameters and created a database.

                      Now my question is:
                      If i restore the database and the datafile will get restored and after renaming all the logfiles, database will be opened.

                      I want to know whether this method is correct or wrong and will the database work as it was working previously. Or do i need create the db_file_recovery and other parameters also for this database.

                      Kindly help me on this..

                      Thanks,
                      nonuday.
                      • 9. Re: ASM RMAN backup to File System
                        Shivananda Rao
                        Hi,

                        If your new database would not be using ASM and do not want to use the Flash Recovery Area, then there is no requirement for you to have these parameters.

                        But at the time of restoration, you'll have to use the "set newname" clause to rename the datafiles to the location where they would be stored on the filesystem.
                        run
                        {
                        set newname for datafile 1 to '<datafile name with full path of the desired location>'; # eg: set newname for datafile 1 to '/u01/mydb/system01.dbf';
                        set newname for datafile 2 to '<datafile name with full path of the desired location>';
                        .
                        .
                        restore database;
                        switch datafile all;
                        recover database;
                        }
                        • 10. Re: ASM RMAN backup to File System
                          Levi Pereira
                          Nonuday wrote:
                          Hi Levi,

                          Thanks for your invaluable script and blog.

                          can you clarify me on this query:

                          I have a RMAN backup taken from ASM and the backup is database and controlf file backup which contains datafiles and controlfiles.
                          Now I need to restore this on my system and here I dont use ASM or archive log, I use single instance in no archive log mode database.

                          I have restored the control file from the RMAN controfile backup.
                          Before restoring the control file I have checked the orginal pfile of the backup database which had parameters like
                          'db_create_file_dest',
                          'db_create_online_log_dest',
                          'db_recovery_file_dest_size',
                          'db_recovery_dest',
                          'log_archive_dest'.

                          Since I am not gng to create a DB in no archive log mode, I didnt use any of the above parameters and created a database.

                          Now my question is:
                          If i restore the database and the datafile will get restored and after renaming all the logfiles, database will be opened.

                          I want to know whether this method is correct or wrong and will the database work as it was working previously. Or do i need create the db_file_recovery and other parameters also for this database.
                          About Parameter:
                          All these parameters should reflect your current environment any reference to the old environment must be modified.

                          About Filesystem used:
                          Does not matter what Filesystem you are using the File (datafile/redolog/controlfile/archivelog/backuppiece) are created on Binary Format which depend on Platform only. So, The same binary file ( e.g datafile) have same format and content on raw device, ASM, ext3, ext2, and so on. So, to database it's only a location where file are stored, but the file are the same. ASM has a different architecture from Regular Filesystem and need be managed in a different manner (i.e using RMAN).

                          About Database:
                          Since your database files are the same even using different filesystem what you need is rename your datafiles/redofiles on controlfile during restore, the redo files will be recreated.
                          So, does not matter if you database are noarchivelog or archivelog, the same way which you will do a restore on ASM is the same way to restore on Regular Filesystem. (it's only about renaming database file on controlfile during restore)

                          On blog the post "How Migrate All Files on ASM to Non-ASM (Unix/Linux)" is about move the file from filesystem to another. But you can modify the script used to restore purposes;
                          ## set newname tell to RMAN where file will be restored and keep this files location on memory buffer
                          RMAN> set newname for datafile 1 to <location>;
                          
                          ### swich get list of files from memory buffer (rman) and rename on controlfile the files already restored.
                          RMAN>switch datafile/tempfile all ;
                          With database mounted use this script below:

                          I just commented three lines that are unnecessary in your case.
                          SET serveroutput ON;
                          DECLARE
                            vcount  NUMBER:=0;
                            vfname VARCHAR2(1024);
                            CURSOR df
                            IS
                              SELECT file#,
                                rtrim(REPLACE(name,'+DG_DATA/drop/datafile/','/u01/app/oracle/oradata/drop/'),'.0123456789') AS name
                              FROM v$datafile;
                            CURSOR tp
                            IS
                              SELECT file#,
                                rtrim(REPLACE(name,'+DG_DATA/drop/tempfile/','/u01/app/oracle/oradata/drop/'),'.0123456789') AS name
                              FROM v$tempfile;
                          BEGIN
                          --  dbms_output.put_line('CONFIGURE CONTROLFILE AUTOBACKUP ON;'); ### commented 
                            FOR dfrec IN df
                            LOOP
                              IF dfrec.name  != vfname THEN
                                vcount      :=1;
                                vfname     := dfrec.name;
                              ELSE
                                vcount := vcount+1;
                                vfname:= dfrec.name;
                              END IF;
                            --  dbms_output.put_line('backup as copy datafile ' || dfrec.file# ||' format  "'||dfrec.name ||vcount||'.dbf";');  ### commented 
                            END LOOP;
                            dbms_output.put_line('run');
                            dbms_output.put_line('{');
                            FOR dfrec IN df
                            LOOP
                              IF dfrec.name  != vfname THEN
                                vcount      :=1;
                                vfname     := dfrec.name;
                              ELSE
                                vcount := vcount+1;
                                vfname:= dfrec.name;
                              END IF;
                              dbms_output.put_line('set newname for datafile ' || dfrec.file# ||'  to  '''||dfrec.name ||vcount||'.dbf'' ;');
                            END LOOP;
                            FOR tprec IN tp
                            LOOP
                              IF tprec.name  !=  vfname THEN
                                vcount      :=1;
                                vfname     := tprec.name;
                              ELSE
                                vcount := vcount+1;
                                vfname:= tprec.name;
                              END IF;
                              dbms_output.put_line('set newname for tempfile ' || tprec.file# ||'  to  '''||tprec.name ||vcount||'.dbf'' ;');
                              END LOOP;
                                dbms_output.put_line('restore database;');
                              dbms_output.put_line('switch tempfile all;');
                              dbms_output.put_line('switch datafile all;');
                              dbms_output.put_line('recover database;');
                              dbms_output.put_line('}');
                           ---   dbms_output.put_line('alter database open;');  ### comented because you need rename your redologs on controlfile before open database
                              dbms_output.put_line('exit');
                          END;
                          /
                          After restore you must rename your redologs on controlfile from old location to new location:

                          e.g
                          ##  use this query to get current location of redolog
                          SQL>  select group#,member from v$logfile order by 1;
                          
                          ## and change from <old_location> to <new_location>
                          
                          SQL > ALTER DATABASE 
                            RENAME FILE '+DG_TSM_DATA/tsm/onlinelog/group_3.263.720532229'  
                                     TO  '/u01/app/oracle/oradata/logs/log3a.rdo'  
                          When you change all redolog on controlfile issue command below:
                          SQL> alter database open resetlogs;
                          PS: Always track database in real time using alert log file of database.


                          HTH,
                          Levi Pereira
                          • 11. Re: ASM RMAN backup to File System
                            Nonuday
                            Hi Shiva,

                            Thanks alot for your answer... :)


                            nonuday..
                            • 12. Re: ASM RMAN backup to File System
                              Nonuday
                              Hi Levi,

                              Many thanks for taking your time and explaining the things.
                              Once it get restored will let you know...

                              Thanks,
                              Nonuday