Mô tả cách cài đặt oracle 19c RPM package trên Oracle linux 7.5
Vì sao phải là oracle linux 7.5?
Tại vì oracle linux đã được oracle cài đặt các thành phần phù hợp các gói cài đặt sẵn. Nếu các bạn không thích các bạn có thể tự cài các gói này. Và hiển nhiên chúng ta không cài được bản oracle mới nhất.
Mình sẽ hướng dẫn các bạn trước tiên cài đặt với Oracle Linux 7.5 trước, sau đó mình sẽ update thêm các cách cài trên các hệ điều hành linux khác nhé.
Cài đặt oracle 19c
yum update
[root@ip-172-31-16-171 ~]# yum install oracle-database-ee-19c-1.0-1.x86_64.rpm
Loaded plugins: ulninfo
No package oracle-database-ee-19c-1.0-1.x86_64.rpm available.
Error: Nothing to do
[root@ip-172-31-16-171 ~]# yum -y install oracle-database-ee-19c
Loaded plugins: ulninfo
No package oracle-database-ee-19c available.
Error: Nothing to do
[root@ip-172-31-16-171 wall]# curl -o oracle-database-ee-19c-1.0-1.x86_64.rpm https://download.oracle.com/otn/linux/oracle19c/190000/oracle-database-ee-19c-1.0-1.x86_64.rpm?AuthParam=1589214671_8e604018ea78aa95791f666a76943393
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
1 2569M 1 34.0M 0 0 7552k 0 0:05:48 0:00:04 0:05:44 7552koracle-database-ee-19c 1 2569M 1 44.0M 0 0 7956k 0 0:05:30 0:00:05 0:05:25 9880koracle-database-ee-19c 2 2569M 2 54.0M 0 0 8294k 0 0:05:17 0:00:06 0:05:11 9806koracle-database-ee-19c-1.0-1.x86_64.rpmoracle-database-ee-19c-1.0-1.x86_64.rpmoracle-database-ee-19c-1.0-1.x86_64.rpmoracl 2 2569M 2 62.0M 0 0 8403k 0 0:05:13 0:00:07 0:05:06 9740koracle-database-ee-19c100 2569M 100 2569M 0 0 9478k 0 0:04:37 0:04:37 --:--:-- 9112k
[root@ip-172-31-16-171 wall]# ll
total 2631516
-rw-r--r--. 1 root root 2694664264 May 11 16:35 oracle-database-ee-19c-1.0-1.x86_64.rpm
[root@ip-172-31-16-171 wall]# yum -y localinstall oracle-database-ee-19c-1.0-1.x86_64.rpm
Loaded plugins: ulninfo
Examining oracle-database-ee-19c-1.0-1.x86_64.rpm: oracle-database-ee-19c-1.0-1.x86_64
Marking oracle-database-ee-19c-1.0-1.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package oracle-database-ee-19c.x86_64 0:1.0-1 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
====================================================================================================
Package Arch Version Repository Size
====================================================================================================
Installing:
oracle-database-ee-19c x86_64 1.0-1 /oracle-database-ee-19c-1.0-1.x86_64 6.9 G
Transaction Summary
====================================================================================================
Install 1 Package
Total size: 6.9 G
Installed size: 6.9 G
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : oracle-database-ee-19c-1.0-1.x86_64 1/1
[INFO] Executing post installation scripts...
[INFO] Oracle home installed successfully and ready to be configured.
To configure a sample Oracle Database you can execute the following service configuration script as root: /etc/init.d/oracledb_ORCLCDB-19c configure
Verifying : oracle-database-ee-19c-1.0-1.x86_64 1/1
Installed:
oracle-database-ee-19c.x86_64 0:1.0-1
Complete!
[root@ip-172-31-16-171 wall]# vi /etc/sysconfig/oracledb_ORCLCDB-19c.conf
[root@ip-172-31-16-171 wall]# cat /etc/init.d/oracledb_ORCLCDB
cat: /etc/init.d/oracledb_ORCLCDB: No such file or directory
[root@ip-172-31-16-171 wall]# /etc/init.d/oracledb_ORCLCDB-19c configure
Configuring Oracle Database ORCLCDB.
Prepare for db operation
8% complete
Copying database files
31% complete
Creating and starting Oracle instance
32% complete
36% complete
40% complete
43% complete
46% complete
Completing Database Creation
51% complete
54% complete
Creating Pluggable Databases
58% complete
77% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
/opt/oracle/cfgtoollogs/dbca/ORCLCDB.
Database Information:
Global Database Name:ORCLCDB
System Identifier(SID):ORCLCDB
Look at the log file "/opt/oracle/cfgtoollogs/dbca/ORCLCDB/ORCLCDB.log" for further details.
Database configuration completed successfully. The passwords were auto generated, you must change them by connecting to the database using 'sqlplus / as sysdba' as the oracle user.
[root@ip-172-31-16-171 wall]#
[root@ip-172-31-16-171 wall]# cat /opt/oracle/cfgtoollogs/dbca/ORCLCDB/ORCLCDB.log
[ 2020-05-11 16:43:34.120 UTC ] Prepare for db operation
DBCA_PROGRESS : 8%
[ 2020-05-11 16:43:34.224 UTC ] Copying database files
DBCA_PROGRESS : 31%
[ 2020-05-11 16:45:07.886 UTC ] Creating and starting Oracle instance
DBCA_PROGRESS : 32%
DBCA_PROGRESS : 36%
DBCA_PROGRESS : 40%
DBCA_PROGRESS : 43%
DBCA_PROGRESS : 46%
[ 2020-05-11 16:53:05.412 UTC ] Completing Database Creation
DBCA_PROGRESS : 51%
DBCA_PROGRESS : 54%
[ 2020-05-11 16:59:30.394 UTC ] Creating Pluggable Databases
DBCA_PROGRESS : 58%
DBCA_PROGRESS : 77%
[ 2020-05-11 16:59:39.946 UTC ] Executing Post Configuration Actions
DBCA_PROGRESS : 100%
[ 2020-05-11 16:59:39.947 UTC ] Database creation complete. For details check the logfiles at:
/opt/oracle/cfgtoollogs/dbca/ORCLCDB.
Database Information:
Global Database Name:ORCLCDB
System Identifier(SID):ORCLCDB
[root@ip-172-31-16-171 wall]# cat /home/oracle/.bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin
export PATH
[root@ip-172-31-16-171 wall]# source /home/oracle/.bash_profile
[root@ip-172-31-16-171 wall]# echo $PATH
/usr/sbin:/usr/local/sbin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin:/opt/oracle/product/19c/dbhome_1/bin
[root@ip-172-31-16-171 wall]# su - oracle
Last login: Mon May 11 16:43:25 UTC 2020 on pts/0
[oracle@ip-172-31-16-171 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon May 11 17:11:34 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> alter user sys identified by 123456;
User altered.
GRANT RESOURCE TO SYS;
GRANT CONNECT TO SYS;
GRANT CREATE VIEW TO SYS;
GRANT CREATE SESSION TO SYS;
GRANT UNLIMITED TABLESPACE TO SYS;
alter session set "_ORACLE_SCRIPT"=true;
CREATE USER TEST IDENTIFIED BY 123456;
GRANT RESOURCE TO TEST;
GRANT CONNECT TO TEST;
GRANT CREATE VIEW TO TEST;
GRANT CREATE SESSION TO TEST;
GRANT UNLIMITED TABLESPACE TO TEST;
-- REVOKE CREATE SESSION FROM newuser;
-- REVOKE CREATE TABLE FROM newuser;
-- REVOKE CREATE VIEW FROM newuser;
-- REVOKE CREATE ANY TRIGGER FROM newuser;
-- REVOKE CREATE ANY PROCEDURE FROM newuser;
-- REVOKE CREATE SEQUENCE FROM newuser;
-- REVOKE CREATE SYNONYM FROM newuser;
GRANT CREATE SESSION TO newuser;
GRANT CREATE TABLE TO newuser;
GRANT CREATE VIEW TO newuser;
GRANT CREATE ANY TRIGGER TO newuser;
GRANT CREATE ANY PROCEDURE TO newuser;
GRANT CREATE SEQUENCE TO newuser;
GRANT CREATE SYNONYM TO newuser;
ALTER USER newuser QUOTA UNLIMITED ON my_tablespace;
Some error:
ORA-27102: out of memory Error
ORA-27102: out of memory Linux-x86_64 Error: 28: No space left on device
Solution:
Check the page size:
#getconf PAGE_SIZE
4096
Calculate proper value for shmall:
The value of shmall should be:
Shmall=total size of the SGAs on the system/page size.
Let’s assume the size of the SGA is 16GB in the system then it would be 1024 * 1024 * 1024 * 16 / 4096 = 4194304
Change shmall in /etc/sysctl.conf
vi /etc/sysctl.conf
kernel.shmall = 4194304
Apply the changes:
# sysctl -p
check shmall value after change
# sysctl -A | grep shmall
Start the database
# su - oracle
# sqlplus sys as sysdba
SQL> startup
su oracle
[oracle@ip-172-31-16-171 wall]$ source /home/oracle/.bash_profile
[oracle@ip-172-31-16-171 wall]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 18-JUL-2020 09:54:33
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ip-172-31-16-171.ap-southeast-1.compute.internal)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 2: No such file or directory
[oracle@ip-172-31-16-171 wall]$ lsnrctl start
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 18-JUL-2020 09:56:23
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Starting /opt/oracle/product/19c/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora
Log messages written to /opt/oracle/diag/tnslsnr/ip-172-31-16-171/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ip-172-31-16-171.ap-southeast-1.compute.internal)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ip-172-31-16-171.ap-southeast-1.compute.internal)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 18-JUL-2020 09:56:23
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora
Listener Log File /opt/oracle/diag/tnslsnr/ip-172-31-16-171/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ip-172-31-16-171.ap-southeast-1.compute.internal)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[oracle@ip-172-31-16-171 wall]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 18-JUL-2020 09:56:37
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ip-172-31-16-171.ap-southeast-1.compute.internal)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 18-JUL-2020 09:56:23
Uptime 0 days 0 hr. 0 min. 13 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora
Listener Log File /opt/oracle/diag/tnslsnr/ip-172-31-16-171/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ip-172-31-16-171.ap-southeast-1.compute.internal)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
What are Sessions in Oracle database : Sessions specify the number of connections that can served by oracle database at a time. This is equivalent to the number of concurrent users of the application. If your application has a lot of concurrent users then you need to increase the sessions in the oracle database.
What are processes in Oracle database : Processes run in the background of Oracle database to maximize performance and accommodate many users.
What are transactions in Oracle database : A transaction is a logical, atomic unit of work that contains one or more SQL statements. A transaction groups SQL statements so that they are either all committed, which means they are applied to the database, or all rolled back, which means they are undone from the database
What is the relation between Sessions, transactions & processes : The Oracle Documentation states that TRANSACTIONS is derived from SESSIONS, which in turn is derived from PROCESSES, thus:
PROCESSES = Operating System Dependant
SESSIONS = (1.1 * PROCESSES) + 5
TRANSACTIONS = 1.1 * SESSIONS
For example, if processes are 100, then sessions will be 115 and transactions will be 127.
To alter the sessions,transactions, processes follow this procedure –
1. At command line, type sqlplus
2. For username give / as sysdba
3. Give the following commands –
alter system set sessions=400 scope=spfile;
alter system set processes=350 scope=spfile;
alter system set transactions=440 scope=spfile;
Give the values according to the requirement
4. After running these commands, you need to restart the Oracel instance, follow these commands
shutdown immediate;
startup;
5. After startup is complete run the following commands to see the updated values
select name, value
from v$spparameter
where name in (‘sessions’,’processes’,’transactions’);
Oracle database has a gv$resource_limit view to check if any database resource reach his connection limit:
This info is, as all v$ views, from instance startup to current time. So, in this example we can say that max processes limit was reached, and just now there are only 44 processes running. v$resource_limit is a very useful view for day-to-day work because you can glance in a moment if there is or were some process reaching his limit
If you check sessions, processes or another parameter and they are nearly or reaching their limits you can resize them by following this article: change parameters in Oracle
Please note that if you set processes value, oracle will automatically set sessions value to (1.1 * PROCESSES) + 5 for oracle 10g and (1.5 * PROCESSES) + 22 in oracle 11g and above...(for previous versions you should set both parameter accordingly)
WALLET IN ORACLE
Create a directory structure like below :
/$ORACLE_BASE/admin/<database_name>/wallet
and then issue the below command. It will work..
SQL> alter system set encryption key identified by "XXXXXXXXXX";
Doc ID 301830.1
Sometime, you may get the below error, when you try to start the Oracle database:
SQL> startup nomount
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device
One of the reason is, shmall kerne parameter is set to a low value on /etc/sysctl.conf or /proc/sys/kernel/shmall file.
SHMALL is the total amount of shared memory, in pages, that the system can use at one time.
This value is set to 50% of the total physical memory available.
So, your total SGA allocated from all the database, hosted on a server, cannot be greater than the SHMALL value.
How to calculate the Memory limit from SHMALL parameter:shmall parameter value is set in Page size.
So,
Memory in GB = (shmall * Page size in Bytes)/(1024*1024*1024)
run the below command to get the page size in Bytes : $ getconf PAGE_SIZE
To change the value of shmall, edit the /etc/sysctl.conf file as root and change the value of shmall.
Then, run the below command as root user to make the change to shamall value parmanent, without the need to to reboot the server. (Alternatively, you can reboot the server to make this change permanent).
# sysctl -p
Modifying /etc/sysctl.conf is a permanent workaround (applies at boot time). If for some reason you DO NOT want to change the system wide configuration, you can do it on the fly by directly changing the kernel pseudo FS AKA procfs.
e.g. # echo "4194304" > /proc/sys/kernel/shmall
