| # 요약 | |
| ■ Flashback Data Archive - create tablespace fda_tbs datafile ' ' size 10m autoextend on next 1m; - create flashback archive fda1 tablespace fda_tbs quota 10m retention 1year; - alter table hr.emp_fda flashback archive fda1; - alter table hr.emp_fda no flashback archive; - alter flashback archive fda1 modify retention 2 year; - alter flashback archive fda1 modify tablespace fda_tbs quota 20m; - alter flashback archive fda1 purge before timestamp(systimestamp - interval '1' day); - drop flashback archive fda1; - select * from dba_flashback_archive; - select * from dba_flashback_archive_tables; - select * from dba_flashback_archive_ts; ■ Flashback Database - alter database flashback on; - create restore point before_hr_jan_trunc; - shutdown immediate - startup mount - flashback database to restore point before_hr_jan_trunc; - alter database open read only; - shutdown immediate - startup mount - alter database open resetlogs; - show parameter db_flashback_retention_target - select flashback_on from v$database; - show parameter db_recovery_file_dest - select * from v$flash_recovery_area_usage; - select * from v$restore_point; ■ Block Corruption - shutdown immediate - startup - OS> dbv userid=system/oracle file=/u01/app/oracle/oradata/ora11g/users01.dbf blocksize=8192 - RMAN> validate datafile 4; - list failure; - advise failure; - repair failure preview; - repair failure; - validate database; ■ RMAN에서 일관성있는 백업 - shutdown immediate - startup mount - backup database; - list backup; - alter database open; ■ Process ! ps -ef | grep fbda ! ps -ef | grep fbda | grep -v grep ■ select * from v$option; |
|
■ Flashback Data Archive
- 기록 데이터 저장소
- FBDA 백그라운드 프로세스를 사용하여 Flashback Data Archive에 대한 활성화되어있는 테이블의 데이터를 자동으로 추적하고 아카이브한다.
| # FDA를 저장할 테이블스페이스 생성 create tablespace fda_tbs datafile '/u01/app/oracle/oradata/ora11g/fda_tbs01.dbf' size 10m autoextend on next 1m; 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 1378079 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1378079 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1378079 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1378079 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1378079 6 /u01/app/oracle/oradata/ora11g/fda_tbs01.dbf FDA_TBS ONLINE 1381154 # Flashback Data Archive 생성 create flashback archive fda1 tablespace fda_tbs quota 10m retention 1year; > fda1 이름의 아카이브에 생성일로부터 1년간 보유, 이후 자동 purge; > OS 종속, 용량관리 잘하자. select flashback_archive_name, create_time, status from dba_flashback_archive; FLASHBACK_ARCHIVE_NAME CREATE_TIME STATUS ---------------------- ------------------------------- ------------- FDA1 31-JAN-24 09.59.17.000000000 AM ! ps -ef | grep fbda oracle 31317 1 0 09:59 ? 00:00:00 ora_fbda_ora11g oracle 31648 30718 0 10:13 pts/0 00:00:00 /bin/bash -c ps -ef | grep fbda oracle 31650 31648 0 10:13 pts/0 00:00:00 grep fbda 또는 ! ps -ef | grep fbda | grep -v grep oracle 31317 1 0 09:59 ? 00:00:00 ora_fbda_ora11g >> 백그라운드 프로세스 활성화 create table hr.emp_fda as select * from hr.employees; select count(*) from hr.emp_fda; COUNT(*) ---------- 107 # FDA 대상 테이블 활성화 alter table hr.emp_fda flashback archive fda1; select * from dba_flashback_archive_tables; TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS -------------------- ---------- ---------------------- -------------------- ------------- EMP_FDA HR FDA1 SYS_FBA_HIST_88615 ENABLED select employee_id, salary from hr.emp_fda where department_id = 20; EMPLOYEE_ID SALARY ----------- ---------- 201 14300 202 6600 update hr.emp_fda set salary = 3000 where department_id = 20; commit; select employee_id, salary from hr.emp_fda where department_id = 20; EMPLOYEE_ID SALARY ----------- ---------- 201 3000 202 3000 select employee_id, salary from hr.emp_fda as of timestamp (systimestamp - interval '3' minute) where department_id = 20; EMPLOYEE_ID SALARY ----------- ---------- 201 14300 202 6600 > 3분전 데이터 delete from hr.emp_fda; commit; select * from hr.emp_fda; no rows selected select employee_id, salary from hr.emp_fda as of timestamp (systimestamp - interval '1' minute) where department_id = 20; EMPLOYEE_ID SALARY ----------- ---------- 201 3000 202 3000 >> 1분전 데이터 insert into hr.emp_fda select * from hr.emp_fda as of timestamp (systimestamp - interval '5' minute); 107 rows created. > 5분전 데이터로 insert commit; select employee_id, salary from hr.emp_fda as of timestamp (systimestamp - interval '50' minute) where department_id = 20; EMPLOYEE_ID SALARY ----------- ---------- 201 14300 202 6600 > undo_retention 900초(15분) 설정 상태이지만 FDA가 활성화되어있어 그 이전 시간의 데이터 확인이 가능하다. # FDA 기록 비활성화 alter table hr.emp_fda no flashback archive; SELECT flashback_archive_name,retention_in_days,create_time,last_purge_time,status from dba_flashback_archive; FLASHBACK_ARCHIVE_NAME RETENTION_IN_DAYS CREATE_TIME LAST_PURGE_TIME STATUS ---------------------- ----------------- ------------------- ------------------------------- ------------- FDA1 365 2024-01-31 09:59:17 2024-01-31 09:59:17 # FDA Retention 보유기간 변경 alter flashback archive fda1 modify retention 2 year; SELECT flashback_archive_name,retention_in_days,create_time,last_purge_time,status from dba_flashback_archive; FLASHBACK_ARCHIVE_NAME RETENTION_IN_DAYS CREATE_TIME LAST_PURGE_TIME STATUS ---------------------- ----------------- ------------------- ------------------------------- ------------- FDA1 730 2024-01-31 09:59:17 2024-01-31 09:59:17 # FDA 테이블스페이스 정보 select * from dba_flashback_archive_ts; FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# TABLESPACE_NAME QUOTA_IN_MB ---------------------- ------------------ --------------- ----------- FDA1 1 FDA_TBS 10 # FDA Quota 수정 alter flashback archive fda1 modify tablespace fda_tbs quota 20m; # FDA 데이터 지우기 alter flashback archive fda1 purge before timestamp(systimestamp - interval '1' day); > 1일 전 데이터 지우기 # FDA 삭제 drop flashback archive fda1; |
■ Flashback Database
- 데이터베이스에 대해 되감기 버튼처럼 작동한다.
- 이전 데이터로 되감기위해서 Redo, Archive 정보를 이용한다.
- Archive Log Mode에서만 수행가능하다.
- RVWR 백그라운드 프로세스에서 수행된다.
> DB를 과거로 돌아간다 = 최신데이터 날린다 = 불안전한 복구 > DB OFF > ... > ResetLogs > Backup
> CloneDB를 더 활용하자.
| ARCHIVE LOG LIST Database log mode Archive Mode Automatic archival Enabled Archive destination /home/oracle/arch1 Oldest online log sequence 1 Next log sequence to archive 3 Current log sequence 3 # DB Flashack 할 수 있는 최대시간을 분단위로 표시 (1440 = 24시간) show parameter db_flashback_retention_target NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_flashback_retention_target integer 1440 select isses_modifiable,issys_modifiable from v$parameter where name = 'db_flashback_retention_target'; ISSES_MODIFIABLE ISSYS_MODIFIABLE ----------------- ----------------- FALSE IMMEDIATE >> system 단위, 수행 즉시 반영 # DB Flashback 가능 시간 변경 alter system set db_flashback_retention_target = 2880 scope = both; > 최대 2일 DB Flaskback 가능, 지금 및 초기파라미터에 적용 show parameter db_flashback_retention_target NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_flashback_retention_target integer 2880 # flashback database 활성화 alter database flashback on; -- Alert Log --Wed Jan 31 11:11:35 2024 --RVWR started with pid=36, OS id=426 --Allocated 3981204 bytes in shared pool for flashback generation buffer --Flashback Database Enabled at SCN 1385440 --Completed: alter database flashback on select flashback_on from v$database; FLASHBACK_ON ------------------ YES create table hr.jan as select * from hr.employees; select count(*) from hr.jan; COUNT(*) ---------- 107 show parameter db_recovery_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/fast_recovery_area db_recovery_file_dest_size big integer 4152M # 공간 사용 비율 select * from v$flash_recovery_area_usage; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES -------------------- ------------------ ------------------------- --------------- CONTROL FILE 0 0 0 REDO LOG 0 0 0 ARCHIVED LOG 0 0 0 BACKUP PIECE 29.42 0 4 IMAGE COPY 0 0 0 FLASHBACK LOG 2.41 0 2 FOREIGN ARCHIVED LOG 0 0 0 select systimestamp from dual; SYSTIMESTAMP --------------------------------------------------------------------------- 31-JAN-24 11.23.16.856889 AM +09:00 # 포인트 생성 create restore point before_hr_jan_trunc; select scn,database_incarnation#,time,restore_point_time,name from v$restore_point; DATABASE_ SCN INCARNATION# TIME RESTORE_POINT_TIME NAME ---------- ------------ ------------------- ------------------- --------------------------------------------- 1385896 4 2024-01-31 11:24:03 BEFORE_HR_JAN_TRUNC # 장애 유발 truncate table hr.jan; > truncate : hr.jan 테이블이 속한 extent 해제를 위한 딕셔너리에 갱신 조작 발생 - undo 미비하게 발생 |
|
| # 복구 | |
| SYS@ora11g> shutdown immediate SYS@ora11g> startup mount # DB전체가 포인트 시점으로 되돌아 감 SYS@ora11g> flashback database to restore point before_hr_jan_trunc; # 읽기 모드로 오픈해서 복구 확인 SYS@ora11g> alter database open read only; # 데이터 확인 SYS@ora11g> select count(*) from hr.jan; COUNT(*) ---------- 107 SYS@ora11g> shutdown immediate SYS@ora11g> startup mount SYS@ora11g> alter database open resetlogs; |
|
| << TEST >> 신규유저 작업 중 point 생성, 유저 삭제 후 Flashback Database 적용하여 복구. | |
| > 유저생성 create user ut identified by oracle default tablespace users quota 10m on users; grant create session to ut; select * from dba_sys_privs where grantee = 'UT'; GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- UT CREATE SESSION NO > 포인트 1. create restore point before_user_ut; > ut 유저 테이블 생성 create table ut.uts as select * from hr.employees; select department_id, salary from ut.uts where department_id = 60; DEPARTMENT_ID SALARY ------------- ---------- 60 9000 60 6000 60 4800 60 4800 60 4200 > 포인트 2. create restore point before_ut_uts; > ut.uts 테이블 업데이트 update ut.uts set salary = 10000 where department_id = 60; commit; select department_id, salary from ut.uts where department_id = 60; DEPARTMENT_ID SALARY ------------- ---------- 60 10000 60 10000 60 10000 60 10000 60 10000 > hr 유저 테이블 생성 create table hr.emp_ut as select * from hr.employees; select count(*) from hr.emp_ut; COUNT(*) ---------- 107 > 포인트 3. create restore point before_hr_ut; > ut 유저 삭제 drop user ut cascade; select * from ut.uts; ORA-00942: table or view does not exist > 포인트 지점 확인 select SCN,DATABASE_INCARNATION#,TIME,RESTORE_POINT_TIME,NAME from v$restore_point; DATABASE_ SCN INCARNATION# TIME RESTORE_POINT_TIME NAME ---------- ------------ ------------------- ------------------- --------------------------------------------- 1385896 4 2024-01-31 11:24:03 BEFORE_HR_JAN_TRUNC 1396189 5 2024-01-31 13:28:46 BEFORE_USER_UT 1396268 5 2024-01-31 13:29:24 BEFORE_UT_UTS 1396310 5 2024-01-31 13:29:58 BEFORE_HR_UT > DB 정상 종료 shutdown immediate > 마운트 단계 startup mount > 포인트 2 지점에서 복구 flashback database to restore point before_ut_uts; > 복구 내역 확인위해 읽기모드로 오픈 alter database open read only; select open_mode from v$database; OPEN_MODE -------------------- READ ONLY select department_id, salary from ut.uts where department_id = 60; DEPARTMENT_ID SALARY ------------- ---------- 60 9000 60 6000 60 4800 60 4800 60 4200 select count(*) from hr.emp_ut; ORA-00942: table or view does not exist >> 업데이트 전으로 돌아갔다. shutdown immediate startup mount alter database open resetlogs; |
|
| > Restore Point는 살아있다. BEFORE_USER_UT 로 복구. select SCN,DATABASE_INCARNATION#,TIME,RESTORE_POINT_TIME,NAME from v$restore_point; DATABASE_ SCN INCARNATION# TIME RESTORE_POINT_TIME NAME ---------- ------------ ------------------- ------------------- --------------------------------------------- 1385896 4 2024-01-31 11:24:03 BEFORE_HR_JAN_TRUNC 1396189 5 2024-01-31 13:28:46 BEFORE_USER_UT 1396268 5 2024-01-31 13:29:24 BEFORE_UT_UTS 1396310 5 2024-01-31 13:29:58 BEFORE_HR_UT SYS@ora11g> shutdown immediate SYS@ora11g> startup mount SYS@ora11g> flashback database to restore point before_user_ut; SYS@ora11g> alter database open read only; SYS@ora11g> select department_id, salary from ut.uts where department_id = 60; ORA-00942: table or view does not exist SYS@ora11g> select * from dba_sys_privs where grantee = 'UT'; GRANTEE PRIVILEGE ADMIN_OPTION ------------------------------ ---------------------------------------- ------------ UT CREATE SESSION NO SYS@ora11g> shutdown immediate SYS@ora11g> startup mount SYS@ora11g> alter database open resetlogs; 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 1396194 2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1396194 3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1396194 4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1396194 5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1396194 6 /u01/app/oracle/oradata/ora11g/fda_tbs01.dbf FDA_TBS ONLINE 1396194 > 백업본을 사용하지 않을뿐 결국 최신 데이터는 없다. CloneDB로 부분 복구를 사용하자. |
|
■ 블록 손상 (Block Corruption)
- 블록을 읽거나 쓸때마다 일관성 검사를 한다.
1. 블록 버전
2. 블록 dba(Data Block Address) 값과 비교되는 캐시의 dba값을 체크
3. 활성화된 블록의 체크섬
- 손상된 블록
1. media 손상
2. 논리적 또는 소프트웨어 손상
| > 백업보유 체크 RMAN> list backup; create table hr.emp_copy as select * from hr.employees; select file_id, block_id from dba_extents where segment_name = 'EMP_COPY' and owner = 'HR'; FILE_ID BLOCK_ID ---------- ---------- 4 584 select file_name from dba_data_files where file_id = 4; FILE_NAME -------------------------------------------------- /u01/app/oracle/oradata/ora11g/users01.dbf # 장애 유발 : 블럭 손상 ! dd if=/dev/zero of=/u01/app/oracle/oradata/ora11g/users01.dbf bs=8192 seek=584 count=2 conv=notrunc 2+0 records in 2+0 records out 16384 bytes (16 kB) copied, 0.000378079 s, 43.3 MB/s > 블럭안의 내용을 zero시킨다 alter system flush buffer_cache; alter system flush buffer_cache; alter system flush buffer_cache; alter system flush buffer_cache; select count(*) from hr.emp_copy; COUNT(*) ---------- 107 SYS@ora11g> shutdown immediate SYS@ora11g> startup >>> 왜 고장이 안남?? 에러 나왔다 생각하고 진행 |
|
| # DataFile 일관성 검사 및 Block 복구 | |
| # OS에서 Block 일관성검사 : dbv [oracle@oracle ~]$ dbv userid=system/oracle file=/u01/app/oracle/oradata/ora11g/users01.dbf blocksize=8192 DBVERIFY: Release 11.2.0.4.0 - Production on Wed Jan 31 14:13:16 2024 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/ora11g/users01.dbf Page 128 is marked corrupt Corrupt block relative dba: 0x01000080 (file 4, block 128) Completely zero block found during dbv: Page 129 is marked corrupt Corrupt block relative dba: 0x01000081 (file 4, block 129) Completely zero block found during dbv: Page 584 is marked corrupt Corrupt block relative dba: 0x01000248 (file 4, block 584) Completely zero block found during dbv: Page 585 is marked corrupt Corrupt block relative dba: 0x01000249 (file 4, block 585) <-- 테스트 중 이런 오류가 이미 나왔어야 한다 Completely zero block found during dbv: DBVERIFY - Verification complete Total Pages Examined : 640 총 블록 수 Total Pages Processed (Data) : 107 테이블 블록 수 Total Pages Failing (Data) : 0 테이블 블록 중 문제가 있는 블록 수 Total Pages Processed (Index): 39 인덱스 블록 수 Total Pages Failing (Index): 0 인덱스 문제가 있는 블록 수 Total Pages Processed (Other): 421 그 외 다른 블록 수 Total Pages Processed (Seg) : 51 segment 관련 정보의 블록 수 Total Pages Failing (Seg) : 0 segment 관련 정보의 블록 중 문제가 있는 블록 수 Total Pages Empty : 18 비어있는 블록 수 Total Pages Marked Corrupt : 4 문제가 있어서 corrupt marked 된 블록 수 Total Pages Influx : 0 체크하는(dbv를 사용하고있는) 동안 다른 사용자가 데이터를 변경하고 있는 블록을 dbv를 사용하기위해 다시 읽은 블록의 수 Total Pages Encrypted : 0 Highest block SCN : 1397985 (0.1397985) [oracle@oracle ~]$ rman target / connected to target database: ORA11G (DBID=256148331) # RMAN에서 일관성검사 RMAN> validate datafile 4; Starting validate at 31-JAN-24 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 device type=DISK channel ORA_DISK_1: starting validation of datafile channel ORA_DISK_1: specifying datafile(s) for validation input datafile file number=00004 name=/u01/app/oracle/oradata/ora11g/users01.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 ---- ------ -------------- ------------ --------------- ---------- 4 FAILED 1 18 676 1397985 File Name: /u01/app/oracle/oradata/ora11g/users01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 107 Index 0 39 Other 3 476 validate found one or more corrupt blocks See trace file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_5282.trc for details Finished validate at 31-JAN-24 RMAN> list failure; List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 9145 HIGH OPEN 31-JAN-24 Datafile 4: '/u01/app/oracle/oradata/ora11g/users01.dbf' contains one or more corrupt blocks RMAN> list failure 9145 detail; List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 9145 HIGH OPEN 31-JAN-24 Datafile 4: '/u01/app/oracle/oradata/ora11g/users01.dbf' contains one or more corrupt blocks Impact: Some objects in tablespace USERS might be unavailable List of child failures for parent failure ID 9145 Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 9166 HIGH OPEN 31-JAN-24 Block 585 in datafile 4: '/u01/app/oracle/oradata/ora11g/users01.dbf' is media corrupt Impact: Object EMP_COPY owned by HR might be unavailable 9160 HIGH OPEN 31-JAN-24 Block 584 in datafile 4: '/u01/app/oracle/oradata/ora11g/users01.dbf' is media corrupt Impact: Object EMP_COPY owned by HR might be unavailable 9154 HIGH OPEN 31-JAN-24 Block 129 in datafile 4: '/u01/app/oracle/oradata/ora11g/users01.dbf' is media corrupt Impact: Object DEPT owned by SCOTT might be unavailable 9148 HIGH OPEN 31-JAN-24 Block 128 in datafile 4: '/u01/app/oracle/oradata/ora11g/users01.dbf' is media corrupt Impact: Object DEPT owned by SCOTT might be unavailable RMAN> advise failure; List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 9145 HIGH OPEN 31-JAN-24 Datafile 4: '/u01/app/oracle/oradata/ora11g/users01.dbf' contains one or more corrupt blocks Impact: Some objects in tablespace USERS might be unavailable List of child failures for parent failure ID 9145 Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 9166 HIGH OPEN 31-JAN-24 Block 585 in datafile 4: '/u01/app/oracle/oradata/ora11g/users01.dbf' is media corrupt Impact: Object EMP_COPY owned by HR might be unavailable 9160 HIGH OPEN 31-JAN-24 Block 584 in datafile 4: '/u01/app/oracle/oradata/ora11g/users01.dbf' is media corrupt Impact: Object EMP_COPY owned by HR might be unavailable 9154 HIGH OPEN 31-JAN-24 Block 129 in datafile 4: '/u01/app/oracle/oradata/ora11g/users01.dbf' is media corrupt Impact: Object DEPT owned by SCOTT might be unavailable 9148 HIGH OPEN 31-JAN-24 Block 128 in datafile 4: '/u01/app/oracle/oradata/ora11g/users01.dbf' is media corrupt Impact: Object DEPT owned by SCOTT 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 Recover multiple corrupt blocks in datafile 4 Strategy: The repair includes complete media recovery with no data loss Repair script: /u01/app/oracle/diag/rdbms/ora11g/ora11g/hm/reco_992776220.hm RMAN> repair failure preview; Strategy: The repair includes complete media recovery with no data loss Repair script: /u01/app/oracle/diag/rdbms/ora11g/ora11g/hm/reco_992776220.hm contents of repair script: # block media recovery for multiple blocks recover datafile 4 block 128 to 129, 584 to 585; RMAN> repair failure; Strategy: The repair includes complete media recovery with no data loss Repair script: /u01/app/oracle/diag/rdbms/ora11g/ora11g/hm/reco_992776220.hm contents of repair script: # block media recovery for multiple blocks recover datafile 4 block 128 to 129, 584 to 585; Do you really want to execute the above repair (enter YES or NO)? y executing repair script Starting recover at 31-JAN-24 using channel ORA_DISK_1 searching flashback logs for block images until SCN 1378079 finished flashback log search, restored 2 blocks channel ORA_DISK_1: restoring block(s) channel ORA_DISK_1: specifying block(s) to restore from backup set restoring blocks of datafile 00004 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T194603_lvknvcrk_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T194603_lvknvcrk_.bkp tag=TAG20240130T194603 channel ORA_DISK_1: restored block(s) from backup piece 1 channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01 starting media recovery media recovery complete, elapsed time: 00:00:03 Finished recover at 31-JAN-24 repair failure complete RMAN> validate datafile 4; Starting validate at 31-JAN-24 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=00004 name=/u01/app/oracle/oradata/ora11g/users01.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 ---- ------ -------------- ------------ --------------- ---------- 4 OK 0 18 676 1397985 File Name: /u01/app/oracle/oradata/ora11g/users01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 107 Index 0 39 Other 0 476 Finished validate at 31-JAN-24 |
|
| # DB 일관성 검사 | |
| > 정기적으로 검사하자 RMAN> validate database; Starting validate at 31-JAN-24 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=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/fda_tbs01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/ora11g/users01.dbf channel ORA_DISK_1: validation complete, elapsed time: 00:00:26 List of Datafiles ================= File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 1 OK 0 14141 96005 1402511 File Name: /u01/app/oracle/oradata/ora11g/system01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 63964 Index 0 13383 Other 0 4512 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 2 OK 0 18605 80751 1402510 File Name: /u01/app/oracle/oradata/ora11g/sysaux01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 16597 Index 0 12172 Other 0 33266 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 3 OK 0 153 2336 1402511 File Name: /u01/app/oracle/oradata/ora11g/undotbs01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 0 Index 0 0 Other 0 2183 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 4 OK 0 18 676 1397985 File Name: /u01/app/oracle/oradata/ora11g/users01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 107 Index 0 39 Other 0 476 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 5 OK 0 31371 42403 1388576 File Name: /u01/app/oracle/oradata/ora11g/example01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 4547 Index 0 1149 Other 0 5333 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 6 OK 0 1099 1280 1382702 File Name: /u01/app/oracle/oradata/ora11g/fda_tbs01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 26 Index 0 1 Other 0 154 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:02 List of Control File and SPFILE =============================== File Type Status Blocks Failing Blocks Examined ------------ ------ -------------- --------------- SPFILE OK 0 2 Control File OK 0 632 Finished validate at 31-JAN-24 |
|
| ■ RMAN에서 일관성있는 백업 | |
| - NoArchive Log Mode에서 RMAN Backup 방법 동일 RMAN> shutdown immediate RMAN> startup mount RMAN> report schema; ㄴ 데이터 확인 RMAN> backup database; RMAN> list backup; BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 72 Full 1.18G DISK 00:01:05 31-JAN-24 BP Key: 95 Status: AVAILABLE Compressed: NO Tag: TAG20240131T144146 Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_31/o1_mf_nnndf_TAG20240131T144146_lvmqdtvr_.bkp List of Datafiles in backup set 72 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 1403123 31-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf 2 Full 1403123 31-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 3 Full 1403123 31-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 4 Full 1403123 31-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf 5 Full 1403123 31-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf 6 Full 1403123 31-JAN-24 /u01/app/oracle/oradata/ora11g/fda_tbs01.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 73 Full 9.95M DISK 00:00:02 31-JAN-24 BP Key: 96 Status: AVAILABLE Compressed: NO Tag: TAG20240131T144251 Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_31/o1_mf_s_1159713653_lvmqgxnj_.bkp SPFILE Included: Modification time: 31-JAN-24 SPFILE db_unique_name: ORA11G Control File Included: Ckp SCN: 1403123 Ckp time: 31-JAN-24 RMAN> alter database open; |
|
| # v$option | |
| - Oracle Database 옵션과 기능 - 기본 제공되는 기능 및 별도 라이센스가 필요한 옵션까지 목록에 포함 select * from v$option; PARAMETER VALUE ----------------------------------- -------------------------------------------------- Partitioning TRUE Objects TRUE Real Application Clusters FALSE 별도 라이센스 Advanced replication TRUE Bit-mapped indexes TRUE Connection multiplexing TRUE Connection pooling TRUE Database queuing TRUE Incremental backup and recovery TRUE Instead-of triggers TRUE Parallel backup and recovery TRUE Parallel execution TRUE Parallel load TRUE Point-in-time tablespace recovery TRUE Fine-grained access control TRUE Proxy authentication/authorization TRUE Change Data Capture TRUE Plan Stability TRUE Online Index Build TRUE Coalesce Index TRUE Managed Standby TRUE Materialized view rewrite TRUE Database resource manager TRUE Spatial TRUE Automatic Storage Management FALSE 공유스토리지 사용해야 설치 된다 Export transportable tablespaces TRUE Transparent Application Failover TRUE Fast-Start Fault Recovery TRUE Sample Scan TRUE Duplexed backups TRUE Java TRUE OLAP Window Functions TRUE Block Media Recovery TRUE Fine-grained Auditing TRUE Application Role TRUE Enterprise User Security TRUE Oracle Data Guard TRUE Oracle Label Security FALSE OLAP TRUE Basic Compression TRUE Join index TRUE Trial Recovery TRUE Data Mining TRUE Online Redefinition TRUE Streams Capture TRUE File Mapping TRUE Block Change Tracking TRUE Flashback Table TRUE 별도 라이센스 Flashback Database TRUE 별도 라이센스 Transparent Data Encryption TRUE Backup Encryption TRUE Unused Block Compression TRUE Oracle Database Vault FALSE Result Cache TRUE SQL Plan Management TRUE SecureFiles Encryption TRUE Real Application Testing TRUE Flashback Data Archive TRUE 별도 라이센스 DICOM TRUE Active Data Guard TRUE Server Flash Cache TRUE Advanced Compression TRUE XStream TRUE Deferred Segment Creation TRUE Data Redaction TRUE |
|
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ALTER-FLASHBACK-ARCHIVE.html
SQL Language Reference
docs.oracle.com
'Oracle 11g > Backup & Recovery' 카테고리의 다른 글
| 76 Backup&Recovery, RMAN, Block Change Tracking, Flashback (0) | 2024.01.30 |
|---|---|
| 75 Backup&Recovery, RMAN (0) | 2024.01.29 |
| 74 Backup&Recovery, RMAN (0) | 2024.01.26 |
| 73 Backup&Recovery, RMAN (0) | 2024.01.25 |
| 72 Backup&Recovery, RMAN (0) | 2024.01.24 |