Oracle8.1.0.7 -> Oracle9.2.0.1 Upgrade

1. 버젼확인
- Select * from v$version;
- Select * from v$option;
8.1.7 -> 9.2 지원

=================================
2. Cold Backup 수행

=================================
3. Avoid running out of space during the migration

- Prepare the system rollback segment:      
  =>Alter rollback segment system storage (maxextents 121 next 1M);      

- Ensure plenty of free space in the SYSTEM tablespace.  A minimum of 150 Mb
  additional free space:          
 =>Select max(bytes)/power(2,20) from dba_free_space where tablespace_name='SYSTEM';

- Ensure plenty of free space in the ROLLBACK tablespace.  Ensure that you have
  at least 1 rollback segment of 70 Mb if the number of objects in the database
  exceeds 5000:
  =>Select count(*) from dba_objects;

If you run out of space in one of these tablespaces during the upgrade, you will
need to perform the upgrade again. 

=================================
4. INVALID Object Check
- 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';

$ 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.

=================================
5. Verify the kernel parameters according to the installation guide of the
new version.
Example for Solaris:
$ cat /etc/system

=================================
6. Ensure ORACLE_SID is set to instance you want to upgrade.
Echo $ORACLE_SID
Echo $ORACLE_HOME

=================================
7. CHARACTERSET check
SELECT NAME,VALUE$
FROM SYS.PROPS$
WHERE NAME ='NLS_LANGUAGE'
OR  NAME ='NLS_TERRITORY'
OR  NAME ='NLS_CHARACTERSET';

=================================
8.
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.

=================================
9.
Ensure that all Snapshot refreshes are successfully completed.      
And replication is stopped.      
$ Sqlplus SYS/<password for SYS>      
Select distinct(trunc(last_refresh)) from dba_snapshot_refresh_times;     

=================================
10.
Stop the listener for the database      
$ lsnrctl      
Lsnrctl> stop <listenername> 

=================================
11.
Ensure no files need media recovery:      
$ sqlplus SYS/<password for SYS>      
Select * from v$recover_file;      
      
This should return no rows   

=================================
12.
Ensure no files are in backup mode:      
Select * from v$backup where status!='NOT ACTIVE';      
      
This should return no rows.    

=================================
13.
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;

=================================
14.
Disable all batch and cron jobs.

=================================
15.
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; 

=================================
16.
Optionally ensure the aud$ is in the system tablespace when auditing is enabled.
Select tablespace_name from dba_tables where table_name='AUD$';

=================================
17.
Note down where all control files are located.      
Select * from v$controlfile;

=================================
18.
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%\database\orapw<SID> 

=================================
19.
Shutdown the database 
$ sqlplus SYS/<password for SYS>      
SQL> Shutdown immediate

=================================
20.
Change the init.ora file

=================================
21.
Check for adequate freespace on archive log destination file systems.

=================================
22.
Ensure the NLS_LANG variable is set correctly:
$ echo $NLS_LANG 

=================================
23.
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      

=================================
24.
If your Operating system is Windows NT, delete your services
With the ORADIM of your old oracle version.      

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 9i service use ORADIM of the 9i ORACLE_HOME:      
      
C:\ORADIM -NEW -SID <SID> -INTPWD <internalpwd> -MAXUSERS n       
   -STARTMODE MANUAL -PFILE %ORACLE_HOME%\DATABASE\init<SID>.ora   
  
=================================
25.
If needed copy the init.ora file to the new oracle_home or      
Create a link to the init.ora.      
cp $OLD_ORACLE_HOME/dbs/init.ora $NEW_ORACLE_HOME/dbs/init.ora 

=================================
26.
Update the oratab entry, to set the new ORACLE_HOME and disable automatic
startup:
<SID>:<new ORACLE_HOME>:N
=================================
27.
Update the environment variables like ORACLE_HOME and PATH      
$ . oraenv  

=================================
28.
Make sure the following enviroment 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 
=================================
29.
Run the upgrade script:      
$ cd $ORACLE_HOME/rdbms/admin      
Sqlplus /nolog
SQL> Connect sys/passwd_for_sys as sysdba

Use Startup RESTRICT when you are upgrading to Oracle 9.0.1:
SQL> Startup restrict      

Use Startup MIGRATE when you are upgrading to Oracle 9.2:
SQL> Startup Migrate

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      
====       ===         ==================      
8.0.6      9.0.1         u0800060.sql  
8.0.6      9.2           u0800060.sql  
8.1.5      9.0.1         u0801050.sql
8.1.5      9.2           Not Supported
8.1.6      9.0.1         u0801060.sql
8.1.6      9.2           Not Supported
8.1.7      9.0.1         u0801070.sql
8.1.7      9.2           u0801070.sql
9.0.1      9.2           u0900010.sql

Each of these scripts is a direct upgrade path from the version you are       
on to 9i. You do not need to run catalog.sql and catproc.sql as these       
two scripts are called from within the upgrade script.      

The remainder of this step is only valid for upgrades towards Oracle 9.2:

Display the contents of the component registry to determine which components
need to be upgraded:
SQL> Select comp_name, version, status from dba_registry;

Run the script cmpdbmig.sql to upgrade the components which can be upgrade
with the SYSDBA privilege:

SQL> @cmpdbmig.sql

The components upgraded by this script are:
Jserver JAVAVM, oracle XDK for Java, Oracle 9i RAC, Oracle Data Mining,
OLAP analytical Workspace, Oracle 9i Java Packages, Messaging Gateway,
Oracle Workspace Manager, OLAP Catalog, Oracle Label Security.

Display the components which were upgraded:
SQL> Select comp_name, version, status from dba_registry;

End the spool of the upgrade:
SQL> Spool Off

=================================
30.
Restart the database:
SQL> Shutdown Immediate (DO NOT USE SHUTDOWN ABORT!!!!!!!!!)      
SQL> Startup restrict

=================================
31.
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 4.

There should be no dictionary objects invalid.

=================================
32.
Edit init.ora file:      
- Leave the parameter JOB_QUEUE_PROCESSES=0
- Leave the parameter AQ_TM_PROCESSES=0
- remove the parameter _system_trig_enabled from the init.ora file.  This
  parameter was explicitly set to false during the upgrade.
- modify the log_archive_dest parameter specify only the path, but make sure it      
  ends with a '/'. (remove the format)      
  e.g. log_archive_dest=/path/arch into log_archive_dest=/path/      
- Modify the parameter log_archive_format and add the format previously       
  removed from the log_archive_dest.      
  E.g log_archive_format=arch%t_SID_%s.log      
- If you are using a password file set the REMOTE_LOGIN_PASSWORDFILE parameter
  to the value used before migration.
 
=================================
33.
Shutdown the database and startup the database.      
$ sqlplus /nolog    
SQL> Connect sys/passwd_for_sys as sysdba      
SQL> Shutdown       
SQL> Startup restrict 

=================================
For all information regarding the national characterset,
please refer to Note 276914.1

A) IF you are NOT using N-type colums 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 goto step 37.

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 37.

C) IF you are using N-type colums 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 colums *data* need to be converted to AL16UTF16:

To upgrade user tables with N-type colums 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 37.

D)IF you are using N-type colums 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 37.

=================================
37.
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

=================================
38.
Startup the database:
SQL> Startup

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.

=================================
39.
Modify the listener.ora file:      
For the upgraded intstance(s) modify the ORACLE_HOME parameter
to point to the new ORACLE_HOME.

=================================
40.
Start the listener      
$ lsnrctl
LSNRCTL> start <listenername> 

=================================
41.
Enable cron and batch jobs 
=================================
42.
Change oratab entry to use automatic startup      
SID:ORACLE_HOME:Y  
=================================
43.
To use the new features in 9i change the compatible parameter to the new release.
When everything is well tested, update the compatible parameter in the init.ora      
and restart to the new release number.      
COMPATIBLE=9.0.X  where x is the release number
=================================