다음과 같은 과정을 거칩니다.
1. 현재상태확인
2. 파라미터 변경
(optional) 2-1. alter system switch를 이용해 현재 redo log의 자료를 archive로 저장, block tracking disable하기
3. backup된 자료를 이용해서 원하는 곳으로 복원
4. 후속작업
4-1. default temporary tablespace 만들기
4-2. 이전 redo log를 drop하고 asm안으로 다시 만들어주기
**변경할 파라미터
db_create_file_dest, db_recovery_file_dest[_size], db_create_online_dest_1,2(for redo), control_files
1. 현재 상태확인
SQL> @dd
FILE_ID TABLESPACE FILE_NAME MB AUT
---------- ---------- --------------------------------------------- ---------- ---
1 SYSTEM /home/oracle/db1/system01.dbf 400 YES
2 UNDO1 /home/oracle/db1/undo01.dbf 200 YES
3 SYSAUX /home/oracle/db1/sysaux01.dbf 300 YES
SQL> @log
GROUP# MEMBER SEQUENCE# ARC STATUS MB
---------- --------------------------------------------- ---------- --- ---------------- ----------
1 /home/oracle/db1/fra/redo01.log 13 YES INACTIVE 100
1 /home/oracle/db1/fra/redo01_b.log 13 YES INACTIVE 100
2 /home/oracle/db1/fra/redo02.log 14 NO CURRENT 100
2 /home/oracle/db1/fra/redo02_b.log 14 NO CURRENT 100
SQL> select name from v$controlfile;
NAME
--------------------------------------------
/home/oracle/db1/control01.ctl
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /home/oracle/product/10g/dbs/s
pfiledb1.ora
SQL> select NAME, TOTAL_MB, FREE_MB, STATE, TYPE from v$asm_diskgroup;
NAME TOTAL_MB FREE_MB STATE TYPE
------------------------------ ---------- ---------- ----------- ------
DATA 8192 8142 MOUNTED EXTERN
FRA 5120 5070 MOUNTED EXTERN
2. 파라미터 변경
alter system set db_create_file_dest='+DATA' scope=spfile;
alter system set db_recovery_file_dest='+FRA' scope=spfile;
alter system set db_recovery_file_dest_size=5069M scope=spfile;
alter system reset control_files scope=spfile sid='*'; << reset에 주의하세요
alter system set db_create_online_log_dest_1='+FRA' scope=spfile;
alter system set db_create_online_log_dest_2='+DATA' scope=spfile;
2-1. 현재 redo log의 자료를 내려쓰기, block tracking disable하기
alter database disable block change tracking;
alter system switch logfile; 여러번~
3. shutdown한 후 rman으로 원하는 곳으로 복원하기
alter system set db_create_online_log_dest_2='+DATA' scope=spfile;
2-1. 현재 redo log의 자료를 내려쓰기, block tracking disable하기
alter database disable block change tracking;
alter system switch logfile; 여러번~
3. shutdown한 후 rman으로 원하는 곳으로 복원하기
rman target /
shutdown immediate
startup nomount
restore controlfile from '/home/oracle/db1/control01.ctl';
alter database mount;
backup as copy database format '+DATA';
switch database to copy;
alter database open;
exit;
[oracle@server15 ~]$ rman target /
RMAN> shutdown immediate
Oracle instance shut down
RMAN> startup nomount
connected to target database (not started)
Oracle instance started
Total System Global Area 239075328 bytes
Fixed Size 1218724 bytes
Variable Size 180356956 bytes
Database Buffers 54525952 bytes
Redo Buffers 2973696 bytes
RMAN> restore controlfile from '/home/oracle/db1/control01.ctl';
Starting restore at 2012-03-30:10:25:51
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=47 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=+FRA/db1/controlfile/backup.256.779279155
Finished restore at 2012-03-30:10:26:00
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> backup as copy database format '+DATA';
Starting backup at 2012-03-30:10:26:37
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=47 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=43 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/home/oracle/db1/system01.dbf
channel ORA_DISK_2: starting datafile copy
input datafile fno=00003 name=/home/oracle/db1/sysaux01.dbf
.... 생략 ....
channel ORA_DISK_2: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_2: starting piece 1 at 2012-03-30:10:27:27
channel ORA_DISK_2: finished piece 1 at 2012-03-30:10:27:28
piece handle=+DATA/db1/backupset/2012_03_30/nnsnf0_tag20120330t102637_0.260.779279249 tag=TAG20120330T102637 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:02
output filename=+DATA/db1/datafile/undo1.258.779279243 tag=TAG20120330T102637 recid=4 stamp=779279254
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:13
Finished backup at 2012-03-30:10:27:36
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DATA/db1/datafile/system.256.779279199"
datafile 2 switched to datafile copy "+DATA/db1/datafile/undo1.258.779279243"
datafile 3 switched to datafile copy "+DATA/db1/datafile/sysaux.257.779279201"
RMAN> alter database open;
database opened
RMAN> exit
Recovery Manager complete.
SQL> @dd
FILE_ID TABLESPACE FILE_NAME MB AUT
---------- ---------- --------------------------------------------- ---------- ---
1 SYSTEM +DATA/db1/datafile/system.256.779279199 400 YES
2 UNDO1 +DATA/db1/datafile/undo1.258.779279243 200 YES
3 SYSAUX +DATA/db1/datafile/sysaux.257.779279201 300 YES
SQL> select name from v$controlfile;
NAME
---------------------------------------------------------------------------------------------
+FRA/db1/controlfile/backup.256.779279155
4. 후속작업
4-1. temporary tablespace를 다시 생성
(add tempfile 이후, 원래 있던 파일 drop tempfile)
SQL> @dt
FILE_ID TABLESPACE FILE_NAME MB AUT
---------- ---------- --------------------------------------------- ---------- ---
1 TEMP /home/oracle/db1/temp01.dbf 100 YES
SQL> alter tablespace temp add tempfile size 100M autoextend on;
Tablespace altered.
SQL> alter tablespace temp drop tempfile '/home/oracle/db1/temp01.dbf';
Tablespace altered.
SQL> @dt
FILE_ID TABLESPACE FILE_NAME MB AUT
---------- ---------- --------------------------------------------- ---------- ---
2 TEMP +DATA/db1/tempfile/temp.261.779279759 100 YES
4-2. redo 옮기기
SQL> @log
GROUP# MEMBER SEQUENCE# ARC STATUS MB
---------- --------------------------------------------- ---------- --- ---------------- ----------
1 /home/oracle/db1/fra/redo01.log 15 NO CURRENT 100
1 /home/oracle/db1/fra/redo01_b.log 15 NO CURRENT 100
2 /home/oracle/db1/fra/redo02.log 14 YES INACTIVE 100
2 /home/oracle/db1/fra/redo02_b.log 14 YES INACTIVE 100
SQL> alter database add logfile group 3 size 50M;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile group 2 size 50M;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1 size 50M;
Database altered.
SQL> @log
GROUP# MEMBER SEQUENCE# ARC STATUS MB
---------- --------------------------------------------- ---------- --- ---------------- ----------
1 +DATA/db1/onlinelog/group_1.264.779280015 0 YES UNUSED 50
1 +FRA/db1/onlinelog/group_1.261.779280015 0 YES UNUSED 50
2 +DATA/db1/onlinelog/group_2.263.779279973 16 NO CURRENT 50
2 +FRA/db1/onlinelog/group_2.259.779279973 16 NO CURRENT 50
3 +DATA/db1/onlinelog/group_3.262.779279945 0 YES UNUSED 50
3 +FRA/db1/onlinelog/group_3.258.779279945 0 YES UNUSED 50
6 rows selected.
File System --> ASM으로 migration 완료
'Oracle > ASM' 카테고리의 다른 글
| control file 다중화하기 ; ASM환경, RMAN이용 (0) | 2012.04.01 |
|---|---|
| DB운영(ASM)중 추가적인 ASM디스크 추가하기 ; 링크 (0) | 2012.03.30 |
| DBCA를 이용해 ASM diskgroup 수동구성하기(silent) ; RHEL4환경 (2) | 2012.03.30 |
| Admin 2 20번째 ASM이란? ; 장단점, 구조, rebalance 등 (0) | 2012.03.11 |
| ASM 설치시 디스크선택 할때(외부, 보통, 높음)에 대한 설명 (0) | 2012.03.04 |