반응형
OS환경 : Oracle Linux 8.1 (64bit)
DB 환경 : Oracle Database 19.9.0.0
방법 : 오라클 19c rman catalog db 이용 백업 복구
원본 db = instance_name : oracle19, IP : 192.168.137.50, hostname : oel8
카탈로그 db = instance_name : catdb, IP : 192.168.137.51, hostname : oel8catalog
아카이브 모드 확인(원본 db, 카탈로그 db)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
원본 db
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /ORA19/app/oracle/arch
Oldest online log sequence 7
Next log sequence to archive 9
Current log sequence 9
카탈로그 db
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /ORA19/app/oracle/arch
Oldest online log sequence 9
Next log sequence to archive 11
Current log sequence 11
|
아카이브모드가 아니라면 아카이브모드 설정 게시글 참조해서 설정 후 진행
오라클 아카이브 모드 설정 및 경로 설정 https://positivemh.tistory.com/147
체크포인트 및 로그스위치, 시간확인(추후 시나리오용 작업)
더보기
샘플 데이터 생성(원본 db)
|
1
2
3
4
5
6
7
8
9
|
SQL> create table newimsi as select * from dba_objects;
Table created.
SQL> select count(*) from newimsi;
COUNT(*)
----------
22960
|
체크포인트 및 로그스위치, 시간확인(추후 시나리오용 작업)
더보기
rman으로 카탈로그 db로 접속 후 파라미터 수정(백업 경로)(원본 db)
|
$ rman target / catalog catuser/catuser@catdb
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/ORA19/app/oracle/rman/%d_%U_%T.bk';
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/ORA19/app/oracle/rman/%d_%U_%T.bk';
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/ORA19/app/oracle/rman/db_ctl_%F';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/ORA19/app/oracle/rman/db_ctl_%F';
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
|
rman으로 카탈로그 db로 접속 후 전체 백업 실행(원본 db)
|
$ rman target / catalog catuser/catuser@catdb
RMAN> backup database;
Starting backup at 29-JAN-21
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=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 29-JAN-21
channel ORA_DISK_1: finished piece 1 at 29-JAN-21
piece handle=/ORA19/app/oracle/rman/ORACLE19_06vlrm0e_1_1_20210129.bk tag=TAG20210129T133926 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 29-JAN-21
Starting Control File and SPFILE Autobackup at 29-JAN-21
piece handle=/ORA19/app/oracle/rman/db_ctl_c-3209222764-20210129-03 comment=NONE
Finished Control File and SPFILE Autobackup at 29-JAN-21
|
백업 정보 확인(원본 db)
|
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
|
$ rman target / catalog catuser/catuser@catdb
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
162 Full 542.66M DISK 00:00:01 29-JAN-21
BP Key: 163 Status: AVAILABLE Compressed: NO Tag: TAG20210129T133926
Piece Name: /ORA19/app/oracle/rman/ORACLE19_06vlrm0e_1_1_20210129.bk
List of Datafiles in backup set 162
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 1038182 29-JAN-21 NO /ORA19/app/oracle/oradata/ORACLE19/system01.dbf
2 Full 1038182 29-JAN-21 NO /ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf
3 Full 1038182 29-JAN-21 NO /ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf
4 Full 1038182 29-JAN-21 NO /ORA19/app/oracle/oradata/ORACLE19/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
183 Full 10.52M DISK 00:00:00 29-JAN-21
BP Key: 188 Status: AVAILABLE Compressed: NO Tag: TAG20210129T133930
Piece Name: /ORA19/app/oracle/rman/db_ctl_c-3209222764-20210129-03
SPFILE Included: Modification time: 29-JAN-21
SPFILE db_unique_name: ORACLE19
Control File Included: Ckp SCN: 1038197 Ckp time: 29-JAN-21
|
정상적으로 백업됨
백업 파일 확인(원본 db, 카탈로그 db)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
원본 db
$ cd /ORA19/app/oracle/rman/
$ ls -al
total 566472
drwxr-xr-x 2 oracle oinstall 86 Jan 29 13:39 .
drwxrwxr-x. 11 oracle oinstall 154 Jan 29 13:32 ..
-rw-r----- 1 oracle oinstall 11042816 Jan 29 13:39 db_ctl_c-3209222764-20210129-03
-rw-r----- 1 oracle oinstall 569024512 Jan 29 13:39 ORACLE19_06vlrm0e_1_1_20210129.bk
카탈로그 db
$ cd /ORA19/app/oracle/rman/
$ ls -al
total 0
drwxr-xr-x 2 oracle oinstall 6 Jan 21 15:38 .
drwxrwxr-x. 11 oracle oinstall 136 Jan 21 15:38 ..
|
rman 백업 파일은 원본 db 서버에 저장됨
장애 발생
원본 db 컨트롤 파일 확인(원본 db)
|
1
2
3
4
5
6
|
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /ORA19/app/oracle/oradata/ORACLE19/control01.ctl,
/ORA19/app/oracle/oradata/ORACLE19/control02.ctl
|
컨트롤파일 삭제 후 db 강제종료(원본 db)
|
1
2
3
4
5
6
7
|
SQL> !rm -rf /ORA19/app/oracle/oradata/ORACLE19/control0*.ctl
SQL> !ls /ORA19/app/oracle/oradata/ORACLE19/con*
ls: cannot access '/ORA19/app/oracle/oradata/ORACLE19/con*': No such file or directory
SQL> shutdown abort
ORACLE instance shut down.
|
db 기동 시도(원본 db)
|
1
2
3
4
5
6
7
8
9
|
SQL> startup
ORACLE instance started.
Total System Global Area 1073737800 bytes
Fixed Size 8904776 bytes
Variable Size 654311424 bytes
Database Buffers 406847488 bytes
Redo Buffers 3674112 bytes
ORA-00205: error in identifying control file, check alert log for more info
|
컨트롤파일이 없어서 nomount 까지만 올라가고 mount 되지 않음
복구
카탈로그 db rman 접속 후 컨트롤파일 복구
|
$ rman target / catalog catuser/catuser@catdb
RMAN> restore controlfile;
Starting restore at 29-JAN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=422 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece /ORA19/app/oracle/rman/db_ctl_c-3209222764-20210129-03
channel ORA_DISK_1: piece handle=/ORA19/app/oracle/rman/db_ctl_c-3209222764-20210129-03 tag=TAG20210129T133930
channel ORA_DISK_1: restored backup piece 1
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 29-JAN-21
|
정상적으로 컨트롤파일이 복구됨
db mount 상태로 변경
|
SQL> alter database mount;
Database altered.
|
db open 시도
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/ORA19/app/oracle/oradata/ORACLE19/system01.dbf'
|
alter database open 명령은 컨트롤파일을 백업본을 사용했기 때문에 불가하고
alter database open resetlogs 명령은 컨트롤파일 백업본 사용후 복원(recover)을 하지 않았기 때문에 에러가 발생함
db recover 시도
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL> recover database using backup controlfile;
ORA-00279: change 1038182 generated at 01/29/2021 13:39:26 needed for thread 1
ORA-00289: suggestion : /ORA19/app/oracle/arch/1_15_1062864404.arc
ORA-00280: change 1038182 for thread 1 is in sequence #15
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto <-- [auto 입력]
ORA-00308: cannot open archived log '/ORA19/app/oracle/arch/1_15_1062864404.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
ORA-00308: cannot open archived log '/ORA19/app/oracle/arch/1_15_1062864404.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
|
1_15_1062864404.arc(sequence #15) 아카이브로그 파일을 찾지못해 복구에 실패함
리두로그 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
set lines 200 pages 1000
col member for a70
select l.group#, member, archived, sequence#
from v$log l, v$logfile f
where f.group# = l.group#
order by 1;
GROUP# MEMBER ARC SEQUENCE#
---------- ---------------------------------------------------------------------- --- ----------
1 /ORA19/app/oracle/oradata/ORACLE19/redo01.log YES 13
2 /ORA19/app/oracle/oradata/ORACLE19/redo02.log YES 14
3 /ORA19/app/oracle/oradata/ORACLE19/redo03.log NO 15
|
sequence #15에 해당하는 로그파일은 현재 redo03 임을 확인
다시 db recover 시도
|
1
2
3
4
5
6
7
8
9
10
|
SQL> recover database using backup controlfile;
ORA-00279: change 1038182 generated at 01/29/2021 13:39:26 needed for thread 1
ORA-00289: suggestion : /ORA19/app/oracle/arch/1_15_1062864404.arc
ORA-00280: change 1038182 for thread 1 is in sequence #15
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/ORA19/app/oracle/oradata/ORACLE19/redo03.log <-- [redo03번 위치 입력]
Log applied.
Media recovery complete.
|
정상적으로 복구됨
db resetlogs로 open 후 샘플 데이터 확인
|
1
2
3
4
5
6
7
8
9
|
SQL> alter database open resetlogs;
Database altered.
SQL> select count(*) from newimsi;
COUNT(*)
----------
22960
|
정상적으로 확인됨
incarnation 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
$ export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"
$ rman target / catalog catuser/catuser@catdb
RMAN> list incarnation;
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 2 ORACLE19 3209222764 PARENT 988348 2021-01-26 16:06:44
1 207 ORACLE19 3209222764 CURRENT 1038289 2021-01-29 13:45:25
|
출처 : 오라클 19c rman catalog db 이용 백업 복구 :: 내맘대로긍정 (tistory.com)
반응형
'오라클 > 참고' 카테고리의 다른 글
| 오라클 19c rman incarnation 설명 및 복구 시나리오 (0) | 2023.01.10 |
|---|---|
| 오라클 19c rman catalog 및 catalog db 생성 (0) | 2023.01.10 |
| 오라클 19c ADG 설치3 (0) | 2023.01.05 |
| 오라클 19c ADG 설치2 (0) | 2023.01.05 |
| 오라클 19c ADG 설치1 (0) | 2023.01.05 |