Oracle database 11g on Oracle Linux 6.10/7.9

The below was carried out on VirtualBox

Install

1) First, we must get the source:: OracleLinux-R6-U10-Server-x86_64-dvd.iso

wget home.d..../oracle/OracleLinux-R7-U9-Server-x86_64-dvd.iso

2) Install the pre installation suite: yum install -y oracle-rdbms-server-11gR2-preinstall

3) Install net-tools and wget

yum install net-tools

yum install wget

4) Download the following files:

wget home.d..../oracle/V17530-01_1of2.zip

wget home.d..../oracle/V17530-01_2of2.zip

5) Unzip the installation files;

unzip V17530-01_1of2.zip

unzip V17530-01_2of2.zip

6) Create DATA directory;

mkdir /var/DATA

chmod 777 /var/DATA

7) Change the file ownership to oracle

chown -R oracle:oinstall ./database

8) Move database directory to /var/DATA

mv database /var/DATA/

9) Change the oracle user password: passwd oracle

10) Run the following:

mkdir -p /u01/app/oracle/product/11.2.0/dbhome_1

chown -R oracle:oinstall /u01

chmod -R 775 /u01

12) Log out and back in as oracle and run the installer:

cd /var/DATA/database

./runInstaller -ignoreSysPrereqs (optional) ./runInstaller -ignoreSysPrereqs

13) Add the following to .bash_profile, the logout and back in or source ~/.bash_profile

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1

export ORACLE_SID=ORCL   # Change ORCL to your database SID if different

export PATH=$PATH:$ORACLE_HOME/bin

14) To check the status do lsnrctl status

15) To connect, do sqlplus / as sysdba

16) to start and stop, do lsnrctl start and lsnrctl stop

Database testing and basics

1) Edit $ORACLE_HOME/network/admin/listener.ora adding host 0.0.0.0 to ensure that it can be reached from another location and then restart the listener: lsnrctl stop then lsnrctl stop

2) Check iptables with iptables -L or iptables -L --line-numbers and if need be, do something like:

iptables -I INPUT 5 -p tcp -m state --state NEW -m tcp --dport 1521 -j ACCEPT

3) Save the rule: service iptables save.

4) Check from remote machine: nc -zv <SERVER_IP> 1521

Import Database

1) Copy and unzip the FULLDB.DMP file to the server, then do:

chown oracle:oinstall /var/DATA/FULLDB.DMP

chmod 666 /var/DATA/FULLDB.DMP

chmod 777 /var/DATA

2) Log in to the Oracle database; sqlplus / as sysdba and run the following to create a directory;

CREATE OR REPLACE DIRECTORY MY_DATA_DIR AS '/var/DATA';

GRANT READ, WRITE ON DIRECTORY MY_DATA_DIR TO system;

GRANT READ, WRITE ON DIRECTORY MY_DATA_DIR TO PUBLIC;

3) Verify the directory exists in the database catalog;

SELECT directory_name, directory_path FROM dba_directories WHERE directory_name = 'MY_DATA_DIR';

4) Import the data as follows:

impdp system/password@orcl DIRECTORY=MY_DATA_DIR DUMPFILE=FULLDB.DMP FULL=Y LOGFILE=fulldb_import.log

Note: password will be the password used when installing the database software, not the oracle user password.

UPDATE: The above (step 4) resulted in user missing errors, so...

4a) The following creates a text sql file from the dump file;

impdp system/password@orcl DIRECTORY=MY_DATA_DIR DUMPFILE=FULLDB.DMP FULL=Y SQLFILE=dump_metadata.sql

4b) The following shows a list of users and table spaces needed by said users;

grep -i "CREATE USER" /var/DATA/dump_metadata.sql

grep -i "CREATE TABLESPACE" /var/DATA/dump_metadata.sql

4c) Create the required table spaces, login using sqlplus / as sysdba

then run the following (based on my working example):

CREATE TABLESPACE "IF_INDEX" DATAFILE '/var/DATA/ordata/orcl/if_index01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

CREATE TABLESPACE "IF_LOB" DATAFILE '/var/DATA/ordata/orcl/if_lob01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

CREATE TABLESPACE "MES_LOB" DATAFILE '/var/DATA/ordata/orcl/mes_lob01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

CREATE TABLESPACE "IFMSCC_DATA" DATAFILE '/var/DATA/ordata/orcl/ifmscc_data01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

CREATE TABLESPACE "BRONER_DATA" DATAFILE '/var/DATA/ordata/orcl/broner_data01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

CREATE TABLESPACE "SESSTATS_DATA" DATAFILE '/var/DATA/ordata/orcl/sesstats_data01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

CREATE TABLESPACE "PPSDEV_INDEX" DATAFILE '/var/DATA/ordata/orcl/ppsdev_index01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

CREATE TABLESPACE "MES_INDEX" DATAFILE '/var/DATA/ordata/orcl/mes_index01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

CREATE TABLESPACE "B21_DATA" DATAFILE '/var/DATA/ordata/orcl/b21_data01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

CREATE TABLESPACE "IF_DATA" DATAFILE '/var/DATA/ordata/orcl/if_data01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

CREATE TABLESPACE "MES_DATA" DATAFILE '/var/DATA/ordata/orcl/mes_data01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

CREATE TABLESPACE "PPS_DATA" DATAFILE '/var/DATA/ordata/orcl/pps_data01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

CREATE TABLESPACE "PPSDEV_DATA" DATAFILE '/var/DATA/ordata/orcl/ppsdev_data01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

CREATE TABLESPACE "APPL_INT" DATAFILE '/var/DATA/ordata/orcl/appl_int01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

CREATE TABLESPACE "CORUS_WSC" DATAFILE '/var/DATA/ordata/orcl/corus_wsc01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

CREATE TABLESPACE "SCD_DATA" DATAFILE '/var/DATA/ordata/orcl/scd_data01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

CREATE TABLESPACE "SECURE_LOB" DATAFILE '/var/DATA/ordata/orcl/secure_lob01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

CREATE TABLESPACE "HANS" DATAFILE '/var/DATA/ordata/orcl/hans01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

CREATE TABLESPACE "BAM_DATA" DATAFILE '/var/DATA/ordata/orcl/bam_data01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

CREATE TABLESPACE "IFMSCC_INDEX" DATAFILE '/var/DATA/ordata/orcl/ifmscc_index01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

CREATE TABLESPACE "IFMSCC_LOB" DATAFILE '/var/DATA/ordata/orcl/ifmscc_lob01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

CREATE TABLESPACE "PPSDEV_LOB" DATAFILE '/var/DATA/ordata/orcl/ppsdev_lob01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

CREATE TABLESPACE "TS_STATSPACK" DATAFILE '/var/DATA/ordata/orcl/ts_statspack01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

EXIT;

NOTE: ALTER USER system IDENTIFIED BY password ACCOUNT UNLOCK;