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]