꿈꾸는 개발자, DBA 커뮤니티 구루비

온라인 백업 스크립트를 하나의 쿼리로 만드려면

by 신이만든짝퉁 [SQL Query] 온라인 백업 hot backup backup online backup [2012.10.10 14:53:10]

안녕하세요~

지금 온라인 백업을 위해서 아래와 같은 쿼리를 이용해 문자열을 생성 한 후 스크립트를 돌리고 있습니다.

테이블스페이스 :

SQL>select ts#, name from v$tablespace;

       TS# NAME
---------- --------------------------------------
         0 SYSTEM
         4 USERS
        12 ABCD
     -- 이하 생략 --

데이터 파일 : (데이터 파일이 2개 이상있는 테이블 스페이스가 존재함)

SQL> select ts#, file#, name from v$datafile;
       TS#      FILE# NAME
---------- ---------- --------------------------------------------------
         0          1 /app/oracle/oradata/testdb/system01.dbf
         4          4 /app/oracle/oradata/testdb/users01.dbf
        12          8 /app/oracle/oradata/testdb/abcd02.dbf
        12         10 /app/oracle/oradata/testdb/abcd01.dbf
     -- 이하 생략 --

현재 쿼리문 :
select 'alter tablespace ' || name || ' begin backup;' from v$tablespace;
select '!cp ' || name || ' /backup/' || regexp_substr(name, '[^/]*\..*$') from v$datafile;
select 'alter tablespace ' || name || ' end backup;' from v$tablespace;

조회결과:
alter tablespace SYSTEM begin backup;
alter tablespace USER begin backup;
-- 중략 --
alter tablespace ABCD begin backup;

!cp /app/oracle/oradata/testdb/system01.dbf /backup/system01.dbf
!cp /app/oracle/oradata/testdb/users01.dbf /backup/sysaux01.dbf
-- 중략 ---
!cp /app/oracle/oradata/testdb/abcd02.dbf /backup/abcd02.dbf
!cp /app/oracle/oradata/testdb/abcd01.dbf /backup/abcd01.dbf

alter tablespace SYSTEM end backup;
alter tablespace USERS end backup;
-- 중략 ---
alter tablespace ABCD end backup;

이 방법은 모든 테이블스페이스를 being backup 상태로 만들고, 백업을 하기에 별로 좋은 방법은 아닌것 같습니다.
더군다나 쿼리도 3번 사용합니다.

그래서 아래처럼 테이블스페이스를 1개씩 begin backup, 백업수행, end backup 하는 1개로 된 쿼리를 만들어 보려고 하는데, 생각처럼 잘 되지 않네요.(결과에 빈 줄은 이 글을 읽는 분들의 가독성을 좋게 하려고 넣은 것일 뿐, 실제로는 빈줄은 넣지 않습니다.)

고수분들의 조언 부탁드립니다.

원하는 쿼리결과 :

alter tablespace SYSTEM begin backup;
!cp /app/oracle/oradata/testdb/system01.dbf /backup/system01.dbf
alter tablespace SYSTEM end backup;

alter tablespace USERS begin backup;
!cp /app/oracle/oradata/testdb/sysaux01.dbf /backup/sysaux01.dbf
alter tablespace USERS end backup;

-- 중략 --
-- 아래 같은 데이터 파일이 2개 있는 테이블스페이스 존재

alter tablespace ABCD begin backup;
!cp /app/oracle/oradata/testdb/abcd02.dbf /backup/abcd02.dbf
!cp /app/oracle/oradata/testdb/abcd01.dbf /backup/abcd01.dbf
alter tablespace ABCD end backup;

by 마농 [2012.10.10 16:10:27]

SELECT script
  FROM (SELECT 1 gb#
             , ts#
             , 0 file#
             , 'alter tablespace ' || name || ' begin backup;' script
          FROM v$tablespace
         UNION ALL
        SELECT 2 gb#
             , ts#
             , file#
             , '!cp ' || name || ' /backup/' || regexp_substr(name, '[^/]*\..*$') script
          FROM v$datafile
         UNION ALL
        SELECT 3 gb#
             , ts#
             , 0 file#
             , 'alter tablespace ' || name || ' end backup;' script
          FROM v$tablespace
        )
 ORDER BY ts#, gb#, file#
;

by 신이만든짝퉁 [2012.10.10 16:43:32]

앗~~ 마농님 감사합니다.

저는 너무 어렵게 생각했는지 다른 방법으로 쿼리를 만들고서 엉뚱한 부분에서 헤매고 있었는데, 덕분에 개안을 한 느낌입니다.

감사합니다. ^^

by 신이만든짝퉁 [2012.10.10 17:03:29]

저는 이렇게 짰습니다. 데이터 파일이 2개 이상인 경우가 잘 처리안되서 고민하여 있었거든요.

SELECT decode(lvl, 1, cmd1, 2, cmd2, 3, cmd3) AS cmd
FROM (SELECT rownum AS no
         , a.ts#
         , b.file#
         , 'alter tablespace ' || a.NAME || ' begin backup;' AS cmd1
         , '!cp ' || b.name || ' /backup/' || regexp_substr(b.name, '[^/]*\..*$') AS cmd2
         , 'alter tablespace ' || a.name || ' end backup;' AS cmd3
      FROM v$tablespace a
         , v$datafile b
     WHERE a.ts#=b.ts#
       AND a.NAME NOT IN ('TEMP')
     ORDER BY a.ts# asc, b.file#
    ) c
      , ( SELECT LEVEL AS lvl FROM dual CONNECT BY LEVEL <= 3
    ) d
ORDER BY c.no, d.lvl, c.file#
;

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변