All about ORA-600 lookup tool

ORA-600/ORA-7445 are generic internal error numbers for Oracle program exceptions.
Sometimes these errors are unique for your specific problem and cannot be found via search engines.
Using "ORA-600 lookup tool" may point your specific problem, faster and accurate.
The tool can be found in ORA-600 lookup tool - Metalink Document ID 153788.1

The flowing video will guide you the usage of the LookUp Tool (11:12) - Metalink Document ID 1082674.1

List of installed Database Patches

Looking for installed patches on the database I have always used

opatch lsinventory

(since 9.2 and up)
From CPUJan2006 onwards you can just query

select * from registry$history;

See Metalink Note:352783.1 for more information.

oracle 11.2 on ubuntu

installing or upgrading on Ubuntu is not supported but can be done
thanks to other bloggers it is well documented here
http://blog.arkzoyd.com/2011/11/oracle-database-11g-and-ubuntu-1110.html
thanks

Changing database options

before oracle 11.2 we should have do this steps:

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk dv_off
cd $ORACLE_HOME/bin
relink all

from now this is simpler and shorter

cd %ORACLE_HOME%/bin
chopt disable dv

All options are written in the usage syntax:

usage:

chopt [enable|disable] {option}

options:
dm = Oracle Data Mining RDBMS Files
dv = Oracle Database Vault option
lbac = Oracle Label Security
olap = Oracle OLAP
partitioning = Oracle Partitioning
rat = Oracle Real Application Testing

ADRCI & alert file

In Oracle version 11, no need to look for the location of alert log file, just use:

adrci exec="set home orcl ;show alert -tail -f"

detailed practical usage can be found here

Block Recovery using RMAN – on Oracle 11g

The purpose of this article is to simulate a block level corruption using BBED utility (block browser and editor) and recover using RMAN.
In this situation the data file remains online throughout the recovery operation and hence other segments within the tablespace remain accessible.
Since BBED exists from Oracle7 to Oracle10g, we will have to copy some files from earlier version and compile it

Cp $ORA10g_HOME/rdbms/lib/ssbbded.o $ORA11g_HOME/rdbms/lib
Cp $ORA10g_HOME/rdbms/lib/sbbdpt.o $ORA11g_HOME/rdbms/lib

Message files (list may differ):

Cp $ORA10g_HOME/rdbms/mesg/bbedus.msb $ORA11g_HOME/rdbms/mesg
Cp $ORA10g_HOME/rdbms/mesg/bbedus.msg $ORA11g_HOME/rdbms/mesg
Cp $ORA10g_HOME/rdbms/mesg/bbedar.msb $ORA11g_HOME/rdbms/mesg

Issue the following command:

make -f $ORA11g_HOME/rdbms/lib/ins_rdbms.mk BBED=$ORACLE_HOME/bin/bbed $ORACLE_HOME/bin/bbed $ORA11g_HOME/bin/bbed
password: blockedit
SQL> Set pages 0
SQL> set feedback off
SQL> spool fileunix.log
SQL> select file#||' '||name||' '||bytes from v$datafile;
SQL> spool off
$ cat bbed.par
blocksize=8192
listfile=/tmp/bbed/fileunix.log
mode=edit

Corrupt a block
Let us take the EMP table and find out the segment header of the same.

SQL> select header_file,header_block from dba_segments where segment_name='EMP';
HEADER_FILE HEADER_BLOCK
----------- ------------
5 1472
SQL> exit

$ bbed parfile=bbed.par
Password: blockedit

BBED: Release 2.0.0.0.0 - Limited Production on Sun Apr 3 14:38:48 2011

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

************* !!! For Oracle Internal Use only !!! ***************

BBED> set dba 5,10
DBA 0x0140000a (20971530 5,10)

BBED> copy to block 1475
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y
File: /u01/data/app/oracle/oradata/orcl/EMP_TS2.DBF (5)

Block: 7011 Offsets: 0 to 511 Dba:0x01401b63
------------------------------------------------------------------------
1ea20000 0a004001 db690200 00000104 bfc10000 05000000 80806c00 00000000
00000000 00f80000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>
BBED> exit

SQL> select * from EMP;
select * from EMP
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 5681)
ORA-01110: data file 5: '/u01/data/app/oracle/oradata/orcl/EMP_TS2.DBF

Recover the block
We now run a validate database command which
- Checks datafiles for physical and logical corruption
- Confirms that all database files exist and are in the correct locations
- insert the list of corrupted blocks to v$database_block_corruption

RMAN> backup validate check logical database;
SQL> select * from v$database_block_corruption;

recovering with RMAN using the list in v$database_block_corruption

RMAN> blockrecover corruption list;

you can still do it manually:

RMAN> run {blockrecover datafile 4 block 24165;}

reliable replacement for "ps -ef"

On linux redhat 5 I checked the command ps -ef in a loop and found out that it is not reliable.
checked this way:

while [ `ps -ef |grep tnslsnr | grep -v grep | wc -l` -eq 1 ]; do printf . ; done

after about of 2 minutes the loop finished since it didn't find the process.

looking for something more trusted I found the command pgrep
checked this way:

while [ `pgrep tnslsnr 1>/dev/null; echo $?` -ne 1 ]; do printf . ; done

and it is still running in a loop .... ;)
and no need to use awk or grep -v

here are some commands and the behavior of pgrep:

# pgrep smon
# pgrep -f smon
2396
2533
# pgrep -fl smon
2396 ora_smon_orcl
2533 ora_smon_mydb
# pgrep -fl ora_smon_orcl
2396 ora_smon_orcl
# echo $?
0
# pgrep not_exist_process
# echo $?
1

There are some more parameters, check: man pgrep

Filename validation Using Regular Expression

Extract the filename from a full file path
unix

select substr(file_name,(instr(file_name,'/',-1,1)+1),length(file_name)) FROM dba_data_files;

windows

select substr(file_name,(instr(file_name,'\',-1,1)+1),length(file_name)) FROM dba_data_files;

Validates a long filename using Windows' rules:

select file_name from table_of_files WHERE not REGEXP_LIKE(file_name,'^[^\\\./:\*\?\"<>\|]{1}[^\\/:\*\?\"<>\|]{0,254}$');

combinning these two SQLs:

WITH files AS (
select substr(file_name,(instr(file_name,'/',-1,1)+1),length(file_name)) base_filename FROM dba_data_files)
select base_filename from files WHERE REGEXP_LIKE(base_filename,'^[^\\\./:\*\?\"<>\|]{1}[^\\/:\*\?\"<>\|]{0,254}$');

Valid values for init.ora parameters

A new option started from 11.1 for listing Valid Values in init.ora at the site of Jonathan Lewis http://jonathanlewis.wordpress.com/2011/03/08/valid-values/

Israeli Identity Card Valiadation

Needed to check the validity of an Israeli identity card number
I created this simple function

CREATE OR REPLACE FUNCTION checkid (id_number IN VARCHAR2)
RETURN VARCHAR2
IS
fixed_number VARCHAR2 (10);
digit NUMBER := 0;
sum_digits NUMBER := 0;
BEGIN
CASE
WHEN LENGTH (id_number) < 9 THEN fixed_number := LPAD (id_number, 9, '0'); WHEN LENGTH (id_number) > 9
THEN
RETURN 'Too Long';
ELSE
fixed_number := id_number;
END CASE;

FOR i IN 1 .. 9
LOOP
digit :=
TO_NUMBER (SUBSTR (fixed_number, i, 1))
* (CASE WHEN MOD (i, 2) = 0 THEN 2 ELSE 1 END);

IF LENGTH (digit) > 1
THEN
digit := SUBSTR (digit, 1, 1) + SUBSTR (digit, 2, 1);
END IF;

DBMS_OUTPUT.put_line (i || '#');
sum_digits := sum_digits + digit;

IF MOD (sum_digits, 10) = 0
THEN
RETURN 'OK';
ELSE
RETURN 'BAD';
END IF;
END LOOP;

RETURN TO_CHAR (sum_digits);
END;
/

references:
http://goo.gl/z2roI
http://goo.gl/dCbS0