버전 : ORACLE 19.3.0.0
| - 복구 시 필요 파일 : full backup, controlfile, 아카이브 로그 - 복구 순서 1. nomount 상태에서 controlfile 복구 2. mount 후 restore database 3. recover database (원하는 시점까지) 4. database open resetlogs - checkpoint_change# 번호 조회 SQL> SELECT name, checkpoint_change#, checkpoint_time, status FROM v$datafile_header ; |
테스트 데이터 INSERT 및 백업 진행
## 테이블 생성
create table sk.t1 (c1 varchar(10), c2 varchar(10)) ;
## FULL BACKUP 수행
RMAN> backup format '/home/oracle/bak/%d_backup_%s_%p_%T' database ;
Starting backup at 26-MAY-22
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=00007 name=/ORA19/app/oracle/oradata/ORA19C/users01.dbf
input datafile file number=00008 name=/ORA19/app/oracle/oradata/ORA19C/undotbs02.dbf
input datafile file number=00001 name=/ORA19/app/oracle/oradata/ORA19C/system01.dbf
input datafile file number=00003 name=/ORA19/app/oracle/oradata/ORA19C/sysaux01.dbf
input datafile file number=00002 name=/ORA19/app/oracle/oradata/ORA19C/ts02.dbf
input datafile file number=00005 name=/ORA19/app/oracle/oradata/ORA19C/ts01.dbf
channel ORA_DISK_1: starting piece 1 at 26-MAY-22
channel ORA_DISK_1: finished piece 1 at 26-MAY-22
piece handle=/home/oracle/bak/ORA19C_backup_30_1_20220526 tag=TAG20220526T145813 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:03:05
Finished backup at 26-MAY-22
Starting Control File and SPFILE Autobackup at 26-MAY-22
piece handle=/home/oracle/reco/ORA19C/autobackup/2022_05_26/o1_mf_s_1105714878_k8y5xgm2_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 26-MAY-22
## 데이터 INSERT
insert into sk.t1 values (1,'a');
alter system switch logfile;
-- 아카이브 로그 번호 4849
2022-05-26T15:04:10.817504+09:00
Thread 1 advanced to log sequence 4850 (LGWR switch)
Current log# 5 seq# 4850 mem# 0: /ORA19/app/oracle/oradata/ORA19C/redo05.log
2022-05-26T15:04:10.820616+09:00
ARC0 (PID:5788): Archived Log entry 5156 added for T-1.S-4849 ID 0x4368ce8e LAD:1
## 데이터 INSERT
insert into sk.t1 values (2,'a');
alter system switch logfile;
-- 아카이브 로그 번호 4850
2022-05-26T15:04:26.023265+09:00
Thread 1 advanced to log sequence 4851 (LGWR switch)
Current log# 6 seq# 4851 mem# 0: /ORA19/app/oracle/oradata/ORA19C/redo06.log
2022-05-26T15:04:26.026529+09:00
ARC1 (PID:5807): Archived Log entry 5157 added for T-1.S-4850 ID 0x4368ce8e LAD:1
#### 여기까지 복구 예정 아래는 안함 ####
insert into sk.t1 values (3,'a');
commit ;
## controlfile 백업
RMAN> backup current controlfile ;
Starting backup at 26-MAY-22
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 26-MAY-22
channel ORA_DISK_1: finished piece 1 at 26-MAY-22
piece handle=/home/oracle/reco/ORA19C/backupset/2022_05_26/o1_mf_ncnnf_TAG20220526T150446_k8y63zw6_.bkp tag=TAG20220526T150446 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 26-MAY-22
controlfile 복구
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1073737800 bytes
Fixed Size 8904776 bytes
Variable Size 880803840 bytes
Database Buffers 176160768 bytes
Redo Buffers 7868416 bytes
SQL>
[oracle@svr1 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Fri May 27 09:38:01 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA19C (not mounted)
RMAN> list backup ;
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 05/27/2022 09:38:09
ORA-01507: database not mounted
RMAN> restore controlfile from '/home/oracle/bak/o1_mf_ncnnf_TAG20220526T150446_k8y63zw6_.bkp' ;
Starting restore at 27-MAY-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 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/ORA19C/control01.ctl
output file name=/ORA19/app/oracle/oradata/ORA19C/control02.ctl
Finished restore at 27-MAY-22
RMAN> sql "alter database mount" ;
sql statement: alter database mount
released channel: ORA_DISK_1
RMAN> list backup ;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
22 Full 20.45G DISK 00:02:56 26-MAY-22
BP Key: 22 Status: AVAILABLE Compressed: NO Tag: TAG20220526T145813
Piece Name: /home/oracle/bak/ORA19C_backup_30_1_20220526
List of Datafiles in backup set 22
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 11232423 26-MAY-22 NO /ORA19/app/oracle/oradata/ORA19C/system01.dbf
2 Full 11232423 26-MAY-22 NO /ORA19/app/oracle/oradata/ORA19C/ts02.dbf
3 Full 11232423 26-MAY-22 NO /ORA19/app/oracle/oradata/ORA19C/sysaux01.dbf
5 Full 11232423 26-MAY-22 NO /ORA19/app/oracle/oradata/ORA19C/ts01.dbf
7 Full 11232423 26-MAY-22 NO /ORA19/app/oracle/oradata/ORA19C/users01.dbf
8 Full 11232423 26-MAY-22 NO /ORA19/app/oracle/oradata/ORA19C/undotbs02.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
23 Full 23.89M DISK 00:00:00 26-MAY-22
BP Key: 23 Status: AVAILABLE Compressed: NO Tag: TAG20220526T150118
Piece Name: /home/oracle/reco/ORA19C/autobackup/2022_05_26/o1_mf_s_1105714878_k8y5xgm2_.bkp
SPFILE Included: Modification time: 10-MAY-22
SPFILE db_unique_name: ORA19C
Control File Included: Ckp SCN: 11232989 Ckp time: 26-MAY-22
RESTORE DATABASE
- v$datafile_header 조회하여 어디까지 복구 되었는지 확인 가능
RMAN> restore database ;
Starting restore at 27-MAY-22
Starting implicit crosscheck backup at 27-MAY-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK
Crosschecked 19 objects
Finished implicit crosscheck backup at 27-MAY-22
Starting implicit crosscheck copy at 27-MAY-22
using channel ORA_DISK_1
Finished implicit crosscheck copy at 27-MAY-22
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /home/oracle/reco/ORA19C/backupset/2022_05_26/o1_mf_ncnnf_TAG20220526T150446_k8y63zw6_.bkp
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 00001 to /ORA19/app/oracle/oradata/ORA19C/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /ORA19/app/oracle/oradata/ORA19C/ts02.dbf
channel ORA_DISK_1: restoring datafile 00003 to /ORA19/app/oracle/oradata/ORA19C/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /ORA19/app/oracle/oradata/ORA19C/ts01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /ORA19/app/oracle/oradata/ORA19C/users01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /ORA19/app/oracle/oradata/ORA19C/undotbs02.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/bak/ORA19C_backup_30_1_20220526
channel ORA_DISK_1: piece handle=/home/oracle/bak/ORA19C_backup_30_1_20220526 tag=TAG20220526T145813
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:06:25
Finished restore at 27-MAY-22
SQL> select status from v$instance ;
STATUS
------------------------------------
MOUNTED
SQL> SELECT name, checkpoint_change#, checkpoint_time, status FROM v$datafile_header ;
NAME CHECKPOINT_CHANGE# CHECKPOINT_TIME STATUS
-------------------------------------------------- ------------------ ------------------ ---------------------
/ORA19/app/oracle/oradata/ORA19C/system01.dbf 0 ONLINE
/ORA19/app/oracle/oradata/ORA19C/ts02.dbf 11232423 26-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/sysaux01.dbf 0 ONLINE
/ORA19/app/oracle/oradata/ORA19C/ts01.dbf 11232423 26-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/users01.dbf 0 ONLINE
/ORA19/app/oracle/oradata/ORA19C/undotbs02.dbf 0 ONLINE
6 rows selected.
SQL> SELECT *
FROM (
SELECT name, SEQUENCE#, first_change#, first_time, next_change#, next_time
FROM V$ARCHIVED_LOG
ORDER BY recid DESC
)
WHERE rownum <= 10
;
NAME SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
-------------------------------------------------- ---------- ------------- ------------------ ------------ ------------------
/home/oracle/arch/1_4850_1077743123.dbf 4850 11233472 26-MAY-22 11233507 26-MAY-22
/home/oracle/arch/1_4849_1077743123.dbf 4849 11233435 26-MAY-22 11233472 26-MAY-22
/home/oracle/arch/1_4848_1077743123.dbf 4848 11220548 26-MAY-22 11233435 26-MAY-22
/home/oracle/arch/1_4847_1077743123.dbf 4847 11176061 25-MAY-22 11220548 26-MAY-22
/home/oracle/arch/1_4846_1077743123.dbf 4846 11164144 25-MAY-22 11176061 25-MAY-22
/home/oracle/arch/1_4845_1077743123.dbf 4845 11132296 25-MAY-22 11164144 25-MAY-22
/home/oracle/arch/1_4844_1077743123.dbf 4844 11077241 24-MAY-22 11132296 25-MAY-22
/home/oracle/arch/1_4843_1077743123.dbf 4843 11040175 17-MAY-22 11077241 24-MAY-22
/home/oracle/arch/1_4842_1077743123.dbf 4842 11001466 10-MAY-22 11040175 17-MAY-22
/home/oracle/arch/1_4841_1077743123.dbf 4841 10862880 09-MAY-22 11001466 10-MAY-22
10 rows selected.
SQL> SELECT name, checkpoint_change#, checkpoint_time, status FROM v$datafile_header ;
NAME CHECKPOINT_CHANGE# CHECKPOINT_TIME STATUS
-------------------------------------------------- ------------------ ------------------ ---------------------
/ORA19/app/oracle/oradata/ORA19C/system01.dbf 11232423 26-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/ts02.dbf 11232423 26-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/sysaux01.dbf 11232423 26-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/ts01.dbf 11232423 26-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/users01.dbf 0 ONLINE
/ORA19/app/oracle/oradata/ORA19C/undotbs02.dbf 11232423 26-MAY-22 ONLINE
6 rows selected.
-- restore database 진행 후 CHECKPOINT_CHANGE#
SQL> SELECT name, checkpoint_change#, checkpoint_time, status FROM v$datafile ;
NAME CHECKPOINT_CHANGE# CHECKPOINT_TIME STATUS
-------------------------------------------------- ------------------ ------------------ ---------------------
/ORA19/app/oracle/oradata/ORA19C/system01.dbf 11233435 26-MAY-22 SYSTEM
/ORA19/app/oracle/oradata/ORA19C/ts02.dbf 11233435 26-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/sysaux01.dbf 11233435 26-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/ts01.dbf 11233435 26-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/users01.dbf 11233435 26-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/undotbs02.dbf 11233435 26-MAY-22 ONLINE
6 rows selected.
SQL> SELECT name, checkpoint_change#, checkpoint_time, status FROM v$datafile_header ;
NAME CHECKPOINT_CHANGE# CHECKPOINT_TIME STATUS
-------------------------------------------------- ------------------ ------------------ ---------------------
/ORA19/app/oracle/oradata/ORA19C/system01.dbf 11232423 26-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/ts02.dbf 11232423 26-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/sysaux01.dbf 11232423 26-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/ts01.dbf 11232423 26-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/users01.dbf 11232423 26-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/undotbs02.dbf 11232423 26-MAY-22 ONLINE
6 rows selected.
recover database until sequence 4849 진행(CHECKPOINT_CHANGE# 값 변경 확인하려 로그 한개씩 진행 테스트 함)
- 아카이브로그가 반영되며 CHECKPOINT_CHANGE# 값이 변하는 것이 확인 된다
RMAN> recover database until sequence 4849 ;
Starting recover at 27-MAY-22
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 4848 is already on disk as file /home/oracle/arch/1_4848_1077743123.dbf
archived log file name=/home/oracle/arch/1_4848_1077743123.dbf thread=1 sequence=4848
media recovery complete, elapsed time: 00:00:00
Finished recover at 27-MAY-22
-- recover database until sequence 4849 진행 후 CHECKPOINT_CHANGE#
SQL> SELECT name, checkpoint_change#, checkpoint_time, status FROM v$datafile ;
NAME CHECKPOINT_CHANGE# CHECKPOINT_TIME STATUS
-------------------------------------------------- ------------------ ------------------ ---------------------
/ORA19/app/oracle/oradata/ORA19C/system01.dbf 11233435 26-MAY-22 SYSTEM
/ORA19/app/oracle/oradata/ORA19C/ts02.dbf 11233435 26-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/sysaux01.dbf 11233435 26-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/ts01.dbf 11233435 26-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/users01.dbf 11233435 26-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/undotbs02.dbf 11233435 26-MAY-22 ONLINE
6 rows selected.
SQL> SELECT name, checkpoint_change#, checkpoint_time, status FROM v$datafile_header ;
NAME CHECKPOINT_CHANGE# CHECKPOINT_TIME STATUS
-------------------------------------------------- ------------------ ------------------ ---------------------
/ORA19/app/oracle/oradata/ORA19C/system01.dbf 11233435 26-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/ts02.dbf 11233435 26-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/sysaux01.dbf 11233435 26-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/ts01.dbf 11233435 26-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/users01.dbf 11233435 26-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/undotbs02.dbf 11233435 26-MAY-22 ONLINE
6 rows selected.
recover database until sequence 4850 진행(CHECKPOINT_CHANGE# 값 변경 확인하려 로그 한개씩 진행 테스트 함)
- 아카이브로그가 반영되며 CHECKPOINT_CHANGE# 값이 변하는 것이 확인 된다
RMAN> recover database until sequence 4850 ;
Starting recover at 27-MAY-22
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 4849 is already on disk as file /home/oracle/arch/1_4849_1077743123.dbf
archived log file name=/home/oracle/arch/1_4849_1077743123.dbf thread=1 sequence=4849
media recovery complete, elapsed time: 00:00:00
Finished recover at 27-MAY-22
-- recover database until sequence 4850 진행 후 CHECKPOINT_CHANGE#
SQL> SELECT name, checkpoint_change#, checkpoint_time, status FROM v$datafile ;
NAME CHECKPOINT_CHANGE# CHECKPOINT_TIME STATUS
-------------------------------------------------- ------------------ ------------------ ---------------------
/ORA19/app/oracle/oradata/ORA19C/system01.dbf 11233472 26-MAY-22 SYSTEM
/ORA19/app/oracle/oradata/ORA19C/ts02.dbf 11233472 26-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/sysaux01.dbf 11233472 26-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/ts01.dbf 11233472 26-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/users01.dbf 11233472 26-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/undotbs02.dbf 11233472 26-MAY-22 ONLINE
6 rows selected.
SQL> SELECT name, checkpoint_change#, checkpoint_time, status FROM v$datafile_header ;
NAME CHECKPOINT_CHANGE# CHECKPOINT_TIME STATUS
-------------------------------------------------- ------------------ ------------------ ---------------------
/ORA19/app/oracle/oradata/ORA19C/system01.dbf 11233472 26-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/ts02.dbf 11233472 26-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/sysaux01.dbf 11233472 26-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/ts01.dbf 11233472 26-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/users01.dbf 11233472 26-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/undotbs02.dbf 11233472 26-MAY-22 ONLINE
6 rows selected.
DATABASE OPEN
- 불완전 복구이기 때문에 resetlogs 옵션이 필수
RMAN> sql "alter database open" ;
sql statement: alter database open
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 05/27/2022 09:51:17
RMAN-11003: failure during parse/execution of SQL statement: alter database open
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
RMAN> sql "alter database open resetlogs" ;
sql statement: alter database open resetlogs
RMAN>
SQL> select status from v$instance ;
STATUS
------------
OPEN
SQL> SELECT name, checkpoint_change#, checkpoint_time, status FROM v$datafile_header ;
NAME CHECKPOINT_CHANGE# CHECKPOINT_TIME STATUS
-------------------------------------------------- ------------------ ------------------ -------
/ORA19/app/oracle/oradata/ORA19C/system01.dbf 11233476 27-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/ts02.dbf 11233476 27-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/sysaux01.dbf 11233476 27-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/ts01.dbf 11233476 27-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/users01.dbf 11233476 27-MAY-22 ONLINE
/ORA19/app/oracle/oradata/ORA19C/undotbs02.dbf 11233476 27-MAY-22 ONLINE
6 rows selected.
SQL> select * from sk.t1 ;
C1 C2
---------- ----------
1 a
2 a
'DBMS > ORACLE' 카테고리의 다른 글
| ASMLIB to AFD(ASM Filter Driver) 마이그레이션 (ASM SINGLE) (0) | 2022.06.08 |
|---|---|
| RMAN RAC(ASM) to SINGLE(file system) DB_NAME 변경하여 RESTORE (0) | 2022.05.27 |
| 선분이력 테이블 (0) | 2022.05.25 |
| RAC relink 작업 (0) | 2022.05.17 |
| 시퀀스(sequence) last_number 의미와 alter sequence 시 변화 (0) | 2022.05.11 |
댓글