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 selected

2.检查控制文件。

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.