Lets use SSH to do the practical as won't get direct VM access to do it in industry. We using MobiXterm(simillar to putty) to connect to our Oracle Linux using SSH.

---

---

After the patching these will change the version.

select banner, banner_full, banner_legacy from v$version;

---

---

select instance_name, host_name, version, version_full from v$instance;

---

---

Capture all the Logs because all of that we have to share with the stakeholders.

Downloaded the opatch file from My oracle support portal. (p33859214_190000_Linux-x86-64.zip)

Downloaded the OPatch utility tool from My oracle support portal. (p6880880_190000_Linux-x86-64.zip)

We will do this 2 later.

We have to change the file and directory ownership to oracle user as required.

select name, db_unique_name, open_mode, database_role, log_mode, flashback_on from v$database;

---

---

select instance_name, status, database_status, to_char(startup_time, 'dd/mm/yyyy hh24:mi:ss') as startup_time from v$instance;

If an interviewer asks can you say when the database last restarted. There is a startup_time column in the v$instance view. We can check that from there.

---

This should be enabled if it is a data guard environment. It's a parameter in data guard environment, what changed you do to the Database servers, a log will be generated and will shift to the standby side.

An invalid object is something created in the database that we can’t use and has become stale. It serves no real purpose, just taking up space and affecting other resources. An example an index, index is something we use to find data in a table quickly (like in a 1000 page book, we can check the index in the first page to find the page number of the chapter we need quickly), if the index become invalid, we won't be able to use it and resort to do a full table scan, on that scenario we have to validate the index so that we can use it again.Objects can become invalid for various reasons, such as migrations, data corruption, or similar naming conventions.

During database upgrades, deployments, and patching activities, invalid objects can appear and affect database performance. That’s why it’s crucial to identify any invalid objects in the database before we start the patching.

select owner, object_type, count(*) as invalid_count from dba_objects where status = 'INVALID' group by owner, object_type order by owner, object_type;

---

---

There is no rows because currently there is no invalid objects in our database, if there was any invalid object then there will be output with this select statement.

If there were any invalid objects then we have to validate it. There is a created PLSQL script which we have to run utlrp.sql inside oracle home/rdbms/admin.

@/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/utlrp.sql or @?/rdbms/admin/utlrp.sql (as inside sql prompt ? means oracle home location)

---

Like in windows there is a thing called recycle bin, where if we delete something it will go there first, and we can restore from there. In database there is also a recycle bin.

Like you have deleeted some objects from the database like an index, a table etc, now during performing some migrations, some patching activities etc, its bette to stay in the safer side so that there is no impact like those things getting restored. So its better to purge anything in the recycle bin first.

purge recyclebin;

---

---

See said to unzip the patch file inside a directory of your choice. Then verify the unzipped directory and file structure inside.

---

---

Then it said to look into the component specific readme inside 33803476 and 33808367. All the necessary commands are written there, and if the commmand are given in $ prompt consider it has be triggered by oracle user, and if # prompt then by the root user.

---

It is written that we need 12.2.0.1.29 OPatch utility release version for installing this patch. Se lets see the current OPatch utility tool version inside out database server. Use ./opatch lsinventory | grep desc or ./opatch version to see that.

---

---

---

Our database serve has 12.2.0.1.17 version and we need 12.2.0.1.29. So we need to put the new version of OPatch utility tool in our database server.

Current we already a version in our DB Servers. We will take a backup of it and not delete it.

---

---

We changed the name of the file to backup and current date so that we can recognize when we did it. Thats it. Now we have to unzip the new OPatch utility tool here.

---

---

---

See the OPatch utility has been upgraded to 12.2.0.1.30. Now we can use that to install the new patch.

---

---

According to the readme the next step is to validate the oracle inventory. We will check whether our software s are in sink or not, whether the binary files, all the inventory files are in sink or not. To validate the inventory of the OPatch utility, whether it is successfully upgraded or not if yes then while applying the patch whether it will work or not

$ORACLE_HOME/OPatch/opatch lsinventory -detail -oh $ORACLE_HOME

---

---

---

This check shows the result as success (OPatch succeded), if it was failure then we will have to look into it and correct this. To correct this, download the OPatch utility tool again and unzip again.

To apply the patch, it must be accessible from all nodes in the Oracle cluster. Download the patch and unzip it to a shared location. This directory must be empty and cannot be /tmp. Additionally, the directory should have read permission for the ORA_INSTALL group.

Create a new directory, with no file inside it, and unzip the file in it. All tbe oracle users must have read write access on that directory.

---

---

---

We have changed the owner to oracle user.

The next step is checking whether the OPatch utility is compatible to apply this patch or not.

---

---

Every command is writtem here. Now for database with ASM configured we need to run the grid home user command. As we dont have ASM we will need to run the commands of oracle home user.

Command: $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/patch/33859214/33803476/33806152

---

---

$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir 33803476/33815596

---

Check if enough free space is available on the ORACLE_HOME filesystem for the patches to be applied as given below:

---

It said to create a file: /tmp/patch_list_dbhome.txt and add <UNZIPPED_PATCH_LOCATION>/33803476/33806152 and <UNZIPPED_PATCH_LOCATION>/33803476/33815596

---

---

The run the command:

$ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseFile /tmp/patch_list_dbhome.txt

---

---

The test has succeeded because we have a minimum of 8 GB space which is required.

---

We need to open those logs and see what type of errors are coming, maybe archive logs are enabled, space is not sufficient etc.

---

---

Our pre-requisites are completed.

It creates a session that will run the job and will not be over until the job finishes, even if connection from our side interrupts. Command is: screen -S session_name and screen -ls (we can see if any screen session is going on or not)

---

---

Now run

$ORACLE_HOME/OPatch/opatch apply

This is because the OPatch utility tool which will apply the patch is in there.

---

---

Running it outside is giving error, lets go inside the sub-patch directories.

---

---

This again showed error, we go inside even more where there is the actual files.

---

---

This time the command run but it didnt succeed. Bacause before applying the patch our database must be shutdown. So if we got approval from the management to shut it down, we need to complete the pre-requisites much before that time. The apply the patch when they give approval for shutting the database.

---

---

---

See the patch succeeded, the database RU changed.

---

This was applying the patch /u01/patch/33859214/33803476/33806152 or 33806152. But the patch /u01/patch/33859214/33803476/33575402 or 33575402 showed some warnings. Then I applied the patch /u01/patch/33859214/33803476/33815596 or 33815596. It succeded.

---

---

select banner, banner_full, banner_legacy from v$version;

---

---

select instance_name, host_name, version, version_full from v$instance;

---

---

See all of this has changed from before.

There is a view dba_registry _sqlpatch, do a select statement on this.

---

See the new patch is not reflected on the database. To reflect that in the database go to the $ORACLE_HOME/OPatch and run ./datapatch -verbose, this updates the database registry so that the new patch is reflected there.

---

---

This is currenlty running. When running the datapatch verbose see the dba_registry_sqlpatch. See the new patch is currently initialized.

---

---

The datapatch verbose got completed.

---

---

---

This is the current status of the dba_registry_sqlpatch after datapatch verbose got completed.

---

19.15 or 19.28

We can see that from opatch utility tool.

Pre-requisites:

Check current database status. 
Archive log status
Force logging
Invalid objects, if found then revalidate
Purge recycle bin:


Patching activities(2-3 hours downtime)

DB will be down
Listener will be down 
No LRT
Application shouldn't be connected with the database
Run opatch apply


Post patching:

Start DB
Start listener 
Ast application team to start the application
Run utlrp.sql 
Run datapatch verbose

From OS these 2 commands:

./opatch lspatches

./opatch lsinventory

We have seen this already.

From DB, this view,

dba_registry_history

A very important view, we can track what was the last patch applied to the DB, it got failed or it got success.

dba_registry_sqlpatch view:

Select patch_id, action, status, description, action time from dba_registry_sqlpatch order by action_time desc;

This is shows the old version. To reflect the new patch in the regiatry run datapatch verbose.

Datapatch verbose, this things will update the database registry immediately that whatever patch was applied in the DB layer will reflect in the database registry.