프로시저, 펑션 등 daily backup 1 5 901

by jkson [2017.01.23 09:48:52]


전에 프로시저 사용이 부담스러운 게 지난 소스 확인이 어렵다는 이야기가 있었어요.

저는 특정 패키지만 백업하고 있었는데 아침에 생각나서 전체 소스 백업 JOB을 만들어보았습니다.

다른 효율적인 방법이 있는데 뻘짓인지는 모르겠네요ㅎㅎ;

------------------------------------------------------------------------------------------------------------

오라클 프로시저나 펑션을 실수로 날려먹었을 때의 당혹스러움. 겪어본 사람만 알겠죠?

테스트 서버인 줄 알고 빌드했는데 알고 보니 운영 서버였다든지..;

혹은 기껏 다 만들어놨더니 예전 소스로 덮쳐버렸다든지..;

이럴 땐 당황하지 마시고

sys 계정으로 접속한 후에

SELECT REPLACE(TEXT, CHR(10), '') TEXT
  FROM DBA_SOURCE AS OF TIMESTAMP TO_TIMESTAMP('2017-01-23 06:00:00','YYYY-MM-DD HH24:MI:SS') -- FLASHBACK 시점
 WHERE TYPE = 'FUNCTION' -- FUNCTION, LIBRARY, PACKAGE, PACKAGE BODY, PROCEDURE, TRIGGER, TYPE, TYPE BODY 등
   AND NAME = 'FN_TEST'  -- 오브젝트 명칭
 ORDER BY LINE

위 쿼리를 수행한 후에 돌려놓으시면 됩니다.

그런데.. 내가 언제 이 소스를 날려먹었는지도 모르겠는 경우.. 혹은 아주 예전 소스를 확인해야하는 경우라면

FLASHBACK 기간은 제한이 있기 때문에 무용지물이죠.

프로시저나 펑션을 제때 백업을 해주지 않았다면 확인할 방법이 없습니다.

해서 프로시저나 펑션 등의 오브젝트는 백업이 중요합니다.

그렇다고 매일 백업하자니 번거롭기도 하고, 번거롭다고 몇번 빼먹었더니 딱 그 시점이 필요하다면..;

번거로운 백업을 자동으로 해준다면 좀 낫겠죠?

--백업용 테이블을 만듭니다.
CREATE TABLE SOURCE_BACKUP
AS
 SELECT NAME
      , TYPE
      , TRUNC(SYSDATE) BACKUPDATE
      , LINE
      , TEXT
   FROM USER_SOURCE;
 
--PK, 인덱스 생성
CREATE UNIQUE INDEX NETM1.SOURCE_BACKUP_PK ON NETM1.SOURCE_BACKUP (NAME, TYPE, BACKUPDATE, LINE);

ALTER TABLE NETM1.SOURCE_BACKUP ADD (CONSTRAINT SOURCE_BACKUP_PK PRIMARY KEY (NAME, TYPE, BACKUPDATE, LINE));
 
--백업 JOB을 만듭니다.   
DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
  ( job       => X
   ,what      => 'DECLARE
BEGIN
INSERT INTO SOURCE_BACKUP
SELECT X.NAME
     , X.TYPE
     , TRUNC(SYSDATE) BACKUPDATE
     , X.LINE
     , X.TEXT
  FROM USER_SOURCE X
 WHERE (X.NAME, X.TYPE) IN (SELECT NAME, TYPE
                              FROM USER_SOURCE Y
                             WHERE NOT EXISTS (SELECT 1
                                                 FROM SOURCE_BACKUP Z
                                                WHERE Z.BACKUPDATE = (SELECT MAX(BACKUPDATE)
                                                                        FROM SOURCE_BACKUP A
                                                                       WHERE A.NAME = Y.NAME
                                                                         AND A.TYPE = Y.TYPE)
                                                  AND Z.NAME = Y.NAME
                                                  AND Z.TYPE = Y.TYPE
                                                  AND Z.LINE = Y.LINE
                                                  AND Z.TEXT = Y.TEXT));
COMMIT;
END;'
   ,next_date => to_date('24-01-2017 00:00:00','dd/mm/yyyy hh24:mi:ss')
   ,interval  => 'TRUNC(SYSDATE+1)'
   ,no_parse  => FALSE
  );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
 
--복구할 땐

--백업 일자 조회
SELECT DISTINCT BACKUPDATE
  FROM SOURCE_BACKUP
 WHERE NAME = 'FN_TEST' -- 복구할 OBJECT 명
   AND TYPE = 'FUNCTION' -- 복구할 OBJECT TYPE
 ORDER BY BACKUPDATE

--백업 내용 조회 
SELECT REPLACE(TEXT, CHR(10), '') TEXT
  FROM SOURCE_BACKUP
 WHERE NAME = 'FN_TEST' -- 복구할 OBJECT 명
   AND TYPE = 'FUNCTION' -- 복구할 OBJECT TYPE
   AND BACKUPDATE = '2017-01-24' -- 복구할 날짜
ORDER BY LINE

이렇게 해두시면 매일 자정에 변경된 오브젝트를 백업해주게 됩니다.

by jkson [2017.01.24 09:54:29]

BACKUPDATE MAX값 가지고 오는 부분 좀 수정했어요. 오브젝트별 MAX값을 가지고 와야 맞겠네요. 그리고 해당 JOB이 두 번 수행된다든지 하면 안 될 것 같아서 PK추가했고 오브젝트별 MAX값 가지고 오는 부분 부하가 있어서 그냥 INDEX 추가했습니다. 테스트 해보고 올릴 걸요ㅠㅠ


by 쌩크 [2017.02.10 10:39:54]

오~~ 멋지네요.

당장 해봐야겠습니다.


by 쌩크 [2017.02.10 10:48:07]

USER_SOURCE에서 조회 할 수 있는게 자기 계정 오브젝트들 뿐인가보네요. 각 계정 별로 이걸 다 해주는것도 뭔가 좀 아닌것 같고. USER_SOURCE에서 다 조회 해 올 방법을 고민해봐야겠습니다.


by 쌩크 [2017.02.10 11:04:12]

오라클 홈피의 레퍼런스를 보니 USER_SOURCE가  OWNER를 제외하고는 ALL_SOURCE와 동일하다고 하는군요. ALL_SOURCE에서 가져오도록 하면 될것 같네요.

system 계정을 사용할 게 아니라면 일반 계정에 DBA_SOURCE를 읽을 수 있는 권한을 줘야 하는데 
보안 측면을 생각한다면 ALL_SOURCE의 읽기전용 버전을 만들어 주는게 나을듯 합니다.


by jkson [2017.08.16 14:43:17]

아 저는 계정별로 백업해서 user_source로 했는데 all_source로 하셔도 되겠네요.

대신 계정 구분은 있어야겠지요^^;

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입