Sunday, June 5, 2011

Oracle DBA Cheat Sheet PART-1

alter database: Alter a Data File

ALTER DATABASE DATAFILE 4 OFFLINE;
ALTER DATABASE DATAFILE '/opt/oracle/datafile/users01.dbf' OFFLINE;
ALTER DATABASE DATAFILE '/opt/oracle/datafile/users01.dbf' RESIZE 100m;
ALTER DATABASE DATAFILE '/opt/oracle/datafile/users01.dbf'
AUTOEXTEND ON NEXT 100M MAXSIZE 1000M;
ALTER DATABASE DATAFILE 4 END BACKUP;

alter database: Alter a Tempfile

ALTER DATABASE TEMPFILE 4 RESIZE 100M;
ALTER DATABASE TEMPFILE 4 AUTOEXTEND ON NEXT 100M MAXSIZE 1000M;
ALTER DATABASE TEMPFILE 4 DROP INCLUDING DATAFILES;
ALTER DATABASE TEMPFILE 4 OFFLINE;

alter database: ARCHIVELOG Mode Commands


ALTER DATABASE ARCHIVELOG;
ALTER DATABASE NOARCHIVELOG;
ALTER DATABASE FORCE LOGGING;
ALTER DATABASE CLEAR LOGFILE '/opt/oracle/logfiles/redo01.rdo';
ALTER DATABASE CLEAR UNARCHIVED LOGFILE '/opt/oracle/logfiles/redo01.rdo';
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE);
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

alter database: Control File Operations


ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/opt/oracle/logfile_backup/backup_logfile.trc' REUSE RESETLOGS;
ALTER DATABASE BACKUP CONTROLFILE TO '/opt/oracle/logfile_backup/backup_logfile.ctl';

alter database: Create a Data File

ALTER DATABASE CREATE DATAFILE '/opt/oracle/datafile/users01.dbf' AS '/opt/oracle/datafile/users01.dbf';
ALTER DATABASE CREATE DATAFILE 4 AS '/opt/oracle/datafile/users01.dbf';
ALTER DATABASE CREATE DATAFILE '/opt/oracle/datafile/users01.dbf' AS NEW;

alter database: Datafile Offline/Online

See alter database: Alter a Data File

alter database: Logfile Commands

ALTER DATABASE ADD LOGFILE GROUP 2
('/opt/oracle/logfiles/redo02a.rdo', '/opt/oracle/logfiles/redo02b.rdo') SIZE 300M REUSE;
ALTER DATABASE ADD LOGFILE MEMBER '/opt/oracle/logfiles/redo02c.rdo' to GROUP 2;
ALTER DATABASE ADD LOGFILE thread 3 GROUP 2
('/opt/oracle/logfiles/redo02a.rdo', '/opt/oracle/logfiles/redo02b.rdo') SIZE 300M REUSE;
ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE DROP LOGFILE MEMBER '/opt/oracle/logfiles/redo02b.rdo';

alter database: Mount and Open the Database

ALTER DATABASE MOUNT;
ALTER DATABASE OPEN;

alter database: Move or Rename a Database File or Online Redo Log

NOTE
The database must be mounted to rename or move online redo logs.
The database must be mounted or the data files taken offline to move database data files.
ALTER DATABASE RENAME FILE '/ora/datafile/oldfile.dbf' TO '/ora/datafile/newfile.dbf';
alter database: Open the Database Read-Only
ALTER DATABASE OPEN READ ONLY;
alter database: Open the Database with resetlogs
ALTER DATABASE OPEN RESETLOGS;
Posted by at No comments:
Labels:

What Do All 10g Flashback Features Rely on and their Limitations ?

The New Oracle 10g Flashback Technologies Consist Of The Following :
  • Flashback Database
  • Flashback Drop
  • Flashback Table
  • Flashback Query
· Flashback Database :
The Flashback Database allows you to flash the entire database back to a specific point-in time. It is best used as a replacement for incomplete recovery of a complete database. The main benefit of the Oracle Flashback Database over incomplete database recovery is that the Flashback Database is much quicker and more efficient. The Flashback Database is not based on undo data but on flashback logs.
If flashback database is enabled, its flashback logs are stored in the Flash Recovery area. Flashback logs are written sequentially During normal database operation, and they are not archived. Oracle automatically creates, deletes, and resizes Flashback logs in the flash recovery area. You only need to be aware of Flashback logs for monitoring performance and deciding how much disk space to allocate to the flash recovery area for Flashback logs.
The amount of time it takes to Flashback a database is proportional to how far back you need to revert the database, rather than the time it would take to restore and recover the whole database, which could be much longer. The before images in the Flashback logs are only used to restore the database to a point in the past, and forward recovery is used to bring the database to a consistent state at some time in the past. Oracle returns datafiles to the previous point-in-time, but not auxiliary files, such as initialization parameter files.
DB_FLASHBACK_RETENTION_TARGET:
A parameter value that determines how far back in time you can recover the flashback database, This value is in minutes.
The setting of the DB_FLASHBACK_RETENTION_TARGET initialization parameter determines, indirectly, how much flashback log data the database retains. The size of flashback logs generated by the database for a given time period can vary considerably, however, depending on the specific database workload. If more blocks are affected by database updates during a given interval, then more disk space is used by the flashback log data generated for that interval.
The V$FLASHBACK_DATABASE_LOG view can help you estimate how much space to add to your flash recovery area for flashback logs. After you have enabled logging for Flashback Database and set a flashback retention target, allow the database to run under a normal workload for a while, to generate a representative sample of flashback logs. Then run the following query:
SQL> SELECT ESTIMATED_FLASHBACK_SIZE FROM      
       V$FLASHBACK_DATABASE_LOG;
Limitations of Flashback Database :
Because Flashback Database works by undoing changes to the datafiles that exist at the moment that you run the command, it has the following limitations:
· Flashback Database can only undo changes to a datafile made by an Oracle database. It cannot be used to repair media failures, or to recover from accidential deletion of datafiles.
· You cannot use Flashback Database to undo a shrink datafile operation.
· If the database control file is restored from backup or re-created, all accumulated flashback log information is discarded. You cannot use FLASHBACK DATABASE to return to a point in time before the restore or re-creation of a control file.
· When using Flashback Database with a target time at which a NOLOGGING operation was in progress, block corruption is likely in the database objects and datafiles affected by the NOLOGGING operation. For example, if you perform a direct-path INSERT operation in NOLOGGING mode, and that operation runs from 9:00 to 9:15 on April 3, 2005, and you later need to use Flashback Database to return to the target time 09:07 on that date, the objects and datafiles updated by the direct-path INSERT may be left with block corruption after the Flashback Database operation completes.
If possible, avoid using Flashback Database with a target time or SCN that coincides with a NOLOGGING operation. Also, perform a full or incremental backup of the affected datafiles immediately after any NOLOGGING operation to ensure recoverability to points in time after the operation. If you expect to use Flashback Database to return to a point in time during an operation such as a direct-path INSERT, consider performing the operation in LOGGING mode.
For Flashback Drop >> Click Here
Posted by at No comments:
Labels:

Steps to recover Applications context file if it is corrupted or deleted accidentally?

The Applications context file can be retrieved by running the adclonectx.pl script.
To retrieve the Applications tier context file,
  • perl /clone/bin/adclonectx.pl retrieve
  • On being prompted for the context file to be retrieved, select the option of retrieving the
          Applications tier context file that has been lost and retrieve it to the default location specified
          by the script.
The above command can be used only when INST_TOP the is still intact. In case that has also been lost
accidentally, the Applications tier context file may be retrieved as follows:
  • Execute the following command on the Database tier: perl /appsutil/clone/bin/adclonectx.pl retrieve
  • On being prompted for the context file to be retrieved, select the option of retrieving the
          Applications tier context file that has been lost.
  • While confirming the location for the context file, set it to any existing directory with write permission.
  • Once the context file has been generated in the specified location, move it to the location specified
          for the context file in the context variable 's_contextfile'.
To retrieve the Database tier context file,
  • Execute the following command on the Database tier: perl /appsutil/clone/bin/adclonectx.pl retrieve
  • On being prompted for the context file to be retrieved, select the Database tier context file and
          retrieve it to the default location specified by the script.
Posted by at No comments:
Labels:

How to relink all binaries after a OS Upgrade to ensure that everything is working as expected on a Release 11i or a Release 12 Installation on Unix ?

To get everything relinked and to ensure that the adadmin Utility was relinked before it is used, follow these steps :

Release 11i (Autoconfig enabled) :

Technology Stack Binaries inclusive Database :

Source the RDBMS ORACLE_HOME Environment
Execute the script $ORACLE_HOME/appsutil/install/adlnkoh.sh
Source the iAS ORACLE_HOME Envrionment
Execute the script $ORACLE_HOME/bin/adlnkiAS.sh
Source the APPS Environment
Execute the script $ORACLE_HOME/bin/adlnk806.sh

Oracle E-Business-Suite Binaries :
1. Source the APPS Environment

2. Execute following command :
adrelink.sh force=y "ad adadmin"
If you are using the command
adrelink.sh force=y "ad all"you are relinking all binaries under the $AD_TOP/bin - otherwise the AD Utilities are relinked via the adadmin Utility.

3. Execute adadmin
select --> '2. Maintain Applications Files menu'
select --> '1. Relink Applications programs'
--> select all modules to relink

Before starting the Instance, please open a new shell and source the Environment.
Release 12 :

Technology Stack Binaries inclusive Database :

Source the RDBMS ORACLE_HOME Environment
Execute the script $ORACLE_HOME/appsutil/clone/adlnkoh.sh
Source the APPS Envrionment
Execute the script $IAS_ORACLE_HOME/appsutil/clone/adlnkweboh.sh
Source the APPS Environment
Execute the script $ORACLE_HOME/appsutil/clone/adlnktools.sh

Oracle E-Business-Suite Binaries :

1. Source the APPS Environment

2. Execute following command
adrelink.sh force=y "ad adadmin"

If you are using the command
adrelink.sh force=y "ad all"

you are relinking all binaries under the $AD_TOP/bin - otherwise the AD Utilities are relinked via the adadmin Utility.

3. Execute adadmin
select --> '2. Maintain Applications Files menu'
select --> '1. Relink Applications programs'
--> select all modules to relink

Before starting the Instance, please open a new shell and source the Environment.
Posted by at No comments:
Labels:

Relink ASM, Agent, OMS Home 

. oraenv [+ASM]
cdo
# Note: cdo is a shell alias defined as: alias cdo='cd $ORACLE_HOME'

relink oracle
relink client_sharedlib
relink client
relink utilities
relink ldap
#relink oemagent - no target
relink network
relink ctx
relink interMedia

DBA Paranoia: To be doubly sure, run make files as listed in $ORACLE_HOME/inventory/make/makeorder.xml
make -f rdbms/lib/ins_rdbms.mk ioracle
make -f rdbms/lib/ins_rdbms.mk client_sharedlib
make -f sqlplus/lib/ins_sqlplus.mk install
make -f network/lib/ins_net_client.mk preinstall
make -f network/lib/ins_net_client.mk itnsping
make -f rdbms/lib/ins_rdbms.mk "irman"
make -f plsql/lib/ins_plsql.mk "install"
make -f network/lib/ins_net_client.mk "nnfgt.o"
make -f network/lib/ins_net_client.mk "mkldflags"
make -f rdbms/lib/ins_rdbms.mk "utilities"
make -f ldap/lib/ins_ldap.mk "clientonlyinstall"
make -f network/lib/ins_net_client.mk "ntcontab.o"
make -f network/lib/ins_net_client.mk  "nnfgt.o"
make -f network/lib/ins_net_client.mk  "install"  
make -f network/lib/ins_nau.mk "ioklist"
make -f network/lib/ins_nau.mk  "iokinit"
make -f network/lib/ins_nau.mk "iokdstry"
make -f sysman/lib/ins_emagent.mk "agent"
make -f sysman/lib/ins_emagent.mk "nmb"
make -f sysman/lib/ins_emagent.mk  "nmo"
make -f sysman/lib/ins_emagent.mk "nmhs"
make -f sysman/lib/ins_emagent.mk "tclexec"
make -f rdbms/lib/ins_rdbms.mk "all_no_orcl"
make -f srvm/lib/ins_srvm.mk "install"
make -f racg/lib/ins_has.mk  "racg_install"
make -f network/lib/ins_net_server.mk "install"
make -f rdbms/lib/ins_rdbms.mk "ctx_on"
make -f ctx/lib/ins_ctx.mk "install"
make -f rdbms/lib/ins_rdbms.mk  "ipc_g"
make -f sysman/lib/ins_emagent.mk "emsubagent"
make -f network/lib/ins_net_client.mk "mkldflags"
make -f rdbms/lib/ins_rdbms.mk "idg4odbc"
make -f precomp/lib/ins_precomp.mk "links"
make -f precomp/lib/ins_precomp.mk "relink"
make -f precomp/lib/ins_precomp.mk  "gen_pcscfg"
make -f rdbms/lib/ins_rdbms.mk "svr_tool"
make -f rdbms/lib/ins_rdbms.mk "patchset_opt"

Execute the $ORACLE_HOME/root.sh script to reset the appropriate file permissions/flags:
sudo $ORACLE_HOME/root.sh

Startup Oracle listener
Start up ASM instance

Relink agent home


# agentenv is a custom script that sets Oracle environment variables appropriately 
# for the agent home - all homes are using the one OS user - 'oracle'. 
# Normally I recommend one OS user per home.

. agentenv
relink client
relink oemagent

DBA Paranoia: To be doubly sure, run make files as listed in $ORACLE_HOME/inventory/make/makeorder.xml
cdo
make -f network/lib/ins_net_client.mk itnsping
make -f network/lib/ins_net_client.mk nnfgt.o
make -f network/lib/ins_net_client.mk mkldflags
make -f network/lib/ins_net_client.mk client_sharedlib
make -f sysman/lib/ins_emagent.mk agent
make -f sysman/lib/ins_emagent.mk nmb
make -f sysman/lib/ins_emagent.mk nmo
make -f sysman/lib/ins_emagent.mk nmhs
make -f sysman/lib/ins_emagent.mk emsubagent

Execute the $ORACLE_HOME/root.sh script to reset the appropriate file permissions/flags:
sudo $ORACLE_HOME/root.sh

Relink OMS HOme


Note omsenv is a custom script setting shell vriables for the OEM/OMS home.
. omsenv
cdo

relink client
relink network
relink client_sharedlib
relink oemagent
relink utilities

DBA Paranoia: To be doubly sure, run make files as listed in $ORACLE_HOME/inventory/make/makeorder.xml
make -f network/lib/ins_net_client.mk "nnfgt.o"
make -f network/lib/ins_net_client.mk "mkldflags"
make -f network/lib/ins_net_client.mk "client_sharedlib"
make -f sysman/lib/ins_sysman.mk agent
make -f sysman/lib/ins_sysman.mk nmo
make -f sysman/lib/ins_sysman.mk nmb
make -f sqlplus/lib/ins_sqlplus.mk install
make -f network/lib/ins_net_client.mk install
make -f network/lib/network/lib/ins_nau.mk ioklist
make -f network/lib/network/lib/ins_nau.mk iokinit
make -f network/lib/network/lib/ins_nau.mk iokdstry
make -f webcache/lib/ins_calypso.mk install

Execute the $ORACLE_HOME/root.sh script to reset the appropriate file permissions/flags:
sudo $ORACLE_HOME/root.sh
Posted by at No comments:
Labels:

Relinking Oracle Application Server 10g R2 (10.1.2) and 10g R3 (10.1.3) on UNIX

All Oracle Application Server 10g processes from the Oracle Home to be relinked need to be stopped using normal
administrative steps prior to beginning the relink.
Setup your environment correctly. Since both OracleAS 10g R2 and R3 are based on Oracle 10.1 database software,
pick the environment variable settings that apply to this version.

Relink Oracle Application Server 10g R2 (10.1.2/10.1.4.0.1)

Relinking the Infrastructure (Identity Management (IM) + Metadata Repository (MR), or MR Only)
  1. Relink Database software
    $ORACLE_HOME/bin/relink all
    cd $ORACLE_HOME/ldap/lib (Not required for MR Only)
    make -f ins_ldap.mk install
    make -f ins_ldap.mk hragentinstall
  2. Relink Application Server Control
    cd $ORACLE_HOME/sysman/lib
    make -f ins_sysman.mk agent
  3. As root execute $ORACLE_HOME/root.sh, and $ORACLE_HOME/root.sh.old if it exists.
This is needed to correctly set the permissions on the OID, and Application Server Control executables.
Relinking the Infrastructure (IM Only)
  1. Relink Database software
    cd $ORACLE_HOME/network/lib
    make -f ins_net_client.mk client_sharedlib
    make -f ins_net_client.mk install
    cd $ORACLE_HOME/rdbms/lib
    make -f ins_rdbms.mk utilities
    cd $ORACLE_HOME/plsql/lib
    make -f ins_plsql.mk install
    cd $ORACLE_HOME/sqlplus/lib
    make -f ins_sqlplus.mk install
    cd $ORACLE_HOME/ldap/lib
    make -f ins_ldap.mk install
    make -f ins_ldap.mk hragentinstall
  2. Relink Application Server Control
    cd $ORACLE_HOME/sysman/lib
    make -f ins_sysman.mk agent
  3. As root execute $ORACLE_HOME/root.sh, and $ORACLE_HOME/root.sh.old if it exists.
This is needed to correctly set the permissions on the OID, and Application Server Control executables.
Relinking the Middle Tier
  1. Relink Database software:
    cd $ORACLE_HOME/network/lib
    make -f ins_net_client.mk client_sharedlib
    make -f ins_net_client.mk install
    make -f ins_net_server.mk install (Business Intelligence & Forms only)
    cd $ORACLE_HOME/rdbms/lib (Not required for J2EE & Web Cache)
    make -f ins_rdbms.mk iexp
    make -f ins_rdbms.mk iimp
    make -f ins_rdbms.mk iloadpsp
    cd $ORACLE_HOME/plsql/lib (Not required for J2EE & Web Cache)
    make -f ins_plsql.mk install
    cd $ORACLE_HOME/sqlplus/lib
    make -f ins_sqlplus.mk install
    cd $ORACLE_HOME/ldap/lib (Not required for J2EE & Web Cache)
    make -f ins_ldap.mk toolsinstall
  2. Relink Application Server Control
    cd $ORACLE_HOME/sysman/lib
    make -f ins_sysman.mk agent
  3. Relink Web Cache
    cd $ORACLE_HOME/webcache/lib
    make -f ins_calypso.mk install
  4. Relink Forms (Business Intelligence & Forms only)
    cd $ORACLE_HOME/forms/lib
    make -f ins_forms.mk frmweb_install
    make -f ins_forms.mk frmcmp_install
    make -f ins_forms.mk frmcmpb_install
  5. Relink Reports (Business Intelligence & Forms only)
    cd $ORACLE_HOME/reports/lib
    make -f ins_reports.mk install
  6. As root execute $ORACLE_HOME/root.sh, and $ORACLE_HOME/root.sh.old if it exists.
This is needed to correctly set the permissions on the Application Server Control executables.

If permissions were previously modified for Web Cache, you will need to re-run any scripts (or apply manual changes)
to obtain any original functionality. For more information about webcache_setuser.sh,
Please refer to the following documentation:
Oracle Application Server Web Cache Administrator's Guide 10g (10.1.2)
http://download.oracle.com/docs/cd/B14099_19/caching.1012/b14046/toc.htm
8 Setup and Configuration
Running webcached with Root Privilege

Relink Oracle Application Server 10g R3 (10.1.3)

Relinking the Middle Tier
  1. Relink Database software:
    cd $ORACLE_HOME/network/lib
    make -f ins_net_client.mk client_sharedlib
    make -f ins_net_client.mk install
    cd $ORACLE_HOME/sqlplus/lib
    make -f ins_sqlplus.mk install
2. As root execute $ORACLE_HOME/root.sh, and $ORACLE_HOME/root.sh.old if it exists. This is needed to correctly
set the permissions on the newly relinked executables.
Posted by at No comments:
Labels:

Thursday, June 2, 2011

Automatic Database Diagnostic Monitor (ADDM)

Automatic Database Diagnostic Monitor (ADDM)
Oracle now provides automatic performance tuning capabilities, the heart of this function is the new statistics collection facility the automatic workload repository (AWR), which automatically collects and stores statistical data in the sysaux tablespace. ADDM ranks both the problems and its recommendations according to the crucial DB time statistic.AWR collects new performance statistics in the form of hourly snapshots (MMON processes the AWR request) and saves these to the sysaux tablespace, it is a snapshot shot of a single point in time. Every time AWR runs ADDM will automatically does a top-down system analysis and reports its findings on the database control home page.
See AWR on how to setup and configure it.
The purpose of ADDM is to reduce a key database metric called DB Time which is the total time (in microseconds) the database spends actually processing users requests. DB time includes the total amount of time spent on actual database calls (at the user level) and ignores time spent on background process. ADDM will only report on processes that contribute excessive DB time.
ADDM will report on the following
  • Expensive SQL/Java statements
  • I/O performance issues
  • Locking and Concurrency issues
  • Excessive parsing
  • High checkpoint load
  • Resource bottlenecks, including memory and CPU bottlenecks
  • Undersized memory allocations
  • Connection management issues, such as excessive logon/logoff activity
The report itself will contain
  • Expert problem diagnosis
  • Emphasis on the root cause of the problem rather than on the symptoms
  • A ranking of the effects of the problems, which means you can quickly find the problem
  • Recommendations ranked according to their benefit
Configuring ADDM
To active the AWR change the system parameter statistics_level to one of three values
  • basic - this option disables the AWR
  • typical (default) - activates standard level of collection
  • all - same as typical but includes execution plans and timing info from the O/S
Active alter system set statistics_level = typical;
alter system set statistics_level = all;
De-active alter system set statistics_level = basic;
Display show parameter statistics_level;
To change the snapshot interval and how many days the snapshots are kept you use the package dbms_workload_repository or Enterprise Manager
Snapshot configuration
Change snapshotting values exec dbms_workload_repository.modify_snapshot_settings ( interval => 60, retention => 43200);
interval = minutes
retention = seconds
Display values select * from dba_hist_wr_control;
Snapshot Management
Create a snapshot exec dbms_workload_repository.create_snapshot;
Delete snapshots exec dbms_workload_repository.drop_snapshot_range (low_snap_id=>1077, high_snap_id=>1078);
Display snapshots select snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot order by 1;
ADDM views
There are a number of views that should used when involving the ADDM
DBA_ADVISOR_FINDINGS finding identified by ADDM
DBA_ADVISOR_OBJECTS describe the objects that are referenced in findings
DBA_ADVISOR_RECOMMENDATIONS describe the recommendations based on ADDM findings
DBA_ADVISOR_RATIONALE describe the rationale behind each ADDM finding
DBA_SCHEDULER_JOBS list the gather_stats_job which runs the automatic snapshotting
dba_hist_baseline display baselines (see below)
dba_hist_snapshot display snapshots that are available
dba_hist_wr_control display the snapshot current settings
v$sys_time_model provides the accumulated time statistics for various operations in the entire database (in microseconds)
v$sess_time_model provides the accumulated time statistics for various operations in the session (in microseconds)
ADDM Report
To run ADDM report you can use the following operating system scripts or use Enterprise Manager.
addmrpt.sql the script will ask for begin snapshot and end snapshot plus if you want the report in text or html
ADDM baselines
The main purpose of a baseline is to preserve typical runtime statistics in the AWR repository, allowing you to run the AWR snapshot reports on the preserved baseline snapshots at any time and compare them to recent snapshots in the AWR.
Create baseline exec dbms_workload_repository.create_baseline (
  start_snap_id=> 1007,
  end_snap_id=> 1009,
  baseline_name=>'EOM baseline'
);
Remove baseline exec dbms_workload_repository.drop_baseline ( baseline_name => 'EOM baseline');
Display baselines select baseline_id, baseline_name, start_snap_id, end_snap_id from dba_hist_baseline;
Server Alerts
Server generated alerts are controlled by the MMON (manageability monitor) which is assisted by the MMNL (manageability monitor light). The metrics are gathered and thresholds checked once every minute. There are two thresholds warning and critical. There are over 100 alerts, MMON compares the metrics to the configured threshold and will write a message to the ALERT_QUEUE (in the AWR - sysaux tablespace). Processes can subscribe (only if you need to write your own alert handler) to this queue (and other queues) you pass on information from one process to another. The Enterprise manager daemon will action the ALERT_QUEUE and can places alerts messages in the GUI or can even send an email, you can also write your own alert handler. Remember the metrics are collected from the SYSAUX tablespace not the v$ views. There is no connection to the alert log and the server alert system.
Alert Types select internal_metric_name from v$alert_types;
Current alerts select reason, object_type type, object_name name from dba_outstanding_alerts;
Old Alerts select reason, object_type type, object_name name from dba_alert_history;
Useful Views
dba_outstanding_alerts current alert waiting for resolution
dba_alert_history alerts that have been cleared
dba_thresholds threshold settings defined for the instance
v$alert_types alert type and group information
v$metric system-level metric values in memory
v$metricname names, identifiers and other info about system metrics
v$metric_history historical system-level metric values in memory
Stateless or non thresholds alerts are stored in dba_alert_history because they are solved as soon as they occur. Remember that the parameter statistics_level must be set to typical or all in order to obtain alerts. Shutdown commands are only recorded in the alert log file.
The dbms_server_alert package contains procedures to change the alerts
Change threshold exec dbms_server_alert.set_threshold(
  dbms_server_alert.tablespace_pct_full,
  dbms_server_alert.operator_ge, 90,
  dbms_server_alert.operator_ge, 99,
  1, 1, null, dbms_server_alert.object_type_tablespace,
‘USERS2’);
Set_threshold fields
-----------------------------------------------------------------------------
Metrics_id - the name of the metric
Warning_operator - the comparison operator to compare values
Warning_value - warning threshold
Critical_operator - comparison operator for comparing current value to the warning threshold
Critical_value - critical threshold
Observation_period - timer period at which metrics are computed against the threshold
Consecutive_occurences - how many times the value exceeds the threshold before an alert is raised
Instance_name - the instance that the threshold is applied
Object_type - object type i.e tablespace, session, service, etc
Object_name - name of the object
Get Threshold set serveroutput on
DECLARE
  vWarnOp NUMBER(10);
  vWarnVal VARCHAR2(100);
  vCritOp NUMBER(10);
  vCritVal VARCHAR2(100);
  vObsvPer NUMBER(5);
  vConOcur NUMBER(5);
BEGIN
  dbms_server_alert.get_threshold(dbms_server_alert.tablespace_pct_full,
     vWarnOp, vWarnVal, vCritOp, vCritVal, vObsvPer, vConOcur, NULL,
  dbms_server_alert.object_type_tablespace, 'TEST');
  dbms_output.put_line('WarnOp: ' || TO_CHAR(vWarnOp));
  dbms_output.put_line('WarnVal: ' || vWarnVal);
  dbms_output.put_line('CritOp: ' || TO_CHAR(vCritOp));
  dbms_output.put_line('CritVal: ' || vCritVal);
  dbms_output.put_line('Observation: ' || vObsvper);
  dbms_output.put_line('Occurences: ' || vConOcur);
END;
/
Expand_message select dbms_server_alert.expand_message(null, 6, null, null, null, null, null) alert_msg from dual;
Expand Message fields
----------------------------------------------------------------------------
User_language - the current sessions language
Message_id - alert message ID
Argument_1 - 1 st argument
Argument_2 - 2nd argument
Argument_3 - 3rd argument
Argument_4 - 4th argument
Argument_5 - 5th argument
Posted by at No comments:
Labels:

Automatic Workload Repository (AWR)

Automatic Workload Repository (AWR)
The AWR collects and stores database statistics relating to problem detection and tuning. AWR is a replacement for the statspack utility which helps gather database performance statistics. AWR generates snapshots of key performance data, such as system and session statistics, segment-usage statistics, time-model statistics, high-load statistics and stores it in the sysaux tablespace.
AWR provides statistics in two formats
  • temporary - in memory collection of statistics in the SGA, accessible via the V$ views
  • persistent - type of performance data in the form of regular AWR snapshots which you access via the DBA_ views
The MMON process is responsible for collecting the statistics in the SGA and saving them to the sysaux tablespaces.
AWR will collect data on the following
  • Base statistics that are also part of the v$SYSSTAT and V$SESSTAT views
  • SQL statistics
  • Database object-usage statistics
  • Time-model statistics
  • Wait statistics
  • ASH (active session history) statistics
  • Operating system statistics
Tables that AWR uses to collect statistics
v$sys_time_model time model stats (db time, java execution time, pl/sql execution time, etc)
v$osstat operating system stats (avg_busy_ticks, avg_idle_ticks, etc)
v$service_stats wait statistics ( db cpu, app wait time, user commits, etc)
v$sysstat system stats
v$sesstat session stats
Database performance stats fall into one of three categories:
  • Cumulative values - collect stats over a period of time from the v$sysstat, etc
  • Metrics - use the collected stats to make some sort of sense.
  • Sampled data - the ASH sampler is used to collect these stats.
AWR Setup
To active the AWR change the system parameter statistics_level to one of three values
  • basic - this option disables the AWR
  • typical (default) - activates standard level of collection
  • all - same as typical but includes execution plans and timing info from the O/S
Active alter system set statistics_level = typical;
alter system set statistics_level = all;
De-active alter system set statistics_level = basic;
Display show parameter statistics_level;
To change the snapshot interval and how many days the snapshots are kept you use the package dbms_workload_repository or Enterprise Manager
Snapshot configuration
Change snapshotting values exec dbms_workload_repository.modify_snapshot_settings ( interval => 60, retention => 43200);
interval = minutes
retention = seconds
Display values select * from dba_hist_wr_control;
Snapshot Management
Create a snapshot exec dbms_workload_repository.create_snapshot;
Delete snapshots exec dbms_workload_repository.drop_snapshot_range (low_snap_id => 1077, high_snap_id => 1078);
Create a baseline exec dbms_workload_repository.create_baseline (start_snap_id => 1070, end_snap_id => 1078, baseline_name => 'Normal Baseline');
Delete a baseline exec dbms_workload_repository.drop_baseline (baseline_name => 'Normal Baseline', cascade => FALSE);
Display snapshots select snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot order by 1;
View the repository tables select table_name from dba_tables where tablespace_name = ‘SYSAUX’ and substr(table_name, 1,2) = ‘WR’ and rownum <= 20 order by 1;
Useful Views
dba_hist_active_sess_history ASH info (see below)
dba_hist_baseline baseline info
dba_hist_database_instance environment data
dba_hist_sql_plan sql execution path data
dba_hist_wr_control AWR settings
dba_hist_snapshot snapshot info in the AWR
AWR Report
To run AWR report you can use the following operating system scripts or use Enterprise Manager.
awrrpt.sql the script will ask for begin snapshot and end snapshot and will be generated in text format
Note: reports went in $oracle_home\db_1\bin
awrrpti.sql the script will ask for begin snapshot and end snapshot and will be generated in HTML format
Note: reports went in $oracle_home\db_1\bin
You can obtain more information regarding the AWR from automatic database diagnostic manager (ADDM).
Posted by at No comments:
Labels: