11 Replies Latest reply: Jan 16, 2013 3:52 PM by 984812 RSS

    Backing up duplicated database in mount mode

    984812
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              Thanks Hemant and mseberg for your assistance.