DBAdminisaurus: Open Source Monitoring Application for Oracle

Posted at Wednesday, October 15, 2008
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.0Initial 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

<Directory "C:/Program Files/Apache/Apache229/htdocs">

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

</Directory>

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'



Labels: ,

Installing Apache/PHP/11g Instant Client on Windows

Posted at Tuesday, October 14, 2008
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.

Labels: , , ,

Monitoring a Grid Control Installation

Posted at Wednesday, September 24, 2008
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

Labels: ,

Using the KEEP pool to improve performance

Posted at Tuesday, September 23, 2008
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.

Labels:

Cloning a Standalone Database without RMAN

Posted at Sunday, September 21, 2008
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


Labels:

How to Duplicate a Standalone Database: ASM to ASM

Posted at Thursday, September 18, 2008
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.

Labels: ,

Cleaning up after a Failed Clusterware Install

Posted at Thursday, September 18, 2008
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


Labels:

How to Migrate a Standalone Database to ASM

Posted at Thursday, September 18, 2008
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.

Labels: ,

Reorganizing a Table using DBMS_REDEFINITION

Posted at Thursday, July 03, 2008
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';


Labels:

Rebuilding Indexes the Easy Way

Posted at Monday, June 30, 2008
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.


Labels:


Categories

Previous Posts

Archives

Links


James Colestock's Facebook profile

View James Colestock's profile on LinkedIn

Google