Oracle - Tricks of the Trade

DBAdminisaurus: Open Source Monitoring Application for Oracle

2008-10-15T13:18:00.000-07:00

I have been working on an open source application for monitoring Oracle; for lack of a better name, I am calling it "DBAdminisaurus."

DBAdminisaurus is basically a PHP application which allows you to see real-time as well as historical information about your Oracle databases. It leverages open source tools; specifically: Apache, Oracle 11g Instant Client, PHP, MySQL, and FusionCharts Free. Accordingly, DBAdminisaurus costs nothing!

I highly recommend FusionCharts Free for developers building these kinds of applications.

DBAdminisaurus is fully customizable. For example, all chart metadata is contained in an easy to edit XML file.

DBAdminisaurus uses a local MySQL database for login/authenication information.

Prerequisites

Apache Instance with OCI/PHP/MySQL Modules

PHP with GD,OCI, and MySQL

Oracle Client

MySQL

If you need help configuring your server to meet the prerequisities listed above, reference the following blog posts. In the case of a Linux/Unix-based distribution, many of these services may already be installed and configured.

Windows

Installing Apache/PHP/11g Instant Client/MySQL on Windows

Linux

Installing Apache/Tomcat

Installing PHP/11g Instant Client/OCI8

Installing Multiple Versions of MySQL

Download DBAdminisaurus

DBAdminisaurus Version 1.0

Initial Release, 10/15/2008

Installation

For the most part, installation should be platform independent. The following shows how to get the application up and running on a Windows platform, however, the same basic steps should apply to other platforms as well.

Unzip DBAdminisaurus to an Apache Directory

Install the dbsaurus MySQL Database

The script to do this is found in the setup directory of DBAdminisaurus.

Edit the setup/setup_dbsaurus.sql file to reflect the desired password for the dbsaurus user.

Connect to your local MySQL database and execute this script as root.

C:\Program Files\Apache\Apache229\htdocs\setup>mysql -h localhost -u root -P 330
7 -p < setup_dbsaurus.sql
Enter password: ********

This script creates the dbsaurus user, database, as well as the required tables and indexes.

Move and Update /setup/dbsaurus.ini

DBAdminisaurus has a single application configuration file: dbsaurus.ini. Before using DBAdminisaurus, you will want to move this file out of the Apache directory, to a location local to the server, which cannot be accessed by remote users.

C:\Program Files\Apache\Apache229\htdocs\setup> move dbsaurus.ini C:\dbsaurus.ini

Next, edit dbsaurus.ini, changing the following values: 'mysql.hostname,' 'mysql.password,' and 'aes.salt.' The 'mysql' values are for the local MySQL database you just created. The 'aes.salt' value is used during the encryption of your oracle database passwords. Change this value to something that you can remember.

dbsaurus.ini

[mysql]
mysql.username = dbsaurus
mysql.hostname = localhost:3307
mysql.password = dbsaurus
mysql.default_db = dbsaurus

[security]
aes.salt = babalugats

Update /dbsaurus/dbsaurusconfig.php

The /dbsaurus/dbsaurusconfig.php file needs to be updated to reflect the location of your dbsaurus.ini file. Alter the following line to reflect the path to your configuration file.

var $ini = 'C:\dbsaurus.ini';

Update httpd.conf

Next, update the Apache configuration for the directory which will serve DBAdminisaurus

DirectoryIndex index.php

#
# AllowOverride controls what directives may be placed in .htaccess files.
# It can be "All", "None", or any combination of the keywords:
# Options FileInfo AuthConfig Limit
#

AllowOverride None

#
# Controls who can get stuff from this server.
#

Order deny,allow
Deny from all
Allow from 192.168.1.104
Allow from 127.0.0.1
Allow from localhost

In my case, I simply update the 'Directory' directive to restrict access as appropriate and to automatically serve 'index.php' by default.

Test Logging In

By default, one user already exists, named 'test' with the initial password of 'test'.

If everything is working you should see the following screen.

Create Oracle Users

DBAdminisaurus needs to connect to your Oracle databases in order to run various queries. Therefore, you should create a user against each Oracle database you wish to monitor using the /setup/setup_orauser.sql script.

$ export ORACLE_SID=emrep
$ . oraenv
$ sqlplus "/ as sysdba" @setup_orauser.sql

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Oct 16 10:58:56 2008

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

User: dbsaurus
Password:

User created.

Grant succeeded.

Setup the Oracle User in DBAdminisaurus

Select 'Databases'

Click 'Add' and Enter the Database Information; Click 'Submit' (Remember that the value of 'Instance' should correspond to the net service name in your tnsnames.ora.

You should see an entry for the newly entered database

Select 'Dashboard Rex' from the Navigation Bar. Your new database is now available in the drop down.

Test the Dashboard by choosing your database from the drop-down menu; click 'Submit'

Installing Apache/PHP/11g Instant Client on Windows

2008-10-14T10:04:00.000-07:00

This post covers the steps necessary to set-up open source development tools on Windows. Specifically, PHP, Apache, and Oracle's 11g Instant Client. This will allow you to deploy applications locally to a Windows server or laptop should you not have other resources.

Here is the software I used:

Apache 2.2.9 with OpenSSL (x86) on Windows

php-5.2.6-win32-installer.msi

instantclient-basic-win32-11.1.0.6.0.zip

mysql-5.0.67-win32.zip

Install Apache

Normally I shutdown IIS before installing Apache on Windows as not to disturb the installation routine (port conflicts):

net stop "IIS Admin"

The following services are dependent on the IIS Admin service.
Stopping the IIS Admin service will also stop these services.

World Wide Web Publishing
Simple Mail Transfer Protocol (SMTP)

Do you want to continue this operation? (Y/N) [N]: Y
The World Wide Web Publishing service is stopping....
The World Wide Web Publishing service was stopped successfully.

The Simple Mail Transfer Protocol (SMTP) service is stopping.
The Simple Mail Transfer Protocol (SMTP) service was stopped successfully.

....
The IIS Admin service was stopped successfully.

Alternatively, you could shut this down via the GUI.

Run the Apache Installer

Double-click or enter on the command-line:

apache_2.2.9-win32-x86-openssl-0.9.8h-r2.msi

Enter the information relevant to your Apache instance

Accept the default directory or enter your desired path

Test connectivity to your Apache instance

Now we will change the port so that the Apache instance doesn't conflict with IIS. We will do this by editing httpd.conf

C:\Program Files\Apache\Apache229\conf> notepad httpd.conf

Change Listen 80 to reflect a different port (in my case 81) and save the file.

Bounce Apache to test

net stop "Apache2.2"

The Apache2.2 service is stopping.
The Apache2.2 service was stopped successfully.

net start "Apache2.2"

The Apache2.2 service is starting.
The Apache2.2 service was started successfully.

Afterwards, you should be able to contact Apache on the new port

Restart IIS and its dependent services

net start "IIS Admin"

The IIS Admin service is starting.
The IIS Admin service was started successfully.

net start "World Wide Web Publishing"

The World Wide Web Publishing service is starting.
The World Wide Web Publishing service was started successfully.

net start "FTP Publishing"

The FTP Publishing service is starting.
The FTP Publishing service was started successfully.

Install PHP

Run the PHP Installer

Double-click or enter on the command-line:

php-5.2.6-win32-installer.msi

Accept the default directory or browse to the desired location

Choose 'Apache 2.2.x' since that is the version of Apache we just installed

Point the installer to the conf directory of the Apache installation you performed previously

Install the PHP options that are needed for the applications you wish to create or run. In my case, I installed: Oracle 8, GD, MySQL, and OpenSSL. I also selected the 'Register php' option.

Bounce Apache. This time I used the Apache Service Monitor from the toolbar.

Test your PHP installation by placing the following file in your Apache instance's htdocs directory:

test.php

<?php phpinfo() ?>

Access the file from your web browser to verify that PHP is integrated with Apache properly. Also, review any options that you had the PHP installer configure.

Install 11g Instant Client

In order to connect to Oracle databases using the OCI component of PHP, you will need a client local to the machine that contains the necessary networking libraries. In most cases, the easiest, lightest way to do this is with the Oracle Instant Client.

Simply unzip the Instant Client to the location of your choice

Afterwards, my Instant Client is located at C:\instantclient_11_1

Create a tnsnames.ora file in the Instant Client software directory and add any services you wish to connect to.

tnsnames.ora (example)

EMREP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1.colestock.test)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = emrep.colestock.test)
(INSTANCE_NAME = emrep)
)
)

CUBS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1.colestock.test)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cubs.colestock.test)
)
)

JLC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1525))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1525))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jlc.colestock.test)
)
)

JLC2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jlc.colestock.test)
(INSTANCE_NAME = jlc2)
)
)

JLC1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jlc.colestock.test)
(INSTANCE_NAME = jlc1)
)
)

You will need to update your system's environment variables so that Apache can find the necessary libraries. Create or Update TNS_ADMIN, PATH, and ORACLE_HOME variables with the values from your Instant Client via Control Panel > System > Advanced > Environment Variables. You may also have to consider altering any NLS_LANG settings of ORACLE_HOMEs in the registry.

Reboot your system

Create and modify the following script to test Oracle connectivity

oratest.php

<?php
$db_conn = ocilogon('username', 'password', 'db');
$cmdstr = "select 'It Works' as \"Message\" from dual";
$parsed = ociparse($db_conn, $cmdstr);
ociexecute($parsed);
$nrows = ocifetchstatement($parsed, $results);
for ($i = 0; $i < $nrows; $i++ ) {
echo $results["Message"][$i] ;
}
?>

Test the script from your browser

If the test succeeded, then you have a working configuration.

Optionally, update OCI settings in php.ini. The settings I use are below:

php.ini

oci8.privileged_connect = Off
oci8.max_persistent = -1
oci8.persistent_timeout = 60
oci8.ping_interval = 60
oci8.statement_cache_size = 50
oci8.default_prefetch = 100
oci8.old_oci_close_semantics = On
oci8.events = On

Optionally, Install and Configure MySQL

Run the Installer and follow the Screens

Test Integration with Apache and PHP, by creating the following script

testmysql.php

<?php
$conn = mysql_connect("localhost:3307", "root", "password");
mysql_select_db("mysql",$conn);
$results = mysql_query("select 'It works!'", $conn);
mysql_data_seek($results,0);
$x = mysql_fetch_array($results,MYSQL_NUM);
echo var_dump($x);
?>

Test the script from your browser

If the test succeeded, then you have a working configuration.

Monitoring a Grid Control Installation

2008-09-24T16:37:00.001-07:00

This posting covers how to monitor your 10g Grid Control installation. This is referred to as OOB (Out-of-Bound) Notification. I am using a version 10.2.0.4 OMS (Oracle Management Service), Repository Database, and Agent on Enterprise Linux.

In essence, the agent local to the OMS must be used in order to monitor your Grid Control installation via the Oracle-provided method (i.e. series of perl scripts, run by the agent).

This post assumes that you already have a local mail service - in my case sendmail - established and functioning.

Check the Agent and Verify the OMS Target

$ export ORACLE_SID=agent10g
$ . oraenv
$ emctl config agent listtargets | grep oracle_emrep

If the OMS target is missing then add it to the $AGENT_HOME/sysman/emd/targets.xml file

<target type="oracle_emrep" name="Management Services and Repository" version="1.0">
<property value="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.colestock.test)(PORT=1521)))(CONNECT_DATA=(SID=emrep)))" name="ConnectDescriptor"></property>
<property value="sysman" name="UserName" encrypted="FALSE"></property>
<property value="password" name="password" encrypted="FALSE"></property></target>

Adjust the aforementioned values to suit your environment

Reload the Agent

$ emctl reload agent
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD reload completed successfully

This will encrypt the username and password properties in the targets.xml

The agent should now be monitoring the OMS as a target

$ emctl config agent listtargets | grep oracle_emrep
[Management Services and Repository, oracle_emrep]

Update the $AGENT_HOME/sysman/config/emd.properties

Configure the email properties for the agent in emd.properties

emd_email_address=oracle@rac1.colestock.test,james@colestock.com
emd_email_gateway=localhost
emd_from_email_address=

Reload the agent again

$ emctl reload agent
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD reload completed successfully

Verify $AGENT_HOME/bin/emrepdown.pl

This file may be missing from your agent installation. If so, copy the file from the OMS' $ORACLE_HOME to $AGENT_HOME/bin/emrepdown.pl

Review this file closely. For example, if you specified a value for emd_from_email_address earlier, then notifications might fail. This is due to the fact that the script uses the -r or "recipient" option of mailx; this option is not available on all Linux/UNIX-based distributions. Make the changes that are necessary for your environment.

Test Notification

Shut down the OMS and wait to see whether the notification email fires off.

$ export ORACLE_SID=oms10g
$ . oraenv
$ $ORACLE_HOME/opmn/bin/opmnctl stopall
opmnctl: stopping opmn and all managed processes...

The notification should take at least a few minutes to appear.

If you were to trace the agent's activity (DEBUG level) you would see the following in $AGENT_HOME/sysman/log/emagent_perl.trc

emrepnotif.pl: Wed Sep 24 18:28:55 2008: DEBUG: emrepnotif: Connectdescriptor (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.colestock.test)(PORT=1521)))(CONNECT_DATA=(SID=emrep)))
emrepnotif.pl: Wed Sep 24 18:28:55 2008: DEBUG: emrepnotif: emConsoleMode=STANDALONE jobLike=EMD_MAINTENANCE%
emrepnotif.pl: Wed Sep 24 18:28:55 2008: DEBUG: emrepnotif: , /tmp/_emrepnotif
emrepnotif.pl: Wed Sep 24 18:28:55 2008: DEBUG: emrepnotif: sql is: SELECT
(SELECT count(broken) FROM user_jobs
WHERE what LIKE('EMD_MAINTENANCE%')
AND broken = 'Y'),
(SELECT MIN(SYSDATE-next_date) FROM user_jobs
WHERE what LIKE('EMD_MAINTENANCE%')),
NVL((SELECT AVG(value) FROM mgmt_system_performance_log
WHERE job_name like('EMD_MAINTENANCE%')
AND name='Queued Notifications'
AND time>(SYSDATE-(1/24))),0),
(SELECT DECODE(COUNT(a.device_name), 0, -1, COUNT(a.device_name)) - COUNT(b.device_name)
FROM mgmt_notify_devices a, mgmt_notify_devices b
WHERE b.status = 0) FROM DUAL
emrepnotif.pl: Wed Sep 24 18:28:55 2008: DEBUG: emrepnotif: query result is:
em_result=0|0|-1

emrepnotif.pl: Wed Sep 24 18:28:55 2008: DEBUG: emrepnotif: Time in emrepnotif: 61.2359046936035
emrepresp.pl: Wed Sep 24 18:29:15 2008: DEBUG: Connectdescriptor (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.colestock.test)(PORT=1521)))(CONNECT_DATA=(SID=emrep)))
emrepresp.pl: Wed Sep 24 18:29:15 2008: DEBUG: , /tmp/_emrepresp
emrepresp.pl: Wed Sep 24 18:29:15 2008: DEBUG: sql is: select count(distinct host_url) from mgmt_failover_table where sysdate-last_time_stamp < 300
emrepresp.pl: Wed Sep 24 18:29:15 2008: DEBUG: sql1 is: SELECT
(SELECT count(broken) FROM user_jobs
WHERE what LIKE('EMD_COLLECTION.%')
AND broken = 'Y'),
(SELECT MIN(SYSDATE-next_date) FROM user_jobs
WHERE what LIKE('EMD_COLLECTION.%')) FROM DUAL
emrepresp.pl: Wed Sep 24 18:29:15 2008: DEBUG: No active OMSs
emrepresp.pl: Wed Sep 24 18:29:15 2008: DEBUG: exists=, accesstime=, interval=0.0416666666666667 mailscriptexists=
emrepresp.pl: Wed Sep 24 18:29:15 2008: ERROR: emrepresp: processfailure /u01/app/oracle/product/agent10g/bin/emrepdown.pl, Message:No active Management Services were found, Subject:Severe Enterprise Manager problem
emrepresp.pl: Wed Sep 24 18:29:15 2008: DEBUG: opened /tmp/sysman1234_emrepdown
emrepresp.pl: Wed Sep 24 18:29:15 2008: DEBUG: list command1=cat /u01/app/oracle/product/agent10g/sysman/config/emd.properties | grep -i EMD_EMAIL_ADDRESS= | sed s?EMD_EMAIL_ADDRESS=??i | awk '{print }'
emrepresp.pl: Wed Sep 24 18:29:15 2008: DEBUG: return command1=cat /u01/app/oracle/product/agent10g/sysman/config/emd.properties | grep -i EMD_FROM_EMAIL_ADDRESS= | sed s?EMD_FROM_EMAIL_ADDRESS=??i | awk '{print }'
emrepresp.pl: Wed Sep 24 18:29:15 2008: DEBUG: list=oracle@rac1.colestock.test,james@colestock.com
emrepresp.pl: Wed Sep 24 18:29:15 2008: DEBUG: return=
emrepresp.pl: Wed Sep 24 18:29:15 2008: DEBUG: command1=`mailx -s "Severe Enterprise Manager problem" oracle@rac1.colestock.test,james@colestock.com < /tmp/sysman1234_emrepdown`
emrepresp.pl: Wed Sep 24 18:29:16 2008: DEBUG: out=
emrepresp.pl: Wed Sep 24 18:29:16 2008: DEBUG: emrepresp: Time in emrepresp: 1205.62410354614

The referenced perl scripts are the ones involved in checking the status of the Grid Control installation and sending the email

If everything works, you should receive an email similar to the following

From oracle@colestock.com  Wed Sep 24 18:29:16 2008
Return-Path: 
Received: from rac1.colestock.test (localhost.colestock.test [127.0.0.1])
        by rac1.colestock.test (8.13.1/8.13.1) with ESMTP id m8P0TFTp006370;
        Wed, 24 Sep 2008 18:29:16 -0600
Received: (from oracle@localhost)
        by rac1.colestock.test (8.13.1/8.13.1/Submit) id m8P0TFpN006368;
        Wed, 24 Sep 2008 18:29:15 -0600
Date: Wed, 24 Sep 2008 18:29:15 -0600
From: Oracle Software Owner 
Message-Id: <200809250029.m8P0TFpN006368@rac1.colestock.test>
To: oracle@rac1.colestock.test, james@colestock.com
Subject: Severe Enterprise Manager problem


Wed Sep 24 18:29:15 MDT 2008
Severe Enterprise Manager problem
Error message: No active Management Services were found

For more help, refer to this Metalink article: 429257.1

Using the KEEP pool to improve performance

2008-09-23T14:15:00.000-07:00

In certain circumstances, pinning segments into memory - using the KEEP pool - can improve performance dramatically. I normally resort to using this feature when administering a database that can't be tuned otherwise. For example, it is often hard to change/tune the segments that are part of a COTS product, such as in a vendor-supplied ERP system.

Of course this option uses, a critical resource, memory; therefore, it's not a viable solution when large tables/indexes are involved.

The KEEP pool can be used in Data Warehouse applications as well, namely to pin aggregate materialized views, etc.

In this example, I show how to set-up and use the KEEP pool. In my example I am pinning LOB data and index segments, but I could have just as easily crafted an example that simply pinned non-LOB table/index segments.

Set-up the KEEP pool

SQL> alter system set db_keep_cache_size=64m scope=spfile;
SQL> startup;

Create a Sample Table

SQL> create table lob_pin_example ( lpe_id number(10), 
                                         lpe_desc varchar2(40),
                                         lpe_blob BLOB) 
          tablespace users 
          LOB(lpe_blob) store as lpe_blob_data
          (tablespace users disable storage in row storage 
          (buffer_pool KEEP) cache
          index lpe_blob_index);

Table created.

Load Sample Data

SQL> declare
          begin
          for i in 1..6000 loop
          insert into lob_pin_example values (i,'Test','01010101010101');
          end loop;
          commit;
          end;
          /

PL/SQL procedure successfully completed.

SQL> select count(*) from lob_pin_example;

  COUNT(*)
----------
      6000

SQL> analyze table lob_pin_example compute statistics;

Table analyzed.

When using the KEEP pool feature, I find it helpful to load the following custom view into the database as SYS. This view allows you to see into the data dictionary to determine how the KEEP pool is being utilized.

Load the objects_in_keep_pool view

CREATE OR REPLACE VIEW objects_in_keep_pool (bp_name,blocks,object_name,pool_used_size_kb,seg_size_kb)
AS
SELECT      kcbwbpd.bp_name, 
            COUNT (*) blocks, 
            ob.name,
            ROUND (bs.block_size * COUNT (*) / 1024) "POOL_USED_SIZE_KB",
            ROUND (ds.BYTES / 1024 ) "SEG_SIZE_KB"
      FROM  x$kcbwds kcbwds,
            x$kcbwbpd kcbwbpd,
            x$bh bh,
            obj$ ob,
            (SELECT block_size
              FROM v$buffer_pool
              WHERE NAME = 'KEEP') bs,
            dba_segments ds
      WHERE kcbwds.set_id >= kcbwbpd.bp_lo_sid
        AND kcbwds.set_id <= kcbwbpd.bp_hi_sid
        AND kcbwbpd.bp_size != 0
        AND kcbwds.addr = bh.set_ds
        AND bh.state != 0
        AND kcbwbpd.bp_name = 'KEEP'
        AND bh.obj = ob.obj#(+)
        AND ob.name = ds.segment_name(+)
  GROUP BY kcbwbpd.bp_name, bh.obj, ob.name, bs.block_size, ds.BYTES;

Query the Sample Table

SQL> set autotrace on explain statistics ;

SQL> select dbms_lob.substr(lpe_blob,1) from lob_pin_example;

As you can see, there are no physical reads.

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      18438  consistent gets
          0  physical reads
          0  redo size
      81128  bytes sent via SQL*Net to client
      4789  bytes received via SQL*Net from client
        401  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      6000  rows processed

Query the objects_in_keep_pool

SQL> select * from objects_in_keep_pool;

BP_NAME        BLOCKS OBJECT_NAME          POOL_USED_SIZE_KB SEG_SIZE_KB
---------- ---------- -------------------- ----------------- -----------
KEEP              48 LPE_BLOB_INDEX                    384        384
KEEP            6112 LPE_BLOB_DATA                    48896      49152

As you can see both LOB segments are in the KEEP pool.

Cloning a Standalone Database without RMAN

2008-09-21T13:38:00.000-07:00

This post covers how to clone/duplicate a database the old-fashioned way, without RMAN, using manual scripts, etc.

Backup the Source Database

In my example, I backup a 10.2.0.4 database called 'cubs' on a host called 'rac1', using a series of customizable scripts. Feel free to download and customize these 3 scripts to suit your needs.

Once the scripts have been edited, run the run_hotbkp.bsh script.

The hot backup copies the datafiles, etc. to the backup location of your choosing.

ls -lart /u01/app/oracle/orabackup/cubs
total 779072
drwxr-xr-x  4 oracle dba      4096 Sep 21 12:15 ../
-rw-r-----  1 oracle dba   5251072 Sep 21 14:27 users01.dbf
-rw-r-----  1 oracle dba  26222592 Sep 21 14:27 undotbs01.dbf
-rw-r-----  1 oracle dba 503324672 Sep 21 14:27 system01.dbf
-rw-r-----  1 oracle dba 262152192 Sep 21 14:28 sysaux01.dbf
-rw-r--r--  1 oracle dba      5602 Sep 21 14:28 CUBS.ctlbkp

Prepare the Target Host

In my example, I will be cloning the 'cubs' database to the 'rac2' host, renaming it to 'colts.'

Create any necessary directories

$ mkdir -p $ORACLE_BASE/admin/colts/adump
$ mkdir -p $ORACLE_BASE/admin/colts/bdump
$ mkdir -p $ORACLE_BASE/admin/colts/cdump
$ mkdir -p $ORACLE_BASE/admin/colts/udump
$ mkdir -p $ORACLE_BASE/oradata/colts
$ mkdir -p $ORACLE_BASE/oradata/colts/arch

Update /etc/oratab

echo "colts:/u01/app/oracle/product/10.2:N" >> /etc/oratab

Create a Passwordfile for the Target Database

$ export ORACLE_SID=colts
$ . oraenv
$ orapwd file=$ORACLE_HOME/dbs/orapwcolts password=password;

Create a PFILE for the Target Database

Create a PFILE on the Source host, transfer it to the Target, and modify it as appropriate.

$ export ORACLE_SID=cubs
$ . oraenv

$ sqlplus "/ as sysdba"
SQL> create pfile='/tmp/initcolts.ora' from spfile;

$ scp /tmp/initcolts.ora oracle@rac2:/u01/app/oracle/product/10.2/dbs/.

Edit the necessary values; for example:

*.audit_file_dest='/u01/app/oracle/admin/colts/adump'
*.background_dump_dest='/u01/app/oracle/admin/colts/bdump'
*.control_files='/u01/app/oracle/oradata/colts/control01.ctl','/u01/app/oracle/oradata/colts/control02.ctl','/u01/app/oracle/oradata/colts/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/colts/cdump'
*.db_file_name_convert='/cubs/','/colts/'
*.db_name='colts'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=coltsXDB)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/colts/arch'
*.log_archive_format='colts_%t_%s_%r.dbf'
*.user_dump_dest='/u01/app/oracle/admin/colts/udump'

Transfer the Hot Backup

Move the Hot Backup to the desired location on the Target Host.

$ scp /u01/app/oracle/orabackup/cubs/* oracle@rac2:/u01/app/oracle/oradata/colts/.
oracle@rac2's password:
CUBS.ctlbkp                                                               100% 5602     5.5KB/s   00:00
sysaux01.dbf                                                              100%  250MB  27.8MB/s   00:09
system01.dbf                                                              100%  480MB  25.3MB/s   00:19
undotbs01.dbf                                                             100%   25MB  25.0MB/s   00:01
users01.dbf                                                               100% 5128KB   5.0MB/s   00:00

Transfer Needed Archive Logs to the Target

$ scp -p /u02/oracle/arch/* oracle@rac2:/u01/app/oracle/oradata/colts/arch/.

Edit the Controlfile Trace

$ vi /u01/app/oracle/oradata/colts/CUBS.ctlbkp

Edit the text-based controlfile script until it resembles the following

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE SET DATABASE "COLTS" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/colts/redo01.log'  SIZE 50M REUSE,
  GROUP 2 '/u01/app/oracle/oradata/colts/redo02.log'  SIZE 50M REUSE,
  GROUP 3 '/u01/app/oracle/oradata/colts/redo03.log'  SIZE 50M REUSE
DATAFILE
  '/u01/app/oracle/oradata/colts/system01.dbf',
  '/u01/app/oracle/oradata/colts/undotbs01.dbf',
  '/u01/app/oracle/oradata/colts/sysaux01.dbf',
  '/u01/app/oracle/oradata/colts/users01.dbf'
CHARACTER SET AL32UTF8
;
RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;

I normally recover until cancel because user intervention is often required. This was the case for my recovery, because a datafile was added after the backup.

Run the modified script

$ sqlplus "/ as sysdba"
SQL> @CUBS.ctlbkp

ORACLE instance started.

Total System Global Area  176160768 bytes
Fixed Size                  1266416 bytes
Variable Size              62917904 bytes
Database Buffers          104857600 bytes
Redo Buffers                7118848 bytes

Control file created.

ORA-00279: change 485386 generated at 09/21/2008 15:46:08 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/oradata/colts/arch/cubs_1_42_666013624.dbf
ORA-00280: change 485386 for thread 1 is in sequence #42

Respond to the recovery:

Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 5: '/u01/app/oracle/oradata/cubs/tools01.dbf'

ORA-01112: media recovery not started

Remedy the file situtaion via the following commands

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/colts/system01.dbf
/u01/app/oracle/oradata/colts/undotbs01.dbf
/u01/app/oracle/oradata/colts/sysaux01.dbf
/u01/app/oracle/oradata/colts/users01.dbf
/u01/app/oracle/product/10.2/dbs/UNNAMED00005

SQL> alter database create datafile '/u01/app/oracle/product/10.2/dbs/UNNAMED00005' as '/u01/app/oracle/oradata/colts/tools01.dbf';

Database altered.

Resume the recovery

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 485384 generated at 09/21/2008 15:46:06 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/oradata/colts/arch/cubs_1_42_666013624.dbf
ORA-00280: change 485384 for thread 1 is in sequence #42


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 485411 generated at 09/21/2008 15:46:20 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/oradata/colts/arch/cubs_1_43_666013624.dbf
ORA-00280: change 485411 for thread 1 is in sequence #43
ORA-00278: log file
'/u01/app/oracle/oradata/colts/arch/cubs_1_42_666013624.dbf' no longer needed
for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 485415 generated at 09/21/2008 15:46:25 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/oradata/colts/arch/cubs_1_44_666013624.dbf
ORA-00280: change 485415 for thread 1 is in sequence #44
ORA-00278: log file
'/u01/app/oracle/oradata/colts/arch/cubs_1_43_666013624.dbf' no longer needed
for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.

Open the Database RESETLOGS:

SQL> alter database open resetlogs;

Complete Post-Cloning Steps

Add back tempfiles

SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/colts/temp01.dbf' size 250M;

Change the DBID (Database ID) of the Database via nid

SQL> shutdown immediate;
SQL> startup mount;

$ nid target=/

DBNEWID: Release 10.2.0.4.0 - Production on Sun Sep 21 16:55:20 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to database COLTS (DBID=2153372661)

Connected to server version 10.2.0

Control Files in database:
    /u01/app/oracle/oradata/colts/control01.ctl
    /u01/app/oracle/oradata/colts/control02.ctl
    /u01/app/oracle/oradata/colts/control03.ctl

Change database ID of database COLTS? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 2153372661 to 2820171320
    Control File /u01/app/oracle/oradata/colts/control01.ctl - modified
    Control File /u01/app/oracle/oradata/colts/control02.ctl - modified
    Control File /u01/app/oracle/oradata/colts/control03.ctl - modified
    Datafile /u01/app/oracle/oradata/colts/system01.dbf - dbid changed
    Datafile /u01/app/oracle/oradata/colts/undotbs01.dbf - dbid changed
    Datafile /u01/app/oracle/oradata/colts/sysaux01.dbf - dbid changed
    Datafile /u01/app/oracle/oradata/colts/users01.dbf - dbid changed
    Datafile /u01/app/oracle/oradata/colts/tools01.dbf - dbid changed
    Datafile /u01/app/oracle/oradata/colts/temp01.dbf - dbid changed
    Control File /u01/app/oracle/oradata/colts/control01.ctl - dbid changed
    Control File /u01/app/oracle/oradata/colts/control02.ctl - dbid changed
    Control File /u01/app/oracle/oradata/colts/control03.ctl - dbid changed
    Instance shut down

Database ID for database COLTS changed to 2820171320.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.

SQL> startup mount;
SQL> alter database open resetlogs;

Optionally, create a SPFILE

SQL> create spfile from pfile;

Change the log_archive_format parameter if necessary

SQL> alter system set log_archive_format='colts_%t_%s_%r.dbf' scope=spfile;
SQL> shutdown immediate;
SQL> startup

How to Duplicate a Standalone Database: ASM to ASM

2008-09-18T17:30:00.001-07:00

This example covers how to duplicate a 10.2.0.4 database using ASM from one host to another, which is also using ASM. This example uses RMAN without a catalog to disk, but could be quite easily retrofit to support tape channels or the use of a recovery catalog. The RMAN backup is written to a file system available to both hosts.

In my example, I am duplicating my Grid Control Database from one host to another (EMREP, duplicating to EMTST).

The term 'TARGET' is used to refer to the Primary Host/Database (The Database you are copying). The term 'AUXILIARY' is used to refer to the Duplicate Host/Database (The New Cloned Database you are creating).

Backup the TARGET Database (EMREP)

backup_emrep.rman

run {
configure device type disk parallelism 4 backup type to compressed backupset;
configure default device type to disk;
configure controlfile autobackup on;
configure controlfile autobackup format for device type disk to '/u02/oracle/ora
1/emrep/%F';
configure maxsetsize to 5G;
configure snapshot controlfile name to '/u02/oracle/ora1/emrep/snapf_emrep.ora';
backup full database format '/u02/oracle/ora1/emrep/%d_%U.rman' tag rman_nocat_e
mrep plus archivelog format '/u02/oracle/ora1/emrep/%d_%U.rman' delete input tag
 rman_nocat_emrep ;
}

run_backup.bsh

#!/bin/bash

export $ORACLE_SID=emrep

. oraenv

rman target=/ @backup_emrep.rman

Run the Backup

$ ./run_backup.bsh > rman_emrep.log

The output should look something like this:

$ more rman_emrep.log

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Sep 18 12:13:21 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: EMREP (DBID=3888623110)

RMAN> run {
2> configure device type disk parallelism 4 backup type to compressed backupset;
3> configure default device type to disk;
4> configure controlfile autobackup on;
5> configure controlfile autobackup format for device type disk to '/u02/oracle/ora1/emrep/%F';
6> configure maxsetsize to 5G;
7> configure snapshot controlfile name to '/u02/oracle/ora1/emrep/snapf_emrep.ora';
8> backup full database format '/u02/oracle/ora1/emrep/%d_%U.rman' tag rman_nocat_emrep plus archivelog format '/u02/orac
le/ora1/emrep/%d_%U.rman' delete input tag rman_nocat_emrep ;
9> }
10>
using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters are successfully stored

old RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters are successfully stored

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u02/oracle/ora1/emrep/%F';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u02/oracle/ora1/emrep/%F';
new RMAN configuration parameters are successfully stored

old RMAN configuration parameters:
CONFIGURE MAXSETSIZE TO 5 G;
new RMAN configuration parameters:
CONFIGURE MAXSETSIZE TO 5 G;
new RMAN configuration parameters are successfully stored

snapshot control file name set to: /u02/oracle/ora1/emrep/snapf_emrep.ora
new RMAN configuration parameters are successfully stored

Starting backup at 18-SEP-08
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=145 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=135 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=144 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=125 devtype=DISK
channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=68 recid=53 stamp=665756004
channel ORA_DISK_1: starting piece 1 at 18-SEP-08
channel ORA_DISK_1: finished piece 1 at 18-SEP-08
piece handle=/u02/oracle/ora1/emrep/EMREP_24jqt8b5_1_1.rman tag=RMAN_NOCAT_EMREP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=+DATA2/emrep/archivelog/2008_09_18/thread_1_seq_68.263.665756005 recid=53 stamp=665756004
Finished backup at 18-SEP-08

Starting backup at 18-SEP-08
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00005 name=+DATA2/emrep/datafile/mgmt_tablespace.256.665690321
channel ORA_DISK_1: starting piece 1 at 18-SEP-08
channel ORA_DISK_2: starting compressed full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
input datafile fno=00001 name=+DATA2/emrep/datafile/system.257.665690973
input datafile fno=00006 name=+DATA2/emrep/datafile/mgmt_ecm_depot_ts.260.665691169
channel ORA_DISK_2: starting piece 1 at 18-SEP-08
channel ORA_DISK_3: starting compressed full datafile backupset
channel ORA_DISK_3: specifying datafile(s) in backupset
input datafile fno=00002 name=+DATA2/emrep/datafile/undotbs1.258.665691079
input datafile fno=00003 name=+DATA2/emrep/datafile/sysaux.259.665691133
channel ORA_DISK_3: starting piece 1 at 18-SEP-08
channel ORA_DISK_4: starting compressed full datafile backupset
channel ORA_DISK_4: specifying datafile(s) in backupset
input datafile fno=00004 name=+DATA2/emrep/datafile/users.261.665691205
channel ORA_DISK_4: starting piece 1 at 18-SEP-08
channel ORA_DISK_4: finished piece 1 at 18-SEP-08
piece handle=/u02/oracle/ora1/emrep/EMREP_28jqt8b9_1_1.rman tag=RMAN_NOCAT_EMREP comment=NONE
channel ORA_DISK_4: backup set complete, elapsed time: 00:00:18
channel ORA_DISK_3: finished piece 1 at 18-SEP-08
piece handle=/u02/oracle/ora1/emrep/EMREP_27jqt8b9_1_1.rman tag=RMAN_NOCAT_EMREP comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:01:23
channel ORA_DISK_2: finished piece 1 at 18-SEP-08
piece handle=/u02/oracle/ora1/emrep/EMREP_26jqt8b8_1_1.rman tag=RMAN_NOCAT_EMREP comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:01:31
channel ORA_DISK_1: finished piece 1 at 18-SEP-08
piece handle=/u02/oracle/ora1/emrep/EMREP_25jqt8b8_1_1.rman tag=RMAN_NOCAT_EMREP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:56
Finished backup at 18-SEP-08

Starting backup at 18-SEP-08
current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=69 recid=54 stamp=665756192
channel ORA_DISK_1: starting piece 1 at 18-SEP-08
channel ORA_DISK_1: finished piece 1 at 18-SEP-08
piece handle=/u02/oracle/ora1/emrep/EMREP_29jqt8h1_1_1.rman tag=RMAN_NOCAT_EMREP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=+DATA2/emrep/archivelog/2008_09_18/thread_1_seq_69.263.665756189 recid=54 stamp=665756192
Finished backup at 18-SEP-08

Starting Control File and SPFILE Autobackup at 18-SEP-08
piece handle=/u02/oracle/ora1/emrep/c-3888623110-20080918-01 comment=NONE
Finished Control File and SPFILE Autobackup at 18-SEP-08

Recovery Manager complete.

The filesystem should now house a series of files, similar to:

$ ls -lart
total 160138
drwxr-xr-x  9 oracle dba     4096 Sep 18 11:36 ../
-rw-r-----  1 oracle dba   376320 Sep 18 12:13 EMREP_24jqt8b5_1_1.rman
-rw-r-----  1 oracle dba    98304 Sep 18 12:13 EMREP_28jqt8b9_1_1.rman
-rw-r-----  1 oracle dba 76972032 Sep 18 12:14 EMREP_27jqt8b9_1_1.rman
-rw-r-----  1 oracle dba 54304768 Sep 18 12:14 EMREP_26jqt8b8_1_1.rman
-rw-r-----  1 oracle dba 22044672 Sep 18 12:16 EMREP_25jqt8b8_1_1.rman
-rw-r-----  1 oracle dba   100352 Sep 18 12:16 EMREP_29jqt8h1_1_1.rman
drwxr-xr-x  2 oracle dba     4096 Sep 18 12:16 ./
-rw-r-----  1 oracle dba  5079040 Sep 18 12:16 c-3888623110-20080918-01
-rw-r-----  1 oracle dba  4997120 Sep 18 12:16 snapf_emrep.ora

Prepare the Auxiliary Host for the Duplicate Database (EMTST)

Update /etc/oratab

echo "emtst:/u01/app/oracle/product/10.2:N" >> /etc/oratab

Create a password file for the Database (EMTST)

$ cd $ORACLE_HOME/dbs
$ orapwd file=orapwemtst password=shine123

Create any Required Directories for the Database (EMTST)

$ mkdir -p /u01/app/oracle/admin/emtst/bdump
$ mkdir -p /u01/app/oracle/admin/emtst/udump
$ mkdir -p /u01/app/oracle/admin/emtst/cdump
$ mkdir -p /u01/app/oracle/admin/emtst/adump

Update the Oracle Networking Files as appropriate (EMTST)

$ cd $TNS_ADMIN
$ vi tnsnames.ora

EMREP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.colestock.test)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = emrep.colestock.test)
      (INSTANCE_NAME = emrep)
    )
  )

EMTST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.colestock.test)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = emtst.colestock.test)
      (INSTANCE_NAME = emtst)
    )
  )

Test connectivity to all services involved in the DUPLICATE operation, for example:

$ tnsping emrep

TNS Ping Utility for Linux: Version 10.2.0.4.0 - Production on 18-SEP-2008 13:27:46

Copyright (c) 1997,  2007, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.colestock.test)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = emrep.colestock.test) (INSTANCE_NAME = emrep)))
OK (0 msec)

Create/Update the Listener with a Static Entry (EMTST)

$ vi listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.colestock.test)(PORT = 1521))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = emtst.colestock.test)
      (ORACLE_HOME = /u01/app/oracle/product/10.2)
      (SID_NAME = emtst)
    )
  )

If creating a new LISTENER, start it:

$ lsnrctl start LISTENER

LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 18-SEP-2008 13:26:13

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.1.0.6.0 - Production
System parameter file is /u01/app/oracle/product/11.1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/rac2/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2.colestock.test)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.1.0.6.0 - Production
Start Date                18-SEP-2008 13:26:15
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2.colestock.test)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "emtst.colestock.test" has 1 instance(s).
  Instance "emtst", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Create Necessary ASM Directories on the Host of the New Database (EMTST)

$ export ORACLE_SID=+ASM2
$ . oraenv
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.1 is /u01/app/oracle
$ asmcmd
ASMCMD> cd DATA
ASMCMD> mkdir EMTST
ASMCMD> cd EMTST
ASMCMD> mkdir CONTROLFILE
ASMCMD> mkdir ONLINELOG

Copy the Parameter File from the TARGET to the AUXILIARY(EMREP to EMTST)

$ export ORACLE_SID=emrep
$ . oraenv
$ sqlplus "/ as sysdba"
SQL> create pfile='/tmp/initemtst.ora' from spfile;
scp /tmp/initemtst.ora oracle@rac2:/u01/app/oracle/product/10.2/dbs/.
oracle@rac2's password:
initemtst.ora                                 100% 1409     1.4KB/s   00:00
cd $ORACLE_HOME/dbs

Edit the Parameter File, Changing Necessary Values on Auxiliary Host (EMTST)

In this example, the Diskgroup is switched from DATA to DATA2. I will create the online redo logs as part of the DUPLICATE clause.

vi initemtst.ora

*.background_dump_dest='/u01/app/oracle/admin/emtst/bdump'
*.core_dump_dest='/u01/app/oracle/admin/emtst/cdump'
*.db_file_name_convert=('+DATA2','+DATA')
*.control_files='+DATA/EMTST/CONTROLFILE/control01.ctl','+DATA/EMTST/CONTROLFILE/control02.ctl','+DATA/EMTST/CONTROLFILE/control03.ctl'
*.db_name='emtst'
*.log_archive_dest_1='LOCATION=+DATA'
*.log_archive_format='emtst_%t_%s_%r.arch'
*.user_dump_dest='/u01/app/oracle/admin/emtst/udump'

Create an SPFILE and startup nomount the Auxiliary Instance

SQL> create spfile from pfile;

File created.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  536870912 bytes
Fixed Size                  1268484 bytes
Variable Size             503317756 bytes
Database Buffers           25165824 bytes
Redo Buffers                7118848 bytes

Create a Connection File that Obfuscates Passwords for RMAN

connect.rman

connect auxiliary / ;
connect target sys/password@emrep ;

Change the file's ownership:

chmod 711 connect.rman

Perform the DUPLICATE operation on the Auxiliary Host

duplicate_emrep_to_emtst.rman

@connect.rman
run {
allocate auxiliary channel aux1 device type disk;
allocate auxiliary channel aux2 device type disk;
allocate auxiliary channel aux3 device type disk;
duplicate target database to emtst logfile
group 1 ('+DATA/EMTST/ONLINELOG/redo_0101.log','+DATA/EMTST/ONLINELOG/redo_0102.log') SIZE 100M REUSE,
group 2 ('+DATA/EMTST/ONLINELOG/redo_0201.log','+DATA/EMTST/ONLINELOG/redo_0202.log') SIZE 100M REUSE,
group 3 ('+DATA/EMTST/ONLINELOG/redo_0301.log','+DATA/EMTST/ONLINELOG/redo_0302.log') SIZE 100M REUSE,
group 4 ('+DATA/EMTST/ONLINELOG/redo_0401.log','+DATA/EMTST/ONLINELOG/redo_0402.log') SIZE 100M REUSE;
}

Run the aforementioned script

rman @duplicate_emrep_to_emtst.rman > duplicate_emrep_to_emtst.log &

A successful DUPLICATE should yield output similar to this:

$ more duplicate_emrep_to_emtst.log

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Sep 18 16:18:02 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

RMAN> @connect.rman
2> connect auxiliary *;
3> connect target *;
4> **end-of-file**
5> run {
6> allocate auxiliary channel aux1 device type disk;
7> allocate auxiliary channel aux2 device type disk;
8> allocate auxiliary channel aux3 device type disk;
9> duplicate target database to emtst logfile
10> group 1 ('+DATA/EMTST/ONLINELOG/redo_0101.log','+DATA/EMTST/ONLINELOG/redo_0102.log') SIZE 100M REUSE,
11> group 2 ('+DATA/EMTST/ONLINELOG/redo_0201.log','+DATA/EMTST/ONLINELOG/redo_0202.log') SIZE 100M REUSE,
12> group 3 ('+DATA/EMTST/ONLINELOG/redo_0301.log','+DATA/EMTST/ONLINELOG/redo_0302.log') SIZE 100M REUSE,
13> group 4 ('+DATA/EMTST/ONLINELOG/redo_0401.log','+DATA/EMTST/ONLINELOG/redo_0402.log') SIZE 100M REUSE;
14> }
15>
connected to auxiliary database: EMTST (not mounted)

connected to target database: EMREP (DBID=3888623110)

using target database control file instead of recovery catalog
allocated channel: aux1
channel aux1: sid=156 devtype=DISK

allocated channel: aux2
channel aux2: sid=155 devtype=DISK

allocated channel: aux3
channel aux3: sid=154 devtype=DISK

Starting Duplicate Db at 18-SEP-08
WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to diskgroup only.

contents of Memory Script:
{
   set until scn  5512981;
   set newname for datafile  1 to
 "+data";
   set newname for datafile  2 to
 "+data";
   set newname for datafile  3 to
 "+data";
   set newname for datafile  4 to
 "+data";
   set newname for datafile  5 to
 "+data";
   set newname for datafile  6 to
 "+data";
   restore
   check readonly
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 18-SEP-08

channel aux1: starting datafile backupset restore
channel aux1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to +DATA
channel aux1: reading from backup piece /u02/oracle/ora1/emrep/EMREP_28jqt8b9_1_1.rman
channel aux2: starting datafile backupset restore
channel aux2: specifying datafile(s) to restore from backup set
restoring datafile 00002 to +DATA
restoring datafile 00003 to +DATA
channel aux2: reading from backup piece /u02/oracle/ora1/emrep/EMREP_27jqt8b9_1_1.rman
channel aux3: starting datafile backupset restore
channel aux3: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +DATA
restoring datafile 00006 to +DATA
channel aux3: reading from backup piece /u02/oracle/ora1/emrep/EMREP_26jqt8b8_1_1.rman
channel aux1: restored backup piece 1
piece handle=/u02/oracle/ora1/emrep/EMREP_28jqt8b9_1_1.rman tag=RMAN_NOCAT_EMREP
channel aux1: restore complete, elapsed time: 00:00:15
channel aux1: starting datafile backupset restore
channel aux1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to +DATA
channel aux1: reading from backup piece /u02/oracle/ora1/emrep/EMREP_25jqt8b8_1_1.rman
channel aux2: restored backup piece 1
piece handle=/u02/oracle/ora1/emrep/EMREP_27jqt8b9_1_1.rman tag=RMAN_NOCAT_EMREP
channel aux2: restore complete, elapsed time: 00:01:20
channel aux3: restored backup piece 1
piece handle=/u02/oracle/ora1/emrep/EMREP_26jqt8b8_1_1.rman tag=RMAN_NOCAT_EMREP
channel aux3: restore complete, elapsed time: 00:01:20
channel aux1: restored backup piece 1
piece handle=/u02/oracle/ora1/emrep/EMREP_25jqt8b8_1_1.rman tag=RMAN_NOCAT_EMREP
channel aux1: restore complete, elapsed time: 00:02:30
Finished restore at 18-SEP-08
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "EMTST" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      454
 LOGFILE
  GROUP  1 ( '+DATA/EMTST/ONLINELOG/redo_0101.log', '+DATA/EMTST/ONLINELOG/redo_0102.log' ) SIZE 100 M  REUSE,
  GROUP  2 ( '+DATA/EMTST/ONLINELOG/redo_0201.log', '+DATA/EMTST/ONLINELOG/redo_0202.log' ) SIZE 100 M  REUSE,
  GROUP  3 ( '+DATA/EMTST/ONLINELOG/redo_0301.log', '+DATA/EMTST/ONLINELOG/redo_0302.log' ) SIZE 100 M  REUSE,
  GROUP  4 ( '+DATA/EMTST/ONLINELOG/redo_0401.log', '+DATA/EMTST/ONLINELOG/redo_0402.log' ) SIZE 100 M  REUSE
 DATAFILE
  '+DATA/emtst/datafile/system.282.665770693'
 CHARACTER SET AL32UTF8


contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=665770855 filename=+DATA/emtst/datafile/undotbs1.283.665770693
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=665770855 filename=+DATA/emtst/datafile/sysaux.292.665770695
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=665770855 filename=+DATA/emtst/datafile/users.284.665770693
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=665770855 filename=+DATA/emtst/datafile/mgmt_tablespace.291.665770703
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=665770855 filename=+DATA/emtst/datafile/mgmt_ecm_depot_ts.293.665770695

contents of Memory Script:
{
   set until scn  5512981;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 18-SEP-08

starting media recovery

archive log thread 1 sequence 70 is already on disk as file +DATA2/emrep/archivelog/2008_09_18/thread_1_seq_70.
280.665766251
channel aux1: starting archive log restore to default destination
channel aux1: restoring archive log
archive log thread=1 sequence=69
channel aux1: reading from backup piece /u02/oracle/ora1/emrep/EMREP_29jqt8h1_1_1.rman
channel aux1: restored backup piece 1
piece handle=/u02/oracle/ora1/emrep/EMREP_29jqt8h1_1_1.rman tag=RMAN_NOCAT_EMREP
channel aux1: restore complete, elapsed time: 00:00:02
archive log filename=+DATA/emtst/archivelog/2008_09_18/thread_1_seq_69.287.665770857 thread=1 sequence=69
channel clone_default: deleting archive log(s)
archive log filename=+DATA/emtst/archivelog/2008_09_18/thread_1_seq_69.287.665770857 recid=1 stamp=665770856
archive log filename=+DATA2/emrep/archivelog/2008_09_18/thread_1_seq_70.280.665766251 thread=1 sequence=70
media recovery complete, elapsed time: 00:00:32
Finished recover at 18-SEP-08

contents of Memory Script:
{
   shutdown clone;
   startup clone nomount ;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     536870912 bytes

Fixed Size                     1268484 bytes
Variable Size                503317756 bytes
Database Buffers              25165824 bytes
Redo Buffers                   7118848 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "EMTST" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      454
 LOGFILE
  GROUP  1 ( '+DATA/EMTST/ONLINELOG/redo_0101.log', '+DATA/EMTST/ONLINELOG/redo_0102.log' ) SIZE 100 M  REUSE,
  GROUP  2 ( '+DATA/EMTST/ONLINELOG/redo_0201.log', '+DATA/EMTST/ONLINELOG/redo_0202.log' ) SIZE 100 M  REUSE,
  GROUP  3 ( '+DATA/EMTST/ONLINELOG/redo_0301.log', '+DATA/EMTST/ONLINELOG/redo_0302.log' ) SIZE 100 M  REUSE,
  GROUP  4 ( '+DATA/EMTST/ONLINELOG/redo_0401.log', '+DATA/EMTST/ONLINELOG/redo_0402.log' ) SIZE 100 M  REUSE
 DATAFILE
  '+DATA/emtst/datafile/system.282.665770693'
 CHARACTER SET AL32UTF8


contents of Memory Script:
{
   set newname for tempfile  2 to
 "+data";
   switch clone tempfile all;
   catalog clone datafilecopy  "+DATA/emtst/datafile/undotbs1.283.665770693";
   catalog clone datafilecopy  "+DATA/emtst/datafile/sysaux.292.665770695";
   catalog clone datafilecopy  "+DATA/emtst/datafile/users.284.665770693";
   catalog clone datafilecopy  "+DATA/emtst/datafile/mgmt_tablespace.291.665770703";
   catalog clone datafilecopy  "+DATA/emtst/datafile/mgmt_ecm_depot_ts.293.665770695";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed temporary file 2 to +data in control file

cataloged datafile copy
datafile copy filename=+DATA/emtst/datafile/undotbs1.283.665770693 recid=1 stamp=665770907

cataloged datafile copy
datafile copy filename=+DATA/emtst/datafile/sysaux.292.665770695 recid=2 stamp=665770907

cataloged datafile copy
datafile copy filename=+DATA/emtst/datafile/users.284.665770693 recid=3 stamp=665770907

cataloged datafile copy
datafile copy filename=+DATA/emtst/datafile/mgmt_tablespace.291.665770703 recid=4 stamp=665770907

cataloged datafile copy
datafile copy filename=+DATA/emtst/datafile/mgmt_ecm_depot_ts.293.665770695 recid=5 stamp=665770907

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=665770907 filename=+DATA/emtst/datafile/undotbs1.283.665770693
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=665770907 filename=+DATA/emtst/datafile/sysaux.292.665770695
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=665770907 filename=+DATA/emtst/datafile/users.284.665770693
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=665770907 filename=+DATA/emtst/datafile/mgmt_tablespace.291.665770703
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=665770907 filename=+DATA/emtst/datafile/mgmt_ecm_depot_ts.293.665770695

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 18-SEP-08
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-12005: error during channel cleanup
ORA-06502: PL/SQL: numeric or value error: character to number conversion error

Recovery Manager complete.

Logon to the the Auxiliary Database (EMTST) and Verify

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/emtst/datafile/system.282.665770693
+DATA/emtst/datafile/undotbs1.283.665770693
+DATA/emtst/datafile/sysaux.292.665770695
+DATA/emtst/datafile/users.284.665770693
+DATA/emtst/datafile/mgmt_tablespace.291.665770703
+DATA/emtst/datafile/mgmt_ecm_depot_ts.293.665770695

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+DATA/emtst/controlfile/control01.ctl
+DATA/emtst/controlfile/control02.ctl
+DATA/emtst/controlfile/control03.ctl

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+DATA/emtst/onlinelog/redo_0401.log
+DATA/emtst/onlinelog/redo_0402.log
+DATA/emtst/onlinelog/redo_0301.log
+DATA/emtst/onlinelog/redo_0302.log
+DATA/emtst/onlinelog/redo_0201.log
+DATA/emtst/onlinelog/redo_0202.log
+DATA/emtst/onlinelog/redo_0101.log
+DATA/emtst/onlinelog/redo_0102.log

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+DATA/emtst/tempfile/temp.295.665770955

Here is how to adjust the aforementioned example to use an RMAN Recovery Catalog

Create the RMAN Recovery Catalog

$ export ORACLE_SID=jlc2
$ . oraenv
$ sqlplus "/ as sysdba"

SQL> create user rman identified by rman
  2  temporary tablespace temp
  3  default tablespace users quota unlimited on users;

User created.

SQL> grant recovery_catalog_owner to rman;

Grant succeeded.

Create the Recovery Catalog and Register the TARGET Database (EMREP)

$ export ORACLE_SID=emrep
$ . oraenv
$ rman catalog=rman/rman@jlcc target=/

RMAN> create catalog;

recovery catalog created

RMAN> register database;

Create a Connection File on the TARGET to obfuscate the RMAN password

rman.connect

connect catalog rman/rman@jlc;

Modify the file's permissions

$ chmod 711 rman.connect

Add the following line to the previously created backup script, backup_emrep.rman

@rman.connect

Create a New Backup of the TARGET (EMREP)

./run_backup.bsh > rman_emrep.log

Update the Connect File on the AUXILIARY (EMTST)

rman.connect

Add the following line:

connect catalog rman/rman@jlc;

Drop the Previously Created Database (EMTST)

$ export ORACLE_SID=emtst
$ . oraenv

SQL> shutdown immediate;
SQL> startup restrict mount;
SQL> drop database;
SQL> exit;

$ sqlplus "/ as sysdba"

SQL> startup nomount;
SQL> exit

Duplicate the Database using the Recovery Catalog

rman @duplicate_emrep_to_emtst.rman > duplicate_emrep_to_emtst.log &

You may have noticed the following error earlier on in this example:
"WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to diskgroup only"

Using OMF (Oracle Managed Files) and ASM with this parameter is a little tricky. I prefer to discontinue the use of OMF by relocating the files and renaming them; for example:

RMAN> COPY DATAFILE '+DATA2/emrep/datafile/undotbs1.258.665691079' TO '+DATA2/emrep/datafile/undotbs01.dbf';

SQL> alter database rename file '+DATA2/emrep/datafile/undotbs1.258.665691079' TO '+DATA2/emrep/datafile/undotbs01.dbf';

Once all the files have been explicitly named, file name conversion becomes much more straightforward.

If you are using explicitly named files on ASM, then you can alter db_file_name_convert; for example:

*.db_file_name_convert=('+DATA2/emrep','+DATA/emtst')

Provided that the necessary directories are there, this should work. You will, however, see the aforementioned error message. The intended mapping should - in essence - have the intended effects.

Cleaning up after a Failed Clusterware Install

2008-09-18T09:11:00.000-07:00

I normally use the following script when cleaning up after a failed Cluster Ready Services (Clusterware) install.

In my example, I remove the dependent files and also the entire Oracle Inventory and Binaries directories: this script is Linux-specific.

$ ./remove_crs_files.sh

remove_crs_files.sh

rm -Rf /etc/oracle/*
rm -f /etc/init.d/init.cssd
rm -f /etc/init.d/init.crs
rm -f /etc/init.d/init.crsd
rm -f /etc/init.d/init.evmd
rm -f /etc/rc2.d/K96init.crs
rm -f /etc/rc2.d/S96init.crs
rm -f /etc/rc3.d/K96init.crs
rm -f /etc/rc3.d/S96init.crs
rm -f /etc/rc5.d/K96init.crs
rm -f /etc/rc5.d/S96init.crs
rm -Rf /etc/oracle/scls_scr
rm -f /etc/inittab.crs
cp /etc/inittab.orig /etc/inittab
rm -Rf /u01/app/crs
mkdir /u01/app/crs
chown oracle:dba /u01/app/crs
rm -Rf /u01/app/oracle/oraInventory
mkdir /u01/app/oracle/oraInventory
chown oracle:dba /u01/app/oracle/oraInventory

For more information, reference the following Metalink Article: 239998.1

How to Migrate a Standalone Database to ASM

2008-09-18T08:03:00.000-07:00

The following example shows how to migrate a standalone database to ASM. My environment uses an ASM instance which is version 11.1, ASMLib, and a 10.2.0.4 database (my Grid Control database) on Linux.

I will move the EM database to the DATA2 diskgroup:

ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Name
MOUNTED  EXTERN  N         512   4096  1048576     25023    21597                0           21597              0  DATA/
MOUNTED  EXTERN  N         512   4096  1048576     25023    24930                0           24930              0  DATA2/

In order to migrate the database, it must be in ARCHIVELOG mode. If not in ARCHIVELOG mode, place the database in it now

$ sqlplus "/ as sysdba"
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     10
Current log sequence           13

SQL> alter system set log_archive_format='emrep_%t_%s_%r.arch' scope=spfile;

System altered.

SQL> alter system set log_archive_dest_1='LOCATION=/u02/oracle/arch/' scope=spfile;

System altered.

SQL> startup mount;
ORACLE instance started.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/oracle/arch/
Oldest online log sequence     13
Next log sequence to archive   16
Current log sequence           16

SQL> alter system switch logfile;

System altered.

SQL> alter system archive log current;

System altered.

SQL> ! ls -lart /u02/oracle/arch
total 28506
drwxr-xr-x  2 oracle dba     4096 Apr 16 15:31 lost+found
drwxrwxr-x  8 oracle dba     4096 Sep 14 12:39 ..
-rw-r-----  1 oracle dba 29053440 Sep 17 10:43 emrep_1_16_665591431.arch
drwxr-xr-x  3 oracle dba    24576 Sep 17 10:44 .
-rw-r-----  1 oracle dba   103424 Sep 17 10:44 emrep_1_17_665591431.arch

Backup the Database to the Diskgroup(s)

$ rman target=/
RMAN> backup as copy database format '+DATA2';

Output should be similar to the following:

Starting backup at 17-SEP-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u01/app/oracle/oradata/emrep/mgmt.dbf
output filename=+DATA2/emrep/datafile/mgmt_tablespace.256.665690321 tag=TAG20080917T175835 recid=1 stamp=665690971
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:10:57
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/u01/app/oracle/oradata/emrep/system01.dbf
output filename=+DATA2/emrep/datafile/system.257.665690973 tag=TAG20080917T175835 recid=2 stamp=665691068
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:45
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u01/app/oracle/oradata/emrep/undotbs01.dbf
output filename=+DATA2/emrep/datafile/undotbs1.258.665691079 tag=TAG20080917T175835 recid=3 stamp=665691129
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u01/app/oracle/oradata/emrep/sysaux01.dbf
output filename=+DATA2/emrep/datafile/sysaux.259.665691133 tag=TAG20080917T175835 recid=4 stamp=665691166
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/u01/app/oracle/oradata/emrep/mgmt_ecm_depot1.dbf
output filename=+DATA2/emrep/datafile/mgmt_ecm_depot_ts.260.665691169 tag=TAG20080917T175835 recid=5 stamp=665691194
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/u01/app/oracle/oradata/emrep/users01.dbf
output filename=+DATA2/emrep/datafile/users.261.665691205 tag=TAG20080917T175835 recid=6 stamp=665691204
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=+DATA2/emrep/controlfile/backup.262.665691205 tag=TAG20080917T175835 recid=7 stamp=665691207
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 17-SEP-08
channel ORA_DISK_1: finished piece 1 at 17-SEP-08
piece handle=+DATA2/emrep/backupset/2008_09_17/nnsnf0_tag20080917t175835_0.263.665691209 tag=TAG20080917T175835 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 17-SEP-08

You should see entries in the alert log similar to:

Starting background process ASMB
ASMB started with pid=52, OS id=10868
Starting background process RBAL
RBAL started with pid=53, OS id=10885
Wed Sep 17 17:22:20 2008
SUCCESS: diskgroup DATA2 was mounted

During the backup you should see files created in the Diskgroup:

$ export ORACLE_SID=+ASM1
$ . oraenv
ASMCMD> cd DATA2/EMREP/DATAFILE
ASMCMD> ls
MGMT_TABLESPACE.256.665690321
SYSTEM.257.665690973

Switch the Database to the Copy on ASM

RMAN> shutdown immediate;

RMAN> startup mount;

database mounted

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA2/emrep/datafile/system.257.665690973"
datafile 2 switched to datafile copy "+DATA2/emrep/datafile/undotbs1.258.665691079"
datafile 3 switched to datafile copy "+DATA2/emrep/datafile/sysaux.259.665691133"
datafile 4 switched to datafile copy "+DATA2/emrep/datafile/users.261.665691205"
datafile 5 switched to datafile copy "+DATA2/emrep/datafile/mgmt_tablespace.256.665690321"
datafile 6 switched to datafile copy "+DATA2/emrep/datafile/mgmt_ecm_depot_ts.260.665691169"

RMAN> recover database;

Starting recover at 17-SEP-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 17-SEP-08

RMAN> alter database open;

You should see the datafiles assigned to their new locations:

SQL> col name format a60
SQL> select name from v$datafile;

NAME
------------------------------------------------------------
+DATA2/emrep/datafile/system.257.665690973
+DATA2/emrep/datafile/undotbs1.258.665691079
+DATA2/emrep/datafile/sysaux.259.665691133
+DATA2/emrep/datafile/users.261.665691205
+DATA2/emrep/datafile/mgmt_tablespace.256.665690321
+DATA2/emrep/datafile/mgmt_ecm_depot_ts.260.665691169

6 rows selected.

Switch Tempfiles to the ASM Diskgroups

SQL> alter tablespace temp add tempfile '+DATA2' size 250M;

Tablespace altered.

SQL> select name from v$tempfile;

NAME
------------------------------------------------------------
/u01/app/oracle/oradata/emrep/temp01.dbf
+DATA2/emrep/tempfile/temp.264.665692035

SQL> alter database tempfile '/u01/app/oracle/oradata/emrep/temp01.dbf' drop  including datafiles;

Database altered.

SQL> select name from v$tempfile;

NAME
------------------------------------------------------------
+DATA2/emrep/tempfile/temp.264.665692035

Switch the Online Redo Logs to the ASM diskgroup

SQL> alter database add logfile group 5 ('+DATA2','+DATA2') size 100M;

Database altered.

SQL> alter database add logfile group 6 ('+DATA2','+DATA2') size 100M;

Database altered.

SQL> alter database add logfile group 7 ('+DATA2','+DATA2') size 100M;

Database altered.

SQL> alter database add logfile group 8 ('+DATA2','+DATA2') size 100M;

Database altered.

You should see the Online Redo Logs created in the ASM instance:

ASMCMD> cd DATA2/EMREP/ONLINELOG
ASMCMD> ls
group_5.265.665693021
group_5.266.665693027
group_6.267.665693117
group_6.268.665693121
group_7.269.665693133
group_7.270.665693141
group_8.271.665693163
group_8.272.665693169

Switch through Logfile Groups and drop as possible

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database drop logfile group 4;

Database altered.

Afterwards, only the Online Redo Logs on the ASM diskgroup should remain:

SQL> select bytes/1024/1024, group#, thread#, status from v$log;

BYTES/1024/1024     GROUP#    THREAD# STATUS
--------------- ---------- ---------- ----------------
            100          5          1 ACTIVE
            100          6          1 ACTIVE
            100          7          1 ACTIVE
            100          8          1 CURRENT

SQL> select member from v$logfile;

MEMBER
---------------------------------------------------------------------------
+DATA2/emrep/onlinelog/group_5.265.665693021
+DATA2/emrep/onlinelog/group_5.266.665693027
+DATA2/emrep/onlinelog/group_6.267.665693117
+DATA2/emrep/onlinelog/group_6.268.665693121
+DATA2/emrep/onlinelog/group_7.269.665693133
+DATA2/emrep/onlinelog/group_7.270.665693141
+DATA2/emrep/onlinelog/group_8.271.665693163
+DATA2/emrep/onlinelog/group_8.272.665693169

Migrate the Controlfiles to ASM

SQL> alter system set control_files='+DATA2','+DATA2','+DATA2' scope=spfile;

System altered.

SQL> shutdown immediate;

$ rman target=/

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Sep 17 19:03:37 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database (not started)

RMAN> startup nomount;

database is already started

RMAN> restore controlfile from '/u01/app/oracle/oradata/emrep/control01.ctl';

Starting restore at 17-SEP-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=+DATA2/emrep/controlfile/current.273.665694281
output filename=+DATA2/emrep/controlfile/current.274.665694285
output filename=+DATA2/emrep/controlfile/current.275.665694285
Finished restore at 17-SEP-08

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> alter database open;

The control_files parameter should be updated via RMAN if you are using an spfile. If using a pfile, you will have to update this value yourself.

Optionally Migrate the Archive Log Files

SQL> alter system set log_archive_dest_1='LOCATION=+DATA2' scope=spfile;

System altered.

SQL> shutdown immediate;

SQL> startup

SQL> alter system archive log current;

Afterwards, you should see Archivelogs in the ASM instance:

$ asmcmd
ASMCMD> ls DATA2/EMREP/ARCHIVELOG
2008_09_17/

ASMCMD> cd DATA2/EMREP/ARCHIVELOG/2008_09_17
ASMCMD> ls
thread_1_seq_61.276.665694577

Drop the Copy of the Database

$ rman target=/

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Sep 17 19:12:26 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: EMREP (DBID=3888623110)

RMAN> list copy of database;

using target database control file instead of recovery catalog

List of Datafile Copies
Key     File S Completion Time Ckp SCN    Ckp Time        Name
------- ---- - --------------- ---------- --------------- ----
8       1    A 17-SEP-08       2607286    17-SEP-08       /u01/app/oracle/oradata/emrep/system01.dbf
9       2    A 17-SEP-08       2607286    17-SEP-08       /u01/app/oracle/oradata/emrep/undotbs01.dbf
10      3    A 17-SEP-08       2607286    17-SEP-08       /u01/app/oracle/oradata/emrep/sysaux01.dbf
11      4    A 17-SEP-08       2607286    17-SEP-08       /u01/app/oracle/oradata/emrep/users01.dbf
12      5    A 17-SEP-08       2607286    17-SEP-08       /u01/app/oracle/oradata/emrep/mgmt.dbf
13      6    A 17-SEP-08       2607286    17-SEP-08       /u01/app/oracle/oradata/emrep/mgmt_ecm_depot1.dbf

RMAN> drop copy of database;

Do you really want to delete the above objects (enter YES or NO)? YES

deleted datafile copy
datafile copy filename=/u01/app/oracle/oradata/emrep/system01.dbf recid=8 stamp=665691542
deleted datafile copy
datafile copy filename=/u01/app/oracle/oradata/emrep/undotbs01.dbf recid=9 stamp=665691542
deleted datafile copy
datafile copy filename=/u01/app/oracle/oradata/emrep/sysaux01.dbf recid=10 stamp=665691542
deleted datafile copy
datafile copy filename=/u01/app/oracle/oradata/emrep/users01.dbf recid=11 stamp=665691542
deleted datafile copy
datafile copy filename=/u01/app/oracle/oradata/emrep/mgmt.dbf recid=12 stamp=665691542
deleted datafile copy
datafile copy filename=/u01/app/oracle/oradata/emrep/mgmt_ecm_depot1.dbf recid=13 stamp=665691542
Deleted 6 objects

Obviously, you should distribute your files in a way that maximizes I/O throughput, etc. This example simply demonstrates mechanically how to perform the migration.

Reorganizing a Table using DBMS_REDEFINITION

2008-07-03T10:17:00.000-07:00

There are many methods to reorganize/defragment a table in Oracle. If you want to avoid downtime, however, your choices are limited to using the 'SHRINK SPACE' feature and/or employing the DBMS_REDEFINITION PL/SQL package.

This post demonstrates the latter method. The DBMS_REDEFINITION package is most commonly used for redefining a table, however can be used to reorganize a table amongst other things. Here is a great article which covers the package in a little more detail: On-line Table Reorganization and Redefinition.

This example rebuilds the OE.ORDER_ITEMS table using DBMS_REDEFINITION.

Collect information about the table's segments prior to the reorg

ANALYZE TABLE oe.order_items COMPUTE STATISTICS;

SELECT avg_row_len, num_rows, chain_cnt
  FROM dba_tables
 WHERE table_name = 'ORDER_ITEMS' AND owner = 'OE';

SELECT ROUND (BYTES / 1024) kb
  FROM dba_segments
 WHERE owner = 'OE' AND segment_name = 'ORDER_ITEMS';

Check whether the table in question violates any redefinition restrictions

EXEC dbms_redefinition.can_redef_table('OE','ORDER_ITEMS', dbms_redefinition.cons_use_pk);

Create the Intermediary Table - in my case, I use the exact same structure

CREATE TABLE OE.ORDER_ITEMS_REDEF
(
  ORDER_ID      NUMBER(12),
  LINE_ITEM_ID  NUMBER(3)                       NOT NULL,
  PRODUCT_ID    NUMBER(6)                       NOT NULL,
  UNIT_PRICE    NUMBER(8,2),
  QUANTITY      NUMBER(8)
) TABLESPACE USERS;

Start the Redefinition Process

EXEC dbms_redefinition.start_redef_table('OE', 'ORDER_ITEMS', 'ORDER_ITEMS_REDEF');

You will notice that Oracle creates a Materialized View in order to store the table changes, etc.

SELECT COUNT (*)
  FROM oe.mlog$_order_items;

Sync up the Source table with the Intermediary table

EXEC dbms_redefinition.sync_interim_table('OE', 'ORDER_ITEMS', 'ORDER_ITEMS_REDEF');

Duplicate the table's dependent objects on the Intermediary table

CREATE INDEX oe.item_order_redef_ix ON oe.order_items_redef
(order_id) LOGGING TABLESPACE users;

CREATE INDEX oe.item_product_redef_ix ON oe.order_items_redef
(product_id) LOGGING TABLESPACE users;

CREATE UNIQUE INDEX oe.order_items_redef_pk ON oe.order_items_redef
(order_id, line_item_id) LOGGING TABLESPACE users;

CREATE UNIQUE INDEX oe.order_items_redef_uk ON oe.order_items_redef
(order_id, product_id) LOGGING TABLESPACE users;

ALTER TABLE oe.order_items_redef ADD (
  CONSTRAINT order_items_redef_pk
 PRIMARY KEY (order_id, line_item_id) USING INDEX TABLESPACE USERS);

ALTER TABLE oe.order_items_redef ADD (
  CONSTRAINT order_items_redef_order_id_fk
 FOREIGN KEY (order_id)
 REFERENCES oe.orders (order_id)
 ON DELETE CASCADE DISABLE NOVALIDATE);

ALTER TABLE oe.order_items_redef ADD (
  CONSTRAINT order_items_rdef_product_id_fk
 FOREIGN KEY (product_id)
 REFERENCES oe.product_information (product_id) DISABLE NOVALIDATE);

GRANT SELECT ON oe.order_items_redef TO bi;

GRANT SELECT ON oe.order_items_redef TO pm;

Notice how, I create the foreign key constraints initially as disabled.

To simplify things, I use the following to duplicate any triggers to the Intermediary table

DECLARE
 retval NUMBER(5);
BEGIN
 dbms_redefinition.copy_table_dependents('OE', 'ORDER_ITEMS', 'ORDER_ITEMS_REDEF', copy_indexes=>0, copy_triggers=>TRUE, copy_constraints=>FALSE,copy_privileges=>FALSE, num_errors=>retval);
 dbms_output.put_line(retval);
END;
/

Disable any foreign key constraints on the Source table before finishing the table redefinition process

ALTER TABLE oe.order_items DISABLE CONSTRAINT order_items_order_id_fk;

ALTER TABLE oe.order_items DISABLE CONSTRAINT order_items_product_id_fk;

Finish the Redefinition

EXEC dbms_redefinition.finish_redef_table('OE', 'ORDER_ITEMS', 'ORDER_ITEMS_REDEF');

Drop the Intermediary Table

DROP TABLE oe.order_items_redef;

Rename any dependent objects

ALTER TABLE oe.order_items RENAME CONSTRAINT order_items_rdef_product_id_fk TO order_items_product_id_fk;

ALTER TABLE oe.order_items RENAME CONSTRAINT order_items_redef_order_id_fk TO order_items_order_id_fk;

ALTER TABLE oe.order_items RENAME CONSTRAINT order_items_redef_pk TO order_items_pk;

ALTER INDEX oe.item_order_redef_ix RENAME TO item_order_ix;

ALTER INDEX oe.item_product_redef_ix RENAME TO item_product_ix;

ALTER INDEX oe.order_items_redef_pk RENAME TO order_items_pk;

ALTER INDEX oe.order_items_redef_uk RENAME TO order_items_uk;

At this point, the table should be reorganized/defragmented. Optionally, compare the storage footprint post-redefintion

ANALYZE TABLE oe.order_items COMPUTE STATISTICS;

SELECT avg_row_len, num_rows, chain_cnt
  FROM dba_tables
 WHERE table_name = 'ORDER_ITEMS' AND owner = 'OE';

SELECT ROUND (BYTES / 1024) kb
  FROM dba_segments
 WHERE owner = 'OE' AND segment_name = 'ORDER_ITEMS';

Rebuilding Indexes the Easy Way

2008-06-30T15:15:00.000-07:00

Oracle has made index rebuilding easier through the introduction of the DBMS_INDEX_UTL PL/SQL package back in 10g.

In lieu of creating hundreds of seperate rebuild DDL statements, you can now create single calls to this package's methods in order to accomplish the same task(s).

This example demonstrates how to rebuild all indexes - including those with partitions - for the SH.SALES table - by constructing a single call to the aforementioned package.

Here are the indexes in question for my test system:

SELECT 
     TRIM(di.owner) "OWNER", TRIM (di.index_name) "INDEX",
     TRIM(MAX (TO_CHAR (dip.last_analyzed, 'MON-DD-YYYY HH24:MI:SS'))) "LAST ANALYZED",
     COUNT(dip.partition_name) "PART COUNT",
     ROUND(SUM (ds.BYTES) / 1024) "KB"
     FROM  dba_indexes di, 
           dba_ind_partitions dip, 
           dba_segments ds
     WHERE di.index_name = ds.segment_name
     AND di.owner = ds.owner
     AND dip.partition_name = ds.partition_name
     AND di.table_owner = 'SH'
     AND di.table_name = 'SALES'
     AND di.index_name = dip.index_name
     AND di.owner = dip.index_owner
     AND di.owner = dip.index_owner
GROUP BY di.owner, di.index_name;

OWNER                          INDEX                          LAST ANALYZED              PART COUNT         KB
------------------------------ ------------------------------ -------------------------- ---------- ----------
SH                             SALES_TIME_BIX                 JUN-30-2008 16:14:27               28       9216
SH                             SALES_CHANNEL_BIX              JUN-30-2008 16:14:18               28       4288
SH                             SALES_PROD_BIX                 JUN-30-2008 16:14:24               28       5056
SH                             SALES_CUST_BIX                 JUN-30-2008 16:14:23               28      46848
SH                             SALES_PROMO_BIX                JUN-30-2008 16:14:26               28       2944

5 rows selected.

I will rebuild all these indexes via the following call:

EXEC dbms_index_utl.build_table_indexes (list=>'SH.SALES',just_unusable=>FALSE, locality=>'ALL',concurrent=>TRUE, cont_after_err=>TRUE, max_slaves=>8);
PL/SQL procedure successfully completed.

Afterwards, the same query shows that the overall footprint of the indexes in question has been reduced. Additionally, the segments have been analyzed as part of the rebuild.

OWNER                          INDEX                          LAST ANALYZED              PART COUNT         KB
------------------------------ ------------------------------ -------------------------- ---------- ----------
SH                             SALES_TIME_BIX                 JUN-30-2008 17:03:47               28       7104
SH                             SALES_CHANNEL_BIX              JUN-30-2008 17:03:42               28       4288
SH                             SALES_PROD_BIX                 JUN-30-2008 17:03:44               28       3968
SH                             SALES_CUST_BIX                 JUN-30-2008 17:03:44               28      37632
SH                             SALES_PROMO_BIX                JUN-30-2008 17:03:46               28       3008

5 rows selected.

Identifying Resource-Intensive SQL

2008-06-18T14:25:00.001-07:00

Here is a short example on how to identify resource-intensive SQL by querying the DBA_HIST_* views.

In my example, I query the AWR, ranking the top 20 SQL statements using a rough formula based upon CPU and IO-related statistics for the time period in question. In my case, I am querying the current day, but this can be changed to examine time periods further in the past as well.

Top 20 Resource-intensive SQL Example

Top 20 Resource-intensive SQL Example Source (Right-Click - Save Target As...)

Pipelined SQL Function Example

2008-06-18T14:13:00.000-07:00

Here is a short example on how to create and use a pipelined sql function. In my example, I use a pipelined function along with a MERGE statement to keep a star schema's dimension table up to date.

Pipelined SQL Example

Pipelined SQL Example Source (Right-Click - Save Target As...)

Setting up OID (LDAP) for TNS Service Resolution

2008-06-08T10:44:00.000-07:00

Here are the steps for configuring OID/LDAP to use for your Net Service (TNS) resolution.

This example assumes that you already have an LDAP directory available for use, in my case Oracle Internet Directory (OID) installed as part of OracleAS Infrastructure/Identity Management. Additionally, this example does not cover SSL.

Launch the Net Configuration Assistant

# export DISPLAY=192.168.1.3:0.0
# netca

Set-up the Directory Usage Configuration

At this point, you should have a properly configured directory file similar to the following:

ldap.ora

DIRECTORY_SERVERS= (rac2.colestock.test:389:636)

DEFAULT_ADMIN_CONTEXT = "dc=colestock,dc=test"

DIRECTORY_SERVER_TYPE = OID

Configure your Oracle Naming Configuration. In my case, I will use my OID (LDAP) directory as my primary lookup service; if it fails, the tnsnames.ora will be used.

At this point, your sqlnet.ora file should be properly configured for OID (LDAP) use:

sqlnet.ora

NAMES.DIRECTORY_PATH= (LDAP, TNSNAMES)

Import your Database Services, etc. into your OID (LDAP) Directory via Oracle Network Manager

# export DISPLAY=192.168.1.3:0.0
# netmgr

Manually confirm that the entries are present via a manual search of LDAP:

# ldapsearch -h rac2 -p 389 -D cn=orcladmin -w password -b "cn=OracleContext,dc=colestock,dc=test" -s one "objectclass=orclNetService" orclnetdescstring

cn=LDG,cn=OracleContext,dc=colestock,dc=test
orclnetdescstring=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.101)(PORT=1525)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ldg.colestock.test)))

cn=EM,cn=OracleContext,dc=colestock,dc=test
orclnetdescstring=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=rac2-vip)(PORT=1521))(LOAD_BALANCE=yes))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=em.colestock.test)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))))

cn=STDBY,cn=OracleContext,dc=colestock,dc=test
orclnetdescstring=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.102)(PORT=1525)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=stdby.colestock.test)))

cn=STANDBY,cn=OracleContext,dc=colestock,dc=test
orclnetdescstring=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.102)(PORT=1525)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=stdby.colestock.test)))

cn=EM2,cn=OracleContext,dc=colestock,dc=test
orclnetdescstring=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=rac2-vip)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=em.colestock.test)(INSTANCE_NAME=em2)))

cn=EM1,cn=OracleContext,dc=colestock,dc=test
orclnetdescstring=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1-vip)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=em.colestock.test)(INSTANCE_NAME=em1)))

Confirm SQLNet's use of the OID (LDAP) via a tnsping:

# tnsping EM

Used parameter files:
/u01/app/oracle/product/10.2/network/admin/sqlnet.ora

Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=rac2-vip)(PORT=1521))(LOAD_BALANCE=yes))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=em.colestock.test)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))))
OK (0 msec)

How to Install OracleAS Infrastructure (10.1.4.0.1)

2008-06-05T18:37:00.000-07:00

This post assumes that you want to install OracleAS Infrastructure (10.1.4.0.1) using an existing Metadata Repository, created earlier with the Metadata Repository Creation Assistant (MRCA): see the following post.

The above is normally accomplished in steps to allow for a more distributed topology for the OracleAS Infrastructure installation. In my case, I will place the Metadata Repository (database) on one server and then use another server for the remaining components, such as the Application Server. This posting covers the latter step.

Unpack the appropriate software

# cpio -idmv < as_linux_x86_oim_oif_101401_disk1.cpio
# cpio -idmv < as_linux_x86_oim_oif_101401_disk2.cpio

Due to some installation errors, namely inventory corruption issues, I specify an installation-specific inventory location via /etc/oraInst.loc (depending upon your installation, you may have to do so as root)

# su - root
# vi /etc/oraInst.loc
# more /etc/oraInst.loc
inventory_loc=/u01/app/oracle/product/oim/oraInventory
inst_group=dba

Set the environment

# echo "oim:/u01/app/oracle/product/oim:N" >> /etc/oratab
# export ORACLE_SID=oim
# . oraenv
# export DISPLAY=192.168.1.3:0.0
# export TMP=/tmp
# export TMPDIR=/tmp
# unset TNS_ADMIN=
# export PATH=.:/usr/local/java/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:/usr/bin:/bin:/usr/local/bin:/u02/oracle/logs/em/scripts/common/oracle/bin:/usr/bin/X11
#  CLASSPATH=
# export LD_LIBRARY_PATH=/lib:/usr/lib:/usr/local/lib

Run the Installer

# ./runInstaller -ignoreSysPreReqs

Run the root.sh as root

# su - root
# cd /u01/app/oracle/product/oim
# ./root.sh
Running Oracle10 root.sh script...
\nThe following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/oracle/product/oim

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]:
The file "oraenv" already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]:
The file "coraenv" already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]:

Adding entry to /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
OCR is configured for Oracle Cluster Ready Services. CSS is already configured and should be running from Oracle Cluster Ready Services home


Entering Oracle Internet Directory Root Installation Section

OiD Server Installation
Checking LDAP binary file protections
Setting oidmon file protections
Setting oidldapd file protections
Setting oidrepld file protections
Setting oidemdpasswd file protections
Setting remtool file protections
Setting oiddiag file protections
Leaving Oracle Internet Directory Root Installation Section

Post Installation Steps

Check key URLs:

Validate that components are running

# cd /u01/app/oracle/product/oim/opmn/bin/
# ./opmnctl status

Processes in Instance: jlc.rac2.colestock.test
-------------------+--------------------+---------+---------
ias-component      | process-type       |     pid | status
-------------------+--------------------+---------+---------
DSA                | DSA                |    9142 | Alive
LogLoader          | logloaderd         |     N/A | Down
dcm-daemon         | dcm-daemon         |   28047 | Alive
OC4J               | oca                |    5202 | Alive
OC4J               | OC4J_SECURITY      |    4824 | Alive
HTTP_Server        | HTTP_Server        |    4630 | Alive
OID                | OID                |   14673 | Alive

Check that you can login to the Oracle Internet Directory as cn=orcladmin

# export DISPLAY=192.168.1.3:0.0
# oidadmin

Before verifying that single sign-on is operating, you will have to work around a known bug

SSO Authentication Fails With Internal Server Error After Upgrading To 10.1.4 Or Using MRCA 10.1.4.0.1 (Metalink NOTE:467706.1)

After install you will not be able to use SSO, instead the following error will be in the log files:

java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'ADD_AUDIT_LOG'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

The workaround according to Oracle:

The SSO database schema needs to be upgraded to 10.1.4 by running the ssopatch.sql as user orasso.

1. Get the orasso database password:
ldapsearch -D cn=orcladmin -w -p
-b "cn=IAS,cn=Products,cn=OracleContext" -s sub "OrclresourceName=ORASSO" orclpasswordattribute

2. $ORACLE_HOME/opmn/bin/opmnctl stopall

3. cd $ORACLE_HOME/sso/admin/plsql/sso

4. sqlplus orasso/@

5. restart Identity Management
$ORACLE_HOME/opmn/bin/opmnctl starttall

In my case:

[oracle@rac2 log]$ ldapsearch -D cn=orcladmin -w password -p 389 -b "cn=IAS,cn=Products,cn=OracleContext" -s sub "OrclresourceName=ORASSO" orclpasswordattribute
OrclResourceName=ORASSO,orclReferenceName=EM.COLESTOCK.TEST,cn=IAS Infrastructure Databases,cn=IAS,cn=Products,cn=OracleContext
orclpasswordattribute=WV1z3ihA

# $ORACLE_HOME/opmn/bin/opmnctl stopall
opmnctl: stopping opmn and all managed processes...

# cd $ORACLE_HOME/sso/admin/plsql/sso                          
# sqlplus orasso/WV1z3ihA@em @ssopatch.sql

# $ORACLE_HOME/opmn/bin/opmnctl startall
opmnctl: starting opmn and all managed processes...

Afterwards, you can validate that SSO is functional by logging in as orcladmin

Optionally, set-up your OID installation for Net Service Name Resolution - reference my post on Setting Up OID (LDAP)

How to Install Metadata Repository for Oracle Infrastructure/Identity Management

2008-06-04T11:40:00.000-07:00

Install the Respository Configuration Assistant Software

Prepare the kernel for RepCA:

# su - root

# vi /etc/sysctl.conf

# /sbin/sysctl -p
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
net.core.rmem_default = 262144
net.core.wmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_max = 262144
kernel.shmmax = 4294967295
kernel.sem = 256 32000 100 142
fs.file-max = 206173
net.ipv4.ip_local_port_range = 1024 65000
kernel.msgmni = 2878
kernel.msgmnb = 65535

Unpack the Software and Install:

# cpio -idmv < as_linux_x86_mrca_101203.cpio

# export DISPLAY=192.168.1.3:0.0
# cd Disk1
# ./runInstaller

Run the root.sh script as root:

# pwd
/u01/app/oracle/product/mrca
# ./root.sh

Update /etc/oratab:

# echo "mrca:/u01/app/oracle/product/mrca:N" >> /etc/oratab
# export ORACLE_SID=mrca
# . oraenv

Prepare the destination database for the installation of the OracleAS Repository:

SQL> alter system set sga_target=600M scope=spfile;
SQL> alter system set pga_aggregate_target=96M scope=spfile;
SQL> alter system set java_pool_size=120M scope=spfile;
SQL> alter system set shared_pool_size=175M scope=spfile;
SQL> alter system set db_cache_size=144M scope=spfile;
SQL> alter system set sessions=400 scope=spfile;

# srvctl stop database -d em
# srvctl start database -d em

If not already present, install Ultrasearch Database Option:

# export ORACLE_SID=em1
# . oraenv
# export DISPLAY=192.168.1.3:0.0
# dbca

Check that all prerequisites for Installing the Metadata Repository Configuration Assistant are met:

# export ORACLE_SID=mrca
# . oraenv
# cd $ORACLE_HOME/repca/prereq

# DBPrereqChecker -DBHOST rac1.colestock.test -DBPORT 1521 -DBNAME em.colestock.test -SYSPWD password -LOGDIR /tmp/
Starting validation DatabasePrerequisite
Validation Succeeded -> DatabasePrerequisite
Validation check complete

Install the Metadata Repository Configuration Assistant:

# cd $ORACLE_HOME
# pwd
/u01/app/oracle/product/mrca
# ./runRepca

Resize any files as prompted:

SQL> alter database datafile '+DATA/em/datafile/undotbs1.261.655668297' resize 600M;
SQL> alter database datafile '+DATA/em/datafile/system.258.655668171' resize 1G;
SQL> alter database datafile '+DATA/em/datafile/undotbs2.260.655668271' resize 750M;

Before Installing the OracleAS Infrastructure/Identity Management you have to address the following bugs:

Bug 5612298 (SEE METALINK - Note:550260.1)

Download Patch 5612298 for HP-UX PA-RISC (64-bit) or HP-UX Itanium

MR schemas and objects will need to be cleaned up within the Database using the MRCA Installation Guide instructions.

Follow instructions in Patch 5612298 readme file:

After the MRCA successful installs, copy the patch ldap.tar to $ORACLE_HOME and extract the ldap.tar file from the $ORACLE_HOME by doing the following steps:

cp ldap.tar $ORACLE_HOME
cd $ORACLE_HOME
tar xvf ldap.tar

Note that the aforementioned note mentions HP-UX as the platform, but this has effected me on Linux as well! Also, it is the MRCA $ORACLE_HOME in which you should unpack this .tar file.

Warning to Have SSO Schema Version at 10.1.4.0.1 During Installation (SEE METALINK - Note:433208.1)

1. Connect to the Oracle Database as SYSDBA and perform the following:

SQL> update orasso.wwc_version$ set version='10.1.4.0.1';
SQL> Commit;

2. Re-start the Oracle Identity Management 10g (10.1.4.0.1) installation

Accomplishing the above will allow for a successful posthumous install of OracleAS Infrastructure/Identity Management...

How to Create and Schedule a SSIS Job in SQL Server 2005

2008-06-01T12:04:00.000-07:00

This post covers how to create and schedule a SSIS job on SQL Server 2005.
The biggest challenge in doing this has to do more with the convaluted security setup, than with the SSIS package creation and/or scheduling.

The first step is to create the necessary security objects:

ssis_security_setup.sql

Create a Login who will own the job

USE [master]
GO

CREATE LOGIN [ssis_usr] WITH PASSWORD=N'password', DEFAULT_DATABASE=[msdb], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO

Grant the sysadmin role to the previously created login

sp_addsrvrolemember @loginame = N'ssis_usr', @rolename = N'sysadmin'
GO

Create a user in the msdb system database for the login, creating the mapping

USE [msdb]
GO

CREATE USER [ssis_usr] FOR LOGIN [ssis_usr]
GO

Grant the following roles to the user

sp_addrolemember N'SQLAgentReaderRole', N'ssis_usr';
GO

sp_addrolemember N'SQLAgentOperatorRole', N'ssis_usr'
GO

sp_addrolemember N'SQLAgentUserRole', N'ssis_usr'
GO

Create a credential to be used by the proxy - the proxy will execute the SSIS package within the job

USE [master]
GO

CREATE CREDENTIAL [ssis_cred] WITH IDENTITY = N'PRIVATE-E3A52F5\James', SECRET='password';
GO

Create the proxy, mapping to the credential and provide access to the SSIS subsystem

USE [msdb]
GO

sp_add_proxy @proxy_name=N'ssis_prxy', @credential_name=N'ssis_cred';
GO

sp_grant_proxy_to_subsystem @proxy_name=N'ssis_prxy', @subsystem_name=N'SSIS';
GO

Now you are ready to create a sample SSIS package.

I launch the SQL Server Business Intelligence Development Studio via All Programs -> Microsoft SQL Server 2005 -> SQL Server Business Intelligence Development Studio

I create a project named sample_ssis, which consists of a Transfer Database Task, which refreshes a database from one SQL Server instance to another:

sample_ssis.dstx

Task Properties

Once the sample SSIS package is created, import into the msdb system database via Integration Services:

Login to Integration Services

Import the SSIS Package from the File System

Login to the Database Engine using ssis_usr

Create a SQL Server Agent Job

Create a Step within the Job run by ssis_prxy

Create a Schedule for the Job

Monitor the Job

You should see something similar to the aforementioned if your job executed successfully.

SQL Server: Point in Time Recovery Example

2008-05-29T14:23:00.000-07:00

This example demonstrates how to backup a SQL Server Database and then restore it to a specific point in time.

The database in question uses the FULL recovery model, which supports such a PITR (Point in Time Recovery). The example uses a simple table to illustrate how all this works.

The example first creates a sample table and performs a full backup on the database. Then, values are inserted into the table periodically, interlaced with transaction log backups.

Set-up the example by running PITRBackupExample.sql.

The script yields the following output.

In this example, I want to restore the database to the point at which the 'Point-in-time I want to recover to!' value was inserted into the sample table. To do this, a restore point just after this transaction, specifically the '2008-05-28 11:58:09.680' timestamp, will need to be specified.

The recovery first places the database in single-user mode and performs a tail-log transaction log backup. Then, the full backup and transaction log backups are applied. For the last transaction log backup the STOPAT option is specified, along with the WITH RECOVERY option, which denotes that this operation is to end the recovery. Afterwards, the database is placed into multi-user mode. Last, we check the sample table to make sure that we recovered successfully to the desired timestamp.

Recover the example by running PITRExample.sql

The script yields the following output

Unable to Open MSDB in Integration Services when using Named Instance(s)

2008-05-27T12:06:00.000-07:00

Follow the accepted answer from the following post: Re: Unable to open MSDB node if you are using a named SQL Server instance and you can't access the SSIS packages within the MSDB database.

You will experience a 'Login Timeout' exception until you edit the XML file mentioned in the post. By default, Microsoft is configured to connect to the DEFAULT instance only; short of modifying this file, there is no way of specifying a different Database Service for Integration Services to use.

How to Migrate an Existing RAC database to ASM

2008-05-25T14:43:00.000-07:00

This post covers how to migrate an existing RAC database to use ASM storage. In my case, I use a 2-node, 10gR2 database on Linux that is currently using OCFS2 filesystems for the database's files. I will migrate the database to ASM using ASMLib on top of iSCSI volumes. This example assumes that you have already prepared the relevant storage devices for recognition by the O/S.

Prepare the Storage

As root on one of the hosts, first identify the device names to format:

# ./iscsi-map.sh
Host / SCSI ID    SCSI Device Name         iSCSI Target Name
----------------  -----------------------  -----------------
2                 /dev/sdb                 asm2
3                 /dev/sde                 asm1
4                 /dev/sdc                 arch
5                 /dev/sdf                 software
6                 /dev/sdd                 logs
7                 /dev/sdg                 ora2
8                 /dev/sdi                 ora1
9                 /dev/sdh                 crs

Format the devices you plan to use for the ASM migration:

# fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.


The number of cylinders for this disk is set to 25024.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
   (e.g., DOS FDISK, OS/2 FDISK)
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-25024, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-25024, default 25024):
Using default value 25024

Command (m for help): p

Disk /dev/sdb: 26.2 GB, 26239565824 bytes
64 heads, 32 sectors/track, 25024 cylinders
Units = cylinders of 2048 * 512 = 1048576 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sdb1               1       25024    25624560   83  Linux

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

# fdisk /dev/sde
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.


The number of cylinders for this disk is set to 25024.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
   (e.g., DOS FDISK, OS/2 FDISK)
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-25024, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-25024, default 25024):
Using default value 25024

Command (m for help): p

Disk /dev/sde: 26.2 GB, 26239565824 bytes
64 heads, 32 sectors/track, 25024 cylinders
Units = cylinders of 2048 * 512 = 1048576 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sde1               1       25024    25624560   83  Linux

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

Probe each host to pickup the changes via:

# partprobe
# fdisk -l

Configure ASMLib on each host via:

# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets ('[]').  Hitting  without typing an
answer will keep that current value.  Ctrl-C will abort.

Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Fix permissions of Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration:           [  OK  ]
Loading module "oracleasm":                                [  OK  ]
Mounting ASMlib driver filesystem:                         [  OK  ]
Scanning system for ASM disks:                             [  OK  ]

On one node as root, create the ASM Volumes via:

# /etc/init.d/oracleasm createdisk VOL1 /dev/sdb1
Marking disk "/dev/sdb1" as an ASM disk:                   [  OK  ]

# /etc/init.d/oracleasm createdisk VOL2 /dev/sde1
Marking disk "/dev/sde1" as an ASM disk:                   [  OK  ]

On all other nodes, scan to recognize the changes via:

# /etc/init.d/oracleasm scandisks
Scanning system for ASM disks:                             [  OK  ]

# /etc/init.d/oracleasm listdisks
VOL1
VOL2

Create the ASM Instances

Create directories for the clustered ASM instance on shared storage. In my case, I will put it on the same OCFS2 volumes hosting the voting and cluster registry files for CRS.

# mkdir -p /u02/oracle/crs/asm
# mkdir -p /u02/oracle/crs/asm/bdump
# mkdir -p /u02/oracle/crs/asm/cdump
# mkdir -p /u02/oracle/crs/asm/udump
# chown -R oracle:dba /u02/oracle/crs/asm

Create the initial ASM instance parameter file on the shared storage:

# touch /u02/oracle/crs/asm/init+ASM.ora
# vi /u02/oracle/crs/asm/init+ASM.ora

init+ASM.ora

*.asm_diskgroups='DATA','BACKUPS'
*.asm_diskstring='/dev/oracleasm/disks/*'
*.background_dump_dest='/u02/oracle/crs/asm/bdump'
*.cluster_database=true
*.core_dump_dest='/u02/oracle/crs/asm/cdump'
+ASM2.instance_number=2
+ASM1.instance_number=1
*.instance_type='asm'
*.large_pool_size=20M
*.remote_login_passwordfile='exclusive'
*.user_dump_dest='/u02/oracle/crs/asm/udump'

Link to the parameter file from each host:

# su - oracle
# cd $ORACLE_HOME/dbs
# ln -s /u02/oracle/crs/asm/init+ASM.ora init+ASM1.ora

# su - oracle
# cd $ORACLE_HOME/dbs
# ln -s /u02/oracle/crs/asm/init+ASM.ora init+ASM2.ora

Create the passwordfile on shared storage and link to it from each ASM instance:

# orapwd file=/u02/oracle/crs/asm/orapw+ASM password={password}

Link to the passwordfile on each node:

# cd $ORACLE_HOME/dbs
# ln -s /u02/oracle/crs/asm/orapw+ASM orapw+ASM1

# cd $ORACLE_HOME/dbs
# ln -s /u02/oracle/crs/asm/orapw+ASM orapw+ASM2

Update /etc/oratab on each node:

# echo "+ASM1:/u01/app/oracle/product/10.2:N" >> /etc/oratab

# echo "+ASM2:/u01/app/oracle/product/10.2:N" >> /etc/oratab

Create the ASM Diskgroups:

# export ORACLE_SID=+ASM1
# . oraenv
# sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Sun May 25 16:52:56 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup mount;
ASM instance started

Total System Global Area  100663296 bytes
Fixed Size                  1260312 bytes
Variable Size              74237160 bytes
ASM Cache                  25165824 bytes
ORA-15032: not all alterations performed
ORA-15063: ASM discovered an insufficient number of disks for diskgroup "DATA"
ORA-15063: ASM discovered an insufficient number of disks for diskgroup
"BACKUPS"

SQL> create diskgroup DATA external redundancy disk '/dev/oracleasm/disks/VOL1';

Diskgroup created.

SQL> create diskgroup BACKUPS external redundancy disk '/dev/oracleasm/disks/VOL2';

Diskgroup created.

Now that you have confirmed that the ASM instance is functional, create the spfile from pfile:

SQL> create spfile='/u02/oracle/crs/asm/spfile+ASM.ora' from pfile;

File created.

Update each node to reflect use of the spfile:

# cd $ORACLE_HOME/dbs
# vi init+ASM1.ora

init+ASM1.ora

spfile='/u02/oracle/crs/asm/spfile+ASM.ora'

# cd $ORACLE_HOME/dbs
# vi init+ASM2.ora

init+ASM2.ora

spfile='/u02/oracle/crs/asm/spfile+ASM.ora'

Register the clustered ASM instance with srvctl

# srvctl add asm -n rac1 -i +ASM1 -o $ORACLE_HOME
# srvctl add asm -n rac2 -i +ASM2 -o $ORACLE_HOME
# srvctl enable asm -n rac1 -i +ASM1
# srvctl enable asm -n rac2 -i +ASM2

Start and verify ASM instances:

# srvctl start asm -n rac1
# srvctl start asm -n rac2
# srvctl status asm -n rac2
ASM instance +ASM2 is running on node rac2.

# srvctl status asm -n rac1
ASM instance +ASM1 is running on node rac1.

Prepare to migrate the database in question to use ASM as storage

Query the names of the database files:

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u02/oracle/ora1/em/system01.dbf
/u02/oracle/ora1/em/undotbs01.dbf
/u02/oracle/ora1/em/sysaux01.dbf
/u02/oracle/ora1/em/users01.dbf
/u02/oracle/ora1/em/example01.dbf
/u02/oracle/ora1/em/undotbs02.dbf
/u02/oracle/ora2/em/mgmt.dbf
/u02/oracle/ora1/em/mgmt_ecm_depot1.dbf

8 rows selected.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u02/oracle/ora1/em/control01.ctl
/u02/oracle/ora2/em/control02.ctl

SQL> select * from gv$logfile;

   INST_ID     GROUP# STATUS  TYPE    MEMBER                              IS_
---------- ---------- ------- ------- ----------------------------------- ---
         1          2         ONLINE  /u02/oracle/ora1/em/redo_1_2a.log   NO
         1          2         ONLINE  /u02/oracle/ora2/em/redo_1_2b.log   NO
         1          1         ONLINE  /u02/oracle/ora1/em/redo_1_1a.log   NO
         1          1         ONLINE  /u02/oracle/ora2/em/redo_1_1b.log   NO
         1          3         ONLINE  /u02/oracle/ora1/em/redo_2_3a.log   NO
         1          3         ONLINE  /u02/oracle/ora2/em/redo_2_3b.log   NO
         1          4         ONLINE  /u02/oracle/ora1/em/redo_2_4a.log   NO
         1          4         ONLINE  /u02/oracle/ora2/em/redo_2_4b.log   NO
         2          2         ONLINE  /u02/oracle/ora1/em/redo_1_2a.log   NO
         2          2         ONLINE  /u02/oracle/ora2/em/redo_1_2b.log   NO
         2          1         ONLINE  /u02/oracle/ora1/em/redo_1_1a.log   NO

   INST_ID     GROUP# STATUS  TYPE    MEMBER                              IS_
---------- ---------- ------- ------- ----------------------------------- ---
         2          1         ONLINE  /u02/oracle/ora2/em/redo_1_1b.log   NO
         2          3         ONLINE  /u02/oracle/ora1/em/redo_2_3a.log   NO
         2          3         ONLINE  /u02/oracle/ora2/em/redo_2_3b.log   NO
         2          4         ONLINE  /u02/oracle/ora1/em/redo_2_4a.log   NO
         2          4         ONLINE  /u02/oracle/ora2/em/redo_2_4b.log   NO

16 rows selected.

Migrate the Database to ASM

Update the control_files parameter in preparation for migration:

$ srvctl stop database -d em
[oracle@rac1 dbs]$ export ORACLE_SID=em1
[oracle@rac1 dbs]$ . oraenv
[oracle@rac1 dbs]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Sun May 25 17:38:38 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  268435456 bytes
Fixed Size                  1261300 bytes
Variable Size             243269900 bytes
Database Buffers           16777216 bytes
Redo Buffers                7127040 bytes

SQL> alter system set control_files='+DATA/control.ctl' scope=spfile sid='*';

System altered.

SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.

Migrate the controlfile:

# rman target=/

Recovery Manager: Release 10.2.0.3.0 - Production on Sun May 25 17:40:41 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area     268435456 bytes

Fixed Size                     1261300 bytes
Variable Size                247464204 bytes
Database Buffers              12582912 bytes
Redo Buffers                   7127040 bytes

RMAN> restore controlfile from '/u02/oracle/ora1/em/control01.ctl';

Starting restore at 25-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=145 instance=em1 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=+DATA/control.ctl
Finished restore at 25-MAY-08

Migrate the Datafiles:

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> backup as copy database format '+DATA';

Starting backup at 25-MAY-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=145 instance=em1 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00007 name=/u02/oracle/ora2/em/mgmt.dbf
output filename=+DATA/em/datafile/mgmt_tablespace.257.655667175 tag=TAG20080525T174614 recid=10 stamp=655668164
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:16:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/u02/oracle/ora1/em/system01.dbf
output filename=+DATA/em/datafile/system.258.655668171 tag=TAG20080525T174614 recid=11 stamp=655668229
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u02/oracle/ora1/em/sysaux01.dbf
output filename=+DATA/em/datafile/sysaux.259.655668235 tag=TAG20080525T174614 recid=12 stamp=655668262
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/u02/oracle/ora1/em/undotbs02.dbf
output filename=+DATA/em/datafile/undotbs2.260.655668271 tag=TAG20080525T174614 recid=13 stamp=655668293
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u02/oracle/ora1/em/undotbs01.dbf
output filename=+DATA/em/datafile/undotbs1.261.655668297 tag=TAG20080525T174614 recid=14 stamp=655668309
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u02/oracle/ora1/em/example01.dbf
output filename=+DATA/em/datafile/example.262.655668311 tag=TAG20080525T174614 recid=15 stamp=655668322
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00008 name=/u02/oracle/ora1/em/mgmt_ecm_depot1.dbf
output filename=+DATA/em/datafile/mgmt_ecm_depot_ts.263.655668327 tag=TAG20080525T174614 recid=16 stamp=655668337
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/u02/oracle/ora1/em/users01.dbf
output filename=+DATA/em/datafile/users.264.655668341 tag=TAG20080525T174614 recid=17 stamp=655668352
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=+DATA/em/controlfile/backup.265.655668357 tag=TAG20080525T174614 recid=18 stamp=655668358
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 25-MAY-08
channel ORA_DISK_1: finished piece 1 at 25-MAY-08
piece handle=+DATA/em/backupset/2008_05_25/nnsnf0_tag20080525t174614_0.266.655668361 tag=TAG20080525T174614 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 25-MAY-08

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/em/datafile/system.258.655668171"
datafile 2 switched to datafile copy "+DATA/em/datafile/undotbs1.261.655668297"
datafile 3 switched to datafile copy "+DATA/em/datafile/sysaux.259.655668235"
datafile 4 switched to datafile copy "+DATA/em/datafile/users.264.655668341"
datafile 5 switched to datafile copy "+DATA/em/datafile/example.262.655668311"
datafile 6 switched to datafile copy "+DATA/em/datafile/undotbs2.260.655668271"
datafile 7 switched to datafile copy "+DATA/em/datafile/mgmt_tablespace.257.655667175"
datafile 8 switched to datafile copy "+DATA/em/datafile/mgmt_ecm_depot_ts.263.655668327"

Open the Database by exiting RMAN and then issuing the following via SQL*Plus:

# SQL> alter database open;

Database altered.

Startup the other instances:

# srvctl start instance -d em -i em2

Create a new Temporary Tablespace on the ASM Diskgroup:

SQL> CREATE TEMPORARY TABLESPACE TEMPTBS TEMPFILE '+DATA' SIZE 200M;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPTBS;

Database altered.

SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

Create new Online Redo Log Groups:

SQL> alter system archive log all;
alter system archive log all
*
ERROR at line 1:
ORA-00271: there are no logs that need archiving

SQL> alter system archive log stop;

System altered.

Create ASM Directories

# export ORACLE_SID=+ASM1
# . oraenv
# sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Sun May 25 18:32:31 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> alter diskgroup DATA add directory '+DATA/em/log1';

Diskgroup altered.

SQL> alter diskgroup DATA add directory '+DATA/em/log2';

Diskgroup altered.

Create New Online Redo Log Groups for each public thread before dropping the old ones

SQL> alter database add logfile thread 1 group 5
  2  ('+DATA/em/log1/log_1_5a.log','+DATA/em/log1/log_1_5b.log') size 10M;

Database altered.

SQL> alter database add logfile thread 1 group 6
  2  ('+DATA/em/log1/log_1_6a.log','+DATA/em/log1/log_1_6b.log') size 10M;

Database altered.

SQL> alter database add logfile thread 1 group 7
  2  ('+DATA/em/log1/log_1_7a.log','+DATA/em/log1/log_1_7b.log') size 10M;

Database altered.

SQL> alter database add logfile thread 2 group 8
  2   ('+DATA/em/log2/log_2_8a.log','+DATA/em/log2/log_2_8b.log') size 10M;

Database altered.

SQL> alter database add logfile thread 2 group 9
  2  ('+DATA/em/log2/log_2_9a.log','+DATA/em/log2/log_2_9b.log') size 10M;

Database altered.

SQL> alter database add logfile thread 2 group 10
  2  ('+DATA/em/log2/log_2_10a.log','+DATA/em/log2/log_2_10b.log') size 10M;

Database altered.

Drop the Old Online Redo Log Groups using the traditional process, for example:

SQL>  alter system switch logfile;


System altered.

SQL> select group#, thread#, status from v$log;

    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 INACTIVE
         5          1 ACTIVE
         6          1 ACTIVE
         7          1 CURRENT
         8          2 CURRENT
         9          2 ACTIVE
        10          2 ACTIVE

7 rows selected.

SQL> alter database drop logfile group 1;

Database altered.

Optionally, drop the old datafile images:

# rman target=/

Recovery Manager: Release 10.2.0.3.0 - Production on Sun May 25 19:00:45 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: EM (DBID=1167076574)

RMAN> delete copy of database;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=124 instance=em1 devtype=DISK

List of Datafile Copies
Key     File S Completion Time Ckp SCN    Ckp Time        Name
------- ---- - --------------- ---------- --------------- ----
19      1    A 25-MAY-08       2754400    25-MAY-08       /u02/oracle/ora1/em/system01.dbf
20      2    A 25-MAY-08       2754400    25-MAY-08       /u02/oracle/ora1/em/undotbs01.dbf
21      3    A 25-MAY-08       2754400    25-MAY-08       /u02/oracle/ora1/em/sysaux01.dbf
22      4    A 25-MAY-08       2754400    25-MAY-08       /u02/oracle/ora1/em/users01.dbf
23      5    A 25-MAY-08       2754400    25-MAY-08       /u02/oracle/ora1/em/example01.dbf
24      6    A 25-MAY-08       2754400    25-MAY-08       /u02/oracle/ora1/em/undotbs02.dbf
25      7    A 25-MAY-08       2754400    25-MAY-08       /u02/oracle/ora2/em/mgmt.dbf
26      8    A 25-MAY-08       2754400    25-MAY-08       /u02/oracle/ora1/em/mgmt_ecm_depot1.dbf

Do you really want to delete the above objects (enter YES or NO)? YES
deleted datafile copy
datafile copy filename=/u02/oracle/ora1/em/system01.dbf recid=19 stamp=655668498
deleted datafile copy
datafile copy filename=/u02/oracle/ora1/em/undotbs01.dbf recid=20 stamp=655668498
deleted datafile copy
datafile copy filename=/u02/oracle/ora1/em/sysaux01.dbf recid=21 stamp=655668498
deleted datafile copy
datafile copy filename=/u02/oracle/ora1/em/users01.dbf recid=22 stamp=655668498
deleted datafile copy
datafile copy filename=/u02/oracle/ora1/em/example01.dbf recid=23 stamp=655668498
deleted datafile copy
datafile copy filename=/u02/oracle/ora1/em/undotbs02.dbf recid=24 stamp=655668498
deleted datafile copy
datafile copy filename=/u02/oracle/ora2/em/mgmt.dbf recid=25 stamp=655668498
deleted datafile copy
datafile copy filename=/u02/oracle/ora1/em/mgmt_ecm_depot1.dbf recid=26 stamp=655668498
Deleted 8 objects

Migrate the Archive Log Destinations to ASM:

SQL> alter system set log_archive_dest_1='LOCATION=+BACKUPS' scope=BOTH sid='*';

System altered.

SQL> alter system archive log start;

System altered.

SQL> alter system archive log current;

System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +BACKUPS
Oldest online log sequence     342
Next log sequence to archive   344
Current log sequence           344

At this point, you should be able to query an ASM instance and find archivelogs in the BACKUPS diskgroup:

SQL> select count(*) from v$asm_file where type='ARCHIVELOG' and group_number = (select group_number from v$asm_diskgroup where name='BACKUPS');

  COUNT(*)
----------
         4

How to Backup and Restore a RAC Database

2008-05-24T15:33:00.001-07:00

This example illustrates how to backup and recover a 2-node, 10gR2 RAC cluster on Linux. Backup and recovery operations for RAC databases are similar to that of single instance databases, however there are some key differences - the goal of this post is to highlight the steps required for recoverying RAC databases.

Backup the Database

Use the method you prefer. I used a NOCATALOG, disk-based backup for this example, using the scripts found here.

Simulate the Failure

Shutdown the database and simulate a failure by dropping and/or corrupting a datafile.

# export ORACLE_SID=em1
# . oraenv
# srvctl stop database -d em
# srvctl status database -d em
Instance em1 is not running on node rac1
Instance em2 is not running on node rac2

# cd /u02/oracle/ora1/em
# rm sysaux01.dbf

Verify the Failure

# srvctl start instance -d em -i em1
PRKP-1001 : Error starting instance em1 on node rac1
CRS-0215: Could not start resource 'ora.em.em1.inst'.

You will find something similar to the following in the instance's alert log:

Sat May 24 16:53:47 2008
Errors in file /u02/oracle/logs/em/bdump/em1_dbw0_16947.trc:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u02/oracle/ora1/em/sysaux01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-1157 signalled during: ALTER DATABASE OPEN...
Sat May 24 16:53:49 2008
Shutting down instance (abort)

Restore the Database

First, take the database out of cluster mode via:

# sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Sat May 24 17:02:17 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  268435456 bytes
Fixed Size                  1261300 bytes
Variable Size             251658508 bytes
Database Buffers            8388608 bytes
Redo Buffers                7127040 bytes

SQL> alter system set cluster_database=false scope=spfile sid='*';

System altered.

SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.

Then restore the database via RMAN:

# rman target=/

Recovery Manager: Release 10.2.0.3.0 - Production on Sat May 24 17:04:10 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area     268435456 bytes

Fixed Size                     1261300 bytes
Variable Size                251658508 bytes
Database Buffers               8388608 bytes
Redo Buffers                   7127040 bytes

RMAN> restore database;

Starting restore at 24-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u02/oracle/ora1/em/system01.dbf
restoring datafile 00002 to /u02/oracle/ora1/em/undotbs01.dbf
restoring datafile 00003 to /u02/oracle/ora1/em/sysaux01.dbf
restoring datafile 00004 to /u02/oracle/ora1/em/users01.dbf
restoring datafile 00005 to /u02/oracle/ora1/em/example01.dbf
restoring datafile 00006 to /u02/oracle/ora1/em/undotbs02.dbf
restoring datafile 00007 to /u02/oracle/ora2/em/mgmt.dbf
restoring datafile 00008 to /u02/oracle/ora1/em/mgmt_ecm_depot1.dbf
channel ORA_DISK_1: reading from backup piece /u02/oracle/ora3/em/backups/0ijh6j4t_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/oracle/ora3/em/backups/0ijh6j4t_1_1 tag=FHB_EM1200805241630
channel ORA_DISK_1: restore complete, elapsed time: 00:06:36
Finished restore at 24-MAY-08

Since my database is so small, I will simply restore the entire thing, however, since you know which datafile is missing, you could simply restore it and then recover the database as necessary.

Recover the Database

RMAN> recover database;

Starting recover at 24-MAY-08
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 24-MAY-08

RMAN> alter database open;

database opened
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error

Afterwards, place the database back into cluster mode and startup both instances:

# sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Sat May 24 17:16:36 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> alter system set cluster_database=true scope=spfile sid='*';

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

# srvctl start database -d em
[oracle@rac1 bdump]$ srvctl status database -d em
Instance em1 is running on node rac1
Instance em2 is running on node rac2

Installing DB2 V9.5 on Linux

2008-05-22T15:22:00.000-07:00

Get the Software Here:

DB2 9 Trial Download

Reference the Following Documentation:

Information Center DB2 9.5 Documentation

Unpack the Software and Satisfy any Prerequisites

export DISPLAY=192.168.1.3:0.0

# gunzip db2_v95_linuxia32_server.tar.gz
# tar -xvf db2_v95_linuxia32_server.tar

Note that these are the recommended kernel values

kernel.shmmax=268435456 for 32-bit; 1073741824 for 64-bit
kernel.msgmni=1024
fs.file-max=8192
kernel.sem=”250 32000 32 1024”

Install Database Engine

# ./server/db2_install -f NOTSAMP

Default directory for installation of products - /opt/ibm/db2/V9.5

***********************************************************
Do you want to choose a different directory to install [yes/no] ?
no


Specify one of the following keywords to install DB2 products.

  ESE
  CONSV
  WSE
  EXP
  PE
  CLIENT
  RTCL

Enter "help" to redisplay product names.

Enter "quit" to exit.

***********************************************************
ESE
DB2 installation is being initialized.

Total number of tasks to be performed: 41
Total estimated time for all tasks to be performed: 1461

The execution completed successfully.

For more information see the DB2 installation log at
"/tmp/db2_install.log.6602".

Perform Post-Installation Tasks

Add O/S User and Groups

# groupadd -g 999 db2iadm1
# groupadd -g 998 db2fadm1
# groupadd -g 997 dasadm1
# useradd -u 1004 -g db2iadm1 -m -d /home/db2inst1 db2inst1
# useradd -u 1003 -g db2fadm1 -m -d /home/db2fenc1 db2fenc1
# useradd -u 1002 -g dasadm1 -m -d /home/dasusr1 dasusr1

Set the Passwords

# passwd db2inst1
# passwd db2fenc1
# passwd dasusr1

Create the DAS Instance

# cd /opt/ibm/db2/V9.5/instance/
# ./dascrt -u dasusr1
SQL4406W  The DB2 Administration Server was started successfully.
DBI1070I  Program dascrt completed successfully.

Create a DB2 Instance

# cd /opt/ibm/db2/V9.5/instance
# ./db2icrt -a server -u db2fenc1 db2inst1
DBI1070I  Program db2icrt completed successfully.

Create Symbolic Links to DB2 Binaries

# cd /opt/ibm/db2/V9.5/cfg
# ./db2ln
# ls -lart /usr/lib/*db2* | wc -l
6
# ls -lart /usr/include/*db2* | wc -l
8

Setup Communications

Verify Service File Entry

# grep db2c_db2inst1 /etc/services
db2c_db2inst1   50000/tcp

Update the Service Name

# su - db2inst1
# . $HOME/sqllib/db2profile
# db2
db2 => update dbm cfg using svcename db2c_db2inst1;
DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.

# db2stop
05/23/2008 14:03:33     0   0   SQL1032N  No start database manager command was issued.
SQL1032N  No start database manager command was issued.  SQLSTATE=57019

# db2start
05/23/2008 14:03:46     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.
# db2 get database manager configuration | grep "TCP/IP Service name"
 TCP/IP Service name                          (SVCENAME) = db2c_db2inst1;

# db2set DB2COMM=tcpip
# db2stop
05/23/2008 14:50:03     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.
# db2start
05/23/2008 14:50:14     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.

Enable SSL Support

# export DISPLAY=192.168.1.3:0.0
# export JAVA_HOME=$HOME/sqllib/java/jdk32
# export PATH=$PATH:/usr/local/ibm/gsk7/bin
# gsk7ikm

Create the Key Store Database as well as a Self-signed Certificate

Create the SSLconfig.ini File and Populate with Correct Values

# touch $HOME/sqllib/cfg/SSLconfig.ini
# vi $HOME/sqllib/cfg/SSLconfig.ini

SSLconfig.ini

DB2_SSL_KEYSTORE_FILE=/home/db2inst1/GSKit/Keystore/key.kdb
DB2_SSL_KEYSTORE_PW=password
DB2_SSL_LISTENER=40000

# db2set DB2COMM=SSL,TCPIP
# db2stop
05/23/2008 15:50:13     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.
# db2start
05/23/2008 15:50:20     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.

Create the Tools Catalog for Task Center/Control Center

# db2 create database TOOLSDB AUTOMATIC STORAGE YES ON /u01/app/ibm/data
DB20000I  The CREATE DATABASE command completed successfully.
# db2 create tools catalog toolscat use existing database toolsdb force
DB20000I  The CREATE TOOLS CATALOG command completed successfully.

Update the DAS Configuration, Verifying Values

# db2 update admin configuration using smtp_server localhost
DB20000I  The UPDATE ADMIN CONFIGURATION command completed successfully.
# db2 update admin configuration using dasadm_group dasadm1
DB20000I  The UPDATE ADMIN CONFIGURATION command completed successfully.
# db2 get admin configuration

            Admin Server Configuration

 Authentication Type DAS                (AUTHENTICATION) = SERVER_ENCRYPT

 DAS Administration Authority Group Name  (DASADM_GROUP) = dasadm1

 DAS Discovery Mode                           (DISCOVER) = SEARCH
 Name of the DB2 Server System               (DB2SYSTEM) = RAC1

 Java Development Kit Installation Path DAS   (JDK_PATH) = AUTOMATIC (/home/db2inst1/sqllib/java/jdk32)
 Java Development Kit Installation Path DAS   (JDK_64_PATH) = AUTOMATIC (/home/db2inst1/sqllib/java/jdk32)

 DAS Code Page                            (DAS_CODEPAGE) = 0
 DAS Territory                           (DAS_TERRITORY) = 0

 Location of Contact List                 (CONTACT_HOST) =
 Execute Expired Tasks                   (EXEC_EXP_TASK) = NO
 Scheduler Mode                           (SCHED_ENABLE) = ON
 SMTP Server                               (SMTP_SERVER) = localhost
 Tools Catalog Database                    (TOOLSCAT_DB) = TOOLSDB
 Tools Catalog Database Instance         (TOOLSCAT_INST) = db2inst1
 Tools Catalog Database Schema         (TOOLSCAT_SCHEMA) = TOOLSCAT
 Scheduler User ID                                       =

 Diagnostic error capture level              (DIAGLEVEL) = 2

SQL Server Statistics Maintenance Job

2008-05-20T13:36:00.001-07:00

I use the following job to augment SQL Server's auto statistics create/update functionality:

create_update_statistics.sql

sp_MSforeachdb @command1='
USE ?;
IF NOT ("?" IN ("model","msdb","master","tempdb")) 
BEGIN
   EXEC sp_createstats;
   DECLARE @tbls TABLE (rowId INT IDENTITY(1,1), tblschema NVARCHAR(128), tblname NVARCHAR(128));
   DECLARE @stmt NVARCHAR(2000), @rowId INT, @maxRowId INT, @tblschema NVARCHAR(128), @tblname NVARCHAR(128);
   INSERT INTO @tbls (tblschema, tblname) 
   SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_type="BASE TABLE";
   SELECT @rowId = MIN(rowId), @maxRowId = MAX(rowId) FROM @tbls;
   WHILE  @rowId <= @maxRowId 
      BEGIN
         SELECT @tblschema = tblschema, @tblname = tblname FROM @tbls WHERE rowId = @rowId;
         SET @stmt = "UPDATE STATISTICS " + "[" + @tblschema + "].[" + @tblname + "];"; 
         PRINT "?: " + @stmt;
         EXECUTE (@stmt);
         SET @rowId = @rowId + 1;
      END
END
';

You can adjust this to fit your needs and implement as part of your Maintenance Plans and/or run from a batch file, such as:

create_update_statistics.bat

sqlcmd -S PRIVATE-E3A52F5\SS1 -E -i create_update_statistics.sql -o create_update_statistics.out
notepad.exe create_update_statistics.out

Backup and Recovery of SQL Server System Databases

2008-05-19T13:12:00.000-07:00

This posting covers how to backup and restore SQL Servers' system databases - master, msdb, model, etc.

In this example, I backup SQL Server's databases and then replicate a loss/corruption - and subsequent restoration - of the master database's underlying datafiles.

In SQL Server 2000, the rebuildm.exe executable was available to help you rebuild the master database so that you could start the SQL Server and then, in turn, restore the master (and related system databases) database from an available backup.

In SQL Server 2005, the rebuildm.exe executable is not available, rather the approved procedure is to call the setup.exe of the installation binaries, passing the appropriate parameters/switches, etc. in order to rebuild the master database. Afterwards, you could startup the database engine and then restore the system databases from backups. Unfortunately, this process is riddled with bugs, particularly post application of SP2.

Given the aforementioned, my preferred approach is to retain offline copies of the system databases' underlying datafiles. In the event of a loss/corruption of the master database, I simply restore these old datafile copies to the default location. This allows me to startup the database engine and restore the system databases from backups, avoiding the above flawed mechanisms. Such datafile backups should be taken at key times, such as post-upgrade, etc.

All the scripts used in this example are available in the following .zip file:

bckp_restore_sys_dbs_sql2005.zip

My example uses SQL Server 2005 - my instance is named PRIVATE-E3A52F5\SS1

Step 1 - backup_master_files.bat

I first take an offline file backup of the master database's datafiles:

net stop "SQL Server Agent (SS1)"
net stop "SQL Server (SS1)"
copy /Y "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master.mdf" "C:\backups\master.mdf"
copy /Y "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mastlog.ldf" "C:\backups\mastlog.ldf"
net start "SQL Server (SS1)"
net start "SQL Server Agent (SS1)"

Step 2 - backup_databases.bat

Next, I backup all the SQL Server's databases via a batch file:

sqlcmd -S PRIVATE-E3A52F5\SS1 -E -i "C:\Documents and Settings\James\Desktop\backup_all_databases.sql" -o "C:\Documents and Settings\James\Desktop\backup_all_databases.out"

This batch file calls the following SQL script:

backup_all_databases.sql

--Use the foreach stored procedure  
--To backup each database to disk - except - tempdb
--Create necessary sub-directories and validate each backup
EXEC master.sys.sp_MSforeachdb @command1='
BEGIN
DECLARE @bkpfile NVARCHAR(1000), @bkploc NVARCHAR(500), @bkpname NVARCHAR(500);
DECLARE @backupsetid INT;
SET @bkploc  = ''C:\backups\?\'';
PRINT @bkploc;
SET @bkpname = @bkploc +  ''?_backup_''+ cast(datepart(yyyy, getdate()) as nvarchar) + cast(datepart(mm,getdate()) as nvarchar) +  cast(datepart(dd, getdate()) as nvarchar) +  cast(datepart(hh, getdate()) as nvarchar) + cast(datepart(mi, getdate()) as nvarchar);
PRINT @bkpname;
SET @bkpfile = @bkpname + ''.BAK'';
  IF NOT (''?'' = ''tempdb'')
  BEGIN
     BEGIN 
        EXECUTE master.dbo.xp_create_subdir @bkploc;
     END;
     
     BEGIN
        BACKUP DATABASE ? TO DISK = @bkpfile WITH NOFORMAT, NOINIT,  NAME = @bkpname, SKIP, REWIND, NOUNLOAD;
     END;
   
     BEGIN
       SELECT @backupsetid = position FROM msdb.dbo.backupset WHERE database_name=''?'' AND backup_set_id=(SELECT MAX(backup_set_id) FROM msdb.dbo.backupset WHERE database_name=''?'' );
       RESTORE VERIFYONLY FROM  DISK = @bkpfile WITH  FILE = @backupsetid,  NOUNLOAD,  NOREWIND
     END;

  END;
END';
GO

Yielding the following output:

C:\backups\master\
C:\backups\master\master_backup_20085181746
Processed 352 pages for database 'master', file 'master' on file 1.
Processed 2 pages for database 'master', file 'mastlog' on file 1.
BACKUP DATABASE successfully processed 354 pages in 0.517 seconds (5.599 MB/sec).
The backup set on file 1 is valid.
C:\backups\tempdb\
C:\backups\tempdb\tempdb_backup_20085181746
C:\backups\model\
C:\backups\model\model_backup_20085181746
Processed 152 pages for database 'model', file 'modeldev' on file 1.
Processed 2 pages for database 'model', file 'modellog' on file 1.
BACKUP DATABASE successfully processed 154 pages in 0.285 seconds (4.426 MB/sec).
The backup set on file 1 is valid.
C:\backups\msdb\
C:\backups\msdb\msdb_backup_20085181746
Processed 600 pages for database 'msdb', file 'MSDBData' on file 1.
Processed 6 pages for database 'msdb', file 'MSDBLog' on file 1.
BACKUP DATABASE successfully processed 606 pages in 0.763 seconds (6.500 MB/sec).
The backup set on file 1 is valid.
C:\backups\ReportServer$SS1\
C:\backups\ReportServer$SS1\ReportServer$SS1_backup_20085181746
Processed 288 pages for database 'ReportServer$SS1', file 'ReportServer$SS1' on file 1.
Processed 1 pages for database 'ReportServer$SS1', file 'ReportServer$SS1_log' on file 1.
BACKUP DATABASE successfully processed 289 pages in 0.496 seconds (4.773 MB/sec).
The backup set on file 1 is valid.
C:\backups\ReportServer$SS1TempDB\
C:\backups\ReportServer$SS1TempDB\ReportServer$SS1TempDB_backup_20085181746
Processed 176 pages for database 'ReportServer$SS1TempDB', file 'ReportServer$SS1TempDB' on file 1.
Processed 1 pages for database 'ReportServer$SS1TempDB', file 'ReportServer$SS1TempDB_log' on file 1.
BACKUP DATABASE successfully processed 177 pages in 0.357 seconds (4.061 MB/sec).
The backup set on file 1 is valid.

Step 3 - destroy_master.bat

When ready to perform the test, I call this batch file to simulate a destruction/corruption of the master database.

net stop "SQL Server Agent (SS1)"
net stop "SQL Server (SS1)"
del "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master.mdf"
del "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mastlog.ldf"

At this point, attempts to start the database engine, would yield something similar to the following with the ERRORLOG:

2008-05-18 13:09:26.73 Server      Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) 
 Oct 14 2005 00:33:37 
 Copyright (c) 1988-2005 Microsoft Corporation
 Standard Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

2008-05-18 13:09:26.73 Server      (c) 2005 Microsoft Corporation.
2008-05-18 13:09:26.73 Server      All rights reserved.
2008-05-18 13:09:26.73 Server      Server process ID is 3360.
2008-05-18 13:09:26.73 Server      Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2008-05-18 13:09:26.74 Server      This instance of SQL Server last reported using a process ID of 456 at 5/18/2008 1:08:35 PM (local) 5/18/2008 7:08:35 PM (UTC). This is an informational message only; no user action is required.
2008-05-18 13:09:26.74 Server      Registry startup parameters:
2008-05-18 13:09:26.74 Server        -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2008-05-18 13:09:26.74 Server        -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2008-05-18 13:09:26.74 Server        -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2008-05-18 13:09:26.74 Server      Error: 17113, Severity: 16, State: 1.
2008-05-18 13:09:26.74 Server      Error 2(The system cannot find the file specified.) occurred while opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf' to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.

Step 4 - recover_master.bat

Next, a batch file is called to perform the recovery:

net stop  "SQL Server Agent (SS1)"
net stop  "SQL Server (SS1)"
net stop  "SQL Server Analysis Services (SS1)"
net stop  "SQL Server FullText Search (SS1)"
net stop  "SQL Server Integration Services"
net stop  "SQL Server Reporting Services (SS1)"
copy /Y "C:\backups\master.mdf"  "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master.mdf" 
copy /Y "C:\backups\mastlog.ldf" "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mastlog.ldf"
net start "SQL Server (SS1)" /m
sqlcmd -S PRIVATE-E3A52F5\SS1 -E -i "restore_master.sql" -o "restore_master.out"
net start "SQL Server (SS1)"
sqlcmd -S PRIVATE-E3A52F5\SS1 -E -i "restore_databases.sql" -o "restore_databases.out"
net start "SQL Server Agent (SS1)"
net start  "SQL Server Analysis Services (SS1)"
net start  "SQL Server FullText Search (SS1)"
net start "SQL Server Integration Services"
net start  "SQL Server Reporting Services (SS1)"

The batch file first restores the offline backups of the master database's datafiles and then performs a recovery of that database via:

restore_master.sql

RESTORE DATABASE [master] from DISK = 'C:\backups\master\master_backup_20085181746.BAK' WITH REPLACE ;
GO

Yielding the following output:

Processed 352 pages for database 'master', file 'master' on file 1.
Processed 2 pages for database 'master', file 'mastlog' on file 1.
The master database has been successfully restored. Shutting down SQL Server.
SQL Server is terminating this process.

Next, the batch file restores the other system databases via:

restore_databases.sql

RESTORE DATABASE [msdb] from DISK = 'C:\backups\msdb\msdb_backup_20085181746.BAK' WITH REPLACE  ;
GO
RESTORE DATABASE [model] from DISK = 'C:\backups\model\model_backup_20085181746.BAK' WITH REPLACE ;
GO

Yielding the following output:

Processed 600 pages for database 'msdb', file 'MSDBData' on file 1.
Processed 6 pages for database 'msdb', file 'MSDBLog' on file 1.
RESTORE DATABASE successfully processed 606 pages in 0.531 seconds (9.341 MB/sec).
Processed 152 pages for database 'model', file 'modeldev' on file 1.
Processed 2 pages for database 'model', file 'modellog' on file 1.
RESTORE DATABASE successfully processed 154 pages in 0.127 seconds (9.933 MB/sec).

At this point, your SQL Server Instance should be ready for use.

Basic Oracle Maintenance Jobs

2008-05-17T13:08:00.000-07:00

There are many processes that need to be in place in order to maintain Oracle databases' environments.

There are, also, many 3rd-party tools that are available to assist the DBA as well as many differing methods in which to approach performing these tasks; however, traditionally, shell scripts called from an O/S scheduler - such as cron - have been used to perform - at a bare minimum - the following tasks:

Rotate Listener Log

Rotate Alert Log

Romove Old Files - Trace, etc.

Backup Database and Archive Logs, including Deleting Old Logs and Crosschecking Recovery Catalogs/Controlfiles, etc.

Gather Object - Table, Index, etc. - Statistics

This example includes a tar file which contains all of the scripts necessary to perform the above 5 tasks on the Linux platform for any Oracle 9i/10g database - these scripts can also be modified to support 11g:

oracron.tar

Simply untar the files to the desired location, edit as appropriate and schedule via your preferred mechanism; below are the entries from my oracle user's crontab file:

49 18 * * * sh -c '. ~/.bash_profile ; ~/scripts/rotate_alert.bsh ldg' >> ~/logs/rotate_alert.log 2>&1
50 18 * * * sh -c '. ~/.bash_profile ; ~/scripts/remove_old_files.bsh ldg 2' >> ~/logs/remove_old_files.log 2>&1
13 20 * * * sh -c '. ~/.bash_profile ; ~/scripts/rman_full_nc.bsh ldg' >> ~/logs/rman_full_nc.log 2>&1
12 21 * * * sh -c '. ~/.bash_profile ; ~/scripts/analyze_db.bsh ldg' >> ~/logs/analyze_db.log 2>&1
23 22 * * * sh -c '. ~/.bash_profile ; ~/scripts/rotate_listener.bsh ldg ldg' >> ~/logs/rotate_listener.log 2>&1

Make sure that oraenv is in the oracle user's path and that each database has a /etc/oratab entry. You will want to adjust the crontab entries above to use the $ORACLE_SID of your choosing. Additionally, my database statistics gathering job requires that you first compile the DBA_STATS_MAINT_PKG in the SYS schema. You will find the ddl for this package within the tar file.

analyze_db.bsh

   #!/bin/bash
  
   # Wrapper Script that calls PL/SQL function to analyze your
   # database to suite your particular needs 

   function quit {
     echo "${0} ORACLE_SID";
     exit;
   } 

   # Make Sure Oracle instance name is passed
   if [ "" == "${1}" ]; then 
    quit;
   fi; 

   # Set the Environment
   export ORACLE_SID=${1};
   export ORAENV_ASK=NO;
   . oraenv;
  
   date ;
   sqlplus -s /nolog <<INP
   connect / as sysdba ;
   exec dba_stats_maint_pkg.disable_auto_job('GATHER_STATS_JOB'); 
   exec dba_stats_maint_pkg.gather_stats;
   exec dba_stats_maint_pkg.gather_data_dict_stats;
INP
   date ;

remove_old_files.bsh

   #!/bin/bash
   
   # Works for Oracle databases pre-11g 
   # 11g introduces universal diagnostic dest
  
   function quit {
     echo "${0} ORACLE_SID DAYS_OLD";
     exit;
   } 

   # Make Sure Oracle instance name is passed
   if [ "" == "${1}" ]; then 
    quit;
   fi; 

   # Make Sure the Days Old argument is passed
   if [ "" == "${2}" ]; then 
    quit;
   fi; 
   
   # Set the Environment
   export ORACLE_SID=${1};
   export DAYS_OLD=${2};
   export ORAENV_ASK=NO;
   . oraenv;
   
   # Get the location for the bdump
   bdump=`${ORACLE_HOME}/bin/sqlplus -s /nolog <<EOF
    connect / as sysdba ;
    set head off;
    set feedback off;
    set verify off;
    select value from v\\$parameter where name='background_dump_dest'; 
    exit;
    EOF `

   # Get the location for the udump
   udump=`${ORACLE_HOME}/bin/sqlplus -s /nolog <<EOF
    connect / as sysdba ;
    set head off;
    set feedback off;
    set verify off;
    select value from v\\$parameter where name='user_dump_dest'; 
    exit;
    EOF `

    # Make sure the directories are valid and cleanup the files
   if [ -d ${bdump} ] && [ -d ${udump} ] ;  then
     echo "Removing old files at `date +%Y%m%d%H%M` for ${ORACLE_SID}" ;
     echo "Background Dump Before Cleanup `ls -l ${bdump} | wc -l`";
     echo "User Dump Before Cleanup `ls -l ${udump} | wc -l`";
     echo "Listener Log Dump Before Cleanup `ls -l ${ORACLE_HOME}/network/log | wc -l` ";
     find ${bdump} -mtime +${DAYS_OLD} -name "*" -type f -exec rm {} \;
     find ${udump} -mtime +${DAYS_OLD} -name "*" -type f -exec rm {} \;
     find ${ORACLE_HOME}/network/log -mtime +${DAYS_OLD} -name "*" -type f -exec rm {} \;
     echo "Background Dump After Cleanup `ls -l ${bdump} | wc -l`";
     echo "User Dump After Cleanup `ls -l ${udump} | wc -l`";
     echo "Listener Log Dump After Cleanup `ls -l ${ORACLE_HOME}/network/log | wc -l` ";
   fi;

rman_full_nc.bsh

   #!/bin/bash
  
   # Backup/Catalog Crosscheck Delete using RMAN
   # This is for Controlfile backups w/o RMAN Catalog 
 
   function quit {
     echo "${0} ORACLE_SID";
     exit;
   } 

   # Make Sure Oracle instance name is passed
   if [ "" == "${1}" ]; then 
    quit;
   fi; 

   # Set the Environment
   export ORACLE_SID=${1};
   export BACKUP_TAG=FHB_${ORACLE_SID}`date +%Y%m%d%H%M`
   export ARCH_TAG=ARCH_${ORACLE_SID}`date +%Y%m%d%H%M`

   export ORAENV_ASK=NO;
   . oraenv;
  
   date ;
   rman<<EOF
   connect target / ;
   show all ;
   crosscheck backup ;
   crosscheck archivelog all ;
   delete noprompt obsolete ;
   backup full database tag ${BACKUP_TAG} plus archivelog tag ${ARCH_TAG} ; 
   exit ;
EOF
   date ;

rotate_alert.bsh

   #!/bin/bash
   
   # Works for Oracle databases pre-11g 
   # 11g introduces universal diagnostic dest
  
   function quit {
     echo "${0} ORACLE_SID";
     exit;
   } 

   # Make Sure Oracle instance name is passed
   if [ "" == "${1}" ]; then 
    quit;
   fi; 
   
   # Set the Environment
   export ORACLE_SID=${1};
   export ORAENV_ASK=NO;
   . oraenv;
  
   date ; 
   # Get the location for the alert log 
   bdump=`${ORACLE_HOME}/bin/sqlplus -s /nolog <<EOF
    connect / as sysdba ;
    set head off;
    set feedback off;
    set verify off;
    select value from v\\$parameter where name='background_dump_dest'; 
    exit;
    EOF
    `  
    logfile=${bdump}/alert_${ORACLE_SID}.log ;

    # Ensure that the background dump dest exists  
    # If so, rotate the log 
    if [ -d ${bdump} ]; then 
      if [ -f ${logfile} ]; then
        echo "${logfile} rotated at `date +%Y%m%d%H%M`" ;
        mv ${logfile} ${bdump}/alert_${ORACLE_SID}.`date +%Y%m%d%H%M`.log
          if [ $? == 0 ]; then
            touch ${logfile};
          fi;
      else 
        echo "Alert Log ${logfile} does not exist...";
        quit; 
      fi;
    else 
     echo "Directory does not exist...";
     quit;
    fi;  
    date ;

rotate_listener.bsh

   #!/bin/bash
   
   # Works for Oracle databases pre-11g 
   # 11g introduces universal diagnostic dest
  
   function quit {
     echo "${0} ORACLE_SID LSNR";
     exit;
   } 

   # Make Sure Oracle instance name is passed
   if [ "" == "${1}" ]; then 
    quit;
   fi; 

   # Make Sure Listener name is passed
   if [ "" == "${2}" ]; then 
    quit;
   fi; 
   
   # Set the Environment
   export NOW=`/bin/date +%m%d%y%H%M%S`;
   export ORACLE_SID=${1};
   export LSNR=${2};
   export ORAENV_ASK=NO;
   . oraenv;
  
   date ; 
   
   if [ -d ${ORACLE_HOME}/network/log ]; then
     if [ -f ${ORACLE_HOME}/network/log/listener_${LSNR}.log ]; then
      cat ${ORACLE_HOME}/network/log/listener_${LSNR}.log >> ${ORACLE_HOME}/network/log/listener_${LSNR}.log.$NOW ;    
      ${ORACLE_HOME}/bin/lsnrctl << EOF
      set current_listener LISTENER_${LSNR}
      set log_file temp.log
EOF

      rm ${ORACLE_HOME}/network/log/listener_${LSNR}.log ;

      ${ORACLE_HOME}/bin/lsnrctl << EOF
      set current_listener LISTENER_${LSNR}
      set log_file listener_${LSNR}.log
EOF

      cat ${ORACLE_HOME}/network/log/temp.log >> $ORACLE_HOME/network/log/listener_${LSNR}.log.${NOW} ;
      rm ${ORACLE_HOME}/network/log/temp.log ;
    fi ;
   else
      echo "Directory not found...";
      quit ;
   fi ; 
   date ;

Edit the above mentioned in any way you see fit. Although these are written using bash, you could easily adapt these scripts to your preferred shell.

Bugs when installing OWB 10g Release 2

2008-05-14T16:50:00.000-07:00

When installing Oracle Warehouse Builder - specifically 10g Release 2 - there are a couple of Oracle bugs that need to be addressed:

Depending upon your platform, you may encounter:

Error in invoking target 'isqlldr' of makefile 
'$ORACLE_HOME/rdbms/lib/ins_rdbms.mk'.

The following Oracle Forum posting identifies the workaround:

Thread: OWB paris install on 10g rel1 RAC cluster

Depending upon your platform, you may also encounter the following bug while installing the optional Oracle Workflow - version 2.6.4 - component.

After issuing this command:

$ORACLE_HOME/wf/install/wfinstall.csh

You will encounter an error which mentions WorkflowCA: WFTLoad Exception: could not upload the wfstd.wft file.

The above issues is resolved with patch:

4778368 - WFTLOAD EXCEPTION:COULD NOT UPLOAD FILE /RDBMS/10201/WF/RES/US/WFSTD.WFT