ArchiveMode 에서의 Backup & Recovery-
control file
controlfile
- control file만 유실한 경우 백업 컨트롤 파일로 복구하는 경우 - resetlogs 로 오픈 ( 데이타는 완전 복구)
- control file만 유실한 경우 컨트롤 파일 재생성 해서 복구하는 경우 - resetlogs or noresetlogs
- control file + Data file 유실 - resetlogs 로 오픈 (데이타는 완전 복구)
- control file + redo log file 유실 - resetlogs 로 오픈 (데이타 유실 있다)
- Backup control file과 현재 data file 정보가 다를 때 복구 - resetlogs 로 오픈 ( 데이타는 완전 복구)
추가정보
@con
SYS@orcl2> select name from v$controlfile;
@log
select group#, sequence#, status, archived, members, first_change#, bytes/1024/1024 mb
from v$log;
@logfile
col status for a10
col member for a35
SELECT a.group# ,b.sequence#,a.member , b.bytes/1024/1024 MB , b.archived , b.status, b.first_change#
FROM v$logfile a , v$log b
WHERE a.group#=b.group#
ORDER BY 1;
@data
col name for a35
select name, status from v$datafile;
@datafile
col name1 for a15
col name2 for a35
select a.file#,b.name name1,a.name name2,a.status,a.checkpoint_change# from v$datafile a, v$tablespace b where a.ts#=b.ts#;
@switch
alter system switch logfile;
@check
alter system checkpoint;
@logmnr
col filename for a35
select db_name, filename from v$logmnr_logs;
@db_list
set echo off
set pages 0
set head off
set feed off
create pfile from spfile;
spool orcl2_backup.sh
select 'cp -av ' || name||' /home/oracle/backup/arch/cold'
from v$controlfile
union all
select 'cp -av ' || name||' /home/oracle/backup/arch/cold'
from v$datafile
union all
select 'cp -av ' || member ||' /home/oracle/backup/arch/cold'
from v$logfile;
select 'cp -av $ORACLE_HOME/dbs/initorcl2.ora /home/oracle/backup/arch/cold' from dual;
spool off
set feedback on
set head on
set pages 100
set echo on
control file만 유실한 경우 백업 컨트롤 파일로 복구하는 경우 - resetlogs 로 오픈 ( 데이타는 완전 복구)
/* 모든 control file의 손상시 불완전복구 */
/* data file, redo log file은 손상되지 않고 control file만 손상되었다. control file은 백업되어 있을때 복구 방식 */
/* 실습 편이를 위하여 control file을 하나만 사용합니다. */
SYS@orcl2> @con
NAME
--------------------
/u01/app/oracle/orcl
2/control01.ctl
SYS@orcl2> @log
GROUP# SEQUENCE# STATUS ARC MEMBERS MB
---------- ---------- ---------------- --- ---------- ----------
1 4 INACTIVE YES 1 50
2 5 INACTIVE YES 1 50
3 6 CURRENT NO 1 50
SYS@orcl2> @logfile
GROUP# SEQUENCE# MEMBER MB ARC STATUS
---------- ---------- ----------------------------------- ---------- --- ----------------
1 4 /u01/app/oracle/orcl2/redo01.log 50 YES INACTIVE
2 5 /u01/app/oracle/orcl2/redo02.log 50 YES INACTIVE
3 6 /u01/app/oracle/orcl2/redo03.log 50 NO CURRENT
SYS@orcl2> @datafile
FILE# NAME1 NAME2 STATUS CHECKPOINT_CHANGE#
---------- --------------- ----------------------------------- ------- ------------------
1 SYSTEM /u01/app/oracle/orcl2/system01.dbf SYSTEM 1111145
2 SYSAUX /u01/app/oracle/orcl2/sysaux01.dbf ONLINE 1111145
3 UNDOTBS1 /u01/app/oracle/orcl2/undotbs01.dbf ONLINE 1111145
4 USERS /u01/app/oracle/orcl2/users01.dbf ONLINE 1111145
5 EXAMPLE /u01/app/oracle/orcl2/example01.dbf ONLINE 1111145
1. DB 전체 백업
=========================================================================
다음을 반드시 수행 : 전체 DB cold backup 수행
ARCHIVE LOG : 전체 DB 백업
SYS@orcl2> @db_list
SYS@orcl2> shutdown immediate
[orcl2:~]$ chmod 755 orcl2_backup.sh
[orcl2:~]$ . orcl2_backup.sh -- 전체 DB 백업
[orcl2:~]$ rm /home/oracle/arch1/* /home/oracle/arch2/* -- 불필요한 archive log 삭제
SYS@orcl2> startup
=========================================================================
2. DB 작업 -- emp_20 생성
SYS@orcl2> @log
GROUP# SEQUENCE# STATUS ARC MEMBERS MB
---------- ---------- ---------------- --- ---------- ----------
1 4 INACTIVE YES 1 50
2 5 INACTIVE YES 1 50
3 6 CURRENT NO 1 50 <== CURRENT 확인
SYS@orcl2> create table hr.emp_20 as select * from hr.employees where department_id = 20;
Table created.
SYS@orcl2> select count(*) from hr.emp_20;
COUNT(*)
----------
2
SYS@orcl2> @log
GROUP# SEQUENCE# STATUS ARC MEMBERS MB
---------- ---------- ---------------- --- ---------- ----------
1 4 INACTIVE YES 1 50
2 5 INACTIVE YES 1 50
3 6 CURRENT NO 1 50
SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
3. control file 유실
[orcl2:~]$ rm -f /u01/app/oracle/orcl2/control01.ctl
[orcl2:~]$ ls /u01/app/oracle/orcl2/control01.ctl
ls: /u01/app/oracle/orcl2/control01.ctl: No such file or directory
SYS@orcl2> startup
ORACLE instance started.
Total System Global Area 431038464 bytes
Fixed Size 1337016 bytes
Variable Size 146803016 bytes
Database Buffers 276824064 bytes
Redo Buffers 6074368 bytes
ORA-00205: error in identifying control file, check alert log for more info
SYS@orcl2> select status from v$instance;
STATUS
------------
STARTED <== NOMOUNT 상태
===============================================================
alert.log
ALTER DATABASE MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/orcl2/control01.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
===============================================================
SYS@orcl2> ! ls /u01/app/oracle/orcl2/control01.ctl
ls: /u01/app/oracle/orcl2/control01.ctl: No such file or directory
4. control file restore
SYS@orcl2> shutdown abort
ORACLE instance shut down.
SYS@orcl2> ! cp -av /home/oracle/backup/arch/cold/*.ctl /u01/app/oracle/orcl2/
`/home/oracle/backup/arch/cold/control01.ctl' -> `/u01/app/oracle/orcl2/control01.ctl'
5. mount 모드에서 복구
SYS@orcl2> startup
ORACLE instance started.
Total System Global Area 431038464 bytes
Fixed Size 1337016 bytes
Variable Size 146803016 bytes
Database Buffers 276824064 bytes
Redo Buffers 6074368 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/app/oracle/orcl2/system01.dbf'
ORA-01207: file is more recent than control file - old control file
SYS@orcl2> recover database using backup controlfile;
ORA-00279: change 1113831 generated at 04/17/2019 13:47:19 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_6_1005823121.arc
ORA-00280: change 1113831 for thread 1 is in sequence #6
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/home/oracle/arch2/arch_1_6_1005823121.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 '/home/oracle/arch2/arch_1_6_1005823121.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
-- 6번이 current 번호, 그래서 아직 archive 없다.
SYS@orcl2> @logfile
GROUP# SEQUENCE# MEMBER MB ARC STATUS
---------- ---------- ----------------------------------- ---------- --- ----------------
1 4 /u01/app/oracle/orcl2/redo01.log 50 YES INACTIVE
2 5 /u01/app/oracle/orcl2/redo02.log 50 YES INACTIVE
3 6 /u01/app/oracle/orcl2/redo03.log 50 NO CURRENT
-- 6번이 들어있는 redo log file 명을 직접 써준다.
SYS@orcl2> recover database using backup controlfile
ORA-00279: change 1113831 generated at 04/17/2019 13:47:19 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_6_1005823121.arc
ORA-00280: change 1113831 for thread 1 is in sequence #6
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/orcl2/redo03.log
Log applied.
Media recovery complete.
SYS@orcl2> alter database open resetlogs;
Database altered.
SYS@orcl2> select count(*) from hr.emp_20;
COUNT(*)
----------
2
=========================================================================
다음을 반드시 수행 : 전체 DB cold backup 수행
ARCHIVE LOG : 전체 DB 백업
SYS@orcl2> @db_list
SYS@orcl2> shutdown immediate
[orcl2:~]$ chmod 755 orcl2_backup.sh
[orcl2:~]$ . orcl2_backup.sh -- 전체 DB 백업
[orcl2:~]$ rm /home/oracle/arch1/* /home/oracle/arch2/* -- 불필요한 archive log 삭제
SYS@orcl2> startup
=========================================================================
-- control file만 유실한 경우 컨트롤 파일 재생성 해서 복구하는 경우 - resetlogs or noresetlogs
/* 모든 control file의 손상시 재생성으로 복구 */
/* data file, redo log file은 손상되지 않고 control file만 손상되었다. */
1. 현재 상황 확인과 DB 작업 - hr.emp_30 생성
SYS@orcl2> @log
GROUP# SEQUENCE# STATUS ARC MEMBERS MB
---------- ---------- ---------------- --- ---------- ----------
1 4 INACTIVE YES 1 50
2 5 INACTIVE YES 1 50
3 6 CURRENT NO 1 50
SYS@orcl2> create table hr.emp_30 as select * from hr.employees where department_id = 30;
Table created.
SYS@orcl2> select count(*) from hr.emp_30;
COUNT(*)
----------
6
SYS@orcl2> @switch
System altered.
SYS@orcl2> @log
GROUP# SEQUENCE# STATUS ARC MEMBERS MB
---------- ---------- ---------------- --- ---------- ----------
1 7 CURRENT NO 1 50
2 5 INACTIVE YES 1 50
3 6 ACTIVE YES 1 50
2. 장애 발생 : 모든 컨트롤 파일 유실
SYS@orcl2> ! rm /u01/app/oracle/orcl2/control01.ctl
SYS@orcl2> ! ls /u01/app/oracle/orcl2/control01.ctl
ls: /u01/app/oracle/orcl2/control01.ctl: No such file or directory
SYS@orcl2> @switch
System altered.
SYS@orcl2> @switch
System altered.
SYS@orcl2> @switch
System altered.
SYS@orcl2> @switch
System altered.
SYS@orcl2> @log
GROUP# SEQUENCE# STATUS ARC MEMBERS MB
---------- ---------- ---------------- --- ---------- ----------
1 10 INACTIVE YES 1 50
2 11 CURRENT NO 1 50
3 9 INACTIVE YES 1 50
SYS@orcl2> @datafile
FILE# NAME1 NAME2 STATUS CHECKPOINT_CHANGE#
---------- --------------- ----------------------------------- ------- ------------------
1 SYSTEM /u01/app/oracle/orcl2/system01.dbf SYSTEM 1115403
2 SYSAUX /u01/app/oracle/orcl2/sysaux01.dbf ONLINE 1115403
3 UNDOTBS1 /u01/app/oracle/orcl2/undotbs01.dbf ONLINE 1115403
4 USERS /u01/app/oracle/orcl2/users01.dbf ONLINE 1115403
5 EXAMPLE /u01/app/oracle/orcl2/example01.dbf ONLINE 1115403
=========================
다른 session을 열어서 작업
=========================
SYS@orcl2> select name from v$database;
select name from v$database
*
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/orcl2/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SYS@orcl2> shutdown abort
ORACLE instance shut down.
3. 컨트롤 파일을 restore
[orcl2:~]$ cp -av /home/oracle/backup/arch/cold/control01.ctl /u01/app/oracle/orcl2/control01.ctl
`/home/oracle/backup/arch/cold/control01.ctl' -> `/u01/app/oracle/orcl2/control01.ctl'
4. 복구
SYS@orcl2> startup mount
ORACLE instance started.
Total System Global Area 431038464 bytes
Fixed Size 1337016 bytes
Variable Size 146803016 bytes
Database Buffers 276824064 bytes
Redo Buffers 6074368 bytes
Database mounted.
SYS@orcl2> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/app/oracle/orcl2/system01.dbf'
ORA-01207: file is more recent than control file - old control file
-- 안된다.
5. 복구 : 이번에는 트레이스 파일을 이용한 복구를 해보자
SYS@orcl2> alter database backup controlfile to trace as '/home/oracle/mkcon.sql';
Database altered.
SYS@orcl2> shutdown abort
ORACLE instance shut down.
[orcl2:~]$ vi mkcon.sql
CREATE CONTROLFILE REUSE DATABASE "ORCL2" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/orcl2/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/orcl2/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/orcl2/redo03.log' SIZE 50M BLOCKSIZE 512
DATAFILE
'/u01/app/oracle/orcl2/system01.dbf',
'/u01/app/oracle/orcl2/sysaux01.dbf',
'/u01/app/oracle/orcl2/undotbs01.dbf',
'/u01/app/oracle/orcl2/users01.dbf',
'/u01/app/oracle/orcl2/example01.dbf'
CHARACTER SET AL32UTF8
;
만 남기고 전부 삭제
SYS@orcl2> startup nomount
ORACLE instance started.
Total System Global Area 431038464 bytes
Fixed Size 1337016 bytes
Variable Size 146803016 bytes
Database Buffers 276824064 bytes
Redo Buffers 6074368 bytes
SYS@orcl2> @mkcon
Control file created.
-- checkpoint 정보를 control file에 반영해서 current 의 내용으로 만들어준다.
SYS@orcl2> alter database open;
Database altered.
SYS@orcl2> @log
GROUP# SEQUENCE# STATUS ARC MEMBERS MB
---------- ---------- ---------------- --- ---------- ----------
1 10 INACTIVE YES 1 50
2 11 INACTIVE YES 1 50
3 12 CURRENT NO 1 50
6. 복구된 것 확인
SYS@orcl2> select count(*) from hr.emp_30;
COUNT(*)
----------
6
=========================================================================
다음을 반드시 수행 : 전체 DB cold backup 수행
ARCHIVE LOG : 전체 DB 백업
SYS@orcl2> @db_list
SYS@orcl2> shutdown immediate
[orcl2:~]$ chmod 755 orcl2_backup.sh
[orcl2:~]$ . orcl2_backup.sh -- 전체 DB 백업
[orcl2:~]$ rm /home/oracle/arch1/* /home/oracle/arch2/* -- 불필요한 archive log 삭제
SYS@orcl2> startup
=========================================================================
control file + Data file 유실 - resetlogs 로 오픈 ( 데이타는 완전 복구)
/* 모든 control file의 손상시 control file backup으로 복구 */
/* redo log file은 손상되지 않고 control file과 datafile이 손상되었다. */
1. 현재 상황 확인 & DB 작업 - hr.emp_50
SYS@orcl2> @log
GROUP# SEQUENCE# STATUS ARC MEMBERS MB
---------- ---------- ---------------- --- ---------- ----------
1 10 INACTIVE YES 1 50
2 11 INACTIVE YES 1 50
3 12 CURRENT NO 1 50
SYS@orcl2> @datafile
FILE# NAME1 NAME2 STATUS CHECKPOINT_CHANGE#
---------- --------------- ----------------------------------- ------- ------------------
1 SYSTEM /u01/app/oracle/orcl2/system01.dbf SYSTEM 1136314
2 SYSAUX /u01/app/oracle/orcl2/sysaux01.dbf ONLINE 1136314
3 UNDOTBS1 /u01/app/oracle/orcl2/undotbs01.dbf ONLINE 1136314
4 USERS /u01/app/oracle/orcl2/users01.dbf ONLINE 1136314
5 EXAMPLE /u01/app/oracle/orcl2/example01.dbf ONLINE 1136314
SYS@orcl2> create table hr.emp_50 as select * from hr.employees where department_id = 50;
Table created.
SYS@orcl2> select count(*) from hr.emp_50;
COUNT(*)
----------
45
SYS@orcl2> @log
GROUP# SEQUENCE# STATUS ARC MEMBERS FIRST_CHANGE# MB
---------- ---------- ---------------- --- ---------- ------------- ----------
1 10 INACTIVE YES 1 1115399 50
2 11 INACTIVE YES 1 1115403 50
3 12 CURRENT NO 1 1135636 50 <= 여기 emp_50
SYS@orcl2> @logfile
GROUP# SEQUENCE# MEMBER MB ARC STATUS
---------- ---------- ----------------------------------- ---------- --- ----------------
1 10 /u01/app/oracle/orcl2/redo01.log 50 YES INACTIVE
2 11 /u01/app/oracle/orcl2/redo02.log 50 YES INACTIVE
3 12 /u01/app/oracle/orcl2/redo03.log 50 NO CURRENT
SYS@orcl2> @switch
System altered.
SYS@orcl2> @switch
System altered.
SYS@orcl2> @switch
System altered.
SYS@orcl2> @logfile
GROUP# SEQUENCE# MEMBER MB ARC STATUS
---------- ---------- ----------------------------------- ---------- --- ----------------
1 13 /u01/app/oracle/orcl2/redo01.log 50 YES INACTIVE
2 14 /u01/app/oracle/orcl2/redo02.log 50 YES INACTIVE
3 15 /u01/app/oracle/orcl2/redo03.log 50 NO CURRENT <== 이 파일 필요
2. 장애 발생 : control file + 모든 Data file 유실
SYS@orcl2> ! rm -f /u01/app/oracle/orcl2/*.dbf
SYS@orcl2> ! rm -f /u01/app/oracle/orcl2/*.ctl
SYS@orcl2> @check
System altered.
SYS@orcl2> /
System altered.
SYS@orcl2> /
System altered.
SYS@orcl2> select status from v$instance;
STATUS
------------
OPEN
SYS@orcl2> conn hr/hr
ERROR:
ORA-00604: error occurred at recursive SQL level 2
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/orcl2/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Warning: You are no longer connected to ORACLE.
@> conn / as sysdba
ERROR:
ORA-01075: you are currently logged on
@> shutdown abort
ORACLE instance shut down.
-- DB shutdown abort 하고 datafile controlfile 을 restore
-- 만약 shutdown abort 못하는 상황이면 pmon을 kill 해서 해결
@> ! cp -av /home/oracle/backup/arch/cold/control01.ctl /u01/app/oracle/orcl2/control01.ctl
`/home/oracle/backup/arch/cold/control01.ctl' -> `/u01/app/oracle/orcl2/control01.ctl'
@> ! cp -av /home/oracle/backup/arch/cold/*.dbf /u01/app/oracle/orcl2/
`/home/oracle/backup/arch/cold/data01.dbf' -> `/u01/app/oracle/orcl2/data01.dbf'
`/home/oracle/backup/arch/cold/data02.dbf' -> `/u01/app/oracle/orcl2/data02.dbf'
`/home/oracle/backup/arch/cold/example01.dbf' -> `/u01/app/oracle/orcl2/example01.dbf'
`/home/oracle/backup/arch/cold/insa_tbs01.dbf' -> `/u01/app/oracle/orcl2/insa_tbs01.dbf'
`/home/oracle/backup/arch/cold/sysaux01.dbf' -> `/u01/app/oracle/orcl2/sysaux01.dbf'
`/home/oracle/backup/arch/cold/system01.dbf' -> `/u01/app/oracle/orcl2/system01.dbf'
`/home/oracle/backup/arch/cold/undotbs01.dbf' -> `/u01/app/oracle/orcl2/undotbs01.dbf'
`/home/oracle/backup/arch/cold/users01.dbf' -> `/u01/app/oracle/orcl2/users01.dbf'
3. 복구 : recover database using backup controlfile
SYS@orcl2> startup mount
ORACLE instance started.
Total System Global Area 431038464 bytes
Fixed Size 1337016 bytes
Variable Size 146803016 bytes
Database Buffers 276824064 bytes
Redo Buffers 6074368 bytes
Database mounted.
SYS@orcl2> recover database using backup controlfile
ORA-00279: change 1136311 generated at 04/17/2019 15:37:47 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_12_1005837298.arc
ORA-00280: change 1136311 for thread 1 is in sequence #12
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 1137667 generated at 04/17/2019 16:01:47 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_13_1005837298.arc
ORA-00280: change 1137667 for thread 1 is in sequence #13
ORA-00278: log file '/home/oracle/arch2/arch_1_12_1005837298.arc' no longer needed for this
recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 1137670 generated at 04/17/2019 16:01:50 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_14_1005837298.arc
ORA-00280: change 1137670 for thread 1 is in sequence #14
ORA-00278: log file '/home/oracle/arch2/arch_1_13_1005837298.arc' no longer needed for this
recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 1137675 generated at 04/17/2019 16:01:55 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_15_1005837298.arc
ORA-00280: change 1137675 for thread 1 is in sequence #15
ORA-00278: log file '/home/oracle/arch2/arch_1_14_1005837298.arc' no longer needed for this
recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log '/home/oracle/arch2/arch_1_15_1005837298.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SYS@orcl2> recover database using backup controlfile
ORA-00279: change 1137675 generated at 04/17/2019 16:01:55 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_15_1005837298.arc
ORA-00280: change 1137675 for thread 1 is in sequence #15
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/orcl2/redo03.log
Log applied.
Media recovery complete.
4. 복구 확인
SYS@orcl2> alter database open read only;
Database altered.
SYS@orcl2> select count(*) from hr.emp_50;
COUNT(*)
----------
45
SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl2> startup mount
ORACLE instance started.
Total System Global Area 431038464 bytes
Fixed Size 1337016 bytes
Variable Size 146803016 bytes
Database Buffers 276824064 bytes
Redo Buffers 6074368 bytes
Database mounted.
SYS@orcl2> alter database open resetlogs;
Database altered.
SYS@orcl2> @log
GROUP# SEQUENCE# STATUS ARC MEMBERS FIRST_CHANGE# MB
---------- ---------- ---------------- --- ---------- ------------- ----------
1 1 CURRENT NO 1 1137755 50
2 0 UNUSED YES 1 0 50
3 0 UNUSED YES 1 0 50
=========================================================================
다음을 반드시 수행 : 전체 DB cold backup 수행
ARCHIVE LOG : 전체 DB 백업
SYS@orcl2> @db_list
SYS@orcl2> shutdown immediate
[orcl2:~]$ chmod 755 orcl2_backup.sh
[orcl2:~]$ . orcl2_backup.sh -- 전체 DB 백업
[orcl2:~]$ rm /home/oracle/arch1/* /home/oracle/arch2/* -- 불필요한 archive log 삭제
SYS@orcl2> startup
=========================================================================
control file + redo log file 유실 - resetlogs 로 오픈 ( 데이타 유실 있다)
복구 방법 : recover database until cancel using backup controlfile;
/* 모든 control file의 손상시 control file backup으로 복구 */
/* data file은 손상되지 않고 control file과 redo log file이 손상되었다. */
1. 현재 상황 확인
SYS@orcl2> @log
GROUP# SEQUENCE# STATUS ARC MEMBERS FIRST_CHANGE# MB
---------- ---------- ---------------- --- ---------- ------------- ----------
1 4 INACTIVE YES 1 1138718 50
2 5 CURRENT NO 1 1138721 50
3 3 INACTIVE YES 1 1138714 50
SYS@orcl2> @datafile
FILE# NAME1 NAME2 STATUS CHECKPOINT_CHANGE#
---------- --------------- ----------------------------------- ------- ------------------
1 SYSTEM /u01/app/oracle/orcl2/system01.dbf SYSTEM 1138727
2 SYSAUX /u01/app/oracle/orcl2/sysaux01.dbf ONLINE 1138727
3 UNDOTBS1 /u01/app/oracle/orcl2/undotbs01.dbf ONLINE 1138727
4 USERS /u01/app/oracle/orcl2/users01.dbf ONLINE 1138727
5 EXAMPLE /u01/app/oracle/orcl2/example01.dbf ONLINE 1138727
2. DB 작업 - hr.dept_new 생성
SYS@orcl2> create table hr.dept_new as select * from hr.departments;
Table created.
SYS@orcl2> select count(*) from hr.dept_new;
COUNT(*)
----------
27
SYS@orcl2> @logfile
GROUP# SEQUENCE# MEMBER MB ARC STATUS
---------- ---------- ----------------------------------- ---------- --- ----------------
1 4 /u01/app/oracle/orcl2/redo01.log 50 YES INACTIVE
2 5 /u01/app/oracle/orcl2/redo02.log 50 NO CURRENT
3 3 /u01/app/oracle/orcl2/redo03.log 50 YES INACTIVE
SYS@orcl2> @switch
System altered.
SYS@orcl2> /
System altered.
SYS@orcl2> /
System altered.
SYS@orcl2> insert into hr.dept_new select * from hr.dept_new;
27 rows created.
SYS@orcl2> commit;
Commit complete.
SYS@orcl2> select count(*) from hr.dept_new;
COUNT(*)
----------
54
SYS@orcl2> @logfile
GROUP# SEQUENCE# MEMBER MB ARC STATUS
---------- ---------- ----------------------------------- ---------- --- ----------------
1 7 /u01/app/oracle/orcl2/redo01.log 50 YES INACTIVE
2 8 /u01/app/oracle/orcl2/redo02.log 50 NO CURRENT
3 6 /u01/app/oracle/orcl2/redo03.log 50 YES INACTIVE
3. 장애 발생 - 컨트롤 파일과 리두로그 파일 유실
SYS@orcl2> ! rm /u01/app/oracle/orcl2/control01.ctl
SYS@orcl2> ! rm /u01/app/oracle/orcl2/*.log
SYS@orcl2> @switch
System altered.
SYS@orcl2> conn / as sysdba
Connected.
SYS@orcl2> select name from v$database;
select name from v$database
*
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/orcl2/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SYS@orcl2> shutdown abort
ORACLE instance shut down.
4. 컨트롤 파일과 모든 데이타파일 복원(restore) ( Not redo log !!)
SYS@orcl2> ! cp -av /home/oracle/backup/arch/cold/control01.ctl /u01/app/oracle/orcl2/control01.ctl
`/home/oracle/backup/arch/cold/control01.ctl' -> `/u01/app/oracle/orcl2/control01.ctl'
SYS@orcl2> ! cp -av /home/oracle/backup/arch/cold/*.dbf /u01/app/oracle/orcl2/
`/home/oracle/backup/arch/cold/data01.dbf' -> `/u01/app/oracle/orcl2/data01.dbf'
`/home/oracle/backup/arch/cold/data02.dbf' -> `/u01/app/oracle/orcl2/data02.dbf'
`/home/oracle/backup/arch/cold/example01.dbf' -> `/u01/app/oracle/orcl2/example01.dbf'
`/home/oracle/backup/arch/cold/insa_tbs01.dbf' -> `/u01/app/oracle/orcl2/insa_tbs01.dbf'
`/home/oracle/backup/arch/cold/sysaux01.dbf' -> `/u01/app/oracle/orcl2/sysaux01.dbf'
`/home/oracle/backup/arch/cold/system01.dbf' -> `/u01/app/oracle/orcl2/system01.dbf'
`/home/oracle/backup/arch/cold/undotbs01.dbf' -> `/u01/app/oracle/orcl2/undotbs01.dbf'
`/home/oracle/backup/arch/cold/users01.dbf' -> `/u01/app/oracle/orcl2/users01.dbf'
5. 복구 : recover database until cancel using backup controlfile;
SYS@orcl2> startup mount
ORACLE instance started.
Total System Global Area 431038464 bytes
Fixed Size 1337016 bytes
Variable Size 146803016 bytes
Database Buffers 276824064 bytes
Redo Buffers 6074368 bytes
Database mounted.
SYS@orcl2> recover database until cancel using backup controlfile;
ORA-00279: change 1138366 generated at 04/17/2019 16:12:24 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_1_1005840689.arc
ORA-00280: change 1138366 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 1138777 generated at 04/17/2019 16:15:50 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_8_1005840689.arc
ORA-00280: change 1138777 for thread 1 is in sequence #8
ORA-00278: log file '/home/oracle/arch2/arch_1_7_1005840689.arc' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log '/home/oracle/arch2/arch_1_8_1005840689.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SYS@orcl2> recover database until cancel using backup controlfile;
ORA-00279: change 1138777 generated at 04/17/2019 16:15:50 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_8_1005840689.arc
ORA-00280: change 1138777 for thread 1 is in sequence #8
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel ==> 8번은 current라서 archive 안되서 없다.
Media recovery cancelled.
6. resetlogs로 오픈
SYS@orcl2> alter database open resetlogs;
Database altered.
SYS@orcl2> ! ls /u01/app/oracle/orcl2/*.log
/u01/app/oracle/orcl2/redo01.log
/u01/app/oracle/orcl2/redo02.log
/u01/app/oracle/orcl2/redo03.log
SYS@orcl2> select count(*) from hr.dept_new;
COUNT(*)
----------
27
SYS@orcl2> @logfile
GROUP# SEQUENCE# MEMBER MB ARC STATUS
---------- ---------- ----------------------------------- ---------- --- ----------------
1 1 /u01/app/oracle/orcl2/redo01.log 50 NO CURRENT
2 0 /u01/app/oracle/orcl2/redo02.log 50 YES UNUSED
3 0 /u01/app/oracle/orcl2/redo03.log 50 YES UNUSED
=========================================================================
다음을 반드시 수행 : 전체 DB cold backup 수행
ARCHIVE LOG : 전체 DB 백업
SYS@orcl2> @db_list
SYS@orcl2> shutdown immediate
[orcl2:~]$ chmod 755 orcl2_backup.sh
[orcl2:~]$ . orcl2_backup.sh -- 전체 DB 백업
[orcl2:~]$ rm /home/oracle/arch1/* /home/oracle/arch2/* -- 불필요한 archive log 삭제
SYS@orcl2> startup
=========================================================================
Backup control file과 현재 data file 정보가 다를 때 복구 - resetlogs 로 오픈 ( 데이타는 완전 복구)
/* 백업한 control file 내용과 현재 data file의 정보가 달라졌을때 복구 방식 */
1. 현재 상황 확인 & DB 작업 hr.emp_20 생성
SYS@orcl2> @logfile
GROUP# SEQUENCE# MEMBER MB ARC STATUS
---------- ---------- ----------------------------------- ---------- --- ----------------
1 7 /u01/app/oracle/orcl2/redo01.log 50 NO CURRENT
2 5 /u01/app/oracle/orcl2/redo02.log 50 YES INACTIVE
3 6 /u01/app/oracle/orcl2/redo03.log 50 YES INACTIVE
SYS@orcl2> create table hr.emp_20 as select * from hr.employees where department_id = 20;
Table created.
SYS@orcl2> select count(*) from hr.emp_20;
COUNT(*)
----------
2
SYS@orcl2> @switch
System altered.
SYS@orcl2> /
System altered.
SYS@orcl2> /
System altered.
SYS@orcl2> @logfile
GROUP# SEQUENCE# MEMBER MB ARC STATUS FIRST_CHANGE#
---------- ---------- ----------------------------------- ---------- --- ---------- -------------
1 10 /u01/app/oracle/orcl2/redo01.log 50 NO CURRENT 1166901
2 8 /u01/app/oracle/orcl2/redo02.log 50 YES ACTIVE 1166889
3 9 /u01/app/oracle/orcl2/redo03.log 50 YES ACTIVE 1166892
2. data_tbs 테이블스페이스 생성 : DB 구조의 변경이 일어났다.
SYS@orcl2> create tablespace data_tbs
datafile '/u01/app/oracle/orcl2/data04.dbf' size 5m
extent management local uniform size 64k
segment space management auto;
Tablespace created.
SYS@orcl2> create table hr.dept_new tablespace data_tbs as select * from hr.departments;
Table created.
SYS@orcl2> select count(*) from hr.dept_new;
COUNT(*)
----------
27
SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
3. 장애 발생
SYS@orcl2> ! rm /u01/app/oracle/orcl2/control01.ctl
SYS@orcl2> ! ls /u01/app/oracle/orcl2/control01.ctl
ls: /u01/app/oracle/orcl2/control01.ctl: No such file or directory
SYS@orcl2> startup
ORACLE instance started.
Total System Global Area 431038464 bytes
Fixed Size 1337016 bytes
Variable Size 146803016 bytes
Database Buffers 276824064 bytes
Redo Buffers 6074368 bytes
ORA-00205: error in identifying control file, check alert log for more info
=========================================================
alert log
ALTER DATABASE MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/orcl2/control01.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-205 signalled during: ALTER DATABASE MOUNT...
Shutting down instance (abort)
==========================================================
SYS@orcl2> shutdown abort
ORACLE instance shut down.
4. restore
SYS@orcl2> ! cp -av /home/oracle/backup/arch/cold/control01.ctl /u01/app/oracle/orcl2/control01.ctl
`/home/oracle/backup/arch/cold/control01.ctl' -> `/u01/app/oracle/orcl2/control01.ctl'
5. 복구 시도 1 recover database using backup controlfile; => 추가된 테이블스페이스 정보가 없어서 실패
SYS@orcl2> startup
ORACLE instance started.
Total System Global Area 431038464 bytes
Fixed Size 1337016 bytes
Variable Size 146803016 bytes
Database Buffers 276824064 bytes
Redo Buffers 6074368 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/app/oracle/orcl2/system01.dbf'
ORA-01207: file is more recent than control file - old control file
SYS@orcl2> recover database using backup controlfile
..............
ORA-00308: cannot open archived log '/home/oracle/arch2/arch_1_10_1005841294.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SYS@orcl2> recover database using backup controlfile
ORA-00279: change 1166901 generated at 04/18/2019 09:56:40 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_10_1005841294.arc
ORA-00280: change 1166901 for thread 1 is in sequence #10
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/orcl2/redo01.log
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 6: '/u01/app/oracle/orcl2/data04.dbf'
ORA-01112: media recovery not started
5. 복구 시도 2 : trace를 떠서 UNNAME 으로 되어있는 데이타파일을 rename 해주고 다시 복구
SYS@orcl2> alter database backup controlfile to trace as '/home/oracle/mkcon2.sql';
Database altered.
[orcl2:~]$ vi mkcon2.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL2" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/orcl2/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/orcl2/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/orcl2/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/orcl2/system01.dbf',
'/u01/app/oracle/orcl2/sysaux01.dbf',
'/u01/app/oracle/orcl2/undotbs01.dbf',
'/u01/app/oracle/orcl2/users01.dbf',
'/u01/app/oracle/orcl2/example01.dbf',
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00006'
CHARACTER SET AL32UTF8
;
SYS@orcl2> @datafile
FILE# NAME1 NAME2 STATUS CHECKPOINT_CHANGE#
---------- --------------- ----------------------------------- ---------- ------------------
1 SYSTEM /u01/app/oracle/orcl2/system01.dbf SYSTEM 1139360
2 SYSAUX /u01/app/oracle/orcl2/sysaux01.dbf ONLINE 1139360
3 UNDOTBS1 /u01/app/oracle/orcl2/undotbs01.dbf ONLINE 1139360
4 USERS /u01/app/oracle/orcl2/users01.dbf ONLINE 1139360
5 EXAMPLE /u01/app/oracle/orcl2/example01.dbf ONLINE 1139360
6 DATA_TBS /u01/app/oracle/product/11.2.0/dbho RECOVER 1167313
me_1/dbs/UNNAMED00006
6 rows selected.
SYS@orcl2> alter database rename file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00006' to '/u01/app/oracle/orcl2/data04.dbf';
Database altered.
SYS@orcl2> recover database using backup controlfile
ORA-00279: change 1167315 generated at 04/18/2019 10:00:57 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_10_1005841294.arc
ORA-00280: change 1167315 for thread 1 is in sequence #10
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/orcl2/redo01.log
Log applied.
Media recovery complete.
SYS@orcl2> alter database open resetlogs;
Database altered.
SYS@orcl2> select count(*) from hr.dept_new;
COUNT(*)
----------
27
SYS@orcl2> @datafile
FILE# NAME1 NAME2 STATUS CHECKPOINT_CHANGE#
---------- --------------- ----------------------------------- ---------- ------------------
1 SYSTEM /u01/app/oracle/orcl2/system01.dbf SYSTEM 1167989
2 SYSAUX /u01/app/oracle/orcl2/sysaux01.dbf ONLINE 1167989
3 UNDOTBS1 /u01/app/oracle/orcl2/undotbs01.dbf ONLINE 1167989
4 USERS /u01/app/oracle/orcl2/users01.dbf ONLINE 1167989
5 EXAMPLE /u01/app/oracle/orcl2/example01.dbf ONLINE 1167989
6 DATA_TBS /u01/app/oracle/orcl2/data04.dbf ONLINE 1167989
6 rows selected.
SYS@orcl2> @log
GROUP# SEQUENCE# STATUS ARC MEMBERS FIRST_CHANGE# MB
---------- ---------- ---------- --- ---------- ------------- ----------
1 1 CURRENT NO 1 1167986 50
2 0 UNUSED YES 1 0 50
3 0 UNUSED YES 1 0 50
SYS@orcl2> DROP TABLESPACE DATA_TBS INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
=========================================================================
다음을 반드시 수행 : 전체 DB cold backup 수행
ARCHIVE LOG : 전체 DB 백업
SYS@orcl2> @db_list
SYS@orcl2> shutdown immediate
[orcl2:~]$ chmod 755 orcl2_backup.sh
[orcl2:~]$ . orcl2_backup.sh -- 전체 DB 백업
[orcl2:~]$ rm /home/oracle/arch1/* /home/oracle/arch2/* -- 불필요한 archive log 삭제
SYS@orcl2> startup
=========================================================================
'DATABASE(oracleDB 11g) > DBA' 카테고리의 다른 글
| [Oracle DBA]ArchiveMode 에서의 Backup & Recovery-RMAN02 (0) | 2019.04.22 |
|---|---|
| [Oracle DBA]ArchiveMode 에서의 Backup & Recovery-RMAN01 (0) | 2019.04.22 |
| [Oracle DBA]ArchiveMode 에서의 Backup & Recovery-logmnr (0) | 2019.04.22 |
| [Oracle DBA]ArchiveMode 에서의 Backup & Recovery-redo log (0) | 2019.04.22 |
| [Oracle DBA]ArchiveMode 에서의 Backup & Recovery-archive_log (0) | 2019.04.22 |