Oracle DBA Place

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 fi

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

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}$');

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/

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

Trying to retrieve lost records from a datapump backup directly to the production database using impdp syntax: impdp User/password directory=my_directory dumpfile=my_full_backup.dmp logfile=imp_lost_records.log QUERY=MY_TABLE:\"where code=1 and recid in \(2,5,8\) \" job_name=imp_lost_records INCLUDE=TABLE:\"=\'MY_TABLE\'\" CONTENT=DATA_ONLY TABLE_EXISTS_ACTION=APPEND