More on Oracle Database high availability and security
Find and secure your sensitive Oracle database data
Ensure high availability in Oracle Database 11g R2
Learn about Oracle RAC’s distance limitations
This is the first in an occasional “faceoff” series between Oracle and competing technologies. This installment is Oracle vs. SQL Server. This column takes the Oracle Database side, while another one argues for Microsoft SQL Server.
Oracle provides several advantages over the SQL Server platform. They both can provide enterprise technology, but when it comes to security and high availability, Oracle provides solid tools and software above the rest.
Before getting into specifics around security and high availability, one key factor that needs to be called out is the read consistency of the Oracle database. Every query issued in the Oracle database returns rows at the same point in time. Even with long running queries, the result set provides read consistency. If there are several transactions that might hit the database, using the undo transaction information, the first rows returned are consistent with the last rows returned.
PL/SQL is a more flexible and powerful coding language than T-SQL. The extensive set of features and error handling allow for programming large-scale applications and effective coding for database activities. In Oracle Database 11g, the Edition-Based Redefinition allows code changes to occur while the database is in use, minimizing downtime to redefine objects and upgrade application components.
High availability in Oracle Database
The options available to architect a high availability tool in Oracle reach from clustering to off-site active standby database servers. Real Application Clusters (RAC) provide failover to other nodes in the cluster while allowing active-active clustering to use resources across all available nodes. The Active Data Guard standby database can be in the same location or at another location to provide an alternative disaster recovery option, but it can be available for queries, reporting or backups as well. It is also a good way to test production changes, after which it can sync back up to the production database server.
With high availability options, one should also consider recovery options, especially for upgrades, or data issues. The ability of the Oracle Database to flashback to a point in time, or flashback a query, places quick options into the database administrator’s hands to bring back data, objects for troubleshooting or a swift recovery. This capability also provides options for looking at what changed by being able to query a table as of a change or point in time.
Automatic Storage Management (ASM) provides a file manager for Oracle files. With Oracle 11g, Automatic Storage Management Cluster File System (ACFS) delivers file management and security for files outside of the database as well as those within.
Oracle security, engineered systems
Oracle also provides a host of engineered solutions which make the configuration easier and provide shorter rollout times to production while implementing the features of high availability and manageability. The Independent Oracle User Group (IOUG) has a group of members with the Exadata SIG to provide best practices and practical experience with use of the Exadata machines.
Security is also important to the database environment. Oracle Transparent Data Encryption provides a high level of security, encrypting data at a column and tablespace level transparent to the application. It encrypts files at rest, which means backups and exports also contain encrypted data. Other security features include fine grain access and virtual private databases to restrict access to sensitive areas even if there is direct access to the database outside of the application and application security. Should the need arise, database administrator access can be restricted using Oracle Database Vault. Auditing system privileges and changes can be captured and audited as there are a host of commands audited by default in Oracle 11g.
ABOUT THE AUTHOR
Michelle Malcher is a database administrator and a member of the IOUG Board of Directors. She plans content on topics such as high availability, security, PL/SQL and best practices and designs deep dives at Collaborate 2012 – IOUG Forum, an annual conference for the Oracle user community.
This was first published in April 2012