고정 헤더 영역
상세 컨텐츠
본문
1. RMAN 에서 백업본 과감하게 지워버리기
RMAN> delete backup;
2. RMAN 환경 보기
RMAN> show all;
그런데 너무 많으므로
RMAN> show maxsetsize;
maxsetsize이라는 글자가 들어가는 configure 만 보기:
딱 하나 나온다.
더보기
RMAN configuration parameters for database with db_unique_name ORA11G are:
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
maxsetsize를 unlimited로 설정해놓으면 : 데이터 백업을 할 때
하나의 백업셋 안에 전체 데이터파일을 다 받을 수 있다.
그런데 700m 짜리 하나의 테이프 장비 안에 백업을 받으려고 한다면?
그 테이프 장비 안에는 700m 보다 큰 값이 들어갈 수 없다.
그래서 이럴 때는 maxsetsize 의 값을 조정해서
테이프 안에 들어갈 수 있는 파일의 최대 크기를 제한해야 한다.
★ maxsetsize 값 조정하기
1. maxsetsize 600m 로 조정
RMAN> configure maxsetsize to 600m;
하나의 테이프 장비 안에 들어갈 수 있는 최대 파일의 크기를 600m 까지로 제한
더보기
new RMAN configuration parameters:
CONFIGURE MAXSETSIZE TO 600 M;
new RMAN configuration parameters are successfully stored
2. 조정이 제대로 되었는지 확인해보기
RMAN> show maxsetsize;
더보기
RMAN> show maxsetsize;
RMAN configuration parameters for database with db_unique_name ORA11G are:
CONFIGURE MAXSETSIZE TO 600 M; --- 600m 로 잘 바뀌어 있다.
3. 백업 새로 받아서 백업이 어떻게 되는지 확인해보기
RMAN> backup database;
(모두가 다 함께 오류 발생:
하나의 테이프 장치 안에 600m 까지만 넣겠다고 제한을 해 놨는데
시스템 데이터파일이 600m 보다 더 커서 오류 발생함)
더보기
RMAN> backup database;
Starting backup at 29-JAN-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=177 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 01/29/2024 15:34:03
RMAN-06183: datafile or datafile copy /u01/app/oracle/oradata/ora11g/system01.dbf (file number 1) larger than MAXSETSIZE
RMAN> report schema;
테이블스페이스와 그 각각의 테이블스페이스에 속한 데이터파일 및 그 크기 보기:
시스템 데이터파일의 크기가 750m 라는데 그걸 600m 짜리 테이프 장치 안에 넣으려고 했으니
당연히 오류가 발생한다.
그리고 보니까 sysaux 도 문제겠네... 쟤는 또 730m 짜리니까...
더보기
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 730 SYSAUX *** /u01/app/oracle/oradata/ora11g/sysaux01.dbf
4 8 USERS *** /u01/app/oracle/oradata/ora11g/users01.dbf
5 338 EXAMPLE *** /u01/app/oracle/oradata/ora11g/example01.dbf
6 40 UNDOTBS *** /u01/app/oracle/oradata/ora11g/undotbs01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 29 TEMP 32767 /u01/app/oracle/oradata/ora11g/temp01.dbf
RMAN> configure maxsetsize to 800m;
그럼 이번에는 800m 로 바꿔 놓고
더보기
RMAN> configure maxsetsize to 800m;
old RMAN configuration parameters:
CONFIGURE MAXSETSIZE TO 600 M;
new RMAN configuration parameters:
CONFIGURE MAXSETSIZE TO 800 M;
new RMAN configuration parameters are successfully stored
RMAN> backup database;
다시 백업을 받으면 :
system 데이터파일(750m)과 sysaux 데이터파일(730m)은 각각 따로따로 백업본이 만들어지고
나머지 3개 데이터파일(8+338+40 = 386 < 800) 은 뭉뚱그려서 백업본이 만들어지게 된다.
list backup; 명령어로 확인해보자.
더보기
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
41 Full 644.84M DISK 00:00:15 30-JAN-24
BP Key: 61 Status: AVAILABLE Compressed: NO Tag: TAG20240130T071857
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T071857_lvj92kj1_.bkp
List of Datafiles in backup set 41
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 2225986 30-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf
41번 백업본: 시스템파일 백업본 (1개째)
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
42 Full 574.24M DISK 00:00:13 30-JAN-24
BP Key: 62 Status: AVAILABLE Compressed: NO Tag: TAG20240130T071857
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T071857_lvj930rq_.bkp
List of Datafiles in backup set 42
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 2226001 30-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
42번 백업본: sysaux 파일 백업본 (2개째)
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
43 Full 78.59M DISK 00:00:04 30-JAN-24
BP Key: 63 Status: AVAILABLE Compressed: NO Tag: TAG20240130T071857
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T071857_lvj93j1d_.bkp
List of Datafiles in backup set 43
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 Full 2226007 30-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf
5 Full 2226007 30-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf
6 Full 2226007 30-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
43번 백업본: users, example, undotbs 파일 백업본 (3개째)
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
44 Full 9.95M DISK 00:00:01 30-JAN-24
BP Key: 64 Status: AVAILABLE Compressed: NO Tag: TAG20240130T071934
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_30/o1_mf_s_1159600774_lvj93q6g_.bkp
SPFILE Included: Modification time: 30-JAN-24
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 2226015 Ckp time: 30-JAN-24
44번 백업본: 컨트롤파일 백업본 (4개째)
총 4개의 백업본이 만들어진 것을 볼 수 있다.
만약 백업할 때 백업셋 크기를 제한해서 받고 싶다면 이렇게
maxsetsize 의 크기를 조정해서 백업을 받으면 된다.
4. 다시 원래의 설정으로 되돌아가고 싶다면: clear
RMAN> configure maxsetsize clear;
clear 명령어를 이용하면 다시 원래 값으로 되돌아갈 수 있다.
더보기
RMAN> configure maxsetsize clear;
old RMAN configuration parameters:
CONFIGURE MAXSETSIZE TO 800 M;
RMAN configuration parameters are successfully reset to default value
RMAN> show maxsetsize;
되돌아갔는지 확인해보기: 다시 unlimited 상태로 돌아와 있다.
더보기
RMAN> show maxsetsize;
RMAN configuration parameters for database with db_unique_name ORA11G are:
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
5. 백업을 하면서도 백업셋의 크기를 제한할 수 있다.
RMAN> delete backup;
결과를 보기 편하도록 일단 백업본 지우고
RMAN> backup database maxsetsize 800m;
백업셋 최대 크기를 800m 로 제한해서 백업 받기
RMAN> list backup;
백업이 어떻게 받아졌는지 확인해보기:
아까와 똑같이 백업본 4개가 만들어져 있는 것을 볼 수 있다.
더보기
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
45 Full 644.84M DISK 00:00:15 30-JAN-24
BP Key: 65 Status: AVAILABLE Compressed: NO Tag: TAG20240130T073952
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T073952_lvjb9rm0_.bkp
List of Datafiles in backup set 45
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 2227272 30-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
46 Full 574.24M DISK 00:00:13 30-JAN-24
BP Key: 66 Status: AVAILABLE Compressed: NO Tag: TAG20240130T073952
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T073952_lvjbbko8_.bkp
List of Datafiles in backup set 46
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 2227283 30-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
47 Full 78.12M DISK 00:00:04 30-JAN-24
BP Key: 67 Status: AVAILABLE Compressed: NO Tag: TAG20240130T073952
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T073952_lvjbc0vf_.bkp
List of Datafiles in backup set 47
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 Full 2227289 30-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf
5 Full 2227289 30-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf
6 Full 2227289 30-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
48 Full 9.95M DISK 00:00:01 30-JAN-24
BP Key: 68 Status: AVAILABLE Compressed: NO Tag: TAG20240130T074039
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_30/o1_mf_s_1159602039_lvjbc83r_.bkp
SPFILE Included: Modification time: 30-JAN-24
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 2227349 Ckp time: 30-JAN-24
RMAN> delete backup;
Do you really want to delete the above objects (enter YES or NO)? yes
백업본 지우기
RMAN> list backup;
지워졌는지 확인하기: specification does not match any backup in the repository
잘 지워져 있다.
그렇지만 위의 방법은 하나의 테이프 장비 안에 받을 수 있는 파일의 크기를
데이터파일의 크기보다 크게 설정해놓는 것일 뿐...
데이터파일이 만약 1G 정도로 클 경우에는 테이프 장비 크기를 1G 보다 크게 할 수 없어서 문제가 발생한다.
(테이프장비의 크기가 600m 로 설정되어 있는데
그 안에 750m 짜리 시스템 데이터파일의 백업본을 받을 수 없었던 것처럼)
이럴 때는 물리적 백업피스의 크기를 조각조각 쪼개어서 백업받는 방법을 생각해볼 수 있다.
★ maxpiecesize 값 조정
백업피스의 크기를 조정해야 할 때 쓰이는 환경변수는 show all 했을 때는 보이지 않는다.
RMAN> show all;
더보기
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 ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
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
눈 씻고 찾아봐도 없음.
1.
RMAN> configure channel device type disk maxpiecesize 600m;
채널 디바이스 타입 디스크라는 곳에 백업을 받는데
백업피스 하나당 600m 를 넘지 못하도록 설정
더보기
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
그런데 설정해놓고 나서 다시 show all; 하면
그 때는 보여준다. (뭐하자는 거야...)
RMAN> show all;
더보기
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 ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
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 CHANNEL DEVICE TYPE DISK MAXPIECESIZE 600 M;
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
2. 백업받기
RMAN> backup database;
이제 하나의 백업셋 안에 백업피스가 600m 짜리로 분할해서 만들어지게 된다.
(750m 짜리 시스템 데이터파일과 730m 짜리 sysaux 데이터파일은 2개로 분할되어 만들어지게 된다)
RMAN> list backupset;
백업본 확인해보기: 백업셋이 하나만 만들어짐
더보기
RMAN> list backupset;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
49 Full 1.27G DISK 00:00:43 30-JAN-24
List of Datafiles in backup set 49
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 2228145 30-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf
2 Full 2228145 30-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
4 Full 2228145 30-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf
5 Full 2228145 30-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf
6 Full 2228145 30-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
Backup Set Copy #1 of backup set 49
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:43 30-JAN-24 NO TAG20240130T075912
List of Backup Pieces for backup set 49 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
69 1 AVAILABLE /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T075912_lvjcg11z_.bkp
70 2 AVAILABLE /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T075912_lvjcgt9c_.bkp
71 3 AVAILABLE /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T075912_lvjch9cv_.bkp
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
50 Full 9.95M DISK 00:00:01 30-JAN-24
BP Key: 72 Status: AVAILABLE Compressed: NO Tag: TAG20240130T075956
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_30/o1_mf_s_1159603196_lvjchf8j_.bkp
SPFILE Included: Modification time: 30-JAN-24
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 2228166 Ckp time: 30-JAN-24
데이터파일 백업셋 1개, 컨트롤파일 백업셋 1개
데이터파일 백업셋 1개 안에는 모든 데이터파일이 들어가 있지만
백업피스의 크기를 제한해두었기 때문에 물리적 백업피스는 총 3개 만들어지게 된다.
3. 백업본 지우기
RMAN> delete backup;
Do you really want to delete the above objects (enter YES or NO)? yes
(18:30)
★ 작업형을 이용해서 백업받기 - 데이터베이스 레벨과 테이블스페이스 레벨
1. 데이터베이스 레벨로 백업받기
RMAN>
run {
allocate channel c1 device type disk maxpiecesize 100m;
backup database;
}
(QUESTION. 이거 뭐 한 거야?
allocate channel c1 device type disk maxpiecesize 100m? 이게 뭔 소리지?
백업피스 크기를 100m 로 제한한다는 소리 같긴 한데 앞에 나온 allocate 는 뭐고 또 c1 은 뭐야?
그냥 있는 그대로 해석해보면 c1 을 백업받는 채널로 할당하고,
거기다 백업피스당 하나 크기를 100m 로 제한해서 백업받겠단 소린가?)
(ANSWER. 그렇다.)
RMAN> list backup;
백업본 확인하기: 백업피스들이 조각조각 쪼개져 있는 것이 보인다. (13개)
(QUESTION. 왜 13개밖에 안돼? system 이 750이고 sysaux 가 730이고 그 외 나머지 것들이 386 이니까
750+730+386=1866... 해서 19개 만들어져야 될 텐데?
설마 안 쓰는 공간은 안가져와서 그런 건가?)
(ANSWER. 그렇다.)
더보기
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
51 Full 1.27G DISK 00:00:46 30-JAN-24
List of Datafiles in backup set 51
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 2229247 30-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf
2 Full 2229247 30-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
4 Full 2229247 30-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf
5 Full 2229247 30-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf
6 Full 2229247 30-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
Backup Set Copy #1 of backup set 51
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:46 30-JAN-24 NO TAG20240130T081407
List of Backup Pieces for backup set 51 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
73 1 AVAILABLE /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T081407_lvjd9zp9_.bkp
74 2 AVAILABLE /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T081407_lvjdb2rm_.bkp
75 3 AVAILABLE /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T081407_lvjdb5m3_.bkp
76 4 AVAILABLE /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T081407_lvjdb8mz_.bkp
77 5 AVAILABLE /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T081407_lvjdbhn2_.bkp
78 6 AVAILABLE /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T081407_lvjdbpnr_.bkp
79 7 AVAILABLE /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T081407_lvjdbsoj_.bkp
80 8 AVAILABLE /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T081407_lvjdbwp3_.bkp
81 9 AVAILABLE /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T081407_lvjdbzph_.bkp
82 10 AVAILABLE /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T081407_lvjdc2q3_.bkp
83 11 AVAILABLE /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T081407_lvjdc5q9_.bkp
84 12 AVAILABLE /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T081407_lvjdc8r8_.bkp
85 13 AVAILABLE /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T081407_lvjdccrg_.bkp
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
52 Full 9.95M DISK 00:00:01 30-JAN-24
BP Key: 86 Status: AVAILABLE Compressed: NO Tag: TAG20240130T081454
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_30/o1_mf_s_1159604094_lvjdch3b_.bkp
SPFILE Included: Modification time: 30-JAN-24
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 2229273 Ckp time: 30-JAN-24
백업본 지우기
RMAN> delete backup;
Do you really want to delete the above objects (enter YES or NO)? yes
2. 테이블스페이스 레벨로 백업받기
RMAN>
run {
allocate channel c1 device type disk maxpiecesize 100m;
backup tablespace system;
}
특정한 테이블스페이스 레벨로 백업하기
(20:55)
채널 하나를 띄워서 시스템 데이터파일을 로드시켜서
100m 짜리 백업피스를 여러 개 만듦 (750m 짜리 파일이면 백업피스 8개가 만들어진다)
RMAN> list backup of tablespace system;
시스템 데이터파일의 백업본만 얼른 확인해보면 : 7개
(QUESTION. 이것도 왜 7개야... 750M 짜리인데 8개가 만들어져야 맞지...
이것도 설마 750M 중에서 쓰는 공간만 백업해서 그런 건가?)
(ANSWER. 그렇다.)
더보기
RMAN> list backup of tablespace system;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
53 Full 644.98M DISK 00:00:19 30-JAN-24
List of Datafiles in backup set 53
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 2230072 30-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf
Backup Set Copy #1 of backup set 53
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:19 30-JAN-24 NO TAG20240130T083259
List of Backup Pieces for backup set 53 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
87 1 AVAILABLE /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T083259_lvjffclz_.bkp
88 2 AVAILABLE /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T083259_lvjffglv_.bkp
89 3 AVAILABLE /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T083259_lvjffkmb_.bkp
90 4 AVAILABLE /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T083259_lvjffnmw_.bkp
91 5 AVAILABLE /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T083259_lvjffqno_.bkp
92 6 AVAILABLE /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T083259_lvjffto4_.bkp
93 7 AVAILABLE /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T083259_lvjffxoh_.bkp
3. 다시 원래대로 되돌아가기
RMAN> configure channel device type disk clear;
더보기
RMAN> configure channel device type disk clear;
old RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 600 M;
old RMAN configuration parameters are successfully deleted
4. 백업본 지우기
RMAN> delete backup;
Do you really want to delete the above objects (enter YES or NO)? yes
RMAN> list backup;
specification does not match any backup in the repository
★ 이미지 카피 백업 image copy backup
1. 이미지 카피 백업받기
RMAN> backup as copy database;
이미지 카피 백업을 받으면: 파일 하나씩 카피가 만들어진다.
RMAN> list backup;
백업본 정보 확인: 컨트롤파일의 백업 정보밖에는 안 보인다.
더보기
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
55 Full 9.95M DISK 00:00:01 30-JAN-24
BP Key: 95 Status: AVAILABLE Compressed: NO Tag: TAG20240130T085121
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_30/o1_mf_s_1159606281_lvjght1t_.bkp
SPFILE Included: Modification time: 30-JAN-24
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 2230806 Ckp time: 30-JAN-24
이미지 카피 방식의 백업본을 확인하려면: list backup 이 아니라 list copy 로 확인해야 한다.
RMAN> list 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
------- ---- - --------------- ---------- ---------------
9 1 A 30-JAN-24 2230774 30-JAN-24
Name: /u01/app/oracle/fast_recovery_area/ORA11G/datafile/o1_mf_system_lvjgg871_.dbf
Tag: TAG20240130T085032
10 2 A 30-JAN-24 2230784 30-JAN-24
Name: /u01/app/oracle/fast_recovery_area/ORA11G/datafile/o1_mf_sysaux_lvjgh1cy_.dbf
Tag: TAG20240130T085032
13 4 A 30-JAN-24 2230800 30-JAN-24
Name: /u01/app/oracle/fast_recovery_area/ORA11G/datafile/o1_mf_users_lvjghrs6_.dbf
Tag: TAG20240130T085032
11 5 A 30-JAN-24 2230790 30-JAN-24
Name: /u01/app/oracle/fast_recovery_area/ORA11G/datafile/o1_mf_example_lvjghjj0_.dbf
Tag: TAG20240130T085032
12 6 A 30-JAN-24 2230799 30-JAN-24
Name: /u01/app/oracle/fast_recovery_area/ORA11G/datafile/o1_mf_undotbs_lvjghqoy_.dbf
Tag: TAG20240130T085032
RMAN> list copy of tablespace system;
이미지 카피 백업받아놓은 것 중에 시스템 테이블스페이스 백업이 있는지 보기
더보기
RMAN> list copy of tablespace system;
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
9 1 A 30-JAN-24 2230774 30-JAN-24
Name: /u01/app/oracle/fast_recovery_area/ORA11G/datafile/o1_mf_system_lvjgg871_.dbf
Tag: TAG20240130T085032
RMAN> backup as copy current controlfile;
컨트롤파일도 이미지 카피로 백업받기
더보기
RMAN> backup as copy current controlfile;
Starting backup at 29-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_TAG20240129T160237_lvgmdfsm_.ctl tag=TAG20240129T160237 RECID=7 STAMP=1159545758
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 29-JAN-24
Starting Control File and SPFILE Autobackup at 29-JAN-24
piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_29/o1_mf_s_1159545758_lvgmdh7c_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 29-JAN-24
RMAN> list copy of controlfile;
컨트롤파일의 이미지 카피 확인
(QUESTION. 근데 그러면 아까 list backup 으로 확인했을 때 나온 컨트롤파일 백업본은 뭐야?
걔는 그냥 지가 자동으로 받아졌던 거야?)
(ANSWER. 그렇다. autobackup 으로 받아졌던 것)
더보기
RMAN> list copy of controlfile;
List of Control File Copies
===========================
Key S Completion Time Ckp SCN Ckp Time
------- - --------------- ---------- ---------------
14 A 30-JAN-24 2230906 30-JAN-24
Name: /u01/app/oracle/fast_recovery_area/ORA11G/controlfile/o1_mf_TAG20240130T085356_lvjgnnb1_.ctl
Tag: TAG20240130T085356
2. 데이터파일 없애고 장애 유발하기 (이미지 카피 백업본으로 복구해 보자.)
2-1. RMAN 말고 퍼티 sys 계정에서 DB 내리기
SYS@ora11g> shutdown immediate
2-2. 모든 데이터파일 없애고 장애 유발시키기
SYS@ora11g> ! rm /u01/app/oracle/oradata/ora11g/*.dbf
SYS@ora11g> ! ls /u01/app/oracle/oradata/ora11g/*.dbf
ls: cannot access /u01/app/oracle/oradata/ora11g/*.dbf: No such file or directory
2-3. DB 올리기
SYS@ora11g> startup
오류 발생 : 마운트 단계까지만 올라옴
더보기
SYS@ora11g> startup
ORACLE instance started.
Total System Global Area 711430144 bytes
Fixed Size 1367004 bytes
Variable Size 448791588 bytes
Database Buffers 255852544 bytes
Redo Buffers 5419008 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf'
3. 백업으로 복구 작업
3-1. RMAN 으로 새로 접속하기
RMAN> exit
[oracle@oracle ~]$ rman target /
3-2. 이미지 카피로 받아놓은 백업을 써서 복구작업해 보자.
3-2-1.
RMAN> restore database;
이렇게 하면 : 데이터파일들 하나하나에 백업본을 적용하게 된다.
(QUESTION. 이게 무슨 말이야? 원래 백업할 때도 이렇게 하는 거 아니었어?
원래는 그럼 백업셋을 통째로 다 데이터파일에 적용했다는 뜻이야?)
(ANSWER. 파일 하나짜리 백업셋으로 복구할 때는 RMAN 이 알아서 필요한 것만 꺼내 썼는데
이미지 카피는 데이터파일 5개 중에서 필요한 걸 꺼내 쓰는 것,
백업 방식은 똑같은데 파일 개수의 차이라고 보면 된다)
더보기
RMAN> restore database;
Starting restore at 30-JAN-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=171 device type=DISK
channel ORA_DISK_1: restoring datafile 00001
input datafile copy RECID=9 STAMP=1159606247 file name=/u01/app/oracle/fast_recovery_area/ORA11G/datafile/o1_mf_system_lvjgg871_.dbf
destination for restore of datafile 00001: /u01/app/oracle/oradata/ora11g/system01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00001
output file name=/u01/app/oracle/oradata/ora11g/system01.dbf RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00002
input datafile copy RECID=10 STAMP=1159606271 file name=/u01/app/oracle/fast_recovery_area/ORA11G/datafile/o1_mf_sysaux_lvjgh1cy_.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/ora11g/sysaux01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00002
output file name=/u01/app/oracle/oradata/ora11g/sysaux01.dbf RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00004
input datafile copy RECID=13 STAMP=1159606280 file name=/u01/app/oracle/fast_recovery_area/ORA11G/datafile/o1_mf_users_lvjghrs6_.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/ora11g/users01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00004
output file name=/u01/app/oracle/oradata/ora11g/users01.dbf RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00005
input datafile copy RECID=11 STAMP=1159606278 file name=/u01/app/oracle/fast_recovery_area/ORA11G/datafile/o1_mf_example_lvjghjj0_.dbf
destination for restore of datafile 00005: /u01/app/oracle/oradata/ora11g/example01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00005
output file name=/u01/app/oracle/oradata/ora11g/example01.dbf RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00006
input datafile copy RECID=12 STAMP=1159606280 file name=/u01/app/oracle/fast_recovery_area/ORA11G/datafile/o1_mf_undotbs_lvjghqoy_.dbf
destination for restore of datafile 00006: /u01/app/oracle/oradata/ora11g/undotbs01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00006
output file name=/u01/app/oracle/oradata/ora11g/undotbs01.dbf RECID=0 STAMP=0
Finished restore at 30-JAN-24
3-2-2. 복구한 데이터파일에 리두와 아카이브파일 부어넣기
RMAN> recover database;
더보기
RMAN> recover database;
Starting recover at 30-JAN-24
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 30-JAN-24
3-2-3. DB 열기
RMAN> alter database open;
database opened
복구작업 끝!
DB 가 문제없이 복구되었는지 확인해 보자.
SYS@ora11g> select count(*) from hr.employees;
COUNT(*)
----------
107
테이블 조회 이상 없다. (DB 복구 완료된 것)
★ 이미지 카피 백업본을 쓰면 좋은 점
1. 실습용 테이블스페이스 만들기
SYS@ora11g> create tablespace insa_tbs datafile '/home/oracle/insa_tbs01.dbf' size 10m;
Tablespace created.
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#;
테이블스페이스가 만들어졌는지 확인해보기
SYS@ora11g> create table hr.emp_insa tablespace insa_tbs as select * from hr.employees;
Table created.
조금 전에 만든 테이블스페이스에 테이블 생성
다시 RMAN 으로 가서
RMAN> report schema;
여기서도 테이블스페이스가 만들어졌는지 확인해보기
더보기
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 730 SYSAUX *** /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 10 INSA_TBS *** /home/oracle/insa_tbs01.dbf
4 8 USERS *** /u01/app/oracle/oradata/ora11g/users01.dbf
5 338 EXAMPLE *** /u01/app/oracle/oradata/ora11g/example01.dbf
6 40 UNDOTBS *** /u01/app/oracle/oradata/ora11g/undotbs01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 29 TEMP 32767 /u01/app/oracle/oradata/ora11g/temp01.dbf
2. 실습용 테이블스페이스 백업받기
RMAN> report need 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
---- ----- -----------------------------------------------------
3 0 /home/oracle/insa_tbs01.dbf
테이블스페이스 백업받기
RMAN> backup as copy datafile 3 format '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf';
인사 테이블스페이스를 백업받는데, 백업받는 위치를 /u01/app/oracle/oradata/ora11g/ 여기로 지정
더보기
RMAN> backup as copy datafile 3 format '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf';
Starting backup at 29-JAN-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/home/oracle/insa_tbs01.dbf
output file name=/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf tag=TAG20240129T161449 RECID=8 STAMP=1159546489
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 29-JAN-24
Starting Control File and SPFILE Autobackup at 29-JAN-24
piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_29/o1_mf_s_1159546490_lvgn3c22_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 29-JAN-24
RMAN> list copy;
이미지 카피 백업본 확인하기
더보기
RMAN> list copy;
specification does not match any archived log in the repository
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
9 1 A 30-JAN-24 2230774 30-JAN-24
Name: /u01/app/oracle/fast_recovery_area/ORA11G/datafile/o1_mf_system_lvjgg871_.dbf
Tag: TAG20240130T085032
10 2 A 30-JAN-24 2230784 30-JAN-24
Name: /u01/app/oracle/fast_recovery_area/ORA11G/datafile/o1_mf_sysaux_lvjgh1cy_.dbf
Tag: TAG20240130T085032
15 3 A 30-JAN-24 2232922 30-JAN-24
Name: /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf
Tag: TAG20240130T091613
13 4 A 30-JAN-24 2230800 30-JAN-24
Name: /u01/app/oracle/fast_recovery_area/ORA11G/datafile/o1_mf_users_lvjghrs6_.dbf
Tag: TAG20240130T085032
11 5 A 30-JAN-24 2230790 30-JAN-24
Name: /u01/app/oracle/fast_recovery_area/ORA11G/datafile/o1_mf_example_lvjghjj0_.dbf
Tag: TAG20240130T085032
12 6 A 30-JAN-24 2230799 30-JAN-24
Name: /u01/app/oracle/fast_recovery_area/ORA11G/datafile/o1_mf_undotbs_lvjghqoy_.dbf
Tag: TAG20240130T085032
List of Control File Copies
===========================
Key S Completion Time Ckp SCN Ckp Time
------- - --------------- ---------- ---------------
14 A 30-JAN-24 2230906 30-JAN-24
Name: /u01/app/oracle/fast_recovery_area/ORA11G/controlfile/o1_mf_TAG20240130T085356_lvjgnnb1_.ctl
Tag: TAG20240130T085356
RMAN> list datafilecopy all;
컨트롤파일은 빼고 데이터파일들의 이미지 카피 백업본만 확인해보기
더보기
RMAN> list datafilecopy all;
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
9 1 A 30-JAN-24 2230774 30-JAN-24
Name: /u01/app/oracle/fast_recovery_area/ORA11G/datafile/o1_mf_system_lvjgg871_.dbf
Tag: TAG20240130T085032
10 2 A 30-JAN-24 2230784 30-JAN-24
Name: /u01/app/oracle/fast_recovery_area/ORA11G/datafile/o1_mf_sysaux_lvjgh1cy_.dbf
Tag: TAG20240130T085032
15 3 A 30-JAN-24 2232922 30-JAN-24
Name: /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf
Tag: TAG20240130T091613
13 4 A 30-JAN-24 2230800 30-JAN-24
Name: /u01/app/oracle/fast_recovery_area/ORA11G/datafile/o1_mf_users_lvjghrs6_.dbf
Tag: TAG20240130T085032
11 5 A 30-JAN-24 2230790 30-JAN-24
Name: /u01/app/oracle/fast_recovery_area/ORA11G/datafile/o1_mf_example_lvjghjj0_.dbf
Tag: TAG20240130T085032
12 6 A 30-JAN-24 2230799 30-JAN-24
Name: /u01/app/oracle/fast_recovery_area/ORA11G/datafile/o1_mf_undotbs_lvjghqoy_.dbf
Tag: TAG20240130T085032
그러면 insa_tbs 의 실제 데이터파일은
/home/oracle/insa_tbs01.dbf 여기 있게 되고
백업본은
/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf 여기 있게 되는데
3. 실습용 테이블스페이스 깨뜨려서 장애 유발하기
SYS@ora11g> ! rm /home/oracle/insa_tbs01.dbf
백업본 말고 실제 데이터파일 깨뜨려서 장애 유발
SYS@ora11g> ! ls /home/oracle/insa_tbs01.dbf
ls: cannot access /home/oracle/insa_tbs01.dbf: No such file or directory
SYS@ora11g> shutdown immediate
ORA-03113: end-of-file on communication channel
Process ID: 21812
Session ID: 9 Serial number: 3
DB 내려보기: 채널이 아예 끊어짐.
SYS@ora11g> conn / as sysdba
Connected to an idle instance.
다시 접속 : DB 가 내려가 있다고 나온다.
DB 올리기 (올라갈 리가 없지...)
SYS@ora11g> startup
ORACLE instance started.
Total System Global Area 711430144 bytes
Fixed Size 1367004 bytes
Variable Size 448791588 bytes
Database Buffers 255852544 bytes
Redo Buffers 5419008 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'
4. 문제가 되는 데이터파일만 얼른 오프라인으로 떨어뜨리고
SYS@ora11g> alter database datafile 3 offline;
Database altered.
5. DB 열기
SYS@ora11g> alter database open;
Database altered.
6. 테이블스페이스 확인
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#;
insa_tbs 가 리커버가 필요하다고 나온다.
다시 RMAN 으로 새로 접속하기
[oracle@oracle ~]$ rman target /
RMAN> list datafilecopy all;
백업본 확인하기
더보기
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
------- ---- - --------------- ---------- ---------------
9 1 A 30-JAN-24 2230774 30-JAN-24
Name: /u01/app/oracle/fast_recovery_area/ORA11G/datafile/o1_mf_system_lvjgg871_.dbf
Tag: TAG20240130T085032
10 2 A 30-JAN-24 2230784 30-JAN-24
Name: /u01/app/oracle/fast_recovery_area/ORA11G/datafile/o1_mf_sysaux_lvjgh1cy_.dbf
Tag: TAG20240130T085032
15 3 A 30-JAN-24 2232922 30-JAN-24
Name: /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf
Tag: TAG20240130T091613
13 4 A 30-JAN-24 2230800 30-JAN-24
Name: /u01/app/oracle/fast_recovery_area/ORA11G/datafile/o1_mf_users_lvjghrs6_.dbf
Tag: TAG20240130T085032
11 5 A 30-JAN-24 2230790 30-JAN-24
Name: /u01/app/oracle/fast_recovery_area/ORA11G/datafile/o1_mf_example_lvjghjj0_.dbf
Tag: TAG20240130T085032
12 6 A 30-JAN-24 2230799 30-JAN-24
Name: /u01/app/oracle/fast_recovery_area/ORA11G/datafile/o1_mf_undotbs_lvjghqoy_.dbf
Tag: TAG20240130T085032
데이터파일 위치를 백업파일이 있는 위치로 바꿔서 복구해주고 싶은데
저 위치를 컨트롤파일은 모르므로 바꿔주는 작업이 필요하다.
RMAN> switch datafile 3 to copy;
datafile 3 switched to datafile copy "/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf"
기존 파일이 아닌 백업파일이 있는 위치로 데이터파일 복원
(그냥 restore datafile 3 하면 기존 파일 위치로 데이터파일을 복원하게 된다.)
RMAN> recover datafile 3;
더보기
RMAN> recover datafile 3;
Starting recover at 29-JAN-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=182 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 29-JAN-24
아카이브 찾아서 리두 적용까지 다 해준다.
RMAN> sql 'alter database datafile 3 online';
sql statement: alter database datafile 3 online
RMAN> report schema;
확인해보면: insa_tbs 데이터파일의 위치가 바뀌어 있는 것이 보인다.
더보기
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 730 SYSAUX *** /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 10 INSA_TBS *** /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf
4 8 USERS *** /u01/app/oracle/oradata/ora11g/users01.dbf
5 338 EXAMPLE *** /u01/app/oracle/oradata/ora11g/example01.dbf
6 40 UNDOTBS *** /u01/app/oracle/oradata/ora11g/undotbs01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 29 TEMP 32767 /u01/app/oracle/oradata/ora11g/temp01.dbf
SYS@ora11g> select count(*) from hr.employees;
COUNT(*)
----------
107
복구가 잘 되었는지 확인해보기
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#;
SYS@ora11g> drop tablespace insa_tbs including contents and datafiles;
Tablespace dropped.
테이블스페이스 지우기
RMAN> list copy;
이미지 카피 확인
RMAN> delete copy;
Do you really want to delete the above objects (enter YES or NO)? yes
이미지 카피 받아놓은 것들 다 지워버리기
RMAN> list backup;
RMAN> delete backup;
Do you really want to delete the above objects (enter YES or NO)? yes
백업도 다 지워버리기
이미지 카피로도 백업을 받을 수 있지만 백업셋으로 백업받는 것이 훨씬 낫다.
(이미지 카피로 백업할 경우 사용하지 않는 공간까지 백업하기 때문)
RMAN> backup as compressed backupset database;
백업 새로 받기
