Tuesday, July 29, 2014

CRON SCHEDULING



1. Scheduling a Job For a Specific Time

The basic usage of cron is to execute a job in a specific time as shown below. This will execute the Full backup shell script (full-backup) on 10th June 08:30 AM.

Please note that the time field uses 24 hours format. So, for 8 AM use 8, and for 8 PM use 20.

30 08 10 06 * /home/ramesh/full-backup
30 – 30th Minute
08 – 08 AM
10 – 10th Day
06 – 6th Month (June)
* – Every day of the week
2. Schedule a Job For More Than One Instance (e.g. Twice a Day)

The following script take a incremental backup twice a day every day.

This example executes the specified incremental backup shell script (incremental-backup) at 11:00 and 16:00 on every day. The comma separated value in a field specifies that the command needs to be executed in all the mentioned time.

00 11,16 * * * /home/ramesh/bin/incremental-backup
00 – 0th Minute (Top of the hour)
11,16 – 11 AM and 4 PM
* – Every day
* – Every month
* – Every day of the week
3. Schedule a Job for Specific Range of Time (e.g. Only on Weekdays)

If you wanted a job to be scheduled for every hour with in a specific range of time then use the following.

Cron Job everyday during working hours
This example checks the status of the database everyday (including weekends) during the working hours 9 a.m – 6 p.m

00 09-18 * * * /home/ramesh/bin/check-db-status
00 – 0th Minute (Top of the hour)
09-18 – 9 am, 10 am,11 am, 12 am, 1 pm, 2 pm, 3 pm, 4 pm, 5 pm, 6 pm
* – Every day
* – Every month
* – Every day of the week
Cron Job every weekday during working hours
This example checks the status of the database every weekday (i.e excluding Sat and Sun) during the working hours 9 a.m – 6 p.m.

00 09-18 * * 1-5 /home/ramesh/bin/check-db-status
00 – 0th Minute (Top of the hour)
09-18 – 9 am, 10 am,11 am, 12 am, 1 pm, 2 pm, 3 pm, 4 pm, 5 pm, 6 pm
* – Every day
* – Every month
1-5 -Mon, Tue, Wed, Thu and Fri (Every Weekday)
4. How to View Crontab Entries?

View Current Logged-In User’s Crontab entries
To view your crontab entries type crontab -l from your unix account as shown below.




# * * * * *  command to execute
# ┬ ┬ ┬ ┬ ┬
# │ │ │ │ │
# │ │ │ │ │
# │ │ │ │ └───── day of week (0 - 6) (0 to 6 are Sunday to Saturday, or use names; 7 is Sunday, the same as 0)
# │ │ │ └────────── month (1 - 12)
# │ │ └─────────────── day of month (1 - 31)
# │ └──────────────────── hour (0 - 23)
# └───────────────────────── min (0 - 59)                                         


http://en.wikipedia.org/wiki/Cron

Monday, July 28, 2014

ORA-19809 ERROR CHANGE DB_RECOVERY_FILE_DEST_SIZE

SQL> show parameter db_recovery



SQL > alter system set db_recovery_file_dest_size=15G scope=both;


ORA-19809 : limit exceeded for recovery  due to  lack of space 




Friday, June 27, 2014

CHECK HOW MANY DML OPERATION PERFORM ON TABLE IN DATABASE


SQL> execute dbms_stats.flush_database_monitoring_info;

SQL> select table_owner,table_name,inserts,updates,deletes from dba_tab_modifications where table_name='TEST';

TABLE_OWNER                    TABLE_NAME                        INSERTS
------------------------------ ------------------------------ ----------
   UPDATES    DELETES
---------- ----------
ANURAG                         TEST                                    1
         0          0


SQL> execute dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

SQL>  select table_owner,table_name,inserts,updates,deletes from dba_tab_modifications where table_name='TEST';

TABLE_OWNER                    TABLE_NAME                        INSERTS
------------------------------ ------------------------------ ----------
   UPDATES    DELETES
---------- ----------
ANURAG                         TEST                                    2
         0          0


Tuesday, June 24, 2014

SHARE FOLDER REMOTELY IN SOLARIS

bash-3.2#
bash-3.2# mkdir db_exp_backup
bash-3.2#
bash-3.2#
bash-3.2# chmod -R 777 /opt/db_exp_backup/
bash-3.2#
bash-3.2#
bash-3.2# df -h
Filesystem             size   used  avail capacity  Mounted on
/dev/dsk/c0t0d0s0      7.9G   441M   7.4G     6%    /
/devices                 0K     0K     0K     0%    /devices
ctfs                     0K     0K     0K     0%    /system/contract
proc                     0K     0K     0K     0%    /proc
mnttab                   0K     0K     0K     0%    /etc/mnttab
swap                    22G   996K    22G     1%    /etc/svc/volatile
objfs                    0K     0K     0K     0%    /system/object
sharefs                  0K     0K     0K     0%    /etc/dfs/sharetab
/dev/dsk/c0t0d0s4      7.9G   3.3G   4.5G    43%    /usr
/usr/lib/libc/libc_hwcap1.so.1
                       7.9G   3.3G   4.5G    43%    /lib/libc.so.1
fd                       0K     0K     0K     0%    /dev/fd
/dev/dsk/c0t0d0s3      7.9G   180M   7.6G     3%    /var
swap                    22G    64K    22G     1%    /tmp
swap                    22G    28K    22G     1%    /var/run
/dev/dsk/c0t0d0s5       90G    48G    42G    54%    /opt
bash-3.2# share
bash-3.2# vi /etc/dfs/dfstab
"/etc/dfs/dfstab" 12 lines, 397 characters

#       Place share(1M) commands here for automatic execution
#       on entering init state 3.
#
#       Issue the command 'svcadm enable network/nfs/server' to
#       run the NFS daemon processes and the share commands, after adding
#       the very first entry to this file.
#
#       share [-F fstype] [ -o options] [-d "<text>"] <pathname> [resource]
#       .e.g,
#       share  -F nfs  -o rw=engineering  -d "home dirs"  /export/home2
share -F nfs -o rw,anon=0 /opt/db_exp_backup


bash-3.2# svcs -a |grep -i nfs
disabled       Feb_15   svc:/network/nfs/server:default
online         Feb_15   svc:/network/nfs/cbd:default
online         Feb_15   svc:/network/nfs/mapid:default
online         Feb_15   svc:/network/nfs/status:default
online         Feb_15   svc:/network/nfs/nlockmgr:default
online         Feb_15   svc:/network/nfs/client:default
online         Feb_15   svc:/network/nfs/rquota:default
bash-3.2# svcadm enable nfs/server
bash-3.2# svcs -a |grep -i nfs
online         Feb_15   svc:/network/nfs/cbd:default
online         Feb_15   svc:/network/nfs/mapid:default
online         Feb_15   svc:/network/nfs/status:default
online         Feb_15   svc:/network/nfs/nlockmgr:default
online         Feb_15   svc:/network/nfs/client:default
online         Feb_15   svc:/network/nfs/rquota:default
online         17:39:17 svc:/network/nfs/server:default
bash-3.2# share
-               /opt/db_exp_backup   rw,anon=0   ""
bash-3.2#
bash-3.2#




///////////////////////////////////check shared folder detail

bash-3.2# cat /etc/dfs/dfstab

#       Place share(1M) commands here for automatic execution
#       on entering init state 3.
#
#       Issue the command 'svcadm enable network/nfs/server' to
#       run the NFS daemon processes and the share commands, after adding
#       the very first entry to this file.
#
#       share [-F fstype] [ -o options] [-d "<text>"] <pathname> [resource]
#       .e.g,
#       share  -F nfs  -o rw=engineering  -d "home dirs"  /export/home2
share -F nfs -o rw,anon=0 /opt/db_exp_backup




///////////////////////On target server

bash-3.2# mkdir  db_exp_backup
bash-3.2# mount 10.1.1.130:/opt/db_exp_backup  /db_exp_backup


Monday, May 12, 2014

STEPS TO TAKE BACKUP ON TAPE

[root@iptvdb2]mt -f /dev/rmt/0n status
Vendor 'HP      ' Product 'Ultrium 4-SCSI ' tape drive:
   sense key(0x0)= No Additional Sense   residual= 0   retries= 0
   file no= 2   block no= 0
[root@iptvdb2]


[root@iptvdb2]tar -cvf /dev/rmt/0n /nasbackup/RMANBACKUP/Nov_04_2013_backup/

a /nasbackup/RMANBACKUP/Nov_04_2013_backup// 0 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_10_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_11_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_12_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_13_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_14_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_15_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_16_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_17_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_18_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_19_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_1_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_20_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_21_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_22_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_23_1.bak 1080960 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_2_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_3_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_4_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_5_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_6_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_7_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_8_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_9_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j7oo42i2_1_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j7oo42i2_2_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j7oo42i2_3_1.bak 5101856 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j8oo42u3_1_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j8oo42u3_2_1.bak 1047248 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j9oo4341_1_1.bak 9100064 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_jaoo4396_1_1.bak 6004224 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//archive_d_jboo43de_1_1.bak 5870552 tape blocks
[root@iptvdb2]
[root@iptvdb2]




SOME OTHER ATTRIBUTES

# mt status    Print status information about the tape unit.

  # mt rewind    Rewind the tape.

  # mt erase     Erase the tape.

  # mt retension Re-tension the tape (one full wind forth and back.

  # mt fsf 1     Forward space count by one file. One can be any number.

  -f option can be used with mt to specify the different device. For
  solaris /dev/rmt/0 is the default device.

  # mt -f /dev/rmt/1n fsf 3

ERROR NO SPACE LEFT ON DEVICE

ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed withstatus: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpsemsper

Although theerror says “No space left on device”, but it is not true. The problem is duekernel semaphore configuration. After setting a proper value(echo"250 1500 100 128" > /proc/sys/kernel/sem), the problemwill be solved:

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on MonJun 21 08:20:51 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORA-27154: post/wait create failed
ORA-27300: OS system dependentoperation:semget failed with status: 28
ORA-27301: OS failure message: No spaceleft on device
ORA-27302: failure occurred at:sskgpsemsper
SQL> exit
Disconnected
[oracle@localhost ~]$ df -kh
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
304G 173G 115G 61% /
/dev/hda1 99M 12M 82M 13% /boot
tmpfs 1.3G 0 1.3G 0% /dev/shm
[root@localhost kernel]# cat /proc/sys/kernel/sem
250 100 32 128
[root@localhost kernel]# echo "250 1500 100 128" > /proc/sys/kernel/sem
[root@localhost kernel]# cat /proc/sys/kernel/sem
250 500 100 128
[root@localhost kernel]#

SCRIPT FOR CHECKING ARCHIVE LOG GENERATION ON ORACLE DATABASE

/////////////////////////On daily basis:

set pagesize 100
ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
-- As Table
SELECT TRUNC(completion_time) AS time, ROUND(SUM(blocks * block_size)/1024/1024,2) AS size_mb, ROUND(SUM(blocks * block_size)/1024/1024/1024,2) AS size_gb
FROM v$archived_log GROUP BY TRUNC(completion_time) ORDER BY 1 DESC;
-- As csv-File
SELECT TRUNC(completion_time) || ';' || ROUND(SUM(blocks * block_size)/1024/1024,2) || ';' || ROUND(SUM(blocks * block_size)/1024/1024/1024,2)
FROM v$archived_log GROUP BY TRUNC(completion_time) ORDER BY 1 DESC;



//////////////////////////On hourly basis:



set pagesize 100
ALTER SESSION SET nls_date_format = 'YYYY-MM-DD HH24';
-- As Table
SELECT TRUNC(completion_time,'HH24') AS time, ROUND(SUM(blocks * block_size)/1024/1024,2) AS size_mb, ROUND(SUM(blocks * block_size)/1024/1024/1024,2) AS size_gb
FROM v$archived_log GROUP BY TRUNC(completion_time,'HH24') ORDER BY 1 DESC;
-- As csv-File
SELECT TRUNC(completion_time,'HH24') || ';' || ROUND(SUM(blocks * block_size)/1024/1024,2) || ';' || ROUND(SUM(blocks * block_size)/1024/1024/1024,2)
FROM v$archived_log GROUP BY TRUNC(completion_time,'HH24') ORDER BY 1 DESC;

Happy Diwali 2025