Upgrade
Database From 11g To 12c Manually
A database can be upgrade either using DBUA or manual method.
But for major production databases , it is better to do it manual, so that
troubleshooting will be easy. Here we will provide steps for upgrading from
11gr2 to 12c database manually and DBUA silent method
Current ORACLE_HOME= /u01/app/oracle/product/11.2.0.4
Target
ORACLE_HOME=/u01/app/oracle/product/12.1.0.2
PRECHECKS:
·
Check for the Invalid
Components in the database using query DBA_REGISTRY. It list the components’
status. The status for all components must be valid before attempting the
upgrade.
SQL> SELECT comp_name ,
version, status FROM dba_registry;
·
Find out if there are
invalid and duplicate objects owned by the SYS and SYSTEM users. We find and
fix invalid and duplicate objects as illustrated below.
SQL> SELECT DISTINCT
object_name, object_type, owner FROM dba_objects WHERE
status=’INVALID’ ORDER BY owner,object_name, object_type;
It’s important to note that you do not start the
database upgrade when there are invalid data dictionary objects. To recompile
invalid objects, connect as SYSDBA and run the supplied script ‘utlrp.sql’ as
shown below.
SQL> @?/rdbms/admin/utlrp.sql
·
Before starting the
upgradation process, take the complete backup of database
Take cold backup of 11.2.0.4 database:
$ mkdir -p /u01/ test11g12c /coldbkp_prod
$ sqlplus / as sydba
SQL> select name from
v$datafile;
SQL> select name from
v$controlfile;
SQL> select member from
v$logfile;
SQL> shutdown
$ cp <all datafile names>
<all controlfile> <all redolog files> /u01/test11g12c/coldbkp_prod/.
·
Stop the Listener
$ lsnrctl stop listtest11g12c
UPGRADATION PROCEDURE
After checking all the prerequisites follow the below
procedure to upgrade the database.
·
The sql script has been
replaced with the preupgrd.sql script. The new Pre-Upgrade Information tool
provides fix-up scripts with the ability to fix and address any issues
identified by the pre-upgrade check script
Copy the preupgrade.sql script from the new 12c
home to present working directory.
$ cp /u01/app/oracle/product/12.1.0.2/rdbms/admin/preupgrd.sql /u01/test11g12c/.
$ export ORACLE_HOME=
/u01/app/oracle/product/11.2.0.4
SQL> @/u01/test11g12c/preupgrd.sql
·
The above script will give
you the requirements to upgrade the database to new version. Perform any
manual fix steps identified by the pre upgrade script or you can run
preupgrade_fixups.sql. It is a fix up script that should be run before the
upgrade but the manual fix procedure is recommended. To run
preupgrade_fixups.sql use
SQL> @/u01/app/oracle/product/11.2.0.4/cfgtoollogs/test11g12c/preupgrade/preupgrade_fixups.sql
·
Copy the parameter and
password files from the old home to the new 12c home.
$ cp
/u01/app/oracle/product/11.2.0.4/dbs/inittest11g12c.ora
/u01/app/oracle/product/12.1.0.2/dbs/inittest11g12c.ora
$ cp /u01/app/oracle/product/11.2.0.4/dbs/orapwtest11g12c
/u01/app/oracle/product/12.1.0.2/dbs/orapwtest11g12c
·
If the “preupgrade.log”
file contains references to deprecated initialization parameters like changing
the SGA size, make the suggested changes to the parameters now before
continuing by editing parameters in the parameter file,
$ vi inittest11g12c.ora
·
Set the environment is to
new 12c home and run the environment file.
$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2
·
By connecting to SQL*PLUS
client start the database in upgrade mode,
$sqlplus / as sysdba
SQL> STARTUP UPGRADE
·
The previous upgrade
script catupgrd.sql has been replaced with the new catctl.pl Parallel Upgrade
Utility script which provides the ability to run upgrade scripts in parallel
taking advantage of spare CPU capacity which can potentially reduce upgrade
times. Run the new Parallel Upgrade Utility (catctl.pl).
$ cd $ORACLE_HOME/rdbms/admin
$ $ORACLE_HOME/perl/bin/perl
catctl.pl -n 4 catupgrd.sql
·
In case of a container
12.1.0.1 source database with one or more pluggable databases, it should be run
for CDB$ROOT, PDB$SEED and also each of the pluggable database.
SQL>alter session set container
= “CDB$ROOT”
SQL>
spool c:\temp\upgrade.log
SQL>
@catupgrd.sql PARALLEL=NO
SQL>alter
session set container = “PDB$SEED”
SQL> @catupgrd.sql PARALLEL=NO
·
Startup the upgraded
database and run the “utlu121s.sql” script to check the summary of the upgrade
results.
SQL>
@?/rdbms/admin/utlu121s.sql
If we had no errors, the “catuppst.sql” script would
have been run as part of the upgrade. if you have an errors, you
need to run it manually.
SQL>
@?/rdbms/admin/catuppst.sql
·
If the
“postupgrade_fixups.sql” file contained any recommendations, run it now
SQL> @/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql
·
You can do this by
manually as follows,
Check for the invalid objects by
SQL> SELECT DISTINCT object_name, object_type, owner FROM
dba_objects WHERE status=’INVALID’ ;
SQL> select count(*) from
dba_objects where status like ‘%INVALID%’;
If there are any invalid objects run the below script
to validate the invalid objects,
Recompile invalid objects.
SQL>
@?/rdbms/admin/utlrp.sql
Check for newly invalid objects.
SQL>
@?/rdbms/admin/utluiobj.sql
Run again to check the final outcome of the upgrade.
SQL>
@?/rdbms/admin/utlu121s.sql
After running above step again check the invalid
objects.
·
Now the upgraded database
is available for use and you must alter any script or profile files that
contain references to the ORACLE_HOME environment variable.
SQL> Startup
SQL> SELECT name, open_mode FROM v$database;
SQL> select banner from v$version
SQL> select
version, comp_id, comp_name, status from dba_registry;
This completes the upgradation procedure.
Upgrade
Database From 11g To 12c DBUA Silent mode
PRECHECKS:
Delete the Database Recycle Bin
The
recycle bin must be purged before starting the database upgrade. Use the
command below to empty the database recycle bin for all users on the source
database.
SQL> PURGE
DBA_RECYCLEBIN;
DBA Recyclebin
purged.
SQL>
Invalid and Duplicate Objects
Here,
we find out if there are invalid and duplicate objects owned by the SYS and
SYSTEM users. We find and fix invalid and duplicate objects as illustrated
below.
SQL> SELECT
DISTINCT object_name, object_type, owner
2 FROM dba_objects
3 WHERE status='INVALID'
4 ORDER BY owner, object_name,
object_type;
no rows selected
SQL>
It's
important to note that you do not start the database upgrade when there are
invalid data dictionary objects. To recompile invalid objects, connect as
SYSDBA and run the supplied script 'utlrp.sql' as shown below.
SQL>
@?/rdbms/admin/utlrp.sql
Invalid Components
Query
DBA_REGISTRY to list the components' status. The status for all components must
be valid before attempting the upgrade. Also, the DBUA pre-upgrade script
reports installed components' status.
SQL> SELECT
comp_name component, version, status
2 FROM dba_registry;
Database Pre-Upgrade
· The sql script has been replaced with the preupgrd.sql script. The new Pre-Upgrade Information tool provides fix-up scripts with the ability to fix and address any issues identified by the pre-upgrade check script
· The sql script has been replaced with the preupgrd.sql script. The new Pre-Upgrade Information tool provides fix-up scripts with the ability to fix and address any issues identified by the pre-upgrade check script
Copy the preupgrade.sql script from the new 12c
home to present working directory.
$ cp /u01/app/oracle/product/12.1.0.2/rdbms/admin/preupgrd.sql /u01/test11g12c/.
$ export ORACLE_HOME=
/u01/app/oracle/product/11.2.0.4
SQL> @/u01/test11g12c/preupgrd.sql
·
The above script will give
you the requirements to upgrade the database to new version. Perform any
manual fix steps identified by the pre upgrade script or you can run
preupgrade_fixups.sql. It is a fix up script that should be run before the
upgrade but the manual fix procedure is recommended. To run
preupgrade_fixups.sql use
SQL> @/u01/app/oracle/product/11.2.0.4/cfgtoollogs/test11g12c/preupgrade/preupgrade_fixups.sql
Upgradation method
$ /u01/app/oracle/product/12.1.0.2/db_1/bin/dbua
-silent \
> -sid test11g12c \
> -oracleHome @/u01/app/oracle/product/11.2.0.4/ \
> -diagnosticDest /u01/app/oracle \
> -autoextendFiles \
> -recompile_invalid_objects true \
> -degree_of_parallelism 2 \
> -upgradeTimezone \
> -emConfiguration NONE \
> -keepHiddenParams \
> -gatheringStatistics \
> -createGRP true \
> -upgrade_parallelism 2
After the script completed complete all post check which is same
as manual method
