9 Replies Latest reply: Apr 24, 2013 3:24 AM by Mahir M. Quluzade RSS

    restore primary database using physical standby

    Faruk.m
      hi,

      i am taking rman backup on production database,we have physical standby.coming month i have to take backup from standby instead of prod db.pls advise me, is it possible to restore and recover production database using standby database backup.if production crashed.pls advise me .i am new to dba.if possible pls provide proper steps.

      Regards
      Faruk
        • 1. Re: restore primary database using physical standby
          FreddieEssex
          Is it possible...yes.

          There is plenty of stuff on google which will give you the steps. This is the first hit I got on google.

          http://alexzeng.wordpress.com/2008/09/13/how-to-use-standby-backup-to-restore-primary-db/

          Goes without saying that I would test out the procedure on a test environment first before attempting it on production if I picked up a procedure off the internet.

          Why not just create your primary from your standby using dataguard or flashback if you have implemented it?

          http://docs.oracle.com/cd/E11882_01/server.112/e17022/scenarios.htm#i1049997
          • 2. Re: restore primary database using physical standby
            Mahir M. Quluzade
            Faruk.m wrote:
            hi,

            i am taking rman backup on production database,we have physical standby.coming month i have to take backup from standby instead of prod db.pls advise me, is it possible to restore and recover production database using standby database backup.if production crashed.pls advise me .i am new to dba.if possible pls provide proper steps.

            Regards
            Faruk
            Hi,

            If production database is failed, then you must fail over to standby database, change role to physical and
            continue your work. It is means High Availability.
            Now you can reinstate your old primary, or you can create new physical standby from new primary with duplicate command or etc.
            If you are backed physical standby, you can restore this backups as primary or standby database.

            Because physical standby database is is create from primary, and same bit for bit is primary database.
            Only different control file. Control file is standby control file and database role and very important db_unique_name is different.


            Regards
            Mahir M. Quluzade
            http://www.mahir-quluzade.com
            • 3. Re: restore primary database using physical standby
              Uwehesse-Oracle
              Yes, what you describe is possible. Especially the option to take backups from a Physical Standby on behalf of the Primary is one advantage of Physical Standby.
              As others have pointed out, in case of a damage on the Primary, the first and probably best reaction would be a failover to the Standby. After all, that's why you have it :-)

              Apart from that natural reaction, you have some other options:

              1) restore from the backup you took on the Primary or Standby
              2) restore datafiles directly from the Standby

              Check out *11 Using RMAN to Back Up and Restore Files* http://docs.oracle.com/cd/E11882_01/server.112/e25608/rman.htm#CHDHAJFD from
              Data Guard Concepts and Administration for details

              If a logical mistake happened on the Primary, you may be able to address that with Flashback technology instead of having to go through a Point In Time Recovery that uses a Backup (from Primary or Standby)
              This is why the writing of Flashback Logs is recommended especially also for a Data Guard configuration.

              Kind regards
              Uwe Hesse

              "Don't believe it, test it!"
              http://uhesse.com
              • 4. Re: restore primary database using physical standby
                Faruk.m
                hi,

                i have to take backup on standby instead of production.failover i can do.but i need to know the recovery procedures from standby.and thank u for reply.i want, is it possible to restore my production controlfile with controlfile backup from standby.

                i tested with my local database ,that below:
                1.backup with controlfile from standby (rman>backup current controlfile)
                2.manual deleted my test db controlfile,tried to restore from backup taken from standby(rman>restore controlfile from '*****************';)
                3.rman>restore database
                3.but it shows error (datafile already exists) ,controlfile tried to create datafiles on standby location.

                my conclusion:above my cmds used to create datafiles on standby becoz i restored with standby controlfile.

                my question:what is wrong with my steps? and how to restore my (prod) controlfile from standby.and bring backup my prod db.

                regards
                faruk
                • 5. Re: restore primary database using physical standby
                  Faruk.m
                  thank you all,is t possible to restore standby controlfile to production database,is there any difference between standby ctrolfile and prod controlfile.pls clarify


                  Regards
                  faruk
                  • 6. Re: restore primary database using physical standby
                    FreddieEssex
                    my conclusion:above my cmds used to create datafiles on standby becoz i restored with standby controlfile
                    What do you mean, it created the datafiles on standby? Do you mean it created the datafiles in the same location as on the standby database?
                    is it possible to restore my production controlfile
                    3.rman>restore database
                    Why restore the database when all you are trying to restore is the controlfile as per your post? Or are you tring to restore your controlfile and your database?
                    3.but it shows error (datafile already exists) ,controlfile tried to create datafiles on standby location.
                    Can you post the ORA/RMAN error?
                    is t possible to restore standby controlfile to production database
                    Yes it is, use the restore controlfile RMAN command (like you already did).
                    is there any difference between standby ctrolfile and prod controlfile.pls clarify
                    Yes, there is a important difference. The standby controlfile identifies the database as a standby database so you can not open this database in read write mode, just like you can't put the primary database into managed recovery mode.

                    Consider a real life scenario:

                    You have a data guard environment where you lose your primary database. In this case you would failover to your standby which would then become your primary. So once this is your primary why not recreate your standby database (which was your primary) using RMAN as per standard procedures. With 11g you have the "from active database" feature you can use in RMAN. Once you have your standby created you can do a switchover to make your standby the primary. Your end goal is to be able to create your primary from what used to be your standby....correct? If you restore your primary from your backups you will still have to setup your standby again...correct?

                    Note 1075908.1 Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE...FROM ACTIVE DATABASE
                    • 7. Re: restore primary database using physical standby
                      Mahir M. Quluzade
                      Faruk.m wrote:
                      hi,

                      i have to take backup on standby instead of production.failover i can do.but i need to know the recovery procedures from standby.and thank u for reply.i want, is it possible to restore my production controlfile with controlfile backup from standby.

                      i tested with my local database ,that below:
                      1.backup with controlfile from standby (rman>backup current controlfile)
                      2.manual deleted my test db controlfile,tried to restore from backup taken from standby(rman>restore controlfile from '*****************';)
                      3.rman>restore database
                      3.but it shows error (datafile already exists) ,controlfile tried to create datafiles on standby location.

                      my conclusion:above my cmds used to create datafiles on standby becoz i restored with standby controlfile.

                      my question:what is wrong with my steps? and how to restore my (prod) controlfile from standby.and bring backup my prod db.

                      regards
                      faruk
                      Hi Faruk,

                      Sorry my late.
                      I can share with you my test.
                      It is my standby  database, I connecting with RMAN 
                      
                      SQL> select database_role, db_unique_name, name from  v$database; 
                      
                      DATABASE_ROLE      DB_UNIQUE_NAME           NAME
                      ---------------- ------------------------------ ---------
                      PHYSICAL STANDBY       stldb                    ADMDB
                      
                      
                      [oracle@oel62-x64 oradata]$ rman target / 
                      
                      Recovery Manager: Release 11.2.0.3.0 - Production on Tue Apr 23 12:23:00 2013
                      
                      Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
                      
                      connected to target database: ADMDB (DBID=4063877183, not open)
                      
                      RMAN> backup current controlfile; 
                      
                      Starting backup at 23-APR-13
                      Starting implicit crosscheck backup at 23-APR-13
                      using target database control file instead of recovery catalog
                      allocated channel: ORA_DISK_1
                      channel ORA_DISK_1: SID=29 device type=DISK
                      Finished implicit crosscheck backup at 23-APR-13
                      
                      Starting implicit crosscheck copy at 23-APR-13
                      using channel ORA_DISK_1
                      Finished implicit crosscheck copy at 23-APR-13
                      
                      searching for all files in the recovery area
                      cataloging files...
                      no files cataloged
                      
                      using channel ORA_DISK_1
                      channel ORA_DISK_1: starting full datafile backup set
                      channel ORA_DISK_1: specifying datafile(s) in backup set
                      including current control file in backup set
                      channel ORA_DISK_1: starting piece 1 at 23-APR-13
                      channel ORA_DISK_1: finished piece 1 at 23-APR-13
                      piece handle=/u01/app/oracle/fast_recovery_area/stldb/STLDB/backupset/2013_04_23/o1_mf_ncnnf_TAG20130423T122310_8qdfq19x_.bkp tag=TAG20130423T122310 comment=NONE
                      channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
                      Finished backup at 23-APR-13
                      
                      RMAN> exit
                      
                      
                      Recovery Manager complete.
                      [oracle@oel62-x64 oradata]$ sqlplus / as sysdba
                      
                      SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 23 12:23:26 2013
                      
                      Copyright (c) 1982, 2011, Oracle.  All rights reserved.
                      
                      
                      Connected to:
                      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
                      With the Partitioning, OLAP, Data Mining and Real Application Testing options
                      
                      SQL> shut immediate;
                      ORA-01109: database not open
                      
                      
                      Database dismounted.
                      ORACLE instance shut down.
                      SQL> startup nomount;
                      ORACLE instance started.
                      
                      Total System Global Area  551165952 bytes
                      Fixed Size              2230232 bytes
                      Variable Size            167774248 bytes
                      Database Buffers       377487360 bytes
                      Redo Buffers              3674112 bytes
                      SQL> 
                      SQL> 
                      SQL> exit
                      Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
                      With the Partitioning, OLAP, Data Mining and Real Application Testing options
                      [oracle@oel62-x64 oradata]$ rman target / 
                      
                      Recovery Manager: Release 11.2.0.3.0 - Production on Tue Apr 23 12:34:58 2013
                      
                      Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
                      
                      connected to target database: ADMDB (not mounted)
                      
                      RMAN> restore controlfile  from '/u01/app/oracle/fast_recovery_area/stldb/STLDB/backupset/2013_04_23/o1_mf_ncnnf_TAG20130423T122310_8qdfq19x_.bkp';
                      
                      Starting restore at 23-APR-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:01
                      output file name=/u01/app/oracle/oradata/stldb/control01.ctl
                      Finished restore at 23-APR-13
                      
                      RMAN> alter database mount;
                      
                      database mounted
                      released channel: ORA_DISK_1
                      
                      RMAN> exit
                      
                      
                      Recovery Manager complete.
                      [oracle@oel62-x64 oradata]$ sqlplus / as sysdba
                      
                      SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 23 12:35:31 2013
                      
                      Copyright (c) 1982, 2011, Oracle.  All rights reserved.
                      
                      
                      Connected to:
                      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
                      With the Partitioning, OLAP, Data Mining and Real Application Testing options
                      
                      SQL> select database_role, db_unique_name, name from  v$database; 
                      
                      DATABASE_ROLE      DB_UNIQUE_NAME           NAME
                      ---------------- ------------------------------ ---------
                      PRIMARY       stldb                    ADMDB
                      
                      
                      
                       
                      As show here, you can backup standby control file, and restore as primary .

                      Regars
                      Mahir M. Quluzade
                      http://www.mahir-quluzade.com
                      • 8. Re: restore primary database using physical standby
                        Faruk.m
                        Thank you Mr.mahir,i got the point