Document Type | Technical Information
Category | Backup/Recovery
Applicable Product Version | 7FS02PS
Document Number | TBATI019
Overview
This is a scenario for changing a tablespace from Offline Normal state to Online after recovery.
OFFLINE NORMAL
The tablespace is switched to offline after performing a checkpoint. When switching back to online, no separate recovery process is required. If neither NORMAL nor IMMEDIATE options are specified, the default behavior is NORMAL.
OFFLINE IMMEDIATE
Unlike NORMAL, the tablespace is switched to offline immediately without performing a checkpoint. Therefore, media recovery must be done before switching back to online.
Method
This is an example case for OFFLINE NORMAL.
1. Create tablespace and change to 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. Perform backup
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. Failure occurs
[tibero@tibero7 ~/tibero7/database/tibero]$ rm -f usr001.dtf
4. Proceed with recovery
[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. Change tablespace from offline to online and verify
[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. Change the tablespace that was offline to 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 #-- changed to online
2024/11/29 17:07:31
10485760
/home/tibero/tibero7/database/tibero/test_001.dtf
5 rows selected.5-3. Perform checkpoint and check 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.