ADDM and Query for recommendations of the last ADDM run

ADDM
=====
=====
Once the AWR snapshot is taken, the ADDM analysis occurs automatically as long as the STATISTICS_LEVEL parameter is set to TYPICAL or ALL. The new Oracle Database 10g background process MMON performs the ADDM analysis. The results of the ADDM run is stored in the AWR, and can be accessed through various means such as OEM, manual query, or an Oracle supplied API.
One of the parameters that ADDM uses to perform its analysis is DBIO_EXPECTED. This parameter is not set in the database parameter file, but is rather set using the Oracle supplied package DBMS_ADVISOR. This parameter defines the response time expected by Oracle from the disk I/O system, and the value defaults to 10 milliseconds. If you have slower disks, you will want to reset this value. In the following example, we have reset the expected disk response time to 20 milliseconds, since that is our measured response time from our disks:

Exec dbms_advisor.set_default_task_parameter(’ADDM’, -’DBIO_EXPECTED’, 20000);

ADDM Analysis Goals
=====================
ADDM’s goal is to improve the value of a statistic called db time. Db time is a value that Oracle calculates to indicate the cumulative time that is spent processing user requests. ADDM’s goal is to reduce the overall db time value, and it does not target individual users or user response times, and thus the db time it uses is an aggregate value of overall system CPU and wait times You can see the current value of db time for the entire system by querying the V$SYS_TIME_MODEL or you can see it for a given session by using the V$SESS_TIME_MODEL view as seen here:

select sum(value) "DB time" from v$sess_time_modelwhere stat_name='DB time';
DB time


Memory-related issues such as
========================
shared pool latch contention, log buffer issues, or database buffer cache related problems
CPU bottlenecks
Disk I/O performance issues
Database configuration problems
Space-related issues, such as tablespaces running out of space
Application and SQL tuning issues such as excessive parsing and excessive locking

ADDM Analysis Results

The results of the ADDM analysis are stored in the AWR in the form of findings. There are three different kinds of findings:

Problem — A problem indicates a root cause problem that is causing a database performance problem.
Symptom — A symptom indicates a performance issue that normally points to one or more specific problem findings.
Information — This is just basic database-related information that is not related to a specific problem.

The recommendations of the last ADDM run, you can execute this query:
==================================================================


SELECT a.execution_end, b.TYPE, b.impact, d.RANK, d.TYPE,
'Message : ' || b.MESSAGE MESSAGE,
'Command To correct: ' || c.command command,
'Action Message : ' || c.MESSAGE action_message
FROM dba_advisor_tasks a,
dba_advisor_findings b,
dba_advisor_actions c,
dba_advisor_recommendations d
WHERE a.owner = b.owner
AND a.task_id = b.task_id
AND b.task_id = d.task_id
AND b.finding_id = d.finding_id
AND a.task_id = c.task_id
AND d.rec_id = c.rec_id
AND a.task_name LIKE 'ADDM%'
AND a.status = 'COMPLETED'
ORDER BY b.impact, d.RANK;

bottleneck, throughput, Wait events

1. Removing the major bottleneck (apoint where resource contention is highest)
2. performance problems result from a lack of throughput (the amount of work
that can be completed in a specified time), unacceptable user or job response time (the
time to complete a specified workload), or both.
3. Wait events are incremented by a session to indicate that the session had to wait for an event to complete before being able to continue processing. When a session has to wait while processing a user request, the database records the wait by using one of a set of predefined wait events. The events are then grouped into wait classes, such as User I/O and Network. Wait event data reveals symptoms of problems that might be affecting performance, such as latch, buffer, or I/O contention.

Active Session History Statistics

Active Session History Statistics
=================================
The Active Session History (ASH) statistics are samples of session activity in the
database. The database samples active sessions every second and stores them in a
circular buffer in the System Global Area (SGA). Any session that is connected to the
database and using CPU, or is waiting for an event that does not belong to the idle
wait class, is considered an active session. By capturing only active sessions, a
manageable set of data is represented. The size of the data is directly related to the
work being performed, rather than the number of sessions allowed on the database.

Database time (DB TIME)

Database time (DB TIME)
====================
The objective of database tuning is to reduce database time. In this way, you can
improve the overall response time of user transactions in the application.

The most important time model statistic is database time (DB time). Database time
represents the total time spent in database calls, and is an indicator of the total instance
workload. Database time makes up a portion of an application's overall user response time.
Database time is calculated by aggregating the CPU time and wait time of all active sessions
(sessions that are not idle). For anydatabase request, the CPU time is the sum of the
time spent working on the request,while the wait time is the sum of all the waits for
various database instance resources.DB time does not include time spent on background
processes such as PMON.

The CPU time spent by thedatabase processing the request and the wait time spent
waiting for the database are considered DB time.

Note: DB time is measured cumulatively from when the instance
started. Because DB time combines times from all non-idle user
sessions, DB time can exceed the time elapsed since the instance
started. For example, an instance that has run 5 minutes could have
four active sessions whose cumulative DB time is 20 minutes.

you can find DB time with this query's
---------------------------------------

SELECT SUM (VALUE) "DB time"
FROM v$sess_time_model
WHERE stat_name = 'DB time';


select * from V$SYS_TIME_MODEL

Migrate DB to ASM

We are Using RMAN to relocate non-ASM files to ASM files. The ASM files cannot be accessed through normal OS interfaces.

Step1: Query V$CONTROLFILE and V$LOGFILE to get the file names.
SQL> select * from V$CONTROLFILE;
SQL> select * from V$LOGFILE;

Step 2: Shutdown the database.
SQL> SHUTDOWN IMMEDIATE;

Step3: Modify the target database parameter file:
(i) Remove the CONTROL_FILES parameter from the spfile, so the control files will be created automatically in ASM Disk group mentioned in DB_CREATE_FILE_DEST destination
Using a pfile then set CONTROL_FILES parameter to the appropriate ASM files or aliases.
(ii) Set the DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n parameters to the relevant ASM disk groups.

Step 4: Startup nomount mode and Restore control file
RMAN> STARTUP NOMOUNT;

Restore the control file into the new location.
RMAN> RESTORE CONTROLFILE FROM 'old_controlfile_name';

Step 5: Startup mount mode and backup the Database.
RMAN> ALTER DATABASE MOUNT;

Backup database into ASM disk group.
RMAN> BACKUP AS COPY DATABASE FORMAT '+diskgroup1';

Step 6: Switch database and create or rename Redo log members
Switch all data files to the new ASM Diskgroup location.
RMAN> SWITCH DATABASE TO COPY;
RMAN> SQL “ALTER DATABASE RENAME ‘old_redolog_member’ to ‘+diskgroup2’;
or
Create new redo logs in ASM Disk group and delete the old redo log files.

Step 7: Open Database and create temporary tablespace.
Open database using resetlogs
SQL> ALTER DATABASE OPEN RESETLOGS;

Create temporary tablespace in ASM disk group.
SQL> CREATE TABLESPACE temp1 ADD TEMPFILE ‘+diskgroup1’;

Step 8: Drop old database files.
1. SQL> DROP TABLESPACE ‘old_temporary_tablespace’ including contents and datafiles;
2. Remove all remaining Non-ASM database files using OS commands

Set DISPLAY variable

Set DISPLAY variable & Enable access control

Before starting the Oracle Universal Installer, the DISPLAY environment must be set correctly for display terminal support.
To set the DISPLAY environment run the following command where hostname is the hostname or IP address of the system where the X server is running:

$ export DISPLAY=hostname:0.0

It may be necessary to enable access control on the system where the X server is running, so that the clients can connect.
The "xhost" command is used to set access controls. The xhost command must be run on the console of the system where
the X server is running. It cannot be done remotely.

To enable access control so that clients from any host can connect to the X server, type the following command:

$ xhost +

access control disabled, clients can connect from any host

To enable access control only to certain clients run the following command
$ xhost +hostname
For ex:- $ xhost +server1
Server1 being added to access control list

if you face
1. Error: Can't open display
Error: Couldn't find per display Information

Solution:
1. Run xhost + to enable access control
2. Set your DISPLAY

Oracle Block change Tracking

Oracle Block change Tracking
============================
============================

Oracle Block change Tracking improves the incremental Backup performance and
is used to record changed blocks in each datafile in a change tracking file.
If change Tracking is enabled, RMAN uses block change tracking file to identify
changed blocks for incremental backups and avoids full datafile scans during the backup.Block change tracking feature introduce in Oracle 10g R1. By default,
the Block change tracking file is created as Oracle managed file in
DB_CREATE_FILE_DEST.By default, Oracle will not record block change information.
You can Enable or disable the change tracking when the database is open or mounted.

Enable and Disable Block Change Tracking:
===============================================

SQL>ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

Run the below command to create change tracking file in a specified location.
===========================================================================

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE 'D:\oradata\orcl\block_change_track.ora' REUSE;

Run the below command to disable block change tracking
=======================================================

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Run the below Query to monitor the status of block change tracking
==================================================================

SQL> SELECT FILE,STATUS,BYTES FROM V$BLOCK_CHANGE_TRACKING;

SQLPLUS COMMAND DESCRIPTION

SQLPLUS - COMMAND

========================================
GET A LIST OF SET COMMAND
---------------------------
SQLPLUS> HELP SET
========================================
Syntax:
SET option value

SHO[W] option

========================================
APPI[NFO]{ON|OFF|text}
Application info for performance monitor (see DBMS_APPLICATION_INFO)

ARRAY[SIZE] {15|n}
Fetch size (1 to 5000) the number of rows that will be retrieved in one go.

AUTO[COMMIT] {OFF|ON|IMM[EDIATE]|n}
Autocommit commits after each SQL command or PL/SQL block

AUTOP[RINT] {OFF|ON}
Automatic PRINTing of bind variables.(see PRINT)

AUTORECOVERY [ON|OFF]
Configure the RECOVER command to automatically apply
archived redo log files during recovery - without any user confirmation.

AUTOT[RACE] {OFF|ON|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
Display a trace report for SELECT, INSERT, UPDATE or DELETE statements
EXPLAIN shows the query execution path by performing an EXPLAIN PLAN.
STATISTICS displays SQL statement statistics.
Using ON or TRACEONLY with no explicit options defaults to EXPLAIN STATISTICS

BLO[CKTERMINATOR] {.|c|OFF|ON}
Set the non-alphanumeric character used to end PL/SQL blocks to c

CMDS[EP] {;|c|OFF|ON}
Change or enable command separator - default is a semicolon (;)

COLSEP { |text}
The text to be printed between SELECTed columns normally a space.

COM[PATIBILITY] {V5|V6|V7|V8|NATIVE}
Version of oracle - see also init.ora COMPATIBILITY=
You can set this back by up to 2 major versions e.g. Ora 9 supports 8 and 7

CON[CAT] {.|c|OFF|ON}
termination character for substitution variable reference
default is a period.

COPYC[OMMIT] {0|n}
The COPY command will fetch n batches of data between commits.
(n= 0 to 5000) the size of each fetch=ARRAYSIZE.
If COPYCOMMIT = 0, COPY will commit just once - at the end.

COPYTYPECHECK {OFF|ON}
Suppres the comparison of datatypes while inserting or appending to DB2

DEF[INE] {&|c|OFF|ON}
c = the char used to prefix substitution variables.
ON or OFF controls whether to replace substitution variables with their values.
(this overrides SET SCAN)

DESCRIBE [DEPTH {1|n|ALL}][LINENUM {ON|OFF}][INDENT {ON|OFF}]
Sets the depth of the level to which you can recursively describe an object
(1 to 50) see the DESCRIBE command

ECHO {OFF|ON}
Display commands as they are executed

EMB[EDDED] {OFF|ON}
OFF = report printing will start at the top of a new page.
ON = report printing may begin anywhere on a page.

ESC[APE] {\|c|OFF|ON}
Defines the escape character. OFF undefines. ON enables.

FEED[BACK] {6|n|OFF|ON}
Display the number of records returned (when rows >= n )
OFF (or n=0) will turn the display off
ON will set n=1

FLAGGER {OFF|ENTRY|INTERMED[IATE]|FULL}
Checks to make sure that SQL statements conform to the ANSI/ISO SQL92 standard.
non-standard constructs are flagged as errors and displayed
See also ALTER SESSION SET FLAGGER.

FLU[SH] {OFF|ON}
Buffer display output (OS)
(no longer used in Oracle 9)

HEA[DING] {OFF|ON}
print column headings

HEADS[EP] {||c|OFF|ON}
Define the heading separator character (used to divide a column heading onto > one line.)
OFF will actually print the heading separator char
see also: COLUMN command

INSTANCE [instance_path|LOCAL]
Change the default instance for your session, this command may only be issued when
not already connected and requires Net8

LIN[ESIZE] {150|n}
Width of a line (before wrapping to the next line)
Earlier versions default to 80, Oracle 9 is 150

LOBOF[FSET] {n|1}
Starting position from which CLOB and NCLOB data is retrieved and displayed

LOGSOURCE [pathname]
Change the location from which archive logs are retrieved during recovery
normally taken from LOG_ARCHIVE_DEST

LONG {80|n}
Set the maximum width (in chars) for displaying and copying LONG values.

LONGC[HUNKSIZE] {80|n}
Set the fetch size (in chars) for retrieving LONG values.

MARK[UP] HTML [ON|OFF]
[HEAD text] [BODY text] [TABLE text]
[ENTMAP {ON|OFF}][SPOOL {ON|OFF}]
[PRE[FORMAT] {ON|OFF}]
Output HTML text, which is the output used by iSQL*Plus.

NEWP[AGE] {1|n}
The number of blank lines between the top of each page and the top title.
0 = a formfeed between pages.

NULL text
Replace a null value with 'text'
The NULL clause of the COLUMN command will override this for a given column.

NUMF[ORMAT] format
The default number format.
see COLUMN FORMAT.

NUM[WIDTH] {10|n}
The default width for displaying numbers.

PAGES[IZE] {14|n}
The height of the page - number of lines.
0 will suppress all headings, page breaks, titles

PAU[SE] {OFF|ON|text}
press [Return] after each page
enclose 'text' in single quotes

RECSEP {WR[APPED]|EA[CH]|OFF}
Print a single line of the RECSEPCHAR between each record.
WRAPPED = print only for wrapped lines
EACH=print for every row

RECSEPCHAR {_|c}
Define the RECSEPCHAR character, default= ' '

SCAN {OFF|ON}
OFF = disable substitution variables and parameters

SERVEROUT[PUT] {OFF|ON} [SIZE n] [FOR[MAT] {WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]}]
whether to display the output of stored procedures (or PL/SQL blocks)
i.e., DBMS_OUTPUT.PUT_LINE

SIZE = buffer size (2000-1,000,000) bytes

SHOW[MODE] {OFF|ON}
Display old and new settings of a system variable

SPA[CE] {1|n}
The number of spaces between columns in output (1-10)

SQLBL[ANKLINES] {ON|OFF}
Allow blank lines within an SQL command. reverts to OFF after the curent command/block.

SQLC[ASE] {MIX[ED]|LO[WER]|UP[PER]}
Convert the case of SQL commands and PL/SQL blocks
(but not the SQL buffer itself)

SQLPLUSCOMPAT[IBILITY] {x.y[.z]}
Set the behavior or output format of VARIABLE to that of the
release or version specified by x.y[.z].

SQLCO[NTINUE] {> |text}
Continuation prompt (used when a command is continued on an additional line using a hyphen -)

SQLN[UMBER] {OFF|ON}
Set the prompt for the second and subsequent lines of a command or PL/SQL block.
ON = set the SQL prompt = the line number.
OFF = set the SQL prompt = SQLPROMPT.

SQLPRE[FIX] {#|c}
set a non-alphanumeric prefix char for immediately executing one line of SQL (#)

SQLP[ROMPT] {SQL>|text}
Set the command prompt.

SQLT[ERMINATOR] {;|c|OFF|ON}|
Set the char used to end and execute SQL commands to c.
OFF disables the command terminator - use an empty line instead.
ON resets the terminator to the default semicolon (;).

SUF[FIX] {SQL|text}
Default file extension for SQL scripts

TAB {OFF|ON}
Format white space in terminal output.
OFF = use spaces to format white space.
ON = use the TAB char.
Note this does not apply to spooled output files.
The default is system-dependent. Enter SHOW TAB to see the default value.

TERM[OUT] {OFF|ON}
OFF suppresses the display of output from a command file
ON displays the output.
TERMOUT OFF does not affect the output from commands entered interactively.

TI[ME] {OFF|ON}
Display the time at the command prompt.

TIMI[NG] {OFF|ON}
ON = display timing statistics for each SQL command or PL/SQL block run.
OFF = suppress timing statistics

TRIM[OUT] {OFF|ON}
Display trailing blanks at the end of each line.
ON = remove blanks, improving performance
OFF = display blanks.
This does not affect spooled output.
SQL*Plus ignores TRIMOUT ON unless you set TAB ON.

TRIMS[POOL] {ON|OFF}
Allows trailing blanks at the end of each spooled line.
This does not affect terminal output.

UND[ERLINE] {-|c|ON|OFF}
Set the char used to underline column headings to c.

VER[IFY] {OFF|ON}
ON = list the text of a command before and after replacing substitution variables with values.
OFF = dont display the command.

WRA[P] {OFF|ON}
Controls whether to truncate or wrap the display of long lines.
OFF = truncate
ON = wrap to the next line
The COLUMN command (WRAPPED and TRUNCATED clause) can override this for specific columns.

How to keep Source code creation History

How to keep Source code creation History
==========================================

CREATE TABLE halim.SOURCE_HIST
(
CHANGE_DATE DATE NULL,
NAME VARCHAR2(30 BYTE) NULL,
TYPE VARCHAR2(12 BYTE) NULL,
LINE NUMBER NULL,
TEXT VARCHAR2(4000 BYTE) NULL
)


CREATE OR REPLACE TRIGGER change_hist
AFTER CREATE ON halim.SCHEMA
DECLARE
BEGIN
IF ora_dict_obj_type IN
('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'TYPE')
THEN
INSERT INTO source_hist
SELECT SYSDATE, user_source.*
FROM user_source
WHERE TYPE = ora_dict_obj_type AND NAME = ora_dict_obj_name;
END IF;
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20000, SQLERRM);
END;
/


SELECT DISTINCT change_date, NAME, TYPE
FROM source_hist
ORDER BY change_date DESC