How to connect SQLserver from cmd

Syntax:
C:net use \\server_name /u:corporateNetwrkname\ur_sqldba_id
C:query session /server:server_name

C:\xx_dba>net use \\srvr_name /u:c.network\xx_dba

The command completed successfully.

C:\xx_dba>query session /server:srvr_name
SESSIONNAME USERNAME ID STATE TYPE DEVICE
console 0 Conn wdcon
rdp-tcp 65536 Listen rdpwd
rdp-tcp#26 xx_dba 2 Active rdpwd

(or) can use tsadmin

Connect isql from command prompt in local machine:

C:\Documents and Settings\xx_dba>osql -E -s SERVER_NAME
1> select spid,cmd,blocked from sysprocesses where blocked>0
2> go
spid Gcmd Gblocked
------G----------------G-------

(0 rows affected)

connect isql from remote machine or from ur local box(sql should install/path)

H:\>osql -U -P -S SERVER_NAME

Pharmacy topics

(R7503) PHARMACOLOGY – II
http://www.empr.com/cardiovascular-system/hypertension/pnote/143/
http://www.toodoc.com/essential-of-pharmacology-by-K-D-Tripathi-ebook.html
Pharmacology of Cardiovascular System - Hypertension & congestive heart failure
http://www.emedicinehealth.com/congestive_heart_failure/article_em.htm
UNIT-II Pharmacology of Drugs used in coronary artery disease
http://www.emedicinehealth.com/slideshow_heart_disease/article_em.htm

What's oracle DBA and Oracle Application DBA

Basically to maintain Oracle Applications (11i or earlier versions) you need mainly three kind of expertise, apart from others:
Oracle Apps Technical
Oracle Apps Functional
Oracle Apps DBA
Oracle Apps DBA should be one who is having knowledge of Oracle Database Administration along with some of the skills like:Oracle Applications Cloning (Database clone included). Cloning with Rapid clone and Autoconfig Clone etc.Patching using Adpatch utility (patchset and Family Pack etc).Knowledge of MLS.Understanding Application issue w.r.to Database.Conceptual knowledge of Apache, Jserv, Forms & Reports Server, CM etc.Comfortable with OA-Frame Work, iProcurement, iPayment, Configurator, Workflow etc.
Can refer best article.. http://teachmeoracle.com/
http://onlineappsdba.com/index.php/2006/07/29/how-2-become-oracle-apps-dba/
apps contents..http://download-uk.oracle.com/docs/cd/B25516_08/current/html/docset.html
Basic Info..go thru first chapter in below link
http://download-uk.oracle.com/docs/cd/B25516_08/current/acrobat/11iconcepts.pdf

Oralce 11gR2 Performance Tuning

Tools for tuning the db:

Oracle Database 11g Enterprise Edition
Oracle Enterprise Manager
Oracle Diagnostics Pack

- AWR, Automatic workload repository

- ADDM, automatic database diagnostic monitor

- ASH, Active session History

Oracle Database Tuning Pack

--SQL Tuning Advisor
This feature enables you to submit one or more SQL statements as input and receive output in the form of specific advice or recommendations for how to tune statements, along with a rationale for each recommendation and its expected benefit. A recommendation relates to collection of statistics on objects, creation of new indexes, restructuring of the SQL statements, or creation of SQL profiles.
– SQL Access Advisor
This feature enables you to optimize data access paths of SQL queries by recommending the proper set of materialized views and view logs, indexes, and partitions for a given SQL workload.

Oracle Real Application testing

- Database Replay

- SQL Performance analyzer

Performance improvement is an iterative process,removing the first bottleneck may not lead to performance improvement immediately becz another bottleneck leads greater performance impact, so accurately diagnosing the performance proplem is the fist step.

performance proplems results lack of throughput(the amount of work that can be completed in a specified time), unacceptable user or job response time

Gathering Database Statistics Using the Automatic Workload Repository

By default, the database gathers statistics every hour and creates an AWR snapshot, which is a set of data for a specific time that is used for performance comparisons

Initialization parameters are relevant for AWR:

STATISTICS_LEVEL, CONTROL_MANAGEMENT_PACK_ACCESS

statistics_level - TYPICAL or ALL -- for enabling AWR reports

control_management_pack_access - DIAGNOSTIC+TUNING - for enablin ADDM

Setting the DBIO_EXPECTED Parameter
ADDM analysis of I/O performance partially depends on a single argument,DBIO_EXPECTED, that describes the expected performance of the I/O subsystem. The value of DBIO_EXPECTED is the average time it takes to read a single database block, in microseconds. Oracle Database uses the default value of 10 milliseconds..

Set the value one time for all subsequent ADDM executions.For example, if the measured value is 8000 microseconds, then execute the following PL/SQL code as the SYS user:
EXECUTE DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER('ADDM', 'DBIO_EXPECTED', 8000);

The database statistics collected and processed by AWR include:

■ Time Model Statistics■ Wait Event Statistics■ Session and System Statistics■ Active Session History Statistics■ High-Load SQL Statistics

Common Performance Problems Found in Oracle Databases

CPU Bottlenecks

Undersized Memory Strucutures

I/O Capacity issues

Suboptimal use of Oracle Database by the application

Concurrency issues

Database configuration issues
Short-lived performance problems

Degradation of database performance over time

Inefficient or high-load SQL statements

Object contention

Unexpected performance regression after tuning SQL statements

Sybase Prod issues..

Tempdb full

–Check the error log if it is the data segment or the log segment that is full
–If it is the log segment, do the following sequentially:
Try to truncate the log
dump tran tempdb with truncate_only
dump tran tempdb with no_log
Check the spid causing the log to get full
select * from master..syslogshold where dbid = 2
Or use d_dbdba002, for the tables syslocks_copy, sysprocesses_copy, syslogshold_copy to check the process that has caused tempdb to get full
Check with the user about the spid identified and kill the spid after confirmation with the application contact
Try to abort the transaction using lct_admin
Select lct_admin("abort",spid)
spid in the syntax is that of the process that initiated the transaction. This also terminates any other suspended transactions in the log that belong to the specified process. Ensure that this is notified to the user and user approval is taken prior to executing this command
Retry executing the dump tran commands
Alter the tempdb on the device that has space

If there is no space on any of the existing devices then create a database device in the location : /app/sybase//tempdb
Check for the name of the last tempdb device created on this server
-Create the new device in the sequential order as per the name in the server
In worst case scenario where no space is available anywhere in the server, extend the tempdb on any device with free space available as the tempdb can be shrunk during a subsequent restart of the server by making changes to the sysusages table

Blocking process

–Execute the following query to check the process that is blocking:
select spid, suser_name(suid), db_name(dbid), blocked from sysprocesses where blocked > 0
Check the physical io for the process:
sp_whodo ‘blocking process’
select spid, suser_name(suid), db_name(dbid), physical_io, memusage from sysprocesses where spid = BlockingSpid
sp_lock ‘BlockingSpid’

Open tran issue..

–Execute the following query to check the process that is open:
select db_name(dbid), spid, starttime, name from master..syslogshold where dbid = db_id('dbname') order by starttime
Check the physical io for the Open Transaction:
sp_whodo ‘OpenTranSpid’
select spid, suser_name(suid), db_name(dbid), physical_io, memusage from sysprocesses where spid =OpenTranSpid
sp_lock ‘OpenTranSpid’

LogSuspend issue..

Check the spid causing the log to get full
select db_name(dbid), spid, starttime, name from master..syslogshold where dbid = db_id('dbname')
Check with the user if the spid has been identified and kill the spid after confirmation with the application contact
If the spid belongs to the truncation point for a replicate database check if there is an issue with replication. If so, correct the problem with replication
Check if the transaction log can be dumped:
Execute the ncycle job for dumping the transaction
Execute dump tran dbname with truncate_only (This will invalidate the future tran dumps)
Execute dump tran dbname with no_log (This will invalidate the future trans dumps)
Check for an existing device where the database can be extended and extend the database
Alter the database on the device that has space
Ensure that the database does not have a data fragment on that device already

Try to abort the transaction using lct_admin
Select lct_admin("abort",spid)
Performance issues..

–Log into the server and check the following:
Check if there are any known issues with that server/region (outages etc.)
Execute sp_monitor and check for CPU utilization
Check if there is blocking
Check if there are issues with the tempdb
Check if there are any open transactions
Ask the application user, if he is aware of the process that is causing the problem
If yes, get the query plan of the process
Look out for table scans, deferred modifications. If you find any, suggest using indexes for the query
If the query is using the correct indexes, check from when has the issue been recurring. If it is a recent issue, check for the following:
Was update statistics successfully executed
Did the index rebuild job complete successfully

If the application user is not aware as to what process is causing the problem, execute the following query:
select spid,status,hostname,program_name,hostprocess,cmd, cpu,physical_io from sysprocesses where status like 'runn%'
Generally the spid showing cpu and physical_io as 0 is the prime candidate to be killed
How to use VCS Commands

Login to the host as sybase to start the servers...
1) STATUS
/opt/VRTSvcs/bin/hastatus -sum grep

) SHUTDOWN
/opt/VRTSvcs/bin/hagrp -offline sg_dbservername -sys lns66h-6001e
3) START
/opt/VRTSvcs/bin/hagrp -online sg_dbservername -sys lns66h-6001e

About Quorum resource in Windows Cluster

Quorum resource
Updated: January 21, 2005
Applies To: Windows Server 2003, Windows Server 2003 R2, Windows Server 2003 with SP1, Windows Server 2003 with SP2
Quorum resource
In every cluster, a single resource is designated as the quorum resource. This resource maintains the configuration data necessary for recovery of the cluster. This data, in the form of recovery logs, contains details of all of the changes that have been applied to the cluster database. This provides node-independent storage for cluster configuration and state data.
Quorum resource and the cluster database
The cluster database is an integral part of the formation of a server cluster. When a node joins or forms a cluster, the Cluster service must update the node's private copy of the cluster database. When a node joins an existing cluster, the Cluster service can retrieve the data from the other active nodes. However, when a node forms a cluster, no other node is available. The Cluster service uses the quorum resource's recovery logs to update the node's cluster database. To ensure cluster unity, Windows Server 2003, Enterprise Edition and Windows Server 2003, Datacenter Edition use the quorum resource to ensure that only one set of active, communicating nodes is allowed to operate as a cluster. A node can form a cluster only if it can gain control of the quorum resource. A node can join a cluster or remain in an existing cluster only if it can communicate with the node that controls the quorum resource.
For more information on the cluster database, see Cluster database.
For more information on cluster resources, see Server Cluster Resources.
Scenarios
To illustrate the importance of the quorum resource, consider these situations in a simple two-node cluster:
Node 1 fails. Node 2 continues operating, writing changes to the cluster database. But before node 1 can be restarted, node 2 fails. When node 1 becomes active, it must update its private copy of the cluster database with the changes made by node 2. The Cluster service uses the quorum resource's recovery logs to perform the update.
The networks providing communication between nodes 1 and 2 fail. Each node assumes the other node has failed and attempts to continue operating as the cluster. If both could succeed, the result would be two separate clusters using the same cluster name and competing for the same resources. Windows Server 2003, Enterprise Edition and Windows Server 2003, Datacenter Edition use quorum resource ownership to prevent this problem and maintain cluster unity. In this scenario, the node that gains control of the quorum resource is allowed to form a cluster, and the other fails over its resources and becomes inactive.
The quorum resource can be any resource with the following attributes:
The resource must enable a single node to gain physical control of it and defend its control.
The resource must provide physical storage that can be accessed by any node in the cluster.
The resource must use the NTFS file system.
Types of quorum resources
There are two resources that come with the operating system that can act as a quorum resource for multinode clusters. They are:
The Physical Disk resource
The Majority Node Set resource
For single-node clusters, the Local Quorum resource acts as a quorum resource. However, other developers can create their own quorum resource types for any resources that meet the arbitration and storage requirements

SQL Server Basic Prod DBA commands..

Blocking in db server..
select spid,cmd,blocked from sysprocesses where blocked>0
select spid,blocked,cmd,memusage,physical_io from sysprocesses order by memusage desc
select spid,blocked,cmd,memusage,physical_io from sysprocesses order by physical_io desc
select spid, loginame, db_name (dbid) 'dbname',status, cmd,hostname from sysprocesses where db_name (dbid) = 'P_DBAPMCOR01' order by dbid
sp_who2 "spid"
dbcc traceon(3604)
dbcc inputbuffer(spid)
dbcc sqltext(spid)
sp_showplan spid,null,null,null
Space Usage check for tempdb or Userdb:
xp_fixeddrives
dbcc sqlperf(logspace)
dbcc shrinkdatabase(tempdb)
sp_helpdb d_dbdba002 - db name
dbcc shrinkfile('L001') - logfilename
other commands
sp_whodo n_cycle
xp_cmdshell "tlist"
xp_cmdshell "nc_restart servername jobname"
xp_readerrorlog
select @@errorlog
select @@boottime
select @@servername
select getdate()
select @@version
nc_task "jobname"
select crdate from sysdatabases where dbid=2
select suser_name(suid),count(*) from sysprocesses group by suid
select paramname, paramvalue from d_dbdba002..task_params where paramname = 'DUMP_GENERATIONS'
select loginname , createdate,updatedate,accdate from master..syslogins where loginname like '%sysfaonycapmsvc%'
Last Gather Stats
STATS_DATE function takes two arguments table_id and index_id. Following is the SQL followed by output. You can run it from query analyzer or management studio.
SELECT t.table_name, i.name as Index_Name, STATS_DATE(i.id, i.indid) as Statistics_last_collected FROM information_Schema.tables t INNER JOIN sysindexes i ON object_id(t.table_name) = i.id WHERE t.table_type = ‘BASE TABLE’ AND i.Name is NOT NULL AND i.Name not like ‘_WA_Sys%’ GO
connetin sqlserver frm command prompt:
H:\>net use \\GPRI11D11013 /user:csfb\sys0amdbadm1
Enter the password for 'csfb\sys0amdbadm1' to connect to 'GPRI11D11013':The command completed successfully.
H:\>osql -SGPRI11D11013 -E

Future of the DatabaseManagment..

http://www.dba-oracle.com/art_dbazine_2020_p2.htm

By 2015, the automation of many of the Oracle DBA functions led to the Oracle professional accepting responsibility for a whole new set of duties:
Data modeling and Oracle database design
Data interface protocols
Managing data security
Managing development projects
Predicting future Oracle trends for hardware usage and user load

http://oracle4dbas.blogspot.com/

Refreshing.....

OraInventory
The first time you install Oracle Database software on a server, you are prompted to specify the location of Inventory directory, called oraInventory. This directory provides a centralized inventory of all Oracle software products installed on the server. You should use the same value for the Oracle inventory directory each time you perform an Oracle software installation on
the server.
Below gud information from Atul's Blog:
What is oraInventory ?oraInventory is repository (directory) which store/records oracle software products & their oracle_homes location on a machine. This Inventory now a days in XML format and called as XML Inventory where as in past it used to be in binary format & called as binary Inventory.There are basically two kind of Inventory Global Inventory (also called as Central Inventory) and Local Inventory also called as Oracle Home Inventory.Global Inventory ?Global Inventory holds information about Oracle Products on a Machine. These products can be various oracle components like database, oracle application server, collaboration suite, soa suite, forms & reports or discoverer server . This global Inventory location will be determined by file oraInst.loc in /etc (on Linux) or /var/opt/oracle (solaris). If you want to see list of oracle products on machine check for file inventory.xml under ContentsXML in oraInventory (Please note if you have multiple global Inventory on machine check all oraInventory directories)You will see entry likeHOME NAME="ORA10g_HOME" LOC="/u01/oracle/10.2.0/db" TYPE="O" IDX="1"/......Local InventoryInventory inside each Oracle Home is called as local Inventory or oracle_home Inventory. This Inventory holds information to that oracle_home only.

Can I have multiple Global Inventory on a machine ?Quite common questions is that can you have multiple global Inventory and answer is YES you can have multiple global Inventory but if your upgrading or applying patch then change Inventory Pointer oraInst.loc to respective location. If you are following single global Inventory and if you wish to uninstall any software then remove it from Global Inventory as well.

What to do if my Global Inventory is corrupted ?No need to worry if your global Inventory is corrupted, you can recreate global Inventory on machine using Universal Installer and attach already Installed oracle home by option-attachHome./runInstaller -silent -attachHome -invPtrLoc $location_to_oraInst.locORACLE_HOME="Oracle_Home_Location" ORACLE_HOME_NAME="Oracle_Home_Name"CLUSTER_NODES="{}"

Simply...oraInventory is where oracle stores the inventory details of all the patches and components it has installed.

1) oraInventory could be placed anywhere which can be determined from inventory_loc in oraInst.loc. For oratab and oraInst.loc, OUI looks for those files usually at:/var/opt/oracle/oraInst.loc (typical)/etc/oraInst.loc (AIX and Linux)HKEY_LOCAL_MACHINE -> Software -> Oracle -> INST_LOC (Windows platforms)2) Correct. "oraInstRoot.sh" will be created for you by OUI and once you run it successfully you do not need it anymore.3) Please note that there are two inventories with the newer releases of OUI (2.x and higher): - The inventory in the ORACLE_HOME (Local Inventory)- The central inventory directory outside the ORACLE_HOME (Global Inventory)For more details about the two inventories, please check Note: 360079.1 - Global and Local Inventory explained

If this is the first time you are installing any Oracle software on this computer, then the Create Inventory Directory window appears. You must specify a local for the inventory, which OUI uses to keep track of all Oracle software installed on the computer. This information is used while applying patches or upgrading an existing installation, and while deinstalling Oracle software. Note that this directory is different from the Oracle home directory. The recommended value for the inventory directory is Oracle_base/../oraInventory, or one level above the Oracle base directory, in the oraInventory subdirectory. If you Oracle base directory is /u01/app/oracle, then the Oracle inventory directory defaults to /u01/app/oraInventory.