11gR2 GI打PSU ORACLE_HOME空间不足案例—需要20G以上空闲空间

GI打auto打patch命令

#/u01/oracle/app/grid/OPatch/opatch auto /u01/soft/18706472 -ocmrf /u01/soft/ocm.rsp
Executing /u01/oracle/app/grid/perl/bin/perl /u01/oracle/app/grid/OPatch/crs/patch11203.pl
-patchdir /u01/soft -patchn 18706472 -ocmrf /u01/soft/ocm.rsp -paramfile /u01/oracle/app/grid/crs/install/crsconfig_params
This is the main log file: /u01/oracle/app/grid/cfgtoollogs/opatchauto2014-10-15_01-15-28.log
This file will show your detected configuration and all the steps that opatchauto attempted to do on your system:
/u01/oracle/app/grid/cfgtoollogs/opatchauto2014-10-15_01-15-28.report.log
2014-10-15 01:15:28: Starting Clusterware Patch Setup
Using configuration parameter file: /u01/oracle/app/grid/crs/install/crsconfig_params
Stopping CRS...
Stopped CRS successfully
patch /u01/soft/18706472/18522509  apply successful for home  /u01/oracle/app/grid
patch /u01/soft/18706472/18522515  apply failed  for home  /u01/oracle/app/grid
Starting CRS...
Installing Trace File Analyzer
CRS-4123: Oracle High Availability Services has been started.
opatch auto succeeded.

这里可以看到,打patch 18522509成功,后续patch都失败,而不是仅仅18522515失败

分析错误日志

Composite patch 18522509 successfully applied.
 OPatch Session completed with warnings.
 Log file location: /u01/oracle/app/grid/cfgtoollogs/opatch/opatch2014-10-15_01-19-00AM_1.log
 OPatch completed with warnings.
2014-10-15 01:21:44: patch /u01/soft/18706472/18522509  apply successful for home  /u01/oracle/app/grid
2014-10-15 01:21:44: Executing command /u01/oracle/app/grid/OPatch/opatch napply /u01/soft/18706472/18522515 -local -silent -ocmrf /
u01/soft/ocm.rsp -oh /u01/oracle/app/grid -invPtrLoc /u01/oracle/app/grid/oraInst.loc as grid
2014-10-15 01:21:44: Running as user grid: /u01/oracle/app/grid/OPatch/opatch napply /u01/soft/18706472/18522515 -local -silent -ocm
rf /u01/soft/ocm.rsp -oh /u01/oracle/app/grid -invPtrLoc /u01/oracle/app/grid/oraInst.loc
2014-10-15 01:21:44: s_run_as_user2: Running /bin/su grid -c ' /u01/oracle/app/grid/OPatch/opatch napply /u01/soft/18706472/18522515
 -local -silent -ocmrf /u01/soft/ocm.rsp -oh /u01/oracle/app/grid -invPtrLoc /u01/oracle/app/grid/oraInst.loc '
2014-10-15 01:21:49: Removing file /tmp/uaa7jC7eu
2014-10-15 01:21:49: Successfully removed file: /tmp/uaa7jC7eu
2014-10-15 01:21:49: /bin/su exited with rc=73
2014-10-15 01:21:49: status of apply patch is 18688
2014-10-15 01:21:49: The apply patch output is Oracle Interim Patch Installer version 11.2.0.3.6
 Copyright (c) 2013, Oracle Corporation.  All rights reserved.
 Oracle Home       : /u01/oracle/app/grid
 Central Inventory : /u01/oracle/app/oraInventory
    from           : /u01/oracle/app/grid/oraInst.loc
 OPatch version    : 11.2.0.3.6
 OUI version       : 11.2.0.4.0
 Log file location : /u01/oracle/app/grid/cfgtoollogs/opatch/opatch2014-10-15_01-21-44AM_1.log
 Verifying environment and performing prerequisite checks...
 Prerequisite check "CheckSystemSpace" failed.
 The details are:
 Required amount of space(24021.839MB) is not available.
 UtilSession failed:
 Prerequisite check "CheckSystemSpace" failed.
 Log file location: /u01/oracle/app/grid/cfgtoollogs/opatch/opatch2014-10-15_01-21-44AM_1.log
 OPatch failed with error code 73

这里可以看出来,patch 18522509 成功,但是18522515在check的时候失败,由于$ORACLE_HOME(GI)需要可用空间为24021.839MB,现在空闲空间不足

查看空间并腾空间

# df -g
Filesystem    GB blocks      Free %Used    Iused %Iused Mounted on
/dev/hd4          10.00      9.56    5%    12443     1% /
/dev/hd2          15.00     11.42   24%    60339     3% /usr
/dev/hd9var       10.00      9.91    1%     3310     1% /var
/dev/hd3          10.00      9.65    4%      364     1% /tmp
/dev/hd1          10.00     10.00    1%       32     1% /home
/dev/hd11admin      0.25      0.25    1%        5     1% /admin
/proc                 -         -    -         -     -  /proc
/dev/hd10opt      10.00      8.98   11%    12401     1% /opt
/dev/fslv00       50.00     12.94   75%    59137     2% /u01
/dev/odm           0.00      0.00   -1%        6   100% /dev/odm
/dev/vx/dsk/crs_dg/crs_vol      1.96      1.75   12%        5     1% /crsdata
/dev/vx/dsk/ora_dg/data01_vol   2048.00   2031.32    1%        4     1% /oradata/data01
/dev/vx/dsk/ora_dg/sys_vol    400.00    350.29   13%     3702     1% /oradata/sys
# cd /u01
# ls
lost+found  oracle      soft
# cd soft
# du -sg
17.34   .
# mv /u01/soft /oradata/sys/
# df -g
Filesystem    GB blocks      Free %Used    Iused %Iused Mounted on
/dev/hd4          10.00      9.56    5%    12443     1% /
/dev/hd2          15.00     11.42   24%    60339     3% /usr
/dev/hd9var       10.00      9.91    1%     3310     1% /var
/dev/hd3          10.00      9.65    4%      364     1% /tmp
/dev/hd1          10.00     10.00    1%       32     1% /home
/dev/hd11admin      0.25      0.25    1%        5     1% /admin
/proc                 -         -    -         -     -  /proc
/dev/hd10opt      10.00      8.98   11%    12401     1% /opt
/dev/fslv00       50.00     27.56   45%    59137     2% /u01
/dev/odm           0.00      0.00   -1%        6   100% /dev/odm
/dev/vx/dsk/crs_dg/crs_vol      1.96      1.75   12%        5     1% /crsdata
/dev/vx/dsk/ora_dg/data01_vol   2048.00   2031.32    1%        4     1% /oradata/data01
/dev/vx/dsk/ora_dg/sys_vol    400.00    333.54   14%     3702     1% /oradata/sys
# cd soft
# ls -ltr
total 12464202
drwxr-xr-x    5 grid     dba            1024 Jul 07 21:06 18706472
-rw-rw-r--    1 grid     dba            2123 Jul 15 20:06 PatchSearch.xml
-rw-r--r--    1 root     system   1801653734 Oct 14 14:39 p13390677_112040_aix64-5l_1of7.zip
-rw-r--r--    1 root     system   1170882875 Oct 14 14:40 p13390677_112040_aix64-5l_2of7.zip
-rw-r--r--    1 root     system   2127071138 Oct 14 14:41 p13390677_112040_aix64-5l_3of7.zip
-rw-r--r--    1 root     system   1242090126 Oct 14 14:43 p18706472_112040_AIX64-5L.zip
-rw-r--r--    1 root     system     34964928 Oct 14 14:43 p6880880_112000_AIX64-5L.zip
-rwxr-xr-x    1 root     system       127965 Oct 14 14:48 unzip_aix
-rw-r--r--    1 root     system      4871110 Oct 14 14:50 ONEOFF_112043_AIX64.zip
drwxr-xr-x    8 grid     dba            1024 Oct 14 14:55 grid
drwxr-xr-x    8 oracle   dba            1024 Oct 14 14:57 database
-rw-r--r--    1 grid     dba             621 Oct 15 00:11 ocm.rsp

确实/u01空间不足,通过把RAC安装文件迁移到其他目录,确保/u01有足够空间用来打patch

回退命令

# /u01/oracle/app/grid/OPatch/opatch auto /u01/soft/18706472 -rollback -ocmrf /u01/soft/ocm.rsp

重新打patch

#/u01/oracle/app/grid/OPatch/opatch auto /u01/soft/18706472 -ocmrf /u01/soft/ocm.rsp                 <
Executing /u01/oracle/app/grid/perl/bin/perl /u01/oracle/app/grid/OPatch/crs/patch11203.pl -patchdir /oradata/sys/soft
-patchn 18706472 -ocmrf /oradata/sys/soft/ocm.rsp -paramfile /u01/oracle/app/grid/crs/install/crsconfig_params
This is the main log file: /u01/oracle/app/grid/cfgtoollogs/opatchauto2014-10-15_01-57-42.log
This file will show your detected configuration and all the steps that opatchauto attempted to do on your system:
/u01/oracle/app/grid/cfgtoollogs/opatchauto2014-10-15_01-57-42.report.log
2014-10-15 01:57:42: Starting Clusterware Patch Setup
Using configuration parameter file: /u01/oracle/app/grid/crs/install/crsconfig_params
Stopping CRS...
Stopped CRS successfully
patch /oradata/sys/soft/18706472/18522509  apply successful for home  /u01/oracle/app/grid
patch /oradata/sys/soft/18706472/18522515  apply successful for home  /u01/oracle/app/grid
patch /oradata/sys/soft/18706472/18522514  apply successful for home  /u01/oracle/app/grid
Starting CRS...
Installing Trace File Analyzer
CRS-4123: Oracle High Availability Services has been started.
opatch auto succeeded.

检查确认patch成功

# su - grid
xifenfei1:/home/grid> opatch lspatches
18522514;ACFS PATCH SET UPDATE : 11.2.0.4.3 (18522514)
18522515;OCW Patch Set Update : 11.2.0.4.3 (18522515)
18522509;Database Patch Set Update : 11.2.0.4.3 (18522509)
xifenfei1:/home/grid> crsctl status res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE  ONLINE       xifenfei1
               ONLINE  ONLINE       xifenfei2
ora.asm
               OFFLINE OFFLINE      xifenfei1
               OFFLINE OFFLINE      xifenfei2
ora.gsd
               OFFLINE OFFLINE      xifenfei1
               OFFLINE OFFLINE      xifenfei2
ora.net1.network
               ONLINE  ONLINE       xifenfei1
               ONLINE  ONLINE       xifenfei2
ora.ons
               ONLINE  ONLINE       xifenfei1
               ONLINE  ONLINE       xifenfei2
ora.registry.acfs
               OFFLINE OFFLINE      xifenfei1
               OFFLINE OFFLINE      xifenfei2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       xifenfei2
ora.cvu
      1        ONLINE  ONLINE       xifenfei2
ora.xifenfei1.vip
      1        ONLINE  ONLINE       xifenfei1
ora.xifenfei2.vip
      1        ONLINE  ONLINE       xifenfei2
ora.oc4j
      1        ONLINE  ONLINE       xifenfei2
ora.scan1.vip
      1        ONLINE  ONLINE       xifenfei2

通过多次resetlogs规避类似ORA-01248: file N was created in the future of incomplete recovery错误

数据库现状
控制文件
recover_xifenfei0
控制文件中数据文件信息
recover_xifenfei1
数据文件头信息
recover_xifenfei2
redo信息
recover_xifenfei3
根据当前数据库恢复检查脚本(Oracle Database Recovery Check)收集的信息,数据库的是非归档状态,而且redo已经覆盖,数据库datafile 5 无法直接online.遇到这样情况,可以使用bbed修改文件头scn实现online(使用bbed让rac中的sysaux数据文件online),也可以通过使用_allow_resetlogs_corruption等隐含参数实现online.本恢复案例中有180个数据文件,160个offline,然后open数据库,所以大量数据文件无法正常online,bbed工作量太大.在恢复过程中不幸遇到ORA-01248

数据库resetlogs出现ORA-01248错误

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01248: file 5 was created in the future of incomplete recovery
ORA-01110: data file 5: 'F:\TTDATA\PUBRTS.DAT'

alert日志记录

Fri Oct 10 15:09:26 2014
alter database open resetlogs
Fri Oct 10 15:09:26 2014
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
ORA-1248 signalled during: alter database open resetlogs...
Fri Oct 10 15:15:22 2014
alter database open
Fri Oct 10 15:15:22 2014
ORA-1589 signalled during: alter database open...
Fri Oct 10 15:15:30 2014
alter database  open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
ORA-1248 signalled during: alter database  open resetlogs...

尝试offline文件然后resetlogs

SQL>ALTER DATABASE DATAFILE 5  OFFLINE;
Database altered.
sql>ALTER DATABASE OPEN RESETLOGS；
ERROR at line 1:
ORA-01245: ffline file 5 will be lost if resetlogs is done
ORA-01110: data file 5: 'F:\TTDATA\PUBRTS.DAT'

alert日志

Fri Oct 10 15:19:37 2014
ALTER DATABASE DATAFILE 5 offline
Fri Oct 10 15:19:37 2014
Completed: ALTER DATABASE DATAFILE 5 offline
Fri Oct 10 15:19:40 2014
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
ORA-1245 signalled during: alter database open resetlogs...

出现该错误原因是由于数据库是非归档模式,offline数据文件需要使用offline drop

Fri Oct 10 15:22:16 2014
alter database datafile 5 offline drop
Fri Oct 10 15:22:17 2014
Completed: alter database datafile 5 offline drop
Fri Oct 10 15:23:13 2014
alter database open resetlogs
Fri Oct 10 15:23:14 2014
Fri Oct 10 15:23:49 2014
RESETLOGS after complete recovery through change 1422423346
Resetting resetlogs activation ID 3503292347 (0xd0cfffbb)
Fri Oct 10 15:24:01 2014
Setting recovery target incarnation to 3
Fri Oct 10 15:24:04 2014
Assigning activation ID 3649065262 (0xd980512e)
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=23, OS id=3772
Fri Oct 10 15:24:04 2014
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=24, OS id=3668
Fri Oct 10 15:24:05 2014
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLTTDB\REDO01.LOG
Successful open of redo thread 1
Fri Oct 10 15:24:05 2014
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Oct 10 15:24:05 2014
ARC0: STARTING ARCH PROCESSES
ARC2: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the 'no FAL' ARCH
ARC2 started with pid=25, OS id=636
Fri Oct 10 15:24:06 2014
ARC0: Becoming the 'no SRL' ARCH
Fri Oct 10 15:24:06 2014
ARC1: Becoming the heartbeat ARCH
Fri Oct 10 15:24:06 2014
SMON: enabling cache recovery
Fri Oct 10 15:24:07 2014
Successfully onlined Undo Tablespace 1.
Dictionary check beginning
File #5 is offline, but is part of an online tablespace.
data file 5: 'F:\TTDATA\PUBRTS.DAT'
Dictionary check complete
Fri Oct 10 15:24:19 2014
SMON: enabling tx recovery
Fri Oct 10 15:24:19 2014
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=26, OS id=868
Fri Oct 10 15:24:21 2014
LOGSTDBY: Validating controlfile with logical metadata
Fri Oct 10 15:24:21 2014
LOGSTDBY: Validation complete
Completed: alter database open resetlogs

open成功后,再次resetlogs库,实现数据文件online

Fri Oct 10 15:28:44 2014
ALTER DATABASE DATAFILE 5 online
Fri Oct 10 15:28:44 2014
Completed: ALTER DATABASE DATAFILE 5 online
Fri Oct 10 15:31:46 2014
alter database open resetlogs
Fri Oct 10 15:31:46 2014
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
Setting recovery target incarnation to 4
Fri Oct 10 15:32:00 2014
Assigning activation ID 3649091231 (0xd980b69f)
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=23, OS id=700
Fri Oct 10 15:32:00 2014
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=24, OS id=3360
Fri Oct 10 15:32:01 2014
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLTTDB\REDO01.LOG
Successful open of redo thread 1
Fri Oct 10 15:32:01 2014
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Oct 10 15:32:01 2014
ARC0: STARTING ARCH PROCESSES
ARC2: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the 'no FAL' ARCH
ARC2 started with pid=25, OS id=2016
Fri Oct 10 15:32:02 2014
ARC0: Becoming the 'no SRL' ARCH
Fri Oct 10 15:32:02 2014
ARC1: Becoming the heartbeat ARCH
Fri Oct 10 15:32:02 2014
SMON: enabling cache recovery
Fri Oct 10 15:32:03 2014
Successfully onlined Undo Tablespace 1.
Dictionary check beginning
Fri Oct 10 15:32:15 2014
Dictionary check complete
Fri Oct 10 15:32:15 2014
SMON: enabling tx recovery
Fri Oct 10 15:32:15 2014
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=26, OS id=256
Fri Oct 10 15:32:17 2014
LOGSTDBY: Validating controlfile with logical metadata
Fri Oct 10 15:32:17 2014
LOGSTDBY: Validation complete
Completed: alter database open resetlogs

MOS又一次不靠谱—ORA-27163: out of memory

数据库版本

oracle -> 11g @xifenfei:/home/oracle$sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 30 10:28:30 2014
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for HPUX: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

数据库补丁信息

oracle -> 11g @xifenfei:/home/oracle$opatch lspatches
18973907;
18795105;
18701707;
18284357;
18020394;
17564992;
17308789;
17306264;
17259786;
17079301;
16477664;
16188701;
14368995;
14245531;
11744544;
18522515;OCW Patch Set Update : 11.2.0.4.3 (18522515)
18522509;Database Patch Set Update : 11.2.0.4.3 (18522509)

收集spa报告报ORA-27163: out of memory错误

SQL> SELECT dbms_sqlpa.report_analysis_task('SPA_TEST', 'HTML', 'errors','ALL') FROM dual;
ERROR:
ORA-27163: out of memory
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 8211
ORA-06512: at "SYS.DBMS_SQLPA", line 515
ORA-06512: at line 1
no rows selected

设置event后收集

Connected.
SQL> alter session set events '31156 trace name context forever, level 0x400';
Session altered.
SQL> SELECT dbms_sqlpa.report_analysis_task('SPA_TEST', 'HTML', 'errors','ALL') FROM dual;
DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TEST','HTML','ERRORS','ALL')
--------------------------------------------------------------------------------
<html>
    <head>
        <title>
   SQL Performance Impact Analyzer Report

查询MOS发现XML Parser Fails With ORA-27163 (Out Of Memory) (Doc ID 1599434.1),相关描述:
bug-xml
按照文档描述,该问题在11.2.0.4中已经修复,可是在hp unix中依然存在该问题,经验告诉我们,现在的MOS不能完全相信

Alert Log Errors: 12170 TNS-12535/TNS-00505: Operation Timed Out

客户反馈系统经常报会话超时,导致应用测试无法正常进行,经检查alert日志发现

Fatal NI connect error 12170.
  VERSION INFORMATION:
        TNS for HPUX: Version 11.2.0.4.0 - Production
        Oracle Bequeath NT Protocol Adapter for HPUX: Version 11.2.0.4.0 - Production
        TCP/IP NT Protocol Adapter for HPUX: Version 11.2.0.4.0 - Production
  Time: 29-SEP-2014 20:42:56
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535
TNS-12535: TNS:operation timed out
    ns secondary err code: 12560
    nt main err code: 505
TNS-00505: Operation timed out
    nt secondary err code: 238
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.78.199.47)(PORT=55447))
Mon Sep 29 20:42:56 2014

虽然大部分网站或者mos上描述,Fatal NI connect error 12170部分情况考虑使用配置如下配置

##调整listener.ora
vi $ORACLE_HOME/network/admin/listener.ora
增加:
DIAG_ADR_ENABLED_LISTENER=OFF
INBOUND_CONNECT_TIMEOUT_LISTENER=180
##调整sqlnet.ora
vi $ORACLE_HOME/network/admin/sqlnet.ora
增加:
DIAG_ADR_ENABLED=OFF
SQLNET.INBOUND_CONNECT_TIMEOUT=180

这些已经配置,但是现在报12170 TNS-12535 TNS-00505错误,通过结合mos发现,出现该问题,可能是由于应用服务器和数据库服务器之间的防火墙策略设置不适合业务查询需求,出现应用服务器和数据库服务器防火墙超时（比如应用服务器发起一个大查询,在数据库服务器中执行,尚未返回结果,可是网络已经超时,终止会话)
补充知识点

The 'nt secondary err code' identifies the underlying network transport, such as (TCP/IP) timeout limit.
In the current case 60 identifies Windows underlying transport layer.
The "nt secondary err code" will be different based on the operating system:
Linux x86 or Linux x86-64: "nt secondary err code: 110"
HP-UX : "nt secondary err code: 238"
AIX: "nt secondary err code: 78"
Solaris: "nt secondary err code: 145"
The alert.log message indicates that a connection was terminated AFTER it was established to the instance.
In this case, it was terminated 2 hours and 3 minutes after the listener handed the connection to the database.
 This would indicate an issue with a firewall where a maximum idle time setting is in place.
The connection would not necessarily be "idle".  This issue can arise during a long running query
or when using JDBC Thin connection pooling. If there is no data 'on the wire' for lengthy
periods of time for any reason, the firewall might terminate the connection.

解决方案

The non-Oracle solution would be to remove or increase the firewall setting for maximum idle time.
In cases where this is not feasible, Oracle offers the following suggestion:
The following parameter, set at the **RDBMS_HOME/network/admin/sqlnet.ora, can resolve this kind of problem.
DCD or SQLNET.EXPIRE_TIME can mimic data transmission between the server and the client during long periods of idle time.
SQLNET.EXPIRE_TIME=n  Where <n> is a non-zero value set in minutes.
See the following : Note 257650.1 Resolving Problems with Connection Idle Timeout With Firewall

当然除下面数据库中解决外,还可以在网络防火墙层面解决,比如增加网络空闲终止时间等

具体参考:Alert Log Errors: 12170 TNS-12535/TNS-00505: Operation Timed Out (Doc ID 1628949.1)
Fatal NI Connect Error 12170, ‘TNS-12535: TNS:operation timed out’ Reported in 11g Alert Log (Doc ID 1286376.1)

ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled

在昨天11.2.0.2 for Linux 数据库恢复过程中,把数据文件从asm复制到单节点机器中恢复,在resetlogs过程中报如下ORA-38856错误

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled

ORA-38856 is the expected error during open database resetlogs when the set of enabled instances (redo threads) in the controlfile does not match the set of enabled instances (redo threads) in datafile checkpoint. This is expected behavior in a normal RAC restore/recover/open resetlogs situation.
这句话的意思是:数据库在resetlogs的时候发现控制文件中的redo threads和数据文件汇总的redo threads不一致,从而出现该问题.
在本次恢复中禁用了所有和thread 2相关参数,数据库依然报告错误,是因为数据库在异常恢复过程中需要读取节点2的redo信息,现在无法读取从而出现该错误.但是使用了_allow_resetlogs_corruption 之后还是报该错误,实在诡异.通过查询mos发现有类似Unpublished Bug 4355382 ORA-38856: FAILED TO OPEN DATABASE WITH RESETLOGS WHEN USING RAC BACKUP,虽然说该bug在10.2.0.3中修复,但是在异常恢复过程中,本着在风险可控的情况下,大胆尝试,继续使用_no_recovery_through_resetlogs,数据库正常resetlogs成功.
可以参考：RMAN Duplicate from RAC backup fails ORA-38856 (Doc ID 334899.1)

重建控制文件丢失数据文件导致悲剧

在Oracle职业生涯中，恢复过生产环境数据库也有几百个.对于Oracle恢复我还是相当的自信,今天因为自己的一时过于自信,对于环境错了错误的判断,简单问题复杂化,差点变成悲剧
数据库最初故障

Thu Sep 25 09:27:26 2014
MMON started with pid=15, OS id=1968
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
ORACLE_BASE from environment = F:\oracle
Thu Sep 25 09:27:26 2014
ALTER DATABASE   MOUNT
Thu Sep 25 09:27:26 2014
MMNL started with pid=16, OS id=5976
Errors in file f:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_4624.trc:
ORA-00202: ????: ''F:\ORACLE\ORADATA\ORCL\CONTROL01.CTL
ORA-27070: ????/????
OSD-04006: ReadFile() 失败, 无法读取文件
O/S-Error: (OS 23) 数据错误(循环冗余检查)。
Thu Sep 25 09:28:31 2014
ORA-204 signalled during: ALTER DATABASE   MOUNT...

因为硬件或者系统层面问题，导致控制文件无法正常访问

重建控制文件

Fri Sep 26 12:28:44 2014
Successful mount of redo thread 1, with mount id 1387065723
Completed: CREATE CONTROLFILE REUSE DATABASE "orcl" RESETLOGS ARCHIVELOG
    MAXLOGFILES 5
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 2
    MAXLOGHISTORY 226
LOGFILE
  GROUP 1 'F:\oracle\oradata\orcl\REDO01.LOG'  SIZE 50M,  --redo log ????
  GROUP 2 'F:\oracle\oradata\orcl\REDO02.LOG'  SIZE 50M,  --redo log ????
  GROUP 3 'F:\oracle\oradata\orcl\REDO03.LOG'  SIZE 50M  --redo log ????
-- STANDBY LOGFILE
DATAFILE
  'F:\oracle\oradata\orcl\SYSAUX01.DBF',  --sysaux???????
  'F:\oracle\oradata\orcl\SYSTEM01.DBF',
  'F:\oracle\oradata\orcl\USERS01.DBF', --user????????
  'F:\oracle\oradata\orcl\UNDOTBS01.DBF' --undo???????
CHARACTER SET ZHS16GBK
Fri Sep 26 12:29:55 2014
alter database open resetlogs
ORA-1194 signalled during: alter database open resetlogs...

埋下了雷，创建控制文件中未全部列举出来所有数据文件

进行不完全恢复,尝试resetlogs库发现redo异常

Fri Sep 26 14:13:24 2014
ALTER DATABASE   MOUNT
Fri Sep 26 14:13:24 2014
MMNL started with pid=16, OS id=9024
Successful mount of redo thread 1, with mount id 1387037444
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Fri Sep 26 14:14:08 2014
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
Fri Sep 26 14:15:16 2014
Errors in file f:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_3720.trc:
ORA-00333: 重做日志读取块 2049 计数 6143 出错
ORA-00312: 联机日志 1 线程 1: 'F:\ORACLE\ORADATA\ORCL\REDO01.LOG'
ORA-27070: 异步读取/写入失败
OSD-04016: 异步 I/O 请求排队时出错。
O/S-Error: (OS 23) 数据错误(循环冗余检查)。
Fri Sep 26 14:16:24 2014
Errors in file f:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_3720.trc:
ORA-00333: 重做日志读取块 1 计数 8191 出错
ORA-00312: 联机日志 1 线程 1: 'F:\ORACLE\ORADATA\ORCL\REDO01.LOG'
ORA-27070: 异步读取/写入失败
OSD-04006: ReadFile() 失败, 无法读取文件
O/S-Error: (OS 23) 数据错误(循环冗余检查)。
Errors in file f:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_3720.trc:
ORA-00333: 重做日志读取块 1 计数 8191 出错
ARCH: All Archive destinations made inactive due to error 333

使用隐含参数尝试拉库,报ORA-600[2662]

Fri Sep 26 14:16:45 2014
SMON: enabling cache recovery
Errors in file f:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_3720.trc  (incident=57761):
ORA-00600: 内部错误代码, 参数: [2662], [0], [38221304], [0], [38352371], [4194545], [], [], [], [], [], []
Incident details in: f:\oracle\diag\rdbms\orcl\orcl\incident\incdir_57761\orcl_ora_3720_i57761.trc
Fri Sep 26 14:16:45 2014
ARC3 started with pid=23, OS id=9692
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Errors in file f:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_3720.trc:
ORA-00704: 引导程序进程失败
ORA-00704: 引导程序进程失败
ORA-00600: 内部错误代码, 参数: [2662], [0], [38221304], [0], [38352371], [4194545], [], [], [], [], [], []
Errors in file f:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_3720.trc:
ORA-00704: 引导程序进程失败
ORA-00704: 引导程序进程失败
ORA-00600: 内部错误代码, 参数: [2662], [0], [38221304], [0], [38352371], [4194545], [], [], [], [], [], []
Error 704 happened during db open, shutting down database
USER (ospid: 3720): terminating the instance due to error 704
Instance terminated by USER, pid = 3720
ORA-1092 signalled during: alter database open resetlogs...
opiodr aborting process unknown ospid (3720) as a result of ORA-1092

数据库在未使用所有数据文件的情况下,进行了resetlogs操作,悲剧的本质已经注定,我的失误是没有评估好现状,还继续在错误的道路上越走越远.

我开始接手该库现况

Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Fri Sep 26 14:18:55 2014
alter database open
Errors in file f:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_8968.trc:
ORA-01113: 文件 1 需要介质恢复
ORA-01110: 数据文件 1: 'F:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF'
ORA-1113 signalled during: alter database open...
Fri Sep 26 14:19:31 2014
alter database open
Errors in file f:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_8968.trc:
ORA-01113: 文件 1 需要介质恢复
ORA-01110: 数据文件 1: 'F:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF'
ORA-1113 signalled during: alter database open ...
Fri Sep 26 14:22:26 2014
ALTER DATABASE RECOVER  database
Media Recovery Start
 started logmerger process
Fri Sep 26 14:22:26 2014
Media Recovery failed with error 16433
Recovery Slave PR00 previously exited with exception 283
ORA-283 signalled during: ALTER DATABASE RECOVER  database  ...
Fri Sep 26 14:24:25 2014
ALTER DATABASE RECOVER  datafile 'F:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF'
Media Recovery Start
Media Recovery failed with error 16433
ORA-283 signalled during: ALTER DATABASE RECOVER  datafile 'F:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF'  ...
Fri Sep 26 14:28:47 2014
alter database open read write
Errors in file f:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_8968.trc:
ORA-01113: 文件 1 需要介质恢复
ORA-01110: 数据文件 1: 'F:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF'
ORA-1113 signalled during: alter database open read write...
Fri Sep 26 14:31:48 2014
ALTER DATABASE RECOVER  datafile 'F:\oracle\oradata\orcl\SYSTEM01.DBF'
Media Recovery Start
Media Recovery failed with error 16433
ORA-283 signalled during: ALTER DATABASE RECOVER  datafile 'F:\oracle\oradata\orcl\SYSTEM01.DBF'  ...

提示ORA-01110: 数据文件 1需要恢复,尝试recover操作

尝试recover操作

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> recover database ;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.
SQL> alter database backup controlfile to trace as 'd:\ctl.txt';
alter database backup controlfile to trace as 'd:\ctl.txt'
*
第 1 行出现错误:
ORA-16433: 必须以读/写模式打开数据库。
SQL> recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.

重建控制文件

SQL> shutdown immediate;
ORA-01109: 数据库未打开
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> STARTUP NOMOUNT
ORACLE 例程已经启动。
Total System Global Area  970895360 bytes
Fixed Size                  1375452 bytes
Variable Size             603980580 bytes
Database Buffers          360710144 bytes
Redo Buffers                4829184 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE orcl NORESETLOGS FORCE LOGGING ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 2921
  7  LOGFILE
  8    GROUP 1 'F:\ORACLE\ORADATA\ORCL\REDO01.LOG'  SIZE 50M,
  9    GROUP 2 'F:\ORACLE\ORADATA\ORCL\REDO02.LOG'  SIZE 50M,
 10    GROUP 3 'F:\ORACLE\ORADATA\ORCL\REDO03.LOG'  SIZE 50M
 11  DATAFILE
 12    'F:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF',
 13    'F:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF',
 14    'F:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF',
 15    'F:\ORACLE\ORADATA\ORCL\USERS01.DBF'
 16  CHARACTER SET ZHS16GBK
 17  ;
控制文件已创建。

这一步严重发错,在恢复前未认真看alert日志,太依赖v$datafile查询出来结果,导致重建控制文件丢失数据文件,埋下大雷。根据前面alert日志报错ORA-600 2662,决定一并处理该问题,然后进行恢复

SQL> shutdown immediate;
ORA-01109: ??????
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup pfile='d:\pfile.txt'  mount;
ORACLE 例程已经启动。
Total System Global Area  970895360 bytes
Fixed Size                  1375452 bytes
Variable Size             603980580 bytes
Database Buffers          360710144 bytes
Redo Buffers                4829184 bytes
数据库装载完毕。
SQL> recover database;
完成介质恢复。
SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [4194], [], [

数据库报ORA-600 4194,直接修改undo_management=manual,然后尝试启动数据库

SQL> conn / as sysdba
已连接到空闲例程。
SQL> startup pfile='d:\pfile.txt'
ORACLE 例程已经启动。
Total System Global Area  970895360 bytes
Fixed Size                  1375452 bytes
Variable Size             603980580 bytes
Database Buffers          360710144 bytes
Redo Buffers                4829184 bytes
数据库装载完毕。
数据库已经打开。
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
F:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
F:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF
F:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
F:\ORACLE\ORADATA\ORCL\USERS01.DBF
F:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00005
F:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00006
已选择6行。
SQL> alter database rename file 'F:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00005'
2   to 'F:\oracle\oradata\SOURCE_DATA1.DBF';
数据库已更改。
SQL> alter database rename file 'F:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00006'
2   to 'F:\oracle\oradata\SOURCE_idx1.DBF';
数据库已更改。
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount pfile='d:\pfile.txt'
ORACLE 例程已经启动。
Total System Global Area  970895360 bytes
Fixed Size                  1375452 bytes
Variable Size             603980580 bytes
Database Buffers          360710144 bytes
Redo Buffers                4829184 bytes
数据库装载完毕。
SQL> alter datafile 5 online;
alter datafile 5 online
      *
第 1 行出现错误:
ORA-00940: 无效的 ALTER 命令
SQL> alter database datafile 5 online;
数据库已更改。
SQL> alter database datafile 6 online;
数据库已更改。
SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 5 belongs to an orphan incarnation
ORA-01110: data file 5: 'F:\ORACLE\ORADATA\SOURCE_DATA1.DBF'
SQL> alter database open resetlogs;
alter database open resetlogs
*
第 1 行出现错误:
ORA-01139: RESETLOGS 选项仅在不完全数据库恢复后有效
SQL> alter database datafile 6 offline;
数据库已更改。
SQL> alter database datafile 5 offline;
数据库已更改。
SQL> recover database until cancel;
完成介质恢复。
SQL> alter database datafile 6 online;
数据库已更改。
SQL> alter database datafile 5 online;
数据库已更改。
SQL> alter database open resetlogs;
数据库已更改。

还好结合一些隐含参数侥幸恢复成功,差点到了要使用bbed的程度

这次的恢复告诉我:Oracle数据库恢复千万比大意,需要认真分析alert日志和咨询客户做了那些操作,不然可能导致万劫不复之禁地

ORA-600 2663 故障恢复

朋友数据库启动遭遇ORA-00600[2663]

Mon Sep 22 19:24:20 2014
Thread 1 advanced to log sequence 17 (thread open)
Thread 1 opened at log sequence 17
  Current log# 17 seq# 17 mem# 0: /u02/orayali2/redo17.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Sep 22 19:24:20 2014
SMON: enabling cache recovery
Errors in file /u01/app/oracle/diag/rdbms/orayali2/orayali2/trace/orayali2_ora_20722.trc  (incident=336180):
ORA-00600: internal error code, arguments: [2663], [13], [3140023138], [13], [3141216403], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orayali2/orayali2/incident/incdir_336180/orayali2_ora_20722_i336180.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/orayali2/orayali2/trace/orayali2_ora_20722.trc:
ORA-00600: internal error code, arguments: [2663], [13], [3140023138], [13], [3141216403], [], [], [], [], [], [], []
Errors in file /u01/app/oracle/diag/rdbms/orayali2/orayali2/trace/orayali2_ora_20722.trc:
ORA-00600: internal error code, arguments: [2663], [13], [3140023138], [13], [3141216403], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 20722): terminating the instance due to error 600
Instance terminated by USER, pid = 20722
ORA-1092 signalled during: alter database open...
opiodr aborting process unknown ospid (20722) as a result of ORA-1092
Mon Sep 22 19:24:24 2014
ORA-1092 : opitsk aborting process

ORA-600[2663]与常见的ORA-600[2662]类似,都是由于block的scn大于文件头的scn导致,只不过错误的对象不一样而已.对于该类问题,我们的处理方法一般就是简单的推scn,但是这个库比较特殊11.2.0.3.5版本,一般方法无法推scn,因为收集操作日志有限，贴出核心操作步骤

[oracle@orayali2 OPatch]$ uname -a
Linux orayali2 2.6.32-279.el6.x86_64 #1 SMP Wed Jun 13 18:24:36 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux
[oracle@orayali2 OPatch]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Sep 22 19:09:18 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1.6535E+10 bytes
Fixed Size                  2244792 bytes
Variable Size            9898561352 bytes
Database Buffers         6610223104 bytes
Redo Buffers               24256512 bytes
Database mounted.
SQL> oradebug setmypid
Statement processed.
SQL>  oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [060019598, 0600195C8) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 60019278 00000000
SQL> oradebug poke 0x060019598 8 0x0000000000000040
BEFORE: [060019598, 0600195A0) = 00000000 00000000
AFTER:  [060019598, 0600195A0) = 00000040 00000000
SQL> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [060019598, 0600195C8) = 00000040 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 60019278 00000000
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-30012: undo tablespace 'SYSTEM' does not exist or of wrong type
Process ID: 21174
Session ID: 1563 Serial number: 3

现在错误已经改变,而是出现了ORA-30012的错误

alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 31 processes
Started redo scan
Completed redo scan
 read 4 KB redo, 0 data blocks need recovery
Started redo application at
 Thread 1: logseq 17, block 2, scn 58974597984
Recovery of Online Redo Log: Thread 1 Group 17 Seq 17 Reading mem 0
  Mem# 0: /u02/orayali2/redo17.log
Completed redo application of 0.00MB
Completed crash recovery at
 Thread 1: logseq 17, block 3, scn 58974617986
 0 data blocks read, 0 data blocks written, 4 redo k-bytes read
Mon Sep 22 19:30:05 2014
Thread 1 advanced to log sequence 18 (thread open)
Thread 1 opened at log sequence 18
  Current log# 18 seq# 18 mem# 0: /u02/orayali2/redo18.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Sep 22 19:30:05 2014
SMON: enabling cache recovery
Undo initialization errored: err:30012 serial:0 start:1143146928 end:1143147338 diff:410 (4 seconds)
Errors in file /u01/app/oracle/diag/rdbms/orayali2/orayali2/trace/orayali2_ora_21174.trc:
ORA-30012: undo tablespace 'SYSTEM' does not exist or of wrong type
Errors in file /u01/app/oracle/diag/rdbms/orayali2/orayali2/trace/orayali2_ora_21174.trc:
ORA-30012: undo tablespace 'SYSTEM' does not exist or of wrong type
Error 30012 happened during db open, shutting down database
USER (ospid: 21174): terminating the instance due to error 30012
Instance terminated by USER, pid = 21174
ORA-1092 signalled during: alter database open...
opiodr aborting process unknown ospid (21174) as a result of ORA-1092
Mon Sep 22 19:30:08 2014
ORA-1092 : opitsk aborting process

猜测原因是undo设置有问题导致,检查果然发现undo_management=auto,而undo_tablespace=SYSTEM

SQL> startup mount
ORACLE instance started.
Total System Global Area 1.6535E+10 bytes
Fixed Size                  2244792 bytes
Variable Size            9898561352 bytes
Database Buffers         6610223104 bytes
Redo Buffers               24256512 bytes
Database mounted.
SQL> show parameter undo;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     10800
undo_tablespace                      string      SYSTEM
SQL> alter system set undo_management=manual scope=spfile;
System altered.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1.6535E+10 bytes
Fixed Size                  2244792 bytes
Variable Size            9898561352 bytes
Database Buffers         6610223104 bytes
Redo Buffers               24256512 bytes
Database mounted.
Database opened.

解决该问题修改undo_management=manual即可

因用户错误创建index报ORA-01129错误

数据库版本

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for HPUX: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

报ORA-01129错误

SQL> create index SERIVCE.ind_xifenfei on SERVICE.t_user(create_date);
create index SERIVCE.ind_xifenfei on SERVICE.t_user(create_date)
                                         *
ERROR at line 1:
ORA-01129: user's default or temporary tablespace does not exist

查询表空间信息

SQL> select TABLESPACE_NAME,CONTENTS,STATUS from dba_tablespaces where TABLESPACE_NAME in ('SERVICE','TEMP');
TABLESPACE_NAME                CONTENTS  STATUS
------------------------------ --------- ---------
SERVICE                        PERMANENT ONLINE
TEMP                           TEMPORARY ONLINE

通过分析,证明相关的表空间都存在,进一步检查sql语句,发现SERVICE被错误的书写为了SERIVCE,检查SERIVCE用户.

SQL> select count(*) from dba_users where username='SERIVCE';
  COUNT(*)
----------
         0

通过分析,可以知道是因为index对应的用户不存在,从而出现了ORA-01129的错误,按道理应该报ORA-01918,而不是ORA-01129.查询MOS发现Bug 17058847 Creating index in non existing schema results in ORA-1129 and not ORA-1918
bug 17058847

bbed简单替换block测试

有朋友在我论坛中提问copy一个块，如何在前台显示其中数据,前段时间比较忙,没有及时答复该问题,今天通过试验方式进行了测试说明,本试验简单,仅是同一个数据文件中的同一个对象中的两个block进行了替换
创建测试表

SQL> conn chf/xifenfei
Connected.
SQL> create table t_xifenfei as select * from dba_objects;
Table created.
SQL> select EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where segment_name='T_XIFENFEI' AND OWNER='CHF';
 EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ---------- ----------
         0          4        176          8
         1          4        184          8
         2          4        192          8
         3          4        200          8
         4          4        208          8
         5          4        216          8
         6          4        224          8
         7          4        232          8
         8          4        240          8
         9          4        248          8
        10          4        256          8
 EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ---------- ----------
        11          4        264          8
        12          4        272          8
        13          4        280          8
        14          4        288          8
        15          4        296          8
        16          4        384        128
        17          4        512        128
        18          4        640        128
        19          4        768        128
        20          4        896        128
        21          4       1024        128
 EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ---------- ----------
        22          4       1152        128
        23          4       1280        128
        24          4       1408        128
25 rows selected.
SQL>  select * from (select distinct dbms_rowid.rowid_relative_fno(rowid),
  2  dbms_rowid.rowid_block_number(rowid) from t_xifenfei ORDER BY 2 )where rownum<5 ;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
                                   4                                  179
                                   4                                  180
                                   4                                  181
                                   4                                  182

查询file 4 block 180 数据情况

SQL> select object_id from t_xifenfei where  dbms_rowid.rowid_relative_fno(rowid)=4
    2 and dbms_rowid.rowid_block_number(rowid)=180;
 OBJECT_ID
----------
        81
        82
        83
        84
        85
        86
        87
        88
        89
        90
        91
 OBJECT_ID
----------
        92
        93
        94
        95
        96
        97
        98
        99
       100
       101
       102
 OBJECT_ID
----------
       103
       104
       105
       106
       107
       108
       109
       110
       111
       112
       113
 OBJECT_ID
----------
       114
       115
       116
       117
       118
       119
       120
       121
       122
       123
       124
 OBJECT_ID
----------
       125
       126
       127
       129
       128
       130
       131
       132
       133
       134
       135
 OBJECT_ID
----------
       137
       136
       138
       139
       140
       141
       142
       143
       144
       145
       146
 OBJECT_ID
----------
       147
       148
       149
       150
       151
       153
       152
       154
       155
       156
76 rows selected.

查询file 4 block 181 数据情况

SQL> select object_id from t_xifenfei where  dbms_rowid.rowid_relative_fno(rowid)=4
   2 and dbms_rowid.rowid_block_number(rowid)=181;
 OBJECT_ID
----------
       157
       158
       159
       160
       161
       162
       163
       164
       165
       166
       167
 OBJECT_ID
----------
       168
       169
       170
       171
       172
       173
       174
       175
       176
       177
       178
 OBJECT_ID
----------
       179
       180
       181
       182
       183
       184
       185
       186
       187
       188
       189
 OBJECT_ID
----------
       190
       191
       192
       193
       194
       195
       196
       197
       198
       199
       200
 OBJECT_ID
----------
       201
       202
       203
       204
       205
       206
       208
       207
       209
       210
       211
 OBJECT_ID
----------
       212
       213
       214
       215
       216
       217
       218
       219
       220
       221
       222
 OBJECT_ID
----------
       223
       224
       225
       226
       227
       228
       229
       230
       231
75 rows selected.

定位file 4 文件名

SQL> select name from v$datafile where file#=4;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/users01.dbf

bbed 替换file 4 block 180到file 4 block 181

[oracle@oel6 ~]$ bbed filename='/u01/app/oracle/oradata/ORCL/users01.dbf' mode=edit blocksize=8192
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Wed Aug 6 21:17:11 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> show all
        FILE#           0
        BLOCK#          1
        OFFSET          0
        DBA             0x00000000 (0 0,1)
        FILENAME        /u01/app/oracle/oradata/ORCL/users01.dbf
        BIFILE          bifile.bbd
        LISTFILE
        BLOCKSIZE       8192
        MODE            Edit
        EDIT            Unrecoverable
        IBASE           Dec
        OBASE           Dec
        WIDTH           80
        COUNT           512
        LOGFILE         log.bbd
        SPOOL           No
BBED> map
 File: /u01/app/oracle/oradata/ORCL/users01.dbf (0)
 Block: 180                                   Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Table/Cluster)
 struct kcbh, 20 bytes                      @0
 struct ktbbh, 96 bytes                     @20
 struct kdbh, 14 bytes                      @124
 struct kdbt[1], 4 bytes                    @138
 sb2 kdbr[76]                               @142
 ub1 freespace[856]                         @294
 ub1 rowdata[7038]                          @1150
 ub4 tailchk                                @8188
BBED> p kcbh
struct kcbh, 20 bytes                       @0
   ub1 type_kcbh                            @0        0x06
   ub1 frmt_kcbh                            @1        0xa2
   ub1 spare1_kcbh                          @2        0x00
   ub1 spare2_kcbh                          @3        0x00
   ub4 rdba_kcbh                            @4        0x010000b4
   ub4 bas_kcbh                             @8        0x000b258a
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0x02
   ub1 flg_kcbh                             @15       0x04 (KCBHFCKV)
   ub2 chkval_kcbh                          @16       0x0eb6
   ub2 spare3_kcbh                          @18       0x0000
BBED> p kcbh block 181
struct kcbh, 20 bytes                       @0
   ub1 type_kcbh                            @0        0x06
   ub1 frmt_kcbh                            @1        0xa2
   ub1 spare1_kcbh                          @2        0x00
   ub1 spare2_kcbh                          @3        0x00
   ub4 rdba_kcbh                            @4        0x010000b5
   ub4 bas_kcbh                             @8        0x000b258a
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0x02
   ub1 flg_kcbh                             @15       0x04 (KCBHFCKV)
   ub2 chkval_kcbh                          @16       0xa1c5
   ub2 spare3_kcbh                          @18       0x0000
BBED> copy block 180 to block 181
 File: /u01/app/oracle/oradata/ORCL/users01.dbf (0)
 Block: 181              Offsets:    0 to  511           Dba:0x00000000
------------------------------------------------------------------------
 06a20000 b4000001 8a250b00 00000204 b60e0000 01000000 252d0100 84250b00
 00000000 03003200 b0000001 ffff0000 00000000 00000000 00000000 00800000
 84250b00 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00014c00
 ffffaa00 02045803 58030000 4c00221f c21e661e 0e1eb01d 521df81c 9e1c421c
 ea1b901b 361be11a 8c1a361a da198019 2419c818 73181918 b9176017 0717ae16
 5416f815 a2154915 f0149614 3914dc13 7f131f13 c8126e12 1412ba11 61110511
 a9104f10 f20f900f 370fdc0e 710e070e a60d430d df0c850c 2a0ccf0b 640bfa0a
 9d0a400a e6098509 2e09d408 79081e08 c3076607 0907a206 3806e005 75050b05
 b0045a04 02040000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 <32 bytes per line>
BBED> p kcbh block 181
struct kcbh, 20 bytes                       @0
   ub1 type_kcbh                            @0        0x06
   ub1 frmt_kcbh                            @1        0xa2
   ub1 spare1_kcbh                          @2        0x00
   ub1 spare2_kcbh                          @3        0x00
   ub4 rdba_kcbh                            @4        0x010000b4
   ub4 bas_kcbh                             @8        0x000b258a
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0x02
   ub1 flg_kcbh                             @15       0x04 (KCBHFCKV)
   ub2 chkval_kcbh                          @16       0x0eb6
   ub2 spare3_kcbh                          @18       0x0000
BBED> p rdba_kcbh
ub4 rdba_kcbh                               @4        0x010000b4
BBED> d
 File: /u01/app/oracle/oradata/ORCL/users01.dbf (0)
 Block: 181              Offsets:    4 to  515           Dba:0x00000000
------------------------------------------------------------------------
 b4000001 8a250b00 00000204 b60e0000 01000000 252d0100 84250b00 00000000
 03003200 b0000001 ffff0000 00000000 00000000 00000000 00800000 84250b00
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00014c00 ffffaa00
 02045803 58030000 4c00221f c21e661e 0e1eb01d 521df81c 9e1c421c ea1b901b
 361be11a 8c1a361a da198019 2419c818 73181918 b9176017 0717ae16 5416f815
 a2154915 f0149614 3914dc13 7f131f13 c8126e12 1412ba11 61110511 a9104f10
 f20f900f 370fdc0e 710e070e a60d430d df0c850c 2a0ccf0b 640bfa0a 9d0a400a
 e6098509 2e09d408 79081e08 c3076607 0907a206 3806e005 75050b05 b0045a04
 02040000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 <32 bytes per line>
BBED> set count 32
        COUNT           32
BBED> d
 File: /u01/app/oracle/oradata/ORCL/users01.dbf (0)
 Block: 181              Offsets:    4 to   35           Dba:0x00000000
------------------------------------------------------------------------
 b4000001 8a250b00 00000204 b60e0000 01000000 252d0100 84250b00 00000000
 <32 bytes per line>
BBED> m /x b5
 File: /u01/app/oracle/oradata/ORCL/users01.dbf (0)
 Block: 181              Offsets:    4 to   35           Dba:0x00000000
------------------------------------------------------------------------
 b5000001 8a250b00 00000204 b60e0000 01000000 252d0100 84250b00 00000000
 <32 bytes per line>
BBED> sum apply
Check value for File 0, Block 181:
current = 0x0eb7, required = 0x0eb7
BBED> p kcbh block 181
struct kcbh, 20 bytes                       @0
   ub1 type_kcbh                            @0        0x06
   ub1 frmt_kcbh                            @1        0xa2
   ub1 spare1_kcbh                          @2        0x00
   ub1 spare2_kcbh                          @3        0x00
   ub4 rdba_kcbh                            @4        0x010000b5
   ub4 bas_kcbh                             @8        0x000b258a
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0x02
   ub1 flg_kcbh                             @15       0x04 (KCBHFCKV)
   ub2 chkval_kcbh                          @16       0x0eb7
   ub2 spare3_kcbh                          @18       0x0000

验证替换后的file 4 block 181

SQL>  select object_id from t_xifenfei where  dbms_rowid.rowid_relative_fno(rowid)=4
   2  and dbms_rowid.rowid_block_number(rowid)=181;
 OBJECT_ID
----------
        81
        82
        83
        84
        85
        86
        87
        88
        89
        90
        91
 OBJECT_ID
----------
        92
        93
        94
        95
        96
        97
        98
        99
       100
       101
       102
 OBJECT_ID
----------
       103
       104
       105
       106
       107
       108
       109
       110
       111
       112
       113
 OBJECT_ID
----------
       114
       115
       116
       117
       118
       119
       120
       121
       122
       123
       124
 OBJECT_ID
----------
       125
       126
       127
       129
       128
       130
       131
       132
       133
       134
       135
 OBJECT_ID
----------
       137
       136
       138
       139
       140
       141
       142
       143
       144
       145
       146
 OBJECT_ID
----------
       147
       148
       149
       150
       151
       153
       152
       154
       155
       156
76 rows selected.

通过替换block 180的block到181,查询block 181和180数据相同,证明替换block成功

创建控制文件出现ORA-01565 ORA-27041 OSD-04002

oracle 在win平台上创建控制文件可能会出现ORA-01565 ORA-27041 OSD-04002错误

C:\Users\feicheng>sqlplus  / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on 星期六 9月 13 16:20:38 2014
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup nomount;
ORACLE 例程已经启动。
Total System Global Area  400846848 bytes
Fixed Size                  2281656 bytes
Variable Size             188747592 bytes
Database Buffers          201326592 bytes
Redo Buffers                8491008 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "XFF" NORESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 'D:\ORACLE\ORADATA\XFF\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 'D:\ORACLE\ORADATA\XFF\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 'D:\ORACLE\ORADATA\XFF\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
 11  DATAFILE
 12    'D:\ORACLE\ORADATA\XFF\SYSTEM01.DBF',
 13    'D:\ORACLE\ORADATA\XFF\SYSAUX01.DBF',
 14    'D:\ORACLE\ORADATA\XFF\UNDOTBS01.DBF',
 15    'D:\惜分飞\USERS01.DBF'
 16  CHARACTER SET ZHS16GBK
 17  ;
CREATE CONTROLFILE REUSE DATABASE "XFF" NORESETLOGS  NOARCHIVELOG
*
第 1 行出现错误:
ORA-01503: CREATE CONTROLFILE ??
ORA-01565: ???? 'D:\???\USERS01.DBF' ???
ORA-27041: ??????
OSD-04002: ????????????
O/S-Error: (OS 123) ????????????????????????????????

alert日志对应错误提示为

Sat Sep 13 16:27:48 2014
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
ORACLE_BASE from environment = D:\oracle
Sat Sep 13 16:28:11 2014
CREATE CONTROLFILE REUSE DATABASE "XFF" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'D:\ORACLE\ORADATA\XFF\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 'D:\ORACLE\ORADATA\XFF\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 'D:\ORACLE\ORADATA\XFF\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
DATAFILE
  'D:\ORACLE\ORADATA\XFF\SYSTEM01.DBF',
  'D:\ORACLE\ORADATA\XFF\SYSAUX01.DBF',
  'D:\ORACLE\ORADATA\XFF\UNDOTBS01.DBF',
  'D:\???\USERS01.DBF'
CHARACTER SET ZHS16GBK
WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command
Default Temporary Tablespace will be necessary for a locally managed database in future release
Errors in file D:\ORACLE\diag\rdbms\xff\xff\trace\xff_ora_8136.trc:
ORA-01565: ???? 'D:\???\USERS01.DBF' ???
ORA-27041: ??????
OSD-04002: 无法打开文件
O/S-Error: (OS 123) 文件名、目录名或卷标语法不正确。
ORA-1503 signalled during: CREATE CONTROLFILE REUSE DATABASE "XFF" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'D:\ORACLE\ORADATA\XFF\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 'D:\ORACLE\ORADATA\XFF\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 'D:\ORACLE\ORADATA\XFF\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
DATAFILE
  'D:\ORACLE\ORADATA\XFF\SYSTEM01.DBF',
  'D:\ORACLE\ORADATA\XFF\SYSAUX01.DBF',
  'D:\ORACLE\ORADATA\XFF\UNDOTBS01.DBF',
  'D:\???\USERS01.DBF'
CHARACTER SET ZHS16GBK

ORA-01565 ORA-27041 OSD-04002的含义大致为:在创建控制文件的时候,有数据文件无法不存在.
另外在alert日志里面也可以看到,sqlplus中的”D:\惜分飞\USERS01.DBF”变为了”D:\???\USERS01.DBF”导致无法定位到数据文件,从而在创建数据文件之时出现ORA-01565 ORA-27041 OSD-04002错误.
解决放方法:
1.创建控制文件语句中不含中文

C:\Users\feicheng>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on 星期六 9月 13 16:32:09 2014
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
已连接到空闲例程。
SQL> STARTUP NOMOUNT
ORACLE 例程已经启动。
Total System Global Area  400846848 bytes
Fixed Size                  2281656 bytes
Variable Size             188747592 bytes
Database Buffers          201326592 bytes
Redo Buffers                8491008 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "XFF" NORESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 'D:\ORACLE\ORADATA\XFF\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 'D:\ORACLE\ORADATA\XFF\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 'D:\ORACLE\ORADATA\XFF\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
 11  DATAFILE
 12    'D:\ORACLE\ORADATA\XFF\SYSTEM01.DBF',
 13    'D:\ORACLE\ORADATA\XFF\SYSAUX01.DBF',
 14    'D:\ORACLE\ORADATA\XFF\UNDOTBS01.DBF',
 15    'D:\xifenfei\USERS01.DBF'
 16  CHARACTER SET ZHS16GBK
 17  ;
控制文件已创建。

2.设置nls_lang为american_america.ZHS16GBK

C:\Users\feicheng>set NLS_LANG=american_america.ZHS16GBK
C:\Users\feicheng>sqlplus  / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Sep 13 16:29:04 2014
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> CREATE CONTROLFILE REUSE DATABASE "XFF" NORESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 'D:\ORACLE\ORADATA\XFF\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 'D:\ORACLE\ORADATA\XFF\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 'D:\ORACLE\ORADATA\XFF\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
 11  DATAFILE
 12    'D:\ORACLE\ORADATA\XFF\SYSTEM01.DBF',
 13    'D:\ORACLE\ORADATA\XFF\SYSAUX01.DBF',
 14    'D:\ORACLE\ORADATA\XFF\UNDOTBS01.DBF',
 15    'D:\惜分飞\USERS01.DBF'
 16  CHARACTER SET ZHS16GBK
 17  ;
Control file created.

此时alert日志提示

Sat Sep 13 16:29:22 2014
CREATE CONTROLFILE REUSE DATABASE "XFF" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'D:\ORACLE\ORADATA\XFF\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 'D:\ORACLE\ORADATA\XFF\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 'D:\ORACLE\ORADATA\XFF\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
DATAFILE
  'D:\ORACLE\ORADATA\XFF\SYSTEM01.DBF',
  'D:\ORACLE\ORADATA\XFF\SYSAUX01.DBF',
  'D:\ORACLE\ORADATA\XFF\UNDOTBS01.DBF',
  'D:\惜分飞\USERS01.DBF'
CHARACTER SET ZHS16GBK
WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command
Default Temporary Tablespace will be necessary for a locally managed database in future release
Sat Sep 13 16:29:25 2014
Successful mount of redo thread 1, with mount id 3507744098
Completed: CREATE CONTROLFILE REUSE DATABASE "XFF" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'D:\ORACLE\ORADATA\XFF\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 'D:\ORACLE\ORADATA\XFF\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 'D:\ORACLE\ORADATA\XFF\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
DATAFILE
  'D:\ORACLE\ORADATA\XFF\SYSTEM01.DBF',
  'D:\ORACLE\ORADATA\XFF\SYSAUX01.DBF',
  'D:\ORACLE\ORADATA\XFF\UNDOTBS01.DBF',
  'D:\惜分飞\USERS01.DBF'
CHARACTER SET ZHS16GBK

通过此实验简单说明:在oracle使用该过程中,尽可能少用中文路径或者文件名