April « 2010 « Oracle DBA – Tips and Techniques

Creating an ASM duplicate database from a non-ASM database

Here are some simple steps we can take to clone or create a duplicate database from a non-ASM file system to an ASM file system.

1) create a static listener.ora entry on target machine

(SID_DESC =
(GLOBAL_DBNAME = dupdb)
(ORACLE_HOME = /u01/oracle/product/10.2.0/db_1)
(SID_NAME = dupdb)
)

2) add a tns alias on the source machine – this will be used for the auxiliary connection

DUPDBAUX =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = devu009n3)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dupdb)
)
)

3) Copy the init,ora and password file from source database to $ORACLE_HOME/dbs on target machine

4) Edit the following entries in the init.ora file

In our case we

Continue reading Creating an ASM duplicate database from a non-ASM database

11g Enterprise Manager Grid Control Installation Overview

Last week on the 23rd of April, Oracle has announced availability of 11g EM Grid Control on the Linux x86 and the Linux x86-64 platforms.

I am attaching a document which has the screen shots of the installation process. There are some differences in the install process as well as prerequisites as compared to the 10g EM Grid Control which I will try to highlight.

Before installing Enterprise Manager Grid Control, we need to install Oracle WebLogic Server 10.3.2. This is available on OTN under the Oracle Fusion Middleware 11g Software Downloads category.

The installation will create a new Web Logic domain called GCDomain and we need to provide the location of the Oracle Middleware Home which was used to install the Web Logic server.

The Oracle Middleware Home will be the location for the Web Logic Server and it’s components, the OMS and the Management Agent as well.

In earlier releases of EM

Continue reading 11g Enterprise Manager Grid Control Installation Overview

DBA Monitoring Menu

I have written a simple Unix shell script driven menu which contains SQL scripts which covers most common day to day DBA activity tasks related to monitoring and performance tuning. Once installed in a central location, we canconnect to any database in our enterprise and monitor it from one interface.

We can also connect to and monitor any databases on Windows platform. The scripts will support all databases in versions 10g as well as 11g – a few menu options may not work for Oracle 9i databases.

Have a look at the screen shots below. The download links as well as installation instructions are provide below as well.

Please let me know if this menu can be enhanced in any way and your feedback will be taken on board.

Installation Instructions:

Unzip the contents in a directory with read/write privileges. Note this needs to be installed on only one machine where SQL*Net is

Continue reading DBA Monitoring Menu

EM Grid Control and High Availability

A question often asked is can we use Oracle Data Guard as a high availibility option for the EM Grid Control Repository database. The repository database contains information on all the targets, the jobs configured, alerts and notification history etc. It has a lot of very valuable information and can be deemed as a fairly critical production database especially if there are hundreds of targets and hundreds of jobs defined in EM Grid Control. Also, how do we protect the Application Server part of the Grid Control environment? What do we do if lose the host where the management server resides?

This note will provide an overview of setting up a DR environment for the EM Grid Control Repository database. It will show how using Oracle Data Guard, a database failover is done to the standby site as well as how to set up an additional OMS (Oracle Management Service)

Continue reading EM Grid Control and High Availability

RMAN KEEP FOREVER, KEEP UNTIL TIME and FORCE commands

The KEEP FOREVER option in 11g has been improved over what was available in Oracle 10g from the point of view that in 11g, only the archivelogs which are required to keep the online backup consistent are retained.

In Oracle 10g we could roll forward this backup taken with the KEEP FOREVER option allowing point in time recovery to a time after the backup was taken. In Oracle 11g, the KEEP option will only retain enough archive logs to keep the backup consistent – so we can only use this backup to recover to the point where the backup was taken.

Archivelogs generated after this backup is completed need not be retained and can be candidates for the DELETE OBSOLETE command. In Oracle 10g, ALL archivelog backups taken after the KEEP FOREVER backup was taken had to be retained.

The KEEP FOREVER requires the use of an RMAN Catalog. The KEEP

Continue reading RMAN KEEP FOREVER, KEEP UNTIL TIME and FORCE commands

Using @http command with SQL*PLUS

By using the SQL*PLUS ‘@http’ command, we can access scripts that are located in a central repository – in this case residing on a machine where the 10g Oracle Apache Http Server is running. Rather than have local copies of SQL scripts residing on all the client machines, we can store them in one location and then run them from any client. If we need to add new scripts or edit scripts, similarly we do it in just the one location.

These scripts can be called from all kinds of clients including tools like Toad or from any server in the same network as the Apache web server.

The set up is very straight forward.

In the HTTP server Oracle Home we create a sub directory to store the scripts.

$:/u02/oradata/product/apache/Apache/Apache/> mkdir scripts

We now copy all the *.sql files containing the scripts which we would like to run in this location

We then add the

Continue reading Using @http command with SQL*PLUS

Using the AWR History Tables to compare performance - Part 2

In the Part 1 of this post, we saw how we can use the AWR history tables to compare the top wait events between two different time periods.

We will now use the history tables to track and identify problem SQL statements.

Suppose we have been informed that there was a I/O performance issue early in the morning around 4.30 AM …

1) We obtain the SNAP_ID for the period in question

SQL> select snap_id,begin_interval_time,end_interval_time
2 from dba_hist_snapshot where to_char(begin_interval_time,’DD-MON-YYYY HH24:MI’)=’28-JUL-2009 04:00′;

SNAP_ID BEGIN_INTERVAL_TIME
———- —————————————————————————
END_INTERVAL_TIME
—————————————————————————
10951 28-JUL-09 04.00.08.054 AM
28-JUL-09 05.00.58.732 AM

2) We then obtain the Top 5 SQL statements executing during that period – we can sort on a number of variables like disk reads, buffer gets, I/O waits, Cpu time etc. We will sort it by disk reads to find the top 5 SQL statements with the most disk reads.

SQL> select

Continue reading Using the AWR History Tables to compare performance – Part 2

Using the AWR History Tables to compare performance

Using the DBA_HIST AWR history tables, we can compare the top wait events between different days or different time periods.

Let us assume that we find that batch jobs which are executed at night particularly between 1 and 2 AM are experiencing performance issues.

In this case we are comparing performance of a particular database on the 20th and 21st of July for the time periods 01:00 to 02:00.

SQL>
1 select snap_id,to_char(BEGIN_INTERVAL_TIME,’DD-MON-YY HH24:MI:SS’) “Runtime”
2 from dba_hist_snapshot
3* where trunc(BEGIN_INTERVAL_TIME)=’21-JUL-2009′ order by snap_id;

SNAP_ID Runtime
———- ——————
10781 21-JUL-09 00:00:14
10782 21-JUL-09 01:01:01
10783 21-JUL-09 02:00:27
10784 21-JUL-09 03:00:42
……..

Since the snapshots are collected every hour, for the same time period on the previous day we substract 24 – so the snap_ids for the 20th of July

Continue reading Using the AWR History Tables to compare performance – Part 1

Oracle DBA – Tips and Techniques

That’s Me

Feedback

Categories

Archives

Recent Posts

Administration