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.
oracle-database-11g-top-features
Step by Step document for Physical Database creation
project-lockdown – a wonderful doc about security of database
Performance Tuning
Analyzing AWR/statspack report-1
Analyzing AWR/Statspack report-2
Analyzing AWR/statspack report-3
RMAN Tuning with memory buffers
Oracle Installation Documents
Installing_Linux_on_VMware_Copy
Oracle 10g Installation on OEL 4.7
Oracle 11g installation on Linux
installing_oracle_10g_on_vmware(linux)
Oracle_10g_installation_on_windows
Oracle 10gRAC On Linux Using VMware Server
Oracle Database 11G Installation on Solaris 10
Manual_database_creation_in_linux_machine
how to configure DG broker and observer
Video Tutorial for creating physical standby database
twp_security_checklist_database
Add a Database Instance on the New Node
Automation of Database on Linux
RMAN & Media Manager Troubleshoot
RMAN Cloning using Recovery Catalog
step by step document for 10.2.0.4 patchset installation
step by step document for 10.2.0.4 patchset installation and Manual DB upgrade
Pavan DBA's Blog 
mani said
Which process is responsible for writing data into dumpfile?
Pavan DBA said
it is datapump worker process
bhavanisankar said
please send oracle dba roles and responsibulites to bhavanisankar514@gmail.com
Pavan DBA said
you can find them in google
Madhu said
Hi Pavan sir,
I have searched something about ORACLE DBA in google, luckily i got your website… i have found answer for that… then i started going through the all your posts till end. I came to know many things from your blog. Its simply awesome sir… you are really doing great job sir. Its really helpful….
Pavan DBA said
thank you very much madhu
Mir Sadath Ali said
Thank you very much pavan sir, I newly entered in dba, my background ccna networking, I really appreciate your job for helping students. Sir, in your blog mostly 10g docs and few are there about 11g, could you plz update your blog about full doc 11g and 12c….it will be very kind of you….
Pavan DBA said
thanks. will do it
uday paritala said
Redolog file will be having 3 states current,active,inactive.
In noarchivelog mode active status available or not?
Pavan DBA said
yes. still active status will be there even though there is no significance for it in noarchive log mode
saikrishnamadamalai said
Hi Pavan,
I need some clarification on undo table space.
Actually size of my undo table space is 16GB.
Undo retention period 24hrs.
Undo retention was NOGUARANTEE.
If you see below unexpired blocks are almost equal to 16GB.
SELECT SUM(BYTES)/1024/1024 “MB” ,STATUS FROM DBA_UNDO_EXTENTS
GROUP BY STATUS;
16537.5 UNEXPIRED
0.625 EXPIRED
0.25 ACTIVE
1)If any one tries to load data then they will face error ORA-30036: unable to extend segment undo table space or not?
2) Did i have to wait for 24hrs to get clear of this undo tablespace as per retention?
3) As we can’t flush this undo tablespace. Is there any way other than recreating undo to clear undo tablespace?
4) As retention was no guarantee will oracle will use unexpire blocks for further transactions or not?
Thanks,
Sai Krishna M
Pavan DBA said
please find answers inline
1)If any one tries to load data then they will face error ORA-30036: unable to extend segment undo table space or not?
yes. they will get error
2) Did i have to wait for 24hrs to get clear of this undo tablespace as per retention?
yes. till that time blocks will remain
3) As we can’t flush this undo tablespace. Is there any way other than recreating undo to clear undo tablespace?
you can create new undo tablespace and make it default. or resize your existing undo tablespace
4) As retention was no guarantee will oracle will use unexpire blocks for further transactions or not?
oracle will make use of only expired blocks
Hari said
Hi Pavan Sir,
am new for this, and i finish the oracle 11g DBA course & SQL which is best way to start my Carrier SQl or Oracle. am trying job for since 6 months but i didn’t get single interview call, how can i get a job where i can search the job..
regards
harinath
Pavan DBA said
Hi Hari, plz drop an email to me – mymailbox.21@gmail.com or chat with me on my facebook id – Pavan DBA
Raviteja Sama said
Pavan, as Undo is in No Guarantee mode, unexpired blocks won’t be over written by oracle. Please correct me if am wrong.
Pavan DBA said
correct
Mohammed Ishaq said
Hi,
Presently I am working I T tech support
i have four years experienced Network admin but i am switch to Oracle dba and apps dba
so please tell me best institute and giving the placement.
Pavan DBA said
for training u can contact corporate@dbatalent.com. i am not sure about placements.
Mohammed Ishaq said
thanks.
narayanreddy said
Iam narayana reddy, I have learning DBA in ameerpet. Plz explain Rman briefly….
.i have some little doubts on Rman.
Pavan DBA said
plz read this
https://docs.oracle.com/database/121/BRADV/rcmquick.htm#BRADV89346
Murali said
Hi Pavan,
Hope your doing Good ! i am an Ex- Student from Kanna Technologies
I have one question regarding capacity planning.
We have 8i,9i Databases in our environment and we are and frequently getting space issues — DB size exceeded.
I would like to know how we can estimate the DB growth for Next 6 Months and how do we plan the Storage.
Is there any standards where we can estimate the growth and plan the storage .. ?
We have 30GB databases …
Thanks,
Murali
Pavan DBA said
hi murali, you can collect tablespace usage every week for a month and save that in either excel or any other format. after a month, u can check avg of tablespaces between beginning of month and also end of month by which u will get to know how much space is occupied in 1 month.
suppose you got 10GB as value then for 6 months u can say 60GB approx is required. also plz refer to this document
https://pavandba.files.wordpress.com/2009/11/capacity-planning-with-oracle.doc
Suriyaakumar G M said
Hi Pavan,
This is Suriyaakumar… I am completing my M.tech in Database Systems @ IIIT Srirangam. I have basic theoritical and practical knowledge about oracle. I am willing to work in either oracle or mysql. May i know any oppurtunities exist in syntel…I am basically from chennai . Thanks in advance
With Regards,
Suriyaa
Pavan DBA said
there are lot many openings. its good for career
Prem Kumar C said
HI Pavan,
Please send the oracle 11g DUMP for OCP certification.
preekpremcm@gmail.com
mohan.rajendran@gmail.com
Regards,
Prem
Pavan DBA said
plz chat with me on mymailbox.21@gmail.com
Sanjay said
Dear Pavan,
First of all tank you sharing ur knowledge for us… It is so encouraging for us to learn things in your blog..
My Needs are:
I am new to this carrier, recently we have implemented dataguard and now we are planning to upgrade our DB from 10.2.0.5 to 11.2 in solaris 10 also to install RMAN concept.
In your blog i could see document only for windows OS. Kindly request your assistance and guide me with some documents or video tutorials to upgrade the DB from 10.2 to 11.2 in solaris and to implement RMAN.
Thanks in advance.. Waiting for your valuable response…
Regards,
Sanjay G
Pavan DBA said
similar steps are there for unix. also rman is in built. not req separately to configure
mohamedfarouk said
Can I import a table from unrestored (a dump file) which is stored on my E drive without restoring this dump thanks a lot
Pavan DBA said
hi, your question is not clear to me. can you plz explain a bit more? what u mean by unrestore?
aabid janwari said
hi sir
i have completed my bba and now pursuing mba and want to learn about oracle dba and its architeture plz recommand some links
Pavan DBA said
https://pavandba.com/recommended-books/
Srinivas said
I’m a PL/SQL with Linux Developer working in MNC at Hyd. I have 4 yrs of exp. Right now i’m planning to upgrade technology. Could you please suggest any Oracle related technologies.
PL/SQL developer can upgrade to oracle DBA ?
Thank You…
Pavan DBA said
yes. dba would be good choice to developers.
Mohan yadav said
Hi sir
I have done B.com. Rightnow pursueing oracle dba. What do u suggest best for future.
Pavan DBA said
plz learn oracle rac and apps dba also
sridhar.L said
hi pavan, can you please tell me, on what basis indexes are created?
Pavan DBA said
http://docs.oracle.com/cd/B10501_01/appdev.920/a96590/adg06idx.htm
Naveen said
Hi Sir,
Could you please let me know how to track user sql statements(queries performed by user to the database) with respect auditing?
I have googled it but not getting the exact result what I am expecting. Plz help me on this..
Thanks,
Naveen
Pavan DBA said
you need to enable auditing and then use below command
SQL> audit all by schema name;
if you want specific statement, then use
SQL> audit create table;
Kumar Vish said
Informative… Thanks for sharing
naveen said
when i am trying to connect oracle from linux prompt with following command
]$sqlplus / as sysdba
i am getting error like
-bash:sqlplus:command not found please give solution for this
Pavan DBA said
u need to set environment variables. plz check google or my installation document in “important docs” section to get the steps.
shaik said
ANNA SUPER..
Ankur Dayama said
Hi Pavan,
Thanks for writing such a useful blog.Please help to upgrade a db from 11.2.0.3 standard edition to 11.2.0.3 enterprise edition.Also need a step by step doc for standby database creation.
Thanks
Ankur Dayama
Ankur Dayama said
One small mistake its 11.2.0.2 not 11.2.0.3
Pavan DBA said
r u doing it in same server or diff server? i have already posted this in my blog. see this link
https://pavandba.com/2009/12/21/moving-from-the-standard-edition-to-the-enterprise-edition/
standby creation document is there in my blog. check out in important docs section.
Santhosh said
Hi Pavan
I am Santhosh , again I am asking same question , I didn’t get any solution from your side. I tried for RAC installation on Linux (VMware ),
I created shared storage and mounted the directory in both nodes, after that nodes are rebooting, even though I set same time in both nodes. Please solve this issue , I am trying this since last 15 days.
Pavan DBA said
r u following any document for installing?
Santhosh said
Hi Pavan
I am installing oracle 11gr2 on Linux in VMware , I configured up to shared storage , and mount shared directory in both the nodes , then im ready to install clusterware , at that time machines are rebooting even I set same time in both nodes, I am unable to solve the issue because its rebooting in fraction of seconds even not to wait enter the username,password.
Pavan DBA said
have u verified log files? if so what is the error msg thr?
ellareddy said
Hi sir, This is Ellareddy
Today I faced this issue in my office,This is 3rd time i am getting same problem
user got following error :
Intermittent SQLException: OALL8 is in an inconsistent state.
I checked alertlog file ,it had following message , i got the answer from is raise a SR to oracle support but i bounce the database working fine.
.wt is permanent fix with raise SR.
ORA-07445: exception encountered: core dump [_intel_fast_memcmp()+81] [SIGSEGV] [Address not mapped to object] [0x000000000] [] []
Wed Feb 12 10:07:35 2014
Errors in file /oraeng/admin/uops/udump/uops_ora_17303.trc:
ORA-07445: exception encountered: core dump [_intel_fast_memcmp()+81] [SIGSEGV] [Address not mapped to object] [0x000000000] [] []
Wed Feb 12 10:09:28 2014
Errors in file /oraeng/admin/uops/udump/uops_ora_17307.trc:
ORA-07445: exception encountered: core dump [_intel_fast_memcmp()+81] [SIGSEGV] [Address not mapped to object] [0x000000000] [] []
Wed Feb 12 10:09:59 2014
ellareddy said
i got the answer from google is raise a SR to oracle support.
.wt is permanent fix without raising SR to oracle .
Pavan DBA said
hi, for ora-7445/ora-600 and ora-700 errors, apart from raising SR, we don’t have any other way to get the permanent fix.
ellareddy said
wt is the solution to :Intermittent SQLException: OALL8 is in an inconsistent state
Pavan DBA said
you need to raise SR and get the solution from oracle corporation itself. even i can’t able to tell.
udhaya said
Its really helpful for me to understand where we i lost in my previous interview. Thanks.
If anyone wants to Learn oracle in Chennai go to the Besant Technologies which is No.1 Training Institute in Chennai.
Ravi Kumar said
Hai pavan Sir,
This is ravi kumar.ur ex-student..presently i am working as Mysql dba since 2 years..is there any openings in Mysql dba positions.plz let me know
Pavan DBA said
hi ravi, nice to see you. as you know any openings, i will be posting to my blog. so keep on checking here.
Prasanna Narayanan said
Hi Pavan, Good Day! I am 9i OCP DBA and planning to upgrade to 11g OCP. I am referring Oracle 11g New features document and 1z0-055 study guide. Could you please share 1z0-055 exam dumps.
Thanks & Regards,
Prasanna Narayanan.
kishore said
4+ exp oracle dba interview questions on real time issues plz send me
Pavan DBA said
interview q’s already in my blog. real time issues, u can get it from oracle forums
Venkatesh said
Hello PavanDba..!
I have 1.8 yrs of experience as Mysql Dba. Now I wanted to switch to Oracle DBA. I undergone one of interview, he asked, why u wanted to switch over to oracle. I think I was unable to give satisfactory answer to them.
Can you help me out, how to face such questions..??
Pavan DBA said
you can say that you are passionate to do oracle dba than MySQL dba or u can say I am interested in multiple database technologies
sure said
@echo off
cls
@echo *****************************************************************
echo Script:Generating MSSQL Entitlement Report
set mydate=%date:~4,2%-%date:~7,2%-%date:~10,4%-%time:~0,2%%time:~3,2%
echo DATE:%mydate%
echo.
setlocal EnableDelayedExpansion
rem setting the environment
set PATH=%PATH%;C:\Program Files\Microsoft SQL Server\100\Tools\Binn\
@echo ********************* SQL SERVER DETAILS *******************
@echo.
rem passing input values
:servername
set /p ServerName=Enter Server Name :
if “%ServerName%”==”” (
echo.
echo.
echo Servder Name should not be blank
goto servername
)
:environment
@echo *************** SQL SERVER ENVIRONMENT *********************
echo 1.PRODUCTION
echo 2.DEVELOPMENT
echo 3.UAT
echo 4.LAB
echo 5.COB
set /p ServerEnv=Enter Server Environment :
if “%ServerEnv%”==”” (
echo.
echo.
echo Server Environment should not be blank
echo.
echo.
goto environment
)
if %ServerEnv%==1 (
set Env=PRODUCTION
)else if %ServerEnv%==2 (
set Env=DEVELOPMENT
)else if %ServerEnv%==3 (
set Env=UAT
)else if %ServerEnv%==4 (
set Env=LAB
)else if %ServerEnv%==3 (
set Env=COB
)else (
echo.
echo.
echo Invalid Environment
echo.
echo.
goto environment
)
rem setting output and log file names
set mydate=%date:~4,2%-%date:~7,2%-%date:~10,4%-%time:~0,2%%time:~3,2%
set LOGFILE=BATCHSCRIPT-LOG-MSSQL-%Env%-%mydate%.log
set OUTFILE=OUTPUT_%ServerName%-MSSQL-%Env%-%mydate%.txt
set COMPARE=VERIFICATION-%ServerName%-MSSQL-%ENV%-%mydate%.log
rem Redirecting input details to log file
echo ***********************************************>%LOGFILE%
echo Date : %date%
echo. >>%LOGFILE%
echo Server Name : %ServerName%>>%LOGFILE%
echo Server Environment : %Env%>>%LOGFILE%
echo Server Type : MS-SQL>>%LOGFILE%
echo User Name : %USERDOMAIN%\%USERNAME%>>%LOGFILE%
echo.
echo check log file “%LOGFILE%”
rem checking master.sql and alldbs.sql files are exist or not
IF NOT EXIST master.sql. (
echo master.sql not found.. Trerminating script>>%LOGFILE%
echo master.sql not found.. Terminating Script…
pause
exit /b
)
IF NOT EXIST alldbs.sql. (
echo alldbs.sql not found.. Terminating script>>%LOGFILE%
echo alldbs.sql not found.. Terminating Script…
pause
exit /b
)
rem checking database connectivity
echo checking database connectivity>>%LOGFILE%
echo exit|sqlcmd -W -h-1 -E -S %ServerName% >>temp.log 2>&1
if %ERRORLEVEL%==1 (
echo.
echo.
echo NO DB CONNECTIVITY OR INVALID CREDENTIALS-Check the %LOGFILE%
echo.
type temp.log >>%LOGFILE%
echo Press any key to exit
pause > nul
exit /b
)
rem script for getting database names
echo set nocount on >db.sql
echo go>>db.sql
echo select name from sysdatabases where name not in(‘tempdb’)>>db.sql
echo go>>db.sql
echo exit>>db.sql
rem connecting to MSSQL and getting database names
sqlcmd -E -W -h-1 -S %ServerName% -i db.sql -o dbnames.txt
echo DATABSES List >%COMPARE%
echo ************************* >>%COMPARE%
type dbnames.txt >>%COMPARE%
echo. >>%COMPARE%
echo. >>%COMPARE%
echo Databse Connection Status>>%COMPARE%
echo ************************* >>%COMPARE%
rem script for getting Sql Server Version
echo set nocount on >db1.sql
echo go>>db1.sql
echo select SUBSTRING(@@VERSION,23,4) >>db1.sql
echo go>>db1.sql
echo exit>>db1.sql
sqlcmd -E -W -h-1 -S %ServerName% -i db1.sql -o version.log
set /p version=>%LOGFILE%
echo Fetching Data…>>%LOGFILE%
rem Excuitng script in master database
sqlcmd -E -W -h-1 -s “|” -S %ServerName% -d master -i %script1% -v DB_ENV=’%Env%’ > temp1.log 2>&1
if !ERRORLEVEL!==1 (
echo Not Connected To database : master >>%COMPARE%
echo. >>%LOGFILE%
echo Error connecting to database : master >>%LOGFILE%
echo ———————————————>>%LOGFILE%
type temp1.log >>%LOGFILE%
)else (
echo Connected To Database : master >>%COMPARE%
type temp1.log >>%OUTFILE%
)
rem run the script in all databases
for /f “tokens=*” %%a in (dbnames.txt) do (
sqlcmd -E -W -h-1 -s “|” -S %ServerName% -d %%a -i %script1% -v DB_ENV=’%Env%’ > temp1.log 2>&1
if !ERRORLEVEL!==1 (
echo Not Connected To database : %%a >>%COMPARE%
echo. >>%LOGFILE%
echo Error connecting to database : %%a>>%LOGFILE%
echo ———————————————>>%LOGFILE%
type temp1.log >>%LOGFILE%
)else (
echo Connected To Database : %%a >>%COMPARE%
type temp1.log >>%OUTFILE%
)
)
echo.
echo.
echo. >>%LOGFILE%
echo. >>%LOGFILE%
echo find final report in “%OUTFILE%”>>%LOGFILE%
echo find final report in “%OUTFILE%”
del temp.log
del temp1.log
del db.sql
del db1.sql
del version.log
del dbnames.txt
echo.
echo.
echo Press any key to exit
pause > nul
Pavan DBA said
can u tell me what this script is for? seems sql server database related
Dominic savio said
Hi pavan. I have installed linux on VM Ware. I am not able to connect linux server to putty. Please help me.
Pavan DBA said
check ur network settings. also u should select “bridged networking” in network adapter. for more details, check the video in my blog.
Ravi said
Hi Sir,
I am facing this problem and i am not able to connect sys. I have been tried all google view.
ORA-12528: TNS:listener: all approriate instances are blocking new connections
Please help me..
Pavan DBA said
its usually because DB_UNIQUE_NAME is not set correctly. check that first. if still you are facing issue, then you can add UR=A in tns entry. for sample tns entry, check google with “UR=A in tns”
Ravi said
Hi Sir,
I have already check add to UR=A in tns but same problem is coming.
Pavan DBA said
you need to set ur db_unique_name value correctly. then this issue will not arise
tangy said
Hello Ravi
I recieved this problem earlier when my database is in nomount and trying to access it with sys@dbname. remotely. If you are doing the same while clonning, please add a static entry to the database in listener (SID_NAME = and restart the listener. This would resolve the issue.
Please let me know what is your case if it is not above
ellareddy said
hi sir
Interviewer is asking me the following questions ,plz give me u r answer
1) How u r database mapping to ur application?
2) How to justify u r redolog files are adequate to ur database ?
Pavan DBA said
1. using tns entry or any other protocol
2. by checking log switches occurring per hour
ellareddy said
Thanq sir
ellareddy said
hi sir,
i am getting following error in alert log file.
Thread 1 cannot allocate new log, sequence 55998
Checkpoint not complete
i Checked in google for answer, is this correct answer (alter system set archive_lag_target=0 scope=both;)
Pavan DBA said
that’s just a message and you can ignore it
tangy said
If feel it can’t be ignored, Please generate a AWR report and find how much time is your database waiting for this event. If it is massive then increase the redo log size or increase the redo log groups or increase no of db writers.
ellareddy said
Hi sir,i am x stu of u r kt.
I am getting this error in alert log file …………. wt is d solu?
Memory Notification: Library Cache Object loaded into SGA
Heap size 3067K exceeds notification threshold (2048K)
Pavan DBA said
that’s a message for dba, not a error. so you can ignore it
tangy said
It depends on how often do you get.
Ahmed said
Hi Pavan,
I am an ex student of KT, I hope you are doing good.
I need your expertise to resolve this issue.
We have a production database on RHEL 4.0,ORACLE 10.2.0.1 and we would like to do a refresh of production into RHEL 5.0,ORACLE 11.2.0.1 for sandbox.
I did the following but facing difficulty to proceed further. could you please help me out here.
1)took cold backup of Production DB which is on 10.2.0.1
2)transferd the dump to sandbox which is on 11.2.0.1
3)created similar directory structure in sandbox and copied the DB files,control files and redolog files respectively.
4) edited Pfile by removing adump,bdump,sga_max_size and adding diagonostic_dest and sga_target values.
5) startup
Error: unable to process initialisation parameter
and it was taking $ORACLE_BASE/11.2.0/dbs/initprod.ora
It gave me the below error
“LRM-00101: unknown parameter name ‘diagonostic_dest'”
ORA-01078: failure in processing system parameters
I commented this parameter and started the db.
6) startup upgrade
It started in restricted mode.
7) alter system set disable restricted session.
8)@?/rdbms/catupgrd.sql
Error: it gave an error to run utlu112i.sql for tz_version time record.
10) @?/rdbms/admin/utlu112i.sql
Error: ORA-04023: Object SYS.STANDARD could not be validated or authorized
I cannot move forward….could you please help me with this.
Thanks in advance.
Pavan DBA said
you can’t do refresh between different versions of database like from 10g to 11g through cold/hot/rman backup. if you have different versions, then best way is to use datapump export/import.
Ahmed said
Thanks for ur reply. I did the same and addressed this Issue.
Pavan, One more thing. whenever i login to ur website, it usually hangs on me and takes sometime to load. whenever i try to type something it takes very long to appear in the comments box. So, I have to type in a notepad and paste it in the comments sections. Is this a problem from my browser side or some kind of regular behaviour from the website itself.
Thanks for all ur help.
Pavan DBA said
no. it might be problem with your browser as its working fine with many people
piyush kumar said
hi sir
my self piyush kumar i am completed my B.sc IT in 2007 and MCA in 2010 both are distance mode now i want to make my career in DBA tell me is it possible or not for me please sir help me
Pavan DBA said
yes. it is possible.
Jayender said
Hi Pavan sir,
I have one concern in installation process on linux and windows can please clarify my questions.
1)while we are installing any oracle version on linux OS we must to create ORACLE Group and user but installing on windows OS y we are not creating the same before we install the Oracle.
Pavan DBA said
in windows also one group by name ora_dba will get created. but this will happen automatically in windows. in linux it won’t happen auto because linux is more secured than windows.
sadan said
Hi Pavan .i want learn oracle dba course .please let me know the best institute to learn the dba .
Pavan DBA said
I am not sure from which location you are. if you are from hyd, you can try kanna technologies. I am providing dba training there. for more details, look the page in my blog on training
subhasish said
Dear Pavan
Can u specify what are the topics you are covering on advance DBA course
Pavan DBA said
sorry for replying late as i am out of city last week.
configuring physical standby, logical standby
upgrading both primary and standby database from 10g to 11.2.0.3
how to apply patches on standby db
active cloning, active dataguard etc
the above r some of the adv topics tht will be covered in course
venkat123 said
Hi,
hru? iam fine. sir my name is venkat iam a .net developer i have 3.8 years of exp on that . MY question is am i change to oracle dba platform please give me advise it is help ful 2 my to my carriear
Pavan DBA said
hi, as you are on development side, i think changing career is not good at this moment of time. if you wish you can change the technology and be continue on programming side. but if you are really interested on dba side, once you learn it, u need to start the job from the beginning which means all these 4 yrs of exp u have will become waste.
subha19821Subhasish said
Hii Pavan
Plz answer these basic question:
How oracle manage connection of sql* plus to oracle database both on the server and remotely from client machine
Pavan DBA said
can you plz explain wht is mean by managing connections?
subha19821 said
what i meant is :howconnection is made b/w sql*plus and oracle database from same node where database server is and from remote client as well
Thanks a ton in advance
subha19821 said
I mean how connection is made b/w sql*plus and oracle database
Pavan DBA said
connection in local node:
1. when provided with login credentials, pmon process will verify data dictionary and will authenticate the user
2. if authentication is successful, then it will allow the connection.
3. finally server process for that user connection will get created.
connection from remote node:
1. after giving login details, oracle will check for tns alias in tnsnames.ora to understand which server it need to connect.
2. after reading tns entry, it will attempt a connection to that host (database server) using tcp/ip
3. this info will be sent to listener which will accept connection and sent to pmon for authentication
rest of the steps are same as in local node.
UPGRADE Vs MIGRATION « Pavan DBA's Blog said
[…] Important Docs […]
iliyas said
sir when is your next batch starting for Oracle DBA
Pavan DBA said
it is starting from 4th march (coming monday)
Venkatesh Appajigol said
Hi Pavan,
could you please send me the Iz0-055 dumps planning for ocp 11g, since i am 9i ocp certified.
Thanks for your help in advance
Thanks,
Venkatesh
Iliyas said
hello sir……i have completed MSC(Comp) in 2010….as i have 2 years of gap, is their any course in oracle where a fresher with no experience can get a job easily, bcoz when we go for oracle DBA everyone asking min 2years of experience
Pavan DBA said
i am nt sure about any such course, bcz most of the courses need some exp
sridevi said
hi Pavan sir i need 1z0 007 9i sql latest dumps please provide me dumps
Ravi said
Hi Sir,
Please let me know that I upgrade our database 10.2.0.2 from 10.2.0.5 and upgraded sucessfully but
DBCA utilities not working or nor run command.
If I go
start -> program ->oracel-oradb10g_home1 -> configuration and migration tools -> dbca
not working.. please suggest me…
Pavan DBA said
check ur environment varilables like oracle_home path and ORACLE_HOME/bin exist in path variable. i feel this is causing issue in your case
Ravi said
Hi Sir,
oracle_home path is correct.
Thanks
Pavan DBA said
for which problem you are talking about?
Naveed Mohammad said
Hi sir Can u tell me the best institutions for Oracle apps DBA In hyderabad. Thanking u sir
Pavan DBA said
you can try kanna technologies or techzone
Naveed Mohammad said
thank you pawan sir
Manju said
Hello Pavan,
I am bit confused about a requirement from users; I have several schema’s in 11gR2 database. The user needs rights only on specified schema’s and not on all schema’s!
I mean for ex: I have Oracle schema A, B, C & D
and users with name 1, 2 & 3
User 1 is required to have all privileges only on the objects of schema A & B
User 2 is required to have all privileges only on the objects of schema C & D
User 3 is required to have all privileges only on the objects of schema A & D
Could you please guide how to solve this.
Thank you.
Pavan DBA said
i feel it is better to create roles. you need 3 roles. 1st role u will to grant with privileges on tables of A&B n then grant that role to user 1. same u can do for other two users.
kanthi said
Hi Pavan,
Gd Morning, I have a confusion in undo tablespace,let me explain in clear…one of the user is inserting bulk data at a time that time undo is using more space and prompt error like unable to extend the undo tablespace to 8mb so i add some more space to the undo later some moment user having same problem for the undo,then i checked in the dba_undo_extents it explains Active blocks occupy 10Gb,expired blocks occupy 4 Gb,and unexpired blocks occuply 22Gb and the undo_retention=900 sec, My question is how to clear unexpired blocks from undo tablespace, is this error is occur because of retention policy? could i need to increase undo_retention if i increased is there any issue will occur with database? previously my undo_retention is 20000 and i changed it to 900 sec..
Sir please help me in this cases.
Pavan DBA said
Hi Kiran,
transactions will make use of space in undo till they get commit. the issue you are facing is, user is doing bulk insert and it is not getting required amount of free space in the undo tablespace. as u said, u have added some space after user got the error. still user facing issue means, the amount you added is not sufficient. still u need to add more as this is bulk insert.
we cannot make unexpired blocks to be cleared. unexpired blocks will be cleared automatically when user issues commit/rollback.
for this there are 2 solutions….
1. add more space to undo
2. ask user to issue commit in between. for eg, if user is inserting 10 lac rows, ask to commit after every one lac. by this unexpired blocks will become expired and oracle will reuse them again. if user don’t give commit, then it will not make undo blocks as expired.
undo_retention doesn’t have any impact on the error you are facing. you said you decreased the value from 20000. when it is at 20000, did u faced this error during this bulk insert?
kiran said
Hi sir thanks a lot,
after user send the error message then i login into database and check for the undo_retention thier it is 20000 thats why i changed to 900 sec but i din’t get better result.
Sir could i keep undo_retention= gurantee or need to keep old value i.e 20000 if i keep these values is there any slow performance in database side?
Pavan DBA said
if u dnt get error when using value of 20000, then u can change to that only. reg database performance, we can’t until we set that value
krish said
thnk u
kishore said
can i install 11g rac on vmware player 4.0.1
Pavan DBA said
yes we can.
sorry culdn’t reply u bcz i am on leave since 3 weeks
krish said
hi pavan,
can you help how to configureRAC in 2 node and single node in laptop using vmware workstation i.e.,step by step .pl send asap
Pavan DBA said
plz refer below link
http://kamranagayev.com/2011/04/05/step-by-step-installing-oracle-10g-rac-on-vmware/
sorry culdn’t reply u bcz i am on leave since 3 weeks
pavankumar said
hi sir,
I serched performance tuing these 3 links
http://pavandba.file.wordpress.com/2009/11/statspack opm4.pdf
http://pavandba.file.wordpress.com/2009/11/statspack tuing otn new1.pdf
http://pavandba.file.wordpress.com/2009/11/opdg slow database.pdf
showing
404 — File not found.
You can create your own free blog on WordPress.com.
plz tell me how to open these links
Thanks&Regaurds
pavan
Pavan DBA said
not sure why it didn’t worked for you. seems some undersocre etc is missing. try these links
Click to access statspack_opm4.pdf
Click to access statspack_tuning_otn_new1.pdf
Click to access opdg_slow_database.pdf
Naveed Mohammad said
Very usefull. Thanks a lot
ashok said
hi sir, this is reg- oracle11g instlln on linux:
in one of the screens where, when i click check & fix again,
it is asking to run a script “/tmp/CVU_11.2.0.1.0_pramod/runfixup.sh” as root user. so when i run it (as root user)
” -bash: /tmp/CVU_11.2.0.1.0_pramod/runfixup. No such file or directory”
& on checking the reqd file is not avbl in the directory called ‘/tmp/CVU_11.2.0.1.0_pramod/”
so how to tackle this issue, for now i chose ignore all & proceeding…
awaiting ur reply on this…
Pavan DBA said
it usually won’t happen. just check if you are using correct name or not. bcz linux is case sensitive
hari said
@ashok:
/tmp/CVU_11.2.0.1.0_pramod/runfixup.sh this is the file you should run. May be you would have run runfixup and not runfixup.sh
sreekanh said
hai..
This is sreekanth ..working as a test eng in a cmmi level 3 company.
looking for securable platform..
Oracle DB2 IS real platform im i looking for?
plz help
Pavan DBA said
sorry your question confused me. Oracle and DB2 are two different streams. Oracle DBA is definitely a good career option to choose for.
takecare said
hii sreekanth, It is upto your interest if you really interest on learn database concepts.,If you have good understanding about db i will recommend you to join db related courses.
If you choose oracle, it is high end database vendor and having same competition in the market. But every db having the its own architecture, if you learn oracle you easily upgrade your skills to db2 as well as teradata which are having similar architecture like oracle. If you choose sql server dba it is the vendor of microsoft it is no comparative to oracle.
vemuri said
Hi sir,
While cloning oracle home i used the following syntax for creating and extracting tar ball to remote machine but i am getting an error and unable to create a tar ball and the error i am getting is cowardly refusing to create empty archive.The command used is
tar -cvf 10ghome | ssh dba3 “cd /data1; tar -xvf -“.Plz help me
Pavan DBA said
are you trying to copy the tar file in the same command? if so you need to use scp
Sivabharathi Devasagayam said
Dear Pavan,
Could you please help me
i try to connect to server database “tnsping dup”
but i got an error
tnd – 12560 protocol adaptor error
but i check server listener on pro. work and also check “tnsping dup” its on work
how to i will salve this problem
pls help
regards
siva
Pavan DBA said
can you please let me know your db and os versions? also OS from which you are doing tnsping
venkat said
Hi pavan,
please provide any ticketing tool doc and what are the tools most of the companies using
Pavan DBA said
hi venkat, i don’t have a doc for that and feel not req also bcz using it doesn’t need any doc.
there are many tools companies will use, but mostly used one is remedy
p roy said
hi pawan
im a big fan u .plz kepp helping like this……..im completed my core dba.. as im fresher in this field plz help how to get a job
Pavan DBA said
thank you very much. to get a fresher job as dba is a bit difficult task until unless you have OCP certification and knowledge on advanced topics like RAC, dataguard etc. so try to learn them if not already done.
renna said
http://rupamverma.blogspot.in/2011/03/how-to-check-whether-physical-standby.html
anup said
i want complete core dba material ……………
Pavan DBA said
hi, you can refer to http://tahiti.oracle.com which is oracle’s official documentation
maaz said
Hi
im getting this error after doing hotbackup
ORA-01157: cannot identify/lock data file 6
ORA-01110: data file 6: ‘/u01/app/oracle/oradata/test01.dbf’
as my test01.dbf file is still in the same location
Pavan DBA said
have you confirmed that the file is in same location using “ls -ltr /u01/app/oracle/oradata/test01.dbf” command? If not please do so. I am sure somewhere, mistake occurred
kiran said
hello sir,
I need a steps for to export a single row from a table and that is located in differnet server (UAT) and the same which i exported that should be imported in differnet server(PROD).But the usernames are lying in both the UAT and PROD
Pavan DBA said
you need to use QUERY option in export which will allow you to take single row export provided the where condition. for syntax, you can refer google
Ahmed said
Hi Pavan,
I see the following lines in the alert log of my standby Database. Just wanted to ask you is this “Normal” or is it some kind of error which needs to be addressed.
Tue May 15 15:45:27 2012
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 8 slaves
ORA-279 signalled during: ALTER DATABASE RECOVER standby database …
ALTER DATABASE RECOVER CANCEL
Media Recovery Canceled
Completed: ALTER DATABASE RECOVER CANCEL
Tue May 15 15:45:32 2012
ALTER DATABASE RECOVER standby database
Media Recovery Start
started logmerger process
Thanks In Advance.
Ahmed
Pavan DBA said
yes this is normal
f insta said
Hi Pavan ,
Just now i installed Oracle10g on Red Hat 4 but the problem is iam facing the error at the time of installing , i ignore that errors but completion is successful.After that i set batch profile ready to connect to sql as a sysdba , but it is not connected just prompt will comw back, What should i do , Please help me.
Pavan DBA said
Hi Mahesh, it would be better if u let me know what error u got during installation. generally we should not supposed to ignore the errors during installation, otherwise it will lead to these kind of probs.
also about connecting, please copy and paste the steps what you have done along with error message (if any)
Mahesh said
Thank you for reply sir , iam issue the command at linux level : sqlplus / as sysdba , it is not connected to sql prompt just redirected to linux level.so what should i do.
Pavan DBA said
I feel there is issue with your installation. I recommend to do fresh oracle installation again….
Ahmed said
Hi Pavan,
we are migrating our database from 10g enterprise edition to 11g standard edition.
i have exported 14 schema`s using export utility from 10g, created a new db in 11g now, what do i need to do in the new database? Do i need to create the 14 users of the same name and create the tablespacesand then import it or can i do it directly without creating it. could you please help me with the Imp command that i can use to import it.
Also will the partioned tables in enterprise edition have any impact on standard edition database?
Looking forward to hear from you
Thanks
Ahmed
Pavan DBA said
Hi Ahmed, steps to follow are
1. take export of 14 schemas from 10g EE database
2. create all the tablespaces with the same name in 11g SE database.not necessary to create any users
3. import the dump file with below command
impdp directory=”directory name” dumpfile=”dump file name” logfile=”log file name” schemas=”provide all 14 schema names separated by ,”
I believe partitioning is not supported in standard edition. as far as i know, it is there only in enterprise edition. so you may face issues during import
krishna kumar said
hello sir,
i want to install oracle 11g R2 on rhel 6.1 32 bit .Can u tell the name of the package required and the installation guide.
reply me asap
Pavan DBA said
the advantage of 11g installation is, you can start the installation and at product pre-requisite page, oracle will list you what are the packages still it need to have in order to have successful installation.
I found this link is nice – http://server.billhamilton.com:84/wp/oracle/installing-oracle-11gr2-on-rhel6-in-4-long-steps/
mohammedosman said
Sir, please upload document from creating dataguard from RAC.
Pavan DBA said
Hi Osman,
I will upload it soon…. as there are some final touches need to be done
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
suman said
In 10g you can configure 9 standby db’s, in 11g it increased up to 30.
Pavan DBA said
yes you are right….
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
renna said
Great ANS.
Thanks,
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 – https://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 https://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
nasir said
Which background process will write alertlog file?
Pavan DBA said
Nasir, every background process will have authority to write into alert log file
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 https://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…
shushmith said
u can find from os level ps -ef| grep cpuinfo
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