We have a oracle 10g database, we have upgraded this to 11.1.0.6.
Oracle 10g home - /u01/app/ora10g/product/10.2.0/db_1
Oracle 11g home - /u01/app/ora10g/product/11.1.0/db_1
Synopsis Steps:-
1. Run the pre-upgradation sql script (utlu111i.sql) in the 10g database and
make the necessary changes as per the requirements it
throws.
2. Take a copy of the spfile as pfile and make the necessary
changes, (i.e) remove the obsolete parameter and
add the new parameters.
3. Shutdown the database and take the backup of datafiles,
redo logs, controlfiles, password file and pfiles.
4. Copy the modified pfile in the 11g home.
5. Startup the database in upgrade mode.
6. Run the catlag upgrade utility (catupgrd.sql), the database will be shutdown
once this script is completed.
7. Now Start the database in normal mode and run the catalog post upgrade
catuppst.sql and then run the utlu111s.sql and check whether all the components
are been upgraded.
8. Run the utlrp.sql to recompile all the objects.
9. Upgrade process completed.
Detailed Steps:-
Step 1:-
[oracle@linux11g ~]$ export ORACLE_HOME=/u01/app/ora10g/product/10.2.0/db_1
[oracle@linux11g ~]$ export ORACLE_SID=upg211g
[oracle@linux11g ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Mon May 30 22:30:07 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> set linesize 150;
SQL> @/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/utlu111i.sql;
Oracle Database 11.1 Pre-Upgrade Information Tool 05-30-2011 22:30:41
.
**********************************************************************
Database:
**********************************************************************
--> name: UPG211G
--> version: 10.2.0.1.0
--> compatible: 10.2.0.1.0
--> blocksize: 8192
--> platform: Linux IA (32-bit)
--> timezone file: V2
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 723 MB
.... AUTOEXTEND additional space required: 243 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 474 MB
.... AUTOEXTEND additional space required: 444 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 411 MB
.... AUTOEXTEND additional space required: 181 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
.... AUTOEXTEND additional space required: 41 MB
--> EXAMPLE tablespace is adequate for the upgrade.
.... minimum required size: 69 MB
.
**********************************************************************
Update Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
WARNING: --> "sga_target" needs to be increased to at least 336 MB
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
--> "background_dump_dest" replaced by "diagnostic_dest"
--> "user_dump_dest" replaced by "diagnostic_dest"
--> "core_dump_dest" replaced by "diagnostic_dest"
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade] VALID
--> Oracle XDK for Java [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] VALID
--> OLAP Catalog [upgrade] VALID
--> EM Repository [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Spatial [upgrade] VALID
--> Data Mining [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
--> Oracle OLAP API [upgrade] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using an old timezone file version.
.... Patch the 10.2.0.1.0 database to timezone file version 4
.... BEFORE upgrading the database. Re-run utlu111i.sql after
.... patching the database to record the new timezone file version.
WARNING: --> Database contains stale optimizer statistics.
.... Refer to the 11g Upgrade Guide for instructions to update
.... statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
.... SYS
.... OLAPSYS
.... SYSMAN
.... CTXSYS
.... XDB
WARNING: --> Database contains schemas with objects dependent on network packages.
.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
.... USER SYSMAN has dependent objects.
WARNING: --> EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... 11g Upgrade Guide for instructions to save the EM data prior to upgrade.
.
PL/SQL procedure successfully completed.
Here the first warning shows that the timezone file version is lower
and it has to be upgraded to timezone file version 4.
SQL> select * from V$timezone_file;
FILENAME VERSION
------------ ----------
timezlrg.dat 2
sql> Shutdown immediate;
We need to upgrade the timezonefile version to 4 before proceeding the
upgrade. Hence download the patch from the Metalink and apply the
patch.
Patchno for this is p5632264_10202_LINUX.zip (This patch is for 10.2.0.2 but
this patch can be applied for 10.2.0.1 also) and you cannot apply this patch
using opatch utility since it belong to 10.2.0.2 version. Hence we need to
patch it manually as below.
a) Download the patch from metalink p5632264_10202_LINUX.zip
$ unzip p5632264_10202_LINUX.zip
Take the backup of version 2 timezone files.
[oracle@linux11g zoneinfo]$ cp /u01/app/ora10g/product/10.2.0/db_1/oracore/zoneinfo/* /u01/bkpofoldtimezonefiles
copy the new timezone files to the zoneinfo directory.
$ cd 5632264
$ cd files/oracore/zoneinfo
[oracle@linux11g zoneinfo]$ cp * /u01/app/ora10g/product/10.2.0/db_1/oracore/zoneinfo/
Once the new files are copied start the datbase and check the timezone version;
[oracle@linux11g zoneinfo]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Mon May 30 22:42:24 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 83887696 bytes
Database Buffers 197132288 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> select * from V$timezone_file;
FILENAME VERSION
------------ ----------
timezlrg.dat 4
Gather the statistics as per the warning in the preupgrade script output.
SQL> exec dbms_stats.gather_dictionary_stats;
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_schema_stats('SYS');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_schema_stats('OLAPSYS');
PL/SQL procedure successfully completed.SQL> exec dbms_stats.gather_schema_stats('SYSMAN');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_schema_stats('CTXSYS');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_schema_stats('XDB');
PL/SQL procedure successfully completed.
Ignore the below warnings:-
WARNING: --> Database contains schemas with objects dependent on network packages.
.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
.... USER SYSMAN has dependent objects.
WARNING: --> EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... 11g Upgrade Guide for instructions to save the EM data prior to upgrade.
.
SQL> create pfile = '/u01/upg211g.ora' from spfile;
File created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Now open the pfile which we created above and remove the obsolete parameter and
new the new parameters and also modified the sga setting accordingly.
sga_target=336m
diagnostic_dest='/u01/app/oracle/diag'
Remove the below parameters
"background_dump_dest"
"user_dump_dest"
"core_dump_dest"
Now paste the modified pfile in the 11g home and start the database in upgrade
mode.
[oracle@linux11g ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
[oracle@linux11g ~]$ export ORACLE_SID=upg211g
[oracle@linux11g ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Mon May 30 22:52:21 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile='/u01/upg211g.ora';
File created.
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 355717120 bytes
Fixed Size 1299876 bytes
Variable Size 79694428 bytes
Database Buffers 268435456 bytes
Redo Buffers 6287360 bytes
Database mounted.
Database opened.
Check whether the Oracle Database Vault Option is enabled, if it is enabled then
disable it before the upgrade and enable it once the db is upgrade.
You can check if Oracle Database Vault is enabled or disabled by querying the V$OPTION data dictionary view. Any user can query this view. If Oracle Database Vault is enabled, the query returns TRUE. Otherwise, it returns FALSE.
Remember that the PARAMETER column value is case sensitive. For example:
SELECT * FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault';
If Oracle Database Vault is enabled, the following output appears:
PARAMETER VALUE
----------------------------- -----------------------
Oracle Database Vault TRUE
Follow these steps to disable Oracle Database Vault on UNIX systems:
Turn off the software processes. Make sure that the environment variables, ORACLE_HOME, ORACLE_SID, and PATH are correctly set.
Stop the dbconsole process in case it is running. For both single-instance and Oracle Real Application Clusters installations, run the following command at a command prompt:
emctl stop dbconsole
For single-instance installations, shut down the database instance:
sqlplus sys as sysoper
Enter password: password
SHUTDOWN NORMAL
EXIT
For Oracle Real Application Clusters (Oracle RAC) installations, shut down each database instance as follows, from a command prompt:
srvctl stop database -d db_name
If you cannot connect to the database, then proceed to the next step.
At a command prompt, run the following commands to turn off the Oracle Database Vault option:
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk dv_off ioracle
For Oracle RAC installations, run these commands on all nodes.
In SQL*Plus, start the database.
For single-instance database installations:
sqlplus sys as sysoper
Enter password: password
STARTUP
For Oracle RAC installations:
srvctl start database -d db_name
---------------
--Run the catalog upgrade script.
SQL> spool catupgrdelog
SQL> @?/rdbms/admin/catupgrd.sql;
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC> The first time this script is run, there should be no error messages
DOC> generated; all normal upgrade error messages are suppressed.
DOC>
DOC> If this script is being re-run after correcting some problem, then
DOC> expect the following error which is not automatically suppressed:
...
...
...
...
...
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> /*****************************************************************************/
SQL> /* Step 10 - SHUTDOWN THE DATABASE..!!!!!
SQL> */
SQL> /*****************************************************************************/
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Now start the database in normal mode and run post upgrade scripts.
SQL> startup
ORACLE instance started.
Total System Global Area 355717120 bytes
Fixed Size 1299876 bytes
Variable Size 150997596 bytes
Database Buffers 197132288 bytes
Redo Buffers 6287360 bytes
Database mounted.
Database opened.
SQL> select * from V$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
SQL> @?/rdbms/admin/utlrp.sql; -- ( To Recompile all the invlaid objects)
SQL> @?/rdbms/admin/catuppst.sql;
Rem DESCRIPTION
Rem This post-upgrade script performs remaining upgrade actions that
Rem dont require that the database be open in UPGRADE mode. It
Rem can be run at the same time utlrp.sql is being run.
SQL> @?/rdbms/admin/utlu111s.sql;
Oracle Database 11.1 Post-Upgrade Status Tool 05-31-2011 09:02:13
.
Component Status Version HH:MM:SS
.
Oracle Server
. VALID 11.1.0.6.0 00:13:30
JServer JAVA Virtual Machine
. VALID 11.1.0.6.0 00:04:11
Oracle Workspace Manager
. VALID 11.1.0.6.0 00:00:55
OLAP Analytic Workspace
. VALID 11.1.0.6.0 00:00:39
OLAP Catalog
. VALID 11.1.0.6.0 00:00:55
Oracle OLAP API
. VALID 11.1.0.6.0 00:00:23
Oracle Enterprise Manager
. VALID 11.1.0.6.0 00:07:39
Oracle XDK
. VALID 11.1.0.6.0 00:00:36
Oracle Text
. VALID 11.1.0.6.0 00:00:42
Oracle XML Database
. VALID 11.1.0.6.0 00:02:57
Oracle Database Java Packages
. VALID 11.1.0.6.0 00:00:20
Oracle Multimedia
. VALID 11.1.0.6.0 00:03:21
Spatial
. VALID 11.1.0.6.0 00:03:58
Oracle Expression Filter
. VALID 11.1.0.6.0 00:00:12
Oracle Rules Manager
. VALID 11.1.0.6.0 00:00:09
Gathering Statistics
. 00:03:48
Total Upgrade Time: 00:44:27
The above status shows that all the components has been upgraded to 11.1.0.6. and it
is in valid state now.
SQL> select cname,version, status, date_upgraded,org_version,prv_version from registry$;
CNAME VERSION STATUS DATE_UPGR ORG_VERSION PRV_VERSION
-------------------------------------------------- ------------------------------ ---------- --------- ------------------------------ ------------------------------
Oracle Database Catalog Views 11.1.0.6.0 1 10.2.0.1.0 10.2.0.1.0
Oracle Database Packages and Types 11.1.0.6.0 1 10.2.0.1.0 10.2.0.1.0
Oracle Workspace Manager 11.1.0.6.0 1 30-MAY-11 10.2.0.1.0
JServer JAVA Virtual Machine 11.1.0.6.0 1 10.2.0.1.0 10.2.0.1.0
Oracle XDK 11.1.0.6.0 1 10.2.0.1.0 10.2.0.1.0
Oracle Database Java Packages 11.1.0.6.0 1 10.2.0.1.0 10.2.0.1.0
Oracle Expression Filter 11.1.0.6.0 1 30-MAY-11 10.2.0.1.0
Oracle Data Mining 11.1.0.6.0 1 30-MAY-11 10.2.0.1.0
Oracle Text 11.1.0.6.0 1 30-MAY-11 10.2.0.1.0 10.2.0.1.0
Oracle XML Database 11.1.0.6.0 1 30-MAY-11 10.2.0.1.0 10.2.0.1.0
Oracle Rules Manager 11.1.0.6.0 1 30-MAY-11 10.2.0.1.0
Oracle Multimedia 11.1.0.6.0 1 30-MAY-11 10.2.0.1.0
OLAP Analytic Workspace 11.1.0.6.0 1 30-MAY-11 10.2.0.1.0
Oracle OLAP API 11.1.0.6.0 1 30-MAY-11 11.1.0.6.0 10.2.0.1.0
OLAP Catalog 11.1.0.6.0 1 30-MAY-11 10.2.0.1.0 10.2.0.1.0
Spatial 11.1.0.6.0 1 30-MAY-11 10.2.0.1.0 10.2.0.1.0
Oracle Enterprise Manager 11.1.0.6.0 1 30-MAY-11 10.2.0.1.0
17 rows selected.
-------------Upgrade process completed here-------------------------
Downgrade to 10g from 11g.
Steps:-
1) shutdown the database which been upgrade to 11g.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
2) Startup the database in downgrade mode.
SQL> startup downgrade;
ORACLE instance started.
Total System Global Area 355717120 bytes
Fixed Size 1299876 bytes
Variable Size 150997596 bytes
Database Buffers 197132288 bytes
Redo Buffers 6287360 bytes
Database mounted.
Database opened.
3) Drop the SYSMAN user inorder to drop EM config errors.
SQL> drop user sysman cascasde;
4) Run the catalog downgrade script.
SQL> @?/rdbms/admin/catdwngrd.sql
5) Shutdown the database.
6) Modify the parameter files and remove the 11g parameters and include the 10g parameters.
eg:-
Add :- background_dump_dest, user_dump_dest,core_dump_dest
Remove :- diagnostic_dest
7) Set the Oracle_home environment variables pointing to 10g home.
8) Startup the database in upgrade mode using the modified pfile and run the catalog reload script.
$ sqlplus / as sysdba
SQL> create spfile from pfile = '/u01/upg211g';
sql> startup upgrade;
sql> @?/rdbms/admin/catrelod.sql;
sql> shutdown immediate;
9) Startup the database in normal mode and check the version and status.
End of downgrade steps.
==================================================
To upgrade in Windows environment
1) Stop the oracle service which runs in the 10g home
> net stop oracleservicename
2) Delete the oracle service
> oradim -delete -sid oraclesidname
3) Create new oracle service in 11g home
> oradim -new -sid oraclesidname -pfile pfile_path
4) Set your oracle home and oracle sid environment variables pointing to the
11g home and startup database in upgrade mode.
> set oracle_home =
> set oracle_sid =
> sqlplus / as sysdba
sql> startup upgrade;
sql> @?\RDBMS\ADMIN\catupgrd.sql;
once upgrade is completed upgrade the timezone file if required.
5) Relocate the listener from 10g home to 11g home
> net stop oraclelistenerservicename
usage of sc -
sc is a command tool program to interact with the service
control manage or services .
Delete the listener service running in 10g home
> sc delete oraclelistenername
Create a new listener service in 11g home
> sc create oraclelistnername binPath= "C:\oracle11\bin\tnslsnr.exe"
================================================================
Point to remember:-
While upgrade from 10.2.0.4 to 11.2.0.1 while running the utlu112i.sql script i got the error as below.
SQL> @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/utlu112i.sql;
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 3652
On googling i have found a link
http://davidalejomarcos.wordpress.com/2010/05/07/ora-06512-executing-utlu112i-sql/
while states to disable the flashback option. Go through the link for more info.
Pls check the below link for 11.2 upgrade.
http://oracleflash.com/33/Oracle-11g-Release-2-Pre-Upgrade-tool-utlu112i-sql.html
No comments:
Post a Comment