ORACLE 11G TO 12C DATABASE UPGRADE MANUALLY
-----------------------------------------------------------------------------
SUMMARY ORACLE 11.2.0.4 TO 12.1.0.1 UPGRADE MANUALLY
----------------------------------------------------------------------------------------
PREPARE FOR UPGARDE:
-------------------------------------
1)Take the database backup via Rman
2)Take oracle_home,orainventory backup via os level copy
3)Run the utlrp.sql script to validate the invalid objects
4)exec dbms_stats.gather_database_stats;
5)exec dbms_stats.gather_dictinary_stats;
6)exec dbms_stats.gather_fixed_object_stats;
INSTALL 12.1.0.1:
------------------------
1)Download and Install 12.1.0.1 into a a different Oracle_home location
2)Run the pre_update tility which resides under 12.1.0.1 home
12.1.0.1_home/rdbms/admin/preupgrad.sql
3)Review and fix all the issues reported via above script
4)Shutdown the Database, Listener.EMC(Enterprise manager)
START THE UPGRADE:
---------------------------------
1)Copy DBS and Network Folder from old ORACLE_HOME to new ORACLE_HOME
2)Set environmental variable old ORACLE_HOME to new ORACLE_HOME loaction
3)Start up the Database in startup upgarde mode
4)Run the upgarde utilitywhich upgrades the database exit from sqlplus
cd $ ORACLE_HOME/rdbms/admin
$ ORACLE_HOME/perl/bin/perl catctl.pl catupgrad.sql
POST UPGRADE STEPS:
-----------------------------
1)Start up the Database Normally make sure its started with 12.1.0.1 ORACLE_HOME
2)Run the utlrp.sql script to validate the invalidobject
3)Run the post- update script
12.1.0.1_home/rdbms/admin/utlu121s.sql
12.1.0.1_home/rdbms/admin/catuppst.sql
12.1.0.1_home/rdbms/admin/utlu121s.sql
4) Check the Component status
sql>select comp_name,status,substr(vesrion,1,10) as version from dba_registry;
5)Change the Compatibilty parameter to 12.1.0.1
sql>alter system set compatibile= '12.1.0.1.0' scope= spfile;
6)Check the vesrion details
sql>select comp_name,version,status from dba_resgistry;
CLEAN UP PROCESS:
------------------------------
1)Update the new oracle_home under /etc/oratabfile
2)Uninstall the old_home
3)Take a fresh backup of Database post upgrade
Note: Upgrade only for the binaries not for the data
NOW LETS START WITH THE PROCESS :
-----------------------------------------------------------
$ ps - ef | grep pmon
$ sqlplus '/as sysdba'
sql> starup
Oracle instance started
Database Opened
sql> select name,open_mode from v$database;
name--->proddb
open_mode---->read,write
sql>exit
$ rman target /
RMAN> list backup of database summary;
RMAN> show all;
Configure channnel device type to disk '/u02/backup/proddb_%U';
RMAN>exit
$ df -h
for eg /u02 is filled with 80% ofthe disk
so available space in /u03
$ mkdir - p /u03/backup ( we cretaed a directory backup in u03 location bcoz space is available)
$ rman target /
RMAN> configure channel device type disk format '/u03/backup/proddb_%U';
RMAN> congigure channel device type disk format '/u03/backup/proddb_%U';
Rman parameters successfully configured
RMAN> show all;
RMAN> backup database;
RMAN>exit
$ sqlplus '/as sysdba '
sql> exec dbms_stas.gather_database_stats;
open a terminal login as oracle user execeute second script
sql>exec dbms_stats.gather_dictionary_stats;
and next open another new terminal login as a oracle user and execute the third script
sql>exec dbms_stas.gather_fixed_object_stats;
once the script is complted close the terminal and have an error in the scripts do it again and run the script
after that 12c binaries and into different location already 12c database downloaded inthe /u03 location
login in as oracle user ( 12 c database (binaries) unzip one by one 12c release 1 software after that we have to create a oraclehome directory in /u03 after unzipped
create a directory database folder in the softwares directory
Note : old oracle_home /u02/oracle/11.2.0.4/db_home
new oracle_home /u03/oracle/12.1.0.1/db_home
$ df -h
/u02
/u03
$ cd /u03/
ls
we will find folders backup lost+fount softwares
$ cd softwares /
$ ls -lrth
linuxamd64_12c_database_1of2.zip
linuxamd64_12c_database_2of2.zip
$ unzip linuxamd64_12c_database_1of2.zip
$ unzip linuxamd64_12c_database_2of2.zip
$ df -h
$ env |grep ORA
ORACLE_SID = proddb
ORACLE_HOME =/u02/oracle/11.2.0.4/db_home
$ mkdir -p /u03/oracle/12.1.0.1/db_home
$ cd softwares
$ ls
database
$ softwares] cd database
ls
runInstaller
$ ./runInstaller
( Oracle Installer 12c window is starting before getting into installation 12c one thing i have to tell that 12c is a multitenant architecture we have cdb and pdb concept will come on 12c and we are installing only binaries that is install database software only after that login as a root user run that root.sh script back to the first terminal env it shows the old loaction we have to go the sql prompt login as sysdba go to the oracle home loaction rdbms/admin/preupgrad.sql run the procedure all the prechecks before upgrade we have to check the log preupgradation utility once the run preupgrad utility go to the sql plus)
-----> Insatll database software only
------>single Instance database installation
------> go with enterprise edition
oracel_base : /u03/oracle/oraclebase
software location :/u03/oracle/12.1.0.1/db_home
click on --->next
select dba --->all
click on --->install on your server
Open a new terminal
login as root user
and execute the root.sh scripts
# /u03/oracle/12.1.0.1/db_home/root.sh
after execucting this script 12 c installatioin is done
$ env |grep ORA
ORACLE_SID = proddb
ORACLE_HOME= /u02/oracle/11.2.0.4/db_home
sqlplus '/as sysdba
sql> @ /u03/oracle/12.1.0.1/db_home/rdbms/admin/preupgrad.sql
(preupgrade pre execute is complete some errors and warnings we have go to the logs and check the errors and warnings)
$ cat /u02/oracle/11.2.0.4/db_home/cfgtoollogs/proddb/preupgrad/preupgrad.log
$ sqlplus '/as sysdba'
sql> alter system set process = 300 scope =spfile;
sql>execute dbms_preup.purge_recyclebin_fixup;
sql>pl/sql procedure suceesfully completed
sql >shutdown immediate
sql> startup
database opened
sql> @/u03/oracle/12.1.0.1/db_home/rdbms/admin/preupgrad.sql
sql> exit
$ cat /u02/oracle/11.2.0.4/db_home/cfgtoollogs/proddb/preupgrade/preupgrade.log
(Return the preupgrad utility once again and resolve the errors and warnings and see the log file there is no error in the log look at the log file and found 0 errors)
sqlplus '/as sysdba'
shutdown immediate
exit
ps -ef | grep pmon
ps -ef| grep tns
$ env |grep ora
ORACLE_SID = proddb
ORACLE_HOME ='/u02/oracle/11.2.04/db_home
( We need to copy the spfile and network/admin listener & Tns files to 12.1.0.1 home location
11.2.0.4-----> db_home/dbs----> spfile copy ----> new loaction 12.1.0.1 /u03/db_home/dbs
11.2.0.4-------> db_home/network/admin----> tnsnames.ora listener.ora shrept.lst copy all files to new location --->12.1.0.1 /u03/db_home/network/admin
(copy the dbs and network folder from old oracle_home 11gto new oracle_home 12c location let us check the directory /u03 whether the files are copied or not)
ORACLE_SID = proddb
ORACLE_HOME ='/u02/oracle/11.2.04/db_home
$cd u02/oracle/11.2.0.4/db_home/dbs
$ ls -lrth
$ cp * /u03/oracle/12.1.0.1/db_home/dbs/
ls -lrth /u03/oracle/12.1.0.1/db_home/dbs/
$ cd /u02/oracle/11.2.0.4/db_home/network/admin/
$ ls -lrt
$ cp * /u03/oracle/12.1.0.1/db_home/network/admin/
$ ls -lrth u03/oracle/12.1.0.1/db_home/network/admin/
tnsnames.ora, listener.ora ,shrept.lst
$ env |grep ORA
ORACLE_SID = proddb
ORACLE_HOME ='/u02/oracle/11.2.04/db_home
$ export ORACLE_HOME ='/u03/oracle/12.1.0.1/db_home
(Till now we will not change environment till its pointing to oracle home 11204 so set the export oracle_home and oraclepath after that once again check now its pointing to 12c home)
$ echo $ path
$ /usr/lib64/qt_3.3/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/u03/oracle/11204/db_home/bin
$ export path= /usr/lib64/qt_3.3/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/u03/oracle/12101/db_home/bin
$ env |grep ORA
ORACLE_SID = proddb
ORACLE_HOME =/u03/oracle/12.1.0.1/db_home
sqlplus '/as sysdba'
release version it showing while opening database 12.1.0.1.0
exit
$ lsnrctl
$ rman
exit
$ env |grep ORA
ORACLE_SID = proddb
ORACLE_HOME =/u03/oracle/12.1.0.1/db_home
sqlplus '/as sysdba'
sql>startup upgrade
sql> select instance_name,status from v$instance;
instance_name--> proddb
status----->open migrate
exit
cd $ oracle_home/rdbms/admin
$ pwd
/u03/oracle/12.1.0.1/db_home/rdbms/admin
$ ORACLE_HOME/perl/bin/perl catctl.pl
catupgrd.sql
$ ps -ef |grep pmon
env |grep ORA
ORACLE_SID = proddb
ORACLE_HOME =/u03/oracle/12.1.0.1/db_home
sqlplus '/as sysdba'
sql> startup
sql> select name,open_mode from v$database;
name---> proddb
open_mode--->read,write
sql> @?/rdbms/admin/utlrp.sql
pl/sql procedure sucessfully completed
sql> @?/rdbms/admin/catuppst.sql
sql> @?/rdbms/admin/utlul21s.sql
check the component status
sql> set lines 999;
col comp_name for a60;
/
sql> select comp_name,status,substr(version,1,10)as vesrsion from dba_regsistry;
sql> show parameter compatible;
compatible 11.2.0.4.0
sql> alter system set compatible= '12.1.0.1.0' scope=spfile;
sql> select comp_name,vesrion,status from dba_registry;
bounce the database
sql> shut immediate
exit
sqlplus '/as sysdba'
sql> select name,open_mode from v$database;
name---> proddb
open_mode--->reda,write
exit
$ vi /etc/oratab
proddb: /u03/oracle/12.1.0.1/db_home:n
(just remove the old oracle home to new oracle home)
cat etc/oratab
proddb: /u03/oracle/12.1.0.1/db_home:n
vi bash_profile
after that execute the bash profile
..bash_profile
export oracle_home= '/u03/oracle/12.1.0.1/db_home
env | grep ORA
ORACLE_SID = proddb
ORACLE_HOME =/u03/oracle/12.1.0.1/db_home
Note: Upgrading 11g to 12c info on it may be differ from your environment like production,testing ,development. and directory structure etc
THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW MY SITE
https://chaitanyaoracledba.blogspot.com/
