Oracle DBA Place

Making my day to day job easier and convenient I like having some utilities and some aliases. We, the DBAs nation, have a need to look at the alert.log file frequently. So basically, I did the following: - Created a script to tail my alert.log file. - Colored the important words. - Added an alias to that script $ vi tail_alert.sh #!/bin/bash export SCRIPT_DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )" source ${SCRIPT_DIR}/platform.env ECHO_RED="\E[1;40;31m" ECHO_STD="\E[1;40;37m" LIGHT_RED=`echo -e '\033[1;40m\033[1;31m'` RED=`echo -e '\033[1;40m\033[0;31m'` LIGHT_PURPLE=`echo -e '\033[1;40m\033[0;35m'` GREEN=`echo -e '\033[1;40m\033[32m'` NORMAL=`echo -e '\033[0m'` if [ "$1" = "" ] then     ALERT_FILE=${ORACLE_BASE}/diag/${DIR_TYPE}/${ORACLE_UNQNAME,,}/${ORACLE_SID}/trace/alert_${ORACLE_SID}.log     if [ ! -s ${ALERT_FILE} ]     then...

Did you find yourself trying to convert your DBA scripts to work on other OS or UNIX flavors ? This is a typical issue DBAs cope with, the solutions can be: Each system has its own best scripting code (PowerShell for windows, bash for Linux), let's keep some versions of the same code. Write the code in a common language for all systems Shell : In the past I used the old Posix shell (sh) which exited in all Unix systems. These days I use bash as is very common.  Perl : Oracle installation already has its own Perl, no need to install it. Java : Oracle installation already has its own java, no need to install it. Python : Python should be installed, but the libraries that can be used are huge, and you can write shorter code. Write in sql/plsql as much as you can, the external code will be minimal (cmd / sh). Work remotely from your convenient operating system, using your scripts written in the language you master. Use a few macros injected to your terminal. Why...

Hi, In the next few blogs I will discuss on scripting technics that DBA usually needs. I am concentrating in the commands, that are usually part of larger script. Later on I will submit some scripts that uses these technics. Identifying all instances running on the current machine Option 1:  # ps -ef |grep smon oracle    3025     1  0  2016 ?        00:00:48 asm_smon_+ASM oracle    11459      1  0 17:24 ?        00:00:00 ora_smon_ fdb root     12763 11332  0 17:52 pts/1    00:00:00 grep --color=auto smon SID is fdb and process id is 11459     Option 2:  # pgrep  -lf _pmon_ 3025 asm_pmon_+asm 11459 ora_pmon_ fdb SID is fdb and process id is 11459    Option 3:  Cleaner way for sid: $ ps -ef |grep 'ora_smon_.*$' | grep -v grep | awk -F_ '/ora_smon/{print $NF}' fdb SID is fdb Op...

SQL script for calculating the total size of RMAN backups col TYPE for a20 Heading "Backup Type" col completion_time Heading "Completion Time" col MB for 99999999999  Heading "Size (MB)" col BCPTIME for 99999999999 Heading "Backup Time (minutes)" SELECT TO_CHAR (completion_time, 'YYYY-MON-DD') completion_time        , TYPE        , ROUND (SUM (bytes) / 1048576)          MB        , ROUND (SUM (elapsed_seconds) / 60)     BCPTIME     FROM (SELECT CASE                     WHEN s.backup_type = 'L' THEN 'Archive Log' --                    WHEN s.controlfile_included = 'YES' THEN 'Control File'                  WHEN s.backup_type = 'D' THEN 'Full (Level ' || NVL (s.incremental_level, 0) || ')'       ...

Hi, I found a great code for extracting data guard commands (reverse engineering) I adjusted it to oracle 12.1 with far sync and it working fine. Just remember to run on Primary & Far Sync instances the following command: 1. SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_n='' scope=both sid='*'; (where n is 2 or above) 2. DGMGRL> disable configuration; 3. DGMGRL> remove configuration; and then run the script: displayconfig.sql SET SERVEROUTPUT ON SET LINESIZE 300 SET FEEDBACK OFF DECLARE rid INTEGER; indoc VARCHAR2 ( 4000 ); outdoc VARCHAR2 ( 4000 ); p INTEGER; z XMLTYPE; y CLOB; v_xml XMLTYPE; tout VARCHAR2 ( 4000 ); db_type VARCHAR2 ( 10 ); db_headers_commands clob; db_commands clob; db_commands_RedoRoutes clob; BEGIN indoc := ' '; y := NULL; rid := dbms_drs.do_control ( indoc ); outdoc := NULL; p := 1; WHILE ( outdoc IS NULL ) LOOP outdoc := dbms_drs.get_respons...

I am used to relay on tools for formatting and indenting SQL or PL/SQL code. Looking for the same for linux scripts (especially bash) I found the following code fmt.script #!/usr/bin/env perl # fmt.script - format scripts in awk, csh, ksh, perl, sh # # we do: # standardize indentation (an indent is one tab by default) # strip trailing whitespace # change ${var} to $var where possible # change ">x" to "> x" for shell scripts # change "[ ... ]" to "test ..." for Bourne shell scripts # # we may do someday, but these are harder: # convert $VAR to $var unless a setenv or default environment variable # possibly prepending stuff from template.sh # "if ... \nthen", for ... in\ndo", "while/until ... \ndo", "fn()\n{" # # to have fmt.script reformat itself (a fair test, yes?) try: # fmt.script fmt.script fmt.script.new # use tabs for indents # fmt.script -s4 fmt.script fmt.script.new # in...

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

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

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/', ...

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

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

When talking ASM between DBAs and System guys I had to let them "see" the new filesystem in the way they are used to. I parsed the output of asmcmd utility to be as close as it can be to the command in Unix/Linux, I called it asmbdf: #!/bin/ksh export user=oracle export ORACLE_HOME=`grep ASM /etc/oratab | cut -d: -f2` export PATH=$PATH:~$user/dba/scripts/bin:$ORACLE_HOME/bin export ORACLE_SID=`grep ASM /etc/oratab | cut -d: -f1` asmcmd lsdg | \ awk '{ if ( FNR == 1 ) { printf "%-20s %10s %10s %10s %10s %-20s\n","Filesystem","Size","Used","Avail","Use%","Mounted on" } if ( FNR > 1 ) { if ( $2 == "EXTERN" ) { REDUNDENCY=1 } if ( $2 == "NORMAL" ) { REDUNDENCY=2 } if ( $2 == "HIGH" ) { REDUNDENCY=3 } printf "%-20s %10d %10d %10d %10.2f%% %-20s\n",$(NF),($8/REDUNDENCY)*1024,(($8-$9)/REDUNDENCY)*1024,($9/REDUNDE...