Oracle Database Product Life Cycle Support 19c,18c and now 12cR2 is extended !

Oracle has just changed their product life cycle support for their database technology especially Oracle 12cR2 !

Per Oracle ….As a one-time exception, Oracle will provide Limited Error Correction at no additional charge, for a period of 16 months after Premier Support Error Correction ends. Limited Error Correction will be in place from December 1, 2020 until March 31, 2022

The strategy is to give Oracle customers the time to upgrade to 19c (which is the stable release).

So far,  18c premier support ends June 2021 …..and 19c premier support ends March 2023

In my opinion this was too late from Oracle since many organizations were shooting for 18c for sometime in their landscape upgrades …..so this means basically any footprint of 18c in your landscape will require another upgrade to 19c !

its clear 12cR2 is a stable release compared to 18c, and even many other Oracle software solutions were late to be compatible with 18c !

 

Oracle_Product_Lifecycle_Support

 

 

 

 

 

 

Oracle SYSAUX size is exponentially expanding after upgrade to Oracle 12cR2

After upgrading Oracle database to 12cR2 SYSAUX table-space was noticed to be exponentially increasing space and table SYS. WRI$_ADV_OBJECTS was largest !

SYSAUX_TBS

Why is that ?

In Oracle 12.2 a new feature “Optimizer Statistics Advisor” [AUTO_STATS_ADVISOR_TASK] runs huge number of times causing SYSAUX tablespace to grow

SOLUTION:

Using the following query I will calculate the number of rows in table SYS. WRI$_ADV_OBJECTS:

SQL> SELECT COUNT(*) FROM WRI$_ADV_OBJECTS WHERE TASK_ID=(SELECT DISTINCT ID FROM WRI$_ADV_TASKS WHERE NAME=’AUTO_STATS_ADVISOR_TASK’);

  COUNT(*)

———-

  28251381

Migrate the rows from the current table to a new table

SQL> CREATE TABLE WRI$_ADV_OBJECTS_NEW AS SELECT * FROM WRI$_ADV_OBJECTS WHERE TASK_ID !=(SELECT DISTINCT ID FROM WRI$_ADV_TASKS WHERE NAME=’AUTO_STATS_ADVISOR_TASK’);

Table created.

Then truncate the original table:

SQL> TRUNCATE TABLE WRI$_ADV_OBJECTS;

Table truncated.

SQL> INSERT /*+ APPEND */ INTO WRI$_ADV_OBJECTS SELECT * FROM WRI$_ADV_OBJECTS_NEW;

20703 rows created.

SQL> commit;

Commit complete.

SQL> ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;

Index altered.

SQL> ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;

Index altered.

SQL> drop table WRI$_ADV_OBJECTS_NEW purge;

Table dropped.

Drop the statistics advisor task from dictionary to refrain from executing:

DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := ‘AUTO_STATS_ADVISOR_TASK’;
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/

SQL> EXEC DBMS_STATS.INIT_PACKAGE();

PL/SQL procedure successfully completed.

SQL> DECLARE

filter1 CLOB;

BEGIN

filter1 := DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER(‘AUTO_STATS_ADVISOR_TASK’,NULL,NULL,’DISABLE’);

END;

/  2    3    4    5    6

PL/SQL procedure successfully completed.

Upgrading from Oracle 12cR1 to 12cR2 error ORA-40362: invalid object SYS.ODCIPARTINFOLIST

After upgrading a database from Oracle 12cR1 to 12cR2 (Non-CDB) environment, the following errors were thrown in the post-upgrade logs, and XDB component was not valid:

Oracle Database 12.2 Post-Upgrade Status Tool           02-24-2020 09:47:50

Component                               Current         Version  Elapsed Time

Name                                    Status          Number   HH:MM:SS

Oracle Server

   ORA-40362: invalid object SYS.ODCIPARTINFOLIST specified in the statement

Oracle Server                           INVALID      12.2.0.1.0  00:06:34

Oracle Text

    ORA-06550: line 4, column 22: PLS-00905: object CTXSYS.TEXTINDEXMETHODS is i

nvalid

ORA-06550: line 4, column 6:

PL/SQL: Statement ignored

PLS-00905: object CTXSYS.TEXTINDEXMETHODS is invalid ORA-06550: line 4, colu

mn 6:

PL/SQL: Statement ignored

ORA-06550: line 4, column 6: PL/SQL: Statement ignored

    PL/SQL: Statement ignored

    ORA-06552: PL/SQL: Declaration ignored ORA-06553: PLS-905: object CTXSYS.CTX

_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905:

object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA

-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declar

ation ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-0655

2: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is

invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSY

S.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-

905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignore

d

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: D

eclaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA

-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAI

NS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object

CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553:

PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration i

gnored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/S

QL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invali

d

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_C

ONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: ob

ject CTXSYS.CTX_CONTAINS is invalid

Solution:

sqlplus / as sysdba

SQL> GRANT EXECUTE ON ODCIPartInfoList TO PUBLIC;

SQL> spool xdbreload.log

SQL> set echo on;

SQL> shutdown immediate;

SQL> startup upgrade;

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

SQL> shutdown immediate;

SQL> startup;

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

SQL> spool off

*** check database components after that:

SQL> select comp_name,status from dba_registry;

if still NOT Valid…..then perform a “drastic measure” by removing Oracle Text component and re-install it again !

To Remove Oracle Text:

sqlplus / as sysdba

SQL>@?/ctx/admin/catnoctx.sql
SQL> drop procedure sys.validate_context;
SQL>@?/rdbms/admin/utlrp.sql

check database components

SQL> select name,status from dba_registry;

if everything is Ok ….then we can install oracle text using DBCA (Database Configuration Assistant)

export DISPLAY=PCXXXXX:0.0

cd $ORACLE_HOME/bin

dbca

 

 

 

Oracle 18c Private Temporary Tables

Oracle introduced a new feature in 18c release called “Private Temporary Tables”, This feature will essentially enable you to create a temporary memory based table, and this table will ONLY be viewed by the database session created this table. Moreover, here I will compare it with another old Oracle feature “Global Temporary Tables”.

Picture1

The parameter for the predefined naming is PRIVATE_TEMP_TABLE_PREFIX

SQL> show parameter PRIVATE_TEMP_TABLE_PREFIX

Picture2

To simulate an example of creating a temporary private table:

SQL> alter session set container=ORCLPDB1;

Create a local database user in the pluggable database:

SQL> CREATE USER HR IDENTIFIED BY hr_123 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;

SQL> grant create session to hr;

SQL> grant resource to hr;

SQL> alter user hr quota unlimited on USERS;

SQL> alter user hr default role all;

Connect to the pluggable database as hr user and execute the following to create a private temporary table:

SQL> CREATE PRIVATE TEMPORARY TABLE ORA$PTT_sales_records

(time_id      DATE,

quantity_of_sale  NUMBER(10,2))

ON COMMIT PRESERVE DEFINITION;

Picture3

I will execute the following queries to insert a record in the temporary table:

SQL> insert into ORA$PTT_sales_records values(sysdate,1000);

SQL> commit;

Picture4

If I open another session with the same database account, I can’t query the newly created private temporary table as shown below:

Picture5

the following are the database views:

USER_PRIVATE_TEMP_TABLES

DBA_PRIVATE_TEMP_TABLES

CDB_PRIVATE_TEMP_TABLES

Its worth mentioning that if you attempt to create a private temporary table using SYS account, the following error will be thrown:

ORA-14451: unsupported feature with temporary table

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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