by Robert Buda | Jun 8, 2020 | Best Practices, Database Security, Oracle
Have you been wanting to encrypt your Oracle database “since forever,” but feel like you just can’t afford the downtime? If a lot of data is involved, taking it all offline and encrypting it could be very time-consuming. So you’ve been putting the process off, while keeping your fingers crossed that your company’s network security will somehow protect you from a data breach and associated legal, compliance and reputational impacts.
But did you know that you can now encrypt existing tablespaces in-place, either online or offline in Oracle? In case you missed it, Oracle Enterprise Edition version 12.2 (released in 2017) added Transparent Data Encryption (TDE), a much-needed feature that enables you to encrypt an existing database while it remains online.
If you’ve been running an earlier Oracle version and haven’t seen a compelling reason to update, TDE could be it. This capability is a game-changer for those who want to “do the right thing” and encrypt their data at rest, but haven’t wanted to incur the downtime.
At a high level, here is how TDE works:
-
- First, encrypt the system tablespaces (these must be done separate from user tablespaces)
- Next, encrypt the user tablespaces, one at a time.
- Finally, drop and recreate any temporary tablespaces (these cannot be converted online)
That’s basically all there is to it! There are some technical issues that your DBA and/or security group will need to work out, such as key management and disk space. (You must have enough available disk space during the conversion to duplicate your largest tablespace.)
Of course, you need to back up your entire database before you start the encryption process. If you decide to tackle encryption gradually, then just back up each tablespace before you convert it.
Taking the important step of encrypting your sensitive data at rest will significantly improve your security posture.
So what are you waiting for? Get encrypting!
To schedule a free consultation on your database security, including encryption requirements, contact Buda Consulting.
by Robert Buda | May 13, 2020 | Database Patch News, database patching, Oracle, SQL Server
Welcome to Database Patch News, Buda Consulting’s newsletter of current patch information for Oracle and Microsoft SQL Server. Here you’ll find information on available patches—including security patches—and desupported versions recently made available.
Why should you care about patching vulnerabilities and bugs? Two big reasons:
-
- Unpatched systems are a top cyber attack target. Patch releases literally advertise vulnerabilities to the hacker community. The longer you wait to patch, the greater your security risk.
- Along with running a supported database version, applying the latest patches ensures that you can get support from the vendor in case of an issue. Patching also helps eliminate downtime and lost productivity associated with bugs.
Here are the latest patch updates for Oracle and SQL Server:
Oracle Patches:
Apr 14 2020 Quarterly Patch Updates:
19c – Release Update 19.7 available
18c – Release Update 18.10 available
12.2.0.1 – APR 2020 Release Update 12.2.0.1.200414 available
Regular support ends Mar 2023 and extended support ends Mar 2026.
12.1.0.2 – Currently in extended support.
The last freely available patch was July 2019 for 12.1.0.2.
Apr 14 2020 PSU available but may require extended support purchase to access it.
Patches will be released until July 2021 for this version.
PATCH SET UPDATE 12.1.0.2.200414 available
11.2.0.4 – Entered extended support December of 2017
Last free available patch was October 2018 for 11.2.0.4.
PATCH SET UPDATE 11.2.0.4.200414 available but may require clients purchase extended support to have access to it.
SQL Server Patches:
SQL Server 2019
Cumulative update 3 (Latest build) Released March 12, 2020
Mainstream support ends Jan 7, 2025
Extended support ends Jan 8, 2030
SQL Server 2017
Cumulative update 20 (Latest build) Released Apr 7, 2020
Mainstream support ends Oct 11, 2022
Extended support ends Oct 12, 2027
SQL Server 2016 Service Pack 2
Cumulative update 12 Release date: Feb 25, 2020
Mainstream support ends Jul 13, 2021
Extended support ends Jul 14, 2026
SQL Server 2014 Service Pack 3
Cumulative update 3 Release date: Apr 16, 2019
Mainstream support ended Jul 9, 2019
Extended support ends Jul 9, 2024
SQL Server 2012 Service Pack 4
Release date: Oct 5, 2017
Mainstream support ended Jul 11, 2017
Extended support ends Jul 12, 2022
Note: All other SQL Server versions not mentioned are no longer supported.
by Robert Buda | May 12, 2020 | Database Architecture, Oracle, SQL Server
What is a schema as opposed to a database as opposed to an instance? And how do schemas differ between Oracle and MSSQL? Or between Oracle and PostgreSQL? Or between PostgreSQL and MongoDB?
These terms can be confusing, but they are very important when planning a database architecture. So let’s define these terms and discuss conceptually how they are similar and how they differ between database software implementations. This post will focus mostly on schemas, with some references to the other terms for context.
What is a database ?
A database is the collection of database files that contain the data being stored. These files hold both the user data and the metadata (data dictionary) that the database needs to make sense of the user data. The metadata includes the schema definitions (where applicable) as described below.
What is a database instance?
A database instance is the collection of all of the database software processes plus any memory structures required by those processes, plus the database files where the database data is stored. (See diagram)
The different software vendors treat the relationship between databases and instances in different ways.
Oracle supports one database per instance unless you are working with 12c and above and using Oracle Multitenant.
PostgreSQL supports multiple databases per instance. Some system catalogs are shared across all databases in an instance.
MSSQL supports multiple databases per instance. Each instance has a set of system databases that are shared across all databases served by that instance.
MongoDB supports multiple databases per instance.
What is a schema?
The concept of a schema can be a little confusing because there are three different relevant uses of the word “schema” in the context of an IT project.
-
- Mirriam Webster defines a schema as “a structured framework or plan, an outline.”
- In the realm of database technology, a schema means a structural definition of the data that you are storing. This essentially defines the datatypes of the data you are storing, and the organization of that data (into tables, documents, indexes and constraints, etc). This can be expressed in the form of a diagram such as an entity relationship diagram (ERD), or in a set of data dictionary language (DDL) statements, or in a JSON object.
- Some database vendors have extended the concept of a schema to include not just a definition of the structure of a set of data, but also a particular collection of objects that contain the data (tables, etc), and even the data itself. This is sometimes a named collection and is typically based on one of these factors:
- Who owns the objects (a database user)
- Who should have access to the objects (e.g., a database role that may be assigned to users)
- What the objects are used for (e.g., all objects for a given application or function within an application)
When implemented in this fashion, a schema can also be thought of as a namespace. An object can have the same name in two different schemas and the two objects will be distinct from each other.
It is interesting to note that MongoDB, which is a document database as opposed to a relational database, is sometimes called a schema-less database. MongoDB also has the concept of a schema, but it is purely a description of the structure of the data, more like definition 2 above than 3. A MongoDB schema does not represent the actual instance of the data, as it does with the relational databases mentioned.
To summarize, within the context of database management software, a schema is either a set of objects that contain data that is related in some logical way (user, access, application), or simply a definition of the structure of data.
Examples
Here are some example of schemas (see the diagram below):
-
- Schema JSMITH: A schema that contains all of the tables that belong to user Jsmith. This schema would typically simply be named the same as the user, and is often created automatically when that database user account is created. When the user connects to the database, this will typically be his default schema. So any objects that he creates will automatically be part of that schema. When he issues a query, unless he specifies a schema name as part of the name of the object he is querying, or changes his schema search path (this is done differently by each database vendor), the result set will come from the object by that name that exists in his default schema.
- Schema PAYROLL: A schema that contains all of the tables for the payroll application. This schema would typically be named for the application or a functional area within the application. When accessing data from the Payroll schema, users will need to either set their schema search path to the Payroll schema, or prefix all object names in the query with PAYROLL.
- Schema DBO: This built-in schema in MSSQL is the default for all users unless otherwise specified. In many SQL Server databases, almost all objects end up here. This is similar to the public schema in PosgtreSQL.
How does a schema differ between database vendors?
MSSQL and PostgreSQL have an actual object in the database called a schema. You can create and drop a schema, and you can assign access rights and ownership to a schema as a whole. In these environments, there is a loose connection between a schema and a database user. A schema may be owned by a database user. But a database user does not have to own any schemas. A schema may also be owned by a role instead of an individual user. If you want to drop a user that owns a schema, they can and must first transfer ownership of the schema to another user.
Oracle has the concept of a schema but it does not really have an object in the database called a schema. It is more conceptual. In Oracle, each database user may be an owner of objects, and the collection of objects owned by a given user is considered a schema. If a database user is dropped, all objects owned by that user (in that user’s schema) must be dropped first. A schema in Oracle does not exist independently of a database user. There is a command in Oracle called Create Schema that is essentially a wrapper that lets you create a database user and a set of objects to be owned by that user all at once. The Drop Schema command is really the same thing as Drop User. One cannot transfer ownership of an object from one user to another. The new user would need to recreate the object. (A CTAS query may be helpful here).
MongoDB, as mentioned earlier, uses the concept of a schema in database design and in the validation of the structure of incoming data. (Nice blog on this here.) But there is no object in the database known as a schema.
I hope this post helps pull together the concept of a schema and the way the different vendors have implemented schemas.
Please comment with any questions or examples that you think might be helpful, including for database vendors that are not listed here. Also, if you disagree with the way I defined schema, please let me know how you see it.
To talk over any questions you may have around schemas or database architecture in general, contact Buda Consulting.
by Robert Buda | May 11, 2020 | Database, licensing, Oracle
Do I really need Oracle Enterprise Edition? Do I need Oracle Enterprise Edition in order to have a standby database? What version of Oracle do I need?
We get questions like these a lot. Oracle’s editions and features can be a bit overwhelming. This post shares some key information that I hope will make your choice easier.
To keep this post to a manageable length, this post deals only with Oracle Version 12c and Version 19c, and excludes Oracle cloud offerings. A future post will cover those feature sets.
Oracle Standard Vs. Enterprise
For each version covered below, I took the feature set from Oracle’s licensing documentation and listed those things that are available only in the Enterprise Edition of Oracle, and not in any of the Standard Editions.
But before we look at the specific features available in each version, let’s explore two important categories of features:
Data Guard
One of the more frequently used options in Enterprise Edition is Data Guard. A set of Data Guard features is listed in the first table for each edition below. These are used to implement high availability; that is, having a standby database available that is kept up to date with your primary production database in case your primary database goes down.
If Data Guard related features are the only set of “Enterprise-only” features that you need, then you may still be able to use Oracle’s Standard Edition. Here’s why:
DBvisit Standby, an alternative to Oracle’s Data Guard, provides an active standby database using any version of Oracle. You will still need to license the database software on both the primary and the standby server, but both can be much less expensive Standard Edition licenses.
Oracle Security
Oracle has a number of important security options.
The advanced security option is an extra cost option that is only available with Enterprise Edition. This includes transparent data encryption, data redaction, data masking and subsetting (very helpful for development environments), and other security options. To adequately secure sensitive data at the database level, we advise that you use advanced security, and thus Oracle Enterprise Edition.
With that said, if you are not using advanced security, and the only Enterprise-only option you need is Data Guard, then we recommend taking a good look at Dbvisit.
Oracle Version 12c Features
Below are the features that are only available in Enterprise Edition for Oracle 12c. If you need anything on this list, then you need Oracle Enterprise Edition. Period!
Category |
Feature |
Data Warehousing and Business Intelligence |
Advanced Index Compression |
Data Warehousing and Business Intelligence |
Automatic Data Optimization |
Data Warehousing and Business Intelligence |
Basic Table Compression |
Data Warehousing and Business Intelligence |
Bitmapped index, bitmapped join index, and bitmap plan conversions |
Data Warehousing and Business Intelligence |
Deferred Segment Creation |
Data Warehousing and Business Intelligence |
Exadata Flash Cache Compression |
Data Warehousing and Business Intelligence |
Heat Map |
Data Warehousing and Business Intelligence |
Hybrid Columnar Compression |
Data Warehousing and Business Intelligence |
Hybrid Columnar Compression Row-Level Locking |
Data Warehousing and Business Intelligence |
In-memory Parallel Execution |
Data Warehousing and Business Intelligence |
Oracle Advanced Compression |
Data Warehousing and Business Intelligence |
Oracle OLAP |
Data Warehousing and Business Intelligence |
Oracle Partitioning |
Data Warehousing and Business Intelligence |
Parallel capture and apply via XStream |
Data Warehousing and Business Intelligence |
Parallel Data Pump Export/Import |
Data Warehousing and Business Intelligence |
Parallel index build/scans |
Data Warehousing and Business Intelligence |
Parallel query/DML |
Data Warehousing and Business Intelligence |
Parallel Statement Queuing |
Data Warehousing and Business Intelligence |
Parallel statistics gathering |
Data Warehousing and Business Intelligence |
Prefix Compression (also called Key Compression) |
Data Warehousing and Business Intelligence |
Summary management—Materialized View Query Rewrite |
Data Warehousing and Business Intelligence |
Transportable tablespaces, including cross-platform and full transportable export and import |
High Availability |
Application Continuity |
High Availability |
Automatic Block Repair |
High Availability |
Block change tracking for fast incremental backup |
High Availability |
Block-level media recovery |
High Availability |
Cross-platform Backup and Recovery |
High Availability |
Duplexed backup sets |
High Availability |
Fast-start fault recovery |
High Availability |
Flashback Database |
High Availability |
Flashback Table |
High Availability |
Flashback Transaction |
High Availability |
Flashback Transaction Query |
High Availability |
Global Data Services |
High Availability |
Lost Write Protection |
High Availability |
Online Datafile Move |
High Availability |
Online index rebuild |
High Availability |
Online index-organized table organization |
High Availability |
Online table redefinition |
High Availability |
Oracle Active Data Guard |
High Availability |
Oracle Data Guard—Far Sync Standby |
High Availability |
Oracle Data Guard—Real-Time Cascading Standbys |
High Availability |
Oracle Data Guard—Redo Apply |
High Availability |
Oracle Data Guard—Snapshot Standby |
High Availability |
Oracle Data Guard—SQL Apply |
High Availability |
Parallel backup and recovery |
High Availability |
Recovering tables and table partitions from RMAN backups |
High Availability |
Rolling Upgrade Using Active Data Guard |
High Availability |
Rolling Upgrades—Patch Set, Database, and Operating System |
High Availability |
Tablespace point-in-time recovery |
High Availability |
Transaction Guard |
High Availability |
Trial recovery |
High Availability |
Unused block compression in backups |
Integration |
Advanced Replication |
Integration |
Messaging Gateway |
Integration |
Sharded Queues |
Manageability |
Database Resource Manager |
Manageability |
Oracle Cloud Management Pack for Oracle Database |
Manageability |
Oracle Data Masking and Subsetting Pack |
Manageability |
Oracle Database Lifecycle Management Pack for Oracle Database |
Manageability |
Oracle Diagnostics Pack |
Manageability |
Oracle Fleet Patching and Provisioning
(formerly Rapid Home Provisioning) |
Manageability |
Oracle Real Application Testing |
Manageability |
Oracle Tuning Pack |
Manageability |
SQL Plan Management |
Manageability |
SQL Tuning Sets |
Networking |
Infiniband Support |
Networking |
Network Compression |
Networking |
Oracle Connection Manager |
Performance |
Adaptive Execution Plans |
Performance |
Attribute Clustering |
Performance |
Client Side Query Cache |
Performance |
Concurrent Execution of UNION and UNION ALL Branches |
Performance |
Database Smart Flash Cache |
Performance |
Fault Tolerant In-Memory Column Store |
Performance |
In-Memory Aggregation |
Performance |
In-Memory Column Store |
Performance |
PL/SQL Function Result Cache |
Performance |
Query Results Cache |
Performance |
Support for Oracle Exadata Storage Server Software |
Performance |
TimesTen Application-Tier Database Cache |
Performance |
Zone Maps |
Scalability |
Oracle NoSQL Database Basic Edition |
Scalability |
Quality of Service Management |
Security |
Enterprise User Security |
Security |
Fine-grained Auditing |
Security |
Oracle Advanced Security |
Security |
Oracle Database Vault |
Security |
Oracle Label Security |
Security |
Privilege Analysis |
Security |
Real Application Security |
Security |
Redaction |
Security |
Transparent Sensitive Data Protection |
Security |
Virtual Private Database |
Snapshots and Cloning |
Storage Snapshot Optimization |
Spacial |
Multimaster replication of SDO_GEOMETRY objects |
Spacial |
Parallel spatial index builds |
Spacial |
Partitioned spatial indexes |
Now we’ll look at features that are only available in certain versions of Oracle 12c Standard Edition, or that are limited in Standard Edition. The “Notes” column explains the limitations.
Category |
Feature |
SE1 |
SE/SE2 |
EE |
Notes |
Data Warehousing and Business Intelligence |
Oracle Machine Learning (formerly Advanced Analytics) |
N |
Y |
N |
Included with SE2 and EE
Not included with SE1 and SE
OML4R (Oracle R Enterprise) Server is limited to operation on Oracle Linux 6 or higher |
High Availability |
Optimization for Flashback Data Archive |
N |
Y |
N |
Basic Flashback Data Archive is in all editions. Optimization for Flashback Data Archive requires EE and the Oracle Advanced Compression option. |
Integration |
Basic Replication |
Y |
Y |
N |
SE/SE1/SE2: read-only, updateable materialized view |
Integration |
Oracle Streams |
Y |
Y |
N |
SE/SE1/SE2: no capture from redo |
Manageability |
Instance Caging |
N |
Y |
N |
Included with SE2 and EE
Not included with SE1 and SE |
Spacial |
Oracle Spatial |
N |
Y |
N |
Included with SE2 and EE
Not included with SE1 and SE |
Spacial |
RDF Graph |
N |
Y |
N |
Included with SE2 and EE
Not included with SE1 and SE |
If you need any of the above features, then you may need Oracle Enterprise Edition, depending on the specifics as listed in the notes.
That’s it!
Your DBA team should be able to tell you if you need/use these Enterprise-only features.
If you don’t need any of these features, then potentially you can save many thousands of dollars by using the Standard Edition instead.
Oracle version 19c Features
OK, that’s it for 12c. Oracle 19c is essentially the same but with a somewhat different, more “evolved” set of options. The considerations mentioned above remain valid.
First, let’s look at the 19c features that definitely require Enterprise Edition:
Category |
Feature |
High Availability |
Application Continuity |
High Availability |
Oracle Sharding |
High Availability |
Oracle RAC One Node |
High Availability |
Oracle Data Guard—Redo Apply |
High Availability |
Oracle Data Guard—Far Sync Standby |
High Availability |
Oracle Data Guard—SQL Apply |
High Availability |
Oracle Data Guard—Snapshot Standby |
High Availability |
Oracle Data Guard—Real-Time Cascading Standbys |
High Availability |
Oracle Active Data Guard |
High Availability |
Rolling Upgrades—Patch Set, Database, and Operating System |
High Availability |
Rolling Upgrade Using Active Data Guard |
High Availability |
Online index rebuild |
High Availability |
Online table organization |
High Availability |
Online table redefinition |
High Availability |
Duplexed backup sets |
High Availability |
Block change tracking for fast incremental backup |
High Availability |
Unused block compression in backups |
High Availability |
Block-level media recovery |
High Availability |
Lost Write Protection |
High Availability |
Automatic Block Repair |
High Availability |
Parallel backup and recovery |
High Availability |
Tablespace point-in-time recovery |
High Availability |
Trial recovery |
High Availability |
Fast-start fault recovery |
High Availability |
Flashback Table |
High Availability |
Flashback Database |
High Availability |
Flashback Transaction |
High Availability |
Flashback Transaction Query |
High Availability |
Optimization for Flashback Data Archive |
High Availability |
Online Datafile Move |
High Availability |
Transaction Guard |
High Availability |
Cross-platform Backup and Recovery |
High Availability |
Global Data Services |
High Availability |
Recovering tables and table partitions from RMAN backups |
Integration |
Messaging Gateway |
Integration |
Sharded Queues |
Manageability |
Oracle Cloud Management Pack for Oracle Database |
Manageability |
Oracle Data Masking and Subsetting Pack |
Manageability |
Oracle Database Lifecycle Management Pack for Oracle Database |
Manageability |
Oracle Diagnostics Pack |
Manageability |
Oracle Tuning Pack |
Manageability |
Oracle Real Application Testing |
Manageability |
Database Resource Manager |
Manageability |
SQL Tuning Sets |
Manageability |
Oracle Fleet Patching and Provisioning (formerly Rapid Home Provisioning) |
Networking |
Infiniband Support |
Networking |
Oracle Connection Manager |
Networking |
Network Compression |
Performance |
Client Side Query Cache |
Performance |
Query Results Cache |
Performance |
PL/SQL Function Result Cache |
Performance |
Oracle TimesTen Application-Tier Database Cache |
Performance |
Database Smart Flash Cache |
Performance |
Adaptive Execution Plans |
Performance |
Concurrent Execution of UNION and UNION ALL Branches |
Performance |
Oracle Database In-Memory |
Performance |
In-Memory Column Store |
Performance |
In-Memory Aggregation |
Performance |
Attribute Clustering |
Scalability |
Oracle Real Application Clusters (Oracle RAC) |
Scalability |
Oracle NoSQL Database Basic Edition |
Security |
Column-Level Encryption |
Security |
Tablespace Encryption |
Security |
Oracle Advanced Security |
Security |
Oracle Database Vault |
Security |
Oracle Label Security |
Security |
Enterprise User Security |
Security |
Centrally Managed Users |
Security |
Fine-grained Auditing |
Security |
Privilege Analysis |
Security |
Real Application Security |
Security |
Redaction |
Security |
Transparent Sensitive Data Protection |
Security |
Virtual Private Database |
Snapshots and Cloning |
Storage Snapshot Optimization |
Spatial and Graph Data |
Parallel spatial index builds |
Spatial and Graph Data |
Multimaster replication of SDO_GEOMETRY objects |
Spatial and Graph Data |
Partitioned spatial indexes |
VLDB, Data Warehousing, and Business Intelligence |
Oracle Partitioning |
VLDB, Data Warehousing, and Business Intelligence |
Oracle On-Line Analytical Processing (OLAP) |
VLDB, Data Warehousing, and Business Intelligence |
Oracle Advanced Compression |
VLDB, Data Warehousing, and Business Intelligence |
Advanced Index Compression |
VLDB, Data Warehousing, and Business Intelligence |
Prefix Compression (also called Key Compression) |
VLDB, Data Warehousing, and Business Intelligence |
Hybrid Columnar Compression |
VLDB, Data Warehousing, and Business Intelligence |
Hybrid Columnar Compression Row-Level Locking |
VLDB, Data Warehousing, and Business Intelligence |
Heat Map |
VLDB, Data Warehousing, and Business Intelligence |
Automatic Data Optimization |
VLDB, Data Warehousing, and Business Intelligence |
Basic Table Compression |
VLDB, Data Warehousing, and Business Intelligence |
Deferred Segment Creation |
VLDB, Data Warehousing, and Business Intelligence |
Bitmapped index, bitmapped join index, and bitmap plan conversions |
VLDB, Data Warehousing, and Business Intelligence |
Parallel query/DML |
VLDB, Data Warehousing, and Business Intelligence |
Parallel statistics gathering |
VLDB, Data Warehousing, and Business Intelligence |
Parallel index build/scans |
VLDB, Data Warehousing, and Business Intelligence |
Parallel Data Pump Export/Import |
VLDB, Data Warehousing, and Business Intelligence |
In-memory Parallel Execution |
VLDB, Data Warehousing, and Business Intelligence |
Parallel Statement Queuing |
VLDB, Data Warehousing, and Business Intelligence |
Parallel capture and apply via XStream |
VLDB, Data Warehousing, and Business Intelligence |
Transportable tablespaces, including cross-platform and full transportable export and import |
VLDB, Data Warehousing, and Business Intelligence |
Summary management—Materialized View Query Rewrite |
If you need any of the above, the you must use Oracle Enterprise Edition
Now we look at the features that are included in the Oracle 19c Standard edition, but are limited.
Category |
Feature |
Notes |
Consolidation |
Oracle Multitenant – # of PDBs |
The number value in each column indicates the maximum number of pluggable databases (PDBs) that can be created for each offering.
For all offerings, if you are not licensed for Oracle Multitenant, then you may have up to 3 user-created PDBs in a given container database at any time. |
Manageability |
SQL Plan Management |
SE2: Only one SQL plan baseline per SQL statement is allowed and SQL plan evolution is disabled. Limits on baseline capture methods as well.
The following DBMS_SPM functions and procedures are not allowed: CONFIGURE, LOAD_PLANS_FROM_AWR, LOAD_PLANS_FROM_SQLSET, and all functions and procedures associated with SQL plan evolution. |
If you are not using pluggable databases, or if three databases are enough, and if you are not using sophisticated plan management, then you can likely use Standard Edition.
If you know of any other enterprise-only options that are not listed here, or if you think I misstated anything, please comment. I’d like to hear from you. Also, if you know of other good disaster recovery alternatives, let me know.
If you are not sure about any of this, or you just want to double-check your thought process, leave a comment or give me a call and I will be happy to have a conversation with you about it.
by Scott Loudon | Apr 22, 2020 | Backup and Recovery, Database, Oracle, Oracle DBA
One of our customers had issues over the past several months with Oracle Active Data Guard replication to a standby server. The database on the standby server would intermittently fall out of sync with the database on the primary server, and log files were not consistently shipping to the standby server.
Active Data Guard Replication Error
The Oracle database version was 12.2.0.1 running on Oracle Linux 6. The firewall was a Cisco ASA-5585-SSP-40, and the ASA version is 9.6(4)8.
TNS tracing showed: CORRUPTION DETECTED: In redo blocks starting at block #…
By the time I got involved, the firewall administrators had already implemented all the recommended firewall changes to disable the following:
-
- SQLNet fixup protocol
- Deep Packet Inspection (DPI)
- SQLNet packet inspection
- SQL Fixup
The following errors were noted in the primary database alert log:
-
- ORA-16055: FAL request rejected
- ARC6: Standby redo logfile selected for thread 2 sequence 33351 for destination LOG_ARCHIVE_DEST_2
- ARC6: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (12152)
- ARC6: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
The following errors were noted in the standby database alert log:
-
- CORRUPTION DETECTED: In redo blocks starting at block #…
- RFS[20]: Possible network disconnect with primary database
- Error 1017 received logging on to the standby
- FAL[client, USER]: Error 16191 connecting to…
- ORA-16191: Primary log shipping client not logged on standby
The root cause of the problem turned out to be a bug in the Cisco firewall. For reasons unknown, when the primary and standby database listeners were using port 1521, the firewall would ignore the settings the admins had implemented for the Oracle Data Guard connections and revert to the default settings. As a workaround, we changed to a different port.
If you are experiencing intermittent or hard-to-diagnose database issues in your environment, contact Buda Consulting.
by Robert Buda | Feb 19, 2020 | Best Practices, Database Patch News, database patching, Database Security, Oracle, SQL Server
Welcome to Database Patch News, Buda Consulting’s monthly newsletter of current patch information for Oracle and Microsoft SQL Server. Here you’ll find information on available patches—including security patches—and desupported versions made available during the past month.
Why should you care about patching vulnerabilities and bugs? Two big reasons:
- Unpatched systems are a top cyber attack target. Patch releases literally advertise vulnerabilities to the hacker community. The longer you wait to patch, the greater your security risk.
- Along with running a supported database version, applying the latest patches ensures that you can get support from the vendor in case of an issue. Patching also helps eliminate downtime and lost productivity associated with bugs.
Here are the latest patch updates for Oracle and SQL Server:Oracle Patches:
19c
DATABASE RELEASE UPDATE 18.9.0.0.0.200114
OJVM RELEASE UPDATE 18.9.0.0.0.200114
18c
DATABASE RELEASE UPDATE 19.6.0.0.0.200114
OJVM RELEASE UPDATE 19.6.0.0.0.200114
12cR2
DATABASE RELEASE UPDATE 12.2.0.1.200114
OJVM RELEASE UPDATE 12.2.0.1.200114
Regular support ends Mar 2023 and extended support ends Mar 2026
12cR1
DATABASE PATCH SET UPDATE 12.1.0.2.200114
(Extended Support Contract Required)
OJVM PATCH SET UPDATE 12.1.0.2.200114
(Extended Support Contract Required)
The last freely available patch was July 2019 for 12.1.0.2. The Oct 15 2019 Patch Set Update (PSU) is available but may require an extended support purchase to access it. Patches will be released until July 2021 for this version. PSU 12.1.0.2.191015 is available.
11gR4
DATABASE PATCH SET UPDATE 11.2.0.4.200114
(Extended Support Contract Required)
OJVM PATCH SET UPDATE 11.2.0.4.200114
(Extended Support Contract Required)
The last freely available patch was October 2018 for 11.2.0.4. PSU 11.2.0.4.191015 is available but may require clients to purchase extended support to access it.
Oracle Engineered Systems
Oracle Exadata System Software for 18.1.24, 19.2.10 & 19.3.4
Oracle Exadata QFSDP for Jan 2020
Oracle SuperCluster QFSDP for Jan 2020
SQL Server Patches:
SQL Server 2019 – Cumulative Update 1 released on 01/07/2019
SQL Server 2017 – Cumulative Update 18 released on 12/09/2019
SQL Server 2016 Service Pack 2 – Cumulative Update 11 released on 12/09/2019
SQL Server 2016 Service Pack 1 – Cumulative Update 15 released on 07/09/2019
SQL Server 2014 Service Pack 3 – Cumulative Update 4 released on 07/29/2019
SQL Server 2014 Service Pack 2 – Cumulative Update 18 released on 07/29/2019