Important Data Guard Process

Remote File Server (RFS) Process:
The RFS process runs on the standby database and is responsible for communication between the primary and the standby database. For the log transport service, the RFS on the standby database receives the redo records from the archiver or the log writer process of the primary database over Oracle Net and writes to the filesystem on the standby site.

Fetch Archive Log (FAL):
The FAL process has two components: FAL Client and FAL Server. Both processes are used for archive gap resolution. If the Managed Recovery Process (MRP) on the standby database site detects an archive gap sequence, it initiates a fetch request to the FAL client on the standby site. This action, in turn, requests the FAL server process on the primary database to re-transmit the archived log files to resolve the gap sequence. Archive gap sequences will be discussed later in this chapter.
Once the log transport service completes the transmission of redo records to the standby site, the log apply service starts applying the changes to the standby database. The log apply service operates solely on the standby database. The following processes on the standby site facilitate the log apply operations.

Managed Recovery Process (MRP):
The MRP  applies the redo entries from the archived redo logs onto the physical standby database.

Logical Standby Process (LSP):
The LSP applies the redo records from archived redo logs to the logical standby database. The Oracle database log miner engine is used by the logical standby process for the SQL apply operations. Using the log miner engine, the LSP process recreates the SQL statements from redo logs that have been executed on the primary database. These statements are then applied to the standby database to keep it current with the primary database.

Data Guard background processes:
In a Data Guard configuration, we can see some Oracle Data Guard specific background processes in both, primary and standby databases. These processes perform the operations of redo transport and apply services. Data Guard broker also has some specific background processes. We can see the description and duties of the most important Data Guard processes as follows:

MRP0 (Managed Standby Recovery Process): 
 coordinates the read and apply process of redo in a physical standby database.

RFS (Remote File Server):
Is responsible for receiving the redo data, which is sent by the primary database to the standby database.

LSP0 (Logical Standby Coordinator Process):
Coordinates the SQL Apply processes, which are the mining processes and apply processes.

LSP1 (Logical Standby Dictionary Build Process):
Is used on the logical standby databases when a switchover or failover is in action.

LSP2 (Logical Standby Set Guard Process):
Is used to operate Database Guard settings. Database Guard specifies which objects will be protected for modification in a logical standby database.

NSAn (Redo Transport NSA1 Process):
Is used on the primary database to ship redo data to the standby database when ASYNC mode is being used. There may be multiple NSA processes such as NSA1 and NSA2.

NSSn (Redo Transport NSA1 Process):
Is also used on the primary database to ship redo data to the standby database. However, only when the SYNC mode is being used.

DMON (Data Guard Broker Monitor Process):
runs on every instance in a Data Guard broker configuration. It communicates with local database and DMON processes of the remote databases. The broker-related requests and the monitoring information are transferred on this communication channel.

FSFP (Data Guard broker fast-start failover pinger process):
Is used for the management of fast-start failover status.

DataGuard Issues

DataGuard Issues

1):

 Warning: ORA-16826: apply service state is inconsistent with the DelayMins property

Workaround:

Check DG configuration:

DGMGRL> show configuration

Configuration - ZEE_DG_CONFIG

  Protection Mode: MaxPerformance
  Databases:
    zee   - Primary database
    zeedr - Physical standby database
      Warning: ORA-16826: apply service state is inconsistent with the DelayMins property

Fast-Start Failover: DISABLED

Configuration Status:

WARNING

Check on Which node MRP process is running.

On Standby

Make sure  Standby redo logs files are created on standby.

Please refere < Doc ID 249703.1> for the same.

DGMGRL> edit database <STANDBY DB> set state=apply-off;

DGMGRL> edit database <STANDBY DB> set state=apply-on;

OR

SQL>alter database recover managed standby database cancel;

SQL> alter database recover managed standby database using current logfile disconnect;

Verify DG Broker Configuration:

DGMGRL> show configuration

Configuration - ZEE_DG_CONFIG

  Protection Mode: MaxPerformance

  Databases:

    zee   - Primary database

    zeedr - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

---------------------------------------------------------

DGMGRL> show database zeedr

Database - zeedr

  Role:            PHYSICAL STANDBY

  Intended State:  APPLY-ON

  Transport Lag:   0 seconds

  Apply Lag:       0 seconds

  Real Time Query: OFF

  Instance(s):

    zee

Database Status:

SUCCESS

How to avoid this issue:

1. This Problem can only happen if Managed Recovery is started manually which should not be done at 

all if there is an active Data Guard Broker Configuration - reference Doc ID 249703.1 Typically the 

Data Guard Broker should start Managed Recovery.

2. It can also happen if there are no Standby RedoLogs in Place or they are incorrectly configured

- reference Doc ID 219344.1 to set them up.

Usefull Scripts related to Concurrent Manager.

 Concurrent Queries
===================

1: For checking the locks in concurrent jobs

SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,inst_id,id1, id2, lmode, request, type FROM gV$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM gV$LOCK WHERE request>0) ORDER BY id1,request;
 ============================
2: For checking last run of a Concurrent Program along with Processed time
-- Useful to find the Details of Concurrent programs which run daily and comparison purpose

SELECT DISTINCT c.USER_CONCURRENT_PROGRAM_NAME,
round(((a.actual_completion_date-a.actual_start_date)*24*60*60/60),2) AS Process_time,
a.request_id,a.parent_request_id,To_Char(a.request_date,'DD-MON-YY HH24:MI:SS'),To_Char(a.actual_start_date,'DD-MON-YY HH24:MI:SS'),
To_Char(a.actual_completion_date,'DD-MON-YY HH24:MI:SS'), (a.actual_completion_date-a.request_date)*24*60*60 AS end_to_end,
(a.actual_start_date-a.request_date)*24*60*60 AS lag_time,
d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_programs b ,
apps.FND_CONCURRENT_PROGRAMS_TL c,
apps.fnd_user d
WHERE a.concurrent_program_id= b.concurrent_program_id AND
b.concurrent_program_id=c.concurrent_program_id AND
a.requested_by =d.user_id AND
-- trunc(a.actual_completion_date) = '24-AUG-2005'
c.USER_CONCURRENT_PROGRAM_NAME='Incentive Compensation Analytics - ODI' -- and argument_text like '%, , , , ,%';
-- and status_code!='C';

=========================
3: For Checking the last run of concurrent Program.
- Use below query to check all the concurrent request running which may refer given package
-- This is very useful check before compiling any package on given instance.
-- The query can be modified as per requirement.
-- Remove FND_CONCURRENT_REQUESTS table and joins to check all program dependent on given package.

SELECT
FCR.REQUEST_ID
,FCPV.USER_CONCURRENT_PROGRAM_NAME
,FCPV.CONCURRENT_PROGRAM_NAME
,FCPV.CONCURRENT_PROGRAM_ID
,FCR.STATUS_CODE
,FCR.PHASE_CODE
FROM FND_CONCURRENT_PROGRAMS_VL FCPV
,FND_EXECUTABLES FE
,SYS.DBA_DEPENDENCIES DD
,FND_CONCURRENT_REQUESTS FCR
WHERE FCPV.EXECUTABLE_ID = FE.EXECUTABLE_ID
AND FE.EXECUTION_METHOD_CODE = 'I'
AND SUBSTR(FE.EXECUTION_FILE_NAME,1,INSTR(FE.EXECUTION_FILE_NAME, '.', 1, 1) - 1) = UPPER(DD.NAME)
AND DD.REFERENCED_TYPE IN ('VIEW', 'TABLE', 'TRIGGER', 'PACKAGE') -- add as required
--AND referenced_owner = 'XXCUS'
AND DD.REFERENCED_NAME = UPPER('&Package_name')
AND FCR.CONCURRENT_PROGRAM_ID = FCPV.CONCURRENT_PROGRAM_ID
AND fcr.phase_code NOT IN ( 'C','P');

=============================
4: The following query will display the time taken to execute the concurrent Programs
--for a particular user with the latest concurrent programs sorted in least time taken
-- to complete the request.

SELECT
f.request_id ,
pt.user_concurrent_program_name user_conc_program_name,
f.actual_start_date start_on,
f.actual_completion_date end_on,
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)
|| ' HOURS ' ||
floor((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
round((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600)/60)*60) ))
|| ' SECS ' time_difference,
p.concurrent_program_name concurrent_program_name,
decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,
f.status_code
from apps.fnd_concurrent_programs p,
apps.fnd_concurrent_programs_tl pt,
apps.fnd_concurrent_requests f
where f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV('Lang')
and f.actual_start_date is not null
order by
f.actual_start_date desc;
========================
5:  By using below Concurrent Manager and Program rules...
--Gives Detail of the Concurrent_queue_name and User_concurrent_program_name

SELECT b.concurrent_queue_name, c.user_concurrent_program_name
FROM FND_CONCURRENT_QUEUE_CONTENT a, fnd_concurrent_queues b, fnd_concurrent_programs_vl c
WHERE a.queue_application_id = 283
and a.concurrent_queue_id = b.concurrent_queue_id
and a.type_id = c.concurrent_program_id
order by decode(INCLUDE_FLAG, 'I', 1, 2), type_code;
=======================
6: Gives detail of Concurrent job completed and pending

SELECT
FCR.REQUEST_ID
,FCPV.USER_CONCURRENT_PROGRAM_NAME
,FCPV.CONCURRENT_PROGRAM_NAME
,FCPV.CONCURRENT_PROGRAM_ID
,FCR.STATUS_CODE
,FCR.PHASE_CODE
FROM FND_CONCURRENT_PROGRAMS_VL FCPV
,FND_EXECUTABLES FE
,SYS.DBA_DEPENDENCIES DD
,FND_CONCURRENT_REQUESTS FCR
WHERE FCPV.EXECUTABLE_ID = FE.EXECUTABLE_ID
AND FE.EXECUTION_METHOD_CODE = 'I'
AND SUBSTR(FE.EXECUTION_FILE_NAME,1,INSTR(FE.EXECUTION_FILE_NAME, '.', 1, 1) - 1) = UPPER(DD.NAME)
AND DD.REFERENCED_TYPE IN ('VIEW', 'TABLE', 'TRIGGER', 'PACKAGE') -- add as required
--AND referenced_owner = 'XXCUS'
AND DD.REFERENCED_NAME = UPPER('&Package_name')
AND FCR.CONCURRENT_PROGRAM_ID = FCPV.CONCURRENT_PROGRAM_ID
AND fcr.phase_code NOT IN ( 'C','P');

======================
7: wait events details related with Concurrent programs

SELECT s.saddr, s.SID, s.serial#, s.audsid, s.paddr, s.user#, s.username,
s.command, s.ownerid, s.taddr, s.lockwait, s.status, s.server,
s.schema#, s.schemaname, s.osuser, s.process, s.machine, s.terminal,
UPPER (s.program) program, s.TYPE, s.sql_address, s.sql_hash_value,
s.sql_id, s.sql_child_number, s.sql_exec_start, s.sql_exec_id,
s.prev_sql_addr, s.prev_hash_value, s.prev_sql_id,
s.prev_child_number, s.prev_exec_start, s.prev_exec_id,
s.plsql_entry_object_id, s.plsql_entry_subprogram_id,
s.plsql_object_id, s.plsql_subprogram_id, s.module, s.module_hash,
s.action, s.action_hash, s.client_info, s.fixed_table_sequence,
s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#,
s.row_wait_row#, s.logon_time, s.last_call_et, s.pdml_enabled,
s.failover_type, s.failover_method, s.failed_over,
s.resource_consumer_group, s.pdml_status, s.pddl_status, s.pq_status,
s.current_queue_duration, s.client_identifier,
s.blocking_session_status, s.blocking_instance, s.blocking_session,
s.seq#, s.event#, s.event, s.p1text, s.p1, s.p1raw, s.p2text, s.p2,
s.p2raw, s.p3text, s.p3, s.p3raw, s.wait_class_id, s.wait_class#,
s.wait_class, s.wait_time, s.seconds_in_wait, s.state,
s.wait_time_micro, s.time_remaining_micro,
s.time_since_last_wait_micro, s.service_name, s.sql_trace,
s.sql_trace_waits, s.sql_trace_binds, s.sql_trace_plan_stats,
s.session_edition_id, s.creator_addr, s.creator_serial#
FROM v$session s
WHERE ( (s.username IS NOT NULL)
AND (NVL (s.osuser, 'x') <> 'SYSTEM')
AND (s.TYPE <> 'BACKGROUND') AND STATUS='ACTIVE'
)
ORDER BY "PROGRAM";

=============================
8: To find the Database SID of the Concurrent job
-- We need our concurrent request ID as an input.
-- c.SPID= is the operating system process id
-- d.sid= is the Oracle process id

column process heading "FNDLIBR PID"
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID
AND a.phase_code = 'R';

=============================
9: To find the currently running SQL after finding SID from Query 12

SELECT SQL_TEXT FROM V$SQLAREA WHERE (ADDRESS, HASH_VALUE) IN
(SELECT SQL_ADDRESS, SQL_HASH_VALUE FROM V$SESSION WHERE SID=11710);

========================
10: To check the SID and concurrent program details, where 5991=SID

select sid,serial#,program,module,p2 from gv$session where SID=5991;

============================
11: To check time remaining in minutes and elapsed in minutes for long running job

Select round(sofar*100/totalwork,2)"finished(%)",
Sid,
Serial#,
Opname,
Target,
Sofar,
Totalwork,
Units,
(Time_Remaining/60) Time_Rem_Mins,
(Elapsed_Seconds/60) Elapsed_Time_Mins
From gV$session_Longops
where TIME_REMAINING>0;
==========================
12: To list the concurrent programs that run today,Giving details about status and logs

SELECT DISTINCT fcp.user_concurrent_program_name,
fcp.concurrent_program_name,
fcr.request_id,
fcr.request_date,
flv.meaning status,
fcr.status_code,
fcr.completion_text,
fcr.logfile_name,
fcr.outfile_name,
fcr.argument_text
FROM apps.fnd_concurrent_programs_vl fcp,
apps.fnd_concurrent_requests fcr,
apps.fnd_lookup_values flv
WHERE fcr.concurrent_program_id = fcp.concurrent_program_id
AND trunc(fcr.last_update_date) = trunc(SYSDATE)
AND flv.lookup_code = fcr.status_code
AND flv.lookup_type = 'CP_STATUS_CODE'
AND flv.language = 'US'
ORDER BY fcr.request_date,
fcr.request_id DESC;
==============================
13: Current Running SQL for a Concurrent Program

SELECT C.sql_text
,C.module
FROM APPS.fnd_concurrent_requests A
,V$SESSION B
,V$SQLAREA C
WHERE A.oracle_session_id = B.audsid
AND B.sql_hash_value = C.hash_value
AND A.request_id = <p_request_id>;--Pass the Request ID for the above Query.

SELECT SQLT.hash_value
,SQLT.sql_text
,VSES.username
,VSES.module
,VSES.command
FROM v$sqltext SQLT
,v$session VSES
,APPS.fnd_concurrent_requests FCONC
WHERE SQLT.hash_value = VSES.sql_hash_value
AND FCONC.oracle_session_id = VSES.audsid
AND FCONC.request_id = <p_request_id>;

==============================

14: How to Determine Which Manager Ran a Specific Concurrent Request?

col USER_CONCURRENT_QUEUE_NAME for a100
select b.USER_CONCURRENT_QUEUE_NAME from fnd_concurrent_processes a,
fnd_concurrent_queues_vl b, fnd_concurrent_requests c
where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID
and a.CONCURRENT_PROCESS_ID = c.controlling_manager
and c.request_id = '&conc_reqid';

=============================
15: Request submitted by User

SELECT
user_concurrent_program_name,
request_date,
request_id,
phase_code,
status_code
FROM
fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcp,
fnd_responsibility_tl fr,
fnd_user fu
WHERE
fcr.CONCURRENT_PROGRAM_ID = fcp.concurrent_program_id
and fcr.responsibility_id = fr.responsibility_id
and fcr.requested_by = fu.user_id
and user_name = '&user'
AND actual_start_date > sysdate - 1
ORDER BY REQUEST_DATE Asc;
=============================
16: To find any blocking is there in RAC or non-RAC Database

select process,sid,blocking_session,BLOCKING_INSTANCE from gv$session
where blocking_session is not null;

=======================