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

문서 유형

기술 정보

분야

백업/복구

키워드

tablespace

적용 제품 버전

7FS02PS

개요

Tablespace offline immediate 상태에서 복구 후 Online 변경 방안에 대한 시나리오입니다.

OFFLINE NORMAL

해당 테이블스페이스에 체크포인트를 수행한 후 오프라인 상태로 변경합니다. 다시 온라인 상태로 전환할 때 별도의 복구 과정을 거치지 않습니다. NORMAL이나 IMMEDIATE 옵션을 지정하지 않으면 기본적으로 NORMAL로 동작합니다.

OFFLINE IMMEDIATE

NORMAL과 달리 체크포인트를 수행하지 않고 바로 오프라인 상태로 변경하기 때문에, 온라인 상태로 다시 전환하기 전에 반드시 미디어 복구를 해야 합니다.

방법

tablespace offline immediate 상태인 경우 automatic tablespace 명령어를 이용합니다.

1. tablespace 생성 및 offline immediate 변경

SQL>  create tablespace test01 datafile 'test_001.dtf' size 10M autoextend on next 10M maxsize unlimited;

Tablespace 'TEST01' created.


SQL> alter tablespace test01 offline immedate;


Tablespace 'TEST01' altered.

SQL&gt; 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      45957
2024/11/29 17:18:31
  104857600
/home/tibero/tibero7/database/tibero/system001.dtf

        1          4
2024/11/29 17:04:59
        1          1 NO      ONLINE            READ WRITE      45957
2024/11/29 17:18: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      45957
2024/11/29 17:18:31
  104857600
/home/tibero/tibero7/database/tibero/usr001.dtf

        3      20423
2024/11/29 17:05:38
        4          3 NO      ONLINE            READ WRITE      45957
2024/11/29 17:18: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 IMMEDIATE DISABLED        44802
2024/11/29 17:13:55
   10485760
/home/tibero/tibero7/database/tibero/test_001.dtf
SQL
복사

2. 백업 수행

SQL> alter database begin backup;

Database altered.

[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.
SQL
복사

3. 장애 상황 발생

[tibero@tibero7 ~/tibero7/database/tibero]$ rm -f usr001.dtf
SQL
복사

Case 1) automatic 수행 후 tbdown, tbboot → offline immediate 플래그 유지

# 복구 진행
[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.
SQL
복사

Tablespace offline immediate → 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      45957
2024/11/29 17:18:31
  104857600
/home/tibero/tibero7/database/tibero/system001.dtf

        1          4
2024/11/29 17:04:59
        1          1 NO      ONLINE            READ WRITE      45957
2024/11/29 17:18: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      45957
2024/11/29 17:18:31
  104857600
/home/tibero/tibero7/database/tibero/usr001.dtf

        3      20423
2024/11/29 17:05:38
        4          3 NO      ONLINE            READ WRITE      45957
2024/11/29 17:18: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 IMMEDIATE DISABLED        44802        #--offline immediate에서 변경사항 없음
2024/11/29 17:13:55
   10485760
/home/tibero/tibero7/database/tibero/test_001.dtf

5 rows selected.

SQL> alter tablespace test01 online;
TBR-1024: Database needs media recovery: media recovery required(/home/tibero/tibero7/database/tibero/test_001.dtf).


SQL> q
Disconnected.
SQL
복사

Case 2) automatic 수행 후 alter database open → offline immediate 플래그 유지

#. 복구 진행
[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.

SQL> alter database open;

Database altered.
SQL
복사

Tablespace offline immediate → online 변경을 확인할 수 있습니다.

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      48723
2024/11/29 17:45:08
  104857600
/home/tibero/tibero7/database/tibero/system001.dtf

        1          4
2024/11/29 17:04:59
        1          1 NO      ONLINE            READ WRITE      48723
2024/11/29 17:45:08
  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      48723
2024/11/29 17:45:08
  104857600
/home/tibero/tibero7/database/tibero/usr001.dtf

        3      20423
2024/11/29 17:05:38
        4          3 NO      ONLINE            READ WRITE      48723
2024/11/29 17:45:08
   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 IMMEDIATE DISABLED        47557        #--offline immediate에서 변경사항 없음
2024/11/29 17:40:07
   10485760
/home/tibero/tibero7/database/tibero/test_001.dtf


5 rows selected.
SQL
복사

Case 3) automatic tablespace 수행 후 alter database open → offline immediate 플래그 변경

#. 복구 진행
[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 tablespace test01;

Database altered.

SQL> alter database open;

Database altered.
SQL
복사

Tablespace offline immediate → online 변경을 확인할 수 있습니다.

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      46005
2024/11/29 17:19:27
  104857600
/home/tibero/tibero7/database/tibero/system001.dtf

        1          4
2024/11/29 17:04:59
        1          1 NO      ONLINE            READ WRITE      46005
2024/11/29 17:19:27
  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      46005
2024/11/29 17:19:27
  104857600
/home/tibero/tibero7/database/tibero/usr001.dtf

        3      20423
2024/11/29 17:05:38
        4          3 NO      ONLINE            READ WRITE      46005
2024/11/29 17:19:27
   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        46005
2024/11/29 17:20:35
   10485760
/home/tibero/tibero7/database/tibero/test_001.dtf

→ automatic tablespace 후 offline immediate 상태가 offline으로 변경됨


5 rows selected.


SQL> alter tablespace test01 online;


Tablespace 'TEST01' altered.
SQL
복사

Case 4) automatic tablespace 수행 후 tbdown, tbboot → offline immediate 플래그 변경

#. 복구 진행
[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 tablespace test01;

Database altered.
SQL
복사

Tablespace offline immediate → online 변경을 확인할 수 있습니다.

[tibero@tibero7 ~/tibero7/scripts]$ tbdown

Tibero instance terminated (NORMAL mode).


[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      47558
2024/11/29 17:39:56
  104857600
/home/tibero/tibero7/database/tibero/system001.dtf

        1          4
2024/11/29 17:04:59
        1          1 NO      ONLINE            READ WRITE      47558
2024/11/29 17:39:56
  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      47558
2024/11/29 17:39:56
  104857600
/home/tibero/tibero7/database/tibero/usr001.dtf

        3      20423
2024/11/29 17:05:38
        4          3 NO      ONLINE            READ WRITE      47558
2024/11/29 17:39:56
   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        47557
2024/11/29 17:40:07
   10485760
/home/tibero/tibero7/database/tibero/test_001.dtf

→ automatic tablespace 후 offline immediate 상태가 offline으로 변경됨


5 rows selected.


SQL> alter tablespace test01 online;


Tablespace 'TEST01' altered.
SQL
복사