RMAN 복구 시나리오
▶ 1. datafile 이 손상되었을때 시나리오
-> user managed 의 아카이브로그모드 시나리오 1번하고 같다.
● 1. 작업
- 테이블생성후 로그스위치 발생
SQL> create table hr.loc_new tablespace users as select * from hr.employees;
Table created.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.- 물리적 아카이브파일 확인
SQL> ! ls /home/oracle/arch1
arch_1_1_1158057974.arc arch_1_4_1158057974.arc arch_1_7_1158057974.arc
arch_1_2_1158057974.arc arch_1_5_1158057974.arc arch_1_8_1158057974.arc
arch_1_3_1158057974.arc arch_1_6_1158057974.arc- redo log 확인
select a.group#,b.thread#,b.sequence#, member, b.bytes/1024/1024 mb, b.archived, b.status,
b.first_change#, b.next_change#, b.next_time
from v$logfile a, v$log b
where a.group# = b.group#
order by 1,2;
---
1 1 7 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES INACTIVE 1682249 1682252 24/01/14
2 1 8 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES INACTIVE 1682252 1682255 24/01/14
3 1 9 /u01/app/oracle/oradata/ora11g/redo03.log 50 NO CURRENT 1682255 281474976710655 ● 2. 장애발생 : users 데이터파일 삭제 (생성한 테이블 있는곳)
- 생성한 테이블 위치확인
select f.file_name
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'LOC_NEW'
and e.owner = 'HR';
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/users01.dbf- users 데이터파일 삭제
SQL> ! rm /u01/app/oracle/oradata/ora11g/users01.dbf- 아직 리두버퍼 메모리에서 사용하고있어서 테이블 생성이 가능하다. (에러뜰수도 있음)
SQL> create table hr.emp_new tablespace users as select * from hr.employees;
Table created.● 3. RMAN으로 FAILURE 확인
- rman 접속
[oracle@oracle autobackup]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sun Jan 14 17:17:37 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (DBID=256148002)- failure 리스트 확인
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
105 HIGH OPEN 14-JAN-24 One or more non-system datafiles are missing- failure 자세히 보기 (advise failure;
쓰면 더 좋은데, 실습이니깐)
RMAN> list failure 105 detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
105 HIGH OPEN 14-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 105
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
3785 HIGH OPEN 14-JAN-24 Datafile 4: '/u01/app/oracle/oradata/ora11g/users01.dbf' is missing
Impact: Some objects in tablespace USERS might be unavailable
- alertlog 창에도 보인다.
Sun Jan 14 17:14:37 2024
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_m000_3071.trc:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/app/oracle/oradata/ora11g/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Sun Jan 14 17:14:37 2024
Checker run found 1 new persistent data failures- backup된 리스트 보기
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 1.22G DISK 00:00:29 14-JAN-24
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20240114T163728
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_14/o1_mf_nnndf_TAG20240114T163728_lt73srk7_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1680006 14-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf
2 Full 1680006 14-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 Full 1680006 14-JAN-24 /u01/app/oracle/oradata/ora11g/hrm_tbs01.dbf
4 Full 1680006 14-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf
5 Full 1680006 14-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf
6 Full 1680006 14-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 9.36M DISK 00:00:00 14-JAN-24
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20240114T163803
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_14/o1_mf_s_1158251883_lt73tvtz_.bkp
SPFILE Included: Modification time: 14-JAN-24
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 1680032 Ckp time: 14-JAN-24- rman advise
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
105 HIGH OPEN 14-JAN-24 One or more non-system datafiles are missing
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=15 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file /u01/app/oracle/oradata/ora11g/users01.dbf was unintentionally renamed or moved, restore it
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 4
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/ora11g/ora11g/hm/reco_2638914110.hm
RMAN> list failure 105 detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
105 HIGH OPEN 14-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 105
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
3785 HIGH OPEN 14-JAN-24 Datafile 4: '/u01/app/oracle/oradata/ora11g/users01.dbf' is missing
Impact: Some objects in tablespace USERS might be unavailable
● 4. 장애발생 테이블스페이스 offline immediate 변경
- rman에서 sql 쓸려면
RMAN> sql '쿼리';
RMAN> sql 'alter tablespace users offline immediate';
sql statement: alter tablespace users offline immediate● 5. 가장최근 백업으로 restore
RMAN> restore tablespace users;
Starting restore at 14-JAN-24
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 00004 to /u01/app/oracle/oradata/ora11g/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_14/o1_mf_nnndf_TAG20240114T163728_lt73srk7_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_14/o1_mf_nnndf_TAG20240114T163728_lt73srk7_.bkp tag=TAG20240114T163728
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 14-JAN-24● 6. 테이블스페이스 recover
: 아카이브, 리두 적용
RMAN> recover tablespace users;
Starting recover at 14-JAN-24
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 6 is already on disk as file /home/oracle/arch1/arch_1_6_1158057974.arc
archived log for thread 1 with sequence 7 is already on disk as file /home/oracle/arch1/arch_1_7_1158057974.arc
archived log for thread 1 with sequence 8 is already on disk as file /home/oracle/arch1/arch_1_8_1158057974.arc
archived log file name=/home/oracle/arch1/arch_1_6_1158057974.arc thread=1 sequence=6
media recovery complete, elapsed time: 00:00:00
Finished recover at 14-JAN-24● 7. 복구한 테이블스페이스 online 변경
RMAN> sql 'alter tablespace users online';
sql statement: alter tablespace users online- failure 리스트 확인
RMAN> list failure;
no failures found that match specification▶ 2. 기존위치(디스크)가 문제가 생길때 다른 디스크로 적용후 복구 시나리오
-> user managed 의 아카이브로그모드 시나리오 5번하고 같다.
● 1. 작업
- 다른 디스크라고 생각하고 테이블스페이스 생성
: 디스크가 깨졋을때 그곳에 restore할수 없으니깐 다른 디스크에다가 복구해줘야한다.
create tablespace insa_tbs datafile '/home/oracle/insa_tbs01.dbf' size 10m;
create table hr.dept_new tablespace insa_tbs
as select * from hr.departments;
select count(*) from hr.dept_new;
- 로그 스위치 발생
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.- 리두로그 확인
select a.group#,b.thread#,b.sequence#, member, b.bytes/1024/1024 mb, b.archived, b.status,
b.first_change#, b.next_change#, b.next_time
from v$logfile a, v$log b
where a.group# = b.group#
order by 1,2;
1 1 10 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES INACTIVE 1683866 1683869 24/01/14
2 1 11 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES INACTIVE 1683869 1683872 24/01/14
3 1 12 /u01/app/oracle/oradata/ora11g/redo03.log 50 NO CURRENT 1683872 281474976710655 - 물리적 아카이브파일 확인
SQL> ! ls /home/oracle/arch1
arch_1_10_1158057974.arc arch_1_3_1158057974.arc arch_1_7_1158057974.arc
arch_1_11_1158057974.arc arch_1_4_1158057974.arc arch_1_8_1158057974.arc
arch_1_1_1158057974.arc arch_1_5_1158057974.arc arch_1_9_1158057974.arc
arch_1_2_1158057974.arc arch_1_6_1158057974.arc● 2. 새로만든 테이블스페이스 부분 backup
- rman 접속
[oracle@oracle autobackup]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sun Jan 14 17:55:53 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (DBID=256148002)- 한번도 backup 안한것 report
RMAN> report need backup;
using target database control file instead of recovery catalog
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
7 0 /home/oracle/insa_tbs01.dbf
- 부분 백업
RMAN> backup tablespace insa_tbs;
Starting backup at 14-JAN-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=186 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=00007 name=/home/oracle/insa_tbs01.dbf
channel ORA_DISK_1: starting piece 1 at 14-JAN-24
channel ORA_DISK_1: finished piece 1 at 14-JAN-24
piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_14/o1_mf_nnndf_TAG20240114T175651_lt78gmqb_.bkp tag=TAG20240114T175651 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-JAN-24
Starting Control File and SPFILE Autobackup at 14-JAN-24
piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_14/o1_mf_s_1158256612_lt78gnxr_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 14-JAN-24● 3. 특정한 테이블스페이스 백업 list 확인
RMAN> list backup of tablespace insa_tbs;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 1.07M DISK 00:00:00 14-JAN-24
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20240114T175651
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_14/o1_mf_nnndf_TAG20240114T175651_lt78gmqb_.bkp
List of Datafiles in backup set 4
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
7 Full 1684864 14-JAN-24 /home/oracle/insa_tbs01.dbf● 4. 장애발생 :
- 아까 만든 테이블 위치 확인
select f.file_name
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'DEPT_NEW'
and e.owner = 'HR';
---
/home/oracle/insa_tbs01.dbf- datafile 삭제
SYS@ora11g> ! rm /home/oracle/insa_tbs01.dbf- alert log 창 확인 -> 에러발생
- 만약 DB가 내려간다면?
- startup mount
- alter database datafile '문제되는 데이터파일' offline;
- alter database open
- rman 접속
● 5. rman에서 failure 확인
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
105 HIGH OPEN 14-JAN-24 One or more non-system datafiles are missingRMAN> list failure 105 detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
105 HIGH OPEN 14-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 105
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
3910 HIGH OPEN 14-JAN-24 Datafile 7: '/home/oracle/insa_tbs01.dbf' is missing
Impact: Some objects in tablespace INSA_TBS might be unavailable● 6. 작업형 모드로 수행
- 설명
- 문제되는 테이블스페이스 오프라인
- datafile 경로 변경
- 최근백업으로 restore
- 문제되는 datafile 스위치 - report schema로 조회
- recover
- online으로 변경
RMAN> run { sql 'ALTER TABLESPACE insa_tbs OFFLINE IMMEDIATE'; SET NEWNAME FOR DATAFILE '/home/oracle/insa_tbs01.dbf' TO '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf'; RESTORE TABLESPACE insa_tbs; SWITCH DATAFILE 7; -- 문제되는 file number 확인 RECOVER TABLESPACE insa_tbs; sql 'ALTER TABLESPACE insa_tbs ONLINE'; }
sql statement: ALTER TABLESPACE insa_tbs OFFLINE IMMEDIATE
executing command: SET NEWNAME
Starting restore at 14-JAN-24
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 /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_14/o1_mf_nnndf_TAG20240114T175651_lt78gmqb_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_14/o1_mf_nnndf_TAG20240114T175651_lt78gmqb_.bkp tag=TAG20240114T175651
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 14-JAN-24
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=1158258210 file name=/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf
Starting recover at 14-JAN-24
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 14-JAN-24
sql statement: ALTER TABLESPACE insa_tbs ONLINE
RMAN> list failure;
no failures found that match specification▶ 3. system 데이터 파일이 손상 되었을때 시나리오
-> user managed 의 아카이브로그모드 시나리오 7번하고 같다.
● 1. 작업
- redo log 확인
select a.group#,b.thread#,b.sequence#, member, b.bytes/1024/1024 mb, b.archived, b.status,
b.first_change#, b.next_change#, b.next_time
from v$logfile a, v$log b
where a.group# = b.group#
order by 1,2;
---
1 1 13 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES INACTIVE 1685539 1685580 24/01/14
2 1 14 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES INACTIVE 1685580 1701392 24/01/14
3 1 15 /u01/app/oracle/oradata/ora11g/redo03.log 50 NO CURRENT 1701392 281474976710655 - 테이블생성후, 로그스위치 발생
create table hr.emp_20240124 tablespace users
as select * from hr.employees;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;● 2. 장애 발생
- system 데이터파일 삭제
SYS@ora11g> ! rm /u01/app/oracle/oradata/ora11g/system01.dbf● 3. startup mount 단계까지 올린다
- rman 에서도 db shutdown, startup 가능하다.
RMAN> startup mount
connected to target database: ORA11G (DBID=256148002, not open)
using target database control file instead of recovery catalog
database is already started- startup mount
RMAN> startup mount
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
RMAN> list failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
4107 CRITICAL OPEN 14-JAN-24 System datafile 1: '/u01/app/oracle/oradata/ora11g/system01.dbf' is missing● 4. failure 조회
RMAN> list failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
4107 CRITICAL OPEN 14-JAN-24 System datafile 1: '/u01/app/oracle/oradata/ora11g/system01.dbf' is missing- 상세보기
RMAN> list failure 4107 detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
4107 CRITICAL OPEN 14-JAN-24 System datafile 1: '/u01/app/oracle/oradata/ora11g/system01.dbf' is missing
Impact: Database cannot be opened
● 5. 백업 리스트 확인후 restore
- 백업 리스트 확인
RMAN> list backup;- 최근 백업본으로 restore
RMAN> restore tablespace system;● 6. recover 후 open
RMAN> recover tablespace system;- db open
RMAN> alter database open;
database opened▶ 4. 모든 데이터 파일이 손상, 기존위치가 아닌 새로운 위치로 데이터파일 복구 시나리오
-> user managed 의 아카이브로그모드 시나리오 10번하고 같다.
● 1. 백업본 확인
- 백업본 리스트 확인
RMAN> list backup;- datafile 확인
select tablespace_name, file_name, bytes/1024/1024 mb from dba_data_files;
----
USERS /u01/app/oracle/oradata/ora11g/users01.dbf 8.75
SYSAUX /u01/app/oracle/oradata/ora11g/sysaux01.dbf 680
SYSTEM /u01/app/oracle/oradata/ora11g/system01.dbf 750
HRM_TBS /u01/app/oracle/oradata/ora11g/hrm_tbs01.dbf 10
EXAMPLE /u01/app/oracle/oradata/ora11g/example01.dbf 330
UNDOTBS /u01/app/oracle/oradata/ora11g/undotbs01.dbf 74.875● 2. 장애발생 : 모든 데이터파일 삭제
SYS@ora11g> ! rm /u01/app/oracle/oradata/ora11g/*.dbf- 에러
SYS@ora11g> alter system switch logfile;
ERROR:
ORA-03114: not connected to ORACLE- alert log 창
Sun Jan 14 21:55:16 2024
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_j000_21205.trc:
ORA-12012: error on auto execute of job 3
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/u01/app/oracle/oradata/ora11g/undotbs01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Sun Jan 14 21:55:17 2024
Checker run found 5 new persistent data failures● 3. rman 을 이용하여 mount단계까지 startup
RMAN> shutdown abort
using target database control file instead of recovery catalog
Oracle instance shut down- startup mount 단계
RMAN> startup mount
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes- report schema
: control 파일의 정보이다.
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 0 SYSTEM *** /u01/app/oracle/oradata/ora11g/system01.dbf
2 0 SYSAUX *** /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 0 HRM_TBS *** /u01/app/oracle/oradata/ora11g/hrm_tbs01.dbf
4 0 USERS *** /u01/app/oracle/oradata/ora11g/users01.dbf
5 0 EXAMPLE *** /u01/app/oracle/oradata/ora11g/example01.dbf
6 0 UNDOTBS *** /u01/app/oracle/oradata/ora11g/undotbs01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/ora11g/temp01.dbf● 4. 백업 정보 확인
RMAN> list backup;
----
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8 Full 1.23G DISK 00:00:21 14-JAN-24
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20240114T204057
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_14/o1_mf_nnndf_TAG20240114T204057_lt7l29vo_.bkp
List of Datafiles in backup set 8
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1701790 14-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf
2 Full 1701790 14-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 Full 1701790 14-JAN-24 /u01/app/oracle/oradata/ora11g/hrm_tbs01.dbf
4 Full 1701790 14-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf
5 Full 1701790 14-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf
6 Full 1701790 14-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9 Full 9.36M DISK 00:00:01 14-JAN-24
BP Key: 9 Status: AVAILABLE Compressed: NO Tag: TAG20240114T204122
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_14/o1_mf_s_1158266482_lt7l32yj_.bkp
SPFILE Included: Modification time: 14-JAN-24
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 1701812 Ckp time: 14-JAN-24● 5. 스크립트 실행
- datafile 이름변경 (newname) 해주고
- restore
- switch
- recover
- open
RMAN> run {
set newname for datafile 4 to '/home/oracle/ora_data/users01.dbf';
set newname for datafile 2 to '/home/oracle/ora_data/sysaux01.dbf';
set newname for datafile 1 to '/home/oracle/ora_data/system01.dbf';
set newname for datafile 3 to '/home/oracle/ora_data/hrm_tbs01.dbf';
set newname for datafile 5 to '/home/oracle/ora_data/example01.dbf';
set newname for datafile 6 to '/home/oracle/ora_data/undotbs01.dbf';
restore database;
switch datafile all;
recover database;
alter database open;
}
...
media recovery complete, elapsed time: 00:00:02
Finished recover at 14-JAN-24
database opened▶ 불필요한 백업파일들 삭제 일련의 과정
- 스키마 확인, 백업확인, 삭제, 전체 백업, 백업확인
RMAN> report schema;
RMAN> list backup;
RMAN> delete backup;
RMAN> backup database;
RMAN> list backup;- 아카이브로그 리스트 확인
RMAN> list archivelog all;- 컨트롤파일정보와 실제랑 다른것 확인
RMAN> crosscheck archivelog all;- 만기된 아카이브 리스트 (안보일때도 있다.)
RMAN> list expired archivelog all;- 정책상 필요없는 백업 리스트
: 아카이브가 보인다.
RMAN> report obsolete;- 물리적 위치에서 확인
[oracle@oracle ora_data]$ ls /home/oracle/arch1
arch_1_15_1158057974.arc arch_1_17_1158057974.arc arch_1_19_1158057974.arc
arch_1_16_1158057974.arc arch_1_18_1158057974.arc arch_1_20_1158057974.arc- 필요없는 백업 삭제
RMAN> delete obsolete;- 물리적 위치에서 확인
[oracle@oracle ora_data]$ ls /home/oracle/arch1
- 다시 확인
RMAN> report obsolete;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
no obsolete backups found- 백업정보 확인
RMAN> list backup;▶ 5. 압축된 아카이브파일로 복구 시나리오
● 1. 작업
- users테이블스페이스에 테이블 생성
● 2. 장애발생 : users 데이터파일 삭제
SYS@ora11g> ! rm /u01/app/oracle/oradata/ora11g/users01.dbf- db 강제 종료
SYS@ora11g> shu abort
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SYS@ora11g> conn / as sysdba
Connected.
SYS@ora11g> shu abort
ORACLE instance shut down.● 3. 문제되는것 offline 후 db open
- mount 단계까지 startup
SYS@ora11g> startup
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
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'- recover file 조회
SYS@ora11g> select * from v$recover_file;
FILE# ONLINE ONLINE_
---------- ------- -------
ERROR CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
4 ONLINE ONLINE
FILE NOT FOUND 0- 문제되는 datafile offline
SYS@ora11g> alter database datafile 4 offline;
Database altered.- db open
SYS@ora11g> alter database open;
Database altered.● 4. restore 후에 recover
- restore
RMAN> restore tablespace users;- recover (압축된 아카이브 파일로 recover)
RMAN> recover tablespace users;● 5. 테이블스페이스 online
RMAN> sql 'alter tablespace users online';
sql statement: alter tablespace users online- SHOW ALL
: controlfile auto백업으로 해준다 (controlfile은 중요하므로)
RMAN> show all;
CONFIGURE CONTROLFILE AUTOBACKUP OFF;- controlfile autobackup on
RMAN> CONFIGURE CONTROFILE AUTOBACKUP OFF;
- 불완전한 복구를 할때
- 아카이브파일 깨졋을때
- time base recovery
: DDL 문자 잘못했을때- current한 리두로그 그룹이 깨졌을때
▶ 6. 백업받지 않은 datafile 손상시 복구 시나리오
● 1. 작업
drop tablespace dba_tbs including contents and datafiles;
create tablespace data_tbs datafile
'/u01/app/oracle/oradata/ora11g/data01.dbf' size 5m;
create table hr.dept_temp tablespace data_tbs as select * from hr.departments;● 2. 장애 발생
SYS@ora11g> ! rm /u01/app/oracle/oradata/ora11g/data01.dbf● 3. db 내린후 mount 단계까지 start
SYS@ora11g> shutdown abort
ORACLE instance shut down.- rman 접속후 mount 단계까지
[oracle@oracle ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jan 24 21:26:56 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup mount
Oracle instance started
database mounted
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers ● 4. list failure 로 장애 확인
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
105 HIGH OPEN 24-JAN-24 One or more non-system datafiles are missing- detail로 봐 몇번 datafile이 손상되었는지 확인
RMAN> list failure 105 detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
105 HIGH OPEN 24-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 105
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
4530 HIGH OPEN 24-JAN-24 Datafile 3: '/u01/app/oracle/oradata/ora11g/data01.dbf' is missing
Impact: Some objects in tablespace DATA_TBS might be unavailable테이블스페이스 구조, redo 정보가 변경될때
CONFIGURE CONTROLFILE AUTOBACKUP ON 이므로 자동으로 컨트롤 파일이 백업된다.
● 5. 문제되는 datafile offline 변경후 db open
RMAN> sql 'alter database datafile 3 offline';
sql statement: alter database datafile 3 offline- db open
RMAN> alter database open;
database opened● 6. 문제되는 데이터파일 대체할것 생성
RMAN> sql " alter database create datafile ''/u01/app/oracle/oradata/ora11g/data01.dbf'' ";
using target database control file instead of recovery catalog
sql statement: alter database create datafile ''/u01/app/oracle/oradata/ora11g/data01.dbf''● 7. tablespace online으로 변경
RMAN> sql 'alter tablespace data_tbs online';
sql statement: alter tablespace data_tbs online
- db open 시점
: 테이블스페이스 레벨에서 online offline 변경 가능하지만- db mount 시점
테이블스페이스 레벨에서 online offline 변경 불가
data 레벨에서 on/off 변경
▶ 7. 특정 datafile 이 깨졌을때 datafile레벨로 백업,복구 시나리오
● 1. 작업
- 테이블스페이스 파셜 백업
RMAN> backup tablespace data_tbs;- 특정 테이블스페이스 백업 리스트
RMAN> list backup of tablespace data_tbs;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
19 Full 1.07M DISK 00:00:00 24-JAN-24
BP Key: 19 Status: AVAILABLE Compressed: NO Tag: TAG20240124T221134
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_24/o1_mf_nnndf_TAG20240124T221134_lv2346c8_.bkp
List of Datafiles in backup set 19
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 1855537 24-JAN-24 /u01/app/oracle/oradata/ora11g/data01.dbf- 테이블스페이스에 datfile 추가
alter tablespace data_tbs add datafile
'/u01/app/oracle/oradata/ora11g/data02.dbf' size 10m;- 테이블생성
create table hr.emp_2040125 tablespace data_tbs
as select * from hr.employees;- 여러번 insert 해줘서 새로만든 datafile에 들어가게
insert into hr.emp_2040125
select * from hr.emp_2040125;1 Bit = 2진수 (0과 1)
1 Byte (1바이트) = 8bit (8비트) << 정보의 기본 단위
1 KB (킬로바이트) = 2의 10승 바이트, 2를 10번 곱한 것 = 1024 byte
1 MB (메가바이트) = 2의 20승 바이트 (1024KB)
1 GB (기가바이트) = 2의 30승 바이트 (1024 MB)
1 TB (테라바이트) = 2의 40승 바이트 (1024 GB)
1 PB (페타바이트) = 2의 50승 바이트 (1024 TB)
1 EB (엑사바이트) = 2의 60승 바이트 (1024 PB)
1 ZB (제타바이트) = 2의 70승 바이트 (1024 EB)
1 YB (요타바이트) = 2의 80승 바이트 (1024 ZB)
- 테이블 사이즈보기
select bytes/1024/1024 mb
from dba_segments
where segment_name = 'EMP_2040125'
and owner = 'HR';
MB
----------
5- 어느 segment에 있는지 확인
select f.file_name
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'EMP_2040125'
and e.owner = 'HR';
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/data02.dbf
/u01/app/oracle/oradata/ora11g/data02.dbf
/u01/app/oracle/oradata/ora11g/data02.dbf
/u01/app/oracle/oradata/ora11g/data01.dbf
/u01/app/oracle/oradata/ora11g/data01.dbf- 필요한 backup 확인
RMAN> report need backup;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
7 0 /u01/app/oracle/oradata/ora11g/data02.dbf
- datafile 레벨로 backup
RMAN> backup datafile '/u01/app/oracle/oradata/ora11g/data02.dbf';
- 테이블스페이스 레벨로 backup list 확인
RMAN> list backup of tablespace data_tbs;
List of Backup Sets
===================
....
List of Datafiles in backup set 19
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 1855537 24-JAN-24 /u01/app/oracle/oradata/ora11g/data01.dbf
List of Datafiles in backup set 22
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
7 Full 1856486 24-JAN-24 /u01/app/oracle/oradata/ora11g/data02.dbf- 테이블 생성
create table hr.dept_2040125 tablespace data_tbs
as select * from hr.dept;- 생성된 테이블 위치 확인
select f.file_name
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'DEPT_2040125'
and e.owner = 'HR';
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/data02.dbf● 2. 장애발생
SYS@ora11g> ! rm /u01/app/oracle/oradata/ora11g/data02.dbf● 3. rman 접속후 장애 확인
- list failure
: 안보일때도 있지만 alertlog 를 보면된다.
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
105 HIGH OPEN 24-JAN-24 One or more non-system datafiles are missing- failure detail 로 확인
RMAN> list failure 105 detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
105 HIGH OPEN 24-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 105
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
4730 HIGH OPEN 24-JAN-24 Datafile 7: '/u01/app/oracle/oradata/ora11g/data02.dbf' is missing
Impact: Some objects in tablespace DATA_TBS might be unavailable● 4. 문제되는 datafile offline 변경후 dbopen
RMAN> sql 'alter database datafile 7 offline';
sql statement: alter database datafile 7 offline- db open;
RMAN> alter database open;
database opened- 그 문제되는 데이터파일의 테이블스페이스 다른 데이터파일 문제있는지 확인
SYS@ora11g> select count(*) from hr.dept_temp;
COUNT(*)
----------
27- 테이블스페이스 상태확인 -> RECOVER
SYS@ora11g> select a.file#, b.name tbs_name, a.name file_name,
a.status, a.checkpoint_change#
from v$datafile a, v$tablespace b
where a.ts# = b.ts#;
----
1 SYSTEM /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM 1852812
2 SYSAUX /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE 1852812
3 DATA_TBS /u01/app/oracle/oradata/ora11g/data01.dbf ONLINE 1855537
4 USERS /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE 1852812
5 EXAMPLE /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE 1852812
6 UNDOTBS /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE 1852812
7 DATA_TBS /u01/app/oracle/oradata/ora11g/data02.dbf RECOVER 1856486● 5. datafile 레벨로 restore , recover
RMAN> restore datafile 7;
Starting restore at 24-JAN-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=180 device type=DISK
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 24-JAN-24- datafile레벨로 recover
RMAN> recover datafile 7;
Starting recover at 24-JAN-24
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 24-JAN-24● 6. datafile online변경
RMAN> sql 'alter database datafile 7 online';
sql statement: alter database datafile 7 online- 확인
select a.file#, b.name tbs_name, a.name file_name,
a.status, a.checkpoint_change#
from v$datafile a, v$tablespace b
where a.ts# = b.ts#;
---
1 SYSTEM /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM 1877180
2 SYSAUX /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE 1877180
3 DATA_TBS /u01/app/oracle/oradata/ora11g/data01.dbf ONLINE 1877180
4 USERS /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE 1877180
5 EXAMPLE /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE 1877180
6 UNDOTBS /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE 1877180
7 DATA_TBS /u01/app/oracle/oradata/ora11g/data02.dbf ONLINE 1877833▶ 8. db가 내려가있는 상태에서 undo 테이블스페이스의 datafile 삭제시 복구 시나리오
● 1. 장애 발생 : undo datafile 삭제
SYS@ora11g> ! rm /u01/app/oracle/oradata/ora11g/undotbs01.dbf- alert log 창에 에러발생
ORA-12012: error on auto execute of job 3
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/u01/app/oracle/oradata/ora11g/undotbs01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Thu Jan 25 00:38:16 2024
Checker run found 1 new persistent data failures● 2. db 강제종료후 mount단계까지 open
SYS@ora11g> shutdown abort
ORACLE instance shut down.
SYS@ora11g> startup mount
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
● 3. rman 접속후 장애 확인
[oracle@oracle ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 25 00:39:58 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (DBID=256148002, not open)- list failure
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
105 HIGH OPEN 24-JAN-24 One or more non-system datafiles are missing- failure detail
RMAN> list failure 105 detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
105 HIGH OPEN 24-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 105
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
4810 HIGH OPEN 25-JAN-24 Datafile 6: '/u01/app/oracle/oradata/ora11g/undotbs01.dbf' is missing
Impact: Some objects in tablespace UNDOTBS might be unavailable- 처음부터 advise failure 로 보면 편하다.
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
105 HIGH OPEN 24-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 105
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
4810 HIGH OPEN 25-JAN-24 Datafile 6: '/u01/app/oracle/oradata/ora11g/undotbs01.dbf' is missing
Impact: Some objects in tablespace UNDOTBS 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
=======================
1. If file /u01/app/oracle/oradata/ora11g/undotbs01.dbf was unintentionally renamed or moved, restore it
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 6
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/ora11g/ora11g/hm/reco_2733741146.hm● 4. restore후 , recover
- undotbs 테이블스페이스 백업있는지 확인
RMAN> list backup of tablespace undotbs;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
15 Full 1.28G DISK 00:00:36 24-JAN-24
BP Key: 15 Status: AVAILABLE Compressed: NO Tag: TAG20240124T204433
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_24/o1_mf_nnndf_TAG20240124T204433_lv1y11gz_.bkp
List of Datafiles in backup set 15
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
6 Full 1829846 24-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
- restore
RMAN> restore datafile 6;- recover
RMAN> recover datafile 6;● 5. datafile online후에 db open
RMAN> sql 'alter database datafile 6 online';
sql statement: alter database datafile 6 online- 확인
select a.file#, b.name tbs_name, a.name file_name,
a.status, a.checkpoint_change#
from v$datafile a, v$tablespace b
where a.ts# = b.ts#;
---
1 SYSTEM /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM 1904080
2 SYSAUX /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE 1904080
4 USERS /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE 1904080
5 EXAMPLE /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE 1904080
6 UNDOTBS /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE 1904080
---- db open
RMAN> alter database open;
database openedOFFLINE 으로 설정할 수 없는 테이블 스페이스는
- SYSTEM
- 활성화 되어있는 UNDO
- 기본 TEMP 테이블 스페이스
select a.group#,b.thread#,b.sequence#, member, b.bytes/1024/1024 mb, b.archived, b.status,
b.first_change#, b.next_change#, b.next_time
from v$logfile a, v$log b
where a.group# = b.group#
order by 1,2;
-----
1 1 37 /u01/app/oracle/oradata/ora11g/redo01.log 50 NO CURRENT 1904079 281474976710655
2 1 35 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES INACTIVE 1852811 1877179 24/01/24
3 1 36 /u01/app/oracle/oradata/ora11g/redo03.log 50 YES INACTIVE 1877179 1904079 24/01/25create tablespace data_tbs datafile
'/u01/app/oracle/oradata/ora11g/data_tbs01.log' size 10m;
RMAN> delete obsolete;
tem altered.
SYS@ora11g> /
System altered.
SYS@ora11g> /
System altered.
cold 백업 있는 상태에서
tbs 테이블스페이스, 테이블 생성
다시 backup database;
tbs 테이블스페이스 삭제
time base recovery 할려면
현재 컨트롤파일은 tbs 테이블스페이스가 없으므로
그전의 백업컨트롤 파일로 restore 하고 ▶ 9. 테이블스페이스 drop시에 time base recovery
● 1. 테이블스페이스 삭제
- tbs 테이블스페이스 삭제
drop tablespace data_tbs including contents and datafiles;● 2. nomount 상태로 controlfile restore
RMAN> startup nomount;
connected to target database (not started)
Oracle instance started
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes- controlfile restore 하고
: 백업된 컨트롤파일에는 삭제된 테이블스페이스 있다는 시나리오
RMAN> restore controlfile from
'/u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159150903_lv2ks7tc_.bkp';2>
Starting restore at 25-JAN-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=171 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 25-JAN-24● 3. mount 후 time base recovery하기전 날째 포맷변경
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1- 날짜 포맷변경
RMAN> sql 'alter session set nls_date_format ="yyyy/mm/dd hh24:mi:ss"';
sql statement: alter session set nls_date_format ="yyyy/mm/dd hh24:mi:ss"● 4. alertlog 창을 보고 테이블스페이스 삭제한 시간 전 확인하기
- alert log 파일
Thu Jan 25 02:22:30 2024
drop tablespace data_tbs including contents and datafiles
Deleted file /u01/app/oracle/oradata/ora11g/data_tbs01.log
Completed: drop tablespace data_tbs including contents and datafiles● 5. 전으로 recover
run {
set until time '2024/01/25 02:21:55';
restore database;
recover database;
}● 6. reset logs로 open
alter database open resetlogs;- 시간정보 직접수정
date -s "2024-01-26 11:01:55"▶ 10. drop table 잘못했을시에 crone db를 이용하여 복구
● 1. 작업
- 테이블 생성후, 로그스위치, 테이블 삭제
alter system switch logfile;
create table hr.emp_new as select * from hr.employees;
select count(*) from hr.emp_new;
SYS@ora11g> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
25-JAN-24 05.34.45.706768 AM +09:00
drop table hr.emp_new purge;
alter system switch logfile;
alter system switch logfile;select a.group#,b.thread#,b.sequence#, member, b.bytes/1024/1024 mb, b.archived, b.status,
b.first_change#, b.next_change#, b.next_time
from v$logfile a, v$log b
where a.group# = b.group#
order by 1,2;
1 1 7 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES ACTIVE 1940601 1940683 24/01/25
2 1 8 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES ACTIVE 1940683 1940711 24/01/25
3 1 9 /u01/app/oracle/oradata/ora11g/redo03.log 50 NO CURRENT 1940711 281474976710655 ● 2. 장애발생
- 테이블 없는것을 알아차림
SYS@ora11g> select count(*) from hr.emp_new;
select count(*) from hr.emp_new
*
ERROR at line 1:
ORA-00942: table or view does not exist● 3. 복제 데이터베이스 생성시 필요한 데이터파일, 리두로그파일, 아카이브 파일 복사
: sysaux, system, undo 필수
- 클론 db의 초기파라미터 파일 생성
[oracle@oracle clone]$ vi $ORACLE_HOME/dbs/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'
---[oracle@oracle ~]$ mkdir clone
[oracle@oracle ~]$ cd clone
[oracle@oracle clone]$ pwd
/home/oracle/clone- rman의 list backup을 복사
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
39 Full 1.22G DISK 00:00:22 25-JAN-24
BP Key: 39 Status: AVAILABLE Compressed: NO Tag: TAG20240125T052102
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T052102_lv2w9gpv_.bkp
List of Datafiles in backup set 39
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1939955 25-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf
2 Full 1939955 25-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
4 Full 1939955 25-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf
5 Full 1939955 25-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf
6 Full 1939955 25-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
40 Full 9.42M DISK 00:00:00 25-JAN-24
BP Key: 40 Status: AVAILABLE Compressed: NO Tag: TAG20240125T052127
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159161687_lv2wb7tv_.bkp
SPFILE Included: Modification time: 25-JAN-24
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 1939969 Ckp time: 25-JAN-24
- control 파일 복사
[oracle@oracle clone]$ cp -av /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159161687_lv2wb7tv_.bkp .
‘/u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159161687_lv2wb7tv_.bkp’ -> ‘./o1_mf_s_1159161687_lv2wb7tv_.bkp’- 아카이브 파일 복사
[oracle@oracle clone]$ cp -av /home/oracle/arch1/*.* /home/oracle/clone
‘/home/oracle/arch1/arch_1_2_1159152478.arc’ -> ‘/home/oracle/clone/arch_1_2_1159152478.arc’
‘/home/oracle/arch1/arch_1_3_1159152478.arc’ -> ‘/home/oracle/clone/arch_1_3_1159152478.arc’
‘/home/oracle/arch1/arch_1_4_1159152478.arc’ -> ‘/home/oracle/clone/arch_1_4_1159152478.arc’
‘/home/oracle/arch1/arch_1_5_1159152478.arc’ -> ‘/home/oracle/clone/arch_1_5_1159152478.arc’
‘/home/oracle/arch1/arch_1_6_1159152478.arc’ -> ‘/home/oracle/clone/arch_1_6_1159152478.arc’
‘/home/oracle/arch1/arch_1_7_1159152478.arc’ -> ‘/home/oracle/clone/arch_1_7_1159152478.arc’
‘/home/oracle/arch1/arch_1_8_1159152478.arc’ -> ‘/home/oracle/clone/arch_1_8_1159152478.arc’- 데이터파일 복사
[oracle@oracle ~]$ cp -av /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T052102_lv2w9gpv_.bkp /home/oracle/clone
‘/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T052102_lv2w9gpv_.bkp’ -> ‘/home/oracle/clone/o1_mf_nnndf_TAG20240125T052102_lv2w9gpv_.bkp’● 4. . oraenv 로 접속
- 만약 sqlplus 접속이 안되면
ps -ef | grep clone을 조회하여
kill -9 pmon번호하면 된다.
그리고startup pfile='경로' nomount
[oracle@oracle clone]$ . 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 clone]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 25 06:01:06 2024
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.SYS@clone> startup nomount
ORACLE instance started.
Total System Global Area 167407616 bytes
Fixed Size 1363300 bytes
Variable Size 109052572 bytes
Database Buffers 50331648 bytes
Redo Buffers 6660096 bytes● 5. RMAN 클론 데이터베이스에 연결
[oracle@oracle ~]$ rman auxiliary /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jan 26 11:04:18 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to auxiliary database: CLONE (not mounted)- 클론db를 테이블 삭제된 시간 전으로
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 4 to '/home/oracle/clone/users01.dbf';
set newname for datafile 5 to '/home/oracle/clone/example01';
set newname for datafile 6 to '/home/oracle/clone/undotbs01.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 10.04.30','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;
}● 7. 클론 db에서 복원된 테이블 export
[oracle@oracle ~]$ exp system/oracel tables=hr.emp_new file=emp_temp.dmp● 8. 다시 운영 db로 돌아가서 import
[oracle@oracle ~]$ . oraenv
ORACLE_SID = [clone] ? ora11g
The Oracle base remains unchanged with value /u01/app/oracle● 9. import
[oracle@oracle ~]$ imp system/oracle tables=emp_new file=emp_temp.dmp schemas=hr● 10. 접속해서 테이블 확인
[oracle@oracle ~]$ sqlplus / as sysdba
SYS@ora11g> select count(*) from hr.emp_new▶ 11. drop table 잘못했을시에 crone db를 이용하여 복구
● 1. 작업
- 테이블생성로그, 스위치 발생
alter system switch logfile;
create table hr.new_20240126
as select * from hr.employees;
alter system switch logfile;- 로그파일 확인
select a.group#,b.thread#,b.sequence#, member, b.bytes/1024/1024 mb, b.archived, b.status,
b.first_change#, b.next_change#, b.next_time
from v$logfile a, v$log b
where a.group# = b.group#
order by 1,2;
---
1 1 1 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES ACTIVE 1584059 1591748 24/01/26
2 1 2 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES ACTIVE 1591748 1591777 24/01/26
3 1 3 /u01/app/oracle/oradata/ora11g/redo03.log 50 NO CURRENT 1591777 281474976710655 - 백업 만들 디렉토리 생성
[oracle@oracle ~]$ mkdir rman_clone- delete backup
RMAN> list backup;
specification does not match any backup in the repository
RMAN> delete backup;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
specification does not match any backup in the repository
- 백업 compressed 위치,포맷지정
%d : 데이터베이스이름
%U : %u%p%c
%u : 백업셋 번호와 백업셋이 생성된 시간을 조합한 8자리 번호
%p : 백업셋 피스 번호
%c : 중복된 백업 피스셋 내에서 백업 피스의 고유번호
%T : 날짜RMAN> backup as compressed backupset format '/home/oracle/rman_clone/%d_%U' database include current controlfile;
- 필요없는 백업본 지우기
- 정책상 필요없는 백업본지우고 , 만료된 아카이브 지우기
RMAN> delete obsolete RMAN> crosscheck archivelog all; RMAN> list expired archivelog all; RMAN> delete expired archivelog all;
- 다시한번 확인 (이미지 껍데기만 남아있는것도 있다)
RMAN> report obsolete; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 1 Report of obsolete backups and copies Type Key Completion Time Filename/Handle -------------------- ------ ------------------ -------------------- Control File Copy 2 11-JAN-24 /home/oracle/backup/arch/hot_20240112/control01.ctl
- crosscheck copy, delete
RMAN> crosscheck copy; released channel: ORA_DISK_1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=181 device type=DISK specification does not match any datafile copy in the repository specification does not match any archived log in the repository validation failed for control file copy control file copy file name=/home/oracle/backup/arch/hot_20240112/control01.ctl RECID=2 STAMP=1158016454 Crosschecked 1 objectsRMAN> delete copy;
● 2. 장애발생 : 테이블삭제
- 시간확인
SYS@ora11g> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
26-JAN-24 02.13.16.106968 PM +09:00- 테이블 삭제
SYS@ora11g> drop table hr.new_20240126;
Table dropped.- 로그 스위치 발생 (하지않으면 아카이브에 안떨어지니깐)
SYS@ora11g> alter system switch logfile;- 아카이브 파일 확인 (delete obsolete 한것 빼고)
select sequence# , name from v$archived_log
where name is not null;
--------------------
3 /home/oracle/arch1/arch_1_3_1158057974.arc
4 /home/oracle/arch1/arch_1_4_1158057974.arc
5 /home/oracle/arch1/arch_1_5_1158057974.arc● 4. 클론 db 초기파라미터 생성
- 클론 db pfile 생성
: datafile, logfile 이름 변경도 넣어주었다.
[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/')- 아카이브 파일 복사
[oracle@oracle ~]$ cp -av /home/oracle/arch1/*.* /home/oracle/rman_clone/
‘/home/oracle/arch1/arch_1_3_1158057974.arc’ -> ‘/home/oracle/rman_clone/arch_1_3_1158057974.arc’
‘/home/oracle/arch1/arch_1_4_1158057974.arc’ -> ‘/home/oracle/rman_clone/arch_1_4_1158057974.arc’
‘/home/oracle/arch1/arch_1_5_1158057974.arc’ -> ‘/home/oracle/rman_clone/arch_1_5_1158057974.arc’- 확인
[oracle@oracle ~]$ ls /home/oracle/rman_clone
arch_1_3_1158057974.arc arch_1_5_1158057974.arc ORA11G_032hicpp_1_1
arch_1_4_1158057974.arc ORA11G_022hicom_1_1- 논리적,물리적 백업셋 일치하는지 확인
RMAN> crosscheck backupset;
using channel ORA_DISK_1
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/rman_clone/ORA11G_022hicom_1_1 RECID=1 STAMP=1159279382
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/rman_clone/ORA11G_032hicpp_1_1 RECID=2 STAMP=1159279418
Crosschecked 2 objects● 5. 클론 db로 접속
- 오라클 홈 경로확인 (sqlplus 띄우기 위해서)
[oracle@oracle ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0.4/db_1- 클론 db접속
[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- sqlplus 접속
[oracle@oracle ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 26 14:29:10 2024
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options● 6. nomount 단계로 startup
SYS@clone> startup pfile=$ORACLE_HOME/dbs/initclone.ora nomount
ORACLE instance started.
Total System Global Area 167407616 bytes
Fixed Size 1363300 bytes
Variable Size 109052572 bytes
Database Buffers 50331648 bytes
Redo Buffers 6660096 bytes● 7. rman auxiliary 접속후
[oracle@oracle ~]$ rman auxiliary /- 테이블 DROP하기전으로 time base
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:12:20','yyyy-mm-dd hh24:mi:ss')"● 8. export 후에
● 8. 원래 db에 import
▶ 12. RMAN에서의 cancel base recovey
● 1. 작업
- 자동 컨트롤 파일 백업 위치,포맷 설정
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/backup/rman/%F';- 백업 새로 받기 : 위치,포맷 설정
RMAN> delete backup;
RMAN> list backup;
RMAN> backup as compressed backupset format '/home/oracle/backup/rman/%d_%U_%T' database;- 리두로그 확인
select a.group#,b.thread#,b.sequence#, member, b.bytes/1024/1024 mb, b.archived, b.status,
b.first_change#, b.next_change#, b.next_time
from v$logfile a, v$log b
where a.group# = b.group#
order by 1,2;- 테이블 생성
-- SEQUENCE# 8번
create table hr.emp_arch
as select * from hr.employees;
alter system switch logfile;
-- SEQUENCE# 9번
create table hr.dept_arch
as select * from hr.departments;
alter system switch logfile;● 2. 장애발생
- users datafile 삭제
[oracle@oracle ~]$ rm /u01/app/oracle/oradata/ora11g/users01.dbf- 아카이브 파일 삭제 (두번째 테이블생성한 아카이브파일)
[oracle@oracle ~]$ cd arch1
[oracle@oracle arch1]$ ls
arch_1_10_1158057974.arc arch_1_5_1158057974.arc arch_1_8_1158057974.arc
arch_1_3_1158057974.arc arch_1_6_1158057974.arc arch_1_9_1158057974.arc
arch_1_4_1158057974.arc arch_1_7_1158057974.arc
[oracle@oracle arch1]$ rm arch_1_9_1158057974.arc● 3. rman 접속후 restore , recover
- rman 접속
[oracle@oracle ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jan 26 16:41:16 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (DBID=256148002, not open)- list failure
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
105 HIGH OPEN 26-JAN-24 One or more non-system datafiles are missing- list failure detail
RMAN> list failure 105 detail;- users 테이블스페이스 restore 해주고
RMAN> restore tablespace users;
Starting restore at 26-JAN-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK
...............
Finished restore at 26-JAN-24- 아카이브 파일 있는줄알고 recover
: 에러발생
RMAN> recover tablespace users;
RMAN-03002: failure of recover command at 01/26/2024 16:41:42
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 9 and starting SCN of 1601289 found to restore● 4. 아카이브 파일없는것 확인, nomount 단계까지 올린다(컨트롤파일이 손상되었을수도 있으므로)
RMAN> shutdown abort
Oracle instance shut down
RMAN> startup nomount
connected to target database (not started)
Oracle instance started
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes- 확인 (진작에 list backup으로 봐둬야한다)
[oracle@oracle rman]$ ls
ORA11G_042hil3t_1_1_20240126 ORA11G_052hil50_1_1_20240126● 5. controlfile resotre 후 mount 단계까지 startup
RMAN> restore controlfile from '/home/oracle/backup/rman/ORA11G_052hil50_1_1_20240126';
Starting restore at 26-JAN-24
using channel ORA_DISK_1
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- mount까지 open
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1● 6. scn번호를 이용하여 그전까지 recover
: cancel base recovery 랑 똑같은것이다.
RMAN> recover database until scn 1601492;
- 만약 resetlogs 로 open한것을 백업한것을 이용하면 에러가 난다.
● 7. open resetlogs
RMAN> alter database open resetlogs;● 8. incarnatio 확인후 그전의 scn번호로 recover
- 데이터베이스가 복구 또는 리셋되는 시점
RMAN> list incarnation of database;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ORA11G 256148002 PARENT 1 25-AUG-13
2 2 ORA11G 256148002 PARENT 635002 07-DEC-23
3 3 ORA11G 256148002 PARENT 1513641 11-JAN-24
4 4 ORA11G 256148002 PARENT 1580233 12-JAN-24
5 5 ORA11G 256148002 PARENT 1580748 12-JAN-24
6 6 ORA11G 256148002 CURRENT 1584059 12-JAN-24- db 종료후 mount단계까지 open
RMAN> shutdown abort
RMAN> startup mount- 3번으로 reset
RMAN> RESET DATABASE TO INCARNATION 3;- set, restore, recover
RMAN> run {
set until scn=1580748;
restore database;
recover database;
}● 9.
RMAN> alter database open resetlogs;▶ 13. controlfile 손상시 복구 시나리오
● 1. 백업작업
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;
delete backupset;
backup as compressed backupset database;
list backup;
report obsolete;
delete obsolete;
crosscheck archivelog all;
list expired archivelog all;
delete expired archivelog all;
report obsolete;
crosscheck copy;
delete copy;
report obsolete;- list backup
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 312.70M DISK 00:00:24 26-JAN-24
BP Key: 3 Status: AVAILABLE Compressed: YES Tag: TAG20240126T192257
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_26/o1_mf_nnndf_TAG20240126T192257_lv7201v5_.bkp
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1599371 26-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf
2 Full 1599371 26-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 Full 1599371 26-JAN-24 /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf
4 Full 1599371 26-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf
5 Full 1599371 26-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf
6 Full 1599371 26-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 1.03M DISK 00:00:01 26-JAN-24
BP Key: 4 Status: AVAILABLE Compressed: YES Tag: TAG20240126T192257
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_26/o1_mf_ncsnf_TAG20240126T192257_lv720vvk_.bkp
SPFILE Included: Modification time: 26-JAN-24
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 1599385 Ckp time: 26-JAN-24● 2. 장애발생 : controlfile 삭제
SYS@ora11g> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/control01.ctl
SYS@ora11g> ! rm /u01/app/oracle/oradata/ora11g/control01.ctl
SYS@ora11g> alter system checkpoint;
System altered.
- 에러발생
SYS@ora11g> shutdown immediate
Database closed.
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/ora11g/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3● 3. 컨트롤파일이 깨졋으므로 nomount 단계까지 startup
- db 강제종료
SYS@ora11g> shutdown abort
ORACLE instance shut down.- nomount
RMAN> startup nomount
Oracle instance started
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes● 4. controlfile restore 후, mount
- restore 두가지 방법이 있다.
- restore auto backup
RMAN> restore controlfile from auto backup;
- 컨트롤파일 경로를 이용하여 restore
RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_26/o1_mf_ncsnf_TAG20240126T192257_lv720vvk_.bkp';
- mount
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1● 5. recover 후, open resetlogs
- recover
RMAN> recover database;- 이런 에러가뜬다? -> 8번으로
RMAN> recover database;
Starting recover at 26-JAN-24
using channel ORA_DISK_1
starting media recovery
unable to find archived log
archived log thread=1 sequence=4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/26/2024 19:46:04
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 4 and starting SCN of 1599371- open resetlog
RMAN> alter database open resetlogs;● 6. backup 다시받고 필요없는 backup 삭제
RMAN> backup as compressed backupset database;
RMAN> list backup;
RMAN> report obsolete;
RMAN> delete obsolete;
RMAN> list backup;● 8. recover 안될때 => resetlogs때문이다.
- 에러난것을 보면 SCN: 1599371 전으로 가야한다.
따라서 6번
: 데이터베이스가 복원되거나 resetlogs 작업이 수행된 경우와 같은 데이터베이스의 변경 내역을 기록합니다.
RMAN> list incarnation of database;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ORA11G 256148002 PARENT 1 25-AUG-13
2 2 ORA11G 256148002 PARENT 635002 07-DEC-23
3 3 ORA11G 256148002 PARENT 1513641 11-JAN-24
4 4 ORA11G 256148002 PARENT 1580233 12-JAN-24
5 5 ORA11G 256148002 PARENT 1580748 12-JAN-24
6 6 ORA11G 256148002 PARENT 1584059 12-JAN-24
7 7 ORA11G 256148002 CURRENT 1911228 25-JAN-24- db강제종료후 mount
RMAN> shutdown abort
Oracle instance shut down
RMAN> startup mount- 6번으로 INCARNATION
RMAN> RESET DATABASE TO INCARNATION 6;
database reset to incarnation 6- restore, recover
RMAN> run {
set until scn=1580748;
restore database;
recover database;
}- resetlogs로 open
RMAN> alter database open resetlogs;
database opened- 백업본 정리후, 다시백업 받기
RMAN> backup as compressed backupset database;
RMAN> list backup;
RMAN> report obsolete;
RMAN> delete obsolete;
RMAN> list backup;
RMAN> list incarnation;
RMAN> drop catalog;
recovery catalog owner is
enter DROP CATALOG command again to confirm catalog removal
불필요한 resetlogs 삭제
※ drop catalog 한후에는
timebase recovey 불가능하므로 조심해서 삭제해야한다.
또한 다시 backup 받아야한다.
● 1. drop catalog
RMAN> list incarnation;
RMAN> drop catalog;
recovery catalog owner is
enter DROP CATALOG command again to confirm catalog removal● 2. controlfile trace 뜨기
SYS@ora11g> alter database backup controlfile to trace as '/home/oracle/con.sql';
Database altered.● 3. db nomount 단계로 startup 한 후에
, trace한 controlfile로 재생성
SYS@ora11g> shutdown immediate- controlfile trace sql문 복사
[oracle@oracle ~]$ vi con.sql- startup mount
SYS@ora11g> startup nomount;- controlfile 재생성
SYS@ora11g> CREATE CONTROLFILE REUSE DATABASE "ORA11G" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/ora11g/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/ora11g/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/ora11g/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/ora11g/system01.dbf',
'/u01/app/oracle/oradata/ora11g/sysaux01.dbf',
'/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf',
'/u01/app/oracle/oradata/ora11g/users01.dbf',
'/u01/app/oracle/oradata/ora11g/example01.dbf',
'/u01/app/oracle/oradata/ora11g/undotbs01.dbf'
CHARACTER SET AL32UTF8
; 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
Control file created.● 4. recover 후 open
SYS@ora11g> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery requiredSYS@ora11g> alter database open;
Database altered.● 5. rman 접속후 확인
[oracle@oracle ~]$ rman target /
RMAN> list incarnation;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ORA11G 256148002 CURRENT 1600117 26-JAN-24
● 6. 재생성한 controlfile 에는 tempfile은 없으므로 trace딴 controlfile 가서 복붙
SYS@ora11g> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/ora11g/temp01.dbf'
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M;
Tablespace altered.● 7. 다시 백업받기
RMAN> list backup;
using target database control file instead of recovery catalog
specification does not match any backup in the repository
RMAN> backup as compressed backupset database;▶ 14. db 내려간 상태에서 spfile, pfile 손상시 복구 시나리오
● 1. 장애발생
[oracle@oracle db_1]$ cd $ORACLE_HOME/dbs
[oracle@oracle dbs]$ ls
hc_clone.dat initclone.ora initora11g.ora lkORA11G snapcf_ora11g.f
hc_ora11g.dat init.ora lkCLONE orapwora11g spfileora11g.ora
[oracle@oracle dbs]$ rm initora11g.ora
[oracle@oracle dbs]$ rm spfileora11g.ora- 장애발생
SYS@ora11g> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0.4/db_1/dbs/initora11g.ora'● 2. rman 접속후 nomout 단계로 열기
- spfile, pfile 이없는데 nomout 단계 어떻게여나?
: spfile이없어서 임시로 만든 메모리
[oracle@oracle ~]$ rman target /
RMAN> startup nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0.4/db_1/dbs/initora11g.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 1071333376 bytes
Fixed Size 1369420 bytes
Variable Size 281021108 bytes
Database Buffers 784334848 bytes
Redo Buffers 4608000 bytes● 3. DBID 값을 이용한 set
DBID 값을 꼭 알아야 한다!
: rman접속시에도 보인다.
RMAN> set dbid 256148002
executing command: SET DBID● 4. restore controlfile
- from autobackup
RMAN> restore spfile from autobackup;- 안된다면 경로지정
RMAN> restore spfile from '/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_26/o1_mf_ncsnf_TAG20240126T204412_lv76s67o_.bkp';
Starting restore at 26-JAN-24
using channel ORA_DISK_1
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_26/o1_mf_ncsnf_TAG20240126T204412_lv76s67o_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 26-JAN-24● 5. db 종료후 다시시작
RMAN> shutdown abort
Oracle instance shut down
RMAN> startup
connected to target database (not started)
Oracle instance started
database mounted
database opened
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes- 확인
SYS@ora11g> create pfile from spfile;
File created.
SYS@ora11g> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
.4/db_1/dbs/spfileora11g.ora● 6. 설정이 default로 돌아갔으므로 주의한다.
RMAN>c
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default- controlfile 자동백업 on
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;▶ 15. advise failure 을 통한 datafile 손상시 복구 시나리오
불완전 복구는 불가능하다.
● 1. 장애 발생
SYS@ora11g> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ora11g> ! rm /u01/app/oracle/oradata/ora11g/users01.dbf
SYS@ora11g> startup
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
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'● 2. rman 접속후 복구
- mount단계까지 startup
RMAN> startup mount- advise failure
: Repair script 생성한다.
RMAN> list failure
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
105 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 105
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
5400 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
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file /u01/app/oracle/oradata/ora11g/users01.dbf was unintentionally renamed or moved, restore it
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 4
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/ora11g/ora11g/hm/reco_3845644465.hm- repair script 보기
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_3845644465.hm
contents of repair script:
# restore and recover datafile
restore datafile 4;
recover datafile 4;
sql 'alter database datafile 4 online';- repair failure
RMAN> repair failure;▶ 16. db 내려간 상태에서 advise failure 을 통한 datafile, controlfile 손상시 복구 시나리오
● 1. 장애발생 : datafile, controlfile 손상
SYS@ora11g> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ora11g> ! rm /u01/app/oracle/oradata/ora11g/*.ctl
SYS@ora11g> ! rm /u01/app/oracle/oradata/ora11g/*.dbf
SYS@ora11g> startup
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
ORA-00205: error in identifying control file, check alert log for more info● 2. rman 접속후 nomount에서 controfile restore
- rman 접속
[oracle@oracle ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jan 26 23:30:41 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (DBID=256148002, not open)- mount
RMAN> shutdown abort
RMAN> startup nomount- restore controlfile auto
RMAN> restore controlfile from auto backup;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================- restore controlfile 경로지정
RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/ORA11G/autockup/2024_01_26/o1_mf_s_1159307518_lv7bqgy6_.bkp';
...
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
● 3. mount단계로 올린뒤 advise failure로 recover
- mount
RMAN> startup mount
database is already started
database mounted
released channel: ORA_DISK_1- list failure
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
5950 CRITICAL OPEN 26-JAN-24 System datafile 1: '/u01/app/oracle/oradata/ora11g/system01.dbf' is missing
5947 CRITICAL OPEN 26-JAN-24 Control file needs media recovery
105 HIGH OPEN 26-JAN-24 One or more non-system datafiles are missing- advise failure
RMAN> advise failure;
List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_25/o1_mf_nnndf_TAG20240125T052102_lv2w9gpv_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_26/o1_mf_nnndf_TAG20240126T195113_lv
...- repair failure 스크립트 보기
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_1214789575.hm
contents of repair script:
# restore and recover database
restore database;
recover database;
alter database open resetlogs;- repair failure
RMAN> repair failure;
....
contents of repair script:
# restore and recover database
restore database;
recover database;
alter database open resetlogs;
Do you really want to execute the above repair (enter YES or NO)? y
executing repair script
repair failure complete▶ 17. redolog file, controlfile 손상시 복구 시나리오
● 1. 장애 발생 : redologfile, controlfile 손상
SYS@ora11g> ! rm /u01/app/oracle/oradata/ora11g/*.log
SYS@ora11g> ! rm /u01/app/oracle/oradata/ora11g/*.ctlSYS@ora11g> shutdown abort● 2. nomount 단계에서 controlfile restore
RMAN> startup nomountRMAN> restore controlfile from auto backup;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_27/o1_mf_s_1159315622_lv7lnpqt_.bkp';
...
output file name=/u01/app/oracle/oradata/ora11g/control01.ctl
Finished restore at 27-JAN-2● 3. mount 단계에서 restore, recover
RMAN> startup mount
database is already started
database mounted
released channel: ORA_DISK_1RMAN> restore database;- recover -> 에러
current한 redolog가 없어서 완전복구 불가
따라서, 그전 SCN까지 아카이브파일로 복구
RMAN> recover database;
Starting recover at 27-JAN-24
using channel ORA_DISK_1
starting media recovery
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 recover command at 01/27/2024 00:33:05
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 1 and starting SCN of 1614198● 4. set until scn, restore, recover
- set until scn=번호;
RMAN> run {
set until scn=1614198;
restore database;
recover database;
}2> 3> 4> 5>
executing command: SET until clause
Starting restore at 27-JAN-24
using channel ORA_DISK_1
...
Finished recover at 27-JAN-24● 5. open resetlogs
RMAN> alter database open resetlogs;
database opened● 6. 백업 다시받기
RMAN> backup as compressed backupset database;▶ 18. image copy 백업을 이용한 복구 시나리오
● 1. 작업
- 테이블스페이스 생성후, 그곳에 테이블 생성
SYS@ora11g> create tablespace insa_tbs datafile '/home/oracle/insa_tbs01.dbf' size 10m;
Tablespace created.
SYS@ora11g> create table hr.emp_tbs tablespace insa_tbs
as select * from hr.employees; 2
Table created.- backup이 필요한것 있는지 확인
RMAN> report need backup;
using target database control file instead of recovery catalog
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
3 0 /home/oracle/insa_tbs01.dbf- datafile을 copy를 이용해 백업
RMAN> backup as copy datafile 3 format '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf';
Starting backup at 27-JAN-24- copy 목록확인
RMAN> list copy;- datafile copy 목록확인
RMAN> list datafilecopy all;● 2. 장애 발생 : 데이터파일 삭제
SYS@ora11g> ! rm /home/oracle/insa_tbs01.dbf
SYS@ora11g> shutdown immediate
ORA-03113: end-of-file on communication channel
Process ID: 7943
Session ID: 162 Serial number: 5
SYS@ora11g> conn / as sysdba
Connected to an idle instance.
SYS@ora11g> startup
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/home/oracle/insa_tbs01.dbf'● 3. datafile offline 변경후 db open
- datafile offline
SYS@ora11g> alter database datafile 3 offline;
Database altered.- open
SYS@ora11g> alter database open;
Database altered.● 4. rman 접속후 기존위치가 아닌 백업 파일 위치에 그대로 사용
[oracle@oracle ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Jan 27 02:01:28 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (DBID=256148002)- 기존위치가 아닌 백업 파일 위치에 그대로 사용
RMAN> list datafilecopy all;
using target database control file instead of recovery catalog
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
1 1 A 27-JAN-24 1618122 27-JAN-24
Name: /u01/app/oracle/fast_recovery_area/ORA11G/datafile/o1_mf_system_lv7qv2x0_ .dbf
Tag: TAG20240127T013546
2 2 A 27-JAN-24 1618128 27-JAN-24
Name: /u01/app/oracle/fast_recovery_area/ORA11G/datafile/o1_mf_sysaux_lv7qvkys_ .dbf
Tag: TAG20240127T013546
7 3 A 27-JAN-24 1619547 27-JAN-24
Name: /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf
Tag: TAG20240127T015213
5 4 A 27-JAN-24 1618155 27-JAN-24
Name: /u01/app/oracle/fast_recovery_area/ORA11G/datafile/o1_mf_users_lv7qw958_. dbf
Tag: TAG20240127T013546
3 5 A 27-JAN-24 1618134 27-JAN-24
Name: /u01/app/oracle/fast_recovery_area/ORA11G/datafile/o1_mf_example_lv7qw11j _.dbf
Tag: TAG20240127T013546
4 6 A 27-JAN-24 1618154 27-JAN-24
Name: /u01/app/oracle/fast_recovery_area/ORA11G/datafile/o1_mf_undotbs_lv7qw845 _.dbf
Tag: TAG20240127T013546
- 기존위치가 아닌 백업 파일 위치에 그대로 사용
: restore 할 필요없다.
RMAN> switch datafile 3 to copy;
datafile 3 switched to datafile copy "/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf"만약 그대로 안쓰고 싶으면
RMAN> restore datafile 3;
- online 변경
RMAN> sql 'alter database datafile 3 online';
sql statement: alter database datafile 3 online- 확인
select a.file#, b.name tbs_name, a.name file_name,
a.status, a.checkpoint_change#
from v$datafile a, v$tablespace b
where a.ts# = b.ts#;
SYS@ora11g> select count(*) from hr.emp_tbs;
COUNT(*)
----------
107▶ 19. controlfile, datafile, redologfile 모든 파일이 손상되었고, 디스크가 손상되었을때 복구 시나리오
- user management
- pfile에 controlfile 위치변경
- 변경된 pfile로 nomount 단계까지 startup
- nomount 단계에서 controlfile restore
- mount 단계에서 datafile rename
- datafile restore
- cancel base recovery
- open resetlogs
- 전체 백업
● 1. 작업
[oracle@oracle ~]$ mkdir ora_data
SYS@ora11g> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
.4/db_1/dbs/spfileora11g.oraselect a.group#,b.thread#,b.sequence#, member, b.bytes/1024/1024 mb, b.archived, b.status,
b.first_change#, b.next_change#, b.next_time
from v$logfile a, v$log b
where a.group# = b.group#
order by 1,2;
-----------------
1 1 4 /u01/app/oracle/oradata/ora11g/redo01.log 50 NO CURRENT 1647979 281474976710655
2 1 2 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES INACTIVE 1640001 1647976 24/01/27
3 1 3 /u01/app/oracle/oradata/ora11g/redo03.log 50 YES INACTIVE 1647976 1647979 24/01/27● 2. controlfile, datafile, redologfile 삭제
SYS@ora11g> ! rm /u01/app/oracle/oradata/ora11g/*.*- db 강제종료
SYS@ora11g> shutdown abort
ORACLE instance shut down.● 3. pfile의 controlfile 위치 변경
[oracle@oracle ~]$ cd $ORACLE_HOME/dbs
[oracle@oracle dbs]$ ls
hc_clone.dat init.ora lkDUMMY snapcf_ora11g.f
hc_ora11g.dat initora11g.ora lkORA11G spfileora11g.ora
initclone.ora lkCLONE orapwora11g
[oracle@oracle dbs]$ vi initora11g.ora
--------------------------------------
*.control_files='/home/oracle/ora_data/control01.ctl'● 4. rman 접속후 변경된 pfile로 nomount 단계까지 startup
RMAN> startup pfile=$ORACLE_HOME/dbs/initora11g.ora nomount
Oracle instance started
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes● 5. controlfile restore 후 mount단계로 올림
RMAN> restore controlfile from autobackup;
-- 또는
RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_27/o1_mf_s_1159331338_lv81ztz6_.bkp';
Starting restore at 27-JAN-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=171 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/home/oracle/ora_data/control01.ctl
Finished restore at 27-JAN-24- mount
RMAN> startup mount
database is already started
database mounted
released channel: ORA_DISK_1● 6. 작업형을 사용하여
: 타이핑오류나면 거기까지 controlfile이 변경되므로 다시 controlfile restore 해줘야한다.
- redologfile, datafile RENAME 작업
- restore
- switch
- recover
- open resetlogs
run { sql "alter database rename file ''/u01/app/oracle/oradata/ora11g/redo01.log'' TO ''/home/oracle/ora_data/redo01.log''"; sql "alter database rename file ''/u01/app/oracle/oradata/ora11g/redo02.log'' TO ''/home/oracle/ora_data/redo02.log''"; sql "alter database rename file ''/u01/app/oracle/oradata/ora11g/redo03.log'' TO ''/home/oracle/ora_data/redo03.log''"; set newname for datafile 1 to '/home/oracle/ora_data/system01.dbf'; set newname for datafile 2 to '/home/oracle/ora_data/sysaux01.dbf'; set newname for datafile 4 to '/home/oracle/ora_data/users01.dbf'; set newname for datafile 5 to '/home/oracle/ora_data/example01.dbf'; set newname for datafile 6 to '/home/oracle/ora_data/undotbs01.dbf'; set newname for tempfile 1 to '/home/oracle/ora_data/temp01.dbf'; restore database; switch datafile all; switch tempfile all; recover database; alter database open resetlogs; }
- current한 redolog가 없기때문에 에러에서 나온 SCN 번호까지 복구해야한다.
archived log file name=/home/oracle/arch1/arch_1_2_1159317223.arc thread=1 sequence=2
archived log file name=/home/oracle/arch1/arch_1_3_1159317223.arc thread=1 sequence=3
unable to find archived log
archived log thread=1 sequence=4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/27/2024 05:16:52
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 4 and starting SCN of 1647979● 7. 따라서 처음부터 작업해야한다.
set until scn=번호;
RMAN> shutdown abort
RMAN> startup pfile=$ORACLE_HOME/dbs/initora11g.ora nomount
RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_27/o1_mf_s_1159331338_lv81ztz6_.bkp';
RMAN> startup mountRMAN> run
{
sql "alter database rename file ''/u01/app/oracle/oradata/ora11g/redo01.log'' TO ''/home/oracle/ora_data/redo01.log''";
sql "alter database rename file ''/u01/app/oracle/oradata/ora11g/redo02.log'' TO ''/home/oracle/ora_data/redo02.log''";
sql "alter database rename file ''/u01/app/oracle/oradata/ora11g/redo03.log'' TO ''/home/oracle/ora_data/redo03.log''";
set newname for datafile 1 to '/home/oracle/ora_data/system01.dbf';
set newname for datafile 2 to '/home/oracle/ora_data/sysaux01.dbf';
set newname for datafile 4 to '/home/oracle/ora_data/users01.dbf';
set newname for datafile 5 to '/home/oracle/ora_data/example01.dbf';
set newname for datafile 6 to '/home/oracle/ora_data/undotbs01.dbf';
set newname for tempfile 1 to '/home/oracle/ora_data/temp01.dbf';
restore database;
switch datafile all;
switch tempfile all;
set until scn=1647979;
recover database;
alter database open;
}▶ 20. controlfile, datafile, redologfile 모든 파일들의 위치를 변경 (이관작업)
● 1. controlfile, datafile,redologfile
위치확인
select name from v$datafile;
select name from v$controlfile;
select member from v$logfile;● 2. db 정상종료후 데이터 옮기기
shutdown immediate- mv 로 새로운 위치에 데이터 옮기기
[oracle@oracle ~]$ cd ora_data/
[oracle@oracle ora_data]$ ls
control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
[oracle@oracle ora_data]$ pwd
/home/oracle/ora_data
[oracle@oracle ora_data]$ mv *.* /u01/app/oracle/oradata/ora11g/● 3. 초기파라미터 파일 controlfile 위치 수정후 pfile을 이용하여 mount 단계까지 open
[oracle@oracle dbs]$ vi initora11g.ora
--------------------------------------
*.control_files='/u01/app/oracle/oradata/ora11g/control01.ctl'- pfile이용하여 mount 단계까지
SYS@ora11g> startup pfile=$ORACLE_HOME/dbs/initora11g.ora mountsystem,undo,temp, redolog는
따라서 db내린후 mount단계에서 작업해야한다
● 4. 모든 datafile, redologfile, tempfile RENAME 해준다
alter database rename file '기존위치' to '새로운위치';
● 5. db open 후 pfile이용하여 spfile 만들기
SYS@ora11g> alter database open;- spfile 생성
SYS@ora11g> create spfile from pfile;
DB 공부를 하고 있는 사람입니다. 글을 읽어주셔서 감사하고 더 좋은 글을 쓰기 위해 노력하겠습니다 :)