Adding new PDB to a CDB with Standby

6 de March de 20195 de March de 2019Elisson Almeida Leave a comment

On Oracle 12c we got a new feature on RMAN which allows you to restore database files, over the network, from a physical standby database by using the FROM SERVICE clause of the RESTORE command.

So in a Data Guard environment in case you add a new Pluggable database (PDB) you don’t need to duplicate the entire Container database (CDB) again. You can restore only the new PDB to the physical standby. Cool right, less work, faster work. I would prefer this as RMAN would do all its magic and we would not need to manual copies to renames.

On the practical side, this would be done in case you need to add a new PDB in your Data Guard configuration.

Stop the archive log apply on the standby using DG broker

validate database verbose PRIMARY;
validate database verbose STANDBY;
edit database 'STANDBY' set state='apply-off';

On the standby database restore the new PDB

rman target /
run{
allocate channel prim1 type disk;
set newname for pluggable database NEW_PDB to new;
restore pluggable database NEW_PDB from service PRIMARY;
switch datafile all;
}

– Enable recovery on the new PDB, if you running an Active DataGuard you need to stop it and start the standby database in mount, also if you get ORA-1113 make sure you are in MOUNT stage.

sqlplus / as sysdba
shtudown immediate
startup mount
alter session set container=NEW_PDB;
alter pluggable database enable recovery;
exit

– Enable archive log apply

edit database 'STANDBY' set state='apply-on';
validate database verbose PRIMARY;
validate database verbose STANDBY;

More information you can find on the MOS Note Making Use Deferred PDB Recovery and the STANDBYS=NONE Feature with Oracle Multitenant (Doc ID 1916648.1)

Hope it helps,
Elisson Almeida

After Patch: MRP0: Background Media Recovery terminated with error 10485

11 de July de 201818 de June de 2018matheusdba Leave a comment

Ok,
I had that some time ago after applying Patch 27475598 – Oracle JavaVM Component 11.2.0.4.180417 Database PSU.
Why? Well, this is Non RAC-Rolling Installable and also Not Data Guard Standby First Installable.

This means there downtime for this patch, no escape.

I had to (skipping all the standard opatch steps, you can see those on README):

Stop DG Replication:

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

Run postinstall.sql in upgrade mode with only 1 instance on (disable RAC):

cd $ORACLE_HOME/sqlpatch/27475598
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> alter system set cluster_database=false scope=spfile;
SQL> SHUTDOWN
SQL> STARTUP UPGRADE
SQL> @postinstall.sql
SQL> alter system set cluster_database=true scope=spfile;
SQL> SHUTDOWN
SQL> STARTUP

Ok, all good, seems all fine.

But now when starting my DG replication:

dgmgrl /
show configuration
show database mydg
edit database 'mydg' set state='apply-on';
show database mydg

What I see is:

DGMGRL> show database mydg

Database - mydg

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       41 minutes 53 seconds (computed 1 second ago)
  Apply Rate:      (unknown)
  Real Time Query: OFF
  Instance(s):
    myprod

  Database Error(s):
    ORA-16766: Redo Apply is stopped

Database Status:
ERROR

DGMGRL>

And on Database Alert Log:

MRP0: Background Media Recovery terminated with error 10485
Errors in file /u01/app/oracle/diag/rdbms/axwest/greporaprod/trace/greporaprod_pr00_42628.trc:
ORA-10485: Real-Time Query cannot be enabled while applying migration redo.
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
Recovery interrupted!
MRP0: Background Media Recovery process shutdown (greporaprod)

Well, in my case it happens because I use an Active Dataguard, in open read only. The solution? Start you DG in Mount Mode to apply the patching replication!

This is well described as per MOS: MRP process getting terminated with error ORA-10485 (Doc ID 1618485.1).

After getting sync, you can simple promote it to read only mode again.

Hope it helps!

Trace on Standby: Tracing MRP Process

21 de February de 201819 de January de 2018matheusdba Leave a comment

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!

ORA-01153: an incompatible media recovery is active

5 de October de 201631 de January de 2017matheusdba 1 Comment

When trying to start or increase parallel of recover manager on datagauard (MRP):

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active

I simply happen because there already are a process runnning, let’s check:

Continue reading →

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

17 de August de 201631 de January de 2017matheusdba Leave a comment

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 →

nc -l – Starting up a fake service

22 de April de 201631 de January de 2017Rafael Nólio 1 Comment

Hi everyone!

Recently i have faced a situation that made me find out a very nice and useful command that helped me a lot, and i hope it comes to help you guys as well, and it’s named:

Situation: We have a replicated environment from one datacenter to another (Using Golden Gate), where the ETL happens. So basically is:

Datacenter 1 (root data)

Replicates to datacenter 2 (transforming the data)

that replicates to datacenter 3 (production itself)

In Datacenter level 2, we have a dataguard configured. So then came the question:

What if we need to do the switchover to the standby environments?
Will we gonna have everything we need properly set up for the replication?
How are we going to test the ports if nothing is up in there? Aren’t we gonna get “connection refused”?

Calm down! There is a very nice workaround for this.

All you need to do is install the nc command as root (if it is not installed already):

yum install nc

Then execute it as follows, on the server you wanna test:

nc -l

example:

I wanna make sure that on the standby server the port 7809 (Golden Gate MANAGER port) is open. On the standby server you run:

nc -l 7809

Then, from a remote server, you are going to be able to connect through a simple telnet command:

telnet server.domain port

example:

telnet standby.company.com 7809

ON PRACTICE:

Try the telnet from the remote server to the standby:

remoteserver {/home/oracle}: telnet standby.server 7809

Trying 192.168.0.10…

telnet: connect to address 192.168.0.10: Connection refused

Then we start the fake service on the standby server!

standby.server {/home/oracle}: nc -l 7809

And try the telnet again:

remoteserver {/home/oracle}: telnet standby.server 7809

Trying 192.168.0.10…

Connected to standby.server.

Escape character is ‘^]’.

Cheers!

Rafael.

ORA-10456: cannot open standby database; media recovery session may be in progress

22 de February de 201631 de January de 2017matheusdba Leave a comment

A dataguard quick tip!

# Error

SQL> ALTER DATABASE OPEN READ ONLY;
ALTER DATABASE OPEN READ ONLY
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in progress

# Solution

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> ALTER DATABASE OPEN READ ONLY;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Database altered.

See ya! 🙂
Matheus.

ORA-10456: cannot open standby database; media recovery session may be in progress

4 de June de 201531 de January de 2017matheusdba Leave a comment

Easy, easy… Take a look:

# Error

db2database2p:>srvctl status database -d database Instance database1 is running on node db1database1p Instance database2 is not running on node db2database2p db2database2p:>srvctl start instance -d database -i database2 PRCR-1013 : Failed to start resource ora.database.db PRCR-1064 : Failed to start resource ora.database.db on node db2database2p CRS-5017: The resource action "ora.database.db start" encountered the following error: ORA-10456: cannot open standby database; media recovery session may be in progress . For details refer to "(:CLSN00107:)" in "/grid/product/11.2.0/log/db2database2p/agent/crsd/oraagent_oracle/oraagent_oracle.log". CRS-2674: Start of 'ora.database.db' on 'db2database2p' failed.

# Solution

Connected to an idle instance.

SQL> startup ORACLE instance started. Total System Global Area 1.1224E+11 bytes Fixed Size 2234920 bytes Variable Size 6.1472E+10 bytes Database Buffers 5.0466E+10 bytes Redo Buffers 299741184 bytes Database mounted. ORA-10456: cannot open standby database; media recovery session may be in progress

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Database altered. SQL> ALTER DATABASE OPEN READ ONLY; Database altered. SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options

db2database2p:>srvctl status database -d database Instance database1 is running on node db1database1p Instance database2 is running on node db2database2p

Matheus.