Oracle DBA Place

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 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

Using Autotrace SQL> set autotrace traceonly explain SQL> select ename from emp where sal > 500; Execution Plan ---------------------------------------------------------- Plan hash value: 2872589290 ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |Cost (%CPU)| Time | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 1 | 25 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------- Using DBMS_XPLAN Package SQL> explain plan for select ename from emp where sal > 500; Explained. SQL> select * from TABLE(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------ Plan hash value: 2872589290 ------------------------------------------------------------------------- |...

Using the same technique I mentioned at " Tracing Commands " here is a code in a form of logging trigger, easier when you want to capture just a specific schema from the very first transaction. CREATE OR REPLACE TRIGGER SYS. LOGON_TRACE_CRYSTAL_TRG AFTER LOGON ON CRYSTAL. SCHEMA DECLARE cmd VARCHAR2(100); BEGIN cmd := 'ALTER SESSION SET max_dump_file_size = unlimited'; EXECUTE IMMEDIATE cmd; cmd := 'ALTER SESSION SET tracefile_identifier = ''10046'''; EXECUTE IMMEDIATE cmd; cmd := 'ALTER SESSION SET statistics_level = ALL'; EXECUTE IMMEDIATE cmd; cmd := 'ALTER SESSION SET events ''10046 trace name context forever, level 12'''; EXECUTE IMMEDIATE cmd; EXCEPTION WHEN OTHERS THEN --NULL; RAISE; END ; / optional: CREATE OR REPLACE TRIGGER  SYS. LOGOFF_TRACE_CRYSTAL_TRG BEFORE LOGOFF ON CRYSTAL. SCHEMA DECLARE cmd VARCHAR2(100); BEGIN cmd := 'ALTER SESSION SET EVENTS ''10046...

Cool and simple code to format any kind of script in Unix environment awk.info » Format Shell Scripts Recommended

Background: Dynamicaly moving control files and redo log files from filesystem /dbdata1/ to /dbdata3/ and from /dbdata2/ to /dbdata4/ 1. run as sysdba export ORACLE_SID=orcl sqlplus / as sysdba create pfile='pfile_backup_orcl.ora' from spfile; SET pagesize 0 SET feedback off spool OS_command_orcl.sh SELECT 'cp ' || NAME || ' ' || CASE WHEN REGEXP_SUBSTR(NAME, '^\/[^/]+\/') = '/dbdata1/' THEN REPLACE (NAME, '/dbdata1/', '/dbdata3/') WHEN REGEXP_SUBSTR(NAME, '^\/[^/]+\/') = '/dbdata2/' THEN REPLACE (NAME, '/dbdata2/', '/dbdata4/') END OS_command FROM v$controlfile UNION ALL SELECT 'cp ' || MEMBER || ' ' ||CASE WHEN REGEXP_SUBSTR(MEMBER, '^\/[^/]+\/') = '/dbdata1/' THEN REPLACE (MEMBER, '/dbdata1/', '/dbdata3/') WHEN REGEXP_SUBSTR(MEMBER, '^\/[^/]+\/') = '/dbdata2/' THEN REPLACE (MEMBER, '/dbdata2/', ...

I find it usefull sending log files from linux/unix by mail, some times it is just for saving clicks instead of using ftp, and often as part of a script. Of course that the server should be configured for that, usually it is enabled by default. Sending attached file uuencode original_file_name new_file_name | mailx -s "Subject" My.Mail@Mail.COM Sending a file as text in the email body mailx -s "Subject" My.Mail@Mail.COM &lt file_name (if mailx does not exists -&gt just use mail)

Using Enterprise Manager or dbconsole is the convenient way for using SQL Advisor, the problem is that these options not always exists, and there are some scenarios that you are not authorized to activate dbconsole. So we are left with the PL/SQL option which appears to be not so complicated. In order to access the SQL tuning advisor API, a user must be granted the ADVISOR privilege: CONN sys/password AS SYSDBA GRANT ADVISOR TO my_user; CONN my_user/my_password Creating tuning task DECLARE l_sql VARCHAR2(3200); l_sql_tune_task_id VARCHAR2(100); BEGIN l_sql := 'SELECT COUNT (*) FROM MY_TABLE'; l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( sql_text => l_sql, scope => DBMS_SQLTUNE.scope_comprehensive, time_limit => 360, task_name => 'Yossi_Nixon_tuning_task1', description => 'Tun...

Some times there are problems using installation with GUI, the reason can be: 1. Firewall 2. Unix security 3. Client software (missing or not working( and maybe some more reasons If your environment is cooperative helping you solve this - it is simple - go for it. But if you are on your own and can't reach "the right guys" - do it yourself " Silently " Look for the proper response file fits for your needs in /database/response/ Edit it and run the following command: $ ./runInstaller -force -invPtrLoc /oraInst.loc -silent -noconfig -ignoreSysPrereqs -responseFile /database/response/enterprise.rsp You can also run the installer without a response file as mentioned in Metalink Note 782918.1 $ ./runInstaller -silent -force -debug \ FROM_LOCATION="/mount/dvd/database/stage/products.xml" \ ORACLE_HOME="/u01/app/oracle/product/11.1.0/db_1" \ ORACLE_HOME_NAME="Ora11gDb1" ORACLE_BASE="/u01/app/oracle" \ TOPLEVEL_COMPONENT='{...

I noticed that many DBAs are not aware of the need to run a prerequisite system check before installation of an oracle product. This special check should be done in addition to the installation document and the check inside the installer. The utility called RDA with a module for Health Check called HCVE. It is well documented for RAC but can be used for simple installation. RDA is maintained frequently and adjusted to the OS version against the oracle software. You can read about it in Metalink Doc ID: 250262.1 "RDA - Health Check / Validation Engine Guide" Sample ./rda.sh -T hcve Processing HCVE tests ... Available Pre-Installation Rule Sets: 1. Oracle Database 10g R1 (10.1.0) PreInstall (AIX) 2. Oracle Database 10g R2 (10.2.0) PreInstall (AIX) 3. Oracle Database 11g R1 (11.1.0) PreInstall (AIX) 4. Oracle Application Server 10g (9.0.4) PreInstall (AIX) 5. Oracle Fusion Middleware 11g R1 (11.1.1) PreInstall (AIX) 6. Oracle Portal PreInstall (Generic) Availab...

Hi, Struggling the right syntax in Unix environment for importing a lost view, here is the proper way: impdp user/password@database directory=dir_dump dumpfile=dump.dmp logfile=imp_MY_VIEW.log INCLUDE=VIEW:\"= \'MY_VIEW\'\" job_name=imp_MY_VIEW

while running archivelog backup and the file is missing: RMAN-06059: expected archived log not found, lost of archived log compromises recoverability trying to fix it by crosscheck: run { allocate channel c1 type disk ; crosscheck archivelog all ; release channel c1 ; } validation succeeded for archived log archive log filename=D:REDOARCHARCH_1038.DBF recid=1017 stamp=611103638 still need to run: allocate channel for maintenance type disk; crosscheck archivelog all; release channel;

We already had the same symptom of ORA-15032 & ORA-15063 mentioned in my last post . Trying to follow these instructions didn't help this time. ls -l /dev/oracleasm/disks/* rpm -qa | grep oracleasm kfod asm_diskstring='ORCL:*' disks=all /etc/sysconfig/oracleasm conatin ORACLEASM_SCANORDER=dm spfile has: asm_diskstring = /dev/dm* All of these command worked fine. Beacause of the dm* parameter I checked the filesystem: $ ls -l /dev/dm-* /bin/ls: /dev/dm-*: No such file or directory It seems that the dm* devices are missing and must be there for ASM by Note 602952.1 : NOTE: When scanning, only the device names known by the kernel are scanned. With device-mapper, the kernel sees the devices as /dev/dm-XX. The /dev/mapper/XXX names are created by udev for human readability. Any configuration of ORACLEASM_SCANORDER or ORACLEASM_SCANEXCLUDE must use the dm prefix. Since this Host is RedHat 5, we found the source of the problem by Note 558596.1: Cause: The oracleasm scans /p...

We are cloning our primary production database to a test system periodically. Sometimes it happens that we are adding a datafile to the primary database while the copy is in progress. After the copy we are creating a new control file based on the production ( with the new datafile ), trying to recover the cloned database we are getting the following error (from the alert.log): SQL> alter database recover automatic until time '2009-03-15 16:00:00' using backup controlfile; File #114 added to control file as ' UNNAMED00114 '. Originally created as: ' /bzq1/oracalls_2009_indx1/q109_icalls_ts_05.dbf ' Errors with log /bilpre/oraarch/bzq1_1_0000048990_561428818.arc Some recovered datafiles maybe left media fuzzy Media recovery may continue but open resetlogs may fail Media Recovery failed with error 1244 ORA-283 signalled during: alter database recover automatic until time '2009-03-15 16:00:00' using backup controlfile... It seems that in the archive log t...

In Oracle 10g, the glogin.sql and login.sql are run whenever you connect to a new user. This is the glogin.sql additions that I am using in 10g: set verify off termout off head off feed off col login_prompt new_value welcome SELECT upper(SYS_CONTEXT('USERENV','SERVER_HOST') ||' ' || SYS_CONTEXT('USERENV','CURRENT_USER') ||'@' || SYS_CONTEXT('USERENV','DB_NAME')) login_prompt FROM DUAL ; set sqlprompt "_user'@'_connect_identifier> " set verify on termout on head on feed on prompt ************************************ prompt WELCOME TO &&welcome prompt ************************************ prompt set echo off serveroutput on size 100000 line 100 trims on

There is a small difference of the connection string when the target is SID or SERVICE jdbc:oracle:driver_type:[username/password]@//host_name:port_number : SID_NAME jdbc:oracle:driver_type:[username/password]@//host_name:port_number / SERVICE_NAME Colon means SID Slash means Service name

On regular basis, we encounter the need to copy a whole directory between two hosts. Usually we do it when we clone oracle application software. Each time I am looking for the right syntax … :( NO MORE !!! tar cvf‎ – source_dir / | ssh target_host "cd `pwd`;tar xvf‎ -" This one will copy source_dir to‎ target_host For slow connection‎: tar cvf‎ – source_dir / | gzip -c‎ | ssh target_host "cd `pwd`;gunzip -c|tar xvf‎ -" You will enter password once‎ (probably‎)

switch the database to archivelog mode. SQL> shutdown immediate; SQL> startup mount; SQL> alter database archivelog; SQL> alter database open; switch database to noarchivelog mode. SQL> shutdown immediate; SQL> startup mount; SQL> alter database noarchivelog; SQL> alter database open;

The trcsess utility consolidates trace output from selected trace files based on several criteria: trcsess &nbsp[output=output_file_name]         &nbsp[session=session_id]         &nbsp[clientid=client_id]         &nbsp[service=service_name]         &nbsp[action=action_name]         &nbsp[module=module_name]         &nbsp[trace_files] trcsess output=main.trc service=psdwh *trc After the consolidate trace file had been generated you can execute tkprof on it. For example: tkprof main.trc main.prf sys=no sort=exeela TKPROF: Release 10.2.0.3.0 - Production on Thu Feb 28 13:38:23 2008 Copyright (c) 1982, 2005, Oracle. All rights reserved. for more information: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sqltrace.htm

In my firm we have a seperation between System guys to the DBAs, we don't have root privileges. this is the reason that they are peparing us the ASM disks. After using "/etc/init.d/oracleasm createdisk..." I tailored a script to see all the information that I can get WITHOUT the need to connect to the ASM instance. #!/bin/ksh export ORACLE_HOME=`grep ASM /etc/oratab | cut -d: -f2` export PATH=$PATH:~$user/dba/scripts/bin:$ORACLE_HOME/bin export SID=`grep ASM /etc/oratab | cut -d: -f1` printf "\n%-15s %-14s %-11s %s\n" "ASM disk" "based on" "Minor,Major" "Size (Mb)" printf "%-15s %-14s %-11s %s\n" "===============" "=============" "===========" "=========" for i in `/etc/init.d/oracleasm listdisks` do v_asmdisk=`/etc/init.d/oracleasm querydisk $i | awk '{print $2}' | sed 's/\"//g'` v_minor=`/etc/init.d/oracleasm querydisk $i | awk -F[ '{print $2}...