This script calculates the size of all databases in the oratab file if no arguments are passed or the size of a specific database if an argument is passed.The first argument $1 is considered to be the instance name. All other arguments are ignored.


#! /bin/ksh

PROGRAM_NAME=`basename $0`
PROG_BIN_DIR=/users/oracle/local/prod/sh
PROG_LOG_DIR=/users/oracle/local/prod/logs
PATH=${PROG_BIN_DIR}:/users/oracle/product/10.2.0/db/bin:/usr/bin:/users/oracle/bin:/usr/local/bin:/sbin:/usr/sbin:/usr/ccs/bin:/usr/ucb:/usr/bin/X11:/usr/bin:/usr/proc/bin:/usr/ucb:/usr/local/bin
ORATABFILE=/etc/oratab
# If the first argument to this script is null, then calculate the size all databases in the oratab file
if [[ -z $1 ]]; then
# Get all SIDs from oratab file
for dbname in `sed -e '/^[ ]*$/d' -e '/^[ ]*\#/d' -e '/^[ ]*\*/d' ${ORATABFILE}
cut -d":" -f1
sed 's/ //'
grep -v '\+ASM'` ; do
RUNDATE=`date '+%m%d%Y_%H%M%S'`
# Continue with next SID when instance is not running
if [[ `ps -ef
grep "ora_smon_${dbname}"
grep -v 'grep'
wc -l
awk '{ print $1 }'` -eq 0 ]]; then
echo "Database Name ${dbname} is not up and running"
continue
fi
# Change the Oracle environment for the current dbname
ORACLE_SID=${dbname}
ORAENV_ASK=NO
. oraenv
ORAENV_ASK=YES
PATH=${ORACLE_HOME}/bin:${PATH}
LIBPATH=${ORACLE_HOME}/lib


echo "Database Size of ${dbname} = \c"
sqlplus -s / <<-EOF
WHENEVER SQLERROR EXIT FAILURE
SET PAGES 0 HEA OFF FEED OFF VER OFF
--
SELECT TO_CHAR(ROUND(SUM(bytes)/1024/1024/1024, 2))
' GB'
FROM
(
SELECT SUM(bytes) bytes
FROM dba_data_files
UNION
SELECT SUM(bytes) bytes
FROM dba_temp_files
)
/
EXIT
EOF
RETCODE=$?



if [[ ${RETCODE} != 0 ]]; then
echo "FAILURE : Calculating Database Size of Database ${dbname} On ${RUNDATE}"
continue
fi
done

# If the first argument to this script is not null, then calculate the size of the database specified as the argument
else
dbname=`echo $1
tr "[A-Z]" "[a-z]"`
RUNDATE=`date '+%m%d%Y_%H%M%S'`


# Exit when dbname does not exist in oratab file
# This is required because oraenv file is being called later
# and if dbname is missing from oratab file, the oraenv script
# will prompt for ORACLE_HOME




if [[ `grep "^${dbname}:" ${ORATABFILE}
wc -l
awk '{ print $1 }'` -eq 0 ]]; then
echo "Database Name ${dbname} does not exist in ${ORATABFILE} file"
exit 16
fi


# Exit when instance is not running
if [[ `ps -ef
grep "ora_smon_${dbname}"
grep -v 'grep'
wc -l
awk '{ print $1 }'` -eq 0 ]]; then
echo "Database Name ${dbname} is not up and running"
exit 16
fi






# Change the Oracle environment for the current dbname
ORACLE_SID=${dbname}
ORAENV_ASK=NO
. oraenv
ORAENV_ASK=YES
PATH=${ORACLE_HOME}/bin:${PATH}
LIBPATH=${ORACLE_HOME}/lib




echo "Database Size of ${dbname} = \c"
sqlplus -s / <<-EOF
WHENEVER SQLERROR EXIT FAILURE
SET PAGES 0 HEA OFF FEED OFF VER OFF
--
SELECT TO_CHAR(ROUND(SUM(bytes)/1024/1024/1024, 2))
' GB'
FROM
(
SELECT SUM(bytes) bytes
FROM dba_data_files
UNION
SELECT SUM(bytes) bytes
FROM dba_temp_files
)
/
EXIT
EOF
RETCODE=$?






if [[ ${RETCODE} != 0 ]]; then
echo "FAILURE : Calculating Database Size of Database ${dbname} On ${RUNDATE}"
fi
fi

Apply or Delete Apply errors and Restart Stream Apply process

Step 1: Check Apply status



select apply_name,status from dba_apply;


APPLY_NAME                     STATUS
------------------------------ --------
TARGET_APPLY                   ABORTED


If status is aborted then check dba_apply_error view


Step 2: Check Apply error

 select local_transaction_id,error_number,error_message from dba_apply_error;


LOCAL_TRANSACTION_ID  ERROR_NUMBER ERROR_MESSAGE
-------------------- ------------- ------------------------
9.59.1031 959    ORA-00959: tablespace 'CATTBS' does not exist


Step 3: Print Apply error statement and LCRs

SQL> select local_transaction_id from dba_apply_error;


If the local transaction identifier is 1.17.2485, then enter the following:


SQL> set serveroutput on size unlimited
SQL> spool error.log
SQL> EXEC print_transaction('1.17.2485');
SQL> spool error.log



Step 4: Re-execute apply error

-- To Execute specific transaction

exec DBMS_APPLY_ADM.EXECUTE_ERROR ('');

-- To Execute all errors:

exec dbms_apply_Adm.execute_all_errors;


Step 5: Delete the apply errors

-- Check the errors in the error queue:

select apply_name, LOCAL_TRANSACTION_ID, ERROR_MESSAGE from dba_apply_error;

-- To delete specific transaction:

exec DBMS_APPLY_ADM.DELETE_ERROR('');

-- To delete all errors:

exec DBMS_APPLY_ADM.DELETE_ALL_ERRORS('');


Step 6: Check Apply status


select apply_name,status from dba_apply;


APPLY_NAME                      STATUS
------------------------------ --------
TARGET_APPLY                    ABORTED