Oracle 12.1 upgrade

Remote Database Administration, Monitoring and Support

  • SATS Remote DBAis dedicated to optimizing database uptime and performance, and to reducing the cost of IT ownership. Our Like Clockwork RDBA service saves you time, recourses, continuity, and helps you to ally IT tasks with the business goals of your organization. Many other companies chose SATS for our dependability, cost-effectiveness, and expert technological support. SATS RDBA advantages help our customers achieve costs savings of up to 75% while increasing the quality of DBA coverage.
  • SATS Remote DBA could be a one stop service provider for all this platforms:
  • Oracle, PostgreSQL, Hadoop, MS SQL, MySQL, Mongo DB

For specific inquiries please contact SATS RDBA Services or call 650 889-5533.

PUTTING ORACLE DATABASE BACK INTO MANUAL CONTROL MODE

PROBLEM CASE

With recent versions of Oracle software (starting back from 10.1 and all the way to current 11.2), Oracle delivers more and more automation in different areas of database techology. While the general idea is not bad, quite often, it is implemented wrongly on the end-user sites and over-automation is taxing performance I have seen multiple automated options turned on where they where not needed, and actually hurting performance. This document summarizes the most common options and talk about how to unwind them back, delivering control back to DBA.

  • 1. No Flash area

    Stop wasting performance and disk space on the flash recovery option. There are other and better working ways to restore the accidentally deleted or damaged data.

    1. Run this command from sqlplus (if on RAC run for each instance):
      
        alter database flashback off;
    2. Reset the DB_RECOVERY_FILE_DEST initialzation parameter to a null string:
      
        ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='' SCOPE=BOTH SID='*';
    3. And change archive log destination:
      
        alter system set log_archive_dest_1='LOCATION=+FLASH';
      
        or pointing to file system location right away:
      
        alter system set log_archive_dest_1='LOCATION=/cdb/archives';

  • 2. Disable automatic stats

    There are database environment where stats and other automated stats related jobs are the only ones showing in top sql. The actual application SQL is not even making it up to the top. Stats gathering jobs could be heavy on the database and cost a lot of performance. Here is what and where to disable or reduce.


    1. Set STATISTICS_LEVEL parameter to BASIC. (carefull as you may still want to keep it in TYPICAL instead) If set to BASIC this will disables the collections of:
      Automatic Workload Repository (AWR) Snapshots
      Automatic Database Diagnostic Monitor (ADDM)
      All server-generated alerts
      Automatic SGA Memory Management
      Automatic optimizer statistics collection
      Object level statistics
      End to End Application Tracing (V$CLIENT_STATS)
      Database time distribution statistics (V$SESS_TIME_MODEL and V$SYS_TIME_MODEL)
      Service level statistics
      Buffer cache advisory
      MTTR advisory
      Shared pool sizing advisory
      Segment level statistics
      PGA Target advisory
      Timed statistics
      Monitoring of statistics
      however some of them (listed above) could be turned back on manually, while keeping general switch at BASIC value. Also before set STATISTICS_LEVEL = BASIC please make sure you disable the Automatic Shared Memory Management (ASMM) feature by unsetting the SGA_TARGET parameter first, otherwise you will get the following error ORA-00830: cannot set statistics_level to BASIC with auto-tune SGA enabled

    2. Disable Automatic Statistics Collection
      check what you got:

      
        SELECT STATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';
      
        STATE
      
        ---------------
      
        SCHEDULED (or could be RUNNING)

      To Disable the automatic statistics collection , execute the following procedure:

      
        EXEC DBMS_SCHEDULER.STOP_JOB('GATHER_STATS_JOB');
      
        EXEC DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

  • 3. Reduce Automatic Workload Repository retention.

    1. change AWR retention from default (10 minute snapshots retained for 30 days) to hourly snapshots retained for 1 day:
      
        exec dbms_workload_repository.modify_snapshot_settings(1440, 60);
      check with: 
      
        select * from DBA_HIST_WR_CONTROL;
    2. on 11g first do:
      
        exec  DBMS_WORKLOAD_REPOSITORY.modify_baseline_window_size(window_size => 1);
      after that the statement
      
        exec dbms_workload_repository.modify_snapshot_settings(1440, 60); will work OK.

  • 4. Disable recycle bin.

    Run these, while connected as sys:

    1. 
        purge dba_recyclebin;
      
    
    2. 
        alter system set recyclebin =off; (in 10.1 use  _recyclebin=FALSE instead)
      
    
    3. 
        add recyclebin=off to init/spfile.ora

  • 5. Disable auditing

    Oracle's audit is enabled by default. this generates lots of audit files on the disk and eats the space.
    To enable/disable auditing set: audit_trail=db_extended/none


  • 6. Convert from spfile to init.ora

    Some people still think that vi editor is a better way to view active/non-default oracle parameters than using sqlplus from command line. So, if spfile is sitting on +ASM first unlink ifile= from spfile and repoint to init <SID>.ora to use local init <SID>.ora instead.
    this will create init.ora from ASM shared spfile:

    
    echo "SPFILE='+DATA/BOSTON/spfileBOSTON.ora'" > initBOSTON.ora
    or:
    
    CREATE PFILE='/appdata1/achpd/initachpd.ora' FROM SPFILE;

  • 7. Get rid of sga_target and replace with regular memory parameters.

    any DBA knows how to go manual from automatic when performance gain is crusial.

  • 8. Stop autoextension of datafiles

    autoextending of oracle files could be justified only by one's amount of laziness to maintain static or dynamic-limited file structure with advanced space pre-allocation.
    Run this to generate the runnable syntax. And than run generated script:

    
    select 'alter database datafile '||chr(39)||file_name||chr(39)||' autoextend off;'||chr(10)
    
    from dba_data_files where AUTOEXTENSIBLE = 'YES' order by tablespace_name;


Questions/Comments? - feel free to contact Cerberus (which is me) on RDBA.net forums under Oracle DBA corner.