Archive for the ‘Docker’ Category
Native sqlplus editing
I have to remind myself from time to time that Ubuntu is a Desktop or Workstation and by default can go missing key server software, like ssh. This became evident when I wanted to check whether I could run sqlplus from my Mac OS terminal through my Ubuntu VM and internally embedded Oracle Database 23c Free docker instance.
If like me you forgot to add it, you can add the ssh service with the following commands to your Ubuntu VM:
sudo apt update sudo apt install -y openssh-server sudo systemctl start ssh.service |
Then, you can test the installation with an ssh call to localhost, like:
ssh localhost |
You should see the following, where you need to enter the sudoer’s password to continue. Your localhost target causes an authenticity check, like:
The authenticity of host 'localhost (127.0.0.1)' can't be established. ED25519 key fingerprint is SHA256:js8knEf/lOE1rSss3u8lP4Ii634Y0CkUz+oJM5dt3w4. This key is not known by any other names Are you sure you want to continue connecting (yes/no/[fingerprint])? |
Enter yes to continue:
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes |
It will now add localhost to the list of known hosts provide standard messages, as shown below.
Warning: Permanently added 'localhost' (ED25519) to the list of known hosts. student@localhost's password: Welcome to Ubuntu 22.04.3 LTS (GNU/Linux 6.2.0-39-generic x86_64) * Documentation: https://help.ubuntu.com * Management: https://landscape.canonical.com * Support: https://ubuntu.com/advantage Expanded Security Maintenance for Applications is not enabled. 9 updates can be applied immediately. 5 of these updates are standard security updates. To see these additional updates run: apt list --upgradable Enable ESM Apps to receive additional future security updates. See https://ubuntu.com/esm or run: sudo pro status The programs included with the Ubuntu system are free software; the exact distribution terms for each program are described in the individual files in /usr/share/doc/*/copyright. Ubuntu comes with ABSOLUTELY NO WARRANTY, to the extent permitted by applicable law. |
Having verified the installation and functionality of ssh in the Ubuntu VM. Then, I launched a Terminal session on my MacBookPro base operating system. Using the Ubuntu instance ssh and a customized Bash function, I discovered its IP address.
The following is the get_ip() user-defined function in the Ubuntu instance’s student user’s customized .bashrc file:
# Return the local instance's IP address. get_ip () { echo `hostname -I | cut -f1 -d' '` } |
In this instance, it returned:
192.168.195.155 |
With the IP address, I secured shelled into my Ubuntu sudoer student user like this:
ssh student@192.168.195.155 |
It’ll prompt you for the remote server’s student password, like:
student@192.168.195.155's password: |
After entering the correct password, I got the standard reply of a valid connection:
Welcome to Ubuntu 22.04.3 LTS (GNU/Linux 6.2.0-39-generic x86_64) * Documentation: https://help.ubuntu.com * Management: https://landscape.canonical.com * Support: https://ubuntu.com/advantage Expanded Security Maintenance for Applications is not enabled. 9 updates can be applied immediately. 5 of these updates are standard security updates. To see these additional updates run: apt list --upgradable Enable ESM Apps to receive additional future security updates. See https://ubuntu.com/esm or run: sudo pro status Last login: Fri Jan 5 18:13:21 2024 from 127.0.0.1 |
Next, I connected to the Ubuntu Docker Oracle Database 23c Free instance with this syntax:
docker exec -it --user student oracle23c bash |
At the prompt for the Docker instance of Oracle Database 23c Free, you can type sqlplus to work directly against the Oracle Database 23c Free instance with a pluggable c##student database user.
sqlplus c##student/student SQL*Plus: Release 23.0.0.0.0 - Production on Sat Jan 6 01:38:06 2024 Version 23.3.0.23.09 Copyright (c) 1982, 2023, Oracle. All rights reserved. Last Successful login time: Sat Dec 23 2023 04:30:00 +00:00 Connected to: Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.3.0.23.09 |
Now, I can interactively edit my files with vi in the Docker Oracle Database 23c Free directory. The following demonstrates using the sandboxed student() function from my earlier Oracle 23c Free SQL*Plus blog post and connects as a sandboxed student user in the Docker Oracle 23c Free container. The image uses a different Mac OS and different Ubuntu VM from the earlier entries in this blog post from the earlier examples.
You can edit and test the files in the Docker Oracle 23c Free instance through the command-line interface (CLI). You can further automate the ssh connection by making the Ubuntu instance’s IP address a static address instead of a DCHP-assigned address; and then you can put it in the Mac OS’s /etc/hosts file which lets you resolve it by name (through file versus DNS resolution).
As always, I hope this helps those looking for a solution.
Parallels + Ubuntu
Installing Parallels on my iStudio (M2) was straightforward because I let it install Windows 11. Then, when I wanted to install Ubuntu it wasn’t quite that easy. You just need to understand that you click the Parallels’ Window menu option and Control Center option.
The Control Center option provides the following dialog.
Click the + symbol to create a new virtualization and you get the following dialog; and choose the Download Ubuntu with x86_64 emulation if you want to install a Docker image that’s not ported to ARM, like the Oracle Database 19c.
It’ll then explain in this dialog that is uses Apple’s Rosetta 2 technology, which means you should be able to install an Intel architecture Docker image with Oracle Database 23c.
As always, I hope this helps those trying to sort out how to leverage a new stack.
Docker on macOS
I finally got on the current release of macOS, Monterey, and found that my tedious Docker error still existed. When the computer boots, I get the following Fatal Error message:
Open a Terminal session and issue the following command:
killall Docker |
Then, restart Docker and everything is fine.
MySQL macOS Docker
While you can download MySQL as a DMG package, a number of users would prefer to install it as a Docker instance. You won’t find the macOS downloads on the same web site as other downloads. You can use the following macOS download site.
After installing Docker on your macOS, you can pull a copy of the current MySQL Server with the following command:
docker pull mysql/mysql-server |
You should create a mysql directory inside your ~/Documents directory with this command:
mkdir ~/Documents/mysql |
Then, you should use the cd command to change into the ~/Documents/mysql directory and run this command:
pwd |
It should return the following directory:
/Users/<user_name>/Documents/mysql |
Use the /Users/<user_name>/Documents/mysql as the
docker run --name=mysql1 --volume=<path_to_folder>:/var/lib/mysql -p 33060:3306/tcp -d mysql/mysql-server |
The --name option value is mysql1 and it becomes the container value. Docker mounts the column in the ~/Documents/mysql folder. All data from the Docker container under the /var/lib/mysql directory will persist in this directory. This directory will still contain the database when the container is shut down.
The docker run command maps the localhost’s 33060 port to the 3306 port on the Docker container. You will use the 33060 port to connect to the Docker instance of MySQL. It raises a dialog box asking for permission to access the directory. You need to allow Docker to write to the ~/Documents/mysql directory.
You can verify that the Docker container is running with the following command:
docker ps |
It should return:
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 142b5c491cd8 mysql/mysql-server "/entrypoint.sh mysq…" 7 minutes ago Up 6 minutes (healthy) 33060-33061/tcp, 0.0.0.0:33060->3306/tcp mysql1 |
You can get the MySQL generated root password with this Docker command:
docker logs mysql1 2>&1 | grep GENERATED |
It returns something like the following:
[Entrypoint] GENERATED ROOT PASSWORD: vop#3GNYqK3nC@S@N3haf3nox5E |
Use the following Docker command to connect to the Docker container:
docker exec -it mysql1 /bin/bash |
It launches a Bash shell inside the Docker container:
bash-4.2# |
Start the mysql Command-Line Interface (CLI):
mysql -uroot -p |
You are then prompted for a password:
Enter password: |
After successfully entering the password, you’ll see the following:
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 55 Server version: 8.0.22 Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> |
Unless you want to remember that hugely complex root password, you should consider changing it to something simple like, 'cangetin' with the following command:
ALTER USER 'root'@'localhost' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'cangetin'; |
Next, you should check for the installed databases with this command:
show databases; |
It will return:
+--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.01 sec) |
Exiting mysql, you can see the contents of the root user’s directory with this list command:
ls -al |
It should return:
total 84 drwxr-xr-x 1 root root 4096 Jan 12 03:41 . drwxr-xr-x 1 root root 4096 Jan 12 03:41 .. -rwxr-xr-x 1 root root 0 Jan 12 03:41 .dockerenv lrwxrwxrwx 1 root root 7 Oct 12 22:06 bin -> usr/bin dr-xr-xr-x 2 root root 4096 Apr 11 2018 boot drwxr-xr-x 5 root root 340 Jan 12 03:41 dev drwxr-xr-x 2 root root 4096 Oct 19 05:47 docker-entrypoint-initdb.d -rwxr-xr-x 1 root root 7496 Oct 19 05:37 entrypoint.sh drwxr-xr-x 1 root root 4096 Jan 12 03:41 etc -rw-r--r-- 1 root root 86 Jan 12 03:41 healthcheck.cnf -rwxr-xr-x 1 root root 1073 Oct 19 05:37 healthcheck.sh drwxr-xr-x 2 root root 4096 Apr 11 2018 home lrwxrwxrwx 1 root root 7 Oct 12 22:06 lib -> usr/lib lrwxrwxrwx 1 root root 9 Oct 12 22:06 lib64 -> usr/lib64 drwxr-xr-x 2 root root 4096 Apr 11 2018 media drwxr-xr-x 2 root root 4096 Apr 11 2018 mnt -rw-r--r-- 1 root root 0 Jan 12 03:41 mysql-init-complete drwxr-xr-x 2 root root 4096 Apr 11 2018 opt dr-xr-xr-x 127 root root 0 Jan 12 03:41 proc dr-xr-x--- 1 root root 4096 Jan 12 04:21 root drwxr-xr-x 1 root root 4096 Oct 19 05:47 run lrwxrwxrwx 1 root root 8 Oct 12 22:06 sbin -> usr/sbin drwxr-xr-x 2 root root 4096 Apr 11 2018 srv dr-xr-xr-x 13 root root 0 Jan 12 03:41 sys drwxrwxrwt 1 root root 4096 Jan 12 03:41 tmp drwxr-xr-x 1 root root 4096 Oct 12 22:06 usr drwxr-xr-x 1 root root 4096 Oct 12 22:06 var |
At this point, you have to make a choice about how you will access the MySQL database. You have a couple options:
- Create an individual
studentuser that can access the MySQL-Server as a micro-service, which would only be a MySQL user connecting through MySQL workbench. At least, that’s the only connection option unless you likewise install themysqlclient on your host macOS. Themysqlclient lets you connect from the host operating system through the Command-Line Interface (CLI). - Create a local
studentuser account inside the Docker container. It will have access to the container file system and mimic the behavior of a non-rootuser on a server.
Let’s create both for this demonstration. Reconnect as the root user and issue the following two commands:
CREATE USER 'student'@'localhost' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'student'; CREATE USER 'student'@'%.%.%.%' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'student'; |
The first version of the student user lets you access the database from inside the Docker container. The second version of the student user lets you access the database from MySQL Workbench deployed on your base macOS.
You can add a sakila database and grant all privileges to the student user with the following command as the root user:
CREATE DATABASE sakila; GRANT ALL ON sakila.* TO 'student'@'localhost'; GRANT ALL ON sakila.* TO 'student'@'%.%.%.%'; |
You need to get the sakila database from the Internet within the Docker container. Exit the mysql client with the following command:
quit; |
As the root user, install the wget and tar Linux utilities with this command:
yum install -y wget tar |
As the student user, you can use the wget command to grab a copy of the sakila database and store the database locally. Use the cd command to get to your ${HOME} directory, like:
cd |
Use this syntax to get a copy of the sakila database:
wget http://downloads.mysql.com/docs/sakila-db.tar.gz |
Use the ls command to verify the download, then run the following set of Linux commands from the Linux CLI:
tar -xzf sakila-db.tar.gz cd sakila-db |
Run the following two commands from the sakila-db directory:
mysql -ustudent -p < sakila-schema.sql mysql -ustudent -p < sakila-data.sql |
or, you can connect as the student user to the MySQL client and run them there:
source sakila-schema.sql source sakila-data.sql |
You create a non-root student user for the Docker container from the macOS host opearting system. Which means you need to quit; the mysql client, and exit the root user’s session with the Docker container.
At the terminal in your macOS, issue the following Docker command to create a student account in the mysql1 container:
docker exec mysql1 bash -c "useradd -u 501 -g mysql -G users \ > -d /home/student -s /bin/bash -c "Student" -n student" |
Now, you can connect as the student user to the mysql1 container, with the following Docker command:
docker exec -it --user student mysql1 bash |
The first time you connect, you will be a the / (root) directory. Use the following cd command to go to the student user’s home directory:
cd |
Then, type the following command to set the student user’s home directory as the default. You need to use this command because vim isn’t installed in the default Docker container, which would let you interactively edit files. It appends the necessary Bash shell command to the end of the .bashrc file.
echo 'cd ${HOME}' >> .bashrc |
With this change, the student user will always be available form its home directory next time you connect to the mysql1 container. You can use scp to move files into the student user’s home (/home/student) directory. However, you can create a quick test.sql file like this:
echo "select user();" > test.sql |
Connect to the mysql CLI with as the student user:
mysql -ustudent -p |
Call your test.sql file from the Linux CLI, like:
mysql -ustudent -p < test.sql |
or, you can run the test.sql program as follows form the MySQL command-line:
source test.sql |
It will return:
+-------------------+ | user() | +-------------------+ | student@localhost | +-------------------+ 1 row in set (0.00 sec) |
That’s the basic setup of the Docker MySQL Container on the macOS. You can do much more once you’ve configured it like this. For example, you can add vim to your library repository as the root user with the following command:
yum install -y vim |
It just takes a minute or a bit more. Adding vim opens up so much flexibility for you inside the Docker container, it’s impossible for me to resist. 😉
Oracle External Table
Supporting my student labs requires the ability to use external tables. I didn’t have a chance to test external tables when I adopted Oracle’s 18c Docker installations. The following are the instructions for the macOS version, which work. Unfortunately, I couldn’t sort out a way to implement external tables in the Windows version of Oracle’s 18c Docker installation. If somebody knows the way to do that, feel free to drop me a note or a URL to where the information may be found.
These two blog posts explain Oracle’s external tables:
External Tables
External Tables with Preprocessing
These instructions build on my base macOS instructions, which you can find in this earlier Install, configure, and use an Oracle Docker Container blog post. You can find the existing Oracle virtual directories when you connect as the system user and query the dba_directories view. I used the following SQL*Plus formatting commands for the query:
SET PAGESIZE 99 COL directory_name FORMAT A24 COL directory_path FORMAT A54 |
and the following SQL query:
SELECT directory_name , directory_path FROM dba_directories; |
It returns the following results:
DIRECTORY_NAME DIRECTORY_PATH ------------------------ ------------------------------------------------------ ORA_DBMS_FCP_LOGDIR /opt/oracle/product/18c/dbhomeXE/cfgtoollogs SDO_DIR_WORK /ade/b/2794046351/oracle/work SDO_DIR_ADMIN /ade/b/2794046351/oracle/md/admin ORA_DBMS_FCP_ADMINDIR /opt/oracle/product/18c/dbhomeXE/rdbms/admin XMLDIR /opt/oracle/product/18c/dbhomeXE/rdbms/xml XSDDIR /opt/oracle/product/18c/dbhomeXE/rdbms/xml/schema ORACLE_OCM_CONFIG_DIR2 /opt/oracle/product/18c/dbhomeXE/ccr/state ORACLE_OCM_CONFIG_DIR /opt/oracle/product/18c/dbhomeXE/ccr/state OPATCH_INST_DIR /opt/oracle/product/18c/dbhomeXE/OPatch DATA_PUMP_DIR /opt/oracle/admin/XE/dpdump/ OPATCH_SCRIPT_DIR /opt/oracle/product/18c/dbhomeXE/QOpatch OPATCH_LOG_DIR /opt/oracle/product/18c/dbhomeXE/rdbms/log ORACLE_BASE /opt/oracle ORACLE_HOME /opt/oracle/product/18c/dbhomeXE 14 rows selected. |
In that prior post, you will find instructions for creating an environment file. The following instructions leverage the $ORACLE_BASE environment variable, which points to the /opt/oracle directory in the Docker environment.
Here are the steps to complete the external file setup, including how to position the physical comma-separated value (CSV) files in the available but otherwise hidden Docker directories. Hidden might be the wrong word choice but they’re not visible from the host macOS operating system. You have to connect to the Docker instance as the root user.
The following Docker command, used in the prior blog post, connects as the root user:
docker exec -it videodb bash |
The instructions start before connecting to and after connecting to the Docker instance:
- Put the
transaction_upload.csvandtransaction_upload2.csvfiles into the following macOS host directory:/Users/<installuser>/oracle/student
which matches to the following internal Docker directory:
/home/student
- As the connected
rootuser, change directory to the$ORACLE_BASE(/opt/oracle) directory with the following command:cd $ORACLE_BASE
- As the connected
rootuser, make anuploaddirectory inside the$ORACLE_BASE(/opt/oracle) directory with the following command:mkdir uploadAs the
rootuser, change the ownership of theuploaddirector fromrootas the owner and primary group tooracleas the owner andoinstallas the primary group with the following command:chown oracle:oinstall upload - As the connected
rootuser, copy thetransaction_upload.csvandtransaction_upload2.csvfiles from the/home/studentdirectory to the$ORACLE_BASE(/opt/oracle) directory with the following command:cp ~student/transaction_upload*.csv $ORACLE_BASE/upload/.
- As the connected
rootuser, change from the$ORACLE_BASE(/opt/oracle) directory to theuploadsubdirectory with the following command:cd uploadAs the
rootuser, change the ownership of thetransaction_upload.csvandtransaction_upload2.csvfiles fromrootas the owner and primary group tooracleas the owner andoinstallas the primary group with the following command:chown oracle:oinstall transaction_upload*.csv - As the connected Oracle
systemuser, create theuploadvirtual directory with the following command:CREATE DIRECTORY upload AS '/opt/oracle/upload';
As the connected Oracle
systemuser, grantreadandwriteprivileges to the Oraclec##studentuser with the following command:GRANT READ, WRITE ON DIRECTORY upload TO c##student;
- As the connected Oracle
c##studentuser, create thetransaction_uploadexternally managed table with the following command:CREATE TABLE transaction_upload ( account_number VARCHAR2(10) , first_name VARCHAR2(20) , middle_name VARCHAR2(20) , last_name VARCHAR2(20) , check_out_date DATE , return_date DATE , rental_item_type VARCHAR2(12) , transaction_type VARCHAR2(14) , transaction_amount NUMBER , transaction_date DATE , item_id NUMBER , payment_method_type VARCHAR2(14) , payment_account_number VARCHAR2(19)) ORGANIZATION EXTERNAL ( TYPE oracle_loader DEFAULT DIRECTORY upload ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII BADFILE 'UPLOAD':'transaction_upload.bad' DISCARDFILE 'UPLOAD':'transaction_upload.dis' LOGFILE 'UPLOAD':'transaction_upload.log' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'" MISSING FIELD VALUES ARE NULL ) LOCATION ('transaction_upload.csv')) REJECT LIMIT UNLIMITED;
As the connected Oracle
c##studentuser, query thetransaction_uploadtable to verify that you can read the external file source through the virtualuploaddirectory.COL record FORMAT 99,999 HEADING "Record|Count" SELECT TO_CHAR(COUNT(*),'99,999') AS record FROM transaction_upload;
It should return the following value:
Record Count ------- 11,520
- As the connected Oracle
c##studentuser, create thetransaction_reversalexternally managed table with the following command:CREATE TABLE transaction_reversal ( transaction_id NUMBER , transaction_account VARCHAR2(15) , transaction_type VARCHAR2(30) , transaction_date DATE , transaction_amount NUMBER , rental_id NUMBER , payment_method_type NUMBER , payment_account_number VARCHAR2(20) , created_by NUMBER , creation_date DATE , last_updated_by NUMBER , last_update_date DATE) ORGANIZATION EXTERNAL ( TYPE oracle_loader DEFAULT DIRECTORY upload ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII BADFILE 'UPLOAD':'transaction_upload2.bad' DISCARDFILE 'UPLOAD':'transaction_upload2.dis' LOGFILE 'UPLOAD':'transaction_upload2.log' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'" MISSING FIELD VALUES ARE NULL ) LOCATION ('transaction_upload2.csv')) REJECT LIMIT UNLIMITED;
As the connected Oracle
c##studentuser, query thetransaction_reversaltable to verify that you can read the external file source through the virtualuploaddirectory.COL record FORMAT 99,999 HEADING "Record|Count" SELECT TO_CHAR(COUNT(*),'99,999') AS record FROM transaction_reversal;
It should return the following value:
Record Count ------- 1,170
Unfortunately, the file permission on the Windows version of the Oracle Docker 18c installation make it more difficult to install.
Oracle Docker Container
Install, configure, and use an Oracle Docker Container
Installing a Docker instance isn’t quite straightforward nor is it terribly difficult. It can be quite overwhelming if you’re unfamiliar with the technology of virtualization and containerization. This essay shows you how to create, configure, and manage an Oracle Database 18c XE Docker instance on the macOS. There are some slight differences when you install it on Windows OS.
Installation
You need to download the Oracle Database 18c XE file for Linux. You will find it on Oracle’s OTN website at https://www.oracle.com/downloads/. Click the Database link under the Developer Downloads banner. You want to download the Oracle Database Express Edition (XE), Release 18.4.0.0.0 (18c) file.
The file is a Linux Red Hat Package Manager (rpm) file. The file is approximately 2.5 GB in size, which means you may want to take a break while downloading it. Whether you take a break or not, this step may take more time than you like.
While downloading the Oracle database, you want to work on the two other tasks concurrently. You need to download and install Docker and Homebrew software, which aren’t installed from Apple’s Application Store. Many macOS systems disallow by default software from outside the comfy boundaries and inspections of the Apps Store. You may need to change your system preferences to install Docker and Homebrew.
You can download Docker for the macOS from the following website:
https://docs.docker.com/docker-for-mac/install/
The Homebrew (the missing package manager for macOS) website will tell you to install it from the macOS Command Line Interface (CLI). Please note that you must already have the Xcode Command Line Tools installed before you install Homebrew. The following Homebrew installation will update your Command Line Tools to macOS Mojave Version 10.14.
Open a Terminal session from your finder and run this command:
bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)" |
After you install Homebrew in the Terminal, type the following to go to your user’s home folder (or directory):
cd |
In your home directory (/Users/username [~]), create the docker-images directory from the available GitHub docker containers with these two commands (separated by the semicolon):
cd ~/; git clone https://github.com/oracle/docker-images.git |
Move the Oracle Database XE 18c rpm file from your Downloads folder to the target docker-images subfolder with the following command:
mv ~/Downloads/oracle-database-xe-18c-1.0-1.x86_64-2.rpm \ ~/docker-images/OracleDatabase/SingleInstance/dockerfiles/18.4.0/. |
Change your present working directory with the following cd command:
cd docker-images/OracleDatabase/SingleInstance/dockerfiles |
Build the Docker image with the following command (from the directory where the buildDockerImage.sh shell script is found):
./buildDockerImage.sh -v 18.4.0 -x |
The Docker image build takes quite some time. It may take more than 10 minutes on some macOS computers. After it completes, you should see that it was successfully built and tagged in the Terminal. You can confirm the image build with this command:
docker images |
It should return something like this:
REPOSITORY TAG IMAGE ID CREATED SIZE oracle/database 18.4.0-xe 926f4349b277 12 minutes ago 5.89GB oraclelinux 7-slim 153f8d73287e 8 weeks ago 131MB |
Before you start your Docker container, you need to open a Terminal session. You will be in your home directory, which should give you a prompt like:
machine_name:~ username$ |
If you issue a pwd command, you should see the following:
/Users/username |
Create an oracle directory as subdirectory:
mkdir oracle |
While you might wonder about the oracle directory at this point, it’s to help keep straight Docker containers on the macOS file system. For example, when you install Docker instances for MySQL and PostgreSQL, you can see the Docker file systems as:
/Users/username/mysql /Users/username/oracle /Users/username/postgres |
Now, you start the Docker container with the following command:
sudo \ docker run --name videodb -d -p 51521:1521 -p 55500:5500 -e ORACLE_PWD=cangetin \ -e ORACLE_CHARACTERSET=AL32UTF8 -v ~/oracle:/home oracle/database:18.4.0-xe |
After starting the Docker container, you check the container’s status the following command:
docker ps |
Congratulations, you have successfully installed the Docker container.
Configure
The standard docker container prepares a base platform for you. It doesn’t create a schema or containerized user. It simply installs the Oracle Database Management System (DBMS) and Oracle Listener. You need to configure your Linux environment and your database.
You connect to the container as the root user, like:
docker exec -it videodb bash |
You should update any of the older packages with the following command:
yum update |
Next, you should install the openssh-server and vim packages. They’re not installed as part of the docker container’s default configuration. You’ll need them when you create non-root users and edit configuration files. This command installs them both:
yum openssh-server vim |
There are a number of things for you to do at this point. They don’t all have to be done in the order that this essay takes. Like any other installation of Oracle on Linux, there is an oracle user who owns the installation. The oracle user is a non-login user. A non-login user doesn’t have a password and disallows a ssh connection. You need to first become the root user before you can use the su (substitute user) command to become the oracle user. Only superuser accounts hold the privileges to su without credentials because they’re trusted users.
The easiest thing to do while you’re the root user is test your ability to connect to the Oracle database’s system schema. You set the system schema’s password to cangetin when you ran the docker run command. At the command prompt, type the following to connect to the database:
sqlplus system/cangetin@xe |
You should see the following when you connect as the system user:
SQL*Plus: Release 18.0.0.0.0 - Production on Sun Sep 13 02:48:44 2020 Version 18.4.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Last Successful login time: Sat Sep 12 2020 21:13:33 +00:00 Connected to: Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production Version 18.4.0.0.0 SQL> |
Please note that only the oracle user can connect without referencing the @xe service identifier, which is defined in the $ORACLE_HOME/network/admin/tnsnames.ora file. You can read more about the SQL*Net configuration in the documentation. The quit command exits the SQL*Plus Command Line Interface. At this point, as root, lets you create a missing vi symbolic link to the vim utility you installed earlier.
ln -s /usr/bin/vim /usr/bin/vi |
With vi configured, let’s su to the oracle user and create an .bashrc file for it. You should note that a non-login user doesn’t have a .bashrc file by default. You become the oracle user with the following command:
su oracle |
You may notice that you’re not in the oracle user’s home directory. Let’s change that by moving to the correct home directory.
The home directory for any user is configured in the /etc/passwd file and available by using the echo command to see the $HOME environment variable’s value. This is true for Red Hat, Oracle Unbreakable Linux, CentOS, and Fedora distributions. They create users’ home directories as subdirectories in the /home directory.
The .bashrc file is a hidden file. Hidden files have no file name before the period and are not visible with an ls (list) command. You can find them by using a -al flag value with the ls command
ls -al |
You can use the vi editor to create a .bashrc file like this:
vi .bashrc |
A minimal oracle .bashrc (bash resource) file should look like this:
# Source global definitions if [ -f /etc/bashrc ]; then . /etc/bashrc fi # User specific environment if ! [[ "$PATH" =~ "$HOME/.local/bin:$HOME/bin:" ]] then PATH="$HOME/.local/bin:$HOME/bin:$PATH" fi export PATH # Set Prompt export PS1="[\u@localhost \W]\$ " # Change to home directory. cd $HOME # Uncomment the following line if you don't like systemctl's auto-paging feature: # export SYSTEMD_PAGER= # User specific aliases and functions |
If you know about the Linux CLI prompt, the localhost string may seem odd. It’s there to suppress the random string otherwise provided by the docker container.
A number of other Oracle environment parameters have already been set. You can see them with this command:
env | grep -i oracle |
You can connect as the privileged sysdba role, once known as the internal user, to start and stop the database instance without stopping the docker container. That command syntax is:
sqlplus / as sysdba |
Only the oracle user has privileges to connect with the sysdba role by default. That’s because the oracle user is the owner of the Oracle database installation.
While connected as the oracle user, you should make three changes. One change to oracle executable file permissions and two changes to the glogin.sql default configuration file.
The initial permissions on the $ORACLE_HOME/bin/oracle executable file in the docker container are shown below.
-rwxr-x--x 1 oracle oinstall 437755981 Oct 18 2018 oracle |
The setuid bit is disabled when the user’s permissions are rwx. The oracle executable should always run with the permissions and ownership of the oracle user. That only happens when the setuid bit is enabled. You set the setuid. bit with the following syntax as the oracle user or privileged root superuser (from the $ORACLE_HOME/bin directory):
chmod u+s oracle |
Relisting the file in a long list view (ls -al) after the change, you should see the following:
-rwsr-x--x 1 oracle oinstall 437755981 Oct 18 2018 oracle |
The setuid bit is enabled when the user permissions are rws. Connections to the database by non-privileged Oracle users may raise ORA-01017 and ORA-12547 errors when the setuid bit is unset.
The glogin.sql file is read and processed with every connection to the database. Therefore, you should put little in there, and some would argue nothing in there. You’re going to enter the command that lets you interactively launch vi from a SQL> command prompt and set a SQL*Plus environment variable. The SQL*Plus environment variable lets you see debug messages raised by your PL/SQL programs.
To edit the glogin.sql file, change your terminal directory as follows:
cd $ORACLE_HOME/sqlplus/admin |
Add the following two lines at the bottom of the glogin.sql file:
define _editor=vi SET SERVEROUTPUT ON SIZE UNLIMITED |
That’s it for configuring the oracle user’s account. Type exit to return to the root user shell. Type exit again, this time to leave the root user’s account and return to your hosting macOS.
The next configuration step sets up a non-privileged student account in Linux. You setup the student user with the following Docker command (for reference, it can’t be done from within the docker container):
sudo \ docker exec -it videodb bash -c "useradd -u 501 -g dba -G users \ -d /home/student -s /bin/bash -c "Student" -n student" |
You will be prompted for a password when this command runs. Try to keep the password simple. Using a password like cangetin is recommended when it’s a development instance. You can connect with the following docker command:
docker exec -it --user student videodb bash |
After logging in to the docker container as the student user, you need to configure the .bashrc file. You should use the following minimal .bashrc file in the /home/student directory, which you can create with the vi editor.
# Source global definitions if [ -f /etc/bashrc ]; then . /etc/bashrc fi # User specific environment if ! [[ "$PATH" =~ "$HOME/.local/bin:$HOME/bin:" ]] then PATH="$HOME/.local/bin:$HOME/bin:$PATH" fi export PATH # Set Prompt export PS1="[\u@localhost \W]\$ " # Change to home directory. cd $HOME # Uncomment the following line if you don't like systemctl's auto-paging feature: # export SYSTEMD_PAGER= # User specific aliases and functions # Set Oracle environment variables. export ORACLE_SID=XE export ORACLE_BASE=/opt/oracle export ORACLE_HOME=/opt/oracle/product/18c/dbhomeXE |
As the c##student user, you need to connect to the system schema and provision a c##student container database. You can connect to the system schema with the following syntax:
sqlplus system/cangetin@xe |
There are four steps required to provision a container database. These steps are different than the steps for previous non-container databases. In non-container databases, you could grant privileges directly to database users. Oracle now requires that you create database roles, which bundle privileges together. Then, you grant roles to users. The four provisioning steps are:
- Create a user, which must adhere to the following naming convention from Oracle Database 12c forward. The database user’s name must start with the letter
cand two#(pound) symbols followed by a character and then a string of letters and numbers. - Create a role, which must use the same naming convention as containerized users. Failure to use the correct naming convention raises an
ORA-65096error. - Grant database privileges to a role.
- Grant a database role to a user.
You create a c##student container database user with the following syntax:
CREATE USER c##student IDENTIFIED BY student DEFAULT TABLESPACE users QUOTA 100M ON users TEMPORARY TABLESPACE temp; |
Next, you create a c##studentrole container role with the following syntax:
CREATE ROLE c##studentrole CONTAINER = ALL; |
Then, you grant the following privileges to your newly created c##studentrole role:
GRANT CREATE CLUSTER, CREATE INDEXTYPE, CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION, CREATE TABLE, CREATE TRIGGER, CREATE TYPE, CREATE VIEW TO c##studentrole; |
Finally, you grant a c##studentrole role (bundle of privileges) to a c##videodb user:
GRANT c##studentrole TO c##student; |
After completing these tasks, you should use the quit command to exit the SQL*Plus CLI. Please note that unlike some database CLIs, you do not need to add a semicolon to quit the connection. Oracle divides its CLI commands into SQL*Plus and SQL commands; and the quit command is a SQL*Plus command. SQL*Plus commands do not require the terminating semicolon. SQL commands do require the semicolon or a line return and forward slash, which dispatches the SQL command to the SQL engine.
You should confirm that the provisioning worked by reconnecting to the Oracle database as the c##student user with this syntax:
sqlplus c##student/student@xe |
You have now provisioned and verified the connection to a database container user. Use the quit command to disconnect from the Oracle database, and the exit command to return to your host operating system.
At this point you have a couple options for using the docker container. You can install a proprietary Integrated Development Environment (IDE), like Oracle’s free SQL Developer. There are several others and some support more than one database engine. Unfortunately, all the others have annual licensing costs.
Post Install: Access Tools
Oracle’s SQL Developer is a Java-based solution that runs on numerous platforms. You can download SQL Developer from Oracle’s OTN web site:
https://www.oracle.com/downloads/
Click on the Developer Tools link to find the current version of the SQL Developer. Before you install SQL Developer on your macOS, you will need to download and install the Java 8 Software Development Kit (SDK) from this web site:
http://www.oracle.com/technetwork/java/javase/downloads/
You configure a SQL Developer connection with the following values: use localhost as the host, c##student as the user, xe as the network service identifier, and 51521 as the port address. Click the plus button to add a connection where you enter these values, like shown below:
While the Java code in SQL Developer supports a port connection, Docker maps the port to the Oracle docker container. You don’t need to resolve the connection between SQL Developer and the Oracle Database listener through the network layer because this solution uses an Internal Process Control (IPC) model, based on socket to socket communication.
With virtualization you would need to install the Oracle Instant Client software on the hosting operating system. Then, you would configure your /etc/hosts file on both the hosting (macOS) and hosted (Oracle Linux) operating systems. Alternatively, you could add both IP addresses to a DNS server. The IP addresses let you map the connection between your physical macOS system and the Docker container running Oracle Linux. You can find further qualification of the connection mechanisms and repackaging in the Oracle Docker User Guide.
Containers map a local directory to operating system inside the container. Based on the earlier instructions the ~/oracle directory maps to the /home directory in the docker container. You have the ability to edit and move files within this portion of the file system’s hierarchy, which means you have complete control of the portion of the file system owned by the student user.
The next few steps help you verify the dual access to this portion of the docker container. Open a Terminal session and check your present working directory (with the pwd utility).
macName:~ username$ pwd |
It should return:
/Users/username |
During the installation, you created two subdirectories in the /Users/username directory. They were the oracle and docker-images subdirectories. In your host macOS, you should list (with the ls utility) the contents of your oracle subdirectory:
ls ~/oracle |
It should return the following:
oracle student |
As mentioned, your macOS /Users/username/oracle directory holds the contents of your docker container’s /home directory. That means that your /Users/username/oracle/student directory mirrors the /home/student directory in your docker container.
Assume your GitHub code repository for Oracle development is in a directory on your macOS side. The local mapping to the ~/oracle/student directly lets you put symbolic links in the hosted student user’s subdirectories. These symbolic links would point to the editable code on the macOS file system, which can be concurrently linked to your GitHub code repository.
Docker on Fedora 27
This walks you through the steps to install the Community Edition of Docker on Fedora 27. If you’ve been living under a rock for a few years, Docker is an open source container virtualization software.
Docker or Docker Community Edition is a necessary step if you want to install something like Microsoft’s SQL Server on Fedora because only these are supported platforms:
- Red Hat Enterprise Linux 7.3 or 7.4
- SUSE Linux Enterprise Server V12 SP2
- Ubuntu 16.04
- Docker Engine 1.8+
The first step requires you to add the docker-ce repo to your instance with he curl utility, which should already be installed in most cases. You can check whether the curl utility is available with the which command, like:
which -a curl |
If installed it should return the following:
/usr/bin/curl /bin/curl |
You add the docker-ce repo with the following curl command:
sudo curl -o /etc/yum.repos.d/docker-ce.repo https://download.docker.com/linux/fedora/docker-ce.repo |
You should see something like the following if it was successful:
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 2544 100 2544 0 0 2544 0 0:00:01 --:--:-- 0:00:01 12913 |
Next, you can install docker-ce with the following yum command or if you prefer use the dnf utility:
sudo yum install -y docker-ce |
It should produce a log file like the one provided, which you can see by clicking the Display detailed console link that expands the page to show you the console details.
Display detailed console →
Docker CE Stable - x86_64 30 kB/s | 5.9 kB 00:00
Last metadata expiration check: 0:00:00 ago on Thu 05 Jul 2018 06:56:36 PM MDT.
Dependencies resolved.
================================================================================================
Package Arch Version Repository Size
================================================================================================
Installing:
docker-ce x86_64 18.03.1.ce-1.fc27 docker-ce-stable 35 M
Installing dependencies:
container-selinux noarch 2:2.65-1.gitbf5b26b.fc27 updates 41 k
Transaction Summary
================================================================================================
Install 2 Packages
Total download size: 35 M
Installed size: 150 M
Downloading Packages:
(1/2): container-selinux-2.65-1.gitbf5b26b.fc27.noarch.rpm 133 kB/s | 41 kB 00:00
(2/2): docker-ce-18.03.1.ce-1.fc27.x86_64.rpm 20 MB/s | 35 MB 00:01
------------------------------------------------------------------------------------------------
Total 14 MB/s | 35 MB 00:02
warning: /var/cache/dnf/docker-ce-stable-8cf73e63b8c72e93/packages/docker-ce-18.03.1.ce-1.fc27.x86_64.rpm: Header V4 RSA/SHA512 Signature, key ID 621e9f35: NOKEY
Importing GPG key 0x621E9F35:
Userid : "Docker Release (CE rpm) <docker@docker.com>"
Fingerprint: 060A 61C5 1B55 8A7F 742B 77AA C52F EB6B 621E 9F35
From : https://download.docker.com/linux/fedora/gpg
Key imported successfully
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : container-selinux-2:2.65-1.gitbf5b26b.fc27.noarch 1/2
Running scriptlet: container-selinux-2:2.65-1.gitbf5b26b.fc27.noarch 1/2
neverallow check failed at /var/lib/selinux/targeted/tmp/modules/100/base/cil:9006
(neverallow base_typeattr_7 unlabeled_t (file (entrypoint)))
<root>
allow at /var/lib/selinux/targeted/tmp/modules/200/container/cil:1489
(allow spc_t unlabeled_t (file (entrypoint)))
<root>
allow at /var/lib/selinux/targeted/tmp/modules/100/sandboxX/cil:866
(allow sandbox_x_domain exec_type (file (entrypoint)))
<root>
allow at /var/lib/selinux/targeted/tmp/modules/100/virt/cil:1669
(allow virtd_lxc_t exec_type (file (entrypoint)))
<root>
allow at /var/lib/selinux/targeted/tmp/modules/100/virt/cil:2060
(allow svirt_sandbox_domain exec_type (file (entrypoint)))
Failed to generate binary
/usr/sbin/semodule: Failed!
Running scriptlet: docker-ce-18.03.1.ce-1.fc27.x86_64 2/2
Installing : docker-ce-18.03.1.ce-1.fc27.x86_64 2/2
Running scriptlet: docker-ce-18.03.1.ce-1.fc27.x86_64 2/2
Running as unit: run-rb873740b51a44061abb2c9e197871e4b.service
Verifying : docker-ce-18.03.1.ce-1.fc27.x86_64 1/2
Verifying : container-selinux-2:2.65-1.gitbf5b26b.fc27.noarch 2/2
Installed:
docker-ce.x86_64 18.03.1.ce-1.fc27 container-selinux.noarch 2:2.65-1.gitbf5b26b.fc27
Complete! |
After installing docker-ce, you can check it with the following systemctl command:
sudo systemctl status docker |
It should show service is disabled (default) and inactive:
● docker.service - Docker Application Container Engine
Loaded: loaded (/usr/lib/systemd/system/docker.service; disabled; vendor preset: disabled)
Active: inactive (dead)
Docs: https://docs.docker.com |
This indicates everything installed correctly, now you need to enable the docker service with this systemctl command:
sudo systemctl enable docker |
It should show that the docker service is enabled and inactive:
● docker.service - Docker Application Container Engine
Loaded: loaded (/usr/lib/systemd/system/docker.service; enabled; vendor preset: disabled)
Active: inactive (dead)
Docs: https://docs.docker.com |
You start the docker service with this systemctl command:
sudo systemctl start docker |
It should show that the service is enabled and inactive:
● docker.service - Docker Application Container Engine
Loaded: loaded (/usr/lib/systemd/system/docker.service; enabled; vendor preset: disabled)
Active: active (running) since Thu 2018-07-05 19:22:25 MDT; 4s ago
Docs: https://docs.docker.com
Main PID: 107768 (dockerd)
Tasks: 16
Memory: 35.8M
CPU: 140ms
CGroup: /system.slice/docker.service
├─107768 /usr/bin/dockerd
└─107772 docker-containerd --config /var/run/docker/containerd/containerd.toml
Jul 05 19:22:25 localhost.localdomain dockerd[107768]: time="2018-07-05T19:22:25.350914727-06:00
Jul 05 19:22:25 localhost.localdomain dockerd[107768]: time="2018-07-05T19:22:25.352583385-06:00
Jul 05 19:22:25 localhost.localdomain dockerd[107768]: time="2018-07-05T19:22:25.352718174-06:00
Jul 05 19:22:25 localhost.localdomain dockerd[107768]: time="2018-07-05T19:22:25.353419088-06:00
Jul 05 19:22:25 localhost.localdomain dockerd[107768]: time="2018-07-05T19:22:25.632193965-06:00
Jul 05 19:22:25 localhost.localdomain dockerd[107768]: time="2018-07-05T19:22:25.823732094-06:00
Jul 05 19:22:25 localhost.localdomain dockerd[107768]: time="2018-07-05T19:22:25.893601936-06:00
Jul 05 19:22:25 localhost.localdomain dockerd[107768]: time="2018-07-05T19:22:25.894351396-06:00
Jul 05 19:22:25 localhost.localdomain systemd[1]: Started Docker Application Container Engine.
Jul 05 19:22:25 localhost.localdomain dockerd[107768]: time="2018-07-05T19:22:25.907087735-06:00
lines 1-22/22 (END) |
A CNTL+C dismisses the open log file. As usual, I hope this helps those trying to accomplish the task for the first time.
Subscribe via RSS