You can also refer – How to install TFACTL    1. Check tfactl status with version: tfactl status 2. Check tfactl tool status: tfactl toolstatus 3. Get config details: tfactl print config 4. List of user having access to tfactl: tfactl access lsusers 6. changing property of a user: tfactl access promote -user oracle 5.Adding […]

Recently oracle has bundled both  Trace file analyzer(TFA /TFACTL) and orachk into Autonomous health framework popularly known as AHF. So just installing ahf setup will take care of both tfa and orachk including few other monitor tools. STEPS TO INSTALL/UPGRADE AHF Download the AHF tool from oracle support. – DOWNLOAD AHF ENVIRONMENT – SOLARIS 2 NODE […]

AWR report can be generating in RAC database using 2 scripts awrrpt.sql or awrrpti.sql awrrpt.sql – > This will generate the one report for the database across all the nodes(i.e for all instances) for a partiular snapshot range. awrrpti.sql – > This will genereate report for a particular instance, i.e for a 2 node RAC […]

ADRCI is the command line interface for diagnostic utility used for viewing diagnostics data like listener log , alert log ,incident and cor dump etc and creating incident packages. Below are the the list of useful commands. 1. Get current  base location:( Also known as ADR_BASE) adrci> show base ADR base is “/u01/app/oracle/” 2. Set new […]

Below is the steps for dropping a sql baseline from the database.   1.Get the sql_handle and sql_baseline name of the sql_id: SELECT sql_handle, plan_name FROM dba_sql_plan_baselines WHERE signature IN ( SELECT exact_matching_signature FROM gv$sql WHERE sql_id=’&SQL_ID’) SQL_HANDLE PLAN_NAME ——————————————— —————————————————- SQL_164b2be280f1ffba SQL_PLAN_1cktbwa0g3zxu06dab5d5 2. Drop the baseline: SQL> select sql_handle,plan_name from dba_sql_plan_baselines where plan_name=’SQL_PLAN_1cktbwa0g3zxu06dab5d5′; SQL_HANDLE […]

Automatic Database Diagnostic Monitor(ADDM) can analyze performance issues during a particular period and provide suggestion. An ADDM analysis is performed on a set of awr snapshots. addmrpt.sql script is used to generate addm report. Generate ADDM report: cd $ORACLE_HOME/rdbms/admin SQL> @addmrpt.sql It will ask for begin snap_id and end snap_id SQL> @addmrpt.sql Current Instance ~~~~~~~~~~~~~~~~ DB […]

The V$SGA_TARGET_ADVICE view provides information that helps us in deciding optimal value for  SGA_TARGET. MMON background process gather statistics about sga_target usage and update the V$SGA_TARGET_ADVICE view. Check sga target advisory is enable? — DB_CACHE_ADVICE should be ON SQL> show parameter db_cache_advice NAME TYPE VALUE ———————————— ——————————– —————— db_cache_advice string ON — STATISTICS_LEVEL should be […]

Shared pool stores and caches the SQL and PL/SQL queries hitting the database , which avoids hard parsing the repetitive SQLs and It leads to increasing performance and memory usage. shared_pool_size is the parameter which controls the size of shared_pool.But Most of the time DBAs wonder how much value should be the shared_pool_size. In that […]

We can get the all the execution plan details of an sql_id for a particular awr snapshot. EXAMPLE: sql_id – > 5vqy6cj4jr89k Run the below script: start $ORACLE_HOME/rdbms/admin/awrsqrpt.sql We need to pass below inputs while running the script. REPORT_TYPE – NUM_DAYS – BEGIN_SNAP – END_SNAP – SQL_ID – SQL> start $ORACLE_HOME/rdbms/admin/awrsqrpt.sql Current Instance ~~~~~~~~~~~~~~~~ DB Id […]

By default, only top sql_ids are captured in database snapshots. If an SQL ID is colored, it will be captured in every snapshot, independent of its level of activities  (so that it does not have to be a TOP SQL). Mark an sql_id as colored execute DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL(sql_id=>’b62q7nc33gzwx’); PL/SQL procedure successfully completed. colored sql_ids will be […]