Document Type | Technical Information

Category | Administration

Applicable Product Version | Tibero 7.2.3

Document Number | TADTI115

Overview

This document guides three methods for changing the name or path of data files.

Test Environment: Tibero 7.2.3

Methods

Case 1. Changing Datafile Name/Path While Tablespace is Offline

Method to change the name or path of a datafile without restarting the DB.
Change the availability of the tablespace to offline for the target datafile, then execute the rename datafile command to change the datafile's name or path.
At this time, the tablespace must be non-SYSTEM to be changed to offline status.
Since the datafile information has changed, a control file backup is required after the operation.

Note

For methods of changing datafile names and paths while the tablespace is offline applicable to versions prior to Tibero 7,
please refer to the link below.

How to Change Datafile Name and Path While Tablespace is Offline

Procedure

1. Check datafile and tablespace

SQL> set linesize 200
SQL> col file_name for a40
SQL> col tablespace_name for a20
SQL> select file_name, tablespace_name from dba_datafiles where file_name like '%ts%.dtf';

FILE_NAME                                TABLESPACE_NAME
---------------------------------------- --------------------
/tibero/tibero7/database/tac/ts1.dtf     TS1
/tibero/tibero7/database/tac/ts2.dtf     TS1
/tibero/tibero7/database/tac/ts3.dtf     TS1

3 rows selected.

2. Change target tablespace to offline

SQL> alter tablespace TS1 offline;

Tablespace 'TS1' altered.



SQL> select tablespace_name, status from dba_tablespaces where tablespace_name = 'TS1';

TABLESPACE_NAME      STATUS
-------------------- ---------
TS1                  OFFLINE

3. Change target datafile path

[tibero@devdb tac]$ ls ts1.dtf
ts1.dtf
[tibero@devdb tac]$ cp ts1.dtf /tibero/tac/datafile/ts1.dtf
[tibero@devdb tac]$ cd /tibero/tac/datafile
[tibero@devdb datafile]$ ls ts1.dtf
ts1.dtf

4. rename datafile command

SQL> alter tablespace TS1 rename datafile '/tibero/tibero7/database/tac/ts1.dtf' 
     TO '/tibero/tac/datafile/ts1.dtf';

Tablespace 'TS1' altered.

5. Change target tablespace to online

SQL> alter tablespace ts1 online;

Tablespace 'TS1' altered.

6. Verify operation and backup controlfile

SQL> select file_name, tablespace_name from dba_datafiles where tablespace_name='TS1';

FILE_NAME                                TABLESPACE_NAME
---------------------------------------- --------------------
/tibero/tac/datafile/ts1.dtf             TS1
/tibero/tibero7/database/tac/ts2.dtf     TS1
/tibero/tibero7/database/tac/ts3.dtf     TS1

3 rows selected.




SQL> alter database backup controlfile to trace as '/tibero/tac/control_back.sql';

Database altered.



[tibero@devdb tac]$ ls control_back.sql
control_back.sql

Case 2. Changing Datafile Name/Path by Recreating Controlfile

This method uses and modifies the backed-up controlfile creation script to change the datafile name and path. If the datafile name or path has been changed at the OS level, this method allows recovery of the database using the controlfile backup.
DB restart and media recovery (ALTER DATABASE RECOVER -) are required.
(DB operates in ARCHIVELOG MODE)

Note

For methods of changing datafile names and paths by recreating the controlfile applicable to versions prior to Tibero 7,
please refer to the link below.

How to Change Datafile Name and Path by Recreating Controlfile

Procedure

1. Check datafile

SQL> set linesize 200
SQL> col file_name for a50
SQL> col tablespace_name for a20
SQL> select file_name, tablespace_name from dba_datafiles where file_name like '%ts_%';


FILE_NAME                                          TABLESPACE_NAME
-------------------------------------------------- --------------------
/tibero/tac/datafile/ts1.dtf                       TS1
/tibero/tibero7/database/tac/ts2.dtf               TS1
/tibero/tibero7/database/tac/ts3.dtf               TS1

3 rows selected.

2. Backup controlfile

SQL> alter database backup controlfile to trace as '/tibero/ctl_bak.sql' reuse noresetlogs;

Database altered.

[tibero@devdb ~]$ pwd
/tibero
[tibero@devdb ~]$ ls ctl_bak.sql
ctl_bak.sql

3. DB down

[tibero@devdb ~]$ tbdown immediate

Tibero instance terminated (IMMEDIATE mode).

[tibero@devdb ~]$ ps -ef|grep tbsvr
tibero   3810401 3789549  0 14:14 pts/1    00:00:00 grep --color=auto tbsvr

4. Change target datafile path

[tibero@devdb tac]$ pwd
/tibero/tibero7/database/tac
[tibero@devdb tac]$ ls ts2.dtf
ts2.dtf
[tibero@devdb tac]$ cp ts2.dtf /tibero/tac/datafile/ts2.dtf
[tibero@devdb tac]$ ls /tibero/tac/datafile/ts2.dtf
/tibero/tac/datafile/ts2.dtf

5. Modify backed-up controlfile creation script

[tibero@devdb ~]$ vi ctl_bak.sql

CREATE CONTROLFILE REUSE DATABASE "tac"
LOGFILE
-- Thread #0
GROUP 0 (
        '/tibero/tibero7/database/tac1/redo001.log',
        '/tibero/tibero7/database/tac1/redo002.log'
          ) SIZE 100M,
GROUP 1 (
        '/tibero/tibero7/database/tac1/redo011.log',
        '/tibero/tibero7/database/tac1/redo012.log'
          ) SIZE 100M
NORESETLOGS
DATAFILE
        '/tibero/tibero7/database/tac1/system001.dtf',
        '/tibero/tibero7/database/tac1/undo001.dtf',
        '/tibero/tibero7/database/tac1/usr001.dtf',
        '/tibero/tibero7/database/tac1/syssub001.dtf',
        '/tibero/tac/datafile/ts1.dtf',
--      '/tibero/tibero7/database/tac/ts2.dtf',  ### Old path
        '/tibero/tac/datafile/ts2.dtf',  ### New path
        '/tibero/tibero7/database/tac/ts3.dtf'
ARCHIVELOG
MAXLOGFILES 100
MAXFBLOGFILES 255
MAXLOGMEMBERS 8
MAXDATAFILES 4026
MAXARCHIVELOG 500
MAXBACKUPSET 500
MAXLOGHISTORY 500
MAXFBMARKER 168
MAXFBARCHIVELOG 500
CHARACTER SET UTF8
NATIONAL CHARACTER SET UTF16
;


---- Adding Tempfiles is required in OPEN(NORMAL) mode.
-- ALTER TABLESPACE TEMP ADD TEMPFILE '/tibero/tibero7/database/tac1/temp001.dtf'
--    SIZE 100M REUSE AUTOEXTEND ON NEXT 256K MAXSIZE 32G;
### TEMP file addition required after DB recovery.

6. Start DB in NOMOUNT mode and recreate CONTROLFILE

[tibero@devdb ~]$ tbboot nomount
Change core dump dir to /tibero/tibero7/bin/prof.
Listener port = 8629

Tibero 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (NOMOUNT mode).

[tibero@devdb ~]$ tbsql sys/tibero

tbSQL 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.

Connected to Tibero.

SQL> @ctl_bak.sql

Control File created.

7. Start DB in MOUNT mode and recover DB

[tibero@devdb ~]$ tbdown immediate

Tibero instance terminated (IMMEDIATE mode).

[tibero@devdb ~]$ tbboot mount
Change core dump dir to /tibero/tibero7/bin/prof.
Listener port = 8629

Tibero 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (MOUNT mode).

[tibero@devdb ~]$ tbsql sys/tibero

tbSQL 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.

Connected to Tibero.

SQL> alter database recover automatic;

Database altered.

8. Start DB normally and recreate TEMP file

[tibero@devdb ~]$ tbdown immediate

Tibero instance terminated (IMMEDIATE mode).

[tibero@devdb ~]$ tbboot
Change core dump dir to /tibero/tibero7/bin/prof.
Listener port = 8629

Tibero 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (NORMAL mode).

[tibero@devdb ~]$ tbsql sys/tibero

tbSQL 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.

Connected to Tibero.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/tibero/tibero7/database/tac1/temp001.dtf' 
SIZE 100M REUSE AUTOEXTEND ON NEXT 256K MAXSIZE 32G;

Tablespace 'TEMP' altered.

9. Verify operation and backup controlfile

SQL> select name from v$datafile;

NAME
----------------------------------------------------------
/tibero/tibero7/database/tac1/system001.dtf
/tibero/tibero7/database/tac1/undo001.dtf
/tibero/tibero7/database/tac1/usr001.dtf
/tibero/tibero7/database/tac1/syssub001.dtf
/tibero/tac/datafile/ts1.dtf
/tibero/tibero7/database/tac/ts2.dtf  ## File worked on
/tibero/tibero7/database/tac/ts3.dtf


SQL> alter database backup controlfile to trace as 'ctl_after_backup.sql' reuse noresetlogs;

Database altered.

Case 3. Changing Datafile Name/Path in MOUNT Mode

Method to change the datafile name and path by executing the rename datafile command in MOUNT mode.
DB restart is required, and this method allows changing the name and path of one or more tablespace datafiles or SYSTEM tablespace datafiles without DB recovery process.
Since the datafile information has changed, a control file backup is required after the operation.

Note

For methods of changing datafile names and paths in DB mount mode applicable to versions prior to Tibero 7,
please refer to the link below.

How to Change Datafile Name and Path in DB Mount Mode

Procedure

1. Check datafile

SQL> set linesize 200
SQL> col file_name for a50
SQL> col tablespace_name for a20
SQL> select file_name, tablespace_name from dba_datafiles where file_name like '%ts%.dtf';

FILE_NAME                                          TABLESPACE_NAME
-------------------------------------------------- --------------------
/tibero/tac/datafile/ts1.dtf                       TS1
/tibero/tac/datafile/ts2.dtf                       TS1
/tibero/tibero7/database/tac/ts3.dtf               TS1

2. Start DB in MOUNT mode

[tibero@devdb ~]$ tbdown immediate

Tibero instance terminated (IMMEDIATE mode).

[tibero@devdb ~]$ tbboot mount
Change core dump dir to /tibero/tibero7/bin/prof.
Listener port = 8629

Tibero 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (MOUNT mode).

3. Change target datafile path

[tibero@devdb tac]$ pwd
/tibero/tibero7/database/tac
[tibero@devdb tac]$ cp ts3.dtf /tibero/tac/datafile/ts3.dtf




[tibero@devdb tac]$ ls /tibero/tac/datafile/ts3.dtf
/tibero/tac/datafile/ts3.dtf

4. rename datafile command

[tibero@devdb tac]$ tbsql sys/tibero

tbSQL 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.

Connected to Tibero.

SQL> alter tablespace ts1 rename datafile '/tibero/tibero7/database/tac/ts3.dtf' TO 
     '/tibero/tac/datafile/ts3.dtf';

Tablespace 'TS1' altered.

5. Start DB normally

[tibero@devdb ~]$ tbdown immediate

Tibero instance terminated (IMMEDIATE mode).

[tibero@devdb ~]$ tbboot
Change core dump dir to /tibero/tibero7/bin/prof.
Listener port = 8629

Tibero 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (NORMAL mode).

6. Verify operation and backup controlfile

SQL> set linesize 200
SQL> col file_name for a50
SQL> col tablespace_name for a20
SQL> select file_name, tablespace_name from dba_datafiles where file_name like '%ts_%';

FILE_NAME                                          TABLESPACE_NAME
-------------------------------------------------- --------------------
/tibero/tac/datafile/ts1.dtf                       TS1
/tibero/tac/datafile/ts2.dtf                       TS1
/tibero/tac/datafile/ts3.dtf                       TS1

3 rows selected.

SQL> alter database backup controlfile to trace as '/tibero/ctl_bak_3after.sql' reuse noresetlogs;

Database altered.

Related to

Search

Welcome to Tibero GTS!

Tibero Datafile Name and Path Change Guide

Overview

Methods

Case 1. Changing Datafile Name/Path While Tablespace is Offline

Procedure

Case 2. Changing Datafile Name/Path by Recreating Controlfile

Procedure

1. Check datafile

Case 3. Changing Datafile Name/Path in MOUNT Mode

Procedure

업무 외 시간 안내

Search

Welcome to Tibero GTS!

Overview

Methods

Case 1. Changing Datafile Name/Path While Tablespace is Offline

Procedure

Case 2. Changing Datafile Name/Path by Recreating Controlfile

Procedure

1. Check datafile

Case 3. Changing Datafile Name/Path in MOUNT Mode

Procedure