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.