11g부터 추가된 기능으로써, 장애 발생 시 장애 분석과 해결에 도움이 되는 Oracle의 기능입니다.
(1) 장애 테이블스페이스 확인 작업
SQL> select tablespace_name,bytes/1024/1024 mb,file_name from dba_data_files;
TABLESPACE_NAME MB FILE_NAME
------------------------------ ----------
USERS 355 /oradata/ORCL19C/users01.dbf
(2) 장애 테이블스페이스 offline으로 작업
SQL> alter tablespace users offline immediate;
Tablespace altered.
(3) 장애 테이블스페이스 확인
SQL> select tablespace_name,file_name,online_status from dba_data_files;
TABLESPACE_NAME FILE_NAME ONLINE_
------------------------------
USERS /oradata/ORCL19C/users01.dbf RECOVER
(4) 장애 발생 및 확인
SQL> !rm -rf /oradata/ORCL19C/users01.dbf
SQL> !ls -al /oradata/ORCL19C/users01.dbf
ls: cannot access /oradata/ORCL19C/users01.dbf: 그런 파일이나 디렉터리가 없습니다
(5) 장애 테이블스페이스 online
SQL> alter tablespace users online;
alter tablespace users online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/oradata/ORCL19C/users01.dbf'
(6) RMAN에서 장애 확인
RMAN> list failure;
using target database control file instead of recovery catalog
Database Role: PRIMARY
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
1328 HIGH OPEN 19/12/06 Tablespace 4: 'USERS' is offline
762 HIGH OPEN 19/12/06 One or more non-system datafiles are missing
(7) 상세 장애내역 확인
RMAN> list failure 762 detail;
Database Role: PRIMARY
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
762 HIGH OPEN 19/12/06 One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 762
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
1325 HIGH OPEN 19/12/06 Datafile 7: '/oradata/ORCL19C/users01.dbf' is missing
Impact: Some objects in tablespace USERS might be unavailable
(8) 해결법에 따른 확인
RMAN> advise failure;
Database Role: PRIMARY
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
762 HIGH OPEN 19/12/06 One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 762
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
1325 HIGH OPEN 19/12/06 Datafile 7: '/oradata/ORCL19C/users01.dbf' is missing
Impact: Some objects in tablespace USERS might be unavailable
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=265 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file /oradata/ORCL19C/users01.dbf was unintentionally renamed or moved, restore it
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 7
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl19c/orcl19c/hm/reco_963146171.hm
원인을 파악하고 완전복구가 가능하며, 데이터 손실이 없다고 조언 확인
RMAN> repair failure preview;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl19c/orcl19c/hm/reco_963146171.hm
contents of repair script:
# restore and recover datafile
sql 'alter database datafile 7 offline';
restore ( datafile 7 );
recover datafile 7;
sql 'alter database datafile 7 online';
(9) 복구작업 수행
RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl19c/orcl19c/hm/reco_963146171.hm
contents of repair script:
# restore and recover datafile
sql 'alter database datafile 7 offline';
restore ( datafile 7 );
recover datafile 7;
sql 'alter database datafile 7 online';
Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script
sql statement: alter database datafile 7 offline
Starting restore at 19/12/06
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 /oradata/ORCL19C/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/rman2/20191205_1duim4fj_1_1
channel ORA_DISK_1: piece handle=/home/oracle/rman2/20191205_1duim4fj_1_1 tag=TAG20191205T164019
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 19/12/06
Starting recover at 19/12/06
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00007: /oradata/ORCL19C/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/rman2/20191205_1iuim5n4_1_1
channel ORA_DISK_1: piece handle=/home/oracle/rman2/20191205_1iuim5n4_1_1 tag=TAG20191205T170124
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00007: /oradata/ORCL19C/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/rman2/20191205_1kuim6bd_1_1
channel ORA_DISK_1: piece handle=/home/oracle/rman2/20191205_1kuim6bd_1_1 tag=TAG20191205T171213
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00007: /oradata/ORCL19C/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/rman2/20191205_1muim6jt_1_1
channel ORA_DISK_1: piece handle=/home/oracle/rman2/20191205_1muim6jt_1_1 tag=TAG20191205T171644
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/product/19.5.0/db_1/dbs/arch1_2_1026227892.dbf
archived log file name=/u01/app/oracle/product/19.5.0/db_1/dbs/arch1_2_1026227892.dbf thread=1 sequence=2
unable to find archived log
archived log thread=1 sequence=1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of repair command at 12/06/2019 00:14:31
RMAN-03015: error occurred in stored script Repair Script
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 1 and starting SCN of 2851281
RMAN> recover tablespace users;
Starting recover at 19/12/06
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 19/12/06
RMAN> sql'alter tablespace users online';
sql statement: alter tablespace users online
(10) 테이블스페이스 확인
SQL> select tablespace_name,file_name,online_status from dba_data_files;
TABLESPACE_NAME FILE_NAME ONLINE_
------------------------------
USERS /oradata/ORCL19C/users01.dbf ONLINE
복구완료
'Oracle > RMAN' 카테고리의 다른 글
[RMAN]Recover Table (2) | 2019.12.22 |
---|---|
[RMAN]Database 사전 예방 점검 기능 (0) | 2019.12.21 |
[RMAN] 전체 DATABASE(DATAFILE,CONTROLFILE,REDOLOG등)이 삭제 되었을때 복구 (0) | 2019.12.19 |
[RMAN] 증분 백업을 사용한 Drop Table 복구 (0) | 2019.12.18 |
[RMAN] Datafile 삭제 후 Mount 상태에서복구(offline 안되는 Tablespace) (0) | 2019.12.17 |
댓글