Allow public connection to Oracle Database

0.pre_install_oracle.md

Environment:

• CentOS 7

Prerequistes:

• Packages:

zip unzip wget bc binutils compat-libcap1 compat-libstdc++-33 dtrace-modules dtrace-modules-headers \
dtrace-modules-provider-headers dtrace-utils elfutils-libelf elfutils-libelf-devel \
fontconfig-devel glibc glibc-devel ksh libaio libaio-devel libdtrace-ctf-devel libXrender \
libXrender-devel libX11 libXau libXi libXtst libgcc librdmacm-devel libstdc++ libstdc++-devel \
libxcb make net-tools nfs-utils python python-configshell python-rtslib python-six \
targetcli smartmontools sysstat unixODBC

• AWS CLI:

curl "https://awscli.amazonaws.com/awscli-exe-linux-x86_64.zip" -o "awscliv2.zip"
unzip awscliv2.zip
sudo ./aws/install

• Oracle setting user:

curl -o oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
sudo yum -y localinstall oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm

Config:

• User config

sudo passwd oracle
sudo usermod -aG wheel oracle

• Uncomment this line with visudo:

%wheel  ALL=(ALL)       NOPASSWD: ALL

• Other configuration:

sudo sed -i "s/^SELINUX=.*/SELINUX=disabled/g" /etc/selinux/config
sudo setenforce Permissive
sudo systemctl stop firewalld
sudo systemctl disable firewalld

sudo dd if=/dev/zero of=/swapfile bs=250M count=30
sudo chmod 600 /swapfile
sudo mkswap /swapfile
sudo swapon /swapfile
sudo swapon -s
echo "\n/swapfile swap swap defaults 0 0" >> /etc/fstab

1.listener_setup.md

Create listener config file

cat >"$ORACLE_HOME/network/admin/listener.ora" <<EOF
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = $ORACLE_SID)
      (ORACLE_HOME = $ORACLE_HOME)
    )
  )
EOF

Create tnsnames service name config file

cat >"$ORACLE_HOME/network/admin/tnsnames.ora" <<EOF
LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))


ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = $ORACLE_SID)
    )
  )
EOF

Stop & start listener

lsnrctl stop && lsnrctl start

Check listener service

lsnrctl service

2.create_mdmuser.md

Create MDM users & tablespace

sqlplus /nolog <<EOF
conn sys/sys as sysdba
CREATE TABLESPACE MDMDAT07 DATAFILE '/u01/app/oracle/oradata/ORCL/MDMDAT07.dbf' SIZE 10G AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED;
CREATE USER MDMADMIN7 IDENTIFIED BY abcd1234;
GRANT UNLIMITED TABLESPACE TO MDMADMIN7;
GRANT ALL PRIVILEGES TO MDMADMIN7;
GRANT SELECT ANY DICTIONARY TO MDMADMIN7;
EOF

Restart oracle instance

sqlplus /nolog <<EOF
conn sys/sys as sysdba
shutdown abort
startup
EOF

readme.md

This configuration tested successfully with Oracle 12.2.0.1

---

Step by step to config:

• Change $ORACLE_HOME with absolute path of oracle home application
• Restart the listener service by running: lsnrctl stop && lsnrctl start

sql-console.md

Login as SysDBA

sqlplus / as sysdba

SELECT log_mode FROM v$database;

SHUTDOWN IMMEDIATE 
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
ARCHIVE LOG LIST

SELECT supplemental_log_data_min FROM v$database;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

GRANT EXECUTE on DBMS_LOGMNR to db_user;
GRANT SELECT on V_$LOGMNR_LOGS to db_user;
GRANT SELECT on V_$LOGMNR_CONTENTS to db_user;
GRANT LOGMINING to db_user;

GRANT SELECT on v_$transportable_platform to db_user;
GRANT CREATE ANY DIRECTORY to db_user;
GRANT EXECUTE on DBMS_FILE_TRANSFER to db_user;
GRANT EXECUTE on DBMS_FILE_GROUP to db_user;