내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
OS환경 : Oracle Linux7.3(64bit)
DB 환경 : Oracle Database 18.1.0.0
쿼리 : 오라클 hot 백업 스크립트, vi로 파일 하나씩 만들어서 사용하면 된다.
다운로드용 파일과 복사 붙여넣기용 스크립트는 같은 내용입니다.
기본적인 경로
스크립트, 로그 저장 경로 : /home/oracle/hot/script/
핫 백업 경로 : /home/oracle/hot/backup1
다운로드용 파일
복사 붙여넣기용 스크립트
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 | #main_backup.sh touch /home/oracle/hot/script/total.log echo "" echo "set begin backup mode--" time sh /home/oracle/hot/script/begin_backup.sh >> /home/oracle/hot/script/total.log echo "" echo "end begin backup mode--" echo "" echo "start file copy.........." time sh /home/oracle/hot/script/copy_backup.sh >> /home/oracle/hot/script/total.log echo "end file copy~" echo "" echo "set end backup mode~~" time sh /home/oracle/hot/script/end_backup.sh >> /home/oracle/hot/script/total.log echo "complete hot backup~!" #begin_backup.sh sqlplus /nolog <<EOF1 conn / as sysdba set head off set feedback off set time off set timing off set echo off spool /tmp/online.tmp select 'alter tablespace '||tablespace_name||' begin backup;' from dba_tablespaces where status='ONLINE' and contents!='TEMPORARY'; spool off !cat /tmp/online.tmp | egrep -v spool | egrep -v SQL | egrep -v [2-4] > /home/oracle/hot/script/begin.sh @/home/oracle/hot/script/begin.sh !sh /home/oracle/hot/script/status.sh exit EOF1 #copy_backup.sh sqlplus /nolog << EOF3 conn / as sysdba set head off set time off set timing off set feedback off set echo off set line 200 col name for a100 spool /tmp/cp.tmp select 'cp -av '||name||' /home/oracle/hot/backup1/' from v\$datafile; spool off spool /tmp/control.tmp alter session set nls_date_format='YYYY-MM-DD-HH24-MI-SS'; select 'alter database backup controlfile to '||'''/home/oracle/hot/backup1/'||sysdate||'.ctl'';' from dual; spool off !cat /tmp/cp.tmp | egrep -v SQL > /home/oracle/hot/script/cp.sh !cat /tmp/control.tmp | egrep -v SQL > /home/oracle/hot/script/control.sql !sh /home/oracle/hot/script/cp.sh @/home/oracle/hot/script/control.sql exit EOF3 #end_backup.sh sqlplus /nolog << EOF4 conn / as sysdba set head off set feedback off set time off set timing off set echo off spool /tmp/online.tmp select 'alter tablespace '||tablespace_name||' end backup;' from dba_tablespaces where status='ONLINE' and contents!='TEMPORARY'; spool off !cat /tmp/online.tmp | egrep -v spool | egrep -v SQL | egrep -v [2-4] > /home/oracle/hot/script/end.sh @/home/oracle/hot/script/end.sh !sh /home/oracle/hot/script/status.sh exit EOF4 #status.sh sqlplus /nolog << EOF2 conn / as sysdba set head on set echo off set feedback off spool /tmp/status.tmp set line 200 col name for a50 col status for a15 select a.file#,a.name,b.status,to_char(b.time,'YYYY-MM-DD:HH24:MI:SS') "Time" from v\$datafile a, v\$backup b where a.file#=b.file#; spool off exit EOF2 |
결과값 :
$ cat total.log
SQL*Plus: Release 18.0.0.0.0 Production on Tue Jul 10 14:43:47 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2017, Oracle. All rights reserved.
SQL> Connected.
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>
alter tablespace SYSTEM begin backup;
alter tablespace SYSAUX begin backup;
alter tablespace UNDOTBS1 begin backup;
alter tablespace ORCLTS begin backup;
SQL> SQL>
SQL> SQL>
SQL*Plus: Release 18.0.0.0.0 Production on Tue Jul 10 14:43:47 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2017, Oracle. All rights reserved.
SQL> Connected.
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>
FILE# NAME STATUS Time
---------- -------------------------------------------------- --------------- -------------------
1 /oracle/app/oracle/oradata/ORCL/system01.dbf ACTIVE 2018-07-10:14:43:47
2 /oracle/app/oracle/oradata/ORCL/sysaux01.dbf ACTIVE 2018-07-10:14:43:47
3 /oracle/app/oracle/oradata/ORCL/undotbs01.dbf ACTIVE 2018-07-10:14:43:47
4 /oracle/app/oracle/oradata/ORCLTS01.dbf ACTIVE 2018-07-10:14:43:47
Elapsed: 00:00:00.02
SQL> SQL> Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
SQL> Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
SQL*Plus: Release 18.0.0.0.0 Production on Tue Jul 10 14:43:48 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2017, Oracle. All rights reserved.
SQL> Connected.
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>
cp -av /oracle/app/oracle/oradata/ORCL/system01.dbf /home/oracle/hot/backup1/
cp -av /oracle/app/oracle/oradata/ORCL/sysaux01.dbf /home/oracle/hot/backup1/
cp -av /oracle/app/oracle/oradata/ORCL/undotbs01.dbf /home/oracle/hot/backup1/
cp -av /oracle/app/oracle/oradata/ORCLTS01.dbf /home/oracle/hot/backup1/
SQL> SQL> SQL> SQL> SQL>
alter database backup controlfile to '/home/oracle/hot/backup1/2018-07-10-14-43-48.ctl';
SQL> SQL>
SQL>
SQL> SQL> '/oracle/app/oracle/oradata/ORCL/system01.dbf' -> '/home/oracle/hot/backup1/system01.dbf'
'/oracle/app/oracle/oradata/ORCL/sysaux01.dbf' -> '/home/oracle/hot/backup1/sysaux01.dbf'
'/oracle/app/oracle/oradata/ORCL/undotbs01.dbf' -> '/home/oracle/hot/backup1/undotbs01.dbf'
'/oracle/app/oracle/oradata/ORCLTS01.dbf' -> '/home/oracle/hot/backup1/ORCLTS01.dbf'
SQL> SQL> Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
SQL*Plus: Release 18.0.0.0.0 Production on Tue Jul 10 14:44:10 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2017, Oracle. All rights reserved.
SQL> Connected.
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>
alter tablespace SYSTEM end backup;
alter tablespace SYSAUX end backup;
alter tablespace UNDOTBS1 end backup;
alter tablespace ORCLTS end backup;
SQL> SQL>
SQL> SQL>
SQL*Plus: Release 18.0.0.0.0 Production on Tue Jul 10 14:44:10 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2017, Oracle. All rights reserved.
SQL> Connected.
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>
FILE# NAME STATUS Time
---------- -------------------------------------------------- --------------- -------------------
1 /oracle/app/oracle/oradata/ORCL/system01.dbf NOT ACTIVE 2018-07-10:14:43:47
2 /oracle/app/oracle/oradata/ORCL/sysaux01.dbf NOT ACTIVE 2018-07-10:14:43:47
3 /oracle/app/oracle/oradata/ORCL/undotbs01.dbf NOT ACTIVE 2018-07-10:14:43:47
4 /oracle/app/oracle/oradata/ORCLTS01.dbf NOT ACTIVE 2018-07-10:14:43:47
Elapsed: 00:00:00.00
SQL> SQL> Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
SQL> Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
참조 :
'ORACLE > Sql' 카테고리의 다른 글
오라클 샘플 데이터 만들기, 테스트 데이터 벌크인서트 + dbms_random (2) | 2018.07.18 |
---|---|
오라클 프로시저, 잡, 트리거 내용 확인 방법 (0) | 2018.07.18 |
현재 실행중인 SQL 쿼리 확인 (0) | 2018.07.10 |
ORACLE 유저 권한 조회 (0) | 2018.07.10 |
FULL SQL TEXT를 찾는 방법 (SQL FULLTEXT) Note 437304.1 (0) | 2018.07.09 |