从Oracle 10g以后数据库升级前我们都需要将实例关闭，并以startup upgrade的升级模式启动数据库。那么startup upgrade到底对instance做了什么事？在这样的模式下升级可以绕过那些错误呢？ 就常规判断可以猜测startup upgrade升级模式至少会限制以下几点:
/* 任意drop或alter不存在的表或索引都不会报错而会通过，以下错误也是类似的 */
- 关闭自动作业队列
- 关闭回收站功能
- 禁用系统触发器
- 以限制模式启动，仅允许以SYSDBA身份登录
SQL> startup upgrade; 以升级模式启动后观察告警日志内容: Starting ORACLE instance (normal) 以上信息表明虽然是upgrde模式，但其实仍是以NORMAL启动 ........... ALTER SYSTEM enable restricted session; 实例mount之后启用了限制会话特性 ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY; _system_trig_enabled参数控制一些系统触发器是否被启用，例如某些登录审计触发器 (system trigger facilities are used as an additional audit trail or as a mechanism to prevent certain actions taking place or as part of a virtual private database solution) ALTER SYSTEM SET aq_tm_processes=0 SCOPE=MEMORY; 设置aq_tm_processes参数为0，相当于禁用了对信息队列的监控和控制 ALTER SYSTEM SET resource_manager_plan='' SCOPE=MEMORY; 升级期间显然不需要任何resource plan来凑热闹 ALTER SYSTEM SET _undo_autotune=FALSE SCOPE=MEMORY; 设置隐藏参数_undo_autotune为false，禁用undo自动调优特性 ALTER SYSTEM SET undo_retention=900 SCOPE=MEMORY; 900似乎一直是undo保存的理想值 ALTER SYSTEM SET enable_ddl_logging=FALSE SCOPE=MEMORY; 在11g中增加了新的DDL日志功能，这里需要通过设置enable_ddl_logging为false来禁用该特性 ALTER SYSTEM SET recyclebin=‘OFF’ DEFERRED SCOPE=MEMORY; 在10g下似乎不会关闭回收站，但其实这一点很有必要； Completed: ALTER DATABASE OPEN MIGRATE 这里可以看到startup upgrade只是migrate模式的发展除去以上部分初始化参数的修正外，upgrade升级模式下还会“隐瞒“不报一部分常见的非致命错误，这些常见错误并不影响升级进程； 这样做的用意是为了让在升级过程中只有真正致命(fatal)的错误才被反映和记录下来。 这部分升级常见错误包括:
ORA-00942:"table or view does not exist" --alter table/view 或 drop table/view时可能出现以上述ORA-00942错误为例:
|
1
2
3
|
SQL>
drop
table
not_exist;
Table
dropped.
|
ORA-01418: "specified index does not exist" --DROP索引时可能出现 ORA-00955:"name is already used by an existing object" --当创建某些对象时可能出现 ORA-01430:"column being added already exists in table" --ALTER TABLE 时可能出现 ORA-04043:"object %s does not exist" --修改对象时可能出现
// *Cause: An object name was specified that was not recognized by the system. // There are several possible causes: // - An invalid name for a table, view, sequence, procedure, function, // package, or package body was entered. Since the system could not // recognize the invalid name, it responded with the message that the // named object does not exist. // - An attempt was made to rename an index or a cluster, or some // other object that cannot be renamed. // *Action: Check the spelling of the named object and rerun the code. (Valid // names of tables, views, functions, etc. can be listed by querying // the data dictionary.)
本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1277743
