Tales of an oracle dba

As a lot of people we are preparing the upgrade to the next long term support version 19c.At my customer, we mainly have 12.1 non multi tentant and 12.2 multi tenant databases.
Some of them have Database Vault and TDE. 
After a successful upgrade to 19c ( 19.5, 19.6 and 19.8) using the fantastic auto upgrade tool, I need to convert it to multitenant.
I tried different ways to do this :
DBMS_PDB.describe and plug in the non_cdb remote creation of PDB
Each time we received following error or an error similar to this
create pluggable database pdbXXX from non$cdb@XXX.acme.com KEYSTORE IDENTIFIED BY "tde_key_fake_password" ; 
create pluggable database pdbXXX from non$cdb@ XXX.acme.com KEYSTORE IDENTIFIED BY "tde_key_fake_password" 
* 
ERROR at line 1: 
ORA-01078: failure in processing system parameters 
LRM-00111: no closing quote for value 'KEYSTORE_C' 

The common line in all the errors we received was KEYSTORE_C

After opening SR it was clear we hit bug 
Bug 31310564 - CLONE…

Issue with ciphers after patching OJVM

Some functionality uses the embedded JDK in the database. In this case the dev team implemented sftp functionality in java using jcraft jsch, this allows to do sftp in the database. 


The ciphers that we use require to have the Java Cryptographic Extensions to be enabled in the database home.

After successfully applying OJVM RELEASE UPDATE: 12.2.0.1.191015 (3013362, the sftp transfer stopped working on all systems except one.


with errors like this BEGIN * ERROR at line 1: ORA-20000: Java Error: com.jcraft.jsch.JSchException: Algorithm negotiation fail at com.jcraft.jsch.Session.receive_kexinit(Session.java) at com.jcraft.jsch.Session.connect(Session.java:320) at com.jcraft.jsch.Session.connect(Session.java:183) at com.xxx.util.SFTP.(SFTP:73) at com.xxx.util.SFTP.connect(SFTP:118) First checks with tcpdump and wireshark  going to the remote node of the cluster showed the same, only lower ciphers where known on the client side (in our case the DB is cli…

As many of us I took advantage of the COVID-19 isolation to learn a bit more about the Oracle Cloud, something I always put of the last years because I had some bad feelings around it and the Cloud in general.

Mid  April was a partner training around the Oracle Cloud Architect Associate which I followed, and to be honest I was blown away, this really looked like LEGO.

I followed the Training and watched the video material and started playing, this was and is really fun.

As a DBA I forgot about IP's routing and all these things, but by watching

https://learn.oracle.com/ols/course/oracle-cloud-infrastructure-architect-associate-workshop/35644/75384/102540
  a couple of times and playing with the cloud, cursing as well because it didn't work as I expected due to my misunderstanding, I learned quite a bit.

Saturday I have my Exam which btw until 15 th of may is free, meaning register your exam before the15th and do you exam later. Scheduling this exam was really good, it forced me …

A non ADG standby database is as you know running in mount mode and receiving and applying redo.

How do you connect to a database in mount, indeed with a user who has SYSDBA or SYSOPER.

In the past we monitored our standby's by either granting DBSNMP the sysdba privilege or by using SYS for the monitoring credentials.

My friend and Oracle Data Guard PM  Pieter Van Puymbroek pointed me to some interesting part of the licensing guide.























It is very important read this thoroughly : The CDB$ROOT and PDB$SEED can be READ ONLY but your PDB's still need to be in MOUNTED mode, if they are in READ ONLY and redo apply is on you need to license ACTIVE DATA GUARD


for more info have a look here



This means that a regular DBSNMP user can log on.



You will see that on the standby the open mode will be

select open_mode from v$database

OPEN_MODE
--------------------
READ ONLY WITH APPLY 




So no more giving elevated rights to the standby !

Good work oracle
And thank you Pieter for the tip

In the previous posts we have setup Data Guard with the broker.

Now lets add a PDB we will not create it from the seed but from an existing PDB using the HOT (why in bold keep on reading ) Clone feature introduced in 12.2
HOT CLONE
ok on our primary



show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 TDETEST READ WRITE NO 4 ACMEPDB READ WRITE NO 5 NO_TDE_DG READ WRITE NO sys@CDBT01> create pluggable database testphil from acmepdb; Pluggable database created.



In the alert log on the standby we see following :
2020-04-29 12:21:18.831000 +00:00 Recovery created pluggable database TESTPHIL Tablespace-SYSTEM during PDB create skipped since source is in            r/w mode or this is a refresh clone File #72 added to control file as 'UNNAMED00072…

In the previous post we created a standby database using the dbca in silent mode

In this post we will do the necessary to put this configuration in a broker config



On the primary alter system set standy_file_management='AUTO';
alter system set dg_broker_config_file1='+DATA2/c20v02t1/DATAGUARDCONFIG/dr1.dat' ;
alter system set dg_broker_config_file2='+RECO2/c20v02t1/DATAGUARDCONFIG/dr2.dat' ;
alter system set dg_broker_start= true;
alter database flashback on;
On the Standby alter system set standby_file_management='AUTO';
alter system set dg_broker_config_file1='+DATA1/c20v01t1/DATAGUARDCONFIG/dr1.dat' ;
alter system set dg_broker_config_file2='+RECO1/c20v01t1/DATAGUARDCONFIG/dr2.dat' ;
alter system set dg_broker_start= true;
alter database flashback on;
What is left to do  Create static listener entries Depending on the node ( node1 will have cdbt011 node2 cdbt012 ) add following to you SID_LIST_LISTENER

(SID_DESC =
  (SID_NAME = cdbt011)
  …