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 partitioned='NO'
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 partitioned='YES';
spool off
$ sqlplus "/ as sysdba"
SQL> @$ORACLE_HOME/rdbms/admin/utlvalid.SQL
SQL> @analyze.SQL
17. Ensure that all snapshot refreshes are successfully completed，and that replication is stopped.
SELECT DISTINCT(TRUNC(last_refresh))
FROM dba_snapshot_refresh_times;
18. Ensure that no files need media recovery.
SQL> SELECT * FROM v$recover_file;
no rows selected
19. Ensure that no files are in backup mode.
SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
20. Resolve outstanding distributed transactions prior to the upgrade.
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;
21. To check if a standby database exists
SQL> SELECT SUBSTR(VALUE,INSTR(VALUE,'=',INSTR(UPPER(VALUE),'SERVICE'))+1)
2 FROM v$parameter
3 WHERE name LIKE 'log_archive_dest%' AND UPPER(VALUE) LIKE 'SERVICE%';
no ROWS selected
22. Disable all batch and cron jobs.
23. 如果有em，那么需要备份em原数据
由于客户这里并没有配置em，故我跳过该步骤，如果存在的话，那么参看如下文档进行操作即可。
How To Save Oracle Enterprise Manager Database Control Data Before Upgrading The Single Instance Database To Other Release [ID 870877.1]
24. Ensure the users SYS and SYSTEM have 'SYSTEM' as their default tablespace.
You must have sufficient space in the tablespace or be set to extents unlimited.
-- 操作记录
SQL> SELECT SUBSTR(VALUE,INSTR(VALUE,'=',INSTR(UPPER(VALUE),'SERVICE'))+1)
2 FROM v$parameter
3 WHERE name LIKE 'log_archive_dest%' AND UPPER(VALUE) LIKE 'SERVICE%';
no ROWS selected
SQL> SELECT username，default_tablespace
2 FROM dba_users
3 WHERE username IN ('SYS','SYSTEM');
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
SYS SYSTEM
SYSTEM SYSTEM
25. Ensure that if the aud$ table exists that it is in the SYS schema and in the SYSTEM tablespace.
SQL> SELECT owner,tablespace_name
2 FROM dba_tables
3 WHERE TABLE_NAME='AUD$';
OWNER TABLESPACE_NAME
------------------------------ ------------------------------
SYS SYSTEM
26. Check whether database has any externally authenticated SSL users.
SQL> SELECT name
2 FROM sys.USER$
3 WHERE ext_username IS NOT NULL
4 AND password = 'GLOBAL';
no ROWS selected
27. stop database,stoo listener and dbconsole.
-- shutdown immediate
-- lsnrctl stop
-- emctl stop dbconsole
28. 修改.profile，编辑复制原spfile进行适当编辑生成一个11gR2 pfile
-- 修改.profile 中$ORACLE_HOME即可（指定到11gR2 目录）
如下：
export ORACLE_HOME=/ora_engine/product/11.2.0/dbhome_1
-- 修改pfile，如下：
具体内容省略。
备注：如何确认10g中那些参数在11gR2中已经废弃，那么可以参看utlu112i.sql的运行结果，并作相应修改即可。
-- 将10g 密码文件listene