oracle sid modification

Oracle is a commonly used relational database management system used to store and manage critical data for large-scale enterprise applications. SID is the identifier of the Oracle instance and is used to distinguish different Oracle instances. Each instance has a unique SID. In some cases, it is necessary to modify the Oracle SID, such as running multiple instances on the same server, or changing the name of the current instance. This article will introduce how to modify Oracle's SID.

1. Confirm the current SID

Before modifying the SID, you must first confirm the current SID. You can use the following command to query:

echo $ORACLE_SID

This command will output the current SID.

2. Stop the Oracle instance

Before modifying the SID, you must stop the current Oracle instance. The instance can be stopped using the following command:

sqlplus / as sysdba
shutdown immediate;

This will stop the Oracle instance immediately.

3. Modify SID

Before modifying the SID, you need to edit two files: /etc/oratab and $ORACLE_HOME/network/admin/tnsnames.ora. The following describes how to edit these two files.

3.1 Edit the /etc/oratab file

The /etc/oratab file contains a list of Oracle instances and can be used to start, shut down, or restart Oracle instances. In the file, there is one line for each instance. The format of each line is as follows:

ORACLE_SID:ORACLE_HOME:Y|N

where ORACLE_SID is the unique name of the instance, ORACLE_HOME is the Oracle home directory of the instance, Y indicates that the instance should start automatically, and N indicates that the instance should not start automatically.

In order to modify the SID, you need to use an editor to open the /etc/oratab file, find the line of the current instance, and change its ORACLE_SID field to the desired SID. The modified line should look like this:

NEW_ORACLE_SID:/path/to/oracle/home:N

In this example, modify the ORACLE_SID field to NEW_ORACLE_SID. Once editing is complete, save and close the file.

3.2 Edit the tnsnames.ora file

The tnsnames.ora file contains the network service name of the Oracle database. In this file, there is an entry for each service. In order to modify the SID, edit the entry in the tnsnames.ora file.

First, use an editor to open the tnsnames.ora file. Find the entry for the current instance and modify it to the desired SID. The modified entry should look like this:

NEW_ORACLE_SID =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = your_host)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = NEW_ORACLE_SID)
    )
  )

In this example, modify the SID in both the SERVICE_NAME field and the entire entry to NEW_ORACLE_SID. Once editing is complete, save and close the file.

4. Modify the registry key

In Windows, you also need to modify the registry key to update the name of the Oracle instance to the new SID.

First, open the Registry Editor and navigate to the following path:

HKEY_LOCAL_MACHINESOFTWAREORACLEKEYORACLE_HOME_NAME

Under this path, change the ORACLE_SID field to the desired SID. After modification, close the Registry Editor.

5. Restart the Oracle instance

After modifying the SID, you can restart the Oracle instance.

First, confirm that the current SID has been changed to the desired new value. You can query using the following command:

echo $ORACLE_SID

Then, use the following command to start the Oracle instance:

sqlplus / as sysdba
startup

This will start the Oracle instance. If something goes wrong, you can view the error using the following command:

select * from v$diag_info;

This will provide details about the error to help troubleshoot the issue.

In this article, we introduced how to modify the Oracle SID. When doing this, be sure to back up all relevant files and make sure you stop the Oracle instance before making any changes.

The above is the detailed content of oracle sid modification. For more information, please follow other related articles on the PHP Chinese website!