AWS s3를 이용한 백업본 보관 방법

SQL>tbsql sys/tibero SQL>CREATE TABLESPACE TS_TEST DATAFILE 'test001.dtf' SIZE 16M AUTOEXTEND ON NEXT 16M MAXSIZE 1G, 'test002.dtf' SIZE 16M AUTOEXTEND ON NEXT 16M MAXSIZE 1G EXTENT MANAGEMENT LOCAL AUTOALLOCATE;Tablespace 'TS_TEST' created. SQL>CREATE TABLESPACE TS_TEST_IDX DATAFILE 'test_idx_001.dtf' SIZE 8M AUTOEXTEND ON NEXT 8M MAXSIZE 1G EXTENT MANAGEMENT LOCAL AUTOALLOCATE; Tablespace 'TS_TEST_IDX' created. SQL>CREATE USER TEST IDENTIFIED BY TEST DEFAULT TABLESPACE TS_TEST; User 'TEST' created. SQL>GRANT DBA TO TEST; Granted. SQL>CONN TEST/TEST Connected to Tibero. SQL>CREATE TABLE TEST.T1 (ID NUMBER, ANAME VARCHAR2(32), BNAME VARCHAR2(32), ID2 NUMBER) TABLESPACE TS_TEST; Table 'TEST.T1' created. SQL>CREATE INDEX IDX_T1 ON T1(ID, ANAME) TABLESPACE TS_TEST_IDX; Index 'IDX_T1' created.

SQL>SELECT COUNT(*) FROM TEST.T1; COUNT(*) ---------- 0 1 row selected. SQL>SELECT TABLESPACE_NAME FROM DBA_TABLESPACES; TABLESPACE_NAME -------------------------------------------------------------------------------- SYSTEM UNDO0 TEMP USR UNDO1 SYSSUB 6 rows selected. SQL>SELECT FILE_NAME FROM DBA_DATA_FILES; FILE_NAME -------------------------------------------------------------------------------- /dev/raw/raw51 /dev/raw/raw53 /dev/raw/raw55 /dev/raw/raw54 /dev/raw/raw56 5 rows selected.

SQL>ALTER DATABASE BEGIN BACKUP; Database altered. SQL>SELECT * FROM V$BACKUP; FILE# STATUS CHANGE# TIME ---------- ---------- ---------- -------------------------------- 0 ACTIVE 247475 2017/05/31 1 ACTIVE 247475 2017/05/31 2 ACTIVE 247475 2017/05/31 3 ACTIVE 247475 2017/05/31 4 ACTIVE 247475 2017/05/31 5 rows selected. SQL>exit Disconnected.

$ls -al /dev/raw $mkdir -p $TB_HOME/${TB_SID}_hot $dd if=/dev/raw/raw51 of=$TB_HOME/${TB_SID}_hot/raw51 bs=8M $dd if=/dev/raw/raw53 of=$TB_HOME/${TB_SID}_hot/raw53 bs=8M $dd if=/dev/raw/raw54 of=$TB_HOME/${TB_SID}_hot/raw54 bs=8M $dd if=/dev/raw/raw55 of=$TB_HOME/${TB_SID}_hot/raw55 bs=8M $dd if=/dev/raw/raw56 of=$TB_HOME/${TB_SID}_hot/raw56 bs=8M $ls -al $TB_HOME/${TB_SID}_hot total 52428800 drwxr-xr-x 2 tta01 dba 66 May 31 17:19 . drwxr-xr-x 11 tta01 dba 120 May 31 16:46 .. -rw-r--r-- 1 tta01 dba 10737418240 May 31 16:50 raw51 -rw-r--r-- 1 tta01 dba 10737418240 May 31 17:05 raw53 -rw-r--r-- 1 tta01 dba 10737418240 May 31 17:08 raw54 -rw-r--r-- 1 tta01 dba 10737418240 May 31 17:12 raw55 -rw-r--r-- 1 tta01 dba 10737418240 May 31 17:22 raw56

$aws configure AWS Access Key ID [None]: xxxxxxxxxxxxxxxxxxxx #IAM 사용자 access key 설정 값 입력 AWS Secret Access Key [None]: xxxxxxxxxxxxxxxxxxxxxxxxx #access key 생성 시 확인 및 다운로드 가능 Default region name [None]: ap-northeast-2 Default output format [None]: $ aws s3 ls #bucket list 확인 $ls -al $TB_HOME/${TB_SID}_hot total 52428800 drwxr-xr-x 2 tta01 dba 66 May 31 17:19 . drwxr-xr-x 11 tta01 dba 120 May 31 16:46 .. -rw-r--r-- 1 tta01 dba 10737418240 May 31 16:50 raw51 -rw-r--r-- 1 tta01 dba 10737418240 May 31 17:05 raw53 -rw-r--r-- 1 tta01 dba 10737418240 May 31 17:08 raw54 -rw-r--r-- 1 tta01 dba 10737418240 May 31 17:12 raw55 -rw-r--r-- 1 tta01 dba 10737418240 May 31 17:22 raw56 $aws s3 cp raw51 s3://tiberobackup2/raw51_20200618 upload: ./raw51 to s3://tiberobackup2/raw51_20200618 #s3://버킷명/오브젝트명 $aws s3 cp raw53 s3://tiberobackup2/raw53_20200618 upload: ./raw53 to s3://tiberobackup2/raw53_20200618 $aws s3 cp raw54 s3://tiberobackup2/raw54_20200618 upload: ./raw54 to s3://tiberobackup2/raw54_20200618 $aws s3 cp raw55 s3://tiberobackup2/raw55_20200618 upload: ./raw55 to s3://tiberobackup2/raw55_20200618 $aws s3 cp raw56 s3://tiberobackup2/raw56_20200618 upload: ./raw56 to s3://tiberobackup2/raw56_20200618 또는 $ aws s3 sync ./ s3://tiberobackup2 # 현재 디렉터리와 bucket 동기화 하여 현재 디렉터리 내에 있는 파일 모두 업로드 --참고-- $ aws s3 cp ./ s3://[bucketname] --recursive #--recursive : 지정한 디렉토리의 모든 파일을 선택

SQL>tbsql sys/tibero SQL>ALTER DATABASE END BACKUP; Database altered. SQL>SELECT * FROM V$BACKUP; FILE# STATUS CHANGE# TIME ---------- ---------- ---------- -------------------------------- 0 NOT ACTIVE 247475 2017/05/31 1 NOT ACTIVE 247475 2017/05/31 2 NOT ACTIVE 247475 2017/05/31 3 NOT ACTIVE 247475 2017/05/31 4 NOT ACTIVE 247475 2017/05/31 5 rows selected. SQL>ALTER SYSTEM SWITCH LOGFILE; System altered.

SQL>INSERT INTO TEST.T1 SELECT ROWNUM, 'A'||TO_CHAR(ROWNUM), 'B'||TO_CHAR(ROWNUM), ROUND(ROWNUM/50) FROM DUAL CONNECT BY ROWNUM<=50000; 50000 rows inserted. SQL>COMMIT; Commit completed.

SQL>SELECT COUNT(*) FROM TEST.T1; COUNT(*) ---------- 50000 1 row selected. SQL>exit Disconnected.

$tbdown Tibero instance terminated (NORMAL mode).

$tbdown Tibero instance terminated (NORMAL mode). $dd if=/dev/zero of=/dev/raw/raw51 count=1 $dd if=/dev/zero of=/dev/raw/raw53 count=1 $dd if=/dev/zero of=/dev/raw/raw54 count=1 $dd if=/dev/zero of=/dev/raw/raw55 count=1 $dd if=/dev/zero of=/dev/raw/raw56 count=1

$tbboot Change core dump dir to /data/tta01/tibero6/bin/prof. Listener port = 8629 ******************************************************** * Critical Warning : Raise svmode failed. The reason is * TBR-1024 : Database needs media recovery: wrong database file(/dev/raw/raw51). * Current server mode is MOUNT. ******************************************************** Tibero 6 TmaxData Corporation Copyright (c) 2008-. All rights reserved. Tibero instance started suspended at MOUNT mode.

$tbdown Tibero instance terminated (NORMAL mode). S3에서 백업본 다운로드 $aws s3 cp s3://tiberobackup2/raw51_20200618 raw51_bak download: s3://tiberobackup2/raw51_20200618 to ./raw51_bak $aws s3 cp s3://tiberobackup2/raw53_20200618 raw53_bak download: s3://tiberobackup2/raw53_20200618 to ./raw53_bak $aws s3 cp s3://tiberobackup2/raw54_20200618 raw54_bak download: s3://tiberobackup2/raw54_20200618 to ./raw54_bak $aws s3 cp s3://tiberobackup2/raw55_20200618 raw55_bak download: s3://tiberobackup2/raw55_20200618 to ./raw55_bak $aws s3 cp s3://tiberobackup2/raw56_20200618 raw56_bak download: s3://tiberobackup2/raw56_20200618 to ./raw56_bak 또는 $ aws s3 sync s3://tiberobackup2 ./bak #bucket에 있는 백업본 전체를 bak 경로에 다운로드 진행 --참고-- $ aws s3 cp s3://[bucketname] [디렉터리 경로] --recursive --include "[filtering 하고자하는 string]" #--recursive : 지정한 버킷의 모든 파일 선택 #--include : 해당 string이 포함된 객체 name을 filtering download: s3://tiberobackup2/etcfile to bak/etcfile download: s3://tiberobackup2/raw55 to bak/raw56 download: s3://tiberobackup2/raw55 to bak/raw55 download: s3://tiberobackup2/raw54 to bak/raw54 download: s3://tiberobackup2/raw53 to bak/raw53 download: s3://tiberobackup2/raw51 to bak/raw51 $ls -al $TB_HOME/${TB_SID}_hot/ total 52428800 drwxr-xr-x 2 tta01 dba 66 May 31 17:19 . drwxr-xr-x 11 tta01 dba 120 May 31 16:46 .. -rw-r--r-- 1 tta01 dba 10737418240 May 31 16:50 raw51_bak -rw-r--r-- 1 tta01 dba 10737418240 May 31 17:05 raw53_bak -rw-r--r-- 1 tta01 dba 10737418240 May 31 17:08 raw54_bak -rw-r--r-- 1 tta01 dba 10737418240 May 31 17:12 raw55_bak -rw-r--r-- 1 tta01 dba 10737418240 May 31 17:22 raw56_bak $dd if=$TB_HOME/${TB_SID}_hot/raw51_bak of=/dev/raw/raw51 bs=8M $dd if=$TB_HOME/${TB_SID}_hot/raw53_bak of=/dev/raw/raw53 bs=8M $dd if=$TB_HOME/${TB_SID}_hot/raw54_bak of=/dev/raw/raw54 bs=8M $dd if=$TB_HOME/${TB_SID}_hot/raw55_bak of=/dev/raw/raw55 bs=8M $dd if=$TB_HOME/${TB_SID}_hot/raw56_bak of=/dev/raw/raw56 bs=8M

--NODE2 $scp /data/tta02/tbarch/*.arc tta01@192.168.53.186:/data/tta01/tbarch tta01@192.168.53.186's password: log-t1-r0-s1.arc 100% 82MB 10.3MB/s 00:08

$tbboot mount Change core dump dir to /home/tibero6/tibero6/bin/prof. Listener port = 8629 Tibero 6 TmaxData Corporation Copyright (c) 2008-. All rights reserved. Tibero instance started up (MOUNT mode). SQL>tbsql sys/tibero tbSQL 6 TmaxData Corporation Copyright (c) 2008-. All rights reserved. Connected to Tibero. SQL>ALTER DATABASE RECOVER AUTOMATIC; Database altered.

--NODE1 $tbdown Tibero instance terminated (NORMAL mode). $tbboot Change core dump dir to /home/tibero6/tibero6/bin/prof. Listener port = 8629 Tibero 6 TmaxData Corporation Copyright (c) 2008-. All rights reserved. Tibero instance started up (NORMAL mode). --NODE2 $tbboot Change core dump dir to /home/tibero6/tibero6/bin/prof. Listener port = 8629 Tibero 6 TmaxData Corporation Copyright (c) 2008-. All rights reserved. Tibero instance started up (NORMAL mode).

SQL>tbsql sys/tibero tbSQL 6 TmaxData Corporation Copyright (c) 2008-. All rights reserved. Connected to Tibero. SQL>SELECT COUNT(*) FROM TEST.T1; COUNT(*) ---------- 50000 1 row selected. SQL>exit Disconnected.