Configuration of the Standby database for Primary Database

Configuration of the Standby database for Primary Database Explanation:
=======================================================

Primary Database  - may have many standby Databases

Standby - may be Physical standby / logical standby
Physical standby - can open in mount mode
Logical standby - can open in open read only mode

Steps
1. Enabling the force logging from the Primary
2. Set parameter on primary database
3. Configure the standby redo logs on primary
3. Backup the primary database
4. Creating the standby control file.

Enabling the archive log mode
=======================
SQL> startup mount;
- datbase mounted
SQL> alter database archivelog;
- database altered
SQL> alter database open;
- database opened

to verify the log mode of the database:
SQL> arvhive log list
OR
SQL> select name, open_mode, log_mode from v$database;

Enabling the Force logging
====================
nologging - disabling the recording operation - for bulk loading we can disable
force logging - recording the tarnsaction (DML or DDL)

- recording the changes on the database excpet changes made on the temporary tablespace or temporary segment
- all the ongoing unlogged operations must finish before enablign the force logging.

Database level force logging has more power than the tablespace level force logging

SQL> alter database enable force logging;
SQL> select force_logging from v$database;

Creating the password file
====================

Use: authenticate the SYS users to database remotely.

- Password file must be created in primary and same has been copied over to the standby. To have same SYS password on both Priamry and standby
- This is the main pre requisite to ship the log from primary and apply at standby
- Name format
orapw<SID>
- Location
$ORACLE_HOOME/dbs

Creating password file
$ orapwd file=orapw<SID> password=<XXXX> entries=5 force=y

- $pwfile_users view to see the users who all got SYSDBBA or SYSOPER privileges

Set the initialisation parameter
========================

- create the SPFIEL if the database is running fro pfile
- SPFIEL is mandatory in dataguard broker

SQL> create spfile from pfile;

log_archive_config - specifies the unique database name for each database in the configuration
db_unique_name - unique database name for the primary DB
log_archive_dest_n - controls the redo transport service
log_archive_dest_state_n - specifies the destination state
db_file_name_convert - converts primary database file names
log_file_name_convert - converts primary database log file names
standby_file_management - controls automatic standby file management (auto / manual)
archive_log_target - forces a log switch after the specified number of seconds elapses
log_archive_trace - trace redo data transmission (delay trace in shipping)
fal_server - specify the oracle Net services name of the fal server (typically this is the database running in the primary role)
fal_client - specify the oracle Net services name of the fal client (typically this is the database running in the standby role)
remote_login_passwordfile - exclusive

log_archive_config
===============
1. specify the DG_CONFIG attribute the list the DB_UNIQUE_NAME for the Primary and each standby database in the data guard configuration
2. additional LOG_ARCHIVE_CONFIG parameter values:
- SEND: enables a database to send redo data to remote destination
- RECEIVE: enables the standby database to receive redo data another database
- Use NoSEND / NoRECEIVE to disable these setting
3. LOG_ARCHIVE_COFIG='DG_CONFIG=(prim,stand)'
4. v$dataguard_config to get unique database names in the dataguard configuration

DB_UNIQUE_NAME
=================
- uniquely identifies the primary and standby DBs
- It's recommended to have different values for primary and standby database
- used for redo transport service to identify the correct destinations

LOG_ARCHIVE_DEST_n
=====================
1. specify at least two LOG_ARCHIVE_DEST_n parameter
2. specify for local archiving and standby database location
3. must contain (at a minimum) one of the following:
- LOCATION
- SERVICE
- Service refers to the tns entry for the standby instance
4. v$archive_dest to see the current setting of log_archive_dest_n initialization parameter.

LOG_ARCHIVE_DEST_2 = 'SERVICE=stand
VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=stand'

LOG_ARCHIVE_DEST_1 = 'LOCATION=/oracle/prim/arch/
VALID_FOR=(ALL_LOGFILES, ALL_ROLES)
DB_UNIQUE_NAME=prim'

Specifying Role-Based destination
==========================
1. Specified by VALID_FOR attribute for log_arhcive_dest_n parameter
2. Values for VALID_FOR attribute: redo_log_type, database_role
3. specifies which type of logfiles to be used and when the archive destination is to be used
4. database_role:
- primary_role
- standby_role
- all_role
5. redo_log_type
- online_logfile
- standby_logfile
- all_logfile

Defining the redo Transport Mode
==========================
use the attributes of log_archuive_dest_n:
1. SYNC and ASUNC

MaxProtection / MaxAvailability - SYNC
MaxPerformance - ASYNC

2. AFFIRM and NOAFFIRM
- Ensure that redo was successfully written to disk on the standby destination
- NOAFFIRM is the default when ASYNC is specified
- AFFIRM is the default when SYNC is specified

Regards,

Mallik