ORA-28000 Account is Locked in Oracle – Unlock User Easily

 ORA-28000: Account is Locked – Fix in Oracle

This error occurs when a user account is locked due to multiple failed login attempts. To resolve the ORA-28000 error, you must log in to the database as a user with administrative privileges (like SYS or SYSTEM) and manually unlock the account. This error typically occurs when a user exceeds the maximum number of failed login attempts defined by their security profile. 

Error

ORA-28000: the account is locked

Causes

• Multiple wrong password attempts
• Profile lock settings

Quick Fix: Unlock the User

Use the following steps in SQL*Plus or a similar command-line tool: 

1.      Connect as SYSDBA:

Sql> sqlplus / as sysdba

2.      Unlock the Account:
Replace username with the actual name of the locked user.

Sql> ALTER USER username ACCOUNT UNLOCK;

3.      Optional: Reset the Password:

If the account was locked because the user forgot their password, you can reset it simultaneously.

Sql> ALTER USER username IDENTIFIED BY new_password ACCOUNT UNLOCK;

 Verify Account Status

You can check if a user is currently locked by querying the dba_users view: 

Sql> SELECT username, account_status

FROM dba_users

WHERE username = 'YOUR_USERNAME';

 Preventing Future Lockouts

If accounts are locking frequently, you may need to adjust the security profile settings: 

·         Check Attempt Limits: Run this to see the current limit for failed attempts.

Sql> SELECT limit FROM dba_profiles

WHERE profile = 'DEFAULT' AND resource_name = 'FAILED_LOGIN_ATTEMPTS';

·         Increase Limits: You can set the limit to UNLIMITED or a higher number.

Sql> ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;

·         Identify the Source: If an account relocks immediately after being unlocked, a background service or application might be using an old, cached password. Use SELECT username, machine, program FROM v$session; to find active sessions for that user.

Conclusion

Unlock the account and ensure correct credentials.

ORA-12541 TNS No Listener Error – Step-by-Step Solution

ORA-12541: TNS No Listener – Complete Fix

The ORA-12541: TNS:no listener error indicates that the Oracle client is attempting to connect to a database, but there is no listener process running on the specified host and port. This is most commonly caused by the listener service being stopped or a mismatch in the network configuration. This error occurs when the Oracle listener service is not running.

Causes

• Listener service stopped
• Wrong port number
• Incorrect listener configuration

Solution

Step 1: Verify and Start the Listener

    The first step is to check if the listener is active on the database server.

     On Windows:

1.      Open the Services window (press Win + R, type services.msc, and hit Enter).

2.      Locate the service named OracleXETNSListener (or similar, such as OracleOraDB19Home1TNSListener).

3.      If the status is not Running, right-click it and select Start.

4.      Set the Startup Type to Automatic to prevent future occurrences.

    On Linux/Unix:

1.      Log in as the oracle user.

2.      Run the command: lsnrctl status.

3.      If the listener is down, start it using: lsnrctl start

Step 2: Check Network Configuration Files 

    If the listener is running but you still see the error, there is likely a mismatch between your client and server settings.

1.         Locate the files: Both listener.ora (server-side) and tnsnames.ora (client-side) are typically in %ORACLE_HOME%\network\admin.

2.        Verify Host and Port: Ensure the HOST and PORT (usually 1521)     in the tnsnames.ora file match exactly what is defined in the server's listener.ora.

3.        Use IP instead of Hostname: If DNS resolution is failing,     try replacing the hostname with the server's static IP address in both files.

4.        Restart the Listener: After any file changes, run lsnrctl stop followed by lsnrctl start.

Step 3: Test Connectivity

    Use these tools to confirm the connection path is clear: 

• TNSPING: Run tnsping <service_name> from the command prompt. A "Successful" message indicates the client can reach the listener.

• Telnet: Test if the port is open through firewalls by running telnet <server_ip> 1521. If the screen goes blank, the port is open; if it says, "Connect failed," a firewall is likely blocking it.

 Step 4: Register the Database (If Necessary) 

    If lsnrctl status shows the listener is running but lists "The listener supports no services," your database may not be registered.

1.       Connect to the database via SQL*Plus: sqlplus / as sysdba.

2.       Run the command: ALTER SYSTEM REGISTER;.

3.       Re-check the status with lsnrctl status to ensure your   service (e.g., XE or ORCL) is now listed as READY.

 Conclusion

    Ensure listener is running and properly configured.

 

 

ORA-01017 Invalid Username/Password Error in Oracle – Complete Step-by-Step Fix

ORA-01017 Invalid Username/Password Error in Oracle – Complete Fix

If you are facing the ORA-01017: invalid username/password; logon denied error in Oracle Database, don’t worry. This is one of the most common login errors, and it can be resolved with the right steps.

In this guide, we will explain the causes of this error and provide step-by-step solutions to fix it in Oracle 11g, 12c, and higher versions.

Error Message

ORA-01017: invalid username/password; logon denied

What Causes ORA-01017 Error?

This error occurs when Oracle cannot authenticate the user. Below are the most common reasons:

• Incorrect username or password
• Case-sensitive password mismatch
• User account is locked
• Password has expired
• Wrong connection string or service name
• Using incorrect authentication method

Step-by-Step Solutions

1. Verify Username and Password

The first and most obvious step is to check your login credentials.

conn username/password

Make sure:

• No typing mistakes
• No extra spaces
• Correct username

2. Check Case Sensitivity

Oracle passwords are case-sensitive. If your password was created using uppercase or lowercase letters, you must enter it exactly the same.

Example:

conn scott/Tiger   -- Wrong
conn scott/tiger   -- Correct

3. Unlock User Account

If the account is locked due to multiple failed login attempts, you need to unlock it.

ALTER USER username ACCOUNT UNLOCK;

You can check account status using:

SELECT username, account_status FROM dba_users;

4. Reset User Password

If you are unsure about the password, reset it:

ALTER USER username IDENTIFIED BY new_password;

After resetting, try logging in again.

5. Check Password Expiry

If the password is expired, Oracle will not allow login.

Reset it using:

ALTER USER username IDENTIFIED BY new_password;

6. Verify Connection String

If you are connecting using TNS or a connection string, ensure it is correct.

Example:

sqlplus username/password@ORCL

Make sure:

• Service name is correct
• Listener is running

7. Check Authentication Method

If your database uses OS authentication or external authentication, ensure you are using the correct login method.

Real-World Example

A user tried to connect using:

conn hr/HR123

But the password was actually hr123. Since Oracle is case-sensitive, login failed with ORA-01017.

After correcting the password, the issue was resolved.

Common Mistakes to Avoid

• Using uppercase instead of lowercase passwords
• Forgetting to unlock user account
• Not checking password expiry
• Incorrect TNS/service name

Best Practices

• Always store credentials securely
• Avoid multiple failed login attempts
• Regularly update passwords
• Monitor user account status

Frequently Asked Questions (FAQ)

Q1: Why does ORA-01017 occur?
It occurs due to incorrect username or password or account-related issues.

Q2: Can I bypass this error?
No, you must correct credentials or unlock/reset the account.

Q3: How do I check if the account is locked?
Use: SELECT username, account_status FROM dba_users;

ORA-01194 Error Fix in Oracle 11g/12c – File Needs More Recovery (Step-by-Step)

Fix ORA-01194 error in Oracle Database with step-by-step recovery methods. Learn how to recover database and open with RESETLOGS safely.

Introduction

If you are facing ORA-01194: file needs more recovery to be consistent, this guide will help you fix it step-by-step in Oracle 11g, 12c, and higher versions.

The error ORA-01194: file 1 needs more recovery to be consistent occurs in an Oracle database when you attempt to open the database after incomplete recovery.

This issue typically arises when the database files are not fully synchronized with the required redo or archive logs.

Error Message

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '<path>/system01.dbf'

Root Cause

According to Oracle Corporation documentation:

• The database recovery process was incomplete
• Required redo/archive logs are missing or not applied
• The datafile was not closed cleanly
• Backup restoration was not properly followed by recovery

In simple words:
The database is not in a consistent state, and Oracle requires more recovery steps before opening it.

Solution Methods

Method 1: Proper Database Recovery (Recommended)

Step 1: Start Database in Mount Mode

SQL> startup mount;

Step 2: Perform Recovery

SQL> recover database;

If you encounter errors like:

• ORA-00283
• ORA-01610
• ORA-01547
• ORA-01194

Then proceed with incomplete recovery using backup control file:

Step 3: Recover Using Backup Controlfile

SQL> recover database using backup controlfile until cancel;

• Press AUTO or provide archive logs when prompted
• Continue applying logs until no more are required
• Type:

CANCEL;

 

Step 4: Open Database with RESETLOGS

SQL> alter database open resetlogs;

This method ensures the database becomes consistent by applying all available logs.

Method 2: Force Open Database (Last Resort)

Use this method only when:

• Required archive logs are missing
• Normal recovery fails

Step 1: Shutdown & Mount

SQL> shutdown immediate;

SQL> startup mount;

Step 2: Enable Hidden Parameter

SQL> ALTER SYSTEM SET "_allow_resetlogs_corruption"=TRUE SCOPE=SPFILE;

SQL> ALTER SYSTEM SET undo_management=MANUAL SCOPE=SPFILE;

Step 3: Restart Database

SQL> shutdown immediate;

SQL> startup mount;

Step 4: Open with RESETLOGS

SQL> alter database open resetlogs;

Step 5: Reconfigure UNDO

SQL> CREATE UNDO TABLESPACE undo1 

DATAFILE '<path>/undo1.dbf' SIZE 200M AUTOEXTEND ON;

SQL> ALTER SYSTEM SET undo_tablespace=undo1 SCOPE=SPFILE;

SQL> ALTER SYSTEM SET undo_management=AUTO SCOPE=SPFILE;

Step 6: Restart Database

SQL> shutdown immediate;

SQL> startup;

This method forces the database to open but may cause data inconsistency or corruption, so always take a full backup afterward.

Important Notes

• Always try Method 1 first
• Ensure all archive logs are available
• Avoid using _allow_resetlogs_corruption unless absolutely necessary
• After recovery:
• Take a full backup immediately
• Validate database integrity

Conclusion

The ORA-01194 error indicates that your database recovery is incomplete. The safest solution is to apply all required archive logs and open the database using RESETLOGS.

If logs are missing, you can force open the database—but this should only be done as a last resort due to potential data loss.

 

Frequently Asked Questions

Q1: What causes ORA-01194?

        Incomplete recovery or missing archive logs.

Q2: Can I open database without recovery?

        Yes, but only using RESETLOGS with risk of data loss.

MRP: Background Media Recovery process shutdown on Physical standby Dataguard

MRP: Background Media Recovery process shutdown on Physical standby Dataguard

Applies to: 

Oracle Database 10gR2

Description:

RFS process successfully running, archive logs successfully shipped from Primary server to standby, but archive logs did not apply. MRP process going to shutdown automatically. After doing some research work I found the following solution:

Solution:

SQL> alter database recover managed standby database cancel;

SQL> alter database recover managed standby database cancel;

alter database recover managed standby database cancel

*

ERROR at line 1:

ORA-16136: Managed Standby Recovery not active

SQL> alter database recover managed standby database disconnect from session;

Alert log:

alter database recover managed standby database disconnect from session

Sat Jun  9 09:51:56 2012

Attempt to start background Managed Standby Recovery process (PROD)

MRP0 started with pid=18, OS id=6591

Sat Jun  9 09:51:56 2012

MRP0: Background Managed Standby Recovery process started (PROD)

Managed Standby Recovery not using Real Time Apply

MRP0: Background Media Recovery terminated with error 1111

Sat Jun  9 09:52:01 2012

Errors in file /d01/oracle/proddb/10.2.0/admin/PROD_pebsdbdrrt/bdump/prod_mrp0_6591.trc:

ORA-01111: name for data file 59 is unknown - rename to correct file

ORA-01110: data file 59: '/d01/oracle/proddb/10.2.0/dbs/UNNAMED00059'

ORA-01157: cannot identify/lock data file 59 - see DBWR trace file

ORA-01111: name for data file 59 is unknown - rename to correct file

ORA-01110: data file 59: '/d01/oracle/proddb/10.2.0/dbs/UNNAMED00059'

Sat Jun  9 09:52:01 2012

Errors in file /d01/oracle/proddb/10.2.0/admin/PROD_pebsdbdrrt/bdump/prod_mrp0_6591.trc:

ORA-01111: name for data file 59 is unknown - rename to correct file

ORA-01110: data file 59: '/d01/oracle/proddb/10.2.0/dbs/UNNAMED00059'

ORA-01157: cannot identify/lock data file 59 - see DBWR trace file

ORA-01111: name for data file 59 is unknown - rename to correct file

ORA-01110: data file 59: '/d01/oracle/proddb/10.2.0/dbs/UNNAMED00059'

Sat Jun  9 09:52:01 2012

MRP0: Background Media Recovery process shutdown (PROD)

Sat Jun  9 09:52:02 2012

Completed: alter database recover managed standby database disconnect from session

SQL> select thread#,max(sequence#) from v$log_history group by thread#;

   THREAD# MAX(SEQUENCE#)

---------- --------------

248980

SQL> select severity, error_code,message,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') from v$dataguard_status;

SQL> archive log list

SQL> ho ls -ltra /archive/logs/

SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#

--------- ------------ ----------

ARCH      CONNECTED             0

ARCH      CONNECTED             0

RFS       IDLE                  0

RFS       IDLE                  0

SQL> ho ps -ef|grep mrp

oracle   30350 30330  0 09:24 pts/2    00:00:00 /bin/bash -c ps -ef|grep mrp

oracle   30352 30350  0 09:24 pts/2    00:00:00 grep mrp

SQL> select file#,name from v$datafile where name like '%UNNAMED%';

     FILE#

----------

NAME

--------------------------------------------------------------------------------

        59

/d01/oracle/proddb/10.2.0/dbs/UNNAMED00059

SQL> alter database create datafile '/d01/oracle/proddb/10.2.0/dbs/UNNAMED00059' AS '/data3/proddata/daw_INV_06.dbf';

SQL> alter database recover managed standby database cancel;

alter database recover managed standby database cancel

*

ERROR at line 1:

ORA-16136: Managed Standby Recovery not active

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select thread#,max(sequence#) from v$log_history group by thread#;

   THREAD# MAX(SEQUENCE#)

---------- --------------

         1         248980

SQL> /

   THREAD# MAX(SEQUENCE#)

---------- --------------

         1         248981

Problem has been resolved, now MRP process started and recovering the archive logs.

Your comments, especially which will help us improve the functionality, will be greatly appreciated :)