Share this:

Like this:

684 Responses to “CONTACT ME”

Thanks for sharing your experience with us, I am newly in oracle dba track, last few days we analyze that our production database get hang morning 9 am , it’s resolve as we restart our production server & their is not error msg appending in alert logfile, so please suggest idea to trouble shoot this issue.

mithileshsaid

mount point is also called as file system which is storage space (just like D drive etc in windows). if there is no space in the mount point, in 11g you can try compression during expdp. but this doesn’t gurantee that 20gb will be sufficient. we need to have more space in the mount point.

Hi pavan sir,
I completed my b-tech in 2013 with specialization IT, I joined for oracle core dba course, I am planning to go for RAC and apps dba too.
My question is I am planning to take advanced technology course too to get job quickly, so which course is best, I am totally confused with different peoples different advices , some are suggesting big data(hadoop), some are suggesting exadata, some are suggesting SAP, which course is best for for me?
For learning exadata , I heard that cost is too high, I am afraid of that too, and one more question, if I get job after learning core dba along with RAC and apps dba, will the company people will provide coaching facility on exadata or big data what ever technology they need?
one more thing which course is having bright future or opportunities big data or exadata or SAP?
Which course is suitable for oracle DBA to enhance my future?

Balusaid

Hi Pavan,
I completed my bachelors in 2012.Currently wrking as a Recruiter.I want to shift my career and i am planning to learn either Oracle DBA or Oracle Apps DBA..Please suggest me the best one, which will have a good future

Sharath kumar Vadlasaid

This is Sharath working for CTS as a Team Leader for BPO. I heard a lot about you and the skill set that you have is tremendous in DBA. I have around 6.5 years of experience in health care industry (payer and provider – voice & non-voice). But I would like to swap from this stream to IT, which is my goal. So, do you think it is possible to do this course and get in to that stream? I am a laymen of this course. So, need much info about it.. Request your help in this regard.

Javeedsaid

Sir i am Javeed Ahmad. i have done MCA in 2008 and right now i am working in High Court of J&K as technical support. i want to do Oracle DBA. i need your help. from which place shall i do my DBA. please sir help

Raja.Psaid

I sent mail to your mail id: mymailbox.21@gmail.com. If you are not able to help me, please refer some one from your knowledge whom I can approach and get help from them. It would be grateful to you, if you could do the same.

Hii i am 2014 I.T. passed out.. currently im working as a oracle forms and reports developer in a company.. im just dealing wit some basics of oracle form builder & reports builder, and also dealing with sql queries.. is my exp is suffiicient to choose DBA career??.. and which DBA specialisation is suits for me.. ?? for further jobs.. pls give ur valuable suggestion sir.. tnx for reading.. rly must..

Nagu Abbadasarisaid

akhilsaid

i have to prepare data purging plan for a company based on date column of tables which has multiple indexes and relationship with other tables also.the size of data need to be purged is around 10000K records. there is not on delete cascade feature and no indexes on foreign key constraint.also i need to perform this purging in live database only so please share your thoughts to finalize the purging strategy.

you can make indexes unusable and then go ahead for delete. this will run faster. but after delete u need to rebuild indexes. also before starting the activity keep enough space in archivelog destination as it would generate lot many archivelogs.

harsha pydisaid

Recently ,I have tried to deploy the oracle 12c agent installation on windows platform by using the agent pull method.but it is getting failed. I have followed the oracle document.
I have raised an SR to suggest for a solution. i have given necessary log files to them, but they havent given any kind of response uptill now. its been more than a month.
I have followed the below documenthttp://docs.oracle.com/cd/E29505_01/install.1111/e22624/install_agent.htm

Could you please post the “Oracle Cloud Control 12c Agent installation on windows 64bit” document if you have any.

harsha pydisaid

SR Team has replied me back to apply the patch which has a default Agent installation problem with windows environment.
1. I have applied the patch using opatch utility.
2. “emctl resetTZ agent” has been executed successfully and asked me to run exec mgmt_target.set_agent_tzrgn script in sqlplus using sysman account .
3. When i was trying to run the script using sysman schema, it throwed me this below error.

*
ERROR at line 1:
ORA-20233: Invalid agent name hkhgc02mypdb01.aswatson.net:3872
ORA-06512: at “SYSMAN.MGMT_TARGET”, line 4585
ORA-06512: at line 1

Then i looked to trouble shoot by looking this Doc ID 388280.1 and followed it.

4. I have skipped executing (exec mgmt_target.set_agent_tzrgn) part by following the doc ID 388280.1 and rann emctl start agent command.
5. Before starting the Agent command , next step is i have managed to run emctl secure agent command and executed successfully.
6. Now this time i have ran “emctl start Agent”… It was hanged for more than half an hr and the agent didnt started.

7. For my checking i tried to execute like this below to make sure the Agent is able to connect with OMS…..

harsha pydisaid

Please ignore my above comment..it has been resolved now. It is becoz of the JDK version the EMCTL was not getting up.. i have upgraded to latest JDK version and startedt the agent.,,its working fine now. Thanks a lot

Vikassaid

harsha pydisaid

I got the requirement from the developer team requesting the particular table with the particular partition date export from production server ..
I am unable to find the exact syntax for expdp for table with jan and feb partitions.

prathapsaid

Oracle recommends you to use the latest OPatch version
and read the OPatch documentation available in the OPatch/docs
directory for usage. For information about the latest OPatch and
other support-related issues, refer to document ID 293369.1
available on My Oracle Support (https://myoraclesupport.oracle.com)

Vijay Dodlasaid

At first, my thanks for building, managing and maintaining such a wonderful website aimed at helping IT fraternity and especially Oracle DBAs around the globe.
I just happen to stumble upon it this morning!

So, I had a chance to glance through your post on 9th Jan 2014 regarding “Truncate Command not releasing anticipated space back to tablespace”.

My view is that, in practical world, one would hardly allot any time to contemplate and assign specific value to INITIAL storage clause of a segment (be it table, index or any other).
This is especially given the availability of LMTs and auto/system extent allocation policies whereby Oracle would decide the next extent size based on usage pattern/trend.

So, if left to default, which I think is 64K, then overall loss/wastage will not be that huge in overall scheme of things. Of course, this depends on how many segments are to be dealt with!

Hi Vijay, ur understanding is correct. but i have seen env in which application team will define INITIAL clause knowingly/unknowingly to a higher value due to their own reasons. so from that point of view, i posted this article.

hi, new online batch of mine started last week. if u know sql, u can join from monday onwards. for all other details, contact kanna technologies at 040-40036087 / 8008500064 or training@kannatechnologies.com

u need to apply this patch in the same way as u will be applying patches on database server. u need to identify first your application server and then go through the process. if any more help needed, plz chat with me at mymailbox.21@gmail.com

prathapsaid

Oracle support provides below solution that i have apply this patch in e-bussiness application side.

It looks like you have applied the patch in database home. This patch is client side patch and is required to be installed in the middleware in machine usirvsprdebs01.hidglobal.com.
Application of the patch in database home does not resolve the issue. You need to apply it in the machine usirvsprdebs01.hidglobal.com in the client your application uses. In case you have already applied the patch there also, please upload the relevant logs.

1. yes. if only 10 users are there, every time it do the same
2. when you say shared memory, do you mean SGA?
3. oracle will try to reduce any other memory component which is not in use and will allocate that to required memory area
4. no
5. yes

if we do create database manually, we will not have option of selecting which components should be there and should not be there. that is the reason you haven’t seen other components.

if you want, you need to install those components individually by executing some scripts (.sql files) in $ORACLE_HOME/rdbms location. to know which files to execute, u can check google. once you done this you can upgrade your database.

DB DBsaid

What i get from oracle documentation regarding cascade standby
‘ Cascading standby database is a standby database that receives its redo logs from another standby database, not from the original primary database’

Primary database is : primedb

Cascade standby_1 : clonedb

Cascade standby_2 : dummydb

I want to ship redo from primary to standby_1 and standby _1 to standby_ 2.
No direct connections between primary database to standby_ 2. and or standby_ 2 to primary database.

Seshusaid

I have gone through applying patches document it was relay very helpful thank you for sharing So here i have query In the real time how patch will be applied by using OPATCH or as you had documented the same way the patch will be applied i.e by running “run installer” program.

DB DBsaid

Yes sir , i am asking about hot backup cloning.
If we are doing hot backup cloning on same server , don’t we need to issue following query ?
SQL> recover database using backup controlfile until cancel;

Hi Seshu, patches can be applied in rolling fashion in rac databases. I hope u r asking about that only. it is nothing but applying patch in this way
1.shutdown instance on one node
2.apply the patch
3.start the instance
4.do the same for other nodes

in this mode, we dnt need downtime to apply the patch, so its more effective for prod databases

Rohitsaid

Hi Pavan,
we are planning to confiqure RMAN in our environment, i checked some command of RMAN, when we are taking the RMAN backup, whether it is archive file or database backup , we need to confiqure the channel for the same,
do we need take the help from OS Admin for the same.

example:
1> confiqure channel ch1 device type disk

2> confiqure channel ch1 device type sbt1;

i didn’t understand which disk it will take .. or which sbt1 it will take ..

rman will allow you to take backup either to disk or tape. in your example, 1st cmd u showed will take the backup to disk (the default location is flash recovery area. as you haven’t mentioned your database version, i assumed it as 11g).

your 2nd cmd will take the backup to tape drive.but for this you need to first have netbackup client installed on your database server and also configure backup policy. this will be done by netbackup team or storage team(generally we call by that name)

Rohitsaid

Rohitsaid

Hi Pavan
when we are doing re-org for large tables is there any way to know how much re-org has been completed for the table
most of the time we are doing re-org of table which are having size of 20 to 50 GB .. my manager ask me to check how much % reorg has been done at particuler time.. as i m a Oracle DBA as well as doing the job of DB2 DBA i know the procedure for DB2 .. please let me know the any method for checking the how much % table has been re-org at particular time in oracle..

if your oracle database version is 10g or above, then better use shrink space command for table re-org (command you can get it from google)
coming to your question, it is usually not possible to find out how much % of re-org completed, bcz oracle will display results only after its completion.

Rohitsaid

i need to confiqure Dataguard on my two instance RAC environment , please let me know if you have any doc created for the same, also wanted to know the steps for switchover and failover on RAC environment..

I don’t have any docs, but u can find many in google (also videos in youtube). reg switchover nad failover activity in rac, it will be same as stand alone because you will be shutting down one instance during these activities.

there can be several ways like scheduling at different time, checking if any other jobs are running during that time in the database, allocating more no.of channels/using more parallelism value, implementing incremental backups etc

dineshsaid

1) If i update a table 20 times , updated data will be recorded in data file and each transaction is recorded in redo log buffer. – IS this right ?
2) If so , exactly what kind of other information is recorded in RLBC ( redo log buffer) ?
3) Does oracle maintain separate SCN for each and every transactions ?

A number that uniquely identifies a set of redo records in a redo log file. – This is LSN

1. updated data will be there in datafile and redo entries generated for each transaction(update in your case) will be there in redo log buffer
2. redo entry will record information like at what time, in which table, by which user the table has updated etc kind of information
3. yes
4. no, your definition for LSN is wrong. LSN is a sequence number given to redolog file and when LGWR switches writing from one redolog to another, then this LSN number will get incremented
5. there is no relationship between LSN and SCN

dineshbabusaid

Hello Sir ;
I am dinesh from chennai working in customer support service.
You did great work (this blog is very useful for us)
I have two questions
1) what is I/o calls in oracle – ( little brief if possible)
2) FULL TABLE SCAN can use larger I/O calls, and making fewer large I/O calls is cheaper than making many smaller calls ”
i dont understand above point. what it does actually ?

whenever you run a select statement, oracle will search for data in memory first, if the data is not available in memory, it will scan the database and picks it from there. oracle picking up data from database is called I/O and it is a costlier operation means, it will take time. if table contains more rows then oracle need to do lot many I/O’s from database. if the table doesn’t have index, then it will do much more I/O’s and all this will make the response time to grow by which the result of the select statement will get delayed.

1. these are security patches and regular functionality of database will not be impacted even if we dnt apply them. but if db’s like in banking sector etc thr culd be prob with security as these days hackers r smart. so it is wise to apply patches in tht kind of situation
2. even though I have not used that -no_inventory option, u feel without global inventory got fixed, it might not be possible to apply patch. let me check that

yes exactly. we need to take pid from top command and then combine v$session and v$process using spid to get the details. unfortunately we need to do this in all db’s if we have more than one in a server.

Kiran Guptasaid

I am currently working on a Query , the query is taking around 6 mins to execute.

While investigating i found from the explain plan that there is a full table scan on one particular table( thrice) happening and so i created a composite index which later changed to index scan after which the elapsed time came down to 4 mins which was not upto expectations.

Then i gathered schema stats and rebuilt the indexes and fortunately the elapsed time came down to around 25 to 35 seconds which was a good result.

And one thing surprised me, the trace-output showed all the values as ‘ 0 ‘ , Enabled the trace and ran the query again for a couple of times and so but the output was same as below , Please can you help me & clarify !!!

Naveed Mohammadsaid

Suppose you have four data file in which two are having 500mb and two are 2 GB they are full, but u come to know that space is free in one data file of 2 GB. Then how will u can reclaim to that data file or table space?

Anandsaid

In Rac gc cr multiblock requests event is a multi-block read
this event can indicate an overloaded network connection between the RAC nodes, and general network issues because of the work processing the large-table full-table scan.
How to tune this issue and how to make it index base scan.

identify that columns which are need to be indexed, create indexes (if not already there). then automatically your queries should do index scan. if still not working, check explain plan and you can also use index hints.

not complete memory is shared in RAC, instead only cache fusion part. Oracle will take care of this scan using current copy. for example, once one instance performs a query execution using index scan that current copy will be shared to other instances through cache fusion.

Anandsaid

In 2 node rac while adding datafile to tablespace if you forget to metion ‘+’then what will happen whether it is going to create or it throws an error if it creates where exactly located and other node users how to work on that tablespace .what all steps to perform that datafile is usefull for all node users

as far as i know, if we forgot to mention +, then it will be created in ORACLE_HOME/dbs location (I saw this once when my colleague doing). access from other nodes will be impacted because datafile is not in shared location i.e users can access it from the node on which datafile exists in dbs directory. the preferred way is to drop that datafile and create new one in correct location i.e ASM

Anandsaid

Is there any way to make it that datafile to shared location without dropping
one more doubt

In Rac any node is evicted due to network failure then after we rebuild the network .Is there any steps to do manually to access the failure node after rebuilding the network or it will automatically available in cluster group which service is perform this aactivity.

Sagarsaid

Back with a Query again …..
Could you please let me know what are the senarios where a “Select” Statment Generate Redo Logs….. I would be much appreciated if you could explain me in leman terms …… Because tried few Blogs… couldn’t catch up their standards……

we cannot get the scn number now. possible way is to flashback database to approximate time of datafile lost. don’t you have a backup? if you have it, just restore the datafile and apply all archives and user data will be back !!!

ABDUL WAJIDsaid

Hi Pavan its ABDUL WAJID here from bangalore i have completed BE engineering ( CS ) from bangalore in the year 2008
due to rescission in that year so i have been shifted to telecom industry in 2010 i have been resigned to that. one of my friend is working
for Mphasis for PUNE location he has teach me ORACLE DBA since he working from home now work from home has been taken off from him
so as if in the market no job is prefered for oracle dba either u need to go for
oracle RAC or oracle Apps so becoz i dont have exp at all.. in the dba field i have only taken training for single instance Oracle dba
so i want to learn ORACLE RAC so i can get better job. and one more thing to be in frank no body will give me job with no exp becoz i am having 5 year gap so i am planning to use fake exp.. i have called to kenn tech so u r handling RAC classes from 20th onwards… i have been left the job in 2010 my financial condition also not good plz suggest me wt to do my no is 8147171512 r else mail me

yes. oracle RAC is mandatory for job when u go with exp. I am not the trainer who takes RAC training in kanna tech. I teach only DBA there. RAC is by other trainer. aftr learning rac or apps dba, u need to do job trails.

Hii Sir,
Im Bsc graduate in Computer science.
Now i want to learn Oracle-Dba.
So, will there be any job opportunities for me as a fresher after learning that course??
Please, guide me to choose the best option.

In today’s world, competition is more. so learning only oracle DBA will not get you job as fresher. So i recommend to learn any other course like oracle apps dba or oracle rac along with dba to increase your job oppourtunities.

Sree Hari Dubakkasaid

My name is Sree Hari .D working in MNC with total 4.3 yrs of experience in Oracle Apps Technical and recently I have been moved to Oracle Apps DBA role. So could you please let me know if I can get enough opportunities as am new to DBA. And also I recently took core DBA training and planning to take Apps DBA from your institute.

Anandsaid

I have some issues like what are the reasons behind in ora600 error and when this error occurs is there any problem to database.

How to verify corrupted block and how to restore corrupted block

i have 200 datafiles existing and i added one more datafile . if the added data file is corrupted, by applying archive logs data will be recovered but the thing is the added data file is exits or else we need to create?

there are lot many reasons why ora-600 will occur (for example any bug hitting database, any code writing error in sql etc). depends on the reason of ora-600, we can consider that as serious problem or not. but always it is better to search in ora-600 lookup tool first and then raise SR with oracle support for this error code. (even though it is serious or not)

reg corrupted blocks, we have commands like dbv (DBVERIFY) or check corruption in RMAN to find out. If anything found, we have some steps to follow in order to recover that block. you can get those steps from many links in google.

If the datafile is corrupted, we don’t have backup, then it is better to drop that datafile and then create new one and apply archivelogs.

kumarsaid

i was new to database so
i have small doubting in my database . my database sys.aud$ tablespace take 1.3gb i want to drop that . in before that drop operation i will take backup using expdp it is possible ? if yes in which user(that means sys user or local user) i was export and import
please tell me the commands for system user tablespaces

kumarsaid

KUMARsaid

when i was get this error
ORA-00600: internal error code, arguments: [kjucvl:!owner], [], [], [], [], [], [], []
my data base was improper shutdown and start up again manually how can i rectify this error

KUMARsaid

we dnt have any control on applying/releasing locks. if lock is to be released, then either transaction shld be commit or rollback or we need to kill that session. killing a session is not recommended. so do either commit or rollback

kumarsaid

archive logs will generate when huge DML statements ran in your database. frankly this is not a unexpected behaviour, so nothing to avoid it. if you want to reduce, you need to tell the users to not run those statements. but we can’t do that. isn’t it?

so we need to schedule backup of archives in frequent time like every 4 hours, 6 hours etc

Ganapathisaid

one of the database have take to lot of time for RMAN backup it will take around 15 to 20hr size of the database was approximately 900gb . how rectify this problem ..plz give the suggestion..it wil take backup of control file and spfile backup nearly 4hr i don’t understand that why it was take that much of time ..

hi, I believe some problem is there at storage level. check with storage team if they did some changes at SAN level. also u can trace the rman session when it is taking backup so that u can know where it is spending much time and why.

Anandsaid

Myself anand i read your posts i learned so many things from your valuable posts

Today my question is what is blackout and why we need to set blackout in oem
i have read some docs still i am not getting clarity in that . could you explain it briefly so that i can get exact picture.

usually OEM is used to generate alerts for database like when db is down we shld get email etc. but when we are doing some maintenance like patching, we know that db will be down and at tht time we dnt want OEM to generate any alerts. for this we have a facility called blackout.

if u keep blackout (usually it will be for a duration like 15min, 1 hr etc), then OEM will not generate any alerts.

usually, if required we will shutdown the database, listener, enterprise manager etc for OS patching. So, once patching is done, we need to make sure all the services like listener, EM etc and database is up and running. Once users can be able to connect to database, then u can confirm that everything is fine.

Muralisaid

Dear Pavan,
Do chrash course available for DBA RMAN,RAC( can course be completed in around 15days). I have some basic knowledge on rman, taking manual backups, Creating databases, schemas, tablespaces etc.

Anandsaid

I have a question could you tell how to solve this issue
Oracle 10g database.One table is dropped one hour ago by mistake and you don’t have the backup and flash back feature is not enabled?How you will able to recover?

Anandsaid

subhasishsaid

Hi
i am interested to go training on RAC +Apps dba from Kanna technologies>prior to that i thought of rebrushing oracle DBA course but duration (2 months) is too high for me as i have to try the job trial,so wanna finish it at the earliest.

Subhasish, the batches which I handle doesn’t have any fast track option. I believe there are fast track batches by other faculty and it is online. If you are interested, you can attend that. for more details, contact 040-40036087 / 8008500064

it means LOG_ARCHIVE_DEST_2 is valid only for the primary role. This destination transmits redo data to the remote physical standby destination boston. when standby becomes primary, then it uses redolog files as online, so we are mentioning ONLINE_LOGFILES

Shuvsaid

if i set VALID_FOR=(standby_logfile,standby_role) for log_archive_dest_2 then the standby redo logfiles will be filled and archived instead of online redo log files on standby. So cannot perform switchover. Am i correct in my understanding?

KUMARsaid

hi pavan sir
i was new to rac database . we are using 2node RAC . my problem was database node balancing is not good that means example one node contains 100 sessions another node contain only 5 sessions like that plz give any idea about load balancing

this may be because tnsnames.ora file at application side may contain only one node information. you need to add another line to that tns entry and input and a parameter called load_balance=on. for a sample rac tns entry, refer google.

KUMARsaid

my tnsnames.ora file have contain that load_balance=on
but there is no switching,when ever i was restart my database noad switching is well. after 2days from database restart same problem was occurs, this will occurs in single database only

do you have load balancer configured at networking level? (you can find out this with your network admin). just having load_balance=on is not enough. you need to have that load balancer configured, then only connections will be distributed.

namartasaid

Hi pawan sir I have done MCA in 2010 and oca certification at oracle 9i .due to some reason i had to take break .in 2012 i join niit for oracle 10g right now i wants job in oracle field what i have to do for getting entry level job which will lead me as a racle dba in future

Hi Namrata, frankly now a days to get fresher dba job only DBA knowledge is not enough. so I recommend you to do either oracle apps dba course or oracle RAC and also as oppourtunities will be less, you need to keep on trying for job. in DBA also, you need to have good hands on for advanced topics like dataguard, rman cloning, upgrade etc

Shuvsaid

“It is highly recommended that you have one more standby redo log group than you have online redo log groups as the primary database. The files must be the same size or larger than the primary database’s online redo logs. ”

My questions are:

1) Why do we need one additional standby redo log group than in primary database?

Is it because to avoid LGWR waiting on the primary while redo is applied?

2)What type of problem will i encounter if the SRL size is less than the online redo log file size?

1. In case of 3 online redo log groups, it is recommended to use 4 standby redo log group this is in case if log switching is happening frequently on primary and all 3 standby redo logs are still not completely archived on the standby and 4th can be used here as there will be some delay on standby due to network or slowness of arch on standby.

2. you may observe performance impact when you make that standby as primary in future

Rohitsaid

Hi Pavan,
can u plz tell me how to find the number of row updated in current running sql as the query is running since long time..
i googled and found one pl/sql code… but i m not sure it will work..as i its production db.. i m not able to take risk.. can u plz tell me how to find it..

already if the statement is fired, then we cannot be able to know that. otherwise we need to depend on that pl/sql code only. there is no direct way in oracle for that. if u enable auditing on the database, then it is possible.

> ** > Pavan DBA commented: “already if the statement is fired, then we cannot > be able to know that. otherwise we need to depend on that pl/sql code only. > there is no direct way in oracle for that. if u enable auditing on the > database, then it is possible.” >

i recommend to join apps dba (ofcourse if you can do RAC also, then apps dba+RAC is hot in the market). for institute you can contact kanna technologies for apps dba and RAC or sri sai technologies for RAC

hi sir…………………..
actully i have some columns like 123 abc xyz …….>first column
123 abc pqr….> 2 nd column …etc ,here so many columns ya dis releted. we search only pqr status and we skip from abc to pqr.so how to skip before column from one column to another column….

1. we can check the object count between the target database and auxiliary to confirm cloning success.
2. sometimes, i have seen rman cloning become issue without static entry. so i prefer to use static entry than extproc

rohitsaid

hi pavan ..its very simple and nice blog i have seen yet.. i really appreciate ur work..
i am working as oracle DBA at syntel.
i have one problem.. that most of the time we get the ticket on high cpu memory.. will u plz clear me out regarding what to do for that as oracle dba… i m using top command but unable to understand what happening..
waiting for response..

Rohitsaid

MV Bhaskarsaid

Hi Pavan,
This is Bhaskar. I am a BSC graduate of 1990 bach worked with an INHOUSE team of dealership company as Oracle Developer for 11+ years. And I am noware working now. Can I choose my career as DBA, and try for software jobs? Kindly assist. Will my age and Company will be a problem?

1. if u r using multiple hard disks in ur server, then multiple datafiles is recommended. otherwise 1 datafile is enough
2. it depends on how much data that is coming in and whtr in ur company u have 24*7 support or not. i recommend to enable it with maxsize option
3. yes, whenever u r using autoextend, u need to use maxsize also. (give any value for maxsize. for eg, if ur datafile size is 10G, give maxsize as 20G)

shuvsaid

It is recommended that the SPFILE is shared between all nodes within the cluster, but it is possible that each instance can have its own SPFILE. I would like to know why it is recommended? Is it because shared SPFILE simplifies administration or anything else?

yes. we can have individual spfile for each node. common spfile is preferred because whenever you do changes it will be effected for all instances at a time. if u maintain individual spfile and suppose you changed one parameter in one node and forgot in another, that may lead to some issue. to avoid this, we will maintain shared spfile.

Madhava Pai Ksaid

I am searching for a job in .net field .I have 2.3 year of experience .Currently i am working on classic asp and .net.
I have only 0.5-0.6 year of experience in .net.
I want to change my company.Please tell me if there is any vacancy.
may i know your email id so that i can send my Resume.
my mail id is kmadhav.pai555@yahoo.in

Rameshsaid

as far as i know a service delivery manager should be able to have knowledge on all tecnicals concepts like unix administration, DB administration, network etc. I am not saying in depth, but a brief idea. With this knowledge you can promote yourself in current company for the next level as SDM.

sai kiransaid

hi pavan,
Now a days in interview they are stressing on up-gradation and migration (from 10g to 11g) so can you share the documents of up-gradation and migration and some points on this topic so that it will be very helpful to us

Prashantsaid

hello sir… i am a 2012 passout graduate(B.Tech CSE) and completed OCA certification in PL/SQL. Besides i am also have good knowledge of ORACLE DBA (10g) concepts like user mgt., achitechture,backup and recovery,ASM,data guard and performance monitoring and tuning also with basics.
Now i am worried for getting the answer of ” How to start my career in database ??”. as some are suggesting me to join RAC and without it there is no job for fresher DBA.
So please guide me sir…. i am also having good acdmics %(75+) till graduation. but not having any vision that how to start…..please help me sir…..

apoorvsaid

hi i wnt know about the best learning institute in hyderabad for oracle dba, also the institute which provide placements……..,as well as certification…………., plz tell which one is better oracle 11g or oracle 10 g

Caution: I recommend you to join in any of the above mentioned top 8 institutes..If you join in any of listed after 8, there is no problem just you ultimately realize and you are going to join for course once again.

***Do you know already these institutes aware of this situation, they will not bother if you come 100 times for same course by paying only one time, why because these institutes almost out of students.

nandusaid

Dheerajsaid

hi this dheeraj, i completed my course in hashzone..hashzone is one of the best institute in ameerpet..may be after wilshire institue. i dont know about other institutes and also kanna inst.. my friend joined in unirac, institute was not good ..bt they will provide placement

sandysaid

as far as i heard or experienced from my trained candidates, orafact is not a good choice. so many of my trained candidates first joined in orafact, completed their course and then re-joined to my batch

first of all question is not that much clear because the answer would be based on what error it is throwing. i assume the error is about space issue and this can happen due to fragmentation in the tablespace. but usually we won’t find this issue with SYSTEM tablespace.

Sivaprasath.Dsaid

kishoresaid

I am trying to migrate oracle db form windows to linux,in windows 7 i checked that process is 64-bit but in db level i checked in platform_name column it’s showing 32-bit
please explain what exact diff for os level and db level version and why it is showing different from os

Dineshsaid

Due to archive destination got full,Moved some archives to another location,when i am running the rman backup it is not able to find the status of the files which are moved,so run the crosscheck command from rman promt.now the backup is success and deleted the archives.
Now moved that files to original location but the issue is the status of these files is marked as expired in catalog,
1) Is there any way to mark them
as available in catalog.

shashisaid

hi pavan,
i have around 80-100 databased i want to monitor the alert log file for errors.do u have any scripts or tool to monitor d erros which displays the time n date along with error
thanx in advance…
shashi

Rashmisaid

I am facing ” ora-12547 tns lost contact while connecting through toad ” on client machine, all my listeners are ready on the server , even proper entry is made in tnsnames.ora at client end.This is my tns entry ,

nasirsaid

Hi pavan,
its very nice useful blog for your every dba.. as ur answer is very simple …. and easy to understand..
i have one question that when we are upgrading DB from 10g to 11g is it necessary to apply patch to DB before.. and if it is necessary then why ? i m kindly waiting for ur reply
thanks
Nasir

nasirsaid

Rashmisaid

I want to implement backup & recovery at my client end.I have thought of full db backup(cold bkup) every 15 days & incremental level 1 bkup everyday.Can my cold backp act as an incremental level0 backup to restore from incremental bkups.If not how do i figure it out can you plz help because client is more biased towards cold backup.need ur help.Thanks

Hi Rashmi, cold backup cannot be act as level 0 for incremental backup. so that option is ruled out.

you need to take level 0 incremental backup which support level 1 backup later. Tell to your client the benefits of hot backup with RMAN. some clients will be thinking that cold backup is good and hot backup is not. in such situations, as a DBA, we need to explain them the benefits and make them understand that hot and cold backup are one and same especially in rman.

Actually am working as a PL/SQL Developer. But am very much interested to work as a DBA. Even i have completed Admin 1 and 2 Oracle 10 G. But i did my OCA on development track. Right now am searching for a DBA position. Can you guide me in preparing for this.
I have basic knowledge on all DBA concepts. But dont have any handson.
Am just requesting you to give some suggestions, like what can i put in my resume with this knowledge and am having 3 years of experinces in development.

Hi Sunil, If you have DBA knowledge, then you can put the same in your resume that you worked as developer, but got knowledge on dba also. for more understanding, plz chat with me in gtalk. my id is mymailbox.21@gmail.com

shashisaid

hi pavan am shashi am wokring in data center since feb 2008.My role is to taking cold backup of standby and production databases. n i have done switch over activity in windows.now am planning to change the job.i want to know what should i prepare for n from where i should start.please suggest me my id is shashi.mudbagilu@gmail.com

vinod kumarsaid

hii sir,
this is vinod..sir im going to complete my btech within less than a month……..so my area of interests is databases..im still in job trials….so can u please give ur valuable suggestions to get a job especially in the side of databases…and also im not able to find any openings for freshers in the stream of oracle or othr dbs

Hi Vinod, to get job as a fresher DBA, you need to acquire the best knowledge in that concept and also need to have a certification. Comparitively fresher dba jobs will be less as most of the companies are recruiting from campus itself.

If you have strong concepts and certification, you can still get job in very good MNC’s

Asif Hussain Khazisaid

I have installed red hat linux 4 in vmware twice for two different servers and installed oracle 10g. I configuers listener in one server and tnsnamesin another server.
When I attempted to connect to the server I have got the above error. i have attached the file. Please check this and resolve the error………..
~]$sqlplus sys/******@to_prd as sysdba
Error:
ORA-01031: insufficient privileges

Fixed Size
– Contains general information about the state of the database and the
instance, which the background processes need to access.
– No user data is stored here.
– This area is usually less than 100k in size.

Variable Size
This section is influenced by the following init.ora parameters
shared_pool_size
large_pool_size
java_pool_size

Redo Buffers
– A circular buffer in the SGA that holds information about changes made to
the database.
– Enforced mininum is set to 4 times the maximum database block size for the
host operating system.

1. as far as i know, it is not possible to do that.
2. here is one good link to understand about SCN – http://www.dbapool.com/articles/1029200701.html
3. Because without redologs the backup would be incomplete or inconsistent and it is not possible to recover later.
4. By default any DML statement will generate redo entires even if we specify nologging. NOLOGGING is applicable only for certain conditions as belowhttp://dba-oracle.com/t_nologging_append.htm
5. I didn’t get your question. updation date means?

Sudhirsaid

Recently I attended interview. He asked me many question among those I didn’t answered the questions mentioned below. Could you please provide the answers for these questions.
1) when we submit a request it is taking long time, and rest of the queries running properly, what would be the reason.
2) How can we find out whether the request is scanning full table.
3) Can u explain about explain plan , its use.
4) How can u find out the problem is with indexes, how can u resolve.
5) If a request is running properly in all the peak hours, suddenly its get slow, what would be the reason.
6) I found a gap nearly 10 to 20 lines gap in alert log file what would be the reason.

1. reason could be so many. we need to check for the reason in phases by doing gather stats check, generating explain plan etc
2. we can check that from explain plan
3. explain plan is a report which shows us if the query is using indexes or not
4. by looking at explain plan. if we see query is using indexes, its fine. otherwise we need to create index on required columns
5. again reasons can be many.
6. i have never seen any gap like that. may be someone deleted some lines or oracle faced problem in writing to alert log

Sorry to say this. but I am not sure how you are attending interviews without basic knowledge on performance tuning.(I estimated your knowledge based on your questions, i may be wrong)

To compile all the invalid objects in the database, we will use that utlrp.sql script. If even after executing that script, we have invalid objects means, we need to inform application/development team and they will take of them

Rashmisaid

I need to insert lakhs of rows in a table ,what needs to be done so that i get performance benefit.
Secondly I need to migrate db from one platform to other.The db_block_size of primary db is 4kb ,how do i restore it on other linux paltform with different block_size of the db.

With my experience, I have not seen any special things to be taken care while inserting huge no.of rows. Only thing we will worrying as a DBA is about huge archivelog generation which we can manage. But still if we want, least is disabling the index which may speed up the process a little bit.

some other places, you can read that “NOLOGGING” clause may help in speed up process, but I want to say that that is not applicable for DML statements.

regarding your second question, as the databases are different servers, we can use expdp/impdp to acheive this.

upendarsaid

suppose we have deleted some backup pieces of rman at OS level due to some space pressure. In this case, still RMAN thinks that files are available. so in order to make rman understand that files are not available, we will use CROSSCHECK command. when we run this, rman will mark the deleted backups as EXPIRED and we can remove that info from the backup list.

Generally servers will have multiple hard disks to store the files. the reason is, when we use a single hard disk, it will have only 1 I/O header and if this I/O need to do two tasks, then waiting will be there as I/O hdr can handle one task at a time. But when we have mutliple disks, there will be independent I/O hrds for each disk by which writing can be done parallely. This is called “Optimal Flexible Architecture” in Oracle.

So, as per this, even if we create two tablespaces (one for table and other for index) in the same hard disk, there is no use. we need to create these two tablespaces in different hard disks and place indexes and tables separately which will reduce IO contention and will increase performance.

upendarsaid

Hi sir,
i have a doubt in RMAN . According to my knowledge RMAN catalog will holds changed values and RMAN backups are stored in the at OS level to a disk or to a tape. is it true? if it is true what is mean by changed values and what they contain exactly?
thanks in advance…….

CPU (critical patch update) – it is a collection of bug fix patches which will be released every year.
PSU (patch set update) – it is above CPU i.e it contains all the bug fix patches which are there in CPU and along with that it also contains other cumulative patches.

Earlier, only CPU’s used to be released every quarter. but now PSU’s are also getting released every quarter.

Rashmisaid

Gaurav Nigamsaid

Thanks a ton for such a nice notes.
I have a question:
I have to drop a user from production box..
what are the steps i have to take..?
like first i need to take logical backup etc….i am little bit confused on roles,view, synonyms..shall i need to delete all these before droping the user….?
Please help..thanks in advance..:-)

Gaurav Nigamsaid

First you can learn shell scripting. (if not already have idea). then automatically you can able to write shell scripts. DBA scripting is nothing but shell scripting.
for this there is a book called “Unix shell scripting” by “yeshwant kanitkar” which I feel is the easy one to understand

check if statistics are upto date for the table. generate the explain plan in dev and test and compare them. generally I saw these secenarios only when some indexes are missing etc. so explain plan will tell us about indexes.

kishoresaid

Hi,This is kishore,interviewer asked me like that
in 11g we have memory_target parameter is there.they was asking me what about shared_pool,if i need tune that sh_poo how can u tune without mention sh_pool……….in pfile…tell me the ans

abhisaid

Hi pavan
i have gone through several blogs as like Kamran Agayev’s. tom’s etc…but the need full information is at your blog…as like what are the books to be studied…and i have done database cloning through your given steps nice..and very short…

i wish to know what are the good books or blogs to my self strong in the Sun SOLARIS 10 as i am beginner for that…??

Hi Abhishek, thank you first of all for your comments on my blog. as we are not Os admin’s I believe we can concentrate more on DBA. Having basics knowledge of Solaris would be fine to survive. For those commands, you can refer to google. Let me know if there is specific reason to be strong in solaris.

Mohammed Shahid Khansaid

Dear Pavan,
I found very good valuable resources, I need your assitant regarding OEM Grid Control 10gR2 installation on CentOS 5.7
while checking the kernel parameters for installation i am getting the below two parameters failed

Checking for semmsl2=250; found no entry. Failed <<<<
Checking for filemax=65536; found no entry. Failed <<<<

Mostly in interviews they will ask about AWR report analysis etc. for this i already have 2 docs in “important docs” page of my blog. the doc names are “analyzing statspack report 1 & 2”. even though it says about statspack, it is applicable to AWR report also.

for strategies, you can refer to the questions asked in oracle forums. (http://forums.oracle.com) (you can do a free registration here)

vinod kumarsaid

hi sir ,
this is vinod.i am a passout of 2012.is it a good idea to do oracle apps as a fresher.how are the oppurtunities and what are the prerequisites to learn oracle apps..sir please give your suggestions

Hi Vinod, I am not sure what you asked by Oracle apps. Because Oracle apps contains two divisions. One is technical (which is more into programming) and functional (people call it as apps dba, which is into administration side). But as a fresher oppurtunities will be less for any of the divisons

Rashmi, there are several ways to configure database replication like using materialized views, streams, active dataguard (in 11g), golden gate (which is new technology). Out of these I have a doc for stream configuration in my blog. see this

sivakumarsaid

Sir, this is sivakumar, i would like to know how to design the database, i tried to do that, i faced many prob, one of that it, is
putting Primary key and Fk. So please tell with one example, with any name of database. so that , i can do that

Hi Siva, designing database is one the complex jobs DBA will do (that is why most of the times experienced dba’s will do that). As per your question, I can understand that you are talking about table design (preferably we should not call it as database design). In table design, we should concentrate firstly on constraints and need to decide which columns can be PK or FK etc. The best example you can take always is EMP and DEPT tables in SCOTT schema.

Suppose we have a student management system which is storing students information for a college, then columns in that table can be like this
Name
DOB
Father_name
Mother_name
Branch etc

now from the above you can see no columns can be PK, so we need to add new column by ourself called “student_id” and make that as PK. this column should also be there in other tables where it will be FK.

Hi, generally we need to use TOP command to know high load processes and based on process id, we can identify the sql statement. Even for contention we can use iowait command. But all this will be easy if you use Enterprise Manager in 10g

Rashmisaid

anwarsaid

Sir,
i anwartaz completed B.Tech-2011 with aggregate of 65%.After that i did oracle DBA in ameerpet since 5 months i have been looking for job as a fresher/trainee DBA.i uploaded resume in all job portals and i went so many job consultances but idid’nt got any single chance to attend intrview.know i’m totally frustated could u plz guide me how to get job as fresher.i’m eagrly waiting for ur replay

Now a days having Oracle DBA knowledge alone is not sufficient as competition is getting more day by day. so try to learn adv topics like oracle RAC, APPS DBA etc. also if possible, try to do OCP certification which enhances the chances of getting job

rafisaid

hi pavan,
i read u r blogs. its really helps lot.
i have some questions
one is how to restore the table (not using logical backup and flashback technology
second is how to restore rman backup thats having on diff disk or tapes
and third is what is locks latches and enques and please provide the performance tuning related docs.

1. if it is 9.2.0.1 or above version, then FAL processes will take care of archive log gap when network connection re-establishes
2. you need to check alert log of standby to check for any further information.
3. then u need to take incremental backup of primary database and rollforward that to standby. for steps, refer to google

prathapsaid

On the standby database a Fetch Archive Log (FAL) client monitors for gaps in the sequence of received logs. If it finds a gap, it may invoke one or more Fetch Archive Log (FAL) servers to run on the primary database to forward the missing item(s).

prathapsaid

on standby database 400 archivelogs are missed .i transfered that archive logs from production to standby manually by using scp command.then how apply that archive logs in standby?please provide me steps sir…………

Jagatsaid

I checked in the trace file it does not show call stack of use PX operations.
The trace file also masking the commands which were run to hold Shared or Exclusive locks by the SQL. All deadlock traces are partial trace.
Is _bloom_pruning_enabled”=FALSE setting really help on this?,We have PSU3,guess it may be a bug not fixed in PSU3 .can you suggest?.S and X locks are conflicting and one prevent the other P0 from making any progress. The P0 sessions try to get hold of same locks in reverse order.

Ravinder Reddysaid

This is Ravinder. I need some help from you.
recently I got a chance to reorg some tables.
As per my knowledge we have the following methods.
1.CTAS
2.Move
3.export & import.
In my case above 3 are not working.
Here the database version is 8i(8.17).
table size is 7GB and it contain one long datatype.

I tried to use move command but it didn’t work bcos of long datatype.
next trial I export the table with no indexes,no constraints it took almost 30 mins.
But for import it is taking more than 10 hrs.

as per my knowledge, export/import is the possible way since the database is 8i. You can better try to import that table into a test database. once done, you can export from there and import into production. (i believe even if u have import running for more than 10hrs, there will be no issue). Import will take that time because of LONG datatype.

bhaskarsaid

hi sir this is bhaskar student of kanna inst bd-19 batch. plse reply my questions to this mail id bhaskar.kanna1986@gmail.com
1.What is the differences between oracle 9i and 10g versions of RMAN backup ?
2.now iam trying on 3.2 years of exp… in interviews they asking in which level ur working L1 or L2 what is L1 and L2

bhaskarsaid

Shreyasaid

I am a aspiring DBA. I have taken coaching at a institute in Ameerpet. I have practised in lab environment. But i am putting 2.8 yrs experience as i was passed out in 2008. In the interview people are asking me several technical questions that i could answer. But some people are asking me questions such as ” TELL ME THE STEPS – – – HOW YOU CONNECT TO THE DATABASE IN UNIX PLATFORM MACHINES? ” I am unable to give the steps. please help me with these basic things. I am a hard worker, but due to some reasons i had not given a try at software job in these years.