Feed aggregator

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.

Example CCA Generator

Angelo Santagata - Mon, 2017-01-09 17:11
Geertjan, a cool JET PM has created a CCA generator which can be used with JET. Now the nice thing about this is not that it allows you to preconfigure and generate JET forms but that its a good example of how you can build a) a JET CCA Generator and b) something reusable. CCAs are a really important feature of JET going forward, they will allow us to embed JET "applets" , in JET projects, ABCS and in the future in other Oracle products
Check it out Jet Builder GITHUB

Reality hits me home, with aging parents and uncertainties of life. I learn about OPLL

Arvind Jain - Mon, 2017-01-09 16:11
I never thought that I will be writing out something way outside my comfort zone of IT technology.

But a recent mishap accident with my father has made me realize that there are far more difficult and significant things in life than I had yet to confront and I was not prepared to handle it alone.


Diwali the festival of lights had just gone by and we were all in a cheerful mood. My brother and his family were back home in India and the grand parents were having a good time with grand kids. My dad has just returned back to India after a pleasant three month stay with me here in USA.  


Then came the scary Halloween trick for me. On a fateful day of Oct 30th 2016, just after Diwali in Bhilai, India my dad slipped and fell and the back of his neck hit the swing base. It was not a major accident by external measures, no external bleeding or wound, but it was severe enough to compress the nerves to a point were he could not move or stand up. He was lying on the floor, lifeless and could not move his both legs and one hand.


He was fortunately able to make an audible alarm and was picked up , lifted and transported to hospital.  He was immediately admitted to Bhilai Sec-9 hospital in Chhattisgarh, India. There it was diagnosed that he already had a case of OPLL () and this fall and immobility are symptoms of  a final breakdown.


OPLL is a calcification of the soft tissues that connect the spinal bones which results in a narrowing of the spinal canal and compression of the cervical spinal cord.


The cause of OPLL is unclear and I could not get a certain answer from anyone, but was told that people of Asian heritage especially Japanese have a higher likelihood of developing OPLL. Also more men seem to be affected than women.
OPLL SymptomsMost patients with OPLL are asymptomatic with no symptoms at all, but others may experience mild pain and numbness in the arms and/or legs to complete numbness in the extremities. The symptoms are similar to those of cervical cord compression.
Treatment of OPLLThis depends on the stage of OPLL and in my case , my dad is at stage 4 and that is the severest stage. Conservative treatment like activity modification and exercises may help relieve the pain caused by OPLL but I was told that open spine surgery is required.


If pressure is not relieved on the spinal cord then future damage and loss of neurological function is very much possible. At sec 1 Bhilai hospital, my brother was told that this may affect respiratory functions as well. So they advised immediate surgery and there is a major risk when you open up the spine.




Since then in last 2 months we have gone through many things and are working on getting this thing taken care of. It is a big help that my brother is back home in India and can execute on many on site things.  Simple things like opening zipper or buttons that we take granted for in life start to seem like mammoth tasks. What you can do in person on site, is a significant thing in such circumstances.


I would like to thanks and credit the following websites for some of my research. Indian Government portal surprisingly had a good amount of research papers at NCBI.


https://www.emoryhealthcare.org/orthopedics/ossification-posterior-longitudinal-ligament.html
http://www.medscape.com/viewarticle/739292
https://www.ncbi.nlm.nih.gov/pubmed/16306836


We have undergone surgery since then and in future articles I will share more details, but if anyone is in similar situation, I will be glad to share detailed notes. I was helped by many known and unknown people so my heartfelt thanks goes out to them and I plan to pay it forward.


Thanks

Arvind

Difference between Rownum and level in connect by (it is giving different result)

Tom Kyte - Mon, 2017-01-09 13:46
Hi, Hope your doing great! Can you kindly guide me with the difference between the rownum and level in connect by, I though it will give same result but it is little confusing. Can you please teach me in steps how it works. Thanks! drop tabl...
Categories: DBA Blogs

Check constraint violated while loading JSON doc into DB

Tom Kyte - Mon, 2017-01-09 13:46
<code>Team, Could you please help me to understand why this JSON document load got failed with check constraint enabled? but this is a Valid JSON document, validated it through http://jsonlint.com/ portal. for time being, we are able to just...
Categories: DBA Blogs

Executing .sql file in Pl/sql procedure

Tom Kyte - Mon, 2017-01-09 13:46
Hi , I am trying to execute a set of insert statements in a pl/sql block. However these insert scripts vary time to time. Hence i would like to save them in some location as .sql file and execute in pl/sql So i would like to know if it is poss...
Categories: DBA Blogs

issue with the usage of the oracle external table

Tom Kyte - Mon, 2017-01-09 13:46
Hi tom, I have an oracle external table in my database and i am running my database in linux operating system.I have a directory where i will be placing the (.csv) file and loading into the external table.But there is an issue with the externa...
Categories: DBA Blogs

Setting up Transport Layer Security/Secure Sockets Layer for Managed ODP.NET

Christian Shay - Mon, 2017-01-09 08:53

[I was chatting with ODP.NET product manager, Alex Keh, and he let me know about some known issues that developers currently face when using managed ODP.NET with Transport Layer Security/Secure Sockets Layer (TLS/SSL) wallets. I asked him for more details and he wrote up the step by step walkthrough you see below. If you have questions about the steps provided below, please post to the ODP.NET OTN forum for assistance. -CS]


Setting up managed ODP.NET Transport Layer Security/Secure Sockets Layer (TLS/SSL) wallets correctly can be tricky for those that don't do it on a regular basis. Microsoft Windows now restricts wallets from using the MD5 algorithm. Oracle wallets may have been generated with this algorithm as that was the default option in Oracle Public Key Infrastructure (orapki) utility 12.1 and earlier.

When you setup TLS/SSL and encounter an "ORA-0052: Failure during SSL handshake" error combined with a 0x80004005 error code and first inner exception "A SSPI-call failed" and second inner exception "A token sent to the function is invalid", then it is very likely that Microsoft Security Support Provider Interface (SSPI) rejected your Oracle Wallet, such as when MD5 is used. This is a failure on the handshake. You can resolve this error by using the SHA-2 algorithm instead. We'll describe how shortly.

If the second inner exception instead indicates "The credentials supplied to the package were not recognized", it is possible the user certificate was generated without a certificate authority (CA). You can resolve this error by using orapki to generate a CA/root certificate and then regenerating your user wallet/certificate to point to this new CA/root certificate. More info on how to do this is below.

Orapki refers to orapki.exe. This utility is part of full Oracle client (admininstrator) installations. It is not included with Oracle Instant Client. The utility is only needed to setup up the wallet; it is not necessary to deploy it with the wallet.

The steps below will regenerate your Oracle Wallet using orapki. Any orapki version can be used to generate the wallet with these instructions.


1. Create root wallet, for example, a CA wallet.

orapki wallet create -wallet ./root -pwd yourpassword

2. Add a self-signed certificate (CA certificate) to the root wallet.

orapki wallet add -wallet ./root -dn 'CN=yourroot' -keysize 1024 -self_signed -validity 3650 -pwd yourpassword -sign_alg sha512

3. Export the self-signed certificate from the wallet.

orapki wallet export -wallet ./root -dn 'CN=yourroot' -cert ./root/b64certificate.txt -pwd yourpassword

4. Create a user wallet, for example, a customer wallet.

orapki wallet create -wallet ./user -pwd yourpassword -auto_login

5. Add a certificate request.

orapki wallet add -wallet ./user -dn 'CN=clientshostname' -keysize 1024 -pwd yourpassword -sign_alg sha512

6. Export the certificate request.

orapki wallet export -wallet ./user -dn 'CN=clientshostname' -request ./user/creq.txt -pwd yourpassword

7. Create a certificate issued by a CA.

orapki cert create -wallet ./root -request ./user/creq.txt -cert ./user/cert.txt -validity 3650 -pwd yourpassword -sign_alg sha512

8. Add a trusted certificate (CA certificate) to the wallet. This example assumes the same CA for both the client and server wallets.

orapki wallet add -wallet ./user -trusted_cert -cert ./root/b64certificate.txt -pwd yourpassword

9. Add a user certificate.

orapki wallet add -wallet ./user -user_cert -cert ./user/cert.txt -pwd yourpassword -sign_alg sha512

10. Display contents of user wallet.

orapki wallet display -wallet ./user -pwd yourpassword

11. Create a server wallet.

orapki wallet create -wallet ./server -pwd yourpassword -auto_login

12. Add a server certificate request.

orapki wallet add -wallet ./server -dn 'CN=servershostname' -keysize 1024 -pwd yourpassword -sign_alg sha512

13. Export the certificate request.

orapki wallet export -wallet ./server -dn 'CN=servershostname' -request ./server/creq.txt -pwd yourpassword

14. Create a server certificate issued by a CA.

orapki cert create -wallet ./root -request ./server/creq.txt -cert ./server/cert.txt -validity 3650 -pwd yourpassword -sign_alg sha512

15. Add a trusted certificate (CA certificate) to the server wallet. This example assumes the same CA for both the client and server wallets.

orapki wallet add -wallet ./server -trusted_cert -cert ./root/b64certificate.txt -pwd yourpassword

16. Add a user_cert certificate for the server wallet.

orapki wallet add -wallet ./server -user_cert -cert ./server/cert.txt -pwd yourpassword -sign_alg sha512

17. Display contents of server wallet.

orapki wallet display -wallet ./server -pwd yourpassword


Pages

Subscribe to Oracle FAQ aggregator