2012. 10. 11. 11:34
Oracle Hot Backup Work/ORACLE2012. 10. 11. 11:34
* Windows 기반 Oracle Hot Backup
1. Data file Backup
2. Control file Backup
* Backup Script 생성 Query
SELECT
CASE WHEN RN1= 1 THEN '/* some_label */ALTER TABLESPACE ' || TABLESPACE_NAME || ' BEGIN BACKUP;' || CHR(13)||CHR(10) ELSE '' END
|| '/* some_label */host XCOPY ' || FILE_NAME || ' <Backup Path> /F /Y'
|| CASE WHEN RN2= 1 THEN CHR(13)||CHR(10) || '/* some_label */ALTER TABLESPACE ' || TABLESPACE_NAME || ' END BACKUP;' || CHR(13)||CHR(10) ELSE '' END
FROM (
SELECT ROW_NUMBER() OVER (partition by TABLESPACE_NAME order by FILE_NAME) RN1
, ROW_NUMBER() OVER (partition by TABLESPACE_NAME order by FILE_NAME DESC) RN2
, TABLESPACE_NAME, FILE_NAME
FROM DBA_DATA_FILES
) A
ORDER BY TABLESPACE_NAME, FILE_NAME
* Sample
@ECHO OFF
REM Logging start backup
SET ds=%date%_%time%
SET ds=-- Scheduled Job Start : %ds:~0,22%
ECHO %ds%
REM Network drive check
REM NET USE M: /DELETE
REM NET USE M: \\203.xxx.xxx.xxx\ForxDB_Backups <password>/USER:<id>
REM Connect SqlPlus... Backup and Shutdown Oracle
FIND "/*%none% some_label" <%0 |sqlplus "/as sysdba"
GOTO end
: ----------- embedded SQL-------------------------
/* some_label */ALTER TABLESPACE FORXDB_BBS_DATA BEGIN BACKUP;
/* some_label */host XCOPY E:\ORADATA\FORX\FORXDB_BBS_DATA.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */ALTER TABLESPACE FORXDB_BBS_DATA END BACKUP;
/* some_label */ALTER TABLESPACE FORXDB_BBS_INDX BEGIN BACKUP;
/* some_label */host XCOPY E:\ORADATA\FORX\FORXDB_BBS_INDX.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */ALTER TABLESPACE FORXDB_BBS_INDX END BACKUP;
/* some_label */ALTER TABLESPACE FORXDB_INDX BEGIN BACKUP;
/* some_label */host XCOPY E:\ORADATA\FORX\FORXDB_INDX.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */ALTER TABLESPACE FORXDB_INDX END BACKUP;
/* some_label */ALTER TABLESPACE FORXDB_STA_DATA BEGIN BACKUP;
/* some_label */host XCOPY E:\ORADATA\FORX\FORXDB_STA_DATA.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */ALTER TABLESPACE FORXDB_STA_DATA END BACKUP;
/* some_label */ALTER TABLESPACE FORXDB_STA_INDX BEGIN BACKUP;
/* some_label */host XCOPY E:\ORADATA\FORX\FORXDB_STA_INDX.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */ALTER TABLESPACE FORXDB_STA_INDX END BACKUP;
/* some_label */ALTER TABLESPACE FORXDB_USER BEGIN BACKUP;
/* some_label */host XCOPY E:\ORADATA\FORX\FORXDB_USER.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */ALTER TABLESPACE FORXDB_USER END BACKUP;
/* some_label */ALTER TABLESPACE FORX_HOMEPAGE BEGIN BACKUP;
/* some_label */host XCOPY E:\ORADATA\FORX\FORX_HOMEPAGE01.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */ALTER TABLESPACE FORX_HOMEPAGE END BACKUP;
/* some_label */ALTER TABLESPACE FORX_HOMEPAGE_IDX BEGIN BACKUP;
/* some_label */host XCOPY E:\ORADATA\FORX\FORX_HOMEPAGE_IDX01.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */ALTER TABLESPACE FORX_HOMEPAGE_IDX END BACKUP;
/* some_label */ALTER TABLESPACE KPEG_HOMEPAGE BEGIN BACKUP;
/* some_label */host XCOPY E:\ORADATA\FORX\KPEG_HOMEPAGE.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */ALTER TABLESPACE KPEG_HOMEPAGE END BACKUP;
/* some_label */ALTER TABLESPACE KPI_HOME_TS BEGIN BACKUP;
/* some_label */host XCOPY E:\ORADATA\FORX\KPI_HOME01.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */ALTER TABLESPACE KPI_HOME_TS END BACKUP;
/* some_label */ALTER TABLESPACE PCT_SUPPORT BEGIN BACKUP;
/* some_label */host XCOPY E:\ORADATA\FORX\PCT_SUPPORT.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */ALTER TABLESPACE PCT_SUPPORT END BACKUP;
/* some_label */ALTER TABLESPACE SYSTEM BEGIN BACKUP;
/* some_label */host XCOPY E:\ORADATA\FORX\SYSTEM01.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */ALTER TABLESPACE SYSTEM END BACKUP;
/* some_label */ALTER TABLESPACE TS_DEV BEGIN BACKUP;
/* some_label */host XCOPY E:\ORADATA\FORX\DEV\TS_DEV_01.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */host XCOPY E:\ORADATA\FORX\DEV\TS_DEV_02.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */host XCOPY E:\ORADATA\FORX\DEV\TS_DEV_03.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */host XCOPY E:\ORADATA\FORX\DEV\TS_DEV_04.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */host XCOPY E:\ORADATA\FORX\DEV\TS_DEV_05.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */host XCOPY E:\ORADATA\FORX\DEV\TS_DEV_06.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */host XCOPY E:\ORADATA\FORX\DEV\TS_DEV_07.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */host XCOPY E:\ORADATA\FORX\DEV\TS_DEV_08.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */ALTER TABLESPACE TS_DEV END BACKUP;
/* some_label */ALTER TABLESPACE TS_DEV2 BEGIN BACKUP;
/* some_label */host XCOPY E:\ORADATA\FORX\DEV2\TS_DEV2_01.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */host XCOPY E:\ORADATA\FORX\DEV2\TS_DEV2_02.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */host XCOPY E:\ORADATA\FORX\DEV2\TS_DEV2_03.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */host XCOPY E:\ORADATA\FORX\DEV2\TS_DEV2_04.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */host XCOPY E:\ORADATA\FORX\DEV2\TS_DEV2_05.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */host XCOPY E:\ORADATA\FORX\DEV2\TS_DEV2_06.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */host XCOPY E:\ORADATA\FORX\DEV2\TS_DEV2_07.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */host XCOPY E:\ORADATA\FORX\DEV2\TS_DEV2_08.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */ALTER TABLESPACE TS_DEV2 END BACKUP;
/* some_label */ALTER TABLESPACE TS_DEV3 BEGIN BACKUP;
/* some_label */host XCOPY E:\ORADATA\FORX\DEV3\TS_DEV3_01.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */host XCOPY E:\ORADATA\FORX\DEV3\TS_DEV3_02.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */host XCOPY E:\ORADATA\FORX\DEV3\TS_DEV3_03.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */host XCOPY E:\ORADATA\FORX\DEV3\TS_DEV3_04.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */host XCOPY E:\ORADATA\FORX\DEV3\TS_DEV3_05.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */host XCOPY E:\ORADATA\FORX\DEV3\TS_DEV3_06.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */host XCOPY E:\ORADATA\FORX\DEV3\TS_DEV3_07.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */host XCOPY E:\ORADATA\FORX\DEV3\TS_DEV3_08.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */ALTER TABLESPACE TS_DEV3 END BACKUP;
/* some_label */ALTER TABLESPACE TS_IP_KOREA BEGIN BACKUP;
/* some_label */host XCOPY E:\ORADATA\FORX\TS_IP_KOREA.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */ALTER TABLESPACE TS_IP_KOREA END BACKUP;
/* some_label */ALTER TABLESPACE TS_KIPI BEGIN BACKUP;
/* some_label */host XCOPY E:\ORADATA\FORX\TS_KIPI01.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */ALTER TABLESPACE TS_KIPI END BACKUP;
/* some_label */ALTER TABLESPACE TS_KIPIADM BEGIN BACKUP;
/* some_label */host XCOPY E:\ORADATA\FORX\TS_KIPIADM02.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */ALTER TABLESPACE TS_KIPIADM END BACKUP;
/* some_label */ALTER TABLESPACE TS_KIPIADM_IDX BEGIN BACKUP;
/* some_label */host XCOPY E:\ORADATA\FORX\TS_KIPIADMIDX01.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */ALTER TABLESPACE TS_KIPIADM_IDX END BACKUP;
/* some_label */ALTER TABLESPACE TS_KIPI_IDX BEGIN BACKUP;
/* some_label */host XCOPY E:\ORADATA\FORX\TS_KIPI_IDX01 M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */ALTER TABLESPACE TS_KIPI_IDX END BACKUP;
/* some_label */ALTER TABLESPACE TS_KIPI_SUPPORT BEGIN BACKUP;
/* some_label */host XCOPY E:\ORADATA\FORX\KIPO_SUPPORT.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */ALTER TABLESPACE TS_KIPI_SUPPORT END BACKUP;
/* some_label */ALTER TABLESPACE TS_PERF_DATA BEGIN BACKUP;
/* some_label */host XCOPY E:\ORADATA\FORX\TS_PERF_DATA01.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */host XCOPY E:\ORADATA\FORX\TS_PERF_DATA02.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */ALTER TABLESPACE TS_PERF_DATA END BACKUP;
/* some_label */ALTER TABLESPACE TS_PIPC_HOME BEGIN BACKUP;
/* some_label */host XCOPY E:\ORADATA\FORX\PIPC_HOME\TS_PIPC_HOME_01.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */host XCOPY E:\ORADATA\FORX\PIPC_HOME\TS_PIPC_HOME_02.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */host XCOPY E:\ORADATA\FORX\PIPC_HOME\TS_PIPC_HOME_03.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */host XCOPY E:\ORADATA\FORX\PIPC_HOME\TS_PIPC_HOME_04.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */host XCOPY E:\ORADATA\FORX\PIPC_HOME\TS_PIPC_HOME_05.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */host XCOPY E:\ORADATA\FORX\PIPC_HOME\TS_PIPC_HOME_06.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */host XCOPY E:\ORADATA\FORX\PIPC_HOME\TS_PIPC_HOME_07.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */host XCOPY E:\ORADATA\FORX\PIPC_HOME\TS_PIPC_HOME_08.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */ALTER TABLESPACE TS_PIPC_HOME END BACKUP;
/* some_label */ALTER TABLESPACE TS_SEARCH BEGIN BACKUP;
/* some_label */host XCOPY E:\ORADATA\FORX\TS_SEARCH01.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */host XCOPY E:\ORADATA\FORX\TS_SEARCH02.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */host XCOPY E:\ORADATA\FORX\TS_SEARCH03.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */ALTER TABLESPACE TS_SEARCH END BACKUP;
/* some_label */ALTER TABLESPACE TS_WEB_IDX BEGIN BACKUP;
/* some_label */host XCOPY E:\ORADATA\FORX\TS_WEB_IDX01.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */ALTER TABLESPACE TS_WEB_IDX END BACKUP;
/* some_label */ALTER TABLESPACE TS_WEB_TXT BEGIN BACKUP;
/* some_label */host XCOPY E:\ORADATA\FORX\TS_WEB_TXT01.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */ALTER TABLESPACE TS_WEB_TXT END BACKUP;
/* some_label */ALTER TABLESPACE UNDOTBS1 BEGIN BACKUP;
/* some_label */host XCOPY E:\ORADATA\FORX\UNDOTBS01.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */ALTER TABLESPACE UNDOTBS1 END BACKUP;
/* some_label */ALTER TABLESPACE UNDOTBS2 BEGIN BACKUP;
/* some_label */host XCOPY E:\ORADATA\FORX\UNDOTBS02.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */ALTER TABLESPACE UNDOTBS2 END BACKUP;
/* some_label */ALTER TABLESPACE USERS BEGIN BACKUP;
/* some_label */host XCOPY E:\ORADATA\FORX\USERS01.DBF M:\FORX1_ORADATA_BACKUP\ /F /Y
/* some_label */ALTER TABLESPACE USERS END BACKUP;
/* some_label */ALTER SYSTEM SWITCH LOGFILE;
/* some_label */ALTER DATABASE BACKUP CONTROLFILE 'M:\FORX1_ORADATA_BACKUP\CONTROLFILE\control01.ctl'
: ----------- end of embedded SQL-------------------------
:end
REM Logging end backup
SET ds=%date%_%time%
SET ds=-- Scheduled Job End : %ds:~0,22%
ECHO %ds%
ECHO.
* 참고 URL
