db source(pl/sql) backup
출처 : https://logic.edchen.org/how-to-backup-pl-sql-codes-in-oracle/
How to Backup PL/SQL Codes in Oracle - Ed Chen Logic
Backup Packages and Procedures Usually, source codes of programming unit should be version-controlled by the developer team. Mostly, they use Git, CVS or SVN to keep source codes in control. But if they have never carried out their job, we can also do it b
logic.edchen.org
DB 형상 반영 하다보면, 가끔 소스를 원복해야 할일이 생긴다.
소스 관리는 개발자가 해야 겠지만, 급하게 원복을 위해서, OBJECT 별로 소스 백업을 받아보자 .
1. 백업 대상은 PROCEDURE, FUNCTION, PACKAGE, TRIGGER 이다.
2. Object 별로 SQL 로 보관하여, 소스 원복 시, 개별 원복 하면 된다.
[oracle@oel7 source_backup]$ ls
getallcode.sql getcode.sql getSourceCodes.sh scott
[oracle@oel7 source_backup]$ pwd
/home/oracle/source_backup
[oracle@oel7 source_backup]$ ls -al
total 16
drwxr-xr-x. 3 oracle oinstall 85 Nov 24 18:50 .
drwx------. 10 oracle oinstall 4096 Nov 24 18:50 ..
-rw-r--r--. 1 oracle oinstall 486 Nov 24 18:49 getallcode.sql
-rw-r--r--. 1 oracle oinstall 467 Nov 24 18:49 getcode.sql
-rw-r--r--. 1 oracle oinstall 379 Nov 24 18:48 getSourceCodes.sh
drwxr-xr-x. 3 oracle oinstall 22 Nov 24 18:49 scott
[oracle@oel7 source_backup]$ cat getSourceCodes.sh
#!/bin/bash
. ~/.bash_profile
USER=scott
PASSWD=tiger
BACKUP_DIR=/home/oracle/source_backup/$USER
BACKUP_DIR_TODAY=$BACKUP_DIR/`date +'%Y%m%d'`
mkdir -p $BACKUP_DIR_TODAY
cd $BACKUP_DIR_TODAY
ln -s ../../getcode.sql getcode.sql
ln -s ../../getallcode.sql getallcode.sql
sqlplus -s $USER/$PASSWD @getallcode.sql
find $BACKUP_DIR -type d -mtime +29 -name '20*' -exec rm -rf {} \;
[oracle@oel7 source_backup]$ cat getallcode.sql
et termout off
set heading off
set feedback off
set linesize 50
spool xtmpx.sql
select '@getcode ' || object_name
from dba_objects
where object_type in ( 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TRIGGER' )
and owner ='SCOTT'
/
spool off
spool getallcode_INSTALL.sql
select '@' || object_name
from dba_objects
where object_type in ( 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TRIGGER' )
and owner ='SCOTT'
/
spool off
set heading on
set feedback on
set linesize 130
set termout on
@xtmpx.sql
exit
[oracle@oel7 source_backup]$ cat getcode.sql
set feedback off
set heading off
set termout off
set linesize 1000
set trimspool on
set verify off
spool &1..sql
prompt set define off
select decode( type||'-'||to_char(line,'fm99999'),
'PACKAGE BODY-1', '/'||chr(10),
null) ||
decode(line,1,'create or replace ', '' ) ||
text text
from dba_source
where name = upper('&&1')
and owner='SCOTT'
order by type, line;
prompt /
prompt set define on
spool off
set feedback on
set heading on
set termout on
set linesize 100
[oracle@oel7 source_backup]$ ls -al
total 16
drwxr-xr-x. 3 oracle oinstall 85 Nov 24 18:50 .
drwx------. 10 oracle oinstall 4096 Nov 24 18:50 ..
-rw-r--r--. 1 oracle oinstall 486 Nov 24 18:49 getallcode.sql
-rw-r--r--. 1 oracle oinstall 467 Nov 24 18:49 getcode.sql
-rw-r--r--. 1 oracle oinstall 379 Nov 24 18:48 getSourceCodes.sh
drwxr-xr-x. 3 oracle oinstall 22 Nov 24 18:49 scott
[oracle@oel7 source_backup]$ cd scott/2022*
[oracle@oel7 20221124]$ ls -al
total 40
drwxr-xr-x. 2 oracle oinstall 4096 Nov 24 18:49 .
drwxr-xr-x. 3 oracle oinstall 22 Nov 24 18:49 ..
-rw-r--r--. 1 oracle oinstall 2837 Nov 24 18:50 EMP_INFO.sql
-rw-r--r--. 1 oracle oinstall 354 Nov 24 18:50 FUNC_ADD_MONTHS.sql
-rw-r--r--. 1 oracle oinstall 113 Nov 24 18:50 getallcode_INSTALL.sql
lrwxrwxrwx. 1 oracle oinstall 20 Nov 24 18:49 getallcode.sql -> ../../getallcode.sql
lrwxrwxrwx. 1 oracle oinstall 17 Nov 24 18:49 getcode.sql -> ../../getcode.sql
-rw-r--r--. 1 oracle oinstall 297 Nov 24 18:50 TABLE_FUNC.sql
-rw-r--r--. 1 oracle oinstall 434 Nov 24 18:50 TEST_INTERIM2_TRG.sql
-rw-r--r--. 1 oracle oinstall 431 Nov 24 18:50 TEST_INTERIM_TRG.sql
-rw-r--r--. 1 oracle oinstall 192 Nov 24 18:50 TR_CHECK_TRIGGER_TABLE.sql
-rw-r--r--. 1 oracle oinstall 264 Nov 24 18:50 UPDATE_SAL.sql
-rw-r--r--. 1 oracle oinstall 169 Nov 24 18:50 xtmpx.sql
[oracle@oel7 20221124]$-- crontab 등록을 위해, 퍼미션 조정
[oracle@oel7 source_backup]$ chmod 770 getSourceCodes.sh
[oracle@oel7 source_backup]$
-- 매일 아침 5시 11분 소스 백업 받도록 crontab 등록
[oracle@oel7 source_backup]$ crontab -l
#Sample Code
#minute (0-59),
#| hour (0-23),
#| | day of the month (1-31),
#| | | month of the year (1-12),
#| | | | day of the week (0-6 with 0=Sunday).
#| | | | | commands
#0 2 * * 0,4 /etc/cron.d/logcheck
###################################################################################
# Backup SP Source
11 5 * * * //home/oracle/source_backup/getSourceCodes.sh >/dev/null 2>&1
[oracle@oel7 source_backup]$-- 패키지 백업 결과 -- 한글이 깨지는게 아쉽지만, 우선 서비스부터 살리고 볼일이다.
[oracle@oel7 20221124]$ cat EMP_INFO.sql
set define off
create or replace PACKAGE emp_info AS
PROCEDURE all_emp_info; -- ?? ??? ?? ??
PROCEDURE all_sal_info; -- ?? ??? ?? ??
-- ?? ??? ?? ??
PROCEDURE dept_emp_info (v_deptno IN NUMBER) ;
-- ?? ??? ?? ??
PROCEDURE dept_sal_info (v_deptno IN NUMBER) ;
END emp_info;
/
create or replace PACKAGE BODY emp_info AS
-- ?? ??? ?? ??
PROCEDURE all_emp_info
IS
CURSOR emp_cursor IS
SELECT empno, ename, to_char(hiredate, 'RRRR/MM/DD') hiredate
FROM emp
ORDER BY hiredate;
BEGIN
FOR aa IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('?? : ' || aa.empno);
DBMS_OUTPUT.PUT_LINE('?? : ' || aa.ename);
DBMS_OUTPUT.PUT_LINE('??? : ' || aa.hiredate);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM||'?? ?? ');
END all_emp_info;
-- ?? ??? ?? ??
PROCEDURE all_sal_info
IS
CURSOR emp_cursor IS
SELECT round(avg(sal),3) avg_sal, max(sal) max_sal, min(sal) min_sal
FROM emp;
BEGIN
FOR aa IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('?????? : ' || aa.avg_sal);
DBMS_OUTPUT.PUT_LINE('?????? : ' || aa.max_sal);
DBMS_OUTPUT.PUT_LINE('?????? : ' || aa.min_sal);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM||'?? ?? ');
END all_sal_info;
--?? ??? ?? ??
PROCEDURE dept_emp_info (v_deptno IN NUMBER)
IS
CURSOR emp_cursor IS
SELECT empno, ename, to_char(hiredate, 'RRRR/MM/DD') hiredate
FROM emp
WHERE deptno = v_deptno
ORDER BY hiredate;
BEGIN
FOR aa IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('?? : ' || aa.empno);
DBMS_OUTPUT.PUT_LINE('?? : ' || aa.ename);
DBMS_OUTPUT.PUT_LINE('??? : ' || aa.hiredate);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM||'?? ?? ');
END dept_emp_info;
--?? ??? ?? ??
PROCEDURE dept_sal_info (v_deptno IN NUMBER)
IS
CURSOR emp_cursor IS
SELECT round(avg(sal),3) avg_sal, max(sal) max_sal, min(sal) min_sal
FROM emp
WHERE deptno = v_deptno;
BEGIN
FOR aa IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('?????? : ' || aa.avg_sal);
DBMS_OUTPUT.PUT_LINE('?????? : ' || aa.max_sal);
DBMS_OUTPUT.PUT_LINE('?????? : ' || aa.min_sal);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM||'?? ?? ');
END dept_sal_info;
END emp_info;
/
set define on
[oracle@oel7 20221124]$