| # 요약 | |
| ■ RMAN : Recovery Manager # RMAN으로 Backup = Hot Backup = Online Backup << 시나리오 1 >> 데이터파일 손상 << RMAN Backup >> Partial Backup, Tablespace Level << 시나리오 2 >> RMAN 백업O, 데이터파일 손상, 디스크손상으로 위치 변경 # 백업이후 지난 Log 정리 및 백업 << 시나리오 3 >> 시스템 파일 손상 ■ 모든 데이터 파일이 손상, 기존위치가 아닌 새로운 위치로 데이터파일 복구 # 아카이브파일 백업 |
|
■ RMAN : Recovery Manager
- RMAN은 오라클 데이터베이스에서 백업, 복구 작업을 관리하는데 사용하는 오라클 유틸리티
- RMAN은 운영체제에 독립적인 강력한 명령언어를 사용한다.
- RMAN = Hot Backup = Online Backup
- 불안전한 복구 가능
- User-managed backup 과 호환불가
1. 백업 대상
- 디스크 디렉터리
- Media Management Library(테이프 장치)
- Flash Recovery Area(Fast Recovery Area)
- 백업과 복구 및 flashback database 목적으로 마련된 디스크 영역
- 파일은 필요에 따라 자동으로 본관 및 삭제된다.
- 위치, 크기 (DB생성 시 결정)
show parameter db_recovery_file_dest
show parameter db_recovery_file_dest_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size big integer 4152M
SELECT thread#,sequence#,archived,status,first_change#,first_time,next_change#,next_time
FROM v$log;
THREAD# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- --------- ------------- ------------- ----------- ------------ ---------
1 10 YES INACTIVE 2139073 23-JAN-24 2151702 23-JAN-24
1 11 NO CURRENT 2151702 23-JAN-24 2.8147E+14
1 9 YES INACTIVE 1237662 23-JAN-24 2139073 23-JAN-24
[oracle@oracle ~]$ ls /home/oracle/arch1/
arch_1_10_1158511351.arc arch_1_3_1158511351.arc arch_1_6_1158511351.arc arch_1_9_1158511351.arc
arch_1_1_1158511351.arc arch_1_4_1158511351.arc arch_1_7_1158511351.arc
arch_1_2_1158511351.arc arch_1_5_1158511351.arc arch_1_8_1158511351.arc
# OS에서 RMAN 접속
[oracle@oracle ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jan 24 09:59:25 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (DBID=256148331)
RMAN>
- / : as sysdba
- DBID : 중복없다
# RMAN 구성 표시
RMAN> show all;
RMAN configuration parameters for database with db_unique_name ORA11G are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
-- Control File을 자동 백업 설정
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
-- Control File 백업 파일 이름 설정 %F : c-DBID-YYMMDD-QQ
- DBID : DB고유번호
- QQ : 00으로 시작, 최대값이 FF, 16진수 시퀸스
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0.4/db_1/dbs/snapcf_ora11g.f'; # default
-- 백업 중 checkpoint 변화 시 Control File의 스냅샷 임시 생성 > 백업 후 동기화해서 문제 해결
- 백업 중 checkpoint 변화?
예) begin backup > data buffer cache를 data file에 기록 - full checkpoint
> 이후 데이터는 임시로 redo log에 저장 > 데이터가 많아 log switch 발생 > 스냅샹 생성
# 자동 백업 설정 활성화, PFILE 생성
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
# 타겟DB의 테이블스페이스에 속한 데이터 확인
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 620 SYSAUX *** /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 22 UNDOTBS *** /u01/app/oracle/oradata/ora11g/undotbs01.dbf
4 7 USERS *** /u01/app/oracle/oradata/ora11g/users01.dbf
5 331 EXAMPLE *** /u01/app/oracle/oradata/ora11g/example01.dbf
6 10 HRM_TBS *** /u01/app/oracle/oradata/ora11g/hrm_tbs01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 62 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/undotbs01.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/hrm_tbs01.dbf
# RMAN으로 Backup = Hot Backup = Online Backup
RMAN> backup database;
Starting backup at 24-JAN-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=28 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=00003 name=/u01/app/oracle/oradata/ora11g/undotbs01.dbf
input datafile file number=00006 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 24-JAN-24
channel ORA_DISK_1: finished piece 1 at 24-JAN-24
piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_24/o1_mf_nnndf_TAG20240124T102100_lv0shxj2_.bkp tag=TAG20240124T102100 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 24-JAN-24
Starting Control File and SPFILE Autobackup at 24-JAN-24
piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_24/o1_mf_s_1159093306_lv0skd3f_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 24-JAN-24
>> User-Managed Backup의 경우 물리적 파일을 COPY하는 방식으로 미사용 블럭도 포함
>> RMAN Backup의 경우 사용한 블럭들만 쌓아서 메모리가 차면 아웃풋(File)
- Large Pool > 부족하면 Shared Pool
>> PFILE로 사용중이였다면 SPFILE 자동 생성이 안된다.
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 1.17G DISK 00:00:42 24-JAN-24
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20240124T102100
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_24/o1_mf_nnndf_TAG20240124T102100_lv0shxj2_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 2168291 24-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf
2 Full 2168291 24-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 Full 2168291 24-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
4 Full 2168291 24-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf
5 Full 2168291 24-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf
6 Full 2168291 24-JAN-24 /u01/app/oracle/oradata/ora11g/hrm_tbs01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 9.39M DISK 00:00:02 24-JAN-24
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20240124T102146
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_24/o1_mf_s_1159093306_lv0skd3f_.bkp
SPFILE Included: Modification time: 23-JAN-24
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 2168312 Ckp time: 24-JAN-24
>> 컨트롤파일의 SCN: 2168312 다르다 ? > 아카이브파일, 리두로그파일 있으니 걱정없다
# 백업이 필요한 파일 확인
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
---- ----- -----------------------------------------------------
>> 없다
# RMAN 나가기
RMAN> exit
Recovery Manager complete.
# 백업받은 물리적 위치 확인
[oracle@oracle ~]$ cd /u01/app/oracle/fast_recovery_area/ORA11G
[oracle@oracle ORA11G]$ ls -Ra
.:
. .. autobackup backupset onlinelog
./autobackup/2024_01_24:
. .. o1_mf_s_1159093306_lv0skd3f_.bkp
./backupset/2024_01_24:
. .. o1_mf_nnndf_TAG20240124T102100_lv0shxj2_.bkp
./onlinelog:
. ..
SYS@ora11g> SELECT thread#,sequence#,archived,status,first_change#,first_time,next_change#,next_time
FROM v$log;
THREAD# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- --------- ------------- ------------- ----------- ------------ ---------
1 10 YES INACTIVE 2139073 23-JAN-24 2151702 23-JAN-24
1 11 NO CURRENT 2151702 23-JAN-24 2.8147E+14
1 9 YES INACTIVE 1237662 23-JAN-24 2139073 23-JAN-24
SYS@ora11g> ! ls /home/oracle/arch1
arch_1_10_1158511351.arc arch_1_3_1158511351.arc arch_1_6_1158511351.arc arch_1_9_1158511351.arc
arch_1_1_1158511351.arc arch_1_4_1158511351.arc arch_1_7_1158511351.arc
arch_1_2_1158511351.arc arch_1_5_1158511351.arc arch_1_8_1158511351.arc
>>> 백업 이전의 아카이브는 필요없으니 삭제 가능
<< 시나리오 1 >> 데이터파일 손상
| SYS@ora11g> create table hr.loc_new tablespace users as select * from hr.employees; SYS@ora11g> alter system switch logfile; SYS@ora11g> alter system switch logfile; SYS@ora11g> alter system switch logfile; --Thread 1 advanced to log sequence 14 (LGWR switch) -- Current log# 2 seq# 14 mem# 0: /u01/app/oracle/oradata/ora11g/redo02.log SYS@ora11g> SELECT thread#,sequence#,archived,status,first_change#,first_time,next_change#,next_time FROM v$log; THREAD# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ---------- ---------- --------- ------------- ------------- ----------- ------------ --------- 1 13 YES INACTIVE 2169677 24-JAN-24 2169681 24-JAN-24 1 14 NO CURRENT 2169681 24-JAN-24 2.8147E+14 1 12 YES INACTIVE 2169674 24-JAN-24 2169677 24-JAN-24 SYS@ora11g> ! ls /home/oracle/arch1 arch_1_10_1158511351.arc arch_1_13_1158511351.arc arch_1_5_1158511351.arc arch_1_9_1158511351.arc arch_1_11_1158511351.arc arch_1_2_1158511351.arc arch_1_6_1158511351.arc arch_1_1_1158511351.arc arch_1_3_1158511351.arc arch_1_7_1158511351.arc arch_1_12_1158511351.arc arch_1_4_1158511351.arc arch_1_8_1158511351.arc SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 mb, b.archived, b.status FROM v$logfile a, v$log b WHERE a.group# = b.group# ORDER BY 1; GROUP# SEQUENCE# MEMBER MB ARCHIVED STATUS ---------- ---------- -------------------------------------------------- ---------- --------- ------------- 1 13 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES INACTIVE 2 14 /u01/app/oracle/oradata/ora11g/redo02.log 50 NO CURRENT 3 12 /u01/app/oracle/oradata/ora11g/redo03.log 50 YES INACTIVE SELECT e.segment_name,f.tablespace_name, f.file_name FROM dba_extents e, dba_data_files f WHERE f.file_id = e.file_id AND e.owner = 'HR' AND e.segment_name = 'LOC_NEW'; SEGMENT_NAME TABLESPACE_NAME FILE_NAME ----------------------- --------------- -------------------------------------------------- LOC_NEW USERS /u01/app/oracle/oradata/ora11g/users01.dbf # 장애 발생 SYS@ora11g> ! rm /u01/app/oracle/oradata/ora11g/users01.dbf SYS@ora11g> create table hr.emp_new tablespace users as select * from hr.employees; SYS@ora11g> select count(*) from hr.emp_new; COUNT(*) ---------- 107 >>> alert log 아직도 안나옴..데이터버퍼캐시에서 테이블 생성 시 테이블스페이스 블락들을 이미 사용중이였던걸 쓰고있어서 인듯 > alert log --Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_m000_30154.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 --Wed Jan 24 11:00:43 2024 --Checker run found 1 new persistent data failures > 데이터파일이 깨졌다고 시스템이 고장나진 않는 상황 |
|
| # 복구 | |
| [oracle@oracle ~]$ rman target / # DB 오류 목록 RMAN> list failure; using target database control file instead of recovery catalog List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 45 HIGH OPEN 24-JAN-24 One or more non-system datafiles are missing 22 HIGH OPEN 17-DEC-23 One or more non-system datafiles are offline >> Alert Log에 기록되는 시점에 검색이 가능하다 # DB오류 ID로 상세정보 확인 RMAN> list failure 45 detail; List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 45 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 45 Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 3185 HIGH OPEN 24-JAN-24 Datafile 4: '/u01/app/oracle/oradata/ora11g/users01.dbf' is missing Impact: Some objects in tablespace USERS might be unavailable >> 시스템파일이 아니니 운영 중 복구 가능 > offline > restore > recover > online RMAN> list backup; ...목록 확인 RMAN> sql 'alter tablespace users offline immediate'; sql statement: alter tablespace users offline immediate >> RMAN에서 SQL문 사용 시 문법 : SQL ' ' ; # Restore : Tablespace Level, 최근 백업파일로 적용 RMAN> restore tablespace users; Starting restore at 24-JAN-24 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=26 device type=DISK 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_24/o1_mf_nnndf_TAG20240124T102100_lv0shxj2_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_24/o1_mf_nnndf_TAG20240124T102100_lv0shxj2_.bkp tag=TAG20240124T102100 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 24-JAN-24 # Recover : Tablespace Level, 최근 백업파일 적용 RMAN> recover tablespace users; Starting recover at 24-JAN-24 using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 11 is already on disk as file /home/oracle/arch1/arch_1_11_1158511351.arc archived log for thread 1 with sequence 12 is already on disk as file /home/oracle/arch1/arch_1_12_1158511351.arc archived log for thread 1 with sequence 13 is already on disk as file /home/oracle/arch1/arch_1_13_1158511351.arc archived log file name=/home/oracle/arch1/arch_1_11_1158511351.arc thread=1 sequence=11 media recovery complete, elapsed time: 00:00:00 Finished recover at 24-JAN-24 RMAN> sql 'alter tablespace users online'; sql statement: alter tablespace users online RMAN> list failure; List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 22 HIGH OPEN 17-DEC-23 One or more non-system datafiles are offline >> 22번은 예전꺼라 무시 RMAN> exit Recovery Manager complete. [oracle@oracle ~]$ ls /u01/app/oracle/oradata/ora11g control01.ctl example01.dbf redo01.log redo03.log system01.dbf undotbs01.dbf data01.dbf hrm_tbs01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf SYS@ora11g> SELECT a.file#, a.name file_name, b.name tbs_name, a.status, a.checkpoint_change# FROM v$datafile a, v$tablespace b WHERE a.ts# = b.ts#; FILE# FILE_NAME TBS_NAME STATUS CHECKPOINT_CHANGE# ---------- -------------------------------------------------- ---------- ------------- ------------------ 1 /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM SYSTEM 2169681 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 2169681 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 2169681 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 2170910 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 2169681 6 /u01/app/oracle/oradata/ora11g/hrm_tbs01.dbf HRM_TBS ONLINE 2169681 SYS@ora11g> select count(*) from hr.emp_new; COUNT(*) ---------- 107 SYS@ora11g> select count(*) from hr.loc_new; COUNT(*) ---------- 107 SELECT thread#,sequence#,archived,status,first_change#,first_time,next_change#,next_time FROM v$log; THREAD# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ---------- ---------- --------- ------------- ------------- ----------- ------------ --------- 1 13 YES INACTIVE 2169677 24-JAN-24 2169681 24-JAN-24 1 14 NO CURRENT 2169681 24-JAN-24 2.8147E+14 1 12 YES INACTIVE 2169674 24-JAN-24 2169677 24-JAN-24 SYS@ora11g> ! ls /home/oracle/arch1 arch_1_10_1158511351.arc arch_1_13_1158511351.arc arch_1_5_1158511351.arc arch_1_9_1158511351.arc arch_1_11_1158511351.arc arch_1_2_1158511351.arc arch_1_6_1158511351.arc arch_1_1_1158511351.arc arch_1_3_1158511351.arc arch_1_7_1158511351.arc arch_1_12_1158511351.arc arch_1_4_1158511351.arc arch_1_8_1158511351.arc |
|
<< RMAN Backup >> Partial Backup, Tablespace Level
| SYS@ora11g> create tablespace insa_tbs datafile '/home/oracle/insa_tbs01.dbf' size 10m; SELECT tablespace_name,file_name,bytes/1024/1024 mb FROM dba_data_files; TABLESPACE_NAME FILE_NAME MB --------------- -------------------------------------------------- ---------- HRM_TBS /u01/app/oracle/oradata/ora11g/hrm_tbs01.dbf 10 USERS /u01/app/oracle/oradata/ora11g/users01.dbf 7.5 UNDOTBS /u01/app/oracle/oradata/ora11g/undotbs01.dbf 22.25 SYSAUX /u01/app/oracle/oradata/ora11g/sysaux01.dbf 620 SYSTEM /u01/app/oracle/oradata/ora11g/system01.dbf 750 EXAMPLE /u01/app/oracle/oradata/ora11g/example01.dbf 331.25 INSA_TBS /home/oracle/insa_tbs01.dbf 10 create table hr.dept_new tablespace insa_tbs as select * from hr.departments; select count(*) from hr.dept_new; COUNT(*) ---------- 27 SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 mb, b.archived, b.status FROM v$logfile a, v$log b WHERE a.group# = b.group# ORDER BY 1; GROUP# SEQUENCE# MEMBER MB ARCHIVED STATUS ---------- ---------- -------------------------------------------------- ---------- --------- ------------- 1 13 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES INACTIVE 2 14 /u01/app/oracle/oradata/ora11g/redo02.log 50 NO CURRENT 3 12 /u01/app/oracle/oradata/ora11g/redo03.log 50 YES INACTIVE ALTER SYSTEM SWITCH LOGFILE; ALTER SYSTEM SWITCH LOGFILE; ALTER SYSTEM SWITCH LOGFILE; --Thread 1 advanced to log sequence 17 (LGWR switch) -- Current log# 2 seq# 17 mem# 0: /u01/app/oracle/oradata/ora11g/redo02.log SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 mb, b.archived, b.status FROM v$logfile a, v$log b WHERE a.group# = b.group# ORDER BY 1; GROUP# SEQUENCE# MEMBER MB ARCHIVED STATUS ---------- ---------- -------------------------------------------------- ---------- --------- ------------- 1 16 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES INACTIVE 2 17 /u01/app/oracle/oradata/ora11g/redo02.log 50 NO CURRENT 3 15 /u01/app/oracle/oradata/ora11g/redo03.log 50 YES INACTIVE SYS@ora11g> ! ls /home/oracle/arch1 arch_1_10_1158511351.arc arch_1_13_1158511351.arc arch_1_2_1158511351.arc arch_1_6_1158511351.arc arch_1_11_1158511351.arc arch_1_14_1158511351.arc arch_1_3_1158511351.arc arch_1_7_1158511351.arc arch_1_1_1158511351.arc arch_1_15_1158511351.arc arch_1_4_1158511351.arc arch_1_8_1158511351.arc arch_1_12_1158511351.arc arch_1_16_1158511351.arc arch_1_5_1158511351.arc arch_1_9_1158511351.arc |
|
| # 백업 | |
| [oracle@oracle ~]$ rman target / 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 620 SYSAUX *** /u01/app/oracle/oradata/ora11g/sysaux01.dbf 3 22 UNDOTBS *** /u01/app/oracle/oradata/ora11g/undotbs01.dbf 4 7 USERS *** /u01/app/oracle/oradata/ora11g/users01.dbf 5 331 EXAMPLE *** /u01/app/oracle/oradata/ora11g/example01.dbf 6 10 HRM_TBS *** /u01/app/oracle/oradata/ora11g/hrm_tbs01.dbf 7 10 INSA_TBS *** /home/oracle/insa_tbs01.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 62 TEMP 32767 /u01/app/oracle/oradata/ora11g/temp01.dbf 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 /home/oracle/insa_tbs01.dbf # Partial Backup : Tablespace Level RMAN> backup tablespace insa_tbs; Starting backup at 24-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 24-JAN-24 channel ORA_DISK_1: finished piece 1 at 24-JAN-24 piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_24/o1_mf_nnndf_TAG20240124T114145_lv0y79vj_.bkp tag=TAG20240124T114145 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 24-JAN-24 Starting Control File and SPFILE Autobackup at 24-JAN-24 piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_24/o1_mf_s_1159098106_lv0y7c2b_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 24-JAN-24 RMAN> list backup; ... 누적 백업 내역 확인 # 특정 백업본 보기 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 24-JAN-24 BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20240124T114145 Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_24/o1_mf_nnndf_TAG20240124T114145_lv0y79vj_.bkp List of Datafiles in backup set 4 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 7 Full 2173274 24-JAN-24 /home/oracle/insa_tbs01.dbf |
|
<< 시나리오 2 >> RMAN 백업O, 데이터파일 손상, 디스크손상으로 위치 변경
| SELECT e.segment_name,f.tablespace_name, f.file_name FROM dba_extents e, dba_data_files f WHERE f.file_id = e.file_id AND e.owner = 'HR' AND e.segment_name = 'DEPT_NEW'; SEGMENT_NAME TABLESPACE_NAME FILE_NAME ----------------------- --------------- -------------------------------------------------- DEPT_NEW INSA_TBS /home/oracle/insa_tbs01.dbf # 장애 발생 ! rm /home/oracle/insa_tbs01.dbf RMAN> list failure; Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 22 HIGH OPEN 17-DEC-23 One or more non-system datafiles are offline >> alert log 반응이 아직 없는데 rman 에도 없다 alter system checkpoint; alter system switch logfile; --Thread 1 advanced to log sequence 18 (LGWR switch) -- Current log# 3 seq# 18 mem# 0: /u01/app/oracle/oradata/ora11g/redo03.log > alert log --Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_m000_31171.trc: --ORA-01116: error in opening database file 7 --ORA-01110: data file 7: '/home/oracle/insa_tbs01.dbf' --ORA-27041: unable to open file --Linux Error: 2: No such file or directory --Additional information: 3 --Wed Jan 24 11:50:52 2024 --Checker run found 1 new persistent data failures |
|
| # 복구 | |
| RMAN> list failure; Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 45 HIGH OPEN 24-JAN-24 One or more non-system datafiles are missing 22 HIGH OPEN 17-DEC-23 One or more non-system datafiles are offline RMAN> list failure 45 detail; Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 45 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 45 Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 3285 HIGH OPEN 24-JAN-24 Datafile 7: '/home/oracle/insa_tbs01.dbf' is missing Impact: Some objects in tablespace INSA_TBS might be unavailable # 일괄 명령어 실행 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; 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 24-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_24/o1_mf_nnndf_TAG20240124T114145_lv0y79vj_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_24/o1_mf_nnndf_TAG20240124T114145_lv0y79vj_.bkp tag=TAG20240124T114145 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 24-JAN-24 datafile 7 switched to datafile copy input datafile copy RECID=6 STAMP=1159099606 file name=/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf 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 sql statement: alter tablespace insa_tbs online > run {문장; 문장; ...;} > 각 문장 끝에 ; 표시 > tab 공백 과다 사용 시 실행이 안되니 주의 SYS@ora11g> select count(*) from hr.dept_new; COUNT(*) ---------- 27 SYS@ora11g> SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 mb, b.archived, b.status FROM v$logfile a, v$log b WHERE a.group# = b.group# ORDER BY 1; GROUP# SEQUENCE# MEMBER MB ARCHIVED STATUS ---------- ---------- -------------------------------------------------- ---------- --------- ------------- 1 19 /u01/app/oracle/oradata/ora11g/redo01.log 50 NO CURRENT 2 17 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES INACTIVE 3 18 /u01/app/oracle/oradata/ora11g/redo03.log 50 YES INACTIVE SYS@ora11g> SELECT a.file#, a.name file_name, b.name tbs_name, a.status, a.checkpoint_change# FROM v$datafile a, v$tablespace b WHERE a.ts# = b.ts#; FILE# FILE_NAME TBS_NAME STATUS CHECKPOINT_CHANGE# ---------- -------------------------------------------------- ---------- ------------- ------------------ 1 /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM SYSTEM 2273055 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 2273055 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 2273055 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 2273055 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 2273055 6 /u01/app/oracle/oradata/ora11g/hrm_tbs01.dbf HRM_TBS ONLINE 2273055 7 /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf INSA_TBS ONLINE 2274183 FILE# FILE_NAME TBS_NAME STATUS CHECKPOINT_CHANGE# ------ -------------------------------------------------- ---------- ------------- ------------------ 1 /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM SYSTEM 2273055 drop tablespace insa_tbs including contents and datafiles; drop tablespace HRM_TBS including contents and datafiles; |
|
# 백업이후 지난 Log 정리 및 백업
SELECT a.file#, a.name file_name, b.name tbs_name, a.status, a.checkpoint_change#
FROM v$datafile a, v$tablespace b
WHERE a.ts# = b.ts#;
FILE# FILE_NAME TBS_NAME STATUS CHECKPOINT_CHANGE#
----- -------------------------------------------------- ---------- ------------- ------------------
1 /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM SYSTEM 2273055
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 2273055
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 2273055
4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 2273055
5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 2273055
>> 구조 변경 > 신규 백업 필수
RMAN> list backup;
... Backup Set 1, Control File Auto Backup 5
# 정책상 필요 없는 백업 정보, 아카이브 여부
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
-------------------- ------ ------------------ --------------------
Archive Log 12 16-JAN-24 /home/oracle/arch2/arch_1_54_1154915502.arc
Archive Log 14 16-JAN-24 /home/oracle/arch2/arch_1_1_1158372877.arc
Archive Log 13 16-JAN-24 /home/oracle/arch1/arch_1_1_1158372877.arc
Control File Copy 3 16-JAN-24 /home/oracle/backup/arch/hot_20240116/control01.ctl
Archive Log 15 16-JAN-24 /home/oracle/arch1/arch_1_2_1158372877.arc
Archive Log 16 16-JAN-24 /home/oracle/arch2/arch_1_2_1158372877.arc
Control File Copy 4 16-JAN-24 /home/oracle/backup/arch/hot_20240117/control01.ctl
Archive Log 18 16-JAN-24 /home/oracle/arch2/arch_1_3_1158372877.arc
...
Archive Log 36 23-JAN-24 /home/oracle/arch1/arch_1_10_1158511351.arc
Backup Set 2 24-JAN-24
Backup Piece 2 24-JAN-24 /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_24/o1_mf_s_1159093306_lv0skd3f_.bkp
Backup Set 3 24-JAN-24
Backup Piece 3 24-JAN-24 /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_24/o1_mf_s_1159097150_lv0x9h17_.bkp
Backup Set 4 24-JAN-24
Backup Piece 4 24-JAN-24 /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_24/o1_mf_nnndf_TAG20240124T114145_lv0y79vj_.bkp
Backup Set 5 24-JAN-24
Backup Piece 5 24-JAN-24 /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_24/o1_mf_s_1159098106_lv0y7c2b_.bkp
Datafile Copy 7 24-JAN-24 /home/oracle/insa_tbs01.dbf
# 정책상 필요 없는 백업 정보 삭제, 아카이브 정보 삭제
RMAN> delete obsolete;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Archive Log 3 16-JAN-24 /home/oracle/arch1/arch_1_50_1154915502.arc
Archive Log 4 16-JAN-24 /home/oracle/arch2/arch_1_50_1154915502.arc
...
Do you really want to delete the above objects (enter YES or NO)? y
RMAN-06207: WARNING: 26 objects could not be deleted for DISK channel(s) due
RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212: Object Type Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Archivelog /home/oracle/arch1/arch_1_50_1154915502.arc
RMAN-06214: Archivelog /home/oracle/arch2/arch_1_50_1154915502.arc
RMAN> list backup;
... Backup Set 1, Control File Auto Backup 1
# 컨트롤이 기억하고 있는 아카이브 정보와 실제 물리적으로 아카이브 정보를 체크
RMAN> crosscheck archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=192 device type=DISK
validation failed for archived log
archived log file name=/home/oracle/arch1/arch_1_50_1154915502.arc RECID=3 STAMP=1158369393
validation failed for archived log
archived log file name=/home/oracle/arch2/arch_1_50_1154915502.arc RECID=4 STAMP=1158369393
validation failed for archived log
...
# 필요없는 아카이브정보 출력
RMAN> list expired archivelog all;
List of Archived Log Copies for database with db_unique_name ORA11G
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
3 1 50 X 15-JAN-24
Name: /home/oracle/arch1/arch_1_50_1154915502.arc
4 1 50 X 15-JAN-24
Name: /home/oracle/arch2/arch_1_50_1154915502.arc
...
# 필요없는 아카이브정보 삭제
RMAN> delete expired archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=192 device type=DISK
List of Archived Log Copies for database with db_unique_name ORA11G
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
3 1 50 X 15-JAN-24
Name: /home/oracle/arch1/arch_1_50_1154915502.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_50_1154915502.arc RECID=3 STAMP=1158369393
deleted archived log
archived log file name=/home/oracle/arch2/arch_1_50_1154915502.arc RECID=4 STAMP=1158369393
...
Deleted 24 EXPIRED objects
SELECT name,sequence#,first_change#,first_time,next_change#,next_time
FROM v$archived_log;
NAME SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
--------------------------------------------- ---------- ------------- ----------- ------------ ---------
50 1146954 15-JAN-24 1155615 16-JAN-24
50 1146954 15-JAN-24 1155615 16-JAN-24
...
9 1237662 23-JAN-24 2139073 23-JAN-24
10 2139073 23-JAN-24 2151702 23-JAN-24
/home/oracle/arch1/arch_1_11_1158511351.arc 11 2151702 23-JAN-24 2169674 24-JAN-24
/home/oracle/arch1/arch_1_12_1158511351.arc 12 2169674 24-JAN-24 2169677 24-JAN-24
/home/oracle/arch1/arch_1_13_1158511351.arc 13 2169677 24-JAN-24 2169681 24-JAN-24
/home/oracle/arch1/arch_1_14_1158511351.arc 14 2169681 24-JAN-24 2172188 24-JAN-24
/home/oracle/arch1/arch_1_15_1158511351.arc 15 2172188 24-JAN-24 2172191 24-JAN-24
/home/oracle/arch1/arch_1_16_1158511351.arc 16 2172191 24-JAN-24 2172194 24-JAN-24
/home/oracle/arch1/arch_1_17_1158511351.arc 17 2172194 24-JAN-24 2173581 24-JAN-24
/home/oracle/arch1/arch_1_18_1158511351.arc 18 2173581 24-JAN-24 2273055 24-JAN-24
>>> 이름없는 건 삭제 된 상태 > 시간지나면 없어진다
RMAN> list backup;
... Backup Set 1, Control File Auto Backup 1
# 특정 번호의 백업셋 삭제
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_24/o1_mf_nnndf_TAG20240124T102100_lv0shxj2_.bkp
Do you really want to delete the above objects (enter YES or NO)? y
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_24/o1_mf_nnndf_TAG20240124T102100_lv0shxj2_.bkp RECID=1 STAMP=1159093261
Deleted 1 objects
RMAN> delete backupset 7;
...
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/undotbs01.dbf
4 0 /u01/app/oracle/oradata/ora11g/users01.dbf
5 0 /u01/app/oracle/oradata/ora11g/example01.dbf
RMAN> backup database;
...
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 24-JAN-24
...
Finished Control File and SPFILE Autobackup at 24-JAN-24
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8 Full 1.17G DISK 00:00:30 24-JAN-24
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20240124T142532
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_24/o1_mf_nnndf_TAG20240124T142532_lv17tdlq_.bkp
List of Datafiles in backup set 8
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 2282107 24-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf
2 Full 2282107 24-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 Full 2282107 24-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
4 Full 2282107 24-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf
5 Full 2282107 24-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9 Full 9.39M DISK 00:00:00 24-JAN-24
BP Key: 9 Status: AVAILABLE Compressed: NO Tag: TAG20240124T142608
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_24/o1_mf_s_1159107968_lv17vjom_.bkp
SPFILE Included: Modification time: 24-JAN-24
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 2282140 Ckp time: 24-JAN-24
>>> seq번호는 DB재생성하지 않는 이상 연속 카운트
>>> 전체 백업 > 이전 아카이브는 필요없다
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 3 16-JAN-24 /home/oracle/backup/arch/hot_20240116/control01.ctl
Control File Copy 4 16-JAN-24 /home/oracle/backup/arch/hot_20240117/control01.ctl
Archive Log 37 24-JAN-24 /home/oracle/arch1/arch_1_11_1158511351.arc
Archive Log 38 24-JAN-24 /home/oracle/arch1/arch_1_12_1158511351.arc
Archive Log 39 24-JAN-24 /home/oracle/arch1/arch_1_13_1158511351.arc
Archive Log 40 24-JAN-24 /home/oracle/arch1/arch_1_14_1158511351.arc
Archive Log 41 24-JAN-24 /home/oracle/arch1/arch_1_15_1158511351.arc
Archive Log 42 24-JAN-24 /home/oracle/arch1/arch_1_16_1158511351.arc
Archive Log 43 24-JAN-24 /home/oracle/arch1/arch_1_17_1158511351.arc
Archive Log 44 24-JAN-24 /home/oracle/arch1/arch_1_18_1158511351.arc
RMAN> delete obsolete;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Control File Copy 3 16-JAN-24 /home/oracle/backup/arch/hot_20240116/control01.ctl
Control File Copy 4 16-JAN-24 /home/oracle/backup/arch/hot_20240117/control01.ctl
Archive Log 37 24-JAN-24 /home/oracle/arch1/arch_1_11_1158511351.arc
...
Archive Log 44 24-JAN-24 /home/oracle/arch1/arch_1_18_1158511351.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_11_1158511351.arc RECID=37 STAMP=1159095206
deleted archived log
...
deleted archived log
archived log file name=/home/oracle/arch1/arch_1_18_1158511351.arc RECID=44 STAMP=1159099265
Deleted 8 objects
RMAN-06207: WARNING: 2 objects could not be deleted for DISK channel(s) due
RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212: Object Type Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Datafile Copy /home/oracle/backup/arch/hot_20240116/control01.ctl
RMAN-06214: Datafile Copy /home/oracle/backup/arch/hot_20240117/control01.ctl
[oracle@oracle ~]$ ls /home/oracle/arch1
>> no file.
SELECT name,sequence#,first_change#,first_time,next_change#,next_time
FROM v$archived_log;
NAME SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
-------------------------------------------------- ---------- ------------- ----------- ------------ ---------
50 1146954 15-JAN-24 1155615 16-JAN-24
...
18 2173581 24-JAN-24 2273055 24-JAN-24
42 rows selected.
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 3 16-JAN-24 /home/oracle/backup/arch/hot_20240116/control01.ctl
Control File Copy 4 16-JAN-24 /home/oracle/backup/arch/hot_20240117/control01.ctl
>> 오래된 컨트롤파일이다.
# 컨트롤이 기억하고 있는 copy 파일 체크
RMAN> crosscheck copy;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=192 device type=DISK
specification does not match any datafile copy in the repository
validation failed for control file copy
control file copy file name=/home/oracle/backup/arch/hot_20240117/control01.ctl RECID=4 STAMP=1158437182
validation failed for control file copy
control file copy file name=/home/oracle/backup/arch/hot_20240116/control01.ctl RECID=3 STAMP=1158375508
validation succeeded for archived log
archived log file name=/home/oracle/arch1/arch_1_19_1158511351.arc RECID=45 STAMP=1159108379
Crosschecked 3 objects
# 컨트롤이 기억하고 있는 copy 파일 삭제
RMAN> delete copy;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=192 device type=DISK
specification does not match any datafile copy in the repository
List of Control File Copies
===========================
Key S Completion Time Ckp SCN Ckp Time
------- - --------------- ---------- ---------------
4 X 16-JAN-24 1170857 16-JAN-24
Name: /home/oracle/backup/arch/hot_20240117/control01.ctl
Tag: TAG20240116T200622
3 X 16-JAN-24 1162886 16-JAN-24
Name: /home/oracle/backup/arch/hot_20240116/control01.ctl
Tag: TAG20240116T025828
List of Archived Log Copies for database with db_unique_name ORA11G
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
45 1 19 A 24-JAN-24
Name: /home/oracle/arch1/arch_1_19_1158511351.arc
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_20240117/control01.ctl RECID=4 STAMP=1158437182
deleted control file copy
control file copy file name=/home/oracle/backup/arch/hot_20240116/control01.ctl RECID=3 STAMP=1158375508
deleted archived log
archived log file name=/home/oracle/arch1/arch_1_19_1158511351.arc RECID=45 STAMP=1159108379
Deleted 3 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 >> 시스템 파일 손상
RMAN> list backup;
... 목록 확인
SELECT a.group#,b.sequence#,a.member,b.bytes/1024/1024 mb,b.archived,b.status,
b.first_time,b.first_change#,b.next_change#
FROM v$logfile a, v$log b WHERE a.group# = b.group# ORDER BY 1;
GROUP# SEQUENCE# MEMBER MB ARCHIVED STATUS
---------- ---------- -------------------------------------------------- ---------- --------- -------------
FIRST_TIME FIRST_CHANGE# NEXT_CHANGE#
----------- ------------- ------------
1 19 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES INACTIVE
24-JAN-24 2273055 2282563
2 20 /u01/app/oracle/oradata/ora11g/redo02.log 50 NO CURRENT
24-JAN-24 2282563 2.8147E+14
3 18 /u01/app/oracle/oradata/ora11g/redo03.log 50 YES INACTIVE
24-JAN-24 2173581 2273055
create table hr.emp_20240124 tablespace users as select * from hr.employees;
select count(*) from hr.emp_20240124;
COUNT(*)
----------
107
SYS@ora11g> alter system switch logfile;
SYS@ora11g> alter system switch logfile;
SYS@ora11g> alter system switch logfile;
SELECT a.group#,b.sequence#,a.member,b.bytes/1024/1024 mb,b.archived,b.status,
b.first_time,b.first_change#,b.next_change#
FROM v$logfile a, v$log b WHERE a.group# = b.group# ORDER BY 1;
GROUP# SEQUENCE# MEMBER MB ARCHIVED STATUS
------ ---------- -------------------------------------------------- ---------- --------- -------------
FIRST_TIME FIRST_CHANGE# NEXT_CHANGE#
----------- ------------- ------------
1 22 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES INACTIVE
24-JAN-24 2284615 2284618
2 23 /u01/app/oracle/oradata/ora11g/redo02.log 50 NO CURRENT
24-JAN-24 2284618 2.8147E+14
3 21 /u01/app/oracle/oradata/ora11g/redo03.log 50 YES INACTIVE
24-JAN-24 2284606 2284615
SELECT a.file#, a.name file_name, b.name tbs_name, a.status, a.checkpoint_change#
FROM v$datafile a, v$tablespace b WHERE a.ts# = b.ts#;
FILE# FILE_NAME TBS_NAME STATUS CHECKPOINT_CHANGE#
----- -------------------------------------------------- ---------- ------------- ------------------
1 /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM SYSTEM 2284618
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 2284618
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 2284618
4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 2284618
5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 2284618
# 장애 발생
! rm /u01/app/oracle/oradata/ora11g/system01.dbf
SYS@ora11g> alter system switch logfile;
--Thread 1 advanced to log sequence 24 (LGWR switch)
-- Current log# 3 seq# 24 mem# 0: /u01/app/oracle/oradata/ora11g/redo03.log
SYS@ora11g> conn hr/hr
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00604: error occurred at recursive SQL level 2
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00604: error occurred at recursive SQL level 2
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Warning: You are no longer connected to ORACLE.
@>
>> 연결 끊어짐
[oracle@oracle ~]$ sqlplus / as sysdba
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
# RMAN에서 DB 강제 종료
RMAN> shutdown abort
using target database control file instead of recovery catalog
Oracle instance shut down
>>> RMAN에서 shutdown, startup 가능하다
> alert log에 기록 됨
# RMAN에서 DB 시작 mount Mode
RMAN> startup mount
connected to target database (not started)
Oracle instance started
database mounted
RMAN> list failure;
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
3463 CRITICAL OPEN 24-JAN-24 System datafile 1: '/u01/app/oracle/oradata/ora11g/system01.dbf' is missing
22 HIGH OPEN 17-DEC-23 One or more non-system datafiles are offline
> CRITICAL : 심각한 문제
RMAN> list failure 3463 detail;
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
3463 CRITICAL OPEN 24-JAN-24 System datafile 1: '/u01/app/oracle/oradata/ora11g/system01.dbf' is missing
Impact: Database cannot be opened
>> 컨트롤 파일이 살아있어서 볼 수 있는거다.
RMAN> restore tablespace system;
Starting restore at 24-JAN-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ora11g/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_24/o1_mf_nnndf_TAG20240124T142532_lv17tdlq_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_24/o1_mf_nnndf_TAG20240124T142532_lv17tdlq_.bkp tag=TAG20240124T142532
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 24-JAN-24
RMAN> recover tablespace system;
Starting recover at 24-JAN-24
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 19 is already on disk as file /home/oracle/arch1/arch_1_19_1158511351.arc
archived log for thread 1 with sequence 20 is already on disk as file /home/oracle/arch1/arch_1_20_1158511351.arc
archived log for thread 1 with sequence 21 is already on disk as file /home/oracle/arch1/arch_1_21_1158511351.arc
archived log for thread 1 with sequence 22 is already on disk as file /home/oracle/arch1/arch_1_22_1158511351.arc
archived log for thread 1 with sequence 23 is already on disk as file /home/oracle/arch1/arch_1_23_1158511351.arc
archived log file name=/home/oracle/arch1/arch_1_19_1158511351.arc thread=1 sequence=7
archived log file name=/home/oracle/arch1/arch_1_20_1158511351.arc thread=1 sequence=8
archived log file name=/home/oracle/arch1/arch_1_21_1158511351.arc thread=1 sequence=9
archived log file name=/home/oracle/arch1/arch_1_22_1158511351.arc thread=1 sequence=10
archived log file name=/home/oracle/arch1/arch_1_23_1158511351.arc thread=1 sequence=11
media recovery complete, elapsed time: 00:00:01
Finished recover at 24-JAN-24
RMAN> alter database open;
>>>>>> 아카이브 19번 삭제되어 복구 안됨. 다시 하기 >> 지금은 콜드 복구
select status, instance_name from v$instance;
STATUS INSTANCE_NAME
------------ ----------------
OPEN ora11g
■ 모든 데이터 파일이 손상, 기존위치가 아닌 새로운 위치로 데이터파일 복구
RMAN> list backup;
SELECT tablespace_name,file_name,bytes/1024/1024 mb FROM dba_data_files;
TABLESPACE_NAME FILE_NAME MB
--------------- -------------------------------------------------- ----------
USERS /u01/app/oracle/oradata/ora11g/users01.dbf 5
UNDOTBS /u01/app/oracle/oradata/ora11g/undotbs01.dbf 14.25
SYSAUX /u01/app/oracle/oradata/ora11g/sysaux01.dbf 610
SYSTEM /u01/app/oracle/oradata/ora11g/system01.dbf 750
EXAMPLE /u01/app/oracle/oradata/ora11g/example01.dbf 331.25
! rm /u01/app/oracle/oradata/ora11g/*.dbf
*/
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
ORA-03113: end-of-file on communication channel
Process ID: 2756
Session ID: 16 Serial number: 1
conn / as sysdba
[oracle@oracle ~]$ rman target /
RMAN> startup mount
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 0 SYSTEM *** /u01/app/oracle/oradata/ora11g/system01.dbf
2 0 SYSAUX *** /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 0 UNDOTBS *** /u01/app/oracle/oradata/ora11g/undotbs01.dbf
4 0 USERS *** /u01/app/oracle/oradata/ora11g/users01.dbf
5 0 EXAMPLE *** /u01/app/oracle/oradata/ora11g/example01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/ora11g/temp01.dbf
[oracle@oracle ~]$ mkdir ora_data
[oracle@oracle ora_data]$ pwd
/home/oracle/ora_data
> 테이블스페이스 레벨 작업 시 sql '' 표현
RMAN>
run {
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 3 to '/home/oracle/ora_data/undotbs01.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';
restore database;
switch datafile all;
recover database;
alter database open;
}
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 24-JAN-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
datafile 1 is already restored to file /home/oracle/ora_data/system01.dbf
datafile 2 is already restored to file /home/oracle/ora_data/sysaux01.dbf
datafile 3 is already restored to file /home/oracle/ora_data/undotbs01.dbf
datafile 4 is already restored to file /home/oracle/ora_data/users01.dbf
datafile 5 is already restored to file /home/oracle/ora_data/example01.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 24-JAN-24
datafile 1 switched to datafile copy
input datafile copy RECID=15 STAMP=1159113629 file name=/home/oracle/ora_data/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=16 STAMP=1159113629 file name=/home/oracle/ora_data/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=17 STAMP=1159113629 file name=/home/oracle/ora_data/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=18 STAMP=1159113629 file name=/home/oracle/ora_data/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=19 STAMP=1159113629 file name=/home/oracle/ora_data/example01.dbf
Starting recover at 24-JAN-24
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file /home/oracle/arch1/arch_1_2_1158511351.arc
archived log for thread 1 with sequence 3 is already on disk as file /home/oracle/arch1/arch_1_3_1158511351.arc
archived log for thread 1 with sequence 4 is already on disk as file /home/oracle/arch1/arch_1_4_1158511351.arc
archived log file name=/home/oracle/arch1/arch_1_2_1158511351.arc thread=1 sequence=2
media recovery complete, elapsed time: 00:00:02
Finished recover at 24-JAN-24
database opened
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 *** /home/oracle/ora_data/system01.dbf
2 610 SYSAUX *** /home/oracle/ora_data/sysaux01.dbf
3 14 UNDOTBS *** /home/oracle/ora_data/undotbs01.dbf
4 5 USERS *** /home/oracle/ora_data/users01.dbf
5 331 EXAMPLE *** /home/oracle/ora_data/example01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/ora11g/temp01.dbf
[oracle@oracle ~]$ ls /home/oracle/ora_data
example01.dbf sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf
>>>>>>> 복구하자
RMAN> shutdown abort
Oracle instance shut down
RMAN> startup mount
connected to target database (not started)
Oracle instance started
database mounted
RMAN>
run {
set newname for datafile 1 to '/u01/app/oracle/oradata/ora11g/system01.dbf';
set newname for datafile 2 to '/u01/app/oracle/oradata/ora11g/sysaux01.dbf';
set newname for datafile 3 to '/u01/app/oracle/oradata/ora11g/undotbs01.dbf';
set newname for datafile 4 to '/u01/app/oracle/oradata/ora11g/users01.dbf';
set newname for datafile 5 to '/u01/app/oracle/oradata/ora11g/example01.dbf';
restore database;
switch datafile all;
recover database;
alter database open;}
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 24-JAN-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
datafile 1 is already restored to file /u01/app/oracle/oradata/ora11g/system01.dbf
datafile 2 is already restored to file /u01/app/oracle/oradata/ora11g/sysaux01.dbf
datafile 3 is already restored to file /u01/app/oracle/oradata/ora11g/undotbs01.dbf
datafile 4 is already restored to file /u01/app/oracle/oradata/ora11g/users01.dbf
datafile 5 is already restored to file /u01/app/oracle/oradata/ora11g/example01.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 24-JAN-24
datafile 1 switched to datafile copy
input datafile copy RECID=25 STAMP=1159113808 file name=/u01/app/oracle/oradata/ora11g/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=26 STAMP=1159113808 file name=/u01/app/oracle/oradata/ora11g/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=27 STAMP=1159113808 file name=/u01/app/oracle/oradata/ora11g/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=28 STAMP=1159113808 file name=/u01/app/oracle/oradata/ora11g/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=29 STAMP=1159113808 file name=/u01/app/oracle/oradata/ora11g/example01.dbf
Starting recover at 24-JAN-24
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 24-JAN-24
database opened
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
2 610 SYSAUX *** /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 14 UNDOTBS *** /u01/app/oracle/oradata/ora11g/undotbs01.dbf
4 5 USERS *** /u01/app/oracle/oradata/ora11g/users01.dbf
5 331 EXAMPLE *** /u01/app/oracle/oradata/ora11g/example01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/ora11g/temp01.dbf
SYS@ora11g> select status, instance_name from v$instance;
STATUS INSTANCE_NAME
------------- ----------------
OPEN ora11g
SYS@ora11g> SELECT a.file#, a.name file_name, b.name tbs_name, a.status, a.checkpoint_change#
FROM v$datafile a, v$tablespace b
WHERE a.ts# = b.ts#;
2 3
FILE# FILE_NAME TBS_NAME STATUS CHECKPOINT_CHANGE#
----- -------------------------------------------------- ---------- ------------- ------------------
1 /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM SYSTEM 1232917
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1232917
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1232917
4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1232917
5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1232917
SYS@ora11g> SELECT thread#,sequence#,archived,status,first_change#,first_time,next_change#,next_time
FROM v$log;
2
THREAD# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- --------- ------------- ------------- ----------- ------------ ---------
1 7 NO CURRENT 1232916 24-JAN-24 2.8147E+14
1 5 YES INACTIVE 1191744 24-JAN-24 1212485 24-JAN-24
1 6 YES INACTIVE 1212485 24-JAN-24 1232916 24-JAN-24
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
2 610 SYSAUX *** /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 14 UNDOTBS *** /u01/app/oracle/oradata/ora11g/undotbs01.dbf
4 5 USERS *** /u01/app/oracle/oradata/ora11g/users01.dbf
5 331 EXAMPLE *** /u01/app/oracle/oradata/ora11g/example01.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;
RMAN> delete backup;
RMAN> backup database;
Starting backup at 24-JAN-24
using channel ORA_DISK_1
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=00003 name=/u01/app/oracle/oradata/ora11g/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ora11g/users01.dbf
channel ORA_DISK_1: starting piece 1 at 24-JAN-24
channel ORA_DISK_1: finished piece 1 at 24-JAN-24
piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_24/o1_mf_nnndf_TAG20240124T162751_lv1gzqpn_.bkp tag=TAG20240124T162751 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 24-JAN-24
Starting Control File and SPFILE Autobackup at 24-JAN-24
piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_24/o1_mf_s_1159115306_lv1h0v46_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 24-JAN-24
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 1.16G DISK 00:00:34 24-JAN-24
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20240124T162751
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_24/o1_mf_nnndf_TAG20240124T162751_lv1gzqpn_.bkp
List of Datafiles in backup set 5
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1234835 24-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf
2 Full 1234835 24-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 Full 1234835 24-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
4 Full 1234835 24-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf
5 Full 1234835 24-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6 Full 9.36M DISK 00:00:01 24-JAN-24
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20240124T162826
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_24/o1_mf_s_1159115306_lv1h0v46_.bkp
SPFILE Included: Modification time: 24-JAN-24
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 1234870 Ckp time: 24-JAN-24
RMAN> list archivelog all;
List of Archived Log Copies for database with db_unique_name ORA11G
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
28 1 2 A 19-JAN-24
Name: /home/oracle/arch1/arch_1_2_1158511351.arc
29 1 3 A 24-JAN-24
Name: /home/oracle/arch1/arch_1_3_1158511351.arc
30 1 4 A 24-JAN-24
Name: /home/oracle/arch1/arch_1_4_1158511351.arc
31 1 5 A 24-JAN-24
Name: /home/oracle/arch1/arch_1_5_1158511351.arc
32 1 6 A 24-JAN-24
Name: /home/oracle/arch1/arch_1_6_1158511351.arc
RMAN> crosscheck archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
validation succeeded for archived log
archived log file name=/home/oracle/arch1/arch_1_2_1158511351.arc RECID=28 STAMP=1159112548
validation succeeded for archived log
archived log file name=/home/oracle/arch1/arch_1_3_1158511351.arc RECID=29 STAMP=1159112549
validation succeeded for archived log
archived log file name=/home/oracle/arch1/arch_1_4_1158511351.arc RECID=30 STAMP=1159113261
validation succeeded for archived log
archived log file name=/home/oracle/arch1/arch_1_5_1158511351.arc RECID=31 STAMP=1159113632
validation succeeded for archived log
archived log file name=/home/oracle/arch1/arch_1_6_1158511351.arc RECID=32 STAMP=1159113811
Crosschecked 5 objects
RMAN> list expired archivelog all;
specification does not match any archived log in the repository
↓
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
-------------------- ------ ------------------ --------------------
Archive Log 28 24-JAN-24 /home/oracle/arch1/arch_1_2_1158511351.arc
Archive Log 29 24-JAN-24 /home/oracle/arch1/arch_1_3_1158511351.arc
Archive Log 30 24-JAN-24 /home/oracle/arch1/arch_1_4_1158511351.arc
Archive Log 31 24-JAN-24 /home/oracle/arch1/arch_1_5_1158511351.arc
Datafile Copy 34 24-JAN-24 /home/oracle/ora_data/example01.dbf
Datafile Copy 33 24-JAN-24 /home/oracle/ora_data/users01.dbf
Datafile Copy 32 24-JAN-24 /home/oracle/ora_data/undotbs01.dbf
Datafile Copy 31 24-JAN-24 /home/oracle/ora_data/sysaux01.dbf
Datafile Copy 30 24-JAN-24 /home/oracle/ora_data/system01.dbf
Archive Log 32 24-JAN-24 /home/oracle/arch1/arch_1_6_1158511351.arc
SYS@ora11g> ! ls /home/oracle/arch1
arch_1_20_1158511351.arc arch_1_22_1158511351.arc arch_1_4_1158511351.arc
arch_1_21_1158511351.arc arch_1_23_1158511351.arc arch_1_5_1158511351.arc
arch_1_2_1158511351.arc arch_1_3_1158511351.arc arch_1_6_1158511351.arc
RMAN> delete obsolete;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Archive Log 28 24-JAN-24 /home/oracle/arch1/arch_1_2_1158511351.arc
Archive Log 29 24-JAN-24 /home/oracle/arch1/arch_1_3_1158511351.arc
Archive Log 30 24-JAN-24 /home/oracle/arch1/arch_1_4_1158511351.arc
Archive Log 31 24-JAN-24 /home/oracle/arch1/arch_1_5_1158511351.arc
Datafile Copy 34 24-JAN-24 /home/oracle/ora_data/example01.dbf
Datafile Copy 33 24-JAN-24 /home/oracle/ora_data/users01.dbf
Datafile Copy 32 24-JAN-24 /home/oracle/ora_data/undotbs01.dbf
Datafile Copy 31 24-JAN-24 /home/oracle/ora_data/sysaux01.dbf
Datafile Copy 30 24-JAN-24 /home/oracle/ora_data/system01.dbf
Archive Log 32 24-JAN-24 /home/oracle/arch1/arch_1_6_1158511351.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_2_1158511351.arc RECID=28 STAMP=1159112548
deleted archived log
archived log file name=/home/oracle/arch1/arch_1_3_1158511351.arc RECID=29 STAMP=1159112549
deleted archived log
archived log file name=/home/oracle/arch1/arch_1_4_1158511351.arc RECID=30 STAMP=1159113261
deleted archived log
archived log file name=/home/oracle/arch1/arch_1_5_1158511351.arc RECID=31 STAMP=1159113632
deleted datafile copy
datafile copy file name=/home/oracle/ora_data/example01.dbf RECID=34 STAMP=1159113808
deleted datafile copy
datafile copy file name=/home/oracle/ora_data/users01.dbf RECID=33 STAMP=1159113808
deleted datafile copy
datafile copy file name=/home/oracle/ora_data/undotbs01.dbf RECID=32 STAMP=1159113808
deleted datafile copy
datafile copy file name=/home/oracle/ora_data/sysaux01.dbf RECID=31 STAMP=1159113808
deleted datafile copy
datafile copy file name=/home/oracle/ora_data/system01.dbf RECID=30 STAMP=1159113808
deleted archived log
archived log file name=/home/oracle/arch1/arch_1_6_1158511351.arc RECID=32 STAMP=1159113811
Deleted 10 objects
SYS@ora11g> ! ls /home/oracle/arch1
arch_1_20_1158511351.arc arch_1_21_1158511351.arc arch_1_22_1158511351.arc arch_1_23_1158511351.arc
>> 삭제되었어야 하는데 왜 남음??
SYS@ora11g> ! /home/oracle/ora_data/
/bin/bash: /home/oracle/ora_data/: Is a directory
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> crosscheck archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
specification does not match any archived log in the repository
RMAN> crosscheck copy;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
specification does not match any archived log in the repository
RMAN> list expired archivelog all;
specification does not match any archived log in the repository
RMAN> list backup;
SELECT a.group#,b.sequence#,a.member,b.bytes/1024/1024 mb,b.archived,b.status,
b.first_time,b.first_change#,b.next_change#
FROM v$logfile a, v$log b
WHERE a.group# = b.group#
ORDER BY 1;
GROUP# SEQUENCE# MEMBER MB ARCHIVED STATUS
---------- ---------- -------------------------------------------------- ---------- --------- -------------
FIRST_TIME FIRST_CHANGE# NEXT_CHANGE#
----------- ------------- ------------
1 7 /u01/app/oracle/oradata/ora11g/redo01.log 50 NO CURRENT
24-JAN-24 1232916 2.8147E+14
2 5 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES INACTIVE
24-JAN-24 1191744 1212485
3 6 /u01/app/oracle/oradata/ora11g/redo03.log 50 YES INACTIVE
24-JAN-24 1212485 1232916
SYS@ora11g> alter system switch logfile;
--Thread 1 advanced to log sequence 8 (LGWR switch)
-- Current log# 2 seq# 8 mem# 0: /u01/app/oracle/oradata/ora11g/redo02.log
create table hr.rman tablespace users as select * from hr.employees;
SYS@ora11g> select count(*) from hr.rman;
COUNT(*)
----------
107
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
SYS@ora11g> SELECT a.group#,b.sequence#,a.member,b.bytes/1024/1024 mb,b.archived,b.status,
b.first_time,b.first_change#,b.next_change#
FROM v$logfile a, v$log b
WHERE a.group# = b.group#
ORDER BY 1;
2 3 4 5
GROUP# SEQUENCE# MEMBER MB ARCHIVED STATUS
---------- ---------- -------------------------------------------------- ---------- --------- -------------
FIRST_TIME FIRST_CHANGE# NEXT_CHANGE#
----------- ------------- ------------
1 10 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES INACTIVE
24-JAN-24 1236410 1236413
2 11 /u01/app/oracle/oradata/ora11g/redo02.log 50 NO CURRENT
24-JAN-24 1236413 2.8147E+14
3 9 /u01/app/oracle/oradata/ora11g/redo03.log 50 YES INACTIVE
24-JAN-24 1236406 1236410
SYS@ora11g> ! ls /home/oracle/arch1
arch_1_10_1158511351.arc arch_1_21_1158511351.arc arch_1_23_1158511351.arc arch_1_8_1158511351.arc
arch_1_20_1158511351.arc arch_1_22_1158511351.arc arch_1_7_1158511351.arc arch_1_9_1158511351.arc
RMAN> list expired archivelog all;
specification does not match any archived log in the repository
# 아카이브파일 백업
RMAN> backup as compressed backupset archivelog all delete input tag='archivelog backup';
Starting backup at 24-JAN-24
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=9 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=7 RECID=33 STAMP=1159116011
input archived log thread=1 sequence=8 RECID=34 STAMP=1159116146
input archived log thread=1 sequence=9 RECID=35 STAMP=1159116150
input archived log thread=1 sequence=10 RECID=36 STAMP=1159116153
input archived log thread=1 sequence=11 RECID=37 STAMP=1159116513
channel ORA_DISK_1: starting piece 1 at 24-JAN-24
channel ORA_DISK_1: finished piece 1 at 24-JAN-24
piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_24/o1_mf_annnn_ARCHIVELOG_BACKUP_lv1j6kt3_.bkp tag=ARCHIVELOG BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/home/oracle/arch1/arch_1_7_1158511351.arc RECID=33 STAMP=1159116011
archived log file name=/home/oracle/arch1/arch_1_8_1158511351.arc RECID=34 STAMP=1159116146
archived log file name=/home/oracle/arch1/arch_1_9_1158511351.arc RECID=35 STAMP=1159116150
archived log file name=/home/oracle/arch1/arch_1_10_1158511351.arc RECID=36 STAMP=1159116153
archived log file name=/home/oracle/arch1/arch_1_11_1158511351.arc RECID=37 STAMP=1159116513
Finished backup at 24-JAN-24
Starting Control File and SPFILE Autobackup at 24-JAN-24
piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_24/o1_mf_s_1159116514_lv1j6m72_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 24-JAN-24
RMAN> list backup;
List of Backup Sets
===================
...
Control File Included: Ckp SCN: 1234870 Ckp time: 24-JAN-24
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
7 2.74M DISK 00:00:00 24-JAN-24
BP Key: 7 Status: AVAILABLE Compressed: YES Tag: ARCHIVELOG BACKUP
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_24/o1_mf_annnn_ARCHIVELOG_BACKUP_lv1j6kt3_.bkp
List of Archived Logs in backup set 7
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 7 1232916 24-JAN-24 1236321 24-JAN-24
1 8 1236321 24-JAN-24 1236406 24-JAN-24
1 9 1236406 24-JAN-24 1236410 24-JAN-24
1 10 1236410 24-JAN-24 1236413 24-JAN-24
1 11 1236413 24-JAN-24 1236659 24-JAN-24
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8 Full 9.36M DISK 00:00:01 24-JAN-24
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20240124T164834
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_24/o1_mf_s_1159116514_lv1j6m72_.bkp
SPFILE Included: Modification time: 24-JAN-24
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 1236672 Ckp time: 24-JAN-24
RMAN> list backup of archivelog all;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
7 2.74M DISK 00:00:00 24-JAN-24
BP Key: 7 Status: AVAILABLE Compressed: YES Tag: ARCHIVELOG BACKUP
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_24/o1_mf_annnn_ARCHIVELOG_BACKUP_lv1j6kt3_.bkp
List of Archived Logs in backup set 7
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 7 1232916 24-JAN-24 1236321 24-JAN-24
1 8 1236321 24-JAN-24 1236406 24-JAN-24
1 9 1236406 24-JAN-24 1236410 24-JAN-24
1 10 1236410 24-JAN-24 1236413 24-JAN-24
1 11 1236413 24-JAN-24 1236659 24-JAN-24
>>> 백업 이후 아카이브는 손상되면 큰일 > 백업으로 보관하겠다
# 장애 발생
SYS@ora11g> ! rm /u01/app/oracle/oradata/ora11g/users01.dbf
SYS@ora11g> shutdown abort
SYS@ora11g> startup
ORACLE instance started.
...
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'
SYS@ora11g> select status, instance_name from v$instance;
STATUS INSTANCE_NAME
------------- ----------------
MOUNTED ora11g
SYS@ora11g> alter database datafile 4 offline;
SYS@ora11g> alter database open;
SYS@ora11g> select * from v$recover_file;
FILE# ONLINE ONLINE_STATUS ERROR CHANGE# TIME
----- ------- ------------- ------------------------------------------------------- ---------- ---------
4 OFFLINE OFFLINE FILE NOT FOUND 0
[oracle@oracle ~]$ rman target /
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
45 HIGH OPEN 24-JAN-24 One or more non-system datafiles are missing
22 HIGH OPEN 17-DEC-23 One or more non-system datafiles are offline
RMAN> list failure 45 detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
45 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 45
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
3661 HIGH OPEN 24-JAN-24 Datafile 4: '/u01/app/oracle/oradata/ora11g/users01.dbf' is missing
Impact: Some objects in tablespace USERS might be unavailable
RMAN> restore tablespace users;
Starting restore at 24-JAN-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
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_24/o1_mf_nnndf_TAG20240124T162751_lv1gzqpn_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_24/o1_mf_nnndf_TAG20240124T162751_lv1gzqpn_.bkp tag=TAG20240124T162751
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 24-JAN-24
RMAN> recover tablespace users;
Starting recover at 24-JAN-24
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 12 is already on disk as file /home/oracle/arch1/arch_1_12_1158511351.arc
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=7
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=9
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=10
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=11
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_24/o1_mf_annnn_ARCHIVELOG_BACKUP_lv1j6kt3_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_24/o1_mf_annnn_ARCHIVELOG_BACKUP_lv1j6kt3_.bkp tag=ARCHIVELOG BACKUP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/home/oracle/arch1/arch_1_7_1158511351.arc thread=1 sequence=7
archived log file name=/home/oracle/arch1/arch_1_8_1158511351.arc thread=1 sequence=8
archived log file name=/home/oracle/arch1/arch_1_9_1158511351.arc thread=1 sequence=9
archived log file name=/home/oracle/arch1/arch_1_10_1158511351.arc thread=1 sequence=10
media recovery complete, elapsed time: 00:00:00
Finished recover at 24-JAN-24
RMAN> sql 'alter tablespace users online';
SYS@ora11g> select * from v$recover_file;
no rows selected
SYS@ora11g> select count(*) from hr.rman;
COUNT(*)
----------
107
RMAN> list failure 22 detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
22 HIGH OPEN 17-DEC-23 One or more non-system datafiles are offline
Impact: See impact for individual child failures
List of child failures for parent failure ID 22
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
31 HIGH OPEN 17-DEC-23 Datafile 9: '/home/oracle/userdata/dw_tbs02.dbf' is offline
Impact: Some objects in tablespace DW_TBS might be unavailable
>>> 이거 없애보자
# RMAN에서 DB ON/OFF 가능
RMAN> shutdown
RMAN> startup
RMAN> restore
RMAN> recover
# OS에서 RMAN 접속
[oracle@oracle ~]$ rman target /
# 타겟DB의 테이블스페이스에 속한 데이터 확인
RMAN> report schema;
# 백업받은 목록
RMAN> list backup;
# 특정 백업본 보기
RMAN> list backup of tablespace insa_tbs;
# 백업받은 아카이브로그 모두 보기
RMAN> list backup of archivelog all;
# RMAN 구성 표시
RMAN> show all;
# Backup = Hot Backup = Online Backup
RMAN> backup database;
# Partial Backup : Tablespace Level
RMAN> backup tablespace insa_tbs;
# Archivelog Backup
RMAN> backup as compressed backupset archivelog all delete input tag='archivelog backup';
# 전체 백업셋 삭제
RMAN> delete backupset;
# 특정 번호의 백업셋 삭제
RMAN> delete backupset 1;
# 백업이 필요한 파일 확인
RMAN> report need backup;
# DB 오류 목록
RMAN> list failure;
# DB오류 ID로 상세정보 확인
RMAN> list failure 45 detail;
# 일괄 명령어 실행
RMAN> run {문장; 문장; ...;}
> 각 문장 끝에 ; 표시
> tab 공백 과다 사용 시 실행이 안되니 주의
# RMAN에서 SQL문 사용 문법
RMAN> sql '문장';
# 정책상 필요 없는 백업 정보, 아카이브 여부
RMAN> report obsolete;
# 정책상 필요 없는 백업 정보 삭제, 아카이브 정보 삭제
RMAN> delete obsolete;
# Restore : Tablespace Level, 최근 백업파일로 적용
RMAN> restore tablespace users;
# Recover : Tablespace Level, 최근 백업파일 적용
RMAN> recover tablespace users;
# 컨트롤이 기억하고 있는 아카이브 정보와 실제 물리적으로 아카이브 정보를 체크
RMAN> crosscheck archivelog all;
# 필요없는 아카이브정보 출력
RMAN> list expired archivelog all;
# 필요없는 아카이브정보 삭제
RMAN> delete expired archivelog all;
# 컨트롤이 기억하고 있는 copy 파일 체크
RMAN> crosscheck copy;
# 컨트롤이 기억하고 있는 copy 파일 삭제
RMAN> delete copy;
# sqlplus 유저@접속DB 보이기
set sqlprompt "_USER'@'_CONNECT_IDENTIFIER> "
SYS@ora11g>
Backup and Recovery User's Guide
https://docs.oracle.com/en/database/oracle/oracle-database/19/bradv/index.html
Backup and Recovery User's Guide
docs.oracle.com
'Oracle 11g > Backup & Recovery' 카테고리의 다른 글
| 74 Backup&Recovery, RMAN (0) | 2024.01.26 |
|---|---|
| 73 Backup&Recovery, RMAN (0) | 2024.01.25 |
| 71 Backup&Recovery, Export & Import, Data Pump (0) | 2024.01.23 |
| 70 Backup&Recovery, Log Miner, Clone DB, Export & Import (0) | 2024.01.22 |
| 69 Backup&Recovery, Archive Log Mode (0) | 2024.01.19 |