This discussion is archived
9 Replies Latest reply: Apr 24, 2013 1:24 AM by MahirM.Quluzade RSS

restore primary database using physical standby

Faruk.m Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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
    MahirM.Quluzade Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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
    MahirM.Quluzade Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thank you Mr.mahir,i got the point
  • 9. Re: restore primary database using physical standby
    MahirM.Quluzade Guru
    Currently Being Moderated
    You are welcome!

Legend

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