Looking for something? Try here..

Monday, September 6, 2021

Patching Oracle home with limited storage

 Today, while trying to apply patch on one of my test database, I encountered the following error. 

[oracle@linux75-2 32916808]$ $ORACLE_HOME/OPatch/opatch apply
Oracle Interim Patch Installer version 12.2.0.1.26
Copyright (c) 2021, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/orcl/122
Central Inventory : /u01/oraInventory
   from           : /u01/orcl/122/oraInst.loc
OPatch version    : 12.2.0.1.26
OUI version       : 12.2.0.1.4
Log file location : /u01/orcl/122/cfgtoollogs/opatch/opatch2021-09-05_23-04-44PM_1.log

Verifying environment and performing prerequisite checks...
Prerequisite check "CheckSystemSpace" failed.
The details are:
Required amount of space(3702.527MB) is not available.
UtilSession failed:
Prerequisite check "CheckSystemSpace" failed.
Log file location: /u01/orcl/122/cfgtoollogs/opatch/opatch2021-09-05_23-04-44PM_1.log

OPatch failed with error code 73
[oracle@linux75-2 32916808]$ 
/u01 file system has only around 2.6 GB of free space where as the patch is looking for around 3.7 GB of free space. As this being my test machine, I don't want to add space and was looking to work around the situation. 
[oracle@linux75-2 ~]$ df -h /u01 /u02 
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda2        10G  7.5G  2.6G  75% /u01
/dev/sda5        17G  9.3G  7.7G  55% /u02
[oracle@linux75-2 ~]$ 

Now, the downloaded patch (Patch 32916808: DATABASE JUL 2021 RELEASE UPDATE 12.2.0.1.210720) is < 1 GB in size. So why does the patch requires > 3.5 GB of free space. 

Starting with 10.2, Opatch does not backup only the affected modules, it also takes a backup of the complete affected libraries to $ORACLE_HOME/.patch_storage/<patchid>/backup/<lib_directory_name>/<library_name>

So the 3.5 GB of space is needed just to backup the existing software files so that Oracle can safely rollback if we encounter any issue during the patching process. 

So how do we over come this space issue now? 

Firstly, we need to bypass the opatch utility from performing the free space check during the patching operation 
Second, we need to figure out an alternate path to store the backup files for the duration of the patching

By running the $ORACLE_HOME/OPatch/opatch prereq -help, we can find all the prerequisites the OPatch utility is performing of which CheckSystemSpace is one of the prerequisite. 
If we start the opatch utility using -verbose option, we can see all the associated parameters taken during the execution of the opatch operation as below.. 

[oracle@linux75-2 ]$ $ORACLE_HOME/OPatch/opatch apply -verbose
Oracle Interim Patch Installer version 12.2.0.1.26
Copyright (c) 2021, Oracle Corporation.  All rights reserved.

Environment:
   OPatch.ORACLE_HOME=/u01/orcl/122
   oracle.installer.invPtrLoc=/u01/orcl/122/oraInst.loc
   oracle.installer.oui_loc=/u01/orcl/122/oui
   oracle.installer.library_loc=/u01/orcl/122/oui/lib/linux64
   oracle.installer.startup_location=/u01/orcl/122/oui
   OPatch.PLATFORM_ID=
   os.name=Linux
   OPatch.NO_FUSER=
   OPatch.SKIP_VERIFY=null
   OPatch.SKIP_VERIFY_SPACE=null
   oracle.installer.clusterEnabled=false
   TRACING.ENABLED=null
   TRACING.LEVEL=null
   OPatch.DEBUG=false
   OPATCH_VERSION=12.2.0.1.26
   Bundled OPatch Property File=properties
   ...
   ...
   ...
We can see OPatch.SKIP_VERIFY_SPACE is set to null by default. We will set this parameter to TRUE to skip space verification. 

Next, we will create a symbolic link for .patch_storage under ORACLE_HOME to a directory outside of ORACLE_HOME, in our case /u02
[oracle@linux75-2 122]$ pwd
/u01/orcl/122
[oracle@linux75-2 122]$ mkdir -p /u02/.patch_storage
[oracle@linux75-2 122]$ ln -s /u02/.patch_storage .patch_storage
[oracle@linux75-2 122]$ ls -lart .pat*
lrwxrwxrwx. 1 oracle oinstall 19 Sep  5 23:41 .patch_storage -> /u02/.patch_storage
[oracle@linux75-2 122]$
Now, the patching completes without any issues as below. 

[oracle@linux75-2 32916808]$ $ORACLE_HOME/OPatch/opatch apply OPatch.SKIP_VERIFY_SPACE=true
Oracle Interim Patch Installer version 12.2.0.1.26
Copyright (c) 2021, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/orcl/122
Central Inventory : /u01/oraInventory
   from           : /u01/orcl/122/oraInst.loc
OPatch version    : 12.2.0.1.26
OUI version       : 12.2.0.1.4
Log file location : /u01/orcl/122/cfgtoollogs/opatch/opatch2021-09-05_23-43-32PM_1.log

Verifying environment and performing prerequisite checks...

--------------------------------------------------------------------------------
Start OOP by Prereq process.
Launch OOP...

Oracle Interim Patch Installer version 12.2.0.1.26
Copyright (c) 2021, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/orcl/122
Central Inventory : /u01/oraInventory
   from           : /u01/orcl/122/oraInst.loc
OPatch version    : 12.2.0.1.26
OUI version       : 12.2.0.1.4
Log file location : /u01/orcl/122/cfgtoollogs/opatch/opatch2021-09-05_23-43-46PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   32916808

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/orcl/122')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '32916808' to OH '/u01/orcl/122'
ApplySession: Optional component(s) [ oracle.swd, 12.2.0.1.0 ] , [ oracle.swd.oui, 12.2.0.1.0 ] , [ oracle.network.cman, 12.2.0.1.0 ] , [ oracle.ons.cclient, 12.2.0.1.0 ] , [ oracle.ons.eons.bwcompat, 12.2.0.1.0 ] , [ oracle.rdbms.drdaas, 12.2.0.1.0 ] , [ oracle.oid.client, 12.2.0.1.0 ] , [ oracle.ons.daemon, 12.2.0.1.0 ] , [ oracle.network.gsm, 12.2.0.1.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.rdbms, 12.2.0.1.0...

Patching component oracle.rdbms.rsf, 12.2.0.1.0...

Patching component oracle.network.rsf, 12.2.0.1.0...

Patching component oracle.rdbms.util, 12.2.0.1.0...

Patching component oracle.ctx, 12.2.0.1.0...

...

...

...

Patching component oracle.jdk, 1.8.0.91.0...

OPatch found the word "error" in the stderr of the make command.
Please look at this stderr. You can re-run this make command.
Stderr output:
chmod: changing permissions of ‘/u01/orcl/122/bin/extjobO’: Operation not permitted
make: [iextjob] Error 1 (ignored)


Patch 32916808 successfully applied.
OPatch Session completed with warnings.
Log file location: /u01/orcl/122/cfgtoollogs/opatch/opatch2021-09-05_23-43-46PM_1.log

OPatch completed with warnings.
[oracle@linux75-2 32916808]$ 
Note that the opatch is started using OPatch.SKIP_VERIFY_SPACE=true
The final error can be ignored or can be fixed by running root.sh from ORACLE_HOME as root user. 
We are now done with the patching with limited storage in Oracle home file system. 

Foot note: 

The file system utilization of /u02 increased up to 14 GB (from 9.7 GB) as expected and went back to 11 GB and not completely down to 9.7 GB (using ~ 1GB post patching). 
[oracle@linux75-2 u02]$ df -h .
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda5        17G  9.7G  7.3G  58% /u02
[oracle@linux75-2 u02]$ df -h .
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda5        17G   12G  5.2G  70% /u02
[oracle@linux75-2 u02]$ df -h .
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda5        17G   13G  4.2G  76% /u02
[oracle@linux75-2 u02]$ df -h .
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda5        17G   13G  4.1G  76% /u02
[oracle@linux75-2 u02]$ df -h .
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda5        17G   14G  3.9G  78% /u02
[oracle@linux75-2 u02]$ df -h .
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda5        17G   14G  3.8G  78% /u02
[oracle@linux75-2 u02]$ df -h .
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda5        17G   11G  6.5G  62% /u02
 
This is because OPatch utility will delete all the backup files which are not required post patching and keeps any file that will be needed for rollback of the patch if intended. Check MOS Doc ID 550522.1 to understand how to clean up .patch_storage if needed. 

References: 

Happy Patching...!!!


Thursday, August 26, 2021

RMAN restore, DB_CREATE_FILE_DEST and mix of OMF and non OMF files

 We were trying to restore a production database on to a test environment which we do as a usual refresh when application needs. We followed the same procedure which was followed for other databases for long time but this time it failed for this specific database.


The init file of the target database consists the following parameters. 

*.db_create_file_dest='+ABD_DATA_DG01'
*.db_create_online_log_dest_1='+ABD_LOG_DG01'

Once the control file is placed in the respective location and the target database is in mount state, we did a restore which resulted in following error. 

Restore command used:

run {
set until time = "to_date ('22-Aug-2021 18:00', 'dd-Mon-yyyy hh24:mi')";
restore database;
recover database;
}

Error received: 

...
...
...
using channel ORA_DISK_7
using channel ORA_DISK_8

channel ORA_SBT_TAPE_1: starting datafile backup set restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_SBT_TAPE_1: restoring datafile 00031 to +ABP_DATA_DG01/ABP/sr3_28/sr3.data28
channel ORA_SBT_TAPE_1: restoring datafile 00103 to +ABP_DATA_DG01/ABP/sr3_89/sr3.data89
channel ORA_SBT_TAPE_1: restoring datafile 00129 to +ABP_DATA_DG01/ABP/DATAFILE/psapsr3usr.390.1049882411
channel ORA_SBT_TAPE_1: restoring datafile 00143 to +ABP_DATA_DG01/ABP/DATAFILE/psapsr3.402.1074438749
...
...
...
channel ORA_SBT_TAPE_8: restoring datafile 00091 to +ABP_DATA_DG01/ABP/sr3_79/sr3.data79
channel ORA_SBT_TAPE_8: restoring datafile 00104 to +ABP_DATA_DG01/ABP/sr3usr_3/sr3usr.data3
channel ORA_SBT_TAPE_8: reading from backup piece ./server1234/ABP/20210820/set628874_piece1_4a070fpn_628874_1_1
channel ORA_SBT_TAPE_1: ORA-19870: error while restoring backup piece ./server1234/ABP/20210820/set628870_piece1_46070fpm_628870_1_1
ORA-19504: failed to create file "+ABP_DATA_DG01/ABP/sr3_28/sr3.data28"
ORA-17502: ksfdcre:3 Failed to create file +ABP_DATA_DG01/ABP/sr3_28/sr3.data28
ORA-15001: diskgroup "ABP_DATA_DG01" does not exist or is not mounted
ORA-15018: diskgroup cannot be created
channel ORA_SBT_TAPE_1: starting datafile backup set restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_SBT_TAPE_1: restoring datafile 00009 to +ABP_DATA_DG01/ABP/sr3_6/sr3.data6
channel ORA_SBT_TAPE_1: restoring datafile 00037 to +ABP_DATA_DG01/ABP/sr3_34/sr3.data34
...
...
...
ORA-15018: diskgroup cannot be created
failover to previous backup

creating datafile file number=1 name=+ABP_DATA_DG01/ABP/system_1/system.data1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/23/2021 21:30:39
ORA-01180: can not create datafile 1
ORA-01110: data file 1: '+ABP_DATA_DG01/ABP/system_1/system.data1'

RMAN>

As per Oracle support note "RMAN is Not Restoring OMF Datafiles in Their Original Location (Doc ID 882555.1)", the order in which RMAN restores OMF files is as below
  1. If "SET NEWNAME" is specified, RMAN will use that name for restore.
  2. If the original file exists, RMAN will use the original filename for restore.
  3. If the DB_CREATE_FILE_DEST is set, RMAN will use the disk group name specified and create another name for that file in this disk group.
  4. If no DB_CREATE_FILE_DEST is set and the original file does not exist, then RMAN will create another name for that file in the original disk group.
Let's analyze where the restore went wrong. 
  • We have not set "SET NEWNAME", so point 1 is invalid here.
  • We dont have a database and overwriting the same, so point 2 is invalid
  • We have set DB_CREATE_FILE_DEST, so point 4 is invalid. 
Point 3 says RMAN should use the disk group name specified but if you see the error details closely, RMAN is checking for production database's disk group ABP_DATA_DG01 instead of the one provided to the parameter DB_CREATE_FILE_DEST = ABD_DATA_DG01. 
To top it up, there are 2 files already restored to the proper disk group before the error is thrown out. 

So what's happening here? 

After further analysis on why 2 files got created properly as expected and not others, we can see that the files which are being thrown error are not OMF and custom names provided by user. In this case, the files are created with these names by the application's space management software.

From one of the backup set: 

+ABP_DATA_DG01/ABP/sr3_28/sr3.data28 -- custom name
+ABP_DATA_DG01/ABP/sr3_89/sr3.data89 -- custom name
+ABP_DATA_DG01/ABP/DATAFILE/psapsr3usr.390.1049882411 -- Oracle Managed File (OMF)
+ABP_DATA_DG01/ABP/DATAFILE/psapsr3.402.1074438749 -- Oracle Managed File (OMF)

Since we have a mix of OMF and custom files, RMAN restored the OMF to proper disk group but it is looking for the original disk group for the non OMF files. 

How do we resolve this? 

Since we have a mix of OMF and non OMF files, we can make use of "SET NEWNAME" (point 1) for the restore to work properly. 

Revised restore command: 

run {
set until time = "to_date ('22-Aug-2021 18:00', 'dd-Mon-yyyy hh24:mi')";
SET NEWNAME FOR DATABASE TO '+ABD_DATA_DG01';
restore database;
switch datafile all;
switch tempfile all;
recover database;
}

After using SET NEWNAME command, the restore completed without any issues. 

References: 


Happy restoring...!!!