프린트 하기

내맘대로긍정이 알려주는

Oracle 23ai 신기능
무료 세미나 발표자료

다운로드 trending_flat

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




참조 :