Feed aggregator

Oracle Database Migration from HP-UX 64bit (Big-endian) to RHEL 64bit (Little-endian)

Tom Kyte - Thu, 2016-11-17 03:06
Hi Tom, We are planning to migrate Oracle Database from HP-UX 64bit (Big-endian) to RHEL 64bit (Little-endian). My question is can we migrate database from Big-endian format to Little-endian format using RMAN convert command? (like converting d...
Categories: DBA Blogs

comparing two databases, put result into new table

Tom Kyte - Thu, 2016-11-17 03:06
Hi Tom Thank you for having this service available I'm trying to create an application that will allow me to compare data from two tables by a common ID I would like to either: put the results (non matches) into a new table OR remove th...
Categories: DBA Blogs

cursor with dynamic sql

Tom Kyte - Thu, 2016-11-17 03:06
Hi , I need to write a procedure which has a cursor that stores the list of table names. We need to loop the cursor and for each table name it should look for a code in that particular table. The final output of the procedure should be the list of ta...
Categories: DBA Blogs

Track users who make updates to a EBS database table from backend

Tom Kyte - Thu, 2016-11-17 03:06
Hi, We have custom triggers on a few Oracle EBS tables to track & log any insert, update & delete. The trigger captures any changes made through the front end screens. However, if a user does an insert, update or delete to a table in the back end ...
Categories: DBA Blogs

Spool is starting new line after 500 characters

Tom Kyte - Thu, 2016-11-17 03:06
I've got Spool working in that the output from a query is returning everything, but after 500 characters, it starts a new line. So if if I had a line that was 502 characters ending with 'bananasarecool', then it would look something along the lin...
Categories: DBA Blogs

RMAN backup

Tom Kyte - Thu, 2016-11-17 03:06
Hi,guy! I use RMAN to backup the database on Nov 13 through the COMMVAULT software,but on Nov 15,the oracle server CPU is high,then I connect to the server and check,found that the wait event is 'RMAN backup&rcovery I/O',and the logon time is the ...
Categories: DBA Blogs

Update query in after insert trigger

Tom Kyte - Thu, 2016-11-17 03:06
Hello Tom- my requirement is When a record is inserted into ERR table, a post-event trigger will fire updating the D field to accept sys date (3 columns has to update in my original requirement). I've written the below code, however after the recor...
Categories: DBA Blogs

Last DML Operation timestamp on Production

Tom Kyte - Thu, 2016-11-17 03:06
Hi, I need to get timestamp of last DML operation performed on table from Production. I tried using - SELECT SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN)) from mytablename; SELECT MAX(SCN_TO_TIMESTAMP(ORA_ROWSCN)) from mytablename; Non of them worked...
Categories: DBA Blogs

Oracle Enterprise Manager 12.1 Support Dates Changed

Steven Chan - Thu, 2016-11-17 02:08

Two important changes have been made to Oracle Enterprise Manager 12.1 Lifetime Support dates:

  1. Extended Support fees have been waived for the first year up to October 31, 2017
  2. Extended Support now ends on October 31, 2020 (one extra year of Extended Support)

These changes are published here:

Impact on EBS customers

These support changes apply to E-Business Suite customers using Application Management Pack released as part of Oracle Application Management Suite for Oracle E-Business Suite. 

EBS customers should consider upgrading to Enterprise Manager 13c and AMP 13.1 or higher:

Categories: APPS Blogs

Playing with SUBSTR and INSTR

Michael Dinh - Thu, 2016-11-17 01:18
hawk:(SYS@hawk):PRIMARY> r
  1  SELECT handle,
  2  SUBSTR(handle, INSTR(handle,'/',-1)+1)                extract_last_field,
  3  SUBSTR(handle, 1, (INSTR(handle,'/',-1,1)-1))         remove_last_field,
  4  SUBSTR(handle, 1, (INSTR(handle,'/',1,2))-1)          extract_first_field,
  5  SUBSTR(handle, INSTR(handle,'/',1,2), length(handle)) remove_first_field
  6* FROM v$backup_piece_details



hawk:(SYS@hawk):PRIMARY> r
  1  SELECT name,
  2  SUBSTR(name, INSTR(name,'/',-1)+1)              extract_last_field,
  3  SUBSTR(name, 1, (INSTR(name,'/',-1,1)-1))       remove_last_field,
  4  SUBSTR(name, 1, (INSTR(name,'/',1,2))-1)        extract_first_field,
  5  SUBSTR(name, INSTR(name,'/',1,2), length(name)) remove_first_field
  6* FROM v$datafile




Improving performance of top query

Bobby Durrett's DBA Blog - Wed, 2016-11-16 16:56

I’m on call this week. Here are the steps that I took to speed up a query today.

First I got an AWR report and found the top query. Also, someone from support told me to look at November 11 before the latest release and I found a similar top query.

I got a plan for both the new and old top queries – they both use the same plan which makes me think that the two queries are similar.

I looked at the top segments on the AWR report and found a particular table at the top of the logical reads. An index of that table was like number 5 on that list.

I looked at the columns of the table’s index to see how many distinct values there were. None of the three columns had more than 300 distinct values so they were not very selective. I noticed that there was a unique index on the table and the first column of that index had millions of distinct values.

I extracted some sample bind variable values for the query and find that the second bind variable was null or something like that. But, the index we were using included this second variable.

In looked at the bind variables and found that the first column from the unique index was part of the join conditions in the query. (The query had like 20 joins).

Then I extracted the query text and replaced the bind variables with literals to see how it would run. It used the unique index. I used hints to force the original index and compared to running with the unique index. It ran about 30 times faster with the unique index. I ran a few times to make sure it was all cached.

Then I tried to use SQLT’s coe_xfr_sql_profile.sql to force the plan that used the unique index but got an error. Had to download the latest version of SQLT to get it to work.

Now, on average, the query seems to run about 1000 times faster.

It is a delivered vendor package so it was nice to find the better plan and go behind the scenes to fix it. But, if another release comes out and changes this sql to a new sql_id we will have to create a new profile. It’s not perfect but its a good quick fix for my on call.


Categories: DBA Blogs

Building Classrooms in the Cloud

Steve Karam - Wed, 2016-11-16 16:22
Jumpbox Lab Server

Let’s face it: education without interaction is about as effective as shouting origami instructions at a lumberjack who is cutting down trees. Sure, your informative lessons will come in handy when the product of their work finally becomes paper, but it will be long forgotten and ultimately worthless by then. The only way a student is going to learn is if they can put their hands on what you’re teaching, walk the journey with you, and attach a positive interactive experience to the lesson.

A little over a year ago I was trying to solve this interaction problem. As Director of the Customer Education team at Delphix, I wanted to provide an interactive experience during classroom training that:

  • gives each student their own full lab stack
  • makes labs available worldwide
  • is easy to create, manage, and tear down
  • can be built upon over time
  • is intuitive for learners to access and perform their labs
  • is cost effective

I couldn’t find a product that did all of these things in the way I needed; and so, I decided to build my own. It started as a python command line package and evolved into a web application and a huge suite of features. In this post, I’d like to provide an introduction to the LabAlchemy application, its overall features, and how it is used. I’ll be going over the tools used to build it in detail in later posts.

Introducing LabAlchemy – Classroom Management in the Amazon Web Services (AWS) Cloud

In order to fulfill bullets #1-4 above, it was clear the platform would need to be built in the cloud. Gone are the days of empty rooms full of servers that students will VNC into, or even using your own laptop as your test bed. These things worked well, but they’re not intuitive and difficult for everyone to work with. It is absolutely critical that your solution be intuitive for the learner, so they don’t fall down a rabbit hole trying to get into the labs in the first place.

And so, I decided to build the platform on top of Amazon Web Services, a rugged and incredibly diverse set of services for building the equivalent of your own datacenters in the cloud. LabAlchemy itself is composed of:

  • The LabAlchemy central server that hosts the command line interface (written in python), configuration files (in YAML), web application (written in node.js), metadata repository (in mongoDB), and classroom routing (nginx, more on this later)
  • Amazon Machine Images (AMIs) for our various classroom components like Delphix Engines, source and target servers (Linux and Windows), and the ever important “jumpbox” (more on this later too)
  • An automated set of Virtual Private Clouds (VPC), subnets, security groups, EC2 instances, EBS volumes, and copious amounts of tags to keep everything tidy and well orchestrated.

In summary, what LabAlchemy gives us is this: classroom labs on demand, at the push of a button. Each lab can have a number of students, each student gets their very own lab server and a complete Delphix stack to perform their labs. Labs are spun up in Amazon cloud, segregated into their own VPCs for easy network management, and can be stopped, started, and terminated at will.

LabAlchemy in Action

Creating labs can be done using the Command Line Interface (CLI) or web application. Let’s look at the web app. It’s more fun.

Choosing a Classroom Type

There are a few things going on behind the scenes here; most notably logging in, which is done with SSO for Delphix employees. Classroom TypesOnce a user is logged in and given permissions by my team, they are able to spin up lab environments. The first step is for them to choose a classroom type.

These classroom types are defined in a YAML file that bundles different AMIs into pre-created classrooms. All of the details about each classroom type you see on this screen are baked into the YAML file. As you can see here, we have classrooms with different versions of Delphix (laid out as tabs across the top) and different configurations like Oracle, MSSQL Server, Sybase, etc.

Class Details

Lab DetailsOnce you’ve picked a classroom type you have to enter details about the class including: classroom name, number of students, and cost center (for internal accounting). Once the details are entered and confirmed, the job can be submitted.

Class Creation

Once the job is submitted, LabAlchemy allows you to quickly view the job log. Here is a sample job log, showing this classroom being spun up for 5 students in the “alchemy” classroom. As you can see, LabAlchemy automates:Job Log

  • Configuration of a VPC for the classroom
  • Subnet setup
  • Internet Gateway setup
  • Servers for each student based on the AMI specifications

The log is updated in real time using a feature called WebSockets. We’ll cover that in a future post. Once the log is complete, LabAlchemy waits for the instances to come online, executes any final steps, and deems the classroom setup finished.

Viewing/Managing the Classroom

Current ClassroomsOnce a classroom has been created, you can manage it from the “Current Classrooms” page. I’ve set it up to only show my classrooms (3 out of 16 total right now). Each classroom is displayed with a small status box to the right showing whether it is started, stopped, creating, etc. The “alchemy” classroom details are displayed here. There are also a number of options for the administrator:

  • View – Snapshots of each student’s lab server are taken once a minute so instructors can monitor usage and progress
  • Direct Jumpbox Links – Easy access by IP address to student desktops (more in a moment)
  • Direct Delphix Links – Easy access by IP address to Delphix Engines in each student lab environment to use if the student desktop is not sufficient
  • Start/Stop/Terminate – Controls to stop, start, or terminate the servers in AWS, providing cost savings when labs are stopped, and easy self-service controls.
Accessing Labs

Now we can finally get to the fun part: accessing the labs! Jumpbox Lab ServerAgain, it’s very important to provide a clean and intuitive interface for your learners. To that end, I decided to design the idea of a student lab desktop, or jumpbox. This server is a Linux system running Ubuntu and xfce4 and a theme that more or less looks like Windows. It includes Chrome, terminal, putty, SQL Developer, Remmina RDP client, and a number of other tools to help students complete their work.

The best part though, is how they’re accessed. On the backend, LabAlchemy configures an nginx reverse proxy, which basically forwards their connection on based on a URL scheme. In this case, the scheme is http://classname.labalchemyhost.com/studentnum, where “classname” is the classroom name given on creation, “studentnum” is the student’s number (assigned after creation), and “labalchemyhost.com” is the [redacted] hostname for LabAlchemy itself. You simply go to that URL in your browser, enter the username and password provided by the instructor, and the whole lab server is available to you via HTML5 in your browser. No Flash, no plugins, just easy VNC over HTML5. For example, you can see the desktop for Student #3 in this section.

I didn’t invent this capability. I’m using an amazing open source software package called guacamole, which allows for VNC, RDP, or SSH over HTML5 with a wonderful set of additional features. In LabAlchemy, I orchestrated the configuration of guacamole for all jumpboxes, and provide access to it over the nginx reverse proxy using WebSockets. What you see is the result: a fully functional desktop in your web browser.

Neat! What Else?

As you can see from the previous section, the goal was to make it easy to access these labs. No specific software requirements (like a VNC client), no complex changing IP address to take down. Just a browser and a standardized URL. But I also took that kind of ease of use functionality a bit further with some additional features:

  • Guacamole allows connection sharing. This means that more than one person can be on a desktop at a time. In a classroom setting, that allows for students to work on labs, and other students or an instructor to help them out if needed.
  • All servers assigned to a student (Delphix Engine, Linux servers, etc.) are accessed using an easy to follow IP scheme: 10.0.studentnumber.suffix. “Studentnumber” is the number the student was given prior to class. Suffix depends on the system; Delphix Engines are at .10, Linux source servers on .20, Linux Targets on .30 and .40, and so on. This makes it so students always get a consistent experience from their labs, every time.
  • Lab Guides! This is for Delphix Education, remember? By deploying Lab Guides to these systems, students are able to work through a number of activities right from their jumpbox.
  • Carepackages, just like in Hunger Games. LabAlchemy was designed to bring up pre-created labs out of systems saved in Amazon as AMIs. In order to make modifications, add extra features, etc. I designed a system of private/public keys and deployable carepackages, which allow for custom instructions, files, etc. following classroom launch.
  • Mobile! Since the LabAlchemy app was created with a mobile friendly graphics library (Twitter Bootstrap 3), and the jumpboxes are displayed with HTML5, you can spin up/tear down classrooms and take labs with an iPad or other tablet. You can do it on a phone too, but man that’s tiny.

That’s it for today! We’ve been using this system internally for a little over a year to provide training, do internal testing, development, and all manner of demos and learning workshops. At its peak we’ve had over 550 servers up at a single time. The best part: on average, the cost of running labs is on average about $0.75 per student per hour. The beauty of the burstable cloud.

Join me next time for more of a deep dive on the technology behind LabAlchemy: python, node.js, guacamole, and more.

The post Building Classrooms in the Cloud appeared first on Oracle Alchemist.

Import APEX apps now easier with SQLcl

Kris Rice - Wed, 2016-11-16 14:28
Hopefully by now everyone knows there's the ability in SQLcl to use Javascript and the same javascript can be used to make custom commands extending what SQLcl offers out of the box. Also, I hope everyone knows we are posting examples of this to github as a base for anyone to learn and try it out.  Just in case here's the link to the GitHub location https://github.com/oracle/oracle-db-tools/tree

SQL Server 2016 SP1 and unlocked enterprise features!

Yann Neuhaus - Wed, 2016-11-16 11:49

Starting with the release of SQL Server 2016 SP1, you will probably notice that the standard edition will benefit from a lot of features previously available only on Enterprise edition. Such features concern different areas as performance, Data Warehousing and Security features. Yes Sir, this is not a joke and definitly a good reason for customers who want to move on SQL Server 2016!



Of course we may expect some limitations in terms of scalability with some features like In-Memory tables or Columnstore but that’s not so bad!

Microsoft wants a consistent Programming Surface Area and in terms of licences we may find now a similarity with Azure licensing where the edition choice is more related on requested resources than features.

Stay tuned!



Cet article SQL Server 2016 SP1 and unlocked enterprise features! est apparu en premier sur Blog dbi services.

Run SQL Server everywhere!

Yann Neuhaus - Wed, 2016-11-16 11:16

Running SQL Server everywhere is not a dream anymore!  Microsoft has just announced the first release of SQL Server CTP1 on Linux today!


But that’s not all! SQL Server Vnext is also available on Windows, Mac (via Docker), on a physical machine, virtual machine and on a docker container as well … So everywhere you want!





Cet article Run SQL Server everywhere! est apparu en premier sur Blog dbi services.

HelloSign for Oracle Documents Cloud for Employee Onboarding

WebCenter Team - Wed, 2016-11-16 10:59
Authored by: Sarah Gabot, Demand Generation Manager, HelloSign 

Employees starting a new job often need to review several documents when starting a new job: I-9, W-4s, employee handbooks, insurance forms, direct deposit forms, etc. 

All of this can be alleviated by moving your onboarding documents over to a cloud solution like Oracle Documents Cloud Service, coupled with an eSignature solution like HelloSign

Why should I move onboarding to the Cloud? 

Before you write off using eSignatures for onboarding, consider the benefits: 
  • Save money. When you make paper copies of your onboarding documents, it costs money to use these materials--not to mention you also spare administrative costs and storage costs. When you use cloud storage and eSignatures, you eliminate the costs associated with them
  • More accurate paperwork. Having to sift through paperwork with a lot of fine print isn’t an easy task. Not to mention, employees also need to make sure they initial or sign off on nearly everything. It can be easy to miss something. When using an eSignature solution, employers can make certain fields on the document required to fill out so that it can’t be submitted incomplete. The end result: Fewer mistakes!
  • Better candidate experience. New employees will overall have a better experience when you use cloud storage and eSignatures for onboarding. They’ll appreciate that you’re not handing them a stack of paperwork on their first day and that they’ll be able to sail through their onboarding documents online. 
  • Cut the paperwork clutter. This one is an obvious one. When you move to online document storage like Oracle Documents Cloud Service, you don’t have to worry about filing and managing paper copies of employee paperwork. 
  • It’s greener. When you run copies of paperwork for onboarding, you use a lot of materials to put these together: paper, paper clips, staples, folders, etc. Using cloud documents will prevent you from having to use these materials, and deleting documents is done in a few clicks. 
Suggested Onboarding Documents

Here are a few suggested documents you can move over to Oracle Documents Cloud: 
  • Employee handbook
  • W-4
  • I-9
  • Insurance paperwork
  • Direct Deposit
  • NDA
Companies like Instacart, a same-day grocery delivery service, use HelloSign’s eSignatures as part of their contractor onboarding flow. Companies who are looking to move their onboarding documents into the cloud, consider coupling Oracle Documents Cloud with HelloSign. For more information, contact your Oracle Sales rep. 

Number of rows of a value from an unbounded, ignore nulls NEXT_VALUE or FIRST_VALUE

Tom Kyte - Wed, 2016-11-16 08:46
I understand the sql in the LiveSQL link. What I am wondering is how I find the number of rows the analytic function has to look to get the last/next value if IGNORE NULLS is part of the statement. If I look at the second row (REPORT_MONTH = 01-FE...
Categories: DBA Blogs

Synchronize specific data based on date using DBMS_COMPARISON

Tom Kyte - Wed, 2016-11-16 08:46
Hi Tom, I have already synchronizing data successfully between remote table and local table using DBMS_COMPARISON with scan mode FULL. But the synchronization performance very slow as of data growth. When I read the documentation, there is n...
Categories: DBA Blogs

database block and redo blocks

Tom Kyte - Wed, 2016-11-16 08:46
1.how the same blocks are copy/exist(while update transactions) in database block buffer and redolog buffer 2.which one (DBBC and RLB) is first got activated
Categories: DBA Blogs

Mismatch in Execute and Fetch of execution plan of SQL

Tom Kyte - Wed, 2016-11-16 08:46
Hi, I have below trace information for a SQL in tkprof trace file call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0...
Categories: DBA Blogs


Subscribe to Oracle FAQ aggregator