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