Tuning the Database Parameters

Tune the database parameters:

1. Connect with the SYS user account.

2. Execute the following SQL syntax in SQL*Plus:

   SQL> alter system set distributed_lock_timeout = 1400 scope = spfile; 
   alter system set Processes = 1500 scope = spfile; 
   alter system set db_securefile = ALWAYS;
   

3. To achieve better throughput, resize the redo log to be 2 GB.

4. Create the data files for the Oracle SOA schema with the initial size equal to the maximum size allowed. Otherwise, data source-related errors are reported. For example:

   SQLRecoverableException: IO Error: Socket read timed out
   

5. If you turn on the archive log for the database, the system can run out of connections for the SOADataSource. Its Maximum Capacity is set to 300 when the archive log mode is off. As a workaround, set the following parameters:

   1. Set the Maximum Capacity for SOADataSource to 400.

   2. Reduce the counts for the following worker managers:

      • Set the count for SOAIncomingRequests_maxThreads to 60.

      • Set the count for SOAInternalProcessing_maxThreads to 150.

6. Remove the expired archive files to prevent a disk full error.

   1. Connect with the SYS user account.

   2. Execute the following SQL syntax in SQL*Plus:

      SQL> alter system set db_flashback_retention_target = 45 scope=both; restart DB 
      

   3. Run the delArch.sh script shown below to periodically delete the expired archive log files.

      #!/bin/sh
      #######################################################
      ##                                                   ##
      ##        Purge Database Archive Logs                ##
      ##                                                   ##
      #######################################################
      source /home/oracle/.bashrc
      test -z ${ORACLE_HOME} && echo "Please set ORACLE_HOME first" &&  
      exit 1;
      test ! -d ${ORACLE_HOME} && echo "Please make sure you have set ORACLE_HOME correctly: 
      ${ORACLE_HOME}" && exit 1;
      
      #delete ${1} archivelog until time 'SYSDATE-1/(24*6)';
      function rmArch(){
      ${ORACLE_HOME}/bin/rman target / <<EOF
      crosscheck archivelog all;
      delete ${1} archivelog until time 'SYSDATE-1/(24*6)';
      YES
      delete backup;
      YES
      delete datafilecopy all;
      YES
      exit
      EOF
      }
      
      totalcount=0
      
      #interval=${1:-1800}
      interval=${1:-300}
      while [ : ]
      do
      rmArch $2
      ts=$(date)
      let "totalcount=totalcount+1"
      echo ""
      echo ""
      echo "==================================="
      echo "==                               =="
      echo "==        SUMMARY                =="
      echo "==                               =="
      echo "==================================="
      echo ""
      echo ""
      tname='v$asm_diskgroup';
      tname2='v$recovery_file_dest';
      ${ORACLE_HOME}/bin/sqlplus -s  sys/welcome1 as sysdba <<EOF
      
      set feedback 0
      set serveroutput on
      execute dbms_output.put_line('Disk Group space usage (In GigaByte)');
      col total format 999,999,999.00
      col available format 999,999,999.00
      SELECT ROUND(total_mb / 1024) "TOTAL", ROUND(free_mb / 1024 ) "AVAILABLE" FROM ${tname} ;
      
      execute dbms_output.put_line('Archive Log space usage (In Gigabyte)');
      
      col space_total format 999,999,999.00
      col prc_used format 999,999,999.00
      SELECT ROUND(SPACE_LIMIT / (1024*1024*1024)) SPACE_total,ROUND(((SPACE_USED / (1024*1024*1024)) * 100) / (SPACE_LIMIT / (1024*1024*1024)), 2) PRC_USED FROM ${tname2};
      EOF
      echo "TotalCount: $totalcount"
      echo "Last run at $ts"
      echo "Will start another run in $interval seconds"
      sleep $interval
      
      done