oracle错误汇总2
http://blog.itpub.net/30430420/viewspace-1799925/
=============================
现象！！！！！！！！！！！！！！！！！
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 3373858816 bytes
Fixed Size 2180424 bytes
Variable Size 2415921848 bytes
Database Buffers 939524096 bytes
Redo Buffers 16232448 bytes
数据库装载完毕。
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [4724],[43805], [43810], [], [], [], [], [], [], []
[1], [4724],[43805], [43810]
线程1，实例需要恢复日志序列号为4724的联机日志文件，需要恢复到编号为43810的日志块，而实际上只能恢复到第43805个日志块，所以库就启不来了
这可能是由于控制文件的缺失，或者在线日志文件在实例恢复时不完整
查看告警日志：
less alert_orcldata.log
Completed: ALTER DATABASE MOUNT
Sun Feb 25 14:49:04 2018
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
parallel recovery started with 3 processes
Started redo scan
Completed redo scan
read 312 KB redo, 55 data blocks need recovery
Errors in file f:\app\asus\diag\rdbms\orcldata\orcldata\trace\orcldata_ora_2896.trc (incident=465222):
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [4724], [43805], [43810], [], [], [], [], [], [], []
Incident details in: f:\app\asus\diag\rdbms\orcldata\orcldata\incident\incdir_465222\orcldata_ora_2896_i465222.trc
Aborting crash recovery due to error 600
Errors in file f:\app\asus\diag\rdbms\orcldata\orcldata\trace\orcldata_ora_2896.trc:
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [4724], [43805], [43810], [], [], [], [], [], [], []
Errors in file f:\app\asus\diag\rdbms\orcldata\orcldata\trace\orcldata_ora_2896.trc:
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [4724], [43805], [43810], [], [], [], [], [], [], []
ORA-600 signalled during: ALTER DATABASE OPEN...
Trace dumping is performing id=[cdmp_20180225144908]
Sun Feb 25 14:50:04 2018
Sweep [inc][465222]: completed
Sweep [inc2][465222]: completed
查看相应的trace文件：
less orcldata_ora_2896.trc
Trace file f:\app\asus\diag\rdbms\orcldata\orcldata\trace\orcldata_ora_2896.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V6.1 Service Pack 1
CPU : 4 - type 8664, 4 Physical Cores
Process Affinity : 0x0x0000000000000000
Memory (Avail/Total): Ph:1599M/8062M, Ph+PgF:6927M/16124M
Instance name: orcldata
Redo thread mounted by this instance: 1
Oracle process number: 20
Windows thread id: 2896, image: ORACLE.EXE (SHAD)
*** 2018-02-25 14:49:05.149
*** SESSION ID:(5.3) 2018-02-25 14:49:05.149
*** CLIENT ID:() 2018-02-25 14:49:05.149
*** SERVICE NAME:() 2018-02-25 14:49:05.149
*** MODULE NAME:(sqlplus.exe) 2018-02-25 14:49:05.149
*** ACTION NAME:() 2018-02-25 14:49:05.149
Successfully allocated 3 recovery slaves
Using 45 overflow buffers per recovery slave
Thread 1 checkpoint: logseq 4724, block 2, scn 144933326
cache-low rba: logseq 4724, block 43181
on-disk rba: logseq 4724, block 43810, scn 144958360
start recovery at logseq 4724, block 43181, scn 0
*** 2018-02-25 14:49:05.307
Started writing zeroblks thread 1 seq 4724 blocks 43805-43812
*** 2018-02-25 14:49:05.308
Completed writing zeroblks thread 1 seq 4724
*** 2018-02-25 14:49:05.492
==== Redo read statistics for thread 1 ====
Total physical reads (from disk and memory): 4096Kb
-- Redo read_disk statistics --
Read rate (ASYNC): 312Kb in 0.30s => 1.02 Mb/sec
Longest record: 6Kb, moves: 0/595 (0%)
Change moves: 1/73 (1%), moved: 0Mb
Longest LWN: 6Kb, moves: 0/238 (0%), moved: 0Mb
Last redo scn: 0x0000.08a3e393 (144958355)
----------------------------------------------
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 1
Average hash chain = 55/55 = 1.0
Max compares per lookup = 1
Avg compares per lookup = 1514/1663 = 0.9
----------------------------------------------
WARNING! Crash recovery of thread 1 seq 4724 is
ending at redo block 43805 but should not have ended before
redo block 43810
*** 2018-02-25 14:49:06.969
Incident 465222 created, dump file: f:\app\asus\diag\rdbms\orcldata\orcldata\incident\incdir_465222\orcldata_ora_2896_i465222.trc
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [4724], [43805], [43810], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [4724], [43805], [43810], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [4724], [43805], [43810], [], [], [], [], [], [], []
应该是由于我强制关闭电脑，导致LGWR写联机日志文件时失败，下次重新启动数据库时，需要做实例级恢复，而又无法从联机日志文件里获取到这些redo信息，因为上次关闭时，写日志失败了。
===================================
http://blog.itpub.net/28883355/viewspace-1080879/
oradebug help
SETMYPID Debug current process
===================================
http://blog.csdn.net/haibusuanyun/article/details/36868269
方法1：这个方法不行
RECOVER DATABASE;
RECOVER DATABASE UNTIL CANCEL;
方法2：最终通过重建控制文件、再进行不完全恢复来OPEN数据库。（前提是客户只要求OPEN库，是客户的测试库，丢些数据没关系，如果是生产库不允许丢数据，此方法就不适用了）
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS'/home/oracle/a.txt';
Alter database recover database until cancel using backup controlfile;
Alter database open resetlogs;
===================================
下面这个方法有效
http://blog.itpub.net/30430420/viewspace-1799925/
Show parameter control_files;
oradebug setmypid;
Alter session set tracefile_identifier='controlfilerecreate';
Alter database backup controlfile to trace noresetlogs;
oradebug tracefile_name;
shutdown immediate;
startup nomount;
执行trace文件中的创建数据库脚本，orcldata_ora_2896_controlfilerecreate.trc
=====================================
最后验证一下相关表的数据
select uat.table_name from user_all_tables uat;
select object_name, created,last_ddl_time from user_objects;
SELECT
uat.table_name AS 表名,
(
SELECT
last_ddl_time
FROM
user_objects
WHERE
object_name = uat.table_name
) AS 最后修改日期
FROM
user_all_tables uat;
==========================================================
windows启动dbconsole
C:\Windows\System32\drivers\etc\hosts修改这个文件添加主机名的解析
否则只能本地访问EM
F:\app\ASUS\product\11.2.0\dbhome_1\BIN>emctl start dbconsole
OC4J Configuration issue. F:\app\ASUS\product\11.2.0\dbhome_1/oc4j/j2ee/OC4J_DBConsole_ASUS-PC_orcldata not found.
复制该目录下的“OC4J_DBConsole_localhost_orcl”文件夹放在同一目录下,且把名称改成“OC4J_DBConsole_ASUS-PC_orcldata”。
F:\app\ASUS\product\11.2.0\dbhome_1\BIN>emctl start dbconsole
EM Configuration issue. F:\app\ASUS\product\11.2.0\dbhome_1/ASUS-PC_orcldata not found.
复制该目录下的“localhost_orcl”文件夹放在同一目录下,且把名称改成“ASUS-PC_orcldata”。
==========================================
windows登录EM问题
1、问题描述：
打开OEM页面发现这个错误：界面如下
ORA-28001: the password has expired (DBD ERROR: OCISessionBegin)
2问题原因
造成这个问题的主要原因是因为DBSNMP 、SYSMAN用户密码已经过期。
3解决办法
可以使用sys以管理员的身份登录数据库，然后执行select username,account_status from dba_users;语句查询用户状态，可以发现有如下两句：
DBSNMP EXPIRED
SYSMAN EXPIRED
把这俩用户、密码修改了就行。
本机使用sqlplus登陆
sqlplus / as sysdba
SQL> alter user sysman identified by sys123;
用户已更改。
SQL> alter user dbsnmp identified by sys123;
用户已更改。
重启em dbconsole 登陆
================================
三步走，重新创建EM
emctl stop dbconsole
emca -repos recreate
emca -config dbcontrol db
=================================
Wed Jul 31 18:13:51 2019
ORA-1652: unable to extend temp segment by 128 in tablespace KYC_TEMP
Wed Jul 31 18:14:04 2019
Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized. If emergency flushes are a recurring issue, you may consider increasing ASH size by setting the value of _ASH_SIZE to a sufficiently large v
alue. Currently, ASH size is 100663296 bytes. Both ASH size and the total number of emergency flushes since instance startup can be monitored by running the following query:
select total_size,awr_flush_emergency_count from v$ash_info;
Wed Jul 31 18:14:29 2019
ORA-1652: unable to extend temp segment by 128 in tablespace KYC_TEMP
更换临时表空间，数据不影响
create temporary tablespace kyc_temp1;
alter user kyc_loa temporary tablespace kyc_temp1;
