How Do I Reset the Password in Oracle?

Resetting a password in Oracle is a common administrative task that can be accomplished through various methods, depending on the user whose password needs to be changed and your current access level. This guide covers how to reset passwords for general database users, administrative users like SYS or SYSTEM, and in the context of specific applications.

Essential Prerequisites

Before attempting a password reset, ensure you have:

Database Credentials: Access to an administrative user (e.g., SYS or SYSTEM) with ALTER USER privilege, or operating system authentication for SYSDBA if the SYS password is lost.
Database Connection Tools: SQL*Plus or SQL Developer.
Server Access: If dealing with application-specific users or if OS authentication is required, SSH/console access to the database server.

Resetting a Standard Database User Password

The most common scenario is resetting a password for an existing database user (e.g., SCOTT, HR, or an application user) when you have SYSDBA privileges.

Steps:

Connect to the Database as a Privileged User:
Open a terminal or command prompt and connect to SQL*Plus as SYSDBA or SYSTEM.
```
sqlplus / as sysdba
-- OR
sqlplus system/your_system_password
```
Tip: Connecting as sysdba using operating system authentication (e.g., sqlplus / as sysdba) is often the most reliable method if you're on the database server as a user belonging to the OSDBA group.
Execute the ALTER USER Command:
Use the ALTER USER command to set a new password for the desired user.
```
ALTER USER username IDENTIFIED BY new_password;
```
Example:
To reset the password for the HR user to HrNewPass1!, you would execute:
```
ALTER USER HR IDENTIFIED BY HrNewPass1!;
```
Verify the Password Change:
Attempt to connect to the database with the user and the new password.
```
sqlplus HR/HrNewPass1!
```

Resetting SYS or SYSTEM User Password

If you've forgotten the password for the SYS or SYSTEM user, you can typically reset it by connecting with operating system authentication (if available).

Steps:

Log in to the Database Server:
Access the server where your Oracle database is running, using an operating system user that is part of the OSDBA group (e.g., oracle user).

Set Environment Variables (if needed):
Ensure your Oracle environment variables (ORACLE_HOME, ORACLE_SID) are correctly set.

export ORACLE_SID=your_oracle_sid # e.g., ORCL, XE
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1 # Your Oracle Home
export PATH=$ORACLE_HOME/bin:$PATH

Connect as SYSDBA using OS Authentication:
Open SQL*Plus and connect using operating system authentication, which does not require a password.
```
sqlplus / as sysdba
```
Execute the ALTER USER Command:
Once connected as SYSDBA, you can reset the password for SYS or SYSTEM.
```
ALTER USER SYS IDENTIFIED BY new_sys_password;
ALTER USER SYSTEM IDENTIFIED BY new_system_password;
```
Example:
To reset the SYS password to SysAdminPass@2024, you would run:
```
ALTER USER SYS IDENTIFIED BY SysAdminPass@2024;
```
Verify the Password Change:
Exit SQL*Plus and attempt to connect using the new SYS or SYSTEM password.
```
sqlplus sys/SysAdminPass@2024 as sysdba
```

Resetting Passwords for Application Users (e.g., SDM)

In some application environments, resetting an Oracle database user's password might require additional steps, such as shutting down the application first to prevent connection issues or application-level password management conflicts.

Process for Application-Specific Passwords:

Login to the Application Server:
Access the server where the application (e.g., SDM) is installed. Use the designated application user, such as nncentral.
Navigate to the Application's Bin Directory:
Change your current directory to the application's bin directory. This is typically where scripts for managing the application are located.
```
cd /path/to/sdm/bin # Example path
```
Shut Down the Application:
Execute the application shutdown script. This ensures the application releases its database connections and doesn't interfere with the password change.
```
./shutdownnnc.sh
```
Go to the Oracle Database Bin Home Directory:
Navigate to the Oracle database's bin directory, which contains utilities like sqlplus.
```
cd $ORACLE_HOME/bin # Or the specific path to your Oracle bin
```
Specify the Oracle SID:
Set the ORACLE_SID environment variable to ocsdmdw (or the specific SID for your application database) to ensure you connect to the correct instance.
```
export ORACLE_SID=ocsdmdw
```
Connect to the Database as a Privileged User:
As described in the general steps, connect to SQL*Plus as SYSDBA.
```
sqlplus / as sysdba
```
Reset the Application User's Password:
Use the ALTER USER command to set the new password for the application's database user.
```
ALTER USER sdm_app_user IDENTIFIED BY new_app_password; # Replace 'sdm_app_user' and 'new_app_password'
```
Restart the Application:
After successfully resetting the password, navigate back to the application's bin directory and restart the application using its startup script.
```
cd /path/to/sdm/bin
./startnnc.sh # Example startup script
```
Important: Ensure the application's configuration files are updated with the new database password if the application itself stores these credentials.

Secure Password Best Practices

When resetting passwords, always adhere to security best practices:

Complexity: Use a strong password that includes a mix of uppercase and lowercase letters, numbers, and special characters. Avoid easily guessable passwords.
Uniqueness: Do not reuse passwords across different systems or accounts.
Rotation: For critical administrative accounts, consider periodic password rotation.
Secure Storage: If you must document passwords, use a secure, encrypted password manager.
Principle of Least Privilege: Only reset passwords for users and with privileges absolutely necessary for the task.

Summary of Connection Methods

Connection Type	Command Example	Description
As SYSDBA (OS Auth)	`sqlplus / as sysdba`	Connects using OS user's privileges, no password needed.
As SYSDBA (Password)	`sqlplus sys/password as sysdba`	Connects as SYS with explicit password.
As SYSTEM	`sqlplus system/password`	Connects as SYSTEM with explicit password.
Standard User	`sqlplus username/password`	Connects as a regular database user.
Remote Connection (TNS)	`sqlplus username/password@TNS_ALIAS`	Connects to a remote database using a TNS alias.

For more detailed information on managing users and security in Oracle, refer to the official Oracle documentation: