No Archive log mode Recovery 연습문제

Oracle 10g/10g - 연습문제
posted by I유령I 2010. 3. 9. 10:53
장애 상황
No archive log mode로 DB 운영 중 갑자기 데이터 파일이 삭제되면서 DB가 강제 종료되는 현상이 발생했다.
확인 결과 Data file, Control file, Redo log file이 저장되어있는 디스크 장애였다.
현재 Backup file은 아래와 같이 저장되어 있으며 긴급 복구를 해야하는 상황이다.
삭제된 파일은 users01.dbf 이다.

복구 경로 (아래의 경로에 각 파일들이 위치하도록 복구)
- Data file : /data/temp/data/*.dbf
- Redo log file : /data/temp/log/*.log
- Control file : /data/temp/control/*.ctl

[oracle@ghost]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 10 02:52:47 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.
#Data file, Control file, Redo log file이 저장되어 있는 디스크 장애이므로 모든 파일을 삭제한다.
SQL> !rm -rf /home/oracle/oradata/testdb/*

#디스크 장애가 발생하면서 DB가 강제 종료되는 상황을 만들기 위해 shutdown abort; 명령어를 사용한다.
SQL> shutdown abort;
ORACLE instance shut down.
#Control file이 삭제되어 아래와 같이 Control file 관련 에러가 발생하며 alert log를 확인하라는 에러 메시지가 출력된다.
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
ORA-00205: error in identifying control file, check alert log for more info

#복구를 위해 일단 shutdown 한다.
SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
#연습문제 내용과 같이 복구 경로를 지정하기 위해 디렉토리를 생성하고 각 디렉토리로 복사한다.
SQL> !mkdir -p /data/temp/data /data/temp/log /data/temp/control

SQL> !cp -av /data/backup/close/*.dbf /data/temp/data/
`/data/backup/close/example01.dbf' -> `/data/temp/data/example01.dbf'
`/data/backup/close/sysaux01.dbf' -> `/data/temp/data/sysaux01.dbf'
`/data/backup/close/system01.dbf' -> `/data/temp/data/system01.dbf'
`/data/backup/close/temp01.dbf' -> `/data/temp/data/temp01.dbf'
`/data/backup/close/undotbs01.dbf' -> `/data/temp/data/undotbs01.dbf'
`/data/backup/close/users01.dbf' -> `/data/temp/data/users01.dbf'

SQL> !cp -av /data/backup/close/*.log /data/temp/log/ 
`/data/backup/close/redo01.log' -> `/data/temp/log/redo01.log'
`/data/backup/close/redo02.log' -> `/data/temp/log/redo02.log'
`/data/backup/close/redo03.log' -> `/data/temp/log/redo03.log'

SQL> !cp -av /data/backup/close/*.ctl /data/temp/control/
`/data/backup/close/control01.ctl' -> `/data/temp/control/control01.ctl'
`/data/backup/close/control02.ctl' -> `/data/temp/control/control02.ctl'
`/data/backup/close/control03.ctl' -> `/data/temp/control/control03.ctl'

#Control file을 우선 복구하기 위해 Instance를 nomount 단계로 시작한다.
SQL> startup nomount;
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
#Server Parameter file에서 현재 복구할 Control file의 경로를 변경한다.
#기존 내용
#*.control_files='/home/oracle/oradata/testdb/control01.ctl',
#'/home/oracle/oradata/testdb/control02.ctl',
#'/home/oracle/oradata/testdb/control03.ctl'

#pfile 사용시
SQL> !vi /home/oracle/product/10g/dbs/inittestdb.ora
*.control_files='/data/temp/control/control01.ctl',
'/data/temp/control/control02.ctl',
'/data/temp/control/control03.ctl'

#spfile 사용시
SQL> alter system set control_files='/data/temp/control/control01.ctl',
  2  '/data/temp/control/control02.ctl',
  3  '/data/temp/control/control03.ctl'
  4  scope=spfile;

System altered.

#spfile의 변경된 내용을 적용하고 Data file, Redo log file의 복구를 위해 Instance를 재 시작한다.
 위해 Instance를 mount 단계로 재 시작한다.
SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup mount;
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.
#Data file, Redo log file을 복구하기 위해 Control file의 정보를 변경한다.
SQL> alter database rename        
  2  file '/home/oracle/oradata/testdb/system01.dbf'
  3  to '/data/temp/data/system01.dbf';

Database altered.

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

Database altered.

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

Database altered.

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

Database altered.

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

Database altered.

SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/redo01.log'
  3  to '/data/temp/log/redo01.log';

Database altered.

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

Database altered.

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

Database altered.

SQL> alter database open;

Database altered.

#복구 여부를 확인한다.
SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;

TABLESPACE_NAME              MB FILE_NAME
-------------------- ---------- --------------------------------------------------
USERS                         5 /data/temp/data/users01.dbf
SYSAUX                      240 /data/temp/data/sysaux01.dbf
UNDOTBS1                     35 /data/temp/data/undotbs01.dbf
SYSTEM                      480 /data/temp/data/system01.dbf
EXAMPLE                     100 /data/temp/data/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 a.group#, a.member;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /data/temp/log/redo01.log                                  50 NO  INACTIVE
         2 /data/temp/log/redo02.log                                  50 NO  INACTIVE
         3 /data/temp/log/redo03.log                                  50 NO  CURRENT

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

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

Complete Recovery (다른 경로에 응급 복구)  (0) 2010.03.10
Complete Recovery 연습 문제  (0) 2010.03.09
종합 연습문제 (디스크 추가, Controlfile, Redo log file, Tablespace)  (0) 2010.02.28
종합 연습문제 (Controlfile, Redo log file, Tablespace)  (0) 2010.02.27
Controlfile, Redo log files 재구성 #2  (0) 2010.02.25

No Archive log mode Recovery

Oracle 10g/10g - 실습
posted by I유령I 2010. 3. 8. 12:49
No Archive log mode Recovery #1

SQL> !rm -rf /home/oracle/oradata/testdb/users01.dbf 

SQL> alter tablespace users offline;

Tablespace altered.

SQL> alter tablespace users online;
alter tablespace users online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/home/oracle/oradata/testdb/users01.dbf'


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      OFFLINE   PERMANENT
EXAMPLE    ONLINE    PERMANENT

6 rows selected.

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
         5 /home/oracle/oradata/testdb/example01.dbf          NOT ACTIVE         2010-03-08:11:12:16

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

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

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

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> 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 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:10:54:39
         2 /home/oracle/oradata/testdb/undotbs01.dbf          NOT ACTIVE         2010-03-08:10:55:09
         3 /home/oracle/oradata/testdb/sysaux01.dbf           NOT ACTIVE         2010-03-08:10:54:58
         4 /home/oracle/oradata/testdb/users01.dbf            NOT ACTIVE         2010-03-08:10:55:11
         5 /home/oracle/oradata/testdb/example01.dbf          NOT ACTIVE         2010-03-08:10:55:11

SQL> 


No Archive log mode Recovery #2 (해당 Tablespace가 지워져도 괜찮을 경우에 한하여 사용한다.)
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 name format a50
SQL> select tablespace_name, status, contents from dba_tablespaces;                      

TABLESPACE_NAME                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 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:10:54:39
         2 /home/oracle/oradata/testdb/undotbs01.dbf          NOT ACTIVE         2010-03-08:10:55:09
         3 /home/oracle/oradata/testdb/sysaux01.dbf           NOT ACTIVE         2010-03-08:10:54:58
         4 /home/oracle/oradata/testdb/users01.dbf            NOT ACTIVE         2010-03-08:10:55:11
         5 /home/oracle/oradata/testdb/example01.dbf          NOT ACTIVE         2010-03-08:10:55:11

SQL> !rm /home/oracle/oradata/testdb/users01.dbf

SQL> shutdown abort;
ORACLE instance shut down.
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.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/home/oracle/oradata/testdb/users01.dbf'


SQL> alter database datafile '/home/oracle/oradata/testdb/users01.dbf' offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> select tablespace_name, status, contents from dba_tablespaces;

TABLESPACE_NAME                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 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:10:54:39
         2 /home/oracle/oradata/testdb/undotbs01.dbf          NOT ACTIVE         2010-03-08:10:55:09
         3 /home/oracle/oradata/testdb/sysaux01.dbf           NOT ACTIVE         2010-03-08:10:54:58
         5 /home/oracle/oradata/testdb/example01.dbf          NOT ACTIVE         2010-03-08:10:55:11

SQL> 


No Archive log mode Recovery #3
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> !rm -rf /home/oracle/oradata/testdb/*

SQL> !ls /home/oracle/oradata/testdb/

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !mkdir -p /data/temp 

SQL> !cp -av /data/close/*.dbf /data/temp/
cp: cannot stat `/data/close/*.dbf': 그런 파일이나 디렉토리가 없음

SQL> !cp -av /data/backup/close/*.dbf /data/temp/
`/data/backup/close/example01.dbf' -> `/data/temp/example01.dbf'
`/data/backup/close/sysaux01.dbf' -> `/data/temp/sysaux01.dbf'
`/data/backup/close/system01.dbf' -> `/data/temp/system01.dbf'
`/data/backup/close/temp01.dbf' -> `/data/temp/temp01.dbf'
`/data/backup/close/undotbs01.dbf' -> `/data/temp/undotbs01.dbf'
`/data/backup/close/users01.dbf' -> `/data/temp/users01.dbf'

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

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

SQL> startup mount;
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.
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> alter database rename
  2  file '/home/oracle/oradata/testdb/system01.dbf'
  3  to '/data/temp/system01.dbf';

Database altered.

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

Database altered.

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

Database altered.

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

Database altered.

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

Database altered.

SQL> alter database open;

Database altered.

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

TABLESPACE_NAME                        MB FILE_NAME
------------------------------ ---------- --------------------------------------------------
USERS                                     /data/temp/users01.dbf
SYSAUX                                240 /data/temp/sysaux01.dbf
UNDOTBS1                               35 /data/temp/undotbs01.dbf
SYSTEM                                480 /data/temp/system01.dbf
EXAMPLE                               100 /data/temp/example01.dbf

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

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

Incomplete Recovery - Time Base  (0) 2010.03.10
Archive log mode Complete Recovery  (0) 2010.03.09
Cold Backup & Hot Backup  (0) 2010.03.08
Archive log mode 설정 및 활성화/비활성화  (0) 2010.03.05
Privileges(권한) 관리  (0) 2010.03.05

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

Archive log mode 설정 및 활성화/비활성화

Oracle 10g/10g - 실습
posted by I유령I 2010. 3. 5. 12:32
■ Archive log mode 사용을 위한 설정
[oracle@ghost sql]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 5 12:10:55 2010

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

Connected to an idle instance.

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.

#Archive log의 기본 저장 영역은 $ORACLE_BASE/flash_recovery_area 이며, 사용 가능한 기본
#용량은 2GB 이다.
#flash_recovery_area는 archive log 뿐만 아니라 flashback log, rman 등 Backup & Recovery
#관련 데이터가 저장되어 Archive log mode를 활성화하여 사용할 경우 디스크 용량 부족으로 인해
#Database에 문제가 발생될 수 있다.
#디스크 용량 부족으로 인한 문제를 방지하기 위해 아래와 같이 Archive log가 저장될 저장 장소를
#별도로 지정한다.
#spfile 사용시
#log_archive_dest_1 디렉토리 지정
SQL> alter system set log_archive_dest_1='location=/data/arc1' scope=spfile;

System altered.

#log_archive_dest_2 디렉토리 지정
SQL> alter system set log_archive_dest_2='location=/data/arc2' scope=spfile;

System altered.

#log_archive_format 지정
SQL> alter system set log_archive_format='%s_%t_%r.arc' scope=spfile;

System altered.

#pfile 사용시
SQL> !vi $ORACLE_HOME/dbs/inittestdb.ora

*.log_archive_dest_1='location=/data/arc1'
*.log_archive_dest_2='location=/data/arc2'
*.log_archive_format='%s_%t_%r.arc'

SQL> 

#위에서 지정한 log_archive 디렉토리 및 포맷 적용을 위한 재 시작
SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

#Archive log mode 활성화를 위해 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.

#Archive log mode 상태 확인
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /data/arc2
Oldest online log sequence     2
Current log sequence           4

#Archive log mode를 활성화 한다.
SQL> alter database archivelog;

Database altered.

#Archive log mode 상태 재 확인
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /data/arc2
Oldest online log sequence     2
Next log sequence to archive   4
Current log sequence           4
SQL> alter database open;

Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
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.

#Archive log mode를 비활성화 한다.
SQL> alter database noarchivelog;

Database altered.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /data/arc2
Oldest online log sequence     2
Current log sequence           4
SQL> alter database open;

Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 
저작자표시 비영리 변경금지 (새창열림)

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

No Archive log mode Recovery  (0) 2010.03.08
Cold Backup & Hot Backup  (0) 2010.03.08
Privileges(권한) 관리  (0) 2010.03.05
Profile(프로파일) 생성과 관리  (0) 2010.03.05
User(유저) 생성과 관리  (0) 2010.03.05

sysdba 권한 로그인시 패스워드 지정

Oracle 10g/10g - Admin I
posted by I유령I 2010. 3. 5. 11:36
[oracle@ghost sql]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 5 11:35:00 2010

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

Connected to an idle instance.

SQL> exit
Disconnected
[oracle@ghost ~]$ vi $ORACLE_HOME/network/admin/sqlnet.ora

# sqlnet.ora Network Configuration File: /home/oracle/product/10g/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

SQLNET.AUTHENTICATION_SERVICES=(NONE)
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
"product/10g/network/admin/sqlnet.ora" 6L, 218C 저장 했습니다 
[oracle@ghost ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 5 11:37:51 2010

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

ERROR:
ORA-01031: insufficient privileges


Enter user-name: 
ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: 
ERROR:
ORA-01017: invalid username/password; logon denied


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[oracle@ghost ~]$ 
[oracle@ghost ~]$ sqlplus sys/oracle as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 5 11:38:04 2010

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

Connected to an idle instance.

SQL> exit
Disconnected
[oracle@ghost ~]$ 
저작자표시 비영리 변경금지 (새창열림)

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

Restore & Recovery  (0) 2010.03.08
Backup  (0) 2010.03.08
프로파일 & 유저 관리 & 권한 관리  (0) 2010.03.05
Constraints (제약 조건)  (0) 2010.03.05
Index (인덱스)  (0) 2010.03.05

Backup & Recovery용 Data 디스크 추가

O/S/LiNUX
posted by I유령I 2010. 3. 5. 10:24
Last login: Sun Feb 28 21:25:28 2010 from 192.168.0.1
[oracle@ghost ~]$ su -
Password: 
[root@ghost ~]# fdisk -l

Disk /dev/sda: 21.4 GB, 21474836480 bytes
255 heads, 63 sectors/track, 2610 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1          13      104391   83  Linux
/dev/sda2              14         650     5116702+  83  Linux
/dev/sda3             651         911     2096482+  82  Linux swap
/dev/sda4             912        2610    13647217+   5  Extended
/dev/sda5             912        1038     1020096   83  Linux
/dev/sda6            1039        2610    12627058+  83  Linux

Disk /dev/sdb: 21.4 GB, 21474836480 bytes
255 heads, 63 sectors/track, 2610 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/sdb doesn't contain a valid partition table
[root@ghost ~]# fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.


The number of cylinders for this disk is set to 2610.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
   (e.g., DOS FDISK, OS/2 FDISK)
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-2610, default 1): 
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-2610, default 2610): 
Using default value 2610

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
[root@ghost ~]# mkfs.ext3 /dev/sdb1
mke2fs 1.35 (28-Feb-2004)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
2621440 inodes, 5241198 blocks
262059 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=8388608
160 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks: 
        32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208, 
        4096000

Writing inode tables: done                            
Creating journal (8192 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 21 mounts or
180 days, whichever comes first.  Use tune2fs -c or -i to override.
[root@ghost data]# vi /etc/fstab 

/dev/sdb1               /disk1                  ext3    defaults        1 2

[root@ghost data]# exit
logout

[oracle@ghost ~]$ mkdir -p /data/arc1 /data/arc2
[oracle@ghost ~]$ mkdir -p /data/backup/close /data/backup/open /data/backup/rman
[oracle@ghost ~]$ df -kh
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda2             4.9G  2.8G  1.9G  60% /
/dev/sda1              99M  8.5M   86M  10% /boot
none                  506M     0  506M   0% /dev/shm
/dev/sda6              12G  3.1G  8.2G  28% /home
/dev/sda5             981M  100M  832M  11% /var
/dev/sdb1              20G   77M   19G   1% /data
[oracle@ghost ~]$ 
저작자표시 비영리 변경금지 (새창열림)

'O/S > LiNUX' 카테고리의 다른 글

RHEL4 디스크 추가  (0) 2010.02.28

Privileges(권한) 관리

Oracle 10g/10g - 실습
posted by I유령I 2010. 3. 5. 03:37

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

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

Cold Backup & Hot Backup  (0) 2010.03.08
Archive log mode 설정 및 활성화/비활성화  (0) 2010.03.05
Profile(프로파일) 생성과 관리  (0) 2010.03.05
User(유저) 생성과 관리  (0) 2010.03.05
Constraints 실습 (제약 조건 테스트)  (0) 2010.03.05

Profile(프로파일) 생성과 관리

Oracle 10g/10g - 실습
posted by I유령I 2010. 3. 5. 03:35

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

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

Archive log mode 설정 및 활성화/비활성화  (0) 2010.03.05
Privileges(권한) 관리  (0) 2010.03.05
User(유저) 생성과 관리  (0) 2010.03.05
Constraints 실습 (제약 조건 테스트)  (0) 2010.03.05
Table : Partitioned Table  (0) 2010.03.04

티스토리툴바