Subversion Repositories oidplus

Easy setup for Oracle DB testing

================================

Setup OracleDB Virtual Machine (Developer Days VM 2019)

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

Download "DeveloperDaysVM2019-05-31_20.ova" at https://www.oracle.com/database/technologies/databaseappdev-vm.html

Import OVA in Oracle VirtualBox

Set network mode to "Network bridge"

Boot Virtual Machine

Inside the VM, run "ifconfig" to see the IP address of eth0

also note the "Pluggable DB" which should be orcl.

You need to include it in the PDO configuration settings of OIDplus (see below).

Inside the VM, edit /u01/app/oracle/product/version/db_1/network/admin/listener.ora

(this is required so that you can connect to the PDB)

==> Add "USE_SID_AS_SERVICE_LISTENER=on" at the end

Type "service oracle restart"

==> Password "oracle"

Open sqlcl (SQLplus)

	Username:	sys as sysdba

	Password:	oracle

	create user oidplus identified by oidplus;

	grant create session to oidplus;

	grant create table to oidplus;

	grant select any sequence to oidplus;

	grant create any sequence to oidplus;

	grant all privileges to oidplus;

	exit;

Open sqlcl (SQLplus)

	Username:	oidplus

	Password:	oidplus

	paste the struct SQL files from OIDplus

	Suggestion: Open new console Window

		cd Desktop

		wget https://raw.githubusercontent.com/danielmarschall/oidplus/master/plugins/viathinksoft/sqlSlang/oracle/sql/struct.sql

		Then copy-paste the contents of this file into sqlcl

	Type "commit" (very important)

Note: Sometimes, the network connection will break. Then you need to enter "ifdown eth0" and "ifup eth0".

On the OIDplus machine (e.g. Windows)

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

On the OIDplus machine, download

	https://www.oracle.com/de/database/technologies/instant-client/winx64-64-downloads.html

	"Basic package"

	instantclient-basic-windows.x64-21.3.0.0.0

and extract it anywhere.

Extend the PATH environment variable by adding the directory where you extracted the instantclient

Reboot PC or logout and login to make the PATH environment variable effective

In the php.ini , enable the extension php_pdo_oci

	extension=pdo_oci

At OIDplus, edit userdata/baseconfig/config.inc.php:

	(IP-Address 10.0.22.152 is an example)

	OIDplus::baseConfig()->setValue('DATABASE_PLUGIN',   'PDO');

	OIDplus::baseConfig()->setValue('PDO_DSN',           "oci:dbname=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.22.152)(PORT = 1521)))(CONNECT_DATA = (SID = orcl)))");

	OIDplus::baseConfig()->setValue('PDO_USERNAME',      "oidplus"); # Alternative: hr

	OIDplus::baseConfig()->setValue('PDO_PASSWORD',      "oidplus"); # Alternative: oracle

	OIDplus::baseConfig()->setValue('TABLENAME_PREFIX',  '');

Note: orcl is the PDB (pluggable database) and orclcdb is the CDB (container)

Install OracleDB XE 21c Client+Server on an Ubuntu machine

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

In this example, the sys password is "oidplus",

the SID is "XE", the PDB name is "XEPDB1" (these are the standards in my VM),

the port is 1539 (for some reason the default port in my installation),

and the PDB username is "oidplus" with password "oidplus".

1. Download Oracle Database Express Edition

URL https://www.oracle.com/de/database/technologies/appdev/xe/quickstart.html

Download "oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm"

2. Create "DEB" file

Install required packages using the command:

sudo apt-get install alien libaio1 unixodbc

Convert RPM package format to DEB package format (that is used by Ubuntu) using the command:

sudo alien --scripts -d oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm

ATTENTION! THIS COMMAND TAKES VERY LONG (APPROX 2 HOURS) AND NEEDS 14 GB HARD DISK SPACE!

3. Create the required chkconfig script using the command:

sudo nano /sbin/chkconfig

Copy the following into the file and save:

#!/bin/bash

# Oracle 11gR2 XE installer chkconfig hack for Ubuntu

file=/etc/init.d/oracle-xe

if [[ ! `tail -n1 $file | grep INIT` ]]; then

echo >> $file

echo '### BEGIN INIT INFO' >> $file

echo '# Provides: OracleXE' >> $file

echo '# Required-Start: $remote_fs $syslog' >> $file

echo '# Required-Stop: $remote_fs $syslog' >> $file

echo '# Default-Start: 2 3 4 5' >> $file

echo '# Default-Stop: 0 1 6' >> $file

echo '# Short-Description: Oracle 11g Express Edition' >> $file

echo '### END INIT INFO' >> $file

fi

update-rc.d oracle-xe defaults 80 01

4. Change the permission of the chkconfig file using the command:

sudo chmod 755 /sbin/chkconfig  

5. Set kernel parameters. Oracle XE requires additional kernel parameters which you need to set using the command:

sudo nano /etc/sysctl.d/60-oracle.conf

Copy the following into the file and save:

# Oracle 11g XE kernel parameters  

fs.file-max=6815744  

net.ipv4.ip_local_port_range=9000 65000  

kernel.sem=250 32000 100 128 

kernel.shmmax=536870912 

5c. Now load the kernel parameters:

sudo service procps start

7. Set up /dev/shm mount point for Oracle. Create the following file using the command:

sudo nano /etc/rc2.d/S01shm_load

Copy the following into the file and save.

#!/bin/sh

case "$1" in

start) mkdir /var/lock/subsys 2>/dev/null

	touch /var/lock/subsys/listener

	rm /dev/shm 2>/dev/null

	mkdir /dev/shm 2>/dev/null

	mount -t tmpfs shmfs -o size=2048m /dev/shm ;;

*) echo error

exit 1 ;;

esac 

8. Change the permissions of the file using the command:

sudo chmod 755 /etc/rc2.d/S01shm_load

9. Execute the following commands:

sudo ln -s /usr/bin/awk /bin/awk 

sudo mkdir /var/lock/subsys 

sudo touch /var/lock/subsys/listener 

9. REBOOT your Ubuntu VM.

5d. Verify the new parameters are loaded using:

sudo sysctl -q fs.file-max

You should see the file-max value that you entered earlier.

10. Install the oracle DBMS using the command:

sudo dpkg --install oracle-database-xe-21c_1.0-2_amd64.deb

--------- TODO: AFTER I TRIED TO RE-DO THIS TUTORIAL ON A CLEAN INSTALLATION, I NOW GET FOLLOWING ERROR: ---------------

[SEVERE] An error occurred while configuring the directories for the Oracle home. Verify the execution of the following command: /opt/oracle/product/21c/dbhomeXE/bin/roohctl -enable  

dpkg: error processing package oracle-database-xe-21c (--install):

 installed oracle-database-xe-21c package post-installation script subprocess returned error exit status 1

Processing triggers for libc-bin (2.35-0ubuntu3) ...

Errors were encountered while processing:

 oracle-database-xe-21c

Probably the installation scripts in the generated DEB file are broken??? In the postinstall script /var/lib/dpkg/info/oracle-database-xe-21c.postinst we see:

su -c "/opt/oracle/product/21c/dbhomeXE/bin/diagsetup clustercheck=false basedir=/opt/oracle oraclehome=/opt/oracle/product/21c/dbhomeXE " >> /dev/null -m 

this is wrong ">> /dev/null" will make "-m oracle" invalid?!

But why did it work on my first try?

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

11. Configure Oracle using the command:

sudo /etc/init.d/oracle-xe-21c configure 

--------- TODO: AFTER I TRIED TO RE-DO THIS TUTORIAL ON A CLEAN INSTALLATION, I NOW GET FOLLOWING ERROR: ---------------

/opt/oracle/product/21c/dbhomeXE/bin/orabase: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

/bin/mkdir: cannot create directory ‘/cfgtoollogs’: Permission denied

bash: line 1: /cfgtoollogs/netca/netca_configure_out.log: No such file or directory

chmod: cannot access '/cfgtoollogs/netca/netca_configure_out.log': No such file or directory

bash: line 1: /cfgtoollogs/netca/netca_configure_out.log: No such file or directory

Listener configuration failed. Check log '/cfgtoollogs/netca/netca_configure_out.log' for more details.

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

Attention: If the drive has no enough space (approx 5 GB required),

then make sure that you put /opt on a separate virtual drive (add to fstab etc.)

In case you have a damaged installation because of an installer error,

you can try recovering using:

rm -rf /opt/oracle /var/lib/dpkg/info/oracle-database-xe-21c.*

12. Enter the following information:

- A password for the SYS and SYSTEM administrative user accounts    ===> ENTER "oidplus" in our example

- Confirm password for SYS and SYSTEM administrative user accounts  ===> ENTER "oidplus" in our example

13. You can now visit https://localhost:5500/em/login

	Username:	sys

	Passwort:	oidplus

	Cont.name:	[empty]

14. Setup environment variables by editting your .bashrc file:

nano ~/.bashrc

Add the following lines to the end of the file:

export ORACLE_HOME=/opt/oracle/product/21c/dbhomeXE/

export ORACLE_SID=XE

#export NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh`

export ORACLE_BASE=/opt/oracle

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

export PATH=$ORACLE_HOME/bin:$PATH

15. Load the changes by executing your profile:

. ~/.profile

16. Start the Oracle 21c XE:

sudo service oracle-xe-21c start

17. Add user YOURUSERNAME to group dba using the command (not important)

sudo usermod -a -G dba YOURUSERNAME

18. Install php-oci8 on Ubuntu (it is not in the package manager!)

18.1. Download the basic Instant Client

Oracle Website http://www.oracle.com/technetwork/database/database-technologies/instant-client/downloads/index.html

Download instantclient-basic-linux.x64-21.6.0.0.0dbru.zip

18.2. Unzip both files on server, in my case, you will get a new folder named "instantclient_12_2"

unzip instantclient-basic-linux.x64-21.6.0.0.0dbru.zip

18.3. Move and rename the instantclient folder to the oracle folder (in Ubuntu it is /opt/oracle)

sudo mkdir /opt/oracle/instantclient

sudo mv instantclient_12_2 /opt/oracle/instantclient

18.4. Change rights on folder

chown -R root:www-data /opt/oracle/instantclient

18.5. Check if you have the required packages for installing OCI8

apt install php7.2-dev php-pear build-essential libaio1

18.6a. Add instant client to ld config files

echo /opt/oracle/instantclient > /etc/ld.so.conf.d/oracle-instantclient.conf

18.6b. Update Dynamic Linker Run-Time Bindings

ldconfig

18.7a. Now install OCI8 by pecl

pecl install oci8

18.7b. The OCI8 installation is asking you for the right folder

instantclient,/opt/oracle/instantclient

18.8a. Add oci lib to the cli php config (console php)

echo "extension = oci8.so" >> /etc/php/8.1/cli/php.ini

18.8b. Add oci lib to the apache php config

echo "extension = oci8.so" >> /etc/php/8.1/apache2/php.ini

18.9a. Set environement variables for the cli version (you will need to reboot the server after)

echo "LD_LIBRARY_PATH=\"/opt/oracle/instantclient\"" >> /etc/environment

18.9b. Set environement variables for the apache version

echo "export LD_LIBRARY_PATH=\"/opt/oracle/instantclient\"" >> /etc/apache2/envvars

echo "export ORACLE_HOME=\"/opt/oracle/instantclient\"" >> /etc/apache2/envvars

18.10.	Restart Apache

service apache2 restart

19. Start command line shell as the system admin using the command:

sudo sqlplus sys as sysdba

Enter the password that you gave while configuring Oracle earlier. You will now be placed in a SQL environment that only understands SQL commands.

20. Create a regular user account in Oracle using the SQL command:

alter session set container=XEPDB1;

create user oidplus identified by oidplus;

grant create session to oidplus;

grant create table to oidplus;

grant select any sequence to oidplus;

grant create any sequence to oidplus;

grant all privileges to oidplus;

exit;

(Note: How did I find the name "XEPDB1"? I looked in /opt/oracle/oradata/XE/ )

21. Start the commandline shell as a regular user using the command:

sqlplus

You will be prompted for a username and password. Try oidplus and oidplus.

22. In OIDplus, run setup. The parameters of the base config file are:

OIDplus::baseConfig()->setValue('OCI_CONN_STR',      'localhost:1539/XEPDB1');

OIDplus::baseConfig()->setValue('OCI_USERNAME',      'oidplus');

OIDplus::baseConfig()->setValue('OCI_PASSWORD',      'oidplus');

// (Very important is port 1539)

23. Install the database (with examples):

curl -s "http://localhost/oidplus/setup/struct_with_examples.sql.php?prefix=oidplus_&slang=oracle" | sqlplus oidplus/oidplus@localhost:1539/XEPDB1

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

Troubleshooting in case the listener doesn't start:

- chmod -R 0777 /opt/oracle/product/21c/dbhomeXE/network/

- rm -rf /var/tmp/.oracle

- Check the output of /etc/init.d/oracle-xe-21c start

Troubleshooting VMware /mnt/hgfs (shared folders) empty:

- Run  sudo vmhgfs-fuse .host:/ /mnt/hgfs/ -o allow_other -o uid=1000

When the system has rebooted, and Oracle DB is not started, run

	sudo /etc/init.d/oracle-xe-21c start

	TODO: How to put this service in the auto-start???

	This doesn't work:

		# systemctl enable oracle-xe-21c

		oracle-xe-21c.service is not a native service, redirecting to systemd-sysv-install.

		Executing: /lib/systemd/systemd-sysv-install enable oracle-xe-21c

		update-rc.d: error: oracle-xe-21c Default-Start contains no runlevels, aborting.

TODO: I tried the above tutorial with a new Ubuntu VM, but the installation does not work!