Uncategorised

Oracle Tutorials: Useful commands and env variable.

May 2, 2023 Leave a Comment

What is ORACLE_SID?

ORACLE_SID is an environment variable used by Oracle Database to identify the unique system identifier (SID) of an Oracle instance. An Oracle instance is a combination of background processes and memory structures that serve as a runtime environment for a specific database.

The ORACLE_SID environment variable is set when the Oracle instance is started, and it uniquely identifies the instance within a single server or cluster. Multiple instances of Oracle Database can be running on the same server or cluster, each with a different ORACLE_SID.

Applications and utilities that need to connect to a specific Oracle instance use the ORACLE_SID environment variable to specify which instance to connect to. For example, if you have two instances running on a server with ORACLE_SID values of “ORCL” and “TEST”, respectively, you can connect to the “ORCL” instance using the following command:

sqlplus /nolog
SQL> connect sys/password as sysdba
SQL> startup
SQL> exit

Here, “ORCL” is the value of the ORACLE_SID environment variable for the “ORCL” instance. By specifying “sys/password as sysdba” and connecting to the instance using the “startup” command, you can start the instance and access the database.

How to get oracle sid value

To get the Oracle SID value, you can use one of the following methods:

  1. Check the ORACLE_SID Environment Variable: The Oracle SID value is typically stored in the ORACLE_SID environment variable. You can check the value of this variable using the following command on a Unix/Linux system:echo $ORACLE_SID On a Windows system, you can check the value of the ORACLE_SID environment variable by opening a command prompt and running the following command:bashCopy codeecho %ORACLE_SID%
  2. Check the Listener Status: If the Oracle listener is running, you can use the lsnrctl command to check the status of the listener and get the SID value. Here is the command you can use:lsnrctl status This command will display the status of the listener and list the database services registered with the listener, along with their corresponding SID values.
  3. Check the INIT.ORA or SPFILE File: The Oracle SID value can also be found in the Oracle database’s initialization parameter file (INIT.ORA) or server parameter file (SPFILE). The location of these files can vary depending on your Oracle installation and configuration. You can check the contents of these files to find the SID value.

How to know list of Oracle instance in my cluster?

You can use several methods to determine the list of Oracle instances in your cluster. Here are some common methods:

  1. Using srvctl command: The Oracle Clusterware utility srvctl can be used to manage Oracle instances in a cluster. You can use the srvctl status instance -db <db_name> command to display the status of all instances in the specified database. For example, to list all instances in the “orcl” database, use the following command: srvctl status instance -db orcl
  2. Using the Oracle Enterprise Manager (OEM) Console: The OEM Console provides a graphical interface to manage Oracle databases and instances. To view the list of instances in a cluster, log in to the OEM Console and navigate to the “Cluster Database” page.
  3. Using SQLPlus: You can use SQLPlus to connect to the cluster database and run queries to retrieve information about the instances. For example, you can run the following query to list all instances in the cluster: SELECT INSTANCE_NAME, STATUS FROM GV$INSTANCE; This query retrieves the instance name and status from the GV$INSTANCE view, which contains information about all instances in the cluster.

By using these methods, you can determine the list of Oracle instances in your cluster.

Oracle command to list the database

To list the databases that are available on a server running Oracle Database, you can use the lsnrctl status command. This command displays the status of the listener, which is a network service that listens for incoming client connections to Oracle databases.

Here’s how you can use the lsnrctl status command to list the databases:

  1. Open a command prompt or terminal window on the server running Oracle Database.
  2. Type the following command and press Enter: lsnrctl status
  3. The output of this command will display the status of the listener, including the names of the databases that it is configured to listen for. The database names will be listed under the “Services Summary” section of the output, as shown in the following example:
Services Summary...
Service "ORCL" has 1 instance(s).
  Instance "ORCL", status READY, has 1 handler(s) for this service...
The command completed successfully

In this example, “ORCL” is the name of the database that the listener is configured to listen for. If there are multiple databases configured on the server, they will be listed under the “Services Summary” section as separate entries.

What is the meaning of sqlplus / as sysdba

sqlplus / as sysdba is a command to connect to an Oracle database as the SYS user with DBA (database administrator) privileges. The / after sqlplus specifies that you are connecting locally (i.e., on the same machine where the database is installed), and the as sysdba clause specifies that you are connecting with DBA privileges.

When you connect to the database with the sqlplus / as sysdba command, you are prompted to enter the password for the SYS user. However, if the password file for the database has been configured, you may not be prompted for a password and will be automatically granted DBA privileges.

The SYS user is a special user in Oracle that has full privileges to the database and can perform any operation on the database objects. As such, it is recommended to use this account only when necessary and with caution, as it has the potential to cause significant damage if used improperly.

Rajesh Kumar
Follow me
Latest posts by Rajesh Kumar (see all)
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x