JDBC THIN CONNECTIONS OVER SCAN AND NAT FAIL WITH ORA-12516

Problem:

JDBC 11g thin clients are not able to connect to the database over SCAN and NAT. JDBC OCI and SQLPLUS connections work fine. JDBC connections to local listener work fine, only connections to SCAN listener fail.

For visibility, I will use sql developer with 11g JDBC thin driver. Actually, the problem happens on all applications using that version of JDBC.

Cause:

SQL*Plus and OCI clients use a redirect count to inform the SCAN listener about a redirected connection, but JDBC 11g and below use a lower version of NS , which does not keep a redirect count and connections are rejected.

Solution:

There are several solutions:

1.  Upgrade to JDBC 12C
2. Apply Patch 17284368 to the JDBC Thin Driver

The first option may not be acceptable for you, because it requires some changes on application side. Let’s describe how to apply patch to the JDBC thin driver:

1. Download and unzip patch file on database node(s):

$ unzip p17284368_112040_Generic.zip

2. Apply patch on db node(s):

$ cd 17284368/ 
$ $ORACLE_HOME/OPatch/opatch apply

Oracle Interim Patch Installer version 11.2.0.3.20
Copyright (c) 2019, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc
OPatch version : 11.2.0.3.20
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2019-02-22_20-26-57PM_1.log
Verifying environment and performing prerequisite checks…
OPatch continues with these patches: 17284368
Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Problem with accessing response file of "/u01/app/oracle/product/11.2.0/dbhome_1/ccr/bin/setupCCR".
Backing up files…
Applying interim patch '17284368' to OH '/u01/app/oracle/product/11.2.0/dbhome_1'
Patching component oracle.dbjava.ic, 11.2.0.4.0…
Patching component oracle.dbjava.jdbc, 11.2.0.4.0…
Patch 17284368 successfully applied.
Log file location: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2019-02-22_20-26-57PM_1.log
OPatch succeeded.

3. Copy patched $ORACLE_HOME/jdbc/lib/ojdbc5.jar to client side.

4. Restart the application:

Instead of patching database side and then copying patched ojdbc to client side, you can manually patch ojdbc thin driver without touching database.

Please consider that, manual method is for testing purposes only and is explained in the next post.

GGSCI: error while loading shared libraries: libnnz18.so: cannot open shared object file: No such file or directory

Problem:

Oracle GoldenGate Software Command Interface (GGSCI) 18c raises error:

$ /GG_HOME/home_1/ggsci
/GG_HOME/home_1/ggsci: error while loading shared libraries: libnnz18.so: cannot open shared object file: No such file or directory

Cause: 

GoldenGate put all necessary shared objects (.so files) under its own home directory, but several shared objects may not be found there. They should be borrowed from RDBMS or GI home.

Solution:

# find / -name libnnz18.so
/u01/app/18.3.0/grid/lib/libnnz18.so
/u01/app/18.3.0/grid/inventory/Scripts/ext/lib/libnnz18.so

Create symbolic link of the specidifed library in the Golden Gate home:

$ cd /GG_HOME/home_1
$ ln -s /u01/app/18.3.0/grid/lib/libnnz18.so libnnz18.so

Rerun ggsci.

 

MGTCA-1176/MGTCA-1162 : An error occurred while marking the Cluster Manifest File as expired.

If you are installing “Oracle Member Cluster for Oracle Database” and during the installation GIRM configuration assistant fails with the following error:

MGTCA-1176 : An error occurred while marking the Cluster Manifest File as expired.
MGTCA-1162 : failed to add a property to the provided Cluster Manifest File

Just give the following permission to the manifest file , to let the installer make changes there:

chmod 777 manifest.xml

There is no useful info about that on the internet and metalink! I guessed it myself.  That’s why posting that simple solution here.

DNS/NIS name service prereq failed

I was configuring an Oracle Member cluster for Databases. This type of cluster was introduced in 12.2. It has a lot of benefits. To see more information and installation steps for this type of cluster visit Oracle doc site.

Visually it looks like the following:

Domain_Cluster_arch

So during the installtion prerequisite check was complaining about “DNS/NIS name service”

DNS_NIS_Error

If you press details of the error you get a huge message. I won’t paste it’s content here, because it is something like bla bla… bla. Coud not find any meaningfull information there and lost too much time.

I seached on the Internet , still not found any good info.  Then started trying everything that came to my mind and one of them solved it.

So my entries about scan looked like the following:

Scan

Added domain name at the end:

Scan_correct

And my check looks like the following:

Prereq_OK

Please note that there may be several cases when this type of failure appeares. I showed you one of them.

Good Luck!

Exadata: Rebuild RAC clusterware without deleting data Version 2

This post is differrent from my previous post Rebuild RAC clusterware without deleting data . Because two days ago I was upgrading grid infrastructure from 12.1 to 12.2 that was successfull on first node , but failed on second node. I will not describe why this happend, but the whole process was something complicated instead of being simple. We have installed several patches before the installation(gridSetup has this option to indicate patches before installation)… Seems the 12.2 software has many bugs even during upgrade process.(But I agree  with other DBA-s that 12.2 database is very stable itself).

So what happend now is that during first node upgrade OCR files was changed. I tried deconfigure from 12.2 home and it was also failed. So now I am with my clusterware that has corrupted OCR and voting disks(it belongs 12.2 version). In my presious post I was starting clusterware in exclusive mode with nocrs and restoring OCR from backup, but now because of voting disks are different version  it does not starting in even exclusive mode.

So I have followed the steps that recreate diskgroup , where OCR and voting disks are saved. Because it is Exadata Cell Storage disks , it was more complicated than with ordinary disks, where you can cleanup header using “dd”. Instead of dd you use cellcli.

So let’s start:

Connect to each cell server(I have three of them) and drop grid disks that belong to DBFS(it contains OCR and Voting disks). Be careful dropping griddisk causes data to be erased. So DBFS must contain only OCR and Vdisks not !DATA!

#Find the name, celldisk and size of the grid disk:

CellCLI> list griddisk where name like ‘DBFS_.*’ attributes name, cellDisk, size
DBFS_CD_02_lbcel01_dr_adm CD_02_lbcel01_dr_adm 33.796875G
DBFS_CD_03_lbcel01_dr_adm CD_03_lbcel01_dr_adm 33.796875G
DBFS_CD_04_lbcel01_dr_adm CD_04_lbcel01_dr_adm 33.796875G
DBFS_CD_05_lbcel01_dr_adm CD_05_lbcel01_dr_adm 33.796875G
DBFS_CD_06_lbcel01_dr_adm CD_06_lbcel01_dr_adm 33.796875G
DBFS_CD_07_lbcel01_dr_adm CD_07_lbcel01_dr_adm 33.796875G
DBFS_CD_08_lbcel01_dr_adm CD_08_lbcel01_dr_adm 33.796875G
DBFS_CD_09_lbcel01_dr_adm CD_09_lbcel01_dr_adm 33.796875G
DBFS_CD_10_lbcel01_dr_adm CD_10_lbcel01_dr_adm 33.796875G
DBFS_CD_11_lbcel01_dr_adm CD_11_lbcel01_dr_adm 33.796875G

 

#Drop

CellCLI> drop griddisk DBFS_CD_02_lbcel01_dr_adm
drop griddisk DBFS_CD_03_lbcel01_dr_adm
drop griddisk DBFS_CD_04_lbcel01_dr_adm
drop griddisk DBFS_CD_05_lbcel01_dr_adm
drop griddisk DBFS_CD_06_lbcel01_dr_adm
drop griddisk DBFS_CD_07_lbcel01_dr_adm
drop griddisk DBFS_CD_08_lbcel01_dr_adm
drop griddisk DBFS_CD_09_lbcel01_dr_adm
drop griddisk DBFS_CD_10_lbcel01_dr_adm
drop griddisk DBFS_CD_11_lbcel01_dr_adm

#Create

cellcli> create griddisk DBFS_CD_02_lbcel01_dr_adm celldisk=CD_02_lbcel01_dr_adm, size=33.796875G
create griddisk DBFS_CD_03_lbcel01_dr_adm celldisk=CD_03_lbcel01_dr_adm, size=33.796875G
create griddisk DBFS_CD_04_lbcel01_dr_adm celldisk=CD_04_lbcel01_dr_adm, size=33.796875G
create griddisk DBFS_CD_05_lbcel01_dr_adm celldisk=CD_05_lbcel01_dr_adm, size=33.796875G
create griddisk DBFS_CD_06_lbcel01_dr_adm celldisk=CD_06_lbcel01_dr_adm, size=33.796875G
create griddisk DBFS_CD_07_lbcel01_dr_adm celldisk=CD_07_lbcel01_dr_adm, size=33.796875G
create griddisk DBFS_CD_08_lbcel01_dr_adm celldisk=CD_08_lbcel01_dr_adm, size=33.796875G
create griddisk DBFS_CD_09_lbcel01_dr_adm celldisk=CD_09_lbcel01_dr_adm, size=33.796875G
create griddisk DBFS_CD_10_lbcel01_dr_adm celldisk=CD_10_lbcel01_dr_adm, size=33.796875G
create griddisk DBFS_CD_11_lbcel01_dr_adm celldisk=CD_11_lbcel01_dr_adm, size=33.796875G

Do the same steps on other cells.

2.  Deconfigure root.sh on each node

# Run deconfig

/u01/app/12.1.0.2/grid/crs/install/rootcrs.sh -deconfig -force

#rename gpnp profile

mv /u01/app/12.1.0.2/grid/gpnp/profiles/peer/profile.xml /tmp/profile_backup.xml

3. Run root.sh on first node

/u01/app/12.1.0.2/grid/root.sh

It will fail because will not find the disk group DBFS for mounting and of course OCR inside.  But now asm is started in nomount mode and we are able to recreate diskgroup

4. Create DBFS diskgroup

sqlplus / as sysasm

SQL> create diskgroup DBFS
failgroup LBCEL01_DR_ADM disk ‘o/*/DBFS_CD_02_lbcel01_dr_adm’,’o/*/DBFS_CD_03_lbcel01_dr_adm’,’o/*/DBFS_CD_04_lbcel01_dr_adm’,’o/*/DBFS_CD_05_lbcel01_dr_adm’,’o/*/DBFS_CD_06_lbcel01_dr_adm’,’o/*/DBFS_CD_07_lbcel01_dr_adm’,’o/*/DBFS_CD_08_lbcel01_dr_adm’,’o/*/DBFS_CD_09_lbcel01_dr_adm’,’o/*/DBFS_CD_10_lbcel01_dr_adm’,’o/*/DBFS_CD_11_lbcel01_dr_adm’
failgroup LBCEL02_DR_ADM disk ‘o/*/DBFS_CD_02_lbcel02_dr_adm’,’o/*/DBFS_CD_03_lbcel02_dr_adm’,’o/*/DBFS_CD_04_lbcel02_dr_adm’,’o/*/DBFS_CD_05_lbcel02_dr_adm’,’o/*/DBFS_CD_06_lbcel02_dr_adm’,’o/*/DBFS_CD_07_lbcel02_dr_adm’,’o/*/DBFS_CD_08_lbcel02_dr_adm’,’o/*/DBFS_CD_09_lbcel02_dr_adm’,’o/*/DBFS_CD_10_lbcel02_dr_adm’,’o/*/DBFS_CD_11_lbcel02_dr_adm’
failgroup lbcel03_dr_adm disk ‘o/*/DBFS_CD_02_lbcel03_dr_adm’,’o/*/DBFS_CD_03_lbcel03_dr_adm’,’o/*/DBFS_CD_04_lbcel03_dr_adm’,’o/*/DBFS_CD_05_lbcel03_dr_adm’,’o/*/DBFS_CD_06_lbcel03_dr_adm’,’o/*/DBFS_CD_07_lbcel03_dr_adm’,’o/*/DBFS_CD_08_lbcel03_dr_adm’,’o/*/DBFS_CD_09_lbcel03_dr_adm’,’o/*/DBFS_CD_10_lbcel03_dr_adm’,’o/*/DBFS_CD_11_lbcel03_dr_adm’
ATTRIBUTE
‘compatible.asm’=’12.1.0.2.0’,
‘compatible.rdbms’=’11.2.0.2.0’,
‘au_size’=’4194304’,
‘cell.smart_scan_capable’=’TRUE’;

5. Do the following steps:

* Deconfigure root.sh again from first node
* remove gpnp profile
* run root.sh again on first node

At this time root.sh should be successful.

6. Restore OCR

/u01/app/12.1.0.2/grid/cdata/<clustername> directory contans OCR backups by default

crsctl stop crs -f
crsctl start crs -excl -nocrs
ocrconfig -restore /u01/app/12.1.0.2/grid/cdata/lbank-clus-dr/backup00.ocr
crsctl stop crs -f
crsctl start crs

7. Run root.sh on the second node

/u01/app/12.1.0.2/grid/root.sh

Installing DB Vault to an Oracle 12c non-Container Database

Database Vault is a product by which you can restrict access even for SYS, DBA users and so on , so this tool is for Security Administrators to control even DBAs.

We are configuring Oracle Audit Vault and Database Firewall  which is one product and used to find SQL injections and block harmful SQL statements . This product is perfect when you are not connecting locally. But if you connected to the server via ssh and from there connected to the database using sqlplus then Oracle AVDF will not help. For that case there exists Database Vault.

Don’t be consufed : Database Vault  and Audit Vault are two different products. DB Vault should be inabled from the database , let’s see how we do it:

— To check Oracle Label security And DB Vault use below SQL

SQL> select comp_id,status from dba_registry where comp_id in (‘OLS’,’DV’);
COMP_ID STATUS
—————————— ——————————————–
OLS VALID

Note: As you see in my database Label Security is already installed but Database Vault not.

— Check DB vault if already registered

SQL> SELECT parameter,value FROM gv$OPTION WHERE PARAMETER in
2 ( ‘Oracle Database Vault’,’Oracle Label Security’);
PARAMETER VALUE
—————————————————————- —————————————————————-
Oracle Label Security TRUE
Oracle Database Vault FALSE

–Take  backup of  some tables and views.

SQL> create table a_dba_network_acls as
select * FROM  cdb_network_acls;

SQL> create table a_dba_network_acl_privileges as
select * from cdb_network_acl_privileges;

SQL> create table a_gv$parameter as
select * from gv$parameter ;

SQL> create table a_dba_tab_privs as
Select * from dba_tab_privs;

SQL> create table a_dba_sys_privs as
Select * from dba_sys_privs;

SQL> create table a_dba_sys_privs as
Select * from dba_sys_privs;

SQL> create table a_dba_role_privs as
Select * from dba_role_privs;

SQL> create table a_dba_objects as
select owner,object_name,object_type
from dba_objects
where status=’INVALID’ and object_type <> ‘SYNONYM’ ;

SQL> create table a_dba_registry as
select * from dba_registry;

Note: You must consider that enabling Database Vault causes the followng:

1. Enables parameter sql92_security (needs database restart) .

“The SQL92 standards specify that security administrators should be able to require that users have SELECT privilege on a table when executing an UPDATE or DELETE statement that references table column values in  WHERE or SET clause. SQL92_SECURITY specifies whether users must have been granted the SELECT object privilege in order to execute such UPDATE or DELETE statements.”

So better to generate script that will grant necessary SELECT privileges:

select ‘grant select on ‘||owner||’.”‘||table_name||'” to ‘||grantee||’;’
from (
select grantee, owner, table_name
from dba_tab_privs
where privilege in (‘UPDATE’,’DELETE’,’INSERT’)
minus
select grantee, owner, table_name
from dba_tab_privs
where privilege=’SELECT’
)

Run grants generated above not to have problems after restart.

2. Revokes some privileges from roles and users (even from SYS). So use the following queries to generate grant scripts and in case it is necessary regrant them.

select ‘grant ‘||privilege||’ on ‘||owner||’.’||table_name||’ to ‘|| grantee||’;’
from (
select * from a_dba_tab_privs
minus
select * from dba_tab_privs
);

select ‘grant ‘||privilege||’ to ‘|| grantee||’;’
from (
select * from a_dba_sys_privs
minus
select * from dba_sys_privs
);

select ‘grant ‘||granted_role||’ to ‘|| grantee||’;’
from (
select * from a_dba_role_privs
minus
select * from dba_role_privs
)

–Create DB Vault owner and User Administrator users

SQL> CREATE USER dvowner IDENTIFIED BY oracle
DEFAULT TABLESPACE USERS
QUOTA UNLIMITED ON USERS;

SQL> GRANT CREATE SESSION TO dvowner;

SQL> CREATE USER dvacctmngr IDENTIFIED BY oracle
DEFAULT TABLESPACE USERS
QUOTA UNLIMITED ON USERS;

SQL> GRANT CREATE SESSION TO dvacctmngr;

 

–Configure DB Vault

SQL> BEGIN
DVSYS.CONFIGURE_DV (
dvowner_uname => ‘dvowner’,
dvacctmgr_uname => ‘dvacctmngr’);
END;
/

If your environment is like mine than you will see the followng error and go directly to the section “Scenario 2”

ORA-06550: line 2, column 1:
PLS-00201: identifier ‘DVSYS.CONFIGURE_DV’ must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored

Let’s continue the Scenario 1 when you have no errors after running DVSYS.CONFIGURE_DV and then I will write how to avoid this error.

–Recompile objects

SQL> @?/rdbms/admin/utlrp.sql

–Enable Database Vault

SQL> EXEC DBMS_MACADM.ENABLE_DV;
SQL> commit;

–Startup the Database and the installation is finished

SQL> shutdown immediate;
SQL> startup;

That’s it.

Scenario 2: Having errors: 

We should use DBCA to add option DV, I don’t support GUIs so writing script in silent mode:

–Check what options we have in DBCA

[oracle@TCIPreRel bin]$ dbca -CONFIGUREDATABASE -silent -h

-configureDatabase
-sourceDB <Database unique name for RAC Database and SID for Single Instance Database>
[-sysDBAUserName <user name with SYSDBA privileges>
-sysDBAPassword <password for sysDBAUserName user name>]
[-registerWithDirService|-unregisterWithDirService|-regenerateDBPassword <true | false>
-dirServiceUserName <user name for directory service>
-dirServicePassword <password for directory service >
-walletPassword <password for database wallet >]
[-addDBOption <Specify any of the following DB Options as a comma separated list: JSERVER | ORACLE_TEXT | IMEDIA | CWMLITE | SPATIAL | OMS | APEX | DV>]

[-dvConfiguration <true | false Specify “true” to configure and enable Database Vault
-dvUserName <Specify Database Vault Owner user name>
-dvUserPassword <Specify Database Vault Owner password>
-dvAccountManagerName <Specify separate Database Vault Account Manager >
-dvAccountManagerPassword <Specify Database Vault Account Manager password>]

–Run the configuration

[oracle@TCIPreRel bin]$ dbca -CONFIGUREDATABASE -silent -sourceDB LBTCI -sysDBAUserName sys -sysDBAPassword oracle -addDBOption DV -dvConfiguration true -dvUserName dvowne -dvUserPassword oracle -dvAccountManagerName dvacctmngr -dvAccountManagerPassword oracle

Preparing to Configure Database
2% complete
5% complete
28% complete
Adding Oracle Database Vault
85% complete
Completing Database Configuration
100% complete
Look at the log file “/u01/app/oracle/cfgtoollogs/dbca/LBTCI/LBTCI0.log” for further details.

–Restart database

srvctl stop database -db LBTCI
srvctl start database -db LBTCI

–Let’s check

SQL> SELECT parameter,value
FROM gv$OPTION
WHERE PARAMETER in ( ‘Oracle Database Vault’,’Oracle Label Security’);
PARAMETER VALUE
—————————————————————- —————————————————————-
Oracle Label Security TRUE
Oracle Database Vault TRUE

 

Oracle Management Server is Down – Failed to push ldap config data to libOvd for service instance “idstore.ldap”

I have setup Oracle Cloud Control 13c on virtual machine.

Yesterday machine was shut downed forcibly and Cloud Control was unable to start up after that.

[oracle@oragc ~]$ /u01/app/oracle/middleware/bin/emctl start oms
Oracle Enterprise Manager Cloud Control 13c Release 1
Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.
Starting Oracle Management Server...
WebTier Successfully Started
Oracle Management Server Could Not Be Started
Check EM Server log file for details: /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/EMGC_OMS1/logs/EMGC_OMS1.out
AdminServer Could Not Be Started
Check Admin Server log file for details: /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/logs/EMGC_ADMINSERVER.out
Oracle Management Server is Down
JVMD Engine is Down
Starting BI Publisher Server ...
BI Publisher Server Could Not Be Started. Check BIP Server log file for details.
BI Publisher Server Logs: /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/BIP/logs/
AdminServer Could Not Be Started
Check Admin Server log file for details: /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/logs/EMGC_ADMINSERVER.out
BI Publisher Server is Down

Let’s check the following log:

[oracle@oragc log]$ tailf /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/logs/EMGC_ADMINSERVER.out

SEVERE: Failed to push ldap config data to libOvd for service instance "idstore.ldap" in JPS context "default", 
cause: org.xml.sax.SAXException: Error Parsing at line #210: 38.org.xml.sax.SAXParseException; lineNumber: 210; 
columnNumber: 38; <Line 210, Column 38>: XML-20210: (Fatal Error) Unexpected EOF.
Jan 03, 2017 2:02:17 PM oracle.security.opss.internal.runtime.ServiceContextManagerImpl createContextInternal
WARNING: Service instance "idstore.ldap" cannot be instantiated. 
Reason: oracle.security.jps.service.idstore.IdentityStoreException: 
JPS-02592: Failed to push ldap config data to libOvd for service instance "idstore.ldap" in JPS context "default",
cause: org.xml.sax.SAXException: Error Parsing at line #210: 38.org.xml.sax.SAXParseException; 
lineNumber: 210; columnNumber: 38; <Line 210, Column 38>: XML-20210: (Fatal Error) Unexpected EOF..
Jan 03, 2017 2:02:17 PM oracle.security.jps.internal.config.OpssCommonStartup start
INFO: Jps startup failed.
<Jan 3, 2017 2:02:17 PM GET> <Error> <Security> 
<BEA-090892> <The loading of an OPSS java security policy provider failed due to an exception. 
See the exception stack trace or the server log file for the root cause. 
If there is no obvious cause, enable the debug flag -Djava.security.debug=jpspolicy to get more information. 
Error message: null>

I have found the solution on Oracle Community site: https://community.oracle.com/thread/2375509

“Please follow below steps to get you domain up and running
1) Take a backup of $DOMAIN_HOME/config/fmwconfig/ovd/default/adapters.os_xml

2) Delete $DOMAIN_HOME/config/fmwconfig/ovd/default/adapters.os_xml

3) copy adapters.os_xml from $MW_HOME/oracle_common/modules/oracle.ovd_11.1.1/templates/ to $DOMAIN_HOME/config/fmwconfig/ovd/default/

4) Restart the server.”

After performing the above steps:

[oracle@oragc ~]$ /u01/app/oracle/middleware/bin/emctl start oms
Oracle Enterprise Manager Cloud Control 13c Release 1
Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.
Starting Oracle Management Server...
WebTier Successfully Started
Oracle Management Server Successfully Started
Oracle Management Server is Up
JVMD Engine is Up
Starting BI Publisher Server ...
BI Publisher Server Successfully Started
BI Publisher Server is Up