Feed aggregator

Conditionally join (left or inner) two table

Tom Kyte - Fri, 2016-08-26 17:26
Hi! First of all thanks for taking time to read and answer my question. So, I got two set of rows which needs to be joined. In some cases it should be outer left join, but in rest it should be inner. Is it possible make such query? Small ...
Categories: DBA Blogs

Commit Frequency and Rollback

Tom Kyte - Fri, 2016-08-26 17:26
Hi Tom, In our application, we have set the commit frequency as 50 and in one case one record fails to get inserted (due to a constraint violation exception). So, in that case, would all the 50 records in that commit slot get rolled back? P...
Categories: DBA Blogs

SQL Translator in 12c

Tom Kyte - Fri, 2016-08-26 17:26
Started reading about 12c SQL Translator from documentation <u>http://docs.oracle.com/database/121/DRDAA/sql_transl_arch.htm#CHDEIGFA</u> Here is a quick demo on that. <code>demo@ORA12C> create table t1 as select * from all_objects; Table cre...
Categories: DBA Blogs

pivot twice

Tom Kyte - Fri, 2016-08-26 17:26
First, the following sql return this. select trim(p.proc_name) as com_client ,op.name as trait_name , op.value trait_value from oen_procinfo p , oen_personality op where op.interfaceid = p.interfaceid and not p.proc_name like ...
Categories: DBA Blogs

Bulk Collect

Tom Kyte - Fri, 2016-08-26 17:26
There is column called jobstream_dt in a table spc_records I want to run a Delete script using Bulk collect technique to delete rows from that table where jobstream_dt is older than 540 days. We have around 125 million rows in that table which are...
Categories: DBA Blogs

E-Business Suite Technology Sessions at OpenWorld 2016

Steven Chan - Fri, 2016-08-26 14:15

OpenWorld logoPreparations for Oracle OpenWorld have been underway for some time, and the session catalog with detailed schedules is now available. 

This year, the Oracle E-Business Suite Applications Technology Group (ATG) will participate in 29 sessions including Meet the Experts round-table discussions and customer panels, demo booths, and several Special Interest Group meetings as guest speakers. Please join us to hear the latest news and connect with senior ATG development staff.

For detailed and up-to-date information, please refer to the following FOCUS ON documents:

Meeting the Experts

OpenWorld is an excellent opportunity to meet directly with Oracle E-Business Suite tools and technology experts. You can have discussions regarding the Oracle E-Business Suite strategy, your specific business and IT strategy, key planning considerations to upgrade to the latest release of Oracle E-Business Suite and more.

There are several ways of meeting with EBS Development staff:

General sessions: collar the speaker of your choice after his or her presentation.

Meet The Experts round-tables: Our most senior staff host round-table discussions where you can ask your questions. Space is limited and preregistration is recommended.

Private meetings:  If you have confidential or in-depth questions about your implementation that cannot be discussed in front of other customers, a private meeting is your best option.  Contact your Oracle account manager to set up a private meeting in a reserved room in the Moscone On-site Customer Visit Center. 

Time is of the essence

Many of this blog's experts, including me, will be attending OpenWorld this year.  If you'd like to meet with us privately, please contact your Oracle account manager to arrange that as soon as possible.  My calendar, in particular, is already starting to fill up.  It is often completely full by the time OpenWorld starts.

See you there!

Categories: APPS Blogs

Oracle Supply Chain Cloud Takes Off at OpenWorld 2016

Linda Fishman Hoyle - Fri, 2016-08-26 13:35

A Guest Post by Jon Chorley, Oracle CSO & Group Vice President, SCM Product Strategy & PLM (pictured left)

A great deal has happened since Oracle OpenWorld last year. I’m not talking about the daily shocks and counter-shocks of the U.S. election, nor Britain’s unexpected decision to exit the EU, nor the thrills and challenges of the recently concluded Rio Olympics. As strange, as surprising, and at times as amusing, as those have been, I’m talking about the change that has taken place in supply chain.

Last year at OpenWorld we announced our plans to deliver the broadest suite of built-for-the-cloud, enterprise-class supply chain applications. And that is exactly what we have done. The Oracle Supply Chain Cloud is here. Already some 1,000 customers have committed to some or all of these solutions and that number is accelerating.

What do businesses need from supply chain solutions?

When we started this journey, we recognized that businesses need three things from new supply chain solutions:

  • Solutions designed and built for the modern supply chain and the modern supply chain user that can be deployed in the cloud. Yesterday’s systems on cloud are no better than yesterday’s systems on premise. Oracle took the harder road and it has paid off.
  • Comprehensive capabilities that enable businesses to operate their end-to-end supply chain—not piece-part solutions that they have to stitch together themselves.
  • A practical path to the cloud. Businesses need a clear target and end goal, with the ability to deploy and to realize value as they go. This requires a vendor with the strategic vision and wherewithal to deliver continuous innovation that can be adopted and leveraged as needed.

How is Oracle addressing these needs?

At this OpenWorld we’ll demonstrate in detail how we have addressed these needs. Using a combination of education sessions, customer speakers, forums, access to experts, and of course the Demo Grounds, you will be able to see the potential for your company, hear how your fellow customers are approaching this transition, and get a clear sense of our product roadmap. (See SCM Central @ OpenWorld for details.)

Along with the overview sessions, there are four main tracks covering your primary business flows with ample opportunities to explore each. They are:

  • Ideate to Commercialize: Everything you need to maximize your return in innovation spend and to control the complete lifecycle of your product information
  • Source to Settle: Covering the full procurement lifecycle and supporting both direct and indirect material with full integration to supply chain processes
  • Order to Cash: Comprehensive order management, combined with the ability to support omni-channel capture and fulfilment; also full transportation and global trade management
  • Plan to Build: Next-generation planning designed to empower the “citizen planner,” combined with comprehensive discrete manufacturing capabilities supporting a wide variety of manufacturing methods

Key SCM Cloud sessions at OpenWorld

See the Session Catalog 2016 for the full schedule and many more sessions:

  • General Session: Oracle Supply Chain Cloud [GEN6347] Speakers: Richard Jewell, SVP, Applications Development; Randy Chinn, Partner, IBM; Sarosh Khan, Associate Partner, IBM
  • Journey to Supply Chain Management Cloud: Insights from the IBM Global C-suite Study [CON7949] Speakers: Sarosh Khan, Associate Partner, IBM; Rahul Patel, Associate Partner, IBM; Karen Butner, Research Director, IBM
  • Oracle Supply Chain Management Cloud for Manufacturing Companies [CON6775] Customer Speaker: Kirk Boivin, Director Of Operations, Profound Medical Inc.
  • Oracle Manufacturing Cloud and Planning Central Cloud: Early Adopter Panel [CON6774] Customer Speakers: Andre Zardini, CIO; SunCoke, Rashed Dewan; VP Finance and Interim CFO, Profound Medical
  • Order to Cash with Oracle Supply Chain Management Cloud: Customer Case Studies [CON6788] Customer Speakers: Abhijeet Bhandare, CIO, GE Power; Michael Massimino, Director of IT, Telogis Inc.
  • Strategies to Achieve Sustainable Growth: Oracle Innovation Management Cloud [CON6853] Customer Speaker: Steve Abruzzi, NBTY

There’s still plenty of time to register. Don't miss this fantastic opportunity to explore the wonderful world of Oracle Supply Chain Cloud in San Francisco, September 18-22.

Instance and storage snapshot

Pat Shuff - Fri, 2016-08-26 07:51
Yesterday we went through and created an E-Business Suite 12.5.5 instance on three servers in the Oracle Public Cloud. On previous days we talked about how to protect these instances by hiding the database and removing access from the public internet and only allowing the application server to connect to the database instance. Today we are going to assume that our work as an architect is done and we need to backup our work. We could go through the standard ufsdump and backup our system to network storage. This only solves half the problem in the cloud. We can restore our data but things are a little different in the cloud world. We need to backup our Security Lists, Security Rules, and instance configurations. We might want to replicate this environment for a secondary dev/test or QA environment so creating a golden master would be a nice thing to do.

With the Oracle Cloud we have the option of doing an instance snapshot as well as storage snapshot. This is equivalent to cloning our existing instance and having it ready to provision when we want. This is different from a backup. A backup traditionally assumes a fixed computer architecture and we can restore our operating system bits and application code onto a disk. If we suddenly change and add a virtual private network for communications with our on premise data center the backup might or might not have that configuration as part of the bits on the network disk. Many customers found that this was the case with VMWare. When you can redefine the network through software defined networks, create virtual disks and virtual network interfaces, these additions are not part of a ufsdump or OS level backup. You really need to clone the virtual disk as well as the configurations.

Oracle released snapshots of storage as well as snapshots of instances in the May/June update of cloud services. There really are no restrictions on the storage snapshots but there are a few on the instance snapshots. For the instance snapshot you need to make sure that the boot disk is non-persistent. This means that you don't pre-create the disk, attach it to the instance and boot from it. The disk needs to have the characteristic of delete upon termination. This sounds very dangerous up front. If you create customizations like adding user accounts to /etc and init files to the /etc/init directory these get deleted on termination. The key is that you create an instance, customize it, and create a snapshot of it. You then boot from a clone of this snapshot rather than a vanilla image of the operating system.

First, let's look at storage snapshots. We can find more information in the online documentation for the console or the online documentation for the REST API and command line interface. There are some features in the REST API that are worth diving a little deeper into. According to the REST API documentation you can create a snapshot in the same server to allow for faster restores by specifying /oracle/private/storage/snapshot/collocated as a property when you create the snapshot. From this you can create a storage volume from a snapshot. We can do most of these functions through the compute console. We select the storage volume and select the Create Snapshot menu item.

We can now restore this snapshot as a bootable disk and can create a new instance based on this volume. We restore by going to the storage snapshot tab, selecting the snapshot, and selecting Restore Volume from the menu. We can see the restored volume in the storage list.

We can create an instance snapshot as well. The key limitation to creating a snapshot from an instance is that the disk needs to be non-persistent. This means that we have a disk that is deleted on termination rather than created and mounted as part of the instance. This is a little confusing at first. If you follow the default instance creation it creates a storage volume for you. You need to delete this storage volume and have it replaced by a ROOT disk that is deleted upon termination. If we walk through an instance creation we have to change our behavior when we get to the storage creation. The default creates a storage instance. We want to remove it and it will be automatically replaced by a nonpersistent volume.

Once we have this hurdle removed, we can create an instance snapshot. We select the instance and click on the Create Snapshot from the menu item. If the menu item is greyed out we have a persistent storage volume as our boot image.

We can create a bootable image from this snapshot by clicking on the menu for the snapshot and Associate Image with this snapshot. This allows us to create an instance from our image.

The key to using instance snapshots is we create a bootable instance, configure it the way that we want and then create a snapshot of this instance. This gives us a golden master of not only the boot disk but of the network and customizations that we have done to the instance. You have to think a little differently when it comes to instance snapshots. It is a little strange not having a persistent root disk. It is a little strange knowing that any customizations will be lost on reboot. It is a little strange knowing that default log files will be wiped out on reboot. You need to plan a little differently and potentially reconfigure your logs, configurations, and customizations to go to another disk rather than a default root disk. If you think about it, this is not a bad thing. The root disk should be protected and not customized. Once you have the customized it should be frozen in time. One key advantage of this methodology is that you can't really insert a root kit into the kernel. These types of intrusions typically need to reboot to load the malware. Rebooting reverts you back to a safe and secure kernel and default libraries. This does mean that any packages or customizations will require a new snapshot for this customization to be persistent.

In summary, snapshots are a good way of freezing storage and an instance in time. This is good for development and test allowing you to create a golden master that you can easily clone. It also adds a new level of security by freezing your boot disk with packages that you want and locks out malware that requires reboot. It does add a new layer of thought that is needed in that any package or root file customization requires a new golden image with a new snapshot. Hopefully this helps you think of how to use snapshots and create a best practice methodology for using snapshots.

Mount NTFS disk in Linux 7

Darwin IT - Fri, 2016-08-26 06:03
Today I wanted to pass an old disk in a usb-case to my son. It was from an old Windows Laptop and even though I'm administrator, I wasn't able to read the documents in an other user's folder.

So I thought, let's do it from an Oracle Linux 7 VM, as root. But it turns out that Oracle linux did not support NTFS by default.

But with the trick in this link I managed to do it.

To sum up, especially for my self:

Add the EPEL-7 repository from Fedora:
# wget https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm

# rpm -ivh epel-release-latest-7.noarch.rpm

This worked for me. Although I had to step over my issue that I add a Fedora repo to Oracle Linux.... An other option could have been (probably if under Redhat Linux or CentOS:
# yum install epel-release
# yum clean all
# yum update

Install the NTFS-3g package:
# yum install ntfs-3g -y

And enable NTFS support for FileManagers:
# yum install ntfsprogs -y

Then I got to the FileManager and I could browse the disk. It was mounted at '/run/media/oracle/803638DB3638D3BE'.

Links for 2016-08-25 [del.icio.us]

Categories: DBA Blogs

Readers don't block Readers

Tom Kyte - Thu, 2016-08-25 23:06
HI Connor Readers don't block Readers, is this true? As I understand waiting for a resource is also a kind of blocking. I am waiting because the access is blocked. Then please help understanding the below " ---- Another example of a prob...
Categories: DBA Blogs

Oracle forms to create a database user

Tom Kyte - Thu, 2016-08-25 23:06
Hello, Is there a way to create a form with username and password and create the user in the database? I have tried using a host command to log into sqlplus and execute the create user .... but it doesn't want to execute from the form host comm...
Categories: DBA Blogs

WHILE Loop Running forever

Tom Kyte - Thu, 2016-08-25 23:06
Hi , My aim is to print week days from a given date, for ex : IF I provide a date like '12-DEC-2012' ( Which is Thursday ) then my program should print as below FRIDAY SATURDAY But below code is running in a loop for ever, I am unable ...
Categories: DBA Blogs

Lock for "grant"

Tom Kyte - Thu, 2016-08-25 23:06
Hi Tom, When we *grant* a privilege on a table acquires a lock on the table. Just curious to know the following... -- Why does Oracle acquires a lock on the table (apart from grant being a DDL) when we issue a grant. Does it change the definiti...
Categories: DBA Blogs

Synchronous refresh cannot be used for the non-partitioned tables?

Tom Kyte - Thu, 2016-08-25 23:06
Hi, as I want to use the new technology in 12c called "Synchronous refresh" of the MView or group of MViews, I read the documentation ( https://docs.oracle.com/database/121/DWHSG/sync.htm#DWHSG029 ), but for me it's not 100% clear what are the res...
Categories: DBA Blogs

The last query gives the error "Invalid Identifier error".Please explain me whether I have refer the correct table?

Tom Kyte - Thu, 2016-08-25 23:06
---------------------types------------------- create type course_ty1 as object( cid number, titlte varchar(15), credits_req integer ) / create type unit_t as object( ui number(5), credit number(5) ) / create type offering_t as obj...
Categories: DBA Blogs

Oracle ASCIISTR Function with Examples

Complete IT Professional - Thu, 2016-08-25 16:17
In this article, I’ll explain what the Oracle ASCIISTR function does, and show you some examples. Purpose of the Oracle ASCIISTR Function The ASCIISTR function converts a string in any character set to a string in the database character set. If there are any characters that are not ASCII, they are converted to a format […]
Categories: Development

Partner Webcast – Oracle Big Data Preparation Cloud Service: Transform to Self Service Data Preparation for Business Users

When you work with data, the perception is that you spend most of your time on analytics. But in reality, you will devote much more time and effort on importing, profiling, cleansing, repairing,...

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

Accuracy Matters

Tim Hall - Thu, 2016-08-25 13:15

angry-1300616_640One of the annoying things about our industry is the volume of acronyms and very specific terminology used on a day-to-day basis. If you know the lingo you are fine. If not, life can be quite problematic.

In some cases, this is just a smoke screen to hide a lack of true understanding, say enough buzzwords you read in a Gartner report and people will eventually stop asking you to define them, but in other cases it is necessary to be able to communicate *accurately* about complex issues at a reasonable pace.

Some people are completely anal about everything, demanding an incredibly high degree of precision and exact terminology. Deviate from their prescribed ideal and you are in a world of hurt. Others, like myself, allow a lot more slack, knowing that every person is on a different part of the spectrum where this is concerned.

The problem with letting people get away with loose terminology and a lack of accuracy is it can sometimes come back to bite you on the ass. It’s all too easy to let them walk away with an unrealistic view of the situation, that becomes the new office myth, and travels with them through every company they ever work in…

  • Person: So Oracle does this right?
  • Me: Not really. It’s a lot more complicated than that. It’s more accurate to say it does …
  • Person: Yes, but it amounts to the same thing. It basically ends up doing this right?
  • Me: No. It really doesn’t. I can see how it appears that way in this case, but that’s too simplistic a view.
  • Person: Yeah, yeah. I know, but for the sake of argument, we will say it does this, which results in …
  • Me: Fine. That’s wrong, but lets leave that aside and move on…

… One week later …

I’m sure you’ve all been there at some point. It can be a really fine line between getting something across properly and being a total douche. Too far one side of the line and people are walking around believing rubbish. Too far the other way and you end up excluding people and sounding like an elitist prick.

It’s a journey babe!



Accuracy Matters was first posted on August 25, 2016 at 7:15 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

E-Business Suite in the Oracle Cloud

Pat Shuff - Thu, 2016-08-25 09:00
For the last three days we talked about deploying multiple servers and securing the different layers by hiding services in the cloud. Today we are going to go through the installation processes and procedure to install E-Business Suite 12.2.5 (EBS) into a multiple compute cloud instance. We could use this instance for development and test, quality assurance, or even production work if we wanted to. Note that there are many ways to install EBS. We could install everything into Oracle Compute Cloud Services (IaaS) and install WebLogic and the Oracle Database onto Linux servers. We could install the database component into Oracle Database Cloud Services (DBaaS) and the rest on IaaS. We could also install the database into DBaaS, the application into Oracle Java Cloud Services (JaaS), and the rest in IaaS. The current recommended deployment scenario is to deploy everything into IaaS and bring your own licenses for EBS, Database, WebLogic, and Identity Servers.

We are going to go through the tutorial for installing EBS on IaaS for this blog. We are going to go down the multi-node install which first requires installing the provisioning tools to boot all of the other images into standalone instances. We will need at least four compute instances with 500 GB of disk storage to deploy our test. The individual requirements are shown in the diagram below.

Before we can start deploying we must first go to the Oracle Cloud Marketplace and download five EBS bootable images. We start by going to the marketplace and searching for "e-business" images. A list of the images that we need are shown in the diagram below.

Step 1:Download EBS 12.2.5 Fresh Install DB Tier Image. This is done by selecting the image that is returned from the search. When we get to the image page we click on "Get App". This brings up a usage terms screen that we need to click on and click OK. Once we have accepted the terms we are presented with a list of cloud instances that we can deploy into. If you don't see a list of servers you need to go into your preferences for your instance and click the checkbox that allows you to provision the marketplace apps into your instance. You will also need Compute_Admin roles to provision these boot images. You don't need to go to the compute instance after you download the image. You are mainly trying to copy the DB Tier Image into your private images.

Step 2:Download EBS 12.2.5 Demo DB Tier Image. Unfortunately there is no go back feature so you need to go to the marketplace page, search again for e-business, and select the Demo DB Tier Image.

Step 3:Download EBS 12.2.5 Application Tier Image.

Step 4:Download EBS OS-Only Image

Step 5:Download EBS Provisioning Tools Image

Step 6:Verify that all of the images are ready. You should get an email confirmation that the image is ready. You should also be able to create a new instance and see the images in the private images area. You should have five images available and we could create a bootable instance for all of them.

Step 7:Create a compute instance using the Provisioning Tool image. We are going to go with an OC3 instance and accept the default. We will create a new security list and rule that allows http access. We do have to select the boot image from the private image list.

You get to review this before it is provisioned.

This will create an Orchestration that will create the bootable disk and boot the instance. It will take a few minutes to do this and once it is done we should have all of the provisioning tools ready to execute and deploy our multi-node EBS instance.

Step 8:Connect to the server via ssh using opc. Get the ip address from the previous screen. When I first tried to connect I had to add default to the Security List otherwise the connection timed out. Once I added the ssh rule, everything worked as expected.

Step 9:change user to oracle and execute

knife oc image list

You will need the compute endpoint of the compute service because you will be prompted for it. To find this you need to go to the Compute Dashboard and look at the Compute Detail. The RESTapi Endpoint is shown but for our instance we need to change it a little bit. We have two zones associated with this domain. We want to connect to the z16 instead of the z17 zone. Once we enter the endpoint, identity domain, account id, and account password, we get a list of images that we can boot from. At the bottom of the list we see the EBS images and should be good to go. It is important to repeat that using the z17 zone will not show the images so we had to change over to the z16 zone. This is due to a Marketplace configuration that always deploys images into the lowest numbered zone for your instance.

Step 10:Edit /u01/install/APPS/apps-unlimited-ebs/ProvisionEBS.xml and replace the id-domain and user name with the output of the knife command. It is important to note that your substitute command will be a little different from the screen shot below. I also had to change the OS-Image to include the date otherwise the perl script that we are about to execute will fail as well. The file name should be /Compute-obpm44952/pat.shuff@oracle.com/Oracle-E-Business-Suite-OS-Image-12032015 but your instance and user will be different.

Step 11:Run perl /u01/install/APPS/apps-unlimited-ebs/ProvisionEBS.pl to start the install. This will ingest the xml file from the previous section and present you a menu system to install the other instances. The system will again ask for the restAPI Endpoint for the compute server, your restAPI Endpoint for storage (go to Dashboard and click on Storage to get this), your identity domain, account, and password again. For our test installation we selected option 3 for a multi-node single application server installation. The perl script then installs chef, pulls cookbooks, and installs the database, app server, and forms server instances into compute instances. This step will take a while. I recommend playing around with all of the options and configurations until you get comfortable with what you are installing. We were going for the demo installation rather than a dev/test installation. We went for a single app node and a single database node. We could have gone for multiple app nodes and gone with demo or dev deployments. Some of the screen shots from this process are below. We called our installation prsEBS so if you see references to this it relates to our installation. The process deploys orchestrations to the cloud services then starts these services in the Oracle Cloud.

We can confirm that this is doing what is expected by looking at the Orchestration page under the compute console.

When it is complete we will see four instances are running in compute.

In summary, we are able to provision multiple instances that comprise a single application, E-Business Suite. This process is well documented and well scripted. Hopefully these screen shots and steps help you follow the online tutorial mentioned earlier. What is needed next is to apply the security principles that we talked about in the past few days to secure the database and hide it from the public.


Subscribe to Oracle FAQ aggregator