일관성 있는 백업, 일관성 없는 백업 / 아카이브 단일화

############ 일관성 있는 백업 수행 ############
0) redo log file의 seq# 체크해놓기

select * from v$log;

1) 정상 DB종료
SQL> shutdown immediate

2) cp하기
SQL> !
[oracle@oracle ~]$ cd /u01/app/oracle/oradata/ora11g
[oracle@oracle ora11g]$ ls
control01.ctl  redo01.log  redo03.log    system01.dbf    undotbs.dbf
example01.dbf  redo02.log  sysaux01.dbf  temp_new01.dbf  users01.dbf

[oracle@oracle ~]$ cd backup/arch/
[oracle@oracle arch]$ mkdir cold_20240117
[oracle@oracle arch]$ cd cold_20240117

[oracle@oracle cold_20240117]$ pwd
/home/oracle/backup/arch/cold_20240117
[oracle@oracle cold_20240117]$ cp /u01/app/oracle/oradata/ora11g/*.* .
[oracle@oracle cold_20240117]$ ls
control01.ctl  redo01.log  redo03.log    system01.dbf    undotbs.dbf
example01.dbf  redo02.log  sysaux01.dbf  temp_new01.dbf  users01.dbf

3) DB올리기
SQL> startup

4) pfile 만들기
SQL> create pfile='/home/oracle/backup/arch/cold_20240117/initora11g_20240117.ora' from spfile;

5) 조회
SQL> !
[oracle@oracle ~]$ cd /home/oracle1/backup/arch/hot_20240117/
[oracle@oracle cold_20240117]$ ls

control01.ctl  initora11g_20240117.ora  redo02.log  sysaux01.dbf  temp01.dbf     users01.dbf
example01.dbf  redo01.log               redo03.log  system01.dbf  undotbs01.dbf

 

############ 일관성 없는 백업 수행 ############
1) 스크립트 만들기
select 'cp -av '||name||' /home/oracle/backup/arch/hot_20240117/' from v$datafile
union all
select 'cp -av '||name||' /home/oracle/backup/arch/hot_20240117/' from v$tempfile;
==================================================================
cp -av /u01/app/oracle/oradata/ora11g/system01.dbf /home/oracle/backup/arch/hot_20240117/
cp -av /u01/app/oracle/oradata/ora11g/sysaux01.dbf /home/oracle/backup/arch/hot_20240117/
cp -av /u01/app/oracle/oradata/ora11g/users01.dbf /home/oracle/backup/arch/hot_20240117/
cp -av /u01/app/oracle/oradata/ora11g/example01.dbf /home/oracle/backup/arch/hot_20240117/
cp -av /u01/app/oracle/oradata/ora11g/undotbs01.dbf /home/oracle/backup/arch/hot_20240117/
cp -av /u01/app/oracle/oradata/ora11g/temp01.dbf /home/oracle/backup/arch/hot_20240117/
==================================================================

2) 디렉토리 및 쉘 만들기
[oracle@oracle ~]$ cd backup/arch/
[oracle@oracle arch]$ mkdir hot_20240117
[oracle@oracle arch]$ cd hot_20240117

 

[oracle@oracle hot_20240117]$ vi backup.sh

3) begin backup
SQL> alter database begin backup;
--> Database altered.
--> ckpt발생 이 이후에는 dbf에 write하지마 막는작업

4) sh 실행
SQL> !
[oracle@oracle ~]$ cd backup/arch/hot_20240117
[oracle@oracle hot_20240117]$ vi backup.sh
[oracle@oracle hot_20240117]$ sh backup.sh > backup.log

[oracle@oracle hot_20240117]$ vi backup.log

[oracle@oracle hot_20240117]$ ls
backup.log  example01.dbf  system01.dbf    undotbs.dbf
backup.sh   sysaux01.dbf   temp_new01.dbf  users01.dbf

[oracle@oracle hot_20240117]$ exit
exit

5) end backup
SQL> alter database end backup;

#) 조회해야함

SELECT a.file#, a.name, a.checkpoint_change#, b.status, b.change#, b.time
FROM v$datafile a, v$backup b
WHERE a.file# = b.file#;

6) controlfile backup

alter database backup controlfile to '/home/oracle/backup/arch/hot_20240117/control01.ctl';

7) 아카이빙하기

alter system archive log current;

--> 그동안 redo log buffer에 담아둔 redo 정보들이 redo log file에 담기는 내용들 
(백업받는 시점(현재) current한 정보가) 유실되면 안되니까 아카이브 받아놓는거임

8) redo log file의 seq# 체크해놓기

select * from v$log;

--> 현재 current한 seq#2

9) 아카이브 파일 조회하기

select * from v$archived_log;

--> resetlogs_id가 바뀐것만 확인!
--> 백업 정책에 따라 아카이브, 백업파일을 지우는 것에 신중해야함

 

select SEQUENCE#, RESETLOGS_CHANGE#, resetlogs_id, name from v$archived_log;

#############아카이브 단일화#################
1. (사전작업)아카이브 경로 수정 (아카이브 단일화)
1)

 select destination, binding, status 
 from v$archive_dest 
 where DESTINATION in ('/home/oracle/arch1', '/home/oracle/arch2');

 

2) 파라미터 조회
SQL> show parameter log_archive_dest_1
location=/home/oracle/arch1 mandatory

SQL> show parameter log_archive_dest_2
location=/home/oracle/arch2 optional 

3) 경로 취소

alter system set log_archive_dest_2='' scope=spfile;

--> ''으로하면 경로 취소
--> System SET이(가) 변경되었습니다

또는

alter system reset log_archive_dest_2 scope=spfile;

4) 적용하려면, 정상적인 DB 내렸다가 올리기
SQL> shutdown immediate
SQL> startup

5) show parameter log_archive_dest_2
--> 경로삭제완료

6) 기존 pfile은 경로를 가지고 있으니, 현재 수정한 값을 가지고있는 spfile로 pfile을 생성해준다.
(DB 올라왔을 때, 실행)

create pfile from spfile;

7) 로그 조회

select * from v$log;

8) 로그스위치 수행
SQL> alter system switch logfile;

9) 단일로 아카이브 떨어진 것 확인

select * from v$archived_log;

--> /home/oracle/arch1/arch_1_15_1156667649.arc

9-1) 아카이브 로그 확인

archive log list

10) 로그 조회

select * from v$log;

 

11) 물리적인 아카이브파일 조회
SQL> ! ls /home/oracle/arch1 /home/oracle/arch2

12) 필요없는 arch2 디렉토리 삭제
SQL> !
[oracle@oracle ~]$ rm -r arch2

[oracle@oracle ~]$ ls

#############아카이브 단일화#################