RMAN-06059: expected archived log not found, lost of archived log compromises recoverability

Sometimes our RMAN archive log backups hit with following RMAN-06059 error.

sent command to channel: t1

Starting backup at 06-dec-2011 11:04:32

current log archived

released channel: t1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of backup command at 12/06/2011 11:05:30

RMAN-06059: expected archived log not found, lost of archived log compromises recoverability

ORA-19625: error identifying file /arch/ppos/arch/ppos_712666108_1_20221.arc

ORA-17503: ksfdopn:4 Failed to open file /arch/ppos/arch/ppos_712666108_1_20221.arc

ORA-17500: ODM err:File does not exist

RMAN>

Here is what we could do to resolve this issue.

export ORACLE_SID=ppos1

prdposx501:oracle:ppos1:/orasw/static/oradba/bin/rman: rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Dec 6 11:36:11 2011

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

connected to target database: PPOS (DBID=XXXXX31740)

RMAN> connect rcvcat rman_username/password@PRRMAN

connected to recovery catalog database

RMAN> crosscheck archivelog all;

How to send email with html formatting from shell script

I heard questions from more than one person in my team on how to send emails with html contents.

I have written a small test program and it is working fine in HP-UX systems. May be with little or no modifications it works in other Unix variants. The trick is to use sendmail program and not mailx. Also you need to add necessary MIME information in the header.

Here is the script if you are interested

You might have noticed that you could masquerade. (I mean if I wish I can send email with sender ‘s name with our president name)

#!/usr/bin/ksh

# Script to send html email

# Written by Madhu

FROM="madhu@tarikida.com"

TO="madhu@tarikida.com"

SUBJECT="This email has html contents"

CONTENTS="/home/madhu/scripts/mail_text.html"

HEADER="From: ${FROM}                             \

\nTo: ${TO}                                     \

\nSubject: \"$SUBJECT\"                              \

\nContent-Type: text/html; charset=us-ascii   \

\nContent-Transfer-Encoding: 7bit                  \

\nMIME-Version: 1.0"

#echo $HEADER;

( echo $HEADER; cat $CONTENTS ) | /usr/sbin/sendmail -t

Chitti aa gayi (Letter Came)

Finally much awaited letter came in the mail and set rest to the rumors floating around coffee rooms and drive ways in my office. It gave me realistic picture of where I stand now and how valuable is me as an Oracle DBA for a company.

At least it is good in one aspect. My dad worked for just one employer for more than 38 years till his retirement. He was loyal to the employer and did not digest when I use to tell him that I don’t feel what I am doing now and I want to change company. Things have changed lot in last 20 years and now no one work for any employer for an average of more than 5 years. They learn new things, gain experience and move on for a better career. What I noticed is that only those people succeeds in their professional life. Others just stay in the same place for years, do same thing over and over and don’t really learn any new tools  and don’t have the mindset at least to try something new. We could argue that it is because of Inertia (remember old Newton) or H1 and Green Card dream.  What I am saying is that it is not late to learn new things and try to be at the cutting edge. Spend at least 1 hour every day on learning 11gR2 new features and we won’t regret when it is time to look for another job. No one want to hire a regular DBA who knows only basic 10g and no programming or project/people management experience.

“Uthishtatha jagratha praapyavaraan nibodhata!!” - meaning  “Arise , awake and stop not till the Goal is Reached.” - Mundakoupanishad

Upgrading stats table after database is migrated to Oracle 11gR2

Sometimes we keep statistics for some application tables in a stats table for importing it later. You might get following error if your database has been upgraded to Oracle 11gR2 and you try to perform import of stats table

--  Import stats for HELPER_TABLE table from HELPER_TABLE_STATS table

exec dbms_stats.import_table_stats(ownname =>'ETRADM',tabname =>'HELPER_TABLE',stattab =>'HELPER_TABLE_STATS',cascade =>true,statown =>'ETRADM');

I receieved following error.

SQL> exec dbms_stats.import_table_stats(ownname =>'ETRADM',tabname =>'HELPER_TABLE',stattab =>'HELPER_TABLE_STATS',cascade =>true,statown =>'ETRADM');

BEGIN dbms_stats.import_table_stats(ownname =>'ETRADM',tabname =>'HELPER_TABLE',stattab =>'HELPER_TABLE_STATS',cascade =>true,statown =>'ETRADM'); END;

*

ERROR at line 1:

ORA-20002: Version of statistics table ETRADM.HELPER_TABLE_STATS is too old.  Please try upgrading it with dbms_stats.upgrade_stat_table

ORA-06512: at "SYS.DBMS_STATS", line 11013

ORA-06512: at "SYS.DBMS_STATS", line 12396

ORA-06512: at line 1

Here is the fix. We need to upgrade stats table to Oracle 11gR2 format

SQL> exec dbms_stats.upgrade_stat_table('ETRADM','HELPER_TABLE_STATS');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.import_table_stats(ownname =>'ETRADM',tabname =>'HELPER_TABLE',stattab =>'HELPER_TABLE_STATS',cascade =>true,statown =>'ETRADM');

PL/SQL procedure successfully completed.

Don't ignore NLS and NLS_LANG

I would like to share an interesting finding about effect of NLS and NLS_LANG on performance which I learned recently. I don’t know if Database administrators and developers really pay attention to some of the environmental variables like NLS_LANG and NLS. NLS_LANG variable tell Oracle what character set the client is using so that Oracle server could do conversion while sending and receiving data from Oracle client tools. This is different from any server parameter setting. I believe you could have a different NLS_LANG setting on client side when compared with server end and it should not affect performance (Some one needs to validate). NLS environmental variable is used for native language support. Setting it in the client helps the terminal to display correct character set which you could read.

We could query NLS_DATABASE_PARAMETERS settings in database to find database settings.

select * from NLS_DATABASE_PARAMETERS where parameter in ('NLS_LANGUAGE','NLS_CHARACTERSET');

PARAMETER                 VALUE

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

NLS_LANGUAGE              AMERICAN

NLS_CHARACTERSET          AL32UTF8

And here are the values I had for client

[oracle@tstxxc03:/home/oracle] $ echo $LANG

en_US.ISO8859-1

[oracle@tstxxc03:/home/oracle] $ echo $NLS_LANG

AMERICAN_AMERICA.WE8ISO8859P1

The rule is that you should have matching values for NLS_LANG and LANG variables. OS host needs to spend considerable amount of time converting characters from one character set to another character set and it could add significant run times to your batch script and CPU load to the host.

I tested this behavior in QJDA1 instance which is used by JDA application and used following script

-- This script is used to check CPU utilization of Oracle processes when run from multiple windows

-- Used by Madhu K Nair 11/05/2010

-- Checking STSC.DFUTOSKU table

set heading off

set linesize 10000

set recsep off

set echo off

set termout off

set trimspool on

set serveroutput off

set feedback off

set pages 0

set time on timing on

spool /backup/qjda1/exp/dfutosku1.lst;

select 'DMDUNIT'||'||'||'DMDGROUP'||'||'||'DFULOC'||'||'||'ITEM'||'||'||'SKULOC'||'||'||'ALLOCFACTOR'||'||'||'CONVFACTOR'||'||'|| 'EFF'||'||'||'DISC'||'||'||'FCSTTYPE'||'||'||'HISTTYPE'||'||'||'MODEL'||'||'||'SUPERSEDESW'||'||'||'FF_TRIGGER_CONTROL' from dual;

select /*+ PARALLEL (d, 4) */ DMDUNIT||'||'||DMDGROUP||'||'||DFULOC||'||'||ITEM||'||'||SKULOC||'||'||ALLOCFACTOR ||'||'||CONVFACTOR||'||'|| EFF||'||'||DISC||'||'||FCSTTYPE||'||'||HISTTYPE||'||'||MODEL||'||'||SUPERSEDESW|| '||'||FF_TRIGGER_CONTROL from stsc.dfutosku d;

spool off;

Above script took 53:24 seconds to finish with mismatched setting and only 7:24 seconds with correct NLS settings.

## Below 2 lines are the original one which was causing high CPU and long run times

#export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

#export LANG=en_US.ISO8859-1

## Below 2 lines are the  modified one which should improve run times

export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

export LANG=en_US.ISO8859-1

Let me thank Israel Anandan again for finding out this mismatch and advising me ways to fix it. I urge all of you to verify these environmental variables in your systems for any potential issues.

Cloning Oracle Binary in Oracle 11gR2

I get several questions even from my collegues about installing Oracle 11gR2 binaries for a stand alone database instance. So I created a pdf document. Cloning software will help you to install Oracle binary with all customized patches/PSU etc in less than 15 minutes. No need to work on X-Window and navigate through several Oracle installation screens and make costly configuration mistakes. I used IBM AIX for testing these steps. But will work in HP-UX and Solaris if source and target are same Operating system. Please note that you cannot clone Oracle binary from one OS to another one. You also need to make sure that you followed Oracle’s prerequisites for kernel settings, memory, swap, file system and TEMP space needed. You can download entire document from this link.

Steps to create auto login of ssh without typing passwords for oracle user in AIX

Ability for ORACLE user to login with ssh to peer RAC instance is a prerequisite for RAC installation. Verify that ssh daemon is configured on node1 and node2 before proceeding with steps listed here. Steps documented here was tested on 2 node RAC cluster on AIX 6.1 with OpenSSH

Perform steps 1, 2 and 3 on node1 and node2

1) cd /home/oracle/.ssh

2) Generate public keys for the oracle user as follows

$ ssh-keygen -t rsa

This will generate a private (id_rsa) and public file (id_rsa.pub)

3) Copy id_rsa.pub to nodename_username.pub format

Example: cp id_rsa.pub node1_oracle.pub

4) Transfer this file to the node2 to which you need to configure auto login with ssh.

So in my case, I transferred it to tstndc02 using sftp

5) Now on node2, execute following steps,

$ cd /home/oracle/.ssh

$ cat node1_oracle.pub node2_oracle.pub >>authorized_keys

6) As we want to perform auto login from both nodes, we need to share authorized_keys which we created in step 5 to node1.

$ scp authorized_keys oracle@node1:/home/oracle/.ssh/

7) Now you should be able to login from node1 to node2 with ssh without password. To complete this process, please test following steps on node1 and node2 and accept the security keys if it prompts (It will ask you only first time)

$ ssh oracle@localhost

$ ssh oracle@node1

$ ssh oracle@node1.domainname.com

$ ssh oracle@node2

$ ssh oracle@node2.domainname.com

Try ssh to localhost, node1, node2, package name etc to make sure all combinations are working without typing password. If you get PNRG is not seeded error, Contact Unix SA and ask him to change permission for /dev/random and /dev/urandom

You could download same document in pdf  from Google Docs. Here is the link

DBA at Snake Mobile

Please enjoy a blog about an Oracle DBA’s typical day and how he reacts to the issues. Characters and situation in this story are fictitious. If you see any resemblance it is just coincidental and is used for educational purpose.

It was another Thursday morning at Snake Mobile and Oracle DBAs were enjoying Chai with their colleagues at Chandni Chowk Café in their company Cafeteria. Then all of a sudden Manmeet Singh got a call from his manager, Bothering Gupta about an unhappy email from an end user. He explained that one of the customer report is running slow and he want Singh to look into it.Manmeet started gathering all his tools and sticky notes to take a look at what is happening within the database. He IMed the customer to get details about the job and what time it runs daily. He also enquired about expected run time and last run time. Customer informed him that it use to finish in 30 minutes, but in last 3 days it is taking 1 hr 15 minutes to complete.

Using Oracle Grid Control Manmeet found the sql query and its SQL_ID. The sql was a 170 line complicated query with UNION, left and inner joins with several sub selects and inline views. He grabbed another cup of newly brewed Charbucks Coffee from the coffee machine to get over the shock of this tsunami sql.

He tried to see if this sql has more than 1 execution plan. So he used following sql
SQL > select * from table(dbms_xplan.display_awr('csu1z5zqf7sbc')); -- csu1z5zqf7sbc is the SQL_ID

This query listed details of all execution plans available with plan hash value in a nicely formatted way. He found 3 different execution plans for the above sql. But he is not sure which one is the good one and which one is used by system now. So he need to look at some other view. So some more queries from his arsenal. He need to find SNAP_ID, query execution time, number of records processed

SQL> select a.snap_id,a.begin_interval_time,b.executions_total,b.executions_delta,b.elapsed_time_delta,
round((elapsed_time_delta / 1000 / executions_delta),3) avgelapsetimems,
b.rows_processed_total,b.rows_processed_delta,
round((cpu_time_delta / 1000 / executions_delta),3) avgcputimems
from sys.wrh$_sqlstat b, sys.wrm$_snapshot a
where executions_delta > 0 and a.snap_id = b.snap_id and a.dbid = b.dbid
and b.instance_number=a.instance_number
and sql_id ='csu1z5zqf7sbc' and a.begin_interval_time > sysdate - 10
order by a.snap_id,a.begin_interval_time;

SQL> select distinct dbid, sql_id, plan_hash_value from dba_hist_sql_plan where sql_id='csu1z5zqf7sbc';
DBID SQL_ID PLAN_HASH_VALUE
---------- ------------- ---------------
2797229286 csu1z5zqf7sbc 1007430168
2797229286 csu1z5zqf7sbc 1942751973
2797229286 csu1z5zqf7sbc 3495545440
SQL> select distinct a.snap_id,a.plan_hash_value from sys.wrh$_sql_plan a, v$database b where a.dbid = b.dbid and a.sql_id = 'csu1z5zqf7sbc';
SNAP_ID PLAN_HASH_VALUE
---------- ---------------
16850 1007430168
16970 1942751973
17114 3495545440

SQL> select * from sys.wrm$_snapshot where snap_id in (16850,16970,17114);

SNAP_ID DBID INSTANCE_NUMBER STARTUP_TIME
---------- ---------- --------------- ---------------------------------------------------------------------------
BEGIN_INTERVAL_TIME END_INTERVAL_TIME
--------------------------------------------------------------------------- ---------------------------------------------------------------------------
FLUSH_ELAPSED SNAP_LEVEL STATUS ERROR_COUNT BL_MOVED SNAP_FLAG
--------------------------------------------------------------------------- ---------- ---------- ----------- ---------- ----------
16850 2797229286 1 22-JUL-10 10.56.01.000 PM
07-MAR-11 07.00.15.751 PM 07-MAR-11 08.00.28.204 PM
+00000 00:00:17.8 1 0 0 0 0
16970 2797229286 1 22-JUL-10 10.56.01.000 PM
12-MAR-11 07.00.31.943 PM 12-MAR-11 08.00.44.143 PM
+00000 00:00:15.9 1 0 0 0 0

He noticed that something has changed with statistics for the tables used in this complicated query in last 10 days. He can find current values of statistics from DBA_TABLES view. But what about history. He used following query to find that detail. He decided to use a bind variable in his query as there are 6 separate tables involved in the slow running sql from same schema.

SQL> select a.savtime,a.flags,a.rowcnt,a.blkcnt,a.avgrln,a.samplesize,to_char(a.analyzetime,'DD-MON-YY HH24:MI:SS') as anlzetime from sys.WRI$_OPTSTAT_tab_history a, dba_objects b where a.obj#=b.object_id and b.owner='TCSDBOWNER' and b.object_name='&table_name' order by 1;
Enter value for table_name: EXTRA_FIELD

Hurray, he found a sudden change in ROWCNT value for 2 of the big tables used in the query. That explains the reason for slowness.

BONUS QUESTION: Manmeet Singh is planning to lock table stats with the statistics values which gave him a good execution plan. Will it work and is it a good approach?

Transferring table statistics from Instance 1 to Instance 2

Following steps need to be done to transfer statistics of a table from Instance 1 to Instance 2.

• create stats table
• export stats to this stats table
• export newly created stats table
• transfer export dump file to other server
• import this dump file
• import stats from newly imported stats table 

On Instance 1

Step 1) Create stats table
exec dbms_stats.create_stat_table('STSC','STAT_PROCESSSKU_MAR16');
PL/SQL procedure successfully completed.

Step 2) Export stats for a particular table to this stats table
SQL> exec dbms_stats.export_table_stats(ownname =>'STSC',tabname =>'PROCESSSKU',stattab =>'STAT_PROCESSSKU_MAR16',cascade =>true,statown =>'STSC');
PL/SQL procedure successfully completed.

Step 3) Export stats_table using Oracle export
$ exp / file=exp_stsc_processsku_stats_from_pjda1.dmp tables=STSC.STAT_PROCESSSKU_MAR16 log= exp_stsc_processsku_stats_from_pjda1.log

On Instance 2
Step 1) Create stats table
SQL> exec dbms_stats.create_stat_table('STSC','STAT_PROCESSSKU_MAR15');
PL/SQL procedure successfully completed

Step 2) Export stats for a particular table to this stats table
SQL> exec dbms_stats.export_table_stats(ownname =>'STSC',tabname =>'PROCESSSKU',stattab =>'STAT_PROCESSSKU_MAR15',cascade =>true,statown =>'STSC');
PL/SQL procedure successfully completed.

Step 3) Export stats_table using Oracle export (Optional , just for additional backup)
$ exp / file=exp_stsc_processsku_stats_from_qjda1.dmp tables=STSC.STAT_PROCESSSKU_MAR15 log= exp_stsc_processsku_stats_from_qjda1.log
Export: Release 10.2.0.4.0 - Production on Wed Mar 16 10:24:29 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Current user changed to STSC
. . exporting table STAT_PROCESSSKU_MAR15 19 rows exported
Export terminated successfully without warnings.

Step 4) Transfer exp_stsc_processsku_stats_from_pjda1.dmp file from Instance 1 to Instance 2 for import in Instance 2

Step 5) Import stats table from pjda1 using imp command in Instance 2
$ imp / file=exp_stsc_processsku_stats_from_pjda1.dmp full=y log=imp_stsc_processsku_stats_from_pjda1.log

Step 6) Unlock schema stats if it is already locked (Optional. In my case needed)
SQL> exec DBMS_STATS.UNLOCK_SCHEMA_STATS(ownname => 'STSC');
PL/SQL procedure successfully completed.

Step 7) Import stats for STSC.PROCESSSKU table and its index from STSC.STAT_PROCESSSKU_MAR16 stats table
SQL> exec dbms_stats.import_table_stats(ownname =>'STSC',tabname =>'PROCESSSKU',stattab =>'STAT_PROCESSSKU_MAR16',cascade =>true,statown =>'STSC');
PL/SQL procedure successfully completed.

Step 8) Lock schema stats again
SQL> exec DBMS_STATS.LOCK_SCHEMA_STATS(ownname => 'STSC'); (Again optional in your case)
PL/SQL procedure successfully completed.

JDBC programming tips with Oracle RAC databases

You might be using jdbc:oracle:driver:@database format in your java code to establish connection to Oracle instances.

Database can be of the format {host:port:sid | net_service_name | connect_descriptor}. Driver specifies the type of JDBC driver to use for connection. The choices are

thin – for the Oracle thin driver (No SQL net needed)
oci7 – For Oracle 7.3.4 OCI driver
oci8 – For Oracle 8.x.x OCI driver
oci – For an Oracle 9.x.x or higher driver
kprb – For the oracle internal driver used in Java Stored Procedures

Thin driver will understand only SID and Service names won’t work with thin driver. In case of a need to use Service names, we need to use oci driver. But oci needs SQL Client to be installed on the same host where we execute the java calls. It is claimed that OCI calls performs 400% faster than thin driver calls and with a small modification in the definition in the code, we can migrate code to work with RAC instances.

Another question I hear all the time, why there are different ojdbcX.jar files. Ojdbc4.jar is for JDK 1.4, ojdbc5.jar is for JDK 1.5(also known as JDK 5) and ojdbc6.jar is for JDK 6.

The oracle.jdbc.driver.* classes, the ojdbc4.jar file, and the OracleConnectionCacheImpl class are no longer supported or available.

I tested jdbc thin driver connection string with different combinations.

1. Simple method where we specify server name, port and sid
String URL = "jdbc:oracle:thin:@" + serverName + ":" + port + ":" + SID;
2. More complicated setup which is needed for RAC or when using service names. As jdbc thin driver does not understand tns alias or as thin client is used in applet configuration which works from a web brower, it is isolated from oracle client tools. So calling tns alias directly is not supported. But you can take the contents from tnsalias and set in connection string as follows.
String URL = "jdbc:oracle:thin:@" +
"(DESCRIPTION =" +
"(ADDRESS_LIST =" +
"(ADDRESS = (PROTOCOL = TCP)(HOST = node1.somedomain.com)(PORT = 7737))" +
"(ADDRESS = (PROTOCOL = TCP)(HOST = node2.somedomain.com)(PORT = 7737))" +
"(LOAD_BALANCE = yes)" +
")" +
"(CONNECT_DATA =" +
"(SERVER = DEDICATED)" +
"(SERVICE_NAME = node.somedomain.com)" +
")" +
")";

From 10.2 onwards calling tnsalias is supported in following way.
In 10.2, JDBC-Thin supports TNSNAMES entries lookup in tnsnames.ora, resulting in a much simplified and driver type independent Oracle Net descriptor. You must set the system property oracle.net.tns_admin to the directory that contains your tnsnames.ora file. See the Oracle JDBC 10.2 document for detailed description.

java -Doracle.net.tns_admin=$ORACLE_HOME/network/admin
or:
System.setProperty("oracle.net.tns_admin", "c:\oracle\net\admin");
OracleDataSource ods = new OracleDataSource();
ods.setTNSEntryName("tns_alias");
ods.setUser("scott");
ods.setPassword("tiger");
ods.setDriverType("thin");
Connection conn = ods.getConnection();

Here is my test program which used both options. I tested this connection in my RAC instance and is working well with service names.

/* This program is to test different connection options for jdbc thin driver
Written by Madhu Kangara
*/
import java.sql.*;
import oracle.jdbc.pool.OracleDataSource;

class DbConnectService {
  public static void main(String args[]) throws SQLException {
    DriverManager.registerDriver(
       new oracle.jdbc.driver.OracleDriver()
    );
    String serverName = "node2.somedomain.com";
    int port = 7737;
    String user = "mkangar";
    String password = "Change_me";
    String SID = "NODE2";
    // First way to do it (simple way). Uncomment following line if you wish to use this method.
    //String URL = "jdbc:oracle:thin:@" + serverName + ":" + port + ":" + SID;
    // Second way to do with full tnsnames entry
    String URL = "jdbc:oracle:thin:@" +
    "(DESCRIPTION =" +
    "(ADDRESS_LIST =" +
      "(ADDRESS = (PROTOCOL = TCP)(HOST = node1.somedomain.com)  (PORT = 7737))" +
      "(ADDRESS = (PROTOCOL = TCP)(HOST = node2.somedomain.com)(PORT = 7737))" +
      "(LOAD_BALANCE = yes)" +
    ")" +
    "(CONNECT_DATA =" +
      "(SERVER = DEDICATED)" +
      "(SERVICE_NAME = node.somedomain.com)" +
      ")" +
    ")";
    Connection conn = DriverManager.getConnection(URL, user, password);
    String SQL = "SELECT INSTANCE_NAME FROM V$INSTANCE";
    Statement stat = conn.createStatement();
    ResultSet rs = stat.executeQuery(SQL);
    while (rs.next()) {
       System.out.println(rs.getString(1)
       );
    }
    stat.close();
    conn.close();
  }
}

Please don’t forget to set your CLASSPATH with right path to your java class files and ojdbcX.jar file. (Need to add the settings line)

Here is the output of compilation and execution
C:\Projects\java\scripts>javac DbConnectService.java
C:\Projects\java\scripts>java DbConnectService
node1

Due to above compilcations with programming and abilility to scale with more nodes added a RAC cluster, Oracle added a new feature in Oracle 11gR2 which is called SCAN. This is a DNS name which does name resolution management behind the scene with several rules and end user will be dealing with a single domain name like they done with non RAC databases. More about this new feature in another blog.

Killing a session globally in Oracle 11gR2 RAC

Oracle has added a very useful option in 11gR2 RAC to kill a session on instance B from instance 1. They have added instance name as a parameter in kill session syntax

See following example

sys@ARAC> select username ,status,inst_id from gv$session where username='REMEDY';

USERNAME STATUS INST_ID
------------------------------ -------- ----------
REMEDY INACTIVE 2

sys@ARAC> alter system kill session '787,1113,@2' immediate;
System altered.

sys@ARAC> select username ,status,inst_id from gv$session where username='REMEDY';
no rows selected

thanks to my guru Javed Iqbal for telling me to RTFM

ORA-39014: One or more workers have prematurely exited error message with Oracle datapump export

Last week I came across an interesting problem in one 10gR2 10.2.0.4 database instance on AIX platform. I have a daily full datapump export which is running for last one year without any errors. But when I tried to export a couple of tables from one schema, I got ORA-39014 error. Here is the full error message.

Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW01" prematurely terminated
ORA-31672: Worker process DW01 died unexpectedly.
Job "OPS$ORACLE"."SYS_EXPORT_TABLE_01" stopped due to fatal error at 14:05:45
ORA-39014: One or more workers have prematurely exited.

My export par file was as follows

$ cat special_dp_export_pjda1_tables.par
userid=/
DIRECTORY=EXPDP_DIR
DUMPFILE=special_dp_export_pjda1_tables_02082011.dmp
LOGFILE=special_dp_export_pjda1_tables_02082011.log
TABLES=STSC.ITEM,
STSC.DMDUNIT

And datapump export command was

$ expdp parfle=special_dp_export_pjda1_tables.par

I did some Metalink search and found out that this is a bug in 10.2.0.4 and their solution was to upgrade to 11g which is not possible for me to do due to production nature of this instance.

I was able to have a successful datapump export by modifying my par file as follows

userid='/ as sysdba'
DIRECTORY=EXPDP_DIR
DUMPFILE=special_dp_export_pjda1_tables_02082011.dmp
LOGFILE=special_dp_export_pjda1_tables_02082011.log
TABLES=STSC.ITEM,
STSC.DMDUNIT

In the beginning I mentioned that I have a daily full datapump export which is running without any error for last one year with userid=/ and not with userid='/ as sysdba'. What a mystery. I was also able to reproduce this error in another 10.2.0.4 Oracle instance. Does anyone has any answer why I don’t get this error with full export?

ORA-24247 when trying To send email using UTL_SMTP in Oracle 11gR2

If any of your applications use SYS.UTL_SMTP package to send emails in Oracle 11gR2, you will come across ORA-24247 in Oracle 11gR2. Following notes is explaining the problem and solution.

I have a procedure to use SYS.UTL_SMTP package to send mail from my sql scripts in Oracle 10gR2 and was running fine. Here is the simplified version of this code.


create or replace procedure mail_send_test
as
smtp_server varchar2(50);
c utl_smtp.connection;
begin
smtp_server := 'mailserver.domain.name';
c := utl_smtp.open_connection(smtp_server,25);
dbms_output.put_line ('connection success');
end;
/


I started getting following error after upgrading my database instance to 11gR2.
Here is the full work log


[oracle@prdcxx002:/home/oracle] $ sqlplus /
SQL*Plus: Release 11.2.0.2.0 Production on Wed Feb 2 15:40:14 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @smtp_test.sql;
Procedure created.
SQL> exec mail_send_test;
BEGIN mail_send_test; END;
*
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 267
ORA-06512: at "SYS.UTL_SMTP", line 161
ORA-06512: at "SYS.UTL_SMTP", line 197
ORA-06512: at "OPS$ORACLE.MAIL_SEND_TEST", line 10
ORA-06512: at line 1


After reading Oracle Metalink and Oracle 11g manual, I understood that this is an expected behavior as Oracle started implementing Access Control List (ACL) in sensitive packages like UTL_SMTP, UTL_HTTP etc to take care of security loop holes. We need to start granting privileges for users who needs to use these packages via SYS.DBMS_NETWORK_ACL_ADMIN procedure. DBMS_NETWORK_ACL_ADMIN procedure provides security for network related PL/SQL packages UTL_TCP, UTL_HTTP, UTL_SMTP, UTL_MAIL, and UTL_INADDR. Here are the steps to grant them. You need XDB to be installed for ACL to work.


Solution is to execute following sql procedures after connecting as SYSDBA
1) Creates an access control list (ACL) with an initial privilege setting


begin
dbms_network_acl_admin.create_acl (
acl => 'mailserver_acl.xml',
description => 'ACL for user to connect to SMTP Server',
principal => 'ORACLE_USERNAME',
is_grant => TRUE,
privilege => 'connect');
end;
/
commit;


2) Assigns an access control list (ACL) to a network host, and optionally specific to a TCP port range


begin
dbms_network_acl_admin.assign_acl (
acl => 'mailserver_acl.xml',
host => 'mailservername.domain.name',
lower_port => 25);
end;
/
commit;


3) Adds a privilege to grant or deny the network access to the user in an access control list (ACL)


begin
dbms_network_acl_admin.add_privilege (
acl => 'mailserver_acl.xml',
principal => 'OPS$ORACLE',
is_grant => TRUE,
privilege => 'connect');
end;
/
commit;


After these 3 steps, I logged in as Oracle on prdcmds002 and executed the procedure which connects to mail server and it worked without any ORA errors.


SQL> set serveroutput on
SQL> exec mail_send_test;
connection success
PL/SQL procedure successfully completed.


Please note that due to some bugs, we need to grant these privileges to individual users and not to a role. See Bug 7010711: ACL PRIVILEGES GRANTED TO ROLES ARE NOT CASCADED TO THE USERS


References
=======
1. See Oracle Metalink Doc: 754909.1 - ORA-24247 Calling UTL_SMTP or UTL_HTTP or UTL_TCP in a Stored Procedure [ID 754909.1]
2. See Oracle Metalink Doc: 557070.1 - ORA-24247 Trying To Send Email Using UTL_SMTP from 11gR1 (11.1.0.6) or higher

We need to set ACL for UTL_INADDR package also in Oracle 11g. We could check this by issuing
SELECT utl_inaddr.get_host_name FROM dual;If you get ORA-24247, create a acl with privilege=resource.

Here is the sql I used.
-- rem Create ACL for using UTL_INADDR
begin
dbms_network_acl_admin.create_acl (
acl => 'resolve_ipaddr_acl.xml',
description => 'ACL for user to resolv Network names and ip address',
principal => 'ETRADM',
is_grant => TRUE,
privilege => 'resolve');
end;
/
commit;

-- rem Assign ACL to one or more network hosts
begin
dbms_network_acl_admin.assign_acl (
acl => 'resolve_ipaddr_acl.xml',
host => '*');
end;
/
commit;

You need XDB to be installed for ACL to work. So if you get following error, verify XDB is installed.

Duplicate Filename Found for Submission_id: 6011
Standard exception
VALIDATE Submission: 6011 Step: Duplicate Submission Ch
Exception CODE: 1
Exception ERRM: User-Defined Exception
Stop Time: 21-MAR-2011 08:15:04 AM
DECLARE
*
ERROR at line 1:
ORA-24248: XML DB extensible security not installed
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 246
ORA-06512: at "SYS.UTL_SMTP", line 127
ORA-06512: at "SYS.UTL_SMTP", line 150
ORA-06512: at "FFLADM.EMAIL_HANDLER", line 36
ORA-06512: at "FFLADM.REBATE_PROCESS", line 515
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 5
can use following script to install XDB

SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /orasw/app/oracle/admin/scripts/xdb_protocol.log append
@/orasw/app/oracle/product/11.2.0/db/rdbms/admin/catqm.sql change_on_install SYSAUX TEMP YES;
connect "SYS"/"&&sysPassword" as SYSDBA
@/orasw/app/oracle/product/11.2.0/db/rdbms/admin/catxdbj.sql;
@/orasw/app/oracle/product/11.2.0/db/rdbms/admin/catrul.sql;
spool off
You can use following views to see current ACL settings.
 

SQL>  select acl,principal from dba_network_acl_privileges;

ACL                                 PRINCIPAL

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

/sys/acls/mailserver_acl.xml        OPS$ORACLE

/sys/acls/mailserver_acl.xml        ETRADM

/sys/acls/resolve_ipaddr_acl.xml    ETRADM

SQL> select * from DBA_NETWORK_ACLS;

HOST                                     LOWER_PORT UPPER_PORT ACL                                 ACLID

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

myserver.unix.tarikida.org                          25         25 /sys/acls/mailserver_acl.xml        9B6B9AF666988026E0430A9A1D188026

*                                                              /sys/acls/resolve_ipaddr_acl.xml    9B7AC2AFB00B202AE0430A9A1D18202A