Downloading 11.2.0.2 from MoS? Don’t get caught!

Simple post, just to warn the souls that haven’t downloaded 11.2.0.2 patchset yet:

1 – You don’t need the base version 11.2.0.1 anymore, this patchset is selfsufficient

2 – There are 7 files in a total 4.7 Gigs but you don’t have to download them all

So which zip files do you need? Here’s the list :

zip1of7 + zip2of7 => Database (if you’re creating a non-ASM, non-RAC environment this is all you need)

zip3of7 => Grid Infrastructure (only needed for either or both ASM and RAC installations)

zip4of7 => Client

zip5of7 => Gateways

zip6of7 => Examples

zip7of7 => deinstall (uninstalling Oracle software it’s thru a different set of media since 11gR2)

So if you need basic non-rac non-asm upgrade or groud up installation, just download the first 2 zips.

LMC.

New Physical Database Limits of 11.2

It has been a while since I’ve gone through the documentation part of Oracle Database Server that talks about limits! I think the last one was when the rowid format changed from Oracle7 to Oracle8.

This post is not just about publishing those in another website, it’s commenting them. If you want to read the original text click here, if you want to read my remarks on the limitations, keep scrolling down, they are in bold (but don’t expect rocket science though!)

Physical Database Limits

Item	Type of Limit	Limit Value
Database Block Size	Minimum	2048 bytes; must be a multiple of operating system physical block size
Database Block Size	Maximum	Operating system dependent; never more than 32 KB
Database Blocks	Minimum in initial extent of a segment	2 blocks (just because one is for data other is for segment header)
Database Blocks	Maximum per datafile	Platform dependent; typically 222 – 1 blocks
Controlfiles	Number of control files	1 minimum; 2 or more (on separate devices) strongly recommended
Controlfiles	Size of a control file	Dependent on operating system and database creation options; maximum of 20,000 x (database block size)
Database files	Maximum per tablespace	Operating system dependent; usually 1022 (Here the bigfile tablespaces actually can index much more space with just one datafile. It's because the rowid lacks the datafile encoding hence saving more space to index more blocks)
Database files	Maximum per database	65533 – May be less on some operating systems Limited also by size of database blocks and by the DB_FILES initialization parameter for a particular instance
Database extents	Maximum per dictionary managed tablespace	4 GB * physical block size (with K/M modifier); 4 GB (without K/M modifier)
Database extents	Maximum per locally managed (uniform) tablespace	2 GB * physical block size (with K/M modifier); 2 GB (without K/M modifier)
Database file size	Maximum	Operating system dependent. Limited by maximum operating system file size; typically 222 or 4 MB blocks
MAXEXTENTS	Default value	Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter
MAXEXTENTS	Maximum	Unlimited (you should put a brake here not letting the segment have more than 100,000 extents on huge objects; 10,000 on big ones and less than 1,000 on average sized ones: typically huge is terabyte sized, big is hundreds of gigs and average sized less than 10 gigs)
Redo Log Files	Maximum number of logfiles	Limited by value of MAXLOGFILES parameter in the CREATE DATABASE statementControl file can be resized to allow more entries; ultimately an operating system limit (always change the default setting specially in a clustered environment)
Redo Log Files	Maximum number of logfiles per group	Unlimited
Redo Log File Size	Minimum size	4 MB (always start with at least 100MB on OLTP and 500MB on DWH shops)
Redo Log File Size	Maximum Size	Operating system limit; typically 2 GB
Tablespaces	Maximum number per database	64 K Number of tablespaces cannot exceed the number of database files because each tablespace must include at least one file
Bigfile Tablespaces	Number of blocks	A bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion ( 232 ) blocks. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32 K blocks and 32 TB for a tablespace with 8 K blocks.
Smallfile (traditional) Tablespaces	Number of blocks	A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (222) blocks.
External Tables file	Maximum size	Dependent on the operating system.An external table can be composed of multiple files.

Best Practices for Statistics Gathering on Oracle 11g

Some people look at Statistics Gathering as taking vitamins: nice to have. And it’s wrong.

You shouldn’t base your tuning strategy on dynamic sampling alone, or luck. Some people freak out because once they generated stats for some system and it all went south. That’s the same as saying that because cars crash and people get killed we shouldn’t drive at all.

You have to use stats. Period.

So my small contribution to make your life easier is to give 5 tips if you are using Oracle 11g:

1) Run all queries against empty tables to populate column usage (histograms will be gathered based on this column usage).

2) Run stats after data has been loaded but before indexes are created (the creation of the index gathers stats on them automatically).

3) Find (business) correlations between columns and inform the optimizer about these correlations by the means of creating Column Groups. Also use this technique instead of function-based indexes.

4) Always gather stats with AUTO_SAMPLE_SIZE so the new stats gathering algorithm of Oracle 11g can kick in.

5) If you use partition tables you *must* use synopsis so the global stats can be derived much much much faster in an incremental fashion.

I guess 1, 2 and 4 don’t offer any doubts.

Regarding 3 and 5 here’s the usual techie yada yada:

3) Column Groups are created by the means of Extended Optimizer Statistics a new procedure of the DBMS_STATS package: CREATE_EXTENDED_STATS.

See an example in this very good article.

5) Synopsis are created when you enable incremental stats at the table or system level:

table level: SQL> exec DBMS_STATS.SET_TABLE_PREFS(‘SCOTT’,'TABLE_XYZ’,'INCREMENTAL”TRUE’);

system level: SQL> exec DBMS_STATS.SET_GLOBAL_PREFS(‘INCREMENTAL”TRUE’);

This will avoid rescaning partitions that haven’t changed since the last stats generation, making it faster.

So remember: ASICS it’s a matter of survival, not an option.

LMC.

Extended Optimizer Statistics

Best way to deal with ARCHIVED LOGs

This article will try to address a common misunderstanding of the role archived logs and its backups play in the recovery process.
Some notions first:

1) If you backup your archived logs to disk regularly you should delete them after the backup using the DELETE INPUT clause

2) If you backup your archived logs to tape regularly you might want to keep a few of those around (on disk) just in case…

What happens to your archived logs on disk when you backed them up? If you don’t use the DELETE INPUT clause they stay there. Take a look:

– Before the backup

SQL> select count(*) from  v$archived_log where deleted = ‘NO’;

COUNT(*)
———-
32

– RMAN backup launched:

RMAN> backup archivelog all;
(…)
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
Finished backup at 23-OCT-09

– During the first stage of the backup

SQL> r
1* select count(*) from  v$archived_log where deleted = ‘NO’

COUNT(*)
———-
33

One more archived log was generated.

– When the backup finishes

SQL> r
1* select count(*) from  v$archived_log where deleted = ‘NO’

COUNT(*)
———-
33

The archived logs that were backed up, are still around because we haven’t used the DELETE INPUT clause.
Now how can we get rid of these archived logs that have already been backed up?
What’s the best way to clean them regularly?

If we backup again archived logs but with the DELETE INPUT clause, RMAN will backup *again* the ones that haven’t been deleted, but are already backed up:

RMAN> backup archivelog all delete input;

After this we are clean, but we have duplicated archived logs in the backups!

SQL> select count(*) from  v$archived_log where deleted = ‘NO’;

COUNT(*)
———-
0

Now let’s produce some redo:

SQL> create table hr.teste (n number, n2 number, n3 number, n4 number)
2  tablespace users;

Table created.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     38
Next log sequence to archive   40
Current log sequence           40

SQL> begin
2  for i in 1..100000 loop
3  insert into hr.teste values (i,i,i,i);
4  commit;
5  end loop;
6  end;
7  /

PL/SQL procedure successfully completed.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     39
Next log sequence to archive   41
Current log sequence           41

SQL> select count(*) from  v$archived_log where deleted = ‘NO’;

COUNT(*)
———-
1

The following RMAN command will *ALWAYS* produce archived logs to backup:

RMAN> backup archivelog all;

Since it does a “switch logfile” in the beggining of the operation. So if you do it hourly you will always have produce new archived logs.

To avoid backing up always the same archived logs you should  insert the following clause:

RMAN> backup archivelog all not backed up 1 times;

This still does the “switch logfile” in the beggining of the operation, but it won’t backup the same archived logs again.

If you use DELETE INPUT in the mix what happens?

RMAN> backup archivelog all not backed up 1 times delete input;

skipping archived log file /u01/app/oracle/fra/ORCL/archivelog/2009_10_23/o1_mf_1_40_5g32906q_.arc; already backed up 2 time(s)
skipping archived log file /u01/app/oracle/fra/ORCL/archivelog/2009_10_23/o1_mf_1_41_5g32gxdd_.arc; already backed up 2 time(s)
(…)
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/ORCL/archivelog/2009_10_23/o1_mf_1_40_5g32906q_.arc RECID=36 STAMP=701005489
archived log file name=/u01/app/oracle/fra/ORCL/archivelog/2009_10_23/o1_mf_1_41_5g32gxdd_.arc RECID=37 STAMP=701005677
(…)
Finished backup at 23-OCT-09

So with DELETE INPUT mixed in you not only backup only the ones that haven’t been backed up yet, but erase from disk the ones that had been already backed up.

Confused?

Imagine you wan’t to backup to tape every hour, but wish to maintain a 24 hour period of archived logs in your recovery area (disk).

Your hourly sbt_tape archivelog backup should be done with:

RMAN> backup archivelog all not backed up 1 times;

And your daily sbt_tape archivelog backup should be done with:

RMAN> backup archivelog all not backed up 1 times delete input;

If you’re backing up to disk just use one job hourly or each 2 hours or whatever frequency, that will execute this:

RMAN> backup archivelog all delete input;

Still Confused?

If you backup your archived logs to disk, skip this article and have a nice weekend.
If you backup you archived logs to tape have you ever tried to make a restore? Do you know how painfull it is to get them from tape? Ooh. It’s not painfull at all in your case? You can also skip this article and go on to do more usefull or entertainment things.

But if you can relate to the fact that a kind of “buffer” of archived logs should be kept in disk when you backup to tape, please considerer the backup architecture I’ve suggested before.

Didn’t understood jack?

Maybe this Oracle DBA thing is not for you

LMC.

Simple and Handy script for RAC: who’s opened the DB and when

When nodes on a clustered database fail to start, or just crash alone, it’s important to find patterns of behaviour. If you have a more than 2-node RAC cluster and need to figure out when did each instance started and opened the database, I have a very simple script that can help you out.

SQL> column startup_time format a30
SQL> select db_name, instance_name, startup_time
2  from  DBA_HIST_DATABASE_INSTANCE order by 3 asc
3  /

DB_NAME   INSTANCE_NAME    STARTUP_TIME
--------- ---------------- ------------------------------
ORCL      ORCL1            09.09.30 19:10:30,000
ORCL      ORCL2            09.09.30 19:14:13,000
ORCL      ORCL2            09.10.08 16:59:07,000
ORCL      ORCL1            09.10.08 17:07:14,000
ORCL      ORCL1            09.10.20 11:30:17,000
ORCL      ORCL2            09.10.20 13:38:40,000

In this example you see that on the 30th September 2009 instance 1 opened the database at 19:10:30 whilst the second instance only did the same four minutes later. On the 8th October, the second instance was the first one opening the database, and later on the 20th October, there’s a gap of 2 hours and 8 minutes between them.

Simple and handy script that let’s you figure out who’s opened the database and when.

LMC.

How many times have I told not to create objects in the SYS schema?

SQL> show user
USER is “SYS”
SQL> create table xyz (n number);

Table created.

SQL> drop table xyz;

Table dropped.

SQL> flashback table xyz to before drop;
flashback table xyz to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN

SQL> show parameter recycle

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_recycle                  string
db_recycle_cache_size                big integer 0
recyclebin                           string      on

oooopsie dasy! Looks like though I have recycle bin enabled I can’t have the feature, just because it’s in the SYS schema. Is it?

Let’s take a deeper look:

SQL> create table xyz (n number) tablespace users;

Table created.

SQL> drop table xyz;

Table dropped.

SQL> flashback table xyz to before drop;

Flashback complete.

SQL> desc sys.xyz
 
Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 N                                                  NUMBER

So the problem it’s not the SYS schema, it’s the SYSTEM tablespace. Whatever object lands there, it won’t be able to come back after droping it.

Small stuff that sometimes makes the difference.

LMC.

Temporary Tablespace Ocupation

If you are seeing the weird behaviour of seeing sort segments on temporary tablespaces from users who doesn’t have that particular tablespace assigned as the default temp, then it’s probably because their temp tbs is full.

How to check what is the full and the half full use this simple query:

select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

If the third column from any temp tbs appears as zero, then just add more temp files.

Requirements For Installing Oracle 10gR2 On RHEL/OEL 5 (x86)

Oracle shouldn’t certified EL5 for Oracle 10g. Period. So we don’t have to put up with Metalink notes like this one… please put things like they were before where we only needed to create Oracle user, adjust some Kernel parameters, and not having to check on 489371293483 RPM packages. God! Some people have a weird taste for scrambling things over…

Requirements For Installing Oracle 10gR2 On RHEL/OEL 5 (x86)
	Doc ID:	419646.1	Type:	HOWTO
	Modified Date:	01-MAR-2009	Status:	PUBLISHED

In this Document
Goal
Solution
References

---

@ (AuthWiz 2.5.5)
@ Click here to edit in wizard.

Applies to:

Oracle Server Enterprise Edition – Version: 10.2.0.1 to 10.2.0.1
Linux x86
Oracle Server – Enterprise Edition – Version: 10.2.0.1 to 10.2.0.1
Intel Based Server LINUX

Goal

This document describes the installation of Oracle 10gR2 database on RedHat Enterprise Linux/Oracle Enterprise Linux 5 (x86).

NOTE: RAC customers are strongly advised to review Note 414163.1 too

Solution

Installation requirements:

1. Hardware Requirements

i) Physical Memory

At least 1024MB of physical RAM is required. To determine the physical RAM size, enter the following command

# grep MemTotal /proc/meminfo

ii) Swap Space

The following table describes the relationship between installed RAM and the configured swap space requirement.

RAM	Swap Space
Between 1024 MB and 2048 MB	1.5 times the size of RAM
Between 2049 MB and 8192 MB	Equal to the size of RAM
More than 8192 MB	0.75 times the size of RAM

To determine the size of the configured swap space, enter the following command

# grep SwapTotal /proc/meminfo

iii) Disk space Requirement

• 400 MB of disk space in the /tmp directory
• Between 1.5 GB and 3.5 GB of disk space for the Oracle software, depending on the installation type
• 1.2 GB of disk space for a preconfigured database that uses file system storage (optional)

To determine the amount of free disk space on the system, enter the following command

# df -h

2. Kernel Requirements

a) Version

The system must be running the following kernel version (or a later version)

2.6.18-8

To determine whether the required kernel is installed, enter the following command:

# uname -r

b) SELinux

Due to an internal bugs of Oracle database (Bug 6140224, Bug 6079461), SELinux has to be disabled on RHEL/OEL 5 to work with Oracle database 10.2. Please refer Note 454196.1 for more information about these bugs.
To check whether SELinux is Enabled or Disabled in a system, please refer Note 432988.1
To disable SELinux, please refer Note 457458.1

3. Installing required packages

The following packages are required to be installed to have a supported to installation. It is recommended to choose a “default list of RPMs” during installation as per Note 376183.1 ‘Defining a “default RPMs” installation of the RHEL OS’. Please note following points while installing these packages

a) The version numbers for these packages given below are the minimum version required. If you have the packages with higher versions than the versions mentioned below, you can install the higher version packages.

b) Some of the below packages might be already part of “Default RPMs installation”, and some of the packages might be already part of “base” installation of OS.

c) Some of the below packages may require other packages as dependencies. So, make sure that you have installed these packages including the dependency packages required.

binutils-2.17.50.0.6-2.el5
compat-libstdc++-33-3.2.3-61
elfutils-libelf-0.125-3.el5
elfutils-libelf-devel-0.125
gcc-4.1.1-52
gcc-c++-4.1.1-52
glibc-2.5-12
glibc-common-2.5-12
glibc-devel-2.5-12
glibc-headers-2.5-12
libaio-0.3.106
libaio-devel-0.3.106
libgcc-4.1.1-52
libstdc++-4.1.1
libstdc++-devel-4.1.1-52.e15
make-3.81-1.1
sysstat-7.0.0
unixODBC-2.2.11
unixODBC-devel-2.2.11

Note:

As per documentation Bug 7680459, the package libXp-1.0.0-8 is also required to invoke OUI successfully.

4. Set the kernel parameters

Add the following the lines in the file /etc/sysctl.conf

kernel.shmmni = 4096 
kernel.sem = 250 32000 100 128 
net.ipv4.ip_local_port_range = 9000 65500 
net.core.rmem_default = 1048576 
net.core.rmem_max = 1048576 
net.core.wmem_default = 262144 
net.core.wmem_max = 262144

To place these changes into effect, execute the command

# sysctl -p

5. Create Oracle user and groups

# groupadd oinstall 
# groupadd dba 
# useradd -g oinstall -G dba oracle 

# passwd oracle

6. Create the required directories for Oracle Base and change the ownership

For example 
# mkdir -p /u01/app/oracle 
# chown -R oracle:oinstall /u01/app/oracle

7. Set the session limits for Oracle user

Add the following lines to the /etc/security/limits.conf file

oracle soft nproc 2047 
oracle hard nproc 16384 
oracle soft nofile 1024 

oracle hard nofile 65536

Add the following line in the /etc/pam.d/login file

session required pam_limits.so

Add the following lines to the /etc/profile.

if [ $USER = "oracle" ]; then 
	ulimit -u 16384 
	ulimit -n 65536 
fi

8. Configure the Oracle user’s environment

Configuring the oracle User’s Environment, using the appropriate section of ”Oracle® Database Quick Installation Guide 10g Release 2 (10.2) for Linux x86″. Note that setting $ORACLE_BASE (not $ORACLE_HOME) in the environment will ease a couple of entries in the Oracle Universal Installer.

For example, if oracle user is using bash shell, and has the home directory as /home/oracle,
The following lines to be added to /home/oracle/.bash_profile
export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=orcl
unset ORACLE_HOME

9. Invoking OUI

Log in as Oracle user and start installation by any one of the following methods. This is required because Oracle 10.2 expects the OS to be RHEL 3 or RHEL/OEL 4.

Method 1:

$ ./runInstaller -ignoreSysPrereqs

Method 2:

Follow Note 456634.1

10. Warning at “Product-Specific Prerequisite Checks” screen

During the installation at the screen “Product-Specific Prerequisite Checks”, OUI gives a warning that Oracle database 10g is not certified on the current operating system. If we still proceed to continue, OUI gives a dialog-box with a warning
“Some recommended prerequisite checks have failed. You might get errors during installation. Do you want to proceed?”
This warning can be ignored, if all the above requirements are satisfied.

Change History:

Jan 19, 2009: Updated kernel parameter net.ipv4.ip_local_port_range as per correction request given by Oracle development team.

Feb 11, 2009: Updated with SELinux information.

Feb 27, 2009: Removed kernel parameter fs.file-max = 65536 , because the default value 101365 is more than 65536.

Mar 2, 2009: Removed kernel parameters net.ipv4.tcp_wmem and net.ipv4.tcp_rmem as they are not required. (Reference: unpublished Bugs 7647281 and 7647303)

Remote Diagnostic Agent (RDA) FAQ

One of the most popular Metalink notes.
You need this when you log certain Oracle Service Requests.

You have to take more from your Oracle Worldwide Support Services!

Subject:	Remote Diagnostic Agent (RDA) 4 – FAQ
	Doc ID:	330363.1	Type:	DIAGNOSTIC TOOLS
	Modified Date:	13-MAY-2009	Status:	PUBLISHED

RDA 4.x FAQ

RDA Documentation Links
Getting Started	FAQ You are here.	Man Pages	Troubleshooting Guide	Training	RDA Documentation Index

In This Document

Linux/Unix/Windows/MacOS Issues
What is the purpose of (RDA 4.x)? Is RDA required in order to resolve my SR? What is required to run RDA? How much space do I need to run RDA? What platforms does RDA support, what about OpenVMS? Where can I download RDA package? I don’t have Perl, where can I get a binary/compiled version RDA? How do I run a RDA collection? How do I force RDA to run Setup again? How long does it take to run a RDA collection? How do I force RDA to collect data again? Where is the RDA output located? Is there is a localized version of RDA 4.x? Will RDA 4 find the solution to my TAR? There are sections missing my RDA report, why?	What does the RDA-nnnnn error mean when I executed RDA? What does the RDA-00010 error mean? What does the RDA-00006 error mean? What does the OCM-09004 error mean? How to change the default parameters like SQL_TIMEOUT? Can RDA be setup without asking any questions? How can I reduce the size or amount of RDA output files? Can RDA be scheduled to run automatically via CRON or AT? How do I stop RDA from collecting everything? How do I verify the RDA package that I installed is not corrupted? RDA is taking a long time to run the setup process, why?	Why is RDA repeating the same title message several times during setup? When I run RDA it creates a “core dump” or segmention violation, what now? When I run RDA there is not .zip file in my [RDA_OUTPUT] directory, what now? How do I upgrade my RDA installation from the prior release? When I run the binary version of RDA it asks for newer libraries, what now? RDA is reporting incorrect timestamp’s of directory and file listing, why? Can I connect to a remote database using RDA? I am having difficulty running RDA 4. What do I do? How do I provide RDA output to Oracle Support? There are connection issues when attempting to connect to OID database SID? Why does RDA not collect database information when getting ORA-28002 or ORA-28011?
RAC/Cluster/Multi-Node Environments
See RAC Cluster Guide – Knowledge Article 359395.1
OCM_Related_issues
emCCR returns a KEY_MISMATCH error Data Collected by OCM is incorrect/invalid	Installation of OCM fails with “CCR” user already exists – ORA-20005 How to get OCM to connect successfully to Oracle	Knowledge Article 365734.1 – How To Change the CSI, Country code, Metalink_id or proxy details used by OCM

Frequently Asked Questions

1. What is the purpose of Remote Diagnostic Agent (RDA 4.x)?

RDA 4.x is designed to be platform independent; there is no longer a separate version for UNIX and Windows. RDA is able to collect the same data set as both the RDA 3.14.1 for UNIX and RDA 3.11.1 for Windows versions. RDA code is readable by the customer so that if there are any concerns about security, they can see exactly what is collected. Documentation is included with the RDA code so that customers can understand and troubleshoot RDA themselves. Also, the RDA documentation has been rewritten and is more comprehensive than in the past to give the customer (internal and external) better understanding.

2. Is RDA required in order to resolve my TAR?

We strongly encourage the use of RDA as it can greatly assist the analyst in resolving your TAR, it contain vast amount of configuration and diagnostic data that reduces the “round trip” time needed to resolve most issues.

RDA has a strong focus on robustness and supportability, in the rare case you are unable to use RDA, please update the tar to inform your analyst, and request that work on the TAR continue without the benefit of RDA. Oracle engineers are trained to work issues without the aid of RDA.

3. What is required to run RDA?

RDA 4.x is command line program written in Perl. It uses only basic Perl libraries so the customer should never have to install anything, as long as Oracle software is available. It uses the Perl libraries that are shipped with Oracle or shipped with some UNIX platforms. RDA has been certified and tested on Perl versions 5.005 – 5.10.x. If for some reason Perl is not available or is inaccessible on the customer environment, then a binary version is available for download on major platforms.

Additionally, RDA users need access to the 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 on UNIX (e.g. sar, top, vmstat, etc) or on Windows (WinMsd utility and (MSInfo32 on Windows 2000, Windows 2003). If you are running RDA to assist in resolving a tar, 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.

4. How much space do I need run RDA?

RDA 4.x does not require too much space on the host environment to be installed; 2 – 4 MB depending on the RDA package you decide to install from (.tar, tar.gz, or .zip) and whether you require the RDA binary version file.

Regarding RDA output directory requirements, we have observed 80% of the data collected will require less than 150 MB for the final output files generated. We would recommend you allocate at least 150MB to be on the safe side. Sometimes depending on the size of your data and trace 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. Review the your Oracle trace and log files sizes and calculate to have space to have copies of those files, formatted RDA html files, and a compressed RDA package.

5. What platforms does RDA support, what about OpenVMS?

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

• HP-UX (10.* and 11.*)
• HP Itanium
• HP OpenVMS Alpha 7.3-2 and above
• HP OpenVMS Itanium
• HP Tru64 UNIX
• IBM AIX
• IBM zSeries Based Linux
• Intel Linux (RedHat and SuSE)
• Linux PPC
• Linux Itanium
• Mac OS X/Darwin
• Microsoft Windows 2000 Workstation and Server
• Microsoft Windows 2003 Server
• Microsoft Windows Vista
• Microsoft Windows XP Professional
• Sequent Dynix/Ptx
• Sun Solaris (2.6 – 2.10)

RDA can also be run on other platforms that support Perl 5.005 and above, however, Oracle Support recommends testing them on a non-production server first, as their performance is unpredictable. For example, you will receive errors when RDA attempts to run utilities and commands that are not supported on those platforms

6. Where can I download the RDA package?

You can download RDA from Knowledge Article 314422.1, the Main RDA 4.x page. Select the RDA zip file for your platform by clicking on the related link. Each zip file contains all the files you need, so only choose one to download.

7. I don’t have Perl. Where can I get a binary or complied version of RDA?

rda.sh and rda.cmd do not assume that components containing Perl are installed. As long as RDA finds an ORACLE_HOME and Perl 5.005 and above in the path, it tries to locate itself where the library modules are and sets the environment appropriately. Therefore, we can run RDA even when perl -V does not work correctly and when @INC is not configured. In most recent builds, it also ignores Perl from the path when it can’t find some standard required Perl modules. That also prevents using a nonstandard miniperl environment. For performance reasons, we restrict the search to some well known areas.

If you still can’t run RDA because of problems with Perl, you can download the following binary version for your platform.

1. You must first ensure you have RDA downloaded and installed on the environment you will be running RDA (see Knowledge Article 314422.1 for the latest available version).
2. Select and download one of the below binary/compiled versions specific to your operating system where RDA will execute from.
   • The zip file below contains the following files specific to the platform:
     • readme.txt
     • rda_<platform shortname> –> Binary RDA for use on more recent operating systems. Windows binary has “.exe” extension (e.g. rda_win.exe)
     • rda_<platform shortname>56 –> Binary RDA with old libraries intended for use on older operating systems
3. Place the binary file from step 2 into the same directory as the “rda.pl” file is located from step 1. (e.g. Place rda_win.exe in the same directory as rda.pl).
4. To execute, enter “rda_<platform shortname> -S” rather than “rda.pl” or “rda.sh” or “rda.cmd”. (e.g. Execute rda_win.exe -S).

Note: PERL5LIB environment variable must not be set in order to use the following compiled binaries of rda.pl. Make sure your PERL5LIB does not point to any Perl installation.

Platform Specific Compile Agent (Binary) Versions (Released Mar, 9 2009)
Platform	Download File
Apple Mac OS X	Download Zip File
HP-UX PA-RISC (64-bit)	Download Zip File
HP Tru64 UNIX	Download Zip File
IBM AIX Based Systems (64-bit)	Download Zip File
IBM AIX Based Systems (32-bit)	Download Zip File
IBM AIX 4.3 Based Systems (64-bit)	Download Zip File
Linux x86	Download Zip File
Microsoft Windows (32-bit)	Download Zip File
Sun Solaris SPARC (64-bit)	Download Zip File

8. How do I run an RDA collection?

   The following published document detail how to run RDA. There are also, detailed instructions in the README_Unix.txt and README_Windows.txt files contained inside RDA packages.

   Remote Diagnostic Agent (RDA) 4.x – Overview – Knowledge Article 314422.1

9. How do I force RDA to run Setup again?

RDA will read the values it finds in “setup.cfg” first before requiring to answer any setup questions. You can force RDA to start the setup process again in two(2) ways.

1. Deleting or renaming the “setup.cfg” file

$ ./rda.pl -S or
c:\>rda.cmd -S or
$ ./rda.sh -S

2. Running the following RDA command:
   $ ./rda.pl -Sfn or
   c:\>rda.cmd -Sfn or
   $ ./rda.sh -Sfn

10. How long does it take to run RDA collection?

One common question people ask is “How fast does RDA run on Machine x”. This is a very subjective and complex answer that depends on several factors. In general, the processing power of the Machine/Server that RDA has be execute on. e.g. It will run faster on a 4 CPU machine versus a single CPU machine.

The availability of free system resources at the time RDA is going to execute. If you system is running at 95% CPU utilization, RDA will take much longer on that system versus a system where it is at 45% CPU utilization. Other factors like I/O and memory also play an important role.

The number of RDA modules that need to be executed. RDA is comprised of individual modules processing specific tasks from the Operating system (OS) to Database long files (LOG) collection. Each module can take a varying amount of time, depending on the amount of data that needs to be processed. In most RDA collections several modules are executing in sequence or parallel; depending on the operating system and modules in question. See Content modules for a full list of RDA modules.

Other potential areas that affect RDA collection the size of the files that need to be parsed by RDA ( 100kb vs 10000KB files). If there are possible operating system calls errors or malfunctions, cases such as: sometimes commands line netstat -an can take over 15 mins to process, or if the operating system TCP/IP setting (ip,hostname, or dns) has not been properly configured.

In general RDA has been configured with a timeout mechanism to avoid certain modules from taking too long to process. The default timeout mechanism has been set to thirty (30) seconds for each command and sql execution.

Therefore we cannot give exact numbers on how long RDA will take to execute on your environment. However, here are some averages:

• Based on 100 random real world customer RDA 4.x outputs uploaded to Support Services, average time was: 438 seconds or 7 mins.
• Based on internal tests.
  

  rda_4.1-051205
  Operating system:	RHEL AS 3.0 Update 4 – Kernel 2.4.21-20.ELsmp on an i686
  CPU:	1 x Xeon 2.80GHz
  Memory:	1536 MB
  Database:	Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 – Production With the Partitioning, OLAP and Data Mining options
  Memory (Free)	total used free shared buffers cached Mem: 1538936 1495756 43180 0 98844 726916 -/+ buffers/cache: 669996 868940
  Disks:	2 x 73GB 7200RPM SCSI drives
  Load (top):	16:30:46 up 355 days, 1:06, 1 user, load average: 0.10, 0.30, 0.20 141 processes: 127 sleeping, 1 running, 13 zombie, 0 stopped
  RDA modules executed:	Processing Initialization module … Processing CFG module … Processing Sampling module … Processing OS module … Processing PROF module … Processing PERF module … Processing NET module … Processing networking information (pings, network files, sql*net files)… Listener and network checks may take a few minutes. please be patient… Processing listener status, services and log for listener LISTENER Processing Oracle installation module … Processing RDBMS module … Processing LOG module … Processing Web Server module … Processing Web Server (iAS 1.x) module … Processing HTTP Server … Processing End module …
  Average execution Time(seconds) over 5 runs:	357 seconds or ~6 minutes

11. How do I force RDA to collect data again?

RDA will read the values it finds in <output> directory first, if it finds data that has not been collected it will attempt to collect that data. However if you want RDA to collect data again you can execute the following commands. (-v: verbose , C: collect , R: render into html, P: Package contents of output directory into archive, and f : force execution of all commands.

$ ./rda.sh -vCRPf or
c:\>rda.cmd -vCRPf or
$ ./rda.pl -vCRPf or
$./rda.sh -vf or
c:\>rda.cmd -vf

Finally, if you need to run data collection for specific module again you can just run the following command (OS: Operating system and DBA: Database)

$ ./rda.sh -vCRP OS DB or
c:\>rda.cmd -vCRP OS DBA

Note: The collection process will execute must faster and you will not receive any notices when it completes. This also overwrite the data in the output directory.

12. Where is the RDA output located?

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

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 (note: file name varies, but ends in .zip, .tar, .tar.gz, or .tar.Z ; e.a: 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.

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 the files in the <output_directory> manually and send them to Oracle Support. You can use a packaging/archive utility program like http://www.info-zip.org/Zip.html to perform this task.

13. Is there a localized version of RDA 4.x?

No. Currently this utility is written in the English language only, including the built in documentation. In the future, Oracle might offer other versions based on customer demand.

14. Will RDA 4 find the solution to my TAR?

No. RDA is a tool to assist in resolving the customer’s issue. As stated above, RDA’s purpose is to collect accurate information about the Oracle installation environment. In many cases RDA will collect information that you think is unnecessary, but may be needed during a later stage of the tar or by the next person reviewing the tar (e.g. escalations, development, etc.). In its first phase, RDA is a collection agent. The long term plan is that the next phase RDA will involve a diagnostic component to analyze the information collected.

15. There are sections missing my RDA report, Why ?

There may be some confusion about RDA 4.x not displaying all reports or sections. A new feature was added to improve search efficiency and consistency between modules; if a report or section does not contain information, then it is not generated. This was already the case for some modules in the previous version of RDA 3.14.x and it has been generalized. Another factor for this change is that relevant directories could be different from platforms (e.g. UNIX >< Windows) and can even differ between RAC and non-RAC environments.

If an unexpected error (e.g. table not found) is encountered, it will be visible in the reports. Some results can be incomplete due to execution timeouts. An overview of SQL execution is provided in the Report Settings report.

SQL Request Overview
Module	Requests	Errors	Time-Out	Skipped	Comment
S000INI	1	0	0	0	SQL execution limited to 30s
S110PERF	3	0	0	0
S125GTW	1	0	0	0
S200DB	34	0	0	0	SQL execution limited to 30s
S300IAS	2	0	0	0	SQL execution limited to 30s
S400RAC	3	0	0	0

On another hand, the absences of optional components are not considered as an error. The error explanation (-E) and the readme indicates how to change SQL_TIMEOUT and other timeout parameters.

16. What does the RDA-nnnnn error mean when I executed RDA?

RDA has a built in error handling mechanism. You can discover the meaning and possible solution to the error by using the following RDA command line argument: rda.pl -E {ERROR #} . i.e:

 
$ ./rda.pl -E RDA-14
RDA-00014: %d issue(s) found in the RDA installation
*Cause: issues encountered when checking the RDA installation
*Action: A fresh install of RDA is probably the easiest way to fix file
alteration problems. However, permission problems can result from
other factors:
- If RDA has been installed by another user, the default settings
could be too restrictive. Please review the local context with
that user and your appropriate local authorities.
- Execution permissions can be missing on startup scripts for other
platforms and when perl is not available from the command line.

17. What does the RDA-00010 error mean?

This error generally occurs when running a RDA binary (i.e. rda_win.exe, rda_sunos, etc). This error generally is followed by lines containing “PERL2EXE_STORAGE” and or a Perl *.pm module name.

Cause: The following OS environment variable PERL5LIB is set or your OS environment points to Perl installation. This causes a conflict for the RDA binary to located needed Perl modules binded within the executable.

Action: Unset PERL5LIB environment variable.

18. What does the RDA-00006 error mean?

This error generally occurs when RDA cannot find a suitable archiving program like zip or tar in your OS path. You can do two(2) things to resolve this issue: (1) Make you your OS PATH variable contains the location to a “command line” zip or tar program. (2) You can manually compress the RDA <output_directory> directory and sent that to Oracle Support.

See: $ ./rda.pl -E RDA-00006 or c:\>rda.cmd -E RDA-00006
Cause: No packaging tool paths have been found.
Action: The path to zip or tar commands has not been collected. This typically occurs when the end report has not yet been produced or if these commands have not been found in the current execution path (cf. PATH).

19. What does the OCM-09004 error mean?

This error generally mean that certain pre-requisites for OCM installation failed. The details of the failed prerequisites can be retrieved in the ‘Report Setting’ section of RDA output and that more info can be obtained with -E option

See: $ ./rda.pl -E OCM-09004 or c:\>rda.cmd -E OCM-09004
OCM-09004: Java Development Kit not found
*Cause: Java neither found under the $ORACLE_HOME/jdk directory nor
JAVA_HOME is set.
*Action: Set JAVA_HOME to a JDK that is 1.2.2 or greater and re-run the OCM
module setup.

20. How to change the default parameters like SQL_TIMEOUT?

There a few ways to set the default parameters in RDA, which useful for certain situations. Changing it temporarily from the command line using “-e” switch (SEE RDA MAN PAGE ) or using built in help system (rda.pl -h):

/rda.pl -vCRP -e SQL_TIMEOUT=120,SQL_ATTEMPTS=25

This command collects, generates the new reports and provides up to 120 seconds for executing each SQL statement and retries to execute the same sql statement 25 times temporarily for this time only.

Modifying the setup.cfg file using a text editor like “vi” or “wordpad”. You will find section with setup.cfg file that relates to timeout (#N.SQL timeout (in seconds)).
Changing it more permanently from the command like using setup level “-p” switch (SEE RDA MAN PAGE ) or using built in help system (rda.pl -h):]

./rda.sh -S -p advanced DB or
rda.cmd -S -p advanced DB or
rda.pl -S -p advanced DB

Turns on advanced setup prompts, more settings can be specified interactively by using the advanced level.

21. Can RDA be setup without asking any questions?

Yes. RDA was designed to determine values to setup question from the customer OS environment. You can also, pass all default parameters and values in a single command like this:

$ ./rda.pl -Sy -e SQL_SYSDBA=1,SQL_LOGIN=/ OS INST DB

22. How can I reduce the size or amount of RDA output files?

RDA has 2 mechanisms to reduce the size and quantity of trace and log files it collects and packages in the output file. The first is built into the advance setup profiles of the various collection modules, specifically for the (INI, DB, WEBC, IAS, GTW, RAC) modules and can be called using the following command(s):

$ rda.sh -S -p expert INI DB or
rda.cmd -S -p expert INI DB or
rda.pl -S -p expert INI DB

The second is using the using “-e” switch when calling RDA (rda.sh, rda.cmd, or rda.pl):

See modules: DB:[DB_TRACE_AGE, ALERT_TAIL], INI:[RDA_TAIL], GTW:[GATEWAY_TAIL], IAS:[DCM_TAIL], WEBC:[WEBCACHE_TAIL], RAC:[CLUSTER_TAIL]

./rda.sh -e DB_TRACE_AGE=5, ALERT_TAIL=1000 or
./rda.cmd -e DB_TRACE_AGE=5, ALERT_TAIL=1000

23. Can RDA be scheduled to run automatically via CRON or AT?

Yes. You need to check with your OS documentation regarding running scheduling applications like CRON and AT. Please note you may need purge RDA log files in the <output> directory and encode passwords in your setup.cf when required.

24. How do I stop RDA from collecting everything?

RDA has been designed to reduce the number of setup questions and also can be called to only collect certain modules. RDA will call any dependent modules first; for instance OS module will always call the INI module. The list of available diagnostic modules is located here Module Man Pages or can be listed by executing (rda.pl -L):

$./rda.sh -vCRP OS PERF DBA LOG BR RAC or
C:\>rda.cmd -vCRP OS PERF DBA LOG BR RAC or
$./rda.pl -S -p advanced OS or
$ ./rda.pl -S -p advanced INI DB RAC

25. How do I verify the RDA package that I installed is not corrupted?

RDA has an internal file verification system to handle most types of file corruptions, as long as ./rda.pl, rda.sh, or rda.cmd can be called or not corrupt themselves: It can be run using the following command.

$ ./rda.sh -vc or
C:\>rda.cmd -vc or
$ ./rda.pl -vc

26. Why is RDA taking a long time to run the setup process?

Command execution can be slow to start when there are difficulties to detect the TCP/IP domain of the server that RDA is running on. The setup process can takes several minutes in some circumstances. The RDA_DOMAIN environment variable can avoid that.

On UNIX:

$RDA_DOMAIN=oracle.com ./rda.sh -S or
$RDA_DOMAIN=oracle.com
$export RDA_DOMAIN
$./rda.sh -S

On Windows:

C:\set\RDA_DOMAIN=oracle.com
C:\rda.cmd -S

27. Why is RDA repeating the same title message several times during setup?

Sometimes a situation will occur where a user will notice a message like ” S000INI: Initializes the Data Collection” being repeated over and over and without additional new messages.

If you see such strange behavior during setup process, than your current Perl installation contains Perl modules (.pm) that have been corrupted, please either install a newer Perl installation or use the RDA binary version.

28. When I run RDA it creates a “core dump” or segmentation violation, what now?

There could be several things that could cause an OS “core dump”(you local Perl installation is core dumping, the OS libraries/modules is not correctly configured, or JAVA is crashing.

1. Run a few simple tests to verify that Perl is working:
   ./rda.pl -h (does this work?)
   ./rda.pl -c (does this work?)
   ./rda.pl -V (does this work?)
2. If any of the above steps cause a hang or core dump, than your current Perl installation contains Perl modules (.pm) that have been corrupted, please either install a newer Perl installation or use the RDA binary version
3. Test where the failure is occurring during data collection.
   rda.sh -vdt or
   rda.cmd -vdt This command will run a debug and trace of all the commands executed, and will let you see when it core dumps)
4. Provide the output of the above command to Oracle Support for Review.

Note: rda.pl can be substituted by rda.sh on UNIX and rda.cmd on Windows.

29. When I run RDA there is not .zip file in my [RDA_OUTPUT] directory, what now?

    The final output directory may not contain an archive file (.zip, .tar, tar.gz, or tar.Z ) due to problems finding a compression program in your OS environment PATH. If the archive is missing, please archive all the files in the <output_directory> manually using a compression program like zip or Tar and send that to Oracle Support. You can use a packaging/archive utility program like http://www.info-zip.org/Zip.html to perform this task.

30. How do I upgrade my RDA 4.x installation from the prior release?

The most simplest and reliable way to upgrade your RDA installation is delete or move your old installation to a new location. Then install the new release into the location you had the prior release installed. If you want to reuse you old setup.cfg file, you can place the older version into the new <RDA> directory and it will try to upgrade your setup.cfg to the new features.

31. When I run the binary version of RDA it asks for newer libraries, what now?

The binary or compiled build of RDA were designed to have fewest library dependencies as possible. However, certain required libraries like libpthread maybe required. Additional binary versions of RDA has been build using older OS library versions for customer who have problems with our Primary binary version of RDA. Use the following secondary builds of RDA ONLY IF you have problems with primary RDA binary builds.

Knowledge Article 335791.1 – Secondary Binary RDA version

32. Why is RDA reporting incorrect timestamp’s of directory and file listing?

If you noticed that notice the times listed under the Listing of Files from Oracle
Log/Trace Directories are off by 5 or 7 hours, it is because RDA reports all times in GMT instead of local time zone. Also you will notice there are 2 dates in the directory listing, it because they represent:

ctime    inode change time in seconds since the epoch
mtime    last modify time in seconds since the epoch

33. Can I connect to a remote database using RDA?

It is recommended that RDA should be run from the local database ORACLE_HOME for the database collection is needed.  RDA uses the local “sqlplus” executable to perform database collection. In order for “sqlplus scott/tiger@remote_db as sysdba” format to work, you need to setup a sqlnet password file for the remote database.  We don’t recommend a remote connection to the database for the following reasons:

• A remote connection may only be allowed to collect the database information.
• Some modules may not have permissions to collect local files.
• No confirmation about what environment you have connected to.

That being said, RDA supports the following combinations which can be used for SQL_LOGIN/SQL_SYSDBA values:

user
user AS SYSDBA
user@db
user@db AS SYSDBA
/
/ AS SYSDBA
/@db
/@db AS SYSDBA

No password will be required for all ‘/’ combinations.

If @db format is specified and the db must be identical to ORACLE_SID enviroment variable, RDA will assume that the TNS entry needs to be used instead of ORACLE_SID (and ORACLE_SID is removed from the environment).

34. I am having difficulty running RDA 4. What do I do?

Note: If you have issues with the Remote Diagnostic Agent (RDA) , please file a Service Request via My Oracle Support by selecting OSS Support Tools from the product list of values on the Create a SR screen (OSS is above Oracle in the product list). Then select the “*Remote Diagnotic Agent (RDA) Issue*” as the problem type and complete the SR. Please 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/she 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 issues resolved so that it can be used to help speed up resolution for future technical issues.

35. How do I provide RDA output to Oracle Support?

RDA Output can be provided to Oracle Support when creating a Service Request or Updating a Service Request.  During Service Request creation, the second step allows you to upload files that may be needed by Oracle Support. 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.).  During a Service Request Update, click on the link “upload to support”.  You can attach your RDA Output in the new window that opens.  Remember to upload your RDA output as a separate file.

 
@ ORACLE EMPLOYEES: 
@ View existing defect issues and enhancement requests  
@ via the bug database or WebIV.Log defects and 
@ enhancements using the SE Defect and Enhancement 
@ Logging Process. File bugs using Product 1330, 
@ Component RDASYS, make sure to choose E - email
@ notification. Additionally, you can file a TAR 
@ using product 1330, comp RDASYS and assign it to 
@ QUEUE: GRIDTOHUB

36. There are connection issues when attempting to connect to OID database SID.

OID SID DB information is required in the “host:port::ServiceName” format, for the prompt “Enter the OID database SID”. If you do not want RDA to collect information from the database, then enter a dash (‘-’).

37. Why does RDA not collect database information when getting ORA-28002 or ORA-28011?

RDA considers all ORA and SP2 errors at connection time are connection problems and stop collecting database information

.

Where’s Alert.log in Oracle11g?

In Oracle11g with the introduction of the dreadfull ADRCI people lost track of where the heck is ALERT.LOG? Is it true that ALERT.LOG is no longer in text mode? Is it true that I’ll have to buy an XML book to read my DB messages?  STOP THE PANIC!

You have two logs now: one for humans like you that have to see it in text, and other for the EM to bring up in a beatifull tabled format (log.xml).

[oracle@linux trace]$ pwd
/u01/app/oracle/diag/rdbms/orcl/orcl/trace

[oracle@linux trace]$ ll alert_orcl.log
-rw-r—–  1 oracle oinstall 24219 Abr 29 11:00 alert_orcl.log

[oracle@linux trace]$ ll ../alert/log.xml
-rw-r—–  1 oracle oinstall 378669 Abr 29 11:00 ../alert/log.xml

DROP DATABASE in SQL*Plus

Here is how you wack a database in less than no time!

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 146472960 bytes

Fixed Size 1311940 bytes

Variable Size 92277564 bytes

Database Buffers 50331648 bytes

Redo Buffers 2551808 bytes

Database mounted.

SQL> drop database;

drop database

*

ERROR at line 1:

ORA-12719: operation requires database is in RESTRICTED mode

SQL> alter system enable restricted session;

System altered.

SQL> drop database;

Database dropped.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

What does SMON is for? (or how Startup Immediate not always shuts downs on-going transactions)

You really think I’m going to repeat all the Oracle documentation that explains what SMON is for?

The code capture speaks for itself and explains the post title -;)

Note: Don’t try this at the office! … you should never create tables as SYS user!

SQL> create table dummy_table (n number); 

Table created.

SQL> insert into dummy_table values (18);

1 row created.

SQL> shutdown immediate
ORA-01097: cannot shutdown while in a transaction - commit or rollback first
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  523108352 bytes
Fixed Size                  1314492 bytes
Variable Size             427819332 bytes
Database Buffers           92274688 bytes
Redo Buffers                1699840 bytes
Database mounted.
Database opened.
SQL> select * from dummy_table;

no rows selected

SQL> --darn it! where's my row? That SMON dude took it away ...

pfile (init.ora) or spfile?

3 things every dba should know about pfile (init.ora) and spfile:

- always use spfile (you loose some features if you don’t)

- if you have pfile and spfile both at the $ORACLE_HOME/dbs (x servers) or %ORACLE_HOME%\database (windows), then Oracle will prefer spfile (if you haven’t already messed up the spfile binary formating, for instance by opening it with a text editor)

- if your database is up and running and you don’t know what file have Oracle picked up just do SHOW PARAMETER SPFILE at SQL*Plus and if it’s blank it means you have started up your instance with PFILE (init.ora).