Complete checklist for manual upgrades to 10gR1 (10.1.0.x) [263809.1]

PURPOSE
-------
This document is created for use as a guideline and checklist when
manually upgrading Oracle 8, Oracle 8i or Oracle 9i to Oracle 10gR1
(10.1.0.x)

SCOPE & APPLICATION
-------------------
Database administrators

UPGRADE CHECKLIST
-----------------
UPGRADING THE DATABASE
----------------------

1. --------------------------------------------------------------------------
Upgrade path for Oracle 7:

If your old release version is 7.3.4 or less, you must first upgrade to Oracle 8.0.6 or Oracle 8.1.7.  After the upgrade to 8.0.6 or 8.1.7 is done you can directly upgrade your database to 10gR1.

Upgrade path for Oracle 8.0:
If your old release version is 8.0.5 or less (i.e. 8.0.4), then direct upgrade is NOT supported.  You must first upgrade this version to 8.0.6.  After the upgrade to 8.0.6 or your version IS 8.0.6 , you can directly upgrade your database to 10gR1.

Upgrade path for Oracle 8i:
If your old release version is 8.1.5 or 8.1.6, then direct upgrade to Oracle 10g is NOT supported.  You must first upgrade this version to 8.1.7. After the upgrade to 8.1.7 or your version IS 8.1.7, you can directly upgrade your database to Oracle 10gR1.

Upgrade path for Oracle 9i:
Direct upgrade is supported.

If your old release is 8.0.5 or less, look at Note 133920.1 for manual upgrading the database to 8.0.6 or higher.  You can also use this note to first upgrade your database from Oracle 8.1.5 or Oracle 8.1.6 to Oracle 8.1.7.

What version is running? What option is installed?
Select * from v$version;
Select * from v$option;

2. --------------------------------------------------------------------------
When upgrading to Oracle Database 10gR1, optimizer statistics will be collected for dictionary tables that lack statistics. This statistics collection could be time consuming for databases with a large number of dictionary tables, but it will only occur for those tables that lack statistics or are significantly changed during the upgrade.

For databases that are upgraded from Oracle9i, it is possible to decrease the downtime during the upgrade by collecting statistics for the dictionary prior to the upgrade. The following two scripts collect statistics for dictionary objects in Oracle9i.

This process should be tested on a test database just like any other aspect of the upgrade. Also, some schemas referenced in these scripts may not exist if some database components have not been installed.

This script collect stats for system component schemas.

The stats collection may give error if a particular component schema does not exist in the database. This can happen if a component is not installed or if it is invalid.

This script must be run connected AS SYSDBA using SQL*Plus:

Sqlplus /nolog
SQL> Connect sys/passwd_for_sys as sysdba
SQL>spool gdict

grant analyze any to sys;

exec dbms_stats.gather_schema_stats('WMSYS',options=>'GATHER',  
 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

exec dbms_stats.gather_schema_stats('MDSYS',options=>'GATHER',
 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

exec dbms_stats.gather_schema_stats('CTXSYS',options=>'GATHER',
 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

exec dbms_stats.gather_schema_stats('XDB',options=>'GATHER',
 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

exec dbms_stats.gather_schema_stats('WKSYS',options=>'GATHER',
 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

exec dbms_stats.gather_schema_stats('LBACSYS',options=>'GATHER',
 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

exec dbms_stats.gather_schema_stats('OLAPSYS',options=>'GATHER',
 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

exec dbms_stats.gather_schema_stats('DMSYS',options=>'GATHER',
 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

exec dbms_stats.gather_schema_stats('ODM',options=>'GATHER',
 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

exec dbms_stats.gather_schema_stats('ORDSYS',options=>'GATHER',
 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

exec dbms_stats.gather_schema_stats('ORDPLUGINS',options=>'GATHER',
 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

exec dbms_stats.gather_schema_stats('SI_INFORMTN_SCHEMA',options=>'GATHER',
 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

exec dbms_stats.gather_schema_stats('OUTLN',options=>'GATHER',
 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

exec dbms_stats.gather_schema_stats('DBSNMP',options=>'GATHER',
 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

exec dbms_stats.gather_schema_stats('SYSTEM',options=>'GATHER',
 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

exec dbms_stats.gather_schema_stats('SYS',options=>'GATHER',
 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

spool off

This script creates the stats table, 'dictstattab' and exports the stats for the RDBMS component schemas into it.

The export will give error if a particular component schema does not exist in the database. This can happen if a component is not installed or if it is invalid.

This will be useful incase you want to import! the stats back example:
Following stmt import!s the stats for SYS schema after deleting the existing stats.

exec dbms_stats.delete_schema_stats('SYS');
exec dbms_stats.import!_schema_stats('SYS','dictstattab');

This script must be run connected AS SYSDBA using SQL*Plus:
Sqlplus /nolog
SQL> Connect sys/passwd_for_sys as sysdba

spool sdict
grant analyze any to sys;
exec dbms_stats.create_stat_table('SYS','dictstattab');
exec dbms_stats.export_schema_stats('WMSYS','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('MDSYS','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('CTXSYS','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('XDB','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('WKSYS','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('LBACSYS','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('OLAPSYS','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('DMSYS','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('ODM','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('ORDSYS','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('ORDPLUGINS','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('SI_INFORMTN_SCHEMA','dictstattab', statown => 'SYS');
exec dbms_stats.export_schema_stats('OUTLN','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('DBSNMP','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('SYSTEM','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('SYS','dictstattab',statown => 'SYS');
spool off

3. --------------------------------------------------------------------------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 of the new Oracle Database 10gR1 release to a directory outside of the ORACLE_HOME, such as the temporary directory on your system.

Make a note of the new location of this file.

4. --------------------------------------------------------------------------Change to the directory outside of the ORACLE_HOME directory that you copied files to in Step 3.

Start SQL*Plus and connect to the database instance as a user with SYSDBA
privileges. Then run and spool the utlu101i.sql file.

Please note that the database must be running in normal mode in the old release.

The script must be run in the context of the old release and not in the 10gR1 release.

Sqlplus /nolog
SQL> Connect sys/passwd_for_sys as sysdba
SQL> SPOOL info.log
SQL> @utlu101i.sql
SQL> SPOOL OFF

Then, check the spool file and examine the output of the upgrade informationtool. The sections, which follow, describe the output of the Upgrade Information Tool (utlu101i.sql).

Database
This section displays global database information about the current database,

such as the database name, release number, and compatibility level. A warning is displayed if the COMPATIBLE initialization parameter needs to be adjusted before the database is upgraded.

Log files
This section displays a list of redo log files in the current database whose size is less than 4 MB. For each log file, the file name, group number, and recommended size is displayed. New files of at least 4 MB (preferably 10 MB) need to be created in the current database. Any redo log files less than 4 MB must be dropped before the database is upgraded.

Tablespaces
This section displays a list of tablespaces in the current database.
For each tablespace, the tablespace name, owner, and minimum required size is displayed. In addition, a message is displayed if the tablespace is adequate for the upgrade. If the tablespace does not have enough free space, then space must be added to the tablespace in the current database. Tablespace adjustments need to be made before the database is upgraded.

Options
This section displays a list of options in the current database that must be available in the new Oracle Database 10g release before the database is upgraded.

Update Parameters
This section displays a list of initialization parameters in the parameter file of the current database that must be adjusted before the database is upgraded. The adjustments need to be made to the parameter file after it is copied to the new Oracle Database 10gR1 release.

Deprecated Parameters
This section displays a list of initialization parameters in the parameter file of the current database that are deprecated in the new Oracle Database 10gR1 release.

Obsolete Parameters
This section displays a list of initialization parameters in the parameter file of the current database that are obsolete in the new Oracle Database 10gR1 release. Obsolete initialization parameters need to be removed from the parameter file before the database is upgraded.

Components
This section displays a list of database components in the new Oracle
Database 10gR1 release that will be upgraded or installed when the current database is upgraded.

SYSAUX Tablespace
This section displays the minimum required size for the SYSAUX tablespace, which is required in Oracle Database 10gR1. The SYSAUX tablespace must be created after the new Oracle Database 10g release is started and BEFORE the upgrade scripts are invoked.

Note:
Because of Bug 3405535
Check the component status and version in the 9.2 database prior to upgrading  eg; select comp_name, status, version from dba_registry;

5. --------------------------------------------------------------------------
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';        

To create a script to compile all invalid objects, before upgrading, run the script called utlrp.sql in the $ORACLE_HOME/rdbms/admin directory.  This script recompiles all invalid PL/SQL in the database including views.   

 

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus sys/<password for SYS> as sysdba
SQL> @utlrp.sql
 
Run the script and than rerun the query to get invalid objects.     
     
             spool invalid_pre.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     
 
This last query will return a list of all objects that cannot be recompiled before the upgrade in the file 'invalid_pre.lst'      
 
There should be not dictionary objects invalid.
 
6. --------------------------------------------------------------------------
Verify the kernel parameters according to the installation guide of the new version.
Example for Solaris:
$ cat /etc/system
 
7. --------------------------------------------------------------------------
Ensure ORACLE_SID is set to instance you want to upgrade.
Echo $ORACLE_SID
Echo $ORACLE_HOME
 
8. --------------------------------------------------------------------------
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 or NCLOB ) 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 9.
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 9.)
 
If you have N-type columns *for user data* then check:
 
SQL> select * from nls_database_parameters where parameter
='NLS_NCHAR_CHARACTERSET';
 
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 9.
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
 
9. --------------------------------------------------------------------------
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.     
 
10. --------------------------------------------------------------------------
Ensure that all Snapshot refreshes are successfully completed.     
And replication is stopped.     
$ Sqlplus SYS/<password for SYS>     
SQL> Select distinct(trunc(last_refresh)) from     
     dba_snapshot_refresh_times;     
 
11. --------------------------------------------------------------------------
Stop the listener for the database     
$ lsnrctl     
Lsnrctl> stop <listenername>     
 
12. --------------------------------------------------------------------------
Ensure no files need media recovery:     
$ sqlplus SYS/<password for SYS>     
Select * from v$recover_file;     
     
This should return no rows     
 
13. --------------------------------------------------------------------------
Ensure no files are in backup mode:     
Select * from v$backup where status!='NOT ACTIVE';     
     
This should return no rows.     
 
14. --------------------------------------------------------------------------
Resolve any outstanding unresolved distributed transaction: 
Select * from dba_2pc_pending;     
     
If this returns rows you should do the following:     
     
Select local_tran_id from dba_2pc_pending;     
Execute dbms_transaction.purge_lost_db_entry('<LOCAL_TRAN_ID>');     
Commit;
 
15. --------------------------------------------------------------------------
Disable all batch and cron jobs.     
 
16. --------------------------------------------------------------------------
Ensure the users sys and system have 'system' as their default tablespace.     
     
Select username, default_tablespace from dba_users where username     
in ('SYS','SYSTEM');     
 
To modify use:
Alter user sys default tablespace SYSTEM;             
Alter user system default tablespace SYSTEM;                      
 
17. --------------------------------------------------------------------------
Optionally ensure the aud$ is in the system tablespace when auditing is enabled.
Select tablespace_name from dba_tables where table_name='AUD$';
 
18. --------------------------------------------------------------------------
Note down where all control files are located.     
Select * from v$controlfile;
 
19. --------------------------------------------------------------------------
Note down all sysdba users.
Select * from v$pwfile_users;
 
If a passwordfile is used copy it to the new location. On unix the default is $ORACLE_HOME/dbs/orapw<SID>.
 
On windows NT this is %ORACLE_HOME%\databaseorapw<SID>
 
20. --------------------------------------------------------------------------
Shutdown the database
$ sqlplus SYS/<password for SYS>     
SQL> Shutdown immediate
 
21. --------------------------------------------------------------------------
Change the init.ora file:     
- Make a backup of the init.ora file. 
- Comment out obsoleted parameters(list in appendix B). 
- Change all deprecated parameters(list in appendix A).  
- 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
 
22. --------------------------------------------------------------------------
Check for adequate freespace on archive log destination file systems.
 
23. --------------------------------------------------------------------------
Ensure the NLS_LANG variable is set correctly:
$ echo $NLS_LANG     
 
24. --------------------------------------------------------------------------
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 <NEW_ORACLE_HOME>/network/admin     
 
25. --------------------------------------------------------------------------
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 <SID>     
     
For Oracle 8i or higher this is:
C:\ORADIM -DELETE -SID <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
 
26. --------------------------------------------------------------------------
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.
 
27. --------------------------------------------------------------------------Update the oratab entry, to set the new ORACLE_HOME and disable automatic
startup:
<SID>:<new ORACLE_HOME>:N
 
28. --------------------------------------------------------------------------
Update the environment variables like ORACLE_HOME and PATH     
$ . oraenv     
 
29. --------------------------------------------------------------------------
Make sure the following environment variables point to the new     
Release directories:     
- ORACLE_HOME      
- PATH      
- ORA_NLS33     
- ORACLE_BASE     
- LD_LIBRARY_PATH     
- ORACLE_PATH     
     
For HP-UX systems verify the SHLIB_PATH parameter points to the new release directories.     
     
$ env | grep ORACLE_HOME     
$ env | grep PATH     
$ env | grep ORA_NLS33     
$ env | grep ORACLE_BASE     
$ env | grep LD_LIBRARY_PATH     
$ env | grep ORACLE_PATH     
                                 
HP-UX:                                 
$ env | grep SHLIB_PATH                        
 
30. --------------------------------------------------------------------------
PERFORM a Full cold backup!!!!!!!
 
You can either do this by manually copying the files or
sign on to RMAN:
 
$rman "target / nocatalog"
 
And issue the following RMAN commands:
 
RUN
{
    ALLOCATE CHANNEL chan_name TYPE DISK;
    BACKUP DATABASE FORMAT 'some_backup_directory%U' TAG before_upgrade;
    BACKUP CURRENT CONTROLFILE TO 'save_controlfile_location';
}
 
31. --------------------------------------------------------------------------
Startup upgrade the database:                     
 $ cd $ORACLE_HOME/rdbms/admin     
 Sqlplus /nolog
 SQL> Connect sys/passwd_for_sys as sysdba
 
Use Startup with the UPGRADE option:
 SQL> Startup upgrade
 
32. --------------------------------------------------------------------------
Create a SYSAUX tablespace. In Oracle Database 10gR1, the SYSAUX tablespace is used to consolidate data from a number of tablespaces that were separate in previous releases.
 
The SYSAUX tablespace must be created with the following mandatory attributes:
 
- ONLINE
- PERMANENT
- READ WRITE
- EXTENT MANAGEMENT LOCAL
- SEGMENT SPACE MANAGEMENT AUTO
 
The Upgrade Information Tool(utlu101i.sql in step 4) provides an estimate of the minimum required size for the SYSAUX tablespace in the SYSAUX Tablespace section.
 
The following SQL statement would create a 500 MB SYSAUX tablespace
for the database:
 
SQL> CREATE TABLESPACE sysaux DATAFILE 'sysaux01.dbf'
         SIZE 500M REUSE
         EXTENT MANAGEMENT LOCAL
         SEGMENT SPACE MANAGEMENT AUTO
         ONLINE;
 
33. --------------------------------------------------------------------------
Spool the output so you can take a look at possible errors after the upgrade:
SQL> Spool Upgrade.log     
 
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
 
You only need to run one script, even if your upgrade spans more than one
release. For example, if your old release was 8.1.7, then you only need to run u0801070.sql.
 
Each of these scripts is a direct upgrade path from the version you are
on to 10gR1. You do not need to run catalog.sql and catproc.sql as these
two scripts are called from within the upgrade script.     
 
The upgrade script creates and alters certain data dictionary tables.
It also upgrades or installs the following database components in the
new release 10gR1 database:
 
Oracle Database Catalog Views, Oracle Database Packages and Types
JServer JAVA Virtual Machine, Oracle Database Java Packages, Oracle XDK,
Oracle Real Application Clusters, Oracle Workspace Manager, Oracle interMedia, Oracle XML Database, OLAP Analytic Workspace, Oracle OLAP API, OLAP Catalog, Oracle Text, Spatial, Oracle Data Mining, Oracle Label Security, Messaging Gateway
 
Turn off the spooling of script results to the log file:
 
SQL> SPOOL OFF
 
Then, check the spool file and verify that the packages and procedures
compiled successfully. You named the spool file in Step 12; the suggested
name was upgrade.log. Correct any problems you find in this file and rerun the appropriate upgrade script if necessary. You can rerun any of the scripts described in this chapter as many times as necessary.
 
34. --------------------------------------------------------------------------
Run utlu101s.sql, specifying the TEXT option:
 
SQL> @utlu101s.sql TEXT
 
This is the Post-upgrade Status Tool displays the status of the database
components in the upgraded database. The Upgrade Status Tool displays output similar to the following:
 
Oracle Database 10.1 Upgrade Status Tool MM-DD-YYYY HH:MM:SS
--> Oracle Database Catalog Views        Normal successful completion
--> Oracle Database Packages and Types  Normal successful completion
--> JServer JAVA Virtual Machine         Normal successful completion
--> Oracle XDK                              Normal successful completion
--> Oracle Database Java Packages       Normal successful completion
--> Oracle Real Application Clusters   Normal successful completion
--> Oracle interMedia                     Normal successful completion
--> Oracle Text                            Normal successful completion
 
35. --------------------------------------------------------------------------
Restart the database:
SQL> Shutdown Immediate (DO NOT USE SHUTDOWN ABORT!!!!!!!!!)     
SQL> Startup restrict
 
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.
 
36. --------------------------------------------------------------------------
Run script to recompile invalid pl/sql modules:     
SQL> @utlrp
 
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     
 
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.
 
 
37. --------------------------------------------------------------------------
Shutdown the database and startup the database.     
$ sqlplus /nolog   
SQL> Connect sys/passwd_for_sys as sysdba     
SQL> Shutdown      
SQL> Startup restrict     
 
38. --------------------------------------------------------------------------
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
and go to step 39.
 
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
go to step 39.
 
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
 
go to step 39.
 
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
 
go to step 39.
 
39. --------------------------------------------------------------------------
Now edit the init.ora:
- put back the old value for the JOB_QUEUE_PROCESSES parameter
- put back the old value for the AQ_TM_PROCESSES parameter
- If you change the value for NLS_LENGTH_SEMANTICS prior to the upgrade put the value back to CHAR.
- If you changed the CLUSTER_DATABASE parameter prior the upgrade set it back to TRUE
 
40. --------------------------------------------------------------------------
Startup the database :
SQL> Startup
 
This is optional:
 
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 the
$ORACLE_HOME/dbs directory.
 
 
41. --------------------------------------------------------------------------
Modify the listener.ora file:     
For the upgraded intstance(s) modify the ORACLE_HOME parameter
to point to the new ORACLE_HOME.
 
42. --------------------------------------------------------------------------
Start the listener     
$ lsnrctl
LSNRCTL> start <listenername>     
 
43. ----------------------------------------------------------------------------
Enable cron and batch jobs     
 
44. --------------------------------------------------------------------------
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
 
 
 
RELATED NOTES:
--------------
 
Keywords:
---------
Upgrade migration migrate move convert 10g 10.1 9i 9.2 9.0.1 8.1.7 8.0.6 8.1.5 8.1.6
Upgrade migration migrate move convert 10g 10.1 9i 9.2 9.0.1 8.1.7 8.0.6 8.1.5 8.1.6
Upgrade migration migrate move convert 10g 10.1 9i 9.2 9.0.1 8.1.7 8.0.6 8.1.5 8.1.6