'Redo Log File' 태그의 글 목록

일	월	화	수	목	금	토
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30
31

'Redo Log File'에 해당되는 글 6건

2010.03.12 Redo log file Recovery
2010.03.08 Restore & Recovery
2010.03.08 Cold Backup & Hot Backup
2010.03.08 Backup
2010.02.28 종합 연습문제 (디스크 추가, Controlfile, Redo log file, Tablespace)
2010.02.27 종합 연습문제 (Controlfile, Redo log file, Tablespace)

Redo log file Recovery

Oracle 10g/10g - 실습

posted by I유령I 2010. 3. 12. 11:16

1. 실습 환경 설정

실습을 위해 아래와 같이 Redo log group 및 member 을 다중화 한다.

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/disk4/redo01_a.log 5 NO CURRENT

1 /home/oracle/disk5/redo01_b.log 5 NO CURRENT

2 /home/oracle/disk4/redo02_a.log 5 YES UNUSED

2 /home/oracle/disk5/redo02_b.log 5 YES UNUSED

3 /home/oracle/disk4/redo03_a.log 5 YES UNUSED

3 /home/oracle/disk5/redo03_b.log 5 YES UNUSED

6 rows selected.

2. Group의 한 Member 1개 장애 (Active, Inactive 공통)

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/disk4/redo01_a.log 5 NO CURRENT

1 /home/oracle/disk5/redo01_b.log 5 NO CURRENT

2 /home/oracle/disk4/redo02_a.log 5 YES UNUSED

2 /home/oracle/disk5/redo02_b.log 5 YES UNUSED

3 /home/oracle/disk4/redo03_a.log 5 YES UNUSED

3 /home/oracle/disk5/redo03_b.log 5 YES UNUSED

6 rows selected.

SQL> !rm -rf /home/oracle/disk5/redo01_b.log

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

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/disk4/redo01_a.log 5 YES INACTIVE

1 /home/oracle/disk5/redo01_b.log 5 YES INACTIVE

2 /home/oracle/disk4/redo02_a.log 5 NO CURRENT

2 /home/oracle/disk5/redo02_b.log 5 NO CURRENT

3 /home/oracle/disk4/redo03_a.log 5 YES INACTIVE

3 /home/oracle/disk5/redo03_b.log 5 YES INACTIVE

6 rows selected.

SQL> alter database drop logfile member '/home/oracle/disk5/redo01_b.log';

Database altered.

SQL> alter database add logfile member '/home/oracle/disk5/redo01_b.log' to group 1;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

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/disk4/redo01_a.log 5 NO CURRENT

1 /home/oracle/disk5/redo01_b.log 5 NO CURRENT

2 /home/oracle/disk4/redo02_a.log 5 YES ACTIVE

2 /home/oracle/disk5/redo02_b.log 5 YES ACTIVE

3 /home/oracle/disk4/redo03_a.log 5 YES ACTIVE

3 /home/oracle/disk5/redo03_b.log 5 YES ACTIVE

6 rows selected.

SQL>

※ alert_testdb.log 내용

Fri Mar 12 11:26:22 2010

Thread 1 advanced to log sequence 7

Current log# 2 seq# 7 mem# 0: /home/oracle/disk4/redo02_a.log

Current log# 2 seq# 7 mem# 1: /home/oracle/disk5/redo02_b.log

Fri Mar 12 11:26:22 2010

Errors in file /home/oracle/admin/testdb/bdump/testdb_arc0_3194.trc:

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: '/home/oracle/disk5/redo01_b.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

Fri Mar 12 11:26:22 2010

Errors in file /home/oracle/admin/testdb/bdump/testdb_arc0_3194.trc:

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: '/home/oracle/disk5/redo01_b.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

Thread 1 advanced to log sequence 8

Current log# 3 seq# 8 mem# 0: /home/oracle/disk4/redo03_a.log

Current log# 3 seq# 8 mem# 1: /home/oracle/disk5/redo03_b.log

Thread 1 cannot allocate new log, sequence 9

Checkpoint not complete

Current log# 3 seq# 8 mem# 0: /home/oracle/disk4/redo03_a.log

Current log# 3 seq# 8 mem# 1: /home/oracle/disk5/redo03_b.log

Fri Mar 12 11:26:32 2010

Thread 1 advanced to log sequence 9

Current log# 1 seq# 9 mem# 0: /home/oracle/disk4/redo01_a.log

Current log# 1 seq# 9 mem# 1: /home/oracle/disk5/redo01_b.log

Fri Mar 12 11:27:18 2010

Thread 1 advanced to log sequence 10

Current log# 2 seq# 10 mem# 0: /home/oracle/disk4/redo02_a.log

Current log# 2 seq# 10 mem# 1: /home/oracle/disk5/redo02_b.log

Fri Mar 12 11:27:18 2010

Errors in file /home/oracle/admin/testdb/bdump/testdb_arc1_3196.trc:

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: '/home/oracle/disk5/redo01_b.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

Fri Mar 12 11:27:18 2010

Errors in file /home/oracle/admin/testdb/bdump/testdb_arc1_3196.trc:

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: '/home/oracle/disk5/redo01_b.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

Thread 1 advanced to log sequence 11

Current log# 3 seq# 11 mem# 0: /home/oracle/disk4/redo03_a.log

Current log# 3 seq# 11 mem# 1: /home/oracle/disk5/redo03_b.log

Thread 1 cannot allocate new log, sequence 12

Checkpoint not complete

Current log# 3 seq# 11 mem# 0: /home/oracle/disk4/redo03_a.log

Current log# 3 seq# 11 mem# 1: /home/oracle/disk5/redo03_b.log

Thread 1 advanced to log sequence 12

Current log# 1 seq# 12 mem# 0: /home/oracle/disk4/redo01_a.log

Current log# 1 seq# 12 mem# 1: /home/oracle/disk5/redo01_b.log

Fri Mar 12 11:27:30 2010

Thread 1 advanced to log sequence 13

Current log# 2 seq# 13 mem# 0: /home/oracle/disk4/redo02_a.log

Current log# 2 seq# 13 mem# 1: /home/oracle/disk5/redo02_b.log

Fri Mar 12 11:27:30 2010

Errors in file /home/oracle/admin/testdb/bdump/testdb_arc0_3194.trc:

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: '/home/oracle/disk5/redo01_b.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

Fri Mar 12 11:27:30 2010

Errors in file /home/oracle/admin/testdb/bdump/testdb_arc0_3194.trc:

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: '/home/oracle/disk5/redo01_b.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

Fri Mar 12 11:27:30 2010

Expanded controlfile section 11 from 28 to 205 records

Requested to grow by 177 records; added 7 blocks of records

Fri Mar 12 11:27:52 2010

alter database drop logfile member '/home/oracle/disk5/redo01_b.log'

Fri Mar 12 11:27:52 2010

Completed: alter database drop logfile member '/home/oracle/disk5/redo01_b.log'

Fri Mar 12 11:28:12 2010

alter database add logfile member '/home/oracle/disk5/redo01_b.log' to group 1

Fri Mar 12 11:28:12 2010

Completed: alter database add logfile member '/home/oracle/disk5/redo01_b.log' to group 1

Fri Mar 12 11:28:27 2010

Thread 1 advanced to log sequence 14

Current log# 3 seq# 14 mem# 0: /home/oracle/disk4/redo03_a.log

Current log# 3 seq# 14 mem# 1: /home/oracle/disk5/redo03_b.log

Thread 1 advanced to log sequence 15

Current log# 1 seq# 15 mem# 0: /home/oracle/disk4/redo01_a.log

Current log# 1 seq# 15 mem# 1: /home/oracle/disk5/redo01_b.log

3. Inactive한 Group 장애

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/disk4/redo01_a.log 5 NO CURRENT

1 /home/oracle/disk5/redo01_b.log 5 NO CURRENT

2 /home/oracle/disk4/redo02_a.log 5 YES INACTIVE

2 /home/oracle/disk5/redo02_b.log 5 YES INACTIVE

3 /home/oracle/disk4/redo03_a.log 5 YES INACTIVE

3 /home/oracle/disk5/redo03_b.log 5 YES INACTIVE

6 rows selected.

SQL> !rm -rf /home/oracle/disk4/redo03_a.log

SQL> !rm -rf /home/oracle/disk5/redo03_b.log

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

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/disk4/redo01_a.log 5 NO CURRENT

1 /home/oracle/disk5/redo01_b.log 5 NO CURRENT

2 /home/oracle/disk4/redo02_a.log 5 YES INACTIVE

2 /home/oracle/disk5/redo02_b.log 5 YES INACTIVE

3 /home/oracle/disk4/redo03_a.log 5 NO INACTIVE

3 /home/oracle/disk5/redo03_b.log 5 NO INACTIVE

6 rows selected.

SQL> alter database drop logfile group 3;

alter database drop logfile group 3

ERROR at line 1:

ORA-00350: log 3 of instance testdb (thread 1) needs to be archived

ORA-00312: online log 3 thread 1: '/home/oracle/disk4/redo03_a.log'

ORA-00312: online log 3 thread 1: '/home/oracle/disk5/redo03_b.log'

SQL> alter database clear unarchived logfile group 3;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

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/disk4/redo01_a.log 5 NO CURRENT

1 /home/oracle/disk5/redo01_b.log 5 NO CURRENT

2 /home/oracle/disk4/redo02_a.log 5 NO INACTIVE

2 /home/oracle/disk5/redo02_b.log 5 NO INACTIVE

3 /home/oracle/disk4/redo03_a.log 5 NO INACTIVE

3 /home/oracle/disk5/redo03_b.log 5 NO INACTIVE

6 rows selected.

SQL>

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

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

Export / Import - Clone DB로 삭제된 데이터 복구하는 방법 (0)	2010.03.16
Control file 장애 (0)	2010.03.15
Incomplete Recovery - using backup controlfile (0)	2010.03.11
Incomplete Recovery - Time Base (0)	2010.03.10
Archive log mode Complete Recovery (0)	2010.03.09

Restore & Recovery

Oracle 10g/10g - Admin I

posted by I유령I 2010. 3. 8. 11:30

Restore : Backup 받았던 파일을 복원 시키는 것

Recovery : 복원된 파일을 Redo log file을 적용시켜서 복구하는 것

복구 원리

Recover 명령 -> Control file 정보 확인 -> Data file 정보 확인 -> Control file 과 Data file 정보가 다를 경우 Redo log file 또는 Archived log file의 정보를 적용시켜 복구를 완료한다.

복구 명령어

SQL> recover database; ※ mount 단계에서만 사용이 가능한 명령어

SQL> recover tablespace tablespace_name;

SQL> recover datafile '/home/oracle/oradata/testdb/system01.dbf';

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

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

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

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

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

Ghost's Oracle Database !

Category

Notice

Archive

My Link

Tag

Recent Comment

Recent Post

Recent Trackback

calendar

'Redo Log File'에 해당되는 글 6건

Redo log file Recovery

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

Restore & Recovery

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

Cold Backup & Hot Backup

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

Backup

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

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

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

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

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

티스토리툴바

Ghost's Oracle Database !

Category

Notice

Archive

My Link

Search

Tag

Recent Comment

Recent Post

Recent Trackback

calendar

'Redo Log File'에 해당되는 글 6건

Redo log file Recovery

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

Restore & Recovery

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

Cold Backup & Hot Backup

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

Backup

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

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

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

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

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

티스토리툴바