내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
OS환경 : Oracle Linux 7.4 (64bit)
DB 환경 : Oracle Database 19.3.0.0
방법 : 오라클 19c rman 백업 이후 datafile이 추가된 경우 복구 상황 시나리오
오라클에서 5개의 데이터파일만 가지고 있는 상태에서 rman으로 full 백업을 받고(A시점)
이후 datafile을 추가한 경우
추후 문제가 발생했을때 A시점의 컨트롤파일 백업본과 데이터파일 백업본으로 restore를 한다면
datafile이 정상적으로 보이는지 테스트해봄
* 참고로 이전 테스트에서 사용한 테이블스페이스와 유저를 사용해서 작성함
오라클 19c rman backup set 방식과 image copy 방식 백업 크기 비교 ( https://positivemh.tistory.com/980 )
rman 백업용 파라미터 설정
|
1
2
3
|
RMAN>
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/ORA19/app/oracle/rman/%d_%U_%T_bckset.bk';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/ORA19/app/oracle/rman/db_ctl_%F';
|
기존 백업 삭제
|
1
2
|
RMAN> delete backup;
RMAN> delete copy;
|
현재 상태의 데이터파일 목록 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
set lines 200 pages 1000
col file_name for a50
select file_id, file_name, round(bytes/1024/1024/1024, 2) gb
from dba_data_files order by 1;
FILE_ID FILE_NAME GB
---------- -------------------------------------------------- ----------
1 /ORA19/app/oracle/oradata/ORACLE19/system01.dbf 1.54
2 /ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf .54
3 /ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf .21
4 /ORA19/app/oracle/oradata/ORACLE19/users01.dbf .11
5 /ORA19/app/oracle/oradata/ORACLE19/rmants01.dbf 10
|
데이터파일 5개가 존재함
컨트롤 파일 백업후 내용 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
SQL> alter database backup controlfile to trace as '/home/oracle/con1.sql';
Database altered.
$ cat /home/oracle/con1.sql
..생략..
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORACLE19" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/ORA19/app/oracle/oradata/ORACLE19/redo01.log' SIZE 200M BLOCKSIZE 512,
GROUP 2 '/ORA19/app/oracle/oradata/ORACLE19/redo02.log' SIZE 200M BLOCKSIZE 512,
GROUP 3 '/ORA19/app/oracle/oradata/ORACLE19/redo03.log' SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/ORA19/app/oracle/oradata/ORACLE19/system01.dbf',
'/ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf',
'/ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf',
'/ORA19/app/oracle/oradata/ORACLE19/users01.dbf',
'/ORA19/app/oracle/oradata/ORACLE19/rmants01.dbf'
CHARACTER SET KO16MSWIN949
;
..생략..
|
5개의 데이터파일이 보임
rman 백업 진행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
RMAN> backup database;
Starting backup at 21-DEC-23
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/ORA19/app/oracle/oradata/ORACLE19/rmants01.dbf
input datafile file number=00001 name=/ORA19/app/oracle/oradata/ORACLE19/system01.dbf
input datafile file number=00002 name=/ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf
input datafile file number=00003 name=/ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf
input datafile file number=00004 name=/ORA19/app/oracle/oradata/ORACLE19/users01.dbf
channel ORA_DISK_1: starting piece 1 at 21-DEC-23
channel ORA_DISK_1: finished piece 1 at 21-DEC-23
piece handle=/ORA19/app/oracle/rman/ORACLE19_0e2ejjid_1_1_20231221_bckset.bk tag=TAG20231221T151629 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 21-DEC-23
Starting Control File and SPFILE Autobackup at 21-DEC-23
piece handle=/ORA19/app/oracle/rman/db_ctl_c-3209222764-20231221-03 comment=NONE
Finished Control File and SPFILE Autobackup at 21-DEC-23
|
현재 백업에는 5개의 데이터파일만 존재함
백업 파일 확인
|
1
2
3
4
5
6
|
$ cd /ORA19/app/oracle/rman
$ ls -l
합계 2436196
-rw-r----- 1 oracle oinstall 2473230336 12월 21 15:16 ORACLE19_0e2ejjid_1_1_20231221_bckset.bk
-rw-r--r-- 1 oracle oinstall 172 12월 21 15:24 afiedt.buf
-rw-r----- 1 oracle oinstall 10715136 12월 21 15:16 db_ctl_c-3209222764-20231221-03
|
rmants 테이블스페이스에 데이터파일 추가
|
1
2
3
|
SQL> alter tablespace rmants add datafile '/ORA19/app/oracle/oradata/ORACLE19/rmants02.dbf' size 1024m;
Tablespace altered.
|
현재 상태의 데이터파일 목록 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL>
set lines 200 pages 1000
col file_name for a50
select file_id, file_name, round(bytes/1024/1024/1024, 2) gb
from dba_data_files order by 1;
FILE_ID FILE_NAME GB
---------- -------------------------------------------------- ----------
1 /ORA19/app/oracle/oradata/ORACLE19/system01.dbf 1.54
2 /ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf .54
3 /ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf .21
4 /ORA19/app/oracle/oradata/ORACLE19/users01.dbf .11
5 /ORA19/app/oracle/oradata/ORACLE19/rmants01.dbf 10
6 /ORA19/app/oracle/oradata/ORACLE19/rmants02.dbf 1
|
데이터파일이 6개가 됨
현재 컨트롤 파일 백업후 내용 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
SQL> alter database backup controlfile to trace as '/home/oracle/con2.sql';
Database altered.
$ cat /home/oracle/con2.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORACLE19" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/ORA19/app/oracle/oradata/ORACLE19/redo01.log' SIZE 200M BLOCKSIZE 512,
GROUP 2 '/ORA19/app/oracle/oradata/ORACLE19/redo02.log' SIZE 200M BLOCKSIZE 512,
GROUP 3 '/ORA19/app/oracle/oradata/ORACLE19/redo03.log' SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/ORA19/app/oracle/oradata/ORACLE19/system01.dbf',
'/ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf',
'/ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf',
'/ORA19/app/oracle/oradata/ORACLE19/users01.dbf',
'/ORA19/app/oracle/oradata/ORACLE19/rmants01.dbf',
'/ORA19/app/oracle/oradata/ORACLE19/rmants02.dbf'
CHARACTER SET KO16MSWIN949
|
데이터파일 6개가 보임
샘플 유저 생성 및 권한 부여
|
1
2
3
4
5
6
7
|
SQL> create user rmanuser identified by rmanuser account unlock default tablespace rmants quota unlimited on rmants;
User created.
SQL> grant resource, connect to rmanuser;
Grant succeeded.
|
샘플 테이블 생성
|
1
2
3
4
5
6
7
8
9
|
SQL>
conn rmanuser/rmanuser
create table sample_t
(id1 number, id2 number, name varchar2(10),
date1 varchar2(8), date2 varchar2(8), date3 date,
phone varchar2(13), price number, qty number,
test1 number, test2 varchar2(5), test3 varchar2(4)
)
nologging;
|
샘플 데이터 삽입
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
SQL>
DECLARE
TYPE tbl_ins IS TABLE OF sample_t%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR d IN 1..10 LOOP
FOR i IN 1..100000 LOOP
w_ins(i).id1 := i;
w_ins(i).id2 := i||ceil(dbms_random.value(1, 10000000));
w_ins(i).name := dbms_random.string('x',5);
w_ins(i).date1 := round(dbms_random.value(2010,2021))||to_char(round(dbms_random.value(1,12)), 'FM09')||to_char( round(dbms_random.value(1,28)), 'FM09');
w_ins(i).date2 := '2021'||to_char(round(dbms_random.value(1,12)) , 'FM09')||to_char(round(dbms_random.value(1,28)), 'FM09');
w_ins(i).date3 := to_date(round(dbms_random.value(2019,2021))||'-'||round(dbms_random.value(1,12))||'-'||round(dbms_random.value(1,28))||' '||round(dbms_random.value(1, 23))||':'||round(dbms_random.value(0, 59))||':'||round(dbms_random.value(0, 59)), 'YYYY-MM-DD HH24:MI:SS');
w_ins(i).phone := '010-'||ceil(dbms_random.value(1000, 9999))||'-'||ceil(dbms_random.value(1000, 9999));
w_ins(i).price := ceil(dbms_random.value(1, 10))*1000;
w_ins(i).qty := ceil(dbms_random.value(1, 10));
w_ins(i).test1 := 1234;
w_ins(i).test2 := 'SQLP';
w_ins(i).test3 := 'A'||ceil(dbms_random.value(100, 999));
END LOOP;
FORALL i in 1..100000 INSERT INTO sample_t VALUES w_ins(i);
COMMIT;
END LOOP;
END;
/
PL/SQL procedure successfully completed.
|
테이블 용량 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
set lines 200 pages 1000
col segment_name for a10
select segment_name, bytes/1024/1204 mb from dba_segments
where segment_name = 'SAMPLE_T'
and tablespace_name = 'RMANTS';
SEGMENT_NA MB
---------- ----------
SAMPLE_T 1029.684652
|
RMANTS에 유일하게 존재하는 SAMPLE_T 테이블에 대략 1gb 정도가 쌓임
새로운 데이터파일에 데이터가 쌓였는지 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
set lines 200 pages 1000
col file_name for a50
select a.file_id, a.file_name, a.tablespace_name, sum(e.bytes) as used_bytes
from dba_extents e, dba_data_files a
where e.file_id = a.file_id
and a.tablespace_name = 'RMANTS'
group by a.file_id, a.file_name, a.tablespace_name;
FILE_ID FILE_NAME TABLESPACE_NAME USED_BYTES
---------- -------------------------------------------------- ------------------------------ ----------
5 /ORA19/app/oracle/oradata/ORACLE19/rmants01.dbf RMANTS 1207959552
6 /ORA19/app/oracle/oradata/ORACLE19/rmants02.dbf RMANTS 67108864
|
신규 데이터파일(rmants02.dbf)에도 데이터가 쌓임
현재 redo 아카이브로 내려쓰기(2번 진행함)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL> alter system archive log current;
System altered.
SQL>
select group#, thread#, sequence#, archived, status
from v$log;
GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------------
1 1 37 YES ACTIVE
2 1 38 YES ACTIVE
3 1 39 NO CURRENT
|
장애 상황 가정을 위해 비정상 종료
|
1
2
|
SQL> shutdown abort
ORACLE instance shut down.
|
데이터파일, 컨트롤파일, 리두로그 모두 삭제
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
$ cd /ORA19/app/oracle/oradata/ORACLE19
$ ls -al
합계 14710952
drwxr-x--- 2 oracle oinstall 240 12월 21 15:18 .
drwxr-x--- 3 oracle oinstall 22 1월 7 2020 ..
-rw-r----- 1 oracle oinstall 10600448 12월 21 15:35 control01.ctl
-rw-r----- 1 oracle oinstall 10600448 12월 21 15:35 control02.ctl
-rw-r----- 1 oracle oinstall 209715712 12월 21 15:29 redo01.log
-rw-r----- 1 oracle oinstall 209715712 12월 21 15:31 redo02.log
-rw-r----- 1 oracle oinstall 209715712 12월 21 15:35 redo03.log
-rw-r----- 1 oracle oinstall 10737426432 12월 21 15:34 rmants01.dbf
-rw-r----- 1 oracle oinstall 1073750016 12월 21 15:34 rmants02.dbf
-rw-r----- 1 oracle oinstall 576724992 12월 21 15:34 sysaux01.dbf
-rw-r----- 1 oracle oinstall 1656758272 12월 21 15:35 system01.dbf
-rw-r----- 1 oracle oinstall 25174016 12월 19 15:27 temp01.dbf
-rw-r----- 1 oracle oinstall 225452032 12월 21 15:34 undotbs01.dbf
-rw-r----- 1 oracle oinstall 119283712 12월 21 15:34 users01.dbf
$ rm -rf ./*
$ ls -al
합계 0
drwxr-x--- 2 oracle oinstall 6 12월 21 15:42 .
drwxr-x--- 3 oracle oinstall 22 1월 7 2020 ..
|
모두 삭제됨
db 기동 시도
|
1
2
3
4
5
6
7
8
|
SQL> startup nomount
ORACLE instance started.
Total System Global Area 2214590664 bytes
Fixed Size 8898760 bytes
Variable Size 2147483648 bytes
Database Buffers 50331648 bytes
Redo Buffers 7876608 bytes
|
nomount 모드로는 기동됨(파라미터 파일 사용)
mount 모드로 변경
|
1
2
3
4
5
|
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info
|
컨트롤파일이 없어서 에러가 발생함
rman 에서 controlfile부터 restore 진행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Dec 21 16:30:25 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORACLE19 (not mounted)
RMAN> restore controlfile from '/ORA19/app/oracle/rman/db_ctl_c-3209222764-20231221-03';
Starting restore at 21-DEC-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=256 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/ORA19/app/oracle/oradata/ORACLE19/control01.ctl
output file name=/ORA19/app/oracle/oradata/ORACLE19/control02.ctl
Finished restore at 21-DEC-23
|
컨트롤파일이 복구됨
컨트롤파일 디렉토리 확인
|
1
2
3
4
5
6
7
8
|
$ pwd
/ORA19/app/oracle/oradata/ORACLE19
$ ls -al
합계 20704
drwxr-x--- 2 oracle oinstall 48 12월 21 16:30 .
drwxr-x--- 3 oracle oinstall 22 1월 7 2020 ..
-rw-r----- 1 oracle oinstall 10600448 12월 21 16:30 control01.ctl
-rw-r----- 1 oracle oinstall 10600448 12월 21 16:30 control02.ctl
|
정상적으로 컨트롤파일이 복구됨
db 마운트모드로 전환
|
1
2
3
4
|
RMAN> sql 'alter database mount';
sql statement: alter database mount
released channel: ORA_DISK_1
|
db 상태 확인
|
1
2
3
4
5
|
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
oracle19 MOUNTED
|
정상적으로 mount mode가 됨
rman 설정 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
RMAN> show all;
RMAN configuration parameters for database with db_unique_name ORACLE19 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/ORA19/app/oracle/rman/db_ctl_%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 CHANNEL DEVICE TYPE DISK FORMAT '/ORA19/app/oracle/rman/%d_%U_%T_bckset.bk';
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 RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/ORA19/app/oracle/product/19.0.0/db_1/dbs/snapcf_oracle19.f'; # default
|
등록된 백업 파일 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7 Full 2.30G DISK 00:00:11 21-DEC-23
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20231221T151629
Piece Name: /ORA19/app/oracle/rman/ORACLE19_0e2ejjid_1_1_20231221_bckset.bk
List of Datafiles in backup set 7
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 2925262 21-DEC-23 NO /ORA19/app/oracle/oradata/ORACLE19/system01.dbf
2 Full 2925262 21-DEC-23 NO /ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf
3 Full 2925262 21-DEC-23 NO /ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf
4 Full 2925262 21-DEC-23 NO /ORA19/app/oracle/oradata/ORACLE19/users01.dbf
5 Full 2925262 21-DEC-23 NO /ORA19/app/oracle/oradata/ORACLE19/rmants01.dbf
|
데이터베이스 restore 진행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
RMAN> restore database;
Starting restore at 21-DEC-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 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 00001 to /ORA19/app/oracle/oradata/ORACLE19/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /ORA19/app/oracle/oradata/ORACLE19/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /ORA19/app/oracle/oradata/ORACLE19/rmants01.dbf
channel ORA_DISK_1: reading from backup piece /ORA19/app/oracle/rman/ORACLE19_0e2ejjid_1_1_20231221_bckset.bk
channel ORA_DISK_1: piece handle=/ORA19/app/oracle/rman/ORACLE19_0e2ejjid_1_1_20231221_bckset.bk tag=TAG20231221T151629
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 21-DEC-23
|
현재 5개의 datafile만 restore가 됨
파일들 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
$ cd /ORA19/app/oracle/oradata/ORACLE19
$ ls -al
합계 13024264
drwxr-x--- 2 oracle oinstall 148 12월 21 16:32 .
drwxr-x--- 3 oracle oinstall 22 1월 7 2020 ..
-rw-r----- 1 oracle oinstall 10600448 12월 21 16:33 control01.ctl
-rw-r----- 1 oracle oinstall 10600448 12월 21 16:33 control02.ctl
-rw-r----- 1 oracle oinstall 10737426432 12월 21 16:33 rmants01.dbf
-rw-r----- 1 oracle oinstall 576724992 12월 21 16:32 sysaux01.dbf
-rw-r----- 1 oracle oinstall 1656758272 12월 21 16:32 system01.dbf
-rw-r----- 1 oracle oinstall 225452032 12월 21 16:32 undotbs01.dbf
-rw-r----- 1 oracle oinstall 119283712 12월 21 16:32 users01.dbf
|
컨트롤파일 2개, 데이터파일 5개가 restore 됨
v$datafile 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
set lines 200 pages 1000
col name for a70
select file#, name, status, block1_offset from v$datafile;
FILE# NAME STATUS BLOCK1_OFFSET
---------- ---------------------------------------------------------------------- ------- -------------
1 /ORA19/app/oracle/oradata/ORACLE19/system01.dbf SYSTEM 8192
2 /ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf ONLINE 8192
3 /ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf ONLINE 8192
4 /ORA19/app/oracle/oradata/ORACLE19/users01.dbf ONLINE 8192
5 /ORA19/app/oracle/oradata/ORACLE19/rmants01.dbf ONLINE 8192
5 rows selected.
|
5개 데이터파일만 존재함
recover database 시도
|
1
2
3
4
|
$ sqlplus / as sysdba
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
|
백업된 컨트롤 파일을 이용해 복구했기 때문에 일반 recover database 명령으로는 복구되지 않음
recover database using backup controlfile 실행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL> recover database using backup controlfile;
ORA-00279: change 2925262 generated at 12/21/2023 15:16:29 needed for thread 1
ORA-00289: suggestion : /ORA19/app/oracle/arch/oracle19_1_37_1029026604.arc
ORA-00280: change 2925262 for thread 1 is in sequence #37
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 6: '/ORA19/app/oracle/oradata/ORACLE19/rmants02.dbf'
ORA-01112: media recovery not started
|
rman 백업 당시에는 rmants02.dbf가 없기 때문에 복구되지 않음
v$datafile 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL>
set lines 200 pages 1000
col name for a70
select file#, name, status, block1_offset from v$datafile;
FILE# NAME STATUS BLOCK1_OFFSET
---------- ---------------------------------------------------------------------- ------- -------------
1 /ORA19/app/oracle/oradata/ORACLE19/system01.dbf SYSTEM 8192
2 /ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf ONLINE 8192
3 /ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf ONLINE 8192
4 /ORA19/app/oracle/oradata/ORACLE19/users01.dbf ONLINE 8192
5 /ORA19/app/oracle/oradata/ORACLE19/rmants01.dbf ONLINE 8192
6 /ORA19/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00006 RECOVER 4294967295
6 rows selected.
|
recover batabase using backup controlfile 명령 이후 6번 데이터파일이 UNNAMED00006으로 임시로 작성되어 있음
데이터베이스 recover 전 기존에 추가한 데이터파일을 복원시키기 위한 빈 데이터파일 수동 추가
|
1
2
3
|
SQL> alter database create datafile '/ORA19/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00006' as '/ORA19/app/oracle/oradata/ORACLE19/rmants02.dbf';
Database altered.
|
v$datafile 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL>
set lines 200 pages 1000
col name for a70
select file#, name, status, block1_offset from v$datafile;
FILE# NAME STATUS BLOCK1_OFFSET
---------- ---------------------------------------------------------------------- ------- -------------
1 /ORA19/app/oracle/oradata/ORACLE19/system01.dbf SYSTEM 8192
2 /ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf ONLINE 8192
3 /ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf ONLINE 8192
4 /ORA19/app/oracle/oradata/ORACLE19/users01.dbf ONLINE 8192
5 /ORA19/app/oracle/oradata/ORACLE19/rmants01.dbf ONLINE 8192
6 /ORA19/app/oracle/oradata/ORACLE19/rmants02.dbf RECOVER 8192
6 rows selected.
|
rmants02.dbf로 변경됨
다시 recover 시도
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
SQL> recover database using backup controlfile;
ORA-00279: change 2925326 generated at 12/21/2023 15:18:21 needed for thread 1
ORA-00289: suggestion : /ORA19/app/oracle/arch/oracle19_1_37_1029026604.arc
ORA-00280: change 2925326 for thread 1 is in sequence #37
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
엔터 입력
ORA-00279: change 2936135 generated at 12/21/2023 15:29:19 needed for thread 1
ORA-00289: suggestion : /ORA19/app/oracle/arch/oracle19_1_38_1029026604.arc
ORA-00280: change 2936135 for thread 1 is in sequence #38
ORA-00278: log file '/ORA19/app/oracle/arch/oracle19_1_37_1029026604.arc' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
엔터 입력
ORA-00279: change 2936184 generated at 12/21/2023 15:31:10 needed for thread 1
ORA-00289: suggestion : /ORA19/app/oracle/arch/oracle19_1_39_1029026604.arc
ORA-00280: change 2936184 for thread 1 is in sequence #39
ORA-00278: log file '/ORA19/app/oracle/arch/oracle19_1_38_1029026604.arc' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log '/ORA19/app/oracle/arch/oracle19_1_39_1029026604.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
|
마지막에 에러가 발생함, 아카이브로 내려써지지 못한 redo가 있었기 때문에(39번 아카이브로그) 발생하는 에러
해당 아카이브에 대해 적용안하겠다고 선언하는 명령 수행
|
1
2
3
4
5
6
7
8
9
|
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 2936184 generated at 12/21/2023 15:31:10 needed for thread 1
ORA-00289: suggestion : /ORA19/app/oracle/arch/oracle19_1_39_1029026604.arc
ORA-00280: change 2936184 for thread 1 is in sequence #39
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel <<< cancel 입력
Media recovery cancelled.
|
(처음부터 recover database until cancel using backup controlfile; 를 사용하고
마지막 아카이브 파일 전까지 엔터를 눌려주다가(recover수행) 마지막 아카이브에 대해서만 cancel을 입력해도됨)
db open 시도
|
1
2
3
4
5
|
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
|
컨트롤 파일 복구본을 사용하였고, 불완전 복구이기때문에 resetlogs 옵션을 사용해줘야함
db open 시도(resetlogs)
|
1
2
3
|
SQL> alter database open resetlogs;
Database altered.
|
db 상태 확인
|
1
2
3
4
5
|
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
oracle19 OPEN
|
정상적으로 오픈됨
v$datafile 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL>
set lines 200 pages 1000
col name for a70
select file#, name, status, block1_offset from v$datafile;
FILE# NAME STATUS BLOCK1_OFFSET
---------- ---------------------------------------------------------------------- ------- -------------
1 /ORA19/app/oracle/oradata/ORACLE19/system01.dbf SYSTEM 8192
2 /ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf ONLINE 8192
3 /ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf ONLINE 8192
4 /ORA19/app/oracle/oradata/ORACLE19/users01.dbf ONLINE 8192
5 /ORA19/app/oracle/oradata/ORACLE19/rmants01.dbf ONLINE 8192
6 /ORA19/app/oracle/oradata/ORACLE19/rmants02.dbf ONLINE 8192
6 rows selected.
|
모두 정상임
참고1. alert log 확인시 temp ts도 자동으로 recreate 됨
open 시 alert log
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
$ vi alert.log
2023-12-21T17:16:17.285645+09:00
Errors in file /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_dbw0_22821.trc:
ORA-01186: file 201 failed verification tests
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/ORA19/app/oracle/oradata/ORACLE19/temp01.dbf'
2023-12-21T17:16:17.285747+09:00
File 201 not verified due to error ORA-01157
2023-12-21T17:16:17.287934+09:00
Dictionary check complete
Verifying minimum file header compatibility for tablespace encryption..
Verifying file header compatibility for tablespace encryption completed for pdb 0
2023-12-21T17:16:17.289502+09:00
Errors in file /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_dbw0_22821.trc:
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/ORA19/app/oracle/oradata/ORACLE19/temp01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
2023-12-21T17:16:17.290681+09:00
Errors in file /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_dbw0_22821.trc:
ORA-01186: file 201 failed verification tests
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/ORA19/app/oracle/oradata/ORACLE19/temp01.dbf'
2023-12-21T17:16:17.291484+09:00
File 201 not verified due to error ORA-01157
2023-12-21T17:16:17.298824+09:00
Re-creating tempfile /ORA19/app/oracle/oradata/ORACLE19/temp01.dbf
Database Characterset is KO16MSWIN949
Opening with Resource Manager plan: default_plan
joxcsys_required_dirobj_exists: directory object exists with required path /ORA19/app/oracle/product/19.0.0/db_1/javavm/admin/, pid 23075 cid 0
replication_dependency_tracking turned off (no async multimaster replication found)
|
에러 발생하다가 Re-creating tempfile /ORA19/app/oracle/oradata/ORACLE19/temp01.dbf 메세지 나오면서 만들어짐
조금 찾아본 결과 11g 버전부터 db기동시 temp 가 없는경우 자동으로 생성해주는듯함
temp file 확인
|
1
2
3
4
5
6
7
8
|
SQL>
set lines 200 pages 1000
col name for a50
SQL> select file#, status, name, bytes/1024/1024 mb from v$tempfile;
FILE# STATUS NAME MB
---------- ------- -------------------------------------------------- ----------
1 ONLINE /ORA19/app/oracle/oradata/ORACLE19/temp01.dbf 20
|
참고2. add datafile 이후 백업된 컨트롤파일로 복구
add datafile 한 뒤 잠시뒤 컨트롤파일이 자동으로 rman 백업경로에 백업됨
|
1
2
3
4
5
6
7
8
9
|
$ vi alert.log
2023-12-21T15:18:21.130900+09:00
alter tablespace rmants add datafile '/ORA19/app/oracle/oradata/ORACLE19/rmants02.dbf' size 1024m
2023-12-21T15:18:27.083271+09:00
Completed: alter tablespace rmants add datafile '/ORA19/app/oracle/oradata/ORACLE19/rmants02.dbf' size 1024m
2023-12-21T15:24:07.733383+09:00
Control autobackup written to DISK device
handle '/ORA19/app/oracle/rman/db_ctl_c-3209222764-20231221-04'
|
controlfile restore 시 이 파일을 사용하는 경우 새로 추가한 datafile까지 같이 restore됨
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
RMAN> restore controlfile from '/ORA19/app/oracle/rman/db_ctl_c-3209222764-20231221-04';
Starting restore at 22-DEC-23
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=/ORA19/app/oracle/oradata/ORACLE19/control01.ctl
output file name=/ORA19/app/oracle/oradata/ORACLE19/control02.ctl
Finished restore at 22-DEC-23
RMAN> sql 'alter database mount';
sql statement: alter database mount
released channel: ORA_DISK_1
RMAN> restore database;
Starting restore at 22-DEC-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=4 device type=DISK
creating datafile file number=6 name=/ORA19/app/oracle/oradata/ORACLE19/rmants02.dbf
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 00001 to /ORA19/app/oracle/oradata/ORACLE19/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /ORA19/app/oracle/oradata/ORACLE19/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /ORA19/app/oracle/oradata/ORACLE19/rmants01.dbf
channel ORA_DISK_1: reading from backup piece /ORA19/app/oracle/rman/ORACLE19_0e2ejjid_1_1_20231221_bckset.bk
channel ORA_DISK_1: piece handle=/ORA19/app/oracle/rman/ORACLE19_0e2ejjid_1_1_20231221_bckset.bk tag=TAG20231221T151629
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:56
Finished restore at 22-DEC-23
|
그리고 recover database using backup controlfile 명령 수행시 39번 아카이브로그 전까지 복구가 됨
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
SQL> recover database using backup controlfile;
ORA-00279: change 2925262 generated at 12/21/2023 15:16:29 needed for thread 1
ORA-00289: suggestion : /ORA19/app/oracle/arch/oracle19_1_37_1029026604.arc
ORA-00280: change 2925262 for thread 1 is in sequence #37
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto <<< auto 입력
ORA-00279: change 2936135 generated at 12/21/2023 15:29:19 needed for thread 1
ORA-00289: suggestion : /ORA19/app/oracle/arch/oracle19_1_38_1029026604.arc
ORA-00280: change 2936135 for thread 1 is in sequence #38
ORA-00278: log file '/ORA19/app/oracle/arch/oracle19_1_37_1029026604.arc' no
longer needed for this recovery
ORA-00279: change 2936184 generated at 12/21/2023 15:31:10 needed for thread 1
ORA-00289: suggestion : /ORA19/app/oracle/arch/oracle19_1_39_1029026604.arc
ORA-00280: change 2936184 for thread 1 is in sequence #39
ORA-00278: log file '/ORA19/app/oracle/arch/oracle19_1_38_1029026604.arc' no
longer needed for this recovery
ORA-00308: cannot open archived log
'/ORA19/app/oracle/arch/oracle19_1_39_1029026604.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
|
이후 recover database until cancel using backup controlfile 명령 수행
|
1
2
3
4
5
6
7
8
9
|
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 2936184 generated at 12/21/2023 15:31:10 needed for thread 1
ORA-00289: suggestion : /ORA19/app/oracle/arch/oracle19_1_39_1029026604.arc
ORA-00280: change 2936184 for thread 1 is in sequence #39
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel <<< cancel 입력
Media recovery cancelled.
|
(처음부터 recover database until cancel using backup controlfile; 를 사용하고
마지막 아카이브 파일 전까지 엔터를 눌려주다가(recover수행) 마지막 아카이브에 대해서만 cancel을 입력해도됨)
db 기동
|
1
2
3
4
5
|
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
|
백업된 컨트톨파일을 사용하고 until cancel로 불완전 복구를 했기 때문에 당연히 resetlogs 를 사용해야함
db 기동 resetlogs
|
1
2
3
4
5
6
7
8
9
|
SQL> alter database open resetlogs;
Database altered.
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
oracle19 OPEN
|
정상적으로 open 됨
v$datafile 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL>
set lines 200 pages 1000
col name for a70
select file#, name, status, block1_offset from v$datafile;
FILE# NAME STATUS BLOCK1_OFFSET
---------- ---------------------------------------------------------------------- ------- -------------
1 /ORA19/app/oracle/oradata/ORACLE19/system01.dbf SYSTEM 8192
2 /ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf ONLINE 8192
3 /ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf ONLINE 8192
4 /ORA19/app/oracle/oradata/ORACLE19/users01.dbf ONLINE 8192
5 /ORA19/app/oracle/oradata/ORACLE19/rmants01.dbf ONLINE 8192
6 /ORA19/app/oracle/oradata/ORACLE19/rmants02.dbf ONLINE 8192
6 rows selected.
|
모두 정상임
결론 : rman 백업이후 datafile을 추가한 경우 rman 백업만 남아있고 datafile, controlfile, redo log 까지 삭제되었을때
백업된 컨트롤 파일에 추가된 datafile 정보가 없는 경우
복구할 데이터파일을 만들어주는 작업인 alter database create datafile 명령 수행이 필요함
이후 recover batabase using backup controlfile 명령을 이용해 복구가 가능함(마지막 until cancel 포함)
하지만 백업된 컨트롤 파일에 추가된 datafile 정보가 있는 경우
복구할 데이터파일을 만들어주는 작업인 alter database create datafile 명령 수행이 필요없고
바로 recover batabase using backup controlfile 명령을 이용해 복구가 가능함(마지막 until cancel 포함)
참조 :
https://docs.oracle.com/en/database/oracle/oracle-database/19/bradv/rman-backup-concepts.html#GUID-74FF41AF-D1EE-4DB1-AC0D-56FC83CAA91D
https://docs.oracle.com/en/database/oracle/oracle-database/19/bradv/rman-backup-concepts.html#GUID-D73BDDC7-A4AC-40A2-B279-1A764A51EBC4
https://docs.oracle.com/cd/A58617_01/server.804/a58396/ch12.htm
https://positivemh.tistory.com/147
오라클 아카이브 모드 설정 및 경로 설정
OS환경 : Oracle Linux 6.8 (64bit) DB 환경 : Oracle Database 10.2.0.4 방법 : 오라클 아카이브 모드 설정 및 경로 설정오라클 아카이브로그 경로 변경, 아카이브 변경LOG_ARCHIVE_DEST 을 LOG_ARCHIVE_DEST_1 로 변경하는
positivemh.tistory.com
https://positivemh.tistory.com/510
rac db(asm)에서 특정시점의 데이터를 single db(filesystem) 복구 테스트
OS환경 : Oracle Linux 6.8 (64bit) DB 환경 : Oracle Database 11.2.0.4 ASM RAC to FS SINGLE 방법 : RMAN을 이용한 RAC db(asm) to SINGLE db(filesystem) 복구 테스트개요 rac db(asm)에서 특정시점의 데이터를 single db(filesystem)로 복
positivemh.tistory.com
https://positivemh.tistory.com/808
대량 샘플데이터 생성용 쿼리 벌크 인서트
OS환경 : Oracle Linux 7.6 (64bit) DB 환경 : Oracle Database 19.10.0.0 방법 : 대량 샘플데이터 생성용 쿼리 벌크인서트 벌크인서트를 이용해 대량 데이터를 만들 때 회원번호, 전화번호, 주소, 금액, 일자, 일
positivemh.tistory.com
'ORACLE > Backup&Recover' 카테고리의 다른 글
| 오라클 19c DUL 이용 Truncate 된 데이터 복구(비공식 툴) (0) | 2024.03.28 |
|---|---|
| 오라클 19c rman 백업 압축 알고리즘별 압축률 비교 (0) | 2024.01.18 |
| 오라클 19c rman backup set 방식과 image copy 방식 백업 크기 비교 (0) | 2023.12.21 |
| 오라클 19c rman 접속하는 몇가지 방법 (0) | 2023.01.25 |
| Oracle 19c rac to single clone db 생성 가이드 (0) | 2022.02.07 |