search

oracle / vagrant-projects

Vagrant projects for Oracle products and other examples
Universal Permissive License v1.0
930 stars 473 forks source link

database instance is idle #501

Open AnasKiron opened 6 months ago

AnasKiron commented 6 months ago

Hi,

Summary: I couldn't connect to the oracle database engine without manual intervention (starting listener & database manually).

Details: First, I started the VM using vagrant up Then, I tried to connect to the database using SQL Developer from host OS; I got vendor error code 17002.

I used vagrant ssh to connect to the VM, and run the following command on the terminal: lsnrctl status which gives me the following:

LSNRCTL for Linux: Version 23.0.0.0.0 - Production on 30-DEC-2023 03:49:18

Copyright (c) 1991, 2023, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
TNS-12541: Cannot connect. No listener at host localhost port 1521.
 TNS-12560: Database communication protocol error.
  TNS-00511: No listener
   Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: Cannot connect. No listener at key EXTPROC1521.
 TNS-12560: Database communication protocol error.
  TNS-00511: No listener
   Linux Error: 111: Connection refused

I started the listener service using lsnrctl start and got the following:

Starting /opt/oracle/product/23c/dbhomeFree/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 23.0.0.0.0 - Production
System parameter file is /opt/oracle/product/23c/dbhomeFree/network/admin/listener.ora
Log messages written to /opt/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 23.0.0.0.0 - Production
Start Date                30-DEC-2023 03:50:00
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           FREE
Listener Parameter File   /opt/oracle/product/23c/dbhomeFree/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

Then, I tried again to connect to database engine; I got the following error messsage:

An error was encountered performing the requested operation:

Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

I googled it, and found a solution on stack overflow:

https://stackoverflow.com/questions/6555827/ora-01034-oracle-not-available-ora-27101-shared-memory-realm-does-not-exist

I executed the following on the terminal: sqlplus "/ as sysdba" it gives me the following message:

Connected to an idle instance.

Finally, I run startup which started the instance and at this moment I had the chance to connect to the database engine.

Your advice is highly appreciated. Thanks,

Environment

PaulNeumann commented 6 months ago

@AnasKiron I can't reproduce this behavior on Windows 11 23H2 (OS Build 22631.2861) with VirtualBox 7.0.12 and Vagrant 2.4.0. When I provision the VM, the listener and instance start as expected. I'm using slightly newer versions of VirtualBox and Vagrant, but I doubt that's the issue.

I suggest verifying that Hyper-V is disabled on your host, then starting over to see whether the problem happens again.

To verify that Hyper-V is disabled, open an Administrator Command Prompt or an Administrator PowerShell window, and enter the command bcdedit. You may see a line in the Windows Boot Loader section of the output that starts with "hypervisorlaunchtype". If you don't see this line, Hyper-V is disabled, and you can skip ahead. If you see this line, it should read "hypervisorlaunchtype Off", meaning Hyper-V is disabled. If it reads "hypervisorlaunchType Auto", Hyper-V is enabled. Disable it by running the command bcdedit /set hypervisorlaunchtype Off, then reboot the host.

To retry the provisioning, open a PowerShell window and change to the vagrant-projects\OracleDatabase\23.3.0-Free directory. Then destroy the VM (vagrant destroy -f). Finally, run the command vagrant up | Tee-Object provision.log. This will save the output from vagrant up to a file called "provision.log". If the problem happens again, please attach the provision.log file or upload it to a gist, so we can troubleshoot further.

AnasKiron commented 6 months ago

Hi Mr. @PaulNeumann,

I disabled the Hyper-V and restarted my machine but the issue still existed.

So, I installed VirtualBox 7.0.12, and Vagrant 2.4.0.

My Host OS is Windows 11 23H2 (Build is 22631.2861)

I destroyed the previous VM, then I provisioned a new one.

Now, it works.

I had a question, please: Is it possible to provision a VM without internet connection?

Regards,

PaulNeumann commented 6 months ago

@AnasKiron I'm surprised that the versions of VirtualBox and Vagrant made a difference, but I'm glad it's working for you now.

Is it possible to provision a VM without internet connection?

Not without a lot of manual downloads and script changes, because the OracleDatabase/23.3.0-Free project requires an internet connection to download both updates from the Oracle Yum repositories and the database installer. The same is true for the other projects in this repository. However, you could provision the VM on an internet-connected computer, then package it as a new Vagrant box, and use that box with Vagrant on a non-internet-connected computer. See the documentation for vagrant package.

infiniteshi commented 4 months ago

Hi @AnasKiron, My understanding is that you were stuck at this step, and you're very close:

SQL> connect / as sysdba
Connected to an idle instance.
SQL>

What you can do next is simply the command SQL> startup

If it works, you'll see the following prompt:

<instance_name> instance started.
Total System Global Area ... bytes
...
Database mounted.
Database opened.