[Oracle] 한 달 전 데이터를 SELECT 하기

한 달 전 데이터를 복구해보자.

 

ERP운영을 하다보면 특정 시점의 데이터가 필요 할 때가 있다.

빠르게 요청받은 몇 시간 전 데이터는 TimeStamp 를 통해 데이터 복구가 가능하지만

3주 전 시점의 데이터는 복구 할 수가 없는데, 가능하게 해보자.

 

 

전략은 테스트 디비서버를 하나 두는 것이다.

콜드백업 받은 데이터를 그대로 테스트 디비에 올리고

디비링크로 서로 연결시킨 후 원하는 시점까지 복구 후에 select 하면 원하는 시점 데이터를 볼 수 있다.

 

정기적으로 Cold Backup을 하고 Hot Backup을 하며 해당 백업파일을 이용하여 복구작업을 해보자.

필수 파일

LOG FILE - 말그대로 로그가 담긴 파일이다. 디비에 변경사항이 생기면 변경 명령이 해당 파일에 저장된다.

원하는 시점까지 복구하기 위해서는 해당 시점 까지의 로그파일이 필요하다.

DATA FILE - 데이터 파일

CONTROL FILE  - 검색해보면 정보가 더 나오겠지만, 컨트롤 파일은 디비를 구동할 때 데이터파일 경로나, 리두로그 위치, 현재 로그 시퀀스 번호 등 백업정보들이 담겨 있고 컨트롤파일 토대로 디비가 구동된다.

* 시퀀스 번호가 중간에 빵꾸가 났다면 그 뒤로 모든 파일들이 쓸 수 없게된다.

* 복구서버에서 드라이브 위치가 달라서 해당 설정을 추가로 해주기

 

 

명령어 정리

set ORACLE_SID=YJ
sqlplus sys/{password} as sysdba
create pfile='?\database\initYJ10.ORA' from spfile;

 

oradim -new -sid YJ -PFILE C:\oracle\product\11.2.0\dbhome_1\database\initYJ.ORA
create pfile='?\database\initYJ10.ORA' from spfile;
* pfile은 파라미터 파일
* spfile은 서버파라미터파일로 바이너리파일이다.

 

 

파일 복구

1.restore

1-1. 백업파일 리스토어

 

백업 파일을 복구서버의 복구할 위치에 복붙한다.

백업 시점 이후부터 복원할 시점까지의 아카이브로그파일도 복붙한다.

     * 리스토어는 옮기는 시간이 대부분의 시간을 차지한다..

 

 

1-2 서비스 등록 

복구서버에 서비스가 없는 경우 oradim 유틸리티로 등록 
oradim -new -sid YJ -PFILE {리스토어 파일 위치}
ex> 
oradim -new -sid YJ -PFILE C:\oracle\product\11.2.0\dbhome_1\database\initYJ.ORA

=> services.msc  체크 

 

파라미터를 복구서버환경에 맞게 수정. 
( ex >  *.control_files='D:\oradata\YJ\CONTROL.BAK' 
        *.log_archive_dest_1='LOCATION=D:\oradata\YJ_arch' 
        *.audit_file_dest='C:\oracle\product\admin\YJ\adump' ) 

 

 

1-3. 디비 마운트 

set ORACLE_SID={디비명}
SQLPLUS SYSDBA로 접속
STARTUP MOUNT 
select instance_name ,status from v$instance;

 

INSTANCE_NAME                    STATUS
-------------------------------- ------------------------
yj                                     MOUNTED

* 파일 확인 
select name from v$datafile;
select * from v$controlfile; 
select name from v$tempfile;
select member from v$logfile;

 

=> 리스토어 확인 

1-4 리네임 E -> D

alter database rename file 'E:\ORADATA\YJ\SYSTEM01.DBF' to 'D:\ORADATA\YJ\SYSTEM01.DBF'; 
alter database rename file 'E:\ORADATA\YJ\SYSAUX01.DBF' to 'D:\ORADATA\YJ\SYSAUX01.DBF' ; 
alter database rename file 'E:\ORADATA\YJ\UNDOTBS01.DBF' to 'D:\ORADATA\YJ\UNDOTBS01.DBF' ; 
alter database rename file 'E:\ORADATA\YJ\USERS01.DBF' to 'D:\ORADATA\YJ\USERS01.DBF' ; 
alter database rename file 'E:\ORADATA\YJ\DATA01_01.DBF' to 'D:\ORADATA\YJ\DATA01_01.DBF' ; 
alter database rename file 'E:\ORADATA\YJ\DATA02_01.DBF' to 'D:\ORADATA\YJ\DATA02_01.DBF' ; 
alter database rename file 'E:\ORADATA\YJ\DATA03_01.DBF' to 'D:\ORADATA\YJ\DATA03_01.DBF' ; 
alter database rename file 'E:\ORADATA\YJ\DEV_YJ01.DBF' to 'D:\ORADATA\YJ\DEV_YJ01.DBF' ; 
alter database rename file 'E:\ORADATA\YJ\YJUSERS_SPACE01.DBF' to 'D:\ORADATA\YJ\YJUSERS_SPACE01.DBF'; 
alter database rename file 'E:\ORADATA\YJ\INDEX01.DBF' to 'D:\ORADATA\YJ\INDEX01.DBF' ; 
alter database rename file 'E:\ORADATA\YJ\INDEX02.DBF' to 'D:\ORADATA\YJ\INDEX02.DBF' ; 
alter database rename file 'E:\ORADATA\YJ\INDX.DBF' to 'D:\ORADATA\YJ\INDX.DBF' ; 
alter database rename file 'E:\ORADATA\YJ\LARGE_RB01S.DBF' to 'D:\ORADATA\YJ\LARGE_RB01S.DBF' ; 

alter database rename file 'E:\ORADATA\YJ\TEMP01.DBF' TO 'D:\ORADATA\YJ\TEMP01.DBF';
alter database rename file 'E:\ORADATA\YJ\TEMP001.DBF' TO 'D:\ORADATA\YJ\TEMP001.DBF';
alter database rename file 'E:\ORADATA\YJ\TEMP002.DBF' TO 'D:\ORADATA\YJ\TEMP002.DBF';

alter database rename file 'E:\ORADATA\YJ\REDO03.LOG' to 'D:\ORADATA\YJ\REDO03.LOG';
alter database rename file 'E:\ORADATA\YJ\REDO02.LOG' to 'D:\ORADATA\YJ\REDO02.LOG';
alter database rename file 'E:\ORADATA\YJ\REDO01.LOG' to 'D:\ORADATA\YJ\REDO01.LOG';

 

 

2. RECOVER

* 복구가 필요없는 테이블스페이스(리스토어를 아예수행하지 않음) 
ALTER TABLESPACE {테이블스페이스명} OFFLINE DROP; 

 

 

2-1 ARCHIVE 확인 

 

SHOW PARAMETER LOG_ARCHIVE_DEST

=> 운영이랑 달라도 상관없음. 아카이브 리스토어 한 경로로 지정 

 

 

2-2 시점 복원 

ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS' ; 
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL TIME '2022/08/08 03:00:00'; 

 

=> AUTO로 원하는 시간대까지 복구. 개별복구도 가능 .

 

 

2-3 디비 오픈 

ALTER DATABASE OPEN RESETLOGS;

=> 데이터확인 가능하고 , 리스너통한 원격접속도 가능해짐. 

 

 

덤프 이용 오브젝트 복구

1. 복구할 덤프 리스토어 

2. 테이블 복구할 디비 확인 ( 테이블 사라졌는지?, 테이블을 저장할 테이블스페이스 공간이 없는지? ) 

* 기존 테이블의 테이블스페이스 확인 

SELECT OWNER,TABLE_NAME,TABLESPACE_NAME
FROM DBA_TABLES
WHERE OWNER='YJ'
AND TABLE_NAME='YJTABLE';

 

* 복구디비의 테이블스페이스 공간 확인

SELECT FILE_NAME,BYTES/1024/1024,MAXBYTES/1024/1024
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME='DATA03';

 

CREATE DIRECTORY YJ_DMP AS 'D:\oradata\YJ_DMP';
CREATE DIRECTORY YJ_DMP2 AS 'D:\oradata\YJ_DMP2';

 

4. 테이블을 리네임하면서 IMPORT 

impdp system/{password} directory=yj_dmp dumpfile=YJ_2022-08-08.DMP logfile=YJ_2022-08-08_imp.log tables=YJ.YJTABLE remap_table=YJ.YJTABLE:YJ_TABLE