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

Backups on Physical standby database?

990636 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks alot
  • 13. Re: Backups on Physical standby database?
    808758 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points