Feed aggregator

ZDLRA Database Patch Rollback fails with : Re-link fails on target "ioracle"

Arun Bavera - Mon, 2017-01-23 09:19
I was helping my colleague to apply DB patch for ZDLRA metadata repository

It required some conflicting duplicate patch to be rolled back but the roll back failed in between during make operation.
----------------------------------------------------------------------------
RollbackSession rolling back interim patch '20788771' from OH '/u01/app/oracle/product/12.1.0.2/dbhome_1'
 Patching component oracle.rdbms.deconfig, 12.1.0.2.0...
 Patching component oracle.ldap.rsf, 12.1.0.2.0...
 Patching component oracle.rdbms.rsf, 12.1.0.2.0...
 Patching component oracle.rdbms, 12.1.0.2.0...
 Patching component oracle.rdbms.dbscripts, 12.1.0.2.0…

RollbackSession removing interim patch '20788771' from inventory
Make failed to invoke "/usr/bin/make -f ins_rdbms.mk ioracle ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1"....'/u01/app/oracle/product/12.1.0.2/dbhome_1/lib//libserver12.a(krsp.o): In function `krsp_avm_shutdown':
krsp.c:(.text+0x8aca): undefined reference to `krr_find_log_end1'
/u01/app/oracle/product/12.1.0.2/dbhome_1/lib//libserver12.a(ksdxt.o):(.rodata+0x19d0): undefined reference to `krrUt1'
/u01/app/oracle/product/12.1.0.2/dbhome_1/lib//libserver12.a(kbrssv.o): In function `kbrssvReadFChunk':
kbrssv.c:(.text+0x1b812): undefined reference to `kbrs_resilver'
collect2: ld returned 1 exit status
make: *** [/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/lib/oracle] Error 1

The following make actions have failed :
 Re-link fails on target "ioracle".
----------------------------------------------------------------------------
We were able to find these missing object libraries in $ORACLE_HOME/.patch_storage using the find and nm commands:

Cd $ORACLE_HOME/.patch_storage
find . -name "*.o" -exec nm {} \; | grep krrUt1
Used the "ar"  command to updated the library /u01/app/oracle/product/12.1.0.2/dbhome_1/lib//libserver12.a  with the missing library object files and then ran the make command manually to overcome this problem..

ar r /u01/app/oracle/product/12.1.0.2/dbhome_1/lib//libserver12.a  krsp.o  ksdxt.o  kbrssv.o
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk ioracle

Note:
Also, we had to follow the solution in Note: 2131772.1for making ora.gipcd to up and running

In case if you are using opatch to rollback patch in GRID Home you may have to unlock CRS home for applying patch, follow the note:2136437.1
1. Shutdown cleanly if the clusterware is running in the failed node:
# crsctl stop crs -f
If the above fails because of partially run postpatch and not able to bring down ohasd:
# crsctl disable crs
Ensure all clusterware related process are down and nothing is running from the crs home:
# kill -9
Enable clusterware again:
# crsctl enable crs
2. Unlock the CRS home
# rootcrs.sh -unlock
3. Issue -patch to instantiate and lock the home

# rootcrs.sh -patch
Categories: Development

Fast Refresh materialized view filter help

Tom Kyte - Mon, 2017-01-23 07:26
Hi, I have the following sql and it creates the view successfully. It's only selecting out of t1 right now, but it's a simplified version of my sql. I will select out of the other tables once I get this figure out. <code>create materialized v...
Categories: DBA Blogs

Query used for fetching records from last executed query is taking much time

Tom Kyte - Mon, 2017-01-23 07:26
Hi Tom, We have a situation here, in one our tables a column's value is changing and we are not able to find out which batch job (there are thousands) is responsible for that or which query is doing that. Now i am creating a trigger which will be ...
Categories: DBA Blogs

Oracle Text Contains search with Near is very slow

Tom Kyte - Mon, 2017-01-23 07:26
Hi! There is test environment: analytic table with 15,000,000 of documents. Average size of every document is from 20 to 100 pages. We built Oracle Text Index on this table - type is CONTEXT: CREATE INDEX rco_anal ON files(txt) INDEXTYPE IS ctxs...
Categories: DBA Blogs

Oracle Triggers – The Complete Guide

Complete IT Professional - Mon, 2017-01-23 05:00
Oracle triggers are a handy but often misused function of the Oracle database. Learn all about them in this article. Table of Contents This is a long article, so here’s a table of contents you can use to navigate around the page: What are Oracle Triggers? Types of Triggers in Oracle Reasons to Use Oracle […]
Categories: Development

Quarterly EBS Upgrade Recommendations: January 2017 Edition

Steven Chan - Mon, 2017-01-23 02:05

We've previously provided advice on the general priorities for applying EBS updates and creating a comprehensive maintenance strategy.   

Here are our latest upgrade recommendations for E-Business Suite updates and technology stack components.  These quarterly recommendations are based upon the latest updates to Oracle's product strategies, latest support timelines, and newly-certified releases

You can research these yourself using this Note:

Upgrade Recommendations for January 2017


EBS 12.2
 EBS 12.1
 EBS 12.0
 EBS 11.5.10
Check your EBS support status and patching baseline

Apply the minimum 12.2 patching baseline
(EBS 12.2.3 + latest technology stack updates listed below)

In Premier Support to September 30, 2023

Apply the minimum 12.1 patching baseline
(12.1.3 Family Packs for products in use + latest technology stack updates listed below)

In Premier Support to December 31, 2021

In Sustaining Support. No new patches available.

Upgrade to 12.1.3 or 12.2

Before upgrading, 12.0 users should be on the minimum 12.0 patching baseline

In Sustaining Support. No new patches available.

Upgrade to 12.1.3 or 12.2

Before upgrading, 11i users should be on the minimum 11i patching baseline

Apply the latest EBS suite-wide RPC or RUP

12.2.6
Sept. 2016

12.1.3 RPC5
Aug. 2016

12.0.6

11.5.10.2
Use the latest Rapid Install

StartCD 51
Feb. 2016

StartCD 13
Aug. 2011

12.0.6

11.5.10.2

Apply the latest EBS technology stack, tools, and libraries

AD/TXK Delta 8
Sept. 2016

FND
Aug. 2016

EBS 12.2.5 OAF Update 9
Jan. 2017

EBS 12.2.4 OAF Update 14
Jan. 2017

ETCC
Nov. 2016

FMW 11.1.1.9

Daylight Savings Time DSTv28
Nov. 2016

12.1.3 RPC5

OAF Bundle 5
Note 1931412.1
Jun. 2016

JTT Update 4
Oct. 2016

Daylight Savings Time DSTv28
Nov. 2016



Apply the latest security updates

Oct. 2016 Critical Patch Update

SHA-2 PKI Certificates

SHA-2 Update for Web ADI & Report Manager

Migrate from SSL or TLS 1.0 to TLS 1.2

Sign JAR files

Oct. 2016 Critical Patch Update

SHA-2 PKI Certificates

SHA-2 Update for Web ADI & Report Manager

Migrate from SSL or TLS 1.0 to TLS 1.2

Sign JAR files

Oct. 2015 Critical Patch Update
April 2016 Critical Patch Update
Use the latest certified desktop components

Use the latest JRE 1.8, 1.7, or 1.6 release that meets your requirements

Upgrade to IE 11

Upgrade to Firefox ESR 45

Upgrade Office 2003 and Office 2007 to later Office versions (e.g. Office 2016)

Upgrade Windows XP and Vista to later versions (e.g. Windows 10)

Use the latest JRE 1.8, 1.7, or 1.6 release that meets your requirements

Upgrade to IE 11

Upgrade to Firefox ESR 45

Upgrade Office 2003 and Office 2007 to later Office versions (e.g. Office 2016)

Upgrade Windows XP and Vista to later versions (e.g. Windows 10)



Upgrade to the latest database Database 11.2.0.4 or 12.1.0.2 Database 11.2.0.4 or 12.1.0.2 Database 11.2.0.4 or 12.1.0.2 Database 11.2.0.4 or 12.1.0.2 If you're using Oracle Identity Management

Upgrade to Oracle Access Manager 11.1.2.3

Upgrade to Oracle Internet Directory 11.1.1.9

Migrate from Oracle SSO to OAM 11.1.2.3

Upgrade to Oracle Internet Directory 11.1.1.9



If you're using Oracle Discoverer
Migrate to Oracle Business Intelligence Enterprise Edition (OBIEE), Oracle Business Intelligence Applications (OBIA), or Discoverer 11.1.1.7. Migrate to Oracle Business Intelligence Enterprise Edition (OBIEE), Oracle Business Intelligence Applications (OBIA), or Discoverer 11.1.1.7.

If you're using Oracle Portal
Migrate to Oracle WebCenter  11.1.1.9
Migrate to Oracle WebCenter 11.1.1.9 or upgrade to Portal 11.1.1.6.



Categories: APPS Blogs

Oracle Database Cloud (DBaaS) Performance - Part 2 - Storage

Randolf Geist - Mon, 2017-01-23 01:00
In this second part of this installment I'll focus on the performance figures related to I/O encountered when the corresponding tests were performed on the platform.

IOPS

When running with minimum sized buffer cache, direct and asynchronous I/O enabled, the following average read-only IOPS figures were measured over a period of several days (this is the test described in part three of the "performance consistency" series) .

First, running on a 4 OCPU single instance configuration (8 CPUs / 8 cores as outlined in the previous part) with either four or eight sessions:


Second, running on a 2+2 OCPU two instance RAC configuration (4+4 CPUs / 4+4 cores as outlined in the previous part) with either four or eight sessions:


So for the 8 KB block size the single instance test shows an average of almost 40.000 IOPS for read-only tests, and the two instance RAC even scales to almost 60.000 IOPS on average. These are pretty impressive IOPS figures for a general purpose shared / virtualized environment, and - at least - for the read part - are way above what other DBaaS cloud providers offer out of the box.

It's also worth mentioning that I got the same IOPS results independent from the amount of storage allocated - in contrast to Amazon RDS for example, where even the "Provisioned IOPS" storage class requires you to allocate at least 3 TB of storage in order to get the maximum of 30,000 IOPS. I've repeated the same test setup with the table size inflated to the maximum possible within my test account limitations (so total size of storage allocated close to the 1 TB storage quota) and still got comparable IOPS results to this test that only allocates approx. 1 GB for the 8 KB block size test (total storage allocated approx. 100 GB).

I/O Throughput

In order to measure the maximum I/O throughput corresponding Parallel Execution statements were used to create tables of sufficient size and read them via Parallel Query.

First, running on a 4 OCPU single instance configuration (8 CPUs / 8 cores as outlined in the previous part) at a degree of 16:


Second, running on a 2+2 OCPU two instance RAC configuration (4+4 CPUs / 4+4 cores as outlined in the previous part) at a degree of 16:


So the single instance configuration writes at 120 MB per second, and reads at 640 MB per second, whereas the two instance RAC configuration writes at 80 MB per second and reads at 1.100 MB per second. Clearly the storage layer is read optimized and writes are much slower than reads. The read performance is very good however, and again above what other cloud providers deliver (for example Amazon RDS "Provisioned IOPS" offers a maximum throughput of 320 MB per second, but again only if a sufficient amount of storage is allocated).

I/O Latency

In order to measure the I/O latency the test used above to determine the IOPS rate was modified to use random single block reads ("db file sequential read") instead of asynchronous reads ("db file parallel read"). The reason for this is that from a database perspective the latency for reads performed asynchronously cannot be measured properly, due to the way the database handles the I/O.

The results were the following, this time running on a 2 OCPU (4 CPUs / 4 cores) single instance configuration with 8 KB block size and 8 sessions:



So again rather impressive 0.45 ms average wait time for a random single block read, and the wait event histogram also shows that the performance was very consistent, with almost 75 percent of the waits taking less than 512 microseconds at a rate of more than 16.000 IOPS.

Write Performance

The read-only test above was modified to be 100 percent updates (for more details see here). Since the instance was configured to operate with a minimum sized buffer cache this puts maximum pressure on the database writer to write dirty blocks as fast as possible in order to allow new blocks to be read into the buffer cache.

Running on a 4 OCPU single instance configuration (8 CPUs / 8 cores) with eight sessions the following profile was measured - using NOARCHIVELOG mode and Flashback disabled:


So clearly the database writer couldn't write the dirty blocks quick enough - almost 50 percent of the database time the sessions had to wait for free buffers. This means that write intensive workloads might not work too well and run into these limitations.

Summary

The storage layer is clearly optimized for reads and delivers at least for the read-only tests a very good I/O performance. Write intensive workloads might not work too well if they consistently require more write performance than provided by the service.

Oracle technologies used to analyze the Inaugural Addresses of the Presidents of the United States

Joel Kallman - Sun, 2017-01-22 22:42


The Oracle Database ships with a wonderful technology called Oracle Text.  Most people associate Oracle Text with the mere ability to index documents or data, perform text or phrase queries and get the results back.  But Oracle Text can do so much more.  One of the more interesting features of Oracle Text is the ability to analyze a document and generate a list of themes for the document along with a weight associated with that theme.

Back in 2004, in the era of Oracle HTML DB, Jason Straub on the Application Express development team wrote a demonstration application named Presidential Inaugural Addresses, which used both Oracle HTML DB and Oracle Text.  This application actually shipped with Oracle HTML DB for a time.  This was a fascinating application, which enabled you to view all of the Inaugural Addresses of the Presidents of the United States, identify the top themes associated with those addresses, and even identify which past presidents delivered addresses that had similar dominant themes.

This demonstration application stopped shipping with Oracle Application Express in 2006.  But I thought it might be interesting to dust off this application and improve the UI using Oracle Application Express 5.1.  So that's how I spent this past weekend (let the good times roll).  Using the existing data structures, I was able to create a new Universal-theme based application which turned out pretty elegant.

Please understand - this application and everything associated with it is not a political statement.  Given the relevance of this data at the time of this writing, I simply thought it would be a fascinating way to demonstrate insights into this information, using the Oracle Database, Oracle Application Express, Oracle Text and Oracle JET!

You can run this application HERE.

Oracle Analytics

Tom Kyte - Sun, 2017-01-22 13:06
<code>Hi Tom, I have following requirement, create table sim_history (sim varchar2(30), imsi varchar2(30), start_date date, end_date date ) Insert into SIM_HISTORY (SIM, IMSI, START_DATE, END...
Categories: DBA Blogs

sql query optimization

Tom Kyte - Sun, 2017-01-22 13:06
Hi Tom how can i reduce select statement and how optimize the select query please help on this issue tom?thanks SELECT pa_num, partner_id, product_line FROM IF_LSP_PANUM_MAP ilp WHERE icp.country_code=i_workflow_keys AND NOT EXISTS(SELECT 1 ...
Categories: DBA Blogs

Pl/SQL For loop

Tom Kyte - Sun, 2017-01-22 13:06
Hi Tom, Below is the code structure of what i am trying to do declare lowerl number:= 1; upperl number:= 3; num varchar2(10); begin for i in lowerl..upperl loop num:=num||to_char(lowerl); if i=3 then upperl:=5; end if; end loop; mes...
Categories: DBA Blogs

Global Session Parameter must be true when bulk inserting over a database link using a local function

Tom Kyte - Sun, 2017-01-22 13:06
My actual use case is I need to transfer millions of records over a db link and need to make use of certain functions to transform the data. I am having issues when I try do a bulk insert across a database link and make use of a function inside ...
Categories: DBA Blogs

Binding variables change names each time. How to avoid hard parse?

Tom Kyte - Sun, 2017-01-22 13:06
I use an application, which is built with ODP.NET. This application works with binding variables, but for some reason it uses new names of these variable in each sql call by adding a sequential number at the end of each binding variable: For exam...
Categories: DBA Blogs

Calculate months with two date

Tom Kyte - Sun, 2017-01-22 13:06
Hi there, i am newbie for using function please help to solve it I have tried this code and got error as follow Error : Compilation failed,line 2 (13:32:38) PLS-00103: Encountered the symbol "(" when expecting one of the following: . @ % ; is a...
Categories: DBA Blogs

bind variable does not exist

Tom Kyte - Sun, 2017-01-22 13:06
I am getting below error message when trying to execute F_INSERT_ORDER_Details_DATA funtion <code>ORA-01006: bind variable does not exist ORA-06512: at "ADMIN.F_INSERT_ORDER_DETAILS_DATA", line 37 ORA-06512: at line 5</code> CREA...
Categories: DBA Blogs

How to alter system password in oracle 11g

Tom Kyte - Sun, 2017-01-22 13:06
I have installed oracle 11g before a couple of months but i forgot my system password and also i couldn't able to conn /as sysdba. Could you please suggest the process of resetting the password for user SYSTEM.
Categories: DBA Blogs

Printing a name with out using dbms_output.put_line

Tom Kyte - Sun, 2017-01-22 13:06
Hi Tom, Recently in one of the interview i was asked whether i can print any thing with out using dbms_output.put_line in Pl/SQL I am having a little experience in Pl/SQL , As per my knowledge i think we cannot. But please let me know if there ...
Categories: DBA Blogs

UNDO generated By Index blocks during INSERT

Tom Kyte - Sun, 2017-01-22 13:06
Hi, I am using version- 11.2.0.4 of oracle. I have a query running and its getting slow down during specific time period of the day(10PM to 9AM) which i can also verify from the data present in DBA_HIST_SQLSTAT. The number of rows generated from t...
Categories: DBA Blogs

SQL Bind Variable Support in ADF BC REST

Andrejus Baranovski - Sun, 2017-01-22 10:14
Is not that obvious from Oracle ADF BC REST developer guide how to provide value for bind variable defined directly in the View Object SQL statement. I did research around this and would like to post few hints to make your life easier, if you have same requirement - pass values from REST request to View Object required bind variables. This topic is especially useful, when you want to reuse existing ADF BC implementation for ADF BC REST access.

We are going to use View Object Row Finder. Oracle ADF BC REST developer guide explains how to use Row Finder with View Criteria. In our case we have different situation -  we would like to use Row Finder for required bind variables, referenced directly in SQL statement.

You can't define Row Finder without View Criteria. First trick is to define empty View Criteria, just to be able to define Row Finder - we are not going to use View Criteria functionality, our bind variables are referenced directly in SQL WHERE clause:


Once Row Finder is defined, you are going to see bind variables listed. Keep in mind - this doesn't means bind variables are referenced by Row Finder, they are just listed for possible use. Now main trick - go and define some dummy expression for each bind variable you want to use in REST request (make sure to uncheck - Save expression to groovy file):


This action would generate Groovy expression for each bind variable and list these bind variables under Row Finder. Go to source of View Object to see the structure:


Now remove Groovy expressions assigned to each bind variable - we don't need them, keep only bind variable names assigned to Row Finder:


Visually in the wizard is going to look like there are no changes made - but we keep bind variables under Row Finder tag now:


Bind variables are included directly into SQL statement WHERE clause:


ADF BC REST service is defined in regular way, no special tips here:


This is how REST call looks like: Departments?finder=RESTFilter;depNameVar=IT.locIdVar=1700. We include Row Finder name and two bind variables with values:


In the background we could check ADF BC log, where it prints SQL statement with both bind variables assigned with values:


Download and browse sample application ADFBCRestApp from my GitHub repository - jetcrud.

Next Chapter … Time for a Change

DBASolved - Sat, 2017-01-21 20:06

It has been about three months since I’ve published a post. Well this time it has been a mix of work and decision making on my next chapter in my career that has kept me from posting. Hopefully, in the near future I will have more time to publish articles on topics I really want to discuss.

For the people close to me, many of you already know what this change is. I appreciate that you have kept it quite while all the details were worked out.

Before I get into what the next chapter of my career is, I have to say thanks to the management and team members at Accenture Enkitec Group (Enkitec/AEG). It has been a pleasure to work with everyone at Enkitec/AEG over the last couple of years. You truely are a great group of individuals and I will miss working daily with many of you. AEG is such a brain trust of people, it is almost hard to leave! I know that this will continue to be the trend at AEG.

Now what is the next chapter?

As of late January 2017, I have decided to leave Enkitec/AEG in pursuit of an opportunity that I feel is a good mix of my technical skills and allow me to grow in a challenging direction. Come Feburary 6th, 2017, I will be starting my new role as Senior Principal Product Manager on the GoldenGate team at Oracle. This position will allow me to work with a technology I’m very interested in and help shape the path forward for it. After all we know “cloud” is the next best thing right? How are you getting your data into or out of that “cloud”? Well, I will be part of the team that will help provide a path to do that. I’ll have to keep you posted on the technology as it evolves.

Now that you know where I’m going, just a few house keeping items to take care of. I’ve already said thanks to my friends at Enkitec/AEG. Without your support on many fronts, I wouldn’t be where I’m at as I proceed on this new chapter. Again, I greatly appreciate it.

For the community as a whole, many of you know that I’m an ACE Director (ACED) or at least was an ACED … LOL. Well, just like many of former ACEDs, when you go to Oracle you have to give it up. So, as of January 20, 2017, I resigned my ACED status and now listed as an ACE Alumni.

Blog site! I plan on keeping dbasolved.com up and running. I will be posting more article on GoldenGate here after starting with Oracle. So keep checking back to see if I’ve posted anything new. After all, GoldenGate is starting to become a hot topic!

I hope you will join me in my excitement on this new chapter of my career with Oracle and the GoldenGate team! I look forward to seeing many of you out and about within the community as I take on this new chapter.

Enjoy!

about.me:http://about.me/dbasolved


Filed under: Golden Gate
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator