adrci set log purging

How do I use adrci to set log purging. I really appreciate your help. Thanks in advance for your time and help.
hussainkhan22Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hussainkhan22Author Commented:
I need an automatic script for purging log files using adrci.
slightwv (䄆 Netminder) Commented:
If there is not built-in function to get this added to the default maintenance window then, put the commands in a script file and schedule it?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

hussainkhan22Author Commented:
its oracle11g database on linux server. Please provide me the automatic script for purging using adrci. Thanks in advance.
hussainkhan22Author Commented:
$ uname -a
SunOS rdsun02 5.10 Generic_144488-06 sun4u sparc SUNW,Ultra-80

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for Solaris: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production


$ cd /tmp

$ vi adrci.sql
 
#!/usr/bin/ksh
#
#
#
# Description
# Shell script to interact with the ADR through ADRCI and purge ADR contents
#
# Tunables
###
LOCKFILE=/tmp/mor_adrci_purge_.lck
###

######
# Start Of Functions
#
# tidyup . common fuction called if error has occured
tidyup () {
        rm -f ${LOCKFILE}
        echo "ERROR: Purge aborted at `date` with exit code ${ERR}"
        exit ${ERR}
}

######
# End Of Functions

### Main Program

# Check user is oracle
USERID=`/usr/bin/id -u -nr`
if [ $? -ne 0 ]
then
        echo "ERROR: unable to determine uid"
        exit 99
fi
if [ "${USERID}" != "oracle" ]
then
        echo "ERROR: This script must be run as oracle"
        exit 98
fi

echo "INFO: Purge started at `date`"

# Check if lockfile exists
if [ -f ${LOCKFILE} ]
then
        echo "ERROR: Lock file already exists"
        echo "       Purge already active or incorrectly terminated"
        echo "       If you are sure tidy isn.t active, please remove " 
        echo "       ${LOCKFILE}"
        exit 97
fi

# Create lock file
touch ${LOCKFILE} 2>/dev/null
if [ $? -ne 0 ]
then
        echo "ERROR: Unable to create lock file"
        exit 96
fi

# Purge ADR contents
echo "INFO: adrci purge started at `date`"
/appl/oracle/product/v111/bin/adrci exec="show homes"|grep -v : | while rea
d file_line
do
  echo "INFO: adrci purging diagnostic destination " $file_line
  echo "INFO: purging ALERT older than 90 days .."
  /appl/oracle/product/v111/bin/adrci exec="set homepath $file_line;purge -
age 129600 -type ALERT"
  echo "INFO: purging INCIDENT older than 30 days .."
  /appl/oracle/product/v111/bin/adrci exec="set homepath $file_line;purge -
age 43200 -type INCIDENT"
  echo "INFO: purging TRACE older than 30 days .."
  /appl/oracle/product/v111/bin/adrci exec="set homepath $file_line;purge -
age 43200 -type TRACE"
  echo "INFO: purging CDUMP older than 30 days .."
  /appl/oracle/product/v111/bin/adrci exec="set homepath $file_line;purge -
age 43200 -type CDUMP"
  echo "INFO: purging HM older than 30 days .."
  /appl/oracle/product/v111/bin/adrci exec="set homepath $file_line;purge -
age 43200 -type HM"
  echo "" 
  echo "" 
done

echo
echo "INFO: adrci purge finished at `date`"

# All completed
rm -f ${LOCKFILE}
echo "SUCC: Purge completed successfully at `date`"

exit 0
----------------------------------------------------------------------------------------------
Getting the below errors

SQL> @/tmp/adrci.sql
SP2-0734: unknown command beginning "!/usr/bin/..." - rest of line ignored.
SP2-0734: unknown command beginning "Descriptio..." - rest of line ignored.
SP2-0734: unknown command beginning "Shell scri..." - rest of line ignored.
SP2-0042: unknown command "Tunables" - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0042: unknown command "##" - rest of line ignored.
SP2-0734: unknown command beginning "LOCKFILE=/..." - rest of line ignored.
SP2-0042: unknown command "##" - rest of line ignored.
SP2-0042: unknown command "#####" - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0310: unable to open file "Of.sql"
SP2-0734: unknown command beginning "tidyup . c..." - rest of line ignored.
SP2-0734: unknown command beginning "tidyup () ..." - rest of line ignored.
SP2-0734: unknown command beginning "rm -f ${LO..." - rest of line ignored.
SP2-0734: unknown command beginning "echo "ERRO..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
Usage: { EXIT | QUIT }  [ SUCCESS | FAILURE | WARNING | n |
       <variable> | :<bindvariable> ]  [ COMMIT | ROLLBACK ]
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production




slightwv (䄆 Netminder) Commented:
What you posted is a UNIX shell script not a SQLplus script.

You need to execute it from a UNIX command line.
hussainkhan22Author Commented:
how do I execute from unix please let me know?
hussainkhan22Author Commented:
I have saved that file as adrci.sh
$./adrci.sh
/usr/bin/id : illegal option -- u
Usage : id [-ap] [user]
ERROR: unable to determine uid
hussainkhan22Author Commented:
Please help me its urgent, I need to test the script and submit by end of day today
hussainkhan22Author Commented:
$ uname -a
SunOS rdsun02 5.10 Generic_144488-06 sun4u sparc SUNW,Ultra-80

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for Solaris: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production


$ cd /tmp

$ vi adrci.sh
 
#!/usr/bin/ksh
#
#
#
# Description
# Shell script to interact with the ADR through ADRCI and purge ADR contents
#
# Tunables
###
LOCKFILE=/tmp/mor_adrci_purge_.lck
###

######
# Start Of Functions
#
# tidyup . common fuction called if error has occured
tidyup () {
        rm -f ${LOCKFILE}
        echo "ERROR: Purge aborted at `date` with exit code ${ERR}"
        exit ${ERR}
}

######
# End Of Functions

### Main Program

# Check user is oracle
USERID=`/usr/bin/id -u -nr`
if [ $? -ne 0 ]
then
        echo "ERROR: unable to determine uid"
        exit 99
fi
if [ "${USERID}" != "oracle" ]
then
        echo "ERROR: This script must be run as oracle"
        exit 98
fi

echo "INFO: Purge started at `date`"

# Check if lockfile exists
if [ -f ${LOCKFILE} ]
then
        echo "ERROR: Lock file already exists"
        echo "       Purge already active or incorrectly terminated"
        echo "       If you are sure tidy isn.t active, please remove " 
        echo "       ${LOCKFILE}"
        exit 97
fi

# Create lock file
touch ${LOCKFILE} 2>/dev/null
if [ $? -ne 0 ]
then
        echo "ERROR: Unable to create lock file"
        exit 96
fi

# Purge ADR contents
echo "INFO: adrci purge started at `date`"
/appl/oracle/product/v111/bin/adrci exec="show homes"|grep -v : | while rea
d file_line
do
  echo "INFO: adrci purging diagnostic destination " $file_line
  echo "INFO: purging ALERT older than 90 days .."
  /appl/oracle/product/v111/bin/adrci exec="set homepath $file_line;purge -
age 129600 -type ALERT"
  echo "INFO: purging INCIDENT older than 30 days .."
  /appl/oracle/product/v111/bin/adrci exec="set homepath $file_line;purge -
age 43200 -type INCIDENT"
  echo "INFO: purging TRACE older than 30 days .."
  /appl/oracle/product/v111/bin/adrci exec="set homepath $file_line;purge -
age 43200 -type TRACE"
  echo "INFO: purging CDUMP older than 30 days .."
  /appl/oracle/product/v111/bin/adrci exec="set homepath $file_line;purge -
age 43200 -type CDUMP"
  echo "INFO: purging HM older than 30 days .."
  /appl/oracle/product/v111/bin/adrci exec="set homepath $file_line;purge -
age 43200 -type HM"
  echo "" 
  echo "" 
done

echo
echo "INFO: adrci purge finished at `date`"

# All completed
rm -f ${LOCKFILE}
echo "SUCC: Purge completed successfully at `date`"

exit 0
---------------------------------------------------------------------------------

$./adrci.sh

/usr/bin/id : illegal option -- u
Usage : id [-ap] [user]
ERROR: unable to determine uid
slightwv (䄆 Netminder) Commented:
Sorry I can't do much debuging. I'm not on unix.  

From the error it looks like I doesn't like the command:
USERID=`/usr/bin/id -u -nr`

hussainkhan22Author Commented:
its working fine now but how do I set this script for multiple servers and multiple databases to run automatically
slightwv (䄆 Netminder) Commented:
Google: cron
hussainkhan22Author Commented:
How can we just do set_control and just set it automatically without calling this script above. Please help me.
slightwv (䄆 Netminder) Commented:
I'm not familiar with the term set_control.  It may be a unix specific term.  I can try to help if you provide the context for that term.
hussainkhan22Author Commented:
My team lead requesting me to use set_control parameter with adrci to purge log file automatically in the back ground without calling the above script. I really appreciate if anyone could help me in it. Thanks in advance
slightwv (䄆 Netminder) Commented:
Oh, no underscore:  set control.

Did you not read the links I posted in the first post?

I suggest you check the second one.  It is anchored to the set control syntax.
hussainkhan22Author Commented:
I have set control parameter its executed fine but still its not purging files.


When I am executing
$ adrci exec='set homepath diag/rdbms/<SID>/<SID>;set control \(SHORTP_POLICY=144, LONGP_POLICY=144\)"

its not purging files.

I have checked
Adrci> show control

Its showing last_autoprg_time as 4/2/2011 instead of todays time 4/6/2011.

do we need to set anything else for auto purge files, we planning to set this parameter so that it will auto purge automatically instead of running manually or as a script.
I really appreciate your help. thanks for your time and help in advance
slightwv (䄆 Netminder) Commented:
I'm far from an expert on adrci so a lot of what I'm providing is from my interpretation of the docs.

From the docs:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28319/adrci.htm

SHORTP_POLICY Number of hours after which to purge ADR contents that have a short life. Default is 720 (30 days).
LONGP_POLICY Number of hours after which to purge ADR contents that have a long life. Default is 8760 (365 days).


If I did my math right 144 hours is 6 days and 6 days from the last run on 4/2/2011 would be 4/8/2011.  You are just setting the purge time.

What makes you think it should show today (4/6/2011)?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hussainkhan22Author Commented:
I need a script or command for adrci to automatically purge files in background, no need of running adrci or purge command everytime, I just want to run once and it must automatically purge files now and in future automatically
slightwv (䄆 Netminder) Commented:
>>I just want to run once and it must automatically purge files now and in future automatically

and?  If I read the online docs right, you now have 'automatic' purging every 6 days.
hussainkhan22Author Commented:
Yes even after setting automatic setting after 6 days still it's not purging automatically. I need to issue purge from adrci than it's purging files older than 6 days. Can you please let me know how to purge automatically
slightwv (䄆 Netminder) Commented:
What makes you think it isn't purging?  From the links above, the last 'purge' was on the 2nd.  The next 'purge' should be on the 8th.
hussainkhan22Author Commented:
I am sorry for very late response it didnt purge on april 8 also I have to manually use purge command to purge files which were set using set control shotp_policy and longp_policy
slightwv (䄆 Netminder) Commented:
At this point I would suggest you contact Oracle Support and ask why it is not automatically purging.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.