-
1. Re: Backups on Physical standby database?
CKPT Feb 21, 2013 5:11 PM (in response to 990636)1 person found this helpful987633 wrote:
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,
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?
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 Feb 21, 2013 5:18 PM (in response to CKPT)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 Feb 21, 2013 5:21 PM (in response to 990636)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 Feb 21, 2013 5:26 PM (in response to 990636)Hello;1 person found this helpful
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 Feb 21, 2013 5:29 PM (in response to mseberg)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 Feb 21, 2013 5:42 PM (in response to 990636)Correct. I run RMAN daily on all my standby's without change to anything.1 person found this helpful
Simple test
Best RegardsRMAN> 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>
mseberg
Edited by: mseberg on Feb 21, 2013 11:41 AM -
7. Re: Backups on Physical standby database?
990636 Feb 21, 2013 5:48 PM (in response to mseberg)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 Feb 21, 2013 5:55 PM (in response to 990636)1 person found this helpfulIs 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.
Edited by: CKPT on Feb 21, 2013 11:24 PM================================================================================================= 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>
-
9. Re: Backups on Physical standby database?
mseberg Feb 21, 2013 6:04 PM (in response to 990636)OK1 person found this helpful
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.Is it better to assign the RMAN backups on standby? I am using the RMAN catalog.
I'm thinking the RMAN script would handle this, for example :Do I need to change the setting to backup archive logs from standby database?
backup full as compressed backupset database include current controlfile plus archivelog;
You can run a small RMAN job like this as one option :If I Don't backup the archive logs on Primary the FRA will be full and I can't access it ?
delete noprompt ARCHIVELOG UNTIL TIME 'SYSDATE-8';
If you use a deletion policy make sure its APPLIED ON STANDBY
I don't understand this question. Can you explain?How to assign the archive logs backups,full backup on standby database?
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 Feb 21, 2013 6:09 PM (in response to mseberg)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 Feb 21, 2013 6:14 PM (in response to 990636)987633 wrote:
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.
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.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 Feb 21, 2013 6:41 PM (in response to CKPT)Thanks alot -
13. Re: Backups on Physical standby database?
808758 Sep 10, 2013 5:09 PM (in response to 990636)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 Dec 16, 2013 6:25 PM (in response to mseberg)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.