SAP BASIS — ORACLE DATABASE– NOMOUNT-MOUNT-OPEN
- December 25, 2023
- Posted by: MainInstructor
- Category: C Go Java Oracle Database SQL
Video Title: SAP BASIS — ORACLE DATABASE– NOMOUNT-MOUNT-OPEN
Mhm data file Rog file archive file pile f is where we we store the original data of the yeah original data and whenever you do a transaction uh the data will generate along with the data red also generate and the r entry will go and in
R F the back up of your R log files are Arch log files if your database is running in Arch log mode State then all your online Rog files will be backed up they are also called as offline R loog files or or else AR log files what is p file
Guys it is a text file sir okay mhm P file is a texture file will usually starts with the vile only okay and it contains the memory configuration of a data uh yeah to start your Aral instance you need some memory configuration right that memory configuration information
Your SGA information will be stored in your P all your memory information p is missing what happens if P file is missing what happens will not start sir because memory configuration will store in that yeah the instance itself not start the instance the Oracle instance the memory will not be allocated no
Oracle instance no background process will start nothing will happen without P can I start a database Oracle database no no sir what is what is the naming Convention of P file Sid okay what is the location of P file location of P file are Oracle home Oracle home uh database or
DBS yeah yeah and what is control file it exact path of the data control file is a binary file it contains a name and Sid of the database and exact path and location of your data files Rog files and AR log files at the same time it has information about your
Current running log sequence number and scn information everything will be there with log sequence uh what is that control file what is SC system change number so Oracle database has some scn kind of thing the internal clock which tracks all the changes happened in your database for
Each and every commit it will give you one scn number another one is lock sequence number lock sequence number means every R loog file will have one lock sequence one one R log file is running that is log sequence number one for second one log sequence number two
For third one whenever it is switching the log then one log sequence number will be created is it fine with everyone all the these things any doubt here pile is a text file control file is a binary file what is the control file naming convention CNL s d do dvf location of control
Files sa sap data 1 orog A and orog B right yes or no yes sir oh can anyone explain me SGA system Global area what is SGA system Global area the memory configuration whenever you try to start your databas now it will occupy some memory that memory is called as SG in
Oracul that is also called as oracul architecture so this SGA contains few memory areas the total SGS is for example we have taken that example of 6gb if your sgsi is 6gb when you start your Oracle database what happens it will occupy 6gb of memory out
Of that 6gb we have some other memory areas what are they buffer cachier redock buffer shared pool large pool Java pool stream pool and fixed SGA if you combine all these memory areas the total will be your SGA size remember here uh these colored things are mandatory things this gray color are optional
Large pool Java pool stream pool this buffer cache you know about buffer cache buffer cache has three type of buffers what is that pinned buffer buffer pinned buffer and dir buffer free buffer dirty buffers means committed data pinned buffer means written yet to commit free buffer means free buffer so whenever you
Do any transaction first the data will generate in buffer cache the redu entry will generate in Ru log buffer means before going to the hard disk files first the data is generating in your RAM first the data will generate in Ram later it will be moved to your data
Files and Rog files whatever the information is there in buffer cache will be be return to your data files the information of Rog files Rog buffer will be return to your Rog files who will write read this Rog buffer information to Rog files log writer who will write
Buffer cach information to your data files dbor there are five of processes right dbor log rator checkpoint smon and pmon when this DB writer will write there are three conditions one is when one uh when checkpoint initiates checkpoint is another process when checkpoint initiates a DB writer to
Write all the dir buers to data files means all the committed data to data files it will have some time after that uh for example each and every 5 minutes or 10 minutes this checkpoint will initiate DB writer to write all the things or else when 1 by3 of buffer cach
A is dirt means if this buffer cach a is having 1 by3 of committed data then also DB wror write all the data to data F when there is no free free space there is no chance right at that condition also this buffer cach is a dirty buffer
Committed data will be return to your databas but when it comes to redog files log writer how rck wrer will write when you say commit that means if I create a sales order immediately the data will come into buffer cache and Rog buffer from Rog buffer immediately it will go
To Rog files but not to data files why immediately commit when you say commit your log rator is writing this log buffer information to your redog files and every 3 seconds and when there is no free space in redog buffer very very importantly before DB writer before DB writer these four
Conditions so who is aggressive here log writer or DB writer who is aggressive lator or dbor Lor logor right so for example uh some 20 to 30 transactions happened immediately my uh database shut down do you have all the committed transactions in your data files no that will be in buffer cach
Only but all the transaction you will get from Rog files are you getting guys or you forgot go Goa gone or did you remember these things you remember I hope so this oracular architecture is having some logic boss it is not just like sap sap I don’t find any logic in
Sap but this oracular architecture is there is a connection between one topic to another topic yes or no did you feel the same guys I’m on mobile data see I’m confused whether my voice is audible to you or not please respond yes sir we are getting you yes please please respond okay be
Interactive so that I’ll get confused okay see so what we were discussing yeah did you you guys remember everything right so if data files suddenly my power gone what happens this is all your RAM memory right your RAM memory is the volatile memory that means all the data from Ram will be vanished
Deleted from where you recover the data in data files you don’t have everything up toate data you will have everything in data files but up toate data you will get from R log files so if your redo entries are safe if your redo log files are safe your database is safe
You can recover by using Rog files you can do recovery remember this and what is shared pool share pool is for passing of SQL statements whenever you throw some SQL statement it will do passing paring contains syntax check and semantic check syntax means the structure of the statement semantic
Means the meaning of the statement if you throw an SQL statement it will check whether the structure is SQL is a structure coding language right structure should be good and the meaning should be also good then only it will compile your SQL statement so here in a
Shared pool we are having two things one is Library cache second one is dictionary cache Library cache contains the user executed SQL statements passing data dictionary G contains whenever you execute some SQL statement system also calls some internal SQL statements to bring the data out that SQL statements
Passing data will be shed in your dictionary caching so if your shared pool size is big if you give shared pool size generous then it will hold more parsing data if you throw the same SQL statement one more time it won’t do the parsing already the parsing information
Is there already that statement is valid statement immediately it will compile and it will give the result if you throw a new SQL statement which is not there in library cach and dictionary cach then it will take some time to do the passing syntax check and semantic check then if
Everything is okay then it will compile and it will give the result so which one is uh good for you already you are having your SQL statement there or uh sending a new SQL statement which one is good please respond whether it may be right or wrong already having a skill stud already
Having a skill great and large pool for plsql statements execution of plsql programs it will use large pool area for execution of java programs it will use Java pool area for execution of no sorry for data replication it will use stream pool area these three memory areas are
Optional you can if you want you can give that memory areas or else if you don’t give large pool size Java pool size stream pool size it will use shared pool area at the time of plsql program executions Java programs executions and for at the time of data replication it
Will use shared pool area but in SGA the mandatory memory areas you have to Define is buffer cache redog buffer shared pool and fixed SGA is it fine with everyone yes yes no yes yes yeah uh and uh we we discussed each and every topic differently right uh data files redog
Files AR files scn P file control file and today we’ll try to combine all these processes how your H database is starting how it is reading your data files what happens in a back end we’ll see at a sh I’ll try to cover that uh see do you know all these things P
File control file data file redog file archive file and the instance what is the instance guys anyone instance a combination of processes and combination of processes plus what process yeah what RAM memory means what in we have a name for that ex s right SGA plus some processes what are the processes dbor
Logor checkpoint right These processes who will start this processes your processor your processor right your processor right who will give this SGA memory your RAM the combination of this Ram allocation of this RAM memory plus processor giving processes this is collectively called as a instance am I right this is collectively called as
Instance instance wait for a minute see this image we discuss this see database instance is nothing but your processes dbor log rator checkpoint s PM Plus the memory structure your SGA buffer cach R buffer shed pool large pool stream pool Java pool and fixed SGA the combination of these two yellow part is
Your database instance and this data files log files archive files P file your control file where you will get all these things in a hard disk this is completely logical one this is physical one in hard disk you get P file you have a location control file you have a location data file
Location loog file location archive file location these are all you will find it hard disk level the combination of your logical plus physical structure will create your database management system so to start your database you need to start instance as well as data files log files P file
Physical files as well as this logical structure logical structure is what memory should be allocated background process need to be start the combination of that is first of all you have to start the instance after starting instance you need data files log files everything the combination of all these
Logical and physical one both called as database we’ll see that now are you getting see yes sir what I told you when we start the database first which file it will read P P5 see what is the pile location DBS or database in Windows it is DBS and
Linux it is database or vice versa I’m not sure okay in Oracle home DBS or database location you’ll be having one file called parameter file that file is what init Sid raw file that file contains your SGA information right by reading that file it will it will start the instance
Instance means what it will occupy memory plus it will start some processes the combination of your SGA Ram uh SG occupation plus this processor collectively called as a instance first this will happen that means when you start your Oracle database when you say startup database it will read P file from this location
According to this in this P file you’ll be having your SGA information Rog buffer shared pool Java pool stre all the information will be there it will read that files and it will occupy according to that files your memory and will start some processes this thing is called as a
Instance along with this sgaa information this P file contains control file locations can I show you it contains control file locations see what is pile location Oracle home under this uh database init sid. right if I open this file it is having your shared pool size everything my screen is
Visible yeah yes yeah it is having all your memory information along with that memory information it is having one thing called control file location see control file location is what one is or log a second location is or log B and Third location is sap data
1 so it will read this this P file and it will start the instance after starting instance it will read this control files from these three locations what is a control Sid CNL sid. dbf CNL sid. dbf and the location is same Oracle Sid orog a orog b and sap
Theta 1 it will go and read that control file see what it is doing it will read after starting instance it is reading control file from the three locations this control file contains exact path and location of your data files reduck files and archive log files the control file information is
What for example in control file it is C drive Oracle da folder sap data 1 data 2 Data 3 data 4 okay r lock files information C drive Oracle folder da under Dave you’ll be having orog a orog b that information is written it will check compare whatever the information it is written
In your control file and it will check whether that files are present there at that location or not if all are present whatever the information it is written in your control file control file contains all this information right where your data files dat log files files are located it will compare
Whether this location and these files are valid or not once valid then it will open the database otherwise it it won’t open the database and remember a end user can log into the database which is in open State first it will read P file by reading P file it will start the
Instance after starting instance this P file only contains control file information control file location also not control file information control file location information it will go and read that control file from that three locations control file is having what the exact path and location of this these three physical files data files R
Actual your data files right actual data resarch in these files this location it will compare Okay C drive C drive C drive d d equation okay everything is fine then only it will open the database and end user can log into the database which is in open state only
Administrators can log in into the database which is not in open State what is that state we’ll discuss see if control F information and this physical files information has mismatched then your database is not going to open remember that can I show you that is it clear what is control file naming convention
CNL dbf this file contains this is a binary file this is a physical file physical file you can directly read but how can you read this binary file can you read the binary file no so what we do we convert this binary file into text format there is a procedure I’ll show you
That by that you can read that how much time left guys see I’ll show you how to start the database now see my application is running on database I have my application my application is running can I stop my database directly when my application is running no no in one apartment a few
People are residing leaving can you dismantle the pillars while they are in live in that no the same way that is the reason first of all you have to sh stop your application then only you should stop your database directly don’t start and stop your database while your
Application is running your database is a pillar on that pillar you are having your application right so I’m stopping my sap system first stop then I can play with my database I can start and stop the database here you are going to see some technical words like no mount mount open just
Observe that I’ll explain ACC stopped yes now I can play with my database how to log into the database just go to your command promt yeah so uh to talk with your SQL or to talk with your database you should log into SQL prompt first directly I cannot go right so SQL
Plus SQL plus it is a tool see what happens now see I got SQL prompt now connect as CIS DBA database administrator now I connected to my database now I can start and stop the database now I’ll let start what happens oh sorry startup not start startup cannot
Start already running Oracle shut down it first okay shut down I’m saying shut down what happens closed dismounted Oracle instance is shut down instance shutdown means it is releasing your RAM memory and it is stopping your processes instance starting means it is occupying memory plus it is starting the processes now I’ll say
See startup when I say startup what happens see now first first what happens it will read the P file and it will start the instance see your SGA system Global area is allocated the Oracle instance started that means your SJ is allocated background process started then it is came to mounted then
Open what is the meaning of instance started mounted open this area when it is starting the instance this is called as a no Mount phase no mount mount open no mount mount open at no Mount phas what happens three faces are here no mount mount open when
Shutting down see open means first it will close mounted means it dismounted instant started means instant shut down the reverse are you observing this two things instant started mounted open at the time of starting at the time of shutdown first closed then unmounted dismounted then instancy shutdown here we have three things no
Mount mount and open what is no mount mount and open interview question see first the database will be no Mount phase when it is in no Mount phase it will read the P5 and it will start the instance now it will read the control file when it is reading the control file
It will come to mount to face at Mount to face what it will do it will read all this data files information rck files information arock information if everything is okay then it will come to open state so at no Mount phase it will start the instance at Mount phas it will validate
Your control file and physical files if everything is okay it will come to open State and end users can log into the database when it is in open state but as administrator you can log into the database when it is no Mount phase when it is Mount to face you can start the
Database up to no Mount phase you can start the database up to mount phas we can start the database up to open State as administrator but end user only loging into the database when it is in open State remember that okay first it will be no Mount phas at no M
Face it will read the P file and start the instance instance means RAM memory plus SGA occupied plus your process is started then it will go to mount face at Mount face it will validate your control file and validate all these files if everything is okay whatever the
Information which is written in your control file and the exact location of these files is matched then only the database will come into open so no mount mount open when is shutting down close Dismount no Mount see once again I’ll start the database now it is open right now I say shut
Down first it will be closed dismounted and in finally the instance is closed closed dismounted instant shutdown means what the RAM memory is released this is the proper way of shut down if you directly power down uh switch off your laptop what happens your RAM memory will not be
Released if you say proper shutdown then what happens your RAM all the applications will be down and all the processes will be stopped and your RAM memory will be released that is what your instance shut down now I’ll say startup so first it will read the P file and it
Will start the instance it is no M phase now it is reading the control files coming to mount phas now it is validating your physical files if everything is okay it is coming to open state so you have to believe me or else we’ll do practically we’ll delete one
File from that okay first uh I’ll show you what we have in control file how it is validating your control file in your physical files okay so control file location is what sap data 1 urog A and urog B that is binary file you cannot read that if you want to
Read that there is a command called alter database command alter database backup control file to trace remember this alter database is the regular command that is a alter database means a b backup C control file a BC alter database backup control file to trace what is Trace there is one directory
Called sap Trace guys see in your or sap home you’ll be having one sap Trace directory so it is moving this file to this Trace directory as a text f file so if you run this command enter database is altered now go and check in your Trace directory
Or sap Trace under that do rdbms Dave Dave Trace under this folder you’ll be getting one Trace file what is time date modified what is time now 8:30 8 837 you got one file 83 I think this one the trace files open with see uh this one not this one I think no
37 see what is time now 8:39 now I’ll run one more time same Comm Al database backup control file to trace check 37 this one I Think where is the F6 839 got where is that this iser Lo on this one give what is that file name uh did you remember why is not generating I’ll show you tomorrow that control file see I’m say I’m saying that this control file information and physical file information matched then only your
Database will come into open State otherwise the database won’t come to open state right this is what my statement okay so what I do I’ll delete one file from this uh data files location here is my data files okay orle sorry sa data 1 from system1 I’m deleting this file cut uh
Is a use what it is saying and iut cut cut okay I’ll stop the data otherwise shut down first okay now yeah see I deleted that file now I’ll try to start what happens see start first it will read your P file P file no problem is there it will will
Uh start your instance it will go to mount phase at Mount phase it will compare the physical files and the control file information if something is wrong the database won’t come into open State startup instance will start no Mount now it will be mounted see it is mounted and finally
What it is saying cannot identify local data file One DB rated look at the DB Trace file what it is saying C Oracle Sid sap data 1 system1 system. data file this file is missing right system. data 1 how your control file has this information but when comes to physical location that
File is missing that is the reason the database only coming to no Mount then Mount no open state it needs a recovery you have to recover that then only it will come to open State now what I’ll do I’ll shut down one more time and I’ll copy that file to that location
Again what is that sap data 1 system one oh sorry system one system one right now see what happens now I’ll start up I’ll try to start the database now it will go to mount first uh no Mount means instance will start then it will go to mount then open mounted now
Open see if the control file information physical files information is not matched the database won’t come into open state is it fine with everyone Sir yeah any doubts from today’s session tomorrow we’ll discuss a backup concept backup restore recovery if you understand these Concepts well then only you will understand that backup any doubts from today’s session yes or no no sh no thank you
Video Keywords: Oracle Database
-
Sale!
Wireless WIFI Repeater Extender Amplifier Booster 300Mbps
$29.99$14.99 Add to cartWireless WIFI Repeater Extender Amplifier Booster 300Mbps
Categories: Electronics, Wi-Fi Router, Wireless Wi-Fi Extender Tags: 300Mbps, 802.11N, Amplifier, Booster, Extender, mobile wi-fi booster, Remote, WIFI, Wireless, Wireless WIFI, Wireless WIFI Repeater, Wireless WIFI Repeater Extender, Wireless WIFI Repeater Extender Amplifier, Wireless WIFI Repeater Extender Amplifier Booster, Wireless WIFI Repeater Extender Amplifier Booster 300Mbps$29.99$14.99 -
Sale!
Full RGB Light Design Gaming Headset Headphones with Mic
$24.99$14.99 Add to cartFull RGB Light Design Gaming Headset Headphones with Mic
Categories: Electronics, Gaming, Gaming Headsets Tags: Design, Full, Full RGB Light Design Gaming Headset, Full RGB Light Design Gaming Headset Headphones, Full RGB Light Design Gaming Headset Headphones with Mic, Gamer, Gaming, Gaming Headset Headphones, gaming headset wireless, Headphone, Headphones, Headset, Light, Mic, Package, RGB$24.99$14.99 -
Sale!
Wireless BlueTooth Multi-Device Keyboard Mouse Combo
$39.99$19.99 Add to cartWireless BlueTooth Multi-Device Keyboard Mouse Combo
Categories: Electronics, Gaming, Gaming Keyboards, Keyboard Mouse Combos Tags: Combo, Keyboard, keyboard mouse combos, Mouse, MultiDevice, Set, WireKeyboard Mouse Combo, Wireless, Wireless BlueTooth Keyboard Mouse Combo, Wireless BlueTooth Keyboard Mouse Combos, Wireless BlueTooth Multi-Device Keyboard Mouse Combo, Wireless BlueTooth Multi-Device Keyboard Mouse Combos$39.99$19.99 -
Sale!
High Back Leather Executive Adjustable Swivel Gaming Chair with Headrest and Lumbar
$199.99$139.99 Add to cartHigh Back Leather Executive Adjustable Swivel Gaming Chair with Headrest and Lumbar
Categories: Gaming, Gaming Chairs Tags: Adjustable, Chair, computer chairs, Desk, Executive, Gaming, Girl, Headrest, High, High Back Leather Executive Adjustable Swivel Gaming Chair, High Back Leather Executive Adjustable Swivel Gaming Chair with Headrest, High Back Leather Executive Adjustable Swivel Gaming Chair with Headrest and Lumbar, High Back Leather Executive Adjustable Swivel Gaming Chairs, Leather, Lumbar, Office, Racing, Swivel$199.99$139.99 -
Sale!
Professional LED Light Wired Gaming Headphones with Noise Cancelling Microphone
$29.99$19.99 Select optionsProfessional LED Light Wired Gaming Headphones with Noise Cancelling Microphone
SKU: N/A Categories: Electronics, Gaming, Gaming Headsets Tags: Cancelling, Gaming, Gaming Headphones with Noise Cancelling Microphone, gaming headset, Headphones, Headset, LED, Light, Mic, Microphone, Noise, Professional, Professional LED Light Wired Gaming Headphones, Professional LED Light Wired Gaming Headphones with Noise Cancelling Microphone, Wired, Wired Gaming Headphones, Wired Gaming Headphones with Noise Cancelling Microphone$29.99$19.99 -
Sale!
Gaming Desk with LED Lights USB Power Outlets and Charging Ports
$349.99$249.99 Select optionsGaming Desk with LED Lights USB Power Outlets and Charging Ports
SKU: N/A Categories: Computer Desk, Gaming, Gaming Desk Tags: and Charging Ports, Charging, Desk, Desks, Gaming, gaming desk with led lights, Gaming Desks with LED Lights, Home, LED, Lights, Monitor, Office, Outlets, Port, Power, Room, Stand, USB, USB Power Outlets, White, Workstation$349.99$249.99 -
Sale!
Wired Mixed Backlit Anti-Ghosting Gaming Keyboard
$99.99$79.99 Add to cartWired Mixed Backlit Anti-Ghosting Gaming Keyboard
Categories: Electronics, Gaming, Gaming Keyboards Tags: Antighosting, Backlit, Blue, brown, Gaming, Gaming Keyboard, gaming keyboards, gaming keyboards and mouse, Keyboard, Laptop, Switch, Wired, Wired Mixed Backlit Anti-Ghosting Gaming Keyboard, Wired Mixed Backlit Anti-Ghosting Gaming Keyboards, Wired Mixed Backlit Gaming Keyboard$99.99$79.99 -
Sale!
Wireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headset
$119.99$59.99 Add to cartWireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headset
Categories: Electronics, Gaming, Gaming Headsets Tags: 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headset, ANC, Audio, Bluetooth, Cancellation, Ear, Earphone, gaming headset, Headphones, Headset, Hi-Res Over the Ear Headphones Headset, HiRes, Noise, Wireless, Wireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Headphones, Wireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headset, Wireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headsets$119.99$59.99 -
Sale!
Wired Sports Gaming Headset Earbuds with Microphone
$19.99$9.99 Select optionsWired Sports Gaming Headset Earbuds with Microphone
SKU: N/A Categories: Gaming, Gaming Headsets Tags: Accessories, Earbud, Earphone, Earphones, Gaming, gaming headset with microphone, Headphones, Headset, IOS, Microphone, Sports, Wired, Wired Sports Gaming Headset Earbuds, Wired Sports Gaming Headset Earbuds with Microphone, Wired Sports Headset Earbuds$19.99$9.99 -
Sale!
150W Universal Multi USB Fast Charger 16 Port MAX Charging Station
$49.99$29.99 Add to cart150W Universal Multi USB Fast Charger 16 Port MAX Charging Station
Categories: Charging Stations, Electronics Tags: 150W, 150W Charging Station, 150W Universal Multi USB Charging Station, 150W Universal Multi USB Fast Charger 16 Port MAX Charging Station, 150W Universal Multi USB Fast Charger 16 Port MAX Charging Stations, 150W Universal Multi USB MAX Charging Station, 16 Port MAX Charging Station, 3.5A, Charger, Charging, Fast, laptop charging stations, Max, Multi, Port, Stand, Station, Universal, USB$49.99$29.99