手工升级到oracle 10gR2 的步骤

郑全

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

This script (analyze.sql) should not return any errors.

郑全

Step 10:

Ensure that all Materialized views/Snapshot refreshes are successfully completed, and that replication must be stopped (ie: quiesced).

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

郑全

Step 11:

Stop the listener for the database:

$ lsnrctl
LSNRCTL> stop

Ensure no files need media recovery:

$ sqlplus '/ as sysdba'
SQL> select * from v$recover_file;

This should return no rows.

郑全

Step 12:

Ensure no files are in backup mode:

SQL> select * from v$backup where status!='NOT ACTIVE';

This should return no rows.

郑全

Step 13:

Resolve any outstanding unresolved distributed transactions:

SQL> select * from dba_2pc_pending;

If this returns rows you should do the following:

SQL> select local_tran_id from dba_2pc_pending;
SQL> execute dbms_transaction.purge_lost_db_entry('');
SQL> commit;

郑全

Step 14:

Disable all batch and cron jobs.

郑全

Step 15:

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');

To modify use:

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:

 

Note.979942.1 Ext/Pub Database upgrade appears to have halted at SYS.AUD$ Table

郑全

Step 17:

Note down where all control files are located.

SQL> select * from v$controlfile;

郑全

Step 18:

If table XDB.MIGR9202STATUS exists in the database, drop it before upgrading the database (to avoid the issue described in Note:356082.1)