26 September 2022

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

28 March 2022

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....

06 December 2021

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


29 October 2021

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.