How to upgrade Oracle database From 10.2.0.4 to 11.2.0.2
Here we are upgrading our Oracle 10.2.0.4 database software
to Oracle 11.2.0.2
1. Copy Pre upgrade Information gathering Script:
Log in to the system as the owner of the Oracle Database 11g Release 2 (11.2) Oracle home directory.
Copy the Pre-Upgrade Information Tool (utlu112i.sql) and utltzuv2.sql from the Oracle Database 11g Release 2 (11.2) ORACLE_HOME/rdbms/admin directory to a directory outside of the Oracle home, such as the temporary directory on your system.
$ORACLE_HOME/rdbms/admin/utlu112i.sql
2. Execute Pre Upgrade Script:
Should be change to the directory where utlu112i.sql had been copied in the previous step. Start SQL*Plus and connect to the database instance as a user with SYSDBA privileges. Then run and spool the utlu112i.sql file. Please note that the database should be started using the Source Oracle Home.
$ sqlplus '/ as sysdba'
SQL> spool upgrade_info.log
SQL> @utlu112i.sql
SQL> spool off
SQL>
Check the spool file and examine the output of the upgrade information tool.
1. Copy Pre upgrade Information gathering Script:
Log in to the system as the owner of the Oracle Database 11g Release 2 (11.2) Oracle home directory.
Copy the Pre-Upgrade Information Tool (utlu112i.sql) and utltzuv2.sql from the Oracle Database 11g Release 2 (11.2) ORACLE_HOME/rdbms/admin directory to a directory outside of the Oracle home, such as the temporary directory on your system.
$ORACLE_HOME/rdbms/admin/utlu112i.sql
2. Execute Pre Upgrade Script:
Should be change to the directory where utlu112i.sql had been copied in the previous step. Start SQL*Plus and connect to the database instance as a user with SYSDBA privileges. Then run and spool the utlu112i.sql file. Please note that the database should be started using the Source Oracle Home.
$ sqlplus '/ as sysdba'
SQL> spool upgrade_info.log
SQL> @utlu112i.sql
SQL> spool off
SQL>
Check the spool file and examine the output of the upgrade information tool.
3. Check for the integrity of the source database:
Check for the integrity of the source database prior to starting the upgrade by downloading and running dbupgdiag.sql script from below My Oracle Support article
Note 556610.1 Script to Collect DB Upgrade/Migrate
Diagnostic Information (dbupgdiag.sql) (Avoid this step if don’t have support
access)
If the dbupgdiag.sql
script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/utlrp.sql
(multiple times) to validate the invalid objects in the database, until there
is no change in the number of invalid objects.
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> @utlrp.sql
$ sqlplus "/ as sysdba"
SQL> @utlrp.sql
After validating the invalid objects, re-run dbupgdiag.sql in the database once again and make sure that everything is fine.
4. Check that National
Characterset:
Check that National
Characterset (NLS_NCHAR_CHARACTERSET) is UTF8 and AL16UTF16.
select value from NLS_DATABASE_PARAMETERS where parameter = 'NLS_NCHAR_CHARACTERSET';
If this is UTF8 or AL16UTF16 then no action is needed.
If is not UTF8 or AL16UTF16 then refer the following article
select value from NLS_DATABASE_PARAMETERS where parameter = 'NLS_NCHAR_CHARACTERSET';
If this is UTF8 or AL16UTF16 then no action is needed.
If is not UTF8 or AL16UTF16 then refer the following article
Note 276914.1 The National Character Set in Oracle 9i and
10g.
5.
Optimizer Statistics:
When
upgrading to Oracle Database 11g Release 2 (11.2), optimizer statistics are collected for dictionary tables
that lack statistics. This statistics collection can be time consuming for
databases with a large number of dictionary tables, but statistics gathering
only occurs for those tables that lack statistics or are significantly changed
during the upgrade
$ sqlplus "/as sysdba"
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
6. Ensure that no files need
media recovery:
Sql>
SELECT * FROM v$recover_file;
7. Stop the listener for the
database:
Make
pfile from spfile;
$ lsnrctl stop
8.
Suhtdown the Database:
Shutdown the database.
$ sqlplus "/as sysdba"
SQL> shutdown immediate;
SQL> shutdown immediate;
9.
Back Up the Database:
1- Perform Cold Backup
(or)
2- Take a backup using RMAN
1- Perform Cold Backup
(or)
2- Take a backup using RMAN
Connect to RMAN:
rman "target / nocatalog"
RUN
{
ALLOCATE CHANNEL chan_name TYPE DISK;
BACKUP DATABASE FORMAT '%U' TAG before_upgrade;
BACKUP CURRENT CONTROLFILE TO '';
}
rman "target / nocatalog"
RUN
{
ALLOCATE CHANNEL chan_name TYPE DISK;
BACKUP DATABASE FORMAT '%U' TAG before_upgrade;
BACKUP CURRENT CONTROLFILE TO '';
}
10.
Backup and change pfile:
Make
a backup of the init.ora file.
Comment out obsolete parameters
Comment out obsolete parameters
* The DIAGNOSTIC_DEST initialization parameter replaces the USER_DUMP_DEST, BACKGROUND_DUMP_DEST.
Note:
Once the Parameter file is modified as per your requirement, copy the file to
$ORACLE_HOME/dbs (11g Oracle Home )
11.
Set Environment Variables:
If
your operating system is UNIX then complete this step, else skip to next Step.
1. Make sure the following environment variables point to the Oracle database software 11g Release directories:
- ORACLE_BASE
- ORACLE_HOME
- PATH
1. Make sure the following environment variables point to the Oracle database software 11g Release directories:
- ORACLE_BASE
- ORACLE_HOME
- PATH
$ export ORACLE_HOME=
$ export PATH=$ORACLE_HOME/bin:$PATH
$ export ORACLE_BASE=
$ export PATH=$ORACLE_HOME/bin:$PATH
$ export ORACLE_BASE=
Note
: If ORACLE_BASE is not known, after setting Path towards 11g Oracle Home,
execute 'orabase', which will point the location of base.
$ orabase
/uo1/app/oracle
/uo1/app/oracle
2.
Update the oratab entry, to set the new ORACLE_HOME pointing towards ORCL and
disable automatic startup
Sample /etc/oratab
#orcl:/opt/oracle/product/10.2/db_1:N
orcl:/opt/oracle/product/11.2/db_1:N
#orcl:/opt/oracle/product/10.2/db_1:N
orcl:/opt/oracle/product/11.2/db_1:N
Note : After /etc/oratab is updated to have sid and Oracle Home (11.2), you can execute oraenv (/usr/local/bin/oraenv) and set the environment. The input has to be the sid which is entered in /etc/oratab against 11g home.
for Instance,
[oracle@localhost ~]$ . oraenv
ORACLE_SID = [orcl] ? orcl
The Oracle base for ORACLE_HOME=/opt/oracle/product/11.2/db_1 is /u01/app/oracle
[oracle@localhost ~]$
[oracle@localhost ~]$ . oraenv
ORACLE_SID = [orcl] ? orcl
The Oracle base for ORACLE_HOME=/opt/oracle/product/11.2/db_1 is /u01/app/oracle
[oracle@localhost ~]$
12.
Upgrade Database:
At the operating system prompt, change to the $ORACLE_HOME/rdbms/admin directory of 11gR2 Oracle Home.
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> startup UPGRADE
$ sqlplus "/ as sysdba"
SQL> startup UPGRADE
Set
the system to spool results to a log file for later verification after the
upgrade is completed and start the upgrade script.
SQL> set echo on
SQL> SPOOL upgrade.log
SQL> @catupgrd.sql
SQL> spool off
SQL> SPOOL upgrade.log
SQL> @catupgrd.sql
SQL> spool off
These measures are an important final step to ensure the integrity and consistency of the newly upgraded Oracle Database software. Also, if you encountered a message listing obsolete initialization parameters when you started the database for upgrade, then remove the obsolete initialization parameters from the parameter file before restarting. If necessary, convert the SPFILE to a PFILE so you can edit the file to delete parameters.
Post
Upgrade Steps
13.
Post Upgrade:
Start
the database and run the Post-Upgrade Status Tool
$ORACLE_HOME/rdbms/admin/utlu112s.sql which provides a summary of the upgrade
at the end of the spool log. It displays the status of the
database components in the upgraded database and the time required to complete
each componentupgrade. Any errors that occur during the
upgrade are listed with each component and must be addressed
$ sqlplus "/as sysdba"
SQL> STARTUP
SQL> @utlu112s.sql
SQL> STARTUP
SQL> @utlu112s.sql
14.
Recompile Invalid Objects:
This
script can be run concurrently with utlrp.sql. Run utlrp.sql to recompile any
remaining stored PL/SQL and Java code in another session.
SQL> @utlrp.sql
15. Check for the integrity of the
source database:
Check for the integrity of the
upgraded database by running dbupgdiag.sql script from below Metalink article
Note 556610.1 Script to Collect DB Upgrade/Migrate
Diagnostic Information (dbupgdiag.sql)
If
the dbupgdiag.sql script reports any invalid objects, run
$ORACLE_HOME/rdbms/admin/utlrp.sql (multiple times) to validate the invalid
objects in the database, until there is no change in the number of invalid
objects.
After validating the invalid objects, re-run dbupgdiag.sql in the upgraded database once again and make sure that everything is fine.
After validating the invalid objects, re-run dbupgdiag.sql in the upgraded database once again and make sure that everything is fine.
16.
Configure and Start Listener.ora :
Modify the listener.ora file:
For the upgraded instance(s) modify the ORACLE_HOME parameter to point to the new ORACLE_HOME. Start the listener :
Modify the listener.ora file:
For the upgraded instance(s) modify the ORACLE_HOME parameter to point to the new ORACLE_HOME. Start the listener :
lsnrctl start
17. Crosscheck Environment Variables:
Set Environment Variables
1. Make sure the following environment variables point to the Oracle 11g Release directories:
- ORACLE_BASE
- ORACLE_HOME
- PATH
Also check that your oratab file and any client scripts that set the value of ORACLE_HOME point to the Oracle database software11g Release 2 (11.2) home.
Note : If you are upgrading a cluster database, then perform these checks on all nodes in which this cluster database has instances configured.
2. Modify /etc/oratab entry to use automatic startup
SID:ORACLE_HOME:Y
For Instance,
orcl:/opt/oracle/product/11.2/db_1:Y
For Instance,
orcl:/opt/oracle/product/11.2/db_1:Y
18. Spfile from Pfile:
Edit init.ora:
- If you changed the CLUSTER_DATABASE parameter prior the upgrade set it back to TRUE
- Migrate your initialization parameter file to a server parameter file.
Create a server parameter file with a initialization parameter file
- If you changed the CLUSTER_DATABASE parameter prior the upgrade set it back to TRUE
- Migrate your initialization parameter file to a server parameter file.
Create a server parameter file with a initialization parameter file
SQL> create spfile from pfile;
This will create a spfile as a copy of the init.ora file located in $ORACLE_HOME/dbs (UNIX) & %ORACLE_HOME%\database (Windows).
19. Start the Database
with spfile:
Shutdown the database:
Sql> shutdown immediate
Now start the database it
will user spfile
Sql> startup
Check the Alert log file
for any Error.
Database is ready to use
now with Database Software Oracle 11g.
Comments
Post a Comment