Oracle Database 19c -- Modify the ADR Purging Policies

Problem

You need to modify the ADR purging policies for all applicable homes under the ADR base

Solution

Use the below script:

# Set desired values for the ADR Purging Policies in days
SHORTP_POLICY_DAYS=30
LONGP_POLICY_DAYS=30


SHORTP_POLICY_HOURS=$(($SHORTP_POLICY_DAYS * 24))
LONGP_POLICY_HOURS=$(($LONGP_POLICY_DAYS * 24))

for i in `adrci exec="show homes;" | tail -n +2`; do
   if adrci exec="set home $i; show control;" | grep -q "1 row fetched"; then
      adrci exec="set home $i; set control \(SHORTP_POLICY = $SHORTP_POLICY_HOURS, LONGP_POLICY = $LONGP_POLICY_HOURS\);"
      adrci exec="set home $i; show control;" |  awk '
         FNR==2 {print $0}
         FNR==3 {print $0}
         FNR==4 {printf "%20s %20s\n", $2, $3}
         FNR==5 {printf "%20s %20s\n", $2, $3}
         FNR==6 {printf "%20s %20s\n", $2, $3}
         END    {print "\n"}'
   fi
done