Skip to main content

Search

Welcome to Tibero GTS!

Changing Datafile Name and Path by Recreating Control File

Document Type | Technical Information

Category | Installation

Document Number | TINTI010

 

Overview

This explains the method to change the name or path of Datafiles by using and modifying the creation script of the backed-up Control File.
 
This operation requires restarting the DB and performing media recovery. The database must be in ARCHIVELOG mode.
 
 

Method

When the name or path of a Datafile has been changed on the OS, this explains step-by-step how to recover the database (DB) using the backup Control File.

1. Check Datafiles

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/test02.dtf                 TEST
/home/lcj/tbdata/cjdb1/test03.dtf                 TEST 

3 rows selected.


2. Backup Controlfile

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

Database altered.

[lcj@finlin:cj1:/home/lcj/tbdata/cjdb1/rename]ls -alrt 
total 512016
drwxr-xr-x 4 lcj dba      4096 Dec  4 20:17 ..
-rw------- 1 lcj dba 524288000 Dec  4 20:26 test01.dtf 
drwxr-xr-x 2 lcj dba      4096 Dec  4 20:35 .
-rw-r--r-- 1 lcj dba      1554 Dec  4 20:35 cre_ctl_1.sql

 

3. DB Shutdown

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

Tibero instance terminated (IMMEDIATE mode).              

[lcj@finlin:cj1:/home/lcj/tbdata/cj1/rename]ps -ef | grep tbsvr | grep $TB_SID 
[lcj@finlin:cj1:/home/lcj/tbdata/cjdb1/rename]


4. Change Target Datafile Path

[lcj@finlin:cj1:/home/lcj/tbdata/cjdb1]ls test02.dtf 
test02.dtf
[lcj@finlin:cj1:/home/lcj/tbdata/cjdb1]cp test02.dtf /home/lcj/tbdata/cjdb1/rename/test02.dtf 
[lcj@finlin:cj1:/home/lcj/tbdata/cjdb1]cd rename
[lcj@finlin:cj1:/home/lcj/tbdata/cjdb1/rename]ls -alrt 
total 1024016
drwxr-xr-x 4 lcj dba      4096 Dec  4 20:17 ..
-rw-r--r-- 1 lcj dba      1554 Dec  4 20:35 cre_ctl_1.sql 
-rw------- 1 lcj dba 524288000 Dec  4 21:03 test01.dtf 
drwxr-xr-x 2 lcj dba      4096 Dec  4 21:05 .
-rw------- 1 lcj dba 524288000 Dec  4 21:05 test02.dtf


5. Modify Backup Controlfile Creation Script

[lcj@finlin:cj1:/home/lcj/tbdata/cjdb1/rename]vi cre_ctl_1.sql 
[lcj@finlin:cj1:/home/lcj/tbdata/cjdb1/rename]cat cre_ctl_1.sql 
CREATE CONTROLFILE REUSE DATABASE "cjdb1"
LOGFILE 
GROUP 0 (
        '/home/lcj/tbdata/cjdb1/redo001.redo', 
        '/home/lcj/tbdata/cjdb1/redo002.redo'
             ) SIZE 100M, 
GROUP 1 (
        '/home/lcj/tbdata/cjdb1/redo011.redo', 
        '/home/lcj/tbdata/cjdb1/redo012.redo'
             ) SIZE 100M, 
GROUP 2 (
        '/home/lcj/tbdata/cjdb1/redo021.redo', 
        '/home/lcj/tbdata/cjdb1/redo022.redo'
             ) SIZE 100M

NORESETLOGS 
DATAFILE
           '/home/lcj/tbdata/cjdb1/system001.dtf', 
           '/home/lcj/tbdata/cjdb1/undo001.dtf', 
           '/home/lcj/tbdata/cjdb1/usr001.dtf', 
           '/home/lcj/tbdata/cjdb1/tpr_ts.dtf',
           '/home/lcj/tbdata/cjdb1/rename/test01.dtf',
           '/home/lcj/tbdata/cjdb1/rename/test02.dtf',
           '/home/lcj/tbdata/cjdb1/test03.dtf' 
ARCHIVELOG
MAXLOGFILES 100
MAXLOGMEMBERS 8
MAXDATAFILES 1024
MAXARCHIVELOG 500
MAXBACKUPSET 500
MAXLOGHISTORY 500 
CHARACTER SET UTF8 
NATIONAL CHARACTER SET UTF16 
;
---- Recovery is required in MOUNT mode.
--ALTER DATABASE RECOVER AUTOMATIC;

--ALTER DATABASE OPEN ;

---- Adding Tempfiles is required in OPEN mode.
-- ALTER TABLESPACE TEMP ADD TEMPFILE '/home/lcj/tbdata/cjdb1/temp001.dtf
--    SIZE 2G REUSE AUTOEXTEND OFF ;


6. Start DB in NOMOUNT mode and Recreate Controlfile

[lcj@finlin:cj1:/home/lcj/tbdata/cjdb1/rename]tbboot nomount 
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 (NOMOUNT mode).
[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> @cre_ctl_1.sql 

Control File created.


7. Start DB in MOUNT mode and Recover DB (Refer to Step 5 Comments)

[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).
[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 database recover automatic; 

Database altered.

 

8. Start DB in NORMAL mode and Recreate Tempfile (Refer to Step 5 Comments)

[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).
[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  TEMP  ADD  TEMPFILE  '/home/lcj/tbdata/cjdb1/temp001.dtf'  SIZE  2G 
REUSE AUTOEXTEND OFF;

Tablespace 'TEMP' altered.

 

9. Verify Operation and Backup Controlfile

SQL> select name from v$datafile; 

NAME
-------------------------------------------------------------------------------- 
/home/lcj/tbdata/cjdb1/system001.dtf
/home/lcj/tbdata/cjdb1/undo001.dtf 
/home/lcj/tbdata/cjdb1/usr001.dtf 
/home/lcj/tbdata/cjdb1/tpr_ts.dtf
/home/lcj/tbdata/cjdb1/rename/test01.dtf
/home/lcj/tbdata/cjdb1/rename/test02.dtf
/home/lcj/tbdata/cjdb1/test03.dtf 

7 rows selected.

SQL> select name from v$tempfile; 

NAME
--------------------------------------------------------------------------------
/home/lcj/tbdata/cjdb1/temp001.dtf

1 row selected.

SQL> select group#, member from v$logfile;

GROUP# MEMBER          
---------- ----------------------------------------
0 /home/lcj/tbdata/cjdb1/redo001.redo 
0 /home/lcj/tbdata/cjdb1/redo002.redo 
1 /home/lcj/tbdata/cjdb1/redo011.redo 
1 /home/lcj/tbdata/cjdb1/redo012.redo 
2 /home/lcj/tbdata/cjdb1/redo021.redo 
2 /home/lcj/tbdata/cjdb1/redo022.redo

6 rows selected.

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

Database altered.

 

 

 

Related to