About me
Hi Everyone,
This is Pavan Kumar
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 member in Oracle Forums and got Expert status recently. Right now, I am working as Operations Manager for Oracle DBA 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.
Here are some of my certifications
Vishnuvardhan Reddy said
Hi Pvan,
Can we have multiple ASM instances on a single server?
Thanks,
Vishnuvardhan Reddy Pandiri
Pavan DBA said
yes we can have
Ahmed said
Hi Pavan,
Just wanted to ask you how many days of archives do we need to keep.
We take cold backup everyday.
Thanks
Ahmed
Pavan DBA said
if you are taking cold backup, then there is no necessity to store archives. after the cold backup, you can delete all previous archives.
Ahmed said
Thanks for your reply…
You are really awesome and helpful.
Thanks
Ahmed
Pavan DBA said
thanks for your kind words
Binu said
1) When to use 10046 Trace?
2) What is the purpose of the “Row Source operation” section in TKPROF report?
Thanks and Regards,
Binu
Pavan DBA said
10046 is nicely explained here – http://www.oracle-base.com/articles/10g/sql-trace-10046-trcsess-and-tkprof-10g.php
chandu said
Pavan gi could you please provide grid software and installation document on linux os.
Pavan DBA said
here is the link for installation guide – http://docs.oracle.com/cd/E11882_01/install.112/e22489/toc.htm
link from where you can download s/w – http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-linuxsoft-085393.html
chandu said
Sir i got one error while creating the 11g database using dbca on solaries env,Error as”ORA-12560:TNS:Protocol adapter error”
Pls help us,little bit urgrent.
Pavan DBA said
please check the log file of dbca to understand more about the error
sau said
Hi Pavan,
Hope you are in good health. Today i have few queries regarding Standby Redo Logs.
1) We need standby redo log on Primary database for switchover.Right? How many
Standby Redo Log group will be required on the Primary?
2) The size of the current standby redo log files must exactly match the size of the
current primary database online redo log files.What is the exact reason behind
this?
3) Standby Redo Logs require multiplexing?
With Regards
Sau
Pavan DBA said
1. We need to have same no.of redo log groups as primary. Some DBA’s will follow n+1 formula i.e if primary is having 3 groups, they will create 4 standby groups. but even 3 is also sufficient
2. yes it should match, because standby is replication of primary
3. Yes it require. because after switchover or failover, standby will be primary
Anurag Kumar Pandey said
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.
about education–
1 – B.tech. in computer science & engineering 2010 with 73%
2- Diploma in Electronics Engineering 2007 with 76%
my email-id anuragkumarjoy@gmail.com
Pavan DBA said
You can join Oracle dba. Don’t get confused about it. It will be right step for sure…
sau said
Hi Pavan
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?
4.Is quiescing/suspending a database before backup an option?
I look forward to receiving your reply. Thanks in advance
Sau
Pavan DBA said
1. First of all I doubt whether oracle will accept to run it. Even if it is, proper information may not be recorded in the data dictionary and oracle may not apply patchset correctly
2. not required
3. it won’t be any problem
4. nope. we should not use them before taking backup. “begin backup” is the only option to use
sau said
Thanks Pavan
Sau said
Hiii Pavan
Hope you are in good health. Thank you very much for answering my questions &
clearing my doubts.
Today i have few queries regarding User Managed Hot Backup
1.After executing ALTER TABLESPACE … BEGIN BACKUP, What will happen to the
ongoing transaction related to that particular tablespace?
2.What influences the time taken for the ‘alter tablespace begin backup’
Statement?
3.Can we take User Managed Hot Backup using “ALTER TABLESPACE OFFLINE”
command?
Hope to hear from you soon.Thanks in advance
Sau
Pavan DBA said
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
Sau said
Hiiii Pavan
Thanks for your reply.
Issuing “alter tablespace begin backup” across every tablespace is taking about 10-15 minutes.Why is it taking so much time? (This question is related to
my 2nd question)
For example:
SQL> alter tablespace users begin backup;
After waiting for 10-15 minutes, it returns ” Tablespace altered.”
SQL>
Regards
Sau
Pavan DBA said
may be huge transactions are hitting the database which is making oracle slow down in making datafile header to go for freeze state
Sau said
Thanks for your efforts to share your knowledge and experience with Oracle professionals . Please keep doing that
Pavan DBA said
thank you for the comments and yes i will be doing this….
Jeremiah Moses said
Hey Pavan,
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 !
Pavan DBA said
Hey Jere,
Thanks macha…
yes man… i used to be very shy and confined at that time. But grown up a bit now
Good to see your comments in my blog. I am following you on FB even though I nvr intereacted directly….
Will start be in touch with you….
sudheer said
Hi pavan anna,,
Struggling for getting job for the post of ORACLE DBA as a fresher.
Please send or post details for fresher.
Could you let me know the vacancies for fresher.
Pavan DBA said
hi sudheer, any job vacancies, i would be posting in my blog. so keep on watching it. also, recently i posted some fresher openings in CTS. if you haven’t seen that yet, please apply immediately.
Sau said
Hiiii Pavan
Hope you are in good health. I have tow queries regarding Log Switch.
1) If a Log Switch doesn’t happen for a long time, what will happen?
2) If Log switches happen frequently, What will happen?
With Regards
Sau
Pavan DBA said
Hi Sau, thanks. I am doing good and hope the same with you.
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
Sau said
Hiiii Pavan,
Thanx for your reply.
I have read somewhere that if a log switch doesn’t occur for a long time then there
may be chances of losing data when the redo log file get corrupt. Is it true?
Is there any impact for standby DB, if the primary databases have
ARCHIVE_LAG_TARGET parameter enabled?
With regards
Sau
Pavan DBA said
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.
Thiru said
Hi Pavan,
I just seen your “about author” and surprised to see the name Jerusalem over there..Are you graduated from Jerusalem College of Engg,Chennai?
I too completed my degree over there and now working as a Oracle (Apps) DBA in an MNC.
Regards,
Thiru
Pavan DBA said
Hi Thiru, yes I am graduated from JCE in the year 2004. Good to hear that you are also from same college
Sayba said
Pavan,
Kindly provide an accurate answers to the following questions pls,.
Question 1:
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. ”
Question 2:
2.Use the link following this question to download the interview.dmp
Using the INTERVIEW schema:
Table USERS
columns:
User_id (PK) (generated via a sequence)
Email
Gender
Age
Name
table SUBSCRIPTIONS
columns:
SUbscription_id (PK) (generated via a sequence)
user_id (UK) (FK from users)
subscription_type (UK)
active_indicator
table TRANSACTIONS
columns:
subscription_id (PK) (FK from subscriptions)
action (PK)
timestamp (PK)
sample data:
USERS
user_id email gender age name
1 a@a.com m 30 rob
2 a@a.com m 31 robert
3 b@b.com f 18 lucie
4 b@b.com f 22 lulu
5 c@c.com m 10 kim
6 c@c.com f 18 kim
7 c@c.com f 08 kim
8 d@d.com f 18 JJ
9 d@d.com m 22 Jay
10 e@e.com f 88 Bill
11 e@e.com f 88 Will
12 e@e.com f 60 Will
13 f@f.com m 70 George
subscriptions
subscription_id user_id subsciption_type active_indicator
1 2 Magazine Yes
2 3 Music CD No
3 3 Magazine Yes
4 3 Video Yes
5 8 Magazine Yes
6 9 Video Yes
7 10 Magazine No
8 13 Magazine yes
transactions
subscription_id action timestamp
1 Renewal 2002-sep-10
2 Renewal 2002-Jan-01
2 Cancellation 2002-Feb-01
3 Renewal 2002-Aug-20
4 Renewal 2002-Aug-01
4 Renewal 2002-Sep-01
5 Renewal 2002-Aug-01
6 Renewal 2001-Sep-01
7 Renewal 2002-Sep-01
7 Cancellation 2002-Sep-10
Type of business:
We are a subscription company where people signup to received products
like CDs, books, magazines, etc. We always try to market new
subscriptions to our existing client base, but having millions of
customers of which many use the same email address we like to send are
promotional email’s once.
QUERY:
Some assumptions will need to be made. Please list any such assumptions.
Generate a list of unique email addresses with the latest name, gender
and age for a user with that email
The selection criteria limit the list to users which never subscribed to
anything; or;
users with inactive subscriptions; or;
users with active subscriptions that renewed between Sep 1st and sep
30th of any year
answer should be:
a@a.com m 31 robert
b@b.com f 22 lulu
c@c.com f 08 kim
d@d.com m 22 Jay
e@e.com f 60 Will
Question 3:
3.using the INTERVIEW SCHEMA
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
Pavan DBA said
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
Zeeshan Girach said
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!
Pavan DBA said
Hi Zeeshan, you can read a very good book called “expert 11g administration” by sam.r.alapati which is easiest way to learn dba concepts
vikas said
Hi sir,
i am trying to install oracle 10g on RHEL 6 but i could not do it
so will you share some document to install oracle on RHEL 6.
Pavan DBA said
hi vikas, i think oracle 10g is not supported on rhel 6. so please try with 11g r2
Ahmed said
Hi Pavan,
This is ahmed, I have heard lot about you from my friends.
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
Pavan DBA said
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.
hope this gives u some idea
Ahmed said
Thanks for ur quick reply. I Understood now. Appreciate your help.
Pavan DBA said
you are welcome…
chandu said
Hi Sir,
Good Day,
What is the use of v$timezone_file,If it is lessthan 4 what we have to do?Bcz presentely i am working in patching & upgradation team.
How to resolve this one.
Appreciate your help.
Regards,
Chandu
Pavan DBA said
this link will give some idea about that
http://lifedba.wordpress.com/2009/12/29/timezone-file-version-warning-when-upgrading-oracle-database-from-10-2-0-1-to-11-1-0-7/
Sayba said
Pavan,
Iam still waiting for the replay to my question which I posted you yeaterday.
Pavan DBA said
Hi, Sorry I am on vacation since last 3 days. so couldn’t reply you. I replied it now….
suganya said
Hi Pavan,
I have a requirement to convert 9i database to 11g database with zero downtime.
Could you let me know whether this is possible? if so, can you elaborate the steps.
Regards
Suganya.S
Pavan DBA said
Hi Suganya,
I couldn’t understand what you mean by convert? do you mean upgrade or migration?
upgrade means moving to 11g on the same server
migration means moving to 11g on diff server.
If you are doing upgrade then zero down time is not possible. But if you are doing migration, then it is possible.
please elaborate your requirement so that I can give better suggestion and steps to do that
sayba said
Pavan,
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.
Kindly suggest me what I have to do to proceed further ?
Pavan DBA said
Hi,
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
Balakrushna said
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 ?
Pavan DBA said
Hi,
Please go through these links
http://docs.oracle.com/cd/B10501_01/server.920/a96652/ch01.htm#1005545
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:21545894805637
Sayba said
Hi Pavan,
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.
Kindly advice.
Pavan DBA said
Hi,
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 chowdary said
Hi sir, i am ravi kumar chow dary.u r student.i want know about oracle server & oracle client..what is the diffrence between oracle server & client.
right now i installed oracle client in my PC.i want install oracle server in my PC
plese tell me
Pavan DBA said
Hi Ravi,
Oracle server is nothing but database. Client is a software which we will use from applications to connect to database (when database is residing on another machine than application server)
ravi kumar chowdary said
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 ??
tell me sir.
Pavan DBA said
Hi Ravi,
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 kumar chowdary said
Hi sir,
This is ravi kumar.i want know windows plotform dba comands like (to take cold backup & hardback).my oraganization is windows plotform.
i know Linux plotform comands.plese give me sir.
Pavan DBA said
see this link – http://www.computerhope.com/msdos.htm
ravi kumar said
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.
tell me
Pavan DBA said
hi ravi, for windows we don’t have specific commands related to oracle. all oracle commands in linux will work as it is in windows also (for eg lsnrctl, tnsping, exp, imp etc).
regarding your question, yes we can install, but there is no use because server software already will have all the files related to client software
Ravi kumar said
Hi sir,
i am ravi kuamr.u r ex-student.i want know windows plotform oracle dba comands..i learn dba linux plotform comands.i don’t know how to took export &import database.plese send me sir
Pavan DBA said
Hi Ravi, as i mentioned in earlier comment, commands are same in windows or linux. exp, imp etc are oracle commands, so on any OS those will remain same.
Ravi kumar said
Thank you very much sir
anil said
Hello Pavan sir,
I want full details of TOP FIVE EVENTS and ALL HIT RATIOS .why events will be occur and how solve ,Can you please help me by providing the all details?
Thanks ,
Anil.
Pavan DBA said
Hi Anil,
See these links
http://pavandba.files.wordpress.com/2009/11/statspack_opm4.pdf
http://pavandba.files.wordpress.com/2009/11/statspack_tuning_otn_new1.pdf
for more info read this – http://docs.oracle.com/cd/B19306_01/server.102/b14237/waitevents003.htm#BGGIBDJI
prabhudba said
hi sir,
can we do oracle installation in silent mode i’m interested in learning it please help me out with any doc
Pavan DBA said
Hi Prabhu,
You can use below links
http://www.dbapool.com/articles/0803200801.html
http://oracleinstance.blogspot.com/2010/07/normally-we-installed-oracle-using-oui.html
In realtime, we won’t do slient installations mostly
ravi kumar chowdary said
hi pavan sir,
i am ravi kuamr chowdary.u r ex-student..i learn core dba & appsdba..right now i got a job on sql server..i don’t have knowledge about sql server..pls give me breff discussion about sql server.right now i am in training.
Pavan DBA said
Hi Ravi,
Congratulations first of all….
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.
teja said
Thanks for your reply on this Pavan.
Pavan Kumar said
you are welcome….
teja said
Hi Pavan, I need help on this. This entire task should be done on the production environment.
Task :
We have a two node rac now in our environment. But one node was down due to hardware problem from last 1 year.
Recently we ordered a new server for Database. It came to our site now. So first i have to remove the bad node from the existing rac and then i have to add the brand new node to the existing rac.
But the problem is:
Type of the brand new server which is to be add is : T3-1
Type of exixting dbserver in rac is : T5240.
Details of existing rac is:
1) Oracle DB Version : 10.2.0. 4
2) OS : Sun Solaris 10 version 5/9 u7
3)Kernel Version : 144488-17
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.
I am Awaiting for you reply Sir…
Thanks in advance
Regards
Teja
Pavan Kumar said
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….
tangy said
Try building a standby system from existing rac to new 2 node rac and make a switch over. This should have minimum downtime than expected. please give a test before implementing this.
Pavan Kumar said
sorry, i didn’t got your question here….
ravi said
HI PAVAN SIR,
I AM Ex-KANNA INSTITITE STUSENT.I WANT INSTALL ORACLE 9i OR 10G (WINDOWS) IN MY LAPTOP.IN MY LAPTOP OS IS WINDOWS 7.WINDOWS 7 IS COMPATIBLE 9i & 10G.
PLESE TELL ME
Pavan DBA said
Hi Ravi,
9i is not suitable, but 10g can be installed
teja said
Thanks thangy for your note id
teja said
Hi Pavan,
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.
Os version : Sun solaris 10
DB Version : oracle 10.2.04
RAC to be setup : 10G rac
Will you please guide me with the basic procedure and steps orally, So that i will do my work around on this later.
It is very pleasure to me if you give me any doc id on metalink.( doc id needed for setting up 10grac on solaris10 and for rac migration)
Pavan Kumar said
Hi Teja, already I have docs on configuring new RAC setup in my “important docs” page. you can refer the docs there.
May be those docs talk about linux, but steps would be almost same on solaris also.
you can also see this oracle link – http://www.oracle.com/pls/db102/to_toc?pathname=install.102%2Fb14205%2Ftoc.htm&remark=portal+%28Books%29
regarding migration, i need more details to confirm the procedure. plz chat with me when i am online. but most of the times, migration will be done using expdp/impdp
tangy said
Hi Pavan/Teja
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.
Tangy
Pavan Kumar said
Yes, I too agreed with Tangy that datapump is helpful for less DB size
tangy said
Best oracle Note is : 414878.1
Pavan Kumar said
Thank you for providing this
vikas dharmadhikari said
Hi sir.
what is the difference between materialized view and snapshot?
Pavan Kumar said
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
Dilip said
Hi Pavan,
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..
Regards,
Dilip
Pavan Kumar said
thank you Dilip
vemuri said
sir what is the difference between synchronous I/O and asynchronous I/O and what is the relation of them with oracle
Pavan Kumar said
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
see this – http://www.oracle-base.com/articles/misc/DirectAndAsynchronousIO.php
vikas dharmadhikari said
hi sir
i have one question that some dynamic performance views works at nomount
ans mount i just want to know from where these views fetch the data
Pavan Kumar said
These views will fetch the data from X$tables which will get initiated in the nomount state itself
vikas dharmadhikari said
thank u sir…………
tangy said
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.
Mahesh said
Great to See you
Mahesh
vemuri said
hi sir,
where does scn number stored in redologfiles.How to find it?
Pavan Kumar said
SCN number will be stored in redo entry in one of its column. In order to read redo entry we need to use LOGMINER utility. we will use dbms_logmnr package. for syntax and other details, just google it
vemuri said
hi sir,
I have only hotbackup and i lost all my redolog files then how to recover them.plz give me the solution
Pavan Kumar said
you need to follow below steps
1. shutdown immediate
2. startup mount
3. recover database until cancel;
4. alter database open resetlogs;
when you use resetlogs option, oracle will create new redolog files and your log sequence number will be reset from 1 onwards….
vemuri said
thanks sir for the solution
Pavan Kumar said
You are welcome
santhosh said
Hi sir
I’m santhosh
till now i did’nt go through your blog
but after seeing this replies and comments
hope ur blog was plenty enough to become a good DBA
Thank You
Pavan Kumar said
thank you santhosh for your comments. still am trying to post so many articles
chandrashekar G said
Dear Pavan,
I am getting an error while running EXPDP.
Please Help me
C:\>expdp system/manager@CNBKRRB1 full=y directory=test_dir dumpfile=expdp.dmp logfile=expdp.log
Export: Release 10.2.0.4.0 – Production on Wednesday
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at “SYS.UTL_FILE”, line 488
ORA-29283: invalid file operation
Pavan Kumar said
Hi Chandu,
It is due to a bug in windows. see this
http://fritshoogland.wordpress.com/2010/01/20/using-datapump-on-windows-to-write-to-a-share/
chandrashekar G said
pavan sir,
We are using AIX platform….
Pavan Kumar said
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 pogul said
Good Evening Sir,
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?
Pavan Kumar said
Hi Sanjay,
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….
chandu said
hi pavan,
Whenever i enable change tracking file that time i will get the below error..pls provide the solution..
ora-19760 error starting change tracking oracle
Pavan Kumar said
Hi Chandu, can you tell me what more information is there in alert log file. also is there any trace file created? if so have you examined that?
suman said
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
Pavan Kumar said
Hi Suman,
after moving datafiles and redologfiles, did u ran relocation command in mount stage? for example
SQL> alter database rename file ‘old loc’ to ‘new loc’;
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
G .chandrashekar said
Hi Sir,How r u sir,
Sir pls provide script regarding genarating the AWR report(I need to genarate every one hour) so pls provide the script..
and one more thing is i want shift the archive logs from primary to standby but primary & standby both are different environments how to shifts the archives.
Pavan Kumar said
Hi Chandra,
I am fine & hope the same with you
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?
Chandrashekar said
hi pavan,
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…
Pavan Kumar said
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
G .chandrashekar said
Hi Pavan,
Neeed script to monitor CPU Utilization…
Pavan Kumar said
cpu utilization can be found using “top” command in unix and through task manager in windows
Chandrashekar said
In RMAN is it posible to increase the retention policy?,If it is posible pls explain me how to increase and what is the command..
Pavan Kumar said
yes it is possible. when you type show all, by default it gives you retention policy to redundancy 1. you can change that like below
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 28 DAYS; – this is in case you are using recovery window
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 30; – this is in case we use redundancy
G .chandrashekar said
Hi Sir,
How to enable block change tracking in rman,Is there any parameters changing required in controlfile????
Or in sql directely we can use this command only..
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;
Pls explain me what is main purpose in background how it will work…..????
Pavan Kumar said
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
chandu said
thank you pavan ge….
G .chandrashekar said
Hi Sir,
What are the mandatory parameters in DATA GUARD,what is the use of those parameters,and how to transfers the datafiles,archive shipping…
Pls explain me briefly…
Pavan Kumar said
Hi Chandu, I have a document attached in “Important Docs” page for physical standby database creation and dataguard broker. please check that and get back to me in case of any questions
Chinthala Mahenderreddy said
Hai sir,please send some details about crontab with some simple examples.How to execute oracle jobs from crontab?.
Pavan Kumar said
again, google will help you alot….
Nitan Jandial said
What is cron?
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.
Hope this would help you.
Regards
Jandial
Chinthala Mahenderreddy said
Hai sir.Please send some useful suggestions(how to approach) to get Oracle DBA job.
Pavan Kumar said
Hi, the only way to get a DBA job is to practise a lot and keep on reading theory. You can refer to “Expert Oracle administartion” book by Sam.R.Alapati which gives good knowledge.
Jeet Padhee said
i wanna follow u sir..
may allowed please..
you have vry good n nice style of teaching
Chinthala Mahenderreddy said
what is the snapshot too older?
Pavan Kumar said
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
Unmesh said
Hello Mr. Pavan,
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.
Thanks in Advance.
Mail me @ unmeshshah1988@gmail.com
Awaiting for your email.
Pavan Kumar said
Hi, good that you wann to be DBA. welcome !
u can chat with me on mymailbox.21@gmail.com for all your questions
Unmesh said
Hello Mr. Pavan,
Thank You for your generous reply.
I have added you in my chat section of gmail email address.
chandrashekar said
Hi pavan ge,
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….
Pavan Kumar said
the smart thing about DBA is what ever you practised during course, same you will be doing in real time also. so be handy with all the topics which helps you alot
chandrashekar said
thaku pavan, and one more is thing what are the real time senarioes in datagard means(errors, realtime question like that…)Pls help me on this part,bcz i m poor in dataguard
Pavan Kumar said
anyone can explain scenerios only on their experience. if you got any errors whileconfiguring, u can tell them as the scenerios… u can refer to oracle documentation to get idea on dataguard
srinivas said
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.
Thankyou so much sir.
Pavan Kumar said
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
vk said
Hi machi,
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?
VK
Chennai.
Pavan Kumar said
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
Nisha said
Hi.
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
thanks in Advance.
nisha
Pavan Kumar said
for dataguard document, you can refer to my blog section “importatnt docs” where you will get step-by-step doc and also a video tutorial.
banti said
Hi Pavan,
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.
Thanks & regards,
Banti
Pavan Kumar said
Hi Banti,
whatever the technical articles I posted, all are the common problems we face. I will be keep on posting so many others. Do watching this blog.
Nagesh said
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….
Pavan Kumar said
as you are close to a year, you can start searching with 1+. ofcourse chances are less, but not impossible. even being in current position, you can self learn n practise to attain most knowledge
Munna said
Pavan,
I need the steps to upgrade the db from 10.2.0.2 to 10.2.0.4 and from 10.2.0.4 to 10.2.0.5 in both silent and manual mode.Please share ASAP.
–Munna
Pavan Kumar said
hi munna,
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.
Pavan Kumar said
also it is same for 10.2.0.5. you can directly upgrade from 10.2.0.2 to 10.2.0.5
Munna said
thank you..
venkata santhosh kumar B said
Hi Pavan,
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.
Pavan Kumar said
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
Younus said
One of my user has created new datafile in t1 tablespace but the user is not able to insert the data or we can say not get stored data in created table what is the solution of this….
Plz help me out…..
Thanks…
Younus
Pavan Kumar said
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 !
Younus said
hi Pavan thanks for rply
But that is not the answer of my question !!! i mean alert log file has the information about that datafile and the datafile is also added correctly ….
And one more thing how many standby destination we can set in primary parameter file “like destination of archives”…
Pavan Kumar said
ok, what is the error msg users are getting while inserting the data? may be based on that we can analyze more.
regarding, standby destinations, we can have 9 till 10g and 29 in 11g
Naveen Bangari said
Hi Pavan,
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?
Can you please help me on this?
Pavan Kumar said
Hi Naveen,
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
select saddr from v$session where sid = ;
also, Library cache lock is exposed via x$kgllk, dba_kgllock, dba_lock_internal view. so check in those views.
rajesh said
hi
I am rajesh from banglore.this block is very nice,it is very useful for all.i am 2010 fresher i have done oracle DBA,RAC.i want learn more knowlegde from you.i hope i am waiting for u r respond.
thank you
raj
Pavan Kumar said
hi rajesh, first of all its not block, its blog
(just kidding)
let me know in what way i can help you to attain more knowledge…
prathap said
hi pavan garu,
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
Pavan Kumar said
Hi Prathap,
please chat with me at mymailbox.21@gmail.com. I need to ask you few q’s and then can guide you accordingly
anand said
hi pavan,
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 .
best wishes,
anand
Pavan Kumar said
you can chat with me @ mymailbox.21 in gtalk. i can guide you…
Sudhakar Reddy said
hi dude..hw are u..worthful information pavan..your blog is always inspiration to me..give me a chance to attend ur classes, on next visit to hyd surely wl meet, blocking your appointment now itself
Sudha.
Pavan Kumar said
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
)
Shah said
Greetings Mr. Pavan,
Glad to view your blogs….
Pavan, kindly send me your personal id – i need to take your suggestions.
you can email me test mail at shahhasan86@gmail.com
waiting for your reply
regards
srinivas said
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.
Pavan Kumar said
Hi Srinivas, thanks for your comments and hope to see you as great DBA in short time….
varun said
Hello Sir,
I want to perform dataguard implementation on my laptop(both primary and standby on same system).
i googled it out…..dint got exact steps…….
plz can u send me the steps for the same.
it will be really appreciated if u mail me complete steps…
Thanks in advance
varun
Pavan Kumar said
Hi Veeru, plz look into “important docs” section of my blog which contaisn a detailed doc for the same. download it and configure DG
sai said
hi sr
you are so cool
you are very good teacher
how did u learn that much subject sr
Pavan Kumar said
Hi Sayanna,
thanks for your comments. i am trying to be best actually. subject learning is because of my passion on oracle DBA. i wann all to be like that….
raghu said
hai…raghu 4m kanna techonologies…….your so cool sir
Pavan Kumar said
thanks…still wann to be more cool…
RamaRaju said
hi, pavan this is ramaraju,up dated with u r blog last month but sorry unable to be in touch with u.thanks for u r recruitment up dates.
Pavan Kumar said
Hi Ramaraju,
Thanks for your comments….
Munna said
Hey Pavan,
Hope you are fine.
I got the answer for this…
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.
Pavan Kumar said
really worthy info…thanks for sharing
srikanth.M said
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
Pavan Kumar said
thanks for ur comments. surely i will guide you…dnt wrry
srikanth said
thanks for u r reply.
Munna said
Hi Pavan,
I need your help.
1. How to resovle the ORA-1555 error while exporting using exp.
2. On what basis we decide the buffer parameter in exp?
Pavan Kumar said
Hi Munna,
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.
Munna said
thank you…..pavan….
Munna said
Hey Panan,
Can i have your personal mail id please, so that i can send you some questions i have.
Or you can just send me an email to my mail id.
–Munna
Pavan Kumar said
mymailbox.21@gmail.com. had sent mail to you
Ravinder Reddy.V said
hi sir,
Its really excellent. I learnt many new things in oracle database architecture only.
Thank u for giving this formation.
Pavan Kumar said
Hi Ravinder, thank you very much…
pintu said
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.
Regards,
Pintu Shah
Pavan Kumar said
Hi Pintu,
Thanks for visiting my blog !
I can’t deny learning third party tools will help you, but more than that you can explore many areas in Oracle DBA itself like RAC, ASM etc. Still even you can go for APPS DBA.
Personally i had not seen any benefit after doing unix admin etc…but yes i do accept it will give good idea.
Its just a suggestion and Rest is in your hands….
anjibabu said
hi this is anjibabu
I completed db2 dba cetification.
iam completed b.tech in computerscience
any fresher job or db2 dba jobs we can send mail or call
Thanks & regards
anjibabu m
9611445878
anjicse88@gmail.com
Pavan Kumar said
will send u sms….to ur number
Praneeth said
Hi Pavan,
Do you take up apps dba training in your institute. Can you please provide me with the course,fee and duration details for the batch. please do specify me the recent date when u can start the batch.
Thanks,
Praneeth
Pavan Kumar said
Hi Praneeth, i mailed you the details to ur id. plz check
Babasaheb godase said
Hi sir,
i have joined one institute as a dba trainer
not main batch but handling lab demo’s and taking basic sql and unix part…
so,i’m using ur notes and those are rocking here….
Pavan Kumar said
nice to hear that….my friend also jumped into this path
I wish you All the Very BEST for your new role and want to transform into a full fledged trainer
Thanks alot…your support made me to frame that notes….
Vidya said
Hi Pavan,
This blog is fentastic and it helped me to clear most of the doubts.thanks for guiding us.
Vidya
Pavan Kumar said
thnx fr ur comments…and still am striking hard to learn n post new things in oracle
Munna said
Hey Pavan(Father Of Oracle),
First of all I wanna appriciate for sharing your knowledge.I feel very happy and gianing good knowledge by going through your blog.
Your parents are very lucky…blessed with a son like you.
Keep posting the new updates.
–Munna
Pavan Kumar said
Hi Munna,
I can never be a father of oracle. there are lot many people for that designation. i just know 1% of DBA and still waiting to learn more and more and working hard for that.
and a small correction in your statement (if don’t mind). anytime i am lucky to be a son to my parents who strive hard for me all these years.
with all your co-operation and blessings i will continue to post new updates all the time….
Once again thanks for commenting….
Bhaskar ME said
Dear Pavan,
You are doing a wonderful job. I was sitting next to you in all the days of our college, but only now i am coming to know about your talents.
I need more oracle developers in my team. Will let you know more details in personal email.
ALL THE BEST.
Thanks, Bhaskar ME
Pavan Kumar said
yes macha…at that time i am a buddy and now slowly trying to turn to flower…..
Babasaheb said
hi…pavan sir, this is babasaheb from pune…
ur blog is very nice….
i start my day with ur blog…
plz send me updates…preparing for ocp
really u r rocking…………..
Pavan Kumar said
hi, nice to hear from you after long time….thanks for your valuable comments. i hope you already doing hands-on practise. it will be enough for OCP.
Krishnakant said
Hi Pavan!
Nice to see your blog! and also was looking into the comments from the other people. All the best dude….
Give me your mobile. Will call you…
Regards,
Krishnakant
Pavan Kumar said
thank you very much…
pawan.v(BNG) said
Hi Pavan,
Good. Keep going on…..
Thanks & Regards,
Pawan.V
Bengaluru.
Pavan Kumar said
thank you very much
phaniRam said
its really good, it will be supportive for junior DBA’s
Rakesh Soni said
I am really glad to see that you have become EXPERT in the field of Oracle Databases… great.
Please teach me Oracle Databases too, I am Rakesh Soni from Karachi, Pakistan.
Regards
Pavan Kumar said
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….
Samyak Jain said
Hi Pavan , really nice blog …thanx for ur efforts
Rafi(Oracle DBA) said
Hi Pavan,
This Blog is really excellent……….No words………No comments…….
Thanks,
Rafi
Pavan Kumar said
thanks Rafi
Aman.... said
Hey is it the same Pavan from oracle forums? If yes, teach some thing about Oracle to me also dude and if you are in Hyderabad,spend some busy time to meet me
.
Aman….
Pavan Kumar said
Hi Aman Sir, good to see ur comment on my blog.
yes, i am there in Oracle Forums. but as “Pavan-a DBA”. you can see one more Pavan Kumar in forums…..
Pavan Kumar N said
Hi Pavan,
I hope that one more “Pavan Kumar N” is me….;-)
Nice blog…..!!
Pavan Kumar said
hello sir, first of all thank you for your comments. I have been fond of you since our meeting in oracle forums. Hope i can add your mail id to my gmail, if you accept !
Pavan Kumar N said
Hi Pavan,
You are an Expert and I stand no-where when compared to you. I am not capable enough to standby on your side as friend… Yep, I will add up to your list, Once I am enough to something… !!
All the best for your future success and I hope to learn something from you.. !!
take care..
Pavan Kumar said
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 !
gowtham said
hi pavan,
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….
regards,
gowtham,
gowtham.pathakamuri@gmail.com
Pavan Kumar said
hi gowtham, surely i will charge you up. please let me knw in wht way i can help you?
Satish said
Hi Pavan,
Nice blog.
All the dba posts are very much useful.
KEEP POSTING
Thanks and Regards,
Satish.G.S
kishore said
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
kishore said
happy pongal sir to u n ur parents
Pavan Kumar said
thank you and wish you the same
kishore said
hi sir
ur blog is nice, its very helpful for us. i read ur profile its good. i like that u dedicated this blog to ur parents. say namasate to ur parents.
Pavan Kumar said
thank you
jagat mohanty said
I was the 1st student of Pavan who got placed as a DBA in one of the world’s largest MNC within no time.
This guy having loads of talents.
Pavan Kumar said
yes. forever i am proud of that
but as a fact, at that time, i could not able to spend much time for you people. Its all your hardwork and passion towards DBA which made this possible !
Sagar said
Hi Pavan,
Your profile is simply super like your smile.
The ways you are presenting DBA Class are extraordinary.
Sagar
9603182181
Pavan Kumar said
thank you very much Sagar !
Sateesh Kumar.P (DBA) said
Hi Sir,
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.
Pavan Kumar said
sure i will recollect and will update it
SUNIL SURYADEVARA said
Change the caption as
“DBA KNOWLEDGE FACTORY” like RGV-FACTORY ———————————–
Pavan Kumar said
thanks for the caption, but i dnt wann to copy anybody
SUNIL SURYADEVARA said
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.
Bang on! & go ahead
SUNIL SURYADEVARA