Shaamil's Blog

Redo logs cannot be resized on the fly we must drop and recreate them.This is the only method known to resize at the time this post is written. A database requires at least two groups of redo log files,regardless the number of the members. We cannot drop the redo log file if it's status is current or active. Initially the status need to be changed "inactive" before dropping the redo log member. Soon after a redo log member is drooped the file doesn't remove from the file system, instead it need to be separately removed from the file system. Step 1 : Check the Status of Redo Logfile    SQL>   select group#,sequence#,bytes,archived,status from v$log;      GROUP#        SEQUENCE#      BYTES    ARC STATUS ----------  ----------   ----------    -----  -------------          1          5  ...

Evaluate Oracle AWR (Automatic Workload Repository) Some very important operations on Oracle AWR 1. Operations 1.1 How to Install/Enable Set STATISTICS_LEVEL to TYPICAL, does not need bounce.  Ref: http://docs.oracle.com/cd/B19306_01/server.102/b14211/autostat.htm If we want to save every second ASH data to disk, set “_ash_disk_filter_ratio”=1. By default, it save 1 second snapshot out of 10 seconds. If we want to set AWR snapshot interval to 15 minutes, by default it’s 60 minutes: exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval=>15); If we want to set AWR data retention time to 90 days, by default it’s 7 days: exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>90*1440); 1.2 How to Disable In 11g, set CONTROL_MANAGEMENT_PACK_ACCESS = NONE as Oracle document said. But even with that, as my test in 11.2.0.2, Oracle will still create AWR snapshots. You can download the file dbmsnoawr.plb from Oracle DOC [ID 436386.1], and r...