Oracle Certifications - Good or Bad?

Well, here I am, re-reading old posts and finding two comments on my OCP post. It's really good to have the OCP certification, I feel powerful showing my Card to everyone where it says Oracle Databas 10g Certified Professional and my name. Still...

Taking the exam
===============
I live in Argentina and the testing center back when I took the certification exam was Prometric. So I signed up for the exam which took about two months (I paid for it myself, so I had to talk to an Oracle Sales Rep personally... and she wasn't all that helpful. I had to resort to old contacts within Oracle to get help). So, I went to the testing center and was left alone for the duration of the exam. They asked me to leave any books, computers and phones at the front desk, so I wouldn't cheat. This was funny and you will see why later.

The exam itself is annoying rather than difficult, with multiple choice questions with minute differences on commands that takes a lot of focus to notice. Specially all those pesky RMAN questions and the weird stuff from 10g that nobody used to use. Well, it turns out that Real Life experience is not enough to take the exam. I first failed the Administration II exam back in 2006 and almost cost the Gold Partner level to the company I was working for at the moment.

However I did learn a lot taking the exam so I studied as hard as I could, I even purchased the Sybex guide and got a few mock exams from around, I re-read all the course materials (10g Administration Workshop I, 10g Administration Workshop II, 10g New Features for Administrators, 10g Performance Tuning and 9i New Features for 8i Administrators). And one year later I took the same exam and passed it with a good grade (which I don't remember at the moment). So, a few months later (should have been six weeks, but DHL delivered my certificate to the wrong address) I finally got my certificate and had it framed and display it in my Wall of Certificates. I have a copy attached to my Resume and am rather happy with it.

The BIG question... did it help me advance in my career? No, at least not directly. I do know a lot more about Oracle since I had to study a lot. But I got no pay raise, no promotion, not even a 'good job' e-mail for taking the exam.

In Real Life situations, the ideal scenarios presented by Oracle in the courses rarely, if ever, apply so I had to learn how to deal with real life situations. I've often thought of writing them all down and perhaps sending them to Oracle University for them to review the courses... however I'm not that naive anymore. I think that is what this blog is about...

On the other hand, I am still working on getting a college degree (and actually more focused on work) and having an Oracle Certification kind of validates my position as Sr. Oracle DBA within the team, specially with newcomers.

The Dark Side of Oracle Certifications
======================================
It was 2006, I had failed the 10g Administration II exam, and the company needed another OCP in order to keep its Gold Level Partner with Oracle. Both the Company and Oracle were willing to go to great lengths to make this happen. This is how I got to take the exam in my office, in my own computer, with all my co-workers around.

The only other non-OCP who had the OCA and only needed to take the Administration II exam in order to get OCP certificate was my boss. I had to wait for three months (I think) to take the exam again and I was leaving the company so they wouldn't invest in my any further.

My Boss ordered a coworker (Oracle 10g Application Server Certified Associate) and me (Oracle 10g Database Administration Certified Associate) to sit with him for the exam. So, we took the 9i and 10g course books, the 10g administration manuals, a notebook each and sat in my Boss' office. The Proctor from Oracle came, put his code and left, but he was fully aware of what we were going to do.

So, we started taking the exam... and as luck would have it, we had an massive internet outage in the country, causing us to be unable to complete the exam! Two days later, with a restored internet connection, we took another try and this time the power went out. Now, to whoever doesn't believe in a Higher Power... take this as proof!

I guess since there is no harm done (he didn't get certified, at least not with our help) this isn't such a terrible thing... but I think that Oracle (specially in South America) need to re-evaluate their corporate ethics... and put measures in place to prevent such clear violations of Oracle's code of conduct and Oracle Certifications rules and regulations.

I must confess that I was OK with that when I did it... but I have since become a little more of an idealist and believe that the way to end corruption is to NOT BE CORRUPT... so... here I am.

Well... I hope to get more technical content up soon... but I am doing heavy validation of any technical post since I hate it when I get to a post that theoretically solves my problem and find out it is utterly wrong. So... perhaps I won't post a very large number of tech advice, but I intend the quality to be top of the heap.

Cheers,
and thanks for reading my rant...

In the end, Oracle feeds me and my two kittens... so I love Oracle!

Oracle Certified Professional

Well, I finally got the OCP certificate... I am officially OCP now :-)

Changing ORACLE_SID

I recently faced a rather obnoxious CT that demanded that the SID for a certain instance be in uppercase instead of lowercase (as it was created). So I started looking for possibilities (other than recreating the database, that is).

Scenario:
=========
AIX 5.3 ML07
Oracle Database 10g Release 2 Patchset 2 (10.2.0.3)

Database:
=========
There is one database with sid orcl (of course name is uppercase always, so db_name is 'ORCL')

Process Overview:
=================
At this point there are two possible ways to go. First, since the DB_NAME is not going to change, we can simply rename a few files, change the environment variable and tnsnames enty and fire up a new instance.
Another option is DBNEWID utility.

Option 1. Case Change:
======================
First we must shutdown the instance.

1. rename spfile $ORACLE_HOM/dbs/spfileorcl.ora to $ORACLE_HOME/dbs/spfileORCL.ora
2. rename passwordfile $ORACLE_HOME/dbs/orapworcl to $ORACLE_HOME/dbs/orapwORCL
3. create new directory structure $ORACLE_BASE/admin/ORCL/(b|c|u|a)dump and change the proper parameters (USER_DUMP_DEST, BACKGROUND_DUMP_DEST, AUDIT_DUMP_DEST, CORE_DUMP_DEST)

Then change ORACLE_SID environment variable to ORCL (export ORACLE_SID=ORCL) and fire up the instance. Everything should work fine. If you use the instance name as part of the naming standard for db files, you would need to change those as well. I suggest you move everything to the new name and then issue alter database rename file 'orig' to 'new'.

hth
Guillermo Alan Bort
Oracle Certified Associate.

Buenos Aires Timezone Change

This is a strictly unsupported comment

The set up:
===========
Oracle 11g (11.1.0.7)
KUbuntu Intrepid (8.10) (Linux omega 2.6.27-9-generic #1 SMP Thu Nov 20 21:57:00 UTC 2008 i686 GNU/Linux)

I followed one of the many tutorials to get 11g on ubuntu and it worked flawlessly EXCEPT for the enterprise manager.

When I finished creating the database, I found that EM would not start. After checking the logs I found problems with the timezone and finally came to realize that Oracle had anticipated the Daylight Saving Time whereas KUbuntu had not. So, for the OS it was -180min and for Oracle it was -120min. You can see the problem there, EM would not start.

Workaround:
===========
I've done this a few times, and there are many workarounds... here are the most 'pretty' ones.

1. use enviroment variable to force the timezone variation.

$ export TZ=etc/GMT+3

2. Set the OS timezone to an etc/GMT

# echo 'etc/GMT+3' > /etc/timezone

Note: you may need to reboot after this.

Oracle changes:
===============

You may need to run emctl resetTZ agent.

and run the mgmt_target.set_agent_tzrgn to update the repository:

SQL> exec mgmt_target.set_agent_tzrgn('omega:3938','-03:00');

After this small 'trick' everything worked.

Note that when DST changes again, you will need to follow these steps yet again... but still better than nothing. I'm looking into an ubuntu update to take into consideration Argentina's DST policy which is rather new.

hth
Guillermo Alan Bort
Oracle Certified Associate.

Partial incomplete recovery 9i

This works on 10g as well, but it should not be necessary if Flashback is in use.

This is the scenario
====================

Single database (doesn't matter if it's RAC or single instance). Multiple applications on the same database or at least multiple modules of the same application. Due to user error and or application logic error, some data got corrupted but nobody noticed until another application or module committed a lot of changes, so a restore of the whole database would mean loosing data and that is not the best scenario.

Database backup is as follows:

1. Daily full hot backup including archivelogs
2. Hourly archive backup.

Alternatives
============

After analyzing the situation for a while, I found two alternatives. 1) exporting the modified data that needs to be saved, restoring the database to a previous Point in Time (incomplete recovery) and then importing the appropriate data. This had the downside of keeping both application unavailable during export, restore and recover and the second application during import.

The actual solution I finally implemented was duplicating the database to a previous point in time and (before corruption), exporting the tables before corruption. Truncating corrupted production tables, importing them. As a side note, if you are performing this on 10g, you can use datapump and skip a few steps, however as this happened on a 9i database I will show the steps I took in this particular case.

Actual Solution
===============

The main steps involved in the solution are the following:

1. Preparing an auxiliary database for duplication
2. Connecting with RMAN to target database, auxiliary database and catalog (optional, recomended)
3. Duplicating the database to a previous Point in Time
4. Opening the duplicate database
5. Variable steps (export/import)

Breakdown of solution
=====================
1. This step differs a little from unix/linux to windows, I'll only mention the unix version here (it's the same on linux) and probably will make another entry for manual database/instance creation on Windows (I currently don't have a win2k3 environment to toy arround).

We can break down this step in a few actions:
a. create needed files and directories
We will basically need a parameter file (pfile or spfile) a passwordfile and the directory structure for dump destinations and datafiles.

• First, we create the passwordfile with orapwd

orapwd file=$ORACLE_HOME/dbs/orapwdup password=manager entries=5

• Then, we create the pfile and add the following

For the sake of simplicity we will create the pfile in $ORACLE_HOME/dbs/initdup.ora
From the 9.2 database administration guide, here is a sample init file, modify it as necessary.

# Cache and I/O
DB_BLOCK_SIZE=4096
DB_CACHE_SIZE=20971520

# Cursors and Library Cache
CURSOR_SHARING=SIMILAR
OPEN_CURSORS=300

# Diagnostics and Statistics
BACKGROUND_DUMP_DEST=/vobs/oracle/admin/mynewdb/bdump
CORE_DUMP_DEST=/vobs/oracle/admin/mynewdb/cdump
TIMED_STATISTICS=TRUE
USER_DUMP_DEST=/vobs/oracle/admin/mynewdb/udump

# Control File Configuration
CONTROL_FILES=("/recovery_path/control01.ctl",
"/recovery_path/control02.ctl",
"/recovery_path/control03.ctl")

# Archive
LOG_ARCHIVE_DEST_1='LOCATION=/vobs/oracle/oradata/mynewdb/archive'
LOG_ARCHIVE_FORMAT=%t_%s.dbf
LOG_ARCHIVE_START=TRUE

# Shared Server
# Uncomment and use first DISPATCHES parameter below when your listener is
# configured for SSL
# (listener.ora and sqlnet.ora)
# DISPATCHERS = "(PROTOCOL=TCPS)(SER=MODOSE)",
# "(PROTOCOL=TCPS)(PRE=oracle.aurora.server.SGiopServer)"
DISPATCHERS="(PROTOCOL=TCP)(SER=MODOSE)",
"(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)",
(PROTOCOL=TCP)

# Miscellaneous
COMPATIBLE=9.2.0
DB_NAME=dup

# Distributed, Replication and Snapshot
DB_DOMAIN=us.oracle.com
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

# Network Registration
INSTANCE_NAME=mynewdb

# Pools
JAVA_POOL_SIZE=31457280
LARGE_POOL_SIZE=1048576
SHARED_POOL_SIZE=52428800

# Processes and Sessions
PROCESSES=150

# Redo Log and Recovery
FAST_START_MTTR_TARGET=300

# Resource Manager
RESOURCE_MANAGER_PLAN=SYSTEM_PLAN

# Sort, Hash Joins, Bitmap Indexes
SORT_AREA_SIZE=524288

# Automatic Undo Management
UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=UNDOTBS01

Note that you might want to use PGA_TARGET instead of SORT_AREA_SIZE, and in 10g SGA_TARGET instead of DB_CACHE_SIZE and the various auto-tunable pools. (I think 11g supports MEMORY_TARGET or something of the sort)
b. create directory structure to support this new instance
The directories for all the ****_dump_dest must exist and so does the directory structure for the database files (archive, controlfiles, datafiles), so you must create them, as rman will not create them automatically. Use the -p option of mkdir to make your task easier.

c. start the instance (we will not create control files as that will be automatically done with rman's duplicate)
from a shell run the following

ORACLE_SID=dup; export ORACLE_SID
sqlplus "/ as sysdba"
startup nomount

you may use the pfile=FULL_PATH_TO_PFILE parameter to indicate where the pfile is, in case it's not in the default path ($ORACLE_HOME/dbs/initdip.ora)

2. We need to connect with rman to both the target database (the one we want to duplicate) and the auxiliary database (the one we just created), we do so this way

rman target system/manager@orig auxiliary / nocatalog

we can use an rman catalog, though it's not strictly necessary.

3. Once we are conected to the database, we simply need run the duplicate command in this way

run{
allocate auxiliary channel d1 type disk;
--Here we have various options, however we will use db_file_name_convert as it is a lot simpler than the other. Check the documentation for alternatives
UPLICATE TARGET DATABASE TO dup
DB_FILE_NAME_CONVERT=('/u01/oracle/dbs/orig/','/u01/oracle/oradata/dup/')
UNTIL TIME 'SYSDATE-1' # specifies incomplete recovery
SKIP TABLESPACE cmwlite, drsys, example # skip desired tablespaces
PFILE = ?/dbs/initdup.ora
lOGFILE
GROUP 1 ('/u01/oracle/oradata/redo01_1.log') SIZE 200K,
GROUP 2 ('/u01/oracle/oradata/redo02_1.log') SIZE 200K,
GROUP 3 ('/u01/oracle/oradata/redo03_1.log') SIZE 200K REUSE;
}

the auxiliary channel may be allocated to a tape device, but that depends on the setup. We are using disk here for the sake of simplicity. We may also allocate multiple channels to obtain better performance.

4. To open the new database we simply connect to it and open it using resetlogs option

ORACLE_SID=dup; export ORACLE_SID
sqlplus "/ as sysdba"
alter database open resetlogs;

5. We may now begin exporting the necessary data and importing it to production.

I hope you find this useful, please feel free to comment anything and note that as I find new things I will be modifying this post. This method has worked for me a few times already, so I'm confident it works.

HTH
Guillermo Alan Bort
Oracle Certified Associate - Database 10g.-

Oracle Certified Associate

I recently received the 10g OCA certificate, it's the first step towards achieving the OCP credential. I thought it would be wise to start documenting my experiences with Oracle Database in order to become better known in the Oracle Community. I hope you find this blog helpful as I have many others around.

hth
Guillermo Alan Bort
Oracle Certified Associate - Database 10g
Cicciux.-