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é.
[root@ip-172-31-16-171 wall]# yum -y localinstall oracle-database-ee-19c-1.0-1.x86_64.rpmLoadedplugins:ulninfoExaminingoracle-database-ee-19c-1.0-1.x86_64.rpm:oracle-database-ee-19c-1.0-1.x86_64Markingoracle-database-ee-19c-1.0-1.x86_64.rpmtobeinstalledResolvingDependencies--> Runningtransactioncheck---> Packageoracle-database-ee-19c.x86_640:1.0-1willbeinstalled--> FinishedDependencyResolutionDependenciesResolved====================================================================================================PackageArchVersionRepositorySize====================================================================================================Installing:oracle-database-ee-19cx86_641.0-1/oracle-database-ee-19c-1.0-1.x86_646.9GTransactionSummary====================================================================================================Install1PackageTotalsize:6.9GInstalledsize:6.9GDownloadingpackages:RunningtransactioncheckRunningtransactiontestTransactiontestsucceededRunningtransactionInstalling:oracle-database-ee-19c-1.0-1.x86_641/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_641/1Installed:oracle-database-ee-19c.x86_640:1.0-1Complete![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_ORCLCDBcat:/etc/init.d/oracledb_ORCLCDB:Nosuchfileordirectory
[root@ip-172-31-16-171 wall]# /etc/init.d/oracledb_ORCLCDB-19c configureConfiguringOracleDatabaseORCLCDB.Preparefordboperation8%completeCopyingdatabasefiles31%completeCreatingandstartingOracleinstance32%complete36%complete40%complete43%complete46%completeCompletingDatabaseCreation51%complete54%completeCreatingPluggableDatabases58%complete77%completeExecutingPostConfigurationActions100%completeDatabasecreationcomplete.Fordetailscheckthelogfilesat:/opt/oracle/cfgtoollogs/dbca/ORCLCDB.DatabaseInformation:GlobalDatabaseName:ORCLCDBSystemIdentifier(SID):ORCLCDBLookatthelogfile"/opt/oracle/cfgtoollogs/dbca/ORCLCDB/ORCLCDB.log"forfurtherdetails.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 operationDBCA_PROGRESS:8%[ 2020-05-11 16:43:34.224 UTC ] Copying database filesDBCA_PROGRESS:31%[ 2020-05-11 16:45:07.886 UTC ] Creating and starting Oracle instanceDBCA_PROGRESS:32%DBCA_PROGRESS:36%DBCA_PROGRESS:40%DBCA_PROGRESS:43%DBCA_PROGRESS:46%[ 2020-05-11 16:53:05.412 UTC ] Completing Database CreationDBCA_PROGRESS:51%DBCA_PROGRESS:54%[ 2020-05-11 16:59:30.394 UTC ] Creating Pluggable DatabasesDBCA_PROGRESS:58%DBCA_PROGRESS:77%[ 2020-05-11 16:59:39.946 UTC ] Executing Post Configuration ActionsDBCA_PROGRESS:100%[ 2020-05-11 16:59:39.947 UTC ] Database creation complete. For details check the logfiles at:/opt/oracle/cfgtoollogs/dbca/ORCLCDB.DatabaseInformation:GlobalDatabaseName:ORCLCDBSystemIdentifier(SID):ORCLCDB
[root@ip-172-31-16-171 wall]# cat /home/oracle/.bash_profile# .bash_profile# Get the aliases and functionsif [ -f~/.bashrc ]; then.~/.bashrcfi# User specific environment and startup programsPATH=$PATH:$HOME/.local/bin:$HOME/binexport PATH
-- 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;
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