$ rman target devdb
Recovery Manager: Release 11.2.0.1.0 - Production on Thu May 19 16:09:31 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
target database Password:
connected to target database: devdb (DBID=6959472717)
RMAN> list failure all;
using target database control file instead of recovery catalog
no failures found that match specification
RMAN> list failure all;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
148 HIGH OPEN 19-MAY-11 Tablespace 7: 'TEST' is offline
142 HIGH OPEN 19-MAY-11 One or more non-system datafiles are
offline
RMAN> list failure critical;
no failures found that match specification
RMAN> list failure high;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
148 HIGH OPEN 19-MAY-11 Tablespace 7: 'TEST' is offline
142 HIGH OPEN 19-MAY-11 One or more non-system datafiles are
offline
RMAN> list failure detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
148 HIGH OPEN 19-MAY-11 Tablespace 7: 'TEST' is offline
Impact: Some objects in tablespace TEST might be unavailable
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
142 HIGH OPEN 19-MAY-11 One or more non-system datafiles are
offline
Impact: See impact for individual child failures
List of child failures for parent failure ID 142
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
145 HIGH OPEN 19-MAY-11 Datafile 6: '/u01/oracle/oradata
/testdb/test01.dbf' is offline
Impact: Some objects in tablespace TEST might be unavailable
RMAN>
RMAN> change failure 148 priority low;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
148 HIGH OPEN 19-MAY-11 Tablespace 7: 'TEST' is offline
Do you really want to change the above failures (enter YES or NO)? yes
changed 1 failures to LOW priority
RMAN>
By Default, Oracle11g RMAN "list failure" command lists only the critical and high priority alerts as shown below
RMAN> list failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
168 HIGH OPEN 19-MAY-11 One or more non-system datafiles are
missing
142 HIGH OPEN 19-MAY-11 One or more non-system datafiles are
offline
RMAN>
We changed the priority of failure id 148 from high to low. Hence the failure id 148 are not shown for the list failure command.
To list the low priority failures, either use "list failure all" or "list failure low" commands.
Now to change back the priority of failure id 148 to high :
RMAN> change failure 148 priority high;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
148 LOW OPEN 19-MAY-11 Tablespace 7: 'TEST' is offline
Do you really want to change the above failures (enter YES or NO)? yes
changed 1 failures to HIGH priority
RMAN>
$ rman target dev1
Recovery Manager: Release 11.2.0.1.0 - Production on Fri May 20 15:29:11 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
target database Password:
connected to target database: DEV1 (DBID=3359724377)
RMAN> repair failure
2> ;
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of repair command at 05/20/2011 15:29:33
RMAN-06954: REPAIR command must be preceded by ADVISE command in same session
RMAN> repair failure 148;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "integer": expecting one of: "noprompt, no, prev
iew, using, ;"
RMAN-01007: at line 1 column 16 file: standard input
RMAN> repair failure noprompt;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of repair command at 05/20/2011 15:30:30
RMAN-06954: REPAIR command must be preceded by ADVISE command in same session
RMAN>
RMAN> advice failure high;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "identifier": expecting one of: "advise, allocat
e, alter, backup, @, catalog, change, configure, connect, convert, copy, create,
crosscheck, delete, drop, duplicate, exit, flashback, grant, host, import, list
, mount, open, print, quit, recover, register, release, repair, replace, report,
reset, restore, resync, revoke, run, send, set, show, shutdown, spool, sql, sta
rtup, switch, transport, unregister, upgrade, validate, {, "
RMAN-01008: the bad identifier was: advice
RMAN-01007: at line 1 column 1 file: standard input
As shown above, list failure, advise failure and repair failure commands are interdependent. i.e they have to follow in sequence in the same session as shown below
RMAN> list failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
168 HIGH OPEN 19-MAY-11 One or more non-system datafiles are
missing
148 HIGH OPEN 19-MAY-11 Tablespace 7: 'TEST' is offline
142 HIGH OPEN 19-MAY-11 One or more non-system datafiles are
offline
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
168 HIGH OPEN 19-MAY-11 One or more non-system datafiles are
missing
148 HIGH OPEN 19-MAY-11 Tablespace 7: 'TEST' is offline
142 HIGH OPEN 19-MAY-11 One or more non-system datafiles are
offline
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=76 device type=DISK
analyzing automatic repair options complete
Not all specified failures can currently be repaired.
The following failures must be repaired before advise for others can be given.
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
168 HIGH OPEN 19-MAY-11 One or more non-system datafiles are
missing
Mandatory Manual Actions
========================
1. If file /u01/oracle/oradata/testdb/test01.dbf was unintentionally renamed or
moved, restore it
2. If you have an export of tablespace TEST, then drop and re-create the tablesp
ace and import the data.
3. Contact Oracle Support Services if the preceding recommendations cannot be us
ed, or if they do not fix the failures selected for repair
Optional Manual Actions
=======================
no manual actions available
Automated Repair Options
========================
no automatic repair options available
RMAN>
SQL> alter tablespace test online;
Tablespace altered.
RMAN> validate database;
Starting validate at 20-MAY-11
using channel ORA_DISK_1
RMAN-06169: could not read file header for datafile 6 error reason 4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of validate command at 05/20/2011 15:40:07
RMAN-06056: could not access datafile 6
RMAN> validate database;
Starting validate at 20-MAY-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=/u01/oracle/oradata/testdb/system01.dbf
input datafile file number=00002 name=/u01/oracle/oradata/testdb/sysaux01.dbf
input datafile file number=00003 name=/u01/oracle/oradata/testdb/undotbs01.dbf
input datafile file number=00005 name=/u01/oracle/oradata/testdb/example01.dbf
input datafile file number=00004 name=/u01/oracle/oradata/testdb/users01.dbf
input datafile file number=00006 name=/u01/oracle/oradata/testdb/test01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:07
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 OK 0 13131 90884 2087390
File Name: /u01/oracle/oradata/testdb/system01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 61902
Index 0 12538
Other 0 3309
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2 OK 0 20164 89630 2087388
File Name: /u01/oracle/oradata/testdb/sysaux01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 21854
Index 0 17196
Other 0 30386
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3 OK 0 385 14080 2087390
File Name: /u01/oracle/oradata/testdb/undotbs01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 13695
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 OK 0 255 640 1000225
File Name: /u01/oracle/oradata/testdb/users01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 91
Index 0 39
Other 0 255
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5 OK 0 1688 12804 1987513
File Name: /u01/oracle/oradata/testdb/example01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 6603
Index 0 1261
Other 0 3248
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6 OK 0 121 128 1958860
File Name: /u01/oracle/oradata/testdb/test01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 7
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
including current SPFILE in backup set
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE OK 0 2
Control File OK 0 594
Finished validate at 20-MAY-11
RMAN>
Thank you for your patience in reading, If you could drop me a line giving your feedback - good, bad or any suggestions to improve would be much appreciated.
Friday 1 July 2011
Automatic SQL Tuning
Automatic SQL Tuning
Automatic SQL Tuning is a new feature of Oracle 11g which utilizes the SQL Tuning Advisor (introduced in 10g) to create sql profiles for the queries executed on the database. This tuning task runs every night as part of the daily maintenance window.
Below are the steps carried by the automatic sql tuning process every night
Step 1: Analyzes the Automatic workload repository (AWR) to identify the top sql statements during different time periods in the past week.
Step 2: Assigns weight to each sql statement identified from different time periods and starts working from the top sql. And in the mean time, it invokes the sql tuning advisor to tune the previously identified sql statements.
Step 3: The sql tuning advisor creates a sql profile which are stored in sql management base.
Step 4: After thorough verification of the sql profile, Automatic sql tuning adds it to the sql plan baseline.
You can control the sql profile implementation and tuning from automatic maintenance tasks configuration page on the enterprise manager. DBMS_SQLTUNE package is used to control the automatic sql tuning from the command line
BEGIN
dbms_sqltune.set_auto_tuning_task_parameter( 'LOCAL_TIME_LIMIT', 900);
END;
PL/SQL procedure successfully completed.
SQL>
Where LOCAL_TIME_LIMIT is the parameter to control the maximum time spent per SQL during the tuning activity. The value 900 means a maximum of 15 minutes.
MAX_SQL_PROFILES_PER_EXEC is the parameter to control the maximum number of sql profiles which can be implemented per execution. From our example, the maximum number of sql profile implementation per execution is 10.
BEGIN
dbms_sqltune.set_auto_tuning_task_parameter( 'MAX_SQL_PROFILES_PER_EXEC', 10);
END;
PL/SQL procedure successfully completed.
SQL>
By default, the Automatic SQL Tuning implements the beneficial sql profiles after verification. To disable the automatic implementation, ACCEPT_SQL_PROFILES parameter should be set to FALSE as shown below
BEGIN
dbms_sqltune.set_auto_tuning_task_parameter( 'ACCEPT_SQL_PROFILES', 'FALSE');
END;
PL/SQL procedure successfully completed.
SQL>
The Automatic sql tuning can be disabled by disabling the sql tuning advisor using DBMS_AUTO_TASK_ADMIN package.
BEGIN
dbms_auto_task_admin.disable(client_name => 'sql tuning advisor', operation => NULL, window_name => NULL);
END;
PL/SQL procedure successfully completed.
SQL>
Some times we may need to disable the automatic sql tuning for a particular day. For example, if we want to disable the automatic sql tuning on thursday then we need to specify the window name.
BEGIN
dbms_auto_task_admin.disable(client_name => 'sql tuning advisor', operation => NULL, window_name => 'THURSDAY_WINDOW');
END;
PL/SQL procedure successfully completed.
SQL>
Similarly, to enable back the automatic sql tuning on Thursday
BEGIN
dbms_auto_task_admin.enable(client_name => 'sql tuning advisor', operation => NULL, window_name => 'THURSDAY_WINDOW');
END;
PL/SQL procedure successfully completed.
SQL>
To check the automatic sql tuning executions query DBA_ADVISOR_EXECUTIONS data dictionary table.
SQL> column execution_name format a10
SQL> column owner format a20
SQL> column advisor_name format a30
SQL>
SQL> select execution_name,status,owner,advisor_name from dba_advisor_executions where advisor_name = 'SQL Tuning Advisor';
EXECUTION_ STATUS OWNER ADVISOR_NAME
---------- ----------- -------------------- ------------------------------
EXEC_17 COMPLETED SYSTEM SQL Tuning Advisor
EXEC_23 COMPLETED SYS SQL Tuning Advisor
EXEC_62 COMPLETED SYS SQL Tuning Advisor
SQL>
From the above list we can generate a report of the execution using REPORT_AUTO_TUNING_TASK procedure.
Automatic SQL Tuning is a new feature of Oracle 11g which utilizes the SQL Tuning Advisor (introduced in 10g) to create sql profiles for the queries executed on the database. This tuning task runs every night as part of the daily maintenance window.
Below are the steps carried by the automatic sql tuning process every night
Step 1: Analyzes the Automatic workload repository (AWR) to identify the top sql statements during different time periods in the past week.
Step 2: Assigns weight to each sql statement identified from different time periods and starts working from the top sql. And in the mean time, it invokes the sql tuning advisor to tune the previously identified sql statements.
Step 3: The sql tuning advisor creates a sql profile which are stored in sql management base.
Step 4: After thorough verification of the sql profile, Automatic sql tuning adds it to the sql plan baseline.
You can control the sql profile implementation and tuning from automatic maintenance tasks configuration page on the enterprise manager. DBMS_SQLTUNE package is used to control the automatic sql tuning from the command line
BEGIN
dbms_sqltune.set_auto_tuning_task_parameter( 'LOCAL_TIME_LIMIT', 900);
END;
PL/SQL procedure successfully completed.
SQL>
Where LOCAL_TIME_LIMIT is the parameter to control the maximum time spent per SQL during the tuning activity. The value 900 means a maximum of 15 minutes.
MAX_SQL_PROFILES_PER_EXEC is the parameter to control the maximum number of sql profiles which can be implemented per execution. From our example, the maximum number of sql profile implementation per execution is 10.
BEGIN
dbms_sqltune.set_auto_tuning_task_parameter( 'MAX_SQL_PROFILES_PER_EXEC', 10);
END;
PL/SQL procedure successfully completed.
SQL>
By default, the Automatic SQL Tuning implements the beneficial sql profiles after verification. To disable the automatic implementation, ACCEPT_SQL_PROFILES parameter should be set to FALSE as shown below
BEGIN
dbms_sqltune.set_auto_tuning_task_parameter( 'ACCEPT_SQL_PROFILES', 'FALSE');
END;
PL/SQL procedure successfully completed.
SQL>
The Automatic sql tuning can be disabled by disabling the sql tuning advisor using DBMS_AUTO_TASK_ADMIN package.
BEGIN
dbms_auto_task_admin.disable(client_name => 'sql tuning advisor', operation => NULL, window_name => NULL);
END;
PL/SQL procedure successfully completed.
SQL>
Some times we may need to disable the automatic sql tuning for a particular day. For example, if we want to disable the automatic sql tuning on thursday then we need to specify the window name.
BEGIN
dbms_auto_task_admin.disable(client_name => 'sql tuning advisor', operation => NULL, window_name => 'THURSDAY_WINDOW');
END;
PL/SQL procedure successfully completed.
SQL>
Similarly, to enable back the automatic sql tuning on Thursday
BEGIN
dbms_auto_task_admin.enable(client_name => 'sql tuning advisor', operation => NULL, window_name => 'THURSDAY_WINDOW');
END;
PL/SQL procedure successfully completed.
SQL>
To check the automatic sql tuning executions query DBA_ADVISOR_EXECUTIONS data dictionary table.
SQL> column execution_name format a10
SQL> column owner format a20
SQL> column advisor_name format a30
SQL>
SQL> select execution_name,status,owner,advisor_name from dba_advisor_executions where advisor_name = 'SQL Tuning Advisor';
EXECUTION_ STATUS OWNER ADVISOR_NAME
---------- ----------- -------------------- ------------------------------
EXEC_17 COMPLETED SYSTEM SQL Tuning Advisor
EXEC_23 COMPLETED SYS SQL Tuning Advisor
EXEC_62 COMPLETED SYS SQL Tuning Advisor
SQL>
From the above list we can generate a report of the execution using REPORT_AUTO_TUNING_TASK procedure.
Automatic Database Diagnostic Monitor
Automatic Database Diagnostic Monitor was first introduced in Oracle 10g.
Oracle 11g R2 ADDM Advisor has 83 finding names.
SQL> column finding_name format a50
SQL> column advisor_name format a10
SQL> select * from dba_advisor_finding_names
ID ADVISOR_NA FINDING_NAME
---------- ---------- --------------------------------------------------
0 Default Ad normal, successful completion
visor
1 ADDM "Administrative" Wait Class
2 ADDM "Application" Wait Class
3 ADDM "Cluster" Wait Class
4 ADDM "Concurrency" Wait Class
5 ADDM "Configuration" Wait Class
6 ADDM "Network" Wait Class
7 ADDM "Other" Wait Class
8 ADDM "Scheduler" Wait Class
9 ADDM "User I/O" wait Class
10 ADDM Buffer Busy
11 ADDM Buffer Cache Latches
12 ADDM Checkpoints Due to DROP or TRUNCATE
13 ADDM Checkpoints Due to Log File Size
14 ADDM Checkpoints Due to MTTR
15 ADDM Checkpoints Due to Parallel Queries
16 ADDM Checkpoints Due to Tablespace DDL
17 ADDM Commits and Rollbacks
18 ADDM CPU Usage
19 ADDM DBMS_LOCK Usage
20 ADDM DBMS_PIPE Usage
21 ADDM Excessive Rebinds
22 ADDM Free Buffer Waits
23 ADDM Hard Parse
24 ADDM Hard Parse Due to Invalidations
25 ADDM Hard Parse Due to Literal Usage
26 ADDM Hard Parse Due to Parse Errors
27 ADDM Hard Parse Due to Sharing Criteria
28 ADDM High Watermark Waits
29 ADDM I/O Throughput
30 ADDM Index Block Split
31 ADDM Global Cache Busy
32 ADDM Global Cache Congestion
33 ADDM Interconnect Latency
34 ADDM Global Cache Lost Blocks
35 ADDM Global Cache Messaging
36 ADDM Global Cache Multiblock Requests
37 ADDM ITL Waits
38 ADDM Java Execution
39 ADDM Latch Free Waits
40 ADDM Log File Switches
41 ADDM PL/SQL Compilation
42 ADDM PL/SQL Execution
43 ADDM RMAN I/O
44 ADDM Row Lock Waits
45 ADDM Sequence Usage
46 ADDM Session Connect and Disconnect
47 ADDM Session Slot Scheduling
48 ADDM Shared Pool Latches
49 ADDM Slow Archivers
50 ADDM Soft Parse
51 ADDM Space Transaction Waits
52 ADDM Streams Flow Control
53 ADDM Table Locks
54 ADDM Temp Space Contention
55 ADDM Top Segments by I/O
56 ADDM Top SQL by "Cluster" Wait
57 ADDM Top SQL by DB Time
58 ADDM Top SQL By I/O
59 ADDM Undersized Buffer Cache
60 ADDM Undersized PGA
61 ADDM Undersized Redo Log Buffer
62 ADDM Undersized SGA
63 ADDM Undersized Shared Pool
64 ADDM Undersized Streams Pool
65 ADDM Undo I/O
66 ADDM Unusual "Administrative" Wait Event
67 ADDM Unusual "Application" Wait Event
68 ADDM Unusual "Cluster" Wait Event
69 ADDM Unusual "Commit" Wait Event
70 ADDM Unusual "Concurrency" Wait Event
71 ADDM Unusual "Configuration" Wait Event
72 ADDM Unusual "Network" Wait Event
73 ADDM Unusual "Other" Wait Event
74 ADDM Unusual "Scheduler" Wait Event
75 ADDM Unusual "User I/O" Wait Event
76 ADDM Virtual Memory Paging
77 ADDM Unusual "Queueing" Wait Event
78 ADDM "Queueing" Wait Class
79 ADDM Undersized instance memory
80 ADDM Top SQL Statements
81 ADDM Top Segments by "User I/O" and "Cluster"
82 ADDM Buffer Busy - Hot Block
83 ADDM Buffer Busy - Hot Objects
84 rows selected.
SQL>
Oracle 11g R2 ADDM Advisor has 83 finding names.
SQL> column finding_name format a50
SQL> column advisor_name format a10
SQL> select * from dba_advisor_finding_names
ID ADVISOR_NA FINDING_NAME
---------- ---------- --------------------------------------------------
0 Default Ad normal, successful completion
visor
1 ADDM "Administrative" Wait Class
2 ADDM "Application" Wait Class
3 ADDM "Cluster" Wait Class
4 ADDM "Concurrency" Wait Class
5 ADDM "Configuration" Wait Class
6 ADDM "Network" Wait Class
7 ADDM "Other" Wait Class
8 ADDM "Scheduler" Wait Class
9 ADDM "User I/O" wait Class
10 ADDM Buffer Busy
11 ADDM Buffer Cache Latches
12 ADDM Checkpoints Due to DROP or TRUNCATE
13 ADDM Checkpoints Due to Log File Size
14 ADDM Checkpoints Due to MTTR
15 ADDM Checkpoints Due to Parallel Queries
16 ADDM Checkpoints Due to Tablespace DDL
17 ADDM Commits and Rollbacks
18 ADDM CPU Usage
19 ADDM DBMS_LOCK Usage
20 ADDM DBMS_PIPE Usage
21 ADDM Excessive Rebinds
22 ADDM Free Buffer Waits
23 ADDM Hard Parse
24 ADDM Hard Parse Due to Invalidations
25 ADDM Hard Parse Due to Literal Usage
26 ADDM Hard Parse Due to Parse Errors
27 ADDM Hard Parse Due to Sharing Criteria
28 ADDM High Watermark Waits
29 ADDM I/O Throughput
30 ADDM Index Block Split
31 ADDM Global Cache Busy
32 ADDM Global Cache Congestion
33 ADDM Interconnect Latency
34 ADDM Global Cache Lost Blocks
35 ADDM Global Cache Messaging
36 ADDM Global Cache Multiblock Requests
37 ADDM ITL Waits
38 ADDM Java Execution
39 ADDM Latch Free Waits
40 ADDM Log File Switches
41 ADDM PL/SQL Compilation
42 ADDM PL/SQL Execution
43 ADDM RMAN I/O
44 ADDM Row Lock Waits
45 ADDM Sequence Usage
46 ADDM Session Connect and Disconnect
47 ADDM Session Slot Scheduling
48 ADDM Shared Pool Latches
49 ADDM Slow Archivers
50 ADDM Soft Parse
51 ADDM Space Transaction Waits
52 ADDM Streams Flow Control
53 ADDM Table Locks
54 ADDM Temp Space Contention
55 ADDM Top Segments by I/O
56 ADDM Top SQL by "Cluster" Wait
57 ADDM Top SQL by DB Time
58 ADDM Top SQL By I/O
59 ADDM Undersized Buffer Cache
60 ADDM Undersized PGA
61 ADDM Undersized Redo Log Buffer
62 ADDM Undersized SGA
63 ADDM Undersized Shared Pool
64 ADDM Undersized Streams Pool
65 ADDM Undo I/O
66 ADDM Unusual "Administrative" Wait Event
67 ADDM Unusual "Application" Wait Event
68 ADDM Unusual "Cluster" Wait Event
69 ADDM Unusual "Commit" Wait Event
70 ADDM Unusual "Concurrency" Wait Event
71 ADDM Unusual "Configuration" Wait Event
72 ADDM Unusual "Network" Wait Event
73 ADDM Unusual "Other" Wait Event
74 ADDM Unusual "Scheduler" Wait Event
75 ADDM Unusual "User I/O" Wait Event
76 ADDM Virtual Memory Paging
77 ADDM Unusual "Queueing" Wait Event
78 ADDM "Queueing" Wait Class
79 ADDM Undersized instance memory
80 ADDM Top SQL Statements
81 ADDM Top Segments by "User I/O" and "Cluster"
82 ADDM Buffer Busy - Hot Block
83 ADDM Buffer Busy - Hot Objects
84 rows selected.
SQL>
Oracle 11g Automated Maintenance Tasks
Three tasks are executed as part of automated maintenance task. They are
Optimizer Statistics Gathering
Segment Advisor
Automatic SQL Tuning
By default the above tasks are enabled on the database. If required these tasks can be disabled for each week or for a particular day.
To disable the optimizer statistics collection, use the DBMS_AUTO_TASK_ADMIN package as shown below
BEGIN
dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL);
END;
PL/SQL procedure successfully completed.
SQL>
To disable for a particular day for example Thursday
SQL> BEGIN
dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection', operation => NULL, window_name => 'THURSDAY_WINDOW');
END;
PL/SQL procedure successfully completed.
SQL>
To disable the segment advisor,
BEGIN
dbms_auto_task_admin.disable(client_name => 'auto space advisor', operation => NULL, window_name => NULL);
END;
PL/SQL procedure successfully completed.
SQL>
Similarly to enable them use dbms_auto_task_admin.enable procedure.
By default the automated maintenance tasks starts at 10pm every day. Sometimes there might be some batch jobs running on the server so we may need to change the time for the maintenance task. To change the timing, The task needs to be disabled first.
This could be achieved by using DBMS_SCHEDULER package. For example if we want to start the maintenance task at 11pm on Thursdays then
SQL> BEGIN
2 DBMS_SCHEDULER.DISABLE(
3 name=>'"SYS"."THURSDAY_WINDOW"',
4 force=>TRUE);
5 END;
6 /
PL/SQL procedure successfully completed.
SQL>
SQL> BEGIN
2 DBMS_SCHEDULER.SET_ATTRIBUTE(
3 name=>'"SYS"."THURSDAY_WINDOW"',
4 attribute=>'REPEAT_INTERVAL',
5 value=>'FREQ=WEEKLY;BYDAY=THU;BYHOUR=23;BYMINUTE=0;BYSECOND=0');
6 END;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL> BEGIN
2 DBMS_SCHEDULER.ENABLE(
3 name=>'"SYS"."THURSDAY_WINDOW"',
4 force=>TRUE);
5 END;
6 /
PL/SQL procedure successfully completed.
SQL>
Optimizer Statistics Gathering
Segment Advisor
Automatic SQL Tuning
By default the above tasks are enabled on the database. If required these tasks can be disabled for each week or for a particular day.
To disable the optimizer statistics collection, use the DBMS_AUTO_TASK_ADMIN package as shown below
BEGIN
dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL);
END;
PL/SQL procedure successfully completed.
SQL>
To disable for a particular day for example Thursday
SQL> BEGIN
dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection', operation => NULL, window_name => 'THURSDAY_WINDOW');
END;
PL/SQL procedure successfully completed.
SQL>
To disable the segment advisor,
BEGIN
dbms_auto_task_admin.disable(client_name => 'auto space advisor', operation => NULL, window_name => NULL);
END;
PL/SQL procedure successfully completed.
SQL>
Similarly to enable them use dbms_auto_task_admin.enable procedure.
By default the automated maintenance tasks starts at 10pm every day. Sometimes there might be some batch jobs running on the server so we may need to change the time for the maintenance task. To change the timing, The task needs to be disabled first.
This could be achieved by using DBMS_SCHEDULER package. For example if we want to start the maintenance task at 11pm on Thursdays then
SQL> BEGIN
2 DBMS_SCHEDULER.DISABLE(
3 name=>'"SYS"."THURSDAY_WINDOW"',
4 force=>TRUE);
5 END;
6 /
PL/SQL procedure successfully completed.
SQL>
SQL> BEGIN
2 DBMS_SCHEDULER.SET_ATTRIBUTE(
3 name=>'"SYS"."THURSDAY_WINDOW"',
4 attribute=>'REPEAT_INTERVAL',
5 value=>'FREQ=WEEKLY;BYDAY=THU;BYHOUR=23;BYMINUTE=0;BYSECOND=0');
6 END;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL> BEGIN
2 DBMS_SCHEDULER.ENABLE(
3 name=>'"SYS"."THURSDAY_WINDOW"',
4 force=>TRUE);
5 END;
6 /
PL/SQL procedure successfully completed.
SQL>
Subscribe to:
Posts (Atom)