76.临时表空间数据文件损坏修复
--11g单机,无法查询临时表空间。
--2021年,通过备库恢复的方式创建数据库的时候遗留的问题。
1.老核心系统临时表空间查看
SQL> set line 300
SQL> col tablespace_name for a30
SQL> col status for a10
SQL> col FILE_NAME for a110
SQL> select FILE_NAME,tablespace_name,status,round(bytes/1024/1024/1024,2) CUR_GB,AUTOEXTENSIBLE AUTOEXT,round(MAXBYTES/1024/1024/1024,2) MAX_GB
from dba_temp_files where tablespace_name in ('TEMP');
from dba_temp_files where tablespace_name in ('TEMP')
*
ERROR at line 2:
ORA-01157: cannot identify/lock data file 1028 - see DBWR trace file
ORA-01110: data file 1028: '+DATA'
SQL> select FILE_NAME from dba_temp_files;
ERROR:
ORA-01157: cannot identify/lock data file 1028 - see DBWR trace file
ORA-01110: data file 1028: '+DATA'
no rows selected2.检查控制文件。
ALTER TABLESPACE TEMP ADD TEMPFILE '/chac/app/oradata/temp01.dbf'
SIZE 104857600 REUSE AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M;
ALTER TABLESPACE TBLSPACE_TEMP ADD TEMPFILE '+DATA' REUSE;
ALTER TABLESPACE TBLSPACE_TEMP ADD TEMPFILE '+DATA' REUSE;
ALTER TABLESPACE TBLSPACE_TEMP ADD TEMPFILE '+DATA' REUSE;
ALTER TABLESPACE TEMP_DSG ADD TEMPFILE '+DATA' REUSE;
由此可知,老核心系统是由RAC的环境中备份恢复的,
控制文件中残留了RAC的信息。3.检查用户的表空间。
select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users;
CTPROD TBLSPACE_OTH TBLSPACE_TEMP
LVIMS LVIMS_DATA TBLSPACE_TEMP
IBANK IBANK_APP TBLSPACE_TEMP
EBIZ_ECHANNEL ECHANNEL_APP TBLSPACE_TEMP
ECHANNEL EBIZ_APP TBLSPACE_TEMP
4.trace 文件查看
Errors in file /chac/app/oracle/diag/rdbms/core4xb/core4xb/trace/core4xb_dbw0_101263.trc:
ORA-01186: file 1025 failed verification tests
ORA-01157: cannot identify/lock data file 1025 - see DBWR trace file
ORA-01110: data file 1025: '+DATA'
File 1025 not verified due to error ORA-01157
Errors in file /chac/app/oracle/diag/rdbms/core4xb/core4xb/trace/core4xb_dbw0_101263.trc:
ORA-01186: file 1026 failed verification tests
ORA-01157: cannot identify/lock data file 1026 - see DBWR trace file
ORA-01110: data file 1026: '+DATA'
File 1026 not verified due to error ORA-01157
Errors in file /chac/app/oracle/diag/rdbms/core4xb/core4xb/trace/core4xb_dbw0_101263.trc:
ORA-01186: file 1027 failed verification tests
ORA-01157: cannot identify/lock data file 1027 - see DBWR trace file
ORA-01110: data file 1027: '+DATA'
File 1027 not verified due to error ORA-01157
Errors in file /chac/app/oracle/diag/rdbms/core4xb/core4xb/trace/core4xb_dbw0_101263.trc:
ORA-01186: file 1028 failed verification tests
ORA-01157: cannot identify/lock data file 1028 - see DBWR trace file
ORA-01110: data file 1028: '+DATA'
File 1028 not verified due to error ORA-01157
Errors in file /chac/app/oracle/diag/rdbms/core4xb/core4xb/trace/core4xb_dbw0_101263.trc:
ORA-01186: file 1029 failed verification tests
ORA-01157: cannot identify/lock data file 1029 - see DBWR trace file
ORA-01110: data file 1029: '+DATA'
File 1029 not verified due to error ORA-01157
Errors in file /chac/app/oracle/diag/rdbms/core4xb/core4xb/trace/core4xb_dbw0_101263.trc:
ORA-01186: file 1030 failed verification tests
ORA-01157: cannot identify/lock data file 1030 - see DBWR trace file
ORA-01110: data file 1030: '+DATA'
File 1030 not verified due to error ORA-01157
Errors in file /chac/app/oracle/diag/rdbms/core4xb/core4xb/trace/core4xb_dbw0_101263.trc:
ORA-01186: file 1031 failed verification tests
ORA-01157: cannot identify/lock data file 1031 - see DBWR trace file
ORA-01110: data file 1031: '+DATA'
File 1031 not verified due to error ORA-01157
由TRACE可以知道:
损坏的文件为:7个文件。
1025
1026
1027
1028
1029
1030
1031
5.修改用户的临时表空间。
alter user CTPROD temporary tablespace temp;
alter user LVIMS temporary tablespace temp;
alter user IBANK temporary tablespace temp;
alter user EBIZ_ECHANNEL temporary tablespace temp;
alter user ECHANNEL temporary tablespace temp;
select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users
where TEMPORARY_TABLESPACE<>'TEMP';
SQL> select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users
where TEMPORARY_TABLESPACE<>'TEMP';
no rows selected
--说明所有用户都使用temp表空间作为临时表空间。
alter tablespace temp add tempfile '/chac/app/oradata/temp02.dbf' size 1g autoextend on;
alter tablespace temp add tempfile '/chac/app/oradata/temp03.dbf' size 1g autoextend on;
6.删除旧的损坏的临时表空间。
SQL> select ts#, name from sys.ts$ where name like '%TEMP%';
TS# NAME
---------- --------------------------------------------------
3 TEMP
31 TBLSPACE_TEMP
32 TEMP_DSG
SQL> drop tablespace TBLSPACE_TEMP including contents and datafiles;
Tablespace dropped.
SQL> drop tablespace TEMP_DSG including contents and datafiles;
Tablespace dropped.
7.重新查询临时表空间。
SQL> set line 300
SQL> col tablespace_name for a30
SQL> col status for a10
SQL> col FILE_NAME for a110
SQL> select /*+ rule */ FILE_NAME,tablespace_name,status,round(bytes/1024/1024/1024,2) CUR_GB,AUTOEXTENSIBLE AUTOEXT,round(MAXBYTES/1024/1024/1024,2) MAX_GB
2 from dba_temp_files where tablespace_name in ('TEMP');
FILE_NAME TABLESPACE_NAME STATUS CUR_GB AUT MAX_GB
-------------------------------------------------------------------------------------------------------------- ------------------------------ ---------- ---------- --- ----------
/chac/app/oradata/temp01.dbf TEMP ONLINE .1 YES 32
/chac/app/oradata/temp02.dbf TEMP ONLINE 1 YES 32
/chac/app/oradata/temp03.dbf TEMP ONLINE 1 YES 32
处理完后，alert日志中没有报:ORA-17503: ksfdopn:2 Failed to open file +DATA.