Three SCAN IPs Resolving 8 Node RAC | #dailyDBA 10

one of the important questions that I have been asked so many times when whenever I see a lot of DJ's coming over TV Genesis website it is like what is the difference between forces and real time projects I'm back sorry I guess we missed couple of episodes I was busy and do you do that I couldn't record the previous two episodes I believe and now I'm back with another new episode of daily DBA so let us start with the first question of the day by mistake someone removed online redo logs and clean up job has clean archives and we do not have any database backup schedule at this hour what are the ways to recover database again now this question I would like to be very careful do you want to recover the database or you had a previous backup and once you restore from the previous backup you just want to open the database I'm curious to know so understand this see if you don't have archived locks if you don't have redo logs it's highly impossible to recover the database and sometimes I know interviewers ask this question like hey how will you recover the database in case you don't have redo logs or archive logs it's kind of like impossible how will you apply these transactions when you don't have the transactions and the straightforward answer is no you can't so on the flipside now some people ask me this question and I have faced this scenario myself so we had a database backup and we restored the database from the backup now as we restore the database from the backup as you all know database will not directly open because you need to have at least one half can log in order to open the database right now in this kind of situation will be contacted Oracle and Oracle also gave us solution you can forcefully open a database even when you do not have a single archive long after you perform the restore so that is something is possible but in your case as you mentioned all the redo logs are gone archive box are gone no database backup is scheduled what are the ways to recover the database again now there are multiple scenarios in case if you have a standby database then you can probably clone from the standby database and then recreate the database if you have a database export setup already so you use the database export to recreate the database and all those options are available but in case if you had a database backup and using this backup you try to restore the database and then you are trying to apply the Afghan locks which you are telling that you do not have those arcade locks you have lost those are key locks in those situations what will happen is there is still a way that you can forcefully open the database and yes database can be opened so any of the experienced dps over here who's listening to this episode let us all know in case you faced the same scenario when you try to restore the database and then you couldn't find any iPad locks inside the ardent backup so you had perforce will you open the database if that is something you have faced please let me know in the comments and I would definitely love to read your story now that being said let's move on to the next question what is the difference between checkpoint and SCM I will keep it simple but I would definitely want you to read Oracle documentation on this topic understand this SCN is system-wide change number checkpoint is a task level change number or completion number for example checkpoint okay let's take there is an aeroplane that's flying right so for an airplane to fly you have a PNR number right so that is kind of like s-see and consider it as an SCM so one PNR number defines from where to where the airplane is flying correct but a check point would be like exactly at the movement what's happening like checkpoint one would be like people boarded the plane checkpoint two would be plate is ready to take off checkpoint three would be like plane crossed so-and-so destination checkpoint four would be plane landed so understand PNR is like the Sen and inside Sen you have checkpoints which make sure that the task is completed to a certain level so that's the easiest way for me to define the difference between checkpoint and Sen so whenever a transaction runs inside the database the checkpoint defines the level still where the transaction is completed but an SDN is overall system-wide so whenever there is a major change inside the database the entire system-wide Sen will change right so definitely Sen will run in a different number cities and checkpoint will run in a different number series I guess this is the simplest way to explain you the difference between checkpoint and Sen I would still recommend you all to read more in detail about checkpoint and Sen but keep this airplane example in mind and I think that will help you understand the difference all right that being said let's move on what is the difference between local and dictionary tablespace basically the exact term is locally managed tablespace and dictionary managed table spaces assume this guy's inside your tablespace the metadata information above the tablespace there are two ways to store that information one way is either you store that information with the table space or you store the metadata about the table space into dictionary tables that is the page tables so what is the benefit and drawback let us try to understand see if you run a query and the query needs to know what your table space contains and if you have locally managed table space so query can get inside the table space it can read the dictionary details about the table space within the table space that is actually your data file headers so it can read the details about the blocks which are inside the data files within the table space that means the data files which are part of the table space and it will can be data but understand if you are going with the dictionary manage table space you are adding one more heading to your query so your query comes in its rights to read the data file header but the data file header is not having any dictionary information about the table space so now it needs to go to the dictionary tables that means the page tables to get the information and then come back to the write data file so technically if you look at and if you understand the entire concept it is very easy for a query to run when your table spaces are locally managed and it is really hard when your table spaces are dictionary managed all the Paris who needs to go to the base tables needs to get the details about the table spaces the internals of the table spaces they get those details and then the query executes by going to the respective data files inside the tablespace so I guess that's the basic and main difference between locally managed table spaces and dictionary managed table spaces now that being said you know which one is better I think you all know now so you should go ahead and put comments which one would you recommend to one new DBA would you recommend to go for a locally managed tablespace or would you recommend to go for a dictionary managed table space that being said let's move on I want to clone database from source to target but there is no connectivity between servers how will flown happen without connectivity and could you please share high level steps alright I think this is one of the simplest problems and even I have phased these kind of problems but don't worry I am NOT going to give you direct answers to this question rather I want to challenge all the experience DBAs who are listening to this episode I want you to go ahead and put down your comments and tell us or actually help our friend as to how he can perform the database cloning when there is no connectivity from the source and the target server I think it will be fun to see answers from other experienced dps but definitely I would also be answering this question probably in two three days once I see other experience leave a comment and make sure to review back this question answer later on probably in two days and you will get the answer I mean I'll put my answer over there but now I want to keep it as a challenge to all the experience dps and so how would you perform the cloning in such kind of situation when there is no connectivity between the source and the target server I think smart question I love this question because I think in my studying of my career with Oracle database even I face the same thing there I was trying to clone a database one was on one virtual machine and the other who VM I think there was some problem with the network and each VM was not able to talk to each other so what I had to do is I have to perform the cloning I know you guys are expecting me to say the answer but I'm smart enough don't worry so what I want all the experience dps is put on your comments below this video as to how would you perform the cloning well there is no connectivity between these over it's over I'll put my answer exactly after two days and I'll make sure my answer remains on the top of the comments so that in future somebody's watching the video they'll get my answer that being said let's move on can you explain how three scan IDs resolved eight node rack in Oracle because we have eight nodes in the cluster and only three scan IDs how is it possible to resolve these eight nodes amazing I think after a long time I have seen a question related to scan IDs see you need to understand the entire funda of Oracle RAC networking first before I give the exact answer to the point so let us get back to scan IDs and try to understand what our scan IDs so what happens is let's keep it dead simple okay in a normal connection if your server IP address you give the server IP address right away what will happen the server IP address will become like I mean if you change the server or somebody's done what will happen I mean the connection will be lost right so what happens is in rack rack has introduced something called as having multiple listeners running on three scan IPs so what happens is these are known as scan listeners which are running on three scan IDs and these IDs are defined into a DNS server okay I'm not coming on to the rack part I am right now into the DNS server so what happens is whenever a user connection happens to the DNS server out of those three IP addresses any one will be resolved okay the beauty is let's say we have three IP addresses so what happens is first user will come the dns will give one IP to the user and then this next user is coming to the rack setup next IP address is given to the user the another connection is coming next IP address is given to the user and it continues like that so it is like a round robin fashion now what is happening when one I get the result so on each IV each scan ID scanned listener is running okay so what will happen is the primary connection is accepted by scan listener but eventually no response is given to the user okay recap for everyone so we have DNS our DNS server has three IP addresses on these three IP addresses three scan listeners are running so every time a new connection happens new scan ID is given so that a new scan listener will be assigned to the particular user so three IP addresses three scan listeners now looking at the question understand this it is not like three scan IDs will be mapped to all the ignored local listeners okay so let me talk about local listeners what happens is on each your local node definitely you will have a local listener running okay that is called as local listener and the earlier was can listeners so each local listener has a capable D of I mean we can put it this way so scan listener is smart enough to know load on each node and how does it know it can very well talk to the listeners well which is running on the local rack nodes from 1 to 8 nodes or 10 nodes how many nodes you have so scandalous nurse are smart enough to speak with the local node listeners and it can get to know what is the load on each node so let's take in your and back node 1 to 8 node it knows that the fifth node in the entire rat cluster is having less number of connections so scan will transfer or move the user connection to the fifth node local listener alright so if you are trying to put your question saying like how would scan listener resolve 8 node IDs know X from the question is wrong you need to put it in in this way so what happens is this nurse knows all the local listeners in your environment and it also knows that which node is the least loaded load now based on that based on the least loaded node in your environment this canopy will transfer the connection to the local norm all right and then local listener will transfer the connection to the local node database that is how the connection happens so looking back at your question can you explain how three scan is resolved eight node drag in Oracle so what happens in rack is can I please have scan listeners running and each scan listener knows the load on each node so what happens is by default each scan listener will pass on your user connection to the least loaded node and that's how it works I hope that helps you so now whether you have eight node 10 node 20 loads Lac it doesn't make difference because your scan Willis now knows which node is having less number of connections and automatically the connection is given to that particular node all right let's move on to the next question oracle DB has OMF someone deleted all the control files operating system or to Linux Evan Database 12c and os already we started our catalog mode is not enabled without using the Armand how to recover the database I can very well said that somebody asks you this question in one of your database interviews all right because I know these kind of questions are generally asked to confuse you so I assume this so you have a database which has OMF ok Oracle managed files so let us look at the criticality of this sentence like Oracle DB has OMF does it add some value to your answer or add some weight edge to your question not really whether F is it's a separate task so let's keep it aside someone deleted all the control files okay so when the database is running control files are deleted definitely it will have impact on the database right now what the interviewer is saying I can go mode is not an apron okay accepted without using our men how to recover the database so the interviewer is also saying that you cannot use the Armen because you might have some admin battles and you can restore your control file into your system so that way and an interviewer is trying to make your scenario even tough so I will give you the simplest example what you can do is let's take you shut down the database see if control files are not there definitely database won't work so what you have to do is eventually shut down the database now database recovery is one of these simplest thing in the world just in case if you understand the three stages that database goes through all right so the stage one is the no move mode Stage two is mode and stage three is open mode right so for no more mode Oracle needs s FIFA for FIFA and in this scenario we see that there is no SV final loss so definitely once you shut down the database you can put the database in no more mode right the interviewer next says that all the control files are deleted that means from no more money you cannot move correct because you don't have control files now this is where what you can do is even if you do not have a control file backup it's very simple you go to google this type create control file statement and you can get the create can go file statement it's an SQL statement get that statement change the locations of the database redo log files the data files as per your database you have to put the correct locations even if you miss one data file the control file will not be created or it will throw an error so recreate the control file using the create control file statement once we can go faster put the database in mode and then you can definitely open the database because you already have all the data files there is no data file loss correct so that means you can directly open the database but we'll open reset locks option because whenever you recreate the control files inside the Oracle database you need to issue the reset locks option while opening the database amazing guys these worldly top six questions I had for today and I look forward for all of you to send me more questions whether it is on YouTube by commenting below this video or sending your email to support that female genitals calm and I highly recommend all the fresh atps who are watching these episodes I want you to go to ebay Genesis calm that's our main website click on courses and all the courses that we have a lot of three courses out there and I want you to perform or enroll into those three courses no charges it's absolutely free go ahead and complete those courses so that you enhance your knowledge for all the experience degrees we also have fun free course 115 and in the potty SSH or the ultimate Topeka that's the exact name of the course I want you all to actually complete that course because most of you do not even know the power of put PD - and it's an amazing tool and there are hundreds of options inside 40 tool so I want all of you to actually go ahead and register for the ultimate cooking guide course and try to take the benefit from the course apart from that if you have anything else please to commence course and your emails in support at BB Genesis calm and now let's move on the most exciting part that is the bonus question hey guys I'm back so one of the important questions that I've been asked so many times when whenever I see a lot of babies coming to our DB Genesis website it is like what is the difference between courses and real time projects understand this guy's courses is more or less like the formal kind of like training which is like a tradition from Ages to use slides to explain you something in order to achieve something and also include some practicals now that is something which is like a standard format of teaching or training someone but what happened is I personally felt this like whenever I used to learn things I believe like you learn more when you try to describe end to corrupt something you tend to fail at something you will tend to keep those learnings for a longer period of time so based on that what I thought was like rather than having courses of course we do have courses on Oracle database why not we can have something called as projects now what are these projects I think this is the right time and right platform for me to explain all of you what are the real-time projects so projects is completely different way of learning things like I mean I believe on this so what I personally believe is let's take you want to learn something it's better that I put you into or I throw you into waters and you try to swim and come out of the orders I think that's the best way of learning so these real-time projects will force you to come out of their comfort zone the projects have activities and each activity has series of tasks that you need to perform of course in each of the projects there are lot of loopholes and there are lot of variables that you need to follow it's just not a smooth cakewalk like just completing a course and getting your certificate of completion actually we have harder or try to made it more tough regarding the real time projects and how did we do it when someone joins the real time projects you actually will be forced to report to a project manager it's one in the same like you can call it like having an experience of working as a DBA would be be built in it says because at deeper Genesis we have so many clients so my idea was can we do something like if somebody wants to work as a DPM so they work they report to a project manager and the project manager assigns projects to the DBA and then this DBA is kind of like performing those activities on first on test servers and then the project manager will actually pick up the DBA and ask to perform an activity for a real time client now that's the entire game changer that we have seen because I can tell you this on my personal experience when I have worked for the initial students or the first pilot batch of these students who wanted to who enrolled into the real time project right so these first pilot batch of students were the ones who have seen like 200 percent growth in their understanding about the Oracle database because you might be thrown into any of the projects like perform a migration from physical to cloud or probably perform a zero downtime migration using Golden Gate or probably perform migration from non ASM to DSM database right so you can technically get into any kind of project we don't guarantee or even your project manager cannot guarantee because as and when we get real-time clients all the DBS who are into the real-time projects they will be thrown onto the project now that's the only difference I think the most important part about the real-time project is that you hit the keyboard every single day so it's all about executions it's not about slides it is not about theory stuff it's not about somebody telling you what is right or wrong it's about you figuring out answers to the problems that you have been assigned to right so that's the basic difference between courses and real time projects I think for most of the DBS who are looking at enhancing your confidence in case you have less confidence in dealing with the Oracle databases in case if the databases are in terabytes then I would recommend you stop going for courses I think real time projects is something good for all of you apart from that guys continue to send me your questions to support at ebates.com and as I always mention go to support our DBA Genesis combat the knowledge base where I post all my scripts articles and in case if you have anything else write to me support that division is calm till then take care bye bye I'll meet you in the next episode [Music]

Channel: DBA Genesis

Views: 6,705

Rating: 4.8279572 out of 5

Keywords: oracle database backup and recovery, oracle scn, oracle checkpoint, oracle tablespace, oracle database clone, oracle rac, oracle recover database, scan ip address, rac nodes

Id: VwGaT08nSH4

Length: 26min 8sec (1568 seconds)

Published: Tue Jan 14 2020