• Open source integration tool.
• It can be made as centralised server by integrating multiple source like source code management , build tools , deployment environment .
• Complete INTEGRATION TOOL
• Its Java based.
• To automate the repeated task.
• CI/CD ( continuous integration and continuous delivery )
• Even you can do patching.
• In simple , its a centralised server.

Advantages

1. CI/CD
2. Open source
3. Community driven
4. Browser based
5. Supports all Operating system.
6. Distributed build --> Master and Slave node , how ? If you are setting up Jenkins , it uses resource of the system where its installed and accordingly the Jobs will take the RAM , CPU and resources. To avoid this "Distributed build" was introduced , so that "Master and Slave node" with that the load will be distributed.
7. We can increase the scalability of master and slave.

CI/CD Workflow

• Commit
• Build
• Test
• Stage
• Deploy

1. Stages will be different in other CI/CD process. Purpose will be the same.
2. Dedicated branch for each environment it will be a best practice to avoid any confusion here.

Installation & Configuration

sudo apt-get update
java -version

Add the official key of Jenkins and then the repo.

sudo wget -O /usr/share/keyrings/jenkins-keyring.asc \
  https://pkg.jenkins.io/debian-stable/jenkins.io-2023.key
echo "deb [signed-by=/usr/share/keyrings/jenkins-keyring.asc]" \
  https://pkg.jenkins.io/debian-stable binary/ | sudo tee \
  /etc/apt/sources.list.d/jenkins.list > /dev/null
sudo apt-get update
sudo apt-get install jenkins

Now JENKINS had been installed , please check with command also whether its installed or not.

dpkg -l | grep -i jenkins

As we have encountered error , install higher version of Java from 11 to 17 or 21.

sudo systemctl start jenkins
sudo systemctl enable jenkins
sudo systemctl status jenkins

• Now open the console with the IP of the system.

• If you want to know the username , then go to below location.

cat /var/lib/jenkins/users/users.xml
/var/lib/jenkins/users/admin_17980521444909415742

want to change the password , edit and restart the jenkins.

<hudson.security.HudsonPrivateSecurityRealm_-Details> <passwordHash>#jbcrypt:$2a$10$OTYB2osGPi/rasutjHcYOOhByiCoaEZTEQk52CABOwYdrtxeIPnBu</passwordHash>
</hudson.security.HudsonPrivateSecurityRealm_-Details>

• Give no password for jenkins user ( as its a internal user )

• Check whether able to get output without sudo password.

User Management & security

• Manage Jenkins > Security

• Trying to use the OS user for security features , lets try.
• Add the user to the group shadow like below and restart the Jenkins.

• Now its integrated with the OS user (sathishpy1808) , you can even login with the OS users after integrating.

Jenkins own database

• User created in Jenkins only can login , OS users can't.

User creation

• Change to "Jenkins own database" then only you can view the user creation option like below ,

• Go to the ravi user profile and explore.

• Even you can terminate all sessions from the console.
• You can change the password also and delete option is also available.

Authorisation

• what type of authorisation is given to the users , you can see many options in the below screen.

• BY default --> "logged-in users can do anything" its equal to admin access --> its not advisable.
• Try to use with user based ,

• Also ROLE based plugin it will be useful , lets install ( mostly used plugin for role based authorisation )

• Manage & roles won't be available at first , it will come after the plugin installation only.

• Manage roles and assign roles are very important option.
• Lets create a role and then assign the users.
• Pattern based users are created only in "Item Roles". Here if any user starts with word "Manage" , it should the managing job roles.

• Create some Jobs to test this user specification.

• Item roles in "Assign Roles" will be useful for pattern description for users.
• You can see all roles which are added.
• All users are added in this console.

Job Management

• Jobs needs to be created to perform a task.

Build Triggers

• "Trigger builds remotely (e.g., from scripts)" --> it will trigger once some other task gets triggered.
• "GitHub hook trigger for GITScm polling" --> once the code gets committed then only the job needs to be triggered.

Build Environment

Build Steps

• This is the main step , you can add number of steps here.
• Raw commands , shell script , etc.
• Can be added like stages.

Post-build Actions

Notes

1. https://www.jenkins.io/doc/book/installing/linux/ --> In this site , you can get the details of the key and the repo details which we have added in the first of the installation part. [TBD]
2. What is "openjdk-17-jdk-headless package" ?
3. Default port of Jenkins is 8080.
4. jenkins user will be always running.

Commands Used

java -version
locate jdk
whereis java

List of errors

Job for jenkins.service failed because the control process exited with error code.

• Now you can get the details by typing ,

journalctl -xeu jenkins.service

Error clearly says , ( version not supported )

Dec 01 22:03:57 meet.sathishpy1808.org jenkins[40153]: Running with Java 11 from /usr/lib/jvm/java-11-openjdk-amd64, which is older than the minimum required version (J>
Dec 01 22:03:57 meet.sathishpy1808.org jenkins[40153]: Supported Java versions are: [17, 21]
Dec 01 22:03:57 meet.sathishpy1808.org jenkins[40153]: See https://jenkins.io/redirect/java-support/ for more information.
Dec 01 22:03:57 meet.sathishpy1808.org systemd[1]: jenkins.service: Main process exited, code=exited, status=1/FAILURE

Access Denied

• change the ownership to the current user,

sudo chown -R sathishpy1808:sathishpy1808 /var/lib/jenkins/
sudo chown $(whoami) /var/lib/jenkins

Dangling meta character '*' near index 0

Lets start to bug the Postgres

https://github.com/syedjaferk/postgres_sample_database/blob/main/dvd_rental/dvdrental.tar

• Above all screenshots are my mistakes , I should use my OS user as POSTGRES then execute all commands.

Select queries

Commands

• to drop the database --> DROP DATABASE ;
• "-\" --> list the database

• Who uses the DB frequently ? Application.

Lets deep dive into Architecture

Post Master

• ஒரு specific portல incoming requestஅ collect பண்ணி வச்சிக்கும். It just re-direct.

• Backend process pool uses RAM & CPU so we can't give any number. It will assigned with a specific value , if all are used then Backend process pool will ask the request to wait until the other request gets completed. Each Backend process pool as separate VIRTUAL memory. it won't all data , but specific information related to request. Even Backend process pool won't execute the request.

• Now Backend process pool ( BP ) will re-direct to Backend workers pool.
• Eg BP - 100 & BW - 100.
• Status of these gets changed like ACTIVE , IDEAL , etc
• Even this Backend workers pool will have separate MEMORY. ( keep in mind )

Query example

• Take an example of the below one,

select * from employee where first_name=john ;

• It will go to the Backend workers pool , then " select * from employee " goes and search in SHARED BUFFER. If its there then it returns the response.
• If the result is not available in SHARED BUFFER , then it goes to DISK.
• Here the catch is , even the SHARED BUFFER size is limited. This can be configured.
• For eg., இப்ப அந்த SHARED BUFFERல 10 mb size allocate பண்ணி இருக்கு , அடுத்த request உள்ள வருது , SHARED BUFFERல இந்த request இல்ல so it goes to DISK and then gets the output of 20 mb file size . இப்ப ஏற்கனவே இருந்த 10 mb size file will be erased and new 20 mb file will be saved in that SHARED BUFFER.
• It will be very fast when the output comes from SHARED BUFFER.
• WORKER Memory will take the order by , sort , etc other than select ( main query ).

Auxiliary Process

WAL WRITER - 1

• Write Ahead Log
• If will take all the backup of the request and the query.
• It as separate buffer space.
• It will also go to DISK.
• The command comes to WAL from two places 1. BACKEND WORKER 2. BACKGROUND WRITER.

DIRTY PAGES

• When the output gets stored in the SHARED BUFFER , at the same time a DIRTY PAGES gets created. why it gets created ? Eg., If there is any update in the query which is stored in SHRED BUFFER , then it updates but this is not saved in DISK.
• If the update or any operation didn't go to main DISK , then it will create a DIRTY PAGES.
• At the same time BACKGROUND WRITER , takes the notes of DIRTY PAGES and asks WAL WRITER to take a not of it.
• The uncommitted task still goes to OS FILE SYSTEM from DIRTY PAGES . Note : still the commit didn't reach the MAIN DISK.

WAL WRITER - 2

• If there is any crash in SHARED BUFFER , then we can get it from WAL WRITER.
• Data won't be recovered but the query can be recovered for the WAL.

WAL ARCHIVER

• WAL WRITER will put all details to WAL ARCHIVER.

CHECK-POINTER

• Whatever comes to OS FILE SYSTEM , CHECK-POINTER will take care to make sure that it reaches the DISK properly.

AUTOVACCUM

• PostgreSQL works in UPPEND method , it takes a clone of the table and then it updates.
• PostgreSQL is Multi-version control.
• It will clean all the STALE process.
• Eg.,

Arun , 24 , 900 - STALE
Raj , 23 , 901
There is an update in Arun Mark ,
Arun , 24 , 910


• இதுல Arunகு மட்டும் updation இருக்கு but இதில் update பண்ணும் போது தனியா ஒரு row create பண்ணி like duplicate and then update takes place.
• AUTOVACCUM will clean all STALE rows.

STARTUP PROCESS

• It will start at first and gets all data from WAL Archive Folder , then only Postgres allows the request.
• Why we need this startup process ? So that all got closed properly.
• This will happen before the "Post Master".

Replica

• Standby unit.
• Its like clone OR Master & Slave concept.
• It receives all details from WAL sender.

Notes

• In windows command path , if you give just "path". It will display all paths which are configured.
• https://miro.com/app/board/uXjVLD2T5os=/

Installation over Oracle Linux

• Version of the Linux Distro which I am using ,

cat /etc/os-release
NAME="Oracle Linux Server"
VERSION="7.9"
ID="ol"
ID_LIKE="fedora"
VARIANT="Server"
VARIANT_ID="server"
VERSION_ID="7.9"
PRETTY_NAME="Oracle Linux Server 7.9"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:oracle:linux:7:9:server"
HOME_URL="https://linux.oracle.com/"
BUG_REPORT_URL="https://github.com/oracle/oracle-linux"
ORACLE_BUGZILLA_PRODUCT="Oracle Linux 7"
ORACLE_BUGZILLA_PRODUCT_VERSION=7.9
ORACLE_SUPPORT_PRODUCT="Oracle Linux"
ORACLE_SUPPORT_PRODUCT_VERSION=7.9

• Install the repository RPM,

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

• Install PostgreSQL,

sudo yum install -y postgresql13-server

Optionally initialize the database and enable automatic start

sudo /usr/pgsql-13/bin/postgresql-13-setup initdb [TBD]
sudo systemctl enable postgresql-13
sudo systemctl start postgresql-13


initdb

• This file will create all DB related files , directories & Configuration files.

• Also creation of system catalogs --> what is this ? --> It will store all indexes , roles , etc. --> you can see list of catalogue using below statement.

postgres=# select relname from pg_catalog.pg_class where relkind='r';

sudo -i -u postgres psql

• By default a user , a role and a database will be created.
• All the above 3 will be created in same name "postgres".
• Then how to check the roles ?

postgres=# \du

• Now see the list of databases, ( these 3 are created by postgres by default with the help of template1.

postgres=# \l

• If you try to create a new DB , then it will follow the "template1", its like the blueprint.
• Then why template0 ? --> you can edit template1 but not template0 ( you can't change and its not editable ).
• so in short template0 --> NOT EDITABLE & template1 --> EDITABLE.

WAL

• WRITE AHEAD LOG.
• If the database gets crashed , with this help we can retrieve the data.

Roles

• Librarian , Library member and visitor all these are roles.

• Lets create a dummy role and test ,

• You can see "Cannot login" , that means you can't login as it's not assigned.

• creating one more ROLE,

• Assigning the permissions or roles,

• Here you can't see 'CANNOT LOGIN'.
• If a ROLE can login then he/she is the user.
• User can inherits the parents also.

How it works ?

Postmaster

• All connection request goes first here. ( whatever client ask , server will respond )
• Follows client server architecture.

WAL Writer

• It will be write in a notebook , to keep the track of all.

Checkpoint

• It periodically checks and responed.

Auto Vaccum

• It periodically checks.

Stats Collector

• Gathers all stats Eg., if a table is getting more request then it will collect the information and try to keep it in cache to respond very fast and also check whether index is there for the table.

Archiver

• To backup the data.

Reference

https://www.postgresql.org/download/linux/redhat/ --> This site will provide steps to install in different distro's.

Notes

1. "template0" is called pristine.

Questions

1. what command initialize a PostgresSQL database cluster? initdb
2. which role is automatically created during PostgresSQL installation ? postgres
3. what is the purpose of template0 in PostgresSQL ? Pristine , unmodified template.
4. which PostgresSQL template database is used as the default for creating new databases ? template1
5. how to connect PostgresSQL interactive terminal ? psql
6. List all databases ? \l
7. Architecture of PostgresSQL ? Client Server

• Its nothing but " Bulk Loader Utility ".
• With this concept we can load the data to the table in bulk.
• Main word is LOAD.
• Then comes to your mind , what is the difference between load and insert ? Insert happens one by one.Load happens in one go.
• what data ? which table ? loading script ? Execute --> these are the four things YOU NEED TO KEEP IN MIND.

• Flat files --> csv ( comma separated value ) , txt , dat , excel , etc.
• Always use notepad to load the data.

select employee_id || ',' || first_name || ',' || salary from employees_table where rownum <= 10 ; --> this will fetch only 10 rows.

• save this file in a folder as csv.

select employee_id || ',' || first_name || ',' || salary from employees_table where employee_id between 150 and 170 ; --> this will fetch rows between those values.

• Save this file as txt.

Now coming to table creation

create table sample(id number , name varchar2(25) , salary number);

Now coming to creation of script

• loading script or control file both are same.

load data infile 'path_of_the_file.csv'
infile 'path_of_the_file.txt'
insert into table sample
fields terminated by ','
(id,name,salary)

• create the script and save as ALL FILES ( notepad ) with .ctl file.

Now coming to Execute

sqlldr hr_schema_name/password control='file_location_of_control_file_or_execution_file' direct = true

• here why direct=true --> it will load very fast and it will by-pass all constraints and triggers.
• if direct=false --> constraints and triggers it will check and then it execute.

• In short ,

Excluding one column

• If you some column should not be loaded , then use FILLER.

load data infile 'path_of_the_file.csv'
infile 'path_of_the_file.txt'
insert into table sample
fields terminated by ','
(id,name,salary filler)

load data infile 'path_of_the_file.csv'
infile 'path_of_the_file.txt'
insert into table sample
fields terminated by ','
(id,name filler,salary)

• In above example , salary and name will be empty . It won't load the data.

Condition

• WHEN --> loading data should obey the condition which you give. If the condition fails , then it stores the failed data in DISCARD FILE.
• If there is Oracle error , then it gets captured in BAD FILE.

• WHEN condition should be used here,

load data infile 'path_of_the_file.csv'
infile 'path_of_the_file.txt'
insert into table sample when ?
fields terminated by ','
(id,name filler,salary)

How to get the process summary ?

• It will be stored in log file.
• you can set all the files in the command itself , like below.

sqlldr hr_schema_name/password control='file_location_of_control_file_or_execution_file' log = summary.log bad = sample.bad discard = sample.dsc direct = true

• If you are giving any file name here , then it will generate automatically.
• So Import take here is ,

skip

• If you want to skip the rows while loading , then you can specify in the command itself.

sqlldr hr_schema_name/password control='file_location_of_control_file_or_execution_file' skip = 2 direct = true

• 2 rows will be skipped.

Notes

• SQL loader short key word is sqlldr.
• insert into table sample --> this will work only when the table is EMPTY. If you try to execute again , then it throw below error.

so you can use ,

load data infile 'path_of_the_file.csv'
infile 'path_of_the_file.txt'
append into table sample
fields terminated by ','
(id,name,salary)

• Also you use truncate ( it will delete old data and insert new data again )

load data infile 'path_of_the_file.csv'
infile 'path_of_the_file.txt'
truncate into table sample
fields terminated by ','
(id,name,salary)

Task

1. For a particular column instead of (,) separated it's used as (#) - how to load ?
2. how to load the excel file ?

• Network --> communications between devices.
• IP Address --> unique identifier to each device which is internet protocol address.

IPv4

1. 4th of version of Internet protocol.
2. 32 bit
3. Totally 4 blocks with 8 bit segments each. A , B , C & D.
4. 2 types of IP address.
5. Public ( Mainly using internet routing ) & Private ( Office )
6. Range --> 0 to 255
7. 0 & 255 reserved by system.
8. 127 --> loop-back address. 253 address we can use.
9. Then how to find whether its public or private ? By classes.
10. A, B , C --> Commonly used.
11. D & E --> Multi-casting & Research purpose.
12. Class A --> 0 to 127 Public , Private 10 is only used eg., 10.0.0.1 ( 16 million hosts can be declared )
13. Class B --> 128 to 191 Public , Private 172 is only used eg., 172.16.0.1 to 171.16.255.254 ( 65,536 hosts can be declared ) - Med size networks.
14. Class C --> 192 to 223 Public , Private 192.168.1.1 small network for 254 hosts.
15. Class D --> 224 to 239 for multicast groups.
16. Class E --> 240 - 255 for research purpose.

As a whole , the private range is .

A --> 10.0.0.1
B --> 172.16.0.0
C --> 192.168.0.0

Subnetting

• Slashing the network.

Virtual Network in Azure

• Software based network connects virtual machines.

Subnet in Azure

• Subdivison of VNET.
• we can organise the resources within a network.
• Features as follows ,

Azure Portal

All service >> Networking >> Virtual Networks >> Create

• Gave a wrong IP , then you can see a prompt is coming.

• VNET & Subnet creation

Notes

1. DNS , DHCP & Gateway , 255 Broadcast --> 4 IP's are reserved.

• Install Oracle Virtual Box , Winscip & Putty.
• Load the lab.
• Change the network setting from NAT to Bridged Adapter.

• Click ON & OFF its like restart.
• IP which is assigned 192.168.1.104.
• Assign any IP from 192.168.1.(0 to 255) , so I have assigned as 192.168.1.104. This IP taken from IPv4 in laptop.
• Open Terminal in the screen by right click.

• Ping Google and check whether its working or not.

• Now you can login with your IP which is assigned.

• Now we need to understand the concept of SHARED FOLDER.
• You can share the SOFTWARES to the VM from laptop folder , no need to use WINSCP. Lets explore.

• Lets create directories and unzip the software.

mkdir -p /u01/app/oracle/product/19.0.0.0/dbhome_1
unzip /media/sf_Software/V982063-01-001.zip -d /u01/app/oracle/product/19.0.0.0/dbhome_1

Install all RPM required for DB installation

• Once you install this RPM , oracle user will be created automatically.

• we don't know the password of ORACLE user , now set it.

• password is oracle and user : oracle
• Now run the installer.
• Before that execute xhost + --> Received error , its mainly used to set the DISPLAY.

• Getting error while installing the runinstaller .

• May be because of LOGIN root user. Lets try with new session with ORACLE user.

• Login with VM machine to avoid the DISPLAY issue.

Now lets create Database

• Password is ORACLE

• DB installation is completed.
• How many Oracle Softwares are installed in the servers ? How to check ? oraInventory file will help to do this.

• Now comes the question , how to find the Inventory location ?

• How many databases are created in the server , if this file is changes , complete database may corrupt.

• How many are running in this server , that is the DB server ?

ps -ef|grep pmon
ps -ef|grep smon

• How to start the DB ?

• As I have faced issue with SID , changed completly to new SID and started the server.

Notes

• Oracle Home / Oracle Software / Database Software / Database Home --> All these are same.
• How many Oracle Softwares are installed in the servers ? How to check ? oraInventory file will help to do this.

• Now comes the question , how to find the Inventory location ?

• How many databases are created in the server , if this file is changes , complete database may corrupt.

• How many are running in this server , that is the DB server ?

ps -ef|grep pmon
ps -ef|grep smon

Error & Solution

https://superuser.com/questions/1755254/virtualbox-guest-additions-update-got-error-missing-selinux-target-policy-file

Linux Commands Used

yum install oracle* --skip-broken 
yum -y install oracle-database-ee-19c
yum -y install oracle-database-ee-21c
# groupadd dba
# groupadd oinstall 
# useradd -g oinstall -G oinstall,dba oracle
#chown -R oracle:oinstall /u01

cd /u01/app/oracle/product/19.0.0.0/dbhome_1 --> Tool used to install Oracle Home / Database Home?
./runInstaller
cd /u01/app/oracle/product/19.0.0.0/dbhome_1/bin --> Tool used to create database?
./dbca

cat /u01/app/oraInventory/ContentsXML/inventory.xml
cat /etc/oraInst.loc
cat /etc/oratab
ps -ef|grep smon
ps -ef|grep pmon
su - oracle
. oraenv
env |grep ORA
sqlplus / as sysdba
passwd oracle
xhost +

Sorry GUYS its WINDOWS

• Run as Administrator

Error & Solution

• This is working
  https://stackoverflow.com/questions/78013258/unable-to-launch-jdeveloper-for-missing-msvcr100-dll

• Also try to install this,
  https://learn.microsoft.com/en-gb/cpp/windows/latest-supported-vc-redist?view=msvc-170#latest-microsoft-visual-c-redistributable-version

Notes :

1. If its windows installation then always install via ADMIN user.
2. If you want to set JAVA , do the below but most of the cases its not required.
3. Many issues with Java download , the JDK should have JRE also . Please check after installation of Java.
4. It may take some time please wait patiently.

Null Functions

• nvl
• nvl2
• nullif
• coalease

select 'NULL',null,'',' ' from dual;

• Only 2 & 3 output are the NULL values.

nvl

• if the value is NULL then print the value in the right hand side value.
• Only two arguments are passed. ( Input , what to replace )

select nvl('',0) from dual;
select nvl(NULL,0) , nvl(NULL , 'A') from dual;

• As the left side argument is NOT null then it prints the same value.

nvl2

• 3 arguments.
• ( 'infosys' , 2 , 'B') --> If the first argument is NULL then 3rd arguments will be printed or else second argument will be printed.

select nvl2('A',0,4) from dual;
select nvl2('',0,4) from dual;

nullif

• two arguments
• both should be NULL.

select nullif(1,7) , nullif('aa','aa') from dual;
select nullif(1,7) , nvl(nullif('aa','aa'),'A') from dual;

coalesce

• only one row.
• It prints first NOT NULL Value.
• It accepts n no of arguments, no limit.

select NULL, NULL , 30 , 40 , NULL from dual;
select coalesce(NULL, NULL , 30 , 40 , NULL) from dual;

Conversion Function

• to_char
• to_number
• to_date
• to_timestamp

to_char

select sysdate from dual ;
select to_char(sysdate,'DAY'),to_char(sysdate,'day'),to_char(sysdate,'Day'),to_char(sysdate,'D'),to_char(sysdate,'DD') from dual ;

--select sysdate from dual ;
select to_char(sysdate,'DAY'),to_char(sysdate,'day'),to_char(sysdate,'Day'),to_char(sysdate,'D'),to_char(sysdate,'DD') from dual ;
select to_char(sysdate,'MONTH'),to_char(sysdate,'MONTH'),to_char(sysdate,'Month'),to_char(sysdate,'MM') from dual ;


select to_char(sysdate,'YEAR'),to_char(sysdate,'Year'),to_char(sysdate,'yyyy'),to_char(sysdate,'yy') from dual ;

select to_char(sysdate,'IW'),to_char(sysdate,'W'),to_char(sysdate,'Q') from dual ;

To Number

--select '123' , 123 , to_number('123') from dual ; - how to check its converted to number ?
select '123' , 123 , reverse (to_number('123')) from dual ;

to_date

select '2024/12/24' , to_date('2024/12/24', 'yyyy/mm/dd') from dual ;
select to_date('24-DEC-24', 'MON-YYYY-DD') from dual ; --> Error
select to_date('DEC-2024-24', 'MON-YYYY-DD') from dual ;


to_timestamp

select TO_TIMESTAMP('DEC-2024-24 16:24:00', 'MON-YYYY-DD HH24:MI:SS') from dual ;

Note :

• Anything which is given within single quotes is always STRING.
• Joins , sub query , group functions , Analytics Functions & Set Operators are very import in SQL.
• what is Index , global temp table , Objects , views , sequence ( why we use ? )
• Reverse function will work only for STRING not for Numbers.
• what is the dataype of NULL ? Its NULL only.
• select sysdate , systimestamp from dual; --> Check this

• In SQL plus it will be different :

• RDBMS language.
• Its ANSI --> standard language for operational relational databases.
• Efficient , easy to learn and use.
• Functionally complete.

How SQL Works

• Data Sublanguage.
• works with logical level.
• common language for all relational databases.

SQL Statement types

• Data Manipulation Language DML

• Data Definition Language DDL

• Data Control Language DCL

• Transaction Control Language TCL

Oracle SQL Development Environments

• Below are the Tools used for SQL:
• Oracle SQL Developer --> debug , export , view reports ,etc
• SQL Plus --> Batch query tool.
• Oracle JDeveloper --> Multi-platform tool for web services.
• Oracle Application Express --> Like a web browser.

Oracle SQL Developer

Concept on RDMS ( Relational Database Management Systems )

• Relational Database --> Collection of relations or two-dimensional tables controlled by server processes.
• Before entering RDMS , What is Data Model ?
• Entity Relationship Model --> its business specifications.
• Then Modelling conventions --> its called Entity Relationship Modelling conventions.

• Relating Multiple Tables --> 1. Each row is unique by primary key 2. Logically a foreign key can be related with multiple tables.

HR Schema Model

SQL Statement

• Not case sensitive.
• one line or more lines.
• keywords can't be across lines or it can't be abbreviated.
• Clauses used in separate lines , what is clause here ? The main clauses are SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, INSERT, UPDATE, DELETE, and JOIN. [ TBD ]
• We can use indents so that one have better enhanced readability.

• dualis a table created by Oracle Database and its owned by SYS.
• dual as one column called DUMMY( data type VARCHAR (1)) and contains one row with value x.
• we can use to compute some expressions.

select * from dual;
select sysdate from dual;

Arithmetic Expression

• +
• "-"
• *
• /

• It reads from LEFT to RIGHT. L--->R

• You can see the difference after using Parenthesis.

select first_name,2*(salary+300), 2*salary+300 from hr.employees;

• NULL--> what is the value of it ? its like unavailable , unassigned , unknown or inapplicable.
• NULLis not as same as ZERO or BLANK SPACE.

Column Aliases

• Rename the column headings
• Column followed by AS.
• without AS also you can declare.

• In the column name , it becomes CAPS. To avoid this you can use "" for AS like below,

-- select first_name AS name , last_name as father_name from hr.employees;
select first_name AS "name" , last_name as "father_name" from hr.employees;
select first_name first  , last_name last  from hr.employees;

Concatenation Operators

• To link the column or character strings to other column.
• ||

select first_name||last_name AS "name"  from hr.employees;

Literal Character Strings

• Its a character , number or a date included in the select statement.
• Date & character should be enclosed with single quotation marks.
• Each character string is the output of each row.

select first_name || ' of ' || last_name OUTPUT from hr.employees;

Alternate Quote Operator

• we can use own quotation mark delimiter.
• specified with 'q'.
• To increase the readability.

select department_name || q'[ Departments Name is :]' || manager_id "Department and Manager" from hr.departments ;
select department_name || 'Departments Name is :' || manager_id "Department and Manager" from hr.departments ;

DISTINCT Keyword

• default select query will display all rows irrespective of duplicate rows.
• To eliminate the duplicate we can use DISTINCT keyword.

-- select department_id from hr.employees ;
select distinct department_id from hr.employees ;

DESCRIBE Command

• Its not a KEYWORD.
• To display table structure.
• Alternatively we can see in SQL developer in the column Tabs.

ORACLE QUESTIONS FOR CLEARING THE LEARNING EXAM

General Functions

• case
• decode

case

• its for condition.
• Relation and logical operators will be used when implementing CONDITIONS.
• It needs to have END word while finishing.

-- select first_name , salary , salary*10/100 from hr.employees; 10 % increment but we need only > 10000 ? how to do ?
-- select first_name , salary , CASE WHEN SALARY > 10000 THEN salary*10/100 END from hr.employees;
select first_name , salary , CASE WHEN SALARY > 10000 AND FIRST_NAME LIKE 'A%' THEN salary*10/100 END from hr.employees;

decode

• NO Relation or logical operators will be used when implementing CONDITIONS.
• decode ( input , p1 , p1 , p2 , p2 )
• Read two two.

• select decode (1,2,4,1,0) from dual;

என்னுடைய input வந்து 1 , now read two by two , the next inputs. ‘1’க்கு அடுத்தது ரெண்டு ரெண்டா படிங்க . இதுல input வந்து ஒன்று , என்னுடைய input ‘2’டா இருந்ததுன்னா 4 என்று print பண்ணனும் but our input is ‘1’.
என்னுடைய input ‘1’ இருந்ததுன்னா 0 என்று print பண்ணனும் but our input is ‘1’
So answer is 0.

• select decode (1,2,4,8,0,5) from dual; --> here 5 is else part.

• Now you will get data for all , because you have given else part in the query.

-- select first_name , salary , CASE WHEN SALARY > 10000 AND FIRST_NAME LIKE 'A%' THEN salary*10/100 END from hr.employees;
select first_name , salary , decode ( salary , 24000 , salary*10/100 , 17000 , salary*10/100 ) from hr.employees;

select first_name , salary , decode ( salary , 24000 , salary*10/100 , 17000 , salary*10/100 , salary*5/100 ) from hr.employees; --> Else is implemented.

select first_name , salary , CASE WHEN SALARY > 10000 THEN salary*10/100 
    WHEN SALARY < 10000 THEN salary*9/100 
    ELSE salary*5/100
    END AS BONUS from hr.employees;

-- select first_name , salary , COMMISSION_PCT , CASE WHEN COMMISSION_PCT IS NULL THEN 1.5 END from hr.employees;
select first_name , salary , CASE WHEN COMMISSION_PCT IS NULL THEN 1.5 ELSE COMMISSION_PCT END AS COMMISSION_PCT from hr.employees;

select FIRST_NAME , Hire_date , to_char (Hire_date,'day') from hr.employees;

• To_char used to convert.

Notes:

1. 19c and 23c --> Oracle
2. what is normalisation ?
3. Performance tuning ?
4. how to delete the duplicate ?
5. case is faster than decode , both are general functions .
6. D --> Day

Number Function

• Round
• Trunc
• Mod
• Ceil
• Floor
• Abs
• Sign

Round

• If there is number => 5 after the decimal point , it will round off to the next number otherwise it will remain the same.

select round(67.34) from dual;
select round(67.49) from dual;
select round(67.57) from dual;

Trunc

• It wont see any number after decimal.
• Trunc is used only with the NUMBERS.
• Lets do one more operation with trunc --> trunc(56.89898,2) means it will take the two digit after the decimal.

select trunc(67.7846434) from dual;
select trunc(67.10001) from dual;
select trunc(67.011234) from dual;

select sysdate from dual; --> it will display time also
select trunc(sysdate) from dual;

Mod

• It will give the remainder.

select mod(10,7) from dual;
select mod(61,10) from dual;
select mod(6,2) from dual;

Ceil

• It will go to the next number, whatever may the number.

select ceil(67.7846434) from dual;
select ceil(67.00001) from dual;
select ceil(67.011234) from dual;

Floor

• It will crop the decimal.
• Then what is the use of Trunc ? In trunc we can use, how many decimal can be included. Hope you remember.

select floor(67.7846434) from dual;
select floor(67.00001) from dual;
select floor(67.011234) from dual;

select floor(67.7846434),trunc(67.7846434,1) from dual;
select floor(67.00001),trunc(67.00001) from dual;
select floor(67.011234) , trunc(67.011234,0) from dual;

Abs

• Only + & - are allowed.
• It will remove the sign.
• There is one more function similar to this sign i.e SIGN, it will give 1 & -1.

select abs(67.7846434), abs(-67.7846434), abs(10-15), sign(-10) , sign(+67.89) from dual;

Date Function

• add_months
• months_between
• next_day
• last_day

add_months

• if you want to add MONTHS then this function is used.

select sysdate, sysdate+2 , sysdate+20 , sysdate-10 from dual; -- Days increases
select sysdate from dual;
select add_months(sysdate,1) from dual;
select add_months('01-DEC-2024',1) from dual;
select add_months(sysdate,-1) from dual; -- months increases

select * from hr.employees where hire_date < add_months(sysdate,-276) -- hired before 23 years , 276 is the month

months_between

• Displays the no of months , sometime it will give in negative number that means subtraction happens between months Jan is 1 and Dec is 12.
• Even the date will be considered.

-- select months_between((01-01-20024), (01-12-2024)) from dual;
-- select months_between((01-jan-2024), (01-dec-2024)) from dual;
select months_between(('01-JAN-2024'), ('01-DEC-2024')) , months_between(('01-DEC-2024'), ('01-jan-2024')) from dual;
select months_between(('17-JAN-2024'), ('01-DEC-2024')) , months_between(('01-DEC-2024'), ('25-jan-2024')) from dual;

• If you don't like the decimal , what you can use ? For sure I forgot . Can't remember very quickly . After reading my notes , found its "TRUNC or FLOOR".
• Find my age ? How ?

select months_between(('01-DEC-2024'), ('01-DEC-1989'))/12 from dual;
select months_between(('01-DEC-2024'), ('01-DEC-1989'))/12 AS AGE from dual;

next_day

• Also will order you can specify.
• Sunday --> 1 , Saturday --> 7

select sysdate from dual ;
select next_day(sysdate,'sunday') from dual;
select next_day(sysdate,1) from dual;

last_day

• To find the last day of the month.

select last_day(sysdate) from dual;
select last_day('01-feb-2024') from dual;

General

• greatest
• least
• distinct
• unique
• case
• decode
• concat
• ||

Greatest

select 12,67,98,90,100 from dual;
select greatest(12,67,98,90,100) , least (12,67,98,90,100) from dual;

Least

select 12,67,98,90,100 from dual;
select greatest(12,67,98,90,100) , least (12,67,98,90,100) from dual;

Distinct & Unique

• It will check the duplicate.
• Both will be same .

select unique FIRST_NAME , last_name from hr.employees;
select distinct FIRST_NAME , last_name from hr.employees;

Concat & ||

select concat(concat(FIRST_NAME,LAST_NAME),salary) from hr.employees;
select FIRST_NAME || LAST_NAME ||' '|| salary from hr.employees;

Note

1. In the Date function --> only months_between --> it returns number.

Single Row Function

• 7 types
• Case Manipulation Function : upper , lower & Initcap
• Character Manipulation : Instr , Substr , Length , Replace , Reverse , Translate , Trim , Ltrim , Rtrim , Lpad & Rpad.
• Number
• Date Function
• General Function
• Null
• Conversion

Initcap

• First letter in caps

> -- select * from HR.COUNTRIES;
> -- select * from HR.COUNTRIES where COUNTRY_NAME='BRAZIL'
> -- select * from HR.COUNTRIES where COUNTRY_NAME=Initcap('BRAZIL')
> -- select *, UPPER(COUNTRY_NAME) from HR.COUNTRIES where COUNTRY_NAME=Initcap('BRAZIL')
> -- select HR.COUNTRIES.*, UPPER(COUNTRY_NAME) from HR.COUNTRIES where COUNTRY_NAME=Initcap('BRAZIL')
> -- select HR.COUNTRIES.*, UPPER(COUNTRY_NAME) AS COUNTRY from HR.COUNTRIES where COUNTRY_NAME=Initcap('BRAZIL')
> select C.*, UPPER(COUNTRY_NAME) AS COUNTRY from HR.COUNTRIES C where COUNTRY_NAME=Initcap('BRAZIL')

Instr

• Tells the number of the Position.
• To find Letter position.
• Number as Output.
• Number wont change when to count from front or reverse.
• 1 -- > Front
• -1 --> Reverse
• Last one in the bracket is *OCCURRENCE *.
• instr(letter, which one you need , front or reverse , occurrence )

-- select 'APPLEE' FROM dual;
-- select INSTR('APPLEE','P',1,1) FROM dual;
select INSTR('APPLEE','P',1,2) FROM dual;
select INSTR('APPLEE','P',-1,2) FROM dual;

Substr

• Character as output.
• substr ( 'letter' , start , how many character/letter u need to be displayed )
• substr ( 'letter', 2 ) --> it will print all letters after 2.
• '-' reverse.

select substr('APPLEE',3,2) FROM dual;
select substr('APPLEE',-3,2) FROM dual;

Length

• To find the length of the character.
• For NULL there is no length.
• Space will be considered.

-- select length('brettlee') from dual;
-- select DEPARTMENT_NAME, length(DEPARTMENT_NAME) as no_of_letters from hr.DEPARTMENTS;
select '',length(''),length(null) from dual;
select '',length(' '),length(null) from dual;

Reverse

• Numbers,it won't accept for reverse function.

Replace

• replace the word.
• replace('word','exact_word','replace_with_this');
• If the second argument is not present in the first then replace won't work.
• word to word we need to use 'Replace'.
• character/letter to character/letter use 'Translator'.

-- select replace('hcl technology','technology',techno) from dual;
-- select replace('hcl technology','technology','techno') from dual;
-- select replace('hcl technology','soft','techno') from dual;
-- select replace('hcl technology','technology') from dual;
select translate('hcl','hl','ts') from dual;

Trim

• space can be trimmed.

Lpad

• to add.
• To add in both sides that's the task.

-- select lpad((rpad('infosys','9','&')),'11','&') from dual;
select rpad(lpad('INFOSYS',length('INFOSYS')+2,'*') ,length(lpad('INFOSYS',length('INFOSYS')+2,'**'))+2,'*') from dual;

Notes

• dual is a dummy table
• always single quotes for string.
• Data in the table is sensitive --> always remember.
• Query you can write in any format but data is sensitive.
• Can we put , after * ? Yes , you need to put the table name in the * then there wont be error.
• AS is the column name or alternate name.
• AS can be used only in the select column.
• AS can't be used for table.
• Alias name won't be saved in database.
• Alias = Alternate Name
• String = Group of letters

nmcli - NetworkManager Command Line Interface

• The nmcli utility can be used by both users and scripts for controlling NetworkManager.
• nmcli is a command-line tool which is used for controlling NetworkManager.
• nmcli command can also be used to display network device status, create, edit, activate/deactivate, and delete network connections.

List of commands

nmcli general status

nmcli connection

nmcli connection modify <name> i<tab>

• Once modified & then bring the nmcli UP.
• nmcli connection modify "name" ipv4.addresses 192.12.123.10/10 ipv4.gateway 192.12.123.254 ipv4.dns 192.12.123.254
• Now bring it UP.

nmcli connection up "<name>"

Notes

• Nameserver is also referred as DNS.
• Give the tab always so that option appears , its like what is next command or word.
• IP address , Netmask , Gateway & Nameserver.
• How to assign number after "/" in the IP address --> TBD

Virtual Machine

Physical servers , Virtual Machine & Containers.

Manual and Dynamic allocation can be done in Virtual Machine.
Type 1 : Its like Enterprise --> In Azure , OCI , etc this concept is used.
Type 2: Mostly which we are using eg., laptop.

• Containers is a concept.
• Docker uses containerisation concept.
• Docker Engine is like Kitchen .
• Docker File is like notes for recipe.
• Sambar recipe for all i.e Docker Image . One cup for each person i.e Docker Container.
• Pantry --> Docker Registry --> all images are available.
• Chef --> Docker Daemon --> It will be running continuously.
• Docker Network and Volumes --> Its Dinning Area.

docker        --> To check whether docker is installed or not.
docker images --> List all the images which are present.
docker pull hello-world ( if no version is provided then it will take the latest version only )

docker pull hello-world:nanoserver-1809

Now run the image so that a container will be created ,

docker run hello-world

Check the container list,

docker ps -a
( only ps will show the running container )

To delete the container,

docker rm -f <container-id>

Sites or URL

https://hub.docker.com/r/tensorflow/tensorflow/tags

Notes

1. Image ID will be same for all.
2. Container ID will be created each time which will be a new number.
3. While running any image without version , then it will take the latest one only.
4. If latest image is not there in the server then it will pull and then run the image. So use latest wisely.

Interview Questions

1. What is VM ? A software emulation of a physical computer.
2. What does hypervisor do in a virtualisation ? It allows VM to run on a physical host by managing their resources.
3. What is Docker primarily used for ? Containerising applications to ensure portability.
4. Advantage of using containers ? Containers are portable & can run across different environments.
5. which type of hypervisor runs directly on Physical hardware ? Type - 1.
6. Difference btw VM & Container ? Containers share the host OS , while VM have their won OS.

Need to know

VMware ESXi
vCenter - Server Management Software - vCenter
VMware Workstation

Application Programming Interface

• Its like server/Waiter in a HOTEL refers to API.
• Interface--> Allow control of interaction with obstruction --> API is for Programmers.

• Client & Server --> Can be any language.
• All web application will have API.

1. SOAP-API --> Simple Object Access Protocol --> Function/Method needs to be written so that one can call it . Its like a WRAPPER.
2. REST-API --> REPRESENTATIONAL STATE TRANSFER --> If you know the concept of REST then everyone will write the same.

• CURL ( cURL ) --> Command line CLIENT for testing and developing API.
• Postman ( its a Graphical version based ) --> its a company now. 1 year we can use it for FREE.

Continue with REST

• Method ( GET , POST , PUT/PATCH , DELETE )
• Address / Endpoint (URL)
• Path

• Query/Search Parameters --> Starts with ? followed by KEY & VALUE.
• Authorization
• Header & Body
• Request Body can be sent via JSON Data types, which is default.
• Response --> 200 OK or 404 Not found or 201 created along with Headers and body.
• Layered --> It will have all configured LB & everything.

Why REST is popular ?

1. Simple & Standardised --> Everyone will use the same method to use.
2. Scalable & Stateles. Eg., It wont store anything. Each time it request as fresh request.
3. High Performance & Caching.

CRUD

Continue with REST

• https://api.github.com/users/
• users --> it will be mostly plural , followed by SINGULAR --> username.
• https://jsonplaceholder.typicode.com/todos

• https://jsonplaceholder.typicode.com/todos/1

import requests
api_url = "https://jsonplaceholder.typicode.com/todos/1"
response = requests.get(api_url)
print(response)
response.json()
print(response.json())

All Request

import requests
api_url = "https://jsonplaceholder.typicode.com/todos/"
response = requests.get(api_url)
print(response)
response.json()
print(response.json())

Print in proper format or PrettyPrint

Post method

• For this API , the best example is BANKING with website & Mobile APP. ( Why ? In internet banking you will pass all values and its the DB then gets the response which is obvious BUT think of the APP in your mobile which is lite weight app which will use API calls to DB and does the same operations )

• Web Scraping --> you can get only DATA but you can't update or modify. Each website we need to write different web scraping code. EACH WEBSITE WILL HAVE DIFFERENT CONTROL.

• ? & key = value & key = value

• Now with KEY ,

Important Links

• https://realpython.com/api-integration-in-python/
• https://jsonplaceholder.typicode.com/ --> API playground.
• Free open API for weather --> https://open-meteo.com/ --> curl "https://api.open-meteo.com/v1/forecast?latitude=52.52&longitude=13.41&current=temperature_2m,wind_speed_10m&hourly=temperature_2m,relative_humidity_2m,wind_speed_10m"
• https://tamilpesu.us/en/number/ --> https://tamilpesu.us/en/number/
• https://www.slideshare.net/slideshow/an-introduction-to-rest-api/76492672
• https://speakerdeck.com/cli4d/introduction-to-rest-apis
• https://www.slideshare.net/slideshow/what-is-rest-api-rest-api-concepts-and-examples-edureka/174179563
• https://jiminbyun.medium.com/building-python-scripts-for-rest-api-calls-a-practical-guide-2-9-3ac9ca1d701a
• https://github.com/vinta/awesome-python ( Important Site )

Important Notes

• ''' ... ''' --> Commenting multiple lines.
• pprint --> its like making JSON beautification , for this we need to use " import pprint ".
• curl -X GET https://jsonplaceholder.typicode.com/todos/1
• curl -X POST -d {"userID":101} https://jsonplaceholder.typicode.com/posts
• What is BROWSER automation for sites ?
• Try this and get the output --> https://www.instructables.com/Get-Weather-Data-Using-Python-and-Openweather-API/
• All social media platform expose their service with API.
• Try this also --> http://tamilpesu.us/number/34?type=IN --> website --> https://tamilpesu.us/en/number/
• POSTMAN --> good platform with UI, mostly used.
• https://en.wikipedia.org/api/rest_v1/ --> wikiepedia rest api
• md --> Markdown
• Super Market Example :-) You need to search each and everything, we wont get it immediately. https://github.com/vinta/awesome-python --> this as many codes , so search one by one.

Reference

https://www.youtube.com/watch?v=HW9E4TYoYXU&list=PLiutOxBS1Mizte0ehfMrRKHSIQcCImwHL&index=38

** ACCEPT THE SLOWNESS **

OUTPUT REDIRECTION

Any command in Linux will have 3 data streams ,

1. STDIN --> 0
2. STDOUT --> 1
3. STDERR --> 2

These numbers are called DESCRIPTOR.

>--> Redirection to a file.
>> --> Upend to the last line.
2> --> This will get the error.
2>> --> Upend the error.
&> --> Both output & error will be stored in the file.

PIPE SYMBOL or PIPE REDIRECTION

• First command output is the input to the second command.

• cat file1 | less --> Its like Booklet ( so ENTER ENTER --> it will turn like a page , Down arrow and Up arrow )

• cat file1 | more

PATTERN MATCHING or CLOBBING

• * ? [] ! {}
• touch file{a..z}
• touch file{1..100}
• touch file{11,12,17}
• ls -l file[abcd] --> exact match pattern
• ls -l !(file) --> list files which is not having any name with file. Its just exclude.
• To remove all files in one command rm -r file*
• ls -l ??? --> Single character matching
• ls -l ?file --> Character matching
• ls -l file?
• ls -l /var/log/???

VI

• sudo yum install vim
• :set number
• dd --> delete
• copy & past --> yy & c
• cut & past --> dd & c
• 3 & dd --> it will delete 3 lines from the cursor
• shift + g --> Last line
• :1 --> it will go to the first line

• Patching is in-place NOT Upgrade.
• Oracle release Quarterly Patches

• Base software's( GI Software's & Oracle Software's ) can be downloaded in Oracle Site.
• Patches can be downloaded from Support Site and also it needs a license.
• Patches are applied on top of GI Home & Oracle Home.
• What these Patches contains ?

• Where to check what patches are applied ?
  /u01/app/oracle/product/19.0.0.0/dbhome_1/OPatch/opatch lspatches
  29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
  29517242;Database Release Update : 19.3.0.0.190416 (29517242)
  OPatch succeeded.

• ASM Home

/u01/app/19.0.0.0/grid/OPatch/opatch lspatches
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
29517247;ACFS RELEASE UPDATE 19.3.0.0.0 (29517247)
29517242;Database Release Update : 19.3.0.0.190416 (29517242)
29401763;TOMCAT RELEASE UPDATE 19.0.0.0.0 (29401763)

• DB Home

/u01/app/oracle/product/19.0.0.0/dbhome_1/OPatch/opatch lspatches
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
29517242;Database Release Update : 19.3.0.0.190416 (29517242)

• 19.3.0.0.190416 --> 190416 --> 2019 - April - Date.
• Second digit is RU. i.e 19.3
• RU will be increasing 19.2,19.4 etc
• Here : 19.3 is the base release. --> GI Home and ASM Home.
• These are cumulative patches --> all patches are not to be applied one by one , you can jump to any patches.

Overview plan

How to download & Patch details ?

• Search like below, Critical Patch Update (CPU) Program Jan/Apr/Jul/Oct 2023 Patch Availability Document (DB-only)
• Section 3 which is for Oracle Database.
• 3.1.7 --> 3.1.7.3 which is 19c
• GI Patch will contain DB patch also.
• Now go to the READ ME file and check the table 1-2 which will say how many sub patches need to be applied.

OPatch Tool

• We need to patch 3 home ,
• GI Home
• DB Home

• Database Home

• All these home will be patched via OPatch Tool.

Best Practice

• SDLC ( DEV --> UAT --> PROD )
• Don't rollback of any previous patches.
• Check the conflict check for all the patches using below command, ( repeat for all 5 patches or sub patches )

/u01/app/19.0.0.0/grid/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/patches/19.17/34416665/33575402 -oh /u01/app/19.0.0.0/grid

/u01/app/oracle/product/19.0.0.0/dbhome_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/patches/19.17/34416665/34419443 -oh /u01/app/oracle/product/19.0.0.0/dbhome_1

OPatch Patch

• 6880880 version.
• Upgrade can be done by downloading.

Steps to apply patch

DB Home patching steps

1) Stop DB Home as root user
2) grant permission on patch directory as root user
3) As oracle user do prechks 
Do OPatch version and current lspatches checks
Conflict pre checks:    
System space prechk:
4) Apply Patch:
5) Post verification
6) Start ORACLE_HOME as root user

GI Home patching steps

• Stop the GI home as root user, which means its a HAS --> Standalone.

1) Stop GI Home as root user
2) Unlock the GI Home as root user
3) grant permission patch directory as root user
4) As grid/oracle user do prechks 
Do OPatch version and current lspatches checks
Conflict pre checks:    
System space prechk:
5) Apply Patch:
6) Post verification
7) Lock and start GI 

Prechecks

- backup GI Home --> tar -cvzf grid.tar.gz grid ( cd /u01/app/19.0.0.0/ )
- backup Oracle Home --> tar -cvzf dbhome_1.tar.gz dbhome_1 ( cd /u01/app/oracle/product/19.0.0.0 )

• System space check

/u01/app/19.0.0.0/grid/OPatch/opatch prereq CheckSystemSpace -phBaseDir /u01/patches/19.17/34416665/33575402 -oh /u01/app/19.0.0.0/grid

/u01/app/oracle/product/19.0.0.0/dbhome_1/OPatch/opatch prereq CheckSystemSpace -phBaseDir /u01/patches/19.17/34416665/34419443 -oh /u01/app/oracle/product/19.0.0.0/dbhome_1

• df -h /u01/app/oracle/product/19.0.0.0/dbhome_1/

Patching

/u01/app/19.0.0.0/grid/OPatch/opatch apply -oh /u01/app/19.0.0.0/grid -local -silent /u01/patches/19.17/34416665/34419443

/u01/app/oracle/product/19.0.0.0/dbhome_1/OPatch/opatch apply -oh /u01/app/oracle/product/19.0.0.0/dbhome_1 -local -silent /u01/patches/19.17/34416665/34419443

• silent --> default is YES , don't wait for command.

Important Notes

• Don't download patches until you have license , it may be fine as they are unauthorised to download.
• List of patches terms used in Oracle :

https://docs.oracle.com/cd/E24628_01/doc.121/e39376/glossary.htm#BGBCGDDF