ORACLE/ORACLE_Backup&Recovery
rman advise를 이용한 복구 테스트
minies
2023. 6. 18. 20:43
반응형
테스트 시나리오
- RMAN 백업
- 블록 확인
- RMAN 복구
테스트
- rman 백업을 진행한다.
RMAN> backup database;
Starting backup at 18-JUN-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=29 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=00002 name=/app/oracle/oradata/ORA19C/user_ts/DAMIN_IDX_TS.dbf
input datafile file number=00005 name=/app/oracle/oradata/ORA19C/user_ts/DAMIN_DATA_TS.dbf
input datafile file number=00001 name=/app/oracle/oradata/ORA19C/system01.dbf
input datafile file number=00003 name=/app/oracle/oradata/ORA19C/sysaux01.dbf
input datafile file number=00004 name=/app/oracle/oradata/ORA19C/undotbs01.dbf
input datafile file number=00008 name=/app/oracle/oradata/ORA19C/user_ts/DAMIN3_DATA_TS.dbf
input datafile file number=00009 name=/app/oracle/oradata/ORA19C/user_ts/DAMIN3_IDX_TS.dbf
input datafile file number=00007 name=/app/oracle/oradata/ORA19C/users01.dbf
channel ORA_DISK_1: starting piece 1 at 18-JUN-23
channel ORA_DISK_1: finished piece 1 at 18-JUN-23
piece handle=/app/oracle/rman/ORA19C_1s1v1lq9_1_1_20230618.bk tag=TAG20230618T190024 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 18-JUN-23
Starting Control File and SPFILE Autobackup at 18-JUN-23
piece handle=/home/oracle/rman/ctl_c-1186485125-20230618-00 comment=NONE
Finished Control File and SPFILE Autobackup at 18-JUN-23
RMAN> list backup;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
36 42.64M DISK 00:00:00 19-APR-23
BP Key: 36 Status: AVAILABLE Compressed: NO Tag: TAG20230419T181644
Piece Name: /app/oracle/rman/ORA19C_1f1q0occ_1_1_20230419.bk
List of Archived Logs in backup set 36
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 9 2629623 13-APR-23 2650572 18-APR-23
1 10 2650572 18-APR-23 2650626 18-APR-23
1 12 2665578 18-APR-23 2667057 18-APR-23
1 13 2667057 18-APR-23 2668489 18-APR-23
1 14 2668489 18-APR-23 2668544 18-APR-23
1 15 2668544 18-APR-23 2668586 18-APR-23
1 16 2668586 18-APR-23 2778586 19-APR-23
1 17 2778586 19-APR-23 2786074 19-APR-23
1 18 2786074 19-APR-23 2786107 19-APR-23
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
37 7.79M DISK 00:00:00 19-APR-23
BP Key: 37 Status: AVAILABLE Compressed: NO Tag: TAG20230419T181644
Piece Name: /app/oracle/rman/ORA19C_1g1q0ocd_1_1_20230419.bk
List of Archived Logs in backup set 37
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 1 2786089 19-APR-23 2794359 19-APR-23
1 2 2794359 19-APR-23 2794386 19-APR-23
1 3 2794386 19-APR-23 2795248 19-APR-23
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
38 5.50M DISK 00:00:00 19-APR-23
BP Key: 38 Status: AVAILABLE Compressed: NO Tag: TAG20230419T181644
Piece Name: /app/oracle/rman/ORA19C_1h1q0oce_1_1_20230419.bk
List of Archived Logs in backup set 38
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 1 2794402 19-APR-23 2799003 19-APR-23
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
39 Full 1.22G DISK 00:00:01 19-APR-23
BP Key: 39 Status: AVAILABLE Compressed: NO Tag: TAG20230419T181647
Piece Name: /app/oracle/rman/ORA19C_1i1q0ocf_1_1_20230419.bk
List of Datafiles in backup set 39
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 2799021 19-APR-23 NO /app/oracle/oradata/ORA19C/system01.dbf
2 Full 2799021 19-APR-23 NO /app/oracle/oradata/ORA19C/user_ts/DAMIN_IDX_TS.dbf
3 Full 2799021 19-APR-23 NO /app/oracle/oradata/ORA19C/sysaux01.dbf
4 Full 2799021 19-APR-23 NO /app/oracle/oradata/ORA19C/undotbs01.dbf
5 Full 2799021 19-APR-23 NO /app/oracle/oradata/ORA19C/user_ts/DAMIN_DATA_TS.dbf
7 Full 2799021 19-APR-23 NO /app/oracle/oradata/ORA19C/users01.dbf
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
40 7.50K DISK 00:00:00 19-APR-23
BP Key: 40 Status: AVAILABLE Compressed: NO Tag: TAG20230419T181650
Piece Name: /app/oracle/rman/ORA19C_1j1q0oci_1_1_20230419.bk
List of Archived Logs in backup set 40
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 2 2799003 19-APR-23 2799036 19-APR-23
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
41 Full 10.52M DISK 00:00:00 19-APR-23
BP Key: 41 Status: AVAILABLE Compressed: NO Tag: TAG20230419T181651
Piece Name: /app/oracle/rman/db_ctl_c-1186485125-20230419-05
SPFILE Included: Modification time: 19-APR-23
SPFILE db_unique_name: ORA19C
Control File Included: Ckp SCN: 2799051 Ckp time: 19-APR-23
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
42 42.64M DISK 00:00:00 19-APR-23
BP Key: 42 Status: AVAILABLE Compressed: NO Tag: TAG20230419T182330
Piece Name: /home/oracle/rman/database_20230419_1l1q0op2_1_1
List of Archived Logs in backup set 42
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 9 2629623 13-APR-23 2650572 18-APR-23
1 10 2650572 18-APR-23 2650626 18-APR-23
1 12 2665578 18-APR-23 2667057 18-APR-23
1 13 2667057 18-APR-23 2668489 18-APR-23
1 14 2668489 18-APR-23 2668544 18-APR-23
1 15 2668544 18-APR-23 2668586 18-APR-23
1 16 2668586 18-APR-23 2778586 19-APR-23
1 17 2778586 19-APR-23 2786074 19-APR-23
1 18 2786074 19-APR-23 2786107 19-APR-23
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
43 7.79M DISK 00:00:00 19-APR-23
BP Key: 43 Status: AVAILABLE Compressed: NO Tag: TAG20230419T182330
Piece Name: /home/oracle/rman/database_20230419_1m1q0op3_1_1
List of Archived Logs in backup set 43
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 1 2786089 19-APR-23 2794359 19-APR-23
1 2 2794359 19-APR-23 2794386 19-APR-23
1 3 2794386 19-APR-23 2795248 19-APR-23
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
44 5.72M DISK 00:00:00 19-APR-23
BP Key: 44 Status: AVAILABLE Compressed: NO Tag: TAG20230419T182330
Piece Name: /home/oracle/rman/database_20230419_1n1q0op4_1_1
List of Archived Logs in backup set 44
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 1 2794402 19-APR-23 2799003 19-APR-23
1 2 2799003 19-APR-23 2799036 19-APR-23
1 3 2799036 19-APR-23 2799923 19-APR-23
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
45 Full 1.22G DISK 00:00:01 19-APR-23
BP Key: 45 Status: AVAILABLE Compressed: NO Tag: TAG20230419T182333
Piece Name: /app/oracle/rman/ORA19C_1o1q0op5_1_1_20230419.bk
List of Datafiles in backup set 45
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 2799948 19-APR-23 NO /app/oracle/oradata/ORA19C/system01.dbf
2 Full 2799948 19-APR-23 NO /app/oracle/oradata/ORA19C/user_ts/DAMIN_IDX_TS.dbf
3 Full 2799948 19-APR-23 NO /app/oracle/oradata/ORA19C/sysaux01.dbf
4 Full 2799948 19-APR-23 NO /app/oracle/oradata/ORA19C/undotbs01.dbf
5 Full 2799948 19-APR-23 NO /app/oracle/oradata/ORA19C/user_ts/DAMIN_DATA_TS.dbf
7 Full 2799948 19-APR-23 NO /app/oracle/oradata/ORA19C/users01.dbf
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
46 7.50K DISK 00:00:00 19-APR-23
BP Key: 46 Status: AVAILABLE Compressed: NO Tag: TAG20230419T182336
Piece Name: /home/oracle/rman/database_20230419_1p1q0op8_1_1
List of Archived Logs in backup set 46
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 4 2799923 19-APR-23 2799959 19-APR-23
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
47 Full 10.52M DISK 00:00:00 19-APR-23
BP Key: 47 Status: AVAILABLE Compressed: NO Tag: TAG20230419T182337
Piece Name: /home/oracle/rman/ctl_c-1186485125-20230419-06
SPFILE Included: Modification time: 19-APR-23
SPFILE db_unique_name: ORA19C
Control File Included: Ckp SCN: 2799974 Ckp time: 19-APR-23
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
48 Full 10.52M DISK 00:00:00 04-JUN-23
BP Key: 48 Status: AVAILABLE Compressed: NO Tag: TAG20230604T223452
Piece Name: /home/oracle/rman/ctl_c-1186485125-20230604-00
SPFILE Included: Modification time: 04-JUN-23
SPFILE db_unique_name: ORA19C
Control File Included: Ckp SCN: 3002627 Ckp time: 04-JUN-23
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
49 Full 1.26G DISK 00:00:11 18-JUN-23
BP Key: 49 Status: AVAILABLE Compressed: NO Tag: TAG20230618T190024
Piece Name: /app/oracle/rman/ORA19C_1s1v1lq9_1_1_20230618.bk
List of Datafiles in backup set 49
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 3116246 18-JUN-23 NO /app/oracle/oradata/ORA19C/system01.dbf
2 Full 3116246 18-JUN-23 NO /app/oracle/oradata/ORA19C/user_ts/DAMIN_IDX_TS.dbf
3 Full 3116246 18-JUN-23 NO /app/oracle/oradata/ORA19C/sysaux01.dbf
4 Full 3116246 18-JUN-23 NO /app/oracle/oradata/ORA19C/undotbs01.dbf
5 Full 3116246 18-JUN-23 NO /app/oracle/oradata/ORA19C/user_ts/DAMIN_DATA_TS.dbf
7 Full 3116246 18-JUN-23 NO /app/oracle/oradata/ORA19C/users01.dbf
8 Full 3116246 18-JUN-23 NO /app/oracle/oradata/ORA19C/user_ts/DAMIN3_DATA_TS.dbf
9 Full 3116246 18-JUN-23 NO /app/oracle/oradata/ORA19C/user_ts/DAMIN3_IDX_TS.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
50 Full 10.52M DISK 00:00:00 18-JUN-23
BP Key: 50 Status: AVAILABLE Compressed: NO Tag: TAG20230618T190040
Piece Name: /home/oracle/rman/ctl_c-1186485125-20230618-00
SPFILE Included: Modification time: 18-JUN-23
SPFILE db_unique_name: ORA19C
Control File Included: Ckp SCN: 3116279 Ckp time: 18-JUN-23
2. DATABASE에 문제가 없는지 검증한다.
- Blocks Failing가 있다면 block corruption이 일어난 것이다.
RMAN> validate database;
Starting validate at 18-JUN-23
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00002 name=/app/oracle/oradata/ORA19C/user_ts/DAMIN_IDX_TS.dbf
input datafile file number=00005 name=/app/oracle/oradata/ORA19C/user_ts/DAMIN_DATA_TS.dbf
input datafile file number=00001 name=/app/oracle/oradata/ORA19C/system01.dbf
input datafile file number=00003 name=/app/oracle/oradata/ORA19C/sysaux01.dbf
input datafile file number=00004 name=/app/oracle/oradata/ORA19C/undotbs01.dbf
input datafile file number=00008 name=/app/oracle/oradata/ORA19C/user_ts/DAMIN3_DATA_TS.dbf
input datafile file number=00009 name=/app/oracle/oradata/ORA19C/user_ts/DAMIN3_IDX_TS.dbf
input datafile file number=00007 name=/app/oracle/oradata/ORA19C/users01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:15
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 OK 0 17793 117769 3116173
File Name: /app/oracle/oradata/ORA19C/system01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 81353
Index 0 13227
Other 0 5387
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2 OK 0 41 131073 3005482
File Name: /app/oracle/oradata/ORA19C/user_ts/DAMIN_IDX_TS.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 6
Other 0 131025
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3 OK 0 18231 80722 3116173
File Name: /app/oracle/oradata/ORA19C/sysaux01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 9398
Index 0 6940
Other 0 46071
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 OK 0 1 44168 3116173
File Name: /app/oracle/oradata/ORA19C/undotbs01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 44159
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5 OK 0 1 131082 3081070
File Name: /app/oracle/oradata/ORA19C/user_ts/DAMIN_DATA_TS.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 65
Index 0 19
Other 0 130987
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7 OK 0 245 992 3081102
File Name: /app/oracle/oradata/ORA19C/users01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 151
Index 0 52
Other 0 512
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
8 OK 0 12673 12800 2997276
File Name: /app/oracle/oradata/ORA19C/user_ts/DAMIN3_DATA_TS.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 127
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
9 OK 0 12673 12800 2997336
File Name: /app/oracle/oradata/ORA19C/user_ts/DAMIN3_IDX_TS.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 127
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
including current SPFILE in backup set
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE OK 0 2
Control File OK 0 666
Finished validate at 18-JUN-23
3. fail 난 곳을 확인한다.
RMAN> list failure;
Database Role: PRIMARY
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
1482 HIGH OPEN 18-JUN-23 One or more non-system datafiles are corrupt
- fail난 부분 관련 상세정보를 확인한다.
RMAN> list failure 1482 detail;
Database Role: PRIMARY
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
1482 HIGH OPEN 18-JUN-23 One or more non-system datafiles are corrupt
Impact: See impact for individual child failures
List of child failures for parent failure ID 1482
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
1485 HIGH OPEN 18-JUN-23 Datafile 8: '/app/oracle/oradata/ORA19C/user_ts/DAMIN3_DATA_TS.dbf' is corrupt
Impact: Some objects in tablespace DAMIN3_DATA_TS might be unavailable
4. 일반적으로 advise를 이용하면 복구 코드를 제공해준다.
RMAN> advise failure;
Database Role: PRIMARY
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
1482 HIGH OPEN 18-JUN-23 One or more non-system datafiles are corrupt
Impact: See impact for individual child failures
List of child failures for parent failure ID 1482
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
1485 HIGH OPEN 18-JUN-23 Datafile 8: '/app/oracle/oradata/ORA19C/user_ts/DAMIN3_DATA_TS.dbf' is corrupt
Impact: Some objects in tablespace DAMIN3_DATA_TS might be unavailable
analyzing automatic repair options; this may take some time
using channel ORA_DISK_1
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
no manual actions available
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 8
Strategy: The repair includes complete media recovery with no data loss
Repair script: /app/oracle/diag/rdbms/ora19c/ORA19C/hm/reco_156656125.hm
5. 내용 확인
- 그대로 rman에서 실행하면 된다.
[/home/oracle]$ cat /app/oracle/diag/rdbms/ora19c/ORA19C/hm/reco_156656125.hm # restore and recover datafile sql 'alter database datafile 8 offline'; restore ( datafile 8 ); recover datafile 8; sql 'alter database datafile 8 online';
그 후에 검증하면 이상없다고 뜬다.
RMAN> sql 'alter database datafile 8 online';
sql statement: alter database datafile 8 online
RMAN> validate tablespace DAMIN3_DATA_TS;
Starting validate at 18-JUN-23
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00008 name=/app/oracle/oradata/ORA19C/user_ts/DAMIN3_DATA_TS.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
8 OK 0 1 12801 2997276
File Name: /app/oracle/oradata/ORA19C/user_ts/DAMIN3_DATA_TS.dbf
Block Type Blocks Failing Blocks Processed
Data 0 0
Index 0 0
Other 0 12799
Finished validate at 18-JUN-23반응형