cold backup
SQL> select file_name from dba_data_files;
SQL> select name from v$controlfile;
SQL> select group#, member
2 from v$logfile
3 order by group#;
1) db를 내린다.
2) datafile, contolfile, redolog file 을 백업한다
[oracle@test ~]$ cd /oracle/app/oracle/oradata/ORCL
[oracle@test ORCL]$
[oracle@test ORCL]$
[oracle@test ORCL]$ ls
control01.ctl control02.ctl redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
[oracle@test ORCL]$ cp * /home/oracle/coldbackup/
[oracle@test ORCL]$ cd /home/oracle/coldbackup/
[oracle@test coldbackup]$ ls -al
total 3078636
drwxr-xr-x 2 oracle dba 4096 Sep 23 10:04 .
drwx------ 11 oracle dba 4096 Sep 23 09:51 ..
-rw-r----- 1 oracle dba 10600448 Sep 23 10:03 control01.ctl
-rw-r----- 1 oracle dba 10600448 Sep 23 10:03 control02.ctl
-rw-r----- 1 oracle dba 209715712 Sep 23 10:03 redo01.log
-rw-r----- 1 oracle dba 209715712 Sep 23 10:03 redo02.log
-rw-r----- 1 oracle dba 209715712 Sep 23 10:03 redo03.log
-rw-r----- 1 oracle dba 985669632 Sep 23 10:04 sysaux01.dbf
-rw-r----- 1 oracle dba 1017126912 Sep 23 10:04 system01.dbf
-rw-r----- 1 oracle dba 181411840 Sep 23 10:04 temp01.dbf
-rw-r----- 1 oracle dba 487596032 Sep 23 10:04 undotbs01.dbf
-rw-r----- 1 oracle dba 5251072 Sep 23 10:04 users01.dbf3)
[oracle@test coldbackup]$ ss
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Sep 23 10:12:30 2024
Version 19.24.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 2516579472 bytes
Fixed Size 8942736 bytes
Variable Size 671088640 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7831552 bytes
Database mounted.
Database opened.
SQL>
사용자 관리 cold backup 하기
cold backup 과 hot backup 의 차이는 db 를 내리고 백업하느냐 아니면 db 운영중에 백업하느냐의 차이입니다.
■ 실습1. db 모드를 아카이브 모드로 변환하시오 !
#1. archive log list
#2. shutdown immediate
#3. startup mount
#4. alter database archivelog;
#5. alter database open;
#6. archive log list
SQL> archive log list
SP2-0718: illegal ARCHIVE LOG option
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 2516579472 bytes
Fixed Size 8942736 bytes
Variable Size 671088640 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7831552 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oraarch
Oldest online log sequence 24
Next log sequence to archive 26
Current log sequence 26
SQL>
system data file 을 삭제하고 복구 하기
system data file ? 오라클을 운영하기 위한 데이터가 들어있는 파일
예: system tablespace, sysaux tablespace, undotbs tablespace
system data file 들은 db 올리고 복구를 할 수 없습니다.
항상 mount 상태에서 복구해야합니다. ■ 실습
#1. 다시 한번 coldbackup 을 수행하는데 /home/oracle 밑에 coldbackup2 라는
폴더를 만들고 수행합니다.
sys> shutdown immediate
$ cd
$ mkdir coldbackup2
$ cp /oracle/app/oracle/oradata/ORCL/* /home/oracle/coldbackup2/
sys> startup
#2. 다시 한번 hotbackup 을 수행하는데 /home/oracle 밑에 hotbackup3 라는 폴더를
만들고 수행합니다.
SQL> select 'alter tablespace ' || tablespace_name || ' begin backup;'
2 from dba_data_files;
'ALTERTABLESPACE'||TABLESPACE_NAME||'BEGINBACKUP;'
-------------------------------------------------------------
alter tablespace SYSTEM begin backup;
alter tablespace SYSAUX begin backup;
alter tablespace UNDOTBS1 begin backup;
alter tablespace USERS begin backup;
alter tablespace CUPPANG01 begin backup;
alter tablespace CUPPANG02 begin backup;
alter tablespace CUPPANG03 begin backup;
select * from v$backup;
SQL> select 'cp '|| file_name ||' /home/oracle/hotbackup3/'
2 from dba_data_files;
'CP'||FILE_NAME||'/HOME/ORACLE/HOTBACKUP3/'
--------------------------------------------------------------------------------
cp /oracle/app/oracle/oradata/ORCL/system01.dbf /home/oracle/hotbackup3/
cp /oracle/app/oracle/oradata/ORCL/sysaux01.dbf /home/oracle/hotbackup3/
cp /oracle/app/oracle/oradata/ORCL/undotbs01.dbf /home/oracle/hotbackup3/
cp /oracle/app/oracle/oradata/ORCL/users01.dbf /home/oracle/hotbackup3/
cp /oracle/app/oracle/oradata/ORCL/cuppang01.dbf /home/oracle/hotbackup3/
cp /oracle/app/oracle/oradata/ORCL/cuppang02.dbf /home/oracle/hotbackup3/
cp /oracle/app/oracle/oradata/ORCL/cuppang03.dbf /home/oracle/hotbackup3/
cd
mkdir hotbackup3
SQL> select 'alter tablespace '|| tablespace_name ||
2 ' end backup;'
3 from dba_data_files;
'ALTERTABLESPACE'||TABLESPACE_NAME||'ENDBACKUP;'
-----------------------------------------------------------
alter tablespace SYSTEM end backup;
alter tablespace SYSAUX end backup;
alter tablespace UNDOTBS1 end backup;
alter tablespace USERS end backup;
alter tablespace CUPPANG01 end backup;
alter tablespace CUPPANG02 end backup;
alter tablespace CUPPANG03 end backup;
select * from v$backup;
#3. /oracle/app/oracle/oradata/ORCL/system01.dbf 를 삭제합니다.
$ rm /oracle/app/oracle/oradata/ORCL/system01.dbf#4. db 를 startup force 해서 내렸다 올립니다.
yys(SYS) > startup force
#5. mount 에서 멈추게 되면 복구해야할 파일이 뭔지 확인을 합니다.
SQL > select * from v$recover_file;
$ cd /oracle/app/oracle/diag/rdbms/orcl/orcl/trace
$ vi alert*
ORA-01110: data file 1: '/oracle/app/oracle/oradata/ORCL/system01.dbf'
2024-09-23T16:29:09.933272+09:00
#6. 백업받은 system01.dbf를 복원합니다.
$ cd
$ cp /home/oracle/hotbackup3/system01.dbf /oracle/app/oracle/oradata/ORCL/system01.dbf
#7. 복원한 파일을 복구 합니다.
sys> recover datafile 1;
#8. db 를 open 시킵니다.
sys> alter database open;
