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

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

Redo log group 변경 두 번째! (3그룹 2멤버)

Oracle 10g/10g - 실습
posted by I유령I 2010. 2. 24. 23:31

리두 로그 그룹을 아래와 같이 구성해본다.
　　　　　group1　　　group2　　　group3
Disk1　　member1　　member2　　member3
--------------------------------------------
Disk2　　member1　　member2　　member3


#현재 리두 로그 그룹 및 멤버와 상태를 확인한다.
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/log/redo01.log                  5 NO  CURRENT
         2 /home/oracle/oradata/testdb/log/redo02.log                  5 NO  INACTIVE
         3 /home/oracle/oradata/testdb/log/redo03.log                  5 NO  INACTIVE

#위와 같이 리두 로그 그룹을 구성하기 위한 임시 리두 로그 그룹 4, 5를 추가한다.
SQL> alter database add logfile group 4
  2  '/home/oracle/oradata/testdb/log/redo04.log' size 5m;

Database altered.

SQL> alter database add logfile group 5
  2  '/home/oracle/oradata/testdb/log/redo05.log' size 5m;

Database 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/oradata/testdb/log/redo01.log                  5 NO  CURRENT
         2 /home/oracle/oradata/testdb/log/redo02.log                  5 NO  INACTIVE
         3 /home/oracle/oradata/testdb/log/redo03.log                  5 NO  INACTIVE
         4 /home/oracle/oradata/testdb/log/redo04.log                  5 YES UNUSED
         5 /home/oracle/oradata/testdb/log/redo05.log                  5 YES UNUSED

#리두 로그 그룹 1, 2, 3을 삭제하기 위해 CURRENT를 리두 로그 그룹 4, 5 둘 중 하나로 이동시킨다.
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

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/oradata/testdb/log/redo01.log                  5 NO  CURRENT
         2 /home/oracle/oradata/testdb/log/redo02.log                  5 NO  ACTIVE
         3 /home/oracle/oradata/testdb/log/redo03.log                  5 NO  ACTIVE
         4 /home/oracle/oradata/testdb/log/redo04.log                  5 NO  INACTIVE
         5 /home/oracle/oradata/testdb/log/redo05.log                  5 NO  INACTIVE

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/oradata/testdb/log/redo01.log                  5 NO  ACTIVE
         2 /home/oracle/oradata/testdb/log/redo02.log                  5 NO  ACTIVE
         3 /home/oracle/oradata/testdb/log/redo03.log                  5 NO  ACTIVE
         4 /home/oracle/oradata/testdb/log/redo04.log                  5 NO  CURRENT
         5 /home/oracle/oradata/testdb/log/redo05.log                  5 NO  INACTIVE

#리두 로그 그룹 1, 2, 3 삭제를 위해 강제로 체크포인트를 발생시켜 INACTIVE 상태로 만든다.
SQL> alter system checkpoint;

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/oradata/testdb/log/redo01.log                  5 NO  INACTIVE
         2 /home/oracle/oradata/testdb/log/redo02.log                  5 NO  INACTIVE
         3 /home/oracle/oradata/testdb/log/redo03.log                  5 NO  INACTIVE
         4 /home/oracle/oradata/testdb/log/redo04.log                  5 NO  CURRENT
         5 /home/oracle/oradata/testdb/log/redo05.log                  5 NO  INACTIVE

#리두 로그 그룹 1, 2, 3을 재구성하기 위해 삭제한다.
SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;

Database 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
---------- -------------------------------------------------- ---------- --- ----------------
         4 /home/oracle/oradata/testdb/log/redo04.log                  5 NO  CURRENT
         5 /home/oracle/oradata/testdb/log/redo05.log                  5 NO  INACTIVE

#재구성할 디렉토리를 생성해준다. (실무에서는 새로 추가한 디스크의 경로를 입력하면 된다.)
SQL> !mkdir -p $ORACLE_BASE/disk1/

SQL> !mkdir -p $ORACLE_BASE/disk2/

#리두 로그 그룹 1의 멤버 2개를 각각의 디스크에 할당하고 생성한다.
SQL> alter database add logfile group 1
  2  ( '$ORACLE_BASE/disk1/redo01a.rdo',
  3    '$ORACLE_BASE/disk2/redo01b.rdo')
  4  size 5m;

Database altered.

#리두 로그 그룹 2의 멤버 2개를 각각의 디스크에 할당하고 생성한다.
SQL> alter database add logfile group 2
  2  ( '$ORACLE_BASE/disk1/redo02a.rdo',
  3    '$ORACLE_BASE/disk2/redo02b.rdo')
  4  size 5m;

Database altered.

#리두 로그 그룹 3의 멤버 2개를 각각의 디스크에 할당하고 생성한다.
SQL> alter database add logfile group 3
  2  ( '$ORACLE_BASE/disk1/redo03a.rdo',
  3    '$ORACLE_BASE/disk2/redo03b.rdo')
  4  size 5m;

Database altered.

#리두 로그 그룹 1, 2, 3은 각각 멤버 2개로 구성되어 있으며, 각 멤버는 안전성을 위해 disk1과 disk2로 나뉘어 사용된다.
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/redo01a.rdo                              5 YES UNUSED
         1 /home/oracle/disk2/redo01b.rdo                              5 YES UNUSED
         2 /home/oracle/disk1/redo02a.rdo                              5 YES UNUSED
         2 /home/oracle/disk2/redo02b.rdo                              5 YES UNUSED
         3 /home/oracle/disk1/redo03a.rdo                              5 YES UNUSED
         3 /home/oracle/disk2/redo03b.rdo                              5 YES UNUSED
         4 /home/oracle/oradata/testdb/log/redo04.log                  5 NO  CURRENT
         5 /home/oracle/oradata/testdb/log/redo05.log                  5 NO  INACTIVE

8 rows selected.

#임시 리두 로그 그룹 4, 5를 삭제하기 위해 강제로 로그 스위치를 발생시킨다.
SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

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/disk1/redo01a.rdo                              5 NO  CURRENT
         1 /home/oracle/disk2/redo01b.rdo                              5 NO  CURRENT
         2 /home/oracle/disk1/redo02a.rdo                              5 YES UNUSED
         2 /home/oracle/disk2/redo02b.rdo                              5 YES UNUSED
         3 /home/oracle/disk1/redo03a.rdo                              5 YES UNUSED
         3 /home/oracle/disk2/redo03b.rdo                              5 YES UNUSED
         4 /home/oracle/oradata/testdb/log/redo04.log                  5 NO  INACTIVE
         5 /home/oracle/oradata/testdb/log/redo05.log                  5 NO  INACTIVE

8 rows selected.

#임시로 생성한 리두 로그 그룹 4, 5를 삭제한다.
SQL> alter database drop logfile group 4;

Database altered.

SQL> alter database drop logfile group 5;

Database altered.

#처음 의도한 바와 같이 3개의 그룹에 각각 2개의 멤버가 존재하며 각각의 멤버는 서로 다른 디스크에 할당된 것을 확인할 수 있다.
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/redo01a.rdo                              5 NO  CURRENT
         1 /home/oracle/disk2/redo01b.rdo                              5 NO  CURRENT
         2 /home/oracle/disk1/redo02a.rdo                              5 YES UNUSED
         2 /home/oracle/disk2/redo02b.rdo                              5 YES UNUSED
         3 /home/oracle/disk1/redo03a.rdo                              5 YES UNUSED
         3 /home/oracle/disk2/redo03b.rdo                              5 YES UNUSED

6 rows selected.

SQL>

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

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

Tablespace 실습 #3  (0) 2010.02.27
Tablespace 실습 #2  (0) 2010.02.27
Tablespace 실습 #1  (0) 2010.02.27
Redo log group 변경 (디렉토리, 사이즈)  (0) 2010.02.24
Redo log group & member 추가/삭제  (0) 2010.02.24

Redo log group 변경 (디렉토리, 사이즈)

Oracle 10g/10g - 실습
posted by I유령I 2010. 2. 24. 21:28

리두 로그 그룹 추가/삭제를 통해 디렉토리 및 사이즈를 변경해본다.

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  CURRENT
         2 /home/oracle/oradata/testdb/redo02.log                     50 NO  INACTIVE
         3 /home/oracle/oradata/testdb/redo03.log                     50 NO  INACTIVE

#새로운 리두 로그 그룹 4, 5를 추가한다.
#리두 로그 그룹 1, 2, 3을 삭제하기 위해 리두 로그 그룹은 최소 2개가 존재해야 한다.
SQL> alter database add logfile group 4
  2  '/home/oracle/oradata/testdb/redo04.log' size 5m;

Database altered.

SQL> alter database add logfile group 5
  2  '/home/oracle/oradata/testdb/redo05.log' size 5m;

Database 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/oradata/testdb/redo01.log                     50 NO  CURRENT
         2 /home/oracle/oradata/testdb/redo02.log                     50 NO  INACTIVE
         3 /home/oracle/oradata/testdb/redo03.log                     50 NO  INACTIVE
         4 /home/oracle/oradata/testdb/redo04.log                      5 YES UNUSED
         5 /home/oracle/oradata/testdb/redo05.log                      5 YES UNUSED

#리두 로그 그룹 4, 5를 추가한 후 CURRENT를 새로 추가한 리두 로그 그룹으로 변경한다.
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

#리두 로그 그룹 1, 2, 3을 삭제하기 위해 수동으로 checkpoint를 발생시킨다.
SQL> alter system checkpoint;

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/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  INACTIVE
         4 /home/oracle/oradata/testdb/redo04.log                      5 NO  INACTIVE
         5 /home/oracle/oradata/testdb/redo05.log                      5 NO  CURRENT

#리두 로그 그룹 5를 CURRENT 상태로 만든 후 리두 로그 그룹 1, 2, 3을 삭제한다.
SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;

Database 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
---------- -------------------------------------------------- ---------- --- ----------------
         4 /home/oracle/oradata/testdb/redo04.log                      5 NO  INACTIVE
         5 /home/oracle/oradata/testdb/redo05.log                      5 NO  CURRENT

#새로운 리두 로그 그룹 1, 2, 3을 추가하기 위한 새로운 디렉토리를 생성한다.
#디렉토리를 생성하지 않고 추가할 경우 디렉토리 문제로 에러가 발생한다.
SQL> !mkdir -p /home/oracle/oradata/testdb/log

#새로운 디렉토리에 리두 로그 그룹 1, 2, 3을 추가한다.
SQL> alter database add logfile group 1
  2  '/home/oracle/oradata/testdb/log/redo01.log' size 5m;

Database altered.

SQL> alter database add logfile group 2
  2  '/home/oracle/oradata/testdb/log/redo02.log' size 5m;

Database altered.

SQL> alter database add logfile group 3
  2  '/home/oracle/oradata/testdb/log/redo03.log' size 5m;

Database 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/oradata/testdb/log/redo01.log                  5 YES UNUSED
         2 /home/oracle/oradata/testdb/log/redo02.log                  5 YES UNUSED
         3 /home/oracle/oradata/testdb/log/redo03.log                  5 YES UNUSED
         4 /home/oracle/oradata/testdb/redo04.log                      5 NO  INACTIVE
         5 /home/oracle/oradata/testdb/redo05.log                      5 NO  CURRENT

#기존 리두 로그 그룹 4, 5를 삭제하기 위해 강제로 로그 스위치를 발생시킨다.
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/oradata/testdb/log/redo01.log                  5 NO  ACTIVE
         2 /home/oracle/oradata/testdb/log/redo02.log                  5 NO  ACTIVE
         3 /home/oracle/oradata/testdb/log/redo03.log                  5 NO  ACTIVE
         4 /home/oracle/oradata/testdb/redo04.log                      5 NO  CURRENT
         5 /home/oracle/oradata/testdb/redo05.log                      5 NO  ACTIVE

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/oradata/testdb/log/redo01.log                  5 NO  ACTIVE
         2 /home/oracle/oradata/testdb/log/redo02.log                  5 NO  ACTIVE
         3 /home/oracle/oradata/testdb/log/redo03.log                  5 NO  ACTIVE
         4 /home/oracle/oradata/testdb/redo04.log                      5 NO  ACTIVE
         5 /home/oracle/oradata/testdb/redo05.log                      5 NO  CURRENT

#강제 로그 스위치 발생 경과로 보아 UNUSED에서 바로 CURRENT가 되지 않는다.
#CURRENT의 순서는 4 -> 5 -> 4 -> 1 순이며 세번째 4가 CURRENT일때 1, 2, 3의 상태는 ACTIVE가 된다.
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/oradata/testdb/log/redo01.log                  5 NO  CURRENT
         2 /home/oracle/oradata/testdb/log/redo02.log                  5 NO  ACTIVE
         3 /home/oracle/oradata/testdb/log/redo03.log                  5 NO  ACTIVE
         4 /home/oracle/oradata/testdb/redo04.log                      5 NO  ACTIVE
         5 /home/oracle/oradata/testdb/redo05.log                      5 NO  ACTIVE

#기존 리두 로그 그룹 4, 5를 삭제하기 위해 강제로 checkpoint를 발생시킨다.
SQL> alter system checkpoint;

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/oradata/testdb/log/redo01.log                  5 NO  CURRENT
         2 /home/oracle/oradata/testdb/log/redo02.log                  5 NO  INACTIVE
         3 /home/oracle/oradata/testdb/log/redo03.log                  5 NO  INACTIVE
         4 /home/oracle/oradata/testdb/redo04.log                      5 NO  INACTIVE
         5 /home/oracle/oradata/testdb/redo05.log                      5 NO  INACTIVE

#기존 리두 로그 그룹 4, 5를 삭제한다.
SQL> alter database drop logfile group 4;

Database altered.

SQL> alter database drop logfile group 5;

Database 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/oradata/testdb/log/redo01.log                  5 NO  CURRENT
         2 /home/oracle/oradata/testdb/log/redo02.log                  5 NO  INACTIVE
         3 /home/oracle/oradata/testdb/log/redo03.log                  5 NO  INACTIVE

#SQL에서 리두 로그 그룹을 DROP 하더라도 실제 OS 파일은 삭제되지 않는다.
#따라서 확인한 후 해당 리두 로그 파일을 삭제한다.
SQL> !ls /home/oracle/oradata/testdb/
control01.ctl  example01.dbf  redo02.log  redo05.log    temp01.dbf
control02.ctl  log            redo03.log  sysaux01.dbf  undotbs01.dbf
control03.ctl  redo01.log     redo04.log  system01.dbf  users01.dbf

SQL> !rm -rf /home/oracle/oradata/testdb/redo*.log

SQL> !ls /home/oracle/oradata/testdb/
control01.ctl  control03.ctl  log           system01.dbf  undotbs01.dbf
control02.ctl  example01.dbf  sysaux01.dbf  temp01.dbf    users01.dbf

SQL>

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

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

Tablespace 실습 #3  (0) 2010.02.27
Tablespace 실습 #2  (0) 2010.02.27
Tablespace 실습 #1  (0) 2010.02.27
Redo log group 변경 두 번째! (3그룹 2멤버)  (0) 2010.02.24
Redo log group & member 추가/삭제  (0) 2010.02.24

Redo log group & member 추가/삭제

Oracle 10g/10g - 실습
posted by I유령I 2010. 2. 24. 21:22

[oracle@ghost sql]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 24 20:13:18 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              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.

#Redo log files 상태 조회
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

#리두 로그 그룹 4 추가
SQL> alter database add logfile group 4
  2  '/home/oracle/oradata/testdb/redo04.log' size 10m;

Database 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/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
         4 /home/oracle/oradata/testdb/redo04.log                     10 YES UNUSED

#리두 로그 그룹 4에 새 멤버 추가
SQL> alter database add logfile member
  2  '/home/oracle/oradata/testdb/redo04_b.log' to group 4;

Database 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/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
         4 /home/oracle/oradata/testdb/redo04.log                     10 YES UNUSED
         4 /home/oracle/oradata/testdb/redo04_b.log                   10 YES UNUSED

#다음 리두 로그 그룹을 CURRENT 상태로 만들기 위해 수동으로 로그 스위치를 발생시킨다.
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/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  ACTIVE
         4 /home/oracle/oradata/testdb/redo04.log                     10 NO  CURRENT
         4 /home/oracle/oradata/testdb/redo04_b.log                   10 NO  CURRENT

#리두 로그 그룹 4의 멤버 삭제시 에러 발생 예제
#현재 사용중(CURRENT 상태)이기 때문에 멤버 삭제가 불가능하다.
SQL> alter database drop logfile member
  2  '/home/oracle/oradata/testdb/redo04_b.log';
alter database drop logfile member
*
ERROR at line 1:
ORA-01609: log 4 is the current log for thread 1 - cannot drop members
ORA-00312: online log 4 thread 1: '/home/oracle/oradata/testdb/redo04.log'
ORA-00312: online log 4 thread 1: '/home/oracle/oradata/testdb/redo04_b.log'

#리두 로그 그룹 4의 그룹 및 멤버를 삭제하기 위해 리두 로그 그룹 1로 로그 스위치를 발생시킨다.
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/oradata/testdb/redo01.log                     50 NO  CURRENT
         2 /home/oracle/oradata/testdb/redo02.log                     50 NO  INACTIVE
         3 /home/oracle/oradata/testdb/redo03.log                     50 NO  ACTIVE
         4 /home/oracle/oradata/testdb/redo04.log                     10 NO  ACTIVE
         4 /home/oracle/oradata/testdb/redo04_b.log                   10 NO  ACTIVE

#리두 로그 그룹 4는 멤버가 2개이기 때문에 ACTIVE 상태에 관계 없이 1개의 멤버는 삭제가 가능하다.
SQL> alter database drop logfile member
  2  '/home/oracle/oradata/testdb/redo04_b.log';

Database altered.

#리두 로그 그룹 4 삭제시 에러 발생 예제
#리두 로그 그룹 4가 ACTIVE 상태이기 때문에 삭제가 불가능하다.
SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-01624: log 4 needed for crash recovery of instance testdb (thread 1)
ORA-00312: online log 4 thread 1: '/home/oracle/oradata/testdb/redo04.log'

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  CURRENT
         2 /home/oracle/oradata/testdb/redo02.log                     50 NO  INACTIVE
         3 /home/oracle/oradata/testdb/redo03.log                     50 NO  ACTIVE
         4 /home/oracle/oradata/testdb/redo04.log                     10 NO  ACTIVE

#리두 로그 그룹 4의 상태를 ACTIVE에서 INACTIVE 상태로 변경한다.
#CURRENT 로그 그룹을 제외한 모든 그룹 및 멤버는 INACTIVE 상태가 된다.
SQL> alter system checkpoint;

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/oradata/testdb/redo01.log                     50 NO  CURRENT
         2 /home/oracle/oradata/testdb/redo02.log                     50 NO  INACTIVE
         3 /home/oracle/oradata/testdb/redo03.log                     50 NO  INACTIVE
         4 /home/oracle/oradata/testdb/redo04.log                     10 NO  INACTIVE

#리두 로그 그룹 4는 INACTIVE 상태이기 때문에 에러가 발생하지 않고 삭제가 가능하다.
SQL> alter database drop logfile group 4;

Database 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/oradata/testdb/redo01.log                     50 NO  CURRENT
         2 /home/oracle/oradata/testdb/redo02.log                     50 NO  INACTIVE
         3 /home/oracle/oradata/testdb/redo03.log                     50 NO  INACTIVE

SQL>

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

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

Tablespace 실습 #3  (0) 2010.02.27
Tablespace 실습 #2  (0) 2010.02.27
Tablespace 실습 #1  (0) 2010.02.27
Redo log group 변경 두 번째! (3그룹 2멤버)  (0) 2010.02.24
Redo log group 변경 (디렉토리, 사이즈)  (0) 2010.02.24

Redo log group & member 관련 명령어

Oracle 10g/10g - Admin I
posted by I유령I 2010. 2. 24. 12:39

Redo log group은 최소 2개, member 는 그룹당 최소 1개이다.


■ 수동 로그 스위치
SQL> alter system switch logfile;

■ 수동 checkpoint
SQL> alter system checkpoint;

■ 리두 로그 그룹 추가
SQL> alter database add logfile group 그룹번호
  2  '경로/파일명.log' size 크기;

■ 리두 로그 그룹별 멤버 추가
SQL> alter database add logfile member
  2  '경로/파일명.log' to group 그룹번호;

■ 리두 로그 그룹 삭제
SQL> alter database drop logfile group 그룹번호;

■ 리두 로그 그룹의 멤버 삭제
SQL> alter database drop logfile member
  2  '경로/파일명_b.log';

■ log file 상태 조회 (그룹번호, 멤버, 용량, 상태)
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  CURRENT
         2 /home/oracle/oradata/testdb/redo02.log                     50 NO  INACTIVE
         3 /home/oracle/oradata/testdb/redo03.log                     50 NO  INACTIVE

SQL>

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

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

프로파일 & 유저 관리 & 권한 관리  (0) 2010.03.05
Constraints (제약 조건)  (0) 2010.03.05
Index (인덱스)  (0) 2010.03.05
Table (테이블)  (0) 2010.03.03
Tablespace 관련 명령어  (0) 2010.02.27

티스토리툴바