오라클 데이터베이스 백업 복구 방법
| 전통적 방식 | RMAN(Recovery Manager) 사용 |
|---|
| 관리자가 직접 백업, 장애 발생시 백업 파일을 복원 후 복구 | 관리자는 RMAN에게 명령, 백업/복구는 RMAN이 수행 |
|---|
| 관리자의 역량에 따라 복구 가능성 결정 | ASM(Automatic Storage Management) 환경에서 RMAN 필수 |
|---|
| 서울→부산 직접 운전(피곤) | 서울→부산 기사 운전(편리) |
|---|
11.1 RMAN의 주요 특징
자주 실행하는 작업을 스크립트로 저장
- RMAN Recovery Catalog Server 사용시 백업 명령어를 저장 후 호출 가능
- 백업 스크립트가 긴 경우 유용
증분 블록 레벨 백업 기능 지원
- 과거 백업 이후 변경된 블록만 백업 수행 가능
- 백업 공간, 시간 절약
사용되지 않은 블록 건너뛰고 백업 수행
- DBF 에서 사용하는 블록만 백업 수행 (BACKUPSET)
- 백업 공간, 시간 절약
백업 수행 중 훼손된 블록 감지
- 백업 중 훼손된 블록 감지시 마킹후 계속 백업
ASM 기반 유일한 백업 도구
백업 수행시 압축 지원
- 백업 진행과 동시에 압축 가능
- 자원(CPU) 손실 거의 없음, 압축 효율 아주 좋음
11.2 Recovery Manager 구성도
- 관리자가 RMAN 에게 백업/복구 명령 → RMAN 은 관리자 대신 대상 DB에 접속하여 명령 수행 (세부 사항은 RMAN이 처리)
- 백업 관련 정보는 대상 DB의 Control file 혹은 복구 카탈로그 DB 저장
- 백업과 복구원리를 몰라도 쉬운(?) RMAN을 통해 백업/복구 가능 (하지만 원리를 알아야 문제에 대처 가능)
11.3 RMAN Memory 구조
- 백업/복구 수행 시 PGA 사용 하나 부족 시 SGA(Large Pool, Shared Pool) 사용
- Buffer
- Input Buffer : DBF 별 생성 되며 가득 차면 Output Buffer 로 복사
- Output Buffer : 여러 DBF 블록이 혼재 하며 가득 차면 Backup Set 에 저장
11.4 Channel 설정하기
Channel
백업과 복구를 하는 경로, 자동/수동 설정 가능
자동 Channel 설정
- db_recovery_file_dest 파라미터 경로(/home/oracle/app/oracle/flash_recovery_area)로 설정
[oracle@test11g ~]$ rman target sys/oracle
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Dec 16 10:19:42 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1327665863)
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
RMAN> backup tablespace example;
Starting backup at 16-DEC-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=195 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=00005 name=/home/oracle/app/oracle/oradata/ORCL/example01.dbf
channel ORA_DISK_1: starting piece 1 at 16-DEC-15
channel ORA_DISK_1: finished piece 1 at 16-DEC-15
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2015_12_16/o1_mf_nnndf_TAG20151216T102645_c71hgo68_.bkp tag=TAG20151216T102645 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-DEC-15
RMAN>
- /home/oracle/rman 경로로 설정
- %U : 유일한 번호 (파일 이름 중복 방지)
- %T : 백업 날짜
RMAN> configure channel device type disk format '/home/oracle/rman/%U_%T';
using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/home/oracle/rman/%U_%T';
new RMAN configuration parameters are successfully stored
RMAN> backup tablespace example;
Starting backup at 16-DEC-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=69 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=00005 name=/home/oracle/app/oracle/oradata/ORCL/example01.dbf
channel ORA_DISK_1: starting piece 1 at 16-DEC-15
channel ORA_DISK_1: finished piece 1 at 16-DEC-15
piece handle=/home/oracle/rman/03qov0u0_1_1_20151216 tag=TAG20151216T103024 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-DEC-15
RMAN>
수동 Channel 설정
- 백업 받을 경로 직접 지정, 자동 Channel 설정 보다 우선 적용 됨
- RMAN은 수동 Channel 백업 파일을 관리하지 않음
-- 작업형 명령어
RMAN> run {
2> allocate channel ch1 type disk format '/home/oracle/close/%U_%T';
3> backup tablespace example;
4> }
released channel: ORA_DISK_1
allocated channel: ch1
channel ch1: SID=69 device type=DISK
Starting backup at 16-DEC-15
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/ORCL/example01.dbf
channel ch1: starting piece 1 at 16-DEC-15
channel ch1: finished piece 1 at 16-DEC-15
piece handle=/home/oracle/close/04qov22i_1_1_20151216 tag=TAG20151216T104954 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-DEC-15
released channel: ch1
-- 독립형 명령어
RMAN> backup tablespace example format '/home/oracle/open/%U_%T';
Starting backup at 16-DEC-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=69 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=00005 name=/home/oracle/app/oracle/oradata/ORCL/example01.dbf
channel ORA_DISK_1: starting piece 1 at 16-DEC-15
channel ORA_DISK_1: finished piece 1 at 16-DEC-15
piece handle=/home/oracle/open/05qov240_1_1_20151216 tag=TAG20151216T105040 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-DEC-15
-- 독립형 명령어 (Backup Set 이름 지정)
RMAN> backup tablespace example format '/home/oracle/rman/%T_example.bak';
Starting backup at 16-DEC-15
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=/home/oracle/app/oracle/oradata/ORCL/example01.dbf
channel ORA_DISK_1: starting piece 1 at 16-DEC-15
channel ORA_DISK_1: finished piece 1 at 16-DEC-15
piece handle=/home/oracle/rman/20151216_example.bak tag=TAG20151216T105117 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-DEC-15
RMAN>
11.5 RMAN 백업 종류
BackupSet 백업 (Default)
- RMAN 만 알 수 있는 형태로 백업 파일 생성
- RMAN 백업의 장점 사용 가능
- Backup Piece : 분할된 백업 파일
Image Copy 백업
- 전통적 방식과 유사
- 사용 블럭만 백업, 증분 백업 등 사용 불가
RMAN> copy datafile '/home/oracle/app/oracle/oradata/ORCL/example01.dbf'
2> to '/home/oracle/rman/example01.dbf.bak';
Starting backup at 16-DEC-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/ORCL/example01.dbf
output file name=/home/oracle/rman/example01.dbf.bak tag=TAG20151216T110056 RECID=2 STAMP=898599658
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 16-DEC-15
RMAN>
11.6 RMAN 백업시 주의 사항
- V$DATABASE.LOG_MODE = 'ARCHIVELOG' 필수
- NOARCHIVELOG 는 Clean 백업만 가능 (OFFLINE TS, READONLY TS 백업)
- V$INSTANCE.STATUS IN ('OPEN', 'MOUNT') 필수
- V$LOG.STATUS = 'CURRENT' 인 온라인 리두 로그 파일 백업 불가
- 대상 TS의 V$BACKUP.STATUS = 'NOT ACTIVE' 필수
11.7 RMAN 명령어 종류
| 구분 | 독립형 명령(standalone) | 작업형 명령 |
|---|
| 형식 | 명령어; | RUN { 명령어들; } |
|---|
| 명령 | 1개 명령어만 사용 | 여러 명령어를 묶어서 한번에 사용 (편리함) |
|---|
| 예제 | RMAN> BACKUP TABLESPACE EXAMPLE; | RUN { ALLOCATE CHANNEL CH1 TYPE DISK FORMAT '...'; BACKUP TABLESPACE EXAMPLE; } |
|---|
※ 작업형 명령을 많이 씀
11.8 증분 백업 (Incremental backup)
- 이전 백업파일과 비교해서 변경된 부분만 백업 수행 (EE ONLY)
- 차등 증분 백업, 누적 증분 백업
차등 증분 백업
- 백업 레벨 숫자가 자기보다 작거나 같으면 그 시점부터 현재 까지 데이터 백업
run
{
allocate channel c1 type disk;
backup incremental level 0 database format '/home/oracle/rman/%U_%T';
}
| 요일 | 백업 | 레벨 |
|---|
| 일 | 전체 백업 | 0 |
|---|
| 월,화 | 당일 변경 백업 | 3 |
|---|
| 수 | 월~수 변경 백업 | 2 |
|---|
| 목,금 | 당일 변경 백업 | 3 |
|---|
| 토 | 목~토 변경 백업 | 2 |
|---|
| 일 | 월~일 변경 백업 | 1 |
|---|
| 구분 | 일 | 월 | 화 | 수 | 목 | 금 | 토 | 일 |
|---|
| 일 | * | ← | - | - | - | - | - | - |
|---|
| 수,토 | * | ← | - | - | ← | - | - | |
|---|
| 월,화,목,금 | * | ← | ← | | ← | ← | | |
|---|
| Data | a | b | c | d | e | f | g | h |
|---|
| Level | 0 | 3 | 3 | 2 | 3 | 3 | 2 | 1 |
|---|
| Backup | a | b | c | bcd | e | f | efg | bcdefgh |
|---|
- 목요일 백업 전 장애 발생
- 일요일 전체 백업 복원
- 수요일 증분 백업 복원
- 수요일 백업 전 장애 발생
- 일요일 전체 백업 복원
- 월요일 증분 백업 복원
- 화요일 증분 백업 복원
누적 증분 백업
- 백업 레벨 숫자가 자기보다 작으면 그 시점부터 현재 까지 데이터 백업
run
{
allocate channel c2 type disk;
backup incremental level 3 cumulative database format '/home/oracle/rman/%U_%T';
}
| 요일 | 백업 | 레벨 |
|---|
| 일 | 전체 백업 | 0 |
|---|
| 월 | 당일 변경 백업 | 3 |
|---|
| 화 | 월~화 변경 백업 | 3c |
|---|
| 수 | 월~수 변경 백업 | 2 |
|---|
| 목 | 당일 변경 백업 | 3 |
|---|
| 금 | 목~금 변경 백업 | 3c |
|---|
| 토 | 목~토 변경 백업 | 3c |
|---|
| 일 | 월~일 변경 백업 | 1 |
|---|
| 구분 | 일 | 월 | 화 | 수 | 목 | 금 | 토 | 일 |
|---|
| 일 | * | ← | - | - | - | - | - | - |
|---|
| 월,목 | * | ← | | | ← | | | |
|---|
| 화,금 | * | ← | - | | ← | - | | |
|---|
| 수,토 | * | ← | - | - | ← | - | - | |
|---|
| Data | a | b | c | d | e | f | g | h |
|---|
| Level | 0 | 3 | 3c | 2 | 3 | 3c | 3c | 1 |
|---|
| Backup | a | b | bc | bcd | e | ef | efg | bcdefgh |
|---|
- 수요일 백업 전 장애 발생
- 일요일 전체 백업 복원
- 화요일 증분 백업 복원
Block Change Tracking
- 변경된 블록만 추적 하는 기능, 10g EE 부터 지원
- 대량 블록 변경 발생 시 BCT 파일 기록 부분에 병목 발생 가능
SQL> select status from v$block_change_tracking;
STATUS
----------
DISABLED
SQL> alter database enable block change tracking using file '/home/oracle/rman/bt.dat';
Database altered.
SQL> select status from v$block_change_tracking;
STATUS
----------
ENABLED
SQL> !ls -al /home/oracle/rman/bt.dat
-rw-rw---- 1 oracle oracle 11600384 Dec 16 12:15 /home/oracle/rman/bt.dat
SQL> alter database disable block change tracking;
Database altered.
SQL> select status from v$block_change_tracking;
STATUS
----------
DISABLED
SQL> select file#, used_change_tracking from v$backup_datafile;
FILE# USE
---------- ---
5 NO
5 NO
5 NO
5 NO
5 NO
11.9 압축하면서 백업 수행하기 (10g, 11g 공통)
- 백업 수행시 압축 기능, 10g 부터 지원
- 압축 알고리즘
| 구분 | BASIC | HIGH | MEDIUM | LOW |
|---|
| 알고리즘 | BZIP2(100K) | BZIP2(900K) | ZLIB | LZO |
|---|
| ACO필요 | X | O | O | O |
|---|
| CPU부하 | 중상 | 상 | 중 | 하 |
|---|
| 백업크기 | 작음 | 매우작음 | 중간 | 큼 |
|---|
| 백업속도 | 느림 | 매우느림 | 중간 | 빠름 |
|---|
| 백업크기(테스트) | 280M | 228M | 295M | 355M | 1.1G |
|---|
| 백업속도(테스트) | 00:00:35 | 00:01:45 | 00:00:15 | 00:00:07 | 00:00:25 |
|---|
SQL> select algorithm_name, algorithm_description, requires_aco from v$rman_compression_algorithm;
ALGORITHM_NAME ALGORITHM_DESCRIPTION REQ
-------------------- -------------------------------------------------- ---
BASIC good compression ratio NO
LOW maximum possible compression speed YES
MEDIUM balance between speed and compression ratio YES
HIGH maximum possible compression ratio YES
-- 비압축 풀백업
RMAN> backup database format '/home/oracle/rman/%T_FULL_%U';
Starting backup at 16-DEC-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=195 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=/home/oracle/app/oracle/oradata/ORCL/system01.dbf
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/ORCL/sysaux01.dbf
input datafile file number=00003 name=/home/oracle/app/oracle/oradata/ORCL/undotbs01.dbf
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/ORCL/example01.dbf
input datafile file number=00004 name=/home/oracle/app/oracle/oradata/ORCL/users01.dbf
channel ORA_DISK_1: starting piece 1 at 16-DEC-15
channel ORA_DISK_1: finished piece 1 at 16-DEC-15
piece handle=/home/oracle/rman/20151216_FULL_08qov88k_1_1 tag=TAG20151216T123532 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 16-DEC-15
channel ORA_DISK_1: finished piece 1 at 16-DEC-15
piece handle=/home/oracle/rman/20151216_FULL_09qov89e_1_1 tag=TAG20151216T123532 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-DEC-15
-- 압축(BASIC) 풀백업
RMAN> show compression algorithm;
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
RMAN> backup as compressed backupset database format '/home/oracle/rman/%T_FULL_COMP1_%U';
Starting backup at 16-DEC-15
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=/home/oracle/app/oracle/oradata/ORCL/system01.dbf
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/ORCL/sysaux01.dbf
input datafile file number=00003 name=/home/oracle/app/oracle/oradata/ORCL/undotbs01.dbf
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/ORCL/example01.dbf
input datafile file number=00004 name=/home/oracle/app/oracle/oradata/ORCL/users01.dbf
channel ORA_DISK_1: starting piece 1 at 16-DEC-15
channel ORA_DISK_1: finished piece 1 at 16-DEC-15
piece handle=/home/oracle/rman/20151216_FULL_COMP1_0aqov8ba_1_1 tag=TAG20151216T123658 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 16-DEC-15
channel ORA_DISK_1: finished piece 1 at 16-DEC-15
piece handle=/home/oracle/rman/20151216_FULL_COMP1_0bqov8cd_1_1 tag=TAG20151216T123658 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-DEC-15
-- 압축(MEDIUM) 풀백업
RMAN> configure compression algorithm 'MEDIUM';
new RMAN configuration parameters:
CONFIGURE COMPRESSION ALGORITHM 'MEDIUM' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
new RMAN configuration parameters are successfully stored
RMAN> show compression algorithm;
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE COMPRESSION ALGORITHM 'MEDIUM' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
RMAN> backup as compressed backupset database format '/home/oracle/rman/%T_FULL_COMP2_%U';
Starting backup at 16-DEC-15
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=/home/oracle/app/oracle/oradata/ORCL/system01.dbf
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/ORCL/sysaux01.dbf
input datafile file number=00003 name=/home/oracle/app/oracle/oradata/ORCL/undotbs01.dbf
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/ORCL/example01.dbf
input datafile file number=00004 name=/home/oracle/app/oracle/oradata/ORCL/users01.dbf
channel ORA_DISK_1: starting piece 1 at 16-DEC-15
channel ORA_DISK_1: finished piece 1 at 16-DEC-15
piece handle=/home/oracle/rman/20151216_FULL_COMP2_0cqov8g9_1_1 tag=TAG20151216T123937 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 16-DEC-15
channel ORA_DISK_1: finished piece 1 at 16-DEC-15
piece handle=/home/oracle/rman/20151216_FULL_COMP2_0dqov8go_1_1 tag=TAG20151216T123937 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-DEC-15
-- 아카이브 압축 백업
RMAN> backup as compressed backupset archivelog all format '/home/oracle/rman/%U_ARC_%T';
Starting backup at 16-DEC-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=10 RECID=1 STAMP=898607279
input archived log thread=1 sequence=11 RECID=2 STAMP=898607331
input archived log thread=1 sequence=12 RECID=3 STAMP=898607332
input archived log thread=1 sequence=13 RECID=4 STAMP=898607335
input archived log thread=1 sequence=14 RECID=5 STAMP=898607335
input archived log thread=1 sequence=15 RECID=6 STAMP=898607341
channel ORA_DISK_1: starting piece 1 at 16-DEC-15
channel ORA_DISK_1: finished piece 1 at 16-DEC-15
piece handle=/home/oracle/rman/0jqova7d_1_1_ARC_20151216 tag=TAG20151216T130901 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-DEC-15
11.10 MultiSection Backup (11g New Feature)
- 큰 DBF 를 여러 개의 프로세스가 동시(*)에 다른 BackupSet 으로 백업 가능
RMAN> report schema;
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 680 SYSTEM *** /home/oracle/app/oracle/oradata/ORCL/system01.dbf
2 530 SYSAUX *** /home/oracle/app/oracle/oradata/ORCL/sysaux01.dbf
3 100 UNDOTBS1 *** /home/oracle/app/oracle/oradata/ORCL/undotbs01.dbf
4 5 USERS *** /home/oracle/app/oracle/oradata/ORCL/users01.dbf
5 100 EXAMPLE *** /home/oracle/app/oracle/oradata/ORCL/example01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 29 TEMP 32767 /home/oracle/app/oracle/oradata/ORCL/temp01.dbf
RMAN> backup as compressed backupset section size 300m format '/home/oracle/rman/%U_MS_%T' datafile 1;
Starting backup at 16-DEC-15
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=/home/oracle/app/oracle/oradata/ORCL/system01.dbf
backing up blocks 1 through 38400
channel ORA_DISK_1: starting piece 1 at 16-DEC-15
channel ORA_DISK_1: finished piece 1 at 16-DEC-15
piece handle=/home/oracle/rman/0sqovafm_1_1_MS_20151216 tag=TAG20151216T131326 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=/home/oracle/app/oracle/oradata/ORCL/system01.dbf
backing up blocks 38401 through 76800
channel ORA_DISK_1: starting piece 2 at 16-DEC-15
channel ORA_DISK_1: finished piece 2 at 16-DEC-15
piece handle=/home/oracle/rman/0sqovafm_2_1_MS_20151216 tag=TAG20151216T131326 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
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=/home/oracle/app/oracle/oradata/ORCL/system01.dbf
backing up blocks 76801 through 87040
channel ORA_DISK_1: starting piece 3 at 16-DEC-15
channel ORA_DISK_1: finished piece 3 at 16-DEC-15
piece handle=/home/oracle/rman/0sqovafm_3_1_MS_20151216 tag=TAG20151216T131326 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
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 16-DEC-15
channel ORA_DISK_1: finished piece 1 at 16-DEC-15
piece handle=/home/oracle/rman/0vqovagg_1_1_MS_20151216 tag=TAG20151216T131326 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-DEC-15
-- Section 별 동시 백업을 위한 채널 설정
run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
backup as compressed backupset section size 300m format '/home/oracle/rman/%U_MS_%T' datafile 1;
}
11.11 그외 각종 백업 옵션들 사용하기
keep - BackupSet 보존기간 설정
RMAN> backup as compressed backupset tablespace example format '/home/oracle/rman/%U_%T' keep until time 'sysdate+90';
Starting backup at 16-DEC-15
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=198 device type=DISK
backup will be obsolete on date 15-MAR-16
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/ORCL/example01.dbf
channel ORA_DISK_1: starting piece 1 at 16-DEC-15
channel ORA_DISK_1: finished piece 1 at 16-DEC-15
piece handle=/home/oracle/rman/15qovf12_1_1_20151216 tag=TAG20151216T143057 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
using channel ORA_DISK_1
backup will be obsolete on date 15-MAR-16
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 16-DEC-15
channel ORA_DISK_1: finished piece 1 at 16-DEC-15
piece handle=/home/oracle/rman/16qovf15_1_1_20151216 tag=TAG20151216T143057 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
current log archived
using channel ORA_DISK_1
backup will be obsolete on date 15-MAR-16
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=17 RECID=8 STAMP=898612262
channel ORA_DISK_1: starting piece 1 at 16-DEC-15
channel ORA_DISK_1: finished piece 1 at 16-DEC-15
piece handle=/home/oracle/rman/17qovf16_1_1_20151216 tag=TAG20151216T143057 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
using channel ORA_DISK_1
backup will be obsolete on date 15-MAR-16
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 16-DEC-15
channel ORA_DISK_1: finished piece 1 at 16-DEC-15
piece handle=/home/oracle/rman/18qovf17_1_1_20151216 tag=TAG20151216T143057 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-DEC-15
not backed up - 백업 안된 DBF만 백업
-- TS 생성, DBF 추가 전
RMAN> report schema;
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 680 SYSTEM *** /home/oracle/app/oracle/oradata/ORCL/system01.dbf
2 530 SYSAUX *** /home/oracle/app/oracle/oradata/ORCL/sysaux01.dbf
3 100 UNDOTBS1 *** /home/oracle/app/oracle/oradata/ORCL/undotbs01.dbf
4 5 USERS *** /home/oracle/app/oracle/oradata/ORCL/users01.dbf
5 100 EXAMPLE *** /home/oracle/app/oracle/oradata/ORCL/example01.dbf
6 10 TEST *** /home/oracle/app/oracle/oradata/ORCL/test01.dbf
7 5 USERS *** /home/oracle/app/oracle/oradata/ORCL/users02.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 29 TEMP 32767 /home/oracle/app/oracle/oradata/ORCL/temp01.dbf
-- TS 생성, DBF 추가
SQL> create tablespace test datafile '/home/oracle/app/oracle/oradata/ORCL/test01.dbf' size 10m;
Tablespace created.
SQL> alter tablespace users add datafile '/home/oracle/app/oracle/oradata/ORCL/users02.dbf' size 5m;
Tablespace altered.
SQL> create table tt500 (no number) tablespace example;
Table created.
SQL> insert into tt500 select level from dual connect by level <= 100;
100 rows created.
SQL> commit;
Commit complete.
-- TS 생성, DBF 추가 후
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 680 SYSTEM *** /home/oracle/app/oracle/oradata/ORCL/system01.dbf
2 530 SYSAUX *** /home/oracle/app/oracle/oradata/ORCL/sysaux01.dbf
3 100 UNDOTBS1 *** /home/oracle/app/oracle/oradata/ORCL/undotbs01.dbf
4 5 USERS *** /home/oracle/app/oracle/oradata/ORCL/users01.dbf
5 100 EXAMPLE *** /home/oracle/app/oracle/oradata/ORCL/example01.dbf
6 10 TEST *** /home/oracle/app/oracle/oradata/ORCL/test01.dbf
7 5 USERS *** /home/oracle/app/oracle/oradata/ORCL/users02.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 29 TEMP 32767 /home/oracle/app/oracle/oradata/ORCL/temp01.dbf
-- not backed up
RMAN> backup as compressed backupset database format '/home/oracle/rman/%U_%T' not backed up;
Starting backup at 16-DEC-15
using channel ORA_DISK_1
skipping datafile 1; already backed up on 16-DEC-15
skipping datafile 2; already backed up on 16-DEC-15
skipping datafile 3; already backed up on 16-DEC-15
skipping datafile 4; already backed up on 16-DEC-15
skipping datafile 5; already backed up on 16-DEC-15
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/ORCL/test01.dbf
input datafile file number=00007 name=/home/oracle/app/oracle/oradata/ORCL/users02.dbf
channel ORA_DISK_1: starting piece 1 at 16-DEC-15
channel ORA_DISK_1: finished piece 1 at 16-DEC-15
piece handle=/home/oracle/rman/1aqovfin_1_1_20151216 tag=TAG20151216T144023 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-DEC-15
-- 추가된 TEST TS 백업 됨
-- 추가된 users02.dbf DBF 백업 됨
-- 데이터가 변경된 EXAMPLE TS 백업 해당 없음
-- not backed up since
RMAN> backup as compressed backupset database format '/home/oracle/rman/%U_%T' not backed up since time = 'sysdate - 5';
11.12 RMAN 백업 작업 진행사항 확인하기
SQL> SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK
, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK; 2 3 4 5 6 7
SID SERIAL# CONTEXT SOFAR TOTALWORK %_COMPLETE
---------- ---------- ---------- ---------- ---------- ----------
198 3 1 34810 183040 19.02
SQL> /
SID SERIAL# CONTEXT SOFAR TOTALWORK %_COMPLETE
---------- ---------- ---------- ---------- ---------- ----------
198 3 1 95036 183040 51.92
SQL> /
SID SERIAL# CONTEXT SOFAR TOTALWORK %_COMPLETE
---------- ---------- ---------- ---------- ---------- ----------
198 3 1 160254 183040 87.55
SQL> /
no rows selected
11.13 주요 백업 예제들
-- 전체 DB 백업 (독립형)
RMAN> backup as compressed backupset database format '/home/oracle/rman/%U_%T';
-- 전체 DB 백업 (작업형)
RMAN> run
{
allocate channel c1 device type disk;
format '/home/oracle/rman/%U_%T';
backup as compressed backupset database;
}
-- 채널3개 할당 후 example, users, system, control file 백업
RMAN> run
{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
backup as compressed backupset tablespace example, users, system include current controlfile;
}
-- 전체 데이터 파일 위치 확인 후 파일 번호로 백업
RMAN> report schema;
RMAN> run
{
allocate channel c1 device type disk;
backup as compressed backupset datafile 1, 2;
}
-- 현재 Control file 백업
RMAN> run
{
allocate channel c1 device type disk;
backup current controlfile;
}
-- 복합 조건 백업
-- * SYSAUX, EXAMPLE, SYSTEM TS 백업
-- * 각각 /home/oracle/{open,rman,close} 경로 사용
-- * 작업형 명령어, 채널 3개 병렬 작업, 백업 파일 크기 10MB 제한
RMAN> run
{
allocate channel c10 device type disk;
allocate channel c11 device type disk;
allocate channel c12 device type disk;
backup as compressed backupset section size 10m
tablespace sysaux channel c10 format '/home/oracle/open/%U_%T'
tablespace system channel c11 format '/home/oracle/close/%U_%T'
tablespace example channel c12 format '/home/oracle/rman/%U_%T';
}
-- 차등 증분 백업 (전체 LEVEL 0)
RMAN> run
{
allocate channel c1 device type disk;
backup as compressed backupset incremental level 0 database format '/home/oracle/rman/%U_%T';
}
-- 차등 증분 백업 (USERS TS LEVEL 3)
RMAN> run
{
allocate channel c1 device type disk;
backup as compressed backupset incremental level 3 tablespace users format '/home/oracle/rman/%U_%T';
}
-- 누적 증분 백업
RMAN> run
{
allocate channel c1 device type disk;
backup as compressed backupset incremental level 3 cumulative tablespace users format '/home/oracle/rman/%U_%T';
}
-- 데이터베이스 와 아카이브 백업
RMAN> run
{
allocate channel c1 device type disk;
backup as compressed backupset database plus archivelog format '/home/oracle/rman/%U_%T';
}
-- 아카이브 백업
RMAN> run
{
allocate channel c1 device type disk;
backup as compressed backupset archivelog all format '/home/oracle/rman/%U_%T';
}
11.14 RMAN으로 복구하기
- 복구 = Restore + Recovery
- 전통적인 백업을 이용한 복구는 수작업이 필요하나, RMAN 은 불필요
Offline 가능한 데이터 파일 삭제 (DB 가동 중 복구)
SQL> create tablespace rtest datafile '/home/oracle/app/oracle/oradata/ORCL/rtest01.dbf' size 10m;
Tablespace created.
RMAN> report schema;
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 680 SYSTEM *** /home/oracle/app/oracle/oradata/ORCL/system01.dbf
2 530 SYSAUX *** /home/oracle/app/oracle/oradata/ORCL/sysaux01.dbf
3 100 UNDOTBS1 *** /home/oracle/app/oracle/oradata/ORCL/undotbs01.dbf
4 5 USERS *** /home/oracle/app/oracle/oradata/ORCL/users01.dbf
5 100 EXAMPLE *** /home/oracle/app/oracle/oradata/ORCL/example01.dbf
6 10 TEST *** /home/oracle/app/oracle/oradata/ORCL/test01.dbf
7 5 USERS *** /home/oracle/app/oracle/oradata/ORCL/users02.dbf
8 10 RTEST *** /home/oracle/app/oracle/oradata/ORCL/rtest01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 29 TEMP 32767 /home/oracle/app/oracle/oradata/ORCL/temp01.dbf
RMAN> backup as compressed backupset database format '/home/oracle/rman/%T_FULL_%U';
Starting backup at 16-DEC-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=72 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=/home/oracle/app/oracle/oradata/ORCL/system01.dbf
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/ORCL/sysaux01.dbf
input datafile file number=00003 name=/home/oracle/app/oracle/oradata/ORCL/undotbs01.dbf
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/ORCL/example01.dbf
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/ORCL/test01.dbf
input datafile file number=00008 name=/home/oracle/app/oracle/oradata/ORCL/rtest01.dbf
input datafile file number=00004 name=/home/oracle/app/oracle/oradata/ORCL/users01.dbf
input datafile file number=00007 name=/home/oracle/app/oracle/oradata/ORCL/users02.dbf
channel ORA_DISK_1: starting piece 1 at 16-DEC-15
channel ORA_DISK_1: finished piece 1 at 16-DEC-15
piece handle=/home/oracle/rman/20151216_FULL_cvqp01ah_1_1 tag=TAG20151216T194313 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 16-DEC-15
channel ORA_DISK_1: finished piece 1 at 16-DEC-15
piece handle=/home/oracle/rman/20151216_FULL_d0qp01b0_1_1 tag=TAG20151216T194313 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-DEC-15
[oracle@test11g ~]$ rm -f /home/oracle/app/oracle/oradata/ORCL/rtest01.dbf
[oracle@test11g ~]$ ls -l /home/oracle/app/oracle/oradata/ORCL/rtest01.dbf
ls: /home/oracle/app/oracle/oradata/ORCL/rtest01.dbf: No such file or directory
RMAN> sql 'alter tablespace rtest offline immediate';
sql statement: alter tablespace rtest offline immediate
RMAN> restore tablespace rtest;
Starting restore at 16-DEC-15
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 00008 to /home/oracle/app/oracle/oradata/ORCL/rtest01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/rman/20151216_FULL_cvqp01ah_1_1
channel ORA_DISK_1: piece handle=/home/oracle/rman/20151216_FULL_cvqp01ah_1_1 tag=TAG20151216T194313
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 16-DEC-15
RMAN> recover tablespace rtest;
Starting recover at 16-DEC-15
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 16-DEC-15
RMAN> sql 'alter tablespace rtest online';
sql statement: alter tablespace rtest online
[oracle@test11g ~]$ ls -l /home/oracle/app/oracle/oradata/ORCL/rtest01.dbf
-rw-rw---- 1 oracle oracle 10493952 Dec 16 19:45 /home/oracle/app/oracle/oradata/ORCL/rtest01.dbf
-- 작업형 명령어
RMAN> run
{
sql 'alter tablespace rtest offline immediate';
restore tablespace rtest;
recover tablespace rtest;
sql 'alter tablespace rtest online';
}2> 3> 4> 5> 6> 7>
sql statement: alter tablespace rtest offline immediate
Starting restore at 16-DEC-15
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 00008 to /home/oracle/app/oracle/oradata/ORCL/rtest01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/rman/20151216_FULL_cvqp01ah_1_1
channel ORA_DISK_1: piece handle=/home/oracle/rman/20151216_FULL_cvqp01ah_1_1 tag=TAG20151216T194313
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 16-DEC-15
Starting recover at 16-DEC-15
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 16-DEC-15
sql statement: alter tablespace rtest online
Offline 불가능 데이터 파일 삭제 (DB 종료 후 복구)
RMAN> report schema;
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 680 SYSTEM *** /home/oracle/app/oracle/oradata/ORCL/system01.dbf
2 530 SYSAUX *** /home/oracle/app/oracle/oradata/ORCL/sysaux01.dbf
3 100 UNDOTBS1 *** /home/oracle/app/oracle/oradata/ORCL/undotbs01.dbf
4 5 USERS *** /home/oracle/app/oracle/oradata/ORCL/users01.dbf
5 100 EXAMPLE *** /home/oracle/app/oracle/oradata/ORCL/example01.dbf
6 10 TEST *** /home/oracle/app/oracle/oradata/ORCL/test01.dbf
7 5 USERS *** /home/oracle/app/oracle/oradata/ORCL/users02.dbf
8 10 RTEST *** /home/oracle/app/oracle/oradata/ORCL/rtest01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 29 TEMP 32767 /home/oracle/app/oracle/oradata/ORCL/temp01.dbf
[oracle@test11g ~]$ rm -f /home/oracle/app/oracle/oradata/ORCL/system01.dbf
[oracle@test11g ~]$ ls -l /home/oracle/app/oracle/oradata/ORCL/system01.dbf
ls: /home/oracle/app/oracle/oradata/ORCL/system01.dbf: No such file or directory
RMAN> sql 'alter tablespace system offline immediate';
sql statement: alter tablespace system offline immediate
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 12/16/2015 19:52:19
RMAN-11003: failure during parse/execution of SQL statement: alter tablespace system offline immediate
ORA-01541: system tablespace cannot be brought offline; shut down if necessary
RMAN> shutdown immediate;
database closed
database dismounted
Oracle instance shut down
RMAN> startup
connected to target database (not started)
Oracle instance started
database mounted
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 12/16/2015 19:53:07
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/home/oracle/app/oracle/oradata/ORCL/system01.dbf'
RMAN> restore tablespace system;
Starting restore at 16-DEC-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /home/oracle/app/oracle/oradata/ORCL/system01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/rman/20151216_FULL_cvqp01ah_1_1
channel ORA_DISK_1: piece handle=/home/oracle/rman/20151216_FULL_cvqp01ah_1_1 tag=TAG20151216T194313
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 16-DEC-15
RMAN> recover tablespace system;
Starting recover at 16-DEC-15
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 16-DEC-15
RMAN> alter database open;
database opened
[oracle@test11g ~]$ ls -l /home/oracle/app/oracle/oradata/ORCL/system01.dbf
-rw-rw---- 1 oracle oracle 713039872 Dec 16 19:53 /home/oracle/app/oracle/oradata/ORCL/system01.dbf
임시 경로에서 복구
RMAN> report schema;
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 680 SYSTEM *** /home/oracle/app/oracle/oradata/ORCL/system01.dbf
2 530 SYSAUX *** /home/oracle/app/oracle/oradata/ORCL/sysaux01.dbf
3 100 UNDOTBS1 *** /home/oracle/app/oracle/oradata/ORCL/undotbs01.dbf
4 5 USERS *** /home/oracle/app/oracle/oradata/ORCL/users01.dbf
5 100 EXAMPLE *** /home/oracle/app/oracle/oradata/ORCL/example01.dbf
6 10 TEST *** /home/oracle/app/oracle/oradata/ORCL/test01.dbf
8 10 RTEST *** /home/oracle/app/oracle/oradata/ORCL/rtest01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 29 TEMP 32767 /home/oracle/app/oracle/oradata/ORCL/temp01.dbf
[oracle@test11g ~]$ rm -f /home/oracle/app/oracle/oradata/ORCL/rtest01.dbf
[oracle@test11g ~]$ ls -l /home/oracle/app/oracle/oradata/ORCL/rtest01.dbf
ls: /home/oracle/app/oracle/oradata/ORCL/rtest01.dbf: No such file or directory
RMAN> run
2> {
3> sql 'alter tablespace rtest offline immediate';
4> set newname for datafile '/home/oracle/app/oracle/oradata/ORCL/rtest01.dbf' to '/home/oracle/temp/rtest01.dbf';
5> restore tablespace rtest;
6> switch datafile 8; <<< rename datafile (control file)
7> recover tablespace rtest;
8> sql 'alter tablespace rtest online';
9> }
sql statement: alter tablespace rtest offline immediate
executing command: SET NEWNAME
Starting restore at 16-DEC-15
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 00008 to /home/oracle/temp/rtest01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/rman/20151216_FULL_cvqp01ah_1_1
channel ORA_DISK_1: piece handle=/home/oracle/rman/20151216_FULL_cvqp01ah_1_1 tag=TAG20151216T194313
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 16-DEC-15
datafile 8 switched to datafile copy
input datafile copy RECID=4 STAMP=898632073 file name=/home/oracle/temp/rtest01.dbf
Starting recover at 16-DEC-15
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 16-DEC-15
sql statement: alter tablespace rtest online
RMAN> report schema;
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 680 SYSTEM *** /home/oracle/app/oracle/oradata/ORCL/system01.dbf
2 530 SYSAUX *** /home/oracle/app/oracle/oradata/ORCL/sysaux01.dbf
3 100 UNDOTBS1 *** /home/oracle/app/oracle/oradata/ORCL/undotbs01.dbf
4 5 USERS *** /home/oracle/app/oracle/oradata/ORCL/users01.dbf
5 100 EXAMPLE *** /home/oracle/app/oracle/oradata/ORCL/example01.dbf
6 10 TEST *** /home/oracle/app/oracle/oradata/ORCL/test01.dbf
8 10 RTEST *** /home/oracle/temp/rtest01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 29 TEMP 32767 /home/oracle/app/oracle/oradata/ORCL/temp01.dbf
임시 경로에 필요한 DBF 만 복원 후 복구
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 680 SYSTEM *** /home/oracle/app/oracle/oradata/ORCL/system01.dbf
2 520 SYSAUX *** /home/oracle/app/oracle/oradata/ORCL/sysaux01.dbf
3 100 UNDOTBS1 *** /home/oracle/app/oracle/oradata/ORCL/undotbs01.dbf
4 5 USERS *** /home/oracle/app/oracle/oradata/ORCL/users01.dbf
5 100 EXAMPLE *** /home/oracle/app/oracle/oradata/ORCL/example01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 29 TEMP 32767 /home/oracle/app/oracle/oradata/ORCL/temp01.dbf
RMAN> backup as compressed backupset database format '/home/oracle/rman/%U_FULL_%T';
SQL> create tablespace rtest2 datafile '/home/oracle/app/oracle/oradata/ORCL/rtest02.dbf' size 10m;
SQL> create table scott.tt310 (no number) tablespace rtest2;
SQL> insert into scott.tt310 values (1);
SQL> commit;
SQL> select * from scott.tt310;
NO
----------
1
SQL> !rm /home/oracle/app/oracle/oradata/ORCL/rtest02.dbf
SQL> !ls /home/oracle/app/oracle/oradata/ORCL/rtest02.dbf
ls: /home/oracle/app/oracle/oradata/ORCL/rtest02.dbf: No such file or directory
SQL> alter tablespace rtest2 offline;
Tablespace altered.
SQL> alter tablespace rtest2 online;
alter tablespace rtest2 online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/home/oracle/app/oracle/oradata/ORCL/rtest02.dbf'
SQL> select * from scott.tt310;
select * from scott.tt310
*
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '/home/oracle/app/oracle/oradata/ORCL/rtest02.dbf'
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !
[oracle@test11g ~]$ cp /home/oracle/app/oracle/oradata/ORCL/*.log /home/oracle/temp
[oracle@test11g ~]$ cp /home/oracle/app/oracle/oradata/ORCL/*.ctl /home/oracle/temp
[oracle@test11g ~]$ vi /home/oracle/app/oracle/product/11.2.0/dbs/initORCL.ora
[oracle@test11g ~]$ grep control_files /home/oracle/app/oracle/product/11.2..0/dbs/initORCL.ora
#*.control_files='/home/oracle/app/oracle/oradata/ORCL/control01.ctl','/home/oracle/app/oracle/flash_recovery_area/ORCL/control02.ctl'
*.control_files='/home/oracle/temp/control01.ctl'
RMAN> run
{
startup mount;
# redo
sql "alter database rename file ''/home/oracle/app/oracle/oradata/ORCL/redo01.log'' to ''/home/oracle/temp/redo01.log''";
sql "alter database rename file ''/home/oracle/app/oracle/oradata/ORCL/redo02.log'' to ''/home/oracle/temp/redo02.log''";
sql "alter database rename file ''/home/oracle/app/oracle/oradata/ORCL/redo03.log'' to ''/home/oracle/temp/redo03.log''";
# rename dbf
set newname for datafile 1 to '/home/oracle/temp/system01.dbf';
set newname for datafile 2 to '/home/oracle/temp/sysaux01.dbf';
set newname for datafile 3 to '/home/oracle/temp/undotbs01.dbf';
# drop dbf
sql "alter database datafile ''/home/oracle/app/oracle/oradata/ORCL/users01..dbf'' offline drop";
sql "alter database datafile ''/home/oracle/app/oracle/oradata/ORCL/example01.dbf'' offline drop";
# create dbf
sql "alter database create datafile ''/home/oracle/app/oracle/oradata/ORCL/rtest02.dbf'' as ''/home/oracle/temp/rtest02.dbf''";
# restore
restore tablespace system;
restore tablespace sysaux;
restore tablespace undotbs1;
# controlfile update
switch datafile all;
# recover
recover tablespace system;
recover tablespace sysaux;
recover tablespace undotbs1;
recover tablespace rtest2;
# open
alter database open;
sql 'alter tablespace rtest2 online';
}
SQL> @dd
TABLESPACE MB FILE_NAME
---------- ---- --------------------------------------------------
EXAMPLE /home/oracle/app/oracle/oradata/ORCL/example01.dbf
USERS /home/oracle/app/oracle/oradata/ORCL/users01.dbf
RTEST2 10 /home/oracle/temp/rtest02.dbf
SYSAUX 520 /home/oracle/temp/sysaux01.dbf
SYSTEM 680 /home/oracle/temp/system01.dbf
UNDOTBS1 100 /home/oracle/temp/undotbs01.dbf
6 rows selected.
SQL> select * from scott.tt310;
NO
----------
1
[oracle@test11g ~]$ ls -al temp
total 1506128
drwxrwxr-x 2 oracle oracle 4096 Dec 16 21:41 .
drwx------ 21 oracle oracle 4096 Dec 16 21:38 ..
-rw-r----- 1 oracle oracle 9748480 Dec 16 21:42 control01.ctl
-rw-r----- 1 oracle oracle 52429312 Dec 16 21:41 redo01.log
-rw-r----- 1 oracle oracle 52429312 Dec 16 21:41 redo02.log
-rw-r----- 1 oracle oracle 52429312 Dec 16 21:42 redo03.log
-rw-rw---- 1 oracle oracle 10493952 Dec 16 21:41 rtest02.dbf
-rw-rw---- 1 oracle oracle 545267712 Dec 16 21:41 sysaux01.dbf
-rw-rw---- 1 oracle oracle 713039872 Dec 16 21:41 system01.dbf
-rw-rw---- 1 oracle oracle 104865792 Dec 16 21:41 undotbs01.dbf
-- set newname for database to 활용
RMAN> run
{
startup mount;
set newname for database to '/home/oracle/temp/%b';
restore database;
switch datafile all;
recover database;
alter database open;
}
Drop table 후 복구 (임시 경로)
- 임시 경로(/home/oracle/temp)를 활용한 불완전 복구
- Control File, Redo Log File, Data File
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 680 SYSTEM *** /home/oracle/app/oracle/oradata/ORCL/system01.dbf
2 530 SYSAUX *** /home/oracle/app/oracle/oradata/ORCL/sysaux01.dbf
3 100 UNDOTBS1 *** /home/oracle/app/oracle/oradata/ORCL/undotbs01.dbf
4 5 USERS *** /home/oracle/app/oracle/oradata/ORCL/users01.dbf
5 100 EXAMPLE *** /home/oracle/app/oracle/oradata/ORCL/example01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 29 TEMP 32767 /home/oracle/app/oracle/oradata/ORCL/temp01.dbf
RMAN> backup as compressed backupset database format '/home/oracle/rman/%T_FULL_%U';
SQL> create table scott.tt320 (no number) tablespace users;
Table created.
SQL> insert into scott.tt320 values (1);
1 row created.
SQL> commit;
Commit complete.
SQL> select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2015-12-16 22:13:48
SQL> select * from scott.tt320;
NO
----------
1
SQL> drop table scott.tt320 purge;
Table dropped.
SQL> shutdown immediate
[oracle@test11g ~]$ cp /home/oracle/app/oracle/oradata/ORCL/*.log /home/oracle/temp
[oracle@test11g ~]$ cp /home/oracle/app/oracle/oradata/ORCL/*.ctl /home/oracle/temp
[oracle@test11g ~]$ vi /home/oracle/app/oracle/product/11.2.0/dbs/initORCL.ora
[oracle@test11g ~]$ grep control_files /home/oracle/app/oracle/product/11.2..0/dbs/initORCL.ora
#*.control_files='/home/oracle/app/oracle/oradata/ORCL/control01.ctl','/home/oracle/app/oracle/flash_recovery_area/ORCL/control02.ctl'
*.control_files='/home/oracle/temp/control01.ctl'
RMAN> run
{
startup mount;
# redo
sql "alter database rename file ''/home/oracle/app/oracle/oradata/ORCL/redo01.log'' to ''/home/oracle/temp/redo01.log''";
sql "alter database rename file ''/home/oracle/app/oracle/oradata/ORCL/redo02.log'' to ''/home/oracle/temp/redo02.log''";
sql "alter database rename file ''/home/oracle/app/oracle/oradata/ORCL/redo03.log'' to ''/home/oracle/temp/redo03.log''";
# rename dbf
set newname for datafile 1 to '/home/oracle/temp/system01.dbf';
set newname for datafile 2 to '/home/oracle/temp/sysaux01.dbf';
set newname for datafile 3 to '/home/oracle/temp/undotbs01.dbf';
set newname for datafile 4 to '/home/oracle/temp/users01.dbf';
set newname for datafile 5 to '/home/oracle/temp/example01.dbf';
# restore
restore database;
# controlfile update
switch datafile all;
# recover
sql 'alter session set nls_date_format="YYYY-MM-DD HH24:MI:SS"';
set until time = '2015-12-16 22:13:48';
recover database;
# open
alter database open resetlogs;
}
SQL> @dd
TABLESPACE MB FILE_NAME
---------- ----- --------------------------------------------------
EXAMPLE 100 /home/oracle/temp/example01.dbf
SYSAUX 530 /home/oracle/temp/sysaux01.dbf
SYSTEM 680 /home/oracle/temp/system01.dbf
UNDOTBS1 100 /home/oracle/temp/undotbs01.dbf
USERS 5 /home/oracle/temp/users01.dbf
SQL> select * from scott.tt320;
NO
----------
1
Drop User 복구 (임시 경로) - 연습문제
- 임시 경로(/home/oracle/temp)를 활용한 불완전 복구
RMAN> backup as compressed backupset database format '/home/oracle/rman/%T_FULL_%U';
SQL> create user tuser identified by oracle default tablespace users;
SQL> grant connect, resource to tuser;
SQL> conn tuser/oracle
SQL> create table test1(no number);
SQL> insert into test1 values (1);
SQL> commit;
SQL> select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2015-12-16 22:26:53
SQL> conn / as sysdba
SQL> select * from tuser.test1;
SQL> drop user tuser cascade;
SQL> shutdown immediate
[oracle@test11g ~]$ cp /home/oracle/app/oracle/oradata/ORCL/*.log /home/oracle/temp
[oracle@test11g ~]$ cp /home/oracle/app/oracle/oradata/ORCL/*.ctl /home/oracle/temp
[oracle@test11g ~]$ vi /home/oracle/app/oracle/product/11.2.0/dbs/initORCL.ora
[oracle@test11g ~]$ grep control_files /home/oracle/app/oracle/product/11.2..0/dbs/initORCL.ora
#*.control_files='/home/oracle/app/oracle/oradata/ORCL/control01.ctl','/home/oracle/app/oracle/flash_recovery_area/ORCL/control02.ctl'
*.control_files='/home/oracle/temp/control01.ctl'
RMAN> run
{
startup mount;
# redo
sql "alter database rename file ''/home/oracle/app/oracle/oradata/ORCL/redo01.log'' to ''/home/oracle/temp/redo01.log''";
sql "alter database rename file ''/home/oracle/app/oracle/oradata/ORCL/redo02.log'' to ''/home/oracle/temp/redo02.log''";
sql "alter database rename file ''/home/oracle/app/oracle/oradata/ORCL/redo03.log'' to ''/home/oracle/temp/redo03.log''";
# rename dbf
set newname for datafile 1 to '/home/oracle/temp/system01.dbf';
set newname for datafile 2 to '/home/oracle/temp/sysaux01.dbf';
set newname for datafile 3 to '/home/oracle/temp/undotbs01.dbf';
set newname for datafile 4 to '/home/oracle/temp/users01.dbf';
set newname for datafile 5 to '/home/oracle/temp/example01.dbf';
# restore
restore database;
# controlfile update
switch datafile all;
# recover
sql 'alter session set nls_date_format="YYYY-MM-DD HH24:MI:SS"';
set until time = '2015-12-16 22:26:53';
recover database;
# open
alter database open resetlogs;
}
SQL> @dd
TABLESPACE MB FILE_NAME
---------- ----- --------------------------------------------------
EXAMPLE 100 /home/oracle/temp/example01.dbf
SYSAUX 530 /home/oracle/temp/sysaux01.dbf
SYSTEM 680 /home/oracle/temp/system01.dbf
UNDOTBS1 100 /home/oracle/temp/undotbs01.dbf
USERS 5 /home/oracle/temp/users01.dbf
SQL> select username, default_tablespace from dba_users where username = 'TUSER';
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
TUSER USERS
SQL> select * from tuser.test1;
NO
----------
1
Drop tablespace 복구 (백업 파일 존재)
- 복구시 삭제된 TS 정보가 남아 있는 이전 Control file 필요
SQL> create tablespace rtest4 datafile '/home/oracle/app/oracle/oradata/ORCL/rtest04.dbf' size 10m;
Tablespace created.
SQL> @dd
TABLESPACE MB FILE_NAME
---------- ----- --------------------------------------------------
EXAMPLE 100 /home/oracle/app/oracle/oradata/ORCL/example01.dbf
RTEST4 10 /home/oracle/app/oracle/oradata/ORCL/rtest04.dbf
SYSAUX 530 /home/oracle/app/oracle/oradata/ORCL/sysaux01.dbf
SYSTEM 680 /home/oracle/app/oracle/oradata/ORCL/system01.dbf
UNDOTBS1 100 /home/oracle/app/oracle/oradata/ORCL/undotbs01.dbf
USERS 5 /home/oracle/app/oracle/oradata/ORCL/users01.dbf
6 rows selected.
RMAN> backup as compressed backupset database format '/home/oracle/rman/%U_%T';
Starting backup at 16-DEC-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=132 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=/home/oracle/app/oracle/oradata/ORCL/system01.dbf
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/ORCL/sysaux01.dbf
input datafile file number=00003 name=/home/oracle/app/oracle/oradata/ORCL/undotbs01.dbf
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/ORCL/example01.dbf
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/ORCL/rtest04.dbf
input datafile file number=00004 name=/home/oracle/app/oracle/oradata/ORCL/users01.dbf
channel ORA_DISK_1: starting piece 1 at 16-DEC-15
channel ORA_DISK_1: finished piece 1 at 16-DEC-15
piece handle=/home/oracle/rman/09qp0cpb_1_1_20151216 tag=TAG20151216T225851 comment=NONE <<<
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 16-DEC-15
channel ORA_DISK_1: finished piece 1 at 16-DEC-15
piece handle=/home/oracle/rman/0aqp0cqe_1_1_20151216 tag=TAG20151216T225851 comment=NONE <<<
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-DEC-15
SQL> create table scott.tt330 (no number) tablespace rtest4;
SQL> insert into scott.tt330 values (1);
SQL> commit;
SQL> select * from scott.tt330;
NO
----------
1
SQL> select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2015-12-16 23:01:58
SQL> drop tablespace rtest4 including contents and datafiles;
SQL> select * from scott.tt330;
select * from scott.tt330
*
ERROR at line 1:
ORA-00942: table or view does not exist
[oracle@test11g ~]$ cp /home/oracle/app/oracle/oradata/ORCL/*.log /home/oracle/temp
[oracle@test11g ~]$ vi /home/oracle/app/oracle/product/11.2.0/dbs/initORCL.ora
[oracle@test11g ~]$ grep control_files /home/oracle/app/oracle/product/11.2..0/dbs/initORCL.ora
#*.control_files='/home/oracle/app/oracle/oradata/ORCL/control01.ctl','/home/oracle/app/oracle/flash_recovery_area/ORCL/control02.ctl'
*.control_files='/home/oracle/temp/control01.ctl'
RMAN> run
{
startup nomount;
# restore controlfile
restore controlfile from '/home/oracle/rman/0aqp0cqe_1_1_20151216'; <<<
alter database mount;
# redo
sql "alter database rename file ''/home/oracle/app/oracle/oradata/ORCL/redo01.log'' to ''/home/oracle/temp/redo01.log''";
sql "alter database rename file ''/home/oracle/app/oracle/oradata/ORCL/redo02.log'' to ''/home/oracle/temp/redo02.log''";
sql "alter database rename file ''/home/oracle/app/oracle/oradata/ORCL/redo03.log'' to ''/home/oracle/temp/redo03.log''";
# rename dbf
set newname for datafile 1 to '/home/oracle/temp/system01.dbf';
set newname for datafile 2 to '/home/oracle/temp/sysaux01.dbf';
set newname for datafile 3 to '/home/oracle/temp/undotbs01.dbf';
set newname for datafile 4 to '/home/oracle/temp/users01.dbf';
set newname for datafile 5 to '/home/oracle/temp/example01.dbf';
set newname for datafile 6 to '/home/oracle/temp/rtest04.dbf';
# set until time
sql 'alter session set nls_date_format="YYYY-MM-DD HH24:MI:SS"';
set until time = '2015-12-16 23:01:58';
# restore
restore database from tag "TAG20151216T225851";
# controlfile update
switch datafile all;
# recover
recover database;
# open
alter database open resetlogs;
}
-- 수행 로그
Oracle instance started
Total System Global Area 417546240 bytes
Fixed Size 2213936 bytes
Variable Size 301991888 bytes
Database Buffers 104857600 bytes
Redo Buffers 8482816 bytes
Starting restore at 16-DEC-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/home/oracle/temp/control01.ctl
Finished restore at 16-DEC-15
database mounted
released channel: ORA_DISK_1
sql statement: alter database rename file ''/home/oracle/app/oracle/oradata/ORCL/redo01.log'' to ''/home/oracle/temp/redo01.log''
sql statement: alter database rename file ''/home/oracle/app/oracle/oradata/ORCL/redo02.log'' to ''/home/oracle/temp/redo02.log''
sql statement: alter database rename file ''/home/oracle/app/oracle/oradata/ORCL/redo03.log'' to ''/home/oracle/temp/redo03.log''
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
sql statement: alter session set nls_date_format="YYYY-MM-DD HH24:MI:SS"
executing command: SET until clause
Starting restore at 16-DEC-15
Starting implicit crosscheck backup at 16-DEC-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
Crosschecked 8 objects
Finished implicit crosscheck backup at 16-DEC-15
Starting implicit crosscheck copy at 16-DEC-15
using channel ORA_DISK_1
Finished implicit crosscheck copy at 16-DEC-15
searching for all files in the recovery area
cataloging files...
no files cataloged
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 /home/oracle/temp/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/temp/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/temp/undotbs01..dbf
channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/temp/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/temp/example01..dbf
channel ORA_DISK_1: restoring datafile 00006 to /home/oracle/temp/rtest04.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/rman/09qp0cpb_1_1_20151216
channel ORA_DISK_1: piece handle=/home/oracle/rman/09qp0cpb_1_1_20151216 tag=TAG20151216T225851
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 16-DEC-15
datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=898643547 file name=/home/oracle/temp/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=9 STAMP=898643547 file name=/home/oracle/temp/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=898643547 file name=/home/oracle/temp/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=11 STAMP=898643547 file name=/home/oracle/temp/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=898643547 file name=/home/oracle/temp/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=13 STAMP=898643547 file name=/home/oracle/temp/rtest04.dbf
Starting recover at 16-DEC-15
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 9 is already on disk as file /home/oracle/temp/redo03.log
archived log for thread 1 with sequence 10 is already on disk as file /home/oracle/temp/redo01.log
archived log file name=/home/oracle/temp/redo03.log thread=1 sequence=9
archived log file name=/home/oracle/temp/redo01.log thread=1 sequence=10
media recovery complete, elapsed time: 00:00:00
Finished recover at 16-DEC-15
database opened
SQL> @dd
TABLESPACE MB FILE_NAME
---------- ----- --------------------------------------------------
EXAMPLE 100 /home/oracle/temp/example01.dbf
RTEST4 10 /home/oracle/temp/rtest04.dbf
SYSAUX 530 /home/oracle/temp/sysaux01.dbf
SYSTEM 680 /home/oracle/temp/system01.dbf
UNDOTBS1 100 /home/oracle/temp/undotbs01.dbf
USERS 5 /home/oracle/temp/users01.dbf
6 rows selected.
SQL> select * from scott.tt330;
NO
----------
1
Drop tablespace 복구 (백업 파일 없음)
| 시점 | 내용 |
|---|
| A | DB 전체 RMAN 풀백업 |
|---|
| ▼ | |
|---|
| B | RTEST5 TS 생성 및 예제 데이터 생성 |
|---|
| ▼ | |
|---|
| C | RTEST5 TS 삭제 (백업 없음) |
|---|
| ▼ | |
|---|
| D | RTEST5 TS 복구 필요 |
|---|
SQL> @dd
-- 시점A
TABLESPACE MB FILE_NAME
---------- ----- --------------------------------------------------
EXAMPLE 100 /home/oracle/app/oracle/oradata/ORCL/example01.dbf
SYSAUX 530 /home/oracle/app/oracle/oradata/ORCL/sysaux01.dbf
SYSTEM 680 /home/oracle/app/oracle/oradata/ORCL/system01.dbf
UNDOTBS1 100 /home/oracle/app/oracle/oradata/ORCL/undotbs01.dbf
USERS 5 /home/oracle/app/oracle/oradata/ORCL/users01.dbf
RMAN> backup as compressed backupset database format '/home/oracle/rman/%U_FULL_%T';
Starting backup at 16-DEC-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 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=/home/oracle/app/oracle/oradata/ORCL/system01.dbf
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/ORCL/sysaux01.dbf
input datafile file number=00003 name=/home/oracle/app/oracle/oradata/ORCL/undotbs01.dbf
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/ORCL/example01.dbf
input datafile file number=00004 name=/home/oracle/app/oracle/oradata/ORCL/users01.dbf
channel ORA_DISK_1: starting piece 1 at 16-DEC-15
channel ORA_DISK_1: finished piece 1 at 16-DEC-15
piece handle=/home/oracle/rman/0bqp0em8_1_1_FULL_20151216 tag=TAG20151216T233120 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 16-DEC-15
channel ORA_DISK_1: finished piece 1 at 16-DEC-15
piece handle=/home/oracle/rman/0cqp0enb_1_1_FULL_20151216 tag=TAG20151216T233120 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-DEC-15
-- 시점B
SQL> create tablespace rtest5 datafile '/home/oracle/app/oracle/oradata/ORCL/rtest05.dbf' size 5m;
SQL> create table scott.tt340 (no number) tablespace rtest5;
SQL> insert into scott.tt340 values (1);
SQL> commit;
SQL> select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2015-12-16 23:33:12
SQL> select * from scott.tt340;
NO
----------
1
-- 시점C
SQL> @dd
TABLESPACE MB FILE_NAME
---------- ----- --------------------------------------------------
EXAMPLE 100 /home/oracle/app/oracle/oradata/ORCL/example01.dbf
RTEST5 5 /home/oracle/app/oracle/oradata/ORCL/rtest05.dbf <<<
SYSAUX 530 /home/oracle/app/oracle/oradata/ORCL/sysaux01.dbf
SYSTEM 680 /home/oracle/app/oracle/oradata/ORCL/system01.dbf
UNDOTBS1 100 /home/oracle/app/oracle/oradata/ORCL/undotbs01.dbf
USERS 5 /home/oracle/app/oracle/oradata/ORCL/users01.dbf
6 rows selected.
SQL> drop tablespace rtest5 including contents and datafiles;
Tablespace dropped.
SQL> @dd
TABLESPACE MB FILE_NAME
---------- ----- --------------------------------------------------
EXAMPLE 100 /home/oracle/app/oracle/oradata/ORCL/example01.dbf
SYSAUX 530 /home/oracle/app/oracle/oradata/ORCL/sysaux01.dbf
SYSTEM 680 /home/oracle/app/oracle/oradata/ORCL/system01.dbf
UNDOTBS1 100 /home/oracle/app/oracle/oradata/ORCL/undotbs01.dbf
USERS 5 /home/oracle/app/oracle/oradata/ORCL/users01.dbf
SQL> select * from scott.tt340;
select * from scott.tt340
*
ERROR at line 1:
ORA-00942: table or view does not exist
-- 시점D
SQL> shutdown immediate;
[oracle@test11g ~]$ cp /home/oracle/app/oracle/oradata/ORCL/*.log /home/oracle/temp
[oracle@test11g ~]$ vi /home/oracle/app/oracle/product/11.2.0/dbs/initORCL.ora
[oracle@test11g ~]$ grep control_files /home/oracle/app/oracle/product/11.2..0/dbs/initORCL.ora
#*.control_files='/home/oracle/app/oracle/oradata/ORCL/control01.ctl','/home/oracle/app/oracle/flash_recovery_area/ORCL/control02.ctl'
*.control_files='/home/oracle/temp/control01.ctl'
RMAN> run
{
startup nomount;
# restore controlfile
restore controlfile from '/home/oracle/rman/0cqp0enb_1_1_FULL_20151216';
alter database mount;
# redo
sql "alter database rename file ''/home/oracle/app/oracle/oradata/ORCL/redo01.log'' to ''/home/oracle/temp/redo01.log''";
sql "alter database rename file ''/home/oracle/app/oracle/oradata/ORCL/redo02.log'' to ''/home/oracle/temp/redo02.log''";
sql "alter database rename file ''/home/oracle/app/oracle/oradata/ORCL/redo03.log'' to ''/home/oracle/temp/redo03.log''";
# rename dbf
set newname for datafile 1 to '/home/oracle/temp/system01.dbf';
set newname for datafile 2 to '/home/oracle/temp/sysaux01.dbf';
set newname for datafile 3 to '/home/oracle/temp/undotbs01.dbf';
set newname for datafile 4 to '/home/oracle/temp/users01.dbf';
set newname for datafile 5 to '/home/oracle/temp/example01.dbf';
# set until time
sql 'alter session set nls_date_format="YYYY-MM-DD HH24:MI:SS"';
set until time = '2015-12-16 23:33:12';
# restore
restore database from tag "TAG20151216T233120";
# controlfile update
switch datafile all;
}
-- 로그
Oracle instance started
Total System Global Area 417546240 bytes
Fixed Size 2213936 bytes
Variable Size 301991888 bytes
Database Buffers 104857600 bytes
Redo Buffers 8482816 bytes
Starting restore at 16-DEC-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/home/oracle/temp/control01.ctl
Finished restore at 16-DEC-15
database mounted
released channel: ORA_DISK_1
sql statement: alter database rename file ''/home/oracle/app/oracle/oradata/ORCL/redo01.log'' to ''/home/oracle/temp/redo01.log''
sql statement: alter database rename file ''/home/oracle/app/oracle/oradata/ORCL/redo02.log'' to ''/home/oracle/temp/redo02.log''
sql statement: alter database rename file ''/home/oracle/app/oracle/oradata/ORCL/redo03.log'' to ''/home/oracle/temp/redo03.log''
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
sql statement: alter session set nls_date_format="YYYY-MM-DD HH24:MI:SS"
executing command: SET until clause
Starting restore at 16-DEC-15
Starting implicit crosscheck backup at 16-DEC-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
Crosschecked 10 objects
Finished implicit crosscheck backup at 16-DEC-15
Starting implicit crosscheck copy at 16-DEC-15
using channel ORA_DISK_1
Finished implicit crosscheck copy at 16-DEC-15
searching for all files in the recovery area
cataloging files...
no files cataloged
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 /home/oracle/temp/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/temp/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/temp/undotbs01..dbf
channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/temp/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/temp/example01..dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/rman/0bqp0em8_1_1_FULL_20151216
channel ORA_DISK_1: piece handle=/home/oracle/rman/0bqp0em8_1_1_FULL_20151216 tag=TAG20151216T233120
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 16-DEC-15
datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=898645343 file name=/home/oracle/temp/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=898645343 file name=/home/oracle/temp/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=898645343 file name=/home/oracle/temp/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=898645343 file name=/home/oracle/temp/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=898645343 file name=/home/oracle/temp/example01.dbf
RMAN> run
{
# set until time
sql 'alter session set nls_date_format="YYYY-MM-DD HH24:MI:SS"';
set until time = '2015-12-16 23:33:12';
# recover
recover database;
alter database open resetlogs;
}
-- 로그 (rtest05.dbf 자동 생성)
sql statement: alter session set nls_date_format="YYYY-MM-DD HH24:MI:SS"
executing command: SET until clause
Starting recover at 16-DEC-15
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 10 is already on disk as file /home/oracle/temp/redo01.log
archived log file name=/home/oracle/temp/redo01.log thread=1 sequence=10
creating datafile file number=6 name=/home/oracle/app/oracle/oradata/ORCL/rtest05.dbf
archived log file name=/home/oracle/temp/redo01.log thread=1 sequence=10
media recovery complete, elapsed time: 00:00:00
Finished recover at 16-DEC-15
database opened
SQL> @dd
TABLESPACE MB FILE_NAME
---------- ----- --------------------------------------------------
RTEST5 5 /home/oracle/app/oracle/oradata/ORCL/rtest05.dbf
EXAMPLE 100 /home/oracle/temp/example01.dbf
SYSAUX 530 /home/oracle/temp/sysaux01.dbf
SYSTEM 680 /home/oracle/temp/system01.dbf
UNDOTBS1 100 /home/oracle/temp/undotbs01.dbf
USERS 5 /home/oracle/temp/users01.dbf
6 rows selected.
SQL> select * from scott.tt340;
NO
----------
1
무정지 응급 복구
- drop table 장애, 운영 서버에서 무정지 응급 복구
RMAN> backup as compressed backupset database format '/home/oracle/rman/%U_FULL_%T';
SQL> create table scott.gogak (no number, name varchar2(10), addr varchar2(20));
SQL> insert into scott.gogak values (1, 'SEO', 'SEOUL');
SQL> insert into scott.gogak values (2, 'KIM', 'BUSAN');
SQL> insert into scott.gogak values (3, 'LEE', 'DAEJUN');
SQL> commit;
SQL> select * from scott.gogak;
NO NAME ADDR
---------- ---------- --------------------
1 SEO SEOUL
2 KIM BUSAN
3 LEE DAEJUN
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2015-12-16 23:51:16
SQL> drop table scott.gogak purge;
SQL> select * from scott.gogak;
select * from scott.gogak
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
[oracle@test11g ~]$ cp $ORACLE_HOME/dbs/initORCL.ora $ORACLE_HOME/dbs/initclone.ora
[oracle@test11g ~]$ vi $ORACLE_HOME/dbs/initclone.ora
[oracle@test11g ~]$ cat $ORACLE_HOME/dbs/initclone.ora
clone.__db_cache_size=104857600
clone.__java_pool_size=4194304
clone.__large_pool_size=4194304
clone.__oracle_base='/home/oracle/app/oracle'#ORACLE_BASE set from environment
clone.__pga_aggregate_target=146800640
clone.__sga_target=272629760
clone.__shared_io_pool_size=0
clone.__shared_pool_size=146800640
clone.__streams_pool_size=0
*.audit_file_dest='/home/oracle/app/oracle/admin/clone/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/home/oracle/clone/control01.ctl'
*.db_block_size=8192
*.db_domain='oracle.com'
*.db_name='clone'
*.db_recovery_file_dest='/home/oracle/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/home/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cloneXDB)'
*.log_archive_dest_1='LOCATION=/home/oracle/clone'
*.memory_target=416284672
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
db_file_name_convert=('/home/oracle/app/oracle/oradata/ORCL/', '/home/oracle/clone/')
log_file_name_convert=('/home/oracle/app/oracle/oradata/ORCL/', '/home/oracle/clone/')
[oracle@test11g ~]$ mkdir clone
[oracle@test11g ~]$ mkdir -p /home/oracle/app/oracle/admin/clone/adump
-- 아카이브 복사
[oracle@test11g ~]$ cp -av archive/* rman
`archive/1_10_799885128.dbf' -> `rman/1_10_799885128.dbf'
`archive/1_11_799885128.dbf' -> `rman/1_11_799885128.dbf'
`archive/1_12_799885128.dbf' -> `rman/1_12_799885128.dbf'
`archive/1_13_799885128.dbf' -> `rman/1_13_799885128.dbf'
`archive/1_14_799885128.dbf' -> `rman/1_14_799885128.dbf'
`archive/1_15_799885128.dbf' -> `rman/1_15_799885128.dbf'
`archive/1_16_799885128.dbf' -> `rman/1_16_799885128.dbf'
`archive/1_17_799885128.dbf' -> `rman/1_17_799885128.dbf'
`archive/1_8_799885128.dbf' -> `rman/1_8_799885128.dbf'
`archive/1_9_799885128.dbf' -> `rman/1_9_799885128.dbf'
-- 시간 기반 무정지 복구 - Targetless DUPLICATE 이용
[oracle@test11g ~]$ ps -ef | grep smon
oracle 17646 1 0 Dec16 ? 00:00:00 ora_smon_ORCL
[oracle@test11g ~]$ export ORACLE_SID=clone
[oracle@test11g ~]$ export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
[oracle@test11g ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 17 00:03:28 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 417546240 bytes
Fixed Size 2213936 bytes
Variable Size 301991888 bytes
Database Buffers 104857600 bytes
Redo Buffers 8482816 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@test11g ~]$ rman auxiliary /
RMAN> duplicate database to 'clone'
pfile='/home/oracle/app/oracle/product/11.2.0/dbs/initclone.ora'
nofilenamecheck
backup location '/home/oracle/rman'
until time "2015-12-16 23:51:16";2> 3> 4> 5>
Starting Duplicate Db at 2015-12-17 00:14:46
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/17/2015 00:14:46
RMAN-06136: ORACLE error from auxiliary database: ORA-01861: literal does not match format string
RMAN> run
{
set until time "to_date('2015-12-16 23:51:16','YYYY-MM-DD HH24:MI:SS')";
duplicate database to 'clone' pfile='/home/oracle/app/oracle/product/11.2..0/dbs/initclone.ora'
nofilenamecheck backup location '/home/oracle/rman';
}
[oracle@test11g ~]$ ps -ef | grep smon
oracle 17646 1 0 Dec16 ? 00:00:00 ora_smon_ORCL
oracle 18411 1 0 00:17 ? 00:00:00 ora_smon_clone
SQL> select * from scott.gogak;
NO NAME ADDR
---------- ---------- --------------------
1 SEO SEOUL
2 KIM BUSAN
3 LEE DAEJUN
[oracle@test11g ~]$ exp scott/tiger file=/home/oracle/temp/gogak.dmp tables=gogak
Export: Release 11.2.0.1.0 - Production on Thu Dec 17 00:21:29 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
.. . exporting table GOGAK 3 rows exported
Export terminated successfully without warnings.
[oracle@test11g ~]$ export ORACLE_SID=ORCL
[oracle@test11g ~]$ imp scott/tiger file=/home/oracle/temp/gogak.dmp ignore=y
Import: Release 11.2.0.1.0 - Production on Thu Dec 17 00:22:09 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
.. importing SCOTT's objects into SCOTT
.. importing SCOTT's objects into SCOTT
.. . importing table "GOGAK" 3 rows imported
Import terminated successfully without warnings.
[oracle@test11g ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 17 00:22:22 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from scott.gogak;
NO NAME ADDR
---------- ---------- --------------------
1 SEO SEOUL
2 KIM BUSAN
3 LEE DAEJUN
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
ORCL
11.15 RMAN으로 Raw Device 백업과 복구하기
@dd.sql
set line 200
col tablespace_name for a10
col file_name for a45
select tablespace_name, bytes/1024/1024 mb, file_name from dba_data_files;
@log.sql
set line 200
col group# for 999
col member for a20
col mb for 999
col seq# for 999
col archived for a4
col status for a8
select a.group#, a.member, b.bytes/024/1024 mb, b.sequence# seq#, b.archived, b.status
from v$logfile a, v$log b
where a.group# = b.group#
order by 1, 2;
RMAN BackupSet 형태 백업
@dd
@log
select name from v$controlfile;
archive log list;
-- 백업
rman target /
backup as compressed backupset format '/home/oracle/rman/%U_%T' database;
ls -lSh /home/oracle/rman
-- 테이블 장애 유발
sqlplus scott/tiger
create table gogak (no number, name varchar2(10)) tablespace users;
insert into gogak values (1, 'KIM');
insert into gogak values (2, 'LEE');
commit;
select * from gogak;
conn / as sysdba
@dd
!dd if=/dev/zero of=/dev/raw/raw4 bs=8k
alter tablespace users offline immediate;
alter tablespace users online;
select * from scott.gogak;
-- 복구
rman target /
run
{
sql 'alter tablespace users offline immediate';
restore tablespace users;
recover tablespace users;
sql 'alter tablespace users online';
}
-- 확인
sqlplus scott/tiger
select * from gogak;
RMAN Rawdevice 형태 백업
-- 백업
report schema;
copy
datafile 1 to '/home/oracle/rman/system.raw',
datafile 2 to '/home/oracle/rman/undotbs1.raw',
datafile 3 to '/home/oracle/rman/sysaux.raw',
datafile 4 to '/home/oracle/rman/users.raw',
datafile 5 to '/home/oracle/rman/example.raw';
ls -lSh /home/oracle/rman
-- 테이블 장애 유발
create table scott.gogak (no number, name varchar2(10)) tablespace users;
insert into scott.gogak values (1, 'KIM');
insert into scott.gogak values (2, 'LEE');
commit;
select * from scott.gogak;
conn / as sysdba
@dd
!dd if=/dev/zero of=/dev/raw/raw4 bs=8k
alter tablespace users offline immediate;
alter tablespace users online;
select * from scott.gogak;
-- 복구
restore tablespace users;
recover tablespace users;
alter tablespace users online;
select * from scott.gogak;
11.16 RMAN으로 Block Corruption Recovery 수행하기
- ORA-01578 ORACLE data block corrupted
- 백업을 활용해 DBF를 복원/복구 수행 (10g)
- RMAN 의 Block Recovery 기능 활용 (11g)
-- 백업
rman target sys/oracle
backup as compressed backupset database format '/home/oracle/rman/%U_%T';
-- 장애 유발
SQL> @dd
TABLESPACE MB FILE_NAME
---------- ----- --------------------------------------------------
EXAMPLE 100 /home/oracle/app/oracle/oradata/ORCL/example01.dbf
SYSAUX 530 /home/oracle/app/oracle/oradata/ORCL/sysaux01.dbf
SYSTEM 680 /home/oracle/app/oracle/oradata/ORCL/system01.dbf
UNDOTBS1 100 /home/oracle/app/oracle/oradata/ORCL/undotbs01.dbf
USERS 5 /home/oracle/app/oracle/oradata/ORCL/users01.dbf
SQL> !dd if=/dev/zero of=/home/oracle/app/oracle/oradata/ORCL/users01.dbf bs=8k conv=notrunc seek=10 count=1
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 7.554e-05 seconds, 108 MB/s
SQL> alter system checkpoint;
System altered.
SQL> alter tablespace users offline;
Tablespace altered.
SQL> alter tablespace users online;
Tablespace altered.
-- alert.log
Thu Dec 17 08:14:27 2015
alter tablespace users offline
Completed: alter tablespace users offline
alter tablespace users online
Completed: alter tablespace users online
-- 확인
[oracle@test11g ~]$ dbv file=/home/oracle/app/oracle/oradata/ORCL/users01.dbf blocksize=8192
DBVERIFY: Release 11.2.0.1.0 - Production on Thu Dec 17 08:16:34 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /home/oracle/app/oracle/oradata/ORCL/users01.dbf
Page 10 is marked corrupt
Corrupt block relative dba: 0x0100000a (file 4, block 10)
Completely zero block found during dbv:
DBVERIFY - Verification complete
Total Pages Examined : 640
Total Pages Processed (Data) : 91
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 39
Total Pages Failing (Index): 0
Total Pages Processed (Other): 317
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 192
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 1037912 (0.1037912)
SQL> select * from v$database_block_corruption;
no rows selected
RMAN> backup validate tablespace users;
Starting backup at 17-DEC-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=192 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=00004 name=/home/oracle/app/oracle/oradata/ORCL/users01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 FAILED 0 255 640 1037912
File Name: /home/oracle/app/oracle/oradata/ORCL/users01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 91
Index 0 39
Other 1 255
validate found one or more corrupt blocks
See trace file /home/oracle/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_23034.trc for details
Finished backup at 17-DEC-15
-- alert.log
Thu Dec 17 08:17:51 2015
Hex dump of (file 4, block 10) in trace file /home/oracle/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_23034.trc
Corrupt block relative dba: 0x0100000a (file 4, block 10)
Completely zero block found during validation
Reread of blocknum=10, file=/home/oracle/app/oracle/oradata/ORCL/users01.dbf. found same corrupt data
Reread of blocknum=10, file=/home/oracle/app/oracle/oradata/ORCL/users01.dbf. found same corrupt data
Reread of blocknum=10, file=/home/oracle/app/oracle/oradata/ORCL/users01.dbf. found same corrupt data
Reread of blocknum=10, file=/home/oracle/app/oracle/oradata/ORCL/users01.dbf. found same corrupt data
Reread of blocknum=10, file=/home/oracle/app/oracle/oradata/ORCL/users01.dbf. found same corrupt data
Checker run found 1 new persistent data failures
SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
4 10 1 0 ALL ZERO
-- 복구#1
rman target sys/oracle
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 680 SYSTEM *** /home/oracle/app/oracle/oradata/ORCL/system01.dbf
2 530 SYSAUX *** /home/oracle/app/oracle/oradata/ORCL/sysaux01.dbf
3 100 UNDOTBS1 *** /home/oracle/app/oracle/oradata/ORCL/undotbs01.dbf
4 5 USERS *** /home/oracle/app/oracle/oradata/ORCL/users01.dbf
5 100 EXAMPLE *** /home/oracle/app/oracle/oradata/ORCL/example01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 29 TEMP 32767 /home/oracle/app/oracle/oradata/ORCL/temp01.dbf
RMAN> recover datafile 4 block 10;
Starting recover at 17-DEC-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=191 device type=DISK
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: reading from backup piece /home/oracle/rman/03qp1cpp_1_1_20151217
channel ORA_DISK_1: piece handle=/home/oracle/rman/03qp1cpp_1_1_20151217 tag=TAG20151217T080513
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished recover at 17-DEC-15
-- alert.log
Thu Dec 17 08:19:49 2015
alter database recover datafile list clear
Completed: alter database recover datafile list clear
Started Block Media Recovery
Recovery of Online Redo Log: Thread 1 Group 3 Seq 9 Reading mem 0
Mem# 0: /home/oracle/app/oracle/oradata/ORCL/redo03.log
Completed Block Media Recovery
SQL> select * from v$database_block_corruption;
no rows selected
-- 복구#2
RMAN> list failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
161 HIGH OPEN 17-DEC-15 Datafile 4: '/home/oracle/app/oracle/oradata/ORCL/users01.dbf' contains one or more corrupt blocks
RMAN> recover corruption list;
Starting recover at 17-DEC-15
using channel ORA_DISK_1
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: reading from backup piece /home/oracle/rman/03qp1cpp_1_1_20151217
channel ORA_DISK_1: piece handle=/home/oracle/rman/03qp1cpp_1_1_20151217 tag=TAG20151217T080513
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished recover at 17-DEC-15
RMAN> backup validate tablespace users;
Starting backup at 17-DEC-15
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=/home/oracle/app/oracle/oradata/ORCL/users01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 OK 0 255 640 1037912
File Name: /home/oracle/app/oracle/oradata/ORCL/users01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 91
Index 0 39
Other 0 255
Finished backup at 17-DEC-15
11.17 Control file 재생성 시 대처 방법
- Control file 에 저장 된 RMAN Recovery Catalog 가 손상 된 경우 Recover Catalog 사용
- RMAN 백업 파일에서 필요한 정보를 추출해 Catalog 에 등록
RMAN> backup as compressed backupset database format '/home/oracle/rman/%U_%T';
RMAN> list backupset;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 281.43M DISK 00:00:26 17-DEC-15
BP Key: 4 Status: AVAILABLE Compressed: YES Tag: TAG20151217T082406
Piece Name: /home/oracle/rman/09qp1dt6_1_1_20151217
List of Datafiles in backup set 4
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1082048 17-DEC-15 /home/oracle/app/oracle/oradata/ORCL/system01.dbf
2 Full 1082048 17-DEC-15 /home/oracle/app/oracle/oradata/ORCL/sysaux01.dbf
3 Full 1082048 17-DEC-15 /home/oracle/app/oracle/oradata/ORCL/undotbs01.dbf
4 Full 1082048 17-DEC-15 /home/oracle/app/oracle/oradata/ORCL/users01.dbf
5 Full 1082048 17-DEC-15 /home/oracle/app/oracle/oradata/ORCL/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 1.03M DISK 00:00:01 17-DEC-15
BP Key: 5 Status: AVAILABLE Compressed: YES Tag: TAG20151217T082406
Piece Name: /home/oracle/rman/0aqp1du9_1_1_20151217
SPFILE Included: Modification time: 17-DEC-15
SPFILE db_unique_name: ORCL
Control File Included: Ckp SCN: 1082843 Ckp time: 17-DEC-15
[oracle@test11g ~]$ ls rman
09qp1dt6_1_1_20151217 0aqp1du9_1_1_20151217
[oracle@test11g ~]$ mv rman/* ./temp
[oracle@test11g ~]$ ls rman
[oracle@test11g ~]$
RMAN> crosscheck backupset;
using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/home/oracle/rman/09qp1dt6_1_1_20151217 RECID=4 STAMP=898676646
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/home/oracle/rman/0aqp1du9_1_1_20151217 RECID=5 STAMP=898676682
Crosschecked 2 objects
RMAN> delete backupset;
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
4 4 1 1 EXPIRED DISK /home/oracle/rman/09qp1dt6_1_1_20151217
5 5 1 1 EXPIRED DISK /home/oracle/rman/0aqp1du9_1_1_20151217
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/home/oracle/rman/09qp1dt6_1_1_20151217 RECID=4 STAMP=898676646
deleted backup piece
backup piece handle=/home/oracle/rman/0aqp1du9_1_1_20151217 RECID=5 STAMP=898676682
Deleted 2 objects
RMAN> list backupset;
specification does not match any backup in the repository
[oracle@test11g ~]$ mv temp/* ./rman
[oracle@test11g ~]$ ls rman
09qp1dt6_1_1_20151217 0aqp1du9_1_1_20151217
RMAN> catalog start with '/home/oracle/rman';
using target database control file instead of recovery catalog
searching for all files that match the pattern /home/oracle/rman
List of Files Unknown to the Database
=====================================
File Name: /home/oracle/rman/09qp1dt6_1_1_20151217
File Name: /home/oracle/rman/0aqp1du9_1_1_20151217
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /home/oracle/rman/09qp1dt6_1_1_20151217
File Name: /home/oracle/rman/0aqp1du9_1_1_20151217
RMAN> list backupset;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6 Full 281.43M DISK 00:00:00 17-DEC-15
BP Key: 6 Status: AVAILABLE Compressed: YES Tag: TAG20151217T082406
Piece Name: /home/oracle/rman/09qp1dt6_1_1_20151217
List of Datafiles in backup set 6
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1082048 17-DEC-15 /home/oracle/app/oracle/oradata/ORCL/system01.dbf
2 Full 1082048 17-DEC-15 /home/oracle/app/oracle/oradata/ORCL/sysaux01.dbf
3 Full 1082048 17-DEC-15 /home/oracle/app/oracle/oradata/ORCL/undotbs01.dbf
4 Full 1082048 17-DEC-15 /home/oracle/app/oracle/oradata/ORCL/users01.dbf
5 Full 1082048 17-DEC-15 /home/oracle/app/oracle/oradata/ORCL/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7 Full 1.03M DISK 00:00:00 17-DEC-15
BP Key: 7 Status: AVAILABLE Compressed: YES Tag: TAG20151217T082406
Piece Name: /home/oracle/rman/0aqp1du9_1_1_20151217
SPFILE Included: Modification time: 17-DEC-15
SPFILE db_unique_name: ORCL
Control File Included: Ckp SCN: 1082843 Ckp time: 17-DEC-15
11.18 RMAN 관련 주요 환경 변수 정리
| 환경변수 | 의미 | 기본값 | 비고 |
|---|
| configure retention policy to recovery window of 1 days; | 백업본 보존 기간 정의 | | delete obsolete; 수행 시 1일 지난 백업 본 삭제 됨 |
|---|
| configure retention policy to redundancy 1; | 손상에 대비한 백업본 다중화 설정 | | **2결과** |
|---|
| configure device type disk parallelism 2; | 기본 Channel 병렬도 설정 | | 백업시 프로세스 2개 생성 |
|---|
| configure controlfile autobackup on; | 백업 시 Control file 자동 백업 여부 | | configure controlfile autobackup format for device type disk to '/home/oracle/rman/cf_%F'; |
|---|
| configure maxsetsize to unlimited; | BackupSet 최대 크기 무제한 설정 | | |
|---|
| configure snapshot controlfile name to '/home/oracle/open/%F'; | Control file 백업 수행중 내용 변경시 스냅샷 생성 | | |
|---|
| configure backup optimization on; | 백업본 존재시 생략 | off | DBID, Checkpoint/Creation/Resetlogs SCN & Time 으로 비교 |
|---|
| configure channel device type disk maxpiecesize 50m; | 백업 파일 하나의 최대 크기 설정 | | |
|---|
| show all; | 모든 설정 표시 | | |
|---|
11.19 Data Recovery Advisor(11g New Feature)
- 장애 해결에 도움을 주는 기능
- GUI, CLI 제공
SQL> create tablespace ts_b datafile '/home/oracle/app/oracle/oradata/ORCL/ts_b01.dbf' size 10m;
Tablespace created.
SQL> alter tablespace ts_b offline;
Tablespace altered.
SQL> @dd
TABLESPACE MB FILE_NAME
---------- ----- --------------------------------------------------
EXAMPLE 100 /home/oracle/app/oracle/oradata/ORCL/example01.dbf
SYSAUX 530 /home/oracle/app/oracle/oradata/ORCL/sysaux01.dbf
SYSTEM 680 /home/oracle/app/oracle/oradata/ORCL/system01.dbf
TS_B /home/oracle/app/oracle/oradata/ORCL/ts_b01.dbf
UNDOTBS1 100 /home/oracle/app/oracle/oradata/ORCL/undotbs01.dbf
USERS 5 /home/oracle/app/oracle/oradata/ORCL/users01.dbf
6 rows selected.
SQL> !rm -f /home/oracle/app/oracle/oradata/ORCL/ts_b01.dbf
SQL> !ls -l /home/oracle/app/oracle/oradata/ORCL/ts_b01.dbf
ls: /home/oracle/app/oracle/oradata/ORCL/ts_b01.dbf: No such file or directory
SQL> alter tablespace ts_b online;
alter tablespace ts_b online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/home/oracle/app/oracle/oradata/ORCL/ts_b01.dbf'
RMAN> list failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
274 HIGH OPEN 17-DEC-15 Tablespace 7: 'TS_B' is offline
268 HIGH OPEN 17-DEC-15 One or more non-system datafiles are missing
262 HIGH OPEN 17-DEC-15 One or more non-system datafiles are offline
RMAN> list failure 268 detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
268 HIGH OPEN 17-DEC-15 One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 268
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
271 HIGH OPEN 17-DEC-15 Datafile 6: '/home/oracle/app/oracle/oradata/ORCL/ts_b01.dbf' is missing
Impact: Some objects in tablespace TS_B might be unavailable
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
268 HIGH OPEN 17-DEC-15 One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 268
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
271 HIGH OPEN 17-DEC-15 Datafile 6: '/home/oracle/app/oracle/oradata/ORCL/ts_b01.dbf' is missing
Impact: Some objects in tablespace TS_B might be unavailable
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=71 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file /home/oracle/app/oracle/oradata/ORCL/ts_b01.dbf was unintentionally renamed or moved, restore it
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 6
Strategy: The repair includes complete media recovery with no data loss
Repair script: /home/oracle/app/oracle/diag/rdbms/orcl/ORCL/hm/reco_1729683115.hm
RMAN> repair failure preview;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /home/oracle/app/oracle/diag/rdbms/orcl/ORCL/hm/reco_1729683115.hm
contents of repair script:
# restore and recover datafile
restore datafile 6;
recover datafile 6;
[oracle@test11g ~]$ cat /home/oracle/app/oracle/diag/rdbms/orcl/ORCL/hm/reco_1729683115.hm
# restore and recover datafile
restore datafile 6;
recover datafile 6;
-- Data Recovery Advisor 권고에 따름
RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /home/oracle/app/oracle/diag/rdbms/orcl/ORCL/hm/reco_2513849271.hm
contents of repair script:
# restore and recover datafile
restore datafile 6;
recover datafile 6;
Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script
Starting restore at 17-DEC-15
using channel ORA_DISK_1
creating datafile file number=6 name=/home/oracle/app/oracle/oradata/ORCL/ts_b01.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 17-DEC-15
Starting recover at 17-DEC-15
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 17-DEC-15
repair failure complete
-- alert.log
Thu Dec 17 08:33:25 2015
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover if needed
datafile 6
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 3 Seq 9 Reading mem 0
Mem# 0: /home/oracle/app/oracle/oradata/ORCL/redo03.log
Media Recovery Complete (ORCL)
Completed: alter database recover if needed
datafile 6
RMAN> list failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
274 HIGH OPEN 17-DEC-15 Tablespace 7: 'TS_B' is offline
262 HIGH OPEN 17-DEC-15 One or more non-system datafiles are offline
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
274 HIGH OPEN 17-DEC-15 Tablespace 7: 'TS_B' is offline
262 HIGH OPEN 17-DEC-15 One or more non-system datafiles are offline
analyzing automatic repair options; this may take some time
using channel ORA_DISK_1
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
no manual actions available
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Online tablespace TS_B
Strategy: The repair includes complete media recovery with no data loss
Repair script: /home/oracle/app/oracle/diag/rdbms/orcl/ORCL/hm/reco_509626531.hm
RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /home/oracle/app/oracle/diag/rdbms/orcl/ORCL/hm/reco_509626531.hm
contents of repair script:
# online a offline tablespace
sql "begin sys.dbms_ir.execsqlscript(filename => ''/home/oracle/app/oracle/diag/rdbms/orcl/ORCL/hm/reco_2212630940.hm'' ); end;";
Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script
sql statement: begin sys.dbms_ir.execsqlscript(filename => ''/home/oracle/app/oracle/diag/rdbms/orcl/ORCL/hm/reco_2212630940.hm'' ); end;
repair failure complete
-- alert.log
Thu Dec 17 08:34:20 2015
ALTER TABLESPACE TS_B ONLINE
Completed: ALTER TABLESPACE TS_B ONLINE
RMAN> list failure;
no failures found that match specification
11.20 Database 사전 예방 점검 기능(11g New Feature)
- 장애 발생 전 미리 점검 (Bad Block 등)
-- 모든 파일 검사
RMAN> validate database;
Starting validate at 17-DEC-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=/home/oracle/app/oracle/oradata/ORCL/system01.dbf
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/ORCL/sysaux01.dbf
input datafile file number=00003 name=/home/oracle/app/oracle/oradata/ORCL/undotbs01.dbf
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/ORCL/example01.dbf
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/ORCL/ts_b01.dbf
input datafile file number=00004 name=/home/oracle/app/oracle/oradata/ORCL/users01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 OK 0 12698 87049 1084638
File Name: /home/oracle/app/oracle/oradata/ORCL/system01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 58634
Index 0 12211
Other 0 3497
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2 OK 0 20788 67862 1084636
File Name: /home/oracle/app/oracle/oradata/ORCL/sysaux01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 11835
Index 0 8143
Other 0 27074
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3 OK 0 1 12800 1084638
File Name: /home/oracle/app/oracle/oradata/ORCL/undotbs01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 12799
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 OK 0 255 640 1037912
File Name: /home/oracle/app/oracle/oradata/ORCL/users01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 91
Index 0 39
Other 0 255
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5 OK 0 1689 12804 1044091
File Name: /home/oracle/app/oracle/oradata/ORCL/example01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 6587
Index 0 1261
Other 0 3263
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6 OK 0 1153 1280 1083388
File Name: /home/oracle/app/oracle/oradata/ORCL/ts_b01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 127
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
including current SPFILE in backup set
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE OK 0 2
Control File OK 0 594
Finished validate at 17-DEC-15
-- 특정 TS 검사
RMAN> validate tablespace ts_b;
Starting validate at 17-DEC-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/ORCL/ts_b01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6 OK 0 1153 1280 1083388
File Name: /home/oracle/app/oracle/oradata/ORCL/ts_b01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 127
Finished validate at 17-DEC-15
-- 특정 DBF 검사
RMAN> validate datafile 6;
Starting validate at 17-DEC-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/ORCL/ts_b01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6 OK 0 1153 1280 1083388
File Name: /home/oracle/app/oracle/oradata/ORCL/ts_b01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 127
Finished validate at 17-DEC-15
-- 특정 블록 검사
RMAN> validate datafile 6 block 1;
Starting validate at 17-DEC-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/ORCL/ts_b01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:02
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6 OK 0 0 1 0
File Name: /home/oracle/app/oracle/oradata/ORCL/ts_b01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 1
Finished validate at 17-DEC-15
11.21 RMAN과 Datapump를 사용하여 데이터 이동하기
- 데이터 이전 작업은 OS 종류, DB 버전 차이가 있는 경우 어려워짐
- RMAN 의 convert 명령어 활용 가능
- 순서
- SDB 플랫폼 확인
- SDB 대상 TS 검사 (DBMS_TTS)
- SDB 대상 TS RO 설정
- 대상 TS expdp#1 (transport_tablespaces 옵션)
- 대상 TS expdp#2 (full=y, include=users,role,role_grant,profile, content=metadata_only 옵션)
- TDB 플랫폼 확인 후 SDB에서 RMAN> convert 수행
- TDB 에서 RMAN> convert 수행
- TDB 에서 impdp#1 (expdp#2) 수행
- TDB 에서 impdp#2 (expdp#1) 수행 (transport_datafiles 옵션)
- TDB 대상 TS RW 설정
SQL> @endian
PLATFORM_ID PLATFORM_NAME ENDIAN_FOR
-------------- ---------------------------------------- ----------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
3 HP-UX (64-bit) Big
4 HP-UX IA (64-bit) Big
5 HP Tru64 UNIX Little
6 AIX-Based Systems (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
8 Microsoft Windows IA (64-bit) Little
9 IBM zSeries Based Linux Big
10 Linux IA (32-bit) Little
11 Linux IA (64-bit) Little
12 Microsoft Windows x86 64-bit Little
13 Linux x86 64-bit Little
15 HP Open VMS Little
16 Apple Mac OS Big
17 Solaris Operating System (x86) Little
18 IBM Power Based Linux Big
19 HP IA Open VMS Little
20 Solaris Operating System (x86-64) Little
21 Apple Mac OS (x86-64) Little
20 rows selected.
RHEL5 + 11gR2/64bit → RHEL5 + 11gR2/64bit 데이터 이동
-- source db
sqlplus / as sysdba
col platform_name for a20
col endian_format for a20
SQL> select d.platform_name, endian_format
from v$transportable_platform tp, v$database d
where tp.platform_name = d.platform_name;
2 3
PLATFORM_NAME ENDIAN_FORMAT
-------------------- --------------------
Linux x86 64-bit Little
create tablespace conv_test datafile '/home/oracle/app/oracle/oradata/ORCL/conv_test01.dbf' size 10m;
create table scott.ctest1 (no number) tablespace conv_test;
insert into scott.ctest1 values (1);
insert into scott.ctest1 values (2);
commit;
select * from scott.ctest1;
-- 준비(DBMS_TTS 점검)
-- Checks if a set of tablespaces (to be transported) is self-contained
SQL> exec dbms_tts.transport_set_check('CONV_TEST', true);
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
no rows selected
-- Datapump
!mkdir /home/oracle/dp_conv
create directory dp_conv as '/home/oracle/dp_conv';
grant read,write on directory dp_conv to system;
alter tablespace conv_test read only;
[oracle@test11g ~]$ expdp system/*** dumpfile=conv.dmp directory=dp_conv transport_tablespaces=conv_test logfile=conv.log
Export: Release 11.2.0.1.0 - Production on Thu Dec 17 08:45:46 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
UDE-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=conv.dmp directory=dp_conv transport_tablespaces=conv_test logfile=conv.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/home/oracle/dp_conv/conv.dmp
******************************************************************************
Datafiles required for transportable tablespace CONV_TEST:
/home/oracle/app/oracle/oradata/ORCL/conv_test01.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 08:46:13
[oracle@test11g ~]$ expdp system/*** dumpfile=conv_meta.dmp directory=dp_conv full=y include=user,role,role_grant,profile content=metadata_only
Export: Release 11.2.0.1.0 - Production on Thu Dec 17 08:46:37 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
UDE-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** dumpfile=conv_meta.dmp directory=dp_conv full=y include=user,role,role_grant,profile content=metadata_only
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
/home/oracle/dp_conv/conv_meta.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at 08:46:41
-- source db
[oracle@test11g dp_conv]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Dec 17 08:47:40 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1327665863)
RMAN> convert tablespace 'CONV_TEST' to platform 'Linux IA (64-bit)' format '/home/oracle/dp_conv/%U';
Starting conversion at source at 17-DEC-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00007 name=/home/oracle/app/oracle/oradata/ORCL/conv_test01.dbf
converted datafile=/home/oracle/dp_conv/data_D-ORCL_I-1327665863_TS-CONV_TEST_FNO-7_0kqp1f9q
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:02
Finished conversion at source at 17-DEC-15
-- target db
-- mkdir /home/oracle/dp_conv
-- scp oracle@*** /home/oracle/dp_conv
-- sqlplus sys/oracle as sysdba
-- grant read,write on directory dp_conv to system;
drop tablespace conv_test including contents and datafiles;
RMAN> convert datafile '/home/oracle/dp_conv/data_D-ORCL_I-1327665863_TS-CONV_TEST_FNO-7_0kqp1f9q' format '/home/oracle/app/oracle/oradata/ORCL/conv_test01.dbf';
Starting conversion at target at 17-DEC-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/home/oracle/dp_conv/data_D-ORCL_I-1327665863_TS-CONV_TEST_FNO-7_0kqp1f9q
converted datafile=/home/oracle/app/oracle/oradata/ORCL/conv_test01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 17-DEC-15
RMAN> report schema;
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 680 SYSTEM *** /home/oracle/app/oracle/oradata/ORCL/system01.dbf
2 530 SYSAUX *** /home/oracle/app/oracle/oradata/ORCL/sysaux01.dbf
3 100 UNDOTBS1 *** /home/oracle/app/oracle/oradata/ORCL/undotbs01.dbf
4 5 USERS *** /home/oracle/app/oracle/oradata/ORCL/users01.dbf
5 100 EXAMPLE *** /home/oracle/app/oracle/oradata/ORCL/example01.dbf
6 10 TS_B *** /home/oracle/app/oracle/oradata/ORCL/ts_b01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 29 TEMP 32767 /home/oracle/app/oracle/oradata/ORCL/temp01.dbf
[oracle@test11g dp_conv]$ impdp system/*** directory=dp_conv dumpfile=conv_meta.dmp full=y
Import: Release 11.2.0.1.0 - Production on Thu Dec 17 08:50:13 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
UDI-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=dp_conv dumpfile=conv_meta.dmp full=y
Processing object type DATABASE_EXPORT/PROFILE
ORA-31684: Object type PROFILE:"MONITORING_PROFILE" already exists
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
ORA-31684: Object type USER:"OUTLN" already exists
ORA-31684: Object type USER:"ORDDATA" already exists
ORA-31684: Object type USER:"OLAPSYS" already exists
ORA-31684: Object type USER:"MDDATA" already exists
ORA-31684: Object type USER:"SPATIAL_WFS_ADMIN_USR" already exists
ORA-31684: Object type USER:"SPATIAL_CSW_ADMIN_USR" already exists
ORA-31684: Object type USER:"SYSMAN" already exists
ORA-31684: Object type USER:"MGMT_VIEW" already exists
ORA-31684: Object type USER:"FLOWS_FILES" already exists
ORA-31684: Object type USER:"APEX_PUBLIC_USER" already exists
ORA-31684: Object type USER:"APEX_030200" already exists
ORA-31684: Object type USER:"OWBSYS" already exists
ORA-31684: Object type USER:"OWBSYS_AUDIT" already exists
ORA-31684: Object type USER:"SCOTT" already exists
ORA-31684: Object type USER:"HR" already exists
ORA-31684: Object type USER:"OE" already exists
ORA-31684: Object type USER:"IX" already exists
ORA-31684: Object type USER:"SH" already exists
ORA-31684: Object type USER:"PM" already exists
ORA-31684: Object type USER:"BI" already exists
Processing object type DATABASE_EXPORT/ROLE
ORA-31684: Object type ROLE:"SELECT_CATALOG_ROLE" already exists
ORA-31684: Object type ROLE:"EXECUTE_CATALOG_ROLE" already exists
ORA-31684: Object type ROLE:"DELETE_CATALOG_ROLE" already exists
ORA-31684: Object type ROLE:"DBFS_ROLE" already exists
ORA-31684: Object type ROLE:"AQ_ADMINISTRATOR_ROLE" already exists
ORA-31684: Object type ROLE:"AQ_USER_ROLE" already exists
ORA-31684: Object type ROLE:"ADM_PARALLEL_EXECUTE_TASK" already exists
ORA-31684: Object type ROLE:"GATHER_SYSTEM_STATISTICS" already exists
ORA-31684: Object type ROLE:"RECOVERY_CATALOG_OWNER" already exists
ORA-31684: Object type ROLE:"SCHEDULER_ADMIN" already exists
ORA-31684: Object type ROLE:"HS_ADMIN_SELECT_ROLE" already exists
ORA-31684: Object type ROLE:"HS_ADMIN_EXECUTE_ROLE" already exists
ORA-31684: Object type ROLE:"HS_ADMIN_ROLE" already exists
ORA-31684: Object type ROLE:"GLOBAL_AQ_USER_ROLE" already exists
ORA-31684: Object type ROLE:"OEM_ADVISOR" already exists
ORA-31684: Object type ROLE:"OEM_MONITOR" already exists
ORA-31684: Object type ROLE:"WM_ADMIN_ROLE" already exists
ORA-31684: Object type ROLE:"JAVAUSERPRIV" already exists
ORA-31684: Object type ROLE:"JAVAIDPRIV" already exists
ORA-31684: Object type ROLE:"JAVASYSPRIV" already exists
ORA-31684: Object type ROLE:"JAVADEBUGPRIV" already exists
ORA-31684: Object type ROLE:"EJBCLIENT" already exists
ORA-31684: Object type ROLE:"JMXSERVER" already exists
ORA-31684: Object type ROLE:"JAVA_ADMIN" already exists
ORA-31684: Object type ROLE:"JAVA_DEPLOY" already exists
ORA-31684: Object type ROLE:"CTXAPP" already exists
ORA-31684: Object type ROLE:"XDBADMIN" already exists
ORA-31684: Object type ROLE:"XDB_SET_INVOKER" already exists
ORA-31684: Object type ROLE:"AUTHENTICATEDUSER" already exists
ORA-31684: Object type ROLE:"XDB_WEBSERVICES" already exists
ORA-31684: Object type ROLE:"XDB_WEBSERVICES_WITH_PUBLIC" already exists
ORA-31684: Object type ROLE:"XDB_WEBSERVICES_OVER_HTTP" already exists
ORA-31684: Object type ROLE:"ORDADMIN" already exists
ORA-31684: Object type ROLE:"OLAPI_TRACE_USER" already exists
ORA-31684: Object type ROLE:"OLAP_XS_ADMIN" already exists
ORA-31684: Object type ROLE:"OLAP_DBA" already exists
ORA-31684: Object type ROLE:"CWM_USER" already exists
ORA-31684: Object type ROLE:"OLAP_USER" already exists
ORA-31684: Object type ROLE:"SPATIAL_WFS_ADMIN" already exists
ORA-31684: Object type ROLE:"WFS_USR_ROLE" already exists
ORA-31684: Object type ROLE:"SPATIAL_CSW_ADMIN" already exists
ORA-31684: Object type ROLE:"CSW_USR_ROLE" already exists
ORA-31684: Object type ROLE:"MGMT_USER" already exists
ORA-31684: Object type ROLE:"APEX_ADMINISTRATOR_ROLE" already exists
ORA-31684: Object type ROLE:"OWB$CLIENT" already exists
ORA-31684: Object type ROLE:"OWB_DESIGNCENTER_VIEW" already exists
ORA-31684: Object type ROLE:"OWB_USER" already exists
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 68 error(s) at 08:50:23
SQL> @dd
TABLESPACE MB FILE_NAME
---------- ----- --------------------------------------------------
EXAMPLE 100 /home/oracle/app/oracle/oradata/ORCL/example01.dbf
SYSAUX 530 /home/oracle/app/oracle/oradata/ORCL/sysaux01.dbf
SYSTEM 680 /home/oracle/app/oracle/oradata/ORCL/system01.dbf
TS_B 10 /home/oracle/app/oracle/oradata/ORCL/ts_b01.dbf
UNDOTBS1 100 /home/oracle/app/oracle/oradata/ORCL/undotbs01.dbf
USERS 5 /home/oracle/app/oracle/oradata/ORCL/users01.dbf
6 rows selected.
[oracle@test11g ~]$ impdp system/*** directory=dp_conv dumpfile=conv.dmp transport_datafiles='/home/oracle/app/oracle/oradata/ORCL/conv_test01.dbf';
Import: Release 11.2.0.1.0 - Production on Thu Dec 17 08:51:53 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=dp_conv dumpfile=conv.dmp transport_datafiles=/home/oracle/app/oracle/oradata/ORCL/conv_test01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 08:51:55
[oracle@test11g ~]$
SQL> @dd
TABLESPACE MB FILE_NAME
---------- ----- --------------------------------------------------
CONV_TEST 10 /home/oracle/app/oracle/oradata/ORCL/conv_test01.dbf
EXAMPLE 100 /home/oracle/app/oracle/oradata/ORCL/example01.dbf
SYSAUX 530 /home/oracle/app/oracle/oradata/ORCL/sysaux01.dbf
SYSTEM 680 /home/oracle/app/oracle/oradata/ORCL/system01.dbf
TS_B 10 /home/oracle/app/oracle/oradata/ORCL/ts_b01.dbf
UNDOTBS1 100 /home/oracle/app/oracle/oradata/ORCL/undotbs01.dbf
USERS 5 /home/oracle/app/oracle/oradata/ORCL/users01.dbf
7 rows selected.
SQL> select * from scott.ctest1;
NO
----------
1
2
SQL> insert into scott.ctest1 values (3);
insert into scott.ctest1 values (3)
*
ERROR at line 1:
ORA-00372: file 7 cannot be modified at this time
ORA-01110: data file 7: '/home/oracle/app/oracle/oradata/ORCL/conv_test01.dbf'
SQL> alter tablespace conv_test read write;
Tablespace altered.
SQL> insert into scott.ctest1 values (3);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from scott.ctest1;
NO
----------
1
2
3
11.22 12c RMAN New Features
SYSBACKUP 권한
sqlplus / as sysbackup;
select privilege from dba_sys_privs where grantee = 'SYSBACKUP';
RMAN 에서 SQL 실행 가능
-- 11g
rman target sys/oracle
select sysdate from dual;
-- 12c
rman target sys/oracle
select sysdate from dual;
select tablespace_name, bytes/1024/1024 mb, file_name from dba_data_files;
H4. MultiSection Backup 기능 향상
- 11g 까지는 BackupSet 백업 시 가능
- 12c 부터는 Image Copy, Incremental Backup 백업 시 가능
-- 12c
rman target /
backup as compressed backupset incremental level 1 section size 100m format '/home/oracle/rman/%U_%T' database;
- 11g : 이기종 DBF 전송시 Image Copy 만 가능, TS Read Only 필수
- 12c : 이기종 DBF 전송시 BackupSet 가능, TS Read Only 선택
- 용량이 줄어듦 (공간, 속도)
- 옵션
- FOR TRANSPORT : 데이터 전송을 목적으로 BackupSet 생성, 백업 시 필요에 따라 Endian 변환
- DATAPUMP : 백업 파일이 Datapump Dump File 로 생성 됨
Recover table 명령어로 drop table 복구
- 11g: drop table 일반 복구 순서 (무정지 응급복구, CloneDB)
- 임시 경로로 필요한 파일 복원
- 파일 경로 변경 후 삭제된 Table 복구
- 임시 경로에서 복구된 테이블 추출 (expdp)
- 추출된 데이터를 운영 서버로 입력 (impdp)
- 복구에 사용된 DB 삭제
- 12c : recover table 수행
archive log list;
@dd.sql
set line 200
col tablespace_name for a10
col file_name for a45
select tablespace_name, bytes/1024/1024 mb, file_name from dba_data_files;
create table scott.test200 (no number) tablespace users;
insert into scott.test200 values (1);
commit;
select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;
rman target /
backup as compressed backupset database format '/home/oracle/rman/%U_%T';
-- 장애 유발
select * from scott.test200;
drop table scott.test200 purge;
select * from scott.test200;
-- 복구
recover table scott.test200 until time "to_date('2015-12-18 08:06:15', 'YYYY-MM-DD HH24:MI:SS')" auxiliary destination '/home/oracle/temp';
select * from scott.test200;
