Oracle

August 20th 2023

Setting up Oracle on VM, (using Virtual Box 7)

Host - (OS - Windows 10, HW - [ RAM 16G, CPU - Intel core i7, Storage - F drive has 500G VM is created on this drive. ] )
Guest - (OS - Centos 7 minimal server, HW - [ RAM 8G, CPU cores - 4, Storage - 80G Auto partitioned, Network Interface - 1 (Bridge Adapter) ] )

Post OS setup on guest..

yum update -y

yum install wget telnet -y

Downloads -

On Guest (using wget/curl, or, can download on host and transfer via scp/winscp/sharedfolder ) - Oracle database preinstall 21c & Oracle Express Edition 21c. [Optional - Sample DB]
On Host - SQL developer

In guest terminal, change directory to download path or prefix path in following commands to make absolute path of downloaded rpms.

sudo yum install oracle-database-preinstall-21c-1.0-1.el7.x86_64.rpm

sudo yum install oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm

Appended following in /etc/bashrc

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

export PATH=$PATH:$ORACLE_HOME/bin

export ORACLE_SID=XE

Updated /etc/sysconfig/network-scripts/ifcfg-enp0s3 to add/update following.

BOOTPROTO="static"

ONBOOT="yes"

IPADDR=192.168.1.206 #can use ip allocated by dhcp initially. use "ifconfig" to see current ip.

Updated /etc/hosts, Commented existing entries by '#' prefix

192.168.1.206 7cent-oracle localhost #IP machine-name localhost

Configure oracle

sudo /etc/init.d/oracle-xe-21c configure #This prompts new/confirm password, and sets for admin universally. It may fail if default port is already in use; free it up and retry.

Change address and port for service as follows.
1. in /opt/oracle/homes/OraDBHome21cXE/network/admin/listener.ora
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.206)(PORT = 1521))
2. in /opt/oracle/homes/OraDBHome21cXE/network/admin/tnsnames.ora; update for XE and LISTENER_XE
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.206)(PORT = 1521))
Start/stop Oracle service

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

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

sudo /etc/init.d/oracle-xe-21c delete #Removes configuration, data and everything without confirmation :|

Connect using sqlplus (CLI)

sqlplus #prompts user name - provide "system", password - use from step #8

Connect using SQLDeveloper on host node? firewalld was blocking incoming requests for 1521..
firewall-cmd --add-port=1521/tcp --permanent
firewall-cmd --reload

then in SQLDeveloper create new connection using user "system", password "provided during configuration step#7"and SID "XE"

Adding new user..

CREATE USER "C##rohitverma" IDENTIFIED BY rohitverma1; --Common User Prefix: In a multitenant environment, common users and roles have a specific prefix defined by the common_user_prefix parameter. By default, this parameter is set to "C##".

GRANT CREATE TABLE TO "C##rohitverma";

GRANT CONNECT TO "C##rohitverma";

GRANT INSERT ON "C##rohitverma".Student TO "C##rohitverma";

GRANT UNLIMITED TABLESPACE TO "C##rohitverma";

Connect using sqlplus as new user.

sqlplus

Enter user-name: "C##rohitverma" #Quotes are needed

Enter password:rohitverma1 #password typed will be invisible.

Importing Sample databases.. (human resource). being root..

tar -xzf db-sample-schemas-21.1.tar.gz #Extract downloaded sample db file. In Github repository (download link), should look for tags corresponding to your installation version.

cd $ORACLE_HOME/demo/schema/

cp ~/db-sample-schemas-21.1/human_resources/ ./ -R #copy extracted database folder to demo/schema/

chown oracle:oinstall human_resources/ -R #Not mandatory..

sqlplus

Enter user-name: sys as sysdba

Enter password:Oraadmin1@localhost:1521/XEPDB1 #This will be typed invisibly <PASSWORD>@<HOST>:<PORT>/<SERVICE_NAME>

@/opt/oracle/product/21c/dbhomeXE/demo/schema/human_resources/hr_main.sql

Hradmin1 #prompt 1 is password that you want to use for new user HR.

users #prompt 2 is tablespace name

temp #prompt 3 is temporary tablespace name

Oraadmin1 #prompt 4 is password for Sysdba user

@ORACLE_HOME/demo/schema/log #prompt 5 is path where logs can be stored

localhost:1521/XEPDB1 #prompt 6 is connection string.

@/opt/oracle/product/21c/dbhomeXE/demo/schema/human_resources/hr_cre.sql

@/opt/oracle/product/21c/dbhomeXE/demo/schema/human_resources/hr_popul.sql

@/opt/oracle/product/21c/dbhomeXE/demo/schema/human_resources/hr_idx.sql

@/opt/oracle/product/21c/dbhomeXE/demo/schema/human_resources/hr_code.sql

@/opt/oracle/product/21c/dbhomeXE/demo/schema/human_resources/hr_comnt.sql

@/opt/oracle/product/21c/dbhomeXE/demo/schema/human_resources/hr_analz.sql

Connect db using sql plus or any other application to execute SQL statements.

sqlplus HR

Enter password:Hradmin1@localhost:1521/XEPDB1

CREATE TABLE student( id INT, name VARCHAR(20));

INSERT INTO student(id, name) VALUES(1, 'Rohit');

INSERT INTO student(id, name) VALUES(2, 'ABC');

INSERT INTO student(id, name) VALUES(3, 'DEF');

SELECT * FROM student

Google Sites

Report abuse