Oracle PSU July 2018 error – ORA-20001: Latest xml inventory is not loaded into table

while applying latest Oracle PSU released in July 2018 for 12.1.0.2 release i have faced the following when i reached the “data patch” level:

myhostt1:orax1> ./datapatch -verbose

SQL Patching tool version 12.1.0.2.0 Production on Thu Jul 19 10:53:37 2018

Copyright (c) 2012, 2016, Oracle.  All rights reserved.

Log file for this invocation: /oracle/orax1/cfgtoollogs/sqlpatch/sqlpatch_109246_2018_07_19_10_53_37/sqlpatch_invocation.log

Connecting to database…OK

Bootstrapping registry and package to current versions…done

Queryable inventory could not determine the current opatch status.

Execute ‘select dbms_sqlpatch.verify_queryable_inventory from dual’

and/or check the invocation log

/oracle/orax1/cfgtoollogs/sqlpatch/sqlpatch_109246_2018_07_19_10_53_37/sqlpatch_invocation.log

for the complete error.

Prereq check failed, exiting without installing any patches.

Please refer to MOS Note 1609718.1 and/or the invocation log

/oraclex/oradbt14/cfgtoollogs/sqlpatch/sqlpatch_109246_2018_07_19_10_53_37/sqlpatch_invocation.log

for information on how to resolve the above errors.

SQL Patching tool complete on Thu Jul 19 10:53:41 2018

if you try to execute the following query:

SQL> select * from OPATCH_XML_INV ;

ERROR:

ORA-29913: error in executing ODCIEXTTABLEFETCH callout

ORA-29400: data cartridge error

KUP-04004: error while reading file

/oracle/orax1/product/12.1.0.2/QOpatch/qopiprep.bat

 

SQL> select dbms_sqlpatch.verify_queryable_inventory from dual;

VERIFY_QUERYABLE_INVENTORY
——————————————————————————–
ORA-20001: Latest xml inventory is not loaded into table

 

SOLUTION:

first, you need to download the latest OPatch utility 12.2.0.1.14

second, the error i have faced due to that i am invoking the opatch utility outside ORACLE_HOME…… now you need copy it in your oracle home directory ( its a must ! )

cd $ORACLE_HOME
mv $ORACLE_HOME/OPatch.orig
mkdir OPatch
cd OPatch
scp -r /newopatch/OPatch/* .

a good reference is Doc ID 1602089.1

impdp oracle error ORA-01000: maximum open cursors exceeded

my current environment were the problem was faced in:

Oracle 12cR1 (12.1.0.2.180417)

Linux Red Hat 7.4

The following errors were thrown in the import (impdp) log file :

ORA-39014: One or more workers have prematurely exited.

ORA-39029: worker 1 with process name “DW00” prematurely terminated

ORA-31671: Worker process DW00 had an unhandled exception.

ORA-01000: maximum open cursors exceeded

ORA-06512: at “SYS.KUPC$QUEUE_INT”, line 254

ORA-06512: at “SYS.KUPW$WORKER”, line 2157

ORA-01000: maximum open cursors exceeded

ORA-06512: at “SYS.KUPW$WORKER”, line 4700

ORA-01000: maximum open cursors exceeded

ORA-06512: at “SYS.KUPW$WORKER”, line 23341

To resolve this problem …you have three options:

Apply ORACLE DATABASE Patch for Bug# 23278750 for Linux-x86-64 Platforms 

OR

Upgrade to 12.2.0.1 🙂

OR

 Set the OPEN_CURSORS parameter to a value equal to or greater than the total number of indexes being imported

 

 

ALTER USER system privilege in Oracle 12cR2

In Oracle 12cR2 a new security enhancement has been introduced, the “alter user” system privilege when granted to an account….this account won’t be able to change “SYS” password anymore.

Let us explore “ALTER USER” system privilege in Oracle 12cR1 first:

1

2

 

3

 

4

while in Oracle 12cR2:

i have granted SYSTEM account the “ALTER USER” system privilege:

SQL> SELECT * FROM DBA_SYS_PRIVS where grantee=’SYSTEM’;

11

As a SYSTEM user I can’t alter the SYS user password as shown below:

12

The oracle error raised is:

ORA-01031: insufficient privileges

Not only you can’t change the password, you can’t change any activity that is related to the “ALTER USER” command such as changing the “profile”, “quota” ….etc

This feature will be very helpful if you have a segregation of duties in your database infrastructure  where account management is maintained by different entity which will definitely be granted “ALTER USER” system privilege for password management and reset….in this case they wont have the power to change the “SYS” user password.

Is there any workaround to go over this security restriction ?

Yes.

Under SYS schema create the following procedure:

SQL> create procedure sys.sys_pass_change(my_pass in varchar2) as

begin

execute immediate ‘alter user sys identified by ‘||my_pass;

end;

/

SQL> grant execute on sys.sys_pass_change to system;

Then, accessing the database as “SYSTEM” user:

SQL> exec sys.sys_pass_change(‘yarn_9ns’);

33

in this example The SYSTEM account will execute the procedure with “definer’s” right.

 

 

 

 

 

 

 

 

 

 

Applying April 2018 PSU Patch ERROR :Files check failed: Some files under ORACLE_HOME are not patched. Please see log file for details.

while applying latest Oracle database PSU (April 2018) on Oracle 12cR1 (12.1.0.2) on IBM AIX 7.1 Operating System the following errors were thrown:

Verifying the update…

There are 1 copy files under ORACLE_HOME that are not patched.
Files check failed: Some files under ORACLE_HOME are not patched. Please see log file for details.

ApplySession failed in system modification phase… ‘Verification of patch failed: Files are not updated completely.’

Restoring “/orcl/oratest/product/12.1.0.2” to the state prior to running NApply…

Checking if OPatch needs to invoke ‘make’ to restore some binaries…

OPatch was able to restore your system. Look at log file and timestamp of each file to make sure your system is in the state prior to applying the patch.

NApply restored the home. Please check your ORACLE_HOME to make sure:

– files are restored properly.

– binaries are re-linked correctly.

(use restore.[sh,bat] and make.txt (Unix only) as a reference. They are located under

“/orcl/oratest/product/12.1.0.2/.patch_storage/NApply/2018-04-19_12-37-29PM”

UtilSession failed: ApplySession failed in system modification phase… ‘Verification of patch failed: Files are not updated completely.’

Log file location: /orcl/oratest/product/12.1.0.2/cfgtoollogs/opatch/opatch2018-04-19_12-37-29PM_1.log

Solution:

use the latest Oracle OPatch utility you can download it from here:

https://updates.oracle.com/download/6880880.html

latest Opatch utility version (at the time of writing this article 🙂 ) is 12.2.0.1.13

Latest OPatch Utility

Migrate Database to CDB environment with different DB_BLOCK_SIZE

If you face a situation where you need to plugin a database that has a different block size than the destination CDB then you need perform the following:

Let us assume that the source database that will be plugged is configured with 16k block size , but the destination CDB environment is configured for 8k as shown below:

SQL> show parameter db_block_size

P1

In the CDB$root container execute the following:

SQL> alter system set db_16k_cache_size=200M scope=spfile;

//note i am using db_nk_cache_size parameter

Restart your CDB instance. And then try to plug-in the database.

Always check pdb_plug_in_violations view for any issues:

SQL> select message,time from pdb_plug_in_violations;

If I try to create a tablespace with 16K size ….. you will be able to create a tablespace successfully  with no issues:

P2

 

 

 

 

 

 

 

 

 

 

 

 

 

ORACLE_MAINTAINED column in Oracle 12c dictionary

Lets say someone asked you what are the Oracle database built-in accounts (Oracle internal) in your current database ?

Most oracle database dictionary views have a column called ORACLE_MAINTAINED that was introduced in Oracle 12c release.

https://docs.oracle.com/database/121/REFRN/GUID-309FCCB2-2E8D-4371-9FC5-7F3B10E2A8C0.htm#REFRN23302

 

SQL> select username,last_login,oracle_maintained from dba_users where oracle_maintained=’Y’;

Picture1

Finding Oracle built-in roles:

 

SQL> select role,oracle_maintained from dba_roles where oracle_maintained=’Y’;

Picture22

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Secure Oracle database binaries by updating JDK

One of the things that gets unnoticed or (overlooked) when securing Oracle Database Infrastructure is securing oracle database binaries by updating JDK build or updating SQL Developer.

When installing Oracle database binaries the version in a database install is always going to be behind the latest JDK so you should update to the latest version since latest version will include security fixes.

to check your current JDK build:

cd $ORACLE_HOME/jdk/bin

java –version

Procedure to replace JDK:

shutdown your oracle database and listener.

cd $ORACLE_HOME

mv jdk jdk.orig

//  you can download the Latest Java SE Patches/Update Releases on MOS (Doc ID 1414485.1) OR All Java SE Downloads on MOS (Doc ID 1439822.1)

//  copy the downloaded JDK to $ORACLE_HOME

scp jdk-6u181-linux-x64.bin $ORACLE_HOME

cd $ORACLE_HOME

./jdk-6u181-linux-x64.bin

cd $ORACLE_HOME

mv jdk1.6.0_181 jdk

rm -rf jdk-6u181-linux-x64.bin

To verify: 

cd $ORACLE_HOME/jdk/bin

java –version

Startup the database and listener.

Run utlrp.sql script and check that all database components are valid:

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

SQL> select * from dba_registry;

Also, check database alert log file …just in case.

If the database has “JAVA” component you can follow the steps described in this procedure for “verification”

How To Determine The JDK Version Used by the Oracle JVM in the Database (Doc ID 131872.1)

Pluggable database Service Level Access Control “Firewall”

If you are using VNC (Valid Node Checking) to implement a TNS firewall in non-CDB Oracle database architecture and wondering if there is a way to perform the same thing in Multitenant Architecture (CDB)…..Yes…through  a package DBMS_SFW_ACL_ADMIN  and its under the account: DBSFWUSER

The account has three tables: ACL$_OBJ , EXADIRECT_ACL , IP_ACL

1

First, you need to add your listener.ora file the following:

LOCAL_REGISTRATION_ADDRESS_LISTENER=ON

2

The firewall On clause means only connection coming validated against ACL will be accepted, others will be rejected. This is documented in the package specification comments as follows:

3

Running the following SQL Query in the CDB$root will provide the information of the services available:

SELECT service_id,name,network_name,pdb FROM   cdb_services;

4

To configure PDB Level Access, execute the following in CDB$root:

BEGIN

  dbsfwuser.DBMS_SFW_ACL_ADMIN.ip_add_pdb_ace(‘pdb_test2‘,’192.142.56.136‘);

  dbsfwuser.DBMS_SFW_ACL_ADMIN.commit_acl;

END;

/

In the package specification you can see the list of procedures included within the package to provide a guide what input parameters are required :

5

Checking ACL has been added:

6

To remove access control entry:

BEGIN

  dbsfwuser.DBMS_SFW_ACL_ADMIN.IP_REMOVE_PDB_ACE(‘pdb_test2′,’192.142.56.136’);

  dbsfwuser.DBMS_SFW_ACL_ADMIN.commit_acl;

END;

/

 

 

 

 

 

 

Oracle RMAN Errors RMAN-03002 RMAN-03014 RMAN-06004

The below error was received after restoring the database to a previous point in time:

RMAN-03002: failure of backup command at 08/10/2017 14:00:15

RMAN-03014: implicit resync of recovery catalog failed

RMAN-06004: ORACLE error from recovery catalog database: RMAN-20035: invalid high RECID

To resolve this either implement OPTION 1 OR OPTION 2 :

OPTION 1. unregister/register the database with recovery catalog:

— make sure you have a backup of your recovery catalog, because

it retains old  backup information of all of  your  databases.

rman target /

connect catalog_usr/catalog_usr_pass@CATALOG

RMAN> unregister database;

RMAN> register database;

OPTION 2. Open Database with Resetlogs:

sqlplus / as sysdba

SQL> ALTER DATABASE MOUNT;

SQL> ALTER DATABASE RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;

SQL> ALTER DATABASE RECOVER CANCEL;

SQL> ALTER DATABASE OPEN RESETLOGS;

rman target /

connect catalog_usr/catalog_usr_pass@CATALOG

RMAN> reset database;

RMAN> backup database;