1 2 Previous Next 15 Replies Latest reply: Dec 16, 2013 12:25 PM by Actitud RSS

    Backups on Physical standby database?

    990636
      I am using dataguard and a physical standby database.How to assign backups on physical standby database to reduce the load on Primary database.

      environment: windows

      Database version:11.2.0.1.0


      What are the pro and cons on assigning RMAN backups jobs to physical standby database?
        • 1. Re: Backups on Physical standby database?
          CKPT
          987633 wrote:
          I am using dataguard and a physical standby database.How to assign backups on physical standby database to reduce the load on Primary database.

          environment: windows

          Database version:11.2.0.1.0


          What are the pro and cons on assigning RMAN backups jobs to physical standby database?
          Its good to have backup on standby to avoid much contention on primary database and of course on standby you have to cancel MRP during backup time,
          You can check more links
          http://www.oracle.com/technetwork/database/features/availability/298772-3-133273.pdf
          http://alexzeng.wordpress.com/2008/09/13/how-to-use-standby-backup-to-restore-primary-db/
          • 2. Re: Backups on Physical standby database?
            990636
            Thanks for the quick response.Daily backups are assigned daily in the night time,Do i need to off the MRP everytime i backup the database RMAN level 0,RMAN level 1,Archive logs?
            • 3. Re: Backups on Physical standby database?
              mseberg
              Hello;

              The standby database is a block identical copy of the primary database.

              Hence you only have to backup one of the both (you can decide to only backup the standby though, if you want to remove load on the primary). But if you are confident with the backup of the primary, there is no need for backing up the standby. (Since you can recreate the standby with the backup from primary).

              The only important part is, that you are using RMAN catalogue. Only then RMAN known, that both dbs are identical and can use the same backup.

              Best Regards

              mseberg
              • 4. Re: Backups on Physical standby database?
                mseberg
                Hello;

                The simple answer is no. RMAN will run fine on a standby without any change to Data Guard.

                Best Regards

                mseberg
                • 5. Re: Backups on Physical standby database?
                  990636
                  The answer is No for the MRP is to be cancelled during the RMAN backup? I do use a rman catalog.
                  • 6. Re: Backups on Physical standby database?
                    mseberg
                    Correct. I run RMAN daily on all my standby's without change to anything.

                    Simple test
                    RMAN> backup spfile;
                    
                    Starting backup at 21-FEB-13
                    allocated channel: ORA_DISK_1
                    channel ORA_DISK_1: SID=52 device type=DISK
                    channel ORA_DISK_1: starting full datafile backup set
                    channel ORA_DISK_1: specifying datafile(s) in backup set
                    including current SPFILE in backup set
                    channel ORA_DISK_1: starting piece 1 at 21-FEB-13
                    channel ORA_DISK_1: finished piece 1 at 21-FEB-13
                    piece handle=/u01/app/oracle/flash_recovery_area/STANDBY/backupset/2013_02_21/o1_mf_nnsnf_TAG20130221T113125_8ldphhgp_.bkp tag=TAG20130221T113125 comment=NONE
                    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
                    Finished backup at 21-FEB-13
                    
                    Starting Control File and SPFILE Autobackup at 21-FEB-13
                    piece handle=/u01/app/oracle/flash_recovery_area/STANDBY/autobackup/2013_02_21/o1_mf_s_807955216_8ldphlsd_.bkp comment=NONE
                    Finished Control File and SPFILE Autobackup at 21-FEB-13
                    
                    RMAN> 
                    Best Regards

                    mseberg

                    Edited by: mseberg on Feb 21, 2013 11:41 AM
                    • 7. Re: Backups on Physical standby database?
                      990636
                      Is it better to assign the RMAN backups on standby?i am using the RMAN catalog.

                      Do i need to change the setting to backup archive logs from standby database? If i won't backup the archive logs on Primary the FRA will be full and i can't access it ?

                      How to assign the archive logs backups,full backup on standby database?
                      • 8. Re: Backups on Physical standby database?
                        CKPT
                        Is it better to assign the RMAN backups on standby?i am using the RMAN catalog.
                        Yes, Already mentioned to reduce contention on primary. You can register even standby.
                        Do i need to change the setting to backup archive logs from standby database? If i won't backup the archive logs on Primary the FRA will be full and i can't access it ?
                        You can include even archive logs with your database backup.
                        How to assign the archive logs backups,full backup on standby database?
                        Same script will work, Before that cancel MRP and after backup start MRP. That should be fine.

                        Recently i tested backup from standby and restore, Please see below log.
                        =================================================================================================
                        Backup Scrpts:-
                        =================================================================================================
                        
                        RMAN> run
                        {
                        2> 3> ALLOCATE CHANNEL dev11 type disk;
                        4> backup as compressed backupset
                        5> format '/home/oracle/backup/DBF_FULLCOLD_BKP_%s'
                        6> filesperset 30
                        7> (database);
                        backup current controlfile format
                        8> 9> '/home/oracle/backup/CTL_FULLCOLD_BKP_%s';
                        RELEASE CHANNEL dev1;
                        RELEASE CHANNEL dev2;
                        10> 11> 12> }
                        
                        using target database control file instead of recovery catalog
                        allocated channel: dev11
                        channel dev11: SID=1 device type=DISK
                        
                        Starting backup at 03-FEB-13
                        Starting implicit crosscheck backup at 03-FEB-13
                        Finished implicit crosscheck backup at 03-FEB-13
                        
                        Starting implicit crosscheck copy at 03-FEB-13
                        Crosschecked 4 objects
                        Finished implicit crosscheck copy at 03-FEB-13
                        
                        searching for all files in the recovery area
                        channel dev11: starting compressed full datafile backup set
                        channel dev11: specifying datafile(s) in backup set
                        input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
                        input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
                        input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
                        input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
                        channel dev11: starting piece 1 at 03-FEB-13
                        
                        channel dev11: starting compressed full datafile backup set
                        channel dev11: specifying datafile(s) in backup set
                        input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
                        input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
                        input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
                        input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
                        channel dev11: starting piece 1 at 03-FEB-13
                        channel dev11: finished piece 1 at 03-FEB-13
                        piece handle=/home/oracle/backup/DBF_FULLCOLD_BKP_23 tag=TAG20130203T192535 comment=NONE
                        channel dev11: backup set complete, elapsed time: 00:03:57
                        channel dev11: starting compressed full datafile backup set
                        channel dev11: specifying datafile(s) in backup set
                        including current control file in backup set
                        channel dev11: starting piece 1 at 03-FEB-13
                        channel dev11: finished piece 1 at 03-FEB-13
                        piece handle=/home/oracle/backup/DBF_FULLCOLD_BKP_24 tag=TAG20130203T192535 comment=NONE
                        channel dev11: backup set complete, elapsed time: 00:00:02
                        Finished backup at 03-FEB-13
                        
                        Starting backup at 03-FEB-13
                        channel dev11: starting full datafile backup set
                        channel dev11: specifying datafile(s) in backup set
                        including current control file in backup set
                        channel dev11: starting piece 1 at 03-FEB-13
                        channel dev11: finished piece 1 at 03-FEB-13
                        piece handle=/home/oracle/backup/CTL_FULLCOLD_BKP_25 tag=TAG20130203T192939 comment=NONE
                        channel dev11: backup set complete, elapsed time: 00:00:03
                        Finished backup at 03-FEB-13
                        
                        released channel: dev11
                        
                        
                        =================================================================================================
                        Backup Files:-
                        =================================================================================================
                        
                        
                        [oracle@oracle-stby backup]$ ls -ltr
                        total 364572
                        -rw-r----- 1 oracle oinstall 361684992 Feb  3 19:29 DBF_FULLCOLD_BKP_23
                        -rw-r----- 1 oracle oinstall   1163264 Feb  3 19:29 DBF_FULLCOLD_BKP_24
                        -rw-r----- 1 oracle oinstall  10092544 Feb  3 19:29 CTL_FULLCOLD_BKP_25
                        [oracle@oracle-stby backup]$
                        
                        
                        =================================================================================================
                        Restore CONTROL FILE:-
                        =================================================================================================
                        
                        
                        RMAN> restore controlfile from '/home/oracle/backup/CTL_FULLCOLD_BKP_25';
                        
                        Starting restore at 03-FEB-13
                        using target database control file instead of recovery catalog
                        allocated channel: ORA_DISK_1
                        channel ORA_DISK_1: SID=19 device type=DISK
                        
                        channel ORA_DISK_1: restoring control file
                        channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
                        output file name=/u02/app/oracle/oradata/orcl/control01.ctl
                        output file name=/u02/app/oracle/flash_recovery_area/orcl/control02.ctl
                        Finished restore at 03-FEB-13
                        
                        RMAN> sql 'alter database mount';
                        
                        sql statement: alter database mount
                        released channel: ORA_DISK_1
                        
                        RMAN>
                        
                        =================================================================================================
                        Restore Database:-
                        =================================================================================================
                        
                        RMAN> restore database;
                        
                        Starting restore at 03-FEB-13
                        Starting implicit crosscheck backup at 03-FEB-13
                        allocated channel: ORA_DISK_1
                        channel ORA_DISK_1: SID=19 device type=DISK
                        Crosschecked 2 objects
                        
                        
                        
                        using channel ORA_DISK_1
                        
                        channel ORA_DISK_1: starting datafile backup set restore
                        channel ORA_DISK_1: specifying datafile(s) to restore from backup set
                        channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbf
                        channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/orcl/sysaux01.dbf
                        channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orcl/undotbs01.dbf
                        
                        
                        Sun Feb 03 19:37:47 2013
                        Checker run found 4 new persistent data failures
                        Sun Feb 03 19:38:40 2013
                        Full restore complete of datafile 3 /u01/app/oracle/oradata/orcl/undotbs01.dbf.  Elapsed time: 0:00:56
                          checkpoint is 1595132
                          last deallocation scn is 1519218
                        
                        
                          channel ORA_DISK_1: piece handle=/home/oracle/backup/DBF_FULLCOLD_BKP_23 tag=TAG20130203T192535
                        channel ORA_DISK_1: restored backup piece 1
                        channel ORA_DISK_1: restore complete, elapsed time: 00:02:07
                        Finished restore at 03-FEB-13
                        
                        RMAN>
                        
                        
                        
                        =================================================================================================
                        Open Database:-
                        =================================================================================================
                        
                        
                        SQL> alter database open resetlogs;
                        
                        Database altered.
                        
                        SQL> select open_mode,database_role from v$database;
                        
                        OPEN_MODE            DATABASE_ROLE
                        -------------------- ----------------
                        READ WRITE           PRIMARY
                        
                        SQL>
                        Edited by: CKPT on Feb 21, 2013 11:24 PM
                        • 9. Re: Backups on Physical standby database?
                          mseberg
                          OK
                          Is it better to assign the RMAN backups on standby? I am using the RMAN catalog.
                          Its an opinion question. I my opinion I would keep the backups on the Primary unless I have a load issue caused by the backup. Otherwise I think its simple to have my recover on the Primary not change much.
                          Do I need to change the setting to backup archive logs from standby database? 
                          I'm thinking the RMAN script would handle this, for example :


                          backup full as compressed backupset database include current controlfile plus archivelog;
                          If I Don't backup the archive logs on Primary the FRA will be full and I can't access it ?
                          You can run a small RMAN job like this as one option :

                          delete noprompt ARCHIVELOG UNTIL TIME 'SYSDATE-8';

                          If you use a deletion policy make sure its APPLIED ON STANDBY

                          How to assign the archive logs backups,full backup on standby database?
                          I don't understand this question. Can you explain?

                          The main issue I see with switching a backup to standby is I then feel the need to test all my recovers again.

                          Best Regard

                          mseberg
                          • 10. Re: Backups on Physical standby database?
                            990636
                            I mean we need to change any RMAN setting to backup the STANDBY database?I really appreciate your answers.It makes a lot sense now on backups on STANDBY.

                            Thanks for your answers.
                            • 11. Re: Backups on Physical standby database?
                              CKPT
                              987633 wrote:
                              I mean we need to change any RMAN setting to backup the STANDBY database?I really appreciate your answers.It makes a lot sense now on backups on STANDBY.

                              Thanks for your answers.
                              Fine, i already mentioned the script[previous post logs]. Of course you have to add to take backup of current control file as below. Rest no changes in backup script.
                              2> 3> ALLOCATE CHANNEL dev11 type disk;
                              4> backup as compressed backupset
                              5> format '/home/oracle/backup/DBF_FULLCOLD_BKP_%s'
                              6> filesperset 30
                              7> (database);
                              backup current controlfile format
                              8> 9> '/home/oracle/backup/CTL_FULLCOLD_BKP_%s';
                              • 12. Re: Backups on Physical standby database?
                                990636
                                Thanks alot
                                • 13. Re: Backups on Physical standby database?
                                  808758

                                  I have question, if the backup was performed on standby database, Should the primary see the same backup when I list the backup?  As of right now I see only the backup created on standby when I connect target as standby but when I connect target as primary I won't see the backup.  What should I do to get the backup seen by the primary?

                                  • 14. Re: Backups on Physical standby database?
                                    Actitud

                                    Hello. Sorry for adding this into an old thread but I think it's related to the same question.

                                     

                                    I have a DG environment 11.2.0.4 and I don't have a shared storage for backups.

                                    I guess I can't directly use the RMAN backups from the Primary if I'm taking the backups from the Standby so, do you think that running the RMAN script from the PRIMARY itself will be the best choice to avoid transferring the backups?

                                     

                                    Thanks in advance!

                                    Alex.

                                    1 2 Previous Next