SQL> column COMP_ID format a20
SQL> column COMP_NAME format a40
SQL> column version format a15
SQL> column status format a15
SQL> select COMP_ID,COMP_NAME,VERSION,status from dba_registry;
COMP_ID COMP_NAME VERSION STATUS
-------------------- ---------------------------------------- --------------- ------------
CATALOG Oracle9i Catalog Views 9.2.0.6.0 VALID
CATPROC Oracle9i Packages and Types 9.2.0.6.0 VALID
SQL>
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for Solaris: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 – Production
Step 2- Log in to the system as the owner of the ORACLE_HOME directory of the
new Oracle Database 10g release and copy the file
ORACLE_HOME/rdbms/admin/utlu101i.sql
bash-2.05$ sqlplus
SQL*Plus: Release 9.2.0.6.0 - Production on Tue Nov 6 11:43:07 2007
PS1=[$PWD]
export ORACLE_SID=PRODCOPY
export ORACLE_HOME=/oracle/product/9.2.0
export LD_LIBRARY_PATH=/oracle/product/9.2.0/lib32
export LD_LIBRARY_PATH_64=/oracle/product/9.2.0/lib
SQL> spool /tmp/utlu101i.txt
SQL> @/tmp/utlu101i.sql
Oracle Database 10.1 Upgrade Information Tool 11-06-2007 11:56:35
.
*************************************************************************
Database:
---------
--> name: PRODCOPY
--> version: 9.2.0.6.0
--> compatibility: 9.2.0
.
*************************************************************************
Logfiles: [make adjustments in the current environment]
----------------------------------------------------
-- The existing log files are adequate. No changes are required.
.
*************************************************************************
Tablespaces: [make adjustments in the current environment]
----------------------------------------------------------
--> SYSTEM tablespace is adequate for the upgrade.
.... owner: SYS
.... minimum required size: 1323 MB
.
*************************************************************************
Options: [present in existing database]
---------------------------------------
--> Partitioning
WARNING: Listed option(s) must be installed with Oracle Database 10.1
.
*************************************************************************
Update Parameters: [Update Oracle Database 10.1 init.ora or spfile]
-------------------------------------------------------------------
WARNING: --> "shared_pool_size" needs to be increased to at least "150944944"
WARNING: --> "pga_aggregate_target" needs to be increased to at least "25165824"WARNING: --> "large_pool_size" needs to be increased to at least "8388608"
WARNING: --> "java_pool_size" needs to be increased to at least "50331648"
.
*************************************************************************
Deprecated Parameters: [Update Oracle Database 10.1 init.ora or spfile]
-----------------------------------------------------------------------
-- No deprecated parameters found. No changes are required.
.
*************************************************************************
Obsolete Parameters: [Update Oracle Database 10.1 init.ora or spfile]
---------------------------------------------------------------------
-- No obsolete parameters found. No changes are required
.
*************************************************************************
Components: [The following database components will be upgraded or installed]
-----------------------------------------------------------------------------
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
.
*************************************************************************
.
*************************************************************************
SYSAUX Tablespace: [Create tablespace in Oracle Database 10.1 environment]
--------------------------------------------------------------------------
--> New "SYSAUX" tablespace
.... minimum required size for database upgrade: 500 MB
Please create the new SYSAUX Tablespace AFTER the Oracle Database
10.1 server is started and BEFORE you invoke the upgrade script.
.
*************************************************************************
Oracle Database 10g: Changes in Default Behavior
------------------------------------------------
This page describes some of the changes in the behavior of Oracle Database 10g from that of previous releases. In some cases the default values of some parameters have changed. In other cases new behaviors/requirements have been introduced that may affect current scripts or applications. More detailed information is in the documentation.
SQL OPTIMIZER
The Cost Based Optimizer (CBO) is now enabled by default.
* Rule-based optimization is not supported in 10g (setting
OPTIMIZER_MODE to RULE or CHOOSE is not supported). See Chapter
12, "Introduction to the Optimizer," in Oracle Database Performance Tuning Guide.
* Collection of optimizer statistics is now performed by default, automatically for all schemas (including SYS), for pre-existing databases upgraded to 10g, and for newly created 10g databases. Gathering optimizer statistics on stale objects is scheduled by
default to occur daily during the maintenance window. See Chapter 15, "Managing Optimizer Statistics" in Oracle Performance Tuning Guide. * See the Oracle Database Upgrade Guide for changes in behavior for the COMPUTE STATISTICS clause of CREATE INDEX, and for behavior changes in SKIP_UNUSABLE_INDEXES.
UPGRADE/DOWNGRADE
* After upgrading to 10g, the minimum supported release to downgrade to is Oracle 9i R2 release 9.2.0.3 (or later), and the minimum value for COMPATIBLE is 9.2.0. The only supported downgrade path is for those users who have kept COMPATIBLE=9.2.0
and have an installed 9i R2 (release 9.2.0.3 or later) executable. Users upgrading to 10g from prior releases (such as Oracle 8, Oracle 8i or 9iR1) cannot downgrade to 9i R2 unless they first install 9i R2. When upgrading to10g, by default the database will remain at 9i R2 file format compatibility, so the on disk structures that 10g writes are compatible with 9i R2 structures; this makes it possible to downgrade to 9i R2. Once file format compatibility has been explicitly advanced to 10g (using COMPATIBLE=10.x.x), it is no longer possible to downgrade. See the Oracle Database Upgrade Guide. * A SYSAUX tablespace is created upon upgrade to 10g. The SYSAUX tablespace serves as an auxiliary tablespace to the SYSTEM tablespace. Because it is the default tablespace for many Oracle features and products that previously required their own tablespaces, it reduces the number of tablespaces required by Oracle that you, as a DBA, must maintain.
MANAGEABILITY
* Database performance statistics are now collected by the Automatic Workload Repository (AWR) database component, automatically upon upgrade to 10g and also for newly created 10g databases. This data is stored in the SYSAUX tablespace, and is
used by the database for automatic generation of performance recommendations. See Chapter 5, "Automatic Performance Statistics" in the Oracle Database Performance Tuning Guide. * If you currently use Statspack for performance data gathering,
see section 1. of the Statspack readme (spdoc.txt in the RDBMS ADMIN directory) for directions on using Statspack in 10g to avoid conflict with the AWR.
MEMORY
Automatic PGA Memory Management is now enabled by default (unless PGA_AGGREGATE_TARGET is explicitly set to 0 or WORKAREA_SIZE_POLICY is explicitly set to MANUAL). PGA_AGGREGATE_TARGET is defaulted to 20% of the SGA size, unless explicitly set. Oracle recommends tuning the value of PGA_AGGREGATE_TARGET after upgrading. See Chapter 14 of the Oracle Database Performance Tuning Guide.
* Previously, the number of SQL cursors cached by PL/SQL was determined by OPEN_CURSORS. In 10g, the number of cursors cached is determined by SESSION_CACHED_CURSORS. See the Oracle Database Reference manual.
* SHARED_POOL_SIZE must increase to include the space needed for shared pool overhead. * The default value of DB_BLOCK_SIZE is operating system specific, but is typically 8KB (was typically 2KB in previous releases).
TRANSACTION/SPACE
* Dropped objects are now moved to the recycle bin, where the space is only reused when it is needed. This allows 'undropping' a table using the FLASHBACK DROP feature. See Chapter 14 of the Oracle Database Administrator's Guide. * Auto tuning undo retention is on by default. For more information, see Chapter 10, "Managing the Undo Tablespace," in the Oracle Database Administrator's Guide.
CREATE DATABASE
* In addition to the SYSTEM tablespace, a SYSAUX tablespace is always created at database creation, and upon upgrade to 10g. The SYSAUX tablespace serves as an auxiliary tablespace to the SYSTEM tablespace. Because it is the default tablespace for many Oracle features and products that previously required their own tablespaces, it reduces the number of tablespaces required by Oracle that you, as a DBA, must maintain. See Chapter 2, "Creating a Database," in the Oracle Database Administrator's
Guide.
* In 10g, by default all new databases are created with 10g file format compatibility. This means you can immediately use all the 10g features. Once a database uses 10g compatible file formats, it is not possible to downgrade this database to prior releases.
Minimum and default logfile sizes are larger. Minimum is now 4 MB, default is 50MB, unless you are using Oracle Managed Files (OMF) when it is 100 MB.
PL/SQL procedure successfully completed.
SQL> spool off
Step 3- Upgrade will leave all objects (packages,views,...) invalid, except for tables.
All other objects must be recompiled manually.
List all objects that are not VALID before the upgrade. This list of fatal objects.
Select substr(owner,1,12) owner, substr(object_name,1,30) object, Substr(object_type,1,30) type, status
from dba_objects where status <> 'VALID’;
OWNER OBJECT TYPE STATUS
------------ ------------------------------ ------------------ ---------------
SYSADM PS_REQ_PO_LN_I VIEW INVALID
SYSADM PS_REQ_PO_LN_D VIEW INVALID
SQL>
Step 4- Ensure ORACLE_SID is set to instance you want to upgrade.
Echo $ORACLE_SID
Echo $ORACLE_HOME
Step 5- As of Oracle 9i the National Characterset (NLS_NCHAR_CHARACTERSET
will be limited to UTF8 and AL16UTF16.)
Note 276914.1 The National Character Set in Oracle 9i and 10g
Any other NLS_NCHAR_CHARACTERSET will no longer be supported.
When upgrading to 9i the value of NLS_NCHAR_CHARACTERSET is based
on value currently used in the Oracle8 version.
If the NLS_NCHAR_CHARACTERSET is UTF8 then new it will stay UTF8.
In all other cases the NLS_NCHAR_CHARACTERSET is changed to AL16UTF16
and -if used- N-type data (= data in columns using NCHAR, NVARCHAR2 orNCLOB )
may need to be converted. The change itself is done in step 31 by running the upgrade script.
If you are NOT using N-type columns *for user data* then simply go to step 6.
No further action required.
( so if: select distinct OWNER, TABLE_NAME from DBA_TAB_COLUMNS where
DATA_TYPE in ('NCHAR','NVARCHAR2', 'NCLOB') and OWNER not in
('SYS','SYSTEM'); returns no rows, go to point 6.)
no rows selected
If you have N-type columns *for user data* then check:
SQL> select * from nls_database_parameters where parameter
='NLS_NCHAR_CHARACTERSET';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_NCHAR_CHARACTERSET AL16UTF16
SQL>
If you are using N-type columns AND your National Characterset
is UTF8 or is in the following list:
JA16SJISFIXED , JA16EUCFIXED , JA16DBCSFIXED , ZHT32TRISFIXED
KO16KSC5601FIXED , KO16DBCSFIXED , US16TSTFIXED , ZHS16CGB231280FIXED
ZHS16GBKFIXED , ZHS16DBCSFIXED , ZHT16DBCSFIXED , ZHT16BIG5FIXED
ZHT32EUCFIXED
then also simply go to point 6.
The conversion of the user data itself will then be done in step 38.
If you are using N-type columns AND your National Characterset is NOT
UTF8 or NOT in the following list:
JA16SJISFIXED , JA16EUCFIXED , JA16DBCSFIXED , ZHT32TRISFIXED
KO16KSC5601FIXED , KO16DBCSFIXED , US16TSTFIXED , ZHS16CGB231280FIXED
ZHS16GBKFIXED , ZHS16DBCSFIXED , ZHT16DBCSFIXED , ZHT16BIG5FIXED
ZHT32EUCFIXED
(your current NLS_NCHAR_CHARACTERSET is for example US7ASCII, WE8ISO8859P1, CL8MSWIN1251 ...)
then you have to:
* change the tables to use CHAR, VARCHAR2 or CLOB instead the N-type
or
* use export/import the table(s) containing N-type columns
and truncate those tables before migrating to 9i.
The recommended NLS_LANG during export is simply the NLS_CHARACTERSET,
not the NLS_NCHAR_CHARACTERSET
Step 6- Check for corruption in the dictionary, use the following commands in sqlplus connected as sys:
Set verify off
Set space 0
Set heading off
Set feedback off
Set pages 1000
Spool analyze.sql
Select 'Analyze '||object_type||' '||object_name
||' validate structure;'
from dba_objects
where owner='SYS'
and object_type in ('INDEX','TABLE','CLUSTER');
spool off
This creates a script called analyze.sql.
Run the script.
This script (analyze.sql) should not return any errors.
Step 7 - Ensure that all Snapshot refreshes are successfully completed.
And replication is stopped.
$ Sqlplus SYS/
SQL> Select distinct(trunc(last_refresh)) from dba_snapshot_refresh_times;
no rows selected
SQL>
Step 8- Stop the listener for the database
$ lsnrctl
Lsnrctl> stop
Step 9 - Ensure no files need media recovery:
SQL> Select * from v$recover_file;
no rows selected
SQL>
Step 10 - Ensure no files are in backup mode:
SQL> Select * from v$backup where status!='NOT ACTIVE';
no rows selected
SQL>
This should return no rows.
Step 11- Resolve any outstanding unresolved distributed transaction:
SQL> Select * from dba_2pc_pending;
no rows selected
SQL>
If this returns rows you should do the following:
Select local_tran_id from dba_2pc_pending;
Execute dbms_transaction.purge_lost_db_entry('
Commit;
Step 12 - Disable all batch and cron jobs.
Step 13 - Ensure the users sys and system have 'system' as their default tablespace.
SQL> Select username, default_tablespace from dba_users where username
in ('SYS','SYSTEM');
2
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
SYSTEM SYSTEM
SYS SYSTEM
SQL>
To modify use:
Alter user sys default tablespace SYSTEM;
Alter user system default tablespace SYSTEM;
Step 14 - Optionally ensure the aud$ is in the system tablespace when auditing is enabled.
SQL> Select tablespace_name from dba_tables where table_name='AUD$';
TABLESPACE_NAME
------------------------------
SYSTEM
SQL>
Step 15 - Note down where all control files are located.
QL> Select name from v$controlfile;
NAME
-------------------------------------------------------------------
/oradata/PRODCOPY/ora_control1
/oradata/PRODCOPY/ora_control2
SQL>
Step 15 - Note down all sysdba users.
SQL> Select * from v$pwfile_users;
no rows selected
SQL>
(Its using OS authentication that’s why its no row selected )
If a passwordfile is used copy it to the new location. On unix the default
is $ORACLE_HOME/dbs/orapw
On windows NT this is %ORACLE_HOME%\database\orapw
Step 16 - Shutdown the database
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
Step 17 -Change the init.ora file:
- Make a backup of the init.ora file.
- Comment out obsolete parameters
- Change all deprecated parameters
- Make sure the COMPATIBLE initialization parameter is properly set for
the new Oracle Database 10gR1 release. The Upgrade Information Tool displays
a warning in the Database section if COMPATIBLE is not properly set.
- If you have set the parameter NLS_LENGTH_SEMANTICS to CHAR, change the value
to BYTE during the upgrade.
- Verify that the parameter DB_DOMAIN is set properly.
- Make sure the PGA_AGGREGATE_TARGET initialization parameter is set to
at least 24 MB.
- Make sure the JAVA_POOL_SIZE initialization parameter is set to at least 48 MB.
- Ensure there is a value for DB_BLOCK_SIZE
- Comment out the JOB_QUEUE_PROCESSES parameter, put in a new and set this
explicitly to zero, during the upgrade
- Comment out the AQ_TM_PROCESSES parameter, put in a new and set this
explicitly to zero, during the upgrade
- On Windows operating systems, change the BACKGROUND_DUMP_DEST and USER_DUMP_DEST
initialization parameters that point to RDBMS80 or any other environment variable
to point to the following directories instead:
BACKGROUND_DUMP_DEST to ORACLE_BASE\oradata\DB_NAME
and USER_DUMP_DEST to ORACLE_BASE\oradata\DB_NAME\archive
- Make sure all path names in the parameter file are fully specified.
You should not have relative path names in the parameter file.
- If you are using a cluster database, set the parameter CLUSTER_DATABASE=FALSE
during the upgrade.
- If you are upgrading a cluster database, then modify the initdb_name.ora
file in the same way that you modified the parameter file.
- check MAX_ENABLED_ROLES parameter value. Increase it if the number of roles
in the Database is close to its value. When upgrading to higher versions,
new roles might be added . If the Database already contains high number of
roles, upgrade scripts can fail with error ORA-01925 as indicated in
Note 261632.1
- Ensure that the shared_pool_size and the large_pool_size are at least 150Mb
- Ensure that PLSQL_V2_COMPATIBILITY is unset for the upgrade, otherwise this
could cause problems during a later stage. See Note 170282.1
Step 18 -Check for adequate freespace on archive log destination file systems.
Step 19 - nsure the NLS_LANG variable is set correctly:
$ echo $NLS_LANG
Step 20 - If needed copy the listener.ora and the tnsnames.ora to the new location
(when no TNS_ADMIN env. Parameter is used)
cp $ORACLE_HOME/network/admin
If your Operating system is Windows NT, delete your services
With the ORADIM of your old oracle version.
Stop the OracleServiceSID Oracle service of the database you are upgrading,
where SID is the instance name. For example, if your SID is ORCL, then enter
the following at a command prompt:
C:\> NET STOP OracleServiceORCL
For Oracle 8.0 this is:
C:\ORADIM80 -DELETE -SID
For Oracle 8i or higher this is:
C:\ORADIM -DELETE -SID
And create the new Oracle Database 10g service at a command prompt using the
ORADIM command of the new Oracle Database release:
C:\> ORADIM -NEW -SID SID -INTPWD PASSWORD -MAXUSERS USERS
-STARTMODE AUTO -PFILE ORACLE_HOME\DATABASE\INITSID.ORA
Step 21 -Copy configuration files from the ORACLE_HOME of the database being upgraded to the new Oracle Database 10gR1 ORACLE_HOME:
If your parameter file resides within the old environment's ORACLE_HOME,
then copy it to the new ORACLE_HOME. By default, Oracle looks for the parameter
file in ORACLE_HOME/dbs on UNIX platforms and in ORACLE_HOME\database on
Windows operating systems. The parameter file can reside anywhere you wish,
but it should not reside in the old environment's ORACLE_HOME after you
upgrade to Oracle Database 10gR1.
If your parameter file is a text-based initialization parameter file with
either an IFILE (include file) or a SPFILE (server parameter file) entry,
and the file specified in the IFILE or SPFILE entry resides within the old
environment's ORACLE_HOME, then copy the file specified by the IFILE or
SPFILE entry to the new ORACLE_HOME. The file specified in the IFILE or SPFILE
entry contains additional initialization parameters.
If you have a password file that resides within the old environments
ORACLE_HOME, then move or copy the password file to the new Oracle Database
10g ORACLE_HOME.
The name and location of the password file are operating system-specific.
On UNIX platforms, the default password file is ORACLE_HOME/dbs/orapwsid.
On Windows operating systems, the default password file is
ORACLE_HOME\database\pwdsid.ora. In both cases, sid is your Oracle instance ID.
If you are upgrading a cluster database and your initdb_name.ora file resides
within the old environment's ORACLE_HOME, then move or copy the initdb_name.ora
file to the new ORACLE_HOME.
Note:
If you are upgrading a cluster database, then perform this step on all nodes
in which this cluster database has instances configured.
Step 23 - Update the oratab entry, to set the new ORACLE_HOME and disable automatic startup:
Step 24 - Update the environment variables like ORACLE_HOME and PATH
$ . oraenv
step 25 - Make sure the following environment variables point to the new Release directories:
- ORACLE_HOME
- PATH
- ORA_NLS10
- ORACLE_BASE
- LD_LIBRARY_PATH
- ORACLE_PATH
Step 26 - PERFORM a Full cold backup!!!!!!!
You can either do this by manually copying the files or sign on to RMAN:
Step 27 - Startup upgrade the database:
[/home/oracle]sqlplus "/ as sysdba"
SQL*Plus: Release 10.1.0.2.0 - Production on Tue Nov 6 13:45:56 2007
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 1048576000 bytes
Fixed Size 1305920 bytes
Variable Size 996676288 bytes
Database Buffers 50331648 bytes
Redo Buffers 262144 bytes
Database mounted.
Database opened.
SQL>
.
Step 28 – The following SQL statement would create a 500 MB SYSAUX tablespace For the database
SQL>
CREATE TABLESPACE sysaux DATAFILE '/oradata/PRODCOPY/sysaux01.dbf'
SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;
Tablespace created.
Step 29 - Spool the output so you can take a look at possible errors after the pgrade:
Run the appropriate script for your version.
From To: Only Script to Run
==== === ==================
7.x.x 10.1 Not supported
8.0.5 10.1 Not supported
8.0.6 10.1 u0800060.sql
8.1.5 10.1 Not Supported
8.1.6 10.1 Not Supported
8.1.7 10.1 u0801070.sql
9.0.1 10.1 u0900010.sql
9.2 10.1 u0902000.sql
SQL> Spool Upgrade.log
SQL>@?/rdbms/admin/u0902000.sql
- - - -- - --
- - - -- - -
-
1 row selected.
1 row selected.
TIMESTAMP
--------------------------------------------------------------------------------
1 row selected.
PL/SQL procedure successfully completed.
COMP_ID COMP_NAME STATUS VERSION
---------- ----------------------------------- ----------- ----------
CATALOG Oracle Database Catalog Views VALID 10.1.0.2.0
CATPROC Oracle Database Packages and Types VALID 10.1.0.2.0
2 rows selected.
DOC>##################################################################
DOC>##################################################################
DOC>
DOC> The above query lists the SERVER components in the upgraded
DOC> database, along with their current version and status.
DOC>
DOC> Please review the status and version columns and look for
DOC> any errors in the spool log file. If there are errors in the spool
DOC> file, or any components are not VALID or not the current version,
DOC> consult the Oracle Database Upgrade Guide for troubleshooting
DOC> recommendations.
DOC>
DOC> Next shutdown immediate, restart for normal operation, and then
DOC> run utlrp.sql to recompile any invalid application objects.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
PL/SQL procedure successfully completed.
COMP_ID COMP_NAME STATUS VERSION
---------- ----------------------------------- ----------- ----------
CATALOG Oracle Database Catalog Views VALID 10.1.0.2.0
CATPROC Oracle Database Packages and Types VALID 10.1.0.2.0
2 rows selected.
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC> The above query lists the SERVER components in the upgraded
DOC> database, along with their current version and status.
DOC>
DOC> Please review the status and version columns and look for
DOC> any errors in the spool log file. If there are errors in the spool
DOC> file, or any components are not VALID or not the current version,
DOC> consult the Oracle Database Upgrade Guide for troubleshooting
DOC> recommendations.
DOC>
DOC> Next shutdown immediate, restart for normal operation, and then
DOC> run utlrp.sql to recompile any invalid application objects.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP DBUPG_END 2007-11-06 15:38:16
1 row selected.
SQL>
Step 30 - Run utlu101s.sql, specifying the TEXT option
SQL> @?/rdbms/admin/utlu101s.sql
PL/SQL procedure successfully completed.
Enter value for 1: TEXT
Oracle Database 10.1 Upgrade Status Tool 06-NOV-2007 03:46:27
--> Oracle Database Catalog Views Normal successful completion
--> Oracle Database Packages and Types Normal successful completion
No problems detected during upgrade
PL/SQL procedure successfully completed.
SQL>
Step 31- Restart the database:
(DO NOT USE SHUTDOWN ABORT!!!!!!!!!)
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup restrict
ORACLE instance started.
Total System Global Area 1048576000 bytes
Fixed Size 1305920 bytes
Variable Size 996676288 bytes
Database Buffers 50331648 bytes
Redo Buffers 262144 bytes
Database mounted.
Database opened.
Executing this clean shutdown flushes all caches, clears buffers and performs
other database housekeeping tasks. Which is needed if you want to upgrade
specific components.
Step 32 - Run script to recompile invalid pl/sql modules:
SQL> @utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2007-11-06 15:50:12
1 row selected.
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2007-11-06 16:16:43
1 row selected.
PL/SQL procedure successfully completed.
If there are still objects which are not valid after running the script run
the following:
spool invalid_post.lst
Select substr(owner,1,12) owner,
Substr(object_name,1,30) object,
Substr(object_type,1,30) type, status from
dba_objects where status <>'VALID';
spool off
OWNER OBJECT TYPE STATUS
------------ ------------------------------ ------------------- -------
SYSADM PS_REQ_PO_LN_I VIEW INVALID
SYSADM PS_REQ_PO_LN_D VIEW INVALID
Now compare the invalid objects in the file 'invalid_post.lst' with the invalid
objects in the file 'invalid_pre.lst' you create in step 5.
There should be no dictionary objects invalid.
Step 33- Shutdown the database and startup the database.
$ sqlplus /nolog
SQL> Connect sys/passwd_for_sys as sysdba
SQL> Shutdown
SQL> Startup restrict
Step 34 – Below is not applicable in my case
A) IF you are NOT using N-type columns for *user* data:
select distinct OWNER, TABLE_NAME from DBA_TAB_COLUMNS where
DATA_TYPE in ('NCHAR','NVARCHAR2', 'NCLOB') and OWNER not in
('SYS','SYSTEM');
did not return rows in point 8 of this note.
then simply:
$ sqlplus /nolog
SQL> connect sys/passwd_for_sys as sysdba
SQL> shutdown immediate
B) IF your version 8 NLS_NCHAR_CHARACTERSET was UTF8:
you can look up your previous NLS_NCHAR_CHARACTERSET using this select:
select * from nls_database_parameters where parameter ='NLS_SAVED_NCHAR_CS';
then simply:
$ sqlplus /nolog
SQL> connect sys/passwd_for_sys as sysdba
SQL> shutdown immediate
C) IF you are using N-type columns for *user* data *AND*
your previous NLS_NCHAR_CHARACTERSET was in the following list:
JA16SJISFIXED , JA16EUCFIXED , JA16DBCSFIXED , ZHT32TRISFIXED
KO16KSC5601FIXED , KO16DBCSFIXED , US16TSTFIXED , ZHS16CGB231280FIXED
ZHS16GBKFIXED , ZHS16DBCSFIXED , ZHT16DBCSFIXED , ZHT16BIG5FIXED
ZHT32EUCFIXED
then the N-type columns *data* need to be converted to AL16UTF16:
To upgrade user tables with N-type columns to AL16UTF16 run the
script utlnchar.sql:
$ sqlplus /nolog
SQL> connect sys/passwd_for_sys as sysdba
SQL> @utlnchar.sql
SQL> shutdown immediate
D) IF you are using N-type columns for *user* data *AND *
your previous NLS_NCHAR_CHARACTERSET was *NOT* in the following list:
JA16SJISFIXED , JA16EUCFIXED , JA16DBCSFIXED , ZHT32TRISFIXED
KO16KSC5601FIXED , KO16DBCSFIXED , US16TSTFIXED , ZHS16CGB231280FIXED
ZHS16GBKFIXED , ZHS16DBCSFIXED , ZHT16DBCSFIXED , ZHT16BIG5FIXED
ZHT32EUCFIXED
then import the data exported in point 8 of this note.
The recommended NLS_LANG during import is simply the NLS_CHARACTERSET,
not the NLS_NCHAR_CHARACTERSET
After the import:
$ sqlplus /nolog
SQL> connect sys/passwd_for_sys as sysdba
SQL> shutdown immediate
Step 35 - Modify the listener.ora file:
For the upgraded intstance(s) modify the ORACLE_HOME parameter
to point to the new ORACLE_HOME.
Step 36 - Start the listener
$ lsnrctl
LSNRCTL> start
Step 37 - Enable cron and batch jobs
Change oratab entry to use automatic startup
SID:ORACLE_HOME:Y
Appendix A: Deprecated parameters in release 10gR1:
------------------------------------------------
BUFFER_POOL_KEEP (replaced by DB_KEEP_CACHE_SIZE)
BUFFER_POOL_RECYCLE (replaced by DB_RECYCLE_CACHE_SIZE)
GLOBAL_CONTEXT_POOL_SIZE
LOCK_NAME_SPACE
LOG_ARCHIVE_START
MAX_ENABLED_ROLES
PARALLEL_AUTOMATIC_TUNING
PLSQL_COMPILER_FLAGS (replaced by PLSQL_CODE_TYPE and PLSQL_DEBUG)
DRS_START (replaced by DG_BROKER_START)
FAST_START_IO_TARGET (replaced by FAST_START_MTTR_TARGET)
MTS_CIRCUITS (replaced by CIRCUITS)
MTS_DISPATCHERS (replaced by DISPATCHERS)
MTS_MAX_DISPATCHERS (replaced by MAX_DISPATCHERS)
MTS_MAX_SERVERS (replaced by MAX_SHARED_SERVERS)
MTS_SERVERS (replaced by SHARED_SERVERS)
MTS_SESSIONS (replaced by SHARED_SERVER_SESSIONS)
PARALLEL_SERVER (replaced by CLUSTER_DATABASE)
PARALLEL_SERVER_INSTANCES (replaced by CLUSTER_DATABASE_INSTANCES)
Appendix B: Obsolete parameters in 10gR1:
---------------------------------------
DBLINK_ENCRYPT_LOGIN
HASH_JOIN_ENABLED
LOG_PARALLELISM
MAX_ROLLBACK_SEGMENTS
MTS_CIRCUITS
MTS_DISPATCHERS
MTS_LISTENER_ADDRESS
MTS_MAX_DISPATCHERS
MTS_MAX_SERVERS
MTS_MULTIPLE_LISTENERS
MTS_SERVERS
MTS_SERVICE
MTS_SESSIONS
OPTIMIZER_MAX_PERMUTATIONS
ORACLE_TRACE_COLLECTION_NAME
ORACLE_TRACE_COLLECTION_PATH
ORACLE_TRACE_COLLECTION_SIZE
ORACLE_TRACE_ENABLE
ORACLE_TRACE_FACILITY_NAME
ORACLE_TRACE_FACILITY_PATH
PARTITION_VIEW_ENABLED
PLSQL_NATIVE_C_COMPILER
PLSQL_NATIVE_LINKER
PLSQL_NATIVE_MAKE_FILE_NAME
PLSQL_NATIVE_MAKE_UTILITY
ROW_LOCKING
SERIALIZABLE
TRANSACTION_AUDITING
UNDO_SUPPRESS_ERRORS
DISTRIBUTED_TRANSACTIONS
MAX_TRANSACTION_BRANCHES
PARALLEL_BROADCAST_ENABLED
STANDBY_PRESERVES_NAMES
ALWAYS_ANTI_JOIN
ALWAYS_SEMI_JOIN
DB_BLOCK_LRU_LATCHES
DB_BLOCK_MAX_DIRTY_TARGET
DB_FILE_DIRECT_IO_COUNT
GC_DEFER_TIME
GC_RELEASABLE_LOCKS
GC_ROLLBACK_LOCKS
HASH_MULTIBLOCK_IO_COUNT
INSTANCE_NODESET
JOB_QUEUE_INTERVAL
OPS_INTERCONNECTS
OPTIMIZER_PERCENT_PARALLEL
SORT_MULTIBLOCK_READ_COUNT
TEXT_ENABLE
DB_BLOCK_BUFFERS
Reference –
Complete checklist for manual upgrades to 10gR2
Metalink Note: 316889.1
Complete checklist for manual upgrades to 10gR1 (10.1.0.x)
Metalink Note: 263809.1
