Install Oracle 10g on RedHat Linux

Step by step Install oracle on linux RedHat :

———————————————————

Create the oracle user and main directory for oracle binary as folowing:

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

mkdir -p /home/oracle/app/oracle
chown -R oracle:dba /home/oracle/app/oracle
chmod -R 775 /home/oracle/app/oracle

Modify the folowing OS kernel parameter :

——————————————————

cat >> /etc/sysctl.conf <<EOF
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
EOF

/sbin/sysctl -p

cat >> /etc/security/limits.conf <<EOF
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
EOF

cat >> /etc/pam.d/login <<EOF
session required /lib/security/pam_limits.so
EOF

cat >> /etc/profile <<EOF
if [ \$USER = "oracle" ]; then
if [ \$SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
umask 022
fi
EOF

cat >> /etc/csh.login <<EOF
if ( \$USER == “oracle” ) then
limit maxproc 16384
limit descriptors 65536
umask 022
endif
EOF

Exec the runInstaller.sh to install the oracle software .

Oracle alert monitoring

The following scripts is used for alert monitoring in oracle database which is file location is on $ORACLE_BASE/admin/$DATABASE_NAME

CREATE TABLE SYS.ALERT_LOG
(
ALERT_DATE  DATE,
ALERT_TEXT  VARCHAR2(1200 BYTE)
)
TABLESPACE SYSTEM
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
INITIAL          512K
MINEXTENTS       1
MAXEXTENTS       UNLIMITED
PCTINCREASE      0
FREELISTS        1
FREELIST GROUPS  1
BUFFER_POOL      DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

–
– ALERT_LOG_IDX  (Index)
–
CREATE INDEX SYS.ALERT_LOG_IDX ON SYS.ALERT_LOG
(ALERT_DATE)
LOGGING
TABLESPACE SYSTEM
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
INITIAL          512K
MINEXTENTS       1
MAXEXTENTS       UNLIMITED
PCTINCREASE      0
FREELISTS        1
FREELIST GROUPS  1
BUFFER_POOL      DEFAULT
)
NOPARALLEL;

DROP TABLE SYS.ALERT_LOG_DISK CASCADE CONSTRAINTS;

–
– ALERT_LOG_DISK  (Table)
–
CREATE TABLE SYS.ALERT_LOG_DISK
(
TEXT  VARCHAR2(2000 BYTE)
)
ORGANIZATION EXTERNAL
(  TYPE ORACLE_LOADER
DEFAULT DIRECTORY BDUMP
ACCESS PARAMETERS
( records delimited by newline nologfile nobadfile
fields terminated by “&” ltrim
)
LOCATION (BDUMP:’alert_orcl.log’)
)
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;

set serveroutput on

declare

isdate         number := 0;
start_updating number := 0;
rows_inserted  number := 0;

alert_date     date;
max_date       date;

alert_text     alert_log_disk.text%type;

begin

/* find a starting date */
select max(alert_date) into max_date from alert_log;

if (max_date is null) then
max_date := to_date(‘01-jun-2008′, ‘dd-mon-yyyy’);
end if;

for r in (
select substr(text,1,180) text from alert_log_disk
where text not like ‘%offlining%’
and text not like ‘ARC_:%’
and text not like ‘%LOG_ARCHIVE_DEST_1%’
and text not like ‘%Thread 1 advanced to log sequence%’
and text not like ‘%Current log#%seq#%mem#%’
and text not like ‘%Undo Segment%lined%’
and text not like ‘%alter tablespace%back%’
and text not like ‘%Log actively being archived by another process%’
and text not like ‘%alter database backup controlfile to trace%’
and text not like ‘%Created Undo Segment%’
and text not like ‘%started with pid%’
and text not like ‘%ORA-12012%’
and text not like ‘%ORA-06512%’
and text not like ‘%ORA-000060:%’
and text not like ‘%coalesce%’
and text not like ‘%Beginning log switch checkpoint up to RBA%’
and text not like ‘%Completed checkpoint up to RBA%’
and text not like ‘%specifies an obsolete parameter%’
and text not like ‘%BEGIN BACKUP%’
and text not like ‘%END BACKUP%’
)
loop

isdate     := 0;
alert_text := null;

select count(*) into isdate
from dual
where substr(r.text, 21) in (‘2003′,’2004′,’2005′,’2006′,’2007′,’2008′)
and r.text not like ‘%cycle_run_year%’;

if (isdate = 1) then

select to_date(substr(r.text, 5),’Mon dd hh24:mi:ss rrrr’)
into alert_date
from dual;

if (alert_date > max_date) then
start_updating := 1;
end if;

else
alert_text := r.text;
end if;

if (alert_text is not null) and (start_updating = 1) then

insert into alert_log values (alert_date, substr(alert_text, 1, 180));
rows_inserted := rows_inserted + 1;
commit;

end if;

end loop;

sys.dbms_output.put_line(‘Inserting after date ‘||to_char(max_date, ‘MM/DD/RR HH24:MI:SS’));
sys.dbms_output.put_line(‘Rows Inserted: ‘||rows_inserted);

commit;

end;
/

Check the blocking process

These script is used to check whether any blocking proses in query process :

SELECT /*+ CHOOSE */
bs.username “Blocking User”,
bs.username “DB User”,
ws.username “Waiting User”,
bs.sid “SID”,
ws.sid “WSID”,
bs.serial# “Serial#”,
bs.sql_address “address”,
bs.sql_hash_value “Sql hash”,
bs.program “Blocking App”,
ws.program “Waiting App”,
bs.machine “Blocking Machine”,
ws.machine “Waiting Machine”,
bs.osuser “Blocking OS User”,
ws.osuser “Waiting OS User”,
bs.serial# “Serial#”,
ws.serial# “WSerial#”,
DECODE(wk.TYPE,
‘MR’, ‘Media Recovery’,         ’RT’, ‘Redo Thread’,        ’UN’, ‘USER Name’,
‘TX’, ‘Transaction’,     ’TM’, ‘DML’,    ’UL’, ‘PL/SQL USER LOCK’,
‘DX’, ‘Distributed Xaction’, ‘CF’, ‘Control FILE’,    ’IS’, ‘Instance State’,
‘FS’, ‘FILE SET’, ‘IR’, ‘Instance Recovery’,    ’ST’, ‘Disk SPACE Transaction’,
‘TS’, ‘Temp Segment’, ‘IV’, ‘Library Cache Invalidation’,
‘LS’, ‘LOG START OR Switch’,    ’RW’, ‘ROW Wait’,'SQ’, ‘Sequence Number’,
‘TE’, ‘Extend TABLE’, ‘TT’, ‘Temp TABLE’,    wk.TYPE) lock_type,
DECODE(hk.lmode, 0, ‘None’,    1, ‘NULL’,     2, ‘ROW-S (SS)’,     3, ‘ROW-X (SX)’,
4, ‘SHARE’,    5, ‘S/ROW-X (SSX)’, 6, ‘EXCLUSIVE’, TO_CHAR(hk.lmode)) mode_held,
DECODE(wk.request,    0, ‘None’,    1, ‘NULL’,     2, ‘ROW-S (SS)’,  3, ‘ROW-X (SX)’,
4, ‘SHARE’,    5, ‘S/ROW-X (SSX)’,    6, ‘EXCLUSIVE’,     TO_CHAR(wk.request)) mode_requested,
TO_CHAR(hk.id1) lock_id1,
TO_CHAR(hk.id2) lock_id2,
DECODE(hk.block,  0, ‘NOT Blocking’,  /* Not blocking any other processes */
1, ‘Blocking’,      /* This lock blocks other processes */
2, ‘Global’,        /* This lock is global, so we can’t tell */
TO_CHAR(hk.block)) blocking_others
FROM
v$lock hk,  v$session bs,
v$lock wk,  v$session ws
WHERE
hk.block   = 1
AND  hk.lmode  != 0
AND  hk.lmode  != 1
AND  wk.request  != 0
AND  wk.TYPE (+) = hk.TYPE
AND  wk.id1  (+) = hk.id1
AND  wk.id2  (+) = hk.id2
AND  hk.sid    = bs.sid(+)
AND  wk.sid    = ws.sid(+)
and (bs.username is not null) and (bs.username<>’SYSTEM’)  and (bs.username<>’SYS’)
ORDER BY 1

Manual Oracle Uninstall

The two methods listed below should only be used as a last resort and will remove all Oracle software allowing a reinstall. If you make any mistakes they can be quite destructive so be careful.

• Windows
• UNIX

Windows

In the past I’ve had many problems uninstalling all Oracle products from Windows systems. Here’s my last resort method:

• Uninstall all Oracle components using the Oracle Universal Installer (OUI).
• Run regedit.exe and delete the HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE key. This contains registry entires for all Oracle products.
• Delete any references to Oracle services left behind in the following part of the registry:
  HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/Ora*
  It should be pretty obvious which ones relate to Oracle.
• Reboot your machine.
• Delete the C:\Oracle directory, or whatever directory is your ORACLE_BASE.
• Delete the C:\Program Files\Oracle directory.
• Empty the contents of your c:\temp directory.
• Empty your recycle bin.

At this point your machine will be as clean of Oracle components as it can be without a complete OS reinstall.

Remember, manually editing your registry can be very destructive and force an OS reinstall so only do it as a last resort.

UNIX

Uninstalling all products from UNIX is a lot more consistent. If you do need to resort to a manual uninstall you should do something like:

• Uninstall all Oracle components using the Oracle Universal Installer (OUI).
• Stop any outstanding processes using the appropriate utilities:

·                # oemctl stop oms user/password
·                # agentctl stop
# lsnrctl stop

Alternatively you can kill them using the kill -9 pid command as the root user.

• Delete the files and directories below the $ORACLE_HOME:

·                # cd $ORACLE_HOME
# rm -Rf *

• With the exception of the product directory, delete directories below the $ORACLE_BASE.

·                # cd $ORACLE_BASE
# rm -Rf admin doc jre o*

• Delete the /etc/oratab file. If using 9iAS delete the /etc/emtab file also.

# rm /etc/oratab /etc/emtab

Get an oracle error info

Here folowing script to get oracle error info :

———————————————————-

CREATE OR REPLACE PACKAGE oracle_error_info
IS
FUNCTION is_app_error (code_in IN INTEGER)
RETURN BOOLEAN;

FUNCTION is_valid_oracle_error (
code_in            IN   INTEGER
, app_errors_ok_in   IN   BOOLEAN DEFAULT TRUE
, user_error_ok_in   IN   BOOLEAN DEFAULT TRUE
)
RETURN BOOLEAN;

PROCEDURE validate_oracle_error (
code_in            IN       INTEGER
, message_out        OUT      VARCHAR2
, is_valid_out       OUT      BOOLEAN
, app_errors_ok_in   IN       BOOLEAN DEFAULT TRUE
, user_error_ok_in   IN       BOOLEAN DEFAULT TRUE
);
END oracle_error_info;
/

CREATE OR REPLACE PACKAGE BODY oracle_error_info
IS
FUNCTION is_app_error (code_in IN INTEGER)
RETURN BOOLEAN
IS
BEGIN
RETURN code_in BETWEEN -20999 AND -20000;
END is_app_error;

PROCEDURE validate_oracle_error (
code_in            IN       INTEGER
, message_out        OUT      VARCHAR2
, is_valid_out       OUT      BOOLEAN
, app_errors_ok_in   IN       BOOLEAN DEFAULT TRUE
, user_error_ok_in   IN       BOOLEAN DEFAULT TRUE
)
IS
l_message   VARCHAR2 (32767);

PROCEDURE set_failure
IS
BEGIN
message_out := NULL;
is_valid_out := FALSE;
END set_failure;
BEGIN
l_message := SQLERRM (code_in);

– If SQLERRM does not find an entry, it return a string like one of these:
– If the number is negative…
– ORA-NNNNN: Message NNNN not found;  product=RDBMS; facility=ORA
– If the number is positive…
–  -13000: non-ORACLE exception
– If the positive number is too big, we get numeric overflow.
IF is_app_error (code_in) AND NOT app_errors_ok_in
THEN
set_failure;
ELSIF code_in = 1 AND NOT user_error_ok_in
THEN
set_failure;
ELSIF    l_message LIKE ‘ORA-_____: Message%not found;%’
OR l_message LIKE ‘%: non-ORACLE exception%’
THEN
set_failure;
ELSE
message_out := l_message;
is_valid_out := TRUE;
END IF;
EXCEPTION
WHEN OTHERS
THEN
–numeric overflow
IF SQLCODE = -1426
THEN
set_failure;
ELSE
RAISE;
END IF;
END validate_oracle_error;

FUNCTION is_valid_oracle_error (
code_in            IN   INTEGER
, app_errors_ok_in   IN   BOOLEAN DEFAULT TRUE
, user_error_ok_in   IN   BOOLEAN DEFAULT TRUE
)
RETURN BOOLEAN
IS
l_message   VARCHAR2 (32767);
retval      BOOLEAN;
BEGIN
validate_oracle_error (code_in
, l_message
, retval
, app_errors_ok_in
, user_error_ok_in
);
RETURN retval;
END is_valid_oracle_error;
END oracle_error_info;
/

Manually install Oracle9i JVM (9.0.1)

If you want to install the Oracle 9i Java Virtual Machine, you have to run the following Scripts as user SYS. Using the database configuration assistant, this scripts will be executed.

Requirements

Make sure, your INIT.ORA Paramaters SHARED_POOL_SIZE
and JAVA_POOL_SIZE are big enough. We have successfully installed the 9i JVM with the following settings on a Windows 2000 server.

shared_pool_size = 200000000
java_pool_size = 100000000

Script to Install 9i JVM

Note, that Oracle Home is: D:\Ora9i

sqlplus /nolog
connect sys/…. as sysdba;

– Setup a database for running Java and the ORB
@D:\Ora9i\javavm\install\initjvm.sql;

– INITialize (load) XML components in JServer
@D:\Ora9i\xdk\admin\initxml.sql;

– Loads NCOMP’ed XML Parser
@D:\Ora9i\xdk\admin\xmlja.sql;

– Loads the XMLSQL Utility (XSU) into the database.
@D:\Ora9i\rdbms\admin\catxsu.sql;

– Install the Oracle Servlet Engine (OSE)
@D:\Ora9i\javavm\install\init_jis.sql D:\Ora9i;

– Adds the set of default end points to the server
– with hardcoded values for the admin service
@D:\Ora9i\javavm\install\jisaephc.sql D:\Ora9i;

– Turn on J Accelerator
@D:\Ora9i\javavm\install\jisja.sql D:\Ora9i;

– Register EJB\Corba Dynamic Registration Endpoint
@D:\Ora9i\javavm\install\jisdr.sql 2481 2482;

– Init Java server pages ???
@D:\Ora9i\jsp\install\initjsp.sql;

– Turn on J Accelerator for JSP libs
@D:\Ora9i\jsp\install\jspja.sql;

– Script used to load AQ\JMS jar files into the database
@D:\Ora9i\rdbms\admin\initjms.sql;

– Load RepAPI server classes and publish ‘repapi’ obj
@D:\Ora9i\rdbms\admin\initrapi.sql;

– Loads sql, objects, extensibility and xml related java
@D:\Ora9i\rdbms\admin\initsoxx.sql;

– Loads appctxapi.jar for JavaVm enabled
– Database.Called by jcoreini.tsc
@D:\Ora9i\rdbms\admin\initapcx.sql;

– Script used to load CDC jar files into the database
@D:\Ora9i\rdbms\admin\initcdc.sql;

– Loads the Java stored procedures as required by the
– Summary Advisor.
@D:\Ora9i\rdbms\admin\initqsma.sql;

– Initialize sqlj type feature in 9i db
@D:\Ora9i\rdbms\admin\initsjty.sql;

– Load java componenets for AQ HTTP Propagation
@D:\Ora9i\rdbms\admin\initaqhp.sql;

Delete duplicate row

Folowing an example how to delete duplicate row in table :

DELETE from address A
WHERE (A.name, A.vorname, A.birth) IN
(SELECT B.name, B.vorname, B.birth FROM address B
WHERE A.name = B.name AND A.vorname = B.vorname
AND A.birth = B.birth AND A.rowid > B.rowid);