Friday, 24 May 2013

How to Schedule a job in Cron Every 5 Minutes, Seconds, Hours, Days, Months

How to Run a Job in Cron Every 5 Minutes, Seconds, Hours, Days, Months


Predefined scheduling definitions
Entry Description Equivalent To
@yearly (or @annually) Run once a year at midnight in the morning of January 1 0 0 1 1 *
@monthly Run once a month at midnight in the morning of the first of the month 0 0 1 * *
@weekly Run once a week at midnight in the morning of Sunday 0 0 * * 0
@daily Run once a day at midnight 0 0 * * *
@hourly Run once an hour at the beginning of the hour 0 * * * *
@reboot Run at startup @reboot
There are several special predefined values which can be used to substitute the CRON expression. Note that in some uses of the CRON format there is also a seconds field at the beginning of the pattern (e.g., Quarz).
*    *    *    *    *  command to be executed
┬    ┬    ┬    ┬    ┬
│    │    │    │    │
│    │    │    │    │
│    │    │    │    └───── day of week (0 - 7) (0 or 7 are Sunday to Saturday, or use names)
│    │    │    └────────── month (1 - 12)
│    │    └─────────────── day of month (1 - 31)
│    └──────────────────── hour (0 - 23)
└───────────────────────── min (0 - 59)
Question: How do I execute certain shell script at a specific intervals in Linux using cron job? Provide examples using different time periods.
Answer: Crontab can be used to schedule a job that runs on certain internal. The example here show how to execute a backup.sh shell script using different intervals.

Also, don't forget to read our previous
 crontab article that contains 15 practical examples, and also explains about @monthly, @daily, .. tags that you can use in your crontab.

1. Execute a cron job every 5 Minutes

The first field is for Minutes. If you specify * in this field, it runs every minutes. If you specify */5 in the 1st field, it runs every 5 minutes as shown below.
*/5 * * * * Location/backup.sh
Note: In the same way, use */10 for every 10 minutes, */15 for every 15 minutes, */30 for every 30 minutes, etc.

2. Execute a cron job every 5 Hours

The second field is for hours. If you specify * in this field, it runs every hour. If you specify */5 in the 2nd field, it runs every 5 hours as shown below.
0 */5 * * * Location/backup.sh
Note: In the same way, use */2 for every 2 hours, */3 for every 3 hours, */4 for every 4 hours, etc.

3. Execute a job every 5 Seconds

Cron job cannot be used to schedule a job in seconds interval. i.e You cannot schedule a cron job to run every 5 seconds. The alternative is to write a shell script that uses 'sleep 5 command in it.
Create a shell script every-5-seconds.sh using bash while loop as shown below.
$ cat every-5-seconds.sh
#!/bin/bash
while true
do
 location/backup.sh
 sleep 5
done
Now, execute this shell script in the background using nohup as shown below. This will keep executing the script even after you logout from your session. This will execute your backup.sh shell script every 5 seconds.
$ nohup ./every-5-seconds.sh &

4. Execute a job every 5th weekday

This example is not about scheduling "every 5 days". But this is for scheduling "every 5th weekday".
The 5th field is DOW (day of the week). If you specify * in this field, it runs every day. To run every Friday, specify either 5 of Fri in this field.
The following example runs the backup.sh every Friday at midnight.
0 0 * * 5 Location/backup.sh
(or)
0 0 * * Fri Location/backup.sh
You can either user number or the corresponding three letter acronym for the weekday as shown below.
§  0=Sun
§  1=Mon
§  2=Tue
§  3=Wed
§  4=Thu
§  5=Fri
§  6=Sat
Note: Get into the habit of using Fri instead of 5. Please note that the number starts with 0 (not with 1), and 0 is for Sun (not Mon).

5. Execute a job every 5 months

There is no direct way of saying 'every 5 months', instead you have to specify what specific months you want to run the job. Probably you may want to run the job on 5th month (May), and 10th month (Oct).
The fourth field is for Months. If you specify * in this field, it runs every month. To run for the specific month, you have to specify the number that corresponds to the month. For example, to run the job on May and Oct, you should specify 5,10 (or) you can simply use the 3 letter acronym of the month and specify May,Oct.
The third field is for DOM (Day of the Month). If you specify * in this field, it runs every day of the month. If you specify 1 in this month, it runs 1st of the month.
The following example runs the backup.sh twice a year. i.e 1st May at midnight, and 1st Oct at midnight.
0 0 1 5,10 * Location/backup.sh
(or)
0 0 1 May,Oct * Location/backup.sh
Note: Don't make the mistake of specifying 5-10 in the 4th field, which means from 5th month until 10th month. If you want only 5th and 10th month, you should use comma.

Thursday, 23 May 2013

Query to find the SCHEMA Size in oracle database

Here the query,


SELECT s.owner,SUM (s.BYTES) / (1024 * 1024 * 1024) SIZE_IN_GB

FROM dba_segments s
GROUP BY s.owner;

Query to find out Required space while import/impdp


set linesize 300
SELECT  dts.tablespace_name,
NVL(ddf.bytes / 1024 / 1024, 0) avail,
NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024 used,
NVL(dfs.bytes / 1024 / 1024, 0) free,
TO_CHAR(NVL((ddf.bytes - NVL(dfs.bytes, 0)) / ddf.bytes * 100, 0), '990.00')
"Used %" ,
TO_CHAR(NVL((ddf.bytes - NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)) / ddf.bytes
* 100, 0), '990.00') free_pct,
decode(sign(
(NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024)/0.85 - NVL(ddf.bytes / 1024 /
1024, 0)),-1,0,(NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024)/0.85 - NVL(ddf.
bytes / 1024 / 1024, 0))  "Required MB"
FROM
sys.dba_tablespaces dts,
(select tablespace_name, sum(bytes) bytes
from dba_data_files group by tablespace_name) ddf,
(select tablespace_name, sum(bytes) bytes
from dba_free_space group by tablespace_name) dfs
WHERE
dts.tablespace_name = ddf.tablespace_name(+)
AND dts.tablespace_name = dfs.tablespace_name(+);

OCR and VOTING DISKS


                                      Voting disk

  * Manages cluster Membership and
* Arbitrates cluster ownership b/w the nodes. (in case of n/w failures)
* File, resides in shared storage.
* High availability, Oracle recommends more then one voting disks.
       * an Odd number of voting disk.
* if use a single voting disk, then use mirroring at the file system level for redundancy.

* A node must be able to access more than half of the voting disks at any time.
Ex ; if you have five voting disks configured,
then a node must be able to access at least three of the voting disks at any time.
    If a node cannot access the minimum required number of voting disks it is evicted, or removed, from the cluster.

Backing Up Voting Disks:
* However, back up the voting disks at the following times:
¦ After installation
¦ After adding nodes to or deleting nodes from the cluster
¦ After performing voting disk add or delete operations
when use dd command to take backup of voting disk, backup can be performed while CRS process is Active.
No need to stop the crsd.bin process before taking voting disk.

To checks voting disks availability
#CRS_home/bin/crsctl query css votedisk

To make a backup copy of the voting disk:
#dd if=voting_disk_name of=backup_file_name
If your voting disk is stored on a raw device, use the device name in place of voting_disk_name,
for example:
#dd if=/dev/sdd1 of=/tmp/voting.dmp

Recovering Voting Disks:
If a voting disk is damaged, no longer to use by the oracle clusterware, can recover if you have voting disk backup

#dd if=backup_file_name of=voting_disk_name

To add or remove a voting disk:
* You can dynamically add and remove voting disks after installing Oracle RAC.
                  1. Run the following command as the root user to add a voting disk:
#crsctl add css votedisk path
                  2. Run the following command as the root user to remove a voting disk:
#crsctl delete css votedisk path


Oracle Cluster Registry (OCR)

* Maintain cluster configuration info., as well configuration info abt any cluster database within the cluster.
* contains info such as which database instances run on which nodes and which services run on which databases.
* OCR resides on shared storage that is accessible by all the nodes in the cluster.
* Oracle clusterware can multiplex, or maintain multiple copies of OCR.
* Oracle recommands for this feature to ensure high availability.

These Oracle Clusterware components require the following additional disk space:
       * Two Oracle Clusterware Registry files, 280 MB each, or 560 MB total disk space
       * Three voting disk files, 280 MB each, or 840 MB total disk space

Backing up of Oracle Cluster Registry(OCR):
* Oracle Clusterware automatically creates OCR backups every 4 hours.
*At any one time, Oracle Clusterware always retains the latest 3 backup copies of the OCR that are
4 hours old,
1 day old and
1 week old.
Default Location in RHEL:
CRS_home/cdata/cluster_name (or)
/u001/app/grid_home/11.2.0/grid/cdata/scanname

To find the most recent backup of the OCR:
#CRS_home/bin/ocrconfig -showbackup

OCR log location:
/001/app/grid_home/11.2.0/grid/log/servername/client

HOW TO RECREATE ASM INSTANCES AND DISKGROUPS, if ASM INSTANCE or DGroups Currepted


RECREATING ASM INSTANCES AND DISKGROUPS
---------------------------------------

In the event you cannot mount your ASM disk groups, you will be unable to start
any databases using those disk groups.  Here is a possible error reported when
mounting ASM disk groups:

SQL> startup mount

ORA-15032: not all alterations performed
ORA-15063: diskgroup "" lacks quorum of 2 PST disks; 0 found

This error may occur if:

a) ASM disk(s) is not visible on the operating system.
b) asm_diskstring parameter is not set correctly on ASM instance(s)
c) ASM metadata in disk is overwritten or corrupted

If you have seen this error or another error indicating ASM metadata corruption
and have verified that the disk(s) is visable with correct permissions on the
operating system and that the asm_diskstring parameter is set correctly, your
ASM metadata may be corrupted.  If this is the case, you may need to re-create
your ASM instance(s) and disk group(s).  The steps are:

1. Ensure that you have a prior RMAN backup of all databases using ASM
2. Shut down your ASM instance(s)
3. Use dd to clear the metadata from ASM disks
4. Re-create your ASM disk group(s)
5. Restore databases


STEP 1: ENSURE THAT YOU HAVE A PRIOR RMAN BACKUP OF ALL DATABASES USING ASM
--------------------------------------------------------------------------

The only way you can recover from ASM metadata corruption is to have a prior
RMAN backup of the database in an area that would not be affected by an ASM
instance outage.  As part of your recovery strategy, you should consider
integrating tape or other tertiary storage to safeguard your backups.

Example of RMAN backup:

1. Connect RMAN to the target database for backup

  rman nocatalog target /

2. Now Backup your Database, Archive logs and Control files.  Example:

  RMAN> backup device type disk format '/u03/backup/%U' database plus archivelog;
  RMAN> backup device type disk format '/u03/backup/ctrlf_%U' current controlfile;

3. Manually make copies of your spfiles.  Example:

  CREATE PFILE='/u03/app/oracle/product/10.1.0/dbs/init.ora'
  FROM SPFILE='/+DATA/V10FJ/spfile.ora';

If you do not have a good backup of all databases (datafiles, controlfiles,
redo logs, archive logs), DO NOT CONTINUE BEYOND STEP 1!


STEP 2: SHUT DOWN YOUR ASM INSTANCE(S)
--------------------------------------

Stop your database instances and ASM instances with sqlplus or srvctl (RAC)

SQLPLUS Example:

  setenv ORACLE_SID +ASM
  sqlplus '/ as sysdba'
  SQL> shutdown immediate

  setenv ORACLE_SID DBSCOTT
  sqlplus '/ as sysdba'
  SQL> shutdown immediate

SRVCTL (RAC) Example:

  srvctl stop asm -n
  srvctl stop asm -n
  srvctl stop database -d


STEP 3: USE DD TO CLEAR THE METADATA FROM ASM DISKS
---------------------------------------------------

All ASM metadata must be cleared before attempting to re-create ASM instances
and diskgroups.  Example Command:

  dd if=/dev/zero of=/dev/rdsk/c1t4d0s4 bs=8192 count=12800


STEP 4: RE-CREATE YOUR ASM DISK GROUP(S)
----------------------------------------

Set your ORACLE_SID to your ASM instance and create a new diskgroup.  Example:

  setenv ORACLE_SID +ASM
  sqlplus '/ as sysdba'
  SQL> startup nomount
  SQL> create diskgroup data disk '/dev/rdsk/c1t4d0s4';
  SQL> shutdown immediate
  SQL> startup mount


STEP 5: RESTORE DATABASES
-------------------------

1. Start instance using the local copy of your pfile from step 1.

  setenv ORACLE_SID DBSCOTT
  sqlplus '/ as sysdba'
  SQL> startup nomount pfile=init.ora

2. Use RMAN to restore the controlfiles and database.  Example:

  rman target /
  RMAN> restore controlfile from '/u03/backup/ctrlf_'; -- where  is the unique string generated by %U.
  RMAN> alter database mount;
  RMAN> restore database;
  RMAN> recover database;
  RMAN> alter database open resetlogs;

3. Connect to the ASM instance and get the controlfile name.  Example:

  setenv ORACLE_SID +ASM
  sqlplus '/ as sysdba'
  SQL> select name, alias_directory from v$asm_alias;

  Look for the controlfile name under the CONTROLFILE directory eg: Current.256.1

4. Edit the init.ora and change the control_files parameter to point to
   the one identified from the ASM v$asm_alias view.

5. Re-create the spfile.  Example:

  SQL> create spfile='+DATA/V10FJ/spfileV10FJ.ora'
       from pfile='/u03/app/oracle/product/10.1.0/dbs/pfile.out';

6. Shutdown and restart the instance to use the newly created spfile.

7. Repeart the "STEP 5" section for additional databases.

ORA-38760: This database instance failed to turn on flashback database


ORA-38760: This database instance failed to turn on flashback database


ORA-38760: This database instance failed to turn on flashback database
As per Oracle :

Error: ORA-38760 

Cause: Database flashback is on but this instance failed to start 
generating flashback data. Look in alert log for more specific 
errors. Action: Correct the error or turn off database flashback.


Solution A:

To Enable flashback:

1.Shutdown the database either in immediate or normal mode.

2.Mount the database.

3.Enable flashback. 

SQL>Alter database flashback on;

4.Open the database.

Sometimes even after solution A you are still getting errors:

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1.4798E+10 bytes
Fixed Size 2046472 bytes
Variable Size 671090168 bytes
Database Buffers 1.4110E+10 bytes
Redo Buffers 14729216 bytes
Database mounted.
SQL> alter database flashback off;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database

SQL> 

Then you should apply solution B.

Solution B:

1. Startup mount.

2. Check db_recovery_file_dest parameter and ensure this directory exists.

3. Check restore points:

select NAME,SCN,GUARANTEE_FLASHBACK_DATABASE,DATABASE_INCARNATION# 
from v$restore_point;

4. Drop restore point:

Drop restore point ;

5. alter database flashback off;

6. Shutdown immediate;

7. Startup

Tuesday, 15 May 2012

RMAN backup failing with RMAN-03009 / RMAN-10038

Applies to:

Oracle Server - Enterprise Edition - Version 10.2.0.1 and later

Symptoms:

Database backup with rman and NetBackup/Tape backup failing with errors:

input datafile file number=00010 name=E:\ORADATA\P0003W\SYMYXAUD01.DBF
channel t1: starting piece 1 at 15-MAY-12
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on t1 channel at 05/15/2012 07:50:04
RMAN-10038: database session for channel t1 terminated unexpectedly

Recovery Manager complete.

Cause:

Incompatible tape software being used to backup Oracle 10.2.0.1.0.
Solution:
 
There was an issue with the NetBackup/Tape Backup versions and Oracle database 10.2.0.1.

After upgrading to NetBackup 7.1, the backup is working with Oracle 10.2.0.1.
Contact the tape software vendor for further questions about compatibility with different Oracle versions,
the certification of Oracle database with third party tape software is performed by the tape software vendor.