Thursday, May 21, 2009

Changing the SQL Prompt to reflect the user and db connected to:

Ever wonder which database you connected to with what user when you are busy changing the connections in SQL*Plus?

Here is the simple trick that shows you the user you connected as on the current database. Also note that the values will change automatically when you make another connection from the same window using "connect".

set sqlprompt "_user'@'_connect_identifier: SQL>"

SQL> set sqlprompt "_user'@'_connect_identifier>"
HR@test: SQL>

Now change the connection:
hr@test: SQL> connect san@prod
Enter password:
Connected.
SAN@prod: SQL> connect san@test2
Enter password:
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Warning: You are no longer connected to ORACLE.
@: SQL> 

Hope this trick comes in handy.
BTW, this trick is from 10g onwards only.

Wednesday, May 13, 2009

PZnn Processes!!!

Dont know dont ask but these PZ99 and PZ98 processes contributed in loosing my hair!!!
Never heard of them before and ever cared to see what those are untill I recently found that the little tool that I wrote to kill all the sessions in the database was failing in RAC databases (you can find that in my earlier posts).

This little tool was suppose to go and kill all the sessions that are connected in the database prior to doing any upgrades and they started failing.
When I looked at there are these processes PZ99 and PZ98 sitting in gv$session and thier serial# keeps changing everytime you run a query against gv$session view and thus my little killing a session in RAC fails as the serial# is changed!!!

Anyways, what are these PZnn processes?
These are PQ slaves used for global views that are RAC Parallel Server Slave processes, but they are not normal parallel slave processes, PZnn processes (starting at 99) are used to query GV$ views which is done using Parallel Execution on all instances, if more than one PZ process is needed, then PZ98, PZ97,... (in that order) are created automatically.

Tuesday, May 12, 2009

RAC - Commonly used Terms and Utilities



.

InShortMeaningDetails

.

RAC Real Application ClustersIs Oracle Implementation of Clustered Database Instances.

.

CRS Cluster Ready ServicesOracle's own cluster ware tightly coupled with Oracle Real Application Clusters

.

OCFS Oracle Cluster File SystemOracle File system Implementation For Linux.

.

PCP Parallel Concurrent ProcessingPCP allows concurrent processing activities to be distributed across multiple nodes in an RAC environment, maximizing throughput and providing resilience to node failure.

.

OCR Oracle Cluster RegistryThe OCR contains cluster and database configuration information for RAC and Cluster Ready Services

.

ASM Automatic Storage ManagementSimplifies database administration by enabling you to create disk groups and manage them instead of individual datafiles.

.

ASMCMDASM Command UtilityASM Command Utility to monitor ASM

.

SSH Secure ShellSSH is a set of standards and an associated network protocol that allows establishing a secure channel between a local and a remote computer.

.

RSH Remote Shell

.

DSA Digital Signature AlgorithmThe Digital Signature Algorithm

.

SCSI Small Computer System InterfaceStandard interface and command set for transferring data between devices commonly used for interfaces for hard disks, and USB and FireWire connections for external devices.

.

NAS Network-attached storageIs a dedicated data storage technology that can be connected directly to a computer network to provide centralized data access and storage to heterogeneous network clients.

.

NFS Network FilesystemIts a protocol allows a user on a client computer to access files over a network as easily as if attached to its local disks.

.

OSCP Oracle Storage Compatibility ProgramAn Oracle Program To assist third-parties with their Oracle compatibility with respect to storage devices.

.

DTP Distributed Transaction ProcessingDTP model is the synchronization of any commits and rollbacks that are required to complete a distributed transaction request.

.

VIP Virtual IP AddressIts an IP address that is not connected to a specific computer or network interface card

.

TCP/IP Transmission Control Protocol TCP

.

DNS Domain Name ServerThe domain name system is responsible for translating it translates domain names

.

MAC Media Access ControlMedia Access Control address

.

JDBC Java Database ConnectivityA Java API that allows independent connectivity between the Java programming language and a wide range of databases.

.

LB Load BalancingIs a technique to balance the load between configured servers.

.

FAN Fast Application NotificationEnables end-to-end, lights-out recovery of applications and load balancing when a cluster configuration changes.

.

FCF Fast Connection FailoverFast Connection Failover provides the ability to failover connections in the connection cache as quickly and efficiently as the database itself.

.

TAF Transparent Application FailoverUsing transparent application recovery

.

OCI Oracle Call InterfaceOCI allows you to develop applications that take advantage of the capabilities of SQL from within the application.

.

ODP Oracle Data ProviderODP.NET features optimized data access to the Oracle database from a .NET environment.

.

ONS Oracle Names ServerOracle Names makes network address and database link information available to all nodes throughout the network.

.

ORION Oracle I/O NumbersORION is a test tool freely available tool which simulates Oracle I/O.

.

GSD Global Services DaemonThe Global Services Daemon

.

NTP Network Time ProtocolThe Network Time Protocol

.

CVU Cluster Verification UtilityCVU is used to verify all the important components that need to be verified at different stages in a RAC environment.

.

VSD Virtual Shared DiskA Virtual Shared Disk

.

LPARs Logical PartitionsLogical Partition, commonly called an LPAR, is a virtualized computing environment abstracted from all physical devices.

.

DBCA Oracle Database Configuration AssistantAn Oracle utility that facilitates the creation and configuration of a database.

.

ORACM Oracle Cluster ManagerAllows to add new nodes to an existing cluster without having to stop/start the whole cluster.

.

RDA Remote Diagnostic AgentAn Oracle Diagnostic Tool used in troubleshooting.

.

GFS Global FilesystemGFS allows a cluster of Linux servers to share data in a common pool of storage.

.

NIC Network Interface ControllerIts a piece of computer hardware designed to allow computers to communicate over a computer network.

.

LVM Logical Volume Manager

.

GPFS General Parallel File SystemGPFS is IBM’s high-performance parallel, scalable file system for IBM UNIX clusters.

.

HA High Availability

.

MAA Maximum Availability Architecture

.

RACDDT RAC Data Collection ToolIs a data collection tool designed and configured specifically for gathering diagnostic data related to Oracle's Real Application Cluster

.

OSW OSWatcherIs an operating system diagnostic utility tool that gathers archival performance data using various native Unix utilities, such as vmstat, iostat and top.

.

OLS Oracle Label SecurityOracle Label Security is developed based on virtual private database

.

RSM Remote Shared MemoryIs a feature that bypasses the UDP/IP communication in Solaris.

.

UDP/IP User Datagram ProtocolCan send short messages sometimes known as datagrams to one another.

.

TNS Transparent Network SubstrateAllows peer-to-peer connectivity where no machine-level connectivity can occur.

.

EMCA Enterprise Manager Configuration AssistantAn Oracle tool to set up the Enterprise Manager.

.

OPS Oracle Parallel ServerThe OPS option allows multiple instances on different computer systems

.

SRVCTL Server ControlA utility to assist in administration and maintenance of RAC databases.

.

CRSCTLCRS ControlA utility to assist in administration and monitoring of CRS.

.

CRS Commands

.

CRS_STATList the status of resources

.

CRS_GETPERMChecks the permissions that are associated with each resource

.

CRS_PROFILECreates, validates, deletes, and updates an Oracle Clusterware profile

.

CRS_REGISTERThis command registers configuration information for an application with the OCR

.

CRS_RELOACTEUsed to relocate an application profile from one node to another.

.

CRS_SETPERMSets and modifies permissions associated with a resource