DATAGUARD

How to use expdp to export data from physical standby database

During peak business hours, It is not advised to export data from production .(as it might impact the performance). So if we have a physical standby database, we can export data using datapump from standby database. PRE-REQUISITES: 1. Physical standby database should be in READ-ONLY MODE (ACTIVE DATAGUARD) 2.We cannot run expdp directly on physical […]

How to enable active dataguard in physical standby database

Active dataguard means, the standby database is open with read only mode, even when redo logs are getting applied in real time. Below are the benefit of using active dataguard. Reporting queries can be offloaded to standby database. Physical block corruptions are repaired automatically either at primary or physical standby database. RMAN backups can be […]

How to recreate physical standby controlfile

     Below are the steps for recreating physical standby controlfile. 1. Get the controlfile location [STANDBY] SQL> show parameter control_files NAME TYPE VALUE ———————————— ———– —————————— control_files string /archive/NONPLUG/NONCDB/contro l01.ctl, /archive/NONPLUG/NONC DB/control02.ctl 2. Cancel recovery [STANDBY] alter database recover managed standby database cancel; 3. Shutdown standby [ STANDBY] SQL> SHUTDOWN IMMEDIATE Database closed. Database […]

Flashback primary database in dataguard environment

      Development wanted to do some changes in the dev database, which needs to be rolled back once testing is over. So we have chosen to create flashback restore point before they do the changes and flashback the database, once testing is over. Now there is a little twist. This is a data […]

ORA-01665: control file is not a standby control file

PROBLEM: After restoring standby database from RMAN backup, got below error, while mounting standby database,getting below error. ORA-01665: control file is not a standby control file SOLUTION: Check the database_role of STANDBY sql> SELECT database_role FROM v$database; DATABASE_ROLE —————- PRIMARY Convert to standby database sql> Alter database convert to physical standby; Now mount again: sql> […]

ORA-01274: cannot add data file that was originally created as

PROBLEM: After adding a datafile in primary database, recovery process in standby stopped with below error. — Primary database: SQL> alter tablespace prim add datafile size 1g; Tablespace altered. — Error in alert log of standby database File #5 added to control file as ‘UNNAMED00005’ because the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL The file […]

Remove dataguard configuration from Primary database:

    If you wish to remove the dataguard/standby setup from primary database i.e to make the primary database as standalone database without any standby database, then follow below steps. 1.Put primary database on maximum performance mode: SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE; 2. Remove below parameters from spfile of primary database: […]

How to open standby database when primary database is lost

    If your primary database is not accessible or crashed for any issue, then we can use open the standby database for use. Below are the steps for opening standby database when the primary database is lost:   1. start the standby database in mount stage:[ STANDBY] shut immediate; startup mount; 2. Check the […]

How to rename datafile in standby setup in 11g

           When you rename a in the primary database, the change won’t be reflected on standby database, even if the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO. Follow below steps for renaming datafile in standby environment: For renaming datafile in oracle 12c – Refer 1. Make standby_file_management parameter to MANUAL [ […]

Restore missing datafile in standby database oracle 12c

        In oracle 12c, if datafile is missing is standby database, then it can be restored easily from primary database using RESTORE DATAFILE FROM SERVICE command. This is one of the new feature of dataguard 12c .    DEMO: PRIMDB – Primary database STYDB – Standby database Check status of primary and […]