Featured Post

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…

Change Listener Ports in RAC Environment

Look at the current port configured for the scan_listener $ srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521$ lsnrctl status LISTENER_SCAN1

LSNRCTL for Solaris: Version - Production on 01-JAN-20** 21:26:16

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Alias                     LISTENER_SCAN1
Version                   TNSLSNR for Solaris: Version - Production
Start Date                01-JAN-20** 16:08:18
Uptime                    0 days 5 hr. 17 min. 58 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/product/
Listener Log File         /oracle/product/…

Log mining a Redo log file / Archive log file on a remote database

At the source site   Enable the database to write to a directory you choose, in this example I use /tmp alter system set utl_file_dir='/tmp' scope=spfile;  Restart of the database is required in order for the parameter utl_file_dir be active
Extract the LogMiner dictionary to a flat file called dictionary.ora
EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', '/tmp', DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);  Copy redo log file from source to target:Copy redo log file from ASM to the operating system asmcmd cp '+DATA/PDB/ONLINELOG/group_1.259.919359545' /tmp Copy the the redo log file to the target host  scp /tmp/dictionary.ora /tmp/group_1.259.919359545 target_host:/tmp Copy the dictionary file from source to target  scp /tmp/dictionary.ora /tmp/group_1.259.919359545 target_host:/tmp  On target db  alter system set utl_file_dir='/tmp' scope=spfile;  Restart of the database is required in order for the parameter utl_file_dir be active
Add the first redo lo…