모든 파일이 손상되었을 경우, 다른 위치로 복구 해야 한다.

■  control file, data file, redo log file 모든 파일이 손상되었다. 다른 위치에서 복구 수행해야 한다.

(=: 데이터 이관 작업)

#) 모든 file 위치 
[oracle1@oracle ~]$ cd /u01/app/oracle/oradata/ora11g/
[oracle1@oracle ora11g]$ ls

#) 디렉터리 생성 및 확인

[oracle@oracle ~]$ mkdir ora_data/
[oracle1@oracle ~]$ cd ora_data/

[oracle1@oracle ora_data]$ pwd

 

#) 옮길 위치
/u01/app/oracle/oradata/ora11g/*.* →  /home/oracle1/ora_data

#) spfile 확인
=> VALUE null 값으로 보인다 하면 pfile로 open

SYS@ora11g> show parameter spfile

#) pfile 생성 
SYS@ora11g> create pfile from spfile;

 

<RMAN session>
#) rman 접속
[oracle1@oracle ~]$ rman target /

 

#) 스키마 확인
RMAN> report schema;

#) backup 정보 확인 후 삭제하기
RMAN> list backup;
RMAN> delete backup;

#) backup 받기
RMAN> backup as compressed backupset database;

#) 정책상 필요없는 정보 확인 후 삭제 
RMAN> report obsolete;
RMAN> delete obsolete;
RMAN> report obsolete;

#) 크로스 체크 확인
RMAN> crosscheck archivelog all;

#) backupset 확인
RMAN> crosscheck backupset;
RMAN>  list expired backupset;
RMAN>  delete expired backupset;

#) redo log 정보 확인

SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 MB, b.archived, b.status, b.first_time, b.first_change#, b.next_change#
FROM v$logfile a, v$log b
WHERE a.group# = b.group#
ORDER BY 1;

GROUP# SEQUENCE# MEMBER                                                   MB ARC STATUS   FIRST_TIM
------ --------- -------------------------------------------------- -------- --- -------- ---------
FIRST_CHANGE# NEXT_CHANGE#
------------- ------------
     7         1 /u01/app/oracle/oradata/ora11g/redo07.log           100.000 YES INACTIVE 29-JAN-24
      2424248      2444923

     8         2 /u01/app/oracle/oradata/ora11g/redo08.log           100.000 NO  CURRENT  29-JAN-24
      2444923   2.8147E+14

     9         0 /u01/app/oracle/oradata/ora11g/redo09.log           100.000 YES UNUSED
            0            0

#) 로그 스위치 발생 

alter system switch logfile;

#) redo log 정보 확인

SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 MB, b.archived, b.status, b.first_time, b.first_change#, b.next_change#
FROM v$logfile a, v$log b
WHERE a.group# = b.group#
ORDER BY 1;

GROUP# SEQUENCE# MEMBER                                                   MB ARC STATUS   FIRST_TIM
------ --------- -------------------------------------------------- -------- --- -------- ---------
FIRST_CHANGE# NEXT_CHANGE#
------------- ------------
     7         1 /u01/app/oracle/oradata/ora11g/redo07.log           100.000 YES INACTIVE 29-JAN-24
      2424248      2444923

     8         2 /u01/app/oracle/oradata/ora11g/redo08.log           100.000 YES ACTIVE   29-JAN-24
      2444923      2484955

     9         3 /u01/app/oracle/oradata/ora11g/redo09.log           100.000 NO  CURRENT  30-JAN-24
      2484955   2.8147E+14

#) 아카이브 정보 확인 

! ls /home/oracle1/arch1/

 

select sequence#, name, first_change#, next_change#, next_time
from v$archived_log
where name is not null;

#) DB 내리기

shutdown immediate

#) 장애 유도!

! rm /u01/app/oracle/oradata/ora11g/*.*
! ls /u01/app/oracle/oradata/ora11g/*.*

#) DB 올리기

startup

#) 현 상태 확인

select status from v$instance;

#) 아카이브 확인

! ls /home/oracle1/arch1/

#) DB 내리기

shutdown abort

#) OS 나와서 pfile로 이동 후 vi 편집기로 열기
SYS@ora11g> !
[oracle1@oracle ~]$ cd $ORACLE_HOME/dbs
[oracle1@oracle dbs]$ ls
hc_clone.dat   initclone.ora  initora11g.ora  lkORA11G     snapcf_ora11g.f
hc_ora11g.dat  init.ora       lkCLONE         orapwora11g  spfileora11g.ora
[oracle1@oracle dbs]$ vi initora11g.ora
#*.control_files='/u01/app/oracle/oradata/ora11g/control01.ctl'#Restore Controlfile => 기존 파일은 주석처리
*.control_files='/home/oracle1/ora_data/control01.ctl => 복원할 새로운 위치로 수정
:wq(저장)

#) oracle로 접속 후 pfile로 nomount까지 열기

=> control file 열기 위함

startup pfile=$ORACLE_HOME/dbs/initora11g.ora nomount

#) DB 내리기

=> RMAN 에서도 DB pfile로 open 가능하다!

shutdown abort

<RMAN session>
#) 재접속
RMAN> exit
[oracle1@oracle ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Jan 30 10:17:47 2024

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

#) DB pfile로 nomount까지 올리기
=> RMAN 으로도 올릴수 있다.
RMAN> startup pfile=$ORACLE_HOME/dbs/initora11g.ora nomount


#) restore 하기
=> RMAN 에서 받은 control file을 복구하기 
RMAN> restore controlfile from autobackup;
...
★ output file name=/home/oracle1/ora_data/control01.ctl ★ 
Finished restore at 30-JAN-24

#) mount로 올리기
RMAN> alter database mount;


#) 위치 변경 => data file + redo log file
=> 작업형으로 변경해준다

=> scn 번호를 알기 위해 일부러 오류 발생
RMAN> run {
sql "alter database rename file ''/u01/app/oracle/oradata/ora11g/redo07.log'' to ''/home/oracle1/ora_data/redo07.log''";
sql "alter database rename file ''/u01/app/oracle/oradata/ora11g/redo08.log'' to ''/home/oracle1/ora_data/redo08.log''";
sql "alter database rename file ''/u01/app/oracle/oradata/ora11g/redo09.log'' to  '/home/oracle1/ora_data/redo09.log''";
set newname for datafile 1 to '/home/oracle1/ora_data/system01.dbf';
set newname for datafile 2 to '/home/oracle1/ora_data/sysaux01.dbf';
set newname for datafile 4 to '/home/oracle1/ora_data/users01.dbf';
set newname for datafile 5 to '/home/oracle1/ora_data/example01.dbf';
set newname for datafile 9 to '/home/oracle1/ora_data/undo01.dbf';
set newname for tempfile 1 to '/home/oracle1/ora_data/temp01.dbf';
restore database;
switch datafile all;
switch tempfile all;
recover database;
alter database open resetlogs;
}

sql statement: alter database rename file ''/u01/app/oracle/oradata/ora11g/redo07.log'' to ''/home/oracle1/ora_data/redo07.log''

sql statement: alter database rename file ''/u01/app/oracle/oradata/ora11g/redo08.log'' to ''/home/oracle1/ora_data/redo08.log''

sql statement: alter database rename file ''/u01/app/oracle/oradata/ora11g/redo09.log'' to  '/home/oracle1/ora_data/redo09.log''

Starting restore at 30-JAN-24
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ora11g/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/ora11g/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ora11g/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/ora11g/example01.dbf
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/ora11g/undo01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T095915_lvjlh49c_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T095915_lvjlh49c_.bkp tag=TAG20240130T095915
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 30-JAN-24

Starting recover at 30-JAN-24
using channel ORA_DISK_1

starting media recovery

archived log file name=/home/oracle1/arch1/arch_1_2_1159552600.arc thread=1 sequence=2
unable to find archived log
archived log thread=1 sequence=3
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/30/2024 10:37:12
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 3 and starting SCN of 2484955

database opened

#) 작업형 + scn번호 추가
RMAN> run {
sql "alter database rename file ''/u01/app/oracle/oradata/ora11g/redo07.log'' to ''/home/oracle1/ora_data/redo07.log''";
sql "alter database rename file ''/u01/app/oracle/oradata/ora11g/redo08.log'' to ''/home/oracle1/ora_data/redo08.log''";
sql "alter database rename file ''/u01/app/oracle/oradata/ora11g/redo09.log'' to  '/home/oracle1/ora_data/redo09.log''";
set newname for datafile 1 to '/home/oracle1/ora_data/system01.dbf';
set newname for datafile 2 to '/home/oracle1/ora_data/sysaux01.dbf';
set newname for datafile 4 to '/home/oracle1/ora_data/users01.dbf';
set newname for datafile 5 to '/home/oracle1/ora_data/example01.dbf';
set newname for datafile 9 to '/home/oracle1/ora_data/undo01.dbf';
set newname for tempfile 1 to '/home/oracle1/ora_data/temp01.dbf';
restore database;
switch datafile all;
switch tempfile all;
set until scn=2484955; => 추가 소스 코드
recover database;
alter database open resetlogs;
}

#) DB를 resetlogs로 열기

RMAN> alter database open resetlogs;

#) 스키마 확인
RMAN> report schema;

============================================================================================

원상복구하기

#) 위치 변경 
/home/oracle1/ora_data/ -> /u01/app/oracle/ora11g/*.*

#) temp file, redo log file, data file, control file 위치 확인

select name from v$tempfile;
select name from v$datafile;
select name from v$controlfile;
select member from v$logfile;

NAME
--------------------------------------------------------------------------------
/home/oracle1/ora_data/temp01.dbf

MEMBER
--------------------------------------------------------------------------------
/home/oracle1/ora_data/redo07.log
/home/oracle1/ora_data/redo09.log
/home/oracle1/ora_data/redo08.log

NAME
--------------------------------------------------------------------------------
/home/oracle1/ora_data/system01.dbf
/home/oracle1/ora_data/sysaux01.dbf
/home/oracle1/ora_data/users01.dbf
/home/oracle1/ora_data/example01.dbf
/home/oracle1/ora_data/undo01.dbf

NAME
--------------------------------------------------------------------------------
/home/oracle1/ora_data/control01.ctl

#) DB 정상적인 종료 

shutdown immediate

#) OS 나와서 위치 확인 후 옮기기
[oracle1@oralce ~]$ mv *.* /u01/app/oracle/oradata/ora11g/

#) pfile 에 있는 control file 경로 변경 
[oracle1@oralce dbs]$ vi $ORACLE_HOEM/dbs/initora11g.ora
=> 추가했던 control_files 삭제

#*.control_files='/home/oracle1/ora_data/control01.ctl 
=> 주석처리 했던 control_files 풀어주기

*.control_files='/u01/app/oracle/oradata/ora11g/control01.ctl'#Restore Controlfile
:wq(저장)

#) oracle 재접속 후 DB mount단계로 올리기

conn / as sysdba
startup pfile=$ORACLE_HOME/dbs/initora11g.ora mount

#) control file 정보 확인(경로 변경되었는지)

select name from v$controlfile;

#) datafile 확인(경로 변경안됨)

select member from v$tempfile;
select name from v$datafile;
select name from v$logfile;

#) redo log file 이관 작업
=> redo log file 경로 변경 후 확인

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

#) redo log file 옮겨졌는지 확인

select member from v$logfile;

#) 나머지 redo log file 이관작업

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

 

#) redo log file 옮겨졌는지 확인

select member from v$logfile;

#) 데이터 파일 이관 작업

=> data file 경로 변경 후 확인

select name from v$datafile;

#) 데이터 파일 이관 작업

alter datafile rename file '/home/oracle1/ora_data/system01.dbf' to '/u01/app/oracle/oradata/ora11g/system01.dbf';

 

 

#)  나머지 데이터 파일 이관 작업

=> data file 경로 변경 후 확인

select name from v$datafile;

alter datafile rename file '/home/oracle1/ora_data/sysaux01.dbf' to '/u01/app/oracle/oradata/ora11g/sysaux01.dbf';
alter datafile rename file '/home/oracle1/ora_data/users01.dbf' to '/u01/app/oracle/oradata/ora11g/users01.dbf';
alter datafile rename file '/home/oracle1/ora_data/example01.dbf' to '/u01/app/oracle/oradata/ora11g/example01.dbf';
alter datafile rename file '/home/oracle1/ora_data/undo01.dbf' to '/u01/app/oracle/oradata/ora11g/undo01.dbf';

#)  data file 확인 

select name from v$datafile;

#) temp file 확인

select name from v$tempfile;

alter datafile rename file '/home/oracle1/ora_data/temp01.dbf' to '/u01/app/oracle/oradata/ora11g/temp01.dbf';

select name from v$tempfile;

#) DB 올리기

alter database open;

#) pfile로 spfile 생성 

create spfile from pfile;
show parameter spfile

#) DB 내렸다가 DB 다시 올리기

shutdown immediate
startup

#) backup 받기 
=> 기존 백업 삭제 + 정책상 필요없는 파일 확인후 삭제