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