오라클 19c rman 백업 이후 datafile이 추가된 경우 복구 상황 시나리오

2023. 12. 21. 16:00 내맘대로긍정

프린트 하기

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/user-managed-recovery-advanced.html#GUID-88CAB555-C409-4805-B6A4-C92401422EF7

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

https://positivemh.tistory.com/980

저작자표시

'ORACLE > Backup&Recover' 카테고리의 다른 글

오라클 19c 스탠다드 에디션에서 엔터프라이즈 에디션으로 백업복구(cold backup) 테스트 (0)	2024.04.01
오라클 19c DUL 이용 Truncate 된 데이터 복구(비공식 툴) (0)	2024.03.28
오라클 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

내맘대로긍정

OracleDBA@kakao.com

Category

Menu

Comment

Link