Trace on Standby: Tracing MRP Process

Hey all!
Recently I got the following error when trying to start a trace in a standby. The idea was to start a trace in MRP to check on performance, but when trying to use DBMS_MONITOR.session_trace_enable:

oracle:standby-srvr /tmp: sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 19 14:07:56 2018

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> exec DBMS_MONITOR.session_trace_enable(session_id =>1970, serial_num=>55126, waits=>TRUE, binds=>TRUE);
BEGIN DBMS_MONITOR.session_trace_enable(session_id =>1970, serial_num=>55126, waits=>TRUE, binds=>TRUE); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_MONITOR.SESSION_TRACE_ENABLE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

OMG!
Don’t panic, you can accomplish the same using oradebug:

-- In my case:
SQL> select process,pid from V$managed_standby where process like '%MRP%';

PROCESS PID
--------- ------------------------
MRP0 45693

SQL> oradebug setospid 19564
Oracle pid: 105, Unix process pid: 19564, image: oracle@standby-srvr (MRP0)
SQL> oradebug unlimit
Statement processed.
SQL> oradebug Event 10046 trace name context forever, level 12
Statement processed.
SQL>

You can apply same for any process/sid you need.

In case you desire to get the spid from a sid:

col machine format a30
col process format 999999
select p.spid,b.sid, p.pid
from v$session b, v$process p
where b.paddr=p.addr
and sid=&sid
/

Ok, and how to stop then?
Easy:

SQL> oradebug setospid 19564
SQL> oradebug Event 10046 trace name context off
SQL> oradebug tracefile_name

Enjoy!

Recover Standby Using an Incremental Backup

Hey all,
I know is there is a lot of posts about it in the internet, but I’m doing mine about it. The main reason is that the other has lots of steps and outputs and I miss simplicity. Hope you enjoy this one as well.

But why?
Sometimes if the standby database fails for any reason – for example when doing NOLOGGING actions in the primary database – then it can be necessary to recover the datafiles from the incremental backup as the archive logs will not contain the right information.

The recovery of the standby is relatively simple.

1. Take a cumulative incremental backup on the primary

Example

Either take a level 1 backup

backup as compressed backupset incremental level 1 cumulative database filesperset 15;

Or get the scn to recover from on the standby using the following SQL

select min(f.fhscn) from x$kcvfh f, v$datafile d where f.hxfil =d.file# and d.enabled != 'READ ONLY';

2. Run the backup using the SCN

backup as compressed backupset incremental from scn &scn;

3. Create a copy of controlfile for Standby

alter database create standby controlfile as '/tmp/newctfl.ctl';

4. Copy incremental backup files and controlfile to standby

5. Restore copied Controlfile

Example

SQL> shutdown abort; 
SQL> startup nomount; 
RMAN> restore controlfile from '/tmp/newctfl.ctl'; 
SQL> alter database mount;

6. Catalog the backup files in the standby controlfile

Example

catalog start with '/tmp/rmanbackup/';

7. Recover the database with no redo

Example

recover database noredo;

8. Start the managed recovery

Example

alter database recover managed standby database using current logfile disconnect from session;

This is also an easy alternative to the lot more complex described in MOS Rolling a Standby Forward using an RMAN Incremental Backup To Fix The Nologging Changes (Doc ID 958181.1).

Hope you enjoy!
Cheers!

DFS Lock Handle During RMAN Backup on Standby Database

Hi all!
Some time ago I faced some session on wait event DFS Lock Handle during a RMAN Backup on Standby Database. Btw, running backup on a Standby is a very interesting approach to avoid running backups on primary, so all nodes can be fully dedicated to application ends.

Turn that in my situation I noticed there was a lock with my apply process. The fix? Quite simple: Cancel apply process, run backup, and restart apply again. In my case, using DG Broker:

dgmgrl
connect / 
show configuration
show database 
edit database  set state = 'apply-off';

— Run Backup

edit database set state = 'apply-on';

 

Hope it helps!
Cheers!

After adding Datafile in Primary the MRP Stopped in Physical Standby (Dataguard)

Hi all!
After add a datafile in PRIMARY database, the STANDBY MRP stopped. An “ALTER DATABASE RECOVER MANAGED STANDBY DATABASE” does not solved te problem, as you see:

SQL> SELECT SEQUENCE#, Name, APPLIED FROM V$ARCHIVED_LOG where APPLIED  'YES' and SEQUENCE#  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SQL> SELECT SEQUENCE#, Name, APPLIED FROM V$ARCHIVED_LOG where APPLIED  'YES' and SEQUENCE# < (select max(SEQUENCE#) -1 from V$ARCHIVED_LOG);
SEQUENCE#  NAME                                                                              APPLIED
----------  --------------------------------------------------------------------------------  ---------
15075  /db/u1004/oracle/admin/MYDB/arch/arch_1_823102978_15075.arc                    NO

Continue reading