Skip to main content

Search

Welcome to Tibero GTS!

Method to Change Tablespace from Offline Immediate to Online After Recovery

Document Type | Technical Information

Category | Backup/Recovery

Applicable Product Version | 7FS02PS

Document Number | TBATI018

 

Overview

This is a scenario for changing a tablespace from offline immediate state to online after recovery.
 

OFFLINE NORMAL

The tablespace is changed to offline state after performing a checkpoint on it. When switching back to online state, no separate recovery process is required. If neither NORMAL nor IMMEDIATE options are specified, NORMAL is the default operation mode.
 

OFFLINE IMMEDIATE

Unlike NORMAL, the tablespace is changed to offline state immediately without performing a checkpoint, so media recovery must be done before switching back to online state.
 
 

Method

If the tablespace is in offline immediate state, use the automatic tablespace command.
 
1. Create tablespace and change to 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> 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
 
2. Perform backup
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.

3. Failure occurrence
[tibero@tibero7 ~/tibero7/database/tibero]$ rm -f usr001.dtf

Case 1) After automatic recovery, tbdown and tbboot โ†’ offline immediate flag remains
# Recovery process
[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.

You can confirm the tablespace changed from offline immediate to 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        #--No change from 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.

Case 2) After automatic recovery, alter database open โ†’ offline immediate flag remains
#. Recovery process
[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.

You can confirm the tablespace changed from offline immediate to 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        #--No change from offline immediate
2024/11/29 17:40:07
   10485760
/home/tibero/tibero7/database/tibero/test_001.dtf


5 rows selected.

Case 3) After automatic tablespace recovery, alter database open โ†’ offline immediate flag changes
#. Recovery process
[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.

You can confirm the tablespace changed from offline immediate to 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

โ†’ After automatic tablespace recovery, offline immediate state changes to offline


5 rows selected.


SQL> alter tablespace test01 online;


Tablespace 'TEST01' altered.

Case 4) After automatic tablespace recovery, tbdown and tbboot โ†’ offline immediate flag changes
#. Recovery process
[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.

You can confirm the tablespace changed from offline immediate to 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        47558
2024/11/29 17:40:07
   10485760
/home/tibero/tibero7/database/tibero/test_001.dtf

โ†’ After automatic tablespace recovery, offline immediate state changes to offline


5 rows selected.


SQL> alter tablespace test01 online;


Tablespace 'TEST01' altered.

 

 
 

Related to