Oracle 11g to 12c step by step database upgrade | 11204 to 12101 manual upgrade process

so in this activity will be understanding how to upgrade your database from eleven to over ten to twelve 101 version all the prerequisites the overall steps of an upgrade remains the same and when there is a version change like from 10g to 11g or 11g to 12c definitely it's an out-of-home upgrade which means you need to perform the 12c installation in a separate Oracle home and migrate all your SP file your TNS entries from your old Oracle home to your new Oracle home but before we can start this upgrade we need to go through all these steps once again these steps are pretty similar to our previous upgrade but it just we will have change in these versions so let us start with the pre upgrade steps nothing but let us prepare our database for upgrade the first thing we should have is take a DB backup via our men this is important because in case if something happens to the database we must be able to recover from this backup so what we'll do is first of all we'll check our database status so it's not running let us start up right so deep select name comma open underscore mode from $3 database it's fraud DB our databases up and running let us exit and connect to our man if you look at the Armen utility in our previous sessions we have upgraded the Oracle database from 11 to 1 to 11 204 even this admin utility will start from 11 204 Oracle home the list backup of database summary and we can see that we have our database backups and these looks to me pretty old backups that's why it is always recommended to trigger a backup before upgrade and after upgrade now the backup which we are going to trigger this will work for both after upgrade to 11 204 backup and before upgrade to 12 101 backup so let us take the backup but before triggering the backup we have to check the disk space show all all our backups are going into u0 to backup location let us check the disk space under u0 - we have only 2 gb left what we'll do is we have good space in u0 3 so let us move the backups to u0 3 I mean we'll be changing the channel parameter and then will trigger the database backup Arman target slash but before that we'll create a directory slash u 0-3 backup now our main target slash configure channel device type disk format now in this location we'll just give the usual 3 location and rest all we'll keep it as same great show all now we have this default channel which will take the backups under u 0 3 now backup database now once the DV backup is done you must take the backup of your Oracle home or our inventory directories and this can be done via os-level copy but because we are trying to do an out of home upgrade we already have all these files in one location which we are not going to clean up we are understanding that after the upgrade we will wait for one month so that application team can come up with some errors and they might ask us to revert back to 11 2:04 version in that case we are keeping these locations we are not removing them and we plan to remove them after 30 days as of now it there's no need to have a copy but in your environment if you get a chance it is always good to take a noise level copy of your old Oracle home and/or our inventory directory and we know that we need to run these three scripts which will gather the statistics of the database the dictionary tables and the fixed objects find our database backup is done so the backup is taken under u0 3 that's the database backup and the control file with your SP file backup it will be done under your flash recovery area so that's ok we'll let it be there we'll exit now next is we need to execute these three scripts so sqlplus / assist DBA as mentioned earlier you can execute these three scripts in separate windows also or kill scale + slashes TBA will execute the second script and we'll take one more window will execute the third script now let us wait for the scripts to be completed okay so one script is completed we'll close this window so this procedure is completed okay if you encountered some errors what you can do is you can try and run it once again so meanwhile the scripts run what we'll do is we'll try and install 12 101 binaries I have already copied the 12 101 binaries into the machine let us try and install it so we'll take a new session yeah 12 okay fine Oracle two three right so I have already copied the binaries into you zero three let us take the binaries it's under soft let us unzip the files one by one this is 12c release one software okay so first file unzip is done let us unzip the second one so once unzip is done we have this database folder but before getting into the database folder let us create a 12c home under you 0 3 because that's the drive where we see space free space so mkdir so before that env crap ora mkdir - p will make it /u 0 3 oracle 12 101 / TB home this is our 12c home let us make it with note of this one will open a new notepad you wanna go home and we'll also make a note of old Oracle home that is nothing but our 11 204 home we'll just keep these details okay so we have our Oracle home let us get inside the database folder and we can see that we have the run Installer let us run the installer dot slash run installer now we see that this procedure got successfully completed so sometimes if you encounter any errors while running these procedures just execute them once again it makes no harm to your database it's just there trying to get these shots of your database so we'll keep this window and in the other window as we are installing 12c we'll come back to this window later now our Oracle Database 12c installer is starting okay guys so before getting into the installation one thing I want to tell you Oracle 12c has a different architecture Oracle 12c is known for its cdb and PDB concepts CDV is nothing but container database pdb is nothing but pluggable database basically what happens in c DB PDB concept is let's take I have broadly B and prod DB is a container database c DB and in my system I also have testy be def DB and QA DB I can eventually plug these databases into proudly be nothing but one container database now when you plug these databases what are the benefits and what are the disadvantages the benefits that you get is like you have one big engine of one container database that takes care of all the databases so if you want to access your test DBQ a DB or any database that is pluggable database you always connect to one container database and you can switch to any of those databases so that is like one benefit so I'm not getting into the concepts right now I am just giving you an overview but what is the major drawback over here is let's take your test databases 11g version your deaf mrs. tangey version and your QA probably let's take it as leverage aversion and your application team is not ready to upgrade these databases just because they want to continue to keep them for some more time for the same database version but when you go with the PDB ncdb concept you have to first upgrade each and every database to 12c before you can make it a pluggable database so you need to upgrade your test DB def DB and QA DB to 12c and then only it becomes your PDB if you want to make a pluggable database you cannot directly plug any version into the container database it must be minimum or it must be same 12c version so now you might have a doubt like what exactly are we doing right now are we creating any container or pluggable databases no so what we are doing is we have our 11 204 11 204 Oracle home and this Leavitt 204 is a normal database that is single instance and there is no concept of PD b or c DB there is no such concept because it is introduced into LC in 12 C because we know we have PD b and c DB concept we are performing a normal installation normal installation is just like our 11g version how we install the Oracle binaries for a single instance database we won't be choosing any option which is related to C DB or PDB concepts we won't be choosing any of these options we will just perform a normal 12 C installation and then wil will perform a simple upgrade of your 11g database 212 C so this 12c the normal installation works exactly like your 11g version there is no PD b c DB concept because we don't want to use that feature the question is why are we not using that feature or what is the market status or what is the usage of pdb ncdb in the market currently because pdb ncdb concept is very new and it is released with 12c version it won't be adapted so fast in the market market takes time and generally organizations will learn from other organizations mistakes but this concept will probably take time to you all or maybe in the 13c version or the upcoming versions it might get pretty much mature and then people or the organization's try to adopt the pdb ncdb concept right now majority of the organization's are just upgrading to 12c normal database and going with the 12c features they are just not using the pdb n c DB concepts yet so yes as of now in our case also we'll be performing a normal 12 z installation and let us proceed fine so let us continue with 12c installation we'll just uncheck this one next we are skipping the software updates next now we have three options we can create and configure a database but we'll just install the database binaries because we are trying to upgrade from earlier version to 12c you also have this option of upgrading an existing database but we'll try to go with the manual method because that's the one which is globally accepted click Next so we'll be performing a single instance database installation go with the default language next we'll go with the Enterprise Edition the software location is nothing but our Oracle home and this value should be our new Oracle home location under use 0 3 ok it's not taking copy/paste let us type the location /you zero three Oracle 12 101 DB undress go home an Oracle base location we'll put it somewhere you zero three slash Oracle slash Oracle underscore base click next so it will prompt you that your Oracle home is outside Oracle base not an issue you can just click Next and proceed so you can keep all of them as DBA just select DBA click Next your run installer will perform os-level prerequisites for your 12c database let's take if something is missing or you need to install some rpms it will prompt you we see that our server is good with all the prerequisites so we are good to install Oracle 12c just click on install now Oracle 12c is being installed on your server so let us review these steps we have performed the free upgrade tasks and right now we are installing 12 101 we have downloaded and installing the 12 one into the different Oracle home location once the installation is done like how we have done in our previous upgrades we'll be running a pre upgrade utility which is provided by Oracle itself so this utility resides under 12 101 Oracle home and once we run this utility we'll get to know all the issues or if you need to add some space into table spaces or if there is any other issue then we'll have to fix them this is kind of like preparing for the upgrade by fixing the issues so once that is done we need to shut down the database listener and Enterprise Manager and after that we'll be starting our database upgrade let us run this route script so we'll have to run it as a root user a login as root this is slash user two three Oracle twelve one no debe home through dot sh done so we can close this window click on OK a year twelve C installation is successful close the window and we can close this window as well right so we are back to our original window.env crap Ora this is still pointing to eleven 204 let us proceed the next step is we need to run the pre upgrade utility which resides under 12 101 home so how do we run this one we'll have to connect to SQL plus and because this is pre upgrade utility you have to start your database from your previous release only and we'll run a pre upgrade utility which comes with every Oracle home and before you try to upgrade to the next version so at the rate we have to give Oracle home location so we have the location over here this is our 12c home our DBMS admin and we have to run the pre upgrade utility so this is pre upgrade dot SQL this will perform all the pre checks before upgrade and it will point us if there are any issues that we need to fix before upgrade ok great so pre upgrade utility is completed let us go through the pre upgrade utility it's saying free upgrade checks complete now result of the checks are located at this is the log which we need to refer pre upgrade fix up script run in source database environment this will suggest you to run some scripts this will automatically try to any issues so 12c is a little bit smart it will give you some scripts which you can run and they will fix if there is an issue next fix up scripts must be reviewed prior to begin execute it don't run the fix-up scripts so if you ask me personally you should not run the fix-up scripts at all you should review the pre upgrade log and then fix the issues manually let's take you need to add some space to table space or you need to gather some starch or any other ask that is provided in the log next user action required the following are error level conditions that must be addressed prior to attempting your upgrade failure to do so will result in a failed upgrade now it's saying we need to purge our recycle bin definitely we know that we should purge recycle bin before upgrade next check the recycle bin is empty prior to upgrade the recycle bin will be first so you must resolve the above error prior to upgrade let us look at this log file flex it and we'll cat this log file the best way to look at this log file is just look at the summary you can see that one error for warnings and two informational messages you must always try to get zero errors and fix any errors if you see in the log so now if we go up and read the law completely we can check the error what is the error and try to make the adjustment okay so Oracle database pre upgrade information tool script version 12 101 database name Prada be current version of the databases 11 204 and our platform is Linux so this part will give us rename parameters so if there is any parameter that is renamed from 11 204 to 12 101 version next is obsolete or depreciated parameters sometimes some parameters are depreciated from the newer versions of the latest versions so we see that there is no obsolete or depreciated parameter in this upgrade next is component list and we can see all these components are upgradable and they are valid which means we can go ahead and upgrade all these components with our database tablespaces if we look at all these tables phases it says no adjustments recommended now pre upgrade checks first one is warning process count may be too low database has a maximum process count of 150 which is lower than the default value of 300 for this release you should update your process value prior to upgrade for a value at least 300 what we'll do is we can keep this as an option and we'll run this one so let's make a note of all the changes what our pre upgrade tool is telling us next Enterprise Manager database control repository found in the database so it's saying in the Oracle Database 12c database control is removed during the upgrade to save time during the upgrade this action can be done prior to upgrading using the following steps after copying already BMS admin e/m removed at a scale from the new oracle home right now we did not configure any Enterprise Manager in our Levangie but there is a repository database so as it is a warning you can just leave it you must focus to fix as many errors as possible and if you are able to fix some warnings also that is okay but it is not mandatory that you have to fix all the warnings next standby database not synced definitely we did not start up our standby database and it will definitely report that standby database is not in sync we can just leave it and you must always remember your standby database should be synced prior upgrading next is existing DBMS LDAP dependent object so database contains schema with object dependent on DBMS Elda package you can just leave this one next is informational informational just leaves them this is the error the error is recycle bin not empty it says your recycle bin contains 48 objects it is required that the recycle bin is empty prior to upgrading let us execute this command this is the command given by our fix-up script and we'll make a note of this now that's the end of our free upgrade utility let us connect to a scale plus and run the commands given by pre upgrade tool and also will purge the recycle bin once this is done try to take a bounce of the database start up once your database starts it's your job to read on the pre upgrade utility and verify whether the errors have been removed or not you should never blindly continue with the next step until you verify whether all the errors are resolved so right now after the database starts we will rerun the pre upgrade utility and try to check the pre upgrade log once again to see zero errors right database is open so we'll copy the script from above it is at the rate you 0 3 that is our 12c home our DBMS Ebden now sorry admin pre upgrade SQL it will exit and we'll catch the result log once again as I told you look at summary and you must see zero errors now once you see zero errors it means you're ready for database upgrade let us move on so next step is review and fix all the issues reported by the above script we have already done that now we'll shut down the database listener and enterprise manager console any which ways we don't have EMC so we'll just shut down the database and listener sqlplus / is TBA shut immediate right our database is down so PA syphon EF crap p m-- on PA - EF CREP TNS no listener is running so we can right away go ahead for the next step next is we need to start the upgrade now before starting the upgrade what we need to make sure is this has already been covered in 11 201 - 11 204 upgrade but once again I am reiterating we have this 11 204 home and this is 12 101 right now when we type a skill plus the utility comes from 11 204 Oracle home that's the utility but right now before we perform the database upgrade we need to start the database from SQL plus of 12 101 home now to do that first of all what we need to do is we need to set environmental variables to 12c Oracle home location that's the first thing second thing is when we start the database start up upgrade and if the SQL plus utilities from 12c home then we need to have the database SP file in the 12c home DBS location so what we need to do is we need to copy the current SP file which is in 11 204 home to 12c DBS location same way we will also be copying the network admin location because this is a location where we have all the listener configuration files and TNS entries to network admin occasion of 12c home remember in every upgrade these two locations you have to copy from your previous Oracle home to your new Oracle home because the first one the BBS location it contains all your P file sv file and your network admin location contains all your DNS entries and listener configuration files so because you don't have to recreate the listener cantina central files on the new oracle home you just copy them from the old oracle home now this is our next activity step now in the start the upgrade phase the first one is copy DBS and network folder from old Oracle home to New York home let us do that first of all check the current environmental variables can be crap let us go to the current Oracle home slash DBS location so we have all these files under current Oracle home location that is 11 204 will copy all the files to 12c home slash bin sorry slash DBS location now let us check whether we got all the files under well see DBS location or not you can see all the files are there and also will go to Oracle hundreds go home slash network slash admin LS hyphen LRT will copy all of them to 12c home network admin location okay just leave these samples directly because it's there in both the Oracle homes so it will just omit the directory check once again whether all the listener and Tina's files are copied or not right they are there in 12c home remember till now we did not change the environmental variables it is still pointing to our old Oracle home 11 204 let's proceed now next step is set environmental variable Oracle home to new Oracle home location so export Oracle underscore home equals two you have to export it to 12c home and we have to also modify the path variable so export path equal to copy the entire string and at the end just append the Oracle home slash bin location once again check your variables aura it is now pointing to 12c home for the testing purpose what we'll do we'll just type a scale plus and see whether the utility is starting from 12c home or not great we see that utility is starting from 12 Z home and even if you want to check further you can test with LS in our CTL it is also starting from 12 101 and even you can type Armand this is also starting from 12c home our environmental variable looks fine let's move start up the database in startup upgrade mode now how are we able to start up the database because right now the database is pointing to wealthy home and we have already kept the SP file in the 12c home DB s location so we can very much start the products from this location so sqlplus / as DBA startup upgrade ok so once your database is started up in the upgrade mode you can check it by a select instance name strength underscore name comma status from V dollar instance we can see these statuses open migrate so whenever you see such status understand that somebody started database in startup upgrade mode right now exit from a skill plus int will see the upgrade utility is little bit changed it is always run from out of the SQL plus utility in our previous upgrade the 11 201 211 204 we ran an upgrade script inside a skill plus which performed the database upgrade but int well see you need to come out of SQL plus you need to get into this location nothing but your Oracle home our DBMS admin location this is a location where we have all the scripts so just for confirmation I am checking the path whether we are really under 12z home a DBMS location yes we are and this is the main script which performs the Oracle upgrade so Oracle home pearl bin pearl cat CTL dot PL and cat upgrade dot SQL this is a script which will perform the actual upgrade of your 12c database just run the script and it will start the upgrade now you can see the phases it will upgrade your database in phase wise right now it is phase 0 then it will start with phase 1 phase 2 phase 3 and it will complete your database upgrade we can see that totally there are 56 phases and once all the phases are completed your database upgrade will be done then we will be getting into the post upgrade steps so now the upgrade is completed let us check the Piemonte status PA - EF creppy Mon nobieman is running and we also know that after the upgrade the database is shut down and what we need to do is we need to start the database in a normal mode so let us review the steps that we have done we have started the upgrade we have already copied the deviation network folder we have set the environmental variables we have startup the Debian startup upgrade mode and we have also ran the upgrade scripts this part is done now we are under post upgrade steps what we need to do is we need to start the database normally and make sure it started with 12-1 oracle home so first cross check your environmental variables it is set to twelve one Oracle home great skill plus slash as TBA startup right so database is open select name comma open and just go mode from three dollar database it's our prod DP and we can also check the database is started with 12 101 SQL plus utility great now once the upgrade is done try to run the UT lr p dot SQL script this will recompile the invalid objects if any in your database environment so add the wait question mark is nothing but your Oracle home our DBMS admin slash util our B dot a skewer our utl our PD script is completed and this is the output mostly whenever you look at this output try to see that there are no invalid objects or and you know if you see this one compile 0 out of two thousand nine ninety eight objects considered zero fail compilation almost all our compiled now we are good to move ahead and even if you scroll up you can see errors during the compilation zero objects with errors 0 so let's move on now there are a few post upgrade scripts that we need to run and these all scripts reside under same location our DBMS Edmund under your 12z home so let us run these scripts at the rate this is question mark is Oracle home our DBMS admin and we have the first script UT Lu 12 1 s dot SQL so this script will give the list of all the components right now in the database and their current versions now we can see that most of these components have 12 101 version and as discussed earlier the Oracle application Express this has its own version and release numbers so we can leave that but apart from this we must see all of them as 12 101 release and also the current status must be valid and there is one more script that is additional in your will see that is cat upp este script and this is also you have to run as part of your post upgrade steps so a delayed Oracle home our DBMS admin and then cat upp st dot SQL this script will modify some components inside your database and this is very important to run your script great script is completed now once again after you run the script you will have to run the same UTL you 12 1s broad SQL script let us run it at the rate Oracle home or DBMS admin why are we running these scripts again and again so sometimes what happens the first time when you run this script you tlu 12 1's toward SQL some of the components will show as upgraded once you run the cat upp estate or SQL script they will become valid so these status will change from upgraded to valid and this is the reason you should run these scripts one by one now once you are done with your post upgrade scripts you need to check the component status let us run this query and check all our components we'll arrange the table set lines 9 9 9 column component name for a 60 great now we see all the components are mostly upgraded to 12 101 so as I told you for most of the components you must see 12 101 and for the others you need to check with the Oracle if there is a separate release number or separate version numbers for those particular components and if there is a separate version number you have to upgrade them specially and separately out of your database like for application Express you have a method to upgrade your Oracle application Express it won't be upgraded along with your database right next is change the compatibility parameter to 12 101 we know the importance of the compatibility parameter in real time you must change the compatible parameter after 30 days do not change it immediately because you cannot downgrade your database if you do so but right now we are in the test environment so we'll go ahead and modify this parameter but before that show parameter compatible it is set to 11 204 whenever you set this parameter you have to take the database bounce so just issue this command alter system set compatible equal to 12 101 scope equal to SB file now if you do not set this parameter to 12 101 your database is already upgraded into 12 101 but you cannot use the 12:1 features next check the version details post upgrade so this query will give us the version details so just now we have seen the same output most of the components are upgraded to 12 101 bounce the database shut immediate once the database bounce is done you need to check the compatible parameter and then your database upgrade is done fine database is down will exit skill plus lashes is DBA startup whenever you perform an upgrade it is always good to exit from the skill plus why because all these upgrade scripts will set some session level parameters now because you are dealing with the upgrade and you shut down your database so try to exit from a skill plus so that all those session level parameters are reached and then once again starred SQL plus with a fresh terminal right so select name comma open and risk of mode from V dollar database perfect we have proudly be up and running let us look at the cleaner process now what we need to do is we need to update the oracle home under slash etc' or a tab file this is very important and why it is important because people must know the current oracle home and/or a tab file is the one which will tell how many databases are there in your server and what is their respective oracle home so VI / ET c or tab and as i mention just commend the last oracle home location don't delete it because it will be easy to understand ok this database has been upgraded from 11 to o and then it was upgraded to 11 204 and finally it was upgraded to 12c so let me just remove this one fraud DB give the Oracle home location this is our new Oracle home to wealthy home and n save your aura tap file check once again so we can see that fraud DB u zero three Oracle twelve 101 DB home great our aura tab file is set next is you can uninstall the old Oracle home but in our case we don't want to do it in real time also keep your old Oracle home until 30 days let's take if your application team asks you to revert to your database back to your old Oracle home you still have everything over there also the compatible parameter not set so you can download it and take a fresh backup of Veda base post upgrade so right now we are not triggering any backup because we don't need it but one more important thing is we need to edit the bash profile so if we check the environmental variables right now Oracle home is set to 12c but if we go with the bash profile we have to edit the bash profile just remove the old Oracle home and put the new Oracle home so once your bash profile is edited always execute your bash profile with dot space the file name so check the environmental variables all are set to 12c SQL plus slashes is DBA everything looks fine so we are done with the 11 204 212 C relieves one upgrade

Channel: DBA Genesis

Views: 43,151

Keywords: oracle database, database upgrade, database upgrade from 11g to 12c, oracle upgrade 11g to 12c, oracle 12c upgrade, dba genesis, oracle dbua, manual database upgrade, oracle Upgrade Steps, oracle 11g to 12c upgrade, how to upgrade oracle 11g to 12c, oracle documentation, oracle database download, oracle 11g download, oracle 12c download, oracle database versions, upgrade oracle 11g to 12c, step by step oracle database upgrade from 11g to 12c

Id: R6ChweJvis0

Length: 45min 40sec (2740 seconds)

Published: Thu Jul 19 2018