分类目录归档：ORA-xxxxx

朋友在数据库软件从11.1.0.6升级到11.1.0.7后，发现数据库无法打开，不能继续下一步升级
数据库启动

SQL> startup upgrade
ORA-03113: end-of-file on communication channel

alert日志

Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on. 
IMODE=BR
ILAT =182
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 11.1.0.7.0.
Using parameter settings in server-side spfile /u01/app/oracle/product/11.1.0/db_1/dbs/spfilecenterdb.ora
System parameters with non-default values:
  processes                = 1500
  sessions                 = 1655
  memory_target            = 12864M
  control_files            = "/u01/app/oracle/oradata/centerdb/control01.ctl"
  control_files            = "/u01/app/oracle/oradata/centerdb/control02.ctl"
  control_files            = "/u01/app/oracle/oradata/centerdb/control03.ctl"
  db_block_size            = 8192
  compatible               = "11.1.0.0.0"
  db_recovery_file_dest    = "/u01/app/oracle/flash_recovery_area"
  db_recovery_file_dest_size= 2G
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=centerdbXDB)"
  audit_file_dest          = "/u01/app/oracle/admin/centerdb/adump"
  audit_trail              = "DB"
  db_name                  = "centerdb"
  open_cursors             = 300
  diagnostic_dest          = "/u01/app/oracle"
Thu Mar 29 15:47:06 2012
PMON started with pid=2, OS id=16324 
Thu Mar 29 15:47:06 2012
VKTM started with pid=3, OS id=16326 at elevated priority
VKTM running at (20)ms precision
Thu Mar 29 15:47:06 2012
DIAG started with pid=4, OS id=16330 
Thu Mar 29 15:47:06 2012
DBRM started with pid=5, OS id=16332 
Thu Mar 29 15:47:06 2012
PSP0 started with pid=6, OS id=16334 
Thu Mar 29 15:47:06 2012
DIA0 started with pid=7, OS id=16336 
Thu Mar 29 15:47:06 2012
MMAN started with pid=8, OS id=16338 
Thu Mar 29 15:47:06 2012
DBW0 started with pid=9, OS id=16340 
Thu Mar 29 15:47:06 2012
DBW1 started with pid=10, OS id=16342 
Thu Mar 29 15:47:06 2012
DBW2 started with pid=11, OS id=16344 
Thu Mar 29 15:47:06 2012
DBW3 started with pid=12, OS id=16346 
Thu Mar 29 15:47:06 2012
DBW4 started with pid=13, OS id=16348 
Thu Mar 29 15:47:06 2012
DBW5 started with pid=14, OS id=16350 
Thu Mar 29 15:47:06 2012
LGWR started with pid=15, OS id=16352 
Thu Mar 29 15:47:06 2012
CKPT started with pid=16, OS id=16354 
Thu Mar 29 15:47:06 2012
SMON started with pid=17, OS id=16356 
Thu Mar 29 15:47:06 2012
RECO started with pid=18, OS id=16358 
Thu Mar 29 15:47:06 2012
MMON started with pid=19, OS id=16360 
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Thu Mar 29 15:47:06 2012
MMNL started with pid=20, OS id=16362 
starting up 1 shared server(s) ...
Errors in file /u01/app/oracle/diag/rdbms/centerdb/centerdb/trace/centerdb_mman_16338.trc:
ORA-27103: internal error
Additional information: -1
Additional information: 1
MMAN (ospid: 16338): terminating the instance due to error 27103
Instance terminated by MMAN, pid = 16338

这里可以发现memory_target在12g以上

trace文件内容

[oracle@fcdb trace]$ more /u01/app/oracle/diag/rdbms/centerdb/centerdb/trace/centerdb_mman_16338.trc
Trace file /u01/app/oracle/diag/rdbms/centerdb/centerdb/trace/centerdb_mman_16338.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1
System name:    Linux
Node name:      fcdb
Release:        2.6.18-164.el5
Version:        #1 SMP Tue Aug 18 15:51:48 EDT 2009
Machine:        x86_64
Instance name: centerdb
Redo thread mounted by this instance: 0 <none>
Oracle process number: 8
Unix process pid: 16338, image: oracle@fcdb (MMAN)


*** 2012-03-29 15:47:06.865
*** SESSION ID:(1648.1) 2012-03-29 15:47:06.865
*** CLIENT ID:() 2012-03-29 15:47:06.865
*** SERVICE NAME:() 2012-03-29 15:47:06.865
*** MODULE NAME:() 2012-03-29 15:47:06.865
*** ACTION NAME:() 2012-03-29 15:47:06.865
 
error 27103 detected in background process
ORA-27103: internal error
Additional information: -1
Additional information: 1

*** 2012-03-29 15:47:06.865
MMAN (ospid: 16338): terminating the instance due to error 27103

结合alert和trace文件查询MOS，发现ORA-27103 when Memory target parameter is set to more than 3 GB [ID 743012.1]描述相符，是由于Bug:7272646引起.
鉴于朋友的数据库还升级过程中，所以给出的处理建议是先把memory_target改为2.8G，执行完升级操作，然后打上Patch:7272646
同时官方还给出了另一种解决方案:设置SHMMAX小于4G，个人不推荐；如果系统内存比较大，会出现多个内存段，影响系统性能

创建两张模拟表

SQL> select * from v$version;

BANNER
------------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production


SQL> create table sys_xifenfei as           
  2  select * from dba_tables;

Table created.

SQL> create table chf.chf_xifenfei as
  2  select * from dba_tables;

Table created.

启动数据库到mount状态查询表

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  353441008 bytes
Fixed Size                   451824 bytes
Variable Size             184549376 bytes
Database Buffers          167772160 bytes
Redo Buffers                 667648 bytes
Database mounted.
SQL> desc dba_tables;
ERROR:
ORA-04043: object dba_tables does not exist


SQL> desc sys_xifenfei
ERROR:
ORA-04043: object sys_xifenfei does not exist


SQL> desc chf.chf_xifenfei
ERROR:
ORA-04043: object chf.chf_xifenfei does not exist

打开数据库查询

SQL> alter database open;

Database altered.


SQL> select count(*) from sys_xifenfei;
select count(*) from sys_xifenfei
                     *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select count(*) from chf.chf_xifenfei;

  COUNT(*)
----------
       868

SQL> select count(*) from dba_tables;
select count(*) from dba_tables
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

解决问题

SQL> alter system flush shared_pool;

System altered.


SQL> select count(*) from dba_tables;

  COUNT(*)
----------
       869

SQL> select count(*) from sys_xifenfei;

  COUNT(*)
----------
       867

MOS解释
ORA-4043 On DBA_* Views If They Are Described In Mount Stage [ID 296235.1]

Available workarounds are:
1) Don't describe the dba_* views at mount stage.
OR
2) If you issue DESC of any DBA_*views at mount stage,
    then shutdown and restart the DB instance.
OR
3) Flush the shared pool.
SQL> Alter system flush shared_pool;
and then reissue the failing command. 

在10g中open库后提示也为类此ORA-04043: object dba_tables does not exist

SCN说明
1、Oracle的SCN在每秒16384次commit的情况下可以维持534年，每秒16384次commit是Oracle早先认为的任何系统的极限commit强度；
2、Oracle里SCN的起点是1988年1月1日；
3、_minimum_giga_scn=n的含义是把SCN往前推进到nG，但请注意，只有在SCN小于nG的时候才会用到这个隐含参数，反之则Oracle会置这个隐含参数于不顾。

求模拟_minimum_giga_scn值
这里通过时间差，大概的模拟_minimum_giga_scn小于当前时间和1988年1月1日的scn最大值(300>290)

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')from dual;

TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS'
--------------------------------------
2012-03-18 04:27:50


SQL> select months_between(sysdate,to_date('19880101','yyyymmdd')) from dual;

MONTHS_BETWEEN(SYSDATE,TO_DATE('19880101','YYYYMMDD'))
------------------------------------------------------
                                             290.55443

SQL> select 16384*60*60*24*31*300/(1024*1024*1024) SCN from dual;

       SCN
----------
12260.7422

启动数据库测试

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

--------------------------------
*._minimum_giga_scn=12260
--------------------------------

SQL> startup pfile='/tmp/pfile'
ORACLE instance started.

Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-01052: required destination LOG_ARCHIVE_DUPLEX_DEST is not specified

分析ORA-01052

SQL> !oerr ora 1052
01052, 00000, "required destination LOG_ARCHIVE_DUPLEX_DEST is not specified"
// *Cause:  A valid destination for parameter LOG_ARCHIVE_DUPLEX_DEST was not
//          specified when parameter LOG_ARCHIVE_MIN_SUCCEED_DEST was set to
//          two.
// *Action: Either specify a value for parameter LOG_ARCHIVE_DUPLEX_DEST, or
//          reduce the value for parameter LOG_ARCHIVE_MIN_SUCCEED_DEST to one.

SQL> show parameter LOG_ARCHIVE_DUPLEX_DEST;

NAME                                 TYPE       VALUE
------------------------------------ ---------- ------------------------------
log_archive_duplex_dest              string
SQL> show parameter LOG_ARCHIVE_MIN_SUCCEED_DEST

NAME                                 TYPE       VALUE
------------------------------------ ---------- ------------------------------
log_archive_min_succeed_dest         integer    1

这里可以看出，不是以为我上面的两个参数设置错误导致ORA-01052，而是因为推进scn过大导致ORA-01052的错误

减小_minimum_giga_scn测试
这里选择_minimum_giga_scn小于当前时间和1988年1月1日的scn最大值(280<290)

SQL>  select 16384*60*60*24*31*280/(1024*1024*1024) SCN from dual;

       SCN
———-
11443.3594

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

—————————–
*._minimum_giga_scn=11443
—————————–


SQL> startup pfile=’/tmp/pfile’
ORACLE instance started.

Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.

SQL> select to_char(dbms_flashback.get_system_change_number(),
2    ’9999999999999999′) from dual;

TO_CHAR(DBMS_FLASHBACK.GET_SYSTEM_
———————————-
   12286827692251

SQL> select dbms_flashback.get_system_change_number()/(1024*1024*1024) from dual;

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()/(1024*1024*1024)
———————————————————-
                                                     11443

通过试验可以发现，在我们的数据库遇到异常，需要恢复通过推进scn来恢复的时候，不是推进的越大越好；如果推进的太大可能导致ORA-01052错误，一般建议是比当前不一致的scn稍微大一点
参考:http://www.dbsnake.net/two-scn-internal-points.html