Using Oracle 12c with Pivotal Cloud Foundry Applications

In this post I walk through what it would take to access Oracle 12c using a spring boot application deployed to Pivotal Cloud Foundry PCF all from my Macbook Pro. Of course this can be done outside of an isolated laptop like my Macbook Pro but handy while doing DEV/TEST and still being able to use Oracle 12c.

Requirements
  • Oracle 12c instance
  • PCFDev 
  • Git Client

1. First you will need a 12c Database and the (more...)

E-book: Discover the breakthrough innovations in Oracle Database 12c

Download the eBook and read to learn about 12c (the next generation most popular database) :

  • Simplify database consolidation and manage them as cloud services
  • Accelerate analytical performance whilst achieving new levels of efficiency, security and availability
  • Lower cost and mitigate risk




RegExp: Constraint to prevent spaces at the beginning or end.

Even though a space is a regular character, the client didn't want spaces at the beginning or end of a string. Any spaces in the middle were fine.
Of course this could be handled by the application, but it must also be implemented in the database. Using a check constraint with a regular expression will prevent the end user from entering unwanted data.

To try things out, let's just start with a simple table with (more...)

Oracle EBS 12.2 & Ruby

As does seem to occur from time-to-time, I’m out there in the weeds again and sorting out a solution that fits a customer’s need. They wanted to know if they could write Oracle EBS 12.2 Concurrent Manager Programs in Ruby. They don’t want to write Java.

I checked the documentation, which as is too common, didn’t answer the question. I’m sure if I downloaded the PDF and searched it for one of the (more...)

Oracle 12c Learning Series: Automatic Table Recovery Using RMAN

Table recovery was possible in earlier release as well. Until previous release, if we wanted to recover a table, we had following options

  • Database point in time recovery (DBPITR)
  • Tablespace point in time recovery (TSPITR)
  • Flashback technology

In Oracle 12c, RMAN has been enhanced to perform recovery of table. We have a new command in RMAN which automates complete process of recovering the table. New process does not affect the existing objects in the database (more...)

Oracle 12c Learning Series: In-database Archiving and Temporal Validity

One of the major challenge faced by an Oracle DBA is – how to effectively deal with historical data? Today, if we consider database tables for an enterprise, data in the table goes back several years and most of the data in the table is inactive. Challenge remains as to how to archive this data and make our query run efficiently.

If we choose to archive old data outside of database on a tape, cost (more...)

DB_LINK w/o tnsnames.ora

A question popped up, which I thought was interesting. How can you create a DB_LINK in Oracle without the DBA changing the tnsnames.ora file? It’s actually quite easy, especially if the DBA sets the TNS address name the same as the instance’s service name or in older databases SID value.

  1. Do the following with the tnsping utility:
    tnsping mohawk

    It should return this when the server’s hostname is mohawk and domain name is techtinker. (more...)

Oracle 12c Learning Series: Automatic Data Optimization – ADO

I am starting Oracle 12c learning series, where I am planning to publish multiple articles on Oracle 12c new features. I hope these articles will be helpful to DBAs aspiring for OCP 12c certification and also others who are planning to implement these features in there databases.

This is a very long post about ADO – Automatic Data Optimization, which is one of the ILM strategy to manage aging data. So please be patient while reading this article. This article provides end-to-end details (more...)

How to view database patching information in Oracle 12.1.0 and higher

We are currently engaged with an assignment to migrate over 50 productions database to Exadata of different sizes , different OS and different business priority. Each passing day, we encounter some technical challenges and of course each situation educates and adds more knowledge.

I would like to share a small post on how to list/view the patching information  in Oracle >= 12.1.0 version:

As of 12.1.0.2, you can extract the (more...)

Can’t Display 256 Colors

If you’re reading this post, you most likely are trying to run the Oracle Database 11g or 12c runInstaller program, and it’s failing a critical dependency check and displaying an error like the one below. If so, choose n because if you choose y it won’t launch the Oracle Installer.

Starting Oracle Universal Installer...
 
Checking Temp space: must be greater than 500 MB.   Actual 30824 MB    Passed
Checking swap space: must be greater than  (more...)

Oracle 12c Pre-requisites

Installing any Oracle database is tedious, but the installing the pre-requisites can be tedious. This post tries to simplify the process by creating a single prereq.sh file for all the pre-requisite libraries. The file should contain the following:

yum install -y binutils \
               compat-libstdc++-33 \
               compat-libstdc++-33.i686 \
               gcc \
               gcc-c++ \
               glibc \
               glibc.i686 \
               glibc-devel \
               glibc-devel.i686 \
               ksh \
               libgcc \
               libgcc.i686 \
               libstdc++ \
               libstdc++. (more...)

Top N- queries: using the 12c syntax.

One of the new features with Oracle database 12c is the new syntax for Top N queries and pagination. Did we really need this? Should you choose for the new syntax over the way we used to do it, with an inline view? I think so, it simply adds syntactic clarity to the query, and in this blogpost I will show the difference between the "old" and the "new".

For the examples I will use (more...)

Linux User-Group Console

This post shows you how to add the menu option and GUI to set users and groups. It’s quite a bit easier than mastering all the command-line syntax. It makes setting up the required user and group accounts for an Oracle Enterprise or MySQL database solution much easier.

You add the utility by calling the yum (Yellowdog Updater, Modified) utility like this:

yum installed -y system-config_users

You should see the following:

Loaded plugins: langpacks
Usage:  (more...)

Rounding amounts, divide cents over multiple lines

In previous articles I wrote about dealing with a missing cent when you need to divide a certain amount over multiple lines. In these articles, links are at the bottom, I described a method to calculate the difference on the last row.
Then a question arose (as a comment):
What if for example i have 42 records and i wish to divide 100 by 42. I would get a rounded value of 2.38. If (more...)

Debug PL/SQL Web Pages

What happens when you can’t get a PL/SQL Web Toolkit to work because it only prints to a web page? That’s more tedious because any dbms_output.put_line command you embed only prints to a SQL*Plus session. The answer is quite simple, you create a test case and test it inside a SQL*Plus environment.

Here’s a sample web page that fails to run successfully …

1
2
3
4
5
6
7
8
9
10
11
 (more...)

SQL Developer & PL/SQL

While SQL Developer installs with a dbms_output view, some organizations close it before they distribute images or virtual machine (VM) instances. This post shows you how to re-enable the Dbms Output view for SQL Developer.

SQL Developer DBMS_OUTPUT Configuration

SQLDeveloper1

  1. You need to open SQL Developer, which may look like this when the DBMS_OUTPUT view isn’t visible.

SQLDeveloper1

  1. You need to click on the View menu option in SQL Developer and choose the Dbms Output dropdown menu (more...)

Using a Sparse Index

My vacation from my blog is officially over. The question that I’m answering today is: How can you pass a set of non-sequential ID values to a function and return a result set?

You need to create three object types for this example. They are:

  • a list of numbers
  • a record structure, declared as an object type without methods
  • a list of the record structure

These are the SQL commands to create the required data (more...)

Oracle 12c Caching and In Memory Databases

A few weeks ago, I was asked to give a private session about In-Memory database vs. traditional persistent databases. I created an hour-long session explaining the basics of database systems, how in-memory systems work, and when to use each of the systems.

One of the questions I got (and answered) was about persistent (regular) database cache mechanism – and I felt this is a good opportunity to write about Oracle 12c new feature – the (more...)

PeopleSoft on Oracle 12c

I was asked by Dan Iverson from psadmin.io about my experiences of PeopleSoft on Oracle 12c.  I have seen a number of PeopleSoft Financials systems on 12c recently.  Generally the experience is very positive, but one common feature is that they had all disabled Adaptive Query Optimization.

What is Adaptive Query Optimization?

Adaptive Query Optimization is a term for a set of new features in Oracle 12c to allow the optimizer to improve the (more...)

#DB12c feature – Secure External Procedures with DBMS_CREDENTIAL

Oracle Database 12c enables enhanced security for extproc by authenticating it against a user-supplied credential. This new feature allows the creation of a user credential and links it with a PL/SQL library object. Whenever an application calls an external procedure, the extproc process authenticates the connection before loading the shared library. The DBMS_CREDENTIAL package is … Continue reading