In this Document
Purpose
Scope and Application
Complete Checklist for Manual Upgrades to 10gR2
Steps for Upgrading the Database to 10g Release 2
Preparing to Upgrade
Useful Hints
Appendix A: Initialization Parameters Obsolete in 10g
Appendix B: Initialization Parameters Deprecated in 10g
Known issues
Community Discussions
Revision History
References
Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 10.2.0.5 - Release: 8.1.7 to 10.2
Oracle Server - Standard Edition - Version: 8.1.7.4 to 10.2.0.5 [Release: 8.1.7 to 10.2]
Information in this document applies to any platform.
This document is created for use as a guideline and checklist when manually upgrading Oracle 8i, Oracle 9i or Oracle 10gR1 to Oracle 10gR2 on a single server.
If the database instance is being moved from one server to another as part of the upgrade process, additional steps for that move may need to be performed which are not included in this checklist.
This document is divided into three major sections.
-- Preparing to Upgrade
-- Upgrading to the New Oracle Database 10g Release 2
-- After Upgrading a Database
Please read the whole article before starting to perform an upgrade.
Additional Note:
These instructions are incomplete for taking an 8i OPS cluster into a 10gR2 RAC cluster due to the differences in OPS vs RAC.
Note:293658.1 "10.1 or 10.2 Patchset Install Getting ORA-29558 JAccelerator (NCOMP) And ORA-06512"
Note:316900.1 "ALERT: Oracle 10g Release 2 (10.2) Support Status and Alerts"
Note 290738.1 "Oracle Critical Patch Update Program General FAQ"
Note 406472.1 "Mandatory Patch 5752399 for 10.2.0.3 on Solaris 64-bit and Filesystems Managed By Veritas or Solstice Disk Suite software"
Note 412271.1 "ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While Upgrading or Patching Databases to 10.2.0.3"
NOTE: If your database was originally created as 32-bit, even if it is 64-bit now, apply the patches recommended in Note 412271.1
Note 565600.1 ERROR IN CATUPGRD: ORA-00904 IN DBMS_SQLPA
Note:471479.1 "IOT Corruptions After Upgrade from COMPATIBLE <= 9.2 to COMPATIBLE >= 10.1"
Note:557242.1 "Upgrade Gives Ora-29558 Error Despite of JAccelerator Has Been Installed"
Note:465951.1 "ORA-600 [kcbvmap_1] or Ora-600 [Kcliarq_2] On Startup Upgrade Moving From a 32-Bit To 64-Bit Release"
Please also note that Oracle have made an "Oracle10g Upgrade Companion" available. For further information, please review:
Note:466181.1 "10g Upgrade Companion"
The above document is continually updated as new information becomes available.
Compatibility Matrix
Minimum Version of the database that can be directly upgraded to Oracle 10g Release 28.1.7.4 -> 10.2.X.X.X
9.0.1.4 or 9.0.1.5 -> 10.2.X.X.X
9.2.0.4 or higher -> 10.2.X.X.X
10.1.0.2 or higher -> 10.2.X.X.X
The following database version will require an indirect upgrade path.
7.3.3 (or lower) -> 7.3.4 -> 8.1.7 -> 8.1.7.4 -> 10.2.X.X.X
7.3.4 -> 8.1.7 -> 8.1.7.4 -> 10.2.X.X.X
8.0.n -> 8.1.7 -> 8.1.7.4 -> 10.2.X.X.X
8.1.n -> 8.1.7 -> 8.1.7.4 -> 10.2.X.X.X
$ sqlplus '/as sysdba'
SQL> spool Database_Info.log
SQL> @utlu102i.sql
SQL> spool off Please note: The instance can be running in normal mode and does not need to be running in a restricted (migrate / upgrade) mode to run the script. Also, the instance must not be running in read-only mode. A few registry$ tables may be created, if they do not already exist, and some rows may be inserted into existing Upgrade tables.
Then, check the spool file and examine the output of the upgrade information tool. The sections which follow, describe the output of the Upgrade Information Tool (utlu102i.sql).
NOTE: If you are upgrading from 8.1.7.4, the utlu102i.sql script will fail with an ORA-1403 error. Please follow the workaround in Note:5640527.8 (or Note:407031.1) to enable utlu102i.sql to run.
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.
Logfiles:
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 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.
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 10g 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 10g 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 10g 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 10g release that will be upgraded or installed when the current database is upgraded.
Miscellaneous Warnings:
This section provides warnings about specific situations that may require attention before and/or after the upgrade.
SYSAUX Tablespace:
This section displays the minimum required size for the SYSAUX tablespace, which is required in Oracle Database 10g. The SYSAUX tablespace must be created after the new Oracle Database 10g release is started and BEFORE the upgrade scripts are invoked.
SELECT grantee FROM dba_role_privs
WHERE granted_role = 'CONNECT' and
grantee NOT IN (
'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER',
'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');SELECT GRANTEE, PRIVILEGE FROM DBA_SYS_PRIVS
WHERE GRANTEE='CONNECT'
GRANTEE PRIVILEGE
------------------------------ ---------------------------
CONNECT CREATE VIEW
CONNECT CREATE TABLE
CONNECT ALTER SESSION
CONNECT CREATE CLUSTER
CONNECT CREATE SESSION
CONNECT CREATE SYNONYM
CONNECT CREATE SEQUENCE
CONNECT CREATE DATABASE LINKSELECT
'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')|| L.NAME||chr(10)
||'connect to ' || L.USERID || ' identified by '''
||L.PASSWORD||''' using ''' || L.host || ''''
||chr(10)||';' TEXT
FROM sys.link$ L,
sys.user$ U
WHERE L.OWNER# = U.USER# ;$ sqlplus '/as sysdba'
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;$ sqlplus '/as sysdba'
SQL>spool sdict
SQL>grant analyze any to sys;
SQL>exec dbms_stats.create_stat_table('SYS','dictstattab');
SQL>exec dbms_stats.export_schema_stats('WMSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('MDSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('CTXSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('XDB','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('WKSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('LBACSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('OLAPSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('DMSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('ODM','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('ORDSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('ORDPLUGINS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('SI_INFORMTN_SCHEMA','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('OUTLN','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('DBSNMP','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('SYSTEM','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('SYS','dictstattab',statown => 'SYS');
SQL>spool offexec dbms_stats.delete_schema_stats('SYS');
exec dbms_stats.import_schema_stats('SYS','dictstattab'); $ sqlplus '/as sysdba'
SQL>spool gdict
SQL>grant analyze any to sys;
SQL>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);
SQL>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);
SQL>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);
SQL>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);
SQL>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);
SQL>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);
SQL>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);
SQL>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);
SQL>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);
SQL>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);
SQL>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);
SQL>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);
SQL>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);
SQL>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);
SQL>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);
SQL>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);
SQL>spool offspool 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 Run the following script as a user with SYSDBA privs using SQL*Plus and then requery invalid objects:
$ sqlplus '/as sysdba'
SQL> @?/rdbms/admin/utlrp.sqlIf you are upgrading from Oracle9iR2 (9.2), verify that the view dba_registry contains data. If the view is empty, run the following scripts from the 9.2 home:
$ sqlplus '/as sysdba'
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> @?/rdbms/admin/utlrp.sql and verify that the dba_registry view now contains data.
Step 9:
Check for corruption in the dictionary useing the following commands in SQL*Plus connected as sys:
Set verify off
Set space 0
Set line 120
Set heading off
Set feedback off
Set pages 1000
Spool analyze.sql
Select 'Analyze cluster "'||cluster_name||'" validate structure cascade;'
from dba_clusters
where owner='SYS'
union
Select 'Analyze table "'||table_name||'" validate structure cascade;'
from dba_tables
where owner='SYS' and partiti and (iot_type='IOT' or iot_type is NULL)
union
Select 'Analyze table "'||table_name||'" validate structure cascade into invalid_rows;'
from dba_tables
where owner='SYS' and partiti;
spool off
This creates a script called analyze.sql.
Now execute the following steps.
$ sqlplus '/as sysdba'
SQL> @$ORACLE_HOME/rdbms/admin/utlvalid.sql
SQL> @analyze.sql$ sqlplus '/ as sysdba'
SQL> select distinct(trunc(last_refresh)) from dba_snapshot_refresh_times; You can also use:
SQL> select distinct owner, name mview, master_owner master_owner, last_refresh from dba_mview_refresh_times; $ lsnrctl
LSNRCTL> stop$ sqlplus '/ as sysdba'
SQL> select * from v$recover_file; SQL> select * from v$backup where status!='NOT ACTIVE'; SQL> select * from dba_2pc_pending; SQL> select local_tran_id from dba_2pc_pending;
SQL> execute dbms_transaction.purge_lost_db_entry('');
SQL> commit;SQL> select username, default_tablespace from dba_users
where username in ('SYS','SYSTEM'); SQL> alter user sys default tablespace SYSTEM;
SQL> alter user system default tablespace SYSTEM; Step 16:
Ensure that the aud$ is in the system tablespace when auditing is enabled.
SQL> select tablespace_name from dba_tables where table_name='AUD$';
If the AUD$ table exists, and is in use, upgrade performance can be effected depending on the number of records in the table.
Please refer to the following note for information on exporting and truncating the AUD$ table:
Step 17:
Note down where all control files are located.
SQL> select * from v$controlfile;Step 19:
Shutdown the database.
$ sqlplus '/as sysdba'
SQL> shutdown immediate; Perform a full cold backup (or an online backup using RMAN).
$ rman "target / nocatalog"RUN
{
ALLOCATE CHANNEL chan_name TYPE DISK;
BACKUP DATABASE FORMAT 'some_backup_directory%U' TAG before_upgrade;
BACKUP CURRENT CONTROLFILE TO 'save_controlfile_location';
} Step 21:
Update the init.ora file:
- Make a backup of the old init.ora file
- Copy it from the old (pre-10.2) ORACLE_HOME to the new (10.2) ORACLE_HOME
On Unix/Linux, the default location of the file is the $ORACLE_HOME/dbs directory
- Comment out any obsoleted parameters (listed in appendix A).
- Change all deprecated parameters (listed in appendix B).
- Set the COMPATIBLE initialization parameter to an appropriate value. If you are
upgrading from 8.1.7.4 then set the COMPATIBLE parameter to 9.2.0 until after the
upgrade has been completed successfully. If you are upgrading from 9.2.0 or 10.1.0
then leave the COMPATIBLE parameter set to it's current value until the upgrade
has been completed successfully. This will avoid any unnecessary ORA-942 errors
from being reported in SMON trace files during the upgrade (because the upgrade
is looking for 10.2 objects that have not yet been created)
- If you have the parameter NLS_LENGTH_SEMANTICS currently set to CHAR, change the value
to BYTE during the upgrade (to avoid the issue described in Note:4638550.8)
- Verify that the parameter DB_DOMAIN is set properly.
- Make sure the PGA_AGGREGATE_TARGET initialization parameter is set to
at least 24 MB.
- Ensure that the SHARED_POOL_SIZE and the LARGE_POOL_SIZE are at least 150Mb.
Please also the check the "KNOWN ISSUES" section
- Make sure the JAVA_POOL_SIZE initialization parameter is set to at least 150 MB.
- Make sure the STREAMS_POOL_SIZE is set to at least 50 MB (200 MB is ideal)
- Ensure there is a value for DB_BLOCK_SIZE
- 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
- Comment out any existing AQ_TM_PROCESSES and JOB_QUEUE_PROCESSES parameter settings, and add new lines in the init.ora/spfile.ora that explicitly set AQ_TM_PROCESSES=0 and JOB_QUEUE_PROCESSES=0 for the duration of the upgrade. The "startup upgrade" command (see step 30) should ensure that these settings are used, but it's worth making sure.
- If you have defined an UNDO tablespace, set the parameter UNDO_MANAGEMENT=AUTO (otherwise, either unset the parameter or explicitly set it to MANUAL). See Note:135090.1 for further information about the Automatic Undo Management feature.
- 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.
- Verify the parameter FIXED_DATE is not set. If it set during the upgrade catalog and catproc will report as invalid even if there are no invalid sys or system objects. See Note: 745183.1 for further information.
Step 22 :
Check for adequate freespace on archive log destination file systems.
升级的过程中,会产生大量的日志,如果开启了归档,一定要检查归档的空间是否充分,否则,升级可能挂起.
$ env | grep $NLS_LANG For additional information on NLS_LANG settings, please refer to the following notes:
$ cp $OLD_ORACLE_HOME/network/admin/*.ora /network/admin C:\> NET STOP OracleServiceORCL C:\ORADIM80 -DELETE -SID C:\ORADIM -DELETE -SID C:\> ORADIM -NEW -SID SID -INTPWD PASSWORD -MAXUSERS USERS
-STARTMODE AUTO -PFILE ORACLE_HOME\DATABASE\INITSID.ORASID:ORACLE_HOME:N$. oraenv- ORACLE_HOME
- PATH
- ORA_NLS10
- ORACLE_BASE
- LD_LIBRARY_PATH
- LD_LIBRARY_PATH_64 (Solaris only)
- LIBPATH (AIX only)
- SHLIB_PATH (HPUX only)
- ORACLE_PATH
$ env | grep ORACLE_HOME
$ env | grep PATH
$ env | grep ORA_NLS10
$ env | grep ORACLE_BASE
$ env | grep LD_LIBRARY_PATH
$ env | grep ORACLE_PATH$ env | grep LIBPATH$ env | grep SHLIB_PATHAs per Note:77442.1, you should set ORA_NLS10 to point to $ORACLE_HOME/nls/data.
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba
Use Startup with the UPGRADE option:
SQL> startup upgradeSQL> CREATE TABLESPACE sysaux DATAFILE 'sysaux01.dbf'
SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;Run the catupgrd.sql script, spooling the output so you can check whether any errors occured and investigate them:
SQL> spool upgrade.log
SQL> @catupgrd.sqlSQL> SPOOL OFFSQL> @utlu102s.sql TEXTSQL> select comp_name, status, version from dba_registry;SQL> shutdown immediate (DO NOT use "shutdown abort" !!!)
SQL> startup restrictSQL> @olstrig.sqlSQL> @utlrp.sql 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 You can also ignore or drop the following public synonyms:
V$KQRPD
V$KQRSD
GV$KQRPD
GV$KQRSD
As they are based on:
SYS.V_$KQRPD
SYS.V_$KQRSD
SYS.GV_$KQRPD
SYS.GV_$KQRSD
NOTE: If you have used OPatch to apply a CPU patch to the 10.2.0.x home, you now need to review and follow the post-installation steps in the README file of the CPU patch to apply the CPU patch to the upgraded database. This may require running the catcpu.sql and other scripts and will vary depending on the version of the CPU installed.
NOTE: After the upgrade there may be invalid views with the prefix x_$. These views are created by third party applications and are pointing to non-existent or modified x$ tables. Third parties should not create SYS owned views, particularly not SYS owned views based on internal X$ tables. Since these are not Oracle created objects, they should be dropped before upgrade, since they cannot be validated or dropped after upgrade using normal methods.
Additional information can be found in:
Step 37:
Shutdown the database and startup the database.
$ sqlplus '/as sysdba'
SQL> shutdown
SQL> startup restrictselect distinct OWNER, TABLE_NAME
from DBA_TAB_COLUMNS
where DATA_TYPE in ('NCHAR','NVARCHAR2', 'NCLOB')
and OWNER not in ('SYS','SYSTEM','XDB'); did not return rows in Step 6 of this note then:
$ sqlplus '/as sysdba'
SQL> shutdown immediateand go to step 40.
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'; $ sqlplus '/as sysdba'
SQL> shutdown immediateand go to step 40.
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 '/as sysdba'
SQL> @utlnchar.sql
SQL> shutdown immediate;go to step 40.
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 '/as sysdba'
SQL> shutdown immediate;go to step 40.
Step 39:
if you are upgrading from 8.1.7.4 skip to Step 40
If , while following,
Note 553812.1 Actions for the DSTv4 update in the 10.2.0.4 patchset
Note 1086400.1 Actions for the DSTv4 update in the 10.2.0.5 patchset
utltzpv4.sql found rows then restore the rows you backed up now.
Check if a "select * from dba_scheduler_jobs;" from the sqlplus found in the oracle_home on the server (!!! this is important !!! - do NOT use a remote client) gives "ORA-01882: timezone region %s not found" , if so then you need to run Fix1882.sql mentioned in Note 414590.1. This will not harm the database.
- If you changed the value for NLS_LENGTH_SEMANTICS from CHAR to BYTE prior to the upgrade (see step 21), set it back to CHAR. Otherwise, do not change the value of the parameter to CHAR without careful evaluation and testing. Switching to CHAR semantics can break application code. See Note:144808.1 for further information about the usage of this parameter.
- If you changed the value for CLUSTER_DATABASE from TRUE to FALSE prior to the upgrade, set it back to TRUE
Step 41:
Startup the database:
SQL> startupSQL> create spfile from pfile;$ lsnrctl
LSNRCTL> start $ srvconfig -upgrade -dbname <db_name> -orahome <pre-10g_Oracle_home>If the output from the $ORACLE_HOME/bin/ocrdump command references the pre-10g home, it may be necessary to do the following:
From the pre-10g home, run the command:
$ svrctl remove database -d <db_name>From the 10g home, run the commands:
$ srvctl add database -d <db_name> -o <10g_Oracle_home>
$ srvctl add instance -d <db_name> -i <instance1_name> -n <node1>
$ srvctl add instance -d <db_name> -i <instance2_name> -n <node2>Use the DBMS_STATS package to gather new statistics for your user objects. Using statistics collected from a previous Oracle version may lead CBO to generate less optimal execution plans.
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS ;Enterprise Manager Grid Control (EMGC) will show that the upgraded database does not have an inventory. To re-discover the database, do the following:
1. Go to EMGC => Targets => Databases
2. Select the upgraded database and remove it
3. Click "Add", enter the name of the host and click "Continue" to allow EMGC to re-discover
the database in the correct home with the correct inventory
** Upgrading With Read-Only and Offline Tablespaces (Database must be in archive log mode)
The Oracle database can read file headers created prior to Oracle 10g, so you do not need to do anything to them during the upgrade. The only exception to this is if you want to transport tablespaces created prior to Oracle 10g, to another platform. In this case, the file headers must be made read-write at some point before the transport. However, there are no special actions required on them during the upgrade.
The file headers of offline datafiles are updated later when they are brought online, and the file headers of read-only tablespaces are updated if and when they are made read-write sometime after the upgrade. In any other circumstance, read-only tablespaces never have to be made read-write.
To decrease the time needed for a recovery in case of issues with the migration, users can OFFLINE NORMAL all user or application created tablespaces prior to migration. This way if migration fails only the Oracle created tablespaces and rollback datafiles need to be restored rather than the entire database.
You can not OFFLINE any Oracle created tablespaces including those containing rollback/UNDO tablespace prior to migration.
Note: You must OFFLINE the TABLESPACE as migrate does not allow OFFLINE files in an ONLINE tablespace.
Note: If you are upgrading from Oracle9i, the CWMLITE tablespace (which contains OLAP objects) will need to be ONLINE during the upgrade (so that the OLAP objects can be upgraded to 10g and moved to the SYSAUX tablespace)
** Converting Databases to 64-bit Oracle Database Software
If you are installing 64-bit Oracle Database 10gR2 software but were previously using a 32-bit Oracle Database installation, then the databases will automatically be converted to 64-bit during the upgrade to Oracle Database 10gR2 except when upgrading from Release 1 (10.1) to Release 2 (10.2).
如果是从以前32位版本中升级到64位版本,是自动升级
The process is not automatic for the release 1 to release 2 upgrade, but is automatic for all other upgrades. This is because the utlip.sql script is not run during the release 1 to release 2 upgrade to invalid all PL/SQL objects. You must run the utlip.sql script as the last step in the release 10.1 environment, before upgrading to release 10.2.
** If error occurs while executing the catupgrd.sql
If an error occurs during the running of the catupgrd.sql script, once the problem is fixed you can simply rerun the catupgrd.sql script to finish the upgrade process and complete the upgrade process.
1) While doing a upgrade from 9iR2 to 10.2.0.X.X, on running the utlu102i.sql script as directed in step 2
Its output informs to add streams_pool_size=50331648 to the init.ora file. While adding the parameter Oracle gives streams_pool_size as invalid parameter.
STREAMS_POOL_SIZE, was introduced in release 10gR1. This message may be ignored for database version 9iR2 or less.
2) One of the customer has reported on keeping the shared_pool_size at 150 MB, catmeta.sql fails with insuffient shared memory during the processing of view KU$_PHFTABLE_VI.
Please set the shared_pool_size at 200M.
3) While upgrade following error was encountered.
create or replace
*
ERROR at line 1:
ORA-06553: PLS-213: package STANDARD not accessible.
ORA-00955: name is already used by an existing object
Please make sure to set the following init parameters as below in the spfile/init file or comment them out to their default values, at the time of upgrading the database.
PLSQL_V2_COMPATIBILITY = FALSE
PLSQL_CODE_TYPE = INTERPRETED # Only applicable to 10gR1
PLSQL_NATIVE_LIBRARY_DIR = ""
PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT = 0
Refer to Note 170282.1 PLSQL_V2_COMPATIBLITY=TRUE causes STANDARD and DBMS_STANDARD to Error at Compile
@
Always disconnect from the session which issues the STARTUP and connect as a fresh session before doing any further SQL. eg: On upgrade to 10.2 startup the instance with the upgrade option, exit sqlplus , reconnect a fresh SQLPLUS session as SYSDBA and then run the upgrade scripts.
If this is not a RAC instance, but DBA_REGISTRY shows the RAC component and it is invalid the following bite can be used to remove the reference from DBA_REGISTRY.
NOTE:312071.1 - RAC Option Invalid After Migration
5) Upgrade log shows process errors with ORA-28031 "maximum of 148 enabled roles" when creating queue table.
The number of DEFINED roles (enabled and disabled) that SYS has must not exceed the maximum number of roles defined for the database as defined by the instance parameter max_enabled_roles.
To correct, reduce the number of DEFINED roles to less then 148 then re-run the catupgrd.sql script.
| 欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/) | Powered by Discuz! X3.2 |