Closed infiniteshi closed 1 year ago
@infiniteshi I don't understand exactly what you're asking. So, a few comments, and a couple of questions:
Comments
/opt/oracle
, including listener.ora
and tnsnames.ora
without using sudo
. Everything under /opt/oracle
is owned by oracle:oinstall./etc/ssh/sshd_config
, you need to run sudo
as vagrant, not as oracle.Questions
vagrant ssh
, then run sudo su - oracle
in the ssh session? Does the command succeed? Do you see "oracle@oracle-21c-vagrant" in the prompt?/home/oracle/setPassword.sh <new password>
as the oracle user? Do the database passwords for SYS, SYSTEM and PDBADMIN get changed?Hi Paul, thanks for the reply. Sorry my initial description wasn't that clear.
To answer your first question, when I ran vagrant up, the prompt got frozen at the line "SSH auth method: private key" (then time-out, didn't made it to the next step vagrant ssh)
... ==> oracle-21c-vagrant-copy: Running 'pre-boot' VM customizations... ==> oracle-21c-vagrant-copy: Booting VM... ==> oracle-21c-vagrant-copy: Waiting for machine to boot. This may take a few minutes... oracle-21c-vagrant-copy: SSH address: 127.0.0.1:2222 oracle-21c-vagrant-copy: SSH username: vagrant oracle-21c-vagrant-copy: SSH auth method: private key
Meanwhile, when I checked the VirtualBox GUI Manager, I saw the following screen:
It's hard to tell if it was expecting the user "vagrant" - password "vagrant" at the vagrant@oracle-21c-vagrant-copy level, or it was expecting the user "oracle" - password "oracle"? at the oracle@oracle-21c-vagrant-copy level. Of course, I tend to believe the former was the case (after all, I hadn't issued the command "sudo su - oracle" at this point yet).
Anyway, I attempted to input username-password pair "vagrant"-"vagrant", "oracle"-"oracle", and then out of desperation, the original hash-look auto-generated password when my vm was provisioned for the first time, and at last my own custom DBA's credentials. The last two really don't make sense, but really out of desperation - all these four username-password pairs are all that I have, just had to give each one a shot.
Besides, I also tried the literal "password way": There was a time when I added to the Vagrantfile
config.ssh.username = 'vagrant' config.ssh.password = 'vagrant'
The the prompt changed the verbiage to "SSH auth method: password" but the screen was exactly the same as above: waiting for me to type in "the correct credential". I also tweaked my mac's /etc/ssh/sshd_config file: PasswordAuthentication yes, PermitRootLogin yes, AllowAgentForwarding yes, AllowTcpForwarding yes, etc. As a matter of fact, none of them made any difference even when I flipped them back and forth. (I also tried another dozens of different things, whose details I'm omitting for now.)
I did succeed once but it was on another vm, named "oracle-21c-vagrant" (yes, exactly as in your project). I passed the freezing SSH auth method phase, could bring the vm up and running via vagrant up (despite red error or warning message but not road-blocking), curiously also passed with vagrant ssh, could do sudo su - oracle, and finally I saw the oracle@oracle-21c-vagrant prompt. I excitedly typed in "sqlplus / as sysdba", what I was expecting was this (old screenshot from June 2022 when things were working perfectly):
but what I actually received was this:
[sudo] password for oracle:
//invisible flashing cursor waiting for my input
Ok, I tried all the four credentials I knew of, and none of them worked.
Since at least I was inside vm oracle-21c-vagrant this time, my last bullet point of the initial post came in:
"I could successfully change the password for user "vagrant" on the vagrant@oracle-21c-vagrant level, and attempted to change the password for oracle DB using sudo passwd oracle, but again, I was prompted the "[sudo] password for oracle" to change the password - a catch-22. Similarly, the attempt to change the sshd_config file was also to avail because of catch-22."
With the very last sentence, I mean, I wanted to change the sshd_config file of the vagrant-user-presented virtual machine (not my mac's sshd_config). The one property I mostly liked to change would be "PermitRootLogin", hoping on the oracle@oracle-21c-vagrant level, the vm would accept that I came from vagrant ssh of "vagrant", thus stop asking me for credentials. I also wanted to be able to modify listener.ora or tnsnames.ora, but they kept denying access, believing I wasn't authorized. I remember being prompted to provide credentials all over the place.
I've never run /home/oracle/setPassword.sh
conn system or connect system Type in the default password you found in the console printout.
Then run CREATE USER
For example, CREATE USER abc IDENTIFIED BY abc;
If you ran into Error "ORA-65096: invalid common user or role name" at this point,
you can choose to run alter session set "_ORACLE_SCRIPT"=TRUE;
and then again CREATE USER
Next, you need to grant a role or the privilege associated with that role to this new user:
GRANT DBA TO
By the way, as an important claim, I don't think what I experienced recently is an issue with this project/repo per se - most likely something changed on my machine without my awareness and I most likely have a corrupted vm (or vms) at hand. It had been working great - one can literally build a powerful vm with DB within minutes. I wish GitHub has a button called "Ask Questions" so I didn't have to use/abuse the "Issues" button for help.
I suspect your issue got to do with your Vagrant setup and SSH, and nothing to do with the Oracle project.
On your macOS, navigate to the directory where you first ran vagrant up
initially, for example vagrant-projects/OracleDatabase/21.3.0
, and try the following:
1) List your SSH identifies loaded by the SSH Agent:
ssh-add -l
2) Delete all your identities loaded by the SSH Agent:
ssh-add -D
3) SSH to your Vagrant box
vagrant ssh
If that didn't work, try to dig further into your Vagrant SSH Configuration for the Oracle box.
1) List your Vagrant SSH Configuration for the Oracle box:
vagrant ssh-config
the output should look something like:
Host default
HostName 127.0.0.1
User vagrant
Port 2222
UserKnownHostsFile /dev/null
StrictHostKeyChecking no
PasswordAuthentication no
IdentityFile /Users/USER/vagrant-projects/OracleDatabase/21.3.0/.vagrant/machines/default/virtualbox/private_key
IdentitiesOnly yes
LogLevel FATAL
2) Try (make sure you change -i
value below to the value of your IdentityFile
above, and the Port
if not 2222
):
ssh vagrant@127.0.0.1 \
-p 2222 \
-o UserKnownHostsFile=/dev/null \
-o StrictHostKeyChecking=no \
-i /Users/USER/vagrant-projects/OracleDatabase/21.3.0/.vagrant/machines/default/virtualbox/private_key
@infiniteshi Please work through the steps that @hussam-qasem recommended. Unless you can log into the VM, there's no way to troubleshoot other issues.
A few notes on the information you posted:
Meanwhile, when I checked the VirtualBox GUI Manager, I saw the following screen:
This is normal. It doesn't indicate a problem. This is the VM console, ready for an interactive login. The passwords for the root, vagrant and oracle OS users are locked by default, so this screen can't be used to log in to the VM. The screen looks exactly the same when an ssh session is connected.
I excitedly typed in "sqlplus / as sysdba", what I was expecting was this (old screenshot from June 2022 when things were working perfectly):
what I actually received was this:
[sudo] password for oracle:
This probably means that you accidentally typed sudo sqlplus
, rather than sqlplus
. The oracle OS user doesn't need to use sudo
to run the Oracle Database utilities or to maintain the files and directories under /opt/oracle
. (And, since the oracle OS user isn't a member of the wheel group and doesn't have a password, it wouldn't work anyway.)
If you ran into Error "ORA-65096: invalid common user or role name" at this point, you can choose to run alter session set "_ORACLE_SCRIPT"=TRUE;
The distinction between common and local users is important, and this isn't the best approach. I suggest reviewing the documentation for CREATE USER in the SQL Language Reference, especially the "Creating a Common User in a CDB" section at the end. The Introduction to Multitenant Administration in the Administrator's Guide is also an excellent resource.
@hussam-quasem Thanks a lot for the step-by-step instructions. Even though following them didn't eliminate a couple specific issues, they were in general very helpful! @PaulNeumann Thanks a lot for the comments. I gained a lot of clarity and insight that I hadn't had before and can now better assess my issues.
-> (5) then ran
The green-underscored line was particularly interesting and I'm curious why I don't see the new fingerprint actually added to my mac's ~/.ssh/known_hosts file. This was verified by running "ssh-add -l" again, which yielded "The agent has no identities". As a matter of fact, I've never seen any vagrant/virtualbox/oracle related fingerprints in the ~/.ssh/known_hosts file since I started using this project June 2022. Did I check in the wrong place? Or this is normal?
-> (6) Since I succeeded in logging as "vagrant" from the last step, I ran sudo su - oracle, no problem, navigated to the Oracle DB Home /opt/oracle/product/21c/dbhome_1, then navigated to /network/admin, then I did the following
As you can see, first, I must do "sudo vim tnsnames.ora" instead of "vim tnsnames.ora". Secondly, the [sudo] password for oracle prompt came out. Just for fun fact, when I typed in any credentials here that are doomed to fail, the prompt would print "Sorry, try again." It even counted how many times I failed and there's a limit of attempts: 3. This point (6) holds true even after I fixed the ORA-12505 error as mentioned in (3). Or in other words, I can connect to the DB via Aqua Data Studio now, but I still can't freely access listener.ora or tnsnames.ora as needed. It's still the case as of speaking.
(2) For comparison to what 1.(6) described
I do have access and can edit tnsnames.ora under directory
Can I draw the conclusion that oracle@oracle-21c-vagrant is an "alias" for /opt/oracle/product/21c/dbhome_1, a.k.a the Oracle DB Home? And that /opt/oracle/product/21c/dbhome_1/ !=
(3) Why the question right above is important to me, or why do I care where the Oracle DB's bounds are: Everything I mentioned in 1. resides in vm "oracle-21c-vagrant", or No.1 in the following screenshot. But it's not my primary DB. My primary DB is actually "oracle-21c-vagrant-copy", or No.2.
(No.3 and No.4 are guinea pigs, don't mind them.) No. 1 is missing 6 months' data compared to No. 2. No.2 is still unusable due to similar ssh issues (I can't login) that I must keep digging for. If I can't fix No.2, is there a way to migrate No.2's data to No.1? Is there a specific directory I can copy and paste/implant? (As long as No.2 is unaccessible, I can't do normal db migration like dump import/export. If No. 2 opens up to me, luckily both are in the same VirtaulBox instance, and both are of exactly the name brand and make, but I still need to learn how to do the dump import/export. Any suggestions or reading-lists?) And for the future, I wish I knew if there's a way to back up the Oracle DB in this project's setup?
@infiniteshi I'm glad to see that you can now use ssh to connect to your VM.
The green-underscored line was particularly interesting
This happened because you used ssh
, rather than vagrant ssh
. By default, the ssh
command writes new hosts to the known_hosts
file. However, when Vagrant runs ssh
, it uses command line switches to prevent this behavior.
As you can see, first, I must do "sudo vim tnsnames.ora" instead of "vim tnsnames.ora".
No. The message said "command not found", not "permission denied", so it's not a privilege issue. Either vim isn't installed, or it isn't installed correctly. As the vagrant user (NOT as the oracle user), run sudo dnf -y install vim
to install it. If you get a message saying that vim is already installed, then run sudo dnf -y remove vim
, followed by sudo dnf -y install vim
. Afterward, the oracle user should be able to run it.
I don't mean the following to sound harsh, but it's very, very important: THE oracle OS USER CANNOT RUN sudo
. Never, for any reason. This is by design, and it's a security feature. User accounts should have the least privileges necessary to do their jobs. If you encounter a situation that you think requires the oracle user to run sudo
, you haven't analyzed the problem completely.
I do have access and can edit tnsnames.ora under directory
/vagrant-projects/OracleDatabase/21.3.0/LINUX.X64_213000_db_home/network/admin
This is a path on your host, not on the VM. Specifically, this is the path to the network/admin
directory inside the database installer zip file. Changing files there won't affect your VM, but it will corrupt the installer. If you've modified any files in the database installer zip file, I strongly recommend deleting it and downloading it again. The directories within the installer zip file have the same structure as the directories installed within your VM because the installer zip file is unzipped under ORACLE_HOME during VM provisioning.
Can I draw the conclusion that oracle@oracle-21c-vagrant is an "alias" for /opt/oracle/product/21c/dbhome_1, a.k.a the Oracle DB Home?
"oracle@oracle-21c-vagrant" is simply the bash prompt. It's telling you the current user and hostname. It's not an alias for anything. The ORACLE_HOME environment variable is separate. By default, ORACLE_HOME is set to /opt/oracle/product/21c/dbhome_1
. If you ever want to see what directory you're in, use the command pwd
. If you run sudo su - oracle
and then pwd
, you'll see that you're in the directory /home/oracle
. The tnsnames.ora
file for the VM is in the directory /opt/oracle/homes/OraDB21Home1/network/admin
.
If I can't fix No.2, is there a way to migrate No.2's data to No.1?
Maybe. If you can still connect to No. 2 with database tools, then you might be able to use them to export the data. And if you have the Oracle database client or Instant Client installed on your host, then exp
might work, as long as you don't have any virtual columns in your tables.
Any suggestions or reading-lists?) And for the future, I wish I knew if there's a way to back up the Oracle DB in this project's setup?
I suggest working through Oracle's 2 Day DBA. The document is for Oracle Database 19c, but it's still applicable, and it covers backup and recovery.
@PaulNeumann Thank you very much for your reply. All your points were tremendously helpful.
It was totally right that after running sudo dnf -y install vim
to install vim as the vagrant user successfully, I could run vim tnsnames.ora
as the oracle user later on. See the terminal printouts below for proof:
[vagrant@oracle-21c-vagrant ~]$ sudo dnf -y install vim
Last metadata expiration check: 2:08:26 ago on Wed 15 Mar 2023 11:50:26 AM -05.
Dependencies resolved.
================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
vim-enhanced x86_64 2:8.0.1763-19.0.1.el8_6.4 ol8_appstream 1.4 M
Installing dependencies:
gpm-libs x86_64 1.20.7-17.el8 ol8_appstream 39 k
vim-common x86_64 2:8.0.1763-19.0.1.el8_6.4 ol8_appstream 6.3 M
vim-filesystem noarch 2:8.0.1763-19.0.1.el8_6.4 ol8_appstream 51 k
Transaction Summary
================================================================================
Install 4 Packages
Total download size: 7.8 M
Installed size: 30 M
Downloading Packages:
(1/4): gpm-libs-1.20.7-17.el8.x86_64.rpm 41 kB/s | 39 kB 00:00
(2/4): vim-filesystem-8.0.1763-19.0.1.el8_6.4.n 98 kB/s | 51 kB 00:00
(3/4): vim-enhanced-8.0.1763-19.0.1.el8_6.4.x86 264 kB/s | 1.4 MB 00:05
(4/4): vim-common-8.0.1763-19.0.1.el8_6.4.x86_6 933 kB/s | 6.3 MB 00:06
--------------------------------------------------------------------------------
Total 1.1 MB/s | 7.8 MB 00:06
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : vim-filesystem-2:8.0.1763-19.0.1.el8_6.4.noarch 1/4
Installing : vim-common-2:8.0.1763-19.0.1.el8_6.4.x86_64 2/4
Installing : gpm-libs-1.20.7-17.el8.x86_64 3/4
Running scriptlet: gpm-libs-1.20.7-17.el8.x86_64 3/4
Installing : vim-enhanced-2:8.0.1763-19.0.1.el8_6.4.x86_64 4/4
Running scriptlet: vim-enhanced-2:8.0.1763-19.0.1.el8_6.4.x86_64 4/4
Running scriptlet: vim-common-2:8.0.1763-19.0.1.el8_6.4.x86_64 4/4
Verifying : gpm-libs-1.20.7-17.el8.x86_64 1/4
Verifying : vim-common-2:8.0.1763-19.0.1.el8_6.4.x86_64 2/4
Verifying : vim-enhanced-2:8.0.1763-19.0.1.el8_6.4.x86_64 3/4
Verifying : vim-filesystem-2:8.0.1763-19.0.1.el8_6.4.noarch 4/4
Installed:
gpm-libs-1.20.7-17.el8.x86_64
vim-common-2:8.0.1763-19.0.1.el8_6.4.x86_64
vim-enhanced-2:8.0.1763-19.0.1.el8_6.4.x86_64
vim-filesystem-2:8.0.1763-19.0.1.el8_6.4.noarch
Complete!
Then
[vagrant@oracle-21c-vagrant ~]$ sudo su - oracle
[oracle@oracle-21c-vagrant ~]$ ls
setPassword.sh
[oracle@oracle-21c-vagrant ~]$ cd /opt/oracle/product/21c/dbhome_1
[oracle@oracle-21c-vagrant dbhome_1]$ ls
addnode demo jlib oracore R sqlj
assistants diagnostics ldap oraInst.loc racg sqlpatch
bin dv lib ord rdbms sqlplus
cfgtoollogs env.ora md ords relnotes srvm
clone has mgw oss root.sh suptools
crs hs network oui root.sh.old ucp
css install nls owm root.sh.old.1 usm
ctx instantclient odbc perl runInstaller utl
cv inventory olap plsql schagent.conf xdk
data javavm oml4py precomp sdk
dbs jdbc OPatch python slax
deinstall jdk opmn QOpatch sqldeveloper
[oracle@oracle-21c-vagrant dbhome_1]$ cd network
[oracle@oracle-21c-vagrant network]$ cd admin
[oracle@oracle-21c-vagrant admin]$ pwd
/opt/oracle/product/21c/dbhome_1/network/admin
[oracle@oracle-21c-vagrant admin]$ ls -a
. .. samples shrept.lst
[oracle@oracle-21c-vagrant admin]$ vim tnsnames.ora
[oracle@oracle-21c-vagrant admin]$ cat tnsnames.ora
#Test editing tnanames.ora - Success!
Per the title of this "issue", my issues are solved. I can login to at least one of the vms (No.1), connect to it via a sql client, can modify files inside the Oracle DB home, no password requests in the way any more. For this reason I'm closing the issue.
When it comes to the other vm (No.2) that I still can't log in, I'm still working on it and might have to file a separate issue with different details. But thanks again to @PaulNeumann for the migration tips so I know what options I have.
Just FYI, the OL vagrant boxes have the vim-minimal
package installed, so you shouldn't have to install vim
.
The thing is that the executable is called vi
, not vim
😉
@AmedeeBulle Ah, great for me to know. Thanks!
@AmedeeBulle Thanks! I didn't know that.
Hello, could you please give me some insight about this?
Description / Steps to Reproduce: Follow the ReadMe of https://github.com/oracle/vagrant-projects/tree/main/OracleDatabase/21.3.0, all the way to the "Resetting password" section. Up to this point, the vagrant up should have been ran successfully, vagrant ssh should have been ran without issue (vagrant@oracle-21c-vagrant), then sudo su - oracle is also ran successfully (oracle@oracle-21c-vagrant). Below are my current versions (many of below are the latest I just updated to recently to troubleshoot, and I had other issues a couple minor versions back already): Vagrant 2.3.4 VirtualBox Guest Addition/inside the vm 7.0.6 (it was a mismatch before and that was even more problematic) VirtualBox on host 7.0.6r155176 Oracle Linux 8.7.411 Oracle DB 21.1.3 OS Ventura 13.2.1 Vagrantfile: currently exactly the same as in your project, no customization
Expected Behavior / Acutal Behavior: At this point, when things were working perfectly in the past, I could do command like sqlplus / as sysdba or sqlplus /nolog to start doing real database work. But now upon sqlplus command(s), oracle@oracle-21c-vagrant keeps the gate and insists that I type in the "password".
What I know / What I've tried:
This is in the way of further trouble-shooting other issues I have, such as I can't modify the listener.ora or tnsnames.ora.