GGSCI Commands : History and Command (!) recall

Ever get tired of typing commands?  Ever wish there was a way to recall commands from GGSCI?  Well, you are in luck!  There are two ways this can be done (listed below).

Commands:
1. History
2. ! (command)

The first option, History, is a command that allows you to view a list of the most recent commands executed in the GGSCI interface since the session started.  

Example:

GGSCI> history

Output:

GGSCI Command History

    (more...)

Non-CDB Architecture Is Officially Deprecated….

Yeah, it’s indeed true and official. So if you still haven’t started reading and learning Multitenant architecture introduced in 12.1.0.1 and enhanced in 12.1.0.2, it’s high time that you start doing it. Not because that it’s new but because the old architecture you are familiar with, it’s officially on the verge of extinction.

In-memory DB

A recent thread on the OTN database forum supplied some code that seemed to show that In-memory DB made no difference to performance when compared with the traditional row-store mechanism and asked why not.  (It looked as if the answer was that almost all the time for the tests was spent returning the 3M row result set to the SQL*Plus client 15 rows at a time.)

The responses on the thread led to the question: (more...)

Oracle Database 12c Patching: DBMS_QOPATCH, OPATCH_XML_INV, and datapatch

Background

Oracle Database 12c brings us many new features including: the long needed ability to run OPatch and query the patch software installed in the Oracle Home programatically through SQL using the new DBMS_QOPATCH package.

If you’re a DBA working in an environment where patching consistently among databases is important, this is a welcomed enhancement. Sometimes one-off critical bug fix patches are important in the environment. Other times regular and consistent application of the quarterly (more...)

Oracle GoldenGate Processes – Part 4 – Replicat

The replicat process is the apply process within the Oracle GoldenGate environment.  The replicat is responsible for reading the remote trail files and applying the data found in cronilogical order.  This ensures that the data is applied in the same order it was captured.  

Until recently there was only one version of a replicat, that version was the classic version.  As of 12.1.2.0, there are now three distinct versions of a (more...)

Oracle GoldenGate Processes – Part 3 – Data Pump

The Data Pump group is an secondary extract group that is used to help send data over a network.  Although a data pump is another extract group, don’t confuse it with the primary extrat group. The main purpose of the data pump extract is to write the captured data over the network to the remote trail files on the target system.  

Note: if the data pump is not confgured then the primary extract group (more...)

Oracle GoldenGate Processes – Part 2 – Extract

The extract process of Oracle GoldenGate is used to perform change data capture from the source database.  The extract can be used to read the online transaction log (in Oracle the online redo logs) or the associated archive logs.  The data that is extracted from the source database is then placed into an trail file (another topic for a post) for shipping to the apply sided. 

To configure an extract process there needs to (more...)

Flashback Data Archive Enhancements in Oracle Database 12c

Having played around with Flashback Data Archive in 11g, I figured I would get through the 12c enhancements pretty quickly. I didn’t account for the fact I’m a donkey and can’t see the wood for the trees. Luckily, I know some people who aren’t stupid and they gave me a nudge in the right direction, allowing me to spot my silly mistake. Thanks Bjoern and Connor:)

So after lots of wasted time, here is the (more...)

Performance Problems with Dynamic Statistics in Oracle 12c

I’ve been making some tests recently with the new Oracle 12.1.0.2 In-Memory option and have been faced with an unexpected  performance problem.  Here is a test case:

create table tst_1 as
with q as (select 1 from dual connect by level <= 100000)
select rownum id, 12345 val, mod(rownum,1000) ref_id  from q,q
where rownum <= 200000000;

Table created.

create table tst_2 as select rownum ref_id, lpad(rownum,10, 'a') name, rownum || 'a'  (more...)

Access Oracle GoldenGate JAgent XML from browser

There are many different ways of monitoirng Oracle GoldenGate; I have posted about many of these in earlier blog posts.  Additionally, I have talked about the different ways of monitoring Oracle GoldenGate at a few conferences as well.  (The slides can be found on my slideshare site if wanted).  In both my blog and presentations I highlight many different approaches; yet I forgot one that I think is really cool!  This one was shown to (more...)

Adaptive Query Optimization in Oracle Database 12c

Over the holiday period I finally decided to tackle the Adaptive Query Optimization stuff in Oracle 12c, which resulted in these articles.

Oracle 12c column upgrades

While playing with 12c I tried the upgrade to the DEFAULT column syntax that now allows sequences.

I came across a basic error, but it's just a small trap for new players.

CREATE TABLE seq_test(a NUMBER)
/

ALTER TABLE seq_test MODIFY (a NUMBER DEFAULT sage_seq.NEXTVAL)
/

SQL Error: ORA-02262: ORA-2289 occurs while type-checking column default value expression

*Cause: New column datatype causes type-checking error for existing column
default value expression.
*Action: Remove the default (more...)

Cloud Control Agent 12c managing many objects

An Oracle Enterprise Management Agent 12c that has to manage hundreds of objects needs extra tweaking. If not, it will not start, or it will die soon, leaving you with an unmanaged infrastructure.

Situation

The customer runs over 200 databases on 1 host. Intel bases host with 384GB RAM and over 4TB of storage. Linux OS. Works as a charm.
Enterprise Manager 12c Release 4, Agent Version 12.1.0.4.0.

The databases should (more...)

Oracle 12c WITH inline PL/SQL

I've been having a bit of a play with the Oracle 12c database over the past few days and I thought I'd mention a gotcha I encountered.

Of course, oracle-base is a great place to start for clear & concise information on new features and I was trying out some of the WITH clause enhancements (a.k.a. subquery factoring clause). As a developer I'm pretty excited about these in particular.

Creating inline functions (more...)

LGWR terminating instance due to error 338

Recently we came across a issue where our DB crashed with ORA-00338 error . Errors in file /oracle/diag/rdbms/orcl11g/orc11g/trace/orc11g_lgwr_24118.trc: ORA-00338: log 2 of thread 1 is more recent than control file ORA-00312: online log 2 thread 1: '/oracle/oradata/orcl11g/redo02.log' LGWR (ospid: 24118): terminating the instance due to error 338 DB couldn't be restarted as it gave same errors…

Oracle 12c Implicit Result Sets in SQL Developer 4.1

Ever want to run a Stored Procedure or a PL/SQL anonymous block and just want to "print out" the results of a query ?  Even as a little bit of debug information?

In Oracle 11g you have to create a SQL*Plus REFCURSOR variable and then bind it within the anonymous block  or  pass it as an argument to a procedure/function. Run the code and then print the refcursor.

This requires a bit of know how in (more...)

Is Oracle Database Time Correct? Something Is Not Quite Right.

Is Oracle Database Time Correct? Something Is Not Quite Right.


Oracle Database tuning and performance analysis is usually based on time. As I blogged HERE, the Oracle "database time" statistic is more interesting than simply "time spent in the database." It is the sum of CPU consumption and non-idle wait time. And Elapsed Time is the sum of all the database time related to perhaps a session or a SQL statement execution. However...

Installing ORDS 3.0 (Early adopter) in Weblogic.

Now the Oracle REST Data Services 3.0 Early Adopter is available, let us have a look how it’s to be installed in Weblogic 12c!

I’ve ommitted the installation or upgrade of Apex here as there is plenty of documentation on that topic, and the ORDS can be applied for other uses than being an Apex Listener since v2.0.4.

Installing ORDS

Download the Application Express and ORDS 3.0 installer from the Oracle (more...)

Oracle 12.1.0.2 Bundle Patching

I’ve spent a few days playing with patching 12.1.0.2 with the so called “Database Patch for Engineered Systems and Database In-Memory”. Lets skip over why these not necessarily related feature sets should be bundled together into effectively a Bundle Patch.

First I was testing going from 12.1.0.2.1 to BP2 or 12.1.0.2.2. Then as soon as I’d done that of course BP3 was released.

So (more...)

Adding a DEFAULT column in 12C

I was at a talk recently, and there was an update by Jason Arneil about adding columns to tables with DEFAULT values in Oracle 12C. The NOT NULL restriction has been lifted and now Oracle cleverly intercepts the null value and replaces it with the DEFAULT meta-data without storing it in the table. To repeat the 11G experiment I ran recently:

 

SQL> alter table ncha.tab1 add (filler_default char(1000) default 'EXPAND' not  (more...)