ORA-00600: internal error codeORA-01092、ORA-30012、ORA-10564、ORA-01110

最新推荐文章于 2024-08-14 20:32:47 发布
最新推荐文章于 2024-08-14 20:32:47 发布
阅读量1.9k 收藏 2
点赞数
分类专栏： oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq961573863/article/details/106087611
版权

SQL> recover datafile ‘C:\app\Administrator\oradata\orcl\CCEN’;
完成介质恢复。
SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01113: 文件 7 需要介质恢复
ORA-01110: 数据文件 7: ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\DETAIL01’

SQL> recover datafile ‘C:\app\Administrator\oradata\orcl\DETAIL01’;
完成介质恢复。
SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01113: 文件 8 需要介质恢复
ORA-01110: 数据文件 8: ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\DETAIL02’

SQL> recover datafile ‘C:\app\Administrator\oradata\orcl\DETAIL02’;

select file_id,block_id,blocks from dba_extents where segment_name=‘SYSTEM’;

SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01578: ORACLE data block corrupted (file # 3, block # 160)
ORA-01110: data file 3: ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF’
进程 ID: 1748
会话 ID: 1 序列号: 5

SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01157: ???/??? 1 - ??? DBWR ???
ORA-01110: ??? 1: ‘E:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF’
SQL> select name from v$datafile
NAME

E:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF
E:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF
E:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF
E:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF
E:\APP\ADMINISTRATOR\ORADATA\ORCL\EXAMPLE01.DBF
E:\APP\ADMINISTRATOR\ORADATA\CCEN
E:\APP\ADMINISTRATOR\ORADATA\DETAIL01
E:\APP\ADMINISTRATOR\ORADATA\DETAIL02
E:\APP\ADMINISTRATOR\ORADATA\DETAIL03
E:\APP\ADMINISTRATOR\ORADATA\DETAIL04

已选择10行。

SQL> alter database rename file ‘E:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF’
to ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF’;

数据库已更改。

SQL> alter database rename file ‘E:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF’
to ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF’;

数据库已更改。

SQL> alter database rename file 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF
’ to ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF’;

数据库已更改。

SQL> alter database rename file ‘E:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF’
to ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF’;

数据库已更改。

SQL> alter database rename file 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\EXAMPLE01.DBF
’ to ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\EXAMPLE01.DBF’;

数据库已更改。
SQL> alter database rename file ‘E:\APP\ADMINISTRATOR\ORADATA\CCEN’ to ‘C:\APP\A
DMINISTRATOR\ORADATA\ORCL\CCEN’;

数据库已更改。

SQL> alter database rename file ‘E:\APP\ADMINISTRATOR\ORADATA\DETAIL01’ to ‘C:\A
PP\ADMINISTRATOR\ORADATA\ORCL\DETAIL01’;

数据库已更改。

SQL> alter database rename file ‘E:\APP\ADMINISTRATOR\ORADATA\DETAIL02’ to ‘C:\A
PP\ADMINISTRATOR\ORADATA\ORCL\DETAIL02’;

数据库已更改。

SQL> alter database rename file ‘E:\APP\ADMINISTRATOR\ORADATA\DETAIL03’ to ‘C:\A
PP\ADMINISTRATOR\ORADATA\ORCL\DETAIL03’;

数据库已更改。

SQL> alter database rename file ‘E:\APP\ADMINISTRATOR\ORADATA\DETAIL04’ to ‘C:\A
PP\ADMINISTRATOR\ORADATA\ORCL\DETAIL04’;

数据库已更改。

SQL> select name from v$tempfile;

NAME

E:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP01.DBF
E:\APP\ADMINISTRATOR\ORADATA\CCEN_TMP

SQL> alter database rename file ‘E:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP01.DBF’ t
o ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP01.DBF’;

数据库已更改。

SQL> alter database rename file ‘E:\APP\ADMINISTRATOR\ORADATA\CCEN_TMP’ to ‘C:\A
PP\ADMINISTRATOR\ORADATA\ORCL\CCEN_TMP’;

数据库已更改。

SQL> select member from v$logfile;

MEMBER

E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG
E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG
E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG

SQL> alter database rename file ‘E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG’ t
o ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG’;

数据库已更改。

SQL> alter database rename file ‘E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG’ t
o ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG’;

数据库已更改。

SQL> alter database rename file ‘E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG’ t
o ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG’;

数据库已更改。

SQL> select name from v$controlfile;

NAME

C:\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL01.CTL
C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

     125406485

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#

     125406485
     125406485
     125406485
     125406485
     125406485
     125406485
     125406485
     125406485
     125406485
     125406485

已选择10行。
SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#

     125406485
     125406485
     125406485
     125406485
     125406485
     125406485
     125406485
     125406485
     125406485
     125406485

已选择10行。

SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [5380],
[11424], [12457], [], [], [], [], [], [], []

SQL> recover database;
ORA-00283: 恢复会话因错误而取消
ORA-00600: 内部错误代码, 参数: [3020], [3], [160], [12583072], [], [], [], [],
[], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 3, block# 160, file
offset is 1310720 bytes)
ORA-10564: tablespace UNDOTBS1
ORA-01110: 数据文件 3: ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF’
ORA-10560: block type ‘KTU SMU HEADER BLOCK’

SQL> recover database using backup controlfile until cancel;
ORA-00279: 更改 125406485 (在 05/09/2020 16:51:14 生成) 对于线程 1 是必需的
ORA-00289: 建议:
C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2020_05_12\O1_MF_1_5380

%U.ARC
ORA-00280: 更改 125406485 (用于线程 1) 在序列 #5380 中

指定日志: {=suggested | filename | AUTO | CANCEL}
CANCEL
ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 将出现如下错误
ORA-01194: 文件 1 需要更多的恢复来保持一致性
ORA-01110: 数据文件 1: ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF’

ORA-01112: 未启动介质恢复

SQL> recover database using backup controlfile until cancel;
ORA-00279: 更改 125406485 (在 05/09/2020 16:51:14 生成) 对于线程 1 是必需的
ORA-00289: 建议:
C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2020_05_12\O1_MF_1_5380

%U.ARC
ORA-00280: 更改 125406485 (用于线程 1) 在序列 #5380 中

指定日志: {=suggested | filename | AUTO | CANCEL}
C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG
ORA-00283: 恢复会话因错误而取消
ORA-00600: 内部错误代码, 参数: [3020], [3], [160], [12583072], [], [], [], [],
[], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 3, block# 160, file
offset is 1310720 bytes)
ORA-10564: tablespace UNDOTBS1
ORA-01110: 数据文件 3: ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF’
ORA-10560: block type ‘KTU SMU HEADER BLOCK’

ORA-01112: 未启动介质恢复
SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

orcl MOUNTED

SQL> show parameter undo;

NAME TYPE

VALUE

undo_management string
MANUAL
undo_retention integer
900
undo_tablespace string
UNDOTBS01
SQL> alter system set undo_management=‘AUTO’ scope=spfile;

系统已更改。

SQL> shu immediate
ORA-01109: 数据库未打开

已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount
ORACLE 例程已经启动。

Total System Global Area 1286066176 bytes
Fixed Size 2175408 bytes
Variable Size 788532816 bytes
Database Buffers 486539264 bytes
Redo Buffers 8818688 bytes
数据库装载完毕。

SQL> recover database using backup controlfile until cancel;
ORA-00279: 更改 125406485 (在 05/09/2020 16:51:14 生成) 对于线程 1 是必需的
ORA-00289: 建议:
C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2020_05_12\O1_MF_1_5380

%U.ARC
ORA-00280: 更改 125406485 (用于线程 1) 在序列 #5380 中

指定日志: {=suggested | filename | AUTO | CANCEL}
C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG
ORA-00283: 恢复会话因错误而取消
ORA-00600: 内部错误代码, 参数: [3020], [3], [160], [12583072], [], [], [], [],
[], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 3, block# 160, file
offset is 1310720 bytes)
ORA-10564: tablespace UNDOTBS1
ORA-01110: 数据文件 3: ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF’
ORA-10560: block type ‘KTU SMU HEADER BLOCK’

ORA-01112: 未启动介质恢复

SQL> show parameter undo

NAME TYPE

VALUE

undo_management string
AUTO
undo_retention integer
900
undo_tablespace string
UNDOTBS01
SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01589: 要打开数据库则必须使用 RESETLOGS 或 NORESETLOGS 选项

SQL> alter database open resetlogs;
alter database open resetlogs
*
第 1 行出现错误:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [0], [125406496], [0],
[125414494], [12583040], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [2662], [0], [125406495], [0],
[125414494], [12583040], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [125406493], [0],
[125414494], [12583040], [], [], [], [], [], []
进程 ID: 3216
会话 ID: 1 序列号: 5
SQL> startup mount;
ORACLE 例程已经启动。

Total System Global Area 1286066176 bytes
Fixed Size 2175408 bytes
Variable Size 788532816 bytes
Database Buffers 486539264 bytes
Redo Buffers 8818688 bytes
数据库装载完毕。
SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

     125406489

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#

     125406489
     125406489
     125406489
     125406489
     125406489
     125406489
     125406489
     125406489
     125406489
     125406489

已选择10行。

SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#

     125406489
     125406489
     125406489
     125406489
     125406489
     125406489
     125406489
     125406489
     125406489
     125406489

已选择10行。

SQL> alter database open resetlogs;
alter database open resetlogs
*
第 1 行出现错误:
ORA-01139: RESETLOGS 选项仅在不完全数据库恢复后有效

SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-30012: undo tablespace ‘UNDOTBS01’ does not exist or of wrong type
进程 ID: 3984
会话 ID: 1 序列号: 5

SQL> exit
从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断
开

C:\Users\Administrator>set ORACLE_SID=orcl

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期二 5月 12 19:43:24 2020

Copyright © 1982, 2010, Oracle. All rights reserved.

已连接到空闲例程。

SQL> startup mount;
ORACLE 例程已经启动。

Total System Global Area 1286066176 bytes
Fixed Size 2175408 bytes
Variable Size 788532816 bytes
Database Buffers 486539264 bytes
Redo Buffers 8818688 bytes
数据库装载完毕。
SQL> show parameter undo

NAME TYPE

VALUE

undo_management string
AUTO
undo_retention integer
900
undo_tablespace string
UNDOTBS01
SQL> desc v$tablespace
名称 是否为空? 类型

TS# NUMBER
NAME VARCHAR2(30)
INCLUDED_IN_DATABASE_BACKUP VARCHAR2(3)
BIGFILE VARCHAR2(3)
FLASHBACK_ON VARCHAR2(3)
ENCRYPT_IN_BACKUP VARCHAR2(3)

SQL> select name from v$tablespace;

NAME

SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
EXAMPLE
CCEN
CCEN_TMP
DETAIL01
DETAIL02
DETAIL03

NAME

DETAIL04

已选择12行。
######################创建参数文件更改undo参数 重建undo表空间失败
SQL> show parameter undo

NAME TYPE

VALUE

undo_management string
AUTO
undo_retention integer
900
undo_tablespace string
UNDOTBS01
SQL> alter system set undo_tablespace=‘UNDOTBS1’ scope=spfile;

系统已更改。

SQL> shu immediate
ORA-01109: 数据库未打开

已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount;
ORACLE 例程已经启动。

Total System Global Area 1286066176 bytes
Fixed Size 2175408 bytes
Variable Size 788532816 bytes
Database Buffers 486539264 bytes
Redo Buffers 8818688 bytes
数据库装载完毕。
SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [],
[], [], [], []
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [],
[], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [],
[], [], [], []
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [],
[], [], [], []
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [],
[], [], [], []
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [],
[], [], [], []
进程 ID: 4084
会话 ID: 1 序列号: 5

SQL> alter database open resetlogs;
ERROR:
ORA-03114: 未连接到 ORACLE

SQL> exit
从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断
开

C:\Users\Administrator>set ORACLE_SID=orcl

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期二 5月 12 19:54:39 2020

Copyright © 1982, 2010, Oracle. All rights reserved.

已连接到空闲例程。

SQL> startup mount;
ORACLE 例程已经启动。

Total System Global Area 1286066176 bytes
Fixed Size 2175408 bytes
Variable Size 788532816 bytes
Database Buffers 486539264 bytes
Redo Buffers 8818688 bytes
数据库装载完毕。
SQL> show parameter undo

NAME TYPE

VALUE

undo_management string
AUTO
undo_retention integer
900
undo_tablespace string
UNDOTBS1
SQL> exit
从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断
开
####创建参数文件更改undo参数 重建undo表空间成功
C:\Users\Administrator>set ORACLE_SID=orcl

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期二 5月 12 20:05:05 2020

Copyright © 1982, 2010, Oracle. All rights reserved.

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create pfile from spfile;
文件已创建。
修改C:\app\Administrator\product\11.2.0\dbhome_1\database\INITorcl.ORA中的*.undo_management=‘MANUAL’
*.undo_tablespace=’’

SQL> show parameter spfile;

NAME TYPE

VALUE

spfile string
C:\APP\ADMINISTRATOR\PRODUCT\1
1.2.0\DBHOME_1\DATABASE\SPFILE
ORCL.ORA
SQL> shu immediate
ORA-01109: 数据库未打开

已经卸载数据库。
ORACLE 例程已经关闭。
SQL> create spfile from pfile=‘C:\app\Administrator\product\11.2.0\dbhome_1\data
base\INITorcl.ORA’;

文件已创建。

SQL> startup nomount;
ORACLE 例程已经启动。

Total System Global Area 1286066176 bytes
Fixed Size 2175408 bytes
Variable Size 788532816 bytes
Database Buffers 486539264 bytes
Redo Buffers 8818688 bytes

SQL> show parameter undo

NAME TYPE

VALUE

undo_management string
MANUAL
undo_retention integer
900
undo_tablespace string

SQL> alter database mount;

数据库已更改。

SQL> alter database open;

数据库已更改。
SQL> select name from v$datafile;

NAME

C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF
C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF
C:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF
C:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF
C:\APP\ADMINISTRATOR\ORADATA\ORCL\EXAMPLE01.DBF
C:\APP\ADMINISTRATOR\ORADATA\ORCL\CCEN
C:\APP\ADMINISTRATOR\ORADATA\ORCL\DETAIL01
C:\APP\ADMINISTRATOR\ORADATA\ORCL\DETAIL02
C:\APP\ADMINISTRATOR\ORADATA\ORCL\DETAIL03
C:\APP\ADMINISTRATOR\ORADATA\ORCL\DETAIL04

已选择10行。

SQL> create undo tablespace UNDOTBS2 datafile ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL
\UNDOTBS02.DBF’ size 100m autoextend on maxsize unlimited;

表空间已创建。
SQL> show parameter undo

NAME TYPE

VALUE

undo_management string
MANUAL
undo_retention integer
900
undo_tablespace string

SQL> alter system set undo_management=auto scope=spfile;

系统已更改。

SQL> alter system set undo_tablespace=undotbs2 scope=spfile;

系统已更改。

SQL> drop tablespace undotbs1 including contents and datafiles;

表空间已删除。

################################乱码问题
一、问题描述：
在将其它数据库的数据导出文件导入本地新建数据库时，所导入的数据全部是乱码，一般表现为数据表中列的值类似于"???"，即内容大部分为?的表现形式。初步判断是因为Oracle客户端与数据库编码不一致所导致。乱码问题在各类技术很多地方都普遍可能存在，其根本原因主要是数据编码不一致导致。根据其原理排查数据编码，基本都可以解决乱码问题。

二、解决办法：
第一步：就Oracle数据库而言，可以使用sqlplus工具或者PL/SQL工具，登录数据库后，执行 select userenv(‘language’) from dual;语句，该语句可以查询当前数据库所使用的编码格式。这里使用sqlplus登录数据库，并执行上述查询语句，如下图所示：
select userenv(‘language’) from dual
“USERENV(‘LANGUAGE’)”
“SIMPLIFIED CHINESE_CHINA.AL32UTF8”

操作系统增加系统变量
变量名：NLS_LANG
变量值：SIMPLIFIED CHINESE_CHINA.ZHS16GBK

乱码问题
C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期二 5月 12 20:23:19 2020

Copyright © 1982, 2010, Oracle. All rights reserved.

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$nls_parameters where parameter =‘NLS_CHARACTERSET’;

PARAMETER

VALUE

NLS_CHARACTERSET
AL32UTF8

SQL> select userenv(‘language’) from dual;

USERENV(‘LANGUAGE’)

SIMPLIFIED CHINESE_CHINA.AL32UTF8

Oracle语言环境变量配置
创建系统环境变量，以下为GBK和UTF8两种模式：

变量名：NLS_LANG
变量值：SIMPLIFIED CHINESE_CHINA.ZHS16GBK

变量名：NLS_LANG
变量值：SIMPLIFIED CHINESE_CHINA.AL32UTF8

################################
坏块
select owner,segment_name,segment_type from dba_extents where file_id=1 and 71013 between block_id and block_id+blocks-1;

“OWNER” “SEGMENT_NAME” “SEGMENT_TYPE”
“SYS” “I_MON_MODS$_OBJ” “INDEX”

select table_name,index_name from dba_indexes where index_name like 'I_MON_MODS O B J ′ ; " T A B L E N A M E " " I N D E X N A M E " " M O N M O D S _OBJ'; "TABLE_NAME" "INDEX_NAME" "MON_MODS O​BJ′;"TABLEN​AME""INDEXN​AME""MONM​ODS" “I_MON_MODS$_OBJ”

alter index I_MON_MODS$_OBJ rebuild online;
/

Errors in file c:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_j002_2764.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-08102: index key not found, obj# 289, file 1, block 2025 (2)
ORA-12012: error on auto execute of job 4002
ORA-08102: index key not found, obj# 289, file 1, block 2025 (2)

SELECT owner, object_name, object_type
FROM Dba_Objects
WHERE object_id IN (289);

可以查出object_name的名字，发现是表TB_WARE的索引,然后重建索引

“OWNER” “OBJECT_NAME” “OBJECT_TYPE”
“SYS” “I_JOB_NEXT” “INDEX”

select index_name from dba_indexes where index_name like ‘I_JOB_NEXT’;

index_name
I_JOB_NEXT

select table_name,index_name from dba_indexes where index_name like ‘I_JOB_NEXT’;

“TABLE_NAME” “INDEX_NAME”
“JOB$” “I_JOB_NEXT”

alter index PK_TB_WARE rebuild online; （记得要用rebuild online ，因为他会重新读表来创建索引，而rebuild可能会读取原先的索引段而不会去读表）

SELECT owner,object_name,object_type FROM Dba_Objects WHERE object_id IN (‘289’);
select index_name from dba_indexes where index_name like ‘I_JOB_NEXT’;
/
desc dba_indexes
/
select table_name,index_name from dba_indexes where index_name like ‘I_JOB_NEXT’;
/
alter index I_JOB_NEXT rebuild online;

SQL> col file_name for a50
SQL> select file_id,file_name from dba_data_files;

FILE_ID FILE_NAME

     4 C:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF
     2 C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF
     1 C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF
     5 C:\APP\ADMINISTRATOR\ORADATA\ORCL\EXAMPLE01.DBF
     6 C:\APP\ADMINISTRATOR\ORADATA\ORCL\CCEN
     7 C:\APP\ADMINISTRATOR\ORADATA\ORCL\DETAIL01
     8 C:\APP\ADMINISTRATOR\ORADATA\ORCL\DETAIL02
     9 C:\APP\ADMINISTRATOR\ORADATA\ORCL\DETAIL03
    10 C:\APP\ADMINISTRATOR\ORADATA\ORCL\DETAIL04
    11 C:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS02.DBF

已选择10行。
alter system checkpoint;
alter system switch logfile;
###########################
修复数据库成功
SQL> shu immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 1286066176 bytes
Fixed Size 2175408 bytes
Variable Size 788532816 bytes
Database Buffers 486539264 bytes
Redo Buffers 8818688 bytes
数据库装载完毕。
数据库已经打开。
########导出数据库文件
SQL> create directory export as ‘E:\bak’;

目录已创建。

SQL> grant read,write on directory export to ccense;

授权成功。

C:\Users\Administrator>expdp system/orcl directory=export dumpfile=20200512.dmp
logfile=20200512.log schemas=ccenseSQL> recover datafile ‘C:\app\Administrator\oradata\orcl\CCEN’;
完成介质恢复。
SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01113: 文件 7 需要介质恢复
ORA-01110: 数据文件 7: ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\DETAIL01’

SQL> recover datafile ‘C:\app\Administrator\oradata\orcl\DETAIL01’;
完成介质恢复。
SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01113: 文件 8 需要介质恢复
ORA-01110: 数据文件 8: ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\DETAIL02’

SQL> recover datafile ‘C:\app\Administrator\oradata\orcl\DETAIL02’;

select file_id,block_id,blocks from dba_extents where segment_name=‘SYSTEM’;

SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01578: ORACLE data block corrupted (file # 3, block # 160)
ORA-01110: data file 3: ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF’
进程 ID: 1748
会话 ID: 1 序列号: 5

SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01157: ???/??? 1 - ??? DBWR ???
ORA-01110: ??? 1: ‘E:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF’
SQL> select name from v$datafile
NAME

E:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF
E:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF
E:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF
E:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF
E:\APP\ADMINISTRATOR\ORADATA\ORCL\EXAMPLE01.DBF
E:\APP\ADMINISTRATOR\ORADATA\CCEN
E:\APP\ADMINISTRATOR\ORADATA\DETAIL01
E:\APP\ADMINISTRATOR\ORADATA\DETAIL02
E:\APP\ADMINISTRATOR\ORADATA\DETAIL03
E:\APP\ADMINISTRATOR\ORADATA\DETAIL04

已选择10行。

SQL> alter database rename file ‘E:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF’
to ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF’;

数据库已更改。

SQL> alter database rename file ‘E:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF’
to ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF’;

数据库已更改。

SQL> alter database rename file 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF
’ to ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF’;

数据库已更改。

SQL> alter database rename file ‘E:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF’
to ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF’;

数据库已更改。

SQL> alter database rename file 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\EXAMPLE01.DBF
’ to ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\EXAMPLE01.DBF’;

数据库已更改。
SQL> alter database rename file ‘E:\APP\ADMINISTRATOR\ORADATA\CCEN’ to ‘C:\APP\A
DMINISTRATOR\ORADATA\ORCL\CCEN’;

数据库已更改。

SQL> alter database rename file ‘E:\APP\ADMINISTRATOR\ORADATA\DETAIL01’ to ‘C:\A
PP\ADMINISTRATOR\ORADATA\ORCL\DETAIL01’;

数据库已更改。

SQL> alter database rename file ‘E:\APP\ADMINISTRATOR\ORADATA\DETAIL02’ to ‘C:\A
PP\ADMINISTRATOR\ORADATA\ORCL\DETAIL02’;

数据库已更改。

SQL> alter database rename file ‘E:\APP\ADMINISTRATOR\ORADATA\DETAIL03’ to ‘C:\A
PP\ADMINISTRATOR\ORADATA\ORCL\DETAIL03’;

数据库已更改。

SQL> alter database rename file ‘E:\APP\ADMINISTRATOR\ORADATA\DETAIL04’ to ‘C:\A
PP\ADMINISTRATOR\ORADATA\ORCL\DETAIL04’;

数据库已更改。

SQL> select name from v$tempfile;

NAME

E:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP01.DBF
E:\APP\ADMINISTRATOR\ORADATA\CCEN_TMP

SQL> alter database rename file ‘E:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP01.DBF’ t
o ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP01.DBF’;

数据库已更改。

SQL> alter database rename file ‘E:\APP\ADMINISTRATOR\ORADATA\CCEN_TMP’ to ‘C:\A
PP\ADMINISTRATOR\ORADATA\ORCL\CCEN_TMP’;

数据库已更改。

SQL> select member from v$logfile;

MEMBER

E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG
E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG
E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG

SQL> alter database rename file ‘E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG’ t
o ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG’;

数据库已更改。

SQL> alter database rename file ‘E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG’ t
o ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG’;

数据库已更改。

SQL> alter database rename file ‘E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG’ t
o ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG’;

数据库已更改。

SQL> select name from v$controlfile;

NAME

C:\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL01.CTL
C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

     125406485

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#

     125406485
     125406485
     125406485
     125406485
     125406485
     125406485
     125406485
     125406485
     125406485
     125406485

已选择10行。
SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#

     125406485
     125406485
     125406485
     125406485
     125406485
     125406485
     125406485
     125406485
     125406485
     125406485

已选择10行。

SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-00600: 内部错误代码, 参数: [kcratr_nab_less_than_odr], [1], [5380],
[11424], [12457], [], [], [], [], [], [], []

SQL> recover database;
ORA-00283: 恢复会话因错误而取消
ORA-00600: 内部错误代码, 参数: [3020], [3], [160], [12583072], [], [], [], [],
[], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 3, block# 160, file
offset is 1310720 bytes)
ORA-10564: tablespace UNDOTBS1
ORA-01110: 数据文件 3: ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF’
ORA-10560: block type ‘KTU SMU HEADER BLOCK’

SQL> recover database using backup controlfile until cancel;
ORA-00279: 更改 125406485 (在 05/09/2020 16:51:14 生成) 对于线程 1 是必需的
ORA-00289: 建议:
C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2020_05_12\O1_MF_1_5380

%U.ARC
ORA-00280: 更改 125406485 (用于线程 1) 在序列 #5380 中

指定日志: {=suggested | filename | AUTO | CANCEL}
CANCEL
ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 将出现如下错误
ORA-01194: 文件 1 需要更多的恢复来保持一致性
ORA-01110: 数据文件 1: ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF’

ORA-01112: 未启动介质恢复

SQL> recover database using backup controlfile until cancel;
ORA-00279: 更改 125406485 (在 05/09/2020 16:51:14 生成) 对于线程 1 是必需的
ORA-00289: 建议:
C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2020_05_12\O1_MF_1_5380

%U.ARC
ORA-00280: 更改 125406485 (用于线程 1) 在序列 #5380 中

指定日志: {=suggested | filename | AUTO | CANCEL}
C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG
ORA-00283: 恢复会话因错误而取消
ORA-00600: 内部错误代码, 参数: [3020], [3], [160], [12583072], [], [], [], [],
[], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 3, block# 160, file
offset is 1310720 bytes)
ORA-10564: tablespace UNDOTBS1
ORA-01110: 数据文件 3: ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF’
ORA-10560: block type ‘KTU SMU HEADER BLOCK’

ORA-01112: 未启动介质恢复
SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

orcl MOUNTED

SQL> show parameter undo;

NAME TYPE

VALUE

undo_management string
MANUAL
undo_retention integer
900
undo_tablespace string
UNDOTBS01
SQL> alter system set undo_management=‘AUTO’ scope=spfile;

系统已更改。

SQL> shu immediate
ORA-01109: 数据库未打开

已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount
ORACLE 例程已经启动。

Total System Global Area 1286066176 bytes
Fixed Size 2175408 bytes
Variable Size 788532816 bytes
Database Buffers 486539264 bytes
Redo Buffers 8818688 bytes
数据库装载完毕。

SQL> recover database using backup controlfile until cancel;
ORA-00279: 更改 125406485 (在 05/09/2020 16:51:14 生成) 对于线程 1 是必需的
ORA-00289: 建议:
C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2020_05_12\O1_MF_1_5380

%U.ARC
ORA-00280: 更改 125406485 (用于线程 1) 在序列 #5380 中

指定日志: {=suggested | filename | AUTO | CANCEL}
C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG
ORA-00283: 恢复会话因错误而取消
ORA-00600: 内部错误代码, 参数: [3020], [3], [160], [12583072], [], [], [], [],
[], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 3, block# 160, file
offset is 1310720 bytes)
ORA-10564: tablespace UNDOTBS1
ORA-01110: 数据文件 3: ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF’
ORA-10560: block type ‘KTU SMU HEADER BLOCK’

ORA-01112: 未启动介质恢复

SQL> show parameter undo

NAME TYPE

VALUE

undo_management string
AUTO
undo_retention integer
900
undo_tablespace string
UNDOTBS01
SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01589: 要打开数据库则必须使用 RESETLOGS 或 NORESETLOGS 选项

SQL> alter database open resetlogs;
alter database open resetlogs
*
第 1 行出现错误:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [0], [125406496], [0],
[125414494], [12583040], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [2662], [0], [125406495], [0],
[125414494], [12583040], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [125406493], [0],
[125414494], [12583040], [], [], [], [], [], []
进程 ID: 3216
会话 ID: 1 序列号: 5
SQL> startup mount;
ORACLE 例程已经启动。

Total System Global Area 1286066176 bytes
Fixed Size 2175408 bytes
Variable Size 788532816 bytes
Database Buffers 486539264 bytes
Redo Buffers 8818688 bytes
数据库装载完毕。
SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

     125406489

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#

     125406489
     125406489
     125406489
     125406489
     125406489
     125406489
     125406489
     125406489
     125406489
     125406489

已选择10行。

SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#

     125406489
     125406489
     125406489
     125406489
     125406489
     125406489
     125406489
     125406489
     125406489
     125406489

已选择10行。

SQL> alter database open resetlogs;
alter database open resetlogs
*
第 1 行出现错误:
ORA-01139: RESETLOGS 选项仅在不完全数据库恢复后有效

SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-30012: undo tablespace ‘UNDOTBS01’ does not exist or of wrong type
进程 ID: 3984
会话 ID: 1 序列号: 5

SQL> exit
从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断
开

C:\Users\Administrator>set ORACLE_SID=orcl

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期二 5月 12 19:43:24 2020

Copyright © 1982, 2010, Oracle. All rights reserved.

已连接到空闲例程。

SQL> startup mount;
ORACLE 例程已经启动。

Total System Global Area 1286066176 bytes
Fixed Size 2175408 bytes
Variable Size 788532816 bytes
Database Buffers 486539264 bytes
Redo Buffers 8818688 bytes
数据库装载完毕。
SQL> show parameter undo

NAME TYPE

VALUE

undo_management string
AUTO
undo_retention integer
900
undo_tablespace string
UNDOTBS01
SQL> desc v$tablespace
名称 是否为空? 类型

TS# NUMBER
NAME VARCHAR2(30)
INCLUDED_IN_DATABASE_BACKUP VARCHAR2(3)
BIGFILE VARCHAR2(3)
FLASHBACK_ON VARCHAR2(3)
ENCRYPT_IN_BACKUP VARCHAR2(3)

SQL> select name from v$tablespace;

NAME

SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
EXAMPLE
CCEN
CCEN_TMP
DETAIL01
DETAIL02
DETAIL03

NAME

DETAIL04

已选择12行。
######################创建参数文件更改undo参数 重建undo表空间失败
SQL> show parameter undo

NAME TYPE

VALUE

undo_management string
AUTO
undo_retention integer
900
undo_tablespace string
UNDOTBS01
SQL> alter system set undo_tablespace=‘UNDOTBS1’ scope=spfile;

系统已更改。

SQL> shu immediate
ORA-01109: 数据库未打开

已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount;
ORACLE 例程已经启动。

Total System Global Area 1286066176 bytes
Fixed Size 2175408 bytes
Variable Size 788532816 bytes
Database Buffers 486539264 bytes
Redo Buffers 8818688 bytes
数据库装载完毕。
SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [],
[], [], [], []
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [],
[], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [],
[], [], [], []
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [],
[], [], [], []
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [],
[], [], [], []
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [],
[], [], [], []
进程 ID: 4084
会话 ID: 1 序列号: 5

SQL> alter database open resetlogs;
ERROR:
ORA-03114: 未连接到 ORACLE

SQL> exit
从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断
开

C:\Users\Administrator>set ORACLE_SID=orcl

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期二 5月 12 19:54:39 2020

Copyright © 1982, 2010, Oracle. All rights reserved.

已连接到空闲例程。

SQL> startup mount;
ORACLE 例程已经启动。

Total System Global Area 1286066176 bytes
Fixed Size 2175408 bytes
Variable Size 788532816 bytes
Database Buffers 486539264 bytes
Redo Buffers 8818688 bytes
数据库装载完毕。
SQL> show parameter undo

NAME TYPE

VALUE

undo_management string
AUTO
undo_retention integer
900
undo_tablespace string
UNDOTBS1
SQL> exit
从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断
开
####创建参数文件更改undo参数 重建undo表空间成功
C:\Users\Administrator>set ORACLE_SID=orcl

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期二 5月 12 20:05:05 2020

Copyright © 1982, 2010, Oracle. All rights reserved.

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create pfile from spfile;
文件已创建。
修改C:\app\Administrator\product\11.2.0\dbhome_1\database\INITorcl.ORA中的*.undo_management=‘MANUAL’
*.undo_tablespace=’’

SQL> show parameter spfile;

NAME TYPE

VALUE

spfile string
C:\APP\ADMINISTRATOR\PRODUCT\1
1.2.0\DBHOME_1\DATABASE\SPFILE
ORCL.ORA
SQL> shu immediate
ORA-01109: 数据库未打开

已经卸载数据库。
ORACLE 例程已经关闭。
SQL> create spfile from pfile=‘C:\app\Administrator\product\11.2.0\dbhome_1\data
base\INITorcl.ORA’;

文件已创建。

SQL> startup nomount;
ORACLE 例程已经启动。

Total System Global Area 1286066176 bytes
Fixed Size 2175408 bytes
Variable Size 788532816 bytes
Database Buffers 486539264 bytes
Redo Buffers 8818688 bytes

SQL> show parameter undo

NAME TYPE

VALUE

undo_management string
MANUAL
undo_retention integer
900
undo_tablespace string

SQL> alter database mount;

数据库已更改。

SQL> alter database open;

数据库已更改。
SQL> select name from v$datafile;

NAME

C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF
C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF
C:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF
C:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF
C:\APP\ADMINISTRATOR\ORADATA\ORCL\EXAMPLE01.DBF
C:\APP\ADMINISTRATOR\ORADATA\ORCL\CCEN
C:\APP\ADMINISTRATOR\ORADATA\ORCL\DETAIL01
C:\APP\ADMINISTRATOR\ORADATA\ORCL\DETAIL02
C:\APP\ADMINISTRATOR\ORADATA\ORCL\DETAIL03
C:\APP\ADMINISTRATOR\ORADATA\ORCL\DETAIL04

已选择10行。

SQL> create undo tablespace UNDOTBS2 datafile ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL
\UNDOTBS02.DBF’ size 100m autoextend on maxsize unlimited;

表空间已创建。
SQL> show parameter undo

NAME TYPE

VALUE

undo_management string
MANUAL
undo_retention integer
900
undo_tablespace string

SQL> alter system set undo_management=auto scope=spfile;

系统已更改。

SQL> alter system set undo_tablespace=undotbs2 scope=spfile;

系统已更改。

SQL> drop tablespace undotbs1 including contents and datafiles;

表空间已删除。

################################乱码问题
一、问题描述：
在将其它数据库的数据导出文件导入本地新建数据库时，所导入的数据全部是乱码，一般表现为数据表中列的值类似于"???"，即内容大部分为?的表现形式。初步判断是因为Oracle客户端与数据库编码不一致所导致。乱码问题在各类技术很多地方都普遍可能存在，其根本原因主要是数据编码不一致导致。根据其原理排查数据编码，基本都可以解决乱码问题。

二、解决办法：
第一步：就Oracle数据库而言，可以使用sqlplus工具或者PL/SQL工具，登录数据库后，执行 select userenv(‘language’) from dual;语句，该语句可以查询当前数据库所使用的编码格式。这里使用sqlplus登录数据库，并执行上述查询语句，如下图所示：
select userenv(‘language’) from dual
“USERENV(‘LANGUAGE’)”
“SIMPLIFIED CHINESE_CHINA.AL32UTF8”

操作系统增加系统变量
变量名：NLS_LANG
变量值：SIMPLIFIED CHINESE_CHINA.ZHS16GBK

乱码问题
C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期二 5月 12 20:23:19 2020

Copyright © 1982, 2010, Oracle. All rights reserved.

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$nls_parameters where parameter =‘NLS_CHARACTERSET’;

PARAMETER

VALUE

NLS_CHARACTERSET
AL32UTF8

SQL> select userenv(‘language’) from dual;

USERENV(‘LANGUAGE’)

SIMPLIFIED CHINESE_CHINA.AL32UTF8

Oracle语言环境变量配置
创建系统环境变量，以下为GBK和UTF8两种模式：

变量名：NLS_LANG
变量值：SIMPLIFIED CHINESE_CHINA.ZHS16GBK

变量名：NLS_LANG
变量值：SIMPLIFIED CHINESE_CHINA.AL32UTF8

################################
坏块
select owner,segment_name,segment_type from dba_extents where file_id=1 and 71013 between block_id and block_id+blocks-1;

“OWNER” “SEGMENT_NAME” “SEGMENT_TYPE”
“SYS” “I_MON_MODS$_OBJ” “INDEX”

select table_name,index_name from dba_indexes where index_name like 'I_MON_MODS O B J ′ ; " T A B L E N A M E " " I N D E X N A M E " " M O N M O D S _OBJ'; "TABLE_NAME" "INDEX_NAME" "MON_MODS O​BJ′;"TABLEN​AME""INDEXN​AME""MONM​ODS" “I_MON_MODS$_OBJ”

alter index I_MON_MODS$_OBJ rebuild online;
/

Errors in file c:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_j002_2764.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-08102: index key not found, obj# 289, file 1, block 2025 (2)
ORA-12012: error on auto execute of job 4002
ORA-08102: index key not found, obj# 289, file 1, block 2025 (2)

SELECT owner, object_name, object_type
          FROM Dba_Objects
         WHERE object_id IN (289);

可以查出object_name的名字，发现是表TB_WARE的索引,然后重建索引

“OWNER” “OBJECT_NAME” “OBJECT_TYPE”
“SYS” “I_JOB_NEXT” “INDEX”

select index_name from dba_indexes where index_name like ‘I_JOB_NEXT’;

index_name
I_JOB_NEXT

select table_name,index_name from dba_indexes where index_name like ‘I_JOB_NEXT’;

“TABLE_NAME” “INDEX_NAME”
“JOB$” “I_JOB_NEXT”

alter index PK_TB_WARE rebuild online; （记得要用rebuild online ，因为他会重新读表来创建索引，而rebuild可能会读取原先的索引段而不会去读表）

SELECT owner,object_name,object_type FROM Dba_Objects WHERE object_id IN (‘289’);
select index_name from dba_indexes where index_name like ‘I_JOB_NEXT’;
/
desc dba_indexes
/
select table_name,index_name from dba_indexes where index_name like ‘I_JOB_NEXT’;
/
alter index I_JOB_NEXT rebuild online;

SQL> col file_name for a50
SQL> select file_id,file_name from dba_data_files;

FILE_ID FILE_NAME

     4 C:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF
     2 C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF
     1 C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF
     5 C:\APP\ADMINISTRATOR\ORADATA\ORCL\EXAMPLE01.DBF
     6 C:\APP\ADMINISTRATOR\ORADATA\ORCL\CCEN
     7 C:\APP\ADMINISTRATOR\ORADATA\ORCL\DETAIL01
     8 C:\APP\ADMINISTRATOR\ORADATA\ORCL\DETAIL02
     9 C:\APP\ADMINISTRATOR\ORADATA\ORCL\DETAIL03
    10 C:\APP\ADMINISTRATOR\ORADATA\ORCL\DETAIL04
    11 C:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS02.DBF

已选择10行。
alter system checkpoint;
alter system switch logfile;
###########################
修复数据库成功
SQL> shu immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 1286066176 bytes
Fixed Size 2175408 bytes
Variable Size 788532816 bytes
Database Buffers 486539264 bytes
Redo Buffers 8818688 bytes
数据库装载完毕。
数据库已经打开。
########导出数据库文件
SQL> create directory export as ‘E:\bak’;

目录已创建。

SQL> grant read,write on directory export to ccense;

授权成功。

C:\Users\Administrator>expdp system/orcl directory=export dumpfile=20200512.dmp
logfile=20200512.log schemas=ccense

董小姐yyds
关注
专栏目录
ORA-00600: internal error code, arguments: [qosdExpStatRead: expcnt mismatch]
perfeader
02-21 2018
问题描述：数据库alert日志报ORA-00600错误，只在数据库收集统计信息的时候报这个错误。从trace文件可以看到就是在收集某一张表统计信息出现问题。在我的数据库环境中，单实例，RAC都出现了这个问题。在MOS提过SR后，Oracle给出了解决办法。12C很多数据库都会遇到该问题，这里记录一下解决过程。   数据库版本：12.2.0.1.0 系统：CentOS Linux releas...
ORA-00600: internal error code, arguments: [4137]/ [4136]
24号信仰
08-01 5700
现象描述： xx系统oracle数据库11.2.0.4 反复重启： 后台日志报错如下： SMON: enabling cache recovery Undo initialization finished serial:0 start:173950654 end:173950754 diff:100 (1 seconds) ...
ORA-00600: internal error code, arguments: [kcratr_scan_lastbwr]错误处理
cloud0912的博客
05-01 379
机器断电以后，重新启动报如下错误：[oracle@zhuxg ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Wed May 1 11:28:4...
数据库报错：ORA-00600: internal error code, arguments: [4193], [457]，该如何解决？
最新发布
老苏畅谈运维
08-14 329
一个数据库在启动后报错：ORA-00600: internal error code, arguments: [4193], [457]，该如何解决？
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr]问题处理
weixin_33946020的博客
06-20 485
服务器断电，异常关机后，重启报错ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr] 数据库运行环境是Linux上运行的ORACLE11G --尝试启动数据库实例，报错如下： SQL> startupORACLE instance started. Total System Global Area ...
ORA-00600: internal error code 内部错误代码
热门推荐
小小关的博客
12-08 2万+
ORA-00600: internal error code 内部错误代码 一般出现这样的错误提示，表示可能是遇到了Oracle的bug: 1725012。错误的全文大概是： java.sql.SQLException: ORA-00600: 内部错误代码，参数: [ttcgcshnd-1], [0], [], [], [], [], [], [] 其实这样并不表示你的数据库或者数据库连接出了问题，这只是Oracle内部的bug，所以只要能够把这个错误屏蔽，
ORA-00600: internal error code, arguments: [kcm_headroom_warn_1]
君子不怨天的博客
06-16 2186
一台老物理服务器搬迁，启动操作系统后尝试启动数据库报错。看这个日志我们可以发现日期明显不对，跟当前时间相差八年。查询MOS，因为时间相差过大导致数据库scn号出了问题，数据库无法启动，需要修改操作系统时间。
ORA-00600: internal error code, arguments [4194]错误
05-25
NULL 博文链接：https://gembler.iteye.com/blog/346242
p8922013_10204_Linux-x86-64.zipORA-00600: INTERNAL ERROR CODE, ARGUMENTS: [17059
08-21
描述中提到的 "ORA-00600: INTERNAL ERROR CODE, ARGUMENTS: [17059], [0X93953434]" 是一个Oracle数据库内部错误代码，表示遇到了无法处理的系统级异常。ORA-00600错误通常涉及到数据库的内部一致性问题，这可能是...
记一次报错ORA-00600: internal error code, arguments: [kdsgrp1]，解决索引坏块问题！
zhongxiayeyu的博客
10-28 6408
记一次报错ORA-00600: internal error code, arguments: [kdsgrp1]，解决索引坏块问题！ 【原创】作者：仲夏夜雨 数据库版本：Oracle 10.2.0.5，RAC集群。 同事找我问数据库问题，客户那边网站点击按添加钮显示error如下图。 现象一： Java程序报错ORA-00600: internal error code, arguments:...
ORA-00600: internal error code, arguments: [4137], [4.10.646], [0], [0]
S1156578253的博客
07-21 420
问题描述： 生产数据库current状态的redo文件丢失，恢复后数据库可以打开，但是alert日志文件里面报ORA-00600错误。 Wed Jul 21 03:19:43 2021 Starting background process CJQ0 Wed Jul 21 03:19:43 2021 CJQ0 started with pid=27, OS id=6443 SMON: Parallel transaction recovery slave got internal error SMON.
ORA-00600: internal error code, arguments: [kkdc1prc:BadType]
kevinyu998的博客
04-12 476
可能是表问题，转给产品处理了，让他们手动执行故障sql看看效果，下班后反馈找到问题了，表结构被修改导致，无人认领。
ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因---惜分飞
03-22 466
其实ORA-600 16703 1403 N的错误,表示数据库在检索tab$表的时候,N为发现异常的obj#的值,这里的报错表示数据库在启动过程中无法检索到tab$中的tab$对象,应该是恢复过程中缺少该记录导致,解决问题的思路就是把该记录回写到tab$表中即可。
ORA-00600: internal error code
cszt85637的博客
08-16 597
处理本故障时，走了点弯路，这告诉我们遇到问题要仔细分析，不要遇到问题就baiduORA-00600: internal error code, arguments: [kcfrbd_3], [1], [655361], [1],...
ORA-00600: internal error code, arguments: [KGL-heap-size-exceeded]
Du.的博客
12-30 3440
ORA-00600: internal error code, arguments: [KGL-heap-size-exceeded]
ORA-00600: internal error code, arguments: [krbcbp_9]
喝醉酒的小白
04-09 489
在 RMAN 中，可以使用“恢复窗口”的概念来指定数据库可以恢复到的时间点。在实际操作中，你可能还需要创建备份保留策略（backup retention policy），例如，定期进行数据库的全备份以及增量备份，并且保留足够的归档日志备份，以确保可以恢复到任意指定的时间点。要设置恢复窗口的时间参数，你需要使用 RMAN 的 CONFIGURE 命令来指定恢复窗口的时间长度。请注意，设置恢复窗口时间应该在满足恢复需求的前提下，因为这会影响备份保留策略和可用于恢复的备份数据。这将显示当前的保留策略配置。
ORA-00600: internal error code, arguments: [4194]
coco3600的博客
09-11 1199
ORA-00600: internal error code, arguments: [4194] 4193：表示undo和redo不一致（Arg [a] Undo record seq numb...
ORA-00600: internal error code, arguments: [kspsetpao1], [1129], [1068]
无心人的终点
01-14 845
SQL> create spfile from pfile='D:\oracle\product\10.2.0\admin\orcl\pfile\init.ora.10132009154514'; create spfile from pfile='D:\oracle\product\10.2.0\admin\orcl\pfile\init.ora.10132009154514' * 第 1
ORA-00600: internal error code, arguments: [keltnfy-ldmInit], [46], [1], [], [],
系统 运维
02-25 124
今天启动数据库发现这样一个错误 ORA-00600: internal error code, arguments: [keltnfy-ldmInit], [46], [1], [], [], 而且用sqlplus 的时候特别费时间 [oracle@tyger ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Produc...
ORA-00600: internal error code, arguments
07-20
ORA-00600是Oracle数据库的内部错误代码，它指示发生了一个未知的、非预期的内部错误。这个错误代码后面通常会跟着一些参数，这些参数提供了更详细的错误信息。然而，ORA-00600错误通常需要Oracle支持团队的介入来...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

董小姐yyds

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值