Important Docs
This page allows me to share good technical docs that I came across and hope you will also learn the best about Oracle Database.
Note: Due to space constraints, these files will be replaced with new documents later, so request you to download and save it in your desktop
Interview_Qns – these interview q’s are collection from various websites. so you may find some repetition, plz bear with that
oracle-database-11g-top-features
Step by Step document for Physical Database creation
project-lockdown – a wonderful doc about security of database
Oracle 10g Installation on OEL 4.7
Oracle 11g installation on Linux
installing_oracle_10g_on_vmware(linux)
Oracle_10g_installation_on_windows
Manual_database_creation_in_linux_machine
how to configure DG broker and observer
Installing_Linux_on_VMware_Copy
Video Tutorial for creating physical standby database
Oracle 10gRAC On Linux Using VMware Server
Cloning Database Using RMAN Backup
twp_security_checklist_database
kanthi said
Hello Sir,
Good afternoon,
Sir I need a steps regarding schema refresh using datapumps.(In detail).I tried in internet
but i din’t get correct method,plz help me sir?
Pavan Kumar said
for schema refresh you need to take export and do import. commands are similar to below
expdp directory= dumpfile= logfile= schemas=”
impdp directory= dumpfile= logfile= remap_schema=’old schema:new schema’
abhiram said
Hi Pavan,
i got following error.
create table test(n number(3,2));
insert into test values(123.89);
ORA-01438: value larger than specified precision allowed for this column
NUMBER(3,2) doest allow value like 123.89 or 12.89.
NUMBER(4,2) doest allow value like 123.89 or 12.89.
but Number(5,2) allow 123.89 value.
could u explain wat exactly Number(3,2) ?
Pavan Kumar said
ORA-01438:
value larger than specified precision allowed for this column
Cause: When inserting or updating records, a numeric value was entered that exceeded the precision defined for the column.
Action: Enter a value that complies with the numeric column’s precision, or use the MODIFY option with the ALTER TABLE command to expand the precision.
but in your case, i can see that number(3,2) should accept 123.89. I need to have exact steps what you did?
abhiram said
Hi Pavan,
suppose i have table ” Employees”
And i created composite index on this table columns
CREATE INDEX employees_ix
ON Employees (last_name, job_id, salary);
last_name col has high cardinality
job_id col has high cardinality
salary has low cardinality
so could u tell me at wat time the index access done by query?
1. only ” last_name ” used in query?
2. both ” last_name ” and ” job_id ” used in query ?
3. both ” last_name ” and ” salary ” used in query ?
4. both “salary” and ” job_id” used in query ?
5. only ” job_id ” used in query?
6. only “salary” used in query?
7. Only All three cols involve in query?
7th option is correct but accord into oracle documentation even high cardinality cols use composite index with out specifying low cardinality cols in query .
how this is possible can u justify?
Pavan Kumar said
As per my knowledge in any of the situation you use column in where condition, index will be used. also there are some rules defined on when query will use index. see this URL http://docs.oracle.com/cd/B19306_01/server.102/b14211/data_acc.htm
abhiram said
Hi Pavan Sir,
currently im working in one mnc bangalore as PL/Sql developer and i haue knowledge on CORE DBA and RAC with APPS 11i/R12 and Perl and Unix shell scripting but i want become good Performance Tuning expert currently i have 2+ exp as pl/sql developer.
could please give valuable advice regarding what are fundamentals require and procedures to folow and any books i need read to get practical knowledge on tunig.
Pavan Kumar said
The only book that will give entire information is Oracle documentation. read “performance tuning guide” from http://tahiti.oracle.com
abhiram said
thanks u sir……..
Pavan Kumar said
welcome
Md.Asfi Alam said
Hi Pavan Sir,
I have got the chance to work as a linux admin in kuwait for cherrytec, so need your help in makeing the descission that should i join & will it be a good company to work with as read many thing abt cherrytec on net want to confirm it form you that how long you are working with this company & will be good for the career growth.
Pavan Kumar said
Hi, First of all I am not working in that comp. I am working in India. So not sure abour the company and its profile….
MD Asfi Alam said
i have asked to you because you have posted some of jobs in your blog which belong to cherrytec group, that is why i have asked you….
Pavan Kumar said
Ok, no problem
MD Asfi Alam said
Sir,
As adviser i ask you to suggest than what will you suggest….
need your help….
Please help
Pavan Kumar said
Hi Asfi, As I don’t about the company, I cannot exactly say about this oppurtunity. But as it is for abroad location, you can think of. But do find out about the company before taking a decision
MD Asfi Alam said
Thank You Sir….
Pavan Kumar said
you are welcome
chandar said
some times its throwing error , OS not found. I tried no of times but i instal Red Hat 4 its taken , i can’t understand this , plz help me.
Pavan Kumar said
I remember it will ask for manual partition in one of the screens. also if u r practising oracle10g/11g, then RHEL 4 is best than 5
venkatravichandar said
i agree with u sir , but present situations we have to know everything as a DBA, i tried that no of times , it’s not asking manual partitions , iam allocating 60gb for Red Hat 5, it’s taken defaultly 5gb for boot and home , another whole memory taken one drive means ( one partition), my request is how to create new part ions from old one .
Pavan Kumar said
I can remember that we can do manual partition, but forgot which screen. The problem I tried installing 10g on RHEL 5 only once and we need to apply so many rpm’s which I feel not comfortable and changed to RHEL 4. Coming to learning, generally this OS installation will be done by Unix admin’s in real time, so not to much worry about it
chandar said
Thank u very much sir.
chandar said
hi sir , i had instal Redhat 5 through vmware workstation 7, defaultly its taking partitions , but i wanna do manual partition , so plz help me.
Actually problem is after creating the virtual machine its
raghu said
hi sir this is raghu(ur bd23 student) plz tell what is the difference between OCA and OCP
Pavan Kumar said
OCA – Oracle Certified Associate. generally this is treated as you are a learner of Oracle technology
OCP – if you have this, it means you got some expertise on Oracle technology
harsha Pydi said
Just looked all ur comments … it is so helpful for refering..!!!
Pavan Kumar said
thank you so much Harsha
Manohar said
Hi sir, This manohar, please tell me, how to know “how many standby database configure in a environment”
Thank you sir,
Pavan Kumar said
we can check that using log_archive_dest_1,2,3….. etc parameters. If we have only one standby dest_1 and dest_2 will only be configured. If we have 3 standby’s then dest_1,2,3 will be configured…. just like that
Manohar said
Hi sir, this is manohar am searching as 3+ yrs of dba. In every interview they are asking about “daily activities”. I have some confuse to explain it. Plz tell me some real time daily activities.
Thank you sir,
Pavan Kumar said
please check this
http://www.google.co.in/search?aq=f&gcx=w&sourceid=chrome&ie=UTF-8&q=daily+activities+of+oracle+dba
kiran said
Hello Sir,
good evening
In a table 14 records are there and i insert one more record in that table,How could i fire a query
to get recent inserted row.
Pavan Kumar said
Hi Kiran,
Please read this http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:912210644860
kiran said
Thank Q very much sir…)
Pavan Kumar said
you are welcome Kiran
madhu said
Hi pavan, plz try to send answers
What are the various options to create more than 1 instance?
How would recover online redo log files?
What are node apps?
What is row migration and row chaining?
What are the various options to shut down and startup explain all?
How can you change database id?
How can u recover a datafile which doesn’t have backup?
How can u switch your logfile in rac database , if you are using hot backup?
How can u disable or enable rac?
How can u convert rac database to standalone or standalone database to rac?
What is catcluster.sql?
How will u recover the rman backup which has been deleted physically by system admin?
What are dataguard protection modes?
What are the main parameters to configure physical standby?
What is the use of lo_archive_max_process?
What is fal_server and fal_client?
What is standby_file_management?
What is segment space management?
What is use of parameters in exp/imp?
ignore,consistent,owner,direct,buffer and etc?
how will u recover ur database if u have lost one of your log member and what happens when u lost it?
What are the kernel parameters used to install rac?
Why do u share private ip on all the nodes?
How many ip’s do u require do install rac?
What are different kinds of cluster file system in rac?
How can u configure ocfs2?
What is oifcfg, ocrcheck, ocrdump, ocr, voting disk?
How will u recover voting disk,ocr ?
What will u recover voting disk which doesn’t have backup?
How many kinds of backup can u have ocr backup and voting disk?
What are crs daemons?
What are rac background process?
When ur cluster nodes gets rebooted? Scenarios.
How can u relocate ur rac vip?
How to check whether ur standby is in sync to primary or not? Various
What is the use of duplicate command in rman?
What are main features of rman in 10g and 11g?
What is use of gsd and vip?
What is the use of srvctl? Some command also they may ask
What is ifconfig?
What kind of errors do u face when ur standby is not in sync with primary? Scenario
What is use of doing force_logging?
What kind of files would be take automatically with rman backup?
How can u recover database if one of the archive is missing using rman?
How to check incarnations?
How many voting disk can u have and how many ocr files can u have and what could be there sizes?
How will u check version of ur clustersoftware?
What are the main parameters needed to convert ur standalone database to rac database?
How many ways are there to convert standalone database to rac database?
What is use of ons?
What is taf?
Why do we need instance specific undo tablespaces?
What is process to delete node in rac and to addnode in rac?
How can u analyze awr report?
How can u maintain connectivity between all the rac nodes?
What is load balancing?
What is difference in using standby and using rac?
Thanks ,
madhu
Pavan Kumar said
Hi Madhu, i hope these are a copy and paste of some interview q’s
I am sure you can find all the answers for these questions in google. Have you tried that first?
madhu said
thanq
kiran said
Thank Q sir
kiran said
Hello sir,
good morning,
If I drop sysaux & undo tablespace,what will happen to the database?
Pavan Kumar said
some of the features like AWR etc will not work if you drop sysaux tablespace. Transactions will start using system tablespace as undo if you drop default undo tablespace
arun said
Hello pavan hw r u? Hpe u r superb.pavan u r blog is superb,u solve all the problems vich v face in dba but pavan can u plz advice for those people who r freshers including me who have jst completed oracle 11g dba n searching for jobs.Everywhere they r asking experience, as u have also started u r career as a fresher in this field,can u plz suggest wat v shld do 2 get job? Plz guide us n reply soon
Pavan Kumar said
Hi Arun,
Thanks for your comments. Frankly saying getting a fresher DBA job is a tough one. All we need is to have patience and not leave any chance. we need to keep on trying with our friends for references, upload resume in all job sites and daily or atleast very frequently we need to apply for jobs that are posted in those job sites. this is all what we can do from our side….
sridhar said
Hi sir, this is sridhar.mukta. please provide me rman cloning document.
Thank u sir,
Pavan Kumar said
its already there in my “Important docs” page…. plz take a look
kiran said
Hello sir,gd mrg
Hope you are doing great,
One of the interviewer ask question like this,how you check tkprof,i.e it is in instance level or in database level?will
you please clear it?
Pavan Kumar said
TKPROF is the report which gives information on how a sql statement is working in diff phases of sql (parse, execution and fetch). so it shows both at instance and database level
kiran said
Thank Q sir…)
Srinivasa Chowdary said
Hi sir,
Explain me what is cursor sharing and list out parameters related to that.
Regards
Srinivas k
Pavan Kumar said
this parameter is used to share cursors (a memory area to store value in the buffer/cache) between different sql statements. this will help in repeating I/O’s. for more info read this
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams035.htm
Srinivasa Chowdary said
thanks for the reply
kiran said
1.I AM TRYING TO CONN THRU ONE USER LIKE USER_LEE,BUT NOT ABLE TO CONNECT WITHOUT CONNECTING TO SYS FIRST.GETTING ERROR PROOCOL ADAPTER ERROR.can you please tell suggest me pavan?
2.what are the roles other then connect,resource which are given to the programmers,business analysists in real world.
Pavan Kumar said
1. I feel there should be some change to be done in sqlnet.ora file. plz check in google for solutions n steps
2. appln team will tell what roles or privileges they require. we will follow that
kiran said
can you please tell me by your experience what appln team told you in your previous assignments for roles given to programmers,etc.just giveme a real time example.
Pavan Kumar said
I din’t remember exactly because they will ask us to assign system or object level privs. sometimes they may ask to assign DBA role also….. but we need to give that only after mail approval from higher authorities like our manager etc
kiran said
Hello pavan
I have a question, in most of the interviews they are asking about the directory structure of the database.can you please explain me how will be the structure of databases will be in different companies you worked.
can you please tell me in DETAIL like how you answer tell me about your oracle dba experience.
Thanks
Praveen.
Pavan Kumar said
Hi Praveen,
I believe when they are asking about directory structure, may be they want to know whether your database is having Optimal Fliexible Architecture(OFA) is implemented or not.
Based out of my exp, I saw a environment where people used
/d01, /d02, /d03 – for datafiles
/d04, /d05 – for redologfiles
/d06,/d07 – for controlfiles
/arch – for archives etc
Here we need to identify that all the above mentioned mount points are different hard disks….. Hope that is clear. If not please post me your concern
kiran said
pavan it helped me in understanding that we have different hard disks/mount points to store CRD files,but what about the logical structures like tablespaces.Can you please take a example in your company and explain in detail how the structure and size will be.
what about the flash recovery area?
CAN YOU PLEASE TELL ME HOW TO ANSWER THE “TELL ME ABOUT YOUR DBA EXPEREINCE” WITH EXAMPLES.
THANK YOU PAVAN FOR YOUR GREAT BLOG
Thanks
Praveen.
Pavan Kumar said
What I had explained in last reply is in my previous assignment. The project which I am working now has databases on ASM. So there will be no directory structure in separate. Even in most of companies there will be no specific structure will be followed.
flash recovery area will be part of your Oracle home path and will be defined during DB creation. But I have not seen people using it either to store archives (generally archives will be stored on separate mount point as I told as per OFA) or backups (because backup will be taken to tape in real time)
when someone is asking about your DBA experience all you need to tell is in what companies you worked and on what projects and mainly what is the role and responsibilities you handled there.
kiran said
pavan what about the tablespaces, like can you give me example from your previous assignements like how the DBA’S will plan about the tablespace like the number of tablespaces,size,etc.
Pavan Kumar said
Hi Praveen, There will be no specification in no. of tablespaces and its size. Sometimes Application team will tell you how many tablespaces should be there and whats their size. But most of the times we need to decide that. You can have any no of tablespaces with what ever be the size. but always we need to make sure that datafiles are spreaded across multiple disks
prathap said
hi sir
DATABASE NAME:
db_name=’prod’
INSTANCES
Number of Instances: 3
instance_name=’PROD1′
instance_name=’PROD2′
instance_name=’PROD3’
i want to do coldbackup clonning how can i procced…………………………..by seeing above information u can understand it is a RAC …………..please provide me a steps for this clonning…………….
thanks
prathap
Pavan Kumar said
Hi Prathap, can you let me know if u r cloning from RAC to single node? or RAC to RAC?
prathap said
Hi sir,
iam clonning(cold backup) from RAC TO RAC…………………
Pavan Kumar said
1. Install clusterware and DB software on the target nodes
2. Take cold backup of source RAC database including spfile or pfile
3. copy all the files including spfile or pfile to the locations in target (I hope you are using same directory structure in source and target. If not see my post in cloning category – http://pavandba.com/2009/12/17/database-cloning-with-cold-backup-when-using-diff-directory-structure/)
4. start your instances in node by node
prathap said
thanks sir…………………can u please suggest me good indian blog for APPSDBA …………………..like u…………….bcoz pavan sir is not maintaining appsdba blog……………………………..
thanks
prathap
Pavan Kumar said
I found appsdbaonline.com bit more interesting. but not sure if it contains all apps concepts as i am not apps dba to understand the posts there
prathap said
hi
how to know how many TRIGGERS ARE VALID AND HOW MANY TRIGGERS ARE INVALID IN A SCHEMA………..PLEASE PROVIDE A QUEARY
THANKS
prathap
Pavan Kumar said
select trigger_name,status from dba_triggers where status=’INVALID’;
Ravinder Reddy V said
Hi Sir,
I have one more question.
How do we find the delay in the dataguard?
Pavan Kumar said
select sequence#,archived,applied from v$archived_log;
the above will tell you which are shipped and which are not….
Ravinder Reddy V said
Hi Sir,
This is Ravinder.
I have one question that when we login to the database using
sqlplus “/ as sysdba”
it wont promt for the sys password. But I need to set it to promt for the sys password.
Pavan Kumar said
Hi Ravinder, its not possible to set a prompt to ask password for SYS user when u r there on the oracle db server…. u may give a try with trigger (but i am not sure how far it will work)
also may i know the reason why u need this?
Ravinder Reddy V said
Hi Sir,
Thanks for your reply. These questions are asked in one of my interview.
Pavan Kumar said
you are welcome Ravinder…
nagendra said
hai sir,
i am nagendra, i am regularly following ur blog,it is very nice.
i have a doubt about partioned tables,how to remove partion on a single column(table is also partioned) ?
please give the solution.
thank you
Pavan Kumar said
i hope your question is somthing like this. If i have a table partitioned on column A and B. Now how to remove B column on which already table is partitioned.
Am i right?
If so, then i believe we need to re-org table i.e need to create dummy table with only partitioned on A and insert the data, then drop original table. ofcourse, am not sure… am also checking on this
Chinthala Mahenderreddy said
Hai sir,I have one doubt.please send some useful information about toad and putty.
Pavan Kumar said
Hi Mahender, there are lot of docs available in google. just search them and start reading them….
Thiru said
Dear Pavan,
We are on “32-bit,Window 2003 Server” and planning for an upgrade from 10.2.0.4 to 11.1.0.7.
Could you please help me with any related documents.
Thanks,
Thiru
Pavan Kumar said
I don’t have any specific document. But following are the brief steps
1. install 11.1.0.6 software in other oracle home (it should not be in same location as 10.2.0.4)
2. Download 11.1.0.7 patchset from metalink and apply it. (there will be a readme document for installing this patch. you can follow that)
3. start DBUA from 11.1.0.7 home. this upgrade assistant will help you in upgrading database.
This link will help you alot – http://download.oracle.com/docs/cd/B28359_01/server.111/b28300/toc.htm
I prefer you can upgrade to 11.2.0.2 which is the new version.
Thiru said
Thanks for the guidance Pavan…It will help me a lot for sure….
Pavan Kumar said
you are always welcome
sai krishna said
Hi sir,
I would like to know about oracle cpu patches. Up to my knowledge for every three months oracle releases this patches for security related issues.Recently at july 17 also oracle released this patches and i am not able to understand what’s the patch number /patch id released at that date how to know this patch numbers can i download this patches for practice purpose if so how can i ?
Pavan Kumar said
Hi Sai,
you are right. patch number will be changed and it depends on paltform. for example below are patch numbers for different oracle versions on unix OS
11.2.0.2 CPU Patch 12419321
11.2.0.1 CPU Patch 12419278
11.1.0.7 CPU Patch 12419265
10.2.0.5 CPU Patch 12419258
10.2.0.4 CPU Patch 12419249
You can download these patches only if you have my oracle support (metalink) access which is a licensed online support by oracle…..
sai krishna said
thank’s sir
Saikrishna.M
sai krishna said
Hi
My production database acting as a standby database and standby database as primary. If i want to upgrade my production database from 10g to 11 total downtime required is 10 hrs but client wants to do with the task with in 5 hrs what will u do at that time? and can we configure primary database in 11g and standby database in 10g will the binaries of 11g recognized by 10g
Pavan Kumar said
Hi Sai, in such case you can make standby available for users and upgrade primary to 11g, then rebuild standby with new 11g primary. this will reduce the downtime.
10g standby will not recognize the 11g binaries….
sai krishna said
In continuation what about the archives generated at standby, during the primary in down time, The archives will not be applied to primary from standby right and if i upgrade the primary to 11g there will be some log gap naa how to resolve this?
Pavan Kumar said
thats the reason, its better to upgrade both of them at same time. you can check that from upgrade doc. check in metalink also for docs…
ramesh said
Hi pavan,
What is the importance of orapwd file in networking?
Thanks
ramesh
Pavan Kumar said
that file is used to authenticate a user who is connecting as sysdba from a remote machine to the db. read more from oracle docs
venkat said
hai pavan, plz try to send answers .
1.what is the diff between cmm5 d cmm3?
2. What is your typical day like?
3. What DBA activities did you to do today?
4. Do u consider yourself a development DBA or a production DBA and why ?
5. Are you a nuts – n- bolts DBA or a tools-n-props DBA?
6. Give the reasoning behind using an index ?
7. what is the difference between the Archivelog mode and noarchivelog mode?
8. what column differentiates the v$views to the GV$views and how ?
9. explain an ora-01555?
10. differentiate between $oracle_home and $oraclebase?
11. How would u determine time zone under which a database was operating?
12. you have 4 instances running on the same unix box. How can you determine which shared memory and semaphores are associated with which instance?
13. what is the recommended interval at which to run statspack snapshots, and why?
14. How would u edit ypur CRONTAB to schedule the running of /test/test.sh to run every other day at 2PM?
15. How would u begin to troubleshoot an ORA -3113 error?
16. An automatic job running via DBMS_JOB has failed. Knowing only that “it’s a failed “, how do u approach troubleshooting this issue?
17. How would you extract DDl of a table without using GUI tool?
18. you’re getting high “busy buffer waits “ – how can you find what’s causing it?
19. what query tells u how much space a table space named “test” is taking up,
and how much space is remaining?
22 Database is hung . Old and new user connections alike hang on impact. What do
you do / Your SYS SQLPLUS session is able to connect?
Pavan Kumar said
1. those are levels assigned to companies to differentiate them based on some standards
2. search in google
3. search in google
4.prod DBA, bcz they will face lot of issues daily
5.search in google
6. index will make searching faster
7. archivelog mode will helps us in database recovery up to the point without data loss
8.inst_id
9.it will occur when server process unable to identify old data from undo or new data from datafiles
10.oracle_base is location whr you install oracle s/w. oracle_home is home path for database
11.chk in nls_database_parameters view
12.generally we will not look for it
13.its out dated. now we are in 10g n it will be done every 1 hour
14. using crontab -e command
15. chk if any other session is connected or chk if mount point is full
16.we can check in views related to JOBS
17.using dbms_get.ddl package
18.there are no free buffers in buffer cache, it may occur due to small size
19. dba_data_files n dba_free_space
20.chk why it is hung. most common reason is archives got full or sessions exceeded the limit of SESSIONS parameter in pfile or spfile
i prefer you read oracle docs where you will get entire knowledge on the q’s you asked.
venkat said
thanku very much
poornadba7 said
HI Pavan,
hope u r doing excellent..i am working as DBA and mostly i worked on linux environment but i’ve changed my company recently. But here they working on windows ..i am finding difficult to this windows..can u let me know ur suggestions how i can cope up quickly for this windows..paging and how can we run commands..here..pls suggets me
Pavan Kumar said
Hi Poorna,
as you might have observed by now that basic admin of database is same either in windows or linux. only difference will be in scripting and some OS commands. so i suggest you to refer to windows commands in google. it will be very helpful.
poornadba7 said
Thanks allot Pavan,
Actually i need to configure 2 node windows 2003 cluster in the next week. Can u help me out as i was never configured clustering.
Thanks
Poornachander
Pavan Kumar said
Hi, i hope you are talking about installing oracle RAC cluster. if so you can get good docs from google.
http://www.google.com/search?rlz=1C1CHKZ_enUS440US440&sourceid=chrome&ie=UTF-8&q=configuring+RAC+on+windows+2003
Neeti said
Hi Pavan
How to recover the control filerif all mirror copy lost and no backup is there ? Tell me all steps
Pavan Kumar said
Hi Neeti,
you can get all the steps from this link http://pavandba.wordpress.com/2010/03/18/how-to-do-complete-recovery-if-controlfiles-are-lost/
kumar said
Hi Pavan…
Which background process will write alertlog file?
Pavan Kumar said
I suppose you asked these q’s thru mail. I answered them… plz check
kumar said
What is difference between incident management and change management? With examples……
bheeshma said
hi pavan ,
how can we know that this error belongs to user or core or background in alert log file .bcoz when am practising some error came like control files not found .then where should i search that in alert log i mean in which dump.
Pavan Kumar said
Alert log always will give you trace file location in case error contains more information. depends on location like bdump, udump etc you can decide which trace file it is. In your case, you need to fisrt search in alert log file. (but i assume there will not be any trace file for the same). I think you know that alert log will be there in bdump
bheeshma said
k thanks for the reply ..cheers
Thiru said
Hi Pavan,
At the time of auditing,we found that PUBLIC in database have DBA role by default.we can see that by this query,”select * from dba_role_privs where GRANTEE =’PUBLIC’;”Later I found that it will be created at the time DB creation.My question is If I revoke the DBA role from PUBLIc,will it affect any of DB operations.
Please help me..
Regards,
Thiru
Pavan Kumar said
Hi Thiru,
It is very dangerous to have DBA role to PUBLIC schema as it will reflect the same for all the users in the database. As you said, yes PUBLIC schema will be created during Db creation. But someone might have granted this DBA role to public without knowing its affect.
Please remove it immediately and revoking will not have any affect on the database.
If want to know, how granting privs to PUBLIC is dangerous, see this http://pavandba.wordpress.com/2010/01/21/user-access-other-schema-tables-without-privileges/
Thiru said
Thanks Pavan….
Thank you very much for reply with explanation…
Keep rockin…
kumar said
Hi Pavan sri,
This is Srimannarayana from Hyd.Today only i see u’r site.It is excellent for DBA’s.First of all i congrats u for sharing the knowledge to others.I request to u to keep the details how to face the interview as a fresher and an experience guys.Prepare the list for this like…
1.Self Introduction
2.Roles & Responsibilities
3.Topic wise FAQs and related indepth concepts.
4.Ora errors and real time problems u r faced and challenging issues.
Put heading like “face the Interview easily”
I hope u do this well within a days.
Pavan Kumar said
Hi Sriman, its a nice suggestion. but in google, already you will be getting all these questions with answers. I have that doc. Let me upload that into my “Important docs” section
Thiru said
Hi Pavan,
You are rocking as usual….keep rockin.
Im using windows 32 bit os with 10.2.0.4 Database.I need to develop a script that will monitor tablespace size and sent an alert to email.I did the same in linux but I doesnot know in windows.
Please help me out.
Regards,
Thiru.
Pavan Kumar said
Hi, the best way is to do that through EM. It will be easy to monitor and get alerts…..
Thiru said
Thanks Pavan….
Samyak said
Hi Pavan ,
Nice blog and awesome docs…looked at RAC installation doc … really good
thanks for share …
Pavan Kumar said
thank you Samyak…
Azhar Ali Khan said
Hi Pavan,
Please upload doc regarding ‘How to connect two virtual machines in VMWARE and configuring RMAN on VIrtual MAchines’.
Pavan Kumar said
sure, will upload them soon
praveen said
pavan thanks for the great blog that you have created.can you please tell me how to refresh data from production to development database in windows as well as in linux?
Pavan Kumar said
hi, it depends on type of refresh. if you are refreshing a single schema, always its preferred to use export/import or datapump. if it is entire database, these days we are using RMAN cloning very widely. otherwise hot or cold backup can also be used.
praveen said
can you please provide a document or step by step procedure for datapump and rman cloning also.
Thanks for the reply it cleared my doubt but i wanted to do it practically in my testdatabase.
Pavan Kumar said
For datapump, you can refer to oracle documentation. all we need to do is expdp help=y and start practising all the options. For RMAN cloning, you can refer to a doc in my “important docs” page
abhiram said
thank you sir for earlier answers
————————————
When a redo log file is full, LGWR will move to the
next log group.
if i haue 3 redo log groups each contains 2 log files,if lgwr writing to the first log group. will it(lgwr) move to second log group after writing 2 log members or 1 log member ?
does lgwr assign same log sequence number to log members with in redo log group ?
Pavan Kumar said
1. LGWR will move to 2nd redolog group only after writing into both the members in 1st group.
Here we need to understand how LGWR will write into them. LGWR will not write to members within group in sequential order, rather it will write parallely. (but LGWR writing parallely will happen only if Asynchronous IO is enabled at OS level). If LGWR writes sequentially and a crash occurs, we will be loosing some data
2. yes. Oracle assigns same number. More clearly to say common log sequence number will be there for all the members in same group
shiva said
Hi Sir
This is shiva
Trying to get DBA job
i’m very thankful to you for your blog n imp docs
i’m learning some new things by watching your blog..
and i also i’m getting knwldge in interview point of view
Thanks Alot…
Pavan Kumar said
Thank you very much Shiva for your comments…. Wish you all the best !!!
abhiram said
If the database is running in ARCHIVELOG mode and the log file group to which the member belongs is not archived, then the member cannot be dropped.how we know names of this log members any view is support or not ?
Pavan Kumar said
I had give query for your ealier question. the same query applies here. You can drop a redolog group only if its status is INACTIVE. if you see CURRENT or ACTIVE, then you cannot drop it
abhiram said
hi pavan sir, i need to add online redo log group to production database according to restriction add additional redolog group and drop existing one,but how i know if im tring drop redolog group is online(lgwr writing ) or not ?
Pavan Kumar said
select group#,status from v$log; – this view will tell the status, if you see some group as CURRENT that means LGWR is currently writing into that group
baji said
hi,
How to get size of a table? and difference b/w index rebuild online and index rebuild?
Pavan Kumar said
checking table size
SQL> select sum(bytes/1024/1024) from dba_segments where segment_name=’table_name’; —> this will give size in MB
Rebuild online will allow you to issue DML statement on the table where rebuild doesn’t
baji said
how to know all privileges of a particular user? how to know sql text that is entering by users?(which view contains sql_text column?)
Pavan Kumar said
you can use below six views
dba_sys_privs
dba_tab_privs
dba_role_privs
role_sys_privs
role_tab_privs
role_role_privs
2. you can get that info from v$sql view. you can also enable auditing for tracking user’s sql statement
baji said
Hi,
what is the importance of OraInventory? what is importance of FAL_SERVER and FAL_CLIENT parameters in Dataguard? How to check the load on database at db level and os level?
Pavan Kumar said
OraInventory will store all the oracle products installed on a particular server. So during upgrade or migrations, it will help us to know how many homes existing etc kind of information.
FAL_SERVER and FAL_CLIENT are used to sync the standby database with primary in case archives are not shipping to standby location because of network failure etc.
Load at OS can be checked using top command. on database its better to use EM if version is 10g
baji said
Hi sir,
can we alter sga_target parameter without altering sga_max_size?(sga_max_size=10g and sga_target=6g….. i want to alter my sga_target to 8g)….
and i want to thank you because i am getting more knowledge on oracle dba by watching your blog… thanks alot.
Pavan Kumar said
yes you can alter it without touching sga_max_size. only when you want to change sga_max_size value, you require a restart of the database as it is a static parameter
baji said
HI
How can we change “sys” user password? If checkpoint is not functioning well then what we have to do? can we change check point occurring time?(default is 3 sec.)
Pavan Kumar said
Hi Baji,
We can change sys password in two ways
1. SQL> alter user sys identified by ;
or
2. recreating password file using orapwd utility
$ orapwd file=orapw password= force=y
Regarding checkpoint, it is not by default 3 sec. Checkpoint time can be changed using log_checkpoint_interval till 8i and fast_start_mttr_target from 9i. Also, if checkpoint is not functioning, it will leave the reason in alert log file. so we need to check there for the reason.
baji said
we have to login as which user to change password of sys?
Pavan Kumar said
you can login as system user. but if you are on the server, you can use
$ sqlplus “/ as sysdba” – this will not ask any password
bharadwaj komaragiri said
HiPavan
It’s very much appreciable if we can have Oracle 11g installation on Linux doc
Pavan Kumar said
hi, i will upload that doc in a day or two into my “Important docs” page
baji said
hi,
Please tell me some patching and cloning issues that you have faced? it will be useful to me.
Pavan Kumar said
hi baji, i would be posting some articles in the blog soon… keep watching
baji said
hi sir,
what is the importance of ‘oratab’ directory? and how to find how many cpu’s are there in our system (i know only one way, that is cat proc/cpuinfo), is there any other way?
Pavan Kumar said
oratab file will be used to identify how many databases are there totally on the server, irrespective whether they are up or down. Also DBCA will use the info from oratab to find any database information like SID, ORACLE_HOME etc.
Reg cpu’s we will also use the same command and its the easy way i believe…
Dileep said
Hi sir,this is Dileep looking for a Apps DBA job,your blog is helping out a lota.You are doing a gr8 a job.Keep blogging…
Pavan Kumar said
thank you Dileep for your words….
baji said
hi sir,
what are the daily activities of a Oracle appsDBA. I am trying for a job as oracle appsDBA. please help me.
Pavan Kumar said
Hi Baji, unfortunately I am not yet into apps dba. so not sure of daily activities. but i think you can get the things by googling it
Sai Preetam said
Hi Pawan,
Its really great that some one like you is helping out people like me in every aspect view DBA point view.
I am very new to the DBA community and I would request you to share with me the starting guide for Oracle DBA. I would love to attend your sessions, but my time and situation doesnt permit me to do so
Thanks
Sai Preetam
Pavan Kumar said
Hi Sai,
Thanks for your kind words, but i am not doing any great thing here…. just sharing my knowledge and experience.
Its good to know that you are looking into DBA. For starting phase, I recommend to read a book by SAM.R.ALAPATI titled as “Expert Oracle database 10g administration”. (publisher is APRESS)
You are always welcome to attend the sessions, but i too feel bad about your timings….
vemuri said
hi sir,
what is the meaning of capacity plan in storage management
Pavan Kumar said
Capacity planning is nothing but checking free space in all the tablespaces of a database and make it ready for future space usage to avoid last minute space constraint problems…
sandeep said
Hai,
may i know the difficult task you faced as oracle DBA
Pavan Kumar said
handling multiple projects at same time for different clients is typical task that i handled till now which am doing it now
if you are asking for technical side, they are so many like db recoveries, handling performance problems etc
Rafiq said
Great contribution ever made by an Oracle Expert from hyderabad.
Most helpful blog one can find.
Thanks a lot pavan.
Pavan Kumar said
Hi Rafiq,
Thanks a lot for your comments, but am not a expert still trying to acheive it
sudhir said
Hi,
I need to learn Oracle RAC(real application clusters) from next month(oct 2010) first week, i have no time spend more then 10 days, please advise me any crash course available that time, send me fees details.
Thank’s & Regards
sudhir
Pavan Kumar said
i had sent mail on this to you
pavan said
Could you also let me know , if there is some one to train me on RAC + Golden gate.
i am eagerly waiting on someone to help me out on this issue.
Could also email me the info pavankumar.348@gmail.com
Regards
Pavan.
Pavan Kumar said
Hi, as of now i don’t know who can train on golden gate. KANNA TECHNOLOGIES is planning to start RAC also. you can leave your mobile number in their enquiry form, so that they will intimate when the batch starts
gandhi said
sr i get like ubove when im installing 10g, after i typed command
vi /etc/systel.conf now what can i do sr…
how set the kernel parameters…..
plz tell me the process sr im folloing ur 10g installation process….
Pavan Kumar said
i dropped a mail to u on this. if still having problem, chat with me in gtalk
gandhi said
# Kernel sysctl configuration file for Enterprise Linux
#
# For binary values, 0 is disabled, 1 is enabled. See sysctl(8) and
# sysctl.conf(5) for more details.
# Controls IP packet forwarding
net.ipv4.ip_forward = 0
# Controls source route verification
net.ipv4.conf.default.rp_filter = 1
# Do not accept source routing
net.ipv4.conf.default.accept_source_route = 0
# Controls the System Request debugging functionality of the kernel
kernel.sysrq = 0
# Controls whether core dumps will append the PID to the core filename.
# Useful for debugging multi-threaded applications.
kernel.core_uses_pid = 1
~
~
~
~
~
Type :quit to exit Vim
pratap said
Hi Pavan,
This is great as ur helping us (updating the blog every day),a small request it wud be much helpfull for us ….if u post erros/problem which u will face every day ….like opening database,issue with RMAN,when u add datafile/TB ,when u take backup….in a separate TAB , it wil be much help full for us
Who just joined in the company or who are attending for interviews
Pavan Kumar said
Hi Pratap, thanks for your comments
ofcourse i am trying my best to post all those what you asked and some are already there. just browse in categories…all you need is to go through each article
i am sure it will help alot