Ошибка oracle not available

I hit the same shared memory realm does not exist symptom (on Windows) but for a different reason. I had just installed Oracle (XE) and after some troubleshooting, established that my installation was corrupt due to the presence of an ORACLE_HOME environment property at the time I installed it.

If this is TLDR, skip to ‘So to resolve:’!

My initial symptom was:

Message 850 not found; No message file for product=NETWORK, facility=NL

Apparently the Windows install reads the ORACLE_HOME from the registry and doesn’t need (and certainly in my case shouldn’t have…) an environment property.

Remove it, as follows:

1. Edit the system environment settings (Windows key and start
   typing ‘env’ and you should see this option come up.
2. Delete any User and System Environment Variables called ORACLE_HOME, if
   present. (make a note of their values, mainly out of interest, but may be of
   use if you want to put them back for some reason!)
3. Restart your machine. Don’t muck around with just a log off — restart your
   machine. The Windows Oracle install uses Windows services by default
   and your installation is currently very bad — it needs a restart.

Following the restart I was then able to get error messages other than ‘No message file…’ and could start looking at what the issue was.
Setting the ORACLE_SID to XE and connecting @XE I got as far as the errors in this page, namely the following symptoms:

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

Another symptom was:
When launching the ‘Get started’ page it failed to connect, giving a not found error (if I recall correctly), despite the Windows listener & XE services being started. As noted in another answer, this could be due to the windows services not being started. In my case those services were started, so something else was misconfigured.

At this point, I figured maybe my install had just gone so badly wrong due to the presence of my bad ORACLE_HOME environment property that I should reinstall. (Previous reinstalls hadn’t helped, but those had all been before I noticed the ORACLE_HOME system environment property (probably set up by me a year ago!).

So to resolve:

1. Close any app looking at the Oraclexe install directory (editors/explorer/cmd prompts)
2. A quick trip to Add/Remove programs and uninstall OracleXe
3. Double-check you have no ORACLE_HOME environment property set anywhere, remember — Windows will use registry entries to get it.
4. Restart (take no chances — we’re in this for the long term!)
5. Did you make sure there was no ORACLE_HOME property?
6. Run the Oracle installer again (as local admin account if applicable)
7. You should be able to rejoice in a working install. I did, at least!

I hit the same shared memory realm does not exist symptom (on Windows) but for a different reason. I had just installed Oracle (XE) and after some troubleshooting, established that my installation was corrupt due to the presence of an ORACLE_HOME environment property at the time I installed it.

If this is TLDR, skip to ‘So to resolve:’!

My initial symptom was:

Message 850 not found; No message file for product=NETWORK, facility=NL

Apparently the Windows install reads the ORACLE_HOME from the registry and doesn’t need (and certainly in my case shouldn’t have…) an environment property.

Remove it, as follows:

1. Edit the system environment settings (Windows key and start
   typing ‘env’ and you should see this option come up.
2. Delete any User and System Environment Variables called ORACLE_HOME, if
   present. (make a note of their values, mainly out of interest, but may be of
   use if you want to put them back for some reason!)
3. Restart your machine. Don’t muck around with just a log off — restart your
   machine. The Windows Oracle install uses Windows services by default
   and your installation is currently very bad — it needs a restart.

Following the restart I was then able to get error messages other than ‘No message file…’ and could start looking at what the issue was.
Setting the ORACLE_SID to XE and connecting @XE I got as far as the errors in this page, namely the following symptoms:

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

Another symptom was:
When launching the ‘Get started’ page it failed to connect, giving a not found error (if I recall correctly), despite the Windows listener & XE services being started. As noted in another answer, this could be due to the windows services not being started. In my case those services were started, so something else was misconfigured.

At this point, I figured maybe my install had just gone so badly wrong due to the presence of my bad ORACLE_HOME environment property that I should reinstall. (Previous reinstalls hadn’t helped, but those had all been before I noticed the ORACLE_HOME system environment property (probably set up by me a year ago!).

So to resolve:

1. Close any app looking at the Oraclexe install directory (editors/explorer/cmd prompts)
2. A quick trip to Add/Remove programs and uninstall OracleXe
3. Double-check you have no ORACLE_HOME environment property set anywhere, remember — Windows will use registry entries to get it.
4. Restart (take no chances — we’re in this for the long term!)
5. Did you make sure there was no ORACLE_HOME property?
6. Run the Oracle installer again (as local admin account if applicable)
7. You should be able to rejoice in a working install. I did, at least!

Oracle databases are widely used in the industry; sometimes, you may encounter errors when working with them. One such error is the ORA-01034 Oracle not available error, which indicates that the Oracle database instance is not available. This error can be frustrating, especially when working on a critical project. In this article, we will discuss what this error is, what causes this error, and how to resolve it.

What is ORA-01034 Error?

ORA-01034 is an error code indicating that the Oracle database instance is unavailable. This error usually occurs when the Oracle database service is not running or is not accessible. When you try to connect to the database, you will get the following error message:

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

This error can occur on any operating system, including Windows, Linux, and Unix.

Causes of ORA-01034 Error

There can be several causes of the ORA-01034 error. Some of the most common causes are:

1. Oracle Service Not Running

If the Oracle service is not running, you cannot connect to the database instance. The Oracle service can stop due to several reasons, including hardware failure, power outage, or software issues.

2. Incorrect Oracle SID

The Oracle SID is a unique identifier for the database instance. If the Oracle SID is incorrect, you cannot connect to the database instance. This error can occur when you try to connect to a different database instance or when the Oracle SID is misspelled.

3. Invalid Login Credentials

If you use incorrect login credentials, you cannot connect to the database instance. This error can occur when you forget your password or when your account is locked.

4. Database Corruption

Database corruption can also cause the ORA-01034 error. If the database files are corrupted, you will not be able to connect to the database instance. This error can occur due to hardware failure, software issues, or power outages.

How to Resolve ORA-01034 Error

Now that we know the causes of the ORA-01034 error, let’s list the possible ways to resolve it. Here are some steps that you can follow:

• Check Oracle Service
• Verify Oracle SID
• Verify Login Credentials
• Check Database Corruption

Check the Oracle Service

The first step is to check if the Oracle service is running. You can do this by opening the Services app in Windows and checking if the Oracle service is running. If the service is not running, start it and try to connect to the database again.

Verify the Oracle SID

Make sure that you are using the correct Oracle SID. You can check the Oracle SID by running the following command in the command prompt:

If the Oracle SID is incorrect, set it to the correct value by running the following command:

Replace “sid” with the correct Oracle SID.

Verify the Login Credentials

Make sure that you are using the correct login credentials. If you forget your password, you can reset it by running the following command:

alter user username identified by new_password;

Replace “username” with your username and “new_password” with your new password.

Check for Database Corruption

If none of the above steps work, check for database corruption. You can do this by running the following command in the command prompt:

If you get the following error message, the database files may be corrupted:

ORA-01157: cannot identify/lock

If you get the above error message, the database files may be corrupted. In this case, you need to restore the database from a backup or perform recovery operations.

To perform recovery operations, you can follow these steps:

1. Open the command prompt and connect to the database using the following command:

1. Check the status of the database using the following command:

1. Recover the database using the following command:

1. Open the database using the following command:

Once the database is open, try to connect to it and check if the ORA-01034 error is resolved.

Conclusion

The ORA-01034 error is a common error that occurs when the Oracle database instance is not available. This error can occur due to various reasons, including the Oracle service not running, incorrect Oracle SID, invalid login credentials, and database corruption. To resolve this error, you can follow the steps mentioned above, which include checking the Oracle service, verifying the Oracle SID and login credentials, and checking for database corruption. By following these steps, you can quickly resolve the ORA-01034 error and continue working on your projects.

FAQs

What is the Oracle SID?

Oracle SID is a unique identifier for the database instance.

Why am I getting the ORA-01034 error?

You may be getting the ORA-01034 error due to various reasons, including the Oracle service not running, incorrect Oracle SID, invalid login credentials, and database corruption.

How can I check if the Oracle service is running?

You can check if the Oracle service is running by opening the Services app in Windows and checking if the Oracle service is running.

Can I reset my password if I forget it?

Yes, you can reset your password by running the following command: alter user username identified by new_password;

What should I do if the database files are corrupted?

If the database files are corrupted, you must restore the database from a backup or perform recovery operations.

ORA-01034 means that the command you want to perform cannot be done in a stop, idle or shutdown database. If you’re surprised by the error, maybe you should check the database status immediately. It maybe someone or something turn it off without notification.

Connect to Local Database

Let’s see a case. Tried to connect to the local database.

[oracle@test ~]$ sqlplus system
...
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Additional information: 4376
Additional information: -1649756641
Process ID: 0
Session ID: 0 Serial number: 0

Because the database is stop there’s no way to do authentication.

Let’s see another case.

[oracle@test ~]$ sqlplus / as sysdba
...
SQL> alter database begin backup;
alter database begin backup
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

Although we can connect to the database by OS authentication, we cannot perform any database operation.

The solution is to startup the database.

Data Guard

If you saw ORA-01034 in the alert log, don’t panic, it’s not generated from the database that you are currently running. It’s usually from the standby Database.

...
Tue Oct 13 14:35:08 2008
Errors in file /oracle/admin/ORCL1/bdump/orcl1_arc1_3295892.trc:
ORA-01034: ORACLE not available

In such case, the primary database tried to transport redo log to the standby database, but the standby database is stop for some reason. You need to check what’s going on.