Creating Physical Standby using Data Guard Broker(DGMGRL)

Creating Physical Standby using Data Guard Broker(DGMGRL)

Primary Database PRIM


Oracle Base D:\app\farif\
Database Home D:\app\farif\product\11.2.0.3\dbhome_1
Datafiles Location D:\app\farif\oradata\prim
Fast Recovery Area D:\app\farif\fast_recovery_area
Dump Destination D:\app\farif\admin\prim


Database should be in archivelog mode to create standby database. If your database is not in archivelog mode enable it


C:\Users\farif.HOME>set oracle_sid=prim

C:\Users\farif.HOME>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 4 12:20:55 2012

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> SHUTDOWN IMMEDIATE;

Then start database in mount state

SQL> STARTUP MOUNT;


SQL> ALTER DATABASE ARCHIVELOG;

Then open database

SQL> ALTER DATABASE OPEN;

SQL> ALTER DATABASE FORCE LOGGING;


SQL> select name,log_mode,database_role,force_logging from v$database;

NAME      LOG_MODE     DATABASE_ROLE      FOR
--------- ------------ ----------------   ---
PRIM      ARCHIVELOG   PRIMARY            YES

SQL>

===============================================================



Standby Database STAN


Create Standby Database Directory Structure according to primary
Datafiles Location D:\app\farif\oradata\stan
Fast Recovery Area D:\app\farif\fast_recovery_area
Dump Destination D:\app\farif\admin\stan

=======================================================================

Content of D:\app\farif\product\11.2.0.3\dbhome_1\NETWORK\ADMIN\tnsnames.ora file

PRIM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = farif-PC)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = PRIM)
    )
  )

STAN =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = farif-PC)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = STAN)
    )
  )

==============================================================================

Content of D:\app\farif\product\11.2.0.3\dbhome_1\NETWORK\ADMIN\Listener.ora file

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\app\farif\product\11.2.0.3\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\app\farif\product\11.2.0.3\dbhome_1\bin\oraclr11.dll")
    )
    (SID_DESC =
      (GLOBAL_DBNAME = prim)
      (ORACLE_HOME = D:\app\farif\product\11.2.0.3\dbhome_1)
      (SID_NAME = prim)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = stan)
      (ORACLE_HOME = D:\app\farif\product\11.2.0.3\dbhome_1)
      (SID_NAME = stan)
    )
  )

===========================================================================================

create standby controlfile for standby database


SQL> alter database create standby controlfile as 'D:\app\farif\oradata\stan\CONTROL01.CTL';


===============================================================================

Create PFILE for standby database

create pfile='D:\app\farif\oradata\stan\PFILESTAN.ora' from spfile;

===============================================================================

Make changes to parameter file



stan.__db_cache_size=1946157056
stan.__java_pool_size=16777216
stan.__large_pool_size=16777216
stan.__oracle_base='D:\app\farif'#ORACLE_BASE set from environment
stan.__pga_aggregate_target=855638016
stan.__sga_target=2550136832
stan.__shared_io_pool_size=0
stan.__shared_pool_size=520093696
stan.__streams_pool_size=16777216
*.audit_file_dest='D:\app\farif\admin\stan\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='D:\app\farif\oradata\stan\control01.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='prim'
*.db_unique_name='stan'
*.db_recovery_file_dest='D:\app\farif\fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.diagnostic_dest='D:\app\farif'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stanXDB)'
*.open_cursors=300
*.pga_aggregate_target=846200832
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2538602496
*.undo_tablespace='UNDOTBS1'


===========================================================================================

create instance STAN for standby database


C:\Users\farif.HOME>oradim -new -sid stan
instance created

C:\Users\farif.HOME>set oracle_sid=stan

C:\Users\farif.HOME>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 4 12:20:55 2012

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> startup nomount pfile='D:\app\farif\oradata\stan\pfileSTAN.ora'

SQL> show parameter control_files



NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------

control_files                        string      D:\APP\FARIF\ORADATA\STAN\CONT
                                                        ROL01.CTL

Standby controlfile is in its location, mount database

SQL> ALTER DATABASE MOUNT;


SQL> alter database rename file 'D:\APP\FARIF\ORADATA\PRIM\SYSTEM01.DBF' to 'D:\APP\FARIF\ORADATA\STAN\SYSTEM01.DBF';
Database altered.
SQL> alter database rename file 'D:\APP\FARIF\ORADATA\PRIM\SYSAUX01.DBF' to 'D:\APP\FARIF\ORADATA\STAN\SYSAUX01.DBF';
Database altered.
SQL> alter database rename file 'D:\APP\FARIF\ORADATA\PRIM\UNDOTBS01.DBF'to 'D:\APP\FARIF\ORADATA\STAN\UNDOTBS01.DBF';
Database altered.
SQL> alter database rename file 'D:\APP\FARIF\ORADATA\PRIM\USERS01.DBF' to 'D:\APP\FARIF\ORADATA\STAN\USERS01.DBF';
Database altered.
SQL> alter database rename file 'D:\APP\FARIF\ORADATA\PRIM\EXAMPLE01.DBF' to 'D:\APP\FARIF\ORADATA\STAN\EXAMPLE01.DBF';
Database altered.
SQL> alter database rename file 'D:\APP\FARIF\ORADATA\PRIM\TEMP01.DBF' to 'D:\APP\FARIF\ORADATA\STAN\TEMP01.DBF';
Database altered.
SQL> alter database rename file 'D:\APP\FARIF\ORADATA\PRIM\REDO01.LOG' to 'D:\APP\FARIF\ORADATA\STAN\REDO01.LOG';
Database altered.
SQL> alter database rename file 'D:\APP\FARIF\ORADATA\PRIM\REDO02.LOG' to 'D:\APP\FARIF\ORADATA\STAN\REDO02.LOG';
Database altered.
SQL> alter database rename file 'D:\APP\FARIF\ORADATA\PRIM\REDO03.LOG' to 'D:\APP\FARIF\ORADATA\STAN\REDO03.LOG';
Database altered.

========================================================================================

Add Standby Logs on both PRIM and STAN

=========================================================================================
SQL> alter database add standby logfile 'D:\APP\FARIF\ORADATA\PRIM\STANLOG01.LOG' size 50M;

Database altered.

SQL> alter database add standby logfile 'D:\APP\FARIF\ORADATA\PRIM\STANLOG02.LOG' size 50M;

Database altered.

SQL> alter database add standby logfile 'D:\APP\FARIF\ORADATA\PRIM\STANLOG03.LOG' size 50M;

Database altered.

==========================================================================================

SQL> conn sys/oracle@stan as sysdba
Connected.
SQL> alter database add standby logfile 'D:\APP\FARIF\ORADATA\STAN\STANLOG01.LOG' size 50M;

Database altered.

SQL> alter database add standby logfile 'D:\APP\FARIF\ORADATA\STAN\STANLOG02.LOG' size 50M;

Database altered.

SQL> alter database add standby logfile 'D:\APP\FARIF\ORADATA\STAN\STANLOG03.LOG' size 50M;

Database altered.



**************************************------------******************************************

Data Guard Broker Configuration




C:\Windows\system32>set oracle_sid=prim

C:\Windows\system32>dgmgrl /
DGMGRL for 64-bit Windows: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

=========================================================================

DGMGRL> show configuration
ORA-16532: Data Guard broker configuration does not exist

Configuration details cannot be determined by DGMGRL


==========================================================================
DATA GUARD BROKER is not enabled, Check Data Guard related parameters and enable it on both PRIM and STAN Databases

SQL>SHOW PARAMETER DG_BROKER

dg_broker_config_file1               string   D:\APP\FARIF\PRODUCT\11.2.0.3\DBHOME_1\DATABASE\DR1PRIM.DAT
dg_broker_config_file2               string   D:\APP\FARIF\PRODUCT\11.2.0.3\DBHOME_1\DATABASE\DR2PRIM.DAT
dg_broker_start                      boolean  FALSE


On both PRIM and STAN databases enable dataguard broker



SQL>ALTER SYSTEM SET DG_BROKER_START='TRUE';    

===========================================================================

DGMGRL> help create

Creates a broker configuration

Syntax:

  CREATE CONFIGURATION AS
    PRIMARY DATABASE IS
    CONNECT IDENTIFIER IS ;

=========================================================================
DGMGRL> create configuration DGC as
> primary database is PRIM
> connect identifier is PRIM;
Configuration "dgc" created with primary database "prim"

=========================================================================
DGMGRL> show configuration

Configuration - dgc

  Protection Mode: MaxPerformance
  Databases:
    prim - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

=========================================================================
DGMGRL> help add

Adds a standby database to the broker configuration

Syntax:

  ADD DATABASE
    [AS CONNECT IDENTIFIER IS ]
    [MAINTAINED AS {PHYSICAL|LOGICAL}];

DGMGRL>

=========================================================================
DGMGRL> add database STAN as
> connect identifier is STAN
> maintained as physical;
Database "stan" added

=========================================================================
DGMGRL> show configuration

Configuration - dgc

  Protection Mode: MaxPerformance
  Databases:
    prim - Primary database
    stan - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

=========================================================================
DGMGRL> enable configuration
Enabled.
DGMGRL>


DGMGRL> show database verbose 'stan';

Database - stan

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    stan

  Properties:
    DGConnectIdentifier             = 'stan'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'MANUAL'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName                         = 'stan'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=FARIF-PC)(PORT=1521))(CONNECT_DATA=(SER
VICE_NAME=stan_DGMGRL)(INSTANCE_NAME=stan)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = 'ARC%S_%R.%T'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS

DGMGRL>




********************************------------********************************

Test Log Shipping and apply

create a new user on primary db

SQL> Create user test identified by test;
user created
SQL> grant connect, resource to test;

=================================================================

connect with user test and create a table

create table test 
(id number(10), 
name varchar2(500));

==================================================================
BEGIN
    FOR i IN 1..100000 LOOP
     
     INSERT INTO test VALUES (i,  'dummy');
             
    END LOOP;
    COMMIT;
END;


This data load will generate archive logs and will be shipped to standby database

check log shipping

SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;


SQL> select * from v$archive_gap;

no rows selected

====================================================================