Feed aggregator

New OA Framework 12.2.4 Update 14 Now Available

Steven Chan - Wed, 2017-01-11 02:03

Web-based content in Oracle E-Business Suite 12 runs on the Oracle Application Framework (OAF or "OA Framework") user interface libraries and infrastructure.   Since the release of Oracle E-Business Suite 12.2 in 2013, we have released several updates to Oracle Application Framework to fix performance, security, and stability issues. 

These updates are provided in cumulative Release Update Packs, and cumulative Bundle Patches that can be applied on top of the Release Update Pack. "Cumulative" means that the latest RUP or Bundle Patch contains everything released earlier.

The latest OAF update for EBS 12.2.4 is now available:

Screenshot EBS 12.2.4 OAF Bundle Patch 14

Where is the documentation for this update?

Instructions for installing this OAF Release Update Pack are here:

Who should apply this patch?

All EBS 12.2.4 users should apply this patch.  Future OAF patches for EBS 12.2.4 will require this patch as a prerequisite. 

What's new in this update?

Fixes are included for following critical issues:

  • Line separator for each row in table is missing in Workflow notification e-mail when viewed in Outlook mail client
  • Application menu entry in the Preferences sidebar was seen multiple times
  • Favorite created to open in a new window is opening in the same window from Framework Simplified Home page

This is a cumulative patch for EBS 12.2.4.  It includes updates previously released as Bundle 13 and patch 24618945.

This Bundle Patch requires the R12.ATG_PF.C.Delta.4 Release Update Pack as a mandatory prerequisite.

Related Articles


Categories: APPS Blogs

February 1: Oracle Moves to the HR Cloud—Oracle HCM Cloud Customer Forum

Linda Fishman Hoyle - Tue, 2017-01-10 16:05

Join us for an Oracle HCM Cloud Customer Forum on Wednesday, February 1, 2017, at 9:00 a.m. PT.

Anje Dodson, Oracle HR vice president of talent, technology and operations (pictured left), will explain how and why Oracle has moved 126,000 employees from Oracle E-Business Suite on premises to Oracle HCM Cloud.

Oracle’s HR transformation is taking place in a phased approach:

  • In 2015 Oracle went live with recruiting, talent review, learn, and compensation in the HR Cloud.
  • In March of 2016 Oracle went live with Oracle Global HR Cloud.
  • As of October 2016, the Oracle single global instance is now in the HR Cloud, with more to come.

During this Customer Forum call, Oracle's Linda Fishman will interview Ms. Dodson to discuss what business drivers were behind the move to the cloud and why Oracle chose to implement a phased approach. She also will talk about the company's expectations and benefits for its new modern HR system.

Register now to attend the live forum and learn more about Oracle’s transition from E-Business Suite to HCM Cloud.

January 18: Texas Instruments―Oracle Sales Cloud Customer Forum

Linda Fishman Hoyle - Tue, 2017-01-10 14:37
Join us for an Oracle Sales Cloud Customer Forum call on Wednesday, January 18, 2017.

Syndy Lynch, Director of Sales Operations at Texas Instruments, will discuss the company’s journey from Oracle CRM On Demand to Oracle Sales Cloud. You will hear how Oracle Sales Cloud has improved sales productivity and account management.

Register now to attend the live forum on Wednesday, January 18, 2017, at 8:00 a.m. PT.

Dataguard Oracle 12.2 : Support for Multiple Observers

Yann Neuhaus - Tue, 2017-01-10 11:53

With Oracle 12cR2 it is now possible to configure multiple observers within a single Oracle Data Guard broker configuration.
Multiple observers provide an immediate benefit for High Availability. If one observer fails, there are additional observers that can continue to monitor the status of the configuration.
In this blog we are going to talk about this new feature.

Our configuration have 3 servers:
primaserver.localdomain with db_unique_name ORCL_SITE
standserver1.localdomain with db_unique_name ORCL_SITE1
standserver2.localdomain with db_unique_name ORCL_SITE2

With Oracle 12cR2 you can register up to three observers to monitor a single Data Guard broker configuration. Each observer is identified by a name that you supply when you issue the START OBSERVER command

Let’s start with a configuration where the Fast Start Failover is disabled

DGMGRL> show configuration;
Configuration - ORCL_DR
Protection Mode: MaxProtection
Members:
ORCL_SITE - Primary database
ORCL_SITE1 - Physical standby database
ORCL_SITE2 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 11 seconds ago)
DGMGRL>

And let’s start an observer on each server

DGMGRL> connect sys/root@orcl_site
Connected to "ORCL_SITE"
Connected as SYSDBA.DGMGRL> start observer prima_observer
[W000 01/05 10:46:03.89] FSFO target standby is (empty)


DGMGRL> connect sys/root@ORCL_SITE1
Connected to "ORCL_SITE1"
Connected as SYSDBA.
DGMGRL> start observer standserver1_observer
[W000 01/05 10:47:19.22] FSFO target standby is (empty)


DGMGRL> connect sys/root@orcl_site2
Connected to "ORCL_SITE2"
Connected as SYSDBA.
DGMGRL> start observer standserver2_observer
[W000 01/05 10:48:25.86] FSFO target standby is (empty)

When fast-start failover is disabled the observer does not need to coordinate fast-start failover, so all observers are equivalent and have the same functionality as shown below

DGMGRL> show observer;
Configuration - ORCL_DR
Fast-Start Failover: DISABLED
Observer "prima_observer"
Host Name: primaserver.localdomain
Last Ping to Primary: 17 seconds ago
Observer "standserver1_observer"
Host Name: standserver1.localdomain
Last Ping to Primary: 2 seconds ago
Observer "standserver2_observer"
Host Name: standserver2.localdomain
Last Ping to Primary: 25 seconds ago
DGMGRL>

Now let’s enable the Fast Start Failover

DGMGRL> enable FAST_START FAILOVER
Enabled.


DGMGRL> show configuration
Configuration - ORCL_DR
Protection Mode: MaxProtection
Members:
ORCL_SITE - Primary database
ORCL_SITE1 - (*) Physical standby database
ORCL_SITE2 - Physical standby database
Fast-Start Failover: ENABLED
Configuration Status:
SUCCESS (status updated 43 seconds ago)
DGMGRL>

Now that FSFO is enabled, we have a Master observer and Backup observers. The master observer works in the same manner that a single observer worked prior to the advent of multiple observers in Oracle Database 12c Release 2 (12.2.0.1).
Only the master observer can coordinate fast-start failover with Data Guard broker. All other registered observers are considered to be backup observers. The master observer is randomly chosen by the primary and the standby.

DGMGRL> show observer;
Configuration - ORCL_DR
Primary: ORCL_SITE
Target: ORCL_SITE1
Observer "prima_observer" - Master
Host Name: primaserver.localdomain
Last Ping to Primary: 3 seconds ago
Last Ping to Target: 0 seconds ago
Observer "standserver1_observer" - Backup
Host Name: standserver1.localdomain
Last Ping to Primary: 2 seconds ago
Last Ping to Target: 3 seconds ago
Observer "standserver2_observer" - Backup
Host Name: standserver2.localdomain
Last Ping to Primary: 2 seconds ago
Last Ping to Target: 2 seconds ago
DGMGRL>

If we try to stop the master observer, we will get an error

DGMGRL> stop observer prima_observer
Error: ORA-16878: master observer cannot be stopped
Failed.
DGMGRL>

It means that if for one reason we want to stop an observer that is the master one, we have to promote a backup observer as the new master. The command SET MASTEROBSERVER is used to manually change the master observer

DGMGRL> SET MASTEROBSERVER to standserver2_observer
Sent the proposed master observer to the data guard broker configuration.
Please run SHOW OBSERVER to see if master observer switch actually happens.
DGMGRL>

We can see that now the new master is standserver2_observer

DGMGRL> show observer;
Configuration - ORCL_DR
Primary: ORCL_SITE
Target: ORCL_SITE1
Observer "standserver2_observer" - Master
Host Name: standserver2.localdomain
Last Ping to Primary: 0 seconds ago
Last Ping to Target: 1 second ago
Observer "prima_observer" - Backup
Host Name: primaserver.localdomain
Last Ping to Primary: 0 seconds ago
Last Ping to Target: 2 seconds ago
Observer "standserver1_observer" - Backup
Host Name: standserver1.localdomain
Last Ping to Primary: 1 second ago
Last Ping to Target: 1 second ago
DGMGRL>

Now if we crash the server hosting the master observer (i.e. standserver2.localdomain), one of the backup observers will automatically be the new master.

DGMGRL> show observer;
Configuration - ORCL_DR
Primary: ORCL_SITE
Target: ORCL_SITE1
Observer "prima_observer" - Master
Host Name: primaserver.localdomain
Last Ping to Primary: 2 seconds ago
Last Ping to Target: 1 second ago
Observer "standserver1_observer" - Backup
Host Name: standserver1.localdomain
Last Ping to Primary: 2 seconds ago
Last Ping to Target: 0 seconds ago
Observer "standserver2_observer" - Backup
Host Name: standserver2.localdomain
Last Ping to Primary: 119 seconds ago
Last Ping to Target: 84 seconds ago

What happens if we try to start a fourth observer?

DGMGRL> connect sys/root@ORCL_SITE1
Connected to "ORCL_SITE1"
Connected as SYSDBA.
DGMGRL> start observer fourth_observer
Error: ORA-16647: could not start more than three observers
Failed to start the observer.
DGMGRL>

As we see above, we cannot have more than 3 observers.

One important thing about multiple observers is that we have only more availability for the observer but not for the FSFO configuration. This means that if we lose at the same time the primary database and the master observer, no fast-start failover will be initiated.

Let’s represent this by some configurations
Case1: Master observer and primary database are located in the same datacenter
observer1
Fast Start Failover will not occur if we lose Primary Datacenter

Case2: Master observer and primary database are located in different datacenters
observer2
Fast Start Failover will occur if we lose Primary datacenter

Case3: Master observer and primary database are located in different datacenters
observer3
Fast Start Failover will occur if we lose Primary datacenter

Conclusion: Support of multiple observers is a very nice feature. When using this feature, master observer and primary database should not be located in the same datacenter

 

Cet article Dataguard Oracle 12.2 : Support for Multiple Observers est apparu en premier sur Blog dbi services.

ORA-12801 and ORA-12154 with OracleRestart / GRID / RAC on Oracle Database 12c

Yann Neuhaus - Tue, 2017-01-10 11:39
ORA-12801 and ORA-12154 with OracleRestart / GRID / RAC on Oracle Database 12c Symptom/Analysis:

Using Oracle 12c in a RAC environment, you may encounter the following errors:

ORA-12801: error signaled in parallel query server P000, instance rac2.localdomain:DBRAC2 (2)
ORA-12154: TNS:could not resolve the connect identifier specified

In this article, we will present you an issue that is inspired from a real case:

olivier@olivier-ThinkPad-T550 ~ $ sqlplus sys/***@DBRAC1 as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Jan 9 15:03:42 2017
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> 
select count(*) from DBA_OBJECTS@DBLINK_ONE_MYHOME_TNS, SCOTT.OBJECTS;
exit
ERROR at line 1:
ORA-12801: error signaled in parallel query server P000, instance rac2.localdomain:DBRAC2 (2)
ORA-12154: TNS:could not resolve the connect identifier specified

The following queries return answers with no error:

SQL> select count(*) from DBA_OBJECTS@DBLINK_ONE_MYHOME_TNS;
COUNT(*)
----------
20342
SQL> select count(*) from SCOTT.OBJECTS;
COUNT(*)
----------
90951

Strange !
select * from A is working.
select * from B is working.
select * from A,B is not working.

Let’s check if TNS_ADMIN Oracle environment variable is set in the session:
We will use the dbms_system.get_env function

dbms_system.get_env()
SQL> 
set serveroutput on
DECLARE
RetVal VARCHAR2(4000);
BEGIN
dbms_system.get_env('TNS_ADMIN', RetVal);
dbms_output.put_line('TNS_ADMIN: '||RetVal);
END;
/
SQL> 2 3 4 5 6 7
TNS_ADMIN: /u01/app/MyNetworkAdmin/
PL/SQL procedure successfully completed.
TNS_ADMIN is set correctly.

The dblink is working and is correctly created:

dba_db_links
SQL> SELECT owner, db_link, username, host FROM   dba_db_links ORDER BY owner, db_link;
OWNER	DB_LINK 		  USERNAME   HOST
------- ------------------------- ---------- --------------------
PUBLIC	DBLINK_ONE_MYHOME_TNS	  SYSTEM     DBONE-MYHOME-TNS
DBMS_METADATA.GET_DDL
QL> SELECT DBMS_METADATA.GET_DDL('DB_LINK','DBLINK_ONE_MYHOME_TNS','PUBLIC') ddl from dual;
DDL
-------------------------------------------------------------------------
CREATE PUBLIC DATABASE LINK "DBLINK_ONE_MYHOME_TNS" 
   CONNECT TO "SYSTEM" IDENTIFIED BY VALUES ':1' USING 'DBONE-MYHOME-TNS'
/u01/app/MyNetworkAdmin/tnsnames.ora
DBONE-MYHOME-TNS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = X.X.X.X)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = ONE)
    )
  )

So what ?
Let’s print the SQL plan:

SQL> explain plan for
2 select count(*) from DBA_OBJECTS@DBLINK_ONE_MYHOME_TNS,SCOTT.OBJECTS;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
Plan hash value: 1869185832
-------------------------------------------------------------------------------------------------------------
| Id  | Operation		 | Name        | Rows  | Cost (%CPU)| Time     | TQ/Ins |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	 |	       |     1 | 19591	 (2)| 00:00:01 |	|      |	    |
|   1 |  SORT AGGREGATE 	 |	       |     1 |	    |	       |	|      |	    |
|   2 |   PX COORDINATOR	 |	       |       |	    |	       |	|      |	    |
|   3 |    PX SEND QC (RANDOM)	 | :TQ10001    |     1 |	    |	       |  Q1,01 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE	 |	       |     1 |	    |	       |  Q1,01 | PCWP |	    |
|   5 |      MERGE JOIN CARTESIAN|	       |  1810M| 19591	 (2)| 00:00:01 |  Q1,01 | PCWP |	    |
|   6 |       BUFFER SORT	 |	       |       |	    |	       |  Q1,01 | PCWC |	    |
|   7 |        PX RECEIVE	 |	       | 19911 |    65	 (0)| 00:00:01 |  Q1,01 | PCWP |	    |
|   8 | 	PX SEND BROADCAST| :TQ10000    | 19911 |    65	 (0)| 00:00:01 |	| S->P | BROADCAST  |
|   9 | 	 REMOTE 	 | DBA_OBJECTS | 19911 |    65	 (0)| 00:00:01 | DBLIN~ | R->S |	       |
|  10 |       BUFFER SORT	 |	       | 90951 | 19526	 (2)| 00:00:01 |  Q1,01 | PCWP |	    |
|  11 |        PX BLOCK ITERATOR |	       | 90951 |    15	 (0)| 00:00:01 |  Q1,01 | PCWC |	    |
|  12 | 	TABLE ACCESS FULL| OBJECTS     | 90951 |    15	 (0)| 00:00:01 |  Q1,01 | PCWP |	    |
-------------------------------------------------------------------------------------------------------------
Remote SQL Information (identified by operation id):
----------------------------------------------------
9 - SELECT 0 FROM "DBA_OBJECTS" "DBA_OBJECTS" (accessing 'DBLINK_ONE_MYHOME_TNS' )
Note
-----
- Degree of Parallelism is 4 because of table property
29 rows selected.
 

Parallelism is enabled for the query.
Let’s disable it !

SQL> ALTER SESSION disable parallel query;

Let’s run our query again:

SQL> select count(*) from DBA_OBJECTS@DBLINK_ONE_MYHOME_TNS,SCOTT.OBJECTS;
  COUNT(*)
----------
1850125242

It is working now, but without using parallelism features.

Problem:

In fact, the problem comes from the environment variable TNS_ADMIN that is not (or not correctly) set for the PX servers process:

When parallelism is enabled, the PX servers are doing all the work. (as shown in our parallel plans)
On the server, PX server processes can be easily identified on the OS level. On Linux they are the processes ora_p***:
Source: http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-parallel-execution-fundamentals-133639.pdf

[oracle@rac1 admin]$ ps -ef | grep ora_p0
oracle 25803 1 0 11:21 ? 00:00:00 ora_p000_DBRAC1
oracle 25805 1 0 11:21 ? 00:00:00 ora_p001_DBRAC1
oracle 25807 1 0 11:21 ? 00:00:00 ora_p002_DBRAC1
oracle 25809 1 0 11:21 ? 00:00:00 ora_p003_DBRAC1
oracle 28021 1 0 14:25 ? 00:00:00 ora_p004_DBRAC1
oracle 28023 1 0 14:25 ? 00:00:00 ora_p005_DBRAC1
oracle 28025 1 0 14:25 ? 00:00:00 ora_p006_DBRAC1
oracle 28027 1 0 14:25 ? 00:00:00 ora_p007_DBRAC1
oracle 28029 1 0 14:25 ? 00:00:00 ora_p008_DBRAC1
oracle 28031 1 0 14:25 ? 00:00:00 ora_p009_DBRAC1
oracle 28033 1 0 14:25 ? 00:00:00 ora_p00a_DBRAC1
oracle 28035 1 0 14:25 ? 00:00:00 ora_p00b_DBRAC1
oracle 28037 1 0 14:25 ? 00:00:00 ora_p00c_DBRAC1
oracle 28039 1 0 14:25 ? 00:00:00 ora_p00d_DBRAC1
oracle 28041 1 0 14:25 ? 00:00:00 ora_p00e_DBRAC1
oracle 28047 1 0 14:25 ? 00:00:00 ora_p00f_DBRAC1

The file /proc/25803/environ contains the Oracle environment variables set for the P000 Process:
Command “sudo strings /proc/25803/environ | grep TNS_” give no result.
Source: https://blogs.oracle.com/myoraclediary/entry/how_to_check_environment_variables

If we check the environ file of the listener processes, we can see that the TNS_ADMIN is correctly set.

[oracle@rac1 ~]$ ps -ef | grep lsn
oracle 2731 12705 0 14:49 pts/0 00:00:00 grep --color=auto lsn
oracle 4176 1 0 08:57 ? 00:00:00 /u01/app/grid/bin/tnslsnr MGMTLSNR -no_crs_notify -inherit
oracle 4309 1 0 08:57 ? 00:00:00 /u01/app/grid/bin/tnslsnr LISTENER_SCAN2 -no_crs_notify -inherit
oracle 4320 1 0 08:57 ? 00:00:00 /u01/app/grid/bin/tnslsnr LISTENER_SCAN3 -no_crs_notify -inherit
oracle 9059 1 0 12:01 ? 00:00:00 /u01/app/grid/bin/tnslsnr LISTENER -no_crs_notify -inherit
[oracle@rac1 ~]$ sudo strings /proc/9059/environ | grep TNS_
TNS_ADMIN=/u01/app/MyNetworkAdmin

This small environ.sh script for the lazy one can list the Oracle environment variables set for all the PX Server process and pmon:

environ.sh
#!/bin/bash
mypspmon='/tmp/pspmon'
myprocess1='pmon_DB'
myprocess2='ora_p00'
ps -ef | grep $myprocess1 | awk '{print $2}' > $mypspmon
ps -ef | grep $myprocess2 | awk '{print $2}' >> $mypspmon
while read ligne;
do
myenvironfile="/proc/${ligne#* }/environ"
if [ -e "$myenvironfile" ]
then
strings $myenvironfile
fi
done < $mypspmon

“sudo ./environ.sh | grep TNS” give no result
The TNS_ADMIN Oracle environment variable is not set for the PX server processes that are spawn to handle the parallel queries.

Solution:
  1. Be sure to set the TNS_ADMIN with srvctl !!
  2. Source: https://docs.oracle.com/cd/B19306_01/rac.102/b14197/srvctladmin.htm#i1010191

    [oracle@rac1 ~]$ srvctl setenv listener -l LISTENER -t TNS_ADMIN='/u01/app/MyNetworkAdmin/'
    [oracle@rac1 ~]$ srvctl setenv database -d DBRAC -t TNS_ADMIN='/u01/app/MyNetworkAdmin/'
    

    Let’s check if the variable are correctly set

    [oracle@rac1 ~]$ srvctl getenv listener -l LISTENER -t TNS_ADMIN
    
    LISTENER:
    TNS_ADMIN=/u01/app/MyNetworkAdmin/
    
    [oracle@rac1 ~]$ srvctl getenv database -d DBRAC -t TNS_ADMIN
    
    DBRAC:
    TNS_ADMIN=/u01/app/MyNetworkAdmin/
    

    TNS_ADMIN seems to be correctly set but we still receive ORA-12801 ORA-12154 errors.
    Moreover “sudo ./environ.sh | grep TNS” still gives no result !
    You need to restart database to set up thoroughly the TNS_ADMIN environment variable database !!

    [oracle@rac1 admin]$ srvctl stop listener -l LISTENER
    [oracle@rac1 admin]$ srvctl start listener -l LISTENER
    [oracle@rac1 admin]$ srvctl stop database -d DBRAC
    [oracle@rac1 admin]$ srvctl start database -d DBRAC
    
    [oracle@rac1 admin]$ srvctl status listener -l LISTENER
    
    Listener LISTENER is enabled
    Listener LISTENER is running on node(s): rac1,rac2
    
    [oracle@rac1 admin]$ srvctl status database -d DBRAC
    
    Instance DBRAC1 is running on node rac1
    Instance DBRAC2 is running on node rac2
    

    Now our “sudo ./environ.sh | grep TNS” commands list the TNS_ADMIN environment variable used by our pmon and px server processes.

    TNS_ADMIN=/u01/app/MyNetworkAdmin/
    TNS_ADMIN=/u01/app/MyNetworkAdmin/
    TNS_ADMIN=/u01/app/MyNetworkAdmin/
    TNS_ADMIN=/u01/app/MyNetworkAdmin/
    [...]
    TNS_ADMIN=/u01/app/MyNetworkAdmin/
    

    And our query is working using parallelism features.

  3. Other solutions exist:
  4. One of them would consist in modifying USR_ORA_ENV cluster resources attribute values with crsctl

    [oracle@rac1 admin]$ crsctl modify resource ora.dbrac.db -attr "USR_ORA_ENV=TNS_ADMIN=/u01/app/MyNetworkAdmin/"
    
    CRS-4995: The command 'Modify resource' is invalid in crsctl. Use srvctl for this command.
    

    In 12c, just add the flag -unsupported ad the end to avoid CRS-4995 errors.
    [oracle@rac1 admin]$ crsctl modify resource ora.dbrac.db -attr “USR_ORA_ENV=TNS_ADMIN=/u01/app/MyNetworkAdmin/” -unsupported

Comments:
  1. This issue is inspired from a real case. You can easily reproduce the issue
  2. SCOTT.OBJECTS table was created like that.

    SCOTT.OBJECTS
    
    create table SCOTT.OBJECTS as select * from DBA_OBJECTS;
    alter table scott parallel 32;
    

    Add an entry in your tnsnames.ora and create a dblink
    You can remove TNS_ADMIN environment database with “srvctl unsetenv database -d database -t TNS_ADMIN” command. And restart.

  3. On the server, on the OS level, if ORACLE_HOME is set and TNS_ADMIN is not set then $ORACLE_HOME/network/admin is used to locate tnsname.ora
  4. So it can be interesting to use symlink (ln -s) in $ORACLE_HOME/network/admin to point and use the same tnsnames.ora file when using sqlplus from the server.

    [oracle@rac1 admin]$ pwd
    
    /u01/app/oracle/product/12.1.0.2/db_1/network/admin
    
    [oracle@rac1 admin]$ ll
    
    total 0
    lrwxrwxrwx 1 oracle oinstall 36 Jan 10 09:53 listener.ora -> /u01/app/MyNetworkAdmin/listener.ora
    lrwxrwxrwx 1 oracle oinstall 34 Jan 10 09:53 sqlnet.ora -> /u01/app/MyNetworkAdmin/sqlnet.ora
    lrwxrwxrwx 1 oracle oinstall 36 Jan 10 09:53 tnsnames.ora -> /u01/app/MyNetworkAdmin/tnsnames.ora
    

But remember, although the TNS_ADMIN environment is set on the OS level when starting the instance, you need to to set the TNS_ADMIN with srvctl or crsctl before !!
If not, you may encounter ORA-12154 errors.
And not only for PDML !!

 

Cet article ORA-12801 and ORA-12154 with OracleRestart / GRID / RAC on Oracle Database 12c est apparu en premier sur Blog dbi services.

How to use the null column in where condition

Tom Kyte - Tue, 2017-01-10 08:06
Hi Tom, I have a situation were for some record one column is having a value and for few records the column value is null. how do i select the record for this case. For eg select segment1,segment2 from abc nvl(segment2,'')=nvl(segment2,...
Categories: DBA Blogs

Reading and generating file in outside of db server

Tom Kyte - Tue, 2017-01-10 08:06
Hi Tom, I want to load images into DB table from host server, which is not DB directory. Currently i am using bfile concept to load images, which are in db directory. But, due security purpose client don't want to give access of DB server to...
Categories: DBA Blogs

delayed block cleanout

Tom Kyte - Tue, 2017-01-10 08:06
Hi Tom, Thanks for providing answers in detail for us Though you explained in so many times still I have confusion about this topic,can you clear my question. "A query that reads datablocks that have been updated by a large DML (INSERT/U...
Categories: DBA Blogs

SQLLDR & lob file path

Tom Kyte - Tue, 2017-01-10 08:06
Is there a way to specify the lob file path? I can not change my .dat files to include the path so I am looking for a way when I load to specify the lob file direcotry. Lob file name was dynamically generated so I can't use CONSTANT either. ...
Categories: DBA Blogs

Bitmap Indexes

Tom Kyte - Tue, 2017-01-10 08:06
Hi Tom, Good Day I was reading a book written by a popular author (I would prefer not to mention the name of the book and its author). He says: In discussion on bitmap indexes, the following was written: "Concatenated B*tree indexes were ...
Categories: DBA Blogs

unexpected query plan change from index range scan to full table scan

Tom Kyte - Tue, 2017-01-10 08:06
Our oracle server vision is 11.2.0.1, we found one issue related to query plan change when running stress test, which is unexpected. I have one table with following schema CREATE TABLE JOURNAL ( REVISION_ID NUMBER(20) NOT...
Categories: DBA Blogs

Table Lock During partition import.

Tom Kyte - Tue, 2017-01-10 08:06
Hi Guys, DB Info :needs to be available 24x7x365. Table Info : 300 GB , Partitioned by range and has Global Indexes. needs to be available 24x7x365. I want to do a character set conversion to UTF-8 in the oldest partitions while the table...
Categories: DBA Blogs

SQL Loader

Tom Kyte - Tue, 2017-01-10 08:06
Hi, i loading some into table by using sql loader. to uderstand my issue clearly See the Below Example INSERT INTO t1 VALUES (2,'san'thu'); above statement throws error ORA-00917 missing comma. so to insert san'thu below is the corr...
Categories: DBA Blogs

Set up email notification for DML query for a table

Tom Kyte - Tue, 2017-01-10 08:06
Hi Team, My question is : I need to configure email notification for any insert, update, delete query in database table. I have a trigger in place for this . Trigger name is TTT_WNIU_USER_WORKGROUP Target table name is TTT_om_user_workgro...
Categories: DBA Blogs

Join Elimination 12.2

Jonathan Lewis - Tue, 2017-01-10 07:03

From time to time someone comes up with the question about whether or not the order of tables in the from clause of a SQL statement should make a difference to execution plans and performance. Broadly speaking the answer is no, although there are a couple of boundary cases were a difference can appear unexpectedly.

When considering join permutations the optimizer has a few algorithms for picking an initial join order and then deciding how to permute from that order, and one of the criteria with the very lowest priority (i.e. when all other factors are equal) is dictated by the order the tables appear in the from clause so if you have enough tables in the from clause it’s possible for the subset of join orders considered to change if you change the from clause in a way that causes the initial join order to change.

It’s been over 11 years since I wrote the article I’ve linked to in the previous paragraph and in that time no-one has yet approached me with other examples of a plan changing due to a change in the from clause order (though, with all the transformations now available to the optimizer, I wouldn’t be surprised if a few cases have appeared occasionally, so feel free to let me know if you think you’ve got an interesting example that I can experiment on).

A little while ago, though, while testing a feature enhancement in 12.2, I finally came across a case where a real difference appeared. Here’s the query I was using – I’ll give you the SQL to reproduce the tables at the end of the article:


select 
	count(c.small_vc_c)
from 
	grandparent	g, 
	parent		p,
	child		c
where
	c.small_num_c between 200 and 215
and	p.id   = c.id_p
and	p.id_g = c.id_g
and	g.id   = p.id_g
;

As you will see later on the three tables grandparent, parent, child have the obvious primary keys and referential integrity constraints. This means that grandparent has a single-column primary key, parent has a two-column primary key, and child has a three-column primary key. The query joins the three tables along their primary keys and then selects data only from the child table, so it’s a good candidate for join elimination.

In earlier versions of Oracle join elimination could take place only if the primary key you joined to was a single column key, so 12.1 and earlier would be able to eliminate just the grandparent from this three-table join; but in 12.2 multi-column primary keys also allow join elimination to take place, so we might hope that the plan we get from this query would eliminate both the grandparent and parent tables. Here’s the plan (pulled from memory after execution):

SQL_ID  8hdybjpn2884b, child number 0
-------------------------------------
select  count(c.small_vc_c) from  grandparent g,  parent  p,  child  c
where  c.small_num_c between 200 and 215 and p.id   = c.id_p and p.id_g
= c.id_g and g.id   = p.id_g

Plan hash value: 4120004759

-----------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |       |       |    26 (100)|          |
|   1 |  SORT AGGREGATE     |       |     1 |    23 |            |          |
|   2 |   NESTED LOOPS      |       |    85 |  1955 |    26   (4)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| CHILD |    85 |  1615 |    26   (4)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN| G_PK  |     1 |     4 |     0   (0)|          |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(("C"."SMALL_NUM_C"<=215 AND "C"."SMALL_NUM_C">=200))
   4 - access("G"."ID"="C"."ID_G")

It didn’t work quite as expected. The optimizer has managed to eliminate table parent – so that looks like “single column primary key” join elimination has worked, but “multi-column” join elimination hasn’t appeared. On the other hand, I’ve not followed my usual rules for writing SQL so let’s try again. If I follow the pattern I usually follow, my from clause would have been in the order child  -> parent -> grandparent – listing the tables in the order I expect to visit them. Here’s the plan – again pulled from memory – after making this visual change the SQL:


SQL_ID  1uuq5vf4bq0gt, child number 0
-------------------------------------
select  count(c.small_vc_c) from  child  c,  parent  p,  grandparent g
where  c.small_num_c between 200 and 215 and p.id   = c.id_p and p.id_g
= c.id_g and g.id   = p.id_g

Plan hash value: 1546491375

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |       |       |    26 (100)|          |
|   1 |  SORT AGGREGATE    |       |     1 |    15 |            |          |
|*  2 |   TABLE ACCESS FULL| CHILD |    85 |  1275 |    26   (4)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("C"."SMALL_NUM_C"<=215 AND "C"."SMALL_NUM_C">=200))

So join elimination based on multi-column primary keys does work – but you might have to get a bit lucky in the order you list the tables in the from clause.

Footnote.

If you’re wondering whether or not switching from Oracle syntax to ANSI syntax would make a difference, it does: with ANSI syntax both grandparent and parent are eliminated if the SQL lists the tables in the order grandparent -> parent -> child (i.e. the order which doesn’t work properly for Oracle syntax) and only the parent is eliminated for the order child -> parent -> grandparent. In other words, both syntax options have a point of failure but they fail the opposite way around.

Code:

rem
rem	Script:		join_elimination_12c2.sql
rem	Author:		Jonathan Lewis
rem	

-- Environment details eliminated

define m_pad=100

/*
	IDs will be 1 to 1000
*/

create table grandparent 
as
select 
	rownum			id,
	trunc((rownum-1)/5)	small_num_g,
	rpad(rownum,10)		small_vc_g,
	rpad(rownum,&m_pad)	padding_g
from 
	all_objects 
where 
	rownum <= 1000
;

/*
	Each GP has two (scattered) children here
	Parent IDs are 1 to 2,000
*/

create table parent 
as
select 
	1+mod(rownum,1000)	id_g,
	rownum			id,
	trunc((rownum-1)/5)	small_num_p,
	rpad(rownum,10)		small_vc_p,
	rpad(rownum,&m_pad)	padding_p
from 
	all_objects 
where 
	rownum <= 2000
;

/*
	Simple trick to get 5 (clustered) children per parent
	Child IDs are 1 to 12,000
*/

create table child 
as
select 
	id_g,
	id			id_p,
	rownum			id,
	trunc((rownum-1)/5)	small_num_c,
	rpad(rownum,10)		small_vc_c,
	rpad(rownum,&m_pad)	padding_c
from 
	parent	p,
	(
		select /*+ no_merge */ 
			rownum 
		from	parent p 
		where	rownum <= 5
	)	d
;

create unique index g_pk on grandparent(id);
create unique index p_pk on parent     (id_g, id)       compress 1;
create unique index c_pk on child      (id_g, id_p, id) compress 2;

alter table grandparent add constraint g_pk primary key (id);
alter table parent      add constraint p_pk primary key (id_g, id);
alter table child       add constraint c_pk primary key (id_g, id_p, id);

alter table parent add constraint p_fk_g foreign key (id_g)       references grandparent;
alter table child  add constraint c_fk_p foreign key (id_g, id_p) references parent;

rem
rem	Don't need to collect stats because it's 12c
rem

prompt	===============================================================
prompt	Join all three tables with the FROM clause ordered gp -> p -> c
prompt	The final plan is GP->C, The optimizer eliminated P before
prompt	considering GP
prompt	===============================================================

select 
	count(c.small_vc_c)
from 
	grandparent	g, 
	parent		p,
	child		c
where
	c.small_num_c between 200 and 215
and	p.id   = c.id_p
and	p.id_g = c.id_g
and	g.id   = p.id_g
;

select * from table(dbms_xplan.display_cursor(null,null,'outline'));

prompt	===============================================================
prompt	Join all three tables with the FROM clause ordered c -> p -> gp
prompt	The final plan is a tablescan of C only. The optimizer managed 
prompt	to eliminate GP first and P second
prompt	===============================================================

select 
	count(c.small_vc_c)
from 
	child		c,
	parent		p,
	grandparent	g 
where
	c.small_num_c between 200 and 215
and	p.id   = c.id_p
and	p.id_g = c.id_g
and	g.id   = p.id_g
;

select * from table(dbms_xplan.display_cursor(null,null,'outline'));

prompt	==================================================
prompt	Convert to ANSI standard in the order gp -> p -> c
prompt	and both gp and p eliminated.
prompt	==================================================

select 
	count(c.small_vc_c)
from 
	grandparent	g
join
	parent		p
on	p.id_g = g.id
join
	child		c
on	c.id_p = p.id
and	c.id_g = p.id_g
where
	c.small_num_c between 200 and 215
;

select * from table(dbms_xplan.display_cursor(null,null,'outline'));

prompt	===================================================
prompt	Convert to ANSI standard in the order c -> p -> gp
prompt	and only p is eliminated. 
prompt	===================================================

select 
	count(c.small_vc_c)
from 
	child		c
join
	parent		p
on      p.id   = c.id_p 
and	p.id_g = c.id_g 
join
	grandparent	g
on	g.id = p.id_g 
where
	c.small_num_c between 200 and 215
;

select * from table(dbms_xplan.display_cursor(null,null,'outline'));

It’s possible, of course, that with different system stats, or I/O calibration, or extent sizes, or segment space management, or block sizes, sundry other parameter details that you won’t be able to reproduce the results without messing about a little bit, but I don’t think I’ve done anything special in the setup that would make a real difference.


Sur La Table Selects Oracle Commerce to Complement Oracle Retail Xstore Point-of-Service

Oracle Press Releases - Tue, 2017-01-10 07:00
Press Release
Sur La Table Selects Oracle Commerce to Complement Oracle Retail Xstore Point-of-Service Specialty Midmarket Retailer Connects Stores, Online, and Distribution Center Operations

Redwood Shores, Calif.—Jan 10, 2017

Today Oracle announced that Sur La Table has deployed Oracle Retail Xstore Point-of Service and has selected Oracle Commerce to deliver a converged commerce experience across all channels. Catering to the growing number of consumers visiting its commerce site and more than 130 stores nationwide, Sur La Table is creating a cohesive shopping experience, personalizing offers and streamlining order fulfillment. A popular destination for exclusive and premium-quality goods for the kitchen and table, Sur La Table is a place where cooks find a surprising selection of culinary tools and foods from around the world, with helpful, knowledgeable employees.

“We are empowering our associates in the stores to improve the customer experience with the Oracle Retail Xstore Point-of-Service solution. We selected Oracle Commerce to deliver a consistent brand experience across channels,” said Tracy Cioffi, Senior Vice President of Marketing, Sur La Table.  

Sur La Table continues to transform its IT infrastructure by using Oracle to compliment or replace customized legacy applications that hindered growth and were costly to maintain. Before transitioning to Oracle, Sur La Table evaluated the savings against ongoing cost of maintaining its legacy systems.

“Oracle is providing the retail industry’s leading, most comprehensive enterprise solutions to retailers of all sizes,” said Ray Carlin, Senior Vice President and General Manager, Oracle Retail. “We are pleased to partner with Sur La Table will continue to deliver innovation backed by world class technology.”

Oracle Retail at NRF 2017

Oracle Retail will be showcasing the full suite of Oracle Retail solutions and cloud services at the National Retail Federation Big Show Jan. 15-17, 2017, in New York City at the Jacob K. Javitz Convention Center. Oracle Retail will be located at booth #2521. For more information check out: www.oracle.com/nrf2017

Contact Info
Matthew Torres
Oracle
+415 595 1584
matt.torres@oracle.com
About Oracle

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit www.oracle.com.

Trademarks

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Safe Harbor

The following 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 decisions. The development, release, and timing of any features or functionality described for Oracle's products remains at the sole discretion of Oracle Corporation. 

Talk to a Press Contact

Matthew Torres

  • +415 595 1584

New OA Framework 12.2.5 Update 9 Now Available

Steven Chan - Tue, 2017-01-10 02:00

Web-based content in Oracle E-Business Suite 12 runs on the Oracle Application Framework (OAF or "OA Framework") user interface libraries and infrastructure.  Since the release of Oracle E-Business Suite 12.2 in 2013, we have released several cumulative updates to Oracle Application Framework to fix performance, security, and stability issues.

These updates are provided in cumulative Release Update Packs, and cumulative Bundle Patches that can be applied on top of the Release Update Pack. "Cumulative" means that the latest RUP or Bundle Patch contains everything released earlier.

The latest OAF update for EBS 12.2.5 is now available:


Where is this update documented?

Instructions for installing this OAF Release Update Pack are here:

Who should apply this patch?

All EBS 12.2.5 users should apply this patch.  Future OAF patches for EBS 12.2.5 will require this patch as a prerequisite. 

What's new in this update?

This bundle patch is cumulative: it includes all fixes released in previous EBS 12.2.5 bundle patches.

This latest bundle patch also includes new fixes for several critical issues:

  • Partial Page Rendering event queuing fails in Internet Explorer 11 and Chrome browsers
  • Inability to reset the value of a read-only text input bean mapped to a list of values
  • Columns selected while creating a view are not displayed after running a query against the view
  • An error message is displayed when trying to import a personalization
Related Articles
Categories: APPS Blogs

My first blog post with classeur.io

Dimitri Gielis - Mon, 2017-01-09 18:18

I’m trying to write this blog post with classeur.io.

Just like Martin I’m also searching for alternative ways to write blog posts. I don’t want to completely migrate my blog to a new platform, so I’m searching for a way to write in Markdown and deploy to Blogger.

Why Markdown?
Since our development of APEX Office Print (AOP) we also use Markdown for our documentation as it makes including code samples easier, it can be version controlled and overall it’s pleasant to write in. Whenever we deploy a new version, we publish the markdown as HTML. For example you see the result of our documentation here. SSjj… we like Markdown so much that we are even looking into supporting Markdown to write your template in AOP, next to Word, Excel and Powerpoint, but more on that in the February timeframe. :)

So I’m giving classeur.io a try, it’s just another Markdown editor, but it can publish directly to Blogger. It allows me to include code samples like this:

declare
l varchar2(100);
begin
l := 'hello world';
sys.htp.p(l);
end;

Or if I want to reference somebody I can use a quote:

Oracle Application Express (APEX) changed my life. – Dimitri Gielis

And a list … for example the top 3 reasons I’m looking at a different way to blog:

  1. Faster to write a post
  2. Reuse my writings in different ways
  3. Easier to share code

So this post is really to try the different options of classeur.io which should show up in Blogger after I hit the publish button.

Here’s an image which I plan to use in my next post:
enter image description here

If you see this and the post looks ok, my test went well :)

Categories: Development

Oracle Utilities 2017 Edge Customer Conference Product Forum

Anthony Shorten - Mon, 2017-01-09 17:14

I will  be attending the USA and APAC Oracle Utilities 2017 Edge Customer Conference Product Forum this year, conducting a number of technical sessions. This year, to make the sessions more relevant, the content of the sessions has been tweaked to cover a number of aspects for the subject are chosen. The sessions are a combination of information, future plans, best practices and tips/techniques for getting the most out of your products. The information is based upon feedback from implementations across the world as well as information on how Oracle itself is implementing the products in the cloud.

The sessions this year are as follows:

Session Number
 Topic  TECH-001 Data Management Strategies - Using ILM and CMA to manage your data. This session will not be conducted at the APAC conference.
 TECH-002 Integration Techniques - Using the various techniques available to build an integration solution including Web Services, REST and the Oracle Integration Cloud Adapter.
 TECH-003 Extending your implementation - Various techniques for extending your product on site and in the cloud. 
 TECH-004 Testing your implementation - Outlining testing accelerators with Oracle Utilities Advanced Testing Pack (co-presented with a customer).
 TECH-005 Utilities in the Cloud - An architectural overview of the Oracle Utilities offerings in the cloud to understand the capabilities and learn about how to apply the same architectures to your onsite or cloud implementations.
 TECH-006 Securing your implementation - Understanding the security aspects of the products as well as options for extending the security capabilities.
 TECH-007 General Question and Answer session - A panel session where you can ask product experts questions about implementation issues and directions.

 If you are attending the forum, feel free to attend and catch up with me at the sessions or the various other avenues during the conference.

Pages

Subscribe to Oracle FAQ aggregator