주 콘텐츠로 건너뛰기

검색

Welcome to Tibero GTS!

Tablespace Offline Normal인 상태에서 복구 후 Online 변경 방안

문서유형ㅣ기술정보

분야ㅣ백업/복구

적용제품버전ㅣ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.

 

 

 

관련 대상