Feed aggregator

ORA-01034: ORACLE NOT AVAILABLE

Tom Kyte - Wed, 2016-09-07 22:26
connect system password : password. i got following errors ORA-01034: ORACLE NOT AVAILABLE ORA-27101: shared memory realm does not exist
Categories: DBA Blogs

How to optimize these two Queries

Tom Kyte - Wed, 2016-09-07 22:26
How Can we optimize the below query MERGE INTO Department dept USING (SELECT DISTINCT emp_name, hire_date FROM employees WHERE emp_id = '10') emp_details ON (dept.emp_name = emp_details.emp_name AND emp_details.hire_date IS NOT NULL...
Categories: DBA Blogs

Recover tablespace on anothe host or database

Tom Kyte - Wed, 2016-09-07 22:26
Hello, friends! I can't find any info to solve my issue. We need to restore some old data from table. Full database is too large and we don't have much space for restore full database. That's why we need to restore only tablespace or (ideally) t...
Categories: DBA Blogs

Compress for OLTP

Tom Kyte - Wed, 2016-09-07 22:26
Hi, We have reached 90% of our tablespace (disk space) full, and we thought to compress the tables so that we may free up some space. <code> select segment_name, segment_type, bytes/1024/1024 MB from user_segments where segment_type='TA...
Categories: DBA Blogs

oracle pl/sql

Tom Kyte - Wed, 2016-09-07 22:26
how to breck large database table result in reguller interwol
Categories: DBA Blogs

New OA Framework 12.2.5 Update 6 Now Available

Steven Chan - Wed, 2016-09-07 16:27

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:

Screenshot EBS 12.2.5 OAF Bundle Patch 6

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.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 includes new fixes for the following critical issues:

  • The table header content overlapped with the records present at the immediate row when there is a specific personalization on the page.
  • In environments with Multi-Org Access Control (MOAC) enabled, the List of Values (LOV) search returns empty results with a newly added search query filter while creating a personalized view.
  • On specific JTT Interop pages, the LOV search window opens up only on double click if the LOV is dependent on the Partial Page Refresh (PPR) enabled field.

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

Related Articles

Categories: APPS Blogs

Ask Tom table about NOLOGGING and redo generation

Bobby Durrett's DBA Blog - Wed, 2016-09-07 14:34

I was googling for things related to NOLOGGING operations and found this useful post on the Ask Tom web site: url

There is a nice table in the post that shows when insert operations generate redo log activity. But it isn’t formatted very well so I thought I would format the table here so it lines up better.

Table Mode    Insert Mode     ArchiveLog           mode result
-----------   -------------   -----------------    -----------
LOGGING       APPEND          ARCHIVE LOG          redo generated
NOLOGGING     APPEND          ARCHIVE LOG          no redo
LOGGING       no append       ""                   redo generated
NOLOGGING     no append       ""                   redo generated
LOGGING       APPEND          noarchive log mode   no redo
NOLOGGING     APPEND          noarchive log mode   no redo
LOGGING       no append       noarchive log mode   redo generated
NOLOGGING     no append       noarchive log mode   redo generated

All of this is from Ask Tom. My contribution here is just the formatting.

I ran a couple of tests whose results agree with this table. I ran insert append on a database that was not in archivelog mode and the insert ran for the same amount of time with the table set for LOGGING as it did with the table set for NOLOGGING. I ran the same test on a database that is in archivelog mode and saw a big difference in run time between LOGGING and NOLOGGING. I didn’t prove it but I assume that the redo generation caused the difference in run time.

No archivelog and logging:

insert /*+append*/ into target select * from source;

64000 rows created.

Elapsed: 00:00:00.36

No archivelog and nologging:

insert /*+append*/ into target select * from source;

64000 rows created.

Elapsed: 00:00:00.38

Archivelog and logging:

insert /*+append*/ into target select * from source;

64000 rows created.

Elapsed: 00:00:00.84

Archivelog and nologging:

insert /*+append*/ into target select * from source;

64000 rows created.

Elapsed: 00:00:00.53

I haven’t tested all the table options but I thought it was worth formatting for my reference and for others who find it useful.

Bobby

Categories: DBA Blogs

Speaking on Oracle OOW'16 - MCS, JET, ACS, JCS and MAF

Andrejus Baranovski - Wed, 2016-09-07 13:08
With Oracle Open World'16 around the corner, I have prepared demo use case including Mobile Cloud Service (MCS), JavaScript Extension Toolkit (JET), Application Cloud Service (ACS) and Java Cloud Service (JCS). I will describe what Oracle Cloud offers to implement end-to-end enterprise solution.

This year I will be speaking on two sessions.

- End-to-End Cloud: Oracle Java Cloud, Oracle Mobile Cloud Service, Oracle MAF, and Oracle JET [CON2388]

Monday, Sep 19, 12:30 p.m. - 1:15 p.m. | Moscone West - 2012

I will be co-presenting and talking about Oracle JET Hybrid implementation:

- Building Enterprise-Grade Mobile Apps with Oracle JET and Cordova [CON5731]

Thursday, Sep 22, 12:00 p.m. - 12:45 p.m. | Moscone West - 2016

Demo use case will be based on JET application running on Application Container Cloud and integrated with Mobile Cloud Service (MCS) REST services:


There will be JET Hybrid application listening for MCS notifications:


You will learn how to process notifications:


Display and synch data from MCS in JET Hybrid application:


Functionality to be described during [CON2388] session:

MAF:

- Integration with MCS

JCS:

- ADF BC REST development and deployment
- Security implementation

ACS:

- JET application depoyment process with Node.JS application

JET:

- JET application implementation tips & tricks
- JET oAuth security integration with MCS
- JET and REST calls
- JET and MCS notifications

MCS:

- Custom API implementation tips & tricks
- Security configuration
- MCS DB API
- MCS Notifications API
- MCS Connector API to call ADF BC REST

AIOUG Sangam'16 - India's largest Annual Oracle Confernece - DON'T MISS IT

Syed Jaffar - Wed, 2016-09-07 07:53
AIOUG Sangam'16, India's largest annual Oracle conference, to be held in Bangalore this year during 11th and 12th November.

I am glad and super excited to present and attend the conference. I am going to present the following two sessions:


  • Exadata Migration Recipes - Secret Sauce for a Tasty Migration
Abstract
The purpose of this presentation is to provide real-world augmented database migration procedures that allowed us to successfully achieve the migrations of 54 business critical databases to Exadata x5-2 of different sizes and Operating Systems. The presentation will layout the different possible migration technologies that meets the business demands such as Data Pumps, RMAN, XTTS with Incremental Backups.  The presentation will also highlight and focus on the challenges that were encountered and their solutions. A very specific scenario will also be presented: Cross platform Oracle EBS migration of 30TB size.
  • Cost Optimization and Better Database Management with Oracle 12c Multitenant A 360 Degree Overview
Abstract
Organizations who deploy and manage several numbers of standalone databases in their complex environments typically face the uphill task of better (ease) database management and also Cost optimization. Oracle 12c Multitenant got the ability to consolidate databases, which addresses the major concerns of those Organizations and provide the easier Database management to the DBAs. The presentation will focuses on the key advantages and benefits of 12c Multitenant features, and how one can reduce the IT cost as well as manage their databases efficiently. Outlines the architecture, deployment scenarios, easy management of Multitenant features.


Why should you attend
If you are around in India during those dates, I strongly recommend you to avail the opportunity to meet and learn from many international stars. AIOUG really did a tremeounds job to bring famous Oracle personalities together on board for this year's event.I am really honored and indeed my previlege to present and listen to other fellow speakers.

Visit the website to for more details: enrollment, view list of topics and spearks etc http://www.aioug.org/sangam16/

Look forward to seeng you at the event!


How to solve JSchException Algorithm negotiation fail. And get logging out of JSch in SoapUI.

Darwin IT - Wed, 2016-09-07 05:43
I was so glad with my SoapUI solution to SFTP files to a server. But so dissapointed I couldn't have it working at my customer.

After I changed the log entries to log with e.message, I got the line:
Wed Sep 07 11:17:43 CEST 2016:INFO:JSchException Algorithm negotiation fail

Now I needed more information than that. But the hint is at least that there is a mismatch in the available cyphers client side verses server side.

So first I wanted to get more logging out of Jsch. It turns out that it has it's own Logger framework, but the bright side of that is that you can easily wrap your own logging mechanism. In the case of SoapUI it is log4j. So create a java project with the libraries jsch-0.1.54.jar and from the SoapUI libs: log4j-1.2.14.jar. Then I created the following class file from an example from the answer in this stackoverflow question.

My version is:
package nl.darwin.jsch.log;


import com.jcraft.jsch.Logger;

/**
* Class to route log messages generated by JSch to Apache Log4J Logging.
*
* @author mrabbitt
* @see com.jcraft.jsch.Logger
*/
public class JSchLog4JLogger implements Logger {
private org.apache.log4j.Logger logger;

/**
* Constructor with custom category name
*
* @param logger the logger from Apache Log4J.
*/
public JSchLog4JLogger(org.apache.log4j.Logger logger) {
this.logger = logger;
}

/**
* Default constructor
*/
public JSchLog4JLogger() {
this(org.apache.log4j.Logger.getLogger(Logger.class.getName()));
}

/* (non-Javadoc)
* @see com.jcraft.jsch.Logger#isEnabled(int)
*/
public boolean isEnabled(int level) {
switch (level) {
case DEBUG:
return logger.isDebugEnabled();
case INFO:
return logger.isInfoEnabled();
case WARN:
return logger.isInfoEnabled();
case ERROR:
return logger.isInfoEnabled();
case FATAL:
return logger.isInfoEnabled();
}
return false;
}

/* (non-Javadoc)
* @see com.jcraft.jsch.Logger#log(int, java.lang.String)
*/
public void log(int level, String message) {
switch (level) {
case DEBUG:
logger.debug(message);
break;
case INFO:
logger.info(message);
break;
case WARN:
logger.warn(message);
break;
case ERROR:
logger.error(message);
break;
case FATAL:
logger.fatal(message);
break;
}
}
}

Then Jar it and add it to the bin/ext older of SoapUI (like in the previous blog post).
Now a simple extra line is needed and an import in your groovy script :
import nl.darwin.jsch.log.JSchLog4JLogger
...
JSch.setLogger(new JSchLog4JLogger(log))
JSch ssh = new JSch()

So simply set the logger on the JSch class, before the instantiation. Then the logging of JSch appears in the SoapUI logging, as easy as that.
It turned out that the server required the use of aes256-ctr, while the jre of SoapUI (which is Java 7 in SoapUI 5.2.1) has limited JCE policy. As is suggested here.

You can download the unlimited JCE policies here:
JDK
Unlimited JCE Download
JDK 1.6http://www.oracle.com/technetwork/java/javase/downloads/jce-6-download-429243.htmlJDK 1.7http://www.oracle.com/technetwork/java/javase/downloads/jce-7-download-432124.htmlJDK 1.8http://www.oracle.com/technetwork/java/javase/downloads/jce8-download-2133166.html
For SoapUI, download the JDK 1.7 policy. Go to your SoapUI Home folder, and navigate to the security library folder within the JRE. For instance: c:\Program Files\SmartBear\SoapUI-5.2.1\jre\lib\security.

Unzip the JCE to a new folder UnlimitedJCEPolicy within the security folder. Create a another backup folder like LimitedJCEPolicy and copy the jars US_export_policy.jar and local_policy.jar to the LimitedJCEPolicy folder. And copy the corresponding files from UnlimitedJCEPolicy to the security folder, replacing the original ones.

Restart SoapUI and you're good to go.


Use SoapUI to test your SFTP based services

Darwin IT - Wed, 2016-09-07 04:13
SoapUI is my favorite tool to do unit tests. I'd try to keep my self to test based development and build up tests along with the development service. For SOAP or REST based services this goes quite intuitively using SoapUI. For database driven it is a little harder, but SoapUI has a nice JDBC activity, that supports DML as well as callable statements as stored procedures.

But for files and especially SFTP its a little more complicated. For a while I'm working on a filebased integration with SAP as source system.

I configured and defined the SOASuite FTP adapter to use my SSH user (oracle) on my VirtualBox VM. Until now I tested it using the SFTP/SCP client from MobaXTerm (enthousiastically recommended: download here). But not so handy for unit tests.

I wanted to automate this using SoapUI. With some searching I found that JCraft Java Secure Channel library was the best and easiest option. I did take a look at Apache Commons Net. But couldn't get it to work so easily. Download the jsch-0.1.54.jar (or newer) file and copy it to the ./bin/ext folder in your SoapUI home:


And restart SoapUI.

Create a new empty SoapUI project, create a TestSuite called something like 'Utilities' and a TestCase called 'TC-FTP'. Add the following properties to the TestCase:

Property
Value
ftpHostdarlin-vce-dbftpPort22ftpUsernameoracleftpPasswordwelcome1localFilePathd:/Projects/2016MySapProject/ExampleFiles/SAP-MESSAGE.XMLremoteFileDir/home/oracle/SapHR/in
In the TestCase add a Groovy Script called FTP add the script below. I took the example from snip2code.com (also found elsewhere) and refactered it to:
//Send Files to SSH Location
//
//Download jsch-0.1.54.jar from http://www.jcraft.com/jsch/ and copy it to SoapUI-Home/bin/ext location
//Example from: https://www.snip2code.com/Snippet/413499/SoapUI-Groovy-Script-compatible-SFTP-fil

//import org.apache.commons.net.ftp.FTPSClient
import com.jcraft.jsch.*
//
// Properties
//
def testCase = testRunner.testCase;


def String ftpHost = testCase.getPropertyValue("ftpHost")
def int ftpPort = testCase.getPropertyValue("ftpPort").toInteger()
def String ftpUsername = testCase.getPropertyValue("ftpUsername")
def String ftpPassword = testCase.getPropertyValue("ftpPassword")
def String localFilePath = testCase.getPropertyValue("localFilePath")
def String remoteFileDir = testCase.getPropertyValue("remoteFileDir")
//
//
Session session = null
Channel channel = null
try {
log.info("Starting sftp upload process")
JSch ssh = new JSch()

session = ssh.getSession(ftpUsername, ftpHost, ftpPort)
session.setConfig("StrictHostKeyChecking", "no"); //auto accept secure host
session.setPassword(ftpPassword)
session.connect()
log.info("Connected to session")

channel = session.openChannel("sftp")
channel.connect()
log.info("Connected to channel")

ChannelSftp sftp = (ChannelSftp) channel;
sftp.put(localFilePath, remoteFileDir);
log.info("File Uploaded " + localFilePath + " TO " + remoteFileDir)

} catch (JSchException e) {
e.printStackTrace()
log.info("JSchException " + e.message)
} catch (SftpException e) {
e.printStackTrace()
log.info("SftpException " + e.message)
} finally {
if (channel != null) {
channel.disconnect()
log.info("Disconnected from channel")
}
if (session != null) {
session.disconnect()
log.info("Disconnected from session")
}
log.info("sftp upload process complete")
}

Changes I did was to define the input values based on the properties from the testcase. And move the session and channel variable declartions out of the try, to get it accessible from the finally branch. And to replace e.printStackTrace from the logging by e.message, to have a propery message (e.printStackTrace returns null) in the logging.

The reason that I suggest to have it in a separate test cases is to enable it to be called from actual testcases with parameters. To do so add to your test case a call-test case activity:

Set the following properties:

Choose 'Run primary TestCase (wait for running to finish, Thread-Safe)' option as Run Mode.

And provide the property values.

This script copies a file from a file location and uploads it. But I want to be able to insert some runtime specific options to refer to in asserts and later JDBC calls. To check on specific running instances. So I want to be able to adapt the content running in my test case. Actually I want to upload a string fetched from a property, maybe with expanded properties.

So I copied the testcase and groovy activity and adapted the script to:
//Send Files to SSH Location
//
//Download jsch-0.1.54.jar from http://www.jcraft.com/jsch/ and copy it to SoapUI-Home/bin/ext location
//Example from: https://www.snip2code.com/Snippet/413499/SoapUI-Groovy-Script-compatible-SFTP-fil

//import org.apache.commons.net.ftp.FTPSClient
import com.jcraft.jsch.*
import java.nio.charset.StandardCharsets
//
// Properties
//
def testCase = testRunner.testCase;
//
def String ftpHost = testCase.getPropertyValue("ftpHost")
def int ftpPort = testCase.getPropertyValue("ftpPort").toInteger()
def String ftpUsername = testCase.getPropertyValue("ftpUsername")
def String ftpPassword = testCase.getPropertyValue("ftpPassword")
def String fileContent = testCase.getPropertyValue("fileContent")
def String remoteFile = testCase.getPropertyValue("remoteFile")
//
Channel channel = null
Session session = null
try {
log.info("Starting sftp upload process")
JSch ssh = new JSch()

session = ssh.getSession(ftpUsername, ftpHost, ftpPort)
session.setConfig("StrictHostKeyChecking", "no"); //auto accept secure host
session.setPassword(ftpPassword)
session.connect()
log.info("Connected to session")

channel = session.openChannel("sftp")
channel.connect()
log.info("Connected to channel")

ChannelSftp sftp = (ChannelSftp) channel;

byte[] fileContentBytes = fileContent.getBytes(StandardCharsets.UTF_8)
InputStream fileInputStream = new ByteArrayInputStream(fileContentBytes);
log.info("Start uploaded to " + remoteFile)
sftp.put(fileInputStream, remoteFile);
log.info("File Content uploaded to " + remoteFile)

} catch (JSchException e) {
e.printStackTrace()
log.info("JSchException " + e.message)
} catch (SftpException e) {
e.printStackTrace()
log.info("SftpException " + e.message)
} catch (Exception e) {
e.printStackTrace()
log.info("Exception " + e.message)
} finally {
if (channel != null) {
channel.disconnect()
log.info("Disconnected from channel")
}
if (session != null) {
session.disconnect()
log.info("Disconnected from session")
}
log.info("sftp upload process complete")
}

here the lines and related properties:
def String localFilePath = testCase.getPropertyValue("localFilePath")
def String remoteFileDir = testCase.getPropertyValue("remoteFileDir")

are changed to:
def String fileContent = testCase.getPropertyValue("fileContent")
def String remoteFile = testCase.getPropertyValue("remoteFile")
Then the lines:
  byte[] fileContentBytes =   fileContent.getBytes(StandardCharsets.UTF_8)
InputStream fileInputStream = new ByteArrayInputStream(fileContentBytes);

convert the fileContent property value to an InputString. And that is given as an input to the statement sftp.put(fileInputStream, remoteFile);. Notice that since we upload file content, we need to provide a remoteFile path, including file name, insead of a remote directory. And that we need an extra import java.nio.charset.StandardCharsets.

It would be nice if the guys from SmartBear add both put and get as a seperate activity.  

JSON_VALUE() and JSON_TABLE(...COLUMNS...NUMBER...) honour NLS_NUMERIC_CHARACTERS while they shouldn't.

Tom Kyte - Wed, 2016-09-07 04:06
The following shows that the same (JSON) table data and the same queries yield different results when different NLS_NUMERIC_CHARACTERS are used. The above sentence is no surprise, obviously, but in this case I consider the behavior to be wrong; Plea...
Categories: DBA Blogs

Oracle database not responding

Tom Kyte - Wed, 2016-09-07 04:06
We have a Java application that uses a connection pool with Oracle database, during the week these conections are frequently refreshed (closed and opened again), the problem is at some point (after two or three days) the application is unable to acqu...
Categories: DBA Blogs

connection issue

Tom Kyte - Wed, 2016-09-07 04:06
Hi Tom, I have deployed Oracle RDBMS software & created new database in Azure cloud.And also created Application users in this database. If I connect application user using SQL Developer I am getting error. IO Error: Connection reset ...
Categories: DBA Blogs

Checkpoints

Tom Kyte - Wed, 2016-09-07 04:06
Under what conditions checkpoint occurrs
Categories: DBA Blogs

UTL_FILE.FCOPY

Tom Kyte - Wed, 2016-09-07 04:06
I am having a problem using FCOPY to make a few copies of a file that I've created with UTL_FILE.PUT_LINE. The goal is to generate a txt file and dump into a directory .../output_1/file_1.txt and then copy & rename this file into another directory...
Categories: DBA Blogs

export jobs hang db

Tom Kyte - Wed, 2016-09-07 04:06
Hi team, In our production database we are using logical backup and there is 20 users and the size of data 200 GB each user So it is configure mid night but export started after some time my entire database getting very slow it is find when we ...
Categories: DBA Blogs

Partner Webcast - Enterprise Manager 13c Managing Hybrid Cloud as One

As organizations seek innovation by adopting public, private, and hybrid clouds, many of them are running up against formidable barriers. In some cases they find that the promised innovations of...

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

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

Categories: DBA Blogs

Oracle CPQ Cloud, the Gift that Keeps Giving, at Oracle OpenWorld 2016

Linda Fishman Hoyle - Tue, 2016-09-06 23:05

A Guest Post by Chris Haussler, Senior Principal Product Manager, Oracle (pictured left)

In the 1920s, an advertising salesman delivered the slogan. The gift that keeps giving, to market a record player. The idea was that if you gave this record player as a gift, the recipient would think of you every time he used it. Pretty clever.

What does this have to do with software? I’ll go out on a limb: CPQ Cloud is the front-office software gift that keeps giving. Like the record player, CPQ Cloud’s customers earn incrementally more benefit the more they use it. Don’t believe me?

Proof of Benefit

As you’d expect, companies that invest in front-office transformation tools, such as marketing automation, SFA, and CPQ, get benefits from the increased efficiency that these tools bring to their processes. But after that initial efficiency windfall, it’s important not to get complacent. The potential opportunity is much greater. Persistent use of CPQ Cloud not only helps sales reps serve customers day-to-day, it also builds a storehouse of information about buying behaviors and product mix. This data can be used to optimize product offerings, sales processes, and channel sell-through.

The result: better sales resource utilization, improved margin, and superior market competitiveness.

It Keeps on Giving

But here’s how CPQ Cloud continues its gifting ways. As markets change, the opportunities to review and retune CPQ Cloud continue, offering incremental improvements again and again. CPQ Cloud administration offers the easy-to-use tools to get these changes to market quickly. After 15+ years in the market, we’ve realized that the best CPQ Cloud customers use the tool not only for sales automation, but to drive continual growth and profit.

Key Sessions at OpenWorld

At Open World 2016, CPQ Cloud offers content for customers at every stage: whether new to sales transformation or looking for the next step in their sales optimization journey.

For beginners in every industry, we recommend a survey of sessions to see how Oracle CPQ Cloud can be combined with other products in the Oracle portfolio:

  • Maximize Results from Oracle Sales Cloud and Oracle CPQ Cloud [CON6937]
  • Graduate to full Quote-to-Cash in the Cloud with CPQ Cloud integrated to Oracle ERP and Oracle SCM Clouds [CON7079]
  • Optimize Customer Engagement with a Multi-Pillar Oracle Customer Experience Solution [CON7958]
  • Connect Oracle CPQ Cloud to E-Business Suite [CON7078]

Financial Services and Communications companies have sessions that demonstrate CPQ Cloud’s ability to serve the distinct sales processes in those industries:

  • Oracle CX for Financials Services – CPQ Cloud [CON7080]
  • Oracle CX for Communications – CPQ Cloud [CON7081]

Of interest to all, but particularly relevant for CPQ Cloud customers seeking that next ROI-building transformation:

  • What’s Your CPQ Maturity? A Business Panel Discussion [CON7076]
  • Oracle Configure, Price, and Quote Roadmap [CON6320]

Register Now

We encourage you to attend OpenWorld 2016 and participate in the CPQ Cloud sessions that speak to your needs. As CPQ Cloud is the gift that keeps giving, you will be glad you did ... again and again.

Session Catalog and Registration

Pages

Subscribe to Oracle FAQ aggregator