Duplicating Oracle 11g database using RMAN ACTIVE BACKUP

I hope you knew that we can duplicate one Oracle 11g database to a new server using a latest RMAN option which eliminates the need to backup to tape or shared file system and restore this rman backup at new target location. In Oracle 11g, Oracle has introduced new ACTIVE DATABASE DUPLICATE option to create a copy of source database.

Assume that you have one Oracle 11.2 database named “mango” running on mango.tarikida domain and you want to restore this database to an instance named orange in a  new server named orange.tarikida. Data file systems in mango are /db0X/mango and data file systems in orange are /db0X/orange.

1)      Login to server orange.tarikida as oracle user and set env variables

oracle@orange dbs]$ . oraenv

ORACLE_SID = [orange] ?

The Oracle base remains unchanged with value /apps/oracle

2)      Create password file in $ORACLE_HOME/dbs in orange

oracle@orange dbs]$ orapwd file=orapworange password=passw0rd

3)      Start listener LISTENER_orange in server orange.

[oracle@orange dbs]$ lsnrctl start LISTENER_orange

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 05-AUG-2012 16:02:07

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Starting /apps/oracle/product/11.2.0/db/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.3.0 - Production

System parameter file is /apps/oracle/product/11.2.0/db/network/admin/listener.ora

Log messages written to /apps/oracle/diag/tnslsnr/orange/listener_orange/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=orange)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.168.72)(PORT=7388)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=orange)))

4)      Verify that you can perform tnsping to mango and orange from servers mango and orange. Please make sure that you perform this step on both servers.

oracle@orange dbs]$ tnsping mango

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 05-AUG-2012 16:01:45

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:

/apps/oracle/product/11.2.0/db/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.168.71)(PORT = 7387))) (CONNECT_DATA = (SERVICE_NAME = mango.tarikida)))

OK (10 msec)

[oracle@orange admin]$ tnsping orange

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 05-AUG-2012 16:21:23

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:

/apps/oracle/product/11.2.0/db/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.168.72)(PORT = 7388))) (CONNECT_DATA = (SERVICE_NAME = orange)))

OK (0 msec)

5)      Create a dummy initorange.ora file in ORACLE_HOME/dbs at server orange with just following lines. Please add correct values.

DB_NAME=orange

CONTROL_FILES=

DB_BLOCK_SIZE=

DB_FILE_NAME_CONVERT=

LOG_FILE_NAME_CONVERT=

DIAGNOSTIC_DEST=

COMPATIBLE=11.2.0

6)      Start oracle instance orange in nomount mode in server orange.

[oracle@orange dbs]$ pwd

/apps/oracle/product/11.2.0/db/dbs

[oracle@orange dbs]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Sun Aug 5 16:03:03 2012

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

Connected to an idle instance.

SQL> startup nomount pfile=initorange.ora

ORACLE instance started.

Total System Global Area  263049216 bytes

Fixed Size                  2227456 bytes

Variable Size              96469760 bytes

Database Buffers          159383552 bytes

Redo Buffers                4968448 bytes

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

7)      Start rman for duplicating database

[oracle@orange dbs]$ rman

Recovery Manager: Release 11.2.0.3.0 - Production on Sun Aug 5 16:04:22 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect TARGET sys/passw0rd@mango;  -- mango is my source

connected to target database: MANGO (DBID=3989853245)

RMAN> connect AUXILIARY sys/passw0rd@orange;  -- orange is my new instance

connected to auxiliary database: ORANGE (not mounted)

RMAN> duplicate TARGET DATABASE TO orange FROM ACTIVE DATABASE SPFILE PARAMETER_VALUE_CONVERT 'mango','orange'  SET DB_FILE_NAME_CONVERT 'mango','orange' SET LOG_FILE_NAME_CONVERT 'mango','orange';

For a small database above task should finish in less than 15 minutes.

 

ORA-01008: not all variables bound while doing RMAN backup

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Jan 17 15:00:22 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN>

connected to target database: MADM (DBID=xvxvxvxv)

RMAN>

connected to recovery catalog database

RMAN> 2> 3> 4> 5> 6> 7> 8> 9>

DBGSQL:     TARGET> select  nvl(max(al.recid), '0'),nvl(max(al.recid), 0)   into  :txtparmvalue, :parmvalue   from  v$archived_log al  where  al.status in ('X', 'A')    and  al.is_recovery_dest_file = 'YES'    and  al.creator = 'RMAN'

DBGSQL:        sqlcode = 1008

allocated channel: t1

channel t1: SID=1726 device type=SBT_TAPE

channel t1: Veritas NetBackup for Oracle - Release 6.5 (2009050106)

DBGSQL:     TARGET> select  nvl(max(al.recid), '0'),nvl(max(al.recid), 0)   into  :txtparmvalue, :parmvalue   from  v$archived_log al  where  al.status in ('X', 'A')    and  al.is_recovery_dest_file = 'YES'    and  al.creator = 'RMAN'

DBGSQL:        sqlcode = 1008

released channel: t1

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

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

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

RMAN-03002: failure of backup command at 01/17/2012 15:00:36

RMAN-03014: implicit resync of recovery catalog failed

ORA-01008: not all variables bound

RMAN>

Recovery Manager complete.

I noticed that resync catalog or crosscheck archivelog all did not help in this case and this error appears due to a BUG in 11.2.0.2. You can read more about this issue at Oracle Metalink doc ID: 1280447.1

Current fix is to flush shared_pool and you will be able to start rman backup again.

SQL> alter system flush shared_pool;

ORA-29701: unable to connect to Cluster Manager while starting ASM

I prefer to install Oracle binaries using Silent Cloning method and manually create database instances using my custom scripts. 99% percent of the time I follow this method to build production databases at my work and use 1% only in case I evaluate a new version of Oracle. I need to admit that I never managed an ASM instance really and all our RAC installations are non ASM based with Veritas SFRAC as the shared disk sub system.  Since I don’t want to be left out of this ASM band wagon, last week I started building a new ASM instance from scratch without going through Oracle Installer GUI and DBCA. Everything went smooth till I mount the ASM instance. Then I came across ORA-29701. Please note that this is a non CRS server and I don't plan to run a RAC instance here.

echo $ORACLE_SID
+ASM
$ sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jan 6 15:42:21 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup

ORA-29701: unable to connect to Cluster Manager

ORA-29701. Oracle uses cssd daemon for database instances which uses ASM to communicate with ASM instance. This is needed even if you don't plan to have a RAC instance or Oracle Clusterware installed on this node. Since I did not build ASM using Oracle GUI, Oracle did not add or configure cssd daemon to the OS stack. So  I need to login as root and configure it manually as follows

# cd $ORACLE_HOME/bin

# localconfig add

This step will does the task for you and you can start your ASM instance without any issues.

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.