개요
백업되어 있는 Control File 생성 구문을 이용 및 수정하여, Datafile의 이름 또는 경로를 변경하는 방법을 설명합니다.
이 작업은 DB 재기동과 미디어 복구 절차가 필요하며, 데이터베이스는 ARCHIVELOG 모드여야 합니다.
방법
OS 상에서 Datafile의 이름 또는 경로가 변경되었을 경우, Control File 백업본을 사용하여 데이터베이스(DB)를 복구할 수 있는 방법을 순서대로 설명합니다.
1. Data file 확인
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.
SQL
복사
2. 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
SQL
복사
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]
SQL
복사
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.dtf
SQL
복사
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 ;
SQL
복사
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.
SQL
복사
7. DB MOUNT 기동 및 DB 복구 (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.
SQL
복사
8. DB NORMAL 기동 및 temp파일 재생성 (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.
SQL
복사
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.
SQL
복사