프린트 하기

OS환경 : Oracle Linux7.3(64bit)


DB 환경 : Oracle Database 18.1.0.0


쿼리 : 오라클 hot 백업 스크립트, vi로 파일 하나씩 만들어서 사용하면 된다.

다운로드용 파일과 복사 붙여넣기용 스크립트는 같은 내용입니다.


기본적인 경로

스크립트, 로그 저장 경로 : /home/oracle/hot/script/

핫 백업 경로 : /home/oracle/hot/backup1


다운로드용 파일

hotbackup_allfile.txt


복사 붙여넣기용 스크립트

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

티스토리툴바