Oracle Fusion Middleware & Application Server

Wednesday, September 21, 2016

Configure jobStatusRepository in Oracle Reports 12c

Within this blog post I will show you how to configure the Oracle Reports 12c jobStatusRepository against an Oracle Database.

Within Oracle Reports since release 11.x a major change is, that within the jobStatusRepository configuration, we have now to use an entry in the Credential Store Facility (CSF).

At first create in your target database for the Reports Server Queue a user:

create user rwadmin identified by "Oracle12c" default tablespace users quota unlimited on users;
grant create table to rwadmin;
grant create view to rwadmin;
grant create trigger to rwadmin;
grant create procedure to rwadmin;
grant create sequence to rwadmin;

After you have created your user for the Reports Server Queue, go to the $ORACLE_HOME/reports/admin/sql directory of your Oracle Forms & Reports Installation, open a sqlplus and connect to your target database with the above created user and execute the rw_server.sql. This will create all necessary and required objects for the Reports Server Queue.

SQL> connect rwadmin/Oracle12c@<your tnsnames_entry>
SQL> @rw_server.sql

As next we need to create an entry in the Credential Store Facilty. For this connect to your Enterprise Manager for Fusion Middleware http://server:port/em and select from the WebLogic Menu the Option "Security / Credentials"



Here we need to create a Map


Providing a Map Name, e.g. reports and confirm with OK


After creating the Map, you should see as following and we can now create a Key attached to our Map.


Provide following informations:

  • Select the Map, in this example "reports"
  • Leave Type with "Password"
  • Provide User Name = choose Oracle Database User from above
  • Prove the Password of the Oracle Database User 2 times
  • Optionally you can provide a description



After you have created the Key, you should see following:


The next step would be normally to invoke the System MBean Browser in order to configure your jobStatusRepository, but as there is a small "unexpected feature" (some people call it even a bug :-) ), I will go now directly into the configuration file of the underlying Reports Server and add the necessary elements.

Simply go to the Directory $DOMAIN_HOME/fr_domain/config/fmwconfig/components/ReportsServerComponent/<your_reports_server_name> and open the configuration file rwserver.conf with an editor.

In case you are using the System MBean Browser to configure the below settings, the System MBean Browser will not add the necessary class reference "oracle.reports.server.JobRepositoryDB" and will cause that you can not startup your Reports Server.

For the below configuration provide the following values:

  • dbpassword = csf:<Your_Map_Name>:<Your_Key_Name>
  • dbconn = <Your_DB_Servername>:<LISTENER_PORT>:<ORACLE_SID>
  • dbuser = <Your_DB_Username>

   <jobStatusRepository class="oracle.reports.server.JobRepositoryDB">
      <property name="dbpassword" value="csf:reports:reports_repository"/>
      <property name="dbconn" value="forms12:1521:FRREPO"/>
      <property name="dbuser" value="rwadmin"/>
   </jobStatusRepository/>

Finally restart your Reports Server

cd $DOMAIN_HOME/bin
./stopComponent.sh <Your_Reports_Server_Name>
./startComponent.sh <Your_Reports_Server_Name>

Now you can generate a Report and you should be able to see the executed Reports in your Reports Server Queue in the Database





Wednesday, September 14, 2016

Microsoft SQL Server under Linux - Preview

Today something off topic, as I am normally blogging about Oracle Fusion Middleware stuff.

Microsoft SQL Server under Linux - Preview ... many of you (specially the Oracle Guys) might think now "WHAT ????", but its really true.
Some months ago Microsoft announced that they will release a SQL Server Version for Linux and I was lucky to get into the Preview Program.

The installation of the SQL Server under Linux (currently supported under Ubuntu 16 or RedHat 7) is quite easy and its completed in around 15 minutes (depending of your download speed).

What is quite cool, is that the SQL Server can also be installed under Docker :-)

It's really SQL Server under Linux :-)


As a native UNIX User I recognised one small thing, which were disturbing me, when you want to restore a Database Backup you have to use Windows Path notations which will be internally mapped to UNIX Path notations, but I am pretty sure that within the next Releases or at least with the final Release this small problem will be adjusted.



From the first look, SQL Server on Linux looks quite interesting.

Tuesday, August 30, 2016

Integrating Critical Patch Updates (CPU) into your WebLogic Server on Docker

Bruno Borges is doing a great job providing Docker Images for various Oracle WebLogic scenarios within the GitHub Repository https://github.com/oracle/docker-images/tree/master/OracleWebLogic

The provided samples are a real good starting point to create your Oracle WebLogic Servers and Domains including sample application.

But I personally miss one point within the Installation process of the Oracle WebLogic Server, applying Critical Patch Updates.

The integration is really simple.
As an example I will take Bruno's build file https://github.com/oracle/docker-images/blob/master/OracleWebLogic/dockerfiles/12.2.1/Dockerfile.generic for the installation of the WebLogic Server 12.2.1 Generic Version and extend the Dockerfile with an apply of the Critical Patch Update July 2016 for Oracle WebLogic Server 12.2.1.0.0 (Patch No. 23094285).

As pre requirement download all the mentioned Software Files within Bruno's GitHub Repository and in addition download from My Oracle Support the Patch No. 23094285 (file name: p23094285_122100_Generic.zip) and place them into the dockerfiles/12.2.1 directory on your server.

As next modify the Dockerfile.generic as follows, see line no. 5 - Environment for CPUJuly2016 Patch Number


# Environment variables required for this build (do NOT change)
# -------------------------------------------------------------
ENV FMW_PKG=fmw_12.2.1.0.0_wls_Disk1_1of1.zip \
    FMW_JAR=fmw_12.2.1.0.0_wls.jar \
    CPUJULY2016=p23094285_122100_Generic.zip \
    ORACLE_HOME=/u01/oracle \
    USER_MEM_ARGS="-Djava.security.egd=file:/dev/./urandom" \
    PATH=$PATH:/usr/java/default/bin:/u01/oracle/oracle_common/common/bin

see line no. 4 - for copy of CPUJuly2016 file
# Copy packages
# -------------
COPY $FMW_PKG install.file oraInst.loc /u01/
COPY $CPUJULY2016 /u01/

see lines no. 8, 9 and 14 for the adjusting of the chown command. see lines no 12 and 13 for the extraction and apply of the CPUJuly2016 Patch see line no. 15 for the cleanup of the CPUJuly2016 file
# Setup filesystem and oracle user
# Install and configure Oracle JDK
# Adjust file permissions, go to /u01 as user 'oracle' to proceed with WLS installation
# ------------------------------------------------------------
RUN chmod a+xr /u01 && \
    useradd -b /u01 -m -s /bin/bash oracle && \
    echo oracle:oracle | chpasswd && \
    # Move chown command to an upper position in order to avoid permission problems while applying CPU patch
    chown oracle:oracle -R /u01 && \
    cd /u01 && $JAVA_HOME/bin/jar xf /u01/$FMW_PKG && cd - && \
    su -c "$JAVA_HOME/bin/java -jar /u01/$FMW_JAR -silent -responseFile /u01/install.file -invPtrLoc /u01/oraInst.loc -jreLoc $JAVA_HOME -ignoreSysPrereqs -force -novalidation ORACLE_HOME=$ORACLE_HOME INSTALL_TYPE=\"WebLogic Server\"" - oracle && \
    su -c "cd /u01 && /u01/oracle/oracle_common/adr/unzip $CPUJULY2016" - oracle && \
    su -c "cd /u01/23094285 && /u01/oracle/OPatch/opatch apply -silent" - oracle && \
    # chown oracle:oracle -R /u01 && \
    rm /u01/$FMW_JAR /u01/$FMW_PKG /u01/$CPUJULY2016 /u01/oraInst.loc /u01/install.file

Before you start with the build process, make sure that you have all mentioned pre requirements from Bruno, e.g. the oracle/jdk:8 image.

[root@server] docker images
REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
oracle/jdk          8                   1fe717b7315d        15 minutes ago      520.1 MB

The next step will be to create your Oracle WebLogic Server Image, which now will include the current available Critical Patch Update, you should see during your build process following output:


Sending build context to Docker daemon 882.4 MB
Step 1 : FROM oracle/jdk:8
 ---> 1fe717b7315d
. . .
. . .
Oracle Interim Patch Installer version 13.3.0.0.0
Copyright (c) 2016, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/oracle
Central Inventory : /u01/oracle/.inventory
   from           : /u01/oracle/oraInst.loc
OPatch version    : 13.3.0.0.0
OUI version       : 13.3.0.0.0
Log file location : /u01/oracle/cfgtoollogs/opatch/23094285_Aug_30_2016_11_04_46/apply2016-08-30_11-04-43AM_1.log

OPatch detects the Middleware Home as "/u01/oracle"

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   23094285  

Do you want to proceed? [y|n]
Y (auto-answered by -silent)
User Responded with: Y
All checks passed.
. . . 
. . .
OPatch succeeded.
. . .

And now you got an Oracle WebLogic Server Image including the latest available Critical Patch Update :-)
But keep in mind, for future CPU's you might also include before the opatch apply step an update of the opatch utility, as from time to time Oracle requires a newer version of the opatch utility.


Monday, August 29, 2016

Oracle Forms Stand-alone Application Launcher (FSAL)

Since Oracle Forms 12c its possible to start your Oracle Forms without invoking a browser, this is called Oracle Forms Stand-alone Application Launcher (FSAL).

As pre requirement you will need either a JRE or JDK on your desktop, which should execute the Oracle Forms.

For this new Feature Oracle has integrated a short description Website within your installed and configured Oracle Forms & Reports Server 12c. The description website can be reached under following URL http://<your_server>:<port>/forms/html/fsal.html



Simply access this website from your desktop and download the frmsal.jar file to your desktop.

On your server hosting the Oracle Forms & Reports Server, access the formsweb.cfg and search for the section [standalone] or create a new section with a unique name.

[oracle@server] cd $DOMAIN_HOME/config/fmwconfig/servers/WLS_FORMS/applications/formsapp_12.2.1/config
[oracle@server] vi formsweb.cfg
-- Snippet from formsweb.cfg
[standaloneapp]
# Note: baseSAAfile must end with .txt
baseSAAfile=basesaa.txt
# definition of colorScheme
colorScheme=blaf
# My Start Form
form=t1.fmx
# fsalcheck parameter specifies whether to perform checksum comparison
# of Forms stand-alone app launcher or not. When it is enabled, it triggers
# the comparison at server. The checksum of FSAL at client machine will be
# compared with the checksum of FSAL archived at the server repository.
fsalcheck=true

After you have modified or created your own section for the FSAL, get back to your desktop, open a command prompt and navigate to the directory in which you have placed the frmsal.jar file. From there make sure that you have the java binary in your PATH and execute following command

java -jar frmsal.jar -url "http://<your_server>:<your_port>/forms/frmservlet?config=standaloneapp" -t 10000

You can also define within the parameter -url various options, e.g. .../forms/frmservlet?config=standaloneapp&colorScheme=teal

Depending on your formsweb.cfg section configuration you might see the typical login dialog.



or you will access directly your Oracle Forms Application.



And everything without any browser :-)

Monday, August 15, 2016

Oracle Reports 12c - The missing configuration steps

Oracle Forms & Reports 12c is released since a quite long time and I had now some chance to have a more detailed look on the Oracle Reports 12c component.

After you have successfully installed and configured the Oracle Forms & Reports 12c, you might find out that your Oracle Reports 12c is not working ... because its not yet configured, even you completed the Configuration Assistent successfully :-)

The following steps are describing the necessary tasks for Linux.

There some steps to take as "Post-Configuration":

  1. Create a Reports Tool Instance
  2. Create a Reports Server Instance
  3. if you are running under Linux 7 (either Oracle Enterprise Linux or RedHat) fix libXm.so.3 issue
  4. Adjust the Application Roles within your WebLogic Server Domain

Create a Reports Tool Instance

connect to your Linux server as oracle user and create the necessary Reports Tool Instance via WLST

[oracle@server] cd $ORACLE_HOME/oracle_common/common/bin
[oracle@server] ./wlst.sh
# replace the password with your password and verify if port 7001 is your Admin Server Port
wls:/offline> connect('weblogic','password','localhost:7001')
wls:/fr_domain/serverConfig/> createReportsToolsInstance(instanceName='reptools1',machine='AdminServerMachine')
wls:/fr_domain/serverConfig/> exit()

The above execution of the createReportsToolsInstance command will create under your DOMAIN_HOME following subdirectories:

  • reports/bin
  • reports/cache
  • reports/fonts
  • reports/plugins
  • reports/server
as next step we can create our Reports Server.

Create a Reports Server Instance

connect to your Linux server as oracle user and create the necessary Reports Server Instance via WLST

[oracle@server] cd $ORACLE_HOME/oracle_common/common/bin
[oracle@server] ./wlst.sh
# replace the password with your password and verify if port 7001 is your Admin Server Port
wls:/offline> connect('weblogic','password','localhost:7001')
wls:/fr_domain/serverConfig/> createReportsServerInstance(instanceName='rep_server1',machine='AdminServerMachine')
wls:/fr_domain/serverConfig/> exit()



libXm.so.3 Problem

in case you are using an Oracle Enterprise Linux 7 or RedHat 7, you need to fix a small libXm.so.3 problem. Oracle Reports is looking for the libXm.so.3, but under OEL 7 and RH7 you will find only a libXm.so.4
Simply perform following steps as root user on your Linux server

[root@server] cd /usr/lib64
[root@server] ls libXm.so*
libXm.so.4  libXm.so.4.0.4
[root@server] ln -s /usr/lib64/libXm.so.4 libXm.so.3

Startup your Reports Server Instance

since we have created the Reports Server Instance by attaching to the NodeManager, you must make sure that the NodeManager is up and running and than perform following steps:

[oracle@server] cd $DOMAIN_HOME/bin
[oracle@server] ./startComponent.sh rep_server1
. . .
Successfully Connected to Node Manager.
Starting server rep_server1 ...
Successfully started server rep_server1 ...
Successfully disconnected from Node Manager.

Exiting WebLogic Scripting Tool.

Done

Adjust the Application Roles within your WebLogic Server Domain

At first you need to login to the Fusion Middleware Enterprise Manager, normally http://<your_server_name>:7001/em and access under the menu "Security / Application Roles"


Within the Application Roles option chose from the drop down menu the option reports, than click on the arrow button, mark the line with the Role Name RW_ADMINISTRATOR and finally click the Edit button to modify the selected role



Under "Edit Application Role : RW_ADMINISTRATOR" use the Add button


In the Add Principal screen, select under the option Type "User", click the arrow button and mark the line with the Principal "weblogic" and proceed with the button OK



The last OK button will redirect you to the Application Roles screen, there simply click the OK button on the top right corner, when everything works fine you will get following Information


Now, you are able to access the Reports Status URLs by providing the username and password of the weblogic user

For Example
  • http://<your_server_name>:9002/reports/rwservlet/showenv?server=rep_server1
  • http://<your_server_name>:9002/reports/rwservlet/getserverinfo?server=rep_server1





Wednesday, June 22, 2016

A quick look on WLSDM for WebLogic Server

WLSDM (WL Smart Dashboard & Monitoring) was already mentioned by some people within the Oracle WebLogic Community.

The WLSDM tool is a Console extension with which you can monitor your Oracle WebLogic Server environment. But its not only a pure monitoring tool, its more than this, it provides on top even Notifications and Alarms for metrics.

Current release for WLSDM is 2.3.1 which also comes with Oracle SOA Support :-)

The installation is really straight forward as described in the installation document from the WLSDM, but the given 1 minute deploy time is a bit optimistic :-) its more 5 minutes, but this is still impressive how quick you can setup WLSDM :-) Just follow the instructions for the installation, after you start the WLSDM Dashboard, the configuration assistant will guide you through the necessary configuration and your Monitoring and Notification/Alarm System is ready to use.

WLSDM comes is real Dashboard Style with provides you several views on your WebLogic Server environment.

General Health Overview Dashboard:



JVM Resources Dashboard:


You want to know what SQL Statements are executed from your deployed applications and what was the execution time? Have a look under the section Monitoring & Diagnostics - Back-end Systems.

Either as Chart View:


Or in a list view:


And you can see even more details by clicking the lense symbol:



Under the Section Operational Tools & Utils you can find various helpful stuff like Log File Viewer, Thread Dump Analyzer, MBean Search, Decrypt -Encrypt and so on. My personal favourite is the WLST Web Console, here you can execute directly from your web browser WLST commands or execute WLST Python Scripts.



WLSDM is really a cool Monitoring Dashboard, which is worth to have a look ... :-)




WebLogic Server 12.2.1.1.0 available

Since 21st June 2016 the latest release of Oracle WebLogic Server 12.2.1.1.0 is available for download under Oracle Technology Network.



Under the "What's New in Oracle WebLogic Server 12.2.1.1.0" document you can find all new features for the latest release of Oracle WebLogic Server 12.2.1.1.0.
In general the Release 12.2.1.1.0 is the first patch set for Oracle WebLogic Server 12.2.1.

The main enhancements were done within the Multitenancy area, but also the Domain to Partition Conversion Tool (D-PCT) is now included in the Oracle WebLogic Server 12.2.1.1.0 distribution and doesn't need to be downloaded separately. And as well the latest Oracle WebLogic Server 12.2.1.1.0 is certified to be used on Docker :-)

Wednesday, May 18, 2016

Audit Activities of WebLogic Server in Enterprise Manager 13c

In Oracle Enterprise Manager 13c were introduced a nice new feature for auditing activities of Oracle WebLogic Server events.

Before you can audit your activities like Domain login, Domain logout and Domain updates, you have to enable them via a simple emcli command.

Logon to your server which is hosting your Oracle Management Server (OMS) and perform following steps:

[oracle@em13c]# emcli login -username=sysman
Enter Password:
Login successful
[oracle@em13c]# emcli update_audit_settings \
-operations_to_enable="WEBLOGIC_DOMAIN_UPDATE_INVOKE;WEBLOGIC_DOMAIN_LOGIN;WEBLOGIC_DOMAIN_LOGOUT"
Successfully updated the audit settings.

From now on all Domain login, logout and updates events will be monitored and can be inspected within Oracle Enterprise Manager 13c.

The Audit Data can be found under "Setup / Security / Audit Data"



Within the Audit Data you will find 3 main sections:

  • Search Section were you can define your searches
  • A list view of Audit Records according to your defined search
  • Audit Record Details for your marked Audit Record of the list view


For Audit Records concerning Oracle WebLogic Domain activities, you should redefine your search under the search field "Operation", so that you select only the 3 available Operations related to WebLogic Domain and afterwards click the button "Search"



Now you will see in your List View in the center just the Audit Records related to Oracle WebLogic Domain targets. When you select a Audit Record in the List View you will see below the Audit Record Details for the selected Audit Record. Here you can see different tabs:
  • General: Timestamp, Administrator, Operation Type, Status and so on
  • Client Information: Contains different client informations like browser, client IP and Session ID
  • OMS Information: Hostname and IP of the Oracle Management Server (OMS)
  • Operation Specific Information: Contains detailed informations about the underlying activity.



The Audit Record Details tab "Operation Specific Information" is quite interesting, as we can see here very detailed informations about the performed activity.
You will find here informations about the Object Name, Target Name, Target Type, Operations Type, Message, which contains in case of an update operations the MBean, Processing Time of the Operations and possible Input Parameters. In the "Input Parameters" you will find your modified values for the underlying operation, so you can track down what has been changed exactly. Really nice :-) but one thing you should consider, the Input Parameters will be displayed in plain text, even if you are changing the password within a Data Source, you can see for the corresponding Audit Record Detail in the field Input Parameters the value of your password :-( not really cool ....



But nevertheless, the Audit Data Feature for Oracle WebLogic Server is really cool :-)

Thursday, March 24, 2016

ALTER USER RENAME - Part 3

This is part 3 of the ALTER USER RENAME series


Inspired by a Twitter Comment from Franck Pachot and as I love to test really funny and strange things with Oracle Products.



I was trying to rename the SYS user and the SYSTEM user.

SQL> alter session set "_enable_rename_user"=true;
SQL> alter system enable restricted session;
SQL> alter user sys rename to mysys identified by "Oracle12c";
alter user sys rename to mysys identified by "Oracle12c"
           *
ERROR at line 1:
ORA-42289: may not rename specified user

The same applies for the SYSTEM user


Wednesday, March 23, 2016

ALTER USER RENAME - Part 2

It seems that my first blog post today "ALTER USER RENAME - A half official option" got some great attentions :-)

How did I come to the ALTER USER RENAME command? Just a lucky punch due to my customer. My customer were trying to rename an user in an Oracle Database with several objects (tables, view, functions, types and so) by simply updating the sys.user$ table :-)
The result were better than expected, just synonyms had to be recreated, but a function which is referencing a type caused some problems thats leads to an undocumented ORA-00600.

So I was starting to do some searches in My Oracle Support and finally came across the My Oracle Support Note 10217802.8



Its simply a Bug summing up for a discovered ORA-4030 errors message for the command "ALTER USER .. RENAME". Quick check in the official SQL Language Reference Guides, shows clearly there is no "ALTER USER RENAME" option. So whats going on here? Next search in My Oracle Support with simply the search string "10217802" brings up some Patches for the Bug


Patches for a non-existing feature sounds really interesting :-) when you check out the patches, you will see that the patches were created 10th November 2010 !!!!

So since more than 5 years the really cool feature is existing, but nowhere really described.

What I discover so far:


  • Users which are holding TABLES, VIEWS, FUNCTIONS, PROCEDURES can be renamed without any problems
  • Even GRANTS will be transferred correctly :-)
  • Problems I discovered so far with TYPES and SYNONYMS
    • SYNONYM owner will be switched correctly, but the table_owner stays untouched, so the synonym will point to an invalid reference. Not nice, but its ok

Whats underneath the ALTER USER RENAME command?

The easiest way is just set trace event 10046 :-)

alter session set tracefile_identifier='RENAME';
alter session set events '10046 trace name context forever,level 12';
alter session set "_enable_rename_user"=true;
alter system enable restricted session;
alter user DEMO rename to DEMO_NEW identified by "demo";
alter session set events '10046 trace name context off';
alter system disable restricted session;

In generell the ALTER USER RENAME is doing some modifications (update, delete and insert) against the sys.user$ table and other sys tables, here some examples from the 10046 tracefile:

update user$ set ext_username=:1
where ext_username = (select name from user$ where user#=:2)

delete from user$
where user#=:1

insert into user$(user#,name,password,ctime,ptime,datats#,tempts#,type#,
  defrole,resource$,ltime,exptime,astatus,lcount,defschclass,spare1,spare4,
  ext_username,spare2)
values
 (:1,:2,:3,SYSDATE,DECODE(to_char(:4, 'YYYY-MM-DD'), '0000-00-00',
  to_date(NULL), :4),:5,:6,:7,:8,:9,DECODE(to_char(:10, 'YYYY-MM-DD'),
  '0000-00-00', to_date(NULL), :10),DECODE(to_char(:11, 'YYYY-MM-DD'),
  '0000-00-00', to_date(NULL), :11),:12,:13,:14,:15,:16,:17,:18)

update user$ set user#=:1,password=:3,datats#=:4,tempts#=:5,type#=:6,defrole=
  :7,resource$=:8,ptime=DECODE(to_char(:9, 'YYYY-MM-DD'), '0000-00-00',
  to_date(NULL), :9),defschclass=:10, spare1=:11, spare4=:12
where name=:2

update user$ set exptime=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00',
  to_date(NULL), :2),ltime=DECODE(to_char(:3, 'YYYY-MM-DD'), '0000-00-00',
  to_date(NULL), :3),astatus = :4, lcount = :5
where user#=:1

And what Oracle says?
Official reply from My Oracle Support:

The fact that a certain fix for an undocumented feature is included in a bundle patch such a PSU does not make it official at all. The only official and supported features are those present in the documentation.

Lets see, if some day this cool feature will become official  ... :-)

ALTER USER RENAME series:




ALTER USER RENAME - A half official option

Many times DBA's have to rename / copy complete users/schemas from the current name to a new name within one database. Since several years DBA's are asking for a simple method inside Oracle to execute something like "ALTER USER RENAME".

Common approaches to rename users/schemas are:
  • IMPDP with REMAP_SCHEMA
  • update of sys.user$
For the option "update of sys.user$", which is in my opinion a really bad choice, see Tom Kyte's comment 



For the option "IMPD with REMAP_SCHEMA", its official but can be time consuming.

It would be really cool to have a simple SQL Statement like "ALTER USER RENAME". In the official Oracle Documentation "Database SQL Language Reference" under ALTER USER is nothing documented for a RENAME option, but it exists !!!! :-)

How does it work?

Let's say we have a user called DEMO and we want to rename this user to DEMO_NEW. The user DEMO got tables, views and functions.

# Connect as SYS to your database
conn / as sysdba
# At first we set an undocumented parameter to enable the RENAME option
alter session set "_enable_rename_user"=true;
# Bring the Database to restricted session, in order to avoid Memory Problems for huge schema
alter system enable restricted session;
# Now lets RENAME the user DEMO to DEMO_NEW
# and provide a password for the new user DEMO_NEW
alter user DEMO rename to DEMO_NEW identified by "demo";
# Disable restricted session
alter system disable restricted session;

That's it :-)

The ALTER USER RENAME works from Oracle 11.2.0.2.0 going, my above test case was done with Oracle 12.1.0.2.0.

So why is this option with ALTER USER RENAME "half official"?

  • It's not documented in the official Oracle Documentation
  • It requires an undocumented parameter
  • But there are Patches available for the ALTER USER RENAME within My Oracle Support
    • For Example: Patch 10217802 which fixes an ORA-4030 for ALTER USER RENAME

The question now is why Oracle doesn't make this cool feature official?

So far, I found one limitation, if the user holds a type which is referenced within a table you will get following error for the ALTER USER RENAME:

ORA-42287: cannot rename user on whose type a table depends

If anybody finds more limitation while testing this cool feature, just drop me message here.

But remember, DON'T DO THIS IN PRODUCTION !! Its not an official option, there might be some serious kickback effects.


ALTER USER RENAME series:



Tuesday, February 9, 2016

CPUJan2016 - WebLogic Server 12.1.2.0.8 OPatch apply error

Since 19th January 2016 the Critical Patch Update for January 2016 is out.
In case you are trying to apply the CPUJan2016 against an Oracle WebLogic Server 12.1.2 you may run into following error:

Verifying the update...
[ Error during Update inventory for apply Phase]. Detail: 
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 logfile for details.
[ Error during Update inventory for apply Phase]. Detail: OPatch failed:
ApplySession failed in system modification phase... 
Verification of patch failed: Files are not updated completely.

The problem occurs under following conditions:

  • OPatch 13.1.0.0.0
  • JDK 1.7
  • CPU Patch Number: 21984577
The solution is relatively simple :-) use instead of opatch apply the command opatch napply.

Happy Patching ...


Saturday, December 19, 2015

Enterprise Manager 13c Presentations on Oracle Technology Network

As everybody already know that the latest version of Oracle Enterprise Manager 13c (13.1.0.0) has been released yesterday (see also my post from 18th December 2015), Oracle has uploaded several interesting presentations around the new Oracle Enterprise Manager 13c.

The presentations are covering various topics around the Oracle Enterprise Manager 13c (some presentations are also still covering Enterprise Manager 12c):

  • General
  • Cloud Management
  • Middleware Management
  • Database Management
  • Installation & Upgrade | Best Practices
  • and so on ...


The presentations can be found under: http://www.oracle.com/technetwork/oem/downloads/em-oow2015-2789798.html





Friday, December 18, 2015

Oracle Enterprise Manager 13c available

Seems its early Christmas :-) the new release of the Oracle Enterprise Manager 13c (13.1.0.0) is available for download in the Oracle Technology Network http://www.oracle.com/technetwork/oem/enterprise-manager/downloads/index.html



The new Oracle Enterprise Manager 13c is available for:


  • Linux x86-64
  • Windows x86-64
  • Solaris (SPARC)
  • Solaris (x86-64)
  • IBM AIX on POWER Systems
  • HP-UX Itanium


Happy download :-)

Oracle Enterprise Manager 13c - First Video on Oracle Learning Library YouTube Channel

I just discovered that since today (18th December 2015) is a first Oracle Learning Library video for the Oracle Enterprise Manager 13c (!!!!!) available.




Enjoy watching it :-)

Friday, December 11, 2015

WebLogic 12.2.1 - Redeployment of versioned applications via RESTful Management Services

With the latest release of Oracle WebLogic Server 12.2.1 came quite a lot of enhancements for the RESTful Management Services. The enhancements are really impressive, you are able to monitor (GET) or to modify (POST) your WebLogic environment in detail with a simple URL call either via Browser or via Command Line Tools like cURL. The response time for such REST calls are really impressive compared to the classical way with WLST scripts :-)

For more details about RESTful Management Services you may have a look into the Official Oracle Documentation: http://docs.oracle.com/middleware/1221/wls/WLRUR/index.html

Within this post, I will show how to deploy a versioned application via a simple REST call over cURL. As base for this example you should have a look on the Oracle Learning Library Example "Oracle WebLogic Server 12c (12.2.1): Configure and Using Production Redeployment". Within this Oracle Learning Library Example you can see the classical way of deploying a versioned application. The below used versioned application is coming from this Oracle Learning Library Example, the versioned applications you can download from the before mentioned URL.

Since Oracle WebLogic Server 12.2.1 you will have a lot more possibilities within the RESTful Management Services, for example: creating Managed Servers, creating Data Sources, modifying WebLogic Server settings, deploying applications and so on.

As preparation I have downloaded the versioned.zip file from the Oracle Learning Library example, transferred it to my server on which my WebLogic Server 12.2.1 is running.

First step unzip the versioned.zip file to any directory of your server, make sure that your Oracle WebLogic Server is up and running and specially that the Managed Server to which you want to deploy the versioned application is up and running.

Second step, I simply created two shell scripts to deploy my 2 versioned applications.

Replace within the below provided script for the first deployment just the password for the weblogic user, in my case Oracle12. Align the provided deploymentPath to your path, align the provided Managed Server Name under targets and align the hostname and AdminServer port provided in the last line of the below script:

Script name: deploy_app_v1.sh

#!/bin/ksh
# Script for deployment of version 1
curl -v --user weblogic:Oracle12c \
     -H X-Requested-By:MyClient \
     -H Accept:application/json \
     -H Content-Type:application/json \
     -d "{
          name: 'simple',
          deploymentPath: '/home/oracle/versioned_app/deployversion1/simple.war',
          targets: [ManagedServer1]
         }" \
     -X POST http://wls1221:7001/management/wls/latest/deployments/application

Replace within the below provided script for the second deployment just the password for the weblogic user, in my case Oracle12. Align the provided deploymentPath to your path, align the provided Managed Server Name under targets and align the hostname and AdminServer port provided in the last line of the below script:

Script name: deploy_app_v2.sh

#!/bin/ksh
# Script for deployment of version 2
curl -v --user weblogic:Oracle12c \
     -H X-Requested-By:MyClient \
     -H Accept:application/json \
     -H Content-Type:application/json \
     -d "{
          name: 'simple',
          deploymentPath: '/home/oracle/versioned_app/deployversion2/simple.war',
          targets: [ManagedServer1]
         }" \
     -X POST http://wls1221:7001/management/wls/latest/deployments/application


Now lets start with the first deployment:
[oracle@wls1221]# ./deploy_app_v1.sh

* About to connect() to wls1221 port 7001 (#0)
*   Trying 192.168.56.10...
* Connected to wls1221 (192.168.56.10) port 7001 (#0)
* Server auth using Basic with user 'weblogic'
> POST /management/wls/latest/deployments/application HTTP/1.1
> Authorization: Basic d2VibG9naWM6T3JhY2xlMTJj
> User-Agent: curl/7.29.0
> Host: wls1221:7001
> X-Requested-By:MyClient
> Accept:application/json
> Content-Type:application/json
> Content-Length: 156
> 
* upload completely sent off: 156 out of 156 bytes
< HTTP/1.1 201 Created
< Date: Fri, 11 Dec 2015 10:37:07 GMT
< Location: http://wls1221:7001/management/wls/latest/deployments/application/id/simple
< Content-Length: 794
< Content-Type: application/json
< X-ORACLE-DMS-ECID: 0681dbbd-5c8c-47c3-bf72-2c795675d5b4-0000001a
< X-ORACLE-DMS-RID: 0
< Set-Cookie: JSESSIONID=XMaQnZ6WaiRtdYIelakH-cFG0PHNKdWdp84MMS5ryxHr1xWLUGc6!-693988513; path=/; HttpOnly
< 
{
    "item": {
        "targets": [{
            "status": "completed",
            "errors": [],
            "name": "ManagedServer1",
            "type": "server"
        }],
        "beginTime": 1449830228082,
        "endTime": 1449830234153,
        "status": "completed",
        "deploymentName": "simple",
        "description": "[Deployer:149026]deploy application simple [Version=v1] on ManagedServer1.",
        "operation": "deploy",
        "name": "ADTR-0",
        "id": "0",
        "type": "deployment"
    },
    "messages": [{
        "message": "Deployed the application 'simple'.",
        "severity": "SUCCESS"
    }],
    "links": [{
        "rel": "job",
        "uri": "http:\/\/wls1221:7001\/management\/wls\/latest\/jobs\/deployment\/id\/0"
    }]
* Connection #0 to host wls1221 left intact


Now lets open a browser and access the newly deployed application, in my case http://wls1221:7003/simple (as my target Managed Server is listening on port 7003). You should see a simple website in a blue color scheme. Type in your First Name, Last Name and Favorite Color and press "OK"


Keep the browser open and now we perform the Production Redeployment of the version 2 of our application via RESTful Management Services.


[oracle@wls1221]# ./deploy_app_v2.sh

* About to connect() to wls1221 port 7001 (#0)
*   Trying 192.168.56.10...
* Connected to wls1221 (192.168.56.10) port 7001 (#0)
* Server auth using Basic with user 'weblogic'
> POST /management/wls/latest/deployments/application HTTP/1.1
> Authorization: Basic d2VibG9naWM6T3JhY2xlMTJj
> User-Agent: curl/7.29.0
> Host: wls1221:7001
> X-Requested-By:MyClient
> Accept:application/json
> Content-Type:application/json
> Content-Length: 156
> 
* upload completely sent off: 156 out of 156 bytes
< HTTP/1.1 201 Created
< Date: Fri, 11 Dec 2015 10:37:56 GMT
< Location: http://wls1221:7001/management/wls/latest/deployments/application/id/simple
< Content-Length: 794
< Content-Type: application/json
< X-ORACLE-DMS-ECID: 0681dbbd-5c8c-47c3-bf72-2c795675d5b4-0000001c
< X-ORACLE-DMS-RID: 0
< Set-Cookie: JSESSIONID=2fCQnl74wd1Ps9WDNmfv7eTjnh3yyGnNV6eyYOnbXJHMPO1IQmDn!-693988513; path=/; HttpOnly
< 
{
    "item": {
        "targets": [{
            "status": "completed",
            "errors": [],
            "name": "ManagedServer1",
            "type": "server"
        }],
        "beginTime": 1449830276956,
        "endTime": 1449830282690,
        "status": "completed",
        "deploymentName": "simple",
        "description": "[Deployer:149026]deploy application simple [Version=v2] on ManagedServer1.",
        "operation": "deploy",
        "name": "ADTR-1",
        "id": "1",
        "type": "deployment"
    },
    "messages": [{
        "message": "Deployed the application 'simple'.",
        "severity": "SUCCESS"
    }],
    "links": [{
        "rel": "job",
        "uri": "http:\/\/wls1221:7001\/management\/wls\/latest\/jobs\/deployment\/id\/1"
    }]
* Connection #0 to host wls1221 left intact


Now open a second browser and access once again the URL of the application http://wls1221:7003/simple and now you should see a website with a green color scheme, which is the second version of the application. Type in once again your data and click "OK"



If you now go back to your first browser (with the blue color scheme), click now the link "Start over ..." and you should be able still to use the version 1 of the application as your HTTP Session object is still valid.


When we look now into the Oracle WebLogic Server Console under "Deployments" you should see the 2 versioned applications as following:


So, Production Redeployment of versioned applications via the new enhancements of the RESTful Management Services is possible and really simple.

RESTful Management is the future within Oracle Products ... see for example the latest release of Oracle Golden Gate, even within this product you have now a RESTful API connectivity for monitoring and diagnosing ... http://docs.oracle.com/goldengate/c1221/gg-winux/GRLWU/toc.htm#GRLWU102

These metrics are recorded in a RESTful API for use in diagnosing issues by Oracle GoldenGate Support and Development. These metrics can be used to improve CPU efficiency thus reducing load on source and target databases.


My advice / recommendation:
 have a closer look to the new enhancements of the RESTful Management Services, you have a whole new world of possibilities to refactoring your old monitoring and deployment script based on WLST, even start / shutdown / suspend / resume operations against your WebLogic Servers are possible with the new enhancements of RESTful Management Services.

Thursday, December 10, 2015

WebLogic 12.2.1 - New Feature for Data Source System Properties

Within the new release of Oracle WebLogic Server 12.2.1 is a really cool new feature for Data Source System Properties.

In case you have mutliple data sources defined within your Oracle WebLogic Server pointing to the same Oracle Database and mostly using the same Oracle Database user, you have currently limited chances to identify in your v$session, which session belongs to which WebLogic Server Data Source.

For example, you have 2 Data Sources defined in your WebLogic Server, each Data Source is assigned to different Managed Servers within your WebLogic Domain and using the same Oracle User, so you normally see following:

select username, osuser, process, machine, terminal, program
from v$session where username = 'WLS_REPOS';
USERNAME    OSUSER    PROCESS    MACHINE       TERMINAL   PROGRAM
---------   --------  --------   -----------   ---------  ------------------
WLS_REPOS   oracle    1234       wls1221       unknown    JDBC Thin Client
WLS_REPOS   oracle    1234       wls1221       unknown    JDBC Thin Client
WLS_REPOS   oracle    1234       wls1221       unknown    JDBC Thin Client

You just see under the column program "JDBC Thin Client", but you don't see which defined Data Source is behind your connection in the Oracle Database :-(

Since Oracle WebLogic Server 12.2.1 you have now the possibilty to push a defined set of variables from your Data Source definition into the v$session.

Variable Description
${pid} First part (up to @) of ManagementFactory.getRuntimeMXBean().getName()
${machine} Second part of ManagementFactory.getRuntimeMXBean().getName()
${user.name} Java system property user.anem
${os.name} System property os.name
${datasourcename} Name of your Data Source
${partition} Name of your Partition
${serverport} Listen Port of your WebLogic Server
${serversslport} SSL Listen Port of your WebLogic Server
${servername} Name of your WebLogic Server
${domainname} Name of your WebLogic Domain

With the above list of defined variables you have a wide range of possibilties to populate them into your v$session.

Snippet of your <datasource_name>-<internal_number>-jdbc.xml Configuration File


  user
  WLS_REPOS


  v$session.osuser
  ${user.name}


  v$session.process
  ${pid}


  v$session.machine
  ${machine}


  v$session.terminal
  ${datasourcename}


  v$session.program
  WLS ${servername} @ ${domainname}

Or you can also modify your data source definition within the Oracle WebLogic Server Console under "Services / Data Sources", just select your Data Source and under "Configuration / Connection Pool" modify the "System Properties"


After saving and activating your changes, simply restart your Managed Server(s) which are the target(s) for your modified Data Sources.

You have to consider following limitations for the v$session columns:

v$session Column Length Limiations
osuser 30 characters
process 24 characters
machine 64 characters
terminal 30 characters
program 48 characters

With the above decribed changes on your Data Source definition, you are now able to identify easily your different Data Source connections into your Oracle Database :-)

select username, osuser, process, machine, terminal, program
from v$session where username = 'WLS_REPOS' order by 5;
USERNAME    OSUSER    PROCESS    MACHINE       TERMINAL   PROGRAM
---------   --------  --------   -----------   ---------  --------------------------------
WLS_REPOS   oracle    2959       wls1221       myDS       WLS ManagedServer1 @ base_domain
WLS_REPOS   oracle    2959       wls1221       myDS       WLS ManagedServer1 @ base_domain
WLS_REPOS   oracle    2959       wls1221       myTestDS   WLS ManagedServer2 @ base_domain

Tuesday, October 27, 2015

Oracle Forms 12c (12.2.1) first impressions

As I already posted over the weekend, several new Oracle Fusion Middleware Components were released by Oracle.

One of this new Components is Oracle Forms & Reports 12c (12.2.1).

I have been playing around a little bit with the latest release:


  • Oracle Forms & Reports 12c is now requiring a configured Meta Repository
  • Don't try to use as the underlying Oracle WebLogic Server 12c (12.2.1) the "standalone" WebLogic Server, you NEED to install the Fusion Middleware Infrastructure Option (1.4 GB download) or else you can not install the Forms & Reports Part on top of your WebLogic/Infrastructure
  • The new Enterprise Manager is really cool now :-) can be reached under http://yourserver:port/em

The Look'n Feel is completely redesigned, my personal feeling is that the Oracle ALTA UI from Oracle ADF was the base for this really cool redesign. 

Sunday, October 25, 2015

Several New Oracle Fusion Middleware Components 12.2.1 available

Oracle has been busy releasing several new Release for various Oracle Fusion Middleware Components on Friday 23rd October 2015

Oracle WebLogic Server is now available as Release 12.2.1
Download can be found here: http://www.oracle.com/technetwork/middleware/weblogic/downloads/index.html

  • There are in general 3 options available for the latest Oracle WebLogic Server 12.2.1
    • Quick Installer for Developers for Windows, Linux and MAC OS X
    • Generic Installer
    • Fusion Middleware Infrastructure Installer
Oracle Forms & Reports is now also available in the Release 12.2.1
Download can be found here:

  • The latest Oracle Forms & Reports 12c (12.2.1) is available for following Operating Systems:
    • Windows 64bit
    • Linux x86-64
    • Oracle Solaris on SPARC (64bit)
    • Oracle Solaris on Intel (x86-84)
    • HP UX Itanium
    • IBM AIX on Power Systems (64bit)
Oracle Business Intelligence Enterprise Edition (OBIEE) is also released for 12.2.1.0.0
Download can be found here:

Oracle WebCenter has also been release with 12.2.1.
Oracle SOA Suite comes also along in a new release with 12.2.1


Happy Download :-)





Thursday, April 16, 2015

APEX 5.0 available for download

Since yesterday (15th April 2015) the latest Oracle Application Express (APEX) release 5.0 is available for download.

APEX 5.0 can be found under http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html



The new APEX 5.0 is supported for all Editions of Oracle Database 11.1.0.7 or higher, even Oracle Express Edition 11g (XE) can be used for the new APEX 5.0