shell script to clean the oracle dump directories

find . -name \*.log -mtime +1 -exec ls {} \;
find . -name \*.log -mtime +1 -exec rm {} \;

#!/usr/bin/ksh
#

PATH=${PATH}:/usr/local/bin

# Check Syntax
if [ $# -ne 2 ]
then
echo "Syntax Error: clean_dumps.sh ."
exit 1
else
DAYS=$2
fi

. /var/opt/oracle/test10g.env

# Redirect output to log
exec 1>${ORACLE_DBA}/${ORACLE_SID}_clean_dumps.`date +%w` 2>&1

echo "Start: `date`"

find ${ORACLE_DBA}/${ORACLE_SID}/udump -name "*.trc" -mtime +${DAYS} -exec ls -l {} \;
find ${ORACLE_DBA}/${ORACLE_SID}/udump -name "*.trc" -mtime +${DAYS} -exec rm -f {} \;

find ${ORACLE_DBA}/${ORACLE_SID}/bdump -name "*.trc" -mtime +${DAYS} -exec ls -l {} \;
find ${ORACLE_DBA}/${ORACLE_SID}/bdump -name "*.trc" -mtime +${DAYS} -exec rm -f {} \;

find ${ORACLE_DBA}/${ORACLE_SID}/cdump -name "core_*" -mtime +${DAYS} -exec ls -l {} \;
find ${ORACLE_DBA}/${ORACLE_SID}/cdump -name "core_*" -mtime +${DAYS} -exec rm -f -r {} \;


echo "\nEnd: `date`"

removing oracle shared memory segment in solaris

kill the oracle processes
u@h:OFSWDC:w>ps -ef|grep $ORACLE_SID| grep -v grep|awk '{print $2}'|xargs -i kill -9 {}

view the shared memory still hold by oracle
u@h:OFSWDC:w>ipcs -pmb
IPC status from as of Wed Oct 31 18:25:42 EDT 2007
T ID KEY MODE OWNER GROUP SEGSZ CPID LPID
Shared Memory:
m 19456 0x27926f00 --rw-r----- oracle dba 1140858880 6163 4954

remove it -
u@h:OFSWDC:w>ipcrm -m 19456

unix shell script to take oracle cold backup

define 3 = &backup_directory
define 4 = &instance_name
define 5 = &user_dump_directory

Set Heading Off
Set Verify Off
Set FeedBack Off
Set LineSize 132
Set PageSize 1000
set termout off
Spool cold_back.sql

select 'connect /as sysdba' from dual;

Select 'Startup Force' || CHR(10) ||
'Shutdown Normal' From Dual;

select '! mkdir '||'&&3'||'/'||to_char(sysdate,'yyyyddmm') from dual
/


/* Data Files */
Select '!cp ' || File_Name || ' &&3'||'/'||to_char(sysdate,'yyyyddmm') || CHR(10) ||'!gzip ' || '&&3' ||'/'||to_char(sysdate,'yyyyddmm')|| '/' || SubStr(File_Name, InStr(File_Name, '/', -1)+1) From Sys.DBA_Data_Files
/

/* Redo Log Files */
Select '!cp ' || Member || ' &&3'||'/'||to_char(sysdate,'yyyyddmm') || CHR(10) || '!gzip ' || '&&3'||'/'||to_char(sysdate,'yyyyddmm') || '/' || SubStr(Member, InStr(Member, '/', -1)+1) From V$LogFile
/

/* Control Files */
Select '!cp ' || name || ' &&3'||'/'||to_char(sysdate,'yyyyddmm') From V$controlfile
/

/* Init and Config Files */
Select '!cp $ORACLE_HOME/dbs/init&&4' || '.ora &&3'||'/'||to_char(sysdate,'yyyyddmm') From Dual
/

alter session set tracefile_identifier=coldbackup
/

alter database backup controlfile to trace
/

select '!mv '||'&&5/*COLDBACKUP*' ||' &&3'||'/'||to_char(sysdate,'yyyyddmm') FROM dual
/

Select 'Startup' From Dual;
Select 'Exit' from dual;
set termout on
PROMPT " Run the output cold_back.sql script [sqlplus -s '/as sysdba' @cold_back.sql ]"

Spool Off

setting up nmo for grid control

execute the following as root:
cd $ORACLE_HOME (your OEM HOME directory)
chmod 6750 bin/nm?
chmod 700 bin/emdctl
chmod 700 bin/emagent
chown root bin/nm?

as oem user:
emctl stop agent
emctl start agent

run the root.sh under agent directory

ORA-10980 in trace file while creating materialized view?"

I was getting ORA-10980 in trace files.
After granting all the permission still was not able to create the materialized view. but somehow putting driving_site hint and placing the local table on the right most I was able to create the mview.

SELECT /*+ DRIVING_SITE(cpf) DRIVING_SITE(dr) DRIVING_SITE(txn) DRIVING_SITE(tuc) DRIVING_SITE(rc) DRIVING_SITE(ru) */
mdevice.mPLACE_ID,
mdevice.mMODEL_ID,
TRUNC( cpf.dATE_TIME ) AS TXN_DATE,
COUNT(*) AS NUMBER_TXNS
FROM
FINANCIAL@MLINK cpf,
tDEVICE_RULE@MLINK dr,
tTXN_RULE@MLINK txn,
tTXN_USAGE_CLASSIFICATION@MLINK tuc,
tRULE_CLASSIFICATION@MLINK rc,
tRULE_USAGE@MLINK ru,
MV_MTREVICES mDevice
WHERE
--------