This article is a summary of the procedure for introducing an Oracle environment as a personal development (study) environment. The reason I decided to write an article was because I couldn't find the latest article anywhere and I was in great trouble. I don't know if there is demand, but for my comrades in a similar situation and for myself in the future. I think there are many people who say "I want to do the cloud!" And "I want to do the web designer!", But there may be scenes that are necessary for business (like me). The structure of the article is as follows. If you are not interested in 1, please skip it.
The reason for Linux is that this time we will use Database from C language using Pro * C. I chose Oracle Linux this time because it is (personally) easier to build Linux than to introduce a C language compilation environment on Windows. See below for why we chose Oracle Linux.
-Reference: I would like to install Oracle on Linux for study purposes. I'm at a loss on CentOS or Ubuntu. .. .. --Here, there are Debian and RHEL as the genealogy of Linux, and it is stated that it is better to build on the RHEL series. -Reference: Oracle Linux Source: Free Encyclopedia "Wikipedia" --Here it is stated that Oracle Linux is a RHEL family.
Also, regarding this article, we will use the good articles of our predecessors to build the environment, so please understand that ~~ basics will be skipped ~~ The parts not mentioned in the link are added in this article.
The final target environment is as follows.
Windows OS (Each individual version)
┗ VirtualBox (6.0.6)
┗ Oracle Linux (7.5.0.0.0)
┗ C language execution environment(gcc compilation environment)
┗ Oracle Database (12c Release 2)
There has been a lot of talk about Oracle these days, including Oracle Java, but it is still available free of charge for personal use of Oracle DB. I'm not very familiar with that area, so here are some references.
-Reference: What should I do after January 2019 regarding the Java payment issue and Java 8 end-of-support issue -Reference: I will explain what everyone who uses Java in 2019 should understand -Reference: Oracle Java paid. A survey report on the current situation and future measures of companies.
The above article doesn't mention Oracle DB, but it seems like it will be a subscription charge even for personal use, such as selling on-premise packaged products and focusing on the cloud.
Basically, build by referring to "Reference: Install Oracle Linux 7.5 with VirtualBox (only memo)".
--Access Oracle --Create an Oracle user. Enter your address, email address, etc., but it doesn't matter whether you are an individual or a company. --When you log in with the created user ID, there is a search box, so enter "Oracle Linux 7.5.0.0.0" and press the "Search" button. --If you press "DLP: Oracle Linux 7.5.0.0.0 (Oracle Linux)", it will be added to the cart. --Press "Checkout" on the upper right --If you can confirm that "Oracle Linux 7.5.0.0.0" is included in Selected Software of the cart, set Platforms / Languages to "x86 64bit" and press the "Continue" button. --If the text "I accept the terms in the license agreement" appears, check "I accept the terms in the license agreement" and press the "Continue" button. --Uncheck all except "V975367-01.iso Oracle Linux Release 7 Update 5 for x86 (64 bit), 4.1GB" and click the "Download" button to download. -"Reference: A story about developing with docker even though it is a Windows 7 environment [(1) Environment setting]" "1. Installing VirtualBox" Install Viertualbox for reference --Mount the image on Oracle Linux --Start VM VirtualBox. --Click "New (N)" --If you enter a name like "Oracle" (such as Oracle VM or Oracle Test) in the name, the OS will become Oracle without permission, so next --Set the memory size to an arbitrary value (it will be crisp if there is 4G or more), and then next --Select "Create a virtual hard disk" in the hard disk settings and create it in any size (I want about 40GB because it contains a database) --When creating a virtual hard disk, keep all the defaults and go to the next (variable size / fixed size can be used) --Proceed with the installation according to "Oracle Linux" in "Reference: Install Oracle Linux 7.5 with VirtualBox (only memo)"
--If you cannot input Japanese well, refer to the following article "Reference: How to input Japanese on the desktop of Oracle Linux 7.4 .html) "
--When using in a proxy environment
--Applications → System Tools → Settings → Network
--Open the manual setting screen
--HTTP proxy-Enter the "proxy.co.jp" part of the proxy server " http://proxy.co.jp "in the Socks host.
--Although the port numbers vary from company to company, 80, 8080, 1080, 3128, etc. are often used.
From here, build the environment on Oracle Linux built in 2. Basically, build by referring to "Reference: Install Oracle 12c R2 on Oracle Linux 7.5".
--Run Oracle Preinstall RPM
--Oracle Preinstall RPM is software that makes it easier to install Oracle DB (Reference: What is Oracle Preinstall RPM doing?)
- $ yum install -y oracle-rdbms-server-12cR1-preinstall
- $ export LANG=C
- $ oracle-rdbms-server-12cR1-preinstall-verify
--Create a directory for installing Oracle DB (so-called Oracle home directory)
- $ mkdir -p /u01/app/oraInventory
- $ mkdir -p /u01/app/oracle
- $ mkdir /home/oracle/tmp
--Set owner and access rights to the directory with $ chmod or $ chown as needed. This time, we will do everything with root and full access authority, so the following settings
- $ chown -R root:root /u01/app/oracle
- $ chown -R root:root /u01/app/oraInventory
- $ chmod -R 775 /u01/app/oracle
- $ chmod -R 775 /u01/app/oraInventory
--Execute $ ls -l / u01 / app / to check each permission
drwxrwxrwx.5 root root 89 April 24 18:21 oraInventory
drwxrwxrwx.10 root root 4096 April 24 18:29 oracle
-Access Oracle
--Press "See All" on Linux x86-64 of Oracle Database 12c Release 2 (commonly known as OracleDB 12cR2)
--If you are asked to agree to the license, check "I accept the license" at the top of the page.
--Click "linuxx64_12201_database.zip" to download
--Unzip the downloaded "linuxx64_12201_database.zip" and install it.
--Run $ unzip linuxx64_12201_database.zip under the download folder
--Since there is runInstaller under the database folder you answered, execute $ ./runInstaller under the database folder (the same execution method as executing a normal exe file)
--Proceed with the installation according to "Oracle Installation" in "Reference: Installing Oracle 12cR2 on Oracle Linux 7.5"
--Add proxy = http: //proxy.xxx.xxx:pp to the bottom of the /etc/yum.conf file for yum proxy breakthrough.
--If a warning is issued with the swap size, execute the following command
-Reference: If a swap size warning occurs during the installation of Oracle Database 12c
- $ dd if=/dev/zero of=/var/swpfile bs=1M count=2048
- $ mkswap /var/swpfile
- $ swapon /var/swpfile
- $ free -m
--If you get a message prompting the fix script during installation, move to the root user and execute the fix script.
- $ su -
--$ cd / tmp / CVU_XXXXXXXX ← Described in the warning message
- $ ./runfixup.sh
From here, create a database on Oracle 12c R2 installed in 3. Basically, build it by referring to the database creation and later of "Reference: Install Oracle 12c R2 on Oracle Linux 7.5".
--Add the following to .bash_profile to modify the environment variables (usually exists directly under the login user folder)
export TMPDIR=$HOME/tmp
export TEMP=$HOME/tmp
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/jdk/bin:${PATH}:PATH=$ORACLE_HOME/sqldeveloper/sqldeveloper/bin:
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export NLS_LANG=JAPANESE_JAPAN.UTF8
export LANG=ja_JP.UTF-8
export ORACLE_SID=testdb
--Reflect the environment variable settings
$ source .bash_profile
--$ sudo reboot Reboot just in case--Execute $ dbca to start the database creation screen with GUI
--Create a database according to "Create Database" in "Reference: Install Oracle 12c R2 on Oracle Linux 7.5"
--Start sqlplus and start the database
--Run as system user with $ sqlplus / as sysdba
--The result of SQL> show user should be SYS
--The database starts with SQL> startup
From the following, do everything while logged in to sqlplus with SYS
-Create user
SQL> create user username identified by password;
・ Creating a tablespace
SQL> create tablespace TEST_TBS
datafile '/u01/app/oracle/oradata/testdb/test01.dbf'
size 2G
autoextend off;
-Creating a temporary tablespace
SQL> create temporary tablespace TEST_TMP
tempfile '/u01/app/oracle/oradata/testdb/testtmp01.dbf'
size 2G
autoextend off;
-Setting user authority (assign user authority to the created tablespace)
SQL> alter user username
default tablespace TEST_TBS
temporary tablespace TEST_TMP;
-Setting user authority (adding connection authority and CRUD authority)
SQL> grant connect, resource to username
· Unlimited tablespace allocation
SQL> alter user username quota unlimited on TEST_TBS ;
-Check if the created user can access it
OK if the following SQL can be executed
SQL> connect username/password
SQL> show user
USERNAME
--Rewrite listener.ora as follows to make it accessible from external applications (HOST name is the name decided at the time of installation of Oracle Linux)
$ cat /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
/u01/app/oracle/product/12.2.0/dbhome_1/network/admin
listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)
(SID_NAME = testdb)
(SERVICE_NAME = testdb)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle12test)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
Launch a listener
$ lsnrctl start
--This time, access from the client is also performed from the same OS, so transnames.ora is also rewritten as follows (HOST name is omitted below)
LISTENER_TESTDB =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle12test)(PORT = 1521))
TESTDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle12test)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdb)
)
)
Listener.ora is a network setting file placed on the server side (terminal where Oracle DB is installed). Set which database to send the communication received by the server to
Transnames.ora is a network setting file placed on the client side. Set which port of which server to connect with what protocol
Reference: Oracle a little reference
I created the source code by referring to "Reference: I'm a little scared because it's past the bottom".
First, log in to sqlplus as the user created in 5.
$ sqlplus / as sysdba
SQL> connect username/password
SQL> show user
USERNAME
Create a table while logged in to sqlplus.
SQL> CREATE TABLE username.MEMBER
(CODE varchar2 (10) NOT NULL,
NAME varchar2 (10) NOT NULL,
UPDATE_YMD varchar2 (8) NULL)
SQL> ALTER TABLE username.MEMBER ADD PRIMARY KEY
(CODE,
NAME);
SQL> commit;
Insert sample data while logged in to sqlplus.
insert into username.MEMBER(
CODE,
NAME,
UPDATE_YMD
)
values
(
'0000000000',
'aaaaaaaaaa',
'20190101'
);
insert into username.MEMBER(
CODE,
NAME,
UPDATE_YMD
)
values
(
'1111111111',
'bbbbbbbbbb',
'20190101'
);
commit;
Log out of sqlplus.
The following source code is described.
test_p.pc
#include "stdio.h"
#include "stdlib.h"
#include "string.h"
/*SQL access constants (hard-coded for testing)*/
#define ORAUSER "username"
#define ORAPASSWD "password"
#define ORASYSID_T "testdb"
/*Max count macro definition at fetch*/
#define MAX 100
/*SQL access variable (so-called host variable) declaration part*/
/* "h_"The starting variable is used for select*/
EXEC SQL BEGIN DECLARE SECTION;
varchar username[20];
varchar password[20];
varchar systemid[20];
char h_code[MAX][10];
char h_name[MAX][10];
EXEC SQL END DECLARE SECTION;
/*ProC precompiler header file declaration*/
EXEC SQL INCLUDE sqlca.h;
/******************************/
/*Main function from here*/
/*Flow in the main function*/
/* 1.Variable definition (global definition for host variables)*/
/* 2.Value initialization*/
/* 3.DB connect*/
/* 4.Issuance of select statement*/
/* 5.Cursor open*/
/* 6.Fetch execution*/
/* 7.Cursor close*/
/* 8.DB disconnection (commit)*/
/******************************/
int main(int argc, char *argv[])
{
/*Variable for cursor loop at fetch time*/
int i;
/*Host variable initialization*/
memset(&username, 0x00, sizeof(username));
memset(&password, 0x00, sizeof(password));
memset(&systemid, 0x00, sizeof(systemid));
memset(&h_code, 0x00, sizeof(h_code));
memset(&h_name, 0x00, sizeof(h_name));
/*Connection to oracle (set constants in variables for SQL access)*/
strcpy(username.arr, ORAUSER);
username.len = strlen(username.arr);
strcpy(password.arr, ORAPASSWD);
password.len = strlen(password.arr);
strcpy(systemid.arr, ORASYSID_T);
systemid.len = strlen(systemid.arr);
/*Exception declaration → jump to errorpt*/
EXEC SQL WHENEVER SQLERROR GOTO errorpt;
printf("user: %s, password: %s Start connection\n",
username.arr,password.arr);
/*Connection*/
EXEC SQL CONNECT :username
IDENTIFIED BY :password
AT :systemid;
printf("%s Connection was successful\n",systemid.arr);
printf("system: %s Start search\n", systemid.arr);
/*Cursor declaration (sql issuance in case of select is here)*/
EXEC SQL AT :systemid DECLARE cursor CURSOR FOR
SELECT CODE, NAME
FROM MEMBER
WHERE UPDATE_YMD > 20190101;
printf("Search completed\n");
printf("Open the cursor\n");
/*Cursor open*/
EXEC SQL OPEN cursor;
printf("Cursor opened\n\n");
/*Data retrieval (so-called fetch)*/
EXEC SQL FETCH cursor INTO
:h_code,:h_name;
/*If there is no value, exit*/
for(i=0;i<MAX;i++){
if(0 == strlen(h_code[i])){
printf("\n");
break;
}else{
printf("%s,%s\n",
h_code[i],h_name[i]);
}
}
printf("Fetch completed\n");
/*Cursor close*/
EXEC SQL CLOSE cursor;
printf("Cursor closed\n");
/*Although it says commit, it also disconnects*/
/*If it is not select, it will be rolled back if you do not write this*/
/*Not required because select disconnects when the cursor is closed*/
/* EXEC SQL COMMIT WORK RELEASE; */
return 0;
/*Processing at the time of error*/
errorpt:
printf("\n\n%-79s \n",sqlca.sqlerrm.sqlerrmc);
EXEC SQL WHENEVER SQLERROR CONTINUE;
}
--First, precompile (convert pc file to c file)
- $ proc iname=test_p oname=test_p.c sqlcheck=full
--By executing, test_p.c is generated
--Iname extension may or may not be written
--If oname is not written, it will have the same name as input (extension is "c").
--Perform static parsing at this point with sqlcheck = full option
--Create an object file
- $ gcc -c test_p.c -I $ORACLE_HOME/precomp/public
--By executing, test_p.o is generated
--The precomp / public folder exists under the environment variable "$ ORACLE_HOME" set in 4 and the sqlca header file for pro * c exists.
-"-I" option is an option to add a header
--Create executable file
- $ gcc -o test_p test_p.o -L$ORACLE_HOME/lib -lclntsh
--By executing, test_p is generated
--The lib folder exists under the environment variable "$ ORACLE_HOME" set in 4 and the libraries for pro * c exist.
-"-L" option is an option to add a library
--Since it is an executable file, execute it with $ ./test_p
Execution result
$ ./test_p
user: username, password:password Start connection
testdb connection was successful
system:start testdb search
Search completed
Open the cursor
Cursor opened
0000000000,aaaaaaaaaa
1111111111,bbbbbbbbbb
Fetch completed
Cursor closed
--An error occurred that the library cannot be referenced when compiling the pc
At the time of compiling the pc, the following error occurs that multiple libraries cannot be called
An error occurred at row 0 and column 0. File test_p.pc
PCC-F-02102,A fatal error occurred while executing the C preprocessor process.
An error occurred in row 33, column 11. File/usr/include/stdio.h
33 # include <stddef.h>
33 ..........1
33 PCC-S-02015,Unable to open insert file.
An error occurred in row 15 and column 10. File/usr/include/_G_config.h
15 #include <stddef.h>
15 .........1
15 PCC-S-02015,Unable to open insert file.
An error occurred in row 51, column 11. File/usr/include/wchar.h
51 # include <stddef.h>
51 ..........1
51 PCC-S-02015,Unable to open insert file.
An error occurred at row 50 and column 10. File/usr/include/libio.h
50 #include <stdarg.h>
50 .........1
50 PCC-S-02015,Unable to open insert file.
An error occurred in row 307, column 3. File/usr/include/libio.h
307 size_t __pad5;
307 ..1
307 PCC-S-02201,symbol"size_t"I found. When one of the following enters:
Depending on the environment, the location of each header file may not be under / usr / include / as per the error message. Therefore, it is necessary to add a link to the configuration file pcscfg.cfg.
--Search configuration file $ find / -name pcscfg.cfg
- /u01/app/oracle/product/12.2.0/dbhome_1/precomp/admin/pcscfg.cfg
--Add the path where the header file actually exists to sys_include (/usr/lib/gcc/x86_64-redhat-linux/4.8.2/include/stddef.h in my case)
pcscfg.cfg
...
sys_include=($ORACLE_HOME/precomp/public,/usr/include,/usr/lib/gcc/x86_64-redhat-linux/4.1.2/include,/usr/lib/gcc/x86_64-redhat-linux/4.4.7/include,/usr/lib/gcc/x86_64-redhat-linux/4.4.4/include,/usr/lib64/gcc/x86_64-suse-linux/4.8/include,/usr/lib/gcc/x86_64-redhat-linux/4.8.2/include/)
...
Reference: Error that stddef.h cannot be found in Pro * C precompilation
Recommended Posts