Unified Auditing in Oracle 18c

Oracle database Unified Audit Trail was introduced in Oracle 12cR1 , as a mechanism to unify different oracle database audits (based on different features) under one view. As you may know “mixed mode” auditing is enabled by default starting with 12cR1 release. One of the limitations when switching from “standard auditing” to “unified auditing” in both Oracle 12cR1 and 12cR2 is you can’t push audits to syslog anymore. This has changed in Oracle 18c, you can push audits to SYSLOG in Unix/Linux OS and to windows event log.

A new init parameter has been introduced “unified_audit_systemlog”

https://docs.oracle.com/en/database/oracle/oracle-database/18/refrn/UNIFIED_AUDIT_SYSTEMLOG.html#GUID-C4E65709-0FE3-4945-8F39-7B0867E2494A

Picture1

In window OS I have set the parameter as TRUE as shown below:

Picture2

For simulation through RMAN I have executed command to take controlfile backup then check the windows event log:

 

Picture3.png

Another new feature in 18c , is the ability to export and import unified audit trail !

Command to export:

expdp system/XXXXXXXX full=y directory=DUMP_DIR logfile=exp_unified18c_log.log dumpfile=exp_unified18c.dmp INCLUDE=AUDIT_TRAILS

Picture4

 

 

 

 

 

 

 

 

 

Oracle 18c new feature: Cloning PDB using DBCA (Database Configuration Assistant)

Here I am cloning a local PDB using DBCA, which is a new feature in oracle 18c:

Go to

cd $ORACLE_HOME/bin

Execute the command:

dbca

make sure X-windows is configured: export DISPLAY=PCXXXX:0.0

Picture1Picture2Picture3Picture4Picture5Picture6Picture7Picture8

You can use silent installation:

In Linux:

./dbca -silent -createpluggabledatabase -sourcedb ORACLE -createpdbfrom PDB -pdbName PDB_TEST_CLONE2 -sourcepdb PDB_TEST

In Windows:

dbca -silent -createpluggabledatabase -sourcedb ORACLE -createpdbfrom PDB -pdbName PDB_TEST_CLONE2 -sourcepdb PDB_TEST

silent_install

Upgrading Oracle database from 12cR2 to 18c in Windows

Download the binary for OTN and extract the ZIP file:

Picture1

Choose “set up Software only” option:

Picture2

Upgrading existing Oracle database from 12cR2 to 18c

In the unzipped binaries go to “bin” folder then search for dbua.bat file

 

UP1UP2

UP3

You will see a list of pre-requisite checks that is being listed, in my case I will hit “Fix & Check Again” option….this will successfully eliminate the check with severity “Error” regarding recycle bin not being purged as shown below:

UP4UP5I accepted the warning message, press “Yes”

UP6

DBUA (Database Upgrade Assistant) is providing a rollback (recovery strategy) in case the upgrade fails, you have to choose on of three options:

  • Flashback to guaranteed restore point
  • Full RMAN backup – restore script is also provided as shown below
  • You will be responcible for the recovery strategy

UP7UP8UP9UP10

Wait until the upgrade finishes successfully !

After successfully upgrading your Oracle database, you can enable a new feature in 18c called “Read-Only” Oracle Homes .

This feature enables you take a software image, and separating configuration files. Also, it will improve your patching process as the patched (binaries) image can be distributed to other servers.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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