This discussion is archived
11 Replies Latest reply: Jan 16, 2013 1:52 PM by 984812 RSS

Backing up duplicated database in mount mode

984812 Newbie
Currently Being Moderated
Hi all

In our environment we have a live database and a standby database, which is a duplicate of live. We keep this standby database in permanent recovery mode (ie mounted), where archive logs from live are recovered against the standby to keep it up to date. It is opened with 'open resetlogs' whenever we wish to use it. The live database is backed up nightly using RMAN and a recovery catalog on another server.

We are thinking of moving the backup to the standby database to reduce the load on the live database. Given that the standby is the same incarnation and has the same dbid and SID as the live database, is this feasible? When I tried backing up the standby the backup completed without errors, however there was no record of the backup pieces in the recovery catalog. I don't want to have to register them manually.

Has anyone else tried this?

Thanks in advance for any help.
  • 1. Re: Backing up duplicated database in mount mode
    mseberg Guru
    Currently Being Moderated
    Hello;

    Cannot see why it won't work.

    Testing on my Test system right now. Primary side complete. Standby running now. Exact same script which I call using :
    /u01/app/oracle/dba_tool/bin/db_rman_full.sh PRIMARY  > /tmp/rmanfull.out
    
    /u01/app/oracle/dba_tool/bin/db_rman_full.sh STANDBY  > /tmp/rmanfull.out
    Will look for a catalog query to post.

    Output from Standby side
    Starting RMAN...
    
    Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jan 14 18:23:06 2013
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
    connected to target database: PRIMARY (DBID=1608938192, not open)
    connected to recovery catalog database
    
    RMAN>
    RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11>
    allocated channel: d1
    channel d1: SID=84 device type=DISK
    
    allocated channel: d2
    channel d2: SID=85 device type=DISK
    
    allocated channel: d3
    channel d3: SID=86 device type=DISK
    
    allocated channel: d4
    channel d4: SID=87 device type=DISK
    
    
    Starting backup at 14-JAN-13
    channel d1: starting compressed archived log backup set
    channel d1: specifying archived log(s) in backup set
    input archived log thread=1 sequence=161 RECID=159 STAMP=798463372
    input archived log thread=1 sequence=162 RECID=161 STAMP=798487211
    channel d1: starting piece 1 at 14-JAN-13
    channel d2: starting compressed archived log backup set
    channel d2: specifying archived log(s) in backup set
    input archived log thread=1 sequence=163 RECID=162 STAMP=798487327
    input archived log thread=1 sequence=164 RECID=163 STAMP=798487342
    input archived log thread=1 sequence=165 RECID=164 STAMP=798487346
    input archived log thread=1 sequence=166 RECID=165 STAMP=798487348
    channel d2: starting piece 1 at 14-JAN-13
    channel d3: starting compressed archived log backup set
    channel d3: specifying archived log(s) in backup set
    input archived log thread=1 sequence=167 RECID=167 STAMP=804709042
    input archived log thread=1 sequence=168 RECID=166 STAMP=804709041
    input archived log thread=1 sequence=169 RECID=168 STAMP=804709089
    input archived log thread=1 sequence=170 RECID=169 STAMP=804709198
    channel d3: starting piece 1 at 14-JAN-13
    channel d4: starting compressed archived log backup set
    channel d4: specifying archived log(s) in backup set
    input archived log thread=1 sequence=171 RECID=170 STAMP=804709216
    input archived log thread=1 sequence=172 RECID=171 STAMP=804709220
    input archived log thread=1 sequence=173 RECID=172 STAMP=804709223
    channel d4: starting piece 1 at 14-JAN-13
    channel d2: finished piece 1 at 14-JAN-13
    piece handle=/u01/app/oracle/flash_recovery_area/STANDBY/backupset/2013_01_14/o1_mf_annnn_TAG20130114T182314_8h98cndd_.bkp tag=TAG20130114T182314 comment=NONE
    channel d2: backup set complete, elapsed time: 00:00:02
    channel d3: finished piece 1 at 14-JAN-13
    piece handle=/u01/app/oracle/flash_recovery_area/STANDBY/backupset/2013_01_14/o1_mf_annnn_TAG20130114T182314_8h98coy4_.bkp tag=TAG20130114T182314 comment=NONE
    channel d3: backup set complete, elapsed time: 00:00:02
    channel d4: finished piece 1 at 14-JAN-13
    piece handle=/u01/app/oracle/flash_recovery_area/STANDBY/backupset/2013_01_14/o1_mf_annnn_TAG20130114T182314_8h98cp2n_.bkp tag=TAG20130114T182314 comment=NONE
    channel d4: backup set complete, elapsed time: 00:00:01
    channel d1: finished piece 1 at 14-JAN-13
    piece handle=/u01/app/oracle/flash_recovery_area/STANDBY/backupset/2013_01_14/o1_mf_annnn_TAG20130114T182314_8h98cnbh_.bkp tag=TAG20130114T182314 comment=NONE
    channel d1: backup set complete, elapsed time: 00:00:03
    Finished backup at 14-JAN-13
    
    Starting backup at 14-JAN-13
    channel d1: starting compressed full datafile backup set
    channel d1: specifying datafile(s) in backup set
    input datafile file number=00001 name=/u01/app/oracle/oradata/PRIMARY/system01.dbf
    channel d1: starting piece 1 at 14-JAN-13
    channel d2: starting compressed full datafile backup set
    channel d2: specifying datafile(s) in backup set
    input datafile file number=00002 name=/u01/app/oracle/oradata/PRIMARY/sysaux01.dbf
    channel d2: starting piece 1 at 14-JAN-13
    channel d3: starting compressed full datafile backup set
    channel d3: specifying datafile(s) in backup set
    input datafile file number=00003 name=/u01/app/oracle/oradata/PRIMARY/undotbs01.dbf
    channel d3: starting piece 1 at 14-JAN-13
    channel d4: starting compressed full datafile backup set
    channel d4: specifying datafile(s) in backup set
    input datafile file number=00004 name=/u01/app/oracle/oradata/PRIMARY/users01.dbf
    channel d4: starting piece 1 at 14-JAN-13
    channel d3: finished piece 1 at 14-JAN-13
    piece handle=/u01/app/oracle/flash_recovery_area/STANDBY/backupset/2013_01_14/o1_mf_nnndf_TAG20130114T182319_8h98ctdg_.bkp tag=TAG20130114T182319 comment=NONE
    channel d3: backup set complete, elapsed time: 00:00:03
    channel d4: finished piece 1 at 14-JAN-13
    piece handle=/u01/app/oracle/flash_recovery_area/STANDBY/backupset/2013_01_14/o1_mf_nnndf_TAG20130114T182319_8h98cv6l_.bkp tag=TAG20130114T182319 comment=NONE
    channel d4: backup set complete, elapsed time: 00:00:03
    channel d2: finished piece 1 at 14-JAN-13
    piece handle=/u01/app/oracle/flash_recovery_area/STANDBY/backupset/2013_01_14/o1_mf_nnndf_TAG20130114T182319_8h98ctcs_.bkp tag=TAG20130114T182319 comment=NONE
    channel d2: backup set complete, elapsed time: 00:01:06
    channel d1: finished piece 1 at 14-JAN-13
    piece handle=/u01/app/oracle/flash_recovery_area/STANDBY/backupset/2013_01_14/o1_mf_nnndf_TAG20130114T182319_8h98ct7p_.bkp tag=TAG20130114T182319 comment=NONE
    channel d1: backup set complete, elapsed time: 00:01:26
    Finished backup at 14-JAN-13
    
    Starting backup at 14-JAN-13
    specification does not match any archived log in the repository
    backup cancelled because there are no files to backup
    Finished backup at 14-JAN-13
    
    Starting Control File and SPFILE Autobackup at 14-JAN-13
    piece handle=/u01/app/oracle/flash_recovery_area/STANDBY/autobackup/2013_01_14/o1_mf_s_804709223_8h98gnn4_.bkp comment=NONE
    Finished Control File and SPFILE Autobackup at 14-JAN-13
    
    released channel: d1
    
    released channel: d2
    
    released channel: d3
    
    released channel: d4
    Additional Info

    RC_BACKUP_SET_DETAILS shows:

    SESSION_KEY 4870 ( primary side) and 5003 ( Standby side) and start times of 6:18PM and 6:23PM. So I did a full on the primary side and then a full on the Standby side on my tiny test system. Works.

    When backing up from Standby some RMAN setting should change - Like ARCHIVE DELETION POLICY ( NONE ).

    You might want to do Archive only backups on the Primary so you roll forward if needed.

    See - "RMAN in a Data Guard Environment" Chapter 3 Database Backup and Recovery User's Guide 11g Release 2 (11.2) E10642-03

    Best Regards

    mseberg

    Edited by: mseberg on Jan 14, 2013 6:28 PM

    Edited by: mseberg on Jan 14, 2013 6:51 PM
  • 2. Re: Backing up duplicated database in mount mode
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Is the Standby database created with a Standby controlfile (or a regular controlfile where you script the RECOVER and OPEN RESETLOGS commands) ?


    What version are you running ? Have you specified a different DB_UNIQUE_NAME for the "Standby" database ? In 11g, RMAN can register entries for the standby based on the DB_UNIQUE_NAME.


    Hemant K Chitale
  • 3. Re: Backing up duplicated database in mount mode
    984812 Newbie
    Currently Being Moderated
    Hi mseberg

    Thanks for your response.

    Taking the backup worked for me also. I was worried when I couldn't then see the backups listed in a list backup of database.

    I'd be interested to see your catalog query.
  • 4. Re: Backing up duplicated database in mount mode
    984812 Newbie
    Currently Being Moderated
    Hi Hemant

    The standby database is recovered using a controlfile copied from the live database.

    Re version, see my response to mseberg above.

    Thanks
    Justin.
  • 5. Re: Backing up duplicated database in mount mode
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    The standby database is recovered using a controlfile copied from the live database.
    I assume this to mean that the controlfile on the Standby is from a BACKUP CONTROLFILE command rather than created as a ALTER DATABASE CREATE STANDBY CONTROLFILE command.
    Given that the standby is the same incarnation and has the same dbid and SID as the live database
    This seems to mean that you might not have set a separate DB_UNIQUE_NAME for the "standby database"

    Both inferences put together mean that you are not using DataGuard.
    I am not sure what your version is.

    When I said :
    In 11g, RMAN can register entries for the standby based on the DB_UNIQUE_NAME.
    See point 11.1.2 Association of Backups in a Data Guard environment
    at http://docs.oracle.com/cd/E11882_01/server.112/e25608/rman.htm#sthref848


    Hemant K Chitale
  • 6. Re: Backing up duplicated database in mount mode
    mseberg Guru
    Currently Being Moderated
    Hello again;

    Query from catalog :
    SET PAGESIZE 80
    SET LINESIZE 200
    SELECT 
      A.STATUS, A.START_TIME, A.END_TIME, B.DB_UNIQUE_NAME, B.SITE_KEY
    FROM
      RMANCAT.RC_RMAN_STATUS A,
      RMANCAT.RC_SITE B
    WHERE
      A.OPERATION = 'BACKUP'
    AND
      A.START_TIME > SYSDATE -1
    AND
      A.DB_KEY = B.DB_KEY;  
      
    --
    
    STATUS                            START_TIME END_TIME  DB_UNIQUE_NAME                   SITE_KEY
    --------------------------------- ---------- --------- ------------------------------ ----------
    COMPLETED                         14-JAN-13  14-JAN-13 PRIMARY                              3250
    COMPLETED                         14-JAN-13  14-JAN-13 PRIMARY                              3250
    COMPLETED                         14-JAN-13  14-JAN-13 PRIMARY                              3250
    COMPLETED                         14-JAN-13  14-JAN-13 PRIMARY                              3250
    COMPLETED                         14-JAN-13  14-JAN-13 PRIMARY                              3250
    COMPLETED                         14-JAN-13  14-JAN-13 PRIMARY                              3250
    COMPLETED                         14-JAN-13  14-JAN-13 PRIMARY                              3250
    COMPLETED                         14-JAN-13  14-JAN-13 PRIMARY                              3250
    COMPLETED                         14-JAN-13  14-JAN-13 PRIMARY                              3250
    COMPLETED                         14-JAN-13  14-JAN-13 STANDBY                              3442
    COMPLETED                         14-JAN-13  14-JAN-13 STANDBY                              3442
    COMPLETED                         14-JAN-13  14-JAN-13 STANDBY                              3442
    COMPLETED                         14-JAN-13  14-JAN-13 STANDBY                              3442
    COMPLETED                         14-JAN-13  14-JAN-13 STANDBY                              3442
    COMPLETED                         14-JAN-13  14-JAN-13 STANDBY                              3442
    COMPLETED                         14-JAN-13  14-JAN-13 STANDBY                              3442
    COMPLETED                         14-JAN-13  14-JAN-13 STANDBY                              3442
    COMPLETED                         14-JAN-13  14-JAN-13 STANDBY                              3442
    
    18 rows selected.  
    Best Regards

    mseberg
  • 7. Re: Backing up duplicated database in mount mode
    984812 Newbie
    Currently Being Moderated
    Hi Hemant

    Correct, we are not using Data Guard. Our environment is 10g R2.

    Regards
    Justin.
  • 8. Re: Backing up duplicated database in mount mode
    984812 Newbie
    Currently Being Moderated
    Hi mseberg

    Looks like there may have been some confusion over the terms used to describe our environment, 'standby database' is a term we use, and wasn't meant to describe a Data Guard standy database. We aren't using Data Guard.
  • 9. Re: Backing up duplicated database in mount mode
    mseberg Guru
    Currently Being Moderated
    Justin;

    My mistake. I took your question as Data Guard and I replied as such. If I had Hemant's insight I would have never wasted your time and mine.

    Best of luck with your issue.

    mseberg
  • 10. Re: Backing up duplicated database in mount mode
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Without DG, Standby Controlfile and DB_UNIQUE_NAME. In 10gR2

    I suggest that you do not attempt to use the same RMAN Catalog for the primary and the standby. In your environment, Oracle would not be able to distinguish between the two. You could use a different RMAN Catalog.
    If you are periodically opening the standby, I guess that you might also be periodically copying the controlfile from the primary to the standby. In that case, you cannot rely on the controlfile as the RMAN Repository at the standby site as it is being overwritten regularly.


    Hemant K Chitale
  • 11. Re: Backing up duplicated database in mount mode
    984812 Newbie
    Currently Being Moderated
    Thanks Hemant and mseberg for your assistance.

Legend

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