Script to automate generation AWR for a day
– The following anonymous PL/SQL code can be used to generate a script which can be run to generate AWR report for one day. Currently the script runs to create report for yesterday’s report. It creates a report between each snap.
set serveroutput on
set feedback off
spool rpt.sql
set termout off
set linesize 200
set trimspool on
DECLARE
dbid v$database.dbid%TYPE;
dbname v$database.name%TYPE;
-- snap history get snap ids
CURSOR l_cur IS
SELECT snap_id, begin_interval_time, end_interval_time FROM dba_hist_snapshot
WHERE begin_interval_time >= trunc(SYSDATE) – 1 and end_interval_time <= trunc(SYSDATE) + 1/24
ORDER BY begin_interval_time;
l_rec l_cur%ROWTYPE;
l_prev_rec l_cur%ROWTYPE;
l_begin_date VARCHAR2(30);
l_end_date VARCHAR2(30);
BEGIN
SELECT dbid, name INTO dbid, dbname FROM v$database;
OPEN l_cur;
FETCH l_cur INTO l_rec;
WHILE l_cur%FOUND
LOOP
l_prev_rec := l_rec;
FETCH l_cur INTO l_rec;
IF l_cur%found THEN
l_begin_date := to_char(l_prev_rec.BEGIN_INTERVAL_TIME, 'YYYYMMDD_HH24MI');
l_end_date := to_char(l_prev_rec.end_interval_time, 'YYYYMMDD_HH24MI');
dbms_output.put_line('-- ' || l_begin_date
|| ' – ' || l_end_date);
dbms_output.put_line('define inst_num = 1;');
dbms_output.put_line('define num_days = 0;');
dbms_output.put_line('define inst_name = ''' || dbname || ''';');
dbms_output.put_line('define db_name = ''' || dbname || ''';');
dbms_output.put_line('define dbid = ' || dbid || ';');
dbms_output.put_line('define report_type = ''html'';');
dbms_output.put_line('define begin_snap = ' || l_prev_rec.snap_id);
dbms_output.put_line('define end_snap = ' || l_rec.snap_id);
dbms_output.put_line('define report_name = /tmp/awr_' || dbname
|| '_' || l_begin_date || '_' || l_end_date || '.html');
dbms_output.put_line('@?/rdbms/admin/awrrpti');
END IF;
END LOOP;
CLOSE l_cur;
END;
/
Sample Output from rpt.sql:
-- 20111125_0000 – 20111125_0100
define inst_num = 1;
define num_days = 0;
define inst_name = ‘TESTDB’;
define db_name = ‘TESTDB’;
define dbid = 2547329494;
define report_type = ‘html’;
define begin_snap = 384
define end_snap = 385
define report_name = /tmp/awr_TESTDB_20111125_0000_20111125_0100.html
@?/rdbms/admin/awrrpti
-- 20111125_0100 – 20111125_0200
define inst_num = 1;
define num_days = 0;
define inst_name = ‘TESTDB’;
define db_name = ‘TESTDB’;
define dbid = 2547329494;
define report_type = ‘html’;
define begin_snap = 385
define end_snap = 386
define report_name = /tmp/awr_TESTDB_20111125_0100_20111125_0200.html
@?/rdbms/admin/awrrpti
How to view 10053 trace?
To view output from 10053 using the following link one can download software which allows one to review the output from the trace file.
To turn on 10053 trace.
SQL> alter session set events '10053 trace name context forever, level 1';
To review trace 10053
http://jonathanlewis.wordpress.com/2010/04/30/10053-viewer/
How to view/change statistics retention and space used to store stats history?
See the oldest statistics history available
SQL> SELECT DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY FROM DUAL;
GET_STATS_HISTORY_AVAILABILITY
—————————————————————————
25-OCT-11 10.00.55.093000000 PM -07:00
See the stats retention
SQL> select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
—————————
31
Modify retention
SQL> exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(60);
Space currently used to store statistics in SYSAUX in KBytes, so increasing the retention would affect it’s size
SQL> select occupant_desc, space_usage_kbytes from v$sysaux_occupants where OCCUPANT_DESC like '%Statistics%';
OCCUPANT_DESC SPACE_USAGE_KBYTES
—————————————————————- ——————
Server Manageability – Optimizer Statistics History 17920
How to restore statistics and view statistics history?
Shows in this scenrio there are backup of stats that exists for SCOTT.TEST table
SQL> select * FROM DBA_TAB_STATS_HISTORY where owner = 'SCOTT' and table_name = 'TEST';
no rows selected
Display current timestamp of last time when the table and index was analyzed
SQL> select last_analyzed from dba_indexes where table_name = 'TEST' and table_owner = 'SCOTT';
LAST_ANALYZED
——————–
2006-MAY-09 00:00:25
2006-MAY-09 00:00:25
2006-MAY-09 00:00:25
2006-MAY-09 00:00:25
2006-MAY-09 00:00:25
2006-MAY-09 00:00:25
2006-MAY-09 00:00:25
SQL> select last_analyzed from dba_tables where table_name = 'TEST' and owner = 'SCOTT';
LAST_ANALYZED
——————–
2011-JUL-21 00:20:34
Gather/Update stats on the table
SQL> exec dbms_stats.gather_table_stats('SCOTT', 'TEST', cascade=>true);
Check stats on the table by checking last_analyzed date, shows date updated
SQL> select last_analyzed from dba_indexes where table_name = 'TEST' and table_owner = 'SCOTT';
LAST_ANALYZED
——————–
2011-NOV-26 10:38:57
2011-NOV-26 10:39:14
2011-NOV-26 10:39:18
2011-NOV-26 10:39:19
2011-NOV-26 10:39:28
2011-NOV-26 10:39:28
2011-NOV-26 10:39:28
SQL> select last_analyzed from dba_tables where table_name = 'TEST' and owner = 'SCOTT';
LAST_ANALYZED
——————–
2011-NOV-26 10:38:55
When stats are gathered dbms_stats backups the stats are backup and viewed in DB_TAB_STATS_HISTORY which can be used to restore stats
SQL> select * FROM DBA_TAB_STATS_HISTORY where owner = 'SCOTT' and table_name = 'TEST';
OWNER TABLE_NAME STATS_UPDATE_TIME
————— —————————— —————————-
SCOTT TEST 11-11-26 10:38:56,021074 -07
Restore stats as of yesterday i.e. to time before the stats were run
SQL> exec dbms_stats.restore_table_stats('SCOTT', 'TEST', TO_DATE('26-NOV-2011 11:00', 'DD-MON-YYYY HH24:MI') – 1));
Check last analyzed date it indicates the stats were restored back
SQL> select last_analyzed from dba_indexes where table_name = 'TEST' and table_owner = 'SCOTT';
LAST_ANALYZED
——————–
2006-MAY-09 00:00:25
2006-MAY-09 00:00:25
2006-MAY-09 00:00:25
2006-MAY-09 00:00:25
2006-MAY-09 00:00:25
2006-MAY-09 00:00:25
2006-MAY-09 00:00:25
SQL> select last_analyzed from dba_tables where table_name = 'TEST' and owner = 'SCOTT';
LAST_ANALYZED
——————–
2011-JUL-21 00:20:34
dbms_stats.restore_table_stats backs up the stats again to restore the stats that was changed
SQL> select * FROM DBA_TAB_STATS_HISTORY where owner = 'SCOTT' and table_name = 'TEST';
OWNER TABLE_NAME STATS_UPDATE_TIME
————— —————————— —————————-
SCOTT TEST 11-11-26 11:53:24,517857 -07
SCOTT TEST 11-11-26 10:38:56,021074 -07
How to fix a scenerio when the datafile was removed before dropping the tablespace?
Ran into following issue were accidentally the datafile was removed before dropping the tablespace. If not fixed this can cause the instance to crash.
The alert log had the following message in this case, it shows the instance cannot access the datafile:
…
Errors in file /orabin/diag/rdbms/testdb/TESTDB/trace/TESTDB_m000_64749810.trc:
ORA-01116: error in opening database file 89
ORA-01110: data file 89: ‘/oradata/TESTDB/tbs01.dbf’
ORA-27041: unable to open file
IBM AIX RISC System/6000 Error: 2: No such file or directory
If the issue is not fixed the instance crashed on it’s own with following message after some time:
…
Tue Nov 22 17:17:01 2011
System state dump requested by (instance=1, osid=9044114 (CKPT)), summary=[abnormal instance termination].
System State dumped to trace file /orabin/diag/rdbms/testdb/TESTDB/trace/TESTDB_diag_35586096.trc
Instance terminated by CKPT, pid = 9044114
Tue Nov 22 18:43:13 2011
The tablespace can’t be dropped or datafile can’t be taken offline as in this case the instance was running in NOARCHIVE log mode. so one would need to use the “OFFLINE DROP” option show below
SQL> ALTER DATABASE DATAFILE ‘/oradata/TESTDB/tbs01.dbf’ OFFLINE;
ALTER DATABASE DATAFILE ‘/oradata/TESTDB/tbs01.dbf’ OFFLINE
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled
SQL> ALTER DATABASE DATAFILE ‘/oradata/TESTDB/tbs01.dbf’ OFFLINE DROP;
Database altered.
– Step to show the datafile cannot be made online unless datafile is recovered
SQL> ALTER DATABASE DATAFILE ‘/oradata/TESTDB/tbs01.dbf’ ONLINE;
ALTER DATABASE DATAFILE ‘/oradata/TESTDB/tbs01.dbf’ ONLINE
*
ERROR at line 1:
ORA-01113: file 89 needs media recovery
ORA-01110: data file 89: ‘/oradata/TESTDB/tbs01.dbf’
– Display the online status of the datafile
SQL> select * FROM dba_data_files where tablespace_name = ‘TESTTBS’;
FILE_NAME
——————————————————————————–
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
———- —————————— ———- ———- ———
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
———— — ———- ———- ———— ———- ———–
ONLINE_
——-
/oradata/TESTDB/tbs01.dbf
89 TESTTBS AVAILABLE
89
RECOVER
SQL> drop tablespace TESTTBS;
Tablespace dropped.
SQL> select * from dba_data_files where file_name like ‘/oradata/TESTDB/tbs01.dbf’;
no rows selected
– Confirm the datafile is no longer there by creating a control file to trace
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE as ‘/tmp/test.ctl’;
In the scenerio if the database crashed one would need start the database in mount state. If you try to offline the datafile with using the “OFFLINE DROP” option again in this case you will receive the following “ORA-01145: offline immediate disallowed unless media recovery enabled” as this is allowed when the database is in ARCHIVE log mode.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2177328 bytes
Variable Size 314574544 bytes
Database Buffers 197132288 bytes
Redo Buffers 8052736 bytes
Database mounted.
– offline drop the datafile before opening the database, the database can’t be opened as the file doesn’t exists and control file will look for the datafile when one opens the instane.
SQL> ALTER DATABASE DATAFILE ‘/oradata/TESTDB/tbs01.dbf’ OFFLINE DROP;
Database altered.
SQL> alter database open;
Database altered.
– Shows the online status of the datafile in dba_data_files
SQL> select * from dba_data_files where file_name like ‘/oradata/TESTDB/tbs01.dbf’;
FILE_NAME
——————————————————————————–
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
———- —————————— ———- ———- ———
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
———— — ———- ———- ———— ———- ———–
ONLINE_
——-
/oradata/TESTDB/tbs01.dbf
89 TESTTBS AVAILABLE
89
RECOVER
SQL> drop tablespace TESTDBS;
Tablespace dropped.
SQL> select * from dba_data_files where file_name like ‘/oradata/TESTDB/tbs01.dbf’;
no rows selected
– Confirm the datafile is no longer there by creating a control file to trace
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE as ‘/tmp/test.ctl’;
Database altered.
If the instance was running in ARCHIVE LOG mode, the datafile can be offline and tablespace can be dropped as shown below
SQL> ALTER DATABASE DATAFILE ‘I:\DB\ORACLE\TESTDB\ORADATA\TBS01.dbf’ OFFLINE;
Database altered.
– Shows the online status of the datafile in dba_data_files
SQL> select * from dba_data_files where file_name like ‘/oradata/TESTDB/tbs01.dbf’;
FILE_NAME
——————————————————————————–
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
———- —————————— ———- ———- ———
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
———— — ———- ———- ———— ———- ———–
ONLINE_
——-
/oradata/TESTDB/tbs01.dbf
89 TESTTBS AVAILABLE
89
RECOVER
SQL> drop tablespace TESTTBS;
Tablespace dropped.
How to find the queue_depth of a lun based filesystem on AIX?
# Displays information of all volume group, in the following example vb_TESTDB_data1 is one of the oracle filesystem
/home/oracle $ lsvg
..
rootvg
vg_TESTDB_data1
…
# Display the filesystem the volume group maps to
/home/oracle $ lsvg -l vg_TESTDB_data1
vg_TESTDB_data1:
LV NAME TYPE LPs PPs PVs LV STATE MOUNT POINT
fslv06 jfs2 7671 7671 1 open/syncd /oradata/TESTDB/data01
# List the volume group hdisk assigned to it
/home/oracle $ lsvg -p vg_TESTDB_data1
vg_TESTDB_data1:
PV_NAME PV STATE TOTAL PPs FREE PPs FREE DISTRIBUTION
hdisk17 active 494 6 00..00..00..00..06
# List the disk attributes
/home/oracle $ lsattr -El hdisk17
PCM PCM/friend/vscsi Path Control Module False
algorithm fail_over Algorithm True
hcheck_cmd test_unit_rdy Health Check Command True
hcheck_interval 30 Health Check Interval True
hcheck_mode nonactive Health Check Mode True
max_transfer 0×40000 Maximum TRANSFER Size True
pvid 00caea80b93fc0ca0000000000000000 Physical volume identifier False
queue_depth 96 Queue DEPTH True
reserve_policy no_reserve Reserve Policy True
Monitoring AIX
On AIX – nmon on AIX can be used to monitoring a machine (CPU, Memory, Network traffic, I/O, top processes, etc). Here is the link to download nmon http://www.ibm.com/developerworks/wikis/display/WikiPtype/nmon
Example to collect nmon data and extract output to a file:
$ nmon_aix53 -fTNWLA -I 0.001 -s 300 -c 288 -m /home/nmon_data
This collects the data once a day in the directory /home/nmon_data at once every 5 minutes and with 288 snapshot which makes a excellent graph detail level. It also collects top processes and user command lines (T), NFS stats (N), Workload Manager but no Subclasses (W), Large page stats (L) and Asynchronous I/O details. The reporting threshold is 0.001 percent of a CPU.
The output from nmon can be graphed using nmon analyzer it reads output from nmon and it can be loaded into excel that graphs to aid in analysis and reports to analyze nmon.
http://www.ibm.com/developerworks/wikis/display/Wikiptype/nmonanalyser
Why a 99%+ Database Buffer Cache Hit Ratio is Not Ok – by Cary Milsap
The following article by Cary Milsap talks about “Why a 99%+ Database Buffer Cache Hit Ratio is Not Ok” by Hotsos (http://www.hotsos.com/e-library/abstract.php?id=6). One needs to register an account to read the article which is free.
RMAN – incremental backup
RMAN command to create level 0 backup which is needed before running of incremental backup
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;
RMAN command to run level 1 backup. Level 1 backup will backup all blocks changed since most recent cumulative or differential backup. If a level 0 backup doesn’t exists, when running INCREMENTAL backup Oracle will perform a full backup.
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;
RMAN command to run level 1 cumulative backup. Level 1 backup will backup all blocks changed since most recent Level 0 backup. If a level 0 backup doesn’t exists, when running INCREMENTAL backup Oracle will perform a full backup.
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;
RMAN command to backup database level 1 and skip datafiles and archived redo logs that cannot be read due to I/O errors to be excluded from backup
RMAN> BACKUP INCREMENTAL LEVEL 1 INACCESSIBLE DATABASE;
