crontab - scheduling a shell script (for oracle export)

Cron is a time-based job scheduler in Unix-like computer operating systems. 'cron' is short for Chronograph. Cron enables users to schedule jobs (commands or shell scripts) to run automatically at a certain time or date.

> connected with oracle user.
[oracle@oraclelinux bin]$ pwd
/bin

crontab -e - opens the user's crontab file for viewing/editing

crontab -l - simply lists the crontab file's contents for the user. Think of it as a "cat" function for the crontab.

-------------------------------
[oracle@oraclelinux bin]$ crontab -e

SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=oracle
HOME=/
*/5 * * * * sh /home/oracle/rakesh.sh

-----------------------------------------
The script will execute every five minute.

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

--------------------------------------------------
rakesh.sh shell script :
--------------------------------------------------
#!bin/bash
#PATH=$PATH:$HOME/bin;export PATH
unset USERNAME
#DISPLAY=10.142.200.141:0.0; export DISPLAY
#Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1; export ORACLE_HOME
ORACLE_SID=jsildb; export ORACLE_SID
ORACLE_HOME_LISTENER=LISTENER; export ORACLE_HOME_LISTENER
ORATAB=/etc/oratab; export ORATAB
#ORACLE_TERM=xterm; export ORACLE_TERM
export PATH=/u01/app/oracle/product/10.2.0/db_1/bin:$PATH
export PATH=/usr/sbin/:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:lib:usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
#LD_ASSUME_KERNEL=2.4.1; export LD_ASSUME_KERNEL
export DATE=$(date +"%m_%d_%y_%H:%M:%S_%p")

exp rakesh/rakesh@jsildb file=/home/oracle/erp_backups/rakesh_$DATE.dmp log=/home/oracle/erp_backups/rakesh_$DATE.log statistics=none

export bash script in linux with timestamp...

I have created export bash script in linux operating system to export schema and attached time of export with dump & log file..

#!bin/bash
export DATE=$(date +"%m_%d_%y_%H:%M:%S_%p")
/u01/app/oracle/product/10.2.0/db_1/bin/exp rakesh/rakesh@rockdb file=/home/oracle/backups/rakesh$DATE.dmp log=/home/oracle/backups/rakesh$DATE.log statistics=none

ORA-00031: session marked for kill

Today, i was trying to kill a session as explained in
http://rakeshocp.blogspot.com/2009/04/kill-session.html but i was getting error "ORA-00031: session marked for kill" i got to know that we need to kill an OS session then. I wanted to kill "hassan's" session.
select spid, osuser, s.program from v$process p, v$session s where p.addr=s.paddr;
SPID OSUSER PROGRAM
------------ ------------------------------ ----------------
4844 rakesh.kumar
7168 rakesh.kumar plsqldev.exe
6012 Hassan plsqldev.exe


C:\Documents and Settings\Administrator>orakill icpora 6012
Kill of thread id 6012 in instance icpora successfully signalled.
C:\Documents and Settings\Administrator>

Thanks to the Don Burleson
http://www.dba-oracle.com/t_kill_process_windows.htm

When your query takes too long

What to do when your query is too slow? Check the link below for details:

http://forums.oracle.com/forums/thread.jspa?threadID=501834&tstart=0

COLD BACKUP batch file

We have scheduled a Cold backup of DR site: check the steps below
create a coldbackup.bat
-----------------------------
cd D:\oracle\product\10.2.0\db_1\BIN
sqlplus "sys/rock@rockstd as sysdba" @D:\COLDBACKUP_WEEKLY\coldbackup.sql
-----------------------------
steps for creating batch file: http://rakeshocp.blogspot.com/2007/09/batch-file-scripts.html
-----------------------------
coldbackup.sql
-----------------------------
ALTER DATABASE recover managed standby database cancel;
SHUTDOWN IMMEDIATE;
host MD F:\ROCKSTD_Coldbackup\"%DATE:/=_%"
host MD F:\ROCKSTD_Coldbackup\"%DATE:/=_%"\DATAFILES
host MD F:\ROCKSTD_Coldbackup\"%DATE:/=_%"\CONTROLFILES
host MD F:\ROCKSTD_Coldbackup\"%DATE:/=_%"\REDOLOGS
host MD F:\ROCKSTD_Coldbackup\"%DATE:/=_%"\ARCHIVELOGS
host MD F:\ROCKSTD_Coldbackup\"%DATE:/=_%"\PARAMETERFILES
host copy D:\oracle\product\10.2.0\db_1\database\INITrockstd.ORA F:\ROCKSTD_Coldbackup\"%DATE:/=_%"\PARAMETERFILES;
host copy E:\DATAFILES\*.DBF F:\ROCKSTD_Coldbackup\"%DATE:/=_%"\DATAFILES;
host copy E:\REDOLOGS\*.LOG F:\ROCKSTD_Coldbackup\"%DATE:/=_%"\REDOLOGS;
host copy E:\CONTROLFILES\*.CTL F:\ROCKSTD_Coldbackup\"%DATE:/=_%"\CONTROLFILES;
host copy E:\ARCHIVES\*.ARC F:\ROCKSTD_Coldbackup\"%DATE:/=_%\ARCHIVELOGS;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
exit;
exit

Failover Steps

Failover:
A failover is when the primary database is unavailable. Failover is performed only in the event of a catastrophic failure of the primary database, and the failover results in a transition of a standby database to the primary role. The database administrator can configure Data Guard to ensure no data loss.

We performed a failover in our Disastor Recovery scenario. Where the primary database was taken offline (as unavialble), and standby database was acticated as Primary Database.

The Standby database will now be activated as the new Primary database.

SQL> conn sys/****@rockstd as sysdba
SQL> alter database recover managed standby database finish force;
SQL> alter database commit to switchover to primary;
SQL> alter database open;

Switchover Steps

Once the standby database is setup using Data Guard and is configured properly, we should test switchover and failover scenarios to be better prepared for a real life disaster situation.

A switchover allows the primary database to switch roles with its standby database. There is no data loss during a switchover. We can switch back to the original Primary database later by performing another switchover. A switchover is performed on a database when the need arises to upgrade or change configuration settings on the Primary database. The Standby database can temporarily be used as the Primary database for zero downtime while the actual Primary database is being upgraded or changed.

Switchover Configuration:
--------------------------
Database Role---------Service-----
Primary-------------ROCKPRIM
Standby-------------ROCKSTD

Before Switchover:
--------------------------

1. Verify the primary database instance is open and the standby database instance is mounted.
2. Verify there are no active users connected to the databases, if there are active sessions then kill active sessions.
3. Make sure the last redo data transmitted from the Primary database was applied on the standby database. Issue the following commands on Primary database and Standby database to find out:

select sequence#, applied from v$archvied_log; or select max(sequence#)
from v$archived_log where applied = ‘YES’;

4. Make sure that log_archive_dest_2 entry is specified in both databases.

Switchover Steps:
------------------
1. Initiate the switchover on the primary database ROCKPRIM:

SQL> conn sys/****@ROCKPRIM as sysdba
SQL> alter database commit to switchover to physical standby with session shutdown;

2. After step 1 finishes, Switch the original physical standby database ROCKSTD to primary role:

SQL> conn sys/****@ROCKSTD as sysdba
SQL> alter database commit to switchover to primary;

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY

3. Immediately after issuing command in step 2. Shut down and restart the former primary instance ROCKPRIM:

SQL> conn sys/****@ROCKPRIM as sysdba
SQL> shutdown immediate;
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;
SQL> select database_role from v$database;

DATABASE_ROLE
------------------------------------------------
PHYSICAL STANDBY


* Connect at new primary database ROCKSTD, and perform a SWITCH LOGFILE to start sending redo data to the standby database ROCK.