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

Oracle 11g Fusion Middleware Installation on Linux

This note describes the (long) process of installing an 11g Fusion Middleware environment with Forms, Reports, Discoverer and Portal on an Oracle Enterprise Linux 5.6 64 Bit platform.

If you are not using Oracle Portal , then you do not need to configure Identity Management and Single Sign On which is a real big pain as there is no 11g SSO as yet and we have to install, upgrade and configure 10g SSO/DAS as well with a number of patches.

These are the top level steps:

Install Java SE Development Kit 6u25

Install Oracle Weblogic Server 11g Release 1 ( 10.3.2)

Install Oracle 11g R2 Database software (can be on same machine as Middleware or different server)

Create the 11g R2 repository database

Download the Repository creation for Windows available in 32 bit for Linux and Windows ( ofm_rcu_win32_11.1.1.3.3_disk1_1of1.zip) – change compatibility for Windows 7 to run it

Make changes to the database required for running

Continue reading Oracle 11g Fusion Middleware Installation on Linux

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

Oracle 11g Cross platform Active Standby - Windows Primary database and Linux Active Standby

This note describes the procedure of configuring a cross platform using the 11g RMAN Active Duplicate as well as an Active Standby Database setup over a Windows and Linux platform.

The Metalink note Data Guard Support for Heterogeneous Primary and Physical Standbys in Same Data Guard Configuration [ID 413484.1] describes the supported cross platform combinations between a primary and standby database.

In Oracle 10g, we could have a 32 bit and 64 bit Primary/Standby combination on some supported platforms and in 11g this has been extended to cover heterogenous platforms for the Primary and Standby database.

The environment used is as follows:

Primary

Windows 7 64 bit
11g Release 2
DB_UNIQUE_NAME=orcl

Active Standby

Oracle Enterprise Linux 5.7 64 bit
11g Release 2
DB_UNIUE_NAME=orcl_dr

Read the rest of the note Oracle 11g Cross platform Active Standby – Windows Primary database and Linux Active Standby

Recovery through RESETLOGS and reset of the incarnation of the database

Prior to Oracle 10g, the redo log files generated after opening the database with RESETLOGS could not be used with the backups taken before the RESETLOGS was performed.

Therefore, whenever a resetlogs was done, it was important to take an immediate full database backup, since all previous backups became invalid.

Just to recap, a RESETLOGS needs to be performed when we

1) Do a point in time recovery
2) Recover a database using a backup of the control file

What does a RESETLOGS do?

• Archives the current online redo logs (if they are accessible) and then erases the contents of the online redo logs and resets the log sequence number to 1.
• Creates the online redo log files if they do not currently exist.
• Updates all current datafiles and online redo logs and all subsequent archived redo logs with a new RESETLOGS SCN and time stamp

The Recovery through Resetlogs feature provides the following benefits:

* There is no need to

Continue reading Recovery through RESETLOGS and reset of the incarnation of the database

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

Using the GoldgenGate column conversion functions - STRCAT and STREXT

In one of my earlier posts, I had showed some examples of using the CASE and EVAL column conversion functions available in GoldenGate.

Let us today take a look at two other functions STRCAT and STREXT.

STRCAT basically enables us to concatenate two strings or two character columns.

The STREXT function on the other hand is used to extract a portion of a string.

For example in our source database we have a table MYPHONE with the following structure:

SQL> desc myphone
Name Null? Type
—————————————– ——– —————————-
AREA_CODE

Continue reading Using the GoldgenGate column conversion functions – STRCAT and STREXT

Using the GoldenGate column conversion functions - CASE and EVAL

Let us have a look at some GoldenGate column conversion functions – CASE and EVAL

To illustrate this example, we have a source and target table called EMPSAL and the structure of the tables in both databases is slightly different.

In the source table we have the JOB column and in the target database we have a TAX_BRACKET column.

In the first example, we will use CASE function to populate the target database TAX_BRACKET column based on the value for the JOB column in the source database.

In the second example, we will use the EVAL function to populate the target database TAX_BRACKET column based on a particular range of values for the SAL column in the source table.

Remember that since the table structure in source and target are different, we need to run the DEFGEN utility to generate a definitions file which we will then copy to the target GoldenGate

Continue reading Using the GoldenGate column conversion functions – CASE and EVAL

Passed the 1Z0-539 Exam - am now an Oracle Certified GoldenGate Implementation Specialist

I have recently passed the Oracle GoldenGate 10 Essentials exam which recognizes you as a Oracle GoldenGate 10 Certified Implementation Specialist.

Note that the exam is currently based on GoldenGate 10.4 and not the 11g version of GoldenGate which has been out now for quite some time.

I must point out that while there are a number of ‘brain dumps’ like Pass4Sure and Test King to ‘help’ you pass the exam, I have seen the questions and answers that are provided by them and in most cases I found the answers to be provided to be grossly incorrect and wrong. So beware!

The best preparation for the exam is to go through the following sets of documentation:

Oracle Installation and Setup Guide
Reference Guide
Administration Guide

These and other 10.4 GoldenGate documentation can be obtained online here …

I am now available to provide my services in any GoldenGate implementation project around the world as well

Continue reading Passed the 1Z0-539 Exam – am now an Oracle Certified GoldenGate Implementation Specialist

Does GoldenGate support Oracle Flashback?

There is an Oracle Metalink Note (Does Goldengate Support The New Oracle 10g Flashback Feature? [ID 966212.1]) which discusses this aspect, but I thought that I would like to see for myself how does the Extract and Replicat processes behave if we issue any FLASHBACK command on the source database.

The Metalink note states that GoldenGate supports the following Flashback operations:

Flashback Table to SCN
Flashback Table to timestamp
Flashback Table to restore point

and does not support operations like:

Flashback Table to before drop
Flashback database

I have tested both the Flashback Table To Timestamp as well as the Flashback Table To Before Drop operations and find that both seem to be supported by GoldenGate and replication continues inspite of these commands being issued on the source database.

Let us see the examples of the same.

I have an Oracle 11g Goldengate (11.1.1) environment configured with the source and target databases of version 11.2.0.2.

Extract and Replicat processes are running

Continue reading Does GoldenGate support Oracle Flashback?

Oracle DBA – Tips and Techniques

That’s Me

Feedback

Categories

Archives

Recent Posts

Administration