Oracle 19c Easy Connect Plus

In Oracle Database 19c a new feature is introduced called “Easy Connect Plus” that will enhance connection string syntax.

Easy Connect Plus will make it easier to use features such as TLS connections, wallets, load balancing, connection timeouts, and to tune network buffer sizes without external configuration.

The following is an example of the traditional easy connect syntax against a pluggable database:

SQL> connect emad/password@dbhost.example.com:1521/pdb1

Where port number is “optional” in the command syntax, in my example its 1521

The following is Easy Connect syntax structure that is available in Oracle Database drivers (JDBC,

ODP.Net, cx_Oracle, node-oracledb etc) that use Oracle Client 19c and connect to Oracle Database 11.2 or later.

 

[[protocol:]//]host1{,host2}[:port1]{,host2:port2}[/[service_name][:

server_type][/instance_name]][?parameter_name=value{&parameter_name=

value}]

 

Protocol: transport protocol to be used while connecting to the database

host. In 19c, the supported values of protocol are TCP and TCPS. The default is TCP.

 

Hosts: The host list is a comma-separated list of host names or IP addresses which are used to connect to a database.

Ports: Port numbers indicate the ports that the database services are listening on.

When using multiple hosts, if the same port is used on each host, then you can use:

host1,host2:port Otherwise you can indicate different ports should be used:

host1:port1,host2:port2

Service Name:The service name is the service the database is known by. It is a name comprised of the database name and domain name, entered during installation or database creation.Service names can be found by running “lsnrctl services” on the database host.Note that database “system identifiers“ (SIDs) cannot be used in Easy Connect or Easy Connect Plus syntax.

 

Server Type: The server type specifies what kind of server is used on the database host to handle the connection. It can be one of DEDICATED, SHARED, or POOLED.

Instance Name: to connect to a specific instance that the service represents, you can use an instance name.

Parameters: Parameters are name-value pairs that control the behavior of connections. The syntax uses ‘?’ to indicate start of parameters and a ‘&’ delimiter between each parameter. Leading and trailing white spaces are ignored within parameter values. If whitespace is required as part of the value, it should be placed within double quotes. Parameters are described in the next section.

Examples of 19c Easy Connect Plus Syntax:

sqlplus c##emad/emad_123@tcp://oracle-19c-vagrant:1521/ORCLCDB

sqlplus c##emad/emad_123@[fe80::a00:27ff:fed0:4f43]:1521/ORCLCDB

sqlplus c##emad/emad_123@tcps://oracle-19c-vagrant:1521/ORCLCDB

 

Reference Link:

https://download.oracle.com/ocomdocs/global/Oracle-Net-19c-Easy-Connect-Plus.pdf

Oracle 19c RMAN Recovery Catalog Upgrade error RMAN-07539

After successfully upgrading an Oracle database to 19c release, the following error was thrown after attempting to upgrade the recovery catalog:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-07539: insufficient privileges to upgrade the catalog schema

SOLUTION:

connect to Recovery Catalog Database, and grant recovery catalog user the following permissions :

SQL> grant CREATE ANY CONTEXT to catalog_user;

SQL> grant drop ANY CONTEXT to catalog_user;

Then, go to the 19c upgraded database and upgrade the catalog:

rman target /

RMAN> connect catalog catalog_user/XXXXXX@catalogdb

RMAN> upgrade catalog;

RMAN> upgrade catalog;

I hope this helps !

ORA-00600 and Severity Level

if you are a DBA, then you must have faced “ORA-00600” error. This is a generic error that potentially represent a defect and you will need to contact Oracle Support for help to fix the faced problem.

The error can be found in “alert.log” file , however there are different severity levels of ORA-600….. to find out you can use the very useful view: V$diag_alert_ext

The view has a column called “message_level”  that indicates the severity level as shown below:

1 : CRITICAL: critical errors

2 : SEVERE: severe errors

8 : IMPORTANT: important message

16 : NORMAL: normal message

In a production system you will need monitor and check if either severity “1” or “2” is being thrown in the alert log file. So, basically this useful view provides more detailed information about the error…. having ORA-600 with severity level “16” is normal message alert and can be ignored.

*** This query will catch ORA-600 with severity level 1

SQL> select originating_timestamp,detailed_location,message_level,message_text,problem_key from V$diag_alert_ext where message_level=1 and message_text like’%ORA-00600%’ order by originating_timestamp desc;

 

 

 

 

 

 

 

 

 

RMAN List Backup Summary command

one of the useful RMAN commands is:

RMAN> list backup summary;

list_summary_columns.jpg

list_summary2

so what are these columns represent ?

* LV column lists the following:

0 = incremental level 0 backup

1 = incremental level 1 backup

A = archivelogs

f = full – backup database command

* S is the status:

A = available

U = unavailable

As mentioned above F can represent either “FULL” backup or “Control File” Backup. If your RMAN configuration for controlfile auto-backup is set to “ON” , the control file backup will be taken automatically each time RMAN backup runs as shown in the above picture.

The RMAN configuration is:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

 

 

 

 

 

 

 

 

 

 

ArcGIS 10.6.1 error after Oracle 18c upgrade

In ESRI ArcGIS Geodatabase environment its very important to maintain your product life-cycle support. ArcGIS 10.6.1 is compatible with Oracle 18c ….however when you upgrade both environments (ArcSDE & Oracle)  and attempt to connect through ArcGIS Desktop , the following error will be thrown:

 

DBMS error[Unkown errorNo extended error]

arcgis_error

This is a known bug by ESRI, the solution is to apply the following patch on your ArcGIS Desktop software:

https://support.esri.com/en/download/7675

 

 

Oracle oraversion

Since Oracle 18c after you successfully apply the patches on the database binaries you will notice that when you invoke sqlplus after that…the version is immediately changed/reflected:

In 19c, after patching the database binaries sqlplus is referencing 19.4 version number

Picture1

This is due to changes being reflected in a file called “oraversion” under $ORACLE_HOME/bin directory

Picture2

For example:

$ORACLE_HOME/bin/oraversion -baseVersion

Picture3

$ORACLE_HOME/bin/oraversion -compositeVersion

Picture4

 

 

 

 

 

 

 

 

 

 

 

 

 

ORA-00904: “ACDRROWTSINTCOL#

in Oracle 12cR2 database the following errors were thrown after upgrade:

 

ORA-00604: error occurred at recursive SQL level 1

ORA-00904: “ACDRROWTSINTCOL#”: invalid identifier

2019-09-05T06:20:26.344787+03:00

Error 704 happened during db open, shutting down database

2019-09-05T06:20:27.345361+03:00

Errors in file /orcl/oracc01/diag/rdbms/oracc01/oracc01/trace/oracc01_ora_116397.trc (incident=2040067):

ORA-00603: ORACLE server session terminated by fatal error

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00704: bootstrap process failure

ORA-00604: error occurred at recursive SQL level 1

ORA-00904: “ACDRROWTSINTCOL#”: invalid identifier

Incident details in: /orcl/oracc01/diag/rdbms/oracc01/oracc01/incident/incdir_2040067/oracc01_ora_116397_i2040067.trc

2019-09-05T06:20:28.519786+03:00

Non critical error ORA-48913 caught while writing to trace file “/orcl/oracc01/diag/rdbms/oracc01/oracc01/incident/incdir_2040067/oracc01_ora_116397_i2040067.trc”

 

SOLUTION:

SQL> startup upgrade;

SQL> exit;

cd $ORACLE_HOME/rdbms/admin

$ORACLE_HOME/perl/bin/perl catctl.pl -n 4 catupgrd.sql

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

SQL> startup force;

SQL> select comp_name,version from dba_registry where status = ‘VALID’;

Oracle Database java component not valid !

I have faced strange problem when applying July 2019 CPU patch in Oracle 12cR2 (12.2.0.1) database !

So I wanted to share it in my blog in case somebody faced a similar issue.

Symptoms:

Java component is not valid

select * from dba_registry;

when you execute utlrp for recompilation

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

The following error is displayed:

### validate_javavm caught -29548

In the alert log file…the following error is thrown:

joxcsys: release mismatch, 12.2.0.1.190416 1.8 in database (classes.bin) vs 12.2.0.1.190716 1.8 in executable pid 28179 cid 0

SQL> select dbms_java.longname(‘TEST’) from dual;

select dbms_java.longname(‘TEST’) from dual

*

ERROR at line 1:

ORA-29548: Java system class reported: joxcsys: release mismatch,

12.2.0.1.190416 1.8 in database (classes.bin) vs 12.2.0.1.190716 1.8 in Executable

Proposed solution:

the first way: shutdown the database and listener and perform relinking

make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk javavm_refresh ioracle

The second way:

Roll-back the OJVM patch and re-apply the patch again:

SQL> shutdown immediate;

lsnrctl stop listener_db11

$ORACLE_HOME/OPatch/opatch rollback -id 29774415

SQL> startup upgrade;

cd $ORACLE_HOME/OPatch

./datapatch –verbose

After roll-back is successful….apply the patch again and execute utlrp.sql to ensure JAVA component is now valid !

I hope this blog post helps !