ArchiveMode 에서의 Backup & Recovery-RMAN01
Recovery Manager
- 백업, 복원 및 복구 작업을 관리하는 Oracle 유틸리트
- 운영 체제에 독립접인 명령어
- Command line 또는 EM으로 이용가능
- backup set으로 백업받기
- backup database --backup set으로 백업
- BACKUP AS COMPRESSED BACUPSET DATABASE; --compress backup
- report need backup
- report schema --DB구조
- list backup;
SYS@orcl2> show parameter control_file_record_keep_time
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
-- 컨트롤 파일이 7일동안 유지되고 지나면 override가 된다.
- RMAN 실습
- RMAN CONFIGURE 설정하기
- backup set 으로 백업받기
- Incremantal Backup과 Recovery
- incremental backup 후 users 유실 - restore & recovery 과정
- Multisection backup
- CROSSCHECK backupset
- DELETE backupset
- recovery advisor : system 테이블스페이스 장애후 advisor 이용한 복구
- recovery advisor : non system 테이블스페이스 장애후 advisor 이용한 복구
- offline recovery : system
- online recovery : users
- 데이타 파일을 다른 Disk로 복구 : set newname
추가정보
@con
SYS@orcl2> select name from v$controlfile;
@log
select group#, sequence#, status, archived, members, first_change#, bytes/1024/1024 mb
from v$log;
@logfile
col status for a10
col member for a35
SELECT a.group# ,b.sequence#,a.member , b.bytes/1024/1024 MB , b.archived , b.status, b.first_change#
FROM v$logfile a , v$log b
WHERE a.group#=b.group#
ORDER BY 1;
@data
col name for a35
select name, status from v$datafile;
@datafile
col name1 for a15
col name2 for a35
select a.file#,b.name name1,a.name name2,a.status,a.checkpoint_change# from v$datafile a, v$tablespace b where a.ts#=b.ts#;
@switch
alter system switch logfile;
@check
alter system checkpoint;
@logmnr
col filename for a35
select db_name, filename from v$logmnr_logs;
@track
col filename format a50
select status,filename,bytes/1024/1024 mb from v$block_change_tracking;
@db_list
set echo off
set pages 0
set head off
set feed off
create pfile from spfile;
spool orcl2_backup.sh
select 'cp -av ' || name||' /home/oracle/backup/arch/cold'
from v$controlfile
union all
select 'cp -av ' || name||' /home/oracle/backup/arch/cold'
from v$datafile
union all
select 'cp -av ' || member ||' /home/oracle/backup/arch/cold'
from v$logfile;
select 'cp -av $ORACLE_HOME/dbs/initorcl2.ora /home/oracle/backup/arch/cold' from dual;
spool off
set feedback on
set head on
set pages 100
set echo on
1. noarch 모드에서의 RMAN 백업
/*check할 점
noarchive mode는 운영중 backup을 받을 수 없었음(user_managed backup을 수행 시)
RMAN은 datafile에 대한 정보를 cotnrol file로부터 읽어들입니다.
RMAN도 coldbackup을 받아야 할 때는 DB를 mount 상태에서 backup 수행
noarchive는 필수로 coldbackup을 수행해야 하고
user-managed나 RMAN이나 DB를 정상적으로 종료한 이후 백업을 수행한다.
RMAN은 DB를 mount까지만 올려놓고 수행.
<주의점>
RMAN은 redo를 백업 정책에 포함하지 아니함.
*/
2. arch 모드에서의 RMAN 백업
[orcl2:~]$ export NLS_DATE_FORMAT="RR/MM/DD HH24:MI:SS"
1. DB 정보확인
SYS@orcl2> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/arch2
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
SYS@orcl2> @logfile
GROUP# SEQUENCE# MEMBER MB ARC STATUS FIRST_CHANGE#
---------- ---------- ----------------------------------- ---------- --- ---------- -------------
1 4 /u01/app/oracle/orcl2/redo01.log 50 NO CURRENT 1172923
2 2 /u01/app/oracle/orcl2/redo02.log 50 YES INACTIVE 1172915
3 3 /u01/app/oracle/orcl2/redo03.log 50 YES INACTIVE 1172918
[orcl2:~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Apr 18 11:56:36 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL2 (DBID=986068470)
2. /*RMAN의 configure를 조회; #default는 현재 값이 기본값임을 의미함.*/
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL2 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
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 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 ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl2.f'; # default
3. /*RMAN의 config를 일부 변경 할 것임.*/
/* disk 장비에 대한 backup*/
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK;
using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters are successfully stored
/*backuppiece file의 저장경로(및 이름)의 형식을 지정.
%U는 16진수의 unique value(식별자), %T는 YYYYMMDD의 날짜정보
*/
RMAN> configure channel device type disk format '/home/oracle/backup/rman/%U_%T';
-- DISK에 쓰는 프로세스가 이 경로에 이러한 포맷으로 이름을 만들라는 뜻(U : 16진수 구분자, T: 시간)
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/home/oracle/backup/rman/%U_%T';
new RMAN configuration parameters are successfully stored
/*backup 수행시 control file+spfile에 대한 backup을 자동으로 수행하도록 하는 옵션. 기본값이 off이므로 꼭 on해줍시다.*/
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
/*backup될 control file과 spfile은 datafile backup작업과 별도의 backupset을 사용하므로 format도 다르게 명시해주어야 한다.
%F는 DBID(1440943150)를 명시함.
*/
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/backup/rman/%F';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/backup/rman/%F';
new RMAN configuration parameters are successfully stored
==================================================================================================
SYS@orcl2> select * from v$rman_configuration;
CONF# NAME VALUE
---------- ----------------------------------- -----------------------------------
1 DEFAULT DEVICE TYPE TO DISK
2 CHANNEL DEVICE TYPE DISK FORMAT '/home/or
acle/backup/rman/%U_%T'
3 CONTROLFILE AUTOBACKUP ON
4 CONTROLFILE AUTOBACKUP FORMAT FOR D DISK TO '/home/oracle/backup/rman/%
EVICE TYPE F'
-- default 값이 아닌 것을 확인 가능하다.
==================================================================================================
4. /*백업이 필요한 datafile들을 보여줍니다.*/
/*need의 기준은 retention policy 의 redundancy 값이 1이므로 이보다 미만인 #bkps에 대하여*/
RMAN> report need backup;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
1 0 /u01/app/oracle/orcl2/system01.dbf
2 0 /u01/app/oracle/orcl2/sysaux01.dbf
3 0 /u01/app/oracle/orcl2/undotbs01.dbf
4 0 /u01/app/oracle/orcl2/users01.dbf
5 0 /u01/app/oracle/orcl2/example01.dbf
RMAN> report schema;
Report of database schema for database with db_unique_name ORCL2
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 680 SYSTEM *** /u01/app/oracle/orcl2/system01.dbf
2 500 SYSAUX *** /u01/app/oracle/orcl2/sysaux01.dbf
3 100 UNDOTBS1 *** /u01/app/oracle/orcl2/undotbs01.dbf
4 5 USERS *** /u01/app/oracle/orcl2/users01.dbf
5 100 EXAMPLE *** /u01/app/oracle/orcl2/example01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 29 TEMP 32767 /u01/app/oracle/orcl2/temp01.dbf
5. 백업 수행(backup set)
RMAN> backup database;
Starting backup at 18-APR-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/orcl2/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/orcl2/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/orcl2/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/orcl2/example01.dbf
input datafile file number=00004 name=/u01/app/oracle/orcl2/users01.dbf
channel ORA_DISK_1: starting piece 1 at 18-APR-19
channel ORA_DISK_1: finished piece 1 at 18-APR-19
piece handle=/home/oracle/backup/rman/01tva0cf_1_1_20190418 tag=TAG20190418T120743 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 18-APR-19
Starting Control File and SPFILE Autobackup at 18-APR-19
piece handle=/home/oracle/backup/rman/c-986068470-20190418-00 comment=NONE
Finished Control File and SPFILE Autobackup at 18-APR-19
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 1.03G DISK 00:00:38 18-APR-19
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20190418T120743
Piece Name: /home/oracle/backup/rman/01tva0cf_1_1_20190418
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1173524 18-APR-19 /u01/app/oracle/orcl2/system01.dbf
2 Full 1173524 18-APR-19 /u01/app/oracle/orcl2/sysaux01.dbf
3 Full 1173524 18-APR-19 /u01/app/oracle/orcl2/undotbs01.dbf
4 Full 1173524 18-APR-19 /u01/app/oracle/orcl2/users01.dbf
5 Full 1173524 18-APR-19 /u01/app/oracle/orcl2/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 9.67M DISK 00:00:01 18-APR-19
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20190418T120828
Piece Name: /home/oracle/backup/rman/c-986068470-20190418-00
SPFILE Included: Modification time: 18-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1173550 Ckp time: 18-APR-19
-- 한번 백업을 받았는데 2번이 있는 이유는 오토백업이므로 c-로도 저장되기때문
6. 수동 채널 할당
/*run {} 문장은 중괄호 안의 명령을 묶어서 한꺼번에 처리하는 역할*/
/*이 경우는 channel을 user가 직접 지정해서 수동으로 backup하는 형식*/
RMAN> run {
allocate channel c1 type disk format '/home/oracle/backup/rman/%U_%T';
backup database;
}
released channel: ORA_DISK_1
allocated channel: c1
channel c1: SID=21 device type=DISK
Starting backup at 18-APR-19
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/orcl2/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/orcl2/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/orcl2/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/orcl2/example01.dbf
input datafile file number=00004 name=/u01/app/oracle/orcl2/users01.dbf
channel c1: starting piece 1 at 18-APR-19
channel c1: finished piece 1 at 18-APR-19
piece handle=/home/oracle/backup/rman/03tva5fb_1_1_20190418 tag=TAG20190418T133435 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:45
Finished backup at 18-APR-19
Starting Control File and SPFILE Autobackup at 18-APR-19
piece handle=/home/oracle/backup/rman/c-986068470-20190418-01 comment=NONE
Finished Control File and SPFILE Autobackup at 18-APR-19
released channel: c1
7. compressed backup
RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE;
Starting backup at 18-APR-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/orcl2/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/orcl2/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/orcl2/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/orcl2/example01.dbf
input datafile file number=00004 name=/u01/app/oracle/orcl2/users01.dbf
channel ORA_DISK_1: starting piece 1 at 18-APR-19
channel ORA_DISK_1: finished piece 1 at 18-APR-19
piece handle=/home/oracle/backup/rman/05tva630_1_1_20190418 tag=TAG20190418T134504 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 18-APR-19
Starting Control File and SPFILE Autobackup at 18-APR-19
piece handle=/home/oracle/backup/rman/c-986068470-20190418-02 comment=NONE
Finished Control File and SPFILE Autobackup at 18-APR-19
8. partial backup
RMAN> backup tablespace example format '/home/oracle/backup/rman/example_%T';
Starting backup at 18-APR-19
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=/u01/app/oracle/orcl2/example01.dbf
channel ORA_DISK_1: starting piece 1 at 18-APR-19
channel ORA_DISK_1: finished piece 1 at 18-APR-19
piece handle=/home/oracle/backup/rman/example_20190418 tag=TAG20190418T134553 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 18-APR-19
Starting Control File and SPFILE Autobackup at 18-APR-19
piece handle=/home/oracle/backup/rman/c-986068470-20190418-03 comment=NONE
Finished Control File and SPFILE Autobackup at 18-APR-19
***백업 목록 확인
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 1.03G DISK 00:00:38 18-APR-19
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20190418T120743
Piece Name: /home/oracle/backup/rman/01tva0cf_1_1_20190418
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1173524 18-APR-19 /u01/app/oracle/orcl2/system01.dbf
2 Full 1173524 18-APR-19 /u01/app/oracle/orcl2/sysaux01.dbf
3 Full 1173524 18-APR-19 /u01/app/oracle/orcl2/undotbs01.dbf
4 Full 1173524 18-APR-19 /u01/app/oracle/orcl2/users01.dbf
5 Full 1173524 18-APR-19 /u01/app/oracle/orcl2/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 9.67M DISK 00:00:01 18-APR-19
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20190418T120828
Piece Name: /home/oracle/backup/rman/c-986068470-20190418-00
SPFILE Included: Modification time: 18-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1173550 Ckp time: 18-APR-19
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 1.03G DISK 00:00:41 18-APR-19
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20190418T133435
Piece Name: /home/oracle/backup/rman/03tva5fb_1_1_20190418
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1177010 18-APR-19 /u01/app/oracle/orcl2/system01.dbf
2 Full 1177010 18-APR-19 /u01/app/oracle/orcl2/sysaux01.dbf
3 Full 1177010 18-APR-19 /u01/app/oracle/orcl2/undotbs01.dbf
4 Full 1177010 18-APR-19 /u01/app/oracle/orcl2/users01.dbf
5 Full 1177010 18-APR-19 /u01/app/oracle/orcl2/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 9.67M DISK 00:00:00 18-APR-19
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20190418T133520
Piece Name: /home/oracle/backup/rman/c-986068470-20190418-01
SPFILE Included: Modification time: 18-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1177032 Ckp time: 18-APR-19
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 277.30M DISK 00:00:39 18-APR-19
BP Key: 5 Status: AVAILABLE Compressed: YES Tag: TAG20190418T134504
Piece Name: /home/oracle/backup/rman/05tva630_1_1_20190418
List of Datafiles in backup set 5
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1177332 18-APR-19 /u01/app/oracle/orcl2/system01.dbf
2 Full 1177332 18-APR-19 /u01/app/oracle/orcl2/sysaux01.dbf
3 Full 1177332 18-APR-19 /u01/app/oracle/orcl2/undotbs01.dbf
4 Full 1177332 18-APR-19 /u01/app/oracle/orcl2/users01.dbf
5 Full 1177332 18-APR-19 /u01/app/oracle/orcl2/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6 Full 9.67M DISK 00:00:01 18-APR-19
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20190418T134549
Piece Name: /home/oracle/backup/rman/c-986068470-20190418-02
SPFILE Included: Modification time: 18-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1177360 Ckp time: 18-APR-19
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7 Full 69.05M DISK 00:00:01 18-APR-19
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20190418T134553
Piece Name: /home/oracle/backup/rman/example_20190418
List of Datafiles in backup set 7
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
5 Full 1177376 18-APR-19 /u01/app/oracle/orcl2/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8 Full 9.67M DISK 00:00:00 18-APR-19
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20190418T134557
Piece Name: /home/oracle/backup/rman/c-986068470-20190418-03
SPFILE Included: Modification time: 18-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1177383 Ckp time: 18-APR-19
1) <BACKUP AS COPY DATABASE> - image copy 형식으로 backup을 수행
2) datafile 장애후 복구
[orcl2:rman]$ mkdir copy
[orcl2:copy]$ pwd
/home/oracle/backup/rman/copy
[orcl2:~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Apr 18 13:50:22 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL2 (DBID=986068470)
RMAN> run {
allocate channel c1 type disk format '/home/oracle/backup/rman/copy/%U';
backup as copy database;
}
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=145 device type=DISK
Starting backup at 18-APR-19
channel c1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/orcl2/system01.dbf
output file name=/home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-SYSTEM_FNO-1_09tva6e7 tag=TAG20190418T135103 RECID=1 STAMP=1005918679
channel c1: datafile copy complete, elapsed time: 00:00:25
channel c1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/orcl2/sysaux01.dbf
output file name=/home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-SYSAUX_FNO-2_0atva6f0 tag=TAG20190418T135103 RECID=2 STAMP=1005918706
channel c1: datafile copy complete, elapsed time: 00:00:25
channel c1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/orcl2/undotbs01.dbf
output file name=/home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-UNDOTBS1_FNO-3_0btva6fp tag=TAG20190418T135103 RECID=3 STAMP=1005918717
channel c1: datafile copy complete, elapsed time: 00:00:07
channel c1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/orcl2/example01.dbf
output file name=/home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-EXAMPLE_FNO-5_0ctva6g0 tag=TAG20190418T135103 RECID=4 STAMP=1005918723
channel c1: datafile copy complete, elapsed time: 00:00:07
channel c1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/orcl2/users01.dbf
output file name=/home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-USERS_FNO-4_0dtva6g7 tag=TAG20190418T135103 RECID=5 STAMP=1005918727
channel c1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 18-APR-19
Starting Control File and SPFILE Autobackup at 18-APR-19
piece handle=/home/oracle/backup/rman/c-986068470-20190418-04 comment=NONE
Finished Control File and SPFILE Autobackup at 18-APR-19
released channel: c1
/*IMAGE COPY 형식으로 받아둔 BACKUPFILE을 확인함니다.*/
RMAN> LIST DATAFILECOPY ALL;
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
1 1 A 18-APR-19 1177616 18-APR-19
Name: /home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-SYSTEM_FNO-1_09tva6e7
Tag: TAG20190418T135103
2 2 A 18-APR-19 1177632 18-APR-19
Name: /home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-SYSAUX_FNO-2_0atva6f0
Tag: TAG20190418T135103
3 3 A 18-APR-19 1177639 18-APR-19
Name: /home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-UNDOTBS1_FNO-3_0btva6fp
Tag: TAG20190418T135103
5 4 A 18-APR-19 1177645 18-APR-19
Name: /home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-USERS_FNO-4_0dtva6g7
Tag: TAG20190418T135103
4 5 A 18-APR-19 1177642 18-APR-19
Name: /home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-EXAMPLE_FNO-5_0ctva6g0
Tag: TAG20190418T135103
RMAN> list archivelog all;
List of Archived Log Copies for database with db_unique_name ORCL2
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
1 1 9 A 17-APR-19
Name: /home/oracle/arch1/arch_1_9_1005837298.arc
2 1 9 A 17-APR-19
Name: /home/oracle/arch2/arch_1_9_1005837298.arc
..........................
22 1 2 A 18-APR-19
Name: /home/oracle/arch2/arch_1_2_1005906168.arc
23 1 3 A 18-APR-19
Name: /home/oracle/arch1/arch_1_3_1005906168.arc
24 1 3 A 18-APR-19
Name: /home/oracle/arch2/arch_1_3_1005906168.arc
/*IMAGE FILE, ARCHIVELOG FILE의 백업본을 같이 조회*/
RMAN> list copy;
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
1 1 A 18-APR-19 1177616 18-APR-19
Name: /home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-SYSTEM_FNO-1_09tva6e7
Tag: TAG20190418T135103
2 2 A 18-APR-19 1177632 18-APR-19
Name: /home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-SYSAUX_FNO-2_0atva6f0
Tag: TAG20190418T135103
3 3 A 18-APR-19 1177639 18-APR-19
Name: /home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-UNDOTBS1_FNO-3_0btva6fp
Tag: TAG20190418T135103
5 4 A 18-APR-19 1177645 18-APR-19
Name: /home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-USERS_FNO-4_0dtva6g7
Tag: TAG20190418T135103
4 5 A 18-APR-19 1177642 18-APR-19
Name: /home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-EXAMPLE_FNO-5_0ctva6g0
Tag: TAG20190418T135103
List of Archived Log Copies for database with db_unique_name ORCL2
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
1 1 9 A 17-APR-19
Name: /home/oracle/arch1/arch_1_9_1005837298.arc
2 1 9 A 17-APR-19
Name: /home/oracle/arch2/arch_1_9_1005837298.arc
..........................
22 1 2 A 18-APR-19
Name: /home/oracle/arch2/arch_1_2_1005906168.arc
23 1 3 A 18-APR-19
Name: /home/oracle/arch1/arch_1_3_1005906168.arc
24 1 3 A 18-APR-19
Name: /home/oracle/arch2/arch_1_3_1005906168.arc
/*IMAGE COPY 형식이라서 기존 파일과 용량 차이가 거의 나지 않음.*/
[orcl2:copy]$ ls -lhS
total 1.4G
-rw-r----- 1 oracle dba 681M Apr 18 13:51 data_D-ORCL2_I-986068470_TS-SYSTEM_FNO-1_09tva6e7
-rw-r----- 1 oracle dba 521M Apr 18 13:51 data_D-ORCL2_I-986068470_TS-SYSAUX_FNO-2_0atva6f0
-rw-r----- 1 oracle dba 106M Apr 18 13:51 data_D-ORCL2_I-986068470_TS-UNDOTBS1_FNO-3_0btva6fp
-rw-r----- 1 oracle dba 101M Apr 18 13:52 data_D-ORCL2_I-986068470_TS-EXAMPLE_FNO-5_0ctva6g0
-rw-r----- 1 oracle dba 5.1M Apr 18 13:52 data_D-ORCL2_I-986068470_TS-USERS_FNO-4_0dtva6g7
[orcl2:orcl2]$ ls -lhS *.dbf
-rw-r----- 1 oracle dba 681M Apr 18 13:58 system01.dbf
-rw-r----- 1 oracle dba 521M Apr 18 13:57 sysaux01.dbf
-rw-r----- 1 oracle dba 106M Apr 18 13:58 undotbs01.dbf
-rw-r----- 1 oracle dba 101M Apr 18 13:52 example01.dbf
-rw-r----- 1 oracle dba 11M Apr 16 11:57 data02.dbf
-rw-r----- 1 oracle dba 11M Apr 16 15:44 insa_tbs01.dbf
-rw-r----- 1 oracle dba 5.1M Apr 18 10:19 data01.dbf
-rw-r----- 1 oracle dba 5.1M Apr 18 13:52 users01.dbf
2. DBA SESSION 에서 DB 작업
SYS@orcl2> @logfile
GROUP# SEQUENCE# MEMBER MB ARC STATUS FIRST_CHANGE#
---------- ---------- ----------------------------------- ---------- --- ---------- -------------
1 4 /u01/app/oracle/orcl2/redo01.log 50 NO CURRENT 1172923
2 2 /u01/app/oracle/orcl2/redo02.log 50 YES INACTIVE 1172915
3 3 /u01/app/oracle/orcl2/redo03.log 50 YES INACTIVE 1172918
SYS@orcl2> @datafile
FILE# NAME1 NAME2 STATUS CHECKPOINT_CHANGE#
---------- --------------- ----------------------------------- ---------- ------------------
1 SYSTEM /u01/app/oracle/orcl2/system01.dbf SYSTEM 1177616
2 SYSAUX /u01/app/oracle/orcl2/sysaux01.dbf ONLINE 1177632
3 UNDOTBS1 /u01/app/oracle/orcl2/undotbs01.dbf ONLINE 1177639
4 USERS /u01/app/oracle/orcl2/users01.dbf ONLINE 1177645
5 EXAMPLE /u01/app/oracle/orcl2/example01.dbf ONLINE 1177642
-- 테이블 만들어서 데이타 입력
SYS@orcl2> create table hr.t1 (a number);
Table created.
SYS@orcl2> insert into hr.t1 values (1);
1 row created.
SYS@orcl2> commit;
Commit complete.
SYS@orcl2> @switch
System altered.
SYS@orcl2> /
System altered.
SYS@orcl2> /
System altered.
3. 장애 발생 : 모든 데이타 파일 삭제
SYS@orcl2> ! rm /u01/app/oracle/orcl2/*.dbf
SYS@orcl2> ! ls /u01/app/oracle/orcl2/*.dbf
ls: /u01/app/oracle/orcl2/*.dbf: No such file or directory
SYS@orcl2> @check
System altered.
/*dictionary table이 존재하던 system datafile이 없어서 error*/
SYS@orcl2> conn hr/hr
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/orcl2/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Warning: You are no longer connected to ORACLE.
4. 문제 파악
RMAN> list failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
1722 CRITICAL OPEN 18-APR-19 System datafile 1: '/u01/app/oracle/orcl2/system01.dbf' is missing
920 CRITICAL OPEN 17-APR-19 System datafile 1: '/home/oracle/temp/system01.dbf' needs media recovery
5 CRITICAL OPEN 10-APR-19 System datafile 1: '/u01/app/oracle/orcl2/system01.dbf' needs media recovery
42 HIGH OPEN 11-APR-19 One or more non-system datafiles are missing
8 HIGH OPEN 10-APR-19 One or more non-system datafiles need media recovery
242 HIGH OPEN 16-APR-19 One or more non-system datafiles are offline
RMAN> list failure 42 detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
42 HIGH OPEN 11-APR-19 One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 42
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
1734 HIGH OPEN 18-APR-19 Datafile 3: '/u01/app/oracle/orcl2/undotbs01.dbf' is missing
Impact: Some objects in tablespace UNDOTBS1 might be unavailable
1728 HIGH OPEN 18-APR-19 Datafile 2: '/u01/app/oracle/orcl2/sysaux01.dbf' is missing
Impact: Some objects in tablespace SYSAUX might be unavailable
165 HIGH OPEN 15-APR-19 Datafile 4: '/u01/app/oracle/orcl2/users01.dbf' is missing
Impact: Some objects in tablespace USERS might be unavailable
45 HIGH OPEN 11-APR-19 Datafile 5: '/u01/app/oracle/orcl2/example01.dbf' is missing
Impact: Some objects in tablespace EXAMPLE might be unavailable
RMAN> list failure 8 detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
8 HIGH OPEN 10-APR-19 One or more non-system datafiles need media recovery
Impact: See impact for individual child failures
List of child failures for parent failure ID 8
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
944 HIGH OPEN 17-APR-19 Datafile 5: '/u01/app/oracle/orcl2/example01.dbf' needs media recovery
Impact: Some objects in tablespace EXAMPLE might be unavailable
23 HIGH OPEN 10-APR-19 Datafile 4: '/u01/app/oracle/orcl2/users01.dbf' needs media recovery
Impact: Some objects in tablespace USERS might be unavailable
17 HIGH OPEN 10-APR-19 Datafile 3: '/u01/app/oracle/orcl2/undotbs01.dbf' needs media recovery
Impact: Some objects in tablespace UNDOTBS1 might be unavailable
11 HIGH OPEN 10-APR-19 Datafile 2: '/u01/app/oracle/orcl2/sysaux01.dbf' needs media recovery
Impact: Some objects in tablespace SYSAUX might be unavailable
-- 자세하게 볼 수 있다.
RMAN> shutdown abort
Oracle instance shut down
RMAN> startup mount
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 431038464 bytes
Fixed Size 1337016 bytes
Variable Size 146803016 bytes
Database Buffers 276824064 bytes
Redo Buffers 6074368 bytes
5. 복원
/*restore에 필요한 요소들을 조회.*/
RMAN> restore database preview summary;
Starting restore at 18-APR-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
1 1 A 18-APR-19 1177616 18-APR-19
Name: /home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-SYSTEM_FNO-1_09tva6e7
Tag: TAG20190418T135103
2 2 A 18-APR-19 1177632 18-APR-19
Name: /home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-SYSAUX_FNO-2_0atva6f0
Tag: TAG20190418T135103
3 3 A 18-APR-19 1177639 18-APR-19
Name: /home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-UNDOTBS1_FNO-3_0btva6fp
Tag: TAG20190418T135103
5 4 A 18-APR-19 1177645 18-APR-19
Name: /home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-USERS_FNO-4_0dtva6g7
Tag: TAG20190418T135103
4 5 A 18-APR-19 1177642 18-APR-19
Name: /home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-EXAMPLE_FNO-5_0ctva6g0
Tag: TAG20190418T135103
using channel ORA_DISK_1
List of Archived Log Copies for database with db_unique_name ORCL2
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
25 1 4 A 18-APR-19
Name: /home/oracle/arch1/arch_1_4_1005906168.arc
27 1 5 A 18-APR-19
Name: /home/oracle/arch1/arch_1_5_1005906168.arc
29 1 6 A 18-APR-19
Name: /home/oracle/arch1/arch_1_6_1005906168.arc
Media recovery start SCN is 1177616
Recovery must be done beyond SCN 1177645 to clear datafile fuzziness
Finished restore at 18-APR-19
RMAN> restore database;
Starting restore at 18-APR-19
using channel ORA_DISK_1
channel ORA_DISK_1: restoring datafile 00001
input datafile copy RECID=1 STAMP=1005918679 file name=/home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-SYSTEM_FNO-1_09tva6e7
destination for restore of datafile 00001: /u01/app/oracle/orcl2/system01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00001
output file name=/u01/app/oracle/orcl2/system01.dbf RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00002
input datafile copy RECID=2 STAMP=1005918706 file name=/home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-SYSAUX_FNO-2_0atva6f0
destination for restore of datafile 00002: /u01/app/oracle/orcl2/sysaux01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00002
output file name=/u01/app/oracle/orcl2/sysaux01.dbf RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00003
input datafile copy RECID=3 STAMP=1005918717 file name=/home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-UNDOTBS1_FNO-3_0btva6fp
destination for restore of datafile 00003: /u01/app/oracle/orcl2/undotbs01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00003
output file name=/u01/app/oracle/orcl2/undotbs01.dbf RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00004
input datafile copy RECID=5 STAMP=1005918727 file name=/home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-USERS_FNO-4_0dtva6g7
destination for restore of datafile 00004: /u01/app/oracle/orcl2/users01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00004
output file name=/u01/app/oracle/orcl2/users01.dbf RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00005
input datafile copy RECID=4 STAMP=1005918723 file name=/home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-EXAMPLE_FNO-5_0ctva6g0
destination for restore of datafile 00005: /u01/app/oracle/orcl2/example01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00005
output file name=/u01/app/oracle/orcl2/example01.dbf RECID=0 STAMP=0
Finished restore at 18-APR-19
6. 복구
RMAN> recover database;
Starting recover at 18-APR-19
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 4 is already on disk as file /home/oracle/arch1/arch_1_4_1005906168.arc
archived log for thread 1 with sequence 5 is already on disk as file /home/oracle/arch1/arch_1_5_1005906168.arc
archived log for thread 1 with sequence 6 is already on disk as file /home/oracle/arch1/arch_1_6_1005906168.arc
archived log file name=/home/oracle/arch1/arch_1_4_1005906168.arc thread=1 sequence=4
media recovery complete, elapsed time: 00:00:00
Finished recover at 18-APR-19
-- 4를 적용하고, 5와 6은 현재 DB의 REDO LOG에서 가져온 것이다 즉 현재의 CURRENT는 7이 된다.
RMAN> alter database open;
database opened
7.백업본을 정리
RMAN> list datafilecopy all;
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
1 1 A 18-APR-19 1177616 18-APR-19
Name: /home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-SYSTEM_FNO-1_09tva6e7
Tag: TAG20190418T135103
2 2 A 18-APR-19 1177632 18-APR-19
Name: /home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-SYSAUX_FNO-2_0atva6f0
Tag: TAG20190418T135103
3 3 A 18-APR-19 1177639 18-APR-19
Name: /home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-UNDOTBS1_FNO-3_0btva6fp
Tag: TAG20190418T135103
5 4 A 18-APR-19 1177645 18-APR-19
Name: /home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-USERS_FNO-4_0dtva6g7
Tag: TAG20190418T135103
4 5 A 18-APR-19 1177642 18-APR-19
Name: /home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-EXAMPLE_FNO-5_0ctva6g0
Tag: TAG20190418T135103
RMAN> delete datafilecopy all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
1 1 A 18-APR-19 1177616 18-APR-19
Name: /home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-SYSTEM_FNO-1_09tva6e7
Tag: TAG20190418T135103
2 2 A 18-APR-19 1177632 18-APR-19
Name: /home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-SYSAUX_FNO-2_0atva6f0
Tag: TAG20190418T135103
3 3 A 18-APR-19 1177639 18-APR-19
Name: /home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-UNDOTBS1_FNO-3_0btva6fp
Tag: TAG20190418T135103
5 4 A 18-APR-19 1177645 18-APR-19
Name: /home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-USERS_FNO-4_0dtva6g7
Tag: TAG20190418T135103
4 5 A 18-APR-19 1177642 18-APR-19
Name: /home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-EXAMPLE_FNO-5_0ctva6g0
Tag: TAG20190418T135103
Do you really want to delete the above objects (enter YES or NO)? YES
deleted datafile copy
datafile copy file name=/home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-SYSTEM_FNO-1_09tva6e7 RECID=1 STAMP=1005918679
deleted datafile copy
datafile copy file name=/home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-SYSAUX_FNO-2_0atva6f0 RECID=2 STAMP=1005918706
deleted datafile copy
datafile copy file name=/home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-UNDOTBS1_FNO-3_0btva6fp RECID=3 STAMP=1005918717
deleted datafile copy
datafile copy file name=/home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-USERS_FNO-4_0dtva6g7 RECID=5 STAMP=1005918727
deleted datafile copy
datafile copy file name=/home/oracle/backup/rman/copy/data_D-ORCL2_I-986068470_TS-EXAMPLE_FNO-5_0ctva6g0 RECID=4 STAMP=1005918723
Deleted 5 objects
[orcl2:copy]$ ls
없음
RMAN> LIST DATAFILECOPY ALL;
specification does not match any datafile copy in the repository
RMAN 백업 유형
- Full 백업은 사용된 모든 데이터 파일 블록을 포함
- 레벨 0 Incremental 백업은 레벨 0으로 표시된 Full 백업과 동일
- Cumulative 레벨 1 Incremental 백업은 마지막 레벨 0 Incremental 백업 이후 수정한 블록만 포함
- Differential 레벨 1 Incremental 백업은 마지막 Incremental 백업 이후 수정한 블록만 포함
- 빠른 Incremental 백업
- Block Change Tracking 에 의해 구현된다.
Incremantal Backup과 Recovery
incremental backup 후 users 유실 - restore & recovery 과정
- 레벨 0에서 Incremental
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;
- Differential incremental
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;
- Cumulative incremental
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;
Block Change Tracking
- 변경 사항 추적 파일에서 변경된 블록 기록, CTWR
- 활성화된 경우 RMAN에 의해 자동으로 사용됨
SYS@orcl2> alter database enable block change tracking using file '/home/oracle/backup/rman/block_tracking.txt';
- 백업 중에 전체 데이터 파일을 스캔하지 않도록 Incremental 백업 최적화
- db_create_file_dest 파라미터를 설정한 경우 블록 변경 사항 추적 파일의 이름을 지정할 필요가 없습니다. OMF(Oracle-managed file)가 생성됩니다.
SYS@orcl2> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;
SYS@orcl2> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
- V$BLOCK_CHANGE_TRACKING
1. RMAN SESSION
-- 기존의 백업을 전부 삭제
RMAN> delete backupset;
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
1 1 1 1 AVAILABLE DISK /home/oracle/backup/rman/01tva0cf_1_1_20190418
2 2 1 1 AVAILABLE DISK /home/oracle/backup/rman/c-986068470-20190418-00
3 3 1 1 AVAILABLE DISK /home/oracle/backup/rman/03tva5fb_1_1_20190418
4 4 1 1 AVAILABLE DISK /home/oracle/backup/rman/c-986068470-20190418-01
5 5 1 1 AVAILABLE DISK /home/oracle/backup/rman/05tva630_1_1_20190418
6 6 1 1 AVAILABLE DISK /home/oracle/backup/rman/c-986068470-20190418-02
7 7 1 1 AVAILABLE DISK /home/oracle/backup/rman/example_20190418
8 8 1 1 AVAILABLE DISK /home/oracle/backup/rman/c-986068470-20190418-03
9 9 1 1 AVAILABLE DISK /home/oracle/backup/rman/c-986068470-20190418-04
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/home/oracle/backup/rman/01tva0cf_1_1_20190418 RECID=1 STAMP=1005912463
deleted backup piece
backup piece handle=/home/oracle/backup/rman/c-986068470-20190418-00 RECID=2 STAMP=1005912509
deleted backup piece
backup piece handle=/home/oracle/backup/rman/03tva5fb_1_1_20190418 RECID=3 STAMP=1005917675
deleted backup piece
backup piece handle=/home/oracle/backup/rman/c-986068470-20190418-01 RECID=4 STAMP=1005917720
deleted backup piece
backup piece handle=/home/oracle/backup/rman/05tva630_1_1_20190418 RECID=5 STAMP=1005918304
deleted backup piece
backup piece handle=/home/oracle/backup/rman/c-986068470-20190418-02 RECID=6 STAMP=1005918349
deleted backup piece
backup piece handle=/home/oracle/backup/rman/example_20190418 RECID=7 STAMP=1005918354
deleted backup piece
backup piece handle=/home/oracle/backup/rman/c-986068470-20190418-03 RECID=8 STAMP=1005918357
deleted backup piece
backup piece handle=/home/oracle/backup/rman/c-986068470-20190418-04 RECID=9 STAMP=1005918728
Deleted 9 objects
RMAN> list backup;
specification does not match any backup in the repository
/*참고용
RMAN> run {
allocate channel c1 type disk;
allocate channel c2 type disk;
backup incremental level 0 database format '/home/oracle/backup/rman/%U_%T';
}
*/
released channel: ORA_DISK_1
allocated channel: c1
channel c1: SID=133 device type=DISK
allocated channel: c2
channel c2: SID=21 device type=DISK
Starting backup at 18-APR-19
channel c1: starting incremental level 0 datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/orcl2/system01.dbf
input datafile file number=00005 name=/u01/app/oracle/orcl2/example01.dbf
channel c1: starting piece 1 at 18-APR-19
channel c2: starting incremental level 0 datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/orcl2/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/orcl2/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/orcl2/users01.dbf
channel c2: starting piece 1 at 18-APR-19
channel c1: finished piece 1 at 18-APR-19
piece handle=/home/oracle/backup/rman/0ftva9en_1_1_20190418 tag=TAG20190418T144231 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:15
channel c2: finished piece 1 at 18-APR-19
piece handle=/home/oracle/backup/rman/0gtva9en_1_1_20190418 tag=TAG20190418T144231 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:15
Finished backup at 18-APR-19
Starting Control File and SPFILE Autobackup at 18-APR-19
piece handle=/home/oracle/backup/rman/c-986068470-20190418-05 comment=NONE
Finished Control File and SPFILE Autobackup at 18-APR-19
released channel: c1
released channel: c2
-- 이제 FRA에 백업해보자
RMAN> run {
allocate channel c1 type disk;
allocate channel c2 type disk;
backup incremental level 0 database;
} <- 수동 채널에서 경로를 지정하지 않으면 FRA에 backup file이 만들어짐
allocated channel: c1
channel c1: SID=133 device type=DISK
allocated channel: c2
channel c2: SID=21 device type=DISK
Starting backup at 18-APR-19
channel c1: starting incremental level 0 datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/orcl2/system01.dbf
input datafile file number=00005 name=/u01/app/oracle/orcl2/example01.dbf
channel c1: starting piece 1 at 18-APR-19
channel c2: starting incremental level 0 datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/orcl2/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/orcl2/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/orcl2/users01.dbf
channel c2: starting piece 1 at 18-APR-19
channel c2: finished piece 1 at 18-APR-19
piece handle=/u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_18/o1_mf_nnnd0_TAG20190418T144329_gcj3n16p_.bkp tag=TAG20190418T144329 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:19
channel c1: finished piece 1 at 18-APR-19
piece handle=/u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_18/o1_mf_nnnd0_TAG20190418T144329_gcj3n16h_.bkp tag=TAG20190418T144329 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:29
Finished backup at 18-APR-19
Starting Control File and SPFILE Autobackup at 18-APR-19
piece handle=/home/oracle/backup/rman/c-986068470-20190418-06 comment=NONE
Finished Control File and SPFILE Autobackup at 18-APR-19
released channel: c1
released channel: c2
RMAN> list backup;
List of Backup Sets
===================
..............................
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
14 Incr 0 656.40M DISK 00:00:19 18-APR-19
BP Key: 14 Status: AVAILABLE Compressed: NO Tag: TAG20190418T144329
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_18/o1_mf_nnnd0_TAG20190418T144329_gcj3n16h_.bkp
List of Datafiles in backup set 14
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 0 Incr 1200103 18-APR-19 /u01/app/oracle/orcl2/system01.dbf
5 0 Incr 1200103 18-APR-19 /u01/app/oracle/orcl2/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
15 Full 9.67M DISK 00:00:00 18-APR-19
BP Key: 15 Status: AVAILABLE Compressed: NO Tag: TAG20190418T144358
Piece Name: /home/oracle/backup/rman/c-986068470-20190418-06
SPFILE Included: Modification time: 18-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1200120 Ckp time: 18-APR-19
2. DBA SESSION 에서 db 작업
SYS@orcl2> @logfile
GROUP# SEQUENCE# MEMBER MB ARC STATUS FIRST_CHANGE#
---------- ---------- ----------------------------------- ---------- --- ---------- -------------
1 7 /u01/app/oracle/orcl2/redo01.log 50 YES INACTIVE 1178214
2 8 /u01/app/oracle/orcl2/redo02.log 50 NO CURRENT 1198639
3 6 /u01/app/oracle/orcl2/redo03.log 50 YES INACTIVE 1178210
SYS@orcl2> alter database enable block change tracking using file '/home/oracle/backup/rman/block_tracking.txt';
Database altered.
SYS@orcl2> @track
STATUS FILENAME MB
---------- -------------------------------------------------- ----------
ENABLED /home/oracle/backup/rman/block_tracking.txt 11.0625
SYS@orcl2> update hr.employees
set salary = salary * 1.1
where department_id = 20; 2 3
2 rows updated.
SYS@orcl2> commit;
Commit complete.
SYS@orcl2> create table hr.inc_emp as select * from hr.employees;
Table created.
SYS@orcl2> select count(*) from hr.inc_emp;
COUNT(*)
----------
107
SYS@orcl2> delete from hr.inc_emp where department_id = 50;
45 rows deleted.
SYS@orcl2> commit;
Commit complete.
SYS@orcl2> @logfile
GROUP# SEQUENCE# MEMBER MB ARC STATUS FIRST_CHANGE#
---------- ---------- ----------------------------------- ---------- --- ---------- -------------
1 7 /u01/app/oracle/orcl2/redo01.log 50 YES INACTIVE 1178214
2 8 /u01/app/oracle/orcl2/redo02.log 50 NO CURRENT 1198639
3 6 /u01/app/oracle/orcl2/redo03.log 50 YES INACTIVE 1178210
SYS@orcl2> @track
STATUS FILENAME MB
---------- -------------------------------------------------- ----------
ENABLED /home/oracle/backup/rman/block_tracking.txt 11.0625
3. RMAN SESSION 에서 incremental backup 수행
/* 참고용
RMAN> run {
allocate channel c1 type disk format '/home/oracle/backup/rman/incremental_1_%U_%T';
backup incremental level 1 database ;
}
*/
RMAN> run {
allocate channel c1 type disk;
backup incremental level 1 database;
}
allocated channel: c1
channel c1: SID=133 device type=DISK
Starting backup at 18-APR-19
channel c1: starting incremental level 1 datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/orcl2/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/orcl2/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/orcl2/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/orcl2/example01.dbf
input datafile file number=00004 name=/u01/app/oracle/orcl2/users01.dbf
channel c1: starting piece 1 at 18-APR-19
channel c1: finished piece 1 at 18-APR-19
piece handle=/u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_18/o1_mf_nnnd1_TAG20190418T145537_gcj4bsgo_.bkp tag=TAG20190418T145537 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:15
Finished backup at 18-APR-19
Starting Control File and SPFILE Autobackup at 18-APR-19
piece handle=/home/oracle/backup/rman/c-986068470-20190418-07 comment=NONE
Finished Control File and SPFILE Autobackup at 18-APR-19
released channel: c1
-- 빠르게 작업이 진행된다.
[orcl2:2019_04_18]$ pwd
/u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_18
[orcl2:2019_04_18]$ ls
o1_mf_nnnd0_TAG20190418T144329_gcj3n16h_.bkp o1_mf_nnnd1_TAG20190418T145537_gcj4bsgo_.bkp
o1_mf_nnnd0_TAG20190418T144329_gcj3n16p_.bkp
-- 백업파일이 있는 것을 확인
RMAN> list backup;
List of Backup Sets
===================
..............
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
15 Full 9.67M DISK 00:00:00 18-APR-19
BP Key: 15 Status: AVAILABLE Compressed: NO Tag: TAG20190418T144358
Piece Name: /home/oracle/backup/rman/c-986068470-20190418-06
SPFILE Included: Modification time: 18-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1200120 Ckp time: 18-APR-19
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
16 Incr 1 1.02M DISK 00:00:11 18-APR-19
BP Key: 16 Status: AVAILABLE Compressed: NO Tag: TAG20190418T145537
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_18/o1_mf_nnnd1_TAG20190418T145537_gcj4bsgo_.bkp
List of Datafiles in backup set 16
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 1 Incr 1200610 18-APR-19 /u01/app/oracle/orcl2/system01.dbf
2 1 Incr 1200610 18-APR-19 /u01/app/oracle/orcl2/sysaux01.dbf
3 1 Incr 1200610 18-APR-19 /u01/app/oracle/orcl2/undotbs01.dbf
4 1 Incr 1200610 18-APR-19 /u01/app/oracle/orcl2/users01.dbf
5 1 Incr 1200610 18-APR-19 /u01/app/oracle/orcl2/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
17 Full 9.67M DISK 00:00:00 18-APR-19
BP Key: 17 Status: AVAILABLE Compressed: NO Tag: TAG20190418T145552
Piece Name: /home/oracle/backup/rman/c-986068470-20190418-07
SPFILE Included: Modification time: 18-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1200622 Ckp time: 18-APR-19
4. DBA SESSION : 장애 발생 users
SYS@orcl2> @switch
System altered.
SYS@orcl2> /
System altered.
SYS@orcl2> /
System altered.
SYS@orcl2> /
System altered.
SYS@orcl2> select f.file_name from dba_extents e, dba_data_files f where e.file_id = f.file_id and e.segment_name = 'INC_EMP';
FILE_NAME
-----------------------------------------------------------------------------------------
/u01/app/oracle/orcl2/users01.dbf
SYS@orcl2> ! rm /u01/app/oracle/orcl2/users01.dbf
SYS@orcl2> @check
System altered.
SYS@orcl2> @logfile
GROUP# SEQUENCE# MEMBER MB ARC STATUS FIRST_CHANGE#
---------- ---------- ----------------------------------- ---------- --- ---------- -------------
1 10 /u01/app/oracle/orcl2/redo01.log 50 YES INACTIVE 1201108
2 11 /u01/app/oracle/orcl2/redo02.log 50 YES INACTIVE 1201113
3 12 /u01/app/oracle/orcl2/redo03.log 50 NO CURRENT 1201116
5. RMAN SESSION : 장애 내용 확인 후 복구\
RMAN> list failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
242 HIGH OPEN 16-APR-19 One or more non-system datafiles are offline
42 HIGH OPEN 11-APR-19 One or more non-system datafiles are missing
8 HIGH OPEN 10-APR-19 One or more non-system datafiles need media recovery
RMAN> list failure 42 detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
42 HIGH OPEN 11-APR-19 One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 42
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
165 HIGH OPEN 15-APR-19 Datafile 4: '/u01/app/oracle/orcl2/users01.dbf' is missing
Impact: Some objects in tablespace USERS might be unavailable
RMAN> sql 'alter tablespace users offline immediate';
sql statement: alter tablespace users offline immediate
RMAN> restore tablespace users;
Starting restore at 18-APR-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 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 00004 to /u01/app/oracle/orcl2/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_18/o1_mf_nnnd0_TAG20190418T144329_gcj3n16p_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_18/o1_mf_nnnd0_TAG20190418T144329_gcj3n16p_.bkp tag=TAG20190418T144329
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 18-APR-19
RMAN> recover tablespace users;
Starting recover at 18-APR-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: /u01/app/oracle/orcl2/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_18/o1_mf_nnnd1_TAG20190418T145537_gcj4bsgo_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_18/o1_mf_nnnd1_TAG20190418T145537_gcj4bsgo_.bkp tag=TAG20190418T145537
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
starting media recovery
archived log for thread 1 with sequence 8 is already on disk as file /home/oracle/arch1/arch_1_8_1005906168.arc
archived log for thread 1 with sequence 9 is already on disk as file /home/oracle/arch1/arch_1_9_1005906168.arc
archived log for thread 1 with sequence 10 is already on disk as file /home/oracle/arch1/arch_1_10_1005906168.arc
archived log for thread 1 with sequence 11 is already on disk as file /home/oracle/arch1/arch_1_11_1005906168.arc
archived log file name=/home/oracle/arch1/arch_1_8_1005906168.arc thread=1 sequence=8
archived log file name=/home/oracle/arch1/arch_1_9_1005906168.arc thread=1 sequence=9
media recovery complete, elapsed time: 00:00:00
Finished recover at 18-APR-19
RMAN> sql 'alter tablespace users online';
sql statement: alter tablespace users online
6. DBA SESSION
SYS@orcl2> select count(*) from hr.inc_emp;
COUNT(*)
----------
62
매우 큰 파일의 백업 및 복원 구성
- 지정한 크기 값으로 RMAN에 의해 생성
- 독립적으로(직렬 또는 병렬) 처리
- 여러 피스의 백업 셋 생성
- 백업 성능 향상
Multisection backup
/* 원래는 backup set의 maxsize는 파일의 크기보다 작게 설정할 수 없었다. */
/* 예를 들어 1TB짜리 파일을 백업할 때, 4개의 section(backup set)을 구성하여 백업을 나눠서 받을 수 있는 개념*/
/* configure에 설정할 수 있는 개념은 아니고 백업 시 옵션으로 넣을 수 있습니다.*/
/* 11gNF 부터 추가 */
-- 파일 하나가 클 경우 여러 backup set 을 생성해서 백업을 받을 수 있다.
RMAN> report schema;
Report of database schema for database with db_unique_name ORCL2
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 680 SYSTEM *** /u01/app/oracle/orcl2/system01.dbf
2 520 SYSAUX *** /u01/app/oracle/orcl2/sysaux01.dbf
3 105 UNDOTBS1 *** /u01/app/oracle/orcl2/undotbs01.dbf
4 5 USERS *** /u01/app/oracle/orcl2/users01.dbf
5 100 EXAMPLE *** /u01/app/oracle/orcl2/example01.dbf
/*filesize가 680M인 SYSTEM TBS에 대하여 100M짜리 backupset을 이용해 compressed backup을 받고자 한다.*/
/*원래는 안되지만, multisection 개념을 활용한다.*/
RMAN> backup as compressed backupset section size 100m format '/home/oracle/backup/rman/%U_%T' tablespace system;
Starting backup at 18-APR-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/orcl2/system01.dbf
backing up blocks 1 through 12800
channel ORA_DISK_1: starting piece 1 at 18-APR-19
channel ORA_DISK_1: finished piece 1 at 18-APR-19
piece handle=/home/oracle/backup/rman/0otvabth_1_1_20190418 tag=TAG20190418T152433 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/orcl2/system01.dbf
backing up blocks 12801 through 25600
channel ORA_DISK_1: starting piece 2 at 18-APR-19
channel ORA_DISK_1: finished piece 2 at 18-APR-19
piece handle=/home/oracle/backup/rman/0otvabth_2_1_20190418 tag=TAG20190418T152433 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/orcl2/system01.dbf
backing up blocks 25601 through 38400
channel ORA_DISK_1: starting piece 3 at 18-APR-19
channel ORA_DISK_1: finished piece 3 at 18-APR-19
piece handle=/home/oracle/backup/rman/0otvabth_3_1_20190418 tag=TAG20190418T152433 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/orcl2/system01.dbf
backing up blocks 38401 through 51200
channel ORA_DISK_1: starting piece 4 at 18-APR-19
channel ORA_DISK_1: finished piece 4 at 18-APR-19
piece handle=/home/oracle/backup/rman/0otvabth_4_1_20190418 tag=TAG20190418T152433 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/orcl2/system01.dbf
backing up blocks 51201 through 64000
channel ORA_DISK_1: starting piece 5 at 18-APR-19
channel ORA_DISK_1: finished piece 5 at 18-APR-19
piece handle=/home/oracle/backup/rman/0otvabth_5_1_20190418 tag=TAG20190418T152433 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/orcl2/system01.dbf
backing up blocks 64001 through 76800
channel ORA_DISK_1: starting piece 6 at 18-APR-19
channel ORA_DISK_1: finished piece 6 at 18-APR-19
piece handle=/home/oracle/backup/rman/0otvabth_6_1_20190418 tag=TAG20190418T152433 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/orcl2/system01.dbf
backing up blocks 76801 through 87040
channel ORA_DISK_1: starting piece 7 at 18-APR-19
channel ORA_DISK_1: finished piece 7 at 18-APR-19
piece handle=/home/oracle/backup/rman/0otvabth_7_1_20190418 tag=TAG20190418T152433 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 18-APR-19
Starting Control File and SPFILE Autobackup at 18-APR-19
piece handle=/home/oracle/backup/rman/c-986068470-20190418-09 comment=NONE
Finished Control File and SPFILE Autobackup at 18-APR-19
/*BS# 19를 보면 system.dbf가 100M짜리 backupset 7개로 분할되어 구성되어있음을 확인할 수 있음.*/
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
19 Full 182.59M DISK 00:00:28 18-APR-19
List of Datafiles in backup set 19
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1202542 18-APR-19 /u01/app/oracle/orcl2/system01.dbf
Backup Set Copy #1 of backup set 19
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:28 18-APR-19 YES TAG20190418T152433
List of Backup Pieces for backup set 19 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
19 1 AVAILABLE /home/oracle/backup/rman/0otvabth_1_1_20190418
20 2 AVAILABLE /home/oracle/backup/rman/0otvabth_2_1_20190418
21 3 AVAILABLE /home/oracle/backup/rman/0otvabth_3_1_20190418
22 4 AVAILABLE /home/oracle/backup/rman/0otvabth_4_1_20190418
23 5 AVAILABLE /home/oracle/backup/rman/0otvabth_5_1_20190418
24 6 AVAILABLE /home/oracle/backup/rman/0otvabth_6_1_20190418
25 7 AVAILABLE /home/oracle/backup/rman/0otvabth_7_1_20190418
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
20 Full 9.67M DISK 00:00:00 18-APR-19
BP Key: 26 Status: AVAILABLE Compressed: NO Tag: TAG20190418T152502
Piece Name: /home/oracle/backup/rman/c-986068470-20190418-09
SPFILE Included: Modification time: 18-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1202558 Ckp time: 18-APR-19
CROSSCHECK backupset
- 폐기되어야하는 백업은 EXPIRED로 표시한다.
- OPSOLETE : 더이상 필요하지 않은 백업
CORSSCHECK, DELETE EXPIRED, DELETE OBSOLETE
현재 control file이 가진 backup 정보와
실제로 backup된 file간의 정보가 일치하는지 비교 확인.
비교해봐서 만기된( = 정책에서 보증하는 기간이 지난) backupset은 지워주어야합니다.
이게 다 저장공간 관리
RMAN> report schema;
Report of database schema for database with db_unique_name ORCL2
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 680 SYSTEM *** /u01/app/oracle/orcl2/system01.dbf
2 520 SYSAUX *** /u01/app/oracle/orcl2/sysaux01.dbf
3 105 UNDOTBS1 *** /u01/app/oracle/orcl2/undotbs01.dbf
4 5 USERS *** /u01/app/oracle/orcl2/users01.dbf
5 100 EXAMPLE *** /u01/app/oracle/orcl2/example01.dbf
RMAN> backup datafile 4;
Starting backup at 18-APR-19
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=00004 name=/u01/app/oracle/orcl2/users01.dbf
channel ORA_DISK_1: starting piece 1 at 18-APR-19
channel ORA_DISK_1: finished piece 1 at 18-APR-19
piece handle=/home/oracle/backup/rman/10tvadl2_1_1_20190418 tag=TAG20190418T155410 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 18-APR-19
Starting Control File and SPFILE Autobackup at 18-APR-19
piece handle=/home/oracle/backup/rman/c-986068470-20190418-0a comment=NONE
Finished Control File and SPFILE Autobackup at 18-APR-19
RMAN> list backup of tablespace users;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
11 Incr 0 398.30M DISK 00:00:15 18-APR-19
BP Key: 11 Status: AVAILABLE Compressed: NO Tag: TAG20190418T144231
Piece Name: /home/oracle/backup/rman/0gtva9en_1_1_20190418
List of Datafiles in backup set 11
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 0 Incr 1200047 18-APR-19 /u01/app/oracle/orcl2/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
13 Incr 0 398.30M DISK 00:00:12 18-APR-19
BP Key: 13 Status: AVAILABLE Compressed: NO Tag: TAG20190418T144329
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_18/o1_mf_nnnd0_TAG20190418T144329_gcj3n16p_.bkp
List of Datafiles in backup set 13
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 0 Incr 1200104 18-APR-19 /u01/app/oracle/orcl2/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
16 Incr 1 1.02M DISK 00:00:11 18-APR-19
BP Key: 16 Status: AVAILABLE Compressed: NO Tag: TAG20190418T145537
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_18/o1_mf_nnnd1_TAG20190418T145537_gcj4bsgo_.bkp
List of Datafiles in backup set 16
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 1 Incr 1200610 18-APR-19 /u01/app/oracle/orcl2/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
21 Full 3.89M DISK 00:00:00 18-APR-19
BP Key: 27 Status: AVAILABLE Compressed: NO Tag: TAG20190418T155410
Piece Name: /home/oracle/backup/rman/10tvadl2_1_1_20190418
List of Datafiles in backup set 21
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 Full 1203749 18-APR-19 /u01/app/oracle/orcl2/users01.dbf
-- backup file delete --
[orcl2:~]$ rm /home/oracle/backup/rman/0gtva9en_1_1_20190418 -- 가장 오래된 것
RMAN> crosscheck backupset;
using channel ORA_DISK_1
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/backup/rman/0ftva9en_1_1_20190418 RECID=10 STAMP=1005921751
crosschecked backup piece: found to be 'EXPIRED' -OS상에는 없지만, control file에는 정보가 남아있다.
backup piece handle=/home/oracle/backup/rman/0gtva9en_1_1_20190418 RECID=11 STAMP=1005921752
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/backup/rman/c-986068470-20190418-05 RECID=12 STAMP=1005921766
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_18/o1_mf_nnnd0_TAG20190418T144329_gcj3n16p_.bkp RECID=13 STAMP=1005921809
....................
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/backup/rman/10tvadl2_1_1_20190418 RECID=27 STAMP=1005926050
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/backup/rman/c-986068470-20190418-0a RECID=28 STAMP=1005926051
Crosschecked 19 objects
RMAN> list expired backupset;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
11 Incr 0 398.30M DISK 00:00:15 18-APR-19
BP Key: 11 Status: EXPIRED Compressed: NO Tag: TAG20190418T144231
Piece Name: /home/oracle/backup/rman/0gtva9en_1_1_20190418
List of Datafiles in backup set 11
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 0 Incr 1200047 18-APR-19 /u01/app/oracle/orcl2/sysaux01.dbf
3 0 Incr 1200047 18-APR-19 /u01/app/oracle/orcl2/undotbs01.dbf
4 0 Incr 1200047 18-APR-19 /u01/app/oracle/orcl2/users01.dbf
/*만기된 backupset을 지웁니다.*/
RMAN> delete expired backupset;
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
11 11 1 1 EXPIRED DISK /home/oracle/backup/rman/0gtva9en_1_1_20190418
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/home/oracle/backup/rman/0gtva9en_1_1_20190418 RECID=11 STAMP=1005921752
Deleted 1 EXPIRED objects
RMAN> list expired backupset;
specification does not match any backup in the repository
<<DELETE backupset>>
RMAN> list backup;
List of Backup Sets
===================
.................
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
18 Full 9.67M DISK 00:00:00 18-APR-19
BP Key: 18 Status: AVAILABLE Compressed: NO Tag: TAG20190418T151553
Piece Name: /home/oracle/backup/rman/c-986068470-20190418-08
SPFILE Included: Modification time: 18-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1202208 Ckp time: 18-APR-19
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
19 Full 182.59M DISK 00:00:28 18-APR-19
List of Datafiles in backup set 19
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1202542 18-APR-19 /u01/app/oracle/orcl2/system01.dbf
Backup Set Copy #1 of backup set 19
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:28 18-APR-19 YES TAG20190418T152433
List of Backup Pieces for backup set 19 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
19 1 AVAILABLE /home/oracle/backup/rman/0otvabth_1_1_20190418
20 2 AVAILABLE /home/oracle/backup/rman/0otvabth_2_1_20190418
21 3 AVAILABLE /home/oracle/backup/rman/0otvabth_3_1_20190418
22 4 AVAILABLE /home/oracle/backup/rman/0otvabth_4_1_20190418
23 5 AVAILABLE /home/oracle/backup/rman/0otvabth_5_1_20190418
24 6 AVAILABLE /home/oracle/backup/rman/0otvabth_6_1_20190418
25 7 AVAILABLE /home/oracle/backup/rman/0otvabth_7_1_20190418
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
20 Full 9.67M DISK 00:00:00 18-APR-19
BP Key: 26 Status: AVAILABLE Compressed: NO Tag: TAG20190418T152502
Piece Name: /home/oracle/backup/rman/c-986068470-20190418-09
SPFILE Included: Modification time: 18-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1202558 Ckp time: 18-APR-19
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
21 Full 3.89M DISK 00:00:00 18-APR-19
BP Key: 27 Status: AVAILABLE Compressed: NO Tag: TAG20190418T155410
Piece Name: /home/oracle/backup/rman/10tvadl2_1_1_20190418
List of Datafiles in backup set 21
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 Full 1203749 18-APR-19 /u01/app/oracle/orcl2/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
22 Full 9.67M DISK 00:00:00 18-APR-19
BP Key: 28 Status: AVAILABLE Compressed: NO Tag: TAG20190418T155411
Piece Name: /home/oracle/backup/rman/c-986068470-20190418-0a
SPFILE Included: Modification time: 18-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1203756 Ckp time: 18-APR-19
RMAN> delete backupset 19;
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
19 19 1 1 AVAILABLE DISK /home/oracle/backup/rman/0otvabth_1_1_20190418
20 19 2 1 AVAILABLE DISK /home/oracle/backup/rman/0otvabth_2_1_20190418
21 19 3 1 AVAILABLE DISK /home/oracle/backup/rman/0otvabth_3_1_20190418
22 19 4 1 AVAILABLE DISK /home/oracle/backup/rman/0otvabth_4_1_20190418
23 19 5 1 AVAILABLE DISK /home/oracle/backup/rman/0otvabth_5_1_20190418
24 19 6 1 AVAILABLE DISK /home/oracle/backup/rman/0otvabth_6_1_20190418
25 19 7 1 AVAILABLE DISK /home/oracle/backup/rman/0otvabth_7_1_20190418
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/home/oracle/backup/rman/0otvabth_1_1_20190418 RECID=19 STAMP=1005924273
deleted backup piece
backup piece handle=/home/oracle/backup/rman/0otvabth_2_1_20190418 RECID=20 STAMP=1005924276
deleted backup piece
backup piece handle=/home/oracle/backup/rman/0otvabth_3_1_20190418 RECID=21 STAMP=1005924279
deleted backup piece
backup piece handle=/home/oracle/backup/rman/0otvabth_4_1_20190418 RECID=22 STAMP=1005924286
deleted backup piece
backup piece handle=/home/oracle/backup/rman/0otvabth_5_1_20190418 RECID=23 STAMP=1005924293
deleted backup piece
backup piece handle=/home/oracle/backup/rman/0otvabth_6_1_20190418 RECID=24 STAMP=1005924296
deleted backup piece
backup piece handle=/home/oracle/backup/rman/0otvabth_7_1_20190418 RECID=25 STAMP=1005924299
Deleted 7 objects
RMAN> list backupset;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
18 Full 9.67M DISK 00:00:00 18-APR-19
BP Key: 18 Status: AVAILABLE Compressed: NO Tag: TAG20190418T151553
Piece Name: /home/oracle/backup/rman/c-986068470-20190418-08
SPFILE Included: Modification time: 18-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1202208 Ckp time: 18-APR-19
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
20 Full 9.67M DISK 00:00:00 18-APR-19
BP Key: 26 Status: AVAILABLE Compressed: NO Tag: TAG20190418T152502
Piece Name: /home/oracle/backup/rman/c-986068470-20190418-09
SPFILE Included: Modification time: 18-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1202558 Ckp time: 18-APR-19
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
21 Full 3.89M DISK 00:00:00 18-APR-19
BP Key: 27 Status: AVAILABLE Compressed: NO Tag: TAG20190418T155410
Piece Name: /home/oracle/backup/rman/10tvadl2_1_1_20190418
List of Datafiles in backup set 21
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 Full 1203749 18-APR-19 /u01/app/oracle/orcl2/users01.dbf
-- 백업이 삭제된 것을 확인
/*backupset 전체를 다 지웁니다. 괜히 all 키워드 넣을 필요없고 넣어도 에러뜸*/
RMAN> delete backupset;
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
10 10 1 1 AVAILABLE DISK /home/oracle/backup/rman/0ftva9en_1_1_20190418
12 12 1 1 AVAILABLE DISK /home/oracle/backup/rman/c-986068470-20190418-05
13 13 1 1 AVAILABLE DISK /u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_18/o1_mf_nnnd0_TAG20190418T144329_gcj3n16p_.bkp
14 14 1 1 AVAILABLE DISK /u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_18/o1_mf_nnnd0_TAG20190418T144329_gcj3n16h_.bkp
15 15 1 1 AVAILABLE DISK /home/oracle/backup/rman/c-986068470-20190418-06
16 16 1 1 AVAILABLE DISK /u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_18/o1_mf_nnnd1_TAG20190418T145537_gcj4bsgo_.bkp
17 17 1 1 AVAILABLE DISK /home/oracle/backup/rman/c-986068470-20190418-07
18 18 1 1 AVAILABLE DISK /home/oracle/backup/rman/c-986068470-20190418-08
26 20 1 1 AVAILABLE DISK /home/oracle/backup/rman/c-986068470-20190418-09
27 21 1 1 AVAILABLE DISK /home/oracle/backup/rman/10tvadl2_1_1_20190418
28 22 1 1 AVAILABLE DISK /home/oracle/backup/rman/c-986068470-20190418-0a
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/home/oracle/backup/rman/0ftva9en_1_1_20190418 RECID=10 STAMP=1005921751
deleted backup piece
backup piece handle=/home/oracle/backup/rman/c-986068470-20190418-05 RECID=12 STAMP=1005921766
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_18/o1_mf_nnnd0_TAG20190418T144329_gcj3n16p_.bkp RECID=13 STAMP=1005921809
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_18/o1_mf_nnnd0_TAG20190418T144329_gcj3n16h_.bkp RECID=14 STAMP=1005921809
deleted backup piece
backup piece handle=/home/oracle/backup/rman/c-986068470-20190418-06 RECID=15 STAMP=1005921838
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_18/o1_mf_nnnd1_TAG20190418T145537_gcj4bsgo_.bkp RECID=16 STAMP=1005922537
deleted backup piece
backup piece handle=/home/oracle/backup/rman/c-986068470-20190418-07 RECID=17 STAMP=1005922552
deleted backup piece
backup piece handle=/home/oracle/backup/rman/c-986068470-20190418-08 RECID=18 STAMP=1005923753
deleted backup piece
backup piece handle=/home/oracle/backup/rman/c-986068470-20190418-09 RECID=26 STAMP=1005924302
deleted backup piece
backup piece handle=/home/oracle/backup/rman/10tvadl2_1_1_20190418 RECID=27 STAMP=1005926050
deleted backup piece
backup piece handle=/home/oracle/backup/rman/c-986068470-20190418-0a RECID=28 STAMP=1005926051
Deleted 11 objects
recovery advisor : system 테이블스페이스 장애후 advisor 이용한 복구
RMAN> list backup;
specification does not match any backup in the repository
RMAN> backup database;
Starting backup at 18-APR-19
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=/u01/app/oracle/orcl2/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/orcl2/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/orcl2/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/orcl2/example01.dbf
input datafile file number=00004 name=/u01/app/oracle/orcl2/users01.dbf
channel ORA_DISK_1: starting piece 1 at 18-APR-19
channel ORA_DISK_1: finished piece 1 at 18-APR-19
piece handle=/home/oracle/backup/rman/12tvaeer_1_1_20190418 tag=TAG20190418T160755 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 18-APR-19
Starting Control File and SPFILE Autobackup at 18-APR-19
piece handle=/home/oracle/backup/rman/c-986068470-20190418-0b comment=NONE
Finished Control File and SPFILE Autobackup at 18-APR-19
[orcl2:~]$ rm /u01/app/oracle/orcl2/system01.dbf
SYS@orcl2> conn hr/hr
ERROR:
ORA-00604: error occurred at recursive SQL level 2
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/orcl2/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
@> conn / as sysdba
Connected.
SYS@orcl2> shutdown abort
ORACLE instance shut down.
RMAN> exit
Recovery Manager complete.
[orcl2:~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Apr 18 16:11:06 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup mount
Oracle instance started
database mounted
Total System Global Area 431038464 bytes
Fixed Size 1337016 bytes
Variable Size 150997320 bytes
Database Buffers 272629760 bytes
Redo Buffers 6074368 bytes
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
2442 CRITICAL OPEN 18-APR-19 System datafile 1: '/u01/app/oracle/orcl2/system01.dbf' is missing
2168 HIGH OPEN 18-APR-19 Tablespace 4: 'USERS' is offline
242 HIGH OPEN 16-APR-19 One or more non-system datafiles are offline
RMAN> list failure 2442 detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
2442 CRITICAL OPEN 18-APR-19 System datafile 1: '/u01/app/oracle/orcl2/system01.dbf' is missing
Impact: Database cannot be opened
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
2442 CRITICAL OPEN 18-APR-19 System datafile 1: '/u01/app/oracle/orcl2/system01.dbf' is missing
Impact: Database cannot be opened
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file /u01/app/oracle/orcl2/system01.dbf was unintentionally renamed or moved, restore it
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 1
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl2/orcl2/hm/reco_3536619478.hm
-- 어떻게 action을 해야할지 알려준다
RMAN> repair failure preview;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl2/orcl2/hm/reco_3536619478.hm
contents of repair script:
# restore and recover datafile
restore datafile 1;
recover datafile 1;
RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl2/orcl2/hm/reco_3536619478.hm
contents of repair script:
# restore and recover datafile
restore datafile 1;
recover datafile 1;
Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script
Starting restore at 18-APR-19
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 /u01/app/oracle/orcl2/system01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/rman/12tvaeer_1_1_20190418
channel ORA_DISK_1: piece handle=/home/oracle/backup/rman/12tvaeer_1_1_20190418 tag=TAG20190418T160755
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 18-APR-19
Starting recover at 18-APR-19
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 18-APR-19
repair failure complete
RMAN> alter database open;
database opened
-- 복구완료
<<<<복습>>>>
1. 현재 DB의 리두로그 파일의 그룹 번호, 시퀀스번호,리두로그 파일,그룹별 맴버 개수, 크기는 mbyte, 상태 정보를 확인 하는 쿼리문장을 만드세요.
SYS@orcl2> select a.group#, b.sequence#, a.member, b.members, b.bytes/1024/1024 mbyte, b.status
from v$logfile a, v$log b
where a.group# = b.group#
order by 1;
GROUP# SEQUENCE# MEMBER MEMBERS MBYTE STATUS
---------- ---------- ----------------------------------- ---------- ---------- ----------
1 13 /u01/app/oracle/orcl2/redo01.log 1 50 INACTIVE
2 14 /u01/app/oracle/orcl2/redo02.log 1 50 CURRENT
3 12 /u01/app/oracle/orcl2/redo03.log 1 50 INACTIVE
2. 로그 스위치의 주기를 확인 하세요.
SYS@orcl2> select group#, sequence#, first_time
from v$log;
GROUP# SEQUENCE# FIRST_TIME
---------- ---------- -------------------
1 16 2019-04-19 09:55:38
2 17 2019-04-19 09:55:41
3 15 2019-04-19 09:55:36
SYS@orcl2> select * from v$loghist;
THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIME SWITCH_CHANGE#
---------- ---------- ------------- ------------------- --------------
1 11 1115403 2019-04-17 15:21:38 1135636
1 12 1135636 2019-04-17 15:35:49 1137667
1 13 1137667 2019-04-17 16:01:47 1137670
1 14 1137670 2019-04-17 16:01:50 1137675
1 15 1137675 2019-04-17 16:01:55 1137754
1 1 1137755 2019-04-17 16:11:29 1138711
1 2 1138711 2019-04-17 16:14:51 1138714
1 3 1138714 2019-04-17 16:14:53 1138718
1 4 1138718 2019-04-17 16:14:56 1138721
1 5 1138721 2019-04-17 16:14:58 1138769
1 6 1138769 2019-04-17 16:15:44 1138772
1 7 1138772 2019-04-17 16:15:46 1138777
1 1 1138778 2019-04-17 16:21:34 1164171
1 2 1164171 2019-04-18 09:18:23 1166836
1 3 1166836 2019-04-18 09:55:54 1166839
1 4 1166839 2019-04-18 09:55:56 1166843
1 5 1166843 2019-04-18 09:55:57 1166851
1 6 1166851 2019-04-18 09:56:07 1166856
1 7 1166856 2019-04-18 09:56:14 1166889
1 8 1166889 2019-04-18 09:56:34 1166892
1 9 1166892 2019-04-18 09:56:37 1166901
1 10 1166901 2019-04-18 09:56:40 1167985
1 1 1167986 2019-04-18 10:22:48 1172915
1 2 1172915 2019-04-18 11:56:10 1172918
1 3 1172918 2019-04-18 11:56:12 1172923
1 4 1172923 2019-04-18 11:56:15 1178207
1 5 1178207 2019-04-18 14:02:06 1178210
1 6 1178210 2019-04-18 14:02:07 1178214
1 7 1178214 2019-04-18 14:02:09 1198639
1 8 1198639 2019-04-18 14:17:56 1201105
1 9 1201105 2019-04-18 15:04:18 1201108
1 10 1201108 2019-04-18 15:04:19 1201113
1 11 1201113 2019-04-18 15:04:22 1201116
1 12 1201116 2019-04-18 15:04:23 1225076
1 13 1225076 2019-04-18 16:14:43 1250348
1 14 1250348 2019-04-19 09:22:12 1252531
1 15 1252531 2019-04-19 09:55:36 1252535
1 16 1252535 2019-04-19 09:55:38 1252541
38 rows selected.
3. 강제로 로그스위치를 3번 수행하세요. 리두로그 파일 정보 확인하세요.
SYS@orcl2> alter system switch logfile;
System altered.
SYS@orcl2> /
System altered.
SYS@orcl2> /
System altered.
SYS@orcl2> select a.group#, b.sequence#, a.member, b.members, b.bytes/1024/1024 mbyte, b.status
from v$logfile a, v$log b
where a.group# = b.group#
order by 1;
GROUP# SEQUENCE# MEMBER MEMBERS MBYTE STATUS
---------- ---------- ----------------------------------- ---------- ---------- ----------
1 16 /u01/app/oracle/orcl2/redo01.log 1 50 INACTIVE
2 17 /u01/app/oracle/orcl2/redo02.log 1 50 CURRENT
3 15 /u01/app/oracle/orcl2/redo03.log 1 50 INACTIVE
4. 현재 DB에 체크포인트 정보, data file의 체크포인트 정보 확인 하세요.
SYS@orcl2> select DBID, NAME, CHECKPOINT_CHANGE#, RESETLOGS_CHANGE#, RESETLOGS_TIME
from v$database;
DBID NAME CHECKPOINT_CHANGE# RESETLOGS_CHANGE# RESETLOGS
---------- ----------------------------------- ------------------ ----------------- ---------
986068470 ORCL2 1252541 1167986 18-APR-19
SYS@orcl2> select file#, name, checkpoint_change#, checkpoint_time
from v$datafile;
FILE# NAME CHECKPOINT_CHANGE# CHECKPOINT_TIME
---------- ----------------------------------- ------------------ -------------------
1 /u01/app/oracle/orcl2/system01.dbf 1253036 2019-04-19 10:05:37
2 /u01/app/oracle/orcl2/sysaux01.dbf 1253036 2019-04-19 10:05:37
3 /u01/app/oracle/orcl2/undotbs01.dbf 1253036 2019-04-19 10:05:37
4 /u01/app/oracle/orcl2/users01.dbf 1253420 2019-04-19 10:08:39
5 /u01/app/oracle/orcl2/example01.dbf 1253036 2019-04-19 10:05:37
5. 전체 DB의 cold backup을 수행하세요.
vi db_list.sql
set echo off
set pages 0
set head off
set feed off
create pfile from spfile;
spool orcl2_backup.sh
select 'cp -av ' || name||' /home/oracle/backup/arch/cold'
from v$controlfile
union all
select 'cp -av ' || name||' /home/oracle/backup/arch/cold'
from v$datafile
union all
select 'cp -av ' || member ||' /home/oracle/backup/arch/cold'
from v$logfile;
select 'cp -av $ORACLE_HOME/dbs/initorcl2.ora /home/oracle/backup/arch/cold' from dual;
spool off
set feedback on
set head on
set pages 100
set echo on
SYS@orcl2> @db_list
cp -av /u01/app/oracle/orcl2/control01.ctl /home/oracle/backup/arch/cold
cp -av /u01/app/oracle/orcl2/system01.dbf /home/oracle/backup/arch/cold
cp -av /u01/app/oracle/orcl2/sysaux01.dbf /home/oracle/backup/arch/cold
cp -av /u01/app/oracle/orcl2/undotbs01.dbf /home/oracle/backup/arch/cold
cp -av /u01/app/oracle/orcl2/users01.dbf /home/oracle/backup/arch/cold
cp -av /u01/app/oracle/orcl2/example01.dbf /home/oracle/backup/arch/cold
cp -av /u01/app/oracle/orcl2/redo02.log /home/oracle/backup/arch/cold
cp -av /u01/app/oracle/orcl2/redo01.log /home/oracle/backup/arch/cold
cp -av /u01/app/oracle/orcl2/redo03.log /home/oracle/backup/arch/cold
cp -av $ORACLE_HOME/dbs/initorcl2.ora /home/oracle/backup/arch/cold
SYS@orcl2> shutdown immediate
[orcl2:~]$ . orcl2_backup.sh
`/u01/app/oracle/orcl2/control01.ctl' -> `/home/oracle/backup/arch/cold/control01.ctl'
`/u01/app/oracle/orcl2/system01.dbf' -> `/home/oracle/backup/arch/cold/system01.dbf'
`/u01/app/oracle/orcl2/sysaux01.dbf' -> `/home/oracle/backup/arch/cold/sysaux01.dbf'
`/u01/app/oracle/orcl2/undotbs01.dbf' -> `/home/oracle/backup/arch/cold/undotbs01.dbf'
`/u01/app/oracle/orcl2/users01.dbf' -> `/home/oracle/backup/arch/cold/users01.dbf'
`/u01/app/oracle/orcl2/example01.dbf' -> `/home/oracle/backup/arch/cold/example01.dbf'
`/u01/app/oracle/orcl2/redo02.log' -> `/home/oracle/backup/arch/cold/redo02.log'
`/u01/app/oracle/orcl2/redo01.log' -> `/home/oracle/backup/arch/cold/redo01.log'
`/u01/app/oracle/orcl2/redo03.log' -> `/home/oracle/backup/arch/cold/redo03.log'
`/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl2.ora' -> `/home/oracle/backup/arch/cold/initorcl2.ora'
6. users 테이블스페이스의 hot backup을 수행하세요.
SYS@orcl2> select a.file#, a.name,a.checkpoint_change#, b.status, b.change#, b.time
from v$datafile a, v$backup b
where a.file#=b.file#;
FILE# NAME CHECKPOINT_CHANGE# STATUS CHANGE# TIME
---------- ------------------------------ ------------------ --------------- ---------- ---------
1 /u01/app/oracle/orcl2/system01 1253036 NOT ACTIVE 916672 15-APR-19
.dbf
2 /u01/app/oracle/orcl2/sysaux01 1253036 NOT ACTIVE 916672 15-APR-19
.dbf
3 /u01/app/oracle/orcl2/undotbs0 1253036 NOT ACTIVE 916672 15-APR-19
1.dbf
4 /u01/app/oracle/orcl2/users01. 1253036 NOT ACTIVE 916672 15-APR-19
dbf
5 /u01/app/oracle/orcl2/example0 1253036 NOT ACTIVE 916672 15-APR-19
1.dbf
SYS@orcl2> alter tablespace users begin backup;
Tablespace altered.
SYS@orcl2> select a.file#, a.name,a.checkpoint_change#, b.status, b.change#, b.time
from v$datafile a, v$backup b
where a.file#=b.file#;
FILE# NAME CHECKPOINT_CHANGE# STATUS CHANGE# TIME
---------- ------------------------------ ------------------ --------------- ---------- ---------
1 /u01/app/oracle/orcl2/system01 1253036 NOT ACTIVE 916672 15-APR-19
.dbf
2 /u01/app/oracle/orcl2/sysaux01 1253036 NOT ACTIVE 916672 15-APR-19
.dbf
3 /u01/app/oracle/orcl2/undotbs0 1253036 NOT ACTIVE 916672 15-APR-19
1.dbf
4 /u01/app/oracle/orcl2/users01. 1253420 ACTIVE 1253420 19-APR-19
dbf
5 /u01/app/oracle/orcl2/example0 1253036 NOT ACTIVE 916672 15-APR-19
1.dbf
SYS@orcl2> ! cp -av /u01/app/oracle/orcl2 /home/oracle/backup/arch/hot/
`/u01/app/oracle/orcl2/db_list2.sql' -> `/home/oracle/backup/arch/hot/orcl2/db_list2.sql'
`/u01/app/oracle/orcl2/redo01.log' -> `/home/oracle/backup/arch/hot/orcl2/redo01.log'
`/u01/app/oracle/orcl2/redo03.log' -> `/home/oracle/backup/arch/hot/orcl2/redo03.log'
`/u01/app/oracle/orcl2/example01.dbf' -> `/home/oracle/backup/arch/hot/orcl2/example01.dbf'
`/u01/app/oracle/orcl2/afiedt.buf' -> `/home/oracle/backup/arch/hot/orcl2/afiedt.buf'
`/u01/app/oracle/orcl2/system01.dbf' -> `/home/oracle/backup/arch/hot/orcl2/system01.dbf'
`/u01/app/oracle/orcl2/sysaux01.dbf' -> `/home/oracle/backup/arch/hot/orcl2/sysaux01.dbf'
`/u01/app/oracle/orcl2/users01.dbf' -> `/home/oracle/backup/arch/hot/orcl2/users01.dbf'
`/u01/app/oracle/orcl2/undotbs01.dbf' -> `/home/oracle/backup/arch/hot/orcl2/undotbs01.dbf'
`/u01/app/oracle/orcl2/db_noarch_list.sql' -> `/home/oracle/backup/arch/hot/orcl2/db_noarch_list.sql'
`/u01/app/oracle/orcl2/redo02.log' -> `/home/oracle/backup/arch/hot/orcl2/redo02.log'
`/u01/app/oracle/orcl2/control01.ctl' -> `/home/oracle/backup/arch/hot/orcl2/control01.ctl'
SYS@orcl2> alter tablespace users end backup;
Tablespace altered.
SYS@orcl2> select a.file#, a.name,a.checkpoint_change#, b.status, b.change#, b.time
from v$datafile a, v$backup b
where a.file#=b.file#;
FILE# NAME CHECKPOINT_CHANGE# STATUS CHANGE# TIME
---------- ------------------------------ ------------------ --------------- ---------- ---------
1 /u01/app/oracle/orcl2/system01 1253036 NOT ACTIVE 916672 15-APR-19
.dbf
2 /u01/app/oracle/orcl2/sysaux01 1253036 NOT ACTIVE 916672 15-APR-19
.dbf
3 /u01/app/oracle/orcl2/undotbs0 1253036 NOT ACTIVE 916672 15-APR-19
1.dbf
4 /u01/app/oracle/orcl2/users01. 1253420 NOT ACTIVE 1253420 19-APR-19
dbf
5 /u01/app/oracle/orcl2/example0 1253036 NOT ACTIVE 916672 15-APR-19
1.dbf
7. RMAN 접속해서 현재 백업을 확인하세요. (backupset과 image copy의 목록)
[orcl2:~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Apr 19 10:11:50 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL2 (DBID=986068470)
RMAN> list backupset;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
23 Full 1.03G DISK 00:00:15 18-APR-19
BP Key: 29 Status: AVAILABLE Compressed: NO Tag: TAG20190418T160755
Piece Name: /home/oracle/backup/rman/12tvaeer_1_1_20190418
List of Datafiles in backup set 23
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1204944 18-APR-19 /u01/app/oracle/orcl2/system01.dbf
2 Full 1204944 18-APR-19 /u01/app/oracle/orcl2/sysaux01.dbf
3 Full 1204944 18-APR-19 /u01/app/oracle/orcl2/undotbs01.dbf
4 Full 1204944 18-APR-19 /u01/app/oracle/orcl2/users01.dbf
5 Full 1204944 18-APR-19 /u01/app/oracle/orcl2/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
24 Full 9.67M DISK 00:00:00 18-APR-19
BP Key: 30 Status: AVAILABLE Compressed: NO Tag: TAG20190418T160810
Piece Name: /home/oracle/backup/rman/c-986068470-20190418-0b
SPFILE Included: Modification time: 18-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1204957 Ckp time: 18-APR-19
RMAN> LIST DATAFILECOPY ALL;
specification does not match any datafile copy in the repository
8. 현재 DB의 구조를 확인하세요( 테이블스페이스와 데이타 파일 정보)
SYS@orcl2> select a.file#,b.name name1,a.name name2,a.status,a.checkpoint_change#
from v$datafile a, v$tablespace b
where a.ts#=b.ts#;
FILE# NAME1 NAME2 STATUS CHECKPOINT_CHANGE#
---------- --------------- ----------------------------------- ------- ------------------
1 SYSTEM /u01/app/oracle/orcl2/system01.dbf SYSTEM 1253036
2 SYSAUX /u01/app/oracle/orcl2/sysaux01.dbf ONLINE 1253036
3 UNDOTBS1 /u01/app/oracle/orcl2/undotbs01.dbf ONLINE 1253036
4 USERS /u01/app/oracle/orcl2/users01.dbf ONLINE 1253420
5 EXAMPLE /u01/app/oracle/orcl2/example01.dbf ONLINE 1253036
9. 현재 backupset 백업과 image copy 백업을 모두 삭제하세요.
[orcl2:~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Apr 19 10:21:13 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL2 (DBID=986068470)
RMAN> delete datafilecopy all;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
specification does not match any datafile copy in the repository
RMAN> delete backupset;
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
29 23 1 1 AVAILABLE DISK /home/oracle/backup/rman/12tvaeer_1_1_20190418
30 24 1 1 AVAILABLE DISK /home/oracle/backup/rman/c-986068470-20190418-0b
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/home/oracle/backup/rman/12tvaeer_1_1_20190418 RECID=29 STAMP=1005926875
deleted backup piece
backup piece handle=/home/oracle/backup/rman/c-986068470-20190418-0b RECID=30 STAMP=1005926890
Deleted 2 objects
RMAN> LIST DATAFILECOPY ALL;
specification does not match any datafile copy in the repository
RMAN> LIST BACKUPSET;
specification does not match any backup in the repository
10. RMAN 접속해서 전체 database 를 backupset으로 compress 해서 backup하고 백업된 정보를 확인하세요.
RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE;
Starting backup at 19-APR-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/orcl2/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/orcl2/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/orcl2/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/orcl2/example01.dbf
input datafile file number=00004 name=/u01/app/oracle/orcl2/users01.dbf
channel ORA_DISK_1: starting piece 1 at 19-APR-19
channel ORA_DISK_1: finished piece 1 at 19-APR-19
piece handle=/home/oracle/backup/rman/14tvcek1_1_1_20190419 tag=TAG20190419T102256 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 19-APR-19
Starting Control File and SPFILE Autobackup at 19-APR-19
piece handle=/home/oracle/backup/rman/c-986068470-20190419-00 comment=NONE
Finished Control File and SPFILE Autobackup at 19-APR-19
RMAN> LIST BACKUPSET;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25 Full 278.04M DISK 00:00:39 19-APR-19
BP Key: 31 Status: AVAILABLE Compressed: YES Tag: TAG20190419T102256
Piece Name: /home/oracle/backup/rman/14tvcek1_1_1_20190419
List of Datafiles in backup set 25
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1254420 19-APR-19 /u01/app/oracle/orcl2/system01.dbf
2 Full 1254420 19-APR-19 /u01/app/oracle/orcl2/sysaux01.dbf
3 Full 1254420 19-APR-19 /u01/app/oracle/orcl2/undotbs01.dbf
4 Full 1254420 19-APR-19 /u01/app/oracle/orcl2/users01.dbf
5 Full 1254420 19-APR-19 /u01/app/oracle/orcl2/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
26 Full 9.67M DISK 00:00:00 19-APR-19
BP Key: 32 Status: AVAILABLE Compressed: NO Tag: TAG20190419T102342
Piece Name: /home/oracle/backup/rman/c-986068470-20190419-00
SPFILE Included: Modification time: 19-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1254442 Ckp time: 19-APR-19
'DATABASE(oracleDB 11g) > DBA' 카테고리의 다른 글
| [Oracle DBA]ArchiveMode 에서의 Backup & Recovery-TEMP,UNDO (0) | 2019.04.22 |
|---|---|
| [Oracle DBA]ArchiveMode 에서의 Backup & Recovery-RMAN02 (0) | 2019.04.22 |
| [Oracle DBA]ArchiveMode 에서의 Backup & Recovery-control file (0) | 2019.04.22 |
| [Oracle DBA]ArchiveMode 에서의 Backup & Recovery-logmnr (0) | 2019.04.22 |
| [Oracle DBA]ArchiveMode 에서의 Backup & Recovery-redo log (0) | 2019.04.22 |