Oracle 19c HOT Backup TEST해보기

안녕하세요

 

오늘은 Oracle 19c의 HOT backup을 TEST 해보도록 하겠습니다.

 

먼저 TEST 진행을 위해 환경을 구성하겠습니다.

########## HOT Backup Test 환경구성 ########## 

1. 아카이브 모드 확인

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 8 10:50:46 2020
Version 19.8.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0

SQL> archive log list 
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       /shcsw/oracle/app/oracle/arch
Oldest online log sequence     517
Next log sequence to archive   519
Current log sequence	       519


2. 기존 Tablespace 확인

SQL> select tablespace_name, bytes/1024/1024/1024 GB, file_name from dba_data_files;


TABLESPACE_NAME 		       GB 	FILE_NAME
------------------------------ ---------- ------------------------------------------------------------
SYSTEM					.68359375 	+DATA/ORCL19/DATAFILE/system.260.1022509767
SYSAUX			       2.40234375 	+DATA/ORCL19/DATAFILE/sysaux.261.1022509775
UNDOTBS1		       1.66015625 	+DATA/ORCL19/DATAFILE/undotbs1.262.1022509781
USERS			       .009765625 	+DATA/ORCL19/DATAFILE/users.264.1022509789
TEST					  			/dev/raw/raw1
STATSPACK_TS			 .1953125 	+DATA/ORCL19/DATAFILE/statspack_ts.266.1047077147

6 rows selected.

3. TEST Tablespace 생성 및 확인

SQL> create tablespace hottest datafile '/home/oracle/test/hottest.dbf' size 50m autoextend on next 10m maxsize 1g;

Tablespace created.

SQL> select tablespace_name, bytes/1024/1024/1024 GB, file_name from dba_data_files;

TABLESPACE_NAME 		       GB 	FILE_NAME
------------------------------ ---------- ------------------------------------------------------------
SYSTEM					.68359375 	+DATA/ORCL19/DATAFILE/system.260.1022509767
SYSAUX			       2.40234375 	+DATA/ORCL19/DATAFILE/sysaux.261.1022509775
UNDOTBS1		       1.66015625 	+DATA/ORCL19/DATAFILE/undotbs1.262.1022509781
USERS			       .009765625 	+DATA/ORCL19/DATAFILE/users.264.1022509789
TEST					  			/dev/raw/raw1
STATSPACK_TS			 .1953125 	+DATA/ORCL19/DATAFILE/statspack_ts.266.1047077147
HOTTEST 		       .048828125 	/home/oracle/test/hottest.dbf

7 rows selected.

4. TEST User 생성/권한부여 및 TEST Table 생성

SQL> create user hotuser identified by hotuser default tablespace hottest;

User created.

SQL> grant dba to hotuser;

Grant succeeded.

SQL> conn hotuser
Enter password: 
Connected.
SQL> show user
USER is "HOTUSER"
SQL> create table hottable(no number(10), name varchar2(10));

Table created.

SQL> insert into hottable values(1, 'park');

1 row created.

SQL> insert into hottable values(2, 'kim');

1 row created.

SQL> insert into hottable values(3, 'kang');

1 row created.

SQL> select * from hottable;

	NO NAME
---------- ----------
	 1 park
	 2 kim
	 3 kang

TEST를 위해 HOTTEST라는 테이블 스페이스와 이를 default로 사용하는 유저인 HOTUSER를 생성하였고,

해당 유저에 HOTTABLE이라는 테이블을 생성하였습니다.

 

TEST를 진행하도록 하겠습니다.

########## HOT Backup Test ########## 

1. begin backup 실행

SQL> alter tablespace hottest begin backup;

Tablespace altered.

2. backup 상태 확인

SQL> select a.tablespace_name, b.*
  2  from dba_data_files a, v$backup b
  3  where a.file_id=b.file#;


TABLESPACE_NAME 		    FILE# STATUS		CHANGE# 	TIME	      CON_ID
------------------------------ ---------- ------------------ ---------- --------- ----------
SYSTEM					1 NOT ACTIVE		      0 		   				0
SYSAUX					2 NOT ACTIVE		      0 		   				0
UNDOTBS1				3 NOT ACTIVE		      0 		  				0
USERS					4 NOT ACTIVE		      0 		  				0
STATSPACK_TS			6 NOT ACTIVE		      0 		   				0
HOTTEST 				7 ACTIVE	       26676169 		08-DEC-20	    0

6 rows selected.

BEGIN BACKUP 진행한 HOTTEST만 ACTIVE 상태임을 확인

3. 물리적 파일 복사

[oracle@ORACLE19 test]$ pwd
/home/oracle/test
[oracle@ORACLE19 test]$ ls
hot  hottest.dbf
[oracle@ORACLE19 test]$ cp hottest.dbf hot/
[oracle@ORACLE19 test]$ cd hot
[oracle@ORACLE19 hot]$ ls
hottest.dbf

기존 /home/oracle/test에 생성해둔 hottest.dbf를 /home/oracle/test/hot으로 물리 복사

4. end backup 실행

SQL> alter tablespace hottest end backup;

Tablespace altered.

5. backup 상태 확인

SQL> select a.tablespace_name, b.*
  2  from dba_data_files a, v$backup b
  3  where a.file_id=b.file#;

TABLESPACE_NAME 		    FILE# STATUS		CHANGE# TIME	      CON_ID
------------------------------ ---------- ------------------ ---------- --------- ----------
SYSTEM					1 NOT ACTIVE		      	  0 		  		 0
SYSAUX					2 NOT ACTIVE		      	  0 		  		 0
UNDOTBS1				3 NOT ACTIVE		      	  0 		  		 0
USERS					4 NOT ACTIVE		      	  0 		   		 0
STATSPACK_TS				6 NOT ACTIVE		      0 		   		 0
HOTTEST 				7 NOT ACTIVE	       26676169 08-DEC-20	 	 0

6 rows selected.

END BACKUP 이후 상태 확인

6. 장애상황 발생(hottest.dbf 물리적 파일 삭제 상황)

[oracle@ORACLE19 test]$ ls
hot  hottest.dbf
[oracle@ORACLE19 test]$ rm -rf hottest.dbf 

HOTTEST.DBF를 삭제하여 DBF파일 유실상태를 만듬

7. 장애상황 인지(dbf유실에 따른 DB상태 확인)

SQL> shutdown immediate
ORA-03113: end-of-file on communication channel
SQL> shutdown abort
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL> 
SQL> 
SQL> select status from v$instance;
ERROR:
ORA-03114: not connected to ORACLE


SQL> startup
SP2-0642: SQL*Plus internal error state 2133, context 3114:0:0
Unsafe to proceed
ORA-03114: not connected to ORACLE

 HOTTEST.DBF가 유실되어 STARTUP되지 않음.

8. 복구진행 및 확인

[oracle@ORACLE19 test]$ ls
hot
[oracle@ORACLE19 test]$ cd hot
[oracle@ORACLE19 hot]$ ls
hottest.dbf
[oracle@ORACLE19 hot]$ cp hottest.dbf /home/oracle/test
[oracle@ORACLE19 hot]$ cd ..
[oracle@ORACLE19 test]$ ls
hot  hottest.db

SQL> startup
ORACLE instance started.

Total System Global Area 2147479656 bytes
Fixed Size		    8917096 bytes
Variable Size		  369098752 bytes
Database Buffers	  692060160 bytes
Redo Buffers		    3661824 bytes
In-Memory Area		 1073741824 bytes
Database mounted.
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/home/oracle/test/hottest.dbf'

SQL> recover datafile 7;
Media recovery complete.

SQL> alter database open;

Database altered.

백업해둔 HOTTEST.DBF를 기존 위치로 복사후 STARTUP 시도였고, media recovery가 필요한 상태임을 확인.

SQL> select * from hotuser.hottable;

	NO NAME
---------- ----------
	 1 park
	 2 kim
	 3 kang

복구후 HOTTEST에 저장된 테이블 조회하여 확인.