Thursday, December 30, 2010

Clear and Present Danger

While contemplating about writing a blog on WikiLeaks situation, I was thinking about an appropriate title for this blog that would convey the essence of this blog. I was leaning towards a catchy phrase. And one of my favorite movies came to my mind - Clear and Present Danger starring Harrison Ford. This movie title does convey the essence of this blog. There is a clear and present danger.

Confidential US diplomatic cables released by WikiLeaks are just the latest episodes of security breaches. Before publishing hundreds of thousands of diplomatic cables, WikiLeaks dumped Iraq and Afghanistan war documents. WikiLeaks is now rumored to be targeting a major bank in the USA. There may be other leaks that we are not even aware of. There is a clear and present danger.

At the least, WikiLeaks dumps have embarrassed the United States Government. Hillary Clinton briefed the foreign government officials on the leaks and apologized. At the least, WikiLeaks dumps could have damaged our relations with our allies. At worst, WikiLeaks dumps could have jeopardized the lives of our troops in Iraq and Afghanistan. At worst, WikiLeaks could have caused irreparable damage to our relationships with our allies. Experts and government official all over the world are debating the impact of the leaks. Whatever the impact may be, there is a clear and present danger.

The United States Attorney General Eric Holder has opened up a full investigation into the WikiLeaks dump. I don’t have all the facts in front of me, but it appears that this was an insider job. WikiLeaks didn’t hack into the government data repositories and stole the documents. It is reported that an insider was responsible for providing all the confidential documents to WikiLeaks. An insider had access to the cables and war documents. An insider managed to download the documents and then forwarded the cache to WikiLeaks.

I am an Oracle Database Administrator, responsible for maintaining confidentiality, availability and integrity of the database. You may be as well. As a DBA, I take all the precautions to safeguard the confidentiality, availability and integrity of the database. As a DBA, I have developed many security check lists to tighten up the security on the databases and servers. Oracle Security Patches, Access Controls, Database Roles, Privilege Restrictions, Authorizations, Firewalls, Intrusion Detection Systems, Intrusion Prevention Systems, Virus and malicious code protection, SQL Injection Prevention techniques, Best Coding practices are just the few examples of technical security controls that are implemented by the DBAs to secure the database. I am absolutely certain that the confidential documents leaked by WikiLeaks were protected by the technical controls mentioned here and quite a few more were in place. It appears that these technical controls didn’t prevent the leaks. It appears that these technical security controls were not adequate.

Please don’t get me wrong here, the above mentioned technical security controls are absolutely essential. These controls lay the foundation for the adequate security. The level of controls that you implement may vary, but we need to implement them. You need to evaluate threats and vulnerabilities to assess the risk that you are carrying or willing to take. Your security posture should be aligned with the risks that you can afford to take. Please bear in mind that there is a clear and present danger.

In order to fight the clear and present danger, Oracle DBAs need to look beyond the technical controls. Technical controls, or lack thereof, do fail us from time-to-time, but it appears that the technical controls didn’t fail us in WikiLeaks instance.

It is widely reported that the procedure to classify documents is out of control. New York Times reports that number of documents classified as Confidential has skyrocketed during the last decade. And so does the number of government officials who have the authority to classify documents. And so does the number of people who have or need access to such documents. Per media reports, there are approximately 750,000 people with access to confidential documents. That's very large number of people with access to confidential documents. It's counter intutive. One shouldn't share the confidential information with too many folks! An example of how management and operational controls play a part in data security.

Management controls are those that deal with policies, procedures, scope, frequency, oversight, checks and balances, risk assessment, etc. Operational security controls are those that are enforced by the people.There are quite a few security standards that preach the importance of management and operational controls. NIST SP 800-53 classifies security controls in three categories – management, operational and technical. HIPAA categorizes security controls as administrative and technical. So does other prevailing security standards.

As Oracle DBAs, we do get carried away by technical controls. We need to make sure that proper management and operational controls complement the technical controls that we implement. There is a clear and present danger our there!

Saturday, November 27, 2010

How to Configure UTL_MAIL in Oracle DB

To install UTL_MAIL:

sqlplus / as SYSDBA

SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql
SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb

When you run the above mentioned two scripts, Oracle creates UTL_MAIL package. Oracle also creates a public synonym on UTL_MAIL package, but doesn't grant privileges to any user, DBA role or PUBLIC. You need to explicitly grant EXECUTE privilege on this package.

SQL> GRANT EXECUTE ON UTL_MAIL TO APPOWNER, JMEHTA;

Grant succeeded.

In addition, define int.ora parameter as shown below.

SQL> alter system set smtp_out_server= 'mysmtpserver.mydomain.com' scope both;

If you are in Oracle11g, configure network access control lists.

That's all you need to setup UTL_MAIL package. Send a test  email

exec UTL_MAIL.SEND (sender=>'me@mydomain.com', recipients=>'you@google.com', subject=>'Test Message', Message=>'test'); 

If you are receiving the following error, then double-check that SMTP server that you have defined is accessible.

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.UTL_MAIL", line 654
ORA-06512: at "SYS.UTL_MAIL", line 671




Oracle11g Upgrade - Don't Let New Features Bite You!


Oracle11g introduces several new features that are quite useful. Most of the features are available as soon as you upgrade to 11g, but few of them would require additional configuration in 11g. If you are not prepared, then these specific new features, that I will discuss in this blog, could break your applications. Fortunately, these new 11g features are not bugs! They are just features!

For smooth and flawless migration, you need to know the new features, you need to know the impact of these new features on your application, if any, and then you need to take appropriate mitigating steps. Otherwise, they could cause havoc the day-after 11g upgrade. In this blog, I will shed light on two such features that could break your application. One of them is Network Access Control List and the second one is case sensitive passwords.

Network Access Control List

Overview

Oracle database comes with several PL/SQL packages such as UTL_TCP, UTL_HTTP, UTL_MAIL, UTL_SMTP and UTL_INADDR that access network services through back-end Oracle database. UTL_MAIL is widely used to send emails from PL/SQL code. UTL_SMTP and UTL_TCP packages allow you to access back-end network resources over TCP/IP protocol. UTL_INADDR package resolves server names and IP addresses. These PL/SQL Network packages are quite useful when properly used, but outright dangerous if misused.

Oracle's Network PL/SQL packages provide direct access to network resources which are behind firewall and protected by several layers of security controls. In fact, these packages are so powerful that security auditors would closely review access granted on Network PL/SQL packages. Database security vulnerability scanners would raise a red-flag if you have granted access on Network PL/SQL packages to users or roles. By default, Oracle grants EXECUTE privilege on Network PL/SQL packages to PUBLIC. Not a good security practice at all. Therefore, DBAs would revoke PUBLIC access on Network PL/SQL packages and grant EXECUTE privilege to those who need it.

Sure, with controlled grants on Network PL/SQL packages, Oracle DBAs would reduce the security risk level, but the risk still lingers for potential security breaches. If you have access to Network PL/SQL packages, then you can access network resources that you are supposed to, and beyond. From a browser or a client outside the firewall, you potentially can access resources behind firewall.

To further tighten security on PL/SQL Network packages, Oracle11g introduces fine-grained access controls to these PL/SQL packages. With fine-grained access controls through Network Access Control Lists, you define what network resources should be accessed by the users. You can't use any SMTP server available on the back-end network. You can only use the SMTP server that you have been specifically granted access to. Oracle Network Access Control Lists define what network resources are available and accessible. An vastly improved security posture.

Oracle provides DBMS_NETWORK_ACL_ADMIN package to manage Network ACLs. A brief description on DBMS_NETWORK_ACL_ADMIN is in order as this package is little bit confusing. As explained below, a Network ACL is comprised of a list of network resources that you want to grant access to. For each network resource, you include server names and port range through upper and lower port numbers. A Network ACL is assigned to a user. A user can have connect or resolve privilege. Connect privilege includes resolve privilege. Resolve privilege is needed for UTL_INADDR package, but grant connect privilege for other Network PL/SQL packages.

Configuration

Configuration of Network Access Control List is relatively simple. There are two configuration steps:
  • Configure XML DB
  • Configure Network Access Control Lists

Configure XML DB

exec UTL_MAIL.SEND (sender=>'me@mydomain.com', recipients=>'you@gmail.com', subject=>'Test Message', Message=>'test'); 

In response to the above command, if you get the following error, then you need to configure XML DB.

ORA-24248: XMLDB extensible security not installed
ORA-06512: at "SYS.UTL_INADDR", line 19
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1

Let's verify that XML DB has not been installed.

SQL> SELECT COMP_NAME, STATUS FROM DBA_REGISTRY ;

COMP_NAME                                STATUS
---------------------------------------- ---------------------------
Oracle Database Catalog Views            VALID
Oracle Database Packages and Types       VALID

Login / as sysdba to install XML DB.

sqlplus / as SYSDBA

Rem    DESCRIPTION
Rem      Creates the tables and views needed to run the XDB system
Rem      Run this script like this:
Rem        catqm.sql <XDB_PASSWD> <TABLESPACE> <TEMP_TABLESPACE> <SECURE_FILES_REPO>
Rem          -- XDB_PASSWD: password for XDB user
Rem          -- TABLESPACE: tablespace for XDB
Rem          -- TEMP_TABLESPACE: temporary tablespace for XDB
Rem          -- SECURE_FILES_REPO: if YES and compatibility is at least 11.2,
Rem               then XDB repository will be stored as secure files;
Rem               otherwise, old LOBS are used. There is no default value for
Rem               this parameter, the caller must pass either YES or NO.
Rem    NOTES
Rem      Must be run connected as SYS

SQL> @$ORACLE_HOME/rdbms/admin/Catqm xdb xdb temp NO

Let's double-check that XML DB has been installed.

SQL> SELECT COMP_NAME, STATUS FROM DBA_REGISTRY ;

COMP_NAME                                STATUS
---------------------------------------- ---------------------------
Oracle Database Catalog Views            VALID
Oracle Database Packages and Types       VALID
Oracle XML Database                      VALID


exec UTL_MAIL.SEND (sender=>'me@mydomain.com', recipients=>'you@gmail.com', subject=>'Test Message', Message=>'test'); 

ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_MAIL", line 654
ORA-06512: at "SYS.UTL_MAIL", line 671
ORA-06512: at line 1

As you could see from the error message, we need to configure Network Access Control List (ACL). The steps are described below.

There are few important that little details that you should keep in mind.

Don't forget to COMMIIT after you execute DBMS_NETWORK_ACL_ADMIN package.
Parameters to DBMS_NETWORK_ACL_ADMIN are case sensitive.
You can grant access on network resources to users or roles. If you are planning to execute network packages within the context of PL/SQL packages, then you must grant privileges on network resources directly to users, not roles.

-- let's configure network ACL to fix ORA-24247 errors
-- Create a network ACL called Email.xml and grant this list to user JMEHTA with connect privilege
exec DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl=>'Email.xml', description=>'Email Access', principal=>'JMEHTA', is_grant=>TRUE, privilege=> 'connect');
-- Grant Email.xml ACL to JMEHTA1 user with connect privilege
exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl=>'Email.xml', principal=>'JMEHTA1', is_grant=>TRUE, privilege=> 'connect');
-- Add server to ACL with port range
exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl=>'Email.xml', host=>'mysmtpserver.mydomain.com', lower_port=>25, upper_port=>25);
COMMIT;


-- Data Dictionary View
select * from dba_network_acls ;
Few additional useful commands:
-- Use wildcards to grant access on all network resources within a domain
exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl=>'Email.xml', host=>'*.mydomain.com');
-- Unassign servers
EXEC DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL(host=>'*.mydomain.com');
EXEC DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL(host=>'mysmtpserver');
-- Drop a Network ACL
exec DBMS_NETWORK_ACL_ADMIN.DROP_ACL(acl=>'Email.xml');
COMMIT;

Now, test email should work.

Case-sensitive Network Access Control Lists

Passwords in Oracle11g are case-sensitive. You may need to disable password case sensitivity by disabling the following parameter.

SEC_CASE_SENSITIVE_LOGON=false

Wednesday, November 24, 2010

Oracle11g Upgrade - Start Here!


Many organizations are running their production applications on Oracle10g R2. The latest Oracle database is 11g R2. A thousand dollar question is whether to upgrade or not to upgrade to 11g.

Many organizations don’t see any business justification to upgrade to 11g at the moment. Many organization defers database upgrade task as long as possible simply because upgrade process could be long and resource-intensive. Not to mention Oracle10g database just works fine. We all have different priorities.

Business drivers for an Oracle11g upgrade may vary from organization to organization. New administrative features, new developmental features, improved performance, regulatory compliance, compatibility with other products are just a few drivers that I can think of for an Oracle11g upgrade project. The criticality and importance of these drivers may vary from an organization to organization, but regulatory compliance is one such driver that forces many organizations to upgrade.

Regulatory compliance mandates that all software must be patched with the latest security patches. Oracle security patches are available only to the customers with Oracle Support. Oracle10g R2 premier support ends next year in July 2011. What this means that Oracle security patches won’t be available since July 2011 onwards unless you purchase extended support at an additional cost. So most organizations may not have a choice but upgrade to 11g R2 by July 2011. Otherwise they jeopardize regulatory compliance. For this reason, we are expecting a spike in database upgrade activities over the next six months. In fact, we have just embarked on an upgrade project that is expected to be completed by early next year.

In this blog, I will share my thoughts and experience on Oracle11g upgrade project. Hopefully, you will find them useful.

Start Here!

Oracle11g upgrade must be treated as a project. There should not be any issues whatsoever during the upgrade and more importantly after the upgrade. Our applications must not break with Oracle11g. Upgrade must be 110% successful. Upgrade must be transparent, etc, etc, etc.

Proper upgrade planning is the only solution. Don’t underestimate the importance of proper planning. The following documents should help you get started:

  • Jay Mehta Blog (Just kidding!)
  • Oracle Database Upgrade Guide (Included in Oracle11g Documentation Library. Downloadable from Oracle Technet)
  • Oracle Database Operating System specific Installation Guide
  • Oracle 11gR2 Upgrade Companion [MetaLink ID 785351.1]

Pre-Upgrade Information Tool and Upgrade Diagnostic Tool
  • Install Oracle11g R2 software and get hold of$ORACLE_HOME/rdbms/admin/utlu112i.sql script. Run this script against the database you are planning to upgrade to understand what you need to fix before the upgrade.
  • In addition, download dbupgdiag.sql script from metalink and run against the database that you want to upgrade. 

Manual Upgrade

If you are planning to upgrade to 11g R2 manually, as we have decided to, the following metalink notes are indispensable:
  • Complete Checklist for Manual Upgrades to 11gR2 [MetaLink ID 837570.1]
  • RMAN Restore of Backups as Part of a Database Upgrade [Metalink ID 790559.1]

Customize SQL*Plus

SQL*Plus is one the most commonly used tool to access Oracle databases. Although I do use Oracle Grid/Database Controls and few other tools extensively, SQL*Plus is the tool of choice on the servers to perform variety of database administration activities such as ad-hoc queries, monitoring, upgrades, database maintenance, user management, space management, etc. etc. etc. Given its wide-spread usage on day-to-day basis, I have customized SQL*Plus to improve and enhance my user experience!

In this blog, I will show you few productivity boosting tips on how to customize SQL*Plus. Its quite easy. These tips are quite useful to me, and I believe will be useful to you as wll.

SQLPATH Variable

SQLPATH environment variable is equivalent to PATH variable. What PATH variable is to executables, SQLPATH is to SQL scripts. As a DBA, I have my own repository of scripts that I execute quite often, and I am sure you do as well. Simply add your script folders to SQLPATH. SQL*Plus searches for SQL scripts in all directories specified by SQLPATH variable. Quite useful. You don't need to specify the full path of the script. Or you don't need to change directory to the folder where your scripts are stored.

On Windows platform, you also have a choice to add SQLPATH registry variable. Use either environment variable or registry variable.

Login.sql

LOGIN.SQL is equivalent to AUTOEXEC.BAT on Windows or .profile on Unix platforms. SQL*Plus executes login.sql at the startup. At startup, SQL*Plus starts a search for login.sql in your current directory and then it moves on to directories specified by SQLPATH variable. Create login.sql and include it in a directory that is pointed to by SQLPATH variable. You can add any SQL*Plus commands that you like. Another useful customization. The following is my truncated login.sql file.

set termout off
-- formatiing
set pagesize 1000
set linesize 132
alter session set nls_date_foramt = 'MM/DD/YYYY:HH24:MI:SS' ;
-- SQL Prompt
column new_dbid new_value old_dbid noprint
column usr new_value ousr noprint
select upper (substr (global_name, 1, (instr (global_name, '.') -1))) new_dbid
from global_name ;
select user usr from dual ;
set sqlprompt '&ousr &old_dbid> '
column new_dbid print
column usr print
-- Automatic Spool
column tstamp new_value otstamp noprint
select to_char(sysdate,'MMDDYYYYHHMISS') tstamp from dual ;
spool 'E:\MyDoc\Spool\&ousr&otstamp'
column tstamp print
set termout on

Please be careful if you have scheduled unattended batch jobs using SQL*Plus. If database is not up and running, login.sql script will prompt for values for Username and Global Name variables used in the above script, and wait forever.

SQL Prompt

Another useful customization is to customize the SQL*Plus prompt. Login.sql displayed above sets up SQL prompt that comprises of Username and a Database Name.

Automatic Spool File

This customization, included as part of above mentioned login.sql, has been quite useful to me on number of occasions. It automatically creates a spool file for every SQL*Plus session at startup. Your SQL*Plus session history is saved in the spool files that you can reference anytime. Quite useful when you need to reconstruct what happened during that late night session!

Spool filename comprises of username and timestamp. Spool file is created in the folder specified as part of spool command in the above mentioned SQL*Plus.

Update Start in Property on MS Windows

Update Start-in property on Windows so all your spool files will be saved into this folder.



If you have recently installed Oracle11g R2 and are getting the following errors, then you need to update Start in SQL*Plus properties.

JMEHTA> ed
SP2-0110: Cannot create save file "afiedt.buf"


 
JMEHTA> save temp.lst
SP2-0110: Cannot create save file "temp.lst"

Saturday, November 20, 2010

Flashback is not just for humans - Oracle Flashback Features

One of the most critical responsibilities of an Oracle DBA is to protect data against disasters. That includes maintaining data integrity of the database. FIPS Publication 199 “Standards for Security Categorization of Federal Information and Information Systems” defines loss of integrity as unauthorized modification and/or destruction of information. Loss of integrity may be a result of hardware failures, malicious attacks or simply unintentional accidents.
A recent study that cites human errors as the leading cause of loss of data integrity is no surprise. Human errors and accidental mistakes cause more system downtime than all other events combined. We heard the phrase “We are human” quite often. You can’t blame anybody. Accidents do happen. Mistakes do take place. An UPDATE without a WHERE clause runs. DELETE statement may have a bug. Application Code misbehaves. A support staff simply fires the wrong statement in the wrong database. Remember running a script in production which was meant for development database. Ouch! Whatever the cause maybe, an Oracle DBAs are called upon to fix data integrity issues.
It’s Oracle DBA’s responsibility to plan and implement mechanisms to recover from such accidents and disasters. A well-planned and tested backup strategy forms the primary mechanism to protect data. Oracle Flashback is one more weapon in Oracle DBA’s arsenal against such disasters. Oracle Flashback may come to your rescue in some specific scenarios. Oracle Flashback Features may have restrictions, constraints and limitations, but it’s Oracle DBA’s job to maximize the benefits that may be accrued from Oracle Flashback features.
With Oracle Flashback, you can view data as of previous point-in-time, get data update history along with metadata, recover tables to a previous point-in-time, undo transactions, etc.
From an Oracle DBA perspective, Oracle Flashback features are based on three different components:
  • Flashback Features that are based on Undo Segments – Flashback Query, Flashback Version Query and Flashback Transaction fall in this category. These features rely upon data stored in undo segments.
  • Flashback Features that are based on Recycle Bin – Flashback Drop feature falls in this category.
  • Flashback Features that are based on Flashback Archive Logs – Flashback Database feature falls in this category. This feature relies upon flashback logs.
In this blog, I will explain what you need to do to setup flashback features that are based on undo segments.
Init.ora Parameters
Automatic Undo Management (AUM) is a pre-requisite for Oracle Flashback. With AUM, the database manages undo segments in an undo tablespace. Beginning with Release 11g, automatic undo management is the default mode for a newly installed database.
Set Undo_management init.ora parameter to AUTO to enable Automatic Undo Management.
UNDO Tablespace
This parameter is optional. If you have only one undo tablespace in your database, then you don’t need to specify this parameter. Oracle picks up the first available undo tablespace at the database startup. If you have more than one undo tablespaces, then specify the name of the undo tablespace that you want Oracle to use. If you don’t specify undo tablespace parameter and you haven’t pre-created any undo tablespace, then Oracle would start using SYSTEM tablespace as undo tablespace.
Size of Undo Tablespace
Size of undo tablespace is an important parameter that has direct impact on read consistency and flashback. The two primary objectives of the undo segments are (a) to rollback transactions and (2) to provide read consistent view of the database to the users. In addition, undo segments are used during instance recovery to rollback uncommitted transactions. You need to size undo tablespace keeping in mind these objectives. If undo tablespace is too small, then you may get dreaded “snapshot too old” errors. If undo tablespace is too large, then you may waste space.
Proper sizing of undo tablespace is not trivial. You need to know maximum undo generation rate and timings of long running queries. In addition, you need to factor in flashback requirements. Many DBAs don’t have such statistics, and hence start with reasonable undo tablespace size, and then monitor the statistics to fine tune the size if required.
If you have enough disk space, then I would recommend creating an undo tablespace as large as reasonably possible. With larger undo tablespace, you are likely to retain undo data for longer duration, and hence flashback further into past.
Undo_retention
For NON-AUTOEXTEND undo datafiles, you don’t need to specify undo_retention. If you specify, then Oracle ignores it. The database automatically tunes undo_retention parameter for the best possible retention based on system activities and undo tablespace size.
For AUTOEXTEND undo datafiles, the database attempts to maintain undo information for the period specified by this parameter. If necessary, Oracle extends datafiles to ensure that undo information is retained for the duration specified here. Please note that Oracle doesn’t automatically shrink the datafiles when undo tablespace extends and becomes too large.
Enable Row Movement
Enable Row Movement is a pre-requisite for FLASHBACK TABLE <table> TO SCN/TIMESTAMP/RESTORE POINT statement. You must execute ALTER TABLE <table> ENABLE ROW MOVEMENT to enable row movement. This clause grants Oracle permission to move rows within the table. Row movement changes row ids of the rows. FLASHBACK TABLE <table> TO SCN/TIMESTATMP/RESTORE POINT statement deletes and then inserts rows, causing row ids to change from their original values.
Please don’t confuse Row Movement with Row Migration and Row Chaining. Row Chaining occurs when Oracle cannot fit a row in one block and the row is spread across more than one Oracle data blocks. Row migration occurs when Oracle migrates a row to a new block without changing its row id.
By default, row movement is disabled. You need to explicitly enable row movement with CREATE TABLE or ALTER TABLE clauses. Please note that Oracle10g invalidates objects when you enable row movement for a table. I don’t see any reasons why row movement should not be enabled unless
  • Your application stores ROWIDs for future queries and DML operations.
  • Your application relies upon Oracle exceptions to prevent partition key updates.
Please note that FLASHBACK TABLE <table> TO BEFORE DROP doesn’t require row movement enabled. In addition, ALTER TABLE <table> SHRINK and Partition Key Update operations require row movement to be enabled.

Thursday, October 28, 2010

Kill Two Birds with One Stone - Create Physical Standby Database in Oracle10g with Data Guard


Oracle Data Guard, included with Oracle Database Enterprise Edition, is really a cool product. Implement Oracle Data Guard and you will most likely kill two birds with one stone. Oracle Data Guard will help you achieve high availability and disaster recovery. One tool and two benefits. With Oracle Data Guard, you have a standby database that is in synch with the primary database. God forbids and you lose your primary database, you can very quickly switch to the standby database with minimal or no data loss. Your standby database is just a command away from being a primary database.

Most importantly, Oracle manages the Data Guard environment. You don't need to write any custom scripts. You don't need to setup any backup jobs. You don't need to write a job to copy archive logs. You don't need to manage archive logs and RMAN hot backups. Implementation is quite simple, and it works very well.

Based on your Recovery Point Objective(RPO) and Recovery Time Objective(RTO), you can configure Oracle Data Guard in three different modes:
  • Maximum Performance
  • Maximum Availability
  • Maximum Protection
Oracle Data Guard operates on a simple principle - ship redo stream to the standby database and apply it to standby database to keep it in synch with the primary database. A simple but very effective solution. If you have licensed Oracle Enterprise Edition and haven't implemented Data Guard, then you should seriously consider implementing one.

In this blog, I will illustrate the steps that you need to perform to implement physical standby database in maximum performance mode.

Environment

My data guard test environment is as follows:

  • Primary Database Server: HRPRDSVR
  • Oracle Version: 10gR2 10.2.0.4
  • Operating System: Windows
  • Standby Type: Physical
  • Data Guard Mode: Maximum Performance
  • Redo Apply: Real-time
  • Archive Log Mode: Enabled on primary
  • Primary DB Unique Name: HRPRD
  • Primary DB TNS Service Name: HRPRD
  • Data and Log Files Location: E:\Oradata\HRPRD
  • Standby Database Server: HRSTBYSVR
  • Standby DB Unique Name: HRPRDS
  • Standby DB TNS Service Name: HRPRDS
  • Data and Log Files Location: E:\Oradata\HRPRDS
  •  
Note that Primary and Standby Database servers have identical disk configuration.

Primary Database Configuration

Step 1 - Update init.ora parameters on primary database

There are only few init.ora parameters that govern the behavior and implementation of Data Guard based physical standby databases. In order to make it easy for you to understand data guard related parameters, I have created a table that lists data guard related parameters and their corresponding values. This approach would help you understand these parameters and allow you to compare the values between primary and standby databases. I have also added my comments to further explain or clarify these parameters.

Please use this table as a guide while setting up init.ora parameters in your environment. The parameter values listed below are representative values, and should be applicable to most implementations. Update values as appropriate to reflect environment specific characteristics.

Parameter NamePrimary DB ParametersPhysical Standby DB
Maximum Performance Mode with real-time redo apply
Comments
db_nameHRPRDHRPRDDb_name must be the same for primary and physical standby
instance_nameHRPRDHRPRD  
db_unique_nameHRPRDHRPRDSDb_unique_name must be different for primary and physical standby
service_namesHRPRDHRPRDS  
remote_login_passwordfileexclusiveexclusive  
log_archive_config'dg_config=
(HRPRD,HRPRDS)'
'dg_config=
(HRPRD,HRPRDS)'
  
log_archive_format HRPRD%r%s.%tHRPRD%r%s.%t  
log_archive_trace11  
log_archive_dest_1"location=
f:\orarch\HRPRD "
"location=
f:\orarch\HRPRD"
Replace with your Local Archive Destination
log_archive_dest_2'service=
HRPRDS
async
lgwr db_unique_name=
HRPRDS
valid_for=
(primary_role,
online_logfile)'
'service=
HRPRD
async
lgwr
db_unique_name=
HRPRD
valid_for=
(primary_role,
online_logfile)'
For maximum performance mode, make sure that you specify async value as part of this parameter.
You also need to use lgwr value to enable real-time redo apply.
log_archive_dest_state_1enableenable  
log_archive_dest_state_2deferenableStart with deferred configuration on primary. We will enable it later.
standby_file_managementautoautoLet Oracle add/resize data and log files on standby
fal_serverHRPRDSHRPRDIn the event primary assumes the role of standby.
fal_clientHRPRDHRPRDSIn the event primary assumes the role of standby.
log_file_name_convert('HRPRDS',
'HRPRD')
('HRPRD',
'HRPRDS')
Let Oracle manage logfiles on the standby node through this parameter. Specify on both nodes to manage role reversal.
db_file_name_convert'HRPRDS',
'HRPRD')
('HRPRD',
'HRPRDS')
Let Oracle manage datafiles on the standby node through this parameter.
Specify on both nodes to manage role reversal.

Step 2 - Create a password file on primary

In an Oracle Data Guard environment, the primary and standby databases authenticates via password file. Use the following command to create a password file on the primary database server.

orapwd file=PWDHRPRD.ora password=verysecretpassword (On Windows platform)
orapwd file=orapwHRPRD.ora password=verysecretpassword (On Unix platform)

The default name and location of the password file on Windows platform is %ORACLE_HOME%\DATABASE\PWD<ORACLE_SID>.ora and $ORACLE_HOME\dbs\orapw<ORACLE_SID> on Unix platform. You need to restart the instance after creating the password file.

A sidebar on the password file is in order as many Oracle DBAs are not very clear on password file concepts. When you want to startup an Oracle database instance, you need an authentication mechanism outside the database. During startup, you can't use database authentication as Oracle database instance is not up at that time.

Oracle operating system group membership or password file provide authentication outside the database. If you are a member of ORA_DBA group on Windows or dba group on Unix, then you can login "SYS AS SYSDBA" without any password and startup the instance.

But if you are not a member of these groups, then you need a password file to authenticate your "SYS AS SYSDBA" connections. In addition, if you want to startup and/or shutdown databases from remote servers on different domains, then you also need a password file to authenticate "SYS AS SYSDBA" connections. Databases participating in an Oracle Data Guard environment also need password file for authentication.

Step 3 - Enable FORCE LOGGING

Oracle Data Guard operates the standby database with the primary database redo stream. Primary database ships redo stream to the standby database. Redo stream shipment to the standby database can be synchronous or asynchronous depending on the data guard protection mode that you choose. Standby database's managed recovery process applies the redo stream to keep both the databases in synch. Any issues or interruption with the redo stream would put your standby database in jeopardy. For instance, NOLOGGING operations on primary database will definitely jeopardize your standby database. To prevent NOLOGGING operations, you must enable FORCE LOGGING on the primary database.

SQL> SHUTDOWN IMMEDIATE ;
SQL> STARTUP MOUNT ;
SQL> ALTER DATABASE FORCE LOGGING
SQL> ALTER DATABSE OPEN;

Run the following query just to make sure:

SELECT FORCE_LOGGING
FROM V$DATABASE ;

You can also query V$DATAFILE to see if there are any datafiles with NOLOGGED operations, as shown below.

SELECT FILE#, FIRST_NONLOGGED_SCN, FIRST_NONLOGGED_TIME
FROM V$DATAFILE ;

Step 4 - Last Night's RMAN Backup on primary

You need to use your last RMAN backup to seed the standby database. If you don't have RMAN backups, then you need to take a fresh RMAN backup. But I am sure you do perform RMAN backups. If you don't need to take RMAN backups, then I don't think you would be needing a standby database!

If this is a test implementation and you don't have RMAN backups, then take RMAN backup through BACKUP DATABASE command.

BACKUP FORMAT 'C:\OraBack\FullBackup%U.rman' DATABASE PLUS ARCHIVELOG;

Your database must be in ARCHIVELOG mode. If not, enable ARCHIVELOG mode.

Step 5 - Create Standby Control File on primary

A physical standby database needs a control file that is created with "FOR STANDBY" additional clause. You can use RMAN or SQL*Plus to create a standby control file.

SQL> alter database create standby controlfile as 'c:\oraback\stbyctr.ctl';


RMAN> BACKUP FORMAT 'c:\oraback\stbyctr.ctl' CURRENT CONTROLFILE FOR STANDBY;

Step 6 - Add TNSNAMES.ORA Service on primary server

Add the secondary database's TNSNAMES service entry to the primary server's TNSNAMES.ORA file:

HRPRDS =
  (DESCRIPTION =
     (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = HRSTBYSVR)(PORT = 1521))
     )
    (CONNECT_DATA =
        (SERVICE_NAME = HRPRDS)
    )

)

Step 7 - Copy Backups

Copy the following backups to the standby server on the same location as primary database:

  • RMAN Hot Backup with database and archive logs
  • Standby Control File  
You may need to copy additional archive log backups that were generated since you took the RMAN backup and executed RMAN DUPLICATE command to create a standby database.

Standby Database Configuration

Step 1 - Add TNSNAMES.ORA Service on Secondary

Add the primary database's TNSNAMES service entry on the standby server to TNSNAMES.ORA file :

HRPRD =
  (DESCRIPTION =
     (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = HRPRDSVR)(PORT = 1521))
     )
     (CONNECT_DATA =
        (SERVICE_NAME = HRPRD)
     )

  )

Test connectivity to the primary database server as follows:

C:\> SQLPLUS sys/verysecretpassword@HRPRD as sysdba

Step 2 - Configure Listener.ora on standby server

Update listener.ora as appropriate, and reload or restart listener.

(SID_DESC =
    (ORACLE_HOME = <ORACLE_HOME>)
    (SID_NAME = HRPRDS)
    (SDU=8192)
)

Step 3 - Create init.ora file on standby server

Create init.ora file on the secondary server. Refer to the table above for Oracle Data Guard related parameters that you need to configure on the standby server.

If you are on Windows platform, then you need to create the Windows service as follows:

C:>oradim -new -sid HRPRDS -startmode auto -pfile initHRPRDS.ora -syspwd verysecretpassword
Note that the sys password on standby database must be the same as primary database.

Now start the database instance on standby server and logout from your SQL*Plus session.


Step 4 - Create password file on standby

If you are on Windos platform, then you don't need to create a password file. Otherwise, create the password file on the secondary server as follows. Note that the password on standby database must be the same as primary database.

orapwd file=PWDHRPRDS.ora password=verysecretpassword (On Windows platform)

Use SQL*Plus to test connectivity from the primary database server to the standby server.

C:\> SQLPLUS sys/verysecretpassword@HRPRDS as sysdbaDon't proceed to the next step if you are having connectivity issues. Verify passwords and existance of password files

Step 5 - Create Standby Database

RAMN is the tool to create the standby database as follows:

C:\> set ORACLE_SID=HRPRD

C:\>rman nocatalog

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Oct 13 17:04:40 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

RMAN> connect auxiliary /

connected to auxiliary database: HRPRD (not mounted)

RMAN> connect target sys@HRPRD

target database Password:
connected to target database: HRPRD (DBID=358909779)
using target database control file instead of recovery catalog

RMAN> duplicate target database for standby dorecover ;

-----
-----
-----

media recovery complete, elapsed time: 00:07:50
Finished recover at 13-OCT-10
Finished Duplicate Db at 13-OCT-10

RMAN>

Good News! Physical Standby Database has been created.

Common Errors

If you haven't specified db_name = HRPRD and db_unique_name=HRPRDS, then you will get the following error:

RMAN-03002: failure of Duplicate Db command
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel
RMAN-11003: failure during parse/execution of SQL statement: alter database mount standby database
ORA-01103: database name 'HRPRD' in control file is not 'HRPRDS'


If you haven't copied the primary database backup to the standby database server in the same location as primary database, then you would get the following errro.

executing command: SET until clause
Starting restore at 16-DEC-09
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece .......
ORA-19870: error reading backup piece .........
ORA-19505: failed to identify file ........
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified. failover to previous backup

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at ........
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found - aborting restore
RMAN-06024: no backup or copy of the control file found to restore


If you haven't copied all the archive logs that were generated since you took the RMAN backup, you would get the following error:

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: ...........

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/16/2010 11:15:38
RMAN-03015: error occurred in stored script Memory Script
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 10 lowscn 113033639 found to restore
RMAN-06025: no backup of log thread 1 seq 9 lowscn 113029915 found to restore
RMAN-06025: no backup of log thread 1 seq 8 lowscn 113014889 found to restore


Note that the standby database must be in NOMOUNT state when you initiate the RMAN duplicate command.

RMAN> duplicate target database for standby dorecover ;
Starting Duplicate Db at 16-DEC-10
using channel ORA_AUX_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/16/2009 11:26:19
RMAN-05500: the auxiliary database must be not mounted when issuing a DUPLICATE
command

RMAN> quit

Step 6 - Add Standby Redo Log Files on primary and standby databases

You must configure standby redo logs on standby database to activate real-time redo transport. Standby redo log files must be of the same size as primary database redo log files. You need to create at least the same number of standby redo log files as primary database.

ALTER DATABASE
ADD STANDBY LOGFILE 'E:\oradata\HRPRDS\StbyRedo1.log'
SIZE 100M;

Also perform the same task on primary database in the event primary assumes the role of standby database.

Step 7 - Activate standby database managed recovery on Standby

SQL> Alter database recover managed standby database using current logfile disconnect ;

Step 8 - Enable Physical Standby on Primary


Enable log_archive_dest_state_2 parameter and then perform a log switch on the primary database server:

SQL> ALTER SYSTEM SET log_archive_dest_state_2 = enable ;

SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;

And you should see the latest archive log on the standby server! Oracle Data Guard Physical Standby Database is running!!

If you don't see what you were expecting, then review alert logs and trace files. You may also query V$ARCHIVE_LOG table to degug this issue further.

Test It!

You can also run a simple test case to demonstrate that redo is being shipped in real-time and application data is available on standby database.

Run the following on primary database.

SQL> drop table test purge ;

Table dropped.

SQL> create table test
2 ( test date ) ;

 Table created.

SQL> insert into test values(sysdate) ;

1 row created.

SQL> /

1 row created.

SQL> commit ;

Commit complete.

SQL> alter session set nls_date_format='MM/DD/YYYY:HH24:MI:SS' ;

Session altered.

SQL> SELECT * FROM TEST ;

TEST
-------------------
10/17/2010:00:01:57
10/17/2010:00:01:58

Run the following on standby database server:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL ;

Database altered.

SQL> ALTER DATABASE OPEN READ ONLY ;

Database altered.

SQL> SELECT * FROM JMEHTA.TEST ;

TEST
-------------------
10/17/2010:00:01:57
10/17/2010:00:01:58

SQL> ALTER DATABASE CLOSE ;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
USING CURRENT LOGFILE DISCONNECT ;

Database altered.