백업&복구 Extra - 파일 경로 재배치 (Admin복습)

[0] 사전작업

SQL> set linesize 200
SQL> set pagesize 100
SQL> col name for a50
SQL> col member for a50
SQL> col PROPERTY_NAME for a30
SQL> col PROPERTY_VALUE for a30
SQL> col DESCRIPTION for a40

 
 
 
★ pfile에서 작업
 
[1] 컨트롤파일
 
1. 현재상황

SQL> select name from v$controlfile;

NAME
--------------------------------------------------
/data/disk1/ctrl01.ctl
/data/disk2/ctrl02.ctl

 
 
2. 목표

/home/oracle/oradata/testdb/control01.ctl
/home/oracle/oradata/testdb/control02.ctl
/home/oracle/oradata/testdb/control03.ctl

 
 
3. 방법
▶ Nomount 상태작업
 

SQL> shutdown immediate;
 
SQL>!
$ vi /home/oracle/product/10g/dbs/inittestdb.ora
control_files='/home/oracle/oradata/testdb/control01.ctl','/home/oracle/oradata/testdb/control02.ctl',
                   '/home/oracle/oradata/testdb/control03.ctl'
 
$ cp /data/disk2/ctrl02.ctl /home/oracle/oradata/testdb/control01.ctl
$ cp /data/disk2/ctrl02.ctl /home/oracle/oradata/testdb/control02.ctl
$ cp /data/disk2/ctrl02.ctl /home/oracle/oradata/testdb/control03.ctl
$ exit
 
SQL> startup

 
 
 
 
 
[2] redo log file
 
1. 현재상황

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by 1,2;
SQL> save log.sql

 

SQL> @log

    GROUP# MEMBER                                                     MB ARCHIV STATUS
---------- -------------------------------------------------- ---------- ------ --------------------------------
         1 /data/disk1/redo01_1.rdo                                    5 YES    INACTIVE
         1 /data/disk2/redo01_2.rdo                                    5 YES    INACTIVE
         2 /data/disk1/redo02_1.rdo                                    5 YES    INACTIVE
         2 /data/disk2/redo02_2.rdo                                    5 YES    INACTIVE
         3 /data/disk1/redo03_1.rdo                                    5 NO     CURRENT
         3 /data/disk2/redo03_2.rdo                                    5 NO     CURRENT
 
6 rows selected.

 
 
2. 목표

/home/oracle/oradata/testdb/redo01_a.log
/home/oracle/oradata/testdb/redo01_b.log
/home/oracle/oradata/testdb/redo02_a.log
/home/oracle/oradata/testdb/redo02_b.log
/home/oracle/oradata/testdb/redo03_a.log
/home/oracle/oradata/testdb/redo03_b.log

 
 
3. 방법
▶ Mount 상태작업
 

SQL> shutdown immediate;
 
SQL> !
$ cp /data/disk1/redo01_1.rdo /home/oracle/oradata/testdb/redo01_a.log
$ cp /data/disk2/redo01_2.rdo /home/oracle/oradata/testdb/redo01_b.log
$ cp /data/disk1/redo02_1.rdo /home/oracle/oradata/testdb/redo02_a.log
$ cp /data/disk2/redo02_2.rdo /home/oracle/oradata/testdb/redo02_b.log
$ cp /data/disk1/redo03_1.rdo /home/oracle/oradata/testdb/redo03_a.log
$ cp /data/disk2/redo03_2.rdo /home/oracle/oradata/testdb/redo03_b.log
$ exit
 
SQL> startup mount;
 
SQL> alter database rename file '/data/disk1/redo01_1.rdo' to '/home/oracle/oradata/testdb/redo01_a.log';
SQL> alter database rename file '/data/disk2/redo01_2.rdo' to '/home/oracle/oradata/testdb/redo01_b.log';
SQL> alter database rename file '/data/disk1/redo02_1.rdo' to '/home/oracle/oradata/testdb/redo02_a.log';
SQL> alter database rename file '/data/disk2/redo02_2.rdo' to '/home/oracle/oradata/testdb/redo02_b.log';
SQL> alter database rename file '/data/disk1/redo03_1.rdo' to '/home/oracle/oradata/testdb/redo03_a.log';
SQL> alter database rename file '/data/disk2/redo03_2.rdo' to '/home/oracle/oradata/testdb/redo03_b.log';
 
SQL> alter database open;

 
 
 
 

※ 참고
① 상태변환
log switch 발생 (CURRENT → ACTIVE)

SQL> alter system switch logfile;

checkpoint 발생 (ACTIVE → INACTIVE)

SQL> alter system checkpoint;

 
② 추가

그룹> alter database add logfile group 4
   2  '/home/oracle/oradata/testdb/redo04.log' size 10M;
멤버> alter database add logfile member
   2  '/home/oracle/oradata/testdb/redo04_b.log' to group 4;

 
③ 삭제

그룹> alter database drop logfile group 4;
멤버> alter database drop logfile member
   2  '/home/oracle/oradata/testdb/redo04_b.log';

 

 
 
 
 
[3] Data file
1. 현재상황

SQL> select name,status from v$datafile;

NAME                                                      STATUS
--------------------------------------- --------------
/data/disk3/system01.dbf                           SYSTEM
/data/disk4/undotbs01.dbf                          ONLINE
/data/disk5/sysaux01.dbf                           ONLINE
/data/disk5/users01.dbf                              ONLINE
/data/disk5/example01.dbf                          ONLINE

 
 
2. 목표

/home/oracle/oradata/testdb/example01.dbf
/home/oracle/oradata/testdb/sysaux01.dbf
/home/oracle/oradata/testdb/system01.dbf
/home/oracle/oradata/testdb/undotbs01.dbf
/home/oracle/oradata/testdb/users01.dbf
/home/oracle/oradata/testdb/temp01.dbf   ← 새로 생성

 
 
3. 방법
▶ Mount 상태작업

SQL> shutdown immediate;
 
SQL> !
$ cp /data/disk3/system01.dbf /home/oracle/oradata/testdb/system01.dbf
$ cp /data/disk4/undotbs01.dbf /home/oracle/oradata/testdb/undotbs01.dbf
$ cp /data/disk5/sysaux01.dbf /home/oracle/oradata/testdb/sysaux01.dbf
$ cp /data/disk5/users01.dbf /home/oracle/oradata/testdb/users01.dbf
$ cp /data/disk5/example01.dbf /home/oracle/oradata/testdb/example01.dbf
$ exit
 
SQL> startup mount;
 
SQL> alter database rename file '/data/disk3/system01.dbf' to '/home/oracle/oradata/testdb/system01.dbf';
SQL> alter database rename file '/data/disk4/undotbs01.dbf' to '/home/oracle/oradata/testdb/undotbs01.dbf';
SQL> alter database rename file '/data/disk5/sysaux01.dbf' to '/home/oracle/oradata/testdb/sysaux01.dbf';
SQL> alter database rename file '/data/disk5/users01.dbf' to '/home/oracle/oradata/testdb/users01.dbf';
SQL> alter database rename file '/data/disk5/example01.dbf' to '/home/oracle/oradata/testdb/example01.dbf';
 
SQL> alter database open;

 
 
4. temporary 테이블 스페이스 추가
① 현재 temporary tablespace 파일 및 설정 확인

SQL> select tablespace_name, bytes, file_name from dba_temp_files;
SQL> select * from database_properties where property_name like '%TEMP%';

 
③ temporary tablespace 생성

SQL> create temporary tablespace temp tempfile '/home/oracle/oradata/testdb/temp01.dbf' size 10M;
 
SQL> !ls -al /home/oracle/oradata/* | grep temp

③ default temporary tablespace 지정

SQL> alter database default temporary tablespace temp;

 
④ temporary tablespace 삭제

SQL> drop tablespace tmp;

 
 
 
[4] 기존 파일삭제

→ 위에서 작업하고 남은 파일들 삭제 (실습 편의를 위해 cp로 작업해서 남아있음. mv로 작업했으면 생략)
→ 삭제 전에 사용중인 파일인지 꼭 확인하고 삭제하기!!

 
① 위에서 작업해서 지워도 확실한 파일

$ rm /data/disk1/ctrl01.ctl
$ rm /data/disk2/ctrl02.ctl
 
$ rm /data/disk1/redo01_1.rdo
$ rm /data/disk2/redo01_2.rdo
$ rm /data/disk1/redo02_1.rdo
$ rm /data/disk2/redo02_2.rdo
$ rm /data/disk1/redo03_1.rdo
$ rm /data/disk2/redo03_2.rdo
 
$ rm /data/disk3/system01.dbf
$ rm /data/disk4/undotbs01.dbf
$ rm /data/disk5/sysaux01.dbf
$ rm /data/disk5/users01.dbf
$ rm /data/disk5/example01.dbf

$ rm /home/oracle/oradata/testdb/tmp01.dbf    

 
 
② 기타 실습으로 인해 남아있을 지도 모를 파일 검색해 보기

$ ls /data/disk1
$ ls /data/disk2
$ ls /data/disk3
$ ls /data/disk4
$ ls /data/disk5