240111 Backup / Noarchive Log Mode _ temp file 손상 되었을 경우 복구 방식

#) temp file 확인

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/temp01.dbf

#) temp file 전체적으로 확인

SQL> select * from v$tempfile;

     FILE# CREATION_CHANGE# CREATION_        TS#     RFILE# STATUS  ENABLED
---------- ---------------- --------- ---------- ---------- ------- ----------
     BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------- ------------ ----------
NAME
--------------------------------------------------------------------------------
         1           635081 13-DEC-23          3          1 ONLINE  READ WRITE
  30408704       3712     20971520       8192
/u01/app/oracle/oradata/ora11g/temp01.dbf

#) dba로 temp file 로 확인 

SQL> select * from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ---------- -------
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
/u01/app/oracle/oradata/ora11g/temp01.dbf
         1 TEMP                             30408704       3712 ONLINE
           1 YES 3.4360E+10    4194302           80   29360128        3584

# 손상 파일 : temp file 삭제, 장애 유도 #
SQL> ! rm /u01/app/oracle/oradata/ora11g/temp01.dbf

SQL> ! ls /u01/app/oracle/oradata/ora11g/temp01.dbf
ls: cannot access /u01/app/oracle/oradata/ora11g/temp01.dbf: No such file or directory

#) temp file 손상되어도 운영되는지 확인!
SQL> select count(*) from hr.employees;

  COUNT(*)
----------
       107

#) sort 작업 
SQL> select * from hr.employees order by 1;

#) sort 사이즈 확인
SQL> show parameter sort_area_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sort_area_size                       integer     65536

#) sort_area_size

- 오라클에서 사용되는 세션 단위의 파라미터로, 정렬 작업을 수행하는 데 필요한 메모리 영역의 크기를 지정.

=> 메모리를 사용하지 않고 디스크를 사용하여 정렬작업을 수행한다.

SQL> alter session set sort_area_size = 0;

 

Session altered.

#) 변경된 sort 확인
SQL> show parameter sort_area_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sort_area_size                       integer     98304

#) 에러발생
=> temp file 손상으로 에러 발생, 문장 레벨(data가 많은 문장)에서만 에러발생

=> temp file 이 손상되었을 경우 문제되는 SQL문에 대해서만 오류가 발생한다.

SQL> select s.*, b.*
from dba_objects s, dba_objects b
order by 1,2,3,4;  2    3
from dba_objects s, dba_objects b
     *
ERROR at line 2:
ORA-01565: error in identifying file '/u01/app/oracle/oradata/ora11g/temp01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

#) 문제없이 수행됨

=> 비교적 data가 적어 수행이 된다.

SQL> select * from hr.employees order by 1;

#) temp file 있는지 확인!
SQL> ! ls /u01/app/oracle/oradata/ora11g/temp01.dbf
ls: cannot access /u01/app/oracle/oradata/ora11g/temp01.dbf: No such file or directory

tip) alert log file 위치 정보 확인
SQL> show parameter background_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string      /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace

#) temp file 확인
SQL> select * from v$tempfile;

     FILE# CREATION_CHANGE# CREATION_        TS#     RFILE# STATUS  ENABLED         BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE NAME
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
         1           635081 13-DEC-23          3          1 ONLINE  READ WRITE   30408704       3712    20971520        8192 /u01/app/oracle/oradata/ora11g/temp01.dbf

#) database 속성 확인

=>  property_value 의 temp 확

SQL> select * from database_properties;

#1) 해결 방법 : temp file 추가 
# add temp file 추가
SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/ora11g/temp02.dbf' size 20m;

Tablespace altered.

#) tempfile 확인
SQL> select name from v$tempfile;

NAME
--------------------------------------------------
/u01/app/oracle/oradata/ora11g/temp02.dbf
/u01/app/oracle/oradata/ora11g/temp01.dbf

#) temp01.dbf 삭제 
SQL> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/ora11g/temp01.dbf';

Tablespace altered.

#) 삭제 되었는지 확인
SQL> select name from v$tempfile;

NAME
--------------------------------------------------
/u01/app/oracle/oradata/ora11g/temp02.dbf
/u01/app/oracle/oradata/ora11g/temp01.dbf => ?? drop 을 했지만, 그대로 출력된다.

 

tip) 이럴경우에는 drop 한 temp file의 상태를 확인해보면 OFFLINE, ONLINE 으로 구분해보면 된다. 

SQL> select status, name from v$tempfile;

STATUS  NAME
------- --------------------------------------------------
ONLINE  /u01/app/oracle/oradata/ora11g/temp02.dbf
OFFLINE /u01/app/oracle/oradata/ora11g/temp01.dbf

#) 메모리 할당을 비활성화하고 디스크를 사용하도록 재설정. 
SQL> alter session set sort_area_size = 0;
Session altered.

=> 문장 레벨에서 오류
SQL> select s.*, b.*
from dba_objects s, dba_objects b
order by 1,2,3,4;  2    3
from dba_objects s, dba_objects b
     *
ERROR at line 2:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP : 20기가 이상 꽉참
=> 해결방법: tempfile 공간 늘려주기

#2) 해결 방법 : temp file 크기 조정
=> ORA-01652 오류는 임시 세그먼트 확장할 수 없을 때 발생한다.
SQL> alter database tempfile '/u01/app/oracle/oradata/ora11g/temp02.dbf' resize 100m;

Database altered.

2-1. 자동 확정 기능 활성화
SQL> alter database tempfile '/u01/app/oracle/oradata/ora11g/temp02.dbf' autoextend on;

Database altered.

# temp file 추가
SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/ora11g/temp03.dbf' size 10m autoextend on;

Tablespace altered.

SQL> select * from dba_temp_files;
SQL> select file_name, bytes, status, autoextensible from dba_temp_files;

FILE_NAME                                               BYTES STATUS  AUT
-------------------------------------------------- ---------- ------- ---
/u01/app/oracle/oradata/ora11g/temp02.dbf           104857600 ONLINE  YES
/u01/app/oracle/oradata/ora11g/temp03.dbf            10485760 ONLINE  YES
/u01/app/oracle/oradata/ora11g/temp01.dbf                     OFFLINE


2.새로운 temp tablespace 생성하고 default temp tablespace 지정
SQL> create temporary tablespace temp_new 

tempfile '/u01/app/oracle/oradata/ora11g/temp_new01.dbf' size 20m autoextend on;

Tablespace created.

#) 새로운 tablespace 생성 확인
SQL> select file_name, bytes, status, autoextensible from dba_temp_files;

FILE_NAME                                               BYTES STATUS  AUT
-------------------------------------------------- ---------- ------- ---
/u01/app/oracle/oradata/ora11g/temp02.dbf           104857600 ONLINE  YES
/u01/app/oracle/oradata/ora11g/temp03.dbf            10485760 ONLINE  YES
/u01/app/oracle/oradata/ora11g/temp_new01.dbf        20971520 ONLINE  YES
/u01/app/oracle/oradata/ora11g/temp01.dbf                     OFFLINE

#) temp_new 로 지정
SQL> alter database default temporary tablespace temp_new;

Database altered.

#) 변경 확인
select * from dba_temp_files;
select * from database_properties;
select * from dba_users;

#) 기존 삭제 
SQL> drop tablespace temp including contents and datafiles;

=> 행이 오래걸려서 로딩이 오래걸린다.

1) 그럴경우에는 ctrl+C 나오기
^C

drop tablespace temp including contents and datafiles
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

2) SQL을 exit 하고 나왔다가 재접속한다.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle1@oracle 20240111]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 11 02:06:50 2024

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop tablespace temp including contents and datafiles;

Tablespace dropped.

 

#) temp file 삭제 여부 확인 

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/temp02.dbf
/u01/app/oracle/oradata/ora11g/temp03.dbf
/u01/app/oracle/oradata/ora11g/temp_new01.dbf
/u01/app/oracle/oradata/ora11g/temp01.dbf

(삭제 전과 후)

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/temp_new01.dbf


3. temp file 손상된 걸 모르는 상황에서 DB 종료 했다가 

다시 시작하면 오라클이 알아서 Re-create temp file 실행한다

SQL> ! rm /u01/app/oracle/oradata/ora11g/temp_new01.dbf

SQL> ! ls /u01/app/oracle/oradata/ora11g/temp_new01.dbf
ls: cannot access /u01/app/oracle/oradata/ora11g/temp_new01.dbf: No such file or directory

#) DB 내렸다가 올리기
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  711430144 bytes
Fixed Size                  1367004 bytes
Variable Size             440402980 bytes
Database Buffers          264241152 bytes
Redo Buffers                5419008 bytes
Database mounted.
Database opened.

=> "Re-creating tempfile /u01/app/oracle/oradata/ora11g/temp_new01.dbf"

#) 확인
SQL>  ! ls /u01/app/oracle/oradata/ora11g/temp_new01.dbf
/u01/app/oracle/oradata/ora11g/temp_new01.dbf

#) temp file 확인
SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/temp_new01.dbf