Cicciux DBA

Going to the Cloud

2011-10-17T14:01:00.000-07:00

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 11.2.0.2.0 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 11.1.0.1.0 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.

Oracle Certified Professional

2009-04-12T19:12:00.000-07:00

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

Changing ORACLE_SID

2009-01-22T19:56:00.000-08:00

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.

rename spfile $ORACLE_HOM/dbs/spfileorcl.ora to $ORACLE_HOME/dbs/spfileORCL.ora
rename passwordfile $ORACLE_HOME/dbs/orapworcl to $ORACLE_HOME/dbs/orapwORCL
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

2009-01-01T18:10:00.000-08:00

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

2008-12-24T12:27:00.000-08:00

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:

Daily full hot backup including archivelogs
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:

Preparing an auxiliary database for duplication
Connecting with RMAN to target database, auxiliary database and catalog (optional, recomended)
Duplicating the database to a previous Point in Time
Opening the duplicate database
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

2008-12-24T11:24:00.001-08:00

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