Cold Backup & Hot Backup

Oracle 10g/10g - 실습
posted by I유령I 2010. 3. 8. 11:05
Cold Backup

[oracle@ghost]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 8 10:28:54 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              96470608 bytes
Database Buffers          184549376 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> col name format a50
SQL> select name from v$datafile;

NAME
--------------------------------------------------
/home/oracle/oradata/testdb/system01.dbf
/home/oracle/oradata/testdb/undotbs01.dbf
/home/oracle/oradata/testdb/sysaux01.dbf
/home/oracle/oradata/testdb/users01.dbf
/home/oracle/oradata/testdb/example01.dbf

SQL> select name from v$controlfile;

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

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------
/home/oracle/oradata/testdb/redo03.log
/home/oracle/oradata/testdb/redo02.log
/home/oracle/oradata/testdb/redo01.log

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !cp -av /home/oracle/oradata/testdb/*.dbf /data/backup/close/
`/home/oracle/oradata/testdb/example01.dbf' -> `/data/backup/close/example01.dbf'
`/home/oracle/oradata/testdb/sysaux01.dbf' -> `/data/backup/close/sysaux01.dbf'
`/home/oracle/oradata/testdb/system01.dbf' -> `/data/backup/close/system01.dbf'
`/home/oracle/oradata/testdb/temp01.dbf' -> `/data/backup/close/temp01.dbf'
`/home/oracle/oradata/testdb/undotbs01.dbf' -> `/data/backup/close/undotbs01.dbf'
`/home/oracle/oradata/testdb/users01.dbf' -> `/data/backup/close/users01.dbf'

SQL> !cp -av /home/oracle/oradata/testdb/*.ctl /data/backup/close/
`/home/oracle/oradata/testdb/control01.ctl' -> `/data/backup/close/control01.ctl'
`/home/oracle/oradata/testdb/control02.ctl' -> `/data/backup/close/control02.ctl'
`/home/oracle/oradata/testdb/control03.ctl' -> `/data/backup/close/control03.ctl'

SQL> !cp -av /home/oracle/oradata/testdb/*.log /data/backup/close/
`/home/oracle/oradata/testdb/redo01.log' -> `/data/backup/close/redo01.log'
`/home/oracle/oradata/testdb/redo02.log' -> `/data/backup/close/redo02.log'
`/home/oracle/oradata/testdb/redo03.log' -> `/data/backup/close/redo03.log'

SQL> !cp -av $ORACLE_HOME/dbs /data/backup/close/
`/home/oracle/product/10g/dbs' -> `/data/backup/close/dbs'
`/home/oracle/product/10g/dbs/orapwtestdb' -> `/data/backup/close/dbs/orapwtestdb'
`/home/oracle/product/10g/dbs/init.ora' -> `/data/backup/close/dbs/init.ora'
`/home/oracle/product/10g/dbs/initdw.ora' -> `/data/backup/close/dbs/initdw.ora'
`/home/oracle/product/10g/dbs/spfiletestdb.ora' -> `/data/backup/close/dbs/spfiletestdb.ora'
`/home/oracle/product/10g/dbs/lkTESTDB' -> `/data/backup/close/dbs/lkTESTDB'
`/home/oracle/product/10g/dbs/hc_testdb.dat' -> `/data/backup/close/dbs/hc_testdb.dat'

SQL> 



Hot Backup

SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size             100664912 bytes
Database Buffers          180355072 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> col tablespace_name format a10
SQL> col file_name format a50
SQL> select tablespace_name, status, contents from dba_tablespaces;

TABLESPACE STATUS    CONTENTS
---------- --------- ---------
SYSTEM     ONLINE    PERMANENT
UNDOTBS1   ONLINE    UNDO
SYSAUX     ONLINE    PERMANENT
TEMP       ONLINE    TEMPORARY
USERS      ONLINE    PERMANENT
EXAMPLE    ONLINE    PERMANENT

6 rows selected.

SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;

TABLESPACE         MB FILE_NAME
---------- ---------- --------------------------------------------------
USERS               5 /home/oracle/oradata/testdb/users01.dbf
SYSAUX            240 /home/oracle/oradata/testdb/sysaux01.dbf
UNDOTBS1           35 /home/oracle/oradata/testdb/undotbs01.dbf
SYSTEM            480 /home/oracle/oradata/testdb/system01.dbf
EXAMPLE           100 /home/oracle/oradata/testdb/example01.dbf

SQL> alter tablespace system begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/system01.dbf /data/backup/open/
`/home/oracle/oradata/testdb/system01.dbf' -> `/data/backup/open/system01.dbf'

SQL> alter tablespace system end backup;

Tablespace altered.

SQL> alter tablespace sysaux begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/sysaux01.dbf /data/backup/open/
`/home/oracle/oradata/testdb/sysaux01.dbf' -> `/data/backup/open/sysaux01.dbf'

SQL> alter tablespace sysaux end backup;

Tablespace altered.

SQL> alter tablespace undotbs1 begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/undotbs01.dbf /data/backup/open/
`/home/oracle/oradata/testdb/undotbs01.dbf' -> `/data/backup/open/undotbs01.dbf'

SQL> alter tablespace undotbs1 end backup;

Tablespace altered.

SQL> alter tablespace users begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/users01.dbf /data/backup/open/
`/home/oracle/oradata/testdb/users01.dbf' -> `/data/backup/open/users01.dbf'

SQL> alter tablespace users end backup;

Tablespace altered.

SQL> alter tablespace example begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/example01.dbf /data/backup/open/
`/home/oracle/oradata/testdb/example01.dbf' -> `/data/backup/open/example01.dbf'

SQL> alter tablespace example end backup;

Tablespace altered.

SQL> alter database backup controlfile to '/data/backup/open/control01.ctl';

Database altered.

SQL> select a.file#, a.name, b.status, TO_CHAR(b.time, 'YYYY-MM-DD:HH24:MI:SS') as time
  2  from v$datafile a, v$backup b
  3  where a.file# = b.file#;

     FILE# NAME                                               STATUS             TIME
---------- -------------------------------------------------- ------------------ -------------------
         1 /home/oracle/oradata/testdb/system01.dbf           NOT ACTIVE         2010-03-08:11:10:04
         2 /home/oracle/oradata/testdb/undotbs01.dbf          NOT ACTIVE         2010-03-08:11:11:07
         3 /home/oracle/oradata/testdb/sysaux01.dbf           NOT ACTIVE         2010-03-08:11:10:38
         4 /home/oracle/oradata/testdb/users01.dbf            NOT ACTIVE         2010-03-08:11:11:53
         5 /home/oracle/oradata/testdb/example01.dbf          NOT ACTIVE         2010-03-08:11:12:16

SQL> 
저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

Archive log mode Complete Recovery  (0) 2010.03.09
No Archive log mode Recovery  (0) 2010.03.08
Archive log mode 설정 및 활성화/비활성화  (0) 2010.03.05
Privileges(권한) 관리  (0) 2010.03.05
Profile(프로파일) 생성과 관리  (0) 2010.03.05

Backup

Oracle 10g/10g - Admin I
posted by I유령I 2010. 3. 8. 10:45
1. 실습 환경 설정
1) 사용 O/S : Red Hat Enterprise Linux 4
2) Oracle Database : Oracle Database 10g R2 (10.2.0.1)
3) 각 원본 파일 위치
- Data file : /home/oracle/oradata/testdb/*.dbf
- Redo log file : /home/oracle/oradata/testdb/*.log
- Control file : /home/oracle/oradata/testdb/*.ctl
4) Archived log file 위치 : /data/arc1, /data/arc2
5) 백업 경로
- /data/backup/close : Cold Backup 경로
- /data/backup/open : Hot Backup 경로

2. 백업 대상 파일
1) Data file
2) Redo log file
3) Control file
4) Parameter file
5) Password file
※ 1), 2), 3) 은 필수적인 백업 파일이며4), 5) 는 추후 복구할 때 없으면 불편하기 때문에 백업을 받는 파일이다.

3. 오라클 백업 관련 운영 모드
Archive log mode
- Online Redo log 파일을 다른 장소로 하니 더 Archive 해서 혹시 발생할 지 모르는 Online redo log 파일의 장애를 대비하는 방법이다.
- 관리자가 별도로 설정해야 하며 Archived log가 저장될 별도의 저장 공간이 필요하다.
저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - Admin I' 카테고리의 다른 글

Export / Import  (0) 2010.03.15
Restore & Recovery  (0) 2010.03.08
sysdba 권한 로그인시 패스워드 지정  (0) 2010.03.05
프로파일 & 유저 관리 & 권한 관리  (0) 2010.03.05
Constraints (제약 조건)  (0) 2010.03.05

종합 연습문제 (디스크 추가, Controlfile, Redo log file, Tablespace)

Oracle 10g/10g - 연습문제
posted by I유령I 2010. 2. 28. 20:49

아래와 같이 데이터베이스 구성하기 (10GB 디스크 5개 추가)
/disk1/system01.dbf, sysaux01.dbf
/disk2/undotbs01.dbf
/disk3/users01.dbf, insa01.dbf, example01.dbf
/disk4/control01.ctl, redo01_a.log, redo02_a.log, redo03_a.log
/disk5/control02.ctl, redo01_b.log, redo02_b.log, redo03_b.log



#디스크 추가 방법은 LiNUX 카테고리의 "RHEL4 디스크 추가" 글의 내용을 참고하기 바란다.

[oracle@ghost]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Feb 28 19:51:09 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

#startup한 후 데이터 파일, 리두 로그 그룹/멤버를 확인한다.
SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              96470608 bytes
Database Buffers          184549376 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> col name format a50
SQL> col member format a50
SQL> col tablespace_name format a20
SQL> col file_name format a50
SQL> select tablespace_name, bytes, file_name
  2  from dba_data_files;

TABLESPACE_NAME           BYTES FILE_NAME
-------------------- ---------- --------------------------------------------------
USERS                   5242880 /home/oracle/disk5/users01.dbf
SYSAUX                251658240 /home/oracle/disk4/sysaux01.dbf
UNDOTBS1               36700160 /home/oracle/disk4/undotbs01.dbf
SYSTEM                503316480 /home/oracle/disk3/system01.dbf
EXAMPLE               104857600 /home/oracle/disk5/example01.dbf

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;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/disk1/redo01_a.log                            50 NO  INACTIVE
         1 /home/oracle/disk2/redo01_b.log                            50 NO  INACTIVE
         2 /home/oracle/disk1/redo02_a.log                            50 NO  CURRENT
         2 /home/oracle/disk2/redo02_b.log                            50 NO  CURRENT
         3 /home/oracle/disk1/redo03_a.log                            50 NO  INACTIVE
         3 /home/oracle/disk2/redo03_b.log                            50 NO  INACTIVE

6 rows selected.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

#파리미터 파일(inittestdb.ora) 수정
SQL> !vi $ORACLE_HOME/dbs/inittestdb.ora

*.control_files='/disk4/control01.ctl',
                '/disk5/control02.ctl'

#파리미터 파일에서 수정한 디렉토리로 파일 이동
SQL> !cp /home/oracle/disk1/control01.ctl /disk4/control01.ctl

SQL> !cp /home/oracle/disk1/control01.ctl /disk5/control02.ctl

#연습문제 내용과 같은 디렉토리로 각 파일 이동
SQL> !mv /home/oracle/disk3/system01.dbf /disk1/system01.dbf

SQL> !mv /home/oracle/disk4/sysaux01.dbf /disk1/sysaux01.dbf

SQL> !mv /home/oracle/disk4/undotbs01.dbf /disk2/undotbs01.dbf

SQL> !mv /home/oracle/disk5/users01.dbf /disk3/users01.dbf

SQL> !mv /home/oracle/disk5/example01.dbf /disk3/example01.dbf

SQL> !mv /home/oracle/disk1/redo01_a.log /disk4/redo01_a.log

SQL> !mv /home/oracle/disk2/redo01_b.log /disk5/redo01_b.log

SQL> !mv /home/oracle/disk1/redo02_a.log /disk4/redo02_a.log

SQL> !mv /home/oracle/disk2/redo02_b.log /disk5/redo02_b.log

SQL> !mv /home/oracle/disk1/redo03_a.log /disk4/redo03_a.log

SQL> !mv /home/oracle/disk2/redo03_b.log /disk5/redo03_b.log

SQL> startup mount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              96470608 bytes
Database Buffers          184549376 bytes
Redo Buffers                2973696 bytes
Database mounted.
#Control file 내용 수정
SQL> alter database rename
  2  file '/home/oracle/disk3/system01.dbf'
  3  to '/disk1/system01.dbf';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/disk4/sysaux01.dbf'
  3  to '/disk1/sysaux01.dbf';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/disk4/undotbs01.dbf'
  3  to '/disk2/undotbs01.dbf';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/disk5/users01.dbf'
  3  to '/disk3/users01.dbf';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/disk5/example01.dbf'
  3  to '/disk3/example01.dbf';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/disk1/redo01_a.log'
  3  to '/disk4/redo01_a.log';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/disk2/redo01_b.log'
  3  to '/disk5/redo01_b.log';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/disk1/redo02_a.log'
  3  to '/disk4/redo02_a.log';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/disk2/redo02_b.log'
  3  to '/disk5/redo02_b.log';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/disk1/redo03_a.log'
  3  to '/disk4/redo03_a.log';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/disk2/redo03_b.log'
  3  to '/disk5/redo03_b.log';

Database altered.

SQL> alter database open;

Database altered.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------
/disk4/control01.ctl
/disk5/control02.ctl

SQL> select tablespace_name, bytes, file_name
  2  from dba_data_files;

TABLESPACE_NAME           BYTES FILE_NAME
-------------------- ---------- --------------------------------------------------
USERS                   5242880 /disk3/users01.dbf
SYSAUX                251658240 /disk1/sysaux01.dbf
UNDOTBS1               36700160 /disk2/undotbs01.dbf
SYSTEM                503316480 /disk1/system01.dbf
EXAMPLE               104857600 /disk3/example01.dbf

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;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /disk4/redo01_a.log                                        50 NO  INACTIVE
         1 /disk5/redo01_b.log                                        50 NO  INACTIVE
         2 /disk4/redo02_a.log                                        50 NO  CURRENT
         2 /disk5/redo02_b.log                                        50 NO  CURRENT
         3 /disk4/redo03_a.log                                        50 NO  INACTIVE
         3 /disk5/redo03_b.log                                        50 NO  INACTIVE

6 rows selected.

SQL>

저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 연습문제' 카테고리의 다른 글

Complete Recovery 연습 문제  (0) 2010.03.09
No Archive log mode Recovery 연습문제  (0) 2010.03.09
종합 연습문제 (Controlfile, Redo log file, Tablespace)  (0) 2010.02.27
Controlfile, Redo log files 재구성 #2  (0) 2010.02.25
Controlfile, Redo log files 재구성 #1  (0) 2010.02.25

종합 연습문제 (Controlfile, Redo log file, Tablespace)

Oracle 10g/10g - 연습문제
posted by I유령I 2010. 2. 27. 23:13

아래와 같이 데이터베이스 구성하기.
/home/oracle/disk1/control01.ctl, redo01_a.log, redo02_a.log, redo03_a.log
/home/oracle/disk2/control02.ctl, redo01_b.log, redo02_b.log, redo03_b.log
/home/oracle/disk3/control03.ctl, system01.dbf
/home/oracle/disk4/sysaux01.dbf, undotbs01.dbf
/home/oracle/disk5/users01.dbf, example01.dbf



[oracle@ghost]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Feb 27 21:17:14 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              96470608 bytes
Database Buffers          184549376 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> col tablespace_name format a20
SQL> col file_name format a50
SQL> select tablespace_name, bytes, file_name from dba_data_files;

TABLESPACE_NAME           BYTES FILE_NAME
-------------------- ---------- --------------------------------------------------
USERS                   5242880 /home/oracle/oradata/testdb/users01.dbf
SYSAUX                251658240 /home/oracle/oradata/testdb/sysaux01.dbf
UNDOTBS1               36700160 /home/oracle/oradata/testdb/undotbs01.dbf
SYSTEM                503316480 /home/oracle/oradata/testdb/system01.dbf
EXAMPLE               104857600 /home/oracle/oradata/testdb/example01.dbf

SQL> col member format a50
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;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/oradata/testdb/redo01.log                     50 NO  INACTIVE
         2 /home/oracle/oradata/testdb/redo02.log                     50 NO  INACTIVE
         3 /home/oracle/oradata/testdb/redo03.log                     50 NO  CURRENT

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create pfile from spfile;

File created.

#데이터베이스 startup시 pfile 을 적용시키기 위해 spfile의 파일명을 수정(혹은 삭제)
#startup시 파라미터 적용 우선 순위 : spfile<SID>.ora -> 기본 spfile -> init<SID>.ora
SQL> !mv /home/oracle/product/10g/dbs/spfiletestdb.ora /home/oracle/product/10g/dbs/spfiletestdb.ora.old

#Control file 경로 수정
SQL> !vi /home/oracle/product/10g/dbs/inittestdb.ora

testdb.__db_cache_size=184549376
testdb.__java_pool_size=4194304
testdb.__large_pool_size=4194304
testdb.__shared_pool_size=88080384
testdb.__streams_pool_size=0
*.audit_file_dest='/home/oracle/admin/testdb/adump'
*.background_dump_dest='/home/oracle/admin/testdb/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/home/oracle/disk1/control01.ctl',
                '/home/oracle/disk2/control02.ctl',
                '/home/oracle/disk3/control03.ctl'
*.core_dump_dest='/home/oracle/admin/testdb/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='testdb'
*.db_recovery_file_dest='/home/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdbXDB)'
*.job_queue_processes=10
*.local_listener='LISTENER_TESTDB'
*.open_cursors=300
*.pga_aggregate_target=94371840
"~/product/10g/dbs/inittestdb.ora" 29L, 1038C written

#연습문제와 같이 구성하기 위해 각 디스크 디렉토리 생성(디스크 추가, 마운트 및 재구성은 다음 연습문제에서...)
SQL> !mkdir -p /home/oracle/disk1/ /home/oracle/disk2/ /home/oracle/disk3/ /home/oracle/disk4/ /home/oracle/disk5/

SQL> !cp /home/oracle/oradata/testdb/control01.ctl /home/oracle/disk1/control01.ctl

SQL> !cp /home/oracle/oradata/testdb/control01.ctl /home/oracle/disk2/control02.ctl

SQL> !cp /home/oracle/oradata/testdb/control01.ctl /home/oracle/disk3/control03.ctl

#데이터베이스 재 구성을 위해 mount 단계로 startup
SQL> startup mount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              96470608 bytes
Database Buffers          184549376 bytes
Redo Buffers                2973696 bytes
Database mounted.
#각 Data file을 연습문제 내용과 같이 해당 디렉토리로 이동
SQL> !mv /home/oracle/oradata/testdb/system01.dbf /home/oracle/disk3/system01.dbf

SQL> !mv /home/oracle/oradata/testdb/sysaux01.dbf /home/oracle/disk4/sysaux01.dbf

SQL> !mv /home/oracle/oradata/testdb/undotbs01.dbf /home/oracle/disk4/undotbs01.dbf

SQL> !mv /home/oracle/oradata/testdb/users01.dbf /home/oracle/disk5/users01.dbf

SQL> !mv /home/oracle/oradata/testdb/example01.dbf /home/oracle/disk5/example01.dbf

#Control file에 이동시킨 각 Data file의 경로를 새로 지정
SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/system01.dbf'
  3  to '/home/oracle/disk3/system01.dbf';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/sysaux01.dbf'
  3  to '/home/oracle/disk4/sysaux01.dbf';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/undotbs01.dbf'
  3  to '/home/oracle/disk4/undotbs01.dbf';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/users01.dbf'
  3  to '/home/oracle/disk5/users01.dbf';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/example01.dbf'
  3  to '/home/oracle/disk5/example01.dbf';

Database altered.

#리두 로그 그룹 1, 2, 3을 재 구성을 위한 디렉토리로 이동
#각 그룹당 멤버가 한개이기 때문에 *_a.log 파일명으로 이동
SQL> !cp /home/oracle/oradata/testdb/redo01.log /home/oracle/disk1/redo01_a.log

SQL> !cp /home/oracle/oradata/testdb/redo02.log /home/oracle/disk1/redo02_a.log

SQL> !cp /home/oracle/oradata/testdb/redo03.log /home/oracle/disk1/redo03_a.log

#Control file에 이동시킨 각 리두 로그 그룹의 경로를 새로 지정
SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/redo01.log'
  3  to '/home/oracle/disk1/redo01_a.log';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/redo02.log'
  3  to '/home/oracle/disk1/redo02_a.log';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/redo03.log'
  3  to '/home/oracle/disk1/redo03_a.log';

Database altered.

SQL> alter database open;

Database altered.

#연습문제 내용과 같이 리두 로그 그룹 및 멤버를 구성하기 위해 각 그룹당 멤버 한 개씩 추가
SQL> alter database add logfile member
  2  '/home/oracle/disk2/redo01_b.log' to group 1;

Database altered.

SQL> alter database add logfile member
  2  '/home/oracle/disk2/redo02_b.log' to group 2;

Database altered.

SQL> alter database add logfile member
  2  '/home/oracle/disk2/redo03_b.log' to group 3;

Database altered.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------
/home/oracle/disk1/control01.ctl
/home/oracle/disk2/control02.ctl

SQL> select tablespace_name, bytes, file_name from dba_data_files;

TABLESPACE_NAME           BYTES FILE_NAME
-------------------- ---------- --------------------------------------------------
USERS                   5242880 /home/oracle/disk5/users01.dbf
SYSAUX                251658240 /home/oracle/disk4/sysaux01.dbf
UNDOTBS1               36700160 /home/oracle/disk4/undotbs01.dbf
SYSTEM                503316480 /home/oracle/disk3/system01.dbf
EXAMPLE               104857600 /home/oracle/disk5/example01.dbf

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;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/disk1/redo01_a.log                            50 NO  CURRENT
         1 /home/oracle/disk2/redo01_b.log                            50 NO  CURRENT
         2 /home/oracle/disk1/redo02_a.log                            50 NO  INACTIVE
         2 /home/oracle/disk2/redo02_b.log                            50 NO  INACTIVE
         3 /home/oracle/disk1/redo03_a.log                            50 NO  INACTIVE
         3 /home/oracle/disk2/redo03_b.log                            50 NO  INACTIVE

6 rows selected.

SQL>

저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 연습문제' 카테고리의 다른 글

Complete Recovery 연습 문제  (0) 2010.03.09
No Archive log mode Recovery 연습문제  (0) 2010.03.09
종합 연습문제 (디스크 추가, Controlfile, Redo log file, Tablespace)  (0) 2010.02.28
Controlfile, Redo log files 재구성 #2  (0) 2010.02.25
Controlfile, Redo log files 재구성 #1  (0) 2010.02.25

티스토리툴바