Tag Archives: Dataguard

Use of log_archive_config parameter in Oracle Dataguard

Use of log_archive_config parameter in Oracle Dataguard

Log archive config is used to send redo logs to remote location and receipt of remote redo logs. Db unique name is defined in both init file. DB_name is same.

Values of Parameter:

SEND: Enable the sending of redo log to Remote location.
NOSEND: Disable the sending of redo log to remote location.
RECEIVE: Enable the receipt of remotely archived redo logs.
NORECEIVE: Disable the receipt of remotely archived redo logs.
DB_CONFIG: list of 9 services provider name in data-guard configuration.
NODB_CONFIG:Eliminates the list of service provider names

Note: DB_UNIQUE_NAME values of the primary and standby database should be used in the DG_CONFIG setting of the LOG_ARCHIVE_CONFIG parameter.

Example
The standby database will have the value as :

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIM,PRIM_STBY)';

Error
ORA-16191: Primary log shipping client not logged on standby

Solution
Error may fixed with by setting following parameters as follows:

LOG_ARCHIVE_CONFIG=’DG_CONFIG=(PRIM,PRIM_STBY)’ — new entry, using the DB_UNIQUE_NAME of both databases

Advertisements

ORA-19527: physical standby redo log must be renamed

ORA-19527: physical standby redo log must be renamed

Error:

ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 7 thread 0: 'G:\ORACLE\ORADATA\STANDBY05.LOG'

At DR site, we got the error which describe us to rename the standby redo log. But on checking the both DR primary and Standby has same physical location.
It did not harm the data guard replication service but we need to get rid of this error message. It seems to be false alarm in DR alert log.

Solution:
To get rid of this error message in alert log. You need to set the dummy value of parameter log_file_name_convert.
If physical structure has same directories in both Primary & Standby. you can set the dummy value of this parameter as follows:

DR> ALTER SYSTEM SET log_file_name_convert='dummy','dummy';

After the parameter was set, the ORA message was no longer seen in the alert.log

Make the dataguard Standby in Read Only mode

Make the dataguard Standby Server in read only mode

Note: Active Data guard required separate license as per Oracle Product.

In 11g, Oracle introduced the Active Data Guard feature. You can only fire the select command from this database.
This is used as your reporting database. You can fetch data real time from this database.

1. Connect to the standby database:

SET ORACLE_SID=IC_DR
sqlplus / as sysdba

2. Shutdown the standby database:

SHUTDOWN IMMEDIATE;

3. Start the standby database into mount state:

STARTUP MOUNT;

4. Open the database in the readonly mode:

ALTER DATABASE OPEN READ ONLY;

5. Then start the MRP process of recovery of the standby database:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

6. Check the alert log file of standby and primary that every thing is working fine.
 
7. Check the archive log gap on standby server with following query:

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied",
(ARCH.SEQUENCE# – APPL.SEQUENCE#) "Difference"
FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME )
IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

Check complete Status of Dataguard in Oracle

Script for Complete information about the Data Guard

From the following script you will get the complete details of dataguard including db broker parameter, sync status between two servers.

Status Script for Data guard as follows:

Script:

spool F:\dataguard.txt
set line 999 pages 999
col value for a90
col name for a50
col open_mode for a20
col database_role for a50
PROMPT ====================================
PROMPT ==GET DATA GUARD INFORMATION========
PROMPT ====================================
PROMPT

PROMPT Check archive mode, logging Mode, protection Mode,
PROMPT ==============================================================
PROMPT
col name for a10
col open_mode for a10
col databaes_role for a10
col log_mode for a15
col protection_mode for a25
select name,open_mode,database_role,log_mode,protection_mode from v$database;

PROMPT Check the value of parameters
PROMPT ==============================
PROMPT

col name for a26
col value for a50
select name, value
from v$parameter
where name in ('db_name', 'db_unique_name', 'log_archive_config', 'log_archive_dest_1', 'log_archive_dest_2', 'log_archive_dest_state_1', 'log_archive_dest_state_2', 'remote_login_passwordfile',
'log_archive_format', 'log_archive_max_processes', 'fal_server', 'db_file_name_convert', 'fal_client', 'log_file_name_convert', 'standby_file_management');

PROMPT Check the dbbroker service configured
PROMPT ===================================
PROMPT
select name,value from v$parameter where name in ('dg_broker_start','dg_broker_config_file1','dg_broker_config_file2');

PROMPT Check the archive gap
PROMPT ======================
PROMPT
col thread# for a20
col low_sequence# for a20
col high_sequence# for a20
select * from v$archive_gap;

PROMPT Check the standby manged process
PROMPT ================================
PROMPT
select process, client_process, sequence#, status from v$managed_standby;

PROMPT list the archive applied
PROMPT =========================
PROMPT
select sequence#, first_time, next_time, applied from v$archived_log;

PROMPT Check the archive or applied thread
PROMPT ====================================
PROMPT
select archived_thread#, archived_seq#, applied_thread#, applied_seq# from v$archive_dest_status;
spool off

Data Guard Protection Mode

Understanding protection mode in Dataguard Environment

Protection Mode
There are three protection modes for the primary database:

  • Maximum Availability: Transactions on the primary do not commit until redo information has been written to the online redo log and the standby redo logs of at least one standby location. If no standby location is available, it acts in the same manner as maximum performance mode until a standby becomes available again.
  • Maximum Performance: Transactions on the primary commit as soon as redo information has been written to the online redo log. Transfer of redo information to the standby server is asynchronous, so it does not impact on performance of the primary.
  • Maximum Protection: Transactions on the primary do not commit until redo information has been written to the online redo log and the standby redo logs of at least one standby location. If not suitable standby location is available, the primary database shuts down.

By default, for a newly created standby database, the primary database is in maximum performance mode.

SELECT protection_mode FROM v$database;

PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE

Three parameters determine which protection mode should be used in the transmission of the redolog data to the Standby side.

AFFIRM: This conform that a transactions is committed at the Standby Redo log.
SYNC: Redolog entries are concurrently written into the local Redolog files and transmitted to the Standby side.
NET_TIMEOUT: This parameter is only available with SYNC and AFFIRM and regulates after what time to switch to the NOAFFIRM mode.

Following parameter must be set for protection mode:
Maximum Protection mode
AFFIRM
SYNC
Maximum Availability
AFFIRM
SYNC
NET_TIMEOUT: default 30 seconds then change to maximum Performance mode.
Maximum Performance
NOAFFIRM
ASYNC

The mode can be switched using the following commands. Note the alterations in the redo transport attributes.

Maximum Availability.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

Maximum Performance.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

Maximum Protection.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
ALTER DATABASE OPEN;