DBA Blogs

CPU sudden spike

Tom Kyte - Sat, 2017-04-29 12:26
Hi All, We observed a sudden increase in CPU usage in one of the Exa node on 10th April and it is still same till date. Example CPU utilization increased from 2% to 66 % and same till today. Can someone help me how to find what exactly caused th...
Categories: DBA Blogs

COMPOUND KEY

Tom Kyte - Sat, 2017-04-29 12:26
Hi, In Below Document i found this Text 'Oracle considers two nulls to be equal when evaluating a DECODE function. Please refer to DECODE for syntax and additional information. Oracle also considers two nulls to be equal if they appear in compo...
Categories: DBA Blogs

ORA-00923: FROM keyword not found where expected

Tom Kyte - Sat, 2017-04-29 12:26
(SQR 5528) ORACLE OCIStmtExecute error 923 in cursor 16: ORA-00923: FROM keyword not found where expected SQL: SELECT DISTINCT B10.EMPLID from PS_BEN_PROG_PARTIC a10, PS_EMPLOYEES b10, PS_BEN_DEFN_PGM d10 where a10.EMPLID = b10.EMPLID A...
Categories: DBA Blogs

12cR1 RAC Posts -- 9 : Adding a Service to a PDB in RAC

Hemant K Chitale - Sat, 2017-04-29 11:02
My 2node RAC environment has 1 PDB.  Here I add (create) a new Service to the PDB.

Oracle recommends using srvctl instead of DBMS_SERVICE to add a Service.

Can I add a service without having the PDB OPEN ?

[oracle@collabn1 ~]$ srvctl start database -d RAC
[oracle@collabn1 ~]$ srvctl add service -db RAC -service MYSVC -preferred RAC1,RAC2 \
> -tafpolicy BASIC -clbgoal SHORT -pdb PDB
[oracle@collabn1 ~]$
[oracle@collabn1 ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 29 23:16:01 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 con_id, name, open_mode from v$pdbs;

CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
2 PDB$SEED READ ONLY
3 PDB MOUNTED

SQL> alter pluggable database pdb open; -- command issued in both instances

Pluggable database altered.

SQL>
SQL> select con_id, pdb, name, creation_date, clb_goal
2 from v$services
3 where pdb='PDB'
4 /

CON_ID PDB
---------- ------------------------------
NAME CREATION_ CLB_G
---------------------------------------------------------------- --------- -----
3 PDB
pdb.racattack 29-DEC-16 LONG


SQL>


I add the Service to the TNSNAMES.ORA and try to connect to it.

MYSVC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = collabn-cluster-scan.racattack)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = MYSVC.racattack)
)
)

[oracle@collabn1 ~]$ tnsping MYSVC

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 29-APR-2017 23:22:43

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = collabn-cluster-scan.racattack)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MYSVC.racattack)))
OK (0 msec)
[oracle@collabn1 ~]$ sqlplus hemant/hemant@MYSVC

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 29 23:22:51 2017

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

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Enter user-name:


So, apparently, the service isn't running.   Shall I try re-adding the service ?

[oracle@collabn1 ~]$ srvctl add service -db RAC -service MYSVC -preferred RAC1,RAC2 \
> -tafpolicy BASIC -clbgoal SHORT -pdb PDB
PRKO-3117 : Service MYSVC already exists in database RAC
[oracle@collabn1 ~]$ srvctl remove service -db RAC -service MYSVC -pdb PDB
PRKO-2002 : Invalid command line option: -pdb
[oracle@collabn1 ~]$ srvctl remove service -db RAC -service MYSVC
[oracle@collabn1 ~]$ srvctl add service -db RAC -service MYSVC -preferred RAC1,RAC2 \
> -tafpolicy BASIC -clbgoal SHORT -pdb PDB
[oracle@collabn1 ~]$
[oracle@collabn1 ~]$ sqlplus hemant/hemant@MYSVC

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 29 23:31:15 2017

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

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Enter user-name:


Yet, it doesn't work !   How do I add and start service to a PDB ?  What's missing ?

[oracle@collabn1 ~]$ srvctl remove service -db RAC -service MYSVC
[oracle@collabn1 ~]$ srvctl start service -db RAC -service MYSVC
[oracle@collabn1 ~]$

SQL> select distinct name from v$services;

NAME
----------------------------------------------------------------
pdb.racattack
RAC.racattack
MYSVC
SYS$BACKGROUND
RAC_DGB
RACXDB
SYS$USERS


[oracle@collabn1 ~]$ sqlplus hemant/hemant@MYSVC

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 29 23:43:01 2017

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

Last Successful login time: Sun Apr 16 2017 23:30:21 +08:00

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 table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
MY_DATA
MY_NEW_TABLE

SQL>


I had forgotten to *START* the service !   Let me go back and add another service with the PDB CLOSEd.

SQL> alter pluggable database pdb close immediate;  -- on both instances

Pluggable database altered.

SQL>

[oracle@collabn1 ~]$ srvctl add service -db RAC -pdb PDB -service NEWSVC -preferred RAC1,RAC2 \
> -tafpolicy BASIC -clbgoal SHORT
[oracle@collabn1 ~]$

SQL> alter pluggable database pdb open; -- on both instances

Pluggable database altered.

SQL>

NEWSVC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = collabn-cluster-scan.racattack)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = NEWSVC.racattack)
)
)

[oracle@collabn1 ~]$ srvctl start service -db RAC -service NEWSVC
[oracle@collabn1 ~]$ tnsping NEWSVC

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 29-APR-2017 23:54:38

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = collabn-cluster-scan.racattack)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = NEWSVC.racattack)))
OK (0 msec)


[oracle@collabn1 ~]$ sqlplus hemant/hemant@NEWSVC

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 29 23:55:25 2017

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

Last Successful login time: Sat Apr 29 2017 23:54:51 +08:00

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 table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
MY_DATA
MY_NEW_TABLE

SQL>


However, these entries in PDB_PLUG_IN_VIOLATIONS seem to be a bug :

SQL> alter pluggable database pdb close immediate;

Pluggable database altered.

SQL> alter pluggable database pdb open;

Pluggable database altered.

SQL> select * from pdb_plug_in_violations;

TIME
---------------------------------------------------------------------------
NAME
------------------------------
CAUSE TYPE
---------------------------------------------------------------- ---------
ERROR_NUMBER LINE
------------ ----------
MESSAGE
--------------------------------------------------------------------------------
STATUS
---------
ACTION
--------------------------------------------------------------------------------
29-APR-17 11.58.32.409572 PM
PDB
Service Name Conflict WARNING
0 1
Service name or network name of service MYSVC in the PDB is invalid or conflicts
with an existing service name or network name in the CDB.
PENDING
Drop the service and recreate it with an appropriate name.

29-APR-17 11.58.32.412142 PM
PDB
Service Name Conflict WARNING
0 2
Service name or network name of service NEWSVC in the PDB is invalid or conflict
s with an existing service name or network name in the CDB.
PENDING
Drop the service and recreate it with an appropriate name.


SQL>


So, remember, it is not sufficient to ADD a Service. You must also START the Service using srvctl.
.
.
.

Categories: DBA Blogs

Time difference between two date/times

Tom Kyte - Fri, 2017-04-28 18:06
Hey Guys Hoping you will be able to help with an issue I am having. I currently have information stored in a table as StartDate - stored as date (eg 18-APR-17) and StartTime - stored as a number(4,2) so 10.30am would be stored as 10.3 or 3.45pm...
Categories: DBA Blogs

lob tablespace -- moving a lob

Tom Kyte - Fri, 2017-04-28 18:06
How do I move lob indexes and lob segments from one tablespace to another.
Categories: DBA Blogs

How to find out, what stored procedure does a commit

Tom Kyte - Fri, 2017-04-28 18:06
This may sound silly, but I'm unable to figure out what damn stored procedure (or something else?) performs a commit after I manually execute another stored procedure. There is a complicated business logic implemented using stored procedures and, ...
Categories: DBA Blogs

Eexecute an Oracle stored procedure via a database link

Tom Kyte - Thu, 2017-04-27 23:46
Hi, I have procedure via database link, and getting this error ORA-02064: distributed operation not supported This is because need to apply pragma autonomous_transaction. But is there any other way? some of the procedure we need to reuse, a...
Categories: DBA Blogs

PROD (LINUX) TO DEV (WINDOWS)

Tom Kyte - Thu, 2017-04-27 23:46
Hey Tom I am really hoping you can help me with this, I have can not find anything related to this anywhere. We have a daily export .dmp from a Linux PROD DB. We want to import that in a windows new DB . I have tried creating a DB w/ DBCA a...
Categories: DBA Blogs

Advanced index compression HIGH in 12.2

Tom Kyte - Thu, 2017-04-27 23:46
Team, Reading through this documentation link on Advanced index compression HIGH. <u>http://docs.oracle.com/database/122/REFRN/DB_INDEX_COMPRESSION_INHERITANCE.htm#REFRN10336</u> <quote> TABLESPACE Index inheritance is based on tablespac...
Categories: DBA Blogs

Display DBMS output from another DB

Tom Kyte - Thu, 2017-04-27 23:46
Hi, Im running a procedure in DB A which calls a procedure internally which is in DB B. When I turn on set server output on it only displays the DBMS output of DB A and not in DB B. I want all the DBMS outputs regardless of DB.... THANKS
Categories: DBA Blogs

Auto mail for every 6 months

Tom Kyte - Thu, 2017-04-27 23:46
Hi All, I need a query for the below scenario. I am working for a retail application so there is an option for conducting an audit for suppliers. Scenario - While conducting an audit i found a child labor issue so for this case i have to sen...
Categories: DBA Blogs

editing dmp file

Tom Kyte - Thu, 2017-04-27 23:46
Dear Tom iwant to modify the tablespaces in a dump file before importing it to another database doesnt include the tablespaces in the export file how can i do this...? can i generate a sql file to do so...? thanks for your help Mohamed
Categories: DBA Blogs

Oracle E-Business Suite and Java Web Start. Finally!

Pythian Group - Thu, 2017-04-27 20:36

Great things always happen overnight. That’s probably the case for European Oracle Apps DBAs in the same situation, like myself.

This morning I read Steven Chan’s latest blog post about Java Web Start certification with Oracle E-Business Suite and its support release to the public. We heard about it during some informal discussions at Collaborate 17 conference.  Everything is ready, the documentation is prepared, and they are just waiting for final bug related to Workflow Activity Monitor to be addressed…

…and about twenty days later,  it was released.

This is a superb evolutionary event! And, of course, I made time to fit in some good introductory, hands-on experience.

 

Initial thoughts while going through Doc ID 2188898.1

 

1. Java Runtime version for client.

  • JRE 8 Update 131 b31 or JRE 8 Update 121 b33 are required (as a minimum).
    This isn’t clearly visible – these are special update releases (look at b31 and b33), available only through My Oracle Support download via Patch 25767257 and 25450542. A public release containing the support is only scheduled for next quarter and is set to be released with Update 141.
  • JRE 6 and JRE 7 are out of scope! If you don’t have JRE 8 support there in your system, now is the right time to think about it.

2. Support for R12.2. Also there is a support for R12.1.3. No 11i.

Patching requirement is minimal (three small patches for AD/TXK/FND), but you need to be on latest AD/TXK.C.8 and ATG_PF.C.6, and new PSU overlay for 10.1.2 Oracle Home which nicely supersedes the previous overlay released.

There are eight functional module patches posted so far. There are still known issues mentioned for other modules. These are less popular modules, and are to be implemented and added to the list at some point, I believe.
I would include this list in your patch maintenance, especially Oracle Workflow related for Activity Monitor.

s_forms_launch_method new context variable is implemented which sets the required profile options at Site level. However, you can still configure exceptions for users via User Level profile option values. Both methods are available. You can still leave browser plugin method as default, and enable JWS only for particular users, let’s say, for testing purposes.

3. “Only one instance per Java applet can be running on the client PC.”

Yes, it’s only possible to have one environment opened at the same time. This is a JWS limitation.  Even IE’s noframemerging or “hey, I can use my second browser” is not an option here.

4. Chrome full usage support is finally available there for Forms.

Basically, browser support is not dependent on browser plugin availability. There is also Microsoft Edge support to mention and we can finally start using 64-bit Java, as there is no longer browser plugin dependency.

5. Known issues section.

From management and housekeeping perspective, docs are mentioning that the process is self cleanable, like frmservlet.jnlp downloaded files should be automatically cleared from disk after launch. There is a known issues section mentioning some nuances, open issues and workarounds for specific browsers and functionality areas in EBS. As always, it will be kept updated and will be periodically reviewed by many of us Apps DBAs for news.

 

Now to talk about my hands-on…

I used my R12.2.6 Vision lab instance built on AWS.  The patching exercise took something like 30 minutes in total (10.1.2 patch, ebs patches, JAR regeneration). But I was doing this in hotpatch mode and not through online patch cycle, (yes, not the right way, but my goal was to avoid ADOP time consuming tasks.
I also installed the required JRE 8 update 131 b31 64-bit version on my laptop.

I made a huge mistake starting this testing on my MacOS, which introduced a number of issues that almost led me to a huge “why Oracle?” facepalm result. And it’s not just Gatekeeper security feature known issue mentioned for Safari on MacOS. But I’ll talk about this later.

Brought up my Windows 7 VM.
All IE 11, Firefox’s latest public update, and Chrome just worked like a charm. I didn’t experience any issues like I faced previously on my Mac. “Save File” to Downloads folder and then a double-click, or “Open with” to open the applet immediately. And your Forms session is up. Look at the screenshot – CHROME!

Applet window is transparently opening.

The example below is from Workflow Activity Monitor using Firefox. No extra browser tab / window opened in my case, although there was a mention of known issue with Firefox.

Applet files downloaded are automatically cleared once the applet is loaded, you will not find them on the disk. Chrome, as an example, updates the status of each item under Downloads tab.

So again.. Windows platform is covered well. What about the rest?

Linux is not a certified platform for Oracle E-Business Suite end user desktops. Although I was successfully using it in my experience, and it should work, the Oracle team still is not testing and certifying it. You may use and play around it at your own risk, and it shouldn’t be the right production direction. I personally haven’t tested it yet for JWS, but my guess is that it will have the same problem as MacOS is having. Maybe a to-do for this blog post update at some point.

MacOS… Steven Chan’s blog post, as mentioned above, states that “Safari on MacOS” is not certified because of MacOS Gatekeeper security feature that is “making the Java Web Start user experience very challenging”. That’s fine. We know a workaround to go to System Preferences app and click on Open Anyway button, though it’s required every time we launch the Forms session.

But… we don’t even reach this point. In the example below, I will outline a sequence of “nuances” I faced. It will be based on Safari screenshots, as only Safari is officially certified on MacOS. Absolutely the same issues I faced in Firefox on MacOS, and in Chrome on same MacOS.

First – we are trying to open Forms, but just getting this familiar screen.

Why? Because the URL still goes to browser plugin mode – “/forms/frmservlet?config=browserMode&appletmode=…”

We can go and set the ICX_FORMS_LAUNCHER profile option to “/forms/frmservlet?config=jws” on Site level, as MOS note outlines.

This works, but it will be required every time we run AutoConfig, as the profile option is always being reset to just “/forms/frmservlet” on Site level, and Forms opening process is supposed to follow FND_ENABLE_JAVA_WEB_START value direction.

After the manual fix this is what’s happening. Safari is downloading the applet.

We launch it and face a known Gatekeeper issue (only now).

Applet is loaded, but not Forms.

I would expect Safari to cover this itself, but in my case I have Firefox configured as default Web browser in the system.
That caused Firefox tab to be opened (called by initial applet) and a second applet download to be requested.

Once the second applet is up, I finally get the Forms session running. Sort of, it’s a similar flow that is happening with a browser plugin, but it is just killing the user experience.
Initially second applet was blocked by same Gatekeeper, and I had to use the known workaround. But at a later testing MacOS is always blocking only the first applet while launching Forms, second – not anymore. Maybe, I suspect the issue is with a miss of jndi extension for the file.

I got the Forms running. But what a process it was…
And nothing is cleared. My Downloads folder is full of these fndservlet.jndi files downloaded.

To summarize

This is a great moment and long expected feature that got finally released by the Oracle EBS team. But is it ready?

I would say, if your company is following strict control standards what software is used, including the browsers, you are, most probably, using same IE on Windows – this feature will work well. Forms will work even with good old browser plugin mode, your current JRE 6 or JRE 7, and you shouldn’t care and rush with this implementation.
Same comments about Firefox ESR – it will work as before. But not for a long time period until next ESR release branch merge.

If your company is following BYOD approach, multiple platforms in use like MacOS, you seek for Chrome browser support – at this stage only Windows platform is covered well. Others – are not officially certified, including “Safari on MacOS”. You can still use it, but you need to accept the “head ache” process as described by the example above. And that’s not about Safari only, as other browsers, like Firefox and Chrome, behaves in the same way on MacOS.

And not all Apps DBAs will agree to manually fix ICX_FORMS_LAUNCHER every time AutoConfig is executed. Though there might be User level exceptions for a defined end user base list.

Categories: DBA Blogs

Partner Webcast – DevOps and Cloud Native Microservices on Oracle Cloud

DevOps is a culture, movement or practice thatemphasizes the collaboration and communication of both software developers andother information-technology (IT) professionals while automating the...

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

Partner Webcast – DevOps and Cloud Native Microservices on Oracle Cloud

DevOps is a culture, movement or practice that emphasizes the collaboration and communication of both software developers and other information-technology (IT) professionals while automating the...

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

transpose columns to rows

Tom Kyte - Thu, 2017-04-27 05:26
Could you please help me about the below scenario : Two tables, One to Many Relation. I tried by pivot , but pivot can only be used with aggregate functions. master table : members mem_id mname ------ ----- 1 abc 2 xyz 3 qrs Det...
Categories: DBA Blogs

Index script

Tom Kyte - Thu, 2017-04-27 05:26
I have two databases with indexes for same column different. I want a script that compares these two databases and generates the rename index script for the target database. Can you provide me a sample? Need to join dba_indexes and dba_ind_columns fr...
Categories: DBA Blogs

Incremental stats on large partitioned table

Tom Kyte - Thu, 2017-04-27 05:26
we have our a reporting database which is being cloned from our production database daily. The source database is a mission critical database where we can't make any changes(can't set table preferences for stats gathering ). We have a range partition...
Categories: DBA Blogs

Need assistance on calling shell script from oracle triggers.

Tom Kyte - Thu, 2017-04-27 05:26
Hi Tom, REQUIREMENT: Need to send database report before and after database bounce. Test Case: 1) Created a scheduler job. <code> BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => 'RUN_CHECK_REPORT', schedule_name => 'HEALTHCHE...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs