|Lesson 6|| Shared Server Commands used by Oracle DBAs|
|Objective||Functions that use common Shared Server commands|
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.
Connect SQL*PLus to an Oracle Instance
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.
- 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.
Steps for Connection
1. Open Command Prompt or Terminal
2. Connect to the Oracle Instance
- On Windows, navigate to `Start -> Programs -> Oracle-OraDB##Home -> Application Development -> SQL*Plus`.
- On UNIX or Linux, open a terminal.
Option A: Using Direct Credentials
Type the following command and then hit Enter:
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:
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
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.
- 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.
Top 5 commands a DBA should know when working with Oracle Shared Server
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
commands in Oracle.
Some Common SQL*Plus Commands
Since the year 2000, Oracle has been slowly de-commisioning the old server manager interface
The old Oracle Server manager provided a way to enter special database administration functions such as
- startup and
commands for the Oracle server as well as special DBA commands such as
- oradebug and
- the display of internal block structures.
Server Manager not supported in Oracle9i
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
- SQL*Plus AS SYSDBA or
- AS SYSOPER.
Once connected to the database, the Oracle DBA can
In the following examples, one sees how Oracle is used in order to connect to the database and issue startup and shutdown commands:
- startup the database,
- shutdown the database, and
- provide trace files, and
- use the oradebug facility.
connect / as sysdba
connect system/manager as sysdba
These commands can also easily be placed inside shell script to perform routine database functions:
Shell Script Commands
# First set the environment
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
connect system/manager as sysdba
select * from v\$database;
Ad Oracle Database Architecture