Category Archives: 12c

12c, linux, Oracle

Part 10 – Building Oracle 12c development suite – Preparing Linux OS for 12c installation

Leave a comment

Preparing Linux OS for 12c installation
Oracle Corporation provides the ability for developers to create a complete development suite on their personal windows desktop or laptop without requiring the expensive equipment required for a true production system. This article outlines the process for configuration of Oracle Enterprise Linux 6.5 installation in preparation for installation of Oracle 12c RDBMS.
NOTE: This process can be used for most Linux installations, regardless of original company or organization providing the base Linux OS.

1. Logon to your Windows desktop/laptop as an administrator and open Oracle VirtualBox.
2. Logon to your Linux Server as the root user and create the group “dba” and user account “oracle” to be your oracle software owner with the following commands:
groupadd dba
useradd oracle -g dba -G root
passwd oracle

3. Create the following directory structures to support your oracle software installation with the commands:
mkdir /opt/app
mkdir /opt/app/oracle
mkdir /opt/app/oracle_software
mkdir /opt/app/oracle_inventory
mkdir /opt/app/oracle_inventory/inventory

4. Change ownership of directory structures to the oracle software owner with the following commands:
chown oracle:dba /opt/app
chown oracle:dba /opt/app/oracle
chown oracle:dba /opt/app/oracle_software
chown oracle:dba /opt/app/oracle_inventory
chown oracle:dba /opt/app/oracle_inventory/inventory

5. Change the ownership of the Oracle RDBMS media files downloaded in Part 9 of this serious to the oracle software owner with the following commands:
chown oracle:dba /opt/app/oracle_software
chown oracle:dba /opt/app/oracle_software/*
chmod 777 /opt/app/oracle_software/*

6. Switch to the oracle software owners account and change directory back to /opt/app/oracle_software location.
[root@oel65 oracle_software]# su – oracle
[oracle@oel65 ~]$ cd /opt/app/oracle_software
[oracle@oel65 oracle_software]$

7. Unpack all the mediate files with the following command: “find . -name “*.zip” -exec unzip {} \;”
[oracle@oel65 oracle_software]$ find . -name “*.zip” -exec unzip {} \;
…
..
inflating: database/response/netca.rsp
inflating: database/response/dbca.rsp
inflating: database/response/db_install.rsp
[oracle@oel65 oracle_software]$

8. This completes the setup of the VM Enterprise Linux for Oracle 12c RDBMS installation.

Larry Catt, OCP

12c, linux, Oracle

Part 9 – Building Oracle 12c development suite – Media packages required for installation of Oracle RDBMS 12c

Leave a comment

Part 9 – Building Oracle 12c development suite.
Media packages required for installation of Oracle RDBMS 12c

Oracle Corporation provides the ability for developers to create a complete development suite on their personal windows desktop or laptop without requiring the expensive equipment required for a true production system. This article outlines the process of downloading the media required for installation of Oracle 12c RDBMS in a RHEL 6.5 environment.
NOTE: This process can be used for most Linux installations, regardless of original company or organization providing the base Linux OS.

1. Logon to your Windows desktop/laptop as an administrator and open Oracle VirtualBox.
2. Open an internet browser and download the following files from www.oracle.com and place them in the directory /opt/app/oracle_software.

p17694377_121020_Linux-x86-64_1of8.zip
p17694377_121020_Linux-x86-64_2of8.zip
p17694377_121020_Linux-x86-64_3of8.zip
p17694377_121020_Linux-x86-64_4of8.zip
p17694377_121020_Linux-x86-64_5of8.zip
p17694377_121020_Linux-x86-64_6of8.zip
p17694377_121020_Linux-x86-64_7of8.zip
p17694377_121020_Linux-x86-64_8of8.zip

3. Change directory to /opt/app/oracle_software with the command “cd /opt/app/oracle_software”

[root@OEL65 ~]# cd /opt/app/oracle_software/
[root@OEL65 oracle_software]#

4. Extract the zip files with the following command: “find . -name “*.zip” -exec unzip {} \;”

[root@OEL65 oracle_software]# find . -name “*.zip” -exec unzip {} \;
[root@OEL65 oracle_software]#

5. NOTE: This is a base version of the software and requires additional patches to bring it to current date.
6. This completes the setup of the VM network.

Larry Catt, OCP

12c, linux, Oracle

Part 8 – Building Oracle 12c development suite – Library setup for Oracle Enterprise Linux 6.5 for Oracle RDBMS 12c

Leave a comment

Library setup for Oracle Enterprise Linux 6.5 for Oracle RDBMS 12c

Oracle Corporation provides the ability for developers to create a complete development suite on their personal windows desktop or laptop without requiring the expensive equipment required for a true production system. This article outlines the process for configuration libraries for an Oracle Enterprise Linux 6.5 installation in preparation for installation of Oracle 12c RDBMS.
NOTE: This process can be used for most Linux installations, regardless of original company or organization providing the base Linux OS.

1. Logon to your Windows desktop/laptop as an administrator and open Oracle VirtualBox.
2. Logon to your Linux VM as the root user.
3. Change directory to your yum repository with command: “cd /etc/yum.repos.d”

[root@oel65 ~]# cd /etc/yum.repos.d
[root@oel65 yum.repos.d]#

4. Extract the repository from the oracle public yum website with the command: “wget http://public-yum.oracle.com/public-yum-ol6.repo”

[root@OEL65 yum.repos.d]# wget http://public-yum.oracle.com/public-yum-ol6.repo
–2015-04-12 18:49:53– http://public-yum.oracle.com/public-yum-ol6.repo
Resolving public-yum.oracle.com… 70.186.29.33, 70.186.29.58
Connecting to public-yum.oracle.com|70.186.29.33|:80… connected.
HTTP request sent, awaiting response… 200 OK
Length: 5046 (4.9K) [text/plain]
Saving to: “public-yum-ol6.repo.1”

100%[======================================>] 5,046 –.-K/s in 0s

2015-04-12 18:49:53 (225 MB/s) – “public-yum-ol6.repo.1” saved [5046/5046]

[root@OEL65 yum.repos.d]#

5. Edit the yum repo file with appropriate values with the following perl command: “perl -pi.bak -e ‘s/enabled=0/enabled=1/g’ /etc/yum.repos.d/public-yum-ol6.repo”

[root@OEL65 yum.repos.d]# perl -pi.bak -e ‘s/enabled=0/enabled=1/g’ /etc/yum.repos.d/public-yum-ol6.repo
[root@OEL65 yum.repos.d]#

6. Download necessary RPM file from oracle with command: “wget –no-check-certificate https://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6 -O /etc/pki/rpm-gpg/RPM-GPG-KEY-oracle”

[root@OEL65 yum.repos.d]# wget –no-check-certificate https://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6 -O /etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
–2015-04-12 18:50:29– https://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6
Resolving public-yum.oracle.com… 70.186.29.58, 70.186.29.33
Connecting to public-yum.oracle.com|70.186.29.58|:443… connected.
WARNING: no certificate subject alternative name matches
requested host name “public-yum.oracle.com”.
HTTP request sent, awaiting response… 200 OK
Length: 1011 [text/plain]
Saving to: “/etc/pki/rpm-gpg/RPM-GPG-KEY-oracle”

100%[======================================>] 1,011 –.-K/s in 0s

2015-04-12 18:50:30 (16.8 MB/s) – “/etc/pki/rpm-gpg/RPM-GPG-KEY-oracle” saved [1011/1011]

[root@OEL65 yum.repos.d]#

7. Use the following yum command to install the packages from oracle for 12c RDBMS install: “yum install oracle-rdbms-server-12cR1-preinstall”

[root@OEL65 yum.repos.d]# yum install oracle-rdbms-server-12cR1-preinstall
Loaded plugins: rhnplugin, security
This system is receiving updates from ULN.
Setting up Install Process
…
…
…
public_ol6_MySQL | 1.2 kB 00:00
libdmx.x86_64 0:1.1.3-3.el6 libstdc++-devel.x86_64 0:4.4.7-11.el6
xorg-x11-utils.x86_64 0:7.5-6.el6 xorg-x11-xauth.x86_64 1:1.0.2-7.1.el6

Complete!

8. This completes the setup of the VM libraries for Oracle 12c RDBMS.

Larry Catt, OCP

12c

Oracle 12c – Valid Time Temporal Setup

Leave a comment

 

Oracle 12c has added a feature called valid time temporal which determines when a table row is valid.  This is done by defining the table with two hidden columns giving a start and stop top for data validity.   This article describes the setup of valid time temporal and its use in Oracle database.

 

  1. Logon to your oracle server as oracle software owner and logon to sqlplus as sysdba.

 

[root@linux2 ~]# su – oracle

Last login: Wed Mar 14 06:50:11 EST 2015 on pts/1

Enter database to use:

1 – ORCL

2 – CDB1

Option >

1

[oracle@linux2 ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 15 07:50:07 2015

 

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

 

SQL>

 

  1. Valid-time periods can be set during table creation or after with ‘ALTER TABLE’. In this example we will create a table test.test_time with the following statement:   NOTE: using the ‘PERIOD FOR’ clause to implicitly defined columns for valid time temporal.

 

CREATE TABLE test.test_times (pers_id NUMBER PRIMARY KEY,

 first_name VARCHAR2( 20),

last_name VARCHAR2( 25),

PERIOD FOR probation);

 

  1. When the table is described only the following columns are displayed.

 

DESCRIBE probationary_emps

Name                    Null?                     Type

————–         ———                                 ————–

EMP_ID                NOT NULL           NUMBER

FIRST_NAME                                     VARCHAR2( 20)

LAST_NAME                                       VARCHAR2( 25)

 

  1. Quering the USER_TAB_COLS shows additional hidden columns

 

column column_name format a15

column data_type format a30

column hidden format a8

set pagesize 500

SELECT column_name,

data_type,

 hidden_column AS HIDDEN FROM dba_tab_cols

WHERE table_name = ‘TEST_TIMES’;

 

 

SQL> column column_name format a15

column data_type format a30

column hidden format a8

set pagesize 500

SELECT column_name,

data_type,

 hidden_column AS HIDDEN FROM dba_tab_cols

WHERE table_name = ‘TEST_TIMES’;

SQL> SQL> SQL> SQL>   2    3    4

COLUMN_NAME     DATA_TYPE                      HIDDEN

————— —————————— ——–

LAST_NAME       VARCHAR2                       NO

FIRST_NAME      VARCHAR2                       NO

PERS_ID         NUMBER                         NO

PROBATION       NUMBER                         YES

PROBATION_END   TIMESTAMP(6) WITH TIME ZONE    YES

PROBATION_START TIMESTAMP(6) WITH TIME ZONE    YES

 

6 rows selected.

 

SQL>

 

 

  1. insert the following records into new table:

insert into test.test_times(pers_id, first_name, last_name, probation_start, probation_end)

values(1, ‘John’, ‘Doe’, to_date(’01-JAN-16′,’DD-MON-YY’),to_date(’01-APR-16′,’DD-MON-YY’));

insert into test.test_times(pers_id, first_name, last_name, probation_start, probation_end)

values(2, ‘Jan’, ‘Doe’, to_date(’01-May-16′,’DD-MON-YY’),to_date(’01-SEP-16′,’DD-MON-YY’));

insert into test.test_times(pers_id, first_name, last_name, probation_start, probation_end)

values(3, ‘Bill’, ‘Jones’, to_date(’01-OCT-16′,’DD-MON-YY’),to_date(’01-JAN-17′,’DD-MON-YY’));

 

commit;

 

  1. Now select all records in the table test_times.

 

SQL> select * from test.test_times;

 

   PERS_ID FIRST_NAME           LAST_NAME

———- ——————– ————————-

         1 John                 Doe

         2 Jan                  Doe

         3 Bill                 Jones

 

SQL>

 

  1. Now perform the same select but limiting results by hidden column of PROBATION_END

 

SQL> select * from test.test_times where probation_end<to_date(’01-NOV-16′,’DD-MON-YY’);

 

   PERS_ID FIRST_NAME           LAST_NAME

———- ——————– ————————-

         1 John                 Doe

         2 Jan                  Doe

 

SQL>

 

  1. This completes the setup and testing of Valid Time Temporal in Oracle 12c.

 

Larry Catt

OCP

12c, SQLLoader

Oracle 12c – SQL*Loader

Leave a comment

Oracle provides the utility SQL*loader for loading data from non-Oracle databases.  SQL*Loader is very flexible and can read a large range of flat files.  SQL*Loader can perform the following functions:

 

  1. Load data across network if files on different machine.
  2. Load data from multiple files in same session.
  3. Load data into multiple tables in same session.
  4. Specify character set.
  5. Selectively load data by values.
  6. Manipulate data before loading.
  7. Generate unique sequential keys.
  8. Use OS to access data files.
  9. Load data from disk, tape or named pipe.
  10. Load arbitrarily complex object-related data.
  11. Use secondary data files for loading LOBs and collections.
  12. Use conventional, direct path or external table loads.

 

SQL*Loader can be used with or without control file and you may encounter the following files during use:

  • Control – Defines data format and control behaviors.
  • Data – One or more data files containing info to be loaded.
  • Log – Logs performance and errors.
  • Bad – Contains all records not loaded because of errors.
  • Discard – Contains all records discarded by control file identified bypass.

 

SQL*Loader is invoked with the command SQLLDR.  You use parameters in the PARFILE or at the command line to control actions of SQLLDR.  If parameter appears in PARFILE and command line, command line is used.  Three sections of control files: PARFILE.

 

  1. Session wide info: global options, input data file location, data to be loaded.
  2. Contains INTO TABLE blocks (one or more)
  3. Optional third section can contain input data.

 

SQL*Loader can use two methods for loading data: Conventional Path and Direct Load option.  In Conventional Path SQL*Loader creates INSERT statement for data to be loaded and passes it to SQL Parser.   In Direct Load Path SQL*Loader bypasses the SQL and parser and loads data straight into the target table.  Direct Path is much faster than Conventional path, but conventional path is more flexible.

There are a few restrictions of Direct Path method:

 

  1. Cannot run concurrently with other transactions against a target table.
  2. Triggers on table do not fire.
  3. Data is written above high water mark, even if space is available below.
  4. Clustered tables are not supported.
  5. Foreign Key constraints are disabled during load.

 

 

Larry Catt

OCP

12c

Part 7 – Building Oracle 12c development suite – Storage setup for Oracle Enterprise Linux 6.5 on Oracle VirtualBox

Leave a comment

Storage setup for Oracle Enterprise Linux 6.5 on Oracle VirtualBox
Oracle Corporation provides the ability for developers to create a complete development suite on their personal windows desktop or laptop without requiring the expensive equipment required for a true production system. This article outlines the process for configuration storage for an Oracle Enterprise Linux 6.5 installation in preparation for installation of Oracle 12c RDBMS.
NOTE: This process can be used for most Linux installations, regardless of original company or organization providing the base Linux OS.
1. Logon to your Windows desktop/laptop as an administrator and open Oracle VirtualBox.
2. Logon to your server as root user and create the oracle software owner with the following commands and enter your desired password for the oracle software owner:
login as: root
root@10.30.15.127’s password:
Last login: Mon Sun 1 21:57:11 2015 from 10.30.15.64
[root@oel65 ~]# groupadd dba
[root@oel65 ~]# useradd oracle -g dba -G root
[root@oel65 ~]# passwd oracle
Changing password for user oracle.
New password:
BAD PASSWORD: it is based on a dictionary word
Retype new password:
passwd: all authentication tokens updated successfully.
[root@oel65 ~]#create the following directories for the oracle RDBMS storage.

3. Execute the following mkdir statements to create the appropriate storage of oracle RDBMS installation.
mkdir /opt/app
mkdir /opt/app/oracle
mkdir /opt/app/oracle_software
mkdir /u01
mkdir /u02
mkdir /u01/oradata
mkdir /u02/oradata
mkdir /opt/app/oracle_inventory
mkdir /opt/app/oracle_inventory/inventory

4. Finally change the ownership and permissions on the new storage location to the oracle software owner’s account.
chown oracle:dba /opt/app
chown oracle:dba /opt/app/oracle
chown oracle:dba /opt/app/oracle_software
chown oracle:dba /u01
chown oracle:dba /u02
chown oracle:dba /u01/oradata
chown oracle:dba /u02/oradata
chown oracle:dba /opt/app/oracle_inventory
chown oracle:dba /opt/app/oracle_inventory/inventory

5. This completes the setup of the storage for Oracle 12c.

Larry Catt, OCP

12c, linux, VM

Part 6 – Building Oracle 12c development suite – Security setup for Oracle Enterprise Linux 6.5 on Oracle VirtualBox

Leave a comment

Security setup for Oracle Enterprise Linux 6.5 on Oracle VirtualBox

Oracle Corporation provides the ability for developers to create a complete development suite on their personal windows desktop or laptop without requiring the expensive equipment for a true production system. This article outlines the process for configuration security for an Oracle Enterprise Linux 6.5 installation in preparation for installation of Oracle 12c RDBMS.
NOTE: This process can be used for most Linux installations, regardless of original company or organization providing the base Linux OS.

The two key security requirements to temporarily remove during installation of most software suites is the firewall and linux se feature.

To disable the Linux firewall perform the following:
1. Logon to the linux VM as the root user and execute the command: “chkconfig iptables off”

login as: root
root@10.30.15.127’s password:

[root@oel65 ~]# chkconfig iptables off
[root@oel65 ~]#
To remove the Linux SE feature, perform the following:

2. Logon to your linux VM as the root user and execute the command: “perl -pi.bak -e ‘s/SELINUX=enforcing/SELINUX=disabled/g’ /etc/selinux/config”.

login as: root
root@10.30.15.127’s password:
[root@oel65 ~]# perl -pi.bak -e ‘s/SELINUX=enforcing/SELINUX=disabled/g’ /etc/selinux/config
[root@oel65 ~]#

3. Issue the command “reboot” for the the changes to take affect
[root@oel65 ~]# reboot
Broadcast message from root@oel65
(/dev/pts/0) at 22:02 …
The system is going down for reboot NOW!
[root@oel65 ~]#

4. This completes the setup of the linux security for Oracle installation.

Larry Catt, OCP

12c, linux, VM

Part 3 – Building Oracle 12c development suite – Configuration for Oracle Enterprise Linux install on VirtualBox.

Leave a comment

Configuration for Oracle Enterprise Linux install on VirtualBox.
Oracle Corporation provides the ability for developers to create a complete development suite on their personal windows desktop or laptop without requiring the expensive equipment required for a true production system. This article outlines the process of setting up a VirtualBox for a guest Linux OS.

NOTE: This instructions can be used for installing any type of guest OS, just acquire the appropriate ISO or CD for the guest OS you wish to install.
Required files:
ISO V41362-01 – Oracle Enterprise Linux 6.5
1. Logon to your Windows desktop/laptop as an administrator and open Oracle Virtual Box.
2. Select the New button on the tool bar or select Machine ? New from drop down menu.

s
3. Enter the Name, Type and Version of your guest OS and select Next.

4. Select the amount of memory you would like to provide to your Guest OS, I would recommend a minimum of 2048M or ¼ of your system memory. Then select the Next button.

5. Under Create Virtual Machine page, add hard drive via the Create a virtual hard drive now selection and select the Create button.

6. At the page Hard drive file type, select VDI (VirtualBox Disk Image) and press the Next button.

7. At the Storage on Physical hard drive button, select Dynamically allocated button and press the Next button.

8. At the File location and size page, I would recommend a minimum of 50GB and press the Create button.

9. The base configuration of your Guest OS is now create, but we still have to load your guest OS ISO or CD for installation of the guest OS. To do this ensure that your new guest OS is highlighted in the left panel of your VirtualBox Control Panel and select the Settings button from the tool bar.

10. At the Settings page, select the storage option from the left panel and select the plus symbol over the desks next to Controller: IDE.

11. At the VirtualBox – Question select the Choose disk button.

12. Navigate to the directory where you are storing your guest OS ISO or DVD and select the file or device, then press Open.

13. The ISO or DVD will now show up in the Controller: IDE panel of Storage Tree, select the OK button to save your changes.

14. This completes the process of setting up your VirtualBox Console for a new Guest OS installation.

Larry Catt

12c, linux, Oracle, VM

Part 2 – Building Oracle 12c development suite – Installation of Oracle VirtualBox

Leave a comment

Installation of Oracle VirtualBox
Oracle Corporation provides the ability for developers to create a complete development suite on their personal windows desktop or laptop without requiring the expensive equipment required for a true production system. This article outlines the process of installing Oracle VirtualBox for installation of guest Operating Systems on your windows desktop or laptop.

NOTE: This suite can be installed on any type of OS, just download the appropriate VirtualBox software package for your base system.
Required files:
VirtualBox-4.3.20-96997-Win.exe
or
The latest version available at www.oracle.com
1. Logon to your windows desktop or laptop as an administrator.
2. Double click on the VirtualBox executable.
3. Click on Next button at the Welcome page.

4. Click on the Next button at the Custom Setup page.

5. Click on the Next button at the Custom Setup Options page.

6. Click on the Next button at the Warning Network Interfaces page.

7. Click on the Install button at the Ready to Install page.

8. After Installation completes, click the Finish button.

9. The administration console will open after installation is completed.

Larry Catt