Tag Archives: Trivadis Content

Blog posts also posted on the Trivadis Blog (TriBlog)

Script to download Oracle Patch

Downloading Oracle software, patch or patch-set via web browser is handy if you need the software on your client PC or if you just download small patch’s. As soon as you want, however, download a greater volume of patches or a large patch set, it gets cumbersome. After downloading the patch must be copied to the target system. All steps could be quite time consuming depending on your network throughput.

WGET Option

Since a while it is possible to select a WGET Option in the download dialog rather than downloading each file individual (red box in the picture below).

MOS download dialog

In a new dialog box you then my download or copy the wget download script for the selected patch’s.

MOS download wget

Before starting the download via script the MOS credential have to be modified eg. SSO_USERNAME=youraccount
SSO_PASSWORD=yourpassword

But…

So far so good, but currently it is now working. According to Oracle Support this is a known issue and there is the Bug 12372706: WGET SCRIPTS FROM MOS FAIL IN PRODUCTION.

To workaround each file has to be downloaded manually with wget.


wget --http-user=username --http-password=password --no-check-certificate \
--output-document=filename "paste the above copied address here in quotes"

The URL’s can be copied from the download dialog above. If more than just one patch have to be downloaded wget can be put in a for loop which get’s the URL’s from a text file.

Download URL’s

Text File with Patch URL’s

#Linux x86-64
https://updates.oracle.com/Orion/Services/download/p10098816_112020_Linux-x86-64_1of7.zip?aru=13149219&patch_file=p10098816_112020_Linux-x86-64_1of7.zip
https://updates.oracle.com/Orion/Services/download/p10098816_112020_Linux-x86-64_2of7.zip?aru=13149219&patch_file=p10098816_112020_Linux-x86-64_2of7.zip
https://updates.oracle.com/Orion/Services/download/p10098816_112020_Linux-x86-64_3of7.zip?aru=13149219&patch_file=p10098816_112020_Linux-x86-64_3of7.zip
https://updates.oracle.com/Orion/Services/download/p10098816_112020_Linux-x86-64_4of7.zip?aru=13149219&patch_file=p10098816_112020_Linux-x86-64_4of7.zip
https://updates.oracle.com/Orion/Services/download/p10098816_112020_Linux-x86-64_5of7.zip?aru=13149219&patch_file=p10098816_112020_Linux-x86-64_5of7.zip
https://updates.oracle.com/Orion/Services/download/p10098816_112020_Linux-x86-64_6of7.zip?aru=13149219&patch_file=p10098816_112020_Linux-x86-64_6of7.zip
https://updates.oracle.com/Orion/Services/download/p10098816_112020_Linux-x86-64_7of7.zip?aru=13149219&patch_file=p10098816_112020_Linux-x86-64_7of7.zip

Download more patch

For loop to download the patch’s:

for i in $(cat download_url.txt|grep -v ^#)
do
OUTPUT_FILE=$(echo "$i"|cut -d= -f3)
echo "download $OUTPUT_FILE from '$i'" >> $LOGFILE 2>&1
wget --http-user=MOS_USER --http-password=MOS_PASSWORD --no-check-certificate \
-O $OUTPUT_FILE "$i" >> wget_logfile.log 2>&1
done

MOS Download Script

I’ve put everything in a small script. To download the patch a text with the download URL’s have to be specified


mos_download_url.sh -h
INFO : Usage, mos_download_url.sh [-hv]
INFO : -h Usage (this message)
INFO : -u MOS user account
INFO : -p MOS password
INFO : -f Text file with download url
INFO : Logfile : mos_download_url.sh-04-22-11-1422.log

Run the script with nohup on a stage server to download a few patchs.

nohup mos_download_url.sh -u me@domain.com -p secret -f download_url.txt &

The mos_download_url.sh script can be downloaded in the script section of OraDBA or direct ( mos_download_url.sh).

Oracle Database Firewall Test Environment

I’ve tried to have a closer look into the new Oracle Database Firewall. Unfortunately I’ve struggled around already with the installation or more with the setup of the test environment. But lets start at the beginning. According to the Installation Guide Oracle® Database Firewall Installation Guide Database Firewall and Management Server has the following hardware requirements:

  • Oracle Enterprise Linux 5 Update 5
  • 1 GB Memory
  • 80 GB of disk space
  • Three network ports

First Steps

Because I planned to set up the Database Firewall in in-line mode, therefor I’ve decided to setup three VM’s. a Database Server, a Windows Client and the Database Firewall VM (see picture further down). All VM’s has been configured with network interface type host-only. I’ve just assumed that the TCP/IP network (eg. subnet’s) can be configured a bit later and I’ve “overread” that the Database Firewall is working as a network bridge rather than a router. So I’ve ended up with a network bridge where both ports have been connected to the same switch. If I would have to setup the test environment physically I would never get the idea to do this 🙂

Solution

Configure the in-line mode means setting up a transparent network bridge between two physical separated network. The IP network is the same as without Database Firewall.

For my test environment on VMWare Fusion I’ve created a second host-only network vmnet2. VMWare Workstation has a utility to add more network’s but on VMWare Fusion this has to be done manually (config files or with tokamak.sh). This second network has the same IP range and network mask as the vmnet1, but it is only available through the database firewall. That means on the host system is no routing configured.
As you can see in the image below, the Database VM and on interface of the Database Firewall are configured to use vmnet2. The two other interface on the Database Firewall as well the Windows Client VM are configured to use vmnet1.

Demo Infrastruktur

As soon as the VM’s are configured with the right networks, it is an easy task to install and configure the Database Firewall according the short Documentation (Installing Oracle Database Firewall ).

Since I have now a running test environment I’ll start to make a few test with the Database Firewall. Stay tuned to read more….

Oracle CPU Pre-Release Announcement – April 2011

Late last week Oracle published the Oracle Critical Patch Update Pre-Release Announcement – April 2011. The official Oracle Critical Patch Update for April 2011 will be released somewhen on the 19th of april.

This CPU includes up to 73 security fixes for all kind of Oracle products. 6 out of them are just forfor the Oracle Database Server. 2 of these vulnerabilities may also be remotely exploitable without authentication. Due to the fact that the highest CVSS score for the database security fixes is just 6.5 this CPU does not look as critical as others. But to make a clear statement we have to wait for the official release of the patch’s. As soon as they are available I’ll make some tests.

More information on the CPU and Oracle Security:

Case Sensitive Passwords and Strong User Authentication

With 11g R1 Oracle introduced case sensitive passwords for database accounts based on the SHA1 hash algorithm. This feature can easily be enabled with the init.ora parameter SEC_CASE_SENSITIVE_LOGON. As soon as this parameter is set to true, all new passwords will be case sensitive. Existing passwords will remain case insensitive until they are changed.
The downside of this new feature is, that the passwords are also stored with the pre-11g database password hash. This is a potential security leak. The pre-11g password hash string from USER$ can be used to crack the case insensitive version of the password. All kind of tools, utilities, password lists etc are available to do this. As soon as the case insensitive version of the password is known, the case sensitive password can be guessed.

Case Sensitiv Passwords

First of all lets have a look at the parameter of an 11g R2 test database.

show parameter sec_case_sensitive_logon

NAME TYPE VALUE
------------------------- -------- ---------
sec_case_sensitive_logon boolean TRUE

The Column PASSWORD_VERSIONS in DBA_USERS shows the Database version in which the password was created or changed. The user TEST_10G shows only 10g which means that this user has been created before the database has been migrated to 11g and was never changed.

SELECT username, password_versions
FROM dba_users
WHERE username LIKE 'TEST%';

USERNAME PASSWORD
--------------- --------
TEST 10G 11G
TEST_11G 10G 11G
TEST_10G 10G

The Password hashes for both the 11g (SPARE4) and pre-11g hashes (PASSWORD)

set linesize 120
col name for a10
col password for a16
col spare4 for a50
select name,password,spare4 from user$ where name like 'TEST%';

NAME PASSWORD SPARE4
---------- ---------------- --------------------------------------------------
TEST 7A0F2B316C212D67 S:7D5C8604CDF7811E06DAA7C718ADB3684A883CE7521CF5C0
66721877D457
TEST_10G 48AFCE9CD794074D
TEST_11G AE6FC028DF3997FC S:CFD77E59711BC61589C6631C1F824CFC0966972D01599EF6
ED1558A2046F

As you can see user TEST and TEST_11G have a pre-11g Hash and the long 11g Hash. The user TEST_10G only have a pre-11g Hash. This indicates that the user has been created before the database was migrated to 11g and the password never has been changed. Therefor the password for this user is case insensitive even when the parameter SEC_CASE_SENSITIVE_LOGON is set to true.

To enable or disable case sensitive passwords just alter the init.ora parameter.

alter system set SEC_CASE_SENSITIVE_LOGON=true scope=spfile;
alter system set SEC_CASE_SENSITIVE_LOGON=false scope=spfile;

Increase Security

The Idea

The security can be increase when case sensitive password are used and logon’s are limited to the 11g authentication protocols. This can be achievement by setting the sqlnet parameter SQLNET.ALLOWED_LOGON_VERSION to 11. As soon as this has been done the pre-11g hashes can be removed from USER$.

In detail the following steps are required to enable Oracle Database 11g exclusive mode and increase database security.

  • Make sure all application’s and clients are supporting the 11g authentication protocols
  • After migration / database creation enable SEC_CASE_SENSITIVE_LOGON
  • Change passwords on all database accounts. A minimum of 10 alphanumeric characters, special characters and mixed case is recommended
  • Verify and test batch jobs or scripts to make sure the use of mixed chases is consistent
  • Edit the sqlnet.ora parameter file and add the line SQLNET.ALLOWED_LOGON_VERSION=11 or change it to 11
  • Remove the old password hash values from USER$

Remove the old password hash

To remove the old password hash values it is necessary to perform an update on a SYS table. Therefor a full backup of the database is highly recommended. As soon as you have a complete backup, connect to the Oracle Database with SYSDBA privileges to perform the following update.

update sys.user$ set password=NULL;
delete from user_history$;
commit;

From now the password column will be empty even when new user are created as you can see in the following example.

select name,password,spare4 from user$ where name like 'TEST%';
NAME PASSWORD SPARE4
---------- ---------------- --------------------------------------------------
TEST S:4C15788E25102B7B70713D3BD054EF2731E9664CA4FCFD67
BA224E15C5CA
TEST_10G S:35547B514FCA0C895259A4CAD1E50A88AFE086B9844ACF97
A6B9E211490B
TEST_11G S:CDE994528EF0CED53917790F412C1CE4280A05E5007CC0E1
0407337B407
create user test_new identified by Test001;

User created.

select name,password,spare4 from user$ where name like 'TEST%';

NAME PASSWORD SPARE4
---------- ---------------- --------------------------------------------------
TEST S:4C15788E25102B7B70713D3BD054EF2731E9664CA4FCFD67
BA224E15C5CA
TEST_10G S:35547B514FCA0C895259A4CAD1E50A88AFE086B9844ACF97
A6B9E211490B
TEST_11G S:CDE994528EF0CED53917790F412C1CE4280A05E5007CC0E1
0407337B407A
TEST_NEW S:D4808E96184DE35110A5CDFC83A7C496402147338C77185D
336748914299

Conclusion

The sqlnet.ora parameter SQLNET.ALLOWED_LOGON_VERSION provides an easy way to increase the overall database password security for 11g environments (Database and Client / Applications), but it does not replace a well-defined password policy. If user have simple passwords like username=password they still can easily be guessed.

Reference

Metalink Notes related to Case Sensitive Passwords and the Oracle Database 11g Exclusive Mode.

  • 11g R1 New Feature: Case Sensitive Passwords and Strong User Authentication [429465.1]
  • Instructions for Clearing pre-11g Database Password Hashes [463999.1]

Manage Audit Trails with DBMS_AUDIT_MGMT

I’ve recently wrote an DOAG article about Managing Audit Trails with dbms_audit_mgmt, which has been officially introduced in Oracle 11g R2. The same article will also be in the upcoming SOUG newsletter. The article itself is written in german and is also available on this side in the publication section ( Audit Management mit DBMS_AUDIT_MGMT.pdf)

dbms_audit_mgmt has been officially released as a 11g R2 feature. For earlier releases there is a patch available or it is part of a patchset (10.2.0.5, 11.1.0.7). More information to the availability of this package can be found in the Metalink Note New Feature DBMS_AUDIT_MGMT To Manage And Purge Audit Information [731908.1].

In the article I’ve mentioned the licensing, but could not provide a clear statement. In the meantime I’ve been in contact with Oracle to bring a bit of light into the licensing situation (ok just for dbms_audit_mgmt not for the whole oracle licensing 😉 ). As mentioned in the Metalink Note [731908.1] it is mandatory to have a valid Oracle Audit Vault license when using this package in any versions before 11.2. Starting with 11.2 the dbms_audit_mgmt package is part of the regular release and can be used in the Standard and Enterprise Edition without further licensing.

The official feedback:

… I now have some further feedback from the audit development team, and can confirm this package is available with SE and EE starting with 11.2. No further license is required.

As soon as the remaining bugs are fixed (see Current issues Known Issues When Using: DBMS_AUDIT_MGMT [804624.1] we have a nice and smooth way to handle the audit trails. I’ll probably post a few ideas and examples in one of my next posts.

Oracle Critical Patch Update January 2011

It’s not the latest news, but still in the same week 😉 …

On January 19th Oracle released the first Critical Patch Update (CPU) for 2011. This CPU includes up to 66 security fixes across all product families. The number looks quite high, but thats just because SUN Products like Open Office and the SUN Product Suite covered as well by this CPU. For database server issues there are only 7 security fixes, where 5 fixes are for the Oracle Database Server, 1 is for Oracle Secure Backup and 1 for Oracle Audit Vault. The one for Oracle Audit Vault may be remote exploit without any authentication and has a CVSS score of 10. The highest CVSS score of the 5 security fixes for the Oracle Database Server is 7.5 which is still fairly high. I would therefor highly recommended to apply this security fix on Audit Vault installation and recommend to install it as well on Oracle Database Server as soon as possible.

In the next day’s I will install the CPU’s on some databases to test if there are any issues.

More information on the CPU and Oracle Security:

This entry was posted in Critical Patch Update, Oracle Database, Patch Set Update, Security, Security Patch Update and tagged on by .

Metalink Notes, some favorites

If time permits, I try to have a look once a day at the Knowledge Articles listed on my My Oracle Support (MOS) Dashboard. I’ve configured my Dashboard to show recently updated knowledge articles and alerts. Before Metalink was migrated to the flash based My Oracle Support, it was possible to configure a regularly e-mail newsletter with similar information but with MOS this somehow does not work anymore for me. Nevertheless going through the notes and alerts is a simple way to learn everyday something new about Oracle. Especially reading the alerts about bug you will find from time to time interessting internal stuff. Tanel Poder wrote about this topic in his blog quite a while ago.

During my daily MOS review I realized, that there are since a while more and more notes around with titles like Master Note for… or All about…. These notes provides often a rough overview and a good starting point to dig a bit deeper into a particular topic. For my first technical post I thought it would be a good idea to providing a list with some of these notes. Due to the fact that my blog audience is not yet that big, the list is at least a nice personel reference to some notes 🙂 The list is not concluding, I only pick a few which a read or plan to read. There are much more…

Not all of the notes have the same quality but nevertheless they are a good staring point into a certain topic. For a bether overview I’ve put them in a few different categories. Some of the notes would fit into more than just one category. For now I just post this list as a reference. I will probably look a bit deeper into some of the notes in a later post. By the way, you probably have to log into MOS before you click on one of theese links.

Security

Database Security and Security Feature related Notes.

  • All About Security: User, Privilege, Role, SYSDBA, O/S Authentication, Audit, Encryption, OLS, Database Vault, Audit Vault [207959.1]
  • Master Note For Transparent Data Encryption [1228046.1]
  • Quick and dirty TDE Setup and FAQ [1251597.1]
  • Managing TDE wallets in a RAC environment [567287.1]
  • Master Note For Oracle Database Vault [1195205.1]
  • Master Note For Oracle Audit Vault [1199033.1]

Backup & Recovery, Availability and related Features

Master notes related to backup & recovery and availability.

Database Feature, Options and Components

A few master notes on database components and features.

  • Master Note for Oracle Data Mining [1087643.1]
  • Master Note for Oracle JVM [1098505.1]
  • Master Note for Oracle Multimedia/interMedia [1078222.1]
  • Master Note for Oracle Spatial and Oracle Locator Installation [220481.1]
  • Master Note for Oracle XML Developer’s Kit [1133826.1]
  • Master Note for the Oracle OLAP Option [1107593.1]
  • Master Note for OLTP Compression [1223705.1]
  • Master Note for Oracle XML DB Protocols: FTP HTTP HTTPS WebDAV, APEX and Native Database Web Services [1083991.1]
  • Master Note for Oracle Database Machine and Exadata Storage Server [1187674.1]
  • Master Note for Automatic Storage Management (ASM) [1187723.1]
  • Master Note for Transportable Tablespaces (TTS) — Common Questions and Issues [1166564.1]

Troubleshooting and Performance Tuning

A few master notes related to database troubleshooting and performance tuning.

  • Introduction to 600/7445 Internal Error Analysis [390293.1]
  • How to deal with ORA-600 Internal Errors [146581.1]
  • Master Note for Diagnosing ORA-600 [1092832.1]
  • Master Note for Diagnosing ORA-7445 [1092855.1]
  • Master Note: How to diagnose Database Performance – FAQ [402983.1]
  • Master Note: Query Tuning Overview [199083.1]
  • FAQ: Query Tuning Frequently Asked Questions [398838.1]
  • Diagnostics for Query Tuning Problems [68735.1]
  • Master Note for Diagnosing ORA-4030 [1088267.1]
  • Master Note for Diagnosing ORA-4031 [1088239.1]
  • ORA-4031 Common Analysis/Diagnostic Scripts [Video][430473.1]
  • 11g Understanding Automatic Diagnostic Repository. [422893.1]
  • 11g Diagnosability: Frequently Asked Questions [453125.1]
  • Master Note for Troubleshooting Oracle Managed Distributed Transactions [100664.1]

Oracle Enterprise Manager Grid Control

Master notes for Grid Control, DB Console and related Tools.

  • Master Note for 10g Enterprise Manager Grid Control Security Framework [1092513.1]
  • Master Note for 10g Grid Control Enterprise Manager Communication and Upload issues [1086343.1]
  • Master Note for 10g Grid Control OMS Process Control (Start, Stop and Status) & Configuration [1081865.1]
  • Master Note for 10g Grid Control OMS Performance Issues [1161003.1]
  • Master Note for 10g Grid Control Agent Process Control (Start, Stop & Status) & Configuration [1082009.1]
  • Master Note for Diagnostic Tools for 10g Enterprise Manager Grid Control Components [1098262.1]
  • Master Note for Target Maintenance Through 10g Enterprise Manager Grid Control [1202453.1]
  • Master Note for 10g Grid Console Browser / User-interface Issues [1190323.1]
  • Master Note for 10g Enterprise Manager Grid Control Agent Performance & Core Dump issues [1087997.1]
  • Master Note for Grid Control 11.1.0.1.0 Installation and Upgrade [1067438.1]
  • EMDIAG Master Index [421053.1]

Miscellaneous

A lot of other notes which did not fit in one of my 3-4 sections.

  • Master Note For Oracle Database Upgrades and Migrations [1152016.1]
  • Master Note For Oracle Database Downgrade [1151427.1]
  • Master Note For Oracle Database Server Installation [1156586.1]
  • Master Note of Linux OS Requirements for Database Server [851598.1]
  • Master Note for Inventory [1192649.1]
  • Master Note on Oracle Universal Installer (OUI) for installing Oracle Database Software [1157464.1]
  • Master Note For Cloning Oracle Database Server ORACLE_HOME’s Using the Oracle Universal Installer (OUI) [1154613.1]
  • Master Note For OPatch [293369.1]
  • Master Note For Oracle Database Client Installation [1157463.1]
  • Master Note For The Oracle ODBC Driver [741033.1]
  • Master Note For The Oracle Provider for OLE DB (OLE DB) [1132946.1]
  • Master Note Index for Oracle Application Server (Fusion Middleware) [1074916.1]

One more thing…

If you do not like the flash version of MOS there is also a HTML version around. The functionality is a bit limited, but you still can access all important information. Very handy if you access MOS from a device without flash (e.g. iPad). The HTML version is available through the link http://supporthtml.oracle.com. More information to the HTML version can be found in the article Master Note for My Oracle Support – TUGBU Tools and Tips (1151721.1] or My Oracle FAQ [747242.5]