Administration « Oracle DBA – Tips and Techniques

Building a test 11gR2 RAC environment in minutes using Oracle Virtual Machine templates

This note illustrates how we can set up an Orace 11g Release 2 Real Application Clusters environment for test purposes using the Oracle VM templates which are currently available in under 30 minutes!

The templates are available from the Oracle E-Delivery web site and are available in both OEL 32bit Linux as well as 64bit Linux.

While we are demonstrating a test or development RAC setup where a single Oracle VM server is hosting both the guest nodes, a ‘Production’ type envrionment is also supported using the Oracle VM Templates where we have multiple OVM Servers and the shared disks are configured as ‘phy’ devices which are then passed on to the guest Oracle VM’s. So there are a number of Oracle VM Servers in this case.

In this test environment, however, the shared disk is configured as ‘file’ devices and both guests can run on the same Oracle VM Server.

In other words

Continue reading Building a test 11gR2 RAC environment in minutes using Oracle Virtual Machine templates

OEM Tablespace Space Used % Alerts and the AUTOEXTEND effect

I performed a simple test to try and help clarify a rather confusing topic about when OEM will send out alerts for tablespace usage and what happens when autoextend is turned on and what about the MAXSIZE and UNLIMITED parameters of the AUTOEXTEND ON clause.

A number of Metalink support notes discuss this topic like:

Database Tablespace Full Metric Alerts not Generated In Grid Control [ID 357049.1]
Troubleshooting a Database Tablespace Used(%) Alert problem [ID 403264.1]
Oracle Recommended Patches For Tablespace Monitoring using the Tablespace Space Used % metric [ID 849498.1]

What happens if autoextend is turned on but in some cases we have specified an upper limit for the MAXSIZE parameter or have just left it to UNLIMITED? How will OEM handle those cases?

To clarify things, OEM alert notification mechanism will query the DBA_TABLESPACE_USAGE_METRICS view to determine the Used % in a tablespace in relation to the Warning and Critical thresholds which have been

Continue reading OEM Tablespace Space Used % Alerts and the AUTOEXTEND effect

Using the oracle-validated RPM to install Oracle 11g on Linux

I recently came across this OTN article How I Simplified Oracle Database Installation on Oracle Linux.

I tried out the procedure listed by the author and thought I’d share this with those who may not be aware of this note and must say thanks to the author Ginny Henningsen.

When I have tried to install Oracle 11g on Linux, in most cases I have found that the OUI either reports that some packages are missing (typically unixODBC or libaio) or some kernel parameters are not appropriately set. In this case however, we download and install an RPM package called oracle-validated and the oracle-validated RPM does a number of things for us.

It creates the user oracle
It creates the groups oinstall and dba
It modifies the kernel parameters in /etc/sysctl.conf
It sets the hard and soft shell resource limits in /etc/security/limits.conf
And most importantly, downloads and installs number of packages which are required

Continue reading Using the oracle-validated RPM to install Oracle 11g on Linux

Installing Oracle 10.2.0.5 Enterprise Manager Grid Control on Linux and upgrading the repository database to 10.2.0.5

This note explains the procedure used to install 10.2.0.5 Grid Control on a Linux operating system (64 bit Red Hat Enterprise Linux Server release 5.6) as well as the steps taken to upgrade the repository database from the default 10.1.0.4 version to 10.2.0.5.

We will be using the silent installation method and not doing the installation using the OUI in interactive mode.

The steps can be broadly outlined as follows:

• Install any missing RPM’s especially the 32 bit RPM’s needed for OEM
• Create the users and groups
• Configure kernel parameters and hosts file
• Edit the response files needed for the silent installation
• Install Grid Control 10.2.0.3 (OMS and Agent) using response files
• Upgrade Grid Control to 10.2.0.5 using response files
• Configure database repository (10.1.0.4) and OMS using response files
• Upgrade repository database to 10g Release 2 (10.2.0.1) using DBUA
• Apply 10.2.0.5 patch to database software and upgrade repository database to 10.2.0.5

Download the software

Download the Grid Control software from the following

Continue reading Installing Oracle 10.2.0.5 Enterprise Manager Grid Control on Linux and upgrading the repository database to 10.2.0.5

Changing the database characterset from US7ASCII to WE8MSWIN1252

This note looks at the procedure for changing the characterset of a 10g database from US7ASCII to WE8MSWIN1252. Keep this in mind that in Oracle 11g, the DBCA does not show US7ASCII as one of the ‘recommended’ charactersets.

All characters included in the US7ASCII character set are defined in WE8MSWIN1252 with the same code point, which means that WE8MSWIN1252 is a binary or “strict” superset of US7ASCII.

Remember – we cannot go the opposite way and change the characterset from WE8MSWIN1252 to US7ASCII.

There are a number of Metalink notes which discuss in a lot of detail what I have mentioned below, but I had quite a tough time understanding clearly what had been mentioned in those notes which are quite detailed with too many sections and sub-sections, so I thought I’d try to hopefully make things a bit more clear and concise.

Note: 555823.1 – Changing US7ASCII or WE8ISO8859P1 to WE8MSWIN1252
Note: 444701.1

Continue reading Changing the database characterset from US7ASCII to WE8MSWIN1252

Using DBMS_ADVANCED_REWRITE with an HINT to change the execution plan

In one of my earlier posts, I had illustrated a use case of DBMS_ADVANCED_REWRITE and its use in cases where we cannot change the code, but can still influence and change the way the optimizer executes the same SQL statement.

In case of many vendor provided and packaged applications, we do not have access to the SQL code and we cannot rewrite the SQL statements.

So there could be cases where even though indexes are present on the table, they are not being used by the optimizer and one way we can force the optimizer to use an index is via the INDEX hint.

So how we change the execution plan of the optimizer for a particular statement without changing the original SQL statement that the application is executing?

We do it using the powerful new feature introduced in 10g called DBMS_ADVANCED_REWRITE or “tuning without touching the code”.

To illustrate this, let us create

Continue reading Using DBMS_ADVANCED_REWRITE with an HINT to change the execution plan

Upgrading Enterprise Manager Grid Control 10.2.0.5 to 11g

Purpose:

This note describes the steps taken to upgrade an existing 10.2.0.5 Enterprise Manager Grid Control environment hosted on Red Hat Linux 5.5 to Enterprise Manager 11g (11.1.

The upgrade of the 10.2.0.5 OEM broadly involves the following steps

• Install the Oracle 11g Release 2 database software. This will be used for the repository database.

• Install Oracle 11g Release 1 WebLogic Server

• Upgrade the 10.2.0.5 repository database to 11g Release 2

• Install the 11g OEM Grid Control software and upgrade Management Server (OMS) from 10g to 11g.

• Upgrade existing 10g management agents to 11g

The following tasks can be performed while the 10g Grid Control environment is up and running.

• Install the Oracle Database Software – 11g Release 2

• Install the Oracle 11g WebLogic Server (10.3.2)

• Install the Java Runtime Environment 1.6 Update 18

….
……..

Download this note ….

Upgrading to 11g Release 2 and DST Updates

When performing an upgrade to Oracle 11g Release 2, we would need to take into consideration if there are any DST (Daylight Saving Time) implications which could be the vase in case we have application tables using the datatypes TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE.

Oracle 11.2.0.2 uses Time Zone version 14 and 11.2.0.1 use Time Zone version 11. In Oracle 10.2.0.4 the Time Zone is 4 and if we are planning to upgrade from 10.2.0.1 to 11g Release 2, then we need to first patch the database to at least version 10.2.0.2 before we can attempt a direct upgrade to 11g Release 2.

One of the good new features in 11.2.0.2 Database Upgrade Assistant (DBUA) is that DBUA would automatically upgrade Time Zone to version 14 if you checked “Upgrade Timezone Version and TIMESTAMP WITH TIME ZONE data” box as shown below.

If we are using

Continue reading Upgrading to 11g Release 2 and DST Updates

Database Monitoring Menu is now available for HP UX

Sometime back I had released a useful Unix menu driven database monitoring tool and had made it available for the Linux, Solaris and AIX operating systems.

Here is a link to the original post.

I have got numerous requests to do something similar for the HP UX platform, so here is the new database monitoring menu for HP UX which can be downloaded and installed.

Please follow the instructions carefully and they are basically very simple to adopt.

1) Untar the dbamenu.tar file in a directory where you have the appropriate read/write permissions.

oracle@inpsit1:/home/oracle/ tar -xvf dbamenu.tar
x dbamenu/dba_assist.sh, 2392 bytes, 5 tape blocks
x dbamenu/dba_main.sh, 2568 bytes, 6 tape blocks
x dbamenu/dbinfo.sh, 1443 bytes, 3 tape blocks
x dbamenu/dbinfo.sql, 2989 bytes, 6 tape blocks
x dbamenu/dbinfo9.sql, 2757 bytes, 6 tape blocks
x dbamenu/dbmon_menu.sh, 49253 bytes, 97 tape blocks
x dbamenu/machine.sh, 1720 bytes, 4 tape blocks
x dbamenu/menu.log, 467 bytes, 1 tape blocks
x dbamenu/monitor_passwd, 8 bytes, 1

Continue reading Database Monitoring Menu is now available for HP UX

Upgrading 10g ASM to 11gR2 ASM

This note describes the procedure of upgrading the 10g ASM to 11g Release 2 ASM on an HP UX 64 bit platform.

The procedure of upgrading the ASM in 11g R2 is a bit different to the procedure used to upgrade ASM to 11.1.0.6 or 11.1.0.7.

In 11g R2, ASM is now included as part of the 11gR2 Grid Infrastructure software and we use ASMCA GUI tool to administer ASM and not the DBCA which we used in 10g.

Also, it should be noted that an Oracle 10g database can still connect to and use the 11g ASM disk groups.

We can define the ASM as well as database compatibility levels via attributes of the ASM disk groups as we see in the note.

Download the note ……

Oracle DBA – Tips and Techniques

That’s Me

Feedback

Categories

Archives

Recent Posts

Administration