oracle physical standby db creation with broker config

SOURCE SERVER

Create the new directories:
mkdir /oraflashback/PRODDG1
mkdir /oraarc1/PRODDG1
mkdir /oralog1/PRODDG1
mkdir /oralog2/PRODDG1

create a backup of pfile . then move ahead .
Using SQL*Plus:
show parameter recovery;
alter system set db_recovery_file_dest='/oraflashback/PRODDG1' scope=spfile;
alter system set db_recovery_file_dest_size=10G scope=spfile;
alter system set db_flashback_retention_target=2880;
Modify the parameters in the pfile:
*.instance_name=PRODDG1
*.service_names=PROD.com
*.db_file_name_convert=PRODDG2,PRODDG1
*.log_file_name_convert=PRODDG2,PRODDG1

SQL> alter system set instance_name=PRODDG1 scope=spfile;

System altered.


SQL>  alter system set service_names='PROD.com'  scope=spfile;

System altered.

SQL> alter system set db_file_name_convert=PRODDG2,PRODDG1 scope=spfile;

System altered.

SQL> alter system set log_file_name_convert=PRODDG2,PRODDG1 scope=spfile;

System altered.


shutdown immediate;
startup mount;
alter database archivelog;
alter database force logging;
alter database flashback on;
alter database open;

Add the following parameters:

alter system set log_archive_config='DG_CONFIG=(PRODDG1, PRODDG2)' scope=spfile;
alter system set log_archive_dest_1='location=/oraarc1/PRODDG1 valid_for=(ONLINE_LOGFILE,PRIMARY_ROLES)' scope=spfile;
alter system set log_archive_dest_10='location=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,STANDBY_ROLE)' scope=spfile;
alter system set log_archive_dest_2='SERVICE=PRODDG2 ASYNC LGWR NOAFFIRM valid_for=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=PRODDG2' scope=spfile;
alter system set standby_file_management='AUTO' scope=spfile;
alter system set fal_server=PRODDG2 scope=spfile;
alter system set dg_broker_start=true scope=spfile;
alter system set local_listener='LISTENER11';
alter system set db_unique_name=PRODDG1 scope=spfile;




alter database add standby logfile group 4 '/oralog1/PRODDG1/sby_g04_m01.rdo'  size 50M;
alter database add standby logfile group 5 '/oralog1/PRODDG1/sby_g05_m01.rdo'  size 50M;
alter database add standby logfile group 6 '/oralog1/PRODDG1/sby_g06_m01.rdo'  size 50M;
alter database add standby logfile group 7 '/oralog1/PRODDG1/sby_g07_m01.rdo'  size 50M;
alter database add standby logfile member '/oralog2/PRODDG1/sby_g04_m02.rdo' to group 4;
alter database add standby logfile member '/oralog2/PRODDG1/sby_g05_m02.rdo' to group 5;
alter database add standby logfile member '/oralog2/PRODDG1/sby_g06_m02.rdo' to group 6;
alter database add standby logfile member '/oralog2/PRODDG1/sby_g07_m02.rdo' to group 7;


SOURCE:

LISTENER.ora
SID_LIST_LISTENER11 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME =  PRODDG1_DGB.com)
      (ORACLE_HOME = /opt/oracle/product/11.2.0.4)
      (SID_NAME =  PRODDG1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME =  PRODDG1_DGMGRL.com)
      (ORACLE_HOME = /opt/oracle/product/11.2.0.4)
      (SID_NAME =  PRODDG1)
    )
 (SID_DESC =
      (GLOBAL_DBNAME =  PROD.com)
      (ORACLE_HOME = /opt/oracle/product/11.2.0.4)
      (SID_NAME =  PRODDG1)
    )
  )

SID_LIST_LISTENERDG11 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME =  PRODDG1_DGB.com)
      (ORACLE_HOME = /opt/oracle/product/11.2.0.4)
      (SID_NAME =  PRODDG1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME =  PRODDG1_DGMGRL.com)
      (ORACLE_HOME = /opt/oracle/product/11.2.0.4)
      (SID_NAME =  PRODDG1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME =  PROD.com)
      (ORACLE_HOME = /opt/oracle/product/11.2.0.4)
      (SID_NAME =  PRODDG1)
    )
  )


TNSNAMES.ora
PRODDG1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node1.com)(PORT = 1526))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PROD.com)
    )
  )

PRODDG2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node2.com)(PORT = 1526))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PRODDG2.com)
    )
  )

LISTENER_PROD =
  (ADDRESS = (PROTOCOL = TCP)(HOST = node1.com)(PORT = 1526))

LISTENER11 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = node1.com)(PORT = 1526))


Using SQL*Plus:
show parameter spfile;
If no spfile then copy init.ora
cp $ORACLE_HOME/dbs/initPROD.ora $ORACLE_HOME/dbs/initPRODDG1.ora
If an spfile exists then create pfile from spfile
create pfile='?/dbs/initPRODDG1.ora' from spfile;
Modify the parameters in the pfile:
instance_name = PRODDG1
service_names  = PRODDG1.com
db_file_name_convert     = PRODDG2, PRODDG1
log_file_name_convert    = PRODDG2, PRODDG1

Add the database entry in /etc/oratab:
PRODDG1:/opt/oracle/product/11.2.0.4/:Y
Modify the old database entry in /etc/oratab:
PROD:/opt/oracle/product/11.2.0.4/:N
Copy the password file:
cp $ORACLE_HOME/dbs/orapwPROD $ORACLE_HOME/dbs/orapwPRODDG1
oracle@xsnh11b335c:# sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Sep 4 16:51:01 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name , open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
PROD  READ WRITE

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

bounce instance using the new pfile:
. oraenv
PRODDG1

sqlplus / as sysdba
shutdown immediate;
startup nomount;

Verification:
Select instance_name from v$instance;
PRODDG1

Create a spfile from that pfile:

create spfile='?/dbs/spfilePRODDG1.ora' from pfile;

verify if spfile created:
!ll $ORACLE_HOME/dbs/spfilePRODDG1.ora
-rw-r-----   1 oracle     dba           5120 Apr 24 19:03 /opt/oracle/product/11.2.0.4/dbs/spfilePRODDG1.ora
Bounce db

shutdown immediate;
startup nomount;

Show parameter spfile to ensure it’s correct:
SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /opt/oracle/product/11.2.0.4/d
                                                 bs/spfilePRODDG1.ora

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

create pfile from spfile;( move old pfile with another bkp name)


SQL> select log_mode,force_logging,flashback_on from v$database;

LOG_MODE     FOR FLASHBACK_ON
------------ --- ------------------
ARCHIVELOG   YES YES

SQL> select distinct GROUP#,TYPE, (select bytes from v$log where GROUP#=lf.GROUP#) as BYTES from v$logfile lf order by group#;

    GROUP# TYPE         BYTES
---------- ------- ----------
         1 ONLINE    52428800
         2 ONLINE    52428800
         3 ONLINE    52428800
         4 STANDBY
         5 STANDBY
         6 STANDBY
         7 STANDBY

7 rows selected.

oracle@xsnh11b335c:# orapwd file=orapwPROD password=<password> entries=10
oracle@xsnh11b335c:# cp orapwPROD orapwPRODDG1
oracle@xsnh11b335c:# rman target sys/<password>@PRODDG1

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Sep 4 17:04:57 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=3237230181)

For backup configuartion :-

mkdir -p /opt/oracle/admin/PRODDG1/pfile/

/l3scripts/dba/bin/gis_rman_backup.sh SID=PRODDG1 operation=configure catalog=yes force=yes NBU_root=N-E11-BE-ORA-
The configuration file for backup should exist after this command, check its presence :
ls -lsrt /opt/oracle/admin/PRODDG1/pfile/.rman.conf
Open the file and verify that RMAN_CONNECT value and policies have all been correctly set.
Then take a full hot backup using script :
/l3scripts/dba/bin/gis_rman_backup.sh SID=PRODDG1 operation=db method=hot medium=tape level=full


cd $ORACLE_HOME/dbs
scp /opt/oracle/product/11.2.0.4/dbs/orapwPROD oracle@node2:/opt/oracle/product/11.2.0.4/dbs/orapwPRODDG2
scp /opt/oracle/product/11.2.0.4/dbs/initPRODDG1.ora oracle@node2:/opt/oracle/product/11.2.0.4/dbs/initPRODDG2.ora

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

update /etc/oratab

PRODDG2:/opt/oracle/product/11.2.0.4:Y


startup nomount;
create spfile from pfile;
shutdown immediate;
startup nomount;
show parameter pfile;



TARGT SERVER

mkdir -p /oralog1/PROD/
mkdir -p /oralog2/PROD/
mkdir -p /oradata1/PROD/
mkdir -p /oradata2/PROD/
mkdir -p /oradata3/PROD/
mkdir -p /oratemp1/PROD/
mkdir -p /oralog2/PRODDG2
mkdir -p /oralog1/PRODDG2
mkdir -p /opt/oracle/admin/PRODDG2/adump
mkdir -p /opt/oracle/admin/PROD/adump
mkdir -p /oraflashback/PRODDG2

LISTENER.ora

LISTENERDG11=
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = node2.com)(PORT =1546))
    )
  )

SID_LIST_LISTENER11 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME =  PRODDG2_DGB.com)
      (ORACLE_HOME = /opt/oracle/product/11.2.0.4)
      (SID_NAME =  PRODDG2)
    )
    (SID_DESC =
      (GLOBAL_DBNAME =  PRODDG2_DGMGRL.com)
      (ORACLE_HOME = /opt/oracle/product/11.2.0.4)
      (SID_NAME =  PRODDG2)
    )
 (SID_DESC =
      (GLOBAL_DBNAME =  PRODDG2.com)
      (ORACLE_HOME = /opt/oracle/product/11.2.0.4)
      (SID_NAME =  PRODDG2)
    )
  )

SID_LIST_LISTENERDG11 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME =  PRODDG2_DGB.com)
      (ORACLE_HOME = /opt/oracle/product/11.2.0.4)
      (SID_NAME =  PRODDG2)
    )
    (SID_DESC =
      (GLOBAL_DBNAME =  PRODDG2_DGMGRL.com)
      (ORACLE_HOME = /opt/oracle/product/11.2.0.4)
      (SID_NAME =  PRODDG2)
    )
    (SID_DESC =
      (GLOBAL_DBNAME =  PRODDG2.com)
      (ORACLE_HOME = /opt/oracle/product/11.2.0.4)
      (SID_NAME =  PRODDG2)
    )
  )


TNSNAMES.ora
PRODDG1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node1.com)(PORT = 1526))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PROD.com)
    )
  )

PRODDG2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node2.com)(PORT = 1526))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PRODDG2.com)
    )
  )

LISTENER_PROD =
  (ADDRESS = (PROTOCOL = TCP)(HOST = node2.com)(PORT = 1526))


Check the connectivity from each server

rman target sys/<password>@PRODDG1 auxiliary sys/<password>@PRODDG2

duplicate target database for standby from active database dorecover nofilenamecheck;




Once complete

TARGET

alter system set db_flashback_retention_target=2880;
shutdown immediate;
startup mount;
alter database flashback on;



recover managed standby database disconnect from session using current logfile;


BROKER CONFIGURATION

SOURCE/sqlplus
show parameter dg;
dg_broker_start                      boolean     TRUE

TARGET/sqlplus
show parameter dg;
dg_broker_start                      boolean     TRUE


Source/dgmgrl

CREATE CONFIGURATION PRODDGCONF AS PRIMARY DATABASE IS PRODDG1 CONNECT IDENTIFIER IS PRODDG1;


Source/dgmgrl
ADD DATABASE PRODDG2 AS CONNECT IDENTIFIER IS PRODDG2 MAINTAINED AS  PHYSICAL;


Source/dgmgrl

edit database PRODDG1 set property StaticConnectIdentifier='(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST = node1.com) (PORT =1546))(CONNECT_DATA=(SERVICE_NAME=PRODDG1_DGMGRL.com)(INSTANCE_NAME=PRODDG1)(SERVER=DEDICATED)))';


Source/dgmgrl

enable configuration;

TARGET/dgmgrl

edit database PRODDG2 set property StaticConnectIdentifier='(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST = node2.com)(PORT = 1546))(CONNECT_DATA=(SERVICE_NAME=PRODDG2_DGMGRL.com)(INSTANCE_NAME=PRODDG2)(SERVER=DEDICATED)))';


DGMGRL> edit database PRODdg1 set property LogXptMode='ASYNC';


DGMGRL> edit configuration set protection mode as MaxPerformance;

DGMGRL> edit database PRODdg2 set property LogXptMode='ASYNC';

DGMGRL> show database PRODdg1 LogXptMode;

DGMGRL> show database PRODdg2 LogXptMode;
 
DGMGRL> show configuration



DGMGRL>  show database verbose PRODdg1; ( verify for both databases)


Source


DGMGRL> switchover to PRODDG1; and vice versa

Source :-

Create a service to connect to Primary DB:-

exec dbms_service.stop_service('PROD.com');
exec DBMS_SERVICE.DELETE_SERVICE('PROD.com');
exec dbms_service.create_service('PROD','PROD.com');
exec dbms_service.start_service('PROD');

set lines 200 pages 5000
col network_name for a60;
select NAME,NETWORK_NAME from v$active_services;
select NAME,NETWORK_NAME from dba_services;


Source

create trigger PRIM_DB_SRV_Setup after startup on database
declare
v_role varchar(30);
begin
select database_role into v_role from v$database;
if v_role = 'PRIMARY' then
DBMS_SERVICE.START_SERVICE('PROD');
else
DBMS_SERVICE.STOP_SERVICE('PROD');
end if;
end;
/



DGMGRL> connect sys/<password>
Connected.
DGMGRL> switchover to PRODdg1
Performing switchover NOW, please wait...
Operation requires a connection to instance "PRODDG1" on database "PRODdg1"
Connecting to instance "PRODDG1"...
Connected.
New primary database "PRODdg1" is opening...
Operation requires startup of instance "PRODDG2" on database "PRODdg2"
Starting instance "PRODDG2"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "PRODdg1"
DGMGRL> switchover to PRODdg2
Performing switchover NOW, please wait...
Operation requires a connection to instance "PRODDG2" on database "PRODdg2"
Connecting to instance "PRODDG2"...
Connected.
New primary database "PRODdg2" is opening...
Operation requires startup of instance "PRODDG1" on database "PRODdg1"
Starting instance "PRODDG1"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "PRODdg2"
DGMGRL> switchover to PRODdg1;
Performing switchover NOW, please wait...
Operation requires a connection to instance "PRODDG1" on database "PRODdg1"
Connecting to instance "PRODDG1"...
Connected.
New primary database "PRODdg1" is opening...
Operation requires startup of instance "PRODDG2" on database "PRODdg2"
Starting instance "PRODDG2"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "PRODdg1"
DGMGRL> exit

NOte :- First make OID entry below then add target in Grid control( use sys user for stand by and dbsnmp for primary)

PROD 
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=node1.com)(PORT=1526))(ADDRESS=(PROTOCOL=TCP)(HOST=node2.com)(PORT=1526)))(CONNECT_DATA=(SERVICE_NAME=PROD.com)))

PRODDG1
(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = node1.com)(PORT =1526))) (CONNECT_DATA = (SERVICE_NAME = PROD.com)))

PRODDG2
(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = node2.com)(PORT =1526))) (CONNECT_DATA = (SERVICE_NAME = PRODDG2.com)))