DBA Interview Questions with Answers What are four common errors found in an alert .log?
If we are getting any issue regarding database database while performing any activity we should check alert log file in dump destination.. The four common error we find in alert.log are: Deadlock Errors (O!"###$#%& Oracle Internal errors& 'ackup and recovery errors& napshot too old error (O)***% What is PCT FreePCT !sedPCT increase "arameter in segment? What is growth factor? +,T"-EE is a block storage it uses to mention how much space should be left in database block for future updates (updating the records eg. previously name mith after that we will update the name as mith Taylor%. If mention +,T-EE as )#& oracle will adding the new rows to block up to #/ it allows )#/ for future updates. If the +,T used was set to $# this means if the data inside the block is $# it is -011 and if the data inside the block is * it is Empty. This is the parameter which specify in percent that a block can only used for insert or come in the free list(list of blocks in segment ready for insert operation% when used space in a block is less than +,T0ED. uppose value of pctused is 2# and pctfree is 3# then data can be inserted till 4# of the block directly. !nd suppose the used space is $# and some one has perform a delete operation in a row in the same block which brings the used space to *# .5ow one cannot insert any record in the same block unless the used space comes down below 2# i.e. pctused. What is dum" destination? What are #dum"$ cdum" and udum"? The dump destination is the location where the trace files are located for all the Oracle process. bdump""6'ackground bdump""6'ackground processes 7 alert8ID.log file location cdump""6 ,ore +rocesses dump& udump""6 0ser +rocesses dump& adump""6 for !9 processes These destinations destinations contains useful information information related to process failures. !D!%P is specifying the user dump directory where all user error logs (trace files% will be placed. BD!%P is specifying the background dump dump directory where all database error logs (trace files% will be
placed. CD!%P is specifying the core dump directory where all O error logs (core dump files% will be placed.
Default location is (O!,1E8'!Eadmin; (O!,1E8'!Eadmin;ID6% ID6% <16show parameters dump8dest= It>ll show you all the dump directories wherever it is currently located. ?ou can change your parameters in init.ora by creating spfile from pfile. What will &ou do if in an& condition &ou do not 'now how to trou#leshoot the error at all and there are no seniors or &our co(wor'ers around?
@e need to find where in the compilation the error is occurring. @e have to divide the code and check for correctness of the code part"by"part. This is called debugging. Aeep checking the code until you find the code which is wrong. earch forums for similar error codes or symptoms and make a plan then submit it to your supervising D'! if you are not authoriBed to carry it out yourself. I am getting error )*o Communication channel) after changing the domain name? What is the solution?
Cere
+ou +o u have ta'en im"ort of a ta#le in a data#ase. +ou +ou have got got the Integrit& constraint violation error. error. ,ow &ou are going to resolve it.
If u wants to import the table ust says constraintsFn the movement table got imported then u create constraint on that tables. What is the most im"ortant action a DBA must "erform after changing the data#ase from *-AC,I/01-2 T- AC,I/01AC,I/01-2? 2?
-irst of all take an offline backup of whole database (including the (datafile controlfile controlfile and redolog files%. It is obvious that archive log process should be started by: <16alter system !rchivelog start= Otherwise the database halts if unable to rotate redo logs 3how one instance when &ou encountered an error in alert log l og and &ou overcome that error. What actions &ou too' to overcome that error. Oracle writes error in alert log file. Depending upon the error corrective action needs to be taken. )% Deadloc' 0rror4 Take the trace file in user dump destination and analysis it for the error. 3% -A(56777 napshot error: ,heck the uery try to fine tune and check the undo siBe. G% !na#le to e8tent segment4 ,heck the tablespace siBe and if reuire add space in the tablespace by >alter database datafile .... resiBe> or alter tablespace add datafile command. What is -ra(6777 3na"shot too -ld error? 08"lain in detail? Oracle ollback egments (0ndo more recently% hold a copy of data before it was modified and they work in a round"robin fashion. @riting and then eventually overwriting the entries as soon as the changes are committed. They are needed to provide read consistency (a consistent set of data at a point in time% or to allow a process to abandon or rollback the changes or for database recovery. CereHs a typical scenario:" 0ser ! opens a uery to fetch every row from a billion row table. If 0ser ' updates and commits the last row of the billion row table a ollback entry will be created so 0ser ! can see the data as it was before the update. Other users are busily updating rows in the database and this in turn generates rollback which may eventually cause the entry needed for 0ser ! to be overwritten (after all 0ser ' did commit the change so itHs OA to overwrite the rollback segment%. 9aybe )* minutes later the uery is still running and 0ser ! finally fetches the last row of the billion row table but the rollback entry is gone. Ce gets O!"#)***: napshot too old rollback segment too small I have a""lied the following commands4 *ow what will ha""en$ will the data#ase will give an error it will wor'? 3hutdown a#ort9 3tartu"9
Definitely database will be start without error but all uncommitted data will be lost such such as killed all sessions, killed all transactions, and didn't write from the buffers because shutdown abort directly
shutdown instance without committing. There is four modes to shutdown the database: )% hutdown immediate& 3% hutdown normal& G% hutdown transactional& transactional& 2% hutdown aborts @hen the database is shutdown by first G methods checkpoint takes place but when is shutdown by abort option it doesn't enforces checkpoints, it simply shutdowns without waiting any users to disconnect. What is mutated trigger? In single user mode we got mutated error$ as a DBA how &ou will resolve it? 9utated error will occur when same table access more than once in one state. If you are using before in trigger block then replace it with after. 08"lain Dual ta#le. Is an& data internall& internall& stored in dual Ta#le. Ta#le. of users is accessing select s&sdate from dual and the& getting some millisecond differences. If we e8ecute 3010CT 3+3DAT0 3+3DAT0 F-%
+ou +o u have ta'en im"ort of a ta#le in a data#ase. +ou +ou have got got the Integrit& constraint violation error. error. ,ow &ou are going to resolve it.
If u wants to import the table ust says constraintsFn the movement table got imported then u create constraint on that tables. What is the most im"ortant action a DBA must "erform after changing the data#ase from *-AC,I/01-2 T- AC,I/01AC,I/01-2? 2?
-irst of all take an offline backup of whole database (including the (datafile controlfile controlfile and redolog files%. It is obvious that archive log process should be started by: <16alter system !rchivelog start= Otherwise the database halts if unable to rotate redo logs 3how one instance when &ou encountered an error in alert log l og and &ou overcome that error. What actions &ou too' to overcome that error. Oracle writes error in alert log file. Depending upon the error corrective action needs to be taken. )% Deadloc' 0rror4 Take the trace file in user dump destination and analysis it for the error. 3% -A(56777 napshot error: ,heck the uery try to fine tune and check the undo siBe. G% !na#le to e8tent segment4 ,heck the tablespace siBe and if reuire add space in the tablespace by >alter database datafile .... resiBe> or alter tablespace add datafile command. What is -ra(6777 3na"shot too -ld error? 08"lain in detail? Oracle ollback egments (0ndo more recently% hold a copy of data before it was modified and they work in a round"robin fashion. @riting and then eventually overwriting the entries as soon as the changes are committed. They are needed to provide read consistency (a consistent set of data at a point in time% or to allow a process to abandon or rollback the changes or for database recovery. CereHs a typical scenario:" 0ser ! opens a uery to fetch every row from a billion row table. If 0ser ' updates and commits the last row of the billion row table a ollback entry will be created so 0ser ! can see the data as it was before the update. Other users are busily updating rows in the database and this in turn generates rollback which may eventually cause the entry needed for 0ser ! to be overwritten (after all 0ser ' did commit the change so itHs OA to overwrite the rollback segment%. 9aybe )* minutes later the uery is still running and 0ser ! finally fetches the last row of the billion row table but the rollback entry is gone. Ce gets O!"#)***: napshot too old rollback segment too small I have a""lied the following commands4 *ow what will ha""en$ will the data#ase will give an error it will wor'? 3hutdown a#ort9 3tartu"9
Definitely database will be start without error but all uncommitted data will be lost such such as killed all sessions, killed all transactions, and didn't write from the buffers because shutdown abort directly
shutdown instance without committing. There is four modes to shutdown the database: )% hutdown immediate& 3% hutdown normal& G% hutdown transactional& transactional& 2% hutdown aborts @hen the database is shutdown by first G methods checkpoint takes place but when is shutdown by abort option it doesn't enforces checkpoints, it simply shutdowns without waiting any users to disconnect. What is mutated trigger? In single user mode we got mutated error$ as a DBA how &ou will resolve it? 9utated error will occur when same table access more than once in one state. If you are using before in trigger block then replace it with after. 08"lain Dual ta#le. Is an& data internall& internall& stored in dual Ta#le. Ta#le. of users is accessing select s&sdate from dual and the& getting some millisecond differences. If we e8ecute 3010CT 3+3DAT0 3+3DAT0 F-%
0%P9 what error will we get. Wh&?
Dual is a system owned table created during database creation. Dual table consist of a single column and a single row with value J. @e will not get any error if we eJecute select sysdate from scott.emp instead sysdate will be treated as a pseudo column and displays displays the value for all the rows retrieved. -or EJample if there is )3 rows in emp table it will give result of date in )3 rows. As an -racle DBA what are the entire !*I: file &ou should #e familiar with? To check the process use: "s (ef ;gre" "mon or "s (ef Tail (f alert.log To check the alert log file: Tail To check the cpu usage= To" vmstat < 7 What is a Data#ase instance?
! database database instance instance also known known as server is a set of memory structures structures and background background processes processes that access a set of database files. It is possible for a single database to be accessed by multiple instances (this is oracle parallel server option%. What are the e=uirements of sim"le Data#ase? A simple simple database consists of:
One or more data files& One or more control files& Two or more redo log files& 9ultiple usersschemas& One or more rollback segments& One or more Tablespaces& Data dictionary tables& 0ser obects (table& indeJes& views etc.% The server (Instance) that access the database consists of:
K! (Database buffer& Dictionary Dictionar y ,ache 'uffers& edo log buffers& hared <1 pool%& 9O5 (ystem 9onitor%&+9O5 9onitor%&+9O5 (+rocess 9onitor%& 1K@ 1K@ (1og @rite%& D'@ (Data 'ase @rite%& @rite%& !,C (!,Civer%& (!,Civer%& ,A+T (,heck +oint%& E,O& Dispatcher& Dispatcher& 0ser +rocess with associated +K Which "rocess writes data from data files to data#ase #uffer cache?
The 'ackground process D'@ rights data from datafile to D' cache. ,ow to D-P an -racle Data#ase? ?ou can do it at the O level by deleting all the files of the database. The files to be deleted can be found using: )% select L from dba8data8files= dba8data8files= 3% select L from vMlogfile= G% select L from vMcontrolfile= 2% archive log list *% initID.ora $% clean the 0D09+& 'D09+& scripts etc& N% ,leanup the listener.ora and the tnsnames.ora. 9ake sure that the oratab entry is also removed. Otherwise& go to D',! and click on delete database. In Oracle )#g there is a new command to drop an entire database. tartup restrict mount= drop database ;instance8name6= In fact D'! should never drop a database via O level commands rather use K0I utility D',! to drop the database ,ow can #e determining the si>e of the log files.
elect sum(bytes%)#32)#32 siBe8in89' from vMlog= What is difference #etween 1ogical 3tand#& Data#ase and Ph&sical 3tand#& data#ase? ! physical physical or logical logical standby database database is a database database replica created from a backup of a primary database. database. ! physical standby database database is physically identical to the primary database database on a block"for"block basis. It>s maintained in managed recovery mode to remain current and can be set to read only= archive logs are copied and applied. ! logical logical standby database database is logically logically identical to the primary database. database. It is updated using <1 statements ,ow do &ou find whether the instance was started with "file or s"file )% E1E,T name& value -O9 vMparameter @CEE name F >spfile>= This query will return NULL if you are usin !"IL#
3% CO@ +!!9ETE spfile
This query will returns NULL in the value column if you are usin pfile and not spfile
G% E1E,T ,O05T(L% -O9 vMspparameter @CEE value I 5OT 5011= If the count is count is non$%ero then the instance is usin a spfile& and if the count is %ero then it is usin a pfile:
<16 E1E,T DE,ODE(value& 5011& >+-I1E>& >+-I1E>% Init -ile Type -O9 sys.v8Mparameter @CEE name F >spfile>= What is full #ac'u"?
! full backup is an an operating system backup backup of all data files& on"line on"line redo log log files and control file that constitute oracle database and the parameter. If you are using the man for backup then in man full backup means Incremental backup on # level. While ta'ing hot #ac'u" #egin end #ac'u"@ what will ha""ens #ac' end?
@hen we r taking hot backup (begin backup " end backup% the datafile header associated with the datafiles in the corresponding Tablespace is froBen. o Oracle will stop updating the datafile header but will continue to write data into datafiles. In hot backup oracle will generate more redos this is because oracle will write out complete changed blocks to the redo log files. Which is the #est o"tion used to move data#ase from one server to another serve on same networ' and Wh&? Im"ort 08"ort$ Bac'u"(estore$ Detach(Attach
Import"EJport is the best option used to move database from one server to another serve on same network. It reduces the network traffic.ImportEJport works well if youHre dealing with very small databases. If we have few million rows its takes minutes to copy when compared to seconds using backup and restore. What is Different T&"e of %A* Bac'u"? Full #ac'u": During a -ull backup (1evel #% all of the block ever used in datafile are backed up. The only difference between a level # incremental backup and a full backup is that a full backup is never included in an incremental strategy. Comulative Bac'u": During a cumulative (1evel #% the entire block used since last full backup are backed up. 9!56 '!,A0+ I5,E9E5T!1 1EPE1 ) ,0901!TIPE D!T!'!E= Q blocks changed since level # Differential Bac'u": During incremental backup only those blocks that have changed since last cumulative (1evel )% or full backup (1evel #% are backed up. Incremental backup are differential by default. 9!56 '!,A0+ '!, A0+ I5,E9E5T!1 I5, E9E5T!1 1EPE1 ) D!T!'!E D!T!'!E 2ive one method for transferring a ta#le from one schema to another4 There are several possible methods: EJport"Import& ,E!TE T!'1E... ! E1E,T or ,O+?. What is the "ur"ose of the I%P-T o"tion I2*-0? What is its default setting?
The I%P-T I2*-0 option tells import to ignore already eJists errors. If it is not specified the tables that already eJist will be skipped. If it is specified& the error is ignored and the tableHs data will be inserted. The default value is 5. What ha""ens when the D0FA D0FA!1T !1T and T0%P ta#les"ace clauses are left out from C0AT C0AT0 0 !30 statements?
The user is assigned the ?TE9 tablespace as a default and temporary tablespace. This is bad because it causes user obects and temporary segments to be placed into the ?TE9 tablespace resulting in fragmentation and improper table placement (only data dictionary obects and the system rollback segment should be in ?TE9%. What ha""ens if the constraint name is left out of a constraint clause?
The Oracle system will use the default name of ?8,JJJJ where JJJJ is a system generated number. This is bad since it makes tracking which table the constraint belongs to or what the constraint does harder. What ha""ens if a Ta#les"ace clause is left off of a "rimar& 'e& 'e & constraint clause?
This result in the indeJ that is automatically generated being placed in then 0E default tablespace. ince this will usually be the same tablespace as the table is being created in& this can cause serious
performance problems. What ha""ens if a "rimar& 'e& constraint is disa#led and then ena#led without full& s"ecif&ing the inde8 clause?
The indeJ is created in the userHs default tablespace and all siBing information is lost. Oracle doesnHt store this information as a part of the constraint definition& but only as part of the indeJ definition& when the constraint was disabled the indeJ was dropped and the information is gone. !sing hot #ac'u" without #eing in archive log mode$ can &ou recover in the event of a failure? Wh& or wh& not?
?ou can>t recover the data because in archive log mode it take the backup of redo log files if it in !ctive mode& If it in inactive mode then it is not possible to take the backup of redolog files once the siBe is full& so in that case it is impossible to take hot backup What causes the )sna"shot too old) error? ,ow can this #e "revented or mitigated? This is caused by large or long running transactions that have either wrapped onto their own rollback space or have had another transaction write on part of their rollback space. This can be prevented or mitigated by breaking the transaction into a set of smaller transactions or increasing the siBe of the rollback segments and their eJtents. ,ow can &ou tell if a data#ase o#ect is invalid?
select T!T0 from user8obects where obect8typeF>T!'1E> !5D O'RE,T85!9EF>1OK95T8T!'+!TM>=
A user is getting an -A(55< error &et &ou 'now &ou have granted them "ermission on the ta#le$ what else should &ou chec'?
?ou need to check that the user has specified the full name of the obect (E1E,T empid -O9 scott.emp= instead of E1E,T empid -O9 emp=% or has a synonym that points to that obect (,E!TE ?5O5?9 emp -O scott.emp=% A develo"er is tr&ing to create a view and the data#ase wonEt w onEt let him. ,e has the )D0/01-P0) role which has the )C0AT0 /I0W) s&stem "rivilege and 3010CT grants on the ta#les he is using$ what is the "ro#lem?
?ou need to verify the developer has direct grants on all tables used in the view. ?ou can>t create a stored obect with grants given through a role. If &ou have an e8am"le ta#le$ what is the #est w a& to get si>ing data for the "roduction ta#le im"lementation?
The best way is to analyBe the table and then use the data provided in the D'!8T!'1E view to get the average row length and other pertinent data for the calculation. The uick and dirty way is to look at the number of blocks the table is actually using and ratio the number of rows in the table to its number of blocks against the number of eJpected rows. ,ow can &ou find out how man& users are currentl& logged into the data#ase? ,ow can &ou find their o"erating s&stem id?
To look at the vMsession or vMprocess views and check the current8logins parameter in the vMsysstat view. If you are on 05IS is to do a ps "efgrep oraclewc "lU ,ommand& ,ommand& but this only works against a single instance installation. ,ow can &ou determine if an inde8 needs to #e dro""ed and re#uilt? un the !5!1?VE I5DES command on the indeJ to validate its structure and then calculate the ratio of 1-8'1A81E51-8'1A81E57'8'1A81E5 and if it isnHt near ).# (i.e. greater than #.N or so% then the indeJ should be rebuilt or if the ratio '8'1A81E5 1-8'1A81E57'8'1A81E5 is nearing #.G. It is not so easy to decide so I personally suggest contact to the eJpert before going to rebuild. What is t'"rof and how is it used?
The tkprof tool is a tuning tool used to determine ,+0 and eJecution times for <1 statements. ?ou use it by first setting timed8statistics to true in the initialiBation file and then turning on tracing for either the entire database via the sl8trace parameter or for the session using the !1TE EIO5 command. Once the trace file is generated generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool. This can also be used to generate eJplain plan output. What is 08"lain "lan and how is it used?
The ES+1!I5 +1!5 command is a tool to tune <1 statements. To use it you must have an eJplain8table generated in the user you are running the eJplain plan for. This is created using the utlJplan.sl script. Once the eJplain plan table eJists you run the eJplain plan command giving as its argument the <1 statement to be eJplained. The eJplain plan table is then ueried to see the eJecution plan of the statement. EJplain plans can also be run using tkprof. ,ow do &ou "revent out"ut from coming to the sc reen? The ET option TE9O0T controls output to the screen. etting TE9O0T O-- turns off screen output. This option can be shortened to TE9. ,ow do &ou "revent -racle from giving &ou informational messages during and after a 3Q1 statement e8ecution?
The ET options -EED'!,A and PEI-? can be set to O--. ,ow do &ou generate file out"ut from 3Q1? 'y use of the +OO1 command A ta#les"ace has a ta#le with 5 e8tents in it. Is t his #ad? Wh& or wh& not. 9ultiple eJtents in and of themselves arenHt bad. Cowever if you also have chained rows this can hurt performance.
,ow do &ou set u" ta#les"aces during an -racle installation?
?ou should always attempt to use the Oracle -leJible !rchitecture standard or another partitioning scheme to ensure proper separation of ?TE9& O11'!,A& EDO 1OK& D!T!& TE9+O!? and I5DES segments. +ou see multi"le fragments in the 3+3T0% ta#les"ace$ what should &ou chec' first? Ensure that users donHt have the ?TE9 tablespace as their TE9+O!? or DE-!01T tablespace assignment by checking the D'!80E view. What are some indications that &ou need to increase the 3,A0DGP--1G3IH0 "arameter? +oor data dictionary or library cache hit ratios& getting error O!"#2#G). !nother indication is steadily decreasing performance with all other tuning parameters the same. 2uideline for si>ing d#G#loc'Gsi>e and d#GmultiG#loc'Gread for an a""lication that does man& full ta#le scans? Oracle almost always reads in $2k chunks. The two should have a product eual to $2 or a multiple of $2. When loo'ing at vs&sstat &ou see that sorts dis'@ is high. Is this #ad or good? If #ad (,ow do &ou correct it?
If you get eJcessive disk sorts this is bad. This indicates you need to tune the sort area parameters in the initialiBation files. The maor sort parameter is the OT8!E!8IVE parameter. When should &ou increase co"& latches? What "arameters control co"& latches? @hen you get eJcessive contention for the copy latches as shown by the redo copy latch hit ratio. ?ou can increase copy latches via the initialiBation parameter 1OK8I901T!5EO08,O+IE to twice the number of ,+0s on your system. Where can &ou get a list of all initiali>ation "arameters for &our instance? ,ow a#out an indication if the& are default settings or have #een changed? ?ou can look in the init.ora file for an indication of manually set parameters. -or all parameters& their value and whether or not the current value is the default value& look in the vMparameter view. Descri#e hit ratio as it "ertains to the data#ase #uffers. What is the difference #etween instantaneous and cumulative hit ratio and which should #e used for tuning?
The hit ratio is a measure of how many times the database was able to read a value from the buffers verses how many times it had to re"read a data value from the disks. ! value greater than 4#"#/ is good& less could indicate problems. If you simply take the ratio of eJisting parameters this will be a cumulative value since the database started. If you do a comparison between pairs of readings based on some arbitrary time span& this is the instantaneous ratio for that time span. Kenerally speaking an instantaneous reading gives more valuable data since it will tell you what your instance is doing for the time it was generated over. Discuss row chaining$ how does it ha""en? ,ow can &ou reduce it? ,ow do &ou correct it? ow chaining occurs when a P!,C!3 value is updated and the length of the new value is longer than the old value and would not fit in the remaining block space. This results in the row chaining to another block. It can be reduced by setting the storage parameters on the table to appropriate values. It can be corrected by eJport and import of the effected table. +ou are getting #us& #uffer waits. Is this #ad? ,ow can &ou find what is causing it? 'uffer busy waits could indicate contention in redo& rollback or data blocks. ?ou need to check the vMwaitstat view to see what areas are causing the problem. The value of the count column tells where the problem is& the class column tells you with what. 05DO is rollback segments& D!T! is data base buffers. If &ou see contention for li#rar& caches how &ou can fi8 it?
Increase the siBe of the shared pool. If &ou see statistics that deal with )undo) what are the& reall& tal'ing a#out? ollback segments and associated structures. If a ta#les"ace has a default "ctincrease of >ero what will this cause in relationshi" to the 3%-* "rocess@?
The 9O5 process would not automatically coalesce its free space fragments. If a ta#les"ace shows e8cessive fragmentation what are some methods to defragment the ta#les"ace? J.6$J.< and J. onl&@
In Oracle N.# to N.3 The use of the 'alter session set events 'immediate trace name coalesce level ts#'; command is the easiest way to defragment contiguous free space fragmentation. The tsQ parameter
corresponds to the tsQ value found in the tsM ? table. In version N.G the alter tablespace coalesce= is best. If the free space is not contiguous then eJport& drop and import of the tablespace contents may be the only way to reclaim non"contiguous free space. ,ow can &ou tell if a ta#les"ace has e8cessive fragmentation? If a select against the dba8free8space table shows that the count of tablespaces eJtents is greater than the count of its data files& then it is fragmented. +ou see the following on a status re"ort4 redo log s"ace re=uests < redo log s"ace wait time 5 Is this something to worr& a#out? What if redo log s"ace wait time is high? ,ow can &ou fi8 this?
ince the wait time is Bero& no problem. If the wait time was high it might indicate a need for more or larger redo logs. If &ou see a "in hit ratio of less than 5.K in the estat li#rar& cache re"ort is this a "ro#lem? If so$ how do &ou fi8 it? This indicates that the shared pool may be too small. Increase the shared pool siBe. If &ou see the value for reloads is high in the estat li#rar& cache re"ort is this a matter for concern? ?es& you should strive for Bero reloads if possible. If you see eJcessive reloads then increase the siBe of the shared pool. +ou loo' at the d#aGroll#ac'Gsegs view and see that there is a large num#er of shrin's and the& are of relativel& small si>e$ is this a "ro#lem? ,ow can it #e fi8ed if it is a "ro#lem? ! large number of small shrinks indicates a need to increase the siBe of the rollback segment eJtents. Ideally you should have no shrinks or a small number of large shrinks. To fiJ this ust increase the siBe of the eJtents and adust optimal accordingly. +ou loo' at the d#aGroll#ac'Gsegs view and see that &ou have a large num#er of wra"s is this a "ro#lem?
! large number of wraps indicates that your eJtent siBe for your rollback segments are probably too small. Increase the siBe of your eJtents to reduce the number of wraps. ?ou can look at the average transaction siBe in the same view to get the information on transaction siBe. +ou see multi"le e8tents in the Tem"orar& Ta#les"ace. Is this a "ro#lem? !s long as they are all the same siBe this is not a problem. In fact& it can even improve performance since Oracle would not have to create a new eJtent when a user needs one. ,ow do &ou set u" &our Ta#les"ace on installation 1evel4 1ow? The answer here should show an understanding of separation of redo and rollback& data and indeJes and isolation of ?TE9 tables from other tables. !n eJample would be to specify that at least N disks should be used for an Oracle installation. Dis' Configuration4 ?TE9 tablespace on )& edo logs on 3 (mirrored redo logs%& TE9+O!? tablespace on G& O11'!,A tablespace on 2& D!T! and I5DESE *&$ They should indicate how they will handle archive logs and eJports as well as long as they have a logical plan for combining or further separation more or less disks can be specified. +ou have installed -racle and &ou are now setting u" the actual instance. +ou have #een waiting an hour for the initiali>ation scri"t to finish$ what should &ou chec' first to determine if there is a "ro#lem?
,heck to make sure that the archiver is not stuck. If archive logging is turned on during install a large number of logs will be created. This can fill up your archive log destination causing Oracle to stop to wait for more
space. When configuring 3Q1*0T on the server what files must #e set u"? I5ITI!1IV!TIO5 file& T55!9E.O! file& <15ET.O! file When configuring 3Q1*0T on the client what files need to #e set u"?
<15ET.O!& T55!9E.O! +ou have ust started a new instance with a large 32A on a #us& e8isting server. Performance is terri#le$ what should &ou chec' for?
The first thing to check with a large K! is that it is not being swapped out. What -3 user should #e used for the first "art of an -racle installation on !*I:@? ?ou must use root first. When should the default values for -racle initiali>ation "arameters #e used as is? 5ever ,ow man& control files should &ou have? Where should the& #e located?
!t least 3 on separate disk spindles (9irrored by Oracle%. ,ow man& redo logs should &ou have and how should the& #e configured for ma8imum recovera#ilit&?
?ou should have at least G groups of two redo logs with the two logs each on a separate disk spindle (mirrored by Oracle%. The redo logs should not be on raw devices on 05IS if it can be avoided. Wh& are recursive relationshi"s #ad? ,ow do &ou resolve them? ! recursive relationship defines when or where a table relates to itself. It is considered as bad when it is a hard relationship (i.e. neither side is a may both are must% as this can result in it not being possible to put in a top or perhaps a bottom of the table. -or eJample in the E9+1O?EE table you could not put in the +EIDE5T of the company because he has no boss& or the unior anitor because he has no subordinates. These type of relationships are usually resolved by adding a small intersection entity. What does a hard one(to(one relationshi" mean one where the relationshi" on #oth ends is )must)@? This means the two entities should probably be made into one entity. ,ow should a man&(to(man& relationshi" #e handled?
'y adding an intersection entity table What is an artificial derived@ "rimar& 'e&? When should an artificial or derived@ "rimar& 'e& #e used?
! derived key comes from a seuence. 0sually it is used when a concatenated key becomes too cumbersome to use as a foreign key. When should &ou consider de(normali>ation?
@henever performance analysis indicates it would be beneficial to do so without compromising data integrity. (!*I:( ,ow can &ou determine the s"ace left in a file s&stem? There are several commands to do this: du& df& or bdf ,ow can &ou determine the num#er of 3Q1*0T users logged in to the !*I: s&stem? <15ET users will show up with a process uniue name that begins with oracle& if you do a ps "efgrep oraclewc "l you can get a count of the number of users. What command is used to t&"e files to the screen? cat& more& pg Can &ou remove an o"en file under !*I:?
?es What is the "ur"ose of the gre" command? grep is a string search command that parses the specified string from the specified file or f iles The s&stem has a "rogram that alwa&s includes the word nocom" in its name$ how can &ou determine the num#er of "rocesses that are using this "rogram?
ps "efgrep LnocompLwc "l The s&stem administrator tells &ou that the s&stem has not #een re#ooted in L months$ should he #e "roud of this?
9ost 05IS systems should have a scheduled periodic reboot so file systems can be checked and cleaned and dead or Bombie processes cleared out. 9ay be& ome 05IS systems do not clean up well after themselves. Inode problems and dead user processes can accumulate causing possible performance and corruption problems. ,ow can &ou find dead "rocesses? ps "efgrep Bombie "" or "" who "d depending on the system. ,ow can &ou find all the "rocesses on &our s&stem?
0se the ps command ,ow can &ou find &our id on a s&stem? 0se the who am i command. What is the finger command? The finger command uses data in the passwd file to give information on system users. What is the easiest method to create a file on !*I:? 0se the touch command What does MM do? The 66 redirection symbol appends the output from the command specified into the file specified. The file must already have been created. If &ou are not sure what command does a "articular !*I: function what is the #est wa& to determine the command?
The 05IS man (' command will search the man pages for the value specified. eview the results from the command to find the command of interest. ,ow can &ou determine if an -racle instance is u" from the o"erating s&stem level? There are several base Oracle processes that will be running on multi"user operating systems& these will be smon& pmon& dbwr and lgwr. !ny answer that has them using their operating system process showing feature to check for these is acceptable. -or eJample& on 05IS "s (ef;gre" "mon will show what instances are up. !sers from the PC clients are getting messages indicating 4 -A(5L664 *0TTCP4 3ID loo'u" failure. What could the "ro#lem #e?
The instance name is probably incorrect in their connection string. !sers from the PC clients are getting the following error stac'4 0-4 -A(5654 -AC10 not availa#le - A(5J6K4 smsget4 o"en error when o"ening sgadef.d#f file. ,P(!: 0rror4 <4 *o such file or director& What is the "ro#a#le cause?
The Oracle instance is shutdown that they are trying to access& restart the instance. ,ow can &ou determine if the 3Q1*0T "rocess is running for 3Q1*0T /6? ,ow a#out /
The alert.ora log. It is located in the directory specified by the background8dump8dest parameter in the vMparameter table. !sers are not #eing allowed on the s&stem. The following message is received4 -A(55<7J archiver is stuc'. Connect internal onl&$ until freed. What is the "ro#lem?
The archive destination is probably full& backup the archivelogs and remove them and the archiver will re" start. Where would &ou loo' to find out if a redo log was corru"ted assuming &ou are using -racle mirrored redo logs?
There is no message that comes to the <1D'! or P9K programs during startup in this situation& you must check the alert. log file for this information. +ou attem"t to add a datafile and get4 -A(5666K4 cannot add an&more datafiles4 limit of 5 e8ceeded. What is the "ro#lem and how can &ou fi8 it?
@hen the database was created the db8files parameter in the initialiBation file was set to 2#. ?ou can shutdown and reset this to a higher value& up to the value of 9!S8D!T!-I1E as specified at database creation. If the 9!S8D!T!-I1E is set to low& you will have to rebuild the control file to increase it before proceeding. +ou loo' at &our fragmentation re"ort and see that smon has not coalesced an& of &ou ta#les"aces$ even though &ou 'now several have large chun's of contiguous free e8tents. What is the "ro#lem?
,heck the dba8tablespaces view for the value of pct8increase for the tablespaces. If pct8increase is Bero& smon will not coalesce their free space. +our users get the following error4 -A(55577 ma8imum num#er of D%1 loc's e8ceeded? What is the "ro#lem and how do &ou fi8 it?
The number of D91 1ocks is set by the initialiBation parameter D9181O,A. If this value is set to low (which it is by default% you will get this error. Increase the value of D9181O,A. If you are sure that this is ust a temporary problem& you can have them wait and then try again later and the error should clear. +ou get a call from &ou #ac'u" DBA while &ou are on vacation. ,e has corru"ted all of the control files while "la&ing with the A1T0 DATABA30 BACN!P C-*T-1FI10 command. What do &ou do?
!s long as all datafiles are safe and he was successful with the '!,A0+ controlfile command you can do the following: ,O55E,T I5TE5!1 T!T0+ 9O05T (Take any read"only tablespaces offline before neJt step !1TE D!T!'!E D!T!-I1E .... O--1I5E= E,OPE D!T!'!E 0I5K '!,A0+ ,O5TO1-I1E !1TE D!T!'!E O+E5 EET1OK= (bring read"only tablespaces back online% hutdown and backup the system& then restart If they have a recent output file from the !1TE D!T!'!E '!,A0+ ,O5TO1 -I1E TO T!,E= command& they can use that to recover as well. If no backup of the control file is available then the following will be reuired: ,O55E,T I5TE5!1 T!T0+ 5O9O05T ,E!TE ,O5TO1 -I1E .....= Cowever& they will need to know all of the datafiles& logfiles& and settings for 9!S1OK-I1E& 9!S1OK9E9'E& 9!S1OKCITO?& 9!SD!T!-I1E for the database to use the command. +ou have ta'en a manual #ac'u" of a datafile using -3. ,ow %A* will 'now a#out it?
@henever we take any backup through 9!5 in the repository information of the backup is recorded. The 9!5 repository can be either controlfile or recovery catalog. Cowever if you take a backup through O command then 9!5 does not aware of that and hence recorded are not reflected in the repository. This is also true whenever we create a new controlfile or a backup taken by 9!5 is transferred to another place using O command then controlfilerecovery catalog does not know about the prior backups of the database. o in order to restore database with a new created controlfile we need to inform 9!5 about the backups taken before so that it can pick one to restore. This tas' can be done by catalo command in AN*
§ § §
!dd information of backup pieces and image copies in the repository that are on disk. ecord a datafile copy as a level # incremental backup in the 9!5 repository. ecord of a datafile copy that was taken by O.
'ut ,!T!1OK command has some restrictions. It can>t do the following. § ,an>t catalog a file that belong to different database. ,an>t catalog a backup piece that eJists on an sbt device. § Example: Catalog rchive log
9!56,!T!1OK !,CIPE1OK >oracleoradataaruarc##)833G.arc> >oracleoradataaruarc##)8332.arc>= Catalog !atafile
To catalog datafile copy >oradatabackupusers#).dbf> as an incremental level # backup your command will be 9!56,!T!1OK D!T!-I1E ,O+? >oradatabackupusers#).dbf> 1EPE1 #= Note that this datafile copy was ta'en bac'up either usin the AN +A,-U! A. ,/!0 command or by usin operatin system utilities in con1unction with ALT# TA+L#.!A,# +#2IN3#N4 +A,-U!*
Catalog multi"le co"ies in a director&4
9!56,!T!1OK T!T @ITC >tmpbackups> 5O+O9+T= Catalog files in the flash recover" area:
To catalog all files in the currently enabled flash recovery area without prompting the user for each one issue 9!56,!T!1OK E,OPE? !E! 5O+O9+T= ,atalog backup pieces: 9!56,!T!1OK '!,A0++IE,E >oradata3o2ccf2>= ,ow to !ncatalog Bac'u"? In many cases you need to uncatalog command. uppose you do not want a specific backup or copy to be eligible to be restored but also do not want to delete it. To uncatalo all archived los issue:
9!56,C!5KE !,CIPE1OK !11 05,!T!1OK= To uncatao tablespace U.#. issue:
9!56,C!5KE '!,A0+ O- T!'1E+!,E 0E 05,!T!1OK= To uncatalo a bac'uppiece name 3oradata53oft6qq issue:
9!56,C!5KE '!,A0++IE,E >oradata3oftN> 05,!T!1OK= ,ow would &ou find total si>e of data#ase in -3 level The siBe of the database is the total siBe of the datafiles that make up the tablespaces of the database. These details are found in the dba8eJtents view. select sum(bytes)/(1024*1024) from V$datafile; select sum(bytes)/(1024*1024) from dba_data_files; select sum(bytes)/(1024*1024) from dba_extents; Can we take incremental Backup with out
taking complete Backup? No !irst full bac"u# is needed
,ow to use )A1T0 DATABA30 B02I* BACN!P9) command in -racle i.
<16alter tablespace ;tablespace8name6 begin backup= copy all the datafile redolog file using command prompt uerying vMdatafile& vMcontrolfile to check the file status and path after backing up end the command. <16alter tablespace ;tablespace8name6 end backup= repeat this for all tablespaces ,ow will &ou rectif& if one of the roll#ac' segments gets corru"ted
The only option available is to restore and recover the database followed by opening the database with resetlogs. In this case you will lose the entire prior database backup so must make fresh backup. ,ow man& da&s$ we are going to retain the data after ta'ing the #ac'u". For e8am"le the data which #ac'ed u" toda& that will get e8"ire in 5 da&s. That means$ it is 5 da&s retention "olic& for #ac'u"
?ou can configure retention policy command to create a persistent and automatic backup retention policy. @hen a backup retention policy is in effect 9!5 considers backups of datafiles and control files as obsolete that is no longer needed for recovery according to criteria that you specify in the ,O5-IK0E command. ?ou can then use the E+OT O'O1ETE command to view obsolete files and DE1ETE O'O1ETE to delete them. That means it is # days retention policy for backup Difference etention Polic& of 0D!*DA*C+0C-/0+ WI*D-W Parameters?
ETE5TIO5 +O1I,?: (ED05D!5,?E,OPE? @I5DO@% ED05D!5,? defines a fiJed num#er of #ac'u" to #e retained . !ny backup in eJcess of this number can be deleted. The default value ) says as soon as a new backup is created the old one is no longer needed and can be deleted. The other option of retention policy is E,OPE? @I5DO@ specified in days& to define period of time in which point in time recovery must be possible. Thus it defines how long #ac'u" should retain . What 'ind of #ac'u" &ou ta'e Ph&sical 1ogical? Which one is #etter and Wh&?
1ogical backup means backing up the individual database obects such as tables& views & indeJes using the utility called ES+OT& provided by Oracle. The obects eJported in this way can be imported into either same database or into any other database. The backed"up copy of information is stored in a dumpfile& and this file can be read only using another utility called I9+OT. There is no other way you can use this file. In this backup Oracle EJport utility stores data in 'inary f ile at O level. +hysical backups rely on the Operating ystem to make a copy of the physical files like data files& log files& control files that comprise the database. In this backup physically ,D (datafile& controlfile& redolog file% files are copied from one location to another (disk or tape% @e don>t preferred logical backup. It is very slow and recoveries are almost not possible. What is Partial Bac'u"?
! +artial 'ackup is any operating system backup short of a full backup& taken while the database is open or shut down. ! partial backup is an operating system backup of part of a database. The backup of an individual table spaceHs data files or the backup of a control file are eJamples of partial backups. +artial backups are useful only when the database is in !,CIPE1OK ... What are the name of the availa#le /I0W in oracle used for monitoring data#ase is in #ac'u" mode #egin #ac'u"@. /#ac'u" 4 tatus column of this view shows whether a tablespace is in hotbackup mode. The status
>!,TIPE> shows the datafile to be in backup mode. /datafileGheader 4 The fuBBy column also helps a dba to monitor datafile which are in backup mode. The fuBBy W5OH indicates that the datafile is in hotbackup begin backup% mode. $%E : The database doesn7t startup when a datafile is in bac'up mode* .o put datafile bac' in the normal mode before shuttin down the database*
What is Tail log #ac'u"? Where can we use it?
Tail 1og 'ackup is the log backup taken after data corruption (Disaster%. Even though there is file corruption we can try to take log backup (Tail 1og 'ackup%. This will be used during point in time recovery. ,onsider a scenario where in we have full backup of )3:## noon one Transactional log backup at ):## +9. The log backup is scheduled to run for every ) hr. If disaster happens at ):G# +9 then we can try to take tail log backup at ):G# (after disaster%. If we can take tail log backup then in recovery first restore full backup of )3:## noon then ):## +9 log backup recovery and then last tail backup of ):G# (!fter Disaster%. ,ow to chec' the si>e of 32A?
<16 show K! Total ystem Klobal !rea )$NNN3)$# bytes -iJed iBe )32N## bytes Pariable iBe *4N3)$G$ bytes Database 'uffers )#24*N$## bytes edo 'uffers 32*#32 bytes ,ow to define data #loc' si>e
The primary block siBe is defined by the InitialiBation parameter D'8'1O,A8IVE. ,ow can we determine the si>e of the log files.
<16elect sum(bytes%()#32L)#32% XsiBe8in8mbY from vMlog= What do &ou do when the server cannot start due to a corru"t master data#ase?
If the master database is corrupt then surely others also do have the problems and thus the need of 9Drecovery comes to an immediate. Cowever you can try out to rebuild it with re#uild.e8e and restore it. What do &ou do when tem" d# is full?
?ou need to clean up the space and add more space in order to prevent this error in future. <16!lter database tempfile Wtemp#).dbfH resiBe 3##9= 0se /T0%PG3PAC0G,0AD0 to check the free space in Tempfile or use the uery E1E,T !.tablespace8name tablespace& D.mb8total& 09 (!.used8blocks L D.block8siBe% )#32 )#32 mb8used& D.mb8total " 09 (!.used8blocks L D.block8siBe% )#32 )#32 mb8free -O9 vMsort8segment !& ( E1E,T '.name& ,.block8siBe& 09 (,.bytes% )#32 )#32 mb8total -O9 vMtablespace '& vMtempfile , @CEE
'.tsQF ,.tsQ
KO0+ '? '.name& ,.block8siBe %D @CEE
!.tablespace8name F D.name
KO0+ by !.tablespace8name& D.mb8total=
The above uery will displays for each sort segment in the database the tablespace the segment resides in& the siBe of the tablespace& the amount of space within the sort segment that is currently in use& and the amount of space available. What is the fre=uenc& of log !"dated..?
@henever commit& checkpoint or redolog buffer is )Grd full& Time out occurs (G sec.%& ) 9' of redo log buffer What are the Possi#ilities of 1ogical Bac'u" 08"ortIm"ort@
" @e can eJport from one user and import into another within the same database. " @e can eJport from one database and import into another database (but both source and destination databases should be are O!,1E databases% " @hen migrating from one platform to another like f rom windows to sun olaris then eJport is the only method to transfer the data. What is stored in -rata# file
orata# is a file created by Oracle in the etc or varoptoracle directory when installing database software. Originally O!T!' was used for <1L5et P)& but lately it is being used to list the databases and software versions installed on a server. data#aseGsid4oracleGhomeGdir4+;*
The ?5 flags indicate if the instance should automatically start at boot time (?Fyes& 5Fno%. +esides actin as a reistry for what databases and software versions are installed on the server& /ATA+ is also used for the followin purposes: Oracle>s d#start and d#shut scripts use this file to figure out which instances are to be start up or •
shut down (using the third field& ? or 5%. The oraenv utility uses O!T!' to set the correct environment variables. • •
One can also write 0niJ shell scripts that cycle through multiple instances using the information in the
oratab file. In &our data#ase some #loc's of "articular datafile are corru"ted. What statement will &ou issue to 'now how man& #loc's are corru"ted? ?ou can check the Select * from V$DATABASE_BLOCK_CORRUPTION; view to determine the
corrupted blocks. What is a flash #ac' =uer&? This feature is also availa#le in i. What are the difference #etween i and 65g related to flash #ac' =uer&@. -racle i flash#ac' 65g enhancement &lashback uer": • •
-lashback version uery -lashback8Transactional8uery view
()g new &eatures: • •
-lashback Table -lashback database
*etup for new feature: • •
!09 -lash ecovery !rea
Descri#e the use of O-WT+P0 and OT+P0 in P13Q1
/O@T?+E allows you to associate a variable with an entire table row. The /T?+E associates a variable with a single column type.
,ow can the "ro#lem #e resolved if a 3+3DBA$ forgets his "assword for logging into enter"rise manager?
There are two ways to do that: ). 1ogin as ?TE9 and change the ? password by using !1TE 0E. 3. ecreate the password file using orapwd and set remote8password8file eJclusive and then restart the instance. G. !lso you can enter as W as sysdbaH and then after change the password X!lter user sys identified by JJJY= ,ow man& ma8imum num#er of columns can #e "art of "rimar& 'e& in a ta#le in i and 65g. ?ou can set primary key in a single table up to )$ columns of table in oracle i and )#g. What is AC?
!, stands for eal !pplication ,luster. In previous versions& it is known as +!!11E1 EPE. !, is a mechanism that allows multiple instances (on different hostsnodes% to access the same database. The benefits: It provides more memory resources& since more hosts are being used= If one host gets down& then other host assumes it>s work load. What is Data Pum"ing? Data +umping is a data movement utility. This is a replacement to impeJp utilities. The earlier impeJp utilities are also data movement utilities& but they work within the local servers only . @here as& impdpeJpdp (Data pumping% are very fast and perform data movements from one database to another database on same as well as different host. In other words& it provides secure transports. What is Data %igration?
Data migration is actually the translation of data from one format to another format or from one storage device to another storage device. Data migration is necessary when a company upgrades its database or system software& either from one version to another or from one program to an entirely different program. What is difference #etween s"file and init.ora file init.ora or s"file both are contains Database parameters information. 'oth are supported by oracle. Every
database instance reuired either any one. If both are present first choice is given to spfile only. init.ora saved in the format of !,II where as +-I1E saved in the format of binary. init.ora information is read by oracle engine at the time of database instance started only that means any modification made in this those are applicable in the neJt startup only. 'ut in spfile modifications (through alter system..... command% can applicable without restarting oracle database (restarting instance%. What is 3C*? Where the 3C* does resides? ,5 " ystem ,hange 5umber " is always getting incremented by Oracle server and will be used to make sure the consistency across the database. The system change number (,5% is an ever"increasing value that uniuely identifies a committed version of the database. Every time a user commits a transaction. Oracle records a new ,5. ?ou can obtain ,5s in a number of ways for eJample from the alert log. ?ou can then use the ,5 as an identifier for purposes of recovery. -or eJample you can perform an incomplete recovery of a database up to ,5 )#G#. Oracle uses ,5s in control files datafile headers and redo records. Every redo log file has both a log seuence number and low and high ,5. ,5 number will be updated in almost all places of the database. ,O5TO1-I1E& D!T!-I1E CE!DE& EDO1OK -I1E (and hence !,CIPE 1OK -I1E%& D!T! '1O,A CE!DE but not in !1ET 1OK file as it is not part of database. ,ow to 'now which =uer& is ta'ing long time? 'y testing with the help of these tools tkprof or using eJplain plan. t'"rof is available to D'! Only where as eJplain plan can run programmer as well as D'! also. !s well as t'"rof generates compleJilty after sucessful eJecution only where as e8"lain "lan can show Oracle internal plan Z other details. Even though they are not alternatives for one to another. 'ut both are designed for one purpose only. They are two different tools they are engaged in different useful situations also you can use 3TAT3PACN to take naps while running those ueries and get the report with details of <1 taking more time to
respond otherwise& you can search Top ten sl with the following views: SQLSELECT * !RO" V$SQL; SQLSELECT * !RO" V$SQLAREA; <16E1E,T L -O9 (E1E,T rownum ubstr(a.sl8teJt ) 3##% sl8teJt Trunc(a.disk8readsDecode(a.eJecutions # ) a.eJecutions%% reads8per8eJecution a.buffer8gets a.disk8reads a.eJecutions a.sorts a.address -O9 vMslarea a ODE '? G DE,%@CEE rownum ; )#= ,ow can &ou chec' which user has which ole.
l6elect L from D'!8O1E8+IP order by grantee= What are clusters Kroups of tables physically stored together because they share common columns and are often used together is called clusters. *ame init.ora@ "arameters which affects s&stem "erformance. These are the +arameters for init.ora which affect system performance D'8'1O,A8'0--E= C!ED8+OO18IVE= OT8!E!8IVE= D'@8IO81!PE= O11'!,A8EK9E5T= OT8!E!8ET!I5ED8IVE= '8'1O,A8108ESTE5DED8T!TITI, C!ED8+OO18EEPE8IVE ,ow do &ou rename a data#ase?
+rior to the introduction of the D'5E@ID (5ID% utility alteration of the internal D'ID of an instance was impossible and alteration of the D'5!9E reuired the creation of a new controlfile. The D'5E@ID utility allows the D'ID to be altered for the first time and makes changing the D'5!9E simpler. 3te"s4 Change DB*A%0 onl& ).9ount the database after clean shutdown. 3.Invoke the D'5E@ID utility (5ID% from the command line using sys user. nid TA20Ts&s"asswordT3,< DB*A%0T3, 30T*A%0+03 Assumin the validation is successful the utility prompts for confirmation before performin the actions*
*ote4 The ET5!9E parameter tells the D'5E@ID utility to only alter the database name.
G.clean shutdown the database <16C0TDO@5 I99EDI!TE et the D'85!9E initialiBation parameter in the initialiBation parameter file (+-I1E% to the new database name. *ote4The D'5E@ID utility does not change the server parameter file (+-I1E%. Therefore& if you use
+-I1E to start your Oracle database& you must re"create the initialiBation parameter file from the server parameter file& remove the server parameter file& change the D'85!9E in the initialiBation parameter file& and then re"create the server parameter file. 'ecause you have changed only the data#ase name& and not thedata#ase ID& it is not necessary to use the 030T1-23 option when you open the database. This means that all previous backups are still usable. 2.,reate a new password file. orapwd fileFc:[oracle[3#[database[pwdTC3.ora passwordFpassword entriesF)# *.Open the database without eset logs option <16tartup= 3te"s4 change DBID onl&
epeat the same above procedure nid TA20Ts&s"asswordT3, hutdown and open the database with EET1OK option
What is the view name where we can get the s"ace for ta#les or views?
D'!8egments= E1E,T EK9E5T85!9E& 09('?TE% -O9 D'!8EK9E5T @CEE EK9E5T85!9EF>T!'1E85!9E> !5D O@5EF>O@5E O- TCE T!'1E KO0+ '? EK9E5T85!9E= @e cannot get the space of view because view does not have its own space it depend on base table. What #ac'ground "rocess refreshes materiali>ed views?
Rob
<16E1E,T T!'1E+!,E85!9E & '?TE -O9 smMts8free= %&'%'+ +,-'%.,_N,%(-+%/1024/1024) !3 -,_!_%., 53. - +,-'%.,_N,;
If CP! is ver& slow$ what can u do to s"eed? se V%+,+ to c6ec" t6e . en7ueues or use also +3. and %, commands for . load8 What would you use to improve performance on an insert statement that places millions of rows into that table?
ro# t6e indexes and recreate after insert8 ' +ri99ers to be %,-' and t6en N,-' once t6e insert com#leted8 %,-' t6e lustered ndex and t6en N,-' once t6e insert com#leted8 If Monday take full backup and Tuesday it was cumulative backup and Wednesday we taken incremental backup, Thursday some disaster happen then what type of recovery and how it will take?
estore t6e onday full bac"u# : +uesday cumulatie bac"u# :
What is the difference #etween local managed Ta#les"ace R dictionar& managed Ta#les"ace ?
The basic diff between a locally managed tablespace and a dictionary managed tablespace is that in the dictionary managed tablespace every time a eJtent is allocated or deallocated data dictionary is updated which increases the load on data dictionary while in case of locally managed tablespace the space information is kept inside the datafile in the form of bitmaps every time a eJtent is allocated or deallocated only the bitmap is updated which removes burden from data dictionary. The Tablespaces that record eJtent allocationdeallocation in the dictionary are called dictionary managed tablespaces and tablespaces that record eJtent allocation in the tablespace header are called locally managed tablespaces.
While installing the -racle i .<@ version$ automaticall& s&stem ta'es the s"ace of a""ro8imatel& 2B. That is fine.... *ow$ if m& data#ase is growing u" and it is reaching the 2B of m& data#ase s"ace...*ow$ I would li'e to e8tend m& Data#ase s"ace to <5 2B or <7 2B... what are the things i have to do?
"ollowin steps can be performed:
). -irst check for available space on the server. 3. ?ou can increase the siBe of the datafiles if you have space available on the server and also you can make auto eJtend on. o that in future you don>t need to manually increase the siBe. The alternative better idea is that make the autoeJtend off and add more datafiles to the Tablespace. 9aking a single datafile to a bigger siBe is risky. 'y making autoeJtend off you can monitor the growth of the tablespace schedule a growth monitoring script with a threshold of 4* full.
,ow to handle data corru"tion for A3% t&"e files?
The storage array should contain one or more spare disks (often called hot spares%. @hen a physical disk starts to report errors to the monitoring infrastructure or fails suddenly the firmware should immediately restore fault tolerance by mirroring the contents of the failed disk onto a spare disk When a user comes to &ou and as's that a "articular 3Q1 =uer& is ta'ing more time. ,ow will &ou solve this?
If you find the <1
tablespace8name>%= 6eJecute dbms8space8admin.tablespace8convert8from8local(>tablespace8name>%= What is a cluster Ne& ? The related columns of the tables are called the cluster key. The cluster key is using a cluster indeJ and its value is stored only once formultiple tables in the cluster. What are four "erformance #ottlenec's that can occur in a data#ase server and how are the& detected and "revented? ,+0 bottlenecks •
•
0ndersiBed memory structures Inefficient or high"load <1 statements
•
Database configuration issues
•
&our ma+or steps to detect these issues: • • • •
!nalyBing OptimiBer tatistics !nalyBing an EJecution +lan 0sing Cints to Improve Data @arehouse +erformance 0sing !dvisors to Perify <1 +erformance
nal"-ing $ptimi-er *tatistics
OptimiBer statistics are a collection of data that describes more details about the database and the obects in the database. The optimiBer statistics are stored in the data dictionary. They can be viewed using data dictionary views similar to the following: E1E,T L -O9 D'!8,CED01E8RO' @CEE RO'85!9E >K!TCE8T!T8RO'>= 'ecause the obects in a database can constantly change statistics must be regularly updated so that they accurately describe these database obects. tatistics are maintained automatically by Oracle Database or you can maintain the optimiBer statistics manually using the D'98T!T package. nal"-ing an Execution lan
Keneral guidelines for using the ES+1!I5 +1!5 statement are: To use the <1 script 0T1S+1!5.<1 to create a sample output table called +1!58T!'1E in your schema. To include the ES+1!I5 +1!5 -O clause prior to the <1 statement. !fter issuing the ES+1!I5 +1!5 statement to use one of the scripts or packages provided by Oracle Database to display the most recent plan table output. The eJecution order in ES+1!I5 +1!5 output begins with the line that is indented farthest to the right. If two lines are indented eually then the top line is normally eJecuted first. %o anal"-e E/01 0 output:
ES+1!I5 +1!5 -O (W?O0 <0E?H%= ES+1!I5 +1!5 -O E1E,T p.prod8name c.channel8desc 09(s.amount8sold% revenue -O9 products p channels c sales s @CEE s.prod8id p.prod8id !5D s.channel8id c.channel8id !5D s.time8id 'ET@EE5 >#)")3"3##)> !5D >G)")3"3##)>KO0+ '? p.prod8name c.channel8desc= E1E,T L -O9 T!'1E (D'98S+1!5.DI+1!?%= !sing Advisors how to /erif& 3Q1 Performance? 0sing the <1 Tuning !dvisor and <1 !ccess !dvisor you can invoke the uery optimiBer in advisory mode to eJamine a given <1 statement or set of <1 statements and provide recommendations to improve their efficiency. The <1 Tuning !dvisor and <1 !ccess !dvisor can make various types of recommendations such as creating <1 profiles restructuring <1 statements creating additional indeJes or materialiBed views and refreshing optimiBer statistics. !dditionally Oracle Enterprise 9anager enables you to accept and implement many of these recommendations in very few steps Difference #etween man ecover& Catalog or nocatalog -"tion? The recovery catalog is an optional feature of 9!5 though Oracle& recommends that you use it& it isnHt reuired. One maor benefit of the recovery catalog is that it stores metadata a#out #ac'u"s in a database that can be reported or ueried. ,atalog means you have a recovery catalog database& nocatalog means that you are using the controlfile as rman re"ositor&. Of course catalog option can only be used when recovery catalog is present (which is not mandatory%. -rom functional point of view there is no difference either taking backup in catalog or nocatlaog mode. What is -racle *et? Oracle 5et is responsible for handling client(to(server and server to( server communications in an Oracle environment. It manages the flow of information in the Oracle network infrastructure. Oracle 5et is used to establish the initial connection to the Oracle server and then it acts as the messenger& which passes reuests from the client back to the server or between two Oracle servers. Difference of Bac'u" 3ets and Bac'u" Pieces?
9!5 can store backup data in a logical structure called a backup set& which is the smallest unit of an 9!5 backup. ! backup set contains the data from one or more datafiles& archived redo logs& or control files or server parameter file. 'ackup sets& which are only created and accessed through 9!5& are the only form in which 9!5 can write backups to media managers such as tape drives and tape libraries. ! backup set contains one or more binary files in an 9!5"specific format. This file is known as a backup
piece. ! backup set can contain multiple datafiles. -or eJample& you can back up ten datafiles into a single backup set consisting of a single backup piece. In this case& 9!5 creates one backup piece as output. The backup set contains only this backup piece. What is an !T1GFI10? What are different "rocedures and functions associated with it?
The 0T18-I1E package lets your +1<1 programs read and write operating system (O% teJt files. It provides a restricted version of standard O stream file inputoutput (IO%. 3u#"rogram (Descri"tion F-P0* function"Opens a file for input or output with the default line siBe. I3G-P0* function "Determines if a file handle refers to an open file. FC1-30 procedure ",loses a file. FC1-30GA11 procedure ",loses all open file handles. 20TG1I*0 procedure "eads a line of teJt from an open file. P!T procedure"@rites a line to a file. This does not append a line terminator. *0WG1I*0 procedure"@rites one or more O"specific line terminators to a file. P!TG1I*0 procedure "@rites a line to a file. This appends an O"specific line terminator. P!TF procedure "! +0T procedure with formatting. FF1!3, procedure"+hysically writes all pending output to a file. F-P0* function "Opens a file with the maJimum line siBe specified. Differentiate #etween T!*CAT0 and D010T0?
The Delete commands will log the data changes in the log file where as the truncate will simply remove the data without it. Cence Data removed by Delete command can be rolled back but not the data removed by T05,!TE. Truncate is a DD1 statement whereas DE1ETE is a D91 statement. What is an -racle Instance?
Instance is a combination of memory structure and process structure. 9emory structure is 32A ystem or hared Klobal !rea% and +rocess structure is #ac'ground "rocesses . Com"onents of 32A4 Data#ase Buffer Cache4 It is further divided into Library ,ache and 4ata 4ictionary ,ache or ow ,ache &
hared +oollarge poolstream poolava pool edo log Buffer & Bac'ground Process4
9andatory +rocesses (9O5& +9O5& D'@& 1K@& ,A+T& E,O% Optional +rocess (!,5& '!,& 99!5& 99O5& 9951% @hen Oracle starts an instance& it reads the initialiBation parameter file to determine the values of initialiBation parameters. Then& it allocates an K!& which is a shared area of memory used for database information& and creates background processes. !t this point& no database is associated with these memory structures and processes. What information is stored in Control File? The database name& The timestamp of database creation& The names and locations of associated datafiles and redo log files& Tablespace information& Datafile offline ranges& The log history& !rchived log information& 'ackup set and backup piece information& 'ackup datafile and redo log information& Datafile copy information& The current log seuence number When &ou start an -racle DB which file is accessed first?
To tart an instance& oracle server need a parameter file which contains information about the instance& oracle server searches file in following seuence: )% +-I1E """""" if finds instance started .. EJit 3% Default +-I1E "" if it is spfile is not found G% +-I1E """""""" if default spfile not find& instance started using pfile. 2% Default +-I1E "" is used to start the instance.
What is the So# of 3%-*$ P%-* "rocesses? 3%-*4 ystem monitor performs instance recover" at instance startup in a multiple instances. 2ecovers other instances that have failed in cluster environment .It cleans up temporar" segments that are no
longer in use. 2ecovers dead transactions skipped during crash and instance recovery. Coalesce the free extents within the database & to make free space contiguous and easy to allocate.
P%-*4 +rocess monitor performs recover" when a user process fails . It is responsible for cleaning up the cache, freeing resources used b" the processes . In the mts environment it checks on dispatcher and server processes& restarting them at times of failure.
What is Instance ecover&?
@hen an Oracle instance fails& Oracle performs an instance recovery when the associated database is re" started. Instance recovery occurs in two steps: Cache recover&4 ,hanges being made to a database are recorded in the database buffer cache. These
changes are also recorded in online redo log files simultaneously. @hen there are enough data in the database buffer cache& they are written to data files. If an Oracle instance fails before the data in the database buffer cache are written to data files& Oracle uses the data recorded in the online redo log files to recover the lost data when the associated database is re"started. This process is called cache recovery. Transaction recover&4 @hen a transaction modifies data in a database& the before image of the modified data is stored in an undo segment. The data stored in the undo segment is used to restore the original values in case a transaction is rolled back. !t the time of an instance failure& the database may have uncommitted transactions. It is possible that changes made by these uncommitted transactions have gotten saved in data files. To maintain read consistency& Oracle rolls back all uncommitted transactions when the associated database is re"started. Oracle uses the undo data stored in undo segments to accomplish this. This process is called transaction recovery. ). olling forward the committed transactions 3. olling backward the uncommitted transactions What is written in edo 1og Files? 1og writer (1K@% writes redo log buffer contents Into edo 1og -iles. 1og writer does this every three seconds& when the redo log buffer is )G full and immediately before the Database @riter (D'@n% writes its changed buffers into the data file. ,ow do &ou control num#er of Datafiles one can have in an -racle data#ase? @hen starting an Oracle instance& the database>s parameter file indicates the amount of K! space to reserve for datafile information= the maJimum number of datafiles is controlled by the D'8-I1E parameter. This limit applies only for the life of the instance. ,ow man& %a8imum Datafiles can there #e in an -racle Data#ase? Default maJimum datafile is 3** that can be defined in the control file at the time of database creation. It can be increased by setting the initialiBation parameter value up to higher at the time of database creation. etting this value too higher can cause D'@ issues. +efore 8i a9imum number of datafile in database was ;55*After 8i the limit is applicable to the number of datafile in the Tablespace*
What is a Ta#les"ace?
! tablespace is a logical storage unit within the database. It is logical because a tablespace is not visible in the file system of the machine on which database resides. ! tablespace in turn consists of at least one datafile& which& in tune are physically located in the file system of the server. The tablespace builds the bridge between the Oracle database and the file system in which the table>s or indeJ> data is stored. There are three types of tablespaces in /racle:
+ermanent tablespaces& 0ndo tablespaces& Temporary tablespaces
What is the "ur"ose of edo 1og files?
The purpose of redo log file is to record all changes made to the data during the recovery of database. It always advisable to have two or more redo log files and keep them in a separate disk& so you can recover the data during the system crash. Which default Data#ase roles are created when &ou create a Data#ase? ,onnect & resource and dba are three default roles What is a Chec'"oint? A chec'point performs the followin three operations:
). Every block in the buffer cache is written to the data files. That is& it synchroniBes the data blocks in the buffer cache with the datafiles on disk. It>s the D'@ that writes all modified database blocks back to the datafiles. 3. The latest ,5 is written (updated% into the datafile header. G. The latest ,5 is also written to the controlfiles. The update of the datafile headers and the control files is done by the 1K@ (if ,A+T is enabled%. !s of version 4.#& ,A+T is enabled by default. The date and time of the last checkpoint can be retrieved through chec'point
?ou must be logged on as a D'! user& then issue For Data File4 <16!lter database datafile >c:[oradata[mysid[S?V.dbf> autoeJtend on neJt )#m maJsiBe 2#K <16!lter database datafile >c:[oradata[mysid[S?V.dbf> autoeJtend on neJt )#m maJsiBe unlimited= For Tem" File4
<16!lter database tempfile >c:[oradata[mysid[S?V.dbf> autoeJtend on neJt )#m maJsiBe unlimited= This would turn on autoeJtend& grab new disk space of )#m when needed and have no upper limit on the siBe of the datafile. 5ote: This would be bad on a >5bit machine& where the ma9 si%e is typically ?i* What is a 3hared Pool? It is the area in K! that allows sharing of parsed <1 statements among concurrent users. It is to store the <1 statements so that the identical <1 statements do not have to be parsed each time they>re eJecuted. The shared pool is the part of the K! where (among others% the following things are stored: OptimiBed uery plans& ecurity checks& +arsed <1 statements& +ackages& Obect information What is 'e"t in the Data#ase Buffer Cache?
Database 'uffer cache is one of the most important components of ystem Klobal !rea (K!%. Database 'uffer ,ache is the place where data blocks are copied from datafiles to perform <1 operations. 'uffer ,ache is shared memory structure and it is concurrently accessed by all server processes. Oracle allows different block siBe for different tablespaces. ! standard block siBe is defined in D'8'1O,A8IVE initialiBation parameter. ystem tablespace uses standard block siBe. D'8,!,CE8IVE parameter is used to define siBe for Database buffer cache. -or eJample to create a cache of 4## 9'& set parameter as below D'8,!,CE8IVEF4##9 If you have created a tablesapce with bock siBe different from standard block siBe& for eJample your standard block siBe is 2k and you have created a tablespace with 4k block siBe then you must create a 4k buffer cache as
D'84A8,!,CE8IVEF3*$ ,ow man& ma8imum edo 1ogfiles one can have in a Data#ase? 9aJimum number of log files a database can accommodate depends on the parameter 9!S1OK9E9'E specified during database creation. In a database we can create 3** maJimum redo log files. It depends on what you specified for 9!S1OK-I1E during database creation (manually% or what you specified for 9aJimum no. of redo log files with D',!. What is P2AGA2202AT0GTA20T "arameter?
+K!8!KKEK!TE8T!KET is an Oracle server parameter that specifies the target aggregate +K! memory available to all server processes attached to the instance. ome of the properties of the +K!8!KKEK!TE8T!KET parameter are given below: +arameter type: 'ig integer yntaJ: +K!8!KKEK!TE8T!KET F integer \A 9 K]^Default value: 3#/ of K! siBe or )#9'& whichever is greater or modifiable by !1TE ?TE9 1arge Pool is used for what? 1arge +ool is an optional memory structure used for the following purposes: " ()% ession information for shared server
(3% IO server processes (G% Parallel =ueries (2% Bac'u" and recover& if using through %A*.
The role of 1arge +ool is important because otherwise memory would be allocated from the hared pool. Cence 1arge pool also reduces overhead of hared pool. What is PCT Increase setting? +,TI5,E!E refers to the percentage by which each neJt eJtent (beginning with the third eJtend% will grow. The siBe of each subseuent eJtent is eual to the siBe of the previous eJtent plus this percentage increase. What is PCTF00 and PCT!30D 3etting? PCTF00 is a block storage parameter used to specify how much space should be left in a database block for future updates. -or eJample& for +,T-EEF)#& Oracle will keep on adding new rows to a block until it is #/ full. This leaves )#/ for future updates (row eJpansion%. @hen using Oracle !dvanced ,ompression& Oracle will trigger block compression when the PCTF00 is reached. This eliminates holes created by row deletions and maJimiBes contiguous free space in blocks. PCT!30D is a block storage parameter used to specify when Oracle should consider a database block to be empty enough to be added to the freelist. Oracle will only insert new rows in blocks that is enueued on the freelist. -or eJample& if +,T0EDF2#& Oracle will not add new rows to the block unless sufficient rows are deleted from the block so that it falls below 2#/ empty. <16 E1E,T +ct8free -O9 user8tables @CEE table8name F WE9+H=
What is ow %igration and ow Chaining?
There are two circumstances when this can occur& the data for a row in a table may be too large to fit into a single data block. This can be caused by either row chaining or row migration. Chaining4 Occurs when the row is too large to fit into one data block when it is first inserted. In this case& Oracle stores the data for the row in a chain of data blocks (one or more% reserved for that segment. ow chaining most often occurs with large rows& such as rows that contain a column of data type 1O5K& 1O5K !@& 1O'& etc. ow chaining in these cases is unavoidable. %igration4 Occurs when a row that originally fitted into one data block is updated so that the overall row
length increases& and the blockHs free space is already completely filled. In this case& Oracle migrates the data for the entire row to a new data block& assuming the entire row can fit in a new block. Oracle preserves the original row piece of a migrated row to point to the new block containing the migrated row: the rowid of a mirated row does not chane . @hen a row is chained or migrated& performance associated with this row
decreases because $racle must scan more than one data block to retrieve the information for that row. ).I5ET and 0+D!TE statements that cause migration and chaining perform poorly& because they perform additional processing. 3.E1E,Ts that use an indeJ to select migrated or chained rows must perform additional IOs. Detection4 9igrated and chained rows in a table or cluster can be identified by using the !5!1?VE
command with the 1IT ,C!I5ED O@ option. This command collects information about each migrated or chained row and places this information into a specified output table. To create the table that holds the chained rows& eJecute script !T1C,AI*.3Q1. <16 !5!1?VE T!'1E scott.emp 1IT ,C!I5ED O@= <16 E1E,T L -O9 chained8rows= ?ou can also detect migrated and chained rows by checking the Wtable fetch continued rowH statistic in the vMsysstat view. <16 E1E,T name& value -O9 vMsysstat @CEE name F Wtable fetch continued rowH= Althouh miration and chainin are two different thins& internally they are represented by /racle as one* @hen detectin miration and chainin of rows you should analy%e carefully what you are dealin with*
What is -ra(56777 ( 3na"shot Too -ld error and how do &ou avoid it?
). Increase the siBe of rollback segment. (@hich you have already done% 3. +rocess a range of data rather than the whole table. G. !dd a big rollback segment and allot your transaction to this '. 2. There is also possibility of ' getting shrunk during the life of the uery by setting optimal. *. !void freuent commits. $. Koogle out for other causes. What is a locall& %anaged Ta#les"ace?
! 1ocally 9anaged Tablespace is a tablespace that manages its own eJtents maintaining a bitmap in each data file to keep track of the free or used status of blocks in that data file. Each bit in the bitmap corresponds to a block or a group of blocks. @hen the eJtents are allocated or freed for reuse& Oracle changes the bitmap values to show the new status of the blocks. These changes do not generate rollback information because they do not update tables in the data dictionary (eJcept for tablespace uota information%& unlike the default method of Dictionary " 9anaged Tablespaces. "ollowin are the ma1or advantaes of locally manaed tablespaces
= educed contention on data dictionary tables ^ 5o rollback generated ^ 5o coalescing reuired ^ educed recursive space management.
Can &ou audit 3010CT statements? ?es& we can audit the select statements. ,heck out the below eJample:
<16 show parameter audit 5!9E T?+E P!10E ____________ ___ __________ audit8file8dest string E:[O!,1E[+OD0,T[)#.3.#[D'83[ !D9I5[A[!D09+ audit8sys8operations boolean -!1E audit8trail string 5O5E <16 begin dbms8fga.add8policy ( obect8schema F6 W,OTTH& obect8name F6 WE9+3`& policy8name F6 WE9+8!0DITH& statement8types F6 WE1E,TH %= end= +1<1 procedure successfully completed. <16select L from dba8fga8audit8trail= no rows selected In C schema: <16 create table bankim( name varchar3 ()#%& roll number (3#%%= Table created. <16 insert into bankim values (WbankimH& )#%= ) row created. <16 insert into bankim values (Wbankim3`& 3#%= ) row created. <16 select L from bankim= 5!9E O11 ___" ___" bankim )# bankim3 3# <16 select name from bankim= 5!9E ___" bankim bankim3 In sys schema: <16set head off <16 select sl8teJt from dba8fga8audit8trail= select count(L% from emp3 select L from emp3 select L from empG select count(L% from bankim select L from bankim select name from bankim What does DB%3GF2A "ac'age do?
The dbms8fga +ackage is the central mechanism for the -K! is implemented in the package dbms8fga& where all the !+Is are defined. Typically& a user other than ? is given the responsibility of maintaining these policies. @ith the convention followed earlier& we will go with the user E,0E& who is entrusted with much of the security features. The following statement grants the user E,0E enough authority to create and maintain the auditing facility. Krant eJecute on dbms8fga to secuser= The biggest problem with this package is that the polices are not like regular obects with owners. @hile a user with eJecute permission on this package can create policies& he or she can drop policies created by another user& too. This makes it eJtremely important to secure this package and limit the use to only a few users who are called to define the policies& such as E,0E& a special user used in eJamples. What is Cost Based -"timi>ation? The ,'O is used to design an eJecution plan for <1 statement. The ,'O takes an <1 statement and tries to weigh different ways (plan% to eJecute it. It assigns a cost to each plan and chooses the plan with smallest cost. The cost for smallest is calculated: Ph&sical I- 1ogical I- 6555 net I-. ,ow often &ou should collect statistics for a ta#le? ,'O needs some statistics in order to assess the cost of the different access plans. These statistics includes: iBe of tables& iBe of indeJes& number of rows in the tables& number of distinct keys in an indeJ& number of levels in a 'L indeJ& average number of blocks for a value& average number of leaf blocks in an indeJ These statistics can be gathered with d#msGstats and the monitoring feature. ,ow do &ou collect statistics for a ta#le$ schema and Data#ase?
tatistics are gathered using the DB%3G3TAT3 package. The DB%3G3TAT3 package can gather statistics on table and indeJes& and well as individual columns and partitions of tables. @hen you generate statistics for a table& column& or indeJ& if the data dictionary already contains statistics for the obect& then Oracle updates the eJisting statistics. The older statistics are saved and can be restored later if necessary. @hen statistics are updated for a database obect& Oracle invalidates any currently parsed <1 statements that access the obect. The neJt time such a statement eJecutes& the statement is re"parsed and the optimiBer automatically chooses a new eJecution plan based on the new statistics. Collect *tatistics on %able 0evel
slplus scotttiger eJec dbms8stats.gather8table8stats ( " ownname tabname
F6 >,OTT>& " F6 >E9+>& "
estimate8percent F6 dbms8stats.auto8sample8siBe& " method8opt F6 >for all columns siBe auto>& " cascade degree
F6 true& " F6 * " %
Collect *tatistics on *chema 0evel
slplus scotttiger eJec dbms8stats.gather8schema8stats ( " ownname
F6 >,OTT>& "
options F6 >K!TCE>& " estimate8percent F6 dbms8stats.auto8sample8siBe& " method8opt cascade degree
F6 >for all columns siBe auto>& " F6 true& " F6 * " %
Collect *tatistics on $ther 0evels
D'98T!T can collect optimiBer statistics on the following levels& see Oracle 9anual K!TCE8D!T!'!E8T!T K!TCE8DI,TIO5!?8T!T K!TCE8-ISED8O'RE,T8T!T K!TCE8I5DES8T!T K!TCE8,CE9!8T!T K!TCE8?TE98T!T K!TCE8T!'1E8T!T Can &ou ma'e collection of 3tatistics for ta#les automatic?
?es& you can schedule your statistics but in some situation automatic statistics gathering may not be adeuate. It suitable for those databases whose obect is modified freuently. 'ecause the automatic statistics gathering runs during an overnight batch window& the statistics on tables which are significantly modified during the day may become stale. There may be two scenarios in this case: •
Polatile tables that are being deleted or truncated and rebuilt during the course of the day.
•
Obects which are the target of large bulk loads which add )#/ or more to the obectHs total siBe.
o you may wish to manually gather statistics of those obects in order to choose the optimiBer the best eJecution plan. There are two ways to gather statistics. ).0sing D'98T!T package. 3.0sing !5!1?VE command ,ow can &ou use A*A1+H0 statement to collect statistics?
!5!1?VE T!'1E emp ETI9!TE T!TITI, -O !11 ,O1095= !5!1?VE I5DES inv8product8iJ P!1ID!TE T0,T0E= !5!1?VE T!'1E customers P!1ID!TE E- 0+D!TE= !5!1?VE T!'1E orders 1IT ,C!I5ED O@ I5TO chained8rows= !5!1?VE T!'1E customers P!1ID!TE T0,T0E O51I5E= To delete statistics:
!5!1?VE T!'1E orders DE1ETE T!TITI,= To et the analy%e details:
E1E,T owner8name& table8name& head8rowid& analyBe8timestamp -O9 chained8rows= -n which columns &ou should create Inde8es? The followin list ives uidelines in choosin columns to inde9: ?ou
should create indeJes on columns that are used freuently in W,00 clauses. ?ou should create indeJes on columns that are used freuently to oin tables. ?ou should create indeJes on columns that are used freuently in -D0 B+ clauses. ?ou should create indeJes on columns that have few of the same values or uni=ue values in the table. ?ou
should not create indeJes on small ta#les (tables that use only a few blocks% because a full table
scan may be faster than an indeJed uery. If possible& choose a "rimar& 'e& that orders the rows in the most appropriate order. If only one column of the concatenated indeJ is used freuently in @CEE clauses& place that column first
in the ,E!TE I5DES statement. If more than one column in a concatenated indeJ is used freuently in @CEE clauses& place the most selective column first in the ,E!TE I5DES statement. What t&"e of Inde8es is availa#le in -racle? B(tree
inde8es4 the default and the most common.
B(tree ,ash
cluster inde8es4 defined specifically for cluster.
cluster inde8es4 defined specifically for a hash cluster.
2lo#al
and local inde8es4 relate to partitioned tables and indeJes.
everse Bitma"
'e& inde8es4 most useful for Oracle eal !pplication ,lusters.
inde8es4 compact= work best for columns with a small set of values
Function(#ased inde8es4 contain
the pre"computed value of a functioneJpression Domain indeJes:
specific to an application or cartridge. What is B(Tree Inde8? '"Tree is an indeJing techniue most commonly used in databases and file systems where pointers to data are placed in a balance tree structure so that all references to any data can be accessed in an eual time frame . It is also a tree data structure which keeps data sorted so that searching& inserting and deleting can
be done in logarithmic amortiBed time. A ta#le is having few rows$ should &ou create inde8es on this ta#le?
?ou should not create indeJes on small ta#les (tables that use only a few blocks% because a full table scan may be faster than an indeJed uery. A Column is having man& re"eated values which t&"e of inde8 &ou should create on this column '"Tree indeJ is suitable if the columns being indeJed are high cardinality (number of repeated values%. In fact for this situation a bitmap indeJ is very useful but bitmap indeJ are vary eJpensive. When should &ou re#uild inde8es? There is no thumb rule Xwhen you should rebuild the indeJY. !ccording to eJpert it depends upon your database situation: @hen the data in indeJ is sparse (lots of holes in indeJ& due to deletes or updates% and your uery is usually range based or If 'level 6G then takes indeJ in rebuild consideration= desc D'!8IndeJes= 'ecause when you rebuild indeJes then database performance goes down. In fact binary tree indeJ can never be unbalanced. 'inary tree performance is good for both small and large tables and does not degrade with the growth of table. Can &ou #uild inde8es online?
?es& we can build indeJ online. It allows performing D91 operation on the base table during indeJ creation. ?ou can use the statements: ,E!TE I5DES O51I5E and DO+ I5DES O51I5E. !1TE I5DES E'0I1D O51I5E is used to rebuild the indeJ online. ! Table 1ock is reuired on the indeJ base table at the start of the ,E!TE or E'0I1D process to guarantee DD1 information. ! lock at the end of the process also reuired to merge change into the final indeJ structure. A ta#le is created with the following setting storage initial <55' ne8t <55' mine8tents < ma8e8tents 655 "ctincrease 5@ What will #e si>e of th e8tent?
+ercent Increase allows the segment to grow at an increasing rate. The first two eJtents will be of a siBe determined by the Initial and 5eJt parameter (3##k% The third eJtent will be ) 7 +,TI5,E!E)## times the second eJtent ().2L3##F34#k%. !5D the 2th eJtent will be ) 7 +,TI5,E!E)## times the third eJtent ().2L34#FG3k% and so on... Can &ou edefine a ta#le -nline?
?es. @e can perform online table redefinition with the Enterprise 9anager eorganiBe Obects wiBard or with the D'98EDE-I5ITIO5 package.
It provides a mechanism to make table structure modification without significantly affecting the table availability of the table. @hen a table is redefining online it is accessible to both ueries and D91 during the redefinition process. Pur"ose for Ta#le edefinition !dd& remove& or rename columns from a table • •
,onverting a non"partitioned table to a partitioned table and vice versa
•
witching a heap table to an indeJ organiBed and vice versa
9odifying storage parameters !dding or removing parallel support • •
eorganiBe (defragmenting% a table
•
Transform data in a table
estrictions for Ta#le edefinition4 One cannot redefine 9aterialiBed Piews (9Piews% and tables with 9Piews or 9Piew 1ogs defined on •
them. One cannot redefine Temporary and ,lustered Tables One cannot redefine tables with '-I1E& 1O5K or 1O5K !@ columns
• •
•
One cannot redefine tables belonging to ? or ?TE9 One cannot redefine Obect tables Table redefinition cannot be done in 5O1OKKI5K mode (watch out for heavy archiving%
•
,annot be used to add or remove rows from a table
• •
Can &ou assign Priorit& to users?
?es& we can do this through resource manager. The Database esource 9anager gives a database administrators more control over resource management decisions& so that resource allocation can be aligned with an enterprise>s business obectives. @ith /racle database esource anaer an administrator can:
Kuarantee certain users a minimum amount of processing resources regardless of the load on the system
and the number of users Distribute available processing resources by allocating percentages of ,+0 time to different users and
applications. 1imit the degree of parallelism of any operation performed by members of a group of users ,reate an active session pool. This pool consists of a specified maJimum number of user sessions
allowed to be concurrently active within a group of users. !dditional sessions beyond the maJimum are ueued for eJecution& but you can specify a timeout period& after which ueued obs terminate. !llow automatic switching of users from one group to another group based on administrator"defined
criteria. If a member of a particular group of users creates a session that runs for longer than a specified amount of time& that session can be automatically switched to another group of users with different resource reuirements. +revent the eJecution of operations that are estimated to run for a longer time than a predefined limit ,reate an undo pool. This pool consists of the amount of undo space that can be consumed in by a group
of users. ,onfigure an instance to use a particular method of allocating resources. ?ou can dynamically change the
method& for eJample& from a daytime setup to a nighttime setup& without having to shut down and restart the instance.
Can one switch to another data#ase user without a "assword?
0sers normally use the ,O55E,T statement to connect from one database user to another. Cowever& D'!s can switch from one user to another without a password. Of course it is not advisable to bridge Oracle>s security& but look at this eJample: <16 ,O55E,T as sysdba <16 E1E,T password -O9 dba8users @CEE usernameF>,OTT>= -42422,G22#3'$N <16 !1TE 0E scott IDE5TI-IED '? anything= <16 ,O55E,T scottanything OA& we>re in. 1et>s uickly change the password back before anybody notices. <16 !1TE 0E scott IDE5TI-IED '? P!10E >-42422,G22#3'$N>= 0ser altered. ,ow do &ou delete du"licate rows in a ta#le? There is a several method to delete duplicate row from the table:
%ethod64
DE1ETE -O9 C!!5 ! @CEE O@ID 6 (E1E,T min(rowid% -O9 C!!5 ' @CEE !.E9+1O?EE8ID F '.E9+1O?EE8ID%= %ethod<4 delete from C!!5 t) where eJists (select >J> from C!!5 t3 where t3.E9+1O?EE8ID F t).E9+1O?EE8ID and t3.E9+1O?EE8ID F t).E9+1O?EE8ID and t3.rowid 6 t).rowid%= %ethod4
DE1ETE C!!5 @CEE rowid I5 ( E1E,T 1E!D(rowid% OPE (+!TITIO5 '? E9+1O?EE8ID ODE '? 5011% -O9 C!!5 %= %ethod4
delete from C!!5 where rowid not in ( select min(rowid% from C!!5 group by E9+1O?EE8ID%= %ethod74
delete from C!!5 where rowid not in ( select min(rowid% from C!!5 group by E9+1O?EE8ID%= %ethodL4 <16 create table table8name3 as select distinct L from table8name)= <16 drop table table8name)= <16 rename table8name3 to table8name)= What is Automatic %anagement of 3egment 3"ace setting? !utomatic egment pace 9anagement (!9% introduced in Oraclei is an easier way of managing space in a segment using bitmaps. It eliminates the D'! from setting the parameters pctused& freelists& and freelist groups. !9 can be specified only with the locally managed tablespaces (19T%. The ,E!TE T!'1E+!,E statement has a new clause EK9E5T +!,E 9!5!KE9E5T. Oracle uses bitmaps to manage the free
space. ! bitmap& in this case& is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. !s more or less space becomes available in a data block& its new state is reflected in the bitmap. ,E!TE T!'1E+!,E myts D!T!-I1E >oradatamysidmyts#).dbf> IVE )##9 ESTE5T 9!5!KE9E5T 1O,!1 05I-O9 IVE 39 EK9E5T +!,E 9!5!KE9E5T !0TO= What is C-%P033 and C-*3I3T0*T setting in 0:P-T utilit&?
If ,O9+EF?& the I5ITI!1 storage parameter is set to the total siBe of all eJtents allocated for the obect. The change takes effect only when the obect is imported. etting C-*3I3T0*T+ eJports all tables and references in a consistent state. This slows the eJport& as rollback space is used. If ,O5ITE5TF5 and a record is modified during the eJport& the data will become inconsistent. What is the difference #etween Direct Path and Convention Path loading?
@hen you use <1 loader by default it use conventional path to load data. This method competes eually with all other oracle processes for buffer resources. This can slow the load. ! direct path load eliminates much of the Oracle database overhead by formatting Oracle data blocks and writing the data blocks directly to the database files. If load speed is most important to you& you should use direct path load because it is faster. What is an Inde8 -rgani>ed Ta#le? !n indeJ"organiBed table (IOT% is a type of table that stores data in a 'LTree indeJ structure. 5ormal relational tables& called heap"organiBed tables& store rows in any order (unsorted%. ,E!TE T!'1E m&Giot (id I5TEKE +I9!? AE?& value P!,C!3 (*#%% OK!5IV!TIO5 I5DES= What are a 2lo#al Inde8 and 1ocal Inde8?
@hen you create a partitioned table& you should create an indeJ on the table. The indeJ may be partitioned according to the same range values that were used to partition the table. 1ocal keyword in the indeJ partition tells oracle to create a separate indeJ for each partition of the table. The 2lo#al clause in create indeJ command allows you to create a non$partitioned indeJ or to specify ranges for the indeJ values that are different from the ranges for the table partitions. 1ocal indeJes may be easier to manage than global indeJes however& global indeJes may perform uniueness checks faster than local (portioned% indeJes perform them. What is difference #etween %ultithreaded3hared 3erver and Dedicated 3erver?
Oracle Database creates server processes to handle the reuests of user processes connected to an instance. !
dedicated server process& which services only one user process
!
shared server process& which can service multiple user processes
?our database is always enabled to allow dedicated server processes& but you must specifically configure and enable shared server by setting one or more initialiBation parameters. Can &ou im"ort o#ects from -racle ver. J. to i? @e can not import from lower version eJport to higher version in fact. 'ut not sure may be now concept is changed. ,ow do &ou move ta#les from one ta#les"ace to another ta#les"ace? %ethod 64
EJport the table& drop the table& create the table definition in the new tablespace& and then import the data (imp ignoreFy%. %ethod <4 ,reate a new table in the new tablespace with the ,E!TE T!'1E J ! E1E,T L from y command: ,E!TE T!'1E temp8name T!'1E+!,E new8tablespace ! E1E,T L -O9 real8table= Then drop the original table and rename the temporary table as the original:
DO+ T!'1E real8table= E5!9E temp8name TO real8table= *ote4 After step = or =5 is done& be sure to recompile any procedures that may have been invalidated by droppin the table* !refer method =& but =5 is easier if there are no inde9es& constraints& or triers* If there are& you must manually recreate them*
%ethod 4
If you are using Oracle 4i or above then simply use: <16!lter table table8name move tablespace tablespace8name= ,ow do see how much s"ace is used and free in a ta#les"ace? E1E,T L -O9 9MT8-EE= E1E,T T!'1E+!,E85!9E& 09('?TE% -O9 D'!8-EE8+!,E KO0+ '? T!'1E+!,E85! 9E= Can view #e the #ased on other view?
?es& the view can be created from other view by directing a select uery to use the other view data. What ha""ens$ if &ou not s"ecif& Dictionar& o"tion with the start o"tion in case of 1og%inor conce"t?
It is recommended that you specify a dictionar& o"tion. If you do not& 1og9iner cannot translate internal obect identifiers and datatypes to obect names and eJternal data formats. Therefore& it would return internal obect IDs and present data as heJ bytes. !dditionally& the9I5E8P!10E and ,O10958+EE5T functions cannot be used without a dictionary. What is the Benefit and draw #ac' of Continuous %ining?
The continuous mining option is useful if you are mining in the same instance that is generating the redo logs. @hen you plan to use the continuous mining option& you only need to specify one archived redo log before starting 1og9iner. Then& when you start 1og9iner specify theDB%3G1-2%*.C-*TI*!-!3G%I*0 option& which directs 1og9iner to automatically add and mine subseuent archived redo logs and also the online catalog. ,ontinuous inin is not available in eal Application ,luster*
What is 1og%iner and its Benefit?
1og9iner is a recovery utility. ?ou can use it to recover the data from oracle redo log and archive log file . The Oracle 1og9iner utility enables you to uery redo logs through a <1 interface. edo logs contain information about the history of activity on a database. Benefit of 1og%iner? ). inpointing when a logical corruption to a database = suppose when a row is accidentally deleted then log9iner helps to recover the database eJact time based and changed based recovery. 3. erform table specific undo operation to return the table to its original state. 1og9iner reconstruct the <1 statement in reverse order from which they are eJecuted. G. It helps in performance tuning and capacit" planning. ?ou can determine which table gets the most update and insert. That information provides a historical perspective on disk access statistics& which can be used for tuning purpose. 2. Performing "ost auditing = 1og9iner is used to track any D91 and DD1 performed on database in the order they were eJecuted. What is -racle Data2uard? Oracle DataKuard is a tools that provides data protection and ensures disaster recovery for enterprise data. It provides comprehensive set of services that create& maintain& manage& and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruption. Dataguard maintains these standsby databases as transitionally consistent copies of the production database. Then& if the production database becomes failure Data Kuard can switch any standby database to the production role& minimiBing the downtime associated with the outage. Data Kuard can be used with traditional backup&
restoration& and cluster techniues to provide a high level of data protection and data availability. What is 3tand#& Data#ases ! standby database is a transitionally consistent copy of the primary database. 0sing a backup copy of the primary database& you can create up to stand#& data#ases and incorporate them in a Data Kuard configuration. Once created& Data Kuard automatically maintains each standby database by transmitting redo data from the primary database and then applying the redo to the standby database. imilar to a primary database& a standby database can be either a single"instance Oracle database or an Oracle eal !pplication ,lusters database. ! standby database can be either a "h&sical stand#& database or a logical stand#& database: Difference #etween Ph&sical stand#& 1ogical stand#& data#ases
+rovides a physically identical copy of the primary database on a block"for"block basis. The database schema& including indeJes& is the same. ! physical standby database is kept synchroniBed with the primary database& though edo A""l&& which recovers the redo data& received from the primary database and applies the redo to the physical standby database. 1ogical tandby database contains the same logical information as the production database& although the physical organiBation and structure of the data can be different. The logical standby database is kept synchroniBed with the primary database though 3Q1 A""l&& which transforms the data in the redo received from the primary database into <1 statements and then eJecuting the <1 statements on the standby database. If &ou are going to setu" stand#& data#ase what will #e &our Choice 1ogical or Ph&sical?
@e need to keep the "h&sical stand#& database in X recover& modeY in order to apply the received archive logs from the primary database. @e can open Xphysical stand by database to Xread onlyY and make it available to the applications users (Only select is allowed during this period%. Once the database is opened in Xead onlyY mode then we can not apply redo logs received from primary database. @e do not see such issues with logical stand#& database. @e can open up the database in normal mode and make it available to the users. !t the same time& we can apply archived logs received from primary database. If the primary database needed to support pretty large user community for the O1T+ system and pretty large Xeporting KroupY then better to use logical stand#& as primary database instead of physical database. What are the re=uirements needed #efore "re"aring stand#& data#ase? O !rchitecture of primary database secondary database should be same. • •
The version of secondary database must be the same as primary database.
•
The +rimary database must run in !rchivelog mode.
•
euire the same hardware architecture on the primary and all standby site. Does not reuire the same O version and release on the primary and secondary site.
•
Each +rimary and secondary database must have its own database.
•
What are UFailoverV and U3witchoverV in case of dataguard?
-ailover is the operation of bringing one of the standby databases online as the new primary database when failure occurs on the primary database and there is no possibility of recover primary database in a timely manner. The switchover is a situation to handle planned maintenance on the primary database. The main difference between switchover operation and failover operation is that switchover is performed when primary database is still available or it does not reuire a flash back or re"installation of the original primary database. This allows the original primary database to the role of standby database almost immediately. !s a result schedule maintenance can performed more easily and freuently. When &ou use W,00 clause and when &ou use ,A/I*2 clause? C!PI5K clause is used when you want to specify a condition for a group function and it is written after KO0+ '? clause The @CEE clause is used when you want to specify a condition for columns& single row functions eJcept group functions and it is written before KO0+ '? clause if it is used.
What is a cursor and difference #etween an im"licit R an e8"licit cursor?
! cursor is a +1<1 block used to fetch more than one row in a +l
I would create a teJt based backup control file& stipulating where on disk all the data files where and then issue the recover command with the using backup control file clause. A ta#le is classified as a "arent ta#le and &ou want to dro" and re(create it. ,ow would &ou do this without affecting the children ta#les?
Disable the foreign key constraint to the parent& drop the table& re"create the table& and enable the foreign key constraint. ,ow to !nregister data#ase from man catalog -irst we start up 9!5 with a connection to the catalog and the target& making a note of the D'ID in the banner: ,:[6rman catalogFrmanrmanshaan targetFC9passwordorclG connected to target database: @3A) (D'IDF$)23)N2% connected to recovery catalog database 5ote the D'ID from here. 5eJt we list and delete any backupset recorded in the repository: 9!56 1IT '!,A0+ 099!?= 9!56 DE1ETE '!,A0+ DEPI,E T?+E 'T= 9!56 DE1ETE '!,A0+ DEPI,E T?+E DIA= 5eJt we connect to the 9!5 catalog owner using <1L+lus and issue the following statement: <16 ,O55E,T rmanrmanshaan <16 E1E,T db8key& db8id -O9 db @CEE db8id F )24N23)*)2= D'8AE? D'8ID """"""""""
"""""""""" )
$)23)N2
The resulting key and id can then be used to unregister the database: <16 ESE,0TE dbms8rcvcat.unregisterdatabase()& $)23)N2%= +1<1 procedure successfully completed.
y database >as terminated >6ile in -,?. 3 do need to recoer@
If a database was terminated while one of its tablespaces was in BACKUP MODE (ALE! ABLE"PACE #$% BE&I' BACKUP)* it will tell $o+ that media reco,er$ is re-+ired when $o+ tr$ to restart the database. he DBA is then re-+ired to reco,er the database and appl$ all archi,ed lo/s to the database. 0owe,er* from Oracle 1.2* one can simpl$ ta3e the indi,id+al datafiles o+t of bac3+p mode and restart the database. "4L5 ALTER DATABASE DATAFILE ‘C:\PATH\FILENAME’ END BACKUP; One can select from 67BACKUP to see which datafiles are in bac3+p mode 8rom Oracle9i onwards* the followin/ command can be +sed to ta3e all of the datafiles o+t of hotbac3+p mode: "4L5ALE! DAABA"E E'D BACKUP Note: This command must be issued when the database is mounted, but not yet opened.
Does Oracle r!"e "o #a"a $!les !% &e'!%()o" &ac*+, -o#e. ;hen a tablespace is in bac3+p mode* Oracle will stop +pdatin/ its file headers* b+t will contin+e to write to the data files. ;hen in bac3+p mode* Oracle will write complete chan/ed bloc3s to the redo lo/ files. Beca+se of this* increased lo/ acti,it$ and archi,in/ d+rin/ on
D!$$ere%ce Co%s!s"e%" a%# I%co%s!s"e%" Bac*+, he bac3+p ta3en in sh+tdown state or in same point in time are referred to as consistent. Unli3e an inconsistent bac3+p* a consistent whole database bac3+p does not re-+ire reco,er$ after it is restored* here all header of datafile belon/s to writable tablespace ha,e the same "C'. hese datafile donot ha,e an$ chan/e past this chec3 point "C'. he "C' of datafile header matches e#actl$ controlfile chec3point. An inconsistent bac3+p is a bac3+p of one or more database files that $o+ ma3e while the database is open or after the database has sh+t down abnormall$. his means that the files in the bac3+p contain data ta3en from different points in time. his can occ+r beca+se the datafiles are bein/ modified as bac3+ps are bein/ ta3en. 'ot an$ of the abo,e mentioned properties are e#ist here. A reco,er$ (Appl$in/ all the archi,e and online redo lo/s) is needed in order to ma3e the bac3+p consistent.
D!$$ere%ce &e"ee% res"or!%' a%# reco/er!%'. !estorin/ in,ol,es cop$in/ bac3+p files from secondar$ stora/e (bac3+p media) to dis3. his can be done to replace dama/ed files or to cop$=mo,e a database to a new location. !eco,er$ is the process of appl$in/ redo lo/s to the database to r oll it forward. One can roll< forward +ntil a specific point
D!$$ere%ce &e"ee% Co-,le"e a%# I%co-,le"e Reco/er0. Complete reco,er$ in,ol,es +sin/ redo data or incremental bac3+ps combined with a bac3+p of a database* tablespace* or datafile to +pdate it to the most c+rrent point in time. It is called complete beca+se Oracle applies all of the redo chan/es contained in the archi,ed and online lo/s to the bac3+p. $picall$* $o+ perform complete media reco,er$ after a media fail+re
dama/es datafiles or the control file. Incomplete reco,er$* or pointo+ can perform the followin/ t$pes of media reco,er$. ime based !eco,er$* Cancel based !eco,er$* Chan/e based !eco,er$* Lo/ se-+ence !eco,er$
1)a" )a,,e%s )e% e o,e% ")e #a"a&ase !") Rese"lo's o,"!o% a$"er !%co-,le"e reco/er0. he !E"ELO&" operation creates a new incarnation of the database?in other words* a database with a new stream of lo/ se-+ence n+mbers startin/ with lo/ se-+ence @. Before +sin/ the OPE' !E"ELO&" command to open the database in read=write mode after an incomplete reco,er$* it is a /ood idea to first open the database in read
D!$$ere%ce &e"ee% o%l!%e a%# o$$l!%e &ac*+,s. A hot (or on
1)a" !s ")e #!$$ere%ce &e"ee% 2!es a%# Ma"er!al!3e# 2!es !% Oracle. 6iews e,al+ate the data in the tables +nderl$in/ the ,iew definition at the time the ,iew is -+eried. It is a lo/ical ,iew of $o+r tables* with no data stored an$where else. he +pside of a ,iew is that it will alwa$s ret+rn the latest data to $o+. he downside of a ,iew is that its performance depends on how /ood a select statement the ,iew is based on. If the select statement +sed b$ the ,iew oins man$ tables* or +ses oins base d on non
1)a" )a,,e%s )e% 0o+ se" CONTROL4FILE4RECORD4KEEP4TIME "o 5 'e,er set CO'!OL8ILE!ECO!DKEEPIME to . If $o+ do* then bac3+p records ma$ be
o,erwritten in the control file before !MA' is able to add them to the catalo/. As we 3now that he CO'!OL8ILE!ECO!DKEEPIME initiali%ation parameter determines the minim+m n+mber of da$s that records are retained in the co ntrol file before the$ are candidates for bein/ o,erwritten.
Ho "o $!%# ")e las" re$res) o$ 0o+r #a"a&ase 6)e% ")e reco/er0 !") rese"lo's ,er$or-e#7. If the cloned database has been opened with !E"ELO&" option* $o+ can tr$ chec3in/ o+t 67DAABA"E.!E"ELO&"IME. if the 67DAABA"E.C!EAED is not e-+al to 67DAABA"E.!E"ELO&"IME...there is a possibilit$ that it mi/ht be opened with resetlo/s option. I dont ha,e the re-+ired set +p to chec3 and confirm this m$self....b+t this is somethin/ $o+ can /et it a shot.
Co--a%# "o $!%# $!les crea"e# a #a0 &e$ore find .
I%!"!all0 Flas)&ac* Da"a&ase as e%a&le# &+" %o"!ce# Flas)&ac* as #!sa&le# a+"o-a"!call0 lo%' "!-e a'o8 1)a" !s ")e Iss+e. !eason: It co+ld be beca+se the flashbac3 area @J Once 8lashbac3 Area become @J f+ll then oracle will lo/ in Alert that 8lashbac3 will be disabled and it will a+tomaticall$ t+rn off 8lash Bac3 witho+t +ser inter,ention.
Ho ca% I c)ec* !$ ")ere !s a%0")!%' roll!%' &ac*. It depends on how $o+ 3illed the process. If $o+ did and alter s$stem 3ill session $o+ sho+ld be able to loo3 at the +sed+bl3 bloc3 in ,7transaciton to /et an estimate for the rollbac3 bein/ done. If $o+ 3illed to ser,er process in the O" and pmon is reco,erin/ the transaction $o+ can loo3 at 678A""A!!A'"ACIO'" ,iew to /et the estimate
Ho #o 0o+ see )o -a%0 !%s"a%ces are r+%%!%'. In Lin+#* Uni# the command: ps
1)!c) !s -ore e$$!c!e%" I%cre-e%"al Bac*+,s +s!%' RMAN or I%cre-e%"al E9,or". !man
T)e c+rre%" lo'$!le 'e"s #a-a'e#8 1)a" 0o+ ca% #o %o. Once c+rrent redolo/ file is dama/ed* instance is aborted and it needs reco,er$ +pto +ndama/ed part. Onl$ +ndama/ed part can be reco,ered. 0ere DBA m+st appl$ time based reco,er$* means it can be a point in time or specified b$ "C'. It leads to incomplete reco,er$
1)ere s)o+l# ")e "+%!%' e$$or" &e #!rec"e#. Consider the followin/ areas for t+nin/ in order to increase performance of DB Application Tuning: E#perience showed that appro#imatel$ J of all Oracle s$stem performance problems are resol,ed b$ codin/ optimal "4L. Also consider proper sched+lin/ of batch tas3s after pea3 wor3in/ ho+rs. Memory Tuning: Properl$ si%e $o+r database b+ffers (shared pool* b+ffer cache* lo/ b+ffer* etc) b$ loo3in/ at $o+r b+ffer hit ratios. Pin lar/e obects into memor$ to pre,ent fre-+ent reloads. Disk I/ Tuning: Database files needs to be properl$ si%ed and placed to pro,ide ma#im+m dis3 s+bs$stem thro+/hp+t. Also loo3 for fre-+ent dis3 sorts* f+ll table scans* missin/ inde#es* row chainin/*
data fra/mentation* etc !liminate Database "ontention: "t+d$ database loc3s* latches and wait e,ents caref+ll$ and eliminate where possible. Tune the perating #ystem: Monitor and t+ne operatin/ s$stem CPU* I=O and memor$ +tili%ation. 8or more information* read the related Oracle 8A4 dealin/ with $o+r spec ific operatin/ s$stem.
1)a" are ")e co--o% I-,or"( E9,or" ,ro&le-s. ORA5555: Uni-+e constraint (...) ,iolated < >o+ are importin/ d+plicate rows. Use I&'O!E'O to s3ip tables that alread$ e#ist (imp will /i,e an error if the obect is re
ORA5<<<: "napshot too old < As3 $o+r +sers to "OP wor3in/ while $o+ are e#portin/ or +se parameter CO'"I"E''O
ORA5<=>: 8ailed to e#tend rollbac3 se/ment < Create bi//er rollbac3 se/ments or set parameter COMMI> while importin/
IMP555<: "tatement failed ... obect alread$ e#ists... < Use the I&'O!E> import parameter to i/nore these errors* b+t be caref+l as $o+ mi/ht end +p with d+plicate rows.
B0 -!s"a*e a +se #ro, or "r+%ca"e a Ta&le ")e% )a" !s ")e &es" -e")o# "o reco/er !". here are se,eral methods possibl$ thro+/h !MA' s+ch as: !estore and reco,er the primar$ database to a point in time before the drop. his is an e#treme meas+re for one table as the entire database /oes bac3 in time. !estore and reco,er the tablespace to a point in time before the drop. his is a better option* b+t a/ain* it ta3es the entire tablespace bac3 in time. !estore and reco,er a s+bset of the database as a DUMM> database to e#port the table data and import it into the primar$ database. his is the best option as onl$ the dro pped table /oes bac3 in time to before the drop.
Ho "o $!%# r+%%!%' ?o&s !% oracle #a"a&ase select sid* ob*instance from dbaobsr+nnin/ select sid* serialN*machine* stat+s* os+ser*+sername from ,7session where +sername'ULL <
Ho "o $!%# lo%' r+%%!%' ?o&s !% oracle #a"a&ase select +sername*tochar(starttime* hh2Q:mi:ss dd=mm=$$) started* timeremainin/ remainin/* messa/e from ,7sessionlon/ops where timeremainin/ order b$ timeremainin/ desc
Lo'!% !")o+" ,assor# *%ole#'e his is not the /en+ine approach consider it as a practice. "4L5 CO''EC = as s$sdba Connected. "4L5 "ELEC password 8!OM dba+sers ;0E!E +sername"CO PA"";O!D <<<<<<<<<<<<<<< <<<<<<<<<<<<<<< 89QQQCRQQ2BS1 "4L5 ALE! U"E! scott IDE'I8IED B> an$thin/ User altered. "4L5 CO''EC scott=an$thin/
Connected. OK* were in. Lets -+ic3l$ chan/e the password bac3 before an$bod$ notices. "4L5 ALE! U"E! scott IDE'I8IED B> 6ALUE" 89QQQCRQQ2BS1 User altered.
1)!le a,,l0!%' ")e CPU Pa"c) )0 e %ee# "o +,#a"e ")e Oracle I%/e%"or0 T Beca+se when $o+ appl$ the CPU it +pdates the oracle binaries.
D!$$ere%ce &e"ee% loc*s a%# la"c)es Loc3s are +sed to protect the data or reso+rces from the sim+ltaneo+s +se of them b$ m+ltiple sessions which mi/ht set them in inconsistent state. Loc3s are e#ternal mechanism* means +ser can also set loc3s on obects b$ +sin/ ,ario+s oracle statements. Latches are for the same p+rpose b+t wor3s at internal le,el. Latches are +sed to Protect and control access to internal data str+ct+res li3e ,ario+s "&A b+ffers. he$ are handled and maintained b$ oracle and we canHt access or set it.
Se""!%' ")e a+#!"4"ra!l ,ara-e"er !% ")e #a"a&ase "o @#& !" 'e%era"es lo" o$ recor#s !% s0s8a+# "a&le8 Ca% 0o+ s+''es" a%0 -e")o# "o o/erco-e ")!s !ss+e. @. ;hen $o+ set a+dit it does a+dit for e,er$ sin/le acti,it$ on the database. "o it ma$ lead into performance problem. >o+ ha,e to disable e,er$ sin/le a+dit(5 noa+dit) before or after $o+ set the parameter and then enable one b$ one based on the re-+irement. 2. >o+ sho+ld monitor the /rowth of s$s.a+d7 and archi,e it properl$ or maintain the space.
Ho "o c)a%'e ")e "o,%sl o$ A1R S%a,s)o" !% 5' Select * from DBA_HIST_WR_CONTROL 1898043910 +00 010000!000000 +01 000000!000000 D"#A$LT e%ec DB&S_WOR'LOAD_R"(OSITOR)!&ODI#)_SNA(SHOT_S"TTINSto,-./l 302 Select * from DBA_HIST_WR_CONTROL 1898043910 +00 010000!000000 +01 000000!000000 30
Ho "o #e"ec" )o’s ca+s!%' e9cess!/e re#o 'e%era"!o% S"L"CT S1!SID S1!S"RIAL_N$& S1!$S"R_NA&" S1!(RORA& T1!$S"D_$BL' T1!$S"D_$R"C #RO& 56S"SSION S1 56TRANSACTION T1 WH"R" S1!TADDR T1!ADDR ORD"R B) 7 D"SC D"SC 1 3 4
rac3in/ +ndo /eneration b$ all session S"L"CT S1!SID S1!$S"R_NA&" R1!NA&" T1!START_TI&" T1!$S"D_$BL' T1!$S"D_$R"C #RO& 56S"SSION S1 56TRANSACTION T1 56ROLLNA&" R1 WH"R" T1!ADDR S1!TADDR AND R1!$SN T1!:ID$SN
Or $o+ can collect "tatistics from 67"E""A to A;!
Ho #o 0o+ re-o/e a% SPFILE ,ara-e"er 6%o" c)a%'e ")e /al+e o$ &+" ac"+all0
,+r'e !" o+"r!')"7. Use VALE! ">"EM !E"E ...V (8or database ,ersions 9i and +p) "$nta#: ALT"R S)ST"& R"S"T (ARA&"T"R SID;SID<*; ALT"R S)ST"& R"S"T =_TRAC"_#IL"S_($BLIC= SCO("S(#IL" SID;*; NOT" T>e =SID;SID<*;= ?r@me-t . R"$IR"D
Ca% 0o+ +se RMAN "o reco/er RMAN. >es* $o+ can
1)!c) s!"+a"!o% ‘E9!s"’ co%#!"!o% !s &e""er ")a% ‘IN’ If the res+ltant of s+b -+er$ is small then I'H is t$picall$ more appropriate where as res+ltant of s+b -+er$ is bi/=lar/e=lon/ then EWI"H is more appropriate. he E#istH alwa$s res+lts f+ll scan of table where as first -+er$ can ma3e +se of inde# on able.
Is Oracle reall0 +!c*er o% 1!%#os ")a% Solar!s. I fo+nd in m$ e#perience that >es* windows perform better on comparable hardware +s t abo+t an$ U'IW bo#. I am wor3in/ on ;indows b+t once I installed "olaris tr$in/ to test. I fo+nd the windows installations alwa$s o+tperformed the "olaris ones both on initial loadin/ the pool cache and s+bse-+ent r+ns. he test pac3a/e is rather lar/e (XY lines)* which is +sed in a form to displa$ c+stomer details. On "olaris I was t$picall$ /ettin/ an initial ret+rn time of X seconds and on windows* t$picall$* @ second. E,en s+bse-+ent r+ns (i.e. cached) the windows o+tperformed "olaris. he parameter si%es for the "&A were appro#. the same and the file s$stems are the con,entional method. In both cases the dis3 confi/+ration is local.
1)a" !s D!$$ere%ce &e"ee% DB%a-e a%# !%s"a%ce4%a-e. A database is a set of files (data* redo* ctl and so on) where as An instance is a set of processes ("MO'* PMO'* DB;!* etc) and a shared memor$ s e/ment ("&A). A database ma$ be mo+nted and opened b$ man$ I'"A'CE" (Parallel "er,er) conc+rrentl$. An instance ma$ mo+nt and open A'> database << howe,er it ma$ onl$ open a sin/le database at an$ time. here for $o+ need +ni-+e (for the set of files).
Does DBCA crea"e !%s"a%ce )!le crea"!%' #a"a&ase. DBCA does not create instance. It create database (set of files). he instance is onl$ feelin/s do a sh+tdown and /oodb$e instance and on windows it re/isters the necessar$ ser,ices that can be +sed to start an instance when $o+ want.
Is ")ere a%0 a0 "o crea"e #a"a&ase !")o+" DBCA. >es* $o+ can +sed oradim directl$
1)a"s ")e #!$$ere%ce &e"ee% co%%ec"!o%s sess!o%s a%# ,rocesses. A connection is a ph$sical circ+it between $o+ and the database. A connection mi/ht be one of man$ t$pes << most pop+lar be/in DEDICAED ser,er and "0A!ED ser,er. Zero* one or more sessions ma$ be established o,er a /i,en connection to the database as show abo,e with s-lpl+s. A process will be +sed b$ a session to e#ec+te statements. "ometimes there is a one to one relationship between CO''ECIO'<5"E""IO'<5P!OCE"" (e/: a normal dedicated ser,er connection). "ometimes there is a one to man$ from connection to sessions (e/: li3e
a+totrace* one connection* two sessions* one process). A process does not ha,e to be dedicated to a specific connection or session howe,er* for e#ample when +sin/ shared ser,er (M")* $o+r "E""IO' will /rab a process from a pool of processes in order to e#ec+te a statement. ;hen the call is o,er* that process is released bac3 to the pool of processes. "4L5select +sername from ,7session where +sername is not n+ll $o+ can see one session* me SL.elect .er-?me ,ro@r?m from E6,roce..
$o+ can see all of the bac3/ro+nds and m$ dedicated ser,er... A+totrace for statistics +ses A'O0E! session so it can -+er$ +p the stats for $o+r CU!!E' session witho+t impactin/ the "A" for that session SL.elect .er-?me from E6.e..o- F>ere .er-?me . -ot -ll
now $o+ can see two session b+t... SL.elect .er-?me ,ro@r?m from E6,roce..
"ame @Q processes...
1)a" a&o+" Fra'-e%"a"!o% s!"+a"!o% 6LMT7 !% oracle ! a%# +,. 8ra/mentation is that if $o+ ha,e man$ [small\ holes (re/ions of conti/+o+s free space) that are too small to be the ne#t e#tent of an$ obect. hese holes of free space res+lted from droppin/ some obect (or tr+ncatin/ them) and the res+ltin/ free space cannot be +sed b$ an$ other obect in that tablespace. his is a direct res+lt of +sin/ pctincrease that is not %ero and ha,in/ man$ weird si%ed e#tents (e,er$ e#tents is +ni-+e si%e and shape). In oracle i and abo,e we all are +sin/ locall$ mana/ed tablespace. hese wo+ld +se either +niform si%in/ or o+r a+tomatic allocation scheme. In either case it is almost impossible to /et into a sit+ation where $o+ ha,e +n+sable free space. o see if $o+ s+ffer from fra/mentation $o+ can -+er$ from DBA8!EE"PACE (best to do an alter tablespace to ens+re all conti/+o+s made into @ bi/ free re/ion). >o+ wo+ld loo3 an$ free space that is smaller then the smallest ne#t e#tent si%e for an$ obect in that tablespace. Chec3 with below -+er$: Select * from G?_free_.,?ce F>ere t?le.,?ce_-?me ;T; ?-G te. J .elect m--e%t_e%te-t2 from G?_.e@me-t. F>ere t?le.,?ce_-?me ;T;2 orGer locK_G
Is ")ere a a0 e ca% $l+s) o+" a *%o% #a"a se" $ro- ")e #a"a&ase &+$$er cac)e. 'o $o+ donHt* in real life the cache wo+ld ne,er be empt$. It is tr+e that @/ introd+ce an alter s$stem fl+sh b+ffercache* b+t it is not reall$ worthwhile. 0a,in/ empt$ b+ffer cache is fa3e* if no more so than what $o+ are c+rrentl$ doin/.
1)a" o+l# &e ")e &es" a,,roac) "o &e%c)-ar* ")e res,o%se "!-e $or a ,ar"!c+lar +er0. r+n -+er$ -@ o,er and o,er (with man$ different inp+ts) r+n -+er$ -2 o,er and o,er (with man$ different inp+ts) discard first co+ple of obser,ations* and last co+ple +se the obser,ations in the middle
1)a" !s #!$$ere%ce &e"ee% C)ar a%# 2arc)ar> a%# )!c) !s &e""er a,,roac). A C0A! datat$pe and 6A!C0A!2 datat$pe are stored identicall$ (e/: the word ;O!D stored in a C0A!(Q) and a ,archar2(Q) cons+me e#actl$ the same amo+nt of space on dis3* both ha,e leadin/ b$te co+nts). he difference between a C0A! and a 6A!C0A! is that a C0A!(n) will AL;A>" be ' b$tes lon/* it will be blan3 padded +pon insert to ens+re this. A ,archar2(n) on the other hand will be @ to ' b$tes lon/* it will 'O be blan3 padded. Usin/ a C0A! on a ,ar$in/ width field can be a pain d+e to the search semantics o f C0A!. Consider the followin/ e#amples: SL cre?te t?le t % c>?r102 2 T?le cre?teG! SL -.ert -to t E?le. ;Hello; 2 1 roF cre?teG! SL .elect * from t F>ere % ;Hello; : Hello SL E?r?le E?rc>?r72 SL e%ec ;Hello; (LMSL ,roceGre .cce..fll com,leteG! SL .elect * from t F>ere % -o roF. .electeG SL .elect * from t F>ere % r,?G102 : Hello
'otice how when doin/ the search with a ,archar2 ,ariable (almost e,er$ tool in the world +ses this t$pe)* we ha,e to rpad() it to /et a hit. If the field is in fact AL;A>" @ b$tes lon/* +sin/ a C0A! will not h+rt << 0O;E6E!* it will not help either.
R-a% ala0s s)os #a"e !% DDMONGG $or-a"8 Ho "o se" #a"e $or-a" "o M(DD(GGGG HH>:MI:SS !% r-a% . >o+ can +st set the 'L"DAE8O!MA before /oin/ into !MA':
I% R-a% l!s" &ac*+, )o #o ! 'e" "!-e col+-% ")a" s)os -e #a"e a%# "!-e !%cl+#!%' seco%#s as 'e%erall0 !" !s s)o!%' o%l0 #a"e8 Before connectin/ the rman tar/et set the date format on command prompt: e%,ort NLS_DAT"_#OR&ATGGmo- >>4m.. < Lin+# Set NLS_DAT"_#OR&ATGGmo- >>4m.. < windows
then tr$ to connect rman tar/et rm?- t?r@et ..Mor?lceorcl3 c?t?lo@ rm?-Mrm?-.>??rm?- l.t ?cK,.et 10473
1)0 %o" +se O(S &ac*+,s !%s"ea# o$ RMAN. here is nothin/ wron/ with doin/ +st O" bac3+ps. O" bac3+ps are +st as ,alid as !MA' bac3+ps. !MA' is a /reat tool b+t it is not the onl$ wa$ to do it. Man$ people still prefer +sin/ a scriptin/ tool of there choice s+ch as perl or 3sh to do this. !MA' is /ood if $o+ ha,e lots of databases. he catalo/ it +ses remembers lots of details for $o+. >o+ dont ha,e as m+ch to thin3 abo+t. !MA' is /ood if $o+ do not ha,e /ood Vpaper wor3V s3ills in place. Usin/ O" bac3+ps* it is more or less +pto $o+ to remember where the$ are* what the$ are called and so on. >o+ ha,e to do all of the boo3 3eepin/ !MA' wo+ld do. !MA' pro,ides incremental bac3+ps* somethin/ $o+ cannot /et witho+t !MA'. !MA' pro,ides tablespace point in time reco,er$. >o+ can do this witho+t !MA' b+t $o+ ha,e to do it b$ $o+rself and it can be rather con,ol+ted. !MA' is more inte/rated with OEM. If $o+ do O" bac3+ps* $o+ll ha,e to do e,er$thin/ $o+rself. ;ith !MA' $o+ ma$ ha,e less scriptin/ to de,elop* test and maintain.
RMAN !$ ")e ca"alo'(co%"rol$!le are #a-a'e#. )a" !s ")e %e9" s"e,. If $o+ lose rman* $o+ reb+ild from the controlfiles of the bac3ed +p databases b+t* $o+ sho+ld not lose the rman catalo/ +sin/ proper techni-+es of bac3+p itself.
Ho "o s!"c) &e"ee% Noarc)!/elo' a%# arc)!/elo' !% oracle 5' co--ect =M ?. ..G?= ?lter ..tem .et lo@_?rc>Ee_.t?rttre .co,e.,fle ?lter ..tem .et lo@_?rc>Ee_Ge.t;!!!!!!; .co,e.,fle .>tGoF- mmeG?te .t?rt, mo-t ?lter G?t??.e ?rc>Eelo@ ?lter G?t??.e o,e- co--ect M ?-G co--ect =M ?. ..G?= .>tGoF- mmeG?te .t?rt, mo-t ?lter G?t??.e -o?rc>Eelo@ ?lter G?t??.e o,e- co--ect M
Ho "o U,#a"e -!ll!o%s or recor#s !% a "a&le. If we had to +pdate millions of records I wo+ld probabl$ opt to 'O +pdate. I wo+ld more li3el$ do: C!EAE ABLE newtable as select do the +pdate VhereV5 from oldtable inde# newtable /rant on new table add constraints on newtable
etc on newtable drop table oldtable rename newtable to oldtable >o+ can do that +sin/ parallel -+er$* with nolo//in/ on most operations /eneratin/ ,er$ little redo and no +ndo at all in a fraction of the time it wo+ld ta3e to +pdate the data. "4L5create table newemp as select empno* LO;E!(ename) ename* OB* M&!* 0I!EDAE* "AL* COMM* DEP'O from e mp "4L5drop table emp "4L5rename newemp to emp
Ho "o co%/er" #a"a&ase ser/er s0s#a"e "o MT #a"e. Select ..G?te ..G?te+ ..trt_off.etGtmeo-e2112<
A<<..trt_off.et DBTI&"ON" 2 72<
1)a" !s ")e #!$$ere%ce &e"ee% "o &ac* +, ")e c+rre%" co%"rol $!le a%# "o &ac*+, +, co%"rol $!le co,0. If $o+ bac3+p [c+rrent control file\ $o+ bac3+p control file which is c+rrentl$ open b$ an instance where as If $o+ bac3+p [controlfile file cop$V $o+ bac3+p the cop$ of control file which is created either with "6!M&!L command Valter s$stem bac3+p controlfile to ..V or with !MA' command Vcop$ c+rrent controlfile to ...V. In the other words* the control file cop$ is not c+rrent controlfile bac3+p c+rrent controlfile creates a BACKUP"E containin/ controlfile. >o+ dont ha,e to /i,e the 8ILE'AME where as bac3+p controlfile cop$ filename5 creates a BACKUP"E from a cop$ of controlfile. >o+ ha,e to /i,e the 8ILE'AME.
Ho -+c) o$ o/er)ea# !% r+%%!%' BACKUP 2ALIDATE DATABASE a%# RESTORE 2ALIDATE DATABASE co--a%#s "o c)ec* $or &loc* corr+,"!o%s +s!%' RMAN. Ca% I r+% ")ese co--a%#s a%0"!-e. Bac3+p ,alidate wor3s a/ainst the bac3+ps not a/ainst the li,e database so no impact on the li,e database* same for restore ,alidate the$ do not impact the real thin/ (it is readin/ the files there onl$).
Is ")ere a a0 "o $orce r-a% "o +se ")ese o&sole"e &ac*+,s or o%ce !" !s -ar*e# o&sole"e. As per m$ +nderstandin/ it is +st a report* the$ are still there +ntil $o+ delete them.
Ca% I +se ")e sa-e s%a,s)o" co%"rol$!le "o &ac*+, -+l"!,le #a"a&ases6o%e a$"er a%o")er7 r+%%!%' o% ")e sa-e ser/er. his file is onl$ +se temporaril$ li3e a scratch file. Onl$ one rman session can access the snapshot controlfile at an$ time so this wo+ld tend to seriali%e $o+r bac3+ps if $o+ do that.
1)0 #oes %o" oracle *ee, RMAN !%$o a$"er recrea"!%' ")e co%"rol$!le. Creatin/ the new controlfile from scratch how do $o+ e#pect the create controlfile to Vma3e +pV the missin/ dataT that wo+ld be li3e sa$in/ similarl$ we ha,e drop and recreated m$ table and now it is empt$ similarl$ here recreatin/ from the scratch means the contents there will be nat+rall$ will be /one. Use the rman catalo/ to deal this sit+ation. It is +st a s+//estion.
1)a" !s ")e a#/a%"a'e o$ +s!%' PIPE !% r-a% &ac*+,s. I% )a" c!rc+-s"a%ces o%e o+l# +se PIPE "o &ac*+, a%# res"ore. It lets Rrd parties (an$one reall$) b+ild an alternati,e interface to !MA' as it permits an$one that can connect to an Oracle instance to control !MA' pro/rammaticall$.
Ho To "+r% De&+' Fea"+re o% !% r-a%. r+n F allocate channel c@ t$pe dis3 deb+/ on G rman5list bac3+p of database now $o+ will see a o+tp+t >o+ can alwa$s t+rn deb+/ off b$ iss+in/ rman5deb+/ off
Ass+-!%' I )a/e a JFULLJ &ac*+, o$ +sers58#&$ co%"a!%!%' e-,lo0ees "a&le ")a" co%"a!%s 555 &loc*s o$ #a"a8 I$ I "r+%ca"e# e-,lo0ees "a&le a%# ")e% a% !%cre-e%"al le/el &ac*+, o$ +ser’s "a&les,ace !s "a*e% !ll RMAN !%cl+#e 555 &loc*s ")a" o%ce co%"a!%e# #a"a !% ")e !%cre-e%"al &ac*+,. he bloc3s were not written to the onl$ chan/es made b$ the tr+ncate was to the data dictionar$ (or file header) so no* it wont see them as chan/ed bloc3s since the$ were not chan/ed.
1)ere s)o+l# ")e ca"alo' &e crea"e#. he reco,er$ catalo/ to be +sed b$ !man sho+ld be cr eated in a separate database other than the tar/et database. he reason is that the tar/et database will be sh+tdown while datafiles are restored.
Ho -a%0 "!-es #oes oracle as* &e$ore #ro,,!%' a ca"alo'. he defa+lt is two times one for the act+al command* the other for confirmation.
1)a" are ")e /ar!o+s re,or"s a/a!la&le !") RMAN. rman5list bac3+p rman5 list archi,e
1)a" !s ")e +se o$ s%a,s)o" co%"rol$!le !% "er-s o$ RMAN &ac*+,. !man +ses the snapshot controlfile as a wa$ to /et a read consistent cop$ of the controlfile* it +ses this to do thin/s li3e !E">'C the catalo/ (else the controlfile is a mo,in/ tar/etH* constantl$ chan/in/ and !man wo+ld /et bloc3ed and bloc3 the database)
Ca% RMAN r!"e "o #!s* a%# "a,e Parallel. Is !" ,oss!&le. !man c+rrentl$ wont do tape directl$* $o+ need a media mana/er for that* re/ardin/ dis3 and tape parallel not as far as I 3now* $o+ wo+ld r+n two bac3+ps separatel$ (not s+re). Ma$ be tr$in/ to maintain d+plicate li3e that co+ld /et the desired.
1)a" !s ")e #!$$ere%ce &e"ee% DELETE INPUT a%# DELETE ALL co--a%# !% &ac*+,. &enerall$ spea3in/ LO&A!C0I6EDE"n points to two dis3 dri,e locations where we archi,e the files* when a command is iss+ed thro+/h rman to bac3+p archi,elo/s it +ses one of the location to bac3+p the data. ;hen we specif$ delete inp+t the location which was bac3ed +p will /et deleted* if we specif$ delete all (all lo'4arc)!/e4#es"4%) will /et deleted. DELEE all applies onl$ to archi,ed lo/s. delete e#pired archi,elo/ all
Is !" ,oss!&le "o res"ore a &ac*+,se" 6ac"+all0 &ac*+, ,!eces7 $ro- a #!$$ere%" loca"!o% "o )ere RMAN )as recor#e# ")e- "o &e8 ;ith 9.2 and earlier it is not possible to restore a bac 3+pset (act+all$ bac3+p pieces) from a different location to where !MA' has recorded them to be. As a wor3aro+nd $o+ wo+ld ha,e to create a lin3 +sin/ the location of where the bac3+p was ori/inall$ located. hen when restorin/* !MA' will thin3 e,er$thin/ is the same as it was. "tartin/ in @.@ it is possible to catalo/ the bac3+p pieces in their new location into the controlfile and reco,er$ catalo/. his means the$ are a,ailable for restoration b$ !MA' witho+t creatin/ the lin3.
1)a" !s #!$$ere%ce &e"ee% Re,or" o&sole"e a%# Re,or" o&sole"e or,)a%
!eport obsolete bac3+p are reported +n+sable accordin/ to the +serHs retention polic$ where as !eport obsolete orphan report the bac3+p that are +n+sable beca+se the$ belon/ to incarnation of the database that are not direct ancestor of the c+rrent incarnation.
Ho "o I%crease S!3e o$ Re#o Lo' @. Add new lo/ files (/ro+ps) with new si%e ALE! DAABA"E ADD LO&8ILE &!OUP] 2. "witch with alter s$stem switch lo/ fileH +ntil a new lo/ file /ro+p is in state c+rrent R. 'ow $o+ can delete the old lo/ file ALE! DAABA"E D!OP LO&8ILE MEMBE!]
1)a" !s ")e #!$$ere%ce &e"ee% al"er #a"a&ase reco/er a%# sl,l+s reco/er co--a%#. ALE! DAABA"E reco,er is +sef+l when $o+ as a +ser want to control the reco,er$ where as "4L^PLU" reco,er command is +sef+l when we prefer a+tomated reco,er$.
D!$$ere%ce o$ "o /!e 2Bac*+,4Se" a%# Rc4Bac*+,4Se" !% res,ec" o$ R-a% he 2Bac*+,4Se" is +sed to chec3 the bac3+p details when we are not mana/in/ !man catalo/ that is the bac3+p information is stored in controlfile where as Rc4Bac*+,4Se" is +sed when we are +sin/ catalo/ as a central repositor$ to list the bac3+p information.
Ca% I ca%cel a scr!," $ro- !%s!#e ")e scr!,". Ho I ca%c!l a selec" o% 1!%#os cl!e%". Use ctl
Ho "o F!%# ")e N+-&er o$ Oracle I%s"a%ces R+%%!%' o% 1!%#os Mac)!%e C:5net start find [Oracle"er,ice\
Ho "o crea"e a% !%!"8ora $ro- ")e s,$!le )e% ")e #a"a&ase !s #o%. 8ollow the same wa$ as $o+ are +sin/ "4L5 connect s$s=oracle as s$sdba "4L5 sh+tdown "4L5 create pfile from spfile "4L5 create spfile from pfile
1)e% 0o+ s)+"#o% ")e #a"a&ase )o #oes oracle -a!%"a!% ")e +ser sess!o% !8e8o$ s0s#&a. >o+ still ha,e $o+r dedicated ser,er ps o+ can see $o+ still ha,e $o+r dedicated ser ,er. ;hen $o+ connect as s$sdba* $o+ fire +p dedicated ser,er that is where it is.
1)a" !s ORA55>55 error. 1)a" 0o+ !ll #o !% ")a" case. A dis3 I=O fail+re was detected on readin/ the control file. Basicall$ $o+ ha,e to chec3 whether the control file is a,ailable* permissions are ri/ht on the control file* spfile=init.ora ri/ht to the ri/ht location* if all chec3s were done still $o+ are /ettin/ the error* then from the m+ltiple#ed
control file o,erla$ on the corr+pted one. Let +s sa$ $o+ ha,e three control files control@.ctl* control2.ctl and controlR.ctl and now $o+ are /ettin/ errors on controlR.ctl then +st cop$ control@.ctl o,er to controlR.ctl and $o+ sho+ld be all set. In order to iss+e ALE! DAABA"E BACKUP CO'!OL8ILE O !ACE database sho+ld be mo+nted and in o+r case it is not mo+nted then the onl$ other option a,ailable is to restore control file from bac3+p or cop$ the m+ltiple#ed control file o,er to the bad one.
1)0 #o e %ee# SCOPEBOTH cla+se. BO0 indicates that the chan/e is made in memor$ and in the ser,er parameter file. he new settin/ ta3es effect immediatel$ and persists after the database is sh+t down and started +p a/ain. If a ser,er parameter file was +sed to start +p the database* then BO0 is the defa+lt. If a parameter file was +sed to start +p the database* then MEMO!> is the defa+lt* as well as the onl$ scope $o+ can specif$.
Ho "o *%o N+-&er o$ CPUs o% Oracle Lo/in as ">"DBA "4L5show parameter cp+co+nt 'AME >PE 6ALUE cp+co+nt inte/er 2
Co+l# 0o+ ,lease "ell -e )a" are ")e ,oss!&le reaso% $or S,$!le corr+,"!o% a%# Reco/er0. It sho+ld not be corr+pt +nder normal circ+mstances* if it were* it wo+ld be a b+/ or fail+re of some component in $o+r s$stem. It co+ld be a file s$stem error or co+ld be a b+/. >o+ can easil$ reco,er howe,er from a) >o+r alert lo/ has the non
Ho 0o+ !ll c)ec* $las)&ac* !s e%a&le# or %o". "elect flashbac3on from ,7database
I% case Re/o*e CREATE TABLE Pr!/!le'e $ro- a% USER '!/!%' ORA5<>8 1)a" !s ")e !ss+e. Ho "o #o !% ")a" case. "4L5 re,o3e create table from Pa$pa$mentmaster O!A<@9X2: s$stem pri,ile/es not /ranted to PA>PA>ME'MA"E!H his is beca+se this pri,ile/e is not assi/ned to this +ser directl$ rather it was assi/ned thro+/h role [CO''EC\ If $o+ remo,e connect role from the +ser then $o+ will not be able to create session (Connect) to database. "o basicall$ we ha,e to !e,o3e the CO''EC !ole and &rant other than create table pri,ile/e to this +ser.
1)a" *!%# o$ !%$or-a"!o% !s s"ore# !% UNDO se'-e%"s. Onl$ before ima/e of data is stored in the U'DO se/ments. If transaction is rolled bac3 information from U'DO is applied to restore ori/inal datafile. U'DO is ne,er m+ltiple#ed.
Ho "o Re-o/e Oracle Ser/!ce !% !%#os e%/!ro%-e%". ;e can add or remo,e Oracle "er,ice +sin/ oradim which is a,ailable in O!ACLE0OME=bin C:Oradim `delete `sid or Oradim `delete `s,rc
1)0 ORA>555: ")e acco+%" !s loc*e#. 1)a" 0o+ !ll #o !% ")a" case. he Oracle @/ defa+lt is to loc3 an acco+nt after @ bad password attempts and /i,in/ O!A< 2: the acco+nt is loc3ed. In that case one of the sol+tions is increase defa+lt limit of the lo/in attempts. "4L5 Alter profile defa+lt limit 8AILEDLO&I'AEMP" +nlimited
Ho "o Re#+ce ")e P)0s!cal Rea#s o% S"a"!s"!cs. >o+ need to increase the B+ffer Cache Consider the sit+ation B+ffer Cache of the database is RMB. One "4L /a,e the Ph$sical read as @. I increased as QMB and now the same "4L /i,in/ the Ph$sical read ,al+e is
Ho -a%0 re#o 'ro+,s are re+!re# $or a Oracle DB. At least 2 redo /ro+ps are re-+ired for a Oracle database to be wor3in/ normall$. M$ spfile is corr+pt and now I cannot start m$ database r+nnin/ on m$ laptop. Is there a wa$ to b+ild spfile a/ainT if $o+ are on +ni# then 7 cd 7O!ACLE0OME=dbs 7 strin/s spfilename temppfile.ora edit the temppfile.ora* clean it +p if there is an$thin/ Vwron/V with it and then "4L5 start+p pfiletemppfile.ora "4L5 create spfile from pfile "4L5 sh+tdown "4L5 start+p On windows << +st tr$ editin/ the spfile do not try with the prod db $irst try to check on test db. It can be dangerous* create a pfile from it. sa,e it* and do the same or if $o+ /ot problem $o+ can start+p the db from the command line +sin/ s-lpl+s c reate a pfile* do a man+al start+p (start the oracle ser,ice* then +se s-lpl+s to s tart the database)
1)a" !s a $rac"+re# &loc*. 1)a" )a,,e%s )e% 0o+ res"ore a $!le co%"a!%!%' $rac"+re# &loc*. A bloc3 in which the header and footer are not consistent at a /i,en "C'. In a +ser
bloc3 ima/e before the chan/e so that the database can r econstr+ct this bloc3 if media reco,er$ finds that this bloc3 was fract+red. he bloc3 that the operatin/ s$stem reads can be split* that is* the top of the bloc3 is written at one point in time while the bottom of the bloc3 is written at another point in time. If $o+ restore a file containin/ a fract+red bloc3 and Oracle reads the bloc3* then the bloc3 is considered a corr+pt.
Go+ recrea"e# ")e co%"rol $!le &0 @+s!%' &ac*+, co%"rol $!le "o "race a%# +s!%' al"er #a"a&ase &ac*+, co%"rol$!le "o ‘loca"!o%’ co--a%# )a" )a/e 0o+ los" !% ")a" case. >o+ lost all of the bac3+p information as +sin/ bac3+p controlfile to trace where as +sin/ other ALE! ALE! DAABA"E DAABA"E BACKUP CO'!OL8ILE to D:Bac3+pcontrol@.ctlH. D:Bac3+pcontrol@.ctlH. All bac3+p information is retained when $o+ ta3e binar$ control file bac3+p.
I$ a &ac*+, !s !ss+e# a$"er @s)+"#o% a&or" co--a%# )a" *!%# o$ &ac*+, !s ")a". It is an inconsistent bac3+p. If $o+ are in noarchi,elo/ mode ens+re that $o+ iss+e the sh+tdown immediate command or start+p force is another option that $o+ can iss+e: s tart+p force<5sh+tdown abort followed b$ sh+tdown immediate
Ho ca% I c)ec* !$ ")ere !s a%0")!%' roll!%' &ac*. It depends on how $o+ 3illed the process. If $o+ did and alter s$stem 3ill session $o+ sho+ld be able to loo3 at the +se#4+&l* bloc3 in /"ra%sac!"o% to /et an estimate for the rollbac3 bein/ done. If $o+ 3illed to ser,er process in the O" and PMO' is re co,erin/ the transaction $o+ can loo3 at 2FAST4START4TRANSACTIONS ,iew to /et the estimate
Ho "o $!%# o+" )o -+c) roll&ac* a sess!o% )as "o #o select timeremainin/ from ,7sessionlon/ops where sid sid of the session do in/ the rollbac35
Ho "o Dro, a col+-% o$ a Ta&le. Consider the below E#ample
Cre#te t#le %&# '#te '#te(( '#te '#te(( c '#te '#te); ); 'ow to drop col+mn B:
Alter t#le % et +,+e' col+m, ;
<< it will mar3 col+mn as U'U"ED
Select * from -. -.'#_+,+e'_col_t# '#_+,+e'_col_t#;; Alter t#le % 'ro/ +,+e' col+m,; Alternati,e method to drop col+mn:
Alter t#le % 'ro/ col+m, c c#c#'e co,tr#0,t;
Ho ca% e see ")e ol#es" $las)&ac* a/a!la&le. >o+ can +se the followin/ -+er$ to see the flashbac3 data a,ailable.
SELECT to_c1#r&-'#te( to_c1#r&-'#te(233334""4DD 233334""4DD 55678"I2)) c+rre,t_t0me( to_c1#r& 55678"I2 to_c1#r&f f .ol'et_9#1#c:_t0me ol'et_9#1#c:_t0me(( 233334""4DD 55678"I2))OLDEST_!LAS5BACK_TI"E 55678"I2 OLDEST_!LAS5BACK_TI"E(( &-'#te 4 f .ol'et_9#1#c:_t0me ol'et_9#1#c:_t0me))*67 67* *< 5IST_"IN !RO" =$'#t##e '( V$!LAS5BACK_DATABASE_LO> f ;
Ho "o 'e" c+rre%" sess!o% !# ,rocess !# cl!e%" ,rocess !#. select b.sid* b.serialN* serialN* a. a.spid spid processid* processid* b.process clientpid from ,7process a* ,7session b where a. a.addr addr b.paddr and b.a+dsid +seren,( +seren,(sessionid sessionid) ) 67"E""IO'."ID and 67"E""IO'."E!IALN are database process id 67P!OCE""."PID ` "hadow process id on the database ser,er 67"E""IO'.P!OCE"" ` Client process id* on windows it is [:\ separated the first N is the process id on the client and 2nd one is the thread id.
1)a" !s MRC . 1)a" 0o+ #o as a,,l!ca"!o% DBA $or MRC. M!C also called as M+ltiple !eportin/ C+rrenc$ in oracle application. Defa+lt $o+ ha,e c+rrenc$ in U" Dollars b+t if $o+r or/ani%ation operatin/ boo3s are in other c+rrenc$ then $o+ as application DBA need to enable M!C in applications.
Ho !ll 0o+ $!%# I%/al!# O&?ec"s !% #a"a&ase. select co+nt(^) from dbaobects where w here stat+s li3e I'6ALID select ^ from dbaobects where stat+s li3e I'6ALID
Ca% 0o+ +se &o") ADPATCH a%# OPATCH !% a,,l!ca"!o%.
>es $o+ ha,e to +se both in application * for application patches $o+ will +se ADPAC0 UILI> and for appl$in/ database patch in application $o+ will +se opatch UILI>. UILI>.
Do 0o+ )a/e !#ea )o "o "race a r+%%!%' ,rocess o% L!%+9. Usin/ strace $o+ can trace the s$stem calls bein/ e#ec+ted b$ a r +nnin/ process 7 strace
1)a" are #a"a&ase l!%*. D!$$ere%c!a"e ")e +se o$ eac) o$ ")e-. A database lin3 is a named obect that describes a VpathV from one database to another. another. here are different t$pes of database lin3 s+ch as: Pri,ate database lin3* p+blic database lin3 networ3 database lin3. Pri,ate database lin3 is created on behalf of a specific +ser. A pri,ate database lin3 can be +sed onl$ when the owner of the lin3 specifies a /lobal obect name in a "4L statement or in the definition of the owners ,iews or proced+res. P+blic database lin3 is created for the special +ser /ro+p PUBLIC. A p+blic database lin3 can be +sed when an$ +ser in the associated database spec ifies a /lobal obect name in a "4L statement or obect definition. 'etwor3 database lin3 is created and mana/ed b$ a networ3 domain ser,ice. A networ3 database lin3 can be +sed when an$ +ser of an$ database in the networ3 specifies a /lobal obect name in a "4L statement or obect definition.
Ho "o *%o )!c) /ers!o% o$ #a"a&ase 0o+ are or*!%'. select ^ from ,7,ersion ,7,ersion
I% Re$ere%ce "o R-a% ,o!%" !% "!-e Reco/er0 )!c) sce%ar!o !s &e""er $or 0o+ 6U%"!l "!-e or +%"!l se+e%ce7. I am practicin/ ,ario+s scenarios for bac3+p and reco,er$ +sin/ !MA'. I find +ntil "C' better than +ntil time* with lo/se- in the middle. Until time is still /oin/ to +se (+ltimatel$) an "C' to reco,er* so if $o+ 3now the "C' it wo+ld be preferred pr eferred if not then time is fine.
I$ 0o+ )a/e $or'o""e% ")e roo" ,assor# o% Ce%"OS ")e% )a" )a" 0o+ !ll #o. If $o+ are on CentO" then follow these steps: < At the splash screen d+rin/ boot time* press an$ 3e$ which will ta3e $o+ an interacti,e men+. < hen select a Lin+# ,ersion $o+ wish to boot and press [a\ to append option to the line this will brin/ $o+ to a line with the boot command < 'e#t at the end of that line t$pe [sin/le\ as an option=parameter and then Press [Enter\ to e#it and e#ec+te the boot this will start the O" with sin/le +ser mode which allow $o+ to reset the root password b$ t$pin/ passwd and $o+ can set new password for [root\.
Ho "o #e"er-!%e )e")er ")e #a"a$!les are s0%c)ro%!3e# or %o". select stat+s* stat+s* chec3pointchan/eN chec3pointchan/eN** tochar( tochar(chec3pointtime chec3pointtime** DD>> DD>>> > 002Q:MI:"" 002Q:MI:"")) as
chec3pointtime* co+nt(^) from ,7datafileheader /ro+p b$ stat+s* chec3pointchan/eN* chec3pointtime order b$ stat+s* chec3pointchan/eN* chec3pointtime Chec3 the res+lts of the abo,e -+er$ if it ret+rns one and onl$ one row for the online datafiles* means the$ are alread$ s$nchroni%ed in terms of their "C'. Otherwise the datafiles are still not s$nchroni%ed $et.
Go+ )a/e ?+s" res"ore# $ro- &ac*+, a%# #o %o" )a/e a%0 co%"rol $!les8 Ho o+l# 0o+ 'o a&o+" &r!%'!%' +, ")!s #a"a&ase. If $o+ do not ha,e a control file* $o+ can create one from scratch in "4L^Pl+s as follows: @. s-lpl+s =nolo/ 2. connect = as s$sdba R. "tart+p nomo+nt Q. the either create controlfile or restore it from the bac3+p (if $o+ ha,e) X. alter dataase mo+nt S. !eco,er database +sin/ bac3+p co ntrolfile 1. Alter database open 8rom more details follow m$ blo/ post VDisaster !eco,er$ from the scratchV: http:==shahiddba.blo/spot.com=2@2=X=rman
Is ")ere a%0 a0 "o $!%# ")e las" recor# $ro- ")e "a&le. select ^ from emplo$ees where rowid in(select ma#(rowid) from emplo$ees) select ^ from emplo$ees min+s select ^ from emplo$ees where rown+m (select co+nt(^) from emplo$ees)
Ho 0o+ !ll $!%# Oracle "!-es"a-, $ro- c+rre%" SCN. select dbms_flashback.get_system_change_number scn from d+al << Oracle 6er. 9i "C' <<<<<<<<<<<< QRX2X "4L5 "elect tochar(CU!!E'"C') from ,7database << oracle 6er. @/ or abo,e "4L5 select c+rrentscn* dbms_flashback.get_system_change_number from ,7database <
Ho "o s+s,e%#(res+-e a ,rocess +s!%' ora#e&+'. "4L5 oradeb+/ setorapid @Q Uni# process pid: @Q9S2* ima/e: oracle_localhost.localdomain ('" 6@<6R) "4L5 oradeb+/ s+spend "tatement processed. "4L5 oradeb+/ res+me "tatement processed.
Ho "o $!%# ")e las" "!-e a sess!o% ,er$or-e# a%0 ac"!/!"0. In ,7session the col+mn lastcallet has ,al+e which tells +s the last time (seconds) a/o when the session performed an$ acti,it$ within the database. select +sername* floor(lastcallet = S) VMin+tesV* stat+s from ,7session where +sername is not n+ll order b$ lastcallet
Ho "o $!%# ,ara-e"ers ")a" !ll "a*e !%"o e$$ec" $or %e sess!o%s. Usin/ the followin/ -+er$ one can find the list of parameters that will ta3e info effect for new sessions if the ,al+e of the parameter is chan/ed. "4L5 "ELEC name 8!OM ,7parameter ;0E!E iss$s modifiable DE8E!!ED >o+ can chan/e the parameter +sin/ the deferr ed option: "4L5 alter s$stem set sortareasi%eSXXR deferred "$stem altered
Ho "o $ree 6Fl+s)7 &+$$er cac)e. 0ow to free b+ffer cacheT 'ote: $o+ ma$ onl$ want to do this on De, or est en,ironment as it wo+ld affect performance on prod+ction. I alread$ written on m$ earlier post http:==shahiddba.blo/spot.com=2@2=X=dba
BB <<< cr free #c+r << fl+sh b+ffer cache for @/ and +pwards "4L5 alter s$stem fl+sh b+ffercache "$stem altered. << fl+sh b+ffer cache for 9i and +pwards "4L5 alter session set e,ents immediate trace name fl+shcacheH "ession altered. << "hows b+ffer cache was freed after fl+shin/ b+ffer c ache "4L5 select distinct stat+s from ,7bh "AU"
BB < 8ree
Ho "o s+s,e%# all ?o&s $ro- e9ec+"!%' !% #&a4?o&s. B$ settin/ the ,al+e of to the parameter [ob-+e+eprocesses\ $o+ can s+spe nd all obs from e#ec+tin/ in DBAOB". he ,al+e of this parameter can be chan/ed witho+t instance
restart. "4L5 1o? /#r#meter @o_+e+e_/rocee; 'AME >PE 6ALUE
BBBBBBBBBBBB BBBC BBBC ob-+e+eprocesses inte/er Q 'ow set the ,al+e of the parameter in memor$* which will s+spend obs from startin/ "4L5 alter s$stem set ob-+e+eprocesses scopememor$ "$stem altered.
Ho "o see ")e ?o&s c+rre%"l0 &e!%' e9ec+"e#. B$ +sin/ dbaobsr+nnin/ to can see all the ob c+rrentl$ e#ec+tin/ "4L5 select dr.sid* dr.ob* dr.fail+res* dr.thisdate* dr.thissec* d.what from dbaobsr+nnin/ dr* dbaobs d where dr.ob d.ob
1)a" !s SM !% Oracle a,,l!ca"!o% EB+s!%ess S+!"e. &"M stands for &eneric "er,ice Mana/ement 8ramewor3. Oracle E
Ho ca% 0o+ l!ce%se a ,ro#+c" a$"er !%s"alla"!o%. >o+ can +se ad +tilit$ adlicm/r to licence prod+ct in Oracle application.
I% a s!"+a"!o% )e% 0o+ a%" "o *%o )!c) as ")e las" +er0 $!re# &0 ")e +ser8 Ho "o c)ec*. "elect ".U"E!'AME(s.sid)<s.os+ser U'AME *s.sid=s.serialN sid*s.stat+s V"tat+sV*p.spid*s-lte#t s-lte#t from ,7s-lte#twithnewlines t*67"E""IO' s * ,7process p where t.address s.s-laddress and p.addrs.paddr(Y) and t.hash,al+e s.s-lhash,al+e order b$ s.sid*t.piece
Ca% o%e co,0 Oracle so$"are $ro- o%e -ac)!%e "o a%o")er. >es* one can cop$ or 8P the Oracle "oftware between similar machines. Loo3 at the followin/ e#ample: N +se tar to cop$ files and director$s with permissions and ownership tar cf ` 7O!ACLE0OME rsh [cd 7O!ACLE0OME tar #f `[ o cop$ the Oracle software to a different director$ on the same ser,er: cd =new=oracle=dir= (cd 7O!ACLE0OME tar cf ` . ) tar #,f < 'OE: !emember to relin3 the Intelli/ent A/ent on the new machine to pre,ent messa/es li3e [Encr$ption 3e$ s+pplied is not the one +sed to encr$pt file\: cd =new=oracle=dir=
cd networ3=lib ma3e
A s!%'le "ra%sac"!o% ca% )a/e -+l"!,le #ele"es a%# a s!%'le SCN %+-&er !#e%"!$0!%' all o$ ")ese #ele"es8 1)a" !$ I a%" "o $las) &ac* o%l0 a s!%'le !%#!/!#+al #ele"e. >o+ wo+ld flash bac3 to the ">"EM (not $o+r transactions) "C' at that point in time. he ">"EM has an "C'* $o+r transaction has an "C'. >o+ care abo+t the ">"EM "C' with flashbac3* not $o+r transactions "C'.
Are $las) &ac* +er!es +se$+l $or ")e #e/elo,er o r ")e DBA &o"). Ho ca% I as a #e/elo,er a%# DBA 'e" "o *%o ")e SCN %+-&er o$ a "ra%sac"!o%. Oracle 8lashbac3 is a tool is +sef+l for both either DBA and De,eloper. If $o+ deleted data accidentl$ then either DBA or De,eloper both can flashbac3* reco,er and fi# this problem. As a de,eloper $o+ can +se Vdbmsflashbac3./ets$stemchan/en+mberV to ret+rns the c+rrent s$stem "C' and as DBA $o+ can +se Lo/ Miner +tilit$ to to loo3 bac3 in time at ,ario+s e,ents to find "C's as well.
A$"er Per$or-!%' DML o,era"!o% 0o+ are +s!%' $las)&ac* +er0 "o re"+% &ac* 0o+r co--!""e# #a"a ca% 0o+ +se $las)&ac* co%ce," a$"er Tr+%ca"!%' a%0 #a"a. In ,ersion 9i* 8lashbac3 is limited to Data Manip+lation Lan/+a/e (DML) commands s+ch as "ELEC*I'"E!* UPDAE* and DELEE. r+ncate doesnt /enerate an$ +ndo for the table tr+ncate +st c+ts it all loses where as delete p+ts the deleted data into +ndo. 8lashbac3 -+er$ wor3s on +ndo.
1)a" !s SID a%# )a" !s !" +se# $or. 1)ere ca% I $!%# o+" ")e SID o$ -0 #a"a&ase. he "ID is a site identifier. It pl+s the Oraclehome are hashed to/ether in Uni# to create a +ni-+e 3e$ name for attachin/ an "&A. If $o+r Oraclesid or Oraclehome is not set correctl$* $o+ will /et Voracle not a,ailableV. $o+ can /et instance name with the followin/ command: select instance from ,7thread select instancename from ,7instance
I$ 0o+ are &+0!%' a %e ser/er ")a" !ll &e a -!rror !-a'e o$ ")e c+rre%" Pro#+c"!o% Ser/er )a" o+l# &e ")e s"e, $or ")a". In same en,ironment and director$ str+ct+re set+p ser,er* install oracle* +se oradim to set+p the re/istr$ (re/ister the instance) and restore from bac3+p.
I a- clo%!%' #a"a&ase A as #a"a&ase B &o") e9ac"l0 !#e%"!cal r+%%!%' !% NOARCHI2ELO -o#e8 Da"a&ase A !ll &e s)+"#o% &e$ore co,0!%' $!les8 I a- +s!%' ")e CREATE CONTROLFILE s"a"e-e%" "o clo%e8 a7 Do I %ee# "o co,0 re#o lo' $!les $ro- A "o B !$ I %ee# "o o,e% B !") RESETLOS o,"!o%. &7 Do I %ee# "o co,0 co%"rol $!les $ro- A "o B s!%ce I !ll &e crea"!%' co%"rol$!le $or B. a) >o+ do not need to* b+t $o+ wo +ld a,oid ha,in/ to open resetlo/s if that ma3es $o+ feel better. b) 'ot if $o+ are doin/ the create controlfile tric3. >o+ co+ld +st cop$ E6E!>0I'&* start+p mo+nt* and iss+e a series of alter database rename file old name to new names and then alter database open (ass+min/ lo/s are in the same place* else $o+ll drop and create them).
No"e: My understanding is that i$ you use %!#!T&'# option in "%!AT! "NT%&(I&!, the redo log $iles will be created by racle as per the speci$ications gi)en in the create control$ile statement.
I )a/e a %e ser/er8 1)a" !s ")e &es" a0 I ca% )a/e ")e sa-e oracle se"+, ")a" !s ")ere o% a ,ro#% #&. E!")er e %ee# "o res"ore ")e $!le s0s"e-s a%# rel!%* oracle !")o+" #o!%' a%0 !%s"alla"!o%. M$ s+//estion is install the same software on another ser,er then then appl$ res tore and reco,er proced+re on the same en,ironment or director$ str+ct+re. 'o idea abo+t Vrelin3 oracle witho+t doin/ an$ installationV* see the admin /+ide for $o+r O" for details on thin/s li3e this.
T)ere !s a%0 #!$$ere%ce &e"ee% Oracle TCL a%# DCL co--a%#. DCL stands for Data Control Lan/+a/e. hese command are +sed to confi/+re and control database obects s+ch as &!A'* !E6OKE where as CL stands for ransaction Control lan/+a/e. It is +sed to mana/e the chan/es made b$ DML statements. It allows statements to be /ro+ped to/ether into lo/ical transactions s+ch as COMMI < sa,e wor3 done "A6EPOI' < identif$ a point in a transaction to which $o+ can later roll bac3 !OLLBACK < restore database to ori/inal since the last COMMI "E !A'"ACIO' < Chan/e transaction options li3e isolation le,el and what rollbac3 se/ment
to +se
1)a" )a,,e%s )e% ")e loc* !s #!sa&l!%' o% ")e "a&le. ;hen $o+ disablin/ the loc3 on table then $o+ are not able to perform DDL operation on that table b+t $o+ still to mana/e DML operation easil$
For E9a-,le: Create able s@ (Eno n+mber(2)* ename ,archar2(@X)* salar$ n+mber(X*2)) insert into s@ ,al+es (@* shahid* Q) insert into s@ ,al+es (@* a,ed* 2) insert into s@ ,al+es (2* 3arim* @) **disable lock on table Alter table s@ disable table loc3 ** cannot drop/truncate table as table lock is disable drop table s@ tr+ncate table s@ **you cannot able to add/modi$y/drop column Alter table s@ add comm n+mber(X*2) Alter table s@ modif$ s@ salar$ n+mber (@*Q) Alter table s@ drop col+mn salar$ ** +ut still you are able to per$orm DM& +pdate s@ set salar$ where eno2 select ^ from s@ delete from s@ where eno2 insert into s@ ,al+es (2* mohan* 2X)
1)a" !s ")e !-,or"a%ce o$ cloc* "!-e !% case o$ #a"a&ase clo%!%'. M$ personal e#perience sometimes +st clonin/ a database is not eno+/h if mo,in/ it to another machine $o+ also ha,e to ens+re: @. he en,ironment on the new machine is set+p* to match the cloned s$stem this wo+ld incl+de memor$ disc allocation space. 2. he VnewV machine time is the same or /reater than the machine $o+ were clonin/ from
Ho -+c) s,ace #oes !" "a*e "o clo%e a #a"a&ase. he clone needs the same space.
I% )!c) case LIKE 6&e$ore or a$"er +se7 o,era"or ,er$or-a%ce !%creases. LIKEJ wor3s the fastest beca+se it +ses the inde# to search on the col+mn pro,ided an inde# is specified on the col+mn. Usin/ J after LIKE* res+lts in faster res+lts.
Do 0o+ )a/e !#ea a&o+" F+33 "es"!%' or $+33!%'. 8+%% testin/ or f+%%in/ is a software testin/ techni-+e that pro,ides random data (Vf+%%V) to the inp+ts of a pro/ram. If the pro/ram fails (for e#ample* b$ crashin/* or b$ failin/ b+ilt
Us!%' ")e e9,#,(!-,#, 6Da"a P+-, !% 5'7 ca% e9,or" a%# !-,or" #a"a $ro- o%e
sc)e-a(Da"a&ase "o a%o")er sc)e-a(Da"a !s !" ,o ss!&le. >es* $o+ can +se dblin3 for that
1)a" !s Da"aMa,,er. DataMAPPE! is a hi/h
Ho "o S"ar" E%"er,r!se Ma%a'er $ro- co--a%# l!%e. C:cd O!ACLE0OME=bin C:emctl start dbconsole 'ow t$pe on the browser http:==localhost.localdomain:XX=em=
Ho !ll 0o+ $!%# c+rre%" a%# -a9 +"!l!3a"!o% o$ sess!o% a%# %+-&er o$ ,rocesses. "4L5select reso+rcename* c+rrent+tili%ation* ma#+tili%ation from ,7reso+rcelimit where re so+rcename in(processes*sessions) R"SO$RC"_NA&"
C$RR"NT_$TILIATION &A:_$TILIATION
,roce..e.
14
18
.e..o-.
1
1
As ")e "a&le !s &e!%' -o#!$!e# ca% RO1ID o$ a ro c)a%'e. A rowid is assi/ned to a row +pon insert and is im+table (ne,er chan/in/) +nless the row is deleted and re
1)a" )a,,e%e# )e% I +,#a"e# %arro ros se""!%' c)arac"er "o !#e /al+es. In this case row will mi/rate b+t the rowid for the row sta$s the same e,en when the row mi/rate.
Sess!o% : re"r!e/es a ro !") ro!# Sess!o% >: #ele"es ")e ro !") ro!# co--!"s ( ro!# !s %o $ree $or re+se ( Sess!o% : !%ser"s a %e ro !") ro!# co--!"s Sess!o% : +,#a"e 8888 )ere ro!# Sess!o% s +,#a"e !s %o" +,#a"!%' ")e sa-e ro ")a" !" )a# earl!er re"r!e/e#8 Co%s!#er ")e a&o/e sce%ar!o )a" s)o+l# &e ")e sol+"!o% Use the Primar$ Ke$ with the table. If $o+ combine rowid with the primar$ 3e$ then it will be perfectl$ safe to +se rowid id in all cases.
I$ 0o+ )a/e a s!%'le #ele"e s"a"e-e%" ")a" #ele"es -a%0 recor#s +s!%' ro!#s8 1o+l# ")ere e/er &e a "!-e )e% ")e ro!# !")!% ")!s "a&le c)a%'e #+r!%' ")e e9ec+"!o% o$ ")!s #ele"e s"a"e-e%". In order for a rowid to chan/e $o+ ha,e to enable row mo,ement first so if row mo,ement is not enabled then answer is 'O. If it is* then flashbac3 table co+ld chan/e a rowid incase of DDL statement and wo+ld not happen conc+rrentl$ with a delete (so it wo+ld not affect it).
For E9a-,le: Alter table s@ shrin3 space compact* that mo,es rows and wo+ld chan/e rowids.
Update of a partition 3e$ that ca+ses a row to mo,e* that mo,es rows and wo+ld chan/e rowids.
I$ I $!re "o !%ser"s !% a "a&le )e")er ")e ro!# o$ ")e >%# recor# !ll &e 'rea"er ")a% ro!# o$ ")e s" recor#. he answer is 'O see the e#ample below if $o+ insert A then
insert B
later
insert C delete A insert D
It is -+ite possible in abo,e e#ample that D will be VfirstV in the table as it too3 o,er As place. If rowids alwa$s V/rewV* than space wo+ld ne,er be re+sed (that wo+ld be an implication of rowids /rowin/ alwa$s. ;e wo+ld ne,er be able to re+se old space as the rowid is +s t a file.bloc3.slot
D!$$ere%ce &e"ee% S"ore# Proce#+re a%# Macro. S"ore# Proce#+re: It does not ret+rn rows to the +ser. It has to +se c+rsors to fetch m+ltiple rows It +sed Ino+t=o+t to send ,al+es to +ser It is stored in DAABA"E or U"E! PE!M A stored proced+re also pro,ides o+tp+t=Inp+t capabilities
Macros: It ret+rns set of rows to the +ser. It is stored in DBC PE!M space A macro that allows onl$ inp+t ,al+es
I$ ")e ,or" <> !s #e$a+l" ,or" $or ")e TNSL!%s"e%er8 I )a/e a #a"a&ase ser/er o% ,or" <>Q )o ca% I -a*e ")e cl!e%"s co%%ec" o% ")!s ,or" or ca% I )a/e o%e l!s"e%er ser/!ce co%%ec" "o l!s"e% $or > ser/ers. If $o+ are +sin/ V0ost namin/V con,ention (this is a method that does not re-+ire the c lient to ha,e a tnsnames.ora file at all. >o+ m+st be +sin/ CP or $o+ m+st onl$ ha,e one defa+lt database per host. he client onl$ needs to 3now the hostname of the ser,er to connect) then $es* @X2@ is the defa+lt and onl$ port. If $o+ are +sin/ tnsnames.ora* the Oracle nameser,er* or an$ other method to connect then no* @X2@ is not a defa+lt port. In this case* @X2@ is simpl$ the port +sed b$ Vcon,entionV. he clients wo+ld* t$picall$ in their tnsnames.ora* connect to the listener on some specified port n+mber. @X2@ is the con,ention +sed b$ man$ people it is neither mandator$ nor necessar$.
1)a" !s a% IPC ,ro"ocol a%# )ere a%# )o !" !s +se#. I )a/e e9,er!e%ce o%l0 !% TCP(IP ,ro"ocol8 Is ")ere a%0 a#/a%"a'e !% +s!%' IPC o/er TCP. IPC is interposes comm+nication $o+ ha,e messa/es* pipes* soc3et pairs and so on it is alot
li3e +st +sin/ soc3ets with CP=IP. IPC is /enerall$ limited to Va machineV* not o,er a networ3. IPC +sed to be a tad faster than CP b+t recent tests ha,e shown this to be less and less tr+e.
I% 0o+r a&se%ce a%0 &o#0 )as #o%e a%0 al"era"!o% ")e% )o #!# 0o+ %o"!ce or Ho "o *%o las" DDL $!re# $ro- ")e ,ar"!c+lar sc)e-a a%# ,ar"!c+lar "a&le. o find the last ddl performed chec3 o+t the lastddltime from allobects* dbaobects* +serobects ,iew beca+se each time and obect chan/es the lastddltime is +pdated from these ,iew. "elect C!EAED* IME"AMP* lastddltime from allobects ;0E!E O;'E!0!M" A'D OBEC>PEABLE A'D OBEC'AMEPA>!OLLMAI'8IL E In the abo,e -+er$ 0!M"H is the schema name and pa$rollmainfile is the table name.
Ho "o $!%# "a&les ")a" )a/e a s,ec!$!c col+-% %a-e. "ELEC owner* tablename* col+mnname 8!OM dbatabcol+mns ;0E!E col+mnname li3e AMOU' O!DE! b$ tablename
D!$$ere%"!a"e Ro le/el a%# s"a"e-e%" le/el Tr!''er. !ow Le,el ri//er is fired each time row is affected b$ Insert* Update or Delete command. If statement doesnHt affect an$ row then no tri//er action happens where as "tatement le,el tri//er fires when a "4L statement affects the rows of the table. he tri//er acti,ates and performs its acti,it$ irrespecti,e of n+mber of rows affected d+e to "4L statement . The$ /et fired once for each tri//erin/ statement.
I e8"orted one ta#le with a name of user$ how to im"ort that ta#le with another name of user? ":(D( .er1M,FG TABL"Ste.t D$&(#IL"te.t!D&( DIR"CTOR)?c I&(D( .erM,FG R"&A(_SCH"&A.er1.er D$&(#IL"te.t!D&( DIR"CTOR)?c
"or" I&(D( .erM,FG GrectorGrector_-?me t?le.t?le_-?me Gm,fleGm,_-?me!Gm, SLr?-t re?G Frte o- Grector Grector_-?me to ,lc SLr?-t re?G Frte o- Grector JGr_-?me to J.er
Rust careful to give grant to public if it is production Environment I have two server of same configuration having single data#ase of 652B and <5 2B si>e res"ectivel&$ I want to merge into single server what are the "rere=uisites and ste"s to follow in this case.
In my view EJportImport is the best solution to merge the database. ?ou can eJport the schemas from one database and import it into other database. Can one monitor how fast a ta#le is im"orted?
If you need to monitor how fast rows are imported from a running import ob& try one of the following methods: %ethod 64 .elect ..tr./l_te%t-.tr./l_te%t;INTO =;2302 t?le_-?me roF._,roce..eG ro-G..G?teto_G?tefr.t_lo?G_tme;mmGG
>>4m..;22*4*012
m-te. tr-croF._,roce..eGM..G?teto_G?tefr.t_lo?G_tme;mmGG >>4m..;22*4*022 roF._,er_mfrom ..!E_6./l?re? F>ere ./l_te%t lKe ;INS"RT INTO =; ?-G comm?-G_t,e ?-G o,e-_Eer.o-. 0
If the import has more than one table& this statement will only show information about the current table being imported. %ethod <4
0se the -EED'!,AFn import parameter. This command will tell I9+ to display a dot for every 5 rows imported. ,ow we will increase "erformance on "articular ta#le? ,ere I am inserting <2B data in ta#le$ its ta'es more time to insert in a ta#le. Is there an& wa& to increase "erformance on a "articular ta#le?
IndeJ on huge table while doing insert will not only solution to improve performance. Ket your table partitioned that will make table insertion faster and also easy to manage the archive data. !lternatively do one thing first disable constraints as well as indeJ then perform insertion then again enable. ?ou can use high"speed solid"state disk (!9"!5% to make Oracle inserts run up to G##J faster than platter disk. ,ow to reduce alert log 3i>e?
If you move or delete your !lert log file& it is recreated automatically in neJt startup& alternatively you can put a script at O level to move the archives and use new one. o the best way to reduce the siBe of log is ust move your aler.log to some other place. Oracle will recreate it in neJt startup. ,ow &ou will 'now the instance is Primar& or 3tand#&?
'y uerying vMdatabase one can tell if the host is primary or standby On the primary database: <16 select database8role from vMdatabase= DATABAS"_ROL" (RI&AR)
O check the value of controlfile8type in PMdatabase i.e is X,0E5TY for primary and T!5D'? for standby <16 E1E,T controlfile8type -O9 PMdatabase= CONTROL C$RR"NT
On the tandby database: <16 select database8role from vMdatabase= DATABAS"_ROL" (H)SICAL STANDB)
<16 E1E,T controlfile8type -O9 PMdatabase= CONTROL STANDB)
*ote4 ?ou may need to connect to as sys if the instance is in mount state ,ow would &ou determine what sessions are connected and what resources the& are waiting for?
0se of PMEIO5 and PMEIO58@!IT 2ive two methods &ou could use to determine what DD1 changes have #een made.
?ou could use 1ogminer or treams ,ow would &ou determine who has added a row to a ta#le?
Turn on fine grain auditing for the table. 08"lain the differences #etween PFI10 and 3PFI10
! +-I1E is a tatic& teJt file that initialiBes the database parameter in the moment that itHs started. If you want to modify parameters in +-I1E& you have to restart the database. ! +-I1E is a dynamic& binary file that allows you to overwrite parameters while the database is already started (with some eJceptions%. *ame some clients that can connect with -racle?
There are several such as <1 Developer& <1"+lus& TO!D& dbvisualiBer& +1<1 Developer. In which view can &ou find information a#out ever& view and ta#le of oracle dictionar&?
DI,T or DI,TIO5!? view. ?ou can uery as: <16 E1E,T L -O9 DI,T= ,ow can we change which data#ases are started during a re#oot in 1inu8 0nv.?
Edit the etcoratab ,ow can we reduce the s"ace of T0%P datafile?
+rior to Oracle ))g& you have to re"create the datafile. In Oracle ))g a new feature was introduced and you can shrink the TE9+ tablespace. ,ow can &ou view all the current users connected in &our data#ase in this moment?
E1E,T ,O05T(L%&0E5!9E -O9 PMEIO5 KO0+ '? 0E5!9E= What is the difference #etween a view and a materiali>ed view?
! view is a select that is eJecuted each time a user accesses to it. ! materialiBed view stores the result of this uery in memory for faster access purposes. ,an we have different database versions in the same !, Env.U ?es& but ,lusterware version must be greater than the database version. ,ow can &ou difference a usual "arameter and an undocumented "arameter of oracle?
The undocumented parameters have the prefiJ W8H. uch as: 8allow8resetlogs8corruption What should #e the result of logical com"arision *!11 *!11@
A-alseH in both cases: In case of XE1E,T L -O9 9?8,CE9!.9?8T!'1EY why we are getting this error: +3"#$N4: ,olumn or attribute type can not be displayed by <1L+lusU ,heck for sure the table has a '1O' column. @hich are the default passwords of ?TE9?U 9!5!KE ,C!5KE8O58I5T!11 Is it "ossi#le to center an o#ect hori>ontall& in a re"eating frame that has a varia#le hori>ontal si>e?
?es Can a field #e used in a re"ort without it a""earing in an& data grou"?
?es When a form is invo'ed with callGform$ Does oracle forms issues a save "oint?
?es +ou have ust had to restore from #ac'u" and do not have an& control files. ,ow would &ou go a#out #ringing u" this data#ase?
I would create a teJt based backup control file& stipulating where on disk all the data files where and then issue the recover command with the using backup control file clause. 08"lain the difference #etween a data #loc'$ an e8tent and a segment.
! data block is the smallest unit of logical storage for a database obect. !s obects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called eJtents. !ll the eJtents that an obect takes when grouped together are considered the segment of the database obect. A ta#le is classified as a "arent ta#le and &ou want to dro" and re(create it. ,ow would &ou do this without affecting the children ta#les?
Disable the foreign key constraint to the parent& drop the table& re"create the table& enable the foreign key constraint. What column differentiates the / views to the 2/ views and how?
The I5T8ID column which indicates the instance in a !, environment the information came from. ,ow would &ou go a#out increasing the #uffer cache hit ratio?
0se the buffer cache advisory over a given workload and then uery the vMdb8cache8advice table. If a change was necessary then I would use the alter system set db8cache8siBe command. ,ow would &ou determine the time >one under which a data#ase was o"erating?
select D'TI9EVO5E from dual= 08"lain the use of setting 21-BA1G*A%03 e=ual to T!0.
etting K1O'!185!9E indicates how you might connect to a database. This variable is either T0E or -!1E and if it is set to T0E it enforces database links to have the same name as the remote database to which they are linking. What #ac'ground "rocess refreshes materiali>ed views?
The Rob
+9O5 What are the roles and user accounts created automaticall& with the data#ase?
D'! " role ,ontains all database system privileges. ? user account " The D'! role will be assigned to this account. !ll of the base tables and views for the database>s dictionary are store in this schema and are manipulated only by O!,1E. ?TE9 user account " It has all the system privileges for the database and additional tables and views that display administrative information and internal tables and views used by oracle tools are created using this username. What are the minimum "arameters should e8ist in the "arameter file init.ora@ ?
D' 5!9E " 9ust set to a teJt string of no more than 4 characters and it will be stored inside the datafiles& redo log files and control files and control file while database creation. D'8DO9!I5 " It is string that specifies the network domain where the database is created. The global database name is identified by setting these parameters (D'85!9E Z D'8DO9!I5% ,O5TO1 -I1E " 1ist of control filenames of the database. If name is not mentioned then default name will be used. D'8'1O,A8'0--E " To determine the no of buffers in the buffer cache in K!. +O,EE " To determine number of operating system processes that can be connected to O!,1E concurrently. The value should be * (background process% and additional ) for each user. O11'!,A8EK9E5T " 1ist of rollback segments an O!,1E instance acuires at database startup. !lso optionally 1I,E5E89!S8EIO5&1I,E5E8EIO58@!5I5K and 1I,E5E89!S80E. What is the difference #etween *A%0GI* and C-P+ ?
,opy is package procedure and writes values into a field. 5ame in is a package function and returns the contents of the variable to which you apply. ,ow do &ou im"lement the If statement in the 3elect 3tatement
@e can implement the if statement in the select statement by using the Decode statement. e.g select DE,ODE (E9+8,!T&>)>&>-irst>&>3>&>econd>5ull%= Cere the 5ull is the else statement where null is done . ,ow man& rows will the following 3Q1 return? 3elect X from em" Where rownum 659
5o rows Can dual ta#le #e deleted$ dro""ed or altered or u"dated or inserted?
?es
Wh& it is not necessar& to ta'e !*D- #ac'u"
In fact when you do some transaction& redo entries will be generated and accepted ust like that whenever some change happen to 05DO tablespace or 05DO segments oracle will generate redo entries. o even though you does not backup 05DO& you have the redo entries through which you can recover or rollback the transactions. What ha""ens with the datafile during hot #ac'u" "rocess?
The below three action will happen in case of hot backup process in database ).The Tablespace checkpointed. 3.The checkpoint ,5 in datafile header will freeBe to increment with checkpoint. G.-ull image of changed D' block are written to redologs. Wh& more redologs are generated during hot#ac'u"?
During the hotbackup in initial checkpointing& the datafile that comprise the tablespace generates full image of changed Db block in these tablespace to the redologs. 5ormally oracle logs an entry in the redologs for every change in database but it does not log the whole image of database blog. 'y logging full images of changed D' blocks to the redologs during hot backup mode& oracle eliminates the possibility of the backup containing fractured blocks and guarantees that in the event of a recovery& any fractured that might be in the backup copy of the datafile will be resolved by replacing them with the full image of the block from the redologs. ,ow do &ou increase the "erformance of O li'e o"erator?
The / placed after the search word (Wss/H% can enable the use of indeJ if one is specified in the indeJ column. This performance is better than the other two ways using / such as before the search word (like W/ssH% and before and after the search word (W/ss/H%. What is cache Fusion Technolog&?
,ache fusion treats multiple buffer caches as one oint global cache. This solves the issues like data consistency internally& without any impact on the application code or design. ,ache fusion technology eases the process of a very high number of concurrent users and <1 operations without compromising data consistency. Do &ou have idea a#out re"orts server?
eports server is also a component of the middle tier and is hosted in the same node of the concurrent processing server. eports server is used to produce business intelligence reports. What is im"ortance of re"lication and their use in oracle?
eplication is the process of copying and maintaining database obects in multiple databases that make up a distributed database system. ,hanges applied at one site are captured and stored locally before being forwarded and applied each of the remote location. eplication provides user with fast& local access to shared data& and protects availability of applications because alternate data access options eJist. Even if one site becomes unavailable& users can continue to uery or even update the remaining locations. In simple replication& you create a snapshot& a table corresponding to the uery>s column list. @ hen the snapshot is refreshed& that underlying table is populated with the results of the uery. !s data changes in a table in the master database& the snapshot is refreshed as scheduled and moved to the replicated database.
!dvanced replication allows the simultaneous transfer of data between two or more 9aster ites. There are considerations to keep in mind when using multi"master replication. The important ones are seuences (which cannot be replicated%& triggers (which can turn recursive if you>re not careful% and conflict resolution. What is the #asic difference #etween Cloning and 3tand#& data#ases?
The clone database is a copy of the database which can be opened in read write mode. It is treated as a separate copy of the database that is functionally completely separate. The standby database is a copy of the production database used for disaster protection. In order to update the standby database= archived redo logs from the production database can be used. If the primary database is destroyed or its data becomes corrupted& one can perform a failover to the standby database& in which case the standby database becomes the new primary database. Wh& we are using materiali>ed view instead of a ta#le?
9aterialiBed views are basically used to increase uery performance since it contains results of a uery. They should be used for reporting instead of a table for a faster eJecution. Which B2 "rocess refreshes the materiali>ed view?
Rob
The transportable tablespace enable us to transport data obects across different platform. 9oving data using transportable can be much faster than performing either eJportimport or unload or load of the same because transporting a tablespace only reuires the copying of datafiles Z integrating the tablespace structure information. Can we reduce the si>e of T0%P datafile?
?es& we can reduce the space of the TE9+ datafile. +rior to oracle ))g& you had to recreate the datafile but in oracle ))g you reduce space of TE9+ datfile by shrinking the TE9+ tablespace. It is a new feature to ))g. The dynamic performance view DBAGtem"Gfiles can be very useful in determining which table space to shrink. S"L"CT TABL"S(AC"_NA&" RO$NDB)T"SM10487M104 2 =IN B= #IL"_ID #IL"_NA&" #RO& DBA_T"&(_#IL"S ALT"R TABL"S(AC" T"&( SHRIN' T"&(#IL" Q DUORACL"UORADATAUSADHANUT"&(0
!DB#H '""( 7
5ew data dictionary to check free space Select * from G?_tem,_free_.,?ce
,ow can we move ta#le from one schema to another?
The simplest way is 1ogin with the ,OTT schema and use the below command to move E9+ table from C chema. ?ou can also use ,opy and ImportEJport for that. CR"AT" TABL" "&( AS S"L"CT * #RO& HR!"&(
,ow we can "revent fragmentation in oracle Ta#les"ace.
Tablespace fragmentation can be prevented by using +,TI5,E!E command. +,TI5,E!E is the percentage a new subseuent eJtent will grow. This value should be ideally set to # or )## to avoid tablespace fragmentation. !lternate and strange values for +,TI5,E!E results in strange siBes of eJtents. ame siBe of each eJtent of all segments must be used.
Do &ou 'now the use of iostat$ vmstat and netstat?
Iostat report on terminal& disk and terminal IO activities. Pmstat reports on virtual memory statistics for processes& disk& tape and ,+0 activity. 5etstat reports on the contents of network data structures. *ame the different t&"es of inde8es availa#le in -racle?
Oracle provides several IndeJing schemas '"tree indeJ etrieves a small amount of information from a large table. Klobal and 1ocal indeJ elates to partitioned tables and indeJes. everse Aey IndeJ " It Is most useful for oracle real application clusters applications. Domain IndeJ efers to an application Cash cluster IndeJ efers to the indeJ that is defined specifically for a hash cluster. What is a user "rocess trace file?
§
It is an optional file which is produced by user session.
It is generated only if the value of <18T!,E parameter is set to true for a session. §
<18T!,E parameter can be set at database& instance& or session level.
§
If it set at instance level& trace file will be created for all connected sessions.
§
If it is set at session level& trace file will be generated only for specified session.
§
The location of user process trace file is specified in the 0E8D09+8DET parameter.
,ow can &ou use automatic P2A memor& management with oracle i or a#ove?
et the @OA8!E!8IVE8+O1I,? parameter to !0TO and set +K!8!KKEK!TE8T!KET When a user comes to &ou and as's that a "articular 3Q1 =uer& is ta'ing more time. ,ow will &ou solve this?
If you find the particular uery is taking time to eJecute& then take a <1T!,E with eJplain plan& it will show how the <1 uery will be eJecuted by oracle& depending upon the report you will tune your database. Then determine the table siBe and check the user reuirement is / of data from uery table. If it is less then -or eJample: one table has )#### records& but you want to fetch only * rows& but in that uery oracle does the full table scan. Only for * rows full table scan is not a good& so create an indeJ on that particular column. If the user reuirement is more than 4#/ of data from uery table then in that case if we create indeJ& again user will get poor performance because oracle will get contention on db buffer cache since first of all indeJ block need to be picked up as well as almost all block from that table will be pull out. Cence it will increase the IO& also other user reuest may get slow performance since eJisting data in cache will be flush out and reloaded. !dditionally we need to check system level performance& either any problem with dbwn either dbwn writing slow any modified data which is in buffer to datafile and either user server process is waiting for space in buffer cacheU ,heck alert log file too. ,heck if user uery needed oin or sortingU ,heck either there is not enough space in temporary tablespaceU If user again user again facing issue then we need drill down to check either any issue with table block level either table needs defragments if watermark reached high.
What is Difference #etween s=lnet.ora$ listener.ora$ tnsname.ora networ' file? s=lnet.ora4 The normal location for this file is D:[oracle[ora3[network[admin. The slnet.ora file is the profile
configuration file& and it resides on the client machines and the database server. The snet.ora is teJt file (optional% that contain basic configuration details used by the <1L5et. It contain network configuration details such domain name& as what path to take in resolving then name of an instance& order of naming method& authentication services etc. listener.ora4 The normal location for this file is D:[oracle[ora3[network[admin. This file is client side file
(typically on remote +,%. The client uses this tnsname.ora file to obtain connection details from the desired database. tnsname.ora4 The normal location for this file is D:[oracle[ora3[network[admin. This file is located on both
client and server. If you make configuration changes on the server ensure you can connect to the database through the listener if you are logged on to the server. If you make configuration change on the client ensure you can connect from your client workstation to the database through the listener running on the server. What is the address of official oracle su""ort?
9etalink.oracle.com or support.oracle.com Is the "assword in oracle case sensitive?
In oracle )#g and earlier version W5OH and since ))g is W?EH What is the difference #etween I3*!11 and I3 *-T *!11 o"erators?
The I 5011 and I 5OT 5011 operators are used to find the 5011 and not 5011 values respectively. The I 5011 operator returns T0E& when the value is 5011= and -!1E& when the value is not 5011. The I 5OT 5011 operator returns T0E& when the value is not 5011= and -!1E& when the value is 5011.
Wh& dro" ta#le is not going into ec&cle #in?
If you are using ? user to drop any table then userHs obect will not go to the recyclebin as there is no recyclebin for ?TE9 tablespace& even we have already ET recycle bin parameter T0E. Select * from E6,?r?meter F>ere -?me ;reccle-; S>oF ,?r?meter reccle-
,ow to recover "assword in oracle 65g?
?ou can uery with the table .er_>.tor6. The password history is store in this table. ,ow to detect inactive session to 'ill automaticall&?
?ou can use the SLN"T!":(IR"_TI&" for the dead connections (for abnormal disconnections% by specifying a time interval in minute to send a problem message that verify clientserver connections are active. etting the value greater than # to this parameter ensures that connection is not left open indefinitely& due to abnormal client termination. If probe finds a terminated connection& or connection that is no longer in use& it returns an error& causing the server process to eJit. SLN"T!":(IR"_TI&"10
Wh& we need CA3CAD0 o"tion with D-P !30 command whenever dro""ing a user and wh& )D-P !30) commands fails when we donYt use it?
If a user having any obect then W?EH in that case you are not able to drop that user without using ,!,!DE option. The DO+ 0E with ,!,!DE option command drops user along with its all associated obects. emember it is a DD1 command after the eJecution of this command rollback cannot be performed. Can &ou suggest the #est ste"s to refresh a Data#ase?
efreshing the database is nothing but applying the change on one database (+OD% to another (Test%. ?ou can use importeJport and 9!5 method for this purpose. Im"ort08"ort %ethod4 If you database is small and if you need to refresh particular schema only then it is
always better to use this method. ).EJport the dump file from source D' 3.Drop and recreate Test environment 0ser. G.Import the dump to destination D'. %A* %ethod4 5ow days 9!5 is most likely to be used for backup and recovery. It is relatively easier and
better method for full database refresh to be refreshed. It is taking less time as compare to importeJport method. Cere also you can use particular ,5 based refreshing. VM.rM-MK.> e%,ort ORA"N5_AS';NO; e%,ort ORACL"_SID(RD M.rMloc?lM-Mor?e-E e%,ort NLS_LANAmerc?-_?merc?!.?.c e%,ort NLS_DAT"_#OR&AT=&o- DD )))) HH4&ISS= 6ORACL"_HO&"M-Mrm?- t?r@et M -oc?t?lo@ lo@Mtm,MG,lc?te_t?,e_T"ST!lo@ co--ect ?%l?r ..M(ASSWORDT"ST r-
?lloc?te ?%l?r c>?--el ?%1 GeEce t,e G.K .et -tl SCN 4179079 G,lc?te t?r@et G?t??.e to =T"ST= ,fle;M01M?,,M%%%%M,roGctM10!!0MG_1MG.M-tT"ST!or?;
NO#IL"NA&"CH"C'
X "O# U
,ow will we 'now the IP address of our s&stem in 1inu8 environment?
Either use ipconfig command or ip addr show It will give you all I+ address and if you have oracle i you can uery from <1 prompt. S"L"CT $TL_INADDR!"T_HOST_ADDR"SS =Ho.t AGGre..= $TL_INADDR!"T_HOST_NA&" =Ho.t N?me= #RO& D$AL
Can we create Bigfile Ta#les"ace for all data#ases?
Infact your uestion do we create bigfile tablespace for every database is not clear for me. If you are asking can we create bigfile for every databaseU ?es you can but it is not ideal for every datafile if your work is suitable for small file then why you create bigfile but if your mean is impact of bigfile that depends on your reuirements and storage. ! bigfile tablespace is having single very big datafile which can store 2K' to )34 T'. ,reating single large datafile reducing the reuirement of K! and also it will allow you modification at tablespace level. In fact it is ideal for !9& logical device supporting stripping. !void using bigfile tablespace where there is limited space availability. -or more details impact& advantage& disadvantage of bigfile on my blog. Can &ou gice more e8"lanation on logfile states?
D,0E5TY state means that redo records are currently being written to that group. It will be until a log switch occurs. !t a time there can be only one redo group current. If a redo group containing redoHs of a dirty buffer that redo group is said to be W!,TIPEH state. !s we know log file keep changes made to the data blocks then data blocks are modified in buffer cache (dirty blocks%. These dirty blocks must be written to the disk (!9 to permanent media%. !nd when a redolog group contains no redo records belonging to a dirty buffer it is in an I5!,TIPE state. These inactive redolog can be overwritten. One more state W050EDH initially when you create new redo log group its log file is empty on that time it is unused. 1ater it can be any of the above mentioned state. What is difference #etween oracle 3ID and -racle service name?
Oracle ID is the uniue name that uniuely identifies your instancedatabase where as the service name is the T5 alias can be same or different as ID. ,ow to find session for emote users?
"" To return session id on remote session: S"L"CT G.t-ct .G #RO& E6m.t?t
"" eturn session id of you in remote Environment:
Select .G from E6m.t?tremot_G F>ere roF-m1
We have a com"lete cold Bac'u" ta'en on 3unda&. The data#ase crashed on Wednesda&. *one of the data#ase files are availa#le. The onl& files we have are the ta"ed #ac'u" archive files till Wednesda&. Is there a "ossi#ilit& of recovering the data#ase until the recent archive which we have in the ta"e using the cold #ac'u".
?es& if you have all the archive logs since the cold backup then you can recover to your last log 3te"s4
)% estore all backup datafiles& and controlfile. !lso restore the password file and init.ora if you lost those too. Don>t restore your redo logs if you backed them up. 3% 9ake sure that ORACL"_SID is set to the database you want to recover G% startup mount= 2% ecover database using backup controlfile= !t this point Oracle should start applying all your archive logs& assuming that they>re in log8archive8dest *% alter database open resetlogs= Cow to check 9!5 version in oracleU If you want to check 9!5 catalog version then use the below uery from <1Lplus SL Select * from rcEer
If you want to check simply database version. SL Select * from E6Eer.o-
What is the minimum si>e of Tem"orar& Ta#les"ace?
)#2) A' Difference #w image co"ies and #ac'u" sets?
!n image copy is identical& byte by byte& to the original datafile& control file& or archived redo log file. 9!5 can write blocks from many files into the same backup set but canHt do so in the case of an image copy. !n 9!5 image copy and a copy you make with an operating system copy command such as dd (which makes image copies% are identical. ince 9!5 image copies are identical to copies made with operating system copy commands& you may use user"made image copies for an 9!5 restore and recovery operation after first making the copies XknownY to 9!5 by using the catalog command. ?ou can make image copies only on disk but not on a tape device. backup as copy database= Therefore& you can use the backup as copy option only for disk backups& and the backup as backupset option is the only option you have for making tape backups. ,ow can we see the C4Z drive free s"ace ca"acit& from 3Q1?
create an eJternal table to read data from a file that will be as below create '!T file free.#at as .etloc?l e-?lee%te-.o-. e-?le Gel?eGe%,?-.oec>o off for Mf =toKe-.3= ? - ;Gr cU;2 Go .et te.free? 2 .et te.freete.free
ec>o te.free e-Gloc?l YY .et te.freete.free
?ou can create a schedular to run the above free.bat& free8space.tJt inside the oracle directory. Differentiate #etween Tuning Advisor and Access Advisor?
The tuning !dvisor:
C
It suggests indeJes that might be very useful.
C
It suggests uery rewrites.
C
It suggests <1 profile
The !ccess !dvisor:
C
It suggest indeJes that may be useful
C
uggestion about materialiBed view.
C
uggestion about table partitions also in latest version of oracle.
,ow to give Access of "articular ta#le for "articular user? RANT S"L"CT "&(LO)""_N$&B"R2 $(DAT" A&O$NT2 ON HR&S!(A)_(A)&"NT_&AST"R TO SHAHID
The 'elow command checks the E1E,T privilege on the table +!?8+!?9E5T89!TE on the C9 schema (if connected user is different than the schema% S"L"CT (RI5IL"" #RO& ALL_TAB_(RI5S_R"CD WH"R" (RI5IL"" ;S"L"CT; AND TABL"_NA&" ;(A)_(A)&"NT_&AST"R; AND OWN"R ;HR&S; $NION ALL S"L"CT (RI5IL"" #RO& S"SSION_(RI5S WH"R" (RI5IL"" ;S"L"CT AN) TABL";
What are the "ro#lem and com"le8ities if we use 3Q1 Tuning Advisor and Access Advisor together?
I think both the tools are useful for resolving <1 tuning issues. <1 Tuning !dvisor seems to be doing logical optimiBation mainly by checking your <1 structure and statistics and the <1 !ccess !dvisor does suggest good data access paths& that is mainly work which can be done better on disk. 'oth <1 Tuning !dvisor and <1 !ccess !dvisor tools are uite powerful as they can source the <1 they will tune automatically from multiple different sources& including <1 cache& !@& <1 tuning ets and user defined workloads. elated with the argument compleJity and problem of using these tools or how you can use these tools together better to check oracle documentation.
Can &ou differentiate edo vs. oll#ac' vs. !ndo?
I find there is always some confusion when talking about edo& ollback and 0ndo. They all sound like pretty much the same thing or at least pretty close. edo: Every Oracle database has a set of (two or more% redo log files. The redo log records all changes
made to data& including both uncommitted and committed changes. In addition to the online redo logs Oracle also stores archive redo logs. !ll redo logs are used in recovery situations. oll#ac'4 9ore specifically rollback segments. ollback segments store the data as it was before changes
were made. This is in contrast to the redo log which is a record of the insertupdatedeletes. !ndo4 ollback segments. They both are really one in the same. 0ndo data is stored in the undo
tablespace. 0ndo is helpful in building a read consistent view of data. Alert. log showing this error U-A(665 signalled during4 alter data#ase closeV. What is the reason #ehind it?
The O!"))# error ust indicates that the database is not open for business. ?ou>ll have to open it up before you can proceed. It may be while you are shutting down the database& somebody trying to open the database respectively. It is a failure attempt to open the database while shutdown is progress.@ait for the time to successfully shutdown the database and open it again for use. !lternatively you have to restart your oracle services on windows environment. Which factors are to #e considered for creating inde8 on Ta#le? ,ow to select column for inde8?
,reation of indeJ on table depends on siBe of table& volume of data. If siBe of table is large and we need only few data for selecting or in report then we need to create indeJ. There are some basic reason of selecting column for indeJing like cardinality and freuent usage in where condition of select uery. 'usiness rule is also forcing to create indeJ like primary key& because configuring primary key or uniue key automatically create uniue indeJ. It is important to note that creation of so many indeJes would affect the performance of D91 on table because in single transaction should need to perform on various indeJ segments and table simultaneously. What is 3ecure 08ternal "assword 3tore 30P3@?
Through the use of E+ you can store password credentials for connecting to database by using a client side oracle wallet& this wallet stores signing credentials. This feature introduced since oracle )#g. Thus the application code& scheduled ob& scripts no longer needed embedded username and passwords. This reduces risk because the passwords are no longer eJposed and password management policies are more easily enforced without changing application code whenever username and password change. Differentiate DB file se=uential read waitDB File 3cattered ead?
euential read associated with indeJ read where as scattered read has to do with full table scan. The seuential read& reads block into contiguous memory and D' scattered read gets from multiple block and scattered them into buffer cache. I install oracle 65g on windows J successfull&. I found ever& thing wor'ing fine e8ce"t the toad is giving Ucannot load oci.dllV error. Is this com"ati#ilit& issue?
ead the toad user guide. ?ou will get important information related to compatibility issue. In fact toad works with both G3 bit and $2 bit oracle server where as toad only work with G3 bit client. If you need $2 bit client for
other applications& you can install both G3 bit and $2 bit client on a single machine and ust tell the toad to use the G3 bit client. What are the differences #etween Ph&sical1ogical stand#& data#ases? ,ow would &ou decide which one is #est suited for &our environment? Ph&sical stand#& DB4
C
!s the name& it is physically (datafiles& schema& other physical identity% same copy of the primary
database.
C
It synchroniBed with the primary database with !pply edo to the standby D'.
1ogical 3tand#& DB4
C
!s the name logical information is the same as the production database& it may be physical structure
can be different.
C
It synchroniBed with primary database though <1 !pply& edo received from the primary database
into <1 statements and then eJecuting these <1 statements on the standby D'.
C
@e can open Xphysical stand by D' to Xread onlyY and make it available to the applications users
(Only select is allowed during this period%. we can not apply redo logs received from primary database at this time.
C
@e do not see such issues with logical standby database. @e can open the database in normal mode
and make it available to the users. !t the same time& we can apply archived logs received from primary database.
C
-or O1T+ large transaction database it is better to choose logical standby database.
,ow to re(organi>e schema?
@e can use dbms8redefinition package for online re"organiBation of schema obects. Otherwise using importeJport and data pump utility you can recreate or re"organiBe your schema. To configure %A* Bac'u" for 6552B data#ase? ,ow we would estimate #ac'u" si>e and #ac'u" time?
,heck the actual siBe of your database. -or rman backup siBe almost depends on your actual siBe of database. SELECT SU"&B3TES)<67<67<67 !RO" DBA_SE>"ENTS; 'ackup time depends on your hardware configuration of your server such as ,+0& 9emory& and torage. 1ater you can also minimiBe the backup time by configuring multiple channels with the backup scripts. ,ow can &ou control num#er of datafiles in oracle data#ase?
The db8files parameter is a soft limit parameter that controls the maJimum number of physical O files that can map to an Oracle instance. The maJdatafiles parameter is a different " hard limit parameter. @hen issuing a create database command& the value specified for maJdatafiles is stored in Oracle control files and default value is G3. The maJimum number of database files can be set with the init parameter db8files. egardless of the setting of this parameter& maJimum per database: $**GG (9ay be less on some operating systems%& 9aJimum number of datafiles per tablespace: O dependent F usually )#33 ?ou can also by 1imited siBe of database blocks and by the D'8-I1E initialiBation parameter for a particular instance. 'igfile tablespaces can contain only one file& but that file can have up to 2K blocks. What is 1atches and wh& the& are used in oracle?
! latch is a serialiBation mechanism. It is used to gain access to shared data structure in order to latches the structure that will prevent others from modifying it while you are modifying it. Wh& it is not necessar& to ta'e !*D- #ac'u"?
In fact it is not necessary to take 05DO tablespace backup either with ,O1D or COT backup scripts but many of D'! include 05DO tablespace in their backup script. ?ou know when you do some transactions= redo entries will be generated and accepted Rust like that other tablespace whenever any change happens to 05DO tablespace or 05DO segments oracle will generate redo entries. o even you not backed up the 05DO tablespace& you have the redo entries through which you can recover or rollback the transactions. What should #e effect on DB "erformance if virtual memor& used to store 32 A "arameter?
-or optimal performance in most systems& the entire K! should fit in real memory. If it does not& and if virtual memory is used to store parts of it& then overall database system performance can decrease dramatically. The reason for this is that portions of the K! are paged (written to and read from disk% by the operating system. What is the role of locK_.@? "arameter?
The 1O,A8K! parameter& when set to T0E& locks the entire K! into physical memory. This parameter cannot be used with automatic memory management or automatic shared memory management. What is C33CA*?
,,!5 (Database ,haracter et canner% is a ,!5 tool that allows us to see the impact of a database character set change or assist us to correct an incorrect database nls8characterset setup. This helps us to determine the best approach for converting the database characterset. Differentiate #etween co(related su#(=uer& and nested =uer&?
,o"related sub uery is one in which inner uery is evaluated only once and from that result your outer uery is evaluated where as 5ested uery is one in which Inner uery is evaluated for multiple times for getting one row of that outer uery. #9ample: Buery used with IN() clause is ,o$related query*
SELECT E"PLO3EE_NU"BER( LOAN_CODE( DOCU"ENT_NU"BER( LOAN_A"OUNT !RO" PA3_LOAN_TRANS 5ERE E"PLO3EE_NU"BER IN &SELECT E"PLO3EE_NU"BER !RO" PA3_E"PLO3EE_PERSONAL_IN!O 5ERE E"PLO3EE_NU"BER BETEEN AND <<); #9ample: Buery used with C operator is Nested quer y
SELECT * !RO" PARTIAL_PA3"ENT_SEQUENCE 5ERE SEQCOD &SELECT "AF&SEQCOD) !RO" PARTIAL_PA3"ENT_SEQUENCE); -ne after noon suddenl& &ou get a call from &our a""lication user and com"laining the data#ase is slow then what will #e &our first ste" to solve this issue?
Cigh performance is common eJpectation for end user& in fact the database is never slow or fast in most of the case session connected to the database slow down when they receives uneJpected hit. Thus to solve this issue you need to find those uneJpected hit. To know eJactly what the second session is doing oin your uery with vMsession8wait.
SELECT NVL&.+er,#me( 2&or#cle)2) AS +er,#me( .0'( .er0#lG( ?.e=e,t( ?.?#0t_t0me( ?.eco,'_0,_?#0t( ?.t#te !RO" =$e0o,_?#0t ?( =$e0o, 5ERE .0' ?.0' #,' .+er,#me 25R"S2 ORDER B3 ?.eco,'_0,_?#0t DESC; ,heck the events that are waiting for something& try to find out the obects locks for that particular session. -ollow the link: -ind 1ocks : 'lockers 1ocking is not only the cause to effects the performance. Disk IO contention is another case. @hen a session retrieves data from the database datafiles on disk to the buffer cache& it has to wait until the disk sends the data. The wait event shows up for the session as Xdb file seuential readY (for indeJ scan% or Xdb file scattered readY (for full table scan%.
C
educe the number of blocks retrieved by the <1 statement. EJamine the <1 statement to see if it
is doing a full"table scan when it should be using an indeJ& if it is using a wrong indeJ& or if it can be rewritten to reduce the amount of data it retrieves.
C
+lace the tables used in the <1 statement on a faster part of the disk.
C
,onsider increasing the buffer cache to see if the eJpanded siBe will accommodate the additional
blocks& therefore reducing the IO and the wait.
C
Tune the IO subsystem to return data faster.
What is -racle data#ase firewall?
The database firewall has the ability to analyBe <1 statements sent from database clients and determine whether to pass& block& log& alert or substitute <1 statements& based on a defined policy. 0ser can set whitelist and blac'list policy to control the firewall. It can detect the inected <1Hs and block them. The database firewall can do the following: •
9onitor and block <1 traffic on the network with whitelist& blacklist and eJception list policies.
•
+rotect against application bypass& <1 inection and similar threats.
•
eport on database activity.
•
upports other database as well 9"<1 erver& I'9 D'3 and ybase.
Cowever there are some key issues that it does not address. -or EJample privilege user can login to the O directly and make local connections to the database. This bypasses the database firewall. -or these issues& would need use of other security options such as !udit Pault& P+D etc. What is -racle AC -ne *ode?
Oracle !, one 5ode is a single instance running on one node of the cluster while the 3 nd node is in cold standby mode. If the instance fails for some reason then !, one node detect it and restart the instance on the same node or the instance is relocate to the 3 nd node incase there is failure or fault in ) st node. The benefit of this feature is that it provides a cold failover solution and it automates the instance relocation without any downtime and does not need a manual intervention. Oracle introduced this feature with the release of ))g3 (available with Enterprise Edition%. What are invalid o#ects in data#ase?
ometimes schema obects reference other obects such as a view contains a uery that reference table or other view and a +1<1 subprogram invokes other subprograms or may reference another tables or views. These references are established at compile time and if the compiler cannot resolve them& the dependent obect being compiled is marked invalid. !n invalid dependent obect must be recompiled against the new definition of a referenced obect before the dependent obect can be used. ecompilation occurs automatically when the invalid dependent obect is referenced ,ow can we chec' DATAP!%P file is corru"ted or not?
ometimes we may be in situation& to check whether the dumpfile eJported long time back is P!1ID or not or our application team is saying that the dumpfile provided by us is corrupted. 0se <1-I1E +arameter with import script to detect corruption. The use of this parameter will read the entire datapump eJport dumpfile and will report if corruption is detected. m,G, ..temM*** GrectorGm,_Gr Gm,flee%,G,!Gm, lo@flecorr,to-_c>ecK!lo@ ./lflecorr,to-_c>ecK!./l
This will write all DD1 statements (which will be eJecuted if an import is performed% into the file which we mentioned in the command. ,ow can we find ela"sed time for "articular o#ect during Data"um" or 08"ort?
@e have an undocumented parameter WmetricsH in D!T!+09+ to check how much it took to eJport different obects types. "%,G, ..temMor?cle Grector Gm,_Gr Gm,fle e%,_fll!Gm, lo@fle
e%,_fll!lo@ fll metrc.
,ow to chec' oracle data#ase service is running in server?
D'! using this command on daily basis to find running oracle service on server On 1inuJ: ,. ef On @indows: T?.Kl.t M.Ec < f-G =or?cle=
,ow can we find different -3 #loc' si>e?
In oracle we can say that database block siBe should be multiple of O block siBe. On @indows: f.tl f.-fo -tf.-fo c < f-G M =te.= On 1inuJ: t-ef. l On olaris: Gf @ Mtm, ,ow to find location of -C file when C3 is down?
If you need to find the location of O, (Oracle ,luster egistry% but your , is down. 3hen the C2* is down:
1ook into Xocr!locY file& location of this file changes depending on the O: On 1inuJ: MetcMor?cleMocr!loc On olaris: ME?rMo,tMor?cleMocr!loc 3hen C2* is 4:
et !9 environment or , environment then run the below command: ocrc>ecK
,ow can &ou Test &our 3tand#& data#ase is wor'ing "ro"erl& or not?
To test your standby database& make a change to particular table on the production server& and commit the change. Then manually switch a logfile so those changes are archived. 9anually ship the newest archived redolog file& and manually apply it on the standby database. Then open your standby database in read"only mode& and select from your changed table to verify those changes are available. Once you have done& shutdown your standby and startup again in standby mode. What is Dataguard R what is the "ur"ose of Data 2uard?
Oracle Dataguard is a disaster recovery solution from Oracle ,orporation that has been utiliBed in the industry eJtensively at times of +rimary site failure& failover& switchover scenarios. a% Oracle Data Kuard ensures high availability& data protection& and disaster recovery for enterprise data. b% Data Kuard provides a comprehensive set of services that create& maintain& manage& and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions. c% @ith Data Kuard& administrators can optionally improve production database performance by offloading resource"intensive backup and reporting operations to standby systems. What is role of edo Trans"ort 3ervices in Dataguard?
It controls the automated transfer of redo data from the production database to one or more archival destinations. The redo transport services perform the following tasks: a% Transmit redo data from the primary system to the standby systems in the configuration.
b% 9anage the process of resolving any gaps in the archived redo log files due to a network failure. c% !utomatically detect missing or corrupted archived redo log files on a standby system and automatically retrieve replacement archived redo log files from the primary database or another standby database. Is -"atch utilit&@ is also another t&"e of "atch?
O+atch is utility from oracle corp. (Rava based utility% that helps you in applying interim patches to Oracle>s software and rolling back interim patches from Oracle>s software. Opatch also able to eport already installed interim patch and can detect conflict when already interim patch has been applied. This program reuires Rava to be available on your system and reuires installation of O0I. Thus from the above discussion coming to your uestion it is not ideal to say O+!T,C is another patch. When we a""l&ing single Patch$ can &ou use o"atch utilit&?
?es& you can use Opatch incase of single patch. The only type of patch that cannot be used with O+atch is a patchset When &ou a""l&ing Patchsets$ +ou can use -!I.
?es& +atcheset uses O0I. ! patch set contains a large number of merged patches& to change the version of the product or introduce new functionality. +atch sets are cumulative bug fiJes that fiJ all bugs and consume all patches since the last base release. +atch sets and the +atch et !ssistant are usually applied through O0I"based product specific installers. Can &ou A""l& -PATC, without downtime?
!s you know for apply patch your database and listener must be down. @hen you apply O+T!,C it will update your current O!,1E8CO9E. Thus coming to your uestion to the point in fact it is not possible without or Bero downtime in case of single instance but in !, you can !pply Opatch without downtime as there will be more separate O!,1E8CO9E and more separate instances (running once instance on each O!,1E8CO9E%. +ou have collection of "atch nearl& 655 "atches@ or "atchset. ,ow can &ou a""l& onl& one "atch from it?
@ith 5apply itself (by providing patch location and specific patch id% you can apply only one patch from a collection of eJtracted patch. -or more information check the opatch tl NA,,l Z>el,. It will give you clear picture. -or EJample: o,?tc> tl -?,,l J,?tc>_loc?to- G 9 .K,_..et .K,_G,lc?te
This will apply only the patch id from the patch location and will skip duplicate and subset of patch installed in your O!,1E8CO9E. If #oth CP! and P3! are availa#le for given version which one$ &ou will "refer to a""l&?
-rom the above discussion it is clear once you apply the +0 then the recommended way is to apply the neJt +0 only. In fact& no need to apply ,+0 on the top of +0 as +0 contain ,+0 (If you apply ,+0 over +0 will considered you are trying to rollback the +0 and will reuire more effort in fact%. o if you have not decided or applied any of the patches then& I will suggest you to go to use +0 patches. -or more details refer: Oracle +roducts \ID )2G#3G.)]& ID )22$*43.) P3! is su"erset of CP! then wh& someone choose to a""l& a CP! rather than a P3!?
,+0s are smaller and more focused than +0 and mostly deal with security issues. It seems to be theoretically more consecutive approach and can cause less trouble than +0 as it has less code changing in it. Thus any one who is concerned only with security fiJes and not functionality fiJes& ,+0 may be good approach. Will Patch A""lication affect 3&stem Performance?
ometimes applying certain patch could affect !pplication performance of <1 statements. Thus it is recommended to collect a set of performance statistics that can serve as a baseline before we make any maor changes like applying a patch to the system. What is &our da& to da& activit& as an A""s DBA?
!s an !pps D'! we monitor the system for different alerts (Entreprise 9anager or third party tools used for configuring the !lerts% Tablespace Issues& ,+0 consumption& Database blocking sessions etc.& egular maintenance activities like cloning& patching& custom code migrations (provided by developers% and @orking with user issues. ,ow often do &ou use "atch in &our organi>ation?
0sually for non"production the patching reuest comes around weekly 2"$ and the same patches will be applied to +roduction in the outage or maintenance window. +roduction has weekly maintenance window (eg. at $+9 to +9% where all the changes (patches% will applied on production. ,ow often do &ou use cloning in &our organi>ation?
,loning happens weekly or monthly depending on the organiBation reuirement. Kenerally when we need to perform maor task such as oracle financial annual closing etc.
What are the common Tas's or es"onsi#ilities for a Core DBA?
D'! responsibilities are varied from organiBation to organiBation. It depends on the organiBation nature of work. -ollowing are the overall responsibility for a D'!: ).0ser 9anagement: ,reate new user& remove eJisting user and provide the rights as per the reuirement. 3.9anage database storage (Timely space management of Tablespace or datafile% G.!dministrator users and security. 2.9anage chema obect. *.9onitor and 9anage database performance. $.+erform backup and recovery. N.chedule and automate obs. 4.Taking database snapshot or health report. .@orking with user issues for managing overall smooth running of database. What are &our da& to da& activities as an APP3 DBA?
In compare to ,ore D'! !pps D'! include all the responsibilities of ,ore D'! +lus 0pgrade& ,loning and +atching. !s an !pps D'! we monitor the system for different alerts (E9 or third party tools used for configuring the !lerts%& Tablespace issues& ,+0 consumption& Database blocking session etc. egular maintenance activities like cloning& patching and custom code migration (provided by developer%& working with user issues. What t&"e of failure occurs when oracle fails due to -3 or ,ardware failure?
Instance -ailure An -racle s&stem change num#er 3C*@4
!.is a value that is incremented whenever a dirty read occurs. '.is incremented whenever a deadlock occurs. ,.is a value that keeps track of eJplicit locks D.is a value that is incremented whenever database changes are madeU Answer4 D Which "rocess readwrite data from datafiles?
There is no background process which reads data from datafiles or database buffer. Oracle creates server process to handle reuest from connected user processes. ! server process communicates with the user
process and interacts with oracle to carry out reuest from the associated user process. -or eJample: If a user ueries some data not already in database buffer of the K!& then the associated server process reads the proper data block from the datafiles into the K!. D'@ background process is responsible to writes modified (dirty block from buffer cache to the datafiles% block permanently to disk. Wh& %A* incremental #ac'u" fails even though full #ac'u" e8ists?
If you have taken the 9!5 full backup using the command W'ackup databaseH& where as a level # backup is physically identical to a full backup. The only difference is that the level # backup is recorded as an incremental bac'up in the AN repository so it can be used as the parent for a level ) backup. imply
the Wfull backup without level #H can not be considered as a parent backup from which you can take level ) backup. ,ow can &ou change or rename the data#ase name? SL ALT"R DATABAS" BAC'$( CONTROL#IL" TO TRAC"
The above command will create a teJt control file in .er_Gm,_Ge.t directory and change name of the database in above file and also in -t!or? file. 5ow startup your database in nomount phase using the modified pfile and then run the modified controlfile script. SL START$( NO&O$NT SL DUB?cK,Uco-trolfle!t%t SL ALT"R DATABAS" O("N R"S"TLOS
?ou can use D'5E@ID utility 5ID for this purpose. -or more information: D'5E@ID& ,hanging D'5!9E Tem" Ta#les"ace is 655O F!11 and there is no s"ace availa#le to add datafiles to increase tem" ta#les"ace. What can &ou do in that case to free u" T0%P ta#les"ace?
Try to close some of the idle sessions connected to the database will help you to free some TE9+ space. Otherwise you can also use W Alter T?le.,?ce (CTINCR"AS" 1P followed by WAlter T?le.,?ce (CTINCR"AS" 0P
What is the use of setting 21-BA1G*A%03 e=ual to true?
etting LOBAL_NA&"S indicates how you might connect to the database. This variable is either W treH or Wf?l.eH. If it is set to WtrueH enforces database link to have same link as the remote database to which they are linking. What is the "ur"ose of fact and dimension ta#le? What t&"e of inde8 is used with fact ta#le?
-act and dimension tables are involved in producing a star schema. ! fact table contains measurements while dimension table will contain data that will help to describe the fact table. ! 'itmap indeJ is used with fact table. If &ou got com"lain a""lication is running ver& slow from &our a""lication user. Where do &ou start loo'ing first?
'elow are some of very important step to identify the root cause of slowness in !pplication database. •
un TO( command in 1inuJ to check ,+0 usage.
•
un 5&STAT SAR (RSTAT command to get more information on ,+0& memory usage and possible
blocking. •
C
un STATS(AC' report to identify TO( 7 Events and esource Intensive <1 statement. If found poor written statements then run WES+1!I5 +1!5H on these statements and see whether new
indeJ or use of CI5T brings the cost of <1 down. ,ow do &ou add second or su#se=uent B1-CN 3IH0 to an e8isting data#ase?
In fact the block siBe in an oracle database cannot be changed after the database is created. The reason is because oracle track lot of information based on block number. If you change the block siBe all the block number is changed and basically the database would have to re"create. 'ut in the case when you need to add second or subseuent BLOC'_SI" for particular datafile then you have to re"create the CONTROL#IL" to specify the new block siBe for specific datafiles.
or Take the database O##LIN"& and then bring back online with a new BLOC' SI" specification. +ou need to restore from #ac'u" and do not have an& control files. What will #e &our ste" to recover the data#ase?
,reate a teJt based control files& saved on the disk same location where all the datafiles are located then issue the recover command by using backup control file clause. S>tGoF- ?ort
f G .tll o,e-
St?rt, -omo-t cre?te co-trolfle G?t??.e J-?me lo@fle ;Jo-l-e reGo lo@ @ro,.; -ore.etlo@.Eelo@
SL ?lter G?t??.e mo-t recoEer G?t??.e [-tl c?-cel\ [.-@ ?cK, co-trolfle\ ?lter G?t??.e o,e- [[email protected]@.\
C 0se alter database open if you created the control file with 5OEET1OK and have performed no recovery or a full recovery (without until cancel%.
C 0se alter database open noresetlogs if you created the control file with 5OEET1OK and performed a full recovery despite the use of the until cancel option.
C 0se alter database open resetlogs if you created the control file with EET1OK or when you performed a partial recovery. In #elow list which 3Q1 "hrase is *-T su""orted #& oracle? A!ON D"L"T" CASCAD" B!ON $(DAT" CASCAD" C!CR"AT" S"$"NC" [Se/e-ceN?me\ D!DRO( S"$"NC" [Se/e-ceN?me\
Answer4 B What is the effect on wor'ing with e"ort when fle8confine mode are -*?
@hen fleJ mode is O5& reports automatically resiBe the parent when the child is resiBed. @hen the confine mode is O5& the obect cannot be moved outside its parent in layout. ,ow will &ou enforce securit& using stored "rocedure?
DonHt grant user access directly to tables within the application. Instead grant the ability to access the procedure that accesses the tables. @hen procedure eJecute it will eJecute the privilege of procedures owner. 0sers cannot access eJcept via the procedure. What is AC? What is the #enefit of AC over single instance data#ase?
In eal !pplication ,lusters environments& all nodes concurrently eJecute transactions against the same database. eal !pplication ,lusters coordinates each node>s access to the shared data to provide consistency and integrity. Benefits4
C
Improve response time
C
Improve throughput
C
Cigh availability
C
Transparency
Can &ou configure "rimar& server and stand#& server on different -3?
5O& tandby database must be on same version of database and same version of O. If &ou want users will change their "asswords after ever& L5 da&s then how &ou will enforce this?
Oracle password security is implemented through oracle (RO#IL"S which are assigned to users. (ASSWORD_LI#"_TI&" parameter limits the number of days the same password can be used for authentication. ?ou have to first create database (RO#IL" and then assign each user to this profile or if you have already having (RO#IL" then you need to ust alter the above parameter. cre?te ,rofle S?G>?-_.er. lmt (ASSWORD_LI#"_TI&" 0 (ASSWORD_RAC"_TI&" 10 (ASSWORD_R"$S"_TI&" $NLI&IT"D (ASSWORD_R"$S"_&A: 0 #AIL"D_LOIN_ATT"&(TS 3 (ASSWORD_LOC'_TI&" $NLI&IT"D
Then create user or already created user assigned to this profile. SL Cre?te .er HR&S Ge-tfeG or?cle ,rofle .?G>?-_.er.
If you have already assigned profile then you can directly modify the profile parameter: SL Alter ,rofle .?G>?-_.er. .et (ASSWORD_LI#"_TI&" 90
What ha""ens actuall& in case of instance ecover&?
@hile Oracle instance fails& Oracle performs an Instance ecovery when the associated database is being re"started. Instance recovery occurs in two steps:
Cache recover&4 ,hanges being made to a database are recorded in the database buffer cache as well as redo log files simultaneously. @hen there are enough data in the database buffer cache& they are written to data files. If an Oracle instance fails before these data are written to data f iles& Oracle uses online redo log files to recover the lost data when the associated database is re"started. This process is called cache recovery.
Transaction recover&4 @hen a transaction modifies data in a database (the before image of the modified data is stored in an undo segment which is used to restore the original values in case the transaction is rolled back%. !t the time of an instance failure& the database may have uncommitted transactions. It is possible that changes made by these uncommitted transactions have gotten saved in data files. To maintain read
consistency& Oracle rolls back all uncommitted transactions when the associated database is re"started. Oracle uses the undo data stored in undo segments to accomplish this. This process is called transaction recovery. What is the main "ur"ose of [C,0CNP-I*TE in oracle data#ase?
! checkpoint is a database event& which synchroniBe the database blocks in memory with the datafiles on disk. It has two main purposes: To establish a data consistency and enable faster database ecovery. -or more information: Discussion on ,heckpoint and ,5 Can &ou change the Characterset of data#ase?
5o& you can not change the character set of database& you will need to re"create the database with appropriate characterset. What is Cascading stand#& data#ase?
! ,!,!DI5K T!5D'? is a standby database that receives its EDO information from another standby database (not from primary database%. What the use of A*A1+H0 command?
To collect statistics about obect used by the optimiBer and store them in the data dictionary& delete statistics about the obect& validate the structure of the obect and identify migrated and chained rows of the table or cluster. ,ow will &ou chec' active shared memor& segment? ,c. ?
,ow will &ou chec' "aging swa""ing in 1inu8? Em.t?t . ,r.t?t . .F?, l .?r ,
,ow do &ou chec' num#er of CP! installed on 1inu8 server? ,.r-fot E
When &ou moved oracle #inar& files from one - AC10G,-%0 server to another server then which oracle utilit& will #e used to ma'e this new -AC10G,-%0 usa#le? Rel-K ?ll
In which months oracle release CP! "atches? ]AN A(R ]$L OCT
-racle version .<.5..5 what does each num#er refers to?
Oracle version number refers: 9aor database release number 3 Database 9aintenance release number # !pplication server release number 2 ,omponent pecific release number # +latform specific release number What does data#ase do during the mounting "rocess?
@hile mounting the database oracle reads the data from controlfile which is used for verifying physical
database files during sanity check. 'ackground processes are started before mounting the database only. When having multi"le oracle homes on a single server or client what is the "arameter that "oints all -racle installs at one T*3*A%03.-A file.
T58!D9I5 ,ow to im"lement the multi"le controlfile for e8isting data#ase?
).
Edit -t!or? file& set controlfiles parameter with multiple location
3.
hutdown immediate
G.
,opy controlfile to multiple locations Z confirm from -t!or? contolfiles parameter
2.
tart the database.
*.
0se the below uery for changes confirmation
.elect -?me from E6co-trolfle
Wh& we loo' for C,!*N3GF00 s"ace while trac'ing fragmentation details =uer&?
The ,C05A8-EE return the number of chunks of contiguous free space based on dba8free8space table. The motive is to find the largest siBe chunks of free space within a tableapce. This is because as we know oracle server allocates space for segments in unit of one eJtent. @hen the eJisting eJtent of segment is full& the server allocates another eJtent for the segment. In order to do oracle searches free space in the tablespace (contiguous set of data block sufficient to meet the reuired eJtent%. If sufficient space not found then an error is returned by the oracle server. What is the im"act of *13Characterset in data#ase?
51 is a 5ational language support and encompasses how to display currency& whenever we use a comma or a dot to separate numbers& how the name of the day is spelled etc .
,haractersets are how we store data. -or EJample: $SASCII is a Nbit characterset and W"8ISO8879(1 4 bit character set. It can store 3 times as many characters as the Nbit characterset. If you try to eJport from 4 bit characterset database and import into Nbit database then there is chance to loose data in Nbit characterset that have the high bit set and if you try from Nbit to 4bit would not encounter any issues since the Nbit characterset is a subset of the 4bit characterset and can hold more types of characters and can support many countries. Can we "erform %A* level 6 #ac'u" without level 5?
If no level # is available& then the behavior depends upon the compatibility mode setting (oracle version%. If the compatibility mode less than )#.#.#& 9!5 generates a level # backup of files contents at the time of backup. If compatibility is greater than )#.#.# 9!5 copies all block changes since the file was created& and stores the results as level ) backup. What will ha""en if AC,I/0 "rocess cannot co"& an archive redolog to a mandator& archive log destination?
Oracle will continue with cycle to the other online redolog groups until it return to the group that the !,C process is trying to copy to the mandatory archive log destination. If the mandatory archive log destination copy has not occurred& the database operation will suspend until the copy is successful or the D'! has intervened to perform force log switching. Can &ou differentiate #etween ,-TBACN!P and %A* #ac'u"?
-or hotbackup we have to put database in begin backup mode& then take backup where as 9!5 would not put database in begin backup mode. In fact 9!5 has a number of advantages over general backup. -or more information please check: 'enefit of 9!5 'ackup ,ow to "ut %anual!ser managed #ac'u" in %A*?
In case of recovery catalog& you can put by using catalog command: R&AN CATALO START WITH QMor??cKM?cK,!ctlP
When &ou "ut an& 3Q1 statement how oracle res"onds them internall&?
-irst it will check the syntaJ and semantics in library cache& after that it will created eJecution plan. If already data in buffer cache (in case of identical uery% it will directly return to the client. If not it write the fetch to the database buffer cache after that it will send server and finally server send to the client. Can we use 3ame target data#ase as Catalog?
5o& the recovery catalog should not reside in the target database (database to be backed up% because the
database can not be recovered in the mounted state. Differentiate the use of what are P2A and !2A?
@hen you are running dedicated server then process information stored inside the process global area (+K!% and when you are using shared server then the process information stored inside user global area (0K!%. ,ow do &ou automaticall& force the oracle to "erform a chec'"oint?
The following are the parameter that will be used by D'! to adust time or interval of how freuently its checkpoint should occur in database. LO_CH"C'(OINT_TI&"O$T 300
V "Eer o-e >or
LO_CH"C'(OINT_INT"R5AL 1000 V -mer of OS locK.!
What is Cluster ta#le in -racle data#ase?
! ,luster is a schema obect that contains one or more tables that all have one or more common columns. ows of one or more tables that share the same value in these common columns are physically stored together within the database. Kenerally& you should only cluster tables that are freuently oined on the cluster key columns in <1 statements. ,lustering multiple tables improves the performance of oins& but it is likely to reduce the performance of full table scans& I5ET and 0+D!TE statements that modify cluster key values. Can &ou differentiate #etween com"lete and incom"lete recover&?
!n incomplete database recovery is a recovery that it does not reach to the point of failure. The recovery can be either point of time or particular ,5 or +articular archive log specially incase of missing archive log or redolog failure where as a complete recovery recovers to the point of failure possibly when having all archive log backup. What is difference #etween %A* and Traditional Bac'u"?
9!5 is faster can perform incremental (changes only% backup& and does not place tablespace in hotbackup mode. ,heck: 'enefit of 9!5 'ackup What are #ind varia#les and wh& are the& im"ortant?
@ith bind variable in <1& oracle can cache ueries in a single time in the <1 cache area. This avoids a hard parse each time& which saves on various locking and latching resource we use to check obect eJistence and so on. ,ow to recover data#ase without #ac'u"?
If flash recovery is enabled then we can recover database without having backupU Otherwise we cannot recover database without backup. ,ow to write e8"licit cursor to avoid oracle e8ce"tion4 noGdataGfound and tooGman&Grows?
In +1<1 if you try to write select statement with into clause it may return two eJception -o_G?t?_fo-G and too_m?-_roF. to avoid this eJception you have to write eJplicit cursor. "%ce,to- BlocK W>e- -o_G?t?_fo-G MM (t or coGe W>e-_too_m?-_roF. MM ,t or coGe
W>e- ot>er. t>eMM ,t or coGe "-G
What are differences #etween eference cursor and cursor?
eference cursor gives the address of the location instead of putting item directly. It holds the different type of structures. 5ormal cursor holds one structure of table. eference cursor is a dynamic cursor where as normal cursor is static cursor. In dynamic cursor single statement are process multiple select statement dynamically at run time where as in normal cursor we process only one select statement. What is Pi"eline view?
In case of normal views whenever you call the view it will get data from the base table where as in case of pipeline view if you call the view it will get data from another intermediate view. ,ow would &ou find the "erformance issue of 3Q1 =ueries?
C Enable the trace file before running your ueries C Then check the trace file using tK,rof create output file. C !ccording to eJplain plan check the el?,.eG tme for each uery C Then tune them respectively. What is difference #etween ecover& and estoring of data#ase?
estoring means copying the database obect from the backup media to the destination where actually it is reuired where as recovery means to apply the database obect copied earlier (roll forward% in order to bring the database into consistent state. What are the So#s of 3%-* and P%-* "rocesses? 3%-* ystem 9onitor performs recovery after instance failure& monitor temporary segments and eJtents=
clean temp segment& coalesce free space. It is mandatory process of D' and starts by default. P%-* +rocess 9onitor failed process resources. In shared server architecture monitor and restarts any
failed dispatcher or server process. It is mandatory process of D' and starts by default. When &ou should re#uild inde8?
In fact in #/ case never. @hen the data in indeJ is sparse (lot of holes in indeJ& due to delete and updates% and your uery is usually ranged based. !lso indeJ '1EPE1 is one of the key indicators of performance of <1 ueries doing indeJ range scan. What is 'e& "reserved ta#le?
! table is set to be key preserved table if every key of the table can also be the key of the result of the oin. It guarantees to return only one copy of each row from the base table. Which of the following is *-T an oracle su""orted trigger? A!Before B!Dr-@ C!After D!I-.te?G of
Answer4 B
Which of the following is *-T true a#out modif&ing ta#le column? A!)o c?- Gro, ? colm- ?t ?- tme! B!)o c?- ?GG ? colm- ?t ?- tme ?. lo-@ ?. t . ? N$LL colm-! C!)o c?- -cre?.e t>e -mer of c>?r?cter. - c>?r?cter colm-. or -mer of G@t. - -merc colm-.! D!)o c?- -ot -cre?.e or Gecre?.e t>e -mer of Gecm?l ,l?ce.!
Answer4 D ,ow can &ou find 3Q1 of the Currentl& Active 3essions?
,ompare tables 56SL view by <18address with 56S"SSION view of currently active sessions If &ou have A3% data#ase that used #& different "roduction s&stems immediatel& shutdown then what ha""ens to the "roduction s&stem?
In that case the other database would need to .>tGoF- ?ort. ,ow do &ou move ta#le from one ta#les"ace to another ta#les"ace?
?ou can use any of the below method to do this: ).
EJport the table& drop the table& create definition of table in new tablespace and then import the data
using (m, @-ore%. 3.
,reate new table in new tablespace then drop the original table and rename temporary table with
original table name. CR"AT" TABL" tem,_-?me TABL"S(AC" -eF_t?le.,?ce ?. .elect * from Q.orce_t?leP DRO( TABL" re?l_t?le R"NA&" tem,_-?me to re?l_t?le
,ow would &ou decide &our #ac'u" strateg& and timing for #ac'u"?
In fact backup strategy is purely depends upon your organiBation business need. If no downtime then database must be run on archivelog mode and you have to take freuently or daily backup. If sufficient downtime is there and loss of data would not affect your business then you can run your database in archivelog mode and backup can be taken in"freuently or weekly or monthly. In most of the case in an organiBation when no downtime then freuent inconsistent backup needed (daily backup%& multipleJ online redo log files (multiple copies%& different location for redo log files& database must run in archivelog mode and dataguard can be implemented for eJtra bit of protection (to make less downtime during recovery%. What is Sinitiator and what its "ur"ose?
It is a ava virtual machine provided for running web based oracle forms applications inside a client web browser. It is implemented as a plug"in or !ctiveS obect& allows you to specify the use of oracle certified RP9 instead of relying on default RP9 provided by browser. It is automatically downloaded to a client machine from the application. Its installation and update is performed by standard plug"in mechanism provided by the browser. What is the use of large "ool$ which case &ou need to set the large "ool?
?ou need to set large pool if you are using: 9T (9ulti thread server% and 9!5 'ackups. 1arge pool prevents 9!5 Z 9T from competing with other sub system for the same memory. 9!5 uses the large pool for backup Z restore when you set the D'@8IO81!PE or '!,A0+8T!+E8IO81!PE parameters to simulate asynchronous IO. If neither of these parameters is enabled& then Oracle allocates backup buffers from local process memory rather than shared memory. Then there is no use of large pool. ,ow can &ou audit s&stem o"erations?
ys connection can be audited by setting -t!or? parameter A$DIT_S)S_O("RATIONSTR$" ,ow can &ou im"lement 0ncr&"tion in data#ase?
Data with database can be encrypted and decrypted using package: DB&S_OB#$SCATION_TOOL'IT ,ow do &ou list the folder files with hidden file in 1inu8 . Zltr?
,ow to e8ecute 1inu8 command in Bac'ground? $.e t>e =Y= ?t t>e e-G of comm?-G or .e -o>, comm?-G
What 1inu8 command will control the default "ermission when file are created? $m?.K
2ive the command to dis"la& s"ace usage on the 1I*!: file s&stem? Gf ZlK
What is the use of iostatvmstatnetstat command in 1inu8? Iostat reports on terminal& disk and tape IO activity. /mstat reports on virtual memory statistics for processes& disk& tape and ,+0 activity. *etstat reports on the contents of network data structures. What are the ste"s to install oracle on 1inu8 s&stem. 1ist two 'ernel "arameter that effect oracle installation?
Initially set up disks and kernel parameters& then create oracle user and D'! group& and finally run installer
to start the installation process. The SH&&A: Y SH&&NI two kernel parameter reuired to set before installation process. GGGGGGGGGG Parameter change will decrease Paging3wa""ing? A-.Fer Decre?.e_S>?reG_(ool_.e
GGGGGGG Command is used to see the contents of 3Q1X Plus #uffer A-.Fer LIST
Transaction "er roll#ac' segment is derived from GGGGGGGG A-.Fer (roce..e.
12W "rocess writes information into GGGGGGGGGGG A-.Fer ReGo lo@ fle.!
A data#ase over all structure is maintained in a file GGGGGGGGGG A-.Fer Co-trol fle.
What is the use of */1 function?
The 5P1 function is used to replace 5011 values with another or given value. -or EJample: 5P1 (Palue& replace value%= What is WIT, C,0CN -PTI-*?
The @ITC ,CE,A option clause specifies check level to be done in D91 statements. It is used to prevent changes to a view that would produce results that are not included in the sub uery. The concepts are different than previous concept in fact. In that case you can access the some of the concept in your mind to achieve the target. ,ow can &ou trac' the "assword change for a user in oracle?
Oracle only tracks the date that the password will eJpire based on when it was latest changed. Thus listing the view DBA_$S"RS!":(IR)_DAT" and subtracting (ASSWORD_LI#"_TI&" you can determine when password was last changed. ?ou can also check the last password change time directly from the +TI9E column in 0EM table (on which D'!80E view is based%. 'ut If you have (ASSWORD_R"$S"_TI&" andor (ASSWORD_R"$S"_&A: set in a profile assigned to a user account then you can reference dictionary table $S"R_HISTOR)6 for when the password was changed for this account. S"L"CT .er6!NA&" .er6!(ASSWORD .er6!,tme .er_>.tor6!,?..ForG_G?te #RO& S)S!.er_>.tor6 S)S!.er6 WH"R" .er_>.tor6!.erV .er6!.erV
What is the difference #etween a data #loc'e8tentsegment?
! data block is the smallest unit of logical storage for a database obect. !s obects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called eJtents. !ll the eJtents that an obect takes when grouped together are considered the segment of the database obect. What is the difference #etween 3Q1Xloader and Im"ort utilities?
'oth these utilities are used for loading the data into the database. The difference is that the import utility relies on the data being produced by another oracle utility EJport while <1L1oader is a high speed data loading mechanism allows data to be loaded that has been produced by other utilities from different data
source. Import is mainly used reading and writing operating system files. Can &ou list the 3te" how to create 3tand#& data#ase?
).Take a full hot backup of +rimary database 3.,reate standby control file
E8Transfer full backup& -t!or?& standby control file to standby node. 489odify -t!or? file on standby node. *.estore database $.ecover tandby database
F8(!lternatively& R&AN D$(LICAT" DATABAS" #OR STANDB) DO R"CO5"R) can be also used% G8etup #AL_CLI"NT and #AL_S"R5"R parameters on both sides .+ut tandby database in 9anaged ecover mode ,ow would &ou activate Ph&sical 3tand#& data#ase in oracle i?
+erform below on primary database if available to transfer all pending archive logs to standby: SL ALT"R S)ST"& SWITCH LO#IL" SL ALT"R S)ST"& SWITCH LO#IL"
5ow perform below on T!5D'? database: SL ALT"R DATABAS" ACTI5AT" STANDB) DATABAS" SL SH$TDOWN I&&"DIAT" SL START$(
*ote4 /nce you start the .tandby 4+& your relation between primary databases to standby database has been lost and at this time your standby database becomes primary database*
,ow to 3witch from Primar& to Ph&sical 3tand#& data#ase?
+erform below step on +rimary Database: SL ALT"R DATABAS" CO&&IT TO SWITCHO5"R TO (H)SICAL STANDB) WITH S"SSION SH$TDOWN SL SH$TDOWN I&&"DIAT" SL START$( NO&O$NT SL ALT"R DATABAS"
DATABAS"
SL R"CO5"R &ANA"D STANDB) DATABAS" DICONN"CT #RO& S"SSION SL ALT"R S)S"& S"T LO_ARCHI5"_D"ST__STAT$S D"#"R SCO("S(#IL"
+erform below steps on econdary Database: SL ALT"R DATABAS" CO&&IT TO SWITCHO5"R TO (RI&AR) SL SH$TDOWN I&&"DIAT" SL START$( SL ALT"R S)S"& S"T LO_ARCHI5"_D"ST__STAT$S "NABL" SCO("S(#IL"
,ow will &ou list onl& the em"t& lines in a file using 20P@ R"( =^6= fle-?me!t%t
,ow will &ou shutdown &our data#ase if 3,!TD-W* I%%0DIAT0 command is alread& tried and failed to shutdown the data#ase?
'll t>e S&ON ,roce..!
What is log switch?
The point at which oracle ends writing to one online redo log file and begins writing to another is called a log switch. ometimes you can force the log switch by using the command: !1TE ?TE9 1OK @IT,C. ,ow can &ou "ass the ,I*T3 to the 3Q1 "rocessor?
0sing comment line with +2 sign you can pass the CI5T to the <1 engine: -or EJample: M* +(ARALL"L2 *M
2ive 08am"le of availa#le DB administrator utilities with their functionalit&? SL * DBA It allows D'! to monitor and control an oracle database. SL * Lo?Ger Z It loads data from standard O files or flat file in oracle database tables. "%,ortMIm,ort Z It allows moving eJisting data in oracle format to and from oracle database.
Can &ou #uilt inde8es online?
?E. ?ou can create and rebuild indeJes online. This enables you to update base tables at the same time you are building or rebuilding indeJes on that table. ?ou can perform D91 operations while the indeJ building is taking place& but DD1 operations are not allowed. +arallel eJecution is not supported when creating or rebuilding an indeJ online. CR"AT" IND": em,_-?me ON em, m@r em,1 em, em,32 ONLIN"
If an oracle data#ase is crashed? ,ow would &ou recover that transaction w hich is not in #ac'u"?
If the database is in archivelog we can recover that transaction otherwise we cannot recover that transaction which is not in backup. What is the #enefit of running the DB in archivelog mode over no archivelog mode?
@hen a database is in no archivelog mode whenever log switch happens there will be a loss of some redoes log information in order to avoid this& redo logs must be archived. This can be achieved by configuring the database in archivelog mode. What is 32A? Define structure of shared "ool com"onent of 32A?
The system global area is a group of shared memory area that is dedicated to oracle instance. !ll oracle process uses the K! to hold information. The K! is used to store incoming data and internal control information that is needed by the database. ?ou can control the K! memory by setting the parameter db
command available with oracle. SLor?Ge@ .etm,G SLor?Ge@ ,c SLor?Ge@ tr?cfle_-?me
,ow would &ou e8tract DD1 of a ta#le without using a 2!I tool?
Select Gm._met?G?t?!@et_GGl;OB]"CT;;OB]"CT_NA&";2 from G?l
If &ou are getting high UBus& Buffer waitsV then how can &ou find the reason #ehind it?
'uffer busy wait means that the ueries are waiting for the blocks to be read into the db cache. There could be the reason when the block may be busy in the cache and session is waiting for it. It could be undodata block or segment header wait. un the below two uery to find out the +)& +3 and +G of a session causing buffer busy wait then after another uery by putting the above +)& +3 and +G values. SL Select ,1 =#le V=, =BlocK V=,3 =Re?.o- CoGe= from E6.e..o-_F?t W>ere eEe-t ;ffer . F?t.; SL Select oF-er .e@me-t_-?me .e@me-t_t,e from G?_e%te-t. W>ere fle_G Y(1 ?-G Y( etFee- locK_G ?-G locK_G + locK. 1
Can flash#ac' wor' on data#ase without !*D- and with roll#ac' segments?
5o& flashback uery enable us to uery our data as it eJisted in a previous state. In other words& we can uery our data from a point in time before any other users made permanent changes to it. Can we have same listener name for two data#ases?
5o
Basic 3Q1 Question with Answer D!$$ere%"!a"e SL a%# SL,l+s. @. "4L is a lan/+a/e where as I"4L^Pl+s is an en,ironment onl$ 2. "4L is an A'"I standard where ad I"4L^Pl+s is Oracle Proprietar$ onl$ R. In "4L 3e$word cannot be abbre,iated where as in I"4L^Pl+s 3e$word can be abbre,iated.
48 "tatements manip+late data and able definition is possible where as incase of I"4L^Pl+s statement does not manip+late data and table definition is also not possible 8
De$!%e N+ll !% "er- o$ SL. A n+ll is a ,al+e that is +nassi/ned* +n3nown or inapplicable. A n+ll is not the same as Zero. Arithmetic e#pression containin/ n+ll ,al+e e,al+ate to n+ll.
Ho -a%0 "0,es o$ +er!es T0,es !% SL. "election* Proection* oin table
1)a" !s Co%s"ra!%"s. Constraints are +sed to restrict or bo+nd inefficient data. Basicall$ two t$pes of constraints are pro,ided : col+mn constraints are associated with a sin/le col+mn ;here as table constraints are t$picall$ associated with more than one col+mn. Inte/rit$ Constraints@: @. Primar$ Ke$. Uni-+e Y 'ot '+ll 2. 'ot '+ll.Cannot be '+ll R. Uni-+e Ke$Uni-+e can be '+ll
D!$$ere%ce &e"ee% Pr!-ar0 a%# U%!+e *e0. Primar$ Ke$ Defines a col+mn ,al+e Uni-+e as well as 'ot '+ll where as Uni-+e 3e$ defines a col+mn ,al+es onl$ Uni-+e. Means it can accept n+ll ,al+e. A able ma$ ha,e onl$ one primar$ 3e$ where as more than one +ni-+e 3e$ can be assi/ned within a table.
L!s" ")e D!$$ere%ce &e"ee% Dele"e a%# Tr+%ca"e. D010T04
). DE1ETE is a D91 ,ommand. 3. DE1ETE statement is eJecuted using a row lock& each row in the table is locked for deletion. G. @e can specify filters in where clause 2. It deletes specified data if where condition eJists. *. Delete activates a trigger because the operations are logged individually. $. lower than truncate because& it keeps logs. N. ollback is possible. T!*CAT0:
). T05,!TE is a DD1 command. 3. T05,!TE T!'1E always locks the table and page but not each row. G. ,annot use @here ,ondition.
2. It removes all the data. *. T05,!TE T!'1E cannot activate a trigger because the operation does not log individual row deletions. $. -aster in performance wise& because it doesn>t keep any logs. N. ollback is not possible.
D!$$ere%ce Co--!" or I%"er%al Co--!". As lon/ as the +ser has not iss+ed the commit statement* it is possible to +ndo all modifications since the last commit. o +ndo modifications* one has to iss+e the statement rollbac3 'ote that an$ data definition command s+ch as crea"e "a&leres+lts in an internal commit. A commit is also implicitl$ e#ec+ted when the +ser terminates an Oracle session.
1)a" !s O+"er o!%. 6iew data that does not /enerall$ meet a oin condition b$ +sin/ o+ter oin.
D!$$ere%ce &e"ee% E+!?o!% a%# No%E+!?o!%. E-+ioin is possible when same col+mn name appears in more than one table th+s there is a need of primar$ 3e$ or forei/n 3e$ where as in case of 'on e-+ioin there is no need of same col+mn in both the table. E-+ioin possible for more than 2 table where as limit for onl$ 2 table.
1)a" !s Na"+ral o!%. It based on all col+mns in two tables that ha,e the same name or if the col+mn ha,in/ the same name b+t its data t$pe is different then it ret+rns an error.
1)a" !s Us!%' Cla+se ?o!%. If the col+mn ha,e the same name b+t data t$pe is different incase [+sin/ cla+se\ is implemented. It can match onl$ for one col+mn incase more than one col+mn are same.
D!$$ere%ce I%%er /s O+"er o!%. he oin of two tables that ret+rn onl$ matched res+lt is 3nown as inner oin ;here as o+ter oin ret+rns the inner matched res+lt of inner oin as well as +nmatched ret+rn of left and ri/ht table.
De$!%e SL $+%c"!o%. D!$$ere%"!a"e &e"ee% S!%'le ro F+%c"!o% /s M+l"!,le Ro F+%c"!o%. M+ltiple f+nctions can manip+late on /ro+p of rows to /i,e one res+lt per /r o+p of rows where as a sin/le row f+nction can operate on a sin/le row onl$ and ret+rn o ne res+lt per row. "in/le row f+nction is +sed to manip+late data item. he$ accept one or more data item and ret+rn one ,al+e for each row. he sin/le row f+nction can be nested. 'estin/ can be e,al+ated from deeper to hi/her. $pes of "in/le !ow f+nctions @. Character 8+nctions a) Case Manip+lation 8+nction LO;E!* UPPE!* I'ICAP b) Character Manip+lation 8+nction CO'CA*"UB"!*LE'&0*I'"!*LPAD* !PAD*!IM*!EPLACE 2. '+mber 8+nctions
!OU'D* !U'C* MOD R. Date 8+nctions ">"DAE*MO'0"BE;EE'*ADDMO'0"*'EWDA>*LA"DA>*!OU'D*!U'C Q. Con,ersion a) Implicit Data t$pe Con,ersion 6A!C0A! O! C0A! O 'UMBE!*6A!C0A! O! C0A! O DAE b) E#plicit Data t$pe Con,ersion O'UMBE!*OC0A!*ODAE X. &eneral 8+nctions '6L*'6L2*'ULLI8*COALE"CE*DECODE*CA"E
N2L FUNCTION he '6L f+nction is +sed to con,ert n+ll ,al+es to act+al ,al+es* data t$pe m+st match incase of '6L f+nction. he date* char and n+mber data t$pe can be +sed in case of '6L.
N2L> FUNCTION he '6L2 f+nction e#amines the first e#pression* if the first e#pression is not n+ll then the '62 f+nction ret+rns the second e#pression. If the first e#pression is n+ll then it ret+rn the third e#pression.
NULLIF FUNCTION he 'ULLI8 f+nction compares the two e#pression if the$ are e-+al then ret+rn n+ll otherwise it ret+rn the first e#pression.
COALESCE FUNCTION he COALE"CE 8U'CIO' ta3es n n+mber of ar/+ments. It ret+rns the @ st e#pression if it is not n+ll. It ret+rns the 2 nde#pression if the first e#pression is n+ll and 2 nd e#pression is not n+ll. It ret+rns the nth e#pression if all of the abo,e e#pression is n+ll and last e#pr ession is not n+ll.
CASE FUNCTION CA"E E#pression facilitates conditional in-+iries b$ doin/ the wor3 of an I8 0E' EL"E statement. It compares the e#pression if the condition is matched ret+rn that e#pression. If none of then match the condition it ret+rn the else part. his f+nction wor3ed on A'"I standard. his feat+re introd+ce in oracle 9i.
DECODE FUNCTION Decode f+nction is the similar to CA"E f+nction onl$ difference of s$nta#. It also wor3ed as I8 then else statement.
To see c+rre%" +ser %a-e "-l5 show +ser
C)a%'e SL ,ro-," %a-e "4L5 set s-lprompt [Manimara 5 [ Manimara 5
S!"c) "o DOS ,ro-," "4L5 host
Ho #o I el!-!%a"e ")e #+,l!ca"e ros $ro- a "a&le. "4L5 delete from tablename where rowid not in (select ma#(rowid) from table /ro+p b$ d+plicate,al+esfieldname) or "4L5 delete d+plicate,al+esfieldname d, from tablename ta where rowid (select min(rowid) from tablename tb where ta.d,tb.d,) "4L5 delete ename from emp a where rowid ( select min(rowid) from emp b where a.ename b.ename)
Ho #o I #!s,la0 ro %+-&er !") recor#s. "4L5 select rown+m* ename from emp
D!s,la0 ")e recor#s &e"ee% "o ra%'e select rown+m* empno* ename from emp where rowid in (select rowid from emp where rown+m +pto min+s select rowid from emp where rown+m"tart) Enter ,al+e for +pto: @ Enter ,al+e for "tart: 1
T)e %/l $+%c"!o% o%l0 allos ")e sa-e #a"a "0,e 6!e8 %+-&er or c)ar or #a"e N/l6co-- 577 !$ co--!ss!o% !s %+ll ")e% ")e "e9" @No" A,,l!ca&le a%" "o #!s,la0 !%s"ea# o$ &la%* s,ace8 Ho #o I r!"e ")e +er0. "4L5 select n,l(tochar(comm.)*'A) from emp O+tp+t : '6L(OC0A!(COMM)*'A) <<<<<<<<<<<<<<<<<<<<<<< 'A R X 'A
D!$$re%ce &e"ee% I-,l!c!" E9,l!c!" c+rsors . 8or "4L -+eries ret+rnin/ a sin/le row* PL="4L declares all implicit c+rsors. 8or -+eries that ret+rnin/ more than one row* the c+rsor needs to be e#plicitl$ declared.
F!%# o+" %") )!')es" salar0 $ro- e-, "a&le "ELEC DI"I'C (a.sal) 8!OM EMP A ;0E!E ' ("ELEC COU' (DI"I'C (b.sal)) 8!OM EMP B ;0E!E a.salb.sal) Enter ,al+e for n: 2 "AL <<<<<<<<< R1
To /!e !%s"alle# Oracle /ers!o% !%$or-a"!o% "4L5 select banner from ,7,ersion
D!s,la0 ")e %+-&er /al+e !% 1or#s "4L5 select sal* (tochar(todate(sal*)* sp)) from emp "AL (OC0A!(ODAE("AL*)*"P)) <<<<<<<<< <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< ei/ht h+ndred @S one tho+sand si# h+ndred @2X one tho+sand two h+ndred fift$ If $o+ want to add some te#t li3e* !s. hree ho+sand onl$. "4L5 select sal V"alar$ V* ( !s. (tochar(todate(sal*)* sp)) onl$.)) V"al in ;ordsV from emp "alar$ "al in ;ords <<<<<<< <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< !s. Ei/ht 0+ndred onl$. @S !s. One ho+sand "i# 0+ndred onl$. @2X !s. One ho+sand wo 0+ndred 8ift$ onl$. @Q. Displa$ Odd= E,en n+mber of records
F!%# O## %+-&er o$ recor#s. select ^ from emp where (rowid*@) in (select rowid* mod(rown+m*2) from emp)
F!%# E/e% %+-&er o$ recor#s. select ^ from emp where (rowid*) in (select rowid* mod(rown+m*2) from emp) ;hich date f+nction ret+rns n+mber ,al+eT monthsbetween
A%0 ")ree PL(SL E9ce,"!o%s. ooman$rows* 'oData8o+nd* 6al+eError* ZeroError* Others
1)a" are PL(SL C+rsor E9ce,"!o%s. C+rsorAlread$Open* In,alidC+rsor
O")er a0 "o re,lace +er0 res+l" %+ll /al+e !") a "e9" "4L5 "et 'ULL '=AH to reset "4L5 "et 'ULL H
1)a" are ")e -ore co--o% ,se+#ocol+-%s. ">"DAE* U"E! * UID* CU!6AL* 'EW6AL* !O;ID* !O;'UM
1)a" !s ")e o+",+" o$ SIN $+%c"!o%. @ for positi,e ,al+e* for Zero* <@ for 'e/ati,e ,al+e.
1)a" !s ")e -a9!-+- %+-&er o$ "r!''ers ca% a,,l0 "o a s!%'le "a&le. @2 tri//ers.
1)a" are S0%o%0-s. "$non$m is the alias or alternati,e name for table* ,iews* se-+ences proced+res and other
database obect. It is created for reasons of "ec+rit$ and Con,enience. wo le,els of sec+rit$ are P+blic < created b$ DBA accessible to all the +sers. Pri,ate < Accessible to creator onl$. Ad,anta/es are referencin/ witho+t specif$in/ the owner and 8le#ibilit$ to c+stomi%e a more meanin/f+l namin/ con,ention.
1)a" are Se+e%ces. "e-+ences are +sed for /eneratin/ se-+ence n+mbers witho+t an$ o,erhead of loc3in/. Drawbac3 is that after /eneratin/ a se-+ence n+mber if the transaction is rolled bac3* then that se-+ence n+mber is lost.
1)a" !s ")e #!$$ere%ce &e"ee% /!e a%# s0%o%0- !% oracle. A ,iew is a stored -+er$ $o+ can access as a (reado+ +s+all$ create p+blic s$non$ms to access obects in another schema witho+t referencin/ the schema.
Ho #o 0o+ !-,le-e%" o%e"oo%e o%e"o-a%0 a%# -a%0"o-a%0 rela"!o%s)!,s )!le #es!'%!%' "a&les. One
De$!%e ca%#!#a"e *e0 al"er%a"e *e0 a%# Co-,os!"e *e0. A candidate 3e$ is one that can identif$ each row of a table +ni-+el$. &enerall$ a candidate 3e$ becomes the primar$ 3e$ of the table. If the table has more than one candidate 3e$* one of them will become the primar$ 3e$* and the rest are called alternate 3e$s. A 3e$ formed b$ combinin/ at least two or more col+mns is called c omposite 3e$.
E9,la!% #!$$ere%" !sola"!o% le/els. An isolation le,el determines the de/ree of isolation of data between conc+rrent transactions. he defa+lt "4L "er,er isolation le,el is !ead Committed. 0ere are the other isolation le,els (in the ascendin/ order of isolation): !ead Uncommitted* !ead Committed* !epeatable !ead* and "eriali%able.
1)a" !s a% !%#e9. 1)a" are ")e "0,es o$ !%#e9es. Ho -a%0 cl+s"ere# !%#e9es ca% &e crea"e# o% a "a&le. I crea"e a se,ara"e !%#e9 o% eac) col+-% o$ a "a&le8 1)a" are ")e a#/a%"a'es a%# #!sa#/a%"a'es o$ ")!s a,,roac). Inde# in oracle helps to trace the information faster +st li3e and inde# in boo3. It is a pointer to the location of data p+rpose to ma3e "4L -+eries r+n faster. here is se,eral t$pe of inde# in oracle s+ch as: b^tree inde#es* b^tree cl+ster inde#es* hash cl+ster inde#es* re,erse 3e$ inde#es* bitmap inde#es* partitioned inde#es* f+nction
1)a" !s &loc*!%' a%# )o o+l# 0o+ "ro+&les)oo" !".
Bloc3in/ happens when one connection from an application holds a loc3 and a second connection re-+ires a conflictin/ loc3 t$pe. his forces the second connection to wait* bloc3ed on the first. 0ow to find whose loc3 is bloc3in/ in oracle lin3:http:shahiddba.blogspot.com3#)3#2find"whose"lock"is"blocking.html
Discussion4 Im"ort08"ort !tilit&? EJport (eJp%& Import (imp% are Oracle utilities which allow you to write data in an O!,1E"binary format from the database into operating system files and to read data back from those operating system files. ! simple automated script to eJport full database S"T ORACL"_SIDORCL3 Colm- -.t-c -eF_E?le E_-.t -o,r-t colm- -.tG?te -eF_E?le E_-.tG?te -o,r-t S"L"CT TO_CHAR..G?te;DD&&))HH4;2 -.tG?te #RO& G?l >o.t e%, ..temMor?cleorcl3 fll co-..te-t fleDUBAC'$(UGm,YYE_-.tG?te!!Gm,
lo@DUBAC'$(UGm,YYE_-.tG?te!!lo@
e%t
Which are the Im"ort08"ort modes? Full e8"orte8"ort4
The ES+8-0118D!T!'!E Z I9+8-0118D!T!'!E& respectively& are needed to perform a full eJport. 0se the full eJport parameter for a full eJport. Ta#les"ace4
0se the tablespaces eJport parameter for a tablespace eJport. !ser4
This mode can be used to eJport and import all obects that belong to a user. 0se the owner eJport parameter and the -O90E import parameter for a user (owner% eJport"import. Ta#le4
pecific tables (or partitions% can be EJportedImported with table eJport mode. 0se the tables eJport parameter for a table EJport Import mode. -or more details eJample follow the other post: 0sing ImportEJport Is it "ossi#le to e8" im" to multi"le files?
?es& is possible. Cere is an eJample: e%, SCOTTMTI"R #IL"CU?cK,U#le1!Gm,CU?cK,U#le!Gm, LOCU?cK,U.cott!lo@
,ow we can use e8" im" when we have < different -racle data#ase versions? •
eJp must be of the lower version
•
imp must match the target version
What I have to do #efore im"orting data#ase -#ects?
'efore importing database obects& we have to drop or truncate the obects& if not& the data will be added to the obects. If the seuences are not dropped& the seuences will generate inconsistent values. If there are any constraints on the target table& the constraints should be disabled during the import and enabled after import. Is it "ossi#le to im"ort a ta#le in a different ta#les"ace?
'y default& 5O. 'ecause there is no tablespace parameter for the import operation. Cowever this could be done in the following manner: •
(e% create the table in another tablespace (the table will be empty%
•
Import the table using I5DES-I1E parameter (the import is not done& but a file which contains the indeJes creation is generated%
•
9odify this script to create the indeJes in the tablespace we want
•
Import the table using IK5OEFy option (because the table eJists%
•
ecreate the indeJes
In which cases im"e8" is used? •
Eliminate database fragmentation
•
chema refresh (move the schema from one database to another%
•
Detect database corruption. Ensure that all the data can be read (if the data can be read that means there is no block corruption%
•
Transporting tablespaces between databases
•
'ackup database obects
,ow we can im"rove the 0:P Performance? •
et the '0--E parameter to a high value (e.g. 39%
•
If you run multiple eJport sessions& ensure they write to different physical disks.
,ow we can im"rove the I%P "erformance? •
Import the table using I5DES-I1E parameter (the import is not done& but a file which contains the indeJes creation is generated%& import the data and recreate the indeJes
•
tore the dump file to be imported on a separate physical disk from the oracle data files
•
If there are any constraints on the target table& the constraints should be disabled during the import and enabled after import
•
et the '0--E parameter to a high value (eJ. '0--EFG####### (G#9'% % and ,O99IT Fy or set ,O99ITFn (is the default behavior: import commits after each table is loaded& however& this use a lot of the rollback segments or undo space for huge tables.%
•
0se the direct path to import the data (DIE,TFy%
•
(if possible% Increase D'8,!,CE8IVE (D'8'1O,A8'0--E prior to i% considerably in the init;ID6.ora file
•
(if possible% et the 1OK8'0--E to a big value and restart oracle.
Which are the common I%P0:P "ro#lems? -A(555564 !ni=ue constraint ... violated " +erhaps you are importing duplicate rows. 0se IK5OEF5 to
skip tables that already eJist (imp will give an error if the obect is re"created% or the table could be dropped truncated and re"imported if we need to do a table refresh.. I%P(555674 3tatement failed ... o#ect alread& e8ists... " 0se the IK5OEF? import parameter to ignore
these errors& but be careful as you might end up with duplicate rows. -A(567774 3na"shot too old " !sk your users to TO+ working while you are eJporting or use parameter
,O5ITE5TF5O (Cowever this option could create possible referential problems& because the tables are not eJported from one snapshot in time%. -A(567L<4 Failed to e8tend roll#ac' segment " ,reate bigger rollback segments or set parameter
,O99ITF? (with an appropriate '0--E parameter% while importing. Im"ort08"ort elated Post4
EJportImport Table using Toad chema efresh using Toad chema efreshing without Toad 9igrating data Oracle i to )#g
Discussion on 3C* and Chec'"oint in -racle 1)a" !s SCN a%# )0 !" !s +se#. 1)a" )a,,e%s !$ ")e SCN !% #a"a$!le )ea#er !s %o" -a"c)!%' !") ")e Co%"rol $!le. he s$stem chan/e n+mber ("C') is Oracles cloc3* e,er$ time we commit the cloc3 increments. he "C' +st mar3s a consistent point in time in the database. he chec3point "C' in datafile headers are +pdated after chec3point. he "C' is incremented whene,er a transaction commits. "+ppose i do +pdate in one table which is stored in two different datafiles it will +pdate all datafiles header write information in control file after commit. Before openin/ the database "MO' will chec3 the control file datafile headers for the same "C'. If the "C' in datafile header is not matchin/ with the Control file that means datafile need reco,er$.
Ho "o $!%# C+rre%" SCN. "4L5 select dbmsflashbac3./ets$stemchan/en+mber from d+al 6E!. Oracle 9i "4L5 select tochar(c+rrentscn) from ,7database
6E!. Oracle @/
1)a" !s c)ec*,o!%". Is c)ec*,o!%" !s rela"e# "o SCN. 1)0 c)ec*,o!%" %+-&ers 'e" !%crease# %o% se+e%ce "o )!')er /al+e. L&;! or CKP writes the redo lo/ se-+ence to the datafile headers and control files and tells the DB;! to write dirt$ b+ffers from the dirt$ b+ffer write -+e+e (b+ffer cache) to dis3. It is a record indicatin/ the point in the redo lo/ where all DB chan/es prior to this point ha,e been sa,ed in the datafiles. he database AL;A>" has transactions /oin/ on* AL;A>". "MO' and man$ other bac3/ro+nd processes are alwa$s doin/ wor3* the database (+nless it is opened read onl$) is alwa$s doin/ transactions. 'ow* since the database ne,er sleeps. Most of those other [pro/rams\ do transactions and commit. "4L5select +sername* pro/ram from ,7session
T)e ?+s"!$!ca"!o% a'a!%s" ")e +es"!o% !s SCN %+-&er !s !" a %+-&er "o !#e%"!$0 a co--!""e# "ra%sac"!o%. or !s !" a %+-&er ?+s" "o !#e%"!$0 ")e se+e%ce o$ s"a"e-e%"s e9ec+"e# a'a!%s" ")e #a"a&ase . "4L5 create table s ( # int ) able created. "4L5 "elect dbmsflashbac3./ets$stemchan/en+mber scn from d+al SCN 9187 SL e@for - 1 !! 1000
-.ert -to . E?le. 2 e-G loo, e-G M
(LMSL ,roceGre .cce..fll com,leteG!
"4L5 select dbmsflashbac3./ets$stemchan/en+mber < "C' from d+al DBM"8LA"0BACK.&E">"EMC0A'&E'UMBE!<19@12SX <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< X It onl$ ad,anced b$ X < b+t we did o,er @* DML statements th+s the "C' is not assi/ned to a "4L statement. he "C' is incremented +pon commit. "4L5 "4L5 select dbmsflashbac3./ets$stemchan/en+mber scn from d+al "C' <<<<<<<<<< 19@121@ SL e@for - 1 !! 1000 loo, -.ert -to . E?le. 2 CO&&IT e-G loo, e-G (LMSL ,roceGre .cce..fll com,leteG! SL .elect Gm._fl?.>?cK!@et_..tem_c>?-@e_-mer YSCN from G?l DB&S_#LASHBAC'!"T_S)ST"&_CHAN"_N$&B"R9181 101
"ee* now if $o+ COMMI @* times* the "C' does +mp b$ @* (the other +mps are the bac3/ro+nd processes* the$ are alwa$s doin/ st+ff < "MO'* MMO'* PMO'* etc. the$ do "4L all of the time < the database ne,er rests
Is ")ere a l!-!"a"!o% o% ")e %+-&er o$ SCN ")a" ca% &e 'e%era"e# !% a seco%#. It is depends +pon n+mber of commit $o+ are doin/
Ho ca% e c)ec* ,rec!s!o% o$ SCN T!-!%'. "4L5 select timemp*timedp* scnwrp* scnbas from smonscntime It is internall$ done if $o+ loo3 at that table all of the col+mns < there is a field IM"C'MAP* it is hidden in there* b$ +sin/ the APIs $o+ can acces s that information. SL.elect .c-_to_tme.t?m,.c-2 t. m-.c-2 m?%.c-2 from .elect Gm._fl?.>?cK!@et_..tem_c>?-@e_-mer2leEel .cfrom G?l co--ect leEel J 100 2 ro, .c-_to_tme.t?m,SCN2
OrGer .c-_to_tme.t?m,SCN2
1)a" !$ ")e "ra%sac"!o% !s rolle#&ac*. Does ")e SCN a'a!% !%crease. >es it is* chec3 o+t this E#ample "4L5 C!EAE ABLE "@ (E'O 'UMBE!(Q)* E'AME 6A!C0A!2(2)) able created. SL Select Gm._fl?.>?cK!@et_..tem_c>?-@e_-mer .c- from G?l SCN 880087 SL e@for - 1 !! 1000 loo, -.ert -to S1 E?le. 1 ;SHAAN; 2 roll?cK e-G loo, e-G M (LMSL ,roceGre .cce..fll com,leteG! SL .elect .c- .c-880087 from .elect Gm._fl?.>?cK!@et_..tem_c>?-@e_-mer .c- from G?l 2 SCN SCN880087 880087
014
SL Select Gm._fl?.>?cK!@et_..tem_c>?-@e_-mer .c- from G?l SCN 880087 SL e@for - 1 !! 10000 loo, -.ert -to S1 E?le. 1 ;SHAAN; 2 roll?cK e-G loo, e-G M (LMSL ,roceGre .cce..fll com,leteG! SL .elect .c- .c-880087 from .elect Gm._fl?.>?cK!@et_..tem_c>?-@e_-mer .c- from G?l 2
SCN SCN880087 17731184
0180
E/e% -ore ")a% !$ 0o+ #o %o" roll&ac* &+" co--!" !%s"ea# "4L5 Create table "2 ( eno n+mber(Q)) able created. SL .elect Gm._fl?.>?cK!@et_..tem_c>?-@e_-mer .c- from G?l SCN 88843 SL e@for - 1 !! 1000 loo, -.ert -to . E?le. 2 commt e-G loo, e-G M (LMSL ,roceGre .cce..fll com,leteG! SL .elect .c- .c-88843 from .elect Gm._fl?.>?cK!@et_..tem_c>?-@e_-mer .c- from G?l 2 SCN SCN88843 8830391
1979
SL .elect Gm._fl?.>?cK!@et_..tem_c>?-@e_-mer .c- from G?l SCN 883044 SL e@for - 1 !! 1000 loo, -.ert -to . E?le. 2 commt e-G loo, e-G M (LMSL ,roceGre .cce..fll com,leteG! SL .elect .c- .c-883044 from .elect Gm._fl?.>?cK!@et_..tem_c>?-@e_-mer .c- from G?l
2 SCN SCN883044 88487
138
Is ")ere a%0 #!$$ere%ce &e"ee% selec" CURRENT4SCN $ro- /#a"a&ase a%# selec" #&-s4$las)&ac*8'e"4s0s"e-4c)a%'e4%+-&er sc% $ro- #+al. 8or a VnormalV database (not standb$) the$ are for all intents and p+rposes the same. he$ co+ld be a LILE different if $o+ do somethin/ li3e: "4L5select c+rrentscn* dbmsflashbac3./ets$stemchan/en+mber from ,7database since the$ wo+ld be e,al+ated at two sli/htl$ different points in time* b+t consider them Vthe sameV
1)a" !s ")e #!$$ere%ce or s!-!lar!"0 &e"ee% SCN a%# ORA4RO1SCN. 1)ere #oes oracle s"ore SCN. he "C' is li3e a cloc3 < it is alwa$s ad,ancin/ (+se the command [dbmsflashbac3./ets$stemchan/en+mber\ and wait for few seconds* print it a/ain* it will ha,e ad,anced). "o* +st thin3 of the "C' li3e a tic3er* li3e time < e,er$ time a transaction ends < another +nit of time is added* li3e addin/ seconds to time where as orarowscn is an obser,ed point in time. he orarowscn is a ,al+e associated to a bloc3 or a row on a bloc3 that represents the [time\ the bloc3=row was last modified.
1)e% al"er s0s"e- c)ec*,o!%" co--a%# !s +se#. ;hen we ha,e few dirt$ b+ffers of one table in the b+ffer cache and we iss+e the command he chec3point "C' of the data bloc3 is +pdated and IL is also +pdated as: Itl
:G
$?
#l?@
LcK
Sc-M#.c
0%01
0%0007!00!00004
0%00c0037!0G0f!17
$
3
f.c 0%0000!00GGffee
0%0
0%0001!01!0000088
0%00c001G!00!0
$
1
f.c 0%0000!00Gf140
B+t the header bloc3 (after we d+mp and see) of the file still contains the same "C' as before irrespecti,e of the chan/e in the data bloc3
1)ere ")e SCN %+-&er res!#es. Does arc)!/e a%# re#o lo's also co%"a!% SCN %+-&ers. "C' doe not reall$ reside an$where* it is li3e time itself. A ,al+e of the "C'* ta3en at ,ario+s times* representin/ the time somethin/ happened is stored in man$ places* sort of li3e a timestamp wo+ld be. Datafiles ha,e "C's ass ociated with them (times of ,ario+s operations) control files ha,e them (times of ,ario+s operations) lo/ files ha,e them (to record times of ,ario+s operations) +ndo se/ments ha,e them (......) the$ are littered all o,er the place* the$ are li3e timestamps.
O/er/!e DATA DICTIONARG: CHECKPOINT 67I'"A'CE!ECO6E!>* 67LO&* 67LO&0I"O!> 67I'"A'CE!ECO6E!>: lowest ,al+e in last fo+r col+mns controls chec3points redo lo/ file si%e* lo/chec3pointtimeo+t* lo/chec3pointinter,al* faststartiotar/et
!%!" ,ara-e"er: lo/chec3pointinter,al* lo/chec3pointtimeo+t* lo/chec3pointstoalert lo'4c)ec*,o!%"4!%"er/al
C redo lo/ bloc3s (O" bloc3s not DB bloc3s) written before a chec3point C If set /reater than redo lo/ file si%e* chec3points occ+r at lo/ switches C I/nored if set to %ero. lo'4c)ec*,o!%"4"!-eo+"
C n+mber of seconds since last chec3point before another is performed C i/nored if set to %ero C defa+lt @ seconds (R min+tes) C lo/chec3pointstoalert if tr+e* write chec3points to alert lo/ To #ecrease c)ec*,o!%"s:
C set lo/chec3pointinter,al lar/er than the si%e of the online redo lo/s C eliminate time
Im"ortant Points to #e considered #efore we#cam interview !ecr+itment radi+s of man$ companies is not +st limited to their own cit$ or co+ntr$. he$ now search for ,al+able h+man reso+rce across the /lobe. O,erseas companies cond+ct inter,iews o,er the telephones* and with the ad,ent of impro,ed technolo/$* are now ma3in/ more +se of the webcams for their inter,iews. ob see3ers* howe,er* sho+ld not thin3 that a webcam inter,iew is +st li3e a webcam chat. here are se,eral important considerations to be made before $o+r webcam inter,iew.
E,en tho+/h it is ,er$ similar to an in
he webcam that $o+ choose sho+ld be of ,er$ /ood -+alit$. B+$ the one from a rep+ted brand and beware of cheap imitations. Also ma3e s+re that the specifications of the webcam are s+pported b$ $o+r comp+ter. ;hile most webcams come with a b+ilt
Positionin/ $o+rself before the webcam is also ,er$ important. ;hile in a cas+al webcam chat* $o+ ma3e s+re the position of the camera enhances the feat+res of $o+r face* in an inter,iew it is best if $o+ sit strai/ht in front of it* ma3in/ the inter,iewer feel $o+ are loo3in/ into their e$es.
he bac3/ro+nd sho+ld be clear of an$thin/ inappropriate. hese mi/ht incl+de c lothes han/in/ o+t of a wardrobe* +nwashed dishes on the dinin/ table. Preferabl$ ha,e $o+r bac3 to a wall* +st li3e $o+ wo+ld ha,e in an$ formal meetin/. "imilarl$* $o+ need to dress +p +st li3e $o+ wo+ld for an$ ob inter,iew. E,en if $o+ are wearin/ somethin/ cas+al* ma3e s+re it clean and tid$.
Appropriate li/htin/ is ,ital d+rin/ a webcam inter,iew. ;hen in front of the camera* pale li/hts are better than plain white li/ht. >o+ can ha,e a lamp placed close to $o+ b+t not behind $o+ as that wo+ld prod+ce a dar3er ima/e.
Lastl$* 3eep some of the important thin/s close b$* s+ch as $o+r res+me* an$ certificates* ban3 papers and pen* /lass of water and some tiss+e papers. Preparation for a webcam inter,iew is the same as that of an in
,ow to Chec' Wh& 3hutdown Immediate hangs or ta'ing longer time? ef. Doc ID )$2*#2.) In order to check reason why shutdown immediate hangs SLco--ect M ?. S)SDBA SLSelect * from %6Kt%e F>ere Kt%ecfl ;D"AD;
This shows dead transactions that 9O5 is looking to rollback. 5ow +lan to shutdown again and gather some information. 'efore issuing the shutdown immediate command set some events as follows: SL?lter .e..o- .et eEe-t. ;1004 tr?ce -?me co-te%t foreEer leEel 1; SL?lter .e..o- .et eEe-t. ;10400 tr?ce -?me co-te%t foreEer leEel 1; SL.>tGoF- mmeG?te 1004 tr-. o- e%te-GeG SL_TRAC" for t>e .>tGoF- ,roce..! 10400 Gm,. ? ..tem .t?te eEer 7 m-te.!
The trace files should show where the time is going. To check the progress of 9O5 is very important in this case. ?ou can find it with the below uery. S"L"CT r!NA&" =RB Se@me-t N?me= G?_.e@!.e_m D"COD"TR$NCS)SDAT" LOON_TI&"2 0 N$LL TR$NCS)SDAT" LOON_TI&"2 << ; D?.; << ; + ;2 << TO_CHARTO_DAT"TR$NC&ODS)SDAT"LOON_TI&"12 * 84002 ;SSSSS;2 ;HH4&ISS;2 LOON E6.e..o-!SID E6.e..o-!S"RIALV ,!S(ID E6.e..o-!,roce.. E6.e..o-!$S"RNA&" E6.e..o-!STAT$S E6.e..o-!OS$S"R E6.e..o-!&ACHIN" E6.e..o-!(RORA& E6.e..o-!moGle ?cto#RO& E6locK l E6,roce.. , E6roll-?me r E6.e..o- S"L"CT .e@me-t_-?me RO$NDte.M104*10422 .e_m #RO& G?_.e@me-t. WH"R" .e@me-t_t,e ;T)(" $NDO; ORD"R B) te. D"SC2 G?_.e@ WH"R" l!SID ,!,G+2 AND E6.e..o-!SID l!SID AND TR$NC l!G1+2M7732r!. AND l!T)("+2 ;T:; AND l!lmoGe+2 AND r!NA&" G?_.e@!.e@me-t_-?me AND E6.e..o-!.er-?me ;S)ST"&; AND .t?t. ;INACTI5"; ORD"R B) .e_m D"SC
eason4 hut down immediate may hang because of various reasons. § +rocesses still continue to be connected to the database and do not terminate.
§
9O5 is cleaning temp segments or performing delayed block cleanouts.
§
0ncommitted transactions are being rolled back.
De#ugging a hung data#ase in oracle version 66g
'ack in oracle )#g a hung database was real problem& especially could not connect via <1Lplus release the source of the hanging. There is a new feature in Oracle ))g <1L+lus called the XprelimY option. This option is very useful for running oradebug and other utilities that do not reuire a real connection to the database. CU./l,l. Z,relm "or" in <1 you can set SLSet _,relm oSLco--ect M ?. ..G? 5ow you are able to run oradebug commands to diagnose a hung database issue: SL or?Ge@ >?-@?-?le 3 @ait at least 3 minutes to give time to identify process state changes.
SLor?Ge@ >?-@?-?le 3
Open a separate <1 session and immediately generate a system state dump. SL?lter .e..o- .et eEe-t. ;mmeG?te tr?ce -?me S)ST"&STAT" leEel 10; ,ow to Chec' wh& shutdown immediate ta'ing l onger time to shutdown?
ef. )#N$)$).$: hutdown immediate or shutdown hangs. 9O5 disabling TS recovery ef. 5ote GN*G*.): @hat to do and not to do when shutdown immediate hangs. ef. 5ote 234$44.): hutdown immediate very slow to close database. @hen shutdown immediate taking longer time as compare to the normal time usually it is taking. ?ou must perform following task before performing actual shutdown immediate. ). !ll active session. 3. Temporary Tablespace ecover. G. 1ong unning em?-?me lo@o-_tme from E6.e..o- F>ere .t?t.;ACTI5"; ?-G .er-?me . -ot -ll If !ctive session is eJist then& try to find out what is doing in the database by this session. !ctive session makeshutdown slower SL Select f!R =RecoEereG= !-r =NeeG RecoEereG= from .elect co-tlocKV2 R 1 c> from ..!fet6 2 f.electco-tlocKV2 NR 1 c> from ..!et62 F>ere f!c>!c>
,heck to see any long uery is running into the database while you are trying to shutdown the database. SL Select * from E6.e..o-_lo-@o,. F>ere tme_rem?--@0 orGer .er-?me ,heck to ensure large transaction is not going on while you are trying to shutdown the database. SLSelect .m.eG_lK2 from E6tr?-.?cto- ,heck the progress of the transaction that oracle is recovering. SLSelect * from E6f?.t_.t?rt_tr?-.?cto-. ,heck to ensure that any parallel transaction recovery is going on before performing shutdown immediate. SLSelect * from E6f?.t_.t?rt_.erEer. -inally if you do not understand the reason why the shutdown is hanging or taking longer time to shutdown then try to shutdown your database with WabortH option and startup with WrestrictH option and try shutdown with WimmediateH option. ,heck the alert.log& if you find any error related W T>re?G 1 c?--ot ?lloc?te -eF lo@ .e/e-ceH then you need to enable your archival process. ?our archival is disable due to any reason. Process4 ). In command prompt set the oracle8sid first CUS"T ORACL"_SID Qor G_-?meP 3. 5ow start the <1Lplus: CU./l,l. M-olo@ SLco--ect ..M***-.t?-ce_-?me SLSelect -.t?-ce_-?me from E6-.t?-ce
G. Try to checkpoint before shutdown abort SL?lter ..tem c>ecK,o-t SL .>tGoF- ?ort
2. tart the database with WrestrictH option so that no other user is able to connect you in the mean time. SL.t?rt, re.trct SL.elect lo@-. from E6-.t?-ce
