How to check which PSU is installed…if any

Oracle PSUs (Patch Set Updates) are referenced by their 5-place version number.  Unfortunately they do not change version numbers in the Oracle binaries, product banners and such though (see MOS 861152.1), so here’s how to identify which PSU your ORACLE_HOME is at…

Database Server:

$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed 
| grep -i -E 'DATABASE PSU|DATABASE PATCH SET UPDATE'
$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed 
| egrep -i 'DATABASE PSU|DATABASE PATCH SET UPDATE'

(The first command above being for Linux)

…or using the following SQL:

select comments, version, bundle_series
from sys.registry$history
where bundle_series = 'PSU'
order by action_time;

COMMENTS                       VERSION            BUNDLE_SERIES
------------------------------ ------------------ -----------------
Patchset 11.2.0.2.0            11.2.0.3           PSU
PSU 11.2.0.3.5                 11.2.0.3           PSU

The above view is populated when catbundle.sql is executed.  If the query above ends with “ORA-00904: “BUNDLE_SERIES”: invalid identifier” then no bundle patch (PSU or CPU) has been applied.

Grid Infrastructure:

$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed 
| grep -i 'GI PSU'

Cluster Ready Services:

$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed 
| grep -i 'TRACKING BUG' | grep -i 'PSU'

Enterprise Manager Agent:

$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed 
| grep -i 'ENTERPRISE MANAGER AGENT' | grep -i 'PSU'

Enterprise Manager OMS:

$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed 
| grep -i 'ENTERPRISE MANAGER OMS' | grep -i 'PSU'

WebLogic Server:

. $WLS_HOME/server/bin/setWLSEnv.sh
java weblogic.version|grep PSU
Advertisements

Installing Oracle R Distribution and Oracle R Enterprise on Exadata

ORE Installation Documentation and Requirements

Check the official Oracle R Enterprise documentation for a description of the requirements and installations steps.

http://docs.oracle.com/cd/E36939_01/doc/doc.13/e36763/intro.htm#OREAD110

Check the support matrix to ensure that the R distribution supports the version of ORE to be installed.

http://docs.oracle.com/cd/E36939_01/doc/doc.13/e36762/toc.htm#ORERN105

Install the version of ORE that is compatible with the Client / Studio that the customer is using. In this case the customer is using R 2.15.3.

Install R 2.15.3 Distribution.

http://docs.oracle.com/cd/E36939_01/doc/doc.13/e36762/toc.htm#ORERN104

http://docs.oracle.com/cd/E36939_01/doc/doc.13/e36763.pdf

Software Download

http://www.oracle.com/technetwork/database/options/advanced-analytics/r-enterprise/ore-downloads-1502823.html

Overview

  1. Install Oracle R Distribution (RPMs) on all Exadata Nodes. The Oracle R distribution is a prerequisite for installing Oracle R Enterprise.
  2. Execute the Oracle R Enterprise installation script on all Exadata Nodes. First time execution on the first node will create the database repository and packages. Subsequent execution on other nodes will copy the required libraries on that node.
  3. Install Supporting files on all Exadata Nodes.
  4. Create or grant access to the database user.

Obtaining RPMs

The method used for this installation is the RPM method. (Most Exadata systems will not have direct access to the public yum database).

There are several options for obtaining the required RPMS. The method used in this case was to download the rpms from the public YUM server using a VM that has internet access. The rpms were then transferred to Exadata.

Using the yum –downloadonly feature:

yum install yum-downloadonly

yum install <rpmfile> –y –downloadonly –downloaddir=/u01/downloads

or

yum reinstall <rpmfile> –y –downloadonly –downloaddir=/u01/downloads

Installing RPMs

Check exadata for rpms already installed (eg: rpm –qv libXau-devel )

The list below is the order in which the RPMs were installed based on their dependencies.

(The required RPMs and the versions will change over time. Check documentation).

Install the RPMs on all Exadata DB nodes:

rpm -Uvh libXau-devel-1.0.1-3.1.x86_64.rpm

rpm -Uvh libX11-devel-1.0.3-11.el5_7.1.x86_64.rpm mesa-libGL-devel-6.5.1-7.11.el5_9.x86_64.rpm xorg-x11-proto-devel-7.1-13.el5.x86_64.rpm libXdmcp-devel-1.0.1-2.1.x86_64.rpm

rpm -Uvh libtiff-3.8.2-18.el5_8.x86_64.rpm

rpm -Uvh cups-libs-1.3.7-30.el5_9.3.x86_64.rpm

rpm -Uvh cairo-1.2.4-5.el5.x86_64.rpm

rpm -Uvh atk-1.12.2-1.fc6.x86_64.rpm

rpm -Uvh hicolor-icon-theme-0.9-2.1.noarch.rpm

rpm -Uvh bitstream-vera-fonts-1.10-7.noarch.rpm

rpm -Uvh pango-1.14.9-8.0.1.el5_7.3.x86_64.rpm

rpm -Uvh gtk2-2.10.4-29.el5.x86_64.rpm

rpm -Uvh poppler-0.5.4-19.el5.x86_64.rpm

rpm -Uvh poppler-utils-0.5.4-19.el5.x86_64.rpm

rpm -Uvh paps-0.6.6-20.el5.x86_64.rpm

rpm -Uvh dbus-python-0.70-9.el5_4.x86_64.rpm

rpm -Uvh avahi-0.6.16-10.el5_6.x86_64.rpm

rpm -Uvh avahi-compat-libdns_sd-0.6.16-10.el5_6.x86_64.rpm

rpm -Uvh cups-1.3.7-30.el5_9.3.x86_64.rpm

warning: user lp does not exist – using root

warning: user lp does not exist – using root

rpm -Uvh libgfortran-4.1.2-52.el5_8.1.i386.rpm

rpm -Uvh tetex-fonts-3.0-33.15.el5_8.1.x86_64.rpm

rpm -Uvh tetex-dvips-3.0-33.15.el5_8.1.x86_64.rpm

rpm -Uvh libgfortran-4.1.2-54.el5.x86_64.rpm

rpm -Uvh libfontenc-1.0.2-2.2.el5.x86_64.rpm

rpm -Uvh libXfont-1.2.2-1.0.4.el5_7.x86_64.rpm

rpm -Uvh ttmkfdir-3.0.9-23.el5.x86_64.rpm

rpm -Uvh xorg-x11-font-utils-7.1-3.x86_64.rpm

rpm -Uvh libFS-1.0.0-3.1.x86_64.rpm

rpm -Uvh chkfontpath-1.10.1-1.1.x86_64.rpm xorg-x11-xfs-1.0.2-5.el5_6.1.x86_64.rpm

rpm -Uvh urw-fonts-2.3-6.1.1.noarch.rpm

rpm -Uvh ghostscript-fonts-5.50-13.1.1.noarch.rpm ghostscript-8.70-14.el5_8.1.x86_64.rpm

rpm -Uvh netpbm-10.35.58-10.el5.x86_64.rpm

rpm -Uvh netpbm-progs-10.35.58-10.el5.x86_64.rpm

rpm -Uvh desktop-file-utils-0.10-7.x86_64.rpm

rpm -Uvh dialog-1.0.20051107-1.2.2.x86_64.rpm

rpm -Uvh ed-0.2-39.el5_2.x86_64.rpm

rpm -Uvh tetex-3.0-33.15.el5_8.1.x86_64.rpm

rpm -Uvh tetex-latex-3.0-33.15.el5_8.1.x86_64.rpm

rpm -Uvh R-core-2.15.3-1.el5.x86_64.rpm

rpm -Uvh bzip2-devel-1.0.3-6.el5_5.x86_64.rpm

rpm -Uvh gmp-4.1.4-10.el5.x86_64.rpm

rpm -Uvh gcc-gfortran-4.1.2-54.el5.x86_64.rpm

rpm -Uvh pcre-devel-6.6-6.el5_6.1.x86_64.rpm

rpm -Uvh tcl-devel-8.4.13-6.el5.x86_64.rpm

rpm -Uvh tk-devel-8.4.13-5.el5_1.1.x86_64.rpm

rpm -Uvh zlib-devel-1.2.3-7.el5.x86_64.rpm

rpm -Uvh texinfo-4.8-14.el5.x86_64.rpm

rpm -Uvh texinfo-tex-4.8-14.el5.x86_64.rpm

rpm -Uvh R-devel-2.15.3-1.el5.x86_64.rpm

rpm -Uvh libRmath-2.15.3-1.el5.x86_64.rpm

rpm -Uvh libRmath-devel-2.15.3-1.el5.x86_64.rpm

rpm -Uvh R-2.15.3-1.el5.x86_64.rpm

At this point if we try to install the rpms below we will get that they are “Already installed”….

rpm -Uvh zlib-1.2.3-7.el5.i386.rpm

rpm -Uvh zlib-1.2.3-7.el5.x86_64.rpm

rpm -Uvh libgcc-4.1.2-54.el5.i386.rpm

rpm -Uvh libgcc-4.1.2-54.el5.i386.rpm

rpm -Uvh libstdc++-4.1.2-54.el5.x86_64.rpm

rpm -Uvh libstdc++-4.1.2-54.el5.i386.rpm

rpm -Uvh tcl-8.4.13-6.el5.x86_64.rpm

rpm -Uvh libstdc++-devel-4.1.2-54.el5.x86_64.rpm

rpm -Uvh libstdc++-devel-4.1.2-54.el5.i386.rpm

rpm -Uvh cpp-4.1.2-54.el5.x86_64.rpm

rpm -Uvh gcc-c++-4.1.2-54.el5.x86_64.rpm

rpm -Uvh gcc-4.1.2-54.el5.x86_64.rpm

rpm -Uvh mesa-libGL-6.5.1-7.11.el5_9.x86_64.rpm

Check the R software installed and the version

[root@dm01dbadm04 ~]# R

Oracle Distribution of R version 2.15.3 (–) — “Security Blanket”

Copyright (C) The R Foundation for Statistical Computing

ISBN 3-900051-07-0

Platform: x86_64-unknown-linux-gnu (64-bit)

R is free software and comes with ABSOLUTELY NO WARRANTY.

You are welcome to redistribute it under certain conditions.

Type ‘license()’ or ‘licence()’ for distribution details.

Natural language support but running in an English locale

R is a collaborative project with many contributors.

Type ‘contributors()’ for more information and

‘citation()’ on how to cite R or R packages in publications.

Type ‘demo()’ for some demos, ‘help()’ for on-line help, or

‘help.start()’ for an HTML browser interface to help.

Type ‘q()’ to quit R.

You are using Oracle’s distribution of R. Please contact

Oracle Support for any problems you encounter with this

distribution.

> q()

Save workspace image? [y/n/c]: n

Install Oracle R Enterprise

  1. Download the ORE Server and Supporting zip packages for Linux 64-bit.

http://www.oracle.com/technetwork/database/options/advanced-analytics/r-enterprise/ore-downloads-1502823.html

  1. Transfer both the Server and the Supporting files to each Exadata node and unzip.
  2. Login as the “oracle” user and set the ORACLE_SID and Oracle environment variables.
  3. Execute the “install.sh” script within the “server” directory.

[oracle@dm01dbadm02 server]$ ./install.sh

Oracle R Enterprise 1.3.1 Server Installation.

Copyright (c) 2012, 2013 Oracle and/or its affiliates. All rights reserved.

Checking R ………………. Pass

Checking R libraries ……… Pass

Checking ORACLE_HOME ……… Pass

Checking ORACLE_SID ………. Pass

Checking sqlplus …………. Pass

Checking ORACLE instance ….. Pass

Checking ORE …………….. Pass

Current configuration

R_HOME               = /usr/lib64/R

R_LIBS_USER         = /u01/app/oracle/product/11.2.0.3/dbhome_1/R/library

ORACLE_HOME         = /u01/app/oracle/product/11.2.0.3/dbhome_1

ORACLE_SID           = phppovx2

Do you wish to install ORE? [yes]

Choosing RQSYS tablespaces

PERMANENT tablespace to use for RQSYS [SYSAUX]: RQSYS

ERROR: PERMANENT tablespace RQSYS not found

PERMANENT tablespace to use for RQSYS [SYSAUX]:

TEMPORARY tablespace to use for RQSYS [TEMP]:

Tablespaces summary

PERMANENT tablespace = SYSAUX

TEMPORARY tablespace = TEMP

Installing libraries ……… Pass

Installing RQSYS data …….. Pass

Installing RQSYS code …….. Pass

Installing ORE packages …… Pass

Creating ORE script ………. Pass

NOTE: ORE has been enabled for all database users. Next, install the

supporting packages.

You may create an ORE user with the demo_user.sh script, which

automatically grants the required privileges. A complete list of

privileges is available in the script rquser.sql.

To use ORE Embedded R Execution functionality, grant the user

the RQADMIN role.

Please, consult the documentation for more information.

Done

[oracle@dm01dbadm02 server]$

  1. Change directory to the “supporting” directory from the Supporting zip file and install the supporting files

ORE CMD INSTALL ROracle_1.1-9_R_x86_64-unknown-linux-gnu.tar.gz

ORE CMD INSTALL DBI_0.2-5_R_x86_64-unknown-linux-gnu.tar.gz

ORE CMD INSTALL png_0.1-4_R_x86_64-unknown-linux-gnu.tar.gz

Check ORE installation….

ORE -e “library(ORE)”

  1. Execute the ORE server install.sh script on all other RAC nodes :

[oracle@dm01dbadm01 server]$ ./install.sh

Oracle R Enterprise 1.3.1 Server Installation.

Copyright (c) 2012, 2013 Oracle and/or its affiliates. All rights reserved.

Checking R ………………. Pass

Checking R libraries ……… Pass

Checking ORACLE_HOME ……… Pass

Checking ORACLE_SID ………. Pass

Checking sqlplus …………. Pass

Checking ORACLE instance ….. Pass

Checking ORE …………….. Pass

Current configuration

R_HOME               = /usr/lib64/R

R_LIBS_USER         = /u01/app/oracle/product/11.2.0.3/dbhome_1/R/library

ORACLE_HOME         = /u01/app/oracle/product/11.2.0.3/dbhome_1

ORACLE_SID           = osspovx1

Installing libraries ……… Pass

Installing ORE packages …… Pass

Creating ORE script ………. Pass

NOTE: ORE has been enabled for all database users. Next, install the

supporting packages.

You may create an ORE user with the demo_user.sh script, which

automatically grants the required privileges. A complete list of

privileges is available in the script rquser.sql.

To use ORE Embedded R Execution functionality, grant the user

the RQADMIN role.

Please, consult the documentation for more information.

Done

The following libraries will get copied to the location shown.

ls –l $ORACLE_HOME/lib/ore.so

ls –l $ORACLE_HOME/lib/librqe.so

ls –l $ORACLE_HOME/R/library

Install the supporting files on all other Exadata DB nodes.

[oracle@dm01dbadm01 supporting]$ ORE CMD INSTALL ROracle_1.1-9_R_x86_64-unknown-linux-gnu.tar.gz

* installing to library ‘/u01/app/oracle/product/11.2.0.3/dbhome_1/R/library’

* installing *binary* package ‘ROracle’ …

* DONE (ROracle)

[oracle@dm01dbadm01 supporting]$ ORE CMD INSTALL DBI_0.2-5_R_x86_64-unknown-linux-gnu.tar.gz

* installing to library ‘/u01/app/oracle/product/11.2.0.3/dbhome_1/R/library’

* installing *binary* package ‘DBI’ …

* DONE (DBI)

[oracle@dm01dbadm01 supporting]$ ORE CMD INSTALL png_0.1-4_R_x86_64-unknown-linux-gnu.tar.gz

* installing to library ‘/u01/app/oracle/product/11.2.0.3/dbhome_1/R/library’

* installing *binary* package ‘png’ …

* DONE (png)

[oracle@dm01dbadm01 supporting]$

  1. Grant the following privileges to existing db users that will be used for ORE.

grant RQADMIN to OSSDM;

grant create mining model to OSSDM;

Execute demo_user.sh to create new database users for ORE.

  1. Validate ORE installation….

ORE -e “library(ORE)”

See the documentation for further validation checks:

http://docs.oracle.com/cd/E36939_01/doc/doc.13/e36763/postinstall.htm#OREAD190

SAP on Oracle Database 12c now with Oracle In-Memory

On March 31, 2015 SAP has been certified to run on Oracle Database 12.1.0.2:
https://blogs.oracle.com/UPGRADE/entry/sap_is_now_certified_on

As of June 30, 2015, Oracle Database In-Memory is supported and certified for SAP environments for all SAP products based on SAP NetWeaver 7.x. on Unix/Linux, Windows and Oracle Engineered Systems platforms running Oracle Database 12c – in single instance and Oracle Oracle Real Application Clusters deployments.

Oracle Database 12c is now the only database for SAP customers based on In-Memory Technology which is fully supported for SAP BW and SAP OLTP applications.

For requirements, restrictions, and implementation details see the documents below.

Oracle 12.1.0.2 Bundle Patching

I’ve spent a few days playing with patching 12.1.0.2 with the so called “Database Patch for Engineered Systems and Database In-Memory”. Lets skip over why these not necessarily related feature sets should be bundled together into effectively a Bundle Patch.

First I was testing going from 12.1.0.2.1 to BP2 or 12.1.0.2.2. Then as soon as I’d done that of course BP3 was released.

So this is our starting position with BP1:

GI HOME:

1
2
3
4
[oracle@rac2 ~]$ /u01/app/12.1.0/grid_1/OPatch/opatch lspatches
19392604;OCW PATCH SET UPDATE : 12.1.0.2.1 (19392604)
19392590;ACFS Patch Set Update : 12.1.0.2.1 (19392590)
19189240;DATABASE BUNDLE PATCH : 12.1.0.2.1 (19189240)

DB Home:

1
2
3
[oracle@rac2 ~]$ /u01/app/oracle/product/12.1.0.2/db_1/OPatch/opatch lspatches
19392604;OCW PATCH SET UPDATE : 12.1.0.2.1 (19392604)
19189240;DATABASE BUNDLE PATCH : 12.1.0.2.1 (19189240)

Simple enough, right? BP1 and the individual patch components within BP1 give you 12.1.0.2.1. Even I can follow this.

Lets try and apply BP2 to the above. We will use opatchauto for this, and to begin with we will run an analyze:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
[root@rac2 ~]# /u01/app/12.1.0/grid_1/OPatch/opatchauto apply -analyze /tmp/BP2/19774304 -ocmrf /tmp/ocm.rsp
OPatch Automation Tool
Copyright (c) 2014, Oracle Corporation.  All rights reserved.
OPatchauto version : 12.1.0.1.5
OUI version        : 12.1.0.2.0
Running from       : /u01/app/12.1.0/grid_1
opatchauto log file: /u01/app/12.1.0/grid_1/cfgtoollogs/opatchauto/19774304/opatch_gi_2014-12-18_13-35-17_analyze.log
NOTE: opatchauto is running in ANALYZE mode. There will be no change to your system.
Parameter Validation: Successful
Grid Infrastructure home:
/u01/app/12.1.0/grid_1
RAC home(s):
/u01/app/oracle/product/12.1.0.2/db_1
Configuration Validation: Successful
Patch Location: /tmp/BP2/19774304
Grid Infrastructure Patch(es): 19392590 19392604 19649591
RAC Patch(es): 19392604 19649591
Patch Validation: Successful
Analyzing patch(es) on "/u01/app/oracle/product/12.1.0.2/db_1" ...
Patch "/tmp/BP2/19774304/19392604" analyzed on "/u01/app/oracle/product/12.1.0.2/db_1" with warning for apply.
Patch "/tmp/BP2/19774304/19649591" analyzed on "/u01/app/oracle/product/12.1.0.2/db_1" with warning for apply.
Analyzing patch(es) on "/u01/app/12.1.0/grid_1" ...
Patch "/tmp/BP2/19774304/19392590" analyzed on "/u01/app/12.1.0/grid_1" with warning for apply.
Patch "/tmp/BP2/19774304/19392604" analyzed on "/u01/app/12.1.0/grid_1" with warning for apply.
Patch "/tmp/BP2/19774304/19649591" analyzed on "/u01/app/12.1.0/grid_1" with warning for apply.
SQL changes, if any, are analyzed successfully on the following database(s): TESTRAC
Apply Summary:
opatchauto ran into some warnings during analyze (Please see log file for details):
GI Home: /u01/app/12.1.0/grid_1: 19392590, 19392604, 19649591
RAC Home: /u01/app/oracle/product/12.1.0.2/db_1: 19392604, 19649591
opatchauto completed with warnings.

Well, that does not look promising. I have no “one-off” patches in this home to cause a conflict, it should be a simple BP1->BP2 patching without any issues.

Digging into the logs we find the following:

1
2
3
4
5
6
7
8
9
10
.
.
.
[18-Dec-2014 13:37:08]       Verifying environment and performing prerequisite checks...
[18-Dec-2014 13:37:09]       Patches to apply -> [ 19392590 19392604 19649591  ]
[18-Dec-2014 13:37:09]       Identical patches to filter -> [ 19392590 19392604  ]
[18-Dec-2014 13:37:09]       The following patches are identical and are skipped:
[18-Dec-2014 13:37:09]       [ 19392590 19392604  ]
.
.

Essentially out of the 3 patches in the home at BP1 only the Database Bundle Patch 19189240 is superseded by BP2. Maybe this annoys me more than it should. I like my patches applied by BP2 to end in 2. I also don’t like the fact the analyze throws a warning about this.

Lets patch:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
[root@rac2 ~]# /u01/app/12.1.0/grid_1/OPatch/opatchauto apply /tmp/BP2/19774304 -ocmrf /tmp/ocm.rsp
OPatch Automation Tool
Copyright (c) 2014, Oracle Corporation.  All rights reserved.
OPatchauto version : 12.1.0.1.5
OUI version        : 12.1.0.2.0
Running from       : /u01/app/12.1.0/grid_1
opatchauto log file: /u01/app/12.1.0/grid_1/cfgtoollogs/opatchauto/19774304/opatch_gi_2014-12-18_13-54-03_deploy.log
Parameter Validation: Successful
Grid Infrastructure home:
/u01/app/12.1.0/grid_1
RAC home(s):
/u01/app/oracle/product/12.1.0.2/db_1
Configuration Validation: Successful
Patch Location: /tmp/BP2/19774304
Grid Infrastructure Patch(es): 19392590 19392604 19649591
RAC Patch(es): 19392604 19649591
Patch Validation: Successful
Stopping RAC (/u01/app/oracle/product/12.1.0.2/db_1) ... Successful
Following database(s) and/or service(s)  were stopped and will be restarted later during the session: testrac
Applying patch(es) to "/u01/app/oracle/product/12.1.0.2/db_1" ...
Patch "/tmp/BP2/19774304/19392604" applied to "/u01/app/oracle/product/12.1.0.2/db_1" with warning.
Patch "/tmp/BP2/19774304/19649591" applied to "/u01/app/oracle/product/12.1.0.2/db_1" with warning.
Stopping CRS ... Successful
Applying patch(es) to "/u01/app/12.1.0/grid_1" ...
Patch "/tmp/BP2/19774304/19392590" applied to "/u01/app/12.1.0/grid_1" with warning.
Patch "/tmp/BP2/19774304/19392604" applied to "/u01/app/12.1.0/grid_1" with warning.
Patch "/tmp/BP2/19774304/19649591" applied to "/u01/app/12.1.0/grid_1" with warning.
Starting CRS ... Successful
Starting RAC (/u01/app/oracle/product/12.1.0.2/db_1) ... Successful
SQL changes, if any, are applied successfully on the following database(s): TESTRAC
Apply Summary:
opatchauto ran into some warnings during patch installation (Please see log file for details):
GI Home: /u01/app/12.1.0/grid_1: 19392590, 19392604, 19649591
RAC Home: /u01/app/oracle/product/12.1.0.2/db_1: 19392604, 19649591
opatchauto completed with warnings.

I do not like to see warnings when I’m patching. The log file for the apply is similar to the analyze, identical patches skipped.

Checking where we are with GI and DB patches now:

1
2
3
4
5
6
7
8
[oracle@rac2 ~]$ /u01/app/12.1.0/grid_1/OPatch/opatch lspatches
19649591;DATABASE BUNDLE PATCH : 12.1.0.2.2 (19649591)
19392604;OCW PATCH SET UPDATE : 12.1.0.2.1 (19392604)
19392590;ACFS Patch Set Update : 12.1.0.2.1 (19392590)
[oracle@rac2 ~]$ /u01/app/oracle/product/12.1.0.2/db_1/OPatch/opatch lspatches
19649591;DATABASE BUNDLE PATCH : 12.1.0.2.2 (19649591)
19392604;OCW PATCH SET UPDATE : 12.1.0.2.1 (19392604)

The only one changed is the DATABASE BUNDLE PATCH.

The one MOS document I effectively have on “speed dial” is 888828.1 and that showed up BP3 as being available 17th December. It also had the following warning:

Before install on top of 12.1.0.2.1DBBP or 12.1.0.2.2DBBP, first rollback patch 19392604 OCW PATCH SET UPDATE : 12.1.0.2.1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
[root@rac2 ~]# /u01/app/12.1.0/grid_1/OPatch/opatchauto apply -analyze /tmp/BP3/20026159 -ocmrf /tmp/ocm.rsp
OPatch Automation Tool
Copyright (c) 2014, Oracle Corporation.  All rights reserved.
OPatchauto version : 12.1.0.1.5
OUI version        : 12.1.0.2.0
Running from       : /u01/app/12.1.0/grid_1
opatchauto log file: /u01/app/12.1.0/grid_1/cfgtoollogs/opatchauto/20026159/opatch_gi_2014-12-18_14-13-58_analyze.log
NOTE: opatchauto is running in ANALYZE mode. There will be no change to your system.
Parameter Validation: Successful
Grid Infrastructure home:
/u01/app/12.1.0/grid_1
RAC home(s):
/u01/app/oracle/product/12.1.0.2/db_1
Configuration Validation: Successful
Patch Location: /tmp/BP3/20026159
Grid Infrastructure Patch(es): 19392590 19878106 20157569
RAC Patch(es): 19878106 20157569
Patch Validation: Successful
Command "/u01/app/12.1.0/grid_1/OPatch/opatch prereq CheckConflictAgainstOH -ph /tmp/BP3/20026159/19878106 -invPtrLoc /u01/app/12.1.0/grid_1/oraInst.loc -oh /u01/app/12.1.0/grid_1" execution failed
Log file Location for the failed command: /u01/app/12.1.0/grid_1/cfgtoollogs/opatch/opatch2014-12-18_14-14-50PM_1.log
Analyzing patch(es) on "/u01/app/oracle/product/12.1.0.2/db_1" ...
Patch "/tmp/BP3/20026159/19878106" analyzed on "/u01/app/oracle/product/12.1.0.2/db_1" with warning for apply.
Patch "/tmp/BP3/20026159/20157569" analyzed on "/u01/app/oracle/product/12.1.0.2/db_1" with warning for apply.
Analyzing patch(es) on "/u01/app/12.1.0/grid_1" ...
Command "/u01/app/12.1.0/grid_1/OPatch/opatch napply -phBaseFile /tmp/OraGI12Home2_patchList -local  -invPtrLoc /u01/app/12.1.0/grid_1/oraInst.loc -oh /u01/app/12.1.0/grid_1 -silent -report -ocmrf /tmp/ocm.rsp" execution failed:
UtilSession failed: After skipping conflicting patches, there is no patch to apply.
Log file Location for the failed command: /u01/app/12.1.0/grid_1/cfgtoollogs/opatch/opatch2014-12-18_14-15-30PM_1.log
Following step(s) failed during analysis:
/u01/app/12.1.0/grid_1/OPatch/opatch prereq CheckConflictAgainstOH -ph /tmp/BP3/20026159/19878106 -invPtrLoc /u01/app/12.1.0/grid_1/oraInst.loc -oh /u01/app/12.1.0/grid_1
/u01/app/12.1.0/grid_1/OPatch/opatch napply -phBaseFile /tmp/OraGI12Home2_patchList -local  -invPtrLoc /u01/app/12.1.0/grid_1/oraInst.loc -oh /u01/app/12.1.0/grid_1 -silent -report -ocmrf /tmp/ocm.rsp
SQL changes, if any, are analyzed successfully on the following database(s): TESTRAC
Apply Summary:
opatchauto ran into some warnings during analyze (Please see log file for details):
RAC Home: /u01/app/oracle/product/12.1.0.2/db_1: 19878106, 20157569
Following patch(es) failed to be analyzed:
GI Home: /u01/app/12.1.0/grid_1: 19392590, 19878106, 20157569
opatchauto analysis reports error(s).

Looking at the log file we see patch 19392604 already in the home conflicts with patch 19878106 from BP3. 19392604 is the OCW PATCH SET UPDATE in BP1 (and BP2) while 19878106 is the Database Bundle Patch in BP3. We see the following in the log file:

1
2
Patch 19878106 has Generic Conflict with 19392604. Conflicting files are :
                             /u01/app/12.1.0/grid_1/bin/diskmon

That seems messy. It definitely annoys me that to apply BP3 I have to take additional steps of rolling back a pervious BP. I don’t recall having to do this with previous Bundle Patches, and I’ve applied a fair few of them.

I rolled the lot back with opatchauto rollback. Then applied BP3 ontop of the unpatched homes I was left with. But lets look at what BP3 on top of 12.1.0.2 gives you:

GI Home:

1
2
3
4
[oracle@rac1 ~]$ /u01/app/12.1.0/grid_1/OPatch/opatch lspatches
20157569;OCW Patch Set Update : 12.1.0.2.1 (20157569)
19878106;DATABASE BUNDLE PATCH: 12.1.0.2.3 (19878106)
19392590;ACFS Patch Set Update : 12.1.0.2.1 (19392590)

DB Home:

1
2
3
[oracle@rac1 ~]$ /u01/app/12.1.0/grid_1/OPatch/opatch lspatches
20157569;OCW Patch Set Update : 12.1.0.2.1 (20157569)
19878106;DATABASE BUNDLE PATCH: 12.1.0.2.3 (19878106)

So for BP2 we had patch 19392604 OCW PATCH SET UPDATE : 12.1.0.2.1 Now we still have a 12.1.0.2.1 OCW Patch Set Update with BP3 but it has a new patch number!

Restricted access to PL/SQL subprograms in Oracle 12c

Prior to Oracle 12c everyone can refer to a subprogram (helper program) in an other PL/SQL program unit if that user has execute privilege for the helper object or owns it. Now, in Oracle 12c the creator of the helper can determine that
which program units can refer to it, even the other users have execute privilege for the helper objects or they have the EXECUTE ANY PRIVILEGE system privilege.Even the the owner of the helper object is same as the PL/SQL
subprogram’s owner, but if the dependent object is not entitled to use the helper subprogram it can not refer to helper PL/SQL subprogram. The new feature is that the helper PL/SQL subprogram can have
an ACCESSIBLE BY (subprogram1,subprogram2, …) clause where the creator can provide the access to the subprograms listed after the ACCESSIBLE BY keywords.
In the following example HR user who created the tax function provided access of the tax function to the depts procedure (owned by HR) and to depts2 owned by CZINK user.
Note that HR issued the suitable object privilege to czink.
Let’s see the definition of the tax function and the GRANT statement:

CREATE OR REPLACE FUNCTION tax(BASE NUMBER)
RETURN NUMBER
ACCESSIBLE BY (depts,czink.depts2)
IS
S NUMBER;
BEGIN
IF BASE<4000 THEN
  S:= 0.10;
ELSIF BASE<20000 THEN
  S:=0.25;
ELSE
  S:=0.3;
END IF;
RETURN BASE*S;
END;
/
GRANT EXECUTE ON tax TO czink;

Now HR user created a procedure called depts and executed it:

CREATE OR REPLACE
PROCEDURE depts(p_deptno  employees.department_id%TYPE)
AUTHID CURRENT_USER
IS
CURSOR c_emp(c_deptno employees.department_id%TYPE) IS
SELECT e.*, 12*salary*(1+NVL(commission_pct,0)) ANN_SAL
FROM employees e WHERE department_id=c_deptno;
manager employees.last_name%TYPE;
BEGIN
FOR IN c_emp(p_deptno) LOOP
    IF r.manager_id IS NOT NULL THEN
     SELECT last_name INTO MANAGER FROM employees
     WHERE employee_id=r.manager_id;
   ELSE
    manager:='No Manager';
   END IF;
   DBMS_OUTPUT.PUT_LINE(c_emp%ROWCOUNT||'. name:='||r.last_name||' salary:'||r.salary||
   ' tax:'|| tax(r.salary) ||  ' Manager:'||manager||' '||r.manager_id||' Annual Salary:'||r.ann_sal);
END LOOP;
EXCEPTION
 WHEN OTHERS THEN
dbms_output.put_line('The error:'||DBMS_UTILITY.FORMAT_ERROR_STACK );
END;
/
exec depts(90)
anonymous block completed
1. name:=King salary:24000 tax:7200 Manager:No Manager  Annual Salary:288000
2. name:=Kochhar salary:17000 tax:4250 Manager:King 100 Annual Salary:204000
3. name:=De Haan salary:17000 tax:4250 Manager:King 100 Annual Salary:204000

However if HR wants to create a depts2 procedure with the
following code, Oracle produces an error message, because the depts2 procedure WAS NOT ENTITLED to refer to the tax function:

CREATE OR REPLACE
PROCEDURE depts2(p_deptno  employees.department_id%TYPE)
AUTHID CURRENT_USER
IS
CURSOR c_emp(c_deptno employees.department_id%TYPE) IS
SELECT e.*, 12*salary*(1+NVL(commission_pct,0)) ANN_SAL
FROM employees e WHERE department_id=c_deptno;
manager employees.last_name%TYPE;
BEGIN
FOR IN c_emp(p_deptno) LOOP
    IF r.manager_id IS NOT NULL THEN
     SELECT last_name INTO MANAGER FROM employees
     WHERE employee_id=r.manager_id;
   ELSE
    manager:='No Manager';
   END IF;
   DBMS_OUTPUT.PUT_LINE(c_emp%ROWCOUNT||'. name:='||r.last_name||' salary:'||r.salary||
   ' tax:'|| tax(r.salary) ||  ' Manager:'||manager||' '||r.manager_id||' Annual Salary:'||r.ann_sal);
END LOOP;
EXCEPTION
 WHEN OTHERS THEN
dbms_output.put_line('The error:'||DBMS_UTILITY.FORMAT_ERROR_STACK );
END;
/
Error(17,14): PLS-00904: insufficient privilege to access object TAX

Now CZINK user wants to create and execute a depts2 procedure
referring to the tax function owned by HR:

(Supposed that CZINK user has it’s own employees table)

CREATE OR REPLACE
PROCEDURE depts2(p_deptno  employees.department_id%TYPE)
AUTHID CURRENT_USER
IS
CURSOR c_emp(c_deptno employees.department_id%TYPE) IS
SELECT e.*, 12*salary*(1+NVL(commission_pct,0)) ANN_SAL
FROM employees e WHERE department_id=c_deptno;
manager employees.last_name%TYPE;
BEGIN
FOR IN c_emp(p_deptno) LOOP
IF r.manager_id IS NOT NULL THEN
SELECT last_name INTO MANAGER FROM employees
WHERE employee_id=r.manager_id;
ELSE
manager:='No Manager';
END IF;
DBMS_OUTPUT.PUT_LINE(c_emp%ROWCOUNT||'. name:='||r.last_name||' salary:'||r.salary||
' tax:'|| HR.tax(r.salary) ||  ' Manager:'||manager||' '||r.manager_id||' Annual Salary:'||r.ann_sal);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('The error:'||DBMS_UTILITY.FORMAT_ERROR_STACK );
END;
/
exec depts2(90)
anonymous block completed
1. name:=King salary:24000 tax:7200 Manager:No Manager  Annual Salary:288000
2. name:=Kochhar salary:17000 tax:4250 Manager:King 100 Annual Salary:204000
3. name:=De Haan salary:17000 tax:4250 Manager:King 100 Annual Salary:204000

Of course, if CZINK user created a procedure
(referring to HR’s tax function) with different name than depts2
then CZINK user would get the same error message.
(The DBA role was assigned to the CZINK user in my example)

Comment and benefits of using the ACCESSIBLE BY clause:

1. You can provide access to a helper PL/SQL programs only for those
PL/SQL subprograms which are really need to refer to them.
2. The restriction made for PL/SQL subprograms not for users.
3. Even if a user has a DBA role or “just” the
EXECUTE ANY PROCEDURE the user won’t be able to use the helper
PL/SQL subprogram, unless it(the helper program) allows
to access directly to the invoker program.
4. You can specify the ACCESSIBLE BY clause on package level
(not for individual members), like this:

CREATE OR REPLACE PACKAGE taxes ACCESSIBLE BY (depts,czink.depts2)
IS
FUNCTION tax1(BASE NUMBER) RETURN NUMBER;
FUNCTION tax2(BASE NUMBER) RETURN NUMBER;
END taxes;
/
CREATE OR REPLACE PACKAGE BODY taxes
IS
FUNCTION tax1(BASE NUMBER) RETURN NUMBER
IS
S NUMBER;
BEGIN
IF    BASE<4000 THEN   S:= 0.10;
ELSIF BASE<20000 THEN   S:=0.25;
ELSE  S:=0.3;
END IF;
RETURN BASE*S;
END tax1;
FUNCTION tax2(BASE NUMBER) RETURN NUMBER
IS
S NUMBER;
BEGIN
IF BASE<4000 THEN   S:= 0.10;
ELSE   S:=0.3;
END IF;
RETURN BASE*S;
END tax2;
END taxes;
/