I am curious if anyone is using BigFix to patch Oracle DB’s. Our DBA team asked if they can use BigFix for this and my initial thinking is that we could download the patch (like we do Java) and then build a custom Fixlet around it. Just trying to gauge perspective from others who have attempted this and if there is anything to watch out/prepare for in doing so.
If you can do it on the command line, then you can generally do it with BigFix the same way.
1 Like
Yes, we have been deploying and patching Oracle databases for years via Bigfix. Over 1000 currently managed. It is definitely tricky. When CPU patching databases, the biggest things to watch out for is processes that latch onto DB-related files even when the DB is stopped. Java, monitoring agents, and OS processes (WMI, MSDTC) are ones to watch out for. They also need to be terminated otherwise the CPU pre-req check will fail.
Likewise we also do other CPU patches like WebLogic through Bigfix as well.
2 Likes
Thanks for the help guys.
So are you doing the Oracle patching via Server Automation to make sure the apps/processes are stopped or do you just throw those commands in a baseline and then execute the DB patch? And if you are using Server Automation, are you including a task in there that backs up the DB prior to applying the patches and I guess to take it a step further, then revert to the backup should the patch fail for some reason?
Disclaimer: I’m in no way a DBA but I’m just trying to ask the questions that will help me best support that team should we try and take over those patch efforts with BigFix.
1 Like
If you are just patching a single server independently of any others, then you could use a baseline that would backup the data, stop apps, do the updates, and then bring things back up.
Server automation could help, especially if you are wanting to patch multiple servers in a high availability situation where you want to patch each server in the high availability group one at a time and not patch the next until the first is back.
1 Like
@MagLev, you probably could use Server Automation if you like to JGstew’s point. In our situation we do completely custom scripting in fixlets for database management. That includes install via custom template, patching, backups, recovery preps, re-orgs, encrypted tablespace creation/management, DB users, roles, schemas, permissions, etc. Essentially DBA-lite in the form of Bigfix tasks/baselines.
2 Likes
@MagLev, thanks for your interest in the Oracle installers. The fixlets that we wrote are highly customized and proprietary to my company, so I’m not at liberty to share entire fixlets. However I can share the essential process I followed along with some tips and pointers.
Oracle database automated install and management was a multi-month quest for my about five years ago when one day management came in and said we are deploying over 1000 of these Oracle databases at as many locations (that had no local DBA). “Partner up with your Oracle rep and corporate DBAs to make ‘DBA-in-a-baseline’”. I was floored, but accepted the challenge. And challenging it was. I had an Oracle DBA architect tell me it couldn’t be done. Famous last words.
By way of background, all the servers mentioned are Windows and the Oracle database version is 11.2.0.4 (older I know … likely have to do 12 or 13 soon).
To start with, I designated a service account with which to run the Oracle database services. This is crucial to several parts later. That account requires extraordinary privileges that we assigned via GPO to the servers.
a. Act as part of the OS
b. Adjust memory quotas for a process
c. Allow log on locally
d. Log on as a batch job
e. Log on as a service
f. Replace a process level token
Execute the database installer on a lab system interactively selecting whatever options are appropriate for your environment. Save off the selections as a response file.
If your response file is uniform to your environment, you’re set to go. If it needs to be further customized, as ours does, by location (or some other factor), then put it into a createfile or appendfile in a custom fixlet using relevance substitution to inject the right values.
As an aside, we use a custom variation of the OpenSSL process that IBM embeds into Bigfix as a means to securely distribute sensitive information like keys and passwords. IBM’s approach limits you to only known (already created) and specific machines. It doesn’t handle groups or targeting yet-to-be-created systems. There are other posts (older) on this subject where this topic is discussed in more detail. It is applicable here as we use it heavily to protect sensitive details of the database.
In the actual install fixlet, we pre-create install and backup paths, lay down our customized response file, and set variables for things like DB SID (which is dynamic by location in our environment).
During the actual install, the Oracle installer spawns a number of child processes that we found do not accurately return either exit/error codes or duration back to Bigfix. So it was hard to know when it was complete so we could go to the next step. A small script called ‘loop’ helped with that.
//Create loop.cmd file which checks status of installation
delete __createfile
createfile until createmealoop
@echo off
@SETLOCAL ENABLEEXTENSIONS ENABLEDELAYEDEXPANSION
set MyPID=
wmic process where (CommandLine like '%%OraInstall%%' AND name="java.exe") get processid | findstr [0-9] >MyPID.txt
for /f "Tokens=1 delims=" %%a in (MyPID.txt) do set MyPID=%%a
echo PROCESS: !MyPID!
goto :LOOP
:LOOP
rem echo ***** LOOP - LIVE PROCESS: !MyPID!
<nul set /p=.
timeout 3
wmic process get processid,parentprocessid,executablepath|find "!MyPID!"
if ERRORLEVEL 1 goto :EXIT
goto :LOOP
:EXIT
del MyPID.txt
exit
createmealoop
delete \<path>\loop.cmd
copy __createfile \<path>\loop.cmd
// Custom Start Script for the Installer
delete __createfile
createfile until oracleblablabla
start "DB-INSTALLATION" /wait /D \<path> cmd /K "setup.exe -silent -ignorePrereq -nowait -force -responseFile \<path>\db_install.rsp & exit"
timeout /T 2
start "LOOP" /wait /D \<path> cmd /K "MODE CON: COLS=140 LINES=50 & loop.cmd"
timeout /T 60
oracleblablabla
delete \<path>\Install.cmd
copy __createfile \<path>\Install.cmd
wait \<path>\Install.cmd > \<path>\Install.log
How do we define success? It is challenging as the Oracle installer dynamically names its log folders. So we do something like this:
continue if {exists files whose (name of it starts with "installActions" AND content of it contains "11.2.0.4" AND content of it contains "Exit Status is 0") of folder " \<path>\Oracle\Inventory\logs"}
Then we set the ORACLE_HOME variable:
// Set Oracle Home Global Environmental Variable
wait cmd /C setx ORACLE_HOME " \<path>\dbhome_1" /M
We use Sysinternals sdelete to clean up any sensitive data including the response file, then reboot post install.
Then we create a custom SQLnet.ora with relevance substitution.
Next we set the TNSlistener and database SID services to run as the service account created at the beginning.
Then we use relevance substitution to dynamically generate a database wallet and a SQL to create an encrypted tablespace, piping the output to a log.
It is important to verify the success of the encryption.
// If this fails, database ASO/TDE is not working
continue if {not (exists file "\<path>\TDE_Enable.log" whose (content of it contains "cannot auto-create wallet"))}
continue if {not (exists file "\<path>\TDE_Enable.log" whose (content of it contains "tablespace 'USERS_ENCRYPTED' does not exist"))}
Then we create and run some simple test SQLs, outputting to log files to catch failures.
Select * from v$encryption_wallet;
// If this fails, see DBA to reset master key
continue if {not (exists file "\<path>\walletcheck.log" whose (content of it contains "OPEN_NO_MASTER_KEY"))}
// If this fails, the database wallet is not open
continue if {exists file "\<path>\walletcheck.log" whose (content of it contains "OPEN")}
Select status from v$instance;
// If this fails, the database is not open
continue if {exists file "\<path>\opencheck.log" whose (content of it contains "OPEN")}
SELECT owner, object_name, object_type, status FROM dba_objects WHERE object_name='DBMS_METADATA';
continue if {exists file "\<path>\metadata.log" whose (content of it contains "PACKAGE BODY")}
That should at least get you started (and hopefully save a few days/weeks of your life).
Best wishes.
3 Likes
I put some of the above into code blocks.
Hello all, I have a requirement to patch Oracle DB. Currently they are using manual installation method.
Can we use bigfix sh scripts to install those patches?
Do we need Oracle user credentials to patch Orable DB? or BigFix credentials would be enough ?
Yes, you can run Oracle DB CPU patches in Bigfix.
We use a service account with encrypted credentials to authenticate to the database.
Be sure to use the latest Opatch tool as prior versions have issues. Also be sure to stop/terminate anything that has a connection to the database or any of the files used by RDBMS before you run the patch.
Hello @JonL, thanks for response.
How can we mention BigFix to use the service account credentials to connect to DB? and where we need to save those credentials ?
There are several ways to do secure credentials. You need to research to find one you are comfortable with and meets the security requirements of your organization. Bigfix natively has the ability to incorporate a secret in a fixlet. While good, it does have limitations. To overcome some of those limitations, there are third-party tools such as OpenSSL which can be leveraged. If you search on this forum, there are several extensive threads about encryption options with their pros and cons.