CONTACT ME
Folks, you can contact me in below ways…
Personal Mail id : mymailbox.21@gmail.com
Facebook id : Pavan Dba
Twitter id : @pavandba
Linkedin – http://in.linkedin.com/in/pavandba
Pavan DBA's Blog
The DBA Knowledge Store
-
Join 5,009 other subscribers
- Follow Pavan DBA's Blog on WordPress.com
Visitor Count
Categories
- 12cR2 New features (5)
- Admin (50)
- Bkp n Recovery (15)
- cloning (4)
- Certification (3)
- Dataguard (6)
- export/import/Datapump (11)
- Freshers (1)
- General (25)
- Installation (5)
- Interview Questions (1)
- Jobs (1,701)
- Networking with Oracle (5)
- Perf Tuning (3)
- RAC (1)
- RMAN (8)
- Scripts (73)
- Administration (33)
- backup and recovery (9)
- Dataguard (2)
- Performance Tuning (28)
- RAC (1)
- Training (62)
- upgradation (10)
- User management (3)
Vote your opinion…
TechMaster Training
My Facebook Page
My Tweets
Tweets by pavandbaMy DBA links
Other DBA Blogs
Pavan DBA's Blog
Dharmendra Gupta said
Hi Pavan,
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.
Pavan DBA said
check if any backup is running during that time.
check sessions in the database and processes might have reached max value
u can take awr report for that period and can check in that as well
Vaibhav Gaikwad said
Hi Sir,
I completed Bsc(IT).I want become a Oracle dba,but i have three years education gap.Can I get job as a Oracle dba?
Pavan DBA said
yes you can
Pratyush said
Hi Pawan,
Can you please give me Oracle DBA and Oracle apps commands and queries which are very useful to me..
Thanks,
Pratyush
Pavan DBA said
Hi Pratyush, most of them i posted in “scripts” category.
suresh said
hi sir i am suresh from hyderbad .we are conducting offline classes or online classes . i am interested in offline classes. please inform next available oracle dba batch
Pavan DBA said
Hi Suresh, plz contact TechMaster for training related information.
Prakash Badhani said
Hi Pawan,
I am looking Oracle Dba Training,
I am BA Student and you can say non IT student Can i do DBA Training and make career in DBA Field.
Thanks
Prakash Badhani
MObile 9899693875
Mail Genpactprakash@gmail.com
Pavan DBA said
yes, a non IT person can also do dba training. for training details contact admin@thetechmaster.in
Mohanraj said
I want to learn oracle apps dba . Please provide your suggestions
Pavan DBA said
It is good idea to go with it. I believe you are already an Oracle dba.
Ronald said
Sir, keep question section column for visitor so that they ask real time or interview ques to you….
Thank you.
Ronald said
How to backup database in standby db when small space available, primary db data large?
Pavan DBA said
you can take compressed rman backup. if still space is not sufficient, you need to add space first.
vangapalli ramya said
Hai sir
I am looking for Oracle DBA online training .
I don’t know where to start with .I used to work to CTS as a fresher and after that because of my baby had three years gap .
So i want to start my carrer again ..can you please help me with what to start and how to prepare .
Online training s for Unix Linux DBA
Pavan DBA said
please contact admin@thetechmaster.in
mithilesh said
what is mount point and . IF YOU WANT TO EXPORT SCHEMA OF 100G AND SPACE LEFT ON MOUNT POINT IS 20G THEN WHAT SHOULD YOU DO
Pavan DBA said
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.
arun said
hi am looking for some one who can give me oracle core and rac dba training with real time examples.
Pavan DBA said
for dba training, plz contact me at mymailbox.21@gmail.com. i will provide more details.
Jhansi said
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?
Pavan DBA said
depends on the company and project you join, some will provide you learning on exadata. but as of now, dba, apps dba with rac is fine
Balu said
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
Pavan DBA said
both are having equal opportunities. i suggest to learn both
Ramesh said
Looking for DBA job in Hyderabad
Ankit Mishra said
hi sir
i m doing Mca .and i want to start reading for DBA .i have some basic knowlage of SQL.Could u tell me how to start study for DBA and book for preprastion……?
Thankssss. sir
Pavan DBA said
you can do self learning by reading oracle documentation and other books. list is there in my blog’s “recommended books” section.
also there are lot many videos in youtube.
kiruba said
can you tell me how to recover if controlfiles and redolog files got corrupted at the
same time , if there is no backup
Pavan DBA said
if u have controlfile trace u can recreate controlfile. during that time u will open db with resetlogs which will re-create redologs also
satish said
I want to know the online classes information
course: Oracle dba 11g
batch starting date ,free details
Pavan DBA said
hi satish, batch will be starting approx by 20th july 2016. plz send email to mymailbox.21@gmail.com to know other details.
sibu varghese said
Hello Pavan.. have you tried this example?
*************************************************************************
Kiruba said
July 2, 2015 at 12:06 PM
0 0 Rate This
can you tell me how to recover if controlfiles and redolog files got corrupted at the
same time , if there is no backup
Reply
Pavan DBA said
July 2, 2015 at 11:21 PM
1 0 Rate This
if u have controlfile trace u can recreate controlfile. during that time u will open db with resetlogs which will re-create redologs also
Reply
Pavan DBA said
yes. i did it lot many times
satyanarayana said
I have a small doubt that how many redolog groups can be created in the database please clear mee.
Thank U
Pavan DBA said
theoretically unlimited, but practically 1000+
satyanarayana said
Thank u
Sharath kumar Vadla said
Hi Pavan,
Greetings of the day.
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.
Thanks,
Sharath
Sharath.vishwa@gmail.com
Pavan DBA said
yes Sharath. it is possible to learn and change the career to IT. If you need any more details, whatsapp me at +971558926727
kishore said
Hi pavan,
i faced an interview question and didnt find the answer. question is what is the un editable parameter in pfile. please reply the answer.
Regards,
kishore
Pavan DBA said
question is not clear. in pfile we can edit any parameter, but only dynamic parameters will be effected immediately.
Ganesh said
Hello Sir,
I am an IT professional having 4.5 years of experience into Application support profile.
I have cleared my OCA certification for Oracle 11g version by reading theortically.
I want to secure a job in Oracle DBA domain and willing to take training from you.
I am based out of mumbai.
Can you please assist?
Pavan DBA said
hi ganesh, i am not giving training now.
swapniljadhav50@gmail.com said
want to learn oracle apps dba. did my b.tek in 2010
Pavan DBA said
what help you need?
BALAGOPAL R said
hi pavan
i want to do hadoop course from a bast placement oriented institute from chennai or bangalore.so plz tell me some top institutions name…………..
Pavan DBA said
hi i dnt have idea about it
Amarnadha Reddy K said
Hi Pavan,
This is Amarnadha Reddy K (empid:484280, Cognizant) ,I am your old student.I got Opertunity in CTS (Chennai) and joined recently in Chennai Location.
Present i am in PDP, So Please let me know if your having any opertunity in your projects in Hyderbad.
Thank you so much..
Regards,
Amar
Pavan DBA said
good to hear. hope u got project by now
BAJI SHAHEED SHAIK said
PLEASE HELP ME TO GET JOB IN A FIELD OF DB2 DBA (IBM DATA BASE TOOL ) SIR .
Pavan DBA said
any openings will be posted in my blog itself
Anand T said
Hi Pavan
Hope you are doing well. Please help me ,I need a unix shell script to check for multiple RMAN jobs running for DB.if there are multiple RMAN Jobs running then it should be trigger an email .
Thanks,
Anand T
Pavan DBA said
u can get one from google
Krish said
Hi Pavan sir,
I am a junior DBA willing to take OA exam. Kindly send me the dumps for SQL funda to my mail ID : “deepakbkm.v@gmail.com”. Pleae do the needful.
Thanks in advance !
Regards,
Krish
Pavan DBA said
plz send mail to mymailbox.21@gmail.com
David said
Hi Sir,
We have block corruption in our NoArchivelog Mode DB. There is no rman backup available. Please suggest us to fix the block corruption.
Pavan DBA said
any other backup available?
Javeed said
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
Pavan DBA said
u can do it online now from any good institute
kishore said
Dear Pavan,
my database SGA Size is 5Gb and my Table size is 10GB. When i SELECT 10GB table How it is going to fit into buffer cache.
I want to know internal process of how buffer cache will run when table size is greater than SGA size.
Raja.P said
I need help to prepare for “PeopleSoft Enterprise 9 HRMS Fundamentals (1Z0-218)” exam and I’v sent you an email regarding this. Please reply.
Pavan DBA said
hi raja, i haven’t received your mail. also, i don’t have much idea about functional side.
Raja.P said
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.
Pavan DBA said
sure. let me reply
bheemaraju said
hi sir gud morning i am bheemaraju i need oracle rman clonig database steps pls send to my mail bheemaking3@gmail.com
Pavan DBA said
already there in my blog. plz check
Ramakrishna Y said
hi boss,
when i am tryng to edit a pfile it doesn’t allow me save the changes it says: “access is denied”
Pavan DBA said
check who is the owner of the file and u need to modify as that user only.
Anand said
I am stuck at one issue and unable to resolve it. It would be very kind of you if you could stretch some support to me.
I have oracle db installed into my system and i want other team member to get connected to my db via toad. They have client installed in their machine.
I am facing error as follows:
ORA-28547: connection to server failed, probable Oracle Net admin error
Although I have tried all the solutions given in google but could not have it resolved.
Pavan DBA said
from their machine do they able to do tnsping to server?
Gopi Phaneendra said
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..
Pavan DBA said
hi gopi, yes dba will be a good choice for developers.
Nagu Abbadasari said
Thanks
akhil said
Hi
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.
akhil
Pavan DBA said
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.
sreerekha said
Hi Pavan, I tried to enable Database broker as below.
DGMGRL> ENABLE CONFIGURATION
But this operation is not getting completed. It seems like stuck. When checked from another window it gives the below result.
DGMGRL> SHOW CONFIGURATION
Configuration – orclprm
Protection Mode: MaxPerformance
Databases:
orclprm – Primary database
orclstb – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
ORA-16610: command “ENABLE CONFIGURATION” in progress
DGM-17017: unable to determine configuration status
Please help me to resolve this situation. I followed your document. The only difference i made is used same host for primary, standby and dg configuration. Is it not possible to do like that.
sreerekha said
Thankyou Pavan. It was listener/tns issue. Resolved it.
Pavan DBA said
did u used VMware machines?
harsha pydi said
Hi Pavan,
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 document
http://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.
many Thanks,
Harsha
harsha pydi said
Also please check your email . thanks
Pavan DBA said
http://gavinsoorma.com/2012/06/deploying-the-12c-management-agent-on-windows-using-the-12c-cloud-control-self-update-feature/
harsha pydi said
Dear Pavan,
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.
SQL> exec mgmt_target.set_agent_tzrgn(‘hkhgc02mypdb01.aswatson.net:3872′,’Etc/GMT-8’)
BEGIN mgmt_target.set_agent_tzrgn(‘hkhgc02mypdb01.aswatson.net:3872′,’Etc/GMT-8’); END;
*
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…..
C:\oracle\product\agentbase\agent_inst\bin>emctl verifykey
Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation. All rights reserved.
—————————————————–
verifykey failed: unable to connect to the agent at https://hkhgc02mypdb01.aswat
son.net:3872/emd/main/ [hkhgc02mypdb01.aswatson.net]
—————————————————–
ANy suggestions on this please. Many Thanks
Regards,
Harsha
Pavan DBA said
for windows I observed so many problems… check with oracle MOS docs only.
harsha pydi said
Hi pavan,
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
Vikas said
Hi Pavan Sir,
Can we restore oracle dump file in to mysql database OS is OEL 4.1
Pavan DBA said
if u mean to say export dump, it is not possible. u need to use some tools to do that.
Vikas said
Yes export dump, Ok thank you …..
harsha pydi said
Hi Pavan,
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.
this is the scenario of their requirement.
Dump and export table “ASW_AUDIT_TRAIL_2” , 2014 Jan & Feb partitions and China IT will backup them to GZ file server.
Any suggestions please.. !!
harsha pydi said
Hi Pavan,
please have a look at this , if this is ok.?
expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir TABLES=(T1:P1,T1:P2)
Also check how many partitioned tables in DBA_TABS_PARTITION..?
Pavan DBA said
already provided solution over mail
prathap said
[applebs2@usirvsdevebs06 ~]$ opatch lsinventory
Oracle Interim Patch Installer version 1.0.0.0.64
Copyright (c) 2011 Oracle Corporation. All Rights Reserved..
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)
opatch lsinventory command hanged.No logs genrated /.patch_storage.Can you please suggest on this.
regards,
MPR
Pavan DBA said
as it is mentioned you need to use latest opatch version. 6880880 – download this patch to upgrade you opatch version.
see this link – https://community.oracle.com/thread/2365256?tstart=0
prathap said
hi
can you please suggest on this.
SQL> select open_mode from v$database;
OPEN_MODE
———-
MOUNTED
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 3389864191 generated at 09/07/2011 06:40:16 needed for thread
1
ORA-00289: suggestion :
/usr2/app/oracle/flash_recovery_area/1_68853_528537076.dbf
ORA-00280: change 3389864191 for thread 1 is in sequence #68853
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/usr1/app/oracle/oradata/alpha/alpha_system01.dbf’
ORA-01112: media recovery not started
regards,
prathap
Pavan DBA said
already posted article in my blog for this. plz go through it
Vijay Dodla said
Dear Pavan,
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!
Regards,
Vijay
Pavan DBA said
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.
also, thank you for your words about my blog.
deekshith0007 said
Hi Pavan,
i would like to want real time experience and want to attejnd classes for dba and rac plz let me know when it is possible .
Pavan DBA said
already replied to your earlier comment.
deekshith0007 said
hi i would like to learn oracle dba plz let me know wehen ur going to start new classes and to want real time experience
Pavan DBA said
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
prathap said
HI PAVAN SIR,
DATABASE—11.2.0.3.0
APPS————12.1.3
OS———LINUX-X86-64
Recently i got error in my PRODUCTION alert log file.I raised SR for this.
The SR suggested (Please download the patch 14489591 and apply it in the client side) to apply 14489591.
How to apply this patch in client side?
HERE IS ERROR DEATAILS BELOW………
Completed checkpoint up to RBA [0xd29e.2.10], SCN: 5998134606816
Mon Apr 07 22:50:12 2014
Completed checkpoint up to RBA [0xd29f.2.10], SCN: 5998134609987
Mon Apr 07 22:51:39 2014
Errors in file /u01/PROD/db/tech_st/11.2.0/admin/PROD_usirvsprdedb01/diag/rdbms/prod/PROD/trace/PROD_ora_31894.trc (incident=421201):
ORA-03137: TTC protocol internal error : [3149] [] [] [] [] [] [] []
ORA-03149: Invalid Oracle error code
Incident details in: /u01/PROD/db/tech_st/11.2.0/admin/PROD_usirvsprdedb01/diag/rdbms/prod/PROD/incident/incdir_421201/PROD_ora_31894_i421201.trc
Mon Apr 07 22:51:45 2014
Dumping diagnostic data in directory=[cdmp_20140407225145], requested by (instance=1, osid=31894), summary=[incident=421201].
Mon Apr 07 22:51:46 2014
Sweep [inc][421201]: completed
Regards,
prathap
Pavan DBA said
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
prathap said
i have applied this patch by using Opatch.BUT my issue did not resloved.Any suggestion on this?
regards,
prathap
Pavan DBA said
contact oracle support itself conveying the same.
prathap said
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.
regards,
prathap
chinmoy said
Dear Pavan,
Please read my explanation & correct me if is there any wrong steps.
The details is given below,
Sqlplus scott/tiger@mandb
SQL>select * from emp;
SQL>update emp set salary=10000 where empid=5;
SQL>commit;
1. sqlplus command is fired, the user process (client) access sqlnet litener.
2. sqlnet listener confirms that database is open & creates a server process.
3. server process allocates PGA into memory. Each server process has its
Own PGA area, PGA contains data & control information of server process.
PGA ( Private Sql area & Session Memory ).
4. ‘Connected’ message returned to user.
5. select * from emp;
6. select statement is parsed into shared pool ( library cache & data dictionary
cache) , this is called hard parse because the select statement is not already
present in shared pool. An executable code is generated for select statement
in shared pool. Server process got the all necessary information for select
statement before fetch.
7. server process takes the data from data file , loads into the buffer cache, puts
into the most recently used(MRU) area & gives the data to the client via PGA.
8. update emp set salary=10000 where empid=5;
9. Before update statement , I want to say that empid=5 has a salary=8000
10. update statement is parsed & executed in shared pool.
11. server process checks( via PGA) buffer cache for data is available or not.
12. In our case data is available in buffer cache.
13. So data is updated into buffer cache with new salary i.e., 10000 & keep
old copy i.e., 8000 into undo blocks for rollback.
14. update is placed into redo log buffer.(
15. row updated message returned to client via Server Process(via PGA).
16. commit;
17. server process sends a commit to redo log buffer , a SCN is assigned.
18. LGWR process writes redo buffer contents to current online redo log
file on disk & also write current log sequence no. in control files on disk.
19. CKPT process updates the header of all control files with new SCN.
It signals DBWR to write modified blocks (dirty blocks) from buffer
cache to data file on disk.
20. commit complete message returned to user.
21. DBWR process writes modified blocks from buffer cache to data file
on disk & CKPT updates header of the data file with new SCN.
22. Undo data blocks associated with this transaction are released from
Buffer cache.
23. exit
Bimal Shahi said
Hi,I am Asp.net Developer with 5 yrs exp.
I want to learn Oracle and want a career in Oracle DBA.
How to proceed.
I knew basics of Oracle DBA.
Pavan DBA said
if u want totally to shift to dba career, then u need to concentrate on adv topics like tuning, asm, cloning, upgrade, migration, dataguard, asm, rac etc both theory and practically also.
Simli said
Hiii,
Why is that Very less databases exist on windows when compared to Unix flavors?
Thanks in advance
With Regards
Simli
Pavan DBA said
because unix flavor OS are more stable than windows.
DB DB said
Hello Sir ;
Good morning 1
In linux os , a user already created by #.
in this case , how do i find users primary group & secondery group ?
[root@oel5 ~]# id test
uid=503(test) gid=504(share) groups=504(share),505(oragroup)
Thanks in advance ..
Pavan DBA said
504 is primary group and 505 is secondary group
DB DB said
Thanks sir ;
By looking the answer how do we assume –> whether it is primary or not ?
Pavan DBA said
select database_role from v$database;
DB DB said
Hello Sir
Good morning !
I have a doubt in oracle architecture.
” Each user (process) having separate pga memory.”
Consider 10 users are trying to connect to the database , PGA memory is ( 100 MB) ,
1) In this case , Does oracle allocate 10 MB per user for every time ?
2) Is it shared (memory) equally every time ?
3) If any one user needs additional memory for his operation , what ‘ll oracle do ?
Consider user is opening 5 sessions at a time ,,
3) Does oracle allocate ( 10mb memory ) for all sessions ? –
5) Does oracle allocate (10mb ) for each sessions ?
Regards
DB DB
Pavan DBA said
first of all let me correct your statement.
“each server process (not user process) will have separate pga memory”
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
raja said
usually how many databases will be there in an organisation and what will be their size!!!
Pavan DBA said
it varies between clients and project.
Dinesh said
Hello Sir ;
I am trying to upgrade my database from 10.2.0.1.0 to 10.2.0.4.0 .
Before upgrading my local database , i have issued following query..
SQL > SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;
COMP_NAME VERSION STATUS
Oracle Database Catalog Views 10.2.0.1.0 VALID
Oracle Database Packages and Types 10.2.0.1.0 VALID
Only above two components installed. Other components are not installed – Why so ?
Eg : (Oracle Workspace Manager , JServer JAVA Virtual Machine, etc ..)
I did manual database creation. Even i execute utlrp.sql script., no improvement.
I do not have any invalid objects in my database.
1) What was the reason for missing components ?
2) How to resolve this problem ?
Pavan DBA said
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.
Dinesh said
Ya .. thanks sir. got it.
archana said
hi sir,
dis is archana…in my test database ,i have created one table n issued select current_scn,scn_to_timestamp(current_scn) from v$database; it shows like ..
CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)
———– —————————————————————————
209184 22-JAN-14 10.22.24.000000000 AM
without doing any transaction ,after 17 mins i have queried the same,it results
CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)
———– —————————————————————————
209542 22-JAN-14 10.39.07.000000000 AM
without any transaction how scn will increased? what exactly will happen in background regarding scn,,kindly expalin n share if u have any docs or links regarding SCN background functionality/
Pavan DBA said
even though externally we don’t do any transactions, oracle will internally keep on updating dictionary tables. that is the reason why u will see scn is changing.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:48052838748707
Anand T said
Hi Pavan,
I am looking for a process to truncate records in AUD$ table
Please Help me how to clean up older records(eg:older than Jan 1 2013) in AUD$ table .
Thanks,
Anand T
Anand T said
Pavan,
+++++++
Topic related to dataguard standy Lag
On PHYSICAL standy
SELECT name, value, datum_time, time_computed FROM V$DATAGUARD_STATS WHERE name like ‘apply lag’
NAME VALUE DATUM_TIME TIME_COMPUTED
——————————– ——————– —————————— —————————-
apply lag +00 05:40:50 01/17/2014 11:11:54 01/17/2014 11:11:56
In this case archives are applying Primary and standby are in sysnc but there is a lag(05:40:50)
The requirement is to get an email when lag is more than 30m.
Thanks,
Anand.T
Pavan DBA said
is that value (5:40:50) constant always? if not, we shouldn’t worry about that. also what mode is configured for standby?(max perf/max protec/max avail?)
http://docs.oracle.com/cd/E18283_01/server.112/e17110/dynviews_1103.htm
Anand T said
Hi pavan ,
sorry for the late response
The value is keep changing and but we need to monitor the lag .
stand by is max performance.
Thanks,
Anand T
Pavan DBA said
anything you have identified from primary/standby alert log file?
Pavan DBA said
just write delete statement as below
SQL> delete aud$ where to_char(timestamp,’DD-MON-YYYY’)=’01-JAN-2013′;
prathap said
[oratest@pc77 ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.1.0.7.0 – Production on 17-JAN-2014 10:30:14
Copyright (c) 1991, 2008, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12532: TNS:invalid argument
TNS-12560: TNS:protocol adapter error
TNS-00502: Invalid argument
Linux Error: 101: Network is unreachable
oratest@pc77 bin]$ ./netca
Oracle Net Services Configuration:
Xlib: connection to “:0.0” refused by server
Xlib: No protocol specified
java.lang.NullPointerException
at oracle.ewt.lwAWT.BufferedApplet.(Unknown Source)
at oracle.net.ca.NetCA.(Unknown Source)
at oracle.net.ca.NetCA.main(Unknown Source)
Oracle Net Services configuration failed. The exit code is -1
HI sir,
unable start listener and unable to run netca……this software is installed in vmware for practice purpose…
Pavan DBA said
hi prathap, u need to disable xhost using root user with below command
# xhost +
after that try running netca with oratest user. it will work. also check if network configuration is done properly in vmware.
DB DB said
Good Morning sir ,
I have a question in data guard ( oracle 10g).
Primary site : primedb
Standby 1 : clonedb
standby 2 : dummydb
Consider i want to configure cascade standby ( clonedb , dummydb)
Here , my requirements are ,
primedb should not know dummydb details , dummydb should not know primedb details.
1) Could you please show one example , how do i set db_file_name_convert & log_file_name_convert parameters in all init.ora files ?
Thanks in advance ..
Pavan DBA said
i didn’t got what u mean by cascade standby? can u plz explain more?
DB DB said
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.
Thanks sir.
Pavan DBA said
ok. in such case, u need to set db_file_name_convert and log_file_name_convert parameters on standby_2 specifying standby_1 and standby_2 locations
Seshu said
Hi Pawan,
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.
And one more query how to uninstall the patch if needed.
Regards,
Seshu
Pavan DBA said
hi seshu, there is difference between applying patch and patchset. the document which is there in my blog is for applying patchset n opatch is for applying patch
archana s said
in real time ,,is it mandatory to take backup of home,db backup,inventory,opatch while applying pacthset update
Pavan DBA said
yes. it is very much required.
DB DB said
Good Morning Sir ;
I am DB DB. I have a question about database performance.
My production table having ‘n’ number of indexes.
Suppose i want to rebuild anyone index due to increase database performance.
1) How do i know , which index wants to be rebuild ?
2) Which one impacts database performance really?
Table level fragmentation and Index level fragmentation .
Thanks sir.
Pavan DBA said
1. you can use script mentioned in below link
https://pavandba.com/2009/12/14/script-to-find-which-indexes-need-to-rebuild/
2. if your query is using index and index is fragmented, then it will create more impact. otherwise, table fragmentation will impact more.
DB DB said
Thanks sir.
Amar said
Hi Pavan,
Thanks for sharing ur experience with us.
I have one requirement, for that need a dynamic script for to identify the which table and index fragmentation is required for Database/schema levels.
i coudn’t find the script Google and metalink,
Could you please provide me the script if you have?
Thanks in Advance.
Regards,
Amar
Pavan DBA said
script for to identify the which table and index fragmentation is required for Database/schema levels.
i didn’t get u by above line. can u plz explain?
kumar said
i have a table it has an interval partitions . interval partitions was complete how can i add interval partitions on existing table ..
let me explain about interval partitions please ..
Pavan DBA said
http://docs.oracle.com/cd/E18283_01/server.112/e16541/part_admin002.htm
DB DB said
Hello sir ;
I am DB DB from chennai..
When doing hot backup , why we need to specify until cancel option.
Recover the database using backup controlfile option.
————————————————————————-
SQL> recover database using backup controlfile until cancel;
Thanks in advance ..
Pavan DBA said
Hi, you are mixing up two things. what you asked is recovery (not hot backup). i believe u r asking about hot backup cloning. am i right?
DB DB said
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;
Thanks sir.
Pavan DBA said
if we are doing on same server, we will recreate control file. in that case, we dont need this command to run. it is needed only when we clone to a different server
Seshu said
Hi Pawan,
Could you please explain me in detail about ROLLING PATCHES, and also please explain me how to install the patches in detail.
Regards,
Seshu
Pavan DBA said
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
Rohit said
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 ..
or we need to do some prior steps before confiqure RMAN..
please eleborate more about above two command what that means..
your reply is really appreciated…
Thanks and Regards
Rohit
Pavan DBA said
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)
moreover, you don’t need to configure channel separately while taking backup, instead you can configure your default device type in rman configuration parameters
RMAN> show all;
RMAN> configure default device type to disk; –> this is what it shows originally. if u want to take backup to disk, just continue with this.
if u want to take into tape, do this change
RMAN> configure default device type to sbt_tape; (or sbt1 etc)
Rohit said
Hi Pavan,
Great thanks its very clear now…. 🙂
Thanks and Regards
Rohit
Rohit said
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..
Thanks and Regards
Rohit
Pavan DBA said
can you let me know what method you are using to do re-org of the tables?
Rohit said
for SAP Database we are using BRTOOLS and for Non-SAP database we are using normal alter table tab_name move command;
alter index ind_name rebuild online;
Pavan DBA said
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.
Rohit said
Hi Pavan,
Greetings,
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..
Thanks and Regards
Rohit
Pavan DBA said
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.
kumar said
hi pavan
i have one database around 900gb size .every day i was taken rman backup it was run very slow . it takes around 15-20hrs . how can i improve the backup speed ..?
Pavan DBA said
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
dinesh said
Sir , I am dinesh ( oracle enthusiast )
I am learning Oracle Architecture . I have two questions.
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
4) What’s is close relationship between LSN Vs SCN ?
Thanks Sir.
Pavan DBA said
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
dinesh said
Very thanks sir.
rakesh kumar padey said
on same server i need to do….any mannual by RMAN….I want to minimize downtime…..
Pavan DBA said
if it is same server, u dnt need to use expdp/impdp. u can upgrade the database using upgrade procedure. for docs, check out google
rakesh kumar padey said
wht about dictionary views of enterprise edition which z not used in standard edition ..after upgsradation of db .i wl b rmove or not?wht abt SYSDM USER WHICH Z not supported in standard edition..
Pavan DBA said
you don’t require to worry about them. having them will not cause any harm to database
rakesh kumar padey said
thanku sir…but 1 more question ….how much SGA size we can give ( i mn max SGA size it wll support)
Pavan DBA said
it depends on how many transactions hit your database and other factors.
rakesh kumar padey said
hallo sir,
please give me a favour..
I need to upgrade my database from 10.2.0.1 enterprise edition to 11g statandard edition.
Our database size is about 2-4 TB.
I don’t want to use exp/expdp or imp/impdp.
is there any other alternative then provide me mannuals
Regards,
Rakesh Kumar Pandey
(pandey.oradba@gmail.com)
Pavan DBA said
do you want to do in same server or different server?
krishna said
Hi pavan i am getting this error while iam running showall cmd in rman n my database version is mentioned below
RMAN> showall;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found “identifier”: expecting one of: “allocate, amper, alter, advise, backup, beginline, blockrecover, catalog, change, connect, copy, convert, create, crosscheck, configure, duplicate, debug, delete, drop, exit, endinline, flashback, grant, host, import, {, library, list, mount, open, print, quit, recover, register, release, replace, report, renormalize, reset, restore, resync, rman, run, rpctest, repair, revoke, set, setlimit, sql, switch, spool, startup, shutdown, send, show, test, transport, upgrade, unregister, validate”
RMAN-01008: the bad identifier was: showall
RMAN-01007: at line 1 column 1 file: standard input
SQL> select * from v$version;
BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – Production
PL/SQL Release 11.1.0.7.0 – Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 – Production
NLSRTL Version 11.1.0.7.0 – Production
Pavan DBA said
there should be a space between show and all.
RMAN> show all;
deepthi said
Hi Pavan,
i Installed 12cR1 on my servers how can i upgrade to 12cR3?.
please reply me .
Thanks,
Deepthi
Pavan DBA said
Hi deepthi, 12cR3 is not yet in the market. 12cR1 is the latest one.
kumar said
how to restore and recover database from tape drive is it any chance to restore . plz give a suggestion on that concept .
my data was back up directly to tape device .
Pavan DBA said
you need to use channel allocation to tape. rest everything rman will take care
kumar said
Hi Could you please post a document on Cross-platform migration of databases in your blog
Pavan DBA said
sure. I will post soon to my blog.
kumar said
plz give the solution about this error
ORA-00474: SMON process terminated with error
PMON (ospid: 28013): terminating the instance due to error 474
System state dump is made for local instance
System State dumped to trace file /u01/app/diag/rdbms/epccb/EPCCB1/trace/EPCCB1_diag_28019.trc
Thu Oct 17 10:50:46 2013
ORA-1092 : opitsk aborting process
Trace dumping is performing id=[cdmp_20131017105046]
Instance terminated by PMON, pid = 28013
Pavan DBA said
request to check the trace file for more information
Naveed said
Hello sir.,
can you please explain me about the following questions,
1.Can v do the job with export/import which we can do with sql*loader ?
2.what z d diff btwn sql * loader and export/import ?
thank you
Pavan DBA said
1. yes we can
2. sql * loader will help u to load data from a text kind of file. exp/imp will be doing data transfer between oracle databases (not from files)
Naveed Mohammad said
Hello sir
I am naveed mohammad oracle dba fresher.One of interviewer asked me about /proc directory can you tell me about /proc directory and how it is useful for DBA
Thank you
Pavan DBA said
we don’t use it as dba. it is for OS admins to take care.
dineshbabu said
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 ?
Thanks sir;
Pavan DBA said
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.
Adolina said
Hello,
If Listener.log file destination is full what will happen? Whether connections through the listener will go hang? [DB version 10.2.0.4 & 11.2.0.3]
Pavan DBA said
If listener is not required to write anything then still it will accept connections. otherwise, it will throw error when users trying to connect.
Shekar Gupta said
Hello,
Hope you are doing well. I am having couple of queries regarding Patching.
1)What happens if we do not apply these CPU patches?
2)In case of global inventoy corrupted, is it possible to apply opatch without re-creating the global inventory by using opatch -no_inventory option?
Thanks
Pavan DBA said
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
naresh kumar said
sir,
how to flush a single sql statement from a shared pool?
Like i have seen from some of the blogs that after getting sql_id, we need to find the address, hash_value.
To flush that it has some syntax
>exec dbms_shared_pool.purge(“address”,hash_value”,”?”)
but in the question mark(?) place, some “c” is written over their. may i know what does it represents
thnx in advance
Pavan DBA said
C stands for cursor. see this – http://psoug.org/reference/dbms_shared_pool.html
naresh kumar said
thnks sir
naresh kumar said
Hi sir,
If backup of archivelog files failed because of missing one of the archived log file in rman. how can v rectify the issue using cross check command. Sir, please explain with some explanation.
thanks
nareshkumar
Pavan DBA said
RMAN> crosscheck archivelog all;
RMAN> backup archivelog all;
run above commands and it will be successful.
naresh kumar said
Sir,
if v use RMAN>crosscheck archivelog all;
RMAN>resync catalog;
Rman> delete expired archivelog all
Is both of them results the same thing
Pavan DBA said
yes
Rohit said
Hi Pavan,
can u please let me know details steps for oracle 10g installation and database creation on windows system.
Thanks and Regards
Rohit
Pavan DBA said
i have a document already under important docs page. plz check thr
naresh kumar said
Hi pavan,
what type of questions we generally get in managerial /project round
thanks
naresh kumar
Pavan DBA said
questions like
1. why we need to hire you?
2. what are your goals in next 5 yrs?
3. how you will manage the team? etc
naresh kumar said
thnks sir
Pavan DBA said
welcome
Adolina said
Hello,
1) Is it necessary to configure /etc/host file on the database server?
2) Without listener.ora file, is it possible for oracle to know which port to listen when multiple listeners present?
Pavan DBA said
Hi,
1. it is not necessary if you are resolving the server by ip address. if you are using hostname, then you need it
2. it is not possible. listener.ora is mandatory.
Adolina said
Thanks for answering my questions.
Pavan DBA said
u are welcome
Naveed said
Hi Pavan.
Can you tell me the answer for this as am beginner for oracle dba
6) In the early morning u came to your office then u login your username and password, when u execute a command sqlplus then it create an error. How u solve that problem?
Pavan DBA said
it depends on what error we are facing. first thing we check is syntax or else the corresponding error.
Anand said
Hi Pavan,
I meant to say is how to identify which database process is hogging cpu resource.
As per your reply we can identify by joining V$process and V$session Right?
Thank you pavan
Pavan DBA said
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.
easiest way if to have EM grid control and check the issue.
Kiran Gupta said
Hi Pavan,
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 !!!
Statistics
———————————————————-
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
56503 rows processed
The real problem i have now is , when i check after few days (say 3-5 days) the elapsed time is around 3 to 3.5 mins again for the same query ?
Can you plz help/suggest me a permanent solution.
NOTE: I HAVE AROUND 20 QUERIES WHICH ACCESS THE SAME 5-6 TABLES WHICH TAKES MORE THAN 6 MINS AND NEEDS TO BE TUNED.
Your response and help are appreciated.
Kiran Gupta said
Adding to above the present trace output ::
Statistics
———————————————————-
615 recursive calls
0 db block gets
6073012 consistent gets
22108 physical reads
1316 redo size
8044010 bytes sent via SQL*Net to client
60782 bytes received via SQL*Net from client
3768 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
56505 rows processed
Pavan DBA said
are there frequent DML’s operations being performed on those 5-6 tables? if so, you need to collect stats immediate after that. also, how is your stats job scheduled?
Kiran Gupta said
Pavan ,
I have manually collected the stats, but not sure if there is any schedule( newjoinee in this proj) , have to check.
Yes there are around 20 diff queries accessing those tables, could you help me with the process (cmds) to check previous stats info and also with a script to schedule which collects the stats.
BTW what does the trace output stats convey ( All ‘0’ values – check my prev post) & even the recent trace output??
Thnx for the swift response.
Pavan DBA said
you can get the prev stats info from dba_tab_stats_history. also, if you would like to know when the stats job executed in the past, you can check in dba_optstat_operations.
I have already a script in my blog and if you want to use that, may be you need to customize according to your env.
https://pavandba.com/2009/11/06/script-which-collects-statistics-in-9i10g-for-tables-modified-5/
but if your db version is 10g or higer, then an automated job will be there already. you can check that info from
SELECT STATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = ’GATHER_STATS_JOB’;
regarding trace file output, i am not sure why it is showing 0 for all and correct values in next trace. but surely 0 will not be correct.
Anand said
Hi Pavan
I am getting confused,while using top command it shows multiple processes usage.Actually we have 3 databases in the same server
how to find out which database process and user session hogging the cpu resources .
Thanks in advance.
Pavan DBA said
you need to get the process id (pid) and based on that you need to verify in every database about which session is causing the issue. if you have EM or EM grid control, better use that
Anand said
Suppose if we have 10 databases in the same server ,so we need to verify every database right ?
Is there any way to identify the database process usage using top command at os level.
Pavan DBA said
Hi Anand, as far as i know, there is no way until we write some shell script to check in every database
Naveed Mohammad said
Hi Pavan
What do you mean by check every database ?
On Wed, Jun 19, 2013 at 12:31 PM, Pavan DBA’s Blog wrote:
> ** > Pavan DBA commented: “Hi Anand, as far as i know, there is no way until > we write some shell script to check in every database” >
Pavan DBA said
as you said there are 10 databases, we don’t have option directly to get the database info. we need to connect to every database and check that
Shekar Gupta said
Hi Pavan,
While upgrading why we run the gather stats for dictionay?
Pavan DBA said
when we gather stats for dictionary, it will speed up the upgrade process and upgrade is nothing but modifying dictionary objects.
see this
http://docs.oracle.com/cd/E11882_01/server.112/e10819/statistics.htm
Naveed Mohammad said
hai Pawan
can you explain me this,
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?
Thanking you
Pavan DBA said
first u need to identify to what possible value u can resize that and then issue below command
SQL> alter database datafile ‘path’ resize yourpossibleresizevalue;
to get the possible resize value, use below script
https://pavandba.com/tag/possible-resize-value-for-a-datafile/
Shekar Gupta said
Hi Pavan,
LOCAL_LISTENER parameter needs to be set in parameter file for dynamic registration if the standard port 1521 is not used. Am i correct in my understanding?
Does LOCAL_LISTENER parameter have any other functionality?
Pavan DBA said
LOCAL_LISTENER parameter is used in case of dynamic registration whether listener is using 1521 or not. advantage of local_listener will be more in RAC instance rather than stand alone. see this
http://anuj-singh.blogspot.co.uk/2011/05/how-to-set-locallistener.html
Anand said
Hi pavan
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.
Pavan DBA said
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.
Anand said
Actually In Rac memory is shared between the resource requested instances
in these situation how can we made index scan in memory while sharing requested resource (cache fusion).
Pavan DBA said
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.
Anand said
Thanks pavan
Anand said
Hi pavan
I have created asm diskgroup externel redundancy later i need to make it as normal redundancy
Is it possible change redundancy level after creating a diskgroup ?
Pavan DBA said
not possible. u need to drop and recreate diskgroup. see this
https://forums.oracle.com/thread/668772
Adolina said
Hello,
I am having one question regarding Listener.
What is IP=FIRST in listener.ora file?
Pavan DBA said
plz read these links
https://forums.oracle.com/forums/thread.jspa?threadID=572397
http://joordsblog.vandenoord.eu/2010/04/ipfirst-in-listenerora.html
Adolina said
Hello,
Thanks for your reply and thanks for the link you provided.
I have 1 more question regarding IP=FIRST.
IP=FIRST is also for single instance databases or only for RAC databases?
Pavan DBA said
normally its for rac database
Anand said
Hi pavan
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
Pavan DBA said
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
Anand said
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.
Pavan DBA said
if it is non-ASM, we can use relocating steps. but as it is ASM, we don’t have that option.
reg node eviction, we don’t need to take any action if it is caused due to network failure.
Sagar said
Hey Pavan,
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……
Regards
Sagar kasani
Pavan DBA said
check these links
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:44798632736844
http://vineetsoracleblogspot.blogspot.in/2011/08/does-select-statement-generates-redo.html
Sagar said
Thank you verymuch Pavan For your quick reponse ….. That was a good blog explaing each terms….
Shekar Gupta said
Hi Pavan,
Why do we need to run utlu112i.sql script?
Pavan DBA said
that script will tell you whether your current version database is ready for upgrade to 11.2.0.1 or not. also it will give you the steps to complete any pre-requisites before we start with upgrade.
Anand said
Hi pavan
when cloning the production database to test environment .how to change the database name
and why we need to change the DBID in which scenarios we need to change the dbid
Pavan DBA said
that depends on wht type of cloning you perform. if u do cold or hot cloning, then to change db name u need to re-create control file. but in rman cloning, oracle can handle it directly.
I have not seen any situation when we change DBID. so almost 100% we won’t do that in real time.
Anand said
Thanks for your support
please suggest me some useful links regarding database migrations using datapump exppdp/impdp and also database refreshing .
Pavan DBA said
http://docs.oracle.com/cd/E11882_01/server.112/e22490/toc.htm
Anand said
Hi pavan
user mail me i am unable to insert the data in my table . I came to know the user associated datafile was lost .
how to flashback the database to that particular state or scn
and how to get the scn of the particular loss of datafile
Pavan DBA said
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 WAJID said
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
i am waiting for your response
Thanking u in advance
Pavan DBA said
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.
Sabiha said
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.
Pavan DBA said
Hi Sabiha,
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 Dubakka said
Hi DBA Guru,
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.
At present I have 3 month of DBA exp…Please suggest and guide me.
Best Wishes,
Shri Haari.D
Pavan DBA said
Hi Sreehari,
First of all I am not DBA Guru… I am regular DBA with very minimal knowledge on concepts.
Reg openings, yes there are lot of openings in APPS DBA field. For training, you can always contact the numbers provided in the training page.
Anand said
Hi pavan
Is it possible to register multiple database version to catalog database and one more question
Please suggest me how to analyze AWR,ADDM reports
My query is running slowly its take long time to execute how to tune the issue
and tell me 2 major performance issues in the database
Pavan DBA said
yes. u can register. but it is not recommended as sometimes you may face trouble.
reg analyze of reports, in my blog’s “important docs” section, i have already 3 docs listed. you can read them.
for query running slow, check this link – https://forums.oracle.com/forums/thread.jspa?messageID=1812597
for pref issues, you can refer to forums.oracle.com
Anand said
Thanks Pavan for your valuable information.
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?
Pavan DBA said
Hi Anand,
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.
KUMAR said
hi pavan
unexpectedly i was drop one table in my one of the database . i don’t have any backup and my database in no archive log mode
if any possible to recover
Pavan DBA said
if u r using 10g database, it will be there in recyclebin. you can use below commands
connect to user with which table dropped
SQL> show recyclebin
SQL> flashback table tablename to before drop;
KUMAR said
Thank you pavan
in case of using 11g is there is any possibility to recovery or restore
Pavan DBA said
it is same as 10g. using recyclebin and flashback command
kumar said
Hi pavan
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
Pavan DBA said
yes. you can take export using expdp with SYS user. but I am bit confused here. Do you want to take export of only AUD$ table or entire SYSTEM tablespace?
kumar said
aud$ table only
Pavan DBA said
expdp directory=directoryname dumpfile=dumpfilename logfile=logfilename tables=’AUD$’
after entering above command, it will prompt for username, then give ‘/ as sysdba’
this will take export of aud$ table and then you can truncate it (don’t drop the table)
kumar said
thank you pavan
KUMAR said
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
Pavan DBA said
please provide your database version and OS version. also, ora-600 is internal error and for this you need to search in my oracle support (this you can do only if you have license)
KUMAR said
database version 11.1.0.6.0 64 bit
Red Hat Enterprise Linux 5.2
Pavan DBA said
urs is a bug and to fix that u need to upgrade to 11.2.0.1
shailendra m shinde said
SQL>connect to /as sysdba
SQL>startup mount
SQL>alter database recover until cancel using backup control file;
SQL>alter database recover continue default;
SQL>alter database recover cancel;
SQL>alter database open reset log
And my database was alive .. In my case i havent lost any data but i do not guarantee that this method is data loss free (im just a rookie)
This just brings my database back to life.
Pavan DBA said
may I know for which concept u tried this? (I mean which incomplete recovery scenario) and also the db version?
KUMAR said
how to release dml locks in oracle11g
Pavan DBA said
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
KUMAR said
how find how much memory allocate for single database in ram and how to find memory allocation plz provide steps for that
Pavan DBA said
do u mean how much memory is allocated? (how much memory allocate and how to find memory allocation – these two confused me)
KUMAR said
plz provide any materiel for oracle up gradation from 11.1.0.6 to 11.2.0.3 on Linux environment my database are on cluster environment and ocfs also . i was read your documents.
Pavan DBA said
check this upgrade guide, it contains everything
http://docs.oracle.com/cd/E11882_01/server.112/e23633/toc.htm
KUMAR said
plz provide resource allocation management
concept script
Pavan DBA said
http://docs.oracle.com/cd/B19306_01/server.102/b14231/dbrm.htm
KUMAR said
hi pavan
my alert log file show number of core dump file .how can i rectify the problem
Pavan DBA said
surely alert log will have info on why those core dump files are getting created. jst for that error message. if not raise a SR with oracle support
kumar said
how to Reclaim system tablespace . my system tablespace has 30 gb but it uses only 5gb i want to reclaim free space from system tablespace .
Pavan DBA said
check out a post in my blog on how to resize datafile to lower value
kumar said
hi pavan
we have 8 databases . in one of the database was generating large size of archive logs how can i reduce the problem .
Pavan DBA said
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
Ganapathi said
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 ..
Pavan DBA said
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.
Pinaki said
Hello Pavan,
Oracle recommends that once you apply PSU you continue to Apply PSU and not switch to CPU. This is mainly just for my interest. Can we apply CPU over PSU?
Thanks and With regards
Pinaki
Pavan DBA said
not possible. if really required, we need to deinstall PSU and install CPU
Sagar said
How to find a whether Oracle Server Software or Oracle client software is installed on the machine. Is there any command we can execute to get this information.
Pavan DBA said
select comp_name,status,version from dba_registry;
the above will give more component names if it is a database software.
subha19821subhasish said
Dear Pavan
can u tell me how to add an instance in OEM?
Pavan DBA said
I didn’t get you what you mean by adding an instance because OEM will work only for one db at a time. are you talking about grid control?
Anand said
Hi Pavan
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.
Thanks & Regards
Anand T
Pavan DBA said
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.
Anand said
Thanks very much pavan .
Pavan DBA said
u r welcome
Pinaki said
Hello,
I have few questions regarding Physical Standby Database.
1) Does increasing LOG_ARCHIVE_MAX_PROCESS fasten up the apply process?
2) Logs at standby db are being made by LGWR not by ARCH. Would it still help us increasing the number of log_archive_max_processes ?
Thanks and with regards
Pavan DBA said
sorry for replying late as i am out of city last week.
1. no. log_archive_max_processes is only on primary side. this will make archiving faster. on standby side anyway oracle will span MRP process if required.
2. nope. no effect.
Anil kukadeja said
I hv dne ocp frm ahemdabd. Ri8 nw i m searching d job frm last 1 year in gujarat nd gujrat is not relatd to IT.in gujarat u vl find al d things nd jobs except IT.wht shuld i do i m trying for dba on job portals for south region bt m nt getting responses.
Pavan DBA said
sorry for replying late as i am out of city last week.
it depends on how ur cv looks, r u trying as a fresher etc. plz email me ur cv to mymailbox.21@gmail.com
hima said
hi
how much we increase the sga size maximum
let ram size is 24g .right now sga size 10.
what are the changes needed to increase the sga size?
Pavan DBA said
Hi Hima, it depends on lot of factors on deciding whether to increase SGA size or not. Usually we do that if we observe performance issues with database.
Pinaki said
Hello,
DB Version: 11.2.0.2
OS version OEL5.4
1) OS patching will be performed on a server. What kind of things i need to do to verify everything is ok with the database afterwards?
Thanks and with regards
Pavan DBA said
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.
Murali said
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.
Pavan DBA said
Hi Murali, we don’t have any crash courses available.
Anand said
Hi pavan
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?
Pavan DBA said
without backup it is not possible. if u have backup u can restore it and you can do until time recovery
Anand said
Thank you pavan for giving the solution
subhasish said
yeah
Thanx pavan.I will do that
subhasish said
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.
Pavan DBA said
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
naresh kumar said
sir have you ever face a issue related to LISTENER?????i am facing a problem with my ebs server.LISTENER got crashed after i start aplplication.when i use only with database they work fine..plz help i
Pavan DBA said
when you use EBS, there will be two listeners. one will be at application level and other will be at database level. which listener you are talking about?
also, if it is database listener, please check listener log file and alert log file for more details.
Shuv said
Hii Pavan,
I have gone through Oracle 11gR2 data guard concept and administration docs.
As per the Oracle documentation, init file of the primary database is as follows:
Example 3-1 Primary Database: Primary Role Initialization Parameters
DB_NAME=chicago
DB_UNIQUE_NAME=chicago
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(chicago,boston)’
CONTROL_FILES=’/arch1/chicago/control1.ctl’, ‘/arch2/chicago/control2.ctl’
LOG_ARCHIVE_DEST_1=
‘LOCATION=/arch1/chicago/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=chicago’
LOG_ARCHIVE_DEST_2=
‘SERVICE=boston ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=boston’
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
My question is why the value is set as (ONLINE_LOGS,PRIMARY) for LOG_ARCHIVE_DEST_2?
Pavan DBA said
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
Shuv said
Hii Pavan,
Thanks for your reply.
I have one more question regarding this.
What will happen if the value is set as (STANDBY_LOGIFLE,STANDBY_ROLE)for LOG_ARCHIVE_DEST_2?
Pavan DBA said
nothing. you cannot perform switchover. thats it
Shuv said
Hii Pavan,
Thank you for your response and insight.
When Standby DB becomes primary, we need ORL instead of SRL.
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?
Pavan DBA said
yes
Shuv said
Thanks
naresh kumar said
Hi sir,
In an interview, they asked me ” what vil do to end to end instance of a delivery bill” and ” if you cant resolve the ticket what vil you do”. Sir, Please answer questions .
thanks
Naresh kumar
Pavan DBA said
if we can’t resolve the ticket, we will pass that to any other team who is responsible for solving it. if it requires any third party attention, then we will keep ticket in pending state
naresh kumar said
if it is related to production database ?. vil raising an SR can solve the issue?
Pavan DBA said
yes. if the issue is related to database and DBA couldn’t able to solve it, then we can take that to oracle support. in that case also we will place ticket in pending state
Naresh kumar said
Sir, what about the first question
“what vil you for end to end instance of delivery bill?(i thnk it has some grammatical mistakes)
Thanks
Naresh Kumar
Pavan DBA said
I am not quite sure about this because it doesn’t involve DBA terminology
KUMAR said
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
Pavan DBA said
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.
KUMAR said
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
Pavan DBA said
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.
namarta said
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
Pavan DBA said
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
Shuv said
Hii Pavan,
I have couple of basic questions regarding Standby Database.
According to the Oracle docs:
“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?
With Regards
Pavan DBA said
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
Rohit said
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..
Pavan DBA said
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.
kondareddy509 said
On Tue, Mar 5, 2013 at 8:36 PM, Pavan DBA’s Blog wrote:
> ** > 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.” >
sbsr466sankar said
sir i have knowledge in coredba …i want to join RAC …tell me wch one is good in RAC OR APPSDBA…….WCH IS BEST INSTUTUE IN HYDERABAD
Pavan DBA said
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
kondareddy509 said
hi pavan sir
How to skip next column and before the column will be deleted ,so please tell me querry in oracle…please send to my mail : kondareddy509@gmail.com
Pavan DBA said
request you to please explain more on what exactly is ur requirement.
kondareddy509 said
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….
Pavan DBA said
that depends on how you write query. in your select query use where condition which select only pqr column
Sriniv@s.. said
Hi pavan sir,
i have one doubt about ORACLE NETWORKING.i.e suppose i have 3 database in my own pc,can i connect internal databases with out using listener.?
Pavan DBA said
yes. to connect to databases within same server, u dnt need listener. it is required only when u try to connect from remote machine
Shuv said
Hi Pavan,
1)What’s the surest way to check whether a database has been cloned successfully?
Checking RMAN logs is an option. Any other ways to check?
2)I would like to know whether static entry in the listener.ora file required for the cloned database? DB Version 10.2.0.4
Thanks in advance
Pavan DBA said
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
rohit said
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..
Thanks and Regards
Rohit
Pavan DBA said
u can take awr report and check why high cpu is causing, i mean which wait event is triggering this. usually, a bad sql query will cause damage to high cpu
Rohit said
Thanx for valuable reply….
MV Bhaskar said
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?
MV Bhaskar said
Sorry 1995 batch and aged 38 years
Pavan DBA said
Hi Bhaskar, I am not sure and cannot advice accurately on this. But, surely software industry won’t consider age and all. You can learn Oracle DBA and try for jobs.
Himanshu said
Hi Pavan sir,
I will have new machine with oracle 11g and i have exported dump from oracle 10g (10.2.0.3.0)
Now i need to import that dump on oracle 11g.
Is there anything that needs to taken care in this ?
Thanks,
Himanshu
Pavan DBA said
it depends on which dump it is. i mean is it full export dump or schema only etc. if it is full, then u need to create same tablespaces(with same size) & users
Himanshu said
Thank you
I have dump for schema only.
Pavan DBA said
then you have to create that user first on 11g database and then do import
somnath said
Hiiii pavan,
I hope you are doing fine. I am somnath from kolkata. I’m new at administrating a database (10gr2 on RHEL). I need your help regarding few questions.
1)Should I create a smallfile tablespace with one datafile or should i create a smallfile tablespace with several datafiles? Which one is recommended?
2)Whether datafile should be set as AUTOEXTEND=ON or AUTOEXTEND=OFF? What is advisable?
3) Is there any relationship between AUTOEXTEND & MAXSIZE?
Thanks & regards
Somnath
Pavan DBA said
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)
janardhan said
Hi pavan sir,
please provide me latest dumps of oracle 11g OCA & OCP
i wish u Happy sankranti to you and your family members
have a nice day
Thanks
Janardhan
janardhan said
Thanks sir
Janardhan said
Hi pavan sir
Thanks for dumps
i clear 11G OCA certification now..
please provide me gunshot interview question RAC & HA
Have a nice day
Thanks
Janardhan
Pavan DBA said
hi, i don’t have interview q’s in specific
shuv said
Hiii pavan,
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?
Thanks in advance
Pavan DBA said
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.
sorry culdn’t reply u bcz i am on leave since 3 weeks
Madhava Pai K said
Dear Sir,
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
Thank You.
Pavan DBA said
hi, any vacancy info i will be posting in my blog itself. you can keep watching this regularly
vamsi said
Hi Pavan Sir,
Please refer me if you any openings in cognizant for oracle apps dba(11i/R12).
regards
Vams
Pavan DBA said
any requirements, i will post in my blog itself
Ramesh said
Hi Pavan,
Great Sharing & Teaching, Keep it continued….
Am an Oracle DBA with 7 Years and total 10+ IT exp, Looking for the next level . . .
What should I do next? Your Inputs . . .
Regards,
Ramesh
Pavan DBA said
Hi Ramesh, thank you for comments.
depends on your interest of going to management side or remain in technical side, I can give my inputs better. Please let me know which one you are thinking of…
Ramesh said
Thanks pavan for your prompt response . . .
I would like to stick on to Technical side . . . Please advice.
Thanks once again. 🙂
– Ramesh
Pavan DBA said
on technical side, you can choose to be in a role of service manager(service delivery manager). This will be very good in order to grow technically
Ramesh said
Thanks Pavan. Please let me know how to scale up ?
How should I prepare for that?
Ramesh
Pavan DBA said
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.
ashok said
hi pavan,
what was the rman internal mechanism
Pavan DBA said
please read this – http://docs.oracle.com/cd/B19306_01/backup.102/b14191/toc.htm
kishore said
Hi,
Please provide the better answer for below interview question.
Question: What is the most difficult task you faced so far (like in recovery scenario’s)
Pavan DBA said
you can go to forums.oracle.com and there u will find lot of scenarios
VASUDEVAN said
Whata is stats pack?What is flashback?What is pt?how to do pt?What is dataguard how to apply it?what is patching?how to apply patch?
Pavan DBA said
for all your questions, one place to get answers is oracle documentation or google. some of them are also there is my blog
VASUDEVAN said
what is the difference between Oralce Parallel server and RAC
Pavan DBA said
till 8i, we used to call it as oracle parallel server and from 9i onwards name was changed to RAC
sai kiran said
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
Thanks in Advance
Pavan DBA said
i am going to upload them soon
sai kiran said
thanks pavan and also discuss some errors on core dba and rac dba topics
amar said
hi this is amar i want to learn oracle dba through online could you pls tell me which institute is better in Hyd..
Pavan DBA said
hi amar, if you wish you can join kanna technologies. i am providing training there. details are in my blog
amar said
actually i was contact by Kanna tech….but they are saying it takes 2more weeks to start the course, is there any other institutes giving online course
Pavan DBA said
i am not sure who can give online classes
kishore said
hi,
if database restart what happened to scheduler job. is it automatically start’s or we need to start again.
Pavan DBA said
if the job is running during which database got restarted? then it will not start automatically. it will run for next time
Prashant said
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…..
Pavan DBA said
yes. it would be better to acquire RAC knowledge also so that oppoutunities will be more
Prashant said
Without RAC where should i approach for searching job only with DBA skills….???
Pavan DBA said
yes you can. but it depends on how much exp u hav
apoorv said
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
Pavan DBA said
hope we discussed this in chat and you are clear
takecare said
I recommend you and list you the institutes in priority wise from top to bottom,
Wilshire (madhu sir batch)
wilshire if any other faculty
hashzone
srisai institute
db expert
4bs
unirac solutions (with placement)
uclid IT School
dba technologies
kernel it solutions
kanna technlogy (pavan dba)
kyathi technology
sunmarss institute
peers technology.
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.
Take the rite decision by considering the above
nandu said
wilshire is always preferable
Dheeraj said
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
sandy said
How is the orafact institute, as because they are the partner of the oracle and saying for placement also.
Pavan DBA said
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
Sivaprasath.D said
Can u share me with important wait events in oracle performance tuning… i browsed ur blog and unable to find info abt that… so can u help me with this topic…
Pavan DBA said
i have some docs in “important docs” page with name “analyzing awr report 1,2,3”. you can go thru them
Sivaprasath.D said
Hi Pavan,
Today i found a interesting question about System tablespace and i could’n guess what may be the reason.. i believe u can help me with this.. the question is ..
” If there is so much place in system tablespace and it is not used
it throws an error why?”
Pavan DBA said
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.D said
thnx for d answer…:-)
kishore said
Hi,
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
Pavan DBA said
can you post what query you have used? also what is your oracle version?
kishorecv said
Hi,
please let me know the steps to imp 11g to 9i database
Thanks&Regard’s
kishorecv
Pavan DBA said
Hi Kishore, it is not possible to import 11g database into 9i version.
Keerthi said
Shall i know is there any other possible ways ??
Pavan DBA said
sorry… i didn’t get you. what you mean by other ways?
ashish said
hi can u tell me what is differenece between normal restore point and guarantee restore point ……..please explain with example
Pavan DBA said
http://gavinsoorma.com/2009/06/hello-world-2/
Dinesh said
Hi Pavan
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.
2)could you please explain the exact steps suitable to this scenario
Pavan DBA said
RMAN> change archivelog all validate;
try this command by connecting to both target and catalog and let me know if it works
Dinesh said
it’s not working.we need to feed the the expired archives to the catalog once we move it to the original location,but couldn’t able to register them to the catalog.
looking forward to here from you!!!
Pavan DBA said
can you let me know what is the error you got when you ran above command?
shashi said
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
Pavan DBA said
hope we discussed this in chat today morning
Gaurav said
Hi Sir,
while running . oraenv ….i am getting following error ..Pls suggest..
oracle@xxxxx:/usr/local/bin>. oraenv
ksh: @: 0403-041 Parameter not set.
Pavan DBA said
which OS you are using and its version plz?
Rashmi said
Hi pavan ,
Can you please provide me help in creating partition on existing tables in the database .any document or link. its urgent .
Appreciate your help.
Thanks.
prathap said
Hi sir,
1.one of my developer given package and asked me create.when i created it shown error below error.
LINE/COL ERROR
——– ————————————————–
906/10 PL/SQL: ORA-00942: table or view does not exist
906/10 PL/SQL: SQL Statement ignored
909/10 PL/SQL: ORA-00942: table or view does not exist
909/10 PL/SQL: SQL Statement ignored
960/3 PL/SQL: ORA-00942: table or view does not exist
960/3 PL/SQL: SQL Statement ignored
961/11 PL/SQL: ORA-00942: table or view does not exist
961/11 PL/SQL: SQL Statement ignored
then i sent to developer it is showing error.
2.then the developer told me to create dblink and create.
3.how to create db link?wat is the syntax?
regards,
Prathap
Pavan DBA said
http://www.dba-oracle.com/t_how_create_database_link.htm
Rashmi said
Hi Pavan,
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 ,
HAWKEYE=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.33.3.33)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
)
)
And listener.ora on server,
LISTENER1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp)(HOST = 10.33.3.33)(PORT = 1521))
(ADDRESS = (PROTOCOL = ipc)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME= ORCL)
(ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1)
)
)
i am troubleshooting this for past 2 days but all in vain , i am able to connect to other servers through toad succesfully but only this server is causing the problem.
even tnsping fails ,
C:\>tnsping 10.33.3.33
TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 – Production on 27-JUN-2
012 18:07:56
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
C:\app\Rashmi_Pardeshi\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTO
COL=TCP)(HOST=10.33.3.33)(PORT=1521)))
TNS-12537: TNS:connection closed
Plzz help..
Thanks
Pavan DBA said
in tnsping you should use alias name, not ipaddress.
what is the result when you issue “tnsping hawkeye”? plz post me
Rashmi said
C:\Users\rashmi>tnsping HAWKEYE
TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 – Production on 02-JUL-2
012 14:13:01
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
C:\app\Rashmi_Pardeshi\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.33.3.
33)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL)))
TNS-12537: TNS:connection closed
Pavan DBA said
i believe there is some issue with firewall also. so check from that side too or ask your network admin to do that
see this link also – http://www.dba-oracle.com/t_ora_12537_tns_error.htm
vipin garg said
Hi Sir,
I want to know that why Transporting Database is faster than DataPump
Pavan DBA said
because datapump requires lot of pre and post tasks to be done. but in real time we will not use transportable tablespace
manoj pathak said
hi..
this is manoj pathak
if i complet graduation,oracle dba certification.
ca i got job as DBA… is there P.G copulsary…s or no…. .rply i’m wating
Pavan DBA said
yes you can get job on graduation itself….
ashok said
Hi Pavan garu
what is the difference between static listener and dynamic listener.
Pavan DBA said
static listener – which listens to only one database
dynamic listener – which listens to all the databases on the server
nasir said
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
Pavan DBA said
Hi Nasir, as you have not mentioned the 4 digit version of db, i am assuming you are asking about 10.2.0.1 to 11.2.0.1.
we cannot upgrade 10.2.0.1 directly to 11.2.0.1. first we need to upgrade db to 10.2.0.4 and then to 11.2.0.1.
to move db to 10.2.0.4, we need to apply patchset.
this is how oracle defined their upgrade method
nasir said
Thanks Pavan..
Rashmi said
Hi Pavan,
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
Pavan DBA said
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.
upendar said
hi sir,
how i can convert my standby database to primary when my primary is crashed..plz explain me with example..
thanks in advance…
Pavan DBA said
Hi Upendar
we need to use below commands to do failover (run these commands on standby to make it primary)
select database_role from v$database;
recover managed standby database finish;
alter database commit to switchover to primary;
alter database open;
select database_role from v$database;
upendar said
Hi sir,
what is current_user and global user in database links concept? could you please explain me with an examples?
thanks in advance…
Pavan DBA said
this will help you – http://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_admin002.htm
9500057638 said
Hi Pavan,
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.
Thanks,
Sunil kmsunil872yahoo.co.in
+91 9500057638
Pavan DBA said
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
shashi said
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
Pavan DBA said
hope we discussed this…
vinod kumar said
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
Pavan DBA said
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
Mohammed said
Dear Pavan
Great Job!
What is the Duration for RAC Course !
When will a New Batch Start Every Month?
Many thanks
Pavan DBA said
Duration would be 30 days. Regarding the batch details you can find at http://www.kannatechnologies.com
Asif Hussain Khazi said
Hi Pavan,
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
Enter username:
Pavan DBA said
as discussed over mail, please setup sqlnet.ora parameters
Balu said
Hello Sir,
what are the parameters displayed we get while startup of a database.
Fixed size, Variable size, Total SGA, Database buffers, Redo Buffers.
Pavan DBA said
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
Database Buffers
– Holds copies of data blocks read from datafiles.
size = db_block_buffers * block 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.
Total SGA – memory allocated to entire SGA
Balu said
Thank U Sir..
Rahul said
Hello Sir,
Recently I attended an interview , I have given the questions which I’m not able to
answer.Could you please provide the answers for these questions.
1) If your database block size is 8k then how you take the advantage of 16k without setting non-standard block?
2) What is start SCN & stop SCN?
3) Why does Oracle advise against backing up online redo logs with a hot backup?
4) I have created a table with nologging option.But any insert,update or delete still generates redo, why?
5) How can we know the exact updation date of a particular database?
Looking forward for your answers. Thanks in advance
Pavan DBA said
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 below
http://dba-oracle.com/t_nologging_append.htm
5. I didn’t get your question. updation date means?
Sudhir said
Hi Pavan,
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.
Looking forward for your answers.
Pavan DBA said
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)
I recommend you strongly to read performance tuning guide from this link
http://docs.oracle.com/cd/E11882_01/server.112/e16638/toc.htm
Sudhir said
Thanks for your reply.
I am a fresher, I am aware of these questions, but not sure, that is the reason, I asked you.
Thanks for your suggestion and I do my best from the next ….
Pavan DBA said
all the very best
Prathap said
dear sir,
i have total 145 invalid objects in my datadbase.i used the below script to compile.
@$ORACLE_HOME/rdbms/admin/utlrp.sql
after that it reduces 145 to 103.
then it is not reduceing the invalid objects.
SQL> SELECT OWNER, OBJECT_TYPE, COUNT(*)FROM DBA_OBJECTS WHERE STATUS = ‘INVALID’
2 GROUP BY OWNER, OBJECT_TYPE;
OWNER OBJECT_TYPE COUNT(*)
—————————— ——————- ———-
APPS VIEW 1
APPS PACKAGE 5
APPS TRIGGER 1
APPS FUNCTION 5
APPS PROCEDURE 38
APPS PACKAGE BODY 21
ALCRM PACKAGE 1
ALCRM PROCEDURE 5
ALCRM PACKAGE BODY 3
SCOTT MATERIALIZED VIEW 1
EUL_US PACKAGE 3
OWNER OBJECT_TYPE COUNT(*)
—————————— ——————- ———-
EUL_US PROCEDURE 3
EUL_US PACKAGE BODY 6
PUBLIC SYNONYM 5
JUNK_PS SYNONYM 4
OLAPSYS PACKAGE BODY 1
16 rows selected.
SQL> select count (*) from dba_objects where status like ‘INVALID’;
COUNT(*)
———-
103
how can i compile the remaning invalid objects?
Pavan DBA said
Hello Pratap,
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
Prathap said
sir,
i have compiled using utlrp.sql script.it reduceses to 145 to 103.when i will see next day it again showing 145.what is the cause sir?
Pavan DBA said
cause could be because of some modifications to the underlying tables. As a DBA, we should not worry about them until application team says there is some issue
Rashmi said
Hi Pavan,
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.
Thanks.
Pavan DBA said
Hi Rashmi,
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.
upendar said
hi sir,
could you please explain me what is the use of cross check in RMAN with example..
thanks in advance…..
Pavan DBA said
Hi Upendar,
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.
I have given an example below…
RMAN> list backup;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
1 17.81M DISK 00:00:01 31-MAR-12
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20120331T235037
Piece Name: /u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_annnn_TAG20120331T235037_7qglqorr_.bkp
List of Archived Logs in backup set 1
Thrd Seq Low SCN Low Time Next SCN Next Time
—- ——- ———- ——— ———- ———
1 15 255056 31-MAR-12 272351 31-MAR-12
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
2 Full 221.68M DISK 00:00:24 31-MAR-12
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20120331T235057
Piece Name: /u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_nnndf_TAG20120331T235057_7qglr9co_.bkp
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——— —-
1 Full 272367 31-MAR-12 /u01/app/oracle/oradata/test/system.dbf
2 Full 272367 31-MAR-12 /u01/app/oracle/oradata/test/sysaux.dbf
3 Full 272367 31-MAR-12 /u01/app/oracle/oradata/test/undo.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
3 Full 7.52M DISK 00:00:02 31-MAR-12
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20120331T235057
Piece Name: /u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_ncnnf_TAG20120331T235057_7qgls3x0_.bkp
Control File Included: Ckp SCN: 272376 Ckp time: 31-MAR-12
BS Key Size Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
4 2.50K DISK 00:00:00 31-MAR-12
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20120331T235125
Piece Name: /u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_annnn_TAG20120331T235125_7qgls63k_.bkp
List of Archived Logs in backup set 4
Thrd Seq Low SCN Low Time Next SCN Next Time
—- ——- ———- ——— ———- ———
1 16 272351 31-MAR-12 272382 31-MAR-12
RMAN> exit
Recovery Manager complete.
c[oracle@pc1 ~]$ cd /u01/app/oracle/flash_recovery_area/TEST/backupset
[oracle@pc1 backupset]$ ls -ltr
total 4
drwxr-x— 2 oracle dba 4096 Mar 31 23:51 2012_03_31
[oracle@pc1 backupset]$ cd 2012_03_31/
[oracle@pc1 2012_03_31]$ ls -ltr
total 253228
-rw-r—– 1 oracle dba 18676224 Mar 31 23:50 o1_mf_annnn_TAG20120331T235037_7qglqorr_.bkp
-rw-r—– 1 oracle dba 232456192 Mar 31 23:51 o1_mf_nnndf_TAG20120331T235057_7qglr9co_.bkp
-rw-r—– 1 oracle dba 7897088 Mar 31 23:51 o1_mf_ncnnf_TAG20120331T235057_7qgls3x0_.bkp
-rw-r—– 1 oracle dba 3072 Mar 31 23:51 o1_mf_annnn_TAG20120331T235125_7qgls63k_.bkp
[oracle@pc1 2012_03_31]$
[oracle@pc1 2012_03_31]$ rm *.*
[oracle@pc1 2012_03_31]$
[oracle@pc1 2012_03_31]$ rman target /
Recovery Manager: Release 11.2.0.1.0 – Production on Sat Mar 31 23:52:08 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: TEST (DBID=2077458425)
RMAN> crosscheck backup;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK
crosschecked backup piece: found to be ‘EXPIRED’
backup piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_annnn_TAG20120331T235037_7qglqorr_.bkp RECID=1 STAMP=779413837
crosschecked backup piece: found to be ‘EXPIRED’
backup piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_nnndf_TAG20120331T235057_7qglr9co_.bkp RECID=2 STAMP=779413857
crosschecked backup piece: found to be ‘EXPIRED’
backup piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_ncnnf_TAG20120331T235057_7qgls3x0_.bkp RECID=3 STAMP=779413883
crosschecked backup piece: found to be ‘EXPIRED’
backup piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_annnn_TAG20120331T235125_7qgls63k_.bkp RECID=4 STAMP=779413886
Crosschecked 4 objects
RMAN> delete expired backup;
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
——- ——- — — ———– ———– ———-
1 1 1 1 EXPIRED DISK /u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_annnn_TAG20120331T235037_7qglqorr_.bkp
2 2 1 1 EXPIRED DISK /u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_nnndf_TAG20120331T235057_7qglr9co_.bkp
3 3 1 1 EXPIRED DISK /u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_ncnnf_TAG20120331T235057_7qgls3x0_.bkp
4 4 1 1 EXPIRED DISK /u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_annnn_TAG20120331T235125_7qgls63k_.bkp
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_annnn_TAG20120331T235037_7qglqorr_.bkp RECID=1 STAMP=779413837
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_nnndf_TAG20120331T235057_7qglr9co_.bkp RECID=2 STAMP=779413857
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_ncnnf_TAG20120331T235057_7qgls3x0_.bkp RECID=3 STAMP=779413883
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_annnn_TAG20120331T235125_7qgls63k_.bkp RECID=4 STAMP=779413886
Deleted 4 EXPIRED objects
upendar said
thank u very much sir..
here i have one more doubt..if backup is in some tape then how it will check..?
and
what is mean by catalog repository and RMAN repository? is their any difference between these two?
Pavan DBA said
for tape also process is same and RMAN can detect even if backup is in tape. catalog and RMAN repository are one and same
upendar said
thank u sir…..
Rashmi said
Hi Pavan,
Why do we store index & tables in different tablespaces.There is a performance benefit for sure but can you please explain how ?
Thanks.
Pavan DBA said
Hi Rashmi,
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.
Hope you got some idea now…
Rashmi said
Ya.Thanks.
sivakumar said
Good Evening sir, I would like to know about all the information about cronjobs, and how create a script to scheduled a job for rman backup ,expdp Etc….. Please do the needful.
Pavan DBA said
see this link
http://www.cyberciti.biz/faq/how-do-i-add-jobs-to-cron-under-linux-or-unix-oses/
upendar said
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…….
Pavan DBA said
Hi Upendar, it is not right. RMAN catalog will store backup info, not changed values
upendar said
thanks for the replay..
Rashmi said
Hi Pavan,
We can very easily download and apply the oracle patches.But very few of us are aware of main distinction between a CPU & a PSU patch.Can you please share the basic difference betwween the two.
Thanks.
Regards,
Rashmi.
Pavan DBA said
Hi Rashmi,
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.
See this link – https://forums.oracle.com/forums/thread.jspa?threadID=1057736
I will post an article about this soon… thanks for bringing this up 🙂
Rashmi said
Thanks a lot Pavan..Have a good day ahead..
Gaurav Nigam said
Hi Sir,
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..:-)
Pavan DBA said
Hi Gaurav,
when you want to drop a user, first you need to have approval from application team. mandatorily mail confirmation should be there.
1. take logical backup
2. take DDL for create user
3. take info about privileges, roles etc assigned
4. drop the user
the above are the steps we need to perform.
Gaurav Nigam said
Thanks Sir…
One more little help ….i want to learn DBA related scripting, any Suggestions.
Thanks in advance…
Pavan DBA said
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
sreenu said
Hi Pavan,
SQL runs in 10 seconds in DEV DB but the same query takes 10 mins to run in TEST DB? .and Prod DB also 10 secs
Can advise me how to resole it? it’s an urjent
Thanks an advance
Pavan DBA said
Hi Sreenu,
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.
kishore said
Hi,
i attend many interviews recently,most of the are asking 2 qns regularly…1)what are you facing the top issue…in last 3 months
2)how to tune bad query
Pavan DBA said
You can read “Expert Oracle 11g admin” book by Sam. R. Alapati where tuning was explained nice. you will get your answers there
kishore said
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
Pavan DBA said
we can tune shared pool by increasing the size of memory_target parameter only. we can get that adivce from EM easily.
Rashmi said
Hi pavan,
I am continously facing this error, though i tried everything created new listnr but still not able to cope up, please help
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
32-bit Windows Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.24.23.52)(PORT=1521)))
TNS-12535: TNS:operation timed out
TNS-12560: TNS:protocol adapter error
TNS-00505: Operation timed out
32-bit Windows Error: 60: Unknown error
Pavan DBA said
Rashmi, can you please post your listener.ora contents?
Rashmi said
Thanks ..
I resolved it , it was due to incorrect host which couldn’t identify the listener.
Pavan DBA said
ok good.
abhi said
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
<<<SERVICES->LISTENER_NAME->START..
>>>>>
TNS-12535: TNS:operation timed out
TNS-12560: TNS:protocol adapter error
TNS-00505: Operation timed out
<<<enable tracing,increase time out values,.
if you are connecting from client computer such error may come…
sivakumar said
Good Evening sir, this is sivakumar, how to move multiple tables at a time one tablespace to another tablespace?, Please give me in detail explanation.
Pavan DBA said
you need to use dynamic sql… see below
select ‘alter table ‘||owner||’.’||table_name||’ move tablespace ;’ from dba_tables where owner=’your schema name’;
sivakumar said
sir, is there any other way to do this?
Pavan DBA said
Hi Siva, I don’t think so
NIssar said
Send me RAC Documents which contains the subject from the Basics of RAC.
Pavan DBA said
You can read this…
http://www.oracle.com/pls/db102/to_pdf?pathname=rac.102%2Fb14197.pdf&remark=portal+%28Books%29
abhi said
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…??
i need your help sir….
Pavan DBA said
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 Khan said
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 <<<<
I can not be able to resolve it, need your expert advise.
Rgds,
Shahid
Pavan Kumar said
Hi, have you verified the related Oracle Installation guide? if not please do that.
http://kamranagayev.com/2011/01/31/video-tutorial-installing-oracle-10gr2-grid-control-and-deploying-agent/
suman said
HI PAvan SIr,
Can i get step by step doc to upgrade 10g 2 node rac CRS and database upgrade to 11g.
Thanks,
Suman
Pavan Kumar said
Hi Suman, this is one of the finest docs that i recommend
http://www.oracledba.org/upgrade/Upgrade_10gR2_RAC_to_11gR1_RAC.htm
Rashmi said
GM Pavan,
Hope u r fine n doing well.Pavan can u pls provide a good document or some link for PT covering basics ,tuning strategies etc.
Thanks.
Pavan Kumar said
Hi Rashmi, I don’t have any docs. But to learn PT, oracle documentation is the good way. link for that is below
http://www.oracle.com/pls/db102/to_pdf?pathname=server.102%2Fb14211.pdf&remark=portal+%28Books%29
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 kumar said
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
Pavan Kumar said
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
pre-requisites to learn oracle apps technical – sql, pl/sql, forms and reports
pre-requisites to learn oracle apps functional (apps dba) – sql, unix basics & basics of shell scripting, Oracle DBA
vinod kumar said
thank you for the advice sir,
Rashmi said
Pawan,
Any idea about database replication & steps involved in it ?? Plz help.
I browsed on google but couldn’t get the relevant info.Please explain in detail as i have to work with a client on it.
Thanks.
Pavan Kumar said
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
https://pavandba.files.wordpress.com/2009/11/oracle-streams.doc
Rashmi said
Thanks Pavan.But it seems will have to use golden gate third party tool for repliaction here.
Pavan Kumar said
Golden gate is no more a third party tool. Now Oracle acquired Golden gate company, so its product from Oracle itself. so you can use it.
Naveen.K said
Hi,
Iam naveen i completed my DBA . RIght now iam searching for the jobs on DBA as experience of 3 years.Can i let u know in which locations can i find a jobs on DBA….
Pavan Kumar said
DBA jobs are across the country. You can follow my job postings in the blog and you can apply for them
sivakumar said
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
Pavan Kumar said
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.
Rashmi said
Hi Pavan,
Hope you are fine and doing well.Can you plz let me know what query do we need to execute to monitor high loads on db & contension.
Pavan Kumar said
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
Rashmi said
Thanks a lot.
Pavan Kumar said
you are welcome Rashmi
anwar said
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
Pavan Kumar said
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
rafi said
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.
Pavan Kumar said
Hi Rafi, thanks for your comments and here are the points I know….
1. Without using logical backup and flashback technology, u can do until time recovery in order to get a table. But this will effect other schema’s data.
2. either in tape or disk, we need to allocate a channel for that device using “allocate channel for device type” cmd and run “restore database” command (if it is full database)
3. Lock is a method of restricting a user from modification in order to avoid data corruption. This will be maintained for read consistency. Latch is the same concept, but applied for SGA. see this nice ppt – http://www.google.co.in/url?sa=t&rct=j&q=lock%20vs%20latch&source=web&cd=3&ved=0CDYQFjAC&url=http%3A%2F%2Fnocoug.org%2Fdownload%2F2001-05%2Flatches.ppt&ei=oeQLT9KuAY3zrQeWrKGwBA&usg=AFQjCNEFYgofQIuKyRW29LuzRpOmidkDBA&cad=rja
Also, for PT always start with PT guide in oracle documentation
prathap said
Hi sir,
this is prathap,i have 4 doubts?
1.archive logs are applying from production to standby…..suppose suddenely logs stoped shipping due to network problem. then wat steps have to take as a dba?
2.archive logs appling very very slowley?wat type of steps we have to take as a dba?
3.suppose i deleted 20(by mistake)archive logs in production before applying to standby.then wat type of steps to be take as dba?
Pavan Kumar said
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
prathap said
hi sir,
1.we are using 10g …Wat is FAL process duty?
2.thank u
3.thank you.
Pavan Kumar said
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).
prathap said
hi sir,
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…………
Pavan Kumar said
you need to just make sure MRP process is up and running, rest will be taken care by it. or else, u can run register archivelog (for command check google)
prathap said
thank u
Jagat said
Deadlock happened intermittent from a single select statements. There is no other second SQL statement. The select statements have parallel processes.What you think what might be the issues
Pavan Kumar said
Mohanty, I suppose there wouldn’t be deadlock issue for select stmt as they will hold only shared locks. so tracing the session may give more results
Jagat said
Thank Pavan for your reply !
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.
Pavan Kumar said
I feel it is better to raise a SR (if not already done) before setting that parameter, bcz they can give better option for tracing
Rashmi said
Hi Pawan,
Can u plz upload any document for oracle installation on linux platform, with details like
what are kernel parameters and why do we need them. and so on.
Thanks
Rashmi.
Pavan Kumar said
Rashmi, already there is a doc in “Important docs” page of my blog. You can access at https://pavandba.files.wordpress.com/2009/11/oracle-10g-on-linux.docx
Ravinder Reddy said
Hi Sir,
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.
Is there any other way to reorg this table?
Thanks & Regards,
Ravinder.
Pavan Kumar said
Hi Ravinder,
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.
Try to use buffer, commit parameters while importing
Ravinder Reddy said
Hi Sir,
Thanks for your reply.
Will try it.
bhaskar said
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
Pavan Kumar said
1. there are so many differences. you can find them from net
2. give them answer as L2
bhaskar said
thanks sir
Shreya said
Hi Pavan
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.
Please help me.
Pavan Kumar said
I am not sure if you practised on Linux env or not. If you practised, then its same way that we connect to database even on unix.
export ORACLE_SID=
sqlplus “/ as sysdba”
let me know if u have any queries
Shreya said
I have practiced on Linux platform only. RHEL 4 version.
I say this as the answer, but immediate question they ask me is — ” YOUR TASK IS TO SWITCH ON THE MACHINE AND CONNECT TO THE DATABASE AND CHECK TO WHICH DATABASE YOU ARE CONNECTED “.
I understand this sqlplus “/ as sysdba”, but I need elaborate steps.
Please help.
Pavan Kumar said
Generally switch on the machine is not our task as a DBA. connecting to the database is same i told in prev reply. how to check which database we are connected? we need to use below query for that
select * from v$instance;
select name,open_mode from v$database;
Shreya said
This will help me i think.. Thanks a lot Mr.Pavan. I know that there are some tools used to get access to the database like Putty. Can u tell me few other of such type which we generally use.
Thank you so much once again for the reply.
Pavan Kumar said
for daily use to connect to server we use putty
for some other db related tasks we use Enterprise Manager
for installations etc using GUI we use tools like vncviewer, humming bird, xmanager etc
more than this any tools using depends on project
Shreya said
Thank you so much for the valuable response. I hope it helps me get a job soon.
Thanks again Pavan.
Shreya said
Hi pavan,
Can you please list down the steps ” HOW TO APPLY PATCHES,&, DIFF B/W CPU AND PSU”?
Is it possible to directly upgrade from 10.2.0.2 to 10.2.0.5?
Pavan Kumar said
Soon I will upload doc on how to apply CPU and PSU.
Regarding the difference between them, CPU (critical patch update) which is a quarterly patch released by oracle corp. PSU (patch set update) is a collection of patches and when you apply this your database version will change.
you can surely do upgrade from 10.2.0.2 to 10.2.0.5. for this you need to apply 10.2.0.5 patchset update
Shreya said
Hi
I am waiting for you to upload the above mentioned doc.
Meanwhile, I would like to know ” What is a monitoring tool? What monitoring tools do a DBA use in real time? ”
Thanks in advance.
Pavan Kumar said
Hi Shreya, I got too busy with office work and couldn’t be able to upload it. Let me do it soon
About monitoring tool, generally we will use it for performance monitoring of the database. even though there are so many 3rd party tools available in the market, I just saw DBA’s using 10g Enterprise Manager only to do this….
sridhar said
Sir this is sridhar.mukta..
In a team how many members have metalink access? and who wil download the patch and analysis? this question was asked in oracle corporation.. i could not give the successful answer..and what happens if we happen patch?
He asked a question me that any issues you faced in patching? then i was answer that “pre requisite patch was missed”. he asked that is it ur mistake r ur team problem? r any analysis problem? who will analyse patch ?
sir please give me a brief answer “what happens in real time before apply a patch”?
Regards,
sridhar.mukta
Pavan Kumar said
Hi Sridhar,
All of the team members will have access to metalink and anyone can download the patch in real time.
Generally it is the mistake of us only when patch is not appiled successfully. because we need to first test that either in dev or test instance.
Also, we can run a pre-check utility before starting patching. see my article – https://pavandba.com/2011/09/19/how-to-check-conflicts-while-applying-cpu-patch/
sridhar said
Hi sir Good morning,
This is sridhar.mukta please provide me the documentation of Opatch.
Pavan Kumar said
Hi Sridhar, I will provide that soon
vishaal said
Is it necessary to down the nodeapps,CRS(cluster) if we are applying opatch
Pavan DBA said
not required. only db and listener r enugh
mahaveer said
hi anna
i am getting error will installing oracle 11. 2.0
will i am running ./runInstaller it giving permission denied in .oui
Pavan Kumar said
Hi Mahaveer,
Please give full permissions on “database” directory as below
chmod 777 database
Then try installation. If any problems, please post here
Jagadesh said
Hi Pavan,
How to find the fragmented indexes in a Database;
Thanks
Jagan
Pavan Kumar said
Hi, you can use below script for that
select
‘exec analyzedb.reorg_a_table4(’||””||rtrim(t.table_owner)||””||’,’||””||
rtrim(t.table_name)||””||’);’,
t.table_owner||’.’||t.table_name name,
a.num_rows,
sum(t.inserts) ins,
sum(t.updates) upd,
sum(t.deletes) del,
sum(t.updates)+sum(t.inserts)+sum(t.deletes) tot_chgs,
to_char((sum(t.deletes)/(decode(a.num_rows,0,1,a.num_rows)))*100.0,’999999.99′) per_del,
round(((sum(t.updates)+sum(t.inserts)+sum(t.deletes))/(decode(a.num_rows,0,1,a.num_rows)) *100.0),2) per_chg
from analyzedb.table_modifications t,
all_tables a
where t.timestamp >= to_date(’&from_date’,’dd-mon-yyyy’) and
t.table_owner = a.owner and t.table_owner not in (’SYS’,’SYSTEM’) and
t.table_name=a.table_name
having (sum(t.deletes)/(decode(a.num_rows,0,1,a.num_rows)))*100.0 >=5
group by t.table_owner, t.table_name, a.num_rows
order by num_rows desc, t.table_owner, t.table_name;
srinivas reddy said
Hi sir
This is srinivas from hyd… your valuable suggestions are very very help full to us… i completed my Msc(cs) (2011)am searching job now.. I came to know about your blog while searching on oracle material now am eager to see your blog daily…I need your help to get a job…
thank you
Pavan Kumar said
Hi Srinivas, plz chat with me at mymailbox.21@gmail.com or at my facebook profile “Pavan Dba”
srinivas reddy said
thank you very much sir
AJay said
Hi Pavan,
How are you ..?
Could you pls help me out in letting me know any free oracle DB refresh tool, if any. Few senior guys are out here, but from past few days we are missing the integrity of the data, and very recently on friday@7th Oct’11, I too did a refresh, where we lost some of the data.
Thanks much.
AJay.
Pavan Kumar said
Hi Ajay, May i know what is the method you are using for refresh? as far as i knw, DBA’s generally wont use any tools to do this….
AJay said
Manually using impdp with parfile
> impdp parfile=.par
Pavan Kumar said
Ajay, we don’t have any tools for that. its always better to use those utilities directly instead of depending on 3rd party tools
kalaivani said
Hi pavan,
this is kalaivani. I a 2011 passed out BE graduate. I would like to work for cognizant. Your blog site was useful to know about the recruitment procedure. Thanks. But i need to seek for a referral for applying to cognizant. I don’t know anyone there. Could you please refer me? thank you in advance.
Pavan Kumar said
Hi Kalaivani, please send me your cv to nagapavan.s.t.p@cognizant.com, i will provide you reference details
kalaivani said
Thanks a lot pavan. I have sent my cv.
kalaivani said
Delivery to this id is getting failed. Couldn’t able to send my cv to you. Thanks a lot again. Got your reference details.
Kishan said
Hi Pavan,
This is Kishan, It was really great to came to know about your blog and i am very happy that your are helping people with your valuable suggestions.
I am really appreciate your time and patience while you are giving response to all posts…………..:)-
Pavan Kumar said
thank you Kishan…. hope i will do blogging more and more
vivek said
Hello Pavan,
greetings first of all thanks for this blog. i would like to get dumps and study material for exam 1z-051.
Thanks for all your help.
Thanks,
Vivek
Pavan Kumar said
plz chat with me on mymailbox.21@gmail.com
sagar said
Hello sir, i completed Btech in 2007 and after that i worked 3years in satyam bpo in GIS side and now i want to change my career into oracle dba. I completed the cource and searching for job but i am not getting any oppurtunity. Could u suggest
me how to get in. my contact number 09972948746.
Pavan Kumar said
if u r trying correctly, thr is no scope in not getting any opp. plz chat with me at mymailbox.21@gmail.com
SK said
HI Pavan,
I am looking out for any employee referral, is it possible you can refer me to CSC.
Please let me know your contact number/Email address. It would be of great help.
This opening is for Change management.
Regards
SK
Pavan Kumar said
Hi sirish, now i am no more working in CSC. plz forward ur cv to pavandba@facebook.com. i will contact my ex-collegues and will try seek their help
karthik said
hello sir,
this is karthik from chennai. Iam a btech graduate 2009 passed out completed my oracle ocp certification how can i get a job in oracle database .i tried a lot but none worked out pls give me a suggestion Iam ready to pay even via back door.
Pavan Kumar said
Hi Karthik, as a fresher there will be less opps for DBA. But i cannot say its completely impossible as i started as fresher DBA only. so keep on trying and follow my blog. in last 2 weeks i posted some fresher openings in HCL and cognizant. apply for them
karthik said
k sir thanks for ur advice but pl temme if thr is any such opening for dba via backdoor k.really u have a gud heart sir bcoz ur answering for each nd every msg…thank you very much sir i gave u chat req in gmail sir pls acceptit my gmail id is karthiksupreme1986@gmail.com
once again thank u sir……
ramesh said
Dear Pavan,
I would like to know the procedure of RMAN backup, the source and target databases both are in same host.
Thanks
Pavan Kumar said
Hi Ramesh, sorry i didn’t got your question. what you mean by source and target databases? are you talking about recovery catalog?
ramesh said
Dear Pavan,
source (catalog database) target database (yet to take backup using rman)
Pavan Kumar said
I expect you already registered target database with catalog. in such case u need to connect to both target and source and then issue backup database command.see below example
$ rman target / catalog rman/rman@rc
RMAN> backup database plus archivelog;
DL said
“Error in writing to file D:\Oracle\product\10.2.0.2.0\bin\oraevrus10.dll”
Lucky I saw your blog and reboot the server and restart the Oracle patch. Saves the day man !
Pavan Kumar said
thank you and i am happy that my post helped you…..
ramesh said
Hi Pavan
This is ramesh from bangalore
y v r running these two scripts orainstroo.sh & root.sh during installation
Pavan Kumar said
orainstroot.sh – it will change permissions to 755 and will change group owner to oinstall
root.sh – will create oratab file
you can get these answers when u read the output of them….
kirshna said
hi pavan,
This is krishna, I’m very glad to see your blog
I’m oracle core dba, my company people kept me as oracle apps dba. Recently i installed Oracle Apps R12 on RHEL 5.4 (64-bit) (IBM server). in server its working fine. when it comes to client machine its not opening, i added the server ip address in client machines. What to do & how to solve this issue?
Thanks
Pavan Kumar said
sorry Krishna, I am blank on apps dba.
vivek vishal said
Hi Pavan,
Hope u r doing well , Do u have some opening related to SQL server DBA ?
Do u also have document related to SQL server DBA .
If u have please share …
Thanks
Vivek
Pavan Kumar said
Hi Vivek, I am getting SQL server dba openings info very less. but whatever i got, am posting them in my blog.
also I am oracle DBA, so i don’t have any docs on sql server dba
Sriram G said
Hi Pavan,
How are you?
I am Sriram working in Oracle financial services as Oracle DBA. I am a B E (IT) professional.I have not done any course in DBA and I have been put into DBA team as my aggregate is more than 80% in Engg. I joined this DBA team as a fresher. I like this job very much and i am learning new things daily. The only disadvantage in my team is there is not much on-site opportunity but I am more interested in traveling to different countries. Can you please suggest me whether i can continue this team or switch over to any implementation or Appln development team as there is many on-site opportunities. If i want to continue in this team whether i have to do some course?
Please add me in ur gtalk. I have sent you the request.
Waiting for your reply.
Thanks & Regards,
Sriram G
Pavan Kumar said
As your are learning some good things, I suggest to continue in this team. Even for onsite opp, i think u need to get some experience. till that time, you can be here and later you can move to some other team where onsite opp are more. doing a course is not required as i feel.
i added you in the gtalk and you can chat with me now…
Sriram G said
Thanks pavan…
chandrashekar said
Hi pavan just now i saw ur web site,it is very usefull
What is the difference between obsolete RMAN backups and expired RMAN backups?
Pavan Kumar said
Obsolete means unnecessary backups. If at all we have latest backup available, RMAN will mark all older backups as obsolete backups depends on retention policy.
Expired backup means backup which is not available physically at OS.
Harry said
am waiting for Goldengate installation documention and steps from you pavan..
Pavan Kumar said
will upload them in near future
bhargava said
Hi Pavan,
Please try to post maximum openings in SQL DBA…! It will be appreciate…! Thanks in advance
Pavan Kumar said
sure. thanks for comments
balagangadharreddy said
hi pavan kumar,
This is bala ,this blog is very nice,
my ambition and interest is oracle apps technical consultant but i didn’t get any call for fresher (2010 mca pass out ) so i will put fake resume on apps , so plz give suggestion to me
if u don’t mine plz send validations on purchase order conversion thru mail (bala.grk@gmail.com)
Pavan Kumar said
Hi Bala, I am not a oracle apps technical professional. so I didn’t understand what you are asking….sorry !
krishna chaitanya said
Hi pavan,
Please can you post openings for IBM DB2 DBA please
Pavan Kumar said
surely chaitanya, but as I am oracle DBA, i would be getting more req’s on that….
chandra said
Hi pavan,
the blog is very gud. I have been trying for job change and have found the update for jobs posted vry gud here. is there any alert system in your blog where if i need to know any thing of a particular location. a mail can be sent to me.
Regards,
Chandra.
Pavan Kumar said
Hi Chandra,
Thanks for your comments…………
On the left hand side of my blog, there will be email subscription tab. you can leave your email id and whenever any post is pasted, you will get email notification
Ravi said
thanks for reply my msg and i happy that u helps any person who are getting job………….
Ravi said
hi sir
please give me suggestion. i wants to get job in oracle. if u have some time 4 me then u i want chat with u….. i will be waiting u
Pavan Kumar said
plz chat with me in mymailbox.21@gmail.com
vishnu said
Hi Pavan,
This is vishnu,may i know the ans for this-if some archive logs are not copied from prod to standby but we know after 1hr now what will we do know,and
what is softlink?
how do u know 2 users are accessing same table?
what is dbcache wati?
Difference b/w dbms_stats and analyze?
Pavan Kumar said
1. First we need to check the reason why they are not copied. for this we can check alert log of both primary and standby databases. If it is due to network problem, we have FAL server/client which will take care of this gap
2. softlink is a method of accessing a file with alias name (like shortcut in windows)
3. we can check that info by looking at their sql_text or by checking locks that they are holding on the table
4. a transaction waiting for free block in database buffer cache is called dbcache wait
5. dbms_stats will collect more statistical information than analyze command. so oracle recommends to use it.
vishnu said
Hi Pavan,
This is vishnu i want know some info from u i.e, if we create physical standby from production db,Once i create one new tablespace/datafile in production db and that is not a actual loc i created that separate loc that is it updated standy by or not,if i set standby_file_management=auto after what we have to do.
Pavan Kumar said
Hi Vishnu, yes it will be updated provided you need to have same location as primary in standby also.
For example, if you added file in /u01/ora10g/prod/mydata03.dbf in primary, in standby also you need to have /u01/ora10g/prod location.
All this will happen only when standby_file_management is set to AUTO.
If you don’t have that directory structure, oracle will try to create, but fails. in such case, your standby will be out of sync from primary
Hope that answers you question
Ravi said
Hi Sir
i am Ravi and i have been completed OCA & OCP but i am fresher and i am looking job from long time but i am getting job. please tell me what i do…..i am waiting…..
if u want to give me any suggestion please give me because
i love oracle and i am ready for any compromise.
oracle.ravi5@gmail.com
Pavan Kumar said
Hi Ravi,
Unfortunately there will be very less fresher DBA jobs. But its not impossible to get job as fresher as I also started my career like that. So don’t loose hope and keep on trying. I don’t know if you did, if not plz post your cv in all the job sites like naukri,monster etc.
Plz chat with me at mymailbox.21@gmail.com to give more suggestions
Vishnu said
Hi this vishnu can you please send me upgradation steps from 10g to 11g and 9i to 10g with screen shots.
Pavan Kumar said
Hi Vishnu, you can get lot of docs from google itself. But in few more days i am going to upload them in my blog at “important docs” page. keep watching it……
bala said
hai pavan
this is bala from chennai. really it’s amazing tutorials.just i want know without certification can i get job.please tell me or mail me and also i need oracle 9i dumbs .if u have please send me my mail.once again i tell u r done great job.
Pavan Kumar said
Hi bala, certainly certification will be added advantage to the profile while attending interviews. but without certification also you can go through interview process and get the job. i don’t recommend to use dumps to clear the exams. its a preferable way to get good hands on exp on the topics and then appear for the exam.
thanks for all the words you mentioned !!!
Srilakshmi said
Hi Pavan,
I am srilakshmi. I am following your DBA blog. its very nice. I have done Oracle DBA 10g course. My life ambition is work as a DBA without that i cant imagine my life. But i dont how to get a job as a fresher.I would be very greatfull If u give any suggestions to me.
Thanks&Regards,
Srilakshmi
Pavan Kumar said
Hi Srilakshmi,
Thanks for comments abt my blog and I hope I cleared some queries in our chatting… 🙂
Brijendra Kumar said
Hello Sir,
I want to know the procedure to restore database from tape.
I mean if I have backup in tape drive then what are the steps & procedure to restore that backup?
Thanks in advance.
Pavan Kumar said
you will be using restore command of RMAN even if it is tape drive. in other cases (hot or cold backup), there will be a separate (backup) team who will do restore
Nazeem said
Hi Pavan,
This is Nazeem from Pune,It was great to came to know about your blog and i have gone through your blog and i am very happy thet your are helping and cooperating with people,
I am working as Recruiter in MNC and i am looking for Wintel DBA with Unix,the person who will expert in read write and speak Japanese this is with one of my CMML5 client.
I would realy appreciate if you can do something for this.
Awaiting your possitive reply.
I will be reacheble on nazeem.kazi@adecco.co.in
Regards,
Nazeem
8149198928
dev said
Sir,
I want to write a shell script for compiling materialized view.
Let suppose there are 10 MV
when I check the status of them through the query
select object_name from user_objects where object_type=’MATERIALIZED_VIEW’ and status=’invalid’;
Let I got 4 MV whose status are INVALID
Now I want to compile these 4 MV to make it valid by running command one by one
ALTER MATERIALIZED VIEW MV_name COMPILE
Sir,
I want to automate the whole process through shell script.
Kindly advice.
Pavan Kumar said
you can direct the output of select query to a sql file and you can call that sql file in a shell script. for basic shell scripting, refer to google
srinivas said
Hi Sir, how to check the detection of corruption in datafiles in rman ? sir i need command…
Pavan Kumar said
if datafiles are having corruption, RMAN will show that during backup or validating the backup.
still you can use below commands to do that
physical corruption – RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
logical corruption – RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;
you can also use dbverify utility to find corruption for datafile (for syntax, refer google)
srinivas said
thanks u pavan…
vamsi krishna said
what is the force logging and nologging.
what is dataguard broker.
backup of archive log all delete input:what is the use of command
Pavan Kumar said
force logging will make sure all changes will be recorded in redolog files. If you use nologging option, those changes will not be recorded in redolog files. But NOLOGGING is not applicable to DML statements. It is useful for statements like
1. create table as select * from …
2. Insert into A select * from B;
3. alter index rebuild etc
Dataguard Broker – It is a utility through which we can easily manage primary and standby databases using observer. For more details, read oracle docs
backup of archive log all delete input – this is not correct command. The below is the correct command
RMAN> backup archivelog all delete input; – this will take backup of all archive log files in archive destination and will delete them immediately.
vamsi krishna said
Thanks sir
it will delets the all archive log files from archive destination or
delete prev backup of archives from rman backup directory in target database.
Pavan Kumar said
it will delete the archives which were taken backup (not newly generate ones)
vamsi krishna said
sir
what is the minimum size of redo logfile and can we resize the redologfile ?
how to monitor the redolog file size ?
Pavan Kumar said
Min size you can give for redolog file depends on operating system. In 9i min size is 100MB whereas in 10g & 11g it is 50MB.
redo size also depends on how much redo is generating….
we cannot resize redologfile, instead we can create new group with required size and drop the old one
redolog file size can be get from below query
SQL> select sum(bytes/1024/1024),group# from v$log group by group#;
basha said
hi sir, this is basha , pls educate me about cpu patcher which are oracle provides for every 3 months. bcoz i faced that quetion in interview. Our friends told that i.e, app dba relater. so pls help me regarding this
Pavan Kumar said
Hi Basha, its not related to apps dba. it is for DBA only.
As any other software, oracle database also contains some bugs in every version (9i or 10g or 11g). To fix those bugs oracle will release critical patch update (shortly called as CPU) every 3 months.
DBA need to download this patch from metalink and following a readme file (which gives step by step instructions) we need to apply patch on the database.
The latest CPU released is in OCT 2010 for 10.2.0.4 database
vamsi krishna said
hi sir,
if our database in force logging. what about tablespaces whether in nologging or force logging
I have one table with 2 rows.Using only single update statement row positions will become interchange(SWAPING) from tech mahindra interview.
Pavan Kumar said
if entire database is placed in force logging, even if your tablespace is in nologging it doesn’t makes difference
about your another question, i didn’t understand it….plz explain
Abhi said
I want to discuss with someone Oracle table partition. I have some doubts. Please add me on yahoo messenger dbaIBM09@yahoo.ca
Sasiniveda G said
Hi pavan…am sasi…i have completed my BE(CSE)in 2010..looking for software jobs..ll u tell me any interviews.help me.
Pavan Kumar said
hi sasi, plz keep on watching this blog. any updates will be posted here itself
Sasiniveda G said
thank u…
sandeep said
hai.. am sandeep.. i completed MCA in 2009.. i did oracle DBA in wilshire..
will you tell me any interviews for DBA freshers in Hyd
Pavan Kumar said
hi sandeep, plz keep watching this blog…any updates will first reflect here…
sandeep said
hai,
am following your blog from 3 months.. you are posting only exp jobs only.
but not for freshers.. plz post oracle DBA fresher jobs
Pavan Kumar said
Hi Sandeep, am not a HR to know fresher openings. whatever openings i came across am posting them.. frankly its not in my hands ! If i get to know about any opp about fresher DBA jobs, surely i will post them or if you know anything like that, forward me so that i can post it….
Ketan said
Hello sir i am a Btech (2008).Working with a bpo level 2 tech support.In total have around 17 months of experience.Looking for a change.Help me out .Please contact me at 9718685666
Pavan Kumar said
you can chat with me in gmail…@ mymailbox.21. i am not clear on your skill set etc, so its better to chat
sai said
im sai from kannatech
How can i increase the size of the tmp Directory to allow more swapping ?
Pavan Kumar said
am not sure about the way on how to do that. better install linux again. but before that, check your /tmp size using below command
$ df -h /tmp
also check if its writable by oramnr user or not
Brijendra Kumar said
I am not sure but may be it works, just delete the file which are there in /temp or increase the size of ur ram.
Or if u want to increase the ur swap
follow this……….
> Create a swap file and add it to the existing swap:
#mkfile m /swap
#swap -a /swap
Example:
#mkfile 20m /newdir/swapfile
#swap -a /newdir/swapfile
To make this swap space available (add the swap file to virtual memory) each time the system is rebooted, add a line in the /etc/vfstab.
#device device mount FS fsck mount mount
#to mount to fsck point type pass at boot options