| # 요약 | |
| << 시나리오 >> Drop Table, CloneDB - Time Base Recovery - 복원DB 설치할 위치확인 - $ORACLE_HOME/dbs/spfile 생성 *.compatible='11.2.0.4.0' *.control_files='/home/oracle/clone/control01.ctl' *.db_name='clone' *.log_archive_dest_1='location=/home/oracle/clone mandatory' *.log_archive_format='arch_%t_%s_%r.arc' *.undo_tablespace='undotbs' - 신규 위치로 CP - Archive Log File, Data File, Control File - echo $ORACLE_HOME : sqlplus 위치 확인 - . oraenv : 복원DB 연결 - 복원DB에 접속 - nomount - rman auxiliary / -RMAN> RUN { set newname for datafile 1 to '신규위치'; DUPLICATE TARGET DATABASE TO 'clone' pfile='/u01/app/oracle/product/11.2.0.4/db_1/dbs/initclone.ora' nofilenamecheck backup location '/home/oracle/clone' until time "to_date('복원 기준 시간','yyyy-mm-dd hh24:mi:ss')" LOGFILE '/home/oracle/clone/redo01.log' SIZE 50M, } - 복원DB에 접속 - 복구 파일 체크 - export - . oraenv : 기존DB 연결 - 기존DB 접속 - import # 백그라운드 프로세스 강제종료 kill -9 [PID] << 시나리오 >> RMAN 백업 위치변경, cloneDB Time Base Recovery - 신규 위치 확인 - backup as compressed backupset format '/변경위치/%d_%U_%T' database include current controlfile; << 시나리오 >> Data File, Archive Log File 손상 -> RMAN 불안전한 복구 # 백업 저장 위치 변경하기 RMAN> backup as compressed backupset format '/home/oracle/rman_clone/%d_%U_%T' database include current controlfile; # scn 기준으로 해당 번호전까지 복구 RMAN> recover database until scn 1244943; RMAN> crosscheck backup; RMAN> crosscheck backupset; # 설정 초기화 CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR; # incarnation 데이터베이스를 resetlogs 옵션으로 open 할때 마다 incarnation이 생성됨(reset) > 컨트롤파일이 가지고 있는 scn 번호가 current한 scn번호보다 높을경우 사용 불가 # resetlogs list RMAN> list incarnation of database; # inc key 9번으로 변경 RMAN> reset database to incarnation 9; > Rman Backup After Check - list backup; - report schema; - report need backup; - report obsolete; - crosscheck archivelog all; - list expired archivelog all; - crosscheck backupset; - list expired backupset; - crosscheck backup; - crosscheck copy; |
|
<< 시나리오 >> Drop Table, CloneDB - Time Base Recovery
| SELECT a.group#,b.sequence#,a.member,b.bytes/1024/1024 mb,b.archived,b.status, b.first_time,b.first_change#,b.next_change# FROM v$logfile a, v$log b WHERE a.group# = b.group# ORDER BY 1; GROUP# SEQUENCE# MEMBER MB ARCHIVED STATUS ------ --------- -------------------------------------------------- ---------- --------- ------------- FIRST_TIME FIRST_CHANGE# NEXT_CHANGE# ----------- ------------- ------------ 1 1 /u01/app/oracle/oradata/ora11g/redo01.log 50 NO CURRENT 26-JAN-24 1244944 2.8147E+14 2 0 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES UNUSED 0 0 3 0 /u01/app/oracle/oradata/ora11g/redo03.log 50 YES UNUSED 0 0 >> rman backup = seq#1 = redo01.log alter system switch logfile; alter system switch logfile; alter system switch logfile; --Fri Jan 26 17:32:48 2024 --Thread 1 advanced to log sequence 4 (LGWR switch) -- Current log# 1 seq# 4 mem# 0: /u01/app/oracle/oradata/ora11g/redo01.log create table hr.emp_tamp as select * from hr.employees; select count(*) from hr.emp_tamp; COUNT(*) ---------- 107 select systimestamp from dual; SYSTIMESTAMP --------------------------------------------------------------------------- 26-JAN-24 05.33.32.716918 PM +09:00 # 장애 유발 drop table hr.emp_tamp purge; alter system switch logfile; --Fri Jan 26 17:34:03 2024 --Thread 1 advanced to log sequence 5 (LGWR switch) -- Current log# 2 seq# 5 mem# 0: /u01/app/oracle/oradata/ora11g/redo02.log alter system switch logfile; alter system switch logfile; # 시간 찾기 - Log Miner - seq#4 select name from v$archived_log where sequence# = 4; NAME -------------------------------------------------- /home/oracle/arch1/arch_1_4_1159289804.arc SELECT supplemental_log_data_min FROM v$database; SUPPLEME -------- YES BEGIN dbms_logmnr.add_logfile(logfilename => '/home/oracle/arch1/arch_1_4_1159289804.arc', options => dbms_logmnr.new); END; / SELECT db_name, filename FROM v$logmnr_logs; DB_NAME FILENAME -------- --------------------------------------------- ORA11G /home/oracle/arch1/arch_1_4_1159289804.arc BEGIN dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog); END; / --Fri Jan 26 17:32:48 2024 --Thread 1 advanced to log sequence 4 (LGWR switch) -- Current log# 1 seq# 4 mem# 0: /u01/app/oracle/oradata/ora11g/redo01.log --Fri Jan 26 17:34:03 2024 --Thread 1 advanced to log sequence 5 (LGWR switch) -- Current log# 2 seq# 5 mem# 0: /u01/app/oracle/oradata/ora11g/redo02.log SELECT SEQUENCE#,SCN,to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') timestamp,operation,sql_redo FROM v$logmnr_contents WHERE timestamp >= to_date('2024-01-26 17:32:48','yyyy-mm-dd hh24:mi:ss') and timestamp < to_date('2024-01-26 17:34:03','yyyy-mm-dd hh24:mi:ss') and TABLE_NAME = 'EMP_TAMP'; SEQUENCE# SCN TIMESTAMP OPERATION --------- ---------- ------------------- -------------------------------- SQL_REDO -------------------------------------------------------------------------------------------------------------- 1 1248325 2024-01-26 17:33:02 DDL create table hr.emp_tamp as select * from hr.employees; 1 1248361 2024-01-26 17:33:34 DDL drop table hr.emp_tamp purge; |
|
| # 복구 | |
| SELECT name,sequence#,first_change#,first_time,next_change#,next_time FROM v$archived_log where name is not null order by next_time; NAME SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME -------------------------------------------------- --------- ------------- ----------- ------------ --------- /u01/app/oracle/oradata/ora11g/redo01.log 25 1245117 26-JAN-24 1245120 26-JAN-24 /u01/app/oracle/oradata/ora11g/redo02.log 26 1245120 26-JAN-24 1245123 26-JAN-24 /home/oracle/arch1/arch_1_1_1159289804.arc 1 1244944 26-JAN-24 1248271 26-JAN-24 /home/oracle/arch1/arch_1_2_1159289804.arc 2 1248271 26-JAN-24 1248293 26-JAN-24 /home/oracle/arch1/arch_1_3_1159289804.arc 3 1248293 26-JAN-24 1248296 26-JAN-24 /home/oracle/arch1/arch_1_4_1159289804.arc 4 1248296 26-JAN-24 1248400 26-JAN-24 /home/oracle/arch1/arch_1_6_1159289804.arc 6 1248407 26-JAN-24 1248410 26-JAN-24 /home/oracle/arch1/arch_1_5_1159289804.arc 5 1248400 26-JAN-24 1248407 26-JAN-24 /u01/app/oracle/oradata/ora11g/redo03.log 27 1245123 26-JAN-24 2.8147E+14 SYS@ora11g> ! ls /home/oracle/arch1 arch_1_1_1159289804.arc arch_1_2_1159289804.arc arch_1_3_1159289804.arc arch_1_4_1159289804.arc arch_1_5_1159289804.arc arch_1_6_1159289804.arc [oracle@oracle ~]$ cd $ORACLE_HOME/dbs [oracle@oracle dbs]$ ls hc_ora11g.dat init.ora initora11g.ora lkORA11G orapwora11g snapcf_ora11g.f spfileora11g.ora [oracle@oracle dbs]$ vi initclone.ora *.compatible='11.2.0.4.0' *.control_files='/home/oracle/clone/control01.ctl' *.db_name='clone' *.log_archive_dest_1='location=/home/oracle/clone mandatory' *.log_archive_format='arch_%t_%s_%r.arc' *.undo_tablespace='undotbs' :wq [oracle@oracle ~]$ mkdir /home/oracle/clone # CP ArchiveLog cp -av /home/oracle/arch1/*.* /home/oracle/clone/ >> 백업본부터 최근까지 모두 # CP RMAN Backupset DataFile, ControlFile cp -av /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_26/o1_mf_nnndf_TAG20240126T172900_lv6vbdfz_.bkp /home/oracle/clone/ cp -av /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_26/o1_mf_s_1159291775_lv6vchq4_.bkp /home/oracle/clone/ >> 복구시간 이전에 해당하는 최근 백업본 [oracle@oracle ~]$ ls /home/oracle/clone/ arch_1_1_1159289804.arc arch_1_3_1159289804.arc arch_1_5_1159289804.arc o1_mf_nnndf_TAG20240126T172900_lv6vbdfz_.bkp arch_1_2_1159289804.arc arch_1_4_1159289804.arc arch_1_6_1159289804.arc o1_mf_s_1159291775_lv6vchq4_.bkp [oracle@oracle ~]$ echo $ORACLE_HOME /u01/app/oracle/product/11.2.0.4/db_1 [oracle@oracle ~]$ . oraenv ORACLE_SID = [ora11g] ? clone ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/11.2.0.4/db_1 The Oracle base remains unchanged with value /u01/app/oracle [oracle@oracle ~]$ sqlplus / as sysdba Connected to an idle instance. SYS@clone> startup nomount 또는 startup pfile=$ORACLE_HOME/dbs/initclone.ora nomount SYS@clone> select status, instance_name from v$instance; STATUS INSTANCE_NAME ------------ ---------------- STARTED clone # RMAN으로 복제DB 만들기 [oracle@oracle ~]$ rman auxiliary / connected to auxiliary database: CLONE (not mounted) RMAN> RUN { set newname for datafile 1 to '/home/oracle/clone/system01.dbf'; set newname for datafile 2 to '/home/oracle/clone/sysaux01.dbf'; set newname for datafile 3 to '/home/oracle/clone/undotbs01.dbf'; set newname for datafile 4 to '/home/oracle/clone/users01.dbf'; set newname for datafile 5 to '/home/oracle/clone/example01.dbf'; DUPLICATE TARGET DATABASE TO 'clone' pfile='/u01/app/oracle/product/11.2.0.4/db_1/dbs/initclone.ora' nofilenamecheck backup location '/home/oracle/clone' until time "to_date('2024-01-26 17:33:20','yyyy-mm-dd hh24:mi:ss')" LOGFILE '/home/oracle/clone/redo01.log' SIZE 50M, '/home/oracle/clone/redo02.log' SIZE 50M, '/home/oracle/clone/redo03.log' SIZE 50M; } ... database opened Finished Duplicate Db at 26-JAN-24 - datafile : 백업본과 동일하게 목록 구성 - DUPLICATE TARGET DATABASE TO 'clone' : 백업정보 ora11g DB -> clone - pfile : 초기파라미터로 sp파일 만들기 - nofilenamecheck : 파일체크하지마 - backup location : 백업본 위치 - LOGFILE : redo log가 없으면 오픈이 안되니 작성 [oracle@oracle ~]$ sqlplus / as sysdba SYS@clone> select status, instance_name from v$instance; STATUS INSTANCE_NAME ------------ ---------------- OPEN clone 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 /home/oracle/clone/system01.dbf SYSTEM SYSTEM 1247713 2 /home/oracle/clone/sysaux01.dbf SYSAUX ONLINE 1247713 3 /home/oracle/clone/undotbs01.dbf UNDOTBS ONLINE 1247713 4 /home/oracle/clone/users01.dbf USERS ONLINE 1247713 5 /home/oracle/clone/example01 EXAMPLE ONLINE 1247713 select count(*) from hr.emp_tamp; ORA-00942: table or view does not exist >>>> 시간을 잘 못 선택한 듯.. SELECT e.segment_name,f.tablespace_name, f.file_name FROM dba_extents e, dba_data_files f WHERE f.file_id = e.file_id AND e.owner = 'HR' AND e.segment_name = 'EMP_TAMP'; no rows selected >> 테이블이 없네?! [oracle@oracle ~]$ exp system/oracle file=emp_temp.dmp tables=hr.emp_tamp [oracle@oracle ~]$ . oraenv ORACLE_SID = [clone] ? ora11g The Oracle base remains unchanged with value /u01/app/oracle [oracle@oracle ~]$ sqlplus / as sysdba SYS@ora11g> select status, instance_name from v$instance; STATUS INSTANCE_NAME ------------ ---------------- OPEN ora11g SYS@ora11g> select count(*) from hr.emp_tamp; ORA-00942: table or view does not exist >> 당연하게 없는 상태 [oracle@oracle ~]$ imp system/oracle file=emp_temp.dmp tables=emp_tamp fromuser=hr SYS@ora11g> select count(*) from hr.emp_tamp; |
|
# DB 로그인 실패 시 필수 백그라운드 프로세스 강제 종료 후 sqlplus 재시도
- PMON, SMON, ...
[oracle@oracle ~]$ ps -ef | grep clone
oracle 21556 1 0 11:07 ? 00:00:00 ora_pmon_clone
oracle 21580 1 0 11:07 ? 00:00:00 ora_smon_clone
...
[oracle@oracle ~]$ kill -9 21580
[oracle@oracle ~]$ kill -9 21556
[oracle@oracle ~]$ ps -ef | grep clone
oracle 22248 21865 0 11:33 pts/0 00:00:00 grep --color=auto clone
<< 시나리오 >> Control File 백업 위치변경, cloneDB Time Base Recovery
| # RMAN Backup 저장위치 변경 | |
| ------- ---- -- ---------- ----------- ------------ --------------- 27 Full 1.05M DISK 00:00:01 26-JAN-24 BP Key: 27 Status: AVAILABLE Compressed: YES Tag: TAG20240126T140022 Piece Name: /home/oracle/rman_clone/ORA11G_132hickp_1_1_20240126 Control File Included: Ckp SCN: 1215999 Ckp time: 26-JAN-24 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 28 Full 9.67M DISK 00:00:00 26-JAN-24 BP Key: 28 Status: AVAILABLE Compressed: NO Tag: TAG20240126T140100 Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_26/o1_mf_s_1159279260_lv6h4dkt_.bkp SPFILE Included: Modification time: 26-JAN-24 SPFILE db_unique_name: ORA11G Control File Included: Ckp SCN: 1216006 Ckp time: 26-JAN-24 RMAN> report obsolete; RMAN> delete obsolete; RMAN> report obsolete; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 1 no obsolete backups found crosscheck archivelog all; list expired archivelog all; crosscheck copy; SELECT a.group#,b.sequence#,a.member,b.bytes/1024/1024 mb,b.archived,b.status, b.first_time,b.first_change#,b.next_change# FROM v$logfile a, v$log b WHERE a.group# = b.group# ORDER BY 1; alter system switch logfile; alter system switch logfile; alter system switch logfile; --Fri Jan 26 14:10:47 2024 --Thread 1 advanced to log sequence 14 (LGWR switch) -- Current log# 2 seq# 14 mem# 0: /u01/app/oracle/oradata/ora11g/redo02.log select count(*) from hr.new_20240126; # 장애 발생 drop table hr.new_20240126 purge; alter system switch logfile; alter system switch logfile; alter system switch logfile; |
|
| # 복구 | |
SELECT a.group#,b.sequence#,a.member,b.bytes/1024/1024 mb,b.archived,b.status, b.first_time,b.first_change#,b.next_change# FROM v$logfile a, v$log b WHERE a.group# = b.group# ORDER BY 1; alter system switch logfile; create table hr.new_20240126 as select * from hr.employees; select count(*) from hr.new_20240126; alter system switch logfile; mkdir -p /home/oracle/rman_clone rman target / >> DB : ora11g report schema; list backup; delete backup; # 다른 위치에 백업하기 RMAN> backup as compressed backupset format '/home/oracle/rman_clone/%d_%U_%T' database include current controlfile; %d : 데이터베이스 이름 %U : %u_%p_%c %u : 백업셋 번호 + 생성된 시간을 조합한 8자리 번호 %p : 백업셋 피스 번호 %c : 중복된 백업 피스 내에서 백업 피스의 고유번호 %T : 날짜 정보 Starting backup at 26-JAN-24 using channel ORA_DISK_1 channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set 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=00004 name=/u01/app/oracle/oradata/ora11g/users01.dbf input datafile file number=00006 name=/u01/app/oracle/oradata/ora11g/test01.dbf channel ORA_DISK_1: starting piece 1 at 26-JAN-24 channel ORA_DISK_1: finished piece 1 at 26-JAN-24 piece handle=/home/oracle/rman_clone/ORA11G_122hicjm_1_1_20240126 tag=TAG20240126T140022 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35 channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set channel ORA_DISK_1: starting piece 1 at 26-JAN-24 channel ORA_DISK_1: finished piece 1 at 26-JAN-24 piece handle=/home/oracle/rman_clone/ORA11G_132hickp_1_1_20240126 tag=TAG20240126T140022 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 26-JAN-24 Starting Control File and SPFILE Autobackup at 26-JAN-24 piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_26/o1_mf_s_1159279260_lv6h4dkt_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 26-JAN-24 >>> controlFile는 기존 기본폴더에 저장되었다????? SYS@ora11g> ! cp -av /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_26/o1_mf_s_1159279260_lv6h4dkt_.bkp /home/oracle/rman_clone/ RMAN> list backup; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 26 Full 304.15M DISK 00:00:26 26-JAN-24 BP Key: 26 Status: AVAILABLE Compressed: YES Tag: TAG20240126T140022 Piece Name: /home/oracle/rman_clone/ORA11G_122hicjm_1_1_20240126 List of Datafiles in backup set 26 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 1215938 26-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf 2 Full 1215938 26-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 3 Full 1215938 26-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 4 Full 1215938 26-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf 5 Full 1215938 26-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf 6 Full 1215938 26-JAN-24 /u01/app/oracle/oradata/ora11g/test01.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time SELECT name,sequence#,first_change#,first_time,next_change#,next_time FROM v$archived_log where name is not null order by next_time; # 초기 파라미터 수정 [oracle@oracle ~]$ vi $ORACLE_HOME/dbs/initclone.ora *.compatible='11.2.0.4.0' *.control_files='/home/oracle/rman_clone/control01.ctl' *.db_name='clone' *.log_archive_dest_1='location=/home/oracle/rman_clone mandatory' *.log_archive_format='arch_%t_%s_%r.arc' *.undo_tablespace='undotbs' db_file_name_convert=('/u01/app/oracle/oradata/ora11g/','/home/oracle/rman_clone/') log_file_name_convert=('/u01/app/oracle/oradata/ora11g/','/home/oracle/rman_clone/') :wq # CP 아카이브 cp -av /home/oracle/arch1/*.* /home/oracle/rman_clone/ SYS@ora11g> ! ls /home/oracle/rman_clone/ arch_1_13_1159213361.arc arch_1_14_1159213361.arc arch_1_15_1159213361.arc o1_mf_s_1159279260_lv6h4dkt_.bkp ORA11G_122hicjm_1_1_20240126 [oracle@oracle ~]$ echo $ORACLE_HOME /u01/app/oracle/product/11.2.0.4/db_1 [oracle@oracle ~]$ . oraenv ORACLE_SID = [ora11g] ? clone ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/11.2.0.4/db_1 The Oracle base remains unchanged with value /u01/app/oracle [oracle@oracle ~]$ sqlplus / as sysdba SYS@clone> startup pfile=$ORACLE_HOME/dbs/initclone.ora nomount ORACLE instance started. [oracle@oracle ~]$ rman auxiliary / connected to auxiliary database: CLONE (not mounted) RMAN> DUPLICATE DATABASE TO 'clone' pfile='/u01/app/oracle/product/11.2.0.4/db_1/dbs/initclone.ora' nofilenamecheck backup location '/home/oracle/rman_clone' until time "to_date('2024-01-26 14:10:40','yyyy-mm-dd hh24:mi:ss')"; [oracle@oracle ~]$ sqlplus / as sysdba select status, instance_name from v$instance; open conn hr/hr select count(*) from hr.new_20240126; |
|
<< 시나리오 >> Data File, Archive Log File 손상 -> RMAN 불안전한 복구
SELECT a.group#,b.sequence#,a.member,b.bytes/1024/1024 mb,b.archived,b.status,
b.first_time,b.first_change#,b.next_change#
FROM v$logfile a, v$log b
WHERE a.group# = b.group#
ORDER BY 1;
GROUP# SEQUENCE# MEMBER MB ARCHIVED STATUS
------ --------- -------------------------------------------------- ---------- --------- -------------
FIRST_TIME FIRST_CHANGE# NEXT_CHANGE#
----------- ------------- ------------
1 19 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES INACTIVE
26-JAN-24 1241920 1241926
2 20 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES INACTIVE
26-JAN-24 1241926 1241932
3 21 /u01/app/oracle/oradata/ora11g/redo03.log 50 NO CURRENT
26-JAN-24 1241932 2.8147E+14
[oracle@oracle ~]$ mkdir /home/oracle/backup/rman
[oracle@oracle ~]$ rman target /
connected to target database: ORA11G (DBID=256148331)
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 620 SYSAUX *** /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 14 UNDOTBS *** /u01/app/oracle/oradata/ora11g/undotbs01.dbf
4 5 USERS *** /u01/app/oracle/oradata/ora11g/users01.dbf
5 331 EXAMPLE *** /u01/app/oracle/oradata/ora11g/example01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/ora11g/temp01.dbf
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
# 컨트롤 파일 백업 위치 변경
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/backup/rman/%F';
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 '/home/oracle/backup/rman/%F';
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
RMAN> delete backup;
RMAN> list backup;
specification does not match any backup in the repository
RMAN> backup as compressed backupset format '/home/oracle/backup/rman/%d_%U_%T' database;
>> seq#21
RMAN> report obsolete;
RMAN> delete obsolete;
[oracle@oracle rman]$ ls /home/oracle/backup/rman
c-256148331-20240126-06 ORA11G_182hil1f_1_1_20240126
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
32 Full 304.60M DISK 00:00:23 26-JAN-24
BP Key: 32 Status: AVAILABLE Compressed: YES Tag: TAG20240126T162415
Piece Name: /home/oracle/backup/rman/ORA11G_182hil1f_1_1_20240126
List of Datafiles in backup set 32
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1244659 26-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf
2 Full 1244659 26-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 Full 1244659 26-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
4 Full 1244659 26-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf
5 Full 1244659 26-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
33 Full 9.67M DISK 00:00:01 26-JAN-24
BP Key: 33 Status: AVAILABLE Compressed: NO Tag: TAG20240126T162440
Piece Name: /home/oracle/backup/rman/c-256148331-20240126-06
SPFILE Included: Modification time: 26-JAN-24
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 1244747 Ckp time: 26-JAN-24
create table hr.emp_arch as select * from hr.employees;
> seq#21
select count(*) from hr.emp_arch;
COUNT(*)
----------
107
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
--Fri Jan 26 16:28:28 2024
--Thread 1 advanced to log sequence 24 (LGWR switch)
-- Current log# 3 seq# 24 mem# 0: /u01/app/oracle/oradata/ora11g/redo03.log
SYS@ora11g> ! ls /home/oracle/arch1
arch_1_21_1159213361.arc arch_1_22_1159213361.arc arch_1_23_1159213361.arc
create table hr.dept_arch as select * from hr.departments;
select count(*) from hr.dept_arch;
> seq#24
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
SELECT a.group#,b.sequence#,a.member,b.bytes/1024/1024 mb,b.archived,b.status,
b.first_time,b.first_change#,b.next_change#
FROM v$logfile a, v$log b
WHERE a.group# = b.group#
ORDER BY 1;
GROUP# SEQUENCE# MEMBER MB ARCHIVED STATUS
------ --------- -------------------------------------------------- ---------- --------- -------------
FIRST_TIME FIRST_CHANGE# NEXT_CHANGE#
----------- ------------- ------------
1 25 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES INACTIVE
26-JAN-24 1245117 1245120
2 26 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES INACTIVE
26-JAN-24 1245120 1245123
3 27 /u01/app/oracle/oradata/ora11g/redo03.log 50 NO CURRENT
26-JAN-24 1245123 2.8147E+14
SELECT e.segment_name,f.tablespace_name, f.file_name
FROM dba_extents e, dba_data_files f WHERE f.file_id = e.file_id
AND e.owner = 'HR' AND e.segment_name = 'DEPT_ARCH';
SEGMENT_NAME TABLESPACE_NAME FILE_NAME
----------------------- --------------- --------------------------------------------------
DEPT_ARCH USERS /u01/app/oracle/oradata/ora11g/users01.dbf
SELECT name,sequence#,first_change#,first_time,next_change#,next_time
FROM v$archived_log
where name is not null
order by next_time;
NAME SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
-------------------------------------------------- --------- ------------- ----------- ------------ ---------
/u01/app/oracle/oradata/ora11g/redo03.log 3 1179447 25-JAN-24 1180234 25-JAN-24
/u01/app/oracle/oradata/ora11g/redo01.log 4 1180234 25-JAN-24 1180305 25-JAN-24
/home/oracle/arch1/arch_1_21_1159213361.arc 21 1241932 26-JAN-24 1244937 26-JAN-24
/home/oracle/arch1/arch_1_22_1159213361.arc 22 1244937 26-JAN-24 1244940 26-JAN-24
/home/oracle/arch1/arch_1_23_1159213361.arc 23 1244940 26-JAN-24 1244943 26-JAN-24
/home/oracle/arch1/arch_1_24_1159213361.arc 24 1244943 26-JAN-24 1245117 26-JAN-24
/home/oracle/arch1/arch_1_25_1159213361.arc 25 1245117 26-JAN-24 1245120 26-JAN-24
/home/oracle/arch1/arch_1_26_1159213361.arc 26 1245120 26-JAN-24 1245123 26-JAN-24
/u01/app/oracle/oradata/ora11g/redo02.log 5 1180305 25-JAN-24 2.8147E+14
# 장애 발생
! rm /u01/app/oracle/oradata/ora11g/users01.dbf
! rm /home/oracle/arch1/arch_1_24_1159213361.arc
SYS@ora11g> shutdown immediate
SYS@ora11g> startup
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/ora11g/users01.dbf'
[oracle@oracle ~]$ rman target /
connected to target database: ORA11G (DBID=256148331, not open)
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 620 SYSAUX *** /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 14 UNDOTBS *** /u01/app/oracle/oradata/ora11g/undotbs01.dbf
4 5 USERS *** /u01/app/oracle/oradata/ora11g/users01.dbf
5 331 EXAMPLE *** /u01/app/oracle/oradata/ora11g/example01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/ora11g/temp01.dbf
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
45 HIGH OPEN 26-JAN-24 One or more non-system datafiles are missing
22 HIGH OPEN 17-DEC-23 One or more non-system datafiles are offline
RMAN> list failure 45 detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
45 HIGH OPEN 26-JAN-24 One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 45
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
5886 HIGH OPEN 26-JAN-24 Datafile 4: '/u01/app/oracle/oradata/ora11g/users01.dbf' is missing
Impact: Some objects in tablespace USERS might be unavailable
RMAN> restore tablespace users;
Starting restore at 26-JAN-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=171 device type=DISK
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 00004 to /u01/app/oracle/oradata/ora11g/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/rman/ORA11G_182hil1f_1_1_20240126
channel ORA_DISK_1: piece handle=/home/oracle/backup/rman/ORA11G_182hil1f_1_1_20240126 tag=TAG20240126T162415
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 26-JAN-24
RMAN> recover tablespace users;
Starting recover at 26-JAN-24
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 21 is already on disk as file /home/oracle/arch1/arch_1_21_1159213361.arc
archived log for thread 1 with sequence 22 is already on disk as file /home/oracle/arch1/arch_1_22_1159213361.arc
archived log for thread 1 with sequence 23 is already on disk as file /home/oracle/arch1/arch_1_23_1159213361.arc
archived log for thread 1 with sequence 25 is already on disk as file /home/oracle/arch1/arch_1_25_1159213361.arc
archived log for thread 1 with sequence 26 is already on disk as file /home/oracle/arch1/arch_1_26_1159213361.arc
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/26/2024 16:41:24
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 24 and starting SCN of 1244943 found to restore
RMAN-06025: 시퀀스 24 및 1244943의 시작 SCN이 복원된 스레드 1에 대한 보관된 로그 백업이 없습니다.
>>> 완전복구 시도 > 아카이브 파일 손상으로 실패
> rman은 cancel 기능 없다 > scn 번호로 사용
> 아카이브 손상 > 테이블스페이스 레벨의 부분복구 불가 > 불안전한 복구 진행
RMAN> shutdown abort
RMAN> startup nomount
>> 과거 백업본 컨트롤파일 사용 예정으로 노마운트 모드로 오픈
>> 노마운트에서는 백업목록 안나옴 미리 파일명 확인해두자
> 컨트롤 파일 백업본 : /home/oracle/backup/rman/c-256148331-20240126-06
RMAN> restore controlfile from '/home/oracle/backup/rman/c-256148331-20240126-06';
Starting restore at 26-JAN-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/ora11g/control01.ctl
Finished restore at 26-JAN-24
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> restore database;
...
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 26-JAN-24
# scn 기준으로 해당 번호전까지 복구
RMAN> recover database until scn 1244943;
Starting recover at 26-JAN-24
using channel ORA_DISK_1
starting media recovery
archived log file name=/home/oracle/arch1/arch_1_21_1159213361.arc thread=1 sequence=21
archived log file name=/home/oracle/arch1/arch_1_22_1159213361.arc thread=1 sequence=22
archived log file name=/home/oracle/arch1/arch_1_23_1159213361.arc thread=1 sequence=23
media recovery complete, elapsed time: 00:00:01
Finished recover at 26-JAN-24
RMAN> alter database open resetlogs;
SYS@ora11g> select status, instance_name from v$instance;
STATUS INSTANCE_NAME
------------- ----------------
OPEN ora11g
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 1244947
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX ONLINE 1244947
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS ONLINE 1244947
4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS ONLINE 1244947
5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE ONLINE 1244947
GROUP# SEQUENCE# MEMBER MB ARCHIVED STATUS
------ --------- -------------------------------------------------- ---------- --------- -------------
FIRST_TIME FIRST_CHANGE# NEXT_CHANGE#
----------- ------------- ------------
1 1 /u01/app/oracle/oradata/ora11g/redo01.log 50 NO CURRENT
26-JAN-24 1244944 2.8147E+14
2 0 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES UNUSED
0 0
3 0 /u01/app/oracle/oradata/ora11g/redo03.log 50 YES UNUSED
0 0
SELECT * FROM v$log_history;
RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# RESETLOGS_CHANGE# RESETLOGS
---------- ---------- ---------- --------- ------------- ----------- ------------ ----------------- ---------
99 1159289512 1 23 1244940 26-JAN-24 1244943 1180353 25-JAN-24
100 1159289804 1 25 1245117 26-JAN-24 1245120 1180353 25-JAN-24
RMAN-06025: no backup of archived log for thread 1 with sequence 24 and starting SCN of 1244943 found to restore
RMAN-06025: 시퀀스 24 및 1244943의 시작 SCN이 복원된 스레드 1에 대한 보관된 로그 백업이 없습니다.
>> seq#24 파일 손상으로 seq#23까지 복구완료
open resetlogs > backup !!
>>>>> scn으로 복구 실패 시
# resetlogs list
list incarnation of database;
과거부터 현재까지 리셋로그즈 사용한 목록
컨트롤파일이 가지고 있는 scn 번호가 current한 scn번호보다 높을경우 사용 불가
shutdown immediate
startup nomount
reset database to incarnation 9;
> inc key 9번으로 맞춰줘
run {set until scn=1244943;
restore database;
recover database;
}
alter database open resetlogs;
>> rman은 불안전한복구 시 hotbackup 으로 사용가능 > 쌓이고쌓여 컨트롤파일이 과거 정보를 가지고 있어 발생
>> Cancel Base Recovery에서 발생 시 incarnation 확인해보자.
<< TEST >> Drop User, Rman - CloneDB - Time Base Recovery
'Oracle 11g > Backup & Recovery' 카테고리의 다른 글
| 76 Backup&Recovery, RMAN, Block Change Tracking, Flashback (0) | 2024.01.30 |
|---|---|
| 75 Backup&Recovery, RMAN (0) | 2024.01.29 |
| 73 Backup&Recovery, RMAN (0) | 2024.01.25 |
| 72 Backup&Recovery, RMAN (0) | 2024.01.24 |
| 71 Backup&Recovery, Export & Import, Data Pump (0) | 2024.01.23 |