Feed aggregator

The DBA Detective- It Takes More Than Tools to Solve Performance Problems

Chris Foot - Wed, 2016-09-28 05:19

Those who excel at tuning understand that the tuning process starts with an understanding of the problem and continues with the administrator collecting statistical information. Information collection begins at a global level and then narrows in scope until the problem is pinpointed. This article provides hints and tips that can be used to determine what architectural component is causing the problem.  

DSTv27 Timezone Patches Available for E-Business Suite 12.1

Steven Chan - Wed, 2016-09-28 02:05
Hourglass iconIf your E-Business Suite Release environment is configured to support Daylight Saving Time (DST) or international time zones, it's important to keep your timezone definition files up-to-date. They were last changed in November 2015 and released as DSTv25.

DSTv27 is now available and certified with Oracle E-Business Suite Release 12.1. The DSTv27 update includes the timezone information from the IANA tzdata 2016f.  It is cumulative: it includes all previous Oracle DST updates. 

Is Your Apps Environment Affected?

When a country or region changes DST rules or their time zone definitions, your Oracle E-Business Suite environment will require patching if:

  • Your Oracle E-Business Suite environment is located in the affected country or region OR
  • Your Oracle E-Business Suite environment is located outside the affected country or region but you conduct business or have customers or suppliers in the affected country or region

The latest DSTv27 timezone definition file is cumulative and includes all DST changes released in earlier time zone definition files. DSTv27 includes changes to the following timezones since the DSTv24 release:

  • Asia/Novosibirsk
  • America/Cayman
  • Asia/Chita
  • Asia/Tehran,
  • Haiti,
  • Palestine,
  • Azerbaijan,
  • Chile
  • America/Caracas
  • Asia/Magadan

What Patches Are Required?

In case you haven't been following our previous time zone or Daylight Saving Time (DST)-related articles, international timezone definitions for E-Business Suite environments are captured in a series of patches for the database and application tier servers in your environment. The actual scope and number of patches that need to be applied depend on whether you've applied previous DST or timezone-related patches. Some sysadmins have remarked to me that it generally takes more time to read the various timezone documents than it takes to apply these patches, but your mileage may vary.

Proactive backports of DST upgrade patches to all Oracle E-Business Suite tiers and platforms are not created and supplied by default. If you need this DST release and an appropriate patch is not currently available, raise a service request through support providing a business case with your version requirements.

The following Note identifies the various components in your E-Business Suite environment that may need DST patches:

Pending Certification 

Our certification of this DST timezone patch with Oracle E-Business Suite 12.2 is currently underway.

Oracle's Revenue Recognition rules prohibit us from discussing certification and release dates, but you're welcome to monitor or subscribe to this blog. I'll post updates here as soon as soon as they're available.   


Categories: APPS Blogs

Links for 2016-09-27 [del.icio.us]

Categories: DBA Blogs

about materialized view log mlog issues

Tom Kyte - Tue, 2016-09-27 18:26
Hi team, I want to get some field values from some tables.If field values of tables changed. it must be captured.don't use trigger? for example: table?A fields:a1(primary key), a2,a3 table: B fields:b1(primary key),b2,b3 If table A field ...
Categories: DBA Blogs

Help with ANSI outer join

Tom Kyte - Tue, 2016-09-27 18:26
I am not getting same records when converting from oracle outer join (+) to ANSI outer join. Could you please take a look and check what I am missing. How do I write ANSI outer join to return same resultset for below example? <code> -- Cre...
Categories: DBA Blogs

Unknown session

Tom Kyte - Tue, 2016-09-27 18:26
Hi Tom, One of my oracle procedure is not giving desire performance. From last few days I am monitoring sessions for that particular procedure and I am getting such sessions those are not written inside code. I just wanted to know does the oracle ...
Categories: DBA Blogs

Grant privilege to role with admin option Vs Grant role to user with admin option?

Tom Kyte - Tue, 2016-09-27 18:26
Hello Tom, I am little confused between following two CASES, though I know the result but I am not very convinced with the reason behind it. (Say) A role ROLE1 is created along with USER1 and USER2 by dba <i><code>connect /as sysdba create...
Categories: DBA Blogs

FETCHING NULL DATE IS DATE IS NOT MATCHING IN WHERE CONDITION

Tom Kyte - Tue, 2016-09-27 18:26
Sir, There is records month wise like DATE1 ----------- 10/2015 11/2015 03/2016 06/2016 09/2016 I want to get result like this from start date to end date start date := 06/2015 end date := 12/2016 note: when condition would n...
Categories: DBA Blogs

The Biggest OpenWorld 2016 Takeaway

Linda Fishman Hoyle - Tue, 2016-09-27 17:14

A Guest Post by Oracle Vice President Natalia Rachelson, Cloud Applications, Outbound Product Management (pictured left)

What an amazing conference, full of crazy energy and excitement fueled by music from Gwen Stefani and Sting. Ha! Try to top that!

All the billboards and buildings surrounding Moscone Conference Center were covered in Oracle Cloud posters with quotes from Oracle Cloud customers. For Oracle SaaS, the saying “proof is in the pudding” could not ring more true this year as almost every apps session included customers speaking on behalf of our solutions. “Yes and Yes” to more customer advocates!

As Oracle EVP Steve Miranda told one audience, “There's nothing I could do or say that represents us as well as you, our customers.” Here’s a sampling of the invaluable advice that happy Oracle SaaS customers dispensed to their colleagues during the conference:

Implementation

  • Spend time upfront on business process simplification and normalization (SaaS = No Customizations)
  • Leverage solution configurations to tailor the app to meet your specific business needs
  • For truly unique requirements, consider extending the apps with PaaS
  • Remember that out-of-the-box solutions have industry best practices built in, so use them; they will make your lives easier

Security

  • Trust Oracle. Everyone who grapples with various security questions ends up with the same conclusion—Oracle can handle security a lot better than we can. Leave it to the expert.
  • There’s the prospect of sleeping better. Compared to on premises, Oracle Cloud is a lot more secure at every single layer from hardware to applications delivery with data encrypted in transit and at rest.

Note: A typical $1B enterprise spends $30M on IT (all of IT including security), Oracle invested $7B across the entire red stack and inclusive of cloud operations on R&D and security.

Innovation

  • Get ready to consume innovation, and lots of it, with every single release. Releases come at least twice a year.
  • Change management is king. Users must be ready to consume new stuff. Once they get the hang of it, they will keep asking for more.

Integrations

  • It’s OK to be a straddler. If you’re not ready to go full cloud, no problem keeping some applications on premises for a while.

Note: Customers can meet any integration challenge using Oracle’s range of solutions from pre-built adapters and accelerators to Integration Cloud Services to fully integrated cloud solutions.

The tipping point

  • It’s not just about cost. While cost savings are an indisputable value, the even bigger value is the ability to be modern, nimble, and agile; to dazzle employees with new shiny work tools; to let them spend their creative energy on what matters to your business, instead of reconciling multiple Excels and manual data uploads and downloads.

Note: And, how about an adaptive intelligence system that uses reams of data and your own business intelligence to recommend your employees next best action/step/offer that they should undertake as if they have a brainstorming partner right there with them. How about that?

Now this

The possibilities with cloud and Oracle SaaS are truly endless. Encourage your prospects and customers to come join our party and let the OpenWorld 2016 music never stop.

Microsoft Edge Certified with EBS 12.1 and 12.2

Steven Chan - Tue, 2016-09-27 13:20

Microsoft Edge Logo I am very pleased to announce that Microsoft Edge is certified as a new browser for Oracle E-Business Suite 12.1 and 12.2.  Edge can be used to access EBS Self-Service Web Applications (SSWA) -- i.e. the web-based HTML products in Oracle E-Business Suite that use OA Framework (OAF).

EBS Prerequisites for Edge

No Windows or desktop patches are needed for enabling Edge compatibility with E-Business Suite 12.1 or 12.2.

A small number of E-Business Suite server-side patches are required.  See:

Can EBS Forms run in Edge?

No. Microsoft Edge does not support plug-ins.  For details, see:

The Java JRE is a mandatory prerequisite for Forms-based products in E-Business Suite. Since Edge does not support plug-ins, the Java JRE plug-in does not work with this browser. 

E-Business Suite products that use Oracle Forms can run on Internet Explorer 11 and Firefox ESR on Windows desktops, and Safari on Mac desktop clients.

What about Java Web Start?

We are testing prototypes with EBS Forms-based products using Java Web Start.  Once this work is completed, it is likely that it will be possible to run Forms-based products even when using browsers such as Edge.  See:

This work is going well, and we are currently working on security and polishing user interface issues. I'll post updates on this blog as soon as they're available.

Does Edge work with EBS 11i or 12.0?

No. You can download existing patches for EBS 11i and 12.0, but these EBS releases are now in Sustaining Support. We do not produce new certifications for products in Sustaining Support. 

Your end-users should continue to use IE 11, Firefox ESR, or Safari if you are running EBS 11i or 12.0.

Related Articles

The preceding is intended to outline our general product direction.  It is intended for information purposes only, and may not be incorporated into any contract.   It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decision.  The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.

Categories: APPS Blogs

BPM Worklist API 12.2.1.1 and Custom ADF 12.2.1.1 Application

Andrejus Baranovski - Tue, 2016-09-27 12:57
I have updated my sample app with BPM API usage in ADF application to 12.2.1.1. Originally this was developed with ADF/BPM 11.1.1.7 - Dynamic ADF Buttons Solution for Oracle BPM Outcomes. There are several changes in BPM libraries usage. I will describe it all step by step.

Download sample application - adfbpm12211.zip. This archive contains both BPM and ADF app. BPM process implements two roles - request holiday and approve holiday:


Main goal of such use case - we don't want to use out of the box Oracle Worklist app, but prefer to develop our own business logic and manage BPM process from custom ADF app through BPM API. It is important to initialize Workflow context one time during login, this can be heavy operation and we should not call it each time when BPM API is invoked:


I'm using authenticateOnBehalfOf method. This allows to use admin user as a proxy for business user connection. Once Workflow context is established, we can get BPM context to use it for BPM API calls, all this is done during login into ADF app:


Assigned tasks are fetched through Workflow context:


We can initiate new task through BPM API in our custom ADF app:


There is a way to generate buttons to control task actions dynamically. Buttons can be generated on top of task outcomes list obtained from BPM API:


Task action can be executed with payload info, this allows to pass correct info to the next step in the process:


Let's see how it works. User can start new BPM task from ADF:


When task is submitted to approval, manager is assigned task to approve or reject holiday request. Buttons are generated dynamically based on task outcomes:


To double check executed flow from BPM API in ADF we can review it in EM control:


ADF application must import BPM API JARs to be able to compile Java code. In ADF 12.2.1.1 it is enough to import BPM Services JARs:


There is no need to package these JARs into application archive, these should be referenced for compile time only:

Connecting your PostgreSQL instance to an Oracle database – Debian version

Yann Neuhaus - Tue, 2016-09-27 08:03

Some time ago I blogged about attaching your PostgreSQL instance to an Oracle database by using the oracle_fdw foreign data wrapper. This resulted in a comment which is the reason for this post: Doing the same with a Debian system where you can not use the rpm versions of the Oracle Instant Client (at least not directly). Lets go …

What I did to start with is to download the Debian 8 netinstall ISO and started from there with a minimal installation (see the end of this post for the screen shots of the installation if you are not sure on how to do it).

As I will compile PostgreSQL from source I’ll need to install the required packages:

root@debianpg:~ apt-get install libldap2-dev libpython-dev libreadline-dev libssl-dev bison flex libghc-zlib-dev libcrypto++-dev libxml2-dev libxslt1-dev tcl tclcl-dev bzip2 wget screen ksh git unzip

Create the directory structure for my PostgreSQL binaries and instance:

root@debianpg:~ mkdir -p /u01/app/postgres/product
root@debianpg:~ chown -R postgres:postgres /u01/app 
root@debianpg:~ mkdir -p /u02/pgdata
root@debianpg:~ mkdir -p /u03/pgdata
root@debianpg:~ chown -R postgres:postgres /u0*/pgdata 

Compile and install PostgreSQL from source:

postgres@debianpg:~$ PGHOME=/u01/app/postgres/product/95/db_4
postgres@debianpg:~$ SEGSIZE=2
postgres@debianpg:~$ BLOCKSIZE=8
postgres@debianpg:~$ WALSEGSIZE=16
postgres@debianpg:~$ wget https://ftp.postgresql.org/pub/source/v9.5.4/postgresql-9.5.4.tar.bz2
postgres@debianpg:~$ tar -axf postgresql-9.5.4.tar.bz2
postgres@debianpg:~$ cd postgresql-9.5.4/
./configure --prefix=${PGHOME} \
            --exec-prefix=${PGHOME} \
            --bindir=${PGHOME}/bin \
            --libdir=${PGHOME}/lib \
            --sysconfdir=${PGHOME}/etc \
            --includedir=${PGHOME}/include \
            --datarootdir=${PGHOME}/share \
            --datadir=${PGHOME}/share \
            --with-pgport=5432 \
            --with-perl \
            --with-python \
            --with-openssl \
            --with-ldap \
            --with-libxml \
            --with-libxslt \
            --with-segsize=${SEGSIZE} \
            --with-blocksize=${BLOCKSIZE} \
            --with-wal-segsize=${WALSEGSIZE}  \
            --with-extra-version=" dbi services build"
postgres@debianpg:~$ make world
postgres@debianpg:~$ make install
postgres@debianpg:~$ cd contrib
postgres@debianpg:~$ make install
postgres@debianpg:~$ cd ../..
postgres@debianpg:~$ rm -rf postgres*

Initialize a new cluster:

postgres@debianpg:~$ /u01/app/postgres/product/95/db_4/bin/initdb -D /u02/pgdata/PG1 -X /u02/pgdata/PG1 --locale=en_US.UTF-8
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /u02/pgdata/PG1 ... ok
fixing permissions on existing directory /u02/pgdata/PG1 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in /u02/pgdata/PG1/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /u01/app/postgres/product/95/db_4/bin/pg_ctl -D /u02/pgdata/PG1 -l logfile start

Adjust the logging_collector parameter and startup the instance:

postgres@debianpg:~$ sed -i 's/#logging_collector = off/logging_collector = on/g' /u02/pgdata/PG1/postgresql.conf
postgres@debianpg:~$ mkdir /u02/pgdata/PG1/pg_log
postgres@debianpg:~$ /u01/app/postgres/product/95/db_4/bin/pg_ctl start -D /u02/pgdata/PG1/
postgres@debianpg:~$ /u01/app/postgres/product/95/db_4/bin/psql
psql (9.5.4 dbi services build)
Type "help" for help.

postgres= select version();
                                                   version                                                   
-------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.5.4 dbi services build on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
(1 row)

postgres=

Download the Oracle Instant Client zip file from here. You’ll need these:

  • instantclient-basic-linux.x64-12.1.0.2.0.zip
  • instantclient-sqlplus-linux.x64-12.1.0.2.0.zip
  • instantclient-sdk-linux.x64-12.1.0.2.0.zip

Extract to a location which fits your needs:

postgres@debianpg:~$ cd /u01/app/
postgres@debianpg:/u01/app$ unzip /home/postgres/instantclient-basic-linux.x64-12.1.0.2.0.zip
postgres@debianpg:/u01/app$ unzip /home/postgres/instantclient-sqlplus-linux.x64-12.1.0.2.0.zip
postgres@debianpg:/u01/app$ unzip /home/postgres/instantclient-sdk-linux.x64-12.1.0.2.0.zip
postgres@debianpg:/u01/app$ ls -l
total 8
drwxr-xr-x 3 postgres postgres 4096 Sep 27 12:04 instantclient_12_1
drwxr-xr-x 4 postgres postgres 4096 Sep 27 10:57 postgres

Do a connection test with sqlplus to be sure the instant client is working in general:

postgres@debianpg:/u01/app$ export ORACLE_HOME=/u01/app/instantclient_12_1
postgres@debianpg:/u01/app$ export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/u01/app/instantclient_12_1
postgres@debianpg:/u01/app$ export PATH=$PATH:$ORACLE_HOME
postgres@debianpg:/u01/app$ which sqlplus
/u01/app/instantclient_12_1/sqlplus
postgres@debianpg:/u01/app$ sqlplus sh/sh@192.168.22.242:1521/PROD
sqlplus: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

Ups, easy to fix:

root@debianpg:~ apt-get install libaio1

Again:

postgres@debianpg:/u01/app$ sqlplus sh/sh@192.168.22.242:1521/PROD.local

SQL*Plus: Release 12.1.0.2.0 Production on Tue Sep 27 12:12:44 2016

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

Last Successful login time: Tue Sep 27 2016 12:09:05 +02:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> 

Perfect. Connections are working to the Oracle instance. Continue with the oracle_fdw setup:

postgres@debianpg:~$ wget https://github.com/laurenz/oracle_fdw/archive/master.zip
postgres@debianpg:~$ unzip master.zip 
postgres@debianpg:~$ cd oracle_fdw-master/
postgres@debianpg:~/oracle_fdw-master$ export PATH=/u01/app/postgres/product/95/db_4/bin/:$PATH
postgres@debianpg:~/oracle_fdw-master$ which pg_config 
/u01/app/postgres/product/95/db_4/bin//pg_config
postgres@debianpg:~/oracle_fdw-master$ make
...
/usr/bin/ld: cannot find -lclntsh
collect2: error: ld returned 1 exit status
/u01/app/postgres/product/95/db_4/lib/pgxs/src/makefiles/../../src/Makefile.shlib:311: recipe for target 'oracle_fdw.so' failed
make: *** [oracle_fdw.so] Error 1

This one was unexpected. After some digging this resolves the issue:

postgres@debianpg:/u01/app/instantclient_12_1$ cd /u01/app/instantclient_12_1
postgres@debianpg:/u01/app/instantclient_12_1$ ln -s libclntsh.so.12.1 libclntsh.so

Not sure if I missed something or this is a bug (you can follow the issue here).

Once the link is there you’ll be able to “make” and to “make install”. This is the result:

postgres@debianpg:~/oracle_fdw-master$ make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -I/u01/app/instantclient_12_1/sdk/include -I/u01/app/instantclient_12_1/oci/include -I/u01/app/instantclient_12_1/rdbms/public -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/u01/app/postgres/product/95/db_4/include/server -I/u01/app/postgres/product/95/db_4/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o oracle_fdw.o oracle_fdw.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -I/u01/app/instantclient_12_1/sdk/include -I/u01/app/instantclient_12_1/oci/include -I/u01/app/instantclient_12_1/rdbms/public -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/u01/app/postgres/product/95/db_4/include/server -I/u01/app/postgres/product/95/db_4/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o oracle_utils.o oracle_utils.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -I/u01/app/instantclient_12_1/sdk/include -I/u01/app/instantclient_12_1/oci/include -I/u01/app/instantclient_12_1/rdbms/public -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/u01/app/postgres/product/95/db_4/include/server -I/u01/app/postgres/product/95/db_4/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o oracle_gis.o oracle_gis.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -shared -o oracle_fdw.so oracle_fdw.o oracle_utils.o oracle_gis.o -L/u01/app/postgres/product/95/db_4/lib -Wl,--as-needed -Wl,-rpath,'/u01/app/postgres/product/95/db_4/lib',--enable-new-dtags  -L/u01/app/instantclient_12_1 -L/u01/app/instantclient_12_1/bin -L/u01/app/instantclient_12_1/lib -L/u01/app/instantclient_12_1/sdk/include -lclntsh -L/usr/lib/oracle/12.1/client/lib -L/usr/lib/oracle/12.1/client64/lib -L/usr/lib/oracle/11.2/client/lib -L/usr/lib/oracle/11.2/client64/lib -L/usr/lib/oracle/11.1/client/lib -L/usr/lib/oracle/11.1/client64/lib -L/usr/lib/oracle/10.2.0.5/client/lib -L/usr/lib/oracle/10.2.0.5/client64/lib -L/usr/lib/oracle/10.2.0.4/client/lib -L/usr/lib/oracle/10.2.0.4/client64/lib -L/usr/lib/oracle/10.2.0.3/client/lib -L/usr/lib/oracle/10.2.0.3/client64/lib 
postgres@debianpg:~/oracle_fdw-master$ make install
/bin/mkdir -p '/u01/app/postgres/product/95/db_4/lib'
/bin/mkdir -p '/u01/app/postgres/product/95/db_4/share/extension'
/bin/mkdir -p '/u01/app/postgres/product/95/db_4/share/extension'
/bin/mkdir -p '/u01/app/postgres/product/95/db_4/share/doc/extension'
/usr/bin/install -c -m 755  oracle_fdw.so '/u01/app/postgres/product/95/db_4/lib/oracle_fdw.so'
/usr/bin/install -c -m 644 .//oracle_fdw.control '/u01/app/postgres/product/95/db_4/share/extension/'
/usr/bin/install -c -m 644 .//oracle_fdw--1.1.sql .//oracle_fdw--1.0--1.1.sql  '/u01/app/postgres/product/95/db_4/share/extension/'
/usr/bin/install -c -m 644 .//README.oracle_fdw '/u01/app/postgres/product/95/db_4/share/doc/extension/'

Remember that the PostgreSQL instance needs to find the Oracle libraries, so set the environment before restarting PostgreSQL:

postgres@debianpg:~$ echo $ORACLE_HOME
/u01/app/instantclient_12_1
postgres@debianpg:~$ echo $LD_LIBRARY_PATH
:/u01/app/instantclient_12_1:/u01/app/instantclient_12_1/sdk/include/
postgres@debianpg:~$ pg_ctl -D /u02/pgdata/PG1/ restart -m fast
postgres@debianpg:~$ psql
psql (9.5.4 dbi services build)
Type "help" for help.

postgres= create extension oracle_fdw;
CREATE EXTENSION
postgres= \dx
                        List of installed extensions
    Name    | Version |   Schema   |              Description               
------------+---------+------------+----------------------------------------
 oracle_fdw | 1.1     | public     | foreign data wrapper for Oracle access
 plpgsql    | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

All fine. Lets get the foreign data:

postgres= create schema oracle;
CREATE SCHEMA
postgres= create server oracle foreign data wrapper oracle_fdw options (dbserver '//192.168.22.242/PROD.local' );
CREATE SERVER
postgres= create user mapping for postgres server oracle options (user 'sh', password 'sh');
CREATE USER MAPPING
postgres= import foreign schema "SH" from server oracle into oracle;
IMPORT FOREIGN SCHEMA
postgres= set search_path='oracle';
SET
postgres= \d
                       List of relations
 Schema |            Name            |     Type      |  Owner   
--------+----------------------------+---------------+----------
 oracle | cal_month_sales_mv         | foreign table | postgres
 oracle | channels                   | foreign table | postgres
 oracle | costs                      | foreign table | postgres
 oracle | countries                  | foreign table | postgres
 oracle | currency                   | foreign table | postgres
 oracle | customers                  | foreign table | postgres
 oracle | dimension_exceptions       | foreign table | postgres
 oracle | fweek_pscat_sales_mv       | foreign table | postgres
 oracle | products                   | foreign table | postgres
 oracle | profits                    | foreign table | postgres
 oracle | promotions                 | foreign table | postgres
 oracle | sales                      | foreign table | postgres
 oracle | sales_transactions_ext     | foreign table | postgres
 oracle | supplementary_demographics | foreign table | postgres
 oracle | times                      | foreign table | postgres
(15 rows)

postgres= select count(*) from countries;
 count 
-------
    23
(1 row)

Perfect, works. Hope this helps.

Debian 8 installation screen shots:

Selection_014
Selection_015
Selection_016
Selection_017
Selection_018
Selection_020
Selection_021
Selection_022
Selection_023
Selection_024
Selection_025
Selection_026
Selection_027
Selection_028
Selection_029
Selection_030
Selection_031
Selection_032
Selection_033
Selection_034
Selection_036
Selection_037
Selection_038
Selection_039
Selection_040
Selection_041
Selection_042
Selection_043
Selection_044
Selection_045
Selection_046
Selection_047
Selection_048

 

Cet article Connecting your PostgreSQL instance to an Oracle database – Debian version est apparu en premier sur Blog dbi services.

#OOW16 Announcements Round Up

At Oracle OpenWorld 2016, Oracle executives, partners, and leading industry visionaries have provided insight into Oracle Cloud, customer success in the cloud, and the next wave of opportunity in the...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Connection between Mysql and SQL Developer Datamodeler

Tom Kyte - Tue, 2016-09-27 00:06
Hi Tom, Im trying to connect Oracle SQL Developer Datamodeler with Mysql but nothing work. I can connect Oracle SQL Developer with Mysql with an J Connector, but when I do the same steps on SQL Datamodeler (add an extension in the Third Party JDBC) ...
Categories: DBA Blogs

How to copy a DBMS_XMLDOM.domnode between DBMS_XMLDOM.domdocument objects

Tom Kyte - Tue, 2016-09-27 00:06
I'm having trouble using the DBMS_XMLDOM package. I can hardly find more online than the API (which isn't that great) and a few "here's how you build an XML document" introductions, with no tutorials on more advanced uses. I am trying to write a file...
Categories: DBA Blogs

Database Queries Slow After DB Re-Import

Tom Kyte - Tue, 2016-09-27 00:06
After initial creation of user and import of db, queries were fast. Used following commands: " CREATE USER TESTUSR IDENTIFIED BY TESTUSR default tablespace TESTTAB quota unlimited on TESTTAB ACCOUNT UNLOCK; GRANT CREATE SESSION TO TESTUSR; GRAN...
Categories: DBA Blogs

RMAN using dbms_pipe

Tom Kyte - Tue, 2016-09-27 00:06
Hello I wrote a PL/SQL package which allows me to control RMAN out of SQL*Plus using the RMAN syntax. To implement it I used the dbms_pipe/dbms_scheduler functionality which starts the external rman process on OS level and call the rman binary ...
Categories: DBA Blogs

Direct path read temp wait event issue

Tom Kyte - Tue, 2016-09-27 00:06
Hi Tom, I have series of job which are configured daily & every day its been completed within expected time 7 mins. But last 4 days back this job is behaving like any thing its taking more than 2 hours. Its going for more sort operations & waiting...
Categories: DBA Blogs

select from table type

Tom Kyte - Tue, 2016-09-27 00:06
I have created a type below is the code of the type <code>create or replace type tt_name as table of varchar2(250);</code> Now I want to perform select on this type in a plsql code select listagg(column_name,';') within group(order by colum...
Categories: DBA Blogs

New Capabilities for the Status Meter Component in ADF

Shay Shmeltzer - Mon, 2016-09-26 18:58

While building a demo of new Oracle ADF features for my OOW session, I came across some nice new functionality in the dvt status meter gauge component (dvt:statusMeterGauge), specifically the round one which has become quite popular in various Oracle Alta UIs.

Turns out you can turn the dial and cut it so it shows just parts of a circle.

By adding thresholds you can use it as a replacement for the dial gauge.

Here is how it can look: 

status meter

Here is the code used:

                            <dvt:statusMeterGauge orientation="circular" inlineStyle="width:150px; height:100px;"

                                                  id="statusMeterGauge1" maximum="25000"

                                                  value="#{bindings.Salary.inputValue}" minimum="0" startAngle="180" angleExtent="180"

                                                  title="Salary">

                                <dvt:gaugeMetricLabel rendered="true" id="gml1"/>

                                <dvt:gaugeThreshold id="gt1" maximum="4000" color="red"/>

                                <dvt:gaugeThreshold id="gt2" maximum="6000" color="yellow"/>

                                <dvt:gaugeThreshold id="gt3" maximum="20000" color="green"/>

                            </dvt:statusMeterGauge>

Categories: Development

Pages

Subscribe to Oracle FAQ aggregator