Document Type | Technical Information
Category | Backup/Recovery
Applicable Product Version | 6FS07
Document Number | TBATI011
Overview
This document explains how to store backups using AWS S3.
AWS S3 is an object storage service, and you can upload and download DB backups to S3 using AWS CLI.
Method
This is a scenario for recovery using backups stored on S3 via AWS CLI.
[Hot Backup Scenario Using AWS S3]
1. Create Test Data
- Create tablespaces
- Create test user and tables
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.
2. Preliminary Checks
- Check the number of rows in the table
- Check tablespaces
- Check data files
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.
3. Perform and Verify Begin Backup
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.
4. Perform Hot Backup (Data File Copy)
$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 raw56Upload to AWS S3
$aws configure
AWS Access Key ID [None]: xxxxxxxxxxxxxxxxxxxx
# Enter IAM user access key
AWS Secret Access Key [None]: xxxxxxxxxxxxxxxxxxxxxxxxx
# Can be checked and downloaded when creating access key
Default region name [None]: ap-northeast-2
Default output format [None]:
$ aws s3 ls
# Check 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://bucket-name/object-name
$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
Or
$ aws s3 sync ./ s3://tiberobackup2
# Synchronize current directory with bucket to upload all files in the current directory
--Note--
$ aws s3 cp ./ s3://[bucketname] --recursive
#--recursive : Select all files in the specified directory
5. Perform End Backup and Log Switch
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.
6. Insert Data
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.
7. Query Data
SQL>SELECT COUNT(*) FROM TEST.T1;
COUNT(*)
----------
50000
1 row selected.
SQL>exit
Disconnected.
8. Shut Down Tibero and Delete All Data Files
Node 1
$tbdown Tibero instance terminated (NORMAL mode).
Node 2
$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
9. Start Tibero, Check Mount Mode and Failure Status
$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.
10. Shut Down Tibero and Restore Hot Backup
$tbdown
Tibero instance terminated (NORMAL mode).
Download backup from 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
Or
$ aws s3 sync s3://tiberobackup2 ./bak
# Download all backups in the bucket to the bak directory
--Note--
$ aws s3 cp s3://[bucketname] [directory path] --recursive --include "[string to filter]"
#--recursive : Select all files in the specified bucket
#--include : Filter objects whose names include the specified string
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
11. Copy NODE2's Archivelog Files to NODE1
--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 SQL
12. Start Tibero in Mount Mode and Perform Recovery
$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.
13. Shut Down Tibero and Start Tibero
--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).
14. Check Table Row Count
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.