2017. 11. 24. 16:33 오라클
dbms_backup_restore.resetcfilesection 이용 controlfile에서 백업정보 삭제하는 방법
controlfile에서 백업정보 삭제하는 방법
dbms_backup_restore.resetcfilesection procdure 관련 Type별 값 조회
============================================================
버전별로 살짝 틀림.
11.2.0.4 의 경우
set lines 120
col type for A25
select rownum,t.* from v$controlfile_record_section t;
해서 나온 결과로 지우고자 하는 항목 확인!!
ROWNUM TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------- ------------------------------ ----------- ------------- ------------ ----------- ---------- ----------
1 DATABASE 316 1 1 0 0 0
2 CKPT PROGRESS 8180 11 0 0 0 0
3 REDO THREAD 256 8 1 0 0 0
4 REDO LOG 72 16 7 0 0 60
5 DATAFILE 520 100 6 0 0 326
6 FILENAME 524 2298 15 0 0 0
7 TABLESPACE 68 100 7 0 0 21
8 TEMPORARY FILENAME 56 100 1 0 0 364
9 RMAN CONFIGURATION 1108 50 0 0 0 0
10 LOG HISTORY 56 292 292 20 19 311
11 OFFLINE RANGE 200 163 0 0 0 0
12 ARCHIVED LOG 584 187 187 29 28 430
13 BACKUP SET 40 409 0 0 0 0
14 BACKUP PIECE 736 200 0 0 0 0
15 BACKUP DATAFILE 200 245 0 0 0 0
16 BACKUP REDOLOG 76 215 0 0 0 0
17 DATAFILE COPY 736 200 9 1 9 9
18 BACKUP CORRUPTION 44 371 0 0 0 0
19 COPY CORRUPTION 40 409 0 0 0 0
20 DELETED OBJECT 20 818 190 1 190 190
21 PROXY COPY 928 246 0 0 0 0
22 BACKUP SPFILE 124 131 0 0 0 0
23 DATABASE INCARNATION 56 292 1 1 1 1
24 FLASHBACK LOG 84 2048 0 0 0 0
25 RECOVERY DESTINATION 180 1 0 0 0 0
26 INSTANCE SPACE RESERVATION 28 1055 1 0 0 0
27 REMOVABLE RECOVERY FILES 32 1000 0 0 0 0
28 RMAN STATUS 116 141 65 1 65 65
29 THREAD INSTANCE NAME MAPPING 80 8 8 0 0 0
30 MTTR 100 8 1 0 0 0
31 DATAFILE HISTORY 568 57 0 0 0 0
32 STANDBY DATABASE MATRIX 400 31 31 0 0 0
33 GUARANTEED RESTORE POINT 212 2048 0 0 0 0
34 RESTORE POINT 212 2083 0 0 0 0
35 DATABASE BLOCK CORRUPTION 80 8384 0 0 0 0
36 ACM OPERATION 104 64 6 0 0 0
37 FOREIGN ARCHIVED LOG 604 1002 0 0 0 0
해당되는 NUMBER 를 넣어서 삭제
SQL>exec dbms_backup_restore.resetcfilesection(12);
SQL>exec dbms_backup_restore.resetcfilesection(13);
SQL>exec dbms_backup_restore.resetcfilesection(14);
SQL>exec dbms_backup_restore.resetcfilesection(21);
================================================================
12.2.0.1 의 경우 (37번 이후로 몇개 더 생김)
set lines 120
col type for A25
select rownum,t.* from v$controlfile_record_section t;
ROWNUM TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID CON_ID
---------- ------------------------- ----------- ------------- ------------ ----------- ---------- ---------- ----------
1 DATABASE 316 1 1 0 0 0 0
2 CKPT PROGRESS 8180 35 0 0 0 0 0
3 REDO THREAD 256 32 2 0 0 2 0
4 REDO LOG 72 192 4 0 0 4 0
5 DATAFILE 520 1024 5 0 0 20 0
6 FILENAME 524 4674 10 0 0 0 0
7 TABLESPACE 180 1024 6 0 0 6 0
8 TEMPORARY FILENAME 56 1024 1 0 0 1 0
9 RMAN CONFIGURATION 1108 50 0 0 0 0 0
10 LOG HISTORY 56 292 9 1 9 9 0
11 OFFLINE RANGE 200 1063 0 0 0 0 0
12 ARCHIVED LOG 584 28 0 0 0 0 0
13 BACKUP SET 96 1022 0 0 0 0 0
14 BACKUP PIECE 780 1006 0 0 0 0 0
15 BACKUP DATAFILE 200 1063 0 0 0 0 0
16 BACKUP REDOLOG 76 430 0 0 0 0 0
17 DATAFILE COPY 736 1000 0 0 0 0 0
18 BACKUP CORRUPTION 44 1115 0 0 0 0 0
19 COPY CORRUPTION 40 1227 0 0 0 0 0
20 DELETED OBJECT 20 818 0 0 0 0 0
21 PROXY COPY 928 1004 0 0 0 0 0
22 BACKUP SPFILE 124 131 0 0 0 0 0
23 DATABASE INCARNATION 56 292 1 1 1 1 0
24 FLASHBACK LOG 84 2048 0 0 0 0 0
25 RECOVERY DESTINATION 180 1 0 0 0 0 0
26 INSTANCE SPACE RESERVATION 28 1055 1 0 0 0 0
27 REMOVABLE RECOVERY FILES 32 1000 0 0 0 0 0
28 RMAN STATUS 116 141 0 0 0 0 0
29 THREAD INSTANCE NAME MAPPING 80 32 32 0 0 0 0
30 MTTR 100 32 2 0 0 0 0
31 DATAFILE HISTORY 568 57 0 0 0 0 0
32 STANDBY DATABASE MATRIX 400 128 128 0 0 0 0
33 GUARANTEED RESTORE POINT 256 2048 0 0 0 0 0
34 RESTORE POINT 256 2108 0 0 0 0 0
35 DATABASE BLOCK CORRUPTION 80 8384 0 0 0 0 0
36 ACM OPERATION 104 64 10 0 0 0 0
37 FOREIGN ARCHIVED LOG 604 1002 0 0 0 0 0
38 PDB RECORD 780 10 0 0 0 0 0
39 AUXILIARY DATAFILE COPY 584 128 0 0 0 0 0
40 MULTI INSTANCE REDO APPLY 556 1 0 0 0 0 0
41 PDBINC RECORD 144 113 0 0 0 0 0
42 TABLESPACE KEY HISTORY 108 151 0 0 0 0 0
