Basic monitoring requirement for Oracle database?

Please find the basic monitoring setup required for Oracle database.

1.Tablespace monitoring
2.File system monitoring
3.alert log monitoring
4.pmon status
5.Listener status
6.database block corruption

Backup monitoring
------------------------
1.backup start/completion status monitoring
2.in case of RMAN  backup ,monitor sync status with catalog database.
3.Incase of hot backup ,check Db datafiles whether it come out of begin backup once backup completes.

Data gaurd Monitoring
------------------------
1.check both Primary & standby SYnced always
2.check on standby MRP process


ASM monitoring
------------------
1.check the free_MB of each Disk
2.check the Disk status

RAC monitoring
--------------------
1.check the CRS,CSSD,EVMD and alert if its down
2.check the cluster status

Golden gate monitoring
-----------------------
1.check the extract/replicat/manager/datapump process
2.check the record count on both source & Target tables


performance tuning Monitoring
--------------------------------
1.check if the server CPU goes beyond certain threshold  level
2.check if any SQL has multiple hash value
3.check periodic stats on Database
4.check periodic defragmentation happening on application objects
5.check if any DML locks occuring on database

what are the MAJOR areas you need to check when user facing some performance issues on oracle database?

we need to look into the below areas when you user reported  any performance issues on oracle database.

Below are the most of the common root cause for delay in SQL processing in database

------------------------------------------------------------------------------------------------------------

1.no proper stats for the sql

2.no proper indexing for the sql

3.lock while executing the sql

4.waits while executing the sql

5.more I/O while executing the sql

6.fragmentation on the objects of sql execution

7.due to poor memory configuration on database

8.due to library cache lock

9.could be locked /stale stats

10.could be poor resource on the server
11.check if any profiles disabled
12.check if any baseline modified
13.check the sql execution time for current and past dates

Various RDBMS comparisons and characteristics

	Oracle	IBM DB2 LUW	MS SQL	Postgre SQL	IBM Netezza
to connect to DB	set sid,conn / as sysdba	DB2 connect to <Db name>	thru SSMS servername/instance name	pgsql,pgsql/c <Db name>	nzsql,nzsql/c <Db name>
List Db's on server	ps -ef|grep pmon	db2 list database;	select name from sys.databases;	SELECT datname FROM pg_database	select name from _v_database;
default port	1521	50000	1433	5432	5480
backup methods	cold,hot,rman,expdp/exp	offline/online,export	full,log,table level	pgdump	full,Incremental,table export
instance/DB's	one instance per DB except Rac	one instance many Db's	one instance many Db's	one instance many DB's	one instance many Db's
to start/shutdown DB	set sid,conn / as sysdba	db2start/db2stop	SSMS/offline/online	su postgres -c 'pg_ctl start -D /usr/local/pgsql/data -l serverlog'	nzstop/nzstart
	startup/shutdown
error log	alert_sid.log	db2diag.log	USE master	show log_directory ;	pg.log,servermgr.log
			GO	pg_log
			xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, N'asc'
Monitoring	Grid/OEM			pgwatch,Open PostgreSQL Monitoring (OPM)	nzportal
High availability	RAC/DG/ADG	HADR	MS cluter/Mirroring	PGCluster is a multi-master and synchronous replication tool	OS cluster/replication
database backup	rman>backup database ;	QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;BACKUP DB <db>  TO "/home/offline_full"  COMPRESS;	BACKUP DATABASE [db] TO  DISK = 'F\db_backup_2015_09_02_210001_2075068.bak' WITH NOFORMAT, NOINIT,  NAME = N'db_delete-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10	pg_dump -U {user-name} {source_db} -f {dumpfilename.sql}	nzbackup -dir /home/user/backups -u user -pw password -db db1
database restore	rman>restore database	RESTORE DB <db>  FROM "/backup/offline_full"  TAKEN AT <timestamp>  INTO <db2>;	RESTORE DATABASE [db] FROM  DISK = 'F\db_.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 10	$ psql -U {user-name} -d {desintation_db}-f {dumpfilename.sql}	nzrestore -db db1 -u user -pw password -dir /home/user/backups -v
current Db connections	select * from v$session;	db2 list application all;	sp_who2	SELECT usesysid, usename FROM pg_stat_activity;	nzsession
super user	sys,system	SYSADM, SYSCTRL	SA,SYSADMIN	postgres(OS)	NZ(os user) SYSTEM DB usr
Db config files	init.ora/spfile	db2cfg,dbmcfg
Db files	ctl,redo,data	container data	data,log		NO TBS/everything in DISK

what are the differant oracle standby protection MODES

standby protection MODES
----------------------------------

	Max performance	max availability	max protection
type	default
data loss	will be there	will be there	No data lost
DG type	P and L	P and L	P and L
SRedoLog	No,recommended	needed	needed
N/w Transmission mode	Async	Sync	Sync
Disk write operation	affirm	affirm	Noaffirm
response times	primry response good	prmry  response latency	prmry  response latency
If standby Down	prmy will run	prmy will run	prmy will NOT run
redo archive Process	LGWR or ARCH	LGWR	LGWR
log transport	ARCH	SRL	SRL
net_timout # tries			20 ,then it will fail
real time apply	yes ,provided SRl	yes	yes

some possible state of ASM DISK

some possible state of ASM DISK
--------------------------------

UNKNOWN - Automatic Storage Management disk state is not known (typically the disk is not mounted)

NORMAL - Disk is online and operating normally

ADDING - Disk is being added to a disk group, and is pending validation by all instances that have the disk group mounted

DROPPING - Disk has been manually taken offline and space allocation or data access for the disk halts. Rebalancing will commence to relocate data off the disks to other disks in the disk group. Upon completion of the rebalance, the disk is expelled from the group.

HUNG - Disk drop operation cannot continue because there is insufficient space to relocate the data from the disk being dropped

FORCING - Disk is being removed from the disk group without attempting to offload its data. The data will be recovered from redundant copies, where possible.

DROPPED - Disk has been fully expelled from the disk group

some possible mount_status of ASM DISK

some possible mount_status of ASM DISK
--------------------------------------------


MISSING - Automatic Storage Management metadata indicates that the disk is known to be part of the Automatic Storage Management disk group, but no disk in the storage system was found with the indicated name

CLOSED - Disk is present in the storage system but is not being accessed by Automatic Storage Management

OPENED - Disk is present in the storage system and is being accessed by Automatic Storage Management. This is the normal state for disks in a database instance which are part of a Disk Group being actively used by the instance.

CACHED - Disk is present in the storage system, and is part of a disk group being accessed by the Automatic Storage Management instance. This is the normal state for disks in an Automatic Storage Management instance which are part of a mounted disk group.

IGNORED - Disk is present in the system, but is ignored by ASM because of one of the following:

- The disk is detected by the system library, but is ignored because an ASM library discovered the same disk

- ASM has determined that the membership claimed by the disk header is no longer valid

CLOSING - ASM is in the process of closing this disk

Some possible header_status of ASM disk

Some possible header_status of ASM disk
----------------------------------------

UNKNOWN - Automatic Storage Management disk header has not been read

CANDIDATE - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement

INCOMPATIBLE - Version number in the disk header is not compatible with the Automatic Storage Management software version.

PROVISIONED - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement. The PROVISIONED header status is different from the CANDIDATE header status in that PROVISIONED implies that an additional platform-specific action has been taken by an administrator to make the disk available for Automatic Storage Management.

MEMBER - Disk is a member of an existing disk group. No attempt should be made to add the disk to a different disk group. The ALTER DISKGROUP statement will reject such an addition unless overridden with the FORCE option

FORMER - Disk was once part of a disk group but has been dropped cleanly from the group. It may be added to a new disk group with the ALTER DISKGROUP statement.

CONFLICT - Automatic Storage Management disk was not mounted due to a conflict

FOREIGN - Disk contains data created by an Oracle product other than ASM. This includes datafiles, logfiles, and OCR disks.

Some possible statuses for the MRP process in data gaurd

Some possible statuses for the MRP in data gaurd
-------------------------------------------------
ERROR - This means that the process has failed. See the alert log or v$dataguard_status for further information.

WAIT_FOR_LOG - Process is waiting for the archived redo log to be completed. Switch an archive log on the primary and requery v$managed_standby to see if the status changes to APPLYING_LOG.

WAIT_FOR_GAP - Process is waiting for the archive gap to be resolved. Review the alert log to see if FAL_SERVER has been called to resolve the gap.

APPLYING_LOG - Process is applying the archived redo log to the standby database.

How to drop golden gate user from oracle database?

you cant drop the golden gate user when DDL replication set up in ON.

system wont allow you to drop the user  and you will get the below error.

Error ORA-20782 while deleting Golden Gate User

we can drop the user in TWO ways.

1.disable & Drop the trigger associated with it then drop the user.

2.SQL> @ddl_disable.sql

SQL> @ddl_remove.sql

SQL> @marker_remove.sql

-- Now try deleting the user and remove the installed directory.

SQL> drop user ggadmin cascade;

How to find what are the nodes are in RAC cluster

Here is the list  of methods to finds to nodes associated with RAC cluster
1.login to any one of the DB and do select host_name from GV$instance;
2../crsctl check cluster -all
3.olsnodes 

scrip to check temporary tablespace usage by each SQL_IDS in oracle database

select to_char(BEGIN_INTERVAL_TIME,'YYYY-MM-DD HH24:MI'),sql_id,sorts_delta
from dba_hist_sqlstat a, dba_hist_snapshot b
where a.snap_id=b.snap_id and to_char(BEGIN_INTERVAL_TIME,'YYYY-MM-DD HH24:MI')
between '2015-10-05 22:00' and '2015-10-05 23:30' order by sorts_delta desc;

scrip to find sql server last startup time

SELECT    [sqlserver_start_time] AS [LastStartupDate] FROM    [sys].[dm_os_sys_info]

oracle database live monitoring 11g

we may use the below view for Db live monitoring 11g onwards.


 select sql_text ,status,username from V$SQL_MONITOR;

script to check database resource utilization history in oracle database

Please use the below view to find the resource history usage details with specific time frame.

select CURRENT_UTILIZATION,MAX_UTILIZATION,LIMIT_VALUE from DBA_HIST_RESOURCE_LIMIT where RESOURCE_NAME like '%processes%' and SNAP_ID='13011';

scrip to check catalog db synced with rman repository?

select DB_NAME,DB_STATUS,RESYNC_TIME,RESYNC_TYPE from  rman.RC_RESYNC where DB_NAME='DB_NAME';

DB_NAME  DB_STAT RESYNC_TIME RESYNC_
-------- ------- --------- -------
DB_NAME      OPEN    30-OCT-14 FULL

steps to perform table-reorganization using DBMS_REDEFINITION on oracle table

1.DETERMINE IF THE TABLE CAN BE REDEFINED ONLINE

BEGIN
   DBMS_REDEFINITION.CAN_REDEF_TABLE('schema_name','original_TABLE', DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

2.CREATE THE INTERIM TABLE

CREATE TABLE "schema_name"."original_TABLE_INTERIM" AS SELECT * FROM "schema_name"."original_TABLE" WHERE 1=2;

3.PARALLELISM FOR LARGE TABLES

ALTER SESSION FORCE PARALLEL DML PARALLEL 8;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 8;

4.START THE REDEFINITION

BEGIN
   DBMS_REDEFINITION.START_REDEF_TABLE(
                 uname => 'schema_name',
                 orig_table => 'original_TABLE',
                 int_table => 'original_TABLE_INTERIM',
                 options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

5.COPY THE TABLE DEPENDENTS FROM THE ORIGINAL TABLE TO THE INTERIM TABLE

DECLARE
   error_count pls_integer := 0;
BEGIN
   DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('schema_name', 'original_TABLE', 'original_TABLE_INTERIM', dbms_redefinition.cons_orig_params, TRUE,TRUE,TRUE,FALSE, error_count);
   DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/


6.check for invalid objects

select count(*),status from dba_objects where owner='schema_name' group by status;

select object_name from DBA_OBJECTS where owner='schema_name' and status='INVALID';

7.DO ONE FINAL SYNCHRONIZE BEFORE FINISHING THE REDEFINITION

BEGIN
   DBMS_REDEFINITION.SYNC_INTERIM_TABLE('schema_name', 'original_TABLE', 'original_TABLE_INTERIM');
END;
/

8.FINISH THE REDEFINITION

exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('schema_name', 'original_TABLE', 'original_TABLE_INTERIM');

9.Drop the interim table
drop TABLE "schema_name"."original_TABLE_INTERIM"

how to get the index size without creating the index on table?

There are multiple ways we can calculate the Index size without creating it.
1.just check the table size and based on the number of columns used on the index we may put the approx index size.
2.just take the explain plan for the index create statement ,it will show you the index size.

script to grant read/write access to user from all Db users in oracle database?

Here is the script which I used to pull all the grants and push to ROLE.
then grant to role to user.

select 'grant select ,update,insert,delete on '||owner||'.'||view_name||' to RW_ROLE;' from dba_views where owner in (select username from dba_users where username not in ('SYSTEM','DBSNMP','SYS','OUTLN','OLAPSYS','CTXSYS','EM_MONITOR','SYSMAN') );

select 'grant select ,update,insert,delete on '||owner||'.'||table_name||' to RW_ROLE;' from dba_tables where owner in (select username from dba_users where username not in ('SYSTEM','DBSNMP','SYS','OUTLN','OLAPSYS','CTXSYS','EM_MONITOR','SYSMAN') );

steps to generate fake error in oracle database alert log

11g on-wards

alter session set events '942 incident(FAKE_ERROR_GENERATED)';
drop table KILL_NOTEXIST;
alter session set events '942 trace name context off';.

Steps to import same table as different table name in oracle database?

here is the steps:
1.take the expdp of the table  scott.emp

2. impdp scott/scott tables=EMP remap_table=emp:emp_temp3 directory=DATA_PUMP_DIR dumpfile=EMP_table.dmp logfile=impdpEMP3.log

it will create new table as emp_temp3

scrip to detach /attach a sql database from instance?

USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'DB_NAME'
GO

CREATE DATABASE [test2] ON ( FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test2.mdf'), ( FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test2_1.ldf' ) FOR ATTACH ;

script to create sql database manually?

USE [master]
GO

CREATE DATABASE [test2] ON  PRIMARY
( NAME = N'test', FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test2.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'test_log', FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test2_1.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [test2] SET COMPATIBILITY_LEVEL = 100
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [test2].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [test2] SET ANSI_NULL_DEFAULT OFF
GO

ALTER DATABASE [test2] SET ANSI_NULLS OFF
GO

ALTER DATABASE [test2] SET ANSI_PADDING OFF
GO

ALTER DATABASE [test2] SET ANSI_WARNINGS OFF
GO

ALTER DATABASE [test2] SET ARITHABORT OFF
GO

ALTER DATABASE [test2] SET AUTO_CLOSE OFF
GO

ALTER DATABASE [test2] SET AUTO_CREATE_STATISTICS ON
GO

ALTER DATABASE [test2] SET AUTO_SHRINK OFF
GO

ALTER DATABASE [test2] SET AUTO_UPDATE_STATISTICS ON
GO

ALTER DATABASE [test2] SET CURSOR_CLOSE_ON_COMMIT OFF
GO

ALTER DATABASE [test2] SET CURSOR_DEFAULT  GLOBAL
GO

ALTER DATABASE [test2] SET CONCAT_NULL_YIELDS_NULL OFF
GO

ALTER DATABASE [test2] SET NUMERIC_ROUNDABORT OFF
GO

ALTER DATABASE [test2] SET QUOTED_IDENTIFIER OFF
GO

ALTER DATABASE [test2] SET RECURSIVE_TRIGGERS OFF
GO

ALTER DATABASE [test2] SET  DISABLE_BROKER
GO

ALTER DATABASE [test2] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO

ALTER DATABASE [test2] SET DATE_CORRELATION_OPTIMIZATION OFF
GO

ALTER DATABASE [test2] SET TRUSTWORTHY OFF
GO

ALTER DATABASE [test2] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO

ALTER DATABASE [test2] SET PARAMETERIZATION SIMPLE
GO

ALTER DATABASE [test2] SET READ_COMMITTED_SNAPSHOT OFF
GO

ALTER DATABASE [test2] SET HONOR_BROKER_PRIORITY OFF
GO

ALTER DATABASE [test2] SET  READ_WRITE
GO

ALTER DATABASE [test2] SET RECOVERY SIMPLE
GO

ALTER DATABASE [test2] SET  MULTI_USER
GO

ALTER DATABASE [test2] SET PAGE_VERIFY CHECKSUM
GO

ALTER DATABASE [test2] SET DB_CHAINING OFF
GO



GO

scrip to restore sql database from full backup?

RESTORE DATABASE [test] FROM  DISK = N'C:bakup\test.bak' WITH  FILE = 2,  NOUNLOAD,  STATS = 10

script to take full database backup on sql server?

BACKUP DATABASE [test] TO  DISK = N'C:\Backup\test.bak' WITH NOFORMAT, NOINIT,  NAME = N'bala-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

scrip to check stale stats in oracle database?

col TABLE_NAME for a30
col PARTITION_NAME for a20
col SUBPARTITION_NAME for a20
select OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS,LAST_ANALYZED from dba_TAB_STATISTICS where STALE_STATS='YES';

steps to run segment advisor for table using OEM?

1.GO TO OEM  home page -->administration--.storage-->segment advisor
2.provide the table name or schema name to search and submit the job
3.once the job completed ,view the result and recommendations

steps to run awr report for a specific sql_id?

we can generate awr report for a specific  sql_ID

use the below script.

1.@?/rdbms/admin/awrsqrpt.sql;

scrip to check the most expensive sql on the cursor cache?

SQL> @?/rdbms/admin/sqltrpt.sql;

15 Most expensive SQL in the cursor cache
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL_ID           ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------

steps to manually run the sql_tunning advisor for sql_id in oracle database?

1. Create SQL Tuning Advisor task

DECLARE
  my_task_name VARCHAR2(30);
begin
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'd6v2m5q1t3hpp',scope => 'COMPREHENSIVE',time_limit => 60,task_name => 'my_tune',description => '7a6b4442j5pcz');
end;
/

2. Run Task
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'my_tune');

3. View results
SET LONG 10000
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_tune') from dual;


4.drop the sql_tunning task

exec DBMS_SQLTUNE.drop_tuning_task (task_name => 'my_tune');

5.to list the tunning task job details
dba_advisor_tasks,DBA_ADVISOR_FINDINGS

steps to compare the database performance between two time stamps in oracle database?

1.generate the AWR manually and check the performance of the SQL's and TOP sql's
2.use OEM tool ,go to AWR  & compare awr for two time frames by snapshot  .

steps to check sql is hard/soft parse ,#execution,rows_processed in oracle database?

1.check from oem HISTORY FOR CURSOR AND HISOTRIC DATA
2.USE v$SQL,v$SQLAREA,DBA_HIST_SQLSTAT VIEWS TO GET THE INFORMATION

steps to check when the SQL executed on the oracle database?

1.go to OEM page ->performance->sql-->search sql from AWR if its history
2.select sql_id from dba_hist_sqlstat  where sql_text  like 'select * from emp%'; if its history
3. select sql_id from V$sql where sql_text like 'select ename%'; if its in cursor