ORA-39001 invalid argument value
ORA-39000 bad dump file specification
ORA-31619 invalid dump file “/덤프파일경로/파일이름“
ORA-27072 file i/o error
해결 방법
위의 오류 내용은 Export dump 백업이 아니라 기본 Export 백업으로 생성된 파일인데 Import dump 백업을 사용해 파일을 Import 할 때 발생되는 오류입니다. 위의 해결방법은 기본 Export로 백업을 진행하는 것이 아니라 Export dump를 사용해서 백업 파일을 생성하는 방법과 기본 Import로 Export 된 파일을 Import 받는 방법 2가지가 있습니다. 기본 Export 된 파일을 기본 Import로 파일을 Import 받았는데 속도가 너무 느리고 사용할 수 있는 옵션도 dump에 비해 사용할 수 있는 옵션이 적어 다시 Dump를 받을 수 있으면 Dump를 받는 것을 추천드립니다.
이번에 데이터베이스 이관 프로젝트를 진행하면서 DataDump가 아닌 기본 exp, imp를 해야 하는 일이 발생되어 schema별로 exp 된 dmp 파일을 개발서버에 imp를 진행하는데 해당 schema 계정이 아닌 dump 하기 위해 생성한 계정으로 테이블이 imp 되어 기본 exp, imp 하는 방법에 대해 작성하게 되었다.
EXPORT
Full EXPORT
전체 데이터베이스가 Export 된다. 모든 테이블스페이스, 모든 사용자, 또한 모든 객체, 데이터들이 포함 된다.
DB Table의 데이터가 물리적으로 저장되는 공간이다. (Data File 1개의 최대 용량 32GB) Tablespace는 1개 이상의 DataFile로 구성되어 있다.
DataFile 확장 방법
DataFile 추가
AUTOEXTEND로 추가
RESIZE
Tablespace 용량 확인 쿼리문
SELECT A.TABLESPACE_NAME AS "테이블스페이스명",
A.FILE_NAME AS "파일경로",
A.BYTES/1024/1024/1024 AS "총크기",
(A.BYTES - B.FREE)/1024/1024/1024 AS "사용공간",
B.FREE/1024/1024/1024 AS "여유 공간",
TO_CHAR( (B.FREE / A.BYTES * 100) , '999.99')||'%' AS "여유공간"
FROM
(
SELECT FILE_ID,
TABLESPACE_NAME,
FILE_NAME,
SUBSTR(FILE_NAME,1,200) AS FILE_NM,
SUM(BYTES) BYTES
FROM DBA_DATA_FILES
GROUP BY FILE_ID,TABLESPACE_NAME,FILE_NAME,SUBSTR(FILE_NAME,1,200)
) A,
(
SELECT TABLESPACE_NAME,
FILE_ID,
SUM(NVL(BYTES,0)) AS FREE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME,FILE_ID
) B
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME
AND A.FILE_ID = B.FILE_ID
ORDER BY A.TABLESPACE_NAME;
Tablespace 관련 조회 쿼리문
select * from dba_tablespaces;
설정된 테이블 스페이스 확인
SELECT * FROM dba_data_files;
데이터가 저장되는 물리경로 및 테이블스페이스 정보
SELECT * FROM user_users;
현재 유저의 default_tablespace 확인
Tablespace 생성
create tablespace [TABLESPACENAME]
datafile '파일경로' size 10M
autoextend on next 10M
maxsize 100M
uniform size 1M
datafile '파일경로' size 10M : 초기 데이터 파일 경로와 크기 설정
autoextend on next 10M : 초기 설정한 크기를 모두 사용하는 경우 자동으로 파일의 크기가 정해진 크기만큼 커지는 기능
maxsize 100M : 데이터파일이 최대로 커질 수 있는 크기 지정, 기본값 = unlimited
uniform size 1M : EXTENT 한 개의 크기를 설정
Tablespace에 Data File 추가하는 방법
1. DataFile 추가
ALTER TABLESPACE [TABLE_SPACE_NAME] ADD DATAFILE [DATA_FILE_NAME] SIZE [SIZE];
2. DataFile 추가 후 Auto Extensible 설정
ALTER TABLESPACE [TABLE_SPACE_NAME] ADD DATAFILE [DATA_FILE_NAME] SIZE [초기추가용량]
AUTUEXTEND ON NEXT [자동증가 용량] MAXSIZE [최대 용량]
Tablespace 변경 관련 쿼리문
ALTER USER [USER_NAME] DEFAULT TABLESPACE [TABLE_SPACE_NAME];
유저의 default Tablespace 변경
ALTER TABLE [TABLE_NAME] move tablespace [TABLE_SPACE_NAME];
테이블의 Tablespace 변경
ALTER TABLESPACE RENAME A TO B
Tablespace 물리적인 파일의 이름 또는 위치변경
ALTER DATABASE MOVE DATAFILE '/원본 경로/파일 이름' TO '/이동 경로/파일 이름';
Tablespace Online dbf 파일 위치 이동
DROP TABLESPACE [TABLE_SPACE_NAME] include contents;
테이블스페이스 내 객체(테이블, 인덱스 등)를 모두 삭제
DROP TABLESPACE [TABLE_SPACE_NAME] INCLUDING contents;
테이블스페이스의 모든 세그먼트를 삭제 (데이터가 테이블스페이스는 삭제할 수 없다)
DROP TABLESPACE [TABLE_SPACE_NAME] CASCADE constranints;
삭제된 테이블스페이스 내의 테이블의 기본키와 PK를 참조하는 다른 테이블스페이스의 테이블로부터 참조무결성 제약 조건을 삭제한다.
DROP TABLESPACE [TABLE_SPACE_NAME] INCLUDING CONTENTS AND DATAFILES;
테이블 스페이스의 물리파일까지 삭제
유저 생성시 Tablespace 지정
CREATE USER [USER_NAME] IDENTIFIED BY [PASSWORD]
DEFAULT TABLESPACE [TABLESPACE_NAME]
TEMPORARY TABLESPACE [TABLESPACE_NAME];
기본(디폴트) 테이블스페이스, 임시(temp) 테이블스페이스를 명시해 줄 수 있다.
기본 테이블스페이스란 해당 사용자로 로그인한 뒤 테이블과 같은 각종 데이터베이스 객체가 저장되는 테이블스페이스이다.
임시 테이블스페이스는 해당 사용자가 사용하는 디폴트 임시 테이블스페이스를 말한다.
실수로 dbf 파일을 삭제해버렸다면
shutdown immediate 시 테이블 스페이스를 찾을 수 없다고 에러가 난다.
해결 방법 1
테이블스페이스 오프라인 드롭을 하자
alter database datafile 'file path' offline drop;
해결 방법 2
1. shutdown abort 로 강제 종료
2. startup nomount;
3. alter database mount;
4. alter database datafile 'file path' offline
→ control 파일 정보에 해당 dbf 파일 offline 상태 알려준다.
5. alter database open;
# 권한 : oracle1
# OS
1. (1호기) SQLPLUS 접속
sqlplus "/ as sysdba"
# sqlplus
1. DB 마운트
startup mount;
2. DB 마운트 상태 확인(선택)
select status, database_status from v$instance;
3. 마운트 DB OPEN
alter database open;
4. DB 마운트 상태 확인(필수)
select status, database_status from v$instance;
5. DB 전자지갑 확인(선택)
SELECT * FROM v$encrytion_wallet;
6. DB 전자지갑 적용
alter system set ENCRYPTION wallet open identified by "key";
7. DB 전자지갑 확인(필수)
SELECT * FROM v$encrytion_wallet;
2. (2호기) SQLPLUS 접속
sqlplus "/ as sysdba"
# sqlplus
1호기와 방법 동일함
cat << EOF > /home/oracle/script/rman_backup.rcv
run {
allocate channel ch1 device type disk format '/RMANBKP/str_%T_full_%U';
allocate channel ch2 device type disk format '/RMANBKP/str_%T_full_%U';
allocate channel ch3 device type disk format '/RMANBKP/str_%T_full_%U';
allocate channel ch4 device type disk format '/RMANBKP/str_%T_full_%U';
crosscheck archivelog all;
delete noprompt archivelog all completed before 'trunc(sysdate) -1';
backup as compressed backupset database tag 'DB_FULL_BACKUP' plus archivelog tag 'ARCHIVELOG';
backup current controlfile tag 'CONTROLFILE' format '/RMANBKP/str_%T_cont_%U';
backup spfile tag 'SPFILE' format '/RMANBKP/str_%T_para_%U';
crosscheck backup;
delete noprompt backup completed before 'trunc(sysdate) -1';
release channel ch1;
release channel ch2;
release channel ch3;
release channel ch4;
}
run {
begin
for i in 1..10 loop
EXECUTE IMMEDIATE 'ALTER SYSTEM SWITCH LOGFILE';
end loop;
end;
/
}
EOF
백업 실행
rman target /
@/home/oracle/script/rman_backup.rcv
list backup summary;
DB 테이블 복구
-- DB 시간 확인(복구 시간 확인)
SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') AS "RECO_TIME" FROM DUAL;
-- <<DB 시간 확인(복구 시간 확인)>>을 확인하고 until time 뒤에 시간을 넣어주세요.
rman target /
-- PARALLEL 4 변경
CONFIGURE DEVICE TYPE DISK PARALLELISM 4;
-- RMAN TABLE 복구
recover table STR."TEST" until time "TO_DATE('2023/01/12 12:53:47','YYYY/MM/DD HH24:MI:SS')" auxiliary destination '/RMANBKP'
datapump destination '/RMANBKP' dump file 'TEST_20230112.dmp';
RMAN TABLE RENAME 복구
recover table STR."TEST" until time "TO_DATE('2023/01/12 12:53:47','YYYY/MM/DD HH24:MI:SS')" auxiliary destination '/RMANBKP'
remap TABLE 'STR'.'TEST':'TEST_20230112_1253'
datapump destination '/RMANBKP' dump file 'TEST_20230112.dmp';
-- PARALLEL 초기화
CONFIGURE DEVICE TYPE DISK CLEAR;
-- 전체 백업 파일
rman target /
list backup summary;
## 아카이브 로그 파일 확인
ls -l /ARC_STR
## 아카이브 로그 파일 생성
## ALTER SYSTEM SWITCH LOGFILE 10회 수행
sqlplus / as sysdba <<EOF
begin
for i in 1..10 loop
EXECUTE IMMEDIATE 'ALTER SYSTEM SWITCH LOGFILE';
end loop;
end;
/
exit
EOF
데이터 파일 복구
## 리스너 종료
lsnrctl stop
-- DB 접속
sqlplus / as sysdba
-- DB 종료
shutdown abort
startup mount
-- rman 접속
rman target /
REPORT SCHEMA;
RESTORE DATAFILE 3,5;
RECOVER DATAFILE 3,5;
ALTER DATABASE OPEN
REPORT SCHEMA;
## sysaux 데이터 파일 확인
## linux 수행
ls -lh /oracle/app/oracle/oradata/STR
DB 전체 복구
sqlplus / as sysdba
shutdown abort
startup nomount
-- rman 접속
-- /RMANBKP/str_20230108_cont_541hdsi2_1_1 --> 최근 백업한 컨트롤 파일로 변경해주세요.
rman target /
host 'ls -l /RMANBKP/str*cont*';
RESTORE CONTROLFILE FROM '/RMANBKP/str_20230108_cont_541hdsi2_1_1';
host 'ls -l /oracle/app/oracle/oradata/STR';
ALTER DATABASE MOUNT;
CONFIGURE DEVICE TYPE DISK PARALLELISM 4;
RESTORE DATABASE;
RECOVER DATABASE;
host 'ls -l /ARC_STR';
ALTER DATABASE OPEN RESETLOGS;
-- 정상적으로 백업 되었는지 확인
REPORT SCHEMA;
CONFIGURE DEVICE TYPE DISK CLEAR;
## 리스너 시작
lsnrctl start
리스너 빠르게 등록하는 팁
lsnrctl start 명령어 후 SQLPlus에 접속하여 alter system register
오라클에서 제공하는 백업, 복구 유틸리티이다. 예전에는 Begin End 백업을 많이 사용했지만, 요즘 회사 중요 메인 시스템의 경우 오라클을 RAC의 ASM을 많이 사용하는 추세라 RMAN을 이용한 백업은 필수가 되어가고 있다.
RMAN의 이점
성능
백업을 parallel로 병렬 처리가 가능하다.
압축
백업 압축률을 설정해 중복된 데이터가 많다면 백업 용량을 줄일 수 있다.
기본 압축 옵션은 basic이다.
증분 백업
db의 데이터가 50에서 100 테라가 넘는다면 Incremental 백업을 이용해 하루에 증가되는 데이터만 백업할 수 있다.
암호화
암호화를 해서 백업이 가능하다.
백업하는 내용
Data File
Control File
Parameter File
Archived Redo log
백업 계획
1주일에 한번 월요일에 Full Backup 화수목금토일 Incremental Backup을 한다.
스토리지 공간이 넉넉하다면 매일 Full Backup을 하는 것을 추천한다.
백업 성능을 좋게 하려면 디스크 I/O 성능이 좋은 스토리지가 필요하다.
RMAN 실습
디렉터리 생성 /ARC_STR, /RMANBKP
Archive 모드 변경
RMAN 백업
백업 자동화
1. 아카이브 & RMAN BACKUP 디렉터리 생성(root 계정)
## 아카이브 디렉터리 생성
mkdir /ARC_STR
chown oracle.dba /ARC_STR
## RMAN BACKUP 디렉터리 생성
mkdir -p /RMANBKP/control
chown -R oracle.dba /RMANBKP
ls -ld /RMANBKP /ARC_STR
2. 디스크 읽기 성능 테스트
fdisk -l | grep 'Disk /dev/sd'
hdparm -t /dev/sda
3. 아키이브 로그 파라미터 변경, 아카이브 로그 모드 변경
ALTER SYSTEM SET log_archive_dest='/ARC_STR' SCOPE=SPFILE;
ALTER SYSTEM SET log_archive_format='ARCH_STR_%r_%t_%s.ARC' SCOPE=SPFILE;
-- 아카이브 로그 모드 변경
shutdown immediate;
startup mount;
alter database archivelog;
archive log list;
alter database open;
4. RMAN 접속, DB 전체 정보, DB 파일 상태 점검
-- rman 접속
rman target /
-- DB 전체 정보
report schema;
-- DB 파일 상태 점검
validate database;
validate database는 데이터베이스 안에 있는 블록들에 손상된 블록이 있는지 확인하는 방법이다.
5. RMAN 백업 설정
-- 2일 전의 백업까지
rman target /
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/RMANBKP/control/control_%F';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/RMANBKP/control/snapcontrol_STR.f';
-- rman 설정 원복
CONFIGURE RETENTION POLICY CLEAR;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;
CONFIGURE SNAPSHOT CONTROLFILE NAME CLEAR;
-- rman 설정 확인
show all;
6. RMAN 백업
-- %T (YYYYMMDD) 년월일 포멧
-- %U 시스템 생성 고유 파일 이름 8자리
-- 전체 백업
backup database format '/RMANBKP/STRDB2_full_n_bkp_%T_%U';
-- 전체 백업 + 압축
backup as compressed backupset database format '/RMANBKP/STRDB2_full_c_bkp_%T_%U';
-- 백업 상태 확인
list backup;
list backup summary;
host 'ls -lh /RMANBKP';
7. ARCHIVE LOG & BACKUP 삭제(rman에서 수행)
run {
delete noprompt archivelog all;
delete noprompt backup;
}
8. channel 4개로 Parallel 백업(rman에서 수행)
run {
allocate channel ch1 device type disk format '/RMANBKP/str_%T_full_%U';
allocate channel ch2 device type disk format '/RMANBKP/str_%T_full_%U';
allocate channel ch3 device type disk format '/RMANBKP/str_%T_full_%U';
allocate channel ch4 device type disk format '/RMANBKP/str_%T_full_%U';
crosscheck archivelog all;
delete noprompt archivelog all completed before 'trunc(sysdate) -1';
backup as compressed backupset database tag 'DB_FULL_BACKUP' plus archivelog tag 'ARCHIVELOG';
backup current controlfile tag 'CONTROLFILE' format '/RMANBKP/str_%T_cont_%U';
backup spfile tag 'SPFILE' format '/RMANBKP/str_%T_para_%U';
crosscheck backup;
delete noprompt backup completed before 'trunc(sysdate) -1';
release channel ch1;
release channel ch2;
release channel ch3;
release channel ch4;
}
9. Linux Shell을 이용한 DB 전체 자동백업과 삭제 스크립트
## rman 백업 로그 디렉터리 생성
## oracle 계정
cd /home/oracle
mkdir -p /home/oracle/oraclelog
## rman 백업 스크립트 디렉터리 생성
## oracle 계정
mkdir -p /home/oracle/script
## 임시 nfsbackup 디렉터리 생성
## root 계정
mkdir /nfsbackup
chown oracle.dba /nfsbackup
ls -ld /nfsbackup
nfsbackup는 실제 운영에서는 백업 서버를 연결해서 원격 디렉터리를 만들지만 테스트할 때는 백업 서버가 없으니 있다고 가정하고 실행한다.
10. rman 백업 Shell 스크립트 생성(vi 편집기 사용 안하고 파일 만드는 법)
-- Channel을 많이 사용하면 DB 전체 SQL 성능 지연 발생할수 있으니 주의 필요!!!
cat << \\FILE > /home/oracle/script/rman_backup.sh
#!/bin/bash
################################################################################
# RMAN FULL BACKUP SCRIPT
# V 1.0 CPU 8Core 권장 스크립트 (Channel 4개 사용) DBBODA
# Channel을 많이 사용하면 DB 전체 SQL 성능 지연 발생할수 있으니 주의 필요!!!
################################################################################
export ORACLE_HOME=/oracle/app/oracle/product/19.0.0.0/db_1
export ORACLE_SID=STR
export PATH=$ORACLE_HOME/bin:$PATH
export DATE=`date +%Y%m%d`
export YDATE=`date -d '1 day ago' '+%Y%m%d'`
export HOSTNAME=`hostname`
export NLS_DATE_FORMAT="YYYY/MM/DD(DY) HH24:MI:SS"
rman log=/home/oracle/oraclelog/${HOSTNAME}_rman_backup_${DATE}.log << EOF
connect target /
run {
allocate channel ch1 device type disk format '/RMANBKP/str_%T_full_%U';
allocate channel ch2 device type disk format '/RMANBKP/str_%T_full_%U';
allocate channel ch3 device type disk format '/RMANBKP/str_%T_full_%U';
allocate channel ch4 device type disk format '/RMANBKP/str_%T_full_%U';
crosscheck archivelog all;
delete noprompt archivelog all completed before 'trunc(sysdate) -1';
backup as compressed backupset database tag 'DB_FULL_BACKUP' plus archivelog tag 'ARCHIVELOG';
backup current controlfile tag 'CONTROLFILE' format '/RMANBKP/str_%T_cont_%U';
backup spfile tag 'SPFILE' format '/RMANBKP/str_%T_para_%U';
crosscheck backup;
delete noprompt backup completed before 'trunc(sysdate) -1';
release channel ch1;
release channel ch2;
release channel ch3;
release channel ch4;
}
exit
EOF
## RMAN Backup zip
cd /RMANBKP
zip -r str_full_backup_${YDATE}.zip str_${YDATE}* control/
## Archive zip
cd /ARC_STR
export ARCHFILE=`ls -l --time-style="+%Y%m%d" | grep ${YDATE} | awk '{print $7}'`
zip ${HOSTNAME}_arc_backup_${YDATE}.zip ${ARCHFILE}
## NFS Backup
cp /RMANBKP/str_full_backup_${YDATE}.zip /nfsbackup/str_full_backup_${YDATE}.zip
cp /ARC_STR/${HOSTNAME}_arc_backup_${YDATE}.zip /nfsbackup/${HOSTNAME}_arc_backup_${YDATE}.zip
## Remove zip
rm -f /RMANBKP/str_full_backup_${YDATE}.zip
rm -f /ARC_STR/${HOSTNAME}_arc_backup_${YDATE}.zip
## Remove nfs Backup file
export WDATE=`date -d '8 day ago' '+%Y%m%d'`
find /nfsbackup/ -name "*${WDATE}.zip" -exec rm -f {} \\;
FILE
RMAN Backup zip은 변수 값으로 압축하고 Archive zip도 동일하게 압축한다. NFS 디렉터리에서 전체 백업한 압축 파일, 아카이브 로그 압축 파일을 cp로 복사하고 rm 명령으로 RMAN 백업 디렉터리와 아카이브 디렉터리 zip 압축 파일을 삭제한다. 추가로 nfs 백업 디렉터리의 8일 전 파일을 자동으로 삭제하게 만들었다.
11. shell 스크립트 권한 변경, crontab 추가
## shell 스크립트 권한 변경
## oracle 계정
cd /home/oracle/script
chmod 755 rman_backup.sh
--crontab 추가
crontab -e
## RMAN FULL BACKUP
00 02 * * * /home/oracle/script/rman_backup.sh
오라클 DB 데이터를 매우 빠른 속도로 복구하는 기술이다. 사용자 실수로 인한 데이터 오류, 테이블 삭제, 또는 데이터베이스 전체에 대해 문제가 발생했을 때 매우 빠른 속도로 복구할 수 있다.
FlashBack이 필요한 이유
예를 들어 쇼핑몰을 운영하는 회사에서 인플레이션으로 인해 사장님이 전체 상품에 대해 5% 인상을 이대리에게 요청한다. 이대리는 상품 테이블을 update 명령어로 5% 인상해야 하는데 실수로 50% 인상을 해버렸다.
FlashBack을 사용안하던 과거에는 실수로 상품 table을 삭제했을 때는 EXP/IMP, DATAPUMP, RMAN이 있는데 EXP/IMP, DATAPUMP는 원하는 시점으로 복구하기가 어렵고 RMAN은 가능하지만 데이터 크기가 크면 복구하는데 오래 걸릴 수 있다. 이럴 때 FlashBack table 기능을 이용하면 간단하게 복구할 수 있다.
FRA(Fast Recovery Area)
빠르게 복구할 수 있는 공간을 의미하고 백업 데이터 파일들을 통합 관리하는 기능을 제공한다.
테이블, 데이터 삭제 복구
1. find 명령어로 spfile위치를 확인한다.
find $ORACLE_HOME -name spfile*
2. spfile 백업
cp spfileSTR.ora spfileSTR.ora_20221231
3. UNDO RETENTION 파라미터 변경
-- 1Day = 86400sec
show parameter undo_retention
alter system set undo_retention=86400 scope=spfile;
4. DB 종료, 재시작
-- DB 종료
SHUTDOWN IMMEDIATE
-- DB 시작
STARTUP
5. Test 테이블, 데이터 생성 후 삭제
-- 테이블 생성
CREATE TABLE STR.FRA_TEST
(
FRA_NO VARCHAR2(20)
);
-- 데이터 1건 입력
INSERT INTO STR.FRA_TEST VALUES(TO_CHAR(SYSDATE,'YYYYMMDD HH24MISS'));
COMMIT;
-- 데이터 확인
SELECT * FROM STR.FRA_TEST;
-- 테이블 삭제
DROP TABLE STR.FRA_TEST;
6. FLASHBACK을 이용한 테이블 복구
FLASHBACK TABLE username.table_name TO BEFORE DROP;
테이블은 남아있고 데이터만 삭제 복구
1. Test 테이블, 데이터 생성 후 삭제
-- 테이블 생성
CREATE TABLE STR.FRA_TEST
(
FRA_NO VARCHAR2(20)
);
-- 데이터 5건 입력
INSERT INTO STR.FRA_TEST VALUES('1');
INSERT INTO STR.FRA_TEST VALUES('2');
INSERT INTO STR.FRA_TEST VALUES('3');
INSERT INTO STR.FRA_TEST VALUES('4');
INSERT INTO STR.FRA_TEST VALUES('5');
COMMIT;
-- INSERT 완료 시간 확인
-- 데이터 복구 할떄는 +10초 정도 여유를 두고 복구를 진행한다.
SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') AS "COMMIT_TIME" FROM DUAL;
-- 데이터 전체 삭제
DELETE FROM STR.FRA_TEST;
COMMIT;
2. 데이터 복구
-- 데이터 전체 복구
-- (INSERT 완료 시간 확인) 쿼리에 COMMIT_TIME을 확인한다.
-- 데이터 복구 할떄는 +10초 정도 여유를 두고 복구를 진행한다. (2022/12/19 10:00:00)
INSERT INTO STR.FRA_TEST
SELECT * FROM STR.FRA_TEST
AS OF TIMESTAMP TO_TIMESTAMP('2022/12/19 10:00:00','YYYY/MM/DD HH24:MI:SS');
COMMIT;
부분 데이터 삭제 복구
1. Test 데이터 삭제
-- 부분 데이터 삭제
-- FRA_NO 3 데이터 삭제
DELETE FROM STR.FRA_TEST
WHERE FRA_NO = '3';
COMMIT;
2. 부분 데이터 복구(원본 테이블은 두고 복제 테이블을 만든다.)
-- CTAS를 이용한 FRA_TEST_20221219_1000 테이블 생성 및 데이터 복구
-- (INSERT 완료 시간 확인) 쿼리에 COMMIT_TIME을 확인한다.
-- 데이터 복구 할떄는 +10초 정도 여유를 두고 복구를 진행한다. (2022/12/19 10:00:00)
CREATE TABLE STR.FRA_TEST_20221219_1000
AS
SELECT * FROM STR.FRA_TEST
AS OF TIMESTAMP TO_TIMESTAMP('2022/12/19 10:00:00','YYYY/MM/DD HH24:MI:SS');
-- 데이터 1건 복구 확인
SELECT * FROM * STR.FRA_TEST;
-- 테이블 삭제
DROP TABLE STR.FRA_TEST_20221219_1000 PURGE;
DROP TABLE STR.FRA_TEST PURGE;
3. 복제 테이블에서 원본 테이블과 빠진 데이터를 확인해서 Insert
-- 데이터 1건 복구 SQL
INSERT INTO STR.FRA_TEST
SELECT FRA_NO
FROM STR.FRA_TEST_20221219_1000
WHERE FRA_NO NOT IN (
SELECT FRA_NO FROM STR.FRA_TEST);
COMMIT;
데이터베이스 전체 복구
1. 아카이브 & FRA 디렉터리 생성, root 실행
mkdir /FRA
chown oracle.dba /FRA
ls -ld /FRA
2. FRA 파라미터 변경
-- 1Day = 1440min defualt
show parameter DB_FLASHBACK_RETENTION_TARGET
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=20G scope=spfile; -- 용량 설정
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/FRA' scope=spfile; -- 디렉터리 설정
3. DB 종료 후 Mount 상태로 올려야한다.
SHUTDOWN IMMEDIATE
STARTUP MOUNT
4. 아카이브 로그 모드, Flashback 모드로 변경 후 DB Open
ALTER DATABASE ARCHIVELOG;
-- 아카이브 로그 확인
ARCHIVE LOG LIST;
-- Flashback 모드 변경
ALTER DATABASE FLASHBACK ON;
-- Flashback 모드 확인
SELECT FLASHBACK_ON FROM V$DATABASE;
-- DB OPEN
ALTER DATABASE OPEN;
FRA를 사용하려면 아카이브 로그모드와 FlashBack on이 되어있어야 한다.
5. 아카이브 & Flashback 상태 확인
SELECT NAME, LOG_MODE, FLASHBACK_ON FROM V$DATABASE;
FlashBack이 실행되어 있으면 리눅스에서 RVWR 프로세스가 실행 중이다.
-- Linux 실행 RVWR(Recovery Writer) 프로세스 확인
ps -efl | grep [r]vwr
6. 시작 시간 확인
SELECT
TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') AS "START_TIME" ,
TIMESTAMP_TO_SCN(SYSDATE) AS "SCN"
FROM DUAL;
7. DB mount 상태로 이동 mount 상태에서 복구해야 한다.
STARTUP MOUNT
8. DB 전체 Flashback으로 원하는 시간으로 복구 후 DB를 RESETLOGS로 Open 한다.
-- (시작 시간 확인) 쿼리에 START_TIME을 확인한다. (2022/12/19 10:00:00)
FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('2022/12/19 10:00:00','YYYY/MM/DD HH24:MI:SS');
-- DB OPEN
ALTER DATABASE OPEN RESETLOGS;
FRA 관리 방법
1. FRA 디렉터리에 어떤 파일들이 있는지 확인, FRA 디렉터리 사용량 확인할 수 있다.
-- sqlplus / as sysdba 접속
sqlplus / as sysdba
SET LINES 180
COL NAME FOR A20
-- FRA 디렉터리에 어떤 파일들이 있는지 확인, FRA 디렉터리 사용량 확인할 수 있다.
SELECT
FILE_TYPE,
PERCENT_SPACE_USED,
PERCENT_SPACE_RECLAIMABLE, NUMBER_OF_FILES
FROM V$FLASH_RECOVERY_AREA_USAGE;
SELECT
NAME,
ROUND(SPACE_LIMIT/1024/1024/1024) AS "SPACE(GB)",
ROUND(SPACE_USED/1024/1024/1024,2) AS "USED(GB)"
FROM V$RECOVERY_FILE_DEST;
백업 파일 삭제
1. Recovery Manager에 접속 후 백업 파일 삭제
-- rman에서 로그 확인 및 백업 파일 삭제
rman target /
REPORT OBSOLETE;
LIST ARCHIVELOG ALL;
CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;
DELETE EXPIRED BACKUP;
DELETE EXPIRED ARCHIVELOG ALL;
DELETE FORCE OBSOLETE;
FRA 원상 복구
1. 파라미터 원복
ALTER SYSTEM RESET UNDO_RETENTION;
ALTER SYSTEM RESET DB_RECOVERY_FILE_DEST_SIZE;
ALTER SYSTEM RESET DB_RECOVERY_FILE_DEST;
2. DB 종료 후 Mount 상태로 Open
-- DB 종료
SHUTDOWN IMMEDIATE
-- DB 시작(Mount 상태)
STARTUP MOUNT
3. Flashback, 아카이브 로그 OFF 후 DB Open
-- Flashback 기능 OFF
ALTER DATABASE FLASHBACK OFF;
-- 아카이브 로그 OFF
ALTER DATABASE NOARCHIVELOG;
-- DB OPEN
ALTER DATABASE OPEN;
4. DB 상태 확인
-- DB 상태 확인
SELECT NAME, LOG_MODE, FLASHBACK_ON FROM V$DATABASE;