문서유형ㅣ기술정보
분야ㅣ백업/복구
적용제품버전ㅣ7FS02PS
문서번호ㅣTBATI019
개요
Tablespace Offline Normal인 상태에서 복구 후 Online 변경 방안에 대한 시나리오입니다.
OFFLINE NORMAL
해당 테이블스페이스에 체크포인트를 수행한 후 오프라인 상태로 전환합니다. 다시 온라인 상태로 전환할 때는 별도의 복구 과정을 거치지 않습니다. NORMAL이나 IMMEDIATE 옵션을 지정하지 않으면 기본적으로 NORMAL로 동작합니다.
OFFLINE IMMEDIATE
NORMAL과 달리 체크포인트를 수행하지 않고 바로 오프라인 상태로 변경하기 때문에 온라인 상태로 전환하기 전에 반드시 미디어 복구를 해야 합니다.
방법
OFFLINE NORMAL 케이스 예제입니다.
1. tablespace 생성 및 offline 변경
SQL> create tablespace test01 datafile 'test_001.dtf' size 10M autoextend on next 10M maxsize unlimited;
Tablespace 'TEST01' created.
SQL> alter tablespace test01 offline;
Tablespace 'TEST01' altered.
SQL> select * from v$datafile;
FILE# CREATE_TSN
---------- ----------
CREATE_DATE
--------------------------------------------------------------------------------
TS# RFILE# BIGFILE STATUS ENABLED CKPT_TSN
---------- ---------- ------- ----------------- ---------- ----------
CKPT_DATE
--------------------------------------------------------------------------------
CREATE_BYTES
------------
NAME
--------------------------------------------------------------------------------
0 1
2024/11/29 17:04:58
0 0 NO ONLINE READ WRITE 44592
2024/11/29 17:07:31
104857600
/home/tibero/tibero7/database/tibero/system001.dtf
1 4
2024/11/29 17:04:59
1 1 NO ONLINE READ WRITE 44592
2024/11/29 17:07:31
209715200
/home/tibero/tibero7/database/tibero/undo001.dtf
FILE# CREATE_TSN
---------- ----------
CREATE_DATE
--------------------------------------------------------------------------------
TS# RFILE# BIGFILE STATUS ENABLED CKPT_TSN
---------- ---------- ------- ----------------- ---------- ----------
CKPT_DATE
--------------------------------------------------------------------------------
CREATE_BYTES
------------
NAME
--------------------------------------------------------------------------------
2 8
2024/11/29 17:04:59
3 2 NO ONLINE READ WRITE 44592
2024/11/29 17:07:31
104857600
/home/tibero/tibero7/database/tibero/usr001.dtf
3 20423
2024/11/29 17:05:38
4 3 NO ONLINE READ WRITE 44592
2024/11/29 17:07:31
10485760
/home/tibero/tibero7/database/tibero/syssub001.dtf
FILE# CREATE_TSN
---------- ----------
CREATE_DATE
--------------------------------------------------------------------------------
TS# RFILE# BIGFILE STATUS ENABLED CKPT_TSN
---------- ---------- ------- ----------------- ---------- ----------
CKPT_DATE
--------------------------------------------------------------------------------
CREATE_BYTES
------------
NAME
--------------------------------------------------------------------------------
4 43541
2024/11/29 17:07:16
5 4 NO OFFLINE DISABLED 44592
2024/11/29 17:07:31
10485760
/home/tibero/tibero7/database/tibero/test_001.dtf
5 rows selected.2. 백업 수행
SQL> alter database begin backup;
Database altered.
SQL> select * from v$backup;
FILE# STATUS CHANGE#
---------- ---------- ----------
TIME
--------------------------------------------------------------------------------
0 ACTIVE 44601
2024/11/29 17:07:49
1 ACTIVE 44603
2024/11/29 17:07:49
2 ACTIVE 44605
2024/11/29 17:07:49
3 ACTIVE 44607
2024/11/29 17:07:49
4 NOT ACTIVE 0
5 rows selected.
[tibero@tibero7 ~/tibero7/database/tibero]$ cp ./* ../backup/
SQL> alter database end backup;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.3. 장애 상황 발생
[tibero@tibero7 ~/tibero7/database/tibero]$ rm -f usr001.dtf
4. 복구 진행
[tibero@tibero7 ~/tibero7/database/backup]$ cp usr001.dtf ../tibero/ [tibero@tibero7 ~/tibero7/scripts]$ tbboot mount Listener port = 8629 Tibero 7 TmaxTibero Corporation Copyright (c) 2020-. All rights reserved. Tibero instance started up (MOUNT mode). [tibero@tibero7 ~/tibero7/scripts]$ tbsql sys/tibero tbSQL 7 TmaxTibero Corporation Copyright (c) 2020-. All rights reserved. Connected to Tibero. SQL> alter database recover automatic; Database altered. SQL> q Disconnected.
5-1. Tablespace offline → online 변경 및 확인
[tibero@tibero7 ~/tibero7/scripts]$ tbboot
Listener port = 8629
Tibero 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (NORMAL mode).
[tibero@tibero7 ~/tibero7/scripts]$ tbsql sys/tibero
tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
SQL> select * from v$datafile;
FILE# CREATE_TSN
---------- ----------
CREATE_DATE
--------------------------------------------------------------------------------
TS# RFILE# BIGFILE STATUS ENABLED CKPT_TSN
---------- ---------- ------- ----------------- ---------- ----------
CKPT_DATE
--------------------------------------------------------------------------------
CREATE_BYTES
------------
NAME
--------------------------------------------------------------------------------
0 1
2024/11/29 17:04:58
0 0 NO ONLINE READ WRITE 44663
2024/11/29 17:10:18
104857600
/home/tibero/tibero7/database/tibero/system001.dtf
1 4
2024/11/29 17:04:59
1 1 NO ONLINE READ WRITE 44663
2024/11/29 17:10:18
209715200
/home/tibero/tibero7/database/tibero/undo001.dtf
FILE# CREATE_TSN
---------- ----------
CREATE_DATE
--------------------------------------------------------------------------------
TS# RFILE# BIGFILE STATUS ENABLED CKPT_TSN
---------- ---------- ------- ----------------- ---------- ----------
CKPT_DATE
--------------------------------------------------------------------------------
CREATE_BYTES
------------
NAME
--------------------------------------------------------------------------------
2 8
2024/11/29 17:04:59
3 2 NO ONLINE READ WRITE 44663
2024/11/29 17:10:18
104857600
/home/tibero/tibero7/database/tibero/usr001.dtf
3 20423
2024/11/29 17:05:38
4 3 NO ONLINE READ WRITE 44663
2024/11/29 17:10:18
10485760
/home/tibero/tibero7/database/tibero/syssub001.dtf
FILE# CREATE_TSN
---------- ----------
CREATE_DATE
--------------------------------------------------------------------------------
TS# RFILE# BIGFILE STATUS ENABLED CKPT_TSN
---------- ---------- ------- ----------------- ---------- ----------
CKPT_DATE
--------------------------------------------------------------------------------
CREATE_BYTES
------------
NAME
--------------------------------------------------------------------------------
4 43541
2024/11/29 17:07:16
5 4 NO OFFLINE DISABLED 44592
2024/11/29 17:07:31
10485760
/home/tibero/tibero7/database/tibero/test_001.dtf
5 rows selected.5-2. offline이었던 tablespace를 online으로 변경
SQL> alter tablespace test01 online;
Tablespace 'TEST01' altered.
SQL> select * from v$datafile;
FILE# CREATE_TSN
---------- ----------
CREATE_DATE
--------------------------------------------------------------------------------
TS# RFILE# BIGFILE STATUS ENABLED CKPT_TSN
---------- ---------- ------- ----------------- ---------- ----------
CKPT_DATE
--------------------------------------------------------------------------------
CREATE_BYTES
------------
NAME
--------------------------------------------------------------------------------
0 1
2024/11/29 17:04:58
0 0 NO ONLINE READ WRITE 44663
2024/11/29 17:10:18
104857600
/home/tibero/tibero7/database/tibero/system001.dtf
1 4
2024/11/29 17:04:59
1 1 NO ONLINE READ WRITE 44663
2024/11/29 17:10:18
209715200
/home/tibero/tibero7/database/tibero/undo001.dtf
FILE# CREATE_TSN
---------- ----------
CREATE_DATE
--------------------------------------------------------------------------------
TS# RFILE# BIGFILE STATUS ENABLED CKPT_TSN
---------- ---------- ------- ----------------- ---------- ----------
CKPT_DATE
--------------------------------------------------------------------------------
CREATE_BYTES
------------
NAME
--------------------------------------------------------------------------------
2 8
2024/11/29 17:04:59
3 2 NO ONLINE READ WRITE 44663
2024/11/29 17:10:18
104857600
/home/tibero/tibero7/database/tibero/usr001.dtf
3 20423
2024/11/29 17:05:38
4 3 NO ONLINE READ WRITE 44663
2024/11/29 17:10:18
10485760
/home/tibero/tibero7/database/tibero/syssub001.dtf
FILE# CREATE_TSN
---------- ----------
CREATE_DATE
--------------------------------------------------------------------------------
TS# RFILE# BIGFILE STATUS ENABLED CKPT_TSN
---------- ---------- ------- ----------------- ---------- ----------
CKPT_DATE
--------------------------------------------------------------------------------
CREATE_BYTES
------------
NAME
--------------------------------------------------------------------------------
4 43541
2024/11/29 17:07:16
5 4 NO ONLINE READ WRITE 44592 #-- online으로 변경됨
2024/11/29 17:07:31
10485760
/home/tibero/tibero7/database/tibero/test_001.dtf
5 rows selected.5-3. checkpoint 수행하여 TSN 확인
SQL> alter system checkpoint;
System altered.
SQL> select * from v$datafile;
FILE# CREATE_TSN
---------- ----------
CREATE_DATE
--------------------------------------------------------------------------------
TS# RFILE# BIGFILE STATUS ENABLED CKPT_TSN
---------- ---------- ------- ----------------- ---------- ----------
CKPT_DATE
--------------------------------------------------------------------------------
CREATE_BYTES
------------
NAME
--------------------------------------------------------------------------------
0 1
2024/11/29 17:04:58
0 0 NO ONLINE READ WRITE 44802
2024/11/29 17:13:55
104857600
/home/tibero/tibero7/database/tibero/system001.dtf
1 4
2024/11/29 17:04:59
1 1 NO ONLINE READ WRITE 44802
2024/11/29 17:13:55
209715200
/home/tibero/tibero7/database/tibero/undo001.dtf
FILE# CREATE_TSN
---------- ----------
CREATE_DATE
--------------------------------------------------------------------------------
TS# RFILE# BIGFILE STATUS ENABLED CKPT_TSN
---------- ---------- ------- ----------------- ---------- ----------
CKPT_DATE
--------------------------------------------------------------------------------
CREATE_BYTES
------------
NAME
--------------------------------------------------------------------------------
2 8
2024/11/29 17:04:59
3 2 NO ONLINE READ WRITE 44802
2024/11/29 17:13:55
104857600
/home/tibero/tibero7/database/tibero/usr001.dtf
3 20423
2024/11/29 17:05:38
4 3 NO ONLINE READ WRITE 44802
2024/11/29 17:13:55
10485760
/home/tibero/tibero7/database/tibero/syssub001.dtf
FILE# CREATE_TSN
---------- ----------
CREATE_DATE
--------------------------------------------------------------------------------
TS# RFILE# BIGFILE STATUS ENABLED CKPT_TSN
---------- ---------- ------- ----------------- ---------- ----------
CKPT_DATE
--------------------------------------------------------------------------------
CREATE_BYTES
------------
NAME
--------------------------------------------------------------------------------
4 43541
2024/11/29 17:07:16
5 4 NO ONLINE READ WRITE 44802
2024/11/29 17:13:55
10485760
/home/tibero/tibero7/database/tibero/test_001.dtf
5 rows selected.