■ User Managed ( noarchive log mode ) Recovery 13개 시나리오

YoonSeo Park ·2024년 1월 19일
Recoverybackuporacleuser managed

ORACLE 백업/복구 시나리오

목록 보기
1/6
post-thumbnail

장애, 백업,복구 시나리오 - noarchive log mode

▶ 1. 백업 이후에 redo정보가 있을 경우 시나리오 (특정한 데이터 파일이 손상)

● 1. backup할것 위치 확인

SELECT tablespace_name, file_name, bytes, status
FROM dba_data_files;

SELECT tablespace_name, file_name, bytes, status
FROM dba_temp_files;

● 2. db shutdown

  • 이유
    : 데이터베이스를 백업할 때 데이터베이스를 내리는 이유는 백업 중에 데이터의 일관성과 정합성을 유지하기 위함입니다. 데이터베이스를 온라인 상태에서 백업하는 경우, 데이터베이스는 계속해서 변경되고 새로운 트랜잭션이 발생할 수 있습니다. 이러한 상태에서 백업을 수행하면 백업 파일에 포함된 데이터가 중간에 변경되거나 불일치할 수 있습니다
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

● 3. 원하는 위치에 현재 datafile, tempfile, controlfile, redologfile 복사

[oracle@oracle ~]$ mkdir -p backup/noarch/
[oracle@oracle ~]$ cd backup/noarch/
[oracle@oracle noarch]$ cd /u01/app/oracle/oradata/ora11g/
[oracle@oracle ora11g]$ ls
control01.ctl  redo01.log  redo03.log    system01.dbf  undotbs01.dbf
example01.dbf  redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf
[oracle@oracle ora11g]$ date
Tue Jan  9 10:31:14 KST 2024
[oracle@oracle ora11g]$ cp -av *.* /home/oracle/backup/noarch
‘control01.ctl’ -> ‘/home/oracle/backup/noarch/control01.ctl’
‘example01.dbf’ -> ‘/home/oracle/backup/noarch/example01.dbf’
‘redo01.log’ -> ‘/home/oracle/backup/noarch/redo01.log’
‘redo02.log’ -> ‘/home/oracle/backup/noarch/redo02.log’
‘redo03.log’ -> ‘/home/oracle/backup/noarch/redo03.log’
‘sysaux01.dbf’ -> ‘/home/oracle/backup/noarch/sysaux01.dbf’
‘system01.dbf’ -> ‘/home/oracle/backup/noarch/system01.dbf’
‘temp01.dbf’ -> ‘/home/oracle/backup/noarch/temp01.dbf’
‘undotbs01.dbf’ -> ‘/home/oracle/backup/noarch/undotbs01.dbf’
‘users01.dbf’ -> ‘/home/oracle/backup/noarch/users01.dbf’
[oracle@oracle noarch]$ pwd
/home/oracle/backup/noarch
[oracle@oracle noarch]$ ls
control01.ctl  redo01.log  redo03.log    system01.dbf  undotbs01.dbf
example01.dbf  redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf


select * from v$log;

[hr session]

create table new(id number) tablespace example;

insert into new(id) values(1);

commit;

select * from new;

[sys session]

select f.file_name
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'NEW'
and e.owner = 'HR';

FILE_NAME
----------
/u01/app/oracle/oradata/ora11g/example01.dbf

● 4. 장애 유발

  • db shutdown
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !
  • 물리적인 example 테이블스페이스 rm
[oracle@oracle ~]$ rm  /u01/app/oracle/oradata/ora11g/example01.dbf
[oracle@oracle ~]$ ls  /u01/app/oracle/oradata/ora11g/example01.dbf
ls: cannot access /u01/app/oracle/oradata/ora11g/example01.dbf: No such file or directory
[oracle@oracle ~]$ exit
exit
  • db startup , example테이블스페이스가 없어서 mount까지만 된다. (open에서 장애)
SQL> startup
ORACLE instance started.

Total System Global Area  531476480 bytes
Fixed Size                  1365796 bytes
Variable Size             201328860 bytes
Database Buffers          322961408 bytes
Redo Buffers                5820416 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oracle/oradata/ora11g/example01.dbf'

● 5. backup 파일 restore

[oracle@oracle noarch]$ pwd
/home/oracle/backup/noarch
[oracle@oracle noarch]$ ls example01.dbf
example01.dbf
[oracle@oracle noarch]$ cp -av example01.dbf /u01/app/oracle/oradata/ora11g/example01.dbf
‘example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’

● 6. recover

  • recover
    : 마지막 백업시점까지
    리두 정보를 가지고 recover
  • tablespace 이름으로 recover
SQL> recover tablespace example;
Media recovery complete.
  • 그냥 db recover
recover database;

● 7. db open

SQL> alter database open;

Database altered.

▶ 2. 백업 이후에 redo정보가 없을 경우 시나리오 (특정한 데이터 파일이 손상)

★ noarchive 모드에서 redo 가 살아있지 않을때
: 로그스위치로 인하여 redo 정보가 살아있지않고 gap이 있을경우 불완전한 복구가 된다.
따라서 datafile,tempfile,controlfile,redologfile 전체를 백업하여 백업한시점으로 돌아가야한다 -> 불완전복구

● 1. db shutdown

  • SCN 번호 확인
select current_scn from v$database;
select name, checkpoint_change# from v$datafile;
select * from v$log;
---
/u01/app/oracle/oradata/ora11g/system01.dbf	1448669
/u01/app/oracle/oradata/ora11g/sysaux01.dbf	1448669
/u01/app/oracle/oradata/ora11g/users01.dbf	1448669
/u01/app/oracle/oradata/ora11g/example01.dbf	1448669
/u01/app/oracle/oradata/ora11g/undotbs01.dbf	1448669
---
  • shutdown
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !

● 2. backup 파일 restore

[oracle@oracle ~]$ cd /home/oracle/backup/noarch/
[oracle@oracle noarch]$ ls
control01.ctl  redo01.log  redo03.log    system01.dbf  undotbs01.dbf
example01.dbf  redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf
[oracle@oracle noarch]$ cp -av *.* /u01/app/oracle/oradata/ora11g/
‘control01.ctl’ -> ‘/u01/app/oracle/oradata/ora11g/control01.ctl’
‘example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’
‘redo01.log’ -> ‘/u01/app/oracle/oradata/ora11g/redo01.log’
‘redo02.log’ -> ‘/u01/app/oracle/oradata/ora11g/redo02.log’
‘redo03.log’ -> ‘/u01/app/oracle/oradata/ora11g/redo03.log’
‘sysaux01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/sysaux01.dbf’
‘system01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/system01.dbf’
‘temp01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/temp01.dbf’
‘undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/undotbs01.dbf’
‘users01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/users01.dbf’
[oracle@oracle noarch]$ exit
exit

● 3. db start

SQL> startup
ORACLE instance started.

Total System Global Area  531476480 bytes
Fixed Size                  1365796 bytes
Variable Size             201328860 bytes
Database Buffers          322961408 bytes
Redo Buffers                5820416 bytes
Database mounted.
Database opened.
  • SCN 번호 확인
select current_scn from v$database;
select name, checkpoint_change# from v$datafile;
select * from v$log;
---
/u01/app/oracle/oradata/ora11g/system01.dbf	1446401
/u01/app/oracle/oradata/ora11g/sysaux01.dbf	1446401
/u01/app/oracle/oradata/ora11g/users01.dbf	1446401
/u01/app/oracle/oradata/ora11g/example01.dbf	1446401
/u01/app/oracle/oradata/ora11g/undotbs01.dbf	1446401
---

● 4 . hr에서 작업
[hr session]

  • new 테이블 만들기 전으로 돌아갔으니 테이블이 없다
select * from new;
ORA-00942: 테이블 또는 뷰가 존재하지 않습니다
00942. 00000 -  "table or view does not exist"
  • 작업
create table new(id number) tablespace example;

insert into new(id) values(1);

commit;

select * from new;

● 5 . sys session에서 로그스위치 발생

  • 강제로 로그스위치해서 over write 되게 만들기

[sys session]

alter system switch logfile;

alter system switch logfile;

alter system switch logfile;
  • scn 확인
select current_scn from v$database;

CURRENT_SCN
-----------
    1452489

select name, checkpoint_change# from v$datafile;


NAME										CHECKPOINT_CHANGE#
------------------------------------------------
/u01/app/oracle/oradata/ora11g/system01.dbf	1452371
/u01/app/oracle/oradata/ora11g/sysaux01.dbf	1452371
/u01/app/oracle/oradata/ora11g/users01.dbf	1452371
/u01/app/oracle/oradata/ora11g/example01.dbf	1452371
/u01/app/oracle/oradata/ora11g/undotbs01.dbf	1452371

select * from v$log;
select f.file_name
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'NEW'
and e.owner = 'HR';

FILE_NAME
------------------------------------------------
/u01/app/oracle/oradata/ora11g/example01.dbf

● 6 . emample01.dbf 삭제 -> 장애발생

  • db shutdown
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
  • example01.dbf 파일 삭제 -> 장애유발
SQL> !
[oracle@oracle ~]$ rm  /u01/app/oracle/oradata/ora11g/example01.dbf
[oracle@oracle ~]$ ls  /u01/app/oracle/oradata/ora11g/example01.dbf
ls: cannot access /u01/app/oracle/oradata/ora11g/example01.dbf: No such file or directory
[oracle@oracle ~]$ exit
exit
  • db startup
SQL> startup
ORACLE instance started.

Total System Global Area  531476480 bytes
Fixed Size                  1365796 bytes
Variable Size             201328860 bytes
Database Buffers          322961408 bytes
Redo Buffers                5820416 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oracle/oradata/ora11g/example01.dbf'


SQL> select status from v$instance;

STATUS
------------
MOUNTED

● 7. backup 파일 restore

SQL> !
[oracle@oracle ~]$ cd /home/oracle/backup/noarch
[oracle@oracle noarch]$ cp -av example01.dbf /u01/app/oracle/oradata/ora11g/example01.dbf
‘example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’

● 8. recover 실패
: redo 정보가 없기때문에

SQL> recover database;
ORA-00279: change 1446398 generated at 01/09/2024 10:38:43 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2024_01_09/o1_mf_1_43_%u_.a
rc
ORA-00280: change 1446398 for thread 1 is in sequence #43


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00308: cannot open archived log
'/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2024_01_09/o1_mf_1_43_%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_09/o1_mf_1_43_%u_.
arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

● 9. backup 파일 restore , 불완전 복구를 해야한다.

  • db shutdown
SQL> shutdown abort
ORACLE instance shut down.
  • backup 파일 restore
[oracle@oracle ~]$ cd /home/oracle/backup/noarch/
[oracle@oracle noarch]$ ls
control01.ctl  redo01.log  redo03.log    system01.dbf  undotbs01.dbf
example01.dbf  redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf
[oracle@oracle noarch]$ cp -av *.* /u01/app/oracle/oradata/ora11g/
‘control01.ctl’ -> ‘/u01/app/oracle/oradata/ora11g/control01.ctl’
‘example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’
‘redo01.log’ -> ‘/u01/app/oracle/oradata/ora11g/redo01.log’
‘redo02.log’ -> ‘/u01/app/oracle/oradata/ora11g/redo02.log’
‘redo03.log’ -> ‘/u01/app/oracle/oradata/ora11g/redo03.log’
‘sysaux01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/sysaux01.dbf’
‘system01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/system01.dbf’
‘temp01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/temp01.dbf’
‘undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/undotbs01.dbf’
‘users01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/users01.dbf’

● 10. db start

SQL> startup
ORACLE instance started.

Total System Global Area  531476480 bytes
Fixed Size                  1365796 bytes
Variable Size             201328860 bytes
Database Buffers          322961408 bytes
Redo Buffers                5820416 bytes
Database mounted.
Database opened.

▶ 3. 백업 받지 않은 테이블 스페이스 1시나리오

[he session]

  • 테이블스페이스 생성
create tablespace insa_tbs datafile
'/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf' size 10m;
  • 확인
select tablespace_name, file_name from dba_data_files;
  • 체크포인트 확인
select name, checkpoint_change# from v$datafile;
---
/u01/app/oracle/oradata/ora11g/system01.dbf	1450997
/u01/app/oracle/oradata/ora11g/sysaux01.dbf	1450997
/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf	1452359
/u01/app/oracle/oradata/ora11g/users01.dbf	1450997
/u01/app/oracle/oradata/ora11g/example01.dbf	1450997
/u01/app/oracle/oradata/ora11g/undotbs01.dbf	1450997

● 1. 장애발생 : db 올린상태에서 테이블스페이스 삭제

[oracle@oracle ~]$ rm /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf
[oracle@oracle ~]$ exit
exit

● 2. db shutdown, startup

SQL> shutdown immediate
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  531476480 bytes
Fixed Size                  1365796 bytes
Variable Size             201328860 bytes
Database Buffers          322961408 bytes
Redo Buffers                5820416 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf'

● 3. 문제가 되는 테이블스페이스 offline으로 떨어 뜨리기

  • noarchive log mode 에서는 데이터파일을 offline drop 해야한다. (archive mode에서는 offline 까지만 써도 된다.)
SQL> alter database datafile '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf' offline drop;
Database altered.
  • 확인
select name,status from v$datafile;

● 4. db open

SQL> alter database open;

Database altered.
  • 테이블 스페이스 삭제 (backup 해놓은게 없으니깐)
DROP TABLESPACE insa_tbs INCLUDING CONTENTS
AND DATAFILES;

▶ 4. 백업 받지 않은 테이블 스페이스 2시나리오 (redo정보가 있을 경우)

  • 테이블스페이스 생성
create tablespace insa_tbs datafile
'/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf' size 10m;
  • 테이블스페이스 확인
select name,status from v$datafile;
  • 테이블 create,insert
create table hr.new(id number) tablespace insa_tbs;
insert into hr.new(id) values(1);
commit;

● 1. 장애유발 : 운영중에 테이블스페이스 삭제 

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

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

● 2. 문제가 되는 테이블스페이스 offline으로 떨어 뜨리기

select name,status from v$datafile;

alter database datafile '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf' offline drop;

● 3. 비어있는 테이블스페이스(datafile) 생성

alter database create datafile '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf';
  • 확인
SQL>  ! ls /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf
/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf

● 4. datafile recover

SQL> alter database recover datafile '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf';

Database altered.

● 5. datafile online 으로 변경

select name,status from v$datafile;

alter database datafile '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf' online;

select name,status from v$datafile;
  • 조회
select * from hr.new;
  • 테이블스페이스 삭제
DROP TABLESPACE insa_tbs INCLUDING CONTENTS
AND DATAFILES;

▶ 5. 백업 받지 않은 테이블 스페이스 3시나리오 (redo정보가 없을 경우)

  • 테이블스페이스 생성
create tablespace insa_tbs datafile
'/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf' size 10m;
  • 테이블스페이스 확인
select name,status from v$datafile;
  • 테이블 create,insert
create table hr.new(id number) tablespace insa_tbs;
insert into hr.new(id) values(1);
commit;

● 1. 장애유발 : 운영중에 테이블스페이스 삭제 

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

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

● 2. 문제가 되는 테이블스페이스 offline으로 떨어 뜨리기

select name,status from v$datafile;

alter database datafile '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf' offline drop;

● 3. 비어있는 테이블스페이스(datafile) 생성

alter database create datafile '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf';
  • 확인
SQL>  ! ls /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf
/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf

● 4. sys session에서 로그스위치 발생

  • alert log file 항상보기
[oracle@oracle trace]$ pwd
/u01/app/oracle/diag/rdbms/ora11g/ora11g/trace
[oracle@oracle trace]$ tail -f alert_ora11g.log
  • 강제로 로그스위치해서 over write 되게 만들기

[sys session]

alter system switch logfile;

alter system switch logfile;

alter system switch logfile;

● 5. datafile recover -> 에러

SQL> alter database recover datafile '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf';
alter database datafile '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf' offline drop
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files

● 5. db shutdown, startup

SQL> shutdown immediate
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  531476480 bytes
Fixed Size                  1365796 bytes
Variable Size             201328860 bytes
Database Buffers          322961408 bytes
Redo Buffers                5820416 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf'

● 6. 테이블 스페이스 삭제 (backup 해놓은게 없으니깐)

DROP TABLESPACE insa_tbs INCLUDING CONTENTS
AND DATAFILES;
  • 테이블 조회 안되는 것 확인
SQL> select * from hr.new;
select * from hr.new
                 *
ERROR at line 1:
ORA-00942: table or view does not exist

▶ 6. SYSTEM TABLESAPCE 속한 데이터 파일 삭제 시나리오 (redo정보가 있을 경우)

● 1. scn 확인

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1490227


SQL> select name, checkpoint_change# from v$datafile;

NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/ora11g/system01.dbf                   1489665
/u01/app/oracle/oradata/ora11g/sysaux01.dbf                   1489665
/u01/app/oracle/oradata/ora11g/users01.dbf                    1489665
/u01/app/oracle/oradata/ora11g/example01.dbf                  1489665
/u01/app/oracle/oradata/ora11g/undotbs01.dbf                  1489665

● 2. 장애유발 : system01.dbf 파일 삭제

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

SQL> alter system checkpoint;
alter system checkpoint
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 8610
Session ID: 9 Serial number: 3

SQL> select status from v$instance;
ERROR:
ORA-03114: not connected to ORACLE

SQL> startup
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
  • 접속이 끊어져서 재접속후 startup
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  531476480 bytes
Fixed Size                  1365796 bytes
Variable Size             201328860 bytes
Database Buffers          322961408 bytes
Redo Buffers                5820416 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf'


SQL> select status from v$instance;

STATUS
------------
MOUNTED
  • 시스템테이블스페이스의 데이터파일 system01.dbf 는 dictionary 정보가 가지고있는 파일이기때문에 offline으로 db를 start 할수는 없다.
SQL> select name, status from v$datafile;

NAME                                               STATUS
-------------------------------------------------- -------
/u01/app/oracle/oradata/ora11g/system01.dbf        SYSTEM
/u01/app/oracle/oradata/ora11g/sysaux01.dbf        ONLINE
/u01/app/oracle/oradata/ora11g/users01.dbf         ONLINE
/u01/app/oracle/oradata/ora11g/example01.dbf       ONLINE
/u01/app/oracle/oradata/ora11g/undotbs01.dbf       ONLINE

● 3. 백업 restore

[oracle@oracle ~]$ cd backup/noarch/20240110/
[oracle@oracle 20240110]$ ls
control01.ctl  redo01.log  redo03.log    system01.dbf  undotbs01.dbf
example01.dbf  redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf
[oracle@oracle 20240110]$ cp -av system01.dbf /u01/app/oracle/oradata/ora11g/
‘system01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/system01.dbf’
[oracle@oracle 20240110]$ ls /u01/app/oracle/oradata/ora11g/
control01.ctl  redo01.log  redo03.log    system01.dbf  undotbs01.dbf
example01.dbf  redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf

● 4. system tablesapce 복구

  • 백업 restore의 마지막 SCN번호와 redo log file에 있는 SCN번호를 비교하여 복구한다.
  • 마지막 백업시점(마지막 체크포인트(SCN)) 이후 변경 이력정보를 redo log file 에서 찾아서 복구 작업 진행한다.
SQL> recover tablespace system;
Media recovery complete.
SQL> alter database open;

Database altered.

● 5. 마지막 scn 까지 맞춰졌는지 확인

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1510972

SQL> select name, checkpoint_change# from v$datafile;

NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/ora11g/system01.dbf                   1510435
/u01/app/oracle/oradata/ora11g/sysaux01.dbf                   1510435
/u01/app/oracle/oradata/ora11g/users01.dbf                    1510435
/u01/app/oracle/oradata/ora11g/example01.dbf                  1510435
/u01/app/oracle/oradata/ora11g/undotbs01.dbf                  1510435

▶ 7. SYSTEM TABLESAPCE 속한 데이터 파일 삭제 시나리오 (redo정보가 없을 경우)

● 1. SCN, logfile 확인

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1510972

SQL> select name, checkpoint_change# from v$datafile;

NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/ora11g/system01.dbf                   1510435
/u01/app/oracle/oradata/ora11g/sysaux01.dbf                   1510435
/u01/app/oracle/oradata/ora11g/users01.dbf                    1510435
/u01/app/oracle/oradata/ora11g/example01.dbf                  1510435
/u01/app/oracle/oradata/ora11g/undotbs01.dbf                  1510435

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1         49   52428800        512          1 NO  CURRENT       1510434 09-JAN-24    2.8147E+14
         2          1         47   52428800        512          1 NO  INACTIVE      1478367 09-JAN-24       1478444 09-JAN-24
         3          1         48   52428800        512          1 NO  INACTIVE      1478444 09-JAN-24       1510434 09-JAN-24

● 2. redo log file 에 overwrite 되게 로그 스위치 발생

  • 로그스위치 발생
SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.
  • backup파일과 redo정보 gap이 발생한다.
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1         52   52428800        512          1 NO  CURRENT       1512550 09-JAN-24    2.8147E+14
         2          1         50   52428800        512          1 NO  ACTIVE        1512538 09-JAN-24       1512547 09-JAN-24
         3          1         51   52428800        512          1 NO  ACTIVE        1512547 09-JAN-24       1512550 09-JAN-24

● 3. 장애발생 : 시스템테이블스페이스 데이터파일 삭제

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

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  531476480 bytes
Fixed Size                  1365796 bytes
Variable Size             201328860 bytes
Database Buffers          322961408 bytes
Redo Buffers                5820416 bytes
Database mounted.

● 4. 시스템 데이터파일 restore

[oracle@oracle ~]$ cp -av backup/noarch/20240110/system01.dbf /u01/app/oracle/oradata/ora11g/
‘backup/noarch/20240110/system01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/system01.dbf’

● 5. 완전복구 실패
: 마지막 백업 이후에 변경 이력정보가 없어서 완전 복구는 할 수 없다.

SQL> recover database;
ORA-00279: change 1489662 generated at 01/09/2024 19:36:55 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2024_01_09/o1_mf_1_48_%u_.arc
ORA-00280: change 1489662 for thread 1 is in sequence #48

● 6. datafile,tempfile,controlfile,redologfile 전부다 restore 해야한다. (불완전복구)

  • db shutdown
SQL> shutdown abort
ORACLE instance shut down.
  • 전부 restore
[oracle@oracle ~]$ cp -av backup/noarch/20240110/*.* /u01/app/oracle/oradata/ora11g/
‘backup/noarch/20240110/control01.ctl’ -> ‘/u01/app/oracle/oradata/ora11g/control01.ctl’
‘backup/noarch/20240110/example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’
‘backup/noarch/20240110/redo01.log’ -> ‘/u01/app/oracle/oradata/ora11g/redo01.log’
‘backup/noarch/20240110/redo02.log’ -> ‘/u01/app/oracle/oradata/ora11g/redo02.log’
‘backup/noarch/20240110/redo03.log’ -> ‘/u01/app/oracle/oradata/ora11g/redo03.log’
‘backup/noarch/20240110/sysaux01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/sysaux01.dbf’
‘backup/noarch/20240110/system01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/system01.dbf’
‘backup/noarch/20240110/temp01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/temp01.dbf’
‘backup/noarch/20240110/undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/undotbs01.dbf’
‘backup/noarch/20240110/users01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/users01.dbf’
  • db open
SQL> startup
ORACLE instance started.

Total System Global Area  531476480 bytes
Fixed Size                  1365796 bytes
Variable Size             201328860 bytes
Database Buffers          322961408 bytes
Redo Buffers                5820416 bytes
Database mounted.
Database opened.

▶ 8. UNDO datafile 손상 시나리오 (redo정보가 없을 경우)

  • datafile, tablesapce 조회
select a.file#, b.name tbs_name, a.name file_name,
        a.status, a.checkpoint_change#
from v$datafile a, v$tablespace b
where a.ts# = b.ts#;
  • undo segment 조회
SQL> select segment_id, segment_name, owner, tablespace_name, status
from dba_rollback_segs;

SEGMENT_ID SEGMENT_NAME                   OWNER  TABLESPACE_NAME               STATUS
---------- ------------------------------ ------ ------------------------------ ----------------
         0 SYSTEM                         SYS    SYSTEM                        ONLINE
         1 _SYSSMU1_498470694$            PUBLIC UNDOTBS                       ONLINE
         2 _SYSSMU2_1139286244$           PUBLIC UNDOTBS                       ONLINE
         3 _SYSSMU3_2494311762$           PUBLIC UNDOTBS                       ONLINE
         4 _SYSSMU4_535427529$            PUBLIC UNDOTBS                       ONLINE
         5 _SYSSMU5_390294765$            PUBLIC UNDOTBS                       ONLINE
         6 _SYSSMU6_972788150$            PUBLIC UNDOTBS                       ONLINE
         7 _SYSSMU7_4232614060$           PUBLIC UNDOTBS                       ONLINE
         8 _SYSSMU8_1195849182$           PUBLIC UNDOTBS                       ONLINE
         9 _SYSSMU9_3360189142$           PUBLIC UNDOTBS                       ONLINE
        10 _SYSSMU10_1141378060$          PUBLIC UNDOTBS                       ONLINE
  • transaction 발생

[hr session]

update hr.employees
set salary = 2000
where employee_id = 100;
  • 트랜잭션이있는것 조회

[sys session]

select s.username, s.sid, s.serial#, r.name,
        t.xidusn, t.ubafil, t.ubablk, t.used_ublk
from v$session s, v$transaction t, v$rollname r
where s.taddr = t.addr
and t.xidusn = r.usn;

  • 트랜잭션 kill session 'SID,SERIAL#'
alter system kill session '17,65' immediate;
  • 접속이 끊어진다

[hr session]

SQL> select * from hr.employees;
select * from hr.employees
          *
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 21443
Session ID: 17 Serial number: 65
  • redo log 조회
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1         46   52428800        512          1 NO  INACTIVE      1478364 09-JAN-24       1478367 09-JAN-24
         2          1         47   52428800        512          1 NO  INACTIVE      1478367 09-JAN-24       1478444 09-JAN-24
         3          1         48   52428800        512          1 NO  CURRENT       1478444 09-JAN-24    2.8147E+14

● 1. 장애유발 : undo테이블스페이스 데이터파일 삭제

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

SQL> alter sysetem checkpoint;
alter sysetem checkpoint
      *
ERROR at line 1:
ORA-00940: invalid ALTER command
  • db shutdown, startup
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  531476480 bytes
Fixed Size                  1365796 bytes
Variable Size             201328860 bytes
Database Buffers          322961408 bytes
Redo Buffers                5820416 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/oradata/ora11g/undotbs01.dbf'
  • datafile 상태 확인
SQL> select name, status from v$datafile;

NAME                                               STATUS
-------------------------------------------------- -------
/u01/app/oracle/oradata/ora11g/system01.dbf        SYSTEM
/u01/app/oracle/oradata/ora11g/sysaux01.dbf        ONLINE
/u01/app/oracle/oradata/ora11g/users01.dbf         ONLINE
/u01/app/oracle/oradata/ora11g/example01.dbf       ONLINE
/u01/app/oracle/oradata/ora11g/undotbs01.dbf       ONLINE

● 2. 장애난 datafile 6 를 offline 상태로 변경

SQL> alter database datafile 6 offline for drop;

Database altered.

SQL> select name, status from v$datafile;

NAME                                               STATUS
-------------------------------------------------- -------
/u01/app/oracle/oradata/ora11g/system01.dbf        SYSTEM
/u01/app/oracle/oradata/ora11g/sysaux01.dbf        ONLINE
/u01/app/oracle/oradata/ora11g/users01.dbf         ONLINE
/u01/app/oracle/oradata/ora11g/example01.dbf       ONLINE
/u01/app/oracle/oradata/ora11g/undotbs01.dbf       OFFLINE

● 3. db open

SQL> alter database open;

Database altered.

● 4. undo 테이블스페이스 datafile 생성

  • DML (insert, update, delete) 작업이 안된다.
    undo테이블스페이스가 없으므로
SQL> update hr.employees
set salary = 2000
where employee_id = 100;  2    3
update hr.employees
          *
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'EXAMPLE'
  • undo 테이블스페이스 datafile 생성
SQL> create undo tablespace undo1 datafile '/u01/app/oracle/oradata/ora11g/undo01.dbf' size 10m autoextend on;

Tablespace created.

● 5. undo tablespace 변경

  • 현재 undo_tablespace 조회
SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     1800
undo_tablespace                      string      UNDOTBS <- 새로운 언두 수정
  • undo_tablespace 변경
SQL> alter system set undo_tablespace = undo1;

System altered.
  • undo_tablespace 변경된것 확인
SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     1800
undo_tablespace                      string      UNDO1

● 6. undo segments 확인

6-1. 만약 UNDOTBS가 OFFLINE 상태이면
7번으로 간다.

6-2. 만약 UNDOTBS가 NEED RECOVERY 상태이면
8번으로 간다.

SQL> select segment_id, segment_name, owner, tablespace_name, status
from dba_rollback_segs;  2

SEGMENT_ID SEGMENT_NAME                   OWNER  TABLESPACE_NAME               STATUS
---------- ------------------------------ ------ ------------------------------ ----------------
         0 SYSTEM                         SYS    SYSTEM                        ONLINE
         1 _SYSSMU1_498470694$            PUBLIC UNDOTBS                       OFFLINE
         2 _SYSSMU2_1139286244$           PUBLIC UNDOTBS                       OFFLINE
         3 _SYSSMU3_2494311762$           PUBLIC UNDOTBS                       OFFLINE
         4 _SYSSMU4_535427529$            PUBLIC UNDOTBS                       OFFLINE
         5 _SYSSMU5_390294765$            PUBLIC UNDOTBS                       OFFLINE
         6 _SYSSMU6_972788150$            PUBLIC UNDOTBS                       OFFLINE
         7 _SYSSMU7_4232614060$           PUBLIC UNDOTBS                       OFFLINE
         8 _SYSSMU8_1195849182$           PUBLIC UNDOTBS                       OFFLINE
         9 _SYSSMU9_3360189142$           PUBLIC UNDOTBS                       OFFLINE
        10 _SYSSMU10_1141378060$          PUBLIC UNDOTBS                       OFFLINE

SEGMENT_ID SEGMENT_NAME                   OWNER  TABLESPACE_NAME               STATUS
---------- ------------------------------ ------ ------------------------------ ----------------
        11 _SYSSMU11_723168542$           PUBLIC UNDO1                         ONLINE
        12 _SYSSMU12_2689673708$          PUBLIC UNDO1                         ONLINE
        13 _SYSSMU13_3980157822$          PUBLIC UNDO1                         ONLINE
        14 _SYSSMU14_2819430755$          PUBLIC UNDO1                         ONLINE
        15 _SYSSMU15_42532201$            PUBLIC UNDO1                         ONLINE
        16 _SYSSMU16_422771187$           PUBLIC UNDO1                         ONLINE
        17 _SYSSMU17_3987586638$          PUBLIC UNDO1                         ONLINE
        18 _SYSSMU18_2242969461$          PUBLIC UNDO1                         ONLINE
        19 _SYSSMU19_3530733757$          PUBLIC UNDO1                         ONLINE
        20 _SYSSMU20_3172209127$          PUBLIC UNDO1                         ONLINE

21 rows selected.

● 7. 기존 UNDO 테이블스페이스 삭제 (OFFLINE 상태)

SQL> drop tablespace undotbs including contents and datafiles;

Tablespace dropped.

끝

● 8. 초기파라미터 파일로 확인 (NEED RECOVERY 상태)

  • 초기파라미터 파일 생성
SQL> create pfile form spfile;
  • db shutdown
SQL> shutdown immediate

● 9. NEED RECOVERY 상태 인것만 OFFLINE 상태로 변경 ( 수동 변경 )

[oracle@oracle ~]$ cd $ORACLE_HOME/dbs
[oracle@oracle dbs]$ ls
hc_ora11g.dat  initora11g.ora  orapwora11g                spfileora11g.ora
init.ora       lkORA11G        spfileora11g_20231208.bak  spfileora11g.ora.bak
[oracle@oracle dbs]$ vi initora11g.ora
--밑에추가--
_offline_rollback_segments=(
_SYSSMU1_498470694$,  
_SYSSMU2_1139286244$,   
_SYSSMU3_2494311762$,         
_SYSSMU4_535427529$,
_SYSSMU5_390294765$,   
_SYSSMU6_972788150$,     
_SYSSMU7_4232614060$, 
_SYSSMU8_1195849182$,      
_SYSSMU9_3360189142$,       
_SYSSMU10_1141378060$)

● 10. 변경된 pfile로 db start

SQL> startup pfile='$ORACLE_HOME/dbs/initora11g.ora'
ORACLE instance started
  • 전의 undo tablespace 삭제
SQL> drop tablespace undotbs including contents and datafiles;

Tablespace dropped.

▶ 9.현재undo tbs와 백업 undo tbs가 다를때, 모든 datafile,tempfile,controlfile,redologfile 삭제 되었을때 시나리오

● 1. 장애발생 : 모두 삭제

[oracle@oracle ~]$ rm $ORACLE_HOME/dbs/*.*

● 2. backup본으로 restore

[oracle@oracle ~]$ cp -av  /home/oracle/backup/noarch/20240110 $ORACLE_HOME/dbs/
[oracle@oracle ~]$ cd $ORACLE_HOME/dbs/
[oracle@oracle dbs]$ ls
hc_ora11g.dat  initora11g.ora  orapwora11g                spfileora11g.ora
init.ora       lkORA11G        spfileora11g_20231208.bak  spfileora11g.ora.bak

● 3. init 파일의 undotbs 명 변경

[oracle@oracle dbs]$ vi initora11g.ora
--전에 undotbs 로 변경--
*.undo_tablespace='UNDOTBS'

● 4. pfile로 db start

[oracle@oracle dbs]$ exit
exit

SQL> startup pfile='$ORACLE_HOME/dbs/initora11g.ora'
ORACLE instance started.

Total System Global Area  531476480 bytes
Fixed Size                  1365796 bytes
Variable Size             201328860 bytes
Database Buffers          322961408 bytes
Redo Buffers                5820416 bytes
Database mounted.
Database opened.

● 5. spfile 생성 pfile을 이용한

SQL> create spfile from pfile;

File created.

● 6. db 재시작 (spfile로 열리게)

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  531476480 bytes
Fixed Size                  1365796 bytes
Variable Size             201328860 bytes
Database Buffers          322961408 bytes
Redo Buffers                5820416 bytes
Database mounted.
Database opened.

▶ 10. 모든 데이터파일, 리두로그파일, 컨트롤 파일이 있는 디스크 손상 시나리오

● 1. 쿼리문작성

■ control file 위치 변경 작업, 초기파라미터 파일중 pfile을 수정해서 수행

▶ 이전컨트롤파일 위치

*.control_files='/u01/app/oracle/oradata/ora11g/control01.ctl'

▶ 새로운 컨트롤 파일 위치

*.control_files='/home/oracle/ora_data/control01.ctl'

■ 데이터 파일, 리두로그파일 위치변경, 데이터 이관작업

alter database rename file '이전경로의 파일' to '새로운경로의 파일';

================
▶ 데이터 파일

  • 쿼리문
select 'alter database rename file '''||name||''' to ''/home/oracle/ora_data/''' from v$datafile;
alter database rename file '/u01/app/oracle/oradata/ora11g/system01.dbf' to '/home/oracle/ora_data/system01.dbf';

alter database rename file '/u01/app/oracle/oradata/ora11g/sysaux01.dbf' to '/home/oracle/ora_data/sysaux01.dbf';

alter database rename file '/u01/app/oracle/oradata/ora11g/users01.dbf' to '/home/oracle/ora_data/users01.dbf';

alter database rename file '/u01/app/oracle/oradata/ora11g/example01.dbf' to '/home/oracle/ora_data/example01.dbf';

alter database rename file '/u01/app/oracle/oradata/ora11g/undotbs01.dbf' to '/home/oracle/ora_data/undotbs01.dbf';

▶ temp 파일

  • 쿼리문
select 'alter database rename file '''||name||''' to ''/home/oracle/ora_data/'';' from v$tempfile;
alter database rename file '/u01/app/oracle/oradata/ora11g/temp01.dbf' to '/home/oracle/ora_data/temp01.dbf';

▶ redo 파일

  • 쿼리문
select 'alter database rename file '''||member||''' to ''/home/oracle/ora_data/'';' from v$logfile;
alter database rename file '/u01/app/oracle/oradata/ora11g/redo01.log' to '/home/oracle/ora_data/redo01.log';

alter database rename file '/u01/app/oracle/oradata/ora11g/redo02.log' to '/home/oracle/ora_data/redo02.log';

alter database rename file '/u01/app/oracle/oradata/ora11g/redo03.log' to '/home/oracle/ora_data/redo03.log';

● 2. 백업파일 체크하기

  • 초기파라미터 파일 생성
SQL> create pfile from spfile;

File created.

SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 .4/db_1/dbs/spfileora11g.ora
  • 대상파일 확인
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/system01.dbf
/u01/app/oracle/oradata/ora11g/sysaux01.dbf
/u01/app/oracle/oradata/ora11g/users01.dbf
/u01/app/oracle/oradata/ora11g/example01.dbf
/u01/app/oracle/oradata/ora11g/undotbs01.dbf

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

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/redo03.log
/u01/app/oracle/oradata/ora11g/redo02.log
/u01/app/oracle/oradata/ora11g/redo01.log
  • 백업본 체크
[oracle@oracle ~]$ cd backup/noarch/20240110/
[oracle@oracle 20240110]$ ls
control01.ctl  redo01.log  redo03.log    system01.dbf  undotbs01.dbf
example01.dbf  redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf

● 3. 장애유발 : 모든파일삭제

[oracle@oracle ~]$ cd /u01/app/oracle/oradata/ora11g/
[oracle@oracle ora11g]$ ls
control01.ctl  redo01.log  redo03.log    system01.dbf  undotbs01.dbf
example01.dbf  redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf
[oracle@oracle ora11g]$ rm *.*
[oracle@oracle ora11g]$ ls
[oracle@oracle ora11g]$ exit
exit

SQL> alter system checkpoint;
alter system checkpoint
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 31931
Session ID: 162 Serial number: 5

SQL> conn / as sysdba
Connected to an idle instance.
SQL> exit
Disconnected

● 4. 새로운 디스크에 백업본을 이관작업

[oracle@oracle ~]$ ls backup/noarch/20240110
control01.ctl  redo01.log  redo03.log    system01.dbf  undotbs01.dbf
example01.dbf  redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf
[oracle@oracle ~]$ pwd
/home/oracle
[oracle@oracle ~]$ mkdir ora_data -- 다른디스크라고 생각
[oracle@oracle ~]$ cd ora_data
[oracle@oracle ora_data]$ pwd
/home/oracle/ora_data
[oracle@oracle ora_data]$ cp -av /home/oracle/backup/noarch/20240110/*.* /home/oracle/ora_data/
‘/home/oracle/backup/noarch/20240110/control01.ctl’ -> ‘/home/oracle/ora_data/control01.ctl’
‘/home/oracle/backup/noarch/20240110/example01.dbf’ -> ‘/home/oracle/ora_data/example01.dbf’
‘/home/oracle/backup/noarch/20240110/redo01.log’ -> ‘/home/oracle/ora_data/redo01.log’
‘/home/oracle/backup/noarch/20240110/redo02.log’ -> ‘/home/oracle/ora_data/redo02.log’
‘/home/oracle/backup/noarch/20240110/redo03.log’ -> ‘/home/oracle/ora_data/redo03.log’
‘/home/oracle/backup/noarch/20240110/sysaux01.dbf’ -> ‘/home/oracle/ora_data/sysaux01.dbf’
‘/home/oracle/backup/noarch/20240110/system01.dbf’ -> ‘/home/oracle/ora_data/system01.dbf’
‘/home/oracle/backup/noarch/20240110/temp01.dbf’ -> ‘/home/oracle/ora_data/temp01.dbf’
‘/home/oracle/backup/noarch/20240110/undotbs01.dbf’ -> ‘/home/oracle/ora_data/undotbs01.dbf’
‘/home/oracle/backup/noarch/20240110/users01.dbf’ -> ‘/home/oracle/ora_data/users01.dbf’

● 5. 초기파라미터 파일에서 컨트롤 파일 위치정보 수정

[oracle@oracle ora_data]$ cd $ORACLE_HOME/dbs
[oracle@oracle dbs]$ ls
hc_ora11g.dat  initora11g.ora  orapwora11g                spfileora11g.ora
init.ora       lkORA11G        spfileora11g_20231208.bak  spfileora11g.ora.bak
  • controlfile 위치 새로운디스크로 변경
[oracle@oracle dbs]$ vi initora11g.ora
-- 변경 --
*.control_files='/home/oracle/ora_data/control01.ctl'

● 6. pfile 을 이용하여 mount단계까지 db start

system, undo, redologfile 파일은 open 단계(운영중)에서 변경 불가 이므로 mount단계 까지 db start

[oracle@oracle dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jan 10 01:54:03 2024

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

Connected to an idle instance.

SQL> startup pfile='$ORACLE_HOME/dbs/initora11g.ora' mount
ORACLE instance started.

Total System Global Area  531476480 bytes
Fixed Size                  1365796 bytes
Variable Size             201328860 bytes
Database Buffers          322961408 bytes
Redo Buffers                5820416 bytes
Database mounted.
SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /home/oracle/ora_data/control0
                                                 1.ctl

● 7. datafile,tempfile,redo logfile 위치변경

  • datafile 위치변경
alter database rename file '/u01/app/oracle/oradata/ora11g/system01.dbf' to '/home/oracle/ora_data/system01.dbf';

alter database rename file '/u01/app/oracle/oradata/ora11g/sysaux01.dbf' to '/home/oracle/ora_data/sysaux01.dbf';

alter database rename file '/u01/app/oracle/oradata/ora11g/users01.dbf' to '/home/oracle/ora_data/users01.dbf';

alter database rename file '/u01/app/oracle/oradata/ora11g/example01.dbf' to '/home/oracle/ora_data/example01.dbf';

alter database rename file '/u01/app/oracle/oradata/ora11g/undotbs01.dbf' to '/home/oracle/ora_data/undotbs01.dbf';
  • tempfile 위치변경
alter database rename file '/u01/app/oracle/oradata/ora11g/temp01.dbf' to '/home/oracle/ora_data/temp01.dbf';
  • redologfile 위치변경
alter database rename file '/u01/app/oracle/oradata/ora11g/redo01.log' to '/home/oracle/ora_data/redo01.log';

alter database rename file '/u01/app/oracle/oradata/ora11g/redo02.log' to '/home/oracle/ora_data/redo02.log';

alter database rename file '/u01/app/oracle/oradata/ora11g/redo03.log' to '/home/oracle/ora_data/redo03.log';

● 8. db open

SQL> alter database open;
se
Database altered.
  • 확인
select tablespace_name, file_name from dba_data_files;
select name from v$tempfile;
select name from v$controlfile;
select member from v$logfile;

● 9. spfile로 db 재 open

SQL> create spfile from pfile;

File created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  531476480 bytes
Fixed Size                  1365796 bytes
Variable Size             201328860 bytes
Database Buffers          322961408 bytes
Redo Buffers                5820416 bytes
Database mounted.
Database opened.

▶ 11. 10번시나리오 되돌리기 시나리오

● 1. db 정상종료

SQL> shutdown immediatew
SP2-0717: illegal SHUTDOWN option
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !

● 2. 장애유발 : 모든데이터파일 삭제

[oracle@oracle dbs]$ ls
hc_ora11g.dat  initora11g.ora  orapwora11g                spfileora11g.ora
init.ora       lkORA11G        spfileora11g_20231208.bak  spfileora11g.ora.bak
[oracle@oracle dbs]$ cd /home/oracle/ora_data
[oracle@oracle ora_data]$ ls
control01.ctl  redo01.log  redo03.log    system01.dbf  undotbs01.dbf
example01.dbf  redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf
[oracle@oracle ora_data]$ rm *.*
[oracle@oracle ora_data]$ exit
exit
  • db open안되는것 확인
SQL> startup
ORACLE instance started.

Total System Global Area  531476480 bytes
Fixed Size                  1365796 bytes
Variable Size             201328860 bytes
Database Buffers          322961408 bytes
Redo Buffers                5820416 bytes
ORA-00205: error in identifying control file, check alert log for more info


SQL> shutdown abort
ORACLE instance shut down.
SQL> !

● 3. 백업파일로 restore

[oracle@oracle 20240110]$ cd /home/oracle/backup/noarch/20240110
[oracle@oracle 20240110]$ cp -av *.* /u01/app/oracle/oradata/ora11g/
‘control01.ctl’ -> ‘/u01/app/oracle/oradata/ora11g/control01.ctl’
‘example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’
‘redo01.log’ -> ‘/u01/app/oracle/oradata/ora11g/redo01.log’
‘redo02.log’ -> ‘/u01/app/oracle/oradata/ora11g/redo02.log’
‘redo03.log’ -> ‘/u01/app/oracle/oradata/ora11g/redo03.log’
‘sysaux01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/sysaux01.dbf’
‘system01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/system01.dbf’
‘temp01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/temp01.dbf’
‘undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/undotbs01.dbf’
‘users01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/users01.dbf’
[oracle@oracle 20240110]$ cd /u01/app/oracle/oradata/ora11g/
[oracle@oracle ora11g]$ ls
control01.ctl  redo01.log  redo03.log    system01.dbf  undotbs01.dbf
example01.dbf  redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf

● 4. 초기파라미터 파일에서 control file 위치정보수정

[oracle@oracle ora11g]$  cd $ORACLE_HOME/dbs
[oracle@oracle dbs]$ ls
hc_ora11g.dat  initora11g.ora  orapwora11g                spfileora11g.ora
init.ora       lkORA11G        spfileora11g_20231208.bak  spfileora11g.ora.bak

[oracle@oracle dbs]$ vi initora11g.ora
--변경--
*.control_files='/u01/app/oracle/oradata/ora11g/control01.ctl'

● 5. pfile이용하여 open

backup본이 전에꺼 그대로 니깐
alter database rename file 안해도 된다

SQL> startup pfile='$ORACLE_HOME/dbs/initora11g.ora'
ORACLE instance started.

Total System Global Area  531476480 bytes
Fixed Size                  1365796 bytes
Variable Size             201328860 bytes
Database Buffers          322961408 bytes
Redo Buffers                5820416 bytes
Database mounted.
Database opened.

● 6. pfile을 이용하여 spfile 만들기

SQL> create spfile from pfile;

File created.

● 7. spfile로 db 재시작

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  531476480 bytes
Fixed Size                  1365796 bytes
Variable Size             201328860 bytes
Database Buffers          322961408 bytes
Redo Buffers                5820416 bytes
Database mounted.
Database opened.

▶ 12. datafile, controlfile만 백업 이후에 현재 redo에 정보가 없을 경우 시나리오 (특정한 데이터 파일이 손상)

noarchive log mode 입니다. Whole database backup (일관성있는 백업, cold backup)을
/home/oracle/backup/noarch/20240111 디렉토리에 백업해주세여. 단, datafile,control file 만 백업

  • 백업할곳 만들기
[oracle@oracle ~]$ mkdir -p /home/oracle/backup/noarch/20240111
[oracle@oracle 20240111]$ pwd
/home/oracle/backup/noarch/20240111
  • 대상, scn 확인
select name,checkpoint_change# from v$datafile;
select name from v$tempfile;
select name from v$controlfile;
select member from v$logfile;
  • db 정상 종료
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
  • 초기파라미터 파일 backup
create pfile='/home/oracle/backup/noarch/20240111/initora11g_20240110.ora' from spfile;
  • 디렉토리에 backup
[oracle@oracle ora11g]$ cp -av *.dbf /home/oracle/backup/noarch/20240111/
‘example01.dbf’ -> ‘/home/oracle/backup/noarch/20240111/example01.dbf’
‘sysaux01.dbf’ -> ‘/home/oracle/backup/noarch/20240111/sysaux01.dbf’
‘system01.dbf’ -> ‘/home/oracle/backup/noarch/20240111/system01.dbf’
‘temp01.dbf’ -> ‘/home/oracle/backup/noarch/20240111/temp01.dbf’
‘undotbs01.dbf’ -> ‘/home/oracle/backup/noarch/20240111/undotbs01.dbf’
‘users01.dbf’ -> ‘/home/oracle/backup/noarch/20240111/users01.dbf’
[oracle@oracle ora11g]$ cp -av control01.ctl /home/oracle/backup/noarch/20240111/
‘control01.ctl’ -> ‘/home/oracle/backup/noarch/20240111/control01.ctl’
[oracle@oracle ora11g]$ cd /home/oracle/backup/noarch/20240111/
[oracle@oracle 20240111]$ ls
control01.ctl  sysaux01.dbf  temp01.dbf     users01.dbf
example01.dbf  system01.dbf  undotbs01.dbf
[oracle@oracle 20240111]$ rm temp01.dbf
[oracle@oracle 20240111]$ ls
control01.ctl  sysaux01.dbf  undotbs01.dbf
example01.dbf  system01.dbf  users01.dbf
  • db open
SQL> startup
ORACLE instance started.

Total System Global Area  531476480 bytes
Fixed Size                  1365796 bytes
Variable Size             201328860 bytes
Database Buffers          322961408 bytes
Redo Buffers                5820416 bytes
Database mounted.
Database opened.

● 1. 작업

create table hr.emp_new
as
select * from hr.employees;


select 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';

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/users01.dbf

select * from v$log;
  • 로그스위치 발생
SQL> ALTER SYSTEM SWITCH logfile;

System altered.

SQL> ALTER SYSTEM SWITCH logfile;

System altered.

SQL> ALTER SYSTEM SWITCH logfile;

System altered.

● 2. 장애발생 : user테이블스페이스 데이터파일삭제

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !
[oracle@oracle 20240111]$ rm /u01/app/oracle/oradata/ora11g/users01.dbf

SQL> startup
ORACLE instance started.

Total System Global Area  531476480 bytes
Fixed Size                  1365796 bytes
Variable Size             201328860 bytes
Database Buffers          322961408 bytes
Redo Buffers                5820416 bytes
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'

● 3. 문제가 되는 datafile offline 상태로 변경

SQL> alter database datafile 4 offline drop;

Database altered.

SQL> select name, status from v$datafile;

NAME
--------------------------------------------------------------------------------
STATUS
-------
/u01/app/oracle/oradata/ora11g/system01.dbf
SYSTEM

/u01/app/oracle/oradata/ora11g/sysaux01.dbf
ONLINE

/u01/app/oracle/oradata/ora11g/users01.dbf
OFFLINE


NAME
--------------------------------------------------------------------------------
STATUS
-------
/u01/app/oracle/oradata/ora11g/example01.dbf
ONLINE

/u01/app/oracle/oradata/ora11g/undotbs01.dbf
ONLINE

● 4. backup파일 restore

SQL> !
[oracle@oracle 20240111]$ cp -av /home/oracle/backup/noarch/20240111/users01.dbf /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/noarch/20240111/users01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/users01.dbf’
[oracle@oracle 20240111]$ cd /u01/app/oracle/oradata/ora11g/
[oracle@oracle ora11g]$ ls
control01.ctl  redo01.log  redo03.log    system01.dbf  undotbs01.dbf
example01.dbf  redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf

● 5. redologfile에 정보 있는줄 알고 recover
근데 redo에 정보가 없다.

SQL> recover tablespace users;
ORA-00279: change 1513640 generated at 01/11/2024 10:06:03 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2024_01_11/o1_mf_1_49_%u_.a
rc
ORA-00280: change 1513640 for thread 1 is in sequence #49


Specify log: {<RET>=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_49_%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_49_%u_.
arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


SQL> select * from hr.emp_new;
select * from hr.emp_new
                 *
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/u01/app/oracle/oradata/ora11g/users01.dbf'
  • db 강제종료
SQL> shutdown abort
ORACLE instance shut down.

● 6. datafile,controlfile backup본 restore

[oracle@oracle 20240111]$ cp -av /home/oracle/backup/noarch/20240111/*.dbf /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/noarch/20240111/example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’
‘/home/oracle/backup/noarch/20240111/sysaux01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/sysaux01.dbf’
‘/home/oracle/backup/noarch/20240111/system01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/system01.dbf’
‘/home/oracle/backup/noarch/20240111/temp01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/temp01.dbf’
‘/home/oracle/backup/noarch/20240111/undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/undotbs01.dbf’
‘/home/oracle/backup/noarch/20240111/users01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/users01.dbf’
[oracle@oracle 20240111]$ cp -av /home/oracle/backup/noarch/20240111/*.ctl /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/noarch/20240111/control01.ctl’ -> ‘/u01/app/oracle/oradata/ora11g/control01.ctl’

● 7. mount 단계까지 db start

datafile, controlfile 까지만 backup을 했으므로 mount단계 까지만 start

SQL> startup mount
ORACLE instance started.

Total System Global Area  531476480 bytes
Fixed Size                  1365796 bytes
Variable Size             201328860 bytes
Database Buffers          322961408 bytes
Redo Buffers                5820416 bytes
Database mounted.

● 8. cancel base recover
: controlfile의 scn번호와 redologfile의 scn번호가 다르므로

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 1513640 generated at 01/11/2024 10:06:03 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2024_01_11/o1_mf_1_49_%u_.a
rc
ORA-00280: change 1513640 for thread 1 is in sequence #49

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel -- cancel 입력!
Media recovery cancelled.

● 9. redologfile 의 scn을 controlfile scn과 맞춰서 싱크를 맞게해서 open 가능하게 하기

  • redologfile db scn번호 reset

    resetlog를 쓴순가 시퀀스 번호가 다르므로 현재시점까지 backup으로 돌아갈수 없으므로

    db 정상종료후 모든데이터파일 백업해야한다.

SQL> alter database open resetlogs;

Database altered.
  • redologfile scn 확인
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         1          1          1   52428800        512          1 NO
CURRENT                1513641 11-JAN-24   2.8147E+14

         2          1          0   52428800        512          1 YES
UNUSED                       0                      0

         3          1          0   52428800        512          1 YES
UNUSED                       0                      0

리두로그그룹 사이즈가 너무작아서
로그스위치가 빈번히 일어나면서
dbwr도 빈번히 일어나는데 이때 로그스위치 wait event 발생한다.

▶ 13. temp file 손상 되었을 경우 복구 방식 시나리오(특정한 데이터 파일이 손상)

● 1. 장애발생 : tempfile 삭제

  • tempfile 확인
select * from v$tempfile;
select * from dba_temp_files;
  • tempfile 삭제
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

● 2. 정렬작업 수행

  • sort_area_size 변경
    : sort_area_size는 정렬 작업을 수행할 때 사용하는 메모리 영역의 크기를 나타냅니다
SQL> alter session set sort_area_size = 0;

Session altered.

:이렇게 설정하면 오라클은 정렬 작업을 위한 메모리를 할당하지 않고, 대신 디스크 기반 정렬을 수행하게 됩니다.

  • sort 작업 실행 -> 오류
    : tempfile이 손상되었을 경우 문제되는 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
  • alert log file이 있는 위치 정보
SQL> show parameter background_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string      /u01/app/oracle/diag/rdbms/ora
                                                 11g/ora11g/trace
  • default temp tablespace 확인
select * from database_properties;

해결방법 1

● 3. 새로운 tempfile 생성후, 기존 tempfile 삭제

  • tempfile 생성
alter tablespace temp add tempfile '/u01/app/oracle/oradata/ora11g/temp02.dbf' size 20m;
  • 확인
select name from v$tempfile;
  • 기존 tempfile 삭제
alter tablespace temp drop tempfile '/u01/app/oracle/oradata/ora11g/temp01.dbf';
  • 확인
SQL> select name from v$tempfile;

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

● 4. 다시 sort 작업

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

ORA-01652 오류는 임시 세그먼트 확장할 수 없을때 발생
해결방법 3가지

  • tempfile 사이즈 조정
alter database tempfile '/u01/app/oracle/oradata/ora11g/temp02.dbf' resize 100m;
  • 자동 확장 기능 활성화
alter database tempfile '/u01/app/oracle/oradata/ora11g/temp02.dbf' autoextend on;
  • tempfile 추가
alter tablespace temp add tempfile '/u01/app/oracle/oradata/ora11g/temp03.dbf' size 10m autoextend on;
  • tempfile 확인
select * from dba_temp_files;

해결방법 2.

● 3. 새로운 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;
select * from dba_temp_files;
select * from dba_users;

● 4. 기존 temp 테이블스페이스 삭제

  • 기존 temp tablespace 삭제
drop tablespace temp including contents and datafiles;
  • 확인
SQL> select name from v$tempfile;

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

해결방법 3.

temp file 손상된걸 모르는 상황에서 DB 종료했다가 다시시작하면 오라클이 알아서 Re-creating 해준다.

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

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  531476480 bytes
Fixed Size                  1365796 bytes
Variable Size             201328860 bytes
Database Buffers          322961408 bytes
Redo Buffers                5820416 bytes
Database mounted.
Database opened.
  • alertlog 창에 re-creating tempfile 했다고 뜬다.
SMON: enabling tx recovery
Re-creating tempfile /u01/app/oracle/oradata/ora11g/temp_new01.dbf
profile
YoonSeo Park
DB 공부를 하고 있는 사람입니다. 글을 읽어주셔서 감사하고 더 좋은 글을 쓰기 위해 노력하겠습니다 :)
다음 포스트

■ User Managed ( archive log mode ) Recovery 23개 시나리오

0개의 댓글