Thursday, October 24, 2013

When error say nothing about real issue

We want restore backup from source. So we took backup from source and copied it to /dba/share/MYDB on target. My collouge sent me email saying  restore failed with following error


rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Sep 18 20:47:38 2013

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

connected to target database (not started)

RMAN> startup nomount

Oracle instance started

Total System Global Area    8551575552 bytes

Fixed Size                     2245480 bytes
Variable Size                503319704 bytes
Database Buffers            8036286464 bytes
Redo Buffers                   9723904 bytes

RMAN> set DBID=2387922???;

executing command: SET DBID

RMAN> restore controlfile from '/dba/share/MYDB/c-2387922???-20130910-00.ctl';

Starting restore at 18-SEP-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=391 device type=DISK

channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/18/2013 20:48:04
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece




We searched MOS and forums. Most of the search , pointing it is old backup ( more then 7 day ),  some doc say backup corrupted etc etc. Instead going for whole story that how reached to conclusion, let we tell you the finding.

We found the mount option for /dba/share/MYDB ( where we copied backup from source ) not correct
...that's why getting error. We changed it to

           rw,bg,intr,hard,timeo=600,wsize=32768,rsize=32768,nfsvers=3,tcp,noacl 0 0

After this change we able to restore control file and rest of the backup successfully

Sunday, January 29, 2012

How to rename SQL Tuning Set ( STS )

Rename SQL Tuning Set‎( STS )

We can use SQL Performance Analyzer (SPA) to evaluate impact on SQLs of 11g R2 database upgrade from 10.2 .So created some demo , example ( with full code ). EM was very good option , but still preferred sql,pl-sql, command / api.

We all know to, use SPA for 10.2 to 11.2 database upgrade , we need one intermeiate 11g DB.
So we can run trial remotely.So we collected SQL Tuning Set (STS ) ( using demo code ) from Multiple databases and import on intermediate 11g DB. Problem started when unpacking STS, collected from different production / Test environment having same name because all
used demo code :).

So started looking for solution , is there any way to rename STS !!!! and end up with following code

-- Create new STS and copy from exiting one
DECLARE
l_cursor DBMS_SQLTUNE.sqlset_cursor;
BEGIN
-- Create STS
DBMS_SQLTUNE.create_sqlset(sqlset_name => 'STS_DBNAME_CMUL',description => 'new STS SQL tuning set from XYZ DB');
OPEN l_cursor FOR
SELECT VALUE(r)
FROM TABLE (DBMS_SQLTUNE.select_sqlset (
'STS_RAG_CMUL' -- old sqlset_name which need to rename
)
) r;
DBMS_SQLTUNE.load_sqlset (
sqlset_name => 'STS_DBNAME_CMUL',
populate_cursor => l_cursor);
-- Drop STS
dbms_sqltune.drop_sqlset('STS_RAG_CMUL');
close l_cursor;
END;
/

Sunday, September 5, 2010

Sangam’10: All-India Oracle Users Group (AIOUG) Annual Conference

Sangam’10: All-India Oracle Users Group (AIOUG) Annual Conference



The All India Oracl Users Group (AIOUG) presented Sangam 2010, a two-day(3-4-Sep-2010) Oracle Users Conference held in Hyderabad. Sangam 2010 hosted multiple sessions by international and Indian experts like Jonathan Lewis , Rittman , Iggy Fernandez etc.




Jonathan Lewis & Virag Sharma


The event was very good and opportunity to learn from experts. Sangam 2010 started with Murali welcome note followed by Presentation, by Roland Slee, Vice-President, Database Product Management, Oracle Asia Pacific & Japan.


First technical session was from Jonathan Lewis on "Writing Optimal SQL". This One day Presentation was divided in to two , 1/2 day sessions. Conference room packed for his sessions, Jonathan Lewis Presentation was main attraction for Sangam 2010. After Lunch , there were 2 Conference room for different sessions and audience can choose session as per there interest.

Sponsors "OSI Consulting" presentation on "Cross Platform migration challenges and time reduction techniques" was pretty good then expected. After "OSI Consulting" presentation, RAC SIG meeting held. In this meeting Satyendra Kumar , explained things about RAC SIG. In the end of "RAC SIG" meeting Satyendra looking leader for various city , for Delhi/NCR, I proposed Aman Sharma’s name and latter learned that Aman recently become Oracle ACE.

Session “Tips and Best Practices for DBA’s” from Francisco , was in soo much demand that Conference room 2 overflowed and finally session held in Conference room 1.

Rittman and Vivek took one session in Sangam 2010 and unfortunately , Not able to attend any of them.In the end of Sangam 2010 Oracle Users Conference, Jonathan Lewis took 1 Hr question and Answer session, that was pretty good.


Tuesday, April 13, 2010

Certain things in this world

"In this world nothing can be said to be certain but death and taxes."
--Benjamin Franklin

Monday, September 28, 2009

Module name for logon trigger in 11g R2 AWR report

Today while working on Production performance tuning, We came across one difference in awr report of 11g R1 and 11R2

In oracle 11g R1 AWR report logon trigger modelue name usually come like
sqlplus / perl / Mid Tier@server_name_from_it_login

But in 11g R2 AWR report logon trigger modelue name usually come like oraagent.bin@DB_SERVER_WHERE_IT_RUN

New change looks more logical because DB server running logon trigger code ,
not the user.




# Excerpt from 11g R1 AWR report
# In below given example app678 is server name from where
# user logged in to database using sqlplus

Module: sqlplus@app678utl (TNS V1-V3)
UPDATE scott.MY_AUDIT SET LAST_LOGIN = SYSDATE WHERE XYZ_USERNAME = :B1


#Excerpt from 11g R2 AWR report
#In below given example apps001 is database server

Module: oraagent.bin@apps001 (TNS V1-V3)
UPDATE scott.MY_AUDIT SET LAST_LOGIN = SYSDATE WHERE XYZ_USERNAME = :B1



Google