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.

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.

RAC - Commonly used Terms and Utilities

. InShort Meaning Details . RAC Real Application Clusters Is Oracle Implementation of Clustered Database Instances. . CRS Cluster Ready Services Oracle's own cluster ware tightly coupled with Oracle Real Application Clusters . OCFS Oracle Cluster File System Oracle File system Implementation For Linux. . PCP Parallel Concurrent Processing PCP 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 Registry The OCR contains cluster and database configuration information for RAC and Cluster Ready Services . ASM Automatic Storage Management Simplifies database administration by enabling you to create disk groups and manage them instead of individual datafiles. . ASMCMD ASM Command Utility ASM Command Utility to monitor ASM . SSH Secure Shell SSH 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 Algorithm The Digital Signature Algorithm . SCSI Small Computer System Interface Standard 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 storage Is 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 Filesystem Its 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 Program An Oracle Program To assist third-parties with their Oracle compatibility with respect to storage devices. . DTP Distributed Transaction Processing DTP model is the synchronization of any commits and rollbacks that are required to complete a distributed transaction request. . VIP Virtual IP Address Its an IP address that is not connected to a specific computer or network interface card . TCP/IP Transmission Control Protocol TCP . DNS Domain Name Server The domain name system is responsible for translating it translates domain names . MAC Media Access Control Media Access Control address

. JDBC Java Database Connectivity A Java API that allows independent connectivity between the Java programming language and a wide range of databases. . LB Load Balancing Is a technique to balance the load between configured servers. . FAN Fast Application Notification Enables end-to-end, lights-out recovery of applications and load balancing when a cluster configuration changes. . FCF Fast Connection Failover Fast Connection Failover provides the ability to failover connections in the connection cache as quickly and efficiently as the database itself. . TAF Transparent Application Failover Using transparent application recovery . OCI Oracle Call Interface OCI allows you to develop applications that take advantage of the capabilities of SQL from within the application. . ODP Oracle Data Provider ODP.NET features optimized data access to the Oracle database from a .NET environment. . ONS Oracle Names Server Oracle Names makes network address and database link information available to all nodes throughout the network. . ORION Oracle I/O Numbers ORION is a test tool freely available tool which simulates Oracle I/O. . GSD Global Services Daemon The Global Services Daemon . NTP Network Time Protocol The Network Time Protocol . CVU Cluster Verification Utility CVU is used to verify all the important components that need to be verified at different stages in a RAC environment. . VSD Virtual Shared Disk A Virtual Shared Disk . LPARs Logical Partitions Logical Partition, commonly called an LPAR, is a virtualized computing environment abstracted from all physical devices. . DBCA Oracle Database Configuration Assistant An Oracle utility that facilitates the creation and configuration of a database. . ORACM Oracle Cluster Manager Allows to add new nodes to an existing cluster without having to stop/start the whole cluster. . RDA Remote Diagnostic Agent An Oracle Diagnostic Tool used in troubleshooting. . GFS Global Filesystem GFS allows a cluster of Linux servers to share data in a common pool of storage. . NIC Network Interface Controller Its a piece of computer hardware designed to allow computers to communicate over a computer network. . LVM Logical Volume Manager

. GPFS General Parallel File System GPFS is IBM’s high-performance parallel, scalable file system for IBM UNIX clusters. . HA High Availability . MAA Maximum Availability Architecture . RACDDT RAC Data Collection Tool Is a data collection tool designed and configured specifically for gathering diagnostic data related to Oracle's Real Application Cluster . OSW OSWatcher Is 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 Security Oracle Label Security is developed based on virtual private database . RSM Remote Shared Memory Is a feature that bypasses the UDP/IP communication in Solaris. . UDP/IP User Datagram Protocol Can send short messages sometimes known as datagrams to one another. . TNS Transparent Network Substrate Allows peer-to-peer connectivity where no machine-level connectivity can occur. . EMCA Enterprise Manager Configuration Assistant An Oracle tool to set up the Enterprise Manager. . OPS Oracle Parallel Server The OPS option allows multiple instances on different computer systems . SRVCTL Server Control A utility to assist in administration and maintenance of RAC databases. . CRSCTL CRS Control A utility to assist in administration and monitoring of CRS. . CRS Commands . CRS_STAT List the status of resources . CRS_GETPERM Checks the permissions that are associated with each resource . CRS_PROFILE Creates, validates, deletes, and updates an Oracle Clusterware profile . CRS_REGISTER This command registers configuration information for an application with the OCR . CRS_RELOACTE Used to relocate an application profile from one node to another. . CRS_SETPERM Sets and modifies permissions associated with a resource