ORACLE DATABASE 12C – CREATE DATA GUARD STANDBY DATABASE USING DBCA

syntax : dbca -createDuplicateDB 

    -gdbName global_database_name 

    -primaryDBConnectionString easy_connect_string_to_primary

    -sid database_system_identifier

    [-createAsStandby 

        [-dbUniqueName db_unique_name_for_standby]]

--

primary : prid01

standby : prid02

We will run the command from the standby  server  prid02 as shown below.

dbca -silent -createDuplicateDB -gdbName psfp -primaryDBConnectionString prid01:1521/pfspsb -sid psfp -createAsStandby -dbUniqueName psfpsb

enter sys user password

once done connect standby  and check the role.

select database_role from v$database:;

now the sp file and orapwd file automatically created.

tnsnames.ora

add the standby and prod tnsnames files

Databroker configuation

SQL> alter system set dg_broker_start=true scope=both;

System altered.

SQL> quit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle@prod01 archivelog]$ dgmgrl

DGMGRL for Linux: Release 12.2.0.1.0 - Production on Thu Mar 1914:47:27 2019

connect /

DGMGRL> create configuration 'psfp_dg'

> as primary database is 'psfp'

> connect identifier is 'psfpsb';

DGMGRL> add database 'psfpsb' as connect identifier is 'psfpsb';

DGMGRL> enable configuration;
Enabled.

Create the Standby Redo Log Files on the primary database

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oradata/psfp/redo03.log
/u01/app/oradata/psfp/redo02.log
/u01/app/oradata/psfp/redo01.log

SQL> select bytes/1048576 from v$log;

BYTES/1048576
-------------
     200
     200
     200


SQL> alter database add standby logfile '/u01/app/oradata/psfp/standy_redo1.log' size 200m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oradata/psfp/standy_redo2.log' size 200m;

Database altered.


SQL> alter database add standby logfile '/u01/app/oradata/psfp/standy_redo3.log' size 200m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oradata/psfp/standy_redo4.log' size 200m;

Database altered.


Create the Standby Redo Log Files on the standby database



DGMGRL> connect /
Connected to "psfp"
Connected as SYSDG.

DGMGRL> edit database 'psfp_sb' set state='APPLY-OFF';
Succeeded.


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

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1174405120 bytes
Fixed Size          8619984 bytes
Variable Size           436209712 bytes
Database Buffers   721420288 bytes
Redo Buffers              8155136 bytes
Database mounted.

SQL>  alter database add standby logfile '/u01/app/oradata/psfp/standy_redo1.log' size 200m;

Database altered.


SQL> alter database add standby logfile '/u01/app/oradata/psfp/standy_redo2.log' size 200m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oradata/psfp/standy_redo3.log' size 200m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oradata/psfp/standy_redo4.log' size 200m;

Database altered.

SQL> alter database open;

Database altered.

SQL>


Verify the Data Guard Configuration



DGMGRL> edit database 'psfp_sb' set state='APPLY-ON';
Succeeded.


DGMGRL> show configuration;

Configuration - psfp_dg

 Protection Mode: MaxPerformance

 psfp    - Primary database
   psfp_sb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 8 seconds ago)


Set the property StaticConnectIdentifier to prevent errors during switchover operations



Edit database ‘psfp’ set property StaticConnectIdentifier= '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prid01.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=psfp_DGMGRL)(INSTANCE_NAME=psfp)(SERVER=DEDICATED)))';
Edit database ‘psfp_sb’ set property StaticConnectIdentifier=StaticConnectIdentifier= '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prid02.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=psfp_sb_DGMGRL)(INSTANCE_NAME=psfp)(SERVER=DEDICATED)))';
Edit listener.ora on primary database host and add the lines shown below. Reload the listener.



SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = psfp_DGMGRL)
      (SID_NAME = psfp)
        )
  )



Edit listener.ora on standby database host and add the lines shown below. Reload the listener.



SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = psfp_sb_DGMGRL)
      (SID_NAME = psfp)
        )
  )



Enjoy