Access your Alert Log via SQL with External Tables

Starting in Oracle9i you can map external flat files to Oracle tables.
This is especially useful for reading the Oracle alert log and mapping a Excel spreadsheet making the Oracle alert log accessible via SQL from Oracle:
Mapping the Oracle alert log is easy and once defined, all you have to do is query it with standard SQL syntax:

create directory BDUMP as '/u01/app/oracle/admin/orcl/bdump';

create table
alert_log ( msg varchar2(80) )
organization external (
type oracle_loader
default directory BDUMP
access parameters (
records delimited by newline
)
location('alert_orcl.log')
)
reject limit 1000;

Now we can easily extract important Oracle alert log information without leaving SQL*Plus:
select msg from alert_log where msg like '%ORA-00600%';

ORA-00600: internal error code, arguments: [17034],[2940981512],[0], [],[],[],[],[]
ORA-00600: internal error code, arguments: [18095],[0xC0000000210D8BF8],[],[],[],[]
ORA-00600: internal error code, arguments: [4400], [48], [], [], []

ORA-00600: internal error code, arguments: [19004]

If you ever come across the error code "ORA-00600: internal error code, arguments: [19004], [], []...." while running query ralated to any table, view, index
here is the solution for it
login to your oracle database using pl/sql tool
run the below command on the command prompt
1. in case of error on querying table
ANALYZE TABLE table_name delete statistics;
2. in case of error on querying view
put the command above with related tables in the view. i.e run analyze tables for each table that form that view.

Ex:-

Create database with dbca (all default values):-
Created and imported two users in this database

And executed the below query

select d.mdl_code, substr(d.short_desc,1,22) short_desc,
d.version Prod, t.version UAT from (select * from PROD.module_versions) d,
(select * from UAT.module_versions) t where d.mdl_code = t.mdl_code(+)
and d.short_desc = t.short_desc(+)

Get the bellow error:-

ORA-00600: internal error code, arguments: [19004], [], [], [], [], [], [], []
Deleted statistics for the both tables in databaseas a super user:-
ANALYZE TABLE prod.module_versions delete statistics;
ANALYZE TABLE uat.module_versions delete statistics;
Now query is working fine.
____________________________________________________________________________________

ORA-00600[4193]/[4194]: -

Option 1:- Support Method(Drop the undo tablespace).

There is no need to use Unsupported parameter like _offline_rollback_segments and _corrupted_rollback_segments to resolve ora-00600[4193]/[4194]

Option 1 :- Supported Method
======================

Drop the undo tablespace.

This error normally happens for a new transaction. The trace file actually shows an active transaction for the undo segment because this is the transaction created by the same process. If the undo segment happens to have an active transaction , then Oracle may recover it later with no problems .

Normally if the header is dumped after the error, the active transactin is gone.

So a Simpler option to resolve this issue is.

Step 1
SQL> Startup nomount ; --> using spfile

SQL> Create pfile='/tmp/corrupt.ora' from spfile ;
SQL> Shutdown immediate;

Step 2
Modify the corrupt.ora and set Undo_managment=Manual
SQL> Startup mount pfile='/tmp/corrupt.ora'
SQL> Show parameter undo
it should show manual
SQL> Alter database open ;
If it comes up
SQL> Create rollback segment r01 ;
SQL> Alter rollback segment r01 online ;
Create a new undo tablespace
SQL> Create undo tablespace undotbs_new datafile '<>' size <> M ;

Drop the Old undo tablespace

SQL> Drop tablespace including contents and datafiles

Step 3
SQL> Shutdown immediate;
SQL> Startup nomount ; ---> Using spfile
SQL>Alter system set undo_tablespace= scope=spfile;
SQL> Shutdown immediate ;
SQL> Startup

For 8i database and Below

SQL>Startup restrict
Drop the Manual rollback segments and recreate it
@Option 2 (Drop the Rollback segment)
@From the ora-00600[4194] trace file identify the undo segment
@For example
@ORA-00600: internal error code, arguments: [4194], [19], [33], [], [], [],
@In the above example
@ARGUMENTS:
@a----> 19
@Search for UNDO BLK in the trace file
@********************************************************************************
@cnt--->0x13 --> Decimal --> 19
@Option 3(System undo segment erroring with Ora-00600[4194/4193]
@Option 1 would fail if the undo segment involved is System undo segment.
@Please refer the note given below for patching the same.
____________________________________________________________________________________
ORA-00600 arguments: [keltnfy-ldmInit], [46]

Problem Description
In oracle 10.2.0.1 while creating database with dbca it fails with message below.
ORA-00600: internal error code, arguments: [keltnfy-ldmInit], [46], [1], [], [], [], [], [] If you try to create your database manually then also the command startup nomount fails with above error message.

Cause of the Problem
In the error ldmInit indicates that the problem is related while getting host information of oracle during startup. The first argument 46 indicates the exception LDMERR_HOST_NOT_FOUND which is "gethostbyname system call fails". Oracle was unable to get host information from OS and bug fires.

Solution of the Problem
Step 01: Check permission on /etc/hosts

$ ls -l /etc/hosts
-rw-r--r-- 1 root root 153 Nov 24 2007 /etc/hosts
Note that you need read permission of all users.

Step 02: Check the contents of /etc/hosts
Open the contents of /etc/hosts and check the contents inside it.
$ less /etc/hosts
Note that the contents of this files follow following format.

IP Address fully qualified hostname simple or short hostname Alias

A simple example,
$ cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 database localhost.localdomain localhost

Step 03: Check the hostname and make sure you can ping your hostname.
$ hostname
database

$ ping database
PING database (127.0.0.1) 56(84) bytes of data.
64 bytes from database (127.0.0.1): icmp_seq=0 ttl=64 time=0.057 ms
64 bytes from database (127.0.0.1): icmp_seq=1 ttl=64 time=0.050 ms
64 bytes from database (127.0.0.1): icmp_seq=2 ttl=64 time=0.041 ms

If you get the following message,
$ ping database
ping: unknown host database

then possibly you will hit above bug.
And you need to modify /etc/hosts files. In the alias section you can give the name of your machine name. If your machine name is "database" you can give /etc/hosts entry as,

127.0.0.1 database localhost.localdomain localhost

And then ping database again. Make sure you are able to ping your host.

Step 04: Diagnosis DNS problem if you have DNS setup
If you have DNS setup, ping is not a tool to diagnose DNS problem. A better tool to use is nslookup, dnsquery, or dig.

$nslookup www.google.com
The forward and reverse lookup should succeed.

Step 05: Check nsswitch.conf
$ cat /etc/nsswitch.conf
hosts: files dns

Make sure host lookup is also done through the /etc/hosts file and not just dns. The keyword files should come before dns.

Step 06: Check resolv.conf
$ cat /etc/resolv.conf
nameserver 4.2.2.2

Automatic Startup and Shutdown (dbstart & dbstop)

Starting Oracle Instance up on System Startup

RHEL5: -
Creating the startup bash script: -
Open a text editor (as root) and put the content below in it, saving the le as /etc/init.d/dbora :

#!/bin/sh
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.
#
# Set ORA_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
#
# Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORA_HOME.
ORA_HOME=/u01/app/oracle/product/10.2.0/db_1
ORA_OWNER=oracle
if [ ! -f $ORA_HOME/bin/dbstart ]
then
echo "Oracle startup: cannot start"
exit
fi
case "$1" in
'start')
# Start the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"
su - $ORA_OWNER -c $ORA_HOME/bin/dbstart
su - $ORA_OWNER -c "$ORA_HOME/bin/emctl start dbconsole"
su - $ORA_OWNER -c "$ORA_HOME/bin/isqlplusctl start"
;;
'stop')
# Stop the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su - $ORA_OWNER -c "$ORA_HOME/bin/isqlplusctl stop"
su - $ORA_OWNER -c "$ORA_HOME/bin/emctl stop dbconsole"
su - $ORA_OWNER -c $ORA_HOME/bin/dbshut
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
;;
esac

Then, open a root terminal and give the le executable rights:

chmod 755 /etc/init.d/dbora

Then link this script to the les inside /etc/rc3.d:

ln -s /etc/init.d/dbora /etc/rc3.d/S99dbora
ln -s /etc/init.d/dbora /etc/rc4.d/S99dbora
ln -s /etc/init.d/dbora /etc/rc5.d/S99dbora
ln -s /etc/init.d/dbora /etc/rc0.d/K10dbora
ln -s /etc/init.d/dbora /etc/rc6.d/K10dbora

Fixing the dbstart script

The dbstart script, which is called inside the dbora script, has some errors. Open the le /u01/app/oracle/product/10.2.0/db_1/bin/dbstart as oracle user and change: -

ORACLE_HOME_LISTNER=/ade/vikrkuma_new/oracle
to
ORACLE_HOME_LISTNER=$ORACLE_HOME

Otherwise, your listener might not get started.

Flagging the orcl instance to be started: -

We open the /etc/oratab le and change the last letter from N to Y:

orcl:/u01/app/oracle/product/10.2.0/db_1:N
to
orcl:/u01/app/oracle/product/10.2.0/db_1:Y
___________________________________________________________________________________

Normal: -

1. Create oradb file under /etc/init.d

#vi /etc/init.d/oradb (add below contents)

#!/bin/sh
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.
#
# Set ORA_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
#
# Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORA_HOME.

ORA_HOME=/oracle/u01/app/oracle/product/10.2.0/db_1
ORA_OWNER=oracle

if [ ! -f $ORA_HOME/bin/dbstart ]
then
echo "Oracle startup: cannot start"
exit
fi

case "$1" in
'start')
# Start the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"
su - $ORA_OWNER -c $ORA_HOME/bin/dbstart
su - $ORA_OWNER -c "$ORA_HOME/bin/emctl start dbconsole"
su - $ORA_OWNER -c "$ORA_HOME/bin/isqlplusctl start"
;;
'stop')
# Stop the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su - $ORA_OWNER -c "$ORA_HOME/bin/isqlplusctl stop"
su - $ORA_OWNER -c "$ORA_HOME/bin/emctl stop dbconsole"
su - $ORA_OWNER -c $ORA_HOME/bin/dbshut
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
;;
esac

2. Change permissions of the file: -
#chmod 750 /etc/init.d/oradb

3. Associate the oradb service with the appropriate run levels and set it to auto-start using the following command.
#chkconfig --level 345 oradb on

.bash_profile

# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
ORACLE_SID=orcl; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH

if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'
export EPC_DISABLED=TRUE
export PATH=/bin:/usr/gnu/bin:/opt/SUNWspro/bin:/usr/bin:${ORACLE_BASE}/local:/usr/sbin:/usr/ccs/bin:/usr/ucb:/usr/openwin/bin:/usr/local/bin:${ORACLE_HOME}/
bin:/opt/bin:/etc:.
export ENV=${HOME}/.kshrc
export MANPATH=/opt/SUNWspro/man:usr/man:/opt/sfw/man
clear
echo "The SIDs on this machine are:"
cat /etc/oratab | awk -F: '{print $1}' | grep -v "#"
. oraenv

export in Windows (.bat file) and Linux (.sh file)

How to create a batch file in Windows to take full DB backup:-

exp utility: -

for /f "tokens=1,2" %%u in ('date /t') do set d=%%v
for /f "tokens=1" %%u in ('time /t') do set t=%%u
if "%t:~1,1%"==":" set t=0%t%
set timestr=%d:~6,4%%d:~0,2%%d:~3,2%_%t:~0,2%%t:~3,2%
set copydmp=filename_%timestr%_%computername%.dmp
set copylog=filename_%timestr%_%computername%.log
set ORACLE_SID=(sid_name)
exp username/password file=E:\%copydmp% log=E:\%copylog% full=y

expdp utility: -

for /f "tokens=1,2" %%u in ('date /t') do set d=%%v
for /f "tokens=1" %%u in ('time /t') do set t=%%u
if "%t:~1,1%"==":" set t=0%t%
set timestr=%d:~6,4%%d:~0,2%%d:~3,2%_%t:~0,2%%t:~3,2%
set copydmp=filename_%timestr%_%computername%.dmp
set copylog=filename_%timestr%_%computername%.log
set ORACLE_SID=(sid_name)
expdp username/password directory=data_pump_dir dumpfile=%copydmp% logfile=%copylog% full=y

How to write a shell to take full DB backup:-

exp utility: -

export ORACLE_SID=sid_name
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
echo $ORACLE_HOME
echo $ORACLE_SID
echo 'EXPORTING FULL DB'
exp system/password@sid_name file=full_expdp_dbname_`date +%d%m%H%M`.dmp log=full_expdp_dbname_`date +%d%m%H%M`.log full=y

expdp utility: -

export ORACLE_SID=sid_name
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
echo $ORACLE_HOME
echo $ORACLE_SID
echo 'EXPORTING FULL DB'
expdp system/password@sid_name directory=data_pump_dir dumpfile=full_expdp_dbname_`date +%d%m%H%M`.dmp logfile=full_expdp_dbname_`date +%d%m%H%M`.log full=y

Shrinking the Tablespace

/*Procedure : Shrink_tbsp
Owner : SYS
Author : Naresh Awasthi
Note : Set serveroutput on before running this procedure.*/

create or replace procedure SHRINK_TBSP (p_tablespace_name in varchar2,
p_keep_size_pct number) AUTHID CURRENT_USER is
FILE_SIZE number;
FREE_BYTES number;
FILEID number;

LAST_BLOCKID_USED number;
FREE_BLOCKID number;
FILE_NAME varchar2(2000);
STMT varchar2(2000);
SHRINK_TO number;

cursor FREE_SPACE_CUROR is
select file_id FILEID,max(block_id) FREE_BLOCKID from dba_free_space
where tablespace_name=p_tablespace_name group by file_id,bytes;


BEGIN
FOR fsc_row in FREE_SPACE_CUROR LOOP
select bytes into FREE_BYTES from dba_Free_space where file_id=fsc_row.FILEID and block_id=fsc_row.FREE_BLOCKID;
select bytes,file_name into FILE_SIZE,FILE_NAME from dba_data_files where file_id=fsc_row.FILEID;
select nvl(max(block_id),0) into LAST_BLOCKID_USED from dba_extents where file_id=fsc_row.FILEID;
if LAST_BLOCKID_USED < fsc_row.FREE_BLOCKID THEN
select round((FILE_SIZE - round(FREE_BYTES * (100 - p_keep_size_pct) / 100))/1024/1024) into SHRINK_TO from dual;
STMT := 'alter database datafile '||''''||FILE_NAME||''''||' resize '||SHRINK_TO||'M';
EXECUTE IMMEDIATE STMT;

dbms_output.put_line ('Shrunk '||FILE_NAME||' from
'||FILE_SIZE||' to '||SHRINK_TO||' MB.');
end if;
END LOOP;

EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Done.');
WHEN OTHERS THEN
dbms_output.put_line('Unhandled Error : '||sqlerrm);
END;
/
Ex:- exec SHRINK_TBSP('tablespace_name',shrink_size);

Dropping all objects in a Schema

CREATE OR REPLACE PROCEDURE DROP_OBJECTS(P_Owners varchar2) IS
cursor obj_crs is
select owner, object_name, object_type
from all_objects
where owner = upper(P_Owners);
obj_REC obj_CRS%ROWTYPE;
l_grants_objects varchar2(500);
BEGIN
dbms_output.put_line('Begining to drop all objects');

Case upper(P_Owners)
when 'SYS' then dbms_output.put_line('Can drop objects for:'||upper(P_Owners));
when 'SYSTEM' then dbms_output.put_line('Can drop objects for:'||upper(P_Owners));
when 'WMSYS' then dbms_output.put_line('Can drop objects for:'||upper(P_Owners));
when 'TSMSYS' then dbms_output.put_line('Can drop objects for:'||upper(P_Owners));
when 'OUTLN' then dbms_output.put_line('Can drop objects for:'||upper(P_Owners));
when 'DBSNMP' then dbms_output.put_line('Can drop objects for:'||upper(P_Owners));
ELSE
Begin
FOR obj_REC IN obj_CRS LOOP
begin
dbms_output.put_line('OUT-1 -> ' || obj_REC.object_type);
CASE obj_REC.object_type
WHEN 'TABLE' THEN
l_grants_objects := 'drop table '||obj_REC.owner||'.'||obj_REC.object_name||' cascade constraints PURGE';
EXECUTE IMMEDIATE l_grants_objects;
-- WHEN 'MATERIALIZED VIEW' THEN
-- dbms_output.put_line('OUT-2 -> INSIDE MATERIALIZED VIEW');
-- l_grants_objects := 'drop MATERIALIZED VIEW ' || obj_REC.owner||'.'||obj_REC.object_name ;
-- dbms_output.put_line(' OUT-2 -> ' || l_grants_objects);
-- EXECUTE IMMEDIATE l_grants_objects;
WHEN 'INDEX' THEN l_grants_objects := '';
WHEN 'TYPE' THEN
l_grants_objects := 'drop type '||obj_REC.owner||'.'||obj_REC.object_name||' force';
EXECUTE IMMEDIATE l_grants_objects;
ELSE
dbms_output.put_line('OUT-2222** -> INSIDE MATERIALIZED VIEW');
l_grants_objects := 'drop '||obj_REC.object_type||' '||obj_REC.owner||'.'||obj_REC.object_name;
EXECUTE IMMEDIATE l_grants_objects;
END CASE;
dbms_output.put_line(l_grants_objects);
Exception
when others then
l_grants_objects := '';
end;
end loop;
end;
end case;
dbms_output.put_line('All objects are dropped');
End;
/

Ex:- exec DROP_OBJECTS('user_name');

csscan utility

cssan utility to change the Character set of database:-

Beware: - Before doing this take a full database backup

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

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

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

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

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

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

tkprof

How to get queries, which is executed by particular user:-

1. Change the timed_statistics parameter to TRUE:-
SQL> alter system set TIMED_STATISTICS=TRUE;

2. Turn tracing on user for session level:-
SQL> alter session set SQL_TRACE=TRUE;

As a DBA, execute the below command to enable sql trace for particular user:-
SQL> exec DBMS_SESSION.SET_SQL_TRACE_IN_SESSION(sid,serial#,true);
Get the sid and serial# from the V$session view.

3. Then, see the user dump destination for trace file.

4. Execute the below command to analyze & create insert script for the trace file:-
$tkprof (sql_trace_file_name) (any_text_file_name) insert=tkprof_table.sql
It will create in text file and script for to create tkprof_table.

5. Execute the tkprof_table.sql file in sys user schema.

6. It will create tkprof_table, with contents.

7. Execute the below query to get the queries of particular user.
(before get the user_id from the dba_users view)
set long 5000
select sql_statement from tkprof_table where user_id =;