RMAN말고 데이터펌프 사용하여 백업하는 시나리오
1. 데일리로 full백업 받을 것 -> crontab 등록
2. 7일전 백업 파일은 지워지도록 만들기
PUMP파일을 받기 위한 DIRECTORY 생성
-- 경로 생성
$ mkdir -p /u01/app/oracle/backuptest
-- 디렉토리 생성
create or replace directory BACKUP
as '/u01/app/oracle/backuptest';
-- 확인
select *
from dba_directories
where directory_name = 'BACKUP';SHELL SCRIPT 만들기
※ crontab은 os에서 도는거라서 expdp명령어를 사용할 수 있는 경로를 지정해주어야 한다. 경로는 일반적으로 $ORACLE_HOME/bin임!
※ 혹은 /u01/app/oracle/product/19.0.0.0/dbhome_1/bin/expdp ~ 이렇게 사용 가능할듯
※ PATH를 지정 안해주니 (/u01/app/oracle/backuptest/script/DAILY_FULL_BACKUP.sh: line 21: expdp: command not found) 에러 났고 크론탭 안돌았음
vi DAILY_FULL_BACKUP.sh
#!/bin/bash
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
export ORACLE_SID=shsingle
export EDITOR=vi
export PATH=$PATH:$ORACLE_HOME/bin
##### SETTING
DATE=`date '+%Y%m%d'`
DT=/u01/app/oracle/backuptest
DUMP=FULLBACKUP_${DATE}_%U.dmp
LOG=FULLBACKUP_${DATE}.log
##### FULL BACKUP
expdp system/welcome1 full=y directory=BACKUP dumpfile=${DUMP} logfile=${LOG} job_name=FULLBACKUP
sleep 1
##### DELETE AFTER 1 MIN
find ${DT} -name "*.dmp" -mmin +1 -exec rm {} \;
find ${DT} -name "*.log" -mmin +1 -exec rm {} \;=> TEST라서 1분전꺼 모두 삭제되게 만들었음. 7일 전꺼 삭제하려면 -mtime +7 로 쓰면 됨.
CRONTAB 등록
[shsingle]oracle@shsingle:/u01/app/oracle/backuptest/script# crontab -e
no crontab for oracle - using an empty one
crontab: installing new crontab
-- 아래처럼 log떨어지게 하기
[shsingle]oracle@shsingle:/u01/app/oracle/backuptest/script# crontab -l
20 23 * * * /u01/app/oracle/backuptest/script/DAILY_FULL_BACKUP.sh >> /u01/app/oracle/backuptest/script/backup.log 2>&1** sh로 돌렸을 때 log임 잘되는데 gzip은 파일명 못찾는다고 안됨. 구냥 빼버리기..
Export: Release 19.0.0.0.0 - Production on Sat Sep 28 21:57:22 2024
Version 19.22.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYSTEM"."FULLBACKUP": system/******** full=y directory=BACKUP dumpfile=FULLBACKUP_20240928_%U.dmp logfile=FULLBACKUP_20240928.log job_name=FULLBACKUP
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/RADM_FPTM
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
. . exported "SYS"."KU$_USER_MAPPING_VIEW" 6.093 KB 36 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P261" 57.94 MB 25231 rows
. . exported "SYSTEM"."REDO_DB" 25.59 KB 1 rows
. . exported "WMSYS"."WM$WORKSPACES_TABLE$" 12.10 KB 1 rows
. . exported "WMSYS"."WM$HINT_TABLE$" 9.992 KB 97 rows
. . exported "WMSYS"."WM$WORKSPACE_PRIV_TABLE$" 7.085 KB 11 rows
. . exported "SYS"."DAM_CONFIG_PARAM$" 6.531 KB 14 rows
. . exported "SYS"."TSDP_SUBPOL$" 6.328 KB 1 rows
. . exported "WMSYS"."WM$NEXTVER_TABLE$" 6.382 KB 1 rows
. . exported "WMSYS"."WM$ENV_VARS$" 6.023 KB 3 rows
. . exported "SYS"."TSDP_PARAMETER$" 5.953 KB 1 rows
. . exported "SYS"."TSDP_POLICY$" 5.921 KB 1 rows
. . exported "WMSYS"."WM$VERSION_HIERARCHY_TABLE$" 5.984 KB 1 rows
. . exported "WMSYS"."WM$EVENTS_INFO$" 5.820 KB 12 rows
. . exported "AUDSYS"."AUD$UNIFIED":"AUD_UNIFIED_P0" 0 KB 0 rows
. . exported "SYS"."AUD$" 0 KB 0 rows
. . exported "SYS"."DAM_CLEANUP_EVENTS$" 0 KB 0 rows
. . exported "SYS"."DAM_CLEANUP_JOBS$" 0 KB 0 rows
. . exported "SYS"."TSDP_ASSOCIATION$" 0 KB 0 rows
. . exported "SYS"."TSDP_CONDITION$" 0 KB 0 rows
. . exported "SYS"."TSDP_FEATURE_POLICY$" 0 KB 0 rows
. . exported "SYS"."TSDP_PROTECTION$" 0 KB 0 rows
. . exported "SYS"."TSDP_SENSITIVE_DATA$" 0 KB 0 rows
. . exported "SYS"."TSDP_SENSITIVE_TYPE$" 0 KB 0 rows
. . exported "SYS"."TSDP_SOURCE$" 0 KB 0 rows
. . exported "SYSTEM"."REDO_LOG" 0 KB 0 rows
. . exported "WMSYS"."WM$BATCH_COMPRESSIBLE_TABLES$" 0 KB 0 rows
. . exported "WMSYS"."WM$CONSTRAINTS_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$CONS_COLUMNS$" 0 KB 0 rows
. . exported "WMSYS"."WM$LOCKROWS_INFO$" 0 KB 0 rows
. . exported "WMSYS"."WM$MODIFIED_TABLES$" 0 KB 0 rows
. . exported "WMSYS"."WM$MP_GRAPH_WORKSPACES_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$MP_PARENT_WORKSPACES_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$NESTED_COLUMNS_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$RESOLVE_WORKSPACES_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$RIC_LOCKING_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$RIC_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$RIC_TRIGGERS_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$UDTRIG_DISPATCH_PROCS$" 0 KB 0 rows
. . exported "WMSYS"."WM$UDTRIG_INFO$" 0 KB 0 rows
. . exported "WMSYS"."WM$VERSION_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$VT_ERRORS_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$WORKSPACE_SAVEPOINTS_TABLE$" 0 KB 0 rows
. . exported "MDSYS"."RDF_PARAM$" 6.515 KB 3 rows
. . exported "SYS"."AUDTAB$TBS$FOR_EXPORT" 5.960 KB 2 rows
. . exported "SYS"."DBA_SENSITIVE_DATA" 0 KB 0 rows
. . exported "SYS"."DBA_TSDP_POLICY_PROTECTION" 0 KB 0 rows
. . exported "SYS"."FGA_LOG$FOR_EXPORT" 0 KB 0 rows
. . exported "SYS"."NACL$_ACE_EXP" 0 KB 0 rows
. . exported "SYS"."NACL$_HOST_EXP" 6.921 KB 1 rows
. . exported "SYS"."NACL$_WALLET_EXP" 0 KB 0 rows
. . exported "SYS"."SQL$TEXT_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQL$_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$AUXDATA_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$DATA_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$PLAN_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$_DATAPUMP" 0 KB 0 rows
. . exported "SYSTEM"."SCHEDULER_JOB_ARGS" 0 KB 0 rows
. . exported "SYSTEM"."SCHEDULER_PROGRAM_ARGS" 0 KB 0 rows
. . exported "WMSYS"."WM$EXP_MAP" 7.718 KB 3 rows
. . exported "WMSYS"."WM$METADATA_MAP" 0 KB 0 rows
Master table "SYSTEM"."FULLBACKUP" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.FULLBACKUP is:
/u01/app/oracle/backuptest/FULLBACKUP_20240928_01.dmp
Job "SYSTEM"."FULLBACKUP" successfully completed at Sat Sep 28 22:02:08 2024 elapsed 0 00:04:44
gzip: /u01/app/oracle/backuptest/FULLBACKUP_20240928_%U.dmp: No such file or directory
Slow and steady wins the race.