Monday, October 17, 2011

Going to the Cloud

It's been a while (a long while at that) since my last post. I thought it would be fun to start writing again and so will I with a new series of my experience upgrading one of the dev OEM Grid Control 11g to the new OEM Cloud Control 12c

My first day at this task was spent downloading the media packs (I also read through the upgrade documentation and surfed some interesting news sites, had lunch, surfed 9gag for a little while and finally decided to leave the second media pack downloading overnight as it's almost time to go home and it's still at about 60%) due to my limited internet connection (over a vpn link to HQ).

This is my first OEM GC upgrade so I'm not sure what to expect and certainly can't compare it to previous upgrades (i.e. 10g to 11g). I can, however, compare this upgrade to a database upgrade and perhaps to a EBS upgrade.

At first glance I really liked the two-system approach, it makes the upgrade look easy. However I'm not sure I have two systems to do this upgrade, so I might take the one system approach. On the other hand, the system I'm upgrading is clustered, so in theory I could break the cluster, leave only one OMS up and use the second node as the "second system". I think I need to re-read the two-system approach as I mostly focused on 1-system.

The current set up:

I have two nodes with Linux Red Hat Enterprise Linux Server release 5.6 (Tikanga)
The kernel I'm using is:
[oracle@oem1 ~]$ uname -a
Linux oem1 2.6.18-238.5.1.el5 #1 SMP Mon Feb 21 05:52:39 EST 2011 x86_64 x86_64 x86_64 GNU/Linux

I have a RAC running with about 7 cluster databases (one of which acts as EM Repository). I also have the entire grid control installed on this cluster. Oracle Enterprise Manager 11g Release 1 Grid Control with PSU4 applied

The target is to have the OMS upgraded to 12c Release 1 as the database version requirement is already me. This is a clustered OMS so I will need to upgrade all the OMS according to the documentation. This looks fairly simple. I will try to keep the most accurate record of my upgrade I can.

Sunday, April 12, 2009

Oracle Certified Professional

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

Thursday, January 22, 2009


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).

AIX 5.3 ML07
Oracle Database 10g Release 2 Patchset 2 (

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'.

Guillermo Alan Bort
Oracle Certified Associate.

Thursday, January 1, 2009

Buenos Aires Timezone Change

This is a strictly unsupported comment

The set up:
Oracle 11g (
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.

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.

Guillermo Alan Bort
Oracle Certified Associate.

Wednesday, December 24, 2008

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.


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

# Cursors and Library Cache

# Diagnostics and Statistics

# Control File Configuration

# Archive

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

# Miscellaneous

# Distributed, Replication and Snapshot

# Network Registration

# Pools

# Processes and Sessions

# Redo Log and Recovery

# Resource Manager

# Sort, Hash Joins, Bitmap Indexes

# Automatic Undo Management

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
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
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
UNTIL TIME 'SYSDATE-1' # specifies incomplete recovery
SKIP TABLESPACE cmwlite, drsys, example # skip desired tablespaces
PFILE = ?/dbs/initdup.ora
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
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.

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.

Guillermo Alan Bort
Oracle Certified Associate - Database 10g