Showing posts from January, 2018

Coloring alert.log output via tail and less

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


export SCRIPT_DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"
source ${SCRIPT_DIR}/platform.env


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" = "" ]
    if [ ! -s ${ALERT_FILE} ]
        echo -e &…

DBA Scripts coding considerations for best compatibility to any platform

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 systemsShell: 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 scripts, I use Toad / Cloud Con…

Basics commands for DBA part 1 - finding instance

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/ stat res -t -w "TYPE = ora.database.type"|awk '/^ora./ {l=$…

Oracle 12c Capture Privilege Usage

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' );
/ Verify that the capture is defined and enabled COLUMN ROLES                FORMAT a20
COLUMN CONTEXT              FORMAT a30
  SELECT name
,        TYPE
,        enabled
,        roles
,        context
    FROM dba_priv_captures
ORDER BY name; Work with user YOSSI (run the application)... Stop the captur…

Oracle srvctl: Management policy Vs crsctl: AUTO_START (in Oracle Restart)

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.
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 when the server re…