For Oracle versions 12.1 and lowe, use V$MANAGED_STANDBY:
SELECT CLIENT_PROCESS, PROCESS, THREAD#, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY WHERE CLIENT_PROCESS='LGWR' OR PROCESS='MRP0' ;
CLIENT_PROCESS | PROCESS | THREAD# | SEQUENCE# | STATUS |
---|---|---|---|---|
LGWR | RFS | 1 | 116713 | RECEIVING |
N/A | MRP0 | 1 | 114039 | APPLYING_LOG |
Important columns are
* PROCESS, which is the type of process whose information is being reported
MRP0 means the detached recovery server process. In this case, it is applying logs.
* CLIENT_PROCESS, which identifies the corresponding primary database process.
LGWR indicates the background log writer process
If there is a gap to be covered, you'll see the processes as being idle:
CLIENT_PROCESS | PROCESS | THREAD# | SEQUENCE# | STATUS |
---|---|---|---|---|
LGWR | RFS | 1 | 116727 | IDLE |
N/A | MRP0 | 1 | 114115 | WAIT_FOR_LOG |
This is a good time to check where the gap is:
SELECT * FROM v$archive_gap;
THREAD# | LOW_SEQUENCE# | HIGH_SEQUENCE# | CON_ID |
---|---|---|---|
1 | 114120 | 114121 | 1 |
This matches the alert log of the standby database:
FAL[client]: Failed to request gap sequence GAP - thread 1 sequence 114120-114121
For Oracle 12.2 and beyond, Oracle encourage DBAs to use V$DATAGUARD_PROCESS (although V$MANAGED_STANDBY is still there for backward compability).
This view "displays one row for each Oracle Data Guard process that is currently running."
According to Oracle's documentation, there should be information about a process mapped toa role called "recovery logmerger" which in the ACTION column should state "APPLYING_LOG". I have repeatedly tried to find this information without luck.
What I do find in V$DATAGUARD_PROCESS is a role called "managed recovery".
If I run these queries against my database, they seem to yield two different results, the first telling me that manged recovery is idle, the second telling me that it is applying logs:
set lines 200 select name "process name",pid "standby server PID",role,action,client_role,client_pid "primary server PID" ,task_done from v$dataguard_process where role like 'managed recovery' order by role desc, action; SELECT CLIENT_PROCESS, PROCESS, THREAD#, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY WHERE CLIENT_PROCESS='LGWR' OR PROCESS='MRP0'; exit
Output:
proce standby server PID ROLE ACTION CLIENT_ROLE primary server PID T ----- ------------------------ ----------------------- ------------ ---------------- ------------------ - MRP0 10714 managed recovery IDLE none 0 N CLIENT_P PROCESS THREAD# SEQUENCE# STATUS -------- --------- ---------- ---------- ------------ LGWR RFS 1 116940 RECEIVING N/A MRP0 1 116940 APPLYING_LOG
Comments about this seemingly contradictory output would be much welcome.
No comments:
Post a Comment