Advanced Oracle Data Guard Administration

Oracle Data Guard manual describes operations such as switchover, failover, backup/recovery quite well. In this blog, I will share the following advanced data guard operations that are either not described in the manual or need some more information:

• Leverage Flash Recovery Area for archive log management
• Rename Data File on the primary database server
• Rename Data File on the standby database server
• Manage TEMP files on the standby database server

My test environment is on Windows platform with Oracle 10g R2 10.2.0.4. Physical standby database is operating in maximum performance mode.

1. Leverage Flash Recovery Area for archive log management

Primary Database in Oracle Data Guard continuously ships redo vectors to the physical standby database to keep it in synch with the primary. During archive log switch operation, Oracle creates a new archive log file on the standby server. Use of Flash recovery area is definitely advantageous as you don't need to manage archive logs on the physical standby. We use flash recovery area to store and manage archive logs on physical standby. With Oracle flash recovery area, Oracle delete archive logs after applying them to standby database. Works pretty well. And simple setup as shown below:

SQL> connect / as sysdba

Connected.

SQL> alter system set DB_RECOVERY_FILE_DEST='e:\oraflash' ;

alter system set DB_RECOVERY_FILE_DEST='e:\oraflash'

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-19802: cannot use DB_RECOVERY_FILE_DEST without DB_RECOVERY_FILE_DEST_SIZE

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=5g ;

System altered.

SQL> alter system set DB_RECOVERY_FILE_DEST='e:\oraflash' ;

System altered.

SQL> alter system set  LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';

System altered.

Now configure archive log deletion policy.

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

2. Rename Data files on Primary DB

Oracle documentation explains the procedure to rename a data file on the primary database participating in a data guard managed physical standby configuration. Per Oracle documentation, you rename the data file on primary server and then rename it on the standby database server as well.

A test, described below, shows that you don't need to repeat the rename step on physical standby server. 

Step - 1

Check the name of the data file on physical standby database:

SQL> select name, bytes from v$datafile where file# = 4 ;

NAME                                                    BYTES

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

E:\ORADATA\DGS\USERS11.ORA                            52428800

Step 2

Rename the data file on the primary database server

SQL> select name, bytes from v$datafile where file# = 4 ;

NAME                                                    BYTES

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

E:\ORADATA\DGP\USERS11.ORA                             52428800

1 rows selected.

SQL> ALTER TABLESPACE USERS OFFLINE ;

Tablespace altered.

REM Rename USERS11.ORA to USERS21.ora on the primary database server at the OS level.

SQL> ALTER TABLESPACE USERS

  2  RENAME DATAFILE 'E:\ORADATA\DGP\USERS11.ORA'

  3  TO  'E:\ORADATA\DGP\USERS21.ORA' ;

Tablespace altered.

SQL> ALTER TABLESPACE USERS ONLINE ;

Tablespace altered.

REM Resize the file on primary to ensure propagation of resize operation

SQL> ALTER DATABASE DATAFILE 'E:\ORADATA\DGP\USERS21.ORA' RESIZE 250M ;

Database altered.

SQL> select name, bytes from v$datafile where file# = 4 ;

NAME                                                    BYTES

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

E:\ORADATA\DGP\USERS21.ORA                          262144000

1 rows selected.

Step - 3

Verify the name of the data file on physical standby database after renaming it on the primary

SQL> select name, bytes from v$datafile where file# = 4 ;

NAME                                                    BYTES

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

E:\ORADATA\DGS\USERS11.ORA                          262144000

1 rows selected.

3. Rename Data File on Physical Standby

We recently ran out of disk space on the physical standby database server. In ideal world, you would like to have your physical standby database server mirror the primary database server in terms of processors, memory and disk space, but that wasn't the case in our environment. On primary database server, Oracle data files were on E drive which was 500GB in size. We also had E drive on the physical standby database server, but it was smaller in size.

E drive on the physical standby database server was completely full, so we wanted to move few files from E drive to F drive on the physical standby database server to make some space on the drive.

Like a good "DBA", before renaming a data file on standby, we performed a test in a test environment as shown below:

Step 1

Rename Data File on Physical Standby Database Server

SQL> alter database recover managed standby database cancel ;

Database altered.

After cancelling managed recovery on the standby server, we attempted to rename the database at the OS level, but it failed with "File locked" error message. So let's shutdown the database to release locks on data files.

SQL> shutdown immediate ;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

After shutting down the database, we successfully renamed the file at the OS level. A USERS01.ORA file was renamed to USERS31.ORA at the OS level.

SQL> STARTUP MOUNT ;

ORACLE instance started.

Total System Global Area  402653184 bytes

Fixed Size                  1296988 bytes

Variable Size             117441956 bytes

Database Buffers          281018368 bytes

Redo Buffers                2895872 bytes

Database mounted.

SQL> ALTER DATABASE

  2  RENAME FILE 'E:\ORADATA\DGS\USERS01.ORA'

  3* TO  'E:\ORADATA\DGS\USERS31.ORA' ;

ALTER DATABASE

*

ERROR at line 1:

ORA-01511: error in renaming log/data files

ORA-01275: Operation RENAME is not allowed if standby file management is automatic.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

System altered.

SQL>

SQL> ALTER DATABASE

  2  RENAME FILE 'E:\ORADATA\DGS\USERS01.ORA'

  3  TO  'E:\ORADATA\DGS\USERS31.ORA' ;

Database altered.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

Step 2

Resize the corresponding file on the primary database server. The purpose of this step is to verify that file resize operation on this file is replicated to the standby database server.

SQL> ALTER DATABASE DATAFILE 'E:\ORADATA\DGP\USERS01.ORA' RESIZE 300M ;

Database altered.

SQL> ALTER SYSTEM SWITCH LOGFILE ;

System altered.

Step 3

Verify the file size on the physical standby database server. In addition, open the physical standby database in read only mode to verify.

SQL> select name, bytes from v$datafile where file# = 4 ;

NAME                                                    BYTES

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

E:\ORADATA\DGS\USERS31.ORA                          314572800

1 rows selected.

SQL> alter database recover managed standby database cancel  ;

Database altered.

SQL> alter database open ;

Database altered.

To summarize, renaming the data file on physical standby works.

4. Rename TEMP files on Standby

In data guard managed physical standby environment, Oracle manages TEMP files differently. During the initial standby database creation, RMAN creates TEMP files on the standby database server. From this point onwards, TEMP file management is on standby independent of the primary database. You should be able to perform the following on standby server independently:

• Resize TEMP files on standby
• Move TEMP files on standby
• Create additional TEMP files on standby

If you are using the physical standby database for reporting purposes and if you need additional TEMP space on standby, you should be able to perform these tasks independent of the primary database

Learn It Yourself: Oracle Flashback Features

The script below is the one I had used to learn more about Oracle Flashback features and then to teach development teams how to use flashabck features. I have embeded some comments within the script to explain few points. Just copy and run the script to get better understanding of Oracle Flashback features. Enjoy!

REM *****************************************************************
REM Name: OraFlash.sql
REM Author: Jay Mehta
REM Description: A script to demonstrate Oracle flashback features
REM *****************************************************************
REM
REM **** drop Scripts
DROP TABLE TEST PURGE;
DROP SEQUENCE TEST_SEQ;
DROP RESTORE POINT before_insert ;
DROP RESTORE POINT before_upper_update ;
DROP RESTORE POINT before_lower_update ;
DROP RESTORE POINT before_delete ;
REM *****************************************************************
REM
REM *****************************************************************
ALTER SESSION SET NLS_DATE_FORMAT='MM/DD/YYYY:HH24:MI:SS';
SET FEEDBACK ON
SET TERM ON
SET ECHO ON
REM
REM **** Create a test table
REM
REM This table will be used in test cases
CREATE TABLE TEST
(   ID NUMBER(9),
    NAME VARCHAR2(30),
    TIME DATE ) ;
REM
REM **** You must enable row movement to perform FLASHBACK .. TO TIMESTAMP/SCN/RESTORE POINT
REM **** FLASHBACK ..... BEFORE DROP doesn't require Row Movement Enabled.
REN
ALTER TABLE TEST ENABLE ROW MOVEMENT;
REM To show that index and trigger are also flashed back by FLASHBACK ..... BEFORE DROP   
CREATE UNIQUE INDEX TEST_PK
ON TEST (ID);
ALTER TABLE TEST
ADD CONSTRAINT TEST_PK
PRIMARY KEY(ID);
CREATE SEQUENCE TEST_SEQ;
REM **** Create a trigger on Test Table
CREATE OR REPLACE TRIGGER TEST_BRI_TRG
BEFORE INSERT
ON TEST
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
  IF :new.id IS NULL THEN
     SELECT TEST_SEQ.NEXTVAL
     INTO :new.id
     FROM dual;
     IF SQL%notfound THEN
       raise_application_error(-20031,
           'The sequence generator IS NOT wo;king');
     END  IF;
  END IF;
END;
/

REM Create Restore Point
REM Get System Change Number and Timestamp so we can use it to flashback to this SCN
REM You can, of course, use SCN and TIMESTAMP to falshback to, but
REM Restore POint offers a better way to flashback
CREATE RESTORE POINT before_insert ;
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER SCN FROM DUAL;
SELECT SYSDATE FROM DUAL;
REM **** Insert Data
INSERT INTO TEST(ID,NAME,TIME)
VALUES(1,'Jay',SYSDATE);
INSERT INTO TEST(ID,NAME,TIME)
VALUES(2,'Jim',SYSDATE);
INSERT INTO TEST(ID,NAME,TIME)
VALUES(3,'John',SYSDATE);
COMMIT ;
SELECT * FROM TEST ;
REM Create Restore Point
REM Get System Change Number and Timestamp so we can use it to flashback to this SCN
REM You can, of course, use SCN and TIMESTAMP to falshback to, but
REM Restore POint offers a better way to flashback
CREATE RESTORE POINT before_upper_update ;
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER SCN FROM DUAL;
SELECT SYSDATE FROM DUAL;
UPDATE TEST
SET NAME = UPPER(NAME);
COMMIT;
SELECT * FROM TEST ;
REM
CREATE RESTORE POINT before_lower_update ;
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER SCN FROM DUAL;
SELECT SYSDATE FROM DUAL;

Prompt ******* Update to Lower Case
UPDATE TEST
SET NAME = LOWER(NAME);
COMMIT;
SELECT * FROM TEST ;

CREATE RESTORE POINT before_delete ;
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER SCN FROM DUAL;
SELECT SYSDATE FROM DUAL;
Prompt ******* Delete Data
DELETE FROM TEST ;
COMMIT;
Prompt ***** Data in Test Table at this moment
SELECT *
FROM TEST ;
Prompt ************  Update Timestamp as appropriate
REM Flashback Querty Examples with TIMESTAMP clause
REM ***** Data in Test Table 2 seconds ago
SELECT *
FROM TEST
AS OF TIMESTAMP ( SYSDATE - 2/84600) ;
Prompt ***** Data in Test Table 3 seconds ago
SELECT *
FROM TEST
AS OF TIMESTAMP ( SYSDATE - 3/84600) ;
Prompt ***** Data in Test Table 4 seconds ago
SELECT *
FROM TEST
AS OF TIMESTAMP ( SYSDATE - 4/84600) ;
Prompt ***** Data in Test Table 5 seconds ago
SELECT *
FROM TEST
AS OF TIMESTAMP ( SYSDATE - 5/84600) ;
Prompt ***** Data in Test Table 6 seconds ago
SELECT *
FROM TEST
AS OF TIMESTAMP ( SYSDATE - 6/84600) ;
Prompt ***** Data in Test Table 7 seconds ago
SELECT *
FROM TEST
AS OF TIMESTAMP ( SYSDATE - 7/84600) ;
Prompt ***** Data in Test Table 1 hour ago
SELECT *
FROM TEST
AS OF TIMESTAMP ( SYSDATE - 1/24) ;
SELECT *
FROM V$RESTORE_POINT ;
REM
REM Flashback Row History - Retrieve Version History
REM Update Timestamp appropeiately
REM
SELECT VERSIONS_STARTTIME, VERSIONS_ENDTIME,
       DECODE( VERSIONS_OPERATION
         , 'I', 'Insert'
         , 'U', 'Update'
         , 'D', 'Delete'
              , 'Original'
             ) "Operation" ,
             ID, NAME, TIME,
       VERSIONS_STARTSCN, VERSIONS_ENDSCN, VERSIONS_XID
FROM TEST VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
ORDER BY VERSIONS_XID;
REM Update Timestamp appropeiately
SELECT VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_OPERATION, ID, NAME, TIME,
       VERSIONS_STARTSCN, VERSIONS_ENDSCN, VERSIONS_XID
FROM TEST VERSIONS BETWEEN TIMESTAMP TO_DATE('09/08/2010:10:30:20','MM/DD/YYYY:HH24:MI:SS')  AND SYSTIMESTAMP
ORDER BY ID, VERSIONS_STARTSCN ;
REM Update Timestamp appropeiately
SELECT VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_OPERATION, ID, NAME, TIME,
       VERSIONS_STARTSCN, VERSIONS_ENDSCN, VERSIONS_XID
FROM TEST VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP - 1/1440 AND SYSTIMESTAMP
ORDER BY ID, VERSIONS_STARTSCN ;
REM Update Timestamp appropeiately
SELECT VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_OPERATION, ID, NAME, TIME,
       VERSIONS_STARTSCN, VERSIONS_ENDSCN, VERSIONS_XID
FROM TEST
VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP - 1 AND SYSTIMESTAMP
ORDER BY ID, VERSIONS_STARTSCN ;

REM ***** Flashback Row History - Undo Mistakes
REM
-- Get the UNDO Statement to undelete row
SELECT start_timestamp, commit_timestamp, operation, undo_sql
FROM FLASHBACK_TRANSACTION_QUERY
WHERE xid in ( SELECT  hextoraw(VERSIONS_XID)
        FROM TEST VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE )
order by start_scn;

SELECT *
FROM FLASHBACK_TRANSACTION_QUERY
WHERE
TABLE_NAME='TEST'
ORDER BY START_SCN ;

SELECT *
FROM FLASHBACK_TRANSACTION_QUERY
WHERE
XID = '0009002F0001CB51'
-- XID = HEXTORAW('0009002F0001CB51')
ORDER BY START_SCN ;

REM Flashback Table - Undo Mistakes
REM

SELECT * FROM TEST ;
REM Flashback Before Delete
REM User SCN, TIMESTAMP or RESTORE POINT
REM
REM FLASHBACK TABLE TEST TO TIMESTAMP ( TO_TIMESTAMP('09/15/2010:14:37:08', 'MM/DD/YYYY:HH24:MI:SS' )) ;
REM FLASHBACK TABLE TEST TO SCN 2342345234 ;
FLASHBACK TABLE TEST TO RESTORE POINT before_delete;
SELECT * FROM TEST ;

-- Flashback Before Last Update
REM FLASHBACK TABLE TEST TO TIMESTAMP ( TO_TIMESTAMP('09/15/2010:14:37:05', 'MM/DD/YYYY:HH24:MI:SS' )) ;
FLASHBACK TABLE TEST TO RESTORE POINT before_upper_update;
SELECT * FROM TEST ;

-- Flashback Before First Update
REM FLASHBACK TABLE TEST TO TIMESTAMP ( TO_TIMESTAMP('09/09/2010:07:00:00', 'MM/DD/YYYY:HH24:MI:SS' )) ;
FLASHBACK TABLE TEST TO RESTORE POINT before_lower_update;
SELECT * FROM TEST ;
REM
REM Flashback Drop - Recover Dropped Tables
REM
DROP TABLE TEST;
SELECT TABLE_NAME FROM USER_TABLES ;
SELECT * FROM RECYCLEBIN ;
FLASHBACK TABLE TEST TO BEFORE DROP  RENAME TO TEST1 ;
SELECT * FROM RECYCLEBIN ;
SELECT TABLE_NAME FROM USER_TABLES ORDER BY 1;
SELECT * FROM TEST;

-- permanent drop
DROP TABLE TEST PURGE ;
SELECT TABLE_NAME FROM USER_TABLES ;
SELECT * FROM RECYCLEBIN ;

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!

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

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"

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.