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.
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.