Oracle Database 19c REFRESH using RMAN and DATA PUMP

hello and welcome once again to my channel where i try to provide oracle um education for your personal and professional development and um if you like uh what you see on this channel please go ahead and subscribe um do not forget to like the videos um enable notifications as well so that you stay informed when i release new content in one of my previous videos um i think one of the more recent videos um i demonstrated how uh we could actively uh duplicate um a database uh using ironman and um it is important for us to kind of differentiate uh what we mean by duplication uh which also means cloning and uh refresh cloning is simply just creating an identical copy of an existing database usually in the case uh in in our case we are we are cloning a production database uh to create a test database uh and typically that is done for the purposes of testing activities uh maybe such as upgrades patching backup recovery strategies or if you are creating a test environment it is usually also as a result of a request from the application teams uh so that they can have environment an environment a lower environment where they could also perform uh testing and development uh the difference between duplication and refresh uh a refresh typically is when uh there is an existing environment and the data within that environment is considered still it's considered old uh so there is the need uh for uh you as a dba to kind of synchronize uh that data within that environment with a copy of data from production and this is typically done by using uh backups now if you have um other advanced technologies like oracle golden gate that will replicate in real time um then there is no need for a refresh uh but traditionally when we talk about refresh we're talking about either refreshing a low environment a test uh sit database uh with a copy from production or in the event that you are refreshing only particular schemas then you could also use oracle's data pump utility uh to export uh from production and then import into um the test environment now if we have enough time in this demo um we would be able to perform both of them now in my scenario here uh in if you watched my last video you probably are familiar with this diagram where i created a duplicate of a prim db from our prod server a primary server to a test server and called it prim tst um so what i'm trying to do today is um that exercise was done about two weeks ago uh or more and so information on print test is still because i think i've added a few schemas to the print db and i would like just to replicate over uh those changes of course i want to copy down those changes to uh print test so let's go ahead and get started with this demo um my print serv server um i'm going to create a connection here let me make this a little bit bigger here for us to see and i would also create another connection to my test server on my prim server i would look at the contents of my orat file i have three databases but what interests me in here is my prim db so i would set my aura env to my prim zb and uh let's go in there and see what the status of that uh select instance name status from the dollar instance all right so it's in an open state let's do the same for a test server so let's cut the contents of our etsy or a tab and we have our print test so um prim tst sequels so select instance status from the dollar instance all right prime test is also open so uh what i intend to do um in this scenario uh would be first of all because we are trying to refresh uh from backups let's first of all try to see what changes uh we are trying to account for so um and for that i would go over to sql developer which is a much more user-friendly tool um in one of my other videos of course i demonstrated how to create a basic connection um to the database using sql developer so i would add the link to that as a related video for for this one if you want to learn how to do that all right so let's go ahead and and look at database users so um for our prim db and just to make sure that we are sitting on the right um on the right database i will run this same command on this interface just to make sure that we are sitting on the right database so um let's run that and fair enough it's prim db and let's try to see um for our test db uh prim test so there's a spring test so fair enough we're sitting on the right instance let's take a look at the number of uh dba users currently i have 39 here and uh for uh a prim test i have 36 so and the reason like i mentioned earlier was because i created three users i love looking at sports i like sports all kinds of sports and um i created three users nba analyst nfl analyst mlb analyst and for those three users i created some tables just some summary tables with general information you know about um nba teams nfl teams and mlb teams now um each of those tables um for the nba teams we have 30 um for the nfl teams we have 32 and for the mlb teams we have 30. now um we don't have those users on this site so um if i do an oracle maintain equals no um we don't have any of those on the side so um so at the end of our refresh uh what i seek to achieve would be that these users uh would now be present in the test environment where we can kind of start playing around uh doing all kinds of analysis on those tables all right so let's get started here so um again like i mentioned we would need backups right um and we do need backups of a production environment so we'll go ahead and take backups of our production environment and we would copy those backups to the test server so let's go ahead exit out of a sql plus and log into irman connecting to target on the primary database and we are connected to target so first of all let's see let's list what backups we have here this backup summary let's see what backups we have here so okay so uh we don't have any recent backups in there which is okay um so we'll take backups uh we'll take it back up now i don't want to point the back up to the normal flash recovery area because if you take a look at the flash recovery area um let's go in in here and then i do show parameter recovery my flash recovery area is this location home oracle backup so if i exit out of this and i go to home oracle backup and ls minus l um i do have my prim gb folder here so i'm gonna cd into that and what i usually see here is that the backups are segmented into archive lock backups auto backups and these have a control file auto backups and and then the backup sets um i don't want them to be in this format uh because it's going to be a little bit difficult for me to move them over so i would run an ironman and point them to a location that i would i have created or let me see if i have created those already so um i usually have a stage location use 01 stage and i don't have anything in this stage location so um in my ironman command um i am pointing this databases to u01 stage prim zb i would create a folder in here called prim db so let's go ahead i'll copy this one here and let's make sure that i would make the minus p prime db so i now have a print db there so let's see it into print db of course there's going to be no backups in there and and i would want to use that same location that same directory structure when i get into the test database so i would make that directory as well make the minus p u 0 1 stage prime d so let's cd into that of course there's not going to be anything there since we just created it so all right so now we have our locations created and then of course i would go into ironman target and i would copy um this backup command so i'm backing up the database i'm backing up archive logs i'm backing up the current control file and i'm backing up the sp files all right i'm going to copy all and let's go ahead and back up it shouldn't take too long it is a smaller database it shouldn't take too long all right so um ironman is complete um let me list backup summary and here we go um let's see i'm just gonna do a list backup so if i can see a little bit more details um so yes um user one stage prim db our database backup contains our data files and we have four of them and we can also double check that as well so if i go here and i select uh this is my prim db um if i run a select name from the dollar data file i should have an identical amount of files so i have four files uh which are all the same um that are within my database backup and this is the archive logs um let's look at the sequence um we have up to 33 and i can also take a look here um i have a command here already let's run uh the sequence number uh from the v dollar archive blocks and let's take a look at that and if i double click here just to sort it i have archive logs up to this point all right so um let me go ahead then um [Music] and exit out of ironman and uh where am i uh user one stage print db ls minus l just to double check if actually those backups are in this location so all the backups happen to be in this location which is a really really good thing all right um so i'm going to copy this into the test server i'm just going to use an scp command since i'm currently in that location i'm just going to use a star uh which copies everything within this location and i'm connecting to oracle add test serve and i'm going to put that in the u01 uh stage prim db as well so let's do that it's the first time it's connecting i say yes um so i would go ahead and do that so um if you look on the side of the test server side we can start seeing those files coming so we have three of them already i think that the database backup is typically what takes a little bit more time so all right so let's do an ls minus l here um actually let's go on ls minus lh oh let's do an ls i didn't do that right ls minus lh just make sure that we are looking at the file sizes and double checking that they are the same size that came from from here so uh database backup is 1.4 gig 1.4 gig sp file is 112k 112k so control file 11 and archive logs 24 so 11 and 24 so um we're just checking the integrity of the files that came over so that being said um we have taken our backups we've moved them over to the test server um now most of the work we are going to be doing would be at the level of the test server so let's go ahead and do that um if we want to take a look at our prim db so our current prim db uh let's go ahead and cd into [Music] because we have an environmental variable set already we can just use uh the dollar oracle all right cool let me move this here so we can see home dbs folder ls minus l um so we have an sp file we have an init parameter file that we had created that has only the database name so we are going to need this file if you don't have this file then you have to create one um the init the initialization the text version of the initialization parameter file um is init oracle sid in our case is initprimtst.ora okay so um let's go into our database and uh we would shut it down because i need to look this this database um i would drop this database that's what i mean so um it doesn't matter if i you know shut it down a board or immediate as long as the database goes down i would start it in the mount exclusive restrict mode and drop it all right it's shutting down let's see all right our database instance is down all right so let's um exit let's start up mount exclusive restrict because if we don't study it in exclusive restrict we're not going to be able to drop that database all right so it's mounted so all i need to do is issue a drop database you have to be you know be careful each time you issue a drop database you want to make sure really that's what you want to do it goes a lot faster than trying to drop it using gbca so let's exit out of this i would look at my parameter files out here and you realize that that sp file is gone um so all that is left for you is the initialization parameter file so again we view the contents all we need in that initialization parameter file is a dbna so um we are we are going to be able to start a new um database in a no mount state so let's um assuming that you know you are within an environment where you don't have the environmental variable set uh let's go ahead and do that um let's yeah so let's take a look here and see all right so cad etsy or a tab and if you don't have the right app that's fine i mean i'm sure as dbs you know what your oracle will be so let's export oracle home equals this export or a cool sid equals prime test test export or our export path equals um a dollar oracle home a dollar oracle home bin location and then to that we append to the path variable all right um that way we can see core plus as csdba we are connected to an idle instance and uh we want to start up so before we do that um let me exit out of this and show you guys where i am so pwd ls minus l so i am in the oracle home dbs folder and within the oracle room dbs folder we have our initialization parameter files so um i would sql plus as 6dba and i would start up no mount now if i do a startup no mount this is the only initialization parameter file i have it will stack but just as good practice i would point it to that p file p file um in its prim tst so i'm going to point it to use that p file in it print tst to start up in normal okay now that we have a running instance let's double check that select instance names that is from v dollar instance so we have a print tst instance that is started so um let's exit out of this um remember the tns names i mentioned earlier needed to have entries to both databases so um i would run a tns ping to prim tst just to make sure i'm able to resolve that connection it resolves it in zero milliseconds i want a tns ping as well to prim db i did the first one on prim tst prim db if i'm able to resolve that as well so this is important because our man we are connecting to prim db and print tst so iron man let's connect target sees password oracle at frame zb so that would be our target database and to that we are going to add an auxiliary sys password oracle add prem tst okay good so so far everything is looking good um from the query we run to see uh archive logs uh we have archive logs um back up up till about 18 23 which is uh 6 23 so um i can restore or refresh this database up to any point before that but just to be you know um to be on the safe side uh why don't i go with you know uh you know 18 18 0 0 or 18 10 or 18 15. you know usually when you're getting the request to refresh a database um the application folks are going to tell you you know to what point they want that to happen um so there is an iron man run block um i'm duplicating that database um and i'm using a parameter value convert um if you watch the video i did on active database duplication i explained a little bit more and uh for the refresh of course we need to specify because we're using backups we're duplicating using backups we need to specify where those backups are so um i would set another time depending on what i find here again like i said i could set it to 6 i could set it to 6 15. i could set it to 620 anytime before um the last of the archives so all right let's go um let's go 6 15 so let's do 18 maybe 15 let's do 18 15. so it's kind of like a point in time recovery that we are trying to do so let's go ahead and run this and if everything goes well uh database refresh should be on the way that's everything hopefully because this usually is a tricky part we're making good progress here right i think we're about to open our database and reset logs we should be at the end of our r man so voila so it looks like everything did go well let's take a look at our database here to see so let's exit out of our mind and ls minus l there would have been a new sp file that was created sp file um let's go back into our database our print tst select instance name status from the dollar instance all right so there you have it um we have a prime test um select name from v dollar instance no um select name from video database um select a name from the dollar data file and so we have our print tst data files now let's go to our validation right um remember i said before um the beginning of refresh our objective was to make sure that we had these users uh the nba guys over on this side so uh let's go ahead and take a look at this and we want to select the accounts from the teams just to make sure from the tables just to make sure okay first of all we're gonna select oracle maintained and if everything looks good we should see three yay so we see three so mlb and on this sprint test just to make sure we are on the right instant supreme tst and if we look at uh the count of our nba teams we have 30 and we look at the count of our nfl teams we have 32 and we look at the count of our mlb analyst at the mlb teams we have 30. all right so this is the first part so this is a quick way now depending again like i said different environments of automated refreshes in so many different ways um depending on what it is in your environment uh it could be maybe as straightforward as a method like this or it could be heavily scripted um and there are different tools and utilities you can use for refreshes so uh the joy about it is um there are multiple ways for you to be able to do that now uh this is the first part of the video now the second part of the video um let's try a few things and then just make sure that i show you also uh what we want to do uh when it comes to maybe uh um schema refreshes so from our primary database our prim db we're gonna make some changes to um these different you know um um tables because those are some of the schema so these are like this is what we are we are transitioning into what we call a schema based refresh all right so let's take a look here i might have um you know i'm going to create a new table i'm going to create a new table and uh so let's this is our exit uh no let's not exit because we're gonna exit out of the terminal so uh let's go into our sql plus assist dba let's connect the nfl analyst and not list i cannot type um the password is oracle and uh let's select table name from user underscore tables let's see so we have only one table here i don't mess around with this table so i would create another table um and again just to just to make it a little fun and interesting um uh let's see what's in uh let's not select count star here let's just select star right um let me copy this let's not select count star let's actually select star from that table so we see what we have there so basically uh this is just kind of um um a listing of you know all the 32 football teams uh each one of them has an id um 1 to 32 the abbreviation the conference in which they play and the division in which they play so um the cardinals of course are arizona uh they are part of the nfc conference and they are in the west division so i just want to make some just a little change so let's talk super bowl for a little bit and again i don't we don't have the time to go in to update this table with when each of these teams uh won the most recent super bowl and i live in minnesota minnesota viking and it hurts uh when i talk about super bowl uh victories but hey this is a learning you know this is a learning video so let's go ahead and have fun all right uh i'm going to create a table called super bowl um from um our a table called super bowl from our nfl nfl teams so let's do that i'm going to create a table called super bowl from our nfl teams let's go back in here because i own this table um and if i show you here why not i own this table so um if i if i say a show user uh the nfl analyst is the one that owns that tip so if i create a new table super bowl as select star from nfl teams i'm basically just recreating that nfl team's table and giving it a different name and i would want to mess you know a few things around in that table so um so if i select uh before i select uh let's set pages to a hundred and then select start from super fall all right so um this is how my table looks like right now um i want to add a column uh to this table uh where i can maybe you know put uh at least for the first for the last five years uh just as a demo uh to put who the winners were uh for the last five years um even though new england has won twice so i'm gonna skip that one so that it doesn't mess up our um our what we're doing here so all the tables super bowl i'm going to add a column called super bowl champs and so if i describe super bowl um what i have now is i have an additional table which is different from of course the table that we created from so if i describe nfl teams um i should have one column less so i added super bowl champs now the reason i added this was because i wanted to run just a few queries and update you know uh uh some and add some entries to the new row uh to the new column i created so um i'm gonna run some updates um where 2022 the most recent is the rams last year was tampa bay box 2020 kansas city of course 2019 new england uh 18 was philadelphia 17 actually was new england i think if i'm not wrong that's why i had to skip it and go to the denver broncos uh who won in 2016. so let's see how this works let's update those okay so let's select start from super bowl um i added super bowl charms but it doesn't look like it doesn't look like that works so let's go ahead and do that one row updated okay it's gonna need me to do this probably because i added the comments at the end now i'm gonna do that sorry this is a little painful but okay and then one last one um this shouldn't take too long okay good so if we run a select star from super bowl all right so at least we have entries in our you know sp championships bubble champs right okay so assuming that we've made these changes in prod and hey the devs the guy the dev guy says hey listen you know our data is still uh could you refresh just you know a few schemas and maybe they requested particularly for us to refresh this you know nfl schema um we could also make some changes to you know um the other you know um um users as well so let's exit let's go sql plus nba themes no no no nba analyst um oracle okay good show user in the analyst select use a table name from um user underscore tables let's just do it let's just do something so let's select uh let's create a new table create table nba champs from select um sorry ass select star from nba teams all right all right so uh let's select 10 uh the nba champs select star sorry i'm trying to rush it and uh nba champs okay so um so what we can do here is um let's just run um um a truncate let's truncate nba champs oh trunk it sorry trunk table nba champs truncate table and be a chimes okay good so if we want to select star from nba jams we should not see anything in there all right good so and of course if we select a table name from um user underscore tables we should see two tables belonging to nba analysts so nba champs and nba teams so this as minute as these changes might be uh let's see how we can do a schema refresh okay um when you're performing a data pump there are a few things that you need to to know um oracle creates a dom file um in in in in while exporting and and the location to which that dump file is placed depends on what we call uh um an oracle directory right a directory object so uh and we can see what the existing directory objects are if we run um a select star from dba directories um in one of my videos of the one i did on an upgrade from 11 g to 19 using data pump uh if you look go back to my videos you would see explicitly how we're going to detail about creating dba uh creating an oracle directory and things like that for this exercise we're just going to use an existing directory and there is a default uh the data pump there so what we are going to do um the location of the data pump there um we are going to go to this location i'll copy that location um so i would cd into that location uh u01 app admin prim db dp dump and i don't think there is okay there's a dp log in there uh but i would create what we call a power file a power file just simply uh or maybe i'll just keep it simple right let's run an export expdp i will run the exporter system and um for my directory i would give that directory object uh that default name so the dp from data prompter i'll give it that data pump there and i want to move schemas the following schemas um i just want to get the name here so um nfl uh let's start with nba mba analyst hopefully i i'm spelling them right i and i would hate to not spell them right um nba analyst exactly i was telling them right so i was doing mba rather than nba okay let's do this let's do this let's make it a little bit quicker for me or i can maintain here i should get those names here i would copy them i would bring them over here open up a new notepad paste them in here and and then i go to the end i bring that in so that way i know i am not messing any names up because all it takes is a mistake and you are not going to be very happy don't file equals let's do schemas um uh let's do prim zb schemas dot dmp and for the log file let's do uh prim db schemas dot log and if everything looks good so we have a directory object uh we have a directory data pump there we have our schemas we have a dom file and we have a log file and then oracle will be the password for system and if all goes well again these are small schemas it shouldn't take time once this is done and then i would do the same and perform an import on the other side all right absolutely fantastic now let's take a look at this so um it copied 32 rows zero rows of course thirty rows that zero stats two rows okay and let's ls minus l right here and then we have this so again in a similar way i would go over to the print test database and i would select um star from uh gba directories let's do that let's select start from gba directories here and dba directories and then i also do have data pumper in the same um the same location that's interesting so let's take a look at that let's take a look at that location here and see on our test server let's exit out of this cd that let's do cd into that i was kind of surprised i saw gp uh the prim it has to be print tst not prim db i was so let's do that and then i would create that folder in here so make the dp dump okay so uh cd dp dump ll so pwd i will have to recreate this over here so um let's go back on this side and i'll just kind of fix this so create or replace directory as uh directory uh deep data pump there as and then i pointed to that new location so let's do that so create or replace directory data pump dir and then let's create now replace that so okay good so data pump the directory created let's take a look at that now we have it now we have it in a good place that's good so i like i prefer i prefer that so let's move again using our scp commands um let's move this file over ls minus l let's move the dom file over so scp prime gb dot dump let's move that down file over oracle add test serve to that location and quick it's gone so on a test server ls minus l yay so that's good all right so since we have it in there um let's use an impdp same thing system uh directory i can just copy some of this here since most of it is going to be the same schemas dom file that so up to this point is going to be the same up to this point is going to be the same log file equals um and i could do uh that log i can just add imp just to make it a little different log and then i want to add a table exists action so when i add a table exist action it simply just instructs the input that if there is a table that is existing within the schemas what should i do and you can take one of four different variables right it can actually skip and if we use keep of course then we are defining the purpose while we are performing a schema refresh what we want in the schema refresh is to actually replace the data to replace the table now in certain situations you might be wanting to add uh data to the table so you you would append or in certain cases you would truncate but in our case we want to replace so if that table is existing uh please replace so let's go ahead and do that oracle and then let's see what happens to our import so of course objects are already existing and we instructed it to replace remember that in a test database we didn't have the new table you know nba champs we didn't have the new table uh the the the super bowl we didn't have these two tables so and of course there are three errors and we know why because we saw the errors up here now you have to be familiar with data pump and errors uh data pump uh is not short of errors uh more often than not you will get errors with your data pump import and the key is to understand which errors to take cons seriously and which errors to ignore all right so now in this case uh let's go ahead and take a look at what we need to take a look at here so um if we go into our database and then we connect as the nfl analyst oracle show user and then we select user table name from user underscore tables we now see that um rather than one table that it had it now has the nfl teams and the super voltage and we can also connect um the nba user analyst user and run the same command to see if that effectively has also added the nba champs table and um as the nfl user uh let's just double check to see uh nfl analyst let's just connect again and see if running a select uh let's of course set pages set pages 100 and then select star from super bowl and then let's make sure that hey we have the data that we made some changes in our prim db that are now reflected in the test database so this is an example of a schema refresh so uh just to summarize um in the video we planned on doing two things refreshing the database using ironman using backups from production or our primary database and we we also plan on demonstrating how we can use data pump uh to perform schema refreshes i hope this information has been this content has been informative for you and uh if you do like the videos please go ahead um you know subscribe to our channel like our videos and you know enable notifications so that as we release new content you stay informed um thank you very much for watching and i hope to see you in the next video

Channel: Ni TSOH

Views: 6,569

Keywords: Oracle Database 19c REFRESH using RMAN and DATA PUMP, Full ORACLE Database Refresh, Data Pump, Oracle Schema Refresh, ACTIVE Database DUPLICATION using RMAN, Oracle Recovery Manager, Recovery Manager RMAN, RMAN, Duplicate Database, Clone Database, Oracle Database Active Duplicate, NI TSOH, Refresh Database, oracle database refresh, rman refresh, oracle database 19c refresh using rman and data pump, full oracle database refresh, oracle, Oracle, ni tsoh, atsohmofor, bobsukki, Atsoh

Id: NdwurXJAkZ0

Length: 49min 27sec (2967 seconds)

Published: Sat Feb 26 2022