Get Time from Oracle DB in SQLPlus

select systimestamp from dual;

Unlock Oracle user and set FAILED_LOGIN_ATTEMPTS to unlimited

View Profile Assigned to a User:

SELECT PROFILE FROM DBA_USERS
WHERE USERNAME='SCOTT';

  PROFILE
 ----------
  DEFAULT

View Limits Set for the Profile:

SELECT RESOURCE_NAME, LIMIT FROM DBA_PROFILES
WHERE PROFILE='DEFAULT';

 RESOURCE_NAME  LIMIT  
------------------------    -------------      
COMPOSITE_LIMIT              UNLIMITED      
SESSIONS_PER_USER            UNLIMITED      
CPU_PER_SESSION               UNLIMITED      
CPU_PER_CALL                 UNLIMITED      
LOGICAL_READS_PER_SESSION   UNLIMITED      
LOGICAL_READS_PER_CALL        UNLIMITED      
IDLE_TIME                    UNLIMITED      
CONNECT_TIME                 UNLIMITED      
PRIVATE_SGA                  UNLIMITED      
FAILED_LOGIN_ATTEMPTS         10      
PASSWORD_LIFE_TIME            UNLIMITED      
PASSWORD_REUSE_TIME          UNLIMITED      
PASSWORD_REUSE_MAX            UNLIMITED      
PASSWORD_VERIFY_ FUNCTION     NULL      
PASSWORD_LOCK_TIME           UNLIMITED      
PASSWORD_GRACE_TIME          UNLIMITED      
16 rows selected.

Alter FAILED_LOGIN_ATTEMPTS Parameter:

From the user's profile we can see that the FAILED_LOGIN_ATTEMPTS parameter is set to a value of 10. Now we run below command to make it UNLIMITED.

SQL> ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;
Profile altered.

Unlock the Account:

Finally we will unlock the account by running below command.

SQL> ALTER USER SCOTT ACCOUNT UNLOCK;


Update: You'd better NOT change the settings for the "DEFAULT" profile, create a new profile instead "create profile new_profile LIMIT bla bla bla"and then assign it to the given user "alter user olap profile new_profile"

Source

SQL statement to unlock user account in oracle

ALTER USER username ACCOUNT UNLOCK;

How to add a new data file to Oracle Tablespace using sqlplus

First login as the "oracle" user, and issue the command "sqlplus "/ as sysdba"" (I am assuming that you already have configured the 

PATH environment variable correctly) and then type the following:

alter tablespace tablespace_name 

add datafile '/oracle/data/file1.dbf' size 12G autoextend off;

The above command adds a data file named "file1.db" of size 12 GB to the "/oracle/data" path with the autoextend feature turned off

Source: http://www.adp-gmbh.ch/ora/sql/alter_tablespace.html 

How To Compile and Install Tora 2.1 with Oracle Support on Ubuntu Jaunty

1. sudo apt-get build-dep tora
2. sudo apt-get install libqscintilla2-dev
3. Download instant client of Oracle Database (all zip files and extract them to one folder)
4. ./configure --with-instant-client=PATH_TO_YOUR_INSTANT_CLIENT_FOLDER
5. Follow the instructions - if any - and complete any missing steps
6. make
7. sudo make install

If you have any questions, please don't hesitate to ask here.

An Introduction to linux Shell Scripting for DBAs, A Nice Tutorial by Oracle

I found this nice tutorial and would like to share it with you, it's really useful:

http://www.oracle.com/technology/pub/articles/saternos_scripting.html

Monitoring Oracle DB in a HACMP Active/Passive Cluster

The following script is one that I developed to be executed by HACMP every 10 seconds to make sure that the database is up and running:

#!/usr/bin/bash

DATE=`date +%d%m%y` # Record the time to add a time stamp to the log
LOG=/home/hacmp/logs/sblmon_${DATE}.log # Write the time stamp

if [ -f /home/hacmp/lock ]; then # If a file named "lock" exists, then disable the scripts
echo `date` >> $LOG
echo "==================================================" >> $LOG
echo "Oracle Monitoring Script Disabled" >> $LOG
exit 0
fi

export smon=`ps -ef | grep -v grep | grep -c ora_smon_db`
export lsnr=`ps -ef | grep -v grep | grep -c LISTENER`
export pmon=`ps -ef | grep -v grep | grep -c ora_pmon_db`
export lgwr=`ps -ef | grep -v grep | grep -c ora_lgwr_db`
export dbw0=`ps -ef | grep -v grep | grep -c ora_dbw0_db`

# The below statement is to execute the sql_check_rw file (inside /oracle which is the home directory of user oracle) which contains the SQL statement needed to check the DB OPEN_MODE

export dbstatus=`su - oracle <. .profile
./sql_check_rw
EOF`

dbstatus=$(echo ${dbstatus:1}) # To remove the first character (as the string is preceded by an extra special character)

echo `date` >> $LOG
echo "==================================================" >> $LOG


if [ $dbw0 -eq 0 ]; then
echo "dbw0 failed" >> $LOG
exit 1 # Process doesn't exist
elif [ $lsnr -eq 0 ]; then
echo "lsnr failed" >> $LOG
exit 1
elif [ $pmon -eq 0 ]; then
echo "pmon failed" >> $LOG
exit 1
elif [ $lgwr -eq 0 ]; then
echo "lgwr failed" >> $LOG
exit 1
elif [ $smon -eq 0 ]; then
echo "smon failed" >> $LOG
exit 1
elif [ "$dbstatus" != "READ WRITE" ]; then
echo "DB OPEN_MODE is not READ WRITE" >> $LOG
exit 1
else
echo "Success" >> $LOG
exit 0 # All monitored processes are running and database is in "READ WRITE" OPEN_MODE
fi