Search This Blog

Total Pageviews

Tuesday, 19 April 2011

Unix Cron format

Cron format - crontab

crontab -l


1. minute (from 0 to 59)
2. hour (from 0 to 23)
3. day of month (from 1 to 31)
4. month (from 1 to 12)
5. day of week (from 0 to 6) (0=Sunday)


1  2  3  4  5  6
*  *  *  *  *  *
|  |  |  |  |  | 
|  |  |  |  |  +-- Year              (range: 1900-3000)
|  |  |  |  |
|  |  |  |  +---- Day of the Week    (range: 1-7, 1 standing for Monday)
|  |  |  |
|  |  |  +------ Month of the Year   (range: 1-12)
|  |  |
|  |  +-------- Day of the Month     (range: 1-31)
|  |
|  +---------- Hour                  (range: 0-23)
|
+------------ Minute                 (range: 0-59)



* * * * * /xxx/xxx/xx/anuj.sh


if 5 stars then



1. every minute
2. of every hour
3. of every day of the month
4. of every month
5. and every day in the week.



Execute every Friday 1AM

So if we want to schedule script to run at 1AM every Friday,


0 1 * * 5 /xxx/xxx/xx/anuj.sh

The script is now being executed when the system clock hits:

1. minute: 0
2. of hour: 1
3. of day of month: * (every day of month)
4. of month: * (every month)
5. and weekday: 5 (=Friday)
=================================================================

##┌───────────── minute (0 - 59)
# │ ┌───────────── hour (0 - 23)
# │ │ ┌───────────── day of month (1 - 31)
# │ │ │ ┌───────────── month (1 - 12)
##┌───────────── minute (0 - 59)
# │ ┌───────────── hour (0 - 23)
# │ │ ┌───────────── day of month (1 - 31)
# │ │ │ ┌───────────── month (1 - 12)
# │ │ │ │ ┌───────────── day of week (0 - 6) (Sunday to Saturday;
# │ │ │ │ │                                       7 is also Sunday)
# │ │ │ │ │
# │ │ │ │ │
# * * * * *  command to execute
======================================================================


Execute on Mon to friday


So if we want to schedule the script to Monday till Friday at 1 AM


0 1 * * 1-5 /bin/execute/this/script.sh

The script is now being executed when the system clock hits:

1. minute: 0
2. of hour: 1
3. of day of month: * (every day of month)
4. of month: * (every month)
5. and weekday: 1-5 (=Monday til Friday)



# Minute   Hour   Day of Month       Month          Day of Week        Command    
# (0-59)  (0-23)     (1-31)    (1-12 or Jan-Dec)  (0-6 or Sun-Sat)                
    0        2          12             *                *            /usr/bin/find




Examples:

*/30 * * * * /aptus/oracle/admin/aptdb/script/check_oracle_tablespace.sh -c aptdb -w 90 -c95 > /tmp/tablespace.nagios

5 5 * * * /aptus/oracle/admin/aptdb/script/scheduler.sh > /dev/null 2>&1

# 5 6 * * * /aptus/oracle/admin/aptdb/script/exp_solomon.sh > /dev/null 2>&1



Standard output (STDOUT) and standard errors (STDERR).

STDOUT is marked 1, STDERR is marked 2.

So the following statement tells Linux to store STDERR in STDOUT as well, creating one datastream for messages & errors:

2>&1


every hr ....


***-amd-02:/home/anujs/Downloads # crontab -l
# DO NOT EDIT THIS FILE - edit the master and reinstall.
# (/tmp/crontab.XXXXwcdEfa installed on Tue Sep 7 10:10:07 2010)
# (Cron version V5.0 -- $Id: crontab.c,v 1.12 2004/01/23 18:56:42 vixie Exp $)

59 * * * * echo " ">/var/log/warn > /dev/null 2>&1

59 * * * * echo " ">/var/log/messages > /dev/null 2>&1



cron and email confirmation .....

15 22 * * * /xxxx/oracle/admin/xxdb/scripts/Rmanbackup.sh 2>&1 | mail -s "RMAN BACKUP On (xxx-xxx-01) completed... " anuj.singh@xxxx.co.uk

Monday, 18 April 2011

RMAN Backup Report for SCN for Recovery

oracle@novagenesis$ rman target / log=rmanbackupinfo.log
RMAN> list backup ;

RMAN> list backup;

RMAN>exit;


cat rmanbackupinfo.log this will give all the SCN no of and valid backup info


This will provide last SCN number

SQL> set numwidth 18
SQL>select max(CHECKPOINT_CHANGE#) from v$backup_datafile;

MAX(CHECKPOINT_CHANGE#)
-----------------------
121937781

unix kill all process

unix kill process
awk kill


hosea:~ # ps -ef | grep cron | awk '{print $2}'|wc -l
3342

kill -9 `ps -ef | grep cron |grep -v grep | awk '{print $2}'`

Tuesday, 12 April 2011

Oracle ldap clear entry Flush LDAP Cache

Flushing LDAP Cache information for group
Flushing LDAP Cache information for passwd


uid=7011(jtime76) gid=100(users) groups=100(users),6001(oinstall),6002(dba)
jtime76@apt-lnxtst-01:~> logout
apt-lnxtst-01:~ # nscd -i passwd
apt-lnxtst-01:~ # su - jtime76
jtime76@apt-lnxtst-01:~> id
uid=7011(jtime76) gid=100(users) groups=100(users),6001(oinstall),6002(dba)
jtime76@apt-lnxtst-01:~> logout
apt-lnxtst-01:~ # nscd -i passwd
apt-lnxtst-01:~ # vi /etc/nscd.conf
apt-lnxtst-01:~ # /etc/init.d/nscd stop
Shutting down Name Service Cache Daemon done
apt-lnxtst-01:~ # /etc/init.d/nscd start
Starting Name Service Cache Daemon done

changed the value in ldap browser ( removed the group from



/ldapbrowser> ./lbe.sh








apt-lnxtst-01:~ # su - jtime76
jtime76@apt-lnxtst-01:~> id
uid=7011(jtime76) gid=100(users) groups=100(users),6001(oinstall),6002(dba) ---- dba group is still there
jtime76@apt-lnxtst-01:~> logout
apt-lnxtst-01:~ # nscd -i group -------- then flush the group information using this command
apt-lnxtst-01:~ # nscd -i passwd -------- if you want to flush user information
apt-lnxtst-01:~ # su - jtime76
jtime76@apt-lnxtst-01:~> id
uid=7011(jtime76) gid=100(users) groups=100(users),6001(oinstall)

sqlplus / as sysdba is hanged

sqlplus / as sysdba is not working


apt-lnxtst-01:~ # /etc/init.d/nscd stop
Shutting down Name Service Cache Daemon done



sqlplus / as sysdba will hang , if nscd service is not running on your server
( if you are using ldap )


apt-lnxtst-01:~ # su - oracle
oracle@apt-lnxtst-01:~> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Apr 12 12:37:46 2011

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

----------------this screen will stay like this --------------------------
--- hanged hanged hanged hanged hanged hanged hanged hanged hanged hanged hanged ----------


apt-lnxtst-01:/aptus/www/jtime76 # /etc/init.d/nscd start
Starting Name Service Cache Daemon done



now this will work

open a new terminal then

# su - oracle


oracle@apt-lnxtst-01:~> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Apr 12 12:41:57 2011

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Friday, 8 April 2011

How to run SQLplus as different user non oracle user on unix

How to run SQLplus on non oracle user ?



jtime76@apt-lnxtst-01:~> cat .bashrc
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=/opt/oracle/product/10.2
export ORACLE_SID=orcl
export PATH=$ORACLE_HOME/bin:$PATH:.
export LIBXCB_ALLOW_SLOPPY_LOCK=1
export TZ=GMT
# export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib32
export LD_LIBRARY_PATH=$ORACLE_HOME/lib



jtime76@apt-lnxtst-01:~> ls -ltr /opt/oracle/product/10.2
total 58
-rwxr-xr-x 1 oracle oinstall 0 2005-06-07 17:50 root.sh.old
drwxr-x--- 3 oracle oinstall 72 2010-09-09 09:39 wwg



so add user name in the group . My user jtime

cat /etc/group
uucp:x:14:
wheel:x:10:
www:x:8:
xok:x:41:
users:x:100:
oinstall:!:6001:boadmin,jtime <<<<<<<<<<<<----
boadmin:!:6003:
mysql:!:106:
zimbra:!:6004:
postdrop:!:6005:
mailman:!:67:
sophosav:!:6006:
ldap:!:70:
winbind:!:161:
named:!:44:
jtime79:!:6007:



jtime76@apt-lnxtst-01:~> env|grep ora
LD_LIBRARY_PATH=/opt/oracle/product/10.2/lib
ORACLE_BASE=/opt/oracle
ORA_NLS32=/opt/oracle/product/10.2/nls/data
ORACLE_HOME=/opt/oracle/product/10.2

Wednesday, 6 April 2011

Anuj's glogin.sql file



 


https://anuj-singh.blogspot.com/2021_07_20_archive.html






location of file is cd $ORACLE_HOME/sqlplus/admin

Oracle glogin.sql file


glogin file ......

set verify off termout off head off feed off
col add new_value welcome
select UTL_INADDR.GET_HOST_NAME||':'||UTL_INADDR.GET_HOST_ADDRESS "add" from dual ;


column global_name new_value gname
set termout off
select UTL_INADDR.GET_HOST_NAME||'@'||lower(SYS_CONTEXT('USERENV','CURRENT_USER'))||'-Sql>' global_name from dual ;
set sqlprompt 'gname'


set verify on termout on head on feed on
prompt ************************************
prompt WELCOME TO &&welcome
prompt ************************************
prompt
set echo off serveroutput on size 100000 line 100 trims on

==============================================================================================

or 


cat glogin.sql
--
-- Copyright (c) 1988, 2005, Oracle.  All Rights Reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
--   This script is automatically run
--


set termout off
set long 5000

set pagesize 9999  linesize 180  longchunksize 180  arraysize 100  num 10

define gname=idle
column global_name new_value gname
--select lower(user) || '@' || sys_context('USERENV','INSTANCE_NAME') global_name from dual;
select lower(sys_context('USERENV','DATABASE_ROLE'))||':'||lower(user) || '@' || sys_context('USERENV','INSTANCE_NAME') global_name from dual;
select lower(sys_context('USERENV','DATABASE_ROLE'))||':'||lower(user) || '@' || sys_context('USERENV','DB_UNIQUE_NAME') || '-' ||sys_context('USERENV','INSTANCE_NAME') global_name from dual;


set sqlprompt '&gname sqlplus> '

-- host title sqlplus connected to &gname
alter session set nls_date_format='dd-mm-yy hh24:mi';

set tab off
set termout on

===========================
Output .. 
primary:sys@rac-rac2 sqlplus>




====================


login.sql



set termout off  long 5000 pagesize 9999  linesize 180 longchunksize 180 arraysize 100 num 10

define gname=idle
column global_name new_value gname
--select lower(user) || '@' || sys_context('USERENV','INSTANCE_NAME') global_name from dual;
select lower(sys_context('USERENV','DATABASE_ROLE'))||':'||lower(user) || '@' || sys_context('USERENV','INSTANCE_NAME') global_name from dual;
select lower(sys_context('USERENV','DATABASE_ROLE'))||':'||lower(user) || '@' || sys_context('USERENV','DB_UNIQUE_NAME') || '-' ||sys_context('USERENV','INSTANCE_NAME') global_name 
from dual;


set sqlprompt '&gname> '

host title sqlplus connected to &gname
alter session set nls_date_format='dd-mm-yy hh24:mi';

set tab off
set termout on


----
output 
primary:sys@anuj-vihaan>

Oracle DBA

anuj blog Archive