01 No Archive log mode 와 Archive log mode
no archive log mode - redo log file 을 덮어쓰는 경우
archive log mode - online redo log file 을 손실없이 지키는 방법
Archive log 를 만드는 시점은 log switch 가 발생하면 즉시 archiving 작업이 시작되며 이 작업을 하는 백그라운드 프로세스가 archiver(arch) 이다.
주의사항
1. 별도의 저장공간 필요하다.
2. 공간이 가득 찰 경우 DB가 중단된다.
3. 관리가 까다롭다.
archive redo log mode 로 변경하기
DB 종료 → Parameter File 수정 → DB Mount 로 시작 → Mode 변경 → DB Open
SQL> shutdown immediate ;
SQL> !
$ vi $ORACLE_HOME/dbs/inittestdb.ora
log_archive_start=true ← 9i 까지만 사용함, 10g 에서 사용하면 에러 남
log_archive_dest1='location=/data/arc1' ← 첫번째 저장 경로 지정
log_archive_dest1='location=/data/arc2' ← 두번째 저장 경로 지정
log_archive_format=%s_%t_%r.arc ← 아카이브 로그 파일 이름 지정
SQL> startup mount ;
SQL> archive log list ; ← 아카이브 로그 여부 확인
SQL> alter database archivelog ; ← 아카이브 로그 모드로 변경
SQL> alter database open ;
< SPFILE 을 사용할 경우>
SYS> show parameter spfile;
SYS> select status from v$instance;
SYS> show parameter log_archive_dest_1 ;
SYS> alter system set log_archive_dest_1='location=/data/arc1' scope=spfile;
SYS> alter system set log_archive_dest_2='location=/data/arc2' scope=spfile;
SYS> alter system set log_archive_format="%s_%t_%r.arc" scope=spfile ;
SYS> shutdown immediate ;
SQL> startup mount ;
SQL> archive log list ; ← 아카이브 로그 여부 확인
SQL> alter database archivelog ; ← 아카이브 로그 모드로 변경
SQL> alter database open ;
Archive Hang 발생 시 해결방법
디스크 용량 확인
$ df -h
Alert Log 확인
$ vi /home/oracle/admin/testdb/bdump/alert_testdb.log
(제일 아래쪽 확인)
ARCH: Archival stopped, error occurred. Will continue retrying
조치1 여유 공간 확보
$ cd /data/arc1
$ rm -fr [1-3]*
$ df -h
조치2 파라미터 변경 후 archiver 재시작
SQL> alter system set log_archive_dest_state_1 = defer ;
SQL> alter system set log_archive_dest_state_1 = enable ;
SQL> alter system set log_archive_dest_state_2 = defer ;
SQL> alter system set log_archive_dest_state_2 = enable ;
SQL> alter system archive log stop ;
SQL> alter system archive log start ;
SQL> alter system switch logfile ;
잘 되는 것을 확인할 수 있습니다.
02 Oracle Backup
백업받아야 하는 대상
필수 파일 data file, control file, redo log file
선택 파일 parameter file, password file
data file 은 실제 데이터가 저장되어 있는 파일
SQL> select name, status from v$datafile ;
control file 은 현재 사용중인 파일만 쓸 수 있고, 과거에 썼던 파일은 백업 받아도 쓸 수 없다
SQL> select name from v$controlfile ;
redo log file 은 데이터에 변경이 일어난 내용을 복구에 사용하기 위해 저장하고 있는 파일
SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
2 from v$logfile a, v$log b
3 where a.group# = b.group#
4 order by 1, 2 ;
parameter file, password file
$ ls $ORACLE_HOME/dbs/
SYS 계정 암호 묻도록 설정하기
$ cd /home/oracle/product/10g/network/admin/
$ ls
sqlnet.ora 파일이 없을 경우 oracle 계정으로 netca 를 수행하여 이름 지정 방법을 설정하면 됩니다.
$ vi sqlnet.ora
sqlnet.authentication_services=(none)
위 줄을 추가한 후 저장함
Password 파일이 삭제되었을 경우 조치법
$ cd /home/oracle/product/10g/dbs/
$ ls
orapwtestdb 파일이 없을 경우
$ orapwd
$ orapwd file=/home/oracle/product/10g/dbs/orapwtestdb
SYS 계정 암호 바꾸기
닫힌 백업 (cold backup / close backup)
1) 모든 파일은 동일한 checkpoint SCN 을 가져야 합니다.
2) shutdown 은 정상적인 shutdown 이어야 합니다.
3) archive log / no archive log mode 에 상관없이 모두 사용될 수 있습니다.
4) Data files, online redo log files, control files 모두를 백업 받을 수 있습니다.
5) Tablespace 를 offline 시키고 받는 백업도 일종의 close backup 으로 생각할 수 있습니다.
닫힌 백업 수행
백업 대상 확인
SQL> select name, status from v$datafile ;
SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
2 from v$logfile a, v$log b
3 where a.group# = b.group#
4 order by 1, 2 ;
SQL> select name from v$controlfile ;
DB 종료
SQL> shutdown immediate ;
전체 파일 백업 수행
$ cp -av /home/oracle/oradata/testdb/*.dbf /data/backup/close
$ cp -av /home/oracle/oradata/testdb/*.log /data/backup/close
$ cp -av /home/oracle/oradata/testdb/*.ctl /data/backup/close
$ cp -av /home/oracle/product/10g/dbs/inittestdb.ora /data/backup/close
$ cp -av /home/oracle/product/10g/dbs/orapwtestdb.ora /data/backup/close
DB 오픈
SQL> startup
열린 백업 (hot backup / open backup / begin backup)
DB를 절대로 중단할 수 없는 상태인데 백업을 받아야 할 때 사용한다.
Oracle 에서는 어떤 파일도 사용 중일 때 복사나 이동 작업을 해서는 안된다.
열린 백업은 Tablaspace 단위로 백업을 수행한다.
반드시 DB 가 archive log mode 여야만 합니다.
Data file 과 Control file 은 받을 수 있으나 online Redo log file 은 백업받지 못한다.
열린 백업 수행
SQL> alter tablespace users begin backup ;
SQL> ! cp /home/oracle/oradata/testdb/users01.dbf /data/backup/open
SQL> alter tablespace users end backup ;
End backup 이 수행되었는지 아닌지 확인
SQL> select a.file#, a.name, b.status, to_char(b.time, 'YYYY-MM-DD:HH24:MI:SS') as time
2 from v$datafile a, v$backup b
3 where a.file# = b.file# ;
STATUS 상태가 ACTIVE 이면 begin backup 상태
TIME 은 가장 마지막에 begin backup 받은 시간
03 Recovery 원리
Oracle Startup 과정
no mount server process 가 parameter file 을 PGA 로 읽어 들여서 해당 parameter file 에서 지정한 대로 instance 를 생성하는 단계, Alert log 기록
mount parameter file 에 기록되어 있는 위치에서 control file 을 읽어서 장애를 복구하거나, 유지관리작업 (rename file, drop file) 등의 작업을 하는 단계
1. CKPT process 가 모든 control file 을 읽고 일시적으로 lock 을 설정
2. control file header 정보를 검증해서 control file 에 이상이 없는지 확인하고, mountID 를 계산해서 control file 에 저장
3. parameter file 의 database name 과 control file 의 DB name 이 동일한지 검사
4. 위 과정까지 이상이 없으면 alert log file 에 "Sucessful mount of redo Thread" , "Database mounted" 라는 메시지 기록
open Data file header 부분의 정보와 control file 의 정보를 서로 비교해서 장애 유무를 판단 (Checkpoint SCN 정보 확인)
Recovery 원리
SQL> oradebug setmypid ;
SQL> oradebug dump controlf 3 ;
SQL> !
$ pwd
/home/oracle/admin/testdb/udump/testdb_ora_19226.trc
$ ls
testdb_ora_19226.trc
$ vi testdb_ora_19226.trc
...
DB NAME "TESTDB"
...
Database checkpoint: Thread=1 scn: 0x0000.001720d1 ← DB 전체의 checkpoint
...
Checkpointed at scn: 0x0000......
...
Low scn: 0x.0000...
Next scn: 0x0000...
...
Checkpoint cnt: 333 scn: 0x0000...
Stop scn: 0xffff.ffffffff 02/27/2010 08:20:32 ← ffff 란 현재 사용중이란 의미
오라클은 DB 가 open 상태이면 Stop scn 을 무한대 (0xffff.ffffffff) 로 설정하고 DB 가 종료되거나 offline 되면
checkpoint 를 발생시켜 checkpoint scn 과 stop scn 을 동일하게 만들고 해당 데이터 파일을 닫습니다.
정상적으로 종료된 파일은 Checkpoint scn 과 Stop scn 이 동일합니다.
이 원리를 이용해 데이터 파일의 이상 유무를 확인하는 것입니다.
control file 의 SCN 정보와 data file 의 SCN 정보를 비교해서 차이나는 부분을
Redo log file 이나 Archive log file 에서 찾아서 복구한다.
Crash Recovery Instance Recovery 라고도 하는데 운영 중이던 Database 가 비정상적으로 종료된 것을 의미합니다.
startup 시에 automatically 하게 recovery 하는 것을 의미하며 online Redo log file 만 사용됩니다.
Media Recovery file 삭제나 disk fail 등의 이유로 Instance Recovery 가 실패할 경우 DBA 가 수동으로 백업 파일 등을 복원한 후
Online Redo log file 과 archived redo log file 등을 사용하여 recovery 해야 하는데 이것을 Media Recovery 라고 합니다.
04 Parameter file 장애 복구
오라클 관리실무에 있는 내용임
05 Control File
오라클 서버를 운영하는데 필요한 중요한 정보를 저장하고 있는 바이너리 파일
Oracle 버전별 control file 내용
7 버전
1. 데이터베이스에 대한 정보를 저장합니다. 활성화되어 사용중인 데이터 파일과 리두 로그 파일, 스레드 등에 정보를 저장하고 있습니다.
2. 리두 로그 관련 정보를 저장합니다. 리두 스레드 정보 (OPS 아니면 스레드는 1로 되어 있을 것입니다.) 전용인지 아니면 공용인지 여부와
각 로그를 그룹과 LGWR 이 현재 기록중 (Current) 인 로그 그룹도 저장하고 있습니다.
3. 로그 그룹의 각 로그 멤버에 관한 정보를 저장합니다. 각 로그 파일의 크기, 완전한 경로의 이름, 로그 시퀀스 번호, 각 로그 파일별로 최대/최소 SCN 값,
각 로그 파일이 속한 스레드 정보 등을 저장하고 있습니다.
4. 데이터 파일에 대한 정보를 가지고 있습니다. 각 데이터 파일 내의 oracle block 크기를 저장하고 있고, 각 데이터 파일을 읽고 쓸 수 있는지,
온/오프라인 상태, 복구가 필요한지 등과 각 데이터 파일별로 최종 저장된 SCN 번호를 기록하고 있습니다.
이 부분이 나중에 복구할 때 중요하게 사용되는 부분입니다.
5. 로그 히스토리 정보를 저장하고 있습니다.
8 버전
7 버전의 내용에 아래의 항목 추가
1. 체크 포인트 진행 정보를 저장하고 있습니다.
2. 테이블스페이스 정보를 저장하고 있습니다. 각 테이블스페이스별로 이름과 경로, 복구가 진행된 테이블스페이스라면
복구에 사용된 SCN 번호의 처음과 끝을 저장하고 있습니다.
3. 오프라인 정보를 저장하고 있습니다. 테이블스페이스가 오프라인 되었거나 읽기 전용일 경우 온라인으로 만들 경우와
쓰기가 가능하게 하는 정보들이 저장되어 있습니다.
4. 아카이브 로그 관련 정보를 포함하고 있습니다.
5. 백업셋 관련 정보를 보관합니다. 이 부분은 RMAN 관련 정보입니다.
6. RMAN 으로 백업 받을 경우 발견된 백업셋 데이터 손상 내역과 백업 copy 손상 내역을 저장하고 있습니다.
7. RMAN 사용 시 백업셋을 삭제할 경우 삭제 정보를 가지고 있습니다.
9i 버전
1. Database Entry - DB 이름, DB 생성 시간, Resetlogs SCN, checkpoint SCN, Redo log Thread 정보, 아카이브 정보 등이 저장되어 있습니다.
2. Checkpoint Progress Record - 체크포인트 관련 보가 들어있습니다.
3. Extended Database Entry - control file 자동 백업 등의 추가적인 정보가 들어있습니다.
4. Redo Thread Records - Redo log Thread 관련 정보가 들어 있습니다.
OPS 나 RAC 가 아니면 Redo log Thread 정보는 1만 있습니다.
5. Log file records - 각 리두 로그 그룹별로 시퀀스 번호와 SCN 번호 등의 정보가 들어있습니다.
6. Data file records - 각 데이터 파일별로 파일 이름, checkpoint SCN, stop SCN, hot backup 상태, 오프라인 상태 등이 저장되어 있습니다.
7. Temp file records - 각 temporary file 관련 상태가 기록되어 있습니다.
8. Tablespace Records - 각 tablespace 이름과 링크된 데이터 파일 정보 PITR(Point In Time Recovery) 관련 정보들이 저장되어 있습니다.
9. RMAN CONFIGURATION RECORDS
10. LOG FILE HISTORY RECORDS
11. OFFLINE RANGE RECORDS
12. ARCHIVED LOG RECORDS
13. BACKUP SET RECORDS
14. BACKUP PIECE RECORDS
15. BACKUP DATAFILE RECORDS
16. BACKUP LOG RECORDS
17. DATAFILE COPY RECORDS
18. BACKUP DATAFILE CORRUPTION RECORDS
19. DATAFILE COPY CORRUPTION RECORDS
20. DELETION RECORDS
21. PROXY COPY RECORDS
22. INCARNATION RECORDS
10g R2 버전
1. DATABASE ENTRY
2. CHECKPOINT PROGRESS RECORDS
3. EXTENDED DATABASE ENTRY
4. REDO THREAD RECORDS
5. LOG FILE RECORDS
6. DATA FILE RECORDS
7. TEMP FILE RECORDS
8. TABLESPACE RECORDS
9. RMAN CONFIGURATION RECORDS
10. FLASHBACK LOGFILE RECORDS
11. THREAD INSTANCE MAPPING RECORDS
12. MTTR RECORDS
13. STANDBY DATABASE MAP RECORDS
14. RESTORE POINT RECORDS
15. LOG FILE HISTORY RECORDS
16. OFFLINE RANGE RECORDS
17. ARCHIVED LOG RECORDS
18. BACKUP SET RECORDS
19. BACKUP PIECE RECORDS
20. BACKUP DATAFILE RECORDS
21. BACKUP LOG RECORDS
22. DATAFILE COPY RECORDS
23. BACKUP DATAFILE CORRUPTION RECORDS
24. DATAFILE COPY CORRUPTION RECORDS
25. DELETION RECORDS
26. PROXY COPY RECORDS
27. INCARNATION RECORDS
28. RMAN STATUS RECORDS
29. DATAFILE HISTORY RECORDS
30. NORMAL RESTORE POINT RECORDS
trace 수행
$ORACLE_BASE/admin/SID/udump/ 아래에 .trc 파일로 생성
trace 파일에 구분자 주기
SQL> alter session set tracefile_identifier='sjs'
SQL> oradebug setmypid ;
SQL> oradebug dump controlf 3 ;
여기까지 하면 trace file 이 만들어집니다.
※ Control file 안에 있는 내용들과 이 내용들이 오라클이 운영되는 중에 어떻게 사용되는지 이해한다면
Oracle 백업 복구 및 어드민 관련 내용은 거의 정복한 것이라고 할 수 있을만큼 중요한 내용들이니 꼭 숙지하기 바랍니다.
parameter file 에 있는대로 실제 control file 이 존재해야 한다.
Control File 장애 복구
1. Parameter file 의 경로와 실제 파일 경로가 다른 경우 - parameter file 에는 control03.ctl 이 있으나 실제 파일이 없음
ORA-00205: error in identifying control file, check alert log for more info ← 에러 발생
SYS> ! vi /home/oracle/admin/testdb/bdump/alert_testdb.log
SYS> select status from v$instance ;
SYS> ! cp /home/oracle/oradata/testdb/control01.ctl /home/oracle/oradata/testdb/control03.ctl ← 다른 파일에서 복사
SYS> alter database mount ;
SYS> alter database open ;
2. Control file 끼리 내용이 다른 경우 - version error
ORA-00214: control file '/home/oracle/oradata/testdb/control01.ctl' version 644
inconsistent with file '/home/oracle/oradata/testdb/control03.ctl' version 631
SYS> ! cp /home/oracle/oradata/testdb/control01.ctl /home/oracle/oradata/testdb/control03.ctl ← 버전이 높은 파일을 덮어씀
SYS> alter database mount ;
SYS> alter database open ;
3. Old control file, Control file 삭제됨, Incarnation 에러
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/home/oracle/oradata/testdb/system01.dbf'
ORA-01207: file is more recent than control file - old control file
control file 의 checkpoint가 data file 의 checkpoint 보다 예전 내용이기 때문
using backup controlfile 옵션으로 recovery 할 것
이 방법이 불가할 경우 부득이하게 control file 재생성해야 함
1. control file 이 전부 삭제되었을 때
2. old control file 에러 발생시
3. DB name 변경하고 싶을 때
4. 최대 데이터 파일 개수와 리두 로그 파일 개수를 변경하고 싶을 때
Old control file 장애 해결 방법
1) 현재 Data file, Redo log file 에 이상이 없을 경우 - 재생성, noresetlogs 로 복구
control file 을 재생성 하려면 nomount 상태에서 db 를 재생성하는 명령어를 입력하면 되는데
이 명령어가 너무 길어서 스크립트로 만들어서 생성하는 방법을 보여주겠습니다.
control file 을 재생성하는 스크립트는 현재 old control file 로부터 trace 해서 만들며 아래의 방법으로 하면 됩니다.
이 방법은 현재의 control file 의 정보를 사용해서 재생성 하는 것이므로 mount 상태에서 수행해야 합니다.
SYS> select status from v$instance ;
STATUS
--------
MOUNTED
SQL> alter database backup controlfile to trace as '/home/oracle/re11.sql' ; ← control file 재생성 스크립트 만들기
SQL> shutdown immediate ;
SQL> !
Control file 을 생성할 때 Redo log file 에 문제가 있다면 RESETLOGS 로, 문제가 없다면 NORESETLOGS 용으로 생성하면 됩니다.
$ vi /home/oracle/re11.sql
주석과 공백 라인을 모두 삭제합니다.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "TESTDB" NORESETLOGS ARCHIVELOG
...
CHARACTER SET KO16KSC5601
;
control file 재생성 시 주의사항
1. Resetlogs 로 open 되는지 Noresetlogs 로 open 되는지 정확히 구분
2. 스크립트 안에 주석이나 공백이 있으면 생성 도중 에러가 납니다.
3. Noresetlogs 모드일 경우 스크립트 내부의 Redo log file 이 전부 해당 경로에 있어야만 합니다.
4. Resetlogs 모드일 경우 스크립트 내부의 Redo log file 은 실제 파일이 없어도 Resetlogs 로 open 될 때 생성됩니다.
5. Data file 은 반드시 스크립트 내부의 그 경로에 실재 존재해야 합니다.
6. control file 생성 위치는 parameter file 에 지정된 경로입니다.
7. control file 을 재생성하기 위해서는 data file 끼리는 checkpoint 정보가 모두 동일해야 합니다.
$ sqlplus / as sysdba ;
SYS> @/home/oracle/re11.sql
SYS> alter database open ;
SYS> select name from v$controlfile ;
control file 이 재생성 되어 DB 가 정상적으로 open 되는 것을 확인할 수 있습니다.
control file 을 재생성하면 RMAN 백업을 사용 못하게 될 수 있고, FLASHBACK 기능도 사용 못하게 될 수도 있습니다.
2) 현재 Data file, Control file 에 이상이 있고 Data file 백업이 있을 경우 - Using backup controlfile 로 복구
SYS> shutdown immediate ;
SYS> !cp /data/backup/close/*.dbf /home/oracle/oradata/testdb/ ← data file 만 복원함
SYS> recover database until cancel using backup controlfile ;
auto
SYS> alter database open resetlogs ;
3) 현재 Data file, Redo log file 에 이상이 있거나 Data file 백업이 없을 경우 - 재생성, resetlogs 로 복구
현재 사용 중이던 control file 과 redo log file 에 장애가 발생한 후 비정상 종료되었는데 백업된 data file 이 없어서
사용 중이다가 비정상 종료되어 checkpoint 정보가 서로 다른 data file 을 사용해서 긴급 복구해야 하는 경우
data file 끼리 checkpoint 정보가 다를 경우 어떻게 복구하느냐
ORA-00205: error in identifying control file, check alert log for more info
SYS> shutdown abort ;
SQL> !cp /data/backup/close/*.ctl /home/oracle/oradata/testdb/
SQL> startup
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '/home/oracle/oradata/testdb/system01.dbf' ← 장애 발생
SQL> shutdown abort ;
SYS>!vi /app/oracle/re12.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "TESTDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/app/oracle/oradata/testdb/redo01_a.log',
'/app/oracle/oradata/testdb/redo01_b.log'
) SIZE 50M BLOCKSIZE 512,
GROUP 2 (
'/app/oracle/oradata/testdb/redo02_a.log',
'/app/oracle/oradata/testdb/redo02_b.log'
) SIZE 50M BLOCKSIZE 512,
GROUP 3 (
'/app/oracle/oradata/testdb/redo03_a.log',
'/app/oracle/oradata/testdb/redo03_b.log'
) SIZE 50M BLOCKSIZE 512
DATAFILE
'/app/oracle/oradata/testdb/system01.dbf',
'/app/oracle/oradata/testdb/sysaux01.dbf',
'/app/oracle/oradata/testdb/undotbs01.dbf',
'/app/oracle/oradata/testdb/users01.dbf',
'/app/oracle/oradata/testdb/example01.dbf'
CHARACTER SET KO16MSWIN949
;
SQL> @/home/oracle/re12.sql
SQL> alter database open resetlogs ;
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/home/oracle/oradata/testdb/system01.dbf'
위 에러 메시지는 DB 가 종료될 때 shutdown abort 로 비정상 종료되어
데이터 파일끼리 checkpoint 정보가 동기화되지 않아서 resetlogs 옵션으로 open 할 수 없으니 복구하라는 내용입니다.
그러나 현재 모든 Redo log file 이 삭제되었으므로 복구할 수 없는 상황입니다.
이럴 경우 강제로 data file 끼리 checkpoint 정보를 동기화 시켜서 open 시키는 hidden parameter 를 사용해야 합니다.
단, 이 parameter 는 아주 위험하기 때문에 사용하는 것을 권장하지 않고 그 결과를 책임지지도 않습니다.
부득이 하게 사용할 경우라도 모든 책임은 스스로에게 있음을 알려 드립니다.
초기화 파라미터 파일에 아래와 같은 파라미터를 설정한 후 DB를 재시작하면 됩니다.
SYS>shutdown immediate ;
$ vi $ORACLE_HOME/dbs/inittestdb.ora
_allow_resetlogs_corruption=true ← 추가한 후 저장
종합복구01 백업파일이 close backup 일 경우
data file, redo log file, control file 이 모두 소실되었고
어제 백업받았던 data file 과 2개월 전 백업 받았던 control file 밖에 없을 때 복구
복구 작업은 임시 디렉토리에서 실시
$ mkdir /data/temp/imsy
$ cp /data/backup/close/*.dbf /data/temp/imsy/
$ cp /data/backup/close/*.ctl /data/temp/imsy/
SYS> alter database backup controlfile to trace as '/home/oracle/recon.sql';
SYS> !
$ vi recon.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "TESTDB" RESETLOGS ARCHIVELOG
...
LOGFILE
GROUP 1 '/data/temp/imsy/redo01.log' SIZE 5M,
...
DATAFILE
'/data/temp/imsy/system01.dbf',
...
log file 과 data file 의 경로를 임시로 지정합니다.
$ exit
SYS> @/home/oracle/recon.sql
SYS> select name from v$datafile ;
SYS> alter database open resetlogs ;
이 실습의 핵심은 data file 과 checkpoint SCN 의 정보가 다른 old control file 을 이용해서
어떻게 현재 data file 을 복구해 내느냐 입니다.
종합복구02 백업 파일이 hot backup 일 경우
data file, redo log file, control file 이 전부 삭제 되었고
1주일 전 hot backup 으로 받아둔 data file
6개월 전 받아둔 control file 이 있습니다.
SQL> !
$ cp /data/backup/open/*.dbf /home/oracle/oradata/testdb/
$ cp /data/backup/close/*.ctl /home/oracle/oradata/testdb/
$ exit
SQL> startup
SQL> alter database backup controlfile to trace as '/home/oracle/re.sql' ;
SQL> shutdown immediate ;
Redo log file 이 삭제 되었으므로 resetlogs 모드로 생성해야 합니다.
$ vi /home/oracle/re.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "TESTDB" RESETLOGS ARCHIVELOG
...
$ exit
SQL> @re
SQL> alter database open resetlogs ;
data file 끼리 checkpoint 정보가 달라 파일을 복구하라는 메시지가 나옵니다.
SQL> recover database using backup controlfile ; ← recovery 시도
SQL> alter database open resetlogs ;
여전히 복구가 안됨
SQL> shutdown immediate ;
SQL> !
data file 끼리 checkpoint 정보를 동기화하기 위해서는 redo log file 이 있어야 하는데
모든 redo log file 이 삭제된 상태이므로 복구할 수 없게 되었습니다.
어쩔 수 없이 data file 끼리 checkpoint 정보가 달라도 open 을 허용하라는 hidden parameter 를 사용하여야 합니다.
$ vi product/10g/dbs/inittestdb.ora ← _allow_resetlogs_corruption=true 추가
$ exit
SQL> startup mount ;
SQL> alter database open resetlogs ;
이렇게 강제 open 하면 data 정합성이 깨집니다. 그리고 과거 백업 받았던 파일은 사용할 수 없으니
open 한 후 다시 정상 종료 후 전체 백업을 수행하여야 합니다.
RESETLOGS CASE 로 사용될 Control file 을 재생성할 때는 모든 Redo log file 에 관련된 정보들은 초기화 되고
반면에 모든 Data file 의 정보는 전부 필요로 한다는 것입니다.
그렇기 때문에 NORESETLOGS CASE 일 경우는 반드시 control file 재생성 스크립트에
Online Redo log file 이 전부 있어야 하고
Data file 도 전부 있어야 하지만
RESETLOGS CASE 일 경우엔 Data file 만 있으면 됩니다.
06 Data File 장애 및 사용자 장애 복구하기
no archive log mode 에서 장애 해결
SQL> startup mount ;
SQL> archive log list ;
SQL> alter database noarchivelog ;
SQL> alter database open ;
SQL> archive log list ;
tablespace 생성 후 백업 후 datafile 삭제
restore 후 recover
SQL> !cp /data/backup/close/test01.dbf /home/oracle/oradata/testdb/ ← 백업파일 복원
SQL> recover database ;
noarchive mode 에서 장애가 발생했지만 복구가 됩니다.
그 이유는 온라인 Redo log 에 있기 때문입니다.
log switch 를 수동으로 발생시켜 복구하려는 데이터를 archive log 에 있게 만들고 다시 시도
SQL> alter database open ;
SQL> !rm -fr /home/oracle/oradata/testdb/test01.dbf
SQL> !ls /home/oracle/oradata/testdb/test01
SQL> alter system switch logfile ;
SQL> /
SQL> /
SQL> select status from v$instance ;
SQL> exit
$ sqlplus / as sysdba ;
SQL> startup
파일 없어서 에러남
SQL> ! cp /data/backup/close/testdb01.dbf /home/oracle/oradata/testdb/ ← 백업파일 복원
SQL> recover database ; ← 복구시도
archive log 찾지만 없음
SQL> select status from v$instance ;
SQL> alter database open ;
open 실패
SCN 번호가 달라서 DB 가 시작이 안되는 것이므로 SCN 번호를 같게 만들어 주면 됩니다.
SQL>aler database datafile '/home/oracle/oradata/testdb/test01.dbf' offline drop ;
문제가 되는 Data file 사용 안함
SQL> alter database open ;
SQL> select status from v$instance ;
archive log mode 일 경우 장애 복구하기
완전 복구
archive log와 online redo log 를 적용시켜 장애시점 직전까지 모든 데이터를 완전히 복구
불완전 복구
장애가 발생할 경우 복구하기 위해서 해당 파일을 I/O 가 발생하지 않도록 만들어 놓고 복구 작업을 해야 한다.
Offline 되는 일반 Tablespace 장애 복구하기
SQL> startup mount ;
SQL> alter database archivelog ;
SQL> alter database open ;
SQL> alter tablespace test begin backup ;
SQL> !cp /home/oracle/oradata/testdb/test01.dbf /data/backup/open/
SQL> alter tablespace test end backup ;
SQL> !rm -fr /home/oracle/oradata/testdb/testdb01.dbf
SQL> !ls /home/oracle/oradata/testdb/test01.dbf
SQL> create table abc (no number) tablespace test ;
SQL> insert into abc values (1) ;
SQL> insert into abc values (2)
SQL> commit ;
SQL> select * from abc ;
SQL> alter tablespace test offline ;
SQL> !cp /data/backup/open/test01.dbf /home/oracle/oradata/testdb/
SQL> recover tablespace test ;
SQL> alter tablespace test online ;
SQL> select * from abc ;
offline 되는 tablespace 라서 db 종료없이 해당 tablespace 만 offline 시키고 복구한 후 online 시켜서 다운타임 최소화
Offline 안되는 Tablespace 장애 복구하기
SQL> !rm -fr /home/oracle/oradata/testdb/system01.dbf ←system tablespace 삭제
SQL> alter tablespace system offline ; ←offline 시도 했으나 안됨
SQL> shutdown immediate ;
SQL> !cp /data/backup/close/system01.dbf /home/oracle/oradata/testdb/ ← 백업파일 복원
SQL> startup
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/home/oracle/oradata/testdb/system01.dbf'
SQL> recover database ;
SQL> alter database open ;
백업 파일이 없는 경우 복구하기
tablespace 백업파일 없고, archive log 파일 존재할 때
tablespace 파일 삭제 후 데이터 입력한 상태
SQL> alter database create datafile '/data/temp/haksa01.dbf' ← control file 에 있는 원래 경로
2 as '/home/oracle/oradata/testdb/haksa_01.dbf' ; ← 신규로 생성하고자 하는 경로와 이름
SQL> recover tablespace haksa ;
SQL> alter tablespace haksa online ;
★ 임시 경로를 이용한 긴급복구 (일부 Data file 의 백업 파일이 없는 경우)
디스크 불량으로 data file 삭제됨
redo log file, archive log file, 백업된 data file 은 다른 경로에 있고
하드디스크가 오는 동안 db를 중단시킬 수 없어서 /data/temp 에 복원하고 복구하여 운영하고
하드디스크가 도착하면 원래경로로 이동시킨다
SQL> !mkdir /data/temp
SQL> !cp /data/backup/close/*.dbf /data/temp/
SQL> select name from v$datafile ;
SQL> alter database rename file '/home/oracle/oradata/testdb/system01.dbf'
2 to '/data/temp/system01.dbf' ;
...
SQL> alter database create datafile '/home/oracle/oradata/testdb/test01.dbf'
2 as '/data/temp/test01.dbf' ; ← 백업 없던 파일을 신규로 생성함
SQL> recover database ;
SQL> alter database open ;
모든 data file 을 과거에 백업 받았던 것으로 전부 복원한 후, archive log 파일을 적용시켜 장애 직전 시점까지 복구하는 것
모든 data file 은 사고 시점 이전의 백업파일로부터 복원하고, redo log file 과 control file 은 현재 시점의 것을 사용해야 합니다.
Drop table 명령으로 삭제된 table 복구하기 (모든 데이터 파일 복원)
SYS> shutdown immediate ;
SYS> !
$ mkdir /home/oracle/temp
백업된 data file 복사해오기
$ cp /data/backup/close/*.dbf /home/oracle/temp/
현재 쓰고 있던 control file 복사해오기
$ cp /home/oracle/oradata/testdb/*.ctl /home/oracle/temp/
현재 쓰고 있던 redo log file 복사해오기
$ cp /home/oracle/oradata/testdb/*.log /home/oracle/temp/
$ vi /home/oracle/product/10g/dbs/inittestdb.ora
control_files=("/home/oracle/temp/control01.ctl",
"/home/oracle/temp/control02.ctl",
"/home/oracle/temp/control03.ctl")
:wq!
$ exit
SYS> startup mount ;
SYS> select name from v$controlfile ;
SYS> select name from v$datafile ;
redo log file 위치 변경하기
SYS> alter database rename file '/home/oracle/oradata/testdb/system01.dbf'
2 to '/home/oracle/temp/system01.dbf' ;
...
★ Drop table 복구하기 - 일부 파일만 복원 (실무에서 아주 요긴하게 사용되는 방법이니 꼭 숙지할 것)
임시 경로에 필요한 파일 복원
$ mkdir /data/imsy
$ cp /data/backup/close/system01.dbf /data/imsy/
$ cp /data/backup/close/sysaux01.dbf /data/imsy/
$ cp /data/backup/close/undotbs01.dbf /data/imsy/
$ cp /data/backup/close/example01.dbf /data/imsy/
$ cp /home/oracle/oradata/testdb/*.log /data/imsy/
$ cp /home/oracle/oradata/tetsdb/*.ctl /data/imsy/
parameter file 에서 control file 의 경로 변경 후 mount
$ vi /home/oracle/product/10g/dbs/inittestdb.ora
*.control_files='/data/imsy/control01.ctl'
:wq!
$ exit
SQL> startup mount ;
필요한 파일의 경로 변경
SQL> select name form v$datafile ;
SQL> alter database rename file '/home/oracle/oradata/testdb/system01.dbf'
2 to '/data/imsy/system01.dbf' ;
...
복구에 필요없는 파일은 offline drop
SQL> alter database datafile '/home/oracle/oradata/testdb/users01.dbf' offline drop ;
SQL> alter database rename file '/home/oracle/oradata/testdb/redo01.log'
2 '/data/imsy/redo01.log' ;
...
복구 후 데이터 확인
SQL> recover database until time '2013-01-17:06:52:15' ;
SQL> alter database open resetlogs ;
※ 복구에 반드시 필요한 파일만 복사해오고, 나머지 파일들은 mount 상태에서 offline drop 으로 복구에 사용 안하게 만들어야 함
Drop table 복구하기 (백업 없는 경우)
백업파일이 없는 데이터파일이 OS 명령어로 강제 삭제되었을 때
그 후 해당 tablespace 에 들어있던 table 이 drop 되었다.
SQL> !
$ vi /home/oracle/product/10g/dbs/inittestdb.ora
*.control_files='/data/imsy2/control01.ctl'
:wq!
$ exit
SQL> startup mount ;
SQL> select name from v$controlfile ;
SQL> select name from v$datafile ;
SQL> alter database rename file '/home/oracle/oradata/testdb/system01.dbf'
2 to '/data/imsy2/system01.dbf' ;
...
SQL> alter database datafile '/home/oracle/oradata/testdb/users01.dbf' offline drop ;
SQL> alter database datafile '/home/oracle/oradata/testdb/example01.dbf' offline drop ;
offline 후 삭제된 data file 생성 후 online
SQL> alter database datafile '/home/oracle/oradata/testdb/ts_b01.dbf' offline ;
SQL> alter database create datafile '/home/oracle/oradata/testdb/ts_b01.dbf'
2 as '/data/imsy2/ts_b01.dbf' ;
SQL> alter database datafile '/data/imsy2/ts_b01.dbf' online ;
SQL> select name, status from v$datafile ;
SQL> select member from v$logfile ;
SQL> alter database rename file '/home/oracle/oradata/testdb/redo01.log'
2 to '/data/imsy2/redo01.log' ;
...
SQL> select member from v$logfile ;
SQL> recover database until time '2013-01-15:13:16:31' ;
SQL> alter database open resetlogs ;
잘못된 Update 장애 복구하기
복구에 필요한 파일복원 (/home/oracle/temp/ 에서 복구)
백업된 data file 복사해오기
$ cp /data/backup/close/*.dbf /home/oracle/temp/
현재 쓰고 있던 control file 복사해오기
$ cp /home/oracle/oradata/testdb/*.ctl /home/oracle/temp/
현재 쓰고 있던 redo log file 복사해오기
$ cp /home/oracle/oradata/testdb/*.log /home/oracle/temp/
파일 경로 수정
$ vi /home/oracle/product/10g/dbs/inittestdb.ora
control_files=("/home/oracle/temp/control01.ctl",
"/home/oracle/temp/control02.ctl",
"/home/oracle/temp/control03.ctl")
:wq!
SYS> alter database rename file '/home/oracle/oradata/testdb/system01.dbf'
2 to '/home/oracle/temp/system01.dbf' ;
...
SYS> alter database rename file '/home/oracle/oradata/testdb/redo01_a.dbf'
2 to '/home/oracle/temp/redo01_a.dbf' ;
...
복구
SYS> recover database until time '2014-01-15:08:11:00' ;
auto
SYS> alter database open resetlogs ;
잘못된 Delete 장애 복구하기
db 종료 후 필요한 파일 복원
SYS> shutdown immediate ;
SYS> !
$ cp /data/backup/close/system01.dbf /home/oracle/temp/
$ cp /data/backup/close/sysaux01.dbf /home/oracle/temp/
$ cp /data/backup/close/undotbs01.dbf /home/oracle/temp/
$ cp /data/backup/close/test01.dbf /home/oracle/temp/
$ cp /home/oracle/oradata/testdb/*.log /home/oracle/temp/
$ cp /home/oracle/oradata/testdb/*.ctl /home/oracle/temp/
control file 경로 수정 후 mount
$ vi /home/oracle/product/10g/dbs/inittestdb.ora
SYS> startup mount ;
SYS> select name from v$controlfile ;
data file 경로 수정
SYS> select name from v$datafile ;
SYS> alter database rename file '/home/oracle/oradata/testdb/system01.dbf'
2 to '/home/oracle/temp/system01.dbf' ;
...
필요없는 파일 offline으로
SYS> alter database datafile '/home/oracle/oradata/testdb/users01.dbf' offline drop ;
SYS> alter database datafile '/home/oracle/oradata/testdb/example01.dbf' offline drop ;
SYS> @df
redo log file 경로 수정
SYS> select member from v$logfile ;
SYS> alter database rename file '/home/oracle/oradata/testdb/redo03_a.log'
2 to '/home/oracle/temp/redo03_a.log' ;
...
복구
SYS> recover database until time '2013-01-14:12:12:12' ;
SYS> alter database open resetlogs ;
잘못된 Drop User 장애 복구하기
임시 경로로 파일 복원 후 복구하기
SYS> shutdown immediate ;
SYS> !
$ cp /data/backup/close/system01.dbf /home/oracle/temp/
$ cp /data/backup/close/sysaux01.dbf /home/oracle/temp/
$ cp /data/backup/close/undotbs01.dbf /home/oracle/temp/
$ cp /data/backup/close/test01.dbf /home/oracle/temp/
$ cp /home/oracle/oradata/testdb/*.log /home/oracle/temp/
$ cp /home/oracle/oradata/testdb/*.ctl /home/oracle/temp/
control file 경로 수정 후 mount
$ vi /home/oracle/product/10g/dbs/inittestdb.ora
SYS> startup mount ;
SYS> select name from v$controlfile ;
data file 경로 수정
SYS> select name from v$datafile ;
SYS> alter database rename file '/home/oracle/oradata/testdb/system01.dbf'
2 to '/home/oracle/temp/system01.dbf' ;
...
필요없는 파일 offline으로
SYS> alter database datafile '/home/oracle/oradata/testdb/users01.dbf' offline drop ;
SYS> alter database datafile '/home/oracle/oradata/testdb/example01.dbf' offline drop ;
SYS> @df
복구
SYS> recover database until time '2013-01-14:12:12:12' ;
SYS> alter database open resetlogs ;
Drop tablespace 로 잘못 삭제된 Tablespace 복구하기 - 백업 Control File 이 있을 경우
복구 원리
recover → control file → data file → redo/archive log file
순서로 읽어 들여 복구
recover 가 시작되면 control file 에서 checkpoint 정보를 확인한 후 data file 을 찾아서 그 정보가 동일한지 확인하는데
data file 위치를 control file 에서 읽고 간다
drop tablespace 는 drop table 과는 다르게 control file 안에 있는 해당 tablespace 의 정보를 삭제해버린다.
drop tablespace 로 삭제된 tablespace 를 복구하려면 control file 에 해당 tablespace 의 정보가 있어야 한다.
복구 방법
1. 백업된 control file 이용
2. tablespace 정보를 redo/archive log 파일을 이용하여 강제로 control file 에 등록
※ drop tablespace 는 alert_SID.log 파일에 tablespace 가 삭제된 시간을 정확하게 기록해준다.
복구하기
$ vi /home/oracle/admin/testdb/bdump/alert_testdb.log
...
drop tablespace test including contents and datafiles
drop 위의 시간 확인
data file 만 백업 파일에서 복원
$ cp /data/backup/close/*.dbf /home/oracle/temp/
$ cp /home/oracle/oradata/testdb/*.log /home/oracle/temp/
$ cp /home/oracle/oradata/testdb/*ctl /home/oracle/temp/
control file 경로 수정 후 mount
$ vi /home/oracle/product/10g/dbs/inittestdb.ora
SYS> startup mount ;
SYS> select name from v$controlfile ;
data file 경로 수정
SYS> select name from v$datafile ;
SYS> alter database rename file '/home/oracle/oradata/testdb/system01.dbf'
2 to '/home/oracle/temp/system01.dbf' ;
...
필요없는 파일 offline으로
SYS> alter database datafile '/home/oracle/oradata/testdb/users01.dbf' offline drop ;
SYS> alter database datafile '/home/oracle/oradata/testdb/example01.dbf' offline drop ;
SYS> @df
redo log file 경로 수정
SYS> select member from v$logfile ;
SYS> alter database rename file '/home/oracle/oradata/testdb/redo03_a.log'
2 to '/home/oracle/temp/redo03_a.log' ;
복구
SYS> recover database until time '2013-01-14:12:12:12' ;
SYS> alter database open resetlogs ;
SYS> @df
복구가 안된 파일이 있다.
redo log file 에 해당 tablespace 를 만들었다는 기록이 있고 dictionary 에도 있는데
control file 에 정보가 없어서 RECOVER 라고 나오는 것이다.
tablespace 가 삭제되었는데 data file 만 백업을 가져오고 control file 과 redo log file 은 현재 시점의 것을 사용해서 문제가 되는 것이다.
data file 과 control file 모두 백업에서 가져와서 복구하겠다.
control file 을 백업에서 가져와야 하는 이유는
drop tablespace test including contents and datafiles; 로 삭제된 test tablespace 정보가 없기 때문에 복구에 사용할 수 없기 때문
복구 다시
SYS> shutdown immediate ;
SYS> !
$ cp /data/backup/close/system01.dbf /home/oracle/temp/
$ cp /data/backup/close/sysaux01.dbf /home/oracle/temp/
$ cp /data/backup/close/undotbs01.dbf /home/oracle/temp/
$ cp /data/backup/close/test01.dbf /home/oracle/temp/
control file 도 백업본 사용
$ cp /data/backup/close/*.ctl /home/oracle/temp/
redo log file은 최근 사용 파일 복사
$ cp /home/oracle/oradata/testdb/*.log /home/oracle/temp/
$ exit
control file 경로 수정 후 mount
$ vi /home/oracle/product/10g/dbs/inittestdb.ora
SYS> startup mount ;
SYS> select name from v$controlfile ;
data file 경로 수정
SYS> select name from v$datafile ;
SYS> alter database rename file '/home/oracle/oradata/testdb/system01.dbf'
2 to '/home/oracle/temp/system01.dbf' ;
...
필요없는 파일 offline으로
SYS> alter database datafile '/home/oracle/oradata/testdb/users01.dbf' offline drop ;
SYS> alter database datafile '/home/oracle/oradata/testdb/example01.dbf' offline drop ;
SYS> @df
redo log file 경로 수정
SYS> select member from v$logfile ;
SYS> alter database rename file '/home/oracle/oradata/testdb/redo03_a.log'
2 to '/home/oracle/temp/redo03_a.log' ;
...
SYS> select member from v$logfile ;
복구
control file 과 data file 은 예전 백업 파일이고 redo log file 은 현재 파일입니다.
control file 이 data file 보다 더 오래전 파일이면 old control file 이라는 에러가 나오며 복구가 안될 수 있습니다.
using backup controlfile 옵션을 추가해야 합니다.
until time 시간, until cancel 으로만 복구시점을 결정합니다.
SYS> recover database until time '2013-01-15:12:12:12' using backup controlfile ;
auto
SYS> alter database open resetlogs ;
Drop tablespace 로 잘못 삭제된 Tablespace 복구하기 - 백업 Control File 이 없을 경우
altertlog 에서 삭제된 시간 확인하기
SYS> !
$ vi /home/oracle/admin/testdb/bdump/alert_testdb.log
SYS> shutdown immediate ;
SYS> !
$ cp /data/backup/open/system01.dbf /home/oracle/temp/
$ cp /data/backup/open/sysaux01.dbf /home/oracle/temp/
$ cp /data/backup/open/undotbs01.dbf /home/oracle/temp/
control file 도 백업본 사용
$ cp /data/backup/open/*.ctl /home/oracle/temp/
redo log file은 최근 사용 파일 복사
$ cp /home/oracle/oradata/testdb/*.log /home/oracle/temp/
$ exit
control file 경로 수정 후 mount
$ vi /home/oracle/product/10g/dbs/inittestdb.ora
SYS> startup mount ;
SYS> select name from v$controlfile ;
data file 경로 수정
SYS> select name from v$datafile ;
SYS> alter database rename file '/home/oracle/oradata/testdb/system01.dbf'
2 to '/home/oracle/temp/system01.dbf' ;
...
필요없는 파일 offline으로
SYS> alter database datafile '/home/oracle/oradata/testdb/users01.dbf' offline drop ;
SYS> alter database datafile '/home/oracle/oradata/testdb/example01.dbf' offline drop ;
SYS> @df
redo log file 경로 수정
SYS> select member from v$logfile ;
SYS> alter database rename file '/home/oracle/oradata/testdb/redo03_a.log'
2 to '/home/oracle/temp/redo03_a.log' ;
...
SYS> select member from v$logfile ;
SYS> recover database until time '2013-01-15:12:12:12' using backup controlfile ;
/home/oracle/temp/redo02_a.log ← current log 파일 적어줌
SYS> select name from v$datafile ;
새로 생성됨
SYS> alter database create datafile '/home/oracle/product/10g/dbs/UNNAMED00006'
2 as '/home/oracle/temp/test01.dbf' ;
SYS> recover database until time '2013-01-15:12:12:12' using backup controlfile ;
auto ← 이렇게 치면 에러남
/home/oracle/temp/redo02_a.log ← current 였던 redo log 파일 이름 입력 후 엔터
SYS> alter database open resetlogs ;
SYS> @df
Log Miner 를 이용하여 Redo log file 에서 특정 테이블의 삭제 시간 찾기
db 에서 일어난 모든 변경 내역을 기록한 redo log file 안에 있는 내용을 참조하여
장애가 일어난 시간을 찾아내는 기술이 log miner 라는 기술입니다.
log miner 설정하기
1. parameter file 에 dictionary file 이 생성될 경로 설정을 추가
utl_file_dir="/data/logmnr" ← 경로는 원하는 곳으로 설정함. 디렉토리가 존재해야 함
2. 재시작
SQL> shutdown immediate ;
SQL> startup ;
3. 딕셔너리 파일을 생성
SQL> exec dbms_logmnr_d.build ('dict', '/data/logmnr') ;
4. 분석하기 원하는 log 파일 등록
SQL> @log
SQL> exec dbms_logmnr.add_logfile ('/home/oracle/oradata/testdb/redo091.log', 1) ;
5. 등록한 log 파일을 분석합니다
SQL> exec dbms_logmnr.start_logmnr (dictfilename=>'/data/logmnr/dict') ;
6. 찾고자 하는 내용을 조회
SQL> select timestamp, seg_owner, username, sql_redo
2 from v$logmnr_contents
3 where sql_redo like '%drop%'
4 and seg_owner = 'SCOTT' ;
Undo Tablespace 장애 복구하기
운영 중인 undo tablespace 장애 복구하기
현재 상황 확인
SQL> @df
SQL> show parameter undo ;
사용 중인 롤백 segment 확인
SQL> select s.sid, s.serial#, s.username, r.name "ROLLBACK SEG"
2 from v$session s, v$transaction t, v$rollname r
3 where s.taddr = t.addr
4 t.xidusn = r.usn
SQL> !rm -fr /home/oracle/oradata/testdb/undotbs01.dbf ← 운영 중인 undo tablespace 삭제되는 장애 발생
SQL> !ls /home/oracle/oradata/testdb/undotbs01.dbf
SQL> create undo tablespace undo01
2 datafile '/home/oracle/oradata/testdb/undo01.dbf' size 10m ;
SQL> alter system set undo_tablespace=undo01 ;
SQL> drop tablespace undotbs1 ;
사용중이어서 삭제가 안됨
SQL> show parameter undo ;
SQL> shutdown immediate ;
SQL> shutdown abort ;
SQL> !
$ vi product/9.2/dbs/inittestdb.ora
_offline_rollback_segments=(_SYSSM6$) ← 사용 중이던 rollback segment 를 offline 시킨 후 삭제
SQL> startup
SQL> alter database datafile '/home/oracle/oradata/testdb/undotbs01.dbf' offline drop ;
SQL> alter dataase open ;
SQL> show parameter undo ;
SQL> drop tablespace undotbs1 including contents and datafile ;
SQL> shutdown immediate ;
SQL> !vi /home/oracle/product/9.2/dbs/inittestdb.ora
undo_management=auto ← manual 에서 auto 로 변경
# _offline_rollback_segments=(_SYSSM6$) ← 주석 처리
SQL> startup
SQL> show parameter undo ;
No archive log mode 에서 Undo tablespace 의 Data file 이 삭제된 장애 상황 복구하기
파라미터 파일 undo 관련 설정 수동 mode 로 수정
SQL> shutdown abort ;
SQL> !
$ vi /home/oracle/product/10g/dbs/inittestdb.ora
# undo_management=AUTO ← 기존 내용 주석 처리
undo_management=manual ← 추가
_offline_rollback_segments=(_SYSSMU1$, _SYSSMU2$, _SYSSMU3$, _SYSSMU4$, _SYSSMU5$, _SYSSMU6$, _SYSSMU7$, _SYSSMU8$, SYSSMU9$, _SYSSMU10$) ← 추가
재시작 후 복구
$ exit
SQL> startup mount ;
SQL> select name, status from v$datafile ;
장애 상황 반영 안됨
SQL> alter database datafile '/home/oracle/oradata/testdb/undotbs01.dbf' offline drop ;
SQL> alter database open ;
SQL> select status from v$instance ;
SQL> @dd
offline 됨
신규 undo tablespace 생성 후 반영
SQL> create undo tablespace undo
2 datafile '/home/oracle/oradata/testdb/undo01.dbf' size 10m ;
SQL> shutdown immediate ;
SQL> !
$ vi /home/oracle/product/10g/dbs/inittestdb.ora
undo_tablespace=UNDO ← 신규 undo 로 변경
$ exit
SQL> startup
SQL> show parameter undo ;
SQL> @dd
장애난 undo tablespace 삭제
SQL> drop tablespace undotbs1 including contents add datafile ;
drop 안됨
SQL> select segment_name, owner, tablespace_name, status
2 from dba_rollback_segs ;
SQL> shutdown immediate ;
SQL> !
$ vi /home/oracle/product/10g/dbs/inittestdb.ora
_offline_rollback_segments=(_SYSSMU11$) ← 강제로 offline 시킴
$ exit
SQL> startp
SQL> @dd
SQL>drop tablespace undotbs1 including contents and datafiles ;
07 Redo log 장애와 Log Miner
Case 별 Redo log file 장애와 대처 방법
Redo log file
삭제 장애 발생
↓
DB Open yes→ Alter database clear unarchived
logfile group N :
↓no
Current yes→ Shutdown yes→ Recover database until cancel
immediate resetlogs open
↓no ↓no
archive yes→ 장애그룹삭제/ Backup/ yes→ 백업 파일 복원 후 불완전 복구 수행
open archive
↓no ↓no
Alter database clear unarchived 히든 파라미터 사용하여 강제 오픈
logfile group N : (_allow_resetlogs_corruption=true)
※ Redo log file 이 삭제되어 장애가 발생한 경우는 우선 Open 인지 아닌지 확인한 후
Current 유무를 확인하고 Archive 유무를 확인하는 순서로 복구 플랜을 작성하면 됩니다.
1) 대기 중인 그룹의 멤버 1개만 장애 발생할 경우
SYS> !
$ vi /home/oracle/admin/testdb/bdump/alert_testdb.log
멤버가 하나 지워져도 운영에는 문제가 없기 때문에 수시로 alert log 파일을 확인하여 장애를 확인하여야 한다.
SYS> alter database drop logfile member '/home/oracle/oradata/testdb/redo01_a.log' ;
SYS> alter database add logfile member '/home/oracle/oradata/testdb/redo01_a.log' to group 1;
2) 대기 중인 그룹 중 archive 완료된 그룹이 지워진 경우
장애 생긴 그룹 삭제 후 재생성하고 다시 open
SYS> alter database drop logfile group 2 ;
SYS> alert database add logfile group 2
2 ('/home/oracle/oradata/testdb/redo02_a.log',
3 '/home/oracle/oradata/testdb/redo02_b.log') size 5m ;
SYS> alter databse open ;
3) 대기 중인 그룹 중 archive 완료 안 된 그룹이 지워진 경우 - DB OPEN 상태
SYS> atler database clear unarchived logfile group 1 ; ← control file 의 정보를 보고 해당 그룹의 멤버를 자동으로 삭제 후 재생성함
이 명령어로 redo log group 을 재생성하게 되면 archive log file 이 중간에 비어버리기 때문에 반드시 DB 전체를 full backup 받아야 함
SYS> alter database drop logfile group 1 ; ← 문제가 되는 그룹을 삭제
SYS> !
$ rm -fr /home/oracle/oradata/testdb/redo01* ← 파일 삭제
$ exit
SYS> alter database add logfile group 1
2 ('/home/oracle/oradata/testdb/redo01_a.log',
3 '/home/oracle/oradata/testdb/redo01_b.log') size 5m ; ← 그룹 추가
4) 대기 중인 그룹 중 archive 완료 안 된 그룹이 지워진 경우 - DB CLOSE 상태
ORA_00257: archiver error.Connect internal only, until freed.
SYS> alter database drop logfile group 1 ;
삭제가 안되는 이유는 archive log mode 인 db 는 redo 가 archive 완료되기 전에는 절대 삭제도 업데이트도 하지 않기 때문
SYS> atler database clear unarchived logfile group 1 ; ← 삭제 후 자동재생성
open 일 때와 다르게 재생성된 redo log file 을 그냥 사용해도 된다.
5) Current 그룹이 삭제 된 경우 - DB OPEN 상태
해결방법은 3번과 동일하다.
SYS> atler database clear unarchived logfile group 3 ;
SYS> alter database drop logfile group 1 ; ← 문제가 되는 그룹을 삭제
SYS> !
$ rm -fr /home/oracle/oradata/testdb/redo03*
$ exit
SYS> alter database add logfile group 3
2 ('/home/oracle/oradata/testdb/redo03_a.log',
3 '/home/oracle/oradata/testdb/redo03_b.log') size 5m ;
※ DB 운영중(open) 일때 자동 재생성된 그룹은 반드시 수동으로 삭제 후 재생성 해야 함
6) Current 그룹이 삭제 된 경우 - DB Close - Shutdown immediate 로 종료됨
SYS> recover database until cancel ;
SYS> alter database open resetlogs ;
recover database until cancel 명령은 현재 있는 redo log file 이나 archived redo log file 까지만 복구하라는 의미인데,
여기서는 데이터 파일에 모든 데이터가 정상 저장되었기 때문에 복구하지 않습니다.
이렇게 복구하여 OPEN 한 경우에는 반드시 전체 백업을 수행하여야 합니다.
7) Current 그룹이 삭제 된 경우 - DB Close - Shutdown abort 로 종료 - Data file backup 과 Archive log file 있음
과거 백업 받았던 모든 데이터 파일을 전부 복원하여 redo log 와 archive log 를 적용시켜 데이터를 복구하는 것인데
2번 그룹이 current 상태에서 삭제되었기 때문에 2번 그룹은 복구할 수 없습니다.
그래서 복구가 가능한 redo log 까지만 복구하고 2번 그룹을 찾을 때는 cancel 해야 합니다.
복구 가능한 부분까지만 복구하는 명령어인 recover database until cancel; 로 복구해야 합니다.
SYS> !cp /data/backup/close/*.dbf /home/oracle/oradata/testdb/
SYS> startup mount ;
SYS> recover database until cancel ;
sequence #16 나오면
cancel
SYS> alter database open resetlogs ;
8) Current 그룹이 삭제 된 경우 - DB Close - Abort 로 종료 - 백업 없음 (archive log 없음 - DB 를 no archive log mode 로 변경됨)
백업된 data file 이 없거나 archive log file 이 없다면 복구가 불가능하다
hidden parameter 를 사용하는 방법이 있는데 성공률이 높지 않고 실패할 경우 더 이상 방법이 없다
SYS> shutdown immediate ;
SYS> !
$ vi /home/oracle/product/10g/dbs/inittestdb.ora
_allow_resetlogs_corruption=true ← 이 히든 파라미터를 추가
$ exit
SYS> recover database until cancel ;
SYS> alter database open resetlogs ;
open 이 후 full export 로 백업받아야 한다.
data file 끼리 정보가 달라도 강제로 DB Open 해야 한다면 사용한다.
resetlogs 를 수행하면 data file 들의 정보는 변경없고 control file 의 정보만 변경된다.
9) 전체 Redo log 가 삭제된 경우 - DB Open (archive log mode 에서 작업)
SYS> @redo
3번 그룹이 seq# 이 가장 작은 것으로 확인됨
SYS> alter database clear unarchived logfile group 3 ;
SYS> alter database drop logfile group 3 ;
SYS> !rm -f /home/oracle/oradata/testdb/redo03*
SYS> alter database add logfile group 3
2 ('/home/oracle/oradata/testdb/redo03_a.log',
3 '/home/oracle/oradata/testdb/redo03_b.log') size 5m ;
SYS> @redo
SYS> alter system switch logfile ;
SYS> / ← 다시 행이 걸려서 ctrl + c
SYS> @redo
2번 그룹이 seq# 이 가장 작은 것으로 확인됨
SYS> alter database clear unarchived logfile group 2 ;
SYS> alter database drop logfile group 2 ;
SYS> !rm -f /home/oracle/oradata/testdb/redo02*
SYS> alter database add logfile group 2
2 ('/home/oracle/oradata/testdb/redo02_a.log',
3 '/home/oracle/oradata/testdb/redo02_b.log') size 5m ;
SYS> @redo
SYS> alter system switch logfile ;
SYS> / ← 다시 행이 걸려서 ctrl + c
SYS> @redo
1번 그룹이 seq# 이 가장 작은 것으로 확인됨
SYS> alter database clear unarchived logfile group 1 ;
SYS> alter database drop logfile group 1 ;
SYS> !rm -f /home/oracle/oradata/testdb/redo01*
SYS> alter database add logfile group 1
2 ('/home/oracle/oradata/testdb/redo01_a.log',
3 '/home/oracle/oradata/testdb/redo01_b.log') size 5m ;
SYS> @redo
log switch 를 여러번 발생시켜 이상 유무 확인
SYS> alter system switch logfile ;
SYS> /
SYS> /
10) 전체 Redo log 가 삭제된 경우 - DB Close - 백업 있는 상태
7) 번과 복구 방법 동일
11) 전체 Redo log 가 삭제된 경우 - DB Close - 백업 없는 상태
8)번과 복구 방법 동일
08 export / import
conventional path export - export 명령어가 수행되면 export client 가 메모리에 evaluation buffer 라는 곳을 만들고
db buffer cache 에 있는 데이터를 evaluation buffer 로 가져온 후 이곳이 다 차면 다시 디스크에 파일로 저장합니다.
direct path export - 여러 process 가 동시에 접근해서 사용할 경우 속도 저하가 심해지는 단점이 있음
전체 데이터베이스를 export 받으려면 export 를 수행하는 사용자가 DBA 권한을 가지고 있거나
exp_full_database 라는 role 을 가지고 있어야 합니다.
export 와 import 는 temporary tablespace 공간을 사용한다
export
1) conventional path Full export 받기
$ time exp system/oracle full=y file=/data/exp/full01.dmp \
> log=/data/exp/full_log01.log
2) direct path 로 Database Full export 받기
$ time exp system/oracle full=y file=/data/exp/full02.dmp \
> log=/data/exp/full_log02.log direct=y
3) export 를 저장하는 백업 파일을 분할해서 받기
$ time exp system/oracle full=y file=/data/exp/full03_1.dmp, \
> /data/exp/full03_2.dmp, \
> /data/exp/full03_3.dmp \
> filesize=100M
만약 용량 산정을 잘못하여 파일 개수를 부족하게 지정할 경우 아래와 같이 파일 이름을 추가로 입력하도록 프롬프트가 나오며
관리자가 다음 파일명을 입력해 주면 계속 진행 됩니다.
Export file: expdat.dmp > /data/exp/full03_4.dmp <----- 파일명을 직접 입력해 주세요.
4) 특정 테이블스페이스만 받기
$ time exp system/oracle file=/data/exp/ex_users.dmp \
> tablespaces=example,user
5) 특정 Table 만 exp
$ exp scott/tiger tables=emp,deptfile=emp_dept.dmp
6) 여러 사용자를 동시에 백업 받기
$ time exp system/oracle file=/data/exp/scott_hr.dmp owner=scott,hr
7) evaluation Buffer 값을 조정하면서 export 수행
실습용 테이블 생성
SYS> create table scott.test01
2 ( no number,
3 name varchar2(50),
4 address varchar2(50)
5 ) tablespace users
SYS>alter database datafile '/app/oracle/oradata/testdb/users01.dbf'
2 autoextend on;
SYS>alter database datafile '/app/oracle/oradata/testdb/undotbs01.dbf'
2 autoextend on;
SQL> begin
2 for i in 1..5000000 loop
3 insert into scott.test01
4 values (i , dbms_random.string('A',49),
5 dbms_random.string('Q',49) );
6 end loop;
7 commit;
8 end ;
9 /
test 1) evaluation Buffer 값을 설정하지 않고 export 수행
$ time exp scott/tiger file=/data/exp/test01_1.dmp tables=test01
Test 2) evaluation Buffer 값을 1M 로 설정 후 export 수행
$ time exp scott/tiger file=/data/exp/test_01_2.dmp tables=test01 \
> buffer=1024000
Test 3) evaluation Buffer 값을 10M 로 설정 후 export 수행
$ time exp scott/tiger file=/data/exp/test_01_3.dmp tables=test01 \
> buffer=10240000
Test 4) evaluation Buffer 값을 20M 로 설정 후 export 수행
$ time exp scott/tiger file=/data/exp/test_01_4.dmp tables=test01 \
> buffer=20480000
Test 5) Direct Path 로 export 수행
$ time exp scott/tiger file=/data/exp/test_01_5.dmp tables=test01 \
> direct=y
8) 일반 사용자로 Full export 수행
SYS> grant exp_full_database to scott ;
$ time exp scott/tiger full=y \
> file=/data/backup/export/test7.dmp buffer=10240000
9) parameter file 을 이용한 export 수행하기
$ exp system/oracle parfile=full.dat
10) 특정 조건만 export 받기 - query 옵션 사용하기
Test1) emp 테이블에서 이름 첫 글자가 F인 사람만 export 받기
(OS에서 사용하는 ‘,”,< 등의 문자를 쓸 경우 \(escape 문자)를 꼭 써줘야 합니다
$ exp scott/tiger query=\"where ename like \'F%\'\" tables=emp\
> file=/data/exp/test06.dmp
Test2) emp 테이블에서 job 이 CLERK 이고 급여가 1000 이상인 사람만 export 받기
$ exp scott/tiger query=\"where job=\'CLERK\' and sal \> 1000\"
> file=/data/exp/scott2.dmp tables=emp
Test 3) parameter file 에서 query 옵션 사용하기 – escape 문자 안 써도 됨.
$ vi par2.dat
tables=emp query="where job='CLERK' and sal > 1000"
file=/data/exp/scott3.dmp
:wq!
$ exp scott/tiger parfile=par2.dat
10) schema 별로 자동 export 백업 받는 스크립트
$ vi exp_script.sh
export LANG=C
export ORACLE_BASE=/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11g
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=testdb
sqlplus /nolog << EOF3
conn / as sysdba
set head off
set time off
set timing off
set feedback off
set echo off
set line 200
col name for a100
spool /app/oracle/exp.tmp
select 'mkdir -p /data/backup/exp/'||to_char(sysdate,'YYYY-MM-DD-HH24-MI-SS') from dual;
select distinct 'exp system/oracle'||'owner='||lower(owner)||' file=/data/backup/exp/'
||to_char(sysdate,'YYYY-MM-DD-HH24-MI-SS')||'/'||lower(owner)||'.dmp'||' filesize=100m direct=y'
from dba_tables where owner not
in('SYS','DBSNMP','WMSYS','IX','SYSTEM','OE','PM','EXFSYS','CTXSYS','OLAPSYS','MDSYS','SYSMAN','L
OADER','XDB','ORDSYS','OUTLN','TSMSYS','DMSYS');
spool off
!cat /app/oracle/exp.tmp | egrep -v SQL | egrep -v SYS > /app/oracle/exp.sh
!sh /app/oracle/exp.sh
exit
EOF3
$ sh exp_script.sh
import
1) DBA 로 전체 데이터 import 수행하기
$ imp system/oracle file=/data/exp/full11.dmp ignore=y full=y
2) 특정 사용자의 데이터만 import 수행하기
$ imp system/oracle file=/data/exp/full11.dmp fromuser=scott \
> tables=test01 ignore=y
3) SCOTT 사용자의 test02 테이블을 HR 사용자 소유로 변경하기
$ imp system/oracle file=/data/exp/test02.dmp \
> fromuser=scott touser=hr ignore=y
4) 실제 데이터는 import 하지 않고 DDL 문장만 추출하기
show=y 옵션 사용하는 것과 indexfile 옵션을 사용하는 방법
$ imp system/oracle file=/data/backup/export/test02.dmp full=y \
> show=y log=test02.log
$ vi test02.log
log 파일을 열어서 편집
export 와 import 에 대한 궁금증 모음
Q1) Export 시 수행과정을 화면에 안 나오게 하는 방법은 ?
A1) 이 방법은 OS 별로 조금 다른데 여기서는 Linux 를 기준으로 설명하겠습니다.
$ exp scott/tiger file=/data/backup/export/test02.dmp \
> tables=test01 > /dev/null 2>&1
- CShell : exp …… > &/dev/null
- Ksh , Bsh , Bash : exp ….. >/dev/null > 2>&1
Q2) export 시 Buffer 와 Recordlength 의 차이는 무엇인가요?
A2) export 시 Conventional Path 를 이용할 경우 (기본값) 메모리에 evaluation Buffer 를 생성하고
DB Buffer Cache 에 있는 내용을 evaluation buffer 로 가져 온 후 Binary 로 변환 작업을 해서 OS 파일로 저장을 하게 됩니다.
여기서 Buffer 란 evaluation Buffer 크기를 결정하는 파라미터이고
recordlength 는 buffer 의 내용을 OS 파일로 내려쓸 때 사용하는 레코드의 크기를 결정하는 파라미터입니다.
이 두 가지 파라미터의 크기는 OS 블록의 배수로 설정하시는 것이 좋습니다.
그리고 DB Buffer Cache의 내용을 evaluation buffer 로 가져올 때 한 건씩 가져오는 것이 아니라
여러 건 의 데이터를 한꺼번에 가져오게 되는데 그것을 Array fetch 라고 합니다.
Q3) 큰 데이터를 export 또는 import 할 때 화면이 정지된 것 같이 보이는데 입력되고 있는지 아닌지 알 수 있는 방법이 있을까요?
A3) export / import 옵션 중에 feedback 이라는 옵션이 있습니다 . feedback=정수값 이렇게 사용하며 진행되는 과정을 눈에 보여줍니다.
Q4) export 할 때 한 건씩이 아니라 Array Fetch 로 추출된다고 하는데 한번에 몇 개의 Row가 추출되나요?
A4) 이 값은 Buffer(evaluation buffer) 크기와 관련이 있습니다.
하나의 row가 export 시 차지하는 양은 각 column 크기의 합 + 4 X (column 개수)로 구해집니다.
이 자료를 기준으로 해서 한번 Fetch될 때 Row 수는 Buffer size / 1 row size 입니다.
이를 이용하면 export 된 파일의 총 크기는 1 row size X row 수 입니다.
Q5) import 를 수행하다 보면 가끔 core dump/segmentation fault 가 발생합니다. 원인은 무엇이며 해결방법은 어떻게 하나요?
A5) 오라클은 데이터를 저장하기 때문에 character set 이라는 것이 있습니다.
국내에서는 주로 US7ASCII 또는 KO16KSC5601 , KO16MSWIN949 를 사용하는 데
export 받은 DB와 import 하는 DB의 캐릭터 셋이 다를 경우 위와 같은 에러가 발생합니다.
해결방법은 캐릭터 셋을 일치 시킨 후 export 를 받는 것이 정석이지만
이미 받아 버린 덤프 파일이라면
convert 프로그램을 이용하여 import 하는 곳의 캐릭터 셋과 동일하 게 변경한 후 import 를 수행하면 됩니다.
참고로 현재 데이터베이스의 캐릭터 셋을 확인하시려면 아래의 SQL을 사용하시면 됩니다.
SYS> select value from nls_database_parameters w here parameter='NLS_CHARACTERSET';
Q6) Array Fetch 란 무엇인가요?
A6) export 는 select 문장을 만들어서 DB Buffer Cache 에 있는 데이터를 evaluation buffer 가져옵니다.
만약 한번에 한 문장씩 가져와서 파일로 내려쓴다면 시간이 너무 오래 걸리고 파일에 I/O 하는 회수도 아주 많아 질 것입니다.
그래서 export 는 evaluation Buffer 에 일정량의 데이터가 쌓이게 되면 한꺼번에 파일에 내려씁니다.
즉 만약 10개의 row 를 파일에 export 해야 한다면 1번에 1개씩 하면 10번 I/O가 발생하지만
10개를 한번에 내려쓴다면 I/O는 한번만 일어나면 되겠죠.
이렇게 해서 I/O를 줄여서 속도를 높이는 방법이 array fetch라는 기법입니다.
Q7) import 시에 array insert 는 무엇인가요?
A7) export 와 마찬가지로 import 시에도 한번에 1 row 씩 insert 를 하게 되면 너무 비효율적 입니다.
그래서 import 할 때도 여러 건의 데이터를 한꺼번에 insert 하게 되는데 이것이 array insert 입니다.
Q8) import 시에 “ABNORMAL END OF FILE” 라는 에러메시지는 어떤 의미인가요?
A8) 이 에러는 export 받은 파일에 문제가 있음을 의미합니다. 다시 export 받아야 합니다.
Q9) sys 소유자의 객체는 왜 export 가 안되나요?
A9) sys 계정은 dictionary 객체들을 소유하고 있습니다.
새로운 데이터베이스는 이미 고유의 딕셔너리를 가지고 있을 것입니다.
Sys 계정은 DB를 관리하는 계정으로 sys계정의 객체를 export 하고 import 한다는 것은
아주 큰 위험 부담이 있고 부하도 많이 걸리는 작업이라
sys계정은 export 를 수행할 수 없도록 되어 있습니다.
또 개인적인 업무를 하기 위해 sys 계정으로 접속하시는 것은 권장사항이 아니므로 주의해야 합니다.
Q10) A 사용자 소유의 테이블에 B 사용자가 인덱스를 만들었다면
A사용자가 export 받을 때 B 사용자의 인덱스도 함께 export 받을 수 있나요?
A10) A 사용자 계정으로 export 를 수행하면 받을 수 없으나 DBA 권한으로 받으면 받을 수 있습니다.
Q11) offline 상태의 테이블스페이스도 export 받을 수 있나요?
A11) 아닙니다. Exp 는 select 와 동일한 원리이므로 offline 이나 shutdown 상태에서는 사용할 수 없습니다.
심화1 tables 옵션으로 exp 받은 dmp 파일 분석하기
exp 수행한 후 생성되는 dmp 파일 절대 수정하시면 안됩니다
$ exp scott/tiger file=scott.dmp tables=emp,dept
$ vi scott.dmp
위 파일에서 많은 정보를 얻을 수 있는데 중요한 부분들만 발췌해서 살펴보겠습니다.
1 ^C^CNEXPORT:V11.02.00
2 USCOTT
3 RTABLES
4 8192
우선 1번줄은 export 버전이고 2번 줄에 보면 exp 를 수행한 사람이 나옵니다.
3번줄은 table 을 export 한 것이고 4번줄은 블록사이즈가 됩니다.
Sat Apr 23 0:31:0 2013scott.dmp
그 다음 아랫줄에 보면 exp 를 수행한 시간 날짜 정보와 파일이름이 나옵니다.
12 TABLE "EMP"
13 CREATE TABLE "EMP"("EMPNO" NUMBER(4, 0), "ENAME" VARCHAR2(10), "JOB"VARCHAR2(9),
"MGR" NUMB ER(4, 0), "HIREDATE" DATE, "SAL" NUMBER(7, 2), "COMM" NUMBER(7, 2),
"DEPTNO" NUMBER(2, 0)) P CTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT) TABLESPACE "USERS"LOGGING NOCOMPRESS
14 INSERT INTO "EMP"("EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM",
"DEPTNO") VALUE
13번 째 줄에 보면 CREATE TABLE “EMP”.. 라는 부분이 나오면서 table 을 생성합니다.
그리고 14번 줄에 보면 데이터를 입력합니다.
18 CREATE UNIQUE INDEX "PK_EMP" ON "EMP" ("EMPNO") PCTFREE 10 INITRANS 2
MAXTRANS 255 STORAGE( INITIAL 65536 NEXT 1048576MINEXTENTS 1 FREELISTS 1 FREELIST
GROUPS 1 BUFFER_POOL DEFAULT) TA BLESPACE "USERS" LOGGING
19 ANALSTATS IS "EMP"
20 Y^@BEGIN DBMS_STATS.SET_INDEX_STATS(NULL,'"PK_EMP"',NULL,NULL,NULL,14,1,14,1,1,1,0,6);
END;
21 ALTER TABLE "EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")USING
INDEX PCTFREE 10 INITR ANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576
MINEXTENTS 1 FREELISTS 1 FREELIST GROUP S 1 BUFFER_POOL DEFAULT) TABLESPACE
"USERS" LOGGING ENABLE
13번, 14번 줄에서 테이블 생성 후 데이터까지 입력 완료하고 나면 18번 줄에서 인덱스를
생성합니다. 그리고 21번 줄에 제약조건을 추가합니다.
그리고 각종 통계정보를 생성하게 됩니다.
Dept 테이블도 이 과정을 반복하게 됩니다.
우리가 여기서 알 수 있는 사실은 exp 받은 dmp 파일은 나중에 imp 를 하기 위해서 바이너리
파일로 변환되어서 만들어 진다는 것과 내부 순서를 보면 Create Table - > insert -> Create index
-> add constraints 과정으로 진행된다는 것입니다.
심화2 tablespaces 옵션으로 exp 받은 dmp 파일 분석하기
$ exp system/oracle file=users.dmp tablespaces=users
$ vi users.dmp
1 ^C^CNEXPORT:V11.02.00
2 DSYSTEM
3 RTABLES
4 8192
가장 앞부분 1번 줄은 export 버전이며 2번 줄은 exp 를 수행한 계정이고 3번 줄은 table 들을
exp 받았다는 뜻이며 4번 줄은 block 의 크기입니다.
11 CONNECT SCOTT
12 TABLE "BONUS"
13 CREATE TABLE "BONUS"
특이한 점은 11번 줄에 SCOTT 로 접속한 부분이 나옵니다. Export 를 수행할 때 분명히 system
계정으로 수행했지만 exp 유틸이 자동으로 USERS Tablespace 를 default tablespace 로 사용하는
schema가 scott 임을 찾아서 해당 계정으로 로그인 한 것입니다.
그 후에 해당 사용자가 USERS Tablespace 에 생성한 테이블들 단위로 exp 받게 됩니다.
여기서부터는 위에서 Tables 옵션으로 exp 받은 것과 동일한 순서로 진행이 됩니다.
즉 TABLESPACES 옵션을 주었다 하더라도 dmp 파일에서 확인되는 것과 같이 imp 할 때
Tablespace 를 생성하고 Table 을 만드는 것이 아니라는 뜻이며 imp 하길 원하시는 서버에
Tablespace 를 먼저 만들어 두고 imp 를 해야 한다는 뜻입니다.
심화3 clob data export/import 수행하기
Step 1. Clob 컬럼을 포함한 테스트 테이블 생성
SQL> create tablespace ts_lob
2 datafile '/home/oracle/oradata/testdb/ts_lob01.dbf' size 1M
Tablespace created.
SQL> create table scott.clobtest
2 (no number , name varchar2(10), contents clob)
3 lob(contents) store as (tablespace ts_lob);
Table created.
SQL> insert into scott.clobtest
2 values (1,'AAA','BBBBB');
-- 현재 LOB 목록 확인하는 방법
SQL> col owner for a10
SQL> col table_name for a10
SQL> col column_name for a10
SQL> col segment_name for a30
SQL> col tablespace_name for a10
SQL> select owner,table_name,column_name,segment_name,tablespace_name
2 from dba_lobs
3 where table_name='CLOBTEST'
OWNER TABLE_NAME COLUMN_NAM SEGMENT_NAME TABLESPACE
---------- ------------------ -------------------- ----------------------------------- ----------------
SCOTT CLOBTEST CONTENTS SYS_LOB0000054504C00003$$ TS_LOB
Step 2. Export 수행
SQL> !
[oracle@localhost ~]$ exp scott/tiger tables=clobtest file=clobtest.dmp
Step 3. Import 수행
Import 를 수행하기 위해 기존 테이블과 테이블 스페이스(ts_lob)를 먼저 삭제하겠습니다.
테이블 스페이스 (ts_lob)를 삭제하는 이유는 해당 테이블스페이스가 없을 경우 import 시에 에러
가 발생함을 보여주기 위해서입니다.
SQL> drop table scott.clobtest purge;
Table dropped.
SQL> drop tablespace ts_lob including contents anddatafiles;
Tablespace dropped.
SQL>!
[oracle@localhost ~]$ imp scott/tiger file=clobtest.dmp fromuser=scott ignore=y
]$ exit
exit
SQL> create tablespace ts_lob
2 datafile '/home/oracle/oradata/testdb/ts_lob01.dbf' size 1M;
!
[oracle@localhost ~]$ imp scott/tiger file=clobtest.dmp fromuser=scott ignore=y
Import: Release 10.2.0.5.0 - Production on Tue Mar 1 07:41:33 2013
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
import done in KO16MSWIN949 character set and AL16UTF16 NCHAR character set
import server uses KO16KSC5601 character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table "CLOBTEST" 1 rows imported
Import terminated successfully without warnings.
에러 없이 잘 수행됩니다.
이상으로 예전 버전부터 데이터 이동에 널리 사용되고 있는 exp/imp 에 대해서 살펴보았습니다
09 Datapump
1) Datapump 의 장점
(1) 작업 관리의 편의성
Datapump 이전의 exp/imp 방법은 작업이 일단 시작되면 끝날 때까지 제어를 할 수 없었습니다.
예를 들어 어떤 필요성에 의해 일시 중단 시켰다가 다시 시작한다든지 하는 작업을 할 수 없었지만
datapump 는 job 의 제어가 가능해 훨씬 편리해 졌습니다.
(2) 필요한 디스크 공간의 예측
이전 버전의 exp/imp 작업은 작업하다가 디스크 공간의 부족으로 몇 시간 동안 했던 작업을 취소하고
디스크를 추가 후 (또는 파일 삭제 등으로 공간 확보 후) 다시 작업을 수행하는 경우가 종종 있었으나
datapump 는 ESTIMATE 파라미터를 사용하여 해당 작업 시 필요한 디스크 공간을 미리 알 수 있습니다.
(3) 원격지 DB 에 작업 수행 가능
DB Link 라는 기능을 통해 원격지에 있는 데이터베이스에 expdp / impdp 작업을 수행할 수 있습니다.
(4) remapping 기능 지원
스키마 변경이나 테이블 스페이스 변경, 데이터 파일 변경까지 가능합니다.
(5) dump 작업하면서 압축을 동시에 진행
용량이 큰 데이터의 경우 압축을 동시에 진행하므로 dump file 용량을 획기적으로 줄일 수 있습니다.
(6) 아주 빨라진 작업 속도
서버의 환경에 따라 다르지만 일반적으로 이전 버전의 exp/imp 와 비교해서 평균적으로 약 20 배 이상의 성능 향상을 가져와서
대량의 작업도 아주 빨리 수행 할 수 있게 되었습니다.
2) 사용 전 환경 설정하기
Datapump는 export / import 와 다르게 유틸리티가 직접 OS 파일에 I/O를 할 수 없고
오라클에 directory 라는 객체를 통해서 간접으로 접근이 가능합니다.
그래서 datapump를 사용하려면 미리 directory 가 만들어져 있어야 하며 datapump를 수행하는
사용자는 그 directory에 접근할 수 있는 권한이 있어야 합니다.
이 기능을 통해 DBA는 datapump 의 보안관리까지 가능하게 되었습니다.
Datapump를 사용하기 위해서 디렉토리를 생성하고 권한 설정을 하도록 하겠습니다.
$ mkdir /data/dp
$ sqlplus / as sysdba
SQL> create or replace directory datapump as '/data/dp';
SQL> grant read , write on directory datapump to scott;
SQL> grant create any directory to scott;
Datapump는 기존 export 와 구분하기 위해 expdp 로 , import 와 구분하기 위해 impdp 라는 명령을 사용합니다.
3) expdp 실행 모드
(1)Full 모드
Full 이란 파라미터를 사용하며 데이터베이스 전체를 export 받을 수 있습니다.
DBA 권한을 가지고 있거나 export_full_database 권한을 가지고 있어야 수행할 수 있습니다.
(2) schema 모드
Schemas 파라미터를 사용하여 특정 스키마의 전체를 export 받을 수 있습니다.
export 에서의 owner 옵션과 동일합니다.
(3) Tablespace 모드
Tablespaces 파라미터를 사용하며 해당 테이블스페이스에 속한 모든 테이블을 받을 수 있습니다.
만약 transport_tablespace 파라미터를 사용한다면 테이블과 테이블스페이스의 메타 데이터까지
export 받게 되어 다른 서버로 테이블스페이스 전체를 이동시킬 때 아주 유용하게 사용할 수 있
습니다. 단 transport_tablespace 를 사용하려면 양쪽 데이터베이스의 OS가 같아야 하고 block
size 와 characterset 도 같아야 한다는 단점이 있습니다.
(4) table 모드
Tables 파라미터를 사용하며 여러 개의 테이블을 export 받으려면 ,(콤마)로 구 분해주면 됩니다.
*실습 14. scott 계정의 emp,dept 테이블만 백업 받기
$ expdp scott/tiger tables=emp,dept directory=datapump job_name=t1 \
> dumpfile=emp_dept.dmp
* 실습 15. scott schema 전부 백업 받기
$ expdp scott/tiger schemas=scott directory=datapump \
> dumpfile=sco tt01.dmp
*실습 16. DB 전체를 백업 받기
$ expdp system/oracle full=y directory=datapump dumpfile=full01.dmp \
> job_name=a
*실습 17. 일시 중단 후 다시 작업하기
Expdp를 수행하다 보면 서버에 부하를 많이 줘서 일시 중단했다가 나중에 이어서 다시 작업 할 때가 있습니다.
그런 상황일 때 어떻게 하는지 살펴보겠습니다.
DB 전체를 백업 받다가 중간에 Ctrl+c를 눌러 취소하겠습니다.
$ expdp system/oracle full=y directory=datapump \
> dumpfile=full02.dmp job_name=a
<-Ctrl + C 를 눌러서 취소했습니다.
Export> stop_job <- 일시 중단 시킴
$ sqlplus sys/oracle as sysdba;
SQL> col owner_name for a10
SQL> col job_name for a10
SQL> col operation for a10
SQL> col job_mode for a10
SQL> SELECT owner_name , job_name,operation,job_mode,state
2 FROM dba_datapump_jobs;
$ expdp system/oracle attach=system.a <-일시 중단된 작업에 재 접속
Export> start_job
Export> exit
[oracle@localhost ~]$ sqlplus / as sysdba;
SQL> col owner_name for a10
SQL> col job_name for a10
SQL> col operation for a10
SQL> col job_mode for a10
SQL> SELECT owner_name , job_name,operation,job_mode,state
2 FROM dba_datapump_jobs ;
Export > 상태에서 만약 해당 작업을 완전히 취소하고 싶으면 kill_job 입력하시면 됩니다.
* 실습 18. 비정상적으로 종료된 job 취소하기
[oracle@localhost ~]$ mkdir /data/dp_a
[oracle@localhost ~]$ mkdir /data/dp_b
[oracle@localhost ~]$ mkdir /data/dp_c
[oracle@localhost ~]$ mkdir /data/dp_d
$ sqlplus sys/oracle as sysdba
SQL> startup
$ expdp system/oracle full=y directory=dp_a dumpfile=full01.dmp \
> job_name=dp_a
SQL> create directory dp_a as '/data/dp_a';
SQL> create directory dp_b as '/data/dp_b';
SQL> create directory dp_c as '/data/dp_c';
SQL> create directory dp_d as '/data/dp_d';
SQL> grant read,write on directory dp_a to scott;
SQL> grant read,write on directory dp_b to scott;
SQL> grant read,write on directory dp_c to scott;
SQL> grant read,write on directory dp_d to scott;
Step 2. Datapump 수행 중 비정상 종료
SQL> exit
$ expdp system/oracle full=y directory=dp_a dumpfile=full01.dmp \
> job_name=dp_a
Ctrl+C 눌러서 작업 정지 시킴.
Export> stop
Are you sure you wish to stop this job ([yes]/no): yes
$ expdp system/oracle full=y directory=dp_b dumpfile=full02.dmp \
> job_name=dp_b
Export> stop
Are you sure you wish to stop this job ([yes]/no): yes
$ expdp system/oracle full=y directory=dp_c dumpfile=full03.dmp \
> job_name=dp_c
Export> stop
Are you sure you wish to stop this job ([yes]/no): yes
$ sqlplus sys/oracle as sysdba;
SQL> col owner_name for a10
SQL> col job_name for a10
SQL> col operation for a10
SQL> col job_mode for a10
SQL> SELECT owner_name , job_name, operation, job_mode, state
2 FROM dba_datapump_jobs ;
Step 3. 파일을 수동으로 삭제하는 장애 발생
SQL> !rm -fr /data/dp_a/*
SQL> !rm -fr /data/dp_b/*
SQL> !rm -fr /data/dp_c/*
SQL> exit
Step 4. 중지되었던 job 에 다시 접속 시도 – 에러 발생함
$ expdp system/oracle attach=dp_a
( 지면 관계상 중간 내용은 생략합니다 )
ORA-31640: unable to open dump file "/data/dp_a/full01.dmp" for read
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
$ expdp system/oracle attach=dp_b
(지면 관계상 중간 내용은 생략합니다)
ORA-39002: invalid operation
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/data/dp_b/full02.dmp" for read
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
$ expdp system/oracle attach=dp_c
(지면 관계상 중간 내용은 생략합니다)
ORA-39002: invalid operation
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/data/dp_c/full03.dmp" for read
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Step 5. 위 문제의 정보를 가진 마스터 테이블 확인 후 삭제
SYS> set line 200
SYS> col owner.object for a15
SYS> SELECT o.status, o.object_id, o.object_type, o.owner||'.'||object_name "OWNER.OBJECT"
2 FROM dba_objects o, dba_datapump_jobs j
3 WHERE o.owner=j.owner_name
4 AND o.object_name=j.job_name
5 AND j.job_name NOT LIKE 'BIN$%'
6 ORDER BY 4,2;
SYS> drop table system.dp_a;
SYS> drop table system.dp_b;
SYS> drop table system.dp_c;
SYS> SELECT owner_name , job_name, operation, job_mode, state
2 FROM dba_datapump_jobs ;
* 실습 19. 여러 사용자의 테이블 한꺼번에 expdp 받기
$ expdp system/oracle directory=datapump dumpfile=scott16.dmp \
> tables=scott.emp ,hr.departments
*실습 20. 병렬로 expdp 작업 하기
$ expdp system/oracle full=y directory=datapump dumpfile=full04.dmp \
> job_name=a parallel=4
*실습 21. 병렬로 위치 분산하여 expdp 작업하기
$ expdp system/oracle full=y parallel=4 \
> dumpfile=DATADIR1:full1%U.dat, \
> DATADIR2:full2%U.dat, \
> DATADIR3:full3%U.dat, \
> DATADIR4:full4%U.dat \
> filesize=100M
*실습 22. 파라미터 파일 사용해서 expdp 수행 – 여러 개의 파일로 분할
$ vi expdp_pump.par
userid=system/oracle
directory=datapump
job_name=datapump
logfile=expdp.log
dumpfile=expdp_%U.dmp
filesize=100M
full=y
:wq!
$ expdp parfile=expdp_pump.par
* 실습 23. parameter 파일 이용해서 impdp 작업하기
$ vi impdp.par
userid=system/manager
directory=datapump
job_name=datapump
logfile=impdp_pump.log
dumpfile=expdp_%U.dmp
full=y
table_exists_action=append
$ impdp parfile=impdp.par
* 실습 24. Impdp 병렬 작업하기
$ impdp system/manager parallel=4 \
> dumpfile= DATADIR1:full1%U.dat, \
> DATADIR2:full2%U.dat, \
> DATADIR3:full3%U.dat, \
> DATADIR4:full4%U.dat \
> Table_exists_action=append
* 실습 25. Import 수행하지 않고 DDL 문장만 추출하기
$ impdp system/manager directory=datapump \
> dumpfile=expdp_%U.dmp sqlfile=datapump.dat
* 실습 26. 설정된 Directory 경로 확인하기
SQL> set line 200
SQL> col owner for a10
SQL> col directory_name for a25
SQL> col directory_path for a60
SQL> select * from dba_directories ;
* 실습 27. 일자 별 schema 별로 자동 백업 받는 스크립트
이번 실습은 현업에서 많이 사용하는 것으로 마이그레이션 작업 과 같은 일을 할 때
AS-IS 서버(원본서버)에서 모든 schema 별로 datapump를 수행해서 To-Be (신규서버)로 옮기게 됩니다.
그때 AS-IS 서버에 schema 가 너무 많을 경우 일일이 수동으로 찾아서 하기는 너무 힘들기 때문에
아래에서 소개하는 스크립트를 사용하면 작업이 많이 수월해 질 것입니다.
아래 스크립트는 특정 백업 디렉토리(여기서는 /data/backup/expdp) 아래에
백업을 수행하는 년-월-일-시간 형식으로 디렉토리를 자동으로 만들고
그 아래에 Schema 별로 dmp 파일과 log 파일을 생성하게 만들어져 있습니다.
Expdp 에 많은 옵션이 있어서 기본적인 옵션만 주고 만들었지만
원하는 옵션이 있으시다면 스크립트를 조금만 수정해서 사용하시면 될 것입니다.
$ vi expdp_script.sh
export LANG=C
export ORACLE_BASE=/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11g
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=testdb
sqlplus /nolog << EOF3
conn / as sysdba
set head off
set time off
set timing off
set feedback off
set echo off
set line 200
col name for a100
spool /app/oracle/expdp.tmp
SELECT '!mkdir -p /data/backup/expdp/'||to_char(sysdate,'YYYY_MM_DD_HH24') FROM dual;
-- 아래 줄은 화면상 줄 바꿈 했지만 실제로는 ; 까지 한 줄로 이어서 쓰세요
SELECT 'create or replace directory datapump_'||to_char(sysdate,'YYYY_MM_DD_HH24')||
' as '||''''||'/data/backup/expdp/'||to_char(sysdate,'YYYY_MM_DD_HH24')||''''||';' FROM dual;
-- 아래 줄은 화면상 줄 바꿈 했지만 실제로는 ; 까지 한 줄로 이어서 쓰세요
SELECT distinct 'grant read , write on directory '||' datapump_'||to_char(sysdate,'YYYY_MM_DD_HH24')||' to '||
lower(owner) FROM dba_tables WHERE owner not
in('SYS','DBSNMP','WMSYS','IX','SYSTEM','OE','PM','EXFSYS','CTXSYS','OLAPSYS','MDSYS','SYSMAN','LOADER','XDB',
'ORDSYS','OUTLN','TSMSYS','DMSYS');
-- 아래 줄은 화면상 줄 바꿈 했지만 실제로는 ; 까지 한 줄로 이어서 쓰세요
SELECT distinct '!expdp system/oracle'||' schemas='||lower(owner)||
job_name='||lower(owner)||'_datapump_'||to_char(sysdate,'YYYY_MM_DD_HH24')||' directory =
datapump_'||to_char(sysdate,'YYYY_MM_DD_HH24')||' dumpfile='||lower(owner)||'_%U.dmp'||' logfile='||
lower(owner)||'.log'||' filesize=100m ' FROM dba_tables WHERE owner not
in('SYS','DBSNMP','WMSYS','IX','SYSTEM','OE','PM','EXFSYS','CTXSYS','OLAPSYS','MDSYS','SYSMAN','LOADER','XDB',
'ORDSYS','OUTLN','TSMSYS','DMSYS');
spool off
!cat /app/oracle/expdp.tmp | grep -v SQL | grep -v SYS > /app/oracle/expdp.sh
@/app/oracle/expdp.sh
exit
EOF3
:wq!
$ sh expdp_script.sh
SQL> !mkdir -p /data/backup/expdp/2013_02_20_16
$ cd /data/backup/expdp
$ ls
2013_02_20_16
$ cd 2013_02_20_16/
$ ls
hr.log hr_01.dmp scott.log scott_01.dmp sh.log sh_01.dmp
6) Datapump 작업 관리 및 모니터링 하기
DBA_DATAPUMP_JOBS 딕셔너리를 조회하여 현재 작업중인 작업들의 내용을 확인할 수 있습니다.
- owner_name : 작업 계정
- job_name : 작업 명칭
- job_mode : full , table , index , tablespace 등
- state : executing (수행중) , defining , undefined ,not running 등이 있습니다.
7) Datapump 를 사용하여 Tablespace 이동하기 (Transpotable Tablespace : TTS 사용하기)
이 실습을 하기 위해 동일한 OHEL5 + 11g R2 버전을 설치하여 Source DB 의 SID 를 testdb 라고 하고
Target DB 의 SID 를 testdb2 라고 하겠습니다.
아래의 실습은 두 서버가 모두 운영 중이라고 가정하고 진행한 것입니다)
- Source DB(AS-IS) 에서 작업합니다
Step 1. 테스트용 Tablespace 와 Table 을 생성합니다.
$ sqlplus sys/oracle as sysdba;
SYS>select instance_name from v$instance;
SYS>@dd
SYS>create tablespace conv_test1
SYS>create table scott.ctest1 (no number) tablespace conv_test1;
SYS>insert into scott.ctest1 values (1);
SYS>commit;
SYS>select * from scott.ctest1 ;
SYS>exit
Step 2. 이전하기 위해 Datapump 관련 환경설정을 합니다.
$ mkdir /data/con_test1
$ sqlplus sys/oracle as sysdba
SYS>create directory con_test1 as '/data/con_test1';
SYS>grant read,write on directory con_test1 to system;
Step 3. 이전하기 위한 작업을 진행합니다.
SYS>exec dbms_tts.transport_set_check('CONV_TEST1',true);
SYS>select * from transport_set_violations ;
SYS>@dd
SYS>alter tablespace conv_test1 read only; <-- 읽기전용 상태로 변경해야 합니다 !
SYS>exit
$ expdp system/oracle directory=con_test1 transport_tablespaces=conv_test1
file=conv.dmp logfile=conv.log
Step 4. Target DB (TO-BE) 로 파일을 복사합니다.
$ cp /app/oracle/oradata/testdb/conv_test01.dbf /data/testdb2/
$ cp /data/con_test1/* /data/conv_test2/
$ sqlplus / as sysdba;
SYS>alter tablespace conv_test1 read write ;
SYS>exit
여기까지 Source DB (AS-IS) 쪽의 작업은 완료되었습니다.
- 여기서부터는 Target DB (testdb2)에서 작업합니다.
Step 5. Source DB에서 사용하던 계정을 동일하게 생성 한 후 Datapump 관련 작업을 수행합니다.
SYS>select instance_name from v$instance;
INSTANCE_NAME
----------------
testdb2 <-- Target DB 입니다.
SYS>!
$ mkdir /data/conv_test2
$ exit
SYS>create directory conv_test1 as '/data/conv_test2';
SYS>grant read,write on directory conv_test1 to system;
SYS>!
Step 6. impdp 를 수행합니다.
$ cd /data/conv_test2/
$ ls
conv.dmp conv.log
$ impdp system/oracle directory=conv_test1 dumpfile=conv.dmp
logfile=conv2.log transport_datafiles='/data/testdb2/conv_test01.dbf'
Step 7. 확인합니다.
$ sqlplus / as sysdba;
SYS>select instance_name from v$instance;
INSTANCE_NAME
----------------
testdb2
SYS>@dd
TABLESPACE MB FILE_NAME
----------------- ---------- ---------------------------------------------
EXAMPLE 345.625 /data/testdb2/example01.dbf
USERS 10 /data/testdb2/users01.dbf
UNDOTBS1 10 /data/testdb2/undotbs01.dbf
SYSAUX 620 /data/testdb2/sysaux01.dbf
SYSTEM 720 /data/testdb2/system01.dbf
CONV_TEST1 10 /data/testdb2/conv_test01.dbf <-- 이전 완료되었습니다.
6 rows selected.
SYS>select * from scott.ctest1;
SYS>insert into scott.ctest1 values (2);
insert into scott.ctest1 values (2)
*
ERROR at line 1:
ORA-00372: file 6 cannot be modified at this time
ORA-01110: data file 6: '/data/testdb2/conv_test01.dbf'
SYS>alter tablespace conv_test1 read write;
SYS>insert into scott.ctest1 values (2);
SYS>select * from scott.ctest1;
NO
-------------
2
1
8) 12c Datapump New Feature
(1) LOGTIME 지원
Test 1) LOGTIME 사용 전
$ expdp scott/tiger directory=datapump dumpfile=ex1.dmp schemas=scott
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Test 2) LOGTIME 사용 후
[oracle@dbserver ~]$ expdp scott/tiger directory=datapump dumpfile=ex2.dmp schemas=scott \
> logtime=a
21-JUL-13 00:29:26.670: Estimate in progress using BLOCKS method...
21-JUL-13 00:29:28.031: Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
21-JUL-13 00:29:28.195: Total estimation using BLOCKS method: 1.312 MB
21-JUL-13 00:29:28.795: Processing object type SCHEMA_EXPORT/PRE_SCHEMA/
위와 같이 시간이 함께 기록되는 기능이 12c NF 로 등장합니다.
logtime 에 사용할 수 있는 옵션은 아래와 같습니다.
* * * * NONE NONE NONE -- 시간 정보를 남기지 않습니다 (기본값입니다)
* * * * STATUS STATUS STATUS -- 시간 정보를 현재 화면에만 남깁니다.
* * * * LOGFILE LOGFILE LOGFILE -- 시간 정보를 로그 파일에만 남기고 현재 화면에는 보여주지 않습니다.
* * * * ALL ALLALL ALL -- 시간 정보를 현재 화면에도 남기고 로그파일에도 남깁니다.
10 무정지 상태에서의 응급 복구
10.1 Clone DB 와 exp/imp 를 이용한 drop table 복구
Clone DB 생성 개념
TESTDB (운영) TESTDB2 (복구)
Parameter File 복사 → Parameter File
Control File 재생성 → Control File
Data File 백업 파일 → Data File
Redo log File 아카이브 → Redo log File
작업순서
1. 장애상황 발생
1) 테이블 스페이스 생성
2) 테이블 생성 후 데이터 입력
3) 테이블 삭제
2. Clone DB 용 파라미터 파일 생성 (pfile 사용)
3. 백업 데이터 파일 복원
4. 컨트롤 파일 재생성
5. 장애 난 시점 직전까지 시간 기반 복구로 테이블 복구
6. export 수행 후 운영 DB로 import 수행 (또는 DB Link 사용)
7. 복구 확인
1. 복구 서버 (testdb2) 용 파라미터 파일 생성
$ cd $ORACLE_HOME/dbs/
$ ls
$ cp inittestdb.ora inittestdb2.ora 파라미터 파일 복사
파라미터 파일 생성 후 db_name과 control_files 값을 clone db 값으로 변경
$ vi iniittestdb2.ora 내용 수정
db_name=testdb2
control_files=("/data/temp/clone/control01.ctl",
"/data/temp/clone/control02.ctl",
"/data/temp/clone/control03.ctl"
)
2. testdb2용 control file 생성
$ mkdir -p /data/temp/clone 디렉토리 생성
$ cp /data/backup/close/*.dbf /data/temp/clone/ 백업 파일 복사
$ exit
SYS> select instance_name from v$instance ;
SYS> alter database backup controlfile to trace as '/app/oracle/re.sql' ;
SYS> !
$ vi /app/oracle/re.sql resetlogs 로 생성 스크립트 만들기
주석 모두 지우고
1. DB 이름 변경
2. Redo log file 경로 변경
3. Data file 경로 변경
CREATE CONTROLFILE SET DATABASE "TESTDB2" RESETLOGS ARCHIVELOG
... 생략
LOGFILE
GROUP 1 '/data/temp/clone/redo01.log' SIZE 5M,
... 생략
DATAFILE
'/data/temp/clone/system01.dbf',
... 생략
$ export ORACLE_SID=testdb2
$ sqlplus / as sysdba ;
SQL> @/app/oracle/re.sql
SQL> select name from v$controlfile ;
3. Drop 된 테이블 복구
SQL> recover database unti time '복구할 시간' using backup controlfile;
auto
SQL> alter database open resetlogs ;
SQL> alter instance_name from v$instance ;
SQL> select count(*) from scott.gogak ;
testdb2 에서 복구 완료됨
4. 복구된 테이블을 운영서버로 이동하기
$ export ORACLE_SID=testdb2
$ exp scott/tiger file=gogak.dmp tables=gogak
$ export ORACLE_SID=TESTDB
$ imp scott/tiger file=gogak.dmp ignore=y
$ sqlplus / as sysdba
SQL> select instance_name from v$instance ;
SQL> select count(*) from scott.gogak ;
복구 완료
10.2 Clone DB 를 이용한 drop tablespace 복구
복구순서
1) 복구서버용 파라미터파일 생성
2) 백업 데이터 파일 복원
3) 컨트롤 파일 재생성
4) 불완전복구 시작
5) test01.dbf 파일 내용 수정하고 다시 복구 시작
6) 복구 완료되면 export 후 운영 서버에 import 수행
1) 복구서버용 파라미터파일 생성
$ cd /home/oracle/product/10g/dbs
$ cp inittestdb.ora inittestdb2.ora
$ vi inittestdb2.ora
db_name=testdb2 ← 변경
2) 백업데이터 파일복원
$ cp /data/backup/open/*.dbf /data/temp/clone/
3) 컨트롤 파일 재생성
$ export ORACLE_SID=testdb
$ sqlplus / as sysdba ;
SQL> select instance_name from v$instance ;
SQL> alter database backup controlfile to trace as '/home/oracle/re2.sql' ;
SQL> exit
$ vi re2.sql
...
2 CREATE CONTROLFILE SET DATABASE "TESTDB2" RESETLOGS ARCHIVELOG
...
LOGFILE
GROUP 1 '/data/temp/clone/redo01.log' size 5m,
...
DATAFILE
'/data/temp/clone/system01.dbf',
...
$ export ORACLE_SID=testdb2
$ sqlplus / as sysda ;
SYS> @/home/oracle/re2.sql
SQL> recover database until time '2013-01-17:18:29:30' using backup controlfile ;
auto
ORA-01110: data file 6: '/home/oracle/oradata/testdb/test01.dbf' ← 이 파일에서 에러남
SQL> select name from v$datafile ;
SQL> alter database create datafile '/home/oracle/product/10g/dbs/UNNAMED000006'
2 as '/data/temp/clone/test01.dbf' ;
SQL> recover database until time '2013-01-17:18:29:30' using backup controlfile ;
auto
SQL> alter database open resetlogs ;
SQL> select instance_name from v$instance ;
SQL> select count(*) from scott.gogak ;
복구됨
SQL> exit
$ export ORACLE_SID=testdb2
$ exp scott/tiger file=gogak2.dmp tables=gogak
$ export ORACLE_SID=testdb
$ sqlplus / as sysdba ;
SQL> select instance_name from v$instance ;
SQL> seleect count(*) from scott.gogak ;
아직 복구 안됨
SQL> !
$ export ORACLE_SID=testdb
$ imp scott/tiger file=gogak2.dmp ignore=y
$ exit
SQL> select instance_name from v$instance ;
SQL> select count(*) from scott.gogak ;
10.3 Clone DB 로 복구 후 DB Link 를 이용해서 이동하기
DB Link 라는 기술은 원격지의 데이터베이스를 마치 local DB 인 것처럼 연결해주는 기술로,
local DB 서버의 tnsnames.ora 파일과 원격지 DB 서버의 listener.ora 파일의 설정을 사용하며
두 서버 사이는 당연히 네트워킹이 되어야 합니다.
STEP4 Clone DB 용 parameter file 생성하기
SYS> !
$ cd /home/oracle/product/10g/dbs
$ cp inittestdb.ora inittestdb2.ora
$ vi inintestdb1.ora
db_name=clone1 ← db name 변경
control_files=("/data/temp/clone1/control01.ctl", ← control file 변경
"/data/temp/clone1/control02.ctl",
"/data/temp/clone1/control03.ctl")
STEP5 Clone db 용 control file 생성
SYS> alter database backup controlfile to trace as '/home/oracle/ctl_clone.sql' ;
SYS> !
$ cp /data/backup/open/*.dbf /data/temp/clone1/
$ vi /home/oracle/ctl_clone.sql
...
2 CREATE CONTROLFILE SET DATABASE "CLONE1" RESETLOGS ARCHIVELOG
...
LOGFILE
GROUP 1 (
'/data/temp/clone1/redo01_a.log',
'/data/temp/clone1/redo01_b.log'
) SIZE 5M,
...
DATAFILE
'/data/temp/clone1/system01.dbf',
...
$ export ORACLE_SID=clone1
$ sqlplus / as sysdba ;
SYS> @/home/oracle/ctl_clone.sql
STEP6 복구하기
SYS> recover database until time '2013-01-17:17:22:40' using backup controlfile ;
auto
SYS> alter database open resetlogs ;
SYS> select name from v$database ;
SYS> select count(*) from scott.ctest01 ;
clone1 에서 복구완료됨
STEP7 운영 서버에서 clone DB 로 DB Link 생성하기
운영서버의 /home/oracle/product/10g/network/admin/tnsnames.ora 수정
clonedb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.234.129)(PORT=1522)
)
(CONNECT_DATA =
(SERVICE_NAME = clone1)
)
)
$ pwd
/home/oracle/product/10g/network/admin
$ vi listener.ora
LISTENER2
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rcclient)(PORT = 1522))
)
)
SID_LIST_LISTENER2 =
(SID_LIST =
(SID_DESC =
(SID_NAME = clone1)
(ORACLE_HOME = /home/oracle/product/10g)
)
운영 DB 서버에서 clone DB 로 연결하는 DB Link 생성
SQL> create database link clink ← 임의로 정함
2 connect to scott identified by tiger ← clone db 로 연결할 계정과 암호 설정
3 using 'clonedb' ; ← tnsnames.ora 파일에 있는 이름 사용
SQL> exit
STEP8 Clone DB 로 접속
$ lsnrctl
LSNRCTL> start listener2
LSNRCTL> status listener2
$ tnsping clonedb
$ sqlplus / as sysdba
SQL> select count(*) from scott.ctest01@clink ;
SQL> select name from v$database ;
STEP9 scott.ctest01 테이블을 운영 DB 서버로 가져옴
SQL> create table scott.ctest01
2 as select *
3 from scott.ctest01@clink ;
SQL> select count(*) from scott.ctest01 ;
SQL> select name from v$database ;
복구 완료
연습문제 Drop user 장애 복구 후 DB Link 로 이전하기