Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Status : Failure -Test failed: ORA-12541: Cannot connect. No listener at host localhost port 1521 Oracle Installation Errors

Abhinav SargarAug 11 2025

I have seen many students,industrial professionals and experts having Problems about Database connections of Listener as well as Database Connection of Oracle so here is the common questions with solutions - Oracle Installation Errors A to Z Guide.

Error: Status : Failure -Test failed: ORA-12541: Cannot connect. No listener at host localhost port 1521. (CONNECTION_ID=fzrxzZ13RM6w8pxb+1RIqQ==) https://docs.oracle.com/error-help/db/ora-12541/ what is this error and how to fix it if just installation is completed ?

First check if oracle is installed or not as it is mostly the case in new installation! Here's a step-by-step guide on how to install Oracle Database (Express Edition recommended for beginners), create a database, and set environment variables on Windows:

Step 1: Download Oracle Database XE (Express Edition)

Go to Oracle’s official site:
https://www.oracle.com/database/technologies/xe-downloads.html
Download the latest Oracle Database 21c XE or 18c XE with Java17 as it is compulsory (both are free and great for learning)

Step 2: Install Oracle Database XE

Run the downloaded installer (oracle-database-xe-21c-1.0-1.x64.exe or similar)
Follow the installer wizard:
- Accept license agreement
- Choose installation directory (default is fine)
- Set password for administrative accounts (SYS, SYSTEM)
- Installer automatically creates a default database instance called XE
Finish installation

Note: The installer sets up the listener and database automatically for you

Step 3: Set Environment Variables

Why?

So you can run commands like sqlplus and lsnrctl from any Command Prompt.

How?

Open Start Menu → search Environment Variables → select Edit the system environment variables
Click Environment Variables...
Under System variables, find Path → click Edit
Click New and add the path to Oracle binaries, for example:
(Optional but recommended) Add new system variables if not set:
- ORACLE_HOME → C:\oracle\product\21c\dbhomeXE
- ORACLE_SID → XE
Click OK on all dialogs and restart Command Prompt to apply changes.

C:\oracle\product\21c\dbhomeXE\bin

(Replace with your actual Oracle home folder)

Step 4: Verify Oracle Installation and Start Database

Open Command Prompt and check:

sqlplus / as sysdba

If successful, you’ll get the SQL prompt like:

SQL>

Start the database (if not started automatically)

STARTUP;

Step 5: Check Listener Status

lsnrctl status

You should see the listener running and Service "XE" registered.

Step 6: Connect Using SQL Developer or SQL*Plus

In SQL Developer, create a new connection with:
- Username: SYS or SYSTEM
- Password: (the one you set during installation)
- Hostname: localhost
- Port: 1521
- Service Name: XE
- Role: select SYSDBA if username is SYS
Test and connect!

Additional Tips

Oracle XE creates the default database automatically; you don't need to create .ora files manually.
For advanced setups, Oracle uses initialization parameter files (init.ora or spfile.ora), but XE handles this for you.
If you want to create a new database manually (rare for beginners), you’d use the Database Configuration Assistant (dbca), but XE doesn’t include this tool.

but there is no If you don’t see Service "XE" or xepdb1, let me know — that means the DB isn’t registered proxy.

Thanks for clarifying! If you're getting the ORA-12541: No listener error and you don't see Service "XE" or xepdb1 listed in lsnrctl status, that confirms:

❗ Your database instance is running, but it hasn't registered itself with the listener — so the listener can't route connections to it.

This is a common issue on fresh Oracle XE installations and is easy to fix.

✅ Step-by-step Fix: Register the Database with the Listener

🧰 Step 1: Open Command Prompt as Administrator

Run:

sqlplus / as sysdba

If you're in SQL*Plus now, continue below.

🧰 Step 2: Force the Database to Register with the Listener

Once in SQL*Plus, run:

ALTER SYSTEM REGISTER;

✅ This will manually register your database with the listener.

You should see:

System altered.

🧰 Step 3: Check Listener Again

Now in Command Prompt, run:

lsnrctl status

This time, you should see something like:

Service "XE" has 1 instance(s).

Instance "xe", status READY, has 1 handler(s) for this service...

Service "xepdb1" has 1 instance(s).

Instance "xe", status READY, has 2 handler(s) for this service...

🎉 Now your database is visible to the listener and ready for client connections (like SQL Developer).

✅ Step 4: Make the Registration Permanent

So you don't have to run ALTER SYSTEM REGISTER; every time:

1. Stay in SQL*Plus (/ as sysdba)

Run this command to set the LOCAL_LISTENER parameter:

ALTER SYSTEM SET LOCAL_LISTENER = '(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))' SCOPE=SPFILE;

Then restart the database for it to take effect:

SHUTDOWN IMMEDIATE;

STARTUP;

🔁 Optional: Save PDB State (If using XEPDB1)

To make sure your pluggable database (XEPDB1) opens automatically after reboot:

ALTER PLUGGABLE DATABASE XEPDB1 SAVE STATE;

✅ After This:

Your listener starts with Windows ✔
Your database auto-registers ✔
Your PDB opens automatically ✔
You can connect from SQL Developer ✔

Status : Failure -Test failed: ORA-12541: Cannot connect. No listener at host localhost port 1521. (CONNECTION_ID=fzrxzZ13RM6w8pxb+1RIqQ==) https://docs.oracle.com/error-help/db/ora-12541/ what is this error and how to fix it if just installation is completed ?

The error you're seeing:

ORA-12541: TNS: No listener

means that Oracle SQL Developer (or any client) is trying to connect to the database, but there's no listener process running on the specified host (localhost) and port (1521).

This is very common right after installing Oracle or you did your work for first time, shut down os and then started again, especially on Windows, and is easy to fix.

✅ What Causes ORA-12541?

The listener service (OracleXETNSListener) is not running
The hostname or port in your SQL Developer connection is incorrect
The database wasn't fully initialized yet after installation
Firewall or antivirus is blocking port 1521 (less common)

🛠️ How to Fix It (Step-by-Step)

✅ 1. Start the Oracle Listener

Option A: Using Command Prompt

Open Command Prompt as Administrator
Run:

lsnrctl start

You should see:

TNSLSNR for 64-bit Windows: ...

Starting tnslsnr: please wait...

...

The command completed successfully

Option B: Using Windows Services

Press Windows + R, type services.msc, hit Enter
Find the service named:
Right-click → Start
Set Startup Type to Automatic so it runs next time automatically

OracleXETNSListener

✅ 2. Confirm Listener Is Running

In Command Prompt, run:

lsnrctl status

You should see something like:

STATUS of the LISTENER

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

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))

Services Summary...

Service "XE" has 1 instance(s).

Instance "xe", status READY, has 1 handler(s) for this service...

If you don’t see Service "XE" or xepdb1, let me know — that means the DB isn’t registered proy.

✅ 3. Now Try Connecting from SQL Developer

Make sure your connection settings are:

Field

Value

Username

SYS or SYSTEM

Password

(whatever you set)

Hostname

localhost

Port

1521

Service name

XEPDB1 or XE (try both)

Role

SYSDBA (if using SYS)

You can try both service names (XE and XEPDB1) — depends on where you're trying to connect.

✅ 4. Make This Automatic (Optional but Recommended)

Go to services.msc
Set both of these services to Startup type = Automatic:
- OracleXETNSListener
- OracleServiceXE

This way you won’t need to start anything manually after reboot.

🧠 TL;DR

ORA-12541 = Listener is not running
✅ Fix = Start the listener (lsnrctl start)
Then connect in SQL Developer using correct settings (localhost, port 1521, service XE or XEPDB1)

but there is no If you don’t see Service "XE" or xepdb1, let me know — that means the DB isn’t registered proy.

Thanks for clarifying! If you're getting the ORA-12541: No listener error and you don't see Service "XE" or xepdb1 listed in lsnrctl status, that confirms:

❗ Your database instance is running, but it hasn't registered itself with the listener — so the listener can't route connections to it.

This is a common issue on fresh Oracle XE installations and is easy to fix.

✅ Step-by-step Fix: Register the Database with the Listener

🧰 Step 1: Open Command Prompt as Administrator

Run:

sqlplus / as sysdba

If you're in SQL*Plus now, continue below.

🧰 Step 2: Force the Database to Register with the Listener

Once in SQL*Plus, run:

ALTER SYSTEM REGISTER;

✅ This will manually register your database with the listener.

You should see:

System altered.

🧰 Step 3: Check Listener Again

Now in Command Prompt, run:

lsnrctl status

This time, you should see something like:

Service "XE" has 1 instance(s).

Instance "xe", status READY, has 1 handler(s) for this service...

Service "xepdb1" has 1 instance(s).

Instance "xe", status READY, has 2 handler(s) for this service...

🎉 Now your database is visible to the listener and ready for client connections (like SQL Developer).

✅ Step 4: Make the Registration Permanent

So you don't have to run ALTER SYSTEM REGISTER; every time:

1. Stay in SQL*Plus (/ as sysdba)

Run this command to set the LOCAL_LISTENER parameter:

ALTER SYSTEM SET LOCAL_LISTENER = '(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))' SCOPE=SPFILE;

Then restart the database for it to take effect:

SHUTDOWN IMMEDIATE;

STARTUP;

🔁 Optional: Save PDB State (If using XEPDB1)

To make sure your pluggable database (XEPDB1) opens automatically after reboot:

ALTER PLUGGABLE DATABASE XEPDB1 SAVE STATE;

✅ After This:

Your listener starts with Windows ✔
Your database auto-registers ✔
Your PDB opens automatically ✔
You can connect from SQL Developer ✔

but what if there is no db registered as there is no oracle installed, where to give paths, etc ??

Got it! If you see no database registered with the listener at all, and you’re worried that Oracle database itself might not be fully installed or configured yet, here’s what to check and do:

What if no DB is registered, or you think Oracle DB isn’t installed/configured proy yet?

1️⃣ Check Oracle Installation

Make sure Oracle Database (XE or full version) is actually installed on your machine.
For Oracle XE (Express Edition), you should see something like OracleServiceXE in your Windows Services.
If it’s missing, you may need to install Oracle Database first. Oracle Client alone does not include a database!

2️⃣ Verify Oracle Home and Environment Variables

Oracle requires some environment variables to work proy:

ORACLE_HOME — points to the directory where Oracle is installed, e.g., C:\Oracle\product\21c\dbhomeXE
ORACLE_SID — your database SID, e.g., XE
PATH — should include %ORACLE_HOME%\bin so commands like sqlplus and lsnrctl are recognized

You can check these from Command Prompt:

echo %ORACLE_HOME%

echo %ORACLE_SID%

If these are blank or incorrect, you need to set them.

3️⃣ How to Set ORACLE_HOME and ORACLE_SID (Windows)

Temporary for current Command Prompt session

set ORACLE_HOME=C:\Oracle\product\21c\dbhomeXE

set ORACLE_SID=XE

set PATH=%ORACLE_HOME%\bin;%PATH%

Permanent (Recommended)

Open Control Panel > System > Advanced system settings > Environment Variables
Under System variables, click New or Edit to add or update:
- ORACLE_HOME → C:\Oracle\product\21c\dbhomeXE (or your actual install folder)
- ORACLE_SID → XE
- Append %ORACLE_HOME%\bin to the PATH variable
Click OK, then restart Command Prompt.

4️⃣ Start the Oracle Database Instance Manually

Once environment variables are correct:

Open Command Prompt and run:

sqlplus / as sysdba

If you connect successfully, start the database if it’s not running:

STARTUP;

If it says “Database already started” or “Instance started,” great.

5️⃣ Start Listener

Back in Command Prompt:

lsnrctl start

6️⃣ Check Listener Status and Registered Services

lsnrctl status

If your DB instance is running and registered correctly, you should see Service "XE" or similar.

7️⃣ Troubleshooting

If you get errors starting sqlplus or ORACLE_SID is unknown, the database might not be created yet.
Oracle XE usually creates XE instance during installation, but for full versions you might need to run the database creation scripts (dbca tool).
You can also check Oracle documentation or installer logs to confirm installation success.

Summary:

Step

Action

Install Oracle DB

Make sure Oracle Database (XE/full) is installed

Set Env Variables

ORACLE_HOME, ORACLE_SID, and add %ORACLE_HOME%\bin to PATH

Start DB Instance

Use sqlplus / as sysdba and STARTUP

Start Listener

Use lsnrctl start

Verify Listener

Use lsnrctl status and check for service names

we did this but we didnt made sys or system and gave password so there will be listener error

Gotcha — if during installation you skipped setting passwords for SYS or SYSTEM users, or those accounts weren’t proxy configured, it can definitely cause connection or listener-related errors. Here’s what you can do to fix that:

What’s going on?

The listener is running, but the database might not be fully configured because admin users (SYS, SYSTEM) don’t have valid passwords.
Without these, tools like SQL Developer can’t connect, and the DB might not register proy with the listener.
This might cause errors like ORA-12541 No listener or login failures.

How to fix it

Step 1: Connect locally as OS authenticated user (no password needed)

Open Command Prompt and run:

sqlplus / as sysdba

This connects you as the privileged user without needing a password.

Step 2: Set/reset passwords for SYS and SYSTEM

At the SQL prompt, run:

ALTER USER SYS IDENTIFIED BY YourNewStrongPassword;

ALTER USER SYSTEM IDENTIFIED BY YourNewStrongPassword;

Make sure to replace YourNewStrongPassword with a secure password you choose.

Step 3: Check if the database is open and listener registered

Make sure the DB is running:

SELECT status FROM v$instance;

If it’s not started:

STARTUP;

Force DB to register with listener:

ALTER SYSTEM REGISTER;

Step 4: Exit and test connection from SQL Developer

Use the updated passwords you set for SYS or SYSTEM
Host: localhost
Port: 1521
Service Name: XE
Role: SYSDBA for SYS user

Step 5: Make sure listener is running (from command prompt)

lsnrctl status

If not started:

lsnrctl start

Summary

Issue

Fix

No password for SYS

Connect as / as sysdba and ALTER USER SYS IDENTIFIED BY ...

Listener errors

Make sure listener started & DB registered (ALTER SYSTEM REGISTER)

Can't connect

Use correct user/password & check service name

at which step we give password in sys or system during installation ??

Great question! Here’s how it usually works during Oracle Database installation (especially Oracle XE):

When do you set passwords for SYS and SYSTEM during installation?

Oracle XE installer typically prompts you to set a password for the administrative accounts — SYS and SYSTEM — at the end of the installation wizard.
This step is mandatory in most cases; the installer won’t finish until you enter a password for these accounts.
The password you provide will be the same for both SYS and SYSTEM.

What if you skipped or missed this step?

Sometimes people just click through or there’s an issue, and the password doesn’t get set.
In that case, you’ll get errors connecting because these users don’t have valid passwords or aren’t enabled proy.
You cannot connect remotely or even locally using those users until the passwords are set.

How to check/set passwords if you missed it?

Open Command Prompt as Administrator
Connect as OS authenticated privileged user:
Set/reset passwords for SYS and SYSTEM:
Now you can connect remotely using these users with the new passwords.

sqlplus / as sysdba

ALTER USER SYS IDENTIFIED BY YourNewPassword;

ALTER USER SYSTEM IDENTIFIED BY YourNewPassword;

Summary:

During Installation Step

What Happens

Near the end of installer wizard

Prompt to set password for SYS/SYSTEM

Password you enter

Sets the password for both accounts

If skipped or missed

You must reset using ALTER USER later

walk through the installation and show exactly where to enter the passwords or how to reset them afterward!

Absolutely! Here’s a detailed walk-through for Oracle Database XE installation and how/where to set the SYS and SYSTEM passwords — plus how to reset them if you missed that step.

Oracle Database XE Installation & Password Setup Walkthrough

Step 1: Download Oracle Database XE

Get the latest Oracle Database Express Edition (XE) from Oracle’s official site.

Step 2: Run the Installer

Launch the installer executable (.exe for Windows).
Click Next to move through the steps.

Step 3: Choose Installation Location

Pick the folder where Oracle XE will be installed (or accept default).
Click Next.

Step 4: Configure Database Password (Important!)

This is the key step where you set the passwords.

You will see a screen labeled something like:

Specify Database Password

It will ask you to:

Enter a password for SYS and SYSTEM users.
Usually, there is one field where you enter the password (both users share this password).
Enter a strong password and confirm it.

This password is critical — you’ll use it later to connect as SYS or SYSTEM.

Step 5: Complete Installation

Click Next and then Install.
Oracle XE installs the software, creates the database, and configures services.
After finishing, you may see the connection URL and sample commands.

What if you skipped setting the password or forgot it?

How to Reset SYS/SYSTEM Passwords After Installation

Step 1: Open Command Prompt as Administrator

Step 2: Connect as OS Authenticated SYSDBA

sqlplus / as sysdba

Step 3: Reset passwords

ALTER USER SYS IDENTIFIED BY YourNewPassword;

ALTER USER SYSTEM IDENTIFIED BY YourNewPassword;

Replace YourNewPassword with the new password you want.

Step 4: Exit

EXIT;

Step 5: Test login

Try connecting with new password:

sqlplus sys/YourNewPassword@XE as sysdba

Summary

Step

What happens

Installation

Set SYS/SYSTEM password in password screen

If missed

Connect / as sysdba & use ALTER USER to reset

Checking your Oracle install folder

To check your Oracle install folder on Windows, here are some quick ways:

1. Check Default Installation Path

Oracle usually installs under something like:

C:\oracle\product\\dbhomeXE
Example:
C:\oracle\product\21c\dbhomeXE

or for older versions:

C:\app\\product\\dbhome_1

2. Use Environment Variables

Open Command Prompt and type:

echo %ORACLE_HOME%

If set, this will show the Oracle home folder path.

3. Check Registry (if needed)

You can look up Oracle install path in Windows Registry:

Open regedit
Navigate to:
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE

Inside you might find keys like ORACLE_HOME or KEY_OraDB21Home1 showing install paths.

4. Check Services (for XE)

Open Services (services.msc) and find OracleServiceXE
Right-click → Properties → check Path to executable
It usually shows the path where Oracle database binaries live

5. Check Listener Config File

Look at listener.ora file:

Default location:
%ORACLE_HOME%\network\admin\listener.ora

Open it in a text editor to confirm paths.

Setting environment variables step-by-step

Sure! Here’s a clear step-by-step guide to set environment variables for Oracle on Windows — this will make tools like sqlplus and lsnrctl work smoothly from any Command Prompt.

How to Set Oracle Environment Variables on Windows

Step 1: Open Environment Variables Editor

Press Windows key and type: environment variables
Select Edit the system environment variables
In the System Properties window, click the Environment Variables... button

Step 2: Add or Edit ORACLE_HOME

Under System variables (bottom part), click New (if ORACLE_HOME doesn’t exist) or Edit (if it exists)
Variable name:
Variable value: The full path to your Oracle home folder
Example:
Click OK

ORACLE_HOME

C:\oracle\product\21c\dbhomeXE

Step 3: Add or Edit ORACLE_SID

Under System variables, click New (or Edit if it exists)
Variable name:
Variable value: Your database SID, usually:
Click OK

ORACLE_SID

Step 4: Add Oracle bin directory to the Path

In System variables, find the variable called Path
Select it and click Edit
Click New and add the Oracle bin directory:
Click OK to close all dialogs

%ORACLE_HOME%\bin

Step 5: Restart Command Prompt

Close any open Command Prompt windows and open a new one
This loads the updated environment variables

Step 6: Verify

Run:

echo %ORACLE_HOME%

echo %ORACLE_SID%

sqlplus / as sysdba

If Oracle is installed and environment variables are set correctly, you should connect successfully.

Starting your DB and listener from scratch

Got it! Here’s how to start your Oracle Database and Listener from scratch on Windows — step-by-step, assuming Oracle XE is installed:

Starting Oracle Database and Listener on Windows

Step 1: Open Command Prompt as Administrator

Press Windows key, type cmd
Right-click Command Prompt → Run as administrator

Step 2: Start the Listener

In the command prompt, run:

lsnrctl start

This starts the Oracle listener service that handles incoming DB connections.
You should see messages like:
If the listener is already running, it will tell you.

Starting LISTENER ...

The listener supports no services

The command completed successfully

Step 3: Connect to Oracle as SYSDBA

Run:

sqlplus / as sysdba

This connects you locally as a privileged user without a password.

Step 4: Start the Database

At the SQL prompt, run:

STARTUP;

This command starts your database instance.
If it’s already started, you’ll see an error like ORA-01081: cannot start already-running ORACLE.
If it’s started successfully, you’ll see messages about the instance starting and database opening.

Step 5: Verify Database Status

Run:

SELECT status FROM v$instance;

Should return OPEN

Step 6: Register Database with Listener (if needed)

Run:

ALTER SYSTEM REGISTER;

Forces the database to register itself with the listener.
Helps fix connection issues.

Step 7: Exit SQL*Plus

EXIT;

Step 8: Test Connection Using SQL Developer or SQL*Plus

Use:

Username: SYS or SYSTEM
Password: (set during installation or via ALTER USER)
Hostname: localhost
Port: 1521
Service Name: XE
Role: SYSDBA (if using SYS)

Summary commands in one place:

lsnrctl start

sqlplus / as sysdba

SQL> STARTUP;

SQL> SELECT status FROM v$instance;

SQL> ALTER SYSTEM REGISTER;

SQL> EXIT;

i forgot the password for oracle in developer database what should i do

If you've forgotten the Oracle Database user password (like HR, SYSTEM, or any other user) when using SQL Developer, here's how you can reset it, depending on your level of access:

🔐🔧 Situation 1: You know the SYS or SYSTEM user password

If you can log in as a user with DBA privileges (like SYS or SYSTEM), you can reset any user's password.

✅ Steps:

Open SQL Developer.
Connect as SYSTEM (or SYS as SYSDBA).

ALTER USER username IDENTIFIED BY new_password;

🔁 Example:

ALTER USER HR IDENTIFIED BY MyNewPassword123;

Then, disconnect and log in using the updated password.

❌ Situation 2: You forgot the SYSTEM/SYS password too

You need to reset it from the command line or SQL*Plus, depending on how Oracle is installed:

✅ Steps (for Windows/Linux local install):

Open a terminal or command prompt on the machine where Oracle is installed.
Start SQL*Plus as OS authenticated user:

sqlplus / as sysdba

This works only if you're logged into the OS as a user that belongs to the ORA_DBA group (Windows) or the dba group (Linux).

Reset the SYSTEM password:

ALTER USER SYSTEM IDENTIFIED BY NewPassword123;

Then, you can log in using SYSTEM in SQL Developer and reset other users.

🛠 Tips:

Oracle usernames/passwords are case-sensitive in newer versions.
Always grant minimal privileges if you’re creating or resetting passwords for new users.

is there any way we can check the password ??

No, you cannot check or view an existing password for any user in Oracle — not even if you're logged in as SYSDBA. Oracle stores passwords in a hashed and encrypted form for security reasons.

🔒 Why Can't You See the Password?

Oracle (like all secure database systems) does not store plaintext passwords. Instead, it stores a hash of the password, usually in the DBA_USERS or USER$ table, so the system can verify passwords without needing to store the actual text.

🕵️‍♂️ What You Can Do:

✅ 1. Check if a user is locked or expired

SELECT username, account_status

FROM dba_users

WHERE username = 'HR';

Result might be:

OPEN – account is active
LOCKED – account is locked
EXPIRED – password expired

You can unlock and reset like this:

ALTER USER HR IDENTIFIED BY NewPassword123;

ALTER USER HR ACCOUNT UNLOCK;

✅ 2. Check the password hash (not useful for login)

SELECT username, password FROM sys.user$ WHERE name = 'HR';

This gives you the password hash, but not the actual password — it’s useless unless you're doing deep internal diagnostics or cracking (which is unethical and unnecessary).

✅ 3. Enable/Reset Password for Default Users

For example, if you want to enable and reset the HR sample user:

ALTER USER HR IDENTIFIED BY MyNewHRPassword;

ALTER USER HR ACCOUNT UNLOCK;

🛑 Important:

No system (Oracle, MySQL, PostgreSQL, etc.) will ever allow you to retrieve a user's original password.
If you forgot it, reset it.
If you're using Oracle Cloud, password recovery may require access to the admin console or cloud dashboard.

yes i couldn’t access sys

Got it — if you can’t access SYS (or SYSTEM) and you’re using Oracle locally, don’t worry — you can likely still reset it using OS authentication.

Here’s a step-by-step guide based on your environment:

✅ Step-by-Step: Reset SYS/SYSTEM Password via OS Authentication

📌 This works if:

You're using Oracle Database installed locally (Windows or Linux).
You're logged into the OS as a user who belongs to the DBA group (like ORA_DBA on Windows).

🔧 For Windows:

?? Steps:

Open Command Prompt as Administrator
- Press Start, type cmd, right-click and choose "Run as administrator"
Connect to Oracle as SYSDBA using OS authentication:

sqlplus / as sysdba

Once connected, reset the password:

ALTER USER SYS IDENTIFIED BY NewSysPassword123;

ALTER USER SYSTEM IDENTIFIED BY NewSystemPass456;

Exit SQL*Plus:

EXIT;

Now open SQL Developer and try logging in using SYS or SYSTEM with the new password.

🔧 For Linux:

🪜 Steps:

Open terminal.
Switch to the Oracle user if needed:

sudo su - oracle

Start SQL*Plus as SYSDBA:

sqlplus / as sysdba

Reset the password:

ALTER USER SYS IDENTIFIED BY NewSysPassword123;

ALTER USER SYSTEM IDENTIFIED BY NewSystemPass456;

Exit:

EXIT;

💡 Don’t Know Where SQL*Plus Is?

You can usually find it under:

Windows: C:\oracle\product\\dbhome_1\BIN\sqlplus.exe
Linux: try typing sqlplus directly or find under $ORACLE_HOME/bin/sqlplus

even after successful alteration, it showed this when i logged in using the same Status : Failure -Test failed: ORA-12541: Cannot connect. No listener at host localhost port 1521. (CONNECTION_ID=fzrxzZ13RM6w8pxb+1RIqQ==) https://docs.oracle.com/error-help/db/ora-12541/

Thanks for the detail — you're seeing this error:

ORA-12541: TNS: no listener

This means: Your Oracle database listener is not running, so SQL Developer (or any client) cannot connect to the database on localhost:1521.

✅ What’s a Listener?

In Oracle, the listener is a background service that listens for incoming client connection requests. Without it, no app (like SQL Developer) can connect to the database over TCP/IP.

🛠️ Step-by-Step Fix for ORA-12541

✅ Step 1: Start the Listener

🔹 On Windows:

Open Command Prompt as Administrator.
Run:

lsnrctl start

You should see output like:

Starting /.../tnslsnr: please wait...

...

The command completed successfully

🔹 On Linux:

Open terminal as the Oracle user:

sudo su - oracle

Then run:

lsnrctl start

✅ Step 2: Check Listener Status

To confirm it’s running:

lsnrctl status

You should see something like:

markdown

Status of the listener

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

Alias LISTENER

Listening on (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))

✅ Step 3: Try SQL Developer Again

Go back to SQL Developer and test the connection again.

Ensure:

Hostname = localhost
Port = 1521
SID or Service Name = matches your DB (common default = XE or ORCL)
Use the new password you set earlier

🧪 Optional: Check What Your Database SID/Service Name Is

From SQL*Plus or command line (sqlplus / as sysdba), run:

SELECT name FROM v$database;

This tells you what to put in the Service Name field in SQL Developer.

✅ Example SQL Developer Connection Setup:

Field

Value

Username

SYS or SYSTEM

Password

(your new password)

Hostname

localhost

Port

1521

Service Name

XE or ORCL (depends)

Role

SYSDBA (for SYS user)

when i did following, i got this C:\>lsnrctl start LSNRCTL for 64-bit Windows: Version 21.0.0.0.0 - Production on 11-AUG-2025 19:40:24 Copyright (c) 1991, 2021, Oracle. All rights reserved. Starting tnslsnr: please wait... TNSLSNR for 64-bit Windows: Version 21.0.0.0.0 - Production System parameter file is C:\Oracle\homes\OraDB21Home1\network\admin\listener.ora Log messages written to C:\Oracle\diag\tnslsnr\DESKTOP-BBG9HL1\listener\alert\log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DESKTOP-BBG9HL1)(PORT=1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DESKTOP-BBG9HL1.bbrouter)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for 64-bit Windows: Version 21.0.0.0.0 - Production Start Date 11-AUG-2025 19:40:27 Uptime 0 days 0 hr. 0 min. 8 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Default Service XE Listener Parameter File C:\Oracle\homes\OraDB21Home1\network\admin\listener.ora Listener Log File C:\Oracle\diag\tnslsnr\DESKTOP-BBG9HL1\listener\alert\log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DESKTOP-BBG9HL1)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc))) Services Summary... Service "CLRExtProc" has 1 instance(s). Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully

Thanks — that output helps a lot. Here's what it means and what to do next.

✅ What You Did Right:

The Oracle Listener is now running successfully on:
- HOST = DESKTOP-BBG9HL1
- PORT = 1521
Listener is listening, and the default service is XE, which is perfect if you installed Oracle Database 21c XE.

❗️But What's Missing?

The actual database service (like XE or ORCL) isn't showing up under Services Summary.

You only see this:

Service "CLRExtProc" has 1 instance(s).

That means:

The database itself is not started yet — the listener is ready, but there's nothing running to connect to.

🛠️ Step-by-Step: Start the Database Manually

You’ll need to manually start the database instance using SQL*Plus.

✅ Step 1: Open Command Prompt as Administrator

✅ Step 2: Connect as SYSDBA

sqlplus / as sysdba

You should see:

SQL*Plus: Release ...

Connected to an idle instance.

✅ Step 3: Start the Database

STARTUP;

You should get messages like:

ORACLE instance started.

Total System Global Area ...

Database mounted.

Database opened.

✅ Step 4: Confirm the Service Is Now Visible to Listener

Run:

lsnrctl status

Now you should see:

Service "XE" has 1 instance(s).

Instance "xe", status READY, has 1 handler(s) for this service...

✅ Step 5: Reconnect from SQL Developer

Now go back to SQL Developer and try your connection again with these settings:

Setting

Value

Username

SYS or SYSTEM

Password

(new one you set)

Hostname

localhost or DESKTOP-BBG9HL1

Port

1521

Service Name

Role

SYSDBA (for SYS)

✅ Summary

✅ Step

Done?

Listener started

✅

Database started

🔄 (do this via sqlplus / as sysdba + STARTUP;)

SQL Developer connect

🔄 (retry after DB is up)

SQL> STARTUP; ORA-01081: cannot start already-running ORACLE - shut it down first gives this error

Great — that error is actually a good sign.

✅ What It Means:

ORA-01081: cannot start already-running ORACLE - shut it down first

This means:
🎉 Your database is already running!

You don't need to run STARTUP — it’s already up and active.

✅ Next Steps:

Now that:

✅ The listener is running
✅ The database is already started

You can now go to SQL Developer and connect using the correct details.

🧩 Double-Check Your SQL Developer Connection Settings:

✅ Use These Values:

Field

Value

Connection Name

Anything (e.g. XE_SYS)

Username

SYS or SYSTEM

Password

The one you reset earlier

Hostname

localhost or DESKTOP-BBG9HL1

Port

1521

Service Name

Role

SYSDBA (if logging in as SYS)

⚠️ Common Mistakes to Avoid:

Don't use SID = XE unless you're explicitly choosing "SID" instead of "Service Name" in SQL Developer.
Make sure "Connection Type" is set to "Basic", not TNS or Advanced.

🔍 If You're Still Getting ORA-12541

Try this test:

Run:

tnsping XE

If this returns a success message (e.g., OK (10 msec)), your TNS is working.

If it fails, try using DESKTOP-BBG9HL1 instead of localhost in SQL Developer.

🚀 Recap

✅ Component

Status

Listener

✅ Running

Database (XE)

✅ Running

SYS/SYSTEM Password

✅ Reset

SQL Developer

🔄 Now ready to connect

I closed sqldeveloper still it says SQL> STARTUP; ORA-01081: cannot start already-running ORACLE - shut it down first

Thanks for the update. Here's a clear explanation of what's happening:

⚠️ You're Seeing:

SQL> STARTUP;

ORA-01081: cannot start already-running ORACLE - shut it down first

✅ What This Means:

The Oracle Database instance is already running.
It has nothing to do with SQL Developer being open or closed — SQL Developer is just a client.
You're already connected to the database via sqlplus / as sysdba.
Since the instance is already started, trying to STARTUP again causes this error.

✅ What You Should Do:

Since your database is already running, you don’t need to run STARTUP again.

Instead, just connect and use it, or go ahead and open SQL Developer and try connecting.

🔍 If You Want to Confirm It's Running:

From the SQL*Plus prompt, run:

SELECT status FROM v$instance;

Expected output:

markdown

STATUS

-------

OPEN

That means everything is ready.

🚀 Now Open SQL Developer and Connect:

In SQL Developer, use the following connection settings:

Field

Value

Connection Name

Anything (e.g., XE_SYS)

Username

SYS or SYSTEM

Password

Your new password

Hostname

localhost or DESKTOP-BBG9HL1

Port

1521

Service Name

Role

SYSDBA (if using SYS)

✅ Optional: If You Really Want to Restart the DB

Only do this if you need to restart Oracle (not required unless there’s a problem):

SHUTDOWN IMMEDIATE;

STARTUP;

⚠️ Only do this on a local/dev environment — never on production without approval.

Let me know if SQL Developer is working now or if it’s still giving any error. We’re very close — the DB is running!

i did this SQL> STARTUP; ORA-01081: cannot start already-running ORACLE - shut it down first SQL> lsnrctl status SP2-0734: unknown command beginning "lsnrctl st..." - rest of line ignored. SQL> lsnrctl start SP2-0734: unknown command beginning "lsnrctl st..." - rest of line ignored. SQL> EXIT; Disconnected from Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 C:\>lsnrctl start LSNRCTL for 64-bit Windows: Version 21.0.0.0.0 - Production on 11-AUG-2025 20:10:54 Copyright (c) 1991, 2021, Oracle. All rights reserved. TNS-01106: Listener using listener name LISTENER has already been started C:\>lsnrctl status LSNRCTL for 64-bit Windows: Version 21.0.0.0.0 - Production on 11-AUG-2025 20:11:01 Copyright (c) 1991, 2021, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DESKTOP-BBG9HL1.bbrouter)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for 64-bit Windows: Version 21.0.0.0.0 - Production Start Date 11-AUG-2025 19:40:27 Uptime 0 days 0 hr. 30 min. 33 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Default Service XE Listener Parameter File C:\Oracle\homes\OraDB21Home1\network\admin\listener.ora Listener Log File C:\Oracle\diag\tnslsnr\DESKTOP-BBG9HL1\listener\alert\log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DESKTOP-BBG9HL1)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc))) Services Summary... Service "CLRExtProc" has 1 instance(s). Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully

Thanks for sharing all that — you're very close to the solution. Let's clarify where things stand and how to fix the last issue.

🧩 Current Status Summary:

✅ Your listener is running — confirmed.
✅ Oracle instance is already started — confirmed via ORA-01081.
❌ But — your actual database service (XE) is not registered with the listener — that's why you're still getting connection errors in SQL Developer.

This is why this section of the output is concerning:

Services Summary...

Service "CLRExtProc" has 1 instance(s).

There’s no mention of:

Service "XE" has 1 instance(s).

✅ Goal: Register the XE Database with the Listener

🔧 Step 1: Connect via SQL*Plus

From the Windows command prompt, run:

sqlplus / as sysdba

You’ll be back in the SQL prompt.

🔧 Step 2: Manually Register the Database with the Listener

Run:

ALTER SYSTEM REGISTER;

Then exit SQL*Plus:

EXIT;

🔧 Step 3: Check Listener Again

Back at the command prompt, run:

lsnrctl status

✅ Expected output should now include:

Service "XE" has 1 instance(s).

Instance "XE", status READY, has 1 handler(s) for this service...

🎯 Then: Try SQL Developer Again

Use the following connection settings:

Field

Value

Username

SYS or SYSTEM

Password

(the new one you set)

Hostname

localhost or DESKTOP-BBG9HL1

Port

1521

Service Name

Role

SYSDBA (if using SYS)

💡 Optional: Enable Automatic Service Registration (So This Doesn’t Happen Again)

If this happens every time you reboot your PC, update your initXE.ora or spfileXE.ora to make sure service registration happens automatically.

But for now — let's just get you logged in.

✅ Let me know:

What you see after running ALTER SYSTEM REGISTER;
What lsnrctl status shows afterwards
And whether SQL Developer connects

You're 1 step away from success.

it gave this error C:\>lsnrctl status LSNRCTL for 64-bit Windows: Version 21.0.0.0.0 - Production on 11-AUG-2025 20:12:47 Copyright (c) 1991, 2021, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DESKTOP-BBG9HL1.bbrouter)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for 64-bit Windows: Version 21.0.0.0.0 - Production Start Date 11-AUG-2025 19:40:27 Uptime 0 days 0 hr. 32 min. 20 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Default Service XE Listener Parameter File C:\Oracle\homes\OraDB21Home1\network\admin\listener.ora Listener Log File C:\Oracle\diag\tnslsnr\DESKTOP-BBG9HL1\listener\alert\log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DESKTOP-BBG9HL1)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc))) Services Summary... Service "CLRExtProc" has 1 instance(s). Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully

Thanks — that confirms your issue clearly:

❌ Your database (XE) is not registered with the listener,
even though the listener is up and the database is started.

Let’s fix that now — step by step.

✅ Step-by-Step Fix for “Database Not Registered with Listener” Issue

This is a common problem on Oracle XE (especially 21c on Windows), and it's fixable.

✅ 1. Open Command Prompt as Admin

Run:

sqlplus / as sysdba

You should get to the SQL> prompt.

✅ 2. Run This Command

ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))';

Then:

ALTER SYSTEM REGISTER;

Then:

EXIT;

✅ 3. Re-check Listener Status

Back in the command prompt, run:

lsnrctl status

You should now see something like:

Service "XE" has 1 instance(s).

Instance "xe", status READY, has 1 handler(s) for this service...

✅ 4. Open SQL Developer

Use this to connect:

Field

Value

Username

SYS or SYSTEM

Password

(your new one)

Hostname

localhost

Port

1521

Service Name

Role

SYSDBA (for SYS user)

🧠 Why This Happens

In Oracle XE, the database doesn't always register itself with the listener automatically on Windows unless LOCAL_LISTENER is set. You just fixed that by telling Oracle where to find the listener.

✅ Bonus: Make It Permanent (Optional)

You can add the LOCAL_LISTENER setting to your initXE.ora or use an SPFILE, but for now, this should survive reboots unless you restart the listener before the DB.

C:\>lsnrctl status LSNRCTL for 64-bit Windows: Version 21.0.0.0.0 - Production on 11-AUG-2025 20:15:19 Copyright (c) 1991, 2021, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DESKTOP-BBG9HL1.bbrouter)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for 64-bit Windows: Version 21.0.0.0.0 - Production Start Date 11-AUG-2025 19:40:27 Uptime 0 days 0 hr. 34 min. 52 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Default Service XE Listener Parameter File C:\Oracle\homes\OraDB21Home1\network\admin\listener.ora Listener Log File C:\Oracle\diag\tnslsnr\DESKTOP-BBG9HL1\listener\alert\log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DESKTOP-BBG9HL1)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc))) Services Summary... Service "CLRExtProc" has 1 instance(s). Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "XE" has 1 instance(s). Instance "xe", status READY, has 2 handler(s) for this service... Service "XEXDB" has 1 instance(s). Instance "xe", status READY, has 1 handler(s) for this service... Service "d5543769b3214795ab5e6d7c2aa990ba" has 1 instance(s). Instance "xe", status READY, has 2 handler(s) for this service... Service "xepdb1" has 1 instance(s). Instance "xe", status READY, has 2 handler(s) for this service... The command completed successfully

Awesome! 🙌

Your listener status now shows:

Service "XE" has 1 instance(s).

Instance "xe", status READY, has 2 handler(s) for this service...

which means your database is registered proy with the listener and is ready to accept connections.

What to do next:

Open SQL Developer
Use these connection settings:

Field

Value

Username

SYS or SYSTEM

Password

(your reset password)

Hostname

localhost or DESKTOP-BBG9HL1

Port

1521

Service Name

Role

SYSDBA (if SYS)

Test the connection

You should be able to connect successfully now!

Congrats on getting the listener and database synced up — you’re almost done! 🎉

how to set password for it ??

To set or reset the password for a user (like SYS or SYSTEM) in Oracle, you can do it easily via SQL*Plus. Here’s how:

Step-by-step to set/reset password:

Open Command Prompt and connect as SYSDBA:
Change the password for your user (SYS, SYSTEM, or any other user):
Exit SQL*Plus:

sqlplus / as sysdba

ALTER USER SYS IDENTIFIED BY new_password;

Or for SYSTEM:

ALTER USER SYSTEM IDENTIFIED BY new_password;

Replace new_password with the password you want.

EXIT;

Example:

ALTER USER SYS IDENTIFIED BY MyNewPass123!;

After this, use that password in SQL Developer to connect.

but i want this to be fixed. i mean i cant do all these everytime, so there must be a way for this ??

Got it — you want a permanent password setup so you don’t have to keep resetting it or deal with connection issues every time.

Here’s what you need to do for a stable, permanent Oracle XE setup:

1. Set a strong password once (as you did)

Use:

ALTER USER SYS IDENTIFIED BY YourSecurePassword;

This password will stay until you explicitly change it. So no need to reset repeatedly unless you want to.

2. Create and use a dedicated normal user for day-to-day work

SYS and SYSTEM are powerful admin accounts — best not to use them for regular queries.
Create your own user once, assign privileges, and use it in SQL Developer.

Example:

CREATE USER myuser IDENTIFIED BY mypassword;

GRANT CONNECT, RESOURCE TO myuser;

Then use myuser/mypassword in SQL Developer for everyday work.

3. Make sure Oracle starts automatically

On Windows, Oracle XE usually installs as a Windows service that starts with the OS.
Check Services (services.msc) for OracleServiceXE and OracleXETNSListener — set their startup type to Automatic.
This way, listener and database start automatically when your PC boots.

4. Ensure Listener and Database are proy configured

The LOCAL_LISTENER parameter we set makes sure the database registers itself with the listener at startup.
So you won’t need to manually run ALTER SYSTEM REGISTER every time.

5. Save your connection info in SQL Developer

Save connection settings and passwords securely in SQL Developer.
Then just click Connect — no reconfiguration needed.

Summary: What you do once, stays permanent

What you do

Stays fixed?

Set SYS/SYSTEM password

Yes

Create regular user for work

Yes

Set Windows services to auto-start

Yes

Configure LOCAL_LISTENER param

Yes

Save connections in SQL Developer

Yes

Create a regular user with proper permissions

do this as well as Verify your Oracle parameters for permanent listener registration

Absolutely! Let’s do it step-by-step:

1. Create a regular user for everyday use

Open SQL*Plus as SYSDBA:

sqlplus / as sysdba

Then run these commands to create a user and grant basic permissions:

CREATE USER myuser IDENTIFIED BY mypassword;

GRANT CONNECT, RESOURCE TO myuser;

-- Optionally grant DBA role if you want full privileges (use with caution!)

-- GRANT DBA TO myuser;

EXIT;

Replace myuser and mypassword with your desired username and strong password.

2. Verify Oracle parameters for permanent listener registration

Stay connected as SYSDBA, and check the current LOCAL_LISTENER parameter:

SHOW PARAMETER LOCAL_LISTENER;

If it’s empty or incorrect, set it permanently by:

ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))' SCOPE=SPFILE;

Important: Since we used SCOPE=SPFILE, you need to restart the database for this to take effect.

To restart the database:

SHUTDOWN IMMEDIATE;

STARTUP;

3. Verify listener registration

After restart, run:

lsnrctl status

You should see Service "XE" proy registered.

4. Set Oracle Windows Services to Automatic Start

Open services.msc
Find these services:
- OracleServiceXE
- OracleXETNSListener
Right-click > Properties > Set Startup type to Automatic

This ensures listener and database start automatically on system reboot.

5. Use your new user in SQL Developer

In SQL Developer, create a new connection using:

Field

Value

Username

myuser

Password

mypassword

Hostname

localhost

Port

1521

Service Name

Done! You now have:

A permanent regular user with appropriate privileges
Listener registration that persists across restarts
Services auto-starting on Windows boot
Connection ready for SQL Developer use without resets

gave SQL> CREATE USER user IDENTIFIED BY user1234; CREATE USER user IDENTIFIED BY user1234 * ERROR at line 1: ORA-01935: missing user or role name

Ah, yes — that’s a common Oracle error. Here's exactly what's wrong:

❌ You used the word **user** as a username, which is a reserved keyword in Oracle.

✅ Solution: Use a different, non-reserved username

Try something like:

CREATE USER myuser IDENTIFIED BY user1234;

or:

CREATE USER demo_user IDENTIFIED BY user1234;

Then grant the permissions:

GRANT CONNECT, RESOURCE TO myuser;

-- or for demo_user if you used that name

-- GRANT CONNECT, RESOURCE TO demo_user;

🔍 Why it failed:

USER is a reserved keyword in Oracle (it refers to the current session’s user).
Oracle doesn’t allow object names (like users, tables, etc.) to match reserved keywords unless quoted, which is bad practice for user creation.

Great! Here's a clean and safe set of commands you can use to create a permanent user and grant the needed permissions for regular use in SQL Developer:

✅ 1. Create the User (Replace with your desired name and password)

CREATE USER myuser IDENTIFIED BY mypassword;

🔁 Replace:

myuser → your preferred username (e.g. project_user, student1, etc.)
mypassword → a strong password (e.g. User@1234, DemoPass2025)

✅ 2. Grant Basic Permissions

GRANT CONNECT, RESOURCE TO myuser;

This gives your user:

Ability to log in (CONNECT)
Ability to create tables, views, triggers, etc. (RESOURCE)

🛑 Don't grant DBA unless it's absolutely necessary (it's full admin power).

✅ 3. Now Use This User in SQL Developer

Field

Value

Username

myuser

Password

mypassword

Hostname

localhost

Port

1521

Service Name

Role

Default (no SYSDBA)

gave SQL> CREATE USER myuser IDENTIFIED BY user1234; CREATE USER myuser IDENTIFIED BY user1234 * ERROR at line 1: ORA-65096: invalid common user or role name

Thanks — this error:

ORA-65096: invalid common user or role name

is very common when using Oracle 12c, 18c, 19c, 21c, and especially multitenant databases (which includes Oracle XE 21c).

🔍 What it means:

You're likely connected to the CDB (Container Database) instead of the PDB (Pluggable Database).
Oracle doesn’t allow you to create a regular user in the CDB root unless it’s a common user (starts with C##).

But you don’t want a common user — you want a regular one inside the pluggable database, usually called XEPDB1.

✅ Solution: Connect to the PDB first, then create the user

✅ Step 1: Connect as SYSDBA

sqlplus / as sysdba

✅ Step 2: Switch to the PDB (XEPDB1)

ALTER SESSION SET CONTAINER = XEPDB1;

✅ Step 3: Create the user and grant privileges

CREATE USER myuser IDENTIFIED BY user1234;

GRANT CONNECT, RESOURCE TO myuser;

✅ Step 4: Done — now connect in SQL Developer

Use:

Field

Value

Username

myuser

Password

user1234

Hostname

localhost

Port

1521

Service Name

XEPDB1

Role

Default (leave blank)

✅ Bonus: Check Available Pluggable Databases

To see available PDBs (like XEPDB1), from SYSDBA run:

SHOW PDBS;

Expected output:

CON\_ID CON\_NAME   OPEN MODE  RESTRICTED

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

     2 PDB$SEED   READ ONLY  NO

     3 XEPDB1     READ WRITE NO

so now this one is permanent and will not change everytime i shut down and start the windows ??

✅ Yes — your new user (myuser with password user1234) is now permanent.
Once created inside the Pluggable Database (PDB) like XEPDB1, it does not get deleted or reset when you shut down or restart your computer.

But there are a few important things to make this setup reliable and startup-ready every time:

✅ What is permanent and stable now:

✅ Thing

Stays after reboot?

myuser account

✅ Yes

Password (user1234)

✅ Yes

Privileges (CONNECT, RESOURCE)

✅ Yes

Listener and service registration (after earlier fix)

✅ Yes

SQL Developer saved connection

✅ Yes

⚠️ What you still want to ensure:

To avoid manual steps after reboot, make sure these two things auto-start:

🔧 1. Oracle Services Start Automatically (Listener + DB)

Open services.msc, and check these two services:

OracleServiceXE
OracleXETNSListener

✅ Set both to Startup Type: Automatic

This ensures the listener and database start up automatically with Windows.

🔧 2. PDB (XEPDB1) Opens Automatically on Startup

Even if the CDB (container) starts, the PDB (XEPDB1) may remain closed unless you configure it to open automatically.

Do this once:

Connect as SYSDBA:

sqlplus / as sysdba

Then run:

ALTER PLUGGABLE DATABASE XEPDB1 SAVE STATE;

✅ This tells Oracle: "Whenever I start the database, also open XEPDB1 automatically."

✅ Final Summary

You’ve now configured:

Configuration

Done?

Created normal user in PDB

✅

Registered service with listener

✅

Enabled auto-start for Oracle services

✅ (check services.msc)

Enabled PDB to auto-open

🔄 (do with ALTER PLUGGABLE DATABASE XEPDB1 SAVE STATE;)

Once you do that final step, you will no longer have to run any SQL or config manually after reboot.

### 1\. Oracle Services Start Automatically (Listener + DB) Open **`services.msc`**, and check these two services: * `OracleServiceXE` * `OracleXETNSListener` ✅ Set both to **Startup Type: Automatic** > This ensures the **listener** and **database** start up automatically with Windows.

how to do it ??

Great — here’s exactly how to do it. This process ensures that your Oracle database and listener start automatically every time your Windows system boots, so you don’t need to manually run STARTUP, lsnrctl start, or open SQL*Plus each time.

✅ How to Set Oracle Services to Start Automatically on Windows

🪟 Step-by-step:

Press Windows + R to open the Run dialog box.
Type:
The Services window will open. Now scroll down and look for these services:
- OracleServiceXE
- OracleXETNSListener
For each one:
- Right-click > choose Properties
- In the "Startup type" dropdown, select:
- Click Apply, then OK
Once both are set to Automatic, close the Services window.

services.msc

and hit Enter.

Automatic

🔄 Optional: Start them manually now (if not already running)

Still in the Services window:

If Status is blank (not running), right-click on each and click:

Start

This starts the Oracle XE instance and the listener now — and they'll also auto-start from next reboot onward.

✅ You're done!

From now on:

Your Oracle database (XE) starts with Windows.
The listener is also ready, so you can connect from SQL Developer without extra steps.
Your PDB (XEPDB1) opens automatically too (after running the SAVE STATE command earlier).

Oracle Database Installation Errors A to Z Guide..pdf

Oracle Database Installation Errors A to Z Guide..docx

Added on Aug 11 2025

#database-installation, #dba, #general-database-discussions, #listener, #sqlplus, #xe

0 comments

144 views