문서유형ㅣ기술정보
분야ㅣ설치
문서번호ㅣTINTI010
개요
백업되어 있는 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.
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
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]
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
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 ;
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.
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.
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.
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.