Data Guard Physical Standby Setup Using the Data Guard Broker in Oracle Database 11g Release 2

Primary:192.168.235.8
hostname: ora11g.localdomain
SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      db11g
SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      db11g

--db_name stby trùng với db_name primary, db_unique_name không trùng với primary.

Steps to change the database unique name (db_unique_name)

Logon to SQLPLUS:
--change the database unique name on the spfile.
SQL> SHOW PARAMETER db_unique_name;
SQL> ALTER SYSTEM SET db_unique_name="db11g_stby" SCOPE=spfile;

--shutdown and startup the database
SQL> shutdown immediate;
SQL> startup;


# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_HOSTNAME=ora11g.localdomain; export ORACLE_HOSTNAME
ORACLE_UNQNAME=db11g; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=db11g; export ORACLE_SID
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

---------log check
----log_mode
SQL>SELECT log_mode FROM v$database;

LOG_MODE
------------
NOARCHIVELOG

SQL>
If it is noarchivelog mode, switch is to archivelog mode.

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL>  ALTER DATABASE OPEN;
Enabled forced logging by issuing the following command.

SQL> ALTER DATABASE FORCE LOGGING;
-- Make sure at leat one logfile is present.
SQL> ALTER SYSTEM SWITCH LOGFILE;

----log_file
SQL> select * from v$logfile;
----create standby redo log file

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/db11g/standby_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/db11g/standby_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/db11g/standby_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/db11g/standby_redo04.log') SIZE 50M;

SQL> select flashback_on, status from v$database, v$instance;
SQL> ALTER DATABASE FLASHBACK ON;

SQL> show parameter standby
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

------Service Setup
primary and standby databases are needed in the "$ORACLE_HOME/network/admin/tnsnames.ora" files on both servers


db11g =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora11g.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db11g.localdomain)
    )
  )


db11g_stby =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora11gstby.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = db11g)
    )
  )
--------Config listener primary

"$ORACLE_HOME/network/admin/listener.ora"

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora11g.localdomain)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = db11g_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = db11g)
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

--------Config listener standby

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora11gstby.localdomain)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = db11g_stby_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = db11g)
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

----------restart listener
$ lsnrctl stop
$ lsnrctl start


Standby: 192.168.235.9
hostname: ora11gstby
db_name: db11g
db_unique_name: db11g_stby


# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_HOSTNAME=ora11gstby.localdomain; export ORACLE_HOSTNAME
ORACLE_UNQNAME=db11g_stby; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1; export ORACLE_HOME
ORACLE_SID=db11g; export ORACLE_SID
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH


-----Standby Server Setup

create parameter file for the standby database called "/tmp/initdb11g_stby.ora"
*.db_name='db11g'

----Create a password file
$ orapwd file=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwdb11g password=Password1 entries=10
----Create Standby Using DUPLICATE

[oracle@ora11gstby dbs]$ rman TARGET sys/Ora11g.123@db11g AUXILIARY sys/Ora11g.123@db11g_stby
DUPLICATE TARGET DATABASE
  FOR STANDBY
  FROM ACTIVE DATABASE
  DORECOVER
  SPFILE
    SET db_unique_name='db11g_stby' COMMENT 'Is standby'
  NOFILENAMECHECK;

----Enable Broker
--on both primary and standby:
SQL> ALTER SYSTEM SET dg_broker_start=true;
--on primary
$ dgmgrl sys/Ora11g.123@db11g
DGMGRL> CREATE CONFIGURATION dg_config AS PRIMARY DATABASE IS db11g CONNECT IDENTIFIER IS db11g;
--add standby
DGMGRL> ADD DATABASE db11g_stby AS CONNECT IDENTIFIER IS db11g_stby MAINTAINED AS PHYSICAL;
DGMGRL> ENABLE CONFIGURATION;
DGMGRL> SHOW CONFIGURATION;
DGMGRL> SHOW DATABASE db11g;
DGMGRL> SHOW DATABASE db11g_stby;

-----Database Switchover
DGMGRL> SWITCHOVER TO db11g_stby;

----Database Failover
DGMGRL> FAILOVER TO db11g_stby;

----Read-Only Standby and Active Data Guard
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;

source: https://oracle-base.com/articles/11g/data-guard-setup-using-broker-11gr2