12c

Oracle Database Backup Service – Encrypt your 12.2 Database Backups to the Cloud

Author: Martin Berger 27. August 2018

The Oracle RMAN backup encryption is necessary if you want to backup your database into the Oracle cloud. In Oracle 12c, you have three methods available to encrypt an Oracle RMAN backup:

with a passphrase
with a master encryption key
hybrid with a passphrase and an encryption key

On docs.oracle.com, the basic setup is described here: https://docs.oracle.com/en/cloud/paas/db-backup-cloud/csdbb/configuring-encryption-backups.html#GUID-4A1F5CF5-7EAF-4D71-9B7F-B46412F552CE

In this blog post, I show you how to configure your database environment with a master encryption key and a keystore. I use this solution to to backup and recovery to and into the Oracle cloud. And in the cloud, I don’t like to type in passwords manually for every action or write passwords in backup and restore scripts.

There are also some issues reports like in My Oracle Support Note TDE Wallet Problem in 12c: Cannot do a Set Key operation when an auto-login wallet is present (Doc ID 1944507.1).

Here are steps to create an autologin wallet.

Configure SQLNET.ora in $TNS_ADMIN to use a Keystore

ENCRYPTION_WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /u00/app/oracle/network/wallet)
     )
    )

ENCRYPTION_WALLET_LOCATION =

(SOURCE =

(METHOD = FILE)

(METHOD_DATA =

(DIRECTORY = /u00/app/oracle/network/wallet)

)

Create Keystore as SYSDBA

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u00/app/oracle/tde_wallet' IDENTIFIED BY "my#wallet18";

1	SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u00/app/oracle/tde_wallet' IDENTIFIED BY "my#wallet18";

Open Keystore

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "my#wallet18";

1	SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "my#wallet18";

The status is set to OPEN_NO_MASTER_KEY.

SQL> SELECT wrl_parameter, wallet_type, status
  2  FROM v$encryption_wallet;

WRL_PARAMETER                       WALLET_TYPE     STATUS
----------------------------------- --------------- --------------------
/u00/app/oracle/tde_wallet/     PASSWORD        OPEN_NO_MASTER_KEY

SQL> SELECT wrl_parameter, wallet_type, status

2 FROM v$encryption_wallet;

WRL_PARAMETER WALLET_TYPE STATUS

----------------------------------- --------------- --------------------

/u00/app/oracle/tde_wallet/ PASSWORD OPEN_NO_MASTER_KEY

Set Master Key

Now the master key has to defined. When you have already defined a wallet earlier and deleted the keys, you have to set the undocumented parameter to set the master key again. This works here too to set the key. Otherwise you get an ORA-28374: typed master key not found in wallet error. See Master Note For Transparent Data Encryption ( TDE ) (Doc ID 1228046.1) for further information.

SQL> ALTER SYSTEM SET "_db_discard_lost_masterkey"=true;
SQL> ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY "my#wallet18" WITH BACKUP USING 'master_key_1';

1 2	SQL> ALTER SYSTEM SET "_db_discard_lost_masterkey"=true; SQL> ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY "my#wallet18" WITH BACKUP USING 'master_key_1';

Now the status is set to OPEN.

SQL> SELECT wrl_parameter, wallet_type, status
  2  FROM v$encryption_wallet;

WRL_PARAMETER                       WALLET_TYPE     STATUS
----------------------------------- --------------- --------------------
/u00/app/oracle/tde_wallet/     PASSWORD        OPEN

SQL> SELECT wrl_parameter, wallet_type, status

2 FROM v$encryption_wallet;

WRL_PARAMETER WALLET_TYPE STATUS

----------------------------------- --------------- --------------------

/u00/app/oracle/tde_wallet/ PASSWORD OPEN

Activate Auto Login

SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u00/app/oracle/tde_wallet' IDENTIFIED BY "my#wallet18";

1	SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u00/app/oracle/tde_wallet' IDENTIFIED BY "my#wallet18";

Restart the Database

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

1 2	SQL> SHUTDOWN IMMEDIATE SQL> STARTUP

Verify if the keystore is available and WALLET_TYPE is AUTOLOGIN.

SQL> SELECT wrl_parameter, wallet_type, status
  2  FROM v$encryption_wallet;

WRL_PARAMETER                       WALLET_TYPE     STATUS
----------------------------------- --------------- --------------------
/u00/app/oracle/tde_wallet/     AUTOLOGIN       OPEN

SQL> SELECT wrl_parameter, wallet_type, status

2 FROM v$encryption_wallet;

WRL_PARAMETER WALLET_TYPE STATUS

----------------------------------- --------------- --------------------

/u00/app/oracle/tde_wallet/ AUTOLOGIN OPEN

Configure RMAN for Encryption

RMAN> CONFIGURE ENCRYPTION FOR DATABASE ON;

1	RMAN> CONFIGURE ENCRYPTION FOR DATABASE ON;

RMAN Backup Test

A simple RMAN controlfile backup into the Oracle cloud (OPC Backup Module is already configured).

RUN {  
 allocate channel t1 type 'sbt_tape' parms='SBT_LIBRARY=libopc.so, SBT_PARMS=(OPC_PFILE=/u00/app/oracle/admin/OCIDB01/opc_config/opcOCIDB01.ora)';  
 backup current controlfile;  
 release channel t1;  
}

RUN {

allocate channel t1 type 'sbt_tape' parms='SBT_LIBRARY=libopc.so, SBT_PARMS=(OPC_PFILE=/u00/app/oracle/admin/OCIDB01/opc_config/opcOCIDB01.ora)';

backup current controlfile;

release channel t1;

}

Error message if you want to backup into the Oracle cloud and the encryption is not configured correctly:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on t1 channel at 08/27/2018 18:48:27
ORA-19914: unable to encrypt backup
ORA-28365: wallet is not open

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on t1 channel at 08/27/2018 18:48:27

ORA-19914: unable to encrypt backup

ORA-28365: wallet is not open

Backup Verification in V$BACKUP_PIECE – Column ENCRYPTED

SQL> SELECT start_time,handle,substr(media,1,30),encrypted
  2  FROM v$backup_piece;

START_TIME         HANDLE                                   SUBSTR(MEDIA,1,30)                  ENC
------------------ ---------------------------------------- ----------------------------------- ---
27-AUG-18          c-903044157-20180827-00                  eucom-north-1.stora..orage-tri      YES

SQL> SELECT start_time,handle,substr(media,1,30),encrypted

2 FROM v$backup_piece;

START_TIME HANDLE SUBSTR(MEDIA,1,30) ENC

------------------ ---------------------------------------- ----------------------------------- ---

27-AUG-18 c-903044157-20180827-00 eucom-north-1.stora..orage-tri YES

Links

http://www.oracle.com/technetwork/database/security/twp-transparent-data-encryption-bes-130696.pdf

http://www.oracle.com/technetwork/database/security/index-095354.html

12c, 12cR2, Oracle Cloud, Oracle RDBMS, TrivadisContent

Install and Configure Oracle Application Express with Oracle REST Data Services and Apache Tomcat

Author: Martin Berger 9. May 2017

In this article I will show you how you can install and configure Oracle Application Express (APEX) and the Oracle REST Data Service (ORDS), which is running on an Apache Tomcat application server. In previous installations I used the Oracle HTTP server and the extenstion mod_plsq. But this does not work anymore with the newest Oracle HTTP server version. In the Oracle Application Express Installation Guide is written:

mod_plsql is deprecated as of Oracle HTTP Server 12c (12.1.3). For more information, please see My Oracle Support Note 1576588.1. Oracle recommends using Oracle REST Data Services instead.

Oracle REST Data Service is the future – so let’s go to the future.

Installation Steps

Installation and Configuration Oracle Application Express 5.1.1
Installation and Configuration Apache Tomcat 8.5.14
Installation and Configuration Oracle Rest Data Service ORDS 3.0.9

My new architecture what I want to build looks like as described on this picture.

Source: http://www.oracle.com/technetwork/developer-tools/apex/application-express/apex-deploy-installation-1878444.html

My Environment

Red Hat Enterprise Linux Server Release 7.3 – server hostname is neuendorf.jurasuedfuss.coom
Oracle Database 12c Standard Edition Release 12.2.0.1.0 – database service name is APEXORDS.jurasuedfuss.com
OS Firewall is open for Port 8080

OS Users

I work with two OS users to separate RDBMS and application tasks:

oracle – Oracle RDBMS / Listener / Oracle Application Express
tomcat – Apache Tomcat / ORDS / Java Development Kit JDK 1.8

Directories

Oracle Software: /u01/app/oracle
Apache Tomcat / ORDS / JDK: /u01/app/tomcat

1. Installation and Configuration Oracle Application Express 5.1.1 – OS User: oracle

I have downloaded Oracle Application Express here: http://www.oracle.com/technetwork/developer-tools/apex/downloads/download-085147.html . The extracted software is located on the server in the /tmp directory. For the APEX data I have created a new tablespace called APEX too.

Go to the software location:

[oracle@neuendorf ~]$ cd /tmp/apex

1	[oracle@neuendorf ~]$ cd /tmp/apex

[oracle@neuendorf ~]$ . oraenv
ORACLE_SID = [oracle] ? APEXORDS
The Oracle base has been set to /u01/app/oracle

[oracle@neuendorf ~]$ sqlplus / as sysdba

[oracle@neuendorf ~]$ . oraenv

ORACLE_SID = [oracle] ? APEXORDS

The Oracle base has been set to /u01/app/oracle

[oracle@neuendorf ~]$ sqlplus / as sysdba

Execute the installation script:

SQL> @apexins apex apex temp /i/

1	SQL> @apexins apex apex temp /i/

Set password for ADMIN user / Workspace INTERNAL:

SQL> @apxchpwd.sql

1	SQL> @apxchpwd.sql

Configure database RESTful services – the passwords for the new created users APEX_LISTENER and APEX_REST_PUBLIC_USER will be used later for the ORDS setup:

SQL> @apex_rest_config.sql

1	SQL> @apex_rest_config.sql

Set password for the APEX_PUBLIC_USER and unlock the account:

SQL> ALTER USER apex_public_user IDENTIFIED BY  <MY_APEX_PUBLIC_USER_PASSWORD> ACCOUNT UNLOCK;

1	SQL> ALTER USER apex_public_user IDENTIFIED BY <MY_APEX_PUBLIC_USER_PASSWORD> ACCOUNT UNLOCK;

To avoid the password expiration for the APEX_PUBLIC_USER, I have created a new profile especially for this user with unlimited password lifetime:

SQL> CREATE PROFILE upd_password_life_time_unlimited LIMIT PASSWORD_LIFE_TIME UNLIMITED;
SQL> ALTER USER apex_public_user PROFILE upd_password_life_time_unlimited;

1 2	SQL> CREATE PROFILE upd_password_life_time_unlimited LIMIT PASSWORD_LIFE_TIME UNLIMITED; SQL> ALTER USER apex_public_user PROFILE upd_password_life_time_unlimited;

Allow other hosts than the localhost to use the Oracle Application Express installation:

SQL> BEGIN
2     DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
3          host => '*',
4          ace => xs$ace_type(privilege_list => xs$name_list('connect'),
5          principal_name => 'apex_050100',
6          principal_type => xs_acl.ptype_db));
7 END;
8 /

SQL> BEGIN

2 DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(

3 host => '*',

4 ace => xs$ace_type(privilege_list => xs$name_list('connect'),

5 principal_name => 'apex_050100',

6 principal_type => xs_acl.ptype_db));

7 END;

8 /

2. Installation and Configuration Apache Tomcat 8.5.14 – OS User: tomcat

Create directory :

[tomcat@neuendorf ~]$ mkdir /u01/app/tomcat

1	[tomcat@neuendorf ~]$ mkdir /u01/app/tomcat

Go to directory and extract software form /tmp:

[tomcat@neuendorf ~]$ cd /u01/app/tomcat/
[tomcat@neuendorf ~]$ tar xvfz /tmp/jdk-8u131-linux-x64.tar.gz
[tomcat@neuendorf ~]$ tar xvfz /tmp/apache-tomcat-8.5.14.tar.gz

[tomcat@neuendorf ~]$ cd /u01/app/tomcat/

[tomcat@neuendorf ~]$ tar xvfz /tmp/jdk-8u131-linux-x64.tar.gz

[tomcat@neuendorf ~]$ tar xvfz /tmp/apache-tomcat-8.5.14.tar.gz

Two new directories for Apache Tomcat and JDK are created:

[tomcat@neuendorf tomcat]$ ll
total 8
drwxrwxr-x. 9 tomcat tomcat 4096 May 9 12:29 apache-tomcat-8.5.14
drwxr-xr-x. 8 tomcat tomcat 4096 Mar 15 09:35 jdk1.8.0_131

[tomcat@neuendorf tomcat]$ ll

total 8

drwxrwxr-x. 9 tomcat tomcat 4096 May 9 12:29 apache-tomcat-8.5.14

drwxr-xr-x. 8 tomcat tomcat 4096 Mar 15 09:35 jdk1.8.0_131

To simplify the management with the Apache Tomcat application server, I have added environment variables to the .bash_profile:

# Tomcat Environment Variables
JAVA_HOME=/u01/app/tomcat/jdk1.8.0_131
CATALINA_HOME=/u01/app/tomcat/apache-tomcat-8.5.14
CATALINA_BASE=$CATALINA_HOME

export JAVA_HOME
export CATALINA_HOME
export CATALINA_BASE

# Tomcat Environment Variables

JAVA_HOME=/u01/app/tomcat/jdk1.8.0_131

CATALINA_HOME=/u01/app/tomcat/apache-tomcat-8.5.14

CATALINA_BASE=$CATALINA_HOME

export JAVA_HOME

export CATALINA_HOME

export CATALINA_BASE

Startup Tomcat – after the re-login as OS user tomcat the application server can be started by using the environment variable $CATALINA_HOME.

[tomcat@neuendorf ~]$ $CATALINA_HOME/bin/startup.sh
Using CATALINA_BASE: /u01/app/tomcat/apache-tomcat-8.5.14
Using CATALINA_HOME: /u01/app/tomcat/apache-tomcat-8.5.14
Using CATALINA_TMPDIR: /u01/app/tomcat/apache-tomcat-8.5.14/temp
Using JRE_HOME: /u01/app/tomcat/jdk1.8.0_131
Using CLASSPATH: /u01/app/tomcat/apache-tomcat-8.5.14/bin/bootstrap.jar:/u01/app/tomcat/apache-tomcat-8.5.14/bin/tomcat-juli.jar
Tomcat started.

[tomcat@neuendorf ~]$ $CATALINA_HOME/bin/startup.sh

Using CATALINA_BASE: /u01/app/tomcat/apache-tomcat-8.5.14

Using CATALINA_HOME: /u01/app/tomcat/apache-tomcat-8.5.14

Using CATALINA_TMPDIR: /u01/app/tomcat/apache-tomcat-8.5.14/temp

Using JRE_HOME: /u01/app/tomcat/jdk1.8.0_131

Using CLASSPATH: /u01/app/tomcat/apache-tomcat-8.5.14/bin/bootstrap.jar:/u01/app/tomcat/apache-tomcat-8.5.14/bin/tomcat-juli.jar

Tomcat started.

Verify on command line level if tomcat has started, for example with CURL – HTTP 200 means that the response is OK:

[tomcat@neuendorf tomcat]$ curl -I http://localhost:8080
HTTP/1.1 200
Content-Type: text/html;charset=UTF-8
Transfer-Encoding: chunked
Date: Tue, 09 May 2017 12:10:11 GMT

[tomcat@neuendorf tomcat]$ curl -I http://localhost:8080

HTTP/1.1 200

Content-Type: text/html;charset=UTF-8

Transfer-Encoding: chunked

Date: Tue, 09 May 2017 12:10:11 GMT

Browser Verification – http://neuendorf.jurasuedfuss.com:

Shutdown Tomcat:

[tomcat@neuendorf ~]$ $CATALINA_HOME/bin/shutdown.sh

1	[tomcat@neuendorf ~]$ $CATALINA_HOME/bin/shutdown.sh

Start- / Stop Runlevel Script:

To automate the start/stop – we use a runlevel script. The script has to be created as OS user root. In one of the first lines, I have set a sleep command to be sure that the database is available before the application server starts.

[root@neuendorf ~]# vi /etc/init.d/tomcat

1	[root@neuendorf ~]# vi /etc/init.d/tomcat

Content:

#!/bin/bash
#
# tomcat 
#
# chkconfig: 
# description: Start up the Tomcat servlet engine.

# Source function library.
. /etc/init.d/functions

# Sleep 20 seconds until database is started sucessfully
sleep 20

RETVAL=$?
CATALINA_HOME="/u01/app/tomcat/apache-tomcat-8.5.14"

case "$1" in
start)
if [ -f $CATALINA_HOME/bin/startup.sh ];
then
echo $"Starting Tomcat"
/bin/su tomcat $CATALINA_HOME/bin/startup.sh
fi
;;
stop)
if [ -f $CATALINA_HOME/bin/shutdown.sh ];
then
echo $"Stopping Tomcat"
/bin/su tomcat $CATALINA_HOME/bin/shutdown.sh
fi
;;
*)
echo $"Usage: $0 {start|stop}"
exit 1
;;
esac

exit $RETVAL

#!/bin/bash

# tomcat

# chkconfig:

# description: Start up the Tomcat servlet engine.

# Source function library.

. /etc/init.d/functions

# Sleep 20 seconds until database is started sucessfully

sleep 20

RETVAL=$?

CATALINA_HOME="/u01/app/tomcat/apache-tomcat-8.5.14"

case "$1" in

start)

if [ -f $CATALINA_HOME/bin/startup.sh ];

then

echo $"Starting Tomcat"

/bin/su tomcat $CATALINA_HOME/bin/startup.sh

;;

stop)

if [ -f $CATALINA_HOME/bin/shutdown.sh ];

then

echo $"Stopping Tomcat"

/bin/su tomcat $CATALINA_HOME/bin/shutdown.sh

;;

echo $"Usage: $0 {start|stop}"

exit 1

;;

esac

exit $RETVAL

Add the script to the runlevel environment level 3 and 5:

[root@neuendorf ~]# chmod 755 /etc/init.d/tomcat

1	[root@neuendorf ~]# chmod 755 /etc/init.d/tomcat

[root@neuendorf ~]# cd /etc/rc3.d
[root@neuendorf ~]# ln -s /etc/init.d/tomcat S99tomcat

[root@neuendorf ~]# cd /etc/rc5.d
[root@neuendorf ~]# ln -s /etc/init.d/tomcat S99tomcat

[root@neuendorf ~]# cd /etc/rc3.d

[root@neuendorf ~]# ln -s /etc/init.d/tomcat S99tomcat

[root@neuendorf ~]# cd /etc/rc5.d

[root@neuendorf ~]# ln -s /etc/init.d/tomcat S99tomcat

3. Installation and Configuration Oracle Rest Data Service ORDS 3.0.9 – OS User: tomcat

Create ORDS installation directory:

[tomcat@neuendorf tomcat]$ cd /u01/app/tomcat/
[tomcat@neuendorf tomcat]$ mkdir ords.3.0.9
[tomcat@neuendorf tomcat]$ cd ords.3.0.9

[tomcat@neuendorf tomcat]$ cd /u01/app/tomcat/

[tomcat@neuendorf tomcat]$ mkdir ords.3.0.9

[tomcat@neuendorf tomcat]$ cd ords.3.0.9

Extract ORDS from /tmp directory:

[tomcat@neuendorf tomcat]$ unzip /tmp/ords.3.0.9.348.07.16.zip

1	[tomcat@neuendorf tomcat]$ unzip /tmp/ords.3.0.9.348.07.16.zip

Verify directory content:

[tomcat@neuendorf tomcat]$ cd /u01/app/tomcat/ords.3.0.9
[tomcat@neuendorf ords.3.0.9]$ ll
total 48332
drwxrwxr-x. 3 tomcat tomcat 17 May 9 12:40 conf
drwxr-xr-x. 3 tomcat tomcat 20 Dec 13 07:16 docs
drwxr-xr-x. 6 tomcat tomcat 81 Dec 13 07:21 examples
drwxr-xr-x. 2 tomcat tomcat 4096 May 9 12:42 logs
-rw-r--r--. 1 tomcat tomcat 49442163 May 9 12:40 ords.war
drwxr-xr-x. 2 tomcat tomcat 35 May 9 12:40 params
-rw-r--r--. 1 tomcat tomcat 43605 Dec 13 07:21 readme.html

[tomcat@neuendorf tomcat]$ cd /u01/app/tomcat/ords.3.0.9

[tomcat@neuendorf ords.3.0.9]$ ll

total 48332

drwxrwxr-x. 3 tomcat tomcat 17 May 9 12:40 conf

drwxr-xr-x. 3 tomcat tomcat 20 Dec 13 07:16 docs

drwxr-xr-x. 6 tomcat tomcat 81 Dec 13 07:21 examples

drwxr-xr-x. 2 tomcat tomcat 4096 May 9 12:42 logs

-rw-r--r--. 1 tomcat tomcat 49442163 May 9 12:40 ords.war

drwxr-xr-x. 2 tomcat tomcat 35 May 9 12:40 params

-rw-r--r--. 1 tomcat tomcat 43605 Dec 13 07:21 readme.html

Create ORDS directory and edit the database configuration template – set hostname and database service name:

[tomcat@neuendorf tomcat]$ mkdir /u01/app/tomcat/ords.3.0.9/conf
[tomcat@neuendorf tomcat]$ vi /u01/app/tomcat/ords.3.0.9/params/ords_params.properties

1 2	[tomcat@neuendorf tomcat]$ mkdir /u01/app/tomcat/ords.3.0.9/conf [tomcat@neuendorf tomcat]$ vi /u01/app/tomcat/ords.3.0.9/params/ords_params.properties

Content of my ords_params.properties file:

db.hostname=neuendorf
db.port=1521
db.servicename=APEXORDS.jurasuedfuss.com
db.username=APEX_PUBLIC_USER
migrate.apex.rest=false
rest.services.apex.add=
rest.services.ords.add=true
schema.tablespace.default=APEX
schema.tablespace.temp=TEMP
user.tablespace.default=USERS
user.tablespace.temp=TEMP

db.hostname=neuendorf

db.port=1521

db.servicename=APEXORDS.jurasuedfuss.com

db.username=APEX_PUBLIC_USER

migrate.apex.rest=false

rest.services.apex.add=

rest.services.ords.add=true

schema.tablespace.default=APEX

schema.tablespace.temp=TEMP

user.tablespace.default=USERS

user.tablespace.temp=TEMP

Set Oracle REST Database Service ORDS configuration directory:

[tomcat@neuendorf tomcat]$ cd /u01/app/tomcat/ords.3.0.9/
[tomcat@neuendorf ords.3.0.9]$ $JAVA_HOME/bin/java -jar ords.war configdir /u01/app/tomcat/ords.3.0.9/conf
May 09, 2017 12:33:54 PM
INFO: Set config.dir to /u01/app/tomcat/ords.3.0.9/conf in: /u01/app/tomcat/ords.3.0.9/ords.war

[tomcat@neuendorf tomcat]$ cd /u01/app/tomcat/ords.3.0.9/

[tomcat@neuendorf ords.3.0.9]$ $JAVA_HOME/bin/java -jar ords.war configdir /u01/app/tomcat/ords.3.0.9/conf

May 09, 2017 12:33:54 PM

INFO: Set config.dir to /u01/app/tomcat/ords.3.0.9/conf in: /u01/app/tomcat/ords.3.0.9/ords.war

Install Oracle REST Database Service ORDS:

You will be asked for a new password for the database user ORDS_PUBLIC_USER (user will be created by this script), for the already existing users APEX_LISTENER and APEX_REST_PUBLIC_USER and for the SYS password. For ORDS metadata, I have created a new tablespace called ORDS.

The values for database server, hostname and the service name are taken from the configuration file ords_params.properties. Do not start ORDS in standalone mode at the end of the configuration.

[tomcat@neuendorf ords.3.0.9]$ $JAVA_HOME/bin/java -jar ords.war install advanced
Enter the name of the database server [neuendorf]: <ENTER>
Enter the database listen port [1521]: <ENTER>
Enter 1 to specify the database service name, or 2 to specify the database SID [1]: <ENTER>
Enter the database service name [ORDSAPEX.jurasuedfuss.com]: <ENTER>
Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]: <ENTER>
Enter the database password for ORDS_PUBLIC_USER: <MY_ORDS_PUBLIC_USER_PASSWORD> <ENTER>
Confirm password:
Please login with SYSDBA privileges to verify Oracle REST Data Services schema.

Enter the username with SYSDBA privileges to verify the installation [SYS]: <ENTER>
Enter the database password for SYS: <MY_SYS_PASSWORD> <ENTER>
Confirm password:
Enter the default tablespace for ORDS_METADATA [APEX]: ORDS <ENTER>
Enter the temporary tablespace for ORDS_METADATA [TEMP]: <ENTER>
Enter the default tablespace for ORDS_PUBLIC_USER [USERS]:ORDS <ENTER>
Enter the temporary tablespace for ORDS_PUBLIC_USER [TEMP]: <ENTER>
Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step.
If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]: <ENTER>
Enter the PL/SQL Gateway database user name [APEX_PUBLIC_USER]: <ENTER>
Enter the database password for APEX_PUBLIC_USER: <MY_APEX_PUBLIC_USER_PASSWORD> <ENTER>
Confirm password: <MY_APEX_PUBLIC_USER_PASSWORD> <ENTER>
Enter 1 to specify passwords for Application Express RESTful Services database users (APEX_LISTENER, APEX_REST_PUBLIC_USER) or 2 to skip this step [1]: <ENTER>
Enter the database password for APEX_LISTENER: <MY_APEX_LISTENER_PASSWORD> <ENTER>
Confirm password: <MY_APEX_LISTENER_PASSWORD> <ENTER>
Enter the database password for APEX_REST_PUBLIC_USER: <MY_APEX_REST_PUBLIC_USER_PASSWORD> <ENTER>
Confirm password: <MY_APEX_REST_PUBLIC_USER_PASSWORD> <ENTER>
May 09, 2017 11:18:27 AM
INFO: Updated configurations: defaults, apex, apex_pu, apex_al, apex_rt
Installing Oracle REST Data Services version 3.0.9.348.07.16
... Log file written to /u01/app/tomcat/ords.3.0.9/logs/ords_install_core_2017-05-09_111827_00102.log
... Verified database prerequisites
... Created Oracle REST Data Services schema
... Created Oracle REST Data Services proxy user
... Granted privileges to Oracle REST Data Services
... Created Oracle REST Data Services database objects
... Log file written to /u01/app/tomcat/ords.3.0.9/logs/ords_install_datamodel_2017-05-09_111843_00674.log
Completed installation for Oracle REST Data Services version 3.0.9.348.07.16. Elapsed time: 00:00:18.80

Enter 1 if you wish to start in standalone mode or 2 to exit [1]: <2> <ENTER>

[tomcat@neuendorf ords.3.0.9]$ $JAVA_HOME/bin/java -jar ords.war install advanced

Enter the name of the database server [neuendorf]: <ENTER>

Enter the database listen port [1521]: <ENTER>

Enter 1 to specify the database service name, or 2 to specify the database SID [1]: <ENTER>

Enter the database service name [ORDSAPEX.jurasuedfuss.com]: <ENTER>

Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]: <ENTER>

Enter the database password for ORDS_PUBLIC_USER: <MY_ORDS_PUBLIC_USER_PASSWORD> <ENTER>

Confirm password:

Please login with SYSDBA privileges to verify Oracle REST Data Services schema.

Enter the username with SYSDBA privileges to verify the installation [SYS]: <ENTER>

Enter the database password for SYS: <MY_SYS_PASSWORD> <ENTER>

Confirm password:

Enter the default tablespace for ORDS_METADATA [APEX]: ORDS <ENTER>

Enter the temporary tablespace for ORDS_METADATA [TEMP]: <ENTER>

Enter the default tablespace for ORDS_PUBLIC_USER [USERS]:ORDS <ENTER>

Enter the temporary tablespace for ORDS_PUBLIC_USER [TEMP]: <ENTER>

Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step.

If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]: <ENTER>

Enter the PL/SQL Gateway database user name [APEX_PUBLIC_USER]: <ENTER>

Enter the database password for APEX_PUBLIC_USER: <MY_APEX_PUBLIC_USER_PASSWORD> <ENTER>

Confirm password: <MY_APEX_PUBLIC_USER_PASSWORD> <ENTER>

Enter 1 to specify passwords for Application Express RESTful Services database users (APEX_LISTENER, APEX_REST_PUBLIC_USER) or 2 to skip this step [1]: <ENTER>

Enter the database password for APEX_LISTENER: <MY_APEX_LISTENER_PASSWORD> <ENTER>

Confirm password: <MY_APEX_LISTENER_PASSWORD> <ENTER>

Enter the database password for APEX_REST_PUBLIC_USER: <MY_APEX_REST_PUBLIC_USER_PASSWORD> <ENTER>

Confirm password: <MY_APEX_REST_PUBLIC_USER_PASSWORD> <ENTER>

May 09, 2017 11:18:27 AM

INFO: Updated configurations: defaults, apex, apex_pu, apex_al, apex_rt

Installing Oracle REST Data Services version 3.0.9.348.07.16

... Log file written to /u01/app/tomcat/ords.3.0.9/logs/ords_install_core_2017-05-09_111827_00102.log

... Verified database prerequisites

... Created Oracle REST Data Services schema

... Created Oracle REST Data Services proxy user

... Granted privileges to Oracle REST Data Services

... Created Oracle REST Data Services database objects

... Log file written to /u01/app/tomcat/ords.3.0.9/logs/ords_install_datamodel_2017-05-09_111843_00674.log

Completed installation for Oracle REST Data Services version 3.0.9.348.07.16. Elapsed time: 00:00:18.80

Enter 1 if you wish to start in standalone mode or 2 to exit [1]: <2> <ENTER>

You can see the encrypted passwords and the selected tablespaces in the configuration file ords_params.properties:

[tomcat@neuendorf tomcat]$ cat /u01/app/tomcat/ords.3.0.9/params/ords_params.properties

#Tue May 09 12:41:57 CEST 2017
db.hostname=neuendorf
db.password=@057AF00D1B3F13CCECCD71BCF1E0AD259AA183443E64B284CE
db.port=1521
db.servicename=APEXORDS.jurasuedfuss.com
db.username=APEX_PUBLIC_USER
migrate.apex.rest=false
plsql.gateway.add=true
rest.services.apex.add=true
rest.services.ords.add=true
schema.tablespace.default=ORDS
schema.tablespace.temp=TEMP
user.apex.listener.password=@05B9EEC26803C78359021659CB0EEFE0175C898A5D0404FD44
user.apex.restpublic.password=@05F57FA4BB374FE48E6452F1AD3E36FC5BD6B178B18952C791
user.public.password=@05B3277F27BC54402B72DE458C49A4188F33EBDCA7A021DFC3
user.tablespace.default=ORDS
user.tablespace.temp=TEMP

[tomcat@neuendorf tomcat]$ cat /u01/app/tomcat/ords.3.0.9/params/ords_params.properties

#Tue May 09 12:41:57 CEST 2017

db.hostname=neuendorf

db.password=@057AF00D1B3F13CCECCD71BCF1E0AD259AA183443E64B284CE

db.port=1521

db.servicename=APEXORDS.jurasuedfuss.com

db.username=APEX_PUBLIC_USER

migrate.apex.rest=false

plsql.gateway.add=true

rest.services.apex.add=true

rest.services.ords.add=true

schema.tablespace.default=ORDS

schema.tablespace.temp=TEMP

user.apex.listener.password=@05B9EEC26803C78359021659CB0EEFE0175C898A5D0404FD44

user.apex.restpublic.password=@05F57FA4BB374FE48E6452F1AD3E36FC5BD6B178B18952C791

user.public.password=@05B3277F27BC54402B72DE458C49A4188F33EBDCA7A021DFC3

user.tablespace.default=ORDS

user.tablespace.temp=TEMP

Optimize the database connection settings – add these lines to the apex.xml configuration file:

[tomcat@neuendorf tomcat]$ vi /u01/app/tomcat/ords.3.0.9/conf/ords/conf/apex.xml

<entry key="jdbc.InitialLimit">15</entry> 
<entry key="jdbc.MaxLimit">50</entry> 
<entry key="jdbc.MinLimit">15</entry>

[tomcat@neuendorf tomcat]$ vi /u01/app/tomcat/ords.3.0.9/conf/ords/conf/apex.xml

Prepare the application server directory for the Oracle Application Express images

[tomcat@neuendorf tomcat]$ mkdir $CATALINA_HOME/webapps/i/
[tomcat@neuendorf tomcat]$cp -R /tmp/apex/images/* $CATALINA_HOME/webapps/i/

1 2	[tomcat@neuendorf tomcat]$ mkdir $CATALINA_HOME/webapps/i/ [tomcat@neuendorf tomcat]$cp -R /tmp/apex/images/* $CATALINA_HOME/webapps/i/

Copy the ORDS application ords.war to the Apache Tomcat:

[tomcat@neuendorf tomcat]$ cd /u01/app/tomcat/ords.3.0.9
[tomcat@neuendorf tomcat]$ cp ords.war $CATALINA_HOME/webapps/

1 2	[tomcat@neuendorf tomcat]$ cd /u01/app/tomcat/ords.3.0.9 [tomcat@neuendorf tomcat]$ cp ords.war $CATALINA_HOME/webapps/

Startup the application server:

[tomcat@neuendorf ~]$ $CATALINA_HOME/bin/startup.sh
Using CATALINA_BASE: /u01/app/tomcat/apache-tomcat-8.5.14
Using CATALINA_HOME: /u01/app/tomcat/apache-tomcat-8.5.14
Using CATALINA_TMPDIR: /u01/app/tomcat/apache-tomcat-8.5.14/temp
Using JRE_HOME: /u01/app/tomcat/jdk1.8.0_131
Using CLASSPATH: /u01/app/tomcat/apache-tomcat-8.5.14/bin/bootstrap.jar:/u01/app/tomcat/apache-tomcat-8.5.14/bin/tomcat-juli.jar
Tomcat started.

[tomcat@neuendorf ~]$ $CATALINA_HOME/bin/startup.sh

Using CATALINA_BASE: /u01/app/tomcat/apache-tomcat-8.5.14

Using CATALINA_HOME: /u01/app/tomcat/apache-tomcat-8.5.14

Using CATALINA_TMPDIR: /u01/app/tomcat/apache-tomcat-8.5.14/temp

Using JRE_HOME: /u01/app/tomcat/jdk1.8.0_131

Using CLASSPATH: /u01/app/tomcat/apache-tomcat-8.5.14/bin/bootstrap.jar:/u01/app/tomcat/apache-tomcat-8.5.14/bin/tomcat-juli.jar

Tomcat started.

Login into Oracle Application Express – add /ords at the end of the application server URL like http://neuendorf.jurasuedfuss.com:8080/ords:

That’s it – have fun with the Oracle Application Express, Oracle REST Data Services and Tomcat :-). In the next post I will show you how you can secure your installation by change to https/SSL.

Manuals:

Installing Application Express and Configuring Oracle REST Data Services

Tomcat Web Application Deployment

Oracle Application Express Installation Overview

12c, Oracle Application Express, Oracle REST Data Service, Oracle Tools

DATABASE PATCH SET UPDATE 12.1.0.2.170117 apply fails – catconInit failed, exiting

Author: Martin Berger 5. April 2017

Last weekend was patchday. The goal was to apply the patch 24732082 (DATABASE PATCH SET UPDATE 12.1.0.2.170117) to a 12.1.0.2 database on AIX. The OPatch precheck returned no error and OPatch apply was ok. The problem was the post step, the datapatch command failed with the message catconInit failed, exiting.

oracle@srvaix101:/u00/app/oracle/product/12.1.0.2/dbhome_1/OPatch/ [STRSP01] ./datapatch -verbose
SQL Patching tool version 12.1.0.2.0 Production on Sun Apr  2 08:39:35 2017
Copyright (c) 2012, 2016, Oracle.  All rights reserved.

Log file for this invocation: /u00/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_45548440_2017_04_02_08_39_35/sqlpatch_invocati                                               on.log

Connecting to database...OK

catconInit failed, exiting

Please refer to MOS Note 1609718.1 and/or the invocation log
/u00/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_45548440_2017_04_02_08_39_35/sqlpatch_invocation.log
for information on how to resolve the above errors.

SQL Patching tool complete on Sun Apr  2 08:39:36 2017

oracle@srvaix101:/u00/app/oracle/product/12.1.0.2/dbhome_1/OPatch/ [STRSP01] ./datapatch -verbose

SQL Patching tool version 12.1.0.2.0 Production on Sun Apr 2 08:39:35 2017

Log file for this invocation: /u00/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_45548440_2017_04_02_08_39_35/sqlpatch_invocati on.log

Connecting to database...OK

catconInit failed, exiting

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

/u00/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_45548440_2017_04_02_08_39_35/sqlpatch_invocation.log

for information on how to resolve the above errors.

SQL Patching tool complete on Sun Apr 2 08:39:36 2017

The solution was described in this My Oracle Support Note: Datapatch fails with “catconInit: database is not open on the default instance” (Doc ID 2003488.1)

In the glogin.sql file located in ORACLE_HOME/sqlplus/admin were two lines:

set lines 400
set pages 0

1 2	set lines 400 set pages 0

After I have commented out these lines, everything runs ok.

oracle@srvaix101:/u00/app/oracle/product/12.1.0.2/dbhome_1/OPatch/ [STRSP01] ./datapatch -verbose
SQL Patching tool version 12.1.0.2.0 Production on Sun Apr  2 08:52:42 2017
Copyright (c) 2012, 2016, Oracle.  All rights reserved.

Log file for this invocation: /u00/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_45351856_2017_04_02_08_52_42/sqlpatch_invocation.log

Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Bundle series PSU:
  ID 170117 in the binary registry and not installed in the SQL registry

Adding patches to installation queue and performing prereq checks...
Installation queue:
  Nothing to roll back
    24732082 (DATABASE PATCH SET UPDATE 12.1.0.2.170117)

Installing patches...
Patch installation complete.  Total patches installed: 1

Validating logfiles...
Patch 24732082 apply: SUCCESS
  logfile: /u00/app/oracle/cfgtoollogs/sqlpatch/24732082/20919987/24732082_apply_STRSP01_2017Apr02_08_53_02.log (no errors)
SQL Patching tool complete on Sun Apr  2 08:53:39 2017

Summary: Two small lines, a huge effect.

oracle@srvaix101:/u00/app/oracle/product/12.1.0.2/dbhome_1/OPatch/ [STRSP01] ./datapatch -verbose

SQL Patching tool version 12.1.0.2.0 Production on Sun Apr 2 08:52:42 2017

Log file for this invocation: /u00/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_45351856_2017_04_02_08_52_42/sqlpatch_invocation.log

Connecting to database...OK

Bootstrapping registry and package to current versions...done

Determining current state...done

Current state of SQL patches:

Bundle series PSU:

ID 170117 in the binary registry and not installed in the SQL registry

Adding patches to installation queue and performing prereq checks...

Installation queue:

Nothing to roll back

24732082 (DATABASE PATCH SET UPDATE 12.1.0.2.170117)

Installing patches...

Patch installation complete. Total patches installed: 1

Validating logfiles...

Patch 24732082 apply: SUCCESS

logfile: /u00/app/oracle/cfgtoollogs/sqlpatch/24732082/20919987/24732082_apply_STRSP01_2017Apr02_08_53_02.log (no errors)

SQL Patching tool complete on Sun Apr 2 08:53:39 2017

Summary: Two small lines, a huge effect.

Summary: Two small lines, a big impact.

12c, Oracle RDBMS, Oracle Tools, TrivadisContent

Oracle Data Pump with the 12.2.0.1.0 Instant Client for Linux x86-64

Author: Martin Berger 22. March 2017

From the Oracle Database 12c Release 2 (12.2) New Features Guide:

Adding Oracle Data Pump and SQL*Loader Utilities to Instant Client

This feature adds SQL*Loader, expdp, impdp, exp, and imp to the tools for instant client.

Now you can run these utilities on machines that do not have a complete Oracle Database installation.

The newest release of the Oracle Instant Client for Linux x86-64 has an additional package called Tools. This package contains Data Pump, SQL*Loader and the Workload Replay Client for Real Aplication Testing. The good old import export tools is included too. This is very nice.

For example, if you want to load application log files with SQL*Loader into the database which are located on a separate server, there is no need for the client installation anymore. Or if your developers want to export data with Data Pump, all they need is the Instant Client now.

Just install – or better say unzip – the Instant Client basic package and the tools. Set some variables and go for it. The Instant Client packages are available as zip and as rpm for Unix systems. The rpm method requires root access to install.

Sizes of the Zip-Files

Instant Client Package – Basic: 66 Megabyte
Instant Client Package – Tools: 1 Megabyte

Zip Download URLs

Instant Client Package – Basic:
http://download.oracle.com/otn/linux/instantclient/122010/instantclient-basic-linux.x64-12.2.0.1.0.zip
Instant Client Package – Tools:
http://download.oracle.com/otn/linux/instantclient/122010/instantclient-tools-linux.x64-12.2.0.1.0.zip

SQL*Plus® User’s Guide and Reference

https://docs.oracle.com/database/122/SQPUG/SQL-Plus-instant-client.htm#SQPUG157

Instant Client Directory Content

Example for the Instant Client directory content on a Oracle Linux 7.2 server – Basic and Tools package installed.

oracle@kestenholz:/u00/app/oracle/product/instantclient_12_2/ [instantclient122] ll
total 221168
drwxr-xr-x. 2 oracle oinstall      4096 Mar 22 12:22 .
drwxr-xr-x. 6 oracle oinstall      4096 Mar 22 12:21 ..
-rw-r--r--. 1 oracle oinstall       363 Mar 22 12:21 BASIC_README
-rw-r--r--. 1 oracle oinstall       363 Mar 22 12:22 TOOLS_README
-rw-r--r--. 1 oracle oinstall     44220 Mar 22 12:21 adrci
-rw-r--r--. 1 oracle oinstall   1067736 Mar 22 12:22 exp
-rw-r--r--. 1 oracle oinstall    231727 Mar 22 12:22 expdp
-rw-r--r--. 1 oracle oinstall     57272 Mar 22 12:21 genezi
-rw-r--r--. 1 oracle oinstall    527125 Mar 22 12:22 imp
-rw-r--r--. 1 oracle oinstall    239564 Mar 22 12:22 impdp
-rw-r--r--. 1 oracle oinstall  71638263 Mar 22 12:21 libclntsh.so.12.1
-rw-r--r--. 1 oracle oinstall   8033199 Mar 22 12:21 libclntshcore.so.12.1
-rw-r--r--. 1 oracle oinstall   2981501 Mar 22 12:21 libipc1.so
-rw-r--r--. 1 oracle oinstall    539065 Mar 22 12:21 libmql1.so
-rw-r--r--. 1 oracle oinstall     77173 Mar 22 12:22 libnfsodm12.so
-rw-r--r--. 1 oracle oinstall   6568149 Mar 22 12:21 libnnz12.so
-rw-r--r--. 1 oracle oinstall   2218687 Mar 22 12:21 libocci.so.12.1
-rw-r--r--. 1 oracle oinstall 124771800 Mar 22 12:22 libociei.so
-rw-r--r--. 1 oracle oinstall    158543 Mar 22 12:22 libocijdbc12.so
-rw-r--r--. 1 oracle oinstall    380996 Mar 22 12:22 libons.so
-rw-r--r--. 1 oracle oinstall    116563 Mar 22 12:22 liboramysql12.so
-rw-r--r--. 1 oracle oinstall   4036257 Mar 22 12:22 ojdbc8.jar
-rw-r--r--. 1 oracle oinstall   1669142 Mar 22 12:22 sqlldr
-rw-r--r--. 1 oracle oinstall    240476 Mar 22 12:22 uidrvci
-rw-r--r--. 1 oracle oinstall    746798 Mar 22 12:22 wrc
-rw-r--r--. 1 oracle oinstall     74230 Mar 22 12:22 xstreams.jar

oracle@kestenholz:/u00/app/oracle/product/instantclient_12_2/ [instantclient122] ll

total 221168

drwxr-xr-x. 2 oracle oinstall 4096 Mar 22 12:22 .

drwxr-xr-x. 6 oracle oinstall 4096 Mar 22 12:21 ..

-rw-r--r--. 1 oracle oinstall 363 Mar 22 12:21 BASIC_README

-rw-r--r--. 1 oracle oinstall 363 Mar 22 12:22 TOOLS_README

-rw-r--r--. 1 oracle oinstall 44220 Mar 22 12:21 adrci

-rw-r--r--. 1 oracle oinstall 1067736 Mar 22 12:22 exp

-rw-r--r--. 1 oracle oinstall 231727 Mar 22 12:22 expdp

-rw-r--r--. 1 oracle oinstall 57272 Mar 22 12:21 genezi

-rw-r--r--. 1 oracle oinstall 527125 Mar 22 12:22 imp

-rw-r--r--. 1 oracle oinstall 239564 Mar 22 12:22 impdp

-rw-r--r--. 1 oracle oinstall 71638263 Mar 22 12:21 libclntsh.so.12.1

-rw-r--r--. 1 oracle oinstall 8033199 Mar 22 12:21 libclntshcore.so.12.1

-rw-r--r--. 1 oracle oinstall 2981501 Mar 22 12:21 libipc1.so

-rw-r--r--. 1 oracle oinstall 539065 Mar 22 12:21 libmql1.so

-rw-r--r--. 1 oracle oinstall 77173 Mar 22 12:22 libnfsodm12.so

-rw-r--r--. 1 oracle oinstall 6568149 Mar 22 12:21 libnnz12.so

-rw-r--r--. 1 oracle oinstall 2218687 Mar 22 12:21 libocci.so.12.1

-rw-r--r--. 1 oracle oinstall 124771800 Mar 22 12:22 libociei.so

-rw-r--r--. 1 oracle oinstall 158543 Mar 22 12:22 libocijdbc12.so

-rw-r--r--. 1 oracle oinstall 380996 Mar 22 12:22 libons.so

-rw-r--r--. 1 oracle oinstall 116563 Mar 22 12:22 liboramysql12.so

-rw-r--r--. 1 oracle oinstall 4036257 Mar 22 12:22 ojdbc8.jar

-rw-r--r--. 1 oracle oinstall 1669142 Mar 22 12:22 sqlldr

-rw-r--r--. 1 oracle oinstall 240476 Mar 22 12:22 uidrvci

-rw-r--r--. 1 oracle oinstall 746798 Mar 22 12:22 wrc

-rw-r--r--. 1 oracle oinstall 74230 Mar 22 12:22 xstreams.jar

Let’s Data Pump

Set the environment variables. TNS_ADMIN is where my tnsnames.ora / sqlnet.ora are located.

oracle@kestenholz:~/ [instantclient122] export LD_LIBRARY_PATH=/u00/app/oracle/product/instantclient_12_2:${LD_LIBRARY_PATH}
oracle@kestenholz:~/ [instantclient122] export PATH=/u00/app/oracle/product/instantclient_12_2:${PATH}
oracle@kestenholz:~/ [instantclient122] export TNS_ADMIN=/u00/app/oracle/network/admin

oracle@kestenholz:~/ [instantclient122] export LD_LIBRARY_PATH=/u00/app/oracle/product/instantclient_12_2:${LD_LIBRARY_PATH}

oracle@kestenholz:~/ [instantclient122] export PATH=/u00/app/oracle/product/instantclient_12_2:${PATH}

oracle@kestenholz:~/ [instantclient122] export TNS_ADMIN=/u00/app/oracle/network/admin

Start a Data Pump Export

oracle@kestenholz:~/ [instantclient122] expdp system/<mypassword>@GEPDB39.jurasuedfuss.com schemas=scott directory=expdp dumpfile=expdpscott.dmp logfile=expdpscott.log
Export: Release 12.2.0.1.0 - Production on Wed Mar 22 12:44:18 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@GEPDB39.jurasuedfuss.com schemas=scott directory=expdp dumpfile=expdpscott.dmp logfile=expdpscott.log
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /u00/app/oracle/admin/GECDB32/expdp/expdpscott.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Mar 22 12:44:44 2017 elapsed 0 00:00:25

oracle@kestenholz:~/ [instantclient122] expdp system/<mypassword>@GEPDB39.jurasuedfuss.com schemas=scott directory=expdp dumpfile=expdpscott.dmp logfile=expdpscott.log

Export: Release 12.2.0.1.0 - Production on Wed Mar 22 12:44:18 2017

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@GEPDB39.jurasuedfuss.com schemas=scott directory=expdp dumpfile=expdpscott.dmp logfile=expdpscott.log

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:

/u00/app/oracle/admin/GECDB32/expdp/expdpscott.dmp

Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Mar 22 12:44:44 2017 elapsed 0 00:00:25

Permission denied

If you get a permission denied error, change the file properties.

oracle@kestenholz:~/ [instantclient122] expdp system/<mypassword>@GEPDB39.jurasuedfuss.com schemas=scott directory=expdp dumpfile=expdpscott.dmp logfile=expdpscott.log
-bash: /u00/app/oracle/product/instantclient_12_2/expdp: Permission denied

oracle@kestenholz:/u00/app/oracle/product/instantclient_12_2/ [instantclient122] chmod 750 expdp

oracle@kestenholz:~/ [instantclient122] expdp system/<mypassword>@GEPDB39.jurasuedfuss.com schemas=scott directory=expdp dumpfile=expdpscott.dmp logfile=expdpscott.log

-bash: /u00/app/oracle/product/instantclient_12_2/expdp: Permission denied

oracle@kestenholz:/u00/app/oracle/product/instantclient_12_2/ [instantclient122] chmod 750 expdp

Operating System Availability of the Tools Package

At the moment – 22th of March 2017 – the tools packages is only available for these operating systems:

Microsoft Windows (x64)
Linux x86-64
Solaris Operating System (SPARC 64-bit)

I hope the AIX release will coming soon 🙂

12c, 12cR2, Oracle Tools, TrivadisContent

Enterprise Manager 13cR2 – How to apply Patch 23094292: WLS PATCH SET UPDATE 12.1.3.0.160719

Author: Martin Berger 17. October 2016

My fresh EM13cR2 installation showed me that there is a patch for the Weblogic environment available. This patch was released in July 2016: Patch Patch 23094292: WLS PATCH SET UPDATE 12.1.3.0.160719. This patchset has included 157 fixes and is a generic one. The patch is marked in My Oracle Support as recommended. OPatch has no to be updated. This patch is not an online patch, you have to shut down your running EM13cR2 server.

This blog post describes the apply of the Weblogic patch set update in an Enterprise Manager 13cR2 environment running on a Oracle Linux server.

Prepare Patch Set Update on EM13c Server

The patch file has to be extracted. I have copied to file to my EM13cR2 server stage directory /u00/app/oracle/stage.

[oracle@kestenholz ~]$ cd /u00/app/oracle/stage/
[oracle@kestenholz stage]$ ll
total 32560
-rw-r--r--. 1 oracle oinstall 33340052 Oct 17 12:51 p23094292_121300_Generic.zip
[oracle@kestenholz stage]$ unzip p23094292_121300_Generic.zip

[oracle@kestenholz ~]$ cd /u00/app/oracle/stage/

[oracle@kestenholz stage]$ ll

total 32560

-rw-r--r--. 1 oracle oinstall 33340052 Oct 17 12:51 p23094292_121300_Generic.zip

[oracle@kestenholz stage]$ unzip p23094292_121300_Generic.zip

Set ORACLE_HOME

Set the ORACLE_HOME variable to the directory where the Oracle Enterprise Manager 13cR2 is located. In my example the EM13cR2 is installed in directory /u00/app/oracle/product/em13cr2.

[oracle@kestenholz stage]$ export ORACLE_HOME=/u00/app/oracle/product/em13cr2

1	[oracle@kestenholz stage]$ export ORACLE_HOME=/u00/app/oracle/product/em13cr2

Stop running Oracle Enterprise Manager 13c

[oracle@kestenholz stage]$ $ORACLE_HOME/bin/emctl stop oms -all

1	[oracle@kestenholz stage]$ $ORACLE_HOME/bin/emctl stop oms -all

Apply Patch 23094292: WLS PATCH SET UPDATE 12.1.3.0.160719

Go to the extracted patch set directory:

[oracle@kestenholz ~]$ cd /u00/app/oracle/stage/23094292

1	[oracle@kestenholz ~]$ cd /u00/app/oracle/stage/23094292

Apply the patch:

[oracle@kestenholz 23094292]$ $ORACLE_HOME/OPatch/opatch apply
Oracle Interim Patch Installer version 13.8.0.0.0
Copyright (c) 2016, Oracle Corporation.  All rights reserved.


Oracle Home       : /u00/app/oracle/product/em13cr2
Central Inventory : /u00/app/oraInventory
   from           : /u00/app/oracle/product/em13cr2/oraInst.loc
OPatch version    : 13.8.0.0.0
OUI version       : 13.8.0.0.0
Log file location : /u00/app/oracle/product/em13cr2/cfgtoollogs/opatch/23094292_Oct_17_2016_13_02_45/apply2016-10-17_13-02-42PM_1.log


OPatch detects the Middleware Home as "/u00/app/oracle/product/em13cr2"

Verifying environment and performing prerequisite checks...

Conflicts/Supersets for each patch are:

Patch : 23094292

        Bug Superset of 21252292
        Super set bugs are:
        21252292

        Bug Superset of 21243471
        Super set bugs are:
        20613957, 19883023, 19703527

        Bug Superset of 20758863
        Super set bugs are:
        20758863

        Bug Superset of 19953516
        Super set bugs are:
        19953516

        Bug Superset of 19879223
        Super set bugs are:
        19879223

        Bug Superset of 19730967
        Super set bugs are:
        19730967

        Bug Superset of 18836900
        Super set bugs are:
        18836900


Patches [   21252292   21243471   20758863   19953516   19879223   19730967   18836900 ] will be rolled back.

OPatch continues with these patches:   23094292

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u00/app/oracle/product/em13cr2')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '23094292' to OH '/u00/app/oracle/product/em13cr2'
Rolling back interim patch '21252292' from OH '/u00/app/oracle/product/em13cr2'

Patching component oracle.wls.libraries, 12.1.3.0.0...

Patching component oracle.wls.libraries, 12.1.3.0.0...
RollbackSession removing interim patch '21252292' from inventory
Rolling back interim patch '21243471' from OH '/u00/app/oracle/product/em13cr2'

Patching component oracle.wls.libraries, 12.1.3.0.0...

Patching component oracle.wls.libraries, 12.1.3.0.0...
RollbackSession removing interim patch '21243471' from inventory
Rolling back interim patch '20758863' from OH '/u00/app/oracle/product/em13cr2'

Patching component oracle.wls.libraries, 12.1.3.0.0...

Patching component oracle.wls.libraries, 12.1.3.0.0...
RollbackSession removing interim patch '20758863' from inventory
Rolling back interim patch '19953516' from OH '/u00/app/oracle/product/em13cr2'

Patching component oracle.wls.workshop.code.completion.support, 12.1.3.0.0...

Patching component oracle.wls.workshop.code.completion.support, 12.1.3.0.0...

Patching component oracle.wls.libraries, 12.1.3.0.0...

Patching component oracle.wls.libraries, 12.1.3.0.0...

Patching component oracle.wls.clients, 12.1.3.0.0...

Patching component oracle.wls.clients, 12.1.3.0.0...
RollbackSession removing interim patch '19953516' from inventory
Rolling back interim patch '19879223' from OH '/u00/app/oracle/product/em13cr2'

Patching component oracle.wls.libraries, 12.1.3.0.0...

Patching component oracle.wls.libraries, 12.1.3.0.0...
RollbackSession removing interim patch '19879223' from inventory
Rolling back interim patch '19730967' from OH '/u00/app/oracle/product/em13cr2'

Patching component oracle.wls.libraries, 12.1.3.0.0...
RollbackSession removing interim patch '19730967' from inventory
Rolling back interim patch '18836900' from OH '/u00/app/oracle/product/em13cr2'

Patching component oracle.wls.libraries, 12.1.3.0.0...
RollbackSession removing interim patch '18836900' from inventory


OPatch back to application of the patch '23094292' after auto-rollback.


Patching component oracle.wls.workshop.code.completion.support, 12.1.3.0.0...

Patching component oracle.wls.workshop.code.completion.support, 12.1.3.0.0...

Patching component oracle.wls.shared.with.cam, 12.1.3.0.0...

Patching component oracle.wls.shared.with.cam, 12.1.3.0.0...

Patching component oracle.wls.libraries.mod, 12.1.3.0.0...

Patching component oracle.wls.libraries.mod, 12.1.3.0.0...

Patching component oracle.wls.admin.console.en, 12.1.3.0.0...

Patching component oracle.wls.admin.console.en, 12.1.3.0.0...

Patching component oracle.wls.core.app.server, 12.1.3.0.0...

Patching component oracle.wls.core.app.server, 12.1.3.0.0...

Patching component oracle.webservices.wls, 12.1.3.0.0...

Patching component oracle.webservices.wls, 12.1.3.0.0...

Patching component oracle.wls.clients, 12.1.3.0.0...

Patching component oracle.wls.clients, 12.1.3.0.0...

Patching component oracle.wls.server.shared.with.core.engine, 12.1.3.0.0...

Patching component oracle.wls.server.shared.with.core.engine, 12.1.3.0.0...

Patching component oracle.wls.libraries, 12.1.3.0.0...

Patching component oracle.wls.libraries, 12.1.3.0.0...
Patch 23094292 successfully applied.
OPatch Session completed with warnings.
Log file location: /u00/app/oracle/product/em13cr2/cfgtoollogs/opatch/23094292_Oct_17_2016_13_02_45/apply2016-10-17_13-02-42PM_1.log

OPatch completed with warnings.

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

[oracle@kestenholz 23094292]$ $ORACLE_HOME/OPatch/opatch apply

Oracle Interim Patch Installer version 13.8.0.0.0

Oracle Home : /u00/app/oracle/product/em13cr2

Central Inventory : /u00/app/oraInventory

from : /u00/app/oracle/product/em13cr2/oraInst.loc

OPatch version : 13.8.0.0.0

OUI version : 13.8.0.0.0

Log file location : /u00/app/oracle/product/em13cr2/cfgtoollogs/opatch/23094292_Oct_17_2016_13_02_45/apply2016-10-17_13-02-42PM_1.log

OPatch detects the Middleware Home as "/u00/app/oracle/product/em13cr2"

Verifying environment and performing prerequisite checks...

Conflicts/Supersets for each patch are:

Patch : 23094292

Bug Superset of 21252292

Super set bugs are:

21252292

Bug Superset of 21243471

Super set bugs are:

20613957, 19883023, 19703527

Bug Superset of 20758863

Super set bugs are:

20758863

Bug Superset of 19953516

Super set bugs are:

19953516

Bug Superset of 19879223

Super set bugs are:

19879223

Bug Superset of 19730967

Super set bugs are:

19730967

Bug Superset of 18836900

Super set bugs are:

18836900

Patches [ 21252292 21243471 20758863 19953516 19879223 19730967 18836900 ] will be rolled back.

OPatch continues with these patches: 23094292

Do you want to proceed? [y|n]

User Responded with: Y

All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home = '/u00/app/oracle/product/em13cr2')

Is the local system ready for patching? [y|n]

User Responded with: Y

Backing up files...

Applying interim patch '23094292' to OH '/u00/app/oracle/product/em13cr2'

Rolling back interim patch '21252292' from OH '/u00/app/oracle/product/em13cr2'

Patching component oracle.wls.libraries, 12.1.3.0.0...

RollbackSession removing interim patch '21252292' from inventory

Rolling back interim patch '21243471' from OH '/u00/app/oracle/product/em13cr2'

Patching component oracle.wls.libraries, 12.1.3.0.0...

RollbackSession removing interim patch '21243471' from inventory

Rolling back interim patch '20758863' from OH '/u00/app/oracle/product/em13cr2'

Patching component oracle.wls.libraries, 12.1.3.0.0...

RollbackSession removing interim patch '20758863' from inventory

Rolling back interim patch '19953516' from OH '/u00/app/oracle/product/em13cr2'

Patching component oracle.wls.workshop.code.completion.support, 12.1.3.0.0...

Patching component oracle.wls.libraries, 12.1.3.0.0...

Patching component oracle.wls.clients, 12.1.3.0.0...

RollbackSession removing interim patch '19953516' from inventory

Rolling back interim patch '19879223' from OH '/u00/app/oracle/product/em13cr2'

Patching component oracle.wls.libraries, 12.1.3.0.0...

RollbackSession removing interim patch '19879223' from inventory

Rolling back interim patch '19730967' from OH '/u00/app/oracle/product/em13cr2'

Patching component oracle.wls.libraries, 12.1.3.0.0...

RollbackSession removing interim patch '19730967' from inventory

Rolling back interim patch '18836900' from OH '/u00/app/oracle/product/em13cr2'

Patching component oracle.wls.libraries, 12.1.3.0.0...

RollbackSession removing interim patch '18836900' from inventory

OPatch back to application of the patch '23094292' after auto-rollback.

Patching component oracle.wls.workshop.code.completion.support, 12.1.3.0.0...

Patching component oracle.wls.shared.with.cam, 12.1.3.0.0...

Patching component oracle.wls.libraries.mod, 12.1.3.0.0...

Patching component oracle.wls.admin.console.en, 12.1.3.0.0...

Patching component oracle.wls.core.app.server, 12.1.3.0.0...

Patching component oracle.webservices.wls, 12.1.3.0.0...

Patching component oracle.wls.clients, 12.1.3.0.0...

Patching component oracle.wls.server.shared.with.core.engine, 12.1.3.0.0...

Patching component oracle.wls.libraries, 12.1.3.0.0...

Patch 23094292 successfully applied.

OPatch Session completed with warnings.

Log file location: /u00/app/oracle/product/em13cr2/cfgtoollogs/opatch/23094292_Oct_17_2016_13_02_45/apply2016-10-17_13-02-42PM_1.log

OPatch completed with warnings.

You can ignore the warning message, it’s because OPatch has to rollback a previous installed patch.

Start Oracle Enterprise Manager 13c

[oracle@kestenholz 23094292]$ $ORACLE_HOME/bin/emctl start oms

1	[oracle@kestenholz 23094292]$ $ORACLE_HOME/bin/emctl start oms

Summary

Like the patches which were released for Weblogic / Enterprise Manager before, the installation was very smooth. There were no problems and the EM13cR2 started well after the patch apply.

12c, EM13c Cloud Control, Oracle Linux, Oracle Weblogic, TrivadisContent