Sridhar's oracle page

How to know your Oracle database is 32bit or 64bit?

2010-04-13T14:59:00.003+05:30

Method 1:
oracle >cd $ORACLE_HOME/bin
oracle >file oracle
oracle: ELF 32-bit MSB executable SPARC Version 1, dynamically linked, not stripped

Method 2:

oracle >sqlplus "/ as sysdba"

SQL*Plus: Release 9.0.1.4.0 - Production on Tue Apr 13 09:21:49 2010

(c) Copyright 2001 Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.5.0 - Production
With the Partitioning option
JServer Release 9.0.1.4.0 - Production

SQL> select
length(addr)*4 || '-bits' word_length
from
v$process
where
ROWNUM =1; 2 3 4 5 6

WORD_LENGTH
---------------------------------------------
32-bits

The third and the simplest one is while you connect to your database if you find any mentioning of the Platform it is 64bit and if not its 32 bits ;)

64 BITS:

oracle >sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Apr 13 05:35:30 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

32BITS:

oracle >sqlplus "/ as sysdba"

SQL*Plus: Release 9.0.1.4.0 - Production on Tue Apr 13 09:21:49 2010
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.0.1.5.0 - Production
With the Partitioning option
JServer Release 9.0.1.4.0 - Production

TNS-00516: Permission denied, Solaris Error: 13: Permission denied

2009-09-08T11:02:00.001+05:30

Last week our Unix Adminstrators has moved the data related to our Oracle database from a UFS filesystem to a ZFS filesystem on Solaris.
Once the move is completed, when we tried to start the databases and listeners on the server, the databases started normally but we got problems starting the Listeners.

The Problem:
DBA TEST ZONE> lsnrctl start LISTENER_ORA8

LSNRCTL for Solaris: Version 8.0.6.0.0 - Production on 07-SEP-09 10:52:35

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Starting /usr/app/oracle/product/8.0.6/bin/tnslsnr: please wait...

TNSLSNR for Solaris: Version 8.0.6.0.0 - Production
System parameter file is /usr/app/oracle/product/8.0.6/network/admin/listener.ora
Log messages written to /usr/app/oracle/product/8.0.6/network/log/listener_ora8.log
Attempted to listen on: (DESCRIPTION=(CONNECT_TIMEOUT=10)(ADDRESS=(PROTOCOL=IPC)(KEY=test8)))
TNS-12546: TNS:permission denied
TNS-12560: TNS:protocol adapter error
TNS-00516: Permission denied
Solaris Error: 13: Permission denied

The Fix:
The Unix permissions for the hidden directory /tmp/.oracle should be:
drwxrwxrwx

Change the permissions on the .oracle directory:

DBA TEST ZONE> ls -lad /tmp/.oracle /var/tmp/.oracle
/tmp/.oracle: No such file or directory
drwxr-xr-x 2 root root 2 Aug 21 02:05 /var/tmp/.oracle

give full permisions to this file:

DBA TEST ZONE> ls -lad /var/tmp/.oracle
drwxrwxrwx 2 root root 2 Aug 21 02:05 /var/tmp/.oracle

Now again retried to start the Listener:

DBA TEST ZONE> lsnrctl start LISTENER_ORA8

LSNRCTL for Solaris: Version 8.0.6.0.0 - Production on 07-SEP-09 10:57:49

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Starting /usr/app/oracle/product/8.0.6/bin/tnslsnr: please wait...

TNSLSNR for Solaris: Version 8.0.6.0.0 - Production
System parameter file is /usr/app/oracle/product/8.0.6/network/admin/listener.ora
Log messages written to /usr/app/oracle/product/8.0.6/network/log/listener_ora8.log
Listening on: (ADDRESS=(PROTOCOL=ipc)(DEV=8)(KEY=test8))
Listening on: (ADDRESS=(PROTOCOL=tcp)(DEV=13)(HOST=10.52.51.61)(PORT=1526))

Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=test8))
STATUS of the LISTENER
------------------------
Alias LISTENER_ORA8
Version TNSLSNR for Solaris: Version 8.0.6.0.0 - Production
Start Date 07-SEP-09 10:57:52
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /usr/app/oracle/product/8.0.6/network/admin/listener.ora
Listener Log File /usr/app/oracle/product/8.0.6/network/log/listener_ora8.log
Services Summary...
test8 has 1 service handler(s)
The command completed successfully

Delete files of a particular date.

2009-06-22T16:26:00.003+05:30

Quick and efficient.

Remove -> ls -ltr | grep "May 23" | awk '{print "rm "$9" /disk1/oradata/arch/"}' | more

above command will list all the files that are having May 23 date as below:

rm orcl_R676045126_T1_S29396.arc.gz
rm orcl_R676045126_T1_S29397.arc.gz
rm orcl_R676045126_T1_S29398.arc.gz
rm orcl_R676045126_T1_S29399.arc.gz
rm orcl_R676045126_T1_S29400.arc.gz
rm orcl_R676045126_T1_S29401.arc.gz
rm orcl_R676045126_T1_S29402.arc.gz
rm orcl_R676045126_T1_S29403.arc.gz
rm orcl_R676045126_T1_S29404.arc.gz

Command for actually doing the job i.e delete files:

Remove -> ls -ltr | grep "May 23" | awk '{print "rm "$9" /disk1/oradata/arch/"}' | sh –x

Use this carefully. Check twice before issuing the command, because sh –x will execute the output. i.e delete all files without a prompt.

Changing the archivelog destination online.

2009-04-22T12:46:00.002+05:30

Couple of days back received a high priority ticket developers saying not able to connect to database

hard working people they work on saturdays and sundays and make us also do the hard work :(

they are getting an error ORA-00257: archiver error. Connect internal only, until freed.

Found the following error in the alertlog file:

Sat Apr 11 05:20:00 2009
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive thread 1 sequence 2029 (4)

When I checked for details from the database I can see the following:

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 devdb2
sun-boxdb1
10.2.0.2.0 10-APR-09 OPEN NO 1 FAILED ARCHIVE LOG
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /mounts/devdb2_arch/devdb2
Oldest online log sequence 2029
Next log sequence to archive 2029
Current log sequence 2031

Thought may be the archive destination mount is full but shocked coz I cannot see the mount point.
Then I realized the mistake that I not 'I' "they" made the earlier day when we moved the database to the new Sun box.

Our Storage guys forgot to mount the archive Mount point.
I actually prefer seperate mount points for Data,Arch and Temp :)

Dont ask me why I did not checked when I was making the database up..its a friday evening guys...
But did not know how the database was up without the archive mount point destination specified.

So for immediate resolution had to change the archive destination "the day is SATURDAY and storage guys available on saturday??? no way!!! don't look for them"

Did the following:

SQL> alter system set log_archive_dest_1='LOCATION=/mounts/devdb2_temp/devdb2/arch/';

System altered.

SQL> alter system archive log all;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /mounts/devdb2_temp/devdb2/arch/
Oldest online log sequence 2031
Next log sequence to archive 2033
Current log sequence 2033
SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 devdb2
sun-boxdb1
10.2.0.2.0 10-APR-09 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO

SQL>

And you can see that all the errors are cleared in the alertlog file:

Sat Apr 11 05:20:03 2009
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive thread 1 sequence 2029 (4)
Sat Apr 11 05:20:06 2009
ALTER SYSTEM SET log_archive_dest_1='LOCATION=/mounts/devdb2_temp/devdb2/arch/' SCOPE=BOTH;
Sat Apr 11 05:20:07 2009
Archiver process freed from errors. No longer stopped
Sat Apr 11 05:20:09 2009
Thread 1 advanced to log sequence 2032
Current log# 3 seq# 2032 mem# 0: /mounts/devdb2_data/devdb2/redo03.log
Sat Apr 11 05:20:29 2009
Thread 1 advanced to log sequence 2033
Current log# 1 seq# 2033 mem# 0: /mounts/devdb2_data/devdb2/redo01.log
~

Happies endingss

Monitoring Temp Tablespace Use

2009-01-29T12:59:00.003+05:30

This query shows the current usage of the temporary tablespace.

This is very useful to monitor if you are getting ORA-1652: unable to extend temp segment errors, as well as for monitoring the use of space in your temp tablespace.

select sess.USERNAME, sess.sid, sess.OSUSER, su.segtype,
su.segfile#, su.segblk#, su.extents, su.blocks
from v$sort_usage su, v$session sess
where sess.sql_address=su.sqladdr and sess.sql_hash_value=su.sqlhash ;

You can compare the EXTENTS and BLOCKS columns above to the total available segments and blocks by querying V$TEMP_EXTENT_POOL.

select * from v$temp_extent_pool;

The EXTENTS_CACHED are the total number of extents cached -- following an ora-1652, this will be the max number of extents available. The EXTENTS_USED column lets you know the total number of extents currently in use.

In a TEMPORARY temporary tablespace (ie. one with a TEMPORARY datafile), all the space in the datafile is reserved for temporary sort segments. (In a well-managed database, this is of course true for non-TEMPORARY temp tablespaces as well).

Extent management should be set to LOCAL UNIFORM with a size corresponding to SORT_AREA_SIZE. In this case, it's easy to see how many temp extents will fit in the temporary tablespace:

SELECT dtf.file_id, dtf.bytes/dt.INITIAL_EXTENT max_extents_allowed
from dba_temp_files dtf, dba_tablespaces dt
where dtf.tablespace_name='TEMPORARY_DATA' and
dtf.tablespace_name=dt.TABLESPACE_NAME;

To know the corresponding Temp tablespace - datafile name and Total bytes allocated:

select name,bytes/1024/1024 from v$tempfile;

To know the exact Temp tablespace details like name, total blocks allocated,used blocks and free blocks:

select tablespace_name,(total_blocks*8)/1024,(used_blocks*8)/1024,(free_blocks*8)/1024 from v$sort_segment;

Resizing Logfiles

2009-01-29T12:05:00.002+05:30

The best way to resize logfiles is by creating new logfile groups in the new size, then dropping the old logfile groups.

Example:

SYS> select group#,member from v$logfile;

GROUP# MEMBER
---------- ----------------------------------------

1 D:\DATABASE\TRN3\LOGTRN3_1A.LGF
1 F:\DATABASE\TRN3\LOGTRN3_1A.LGF
2 D:\DATABASE\TRN3\LOGTRN3_2A.LGF
2 F:\DATABASE\TRN3\LOGTRN3_2A.LGF
3 D:\DATABASE\TRN3\LOGTRN3_3A.LGF
3 F:\DATABASE\TRN3\LOGTRN3_3A.LGF

6 rows selected.

SYS> alter database add logfile group 4
2 ('d:\database\trn3\logtrn3_4a.lgf','f:\database\trn3\logtrn3_4b.lgf')
3* size 10240K
SYS> /

Database altered.

SYS> alter database add logfile group 5
2 ('d:\database\trn3\logtrn3_5a.lgf','f:\database\trn3\logtrn3_5b.lgf')
3* size 10240K
SYS> /

Database altered.

SYS> alter database add logfile group 6
2 ('d:\database\trn3\logtrn3_6a.lgf','f:\database\trn3\logtrn3_6b.lgf')
3* size 10240K
SYS> /

Database altered.

SYS> select group#, status from v$log;

GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 INACTIVE
4 UNUSED
5 UNUSED
6 UNUSED

6 rows selected.

SYS> alter database drop logfile group 2;

Database altered.

SYS> alter database drop logfile group 3;

Database altered.

SYS> alter system switch logfile;

System altered.

SYS> select group#, status from v$log;

GROUP# STATUS
---------- ----------------
1 ACTIVE
4 CURRENT
5 UNUSED
6 UNUSED

SYS> alter system switch logfile;

System altered.

SYS> select group#, status from v$log;

GROUP# STATUS
---------- ----------------
1 INACTIVE
4 ACTIVE
5 CURRENT
6 UNUSED

SYS> alter database drop logfile group 1;

Database altered.

SYS> select group#, status from v$log;

GROUP# STATUS
---------- ----------------
4 INACTIVE
5 CURRENT
6 UNUSED

Tarballs

2009-01-16T08:24:00.000+05:30

To collect a bunch of files and directories together, use tar.

For example, to tar up your entire home directory and put the tarball into /tmp, do this

$ cd ~srees
$ cd .. # go one dir above dir you want to tar
$ tar cvf /tmp/srees.backup.tar srees

By convention, use .tar as the extension. To untar this file use

$ cd /tmp
$ tar xvf srees.backup.tar

tar untars things in the current directory!

After running the untar, you will find a new directory, /tmp/srees, that is a copy of your home directory.
Note that the way you tar things up dictates the directory structure when untarred.
The fact that I mentioned srees in the tar creation means that I'll have that dir when untarred.

In contrast, the following will also make a copy of my home directory, but without having a srees root dir:

$ cd ~srees
$ tar cvf /tmp/srees.backup.tar *

It is a good idea to tar things up with a root directory so that
when you untar you don't generate a million files in the current directly.

To see what's in a tarball, use

$ tar tvf /tmp/srees.backup.tar

Most of the time you can save space by using the z argument.

The tarball will then be gzip'd and you should use file extension .tar.gz:

$ cd ~srees
$ cd .. # go one dir above dir you want to tar
$ tar cvfz /tmp/srees.backup.tar.gz srees

Unzipping requires the z argument also:

$ cd /tmp
$ tar xvfz srees.backup.tar.gz

If you have a big file to compress, use gzip:

$ gzip bigfile

After execution, your file will have been renamed bigfile.gz.

To uncompress, use

$ gzip -d bigfile.gz

To display a text file that is currently gzip'd, use zcat:

$ zcat bigfile.gz

nohup Execute Commands After You Exit From a Shell Prompt

2009-01-16T07:37:00.002+05:30

Most of the time you login into remote server via ssh. If you start a shell script or command and you exit (abort remote connection), the process / command will get killed. Sometime job or command takes a long time. If you are not sure when the job will finish, then it is better to leave job running in background. However, if you logout the system, the job will be stopped. What do you do?

nohup command

Answer is simple, use nohup utility which allows to run command./process or shell script that can continue running in the background after you log out from a shell:

nohup Syntax:
nohup command-name &

Where,

command-name : is name of shell script or command name. You can pass argument to command or a shell script.

& : nohup does not automatically put the command it runs in the background; you must do that explicitly, by ending the command line with an & symbol.

nohup command example

Eg.
# nohup exp system/password@dbname file=exp_dbname_date.dmp log=exp_dbname_date.log full=y &

or

# vi exp_script.sh
exp system/password@dbname file=exp_dbname_date.dmp log=exp_dbname_date.log full=y
:wq

# ls –l
Check the permissions if that has execute permissions
#chomod 755 exp_script.sh

# nohup ./exp_script.sh &

this ensures of the completion of the Job even if the session terminates... :-)

Checking the Job progress cab be done using:

tail -f nohup.out
ctrl c -- to end the checking.

Some more good options for completing jobs without or when session terminations occur:

Option 1:

You can use the below script to keep your session alive with a simple while loop to run infinite loop.

#while true
>do
>echo " Exp going on"
>sleep 200
>done

Here you will not get prompt till you press Ctl + c (^c). This process will keep your session alive till you press enter.

Option 2:

You can run exp_script.sh script to queue (one minute) later execution:

$ echo "exp_script.sh" | at now + 1 minute

Hope this helps.

To calculate the export dump file size without actually exporting in Oracle 10G and above

2009-01-16T07:11:00.002+05:30

Many times DBAs' may need to know the size of the export dump file size before starting the actual export of the database. This has become easy with the datapump option from Oracle version 10G.

Just give the following command:

expdp system full=y ESTIMATE_ONLY=Y NOLOGFILE=Y

for more options see

expdp help=y -- from your command prompt.

Automtic and Manually sized Components of SGA

2008-12-24T06:52:00.001+05:30

DB_CACHE_SIZE,
SHARED_POOL_SIZE,
LARGE_POOL_SIZE and
JAVA_POOL_SIZE are automatic sized components.

The parameter SGA_TARGET specifies the total size of all SGA components.
If SGA_TARGET is set to a value greater than zero then above components are automatically sized.

On the other hand,

LOG_BUFFER,
BUFFER_POOL_KEEP,
BUFFER_POOL_RECYCLE,
DB_nK_CACHE_SIZE other than DB_CACHE_SIZE,
STREAMS_POOL_SIZE ,
Fixed SGA and
other internal components are manually sized components.

However setting the value to manually sized components automatically reduce the value from SGA_TARGET if SGA_TARGET is set to value greater than zero.

When SGA_TARGET>0 then automatic sized components of SGA are automatically allocated and this is called Automatic Shared Memory Management.

Both the automatic and manually sized components of SGA are dynamic components because they dynamically can be changed.

You can see them from V$SGA_DYNAMIC_COMPONENTS.

Simple Restore and Recovery process using RMAN on a Windows box.

2008-12-24T06:42:00.000+05:30

C:\>set oracle_sid=SDR

C:\>sqlplus /nolog

SQL*Plus: Release 9.2.0.4.0 - Production on Wed Dec 17 05:16:07 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn sys/sys as sysdba
Connected to an idle instance
SQL> startup pfile=c:\oracle\database\initSDR.ora nomount
ORACLE instance started.

Total System Global Area 202448548 bytes
Fixed Size 454308 bytes
Variable Size 159383552 bytes
Database Buffers 41943040 bytes
Redo Buffers 667648 bytes
SQL>

C:\oracle\bin>rman

Recovery Manager: Release 9.2.0.4.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

RMAN> connect target sys/sys

connected to target database: SDR (not mounted)

RMAN>

RUN
{
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\Backup\RMAN\SDR\%F';
RESTORE CONTROLFILE FROM 'D:\Backup\RMAN\SDR\C-3180286864-20081215-00';
ALTER DATABASE MOUNT;
}

RMAN> RUN
2> {
3> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\Backup\RMAN\SDR\%F';
4> RESTORE CONTROLFILE FROM 'D:\Backup\RMAN\SDR\C-3180286864-20081215-00';
5> ALTER DATABASE MOUNT;
6> }

executing command: SET CONTROLFILE AUTOBACKUP FORMAT
using target database controlfile instead of recovery catalog

Starting restore at 17-DEC-08

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=13 devtype=DISK
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=D:\ORACLE\ORADATA\SDR\CONTROL01.CTL
output filename=D:\ORACLE\ORADATA\SDR\CONTROL02.CTL
output filename=C:\ORACLE\ORADATA\SDR\CONTROL03.CTL
Finished restore at 17-DEC-08

database mounted

RMAN> restore archivelog from time '14-DEC-08';

Starting restore at 17-DEC-08

using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=209
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BACKUP\RMAN\SDR\92K28SQ3_1_1 tag=ORACLESRVR - SDR_12140803
0002 params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=210
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BACKUP\RMAN\SDR\95K2BH5F_1_1 tag=ORACLESRVR - SDR_12150803
0005 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 17-DEC-08

RMAN> restore database;

Starting restore at 17-DEC-08

using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE\ORADATA\SDR\SYSTEM01.DBF
restoring datafile 00002 to D:\ORACLE\ORADATA\SDR\UNDOTBS01.DBF
restoring datafile 00003 to D:\ORACLE\ORADATA\SDR\CWMLITE01.DBF
restoring datafile 00004 to D:\ORACLE\ORADATA\SDR\DRSYS01.DBF
restoring datafile 00005 to D:\ORACLE\ORADATA\SDR\EXAMPLE01.DBF
restoring datafile 00006 to D:\ORACLE\ORADATA\SDR\INDX01.DBF
restoring datafile 00007 to D:\ORACLE\ORADATA\SDR\ODM01.DBF
restoring datafile 00008 to D:\ORACLE\ORADATA\SDR\TOOLS01.DBF
restoring datafile 00009 to D:\ORACLE\ORADATA\SDR\USERS01.DBF
restoring datafile 00010 to D:\ORACLE\ORADATA\SDR\XDB01.DBF
restoring datafile 00011 to D:\ORACLE\ORADATA\SDR\SDRD.ORA
restoring datafile 00012 to D:\ORACLE\ORADATA\SDR\SDRI.ORA
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BACKUP\RMAN\SDR\94K2BGV8_1_1 tag=ORACLESRVR - SDR_12150803
0005 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 17-DEC-08

RMAN> recover database until logseq 0 thread 1;

Starting recover at 17-DEC-08
using channel ORA_DISK_1

starting media recovery
media recovery complete

Finished recover at 17-DEC-08

RMAN> open resetlogs database;

database opened

RMAN>exit

Restore and Recover a database using a HOT Backup and lost archivelog files.

2008-12-24T06:18:00.003+05:30

You can say it as a Dummy Recovery.

$ pwd
/uo1/oracle/app/product/9.2.0.1.0/oradata

$ mkdir edr_arch edr_data edr_temp
$ pwd
/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_temp
$ ls
$ mkdir adump bdump cdump create email tmpfiles udump
$
$ pwd
/uo1/oracle/app/product/9.2.0.1.0/admin/edr

ln -s /uo1/oracle/app/product/9.2.0.1.0/oradata/edr_temp/adump
ln -s /uo1/oracle/app/product/9.2.0.1.0/oradata/edr_temp/bdump
ln -s /uo1/oracle/app/product/9.2.0.1.0/oradata/edr_temp/cdump
ln -s /uo1/oracle/app/product/9.2.0.1.0/oradata/edr_temp/create
ln -s /uo1/oracle/app/product/9.2.0.1.0/oradata/edr_temp/email
ln -s /uo1/oracle/app/product/9.2.0.1.0/oradata/edr_temp/tmpfiles
ln -s /uo1/oracle/app/product/9.2.0.1.0/oradata/edr_temp/udump

$ pwd
/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data
Restore or copy all your backup files accordingly.
--------------------------------------------------------------------------------------------------------
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--------------------------------------------------------------------------------------------------------
My Controlfile Script:

CREATE CONTROLFILE SET DATABASE "edr" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 2268
LOGFILE
GROUP 1 '/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/logedr1.ora' SIZE 200M,
GROUP 2 '/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/logedr2.ora' SIZE 200M,
GROUP 3 '/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/logedr3.ora' SIZE 200M,
GROUP 4 '/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/logedr4.ora' SIZE 200M,
GROUP 5 '/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/logedr5.ora' SIZE 200M
DATAFILE
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/edr_system.dbf',
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/edr_users.dbf',
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/edr_tools.dbf',
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/RBS.dbf',
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/edrDATA_D.dbf',
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/edrDATA_I.dbf',
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/edrMETA_D.dbf',
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/edrMETA_.dbf',
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/edrUSERS.dbf',
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/CUSTOM_INDEX.dbf',
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/DBA.dbf',
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/GCMDB.dbf',
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/PREFSTAT.dbf',
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/edrcode.dbf',
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/edrcode_I.dbf',
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/RCX_OTHER.dbf',
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/EXPORT_D.dbf',
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/edrDATA_D2.dbf.dbf',
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/edrDATA_D3.dbf'
CHARACTER SET WE8MSWIN1252
;

--------------------------------------------------------------------------------------------------------
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--------------------------------------------------------------------------------------------------------
My Pfile:

# Instance self registration parameters
instance_name = edr
service_names = edrserver
db_name = "edr"
db_domain = oracle.com
#db_files = 1000
db_block_size = 8192
db_file_multiblock_read_count = 8

compatible = "9.2.0.1.0"

# db_cache_size replaces the earlier db_block_buffers

db_cache_size = 8m
#db_cache_size = 10485760000

# Optional additional block sizes available in Oracle 9i
# You cannot set db_Nk_cache_size for N = DB_BLOCK_SIZE
# db_2k_cache_size = m
# db_4k_cache_size = m
# db_16k_cache_size = m
# db_32k_cache_size = m

shared_pool_size = 20M
#shared_pool_size = 419430400
#log_buffer = 163840
log_buffer = 20070400

# Optional settings for dynamic SGA and PGA's
# sga_max_size =
sga_max_size = 2728640000

pga_aggregate_target = 380M
#pga_aggregate_target = 419430400

# Eliminates the need for the _area_size parameters.

# To eliminate the need for Rollback segments we set the following Automatic UNDO
# parameters, and we don't set ROLLBACK_SEGMENTS
#UNDO_RETENTION = 90 # Measured in Seconds controls undo segment size.
#UNDO_MANAGEMENT = AUTO
#UNDO_TABLESPACE = UNDOTBS
#UNDO_SUPPRESS_ERRORS = TRUE # Kills error messages for rollback segment operations

control_files = ("/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/control01.ctl",
"/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/control02.ctl")
log_archive_start = false
log_archive_dest_1 = "location=/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_arch/"
log_archive_format = edr_arch%S.arc
background_dump_dest = /uo1/oracle/app/product/9.2.0.1.0/oradata/edr_temp/bdump
core_dump_dest = /uo1/oracle/app/product/9.2.0.1.0/oradata/edr_temp/cdump
user_dump_dest = /uo1/oracle/app/product/9.2.0.1.0/oradata/edr_temp/udump
audit_file_dest = /uo1/oracle/app/product/9.2.0.1.0/oradata/edr_temp/adump

#remote_os_authent = true
#os_authent_prefix = "OPS$"

open_cursors = 3000
max_enabled_roles = 148

#optimizer_mode = choose
#nls_language = american
#nls_date_format = DD-MON-RRRR
#nls_date_language = american

timed_statistics=TRUE
#hash_join_enabled=TRUE
#optimizer_features_enable = 9.2.0.1.0
#query_rewrite_enabled=FALSE
#star_transformation_enabled=FALSE
#java_pool_size=20M
#large_pool_size=200M
#large_pool_size= 209715200
#processes=500
#fast_start_mttr_target=300
#audit_trail=TRUE
#max_rollback_segments=50
#sort_area_size=67108864
#workarea_size_policy = AUTo
#remote_login_passwordfile=exclusive
#log_archive_start=TRUE

--------------------------------------------------------------------------------------------------------
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--------------------------------------------------------------------------------------------------------

ORACLE_SID set to: edr.
ORACLE_HOME set to: /uo1/oracle/app/product/9.2.0.1.0.

startup nomount pfile=/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/pfile/initedr.ora

--------------------------------------------------------------------------------------------------------
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--------------------------------------------------------------------------------------------------------

ORACLE_SID set to: edr.
ORACLE_HOME set to: /uo1/oracle/app/product/9.2.0.1.0.

$ sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Dec 23 08:20:07 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile=/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_temp/pfile/initedr.ora
ORACLE instance started.

Total System Global Area 2738868712 bytes
Fixed Size 733672 bytes
Variable Size 2701131776 bytes
Database Buffers 16777216 bytes
Redo Buffers 20226048 bytes
SQL> @cr8ctl.sql

Control file created.

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1:
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/edr_system.dbf'

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 8469459547375 generated at 12/03/2008 22:00:03 needed for
thread 1
ORA-00289: suggestion :
/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_arch/edr_arch0000006653.arc
ORA-00280: change 8469459547375 for thread 1 is in sequence #6653

Here Here.. I lost my archivelog files..remember...so trying my luck with the online redolog files:

Specify log: {=suggested | filename | AUTO | CANCEL}
/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/log1aedr.rdo
ORA-00310: archived log contains sequence 2370; sequence 6653 required
ORA-00334: archived log:
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/log1aedr.rdo'

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1:
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/edr_system.dbf'

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 8469459547375 generated at 12/03/2008 22:00:03 needed for
thread 1
ORA-00289: suggestion :
/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_arch/edr_arch0000006653.arc
ORA-00280: change 8469459547375 for thread 1 is in sequence #6653

Specify log: {=suggested | filename | AUTO | CANCEL}
/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/log2aedr.rdo
ORA-00310: archived log contains sequence 2371; sequence 6653 required
ORA-00334: archived log:
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/log2aedr.rdo'

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1:
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/edr_system.dbf'

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 8469459547375 generated at 12/03/2008 22:00:03 needed for
thread 1
ORA-00289: suggestion :
/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_arch/edr_arch0000006653.arc
ORA-00280: change 8469459547375 for thread 1 is in sequence #6653

Specify log: {=suggested | filename | AUTO | CANCEL}
/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/log3aedr.rdo
ORA-00310: archived log contains sequence 2369; sequence 6653 required
ORA-00334: archived log:
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/log3aedr.rdo'

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1:
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/edr_system.dbf'

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 8469459547375 generated at 12/03/2008 22:00:03 needed for
thread 1
ORA-00289: suggestion :
/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_arch/edr_arch0000006653.arc
ORA-00280: change 8469459547375 for thread 1 is in sequence #6653

Specify log: {=suggested | filename | AUTO | CANCEL}
/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/logedr1.ora
Log applied.
Media recovery complete.

Hurrah..done. This redolog has the change it required.

**The redologs are also backedup along with the other files.
SQL> alter database open resetlogs;

Database altered.

And the database is up.

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_
----------------- --------- ------------ --- ---------- ------- -----------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST
---------- --- ----------------- ------------------ ---------
1 edr
edrserver
9.2.0.1.0 23-DEC-08 OPEN NO 1 STOPPED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
JServer Release 9.2.0.1.0 - Production
--------------------------------------------------------------------------------------------------------
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--------------------------------------------------------------------------------------------------------
Then some more things to make my restore activity complete:

SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
sQL> alter database open;

create spfile from pfile;

SQL> shutdown immediate;
SQL> startup
ORACLE instance started.

Total System Global Area 2738868712 bytes
Fixed Size 733672 bytes
Variable Size 2701131776 bytes
Database Buffers 16777216 bytes
Redo Buffers 20226048 bytes
Database mounted.
Database opened.

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_
----------------- --------- ------------ --- ---------- ------- -----------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST
---------- --- ----------------- ------------------ ---------
1 edr
edrserver
9.2.0.1.0 23-DEC-08 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL

My controlfile script does not contain the temp file..so I have to add it:

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_temp/tmpfiles/temp01.dbf'SIZE 4000M;

And one more thing needed is that I had to create a password file
#pwd
/uo1/oracle/app/product/9.2.0.1.0/dbs
orapwd file=orapwedr password=**********

Rename datafile

2008-08-15T10:47:00.002+05:30

Process to rename the datafile:

alter tablespace master offline;

!mv masterPROD03.dbf childprod03.dbf
!mv masterPROD02.dbf childprod02.dbf
!mv masterPROD01.dbf childprod01.dbf

alter tablespace master rename datafile '/u01/prod_data/oradata/prod/dbfiles/masterPROD03.dbf' to
'/u01/prod_data/oradata/prod/dbfiles/childprod03.dbf';

alter tablespace master rename datafile '/u01/prod_data/oradata/prod/dbfiles/masterPROD02.dbf' to
'/u01/prod_data/oradata/prod/dbfiles/childprod02.dbf';

alter tablespace master rename datafile '/u01/prod_data/oradata/prod/dbfiles/masterPROD01.dbf' to
'/u01/prod_data/oradata/prod/dbfiles/childprod01.dbf';

select file_name from dba_data_files where tablespace_name='master';

alter tablespace master online;

Hot backup script

2008-08-15T10:40:00.000+05:30

Use this script to prepare the required hot backup scripts

prompt alter system switch logfile;;
DECLARE
CURSOR cur_tablespace IS
SELECT tablespace_name
FROM dba_tablespaces;
CURSOR cur_datafile (tn VARCHAR) IS
SELECT file_name
FROM dba_data_files
WHERE tablespace_name = tn;
BEGIN
FOR ct IN cur_tablespace LOOP
dbms_output.put_line ('alter tablespace '||ct.tablespace_name||
' begin backup;');
FOR cd IN cur_datafile (ct.tablespace_name) LOOP
dbms_output.put_line ('host cp '||cd.file_name||' &dir');
END LOOP;
dbms_output.put_line ('alter tablespace '||ct.tablespace_name||
' end backup;');
END LOOP;
END;
/

Simple vi Editor Command

2008-05-12T16:36:00.000+05:30

General Startup

To use vi: vi filename
To exit vi and save changes: ZZ or :wq
To exit vi without saving changes: :q!
To enter vi command mode: [esc]

Counts : A number preceding any vi command tells vi to repeat that command that many times.

Cursor Movement

h move left (backspace)
j move down
k move up
l move right (spacebar) [return] move to the beginning of the next line
$ last column on the current line
0 move cursor to the first column on the current line
^ move cursor to first nonblank column on the current line
w move to the beginning of the next word or punctuation mark
W move past the next space
b move to the beginning of the previous word or punctuation mark
B move to the beginning of the previous word, ignores punctuation
e end of next word or punctuation mark
E end of next word, ignoring punctuation
H move cursor to the top of the screen
M move cursor to the middle of the screen
L move cursor to the bottom of the screen

Screen Movement

G move to the last line in the file
xG move to line x
z+ move current line to top of screen
z move current line to the middle of screen
z- move current line to the bottom of screen
^F move forward one screen
^B move backward one line
^D move forward one half screen
^U move backward one half screen
^R redraw screen ( does not work with VT100 type terminals )
^L redraw screen ( does not work with Televideo terminals )

Inserting

r replace character under cursor with next character typed
R keep replacing character until [esc] is hit
i insert before cursor a append after cursor
A append at end of line
O open line above cursor and enter append mode

Deleting

x delete character under cursor
dd delete line under cursor
dw delete word under cursor
db delete word before cursor

Copying Code yy (yank)'copies' line which may then be put by the p(put) command. Precede with a count for multiple lines.

Put Command brings back previous deletion or yank of lines, words, or characters P bring back before cursor p bring back after cursor

Find Commands

? finds a word going backwards
/ finds a word going forwards
f finds a character on the line under the cursor going forward
F finds a character on the line under the cursor going backwards
t find a character on the current line going forward and stop one character before it
T find a character on the current line going backward and stop one character before it
; repeat last f, F, t, T

Miscellaneous Commands

. repeat last command
u undoes last command issued
U undoes all commands on one line
xp deletes first character and inserts after second (swap)
J join current line with the next line
^G display current line number
% if at one parenthesis, will jump to its mate
mx mark current line with character x
'x find line marked with character x

NOTE: Marks are internal and not written to the file.

Clone an Oracle database using an online/hot backup

2008-05-12T14:20:00.001+05:30

This procedure will clone a database using a online copy of the source database files. Before beginning though, there are a few things that are worth noting about online/hot backups:

* When a tablespace is put into backup mode, Oracle will write entire blocks to redo rather than the usual change vectors. For this reason, do not perform a hot backup during periods of heavy database activity - it could lead to a lot of archive logs being created.
* This procedure will put all tablespaces into backup mode at the same time. If the source database is quite large and you think that it might take a long time to copy, consider copying the tablespaces one at a time, or in groups.
* While the backup is in progress, it will not be possible to take the tablespaces offline normally or shut down the instance.

Ok, lets get started...

* 1. Make a note of the current archive log change number
Because the restored files will require recovery, some archive logs will be needed. This applies even if you are not intending to put the cloned database into archive log mode. Work out which will be the first required log by running the following query on the source database. Make a note of the change number that is returned:

select max(first_change#) chng
from v$archived_log
/

* 2. Prepare the begin/end backup scripts
The following sql will produce two scripts; begin_backup.sql and end_backup.sql. When executed, these scripts will either put the tablespaces into backup mode or take them out of it:

=====================================
cr_hot_backup.sql

set lines 999 pages 999
set verify off
set feedback off
set heading off

spool begin_backup.sql

select 'alter tablespace ' || tablespace_name || ' begin backup;' tsbb
from dba_tablespaces
where contents != 'TEMPORARY'
order by tablespace_name
/
spool off

spool end_backup.sql

select 'alter tablespace ' || tablespace_name || ' end backup;' tseb
from dba_tablespaces
where contents != 'TEMPORARY'
order by tablespace_name
/
spool off

=======================================

#

# 3. Put the source database into backup mode
From sqlplus, run the begin backup script created in the last step:

@begin_backup

This will put all of the databases tablespaces into backup mode.

# 4. Copy the files to the new location
Copy, scp or ftp the files from the source database/machine to the target. Do not copy the control files across. Make sure that the files have the correct permissions and ownership.

# 5. Take the source database out of backup mode
Once the file copy has been completed, take the source database out of backup mode. Run the end backup script created in step 2. From sqlplus:

@end_backup

# 6. Copy archive logs
It is only necessary to copy archive logs created during the time the source database was in backup mode. Begin by archiving the current redo:

alter system archive log current;
# Then, identify which archive log files are required. When run, the following query will ask for a change number. This is the number noted in step 1.

select name
from v$archived_log
where first_change# >= &change_no
order by name
/

Create an archive directory in the clone database.s file system and copy all of the identified logs into it.

# 7. Produce a pfile for the new database
This step assumes that you are using a spfile. If you are not, just copy the existing pfile.

From sqlplus:

create pfile='init.ora' from spfile;

This will create a new pfile in the $ORACLE_HOME/dbs directory.

Once created, the new pfile will need to be edited. If the cloned database is to have a new name, this will need to be changed, as will any paths. Review the contents of the file and make alterations as necessary. Also think about adjusting memory parameters. If you are cloning a production database onto a slower development machine you might want to consider reducing some values.

Ensure that the archive log destination is pointing to the directory created in step 6.

# 8. Create the clone controlfile
Create a control file for the new database. To do this, connect to the source database and request a dump of the current control file. From sqlplus:

alter database backup controlfile to trace as '/home/oracle/cr_.sql'
/

The file will require extensive editing before it can be used. Using your favourite editor make the following alterations:

* Remove all lines from the top of the file up to but not including the second 'STARTUP MOUNT' line (it's roughly halfway down the file).

* Remove any lines that start with --

* Remove any lines that start with a #

* Remove any blank lines in the 'CREATE CONTROLFILE' section.

* Remove the line 'RECOVER DATABASE USING BACKUP CONTROLFILE'

* Remove the line 'ALTER DATABASE OPEN RESETLOGS;'

* Make a copy of the 'ALTER TABLESPACE TEMP...' lines, and then remove them from the file. Make sure that you hang onto the command, it will be used later.

* Move to the top of the file to the 'CREATE CONTROLFILE' line. The word 'REUSE' needs to be changed to 'SET'. The database name needs setting to the new database name (if it is being changed). Decide whether the database will be put into archivelog mode or not.

* If the file paths are being changed, alter the file to reflect the changes.

Here is an example of how the file would look for a small database called dg9a which isn't in archivelog mode:

====================================================
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "DG9A" RESETLOGS FORCE LOGGING NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 '/u03/oradata/dg9a/redo01.log' SIZE 100M,
GROUP 2 '/u03/oradata/dg9a/redo02.log' SIZE 100M,
GROUP 3 '/u03/oradata/dg9a/redo03.log' SIZE 100M
DATAFILE
'/u03/oradata/dg9a/system01.dbf',
'/u03/oradata/dg9a/undotbs01.dbf',
'/u03/oradata/dg9a/cwmlite01.dbf',
'/u03/oradata/dg9a/drsys01.dbf',
'/u03/oradata/dg9a/example01.dbf',
'/u03/oradata/dg9a/indx01.dbf',
'/u03/oradata/dg9a/odm01.dbf',
'/u03/oradata/dg9a/tools01.dbf',
'/u03/oradata/dg9a/users01.dbf',
'/u03/oradata/dg9a/xdb01.dbf',
'/u03/oradata/dg9a/andy01.dbf',
'/u03/oradata/dg9a/psstats01.dbf',
'/u03/oradata/dg9a/planner01.dbf'
CHARACTER SET WE8ISO8859P1
;
=======================================================

# 9. Add a new entry to oratab and source the environment
Edit the /etc/oratab (or /opt/oracle/oratab) and add an entry for the new database.
Source the new environment with '. oraenv' and verify that it has worked by issuing the following command:

echo $ORACLE_SID

If this doesn't output the new database sid go back and investigate.

# 10. Create the a password file
Use the following command to create a password file (add an appropriate password to the end of it):

orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID} password=

# 11. Create the new control file(s)
Ok, now for the exciting bit! It is time to create the new controlfiles and open the database:

sqlplus "/ as sysdba"

@/home/oracle/cr_

If all goes to plan you will see the instance start and then the message 'Control file created'.

# 12. Recover and open the database
The archive logs that were identified and copied in step 6 must now be applied to the database. Issue the following command from sqlplus:

recover database using backup controlfile until cancel

When prompted to 'Specify log' enter 'auto'. Oracle will then apply all the available logs, and then error with ORA-00308. This is normal, it simply means that all available logs have been applied. Open the database with reset logs:

alter database open resetlogs;

# 13. Create temp files
Using the 'ALTER TABLESPACE TEMP...' command from step 8, create the temp files. Make sure the paths to the file(s) are correct, then run it from sqlplus.

# 14. Perform a few checks
If the last couple of steps went smoothly, the database should be open. It is advisable to perform a few checks at this point:

* Check that the database has opened with:

select status from v$instance;

The status should be 'OPEN'

* Make sure that the datafiles are all ok:

select distinct status from v$datafile;

It should return only ONLINE and SYSTEM.

* Take a quick look at the alert log too.

# 15. Set the databases global name
The new database will still have the source databases global name. Run the following to reset it:

alter database rename global_name to
/

Note. no quotes!

# 16. Create a spfile
From sqlplus:

create spfile from pfile;

# 17. Change the database ID
If RMAN is going to be used to back-up the database, the database ID must be changed. If RMAN isn't going to be used, there is no harm in changing the ID anyway - and it's a good practice to do so.

From sqlplus:

shutdown immediate
startup mount
exit

From unix:

nid target=/

NID will ask if you want to change the ID. Respond with 'Y'. Once it has finished, start the database up again in sqlplus:

shutdown immediate
startup mount
alter database open resetlogs
/

# 18. Configure TNS
Add entries for new database in the listener.ora and tnsnames.ora as necessary.

# 19. Finished
That's it!

Reference:
http://www.shutdownabort.com/quickguides/clone_hot.php

Tom Kytes Challenge

2008-05-01T13:30:00.000+05:30

I Came across a wonderful Challenge Tom(Thomas Kyte) gave to people out there some time back, while going through his asktom site.

The challenge is simple(not as it seems) : Have to correctly provide ALL of the versions the following features were added to Oracle.

Thought interesting so blogged it here.

Happy reading, fun along with knowledge.....just go on

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

ops$tkyte%ORA10GR2> select distinct version from features order by version;

VERSION
--------------------
10.1
10.2
11.1
2
3
4
5
6
7.0
7.1
7.2
7.3
8.0
8.1.5
8.1.6
8.1.7
9.0
9.2

18 rows selected.

So, here are the features:

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select rownum, txt
2 from (select txt from features order by rnd)
3 /

ROWNUM TXT
---------- ----------------------------------------------------------------------
1 Real Application Testing
2 Read only Replication
3 Distributed Query
4 Drop column
5 Client-Server (where the client could be elsewhere in the network)
6 Object Relational Features
7 Ability to return result sets from stored procedures (ref cursors)
8 Commit and Rollback (transactions)
9 Triggers
10 Function based indexes
11 Materialized Views
12 Rman
13 Audit SYSDBA/SYSOPER activity
14 Automatic Undo Management
15 Resumable Operations
16 Automatic Storage Management (ASM)
17 Streams
18 Bitmap Indexes
19 csscan - Character Set Scanner utility
20 Flashback Query
21 Case statement (IN SQL, instead of decode)
22 Parallel Query
23 Transparent column level encryption
24 Tablespace encryption
25 PL/SQL
26 Partitioning
27 Row Level Locking
28 Read Consistency (my favorite feature!)
29 2 Phase Commit
30 Sorted Hash Clusters
31 Conditional compilation for PL/SQL
32 Connect By Queries (select ename, level from emp connect by prior....)
33 Update anywhere Replication

33 rows selected.

=======================================
Check your answers:

select rownum, version, txt
2 from (select version, txt from features order by rnd)
3 /

or

col txt for a40
select * from features order by
to_number(substr(version,1,decode(instr(version,'.'),0,1,instr(version,'.'))))
/

ROWNUM VERSION TXT
---------- -------------------- ----------------------------------------
1 8.1.5 Materialized Views
2 10.1 Sorted Hash Clusters
3 8.0 Rman
4 8.1.6 Case statement
5 7.0 2 Phase Commit
6 11.1 Real Application Testing
7 2 Connect By Queries (select ename, level
from emp connect by prior....)

8 9.0 Automatic Undo Management
9 3 Commit and Rollback (transactions)
10 7.2 Ability to return result sets from store
d procedures (ref cursors)

11 8.1.5 Function based indexes
12 11.1 Tablespace encryption
13 8.1.6 csscan - Character Set Scanner utility
14 4 Read Consistency (my favorite feature!)
15 7.0 Triggers
16 10.1 Automatic Storage Management (ASM)
17 7.1 Update anywhere Replication
18 5 Distributed Query
19 7.1 Parallel Query
20 5 Client-Server (where the client could be
elsewhere in the network)

21 10.2 Transparent column level encryption
22 10.2 Conditional compilation for PL/SQL
23 8.1.5 Drop column
24 9.2 Streams
25 8.0 Object Relational Features
26 9.0 Flashback Query
27 9.2 Audit SYSDBA/SYSOPER activity
28 7.3 Bitmap Indexes
29 6 PL/SQL
30 7.0 Read only Replication
31 6 Row Level Locking
32 8.0 Partitioning
33 9.0 Resumable Operations
================================================================================

Want to know how Tom exactly, created this table :

here is the script,

/*

drop table features;
create table features( rnd number, version varchar2(20), txt varchar2(100) );

insert into features values ( dbms_random.random, '2', 'Connect By Queries (select ename, level
from emp connect by prior....)');
insert into features values ( dbms_random.random, '3', 'Commit and Rollback (transactions)');
insert into features values ( dbms_random.random, '4', 'Read Consistency (my favorite feature!)');
insert into features values ( dbms_random.random, '5', 'Client-Server (where the client could be
elsewhere in the network)');
insert into features values ( dbms_random.random, '5', 'Distributed Query');
insert into features values ( dbms_random.random, '6', 'Row Level Locking');
insert into features values ( dbms_random.random, '6', 'PL/SQL');
insert into features values ( dbms_random.random, '7.0', '2 Phase Commit');
insert into features values ( dbms_random.random, '7.0', 'Triggers');
insert into features values ( dbms_random.random, '7.0', 'Read only Replication');
insert into features values ( dbms_random.random, '7.1', 'Update anywhere Replication');
insert into features values ( dbms_random.random, '7.1', 'Parallel Query');
insert into features values ( dbms_random.random, '7.2', 'Ability to return result sets from stored
procedures (ref cursors)');
insert into features values ( dbms_random.random, '7.3', 'Bitmap Indexes');
insert into features values ( dbms_random.random, '8.0', 'Object Relational Features');
insert into features values ( dbms_random.random, '8.0', 'Partitioning');
insert into features values ( dbms_random.random, '8.0', 'Rman');
insert into features values ( dbms_random.random, '8.1.5', 'Materialized Views');
insert into features values ( dbms_random.random, '8.1.5', 'Function based indexes');
insert into features values ( dbms_random.random, '8.1.5', 'Drop column');
insert into features values ( dbms_random.random, '8.1.6', 'Case statement');
insert into features values ( dbms_random.random, '8.1.6', 'csscan - Character Set Scanner
utility');
insert into features values ( dbms_random.random, '9.0', 'Automatic Undo Management');
insert into features values ( dbms_random.random, '9.0', 'Resumable Operations');
insert into features values ( dbms_random.random, '9.0', 'Flashback Query');
insert into features values ( dbms_random.random, '9.2', 'Streams');
insert into features values ( dbms_random.random, '9.2', 'Audit SYSDBA/SYSOPER activity');
insert into features values ( dbms_random.random, '10.1', 'Automatic Storage Management (ASM)');
insert into features values ( dbms_random.random, '10.1', 'Sorted Hash Clusters');
insert into features values ( dbms_random.random, '10.2', 'Conditional compilation for PL/SQL');
insert into features values ( dbms_random.random, '10.2', 'Transparent column level encryption');
insert into features values ( dbms_random.random, '11.1', 'Tablespace encryption');
insert into features values ( dbms_random.random, '11.1', 'Real Application Testing');

select distinct version from features order by version;
*/

===================================================================
Tom Kytes web sites...

http://tkyte.blogspot.com/
http://asktom.oracle.com/

General process for Query Tuning

2008-04-30T14:32:00.001+05:30

Create a plan table

SQL>@?/rdbms/admin/utlxplan.sql

Autotrace

To switch it on:

SQL>column plan_plus_exp format a100

SQL>set autotrace on explain
# Displays the execution plan only.
SQL>set autotrace traceonly explain
# dont run the query
SQL>set autotrace on
# Shows the execution plan as well as statistics of the statement.
SQL>set autotrace on statistics
# Displays the statistics only.
SQL>set autotrace traceonly
# Displays the execution plan and the statistics

To switch it off:

SQL>set autotrace off

Explain plan

SQL>explain plan for
select ...

or...
SQL>explain plan set statement_id = 'bad1' for
select...

Then to see the output...
SQL>set lines 100 pages 999
SQL>@?/rdbms/admin/utlxpls

Put something unique in the like clause

SQL>select hash_value, sql_text
from v$sqlarea
where sql_text like '%TIMINGLINKS%FOLDERREF%'
/

Grab the sql associated with a hash

SQL>select sql_text
from v$sqlarea
where hash_value = '&hash'
/

Look at a query's stats in the sql area

SQL>select executions
, cpu_time
, disk_reads
, buffer_gets
, rows_processed
, buffer_gets / executions
from v$sqlarea
where hash_value = '&hash'
/

Cool Scripts

2008-04-30T12:06:00.002+05:30

Cool script to know the details of a Particular user.

+++++++++++++++++++++++++++++++++++++++++++++++
-- user_conf.sql

set lines 100 pages 999
set verify off
set feedback off

undefine user

accept userid prompt 'Enter username:'

select username
, default_tablespace
, temporary_tablespace
from dba_users
where username = '&userid'
/

select tablespace_name
, decode(max_bytes, -1, 'unlimited'
, ceil(max_bytes / 1024 / 1024) || 'M' ) "QUOTA"
from dba_ts_quotas
where username = upper('&&userid')
/

select granted_role || ' ' || decode(admin_option, 'NO', '', 'YES', 'with admin option')

"ROLE"
from dba_role_privs
where grantee = upper('&&userid')
/

select privilege || ' ' || decode(admin_option, 'NO', '', 'YES', 'with admin option')

"PRIV"
from dba_sys_privs
where grantee = upper('&&userid')
/

undefine user
set verify on
set feedback on

+++++++++++++++++++++++++++++++++++++++++++++++

Cool script to CLONE a Particular user.

+++++++++++++++++++++++++++++++++++++++++++++++
-- user_clone.sql

set lines 999 pages 999
set verify off
set feedback off
set heading off

select username
from dba_users
order by username
/

undefine user

accept userid prompt 'Enter user to clone: '
accept newuser prompt 'Enter new username: '
accept passwd prompt 'Enter new password: '

select username
, created
from dba_users
where lower(username) = lower('&newuser')
/

accept poo prompt 'Continue? (ctrl-c to exit)'

--spool /tmp/user_clone_tmp.sql
spool D:\temp\testing\user_clone_tmp.sql

select 'create user ' || '&newuser' ||
' identified by ' || '&passwd' ||
' default tablespace ' || default_tablespace ||
' temporary tablespace ' || temporary_tablespace || ';' "user"
from dba_users
where username = '&userid'
/

select 'alter user &newuser quota '||
decode(max_bytes, -1, 'unlimited'
, ceil(max_bytes / 1024 / 1024) || 'M') ||
' on ' || tablespace_name || ';'
from dba_ts_quotas
where username = '&&userid'
/

select 'grant ' ||granted_role || ' to &newuser' ||
decode(admin_option, 'NO', ';', 'YES', ' with admin option;') "ROLE"
from dba_role_privs
where grantee = '&&userid'
/

select 'grant ' || privilege || ' to &newuser' ||
decode(admin_option, 'NO', ';', 'YES', ' with admin option;') "PRIV"
from dba_sys_privs
where grantee = '&&userid'
/

spool off

undefine user

set verify on
set feedback on
set heading on

@D:\temp\testing\user_clone_tmp.sql

host del D:\temp\testing\user_clone_tmp.sql

--@/tmp/user_clone_tmp.sql

--!rm /tmp/user_clone_tmp.sql

NOTE: Username should be Entered in Captitals.

+++++++++++++++++++++++++++++++++++++++++++++++

Save the scripts as .sql and run at the command prompt.

How to Create and Use OMF

2008-04-29T15:43:00.000+05:30

OMF indicates Oracle Managed Files. With the use of Oracle-managed files the administration of an Oracle Database can be simplified. Oracle-managed files eliminate the need for you, the DBA, to directly manage the operating system files comprising an Oracle Database. You specify operations in terms of database objects rather than filenames.

Enable the Creation of OMFs
--------------------------------
The following initialization parameters allow the database server to use the Oracle-managed files feature.

1)DB_CREATE_FILE_DEST: Defines the location of the default file system directory where the database creates datafiles or tempfiles when no file specification is given in the creation operation. It is also used as the default file system directory for redo log and control files if DB_CREATE_ONLINE_LOG_DEST_n is not specified.

2)DB_CREATE_ONLINE_LOG_DEST_n:Defines the location of the default file system directory for redo log files and control file creation when no file specification is given in the creation operation. You can use this initialization parameter multiple times, where n specifies a multiplexed copy of the redo log or control file. You can specify up to five multiplexed copies.

3)DB_RECOVERY_FILE_DEST:Defines the location of the default file system directory where the database creates RMAN backups when no format option is used, archived logs when no other local destination is configured, and flashback logs. Also used as the default file system directory for redo log and control files if DB_CREATE_ONLINE_LOG_DEST_n is not specified.

Both of these initialization parameters are dynamic, and can be set using the ALTER SYSTEM or ALTER SESSION statement.

An Example of using OMF :
---------------------------
1)Setting the parameter for the session:

SQL> alter session set db_create_file_dest='/oradata';
Session altered.

2)Create Tablespace using OMF:

SQL> create tablespace omf_tbs;
Tablespace created.

3)Check the data file Location:

SQL> select file_name from dba_data_files where tablespace_name='OMF_TBS';
FILE_NAME
--------------------------------------------------------------------------------
/oradata/EDR/datafile/o1_mf_omf_tbs_4067w4op_.dbf

Here EDR is the Database Name.

The dafault location for datafile is Your settings for parameter/Database Name/datafile/Unique Name.dbf

Oracle11g: Where’s My Alert Log?

2008-04-19T16:41:00.001+05:30

Just a short blog entry about Oracle 11g.
One of the first things that caught me by surprise with 11g,
when I first started in the beta program, was that the default
location for the alert log has moved. It is still placed
under the traditional OFA structure, but not /u01/app/oracle/admin.
There is a new directory called diag that resides
in /u01/app/oracle as seen on one of my systems:

[oracle@oracle11gr1 ~]$ cd /u01/app/oracle/diag/rdbms/orcl/orcl
[oracle@oracle11gr1 orcl]$ ll
total 64
drwxr-x--- 2 oracle oinstall 4096 Jan 9 13:23 alert
drwxr-x--- 2 oracle oinstall 4096 Jan 9 13:23 cdump
drwxr-x--- 2 oracle oinstall 4096 Jan 9 13:23 hm
drwxr-x--- 2 oracle oinstall 4096 Jan 9 13:23 incident
drwxr-x--- 2 oracle oinstall 4096 Jan 9 13:23 incpkg
drwxr-x--- 2 oracle oinstall 4096 Jan 24 01:38 ir
drwxr-x--- 2 oracle oinstall 4096 Jan 9 13:23 lck
drwxr-x--- 2 oracle oinstall 4096 Jan 9 13:23 metadata
drwxr-x--- 2 oracle oinstall 4096 Jan 9 13:23 stage
drwxr-x--- 2 oracle oinstall 4096 Jan 9 13:23 sweep
drwxr-x--- 2 oracle oinstall 24576 Apr 19 15:33 trace

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

[oracle@oracle11gr1 trace]$ ll alert_orcl.log
-rw-r----- 1 oracle oinstall 392 Apr 19 15:33 alert_orcl.log

In this case, my database is called orcl and the instance is orcl.

View : v$diag_info

If Alert log file is lost

2008-04-19T16:37:00.000+05:30

One of my friend asked me whether if in any case the
alert log file is lost what happens?

It is automatically created whenever there needs to
be a new entry into the alert log.

Lets see a practical explanation

[oracle@oracle11gr1 ~]$ sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Apr 19 15:35:05 2008

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

SQL> conn /as sysdba
Connected.
SQL>
SQL>!

[oracle@oracle11gr1]$ cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace
[oracle@oracle11gr1 trace]$
[oracle@oracle11gr1 trace]$ ll alert_orcl.log
-rw-r----- 1 oracle oinstall 144278 Apr 19 15:26 alert_orcl.log

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

[oracle@oracle11gr1 trace]$
[oracle@oracle11gr1 trace]$ mv alert_orcl.log alert_orcl_bak.log

[oracle@oracle11gr1 trace]$ll alert_orcl.log
ls: alert_orcl.log: No such file or directory

[oracle@oracle11gr1 trace]$exit

SQL>
SQL> CREATE TABLESPACE EDR_VECTOR_SPIND DATAFILE
'/u01/app/oracle/oradata/orcl/user.dbf' SIZE 4M AUTOEXTEND ON
MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; 2 3

Tablespace created.

SQL>
SQL>
SQL> !
[oracle@oracle11gr1 ~]$
[oracle@oracle11gr1 ~]$
[oracle@oracle11gr1 ~]$ cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace
[oracle@oracle11gr1 trace]$
[oracle@oracle11gr1 trace]$
[oracle@oracle11gr1 trace]$ ll alert_orcl.log
-rw-r----- 1 oracle oinstall 392 Apr 19 15:33 alert_orcl.log

[oracle@oracle11gr1 trace]$
[oracle@oracle11gr1 trace]$ more alert_orcl.log
Sat Apr 19 15:33:15 2008
CREATE TABLESPACE EDR_VECTOR_SPIND DATAFILE
'/u01/app/oracle/oradata/orcl/user.dbf' SIZE 4M AUTOEXTEND ON
MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
Completed: CREATE TABLESPACE EDR_VECTOR_SPIND DATAFILE
'/u01/app/oracle/oradata/orcl/user.dbf' SIZE 4M AUTOEXTEND ON
MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

[oracle@oracle11gr1 trace]$

Capture all SQL run between two points in time

2008-04-11T12:42:00.001+05:30

There are situations where it is useful to capture the SQL
that a particular user is running in the database. Usually
you would simply enable session tracing for that user, but
there are two potential problems with that approach.

The first is that many web based applications maintain a
pool of persistent database connections which are shared
amongst multiple users. The second is that some applications
connect, run some SQL and disconnect very quickly, making it
tricky to enable session tracing at all (you could of course
use a logon trigger to enable session tracing in this case).

A quick and dirty solution to the problem is to capture all
SQL statements that are run between two points in time.

The following procedure will create two tables, each containing
a snapshot of the database at a particular point. The tables
will then be queried to produce a list of all SQL run during
that period.

If possible, you should do this on a quiet development system -
otherwise you risk getting way too much data back.

1. Take the first snapshot
Run the following sql to create the first snapshot:

create table sql_exec_before as
select executions
, hash_value
from v$sqlarea
/

2. Get the user to perform their task within the application

3. Take the second snapshot

select aft.hash_value
from sql_exec_before bef
, sql_exec_after aft
where aft.executions > bef.executions
and aft.hash_value = bef.hash_value (+)
/

4. Check the results
Now that you have captured the SQL it is time to query the results.

This first query will list all query hashes that have been executed:
select aft.hash_value
from sql_exec_before bef
, sql_exec_after aft
where aft.executions > bef.executions
and aft.hash_value = bef.hash_value (+)
/

This one will display the hash and the SQL itself:

set pages 999 lines 100
break on hash_value
select hash_value
, sql_text
from v$sqltext
where hash_value in (
select aft.hash_value
from sql_exec_before bef
, sql_exec_after aft
where aft.executions > bef.executions
and aft.hash_value = bef.hash_value (+)
)
order by
hash_value
, piece
/

5. Tidy up

Don't forget to remove the snapshot tables once you've finished:

drop table sql_exec_before
/

drop table sql_exec_after
/

Source : www.shutdownabort.com

How to find Client IP address by Quering Oracle Database ?

2008-04-10T17:24:00.001+05:30

We can use PLSQL package UTL_INADDR.

There are two procedures'

1. GET_HOST_NAME or
2. GET_HOST_ADDRESS

for finding out local machine HOST name or IP address.

UTL_INADDR

We can use below query to findout CLIENT IP ADDRESS.

SQL> col machine for a20
SQL> col UTL_INADDR.GET_HOST_ADDRESS(MACHINE) for a40

SQL> select sid,machine,UTL_INADDR.GET_HOST_ADDRESS (machine)
from v$session
where type = 'USER' and username is not null
order by sid;

SID MACHINE UTL_INADDR.GET_HOST_ADDRESS(MACHINE)
---------- -------------------- ----------------------------------------
152 edrserver 10.21.1.249

=====================================================
If you are getting below error message then modify above code little bit.

ERROR at line 1:ORA-29257: host xxxxxxxxxxxxxxxxxx unknown

ORA-06512: at "SYS.UTL_INADDR", line 19

ORA-06512: at "SYS.UTL_INADDR", line 40

ORA-06512: at line 1

==================Modified code======================

SQL> select sid, machine,
UTL_INADDR.get_host_address (substr(machine,instr(machine,'\')+1)) ip
from v$session
where type='USER' and username is not null
order by sid;

SID MACHINE IP
---------- -------------------- ------------------------------
152 edrserver 10.21.1.249

Rman Recovery Scenarios

2008-03-19T11:29:00.000+05:30

Rman recovery scenarios require that the database
is in archive log mode, and that backups of datafiles,
control files and archived redolog files are made using
Rman. Incremental Rman backups may be used also.

Rman can be used with the repository installed on the
archivelog, or with a recovery catalog that may be
installed in the same or other database.

Configuration and operation recommendations:

Set the parameter controlfile autobackup to ON
to have with each backup a controlfile backup also:

rman>configure controlfile autobackup on;

Set the parameter retention policy to the recovery
window you want to have, i.e redundancy 2 will keep
the last two backups available, after executing
delete obsolete commands:

rman>configure retention policy to redundancy 2;

Execute your full backups with the option
'plus archivelogs' to include your
archivelogs with every backup:

rman>backup database plus archivelog;

Perform daily maintenance routines to maintain on
your backup directory the number of backups you need only:

rman>crosscheck backup;
rman>crosscheck archivelog all;
rman>delete noprompt obsolete backup;

To work with Rman and a database based
catalog follow these steps:
1. sqlplus /
2. create tablespace repcat;
3. create user rcuser identified by rcuser default tablespace repcat
temporary tablespace temp;
4. grant connect, resource, recovery_catalog_owner to rcuser;
5. exit
6. rman catalog rcuser/rcuser # connect to rman catalog as the rcuser
7. create catalog # create the catalog
8. connect target / #
=============================================
Complete Closed Database Recovery. System tablespace is missing
---------------------------------------------
In this case complete recovery is performed, only the
system tablespace is missing, so the database can be
opened without reseting the redologs.
1. rman target /
2. startup mount;
3. restore database;
4. recover database;
5. alter database open;
=============================================
Complete Open Database Recovery. Non system tablespace
is missing,database is up
---------------------------------------------
1. rman target /
2. sql 'alter tablespace offline immediate';
3. restore datafile 3;
4. recover datafile 3;
5. sql 'alter tablespace online';
=============================================
Complete Open Database Recovery (when the database
is initially closed). Non system tablespace is missing
---------------------------------------------
A user datafile is reported missing when trying to
startup the database. The datafile can be turned offline
and the database started up. Restore and recovery are
performed using Rman. After recovery is performed
the datafile can be turned online again.
1. sqlplus /nolog
2. connect / as sysdba
3. startup mount
4. alter database datafile '' offline;
5. alter database open;
6. exit;
7. rman target /
8. restore datafile '';
9. recover datafile '';
10. sql 'alter tablespace online';
=============================================
Recovery of a Datafile that has no backups (database is up).
---------------------------------------------
If a non system datafile that was not backed up since
the last backup is missing, recovery can be performed
if all archived logs since the creation of the missing
datafile exist. Since the database is up you can check
the tablespace name and put it offline. The option offline
immediate is used to avoid that the update of the datafile header.

Pre requisites: All relevant archived logs.
1. sqlplus '/ as sysdba'
2. alter tablespace offline immediate;
3. alter database create datafile '/user/oradata/u01/dbtst/newdata01.dbf;
4. exit
5. rman target /
6. recover tablespace ;
7. sql 'alter tablespace online';
If the create datafile command needs to be executed to place
the datafile on a location different than the original use:
alter database create datafile '/user/oradata/u01/dbtst/newdata01.dbf' as
'/user/oradata/u02/dbtst/newdata01.dbf'
=============================================
Restore and Recovery of a Datafile to a different location. Database is up.
---------------------------------------------
If a non system datafile is missing and its original
location not available, restore can be made to a different
location and recovery performed.

Pre requisites: All relevant archived logs, complete cold or hot backup.
1. Use OS commands to restore the missing or corrupted datafile to the new
location, ie:
cp -p /user/backup/uman/user01.dbf /user/oradata/u02/dbtst/user01.dbf
2. alter tablespace offline immediate;
3. alter tablespace rename datafile
'/user/oradata/u01/dbtst/user01.dbf' to '/user/oradata/u02/dbtst/user01.dbf';
4. rman target /
5. recover tablespace ;
6. sql 'alter tablespace online';
=============================================
Control File Recovery
---------------------------------------------
Always multiplex your controlfiles. If you loose only one
controlfile you can replace it with the one you have in place,
and startup the Database. If both controlfiles are missing,
the database will crash.

Pre requisites: A backup of your controlfile and all relevant
archived logs. When using Rman alway set configuration parameter
autobackup of controlfile to ON. You will need the dbid to
restore the controlfile, get it from the name of the backed up
controlfile. It is the number following the 'c-' at the start of the name.
1. rman target /
2. set dbid
3. startup nomount;
4. restore controlfile from autobackup;
5. alter database mount;
6. recover database;
7. alter database open resetlogs;
8. make a new complete backup, as the database is open in a new incarnation and
previous archived log are not relevant.
=============================================
Incomplete Recovery, Until Time/Sequence/Cancel
---------------------------------------------
Incomplete recovery may be necessaire when the database
crash and needs to be recovered, and in the recovery process
you find that an archived log is missing. In this case recovery
can only be made until the sequence before the one that is missing.

Another scenario for incomplete recovery occurs when an important
object was dropped or incorrect data was committed on it.
In this case recovery needs to be performed until before the
object was dropped.

Pre requisites: A full closed or open database backup and archived
logs, the time orsequence that the 'until' recovery
needs to be performed.
1. If the database is open, shutdown it to perform full restore.
2. rman target \
3. startup mount;
4. restore database;
5. recover database until sequence 8 thread 1; # you must pass the thread, if a
single instance will always be 1.
6. alter database open resetlogs;
7. make a new complete backup, as the database is open in a new incarnation and
previous archived log are not relevant.
Alternatively you may use instead of until sequence, until time, ie: '2008-03-
16:01:01:10'.

How to change the root password of a linux server.

2008-03-15T17:52:00.000+05:30

Use this information at your own risk,

Please note that you cannot perform this remotely,
you must be physically at the machine.

1. The first step is to reboot server.

2. Since the server I inherit has GRUB, wait until
the grub splash screen comes on and hit the letter 'e'
on your keyboard. this will stop grub.

3. hit 'e' again and this will take you to another screen

4. you should see one of the choices has "kernel"
select the one with "kernel

5. hit 'e' on your keyboard

6. type ' single' or 1 (make sure to include a space before "single/1")
and hit enter. then enter 'b' to boot.

7. your system should boot up in single mode as root

8. now that you are logged in single mode you can
change the root password by sending this command:
-->> passwd
Give the new password

9. after you have changed the root password, reboot server
and you should be able to login with the new password.

DONE

LILO ----------------------------

1. If you have LILO, its different, first reboot server and when you
see the LILO: prompt type "linux single" and hit enter.
this will log you in as root in single user mode.

2. Once booting is complete, you can change your password with this command:
-->> passwd
Give the new password
and reboot server.

DONE

Usage of Split command in Linux.

2008-03-15T12:45:00.001+05:30

Split, have a large file that you need to split into smaller chucks? A Oracle dump maybe? split is your command. Below I split a 110MB file into 30 megabyte chunks.

Split works just fine on both text, and binary (even compressed) files. An example is worth a thousand words of man page (that don't have examples). Here I have TBL_LOSANGELES_CA.dmp, a 109MB Oracle export dump file.

[root@oracle11gr1 ~]# mkdir split
[root@oracle11gr1 ~]# mv /share/TBL_LOSANGELES_CA.dmp split/
[root@oracle11gr1 ~]# cd split/
[root@oracle11gr1 split]# ll
total 111284
-rwxrw-r-- 1 vshare vshare 113836032 Oct 11 22:43 TBL_LOSANGELES_CA.dmp

[root@oracle11gr1 split]#split -b 30m TBL_LOSANGELES_CA.dmp TBL_LOSANGELES_CA_part_
[root@oracle11gr1 split]# ll
total 222584
-rwxrw-r-- 1 vshare vshare 113836032 Oct 11 22:43 TBL_LOSANGELES_CA.dmp
-rw-r--r-- 1 root root 31457280 Mar 14 14:07 TBL_LOSANGELES_CA_part_aa
-rw-r--r-- 1 root root 31457280 Mar 14 14:07 TBL_LOSANGELES_CA_part_ab
-rw-r--r-- 1 root root 31457280 Mar 14 14:07 TBL_LOSANGELES_CA_part_ac
-rw-r--r-- 1 root root 19464192 Mar 14 14:07 TBL_LOSANGELES_CA_part_ad

Cat command is used to join the files split by above split command.

[root@oracle11gr1 split]#cat TBL_LOSANGELES_CA_part_aa TBL_LOSANGELES_CA_part_ab TBL_LOSANGELES_CA_part_ac TBL_LOSANGELES_CA_part_ad >TBL_LOSANGELES

[root@oracle11gr1 split]# ls -lh
total 327M
-rw-r--r-- 1 root root 109M Mar 14 14:15 TBL_LOSANGELES
-rwxrw-r-- 1 vshare vshare 109M Oct 11 22:43 TBL_LOSANGELES_CA.dmp
-rw-r--r-- 1 root root 30M Mar 14 14:07 TBL_LOSANGELES_CA_part_aa
-rw-r--r-- 1 root root 30M Mar 14 14:07 TBL_LOSANGELES_CA_part_ab
-rw-r--r-- 1 root root 30M Mar 14 14:07 TBL_LOSANGELES_CA_part_ac
-rw-r--r-- 1 root root 19M Mar 14 14:07 TBL_LOSANGELES_CA_part_ad

You can check whether the files are identical are not by using the DIFF command.

[root@oracle11gr1 split]# diff -s TBL_LOSANGELES TBL_LOSANGELES_CA.dmp
Files TBL_LOSANGELES and TBL_LOSANGELES_CA.dmp are identical
[root@oracle11gr1 split]#

The cat command can be broken into parts (this is useful if each part is on a separate disk, like one each on CDs). Take note that the first time, a single > is used to make sure that the data from this first part overwrites the destination file (if it already exists), but that each time after that, a double > is used to append to the destination file.

cat TBL_LOSANGELES_CA_part_aa > TBL_LOSANGELES
cat TBL_LOSANGELES_CA_part_ab >> TBL_LOSANGELES
cat TBL_LOSANGELES_CA_part_ac >> TBL_LOSANGELES
cat TBL_LOSANGELES_CA_part_ad >> TBL_LOSANGELES

RMAN - Recovery Manager for Oracle Database 10g

2008-03-13T16:58:00.000+05:30

WHAT IS RMAN ?

Recovery Manager is a tool that: manages the process of creating backups and also manages the process of restoring and recovering from them.

WHY USE RMAN ?

No extra costs …Its available for free
RMAN introduced in Oracle 8 it has become simpler with
newer versions and easier than user managed backups Proper security
You are 100% sure your database has been backed up.
Its contains detail of the backups taken etc in its central repository
Facility for testing validity of backups also commands like crosscheck to Check the status of backup.
Faster backups and restores compared to backups without RMAN
RMAN is the only backup tool which supports incremental backups.
Oracle 10g has got further optimized incremental backup which has
resulted in improvement of performance during backup and recovery time
Parallel operations are supported
Better querying facility for knowing different details of backup
No extra redo generated when backup is taken..compared to online backup
without RMAN which results in saving of space in hard disk
RMAN is an intelligent tool
Maintains repository of backup metadata
Remembers backup set location
Knows what need to backed up
Knows what is required for recovery
Knows what backup are redundant

UNDERSTANDING THE RMAN ARCHITECTURE

An oracle RMAN comprises of RMAN EXECUTABLE
This could be present and fired even through
client side TARGET DATABASE.
(Target)This is the database which needs to be backed up
RECOVERY CATALOG is optional otherwise backup
details are stored in target database controlfile
It is a repository of information queried and updated by Recovery Manager
It is a schema or user stored in Oracle database
One schema can support many databases
It contains information about physical schema of target database datafile and archive log, backup sets and pieces

Recovery catalog is a must in following scenarios
In order to store scripts
For tablespace point in time recovery

Media Management Software

Media Management software is a must if you are using
RMAN for storing backup in tape drive directly.

Backups in RMAN

Oracle backups in RMAN are of the following type

RMAN complete backup OR RMAN incremental backup

These backups are of RMAN proprietary nature

IMAGE COPY

Its again a kind of backup. The advantage of uing Image copy is its not in RMAN proprietary format.

Backup Format

RMAN backup is not in oracle format but in RMAN format.
Oracle backup comprises of backup sets and it consists
of backup pieces. Backup sets are logical entity.
In oracle 9i it gets stored in a default location.

There are two type of backup sets
Datafile backup sets,
Archivelog backup sets

One more important point of data file backup sets
is it do not include empty blocks. A backup set
would contain many backup pieces. A single backup piece
consists of physical files which are in RMAN proprietary format.

You can go to RMAN prompt by just typing rman.
RMAN executable is present in ORACLE_HOME/bin location.

bash-2.05$ rman

Recovery Manager: Release 10.1.0.5.0 - Production

Copyright (c) 1995, 2004, Oracle. All rights reserved.

RMAN>

You can use target connect to connect to database.
The database it will connect to depends on the environment variable ORACLE_HOME.

RMAN> connect target

connected to target database: ORCL (DBID=1176834246)

Alternatively you can use “rman TARGET SYS/sys@test NOCATALOG”
to connect to the RMAN of “test” instance.

Here we will be using target database control file
to store all the information required for RMAN,
like backupsets and backup image information etc.

Backup Database:

RMAN> shutdown immediate

using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 1660944384 bytes

Fixed Size 1322112 bytes
Variable Size 686019456 bytes
Database Buffers 973078528 bytes
Redo Buffers 524288 bytes

RMAN> backup database;

Starting backup at 13-MAR-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=166 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 13-MAR-08
channel ORA_DISK_1: finished piece 1 at 13-MAR-08
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2008_03_13/

o1_mf_nnndf_TAG20080313T031455_3b8zv57d_.bkp tag=TAG20080313T031455 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:36
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 13-MAR-08
channel ORA_DISK_1: finished piece 1 at 13-MAR-08
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2008_03_13/

o1_mf_ncsnf_TAG20080313T031455_3b8zy7xr_.bkp tag=TAG20080313T031455 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:06
Finished backup at 13-MAR-08
Creating RMAN Catalog

You can also create a repository for RMAN to store all this
information. Repository will be just another small
database which can store the catalog information.
Creating a catalog is a 3 step process

1) Create database which will hold the catalog.
Else you can use the existing database also.
All you need is to create a seperate tablespace
for holding the information about RMAN catalog.

2) Create RMAN tablespace and RMAN user

SQL> create tablespace rman_tbs datafile ‘/u01/app/oracle/oradata/TEST/rman01.dbf’ size 240M EXTENT MANAGEMENT LOCAL segment SPACE MANAGEMENT AUTO ;

Tablespace created.

SQL> create user rcat identified by rcat
default tablespace rman_tbs
TEMPORARY TABLESPACE TEMPTS1
QUOTA UNLIMITED ON rman_tbs account unlock;

User created.

After creating user, you need to grant RECOVERY_CATALOG_OWNER role to that user.

3) Create RMAN catalog.

bash-2.05$ rman catalog rcat/rcat@test

Recovery Manager: Release 10.1.0.5.0 - Production on Thu Mar 13 02:47:16 2008

Copyright (c) 1995, 2004, Oracle. All rights reserved.

connected to recovery catalog database

RMAN> create catalog

recovery catalog created

For registering the database, you need to get connected
to database as well as catalog at the same time. Here is how you can do.

RMAN> connect target

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20001: target database not found in recovery catalog

The above error is because the database we connected to is not found in the catalog database. We can register the database in catalog.

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

Registering database will also resynch the information present
in the target database control file and catalog database.
Since we have taken 1 backup early, it will synchup that
information with RMAN catalog. We can check the same using
LIST BACKUP command at RMAN prompt.

RMAN> list backup;

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
18 Full 573.48M DISK 00:01:26 13-MAR-08
BP Key: 20 Status: AVAILABLE Compressed: NO Tag: TAG20080313T031355
Piece Name: /u01/app/oracle/oradata/ORCL/backupset/2008_03_13/
o1_mf_nnndf_TAG20080313T031355_3b8zv57d_.bkp

List of Datafiles in backup set 18
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——— —-
1 Full 562487 13-MAR-08 /u01/app/oracle/oradata/ORCL/system01.dbf
2 Full 562487 13-MAR-08 /u01/app/oracle/oradata/ORCL/undotbs01.dbf
3 Full 562487 13-MAR-08 /u01/app/oracle/oradata/ORCL/sysaux01.dbf
4 Full 562487 13-MAR-08 /u01/app/oracle/oradata/ORCL/users01.dbf
5 Full 562487 13-MAR-08 /u01/app/oracle/oradata/ORCL/example01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
19 Full 6.80M DISK 00:00:05 13-MAR-08
BP Key: 21 Status: AVAILABLE Compressed: NO Tag: TAG20080313T031355
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2008_03_13/
o1_mf_ncsnf_TAG20080313T031355_3b8zy7xr_.bkp

Control File Included: Ckp SCN: 562487 Ckp time: 13-MAR-08
SPFILE Included: Modification time: 13-MAR-08

RMAN Command line

Backing up the Controlfile and Spfile

The control file can be automatically backed up after each
RMAN backup and database structure change as a way to protect
the RMAN repository (when we are not using a seperate catalog for RMAN).

RMAN> configure controlfile autobackup on;

Backing up control file

RMAN> backup current controlfile;

Creating Image copy of all datafiles in database

These are the image copies and are stored in ORACLE format
and not in RMAN format. Backupsets and backuppieces are
stored in internal RMAN format. Hence these image copies can be
used for manual restore and recovery as well.

RMAN> backup as copy database;

Creating backupsets of all datafiles in database

If you specify BACKUP AS BACKUPSET, then RMAN stores its backups
in backup sets. A backup set, consisting of one or more backup pieces,
contains the physical file data being backed up. This backupset
is written in a format that only RMAN can access. Only RMAN can
create and restore backup sets. Backup sets can be written to disk
or tape, and they are the only type of backup which RMAN can use
to write backups to tape.

RMAN> backup as backupset database;

Backup individual tablespace as backupsets

RMAN> backup tablespace system, HTMLDB;

Backup individual tablespace as image copies

RMAN> backup as copy tablespace system;

Backup individual files as image copies

RMAN> backup as copy datafile ‘/u01/app/oracle/oradata/test/system01.dbf’;

Backup individual files as Backupsets

RMAN> backup datafile ‘/u01/app/oracle/oradata/test/system01.dbf’;

Backup archivelogs

We can backup the archive logs according to the output of some
search condition. Example we want to backup only those
archivelogs which starts with “ARCH_616814159_”.

RMAN> backup acrchivelog like '%ARCH_616814159_%';

Copy archivelogs from some time stamp.

Suppose we want to copy the archivelogs of last 2 days,
then we can use the following commands.

RMAN> BACKUP ARCHIVELOG from time 'sysdate-2';

Backup all archivelog files

RMAN> Backup archivelog all;

Backup archivelog between some time.

RMAN> BACKUP ARCHIVELOG FROM TIME 'SYSDATE-30' UNTIL TIME 'SYSDATE-7';

Specifying copies while backing up.

RMAN> backup copies 2 datafile '/u01/app/oracle/oradata/test/cs_tbs01.dbf';

Remember that copies option cannot be used with image copies.
It can be used only with backupsets.

Giving tags to backups

RMAN> BACKUP TAG ‘weekly_full_db_bkup’ DATABASE MAXSETSIZE 100M;

Backing up backupsets
RMAN> BACKUP BACKUPSET ALL;

Backup imagecopies

RMAN> Backup as copy backupset all;

List Imagecopies

RMAN> list copy;

List Backupsets

RMAN> list backup;

Restoring and Recovering the database

Use the RESTORE and RECOVER commands for RMAN restore and
recovery of physical database files.

RMAN> STARTUP FORCE MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN;

Recovering Current Tablespaces

RMAN> SQL ‘ALTER TABLESPACE users OFFLINE’;
RESTORE TABLESPACE users;
RECOVER TABLESPACE users;
SQL ‘ALTER TABLESPACE users ONLINE;

Recovering Current Datafiles

RMAN> SQL ‘ALTER DATABASE DATAFILE 7 OFFLINE’;
RESTORE DATAFILE 7;
RECOVER DATAFILE 7;
SQL ‘ALTER DATABASE DATAFILE 7 ONLINE’;

Recovering Individual Data Blocks

RMAN can recover individual corrupted datafile blocks.
When RMAN performs a complete scan of a file for a backup,
any corrupted blocks are listed in V$DATABASE_BLOCK_CORRUPTION.
Corruption is usually reported in alert logs, trace files or
results of SQL queries. Use BLOCKRECOVER to repair all corrupted blocks:

RMAN> BLOCKRECOVER CORRUPTION LIST;

You can also recover individual blocks, as shown in this example:
RMAN> BLOCKRECOVER DATAFILE 7 BLOCK 233, 235 DATAFILE 4 BLOCK 101;

Managing RMAN repository

As you know that if you do not use a recovery catalog, then
control file of the target database is used as RMAN repository
and eventually after some time the control file records
for RMAN information will get overwritten.Set this initialization
parameter in the parameter file of the target database to
determine how long records are kept:

CONTROL_FILE_RECORD_KEEP_TIME =

You can configure a retention policy to be used by RMAN
to determine which backups are considered obsolete.
This allows you to remove files from the repository that
are no longer needed to meet your retention requirements.
This policy can be based on a recovery window (the maximum number
of days into the past for which you can recover) or redundancy
(how many copies of each backed-up file to keep).

Two Parameters are used to set retention policies.

You can specify the days days between the current time and the
earliest point of recoverability, this is called RECOVERY WINDOW.
RMAN does not consider any full or level 0 incremental backup
as obsolete if it falls within the recovery window.
Alternatively the REDUNDANCY parameter will instruct to store the
number of copies of backup in RMAN repository. If the number of
backups for a specific datafile or control file exceeds the REDUNDANCY
setting considers the extra backups as obsolete.

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 3;

This will make the REDUNDANCY setting to 3. Meaning that it will
at max store 3 copies of backups and ikmages of datafile.
Any more images or backups are consider obsolete.

When we run the below command

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

it will configure new retention policy based on the RECOVERY WINDOW
of 7 days. This means that all the backups which falls outside this
window will be considered obsolute. So in this case you need to have
backup scheduled every week to have atleast 1 valid backup.

Remember that at any point of time, only one policy can be active.
It can be either REDUNDANCY or RECOVERY WINDOW.

When you change the retention policy to another one, it will suspend
the previous policy as shown below.

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

old RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

Cross checking the available backups

RMAN> CROSSCHECK BACKUP;

Crosscheck is needed when an archivelog file or backup is manually
removed, i.e., not deleted by RMAN. This command ensures that data
about backups in the recovery catalog or control file is synchronized
with corresponding data on disk or in the media management catalog.
The CROSSCHECK command operates only on files that are recorded in
the recovery catalog or the control file.

The CROSSCHECK command does not delete any files that it is
unable to find, but updates their repository records to EXPIRED.
Then, you can run DELETE EXPIRED to remove the repository records
for all expired files as well as any existing physical files whose
records show the status EXPIRED.

If some backup pieces or copies were erroneously marked as EXPIRED,
for example, because the media manager was misconfigured, then after
ensuring that the files really do exist in the media manager, run
the CROSSCHECK BACKUP command again to restore those files to AVAILABLE
status.

RMAN> CROSSCHECK COPY;

This will validate the image copies.

Deleting the backups

The DELETE command removes RMAN backups and copies from DISK marks
the records in control file as DELETED or removes the records from the
recovery catalog (if you use a catalog).

RMAN> DELETE BACKUPSET 101, 102, 103;

RMAN> delete controlfilecopy ‘%ctl’; # Pattern search is allowed.

RMAN> delete archivelog until sequence 20;

This will delete all the archives from the oldest one till the
sequence we have specified.

RMAN> delete backup of tablespace system;

RMAN> DELETE ARCHIVELOG ALL BACKED UP 2 TIMES TO DEVICE TYPE DISK;

List commands

RMAN> list backup;

RMAN> list copy;

RMAN> list incarnation of database;

RMAN> list expired backupset;

RMAN> list expired copy;

RMAN> list backup of tablespace sysaux;

RMAN> list copy of datafile 3;

Reporting in RMAN

RMAN> report need backup;

Reports which database files need to be backed up to meet a configured
or specified retention policy

RMAN> report unrecoverable;

Reports which database files require backup because they have been
affected by some NOLOGGING operation such as a direct-path insert

RMAN> report need backup recovery window of 7 days;

Displays objects requiring backup to satisfy a recovery window-based
retention policy.

Suppose in the above command we want to skip a perticular tablespace
like perfstat then we can use the below command.

RMAN> report need backup recovery window of 2 days database skip tablespace perfstat;

RMAN> report need backup redundancy 3;

Displays objects requiring backup to satisfy a redundancy-based
retention policy.

RMAN> report need backup days 7;

Displays files that require more than n days’ worth of archived
redo log files for recovery.

RMAN> report need backup incremental 7;

Displays files that require application of more than n incremental
backups for recovery.

RMAN> report need backup redundancy 2 datafile 3;

Gives report of files with less than 2 redundant backups.

RMAN> report need backup tablespace htmldb;

Report of files that must be backed up to satisfy current
retention policy for this tablespace (htmldb)

RMAN> report need backup incremental 2;

Report of files that need more than 2 incrementals during recovery

RMAN> report need backup device type disk;

Report of files that must be backed up to satisfy current
retention policy for database

RMAN> report obsolete;
Shows backups that are obsolete according to the current
retention policy.

You can add the options RECOVERY WINDOW and REDUNDANCY with
this command as given below.

RMAN> report obsolete recovery window of 3 days;

RMAN> report obsolete redundancy 2;

RMAN> report schema;

This command lists and displays information about the database files.

RMAN> report schema at time 'sysdate - 14';

This command gives report on schema 14 days ago.

RMAN> report schema at SCN 10000;

This gives report on schema at scn 10000.

RMAN> report schema at sequence 55 thread 1;

Gives report of schema at sequence 55.

RMAN configuration

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM ‘AES128′; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/u01/app/oracle/oradata/test/dbs/snapcf_test.f’; # default

We can change each of these parameters as per our requirements.

You can return any setting to its default value by using CONFIGURE… CLEAR

RMAN> CONFIGURE BACKUP OPTIMIZATION CLEAR;

RMAN> CONFIGURE RETENTION POLICY CLEAR;

By default, RMAN sends all backups to an operating system
specific directory on disk. So default setting for DEVICE
TYPE is DISK. You can configure to make backups by default
on tape or any other device as given below.

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO sbt;

You can configure backup sets or image copies as the default
for a particular device type, using either of the following commands:

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COPY; # Default becomes image copies
RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET; # Default becomes uncompressed

You can configure RMAN to use compressed backupsets by
default on a particular device type, by using the CONFIGURE
DEVICE TYPE command with the BACKUP TYPE TO COMPRESSED BACKUPSET
option, as shown in the following examples.

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;

RMAN> CONFIGURE DEVICE TYPE sbt BACKUP TYPE TO COMPRESSED BACKUPSET;

To disable compression you can use below command

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET;

RMAN> CONFIGURE DEVICE TYPE sbt BACKUP TYPE TO BACKUPSET;

You can configure the parallelism for a device type.

RMAM> CONFIGURE DEVICE TYPE sbt PARALLELISM 2;

You can turn on/off controlfile autobackup using

RMAN> configure controlfile autobackup off;

RMAN> configure controlfile autobackup on;

We can configure RMAN channel to write backups and images in a specific format.

The following command configures RMAN to write disk backups to the /backup directory

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/backup/ora_df%t_s%s_s%p’;

%t is replaced with a four byte time stamp,
%s with the backup set number, and
%p with the backup piece number.

You can also configure format for controlfile autobackup as well.
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE deviceSpecifier TO ’string’;

For example, you can run the following command:

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘?/oradata/cf_%F’;

Here %F will be replaced by following

c-IIIIIIIIII-YYYYMMDD-QQ, where:

IIIIIIIIII stands for the DBID.
YYYYMMDD is a time stamp of the day the backup is generated
QQ is the hex sequence that starts with 00 and has a maximum of FF

You can clear the format using following command.
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;

References:

Metalink Note ID: 360416.1

http://advait.wordpress.com/2007/08/10/rman-recovery-manager-for-oracle-database-10g/

http://download-uk.oracle.com/docs/cd/B19306_01/backup.102/b14192/toc.htm

Network Related Files

2008-03-04T12:27:00.001+05:30

Oracle uses three files (listener.ora, tnsnames.ora & sqlnet.ora) for network configuration.

Listener.ora
The listerner.ora file contains server side network configuration parameters.

It can be found in the ORACLE_HOME/network/admin directory on the server.

EXAMPLE Listener.ora file:

-------------LINUX----------------
# listener.ora Network Configuration File: /u01/app/oracle/product/10.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.1.0/db_1)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = edrserver)(PORT = 1521))
)
)
)
-------------WINDOWS----------------

# listener.ora Network Configuration File: D:\oracle\product\10.1.0\Db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\10.1.0\Db_1)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Ntws205.rmsi.com)(PORT = 1521))
)
)
)
==================================================================================
After any changes/modifications in the listener.ora file
the listener should be restarted or reloaded to allow the new configuation to take effect.
[oracle@edrserver admin]$lsnrctl stop
[oracle@edrserver admin]$lsnrctl start
or
[oracle@edrserver admin]$lsnrctl reload

Tnsnames.ora

The tnsnames.ora file contains client side network configuration parameters. It can be found in the ORACLE_HOME/network/admin directory on the client. This file will also be present on the server if client style connections are used on the server itself.

Example of a tnsnames.ora file:

--------------WINDOWS--------------------
# tnsnames.ora Network Configuration File: D:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

EDR_205 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ntws205.rmsi.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = edr)
(SERVER = DEDICATED)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

----------------LINUX----------------------
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.1.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

EDR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = edrserver)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = edr)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

NOTE: There are two names “EDR_205” which is called as “TNS_ENTRY” which we use with the connect string like “username/pwd@TNS_ENTRY” & “EDR” which is the service_name (instance_name).
=================================================================================
Sqlnet.ora

The sqlnet.ora file contains client side network configuration parameters. It can be found in the ORACLE_HOME/network/admin directory on the client. This file will also be present on the server if client style connections are used on the server itself.

Example of an sqlnet.ora file:

# sqlnet.ora Network Configuration File: D:\oracle\product\10.1.0\Db_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

====================================

You can find some of the Network related issues and their solutions in the following link:

Error codes and solutions

Establishing a Connection from Oracle to SQL Server

2008-02-16T12:42:00.004+05:30

Here are steps to make a connection from Oracle to SQL Server using Oracles' heterogeneous Services ODBC agent.

Oracle has a generic connectivity methodology that allows for the Oracle database server to access non-Oracle database systems through ODBC and SQL*Net services. Step-by-Step setup to query from a SQL Server database.

1. Define a Data Source Name (DSN) for SQL Server

--> The first step is to define a system DSN within the Windows ODBC Data Sources.

a.From the start menu click on Settings -> Control Panel and double click the ODBC icon.

b.Click on the System DSN tab and then click the Add button.

c.Choose the SQL Server driver since this will be a connection to SQL Server. Click Finish to continue with the data source definition.

d.Key in any name you would like to reference this ODBC data source. I have chose SS for simplistic reasons but it should be descriptive to the database you may be connecting to within SQL Server. You may also describe the data source in any way you wish. This is my remote SQL Server that I will be connecting to. Click Next to continue.

e.I selected Sql Server authentication and also provided Login ID and Password below. Then click Next to continue.

f.Typically, this window is populated with the default SQL Server database of "master." Click the check box to change the default database this ODBC connection should connect to and use the drop down list to select. I have chosen to use the my test Remshr database. Click Next to continue.

g.I also left current window alone and clicked Finish.

h.The next window then appears for you to look at the settings you have configured for the data source. Click Test Data Source to validate your definition.

i.TEST COMPLETED SUCCESSFULLY appears, in which case you have successfully configured the data source. Click OK to close all windows as you are done with the data source definition.

j.The end product should be a valid System DSN. You may in the future click on the DSN name and click the Configure button to change the definition if you like. I did this when I wanted to switch between databases. A small warning here on re-configuration of the DSN: you will need to drop and re-create the database link to activate the DSN. Click OK to exit the DSN administrator.

2. Create a Heterogeneous Services Initialization File

Oracle has provided a sample heterogeneous services init file within the %ORACLE_HOME\hs\admin directory. You will need to copy that file to a new file name within the same directory and edit it for the ODBC DSN you have just created. Below you will find the sample heterogeneous services file Oracle provides and then an edited version, which I have given a new name that corresponds to my DSN name.

%ORACLE_HOME\hs\admin\inithsodbc.ora sample file

(ORACLE_HOME=D:\oracle\product\10.1.0\Db_1)

# This is a sample agent init file that contains the HS parameters that are needed for an ODBC Agent.
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = (odbc data_source_name)
HS_FDS_TRACE_LEVEL = (trace_level)
#
# Environment variables required for the non-Oracle system
#
#set (env var)=(value)

%ORACLE_HOME\hs\admin\initSS.ora altered file

# This is a sample agent init file that contains the HS parameters that are needed for an ODBC Agent.
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = SS
HS_FDS_TRACE_LEVEL = OFF

3. Alter your listener.ora file

Here again Oracle has given us a sample listener.ora file to follow for heterogeneous services within the %ORACLE_HOME\hs\admin directory. Below you will find the sample file and the additions I made to my listener.ora file. I made five distinct changes..
Created my own listener name of LISTENERSS
Changed the Port number to 1522
Changed the SID_NAME to my DSN (SS)
Changed the ORACLE_HOME location
Changed the PROGRAM to hsodbc

%ORACLE_HOME\network\admin\listener.ora altered file

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Ntws205.rmsi.com)(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\10.1.0\Db_1)
(PROGRAM = extproc)
)
)

LISTENERSS =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

SID_LIST_LISTENERSS=
(SID_LIST=
(SID_DESC=
(SID_NAME=SS)
(ORACLE_HOME = D:\oracle\product\10.1.0\Db_1)
(PROGRAM=hsodbc)
)
)

4. Alter your tnsnames.ora file

Here again Oracle has given us a sample tnsnames.ora file to follow for heterogeneous services within the %ORACLE_HOME\hs\admin directory. Below you will find the sample file and the additions I made to my tnsnames.ora file.

1. I made four distinct changes.Created a TNS entry named SS

2. Changed the Port number to 1522

3. Changed the SID to my DSN (SS)

4. Added OK to the HS= parameter

%ORACLE_HOME\network\admin\tnsnames.ora altered file

SS =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
(CONNECT_DATA=(SID=SS))
(HS=OK)
)

5. Start the new Listener

This should be self-explanatory but I provide the output here so that you can know what to expect when you start yours.

D:\>lsnrctl start listenerss

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 15-FEB-2008 16:54:55

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

Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
System parameter file is d:\oracle\product\10.1.0\db_1\network\admin\listener.ora
Log messages written to d:\oracle\product\10.1.0\db_1\network\log\listenerss.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\PNPKEYipc)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
STATUS of the LISTENER
------------------------
Alias listenerss
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Start Date 15-FEB-2008 16:55:09
Uptime 0 days 0 hr. 0 min. 4 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File d:\oracle\product\10.1.0\db_1\network\admin\listener.ora
Listener Log File d:\oracle\product\10.1.0\db_1\network\log\listenerss.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\PNPKEYipc)))
Services Summary...
Service "SS" has 1 instance(s).
Instance "SS", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

6. Validate the connection to your DSN

You can now validate the connection to your SQL Server database by the normal Oracle tnsping utility.

D:\>tnsping ss

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 15-FEB-2008 17:14:57

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

Used parameter files:
D:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)) (CONNECT_DATA=(SID=SS)) (HS=OK))
OK (10 msec)

7. Create a Database Link within Your Oracle Database

Since we would like to connect from our Oracle database and select information from the SQL Server database, we need to create a database link just as if we were connecting to any other remote Oracle database. I have used the default sa login from SQL Server but you may wish to create your own.

SQL> create database link ss
connect to sa identified sa by using 'SS';

Database link created.

This is the fun part as it is the culmination of what we were trying to do. You may describe the tables from SQL Server just as you have done with Oracle in the past and then use a SELECT command. Note that my select statement has double quotes and exact upper and lower cases for the SQL Server query. This is required, at least I tried every combination and none worked except this way.

SQL> desc tblemp@ss
Name Null? Type
----------------------------------------- -------- ----------------------------
EmpNo NOT NULL VARCHAR2(4)
EmpInit NOT NULL VARCHAR2(4)
EmpName VARCHAR2(50)
Salutation VARCHAR2(4)
Nationality VARCHAR2(15)
Sex VARCHAR2(1)
DOB DATE
DOW DATE
Qualification VARCHAR2(15)
-------
-------
-------

SQL>select "EmpNo","EmpInit","EmpName" from tblemp@ss;

Source of this article:
http://www.databasejournal.com/features/oracle/article.php/10893_3442661_1

How to apply a patch ?

2008-02-06T12:36:00.000+05:30

1. You MUST read the Readme.txt file included in opatch file, look for any prereq. steps/ post installation steps or and DB related changes. Also, make sure that you have the correct opatch version required by this patch.

2.Make sure you have a good backup of database.

3. Make a note of all Invalid objects in the database prior to the patch.

4. Shutdown All the Oracle Processes running from that Oracle Home , including the Listener and Database instance, Management agent etc.

5. You MUST Backup your oracle Home and Inventory

tar cvf - $ORACLE_HOME $ORACLE_HOME/oraInventory | gzip > Backup_Software_Version.tar.gz

6. Unzip the patch in $ORACLE_HOME/patches

7. cd to the patch direcory and do opatch -apply to apply the patch.

8. Read the output/log file to make sure there were no errors.

How to Rollback a patch?

opatch rollback -id (Patch Number)

I messed up my Oracle home/ opatch rollback is failing , what to do now?

Remember, we took the backup of the ORACLE_HOME and main importantly the Oracle inventory, you can just restore (uncompress) the software and the inventory and you are all set. Also for fixing the corrupt inventory, please refer to this OracleMetaLink Note:298906.1

Critical Patch Update JAN 2008 Linux

2008-02-06T12:18:00.000+05:30

Oracle Critical Patch Update Advisory - January 2008
OracleCPU Advisory

Below procedure based on my test enviourment if you follow this instruction please go through README.TXT file that comes with PATCH SET.

Patch Installation Procedures for Oracle Database Release 11.1.0.6.0
Patch Number : p6646866_111060_LINUX.zip

Download it from OracleMetaLink

SYSTEM Overview

Platforms : Oracle Enterprise Linux 5
Database : 11.1.0.6.0

Shutdown All the Oracle Processes running from that Oracle Home , including the Listener and Database instance, Management agent etc.

OPatch Utility Information

You must use the OPatch utility release 1.0.0.0.57 or later. You can download it from
OracleMetaLink.

Check OPatch Version

Oracle 10G onwards, the RDBMS software ships with OPatch and it can be loacated under $ORACLE_HOME/OPatch. To download, opatch you need to have an metalink.oracle.com account. On OracleMetaLink search for Patch 4898608, select the Oracle RDBMS version and download the patch.

[oracle@oracle11gr1]$ cd $ORACLE_HOME
[oracle@oracle11gr1 db_1]$ cd OPatch/
[oracle@oracle11gr1 OPatch]$ sh opatch version
Invoking OPatch 11.1.0.6.2

OPatch Version: 11.1.0.6.2

OPatch succeeded.

[oracle@oracle11gr1 OPatch]$ sh opatch napply /u01/app/oracle/product/11.1.0/db_1/6646866/ -skip_subset -skip_duplicate

Invoking OPatch 11.1.0.6.2

[Output Edited]
OPatch continues with these patches: 6646866 6650132 6650135 6731395

Do you want to proceed? [y|n]
Y
User Responded with: Y
Running prerequisite checks...
[Output Edited]

Is the local system ready for patching? [y|n]
Y
User Responded with: Y
[Output Edited]

Execution of 'sh /u01/app/oracle/product/11.1.0/db_1/6646866/6646866/custom/scripts/post -apply 6646866 ':

Return Code = 0

The local system has been patched and can be restarted.

UtilSession: N-Apply done.

OPatch succeeded.

Post Installation Instructions

[oracle@oracle11gr1 OPatch]$
After installing the patch, perform the following actions:

Loading Modified .sql Files into the Database

There are no steps to perform, because the CPU for Release 11.1.0.6 does not use catcpu.sql or related scripts.

You can go directly to Recompiling Views in the Database

You must recompile views for all databases except the following:

Databases created with Release 11.1.0.6 or later

Databases created with any release (for example, 10.2.0.3, 10.1.0.5, or 9.2.0.8) after CPUJan2008 or a later CPU has been applied

The time required to recompile the views and related objects depends on the total number of objects and on your system configuration. In one internal Oracle test with approximately 2000 views and 4000 objects, the total execution time for view_recompile_jan2008cpu.sql and utlrp.sql was about 30 minutes.

If you want to check whether view recompilation has already been performed for the database, execute the following statement.

SELECT * FROM registry$history where ID = '6452863';

[oracle@oracle11gr1]$ sqlplus /nolog

SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
Database opened.
SQL>
SQL> SELECT * FROM registry$history where ID = '6452863';
no rows selected

If the view recompilation has been performed, this statement returns one row. If the view recompilation has not been performed, this statement returns no rows.

To recompile the views in the database, follow these steps:

-->Run the pre-check script, which reports the maximum number of views and objects that may be recompiled:

cd $ORACLE_HOME/cpu/view_recompile
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> @recompile_precheck_jan2008cpu.sql

Running precheck.sql...

Number of views to be recompiled :2715
-----------------------------------------------------------------------

Number of objects to be recompiled :5523
Please follow the README.txt instructions for running viewrecomp.sql

PL/SQL procedure successfully completed.

The purpose of this step is to help you determine whether view recompilation should be done at the same time as the CPU install, or scheduled later.

-->Run the view recompilation script. Note that this script is run with the database in upgrade mode, which restricts connections as SYSDBA.

cd $ORACLE_HOME/cpu/view_recompile
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP UPGRADE
SQL> @view_recompile_jan2008cpu.sql
PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

1 row created.

Commit complete.

No. of Invalid Objects is :31
Please refer to README.html to for instructions on validating these objects

PL/SQL procedure successfully completed.

Logfile for the current viewrecomp.sql session is : vcomp_ORCL_04Feb2008_16_10_52.log

SQL> SHUTDOWN;
SQL> STARTUP;
SQL> exit

If any invalid objects were reported, run the utlrp.sql script as follows:

[oracle@oracle11gr1 view_recompile]$ cd $ORACLE_HOME/rdbms/admin
[oracle@oracle11gr1 admin]$ sqlplus /nolog
SQL> conn /as sysdba
Connected.
SQL> @utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2008-02-04 16:18:22

[Output Edited]
PL/SQL procedure successfully completed.

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2008-02-04 16:18:50

PL/SQL procedure successfully completed.

[Output Edited]
OBJECTS WITH ERRORS
-------------------
0
[Output Edited]
ERRORS DURING RECOMPILATION
---------------------------
0
PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

SQL> SELECT * FROM registry$history where ID = '6452863';

ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS
---------------------------- -------- ---------- -------- ---------- ----------
04-FEB-08 04.13.43.702093 PM CPU 6452863 CPUJan2008

Still if you find any invalid objects then, manually recompile them. For example:
SQL> alter package schemaname.packagename compile;

Critical Patch Update JAN 2008 on Windows

2008-02-06T12:00:00.000+05:30

Oracle Critical Patch Update Advisory - January 2008
OracleCPU Advisory

Below procedure based on my test enviourment if you follow this instruction please go through README.TXT file that comes with PATCH SET.

Patch Installation Procedures for Oracle Database Release 10.1.0.5
Patch Number : p6637274_10105_WINNT.zip

SYSTEM Overview

Platforms : Windows XP SP2
Database : 10.1.0.5.0

Shutdown All the Oracle Processes running from that Oracle Home , including the Listener and Database instance, Management agent etc.

OPatch Utility Information

You must use the OPatch utility release 1.0.0.0.53 or later. You can download it from
OracleMetaLink with patch 2617419.

Check Oracle Inventory Setup

Oracle 10G onwards, the RDBMS software ships with OPatch and it can be loacated under $ORACLE_HOME/OPatch. To download, opatch you need to have an metalink.oracle.com account. On OracleMetaLink search for Patch 4898608 , select the Oracle RDBMS version and download the patch.

set ORACLE_SID=ARIES
set ORACLE_HOME=D:\oracle\product\10.1.0\Db_1
set PERL5LIB=D:\oracle\product\10.1.0\db_1\perl\5.6.1\lib;%PERL5LIB%
set PATH=D:\oracle\product\10.1.0\db_1\perl\5.6.1\bin\MSWin32-x86;%PATH%

D:\patchset10gR1\6637274>cd D:\oracle\product\10.1.0\db_1\opatch

D:\oracle\product\10.1.0\Db_1\OPatch>opatch lsinventory
[Output Edited]
Creating log file "D:\oracle\product\10.1.0\db_1\.patch_storage\LsInventory__02-05-2008_10-57-56.log"

Result:

There is no Interim Patch

OPatch succeeded.
OPatch returns with error code = 0

D:\oracle\product\10.1.0\Db_1\OPatch>

==================
D:\oracle\product\10.1.0\Db_1\OPatch>opatch apply D:\patchset10gR1\6637274

[Output Edited]
Creating log file "D:\oracle\product\10.1.0\db_1\.patch_storage\6637274\Apply_6637274_02-05-2008_11-06-12.log"

[Output Edited]
Is this system ready for updating?
Please respond Y|N >
Y
Executing the Apply pre-patch script (D:\patchset10gR1\6637274\custom\scripts\pre.bat)...
Applying patch 6637274...

[Output Edited]
Updating inventory...
Backing up comps.xml ...

********************************************************************************
** ATTENTION **
** **
** Please note that the Security Patch Installation (Patch Deinstallation) is **
** not complete until all the Post Installation (Post Deinstallation) **
** instructions noted in the Readme accompanying this patch, have been **
** successfully completed. **
** **
********************************************************************************
********************************************************************************
Executing the Apply post-patch script (D:\patchset10gR1\6637274\custom\scripts\post.bat)...

OPatch succeeded.
OPatch returns with error code = 0

D:\oracle\product\10.1.0\Db_1\OPatch>

====================================
CD D:\oracle\product\10.1.0\db_1\CPU\cpujan2008

D:\oracle\product\10.1.0\Db_1\cpu\cpujan2008>sqlplus /nolog
SQL> conn sys/sys as sysdba
Connected.
SQL> select name from v$instance;

INSTANCE_NAME
---------------
aries
SQL> shutdown immediate;

SQL> STARTUP MIGRATE
ORACLE instance started.
Database opened.
SQL>@CATCPU.SQL

[OUTPUT EDITED]
Package body created.
Package body created.
Package body created.
Session altered.

1 row selected.

Session altered.

OWA_MESSAGE
--------------------------------------------------------------------------------
Installed OWA version is: 9.0.4.0.2

Backing up previous OWA packages to: MODPLSQL\owa_restore_aries.sql

OWA_MESSAGE
-------------------

OWALOAD File: MODPLSQL\owa_all\90\owaload_patch.sql

Logfile: MODPLSQL\owa_patch_aries.log

OWA_DBG_MSG

-------------------
Installed OWA version is: 9.0.4.0.2;
Shipped OWA version is : 9.0.4.0.6;
OWA packages v9.0.4.0.6 will be installed into your database v101050
Will install owadummy.sql and owacomm.sql
[Output Edited]
No errors.
No. of Invalid Objects is :115
Please refer to README.html to for instructions on validating these objects
Logfile for the current catcpu.sql session is : APPLY_ARIES_05Feb2008_11_53_51.log
not spooling currently
SQL> SHUTDOWN IMMEDIATE
SQL> exit
D:\oracle\product\10.1.0\Db_1\cpu\cpujan2008>

If catcpu.sql reports any Invalid Objects, compile the invalid objects as follows

cd D:\oracle\product\10.1.0\db_1\rdbms\admin

D:\oracle\product\10.1.0\Db_1\RDBMS\ADMIN>sqlplus /nolog

SQL> conn sys/sys as sysdba
Connected to an idle instance.
SQL> startup
Database opened.

SQL> @utlprp.sql 0

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2008-02-05 12:08:43

PL/SQL procedure successfully completed.

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2008-02-05 12:08:43

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

SQL> select OBJECT_NAME from dba_objects where status = 'INVALID';

no rows selected

SQL> exit

Run the CATCPU.sql and UTLRP.sql on all the instances in the ORACLE_HOME individually.

To view the details about the patch applied :

select * from dba_registry_history;

SELECT * FROM registry$history where ID = '6452863';

ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS
---------------------------- -------- ---------- -------- ---------- ----------
05-FEB-08 12.30.36.406000 PM CPU 6637274 CPUJan2008

Still if you find any invalid objects then, manually recompile them. For example:

SQL> alter package schemaname.packagename compile;

TNS less connection in Oracle.

2008-01-24T17:23:00.000+05:30

I have tested this connection method in Oracle 8i, 9i, 10g and in 11g and it works fine.

D:\>sqlplus /nolog

SQL*Plus: Release 10.1.0.5.0 - Production on Thu Jan 24 16:59:44 2008

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

SQL> conn scott/tiger@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
(Host=ntwssrv)(Port=1521))(CONNECT_DATA=(SID=ORCL)))
Connected.
SQL>

If you think about above connection, it is more like specifying complete address in the connect string as you have in the tnsnames.ora file.

For 10g and 11g you can use following method as well. This method does not work for 8i or 9i databases.

==========
D:\>sqlplus /nolog

SQL*Plus: Release 10.1.0.5.0 - Production on Thu Jan 24 17:09:47 2008

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

SQL> conn scott/tiger@ntwssrv:1521/aries
Connected.

===========
[oracle@oracle11gr1 oracle]$ sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Jan 24 17:27:40 2008

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

SQL> conn scott/tiger@oracle11gr1:1521/orcl
Connected.
SQL>

Resolving ORA-12705 on Client

2008-01-22T14:48:00.000+05:30

This tip resolves Oracle client error: “ORA-12705: Cannot access NLS data files or invalid environment specified“. According to Oracle documentation, “ORA-12705 can happen under the following conditions:

1. ORA-12705 with incorrect NLS_LANG parameter
2. ORA-12705 with incorrectly specified ORA_NLSx
3. ORA-12705 from incorrect Oracle install or configuration
4. ORA-12705 when using Special Character sets
5. ORA-12705 when connecting with SQL*Net
6. ORA-12705 during migrations
7. ORA-12705 when connecting with SQL*Plus
8. ORA-12705 when connecting with language pre-compilers
9. ORA-12705 during Export/Import

Try doing the following to fix this error on the client side. On the client Windows machine (this tip is only for Windows), The NLS_LANG must be unset in the Windows registry (re-naming works the best because you know what you changed). Look for the NLS_LANG subkey in the registry at \HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE, and rename it. Then try SQLPlus again.

Limit user sessions in Oracle

2008-01-21T10:55:00.000+05:30

How can I limit the number of times a single user can connect to the database at any one time ?

We can do this by limiting the number of sessions they are allowed to have.
Easy as always to do in Oracle. First we allow resource limits by setting the resource_limit to true, then we simply create a profile and assign that to the user. The profile can set the limit of how many session a user is allowed to have.

A little demonstration:

D:\>sqlplus sys/sys as sysdba

SQL*Plus: Release 10.1.0.5.0 - Production on Mon Jan 21 10:46:28 2008

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

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

SQL> show parameter resource_limit

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_limit boolean FALSE

SQL> alter system set RESOURCE_LIMIT=true scope=both;

System altered.

SQL> create profile sesslimit limit sessions_per_user 1;

Profile created.

SQL> create user sree identified by sree123
default tablespace sbdata profile sesslimit;

User created.

SQL> grant create session to sree;

Grant succeeded.

If you want to add this profile to existing user then:

SQL> alter user sree profile sesslimit;

SQL> connect sree/sree123
Connected.

-- I'll start second session in another terminal.

SQL> connect sree/sree123
ERROR:
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit

==========

SQL> SELECT DISTINCT resource_name, limit
FROM dba_profiles
ORDER BY resource_name;

SQL> drop profile sesslimit cascade;

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production With the Partitioning, OLAP and Data Mining options

IMPORTANT LINKS

2008-01-17T11:10:00.000+05:30

Oracle By Example (OBE)

The Oracle by Example (OBE) series provides hands-on, step-by-step instructions on how to implement various technology solutions to business problems. OBE solutions are built for practical real-world situations, allowing you to gain valuable hands-on experience as well as use the presented solutions as the foundation for production implementation, dramatically reducing time to deployment.
Direct Link to all Articles

Installing Oracle Database 11g on Linux
Direct Link to Article

Installing Oracle Database 11g on Windows
Direct Link to Article

Exploring Your Database with Enterprise Manager Database Control
Direct Link to Article

Using Database Replay to Perform Real-World Testing
Direct Link to Article

Creating a Physical Standby Database
Direct Link to Article

Oracle by Example Series: Oracle Database 11g
Direct Link to Articles

Oracle by Example Series: Oracle Database 10g
Direct Link to Articles

Automating Installation of Oracle Database 10g and Red Hat Enterprise Linux
Direct Link to Article

RAC LINKS

Biggest challenge in learning / understanding RAC is lack of hands on experience because of expensive hardware, network cards, interconnect and cluster file system. The following links cover step by step Oracle RAC installation and configuration
on single laptop/desktop using VMWare Server without investing anything extra on hardware (using two virtual machines, virtual network cards and virtual shared disk for Oracle RAC database shared files system ).

Download VMware Server (for Windows and Linux systems)
Download VMWARE

Installing Oracle Database 10g on Windows by Using Real Application Clusters (RAC) and Automated Storage Management (ASM)
Direct Link to Article

Steps for configuring/building Oracle RAC Database on laptop/desktop machine (without investing any thing on hardware).
Direct Link to Article

Oracle 10g RAC installation using Unbreakable Linux
Direct Link to Article

Installing Oracle RAC 10g Release 1 on Linux x86
Direct Link to Article

Installation of Oracle 10g release 2 (10.2.0.1) RAC on Linux (CentOS 4) using VMware Server with no additional shared disk devices.
Direct Link to Article

Installing Oracle RAC 10g Release 2 on Linux x86
Direct Link to Article

Oracle 10g RAC On Windows 2003 Using VMware Server
Direct Link to Article

Install Oracle RAC 10g on Oracle Enterprise Linux Using VMware Server
Direct Link to Article

The Oracle-on-Linux Installation Menu
Direct Link to Article

Installing Oracle Database 10g with Real Application Cluster (RAC) on Red Hat Enterprise Linux Advanced Server 3
Direct Link to Article

Convert a Single Instance Database to Oracle RAC 10g on RHEL3
Direct Link to Article

Code to shrink Your Tablespaces

2008-01-07T15:01:00.000+05:30

Use it at your own risk.

SET verify OFF
COLUMN file_name format a50 WORD_WRAPPED
COLUMN smallest format 999,990 heading "Smallest|Size|Poss."
COLUMN currsize format 999,990 heading "Current|Size"
COLUMN savings format 999,990 heading "Poss.|Savings"
BREAK on REPORT
COMPUTE sum of savings on REPORT
COLUMN value new_val BLKSIZE
SELECT VALUE
FROM V$PARAMETER
WHERE NAME = 'db_block_size';

SELECT FILE_NAME,
CEIL((NVL(HWM,1) *&& BLKSIZE) / 1024 / 1024) SMALLEST,
CEIL(BLOCKS *&& BLKSIZE / 1024 / 1024) CURRSIZE,
CEIL(BLOCKS *&& BLKSIZE / 1024 / 1024) - CEIL((NVL(HWM,1) *&& BLKSIZE) / 1024 / 1024) SAVINGS
FROM DBA_DATA_FILES A,
(SELECT FILE_ID,
MAX(BLOCK_ID + BLOCKS - 1) HWM
FROM DBA_EXTENTS
GROUP BY FILE_ID) B
WHERE A.FILE_ID = B.FILE_ID (+);

SELECT 'alter database datafile '''
||File_Name
||''' resize '
||Ceil((Nvl(hwm,1) *&& blkSize) / 1024 / 1024)
||'m;' cmd
FROM dba_Data_Files a,
(SELECT File_Id,
MAX(Block_Id + Blocks - 1) hwm
FROM dba_Extents
GROUP BY File_Id) b
WHERE a.File_Id = b.File_Id (+)
AND Ceil(Blocks *&& blkSize / 1024 / 1024) - Ceil((Nvl(hwm,1) *&& blkSize) / 1024 / 1024) > 0;

Cool Scripts for daily DBA activities

2008-01-07T12:51:00.000+05:30

I will be adding scripts to this post when ever I come across a good one.

Note: Test the scripts before using on a Production database.
Finely Format the SQL statements before use.

Calculate the Database Size

COLUMN "Total Mb" FORMAT 999,999,999.0
COLUMN "Redo Mb" FORMAT 999,999,999.0
COLUMN "Temp Mb" FORMAT 999,999,999.0
COLUMN "Data Mb" FORMAT 999,999,999.0

Prompt
Prompt "Database Size"

select (select sum(bytes/1048576) from dba_data_files) "Data Mb",
(select NVL(sum(bytes/1048576),0) from dba_temp_files) "Temp Mb",
(select sum(bytes/1048576)*max(members) from v$log) "Redo Mb",
(select sum(bytes/1048576) from dba_data_files) +
(select NVL(sum(bytes/1048576),0) from dba_temp_files) +
(select sum(bytes/1048576)*max(members) from v$log) "Total Mb"
from dual;
=========================================================
Heavy CPU SQL

This will generate the top SQL statements that produce heavy CPU usage

set termout on
set feedback on
set pagesize 132

#spool cpusql.lis

SELECT username,address, hash_value,
buffer_gets, executions, buffer_gets/executions "Gets/Exec",sql_text
FROM v$sqlarea,dba_users
WHERE buffer_gets > 50000
and executions > 0
and v$sqlarea.parsing_user_id = dba_users.user_id
order by 4 desc;

#spool off;
=========================================================
Calculate the Table Size

SELECT Segment_Name Table_Name,
SUM(Bytes) / (1024 * 1024) Table_Size_Meg
FROM dba_Extents
WHERE Owner = 'SCOTT'
AND Segment_Name = 'DEPT'
AND Segment_Type = 'TABLE'
GROUP BY Segment_Name
/

=============================================================================
Determine Tablespace Usage

SELECT a.TableSpace_Name,
Round(a.Bytes_Alloc / 1024 / 1024,2) Megs_Alloc,
Round(Nvl(b.Bytes_Free,0) / 1024 / 1024,2) Megs_Free,
Round((a.Bytes_Alloc - Nvl(b.Bytes_Free,0)) / 1024 / 1024,
2) Megs_Used,
Round((Nvl(b.Bytes_Free,0) / a.Bytes_Alloc) * 100,
2) pct_Free,
100 - Round((Nvl(b.Bytes_Free,0) / a.Bytes_Alloc) * 100,
2) pct_Used,
Round(MaxBytes / 1048576,2) MAX
FROM (SELECT f.TableSpace_Name,
SUM(f.Bytes) Bytes_Alloc,
SUM(DECODE(f.AutoexTensible,'YES',f.MaxBytes,
'NO',f.Bytes)) MaxBytes
FROM dba_Data_Files f
GROUP BY TableSpace_Name) a,
(SELECT f.TableSpace_Name,
SUM(f.Bytes) Bytes_Free
FROM dba_Free_Space f
GROUP BY TableSpace_Name) b
WHERE a.TableSpace_Name = b.TableSpace_Name (+)
UNION ALL
SELECT h.TableSpace_Name,
Round(SUM(h.Bytes_Free + h.Bytes_Used) / 1048576,
2) Megs_Alloc,
Round(SUM((h.Bytes_Free + h.Bytes_Used) - Nvl(p.Bytes_Used,0)) / 1048576,
2) Megs_Free,
Round(SUM(Nvl(p.Bytes_Used,0)) / 1048576,2) Megs_Used,
Round((SUM((h.Bytes_Free + h.Bytes_Used) - Nvl(p.Bytes_Used,0)) / SUM(h.Bytes_Used + h.Bytes_Free)) * 100,
2) pct_Free,
100 - Round((SUM((h.Bytes_Free + h.Bytes_Used) - Nvl(p.Bytes_Used,0)) / SUM(h.Bytes_Used + h.Bytes_Free)) * 100,
2) pct_Used,
Round(SUM(f.MaxBytes) / 1048576,2) MAX
FROM sys.v_$temp_Space_Header h,
sys.v_$temp_Extent_Pool p,
dba_temp_Files f
WHERE p.File_Id (+) = h.File_Id
AND p.TableSpace_Name (+) = h.TableSpace_Name
AND f.File_Id = h.File_Id
AND f.TableSpace_Name = h.TableSpace_Name
GROUP BY h.TableSpace_Name
ORDER BY 1
-------------------------------------------------------------------------
SELECT TableSpace_Name "Tablespace",
COUNT(Bytes) "Pieces",
MIN(Bytes) "Min",
Round(Avg(Bytes)) "Average",
MAX(Bytes) "Max",
SUM(Bytes) "Total"
FROM sys.dba_Free_Space
GROUP BY TableSpace_Name

==========================================================================
Track your import process:

SELECT Substr(sql_Text,Instr(sql_Text,'INTO "'),30) Table_Name,
Rows_Processed,
Round((SYSDATE - To_date(First_Load_Time,'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,
1) Minutes,
Trunc(Rows_Processed / ((SYSDATE - To_date(First_Load_Time,'yyyy-mm-dd hh24:mi:ss')) * 24 * 60)) Rows_Per_Minute
FROM sys.v_$sqlArea
WHERE sql_Text LIKE 'INSERT %INTO "%'
AND Command_Type = 2
AND Open_Versions > 0;
===============================================================================
Details of parameters for SPfile modifications

SELECT NAME,
Isses_modIfiAble,
Issys_modIfiAble,
IsInstance_modIfiAble
FROM v$Parameter
ORDER BY NAME;
==============================================================================
Query to find the difference between two dates omitting weekends and holidays.

SELECT (To_date('31/01/2008','dd/mm/rrrr') - To_date('01/01/2008','dd/mm/rrrr') + 1) - (SELECT COUNT(Days)
FROM (SELECT To_char(To_date('01/01/2008','dd/mm/rrrr') + LEVEL,'D') Days
FROM Dual CONNECT BY LEVEL <= 31) WHERE Days IN ('7','1')) DAY
FROM Dual;
===============================================================================

Deleting Duplicate Records

2008-01-07T12:46:00.000+05:30

You may have come across numerous situations where duplicate records needs to be deleted from some table quickly. More often than not this is due to an application or data transformation issue and the number of duplicate rows are generally small related to the overall number of rows in the table. DBAs struggle with this task and spend way too much time and so coming up with elaborate scripts.

Assuming the table looks like this:

ACCOUNT
ACCOUNT_ID
STATUS_CODE
FIRST_NAME
LAST_NAME
STREET_ADDRESS

In this example, there is no primary key or unique constraint preventing duplicates from being inserted. The "logical primary key" is ACCOUNT_ID and STATUS_CODE.

First we might want to view the duplicate records to assist with troubleshooting and this can be done with this query:

SELECT Account_Id,
Status_Code,
COUNT(* ) OccurAnces
FROM ACCOUNT
GROUP BY Account_Id,Status_Code
HAVING COUNT(* ) > 1;

And to delete them we rely on Oracle’s rowed which is a unique "key" that exists on every table row:

DELETE
FROM ACCOUNT A
WHERE
(ACCOUNT_ID,STATUS_CODE) IN (
SELECT
ACCOUNT_ID,
STATUS_CODE,
FROM ACCOUNT
GROUP BY
ACCOUNT_ID,
STATUS_CODE
HAVING
COUNT(*) > 1) AND
ROWID != (
SELECT
MIN(ROWID)
FROM ACCOUNT
WHERE
ACCOUNT_ID = A.ACCOUNT_ID AND
STATUS_CODE = A.STATUS_CODE);

Alternatively this more simplified query can be used, but in some cases it might not execute as fast as the above query because of having to do more random I/O:

DELETE FROM ACCOUNT a
WHERE RowId != (SELECT MIN(RowId)
FROM ACCOUNT
WHERE Account_Id = a.Account_Id
AND Status_Code = a.Status_Code);

Please make sure that you thoroughly understand your duplicate record problem and what makes a record unique before deleting duplicate records in a table. Make sure you test the above SQL statements in a test environment with a representative test case before attempting this in a production environment

DBVERIFY

2008-01-07T12:43:00.000+05:30

DBVERIFY is an external command-line utility that performs a physical data structure integrity check. It can be used on offline or online databases, as well on backup files. You use DBVERIFY primarily when you need to ensure that a backup database (or datafile) is valid before it is restored, or as a diagnostic aid when you have encountered data corruption problems.

DBVERIFY checks are limited to cache-managed blocks (that is, data blocks). Because DBVERIFY is only for use with datafiles, it will not work against control files or redo logs.

There are two command-line interfaces to DBVERIFY

1.Using DBVERIFY to Validate Disk Blocks of a Single Datafile

D:/>dbv FILE=D:\oracle\product\10.1.0\oradata\ORCL\users.dbf FEEDBACK=100

------------------------------------------------------------

2.Using DBVERIFY to Validate a Segment
If we want to check segment level you can use SEGMENT_ID

SEGMENT_ID = Segment ID (tsn.relfile.block)

we can get all three values from below query.

SQL> Grant sysdba to scott;

SQL>SELECT t.ts#,
s.Header_File,
s.Header_Block
FROM v$TableSpace t,
dba_Segments s
WHERE s.Owner = 'SCOTT'
AND s.Segment_Name = 'EMP'
AND t.NAME = s.TableSpace_Name;

TS# HEADER_FILE HEADER_BLOCK
---------- ----------- ------------
4 4 27

SQL> host dbv userid=scott/tiger segment_id=4.4.27

New Year Wishes to all my Guests....

2007-12-31T17:54:00.000+05:30

May the dawning of this New Year, fill your heart with new hopes, open up new horizons and bring for you promises of brighter tomorrows. May you have a great New Year - 2008.

Some of the Oracle Database 11g New Features

2007-12-30T10:16:00.000+05:30

Oracle Database 11g Release 1 (11.1) New Features
=================================================

Case Sensitive Passwords

Beginning with Oracle Database 11g Release 1, database passwords are case sensitive.
You can disable this features by setting the SEC_CASE_SENSITIVE_LOGON initialization parameter to FALSE.

=======================================
Readonly Tables

To place a table in read-only mode, you must have the ALTER TABLE privilege on thetable or the ALTER ANY

TABLE privilege. In addition, the COMPATIBILE initializationparameter must be set to 11.1.0 or greater.

The following example places the EMP table in read-only mode:

ALTER TABLE EMP READ ONLY;

The following example returns the table to read/write mode:

ALTER TABLE EMP READ WRITE;

=========================================

Invisible Indexes

An invisible index is an index that is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level. Making an index invisible is an alternative to making it unusable or dropping it.

Using invisible indexes, you can do the following:

Test the removal of an index before dropping it.

Altering Indexes

Use temporary index structures for certain operations or modules of an application without affecting the overall application.

To create an invisible index, use the SQL statement CREATE INDEX with theINVISIBLE clause. The following statement creates an invisible index named IND_EMP_DEPT for the DEPT column of the EMP table:

CREATE INDEX ind_emp_dept ON emp(dept)INVISIBLE;

To make an invisible index visible, issue this statement:

ALTER INDEX ind_emp_dept VISIBLE;

To make a visible index invisible, issue this statement:

ALTER INDEX ind_emp_dept INVISIBLE;

To check whether an index is visible or invisible, query the dictionary views

USER[ALL][DBA]_INDEXES

For example, to determine if above index is invisible, issue the following query:

SELECT INDEX_NAME, VISIBILITY FROM
USER_INDEXES WHERE INDEX_NAME = 'IND_EMP_DEPT';

INDEX_NAME VISIBILITY
------------- ----------
IND_EMP_DEPT VISIBLE

===========================================

Automatic Stats Collection on Tables

As you know that you can enable DBMS_STATS to automatically gather statistics for a table by specifying the MONITORING keyword in the CREATE (or ALTER) TABLE statement. But, starting with Oracle Database 11g, the MONITORING and NOMONITORING keywords have been deprecated and statistics are collected automatically. If you do specify these keywords, they are ignored.

Monitoring tracks the approximate number of INSERT, UPDATE, and DELETE operations for the table since the last time statistics were gathered. Information abouthow many rows are affected is maintained in the SGA, until periodically (about everythree hours) SMON incorporates the data into the data dictionary.

This data dictionary information is made visible through the DBA_TAB_MODIFICATIONS, ALL_TAB_MODIFICATIONS, or USER_TAB_MODIFICATIONS views. The database uses these
views to identify tables with stale statistics.

=================================================

Virtual columns

Tables can now include virtual columns. The value of a virtual column in a row is derived by evaluating an expression. The expression can include columns from the same table, constants, SQL functions, and user-defined PL/SQL functions. In some cases, a virtual column eliminates the need to create a separate view. You can create an index on a virtual column, and you can use a virtual column as a partition or subpartition key.

==============================================

Simplified and improved automatic memory management

You can now set a single initialization parameter (MEMORY_TARGET) to indicate the total amount of memory that is to be allocated to the database (the SGA and instance PGA). The system then automatically and dynamically tunes all SGA and PGA components for optimal performance. You can still designate minimum sizes individually for the SGA and instance PGA.

===============================================

Database resident connection pooling

Database resident connection pooling (DRCP) provides a connection pool in the database server for typical Web application usage scenarios where the application acquires a database connection, works on it for a relatively short duration, and then releases it. DRCP pools "dedicated" servers, which are the equivalent of a server foreground process and a database session combined. DRCP enables sharing of
database connections across middle-tier processes on the same middle-tier host and across middle-tier hosts. This results in significant reduction in database resources needed to support a large number of client connections, thereby boosting the scalability of both middle-tier and database tiers.

==================================================

Tablespace-level encryption

You can encrypt any permanent tablespace to protect sensitive data. Tablespace encryption is completely transparent to your applications. When you encrypt a tablespace, all tablespace blocks are encrypted.

All segment types are supported for encryption, including tables, clusters, indexes, LOBs, table and index partitions, and so on.

==========================================================

Result cache in the system global area

Results of queries and query fragments can be cached in memory in the result cache. The database can then use cached results to answer future executions of these queries and query fragments. Because retrieving results from the result cache is faster than rerunning a query, frequently run queries experience a significant performance improvement when their results are cached.

The result cache occupies memory in the shared pool.

=============================================================

Enhanced online index creation and rebuild

Online index creation and rebuild prior to this release required a DML-blocking lock at the beginning and at the end of the rebuild for a short period of time. This lock could delay other DML statements and therefore cause a performance spike. This lock is no longer required, making these online index operations fully transparent.

===============================================================

Ability to online redefine tables that have materialized view logs

Tables with materialized view logs can now be redefined online. Materialized view logs are now one of the dependent objects that can be copied to the interim table with the DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS package procedure.

=====================================================================

Optimized ALTER TABLE...ADD COLUMN

For certain types of tables, when adding a column that has both a NOT NULL constraint and a default value, the database can optimize the resource usage and storage requirements for the operation. It does so by storing the default value for the new column as table metadata, avoiding the need to store the value in all existing records.

In addition, the following ADD COLUMN operations can now run concurrently with DML operations:

Add a NOT NULL column with a default value

Add a nullable column without a default value

Add a virtual column
==========================================================

Oracle Express Editon

2007-12-28T12:51:00.000+05:30

These are just some general points that might save you a bit of time and head scratching if you decide to give Oracle XE a go:

First, let's start with the good:

It's free to download and develop on
It's free to deploy (including distribution, as long as you make the other party aware of the licensing details)
It is the first really simple installation on either Windows or Linux that I have ever seen Oracle do
The included tools are superb and very modern and AJAXy

However, there are limitations:

--> Although you can install it on a multiple CPU machine, and it will run on one, it will only use one CPU on that machine.

--> It will only use a maximum 1 gigabyte of memory (again, it will run on machines with more memory; it just won't use it)

--> It has a database size limit of 4 gigabytes for user data (this is all available to the user; system data is counted outside of that 4 gig limit)

--> Only one instance of Oracle XE can run on any given computer.

You can download Oracle XE software from following link:
Download Link

Locally vs. Dictionary Managed Tablespaces

2007-12-28T11:46:00.000+05:30

When Oracle allocates space to a segment (like a table or index), a group of contiguous free blocks, called an extent, is added to the segment. Metadata regarding extent allocation and unallocated extents are either stored in the data dictionary, or in the tablespace itself. Tablespaces that record extent allocation in the dictionary, are called dictionary managed tablespaces, and tablespaces that record extent allocation in the tablespace header, are called locally managed tablespaces.

SQL> select tablespace_name, extent_management, allocation_type from dba_tablespaces;

TABLESPACE_NAME EXTENT_MAN ALLOCATIO
------------------------------ ---------- ---------
SYSTEM -- DICTIONARY -USER
UNDOTBS1 -- LOCAL -SYSTEM
TEMP -- LOCAL -UNIFORM
USERS -- LOCAL -SYSTEM

Dictionary Managed Tablespaces (DMT):

Oracle use the data dictionary (tables in the SYS schema) to track allocated and free extents for tablespaces that is in "dictionary managed" mode. Free space is recorded in the SYS.FET$ table, and used space in the SYS.UET$ table. Whenever space is required in one of these tablespaces, the ST (space transaction) enqueue latch must be obtained to do inserts and deletes agianst these tables. As only one process can acquire the ST enque at a given time, this often lead to contention.

Execute the following statement to create a dictionary managed tablespace:

SQL> CREATE TABLESPACE tblspc1 DATAFILE '/oradata/tblspc1_01.dbf' SIZE 50M
EXTENT MANAGEMENT DICTIONARY
DEFAULT STORAGE ( INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 0);

Locally Managed Tablespaces (LMT):

Using LMT, each tablespace manages it's own free and used space within a bitmap structure stored in one of the tablespace's data files. Each bit corresponds to a database block or group of blocks. Execute one of the following statements to create a locally managed tablespace:

SQL> CREATE TABLESPACE tblspc2 DATAFILE '/oradata/tblspc2_01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

SQL> CREATE TABLESPACE tblspc3 DATAFILE '/oradata/tblspc3_01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

Note the difference between AUTOALLOCATE and UNIFORM SIZE:

AUTOALLOCATE specifies that extent sizes are system managed. Oracle will choose "optimal" next extent sizes starting with 64KB. As the segment grows larger extent sizes will increase to 1MB, 8MB, and eventually to 64MB. This is the recommended option for a low or unmanaged environment.

UNIFORM specifies that the tablespace is managed with uniform extents of SIZE bytes (use K or M to specify the extent size in kilobytes or megabytes). The default size is 1M. The uniform extent size of a locally managed tablespace cannot be overridden when a schema object, such as a table or an index, is created.

Also not, if you specify, LOCAL, you cannot specify DEFAULT STORAGE, MINIMUM EXTENT or TEMPORARY.

Advantages of Locally Managed Tablespaces:

Eliminates the need for recursive SQL operations against the data dictionary (UET$ and FET$ tables)
Reduce contention on data dictionary tables (single ST enqueue)
Locally managed tablespaces eliminate the need to periodically coalesce free space (automatically tracks adjacent free space)
Changes to the extent bitmaps do not generate rollback information

Locally Managed SYSTEM Tablespace:

From Oracle9i release 9.2 one can change the SYSTEM tablespace to locally managed. Further, if you create a database with DBCA (Database Configuration Assistant), it will have a locally managed SYSTEM tablespace by default. The following restrictions apply:

No dictionary-managed tablespace in the database can be READ WRITE.
You cannot create new dictionary managed tablespaces
You cannot convert any dictionary managed tablespaces to local

Thus, it is best only to convert the SYSTEM tablespace to LMT after all other tablespaces are migrated to LMT.

Segment Space Management in LMT:

From Oracle 9i, one can not only have bitmap managed tablespaces, but also bitmap managed segments when setting Segment Space Management to AUTO for a tablespace. Look at this example:

SQL> CREATE TABLESPACE tblspc4 DATAFILE '/oradata/tblspc4_01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

Segment Space Management eliminates the need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects. The Automatic Segment Space Management feature improves the performance of concurrent DML operations significantly since different parts of the bitmap can be used simultaneously eliminating serialization for free space lookups against the FREELSITS. This is of particular importance when using RAC, or if "buffer busy waits" are deteted.

Convert between LMT and DMT:

The DBMS_SPACE_ADMIN package allows DBAs to quickly and easily convert between LMT and DMT mode. Look at these examples:

SQL> exec dbms_space_admin.Tablespace_Migrate_TO_Local('tblspc1');
PL/SQL procedure successfully completed.

SQL>exec dbms_space_admin.Tablespace_Migrate_FROM_Local('tblspc2');
PL/SQL procedure successfully completed.

Installing the April 2007 Critical Patch Update on Windows

2007-12-28T11:34:00.000+05:30

This article describes the procedure to install the April 2007 CPU patch on Oracle Database Release 10.1.0.5. The Patch Number is p5907304_10105_WINNT.zip

SYSTEM Overview

Platforms : Windows XP SP2
Database : 10.1.0.5.0

OPatch Utility Information

You must use the OPatch utility release 1.0.0.0.57 or later. You can download it from OracleMetaLink with patch 2617419.

Check Oracle Inventory Setup

D:\>set ORACLE_SID=TEST

D:\>set ORACLE_HOME=D:\oracle\product\10.1.0\db_1

D:\>cd D:\oracle\product\10.1.0\db_1\opatch

D:\oracle\product\10.1.0\Db_1\OPatch>opatch lsinventory

Oracle Interim Patch Installer version 1.0.0.0.53

Note: if Opatch utility version is <= 1.0.0.0.53 then you have to download OPATCH utility from the Metalink site.
[output not attached]

Oracle Home = D:\oracle\product\10.1.0\db_1
Location of Oracle Universal Installer components = D:\oracle\product\10.1.0\db_1\oui
Location of OraInstaller.jar = "D:\oracle\product\10.1.0\db_1\oui\jlib"
Oracle Universal Installer shared library = D:\oracle\product\10.1.0\db_1\oui\lib\win32\oraInstaller.dll
Location of Oracle Inventory Pointer = N/A
Location of Oracle Inventory = D:\oracle\product\10.1.0\db_1\inventory
Path to Java = "D:\oracle\product\10.1.0\db_1\jre\1.4.2\bin\java.exe"
Log file = D:\oracle\product\10.1.0\db_1/.patch_storage//*.log

Creating log file "D:\oracle\product\10.1.0\db_1\.patch_storage\LsInventory__12-13-2007_10-18-45.log"

Result:

There is no Interim Patch

OPatch succeeded.
OPatch returns with error code = 0

Preinstallation Instructions

1. Ensure that your Oracle Database installation is the same release for which you are applying this patch.

2. Back up the current database that is installed
-->ORACLE_HOME or ORA INVENTORY

3. Perform a clean shutdown of all Oracle services

D:\>net stop OracleServiceTEST
The following services are dependent on the OracleServiceTEST service.
Stopping the OracleServiceTEST service will also stop these services.

OracleDBConsoleTEST

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

The OracleServiceTEST service is stopping...........
The OracleServiceTEST service was stopped successfully.

D:\>net stop OracleServiceRCAT
The OracleServiceRCAT service is stopping......
The OracleServiceRCAT service was stopped successfully.

D:\>LSNRCTL stop

LSNRCTL for 32-bit Windows: Version 10.1.0.5.0 - Production on 13-DEC-2007 10:30:50

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
The command completed successfully

D:\>net stop OracleCSService

The OracleCSService service was stopped successfully.

D:\>net stop OracleOraDb10g_home1iSQL*Plus
..
The OracleOraDb10g_home1iSQL*Plus service was stopped successfully.

4. Set the PERL5LIB and PATH environment variables to point to Perl in Oracle Home

D:\>set PERL5LIB=D:\oracle\product\10.1.0\db_1\perl\5.6.1\lib;%PERL5LIB%

D:\>set PATH=D:\oracle\product\10.1.0\db_1\perl\5.6.1\bin\MSWin32-x86;%PATH%

5. set ORACLE_HOME variable

D:\>set ORACLE_HOME=D:\oracle\product\10.1.0\db_1

6. Check if the java and jar executables are present in your Oracle Home

-->JAVA = %ORACLE_HOME%\jre\\bin
-->JAR = %ORACLE_HOME%\jdk\bin

7. Go to directory where you downloaded the patch

D:\oracle\product\10.1.0\Db_1\OPatch>opatch apply D:\5907304

Post Installation Instructions

1. Start All Oracle Services.

D:\>net start OracleServiceTEST

D:\>net start OracleServiceRCAT

D:\>set ORACLE_SID=TEST

D:\>emctl start dbconsole

D:\>LSNRCTL start

D:\>net start OracleCSService

D:\>net start OracleOraDb10g_home1iSQL*Plus

2. For each database instance running out of the ORACLE_HOME being patched, connect to the database using SQL*Plus as SYSDBA and run catcpu.sql as follows:

D:\>rem go to CPU directory

D:\>CD D:\oracle\product\10.1.0\db_1\CPU\CPUApr2007

D:\oracle\product\10.1.0\Db_1\cpu\cpuapr2007>sqlplus /nolog

SQL*Plus: Release 10.1.0.5.0 - Production on Fri Dec 14 14:31:43 2007

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

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
TEST

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

SQL> STARTUP MIGRATE
ORACLE instance started.

Total System Global Area 1023410176 bytes
Fixed Size 782304 bytes
Variable Size 74190880 bytes
Database Buffers 947912704 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.

SQL> @CATCPU.SQL

[output not attached]

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> quit

If catcpu.sql reports any Invalid Objects, compile the invalid objects as follows

D:\>cd D:\oracle\product\10.1.0\db_1\rdbms\admin

D:\oracle\product\10.1.0\Db_1\RDBMS\ADMIN>sqlplus /nolog

SQL*Plus: Release 10.1.0.5.0 - Production on Fri Dec 14 14:55:21 2007

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

SQL> conn sys as sysdba
Enter password:
Connected to an idle instance.
SQL> STARTUP
ORACLE instance started.

Total System Global Area 1023410176 bytes
Fixed Size 782304 bytes
Variable Size 74190880 bytes
Database Buffers 947912704 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.

SQL> @utlprp.sql

SQL> select OBJECT_NAME from dba_objects where status = 'INVALID';

no rows selected

3. If any databases have been created without using DBCA (that is, using the command line), and you need to monitor such a database by using Oracle Enterprise Manager 10g, follow these steps:

$ sqlplus SYS / AS SYSDBA
SQL> ALTER USER DBSNMP ACCOUNT UNLOCK
SQL> PASSWORD DBSNMP
Changing password for DBSNMP
New password: new_password
Retype new password: new_password

Upgrade the Oracle database from 10.1.0.3.0 to 10.1.0.5.0

2007-12-28T11:12:00.000+05:30

This article describes the process of upgrading the Oracle Database Server 10g Release 10.1.0.3 to Oracle Database 10g Release 10.1.0.5 on Windows XP SP2.

1. Patch Set Overview

You can apply the Oracle Database 10g release 10.1.0.5 patch set to the following Oracle Database 10g release 1 (10.1.0.2, 10.1.0.3, or 10.1.0.4) installations.

2. Oracle Universal Installer Version Requirements

This patch set includes Oracle Universal Installer release 10.1.0.5, which is also installed when you install this patch set.

3. System Requirements

Hardware requirement : Intel Pentium
OS requirement : Windows XP Prof SP1 or later
Oracle DB : 10gr1(10.1.0.2) or later

4. Pre-installation Tasks

1. Identify the Oracle Database Installation

This is not a complete software distribution. You must install it in an existing Oracle Database 10g Oracle home.

2. Download and extract the p4505133_10105_WINNT.zip patch set.

3. Shut Down Oracle Databases

D:\>sqlplus sys@TEST/pwd as sysdba
SQL>shutdown immediate;

D:\>sqlplus sys@RCAT/pwd as sysdba
SQL>shutdown immediate;

4. Stop All Services

D:\>net stop OracleOraDb10g_home1iSQL*Plus
..
The OracleOraDb10g_home1iSQL*Plus service was stopped successfully.

D:\>net stop OracleServiceTEST
The following services are dependent on the OracleServiceTEST service.
Stopping the OracleServiceTEST service will also stop these services.

OracleDBConsoleTEST

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

The OracleServiceTEST service is stopping.
The OracleServiceTEST service was stopped successfully.

D:\>net stop OracleServiceRCAT
The OracleServiceRCAT service is stopping.......
The OracleServiceRCAT service was stopped successfully.

5. Stop the listener:

D:\>LSNRCTL stop

LSNRCTL for 32-bit Windows: Version 10.1.0.3.0 - Production on 28-DEC-2007 11:05:13

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
The command completed successfully

D:\>net stop OracleCSService

The OracleCSService service was stopped successfully.

6. Back up the SYSTEM.

Installation Tasks

1. Run SETUP.EXE from where you downloaded/extracted your patch.

In the Name field in the Destination section, select the 10.1.0.x Oracle home that you want to update from the drop down list, then click Next.

On the Summary screen, click Install.

Upgrading Release 10.1 to Oracle Database 10g Release 1 (10.1.0.5)

1.Check SYSTEM TBS should have 10mb Free Space.
2.SHARED_POOL_SIZE or JAVA_POOL_SIZE at least 150 MB.

SQL> alter tablespace SYSTEM
2 add datafile 'D:\oracle\product\10.1.0\oradata\TEST\system02.dbf' size 20m;

Tablespace altered.

SQL> alter system set SHARED_POOL_SIZE = 150M SCOPE=spfile;

System altered.

SQL> alter system set JAVA_POOL_SIZE = 150M SCOPE=spfile;

System altered.

SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP UPGRADE
ORACLE instance started.

Total System Global Area 1023410176 bytes
Fixed Size 782304 bytes
Variable Size 74190880 bytes
Database Buffers 947912704 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.

SQL> SPOOL patch.log

SQL> @D:\oracle\product\10.1.0\db_1\rdbms\admin\catpatch.sql
DOC>######################################################################
DOC>######################################################################
DOC> The following PL/SQL block will cause an ORA-20000 error and
DOC> terminate the current SQLPLUS session if the user is not SYS.
DOC> Disconnect and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#

PL/SQL procedure successfully completed.
....
[output not attached]

SQL> SPOOL OFF

Restart the database

SQL>SHUTDOWN
SQL>STARTUP

Run the utlrp.sql script to recompile all invalid PL/SQL packages

SQL> @D:\oracle\product\10.1.0\db_1\rdbms\admin\utlrp.sql

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Prod
PL/SQL Release 10.1.0.5.0 - Production
CORE 10.1.0.5.0 Production
TNS for 32-bit Windows: Version 10.1.0.5.0 - Production
NLSRTL Version 10.1.0.5.0 - Production

Known Issues

select dbms_java.full_ncomp_enabled from dual
*
ERROR at line 1:
ORA-29558: JAccelerator (NCOMP) not installed. Refer to Install Guide for
instructions.
ORA-06512: at "SYS.DBMS_JAVA", line 236

IGNORE
---> If you do not use Java in the database, then ignore this error message.

Don't Ignore
--->If you use Java in the database, then install the NCOMP libraries by selecting the Oracle Database 10g Products installation type from the Oracle Database Companion CD.

If RMAN CATALOG is also configure then upgrade RMAN CATALOG

D:\>rman CATALOG=rman/cat@RCAT

Recovery Manager: Release 10.1.0.5.0 - Production

Copyright (c) 1995, 2004, Oracle. All rights reserved.

connected to recovery catalog database

RMAN> UPGRADE CATALOG;

recovery catalog owner is RMAN
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> UPGRADE CATALOG;

recovery catalog upgraded to version 10.01.00
DBMS_RCVMAN package upgraded to version 10.01.00
DBMS_RCVCAT package upgraded to version 10.01.00

Finding your OS and Database 32 bits or 64 bits

2007-12-28T10:53:00.000+05:30

Want to know whether your Database / Operating System is running a 32-bit or 64-bit software?

DBAs often need to know if they should install Oracle 32-bit or 64-bit software on a given server. If the operating system support 64-bit applications, the 64-bit Oracle distribution can be loaded. Otherwise, the 32-bit distribution must be installed. The following Unix commands will tell you whether your OS is running 32-bits or 64-bits:

Solaris - isainfo -v (this command doesn't exist on Solaris 2.6 because it is only 32-bits)

HP-UX - getconf KERNEL_BITS

AIX - bootinfo -K

LINUX - uname -a

If your Linux OS is 32 bit the output will be like below:

Linux oraclesrvr 2.6.18-8.el5PAE #1 SMP Tue Jun 5 23:39:57 EDT 2007 i686 i686 i386 GNU/Linux

If your Linux OS is 64 bit the output will be like below:

Linux aptserver 2.6.18-8.el5xen #1 SMP Tue Jun 5 23:53:34 EDT 2007 x86_64 x86_64 x86_64 GNU/Linux

If you need to know Oracle 32-bit or 64-bit software is currently installed on a system, connect using a command line utility like sqlplus and look at the banner. If you are running 64-bit software, it will be mentioned in the banner. If nothing is listed, you are running on a 32-bit base.

If your Oracle Software is 64 bit the output will be like below:
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bi
PL/SQL Release 10.1.0.3.0 - Production
CORE 10.1.0.3.0 Production
TNS for Linux: Version 10.1.0.3.0 - Production
NLSRTL Version 10.1.0.3.0 - Production

If your Oracle Software is 32 bit the output will be like below:
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Prod
PL/SQL Release 10.1.0.3.0 - Production
CORE 10.1.0.3.0 Production
TNS for Linux: Version 10.1.0.3.0 - Production
NLSRTL Version 10.1.0.3.0 - Production

Relocating Database Files

2007-12-27T14:08:00.000+05:30

The CRD&T files are called as Database Files:

1. Control files
2. Redo log files
3. Datafiles & Tempfiles

At some moments we need to move or relocate the Database files from original(old) location to new location.

STEPS FOR RELOCATING THESE DATABASE FILES:

I.How to move DATAFILES from old to new location(not SYSTEM,UNDO & TEMP tablespaces'):

Step 1: Take tablespace offline
SQL> alter tablespace tbsname OFFLINE;

Step 2:Use OS level command to MOVE datafiles from old to new location.
$ mv 'old location' to 'new location'

Step 3: Rename datafiles in database.
SQL> alter database rename file 'old location' to 'new location';

Step 4: Take tablespace ONLINE
SQL> alter tablespace tbsname ONLINE;

II.For SYSTEM or UNDO tablespace:

Step 1: Shutdown database
SQL> shutdown immediate;

Step 2: Move datafiles from old to new location using OS command.
$ mv 'old location' 'new location'

Step 3: Startup database in mount stage.
SQL> startup mount;

Step 4: Rename the datafile
SQL> alter database rename file 'old location' to 'new location';

Step 5: Open the database for normal use
SQL> alter database open;

III.How to move control file from old to new location:

Step 1: Shutdown the Database
SQL> shutdown immediate;

Step 2: Move controlfile from old to new location
$ mv old location new location

Step 3: Edit CONTROL_FILES parameter in pfile and change location from old to new
CONTROL_FILES='new location'

Step 4: recreate SPFILE from PFILE
SQL> create spfile from pfile;

Step 5: startup database
SQL> startup;

IV.How to move REDO LOG MEMBER from old to new location

Step 1: shutdown database
SQL> shutdown immediate;

Step 2: Move redo log member from old to new location
$ mv oldlocation newlocation

Step 3: startup database with mount stage
SQL> startup mount;

Step 4: rename redo log member in database
SQL> alter database rename file 'old location' to 'new location';

Step 5: open database for normal use
SQL> alter database open;

V. We cannot relocate the tempfile, if we need to relocate it(tempfile) then the best option is to recreate the temp tablespace in the new location.

Simple FTP setup in Linux

2007-12-12T15:05:00.000+05:30

By default FTP comes disabled on stock RedHat/Oracle Enterprise/Centos server installs. In some occasions I find the need to enable FTP.

Below I will show you how to do it:
Become the root user on your server.
Change to the /etc/xinetd.d directory

cd /etc/xinetd.d/

Edit the gssftp file

vi gssftp

Change the line that says disable = yes to disable = no. Also remove the –a option in the server_args line.

Your config file should now look like the one below. Save the file and exit vi.

service ftp
{
flags = REUSE
socket_type = stream
wait = no
user = root
server = /usr/kerberos/sbin/ftpd
server_args = -l
log_on_failure += USERID
disable = no
}

The -a option removes the following restriction, as described in the man ftpd page.
-a
Connections are only allowed for users who can authenticate (via the ftp AUTH mechanism) and who are authorized to connect to the named account without a password. (Anonymous ftp may also be allowed if it is configured.)

Restart the xinetd daemon.
/etc/init.d/xinetd restart

You should now be able to FTP to your server.

That’s it, you’re FTP service is now enabled. Now let me explain when I actually use
FTP. Sometimes I will transfer very large files or amounts of data between my servers (many gigabytes worth). I will enable FTP temporarily to do this since FTP can do these transfers a little more quickly than SFTP because it doesn’t have as
much overhead. After I’m done with the transfer, I then disable FTP.

EXAMPLES

ftp rmsi.oracle.com
This command will attempt to connect to the ftp server at rmsi.oracle.com. If it succeeds, it will ask you to log in using a username and password. Public ftp servers often allow you to log in using the username "anonymous" and your email address as password. Once you are logged in you can get a list of the available ftp commands using the help function:

ftp> help
This lists the commands that you can use to show the directory contents, transfer files, and delete files.

ftp> ls
This command prints the names of the files and subdirectories in the current directory on the remote computer.

ftp> cd customers
This command changes the current directory to the subdirecotry "customers", if it exists.

ftp> cd ..
Changes the current directory to the parent direcotry.

ftp> lcd images
Changes the current directory on the local computer to "images", if it exists.

ftp> ascii
Changes to "ascii" mode for transferring text files.

ftp> binary
Changes to "binary" mode for transferring all files that are not text files.

ftp> get image1.jpg
Downloads the file image1.jpg from the remote computer to the local computer. Warning: If there already is file with the same name it will be overwritten.

ftp> put image2.jpg
Uploads the file image2.jpg from the local computer to the remote computer. Warning: If there already is file with the same name it will be overwritten.

ftp> !ls
A '!' in front will execute the specified command on the local computer. So '!ls' lists the file names and directory names of the current directory on the local computer.

ftp> mget *.jpg
With mget you can download multiple images. This command downloads all files that end with ".jgp".

ftp> mput *.jpg
Uploads all files that end with ".jgp".

ftp> mdelete *.jpg
Deletes all files that end with ".jgp".

ftp> prompt
Turns iteractive mode on or off so that commands on multiple files are executed without user confirmation.

ftp> quit
Exits the ftp program.

Changing the current Database Name and DB ID

2007-11-06T12:28:00.000+05:30

CHANGING DBNAME & DBID
-----------------------

SQL>SHUTDOWN IMMEDIATE
SQL>STARTUP MOUNT

$nid TARGET=sys/sys@swdm DBNAME=swm

Change database ID and database name SWDM to SWM? (Y/[N]) => Y

SQL>SHUTDOWN IMMEDIATE

Modify the DB_NAME parameter in the initialization parameter file. The startup will result in an error but proceed anyway.

SQL>STARTUP MOUNT
SQL>ALTER SYSTEM SET DB_NAME=SWM SCOPE=SPFILE;
SQL>SHUTDOWN IMMEDIATE

Create a new password file:

$cd /u01/app/oracle/product/10.1.0/db_1/dbs/

$orapwd file=/u01/app/oracle/product/10.1.0/db_1/dbs/orapw password=sys entries=10

If you are using UNIX/Linux simply reset the ORACLE_SID environment variable:

$ORACLE_SID=SWM; export ORACLE_SID

Rename the SPFILE to match the new DBNAME.

If you are using Windows you must recreate the service so the correct name and parameter file are used:

oradim -delete -sid SWDM
oradim -new -sid SWM -intpwd password -startmode a -pfile c:\oracle\920\database\spfileSWM.ora

Alter the listener.ora and tnsnames.ora setting to match the new database name and restart the listener:

$lsnrctl reload

Open the database with RESETLOGS:

SQL>STARTUP MOUNT
SQL>ALTER DATABASE OPEN RESETLOGS;

Backup the database.

==========================================================================
CHANGING DBNAME Only
---------------------

Repeat the process as before except use the following command to start the DBNEWID utility:

$nid TARGET=sys/sys@SWDM DBNAME=swm SETNAME=YES

SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP MOUNT;
SQL>ALTER SYSTEM SET DB_NAME=SWM SCOPE=spfile;
SQL>SHUDOWN IMMEDIATE;

On Windows:
C:\> orapwd file= C:\oracle\product\10.1.0\db_1\database\pwdSWM.ora password=xxxxxxx entries=10

On Unix:
$ orapwd file=/u01/app/oracle/product/10.1.0/db_1/dbs/orapw password=sys entries=10

SQL>STARTUP;
SQL>SHOW PARAMETER DB_NAME

We have changed only the database name, The SETNAME parameter tells the DBNEWID utility to only alter the database name. and

not the database ID, it is not necessary to use the RESETLOGS option when you open the database

Backup the database.
===========================================================================

CHANGING DBID Only
-------------------
Backup the database.
Mount the database after a clean shutdown:

SQL>SHUTDOWN IMMEDIATE
SQL>STARTUP MOUNT

Invoke the DBNEWID utility (nid) from the command line using a user with SYSDBA privilege. Do not specify a new DBNAME:

$nid TARGET=sys/password@SWDM

Shutdown and open the database with RESETLOGS:

SQL>SHUTDOWN IMMEDIATE
SQL>STARTUP MOUNT
SQL>ALTER DATABASE OPEN RESETLOGS;

Backup the database.

Resize undo tablespace

2007-11-05T11:07:00.000+05:30

You created undo tablespace using AUTOEXTEND ON MAXSIZE UNLIMITED to avoid error.

Your have just done purging (deleting millions of rows) on your production database and you noticed that your undo tablespace datafile is huge in size.

You tried to use “ALTER DATABASE DATAFILE .. RESIZE”, and this command failed with ORA-3297 error. , i.e.: “file contains ~~ blocks of data beyond requested RESIZE value”.

So you should drop and recreate undo tablespace using the following commands:

1, Connect to Oracle

sqlplus /nolog
connect / as sysdba

2, Find out which undo tablespace is being used:

SQL>select name,value from v$parameter where name in ('undo_management','undo_tablespace');

NAME VALUE
——————- ——————-
undo_management AUTO
undo_tablespace UNDOTBS1

3, Create new undo tablespace UNDOTBS2 with smaller size.

SQL> create undo tablespace UNDOTBS2 datafile '/u01/app/oracle/oradata/edr/undotbs02.dbf' size 500m reuse;

4, Tell Oracle to use new undo tablespace.

SQL> alter system set undo_tablespace=UNDOTBS2;

5, Now you can safely drop and recreate tablespace UNDOTBS1.

SQL> drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.

6, SQL> CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE '/u01/app/oracle/oradata/edr/undotbs01.dbf' SIZE 250m reuse AUTOEXTEND ON ;

Tablespace created.

7, SQL> alter system set undo_tablespace=UNDOTBS1;

8, Now you can safely drop tablespace UNDOTBS2.

SQL> drop tablespace UNDOTBS2 including contents and datafiles;

Tablespace dropped.

Recreate Temp tablespace

2007-11-05T11:05:00.000+05:30

You created temp tablespace using AUTOEXTEND ON MAXSIZE UNLIMITED to avoid error.
On your production database you noticed that your temp tablespace datafile is huge in size. So you should drop and recreate temp tablespace using the following commands,

CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/u01/app/oracle/oradata/edr/temp02.dbf' SIZE 5M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
------------------------------------------------------------------------------------------
CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/u01/app/oracle/oradata/edr/temp01.dbf' SIZE 500M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
------------------------------------------------------------------------------------------
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Installation of Oracle 10G on Solaris 10 -- Short

2007-10-04T17:46:00.000+05:30

As the root user,
#groupadd oinstall
#groupadd dba
#useradd -d /oracle -g oinstall -G dba -m -s /bin/ksh oracle
#passwd -r files oracle
--------------------------------------------------------------------------
Edit the /oracle/.profile file and add lines

umask 022
TMP=/tmp
TMPDIR=$TMP
DISPLAY=localhost:0.0
export TMP TMPDIR DISPLAY
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/10.1.0/db_1
ORACLE_SID=sol10
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH
--------------------------------------------------------------------------
save and check
# su - oracle
Sun Microsystems Inc. SunOS 5.10 Generic January 2005
$ env
--------------------------------------------------------------------------
As the root user,
#mkdir /u01/app/oracle
#mkdir /u01/app/oracle/product/10.1.0/db_1
#chown -R oracle:oinstall /oracle
#chmod -R 775 /oracle
--------------------------------------------------------------------------
$gunzip solarisx86_DB_10_1_0_3_Disk1.cpio.gz
$cpio -idm < solarisx86_DB_10_1_0_3_Disk1.cpio
--------------------------------------------------------------------------
edit the /etc/user_attr file and add
oracle::::project=oracle
--------------------------------------------------------------------------
save and check
# su - oracle
Sun Microsystems Inc. SunOS 5.10 Generic January 2005
$ id -p
$ prctl -n project.max-shm-memory -i project oracle
--------------------------------------------------------------------------
new terminal session as root
#prctl -n project.max-shm-memory -v 4gb -r -i project oracle
oracle user's session
$ prctl -n project.max-shm-memory -i project oracle
root user's session
#projmod -s -K "project.max-shm-memory=(priv,4gb,deny)" oracle
---------------------------------------------------------------------------
#xhost +
#su - oracle
$/Disk1/runInstaller -ignoreSysPrereqs
......
......

Oracle Installation(9.2.0.1.0) on Red Hat Enterprise Linux 3 and upgrading to 9.2.0.4

2007-09-13T17:02:00.000+05:30

In order to install an Oracle9iR2 database on RHEL 3, the "Oracle9iR2 Patch Set 3 9.2.0.4.0" patchset and some other patches must be applied. Some errors can only be fixed by applying the 9.2.0.4 patchset.
Patches required:
p3006854_9204_LINUX.zip
p3095277_9204_LINUX.zip
p3119415_9204_LINUX.zip

1. Install/Check the following RPMs (see Oracle Note:252217.1 for more information):
su - root
rpm -q patch name-->To check

rpm -ivh \
compat-db-4.0.14-5.i386.rpm \
compat-gcc-7.3-2.96.122.i386.rpm \
compat-gcc-c++-7.3-2.96.122.i386.rpm \
compat-libstdc++-7.3-2.96.122.i386.rpm \
compat-libstdc++-devel-7.3-2.96.122.i386.rpm \
openmotif21-2.1.30-8.i386.rpm \
setarch-1.3-1.i386.rpm \
tcl-8.3.5-92.i386.rpm

2. Open a terminal window and log in as the root user.

3. The following local UNIX groups and user must exist on the system:

Groups: oinstall and dba
User: oracle

Now create the two groups oinstall, dba

#groupadd oinstall
#groupadd dba

Create the operating system user oracle:

#useradd -g oinstall -G dba oracle

Enter the following command to set the password of the oracle user:

#passwd oracle

4. Create the directory for the software installation and assign ownership to oracle:oinstall. In the example, you use /u01/app/oracle.

mkdir -p /u01/app/oracle
chown -R oracle:oinstall /u01/app
chmod -R 775 /u01/app

5. With an editor of your choosing, open /home/oracle/.bash_profile and add the entries to the appropriate file.

umask 022
PATH=/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin
LD_LIBRARY_PATH=/usr/lib:/usr/X11R6/lib
ORACLE_BASE=/u01/app/oracle/
ORACLE_HOME=/u01/app/oracle/product/9.2.0.1.0

#PATH=/u01/app/oracle/product/9.2.0.1.0/bin:/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/X11R6/bin:/root/bin
export PATH
ORACLE_OWNER=oracle

ORACLE_SID=sid
ORACLE_SID=orcl
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_BASE ORACLE_HOME ORACLE_OWNER ORACLE_SID
export PATH LD_LIBRARY_PATH
CLASSPATH=:/u01/app/oracle/product/9.2.0.1.0/JRE:/u01/app/oracle/product/9.2.0.1.0/jlib:/u01/app/oracle/product/9.2.0.1.0/rdbms/jlib:/u01/app/oracle/product/9.2.0.1.0/network/jlib
export CLASSPATH
LD_ASSUME_KERNEL=2.4.1
export LD_ASSUME_KERNEL

if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi

6. Configuring Kernel Parameters

See my previous post for the kernel configuration settings.
Kernel Settings

---------

7. If now you execute runInstaller from the Oracle 9iR2 (9.2.0) CD, you will get the following error message:

Error occurred during initialization of VM
Unable to load native library: /tmp/OraInstall2007-8-25_03-14-57PM/jre/lib/i386/libjava.so:
symbol __libc_wait, version GLIBC_2.0 not defined in file libc.so.6 with link time reference

To resolve the __libc_wait symbol issue, download the p3006854_9204 patch p3006854_9204_LINUX.zip from http://metalink.oracle.com.

To apply the patch, run
su - root
# unzip p3006854_9204_LINUX.zip
Archive: p3006854_9204_LINUX.zip
creating: 3006854/
inflating: 3006854/rhel3_pre_install.sh
inflating: 3006854/README.txt

# cd 3006854
# sh rhel3_pre_install.sh
Applying patch...
Patch successfully applied
#

Now runInstaller can be started from the CD:
su - oracle
$ echo $LD_ASSUME_KERNEL # it is important that this variable is set!
2.4.1
$ /mnt/cdrom/runInstaller

You may get the following errors:

Error in invoking target install of makefile /u01/app/oracle/product/9.2.0/network/lib/ins_oemagent.mk.

Click ignore.
This will be fixed by applying the patch 3119415 after the 9.2.0.4 patchset has been applied.
You won't be able to apply the patch 3119415 at this time since the file /u01/app/oracle/oraInventory/ContentsXML/comps.xml doesn't exist yet.

Error in invoking target install of makefile /u01/app/oracle/product/9.2.0/ctx/lib/ins_ctx.mk.

Click ignore.
This is fixed by applying the 9.2.0.4 patchset.

Patching Oracle9iR2 on RH AS 3

To patch Oracle9iR2, download the Oracle 9i Release 2 Patch Set 3 Version 9.2.0.4.0 for Linux x86 from http://metalink.oracle.com.

Copy the downloaded "p3095277_9204_LINUX.zip" file to e.g. /tmp and run the following command:
su - oracle
$ cp p3095277_9204_LINUX.zip /tmp
$ cd /tmp
$ unzip p3095277_9204_LINUX.zip
Archive: p3095277_9204_LINUX.zip
inflating: 9204_lnx32_release.cpio
inflating: README.html
inflating: patchnote.css
$
$ cpio -idmv < 9204_lnx32_release.cpio
Disk1/stage/locks
Disk1/stage/Patches/oracle.apache.isqlplus/9.2.0.4.0/1/DataFiles/bin.1.1.jar
Disk1/stage/Patches/oracle.apache.isqlplus/9.2.0.4.0/1/DataFiles/lib.1.1.jar
...

To patch the runInstaller, run:
su - oracle
$ echo $LD_ASSUME_KERNEL # it is important that this variable is set!
2.4.1
$ cd /tmp/Disk1/
$ ./runInstaller

- Welcome Screen: Click Next
- File Locations: Use default values
- Available Products: Select "Oracle Universial Installer 2.2.0.18.0 !"
- Components Locations: Accept default values and click Next
- Summary: Start the Install
- At the end of the installation, you must exit runInstaller!

To patch Oracle9iR2, run:
su - oracle
$ echo $LD_ASSUME_KERNEL # it is important that this variable is set!
2.4.1
$ cd $ORACLE_HOME/bin
$ ./runInstaller

- Welcome Screen: Click Next
- File Locations: Use default values
- Available Products: Select "Oracle9iR2 Patch Set 3 9.2.0.4.0 !"
- Summary: Start the Install
- At the end of the installation, exit runInstaller

You may get the following error:

Error in invoking target install of makefile /u01/app/oracle/product/9.2.0/network/lib/ins_oemagent.mk.

Click ignore.
This will be fixed by applying the patch 3119415 after the 9.2.0.4 patchset has been applied.
The patch 3119415 cannot be applied while the patch process for the 9.2.0.4 patchset is running.

After the 9.2.0.4 patchset has been applied, download the patch p3119415_9204_LINUX.zip. Also, download the opatch Release 2.2.0 utility from http://metalink.oracle.com.

To install opatch, run:
su - oracle
$ cp p2617419_210_GENERIC.zip /tmp
$ cd /tmp
$ unzip p2617419_210_GENERIC.zip
Before you apply the 3119415 patch, you need to make sure the fuser binary can be found by the oracle user, see the PATH environment variable below. Otherwise the patch can't be applied because the fuser binary is used by opatch.

To apply the 3119415 patch, run
su - oracle
$ unzip p3119415_9204_LINUX.zip
$ cd 3119415
$ export PATH=$PATH:/tmp/OPatch
$ export PATH=$PATH:/sbin # the patch needs "fuser" which is located in /sbin
$ which opatch
/tmp/OPatch/opatch
$ opatch apply

Installation of 9iR2 with all required patches is completed succesfully.

Upgrading 9iR2 to 10gR2 using hotbackup

2007-08-28T10:28:00.000+05:30

Upgrading 9iR2 to 10gR2 using hotbackup

Abstract

The document is aimed at describing the step by step procedure that was followed to upgrade a database from Oracle 9iR2 to 10gR2 using hot backup of 9iR2 database. The basis of this upgrade is a hot backup clone.

Case History

The database to be upgraded is about 185GB in size, this activity was tested on a development system to validate the success of this exercise before proceeding with the production environment.

Analysis

Summary

A hot backup was used to create a database on 10gR2 by recreating the controlfile.

Detailed Analysis

1. A password file was initially created in $ORACLE_HOME/dbs of 10gR2 for this particular instance using the orapwd utility. The pfile from the 9i database was used to start the instance by just changing the compatible parameter to 10.2.0 and no other changes were made to the init.ora file since all the deprecated and obsolete parameters will be listed in the alert log once the database is brought up which can be removed later. The instance was now started.

SQL> startup nomount

This step can also be executed as shown below, which is an observation from the alert log when the database failed to open with an error of “ORA-39700: database must be opened with UPGRADE option" preceded with an error of "ORA-00704: bootstrap process failure"

SQL> startup upgrade

2. The next step is to execute the control file recreation script in order to create the controlfile. This is a text controlfile script which was dumped to a trace file from the production environment and updated to reflect the locations of files in the development server.

SQL> !cat control_9_to_10.sql

CREATE CONTROLFILE SET DATABASE "TEN_DB" RESETLOGS
NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 '/home/oracle/dev/log1a.rdo' SIZE 200M,
GROUP 2 '/home/oracle/dev/log2a.rdo' SIZE 200M,
GROUP 3 '/home/oracle/dev/log3a.rdo' SIZE 200M,
GROUP 4 '/home/oracle/dev/log4a.rdo' SIZE 200M
DATAFILE
'/home/oracle/dev/data01.dbf',
'/home/oracle/dev/data02.dbf',
'/home/oracle/dev/data03.dbf',
'/home/oracle/dev/users.dbf',
'/home/oracle/dev/tools.dbf',
'/home/oracle/dev/system01.dbf',
'/home/oracle/dev/undotbs01.dbf',
'/home/oracle/dev/undotbs02.dbf',
'/home/oracle/dev/undotbs03.dbf',
'/home/oracle/dev/index01.dbf',
'/home/oracle/dev/index02.dbf',
'/home/oracle/dev/index04.dbf',
'/home/oracle/dev/index05.dbf',
'/home/oracle/dev/index06.dbf',
'/home/oracle/dev/index03.dbf',
'/home/oracle/dev/index07.dbf',
'/home/oracle/dev/data04.dbf',
'/home/oracle/dev/data05.dbf',
'/home/oracle/dev/data06.dbf',
'/home/oracle/dev/data07.dbf',
'/home/oracle/dev/data08.dbf',
'/home/oracle/dev/data09.dbf',
'/home/oracle/dev/data10.dbf',
'/home/oracle/dev/data11.dbf',
'/home/oracle/dev/data12.dbf',
'/home/oracle/dev/data13.dbf',
'/home/oracle/dev/data14.dbf',
'/home/oracle/dev/data15.dbf',
'/home/oracle/dev/data16.dbf',
'/home/oracle/dev/data17.dbf'
CHARACTER SET US7ASCII;

SQL> @control_9_to_10.sql

Control file created.

3. The database was then recovered to the most recent point of time (until the last available archive file). Since the controlfile was recreated so it was very much necessary to use the keywords “using backup controlfile” during recovery. The files marked in bold are the archives which were applied for recovery.

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 79118 generated at 08/06/2007 15:43:45 needed for thread 1
ORA-00289: suggestion :
/home/oracle/dev/arch/1_43_629467995.dbf
ORA-00280: change 79118 for thread 1 is in sequence #43

Specify log: {=suggested | filename | AUTO | CANCEL}
/home/oracle/dev/arch/1_43_629467995.dbf

ORA-00279: change 79120 generated at 08/06/2007 15:43:51 needed for thread 1
ORA-00289: suggestion :
/home/oracle/dev/arch/1_44_629467995.dbf
ORA-00280: change 79120 for thread 1 is in sequence #44
ORA-00278: log file '/home/oracle/dev/arch/1_43.dbf' no
longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}
/home/oracle/dev/arch/1_44_629467995.dbf

ORA-00279: change 79122 generated at 08/06/2007 15:43:51 needed for thread 1
ORA-00289: suggestion :
/home/oracle/dev/arch/1_45_629467995.dbf
ORA-00280: change 79122 for thread 1 is in sequence #45
ORA-00278: log file '/home/oracle/dev/arch/1_44.dbf' no
longer needed for this recovery

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

4. This is the most daunting step one would go through, because a normal “alter database open resetlogs” reported an error of "ORA-39700: database must be opened with UPGRADE option" in the alert log and an error of "ORA-01092: ORACLE instance terminated. Disconnection forced" at the SQL prompt. It was improvised from this point that a "startup upgrade" can also be used to start the instance. As for the “ORA-39700” error in alert log, the statement shown below was executed and it surprisingly worked.

SQL> alter database open resetlogs upgrade;

Database altered.

5. Since there is no concept of SYSAUX tablespace in 9i and which is one of the essential tablespaces in 10g, a SYSAUX tablespace was then created manually as shown below.

SQL> create tablespace SYSAUX datafile '/home/oracle/dev/sysaux.dbf' size 500M online permanent extent management local segment space management auto;

Tablespace created.

6. A temporary tablespace was created since it was not included in the controlfile recreation script, and made the default temporary tablespace.

SQL> create temporary tablespace temp tempfile
'/home/oracle/dev/temp01.dbf' size 8000M;

Tablespace created.

SQL> alter database default temporary tablespace temp;

Database altered.

7. Now, When trying to query the dba or v$ views, the database would not respond. When trying to interrupt using the traditional interrupt keys on UNIX "Ctrl+c" an error of ORA-600 was reported at the SQL prompt as shown.

SQL> select file_name from dba_data_files;

ORA-00600: internal error code, arguments: [17069], 0xC00000002B8EDDA0],[], [], [], [], [], []

To start it all over again the database was bounced but it would not come up with a normal "startup" and only accepts "startup upgrade" to come up. This is where it was located from the discussion threads of Metalink that in doing an upgrade from 9i to 10g, the data dictionary needs to be synchronized with the version of the database. The Oracle sever makes a match between the database version and data dictionary every time it opens the database thereby only allowing a “startup upgrade" rather than a normal “startup” to bring the database up.

It was suggested from another discussion thread of Metalink that catupgrd.sql script needed to be executed from 10gR2 $ORACLE_HOME/rdbms/admin to achieve the synchronization. The script ran for a while and completed successfully.

SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql

The following script was executed for the utility upgrade status check which was successful as well.

SQL> @$ORACLE_HOME/rdbms/admin/utlu102s.sql

The database was bounced yet again and this time it did come up with a normal "startup". The script utlrp.sql was executed a couple of times to recompile all the invalid objects. The global name of the database was then changed to reflect the new name because creating a database with a control file recreation keeps the global_name of the old database unchanged as shown below.

SQL> select * from global_name;

GLOBAL_NAME
-----------------------------------------------------------
NINE_DB.TEST.COM

The global_name was changed by running the following statement which marked the successful completion of upgrade from Oracle 9iR2 to 10gR2.

SQL> alter database rename global_name to TEN_DB.TEST.COM;

Note: If the upgrade is from Oracle 9iR2 to 10gR1 then the synchronization is done by executing u0902000.sql and the utility upgrade status is checked by executing utlu101s.sql from 10gR1 $ORACLE_HOME/rdbms/admin.(Metalink Note: 263809.1)

10gR1 $ORACLE_HOME/rdbms/admin/u0902000.sql --- to upgrade to 10.1.0
which is renamed as catupgrd.sql in 10gR2

10gR1 $ORACLE_HOME/rdbms/admin/utlu101s.sql --- to upgrade to 10.1.0
which is also renamed as utlu102s.sql in 10gR2

Conclusion

A simple concept of cloning a database has been a vital step for the upgrade of an Oracle server version, which not only reduces the downtime for the upgrade but also is achievable with least overhead.

Author: Nawaz Ahmed M.A.S., System Engineer, GAVS Information Services, India. (Metalink Note id: 455990.1)

Configuration of kernel parameters

2007-08-27T14:02:00.000+05:30

Configuring Kernel Parameters

If the value of any kernel parameter is different from the recommended value, then complete the following procedure:

Using any text editor, create or edit the /etc/sysctl.conf file, and add or edit lines similar to the following:

Note:

Include lines only for the kernel parameter values that you want to change. For the semaphore parameters (kernel.sem), you must specify all four values. However, if any of the current values are larger than the recommended value, then specify the larger value.

fs.file-max = 512 * PROCESSES
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 262144

Note: If the current value for any parameter is higher than the value listed in this table, then do not change the value of that parameter.

By specifying the values in the /etc/sysctl.conf file, they persist when you restart the system.

Enter the following command to change the current values of the kernel parameters:

# /sbin/sysctl -p

Setting Shell Limits for the oracle User

To improve the performance of the software on Linux systems, you must increase the following shell limits for the oracle user:

Shell Limit Item in limits.conf Hard Limit

Maximum number of open file descriptors nofile 65536
Maximum number of processes available to a single user nproc 16384

To increase the shell limits:

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 or edit the following line in the /etc/pam.d/login file, if it does not already exist:

session required /lib/security/pam_limits.so
session required pam_limits.so

Note:

For 64-bit platforms, the following entry is not required in the /etc/pam.d/login file:

session required /lib/security/pam_limits.so

Depending on the oracle user's default shell, make the following changes to the default shell start-up file:

For the Bourne, Bash, or Korn shell, add the following lines to the /etc/profile file (or the file on SUSE systems)/etc/profile.local:

if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi

For the C shell (csh or tcsh), add the following lines to the /etc/csh.login file (or the file on SUSE systems)/etc/csh.login.local:

if ( $USER == "oracle" ) then
limit maxproc 16384
limit descriptors 65536
endif

Scheduling backup using Crontab in Linux

2007-08-13T14:37:00.000+05:30

Edit the crontab using following command and add the line as below:

$crontab -e

0 06,18 * * * sh /home/oracle/Desktop/bkup.sh

Scheduling backup twice a day at 6 in the morning and 6 in the evening.

Contents of bkup.sh
-------------------
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.1.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=edr
mv /home/oracle/edrbkup/old_edr.dmp /home/oracle/edrbkup/old_bkup/
mv /home/oracle/edrbkup/old_edr.log /home/oracle/edrbkup/old_bkup/
mv /home/oracle/edrbkup/edr.dmp /home/oracle/edrbkup/old_edr.dmp
mv /home/oracle/edrbkup/edr.log /home/oracle/edrbkup/old_edr.log
exp edr/regp file=/home/oracle/edrbkup/edr.dmp log=/home/oracle/edrbkup/edr.log owner=edr buffer=200000

This script will automatically clean the old backup and maintains three backups' at any given point of time.

cron commands:

$crontab -l To list the cron jobs scheduled
$crontab -r To remove the cron job
$crontab -e To edit/schedule cron jobs

General Overview:

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.

* * * * * 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)

Hope this may help you.

Creating a Samba Share on a Linux box for a Windows box in network

2007-08-09T10:05:00.000+05:30

[root@oraclesrvr ~]# mkdir /regp
[root@oraclesrvr ~]# useradd regpadmin
[root@oraclesrvr ~]# useradd regpuser
[root@oraclesrvr ~]# passwd regpadmin
[root@oraclesrvr ~]# passwd regpuser
[root@oraclesrvr ~]# chown -R regpadmin:regpadmin /regp
[root@oraclesrvr ~]# chmod -R 777 /regp

[root@oraclesrvr ~]# vi /etc/samba/smb.conf

Edit the following lines which are present at the end of file accordingly:
[regp]
comment = regp user stuff
path = /regp/
valid users = regpadmin regpuser
public = no
writable = yes
printable = no
create mask = 0765
:wq

[root@oraclesrvr ~]# cat /etc/passwd | mksmbpasswd.sh > /etc/samba/smbpasswd
[root@oraclesrvr ~]# chmod 600 /etc/samba/smbpasswd
[root@oraclesrvr ~]# smbpasswd regpadmin
[root@oraclesrvr ~]# smbpasswd regpuser
[root@oraclesrvr ~]# service smb restart

Now connect to the linux box from any windows machine in same network and try to create,modify or delete any file in the share created by you.
These permissions can be set according to your requirements.

Very Large Memory

2007-08-01T11:08:00.000+05:30

Creation of Database With Very Large Memory

Once the Oracle software is installed successfully, then the next major step is to create the database:

This document deals with database with Very Large Memory option i.e., allocating more than 1.7GB of SGA on a 32 bit Windows server.

In Windows 32 bit architechture(x86) there is limit for 2GB of memory usage that Windows will allow for any single process to consume.
And the databases in which a large amount of i/o takesplace needs much more memory than this limit. So, to allocate and allow oracle to use more than 2GB of memory we use VLM.

1. Open your boot.ini file and add /PAE and /3GB switches.

2. Install the Oracle Software.

3. Open the regedit file

start->run->regedit->find ORACLE_HOME key this will be in HKEY_LOCAL_MACHINE-->software-->oracle-->key_oracle_home(what ever your home is)

in the right hand pane right click and create a new string value with name AWE_WINDOW_MEMORY

Then double click thih new string and give the vaule in bytes that you require.
By default the value of this string is 1GB.

4. Restart the server to make this changes take effect.

5. create the database with memory greater than the 1.7GB SGA.

While allocating the SGA parameter values take care that you give the following parameter values:

Parameter NAME VALUE
-------------- -----
db_cache_size =0
db_2k_cahe_size =null
db_4k_cahe_size =null
db_8k_cahe_size =null
db_16k_cahe_size =null
db_32k_cahe_size =null
db_cache_advice =OFF

db_block_buffers =262144(2GB) i.e (262144*8192)
db_block_size =8192

shared_pool_size =600M
large_pool_size =48M
java_pool_size =24M
pga_aggregate_target =700M
sga_target =0
used_indirect_buffers=TRUE

Note:db-block_buffres is calculated with the following formula(max that can be allocated):

==>Your Server Physical Ram - 4GB + AWE_WINDOW_MEMORY(1GB)

So for example if your System Physical RAM is 9GB, then according to formula:

9-4+1=6GB

You can allocate upto 6GB value for your db_block_buffers.

Oracle 10G Installation on Red Hat Linux

2007-08-01T10:34:00.002+05:30

First Install Linux with all the rpms and packages required.

1. Create Groups oinstall & dba, and oracle user as follows:

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

give a new password

2. Edit the .bash_profile file of oracle user and add the following lines as follows:

#vi /home/oracle/.bash_profile

umask 022

PATH=/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin
LD_LIBRARY_PATH=/usr/lib:/usr/X11R6/lib

ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/10.1.0/db_1
ORACLE_SID='SERVICE_NAME'
PATH=$ORACLE_HOME/bin:$PATH

export PATH LD_LIBRARY_PATH
export ORACLE_BASE ORACLE_HOME ORACLE_SID

:wq(save and exit)

3. Then run the .bash_profile to make the current changes effect

#. .bash_profile

Create the directory for the software installation and assign ownership to oracle:oinstall. In the example, you use /u01/app/oracle.

#mkdir -p /u01/app/oracle
#mkdir -p /u01/app/oracle/product/10.1.0/db_1

Then change the permissions accordingly

#chown -R oracle:oinstall /u01/app
#chmod -R 775 /u01/app

4. Edit the /etc/inittab file and change the runlevel from 5 to 3.
Note: Not required if using Oracle's Enterprise Linux.

5. If your OS is greater than RHEL 3, then the Oracle Universal Installer does not recognize the OS, change the version to 3 as follows:

#cp /etc/redhat-release /etc/redhat-release.orig
#vi /etc/redhat-release

#And replace the line present in the file with
Red Hat Enterprise Linux AS release 3 (Taroon)
:wq

Once the installation is completed replace the orignal file back.

#cp /etc/redhat-release.orig /etc/redhat-release

overwrite:yes

6. Change the Kernel Parameters according to your Hardware:

#vi /etc/sysctl.conf

kernel.sem= 250 32000 100 128
kernel.shmmax = 2147483648
kernal.shmmni = 4096
kernel.shmall = 262144000
fs.file-max= 1024
net.ipv4.ip_local_port_range= 1024 65000

:wq

Note: If any of the value is more than the value given above don't change those values.

7. To make the changes effect immediately without reboot give the following command:

#/sbin/sysctl -p

8. REBOOT(init 6) the system and login as oracle User:

9. You need to install Oracle in the GUI mode so change the display mode to GUI by giving followinh command:

$startx

10. Mount the media disk which contains the Oracle 10G software:
Note: It is recommended that you copy the contents of the CD onto the Filesystem and start the installation:

$cp /mnt/disk1/*.* /home/oracle/oracle10g

Give total permissions on the newly created folder oracle10g to oracle user

$chmod -R 777 /home/oracle/oracle10g

11. Then Start the installation as follows:

$sh /home/oracle/oracle10g/runinstaller.sh

The OUI is started and continue Installation of Oracle same as in Windows:

next>next>next>.....................................Finish.

Inbetween it prompts you to run two scripts as root

DON'T close the current window until you run the respected script as root in a new terminal.

1st script:

#root>sh /u01/app/oracle/oraInventory/orainstall.sh

After executing this script as root go to the previous screen and press the continue tab

2nd script:

#root> sh /u01/app/oracle/product/10.1.0/db_1/root.sh

After executing this script as root go to the previous screen and press the OK tab.

Your Installation of Oracle 10G on Linux is completed successfully(hopefully).

Oracle DB Auto Start / Shutdown

2007-07-31T09:54:00.000+05:30

How to Configure a Linux x86 Server for Oracle DB Auto Start / Shutdown

Applies to: Oracle Server - Enterprise Edition - Version: From 8.1.7.4 to 10.1.0.3 on Linux x86(32 Bit)

The Database server software provides the two scripts to configure automatic DB startup/shutdown with the server machine.
They are

$ORACLE_HOME/bin/dbstart
$ORACLE_HOME/bin/dbshut

We need to call these scripts from the unix start/shutdown scripts (rc0.d / rc1.d etc.)

Step - A:

Check the oratab file in /etc/oratab or /var/opt/oracle/oratab
This should have the entry for the DB we are dealing with, with a value Y, like:
$ORACLE_SID:$ORACLE_HOME:Y

Step - B:

Login to root. Create a new file with name like dbora and
Save the following file in /etc/init.d/
Please note that /etc/init.d is RedHat specific.
Change the permissions accordingly

#chmod 755 /etc/init.d/dbora

-rwxr-xr-x 1 root root 1412 Aug 27 19:14 dbora

Mention the correct ORA_OWNER and ORA_HOME in the dbora

------------------ Edit dbora ---------------------------------

#! /bin/bash
#
# oracle Start/Stop the Databases...
#
# chkconfig: 2345 99 10
#
# processname: oracle
# config: /etc/oratab
# pidfile: /var/run/oracle.pid

# Source function library.
. /etc/init.d/functions

RETVAL=0
ORA_OWNER="oracle"
ORA_HOME="/u01/app/oracle/product/10.1.0/db_1"

# See how we were called.

prog="oracle"

start() {
echo -n $"Starting $prog: "
su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart"
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"
RETVAL=$?
echo
[ $RETVAL -eq 0 ] && rm -f /var/lock/subsys/dbora

return $RETVAL
}

stop() {
echo -n $"Stopping $prog: "
su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut"
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
}

restart() {
stop
start
}

case "$1" in
start)
start
;;
stop)
stop
;;
restart)
restart
;;
*)
echo $"Usage: $0 {start|stop|restart}"
exit 1
esac

exit $?

------------------ Save and exit(wq) dbora ---------------------------------

Step - C: Login as root run the following:

# cd /sbin
# chkconfig --add dbora

This will create a system service viz. dbora
# chkconfig --list

This also creates the following files:

/etc/rc2.d/S99dbora ( calls $ORACLE_HOME/bin/dbstart )
/etc/rc3.d/S99dbora ( calls $ORACLE_HOME/bin/dbstart )
/etc/rc4.d/S99dbora ( calls $ORACLE_HOME/bin/dbstart )
/etc/rc5.d/S99dbora ( calls $ORACLE_HOME/bin/dbstart )

/etc/rc0.d/K10dbora ( calls $ORACLE_HOME/bin/dbshut )
/etc/rc1.d/K10dbora ( calls $ORACLE_HOME/bin/dbshut )
/etc/rc6.d/K10dbora ( calls $ORACLE_HOME/bin/dbshut )

chkconfig refers the " # chkconfig: 2345 99 10 " from dbora.
This signifies that the service has start run level set to 2, 3, 4 and 5.Stop run level set to 0, 1 and 6. And the start priority should be 99 and stop priority be 10.

If the version is Red Hat 3 ES or more than the dbora file needs two lines of comments.
Each service which should be manageable by chkconfig needs two or more commented lines
added to its init.d script. The second line contains a description for the service,
and may be extended across multiple lines with backslash continuation.

For example,
# chkconfig: 2345 99 10
# description: Saves and restores system entropy pool for higher quality random number generation

Step - D: Reboot the Linux box and check

In some cases for 9.2.0 we also had to copy the init file for SID "test" from /u01/app/oracle/admin/test/pfile to $ORACLE_HOME/dbs to get dbstart and dbshut working:
cp /u01/app/oracle/admin/test/pfile/inittest.ora.642002224936 $ORACLE_HOME/dbs/inittest.ora
But first make sure if your init file already exists in $ORACLE_HOME/dbs!
Note: An article from Metalink customized for my environment. May help you.

Tuning Database Buffer Cache

2007-06-30T10:29:00.000+05:30

Introduction

Buffer pool or buffer cache is a memory structure within Oracle SGA that is used for caching data blocks in the memory. Accessing data from the memory is significantly faster than accessing data from disk. The goal of block buffer tuning is to efficiently cache frequently used data blocks in the buffer cache (SGA) and provide faster access to data.

Tuning block buffer is a key task in any Oracle tuning initiative and is a part of an ongoing tuning and monitoring of production databases. The task of database performance optimization by allocating appropriate memory structure within SGA is a very interesting and challenging task for a DBA. Generally, it takes a few iterations before you can reach to a satisfactory memory allocation. This article presents a tuning approach that greatly simplifies the tuning effort. The methodology discussed here will help DBAs in creating a proactive database tuning and monitoring strategy for their production databases. If you are a DBA responsible for tuning a database without enough physical memory on the server, you will find this article very informative and useful. With the help of an established methodology and tuning strategy, you can easily identify the problem areas and tune the SGA memory structure.

Step 1: Identify the amount of memory that you can allocate to block buffers. Create buffer pool without inducing significant paging or swapping.

Deciding the size of block buffer is critical to the overall system performance. A large block buffer will provide more memory space for caching data blocks in the SGA. In such a case, most SQL statements will find data blocks in the memory and a less time consuming IO will be performed by the database. The more the data cached in the memory, the faster is the database response. Life of DBAs would have been much simpler if we could cache the entire database (or even 50% of the database) in the block buffers, but we are limited by the physical memory available on the server. If we allocate a very large block buffer, the server may swap / page the SGA into a swap space on the disk (If SGA in not locked in the physical memory). In such circumstances, data will no longer be accessed from the memory, and CPU will have to work harder to manage the virtual memory of the server, thus degrading the overall system performance. A very large block buffer may defeat the purpose of caching data in memory for faster access, if the operating system is swapping / paging SGA pages. Within the limitations of the physical memory, the DBA should allocate block buffers to cache data in the SGA without inducing paging or swapping on the server. Hence, understanding the amount of memory available for block buffers is key to tuning the block buffers.

You can set the buffer cache by specifying DB_CACHE_SIZE (or DB_BLOCK_BUFFERS) initialization parameter as:-

DB_CACHE_SIZE = 128 M

Step 2: Identify how frequently data blocks are accessed from the buffer cache (a. k. a Block Buffer Hit Ratio).

Once you have allocated the available memory to block buffers, the next step is to identify the efficiency of block buffer usage. Oracle database maintains dynamic performance view V$BUFFER_POOL_STATISTICS with overall buffer usage statistics. This view maintains the following counts every time a data block is accessed either from the block buffers or from the disk:

NAME – Name of the buffer pool
PHYSICAL_READS – Number of physical reads
DB_BLOCK_GETS – Number of reads for INSERT, UPDATE and DELETE
CONSISTENT_GETS – Number of reads for SELECT

DB_BLOCK_GETS + CONSISTENT_GETS = Total Number of reads

Based on above statistics we can calculate the percentage of data blocks being accessed from the memory to that of the disk (block buffer hit ratio). The following SQL statement will return the block buffer hit ratio:

SELECT NAME, 100 – round ((PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS))*100,2) HitRatio
FROM V$BUFFER_POOL_STATISTICS;

Before measuring the database buffer hit ratio, it is very important to check that the database is running in a steady state with normal workload and no unusual activity has taken place. Buffer hit ratio can be skewed depending on the type of SQL statements running in the database since startup. For example, when you run a SQL statement just after database startup, no data blocks have been cached in the block buffers. At this point, Oracle reads the data blocks from the disk and will cache the blocks in the memory. If you run the same SQL statement again, then most likely the data blocks will still be present in the cache, and Oracle will not have to perform disk IO. If you run the same SQL statement multiple times you will get a higher buffer hit ratio. On the other hand, if you either run SQL statements that rarely query the same data, or run a select on a very large table, the data block may not be in the buffer cache and Oracle will have to perform disk IO, thereby lowering the buffer hit ratio.

A hit ratio of 95% or greater is considered to be a good hit ratio for OLTP systems. The hit ratio for DSS (Decision Support System) may vary depending on the database load. A lower hit ratio means Oracle is performing more disk IO on the server. In such a situation, you can increase the size of database block buffers to increase the database performance. You may have to increase the physical memory on the server if the server starts swapping after increasing block buffers.

Step 3: Identify frequently used and rarely used data blocks. Cache frequently used blocks and discard rarely used blocks.

If you have a low buffer hit ratio and you cannot increase the size of the database block buffers, you can still gain some performance advantage by tuning the block buffers and efficiently caching the data block that will provide maximum benefits. Ideally, we should cache data blocks that are either frequently used in SQL statements, or data blocks used by performance sensitive SQL statements (A SQL statement whose performance is critical to the system performance). An ad-hoc query that scans a large table can significantly degrade overall database performance. A SQL on a large table may flush out frequently used data blocks from the buffer cache to store data blocks from the large table. During the peak time, ad-hoc queries that select data from large tables or from tables that are rarely used should be avoided. If we cannot avoid such queries, we can limit the impact on the buffer cache by using RECYCLE buffer pool.

A DBA can create multiple buffer pools in the SGA to store data blocks efficiently. For example, we can use RECYCLE pool to cache data blocks that are rarely used in the application. Typically, this will be a small area in the SGA to store data blocks for current SQL statement / transaction that we do not intend to hold in the memory after the transaction is completed. Similarly, we can use KEEP pool to cache data blocks that are frequently used by the application. Typically, this will be big enough to store data blocks that we want to always keep in memory. By storing data blocks in KEEP and RECYCLE pools you can store frequently used data blocks separately from the rarely used data blocks, and control which data blocks are flushed from the buffer cache. Using RECYCLE pool, we can also prevent a large table scan from flushing frequently used data blocks. You can create the RECYCLE and KEEP pools by specifying the following init.ora parameters:

DB_KEEP_CACHE_SIZE =
DB_RECYCLE_CACHE_SIZE = < size of RECYCLE pool>

When you use the above parameters, the total memory allocated to the block buffers is the sum of DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE, and DB_CACHE_SIZE.

Step 4: Assign tables to KEEP / RECYCLE pool. Identify buffer hit ratio for KEEP, RECYCLE, and DEFAULT pool. Adjust the initialization parameters for optimum performance.

By default, data blocks are cached in the DEFAULT pool. The DBA must configure the table to use the KEEP or the RECYCLE pool by specifying BUFFER_POOL keyword in the CREATE TABLE or the ALTER TABLE statement. For example, you can assign a table to the recycle pool by using the following ALTER TABLE SQL statement.

ALTER TABLE TABLE_NAME STORAGE (BUFFER_POOL RECYCLE)

The DBA can take help from application designers in identifying tables that should use KEEP or RECYCLE pool. You can also query X$BH to examine the current block buffer usage by database objects (You must log in as SYS to query X$BH). The following query returns a list of tables that are rarely used and can be assigned to the RECYCLE pool.

Col object_name format a30
Col object_type format a20
SELECT o.owner, object_name, object_type, COUNT(1) buffers
FROM SYS.x$bh, dba_objects o
WHERE (tch = 1 OR (tch = 0 AND lru_flag < 8))
AND obj = o. object_id
AND o.owner not in ('SYSTEM','SYS')
GROUP BY o.owner, object_name, object_type
ORDER BY buffers;

The following query will return a list of tables that are frequently used by SQL statements and can be assigned to the KEEP pool.

SELECT o.owner, object_name, object_type, COUNT(1) buffers
FROM SYS.x$bh, dba_objects o
WHERE tch > 10
AND lru_flag = 8
AND obj = o.object_id
AND o.owner not in ('SYSTEM','SYS')
GROUP BY o.owner, object_name, object_type
ORDER BY buffers;

Once you have setup the database to use KEEP and RECYCLE pools, you can monitor the buffer hit ratio by querying V$BUFFER_POOL_STATISTICS and V$DB_CACHE_ADVICE to adjust the buffer pool initialization parameters.

Step 5: Identify the amount of memory needed to maintain required performance.

Oracle 9i maintains block buffer advisory information in V$DB_CACHE_ADVICE. This view contains simulated physical reads for a range of buffer cache sizes. The DBA can query this view to estimate buffer cache requirement for the database. The cache advisory can be activated by setting DB_CACHE_ADIVE initialization parameter.

DB_CACHE_ADVICE = ON

There is a minor overhead associated with cache advisory collection. Hence, it is not advisable to collect these statistics in production databases until there is a need to tune the buffer cache. The DBA can turn on DB_CACHE_ADVISE dynamically for the duration of sample workload period and collect advisory statistics.

Conclusion

Using this methodical approach, a DBA can easily identify the problem areas, and tune the database block buffers. The DBA can create the following buffer pool to efficiently cache data blocks in SGA:

KEEP: Cache tables that are very critical for system performance. Typically, lookup tables are very good candidates for the KEEP pool. The DBA should create the KEEP pool large enough to maintain 99% buffer hit ratio on this pool.

RECYCLE: Cache tables that are not critical for system performance. Typically, a table containing historical information that is either rarely queried or used by batch process is a good candidate for the RECYCLE pool. The DBA should create the RECYCLE pool large enough to finish the current transaction.

DEFAULT: Cache tables that do not belong to either KEEP or RECYCLE pool.
The DBA can setup OEM jobs, Oracle statspack, or custom monitoring scripts to monitor your production database block buffer efficiency, and to identify and tune the problem area.

Sql Statement We Use to Generate Tablespace Usage Report

2007-06-29T16:22:00.000+05:30

A Sql Statement We Use to Generate Tablespace Usage Report Taking Autoextend Into Consideration

This article describes the query we created in order to generate Database level Tablespace usage in percentage with Autoextend on.
In past we used a query which was showing total assigned space to a particular tablespace and was not including future growth when Autoextend On.

So it was giving wrong numbers:
:-> Example: Size of tablespace is 1GB – Used Space is 900MB – Free Space is 100MB –

Now it has Autoextend On with 4GB Total extension capabilities.

It was sending us Paging telling only 10 Percentage free –instead it should take Total 4GB in consideration and by that it has total 3.1GB Free. Soo we dug into the data dictionary and found base sys views/tables from where we can get that information and refined our query with the same.

Query:

REM Following Sql statement will generate tablespace usage report taking autoextend into consideration
sqlplus "/ as sysdba"
set linesize 250
set pagesize 70
col tablespace_name format a20
select a.tablespace_name, SUM(a.bytes)/1024/1024 "CurMb",
SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) "MaxMb",
(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)) "TotalUsed",
(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))) "TotalFree",
round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) "UPercent"
from dba_data_files a,
sys.filext$ b,
(SELECT c.tablespace_name , sum(c.bytes) "Free" FROM DBA_FREE_SPACE c GROUP BY TABLESPACE_NAME) c
where a.file_id = b.file#(+)
and a.tablespace_name = c.tablespace_name
GROUP by a.tablespace_name, c."Free"/1024
/
exit;

An Approach to Tuning a SQL Statement

2007-06-29T16:08:00.000+05:30

While application knowledge and experience is helpful, We've got a methodical approach to use when asked to tune a SQL statement.

Here's the basic approach/process that can be used to diagnose and analyze a SQL statement..

Analyze the Statement

1. We can start by reviewing the WHERE clause, dividing it into clauses that restrict rows and those that join tables.

2. We can locate candidate Driving Tables by looking at the "restrict rows" clauses as determining which table has the most restrictive clauses ( i.e. those that will result in the fewest rows). If not sure between a couple of candidates, we can pick one.

3. Next we have to list all the tables in the FROM clause, starting with the driving table, using the JOIN criteria to link from one table to the next. This generates an ordered list of how the database should be accessing the tables, starting with the candidate driving table. This exercise generates all possible ways to join the tables together as well as making sure there are Join criteria for all the tables in the FROM clause.

4. Look for indexes on the driving table and determine which should be used or if a full table scan would be best. If we find a better index than the one the optimizer picks then we add an Index hint to get the desired index to be used. If a full table scan is in order, we consider adding a parallel hint.

5. We can try to join two tables that have restrictive where clauses before joining two tables that don't have any such clauses.

6. We now have one or more candidate explain plan(s) that need to be tested.
Compare Plans

1. Next we compare the actual explain plan of the statement in question with the candidate plans generated in the above exercise

2. Looking at the actual data (and running some queries to collect metrics) we consider trying different driving tables and join orders.

3. We review and compare each, and subjectively arrive at a best guess plan.

Test it out

1. The most effective hint we've found is the ORDERED hint. This works especially well when you have a lot of tables in the FROM clause.

2. We add /*+ ordered */ after the Select verb and the list the tables in the from clause from first to last the way we listed them in Analysis Step 3. Adding other hints like /*+ Full (driving table alias) */ is also a good way to get the optimizer to use the proper driving table.

3. Run an explain plan using the above HINTs.

4. If the plan looks good, execute it for real. Set Timing On and set Autotrace On (hopefully you have that enabled in your database)

5. Run the old and new statements several times to see how the timings work out. The Autotrace summary of blocks touched is a. good indicator of performance: the fewer blocks touched, the better.

Tuning Hints and Tips

1. There are many other hints and techniques that can influence an explain plan. Keep in mind that a Hash Join is nearly always better than a Merge Join

2. Nested Loop is better only if a few rows are being retrieved.

3. Tuning sub-selects can be tricky. We will often rewrite a statement to replace " IN (Select...) or a NOT IN" with a join to an in-line view and achieve significant performance increase by avoiding executing thousands of Nested Loop index reads with a hash join. Listing more statement rewrite options is beyond the scope of this article.

Those are the basic steps We follow. Give them a try and over time we are confident your SQL tuning skills will expand.

Manually un-installed Oracle 10g

2007-06-29T16:01:00.001+05:30

How we Manually un-installed Oracle 10g - Windows

As a testing box for RMAN and Flashback query, we are supposed to install and perform the required tests on 10g DB. During the first iteration we encountered that OUI was not responding, I believed it to be the security settings on the Windows box. But it was not getting better after changing the security settings on Windows.

Here the steps we took to manually un-install Oracle 10g on Windows 2003 / XP.

On the desktop, we Right Click on MyComputer Icon and selected "Manage". On the left hand pane, we selected "Service and Applications" from Computer Management.

** Alternatively you can also select Services from Control Panel.

In the next step we Shutdown Oracle Instance and related Oracle Services. Some of them might give errors " Unable to shutdown". It could be because of improper installation. Now after shutting down the Oracle services, we had to locate the registry entries for Oracle and delete them manually.

We made a backup of registry.

We then clicked on Start --> Run --> Regedit.

Looked for the following entries:
=============================================================================
HKEY_CURRENT_USER\SOFTWARE\ORACLE
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Application\Oracle.oracle
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\OracleDBConsole
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Oracle10g_home
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\OraclService
=========================================================================
Deleting the above entries from the registry required a reboot, so we did a Reboot of the windows box.
Some entries in the registry like registering of some components required cleaning software. We did it using "CleanMyPCRegistry”. This removed any discrepancies in the registry.

Now in the next step we deleted the physical directories.

C:\Oracle

Deleted the Oracle Program Files directory:
C:\Program Files\Oracle
Deleted the Oracle Start Menu shortcuts directory:
C:\Documents and Settings\All Users\Start Menu\Programs\Oracle

Removed Oracle references from the path. To edit the path we went to:

Start->Settings->Control Panel->System->Advanced->Environment Variables

We edited both of the environment variables user PATH and system PATH. Remove any Oracle references in them.

Then we removed Oracle.DataAccess and any Polic.Oracle files from the GAC which is at:
C:\Windows\assembly\

We also received an error while removing the $ORACLE_HOME/bin directory. While un-installing oci.dll (Oracle Call interface could not be deleted.

To resolve this, we renamed the Oracle/bin folder and re-boot the machine. After reboot, we are able to delete the oci.dll. We then proceeded with the proper installation of Oracle 10g R2.

Implementing Address Windowing Extensions (AWE) or VLM on Windows Platforms

2007-06-29T15:54:00.000+05:30

PURPOSE
-------

To address the growing need for use of more memory on 32-Bit Windows platforms,
and explain how AWE is implemented by Oracle on Windows.

SCOPE & APPLICATION
-------------------
Oracle DBA's running on the Microsoft Windows platform.
Oracle Support Analysts, Field Engineers troubleshooting problems
related to AWE and/or memory issues on Windows.

AWE Memory implementation on Windows 2000
------------------------------------------

A common question on the Windows NT/Windows 2000 platform revolves around
how to take advantage of systems with more than 4 GB of RAM. As discussed
in Metalink Note 46001.1 and Note 46053.1, the 32-Bit process address
space for any process on Windows equates to a total of 4GB of addressable
RAM. Of this, by default, 2GB is reserved for the process itself, and 2GB
for the kernel. On systems running either Windows 2000 Advanced Server,
or Windows NT 4.0 Enterprise Edition, this ratio can be changed by adding
the /3GB switch to the boot.ini, allowing a process to address 3GB and
reserving 1GB for the kernel. However, the total addressable memory for
a single process is still only 4GB.
See also Note 1036312.6 : Utilizing Up to 3GB Virtual Memory on Windows NT Server 4.0

__________________________________________________________________

What can be done to address memory beyond 4GB?:
===============================================

The answer is to take advantage of Physical Address Extensions (PAE), or
Address Windowing Extensions (AWE)(These two terms are used interchangeably,
so the rest of this document will refer to this simply as AWE).
AWE support is available if you are running on a machine with more than 4GB
of physical RAM which is running any of the below Windows operating systems:

* Windows 2000 Datacenter Server
* Windows 2000 Advanced Server
* Windows 2003 Data Center Edition (32-Bit)
* Windows 2003 Enterprise Edition (32-Bit)

On the above operating systems, AWE support is built into the OS. No
special drivers are needed to take advantage of the additional memory.

AWE CANNOT be used on the following Operating Systems:

* Windows 2000 Server (Standard)
* Windows 2000 Professional
* Windows XP Home Edition
* Windows XP Professional
* Windows 2003 Standard Edition
* Windows 2003 Web Edition

NOTE Also that on 64-Bit Windows operating systems, there is no need for AWE
implementation support, because the directly addressable memory for a single
process on 64-Bit Windows is 8 Terabytes.

__________________________________________________________________

Oracle versions that can use AWE:
=================================

Oracle can take advantage of AWE in the following RDBMS releases:

* Oracle 8.1.6.x
* Oracle 8.1.7.x
* Oracle 9.2.x
* Oracle 10.1.x

Oracle does NOT implement AWE support in release 9.0.1.x

AWE support is available on both the Enterprise Edition of Oracle and
the Standard Edition of Oracle. However, on Standard Edition of 9.2.0.1,
you may receive the following error if trying to start the database with
USE_INDIRECT_DATA_BUFFERS=TRUE:

ORA-439 - FEATURE NOT ENABLED: VERY LARGE MEMORY

In Standard Edition 9.2.0.2 and 9.2.0.3, you will not receive the above errors,
but VLM functionality is still not enabled. Refer to BUG#2945011 for more detail.
This BUG is fixed in 9.2.0.3 Patch 2, and will be fixed in 9.2.0.4 as well.

__________________________________________________________________

Enabling support at the OS level:
==================================

AWE can be enabled at the OS by adding the /PAE switch to the boot.ini
as such:

multi(0)disk(0)rdisk(0)partition(1)\WINNT="Microsoft Windows 2000 Advanced Server" /PAE

It IS possible to have BOTH the /PAE and /3GB switch in place on the same
machine, as such:

multi(0)disk(0)rdisk(0)partition(1)\WINNT="Microsoft Windows 2000 Advanced Server" /3GB /PAE

However, be aware that if BOTH switches are in place, the server will only
be able to recognize up to 16GB of RAM. If you are working with a server
with more than 16GB of RAM, you will need to choose between the two.

It is important to note that once either or both of these switches are in
place in the boot.ini, ALL processes running can take advantage of these
switches. Thus, in a case where multiple Oracle instances are running on
the same server, ALL instances can take advantage of the additional memory
afforded by these switches, up to the amount of physical memory on the box.

Operating System Privileges Needed at the OS Level:
====================================================

In order to take advantage of the additional memory afforded through PAE,
the operating system user account which is used to start the OracleService
must be granted the 'Lock Pages in Memory' system privilege at the operating system
level. By default, the OracleService starts as the LocalSystem account.
The LocalSystem account has the privilege to Lock Pages in Memory granted to
it by default.

However, if you change the OracleService to logon as a user OTHER than
LocalSystem, you may see the following errors when attempting to start the
database with USE_INDIRECT_DATA_BUFFERS set to TRUE :

SQL> startup pfile=c:\temp\initscott.ora
ORA-27102: out of memory
OSD-00010: Message 10 not found; product=RDBMS; facility=SOSD

O/S-Error: (OS 1300) Not all privileges referenced are assigned to the caller.

To rectify this, you must grant the 'Lock pages in memory' privilege to the user
that the OracleService starts as. To do this, click on:
Start -> Programs -> Administrative Tools -> Local Security Policy
(on a Domain Controller, click on 'Domain Security Policy' instead of 'Local Security Policy')
Double-click on the 'Lock Pages in memory' policy.
Add the appropriate user and click 'Ok'.
Restart the OracleService

__________________________________________________________________

Understanding the Oracle implementation of AWE support:
=======================================================

What the PAE switch allows you to do from the Oracle perspective is to
increase the amount of memory that can be used for the Oracle Database
Block Buffer Cache. It is important to note that this additional memory
can ONLY be used by Oracle in the form of an increased value for
DB_BLOCK_BUFFERS.

There is still confusion on the old style of VLM versus AWE on Windows 2000.
With VLM on Windows NT 4.0, there was the concept of pointers pointing to
the extended memory area, but that is no longer the case on Windows 2000.
Instead, the windowing technology as described in these articles is being
used. For more information on AWE/PAE implementation on the Windows
platform, refer to Microsoft's website.

As mentioned previously, with AWE enabled, this allows the process(es)
(in this case ORACLE.EXE) to use memory above and beyond the 4GB
mark defined by a 32-Bit Process Address space. The physical location of
these blocks does not matter. However, the database blocks must still be
accessed from within a ‘window’, which exists (logically) in that regular
3GB process address space.
The size of this window is defined by a registry setting in the HOME key for
Oracle (HKLM\Software\Oracle\Homex) called AWE_WINDOW_MEMORY. By default,
this value is 1GB, so if this value is not set in the registry,
AWE_WINDOW_MEMORY will be 1GB.

It is important to realize that any database blocks accessed by Oracle
(or any user/background thread within Oracle.exe) must first be mapped into
the 'window' defined by AWE_WINDOW_MEMORY. In this scenario, it does not
matter where the blocks are physically located - there is no need to be
concerned with where the blocks are physically residing. The window will be
drawn around the block (i.e. the block will be mapped) wherever it is located
in memory. If the block is in memory but has not been mapped into the
‘window’, then it may be necessary to unmapped another block that IS in the
window, in order to accommodate the new block. While this mapping and
unmapping of blocks does add some cost, it is still faster than incurring
an I/O operation to read the block from disk. This will be discussed
further down in the section on troubleshooting.

Note:

Keep in mind that if there are multiple instances on a machine with
the /PAE switch enabled, ALL instances can take advantage of the additional
memory. However, AWE_WINDOW_MEMORY cannot be set on a per-instance basis,
so all databases that are running out of the HOMEx key where
AWE_WINDOW_MEMORY is set will inherit the same value.

__________________________________________________________________

Enabling AWE Support at the Database/Instance Level:
====================================================

To enable the AWE implementation on Oracle, you must set the following
parameter in the init file (or spfile) used to start the instance:

USE_INDIRECT_DATA_BUFFERS=TRUE

Note again that the buffer cache MUST be defined using the parameter
DB_BLOCK_BUFFERS, no matter what version of the RDBMS you are running.
The 9.2 feature allowing for Multiple block sizes in a database will be
disabled if you set USE_INDIRECT_DATA_BUFFERS=TRUE, and you cannot specify
the DB_CACHE_SIZE parameter to define the size of the buffer cache.

On 9.2, if you attempt to startup a database with this combination of
parameters:

USE_INDIRECT_DATA_BUFFERS=TRUE
DB_CACHE_SIZE=xxxxx (Any number)

The startup will fail with the following error:

SQL> startup
ORA-00385: cannot enable Very Large Memory with new buffer cache
parameters

You must change DB_CACHE_SIZE to use DB_BLOCK_BUFFERS instead, as was the
syntax under Oracle8i and earlier.

__________________________________________________________________

AWE_WINDOW_MEMORY Within the 3GB Process Address Space:
=======================================================

If you are using /PAE and the /3GB switch together, the address space for
ORACLE.EXE will be 3GB. The value for AWE_WINDOW_MEMORY must come from the
normal address space used by the ORACLE.EXE process. Memory that comes
from that 3GB address space addressable by the oracle.exe process includes
the following:

·The Value for AWE_WINDOW_MEMORY
·The rest of the SGA (shared_pool, large_pool, java_pool, log_buffers, etc)
·Overhead for Oracle.exe and DLL’s (65-100M depends on version & options)
·Stack space for all threads (Defaults to 1MB/thread, unless orastack
is used)
·PGA and UGA memory for all user sessions

Therefore, the value for AWE_WINDOW_MEMORY should be tuned such that mapping
and unmapping operations are avoided as much as possible, while still
allowing enough memory within the 3GB address space for the rest of the
process memory that MUST fit within the 3GB (i.e. overhead, remaining SGA
components and all user connection memory (stack + uga + pga) noted above).

The total size of the buffer cache can then be set to the amount of
physical memory remaining above the 4GB barrier, plus AWE_WINDOW_MEMORY.
On a machine with 12GB of RAM, using the default value of 1GB for
AWE_WINDOW_MEMORY, your total buffer cache could theoretically be as high
as 9GB:

(Total RAM - 4GB + AWE_WINDOW_MEMORY) = 12GB - 4GB + 1GB = 9GB

In reality, your maximum buffer cache size will be somewhat less than
this, allowing for some overhead and additional processes running on the
system.

Attempting to startup the database with a buffer cache larger than the
maximum value as calculated above may result in the following errors:

ORA-27102 out of memory
OSD-00034 Message 34 not found; Product=RDBMS;facility =SOSD
O/S Error: (OS 8) Not enough storage is available to process this command

(Note - If you are on Release 9.2, another possible cause for these errors
is noted further down, in the troubleshooting section)

As mentioned above, the buffer cache must be specified using
DB_BLOCK_BUFFERS rather than DB_CACHE_SIZE, so assuming an 8K block
size (8192), to get a 9GB buffer cache, you would set the following init
parameters:

DB_BLOCK_BUFFERS = 1179648
DB_BLOCK_SIZE = 8192

__________________________________________________________________

Troubleshooting AWE_WINDOW_MEMORY implementation:

=========================
=========================

Minimum Value Required for AWE_WINDOW_MEMORY in 9.2 and Above:
==============================================================

Here are key points to understand when using AWE_WINDOW_MEMORY:

1. Under Oracle 8.1.7 we do NOT enforce a minimum value for
AWE_WINDOW_MEMORY to be able to start the database.
2. This was changed under Oracle9i Release 2, such that we DO
enforce a minimum value for AWE_WINDOW_MEMORY. This change was
done to help improve performance by enforcing a larger window size.
3. You can alter the minimum required value for AWE_WINDOW_MEMORY
under 9.2 by changing/setting the value of the parameter
_DB_BLOCK_LRU_LATCHES. Under 8.1.7, this parameter was named
DB_BLOCK_LRU_LATCHES. However, under 9.x, this parameter was
changed to be a hidden parameter.

The minimum value for AWE_WINDOW_MEMORY starting with 9.2 is calculated as such:

MIN(AWE_WINDOW_MEMORY)=(4096 * DB_BLOCK_SIZE * _DB_BLOCK_LRU_LATCHES)/8

Starting with 9.2, to calculate the value for _DB_BLOCK_LRU_LATCHES, we need
this formula:

_DB_BLOCK_LRU_LATCHES = (Max buffer pools * SETS_PER_POOL)

Max Buffer Pools is a constant = 8
SETS_PER_POOL is variable, and depends on whether or not VLM is enabled.

SETS_PER_POOL = 2* CPU_COUNT (if VLM is enabled)
SETS_PER_POOL= CPU Count /2 (If VLM is NOT enabled)

/* Recall that VLM is enabled by setting USE_INDIRECT_DATA_BUFFERS=TRUE

So, as you can see, the value for _DB_BLOCK_LRU_LATCHES in 9.2 and above is
dependent on the number of CPU's in the box, and therefore
MIN(AWE_WINDOW_MEMORY) is dependent on the # of CPU's as well as the
DB_BLOCK_SIZE. The larger the Block Size, and the more CPU's in a system,
the higher the value for MIN(AWE_WINDOW_MEMORY). Here are a couple of
example configurations and caclulations showing MIN(AWE_WINDOW_MEMORY).

Example #1:
----------------
# of CPU's = 8
DB_BLOCK_SIZE = 8192
Total RAM = 8GB

SETS_PER_POOL = 2 * CPU_COUNT = 16
_DB_BLOCK_LRU_LATCHES = (max buffer Pools * sets_per_pool) = 8*16 = 128
MIN(AWE_WINDOW_MEMORY) =(4096*DB_BLOCK_SIZE*_DB_BLOCK_LRU_LATCHES)/8 =
( 4096 * 8192 * 128) / 8 = 536870912 bytes = 512 MB

Example #2:
---------------
# of CPU's = 16
DB_BLOCK_SIZE = 8192
Total RAM = 16 GB

SETS_PER_POOL = 2 * CPU_COUNT = 32
_DB_BLOCK_LRU_LATCHES = (max buffer Pools * sets_per_pool) = 8*32 = 256
MIN(AWE_WINDOW_MEMORY) =(4096*DB_BLOCK_SIZE*_DB_BLOCK_LRU_LATCHES)/8 =
( 4096 * 8192 * 256) / 8 = 1073741824 bytes = 1024 MB

These values above are the minimum values required for AWE_WINDOW_MEMORY
to be set to, UNLESS you explicitly set _DB_BLOCK_LRU_LATCHES to a lower
value. If AWE_WINDOW_MEMORY is not set to the minimum value, you will
receive the following errors:

ORA-27102 out of memory
OSD-00034 Message 34 not found; Product=RDBMS;facility =SOSD
O/S Error: (OS 8) Not enough storage is available to process this command

If you receive these errors when trying to start the database under 9.2 or 10g,
this may be because the AWE_WINDOW_MEMORY value in the registry is set
too low for the calculated minimum value. If you cannot increase the
value for AWE_WINDOW_MEMORY, then you can explicitly set
_DB_BLOCK_LRU_LATCHES to a value lower than the calculated value, and
retry the startup.

_DB_BLOCK_LRU_LATCHES must be at least 8 (Equal to the maximum number of
buffer pools)

Note #1 - Recall from the earlier section that these errors may also occur if
you are trying to start up with a buffer cache that is too large for the
physical memory available.

Note #2 - The same errors above have also been observed with a buffer
cache that is too small. When USE_INDIRECT_DATA_BUFFERS is set to TRUE
the value for DB_BLOCK_BUFFERS should equate to a buffer cache that is
AT LEAST equal to AWE_WINDOW_MEMORY. In most cases, the total buffer
cache size will be greater than AWE_WINDOW_MEMORY. If you attempt to
start up with a buffer cache that is too small (i.e. < AWE_WINDOW_MEMORY)
that may also result in the ORA-27102 error.

Note#3 - It has been observed on some systems that you may need to add a few
additional meg to AWE_WINDOW_MEMORY to calculate for overhead. Therefore, if
you go through the above calculations, and the instance still does not start,
try adding an additional 10 Meg or so to the calculated value.

Note#4 - Also, keep in mind that when calculating the # of CPU's in the system,
you have to take hyperthreading into account. On a hyperthreaded system, the OS
will think that you have double the # of CPU's in the system over what you actually
have, and this is the number that must be used in the calculations.

How to calculate the maximum used memory
=========================================
With respect to awe_window_memory the following maximum amount of memory can be used
from physical memory:

The SGA size is composed from:
((db_block_buffers * block size) + (shared_pool_size + large_pool_size +
java_pool_size + log_buffers) + 1MB
The size of SGA + Oracle's overhead must not exceed the available virtual memory.

The size of buffer cache depends on the available virual memory and can be calculated with
buffer cache = db_block_buffer * db_block_size

CPU Spins Possible When Using AWE Implementation:
=================================================

Use caution when setting _DB_BLOCK_LRU_LATCHES or AWE_WINDOW_MEMORY too low.
If we are unable to map a requested buffer into the window because all of
the space defined by AWE_WINDOW_MEMORY is in use with buffers already
actively being accessed, then we spin and wait, checking every so often
until an existing buffer in the window can be unmapped, and a new buffer can
be mapped in.

This spin will consume CPU cycles until enough buffers can be
Mapped/Unmapped to satisfy the request. In some cases, there may be so
many buffers needing to be mapped into the window, that DBWR will consume
100% of cycles on all CPUs, effectively locking up the machine. This is
normal behavior under some circumstances, and is simply an indication that
AWE_WINDOW_MEMORY is too small.

Monitoring Mapping Operations in 9.2 and later releases:
========================================================

Starting with 9.2, we have added additional statistics which can be
measured in v$sesstat (sesssion-level stats) and v$sysstat (system-wide
stats):

STATISTIC# NAME
---------- ------------------------------
154 number of map operations
155 number of map misses

This query below will give you system-wide information on map
operations and map misses:

SQL> select * from v$sysstat where statistic# in (154, 155);

If the # of Map misses is relatively high, or particularly of the # of map
misses increases consistently over time, this may be an indication that the
value for AWE_WINDOW_MEMORY is set too low.

Dynamic Memory Management/Automatic Memory Management with AWE Enabled
=============================================================

Oracle10g introduces the concept of Automatic Memory Management,
whereby the Oracle RDBMS will dynamically adjust SGA parameters
such as SHARED_POOL_SIZE, JAVA_POOL_SIZE, DB_CACHE_SIZE, etc.

This is enabled by setting the parameter SGA_TARGET to a non-zero value.
However, in order for this to work properly, you must use DB_CACHE_SIZE
for the buffer cache. When setting USE_INDIRECT_DATA_BUFFERS, you cannot
set DB_CACHE_SIZE, as noted above. Therefore, SGA_TARGET should not be set
when using AWE - these two features are mutally exclusive.
When setting USE_INDIRECT_DATA_BUFFERS=TRUE on Oracle10g, you should also
set SGA_TARGET to 0.

Diagnosing Spins Associated With AWE in 8.1.x:
==============================================

The above stats are not available in 8.1.7, so if you are encountering
problems with CPU spins, with AWE_WINDOW_MEMORY enabled, it is more
difficult to diagnose.

You can start by identifying and monitoring the thread associated with
DBWR via the following query:

SQL> select b.name, p.spid from v$process p, v$bgprocess b
where p.addr=b.paddr;

NAME SPID
----- ---------
PMON 1900
DBW0 1956
LGWR 572
CKPT 1908
SMON 1808
RECO 920
SNP0 1784
SNP1 1892
SNP2 1896
SNP3 1844

10 rows selected.

As you can see, DBWR has an SPID of 1956, which will equate to the
Thread ID of that thread within the Oracle executable. This thread can
then be monitored using Performance Monitor and/or the PSLIST utility,
which is available as a free download from http://www.sysinternals.com

If your monitoring shows that DBWR is consuming excessive CPU, you can
attempt to get an errorstack from that thread using oradebug:

SQL> oradebug setospid 1956
Oracle pid: 3, Windows thread id: 1956, image: ORACLE.EXE
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump errorstack 3
Statement processed.

This should dump the errorstack to the DBWR trace file, found in BDUMP.
If the errorstack contains the function SKGMMAP, this is an indication
that DBWR is working to map/unmap database block buffers.

Note: In 8.1.7 of the RDBMS, you cannot use DBWR_IO_SLAVES in combination with
USE_INDIRECT_DATA_BUFFERS, due to BUG#3042660/BUG#2215894. You must leave
DBWR_IO_SLAVES at its default value - otherwise, buffers are not unmapped
and eventually a spin of the process will result.
This problem is resolved in 9.2.0.1 - the fix is NOT backported to 8.1.7

KNOWN ISSUES
--------------------
BUG#2461474 - SHOW SGA DOES NOT SHOW CORRECT # OF DB BUFFERS WITH AWE
BUG#1412485 - LONG SHUTDOWN TIME WITH AWE_WINDOW_MEMORY: FIXED IN 8.1.7.1
BUG#1406194 - AWE_WINDOW_MEMORY NOT RELEASED WHEN DB SHUTDOWN: FIXED IN 8.1.7.1
BUG#2520796 - ORA-439 TRYING TO ENABLE VLM IN STANDARD EDITION OF ORACLE - FIXED IN 9.2.0.4
BUG#2945011 - VLM DOES NOT WORK ON STANDARD EDITION ORACLE 9.2.0.2 ON WINDOWS - FIXED IN 9.2.0.4
BUG#3120033 - ORA-600[KCBVMAP] may occur with AWE, or DBWR may crash with ORA-471 on 9.2.0.4 - FIXED 9.2.0.4 PATCH 2
BUG#3042660 / BUG#2215894 - IO SLAVES DON'T UNMAP BUFFERS ON LINUX IN VLM MODE (APPLIES TO WINDOWS AS WELL)

CONFIGURING AUTOMATIC SHARED MEMORY MANAGEMENT

2007-06-29T15:48:00.000+05:30

AUTOMATIC SHARED MEMORY MANAGEMENT

In Oracle 10g, the memory components are collectively known as the System Global area. Some of the memory components also referred to as pools need to have their size configured properly. The main pools are the shared pool, the large pool, the database cache size and the Java Pool. The appropriate individual sizes need to configured based on the overall SGA size. This can be quite a challenge by itself. Under-sizing may lead to poor performance and out-of-memory errors, while over-sizing may result in wasting of memory.

In Oracle 10g, all you need to specify is the total amount of memory to be used by all SGA components. The database uses this value to redistribute memory between the various components based on the current workload of the database.

•The new initialization parameter (SGA_TARGET) can include all the memory in the SGA, manually and automatically seized components and any internal allocations during startup.

•The initialization parameters used in Oracle 9i and earlier namely DB_CACHE_SIZE, LARGE_POOL_SIZE, SHARED_POOL_SIZE and JAVA_POOL_SIZE are now considered auto-tuned SGA parameters. When the SGA_TARGET is set, the total for the manual SGA size parameters is subtracted from the SGA_TARGET to obtain the amount of memory that can be allocated to auto-tuned memory components.

•The Automatic Shared Memory Management feature uses a background process called Memory Manager (MMAN). This background process is responsible for coordinating the sizes of the various memory components dynamically based on the current workload. This process checks the current workload on the database every few minutes, to ensure that memory is always present wherever needed.

•The basic method used by automatic shared memory is given below:
•Statistics are gathered periodically
•Different memory advisories are used to make recommendations
•"What If" analysis is performed to determine best distribution of memory.
•Memory is moved to wherever it is required.

GUIDELINES

•Automatic Shared memory management is configured by setting the SGA_TARGET initialization parameter. If the parameter is set to the default value of 0, then automatic shared memory management is disabled. If set to a non-zero value then Oracle automatically sizes of the shared pool, the large pool, the java pool and the Database buffer cache accordingly.

•Within the SGA the buffers whose sizes can be configured manually are the Log Buffer (LOG_BUFFER), Keep and Recycle Pools (DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE), A streams pool (STREAMS_POOL_SIZE) which is new Oracle 10g , fixed SGA and other internal allocations and caches to accommodate non-standard tablespace blocks (DB_nk_CACHE_SIZE, n=2,4,8,16,32).

•The initialization parameter STATISTICS_LEVEL must be set to TYPICAL or ALL.

•You can query the values of the auto-tuned parameters from the V$PARAMETER dynamic performance view. If you set a value for the SGA_TARGET and do not specify sizes for the auto-tuned size parameters, the value that is displayed by the V$PARAMETER view against these parameters is 0.

•The SGA_TARGET should be less than the SGA_MAX_SIZE initialization parameter. It can be reduced upto a minimum value defined for an individual auto-tuned component. Any change you make to the SGA_TARGET only affects the auto-tuned initialization parameters. You can dynamically increase the SGA_TARGET to the absolute maximum specified by adjusting the value of SGA_MAX_SIZE.

•You can disable automatic shared memory tuning by setting the SGA_TARGET to value of 0. If this is done, the values taken by the individual auto-tuned memory components freeze to their current sizes. They do not take any defaults or previous values.

•When an auto-tuned parameter is resized, the resize results in a change to the size of the component only if the new value is larger than the present size of the component. If you change JAVA_POOL_SIZE to 2G. This change will take effect immediately ensuring there is always 2G for this parameter, however if you reduce to 1G, where the current size is 1.5G. The change may not occur immediately, and the memory tuning algorithm can reduce to this value if it needs to.

CONFIGURING AUTOMATIC SHARED MEMORY MANAGEMENT.

You can configure Automatic Shared Memory Management either:
•Manually
•Using Enterprise Manager Console
•The Enterprise Manager can be used to configure Automatic Shared Memory Management. To do so, select the Database -> Administration Tab -> Memory Parameters -> SGA Tab -> Enable button. Specify the total SGA size in Megabytes.

•The ALTER SYSTEM command may be used to configure Automatic Shared Memory Management manually.

SQL> ALTER SYSTEM SET SGA_TARGET = 500M SCOPE=spfile;

Also modify the initialization parameter listed below to zero, or completely remove them from the parameter file.

SHARED_POOL_SIZE=0
LARGE_POOL_SIZE=0
JAVA_POOL_SIZE=0
DB_CACHE_SIZE=0
DATA DICTIONARY

Once configured you can check the sizes with the following:

SQL> SELECT CURRENT_SIZE FROM V$BUFFER_POOL;

Display output

SQL> SELECT POOL, SUM(BYTES)/1024/1024 Mbytes FROM V$SGASTAT GROUP BY POOL;

The total sum of values of the above two queries will be the same as the value you set for SGA_TARGET.

Oracle DBA Checklist

2007-06-27T17:15:00.000+05:30

Oracle DBA Checklist

I. Daily Procedures

A. Verify all instances are up

Make sure the database is available. Log into each instance and run daily reports or test scripts. Some sites may wish to automate this.
Optional implementation: use Oracle Enterprise Manager's 'probe' event.

B. Look for any new alert log entries

. Connect to each managed system.
. Use 'telnet' or comparable program.
. For each managed instance, go to the background dump destination, usually $ORACLE_BASE/ /bdump. Make sure to look under each managed database's SID.
. At the prompt, use the Unix 'tail' command to see the alert_ .log, or otherwise examine the most recent entries in the file.
. If any ORA-errors have appeared since the previous time you looked, note them in the Database Recovery Log and investigate each one. The recovery log is in .

C. Verify DBSNMP is running

1. Log on to each managed machine to check for the 'dbsnmp' process. For Unix: at the command line, type ps -ef | grep dbsnmp. There should be two dbsnmp processes running. If not, restart DBSNMP. (Some sites have this disabled on purpose; if this is the case, remove this item from your list, or change it to "verify that DBSNMP is NOT running".)

D. Verify success of database backup

E. Verify success of database archiving to tape

F. Verify enough resources for acceptable performance
1. Verify free space in tablespaces.
For each instance, verify that enough free space exists in each tablespace to handle the day's expected growth. As of , the minimum free space for : [ < tablespace > is < amount > ]. When incoming data is stable, and average daily growth can be calculated, then the minimum free space should be at least days' data growth.
a) Go to each instance, run free.sql to check free mb in tablespaces. Compare to the minimum free MB for that tablespace. Note any low-space conditions and correct.
b) Go to each instance, run space.sql to check percentage free in tablespaces. Compare to the minimum percent free for that tablespace. Note any low-space conditions and correct.

2. Verify rollback segment.

Status should be ONLINE, not OFFLINE or FULL, except in some cases you may have a special rollback segment for large batch jobs whose normal status is OFFLINE.
a) Optional: each database may have a list of rollback segment names and their expected statuses.
b) For current status of each ONLINE or FULL rollback segment (by ID not by name), query on V$ROLLSTAT.
c) For storage parameters and names of ALL rollback segment, query on DBA_ROLLBACK_SEGS. That view's STATUS field is less accurate than V$ROLLSTAT, however, as it lacks the PENDING OFFLINE and FULL statuses, showing these as OFFLINE and ONLINE respectively.

3. Identify bad growth projections.

Look for segments in the database that are running out of resources (e.g. extents) or growing at an excessive rate. The storage parameters of these segments may need to be adjusted. For example, if any object reached 200 as the number of current extents, AND it's an object that is supposed to get large, upgrade the max_extents to unlimited.
a) To gather daily sizing information, run analyze5pct.sql. If you are collecting nightly volumetrics, skip this step.
b) To check current extents, run nr_extents.sql
c) Query current table sizing information
d) Query current index sizing information
e) Query growth trends

4. Identify space-bound objects.
Space-bound objects' next_extents are bigger than the largest extent that the tablespace can offer. Space-bound objects can harm database operation. If we get such object, first need to investigate the situation. Then we can use ALTER TABLESPACE COALESCE. Or add another datafile.
a) Run spacebound.sql. If all is well, zero rows will be returned.

5. Processes to review contention for CPU, memory, network or disk resources.
a) To check CPU utilization, go to x:\web\phase2\default.htm => system metrics=>CPU utilization page. 400 is the maximum CPU utilization because there are 4 CPUs on phxdev and phxprd machine. We need to investigate if CPU utilization keeps above 350 for a while.

G. Copy Archived Logs to Standby Database and Roll Forward
If you have a Standby Database, copy the appropriate Archived Logs to the expected location on the standby machine and apply those logs (roll forward the changes) to the standby database. This keeps the standby database up-to-date.
The copying of logs, the applying of them, or both, can in some cases be automated. If you have automated them, then your daily task should be to confirm that this happened correctly each day.

H. Read DBA manuals for one hour
Nothing is more valuable in the long run than that the DBA be as widely experienced, and as widely read, as possible. Readings should include DBA manuals, trade journals, and possibly newsgroups or mailing lists

Recreate the Database Control Repository

2007-06-27T16:55:00.000+05:30

How to Recreate the Database Control Repository - (Oracle10g R1)

http://www.idevelopment.info/data/Oracle/DBA_tips/Enterprise_Manager/OEM_3.shtml

Installation of Oracle on Linux Links

2007-06-27T16:46:00.000+05:30

http://www.oracle-base.com/articles/linux/OracleEnterpriseLinux4Installation.php
http://www.oracle-base.com/articles/linux/RHEL4Installation.php
http://www.oracle.com/technology/obe/obe10gdb/install/linuxinst/linuxinst.htm
http://www.puschitz.com/OracleOnLinux.shtml

Oracle Database 11g Overview Presentation

2007-06-27T16:21:00.000+05:30

Oracle Database 11g Overview Presentation

The following is a presentation by Mark Townsend, the vice president of database product management, titled The Future of Database Technology. Towards the middle of the presentation, there is a high level overview of many Oracle database 11g features like SQL replay, online table and index redefinition, automatic diagnostic workflow, support for online hot patching and other highlights.

Here is the full document:

http://awads.net/wp/2007/06/25/oracle-database-11g-overview-presentation/

STATSPACK

2007-06-12T14:20:00.000+05:30

STATSPACK

Below are the steps for generating a statspack Report:

1. User must have SYSDBA privs

set ORACLE_SID=orcl
sqlplus sys/pwd as sysdba

2.You should create SEPARATE TABLESPACE for statspack report.

create tablespace PERFSTAT
datafile 'c:\perf01.dbf' size 100m
autoextend on
segment space management auto;

We should generate STATSPACK report DURING PEAK TIME
run SPCREATE.SQL script
Located : $ORACLE_HOME\rdbms\admin\spcreate.sql

conn sys as sysdba
@$ORACLE_HOME\rdbms\admin\spcreate.sql

4.connect with PERFSTAT user
perfstat user create automatically when we run SPCREATE.SQL script

conn perfstat/password

5.run STATSPACK.SNAP procedure

exec statspack.snap;

Note: After 15 minutes run again STATSPACK.SNAP procedure

6.run SPREPORT for generate statspack report in text format.

@$ORACLE_HOME\rdbms\admin\spreport.sql

7.connect with SYSDBA privs user ( sys user)
and execute SPDROP.SQL script for drop PERFSTAT user and all related tables

conn sys as sysdba
@$ORACLE_HOME\rdbms\admin\spdrop.sql

8.DROP TABLESPACE use for STATSPACK REPORT

conn sys as sysdba
drop tablespace [TABLESPACE NAME]
including contents and datafiles

Note: analyze your report

http://www.statspackanalyzer.com/analyze.asp
http://www.oraperf.com

Script to Schedule a Full Database Export backup

2007-06-12T11:48:00.000+05:30

Script to Schedule a Export backup daily three times at 6:00AM,2:30PM,10:30PM.

SCRIPT:

schtasks /create /sc hourly /mo 8 /sd 06/07/2007 /ST 22:30:00 /tn MyDailyBackup /tr C:\sree_backup\SREE_BACKUP.bat /RU sree\kasukurthi.sridhar /RP sreetest@9

Where

/sc = Schedule Creation
/mo = Modification time
/sd = schedule date
/st = schedule time
/tn = transaction name
/tr = transaction to run( Batch file along with path )
/ru = remote user (if not specified it will takke local user name)
/ru = remote password ( if not specified it will prompt at the run time )

To delete above task issue below command at OS prompt

SCHTASKS /Delete /TN "MyDailyBackup" /f

Batch file contents:

@echo off
move old_sree.dmp "C:\sree_backup\move_old\"
move old_sree.log "C:\sree_backup\move_old\"
rename sree.dmp old_sree.dmp
rename sree.log old_sree.log
exp userid=edr/sree@edr file=c:\sree_backup\sree.dmp log=c:\sree_backup\sree.log full=y buffer=2000000

ORA-12528: TNS:listener: all appropriate instances are blocking new connections

2007-06-12T11:33:00.000+05:30

ORA-12528: TNS:listener: all appropriate instances are blocking new connections

It is known issue with ORACLE 10G.

Suggestion: Don't Use "@(tns_entry string)" with userid/pwd.
just export or set ORACLE_SID and connect WITHOUT "@"tns_entry.

SQL> grant SYSDBA to system;

Grant succeeded.

SQL> conn system/oracle@oracle as sysdba
Connected.

SQL> STARTUP FORCE
ORA-12528: TNS:listener: all appropriate instances are blocking new connections

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options

C:\>sqlplus sys as sysdba

SQL*Plus: Release 10.1.0.2.0 - Production on Mon Feb 19 17:13:20 2007

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

Enter password:

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

SQL> select status from v$instance;

STATUS
------------
STARTED

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

SQL> conn system as sysdba
Enter password:
Connected.
SQL> SHOW PARAMETER DB_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string ORACLE
SQL> STARTUP FORCE
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.
SQL>

Moving, Rebuilding and or Resizing Indexes

2007-04-22T18:37:00.000+05:30

As this procedure deals only with indexes, I consider it a very low risk process. The main source of risk if any is if your target tablespace for the index is either low on available space or is very fragmented. Just because the index was removed from a tablespace does not mean it can be recreated in the same tablespace. Unless there is sufficient free space and or contiguous extents in the target tablespace the index may not recreate.

Why Rebuild ?
The reasons for moving and or resizing indexes are relatively self explanatory, however, it is worth explaining why you would want to regularly rebuild some indexes.
When records are added to a table, the table and index grows by the number of records added. When records are deleted from a table the records are physically deleted from the table but are logically deleted from the index.
Table Index
Task count count
------------------------------------
Add 100 Records 100 100
Delete 100 Records 0 100
Add 100 Records 100 200
delete 100 Records 0 200
As you can see from the above example, the index will continue to grow, where as the table may remain static.

How to identify indexes with "excessive" logically deleted rows.
The first place to look is at any dynamic table I.E. Interface and temporary tables.
Once the target tables have been identified, obtain a list of index names by using the following example SQL*PLUS script.
SELECT index_name
FROM dba_indexes
WHERE owner = 'SCOTT'
and table_name = 'EMP'
/
For each of the indexes identified, execute the validate index command followed by the SQL*PLUS script shown below to obtain the results of the validate index command. Note: The index_stats table holds only one row.
SQL> validate index gl.gl_interface_n1
/

Index analyzed.

SQL>
Use the following SQL*PLUS script to obtain the results.
column A heading "Index Name" format a30
column B heading "Rows" format 999,999,990
column C heading "Deleted|Rows" format 999,999,990
column D heading "% Del" format 990.0

SELECT name A,
lf_rows B,
del_lf_rows C,
(del_lf_rows * 100 ) / lf_rows D
FROM index_stats
/
I usually rebuild indexes that have more than 20 % logically deleted rows.

Rebuilding an index.
The following PL*SQL script will build an SQL*PLUS script to drop and then recreate the index.
Note: The storage clause and the index owner's password in the output file will have to be modified before you execute the resulting script. Modifying the storage clause will allow you to resize and or move the index.

clear columns

set serveroutput on;
set echo off
set heading off
set feedback off
set verify off

WHENEVER SQLERROR CONTINUE

/**************************************/
/** OBTAIN COMMAND LINE PARAMETERS **/
/** OWNER = Index owner **/
/** INDEX_NAME = Index name **/
/**************************************/

DEFINE owner = '&1'
DEFINE index_name = '&2'

DECLARE
/**************************************/
/** CURSOR C1 Get the index columns **/
/**************************************/
CURSOR C1 is
SELECT column_name
FROM dba_ind_columns
WHERE index_owner = upper('&&owner')
and index_name = upper('&&index_name')
ORDER by column_position;

/**************************************/
/** Decalre variables **/
/**************************************/
d_column_name VARCHAR(30);
d_table_name VARCHAR(30);
d_uniqe VARCHAR(9);
d_max_columns NUMBER(4) := 0;
d_column_counter NUMBER(4) := 0;

BEGIN

dbms_output.put_line('/****************************************/' );
dbms_output.put_line('/* Drop and create index script */' );
dbms_output.put_line('/* */' );
dbms_output.put_line('/* Dont forget to edit this file for: */' );
dbms_output.put_line('/* */' );
dbms_output.put_line('/* 1. Connect password */' );
dbms_output.put_line('/* 2. Storage params */' );
dbms_output.put_line('/* */' );
dbms_output.put_line('/****************************************/' );
dbms_output.put_line('');
dbms_output.put_line('');

dbms_output.put_line('connect &&owner/password' );
dbms_output.put_line('');
dbms_output.put_line('');

dbms_output.put_line('DROP INDEX &&index_name;' );
dbms_output.put_line('');
dbms_output.put_line('');

/**************************************/
/** Determine the number of columns **/
/** in the index **/
/**************************************/

SELECT max(column_position)
INTO d_max_columns
FROM dba_ind_columns
WHERE index_owner = upper('&&owner')
and index_name = upper('&&index_name');

SELECT table_name, uniqueness
INTO d_table_name, d_uniqe
FROM dba_indexes
WHERE owner = upper('&&owner')
and index_name = upper('&&index_name');

/**************************************/
/** Determine if the index is uniqe **/
/**************************************/
SELECT uniqueness
INTO d_uniqe
FROM dba_indexes
WHERE owner = upper('&&owner')
and index_name = upper('&&index_name');

dbms_output.put_line('CREATE '|| d_uniqe ||' INDEX &&index_name' );
dbms_output.put_line('ON '||d_table_name );
dbms_output.put_line('(');

OPEN C1;
LOOP
FETCH C1 INTO d_column_name;
d_column_counter := d_column_counter + 1;
EXIT WHEN C1%NOTFOUND;
IF d_column_counter < d_max_columns THEN
dbms_output.put_line( d_column_name||',' );
ELSE
dbms_output.put_line( d_column_name||' )' );
END IF;
END LOOP;

dbms_output.put_line('STORAGE ( initial X M next X M');
dbms_output.put_line(' minextents 1 maxextents 50');
dbms_output.put_line(' pctincrease 0 );');

END;
/

NOTE 1: If the index is being used by another user, you will not be able to either validate or drop the index.
NOTE 2: Beware of the primary key constraint.
General details about the index can also be found by:
Analyze index compute statistics;
Select * from user_indexes
where index_name= ‘’;
To obtain further detail about an index:
Analyze index validate structure;
The command:
Validate index ;
Performs the same function.
This places detailed information about the index in the table INDEX_STATS. This table can only contain one row, describing only the one index. This SQL also verifies the integrity of each data block in the index and checks for block corruption.
For example, to get the size of an index:
validate index ;
select name "INDEX NAME", blocks * 8192 "BYTES ALLOCATED",
btree_space "BYTES USED",
(btree_space / (blocks * 8192))*100 "PERCENT USED"
from index_stats;
This assumes a block size of 8K (i.e. 8192 bytes). It shows the number of bytes allocated to the index and the number of bytes actually used.
Note that it does not confirm that each row in the table has an index entry or that each index entry points to a row in the table. To check this:
Analyze table validate structure cascade;