Python and cx_Oracle for Oracle database connections

In my latest adventures, I have been improving my Python skills in regards to running Oracle SQL and PL/SQL code.  Once I got the process down for basic connection and execution, a database migration including new LDAP connectivity threw me for a loop until I got it figured out.

First, a little background on cx_oracle.  cx_Oracle is a Python extension module that enables access to Oracle databases. It conforms to the Python database API 2.0 specification with a considerable number of additions and a couple of exclusions.  cx_Oracle 7 has been tested with Python version 2.7, and with versions 3.5 through 3.8. You can use cx_Oracle with Oracle 11.2, 12, 18 and 19 client libraries. Oracle’s standard client-server version functionality allows connection to both older and newer databases. For example Oracle 19c client libraries can connect to Oracle Database 11.2.

To check if you have the cx_oracle module installed in your current Python environment, you can simply create a Python script with the line import cx_Oracle and execute it with python <filename> or execute this command from the Python command line.  If there are no errors, then the module is likely installed.

If not, you can install it in Linux with the command with the pip (python installs packages) module:

python -m pip install cx_Oracle --upgrade

If you receive an error about the pip module being unavailable, you can install it with the command:

yum install python-pip

Now that we know what cx_oracle is for and how to install it, here is an example of using it to connect to an Oracle RAC system:

import sys
import os
import cx_Oracle

dsnStr = cx_Oracle.makedsn('<SCAN_NAME>','<LISTEN_PORT>',service_name='<SERVICE_NAME>')
con = cx_Oracle.connect(user='<USER>',password='PWD',dsn=dsnStr)
cur.con.cursor()
sql="""
<SQL or PL/SQL to execute>
"""
cur.execute(sql)
cur.close()
con.close()


Seems pretty simple.  This changed when we tried to configure a connection similar to the one above for a database using LDAP authentication.  The new sqlnet.ora and ldap.ora files where in place, but every connection attempt resulted in a ORA-12154 error.  In my particular case, Oracle instant client 12.2 and (regular) client 18c were installed on the server where these scripts were executed.  Even placing the necessary .ora network files in both locations resulted in the same errors.

The python command print os.environ will give you a list of all the environment variables that are set in the current environment.  Note – be sure to use the import os command to import the proper libraries first.  This was an important finding for me since I was using the Jenkins development environment and the actual execution user was not something I could see.  Using this command, I found out the identity of the OS user whom the python scripts were executing under.  After getting access to the development server, this account did not have any of the Oracle environment settings associated with it such as ORACLE_HOME, TNS_ADMIN, or LD_LIBRARY_PATH.  My next step was to set these under the user account on the server.  Even so, the attempts to connect to the Oracle database resulted in the same ORA-12154 error.  As a curious side, the print os.environ command did not list these variables after setting them at the OS level for the user account.

Now what?

Well, if you could pull up the environment variables using os.environ, couldn’t you set them as well?  Setting the values of ORACLE_HOME, TNS_ADMIN, and LD_LIBRARY_PATH in the Python script turned out to be the answer and ensured I was pointing to the correct Oracle client binaries.  Here is the resulting code:

import sys
import os
import cx_Oracle

os.environ['ORACLE_HOME'] = '/opt/oracle/version'
os.environ['TNS_ADMIN'] = '/opt/oracle/version/network/admin'
os.environ['LD_LIBRARY_PATH'] = '/opt/oracle/version/lib'

con = cx_Oracle.connect('{}/{}@{}'.format('<USER>','<PWD>','<LDAP_DESIGNATOR>'))
cur.con.cursor()
sql="""
<SQL or PL/SQL to execute>
"""
cur.execute(sql)
cur.close()
con.close()

Also, while working to get the python script to recognize the Oracle environment variable settings at the OS level, I discovered the use of the Python subprocess module in regards to calling scripts at the OS level.  This was a work around that could take advantage of the OS account environment settings.  However, I was told that even if username and password credentials were masked as they were passed in, they could be displayed by executing ps -ef on the server.  I have not been able to verify this, but it could be a security issue if executing Python scripts against production databases.

import os 
import subprocess 

output = subprocess.check_output(['<PATH_AND_SCRIPT_NAME',str('<ARG1>'),str('<ARG12')]) 
print(output)

If the script called executes SQL or PL/SQL, it is a good idea to use the WHENEVER SQLERROR EXIT 1 clause to ensure that any errors cause a failure and are captured.

 

 

SQL Server Full Backup Verification

As database administrators, our first focus in our production environments should be backup and recovery.  I have mentioned this before in previous posts and it can never be said too much.  Sure, you can backup your database left and right, but unless you verify the backups with practice recovery or some other type of verification, your backup and recovery plan is unreliable.

SQL Server author Steve Rezhener (https://www.sqlservercentral.com/author/walkair) has just published a scripted backup verification process that I thought was interesting.  I tested this script on a SQL Server 2019 Developer Edition lab environment and wanted to pass this process on along with my comments.

This verification script performs the following actions:

  1. Gets the latest full backup of the user-designated database
  2. Checks if there is enough free space for the trial recovery of the user-designated database
  3. Restores the latest full backup into a copy database
  4. The copy database is checked for corruption
  5. The copy database is removed
  6. The check results are placed into a temporary table, published, then dropped

Here are the contents of the published script.

DECLARE @DBName   AS VARCHAR(100) = 'DemoDB'
DECLARE @SQLString NVARCHAR(500), @BackupFileName AS NVARCHAR(500)
DECLARE @ParmDefinition nvarchar(500)
DECLARE @Restore_DBName AS VARCHAR(100) = @DBName
DECLARE @DataFileSpaceTaken_GB AS INT, @FreeDriveSpace_GB AS INT
DECLARE @OriginalDataFileName AS VARCHAR(500), @OriginalLogFileName AS VARCHAR(500)-- just in case
IF EXISTS(SELECT database_id FROM sys.databases WHERE name=@DBName)
BEGIN
--sys.databases
 
PRINT '-- 1. GET LATEST BACKUP FILE FOR A DATABASE--'
SELECT TOP 1 @BackupFileName = bakfams.Physical_Device_Name
--bakfams.Physical_Device_Name
, @DataFileSpaceTaken_GB = masfiles.size/1024/1024/1024
, @FreeDriveSpace_GB = osvols.available_bytes/1024/1024/1024
, @OriginalDataFileName = masfiles.physical_name
, @OriginalLogFileName = masfiles2.physical_name
FROM sys.databases AS dats
INNER JOIN msdb.dbo.backupset AS baks    ON baks.database_name = dats.name
INNER JOIN msdb.dbo.backupmediafamily AS bakfams ON baks.media_set_id = bakfams.media_set_id
INNER JOIN sys.master_files AS masfiles    ON masfiles.database_id = dats.database_id AND masfiles.type_desc = 'ROWS'
CROSS APPLY sys.dm_os_volume_stats(masfiles.database_id, masfiles.file_id) AS osvols
LEFT OUTER JOIN sys.master_files AS masfiles2  ON masfiles2.database_id = dats.database_id AND masfiles2.type_desc = 'LOG'
WHERE 1=1
AND dats.name = @DBName
AND baks.[type]='D'
ORDER BY baks.backup_finish_date DESC
PRINT @BackupFileName
 
PRINT '-- 2. CREATE DATABASE NAME TO RESTORE --'
SET @Restore_DBName = @Restore_DBName + '_' + DATENAME(MONTH,GETDATE())
SET @Restore_DBName = @Restore_DBName + CONVERT(VARCHAR(2),DAY(GETDATE()))
SET @Restore_DBName = @Restore_DBName + CONVERT(VARCHAR(4),YEAR(GETDATE()))
PRINT @Restore_DBName

PRINT '-- 3. CHECK FREE DISKSPACE TO RESTORE THE DATABASE --'
PRINT @DataFileSpaceTaken_GB
PRINT @FreeDriveSpace_GB
IF (@FreeDriveSpace_GB<@DataFileSpaceTaken_GB*2) BEGIN PRINT '-- not enough space --'; RETURN; END
 
PRINT '-- 4. RESTORE DB--'
SET @SQLString = 'RESTORE DATABASE [' + @Restore_DBName + ']'
SET @SQLString = @SQLString + ' FROM DISK = N''' + @BackupFileName + ''''
SET @SQLString = @SQLString + ' WITH FILE = 1,'
SET @SQLString = @SQLString + ' MOVE N''' + @DBName + ''''
SET @SQLString = @SQLString + ' TO N''' + REPLACE(@OriginalDataFileName,@DBName,@Restore_DBName) + ''''
SET @SQLString = @SQLString + ', MOVE N''' + @DBName + '_log'''
SET @SQLString = @SQLString + ' TO N''' + REPLACE(@OriginalLogFileName,@DBName,@Restore_DBName) + ''''
SET @SQLString = @SQLString + ', NOUNLOAD, REPLACE, STATS = 10'
PRINT @SQLString
EXECUTE sp_executesql @SQLString
--RETURN
 
PRINT '-- 5. CHECK RESTORED DATABASE--'
SET @SQLString = 'DBCC CHECKDB ('+ @Restore_DBName + ')'
SET @SQLString = @SQLString + ' WITH NO_INFOMSGS ' -- WITH TABLERESULTS
EXECUTE sp_executesql @SQLString
 
PRINT '-- 6. DROP RESTORED DATABASE--'
SET @SQLString = 'DROP DATABASE ' + @Restore_DBName
EXECUTE sp_executesql @SQLString
 
PRINT '—-7. CREATE TEMP winlog TABLE --'
IF OBJECT_ID('tempdb..#winlog') !=0 DROP TABLE #winlog
CREATE TABLE #winlog
(
rowID  INT IDENTITY(1,1)
, LogDate  DATETIME
, ProcessInfo  VARCHAR(50)
, textRow  VARCHAR(4000)
)
PRINT  '-- 8. STORE DBCC CHECKDB RESULTS --'
INSERT INTO #winlog(LogDate, ProcessInfo, textRow)
EXEC master.dbo.xp_readerrorlog
PRINT  '-- 9. LOCATE LAST/FIRST ROWID —-'
DECLARE @textRow nvarchar(500), @1stRowID AS INT = 0
SET @SQLString = 'SELECT TOP 1 @x1stRowID = rowID'
SET @SQLString = @SQLString + ' FROM #winlog'
SET @SQLString = @SQLString + ' WHERE textRow = ''Starting up database ''''' + @Restore_DBName + '''''.'''
SET @SQLString = @SQLString + ' ORDER BY rowID DESC'
SET @ParmDefinition = N'@x1stRowID AS INT OUTPUT';
EXECUTE sp_executesql @SQLString, @ParmDefinition, @x1stRowID = @1stRowID OUTPUT;
SET @SQLString = 'SELECT *'
SET @SQLString = @SQLString + ' FROM #winlog'
SET @SQLString = @SQLString + ' WHERE RowID >= @xRowID'
SET @SQLString = @SQLString + ' ORDER BY rowID DESC'
PRINT 'SELECT FROM WINLOG: ' + @SQLString
 
PRINT '-- 10. DISPLAY RESULTS--'
SET @ParmDefinition = N'@xRowID INT';
EXECUTE sp_executesql @SQLString, @ParmDefinition, @xRowID = @1stRowID
DROP TABLE #winlog
END


When executed in my lab environment, this is what I saw (the latest entries are first):

rowID LogDate ProcessInfo textRow
105 2019-11-21 07:37:28.030 spid56 DBCC CHECKDB (Hospital_November212019) WITH no_infomsgs 
executed by WIN-NVFHQDPGP9D\Administrator found 0 errors and repaired 0 errors. 
Elapsed time: 0 hours 0 minutes 0 seconds. Internal database snapshot has split 
point LSN = 00000022:00000b2f:0001 and first LSN = 00000022:00000b2d:0001. 

104 2019-11-21 07:37:27.810 Backup RESTORE DATABASE successfully processed 449 pages 
in 0.029 seconds (120.740 MB/sec).

103 2019-11-21 07:37:27.780 Backup Database was restored: Database: Hospital_November212019, 
creation date(time): 2019/01/03(14:07:43), first LSN: 34:2822:1, last LSN: 34:2825:1, 
number of dump devices: 1, device information: (FILE=1, TYPE=DISK: 
{'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\Hospital.bak'}). 
Informational message. No user action required.

102 2019-11-21 07:37:27.760 Backup Restore is complete on database 'Hospital_November212019'. The database 
is now available.

101 2019-11-21 07:37:27.620 spid56 Parallel redo is shutdown for database 'Hospital_November212019' 
with worker pool size [1].

100 2019-11-21 07:37:27.530 spid56 Parallel redo is started for database 'Hospital_November212019' 
with worker pool size [1].

99 2019-11-21 07:37:27.500 spid56 Starting up database 'Hospital_November212019'.



Note that this script only works for full and not incremental backups.  The script needs to be able to find at least one full backup or it will fail.