10 Replies Latest reply on Dec 10, 2018 1:50 PM by 3804456

    rman restore

    3804456

      Hi,

       

      i wanted to do rman restore to a new server in 12c databases, all the backups either stored on tape or disk.

       

      how do i list available backup of control file and its location ? like listing backups available as mon, tue, wed, --- sun and their location

      same thing with available database backups either level 0 or level 1 and their location ?

       

      same with archive log/

       

       

      when i do list backup of control file ===== its giving multiple entries with multiple dates and some times multiple entries with same day.

       

      is there any way from sql scripts or so i can get all these details.

       

      basically we want to automate the restore process, for that we want to list out available backups and with their sizes as well.

        • 1. Re: rman restore
          WadhahDaouehi

          Hi,

           

          You should make a full backup, and then copy it to your new server and restore it, check my article I guess can help you and just stop at the step 9.

          Check from 4. Steps of Oracle Database 12c Restore , Upgrade Oracle database from 11.2.0.4 to 12c (12.1.0.2) using the RMAN backup | Wadhah DAOUEHI

           

          Best regards

          • 2. Re: rman restore
            CristianR-Oracle

            Did you tried a specific SQL and did not work or you want us to give you the SQL.

             

            Did you look at various RMAN views, such as: v$backup_set, v$backup_set_details, v$backup_piece, etc ?

            • 3. Re: rman restore
              3804456

              I haven't checked basically i am looking from a sql script or any other ways i need to list available control file backups, spfile backups, database backups if level 0 or incremental.

               

              for suppose if i have control file backups on dec 1,2,3,4,5

               

              i wanted to restore to dec 3 rd then i will choose dec3rd control file so based on that then it should list out available level 0, incrremental and archive backups.

               

              i am planning to write a script but now i am looking for some views or tables where i can see the backup infirmation with columns like controlfile, or spfile or level 0 or level 1 etc....

              • 4. Re: rman restore
                AJ

                Why care about the names of the backuppieces and their types? The thing that matters is the time you want to recover to, and RMAN will take care of the rest (as long as the other server has access to the backups).

                What is that you are trying to achieve? Are you testing your backup/recovery strategy or are you just needing a copy of the database for some other purposes? Then you should probably look into RMAN duplicate instead.

                 

                 

                AJ

                • 5. Re: rman restore
                  3804456

                  not duplicate that i am looking here.

                   

                  i wanted to restore a database to new server directly from catalog and it sholud not connect to source database.

                   

                  when it connect to catalog then it should look for exsting backups and if they are in proper place

                   

                  so that restore can go based on that..

                   

                  yes its like point in time.

                  • 6. Re: rman restore
                    3804456

                    for suppose I got results like this... now i am looking if there is any way i can get these results in some view like that

                     

                    so based on that i will decide to which time i need to recover if its valid backup

                     

                    RMAN> list backup of controlfile;

                     

                    using target database control file instead of recovery catalog

                     

                    List of Backup Sets

                    ===================

                     

                     

                    BS Key  Type LV Size       Device Type Elapsed Time Completion Time

                    ------- ---- -- ---------- ----------- ------------ ---------------

                    69733   Full    24.00M     SBT_TAPE    00:00:01     06-DEC-18     

                            BP Key: 69733   Status: AVAILABLE  Compressed: NO  Tag: TAG20181206T233146

                            Handle: c-2229393364-20181206-09   Media: OracleRMAN

                      Control File Included: Ckp SCN: 1063226939   Ckp time: 06-DEC-18

                     

                    BS Key  Type LV Size       Device Type Elapsed Time Completion Time

                    ------- ---- -- ---------- ----------- ------------ ---------------

                    69751   Full    24.00M     SBT_TAPE    00:00:00     07-DEC-18     

                            BP Key: 69751   Status: AVAILABLE  Compressed: NO  Tag: TAG20181207T003420

                            Handle: c-2229393364-20181207-00   Media: OracleRMAN

                      Control File Included: Ckp SCN: 1063320799   Ckp time: 07-DEC-18

                     

                    BS Key  Type LV Size       Device Type Elapsed Time Completion Time

                    ------- ---- -- ---------- ----------- ------------ ---------------

                    69752   Full    24.00M     SBT_TAPE    00:00:01     07-DEC-18     

                            BP Key: 69752   Status: AVAILABLE  Compressed: NO  Tag: HOT_PR_DB_BK_LEVEL0_CTL

                            Handle: 5mtk4p5a_1_1   Media: OracleRMAN

                      Control File Included: Ckp SCN: 1063321298   Ckp time: 07-DEC-18

                     

                    BS Key  Type LV Size       Device Type Elapsed Time Completion Time

                    ------- ---- -- ---------- ----------- ------------ ---------------

                    69753   Full    24.00M     SBT_TAPE    00:00:01     07-DEC-18     

                            BP Key: 69753   Status: AVAILABLE  Compressed: NO  Tag: TAG20181207T003455

                            Handle: c-2229393364-20181207-01   Media: OracleRMAN

                      Control File Included: Ckp SCN: 1063321330   Ckp time: 07-DEC-18

                     

                    BS Key  Type LV Size       Device Type Elapsed Time Completion Time

                    ------- ---- -- ---------- ----------- ------------ ---------------

                    69761   Full    24.00M     SBT_TAPE    00:00:00     07-DEC-18     

                            BP Key: 69761   Status: AVAILABLE  Compressed: NO  Tag: TAG20181207T093040

                            Handle: c-2229393364-20181207-02   Media: OracleRMAN

                      Control File Included: Ckp SCN: 1063589923   Ckp time: 07-DEC-18

                     

                    BS Key  Type LV Size       Device Type Elapsed Time Completion Time

                    ------- ---- -- ---------- ----------- ------------ ---------------

                    69762   Full    24.00M     SBT_TAPE    00:00:01     07-DEC-18     

                            BP Key: 69762   Status: AVAILABLE  Compressed: NO  Tag: TAG20181207T093051

                            Handle: 60tk5oib_1_1   Media: OracleRMAN

                      Control File Included: Ckp SCN: 1063590004   Ckp time: 07-DEC-18

                     

                    BS Key  Type LV Size       Device Type Elapsed Time Completion Time

                    ------- ---- -- ---------- ----------- ------------ ---------------

                    69763   Full    24.00M     SBT_TAPE    00:00:31     07-DEC-18     

                            BP Key: 69763   Status: AVAILABLE  Compressed: NO  Tag: TAG20181207T093053

                            Handle: c-2229393364-20181207-03   Media: OracleRMAN

                      Control File Included: Ckp SCN: 1063590018   Ckp time: 07-DEC-18

                     

                    BS Key  Type LV Size       Device Type Elapsed Time Completion Time

                    ------- ---- -- ---------- ----------- ------------ ---------------

                    69764   Full    24.00M     SBT_TAPE    00:00:02     07-DEC-18     

                            BP Key: 69764   Status: AVAILABLE  Compressed: NO  Tag: TAG20181207T093527

                            Handle: 62tk5or0_1_1   Media: OracleRMAN

                      Control File Included: Ckp SCN: 1063592476   Ckp time: 07-DEC-18

                     

                    BS Key  Type LV Size       Device Type Elapsed Time Completion Time

                    ------- ---- -- ---------- ----------- ------------ ---------------

                    69765   Full    24.00M     SBT_TAPE    00:00:00     07-DEC-18     

                            BP Key: 69765   Status: AVAILABLE  Compressed: NO  Tag: TAG20181207T093532

                            Handle: c-2229393364-20181207-04   Media: OracleRMAN

                      Control File Included: Ckp SCN: 1063592511   Ckp time: 07-DEC-18

                     

                    BS Key  Type LV Size       Device Type Elapsed Time Completion Time

                    ------- ---- -- ---------- ----------- ------------ ---------------

                    69769   Full    24.00M     SBT_TAPE    00:00:00     07-DEC-18     

                            BP Key: 69769   Status: AVAILABLE  Compressed: NO  Tag: TAG20181207T094114

                            Handle: c-2229393364-20181207-05   Media: OracleRMAN

                      Control File Included: Ckp SCN: 1063595497   Ckp time: 07-DEC-18

                    • 7. Re: rman restore
                      top.gun

                      If you just want to list what you have, you can do it like this:

                       

                      RMAN> list backup of controlfile completed before "to_date('20181203','yyyymmdd')";

                       

                      List of Backup Sets

                      ===================

                      BS Key  Type LV Size       Device Type Elapsed Time Completion Time

                      ------- ---- -- ---------- ----------- ------------ ---------------

                      227     Full    11.66M     DISK        00:00:02     30-NOV-18

                              BP Key: 309   Status: AVAILABLE  Compressed: NO  Tag: TAG20181130T233410

                              Piece Name: D:\FAST_RECOVERY_AREA\TEST\AUTOBACKUP\2018_11_30\O1_MF_S_993598450_G02P3NGF_.BKP

                        Control File Included: Ckp SCN: 13006713     Ckp time: 30-NOV-18

                      BS Key  Type LV Size       Device Type Elapsed Time Completion Time

                      ------- ---- -- ---------- ----------- ------------ ---------------

                      228     Full    16.00M     SBT_TAPE    00:00:02     30-NOV-18

                              BP Key: 314   Status: AVAILABLE  Compressed: NO  Tag: TAG20181130T233543

                              Handle: c-2273332160-20181130-01   Media: \oracle,c-2273332160-201811

                        Control File Included: Ckp SCN: 13007169     Ckp time: 30-NOV-18

                       

                      RMAN>

                       

                       

                      However if you just want Oracle to restore a backup from the 3rd Dec 2018, then just tell RMAN the date, and then RMAN decides which files it needs, so for example:

                       

                      run {

                        SET UNTIL TIME "to_date('20181203','yyyymmdd')";

                        restore controlfile to 'd:\controlfile.test.restore' from autobackup;

                      }

                       

                      So to restore and recover a whole database you'll do something similar.

                      • 8. Re: rman restore
                        Dude!

                        Why do you need such information? As you have noticed the info is not presented when doing "list backup". That's because there is usually no such requirement. Your questions normally arrive with conventional file system backup software, however, for managing your database with RMAN it is irrelevant. RMAN automatically decides the best restore option based on the existing inventory, including recovery using incremental level backups and archivelogs, and it is not really up to you to decide. Likewise RMAN uses a retention policy if you wish to manage obsolete backups.

                         

                        how do i list available backup of control file and its location

                        There are several options to backup the controlfile. Are you using controlfile autobackup? Whenever you backup the system tablespace the spfile and controlfile are included and stored in a separate RMAN backup piece. You can also see this with "list backup". Normally there is a "Piece Name", which shows you the file system location, or "Media" if you backup to tape.

                        • 9. Re: rman restore
                          CristianR-Oracle

                          Did you look at various RMAN views, such as: v$backup_set, v$backup_set_details, v$backup_piece, etc ?

                          1 person found this helpful
                          • 10. Re: rman restore
                            3804456

                            yes.... all i am trying is to setup job via jenkins in ansible.

                             

                            where i wanted to setup such a way that first lists available backups for controlfile, database, archivelogs

                             

                            (or)

                             

                            otherway if i use set until to point in time then before it starts restore it should validate all the backups are available or not before restore starts.

                             

                            sorry i am new to rman.... so just chekcing all options...