Creating 10g Data Guard

Environment

Primary host : erlach
Standby host : erpsbx
ORACLE_SID=MTD
Kernel :Solaris 10 (Sparc) 64 Bit
Service names : Primary MTD / Standby MTDDG

Data Guard Creation Steps:
1. Enable Force Logging at Primary
SQL>Alter database force logging;
2. Initialization Parameters

Primary Initialization parameters

##COMMON TO BOTH PRIMARY AND STANDBY ROLES
LOG_ARCHIVE_CONFIG='DG_CONFIG=(MTD,MTDDG)'
LOG_ARCHIVE_DEST_1='LOCATION=/oracle/MTD/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=MTD'
LOG_ARCHIVE_DEST_2='SERVICE=MTDDG VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=MTDDG'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_MAX_PROCESSES=2
#log_archive_format='%t_%s_%r.dbf'
#SPECIFIC TO STANDBY ROLE
STANDBY_FILE_MANAGEMENT=AUTO
STANDBY_ARCHIVE_DEST='/oracle/MTD/arch'
FAL_SERVER=MTDDG
FAL_CLIENT=MTD

Standby Initialization parameters
db_name='MTD'
db_unique_name=MTDDG
#COMMON TO BOTH PRIMARY AND STANDBY ROLES
LOG_ARCHIVE_CONFIG='DG_CONFIG=(MTD,MTDDG)'
LOG_ARCHIVE_DEST_1='LOCATION=/oracle/MTD/arch
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=MTD'
LOG_ARCHIVE_DEST_2='SERVICE=MTDDG ARCH
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=MTDDG'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_MAX_PROCESSES=2
log_archive_format='%t_%s_%r.dbf'
#SPECIFIC TO STANDBY ROLE
STANDBY_FILE_MANAGEMENT=AUTO
STANDBY_ARCHIVE_DEST='/oracle/MTD/arch'
FAL_SERVER=MTDDG
FAL_CLIENT=MTD

Steps for init.ora:
1) Use the commands below on the primary database:
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(MTD,MTDDG)' scope=both;
alter system reset log_archive_dest scope=both sid='MTD';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1 = 'enable' scope=both;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=/oracle/MTD/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=MTD MANDATORY' scope=both;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=MTDDG ARCH REOPEN=60 NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=MTD' scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_2=enable scope=both;
alter system set LOG_ARCHIVE_MAX_PROCESSES=15 scope=both;
alter system set FAL_SERVER=MTDDG scope=both;
alter system set FAL_CLIENT=MTD scope=both;
alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both;
alter system set STANDBY_ARCHIVE_DEST='/oracle/MTD/arch' scope=both;
2) Create a pfile for transferring to the standby site:
Sql> create pfile=’initMTD.ora’ from spfile;
3) Go to the $ORACLE_HOME/dbs directory and copy the initMTD.ora in the backup directory which you will be transferring to the standby site along with the password file and standby control file

3. Net Services :

TNSNAMES.ORA sample
- On primary
MTD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = erpsbx)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = MTD)
)

MTDDG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.175.244.188)(PORT = 1527))
)
(CONNECT_DATA =
(SERVICE_NAME = MTD)
)
)

- On standby
MTD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.175.244.188)(PORT = 1527))
)
(CONNECT_DATA =
(SERVICE_NAME = MTD)
)
)
MTDDG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =erpsbx)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = MTD)
)
)

Listener.ora on standby:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = MTD)
(ORACLE_HOME = /oracle/SMD/102_64)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.175.244.188)(PORT = 1527))
)
)

4. Standby Redo Log (SRL) creation :
Create the SRL's :
Use the query below to find out all the standby redo logs that are required to be created:

select 'alter database add standby logfile '''||
regexp_substr(MEMBER,'/.+/')||'stdby_'||
regexp_replace(member,regexp_substr(MEMBER,'/.+/'),'')||
''' size '||bytes||';' "Create Standby redo"
from v$logfile lf , v$log l
where l.group# = lf.group#
union all
select 'alter database add standby logfile '''||
regexp_substr(MEMBER,'/.+/')||'stdby_redo0'||(select max(group#)+1 from v$log)||'.rdo'||
''' size '||bytes||';' "Create Standby redo"
from v$logfile lf , v$log l
where l.group# = lf.group#
and rownum <=2
/
alter database add standby logfile '/oracle/MTD/oralogs/stdby_redo_g13m1.dbf' size 52428800;
alter database add standby logfile '/oracle/MTD/oralogs/stdby_redo_g13m2.dbf' size 52428800;
alter database add standby logfile '/oracle/MTD/oralogs/stdby_redo_g12m1.dbf' size 52428800;
alter database add standby logfile '/oracle/MTD/oralogs/stdby_redo_g12m2.dbf' size 52428800;
alter database add standby logfile '/oracle/MTD/oralogs/stdby_redo_g11m1.dbf' size 52428800;
alter database add standby logfile '/oracle/MTD/oralogs/stdby_redo_g11m2.dbf' size 52428800;
alter database add standby logfile '/oracle/MTD/oralogs/stdby_redo04.rdo' size 52428800;
alter database add standby logfile '/oracle/MTD/oralogs/stdby_redo04.rdo' size 52428800;

5.Backup the Primary
Take a backup of the primary. ( You can take a hot backup or oracle recommends the usage of RMAN.)
- Backup the datafiles, online redologs and the standby logs if created and ftp / restore it on the standby site;

6.Create the standby controlfile :

Primary :

SQL>ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/stdby.ctl';

- ftp / scp the stdby.ctl file to the standby site. we can multiplex this by making copies of this stdby.ctl on different mountpoints on the standby and editing the control_files parameter in the init.ora to reflect this.
- Copy the orapw.ora file from the OH/dbs directory of the primary to the same location on the standby.
- Copy all the archives generated since the start of the backup.
- Create all the directory structures as required for the standby database.
- Also copy the password file from the primary
- Create pfile from spfile and also add to the list of files to be ftped.
SQL> create pfile='initMTD.ora' from spfile;

Verify Connectivity both ways:
We need to verify connectivity from primary to standby and standby to primary. This can be done by making a remote connection as sys:

On primary:
Sqlplus sys/@mtddg as sysdba (this can be done by opening the standby database in read only mode. See below for instructions).
Select host_name from v$instance; (should return the standby host name)
On standby:
Sqlplus sys/@mtd as sysdba
Select host_name from v$instance; (should return the primary host name)
This should result in connecting to the remote site as sys.

7. Starting the standby :
SQL>STARTUP MOUNT

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Remember: If you need to open the standby for any reason it should be done in read only mode:
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;