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 use

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 Option 4:  When we already know ASM home (grid infrastructure) we can use the cluster commands: $ /oracle/product/12.1.0.2/grid/bin/crsctl stat res -t -w &qu

From Oracle 12.1 we have the ability to "record" the usage of permissions in our application and then we can narrow the permissions only to the minimal requirement. Installation CREATE USER YOSSI IDENTIFIED BY YOSSI; GRANT DBA, RESOURCE TO YOSSI; A user defined condition, when user is YOSSI (type = G_CONTEXT). BEGIN     DBMS_PRIVILEGE_CAPTURE.create_capture ( name => 'yossi_pol', TYPE => DBMS_PRIVILEGE_CAPTURE.g_context, condition => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''YOSSI''' );     DBMS_PRIVILEGE_CAPTURE.enable_capture ( 'yossi_pol' ); END; / Verify that the capture is defined and enabled COLUMN ROLES                FORMAT a20 COLUMN CONTEXT              FORMAT a30 COLUMN ENABLED              FORMAT a7   SELECT name ,        TYPE ,        enabled ,        roles ,        context     FROM dba_priv_captures ORDER BY name; Work with user YOSSI (run the a

In Oracle Restart, there are two confusing commands for setting up restart behavior. 1. SRVCTL command. srvctl add/modify database -y {AUTOMATIC | MANUAL} Management policy for the database. If AUTOMATIC (the default), the database is automatically restored to its previous running condition (started or stopped) upon restart of the database host computer. If MANUAL, the database is never automatically restarted upon restart of the database host computer. A MANUAL setting does not prevent Oracle Restart from monitoring the database while it is running and restarting it if a failure occurs. https://docs.oracle.com/cd/E11882_01/server.112/e25494/restart.htm#BABHHAHI 2. CRSCTL command. crsctl modify resource ora.pdb.db -attr AUTO_START=restore -unsupported (The -unsupported syntax is needed for oracle 12c) AUTO_START Indicates whether Oracle Clusterware automatically starts a resource after a cluster server restart. Valid AUTO_START values are: - always: Restarts the resource