Oracle table to html file & Excel

Oracle table to html file 

$sqlplus -S -M "HTML ON TABLE 'BORDER="2"'" username@Ora10g @script.sql>filename.html

ex: - where script.sql contains:

select col1, col2 from table_name;

exit 

Oracle tables to Excel

set echo off pagesize 0 head off feed off veri off
set trimspool on feedback off
spool FILE_NAME.csv
select COL1 || ',' || COL2 || ',' || COL3 || ',' ||........ from TABLE_NAME;
spool off

If table data contain any 'comma' in middle , then follow this below steps: -

set echo off pagesize 0 head off feed off veri off
set trimspool on feedback off
spool FILE_NAME.txt
select COL1 || '~' || COL2 || '~' || COL3 || '~' ||........ from TABLE_NAME;
spool off

open the .txt file in MS-Excel. Selecting the separator option with '~'

Oracle import from 10.2.0.2.0 to 10.1.0.1.0

1) Export from the 10.2.0.2.0 version database with below command:

$expdp system/********* directory=data_pump_dir dumpfile=full_db_exp.dmp logfile=full_db_exp.log full=y version=10.1.0.1.0

2) Import the dumpfile (10.2.0.2.0 version) to the 10.1.0.1.0 database:

$impdp system/******** directory=data_pump_dir dumpfile=full_db_exp.dmp logfile=full_db_imp.log full=y

Silent DBCA

dbca -silent -createDatabase -templateName General_Purpose.dbc
-gdbname ora11g -sid ora11g -responseFile NO_VALUE -characterSet AL32UTF8
-memoryPercentage 30 -emConfiguration LOCAL

Changing Character set of DB - CSSCAN

CSSCAN utility to change the Character set of database:-

Beware: - Before doing this take a full database backup

1. Create data_file_dir and log_file_dir directories with below command:-
SQL> create directory data_file_dir as ‘some_directory_location’;
SQL> create directory log_file_dir as ‘some_directory_location’;

2. And execute the csminst.sql file as a sys user:-
SQL> @$ORACLE_HOME/rdbms/admin/csminst.sql
It will create CSMIG user.

3. Execute the below command in OS level:-
$csscan full=Y fromchar=WE8MSWIN1252 tochar=WE8IS08859P15 log=WE8_TO_WE8
capture=Y array=100000
(later it will prompt for processes, enter some number between 1 to 32)

4. Then shutdown the database and startup database in restrict mode.
SQL>shutdown immediate;
SQL>startup restrict;

5. Execute the below file to change the Character Set:-
SQL>@$ORACLE_HOME/rdbms/admin/csalter.plb;
(it will ask for Y/N, press Y)

6. Then shutdown the database and start it normally:-
SQL>shutdown;
SQL>startup;
Check the character set with the below query:-
SQL> select * from v$nls_parameters;
* if there is any error, we can check with below query:-
first login to the CSMIG user and execute it
SQL> select count(*) from csm$errors;
if the output is '0' then it is fine.

Tablespace Free Sapce

column dummy noprint
column pct_used format 999.9 heading "%|Used"
column name format a19 heading "Tablespace Name"
column Kbytes format 999,999,999 heading "Kbytes"
column used format 999,999,999 heading "Used"
column free format 999,999,999 heading "Free"
column largest format 999,999,999 heading "Largest"
column max_size format 999,999,999 heading "MaxPoss|Kbytes"
column pct_max_used format 999. heading "%|Max|Used"
break on report
compute sum of kbytes on report
compute sum of free on report
compute sum of used on report


select (select decode(extent_management,'LOCAL','*',' ') ||
decode(segment_space_management,'AUTO','a ','m ')
from dba_tablespaces where tablespace_name = b.tablespace_name) ||
nvl(b.tablespace_name,
nvl(a.tablespace_name,'UNKOWN')) name,
kbytes_alloc kbytes,
kbytes_alloc-nvl(kbytes_free,0) used,
nvl(kbytes_free,0) free,
((kbytes_alloc-nvl(kbytes_free,0))/
kbytes_alloc)*100 pct_used,
nvl(largest,0) largest,
nvl(kbytes_max,kbytes_alloc) Max_Size,
decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
from ( select sum(bytes)/1024 Kbytes_free,
max(bytes)/1024 largest,
tablespace_name
from sys.dba_free_space
group by tablespace_name ) a,
( select sum(bytes)/1024 Kbytes_alloc,
sum(maxbytes)/1024 Kbytes_max,
tablespace_name
from sys.dba_data_files
group by tablespace_name
union all
select sum(bytes)/1024 Kbytes_alloc,
sum(maxbytes)/1024 Kbytes_max,
tablespace_name
from sys.dba_temp_files
group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
order by 1
/

Crontab - Quick reference

Setting up cronjobs in Unix and Solaris

Cron is a unix, solaris utility that allows tasks to be automatically run in the background at regular intervals by the cron daemon. These tasks are often termed as cron jobs in unix , solaris.

Crontab (CRON TABle) is a file which contains the schedule of cron entries to be run and at specified times.

1. Crontab Restrictions

You can execute crontab if your name appears in the file /usr/lib/cron/cron.allow. If that file does not exist, you can use

crontab if your name does not appear in the file /usr/lib/cron/cron.deny.

If only cron.deny exists and is empty, all users can use crontab. If neither file exists, only the root user can use crontab. The allow/deny files consist of one user name per line.

2. Crontab Commands

export EDITOR=vi ;to specify a editor to open crontab file.

crontab -e Edit your crontab file, or create one if it doesn't already exist.

crontab -l Display your crontab file.

crontab -r Remove your crontab file.

crontab -v Display the last time you edited your crontab file. (This option is only available on a few systems.)

3. Crontab file

Crontab syntax :-

A crontab file has five fields for specifying day , date and time followed by the command to be run at that interval. 

 

* * * * * command to be executed
- - - - -
| | | | |
| | | | +----- day of week (0 - 6) (Sunday=0)
| | | +------- month (1 - 12)
| | +--------- day of month (1 - 31)
| +----------- hour (0 - 23)
+------------- min (0 - 59)

* in the value field above means all legal values as in braces for that column.

The value column can have a * or a list of elements separated by commas. An element is either a number in the ranges shown above or two numbers in the range separated by a hyphen (meaning an inclusive range).

Note: The specification of days can be made in two fields: month day and weekday. If both are specified in an entry, they are cumulative meaning both of the entries will get executed .

4. Crontab Example

A line in crontab file like below removes the tmp files from /home/someuser/tmp each day at 6:30 PM.

30 18 * * * rm /home/someuser/tmp/*

Changing the parameter values as below will cause this command to run at different time schedule below :

30 0 1 1,6,12 * -- 00:30 Hrs on 1st of Jan, June & Dec.

0 20 * 10 1-5 --8.00 PM every weekday (Mon-Fri) only in Oct.

0 0 1,10,15 * * -- midnight on 1st ,10th & 15th of month

5,10 0 10 * 1 -- At 12.05,12.10 every Monday & on 10th of every month

Note : If you inadvertently enter the crontab command with no argument(s), do not attempt to get out with Control-d. This removes all entries in your crontab file. Instead, exit with Control-c.

5. Crontab Environment

Cron invokes the command from the user's HOME directory with the shell, (/usr/bin/sh).

cron supplies a default environment for every shell, defining:

HOME=user's-home-directory

LOGNAME=user's-login-id

PATH=/usr/bin:/usr/sbin:.

SHELL=/usr/bin/sh

Users who desire to have their .profile executed must explicitly do so in the crontab entry or in a script called by the entry.

6. Disable Email

By default cron jobs sends a email to the user account executing the cronjob. If this is not needed put the following command At the end of the cron job line .

>/dev/null 2>&1

7. Generate log file

To collect the cron execution execution log in a file :

30 18 * * * rm /home/someuser/tmp/* > /home/someuser/cronlogs/clean_tmp_dir.log

8. Next Steps

This article covered a significant aspect of system administration of setting up cronjobs . Unix administration involves lots of different tasks and some of these tasks are covered in this website but still there are many areas not covered here .

Following books available for online buying from Amazon.com . You should have following two books in your bookshelf for ready reference if you are involved in Unix system administration .

Essential System Administration, Third Edition by by Æleen Frisch

Solaris Operating Environment Boot Camp by David Rhodes, Dominic Butler

If you already own these books the amazon display panel below shows some of the best-selling books for System Administration and you can choose the book here or visit my other website for more selection of best selling unix system administration books by following this link - .Unix system administration books at besttechbooks.com ,from Amazon.com.

You can show your appreciation by buying the books for yourself and encouraging the friends to buy using amazon links below or anywhere at adminschoice.com or besttechbooks.com . Thanks for your appreciation in advance.

Script to Move Tables and Indexes to another Tablespace

Generating a Script to move all tables to another tablespace in a schema: -

set heading off
set pages 0
set feedback off
spool move_tables.sql
SELECT 'ALTER TABLE '|| OWNER ||'.'|| OBJECT_NAME ||' MOVE TABLESPACE '||' USERS ;'
FROM ALL_OBJECTS WHERE OWNER = 'SCOTT' AND OBJECT_TYPE = 'TABLE'
/
spool off
@move_tables.sql

Generating a Script to rebuild all indexes to another tablespace in a schema: -

set heading off
set pages 0
set feedback off
spool move_indexes.sql
SELECT 'ALTER INDEX '|| OWNER ||'.'||INDEX_NAME||' REBUILD TABLESPACE USERS ;' FROM ALL_INDEXES
WHERE OWNER = 'LEAD'
/
spool off
@move_indexes.sql

Oracle Background Process

You can see the Oracle background processes with this queries:
* select * from v$session where type ='BACKGROUND';
* select name,description from v$bgprocess;

Here are some of the most important Oracle background processes:

ARCH - (Optional) Archive process writes filled redo logs to the archive log location(s). In RAC, the various ARCH processes can be utilized to ensure that copies of the archived redo logs for each instance are available to the other instances in the RAC setup should they be needed for recovery.

CJQ - Job Queue Process (CJQ) - Used for the job scheduler. The job scheduler includes a main program (the coordinator) and slave programs that the coordinator executes. The parameter job_queue_processes controls how many parallel job scheduler jobs can be executed at one time.

CKPT - Checkpoint process writes checkpoint information to control files and data file headers.

CTWR - This is a new process Change Tracking Writer (CTWR) which works with the new block changed tracking features in 10g for fast RMAN incremental backups.

CQJ0 - Job queue controller process wakes up periodically and checks the job log. If a job is due, it spawns Jnnnn processes to handle jobs.

DBWR - Database Writer or Dirty Buffer Writer process is responsible for writing dirty buffers from the database block cache to the database data files. Generally, DBWR only writes blocks back to the data files on commit, or when the cache is full and space has to be made for more blocks. The possible multiple DBWR processes in RAC must be coordinated through the locking and global cache processes to ensure efficient processing is accomplished.

FMON - The database communicates with the mapping libraries provided by storage vendors through an external non-Oracle Database process that is spawned by a background process called FMON. FMON is responsible for managing the mapping information. When you specify the FILE_MAPPING initialization parameter for mapping data files to physical devices on a storage subsystem, then the FMON process is spawned.

LGWR - Log Writer process is responsible for writing the log buffers out to the redo logs. In RAC, each RAC instance has its own LGWR process that maintains that instance’s thread of redo logs.

LMON - Lock Manager process

MMON - The Oracle 10g background process to collect statistics for the Automatic Workload Repository (AWR).

MMNL - This process performs frequent and lightweight manageability-related tasks, such as session history capture and metrics computation.

MMAN - is used for internal database tasks that manage the automatic shared memory. MMAN serves as the SGA Memory Broker and coordinates the sizing of the memory components.

PMON - Process Monitor process recovers failed process resources. If MTS (also called Shared Server Architecture) is being utilized, PMON monitors and restarts any failed dispatcher or server processes. In RAC, PMON’s role as service registration agent is particularly important.

Pnnn - (Optional) Parallel Query Slaves are started and stopped as needed to participate in parallel query operations.

RBAL - This process coordinates rebalance activity for disk groups in an Automatic Storage Management instance.

SMON - System Monitor process recovers after instance failure and monitors temporary segments and extents. SMON in a non-failed instance can also perform failed instance recovery for other failed RAC instance.

WMON - The "wakeup" monitor process


Data Guard/Streams/replication Background processes

DMON - The Data Guard Broker process.

SNP - The snapshot process.

MRP - Managed recovery process - For Data Guard, the background process that applies archived redo log to the standby database.

ORBn - performs the actual rebalance data extent movements in an Automatic Storage Management instance. There can be many of these at a time, called ORB0, ORB1, and so forth.

OSMB - is present in a database instance using an Automatic Storage Management disk group. It communicates with the Automatic Storage Management instance.

RFS - Remote File Server process - In Data Guard, the remote file server process on the standby database receives archived redo logs from the primary database.

QMN - Queue Monitor Process (QMNn) - Used to manage Oracle Streams Advanced Queuing.


Oracle Real Application Clusters (RAC) Background Processes


The following are the additional processes spawned for supporting the multi-instance coordination:

DIAG: Diagnosability Daemon – Monitors the health of the instance and captures the data for instance process failures.

LCKx - This process manages the global enqueue requests and the cross-instance broadcast. Workload is automatically shared and balanced when there are multiple Global Cache Service Processes (LMSx).
LMON - The Global Enqueue Service Monitor (LMON) monitors the entire cluster to manage the global enqueues and the resources. LMON manages instance and process failures and the associated recovery for the Global Cache Service (GCS) and Global Enqueue Service (GES). In particular, LMON handles the part of recovery associated with global resources. LMON-provided services are also known as cluster group services (CGS)

LMDx - The Global Enqueue Service Daemon (LMD) is the lock agent process that manages enqueue manager service requests for Global Cache Service enqueues to control access to global enqueues and resources. The LMD process also handles deadlock detection and remote enqueue requests. Remote resource requests are the requests originating from another instance.

LMSx - The Global Cache Service Processes (LMSx) are the processes that handle remote Global Cache Service (GCS) messages. Real Application Clusters software provides for up to 10 Global Cache Service Processes. The number of LMSx varies depending on the amount of messaging traffic among nodes in the cluster.

The LMSx handles the acquisition interrupt and blocking interrupt requests from the remote instances for Global Cache Service resources. For cross-instance consistent read requests, the LMSx will create a consistent read version of the block and send it to the requesting instance. The LMSx also controls the flow of messages to remote instances.

The LMSn processes handle the blocking interrupts from the remote instance for the Global Cache Service resources by:

*Managing the resource requests and cross-instance call operations for the shared resources.
*Building a list of invalid lock elements and validating the lock elements during recovery.
*Handling the global lock deadlock detection and Monitoring for the lock conversion timeouts

Deadlocks

What is a deadlock and how does oracle handle it?

A deadlock is a condition where two or more users are waiting for data locked by each other. Oracle automatically detects a deadlock and resolves them by rolling back one of the statements involved in the deadlock, thus releasing one set of data locked by that statement. Statement rolled back is usually the one which detects the deadlock.
Deadlocks are mostly caused by explicit locking because oracle does not do lock escalation and does not use read locks. Multi table deadlocks can be avoided by locking the tables in same order in all the applications, thus precluding a deadlock.

Resolving Oracle deadlocks

There are several remedies for resolving aborted tasks from deadlocks:

• Tune the application - Single-threading related updates and other application changes can often remove deadlocks.  Re-scheduling batch update jobs to low-update times an also help.
   
• Add INITRANS - In certain conditions, increasing INITRANS for the target tables and indexes (adding slots to the ITL) can relieve deadlocks.
   
• Use smaller blocks with less data - Since the deadlock contention is at the block-level, consider moving these tables and indexes to a super-small blocksize (create a db2k_cache_size), and using a high PCTFREE to space-out the data over MORE blocks.

Demo:

Demo

Deadlocks Demo

-- session 1 CREATE TABLE deadlock ( id NUMBER, fld VARCHAR2(1)); INSERT INTO deadlock VALUES (1,'A'); INSERT INTO deadlock values (2,'B'); COMMIT; SELECT * FROM deadlock; UPDATE deadlock SET fld = 'M' WHERE id = 1; -- session 2 UPDATE deadlock SET fld = 'N' WHERE id = 2; -- session 1 UPDATE deadlock SET fld = 'X' WHERE id = 2; -- session as SYS conn / as sysdba SELECT (   SELECT username   FROM gv   WHERE sid=a.sid) blocker,   a.sid, ' is blocking ', (   SELECT username   FROM gv   WHERE sid=b.sid) blockee,   b.sid FROM gv a, gv b WHERE a.block = 1 AND b.request > 0 AND a.id1 = b.id1 AND a.id2 = b.id2; -- session 2 UPDATE DEADLOCK SET fld = 'Y' WHERE id = 1; SQL> ORA-00060: deadlock detected while waiting for resource ROLLBACK;

AUTOMATED HOT BACK UP IN LINUX

STEP 1) CREATE .sh file using text editor

example::::
-------------------------------------------------------------------------------------

#!/bin/bsh -f

usr=sys

pswd=sys

myDate="$(date +%d-%b-%y_%H:%M)"

export ORACLE_HOME=/home/oracle/oracle/product/10.2.0/db_1

export ORACLE_SID=toms

$ORACLE_HOME/bin/sqlplus -s $usr/$pswd as sysdba<<eof

alter database begin backup;

exit;

eof

cp /home/oracle/oracle/product/10.2.0/oradata/toms/sysaux01.dbf /home/oracle/backup/

cp /home/oracle/oracle/product/10.2.0/oradata/toms/example01.dbf /home/oracle/backup/

cp /home/oracle/oracle/product/10.2.0/oradata/toms/system01.dbf /home/oracle/backup/

cp /home/oracle/oracle/product/10.2.0/oradata/toms/undotbs01.dbf /home/oracle/backup/

cp /home/oracle/oracle/product/10.2.0/oradata/toms/users01.dbf /home/oracle/backup/

$ORACLE_HOME/bin/sqlplus -s $usr/$pswd as sysdba <<eof

alter database end backup;

exit;

<<eof

step2) run .sh file and check whether its running or not

./test.sh

step3) create crontab file(for auto scheduling)

$crontab -e

add the following

  * * * * * /home / oracle / test3.sh > /home / oracle / new3.txt

  in the given example test3.sh will execute in each minute......

*     *     *   *    *        command to be executed

-     -     -   -    -

|     |     |   |    |

|     |     |   |    +----- day of week (0 - 6) (Sunday=0)

|     |     |   +------- month (1 - 12)

|     |     +--------- day of        month (1 - 31)

|     +----------- hour (0 - 23)

+------------- min (0 - 59)

More examples:::

              30 0 1 1,6,12 *    –  00:30 Hrs  on 1st of Jan, June & Dec.

              0 20 * 10 1-5      – 8.00 PM every weekday (Mon-Fri) only in Oct.

       0 0 1,10,15 * *    –  Midnight on 1st ,10th & 15th of month
       5.10         0 10 * 1   –  At 12.05,12.10 every Monday & on 10th of every month

More example

If i want to run a particular file on 11.30 in morning daily then

 30  11  *  *  * /home/oracle/test.sh >> /home/oracle/text.txt

All operations will be noted on the text file, and '>>' will append the text to the existing file

Remote Diagnostic Agent (RDA)

RDA 4 Getting Started

RDA Documentation Links
Getting Started	FAQ	Man Pages	Troubleshooting Guide	Training	RDA Documentation Index

In This Document

Overview What's new in this RDA Release Platforms Supported Products Supported Versions Supported Why Use RDA? Oracle Configuration Manager Download RDA Installation Instructions Running RDA - UNIX

Running RDA - Windows Running RDA - RAC Cluster Running RDA - OpenVMS Upgrading RDA Password Security View RDA Output Security Filters Report Feedback Terms of Use

RDA 4.x Overview

Note: Please review installation and execution instructions below. If you experience difficulties or receive errors please follow the RDA Troubleshooting Guide and RDA FAQ  for instructions on reporting problems.

Overview

Remote Diagnostic Agent (RDA) is a command-line diagnostic tool that is executed by an engine written in the Perl programming language. RDA provides a unified package of support diagnostics tools and preventive solutions (see Knowledge Article 330760.1). The data captured provides Oracle Support with a comprehensive picture of the customer's environment which aids in problem diagnosis.
Oracle Support encourages the use of RDA because it greatly reduces service request resolution time by minimizing the number of requests from Oracle Support for more information. RDA is designed to be as unobtrusive as possible; it does not modify systems in any way. It collects useful data for Oracle Support only and a security filter is provided if required.
This guide provides users with an overview of RDA, the download instructions, and general steps about how to execute RDA within a UNIX, Windows, and Mac OS X environment.

What's New in this RDA Release

See the RDA 4 Release Notes for information about new features. The next release in RDA 4.x series is scheduled for release in Mid-December.

Platforms Supported

At this time, RDA has been successfully tested to run on the following Supported platforms:

• Apple Mac OS X/Darwin
• HP OpenVMS Alpha 7.3-2 and above
• HP OpenVMS Itanium
• HP Tru64 UNIX
• HP-UX Itanium
• HP-UX PA-RISC (32 and 64 bit)
• IBM AIX on POWER Systems(32 and 64 bit)
• IBM Dynix/Ptx
• IBM Linux on POWER
• IBM zSeries Based Linux
• Linux Itanium
• Linux x86 (32 and 64 bit)
• Microsoft Windows 2000 Workstation and Server
• Microsoft Windows 2003 Server
• Microsoft Windows Vista
• Microsoft Windows XP Professional
• Sun Solaris Intel
• Sun Solaris SPARC (32 and 64 bit)

Following Platforms are NOT Supported:

• IBM Z/OS (OS/390)

You can run RDA also on other platforms that support Perl 5.005 and later. However, Oracle Support recommends testing on a non-production server first because the performance is unpredictable. For example, you will receive errors when RDA attempts to run utilities and commands that are not supported on these platforms.

Products Supported

RDA collects information that is useful for diagnosing issues related to the following Supported Oracle products

• OLAP Products (Express Server, Financial Analyzer, and Demand Planning Server)
• Oracle Application Server (iAS 1.0.2.x/9.0.x/10.1.2.x/10.1.3.x,10.1.4.x,11g, HTTP Server,WebCache,J2EE/OC4J)
• Oracle BPEL
• Oracle Collaboration Suite (Email Server,Calendar,Discussions,Content Services,Workspaces,WebClient,and Wireless)
• Oracle Data Integrator
• Oracle Developer (Forms and Reports)
• Oracle Ebusiness Suite 11i and 12
• Oracle Enterprise Content
• Oracle Essbase
• Oracle Enterprise Single Sign-on
• Oracle Guardian
• Oracle Hyperion
• Oracle Identity Management
• Oracle JDBC/PRO *C/ODBC and other development client related information
• Oracle Management Server and Intelligent Agent (Grid Server, Agent Server, DB Control)
• Oracle Networking products
• Oracle Portal Software 7.0, 7.2, and 7.3
• Oracle RAC Cluster (Single/Multiple Nodes, Automatic Storage Management, Oracle Cluster File System, Data Guard)
• Oracle RDBMS Server (Standard and Enterprise Editions)
• Oracle Retail (Retek)
• Oracle SQL*Plus/iSQL*Plus
• Oracle Universal Archive (11g)

Additional new Oracle products will be supported in future releases. We are constantly enhancing and refining RDA, so make sure you have the latest version! For more detailed information see the Knowledge Article: 330760.1

Versions Supported

RDA supports most supported versions of the Oracle products listed in Products Supported. In most cases, it runs on desupported versions as well, although the collected information may not be as extensive.

Why use RDA?

Oracle strongly encourages the use of Remote Diagnostic Agent (RDA) diagnostics collections because it provides a comprehensive picture of the customer's environment. Providing RDA diagnostic output, especially when submitting a Service Request online, can minimize the requirement for follow up questions that may delay problem resolution. RDA collections are essential for the following types of service requests:

• Installation/configuration issues
• Performance issues
• ORA-600, ORA-7445, ORA-3113, and ORA-4031 errors
• Upgrade, migration, and linking issues
• Developer issues
• Oracle Database issues
• Oracle Application Server/Fusion Middleware issues
• Oracle Collaboration products (Oracle Collaboration Suite and Oracle Beehive) issues
• Oracle Application issues
• Acquired company product issues
• Other corrective issues

Oracle Configuration Manager

The Oracle Configuration Manager (OCM) is a data collector that collects key Oracle and system statistics of the system that it is running on. As a key component of Oracle Configuration Manager, OCM optimizes the customer benefits of OCM by automating the configuration collections. OCM simplifies your ability to automate your configuration and diagnostics uploads to Oracle. Oracle Configuration Manager bundle provides the following benefits:

• Secure, automated configuration collection
• Simplified Service Request logging, tracking and reporting
• Pro-active configuration-specific notification of Security and General Alerts
• HealthCheck recommendations based on Support best practices when using configuration auto-collection
• Personalized access to best practices and the Oracle Knowledge base
• Project cataloging of key milestones and contacts associated with your configurations
• Improves Oracle's understanding of your business needs

For troubleshooting information or to learn more about Oracle Configuration Manager see the following Oracle Knowledge Article: 250434.1. For your benefit, Oracle created an RDA/OCM bundle . This bundle contains both RDA and OCM software and it allows customers to install OCM as part of the RDA data collection process.

Download

• click the zip file for your platform
• save it locally as rda.zip

Each file contains all of the required files so choose only one to download.

Note:If your platform is certified for OCM, the RDA zip file below is conveniently bundled with OCM.

RDA bundle (Released September, 8 2009)
Platform	Download File
Apple Mac OS X	Download Zip File
HP OpenVMS	Download Zip File
HP Tru64	Download Zip File
HP-UX Itanium	Download Zip File
HP-UX PA-RISC (32-bit)	Download Zip File
HP-UX PA-RISC (64-bit)	Download Zip File
IBM AIX on POWER Systems (32-bit)	Download Zip File
IBM AIX on POWER Systems (64-bit)	Download Zip File
IBM Dynix/Ptx	Download Zip File
IBM Linux on POWER	Download Zip File
IBM zSeries Based Linux	Download Zip File
Linux Itanium	Download Zip File
Linux x86 (32-bit)	Download Zip File
Linux x86 (64-bit)	Download Zip File
Microsoft Windows (32-bit)	Download Zip File
Microsoft Windows (64-bit)	Download Zip File
Sun Solaris Intel (32-bit)	Download Zip File
Sun Solaris Intel (64-bit)	Download Zip File
Sun Solaris SPARC (32-bit)	Download Zip File
Sun Solaris SPARC (64-bit)	Download Zip File

Note: RDA 4 is written in Perl and is a command-line script. Besides Perl and a few basic Perl libraries it is not necessary to install extra software. If for some reason Perl 5.005 or above is not available or is inaccessible on your environment, then a binary version is available for download on major platforms. For additional information please read the RDA FAQ - Knowledge Article 330363.1.

Installation Instructions

Instructions for UNIX/ZLinux Type Operating Systems

1. Choose or create a directory or area on your UNIX server. Make sure you have sufficient space for the RDA output (~150MB). It does not matter where you create this directory or what it is named, but the same user that runs RDA must own it. Do not use a directory that contains an older version of RDA unless you have deleted the previous version of RDA first. If necessary, you can reuse prior setup files.

   Note: The rda.zip creates a directory named "rda" containing all the required files when you extract it..

2. FTP the downloaded rda.zip file to your UNIX server in binary mode and place it in the directory chosen in step 1.
   
3. Extract the .zip archive contents into a new directory, preserving the directory structure of the archive. Do not extract into a directory that contains an older RDA version. For example: unzip rda.zip
   
4. Make sure the RDA command (rda.sh and rda.pl) is executable. To verify, enter the following command: chmod +x <rda>
   
5. You can verify the RDA installation using the following command: ./<rda> -cv

Instructions for Microsoft Windows Based Operating Systems

1. Create a new directory or area on your Windows server. Make sure you have sufficient space for RDA output (~150MB). It does not matter where you create this directory or what it is named, but it should have access to the Oracle software installation. Do not use a directory that contains an older version of RDA unless you have deleted the previous version of RDA first.

   Note: The rda.zip file creates a directory named " rda" containing all the required files when you extract it. Do not extract the contents of the RDA archive on a Windows client first or you will have to remove the ^M characters from the end of each line in all of the shell scripts in order for them to run.

2. Transfer the downloaded rda.zip file to your Windows server and place it in the directory chosen in step 1. If transferring the zip file from one system to another using FTP, remember to transfer it in binary mode.
3. Extract the .zip archive contents into a new directory, preserving the directory structure of the archive. Do not extract into a directory that contains an older RDA version. For example: unzip rda.zip
   
4. You can verify the RDA installation using the following command: <rda> -cv

Instructions for HP OpenVMS Based Operating Systems

Due to the special nature of HP OpenVMS environment, Oracle created a separate document. See the RDA 4 - OpenVMS Users Guide: Knowledge Article 171748.1.

Running RDA

Note: It is impossible to tell how long RDA will take to execute, as it depends on many variables, such as system activity, the options chosen, network settings, and so on. On an average system, RDA takes just a few minutes to run. Most scripts are designed to stop if for some reason they cannot complete within 30 seconds, (for example, the lsnrctl status command will stop if the listener is not responding.) It is not unusual for RDA to take 15 minutes or more on a busy server, especially if there are many Oracle listener processes active.

Instructions for UNIX type operating systems (including ZLinux):

1. Before you begin: Log on as the UNIX user that owns the Oracle installation. On some operating systems, this user will not have the necessary permissions to run all of the commands and utilities called by RDA (e.g. sar, top, vmstat, etc). If you are running RDA to assist in resolving a service request, the analyst will most likely need the information pertaining to the Oracle owner. The exception to this rule is when RDA is used to assist in a performance related issue. In this case, Oracle support recommends that you run RDA as the UNIX user who owns the Oracle software.

   Note: If you use su to connect to root or a privileged user, do not use "su -" as the minus resets the environment.

2. Select the RDA command line script that you will be using. Choose one of the following: rda.sh - Use this command if Perl is not available.
   rda.pl - Use this command if Perl is available.
   
   Use the following command to verify that Perl is installed and available in the path:
   perl -V
   Inspect the command output, checking that '.' (i.e. tells perl to look for libaries in current directory) is present in @INC section. Notice the last entry "." in the example below:
   

   @INC: /usr/lib/perl5/5.8.0/i386-linux-thread-multi /usr/lib/perl5/5.8.0 /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.0 /usr/lib/perl5/site_perl /usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.0 /usr/lib/perl5/vendor_perl /usr/lib/perl5/5.8.0/i386-linux-thread-multi /usr/lib/perl5/5.8.0

   
   

3. Note: The RDA command you selected above is represented as <rda> in the rest of this procedure. Therefore substitute rda.sh, rda.pl, or perl rda.pl in place of <rda>.

4. The data collection requires an initial setup to determine which information should be collected. Enter the following command to initiate the set up: ./<rda> -S
   Including the dot ensures that RDA is executed from the local directory.
   After setup completes, you can review the setup file setup.cfg by opening it a text editor such as vi.
   You can also choose to collect only specific data. For more details, view the command usage help by specifying the -h option, or complete manual page with the -M option.
   
5. Make sure you have ample space for RDA output (approximately 150 MB) in the output location you have chosen in STEP 4. Sometimes, depending on the size of your data files (alert.log, *.trc, install*.log, apache logs, networking logs, etc) of the Oracle installation the final size of your RDA collection may reach over 1000 MB in size.
6. At this point, you can collect diagnostic information. sqlplus should connect to the database with the userid that you specified during the setup. Start the data collection using the following command: ./<rda> [-v]
   The -v option is optional; it allows you to view the collection progression. Additionally, if you want to re-run the RDA collection, you can use the "-fv" option, for example, ./<rda> -fv. For additional information read the RDA FAQ.
   
7. A more targeted way of executing RDA and a way to limit the number of setup questions asked is the use of Diagnostic profiles (<rda> -p <profile_name>), see RDA Profiles for list of various profiles or run <rda> -L profiles. e.g:. ./<rda> -p DB10g
8. The output is a set of HTML files that are located in the RDA output directory which you specified at setup. You can review the data collected by using a Web Browser to open the following file located in: <output_directory>/<report_group>__start.htm
   
9. The final output is also packaged in an archive located in the <output_directory> chosen during RDA setup -- the packaged output file will have a .zip, .tar, .tar.gz, or .tar.Z extension. If the data collection was generated to assist in resolving a Service Request, send the report archive (for example, RDA.RDA_db_autoxyz.tar) to Oracle Support by uploading the file via My Oracle Support. If FTP'ing the file, please be sure to FTP in BINARY format. Please do not rename the file, as the file name helps Oracle Support quickly identify that RDA output is attached to the service request or  bundle with other files such as trace or log files into a single or common archive file (.tar, .zip, .jar, etc.)

   Note: The final output directory may not contain an archive file (.zip, .tar, .tar.gz, or .tar.Z). If the archive is missing, please archive all of the files in the <output_directory> manually and send them to Oracle Support. You can use a packaging/archive utility program such as http://www.info-zip.org/Zip.html to perform this task.

For more information or clarification please review the demonstration viewlets located in the Training document or please read the RDA FAQ. If you still have problems please follow Troubleshooting Steps.

Instructions for Microsoft Windows Based Operating Systems

1. Before you begin: Log on as the WINDOWS user that owns the Oracle installation. On some operating systems, this user will not have the necessary permissions to run all of the commands and utilities called by RDA (e.g. WinMsd utility, MSInfo32 on Windows 2000, Windows 2003) If you are running RDA to assist in resolving a service request, the analyst will most likely need the information pertaining to the Oracle owner. The exception to this rule is when RDA is used to assist in a performance related issue. In this case, Oracle support recommends that you run RDA as the user who owns the Oracle software.
   
2. Choose one RDA command line script that you will be using, either: rda.cmd Use this command if Perl is not available.
   rda.pl Use this command if Perl is available in the path. To verify if Perl is available, enter the following command:
   perl -V
   In the command output, verify that '.' (i.e. the current directory) is present in @INC section.
   The RDA command rda.cmd or rda.pl you choose is represented as <rda> in the rest of this procedure.
   
3. The data collection requires an initial setup to determine which information should be collected. Enter the following command from the Windows Command or DOS prompt to initiate the set up: REMEMBER: <rda> means use either rda.cmd or rda.pl. <rda> -S
   After setup completes, you can review the setup file setup.cfg by opening it a text editor like wordpad.
   You can also choose to collect only specific data. For more details, view the command usage help by specifying the -h option, or complete manual page with the -M option.
   
4. Make sure you have ample space for RDA output (approximately 150 MB) in the output location you have chosen in STEP 4. Sometimes, depending on the size of your data files (alert.log, *.trc, install*.log, apache logs, networking logs, etc) of the Oracle installation the final size of your RDA collection may reach over 1000 MB in size.
5. At this point, you can collect diagnostic information. Sqlplus should connect to the database with the userid that you specified during the setup. Start the data collection using the following command: <rda> [-v]
   The -v option is optional. It allows you to view the collection progression. Additionally, if you want to rerun RDA collection again, you can use the "-fv" option like <rda> -fv. For additional information, read the RDA FAQ.
   
6. A more targeted way of executing RDA and a way to limit the number of setup questions asked is the use of Diagnostic profiles (<rda> -p <profile_name>), see RDA Profiles for list of various profiles . e.g:. <rda> -p DB10g
7. The output is a set of HTML files that are located in the RDA output directory which you specified at setup. You can review the data collected, by using a Web Browser to open the following file located in: <output_directory>/<report_group>__start.htm
   
8. The final output is also packaged in an archive located in the <output_directory> chosen during RDA setup -- the packaged output file will have a .zip, .tar, .tar.gz, or .tar.Z extension. If the data collection was generated to assist in resolving a Service Request, send the report archive (for example, RDA.RDA_db_autoxyz.zip) to Oracle Support by uploading the file via My Oracle Support. If FTP'ing the file, please be sure to FTP in BINARY format. Do not rename the file, as the file name helps Oracle Support quickly identify that RDA output is attached to the service request. The final output directory may not contain an archive file (.zip, .tar, .tar.gz, or .tar.Z). If the archive is missing, please archive all the files in the <output_directory> manually and send them to Oracle Support. You can use a packaging/archive utility program such as http://www.info-zip.org/Zip.html
   
9. When you send/upload an RDA report in a compressed file (such as .zip, .tar, .tar.gz, or .tar.Z), please do so in a separate file and it is named with 'RDA' in the filename; this ensures our internal tools and users can find and analyze it correctly. When sending RDA output to Oracle Support via a My Oracle Support Service Request, we highly recommend that you attached the output file as a single file.  Do not bundle with other files such as trace or log files into a single or common archive file (.tar, .zip, .jar, etc.)

For more information or clarification please review the demonstration viewlets located in the Training document or read the RDA FAQ. If you still have problems please follow Troubleshooting Steps.

Instructions for Oracle RAC Cluster or Multi-Node Environment

Due to the special nature of Oracle RAC Cluster environment a separate document was created. Please refer to RDA 4 - RAC Cluster/Multi-Node Users Guide - Knowledge Article 359395.1.

Instructions for HP OpenVMS Based Operating Systems:

Due to the special nature of HP OpenVMS environment a separate document was created. Please refer to RDA 4 - OpenVMS Users Guide - Knowledge Article 171748.1.

Upgrading RDA

Use the following procedure to upgrade the RDA:

1. Delete the rda directory and all it's contents. If you want to use the same setup.cfg file again, move it to another location first. Also, move any output files you wish to save.
2. Download the new version of RDA from this Knowledge Article.
3. Extract the files in the directory you want to install RDA. It will recreate the rda directory for you. Remember that the directory structure in the archive is important; don't move any RDA files.

Special Notes On Userids And Passwords

As a means of providing higher security when using RDA, passwords are no longer stored in plain text in the setup.txt file. As result, RDA prompts for the required passwords when collecting the data.
If the Perl implementation installed on your operating system supports it, RDA will suppress the character echo during password requests. When the character echo is suppressed, the password is requested twice for verification. If both entered passwords do not match after three attempts, the request is cancelled.
RDA can perform OS authentication, which eliminates having to enter a password for database information gathering. It also accepts "/" as a username to avoid entering a password when RDA is gathering database information.
For executing RDA at regularly scheduled intervals via cron, passwords can be encoded inside the setup file. For instance, to encode the system password, use the following command:
<rda> -A system
The password will be requested interactively.

Special Note on the Output Directory

To limit security vulnerabilities, the permissions of the RDA output directory should be set as restrictive as possible. The output directory could contain sensitive configuration information and, when no other mechanism is available, temporary data collection files.

Viewing RDA Report Output Files

RDA output is not encrypted and can be viewed by anyone. You can view the RDA report files using almost any web browser by opening the <rda_directory><output_directory>/<report_group>__start.htm.
We recommend using Mozilla 1.x ( or Higher), Netscape 4.x (or higher), or Internet Explorer 4.x (or higher).

Special Note on Security Filters

The following table is a snapshot of the 'System Settings' section of the end report to demonstrate exactly what Filtering does. The result below is dependent on the system configuration.

Machine and Version	SunOS <host> 5.6 Generic 105181-29 sun4u
Fully qualified host name	<host><domain>
Platform	32 - bit Sun O/S Version 2.6
Logged in as	<user>
Last run as	uid=52279 (<user>)gid=101(dba) groups=101(dba)
Executed as Oracle home owner?	Yes
Sensitive information removed?	Yes
Output file prefix	RDA
Output file directory	/emea/oracle/<user>/home/rda2/security/output
RDA install directory	/emea/oracle/<user>/home/rda2

The host names (machine names), are substituted by '<host>'. Similarly, user names are substituted by '<user>' instead of 'oracle'. The group of the software owner is 'dba', which is not substituted by the default filter.
Note that the above report was run as the owner of the Oracle home directory, which was different from 'oracle'. This is the reason why we see '<user>' instead of 'oracle'. The group of the Oracle software owner is 'dba', which is not substituted by the default filter.
The RDA filter substitutes sensitive information (such as user names) using something like  <user> in the reports. As shown later in this document, you can customize what information RDA filters out and how RDA substitutes this information.
RDA provides you with a default filter, which currently filters out the information in the list below.

• Domain names
• Group names
• Host names
• IPv4 and IPv6 addresses
• LDAP domain components
• Network masks
• User names

Using Security Filters

RDA allows you to remove sensitive data from RDA reports. The security profile can be used to turn on filtering and can be combined with other profiles. For example:
<rda> -S -p DB10g-Security 
This will do the RDA setup for the DB10g profile and turn on filtering through the Security profile. If you want to enable the filtering for an existing setup:
<rda> -X Filter enable
When the filter is not yet defined, this command will also generate the default filter configuration, based on the system configuration.

Reporting Problems or Feedback

If you have issues with the Remote Diagnostic Agent (RDA), you can file an SR by selecting OSS Support Tools from the product list of values on the Create a SR screen. Click on "Expand the Product List" button to see the full set of products. Select the "Remote Diagnostic Agent (RDA) Issue" type and complete the SR.

Note: Do not let an RDA issue prevent you from progressing on your technical issue that you are running RDA for. Please inform the engineer that owns your technical issue that you are having trouble with RDA and request that he give you instructions on collecting necessary data manually to resolve the issue. RDA is designed to speed up the resolution time of technical issues and we do not want it to interfere with resolution. However, it is important that we get the RDA issue resolved so that it can be used to help speed up resolution for future technical issues.

Legal Notices And Terms Of Use

By downloading and using RDA you agree to the following: Warranties And Disclaimers.

Documentation Accessibility

Our goal is to make Oracle products, services, and supporting documentation accessible, with good usability, to the disabled community. To that end, our documentation includes features that make information available to users of assistive technology. This documentation is available in HTML format, and contains markup to facilitate access by the disabled community. Standards will continue to evolve over time, and Oracle is actively engaged with other market- leading technology vendors to address technical obstacles so that our documentation can be accessible to all of our customers. For additional information, visit the Oracle Accessibility Program Web site at
http://www.oracle.com/accessibility/
Accessibility of Code Examples in Documentation JAWS, a Windows screen reader, may not always correctly read the code examples in this document. The conventions for writing code require that closing braces should appear on an otherwise empty line; however, JAWS may not always read a line of text that consists solely of a bracket or brace.
Accessibility of Links to External Web Sites in Documentation. This documentation may contain links to Web sites of other companies or organizations that Oracle does not own or control. Oracle neither evaluates nor makes any representations regarding the accessibility of these Web sites.