Durham Oracle

Oracle Patching quick notes..

See earlier post for details on where to pull the latest Oracle patches.

"Download Reference for Oracle Database / GI Update, Revision, PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases ( Doc ID 2118136.2 )"

This is the patching info for a standalone grid server with rdbms 12c.

As we are using grid - downloaded the latest grid patch 28828733.

Applied the latest Opatch patch to both homes, applied the patch on both homes and ran datapatch verbose on the rdbms side to apply the patch updates to the database.

As always please try on test environment first!!!

https://dba12c.wordpress.com/2015/05/19/12c-new-pathcing-feature-datapatch-2/

Latest OPatch :

https://updates.oracle.com/download/6880880.html

If you hit this issue when running Opatch after unzipping the patch :

Error: This Java instance does not support a 64-bit JVM.
Please install the desired version.

From the OPatch/jre/bin directory link Java back to the Home Java.

[oracle@usnyssmtaoem01 bin]$ pwd

/u01/app/12.2.0/grid/OPatch/jre/bin

Keep the original java.

[oracle@usnyssmtaoem01 bin]$ mv java java_orig

[oracle@usnyssmtaoem01 bin]$ ln -s /u01/app/12.2.0/grid/jdk/bin/java java

[oracle@usnyssmtaoem01 bin]$ ./java -version

java version "1.8.0_91"

Java(TM) SE Runtime Environment (build 1.8.0_91-b14)

Java HotSpot(TM) 64-Bit Server VM (build 25.91-b14, mixed mode)

This is for the database home but also ran against the Grid Home.

root@oradw01 OPatch]# ./opatchauto apply /u99/media/software/28828733 -oh /u01/app/oracle/product/dbhome

OPatchauto session is initiated at Tue Feb 5 04:02:28 2019

System initialization log file is /u01/app/oracle/product/dbhome/cfgtoollogs/opatchautodb/systemconfig2019-02-05_04-02-32AM.log.

Session log file is /u01/app/oracle/product/dbhome/cfgtoollogs/opatchauto/opatchauto2019-02-05_04-02-37AM.log

The id for this session is V583

Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/product/dbhome

Patch applicability verified successfully on home /u01/app/oracle/product/dbhome

Verifying SQL patch applicability on home /u01/app/oracle/product/dbhome

SQL patch applicability verified successfully on home /u01/app/oracle/product/dbhome

Preparing to bring down database service on home /u01/app/oracle/product/dbhome

Successfully prepared home /u01/app/oracle/product/dbhome to bring down database service

Bringing down database service on home /u01/app/oracle/product/dbhome

Following database has been stopped and will be restarted later during the session: adwdata

Database service successfully brought down on home /u01/app/oracle/product/dbhome

Start applying binary patch on home /u01/app/oracle/product/dbhome

Binary patch applied successfully on home /u01/app/oracle/product/dbhome

Starting database service on home /u01/app/oracle/product/dbhome

Database service successfully started on home /u01/app/oracle/product/dbhome

Preparing home /u01/app/oracle/product/dbhome after database service restarted

No step execution required.........

Trying to apply SQL patch on home /u01/app/oracle/product/dbhome

SQL patch applied successfully on home /u01/app/oracle/product/dbhome

OPatchAuto successful.

-------------------------------Summary--------------------------------

Patching is completed successfully. Please find the summary as follows:

Host:oradw01

SIDB Home:/u01/app/oracle/product/dbhome

Version:12.2.0.1.0

Summary:

==Following patches were SKIPPED:

Patch: /u99/media/software/28828733/28864846

Reason: This patch is not applicable to this specified target type - "oracle_database"

Patch: /u99/media/software/28828733/26839277

Reason: This patch is not applicable to this specified target type - "oracle_database"

Patch: /u99/media/software/28828733/28566910

Reason: This patch is not applicable to this specified target type - "oracle_database"

==Following patches were SUCCESSFULLY applied:

Patch: /u99/media/software/28828733/28822515

Log: /u01/app/oracle/product/dbhome/cfgtoollogs/opatchauto/core/opatch/opatch2019-02-05_04-03-53AM_1.log

Patch: /u99/media/software/28828733/28870605

Log: /u01/app/oracle/product/dbhome/cfgtoollogs/opatchauto/core/opatch/opatch2019-02-05_04-03-53AM_1.log

OPatchauto session completed at Tue Feb 5 04:06:35 2019

Time taken to complete the session 4 minutes, 7 seconds

[root@oradw01 OPatch]# exit

[oracle@oradw01 OPatch]$ pwd

/u01/app/oracle/product/dbhome/OPatch

[oracle@oradw01 OPatch]$ ./datapatch -verbose

SQL Patching tool version 12.2.0.1.0 Production on Tue Feb 5 04:08:51 2019

Copyright (c) 2012, 2018, Oracle. All rights reserved.

Log file for this invocation: /u01/app/12.2.0/cfgtoollogs/sqlpatch/sqlpatch_63892_2019_02_05_04_08_51/sqlpatch_invocation.log

Connecting to database...OK

Bootstrapping registry and package to current versions...done

Determining current state...done

Current state of SQL patches:

Bundle series DBRU:

ID 190115 in the binary registry and ID 190115 in the SQL registry

Adding patches to installation queue and performing prereq checks...

Installation queue:

Nothing to roll back

Nothing to apply

SQL Patching tool complete on Tue Feb 5 04:08:58 2019

[oracle@oradw01 OPatch]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Feb 5 04:09:31 2019

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select patch_id, status, description from dba_registry_sqlpatch

SQL> /

PATCH_ID STATUS DESCRIPTION
--------- ------------------------- ------------------------------------------------------------
28822515 SUCCESS DATABASE JAN 2019 RELEASE UPDATE 12.2.0.1.190115

Unable To mount ACFS after Reboot

Built a stand alone grid infrastructure and added an ACFS file system.

However, following a reboot, I was unable to start the acfs cluster.

Eventually, found this document on Metalink.

Unable To mount ACFS after Reboot On Standalone GI [SIHA] (Doc ID 2348133.1)

Standalone setup does not start ACFS automatically, so create rc.local script to load the ACFS drivers well before the OHASD service starts. ACFS drivers should get loaded before ASM starts.

To prove this worked, I ran a shutdown on ASM, started acfs i.e. "acfsload start" and then started ASM and et voila. Need to add the "acfsload start" to the startup scripts otherwise will need to do this following every reboot.

[oracle@localhost ~]$ sqlplus / as sysasm
SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 22 04:19:45 2019
Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> shutdown abort
ASM instance shutdown
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

If the server is using HA i.e. RAC One Node or Oracle restart you could run a :

crstl stop has

then :

[oracle@localhost ~]$ lsmod | grep oracle
oracleasm              59173 1

--- No acfs.

[oracle@localhost ~]$ su
Password:

[root@localhost oracle]# . oraenv
ORACLE_SID = [+ASM] ?
The Oracle base remains unchanged with value /u01/app

[root@localhost oracle]# acfsload start

ACFS-9391: Checking for existing ADVM/ACFS installation.
ACFS-9392: Validating ADVM/ACFS installation files for operating system.
ACFS-9393: Verifying ASM Administrator setup.
ACFS-9308: Loading installed ADVM/ACFS drivers.
ACFS-9154: Loading 'oracleoks.ko' driver.
ACFS-9154: Loading 'oracleadvm.ko' driver.
ACFS-9154: Loading 'oracleacfs.ko' driver.
ACFS-9327: Verifying ADVM/ACFS devices.
ACFS-9156: Detecting control device '/dev/asm/.asm_ctl_spec'.
ACFS-9156: Detecting control device '/dev/ofsctl'.
ACFS-9322: completed

Now :

[root@node01 ~]# lsmod | grep oracle

oracleacfs    4815623 1
oracleadvm            785504 7
oracleoks 638647 2 oracleacfs,oracleadvm
oracleasm    59173 1

Restart ASM or "crsctl start has" if the server is HA enabled.

[oracle@localhost ~]$ sqlplus / as sysasm

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 22 04:21:26 2019
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup;
ASM instance started
Total System Global Area 1140850688 bytes
Fixed Size                  8629704 bytes
Variable Size            1107055160 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted
ASM diskgroups volume enabled

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Check diskgroups are ok via asmcmd, if not run a "volenable --all" to ensure the volumes are enabled.

ASMCMD> volinfo --all
Diskgroup Name: NFPSADWGGDATA
         Volume Name: NFPSADWGG
         Volume Device: /dev/asm/nfpsadwgg-236
         State: ENABLED
         Size (MB): 306176
         Resize Unit (MB): 512
         Redundancy: UNPROT
         Stripe Columns: 8
         Stripe Width (K): 1024
         Usage: ACFS
         Mountpath: /acfsmounts

Using this information :

mount -t acfs /dev/asm/nfpsadwgg-236 /acfsmounts

Check successfully mounted

$ df -h | grep acfs

Filesystem Size Used Avail Use% Mounted on
/dev/asm/nfpsadwgg-236                            299G 4.6G 295G   2% /acfsmounts

Check anything that is using acfs is working e.g. Golden Gate.

See also this metalink doc when acfs not running on OCI cluster :

OCI Backup issue - DCS-10001:Internal error encountered: Mount point:/opt/oracle/dcs/commonstore is not mounted or does not exist (Doc ID 2513385.1)

Flashback database

I don't really like using flashback, if given the choice would use pluggable databases and clone the pdb prior to doing any work and if you hit a problem clone the pdb back to it's original state (but keep that for another day).

Here are the steps for flashback of a database. The DB needs to be in achieve log mode and forced logging enabled. Verify that the flash recovery area is setup and large enough for the upgrade.

NB - This process is slightly different if using DataGuard please see -

https://www.thegeekdiary.com/how-to-create-and-drop-guaranteed-restore-point-in-oracle-data-guard/

You don’t need to enable flashback on the whole database, but I did it anyway. The restore point needs to be guaranteed.

Verify Flashback status, size and location:

select flashback_on, LOG_MODE, FORCE_LOGGING, status from v$database, v$instance;

show parameter DB_RECOVERY

show parameter FLASHBACK

Steps before the upgrade:

ALTER DATABASE FLASHBACK ON;

CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

Steps after the upgrade:

DROP RESTORE POINT before_upgrade;

ALTER DATABASE FLASHBACK OFF;

Steps if need to flashback the Database:

su – oracle

sqlplus / as sysdba;

select current_scn from v$database;

shutdown immediate;

startup mount;

select * from v$restore_point;

flashback database to restore point before_upgrade;

alter database open resetlogs;

Check flashback space usage:

select * from v$flash_recovery_area_usage;

select name, time, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE from v$restore_point;

More detailed instructions (when running an application upgrade and you hit problems) :

Instruction Steps
Task Steps		Reference/Screenshots
1.	Verify Flashback Recovery status, size and location: If db_recovery_file_dest and db_recovery_file_dest_size is blank, add them before proceeding.	select flashback_on, log_mode, force_logging, status from v$database, v$instance; show parameter db_recovery; show parameter flashback;
2.
3.	Steps before the Application upgrade: Note: Save value for current_scn to compare if recovery is performed	alter database flashback on; select current_scn from v$database; create restore point "before_upgrade" guarantee flashback database;
4.
5.	Steps after the Application upgrade: Note: Only perform these steps if upgrade has been tested and declared successful	drop restore point "before_upgrade"; alter database flashback off;
6.
7.	If Application upgrade was not successful and a roll back is required, perform these steps to roll back database changes: Note: Compare SCN from step 3 to ensure flashback was successful. SCN’s may not match, but should be close in range Replace db_name with your RAC database name	For single database instance: sqlplus / as sysdba; shutdown immediate; startup mount; flashback database to restore point "before_upgrade"; alter database open resetlogs; select current_scn from v$database; For multi-node RAC instances: srvctl stop database -db db_name export ORACLE_SID=db_name_1 sqlplus / as sysdba; startup mount; flashback database to restore point "before_upgrade"; alter database open resetlogs; select current_scn from v$database; shutdown immediate; quit; srvctl start database -db db_name
8.
9.	Check flashback space usage:	select * from v$flash_recovery_area_usage; select name, time, guarantee_flashback_database, storage_size from v$restore_point;
10.