Cài đặt oracle 19c in Linux

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

export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19c/dbhome_1
export ORACLE_SID=ORCLCDB
PATH=/usr/sbin:$PATH:$ORACLE_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]# 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 Error

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

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:

set linesize 190
set pages 200
select * from gv$resource_limit;

    INST_ID RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCAT LIMIT_VALUE
---------- ------------------------------ ------------------- --------------- --------------- ------------
         1 processes                                       44             300        300             300
         1 sessions                                        52             215        512             512
         1 enqueue_locks                                   30             295       6480            6480
         1 enqueue_resources                               24             165       2472       UNLIMITED
         1 ges_procs                                        0               0          0               0
         1 ges_ress                                         0               0          0       UNLIMITED
         1 ges_locks                                        0               0          0       UNLIMITED
         1 ges_cache_ress                                   0               0          0       UNLIMITED
         1 ges_reg_msgs                                     0               0          0       UNLIMITED
         1 ges_big_msgs                                     0               0          0       UNLIMITED
         1 ges_rsv_msgs                                     0               0          0               0
         1 gcs_resources                                    0               0  UNLIMITED       UNLIMITED
         1 gcs_shadows                                      0               0  UNLIMITED       UNLIMITED
         1 smartio_overhead_memory                          0           71704          0       UNLIMITED
         1 smartio_buffer_memory                            0               0          0       UNLIMITED
         1 smartio_metadata_memory                          0               0          0       UNLIMITED
         1 smartio_sessions                                 0               1          0       UNLIMITED
         1 dml_locks                                        1              29       2252       UNLIMITED
         1 temporary_table_locks                            0              28  UNLIMITED       UNLIMITED
         1 transactions                                     1               7        563       UNLIMITED
         1 branches                                         0               0        563       UNLIMITED
         1 cmtcallbk                                        0               3        563       UNLIMITED
         1 max_rollback_segments                           11              11        563           65535
         1 sort_segment_locks                               1              12  UNLIMITED       UNLIMITED
         1 k2q_locks                                        0               0       1024       UNLIMITED
         1 max_shared_servers                               1               1  UNLIMITED       UNLIMITED
         1 parallel_max_servers                             0               0        270            3600

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";

System altered.