About me

Being born and brought up in vijayawada as only son to my parents, I used to be very calm going boy at the start of every academic year and most craziest boy at the end.

After completing my X std education in T.K.S.V.Lions school (don’t ask for abbrevation of T.K.S.V, i forgot long back !!!), and XII std (Intermediate) in Vijaya Krishna Junior College, my father forced me to complete my Engg from Jerusalem college of Engg, Chennai so as to see how the world is…. ( I had not been to any place since my childhood and i realized that this is the reason in my Father’s thoughts)

2004, year which tagged a B.Tech (IT) degree to my hand and throwed me to the world of competetion and asked me to survive.

After a struggle for 1 yr, I am here as Oracle DBA with a minimal knowledge and experience. Daily I feel to dig lot of things in DBA.

This Blog is intended to share my minimum knowledge to others

I have been active EXPERT member in Oracle Forums. Right now, I am working as Operations Manager for Oracle DBA team and as Database Architect in a MNC.

As I love Oracle DBA so much, same I love to watch Movies. Many stories builded from my mind, hope some day I will hold megafone.

I dedicate this blog to my mother VIJAYA LAKSHMI and father MURALI KRISHNA, who were always been there to build me as successful man.

Last but not least, I want to dedicate this page to my wife who have been always with me in understading my passion towards Oracle DBA.

About a year back, wonderful angel DHRUVI occupied our lives and now all the time is set for her to play with me. Still been trying to post new things I am learning

Here are some of my certifications

Share this:

Like this:

489 Responses to “About me”

Harmeet Singhsaid

I have not seen as many comments as I see here. I am a DBA, started career in Oracle and then due to migration had to switch to SQL Server. Now I am planning to move back to Oracle, suggest me a good site or Blog where I can refresh things again. Its been more than 2 years I am out of touch with Oracle.

Hi Harmeet, thanks for your words. for refreshing, i would say always it is best to read oracle documentation or read books which are available in google. also, there are plenty of videos available over youtube which gives knowledge on latest versions.

Melinda Sosasaid

Hi Pavan,
I am an accidental DBA with a modest, but constantly growing skillset. On several occasions I have searched the internet and other resources for days for solutions to various ora- tns- errors. The answers all seem the same, very general and superficial and did not solve my problems. On those occasions, I have eventually found myself on you site, where I find solutions that work. I appreciate your explanations, they are thoughtful and complete. I am making you my first resource!
Peace,
misosa

munirsaid

hello sir
I’am currently teaching oracle 11g administration in delhi,how can I get a full time job in database industry as a dba.Is there any job available for me,I’am a B.Tech in CS with good working knowledge in oracle

oraclesaid

I have a full database backup with archive log all till 17 May 2015 Now team want to restore the database with 13 May 2015 only with the help of 17 May 2015 full backup then how we can restore back date Pls guide.

Amit Kumar Yadavsaid

Difference Between OLAP & OLTP at hardware level ?
How will come to know at schema level difference between OLAP & OLTP ?
Do we have any Parameter which can be a differentiation for OLAP & OLTP ?
Can we create OLTP & OLAP instance in same DB ?

Hi Amit,
SGA and PGA memory allocations will be different for OLTP and OLAP. Hardware level there will not be much difference.
at schema level, we cannot identify really but transactions wise, yes there will be. there is no such single parameter to know if it is OLTP or OLAP. if you can know what is the application functionality, then you can confirm on it.

chandansaid

can we configure/install oracle RAC in a company’s test environment for testing? if yes, does oracle charge for the this configuration/licensing. Appreciate your suggestion/s before making taking any decision.

subhansaid

My self I am Subhan Completed Btech In 2014 , Is it better to do Oracle DBA course as my side because as i am from 2014 batch..If i complete the oracle DBA course will be there any opportunities for me to get the job..
waiting for your reply sir

Satyasaid

I have not done any certifications yet! I am preparing for OCA 11g and searching for latest dumps of OCA 11g, But Oracle keeps changing the questions after every 2 months. So can you please suggest how can I get the latest dumps of OCA 11g. Please send the dumps to my mailid – psndba@gmail.com.

My name is Nick. I am the Sr. IT Recruiter at Pull Skill Technologies Inc (Pull Skill). I have identified your resume online and thought you might be a good match for this requirement! If you are qualified, available, interested, or planning to make a change, please reply to me via email with your most current resume attached. Kindly include a daytime phone number where you can be reached.

However, only you know if the position is the right position for you. Would you be kind enough to review the job description and let me know what you think?

pavansaid

Jayarajsaid

Hi Pavan,
We have a project to setup 3 Node Oracle 11G R2 RAC on RHEL6.X and do a migration of the Databases from old RAC on 11G R1. Could you please direct me to any good consultant we can take up the project, we can offer a decent fee for the task.

Arun Kumarsaid

I have finished my Oracle DBA training in Sqlstar. Now search job in oracle DBA. Iam have 6 months experience in development. For oracle dba jobs whether they will consider my development experience or not??. What are Topics mostly covered in fresher oracle DBA? Whether Oracle DBA jobs can be overcome by automation in future???

Hi Pavan,
Im currently working as a Oracle Apps Developer
Im interested to change my path to (Oracle Apps DBA)
Whether this course will be very helpfull in future and can i know wat will be my package if i shifted to Oracle Apps DBA

rajagopalsaid

Hi Pavan,
I am into testing field and want to move to oracle dba. can i shift to oracle dba as i was working daily on database as a database tester. i am having 6yrs experience in testing. Please reply at the earliest.

Vijaya Kumarsaid

Thanks to share all your experience to get exposure in dba for freshers also experienced persons. I have some queries regarding the database Performance. Please check the below issues. Give me the suggestions to get some clarification on the issues.

1. which parameters have to increase for sga_max_size is too low error and why you are getting error?
2. how will you take stackpack report and how to check the issues in that report and how to resolve the issues in that report?
3. after the recovery complete using rman backup why you are getting system tablespace not exist error?
4. if user say their query is taking more time to complete than before hw will u analyze and hw u will resolve the pbm ?
5. how will you set recovery strategy for 24 hrs data loss if cleint asked ?
6. how will you set recovery and redundancy for 24hrs data loss?

It is little bit urgent pavan. please give the reasons and solutions for the above queries.

Sathishsaid

1) What is the difference between Bitmap and btree index?
2) What is the difference is between physically stand by and logically stand by?
3) What is the difference between SGA Max size and SGA Target size?
4) What is the use of gather stats?
5) Why we put database in begin backup mode?
6) How to change SGA size from OS level?

hi sathish, infact for all ur q’s u can get answers from google. but i will try to give one line answers
1. btree index should be created on high cardinality (no. of distinct values is called as cardinality) and bitmap index should be created on low cardinality columns. btree will store data in tree format and bitmap will store in table format
2. in physical standby database will be in either mount/read-only state. in logical standby, database will be in open mode.
3. sga_target will define what memory size oracle should consume from ram during db startup. sga_max_size will specify what max size it can use from ram.
4. optimizer will generate correct and best execution plan if the stats of the table are upto date.
5. to take backup. i had posted an article already in my blog. check that
6. u need to change kernel parameter called shmmax.

Ravisaid

Hi Pavan,
I am Ravi i have done dba course in Kanna 6 months back. Recently I have one question in an interview which is,
1. If the database size is small how do u migrate to other server?
2. If the database size is huze how will do u migrate to other server? Plz help me how do we do that.

Ashutosh Rathisaid

your blog gave lot of information about database. Even I want to build my career in database. I know SQL very well and started learning PLSQL, please suggest me what should I do after that to become a DBA.

sivasaid

Hi Pavan sir,
I am shiva, i am ur student. i have got one question in an interview which is, I have one primary and standby. one of the archive is deleted on primary and there is a gap between primary & standby how do u fill the gap between both? plz tell the in this situation what we willl do ?

Arunsaid

your blog is really awesome and informative. I have suggested many of my friends who are DBA to instead of going to google they should look into blog for there answers. It is very informative and like a ocean. Really an amazing blog. Many congratulations.

I have a query. Actually I am working a manual testing engg for last 4 yrs. Prior to that I have worked with BPO and KPO. can i change my line/profile ..i mean can i start my career as Oracle DBA?. Will i be taken as fresher? will age be an issue for campanies? do you take online classes for Oracle DBA beginners and what all prequistive is needed from my side. Looking forward for your reply. Thanks

Hi Arun, first of all thanks for your words about my blog. but, really this is not more than google. i am sharing very little knowledge i know.

regarding your question, yes surely you can change your line to oracle dba. usually when you change your career path, they might treat you as a fresher in technical, but still process wise you are treated as experienced. also, if you can show the skill set in dba which an exp’d dba can perform, then usually companies don’t have option to reject. age will never be an issue for the companies.

reg classes, yes i do take online classes. but as of now i am dealing with a classroom batch. once it is finished (expecting it to complete by march end), then only i can start online batch. usually sql basics and unix basic commands are the pre-requisite for learning dba. but we do teach them for first 15 days in our course itself.

sivasaid

Hi Pavan sir,
I am siva. i have a small doubt which is, whenever we create a database using DBCA, the data block size is 8kb. After creating the database . 1. can we change 8kb to 16kb? how can we change?
2. Is there any impact on that database?

Ravisaid

Thanks Pavan. Please let me know if you are providing online training as I’m based out of Bangalore.
Also, can you please clarify my concern : some of my DBA friends suggest me to learn SQL Server & mastering SQL/PL SQL will help me to work better as a successful DBA. Please let me know your point of view.

hi ravi, yes i do provide online training. for more details contact 040-40036087 / 8008500064.
reg another question, sql server is a rdbms like oracle, so learning it will not help to become oracle dba (it helps when u want to be a multi skilled dba)

sql is important, but pl/sql is not. though having pl/sql knowledge will be helpful(but not mandatory)

Shadab Nivekarsaid

I am an Oracle DBA started my career into Oracle as working as a RAC DBA with no prior exp of handling databases oracle databases.
Please answer my below queries :-

1) How much SQL & PLSQL is required for a DBA,
2) To do PT better is SQL & PLSQL is really required,
3) Could you suggest any institute which conducts good training on SQL & PLSQL in Hyderabad,
4) I have chosen my career as a RAC DBA & RAC PT expert,please tell me how should i prepare to become RAC & PT expert.

sivasaid

Hi pavan sir,
i am new to u. i have seen ur complements its so nice. In my pc i have done Dataguard configuration. but archives are not tranfered to standby side what we will do? I have seen alertlogfile of primary but it doesn’t show anything at that time what we will do, to transfer archives from primary to standby?

Pavan kumarsaid

I have not done any certifications yet! I am preparing for OCA 11g and searching for latest dumps of OCA 11g, But Oracle keeps changing the questions after every 2 months. So can you please suggest how can I get the latest dumps of OCA 11g

hi anil, it depends on why sysdba was unable to connect. the only scenario I know is when the audit destination is full, then database will not allow sysdba also to connect. in this case u need to go to audit_file_dest location and clear some old audit files. problem will be resolved by that.

if there is any other reason why sysdba is unable to connect, u can check in alert log for the error.

anilkumarsaid

anilkumarsaid

I am getting the following error: ORA-00604:error occurred at recursive sql level 1. This error occurred when I was trying to connect to the database. user. What could be the reason for this and could u tell me the solution for this ORA-604.

Nareshsaid

When will u take the class for 12cinstallation and brief TOAD. one Week has completed. plz the the class for this 2 topics. we all waiting for ur class. Plz take class as soon as possible. already we informed to Receptionist also. we don’t whether she is convied or not.

Pratheeksaid

This is Pratheek. I am new to Oracle Apps DBA. can you please suggest me which documents are very helpful for me. can you please let me know sir. I mean is there any specific website for that Docs. please suggest me

Eswarsaid

materialized view is stored in data base. query out put stored in data base. if u perform any dml operation on matirialized view we need to refresh other wise dml changes are not effected.
MV log is used for refresh mode Fast . if you want to refresh materialized view with refresh mode “fast” .we need to create materialized view log.this process is called incremental refresh.

hi Naresh,
if we perform any dml operation on base table, it is not visible to the mv (materialized view) until we perform refresh operation. In normal refresh(complete refresh or default referesh) opertion new replica of data is stored as mv and older mv is destroyed. It is not effiecient as each time we execute refresh new mv data is created, thats y we use fast refresh. here in fast referesh if we execute any dml statement on base table then oracle db stores that changes in mv view log, and latter that log is used for refresh mv table based on base table. If we dnt have MV log then we need to reexecute the mv query to refresh mv, which will become complete refresh or default refresh…

nageshsaid

I need some information for Oracle database restore point,I am planing to release some critical objects in Oracle 11g database and before we have to set oracle restore point for flashback.Could please provide the information and how to use restore point if face any issues in activity.

1. if u can able to create database and if its working fine, then u dnt require any other checks
2. if root.sh is ran properly, then it will create oratab file in /etc location. chk if that file is there or not

Simlisaid

Ramasaid

i m rama working as Jr oracle dba in Product based company(Bangalore). I know only core dba part. Now i planning to learn ASM,Dataguard,RAC..etc. So is there any fast track course.
Thanks in advance,
Rama

Hi Rama, ASM, dataguard, cloning, upgrade etc topics are included in the DBA course itself where as RAC is a separate course.
we don’t have any fast track batches for DBA, but if possible u can attend online batch. timings are 8:30 to 10 PM. Already a batch started last week and we are in installation phase.

If you wish to attend only adv topics, you may need to wait for another 10-15 days.

Binusaid

1)I am maintaining multiple oracle homes within a single server for different oracle versions(10.2.0.4,11.2.0.2 & 11.2.0.3). There is one Global inventory for all oracle homes. For some reason, the global inventory gets corrupted. Oracle provides us with a method to recreate the global inventory by using “oui –attachhome” option. My question is in order to update the inventory about the physical location of multiple oracle homes, do I need to run this command from every $ORACLE_HOME or a single oracle home will do?

2) Global Inventory could be placed anywhere which can be determined from inventory_loc in oraInst.loc.Can we change the location of local inventory or does local inventory always reside inside $ORACLE_HOME?

1. yes from every oracle home
2. it should be out of oracle_home. usually we place in the location which points in oraInst.loc. if u place in any other location, then u need to change the path in oraInst.loc file

Binusaid

oraInst.loc file points to the the location of the global inventory ($ORACLE_BASE/oraInventory). We can change this location by configuring oraInst.loc file. Can we change the location of Local inventory($ORACLE_HOME/inventory)?

this we will be mentioning in tns entry. for example when look at below two tns entries (bold letters), first one is specified with dedicated connection (which is default) and second entry specifies shared connection

1. Supplemental logging must be enabled on the primary database to support a logical standby database. Because an Oracle Database only logs the columns that were modified, this is not always sufficient to uniquely identify the row that changed and additional (supplemental) information must be put into the stream of redo data. The supplemental information that is added to the redo data helps SQL Apply to correctly identify and maintain tables in the logical standby database.

Dinesh yadavsaid

Dear Pavan,
We are looking for a Oracle DBA cum developer. Who is interested to work at Tanzania and having some exposure in training. Kindly let me know in case you have any friend / known in your knowledge.

1. there will be no impact except on the performance. that too we can’t say until experience it. usually we expect standby to be replica of primary and that is why it is recommended to maintain same config even at hardware level.

2. still you can open it in read only because online redologs are needed only for write purpose.

Meraj Ahmedsaid

Hello Sir
Presently i’m doing B.com ( frst year) , I m very interested in computers fields.
my teachers even say DBA is a very good option .
sir im i eligible to go for a DBA training?
how can i start off? please help me

Hi Meraj, yes you will be eligible for DBA course. Please take training from any good institute on Oracle 11g DBA course. But as you are in first year only, i suggest you can do this in your last year of graduation

Somnathsaid

even though both do the same action (clone a database), the method used is different. in duplicating, we use rman’s duplicate database command to do that, where as in restoring database, we do complete database recovery including spfile. moreover, in duplicate u can use any name for auxiliary database, but when using restore database u need to use same name

Hi..sir please help to me because i know sql/plsql ,but i don’t know oracle-dba and db2 and remaiming concepts ..please suggestion to me .
i will start preparation ,please guidance for which concepts are first startl like (steps wise which one first first start)…
please..please ..sir help to me

Post-check for cluster services setup was unsuccessful on all the nodes.

Configuration assistant “Oracle Cluster Verification Utility” failed
—————————————————————————–
The “/u01/home/oracle/product/10.2.0/crs/cfgtoollogs/configToolFailedCommands” script contains all commands that failed, were skipped or were cancelled. This file may be used to run these configuration assistants outside of OUI. Note that you may have to update this script with passwords (if any) before executing the same.—————————————————————————–Output generated from configuration assistant “Oracle Cluster Verification Utility”:
Command = /u01/home/oracle/product/10.2.0/crs/bin/cluvfy stage -post crsinst -n rac1,rac2
User equivalence check passed for user “oracle”.

Post-check for cluster services setup was unsuccessful on all the nodes.

Configuration assistant “Oracle Cluster Verification Utility” failed
—————————————————————————–
The “/u01/home/oracle/product/10.2.0/crs/cfgtoollogs/configToolFailedCommands” script contains all commands that failed, were skipped or were cancelled. This file may be used to run these configuration assistants outside of OUI. Note that you may have to update this script with passwords (if any) before executing the same.—————————————————————————–
plz help me.

solosaid

Binusaid

There is a new Linux(RHEL5) box and I am planning to clone an Oracle production database(10g on RHEL5) to new Linux(RHEL5) box for testing upgrades, testing pacthes before applying the same on the production database. My question is do I need to match the cloned system environment include hardware, memory & disc allocation space with the production system environment?

Binusaid

Anand Hebsursaid

This is Anand hebsur from karnataka, Presently working as an System admin cum ERP Admin at Dammam (KSA). We are having Oracle 10g database in windows platform. Some things i know in DBA but that much not in detail.

I want to make my career as an DBA. Please guide me and let me know which institute i have to join and whether they will be providing placement or not ?

I am providing online training. If you are interested, you can join next batch which might start in 2nd week of feb.
for dates and other details, keep watching new batches section in my blog http://www.pavandba.com

Harshsaid

I need your guidance in my career path.I’m working as an Oracle DBA but most of my job part is Oracle Performance Tuning and nothing related to cloning,patching and RMAN etc..as there are different teams for these activities.But when I search any job online they asked for all these activities as part of DBA job.So how could I get a job as an Oracle Performance DBA.Please guide me into this.Please let me know ur email-id so that I can share my resume with you,help me into this.

most of the companies will not have separate teams to work as tuning specialist etc. so i feel it is better you can refresh your knowledge on those areas like cloning, patching etc. as you are already working as DBA and know about oracle, it will not take much time

Lakshmansaid

I am an Experienced IT professional like to change my career to Oracle DBA line.I have done my Oracle Certification long back and I am an OCA but couldnt get a breakthru into oracle dba line .It will be great if you can advice or give your thoughts .

usually i suggest to try in your company for a role change. if not you need to look for another opp stating you want to change your technology (ofcourse valid reason should be there to project in interviews)

Lakshmansaid

Thanks for the reply.Its a good suggestion,since I am in a Lead position when I am trying to switch either in my company or other companies they are unable to accept me for Oracle DBA entry /Junior level position because of Grade /Level issues even though I am open for negotiations.

muralisaid

Hi Pavan sir,
This is Murali from hyderabad… i have recently completed my Oracle DBA course, and searching for a job, unfortunately there are no openings for Freshers,my graduation 2010 my qlf mca.but i have doubt fresher jobs is thir or not in dba field .what would you suggest?

parijeetcsaid

This is Parijeet from Pune… i have recently completed my Oracle DBA course, and searching for a job, unfortunately there are no openings for Freshers, so I was thinking of learning something more about database or learn something which will help me getting a job as a developer ( related to database).. i am a fresher and dont have any technical background.. what would you suggest?

parijeetcsaid

Thank you so much for your reply, i have done MBA(Finance) in 2007 and and dont have any technical background currently working with Mphasis (Pune) for international BPO.

When i spoke to Mr Randy Overmyer (Director of IT at ABVI-Goodwill-US) he said :- Oracle’s new customer DB license sales have been fairly flat for 5 years – it’s crazy to try to enter that job market on an entry level. If you paid for an Oracle course out of your own pocket you’ve probably dumped enough money down the drain, You should spend money on moving into more mainstream products such as The largest DB product is MS SQL, then look at the open source DB products – they get more of the start-up market.

was he right? please guide me?

I was thinking of joining Saturn infotch(Pune) / Blink consulting(Pune) for their training cum job program for Oracle application technical consultant, is it a good option?? Please guide me about this position? As I would be investing 1.25 lacs for this course.

Always there will be a competition between different database vendors and I cannot comment on which will have good market and which doesn’t. But i can surely say Oracle will have its share in the market….

coming to course wise, i am not sure about the genunity of those training consultancies, but i feel 1.25 lac is too much costly for oracle course. Personally i don’t suggest to join those kind of places

Can’t connect to X11 window server using ‘10.76.101.161’ as the value of the DISPLAY variable.
10.76.101.161
10.76.101.161
OUI-10025:Unable to start an interactive install session because of the following error:Can’t connect to X11 window server using ‘10.76.101.161’ as the value of the DISPLAY variable. The DISPLAY environment variable should be set to :, where the is usually ‘0.0’.
OUI-10026epending on the Unix Shell, you can use one of the following commands as examples to set the DISPLAY environment variable:
– For csh: % setenv DISPLAY 192.168.1.128:0.0
– For sh, ksh and bash: $ DISPLAY=192.168.1.128:0.0; export DISPLAY
Use the following command to see what shell is being used:
echo $SHELL
Use the following command to view the current DISPLAY environment variable setting:
echo $DISPLAY
– Make sure that client users are authorized to connect to the X Server.
OUI-10027:To enable client users to access the X Server, open an xterm, dtterm or xconsole as the user that started the session and type the following command:
% xhost +
To test that the DISPLAY environment variable is set correctly, run a X11 based program that comes with the native operating system such as ‘xclock’:
%
If you are not able to run xclock successfully, please refer to your PC-X Server or OS vendor for further assistance.
Typical path for ‘xclock’: ‘/usr/X11R6/bin/xclock’

1. the ownership of the views getting created will change. apart from that i don’t think any other impact (ofcourse i never tested this)
2. yes. oracle still work fine without these views. those are only for DBA’s reference

Ganapthisaid

hai sir
in my one of rac database timsmims which is in asm mode, while i changed one parameter it could not start at next restart i would like to start
from pfile each instance it up, after i created spfile from pfile , i shutdown each instance at sql again i issue startup commond, but it unable to start and i did not find any errors or changes in alert log file
please guide me what was problem that database not starting with spfile.

Anurag Kumar Pandeysaid

sir currently working as software engineer to develope web-application since 1 year using open-source technology(php,postgres-sql,mysql)
but i am very much interested in dba since 2008 so sir what the right step to go for dba job ,sir i am very confuse first i join oracle developer Or Oracle dba i am lot of confuse what to do ?
please sir tell me and guide me about right direction to career.

Anuragsaid

recently i completed oracle dba course ,i post resume with 2 yaer exp in oracle dba then i get call from ericsson my designated id programmer and with real exp is 1.7 year so what to do
i ask to someone who told me ,if u get fake exp then u debarred from company and they blacklisted
u can give me some guide

sausaid

Hope you are in good health. I have few questions regarding different topics.

1.What will happen if I run catpatch.sql not in STARTUP UPGRADE mode?

2.I have two ORACLE_HOME. One for 10.2.0.1 and another for 10.2.0.4 (After applying patch set). 10.2.0.4 ORACLE_HOME has no database. I am planning to create few databases under 10.2.0.4 ORACLE_HOME. Is it necessary, to run catpatch each time after creating a new database?

3.Why oratab and oraInst.loc files are created under root user? If I change their ownership to oracle user, will it cause any problem?

1. Ongoing transaction will continue without any issue because DBWR will still write the dirty blocks during hotbackup. Only datafile header will be freezed.
2. I have not got your question clearly. can you explain a little bit plz?
3. we can take, but that is not a hot backup because you are making tablespace offline which is nothing but downtime for users who are accessing it. so its not a preferred way

Jeremiah Mosessaid

I knew you were writing a blog but today is the first time i saw you website. Been a long way since you were sitting next to me in JCE for 3 years you hardly spoke 1 full sentence a day 😀 now here you writing so much. Keep it up m8 !

1. If log switch not happening, that means redolog file is not full. so in this case, there is nothing to worry until users complain about some issue. in that case we can check in alert log file
2. if log switch occurs frequently, then it would cause burden on the DBWR and CKPT processes as they need to write the data to datafiles and controlfiles. this may have impact on performance of the db

Yes, we would be loosing data if the file is corrupted and log switch is not yet happened. But to avoid that, we are going to have multiplexing of redolog files into different disks. In that case, even if one member is corrupted, other member still will be there to help out.

that is why one more reason, why oracle will recommend to have less size for redolog files (to avoid corruptions and go for frequent log switches)

ARCHIVE_LAG_TARGET limits the amount of data that can be lost and effectively increases the availability of the standby database by forcing a log switch after the specified amount of time.

A 0 value disables this feature.Values larger than 7200 seconds are not of much use in maintaining a reasonable lag in the standby database. The typical, or recommended value is 1800 (30 minutes). Extremely low values can result in frequent log switches, which could degrade performance; such values can also make the archiver process too busy to archive the continuously generated logs.

Saybasaid

1.”Because of DBA error (before we started obviously!), an important database was created with a 2k block size, and as it is physically on a SAN with an 8k write chunk, we are dealing with unnecessary performance implications.

Please prepare a detailed, technical (how you plan to do it) _and_ logistical (what you plan to do, in what order, when and how long you think it would take) project plan for our conversion of our database from a 2k block size to an 8k block size.

For estimation purposes, a full database export of this database takes 12 hours, and we estimate a full database import would take about two to three times as long because of index re-creation time. Assume our cost of down-time is $10,000 per hour and as a result it’s very important to come up with methods of reducing downtime. Needless to say, 36 hours of downtime, at a cost of $360,000 is completely out of the question. Obviously, with such a high cost of downtime, we have properly resourced the environment in terms of disk space on the SAN. As well, we have a spare server available if necessary to further reduce the downtime. ”

We require a PL/SQL program to be written that will take the data in the TSOURCE table and 1. insert rows not currently in the TDEST table and 2. update TDEST rows with a matching KEYVAL value with the data in TSOURCE.

Please write the program so as to maximize overall performance and efficiency.

Be sure to carefully rollback your work after every attempt at running your program so that you have a chance to try again!

Bonus points if you can measure for us exactly how many buffer gets your pl/sql program uses using tkprof.

Question 4:
4.Please install statspack/perfstat to take performance snapshots every 15 minutes on the 15 minutes except during our batch window of midnight until 7am, during which time we would like performance snapshots taken every 2 hours at the top of the hour (midnight, 2, 4 and 6).

I am really interested in the scheduling part not the content of the perfstat reports themselves

As you have not mentioned your OS version and database version, I am assuming it as 10.2.0.4 and OS as some unix flavour.

1. If your database is 9i or above, you can use non-default block size tablespaces, in which case we don’t need to do conversion at all. All we need to do is just move the tables to new non-default block size tablespaces which would not take huge downtime.

as you mentioned export is taking 12 hours, I would like to know if it is traditional export you are talking about or datapump? and what is the database size? If it is traditional export, then we need to check the time taking with datapump also. Surely datapump will run faster than normal export with the help of parallel and other options.

Also, we can do export/import of tables and other objects skipping indexes. later after import, we can create indexes. this will reduce the import time.

2. I have not understood your 2nd question. please explain me in details

3. I am not a perfect programmer in PL/SQL, so any developer can help you on this

4. If your database is 10g version, then you don’t need to use statspack instead you can use AWR report which will run every one hour automatically
If it is statspack, you can try scheduling through dbms_jobs or using spauto.sql script as perfstat user

please do mention versions so that I can help with some accurate answers

Hi Pavan,
This is good to see your dba profile and hope to learn from you in near future. I am from karachi pakistan and completed my training of OCP from an recognized institute and about to attempt my first paper of SQL in coming month. These days m practicing oracle 10g on TEST environment in a pharmaceutical company.
Till now i practiced DDL, DML, TCL and DCL statements but the problem that i am having is m week in concepts of table space, pfile, spfile, user space, schema, constraints, instance, data file etc. operating is fine i can operate but when it comes to the concepts of technical brief i am dumb. How can i improve and can clear my basic concepts apart from searching on google and what line of action should i follow to practice in easy manner to get improve quickly and to be able to operate maximum amount of data in short period of time.
Best Regards,
Zeeshan Girach!

Ahmedsaid

Just wanted to ask you about the difference between an oracle client installation and oracle software installation.
If i install oracle client then i dont think it will have database in it. I guess we need to connect it to a different server to get the access.

Could you please tell me the difference in installing oracle client and oracle software itself and also where and why do we use oracle client?

Thanks in advance for your help. you are doing a very nice job. God Bless you

Hi Ahmed, yes. your view regarding client and database software is correct. client s/w will not have database, so if we install client s/w, we cannot create database.

Oracle database software will be used in DB server to create new databases (as we already know).

Suppose we have a java application on server A (all java code will be put in this server). We have created database on another server B (here we will install database software and created database). Now if this java application (running on A) need to connect o database (running on B), we need to install oracle client s/w on A.

Installing client s/w is almost like database s/w. after installation, we need to configure TNSNAMES.ORA (in our case on server A) and that will connect to database (in our case B) using listener which runs on B.

saybasaid

Thanks for the replay. Let me explain you more about my professional journey,,basically I have been in this IT industry as an oracle DBA from last 4 years.But unfortunatly the account where I used to work for long time does not gave me an oppertunity to explore my skills at all areas of oracle DBA coz of no work. As a result of which when I come out from the company my knowledge level was zero (fit for nothing).

Again I struggled and able to find DBA job in another company,,but the actual thing is iam not interested in technical work coz,,,,

1.Iam not good at logical thinking 2. Iam very very poor at programming whetehr it is shell or PL/SQL or Database 3.Iam reaching to 40yrs
Based on the above constraints please suggest me what I have to do to carry out my journey in an IT industry ?

Basically ,,Iam an ITIL certified with 8+yrs of experience in IT industry with relavent as 4yrs in Oracle DBA.

If you have perfect hands on with DBA topics (as you said you didn’t got much chance to work on all concepts in your earlier Oracle exp), you can get the job with right kind of project within no time.

Moreover DBA will not have any technical work, so you need not to worry about programming etc. So, I suggest to get home practise on RMAN, Cloning, Upgradation, Patching, Dataguard, ASM etc and you will find the best job for you

Balakrushnasaid

Hi pavan Sir
I have dout in older export and import utility
I have taken One dump file of table and Consistent=N before that I have updated that table but I am not given the committed.
While I export to another schema the dump file contains the uncommitted data also.
so i have dout in what does Consistent parameter will do in oracle older export ?
I think that consistent parameter is for the export dump file will take only previous image block am I wright or wrong ?
if I am wrong please explain me ?

Saybasaid

I have gone through your blog first time looks like very interesting. My question is iam not at all good at Programming side even writing/understanding a small database script or RMAN script will be a tough task for me.I wanted to pursue my career in SQL SERVER DBA which I came to know a bit easy to learn and no Programming knowledge required.

Do we really need any programming knowledge or .net or do we need to read scripts used where we need to modify the production server issues in SQL SERVER DBA?

Currently I want to switch my career from oracle DBA To Peoplesoft DBA or Sql Server DBA coz I find it difficult coz of my zero PROGRAMMING KNOWLEDGE.

If you can put little effort in learning shell script, its easy only. So I recommend to try that.

As you said, SQL Server DBA is quite a bit easy as administration of it can be done using GUI tool. But, may be there also you need some logical thinking (I cannot call it as programming as programming means wirting code for front end applications). We need to write some scripts there also.

As I am not having pretty much idea about SQL Server DBA, I cannot comment much on that…

ravi kumar chowdarysaid

Hi sir, this is ravi kumar..i learn core dba on linux plotform (10g version) .right now my organization in windows plotform (9i version).what is the major diffrences between two things.how to work on windows plotform ??

the basic administration activites will remain same even in windows. But some activities which need OS help will change. for example, to start the database you need to make sure even service is running in services.msc file. etc

Create a test database on your home machine (if possible) and get hands on. If any doubts, plz contact me

ravi kumarsaid

Hi pavan sir ,
i am checking in that website,in that there is no information regarding oracle dba(windows plotform) comands..i don’t have sufficient knowledge on windows plotform.i don’t have how to work on windows plotform.

and one more thing sir.in single system (linux) we install oracle 10g client & server in same machine.

I hope it is SQl Server DBA. in such case, it would be similar to Oracle DBA. SQL Server is the database from Microsoft. only difference is in the architecture and way of doing administration. As it is more GUI based, you will feel it is easy to manage.

But the New server which is to be added will not support this “Sun Solaris 10 version 5/9 u7” os (including version). T3-1 server supports these os only: OS solaris 11 , solaris 10 10/9 and solaris 10 9/10.

My doubt here is, In this case shall we add this node to the existing RAC??? If Yes, My problem was solved, If not possible – Is there any possible way to add this node.???

Note : We have a one more T3-1 server which is in nfs server cluster( this is os cluster). In least cases we can remove this from nfs cluster and we can make a new 2 node RAC for DB using 2 T3-1 servers.Then we have to migrate the existing rac which is on only one T5240server to the newly made RAC of 2 T3-1 servers. In this case how much down time for DB is needed…?? Please help me on this.

As far as I know, always it is better to have RAC environment with same OS and same hardware. As you said the new server can support only solaris 11, then I assume it is not possible to have second node on that server.

So, the possible way is to remove that OS cluster server and make that as 2 node RAC and migrate the data. In this case, you may not need much downtime as you can do RMAN cloning from single machine to RAC machine.

Steps for cloning single to RAC machine is available in a note in metalink. you can browse for that.

Above all, I suggest to raise SR with Oracle support and do this task (since it’s production) as they can best tell the easiest way….

tejasaid

tejasaid

Good Morning.
I have seen your active participation on this and it is very pleasure to me on this.
I have a very big task infront of me in coming days. Will you please help me on this.

Task : We have a 2 node rac now and only one node is up and running now since last 1 year. ( The other one was of hardware failure ).
Now 2 new DBservers came for our project and I have to setup rac on these two new servers and after setting up of this new rac environment successfully, then i have to move the old rac ( ie.. running on only one node) to the new rac.

tangysaid

Have you ever tried using convert datafile , convert tablespace and convert database commands in rman which might help in migrating the database faster. Datapumps definitely help you when your database size is manageble.

vikas dharmadhikarisaid

Snapshot is the name oracle used till 8i and from 9i we are calling it as materialized view. difference is MV will use MV log mechanism in order to refresh data. for more details go throguh oracle documentation

Dilipsaid

I am Dilip from chennai, working as Oracle apps DBA having about 1.8 yrs of experience.. I agree its easy way to get placed in campus but tough to landup with fresher jobs as DBA. Your post brings lots of hope and confidence for those want to get sneak through doors of database administrators.. 🙂
I join here today to get some exposure, knowledge sharing and tips from you…All the very best..

vemurisaid

Synchronous IO means OS will wait for one writing task to complete before it starts another. where as asynchronous IO will start another process while first one is in progress. When asynchronous IO is enabled, either DBWR or LGWR can write data fastly which avoids IO bottlenecks

vikas dharmadhikarisaid

tangysaid

Excellent question, It definitely retrieves information from X$tables, it could be proved by running explain plan on the query. But where are these tables stored and retained in no mount state. I hope it should be definitely in the SGA.

chandrashekar Gsaid

may be your database is residing on AIX, but you are trying to take export backup on windows. (i can see export cmd from C: prompt in your query)
so it might be a bug. or also check what is the value that was set to UTL_FILE parameter in your database pfile or spfile and you can change that to ‘*’ which means you can access any location for your database. for more info on UTL_FILE, refer to google

sanjay pogulsaid

My name is Sanjay. Sir, I am an OCP Certified DBA. I am looking for the job of DBA as fresher. Being a fresher & non-IT background, I am not getting any interview calls. I don’t know what to do. Sir, can you guide me please?

getting a fresher DBA job is tough task, but never loose hope and keep on trying. Donno if you had done this already, if not post your cv to all job sites and daily login and apply for the jobs suitable to you….

sumansaid

hi sir,
I got an error ora 01157 error while changing the locations of all my files to a new mount point .
I have moved all the files to new location and edited the pfile .Now when i started my database it just went to mount stage and not able to open showing the above error.I am using 10g database.Are there any other locations to change.
Please give me explanation regarding this scenario

If not plz do it. you edited pfile only for controlfiles. but to make oracle understand about datafiles and redologfiles, you need to run above command for each and every file. then you can open your database

1. AWR itself will take snaps every 1 hour. so there is no need again to run it for every one hour. The only script we use to generate report is ORACLE_HOME/rdbms/admin/awrrpt.sql
2. I am not sure why you need to shift archives. when you have dataguard env, archives will get shipped automatically… can you explain a bit more on this?

Chandrashekarsaid

If archives are not shifting to standby due to n/w problems some other problems,in that time i will disable the DG at Standby side and shift the archives,i know the process of that one but if it is diffrent env what is the procedure…

I am not sure why you are doing it manually? in case if you have network problem, oracle will use the help of FAL (fetch archive log) process to take care of this. so, no need to manually do the things. to know more about FAL, read oracle documentation

yes it is directly to run that sql statement. there is no other parameter to set for this.
what happens in background?
1. when block change tracking is enabled, when ever a block is change the corresponding block id is written to a file.
2. when RMAN backup is initiated, rman process direcly reads this file and will start taking backup of those blocks.
this helps in reducing the backup time as it will not compare block scn with last backup

Nitan Jandialsaid

Actually it is called ‘cron daemon’. Cron is an automatic task machine. You will use it on your Unix or Linux operating systems for doing some tasks at specific intervals with out your intervention every time. You set the clock and forget. The cron daemon runs the work for you.

What are the components of cron?

=Field====Value====Description=
minute====00-59====exact minute the cron executes
hour======00-23====hour of the day the cron executes(0 means midnight)
day=======01-31====day of the month the cron executes
month=====01-12====month of the year the cron executes
weekday===00-06====day of the week the cron executes(Sunday = 0, Monday = 1, Tuesday = 2, and so forth)
command===Special==complete sequence of commands to execute
Examples how to set cron:
If you have installed a cgi script in your cgi-bin directory called members.cgi and wanted to run this program each night as 11.30 PM as in above example.

You would setup the following crontab line:

30 23 * * * /home/username/www/cgi-bin/members.cgi

30–represents the minute of cron work
23–represents the hour of the day
The * represent every day, month, and weekday.

If you want to set the cron job every sunday at midnight 11.30 PM then it would be like:

30 23 * * 0 /home/username/www/cgi-bin/members.cgi
0–represents the Sunday.
If you want the cron job to run at 1:00 and 2:00 A.M then you can set it like:

* 1,2 * * * /home/username/www/cgi-bin/members.cgi

This runs your cron at 1 and 2 A.M every day, every month and every week.

If you want to run the above task only from Monday to Friday then set it like:

* 1,2 * * 1-5 /home/username/www/cgi-bin/members.cgi
Setting up the cron:
You will be in one of these situations.

ONE:This would be your first cron tab function.

SECOND:You already have cron tab file on your server running one or more cron functions for you.

it is a error which oracle throws to a select user if it doesn’t find either new data in datafile and old data in undo. it is famously called as ORA-1555 as you know. this occurs because of less undo tbs size or frequent commits etc

Unmeshsaid

I have gone through your blog and your knowledge on DBA is exceptionally apar from other DBA bloggers.
Like you i am also very much interested in pursuing my career in DBA field, but couldn’t find any opportunities.
Can you please help me out.

Unmeshsaid

chandrashekarsaid

this is chandrashekar,i have done oracle course recentely last 3months back after that i attend interviews i stragulled there bt finally i got the gob in WIPRO last week only.But i dont have practical knowledge,can u guide me how to gain the practical part,enviroment of the realtime really my body is sheking whenver i think that company….

srinivassaid

Hi pavan sir
i am proud to say that i am your student(kanna technologies),i am MCA 2010 passed out.I hav good percentages(above 65) but i hav less confident on me to get a job,so that i stayed in my home last one yr,now i returned to hyderabad now i really got confident that i can also get job and i am thinking that i choosen the best inst and best faculty to learn dba course in hyderabad.

yes being confident will always helps us in acheiving the goals. happy that atlast you decided your path. WISH U ALL THE VERY BEST ! I am here always to help you in case your doubts are within my knowledge level 🙂

vksaid

I am a 2004 batch mca graduate(1 paper pending for a long time) unfortunately caught into the BPO domain for more than 3 yrs and I always wanted to become an dba. Now i have quit the job and finished MCTS certification in sql server to become sql dba but I am searching for more than 6 mths and I am unable to land in a job. Why there is no opening for freshers in the DBA field even though i have knowledge and I clear most of the interviews. Why do co’s expect with 2-3 yr exp. only? Please let me know what abt the chances of getting a job in hyd/banglore? I am almost fed up ! Sometimes i feel iam gonna fall into bpo again which i dont like at all. Whats the use of wkg without satisfaction?

Hi Vinodh, you are definetly right. There is no reason for working if we are not satisfied. reg your question, why fresher DBA jobs are less? as always databases are critical to handle and contains important data, clients will look for an exp professional rather than a fresher(their feeling is unexp person may not take right decision at right time which is sometimes true).

But we do still have fresher DBA openings and it takes is time. Even I also started my career as fresher after struggling alot. I am send mail to you on what can be done next….plz check

Nishasaid

I am new to oracle,i have installed Oracle Database11g R2,Oracle Grid 11g R2
and these two i used Vmware and Oracle Linux 5.5.
Now i want to install Data Guard for my Rac so i read so many
notes and google for it but really i did not feel bad in any other
configuration like i felt bad in Data Guard configurations
so still i am not able to configure Data Guard if anyone plz wants to help me
contact me here is the email ID.nn9887@gmail.com

bantisaid

I am really impressed to see your blog.Its really a good source of sharing knowledge.I did Oracle DBA course and I want to know that what are the common errors facing by you people in real time scenarios.
Plz send me some real and common problems related space management,user management,backup,recovery,data pump(export/import),Cloning,Performance Tuning,Upgardation,Migration with ORA-XXXX and its solutions.

Nageshsaid

Hi pavan,
I ve done oracle DBA 10G nd OCP certified,presently im working in govt sector from 10months but only involved in monitoring database so i looking fr a change in job .. at present no openings fr freshers SO can u nplz suggest me wat to do….

when doing any upgradation, it is must that you follow oracle upgrade guide instead of going or depending on regular ones which you can get from many websites.
you can download 10.2.0.4 patchset from my oracle support (metalink) which contains readme.html file. this file will give details on how to upgrade in both the ways you asked for.

Munnasaid

venkata santhosh kumar Bsaid

This is santhosh, a 2009 B.Tech pass out under the discipline of computers. I have done DBA course perfectly. So, can i have opportunity for a break in my career. I think you can guide me for a best career.

hi santhosh, i can say DBA fresher jobs will be less but not impossible ! so plz keep on trying and post your cv in every job site you come across. also if any openings that i would know, surely i will be posting it here. so keep watching my blog

first we need to check in alert log if there is any error for why data is not been written to file. also need to check whther the datafile added correctly. based on that we can take action. i had never seen such thing till now, but if get it will be quite interesting !

Your blog is becoming a lot day by day (as expected)…its really helping a lot. I posted a comment on your performance tuning related category regarding sessions and process.

Recently i ran a script aflobbld.sql which is to create fnd_lobs_ctx index in Oracle Apps 11i…the script ran for more than 24 hours…so we killed the session using “alter system kill session..” but when we tried to run the same script again this script is waiting for the “library cache lock” held by previous session(which is already marked as killed)…how to get this lock cleared with out bouncing?

Library cache locks will occur when the same object is being accessed by some one else. have you run this script in non-peak hours where there is no user activity? If so, there could be some sessions still holding the lock which you can find using below query

prathapsaid

this is prathap. i would like to do the job in DBA side but i didnt have any experience in that field but can u give any suggestion me for real time scenarios. now i am applying to the oracle dba positions face interviews but ilittle bit difficult to me facing the questions. ac

anandsaid

this is anand ,i have seen ur blog this is really amazing .now am being trained as an oracle apps dba in hyd , i dnt have any experience till now but i want to get recruited as a real time expert for abt 3+ , do u have any suggestions for me plz .

am gud buddy and expecting same with you. thanks for your words, but i am still trying to understand about oracle. for you, my schedule is always vacant dude…but donno if u can able to meet others than my sister :-))

hi pavan this is srinivas from kodada, just i see ur bd (bio data)..i complted my btech(cse)in 2007 from sana engg college,kodada.after that i worked for jkc/ieg,hyderabad upto 2 yrs. i have no job satisfaction in that .. i resigned fir my duties in march-2010.i decided to mould my career in ORACLE DBA side ..i learned the things in hyderabad .. recently i came to bangalore for job trials…

I eill keep in touch with you for the improvement of myself as an ora-dba.

Munnasaid

Is there any way to list the objects that statistics are not collected and not good enough. If so what are the methods. (For a schema/for a object / for a database)

Please do not mention about last_analyzed as everybody knows about it.

Ans :
” dba_tab_statistics & dba_ind_statistics
These views have a column named STALE_STATS, which automatically gets updated to ‘YES’ for a table or index if more than 10% of its data is modified since its last update-stats.
— To know a list of objects with stale stats at the schema level,
we can call the package
DBMS_STATS.GATHER_SCHEMA_STATS with the parameter options=>’LIST STALE’
DBMS_STATS.GATHER_DATABASE_STATS with the parameter options=>’LIST STALE’
This will internally fetch its data from *_tab_modifications views
In 10g when the value of statistics_level is ‘typical’ or ‘all’ table monitoring is automatically enabled. ”

And also look at round(sample_size/num_rows*100,1) AS “%” from dba_tables which gives a percentage of collection for an object.

srikanth.Msaid

hi sir,
i have seen u r blog it’s great.
u r guidence is good b’cause i know without guidence its hard to survive in this competitive market(once I failed without guidence).
Let me hope this time i will get the right guidence from u to get job.Anyways u r blog is very great.
………srikanth.M

1. you will be getting ora-1555 in exp when using consistent=y parameter. the possible solution is to increase either undo tablespace size or undo retention size. if you are using 10g, then oracle will have automatic undo retention by which your datapump will not get these problems.

2. buffer will be decided on 10*avg row length formula for that table or schema.
you can get avg row length for a table or schema from dba_tables.
ex: if you get avg row lengthas 1284, then buffer = 10*1284.
but in general we will give some value like 50000 or 100000 to it, instead of calculating with formula.

pintusaid

Hi Pavan,
I have been working as DBA for last 4 yrs (mainly as dev dba). So didn’t get chance to work on third party tools for backup and recovery. I’m interested into learning somewhat about unix administration and third party tools like TSN, veritas etc. Please let me know if you can help me with the same.

Hi Rakesh, am not expert in oracle DB. i just got expert status in forums and that too i am still surprised how i got that status. So, i am not certainly in level of teaching and i can share the min knowledge through my blog. Thanks for your kind words….

always great people say this. I am really surprised to see how you can be so humble. I am just Expert, but you are Guru !!! Thanks for accepting and i will add your id to my gmail. But don’t worry i will not ping you for asking doubts. Nice to meet you again Sir !

gowthamsaid

I am gowtham…I read ur blog for the first time and i got a little hope about my career…as i am in the same position of yours(in 2004-2005).I am a 2008 passed out and still in search of a job…I have completed courses in oracle dba and peoplesoft dba.As you are working on oracle can you help me out in anyway with my career…I will thankful to you if you can guide me….

Satishsaid

kishoresaid

fixed size means
A portion of the SGA contains general information about the state of the database and the instance, which the background processes need to access; this is called the Fixed SGA. No user data is stored here. The SGA also includes information communicated between processes, such as locking information

Sateesh Kumar.P (DBA)said

Your Blog is very nice, and the colors u choosen is also nice.
I read your “About Me” , it is good, but you forgot your school name (T.K.S.V) that’s not good, re-collect your school name, and post it also.
Satishp.

SUNIL SURYADEVARAsaid

Hi Pavan,
Good to see your blog.I had looked into some of the posts uploaded by you.They are very useful & nice.What a person you are,thank you for your kindness.Being in a position, and sharing all the knowledge with others ,with latest updates.
According to my experience and thinking one should not be a egoist or proud regarding subject or knowledge irrespective of their age and position.We should share with one another,so that they would also be upto the levels.
Hope all of our group people & friends would be benefited with this blog.