Oracle DB Admin – Page 19

Protected: Oracle : Script for sending email for Invalid Objects

6 years ago by Vinod SiramOracle DB Admin

There is no excerpt because this is a protected post.

Protected: Oracle : Script for Sending email for Blocking Sessions

6 years ago by Vinod SiramOracle DB Admin

There is no excerpt because this is a protected post.

How to move or rename Oracle Home

6 years ago by Binnary Ajay KumarOracle DB Admin

How to move or rename Oracle Home If you want to rename Oracle Home you usually need to: 1. uninstall existing Oracle Home 2. reinstall Oracle software to the right directory that will be the new Oracle Home. You cannot just move Oracle Home because… Read More

Oracle : Scheduling crontab to send EMAIL in Unix

6 years ago by Vinod SiramOracle DB Admin

Oracle : Scheduling crontab to send EMAIL in Unix $ vi test.bash

#!/bin/bash
#Script to perform testing
## Environment variables
TODAY=`date +%d-%b-%Y-%H:%M:%S`
export ORACLE_HOME='/u01/app/oracle/product/12102'
export ORACLE_SID='ORCL'
export ORACLE_BASE='/u01/app/oracle'
export PATH=$PATH:${ORACLE_HOME}/bin
export MAILIST='ktexperts@gmail.com'
export LOG='/tmp/test_mig.log'

#Function to trigger email
function mailog
{
cat ${LOG} | mailx -s "${TODAY} - TEST Mail" ${MAILIST}
}

#Function step1
function step1
{
echo " " >> $LOG
echo "Started Step 1" >> $LOG
echo " " >> $LOG
echo "Test email " >> $LOG
sqlplus -s /nolog <<START >>$LOG
Connect / as sysdba
set serveroutput on
set timing on
prompt Todays date
select sysdate from dual;
START
echo " " >> $LOG
echo "----- END OF Step1 ------" >> $LOG
}

#Script execution is here
> $LOG
if [ "$#" -eq 0 ]
then
step1
mailog
else
$1
mailog
fi

#!/bin/bash

#Script to perform testing

## Environment variables

TODAY=`date +%d-%b-%Y-%H:%M:%S`

export ORACLE_HOME='/u01/app/oracle/product/12102'

export ORACLE_SID='ORCL'

export ORACLE_BASE='/u01/app/oracle'

export PATH=$PATH:${ORACLE_HOME}/bin

export MAILIST='ktexperts@gmail.com'

export LOG='/tmp/test_mig.log'

#Function to trigger email

function mailog

{

cat ${LOG} | mailx -s "${TODAY} - TEST Mail" ${MAILIST}

}

#Function step1

function step1

{

echo " " >> $LOG

echo "Started Step 1" >> $LOG

echo " " >> $LOG

echo "Test email " >> $LOG

sqlplus -s /nolog <<START >>$LOG

Connect / as sysdba

set serveroutput on

set timing on

prompt Todays date

select sysdate from dual;

START

echo " " >> $LOG

echo "----- END OF Step1 ------" >> $LOG

}

#Script execution is here

> $LOG

if [ "$#" -eq 0 ]

then

step1

mailog

else

mailog

Crontab entry

00 02 * * * /local/home/oracle/test.bash 1>/local/home/oracle/test.cronlog 2>/local/home/oracle/test.cronerr

1	00 02 * * * /local/home/oracle/test.bash 1>/local/home/oracle/test.cronlog 2>/local/home/oracle/test.cronerr

Updated Script.

#!/bin/bash

#Script to perform testing

## Environment variables
TODAY=`date +%d-%b-%Y-%H:%M:%S`
export ORACLE_HOME='/u01/app/oracle/product/12.1.0.2'
export ORACLE_SID='prod'
export ORACLE_BASE='/u01/app/oracle'
export PATH=$PATH:${ORACLE_HOME}/bin
export MAILIST='ktexperts@gmail.com'
export LOG='/tmp/test_mig.log'

#Function to trigger email
function mailog
{
cat ${LOG} | mailx -s "${TODAY} - TEST Mail" ${MAILIST}
}

#Function step1
function step1
{
echo " " >> $LOG
echo "Started Step 1" >> $LOG
echo " " >> $LOG
echo "Test email " >> $LOG
sqlplus vinod/vinod <<START >>$LOG
prompt updating records in EMP table
select * from emp where deptno=20;
prompt update emp set sal=sal+500 WHERE deptno=20;
update emp set sal=sal+500 WHERE deptno=20;
prompt select * from emp where deptno=20;
select * from emp;
START
echo " " >> $LOG
echo "----- END OF Step1 ------" >> $LOG
}


#Script execution is here
> $LOG
if [ "$#" -eq 0 ]
then
step1
mailog
else
$1
mailog
fi

#!/bin/bash

#Script to perform testing

## Environment variables

TODAY=`date +%d-%b-%Y-%H:%M:%S`

export ORACLE_HOME='/u01/app/oracle/product/12.1.0.2'

export ORACLE_SID='prod'

export ORACLE_BASE='/u01/app/oracle'

export PATH=$PATH:${ORACLE_HOME}/bin

export MAILIST='ktexperts@gmail.com'

export LOG='/tmp/test_mig.log'

#Function to trigger email

function mailog

{

cat ${LOG} | mailx -s "${TODAY} - TEST Mail" ${MAILIST}

}

#Function step1

function step1

{

echo " " >> $LOG

echo "Started Step 1" >> $LOG

echo " " >> $LOG

echo "Test email " >> $LOG

sqlplus vinod/vinod <<START >>$LOG

prompt updating records in EMP table

select * from emp where deptno=20;

prompt update emp set sal=sal+500 WHERE deptno=20;

update emp set sal=sal+500 WHERE deptno=20;

prompt select * from emp where deptno=20;

select * from emp;

START

echo " " >> $LOG

echo "----- END OF Step1 ------" >> $LOG

}

#Script execution is here

> $LOG

if [ "$#" -eq 0 ]

then

step1

mailog

else

mailog

Crontab entry

02 * * * * /home/oracle/test.bash  1>/home/oracle/test.cronlog 2>/home/oracle/test.cronerr

1	02 * * * * /home/oracle/test.bash 1>/home/oracle/test.cronlog 2>/home/oracle/test.cronerr

IMPLEMENTING REPLICATION CONCEPT USING MATERIALIZED VIEWS (PART -02)

6 years ago by Binnary Ajay KumarOracle DB Admin

IMPLEMENTING REPLICATION CONCEPT USING MATERIALIZED VIEWS (PART -02) IMPLEMENTING REPLICATION CONCEPT USING MATERIALIZED VIEWS. MATERIALIZED VIEW WITH REFRESH FAST : Now that we know how materialized view logs track changes to base tables we can use them to perform fast materialized view refreshes, i.e. refreshes where… Read More

IMPLEMENTING REPLICATION CONCEPT USING MATERIALIZED VIEWS.

6 years ago by Binnary Ajay KumarOracle DB Admin

IMPLEMENTING REPLICATION CONCEPT USING MATERIALIZED VIEWS. Materialized views are schema objects that can be used to summarize, compute, replicate, and distribute data. They are suitable in various computing environments such as data warehousing, decision support, and distributed or mobile computing. In data warehouses, materialized… Read More

RMAN Enhancements in Oracle 12c

6 years ago by Binnary Ajay KumarOracle 12C New Features

Dear Readers, In this article, we will see the following RMAN Enhancements in Oracle 12c. 1.SQL Interface Improvements In Oracle 12c, you can run SQL commands in RMAN without preceding the command with the SQL keyword. You also no longer need to enclose the SQL… Read More

DDL Wait Option in Oracle.

6 years ago by Binnary Ajay KumarOracle DB Admin

DDL Wait Option in Oracle. A DBA is trying to alter the table called SALES to add a column, TAX_CODE. he issues the following SQL statement:

SQL> alter table sales add (tax_code varchar2 (10));

1	SQL> alter table sales add (tax_code varchar2 (10));

But instead of getting something like “Table altered”, he gets:

SQL>>Alter table sales add (tax_code varchar2 (10))
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

SQL>>Alter table sales add (tax_code varchar2 (10))

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

The error message says it all: the… Read More

Fine-Grained Auditing (FGA)

6 years ago by Binnary Ajay KumarOracle Auditing

Fine-Grained Auditing (FGA). Fine grained auditing extends Oracle standard auditing capabilities by allowing the user to audit actions based on user-defined predicates. It is independant of the AUDIT_TRAILparameter setting and all audit records are stored in the FGA_LOG$ table, rather than the AUD$ table. The following example illustrates how fine… Read More

Auditing in Oracle

6 years ago by Binnary Ajay KumarOracle Auditing

Auditing in Oracle. Auditing is used to track the occurrence of SQL statements in subsequent user sessions. In this article will see how can dba track all types of sql operations (select,insert,update,delete drop,create and alter) of particular user. Auditing is a default feature of the… Read More