This Blog is discontinued, its only read-only

Wednesday, November 21, 2018

ALTER TABLE ADD column with DEFAULT value and Virtual Private Database

I recently came over a real strange behaviour with ALTER TABLE ADD column with DEFAULT value on one of my customers.

They are using a Software Product based on WebLogic and Oracle Database, from time to time we receive some new version of this Software Product which includes Database related changes, e.g. add new tables, add columns to an existing table and so on.

Within the latest Software Update, we could observe that the overall update process for the Database related part was increasing to around 12 hours !!!! Before it was quite shorter.

Mainly the time was taken for ALTER TABLE ADD column with DEFAULT values. After some investigations, I could see, that a simple ALTER TABLE ADD column with DEFAULT values was resulting into single updates with the provided DEFAULT value instead of updating the metadata for the given DEFAULT value. According to the Oracle Documentation in such a case the provided DEFAULT value should only be metadata and not updated for each row.

It took me some while to identify the reason (thanks to SQLHC, see My Oracle Support Note 1366133.1 https://support.oracle.com/epmos/faces/DocContentDisplay?id=1366133.1 ), I could see that on my target table for the ALTER TABLE ADD column with DEFAULT value was a Virtual Private Database policy attached with statement_types = insert, update, delete. But this policy was not enabled!
So in my opinion when I got a policy on a table which is not enabled, it should affect my ALTER TABLE ADD column command?

So, I simple dropped the complete policy and rerun my ALTER TABLE ADD column with DEFAULT value, and รจ voila my ALTER TABLE ADD column runs in milliseconds instead of serveral minutes.

Here a simple testcase to reproduce the problem:

sqlplus / as sysdba
# Create a Testuser
SQl> create user hr identified by "Oracle12c"
        default tablespace users quota unlimited on users;
SQL> grant connect, resource, create table, 
        create view, create procedure, alter session to hr;

# Connect with the above create testuser
# and create a test table with 10000000 rows
sqlplus hr/Oracle12c

# Set workarea_size_policy temporary to manual
# to avoid memory problems while creating huge test table
SQL> alter session set workarea_size_policy=manual;

Session altered.

SQL> alter session set sort_area_size=1000000000;

Session altered.

SQL> create table t1 as
     select rownum as id,
     'Just some text' as textcol,
     mod(rownum,5) as numcol1,
     mod(rownum,1000) as numcol2,
     5000 as numcol3,
     to_date ('01.'|| lpad(to_char(mod(rownum,12)+1),2,'0') || '.2018', 'dd.mm.yyyy') as time_id
     from dual connect by level<=1e7;

Table created.

SQL> select count(*) from t1;

  COUNT(*)
----------
  10000000

# Connect as sysdba
# create a demo function for VPD policy use
# and create the VPD policy including the statement_type update
# and set enable=FALSE for the policy
sqlplus / as sysdba

SQL> create or replace function no_toad_access
       (schema in varchar2, object in varchar2)
  return varchar2
  as
begin
  return 'upper(substr(sys_context(''userenv'',''module''),1,4))<>''TOAD''';
end;
/

# Create VPD policy including statement_type update
SQL> begin
  dbms_rls.add_policy (object_schema => 'HR',
  object_name => 'T1',
  policy_name => 'BAN_TOAD',
  function_schema => 'SYS',
  policy_function => 'NO_TOAD_ACCESS',
  statement_types => 'select,delete,update',
  enable => TRUE);
end;
/

# Set the VPD policy to false
SQL> begin 
   dbms_rls.enable_policy(object_schema => 'HR',
                                 object_name => 'T1',
                                 policy_name => 'BAN_TOAD',
                                 enable => FALSE);
end;
/

# Connect as testuser
# enable 10046 trace event to see that
# the ALTER TABLE ADD column with DEFAULT value
# turns to row updates
sqlplus hr/Oracle12
SQL> set timing on
SQL> alter session set tracefile_identifier='VPD_Problem_1';
SQL> alter session set events '10046 trace name context forever, level 16';
SQL> alter table t1 add (col_with_disabled_fgac varchar2(50) default 'test_1' not null);
Table altered.

Elapsed: 00:06:15.78

# Connect as sysdba
# drop the VPD policy
sqlplus / as sysdba
SQL> begin
  dbms_rls.drop_policy(object_schema => 'HR',
                       object_name => 'T1',
                       policy_name => 'BAN_TOAD');
end;
/

# Connect as testuser
# execute an ALTER TABLE ADD column with DEFAULT values again
sqlplus hr/Oracle12c
SQL> set timing on
SQL> alter session set tracefile_identifier='VPD_Problem_2';
SQL> alter session set events '10046 trace name context forever, level 16';
SQL> alter table t1 add (col_with_dropped_fgac varchar2(50) default 'test_1' not null);
Table altered.

Elapsed: 00:00:00.04

As you can see, even a disable VPD policy with statement type = update can affect your ALTER TABLE ADD column statement.

I have currently a Support Request with My Oracle Support open, to clarify if this is an expected behaviour or not, as nowhere in the Documentation I could find anything for this behaviour.


Wednesday, October 31, 2018

Oracle 18c Certification for Fusion Middleware 12c Release 2

Since a few days the Certification Matrix for Oracle Fusion Middleware 12c Release 2 (12.2.1.x) was updated within Oracle Technology Network, now Oracle 18c (18.1 on Exadata and 18.3 on On-Premise) is certified and supported as Target Database for RCU (Repository Creation Utility and as Application Datasource.

Certification Matrix for Fusion Middleware 12.2.1.2.0: https://www.oracle.com/technetwork/middleware/fusion-middleware/documentation/fmw-122120-certmatrix-3254735.xlsx



Certification Matrix for Fusion Middleware 12.2.1.3.0: https://www.oracle.com/technetwork/middleware/fmw-122130-certmatrix-3867828.xlsx


In My Oracle Support under the Certification Tab, the new Certification for Oracle Database 18c (18.1 on Exadata and 18.3 on On-Premise) is not yet updated.

See for example the Certification for SOA Suite 12.2.1.2.0


See for example the Certification for SOA Suite 12.2.1.3.0


I have created a Support Request with My Oracle Support for that and according to My Oracle Support, the Certification Matrix on Oracle Technology Network is correct and therefore Oracle 18c (18.1 on Exadata and 18.3 on On-Premise) is fully supported and certified with Oracle Fusion Middleware 12c Release 2


Friday, October 19, 2018

Installation of Oracle 18c (18.3) RPM manually

Since last night the RPM version of Oracle 18c (18.3) is available, see my blog post http://dirknachbar.blogspot.com/2018/10/oracle-18c-rpm-for-linux-available.html

I was directly testing the manually way in installing the Oracle 18c (18.3) RPM version, not Unbreakable Linux Network (ULN).

As pre requirement you will need an up and running Linux Server, in my case an Oracle Enterprise Linux 7.4, and the Oracle 18c RPM and as well the Oracle 18c Preinstallation RPM.


Transfer the 2 above mentioned files to your target server as root user in any temporary directory, navigate in a shell to the temporary directory and execute as root following commands:

1. Install the Oracle 18c Preinstallation RPM

yum localinstall oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm 
Loaded plugins: langpacks, ulninfo
Examining oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm: oracle-database-preinstall-18c-1.0-1.el7.x86_64
Marking oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package oracle-database-preinstall-18c.x86_64 0:1.0-1.el7 will be installed
--> Processing Dependency: glibc-devel for package: oracle-database-preinstall-18c-1.0-1.el7.x86_64
--> Processing Dependency: ksh for package: oracle-database-preinstall-18c-1.0-1.el7.x86_64
--> Processing Dependency: libaio-devel for package: oracle-database-preinstall-18c-1.0-1.el7.x86_64
--> Processing Dependency: libstdc++-devel for package: oracle-database-preinstall-18c-1.0-1.el7.x86_64
--> Running transaction check
---> Package glibc-devel.x86_64 0:2.17-196.el7 will be installed
--> Processing Dependency: glibc-headers = 2.17-196.el7 for package: glibc-devel-2.17-196.el7.x86_64
--> Processing Dependency: glibc-headers for package: glibc-devel-2.17-196.el7.x86_64
---> Package ksh.x86_64 0:20120801-34.el7 will be installed
---> Package libaio-devel.x86_64 0:0.3.109-13.el7 will be installed
---> Package libstdc++-devel.x86_64 0:4.8.5-16.el7 will be installed
--> Running transaction check
---> Package glibc-headers.x86_64 0:2.17-196.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

====================================================================================================================================================================
 Package                                     Arch                Version                        Repository                                                     Size
====================================================================================================================================================================
Installing:
 oracle-database-preinstall-18c              x86_64              1.0-1.el7                      /oracle-database-preinstall-18c-1.0-1.el7.x86_64               55 k
Installing for dependencies:
 glibc-devel                                 x86_64              2.17-196.el7                   OL74                                                          1.1 M
 glibc-headers                               x86_64              2.17-196.el7                   OL74                                                          675 k
 ksh                                         x86_64              20120801-34.el7                OL74                                                          883 k
 libaio-devel                                x86_64              0.3.109-13.el7                 OL74                                                           12 k
 libstdc++-devel                             x86_64              4.8.5-16.el7                   OL74                                                          1.5 M

Transaction Summary
====================================================================================================================================================================
Install  1 Package (+5 Dependent packages)

Total size: 4.1 M
Total download size: 4.1 M
Installed size: 14 M
Is this ok [y/d/N]: y
Downloading packages:
warning: /var/OSimage/OL7.4_x86_64/Packages/glibc-devel-2.17-196.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
Public key for glibc-devel-2.17-196.el7.x86_64.rpm is not installed
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                52 MB/s | 4.1 MB  00:00:00     
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY
Importing GPG key 0xEC551F03:
 Userid     : "Oracle OSS group (Open Source Software group) <build@oss.oracle.com>"
 Fingerprint: 4214 4123 fecf c55b 9086 313d 72f9 7b74 ec55 1f03
 Package    : 7:oraclelinux-release-7.4-1.0.4.el7.x86_64 (@anaconda/7.4)
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY
Is this ok [y/N]: y
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : ksh-20120801-34.el7.x86_64                                                                                                                       1/6 
  Installing : glibc-headers-2.17-196.el7.x86_64                                                                                                                2/6 
  Installing : glibc-devel-2.17-196.el7.x86_64                                                                                                                  3/6 
  Installing : libaio-devel-0.3.109-13.el7.x86_64                                                                                                               4/6 
  Installing : libstdc++-devel-4.8.5-16.el7.x86_64                                                                                                              5/6 
  Installing : oracle-database-preinstall-18c-1.0-1.el7.x86_64                                                                                                  6/6 
  Verifying  : oracle-database-preinstall-18c-1.0-1.el7.x86_64                                                                                                  1/6 
  Verifying  : libstdc++-devel-4.8.5-16.el7.x86_64                                                                                                              2/6 
  Verifying  : libaio-devel-0.3.109-13.el7.x86_64                                                                                                               3/6 
  Verifying  : glibc-headers-2.17-196.el7.x86_64                                                                                                                4/6 
  Verifying  : glibc-devel-2.17-196.el7.x86_64                                                                                                                  5/6 
  Verifying  : ksh-20120801-34.el7.x86_64                                                                                                                       6/6 

Installed:
  oracle-database-preinstall-18c.x86_64 0:1.0-1.el7                                                                                                                 

Dependency Installed:
  glibc-devel.x86_64 0:2.17-196.el7          glibc-headers.x86_64 0:2.17-196.el7      ksh.x86_64 0:20120801-34.el7      libaio-devel.x86_64 0:0.3.109-13.el7     
  libstdc++-devel.x86_64 0:4.8.5-16.el7     

Complete!

2. Install the Oracle 18c (18.3) RPM

yum localinstall oracle-database-ee-18c-1.0-1.x86_64.rpm 
Loaded plugins: langpacks, ulninfo
Examining oracle-database-ee-18c-1.0-1.x86_64.rpm: oracle-database-ee-18c-1.0-1.x86_64
Marking oracle-database-ee-18c-1.0-1.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package oracle-database-ee-18c.x86_64 0:1.0-1 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

====================================================================================================================================================================
 Package                                     Arch                        Version                    Repository                                                 Size
====================================================================================================================================================================
Installing:
 oracle-database-ee-18c                      x86_64                      1.0-1                      /oracle-database-ee-18c-1.0-1.x86_64                      7.8 G

Transaction Summary
====================================================================================================================================================================
Install  1 Package

Total size: 7.8 G
Installed size: 7.8 G
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : oracle-database-ee-18c-1.0-1.x86_64                                                                                                              1/1 
[INFO] Executing post installation scripts...
[INFO] Oracle home installed successfully and ready to be configured.
To configure a sample Oracle Database you can execute the following service configuration script as root: /etc/init.d/oracledb_ORCLCDB-18c configure
  Verifying  : oracle-database-ee-18c-1.0-1.x86_64                                                                                                              1/1 

Installed:
  oracle-database-ee-18c.x86_64 0:1.0-1                                                                                                                             

Complete!

As next we need to configure the Oracle 18c Database by executing the /etc/init.d/oracledb_ORCLDB-18c script as root user

In case you will receive following error message while executing the /etc/init.d/oracledb_ORCLDB-18c script, simply check your /etc/hosts file and add the IP, Fully Qualified Hostname and the Shortname of your server, re-execute the /etc/init.d/oracledb_ORCLCDB-18c script
/etc/init.d/oracledb_ORCLCDB-18c configure
Configuring Oracle Database ORCLCDB.
[FATAL] [DBT-06103] The port (1,521) is already in use.
   ACTION: Specify a free port.

/etc/init.d/oracledb_ORCLCDB-18c configure
Configuring Oracle Database ORCLCDB.
Prepare for db operation
8% complete
Copying database files
31% complete
Creating and starting Oracle instance
32% complete
36% complete
40% complete
43% complete
46% complete
Completing Database Creation
51% complete
54% complete
Creating Pluggable Databases
58% complete
77% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
 /opt/oracle/cfgtoollogs/dbca/ORCLCDB.
Database Information:
Global Database Name:ORCLCDB
System Identifier(SID):ORCLCDB
Look at the log file "/opt/oracle/cfgtoollogs/dbca/ORCLCDB/ORCLCDB.log" for further details.

Database configuration completed successfully. The passwords were auto generated, you must change them by connecting to the database using 'sqlplus / as sysdba' as the oracle user.

And we are nearly done :-)

Switch in your shell to the oracle user:

su - oracle
cd /opt/oracle
export ORACLE_BASE=`pwd`
cd product/18c/dbhome_1
export ORACLE_HOME=`pwd`
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=ORCLCDB
sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Fri Oct 19 13:54:58 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> col name format a30
SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME      OPEN_MODE
---------- ------------------------------ ----------
  2 PDB$SEED     READ ONLY
  3 ORCLPDB1     READ WRITE


You only need to change now the passwords of the Database Users, e.g. SYS, SYSTEM and so on.

It's really a quick and fast way to install and configure an Oracle 18c (18.3) release on your server, what I personally don't like, is the used OFA (Optimal/Oracle Flexible Architecture) layout provided within the RPM, everything goes under /opt ... :-(


Oracle 18c RPM for Linux available

Since last night, the RPM for Oracle Database 18c (18.3) for Linux x86-64 is available for download in Oracle Technology Network



The RPM can be downloaded under following link: https://www.oracle.com/technetwork/database/enterprise-edition/downloads/oracle18c-linux-180000-5022980.html

The corresponding documentation can be found under: https://docs.oracle.com/en/database/oracle/oracle-database/18/ladbi/automatically-configuring-oracle-linux-with-oracle-preinstallation-rpm.html#GUID-22846194-58EF-4552-AAC3-6F6D0A1DF794


Wednesday, September 26, 2018

Java 11 General Availability

Right on time as scheduled Java 11 has become General Availability. This is the first Java Release under the new license with the so called Long Term Support (LTS).

The latest Java SE 11 (LTS) can be found under https://www.oracle.com/technetwork/java/javase/downloads/index-jsp-138363.html


But before using this release in productive environments, just make sure that you really meet the new license or consult your Oracle Sales ;-)

Friday, July 6, 2018

Documentation for Oracle Enterprise Manager 13c Release 3 (13.3.0.0) available

Since a few days the latest Release of Oracle Enterprise Manager 13c Release 3 (13.3.0.0) is available for download (see my blogpost "Oracle Enterprise Manager 13c Release 3 (13.3.0.0) available" ) but the corresponding documentation was not yet available.

But now Oracle also uploaded the documentation for Oracle Enterprise Manager 13c Release 3 (13.3.0.0), which can be found under https://docs.oracle.com/cd/cloud-control-13.3/index.html


Under the section "What's New" you can find all new features of the latest Enterprise Manager 13c Release 3 (13.3.0.0).
Some highlights are:

  • Customization of the Enterprise Manager Login Page
  • Fusion Middleware Section:
    • MSI Startup and Shutdown possibilities for WebLogic Managed Servers while the AdminServer is unavailable
    • Now you can start the Admin Server of a WebLogic Server Domain through the Node Manager

The full list of the New Features can be found under https://docs.oracle.com/cd/cloud-control-13.3/EMCON/GUID-503991BC-D1CD-46EC-8373-8423B2D43437.htm#EMCON-GUID-503991BC-D1CD-46EC-8373-8423B2D43437


Wednesday, July 4, 2018

Oracle Enterprise Manager 13c Release 3 (13.3.0.0) available

The lastest Release of Oracle Enterprise Manager 13c Release 3 (13.3.0.0) is available for download under Oracle Technology Network: https://www.oracle.com/technetwork/oem/enterprise-manager/downloads/index.html



The Oracle Enterprise Manager 13c Release 3 (13.3.0.0) is available for following Operating Systems:

  • Linux x86-64 (64-bit)
  • Windows x86-64 (64-bit)
  • Solaris Operating System (SPARC)
  • Solaris Operating System (x86-64)
  • IBM AIX on POWER Systems (64-bit)
  • HP-UX Itanium (64-bit)
Unfortunately the Documentation for Oracle Enterprise Manager 13c Release 3 (13.3.0.0) is currently not yet uploaded under https://docs.oracle.com/en/enterprise-manager/ . I hope it will be uploaded within the next days.


Friday, June 29, 2018

Oracle Forms 12.2.1.3.0 and FRM- 93552 on Windows

I recently had a project for installing and configure Oracle Forms & Reports 12.2.1.3.0 on a Windows 2016 Server. While the installation and configuration of Oracle Forms & Reports 12.2.1.3.0 went really smooth, I was hitting directly after using the Oracle provided Test Forms under http://:/forms/frmservlet the FRM-93552 error.

The official Oracle Documentation states that you have to install the Microsoft Visual C++ Redistributable 2012 (VC++ 11.0), but with this Version you will hit the FRM-93552 error message while calling your Forms.

You will need to install the Microsoft Visual C++ Redistributable 2010 (VC++ 10.0), which you can find under following link https://www.microsoft.com/en-us/download/details.aspx?id=14632



Just install the above mentioned version of the Microsoft Visual C++ Redistributable 2010, you can have multiple version of the Microsoft Visual C++ Redistributable package at the same time on your server.

After the installation of the Microsoft Visual C++ Redistributable 2010 simple stop and start your Managed Server for Oracle Forms and the problem with the FRM-93552 error is gone.


Wednesday, May 23, 2018

Oracle Reports 12c and DESTYPE=blobdestination

Last week a colleague of mine contacted me to support him for a problem to configure and use Oracle Reports 12c (12.2.1.2.0 and 12.2.1.3.0) with DESTYPE=blobdestination, which means instead of displaying a rendered Report like PDF or XLS or HTML in the Browser to store the generated Report in a LOB into an underlying table in an Oracle Database.

At first you will need to download the BLOBDestination11g.jar (even when its compiled for Oracle Reports 11g, you can use it with Oracle Reports 12c) from My Oracle Support Note 11514155.1 https://support.oracle.com/epmos/faces/DocContentDisplay?id=1151455.1

Transfer the BLOBDestination11g.jar to your Server hosting your Oracle Forms & Reports 12c (e.g. /tmp) and copy it as BLOBDestination.jar to $ORACLE_HOME/reports/jlib

# Copy BLOBDestination11g.jar to $ORACLE_HOME/reports/jlib
cd /tmp
cp BLOBDestination11g.jar $ORACLE_HOME/reports/jlib/BLOBDestination.jar

As next we need to create a table which should receive our generated Reports PDF document.

# connect to your application schema and create following table
sqlplus test/Oracle12c@frrepo
SQL> create table executed_reports ( 
 ID            NUMBER NOT NULL
,CREATED_DATE TIMESTAMP DEFAULT SYSDATE
,CREATED_BY   VARCHAR2(20) DEFAULT USER
,REPORT_DOC   BLOB
,FILE_NAME    VARCHAR2(255));

The next part is depending if you are using an Oracle Reports In-Process Server or a Standalone Server.

Configuration for an In-Process Server

Connect to your Server which is hosting your Oracle Forms & Reports 12c environment and navigate to your $DOMAIN_HOME. There you will have to modify your eventually already existing setUserOverrides.sh or create a new setUserOverrides.sh in $DOMAIN_HOME/bin and modify the rwserver.conf configuration file for your In-Process Server

# add following block or 
# create a new setUserOverrides.sh with following block
# and align the Name of the Managed Server for Reports
# and align the ORACLE_HOME_PATH to your ORACLE_HOME

if [ "${SERVER_NAME}" = "<Name_ManagedServer_Reports>" ]; then
   export POST_CLASSPATH=<ORACLE_HOME_PATH>/reports/jlib/BLOBDestination.jar:$POST_CLASSPATH
fi

# e.g.

if [ "${SERVER_NAME}" = "MS_REPORTS" ]; then
   export POST_CLASSPATH=/u00/app/oracle/product/fmw-fr-12.2.1.2.0/reports/jlib/BLOBDestination.jar:$POST_CLASSPATH
fi

Then you will have to modify the rwserver.conf for your In-Process Server and add a new destination type. The rwserver.conf you can find under $DOMAIN_HOME/config/fmwconfig/components/<Name_ManagedServer_Reports>/applications/reports_12.2.1/configuration

# find the line with 
# <destination destype="WebDav" class="oracle.reports.plugin.destination.webdav.DesWebDAV"/>
# and add following line with the new destination type=BLOBDestination

<destination destype="WebDav" class="oracle.reports.plugin.destination.webdav.DesWebDAV"/>
<destination destype="BLOBDestination" class="oracle.reports.plugin.destination.blob.BLOBDestination"/>

Now restart your Managed Server for Reports and you are ready.

To test, just execute a Reports Call directly in your Browser:

http://<Your_Server>:<Port_of_ManagedServer_Reports>/reports/rwservlet?report=<Your_Test_Report>.rdf&userid=<App_User>/<Password>@<TNS_ALIAS>&DESTYPE=blobdestination&DESFORMAT=PDF&DESNAME=http://<App_User>:<Pasword>@<Your_Server>:<Listener_Port>/<ORACLE_SID>/<target_table>/<BLOB_Column>/<ID_Column>/<ID_Value>/<FileName_Column>

e.g.:

http://forms12:9002/reports/rwservlet?report=test.rdf&userid=test/Oracle12c@frrepo&DESTYPE=blobdestination&DESFORMAT=PDF&DESNAME=http://test:Oracle12c@forms12:1521/FRREPO/executed_reports/report_doc/ID/1/file_name

After that you will see in your target table for the Reports a row with your generated Reports PDF Document.

Configuration for a Standalone Reports Server

Connect to your Server which is hosting your Oracle Forms & Reports 12c environment and navigate to your $DOMAIN_HOME/reports/bin. There you will have to modify the reports.sh and modify the rwserver.conf configuration file for your In-Process Server

# Search for REPORTS_CLASSPATH in reports.sh
# and add the BLOBDestination.jar

REPORTS_CLASSPATH=${ORACLE_HOME}/reports/jlib/BLOBDestination.jar:${ORACLE_HOME}/reports/jlib/rwbuilder.jar:${ORACLE_HOME}/reports/jlib/rwrun.jar:${ORACLE_HOME}/jlib/zrclient.jar; export REPORTS_CLASSPATH

Then you will have to modify the rwserver.conf for your Standalone Reports Server and add a new destination type. The rwserver.conf you can find under $DOMAIN_HOME/config/fmwconfig/components/ReportsServerComponent/<Reports_Server_Name>

# find the line with 
# <destination destype="WebDav" class="oracle.reports.plugin.destination.webdav.DesWebDAV"/>
# and add following line with the new destination type=BLOBDestination

<destination destype="WebDav" class="oracle.reports.plugin.destination.webdav.DesWebDAV"/>
<destination destype="BLOBDestination" class="oracle.reports.plugin.destination.blob.BLOBDestination"/>

After that restart your Standalone Reports Server and thats all.

To test, just execute a Reports Call directly in your Browser:

http://<Your_Server>:<Port_of_ManagedServer_Reports>/reports/rwservlet?report=<Your_Test_Report>.rdf&userid=<App_User>/<Password>@<TNS_ALIAS>&DESTYPE=blobdestination&DESFORMAT=PDF&DESNAME=http://<App_User>:<Pasword>@<Your_Server>:<Listener_Port>/<ORACLE_SID>/<target_table>/<BLOB_Column>/<ID_Column>/<ID_Value>/<FileName_Column>

e.g.:

http://forms12:9002/reports/rwservlet?report=test.rdf&userid=test/Oracle12c@frrepo&DESTYPE=blobdestination&DESFORMAT=PDF&DESNAME=http://test:Oracle12c@forms12:1521/FRREPO/executed_reports/report_doc/ID/1/file_name

After that you will see in your target table for the Reports a row with your generated Reports PDF Document.


Wednesday, May 9, 2018

Tracking applied Patches in WebLogic Server outfile

With a small trick you can track your applied patches in your Oracle Software Home on your Oracle WebLogic Server in the outfile.

Simply add -Dweblogic.log.DisplayPatchInfo=true to your already existing setUserOverrides.sh or create a new setUserOverrides.sh in your $DOMAIN_HOME/bin directory.

 # Display applied patches in WebLogic Server outfile
JAVA_OPTIONS="$JAVA_OPTIONS -Dweblogic.log.DisplayPatchInfo=true "

After that just restart your WebLogic Server and you will find in the outfile of your WebLogic Server following entries:

 # Snippet from outfile
<May 9, 2018 9:15:30 AM CEST> <Info> <Management> <BEA-141107> <Version: WebLogic Server 12.2.1.3.0 Thu Aug 17 13:39:49 PDT 2017 1882952
OPatch Patches:
27342434;21933966;Thu Apr 26 16:14:03 CEST 2018;WLS PATCH SET UPDATE 12.2.1.3.180417
26355633;21447583;Thu Aug 31 14:26:20 CEST 2017;One-off
26287183;21447582;Thu Aug 31 14:26:10 CEST 2017;One-off
26261906;21344506;Thu Aug 31 14:25:53 CEST 2017;One-off
26051289;21455037;Thu Aug 31 14:25:48 CEST 2017;One-off>

The provided data in the outfile is in following format:

  1. Patch Number
  2. Unique Patch ID
  3. On which date and time the Patch was applied
  4. Patch description
To crosscheck just run an opatch lsinventory in order to validate the provided data in your outfile:

 cd $ORACLE_HOME/OPatch
./opatch lsinventory | grep applied

Patch  27342434     : applied on Thu Apr 26 16:14:03 CEST 2018
Patch  26355633     : applied on Thu Aug 31 14:26:20 CEST 2017
Patch  26287183     : applied on Thu Aug 31 14:26:10 CEST 2017
Patch  26261906     : applied on Thu Aug 31 14:25:53 CEST 2017
Patch  26051289     : applied on Thu Aug 31 14:25:48 CEST 2017

As you can see, the provided patch data in the outfile of your WebLogic Server is exactly the same as in the opatch utility.


Wednesday, March 21, 2018

Oracle SOA Suite 12c - Purge

Many people struggle with the configuration and execution of the SOA Purge functionality under Oracle SOA Suite 12c.

Oracle provided with SOA Suite 12c a nice web interface for enabling, and scheduling the AutoPurge functionality within the Enterprise Manager Fusion Middleware Control 12c.

But in case you just enable, schedule and define your retention time for the AutoPurge within this web interface nothing will be happen :-( You have to modify in addition some MBeans in order to enable and execute your AutoPurge Schedules correctly.

Simply login to your Enterprise Manager Fusion Middleware Control 12c (usually http://servername:/em), open the Target Navigation, navigate to "SOA/soa-infra" (in case you got a clustered environment, simply pick one of the available soa-infra, changes will be valid for all cluster members):


Under your soa-infra open the menu "SOA Infrastructure / SOA Administration / Auto Purge"


In the Auto Purge option you will have to define multiple sections.

  • Pick your "Auto Purge Job":
    • SOA Flow Purge Job 1
    • SOA Flow Purge Job 2
    • SOA In-Memory Flow Purge Job
    • Integration Workload Statistics Purge Job
    • Health Check Purge Job
  • Activate the "Enable" button
  • Define your "Job Schedule"
  • Set your "Retain Data"
  • Hit the Apply Button
  • Finally click the Link "More Auto Purge Configuration Properties...": this will bring you to the System MBean Browser to set the necessary options, so that your Auto Purge Job will really do something :-)


In the System MBean Browser you will be pointed directly to the Application Defined MBeans: AutoPurgeJobConfig:purge.
There you will find the Attribute "PurgeJobDetails", simply click it


Under the Attribute PurgeJobDetails you will see under the Key following Structure:

  • PurgeJobDetails
    • DELETE_INSTANCES_AUTO_JOB1
    • DELETE_INSTANCES_AUTO_JOB2
    • DELETE_INMEMORY_JOB1
    • DELETE_AWR_JOB1
    • DELETE_HC_JOB1
Open the DELETE_INSTANCES_AUTO_JOB1


Below the DELETE_INSTANCES_AUTO_JOB1 you will need to align the Key / Element pairs:

  • maxCreationPeriodDays, default value is -1, which means it's DISABLED !!!
  • minCreationPeriodDays, default value is -1, which means it's DISABLED !!!
  • purgePartitionedComponent, default is false, which means you don't have Partitions in your Metadata Repository tables


In my case, I have defined a retentionPeriod of 7 (under the Auto Purge Option, it's called Retain Data), so I have to set my maxCreationPeriodDays to retentionPeriod + 1 = 8 and my minCreationPeriodDays I define with 250. In case your SOA Metadata Repository was created with the profile LARGE in the Repository Creation Utility (rcu), you will have Partitions in several Metadata Repository tables, so you will need to align the value for purgePartitionedComponent from false to true.


Finally go up to the top of the current page and hit the button "Apply" to save your changes.


Now your Auto Purge Job will really purge data in your SOA Metadata Repository. Either wait to the next scheduled execution or execute the Auto Purge Job manually.


In order to check the executions of your Auto Purge Jobs, you can easily query the table SOA_PURGE_HISTORY under your SOAINFRA schema. Under the columns START_TIME and END_TIME you will see the start and end time of the Purge Job, under the column T you can see if your Job execution was single or parallel loop, under the column THREAD you will find the number of parallel threads and under the column S you will find the status of your Job execution, C = Completed, R = Running

 select * from SOA_PURGE_HISTORY order by 1;
    JOB_NO START_TIME                     END_TIME                       T     THREAD S
---------- ------------------------------ ------------------------------ - ---------- -
       442 14-MAR-18 12.00.03.227904 AM   14-MAR-18 12.00.03.677408 AM   S          0 C
       462 15-MAR-18 12.00.00.795402 AM   15-MAR-18 12.00.01.217016 AM   S          0 C
       482 16-MAR-18 12.00.03.573783 AM   16-MAR-18 12.00.04.036468 AM   S          0 C
       483 17-MAR-18 12.00.02.378165 AM   17-MAR-18 12.00.02.863812 AM   S          0 C
       503 18-MAR-18 12.00.02.381384 AM   18-MAR-18 12.00.02.862942 AM   S          0 C
       523 19-MAR-18 12.00.01.608355 AM   19-MAR-18 12.00.02.016223 AM   S          0 C
       524 20-MAR-18 12.00.03.018806 AM   20-MAR-18 12.00.03.347581 AM   S          0 C
       543 21-MAR-18 12.00.02.885658 AM   21-MAR-18 12.00.03.341806 AM   S          0 C
       544 21-MAR-18 01.12.56.213402 PM   21-MAR-18 01.12.58.535938 PM   S          0 C

Enjoy your SOA Purging :-)


Wednesday, March 14, 2018

Oracle 18c - SQLPlus cute Features

As I got my fingers on the latest Oracle 18c Release, I had the chance to test some cute new features within SQLPlus, which are really helpful.

SET LINESIZE WINDOW:

The new option WINDOW for SET LINESIZE automatically adjust your linesize to your current window size.

As you can see in the below screenshot, the first select was executed without the linesize option, so you will have the usual line break in your result set. Afterwards I execute "SET LINESIZE WINDOW", re-executed the select statement and you can see that the linesize is automatically adjusted to my current window size.


SET FEEDBACK ON SQL_ID:

This new option SQL_ID for SET FEEDBACK ON is my personal favorite. This option will provide you on the end of your executed SQL Statement the SQL_ID.

sqlplus test_new/Oracle18c@pdb01
SQL> set feedback on sql_id
SQL> select * from cat;

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
T1                             TABLE
V1                             VIEW

2 rows selected.

SQL_ID: adcm6b60qf64q

Enjoy the new 18c Features :-)

Friday, March 9, 2018

Oracle 18c - ALTER USER RENAME

2 years ago, I have published an article series about the undocumented feature "ALTER USER RENAME":

As I was getting access to an Oracle 18c Database, I was trying directly, if the ALTER USER RENAME still works => IT'S STILL WORKING :-)

Let's create a test user with one table and one view.
sqlplus sys/<password>@pdb01 as sysdba

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

SQL> create user test identified by "Oracle18c" default tablespace users quota unlimited on users;

User created.

SQL> grant connect, resource, create table, create view to test;

Grant succeeded.

SQL> connect test/Oracle18c@pdb01

SQL> create table t1 (id number, col1 varchar2(20));

Table created.

SQL> insert into t1 values (1,'Test 1');

1 row created.
    
SQL> insert into t1 values (2, 'Test 2');

1 row created.

SQL> commit;

Commit complete.

SQL&gt create view v1 as select * from t1;


Now let's rename the above created user TEST to TEST_NEW
sqlplus sys/<password>@pdb01 as sysdba

SQL> alter session set "_enable_rename_user"=true;

Session altered.

SQL> alter system enable restricted session;

System altered.

SQL> alter user test rename to test_new identified by "Oracle18c";

User altered.

SQL> alter system disable restricted session;

System altered.

Now let's try to connect with the renamed user TEST_NEW
sqlplus test_new/Oracle18c@pdb01 as sysdba

SQL> select * from t1;

 ID COL1
---------- --------------------
  1 Test 1
  2 Test 2

SQL> select * from v1;

 ID COL1
---------- --------------------
  1 Test 1
  2 Test 2
So even with Oracle18c the undocumented feature ALTER USER RENAME is still working :-)