# 요약
  # cold backup
<< 시나리오 10 >> 백업본에 redo log file 이 없을 때 복구 방식
<< 시나리오 11 >> Temp File 손상되었을 경우 복구 방식
# 해결방법 1. Temp File 변경
# 해결방법 2.  새로운 Temp Tablespace 생성하고 Default Temp Tablespace 지정
# 해결방법 3.  Temp File 손상된걸 모르는 상황에서 DB 재부팅 시 오라클이 알아서 RE-CREATE TEMP FILE 실행
# 오류해결 ORA-01652: 임시 세그먼트를 확장할 수 없을 때 발생한다.

 

 

 

 

<< BACKUP >> noarchive log mode 입니다. Whole database backup(일관성 있는 백업, cold backup) 을
/home/oracle/backup/noarch/20240111 디렉토리에 백업해 주세요. 단, data file, control file 만 백업해주세요.
  -- noarchive log mode에서 redo loge file 백업은 옵션인 경우가 많다.


# 백업 전 redo log  상태 및 파일 위치 확인

SQL> SELECT sequence#,status,first_change#,first_time,next_change#,next_time FROM v$log;
 SEQUENCE# STATUS           FIRST_CHANGE# FIRST_TIME  NEXT_CHANGE# NEXT_TIME
---------- ---------------- ------------- ----------- ------------ ---------
        98 INACTIVE               1987011 24/01/09    2007014      24/01/09
        99 CURRENT                2007014 24/01/09    2.8147E+14
        97 INACTIVE               1987008 24/01/09    1987011      24/01/09


SQL> SELECT name,status,checkpoint_change# FROM v$datafile;
NAME                                               STATUS  CHECKPOINT_CHANGE#
-------------------------------------------------- ------- ------------------
/u01/app/oracle/oradata/ora11g/system01.dbf        SYSTEM             2036605
/u01/app/oracle/oradata/ora11g/sysaux01.dbf        ONLINE             2036605
/u01/app/oracle/oradata/ora11g/users01.dbf         ONLINE             2036605
/u01/app/oracle/oradata/ora11g/example01.dbf       ONLINE             2036605
/u01/app/oracle/oradata/ora11g/undotbs01.dbf       ONLINE             2036605


SQL> SELECT name FROM v$tempfile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/ora11g/temp01.dbf


SQL> SELECT name FROM v$controlfile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/ora11g/control01.ctl


# 초기 파라미터 파일도 항상 백업하자
CREATE PFILE = '/home/oracle/backup/noarch/20240111/initora11g.ora' FROM SPFILE;


# 백업대상 CP 일괄명령어 추출
SELECT 'cp -av '||name||' /home/oracle/backup/noarch/20240111' FROM v$datafile
UNION ALL
SELECT 'cp -av '||name||' /home/oracle/backup/noarch/20240111' FROM v$tempfile
UNION ALL
SELECT 'cp -av '||name||' /home/oracle/backup/noarch/20240111' FROM v$controlfile;


# DB 정상종료
SQL> SHUTDOWN IMMEDIATE

-- Thread 1 closed at log sequence 99


# 디렉터리 생성
[oracle@oracle ~]$ mkdir -p /home/oracle/backup/noarch/20240111

[oracle@oracle ~]$ cd /home/oracle/backup/noarch/20240111

# 백업(CP)
cp -av /u01/app/oracle/oradata/ora11g/system01.dbf /home/oracle/backup/noarch/20240111
cp -av /u01/app/oracle/oradata/ora11g/sysaux01.dbf /home/oracle/backup/noarch/20240111
cp -av /u01/app/oracle/oradata/ora11g/users01.dbf /home/oracle/backup/noarch/20240111
cp -av /u01/app/oracle/oradata/ora11g/example01.dbf /home/oracle/backup/noarch/20240111
cp -av /u01/app/oracle/oradata/ora11g/undotbs01.dbf /home/oracle/backup/noarch/20240111
cp -av /u01/app/oracle/oradata/ora11g/temp01.dbf /home/oracle/backup/noarch/20240111
cp -av /u01/app/oracle/oradata/ora11g/control01.ctl /home/oracle/backup/noarch/20240111

또는
cp -av /u01/app/oracle/oradata/ora11g/*.dbf /home/oracle/backup/noarch/20240111
cp -av /u01/app/oracle/oradata/ora11g/*.ctl /home/oracle/backup/noarch/20240111

[oracle@oracle 20240111]$ ls
control01.ctl  sysaux01.dbf  temp01.dbf     users01.dbf
example01.dbf  system01.dbf  undotbs01.dbf


# DB 시작
SQL> STARTUP

 

<< 시나리오 10 >> 백업본에 redo log file 이 없을 때 복구 방식
# 백업 이후 DB 운영 중 장애 발생
  SQL> SELECT sequence#,status,first_change#,first_time,next_change#,next_time FROM v$log;


SQL> DROP TABLE hr.emp_new PURGE;

SQL> CREATE TABLE hr.emp_new AS SELECT * FROM hr.employees;

SQL> SELECT COUNT(*) FROM hr.emp_new;
  COUNT(*)
----------
       106

SELECT e.owner,e.segment_name,e.tablespace_name,f.file_name
FROM dba_extents e, dba_data_files f
WHERE e.file_id = f.file_id
AND e.segment_name = 'EMP_NEW'
AND e.owner = 'HR';
OWNER   SEGMENT_NAME         TABLESPACE_NAME                FILE_NAME
------- -------------------- ------------------------------ ------------------------------------------
HR      EMP_NEW              USERS                          /u01/app/oracle/oradata/ora11g/users01.dbf


SQL> SELECT sequence#,status,first_change#,first_time,next_change#,next_time FROM v$log;
 SEQUENCE# STATUS           FIRST_CHANGE# FIRST_TIME  NEXT_CHANGE# NEXT_TIME
---------- ---------------- ------------- ----------- ------------ ---------
        98 INACTIVE               1987011 24/01/09         2007014 24/01/09
        99 CURRENT                2007014 24/01/09      2.8147E+14
        97 INACTIVE               1987008 24/01/09         1987011 24/01/09

ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;

SQL> SELECT sequence#,status,first_change#,first_time,next_change#,next_time FROM v$log;
 SEQUENCE# STATUS           FIRST_CHANGE# FIRST_TIME  NEXT_CHANGE# NEXT_TIME
---------- ---------------- ------------- ----------- ------------ ---------
       101 INACTIVE               2038577 24/01/11         2038581 24/01/11
       102 CURRENT                2038581 24/01/11      2.8147E+14
       100 INACTIVE               2038568 24/01/11         2038577 24/01/11

>> Log Switch 전환되어 Redo log에 백업 시점의 SEQ#99 정보가 없어졌다.


SQL> SHUTDOWN IMMEDIATE


# 장애 발생
SQL> ! rm /u01/app/oracle/oradata/ora11g/users01.dbf


SQL> STARTUP
...
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/ora11g/users01.dbf'

>> 물리적 데이터 파일 헤더의 SCN 정보와 control file 정보 오매칭 시 오류발생

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

>> 파일 손상 확인
# 복구 진행
  >> DB MOUNT 상태

# 일단 빨리 DB 오픈해야하기 때문에 오류대상만 offline 처리
ALTER DATABASE DATAFILE 4 OFFLINE DROP;

SQL> SELECT name,status,checkpoint_change# FROM v$datafile;
NAME                                               STATUS  CHECKPOINT_CHANGE#
-------------------------------------------------- ------- ------------------
/u01/app/oracle/oradata/ora11g/system01.dbf        SYSTEM             2038903
/u01/app/oracle/oradata/ora11g/sysaux01.dbf        ONLINE             2038903
/u01/app/oracle/oradata/ora11g/users01.dbf         OFFLINE            2038903
/u01/app/oracle/oradata/ora11g/example01.dbf       ONLINE             2038903
/u01/app/oracle/oradata/ora11g/undotbs01.dbf       ONLINE             2038903


# DB OPEN
SQL> ALTER DATABASE OPEN;

>> 장애공지 : 해당 테이블스페이스와 관련있는 테이블 체크, 점검 중 공유


# 오류파일만 백업 Restore -> Redo log file 이 살아있을거다라는 가정하에 진행 시도
SQL> ! cp -av /home/oracle/backup/noarch/20240111/users01.dbf /u01/app/oracle/oradata/ora11g

SQL> ! ls /home/oracle/backup/noarch/20240111
control01.ctl  sysaux01.dbf  temp01.dbf     users01.dbf
example01.dbf  system01.dbf  undotbs01.dbf


# Recovery
SQL> RECOVER TABLESPACE USERS;
ORA-00279: change 2037543 generated at 01/11/2024 10:04:53 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2024_01_11/o1_mf_1_99_%u_.arc
ORA-00280: change 2037543 for thread 1 is in sequence #99

Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO        -- 알아서 진행해라

ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2024_01_11/o1_mf_1_99_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2024_01_11/o1_mf_1_99_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

>> 복구 실패 : 백업 시점인 SEQ#99의 Redo Log가 없다. >> 전체 복구 해야한다 >> 불안전한 복구
>> 다른 테이블스페이스의 데이터는 정상인데 불안전한 복구 시 백업이후의 데이터가 없어진다.
    백업이후 데이터의 경우
    1안. 다른 테이블스페이스 export - 불안전복구 - import
    2안. db복제생성(클론db) - 복원 - 오류 데이터만 export - import


# DB 비정상 종료
SQL> SHUTDOWN ABORT


# 백업본 전체 Restore
SQL> ! cp -av /home/oracle/backup/noarch/20240111/*.dbf /u01/app/oracle/oradata/ora11g/
SQL> ! cp -av /home/oracle/backup/noarch/20240111/*.ctl /u01/app/oracle/oradata/ora11g/

>> Data File, Temp File, Control File


# DB MOUNT 단계로 만들기
SQL> STARTUP MOUNT

>> Redo Log File은 기존 파일 유지 중 -> Recovery한 Control File의 SCN 정보와 서로 다르다.


# Control File의 정보를 기준으로 sequence #99까지 Recovery
SQL> RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;
ORA-00279: change 2037543 generated at 01/11/2024 10:04:53 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2024_01_11/o1_mf_1_99_%u_.arc
ORA-00280: change 2037543 for thread 1 is in sequence #99

Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL      -- #99까지만 복구하자

Media recovery cancelled.

>> SCN 2037543 SEQ#99 가 필요한데 데이터가 없다.
>> UNTIL CANCEL USING : cancel base recovery


# DB 오픈 시 sequence #1 부터 다시 시작
SQL> ALTER DATABASE OPEN RESETLOGS;

>> OPEN RESETLOGS 적용 시 Whole database Backup 하자. 이전 백업본은 필요가 없다.


SQL> SELECT sequence#,status,first_change#,first_time,next_change#,next_time FROM v$log;
 SEQUENCE# STATUS           FIRST_CHANGE# FIRST_TIME  NEXT_CHANGE# NEXT_TIME
---------- ---------------- ------------- ----------- ------------ ---------
         1 CURRENT                2037544 24/01/11      2.8147E+14
         0 UNUSED                       0                        0
         0 UNUSED                       0                        0


SQL> SELECT name,status,checkpoint_change# FROM v$datafile;
NAME                                               STATUS  CHECKPOINT_CHANGE#
-------------------------------------------------- ------- ------------------
/u01/app/oracle/oradata/ora11g/system01.dbf        SYSTEM             2037547
/u01/app/oracle/oradata/ora11g/sysaux01.dbf        ONLINE             2037547
/u01/app/oracle/oradata/ora11g/users01.dbf         ONLINE             2037547
/u01/app/oracle/oradata/ora11g/example01.dbf       ONLINE             2037547
/u01/app/oracle/oradata/ora11g/undotbs01.dbf       ONLINE             2037547

 

<< 시나리오 11 >> Temp File 손상되었을 경우 복구 방식
  SELECT name FROM v$tempfile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/ora11g/temp01.dbf

SELECT * FROM dba_temp_files;
AUTOEXTENSIBLE  : YES           자동확장사용
MAXBYTES        : 34359721984   무한


# 장애 발생
SQL> ! rm /u01/app/oracle/oradata/ora11g/temp01.dbf


SQL> SELECT count(*) FROM hr.employees;

SQL> SELECT * FROM hr.employees ORDER BY 1;

>> 조회 가능


SQL> SHOW PARAMETER sort_area_size
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sort_area_size                       integer     1048576

>> 1M 안에서 SORT 작업을 진행할 경우 문제가 없다.


# 해당 SESSION에서의 SORT 작업 메모리 크기 수정
SQL> ALTER SESSION SET sort_area_size = 0;

SQL> SHOW PARAMETER sort_area_size
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sort_area_size                       integer     98304      -- 96KB


SQL> SELECT * FROM hr.employees ORDER BY 1;

>> 이미 실행한적이 있어 실행계획을 생성되어 작동 한다.


SELECT s.*, b.*
FROM dba_objects s, dba_objects b
ORDER BY 1,2,3,4;
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

>> SORT 작업 시 사용되는 메모리가 초과되어 디스크에서 Temp File을 사용해야하는데 파일이 없다.
>> 해당 SESSION에서 문제되는 SQL문에 대해서만 오류가 발생한다.

SQL> select * from hr.employees order by 1;
>> 한정된 메모리 내라면 문제없다. 잘된다.


SQL> ! ls /u01/app/oracle/oradata/ora11g/temp01.dbf
ls: cannot access /u01/app/oracle/oradata/ora11g/temp01.dbf: No such file or directory
>> 파일 손상 오류 확인

>> 장애공지


# alert log file 이 있는 위치 정보
SQL> SHOW PARAMETER background_dump_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string      /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace


SELECT name,status FROM v$tempfile;
STATUS ENABLED       BYTES NAME
------ ---------- -------- -----------------------------------------
ONLINE READ WRITE 20971520 /u01/app/oracle/oradata/ora11g/temp01.dbf


SELECT * FROM database_properties;
DEFAULT_TEMP_TABLESPACE : TEMP
# 해결방법 1. Temp File 변경
  # Temp File 추가
SQL> ALTER TABLESPACE temp ADD TEMPFILE '/u01/app/oracle/oradata/ora11g/temp02.dbf' SIZE 20M;

SQL> SELECT name FROM v$tempfile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/ora11g/temp02.dbf
/u01/app/oracle/oradata/ora11g/temp01.dbf

# 기존 Temp File 삭제
SQL> ALTER TABLESPACE temp DROP TEMPFILE '/u01/app/oracle/oradata/ora11g/temp01.dbf';

SQL> SELECT name,status FROM v$tempfile;
NAME                                               STATUS
-------------------------------------------------- -------
/u01/app/oracle/oradata/ora11g/temp02.dbf          ONLINE
/u01/app/oracle/oradata/ora11g/temp01.dbf          OFFLINE

>> 원래 삭제되어야 한다.


SQL> ALTER SESSION SET sort_area_size = 0;

SELECT s.*, b.*
FROM dba_objects s, dba_objects b
ORDER BY 1,2,3,4;
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

>> 확장하려는데 20M 다차서 공간부족하다
>> 문장레벨 오류

SELECT FILE_NAME,TABLESPACE_NAME,STATUS,AUTOEXTENSIBLE,BYTES FROM dba_temp_files;
FILE_NAME                                     TABLESPACE_NAME STATUS  AUTOEXTENSIBLE     BYTES
--------------------------------------------- --------------- ------- -------------- ---------
/u01/app/oracle/oradata/ora11g/temp02.dbf                TEMP ONLINE              NO  20971520
/u01/app/oracle/oradata/ora11g/temp01.dbf                TEMP OFFLINE

# 오류해결 ORA-01652: 임시 세그먼트를 확장할 수 없을 때 발생한다.
  방법1. Temp Size 크키 조정

SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/ora11g/temp02.dbf' RESIZE 100M;

SELECT FILE_NAME,TABLESPACE_NAME,STATUS,AUTOEXTENSIBLE,BYTES FROM dba_temp_files;
FILE_NAME                                     TABLESPACE_NAME STATUS  AUTOEXTENSIBLE     BYTES
--------------------------------------------- --------------- ------- -------------- ---------
/u01/app/oracle/oradata/ora11g/temp02.dbf                TEMP ONLINE              NO 104857600
/u01/app/oracle/oradata/ora11g/temp01.dbf                TEMP OFFLINE

  방법2. 자동확장기능 활성화

SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/ora11g/temp02.dbf' AUTOEXTEND ON;

SELECT FILE_NAME,TABLESPACE_NAME,STATUS,AUTOEXTENSIBLE,BYTES FROM dba_temp_files;
FILE_NAME                                     TABLESPACE_NAME STATUS  AUTOEXTENSIBLE     BYTES
--------------------------------------------- --------------- ------- -------------- ---------
/u01/app/oracle/oradata/ora11g/temp02.dbf                TEMP ONLINE             YES 104857600
/u01/app/oracle/oradata/ora11g/temp01.dbf                TEMP OFFLINE
  방법3. Temp File 추가하여 다중화

ALTER TABLESPACE temp ADD TEMPFILE '/u01/app/oracle/oradata/ora11g/temp03.dbf'
SIZE 10M AUTOEXTEND ON;

SELECT FILE_NAME,TABLESPACE_NAME,STATUS,AUTOEXTENSIBLE,BYTES FROM dba_temp_files;
FILE_NAME                                     TABLESPACE_NAME STATUS  AUTOEXTENSIBLE     BYTES
--------------------------------------------- --------------- ------- -------------- ---------
/u01/app/oracle/oradata/ora11g/temp02.dbf                TEMP ONLINE             YES 104857600
/u01/app/oracle/oradata/ora11g/temp01.dbf                TEMP OFFLINE
/u01/app/oracle/oradata/ora11g/temp03.dbf                TEMP ONLINE             YES  10485760

# 해결방법 2.  새로운 Temp Tablespace 생성하고 Default Temp Tablespace 지정
  # Temp Tablespace 생성
CREATE TEMPORARY TABLESPACE temp_new TEMPFILE
'/u01/app/oracle/oradata/ora11g/temp_new01.dbf' SIZE 20M AUTOEXTEND ON;


# Default Temp Tablespace 지정
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_new;

SELECT * FROM database_properties;
DEFAULT_TEMP_TABLESPACE : TEMP_NEW


SELECT * FROM dba_users;

>> Defaulte Temporary Tablespace를 사용하고있는 테이블의 값이 전부 temp_new 로 변경되었다.


# 기존 Temp Tablespace 삭제
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

>> 너무 오래걸릴 경우 세션을 재접속 후 진행해보자


SELECT FILE_NAME,TABLESPACE_NAME,STATUS,AUTOEXTENSIBLE,BYTES FROM dba_temp_files;
FILE_NAME                                     TABLESPACE_NAME STATUS  AUTOEXTENSIBLE     BYTES
--------------------------------------------- --------------- ------- -------------- ---------
/u01/app/oracle/oradata/ora11g/temp_new01.dbf        TEMP_NEW ONLINE             YES  20971520


SQL> ! ls /u01/app/oracle/oradata/ora11g
control01.ctl  redo01.log  redo03.log    system01.dbf    undotbs01.dbf
example01.dbf  redo02.log  sysaux01.dbf  temp_new01.dbf  users01.dbf
# 해결방법 3.  Temp File 손상된걸 모르는 상황에서 DB 재부팅 시 오라클이 알아서 RE-CREATE TEMP FILE 실행
  # 장애 발생
SQL> ! rm /u01/app/oracle/oradata/ora11g/temp_new01.dbf


SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP

-- Alert Log
-- ...
--ALTER DATABASE OPEN
-- ...
--Re-creating tempfile /u01/app/oracle/oradata/ora11g/temp_new01.dbf
-- ...
--Completed: ALTER DATABASE OPEN


SELECT FILE_NAME,TABLESPACE_NAME,STATUS,AUTOEXTENSIBLE,BYTES FROM dba_temp_files;
FILE_NAME                                     TABLESPACE_NAME STATUS  AUTOEXTENSIBLE     BYTES
--------------------------------------------- --------------- ------- -------------- ---------
/u01/app/oracle/oradata/ora11g/temp_new01.dbf        TEMP_NEW ONLINE             YES  20971520


SQL> ! ls /u01/app/oracle/oradata/ora11g
control01.ctl  redo01.log  redo03.log    system01.dbf    undotbs01.dbf
example01.dbf  redo02.log  sysaux01.dbf  temp_new01.dbf  users01.dbf

>> Temp File이 자동으로 생성되었다.


** 백업한 Temp File은 언제 사용? -> 불안전한 복구 시 사용한다.




SELECT s.*, b.*
FROM dba_objects s, dba_objects b
ORDER BY 1,2,3,4;

>>> 데이터가 많아 원래 오래걸린다.




** Whole database backup 왜 함?
- 과거의 시점으로 돌아가야하는 경우 = 불안전한 복구에 필요
- Data File, Temp File, Control File, Pfile, Redo Log File
- redo log 가 순환형식이라 정기 백업 필요
- undo tablespace 변경 시에도 백업 필요
- datafile, control file, redo log file 이것만 있으면 DB생성 가능 >> 보안에 주의

1. create pfile
2. DB close (checkpoint)
3. CP Data File, Temp File, Control File, Redo Log File
4. DB open

저작자표시

'Oracle 11g > Backup & Recovery' 카테고리의 다른 글

65 Backup&Recovery, Archive Log Mode  (0) 2024.01.15
64 Backup&Recovery, Archive Log Mode  (0) 2024.01.12
62 Backup&Recovery, Noarchive Log, Data File, Undo Data File  (1) 2024.01.10
61 Backup&Recovery, Noarchive Log, Data File  (0) 2024.01.09
60 Backup  (1) 2024.01.08
'Oracle 11g/Backup & Recovery'의 다른글
  • 현재글63 Backup&Recovery, Noarchive Log, Redo Log File, Temp File

티스토리툴바