Upgrade From 10g to 11gR2
1. BACKUP
BINARIES, DB,TNSNAMES,LISTENER,INIT,SPFILE,PWFILE THAT ARE BEING UPGRADED
2. COMPATIBLE
PARAMETER MUST BE SET TO MINIMUM 10.0.0.0. THE RECOMMENDED IS 11.2.0
FOR 11gR2 PARAMETER FILE.
Part A.
Install the New Oracle Database Software & Apply any patches
necessary.
Part B.
Run the Pre-Upgrade Information Tool (You Must Run This
tool)
1. Copy
the Pre-Upgrade Information Tool (utlul12i.sql) from
the Oracle Database 11gR2 directory: $ORACLE_HOME/rdbms/admin to a temporary
directory /tmp.
2. Set
your environment to the one that is being upgraded. Assuming 10g.
3. Change
directory to /tmp that you copied utlu112i.sql to
in Step 1.
4. Start
SQL*Plus and login as ‘/ as sysdba’
5. Spool
the results to a log file:
o SQL>
SPOOL upgrade_info.log
6. Run
the Pre-Upgrade Information Tool:
o SQL>
@utlul12i.sql
·
SQL> SPOOL OFF
Check
the output of the Pre-Upgrade Information Tool in upgrade_info.log.
The
following is an example of the output generated by the Pre-Upgrade Information
Tool:
|
Oracle Database 11.2
Pre-Upgrade Information Tool 10-14-2008 23:25:25
.
**********************************************************************
Database:
**********************************************************************
--> name: ORCL
--> version:
10.2.0.2.0
--> compatible:
10.2.0.2
--> blocksize:
8192
Run the Pre-Upgrade
Information Tool
Upgrading to the New
Release 3-9
--> platform:
Linux IA (32-bit)
--> timezone
file: V2
.
**********************************************************************
Tablespaces: [make
adjustments in the current environment]
**********************************************************************
WARNING: -->
SYSTEM tablespace is not large enough for the upgrade.
.... currently
allocated size: 560 MB
.... minimum
required size: 910 MB
.... increase
current size by: 350 MB
.... tablespace is
NOT AUTOEXTEND ENABLED.
|
||||
|
--> UNDOTBS1
tablespace is adequate for the upgrade.
.... minimum
required size: 457 MB
.... AUTOEXTEND
additional space required: 352 MB
--> SYSAUX
tablespace is adequate for the upgrade.
.... minimum
required size: 617 MB
.... AUTOEXTEND
additional space required: 287 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.2 init.ora or spfile]
**********************************************************************
WARNING: -->
"sga_target" needs to be increased to at least 388 MB
.
**********************************************************************
Renamed Parameters:
[Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
WARNING: -->
"plsql_compiler_flags" old value was "INTERPRETED";
new name is
"plsql_code_type" new value is "INTERPRETED"
.
**********************************************************************
Obsolete/Deprecated
Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-->
"max_enabled_roles"
--> "remote_os_authent"
-->
"background_dump_dest" replaced by "diagnostic_dest"
-->
"user_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
--> Messaging
Gateway [upgrade] VALID
--> OLAP Analytic
Workspace [upgrade] VALID
--> OLAP Catalog
[upgrade] VALID
--> Oracle Label
Security [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
Run the Pre-Upgrade
Information Tool
3-10 Oracle Database Upgrade Guide
--> Data Mining
[upgrade] VALID
--> Expression
Filter [upgrade] VALID
--> Rule Manager
[upgrade] VALID
--> Oracle
Application Express [upgrade]
--> Oracle OLAP
API [upgrade] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: -->
Database is using a timezone file older than version 11.
.... After the
release migration, it is recommended that DBMS_DST package
.... be used to
upgrade the 10.2.0.2.0 database timezone version
.... to the latest
version which comes with the new release.
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
.... WMSYS
.... CTXSYS
WARNING: -->
Database contains INVALID objects prior to upgrade.
.... The list of
invalid SYS/SYSTEM objects was written to
....
registry$sys_inv_objs.
.... The list of
non-SYS/SYSTEM objects was written to
....
registry$nonsys_inv_objs.
.... Use
utluiobj.sql after the upgrade to identify any new invalid
.... objects due to
the upgrade.
.... USER PUBLIC has
7 INVALID objects.
.... USER
FLOWS_010600 has 1 INVALID objects.
.... USER SYS has 1
INVALID objects.
WARNING: -->
Database contains schemas with objects dependent on network packages.
.... Refer to the
11g Upgrade Guide for instructions to configure Network ACLs.
.... USER WKSYS has
dependent objects.
.... USER SYSMAN has
dependent objects.
.... USER
FLOWS_010600 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.
.
WARNING: -->
There are materialized view refreshes in progress.
.... Ensure all
materialized view refreshes are complete prior to upgrade.
WARNING: -->
There are files which need media recovery.
.... Ensure no files
need media recovery prior to upgrade.
WARNING: -->
There are files in backup mode.
.... Ensure no files
are in backup mode prior to upgrade.
WARNING:--> There
are outstanding unresolved distributed transactions.
.... Resolve
outstanding distributed transactions prior to upgrade.
WARNING:--> A
standby database exists.
.... Sync standby
database prior to upgrade.
WARNING: -->
log_archive_format must be updated.
.... As of 10.1,
log_archive_format requires a %r format qualifier
.... be present in
its format string. Your current setting is:
....
log_archive_format='%t_%s.dbf'.
.... Archive Logging
is currently OFF, but failure to add the %r to the
.... format string
will still prevent the upgraded database from starting up.
WARNING:-->
recycle bin in use.
.... Your recycle
bin is turned on and it contains
.... 3 object(s). It
is REQUIRED
.... that the
recycle bin is empty prior to upgrading
.... your database.
Run the Pre-Upgrade
Information Tool
Upgrading to the New
Release 3-11
.... The command:
PURGE DBA_RECYCLEBIN
1. .... must be executed immediately prior to
executing your upgrade.
|
||||
Check the
warning messages and rectify them as needed. The following
points must be addressed correctly otherwise there maybe problems.
Example: CONNECT
role now only has create session privilege.
If you
have any db links with passwords, you may want to back up the sys.link$ table
to save the information.
Oracle
recommends gathering stats before the upgrade: EXEC
DBMS_STATS.GATHER_DICTIONARY_STATS;
■ Deprecated CONNECT Role
■ Access Control to Network
Utility Packages
■ Database Links with
passwords
■ TIMESTAMP WITH TIME ZONE
Data Type
■ Optimizer Statistics
■ Invalid Objects
■ Save Oracle Enterprise
Manager Database Control Data
■ Complete Materialized View
Refreshes
■ Ensure No Files Need Media
Recovery
■ Ensure No Files Are in
Backup Mode
■ Resolve Outstanding
Distributed Transactions
■ Sync Standby Database with
the Primary Database
■ Purging the Database Recycle Bin
Part C.
Prepare the new
11gR2 ORACLE_HOME
1. Copy
configuration files (init file, tnsnames, listener, pwfile) from old Oracle
Home to new.
2. Update
init files with new COMPATIBLE parameter, fix any deprecated ones, and adjust
the values to at least the minimum values indicated by the Pre-Upgrade Tool.
3. Update
any relative path names in parameter file to fully path names.
Part D.
Upgrade the Database
1. Shutdown
the database:
o SQL>
SHUTDOWN IMMEDIATE;
2. Make
sure the following checks:
o The
oratab file points to Oracle Database 11g Release 2 Oracle Home
o The
following environment variables point to the Oracle 11g Release 2 directories:
§ ORACLE_HOME
§ PATH
3. Change
to the $ORACLE_HOME/rdbms/admin directory and start SQL*Plus
o sqlplus
‘/ as sysdba’
4. Start
the instance by issuing following command (you may get messages that parameters
are obsolete, fix those and start up the db again):
o SQL> STARTUP
UPGRADE;
o SQL> SPOOL
upgrade.log
5. Run
the catupgrd.sql script:
o SQL> @catupgrd.sql
6. Once
completed, shutdown the database and restart it.
o SQL> SHUTDOWN
IMMEDIATE;
o SQL> STARTUP;
7. Run
the Post-Upgrade Status Tool to provide a summary of the upgrade. (If there are
any INVALID components, then check upgrade manual for fixes.)
o SQL>
@utlul12s.sql
8. Run
catuppst.sql to perform upgrade actions that do not require db to be in upgrade
mode:
o SQL>
$ORACLE_HOME/rdbms/admin/catuppst.sql
9. Run
utlrp to recompile any remaining stored PL/SQL and other objects.
o SQL>
$ORACLE_HOME/rdbms/admin/utlrp.sql
10. Verify that
all objects are valid:
o SQL>
SELECT count(*) FROM dba_invalid_objects;
o SQL>
SELECT distinct object_name FROM dba_invalid_objects;
No comments:
Post a Comment