본 문서는 백업되어 있는 컨트롤파일 생성 구문을 이용 및 수정하여 데이터파일의 이름 및 경로를 변경하는 방법을 안내합니다.
2. 컨트롤파일 재생성을 통한 데이터파일 이름/경로 변경
2.1. 개요
DB 재기동과 미디어 복구(ALTER DATABASE RECOVER~)가 필요합니다. (※ DB는 ARCHIVELOG여야 합니다.)
OS 상에서 데이터파일의 이름 혹은 경로가 변경되었을 경우, 컨트롤파일 백업본을 사용하여 데이터베이스를 복구할 수 있는 방법입니다.
2.2. 절차
2.2.1. 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/test02.dtf TEST
/home/lcj/tbdata/cjdb1/test03.dtf TEST
3 rows selected.2.2.2. Controlfile 백업 (1.2.6.에서 백업한 쿼리 사용)
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.sql2.2.3. DB DOWN
[lcj@finlin:cj1:/home/lcj/tbdata/cjdb1/rename]tbdown immediate
Tibero instance terminated (IMMEDIATE mode).
[lcj@finlin:cj1:/home/lcj/tbdata/cjdb1/rename]ps -ef | grep tbsvr | grep $TB_SID
[lcj@finlin:cj1:/home/lcj/tbdata/cjdb1/rename]2.2.4. 대상 Datafile 경로 변경
[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.dtf2.2.5. 백업한 controlfile 생성 구문 변경
[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 ;2.2.6. DB NOMOUNT 기동 및 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.2.2.7. DB MOUNT 기동 및 DB 복구 (2.2.5. 주석 참고)
[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.2.2.8. DB NORMAL 기동 및 temp파일 재생성 (2.2.5. 주석 참고)
[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.2.2.9. 작업 확인 및 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.
