Oracle Health Check

Currently, I am working on health check for ODA and find there are too many tools with disparate information.

I am sure there are more than the ones listed below and stopped searching.

ODA Oracle Database Appliance orachk Healthcheck (Doc ID 2126926.1)
Multiplexing Redolog and Control File on ODA (Doc ID 2086289.1)

ORAchk – Health Checks for the Oracle Stack (Doc ID 1268927.2)
How to Perform a Health Check on the Database (more...)

OTN Appreciation Day : Create Database Using SQL | Thinking Out Loud Blog

Do you ever wonder how to get all parameters for CREATE DATABASE Statement ?

I will be sharing some of the reverse engineering done to create a duplicate copy of the database.

Some of you may be thinking, “Why not just duplicate database or backup and restore?”

For the project I was working on, this was not feasible since Extended Data Types (12c NF) was enabled and there is no going back.

Restoring database (more...)

cluvfy is your friend

Just a reminder to self to use cluvfy

olsnodes -i -n -s -t
grep 'master node' $CRS_HOME/log/`hostname -s`/cssd/ocssd.*|tail -1

cluvfy stage -pre help
cluvfy stage -post  help


$ olsnodes -i -n -s -t
rac01   1       rac01-vip       Active  Unpinned
rac02   2       rac02-vip       Active  Unpinned


$ env|grep HOME


$ grep 'master node' $CRS_HOME/log/`hostname -s`/cssd/ocssd.*|tail -1
/u01/app/ (more...)

Troubleshooting Goldengate Memory Usage

I was paged for GoldenGate processes memory RSS 4507248 KBytes

Here are a few ways to check memory usage.

Initially, I used ps aux –sort -rss|head -5 and Goldengate process was at the top.

After extract was restarted, I used ps ax -o rss,user,command | grep [e]xtract to determine memory usage for Goldengate/

Before: 4,763,432 
After:     31,528  


$ ps aux --sort -rss|head -5
ggsuser   (more...)

Set those Environment Variables

I fail to understand why people do not set environment variables or even update /etc/oratab with AGENT_HOME as an example.

It just makes thing so much easier.

Allow me to demonstrate. I have just awaken Frankenstein and don’t remember how he was configured.

As you can see, that last time I have used the system was back in Dec 2015 supposed.

I recall configuring XAG for the environment and not sure it was installed.

Having (more...)

HOST Options for local_listener

For a long time, I was using *.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1551))’ without specifying the host name since it makes the configuration more dynamic.

Then one day, I ran into issues which I don’t remember and started to use host name for local_listener.

Today, I ran into issues after cloning VM and host name changed causing database not to start and I am now back to using local_listener without host name.

$ sysdba

SQL*Plus: Release 12.1. (more...)

Bad Coding and 12c Upgrade Will Break

With the introduction of CDB, many views have added column CON_ID.

DB1:(SYS@DB1):PRIMARY> select inst_id inst,sid,serial#,program,machine from gv$session where sid in ((select * from dba_blockers));
select inst_id inst,sid,serial#,program,machine from gv$session where sid in ((select * from dba_blockers))
ERROR at line 1:
ORA-00913: too many values

DB1:(SYS@DB1):PRIMARY> desc dba_blockers;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 HOLDING_SESSION                                                NUMBER
 CON_ID                                                         NUMBER

DB1:(SYS@DB1):PRIMARY> select inst_id inst,sid,serial#,program,machine from gv$session where sid in ((select HOLDING_SESSION from dba_blockers));

INST        SID    SERIAL# PROGRAM                         (more...)

Simple Cold Backup using tar

At times, I find it much simpler to shutdown my test database and create cold backup which will allow me to revert from any errors.

$ echo $ORACLE_SID

$ tar -cvzf /media/sf_OracleSoftware/$ORACLE_SID_$USER_`hostname -s`_backup.tar.gz .

$ ll /media/sf_OracleSoftware/DB01_oracle_arrow_backup.tar.gz
-rwxrwx---. 1 root vboxsf 382676286 Aug 28 13:09  (more...)


A huge thank you to Pete Finnigan for his blog post.

Hacking Oracle 12c COMMON Users

Not only is the implementation of ORACLE_MAINTAINED not ideal, it’s a possible security risk.

My tip to you is to always exit the session before performing any object creation.

From my pain as you can see, looks like “_ORACLE_SCRIPT was not reset when new users where created.

Also ORACLE_MAINTAINED Objects Don’t Export

Learned quite a few new thing this weekend and (more...)

ORACLE_MAINTAINED Objects Don’t Export

What a disaster this is with 12c full database export and ORACLE_MAINTAINED objects.

Somehow, oracle thinks MDINH is an ORACLE_MAINTAINED user and user MDINH does not get exported.


Why Can an Object Not Be Exported? Expdp of SYSTEM User’s Table Returns ORA-39166 or ORA-31655 (Doc ID 2114233.1)

There are certain system generated schemas that are not exportable using exp or expdp because they contain Oracle-managed data and metadata. SYS, MDSYS, and ORDSYS are some (more...)