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.