Featured Post

Extract Data Guard Commands on Oracle 12.2

Hi, These days I am starting to work on Oracle 12.2, leaving behind the old versions.
Since my main domain is Oracle Data Guard,  I posted a blog about Oracle 12c Release 2 New Features for Active Data Guard, one of a big new feature I missed is that the internal Data Guard broker configuration was changed dramatically on 12.2.
Last week, I tried to "Extract Data Guard Commands", and realized it is not working as before :(
So I sat down and adjusted it to work on 12.2, this time I enhanced the output with some more important information.

Run the following script as sysdba

PROMPT disable configuration;
PROMPT remove configuration;
   rid                      INTEGER;
   indoc                    VARCHAR2 (4000);
   outdoc                   VARCHAR2 (4000);
   p                        INTEGER;
   z                        XMLTYPE;
   y                        CLOB;
   v_xml                    XMLTYPE;
   tout                …

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

Bypassing the listener and connecting to ASM without a password from Python code

Requirement I was asked to have smart code that can connect to a local ASM instance without the need for any prior preparations like setting up the listener or any other changes in the database. And even do this without a password!
Problems Listener If there is already a listener that uses the default port (i.e. 1521), the ASM can register itself dynamically to the listener with no need to configure anything. But on systems that use ports other than 1521, the listener is not registered automatically. We could set the parameter LOCAL_LISTENER like this:
SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST='; But, I was asked to do no prior configuration.
Password Using SQL*Plus we have the option to connect to the database locally Using Password File Authentication with no need to setup a listener and without using a password in the connection string.
$ export ORACLE_SID=+ASM
$ sqlplus / as sysdba

SQL*Plus: Release Productio…