Oracle Shared Server Commands

Before we begin to execute Oracle commands on the shared server, we must connect to an Oracle Instance using SQL*PLus.

Connecting SQL*Plus to an Oracle instance is an imperative task for database administrators and developers who intend to manage Oracle databases. This is a systematic procedure that involves multiple steps and requires precise attention to detail. SQL*Plus, a command-line utility provided by Oracle, serves as the interface to execute SQL and PL/SQL commands against an Oracle database.
Pre-requisites

• Oracle Database Installation: Ensure that Oracle Database is properly installed on the server where it's intended to run.
• Oracle Client Software: Make sure you have the Oracle client software and SQL*Plus installed on the client machine from which you are connecting.
• Valid Credentials: Confirm you have a valid username and password for the Oracle database instance you wish to connect to.
• Network Accessibility: Ensure that the client machine has network access to the server running the Oracle instance.
• Environment Variables: On UNIX and Linux systems, set the `ORACLE_HOME` and `ORACLE_SID` environment variables. On Windows, these are usually set during the Oracle client installation process.

1. Open Command Prompt or Terminal
   • On Windows, navigate to `Start -> Programs -> Oracle-OraDB##Home -> Application Development -> SQL*Plus`.
   • On UNIX or Linux, open a terminal.
2. Connect to the Oracle Instance:
   Option A: Using Direct Credentials
   Type the following command and then hit Enter:
   

   sqlplus username/password@'hostname:port/SID'
   

   
   Here `username` and `password` are the Oracle database credentials, `hostname` is the name of the machine on which Oracle is running, `port` is the port number (default is 1521), and `SID` is the System ID of the Oracle instance.
   Option B: Using TNSNAMES.ORA
   If you have a `tnsnames.ora` file configured, you can use an alias to connect, as shown below:
   

   sqlplus username/password@TNS_ALIAS
   

   
   In this case, `TNS_ALIAS` is an alias configured in the `tnsnames.ora` file, located usually in `$ORACLE_HOME/network/admin` on UNIX systems or `%ORACLE_HOME%\network\admin` on Windows.
   Option C: Interactive Login
   Simply type `sqlplus` and hit Enter. You'll be prompted to enter the username and password interactively:
   

   SQL*Plus: Release ... on ...
   Copyright (c) ....
   
   Enter user-name: username
   Enter password: password
   

3. Execute Commands: Once connected, you will be presented with the SQL prompt (`SQL>`) from where you can start executing SQL commands and PL/SQL blocks.
   Additional Notes
   • To disconnect, simply type `exit` or `quit` at the SQL prompt and hit Enter.
   • To execute a SQL script, use the `@` symbol followed by the script path, for example, `@/path/to/script.sql`.
   
   By following this guideline, you should establish a successful connection from SQL*Plus to an Oracle instance, allowing you to perform database operations with precision and efficiency. Make sure to always adhere to best practices for database security, including the usage of strong passwords and encrypted connections where applicable.

As an Oracle DBA working with Oracle Shared Server in Oracle 12c, mastering a set of crucial commands is imperative to ensure efficient management, monitoring, and troubleshooting of the shared server environment. Here are the top five commands and sets of queries that you should be proficient with:

1. Viewing Shared Server Configuration: `SHOW PARAMETERS`: To understand and assess the current configuration of your Oracle Shared Server, you must familiarize yourself with the relevant parameters. Use the following SQL query to retrieve information about the shared server configuration:
   

   SHOW PARAMETERS SHARED_SERVERS;
   SHOW PARAMETERS DISPATCHERS;
   SHOW PARAMETERS MAX_SHARED_SERVERS;
   

   
   
   • `SHARED_SERVERS` indicates the number of shared server processes that have been configured.
   • `DISPATCHERS` displays the configuration of dispatcher processes, including their protocols and addresses.
   • `MAX_SHARED_SERVERS` shows the maximum number of shared server processes that can be automatically created.
2. Monitoring Shared Server Activity: `V$SHARED_SERVER` To monitor the activity and performance of shared servers, query the `V$SHARED_SERVER` view:
   

   SELECT ID, STATUS, REQUESTS, BYTES_RECEIVED, BYTES_SENT FROM V$SHARED_SERVER;
   

   
   This query provides information about the status of shared server processes, the number of requests they have handled, and the amount of data they have received and sent.
3. Managing Dispatcher Processes: `ALTER SYSTEM`: Dispatchers play a critical role in routing requests from clients to shared servers. To manage dispatcher processes, use the `ALTER SYSTEM` command:
   

   ALTER SYSTEM SET DISPATCHERS = '(PROTOCOL=TCP)(DISPATCHERS=5)';
   

   
   This example configures five dispatcher processes for TCP/IP connections. Adjust the parameters as necessary to suit your environment and workload.
4. Tracing Shared Server Activity: `DBMS_SYSTEM` To enable and manage tracing for shared server processes, use the `DBMS_SYSTEM` package:
   

   EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(SID, SERIAL#, TRUE);
   

   
   Replace `SID` and `SERIAL#` with the appropriate session identifiers. This command enables SQL tracing for the specified session, helping you diagnose performance issues and analyze the execution of SQL statements.
5. Analyzing Queue Wait Times: `V$QUEUE`: For performance tuning and to ensure optimal resource utilization, analyze the wait times in the shared server queues:
   

   SELECT NAME, TOTALQ, WAIT, TOTALQ - WAIT AS "NOWAIT" FROM V$QUEUE;
   

   This query provides insights into the number of requests in the queue, how many are waiting, and how many are being processed without waiting. Use this information to adjust shared server configuration settings as needed to optimize performance.

Mastering these commands and queries is vital for any Oracle DBA managing a shared server environment in Oracle 12c. They provide a solid foundation for configuring, monitoring, and optimizing the shared server, ensuring that it runs efficiently and meets the demands of your Oracle database applications. Constant practice and real-world application of these commands will enhance your proficiency and effectiveness as an Oracle DBA.

The next several lessons show you how to use the

1. EXIT,
2. HELP,
3. SPOOL, and
4. DESCRIBE

commands in Oracle.

Since the year 2000, Oracle has been slowly de-commisioning the old server manager interface.
Historical Note: The old Oracle Server manager provided a way to enter special database administration functions such as

1. startup and
2. shutdown

commands for the Oracle server as well as special DBA commands such as

1. oradebug and
2. the display of internal block structures.

Server Manager is not supported in Oracle9i, and the DBA must use SQL*Plus instead. Most Server Manager scripts should work in a SQL*Plus environment, but some scripts may need to be modified. The Oracle professional must understand how to use commands within the Oracle SQL*Plus product. In order to connect to SQL*Plus and issue DBA commands, Oracle allows you to connect to

1. SQL*Plus AS SYSDBA or
2. AS SYSOPER.

Once connected to the database, the Oracle DBA can

1. startup the database,
2. shutdown the database, and
3. provide trace files, and
4. use the oradebug facility.

In the following examples, one sees how Oracle is used in order to connect to the database and issue startup and shutdown commands:

sqlplus /nolog
connect / as sysdba
startup
sqlplus /nolog
connect system/manager as sysdba
oradebug ipc

These commands can also easily be placed inside shell script to perform routine database functions:

#!/bin/ksh
# First set the environment 
ORACLE_SID=mon1
export ORACLE_SID
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
$ORACLE_HOME/bin/sqlplus /nolog<<!
connect system/manager as sysdba
select * from v\$database;
shutdown immediate
exit