How to download the latest OPatch with a wget command?

First place to start is MOS Oracle Support Document 274526.1 

https://support.oracle.com/epmos/faces/DocumentDisplay?id=274526.1

Go to patch 6880880 that brings you to the download page.

There is no wget.sh script to download, so that is what we will create ourselves.

Select the correct version to download, and then Right-click the download button and copy the hyperlink.

Paste the hyperlink in a fresh notepad, and enclose it with quotation marks:

"<link>"

Add the following in front, to make your command:

wget --http-user=<MOS-user> --http-password=<MOS-password> --no-check-certificate --output-document=latestopatch.zip "<link>"

Paste this in your Linux session and your opatch is downloading.

 

Upgrade Database System to Linux 8 on OCI using console

When upgrading to Linux 8 of your Database System (Oracle Base Database), using the console, you will lose some settings that you have made manually.

https://docs.oracle.com/en/cloud/paas/base-database/upgrade-dbs/index.html#GUID-32241D49-BA5B-42D3-90B6-83D0A50480C9

What will you CERTAINLY lose:

* changes in /etc/fstab LOST

* extra directory in /opt/ LOST

* all stuff in /home/oracle  (scripts, changes in .bash_profile)

* extra mounted Filesystems. If they are external, you will need to put them back in /etc/fstab and remount.

* /etc/cron.allow is gone

* crontab of user oracle is gone (probably all crontabs!)

* contents of database directory objects if they are not on /u01, /u02, /u03!!

What goes ok:

• extra stuff stored in /u01 /u02 /u03  (if you use Filesystem storage)
• extra entries in tnsnames.ora  (which is in $ORACLE_HOME on u01)
• The external /mnt to a filesystem is gone, but the filesystem itself is ok. Just remount it.

MAKE SURE TO BACKUP ALL BEFORE YOU START UPGRADE

Update your Oracle database - there is more

*updated post 2024-Jan-16

When you want to keep your Oracle database fully up-to-date, you may think that the quarterly patches are perfect:

Update quarterly CPU   https://www.oracle.com/security-alerts/#CriticalPatchUpdates

But the following are not automatically included in the CPU:

+ Monthly Recommended Patches (MRPs)   Primary Note for Database Proactive Patch Program (Doc ID 888.1)

+ JDK patches JDK and PERL Patches for Oracle Database Home and Grid Home (Doc ID 2584628.1)

+ OPatch update How To Download And Install The Latest OPatch(6880880) Version (Doc ID 274526.1)

+ Update Datapump bundle patch Data Pump Recommended Proactive Patches For 19.10 and Above (Doc ID 2819284.1)

+ Update preupgrade.jar  How to Download and Run Oracle's Database Pre-Upgrade Utility (Doc ID 884522.1)

+ Update Apex  https://www.oracle.com/tools/downloads/apex-downloads/

+ Update AHF/TFA  Autonomous Health Framework (AHF) - Including TFA and ORAchk/EXAchk (Doc ID 2550798.1)

So.... a lot of updates!

How to upgrade/patch Oracle Enterprise Manager Cloud Control

In this post, I'd like to give you the main lines of upgrading and patching Cloud Control

UPGRADING

Of course, your situation may be different. But if you have a simple Cloud Control setup, you may work with the following guidelines.

You should be working with Cloud Control 13c, specifically with 13.5

If you are on a lower version, first upgrade it to 13.5. I would suggest that 13.4 is fine as well, but an upgrade could be made nevertheless. 

If you need to upgrade, download the 13.5 version via edelivery (wget is preferred to do so).

When you start it up, you will get the option to upgrade. An example of the steps is on:

https://eclipsys.ca/oracle-enterprise-manager-upgrade-from-13-4-to-13-5/

This will create a whole new tree structure for gc_inst and OMS-home folders. So you need a lot of space.

You can follow all steps in the upgrade guide:

https://docs.oracle.com/en/enterprise-manager/cloud-control/enterprise-manager-cloud-control/13.5/emupg/introduction-upgrading-enterprise-manager-system-13c.html

After the installation, you can remove the old directory structures, but be careful with that.

https://docs.oracle.com/en/enterprise-manager/cloud-control/enterprise-manager-cloud-control/13.4/emupg/deleting-old-oms-home.html

After the upgrade, you will need to upgrade all the agents. 

Start with the Central Agent (on the OMS host itself). You can easily upgrade agents with the cloud control option under Setup>Manage Cloud Control>Upgrade Agents

After the central agent, you need to upgrade all target agents. You can do a few/some/a lot in one go.

Note that upgrade agents requires a root.sh script to be run. 

Note that after the upgrade of the agent you should run post-agent-upgrade steps (which cleans the old agent home).

Upgrading agents is a very smooth process that will not require any downtime (just briefly of the agent itself).

PATCHING

Now the patch áfter the 13.5 upgrade. Because you now have 13.5.0.0.0 installed on OMS and all the agents. 

There is a monthly patch, which can patch the OMS to 13.5.0.10.0 for example. This fixes a lot of bugs and issues.

You can find the patch in:

https://support.oracle.com/epmos/faces/DocumentDisplay?id=2902989.1

After downloading the latest patch (wget preferred), you should also download the latest OMSPatcher tool (follow the readme of OMS patch).

And you should also create a property file, which helps you installing the patch.

With the property file created and OMSPatcher tool upgraded, you can now 

omspatcher -apply -analyze -property_file /pathto/mypropertyfile.txt

emctl stop oms

omspatcher -apply -property_file /pathto/mypropertyfile.txt

The patch is in-place and gets you to OMS 13.5.0.x.0 (last patch).

Hope this helps you in your patching. Always follow the steps in Oracle Support documents!

Quick install Oracle 19c+RU

Following Mike Dietrich's post https://mikedietrichde.com/2020/07/28/install-and-patch-in-one-single-action-with-oui/   I tried to do a quick install of 19c with the latest patchset (July 2022), in one go.

I didnt want to use any graphical interface, so a silent install would be needed.  Below I quickly note down my steps, use them at your own risk. 

I start with a compute node ('Instance') on OCI, with a OEL 8 image. 

Logon (ssh key) as opc user, and then...

sudo  yum -y install oracle-database-preinstall-19c

sudo mkdir /u01

sudo chown oracle: /u01

sudo su - oracle

mkdir /u01/software

cd /u01/software

Download 19.3 + patch + opatch:

The base installation is the V982063-01.zip. This is downloaded with the wget.sh script that is available from edelivery.

The quarterly patch, below is RU July 2022, that is found on MOS, and downloaded with wget script.

The OPatch, you can download in any way you like. I usually make a small wget script and use the link that can be derived from MOS.

[oracle@host01 software]$ ll *zip

-rw-r--r--. 1 oracle oinstall  124211908 Sep  1 13:33 newopatch.zip

-rw-r--r--. 1 oracle oinstall 1684302607 Jul 19 21:52 p34160831_190000_Linux-x86-64.zip

-rw-r--r--. 1 oracle oinstall 3059705302 Apr 23  2019 V982063-01.zip  

unzip -q p34160831_190000_Linux-x86-64.zip

mkdir -p /u01/app/oracle/product/19.0.0/dbhome_1

export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1

cd $ORACLE_HOME

unzip -q /u01/software/V982063-01.zip

mv OPatch/ OPatch.prev/

unzip -q /u01/software/newopatch.zip

Now create a responsefile /home/oracle/db_patched.rsp, as below example. After that,

export CV_ASSUME_DISTID=OEL7.8

cd $ORACLE_HOME

./runInstaller -silent -applyRU /u01/software/34160831/34133642 -applyOneOffs /u01/software/34160831/34086870 -responseFile /home/oracle/db_patched.rsp

---[ responsefile ]------------------

oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v19.0.0

oracle.install.option=INSTALL_DB_SWONLY

UNIX_GROUP_NAME=oinstall

INVENTORY_LOCATION=/u01/app/oraInventory

ORACLE_BASE=/u01/app/oracle

oracle.install.db.InstallEdition=EE

oracle.install.db.OSDBA_GROUP=dba

oracle.install.db.OSOPER_GROUP=oper

oracle.install.db.OSBACKUPDBA_GROUP=backupdba

oracle.install.db.OSDGDBA_GROUP=dgdba

oracle.install.db.OSKMDBA_GROUP=kmdba

oracle.install.db.OSRACDBA_GROUP=racdba

oracle.install.db.rootconfig.executeRootScript=false

---------------------------------------

Run as root:

/u01/app/oraInventory/orainstRoot.sh

/u01/app/oracle/product/19.0.0/dbhome_1/root.sh

Websites needed for install of Oracle Enterprise Manager.

For installing Enterprise Manager 13c, with our host behind a firewall, we had to open some ports.

We came up with the following list, which should be enough.

oauth-e.oracle.com

aru-akam.oracle.com

ccr.oracle.com

login.oracle.com

support.oracle.com

updates.oracle.com

yum.oracle.com 

edelivery.oracle.com

Hope this helps for future installs....

Remove Apex from 12.1 CDB and PDBs

When Apex is installed on 12.1 Oracle DB and you see it in the CDB and PDB's, take care when removing it.  I have found the below steps usefull. In the Oracle documentation and on several blogs the apxremov.sql is simply run on the CDB and that should be it. But if it is installed in all PDB's too (not used, just installed) it wont work removing it like that.

Main steps to remove it completely, details below:

1. Remove from all PDBS
2. Remove from PDB$SEED  (otherwise it will give errors when creating new PDBs)
3. Remove from CDB

If you don't follow this order, you risk that damaging your database and need to recover/restore.

Note:

You need to run the _oracle_script parameter to prevent the ORA-28014 error.

ORA-28014: Cannot Drop Administrative Users (Doc ID 1566042.1)

Details:

-- From CDB :
create restore point BEF_APEX_REMOVE guarantee flashback database;

An extra backup of database is highly recommended.

1 Remove from all PDBs (one-by-one):

-- ======== Generic removal ===========
alter session set "_oracle_script"=true;
@?/apex/apxremov.sql

-- cleanup invalid synonyms (clean.sql):
-- You may pre-create the clean.sql file

set lines 120
set heading off feedback off
spool drinvpub.tmp
select 'ALTER SESSION SET "_oracle_script"=true;' from dual;
select 'drop public synonym '|| object_name ||';'
from dba_objects
where owner='PUBLIC'
and status='INVALID'
and (object_name like 'APEX%'
 or object_name like 'WWV_FLOW%'
 or object_name like 'WWV_%'
 or object_name like 'HTMLDB%'
 or object_name like 'DEVELOPMENT_SERVICE_%'

 or object_name in ('V','F','Z','NV','P','WS')

)

;
spool off
set heading on
set feedback on

@drinvpub.tmp

--- Check:
col comp_name format a40
select comp_name,status from dba_registry

2.  Remove from PDB$SEED

-- on CDB run:

alter session set "_oracle_script"=true;
alter pluggable database PDB$SEED close;
alter pluggable database PDB$SEED open read write;

alter session set container=PDB$SEED;
-- Now Follow the Generic Removal
alter pluggable database PDB$SEED close;
alter pluggable database PDB$SEED open read only;

3. Remove from CDB

-- For the CDB follow the steps as under PDB

Monitor automatic backups on OCI using Cloud Control job

For one of our customers, we run Oracle database VM's in OCI. Also, we have Automatic Backups in place and we have a Cloud Control in that environment too.

The issue with automatic backups is, that you have to check in the OCI console or with dbcli commands to verify the result of the backup run.

What you can use, is a Cloud Control job (daily) to run the host command-script:

# note: in cloud control escape the percentage with an extra percentage

fromdate=$(date --date="1 days ago" +"%%Y-%%m-%%d")

outfile=/tmp/daily_check_backup_`hostname`.lst

/opt/oracle/dcs/bin/dbcli list-jobs -f $fromdate -k DBTRegular | grep Regular > $outfile

cat $outfile

ERRCNT=$(grep -v 'Success' $outfile | wc -l);

if [ $ERRCNT -ne 0 ]

then

  exit 4

fi




Note that in Cloud Control you use double percentages to escape percentage.
We check backups from the last day, you may change that.
If you run this job, you may send an email when it fails. You will get the email from Cloud Control.

Logon as sysdba directly on PDB

This is a short version of https://mikedietrichde.com/2020/05/07/can-you-connect-as-sysdba-without-password-into-a-pdb-directly/

First the normal situation:

[oracle@dbhost01 ~]$ env|grep SID
ORACLE_SID=CDB01
[oracle@dbhost01 ~]$ sqlplus / as sysdba

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> select name from v$pdbs;

NAME
------------------------------
PDB$SEED
PDB01

SQL> exit

Now in your shell, set the variable ORACLE_PDB_SID

[oracle@dbhost01 ~]$ export ORACLE_PDB_SID=PDB01
[oracle@dbhost01 ~]$ sqlplus / as sysdba

SQL> show con_name

CON_NAME
------------------------------
PDB01

SQL> exit

If you want to get in the root, unset it, or set ORACLE_PDB_SID=CDB$ROOT

[oracle@dbhost01 ~]$ unset ORACLE_PDB_SID
[oracle@dbhost01 ~]$ sqlplus / as sysdba

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

So that gives you the opportunity to directly go to the PDB.

Oracle Cloud Infrastructure – Database System with Multitenant license included?

When you create a Database System on Oracle Cloud Infrastructure with license included, how many PDB’s are you allowed to create? Easy answer would be: “well license is included, so all you can eat”. WRONG.

Before 19c, you need a multitenant license if you want more than 1 pdb. Starting with 19c, you can use 3 pdb’s  without license. This can be seen here (under column DBCS EE)

https://docs.oracle.com/en/database/oracle/oracle-database/19/dblic/Licensing-Information.html#GUID-0F9EB85D-4610-4EDF-89C2-4916A0E7AC87

But here is the catch. If you think you can use all the options unlimited when buying ‘license included’ from OCI, you should look at:

https://www.oracle.com/database/vm-cloud-pricing.html

You can see that the Multitenant is only included from ‘Enterprise Edition High Performance’ onward. So NOT in Enterprise Edition.

Enterprise Edition below 19c gives you 1 pdb included

Enterprise Edition 19c upwards gives you 3 pdb’s included

Enterprise Edition High Performance (any version) gives you many pdb’s included.

The Enterprise Edition High Performance is suggested as default when you create a DB System:

 

 

 

 

 

 

And it gives you the option to setup a single PDB from the console. Later you can add PDB’s as needed, but the rules above need to be respected.

If you want to prevent creating to many PDB’s in 19c, you can set the max_pdbs parameter.

SQL> show parameter max_pdbs

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

max_pdbs                             integer     3

 

SQL> create pluggable database TOOMANY admin user ADM identified by Welcome123;

create pluggable database TOOMANY admin user ADM identified by Welcome123

*

ERROR at line 1:

ORA-65010: maximum number of pluggable databases created

 

Take care.

Note: I am not an Oracle spokesman. I cannot guarantee that the contents in this article is right. Always check with Oracle to verify you are license-compliant.

 

Move PDB from standby-CDB to a new CDB within 5 minutes

Recently, we had a case were we had the following (simplified) setup:

host 1: CDB01 with PDB01, PDB02

host 2: CDB01-standby with PDB01 and PDB02

Now we wanted to move the PDB02 to a new container, that had to run from host 2. 

host 1: CDB01 with PDB01

host 2: CDB01-standby with PDB01

        CDB02 with PDB02

We wanted to use the standby database for this, because then you don't have to copy the datafiles from host 1 to host 2. In our case, saving about 500Gb scp which would take a lot of time.

We are working with Oracle 12.1 on Linux 7, with regular filesystem (non-ASM) in Single Instance (no RAC).

The following steps have been made. 

Create a new empty container CDB02 on host 2 with responsefile new.rsp:

[GENERAL]

RESPONSEFILE_VERSION = "12.1.0"

OPERATION_TYPE = "createDatabase"

[CREATEDATABASE]

GDBNAME = "CDB02"

SID = "CDB02"

CREATEASCONTAINERDATABASE = true

NUMBEROFPDBS = 0

TEMPLATENAME = "General_Purpose.dbc"

SYSPASSWORD = "Secret#01"

SYSTEMPASSWORD = "Secret#02"

DATAFILEDESTINATION =/u01/oradata

RECOVERYAREADESTINATION=/u02/fast_recovery_area

STORAGETYPE=FS

CHARACTERSET = "AL32UTF8"

NATIONALCHARACTERSET= "AL16UTF16"

 

dbca -createDatabase -silent -responseFile new.rsp

 

After this, you can change memory settings and such whatever you need.

 

On host 1 in CDB01:

alter pluggable database PDB02 close immediate;

alter pluggable database PDB02 unplug into '/home/oracle/PDB02.xml';

drop pluggable database PDB02;

Do NOT drop the datafiles! You will need them on the standby host.

 

Now scp the created xml file to host 2 /home/oracle/.

On host 2 in CDB01-standby:

select name from v$pdbs order by name;

Check that PDB02 is not there.

Now move the datafiles to the new folder:

mv /u01/oradata/CDB01/PDB02 /u01/oradata/CDB02/

 

On host 2 in CDB02:

create pluggable database PDB02 using '/home/oracle/PDB02.xml' source_file_name_convert=('/u01/oradata/CDB01/PDB02','/u01/oradata/CDB02/PDB02') NOCOPY TEMPFILE REUSE; 

Note the NOCOPY keyword. That tells us that the files are already there, we moved them with a linux mv command, and because it is on the same mountpoint, it will be done in blink of an eye.

alter pluggable database PDB02 open; 

select name,open_mode from v$pdbs order by name;

alter pluggable database PDB02 save state;

On host 1:

Cleanup (can be done later) the old datafiles in /u01/oradata/CDB01/PDB02