Oracle Database 12c Available for Download Finally, Oracle has released the most awaited oracle Database i.e, 12c (Oracle 12.1.0.1) 12.1.0.1) . It is available for download download fr from om the Oracle Software Software lo!d (formerly (formerly "now as eDelivery) eDelivery) and O#$ (Oracle (Oracle #ech $etwor $et wor") ") for %&bit %&bit li lin!' n!' and solaris solaris . Ora Oracl cle e 12 12c c is not availab available le for 2 bi bitt . Ora Oracle cle has yet not release rel eased d the I* and +indow database database 12c sw, ho-ef!lly ho-ef!lly will release release soon . er ere e are lin"s for download Oracle sw. eDelivery : lic" here to download from eDelivery . OTN : lic" here to download from from O#$ . Documentation : Click here to download doc!ment of 12c #here are some very e'itin/ feat!res in Oracle 12c Database .One of them is l!//able Database which whic h allows allows a sin/ sin/le le Oracle Oracle data database base insta instance nce to hold many other data database bases, s, allowin/ allowin/ for mor more e ecient eci ent !se of syst system em reso!rce reso!rces s and easier mana/ement mana/ement.. I will be soon downloa download d and -ost abo!t this feat!res . n ocial Oracle -rice list, which was !-dated #!esday, showed a m!ltitenant database o-tion -riced at 314,500 -er -rocessor. -rocessor license for the main 6nter-rise 6dition remained -riced at 3&4,500 -er -rocessor.
ORA-00020 and Impact on database on increasing processes values The maximum number of processes is specified by the initialization parameter " processes processes"" . When this maximum number of process is reached, no more requests will be processed. If we are try to connect with database then we et the below errors . !ere for testin purpose, i hae hae set my processes alue alue to #$ for this demo . %oracle&serer' ()* sqlplus "sys+xxx as sysdba" -/0lus1 2elease ''.3.$.'.$ 0roduction on 4ri 5ay 36 '#1#718# 3$'# Copyriht 9c: ';<3, 3$$;, =racle. >ll rihts resered. ERROR: ORA-00020: maximum number of processes (30) exceeded I usually check my alert lofile for any oracle errors and find followin info . Alert logfile 4ri 5ay 36 '#1#<1#$ 3$'# =2>?$$$3$1 @o more process state obAects aailable =2>?3$ errors will not be written to the alert lo for the next minute. 0lease look at trace files to see all the =2>?3$ errors. According to oracle oracle docs : Error: =2> 3$ Text: maximum number of pro cesses Bnum exceeded ??????????????????????????????????????????????????????????????????????????????? ause : >n operation requested a resource that was unaailable. unaailable. The maximum number of processes is specified by the initialization parameter 02=CDD. When this maximum is reached, no more requests are processed. Action : Try the operation aain in a few minutes. If this messae occurs often, shut down =racle, increase the 02=CDD parameter in the initialization parameter file, and restart =racle. 4inally , i hae decided to increase the no. of processes but didnEt find any exact formula or any optimall alue to set this parameter optima parameter . o i hae set it to 3$$ for now . >noth >nother er issue here here with us to connect with oracle, since we ettin error while connectin with oracle . !ere is one trick to create a session sess ion by usin "!relim !relim"" option . Interestin thins about this option is that we can only use the "shut abort" command nothin else else 9>4>IF:. 9>4>IF:. !ere are the steps steps to set the processes processes alue 1
%oracle&serer' ()* sqlplus ?prelim "sys+xxxx as sysdba" -/0lus1 2elease ''.3.$.'.$ 0roduction on 4ri 5ay 36 '#1#<188 3$'# Copyriht 9c: ';<3, 3$$;, =racle. >ll rihts resered. - shut immediate =2>?$'$'31 not loed on - shut abort =2>CD instance shut down. - exit =nce the instance is is down we can easily increase the process alue at mount stae . %oracle&serer' ()* sqlplus "sys+sys as sysdba" -/0lus1 2elease ''.3.$.'.$ 0roduction on 4ri 5ay 36 '#18'16$ 3$'# Copyriht 9c: ';<3, 3$$;, =racle. >ll rihts resered. Connected to an idle instance. - startup mount =2>CD instance started. Total ystem Global >rea 6'<6<6336 bytes Total 4ixed ize '##7;#3 bytes Hariable ize #'$#<$;87 bytes atabase Juffers '$$77#3;7 bytes 2edo Juffers 7'$#$6$ bytes atabase mounted. - alter system set processesK3$$ scopeKspfileL ystem altered. - shut immediate =2>?$''$;1 database not open atabase dismounted. =2>CD instance shut down. - startup =2>CD instance started. Total ystem Global >rea 6'<6<6336 bytes Total 4ixed ize '##7;#3 bytes Hariable ize #'$#<$;87 bytes atabase Juffers '$$77#3;7 bytes 2edo Juffers 7'$#$6$ bytes atabase mounted. atabase opened. - select name,openMmode from *databaseL @>5D =0D@M5=D ????????? ???????????????????? =2C 2D> W2ITD
"mpact on db #$ile increasing t$e processes :: While oolin oolin , i found a ery usefu usefull comment by "Nonathan ewis" ewis" .>ccordin .>ccordin to him , Incre Increasin asin processes from say '$$$ to 8$$$ increases the amount of shared memory that needs to be resered
at the =+ leel and disrupt the memory in the G>. The = must be confiured to support the larer amount of shared memory. The impact at the =+ is that eery process that starts up will want to build a memory map for the G> ? dependin on the way we hae confiured memory paes and the way that stratey our =+ adopts to build maps it could demand a hue amount of =+ memory in a short time. The technoloy we need to aoid this issue comes in two different flaours1 lare memory paes, and shared memory maps. The impact on the G> is two?fold ? each process and session has to create an entry in *process and *session, and allocate arious memory structures in the G>1 acquirin the rows in *session and *process are serial actions, and the memory allocation in the G> can cause massie flushin of the library cache . o , it is adisable to increase the number of processes while keepin itEs impact in mind .
Manual Upgradation rom Oracle !i to 10g %pgradation is the process of replacin our existin software with a newer ersion of the same product . 4or example, replacin oracle ;i release to oracle '$ release . Opradin our applications usually does not require special tools. =ur existin reports should look and behae the same in both products. !oweer, sometimes minor chanes may be seen in product .Opradation is done at oftware leel .
•
I receied a mail from a reader reardin the upradation of database . !e wants to uprade his database from ;i to '$ . !ere, i will like adice that itEs better to uprade our database from ;i to '' as compare to ;i to '$ because =racle extended support for '$23 will ends on #'? Nul?3$'# and also there are more features aailable in =racle '' . We can directly uprade to oracle '', if our curent database is ;.3.$.6 or newer then its supports direct uprades to ersions &'2'0' *0'*and *0'2 ' We can uprade the ersion as +'3'3 -, +'3' -, &'2'0' -, **'*
•
'0'. -, '0'/ -, &'2'0' -, **'*
•
'*'+ -, '*'+' -, &'2'0' -, **'*
•
&'0'*'3-, &'0'*' -, &'2'0' -, **'*
•
&'2'0'3 (or lo#er) -, &'2'0' -, **'* =racle '' client can access =racle databases of ersions
anual %pgradation : > manual uprade consists of runnin - scripts and utilities from a command line to uprade a database to the new =racle atabase '$ release. While a manual uprade ies us finer control oer the uprade process, it is more susceptible to error if any of the uprade or pre?uprade steps are either not followed or are performed out of order. Jelow are the steps *') "nstall Oracle *0g soft#are : 4or Opradation , Inoke the .exe or rumInstaller ad select "Install software only" to Install the =racle +w . 2') Ta5e 6ull ac5up 1atabase : Take full database backup of database which is to be upraded . 3') $ec5 t$e in7alid Ob8ects : Check the inalid obAects by runnin ultrp.sql scripts as
- &=2>CDM!=5D+rdbms+admin+utlrp.sql ') 9ogin into &i $ome and run t$e utlu*02i'sl : This script is in oracle '$ home . - spool preMuprd.sql - &B=2>CDM'$GM!=5D+rdbms+admin+utlu'$3i.sql - spool off
•
The aboe scripts checks a number of areas to make sure the instance is suitable for uprade includin atabase ersion
•
o file sizes
•
Tablespace sizes
•
erer options
•
Initialization parameters 9updated, depercated and obsolete:
•
atabase components
•
5iscellaneous Warnins
•
P>OQ tablespace present
•
Cluster information The issues indicated by this script should be resoled before a manual uprade is attempted. =nce we hae resoled the aboe warnin , then re?run the aboe script once more to cross? check . .') $ec5 for t$e timestamp #it$ time;one 1atat4pe : The time zone files that are supplied with =racle atabase '$ hae been updated from ersion ' to ersion 3 to reflect chanes in transition rules for some time zone reions. The chanes may affect existin data of TI5DT>50 WIT! TI5D R=@D datatype. To presere this TI5DT>50 data for updatin accordin to the new time zone transition rules, we must run the utltzu3.sql script on the database before upradin. This script analyzes our database for TI5DT>50 WIT! TI5D R=@D columns that a re affected by the updated time zone transition rules. - &=2>CDM'$GM!=5D+rdbms+admin+utltzu3.sql - select / from sys.sysMtzu3MtemptabL If the utltzu3.sql script identifies columns with time zone data affected by a database uprade, then back up the data in character format before we uprade the database. >fter the uprade, we must update the tables to ensure that the data is stored based on the new rules. If we export the tables before upradin and import them after the uprade, the conersion will happen automatically durin the import. /') <$utdo#n t$e database : shut down the database and copy the spfile9or pfile: and password file from ;i home to '$ home . +') %pgrade 1atabase : et followin enironment for '$ and loin usin "P" user . It takes rouhly half an hour to complete. pool the output to a file so that you can reiew it afterward. =2>CDMIKBsid =2>CDM!=5DKB'$ home 0>T!KB'$ path sqlplus + as sysdba - startup uprade -spool uprdMlo.sql -&catuprd.sql - spool off ') Recompile an4 in7alid ob8ects : Compare the number of inalid obAects with the number noted in step 6 . It should hopefully be the same or less.
-&=2>CDM!=5D+rdbms+admin+utlrp.sql &') $ec5 t$e status of t$e upgrade : - &=2>CDM!=5D+rdbms+admin+utlu'$3s.sql The aboe script queries the J>MD2HD2M2DGIT2P to determine uprade status and proides information about inalid or incorrect component uprades. It also proides names of scripts to rerun to fix the errors. *0') Edit t$e spfile : Create a pfile from spfile as -create pfile from spfile L =pen the pfile and set the compatible parameter to '$.3.$.$.$ . hutdown the database and create the new modified spfile . -shut immediate - create spfile from pfile L **')
"ontrol ile #arallel Read $ %rite wait &vent control fle contains information abo!t the associated database that is re7!ired for access by an instance, both at start!- and d!rin/ normal o-eration . It is the Oracle control 8le(s) that records information abo!t the consistency of a database9s -hysical str!ct!res and o-erational stat!ses . #he database state chan/es thro!/h activities s!ch as addin/ data 8les, alterin/ the si:e or location of data8les, redo bein/ /enerated, archive lo/s bein/ created, bac"!-s bein/ ta"en, S$ n!mbers chan/in/, or chec"-oints bein/ ta"en. Why Control File Waits Occur ? ontrol File +aits Occ!r d!e to the followin/ reason . 1.) #his wait occ!rs when a server -rocess is !-datin/ all co-ies of the control8le i.e, the session is writin/ -hysical bloc"s to all control 8les at same time . 2.) #he session commits a transaction to a control8le 3.) han/in/ a /eneric entry in the control8le, the new val!e is bein/ written to all control8les .) ontrol8le resides on s!ch a dis" which is heavily !sed i.e, facin/ lots of io9s . +e can chec" the wait e'-erience by a session !sin/ v3session;wait views as S<=>select event, wait;time, -1, -2, - fr-m v3session;wait wher event li"e 9?control?9@ ere waitMtime is the ela-sed time for reads or writes.
!ossi"le ste#s to re$uce this %ait : 1.) Aed!ce the n!mber of control8le co-ies to the minim!m that ens!res that not all co-ies can be lost at the same time. 2.) Bove the control8le co-ies to less sat!rated stora/e locations. 3.) Aed!ce the fre7!ent lo/ switches . #o 8nd the o-timal time and si:e for lo/ switch chec& this #ost . s far my e'-erience , ontrol 8le access is /overned by activities s!ch as redo lo/8le switchin/ and chec"-ointin/ . #herefore it can only be inC!enced indirectly by t!nin/ . It rarely occ!rs in my re-ort and it /et solved automatically when i chec" the other wait metric es-ecially lo/ sync wait and others waits .
Interpreting Raw Sql Trace File
'(*T+,C- is the main method for collectin/ S<= 6'ec!tion information in Oracle. It records a wide ran/e of information and statistics that can be !sed to t!ne S<= o-erations. #he s7l trace 8le contains a /reat deal of information . 6ach c!rsor that is o-ened after tracin/ has been enabled will be recorded in the trace 8le. #he raw trace 8le mostly contains the c!rsor n!mber . 6/, ASI$ I$ EASOA . 6*6!tes, F6#es and +I#s are recorded a/ainst a c!rsor. #he information a--lies to the most recently -arsed statement within that c!rsor . Firstly, let9s have a loo" on Wait -vents . W>IT S71 namKEdb file sequential readE elaK <68< fileSK''$ blockSK7#7<3 blocksK' obASK33' timK8$7$3<;7#867
G n event that we waited for. W,T nam G +hat was bein/ waited for . #he wait events here are the same as are seen in view H3S6SSIO$;+I# . ela G 6la-sed time for the o-eration.(microseconds) #1 G 1 for the /iven wait event. #2 G 2 for the /iven wait event. #3 G for the /iven wait event. -/am#le No. 1 : +I# % namG9db 8le se7!ential read9 elaG J&5J 8leG110 bloc"G%%J2 bloc"sG1 obKG221 timG50%02JL%5&% #he above line can be translated as om-leted waitin/ !nder EASOA no % for db 8le se7!ential read . +e waited J&5J microseconds i.e. a--ro'. J.5 milliseconds . For a read of File 110, start bloc" %%J2, for 1 Oracle bloc" of ObKect n!mber 221. #imestam- was 50%02JL%5&% . -/am#le no.2 : +I# 1 namG9library cache m!te' *9 elaG J1& idnG%0%12104 val!eG02J2LJ50%2& whereG& obKGM1 timGLL5%&24%0& #he above line can be translated as om-leted +I#in/ !nder EASOA no 1 for library cache m!te' * .+e waited J1& microseconds i.e. a--ro'. 0.J milliseconds .#o /et an e*cl!sive library cache latch with Identi8er %0%12104 val!e 02J2LJ50%2& location & . It was not associated with any -artic!lar obKect (obKGM1) #imestam- LL5%&24%0&. #he trace 8le also show the -rocessin/ of the s7l statements . Oracle -rocesses S<= statements as follow 'ta0e 1: reate a !rsor 'ta0e 2: arse the Statement 'ta0e 3: Describe Aes!lts 'ta0e : De8nin/ O!t-!t 'ta0e : Nind ny Hariables 'ta0e : 6'ec!te the Statement 'ta0e : aralleli:e the Statement 'ta0e 4: Fetch Aows of a 2I@G I@ CO2=2 Sn . DQDCutes, 4DTC!es and W>ITs are recorded aainst a cursor. The information applies to the most recently parsed statement within that cursor. !AR<"=> "= %R
c!rsor is a handle, or -ointer, to the conte't area. #hro!/h the c!rsor, a =S<= -ro/ram can control the conte't area and what ha--ens to it as the statement is -rocessed. #wo im-ortant feat!res abo!t the c!rsor are 1.) !rsors allow yo! to fetch and -rocess rows ret!rned by a S6M=6# statement, one row at a time.
2.) c!rsor is named so that it can be referenced. !arsin0 : Oracle arsin/ is the 8rst ste- in -rocessin/ of any database statement . AS6 record is accom-anied by the c!rsor n!mber. =et9s have a loo" on arsin/ in !rsor of a -artic!lar trace 8le .
!AR<"=> "= %RIT S31 namKE-/@et messae from clientE elaK #7# drier idK'6'#7;8#7 SbytesK' p#K$ obASK''7 timK;#$##33 4DTC! S31cK$,eK#',pK$,crK$,cuK$,misK$,rK$,depK$,oK',plhK<#7677#6,timK;#$##63' T S3 idK3 cntK3 pidK' posK' obAK$ opKE5D2GD N=I@ C>2TDI>@ 9crK$ prK$ pwK$ timeK'87 us costK$ sizeK;7 cardK':E T>T S3 idK# cntK' pidK3 posK' obAK$ opKE@DTD ==0 9crK$ prK$ pwK$ timeK$ us costK$ sizeK#; cardK':E T>T S3 idK6 cntK' pidK# posK' obAK$ opKE4IQD T>JD 4IQD I@DQ Q*FWT 9ind1': 9crK$ prK$ pwK$ timeK$ us costK$ sizeK37 cardK':E T>T S3 idK8 cntK' pidK# posK3 obAK$ opKE4IQD T>JD 4IQD I@DQ Q*FD 9ind13: 9crK$ prK$ pwK$ timeK$ us costK$ sizeK'# cardK':E T>T S3 idK7 cntK3 pidK3 posK3 obAK$ opKEJO44D2 =2T 9crK$ prK$ pwK$ timeK< us costK$ sizeK8 cardK':E T>T S3 idK cntK3 pidK7 posK' obAK$ opKE4IQD T>JD 4O Q*FO02 9crK$ prK$ pwK$ timeK'#$ us costK$ sizeK8 cardK':E T>T S3 idK< cntK' pidK ' posK3 obAK$ opKE4IQD T>JD 4IQD I@DQ Q*FOD 9ind1': 9crK$ prK$ pwK$ timeK$ us costK$ sizeK87 cardK':E W>IT S31 namKE-/@et messae to clientE elaK drier idK'6'#7;8#7 SbytesK' p#K$ obASK''7 timK;#$##<36< /// 3$'3?$8?'; '81$133.<6# W>IT S31 namKE-/@et messae from clientE elaK #<3;'$<3 drier idK'6'# 7;8#7 SbytesK' p#K$ obASK''7 timK;7<73;6' 9O
len G the n!mber of characters in the S<= statement $e# G tells the a--licationtri//er de-th at which the S<= statement was e'ec!ted. de-G0 indicates that it was e'ec!ted by the client a--lication. de-G1 indicates that the S<= statement was e'ec!ted by a tri//er, the Oracle o-timi:er, or a s-ace mana/ement call. de-G2 indicates that th e S<= statement was called from a tri//er, de-G indicates that the S<= statement was called from a tri//er that was called from a tri//er. G Schema id !nder which S<= was -arsed. ui$ oct G Oracle command ty-e. li$ G rivile/e !ser id tim G #imestam-. hv G ash id. a$ G S<=#6*# address
!,+'- 63: cG15%25, eG1444J2, -G2, crG, c!G0, misG1, rG0, de-G0, o/G1, -lhG2420020J%, timG24%5%51&&40
c G E time (microseconds ro!nded to centiseconds /ran!larity on Li above) e 7 6la-sed time (centiseconds -rior to Li, microseconds thereafter) # 7 $!mber of -hysical reads. cr 7 $!mber of b!Pers retrieved for A reads.(onsistent reads) cu 7$!mber of b!Pers retrieved in c!rrent mode. mis 7 !rsor missed in the cache. r 7 $!mber of rows -rocessed. $e# G Aec!rsive call de-th (0 G !ser S<=, >0 G rec!rsive). o0 G O-timi:er /oal 1Gll;Aows, 2GFirst;Aows, GA!le, &Ghoose From the above arse line it is very clear that the total time ta"en in -arsin/ the statement is 0.144 sec and the no. of -hysical reads done are 2 .
8in$ 9aria"les : If the S<= statement does reference bind variables, then the followin/ S<= statement shown in the c!rsor can locate a section of te't associated with each bind variable. For each bind variable there are a n!mber of attrib!tes listed. #he followin/ are the ones we are interested in here m/l G the ma'im!m len/th M ie. the ma'im!m n!mber of bytes occ!-ied by the variable. 6/. dtyG2 and m'lG22 denotes a $EBN6A(22) col!mn. scl 7 the scale (for $EBN6A col!mns) #re 7 the -recision (for $EBN6A col!mns) value G the val!e of the bind variable G the dataty-e. #y-ical val!es are $ty 1 HAA2 or $HAA2 2 $EBN6A J =O$ 11 AO+ID 12 D#6 2 A+ 2& =O$ A+ L% A 112 =ON or $=ON 11 N=ON 11& NFI=6
--C : 6'ec!te a -reM-arsed statement. t this -oint, Oracle has all necessary information and reso!rces, so the statement is e'ec!ted. For e'am-le 6*6 2cG0,eG225,-G0,crG0,c!G0,misG0,rG0,de-G1,o/G&,-lhG%J&J41242,timG2J2%1J2L&0 Fetch : Fetch rows from a c!rsor . For e'am-le F6# &cG0,eGJJ%&,-G1,crG2%,c!G0,misG0,rG1,de-G0,o/G1,-lhG5%&%L&450,timG2J2%1J2%404 'T,T : =ines re-ort e'-lain -lan statistics for the n!mbered QEASOAR. #hese let !s "now the 9r!n time9 e'-lain -lan. For e'am-le S## 1 idG1 cntG4 -idG0 -osG1 obKG0 o-G9SOA# OAD6A N (crG0 -rG0 -wG0 timeG0 !s costG2 si:eG212J cardG1)9 i$ G =ine of the e'-lain -lan which the row co!nt a--lies to (starts at line 1). #his is ePectively the row so!rce row co!nt for all row so!rces in the e'ec!tion tree cnt G $!mber of rows for this row so!rce.
#i$ G arent id of this row so!rce. #os G osition in e'-lain -lan. o"; G ObKect id of row so!rce (if this is a base obKect). o#7<...< #he row so!rce access o-eration CT-ND transaction end mar"er. For e'am-le *#6$D rlb"G0, rd;onlyG1, timG2J2%%050&L1 rl"& 71 if a rollbac" was -erformed, 0 if no rollbac" (commit). r$*only 71 if transaction was read only, 0 if chan/es occ!rred. CO'c e $e# ty#e
c!rsor is closed .for e'am-le =OS6 &cG0,eG2,de-G0,ty-eG0,timG2J2%%050%JJ G E time (microseconds ro!nded to centiseconds /ran!larity on Li and above) G 6la-sed time (centiseconds -rior to Li, microseconds thereafter) G Aec!rsive de-th of the c!rsor G #y-e of close o-eration
Note : #imestam- are !sed to determine the time between any 2 o-erations. +e=erence : Betalin" QID LJ14.1R
Difference between '(%R )*+" and A)*+" in Oracle Data(uard Oracle 1ata >uard redo lo transport offers synchronous lo transport mode 9oQpt5ode K EP@CE: orasynchronous lo transport mode 9oQpt5ode K E>P@CE:. The difference is all about when the C=55IT happens . 9og@ptode C (D<=D): >s the name implies, P@C mode synchronizes the primary with the standby database and all 5 on the primary serer will @=T be committed until the los hae been successfully transported to the standby serers. The synchronous lo transport mode is required for the 5aximum 0rotection and 5aximum >ailability data protection modes. 9og@ptode C (DA<=D): Conersely, asynchronous mode 9>P@C: allows updates 95: to be committed on the primary serer before the lo file arries on the standby serers. The asynchronous lo transport mode is required for the 5aximum 0erformance data protection mode. There is a ery ood po st witten by "hawn Felley" related to ync and >sync in datauard . GW2 is an attribute of the =GM>2C!IHDMDTMn parameter which is used to specify the network transmission mode. pecifyin the P@C attribute 9which is the default:, tells the GW2 process to synchronously archie to the local online redo lo files at the same time it transmits redo data to archial destinations. pecifically, the P@C atrribute performs all network I+= synchornously in conAunction with each write operation to the online redo lo file. Transactions are not committed on the primary database until the redo data necessary to recoer the transactions is receied by the destination. The >P@C attribute perfoms all network I+= asynchronously and control is returned to the executin application or user immediately. When this attribute is specified, the GW2 process archies to the local online redo lo file and submits the network I+= request to the network serer 9@n process for that destination, and the GW2 process continues processin the next request without waitin for the network I+= to complete. $at $appens if t$e net#or5 bet#een t$e !rimar4 and R <= and A<= H or $at $appens if t$e standb4 database is s$utdo#n #it$ 9>R <= and A<=H This is dependent upon the database mode we hae set. If we hae set 5aximum 0rotection, we hae chosen a confiuration that uarantees that no data loss will occur. We hae set this up by specifyin the WG2, P@C, and >44I25 attributes of the =GM>2C!IHDMDTMn parameter for at least one standby database. This mode proides the hihest leel of data protection possible and to achiee this the redo data needed to recoer each transaction must be written to both the local online redo lo and the standby redo lo on at least one standby database before the transaction commits. To ensure data
loss cannot occur, the primary database shuts down if a fault 9such as the network oin down: preents it from writin its redo stream to at least one remote standby redo lo. If we hae set the 5aximum >ailability mode, we hae chosen a confiuration that proides the hihest leel of data protection that is possible without compromisin the aailablity of the primary database. ike the maximum database does not shut down if a fault preents it from writin its redo stream to a remote standby redo lo. Instead, the primary database operates in maximum performance mode until the fault is corrected and all aps in redo lo files are resoled. When all aps are resoled, the primary database automatically resumes operatin in maximum aailabitly mode. This uarantees that no data loss will occur if the primary database fails, but only if a second fault does not complete set of redo data bein sent from the primary database to at least one standby database. If we hae set the 5aximum 0erformance mode 9the default:, we hae chosen a mode that proides the hihest leel of data protection that is possible without affectin the performance of the primary database. This is accomplished by allowin a transaction to commit as soon as the redo data needed to recoer the transaction is written to the local online redo lo. The primary databaseEs redo data stream is also written to at least one standby database, bu that the redo stream is written asynchronously with respect to the commitment of the transactions that create the redo data. The maximum performance mode enables us to either set the GW2 and >P@C attributes, or set the >2C! attribute on the =GM>2C!IHDMDTMn parameter for the standby database destination. If the primary database fails, we can reduce the amount of data that is not receied on the standby destination by settin the GW2 and >P@C attributes. "f 9>R <= or A<= is deplo4ed #$at process(es) bring(s) t$e standb4 database bac5 into s4nc #it$ t$e primar4 FdatabaseG if t$e net#or5 is lost and is t$en restoredH Bo# does it do itH >ain, this is dependent upon the mode we hae chosen for our database. The GW2 process 9and possibly the @n process if we hae multiple standby databases: is responsible for closin the ap. 5y biest question is, when the network to the standby is lost with P@C or >P@C, where is the information queued and how is it retransmitted once the network has been re?establishedU This implies that our database has been set to either maximum aailability or maximum performance mode. We cannot use the >P@C attribute with maximum protection mode. The information is queued in the local online redo lo and the GW2 9and the @n: process will transmit the data to the standby databaseEs online redo lo file to close the ap once the network connectiity has been re?established Gap recoery is handled throuh the pollin mechanism. 4or physical and loical standby databases, =racle Chane ata Capture, and =racle treams, ata Guard performs ap detection and resolution by automatically retriein missin archied redo lo files from the primary database. @o extra confiuration settins are required to poll the standby database9s: to detect any aps or to resole the aps. The important consideration here is that automatic ap recoery is contient upon the aailablity of the primary database. If the primary database is not aailable and we hae a confiuration with mulitple physical standby databases, we can set up additional initialization parameters so that the 2edo >pply can resole archie aps from another standby database. It is possible to manually determine if a ap exists and to resole those archie aps. To manually determine if a ap exists, query the H*>2C!IHDMG>0 iew on our physical standby database. If a ap is found, we will then need to locate the archied lo files on our primary database, copy them to our standby database, and reister them.
Understanding Inde,es "oncept
"ndexes plays and crucial role in the performance tunnin of a database . It is ery important to know how the index work i.e, how indexes fetches the dataEs from a tables . There is a ery ood post by rleis$man on the workin of indexes . etEs hae a look . $at is an "ndex H >n index is a schema obAect that contains an entry for each alue that appears in the indexed column9s: of the table or cluster and proides direct, fast access to rows. It is Aust as the index in this manual helps us to locate information faster than if there were no index, an =racle atabase index proides a faster access path to table data . loc5s 4irst we need to understand a block. > block ? or pae for 5icrosoft boffins ? is the smallest unit of disk that =racle will read or write. >ll data in =racle ? tables, indexes, clusters ? is stored in blocks. The block size is confiurable for any ien database but is usually one of 6Fb, b?Tree index is a data structure in the form of a tree ? no surprises there ? but it is a tree of database blocks, not rows. Imaine the leaf blocks of the index as the paes of a phone book . Dach pae in the book 9leaf block in the index: contains many entries, which consist of a name 9indexed column alue: and an address 92=WI: that tells us the physical location of the telephone 9row in the table:. The names on each pae are sorted, and the paes ? when sorted correctly ? contain a complete sorted list of eery name and address > sorted list in a phone book is fine for humans, beacuse we hae mastered "the flick" ? the ability to fan throuh the book lookin for the pae that will contain our taret without readin the entire pae. When we flick throuh the phone book, we are Aust readin the first name on each pae, which is usually in a larer font in the pae header. =racle cannot read a sinle name 9row: and inore the reset of the pae 9block:L it needs to read the entire block. If we had no thumbs, we may find it conenient to create a separate ordered list containin the first name on each pae of the phone book alon with the pae number. This is how the branch?blocks of an index workL a reduced list that contains the first row of each block plus the address of that block. In a lare phone book, this reduced list containin one entry per pae will still coer many paes, so the process is repeated, creatin the next leel up in the index, and so on until we are left with a sinle pae1 the root of the tree. 6or example : To find the name >allileo in this b?Tree phone book, we1 K 2ead pae '. This tells us that pae 7 starts with 4ermat and that pae starts with !awkin. K 2ead pae 7. This tells us that pae #8$ starts with 4yshe and that pae #8' starts with Garibaldi. K 2ead pae #8$, which is a leaf blockL we find GallileoEs address and phone number. K ThatEs itL # blocks to find a specific row in a million row table. In reality, index blocks often fit '$$ or more rows, so b?Trees are typically quite shallow. I hae neer seen an index with more than 8 leels. CuriousU Try this1 - select indexMname, bleelV' from userMindexes order by 3 L
userMindexes.bleel is the number of branch leels. >lways add ' to include the leaf leelL this tells us the number of blocks a unique index scan must read to reach the leaf?block. If weEre really, really, insatiably curiousL try this in -/0lus1 - accept indexMname prompt "Index @ame1 " - alter session set tracefileMidentifierKEindexMnameE L - column obAectMid newMalue obAectMid - select obAectMid from userMobAects where obAectMtype K EI@DQE and obAectMnameKupper9EindexMnameE:L - alter session set eents EImmediate trace name treedump leel obAectMidEL - alter session set tracefile identifierK"" L - show parameter userMdumpMdest
Gie the name of an index on a smallish table 9because this will create a JIG file:. @ow, on the =racle serer, o to the directory shown by the final !=W 0>2>5DTD2 userMdumpMdest command and find the trace file ? the file name will contain the index name. !ere is a sample1 ???? bein tree dump branch1 $x7<$77c< '$;$<3'7 9$1 nrow1 #38, leel1 ': leaf1 $x7<$77c; '$;$<3' 9?'1 nrow1 7;6 rrow1 7;6: leaf1 $x7<$77ca '$;$<3'< 9$1 nrow1 7;# rrow1 7;#: leaf1 $x7<$77cb '$;$<3'; 9'1 nrow1 7;# rrow1 7;#: leaf1 $x7<$77cc '$;$<33$ 931 nrow1 7;# rrow1 7;#: leaf1 $x7<$77cd '$;$<33' 9#1 nrow1 7;# rrow1 7;#: ... ... leaf1 $x7<$7;cf '$;$<;;' 9#3$1 nrow1 7# rrow1 7#: leaf1 $x7<$7;d$ '$;$<;;3 9#3'1 nrow1 7' rrow1 7': leaf1 $x7<$7;d' '$;$<;;# 9#331 nrow1 ;< rrow1 ;<: leaf1 $x7<$7;d3 '$;$<;;6 9#3#1 nrow1 <$ rrow1 <$: ????? end tree dump This index has only a ro ot branch with #3# leaf nodes. Dach leaf node contains a ariable number of index entries up to <$X > deeper index would be more interestin, but it would take a while to dump. II is for''' Contrary to popular belief, b is not for binaryL itEs balanced. >s we insert new rows into the table, new rows are inserted into index leaf blocks. When a leaf block is full, another insert will cause the block to be split into two blocks, which means an entry for the new block must be added to the parent branch?block. If the branch?block is also full, it too is split. The process propaates back up the tree until the parent of split has space for one more entry, or the root is reached. > new root is created if the root node splits. taerinly, this process ensures that eery branch will be the same lenth. Bo# are "ndexes used H Indexes hae three main uses1
•
To uic5l4 find specific ro#s b4 a7oiding a 6ull Table rane scan will find the first row in the rane usin the same technique as the Onique can, but will then keep readin the index up to the end of the rane. It is =F if the rane coers many blocks.
•
•
To a7oid a table access altoget$er If all we wanted to do when lookin up Gallileo in the phone book was to find his address or phone number, the Aob would be done. !oweer if we wanted to know his date of birth, weEd hae to phone and ask. This takes time. If it was somethin that we needed all the time, like an email address, we could sae time by addin it to the phone book. =racle does the same thin. If the information is in the index, then it doesnEt bother to read the table. It is a reasonably common technique to add columns to an index, not b ecause they will be used as part of the index scan, but because they sae a table access. In fact, =racle may een perform a 4ast 4ull can of an index that it cannot use in a 2ane or Onique scan Aust to aoid a table access. To a7oid a sort This one is not so well known, larely because it is so poorly documented 9and in many cases, unpredicatably implemented by the =ptimizer as well:. =racle performs a sort for many reasons1 =2D2 JP, G2=O0 JP, ITI@CT, et operations 9e. O@I=@:, ort?5ere Noins, uncorrelated I@? subqueries, >nalytic 4unctions:. If a sort operation requires rows in the same order as the index, then =racle may read the table rows ia the index. > sort operation is not necessary since the rows are returned in sorted order. espite all of the instances listed aboe where a sort is performed, I hae only seen three cases where a sort is actually aoided. *' >RO%! :
TD5D@T Y Y Y ' Y =2T G2=O0 JP =OJD >CCD JP G=J> I@DQ 2=WI Y D4M>CTMDQ0@ Y Y/ # Y I@DQ 2>@GD C>@ Y D4M>DQ0M0F Y ??????????????????????????????????????????????????????????????????????????????????????? 0redicate Information 9identified by operation id:1 ???????????????????????????????????????????????????????????????? 3 ? filter9">CTMDQ0@M>5T"$: # ? access9"2CMP"KECWE: @ote the =O
- select / from efMactlMexpns where srcMsys K ECWE and actlMexpnsMamt $ order by srcMsys ???????????????????????????????????????????????????????????????????????????????????????? Y Id Y =peration Y @ame Y ???????????????????????????????????????????????????????????????????????????????????????? Y $ Y DDCT T>TD5D@T Y Y Y/ ' Y T>JD >CCD JP G=J> I@DQ 2=WI Y D4M>CTMDQ0@Y Y/ 3 Y I@DQ 2>@GD C>@ Y D4M>DQ0M0F Y ????????????????????????????????????????????????????????????????????????????????????????
0redicate Information 9identified by operation id:1 ??????????????????????????????????????????????????? ' ? filter9">CTMDQ0@M>5T"$: 3 ? access9"2CMP"KECWE: @ote that there is no TD5D@T Y Y Y ' Y =2T =2D2 JP Y Y Y/ 3 Y T>JD >CCD JP G=J> I@DQ 2=WI Y D4M>CTMDQ0@ Y Y/ # Y I@DQ 2>@GD C>@ Y D4M>DQ0M0F Y ???????????????????????????????????????????????????????????????????????????????????????? 0redicate Information 9identified by operation id:1 ??????????????????????????????????????????????????? 3 ? filter9">CTMDQ0@M>5T"$: # ? access9"2CMP"KECWE: 3' 1"
- select distinct srcMsys from efMactlMexpns where srcMsys K ECWE and actlMexpnsMamt $ L ??????????????????????????????????????????????????????????????????????????????????????????????? Y Id Y =peration Y @ame Y ??????????????????????????????????????????????????????????????????????????????????????????????? Y $ Y DDCT T>TD5D@T Y Y Y ' Y =2T O@I-OD @==2T Y Y Y/ 3 Y T>JD >CCD JP G=J> I@DQ 2=WI Y D4M>CTMDQ0@ Y Y/ # Y I@DQ 2>@GD C>@ Y D4M>DQ0M0F Y ?????????????????????????????????????????????????????????????????????????????????????? 0redicate Information 9identified by operation id:1 ??????????????????????????????????????????????????? 3 ? filter9">CTMDQ0@M>5T"$: # ? access9"2CMP"KECWE: >ain, note the =O - with a ITI@CT, G2=O0 JP, or =2D2 JP that uses an index to sort can return Aust the first pae of matchin rows without hain to fetch the entire result set for a sort. This can be the difference between sub?second response time and seeral minutes or hours. 6ull table b?Tree index will be no help at all in a reduced scan unless the W!D2D clause compares indexed columns usin , B, IFD, I@, or JDTWDD@ operators. > b?Tree index cannot be used to scan for any @=T style operators1 e. XK, @=T I@, @=T IFD. There are lots of conditions, caeats, and complexities
reardin Aoins, sub?queries, =2 predicates, functions 9inc. arithmetic and concatenation:, and castin that are outside the scope of this article. Consult a ood - tunin manual. 5uch more interestin ? and important ? are the cases where an index makes a - slower. These are particularly common in batch systems that process lare quantities of data. To explain the problem, we need a new metaphor. Imaine a lare deciduous tree in our front yard. ItEs >utumn, and itEs our Aob to pick up all of the leaes on the lawn. Clearly, the fastest way to do this 9without a rake, or a leaf?ac...: would be et down on hands and knees with a ba and work our way back and forth oer the lawn, stuffin leaes in the ba as we o. This is a 4ull Table can, selectin rows in no particular order, except that they are nearest to hand. This metaphor works on a couple of leels1 we would rab leaes in handfuls, not one by one. > 4ull Table can does the same thin1 when a bock is read from disk, =racle caches the next few blocks with the expectation that it will be asked for them ery soon. Type this in -/0lus1 - show parameter dbMfileMmultiblockMreadMcount Nust to shake thins up a bit 9and to feed an undianosed obsessie compulsie disorder:, we decide to pick up the leaes in order of size. In support of this endeaour, we take a diital photoraph of the lawn, write an imae analysis proram to identify and measure eery leaf, then load the results into a Hirtual 2eality headset that will hihliht the smallest leaf left on the lawn. Inenious, yesL but this is clearly oin to take a lot loner than a full table scan because we coer much more distance walkin from leaf to leaf. o obiously 4ull Table can is the faster way to pick up eery leaf. Jut Aust as obious is that the index 9irtual reality headset: is the faster way to pick up Aust the smallest leaf, or een the '$$ smallest leaes. >s the number rises, we approach a break?een pointL a number beyond which it is faster to Aust full table scan. This number aries dependin on the table, the index, the database settins, the hardware, and the load o n the sererL enerally it is somewhere between 'Z and '$Z of the table. T$e main reasons for t$is are :
•
•
•
•
>s implied aboe, readin a table in indexed order means more moement for the disk head. =racle cannot read sinle rows. To read a row ia an index, the entire block must be read with all but one row discarded. o an index scan of '$$ rows would read '$$ blocks, but a 4T miht read '$$ rows in a sinle block. The dbMfileMmultiblockMreadMcount settin described earlier means 4T requires f ewer isits to the physical disk. Den if none of these thins was t rue, accessin the entire index and the entire table is still more I= than Aust accessin the table. o whatEs the lesson hereU Fnow our dataX If our query needs 8$Z of the rows in the table to resole our query, an index scan Aust wonEt help. @ot only should we not bother creatin or inestiatin the existence of an index, we should check to make sure =racle is not already usin an index. There are a number of ways to influence index usaeL once aain, consult a tunin manual. The exception to this rule ? thereEs always one ? is when all of the columns referenced in the - are contained in the index. If =racle does not hae to access the table then there is no break?een pointL it is enerally quicker to scan the index een for '$$Z of the rows.
Indexes are not a dark?artL they work in an entirely predictable and een intuitie way. Onderstandin how they work moes 0erformance Tunin from the realm of uesswork to that of scienceL so embrace the technoloy and read the manual.
ow to Identif. t/e )tatic and D.namic #arameter in Oracle ometimes, we may not ery sure whether an oracle parameter is static9restartin database is required to come under the action: parameter or d4namic9can be chaned without restartin: parameter . We can check this by usin the *parameter3 iew which is ery similar to *parameter hain few extra rows for lon parameters . The another difference between the *parameter and *parameter3 is that the format of the output .. 4or example, if a parameter alue say "x,y" in H*0>2>5DTD2 iew does not tell us if the pa rameter has two alues 9"x" and "y": or one alue 9"x, y": whereas H*0>2>5DTD23 makes the distinction between the list parameter alues clear. - select alue from *parameter W!D2D name IFD EcontrolMfilesE L
- select alue from *parameter3 W!D2D name IFD EcontrolMfilesE L
!ere, If IDM5=I4I>JD parameter is true, the parameter can be chaned on session le7el , and if IDM5=I4I>JD or II@T>@CDM5=I4I>JD is true, then parameter can be chaned on s4stem le7el. !ere is an example - DDCT name,Halue ,IDM5=I4I>JD , II@T>@CDM5=I4I>JD 42=5 *parameter3 W!D2D name IFD EZtaretZE L
%/at is redo log t/read in oracle =n oolin about the redo log t$read, i hae not found proper documentation that clearly explains clearly what the redo lo thread is . !ere i am tryin to coer the redo lo threads in case of sinle instance and 2>C takin reference from >FT=5 site . Dach instance has itEs own personal set of redo and each redo thread is made up of at least two roups that hae one or more members 9files: .Two instances will neer write to the same redo files ? each instance has itEs own set of redo los to write to . >nother instance may well 2D> some other instances redo los ? after that other instance fails for example ? to perform recoery. !ere is a scenario which helps us to understand the thread concepts . 5ost H* iews work by selectin information from the correspondin GH* iew with a predicate "where instanceMid K Bthat instance". o H*DI=@ in sinle Instance9i.e, ': is actually -select / from *instance where instMidK ' L
=n a three node 2>C database, if we select from *session, we et sessions from that instance only. electin from GH*DI=@ creates parallel query slaes on the other instances and ets the information back to our session. This works fine in almost all cases. There are few exceptions1 in case of redo los, the 2>C instance must see all the redo los of other instances as they become important for its recoery. Therefore, H*=G actually shows all the redo los, of all the instances, not Aust of its own. Contrast this with H*DI=@, which shows only sessions of that instance, not all. o, if there are # lo file roups per instance 9actually, per "thread": and there are # instances, H*=G on any instance will show all ; lofile roups, not #. When we select form GH*=G, remember, the session ets the information from other instances as well. Onfortunately, the 0- serers on those instances also et ; records each, since they also see the same information seen by the first instance. =n a three instance 2>C, we will et #Q; K 3 records in GH*=GX To aoid this1 *') >lways select from H*=G, H*=G4ID and H*T!2D> in a 2>C instance. GH* iews are misleadin or 2') >dd a predicate to match T!2D>S with I@TMI. 9Jeware1 thread numbers are by default the same as the instanceMidL but we may hae defined a different thread number while creatin the database: as - select / from *lo where instMloKthreadS L
User Managed ot acups in Oracle cold bac"!- does have the somewhat bad side ePect of wi-in/ o!t o!r shared -ool, o!r b!Per cache and -reventin/ o!r !sers from lo//in/ in to do wor". O!r database is li"e a car, it r!ns better when it is warmed !-. If we want to cold start it M be -re-ared for ro!/h r!nnin/ when we restart as we have to reb!ild that shared -ool, that b!Per cache and so on . I wo!ld never -ic" cold over hot /iven the chance. $o bene8t, only downsides (cc. to #"ye). #he only "ind of bac"!- we do on o!r -rod!ction systems here is hot . #here are two ways to -erform Oracle bac"!- and recovery 1.) +ecovery >ana0er +>,N) : It is an Oracle !tility that can bac"!-, restore, and recover database 8les. It is a feat!re of the Oracle database server and does not re7!ire se-arate installation. 2.) @serA>ana0e$ "ac&u# an$ recovery : +e !se o-eratin/ system commands for bac"!-s and S<=Tl!s for recovery. #his method is called !serMmana/ed bac"!- and recovery and is f!lly s!--orted by Oracle, altho!/h !se of AB$ is hi/hly recommended beca!se it is more rob!st and /reatly sim-li8es administration. #here are basically t%o ty#es o= "ac&u# .#he bac"!- are as
1.) Consistent 8ac&u# : #his is also "now as Cold Backup . consistent bac"!- is one in which the 8les bein/ bac"ed !- contain all chan/es !- to the same system chan/e n!mber (S$). #his means that the 8les in the bac"!- contain all the data ta"en from a same -oint in time . 2.) nconsistent 8ac&u# : #his is also "nown as Hot backup . n inconsistent bac"!- is a bac"!- in which the 8les bein/ bac"ed !- do not contain all the chan/es made at all the S$s . #his can occ!r beca!se the data8les are bein/ modi8ed as bac"!-s are bein/ ta"en.
•
#here are some DNs which -refer oracle !serMmana/ed bac"!-s.#hey -!t their database into bac"!mode -rior to bac"in/ !- and ta"e it o!t of bac"!- mode after bac"!-. If we 9re /oin/ to -erform !serMmana/ed bac"!-s, we m!st bac" !- all of the followin/ 8le Data8les
•
ontrol 8les
•
Online redo lo/s (if -erformin/ a cold bac"!-)
•
•
#he -arameter 8le (not mandatory ) rchived redo lo/s
•
assword 8le if !sed #he below dia/ram shows the +hole Database Nac"!- O-tions
hot bac"!- re7!ires 7!ite a bit more wor" than cold bac"!-.Nelow are ste-s re7!ired for ot bac"!-. 'te# 1 : Chec& the lo0 mo$e o= the $ata"ase +henever we /o for hot bac"!- then the database m!st be in archivelo/ mode . S<=> S6=6# =O;BOD6 FAOB H3D#NS6 @ =O;BOD6 MMMMMMMMMMMMMMM AIH6=O 'te# 2 : !ut the $ata"ase into "ac&u# mo$e If we are !sin/ the oracle 10/A2 or later , then we can -!t the entire database into bac"!- mode and if we are !sin/ the oracle -rior to 10/A2 ,then we have to -!t each tables-ace in bac"!- mode . In my case , I am havin/ 11/A2 . S<=> alter database be/in bac"!- @ Database altered. In case of oracle -rior to 10/A2 !se the below command as S<=> set echo oP S<=> set headin/ oP S<=> set feedbac" oP S<=> set termo!t oP S<=> s-ool bac"mode.s7l S<=> select 9alter tables-ace 9UUnameUU9 be/in bac"!- @9 #ables-ace in bac"!- mode from v3tables-ace@ - spool off S<=> VWbac"mode.s7l
'te# 3 : 8ac&u# all the $atafles o-y all the data8le !sin/ the o-eratin/ system command and aste it on the desired bac"!- location .Beanwhile,we can verify the stat!s of the data8le by !sin/ the v3bac"!- view to chec" the stat!s of the data8les. S<=> select T from v3bac"!- @ FI=6 S##ES $6 #IB6 MMMMMMMMMM MMMMMMMMMMMMMMMMMM MMMMMMMMMM MMMMMMMMM 1 #IH6 L%41J1 0MAM12 2 #IH6 L%41J4 0MAM12 #IH6 L%41L 0MAM12 & #IH6 L%41LL 0MAM12 5 #IH6 L%4205 0MAM12 % #IH6 L%4211 0MAM12 4 #IH6 L%4214 0MAM12 J #IH6 L%422 0MAM12 L #IH6 L%422L 0MAM12 #he ol!mn S##ESG#IH6 shows that the data8les are in bac"!- mode .
'te# : Ta&e out the $ata"ase =rom "ac&u# mo$e If we are !sin/ 10/A2 or above version of oracle , we !se the below command to ta"e o!t the database from bac"!- mode as S<=> alter database end bac"!- @ Database ltered If we are havin/ version -rior to 10/A2 , then we !se the below command as above S<=> set echo oP S<=> set headin/ oP S<=> set feedbac" oP S<=> set termo!t oP S<=> s-ool end;mode.s7l S<=> select 9alter tables-ace 9UUnameUU9 end bac"!- @9 tables-ace in bac"!- mode from v3tables-ace @ - spool off S<=> &C1[endmode.sql
'te# : '%itch the re$olo0 fle an$ "ac&u# archivelo0s fter ta"in/ the database o!t of ot Nac"!- we m!st switch lo/8le (-referably more than once) and bac"!- the archivelo/s /enerated .+e may bac"!- archivelo/s while the database is in bac"!- mode b!t we m!st also bac"!- the 8rst archivelo/(s) after the end bac"!-. #he best method to do both is to r!n the S<= command alter system archive lo/ c!rrent. #his switches the lo/8le b!t does not ret!rn the -rom-t !ntil the -revio!s redo lo/ has been archived. +e can r!n alter system switch lo/8le, b!t then we won9t be s!re that the latest redo lo/ has been archived before we move on to the ne't ste-. S<=> alter system archive lo/ c!rrent @ System altered. S<=> System altered. $ow bac"!- the archivelo/s to the bac"!- location . 'te# : 8ac& u# the control fle $ow , we can bac"!- the control8le as binary 8le and as h!man readable .+e sho!ld !se both methods to bac" !- the control 8le@ either one may come in handy at diPerent times . #he commands are as (Human readable) S<=> alter database bac"!- control8le to trace @ or Database altered. S<=> alter database bac"!- control8le to trace as 9Xbac"!- location>9 @ Database altered. (Binary format) S<=> alter database bac"!- control8le to 9Xbac"!- location>9 @ Database altered.
'te# : 8ac&u# the #ass%or$fle an$ s#fle +e can bac"!- the -assword8le and s-8le tho!/h it is not mandatory.
•
•
•
•
•
'ome !oints Worth +emem"erin0 +e need to bac"!- all the archived lo/ 8les, these 8les are very im-ortant to do recovery. It is advisable to bac"!- all of tables-aces (e'ce-t readMonly tables-aces), else com-lete recovery is not -ossible. Nac"!- of online redo lo/ 8les are not re7!ired, as the online lo/ 8le has the end of bac"!mar"er and wo!ld ca!se corr!-tion if !sed in recovery. It is referable to start the hot bac"!-s at low activity time. +hen hot bac"!-s are in -ro/ress we cannot sh!tdown the database in $OAB= or IBB6DI#6 mode (and it is also not desirable to NOA#).
Difference between Dataguard and Active Dataguard
I found people are bit confused between atauard and >ctie ata uard . They assume that >ctie datauard is hain different confiuration or properties . !ere, i hae tried to coer the >ctie datauard . Acti7e 1ata >uard is a new option for =racle atabase '' Dnterprise Ddition . =racle >ctie ata Guard enables read?only access to a physical standby database . >ctie ata Guard is the possibility to query our 0hysical tandby atabase while it is constantly actualized to reflect the state of the 0rimary atabase . It is additional to '' ata Guard and comes with an extra charge . =racle >ctie ata Guard enhances the -uality of erice 9-o: for production databases by off? loadin resource?intensie operations to one or more standby databases, which are synchronized copies of the production database. With =racle >ctie ata Guard, a physical standby database can be used for real?time reportin, with minimal latency between reportin and production data. Compared with traditional replication methods, >ctie ata Guard is ery simple to use, transparently supports all datatypes, and offers ery hih performance. =racle >ctie ata Guard also allows backup operations to be off?loaded to the standby database, and be done ery fast usin intellient incremental backups. =racle >ctie ata Guard thus is a ery effectie way to insulate interactie users and critical business tasks on the production system from the impact of such lon?runnin operations. =racle >ctie ata Guard proides the additional benefit of hih aailability and disaster protection by quickly failin oer to the standby database in the eent of a planned or an unplanned outae at the production site. T$e Acti7e 1ata >uard contains t$e follo#ing features 1 •
0hysical tandby with 2eal?time -uery
•
4ast Incremental Jackup on 0hysical tandby
•
>utomatic Jlock 2epair If a physical standby database in a ata Guard confiuration has any of the aboe features enabled, then the >ctie ata Guard option must be licensed for eery such physical standby, and also for the primary database. on7ersion from !$4sical standb4 to Acti7e 1ata >uard : We can conert the physical standby into actie ata Guard standby . Jelow are the steps *') top >pply erices - alter database recoer manaed standby database cancel L 2') hut the database and open in mount stae - shut immediate - startup mount - alter database recoer manaed standby database usin current lofile disconnect L - alter database open L It enables us to hae a physical standby read only open, while redo apply is still done in the backround . Bo# to $ec5 if Acti7e 1ata >uard is Enabled or =ot Ose the followin query to confirm that ata Guard is in actie mode1 - select EOsin >ctie ata GuardE >G m.process like E520ZE L >G ??????????????????????? Osin >ctie ata Guard
from *manaedMstandby m,*database d where
or from standby datbase - select openMmode,controlfileMtype from *databaseL
=0D@M5=D ???????????????????? 2D> =@P WIT! >00P
C=@T2=4ID ?????????????????? T>@JP
If the query does not return the aboe result, and instead returns 1 no rows selected, then >ctie ata Guard is not enabled.
Difference etween Uni3ue Inde,es and Uni3ue "onstraints There is a ery eneral confusion that wheneer we create a unique key constraint or primary key then a correspondin index is created . 0rimary key and Onique key creates the unique indexes , but this is not always true . ets hae a look ... - create table T' 9id number : L Table created. - alter table T' add constraint TMIMIQ unique9id: L Table altered. - select indexMname,tableMname,uniqueness from dbaMindexes where tableMnameKET'EL I@DQM@>5D T>JDM@>5D O@I-OD@D ???????????????? ??????????????????? ??????????????? TMIMIQ T' O@I-OD - select constraintMname,constraintMtype tableMnameKET'E and ownerKE!2E L C=@T2>I@TM@>5D C T>JDM@>5D ???????????????????????? ???? ????????????????? TMIMIQ O T'
,tableMname
from
dbaMconstraints
where
!ere, we see that when we hae created a table unique index et created . @ow hae another look .. - create table T3 9id number : L Table created. - create unique index T3MidMidx on T39id: L Index created. - select indexMname,tableMname,uniqueness from dbaMindexes where tableMnameKET3E L I@DQM@>5D T>JDM@>5D O@I-OD@D ?????????????????? ?????????????????? ??????????????? T3MIMIQ T3 O@I-OD - select constraintMname,constraintMtype tableMnameKET3E and ownerKE!2E L no rows selected - alter table T3 add constraint T3MIMIQ Table altered.
,tableMname
from
dbaMconstraints
where
unique9id: L
@ow, we expectin two indexes i.eL one from the unique index and other from unique constraints . letEs look on the below query 1 - select constraintMname,constraintMtype ,tableMname tableMnameKET3E and ownerKE!2E L C=@T2>I@TM@>5D C T>JDM@>5D ????????????????????????? ???? ?????????????????? T3MIMIQ O T3
from
dbaMconstraints
where
- drop index T3MIMIQL drop index T3MIMIQ / D22=2 at line '1 ORA-022&: cannot drop index used for enforcement of unique+primary key >boe query show only one indexes . !ence from the aboe demo, we can only say that " a unique constraint does not necessarily create an index or a unique constraint does not necessarily create a UNIQUE index " . If we want a unique index in place, it is suested we should explicitly create it by usin C2D>TD O@I-OD I@DQ . > primary key or unique constraint is not uaranteed to create a new index, nor is the index they create uaranteed to be a unique index. Therefore, if we desire a unique index to be created for query performance issues, we should explicitly create one. > question may arises that why do we need a unique constraint when we already hae a unique indexU The reason are *' ) The difference between a unique index and a unique constraint starts with the fact that the constraint is a rule while the index is a database obAect that is used to proide improed performance in the retrieal of rows from a table. It is a physical obAect that takes space and is created with the command . 2') we can use either a unique =2 non?unique index to support a unique constraint. Constraints are metadata, more metadata is ood. We can define a forein key to a unique constraint, not so a unique index. 3') > constraint has different meanin to an index. It ies the optimiser more information and allows us to hae forein keys on the column whereas a unique index doesnEt. Jut most importantly because it is the riht way to do it.
%/at is bootstrap 8ootstra# is a techni7!e for loadin/ the 8rst few instr!ctions of a com-!ter -ro/ram into active memory and then !sin/ them to brin/ in the rest of the -ro/ram.
What is "ootstra# in Oracle ? In Oracle, Nootstra- refers to loadin/ of metadata (data dictionary) before we O6$ the database.Nootstra- obKects are classi8ed as the obKects (tables inde'es cl!sters) with the obKect;id below 5% as bootstra- obKects. #hese obKects are mandatory to brin/ !- an instance, as this contains the most im-ortant metadata of the database.
What ha##ens on $ata"ase startu#? #his shall be e'-lained by settin/ the S<=;#A6 while o-enin/ the database.onnect as sysdba and do the followin/ S<=> start!- alter
mo!nt session
set
eents
- alter - alter
E'$$67
trace
name
@ context
database session
set
- OAD6NE - OAD6NE #A6FI=6;$B6
eents
foreer,
leel
'3
open E'$$67
trace
name
E
L L
context off
E@
S6#BID
#he s7l;trace of the above -rocess e'-lains the followin/ o-erations behind start!-. #he bootstrao-eration ha--ens between BOE$# sta/e and O6$ sta/e. 1.) #he 8rst S<= after in the above trace shows the creation of the bootstra-3 table. Somethin/ similar to the followin/ create table bootstra-3 ( line n!mber not n!ll, obK n!mber not n!ll, s7l;te't varchar2(&000) not n!ll) stora/e (initial 50Y obKno 5% e'tents (8le 1 bloc" 44)) #his sys.bootstra-3 table contains the DD=Zs for other bootstra- tables (obKect;id below 5%). ct!ally these tables were created internally by the time of database creation (by s7l.bs7), #he create DD= -assed between BOE$# and O6$ sta/e will be e'ec!ted thro!/h diPerent driver ro!tines. In sim-le words these are not standard A6#6 DD=s.
+hile startin/ !- the database oracle will load these obKects into memory (shared;-ool), (ie) it will assi/n the relevant obKect n!mber and refer to the data8le and the bloc" associated with that. nd s!ch o-erations ha--en only while warm start!-. @ The internals of the above explained in ‘kqlb.c.
2.) $ow a 7!ery e'ec!ted a/ainst the sys.bootstra-3 table, which holds the create s7lZs for other base tables. select line, s7l;te't from bootstra-3 where obK [G 1 (5%) S!bse7!ently it will create those obKects by r!nnin/ those 7!eries. ObKect n!mber 0 \ (System Aollbac" Se/ment) ObKect n!mber 2 to 55 (Other base tables) ObKect n!mber 1 is $O# !sed by any of the obKects. 3.) erforms vario!s o-erations to "ee- the bootstra- obKects in consistent state. E-on the s!ccessf!l com-letion of bootstra- the database will do the other tas"s li"e recovery and will o-en the database.
Which o";ects are classife$ as "ootstra# o";ects in oracle $ata"ase? ObKects with data;obKect;id less than 5% are classi8ed as core bootstraobKects.#he obKects are added to the bootstra-. #he obKects aPected are hist;head3 hist/rm3 i;hh;obK;col i;hh;obK;intcol i;obK;intcol i;h;obK;col c;obK;intcol From 10.1 the followin/ obKects have been added 8'ed;obK3 tab;stats3 ind;stats3 i;8'ed;obK3;obK
i;tab;stats3;obK i;ind;stats3;obK obKect;!sa/e #hese additional obKects shall be reMclassi8ed (or) i/nored by followin/ methods. 1. O-enin/ the database in mi/rate mode 2. Esin/ event J00 6vent J00 aPects the bootstra- -rocess of loadin/ the 8'ed cache in "7lblfc(). er defa!lt certain obKects are mar"ed as bootstra- obKects (even tho!/h they are not de8ned as s!ch in sys.bootstra-3) b!t by settin/ the event they will be left as nonMbootstra--ed.
What is "ootstra# #rocess =ailure? or O+,ABBB #his OAM0040& error S6AIOES if re-orted at start!-. #his error refers to some -roblem d!rin/ bootstra- o-eration. ny OAM0040& error on S#A#E A6OH6A is serio!s, this error normally rose d!e to some inconsistency with the bootstra- se/ments (or) data corr!-tion on bootstra-3 (or) any of the base tables below obKect;id 5%. fter this error it mi/ht not allow to o-en that database.
When O+,ABBB shall occur? 1. #here is a -robable of this error when any !ns!--orted o-erations are tried to force o-en the database. 2. #his error can also occ!r when system data8le has corr!-ted bloc"s. (OAM0154J) . In earlier releases of oracle (-rior to 4..& and J.0.) this iss!e shall arise d!e to N!/ &&5L% #he o-tion is to restore it from a /ood bac"!- and recover it. M> If the !nderlyin/ ca!se is -hysical corr!-tion that is d!e to hardware -roblems then do com-lete recovery. M> If the iss!e is not relatin/ to any -hysical corr!-tion, then the -roblem co!ld be d!e some !ns!--orted actions on Nootstra-, and a oint In #ime Aecovery wo!ld be an o-tion in s!ch cas.
)4'5#lus &rror 'ogging in Oracle 11g S<=Tl!s is the commonly !sed tools by the DNs . 'l!lus -rror o00in0 is one of the new !sef!l feat!re in Oracle 11/ .It -rovides additional methods of tra--in/ errors . +hen error lo//in/ is enabled, it records s7l , -ls7l and s7lT-l!s errors and associated -arameters in an error lo/ table(S6AAOA=O by defa!lt) and we can then 7!ery the lo/ table to review errors res!ltin/ from a 7!ery. Note : It is a 11/ S<=Tl!s feat!re not with database en/ine. Why -rror o00in0 ? +e normally s-ool the synta' to ca-t!re the errors from the scri-ts and trac" the s-ool lo/s for the error o!t-!t . #his is wor" 8ne for sin/le or few scri-t b!t c!mbersome when m!lti-le scri-ts are involved.Secondly we need the OS -ath to store the scri-ts,-ermission and all . #o overcome from this scenario9s 6rror =o//in/ is !sef!l feat!re to ca-t!re and locate the errors in the database table rather than the OS 8les .
'te#s to ,ctivate the -rror o00in0 :
1.) Chec& the status o= -rror o00in0 : #o chec" the stat!s of error lo//in/ , 8re the below command S<=> show errorlo//in/ errorlo//in/ is OFF Note: 6rror lo//in/ is set OFF by defa!lt.
2.) -na"le the -rror o00in0 : +henever we enable the error lo/in/ the defa!lt table S6AAOA=O is created . 6nable by !sin/ the below command S<=> set errorlo//in/ on S<=> show errorlo//in/ errorlo//in/ is O$ #N=6 SO##.S6AAOA=O s, we see that the defa!lt table S6AAOA=O is created in scott schemas, since the c!rrent !ser is scott . ence, s-errorlo/ table is created c!rrent !ser .
Creatin0 a @ser Defne$ -rror o0 Ta"le : +e can create one or more error lo/ tables to !se other than the defa!lt . Nefore s-ecifyin/ a !ser de8ned error lo/ table , let9s have loo" on defa!lt errorlo/ S<=> desc s-errorlo/ $ame $!ll] MMMMMMMMMMMMMMMM MMMMMMMM ES6A$B6 #IB6S#B SAI# ID6$#IFI6A B6SS6 S##6B6$#
#y-e MMMMMMMMMMMMMMMMMMMM HAA2(25%) #IB6S#B(%) HAA2(102&) HAA2(25%) =ON =ON
4or each error, the error loin feature los the followin bits of information. #o !se a !ser de8ned lo/ table, we m!st have -ermission to access the table, and we m!st iss!e the S6# 6AAOA=OI$ command with the #N=6 schema.tablename o-tion to identify the error lo/ table and the schema if a--licable. ere is synta/ to create !serMde8ned table.. S<=> set errorlo//in/ on table QschemaR.QtableR =or e/am#le : S<=> set errorlo//in/ on table hr.6rror;lo/;table
Demo to create userA$efne$ ta"le
'te# 1 : Create the ta"le : If we want create the error lo//in/ !serMde8ned table and if table doesnot e'ist then /et the below error as S<=> set errorlo//in/ on table hr.6rror;lo/;table '!2A1B: 6rrorlo//in/ table, role or -rivile/e is missin/ or not accessible create the table as S<=> create table r.6rror;lo/;table ( !sername varchar(25%), timestam- #IB6S#B, scri-t varchar(102&), identi8er varchar(25%), messa/e =ON, statement =ON) E #able created. 'te# 2 : Create userA$efne$ error lo00in0 ta"le S<=> show errorlo//in/ errorlo//in/ is OFF S<=> set errorlo//in/ on table hr.6rror;lo/;table
S<=> show errorlo//in/ errorlo//in/ is O$ #N=6 hr.6rror;lo/;table 'te# 3 : enerate some errors S<=> selet T from em-loyees @ '!2AB3: !n"nown command be/innin/ selet T fr... M rest of line i/nored. S<=> select T from em-loye @ select T from em-loye T 6AAOA at line 1 O+,ABB52: table or view does not e'ist S<=> set lines:e 2000 '!2AB14: !n"nown S6# o-tion lines:e 'te# : Chec& the error lo00in0 =rom the userA$efne$ errorlo0 S<=> select T from hr.6rror;lo/;table @ - commit L
+itho!t commit, other sessions wonZt see this information. ere i have commit it and ta"en the o!t-!t from other session for the sa"e of -ro-er formattin/ -!r-ose.
+e can tr!ncate to clear all e'istin/ rows in the error lo/ table and be/ins recordin/ errors from the c!rrent session. as S<=> set errorlo//in/ on tr!ncate S<=> select T from 6rror;lo/;table @ $o rows selected
+e can also set an !ni7!e identi8er to ma"e it easier to identify the lo//in/ record.+e can !se it to identify errors from a -artic!lar session or from a -artic!lar version of a 7!ery. S<=> set errorlo//in/ on identi8er 9BAY9 S<= > select T from em-loy @ select T from em-loy T 6AAOA at line 1 O+,ABB52: table or view does not e'ist $ow chec" the identi8er S<=> select T from hr.6rror;lo/;table @
+e can delete records as the re/!lar table. S<=> delete hr.6rror;lo/;table where ID6$#IFI6AG9BAY9 @ S<=> commit@
Disa"le -rror o00in0 : S<=> set errorlo//in/ OFF S<=> show errorlo//in/ errorlo//in/ is OFF
