ORACLE Error

oracle official error explanation 

http://docs.oracle.com/cd/E11882_01/server.112/e17766/toc.htm

从服务器下载ALERT日志文件，传输方式需要选择"ASCII"

ORA-01078: failure in processing system parameters

发生场景：新建数据库第一次启动时

SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/home/oracle/oracle/product/10.2.0/db_1/dbs/initorcl.ora’

原因：oracle_sid没有配置正确

解决方案：

1. 修改oracle用户.bash_profile文件中ORACLE_SID后的内容；

2. 重新已oracle用户登录；

ORA-01157: cannot identify/lock data file string - see DBWR trace file Cause: The background process was either unable to find one of the data files or failed to lock it because the file was already in use. The database will prohibit access to this file but other files will be unaffected. However the first instance to open the database will need to access all online data files. Accompanying error from the operating system describes why the file could not be identified.
Action: Have operating system make file available to database. Then either open the database or do ALTER SYSTEM CHECK DATAFILES.

ORA-01157: cannot identify/lock data file 201 – see DBWR trace file 
ORA-01110: data file 201: ‘/u01/app/oracle/oradata/orcl/temp01.dbf’

Cause：The tempfile belonging to the default temporary tablespace is missing.

ORA-03114: not connected to ORACLE

Errors in file /data1/admin/switch/bdump/switch_arc0_3887.trc:
Cause：备库实例或监听可能临时停止，不能接收归档

ORA-16055: FAL request rejectedARCH: Connecting to console port...

Cause：备库实例或监听可能临时停止，不能接收归档

ORA-12535: TNS:operation timed out
ARC0: FAL archive failed, see trace file.
ARCH: FAL archive failed. Archiver continuing

Cause：备库实例或监听可能临时停止，不能接收归档

ORA-03113: end-of-file on communication channelARC2: FAL archive failed, see trace file.
ARCH: FAL archive failed. Archiver continuing

Cause：备库实例或监听可能临时停止，无法创建备库归档

ORA-12012: error on auto execute of job 47

Cause：执行oracle任务47时报错

WARNING: inbound connection timed out (ORA-3136)

1.set INBOUND_CONNECT_TIMEOUT_ =0 in listener.ora

2.set SQLNET.INBOUND_CONNECT_TIMEOUT = 0 in sqlnet.ora of server.

3.stop and start both listener and database.

4.Now try to connect to DB and observe the behaviour

 

ORA-1578 encountered when generating server alert SMG-4121

 

cause: 数据库数据文件存在坏块

 

检查：select * from v$database_block_currption;

 

ORA-00904:	string: invalid identifier
Cause:	The column name entered is either missing or invalid.
Action:	Enter a valid column name. A valid column name must begin with a letter, be less than or equal to 30 characters, and consist of only alphanumeric characters and the special characters $, _, and #. If it contains other characters, then it must be enclosed in double quotation marks. It may not be a reserved word.

问题现象：在某表中使用查询语句，结果报某个字段不能识别错误ORA-00904: "callid": invalid identifier，但是使用desc显示时却存在该字段，然后将查询语句更换成加双引号，执行即可。

ORA-12537: TNS:connection closed (DBD ERROR: OCIServerAttach)

解决方案：在sqlnet.ora文件中将tcp.validnode_checking的值设为no，然后重启服务

ORA-09925: Unable to create audit trail fileLinux-x86_64 Error: 13: Permission denied
Additional information: 9925

解决方案： oracle 文件权限不对

chmod 6755 $ORACLE_HOME/bin/oracle

-rwsr-sr-x. 1 oracle oinstall 112701117 Dec  6 17:28 /u01/app/oracle/product/10.2.0/db_1/bin/oracle

 

本地无法登录，修改listener.ora, tnsname.ora文件

 

DCD 在SQLNET.ORA文件中添加：sqlnet.expire_time=10

作用：用来侦测没有操作的session，并自动清理。

 

所有用户不能正常使用，但是sys,system均可以使用

原因：数据库因为有执行emca操作进入静默状态

查看数据库状态：select instance_name,status,logins,active_state from v$instance;

其中active_state为quiescing表示数据库处于quiesce状态。

解决方案：

解除静默状态：alter system unquiesce;

如果报错：ORA-25502，检查session表：

select sid,sql_id,username,event from v$session where status='ACTIVE' and event like 'wait for possible quiesce finish';

select  sid, serial#  from v$session where sid='<sid>';

alter system kill session '<sid>,<serial#>';

查看系统状态：select instance_name,status,logins,active_state from v$instance;

其中active_state为normal为正常。

 

错误信息：

released channel: c1

RMAN-08132: WARNING: cannot update recovery area reclaimable file list

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of REFAF command on default channel at 04/12/2012 04:24:48

ORA-00604: error occurred at recursive SQL level 1

ORA-01219: database not open: queries allowed on fixed tables/views only

解决方案：ALTER SYSTEM SET optimizer_secure_view_merging = FALSE;

 

 

错误信息：

在服务器登录数据库：sqlplus sys/xxxx@xxx as sysdbas 是报错 insufficient privilege

原因：

1. 没有密码文件，或者密码文件损坏；

2. 监听器没有启动，或者数据库没有注册；

 

解决方案：

1. 启动监听器：

lsnrctl start

 

2. 注册数据库：

sqlplus / as sysba

alter system register;

 

3. 重建密码文件：

重建：orapwd file=文件名 password=密码 entries=最大连接个数 force=是否覆盖现有文件

例如：orapwd file=orapworcl password=password entries=30 force=y
错误信息：
使用dbca工具创建数据库报错：/etc/oratab文件无法访问，检查/etc下面没有oratab文件
解决方案：root用户执行$ORACLE_HOME下面的root.sh程序

错误信息：

解决方案：给/bin下面make文件777权限

 

ORA-00031: session marked for kill 处理Oracle中杀不掉的锁 

解决方案：

select spid, osuser, s.program 
from v$session s,v$process p
where s.paddr=p.addr and s.sid=24

根据spid到系统中kill -9 <spid>

 

ORA-00604 error occurred at recursive SQL level 2

解决方案：重建数据库

 

错误描述：oracle11g 用户密码过期

 

1、查看用户的proifle是哪个，一般是default：

sql>SELECT username,PROFILE FROM dba_users;

2、查看指定概要文件（如default）的密码有效期设置：

sql>SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';

3、将密码有效期由默认的180天修改成“无限制”：

sql>ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

修改之后不需要重启动数据库，会立即生效。

4、修改后，还没有被提示ORA-28002警告的帐户不会再碰到同样的提示；

 

错误描述：oracle11g用户密码登录错误次数过多

1、查看用户的proifle是哪个，一般是default：

sql>SELECT username,PROFILE FROM dba_users;

2、查看指定概要文件（如default）的密码有效期设置：

sql>SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='FAILED_LOGIN_ATTEMPTS';

3、将尝试登录失败次数由默认的10次修改成“无限制”：

sql>ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;

4、修改后，还没有被提示ORA-28000警告的用户不会再碰到同样的提示；

已经被锁定的帐户必须解除锁定，举例如下：

$sqlplus / as sysdba

sql> alter user smsc identified by oracle account unlock;

5、修改后default profile应该如下：

sql> select * from dba_profiles WHERE dba_profiles.profile='DEFAULT';

PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT

------------------------------ -------------------------------- ------------- ----------------------

DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED

DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED

修改之后不需要重启动数据库，会立即生效。

如果出现ORA-28000 the account is locked.错误

alter user 用户名 account unlock;

 

Error：ORA-32004: obsolete and/or deprecated parameter(s) specified

Reason：

manually run command：alter system set log_archive_start=true scope=spfile;

But ORACLE 10g do not have such parameter

Solution：

alter system reset log_archive_start scope=spfile sid='*';

Error:

Checkpoint not complete

  Current log# 2 seq# 34190 mem# 0: E:\ORADATA\THOREVO\REDO02.LOG

Thread 1 cannot allocate new log, sequence 34191

Private strand flush not complete

Resaon:  redo log file too small, always need to change;

Solution: create new big redo log file instead of the old one;

--Add new logfile group 4

ALTER DATABASE ADD LOGFILE GROUP 4 ('E:\ORADATA\THOREVO\REDO04.LOG') SIZE

200M;

--Switch the log file to group 4

alter system archive log current; 

--Change the status of the logfile

alter system checkpoint;

--Drop the logfile group 1

alter database drop logfile group 1

--Delete the physical file E:\ORADATA\THOREVO\REDO01.LOG

--Add new logfile group 1

ALTER DATABASE ADD LOGFILE GROUP 1 ('E:\ORADATA\THOREVO\REDO01.LOG') SIZE 200M;

--Drop the logfile group 2

alter database drop logfile group 2

--Delete the physical file E:\ORADATA\THOREVO\REDO02.LOG

--Add new logfile group 2

ALTER DATABASE ADD LOGFILE GROUP 2 ('E:\ORADATA\THOREVO\REDO02.LOG') SIZE 200M;

--Drop the logfile group 3

alter database drop logfile group 3

--Delete the physical file E:\ORADATA\THOREVO\REDO03.LOG

--Add new logfile group 3

ALTER DATABASE ADD LOGFILE GROUP 3 ('E:\ORADATA\THOREVO\REDO03.LOG') SIZE 200M;

--Switch the log file to group 1

alter system archive log current; 

--Drop the logfile group 4

alter database drop logfile group 4

--Delete the physical file E:\ORADATA\THOREVO\REDO04.LOG

Error：ORACLE 11G OEM did not show the SQL Response Time

Reason：N/A

Solution：

Click "edit reference collection" button to reset the sql collection. And wait for next collection. About one hour. 

Error: ORACLE INSTALLATION "No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed."
Resaon: 每次运行安装程序时，oracle会在/tmp下面生成60M左右的文件，以Ora开头，且不会自动删除

这个错误明面上开是没有找到这个文件，其实是根源是/tmp下面满了，本次程序执行时无法写入相关文件

Solution: 把/tmp下Ora开头的文件全删了就行！

Error: client can not reach the oracle database which installed in windows server 2008. telnet 1521 1158 all failed

Reason: the firewall of windows is on

Solution: close the firewall of windows

 

Error: ORA-609 TNS-12537 and TNS-12547 in 11g Alert.log

Reason: time out default setting is 60 seconds, EM/DB CONSOLE/BATCH may trigger the problem

Solution: change the setting in sqlnet.ora

Sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=180

Listener.ora: INBOUND_CONNECT_TIMEOUT_listener_name=120




Error: ORA-29278: SMTP transient error: 421 Service not available

Reason: the smtp service not start or not working

Solution: 

1. check the parameter "smtp_out_server" to find out the smtp server setting in computer;

2. check the smtp service

Error: ORA-00020: maximum number of processes (500) exceeded

select name,value from v$parameter where name in ('sessions','processes','transactions')

Solution: increase the parameter processes

Related parameters

sessions / processses / transactions

processes = sessions * 1.1 + 5

transactions = processes * 1.1

Error: DBCA create database met error

Reason: we use the General Purpose, Transaction Procession and DataWarehouse templates and change the DB_BLOCK_SIZE

Solution: if you want to change DB_BLOCK_SIZE, use custom database.

Error: — try to gather statistics on locked table
SQL> exec dbms_stats.gather_index_stats('scott', 'test_idx');
BEGIN dbms_stats.gather_index_stats('scott', 'test_idx'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at “SYS.DBMS_STATS”, line 10640
ORA-06512: at “SYS.DBMS_STATS”, line 10664
ORA-06512: at line 1

– try to gather statistics on the index using analyze
SQL> analyze index ajaffer.test_idx compute statistics;
analyze index ajaffer.test_idx compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked

Solution: – unlock statistics
SQL> exec dbms_stats.unlock_table_stats('scott', 'test');

PL/SQL procedure successfully completed.

Error: the awrrpt report empty and in the title show warning information:

WARNING: Since the DB Time is less than one second, there was  
         minimal foreground activity in the snapshot period.
         Some of the percentage values will be invalid.

Reason: sql>show parameter control_management_pack_access

if the value is none. It means the awr not start

Solution: Change the value

sql>alter system set control_mangement_pack_access="DIAGNOSTIC_TUNING" scope=both;