Skip to main content

Search

Welcome to Tibero GTS!

Changing Datafile Name and Path in DB MOUNT Mode

Document Type | Technical Information

Category | Installation

Document Number | TINTI014

 

Overview

This explains how to change the name and path of a datafile by executing the Rename Datafile command after switching the database to MOUNT mode.
 
This method allows changing the name and path of one or more tablespace datafiles or SYSTEM tablespace datafiles without performing a DB recovery process.
 
After this operation, a DB restart is required, and a control file backup must be performed.
 
 

Method

Perform the change operation as per the following steps.
 

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 '%test0_.dtf';

FILE_NAME                                                       TABLESPACE_NAME     
--------------------------------------------------            --------------------
/home/lcj/tbdata/cjdb1/rename/test01.dtf    TEST 
/home/lcj/tbdata/cjdb1/rename/test02.dtf    TEST
/home/lcj/tbdata/cjdb1/test03.dtf                  TEST

3 rows selected.


2. Start DB in MOUNT Mode

[lcj@finlin:cj1:/home/lcj/tbdata/cjdb1/rename]tbdown immediate 

Tibero instance terminated (IMMEDIATE mode).  
            
[lcj@finlin:cj1:/home/lcj/tbdata/cjdb1/rename]tbboot mount
Change core dump dir to /home/lcj/tibero6/bin/prof. 
Listener port = 5086

Tibero 6  

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

 

3. Change Target Datafile Path

[lcj@finlin:cj1:/home/lcj/tbdata/cjdb1]ls test03.dtf 
test03.dtf
[lcj@finlin:cj1:/home/lcj/tbdata/cjdb1]cp test03.dtf /home/lcj/tbdata/cjdb1/rename/test03.dtf 
[lcj@finlin:cj1:/home/lcj/tbdata/cjdb1]cd rename
[lcj@finlin:cj1:/home/lcj/tbdata/cjdb1/rename]ls -alrt 
total 1536016
drwxr-xr-x 4 lcj dba      4096 Dec  4 20:17 ..
-rw-r--r-- 1 lcj dba      1561 Dec  4 21:07 cre_ctl_1.sql 
-rw------- 1 lcj dba 524288000 Dec  4 21:52 test01.dtf 
-rw------- 1 lcj dba 524288000 Dec  4 21:52 test02.dtf 
drwxr-xr-x 2 lcj dba      4096 Dec  4 21:54 .
-rw------- 1 lcj dba 524288000 Dec  4 21:54 test03.dtf

 

4. Rename Datafile Command

[lcj@finlin:cj1:/home/lcj/tbdata/cjdb1/rename]tbsql sys/tibero 

tbSQL 6  

TmaxData Corporation Copyright (c) 2008-. All rights reserved. 

Connected to Tibero.

SQL> alter tablespace TEST rename datafile '/home/lcj/tbdata/cjdb1/test03.dtf' TO 
'/home/lcj/tbdata/cjdb1/rename/test03.dtf';

Tablespace 'TEST' altered.

 

5. Restart DB

[lcj@finlin:cj1:/home/lcj/tbdata/cjdb1/rename]tbdown immediate 

Tibero instance terminated (IMMEDIATE mode).    
          
[lcj@finlin:cj1:/home/lcj/tbdata/cjdb1/rename]tbboot
Change core dump dir to /home/lcj/tibero6/bin/prof. 
Listener port = 5086

Tibero 6  

TmaxData Corporation Copyright (c) 2008-. 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 '%test0_.dtf'; 

FILE_NAME                                          TABLESPACE_NAME     
-------------------------------------------------- --------------------
/home/lcj/tbdata/cjdb1/rename/test01.dtf           TEST 
/home/lcj/tbdata/cjdb1/rename/test02.dtf           TEST
/home/lcj/tbdata/cjdb1/rename/test03.dtf           TEST

3 rows selected.

SQL> alter database backup controlfile to trace as '/home/lcj/tbdata/cjdb1/rename/cre_ctl_3.sql' 
reuse noresetlogs;

Database altered.

 

 

 

Related to