2023/01/24
RMAN (Recovery Manager)
: RMAN(Recovery Manager)은 오라클 데이터베이스에서 백업, 복구 작업을 관리하는데 사용하는 오라클 유틸리티다.
- RMAN은 운영체제에 독립적인 강력한 명령언어를 사용한다.
▶ 1. 백업대상
- 디스크 디렉터리
- media management library(테이프 장치)
- Flash Recovery Area(Fast Recovery Area)
- 백업과 복구 및 flashback database 목적으로 마련된 디스크영역
- 파일은 필요에 따라 자동으로 보관 및 삭제 된다.
- 위치
show parameter db_recovery_file_dest
--------------------
NAME TYPE VALUE
-------------------------- ----------- ----------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size big integer 4152M - 크기
show parameter db_recovery_file_dest_size
------------------------
NAME TYPE VALUE
-------------------------- ----------- -----
db_recovery_file_dest_size big integer 4152M ▶ 2. RMAN 기본적인 사용법
- rman 접속
[oracle@oracle ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sun Jan 14 16:14:35 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (DBID=256148002)
-- ORA11G(DBID=256148002) 을 타겟으로 하고있다.- 스키마에대해서 report
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORA11G
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 750 SYSTEM *** /u01/app/oracle/oradata/ora11g/system01.dbf
2 680 SYSAUX *** /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 10 HRM_TBS *** /u01/app/oracle/oradata/ora11g/hrm_tbs01.dbf
4 8 USERS *** /u01/app/oracle/oradata/ora11g/users01.dbf
5 330 EXAMPLE *** /u01/app/oracle/oradata/ora11g/example01.dbf
6 74 UNDOTBS *** /u01/app/oracle/oradata/ora11g/undotbs01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/ora11g/temp01.dbf- 백업한 목록 조회
RMAN> list backup;
------------------------------------------
specification does not match any backup in the repository- 한번도 백업받지 않은 정보 조회
RMAN> report need backup;
--------------------------------------------
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
1 0 /u01/app/oracle/oradata/ora11g/system01.dbf
2 0 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 0 /u01/app/oracle/oradata/ora11g/hrm_tbs01.dbf
4 0 /u01/app/oracle/oradata/ora11g/users01.dbf
5 0 /u01/app/oracle/oradata/ora11g/example01.dbf
6 0 /u01/app/oracle/oradata/ora11g/undotbs01.dbf▶ 3. controlfile
control file을 자동 백업 설정
- OFF (기본값)
CONFIGURE CONTROFILE AUTOBACKUP OFF;
- ON
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON; new RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP ON; new RMAN configuration parameters are successfully stored
- controlfile 백업 파일 이름 설정 (기본값)
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';fast_recovery_area 에 저장된다.
- %F
: c-DBID-YYYYMMDD-QQ
QQ는 00으로 시작하고 최대값이 FF인 16진수 시퀀스이다.
controlfile 을 백업하고 있는 도중에 controlfile 의 내용이 변경된다면 백업전과 백업후에 내용이 달라지는 문제가 발생한다. 그런 상황이 되면 백업 받던 controlfile 을 스냅샷을 생성하고 백업후에 동기화 시켜서 문제를 해결한다.
- 이곳에 스냅샷을 생성한다.
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'u01/app/oracle/product/11.2.0.4/db_1/dbs/snapcf_ora11g.f'; #default
■ RMAN 백업
: controlfile 백업 on 이므로 자동으로 받아지고,
SPFILE도 자동으로 받아진다.
rman backup 은 사용한 블록만 가져오므로 효율적이다.
RMAN에서의 백업 파일 = backup piece
- backup database;
RMAN> backup database;
----------------------------------------
Starting backup at 14-JAN-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ora11g/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/ora11g/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/ora11g/example01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/ora11g/undotbs01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ora11g/hrm_tbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ora11g/users01.dbf
channel ORA_DISK_1: starting piece 1 at 14-JAN-24
channel ORA_DISK_1: finished piece 1 at 14-JAN-24
piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_14/o1_mf_nnndf_TAG20240114T163728_lt73srk7_.bkp tag=TAG20240114T163728 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 14-JAN-24
Starting Control File and SPFILE Autobackup at 14-JAN-24
piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_14/o1_mf_s_1158251883_lt73tvtz_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 14-JAN-24- list backup;
밑에 보면 controlfile SCN번호랑 datafile SCN번호가 안맞는데, redo,archive 파일, 스냅샷 있으므로 괜찮다.
RMAN> list backup;
------------------------------------------
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 1.22G DISK 00:00:29 14-JAN-24
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20240114T163728
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_14/o1_mf_nnndf_TAG20240114T163728_lt73srk7_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1680006 14-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf
2 Full 1680006 14-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 Full 1680006 14-JAN-24 /u01/app/oracle/oradata/ora11g/hrm_tbs01.dbf
4 Full 1680006 14-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf
5 Full 1680006 14-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf
6 Full 1680006 14-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 9.36M DISK 00:00:00 14-JAN-24
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20240114T163803
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_14/o1_mf_s_1158251883_lt73tvtz_.bkp
SPFILE Included: Modification time: 14-JAN-24
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 1680032 Ckp time: 14-JAN-24- 필요한 백업 확인
RMAN> report need backup;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- ------------------------------------------------------ 물리적위치에서 확인
[oracle@oracle archivelog]$ cd /u01/app/oracle/fast_recovery_area
[oracle@oracle fast_recovery_area]$ ls
ora11g ORA11G
[oracle@oracle fast_recovery_area]$ cd ORA11G/
[oracle@oracle ORA11G]$ ls
archivelog autobackup backupset onlinelog
[oracle@oracle ORA11G]$ cd archivelog/
[oracle@oracle archivelog]$ ls
2024_01_09 2024_01_11
[oracle@oracle archivelog]$ cd ..
[oracle@oracle ORA11G]$ ls
archivelog autobackup backupset onlinelog
[oracle@oracle ORA11G]$ cd autobackup/
[oracle@oracle autobackup]$ ls
2024_01_14
[oracle@oracle autobackup]$ date
Sun Jan 14 16:48:43 KST 2024■ RMAN 복구 시나리오
▶ 1. datafile 이 손상되었을때 시나리오
-> user managed 의 아카이브로그모드 시나리오 1번하고 같다.
● 1. 작업
- 테이블생성후 로그스위치 발생
SQL> create table hr.loc_new tablespace users as select * from hr.employees;
Table created.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.- 물리적 아카이브파일 확인
SQL> ! ls /home/oracle/arch1
arch_1_1_1158057974.arc arch_1_4_1158057974.arc arch_1_7_1158057974.arc
arch_1_2_1158057974.arc arch_1_5_1158057974.arc arch_1_8_1158057974.arc
arch_1_3_1158057974.arc arch_1_6_1158057974.arc- redo log 확인
select a.group#,b.thread#,b.sequence#, member, b.bytes/1024/1024 mb, b.archived, b.status,
b.first_change#, b.next_change#, b.next_time
from v$logfile a, v$log b
where a.group# = b.group#
order by 1,2;
---
1 1 7 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES INACTIVE 1682249 1682252 24/01/14
2 1 8 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES INACTIVE 1682252 1682255 24/01/14
3 1 9 /u01/app/oracle/oradata/ora11g/redo03.log 50 NO CURRENT 1682255 281474976710655 ● 2. 장애발생 : users 데이터파일 삭제 (생성한 테이블 있는곳)
- 생성한 테이블 위치확인
select f.file_name
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'LOC_NEW'
and e.owner = 'HR';
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/users01.dbf- users 데이터파일 삭제
SQL> ! rm /u01/app/oracle/oradata/ora11g/users01.dbf- 아직 리두버퍼 메모리에서 사용하고있어서 테이블 생성이 가능하다. (에러뜰수도 있음)
SQL> create table hr.emp_new tablespace users as select * from hr.employees;
Table created.● 3. RMAN으로 FAILURE 확인
- rman 접속
[oracle@oracle autobackup]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sun Jan 14 17:17:37 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (DBID=256148002)- failure 리스트 확인
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
105 HIGH OPEN 14-JAN-24 One or more non-system datafiles are missing- failure 자세히 보기 (advise failure;
쓰면 더 좋은데, 실습이니깐)
RMAN> list failure 105 detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
105 HIGH OPEN 14-JAN-24 One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 105
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
3785 HIGH OPEN 14-JAN-24 Datafile 4: '/u01/app/oracle/oradata/ora11g/users01.dbf' is missing
Impact: Some objects in tablespace USERS might be unavailable
- alertlog 창에도 보인다.
Sun Jan 14 17:14:37 2024
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_m000_3071.trc:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/app/oracle/oradata/ora11g/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Sun Jan 14 17:14:37 2024
Checker run found 1 new persistent data failures- backup된 리스트 보기
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 1.22G DISK 00:00:29 14-JAN-24
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20240114T163728
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_14/o1_mf_nnndf_TAG20240114T163728_lt73srk7_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1680006 14-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf
2 Full 1680006 14-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 Full 1680006 14-JAN-24 /u01/app/oracle/oradata/ora11g/hrm_tbs01.dbf
4 Full 1680006 14-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf
5 Full 1680006 14-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf
6 Full 1680006 14-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 9.36M DISK 00:00:00 14-JAN-24
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20240114T163803
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_14/o1_mf_s_1158251883_lt73tvtz_.bkp
SPFILE Included: Modification time: 14-JAN-24
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 1680032 Ckp time: 14-JAN-24- rman advise
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
105 HIGH OPEN 14-JAN-24 One or more non-system datafiles are missing
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=15 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file /u01/app/oracle/oradata/ora11g/users01.dbf was unintentionally renamed or moved, restore it
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 4
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/ora11g/ora11g/hm/reco_2638914110.hm
RMAN> list failure 105 detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
105 HIGH OPEN 14-JAN-24 One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 105
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
3785 HIGH OPEN 14-JAN-24 Datafile 4: '/u01/app/oracle/oradata/ora11g/users01.dbf' is missing
Impact: Some objects in tablespace USERS might be unavailable
오프라인모드로 즉시
백업본으로 restore
redo로 recover
온라인
● 4. 장애발생 테이블스페이스 offline immediate 변경
- rman에서 sql 쓸려면
RMAN> sql '쿼리';
RMAN> sql 'alter tablespace users offline immediate';
sql statement: alter tablespace users offline immediate● 5. 가장최근 백업으로 restore
RMAN> restore tablespace users;
Starting restore at 14-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 00004 to /u01/app/oracle/oradata/ora11g/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_14/o1_mf_nnndf_TAG20240114T163728_lt73srk7_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_14/o1_mf_nnndf_TAG20240114T163728_lt73srk7_.bkp tag=TAG20240114T163728
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 14-JAN-24● 6. 테이블스페이스 recover
: 아카이브, 리두 적용
RMAN> recover tablespace users;
Starting recover at 14-JAN-24
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 6 is already on disk as file /home/oracle/arch1/arch_1_6_1158057974.arc
archived log for thread 1 with sequence 7 is already on disk as file /home/oracle/arch1/arch_1_7_1158057974.arc
archived log for thread 1 with sequence 8 is already on disk as file /home/oracle/arch1/arch_1_8_1158057974.arc
archived log file name=/home/oracle/arch1/arch_1_6_1158057974.arc thread=1 sequence=6
media recovery complete, elapsed time: 00:00:00
Finished recover at 14-JAN-24● 7. 복구한 테이블스페이스 online 변경
RMAN> sql 'alter tablespace users online';
sql statement: alter tablespace users online- failure 리스트 확인
RMAN> list failure;
no failures found that match specification▶ 2. 기존위치(디스크)가 문제가 생길때 다른 디스크로 적용후 복구 시나리오
-> user managed 의 아카이브로그모드 시나리오 5번하고 같다.
● 1. 작업
- 다른 디스크라고 생각하고 테이블스페이스 생성
: 디스크가 깨졋을때 그곳에 restore할수 없으니깐 다른 디스크에다가 복구해줘야한다.
create tablespace insa_tbs datafile '/home/oracle/insa_tbs01.dbf' size 10m;
create table hr.dept_new tablespace insa_tbs
as select * from hr.departments;
select count(*) from hr.dept_new;
- 로그 스위치 발생
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.- 리두로그 확인
select a.group#,b.thread#,b.sequence#, member, b.bytes/1024/1024 mb, b.archived, b.status,
b.first_change#, b.next_change#, b.next_time
from v$logfile a, v$log b
where a.group# = b.group#
order by 1,2;
1 1 10 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES INACTIVE 1683866 1683869 24/01/14
2 1 11 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES INACTIVE 1683869 1683872 24/01/14
3 1 12 /u01/app/oracle/oradata/ora11g/redo03.log 50 NO CURRENT 1683872 281474976710655 - 물리적 아카이브파일 확인
SQL> ! ls /home/oracle/arch1
arch_1_10_1158057974.arc arch_1_3_1158057974.arc arch_1_7_1158057974.arc
arch_1_11_1158057974.arc arch_1_4_1158057974.arc arch_1_8_1158057974.arc
arch_1_1_1158057974.arc arch_1_5_1158057974.arc arch_1_9_1158057974.arc
arch_1_2_1158057974.arc arch_1_6_1158057974.arc● 2. 새로만든 테이블스페이스 부분 backup
- rman 접속
[oracle@oracle autobackup]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sun Jan 14 17:55:53 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (DBID=256148002)- 한번도 backup 안한것 report
RMAN> report need backup;
using target database control file instead of recovery catalog
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
7 0 /home/oracle/insa_tbs01.dbf
- 부분 백업
RMAN> backup tablespace insa_tbs;
Starting backup at 14-JAN-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=186 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/home/oracle/insa_tbs01.dbf
channel ORA_DISK_1: starting piece 1 at 14-JAN-24
channel ORA_DISK_1: finished piece 1 at 14-JAN-24
piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_14/o1_mf_nnndf_TAG20240114T175651_lt78gmqb_.bkp tag=TAG20240114T175651 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-JAN-24
Starting Control File and SPFILE Autobackup at 14-JAN-24
piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_14/o1_mf_s_1158256612_lt78gnxr_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 14-JAN-24● 3. 특정한 테이블스페이스 백업 list 확인
RMAN> list backup of tablespace insa_tbs;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 1.07M DISK 00:00:00 14-JAN-24
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20240114T175651
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_14/o1_mf_nnndf_TAG20240114T175651_lt78gmqb_.bkp
List of Datafiles in backup set 4
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
7 Full 1684864 14-JAN-24 /home/oracle/insa_tbs01.dbf● 4. 장애발생 :
- 아까 만든 테이블 위치 확인
select f.file_name
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'DEPT_NEW'
and e.owner = 'HR';
---
/home/oracle/insa_tbs01.dbf- datafile 삭제
SYS@ora11g> ! rm /home/oracle/insa_tbs01.dbf- alert log 창 확인 -> 에러발생
- 만약 DB가 내려간다면?
- startup mount
- alter database datafile '문제되는 데이터파일' offline;
- alter database open
- rman 접속
● 5. rman에서 failure 확인
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
105 HIGH OPEN 14-JAN-24 One or more non-system datafiles are missingRMAN> list failure 105 detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
105 HIGH OPEN 14-JAN-24 One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 105
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
3910 HIGH OPEN 14-JAN-24 Datafile 7: '/home/oracle/insa_tbs01.dbf' is missing
Impact: Some objects in tablespace INSA_TBS might be unavailable● 6. 작업형 모드로 수행
- 설명
- 문제되는 테이블스페이스 오프라인
- datafile 경로 변경
- 최근백업으로 restore
- 문제되는 datafile 스위치 - report schema로 조회
- recover
- online으로 변경
RMAN> run { sql 'ALTER TABLESPACE insa_tbs OFFLINE IMMEDIATE'; SET NEWNAME FOR DATAFILE '/home/oracle/insa_tbs01.dbf' TO '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf'; RESTORE TABLESPACE insa_tbs; SWITCH DATAFILE 7; -- 문제되는 file number 확인 RECOVER TABLESPACE insa_tbs; sql 'ALTER TABLESPACE insa_tbs ONLINE'; }
sql statement: ALTER TABLESPACE insa_tbs OFFLINE IMMEDIATE
executing command: SET NEWNAME
Starting restore at 14-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 00007 to /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_14/o1_mf_nnndf_TAG20240114T175651_lt78gmqb_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_14/o1_mf_nnndf_TAG20240114T175651_lt78gmqb_.bkp tag=TAG20240114T175651
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 14-JAN-24
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=1158258210 file name=/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf
Starting recover at 14-JAN-24
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 14-JAN-24
sql statement: ALTER TABLESPACE insa_tbs ONLINE
RMAN> list failure;
no failures found that match specification▶ 필요없는 백업본 삭제
- 테이블스페이스 삭제
drop tablespace insa_tbs including contents and datafiles;- 테이블스페이스, datafile 확인
select tablespace_name, file_name, bytes/1024/1024 mb from dba_data_files;
----
USERS /u01/app/oracle/oradata/ora11g/users01.dbf 8.75
SYSAUX /u01/app/oracle/oradata/ora11g/sysaux01.dbf 680
SYSTEM /u01/app/oracle/oradata/ora11g/system01.dbf 750
HRM_TBS /u01/app/oracle/oradata/ora11g/hrm_tbs01.dbf 10
EXAMPLE /u01/app/oracle/oradata/ora11g/example01.dbf 330
UNDOTBS /u01/app/oracle/oradata/ora11g/undotbs01.dbf 74.875
- 정책상 필요없는 백업정보, 아카이브 정보 확인
RMAN> report obsolete;
- 정책상 필요 없는 백업 정보 삭제
RMAN> delete obsolete;
- 백업 리스트 확인
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 1.22G DISK 00:00:29 14-JAN-24
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20240114T163728
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_14/o1_mf_nnndf_TAG20240114T163728_lt73srk7_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1680006 14-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf
2 Full 1680006 14-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 Full 1680006 14-JAN-24 /u01/app/oracle/oradata/ora11g/hrm_tbs01.dbf
4 Full 1680006 14-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf
5 Full 1680006 14-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf
6 Full 1680006 14-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7 Full 9.36M DISK 00:00:01 14-JAN-24
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20240114T202309
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_14/o1_mf_s_1158265389_lt7k0xy2_.bkp
SPFILE Included: Modification time: 14-JAN-24
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 1700794 Ckp time: 14-JAN-24▶ 아카이브 백업정보 체크,삭제
- 컨트롤 파일이 기억하고 있는 아카이브 정보와 실제 물리적으로 아카이브 정보를 체크
RMAN> crosscheck archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK
validation failed for archived log
archived log file name=/home/oracle/arch1/arch_1_3_1157971653.arc RECID=1 STAMP=1158004994
validation failed for archived log
archived log file name=/home/oracle/arch2/arch_1_3_1157971653.arc RECID=2 STAMP=1158004994
validation failed for archived log
archived log file name=/home/oracle/arch1/arch_1_4_1157971653.arc RECID=3 STAMP=1158016539
validation failed for archived log
archived log file name=/home/oracle/arch2/arch_1_4_1157971653.arc RECID=4 STAMP=1158016539
validation failed for archived log
archived log file name=/home/oracle/arch1/arch_1_5_1157971653.arc RECID=5 STAMP=1158022147
validation failed for archived log
archived log file name=/home/oracle/arch2/arch_1_5_1157971653.arc RECID=6 STAMP=1158022147
validation failed for archived log
archived log file name=/home/oracle/arch1/arch_1_1_1158049053.arc RECID=7 STAMP=1158049110
validation failed for archived log
archived log file name=/home/oracle/arch2/arch_1_1_1158049053.arc RECID=8 STAMP=1158049110
validation succeeded for archived log
archived log file name=/home/oracle/arch1/arch_1_6_1158057974.arc RECID=14 STAMP=1158253721
validation succeeded for archived log
archived log file name=/home/oracle/arch1/arch_1_7_1158057974.arc RECID=15 STAMP=1158253721
validation succeeded for archived log
archived log file name=/home/oracle/arch1/arch_1_8_1158057974.arc RECID=16 STAMP=1158253723
validation succeeded for archived log
archived log file name=/home/oracle/arch1/arch_1_9_1158057974.arc RECID=17 STAMP=1158255312
validation succeeded for archived log
archived log file name=/home/oracle/arch1/arch_1_10_1158057974.arc RECID=18 STAMP=1158255313
validation succeeded for archived log
archived log file name=/home/oracle/arch1/arch_1_11_1158057974.arc RECID=19 STAMP=1158255314
validation succeeded for archived log
archived log file name=/home/oracle/arch1/arch_1_12_1158057974.arc RECID=20 STAMP=1158257127
validation succeeded for archived log
archived log file name=/home/oracle/arch1/arch_1_13_1158057974.arc RECID=21 STAMP=1158257171
Crosschecked 16 objects
- 필요없는 아카이브 정보 출력
RMAN> list expired archivelog all;
List of Archived Log Copies for database with db_unique_name ORA11G
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
1 1 3 X 11-JAN-24
Name: /home/oracle/arch1/arch_1_3_1157971653.arc
2 1 3 X 11-JAN-24
Name: /home/oracle/arch2/arch_1_3_1157971653.arc
3 1 4 X 11-JAN-24
Name: /home/oracle/arch1/arch_1_4_1157971653.arc
4 1 4 X 11-JAN-24
Name: /home/oracle/arch2/arch_1_4_1157971653.arc
5 1 5 X 11-JAN-24
Name: /home/oracle/arch1/arch_1_5_1157971653.arc
6 1 5 X 11-JAN-24
Name: /home/oracle/arch2/arch_1_5_1157971653.arc
7 1 1 X 12-JAN-24
Name: /home/oracle/arch1/arch_1_1_1158049053.arc
8 1 1 X 12-JAN-24
Name: /home/oracle/arch2/arch_1_1_1158049053.arc
- 필요없는 아카이브 정보 삭제
RMAN> delete expired archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK
List of Archived Log Copies for database with db_unique_name ORA11G
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
1 1 3 X 11-JAN-24
Name: /home/oracle/arch1/arch_1_3_1157971653.arc
2 1 3 X 11-JAN-24
Name: /home/oracle/arch2/arch_1_3_1157971653.arc
3 1 4 X 11-JAN-24
Name: /home/oracle/arch1/arch_1_4_1157971653.arc
4 1 4 X 11-JAN-24
Name: /home/oracle/arch2/arch_1_4_1157971653.arc
5 1 5 X 11-JAN-24
Name: /home/oracle/arch1/arch_1_5_1157971653.arc
6 1 5 X 11-JAN-24
Name: /home/oracle/arch2/arch_1_5_1157971653.arc
7 1 1 X 12-JAN-24
Name: /home/oracle/arch1/arch_1_1_1158049053.arc
8 1 1 X 12-JAN-24
Name: /home/oracle/arch2/arch_1_1_1158049053.arc
Do you really want to delete the above objects (enter YES or NO)? y
deleted archived log
archived log file name=/home/oracle/arch1/arch_1_3_1157971653.arc RECID=1 STAMP=1158004994
deleted archived log
archived log file name=/home/oracle/arch2/arch_1_3_1157971653.arc RECID=2 STAMP=1158004994
deleted archived log
archived log file name=/home/oracle/arch1/arch_1_4_1157971653.arc RECID=3 STAMP=1158016539
deleted archived log
archived log file name=/home/oracle/arch2/arch_1_4_1157971653.arc RECID=4 STAMP=1158016539
deleted archived log
archived log file name=/home/oracle/arch1/arch_1_5_1157971653.arc RECID=5 STAMP=1158022147
deleted archived log
archived log file name=/home/oracle/arch2/arch_1_5_1157971653.arc RECID=6 STAMP=1158022147
deleted archived log
archived log file name=/home/oracle/arch1/arch_1_1_1158049053.arc RECID=7 STAMP=1158049110
deleted archived log
archived log file name=/home/oracle/arch2/arch_1_1_1158049053.arc RECID=8 STAMP=1158049110
Deleted 8 EXPIRED objects- 확인
RMAN> list expired archivelog all;
specification does not match any archived log in the repository- 아카이브 로그 조회
: 과거 아카이브정보를 지울때 rman을 사용한다
RMAN> delete expired archivelog all;
select sequence#, name from v$archived_log;
---
4 (null)
5 (null)
6 /home/oracle/arch1/arch_1_6_1158057974.arc
7 /home/oracle/arch1/arch_1_7_1158057974.arc
8 /home/oracle/arch1/arch_1_8_1158057974.arc▶ 백업셋 삭제
- 전체 백업셋 삭제
RMAN> delete backupset;- 특정한 번호의 백업셋 삭제
RMAN> delete backupset 1;
---------------
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
1 1 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_14/o1_mf_nnndf_TAG20240114T163728_lt73srk7_.bkp
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_14/o1_mf_nnndf_TAG20240114T163728_lt73srk7_.bkp RECID=1 STAMP=1158251848
Deleted 1 objects- 전체 데이터파일,컨트롤파일,SPFILE 백업
RMAN> backup database;▶ 백업 정보 체크,삭제
: 컨트롤 파일이 기억하고 있는 백업 정보와 실제 물리적으로 백업 정보를 체크
- crosscheck copy
RMAN> crosscheck copy;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK
specification does not match any datafile copy in the repository
specification does not match any archived log in the repository
validation failed for control file copy
control file copy file name=/home/oracle/backup/arch/hot_20240112/control01.ctl RECID=2 STAMP=1158016454
Crosschecked 1 objects- 필요없는것 copy 삭제
RMAN> delete copy;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK
specification does not match any datafile copy in the repository
specification does not match any archived log in the repository
List of Control File Copies
===========================
Key S Completion Time Ckp SCN Ckp Time
------- - --------------- ---------- ---------------
2 X 11-JAN-24 1550820 11-JAN-24
Name: /home/oracle/backup/arch/hot_20240112/control01.ctl
Tag: TAG20240111T231414
Do you really want to delete the above objects (enter YES or NO)? y
deleted control file copy
control file copy file name=/home/oracle/backup/arch/hot_20240112/control01.ctl RECID=2 STAMP=1158016454
Deleted 1 objects- 필요없는 백업 확인
RMAN> report obsolete;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
no obsolete backups found▶ 3. system 데이터 파일이 손상 되었을때 시나리오
-> user managed 의 아카이브로그모드 시나리오 7번하고 같다.
● 1. 작업
- redo log 확인
select a.group#,b.thread#,b.sequence#, member, b.bytes/1024/1024 mb, b.archived, b.status,
b.first_change#, b.next_change#, b.next_time
from v$logfile a, v$log b
where a.group# = b.group#
order by 1,2;
---
1 1 13 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES INACTIVE 1685539 1685580 24/01/14
2 1 14 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES INACTIVE 1685580 1701392 24/01/14
3 1 15 /u01/app/oracle/oradata/ora11g/redo03.log 50 NO CURRENT 1701392 281474976710655 - 테이블생성후, 로그스위치 발생
create table hr.emp_20240124 tablespace users
as select * from hr.employees;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;● 2. 장애 발생
- system 데이터파일 삭제
SYS@ora11g> ! rm /u01/app/oracle/oradata/ora11g/system01.dbf● 3. startup mount 단계까지 올린다
- rman 에서도 db shutdown, startup 가능하다.
RMAN> startup mount
connected to target database: ORA11G (DBID=256148002, not open)
using target database control file instead of recovery catalog
database is already started- startup mount
RMAN> startup mount
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
RMAN> list failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
4107 CRITICAL OPEN 14-JAN-24 System datafile 1: '/u01/app/oracle/oradata/ora11g/system01.dbf' is missing● 4. failure 조회
RMAN> list failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
4107 CRITICAL OPEN 14-JAN-24 System datafile 1: '/u01/app/oracle/oradata/ora11g/system01.dbf' is missing- 상세보기
RMAN> list failure 4107 detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
4107 CRITICAL OPEN 14-JAN-24 System datafile 1: '/u01/app/oracle/oradata/ora11g/system01.dbf' is missing
Impact: Database cannot be opened
● 5. 백업 리스트 확인후 restore
- 백업 리스트 확인
RMAN> list backup;- 최근 백업본으로 restore
RMAN> restore tablespace system;● 6. recover 후 open
RMAN> recover tablespace system;- db open
RMAN> alter database open;
database opened▶ 4. 모든 데이터 파일이 손상, 기존위치가 아닌 새로운 위치로 데이터파일 복구 시나리오
-> user managed 의 아카이브로그모드 시나리오 10번하고 같다.
● 1. 백업본 확인
- 백업본 리스트 확인
RMAN> list backup;- datafile 확인
select tablespace_name, file_name, bytes/1024/1024 mb from dba_data_files;
----
USERS /u01/app/oracle/oradata/ora11g/users01.dbf 8.75
SYSAUX /u01/app/oracle/oradata/ora11g/sysaux01.dbf 680
SYSTEM /u01/app/oracle/oradata/ora11g/system01.dbf 750
HRM_TBS /u01/app/oracle/oradata/ora11g/hrm_tbs01.dbf 10
EXAMPLE /u01/app/oracle/oradata/ora11g/example01.dbf 330
UNDOTBS /u01/app/oracle/oradata/ora11g/undotbs01.dbf 74.875● 2. 장애발생 : 모든 데이터파일 삭제
SYS@ora11g> ! rm /u01/app/oracle/oradata/ora11g/*.dbf- 에러
SYS@ora11g> alter system switch logfile;
ERROR:
ORA-03114: not connected to ORACLE- alert log 창
Sun Jan 14 21:55:16 2024
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_j000_21205.trc:
ORA-12012: error on auto execute of job 3
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/u01/app/oracle/oradata/ora11g/undotbs01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Sun Jan 14 21:55:17 2024
Checker run found 5 new persistent data failures● 3. rman 을 이용하여 mount단계까지 startup
RMAN> shutdown abort
using target database control file instead of recovery catalog
Oracle instance shut down- startup mount 단계
RMAN> startup mount
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes- report schema
: control 파일의 정보이다.
RMAN> report schema;
Report of database schema for database with db_unique_name ORA11G
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 0 SYSTEM *** /u01/app/oracle/oradata/ora11g/system01.dbf
2 0 SYSAUX *** /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 0 HRM_TBS *** /u01/app/oracle/oradata/ora11g/hrm_tbs01.dbf
4 0 USERS *** /u01/app/oracle/oradata/ora11g/users01.dbf
5 0 EXAMPLE *** /u01/app/oracle/oradata/ora11g/example01.dbf
6 0 UNDOTBS *** /u01/app/oracle/oradata/ora11g/undotbs01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/ora11g/temp01.dbf● 4. 백업 정보 확인
RMAN> list backup;
----
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8 Full 1.23G DISK 00:00:21 14-JAN-24
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20240114T204057
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_14/o1_mf_nnndf_TAG20240114T204057_lt7l29vo_.bkp
List of Datafiles in backup set 8
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1701790 14-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf
2 Full 1701790 14-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 Full 1701790 14-JAN-24 /u01/app/oracle/oradata/ora11g/hrm_tbs01.dbf
4 Full 1701790 14-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf
5 Full 1701790 14-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf
6 Full 1701790 14-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9 Full 9.36M DISK 00:00:01 14-JAN-24
BP Key: 9 Status: AVAILABLE Compressed: NO Tag: TAG20240114T204122
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_14/o1_mf_s_1158266482_lt7l32yj_.bkp
SPFILE Included: Modification time: 14-JAN-24
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 1701812 Ckp time: 14-JAN-24● 5. 스크립트 실행
- datafile 이름변경 (newname) 해주고
- restore
- switch
- recover
- open
RMAN> run {
set newname for datafile 4 to '/home/oracle/ora_data/users01.dbf';
set newname for datafile 2 to '/home/oracle/ora_data/sysaux01.dbf';
set newname for datafile 1 to '/home/oracle/ora_data/system01.dbf';
set newname for datafile 3 to '/home/oracle/ora_data/hrm_tbs01.dbf';
set newname for datafile 5 to '/home/oracle/ora_data/example01.dbf';
set newname for datafile 6 to '/home/oracle/ora_data/undotbs01.dbf';
restore database;
switch datafile all;
recover database;
alter database open;
}
...
media recovery complete, elapsed time: 00:00:02
Finished recover at 14-JAN-24
database opened▶ 불필요한 백업파일들 삭제 일련의 과정
- 스키마 확인, 백업확인, 삭제, 전체 백업, 백업확인
RMAN> report schema;
RMAN> list backup;
RMAN> delete backup;
RMAN> backup database;
RMAN> list backup;- 아카이브로그 리스트 확인
RMAN> list archivelog all;- 컨트롤파일정보와 실제랑 다른것 확인
RMAN> crosscheck archivelog all;- 만기된 아카이브 리스트 (안보일때도 있다.)
RMAN> list expired archivelog all;- 정책상 필요없는 백업 리스트
: 아카이브가 보인다.
RMAN> report obsolete;- 물리적 위치에서 확인
[oracle@oracle ora_data]$ ls /home/oracle/arch1
arch_1_15_1158057974.arc arch_1_17_1158057974.arc arch_1_19_1158057974.arc
arch_1_16_1158057974.arc arch_1_18_1158057974.arc arch_1_20_1158057974.arc- 필요없는 백업 삭제
RMAN> delete obsolete;- 물리적 위치에서 확인
[oracle@oracle ora_data]$ ls /home/oracle/arch1
- 다시 확인
RMAN> report obsolete;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
no obsolete backups found- 백업정보 확인
RMAN> list backup;아카이브파일을 백업
● 1. 작업
select a.group#,b.thread#,b.sequence#, member, b.bytes/1024/1024 mb, b.archived, b.status,
b.first_change#, b.next_change#, b.next_time
from v$logfile a, v$log b
where a.group# = b.group#
order by 1,2;
---
1 1 19 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES INACTIVE 1704691 1724829 24/01/14
2 1 20 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES INACTIVE 1724829 1745651 24/01/14
3 1 21 /u01/app/oracle/oradata/ora11g/redo03.log 50 NO CURRENT 1745651 281474976710655 - 로그 스위치 발생후 테이블 생성
alter system switch logfile;
create table hr.rman tablespace users
as select * from hr.employees;- 로그스위치 발생
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;- 물리적 위치확인
[oracle@oracle ora_data]$ ls /home/oracle/arch1
arch_1_21_1158057974.arc arch_1_23_1158057974.arc
arch_1_22_1158057974.arc arch_1_24_1158057974.arc● 2. 현재 아카이브파일을 압축해서 백업
- 만료된 아카이브 리스트 확인
RMAN> list expired archivelog all;
specification does not match any archived log in the repository
- 압축해서 현재 아카이브파일 백업하고 물리적인 아카이브 delete
RMAN> backup as compressed backupset archivelog all delete input tag='archivelog backup';
● 3. 백업된 아카이브 로그 확인
RMAN> list backup of archivelog all;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
12 2.17M DISK 00:00:01 14-JAN-24
BP Key: 12 Status: AVAILABLE Compressed: YES Tag: ARCHIVELOG BACKUP
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_14/o1_mf_annnn_ARCHIVELOG_BACKUP_lt7tfdx3_.bkp
List of Archived Logs in backup set 12
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 21 1745651 14-JAN-24 1749363 14-JAN-24
1 22 1749363 14-JAN-24 1749497 14-JAN-24
1 23 1749497 14-JAN-24 1749500 14-JAN-24
1 24 1749500 14-JAN-24 1749503 14-JAN-24
1 25 1749503 14-JAN-24 1750206 14-JAN-24▶ 5. 압축된 아카이브파일로 복구 시나리오
● 1. 작업
- users테이블스페이스에 테이블 생성
● 2. 장애발생 : users 데이터파일 삭제
SYS@ora11g> ! rm /u01/app/oracle/oradata/ora11g/users01.dbf- db 강제 종료
SYS@ora11g> shu abort
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SYS@ora11g> conn / as sysdba
Connected.
SYS@ora11g> shu abort
ORACLE instance shut down.● 3. 문제되는것 offline 후 db open
- mount 단계까지 startup
SYS@ora11g> 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'- recover file 조회
SYS@ora11g> select * from v$recover_file;
FILE# ONLINE ONLINE_
---------- ------- -------
ERROR CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
4 ONLINE ONLINE
FILE NOT FOUND 0- 문제되는 datafile offline
SYS@ora11g> alter database datafile 4 offline;
Database altered.- db open
SYS@ora11g> alter database open;
Database altered.● 4. restore 후에 recover
- restore
RMAN> restore tablespace users;- recover (압축된 아카이브 파일로 recover)
RMAN> recover tablespace users;● 5. 테이블스페이스 online
RMAN> sql 'alter tablespace users online';
sql statement: alter tablespace users online2023/01/25
- SHOW ALL
: controlfile auto백업으로 해준다 (controlfile은 중요하므로)
RMAN> show all;
CONFIGURE CONTROLFILE AUTOBACKUP OFF;- controlfile autobackup on
RMAN> CONFIGURE CONTROFILE AUTOBACKUP OFF;
- 불완전한 복구를 할때
- 아카이브파일 깨졋을때
- time base recovery
: DDL 문자 잘못했을때- current한 리두로그 그룹이 깨졌을때
▶ 6. 백업받지 않은 datafile 손상시 복구 시나리오
● 1. 작업
drop tablespace dba_tbs including contents and datafiles;
create tablespace data_tbs datafile
'/u01/app/oracle/oradata/ora11g/data01.dbf' size 5m;
create table hr.dept_temp tablespace data_tbs as select * from hr.departments;● 2. 장애 발생
SYS@ora11g> ! rm /u01/app/oracle/oradata/ora11g/data01.dbf● 3. db 내린후 mount 단계까지 start
SYS@ora11g> shutdown abort
ORACLE instance shut down.- rman 접속후 mount 단계까지
[oracle@oracle ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jan 24 21:26:56 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup mount
Oracle instance started
database mounted
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers ● 4. list failure 로 장애 확인
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
105 HIGH OPEN 24-JAN-24 One or more non-system datafiles are missing- detail로 봐 몇번 datafile이 손상되었는지 확인
RMAN> list failure 105 detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
105 HIGH OPEN 24-JAN-24 One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 105
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
4530 HIGH OPEN 24-JAN-24 Datafile 3: '/u01/app/oracle/oradata/ora11g/data01.dbf' is missing
Impact: Some objects in tablespace DATA_TBS might be unavailable테이블스페이스 구조, redo 정보가 변경될때
CONFIGURE CONTROLFILE AUTOBACKUP ON 이므로 자동으로 컨트롤 파일이 백업된다.
● 5. 문제되는 datafile offline 변경후 db open
RMAN> sql 'alter database datafile 3 offline';
sql statement: alter database datafile 3 offline- db open
RMAN> alter database open;
database opened● 6. 문제되는 데이터파일 대체할것 생성
RMAN> sql " alter database create datafile ''/u01/app/oracle/oradata/ora11g/data01.dbf'' ";
using target database control file instead of recovery catalog
sql statement: alter database create datafile ''/u01/app/oracle/oradata/ora11g/data01.dbf''● 7. tablespace online으로 변경
RMAN> sql 'alter tablespace data_tbs online';
sql statement: alter tablespace data_tbs online
- db open 시점
: 테이블스페이스 레벨에서 online offline 변경 가능하지만- db mount 시점
테이블스페이스 레벨에서 online offline 변경 불가
data 레벨에서 on/off 변경
▶ 7. 특정 datafile 이 깨졌을때 datafile레벨로 백업,복구 시나리오
● 1. 작업
- 테이블스페이스 파셜 백업
RMAN> backup tablespace data_tbs;- 특정 테이블스페이스 백업 리스트
RMAN> list backup of tablespace data_tbs;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
19 Full 1.07M DISK 00:00:00 24-JAN-24
BP Key: 19 Status: AVAILABLE Compressed: NO Tag: TAG20240124T221134
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_24/o1_mf_nnndf_TAG20240124T221134_lv2346c8_.bkp
List of Datafiles in backup set 19
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 1855537 24-JAN-24 /u01/app/oracle/oradata/ora11g/data01.dbf- 테이블스페이스에 datfile 추가
alter tablespace data_tbs add datafile
'/u01/app/oracle/oradata/ora11g/data02.dbf' size 10m;- 테이블생성
create table hr.emp_2040125 tablespace data_tbs
as select * from hr.employees;- 여러번 insert 해줘서 새로만든 datafile에 들어가게
insert into hr.emp_2040125
select * from hr.emp_2040125;1 Bit = 2진수 (0과 1)
1 Byte (1바이트) = 8bit (8비트) << 정보의 기본 단위
1 KB (킬로바이트) = 2의 10승 바이트, 2를 10번 곱한 것 = 1024 byte
1 MB (메가바이트) = 2의 20승 바이트 (1024KB)
1 GB (기가바이트) = 2의 30승 바이트 (1024 MB)
1 TB (테라바이트) = 2의 40승 바이트 (1024 GB)
1 PB (페타바이트) = 2의 50승 바이트 (1024 TB)
1 EB (엑사바이트) = 2의 60승 바이트 (1024 PB)
1 ZB (제타바이트) = 2의 70승 바이트 (1024 EB)
1 YB (요타바이트) = 2의 80승 바이트 (1024 ZB)
- 테이블 사이즈보기
select bytes/1024/1024 mb
from dba_segments
where segment_name = 'EMP_2040125'
and owner = 'HR';
MB
----------
5- 어느 segment에 있는지 확인
select f.file_name
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'EMP_2040125'
and e.owner = 'HR';
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/data02.dbf
/u01/app/oracle/oradata/ora11g/data02.dbf
/u01/app/oracle/oradata/ora11g/data02.dbf
/u01/app/oracle/oradata/ora11g/data01.dbf
/u01/app/oracle/oradata/ora11g/data01.dbf- 필요한 backup 확인
RMAN> report need backup;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
7 0 /u01/app/oracle/oradata/ora11g/data02.dbf
- datafile 레벨로 backup
RMAN> backup datafile '/u01/app/oracle/oradata/ora11g/data02.dbf';
- 테이블스페이스 레벨로 backup list 확인
RMAN> list backup of tablespace data_tbs;
List of Backup Sets
===================
....
List of Datafiles in backup set 19
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 1855537 24-JAN-24 /u01/app/oracle/oradata/ora11g/data01.dbf
List of Datafiles in backup set 22
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
7 Full 1856486 24-JAN-24 /u01/app/oracle/oradata/ora11g/data02.dbf- 테이블 생성
create table hr.dept_2040125 tablespace data_tbs
as select * from hr.dept;- 생성된 테이블 위치 확인
select f.file_name
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'DEPT_2040125'
and e.owner = 'HR';
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/data02.dbf● 2. 장애발생
SYS@ora11g> ! rm /u01/app/oracle/oradata/ora11g/data02.dbf● 3. rman 접속후 장애 확인
- list failure
: 안보일때도 있지만 alertlog 를 보면된다.
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
105 HIGH OPEN 24-JAN-24 One or more non-system datafiles are missing- failure detail 로 확인
RMAN> list failure 105 detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
105 HIGH OPEN 24-JAN-24 One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 105
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
4730 HIGH OPEN 24-JAN-24 Datafile 7: '/u01/app/oracle/oradata/ora11g/data02.dbf' is missing
Impact: Some objects in tablespace DATA_TBS might be unavailable● 4. 문제되는 datafile offline 변경후 dbopen
RMAN> sql 'alter database datafile 7 offline';
sql statement: alter database datafile 7 offline- db open;
RMAN> alter database open;
database opened- 그 문제되는 데이터파일의 테이블스페이스 다른 데이터파일 문제있는지 확인
SYS@ora11g> select count(*) from hr.dept_temp;
COUNT(*)
----------
27- 테이블스페이스 상태확인 -> RECOVER
SYS@ora11g> 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#;
----
1 SYSTEM /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM 1852812
2 SYSAUX /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE 1852812
3 DATA_TBS /u01/app/oracle/oradata/ora11g/data01.dbf ONLINE 1855537
4 USERS /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE 1852812
5 EXAMPLE /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE 1852812
6 UNDOTBS /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE 1852812
7 DATA_TBS /u01/app/oracle/oradata/ora11g/data02.dbf RECOVER 1856486● 5. datafile 레벨로 restore , recover
RMAN> restore datafile 7;
Starting restore at 24-JAN-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=180 device type=DISK
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 24-JAN-24- datafile레벨로 recover
RMAN> recover datafile 7;
Starting recover at 24-JAN-24
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 24-JAN-24● 6. datafile online변경
RMAN> sql 'alter database datafile 7 online';
sql statement: alter database datafile 7 online- 확인
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#;
---
1 SYSTEM /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM 1877180
2 SYSAUX /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE 1877180
3 DATA_TBS /u01/app/oracle/oradata/ora11g/data01.dbf ONLINE 1877180
4 USERS /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE 1877180
5 EXAMPLE /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE 1877180
6 UNDOTBS /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE 1877180
7 DATA_TBS /u01/app/oracle/oradata/ora11g/data02.dbf ONLINE 1877833테이블스페이스 삭제시 백업도 필요없기 때문에 삭제해줘야한다.
- 테이블스페이스를 삭제
drop tablespace data_tbs including contents and datafiles;- 정책상 필요없는 백업 삭제
RMAN> delete obsolete- 아카이브 확인
SYS@ora11g> ! ls /home/oracle/arch1
arch_1_31_1158057974.arc arch_1_33_1158057974.arc arch_1_35_1158057974.arc
arch_1_32_1158057974.arc arch_1_34_1158057974.arc▶ 8. db가 내려가있는 상태에서 undo 테이블스페이스의 datafile 삭제시 복구 시나리오
● 1. 장애 발생 : undo datafile 삭제
SYS@ora11g> ! rm /u01/app/oracle/oradata/ora11g/undotbs01.dbf- alert log 창에 에러발생
ORA-12012: error on auto execute of job 3
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/u01/app/oracle/oradata/ora11g/undotbs01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Thu Jan 25 00:38:16 2024
Checker run found 1 new persistent data failures● 2. db 강제종료후 mount단계까지 open
SYS@ora11g> shutdown abort
ORACLE instance shut down.
SYS@ora11g> 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.
● 3. rman 접속후 장애 확인
[oracle@oracle ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 25 00:39:58 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (DBID=256148002, not open)- list failure
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
105 HIGH OPEN 24-JAN-24 One or more non-system datafiles are missing- failure detail
RMAN> list failure 105 detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
105 HIGH OPEN 24-JAN-24 One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 105
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
4810 HIGH OPEN 25-JAN-24 Datafile 6: '/u01/app/oracle/oradata/ora11g/undotbs01.dbf' is missing
Impact: Some objects in tablespace UNDOTBS might be unavailable- 처음부터 advise failure 로 보면 편하다.
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
105 HIGH OPEN 24-JAN-24 One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 105
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
4810 HIGH OPEN 25-JAN-24 Datafile 6: '/u01/app/oracle/oradata/ora11g/undotbs01.dbf' is missing
Impact: Some objects in tablespace UNDOTBS might be unavailable
analyzing automatic repair options; this may take some time
using channel ORA_DISK_1
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file /u01/app/oracle/oradata/ora11g/undotbs01.dbf was unintentionally renamed or moved, restore it
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 6
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/ora11g/ora11g/hm/reco_2733741146.hm● 4. restore후 , recover
- undotbs 테이블스페이스 백업있는지 확인
RMAN> list backup of tablespace undotbs;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
15 Full 1.28G DISK 00:00:36 24-JAN-24
BP Key: 15 Status: AVAILABLE Compressed: NO Tag: TAG20240124T204433
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_24/o1_mf_nnndf_TAG20240124T204433_lv1y11gz_.bkp
List of Datafiles in backup set 15
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
6 Full 1829846 24-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
- restore
RMAN> restore datafile 6;- recover
RMAN> recover datafile 6;● 5. datafile online후에 db open
RMAN> sql 'alter database datafile 6 online';
sql statement: alter database datafile 6 online- 확인
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#;
---
1 SYSTEM /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM 1904080
2 SYSAUX /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE 1904080
4 USERS /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE 1904080
5 EXAMPLE /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE 1904080
6 UNDOTBS /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE 1904080
---- db open
RMAN> alter database open;
database openedOFFLINE 으로 설정할 수 없는 테이블 스페이스는
- SYSTEM
- 활성화 되어있는 UNDO
- 기본 TEMP 테이블 스페이스
OS상에서만 백업 삭제하면 시스템상에서도 삭제 시켜줘야한다.
-
OS상에서 백업본 삭제
list backup;으로 확인후 datafile백업본 물리적으로 삭제 -
list backup 해도 물리적으로만 삭제했으므로 보인다.
list backup -
delete backupset 백업셋번호 : 이렇게는 안된다.
-
control파일과 백업본 일치하는지확인
crosscheck backupset; -
만료된 백업 확인
list expired backupset; -
만료된 백업 삭제
delete expired backupset -
정책상 필요없는 백업삭제
delete obsolete
select a.group#,b.thread#,b.sequence#, member, b.bytes/1024/1024 mb, b.archived, b.status,
b.first_change#, b.next_change#, b.next_time
from v$logfile a, v$log b
where a.group# = b.group#
order by 1,2;
-----
1 1 37 /u01/app/oracle/oradata/ora11g/redo01.log 50 NO CURRENT 1904079 281474976710655
2 1 35 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES INACTIVE 1852811 1877179 24/01/24
3 1 36 /u01/app/oracle/oradata/ora11g/redo03.log 50 YES INACTIVE 1877179 1904079 24/01/25create tablespace data_tbs datafile
'/u01/app/oracle/oradata/ora11g/data_tbs01.log' size 10m;
RMAN> delete obsolete;
System altered.
SYS@ora11g> /
System altered.
SYS@ora11g> /
System altered.
cold 백업 있는 상태에서
tbs 테이블스페이스, 테이블 생성
다시 backup database;
tbs 테이블스페이스 삭제
time base recovery 할려면
현재 컨트롤파일은 tbs 테이블스페이스가 없으므로
그전의 백업컨트롤 파일로 restore 하고
▶ 9. 테이블스페이스 drop시에 time base recovery
● 1. 테이블스페이스 삭제
- tbs 테이블스페이스 삭제
drop tablespace data_tbs including contents and datafiles;● 2. nomount 상태로 controlfile restore
RMAN> startup nomount;
connected to target database (not started)
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- controlfile restore 하고
: 백업된 컨트롤파일에는 삭제된 테이블스페이스 있다는 시나리오
RMAN> restore controlfile from
'/u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159150903_lv2ks7tc_.bkp';2>
Starting restore at 25-JAN-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=171 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/ora11g/control01.ctl
Finished restore at 25-JAN-24● 3. mount 후 time base recovery하기전 날째 포맷변경
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1- 날짜 포맷변경
RMAN> sql 'alter session set nls_date_format ="yyyy/mm/dd hh24:mi:ss"';
sql statement: alter session set nls_date_format ="yyyy/mm/dd hh24:mi:ss"● 4. alertlog 창을 보고 테이블스페이스 삭제한 시간 전 확인하기
- alert log 파일
Thu Jan 25 02:22:30 2024
drop tablespace data_tbs including contents and datafiles
Deleted file /u01/app/oracle/oradata/ora11g/data_tbs01.log
Completed: drop tablespace data_tbs including contents and datafiles● 5. 전으로 recover
run {
set until time '2024/01/25 02:21:55';
restore database;
recover database;
}● 6. reset logs로 open
alter database open resetlogs;2023/01/26
- 시간정보 직접수정
date -s "2024-01-26 11:01:55"▶ 10. drop table 잘못했을시에 crone db를 이용하여 복구
● 1. 작업
- 테이블 생성후, 로그스위치, 테이블 삭제
alter system switch logfile;
create table hr.emp_new as select * from hr.employees;
select count(*) from hr.emp_new;
SYS@ora11g> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
25-JAN-24 05.34.45.706768 AM +09:00
drop table hr.emp_new purge;
alter system switch logfile;
alter system switch logfile;select a.group#,b.thread#,b.sequence#, member, b.bytes/1024/1024 mb, b.archived, b.status,
b.first_change#, b.next_change#, b.next_time
from v$logfile a, v$log b
where a.group# = b.group#
order by 1,2;
1 1 7 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES ACTIVE 1940601 1940683 24/01/25
2 1 8 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES ACTIVE 1940683 1940711 24/01/25
3 1 9 /u01/app/oracle/oradata/ora11g/redo03.log 50 NO CURRENT 1940711 281474976710655 ● 2. 장애발생
- 테이블 없는것을 알아차림
SYS@ora11g> select count(*) from hr.emp_new;
select count(*) from hr.emp_new
*
ERROR at line 1:
ORA-00942: table or view does not exist● 3. 복제 데이터베이스 생성시 필요한 데이터파일, 리두로그파일, 아카이브 파일 복사
: sysaux, system, undo 필수
- 클론 db의 초기파라미터 파일 생성
[oracle@oracle clone]$ vi $ORACLE_HOME/dbs/initclone.ora
---
compatible='11.2.0.4.0'
*.control_files='/home/oracle/clone/control01.ctl'
*.db_name='clone'
*.log_archive_dest_1='location=/home/oracle/clone mandatory'
*.log_archive_format='arch_%t_%s_%r.arc'
*.undo_tablespace='undotbs'
---[oracle@oracle ~]$ mkdir clone
[oracle@oracle ~]$ cd clone
[oracle@oracle clone]$ pwd
/home/oracle/clone- rman의 list backup을 복사
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
39 Full 1.22G DISK 00:00:22 25-JAN-24
BP Key: 39 Status: AVAILABLE Compressed: NO Tag: TAG20240125T052102
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T052102_lv2w9gpv_.bkp
List of Datafiles in backup set 39
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1939955 25-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf
2 Full 1939955 25-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
4 Full 1939955 25-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf
5 Full 1939955 25-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf
6 Full 1939955 25-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
40 Full 9.42M DISK 00:00:00 25-JAN-24
BP Key: 40 Status: AVAILABLE Compressed: NO Tag: TAG20240125T052127
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159161687_lv2wb7tv_.bkp
SPFILE Included: Modification time: 25-JAN-24
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 1939969 Ckp time: 25-JAN-24
- control 파일 복사
[oracle@oracle clone]$ cp -av /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159161687_lv2wb7tv_.bkp .
‘/u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159161687_lv2wb7tv_.bkp’ -> ‘./o1_mf_s_1159161687_lv2wb7tv_.bkp’- 아카이브 파일 복사
[oracle@oracle clone]$ cp -av /home/oracle/arch1/*.* /home/oracle/clone
‘/home/oracle/arch1/arch_1_2_1159152478.arc’ -> ‘/home/oracle/clone/arch_1_2_1159152478.arc’
‘/home/oracle/arch1/arch_1_3_1159152478.arc’ -> ‘/home/oracle/clone/arch_1_3_1159152478.arc’
‘/home/oracle/arch1/arch_1_4_1159152478.arc’ -> ‘/home/oracle/clone/arch_1_4_1159152478.arc’
‘/home/oracle/arch1/arch_1_5_1159152478.arc’ -> ‘/home/oracle/clone/arch_1_5_1159152478.arc’
‘/home/oracle/arch1/arch_1_6_1159152478.arc’ -> ‘/home/oracle/clone/arch_1_6_1159152478.arc’
‘/home/oracle/arch1/arch_1_7_1159152478.arc’ -> ‘/home/oracle/clone/arch_1_7_1159152478.arc’
‘/home/oracle/arch1/arch_1_8_1159152478.arc’ -> ‘/home/oracle/clone/arch_1_8_1159152478.arc’- 데이터파일 복사
[oracle@oracle ~]$ cp -av /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T052102_lv2w9gpv_.bkp /home/oracle/clone
‘/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T052102_lv2w9gpv_.bkp’ -> ‘/home/oracle/clone/o1_mf_nnndf_TAG20240125T052102_lv2w9gpv_.bkp’● 4. . oraenv 로 접속
- 만약 sqlplus 접속이 안되면
ps -ef | grep clone을 조회하여
kill -9 pmon번호하면 된다.
그리고startup pfile='경로' nomount
[oracle@oracle clone]$ . oraenv
ORACLE_SID = [ora11g] ? clone
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/11.2.0.4/db_1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oracle clone]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 25 06:01:06 2024
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.SYS@clone> startup nomount
ORACLE instance started.
Total System Global Area 167407616 bytes
Fixed Size 1363300 bytes
Variable Size 109052572 bytes
Database Buffers 50331648 bytes
Redo Buffers 6660096 bytes● 5. RMAN 클론 데이터베이스에 연결
[oracle@oracle ~]$ rman auxiliary /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jan 26 11:04:18 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to auxiliary database: CLONE (not mounted)- 클론db를 테이블 삭제된 시간 전으로
RMAN> RUN {
set newname for datafile 1 to '/home/oracle/clone/system01.dbf';
set newname for datafile 2 to '/home/oracle/clone/sysaux01.dbf';
set newname for datafile 4 to '/home/oracle/clone/users01.dbf';
set newname for datafile 5 to '/home/oracle/clone/example01';
set newname for datafile 6 to '/home/oracle/clone/undotbs01.dbf';
DUPLICATE TARGET DATABASE TO 'clone'
pfile='/u01/app/oracle/product/11.2.0.4/db_1/dbs/initclone.ora'
nofilenamecheck
backup location '/home/oracle/clone'
until time "to_date('2024-01-26 10.04.30','yyyy-mm-dd hh24:mi:ss')"
LOGFILE
'/home/oracle/clone/redo01.log' SIZE 50M,
'/home/oracle/clone/redo02.log' SIZE 50M,
'/home/oracle/clone/redo03.log' SIZE 50M;
}● 7. 클론 db에서 복원된 테이블 export
[oracle@oracle ~]$ exp system/oracel tables=hr.emp_new file=emp_temp.dmp● 8. 다시 운영 db로 돌아가서 import
[oracle@oracle ~]$ . oraenv
ORACLE_SID = [clone] ? ora11g
The Oracle base remains unchanged with value /u01/app/oracle● 9. import
[oracle@oracle ~]$ imp system/oracle tables=emp_new file=emp_temp.dmp schemas=hr● 10. 접속해서 테이블 확인
[oracle@oracle ~]$ sqlplus / as sysdba
SYS@ora11g> select count(*) from hr.emp_new▶ 11. drop table 잘못했을시에 crone db를 이용하여 복구
● 1. 작업
- 테이블생성로그, 스위치 발생
alter system switch logfile;
create table hr.new_20240126
as select * from hr.employees;
alter system switch logfile;- 로그파일 확인
select a.group#,b.thread#,b.sequence#, member, b.bytes/1024/1024 mb, b.archived, b.status,
b.first_change#, b.next_change#, b.next_time
from v$logfile a, v$log b
where a.group# = b.group#
order by 1,2;
---
1 1 1 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES ACTIVE 1584059 1591748 24/01/26
2 1 2 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES ACTIVE 1591748 1591777 24/01/26
3 1 3 /u01/app/oracle/oradata/ora11g/redo03.log 50 NO CURRENT 1591777 281474976710655 - 백업 만들 디렉토리 생성
[oracle@oracle ~]$ mkdir rman_clone- delete backup
RMAN> list backup;
specification does not match any backup in the repository
RMAN> delete backup;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
specification does not match any backup in the repository
- 백업 compressed 위치,포맷지정
%d : 데이터베이스이름
%U : %u%p%c
%u : 백업셋 번호와 백업셋이 생성된 시간을 조합한 8자리 번호
%p : 백업셋 피스 번호
%c : 중복된 백업 피스셋 내에서 백업 피스의 고유번호
%T : 날짜RMAN> backup as compressed backupset format '/home/oracle/rman_clone/%d_%U' database include current controlfile;
- 필요없는 백업본 지우기
- 정책상 필요없는 백업본지우고 , 만료된 아카이브 지우기
RMAN> delete obsolete RMAN> crosscheck archivelog all; RMAN> list expired archivelog all; RMAN> delete expired archivelog all;
- 다시한번 확인 (이미지 껍데기만 남아있는것도 있다)
RMAN> report obsolete; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 1 Report of obsolete backups and copies Type Key Completion Time Filename/Handle -------------------- ------ ------------------ -------------------- Control File Copy 2 11-JAN-24 /home/oracle/backup/arch/hot_20240112/control01.ctl
- crosscheck copy, delete
RMAN> crosscheck copy; released channel: ORA_DISK_1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=181 device type=DISK specification does not match any datafile copy in the repository specification does not match any archived log in the repository validation failed for control file copy control file copy file name=/home/oracle/backup/arch/hot_20240112/control01.ctl RECID=2 STAMP=1158016454 Crosschecked 1 objectsRMAN> delete copy;
● 2. 장애발생 : 테이블삭제
- 시간확인
SYS@ora11g> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
26-JAN-24 02.13.16.106968 PM +09:00- 테이블 삭제
SYS@ora11g> drop table hr.new_20240126;
Table dropped.- 로그 스위치 발생 (하지않으면 아카이브에 안떨어지니깐)
SYS@ora11g> alter system switch logfile;- 아카이브 파일 확인 (delete obsolete 한것 빼고)
select sequence# , name from v$archived_log
where name is not null;
--------------------
3 /home/oracle/arch1/arch_1_3_1158057974.arc
4 /home/oracle/arch1/arch_1_4_1158057974.arc
5 /home/oracle/arch1/arch_1_5_1158057974.arc● 4. 클론 db 초기파라미터 생성
- 클론 db pfile 생성
: datafile, logfile 이름 변경도 넣어주었다.
[oracle@oracle ~]$ vi $ORACLE_HOME/dbs/initclone.ora
-----------------
compatible='11.2.0.4.0'
*.control_files='/home/oracle/rman_clone/control01.ctl'
*.db_name='clone'
*.log_archive_dest_1='location=/home/oracle/rman_clone mandatory'
*.log_archive_format='arch_%t_%s_%r.arc'
*.undo_tablespace='undotbs'
db_file_name_convert=('/u01/app/oracle/oradata/ora11g/','/home/oracle/rman_clone/')
log_file_name_convert=('/u01/app/oracle/oradata/ora11g/','/home/oracle/rman_clone/')- 아카이브 파일 복사
[oracle@oracle ~]$ cp -av /home/oracle/arch1/*.* /home/oracle/rman_clone/
‘/home/oracle/arch1/arch_1_3_1158057974.arc’ -> ‘/home/oracle/rman_clone/arch_1_3_1158057974.arc’
‘/home/oracle/arch1/arch_1_4_1158057974.arc’ -> ‘/home/oracle/rman_clone/arch_1_4_1158057974.arc’
‘/home/oracle/arch1/arch_1_5_1158057974.arc’ -> ‘/home/oracle/rman_clone/arch_1_5_1158057974.arc’- 확인
[oracle@oracle ~]$ ls /home/oracle/rman_clone
arch_1_3_1158057974.arc arch_1_5_1158057974.arc ORA11G_032hicpp_1_1
arch_1_4_1158057974.arc ORA11G_022hicom_1_1- 논리적,물리적 백업셋 일치하는지 확인
RMAN> crosscheck backupset;
using channel ORA_DISK_1
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/rman_clone/ORA11G_022hicom_1_1 RECID=1 STAMP=1159279382
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/rman_clone/ORA11G_032hicpp_1_1 RECID=2 STAMP=1159279418
Crosschecked 2 objects● 5. 클론 db로 접속
- 오라클 홈 경로확인 (sqlplus 띄우기 위해서)
[oracle@oracle ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0.4/db_1- 클론 db접속
[oracle@oracle ~]$ . oraenv
ORACLE_SID = [ora11g] ? clone
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/11.2.0.4/db_1
The Oracle base remains unchanged with value /u01/app/oracle- sqlplus 접속
[oracle@oracle ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 26 14:29:10 2024
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options● 6. nomount 단계로 startup
SYS@clone> startup pfile=$ORACLE_HOME/dbs/initclone.ora nomount
ORACLE instance started.
Total System Global Area 167407616 bytes
Fixed Size 1363300 bytes
Variable Size 109052572 bytes
Database Buffers 50331648 bytes
Redo Buffers 6660096 bytes● 7. rman auxiliary 접속후
[oracle@oracle ~]$ rman auxiliary /- 테이블 DROP하기전으로 time base
DUPLICATE DATABASE TO 'clone'
pfile='/u01/app/oracle/product/11.2.0.4/db_1/dbs/initclone.ora'
nofilenamecheck
backup location '/home/oracle/rman_clone'
until time "to_date('2024-01-26 14:12:20','yyyy-mm-dd hh24:mi:ss')"● 8. export 후에
● 8. 원래 db에 import
▶ 12. RMAN에서의 cancel base recovey
● 1. 작업
- 자동 컨트롤 파일 백업 위치,포맷 설정
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/backup/rman/%F';- 백업 새로 받기 : 위치,포맷 설정
RMAN> delete backup;
RMAN> list backup;
RMAN> backup as compressed backupset format '/home/oracle/backup/rman/%d_%U_%T' database;- 리두로그 확인
select a.group#,b.thread#,b.sequence#, member, b.bytes/1024/1024 mb, b.archived, b.status,
b.first_change#, b.next_change#, b.next_time
from v$logfile a, v$log b
where a.group# = b.group#
order by 1,2;- 테이블 생성
-- SEQUENCE# 8번
create table hr.emp_arch
as select * from hr.employees;
alter system switch logfile;
-- SEQUENCE# 9번
create table hr.dept_arch
as select * from hr.departments;
alter system switch logfile;● 2. 장애발생
- users datafile 삭제
[oracle@oracle ~]$ rm /u01/app/oracle/oradata/ora11g/users01.dbf- 아카이브 파일 삭제 (두번째 테이블생성한 아카이브파일)
[oracle@oracle ~]$ cd arch1
[oracle@oracle arch1]$ ls
arch_1_10_1158057974.arc arch_1_5_1158057974.arc arch_1_8_1158057974.arc
arch_1_3_1158057974.arc arch_1_6_1158057974.arc arch_1_9_1158057974.arc
arch_1_4_1158057974.arc arch_1_7_1158057974.arc
[oracle@oracle arch1]$ rm arch_1_9_1158057974.arc● 3. rman 접속후 restore , recover
- rman 접속
[oracle@oracle ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jan 26 16:41:16 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (DBID=256148002, not open)- list failure
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
105 HIGH OPEN 26-JAN-24 One or more non-system datafiles are missing- list failure detail
RMAN> list failure 105 detail;- users 테이블스페이스 restore 해주고
RMAN> restore tablespace users;
Starting restore at 26-JAN-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK
...............
Finished restore at 26-JAN-24- 아카이브 파일 있는줄알고 recover
: 에러발생
RMAN> recover tablespace users;
RMAN-03002: failure of recover command at 01/26/2024 16:41:42
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 9 and starting SCN of 1601289 found to restore● 4. 아카이브 파일없는것 확인, nomount 단계까지 올린다(컨트롤파일이 손상되었을수도 있으므로)
RMAN> shutdown abort
Oracle instance shut down
RMAN> startup nomount
connected to target database (not started)
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- 확인 (진작에 list backup으로 봐둬야한다)
[oracle@oracle rman]$ ls
ORA11G_042hil3t_1_1_20240126 ORA11G_052hil50_1_1_20240126● 5. controlfile resotre 후 mount 단계까지 startup
RMAN> restore controlfile from '/home/oracle/backup/rman/ORA11G_052hil50_1_1_20240126';
Starting restore at 26-JAN-24
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/ora11g/control01.ctl
Finished restore at 26-JAN-24- mount까지 open
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1● 6. scn번호를 이용하여 그전까지 recover
: cancel base recovery 랑 똑같은것이다.
RMAN> recover database until scn 1601492;
- 만약 resetlogs 로 open한것을 백업한것을 이용하면 에러가 난다.
● 7. open resetlogs
RMAN> alter database open resetlogs;● 8. incarnatio 확인후 그전의 scn번호로 recover
- 데이터베이스가 복구 또는 리셋되는 시점
RMAN> list incarnation of database;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ORA11G 256148002 PARENT 1 25-AUG-13
2 2 ORA11G 256148002 PARENT 635002 07-DEC-23
3 3 ORA11G 256148002 PARENT 1513641 11-JAN-24
4 4 ORA11G 256148002 PARENT 1580233 12-JAN-24
5 5 ORA11G 256148002 PARENT 1580748 12-JAN-24
6 6 ORA11G 256148002 CURRENT 1584059 12-JAN-24- db 종료후 mount단계까지 open
RMAN> shutdown abort
RMAN> startup mount- 3번으로 reset
RMAN> RESET DATABASE TO INCARNATION 3;- set, restore, recover
RMAN> run {
set until scn=1580748;
restore database;
recover database;
}● 9.
RMAN> alter database open resetlogs;2023/01/29
▶ 13. controlfile 손상시 복구 시나리오
● 1. 백업작업
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;
delete backupset;
backup as compressed backupset database;
list backup;
report obsolete;
delete obsolete;
crosscheck archivelog all;
list expired archivelog all;
delete expired archivelog all;
report obsolete;
crosscheck copy;
delete copy;
report obsolete;- list backup
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 312.70M DISK 00:00:24 26-JAN-24
BP Key: 3 Status: AVAILABLE Compressed: YES Tag: TAG20240126T192257
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_26/o1_mf_nnndf_TAG20240126T192257_lv7201v5_.bkp
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1599371 26-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf
2 Full 1599371 26-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 Full 1599371 26-JAN-24 /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf
4 Full 1599371 26-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf
5 Full 1599371 26-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf
6 Full 1599371 26-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 1.03M DISK 00:00:01 26-JAN-24
BP Key: 4 Status: AVAILABLE Compressed: YES Tag: TAG20240126T192257
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_26/o1_mf_ncsnf_TAG20240126T192257_lv720vvk_.bkp
SPFILE Included: Modification time: 26-JAN-24
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 1599385 Ckp time: 26-JAN-24● 2. 장애발생 : controlfile 삭제
SYS@ora11g> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/control01.ctl
SYS@ora11g> ! rm /u01/app/oracle/oradata/ora11g/control01.ctl
SYS@ora11g> alter system checkpoint;
System altered.
- 에러발생
SYS@ora11g> shutdown immediate
Database closed.
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/ora11g/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3● 3. 컨트롤파일이 깨졋으므로 nomount 단계까지 startup
- db 강제종료
SYS@ora11g> shutdown abort
ORACLE instance shut down.- nomount
RMAN> startup nomount
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● 4. controlfile restore 후, mount
- restore 두가지 방법이 있다.
- restore auto backup
RMAN> restore controlfile from auto backup;
- 컨트롤파일 경로를 이용하여 restore
RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_26/o1_mf_ncsnf_TAG20240126T192257_lv720vvk_.bkp';
- mount
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1● 5. recover 후, open resetlogs
- recover
RMAN> recover database;- 이런 에러가뜬다? -> 8번으로
RMAN> recover database;
Starting recover at 26-JAN-24
using channel ORA_DISK_1
starting media recovery
unable to find archived log
archived log thread=1 sequence=4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/26/2024 19:46:04
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 4 and starting SCN of 1599371- open resetlog
RMAN> alter database open resetlogs;● 6. backup 다시받고 필요없는 backup 삭제
RMAN> backup as compressed backupset database;
RMAN> list backup;
RMAN> report obsolete;
RMAN> delete obsolete;
RMAN> list backup;● 8. recover 안될때 => resetlogs때문이다.
- 에러난것을 보면 SCN: 1599371 전으로 가야한다.
따라서 6번
: 데이터베이스가 복원되거나 resetlogs 작업이 수행된 경우와 같은 데이터베이스의 변경 내역을 기록합니다.
RMAN> list incarnation of database;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ORA11G 256148002 PARENT 1 25-AUG-13
2 2 ORA11G 256148002 PARENT 635002 07-DEC-23
3 3 ORA11G 256148002 PARENT 1513641 11-JAN-24
4 4 ORA11G 256148002 PARENT 1580233 12-JAN-24
5 5 ORA11G 256148002 PARENT 1580748 12-JAN-24
6 6 ORA11G 256148002 PARENT 1584059 12-JAN-24
7 7 ORA11G 256148002 CURRENT 1911228 25-JAN-24- db강제종료후 mount
RMAN> shutdown abort
Oracle instance shut down
RMAN> startup mount- 6번으로 INCARNATION
RMAN> RESET DATABASE TO INCARNATION 6;
database reset to incarnation 6- restore, recover
RMAN> run {
set until scn=1580748;
restore database;
recover database;
}- resetlogs로 open
RMAN> alter database open resetlogs;
database opened- 백업본 정리후, 다시백업 받기
RMAN> backup as compressed backupset database;
RMAN> list backup;
RMAN> report obsolete;
RMAN> delete obsolete;
RMAN> list backup;
RMAN> list incarnation;
RMAN> drop catalog;
recovery catalog owner is
enter DROP CATALOG command again to confirm catalog removal
불필요한 resetlogs 삭제
※ drop catalog 한후에는
timebase recovey 불가능하므로 조심해서 삭제해야한다.
또한 다시 backup 받아야한다.
● 1. drop catalog
RMAN> list incarnation;
RMAN> drop catalog;
recovery catalog owner is
enter DROP CATALOG command again to confirm catalog removal● 2. controlfile trace 뜨기
SYS@ora11g> alter database backup controlfile to trace as '/home/oracle/con.sql';
Database altered.● 3. db nomount 단계로 startup 한 후에
, trace한 controlfile로 재생성
SYS@ora11g> shutdown immediate- controlfile trace sql문 복사
[oracle@oracle ~]$ vi con.sql- startup mount
SYS@ora11g> startup nomount;- controlfile 재생성
SYS@ora11g> CREATE CONTROLFILE REUSE DATABASE "ORA11G" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/ora11g/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/ora11g/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/ora11g/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/ora11g/system01.dbf',
'/u01/app/oracle/oradata/ora11g/sysaux01.dbf',
'/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf',
'/u01/app/oracle/oradata/ora11g/users01.dbf',
'/u01/app/oracle/oradata/ora11g/example01.dbf',
'/u01/app/oracle/oradata/ora11g/undotbs01.dbf'
CHARACTER SET AL32UTF8
; 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
Control file created.● 4. recover 후 open
SYS@ora11g> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery requiredSYS@ora11g> alter database open;
Database altered.● 5. rman 접속후 확인
[oracle@oracle ~]$ rman target /
RMAN> list incarnation;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ORA11G 256148002 CURRENT 1600117 26-JAN-24
● 6. 재생성한 controlfile 에는 tempfile은 없으므로 trace딴 controlfile 가서 복붙
SYS@ora11g> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/ora11g/temp01.dbf'
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M;
Tablespace altered.● 7. 다시 백업받기
RMAN> list backup;
using target database control file instead of recovery catalog
specification does not match any backup in the repository
RMAN> backup as compressed backupset database;▶ 14. db 내려간 상태에서 spfile, pfile 손상시 복구 시나리오
● 1. 장애발생
[oracle@oracle db_1]$ cd $ORACLE_HOME/dbs
[oracle@oracle dbs]$ ls
hc_clone.dat initclone.ora initora11g.ora lkORA11G snapcf_ora11g.f
hc_ora11g.dat init.ora lkCLONE orapwora11g spfileora11g.ora
[oracle@oracle dbs]$ rm initora11g.ora
[oracle@oracle dbs]$ rm spfileora11g.ora- 장애발생
SYS@ora11g> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0.4/db_1/dbs/initora11g.ora'● 2. rman 접속후 nomout 단계로 열기
- spfile, pfile 이없는데 nomout 단계 어떻게여나?
: spfile이없어서 임시로 만든 메모리
[oracle@oracle ~]$ rman target /
RMAN> startup nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0.4/db_1/dbs/initora11g.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 1071333376 bytes
Fixed Size 1369420 bytes
Variable Size 281021108 bytes
Database Buffers 784334848 bytes
Redo Buffers 4608000 bytes● 3. DBID 값을 이용한 set
DBID 값을 꼭 알아야 한다!
: rman접속시에도 보인다.
RMAN> set dbid 256148002
executing command: SET DBID● 4. restore controlfile
- from autobackup
RMAN> restore spfile from autobackup;- 안된다면 경로지정
RMAN> restore spfile from '/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_26/o1_mf_ncsnf_TAG20240126T204412_lv76s67o_.bkp';
Starting restore at 26-JAN-24
using channel ORA_DISK_1
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_26/o1_mf_ncsnf_TAG20240126T204412_lv76s67o_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 26-JAN-24● 5. db 종료후 다시시작
RMAN> shutdown abort
Oracle instance shut down
RMAN> startup
connected to target database (not started)
Oracle instance started
database mounted
database opened
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes- 확인
SYS@ora11g> create pfile from spfile;
File created.
SYS@ora11g> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
.4/db_1/dbs/spfileora11g.ora● 6. 설정이 default로 돌아갔으므로 주의한다.
RMAN>c
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default- controlfile 자동백업 on
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;▶ 15. advise failure 을 통한 datafile 손상시 복구 시나리오
불완전 복구는 불가능하다.
● 1. 장애 발생
SYS@ora11g> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ora11g> ! rm /u01/app/oracle/oradata/ora11g/users01.dbf
SYS@ora11g> 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'● 2. rman 접속후 복구
- mount단계까지 startup
RMAN> startup mount- advise failure
: Repair script 생성한다.
RMAN> list failure
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
105 HIGH OPEN 26-JAN-24 One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 105
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
5400 HIGH OPEN 26-JAN-24 Datafile 4: '/u01/app/oracle/oradata/ora11g/users01.dbf' is missing
Impact: Some objects in tablespace USERS might be unavailable
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file /u01/app/oracle/oradata/ora11g/users01.dbf was unintentionally renamed or moved, restore it
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 4
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/ora11g/ora11g/hm/reco_3845644465.hm- repair script 보기
RMAN> repair failure preview;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/ora11g/ora11g/hm/reco_3845644465.hm
contents of repair script:
# restore and recover datafile
restore datafile 4;
recover datafile 4;
sql 'alter database datafile 4 online';- repair failure
RMAN> repair failure;▶ 15. db 내려간 상태에서 advise failure 을 통한 datafile, controlfile 손상시 복구 시나리오
● 1. 장애발생 : datafile, controlfile 손상
SYS@ora11g> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ora11g> ! rm /u01/app/oracle/oradata/ora11g/*.ctl
SYS@ora11g> ! rm /u01/app/oracle/oradata/ora11g/*.dbf
SYS@ora11g> 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● 2. rman 접속후 nomount에서 controfile restore
- rman 접속
[oracle@oracle ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jan 26 23:30:41 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (DBID=256148002, not open)- mount
RMAN> shutdown abort
RMAN> startup nomount- restore controlfile auto
RMAN> restore controlfile from auto backup;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================- restore controlfile 경로지정
RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/ORA11G/autockup/2024_01_26/o1_mf_s_1159307518_lv7bqgy6_.bkp';
...
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/ora11g/control01.ctl
Finished restore at 26-JAN-24
● 3. mount단계로 올린뒤 advise failure로 recover
- mount
RMAN> startup mount
database is already started
database mounted
released channel: ORA_DISK_1- list failure
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
5950 CRITICAL OPEN 26-JAN-24 System datafile 1: '/u01/app/oracle/oradata/ora11g/system01.dbf' is missing
5947 CRITICAL OPEN 26-JAN-24 Control file needs media recovery
105 HIGH OPEN 26-JAN-24 One or more non-system datafiles are missing- advise failure
RMAN> advise failure;
List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T052102_lv2w9gpv_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_26/o1_mf_nnndf_TAG20240126T195113_lv
...- repair failure 스크립트 보기
RMAN> repair failure preview;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/ora11g/ora11g/hm/reco_1214789575.hm
contents of repair script:
# restore and recover database
restore database;
recover database;
alter database open resetlogs;- repair failure
RMAN> repair failure;
....
contents of repair script:
# restore and recover database
restore database;
recover database;
alter database open resetlogs;
Do you really want to execute the above repair (enter YES or NO)? y
executing repair script
repair failure complete▶ 16. redolog file, controlfile 손상시 복구 시나리오
● 1. 장애 발생 : redologfile, controlfile 손상
SYS@ora11g> ! rm /u01/app/oracle/oradata/ora11g/*.log
SYS@ora11g> ! rm /u01/app/oracle/oradata/ora11g/*.ctlSYS@ora11g> shutdown abort● 2. nomount 단계에서 controlfile restore
RMAN> startup nomountRMAN> restore controlfile from auto backup;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_27/o1_mf_s_1159315622_lv7lnpqt_.bkp';
...
output file name=/u01/app/oracle/oradata/ora11g/control01.ctl
Finished restore at 27-JAN-2● 3. mount 단계에서 restore, recover
RMAN> startup mount
database is already started
database mounted
released channel: ORA_DISK_1RMAN> restore database;- recover -> 에러
current한 redolog가 없어서 완전복구 불가
따라서, 그전 SCN까지 아카이브파일로 복구
RMAN> recover database;
Starting recover at 27-JAN-24
using channel ORA_DISK_1
starting media recovery
unable to find archived log
archived log thread=1 sequence=1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/27/2024 00:33:05
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 1 and starting SCN of 1614198● 4. set until scn, restore, recover
- set until scn=번호;
RMAN> run {
set until scn=1614198;
restore database;
recover database;
}2> 3> 4> 5>
executing command: SET until clause
Starting restore at 27-JAN-24
using channel ORA_DISK_1
...
Finished recover at 27-JAN-24● 5. open resetlogs
RMAN> alter database open resetlogs;
database opened● 6. 백업 다시받기
RMAN> backup as compressed backupset database;■ max backupset size 조정
: 백업 파일을 backupset단위로 분할해서 backup 하기 위해
- backup set
: 논리적 백업 파일 단위- backup piece
: 물리적 백업 파일 단위
- configure maxsetsize to '사이즈값';
RMAN> show all;
RMAN> show maxsetsize;
RMAN configuration parameters for database with db_unique_name ORA11G are:
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
RMAN> configure maxsetsize to 600m;
RMAN> show maxsetsize;
RMAN configuration parameters for database with db_unique_name ORA11G are:
CONFIGURE MAXSETSIZE TO 600 M;- 시스템 데이터파일보다 maxsize가 작으므로 에러
RMAN> report schema;
Report of database schema for database with db_unique_name ORA11G
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 750 SYSTEM *** /u01/app/oracle/oradata/ora11g/system01.dbf- maxsetsize 변경
RMAN> configure maxsetsize to 800m;
- backup database maxsetsize '사이즈값';
RMAN> backup database maxsetsize 800m;- maxsetsize default 값으로 변경
RMAN> configure maxsetsize clear;■ max piece size 조정
: 백업 파일을 piece단위로 분할해서 backup 하기 위해
- maxpiecesize 변경
RMAN> configure channel device type disk maxpiecesize 600m;
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 600 M;
new RMAN configuration parameters are successfully stored
released channel: ORA_DISK_1- backup database
RMAN> backup database;- datafile이 3개의 backupset으로 백업받아져있다.
RMAN> list backupset;
List of Backup Pieces for backup set 27 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
27 1 AVAILABLE /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_27/o1_mf_nnndf_TAG20240127T012418_lv7q5lp4_.bkp
28 2 AVAILABLE /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_27/o1_mf_nnndf_TAG20240127T012418_lv7q5snh_.bkp
29 3 AVAILABLE /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_27/o1_mf_nnndf_TAG20240127T012418_lv7q60no_.bkp- 채널 할당하여 backup
RMAN> run {
allocate channel c1 device type disk maxpiecesize 100m;
backup tablespace system;
}2> 3> 4>
RMAN> list backup of tablespace system;■ image copy
- 백업 copy db
RMAN> backup as copy database;- copy 목록
RMAN> list copy;
specification does not match any control file copy in the repository
specification does not match any archived log in the repository
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
1 1 A 27-JAN-24 1618122 27-
...- 테이블스페이스 단위로 list copy
RMAN> list copy of tablespace system;
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
1 1 A 27-JAN-24 1618122 27-JAN-24
Name: /u01/app/oracle/fast_recovery_area/ORA11G/datafile/o1_mf_system_lv7qv2x0_.dbf
Tag: TAG20240127T013546- current한 controlfile 백업 copy
RMAN> backup as copy current controlfile;
Starting backup at 27-JAN-24
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/fast_recovery_area/ORA11G/controlfile/o1_mf_TAG20240127T013936_lv7r28gt_.ctl tag=TAG20240127T013936 RECID=6 STAMP=1159321176
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 27-JAN-24
Starting Control File and SPFILE Autobackup at 27-JAN-24
piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_27/o1_mf_s_1159321177_lv7r29ns_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 27-JAN-24- list copy
RMAN> list copy of controlfile;
List of Control File Copies
===========================
Key S Completion Time Ckp SCN Ckp Time
------- - --------------- ---------- ---------------
6 A 27-JAN-24 1618354 27-JAN-24
Name: /u01/app/oracle/fast_recovery_area/ORA11G/controlfile/o1_mf_TAG20240127T013936_lv7r28gt_.ctl
Tag: TAG20240127T013936
- 이미지 copy를 이용한 복구 작업
: 똑같다.
SYS@ora11g> ! rm /u01/app/oracle/oradata/ora11g/*.dbf
SYS@ora11g> shutdown abort
RMAN> startup mount
RMAN> restore databas;
RMAN> recover database;
RMAN> alter database open;▶ 16. image copy 백업을 이용한 복구 시나리오
● 1. 작업
- 테이블스페이스 생성후, 그곳에 테이블 생성
SYS@ora11g> create tablespace insa_tbs datafile '/home/oracle/insa_tbs01.dbf' size 10m;
Tablespace created.
SYS@ora11g> create table hr.emp_tbs tablespace insa_tbs
as select * from hr.employees; 2
Table created.- backup이 필요한것 있는지 확인
RMAN> report need backup;
using target database control file instead of recovery catalog
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
3 0 /home/oracle/insa_tbs01.dbf- datafile을 copy를 이용해 백업
RMAN> backup as copy datafile 3 format '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf';
Starting backup at 27-JAN-24- copy 목록확인
RMAN> list copy;- datafile copy 목록확인
RMAN> list datafilecopy all;● 2. 장애 발생 : 데이터파일 삭제
SYS@ora11g> ! rm /home/oracle/insa_tbs01.dbf
SYS@ora11g> shutdown immediate
ORA-03113: end-of-file on communication channel
Process ID: 7943
Session ID: 162 Serial number: 5
SYS@ora11g> conn / as sysdba
Connected to an idle instance.
SYS@ora11g> 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: '/home/oracle/insa_tbs01.dbf'● 3. datafile offline 변경후 db open
- datafile offline
SYS@ora11g> alter database datafile 3 offline;
Database altered.- open
SYS@ora11g> alter database open;
Database altered.● 4. rman 접속후 기존위치가 아닌 백업 파일 위치에 그대로 사용
[oracle@oracle ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Jan 27 02:01:28 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (DBID=256148002)- 기존위치가 아닌 백업 파일 위치에 그대로 사용
RMAN> list datafilecopy all;
using target database control file instead of recovery catalog
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
1 1 A 27-JAN-24 1618122 27-JAN-24
Name: /u01/app/oracle/fast_recovery_area/ORA11G/datafile/o1_mf_system_lv7qv2x0_ .dbf
Tag: TAG20240127T013546
2 2 A 27-JAN-24 1618128 27-JAN-24
Name: /u01/app/oracle/fast_recovery_area/ORA11G/datafile/o1_mf_sysaux_lv7qvkys_ .dbf
Tag: TAG20240127T013546
7 3 A 27-JAN-24 1619547 27-JAN-24
Name: /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf
Tag: TAG20240127T015213
5 4 A 27-JAN-24 1618155 27-JAN-24
Name: /u01/app/oracle/fast_recovery_area/ORA11G/datafile/o1_mf_users_lv7qw958_. dbf
Tag: TAG20240127T013546
3 5 A 27-JAN-24 1618134 27-JAN-24
Name: /u01/app/oracle/fast_recovery_area/ORA11G/datafile/o1_mf_example_lv7qw11j _.dbf
Tag: TAG20240127T013546
4 6 A 27-JAN-24 1618154 27-JAN-24
Name: /u01/app/oracle/fast_recovery_area/ORA11G/datafile/o1_mf_undotbs_lv7qw845 _.dbf
Tag: TAG20240127T013546
- 기존위치가 아닌 백업 파일 위치에 그대로 사용
: restore 할 필요없다.
RMAN> switch datafile 3 to copy;
datafile 3 switched to datafile copy "/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf"만약 그대로 안쓰고 싶으면
RMAN> restore datafile 3;
- online 변경
RMAN> sql 'alter database datafile 3 online';
sql statement: alter database datafile 3 online- 확인
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#;
SYS@ora11g> select count(*) from hr.emp_tbs;
COUNT(*)
----------
1072023/01/30
▶ 17. controlfile, datafile, redologfile 모든 파일이 손상되었고, 디스크가 손상되었을때 복구 시나리오
- user management
- pfile에 controlfile 위치변경
- 변경된 pfile로 nomount 단계까지 startup
- nomount 단계에서 controlfile restore
- mount 단계에서 datafile rename
- datafile restore
- cancel base recovery
- open resetlogs
- 전체 백업
● 1. 작업
[oracle@oracle ~]$ mkdir ora_data
SYS@ora11g> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
.4/db_1/dbs/spfileora11g.oraselect a.group#,b.thread#,b.sequence#, member, b.bytes/1024/1024 mb, b.archived, b.status,
b.first_change#, b.next_change#, b.next_time
from v$logfile a, v$log b
where a.group# = b.group#
order by 1,2;
-----------------
1 1 4 /u01/app/oracle/oradata/ora11g/redo01.log 50 NO CURRENT 1647979 281474976710655
2 1 2 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES INACTIVE 1640001 1647976 24/01/27
3 1 3 /u01/app/oracle/oradata/ora11g/redo03.log 50 YES INACTIVE 1647976 1647979 24/01/27● 2. controlfile, datafile, redologfile 삭제
SYS@ora11g> ! rm /u01/app/oracle/oradata/ora11g/*.*- db 강제종료
SYS@ora11g> shutdown abort
ORACLE instance shut down.● 3. pfile의 controlfile 위치 변경
[oracle@oracle ~]$ cd $ORACLE_HOME/dbs
[oracle@oracle dbs]$ ls
hc_clone.dat init.ora lkDUMMY snapcf_ora11g.f
hc_ora11g.dat initora11g.ora lkORA11G spfileora11g.ora
initclone.ora lkCLONE orapwora11g
[oracle@oracle dbs]$ vi initora11g.ora
--------------------------------------
*.control_files='/home/oracle/ora_data/control01.ctl'● 4. rman 접속후 변경된 pfile로 nomount 단계까지 startup
RMAN> startup pfile=$ORACLE_HOME/dbs/initora11g.ora nomount
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● 5. controlfile restore 후 mount단계로 올림
RMAN> restore controlfile from autobackup;
-- 또는
RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_27/o1_mf_s_1159331338_lv81ztz6_.bkp';
Starting restore at 27-JAN-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=171 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/home/oracle/ora_data/control01.ctl
Finished restore at 27-JAN-24- mount
RMAN> startup mount
database is already started
database mounted
released channel: ORA_DISK_1● 6. 작업형을 사용하여
: 타이핑오류나면 거기까지 controlfile이 변경되므로 다시 controlfile restore 해줘야한다.
- redologfile, datafile RENAME 작업
- restore
- switch
- recover
- open resetlogs
run { sql "alter database rename file ''/u01/app/oracle/oradata/ora11g/redo01.log'' TO ''/home/oracle/ora_data/redo01.log''"; sql "alter database rename file ''/u01/app/oracle/oradata/ora11g/redo02.log'' TO ''/home/oracle/ora_data/redo02.log''"; sql "alter database rename file ''/u01/app/oracle/oradata/ora11g/redo03.log'' TO ''/home/oracle/ora_data/redo03.log''"; set newname for datafile 1 to '/home/oracle/ora_data/system01.dbf'; set newname for datafile 2 to '/home/oracle/ora_data/sysaux01.dbf'; set newname for datafile 4 to '/home/oracle/ora_data/users01.dbf'; set newname for datafile 5 to '/home/oracle/ora_data/example01.dbf'; set newname for datafile 6 to '/home/oracle/ora_data/undotbs01.dbf'; set newname for tempfile 1 to '/home/oracle/ora_data/temp01.dbf'; restore database; switch datafile all; switch tempfile all; recover database; alter database open resetlogs; }
- current한 redolog가 없기때문에 에러에서 나온 SCN 번호까지 복구해야한다.
archived log file name=/home/oracle/arch1/arch_1_2_1159317223.arc thread=1 sequence=2
archived log file name=/home/oracle/arch1/arch_1_3_1159317223.arc thread=1 sequence=3
unable to find archived log
archived log thread=1 sequence=4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/27/2024 05:16:52
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 4 and starting SCN of 1647979● 7. 따라서 처음부터 작업해야한다.
set until scn=번호;
RMAN> shutdown abort
RMAN> startup pfile=$ORACLE_HOME/dbs/initora11g.ora nomount
RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_27/o1_mf_s_1159331338_lv81ztz6_.bkp';
RMAN> startup mountRMAN> run
{
sql "alter database rename file ''/u01/app/oracle/oradata/ora11g/redo01.log'' TO ''/home/oracle/ora_data/redo01.log''";
sql "alter database rename file ''/u01/app/oracle/oradata/ora11g/redo02.log'' TO ''/home/oracle/ora_data/redo02.log''";
sql "alter database rename file ''/u01/app/oracle/oradata/ora11g/redo03.log'' TO ''/home/oracle/ora_data/redo03.log''";
set newname for datafile 1 to '/home/oracle/ora_data/system01.dbf';
set newname for datafile 2 to '/home/oracle/ora_data/sysaux01.dbf';
set newname for datafile 4 to '/home/oracle/ora_data/users01.dbf';
set newname for datafile 5 to '/home/oracle/ora_data/example01.dbf';
set newname for datafile 6 to '/home/oracle/ora_data/undotbs01.dbf';
set newname for tempfile 1 to '/home/oracle/ora_data/temp01.dbf';
restore database;
switch datafile all;
switch tempfile all;
set until scn=1647979;
recover database;
alter database open;
}▶ 18. controlfile, datafile, redologfile 모든 파일들의 위치를 변경 (이관작업)
● 1. controlfile, datafile,redologfile
위치확인
select name from v$datafile;
select name from v$controlfile;
select member from v$logfile;● 2. db 정상종료후 데이터 옮기기
shutdown immediate- mv 로 새로운 위치에 데이터 옮기기
[oracle@oracle ~]$ cd 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]$ pwd
/home/oracle/ora_data
[oracle@oracle ora_data]$ mv *.* /u01/app/oracle/oradata/ora11g/● 3. 초기파라미터 파일 controlfile 위치 수정후 pfile을 이용하여 mount 단계까지 open
[oracle@oracle dbs]$ vi initora11g.ora
--------------------------------------
*.control_files='/u01/app/oracle/oradata/ora11g/control01.ctl'- pfile이용하여 mount 단계까지
SYS@ora11g> startup pfile=$ORACLE_HOME/dbs/initora11g.ora mountsystem,undo,temp, redolog는
따라서 db내린후 mount단계에서 작업해야한다
● 4. 모든 datafile, redologfile, tempfile RENAME 해준다
alter database rename file '기존위치' to '새로운위치';
● 5. db open 후 pfile이용하여 spfile 만들기
SYS@ora11g> alter database open;- spfile 생성
SYS@ora11g> create spfile from pfile;rman 백업 유형
- FULL 백업은 상용된 모든 데이터 파일 블록을 포함한다.
- 레벨 0 incremental 백업은 레벨 0으로 표시된 FULL 백업과 동일
- cumulative 레벨 1 incremental 백업은 마지막 레벨 0 incremental 백업 이후 수정한 블록만 포함
- dirfferential 레벨 1 incremental 백업은 마지막 incremental 백업 이후 수정한 블록만 포함
월 화 수 목 금 토 일
full(0) d(1) d(1) c(1) d(1) d(1) full(0) dirfferential : 변경된 데이터만 백업
cumulative : full 백업후 변경된 데이터 전부 백업
만약 금요일날 깨져서 restore 해야한다면, 월요일 full백업 restore 수요일날 cumulative백업 restore 목요일날 dirfferential백업 restore 해주면 된다.
■ 사용방법
- 레벨 0에서 incremental backup(full backup)
RMAN> backup incremental level 0 database;
- differential incremental backup( incremental backup 이후에 변경한 블록만 백업)
RMAN> backup incremental level 1 database;
- cumulative incremental backup( incremental backup(0) 이후에 변경한 블록들 다 백업)
RMAN> backup incremental level 1 cumulative database;
■ block change tracking
: 변경사항 추적 파일에서 변경된 블록 기록한다.
- ctwr
: 변경된 블록을 추적하고 추적된 기록을 적어놓는것 - 활성화된 경우 rman에 의해 자동으로 사용된다.
SYS@ora11g> alter database enable block change tracking using file '/home/oracle/backup/rman/block_tracking.txt';
Database altered.
SYS@ora11g> ! ls -l /home/oracle/backup/rman/block_tracking.txt
-rw-r-----. 1 oracle oinstall 11600384 Jan 27 09:27 /home/oracle/backup/rman/block_tracking.txt
- 백업중에 전체 데이터 파일을 스캔 하지 않도록 incremental 백업을 최적화 한다.
SYS@ora11g> show parameter db_create_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string- db_create_file_dest 파라미터를 설정한 경우 블록 변경 사항 추적 파일을 설정할 필요 없다. => OMF 방식 (Oracle Manged File)
alter database enable blcok change tracking; -- 활성화
alter database disable blcok change tracking; -- 비활성화- block_change_tracking 조회
SYS@ora11g> select * from v$block_change_tracking;
-------------------------------------
ENABLED /home/oracle/backup/rman/block_tracking.txt 11599872■ 백업실행
- incremental level 0 백업
RMAN> backup incremental level 0 database; ... Finished Control File and SPFILE Autobackup at 27-JAN-24
- backup 확인
datafile은 Incremental
spfile,controlfile은 Full
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
40 Incr 0 1.21G DISK 00:00:25 27-JAN-24
BP Key: 48 Status: AVAILABLE Compressed: NO Tag: TAG20240127T093450
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_27/o1_mf_nnnd0_TAG20240127T093450_lv8mxbw0_.bkp
List of Datafiles in backup set 40
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 0 Incr 1680849 27-JAN-24 /home/oracle/ora_data/system01.dbf
2 0 Incr 1680849 27-JAN-24 /home/oracle/ora_data/sysaux01.dbf
4 0 Incr 1680849 27-JAN-24 /home/oracle/ora_data/users01.dbf
5 0 Incr 1680849 27-JAN-24 /home/oracle/ora_data/example01.dbf
6 0 Incr 1680849 27-JAN-24 /home/oracle/ora_data/undotbs01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
41 Full 9.95M DISK 00:00:01 27-JAN-24
BP Key: 49 Status: AVAILABLE Compressed: NO Tag: TAG20240127T093515
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_27/o1_mf_s_1159349715_lv8my44k_.bkp
SPFILE Included: Modification time: 27-JAN-24
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 1680880 Ckp time: 27-JAN-24
- 작업하기
update hr.employees
set salary = salary*1.1
where department_id=20;
commit;
create table hr.inc_emp as select * from hr.employees;
delete from hr.inc_emp where department_id = 50;
commit;
alter system switch logfile;
- incremental level 1 백업
RMAN> backup incremental level 1 database;
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
40 Incr 0 1.21G DISK 00:00:25 27-JAN-24
BP Key: 48 Status: AVAILABLE Compressed: NO Tag: TAG20240127T093450
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_27/o1_mf_nnnd0_TAG20240127T093450_lv8mxbw0_.bkp
List of Datafiles in backup set 40
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 0 Incr 1680849 27-JAN-24 /home/oracle/ora_data/system01.dbf
2 0 Incr 1680849 27-JAN-24 /home/oracle/ora_data/sysaux01.dbf
4 0 Incr 1680849 27-JAN-24 /home/oracle/ora_data/users01.dbf
5 0 Incr 1680849 27-JAN-24 /home/oracle/ora_data/example01.dbf
6 0 Incr 1680849 27-JAN-24 /home/oracle/ora_data/undotbs01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
41 Full 9.95M DISK 00:00:01 27-JAN-24
BP Key: 49 Status: AVAILABLE Compressed: NO Tag: TAG20240127T093515
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_27/o1_mf_s_1159349715_lv8my44k_.bkp
SPFILE Included: Modification time: 27-JAN-24
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 1680880 Ckp time: 27-JAN-24
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
42 Incr 1 1.12M DISK 00:00:01 27-JAN-24
BP Key: 50 Status: AVAILABLE Compressed: NO Tag: TAG20240127T094546
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_27/o1_mf_nnnd1_TAG20240127T094546_lv8nkv6f_.bkp
List of Datafiles in backup set 42
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 1 Incr 1681337 27-JAN-24 /home/oracle/ora_data/system01.dbf
2 1 Incr 1681337 27-JAN-24 /home/oracle/ora_data/sysaux01.dbf
4 1 Incr 1681337 27-JAN-24 /home/oracle/ora_data/users01.dbf
5 1 Incr 1681337 27-JAN-24 /home/oracle/ora_data/example01.dbf
6 1 Incr 1681337 27-JAN-24 /home/oracle/ora_data/undotbs01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
43 Full 9.95M DISK 00:00:01 27-JAN-24
BP Key: 51 Status: AVAILABLE Compressed: NO Tag: TAG20240127T094547
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_27/o1_mf_s_1159350347_lv8nkw0c_.bkp
SPFILE Included: Modification time: 27-JAN-24
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 1681344 Ckp time: 27-JAN-24undo에 undo retention 기간 동안 쿼리문이 남아있음.
읽기일관성으로 long 쿼리 문장때문에
flashback query 때문에
하지만 항상 보장하는것은 아닌다
no guarantee 이기때문에
Flashback
undo_retention 기간동안 가능
, 지났다면 로그마이너로 쿼리추출해야한다.
■ Flashback Query
- 특정 시간에 query를 수행할 수 있다.
- select 문의 as of 절을 사용하여 데이터를 확인할 시간 기록을 지정할 수 있다.
- 데이터 불일치 분석에 유용하다.
● 1. 작업
- 테이블 생성
create table hr.emp_30
as select * from hr.employees where department_id=30;SYS@ora11g> select current_scn, systimestamp from v$database;
CURRENT_SCN
-----------
SYSTIMESTAMP
---------------------------------------------------------------------------
1594267
27-JAN-24 10.41.00.373688 AM +09:00- update 문
update hr.emp_30
set salary =3000
where employee_id=114;
commit;
SYS@ora11g> select current_scn, systimestamp from v$database;
CURRENT_SCN
-----------
SYSTIMESTAMP
---------------------------------------------------------------------------
1594466
27-JAN-24 10.44.55.764264 AM +09:00● 2. Flashback Query
- as of timestamp
: 특정시간대 쿼리를 확인할수있다
대신, undo_retention 기간동안만 가능
SYS@ora11g> select salary
from hr.emp_30 as of timestamp to_timestamp('2024/01/27 10:42:00','yyyy/mm/dd hh24:mi:ss')
where employee_id = 114;
SALARY
----------
11000■ Flashback version query
: version 절을 사용하여 두 point-in-time 또는 두 SCN 사이에 존재하는 행의 모든 버전을 검색할 수 있다.
● 1. 작업
SYS@ora11g> select count(*) from hr.emp_30;
COUNT(*)
----------
6
SYS@ora11g> select current_scn, systimestamp from v$database;
CURRENT_SCN
-----------
SYSTIMESTAMP
---------------------------------------------------------------------------
1595132
27-JAN-24 10.56.45.924870 AM +09:00
SYS@ora11g> update hr.emp_30 set salary=salary*1.1 where employee_id=115;
1 row updated.
SYS@ora11g> delete from hr.emp_30 where employee_id =116;
1 row deleted.
SYS@ora11g> commit;
Commit complete● 2. Flashback version query
- Flashback Query
SYS@ora11g> select salary
from hr.emp_30 as of timestamp to_timestamp('2024/01/27 10:56:45','yyyy/mm/dd hh24:mi:ss');
SALARY
----------
3000
3100
2900
2800
2600
2500
6 rows selected.select versions_xid, employee_id, last_name, salary
from hr.emp_30 versions between scn minvalue and maxvalue;
select versions_xid, employee_id, last_name, salary
from hr.emp_30 versions between timestamp to_timestamp('2024/01/27 10:56:45','yyyy/mm/dd hh24:mi:ss')
and to_timestamp('2024/01/27 10:57:00','yyyy/mm/dd hh24:mi:ss');
select versions_xid, employee_id, last_name, salary
from hr.emp_30 versions between scn minvalue
and '1595132';■ Flashback Table
: 백업으로 복원하지 않고 테이블을 특정 시점으로 recovery 할 수 있다.
- 데이터베이스는 온라인 상태를 유지 한다.
- flashback table 작업을 수행하기 위해 언두 테이블스페이스에서 데이터를 사용한다.
- 권한 부여
- 객체 권한
grant flashback on '테이블명' to '유저명'
- 시스템 권한
grant flashback any table to '유저명';
- flashback table에 대한 행 이동이 활성화 되어있어야 한다.
● 1. 작업
select current_scn, systimestamp from v$database;
1596824 24/01/27 11:17:02.488599000 +09:00
delete from hr.emp_30;
commit;● 2. 테이블을 활성화 작업
SYS@ora11g> alter table hr.emp_30 enable row movement;
Table altered.● 3. flashback table
SYS@ora11g> flashback table hr.emp_30 to timestamp to_timestamp('2024/01/27 11:17:00','yyyy/mm/dd hh24:mi:ss');
Flashback complete.- 확인
SYS@ora11g> select count(*) from hr.emp_30;
COUNT(*)
----------
5● 4. 테이블 비활성화
SYS@ora11g> alter table hr.emp_30 disable row movement;
Table altered.2023/01/31
- 오라클이 갖고있는 모든 옵션기능 확인
SELECT * FROM v$option;■ Flashback Data Archive
-
기록 데이터 저장소
-
fbda 백그라운드 프로세스를 사용하여 flashback data archive에 대해 활성화되어 있는
테이블의 데이터를 자동으로 추적하고 아카이브로 저장한다 -
트리거성
-
fbda를 저장할 테이블스페이스 생성
create tablespace fda_tbs datafile
'/u01/app/oracle/oradata/ora11g/fda_tbs01.dbf' size 10m
autoextend on next 1m;
- flashback data archive 생성
create flashback archive fda1 tablespace fda_tbs quota 10m retention 1 year; -- 1년 동안 가지고있겠다.
- fbda 아카이브 조회
SYS@ora11g> select flashback_archive_name, create_time, status
from dba_flashback_archive;
FLASHBACK_ARCHIVE_NAME
--------------------------------------------------------------------------------
CREATE_TIME
---------------------------------------------------------------------------
STATUS
-------
FDA1
27-JAN-24 12.57.24.000000000 PM- 테이블 생성
create table hr.emp_fda
as select * from hr.employees;
- fbda에 저장할 대상 테이블 활성화
alter table hr.emp_fda flashback archive fda1;
- fbda에 저장할 대상 테이블 비활성화
alter table hr.emp_fda no flashback archive;
- fbda 테이블 조회
SYS@ora11g> select * from dba_flashback_archive_tables;
▶ 특정시간전으로 돌아가기
- update 작업
select employee_id, salary
from hr.emp_fda
where department_id=20;
update hr.emp_fda
set salary=3000
where department_id = 20;
commit;- as of timestamp 2분전 조회
원래 as of timestamp 는 undo에서 찾아서 보여주지만, 지금은 테이블이 fbda 활성화 되어있으므로 fda에서 찾아서 보여준다.
select employee_id, salary
from hr.emp_fda as of timestamp(systimestamp - interval '2' minute)
where department_id=20;▶ 삭제된 데이터 복원
- 데이터 삭제
delete from hr.emp_fda;
commit;- fda에 있는 3분전 테이블의 데이터 확인
insert into hr.emp_fda
select *
from hr.emp_fda as of timestamp(systimestamp - interval '3' minute);
commit;- 확인
select count(*)
from hr.emp_fda;- retention 변경
alter flashback archive fda1 modify retention 2 year;
- fbda 만들어진 테이블과 테이블스페이스 작성
select * from dba_flashback_archive_ts;
- quota 값 변경
alter flashback archive fda1 modify tablespace fda_tbs quota 20m;
- fda 데이터 지우기
alter flashback archive fda1 purge before timestamp(systimestamp - interval '1' day);
- fda 삭제
drop flashback archive fda1;
■ Flashback Database
- 데이터베이스에 대해 되감기 버튼처럼 작동한다.
- 이전 데이터로 되감기 하기 위해서 리두 정보를 이용한다.
- 아카이브 모드에서 수행된다.
아카이브 모드 변경, 저장할 위치저장, 저장포맷 설정 (기본값인 flash recovery area 에 저장된다.)
- rvwr 백그라운드 프로세스에서 수행된다.
- flashback 할 수 있는 시간을 분단위로 설정
SYS@ora11g> show parameter db_flashback_retention_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440- db_flashback_retention_target 변경
: flashback database 가능한 시간
alter system set db_flashback_retention_target=2880 scope=both;- flashback database 활성화
alter database flashback on;- 확인
SYS@ora11g> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES● 1. 작업
SYS@ora11g> create table hr.jan as select * from hr.employees;- flash_recovery_area 사용량 보기
select * from v$flash_recovery_area_usage;- recovery_file 위치
SYS@ora11g> show parameter db_recovery_file_dest
NAME TYPE VALUE
-------------------------- ----------- ----------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size big integer 4152M - restore point 생성
create restore point before_hr_jan_trunc;- 확인
select * from v$restore_point;
----------------
1613043 6 NO 0 24/01/27 14:22:10.000000000 NO BEFORE_HR_JAN_TRUNC● 2. 테이블 삭제
- 테이블 데이터 삭제
truncate table hr.jan;undo 에 쌓이다가
다른 트랜잭션이 밀린다.
delete와 truncate 의 차이점
- extent를 해제하냐 마냐
- rollback 가능/불가능
- undo 를 사용하냐/미비하게 사용하냐
extent를 해제하기때문에 dictionary에 갱신조작을 하기때문에
● 3. flashback database
- db mount 단계까지 올리기
SYS@ora11g> shu immediate
SYS@ora11g> startup mount- restore point 를 이용하여 flashback database
SYS@ora11g> flashback database to restore point before_hr_jan_trunc;● 4. read only모드로 open, 확인
- read only 모드로 db open
SYS@ora11g> alter database open read only;
Database altered.- 확인
SYS@ora11g> select count(*) from hr.jan;
COUNT(*)
----------
107● 5. resetlogs로 open
- db mount 단계까지 open
SYS@ora11g> shutdown immediate
SYS@ora11g> startup mount- open resetlogs
SYS@ora11g> alter database open resetlogs;
Database altered.■ 블록 손상(block corruption)
- 블록을 읽거나 쓸때마다 일관성 검사 한다.
- 블록버전
- 블록 dba(data block address) 값과 비교되는 캐시의 dba값을 체크
- 활성화된 블록의 체크섬
- 손상된 블록
- media 손상
- 논리적 또는 소프트웨어에 의한 손상
● 1. 작업
SYS@ora11g> create table hr.emp_copy as select * from hr.employees;
SYS@ora11g> select file_id, block_id
from dba_extents
where segment_name = 'EMP_COPY'
and owner = 'HR'; 2 3 4
FILE_ID BLOCK_ID
---------- ----------
4 688
select file_name from dba_data_files where file_id=4;
---
/u01/app/oracle/oradata/ora11g/users01.dbf● 2. 장애발생: 블록 손상
SYS@ora11g> ! dd if=/dev/zero of=/u01/app/oracle/oradata/ora11g/users01.dbf bs=8192 seek=688 count=2 conv=notrunc
2+0 records in
2+0 records out
16384 bytes (16 kB) copied, 0.00010189 s, 161 MB/s- 버퍼 캐시를 비우는 명령입니다
: 에러 발생 block corrupted
SYS@ora11g> alter system flush buffer_cache;
System altered.
SYS@ora11g> alter system flush buffer_cache;
System altered.
ORA-01578: ORACLE data block corrupted (file#4 , block #688)
ORA-01110 : data file 7: '/u01/app/oracle/oradata/ora11g/users01.dbf'- 데이터 파일의 블록 유효성을 확인
[oracle@oracle ~]$ dbv userid=system/oracle file=/u01/app/oracle/oradata/ora11g/users01.dbf blocksize=8192
----------------------------------------
Corrupt block relative dba: 0x010002b1 (file 4, block 689)
Completely zero block found during dbv:
DBVERIFY - Verification complete
Total Pages Examined : 800 -- 총 블록 수
Total Pages Processed (Data) : 154 -- 테이블 블록 수
Total Pages Failing (Data) : 0 -- 테이블 블록중 문제가 있는 블록수
Total Pages Processed (Index): 42 -- 인덱스 블록수
Total Pages Failing (Index): 0 -- 인덱스 블록중 문제가 있는 블록수
Total Pages Processed (Other): 394 -- 테이블이나 인덱스와 다른 블록수
Total Pages Processed (Seg) : 64 -- segment 관련 정보의 블록 수
Total Pages Failing (Seg) : 0 -- segment 관련 정보의 블록 중 문제되는 블록수
Total Pages Empty : 142 -- 비어 있는 블록수
Total Pages Marked Corrupt : 4 -- 문제가 있어섯 corrupt marked 된 블록 수
Total Pages Influx : 0 -- dbv를 사용하고 있는 동안 다른 사용자가 데이터를 변경하고 있는 블록을 dbv를 사용하기 위해 다시 읽은 블록의 수
Total Pages Encrypted : 0
Highest block SCN : 1620375 (0.1620375)● 4. rman 접속후 block recovery
- datafile 검증
RMAN> validate datafile 4;
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 FAILED 0 142 833 1620375
File Name: /u01/app/oracle/oradata/ora11g/users01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 154
Index 0 42
Other 4 462
validate found one or more corrupt blocks- 문제되는거 확인
RMAN> list failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
7334 HIGH OPEN 27-JAN-24 Datafile 4: '/u01/app/oracle/oradata/ora11g/users01.dbf' contains one or more corrupt blocks
RMAN> list failure 7334 detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
7334 HIGH OPEN 27-JAN-24 Datafile 4: '/u01/app/oracle/oradata/ora11g/users01.dbf' contains one or more corrupt blocks- advise failure
RMAN> advise failure;- 문제되는거 보기
RMAN> repair failure preview;- repair failure (복구 완료)
RMAN> repair failure;
...
Finished validate at 27-JAN-24
■ RMAN에서의 일관성있는 백업(cold backup) (datafile, controlfile 의 SCN번호가 맞음)
[아카이브 로그모드]
1. db 내리고
2. mount까지 올려놓고
3. backup database
[노아카이브 모드]
백업은 운영중에 불가능하므로
일관성있는 백업만 가능하다.
DB 공부를 하고 있는 사람입니다. 글을 읽어주셔서 감사하고 더 좋은 글을 쓰기 위해 노력하겠습니다 :)