오라클 스터디

전체 글

Redo log flle 장애 처리방법 2014.11.05
Log Miner 활용하기 2014.11.05
control file, data file 연습문제 2014.11.04
Control file 복구 2014.11.04
Data file Recovery (불완전 복구) 2014.11.04
Data File Recovery 2014.10.30
Backup (cold, hot) 2014.10.30
사용자 암호설정/ Archive log mode, No Archive log mode 2014.10.29
DBMS_JOB & DBMS_SCHEDULER 2014.10.27
사용자 관리 2014.10.27

Redo log flle 장애 처리방법

김포춘 2014. 11. 5. 14:32

2014. 11. 5. 14:32

< Redo Log File 장애 처리 순서도 >

Case 1. 1개의 member 가 삭제되는 장애가 발생하는 경우

(에러코드 : ORA-00313: open failed for members of log group....)

SQL> !rm -f /data/temp3/redo01.log <-- 멤버 삭제

SQL> !ls /data/temp3/redo01.log

ls: /data/temp3/redo01.log: 그런 파일이나 디렉토리가 없음

SQL> alter system switch logfile; <-- 로그스위치를 일으켜도 이상 없음

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> @log

6 ;

GROUP# MEMBER MB seq STATUS arc

------ --------------------------------------------- ---- -----

1 /data/temp3/redo01.log 5 25 CURRENT NO <-- 파일을 삭제했는데 조회가능

1 /data/temp3/redo01_b.log 5 25 CURRENT NO

2 /data/temp3/redo02.log 5 23 INACTIVE YES

2 /data/temp3/redo02_b.log 5 23 INACTIVE YES

3 /data/temp3/redo03.log 5 24 INACTIVE YES

3 /data/temp3/redo03_b.log 5 24 INACTIVE YES

6 rows selected.

장애 내역을 보려면 alert log file 을 봐야 확인 할 수 있음

SQL> !

v[oracle@localhost ~]$ vi /app/oracle/diag/rdbms/testdb/testdb/trace/alert_testdb.log

Errors in file /app/oracle/diag/rdbms/testdb/testdb/trace/testdb_arc3_1063.trc:

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

ORA-00312: online log 1 thread 1: '/data/temp3/redo01.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

멤버 하나가 지워져도 운영에는 문제가 없어서 alert log 파일에만 장애를 기록하고 운영중인 DB는 정상작동 됨

장애가 확인된 멤버는 삭제 한 후 다시 생성해주면 잘 돌아감

[oracle@localhost ~]$ exit

exit

SQL> @log

6 ;

GROUP# MEMBER MB seq STATUS arc

------ --------------------------------------------- ---- ------

1 /data/temp3/redo01.log 5 25 CURRENT NO

1 /data/temp3/redo01_b.log 5 25 CURRENT NO

2 /data/temp3/redo02.log 5 23 INACTIVE YES

2 /data/temp3/redo02_b.log 5 23 INACTIVE YES

3 /data/temp3/redo03.log 5 24 INACTIVE YES

3 /data/temp3/redo03_b.log 5 24 INACTIVE YES

6 rows selected.

SQL> alter database drop logfile member

2 '/data/temp3/redo03_b.log';

Database altered.

SQL> @log

6 ;

GROUP# MEMBER MB seq STATUS arc

------ --------------------------------------------- ---- ---------- -------- -----

1 /data/temp3/redo01.log 5 25 CURRENT NO

1 /data/temp3/redo01_b.log 5 25 CURRENT NO

2 /data/temp3/redo02.log 5 23 INACTIVE YES

2 /data/temp3/redo02_b.log 5 23 INACTIVE YES

3 /data/temp3/redo03.log 5 24 INACTIVE YES

SQL> alter database add logfile member

2 '/data/temp3/redo03_b.log' t group 3;

'/data/temp3/redo03_b.log' t group 3

ERROR at line 2:

ORA-00946: missing TO keyword

SQL> ed

Wrote file afiedt.buf

1 alter database add logfile member

2* '/data/temp3/redo03_b.log' to group 3

SQL> /

alter database add logfile member

ERROR at line 1:

ORA-00301: error in adding log file '/data/temp3/redo03_b.log' - file cannot be created

ORA-27038: created file already exists

Additional information: 1

SQL> ed

Wrote file afiedt.buf

1 alter database add logfile member

2* '/data/temp3/redo03_b.log' to group 3

SQL> alter database add logfile member

2 '/data/temp3/redo03_b.log' to group 3;

alter database add logfile member

ERROR at line 1:

ORA-00301: error in adding log file '/data/temp3/redo03_b.log' - file cannot be created

ORA-27038: created file already exists

Additional information: 1

SQL> @log

6 ;

GROUP# MEMBER MB seq STATUS arc

------ --------------------------------------------- ---- ---------- -------- -----

1 /data/temp3/redo01.log 5 25 CURRENT NO

1 /data/temp3/redo01_b.log 5 25 CURRENT NO

2 /data/temp3/redo02.log 5 23 INACTIVE YES

2 /data/temp3/redo02_b.log 5 23 INACTIVE YES

3 /data/temp3/redo03.log 5 24 INACTIVE YES

삭제된 member 를 생성해주기 전에 해당 디렉토리가서 파일 지워야 함

SQL> !rm /data/temp3/redo03_b.log

SQL> alter database add logfile member '/data/temp3/redo03_b.log'to group 3;

Database altered.

SQL> @log

6 ;

GROUP# MEMBER MB seq STATUS arc

------ --------------------------------------------- ---- ---------- -------- -----

1 /data/temp3/redo01.log 5 25 CURRENT NO

1 /data/temp3/redo01_b.log 5 25 CURRENT NO

2 /data/temp3/redo02.log 5 23 INACTIVE YES

2 /data/temp3/redo02_b.log 5 23 INACTIVE YES

3 /data/temp3/redo03.log 5 24 INACTIVE YES

3 /data/temp3/redo03_b.log 5 24 INACTIVE YES

6 rows selected.

Case 2. archive 완료된 그룹이 지워진 후 DB Close 상태

( 에러코드 : ORA-00313: open failed for members of log group....)

current가 아닌 하나의 그룹이 삭제된 후 DB가 종료된 경우

DB를 시작하면 지워진 Redo Log 그룹 때문에 에러가 발생하며 DB가 켜지지 않음

그룹이 삭제전 Archive 에 데이터를 복사해 놓았다면 별 문제 없이 해당 그룹을 삭제하고 재생성 하는것으로 해결가능

step1. 현재 상태 확인

SQL> @log

6 ;

GROUP# MEMBER MB seq STATUS arc

------ --------------------------------------------- ------

1 /data/temp3/redo01_b.log 5 25 CURRENT NO

2 /data/temp3/redo02.log 5 23 INACTIVE YES

2 /data/temp3/redo02_b.log 5 23 INACTIVE YES

3 /data/temp3/redo03.log 5 24 INACTIVE YES

3 /data/temp3/redo03_b.log 5 24 INACTIVE YES

6 rows selected.

step2. redo log file 삭제 후 장애 확인

SQL> !rm /data/temp3/redo03*

SQL> !ls /data/temp3/redo03*

ls: /data/temp3/redo03*: 그런 파일이나 디렉토리가 없음

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 422670336 bytes

Fixed Size 1344616 bytes

Variable Size 293604248 bytes

Database Buffers 121634816 bytes

Redo Buffers 6086656 bytes

Database mounted.

ORA-03113: end-of-file on communication channel <-- 강제로 종료되었다는 뜻

Process ID: 1762

Session ID: 1 Serial number: 5

SQL> !

[oracle@localhost ~]$ vi /app/oracle/diag/rdbms/testdb/testdb/trace/alert_testdb.log

alert log 파일에서 redo03 장애 부분 확인

[oracle@localhost ~]$ exit

exit

step3. 복구 후 OPEN

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 17 16:53:53 2014

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 422670336 bytes

Fixed Size 1344616 bytes

Variable Size 293604248 bytes

Database Buffers 121634816 bytes

Redo Buffers 6086656 bytes

Database mounted.

SQL> @log

6 ;

GROUP# MEMBER MB seq STATUS arc

------ --------------------------------------------- ---- ---------- -------- -----

1 /data/temp3/redo01.log 5 25 CURRENT NO

1 /data/temp3/redo01_b.log 5 25 CURRENT NO

2 /data/temp3/redo02.log 5 23 INACTIVE YES

2 /data/temp3/redo02_b.log 5 23 INACTIVE YES

3 /data/temp3/redo03.log 5 24 INACTIVE YES

3 /data/temp3/redo03_c.log 5 24 INACTIVE YES

6 rows selected.

삭제된 그룹3 이 archive 의 상태가 YES 이므로 데이터가 들어갔음. 이럴 경우는 해당 그룹삭제후 OPEN 하면됨

SQL> alter database drop logfile group 3;

Database altered.

SQL> @log

6 ;

GROUP# MEMBER MB seq STATUS arc

------ --------------------------------------------- ---- ---------- -------- -----

1 /data/temp3/redo01.log 5 25 CURRENT NO

1 /data/temp3/redo01_b.log 5 25 CURRENT NO

2 /data/temp3/redo02.log 5 23 INACTIVE YES

2 /data/temp3/redo02_b.log 5 23 INACTIVE YES

SQL> alter database open;

Database altered. <-- 제대로 OPEN 됬음

SQL> alter database add logfile group 3(

2 '/data/temp3/redo03.log',

3 '/data/temp3/redo03_b.log') size 50m;

Database altered. <-- 다시 그룹3 생성해줌 (MOUNT 에서 만들고 OPEN 해도 됨)

SQL> @log

6 ;

GROUP# MEMBER MB seq STATUS arc

------ --------------------------------------------- ---- ---------- -------- -----

1 /data/temp3/redo01.log 5 25 CURRENT NO

1 /data/temp3/redo01_b.log 5 25 CURRENT NO

2 /data/temp3/redo02.log 5 23 INACTIVE YES

2 /data/temp3/redo02_b.log 5 23 INACTIVE YES

3 /data/temp3/redo03.log 50 0 UNUSED YES

3 /data/temp3/redo03_b.log 50 0 UNUSED YES

6 rows selected.

Case 3. current 아닌 그룹 중 archive 안 된 그룹이 삭제되고 DB가 OPEN 상태일 경우

(에러코드 : ORA-00313: open failed for members of log group ....)

삭제된 그룹이 archive 되지 않은 경우.

서버 운영중 특정 그룹이 지워진 후 계속 log switch 가 발생하게 되면 DB가 archive hang 현상이 발생해 비정상 종료됨

step1. 현재상태 확인

SQL> @log

6 ;

GROUP# MEMBER MB seq STATUS arc

------ --------------------------------------------- ---- ---------- -------- -----

1 /data/temp3/redo01.log 5 25 INACTIVE YES

1 /data/temp3/redo01_b.log 5 25 INACTIVE YES

2 /data/temp3/redo02.log 5 27 CURRENT NO

2 /data/temp3/redo02_b.log 5 27 CURRENT NO

3 /data/temp3/redo03.log 50 26 INACTIVE YES

3 /data/temp3/redo03_b.log 50 26 INACTIVE YES

6 rows selected.

step2. 그룹 삭제 ( archive 완료된 그룹 3 을 삭제 )

SQL> !rm -f /data/temp3/redo03*

SQL> !ls /data/temp3/redo03*

ls: /data/temp3/redo03*: 그런 파일이나 디렉토리가 없음

SQL> alter system switch logfile; <-- 로그스위치를 계속 발생시켜 hang 상태를 유발

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> / <-- hang 발생해서 대기상태 됨. ctrl + c 키로 작업 취소시킴

alter system switch logfile

ERROR at line 1:

ORA-01013: user requested cancel of current operation

SQL> @log

6 ;

GROUP# MEMBER MB seq STATUS arc

------ --------------------------------------------- ---- ---------- -------- -----

1 /data/temp3/redo01.log 5 31 CURRENT NO

1 /data/temp3/redo01_b.log 5 31 CURRENT NO

2 /data/temp3/redo02.log 5 30 INACTIVE NO

2 /data/temp3/redo02_b.log 5 30 INACTIVE NO

3 /data/temp3/redo03.log 50 29 INACTIVE NO

3 /data/temp3/redo03_b.log 50 29 INACTIVE NO

6 rows selected.

위에서 arc 부분을 보면 전부 NO 상태임을 확인할 수 있다.

3번이 장애가 일어나서 차례로 아카이빙이 되지않았기 때문에 hang 이 일어난것

SQL> !

[oracle@localhost ~]$ vi /app/oracle/diag/rdbms/testdb/testdb/trace/alert_testdb.log

Fri Oct 17 17:14:20 2014

Errors in file /app/oracle/diag/rdbms/testdb/testdb/trace/testdb_arc3_1979.trc:

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

ORA-00312: online log 3 thread 1: '/data/temp3/redo03_b.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

ORA-00312: online log 3 thread 1: '/data/temp3/redo03.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

위 에러처럼 그룹 3이 장애가 난것을 확인할 수 있음

[oracle@localhost ~]$ exit

exit

SQL> alter database clear unarchived logfile group 3;

Database altered.

위 명령어는 redo log file 을 drop 하고 신규로 add 해주는 역할.

위처럼 운영중 redo log file 이 장애가 발생했을 경우 redo log file 을 초기화시켜 DB 중단없이 사용할 수 있도록 하는 기능

이 명령어로 redo log file 을 재생성하게 되면 반드시 DB 전체를 backup 해줘야 한다.

향후 장애가 생겼을 경우 archive log file 이 중간에 비어있어서 복구가 안됨

SQL> @log

6 ;

GROUP# MEMBER MB seq STATUS arc

------ --------------------------------------------- ---- ---------- -------- -----

1 /data/temp3/redo01.log 5 31 CURRENT NO

1 /data/temp3/redo01_b.log 5 31 CURRENT NO

2 /data/temp3/redo02.log 5 30 INACTIVE YES

2 /data/temp3/redo02_b.log 5 30 INACTIVE YES

3 /data/temp3/redo03.log 50 0 UNUSED YES

3 /data/temp3/redo03_b.log 50 0 UNUSED YES

6 rows selected.

Case 4. Current 아닌 그룹 중 archive 안 된 그룹이 지워지고 DB Close 상태인 경우

[관련 에러코드]

ORA-00257 : archiver error. Connect internal only, until freed.

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

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

step1. 상태 확인

SQL> @log

6 ;

GROUP# MEMBER MB seq STATUS arc

------ --------------------------------------------- ---- ---------- -------- -----

1 /data/temp3/redo01.log 5 34 CURRENT NO

1 /data/temp3/redo01_b.log 5 34 CURRENT NO

2 /data/temp3/redo02.log 5 33 INACTIVE YES

2 /data/temp3/redo02_b.log 5 33 INACTIVE YES

3 /data/temp3/redo03.log 50 32 INACTIVE YES

3 /data/temp3/redo03_b.log 50 32 INACTIVE YES

6 rows selected.

step2. 장애 발생시킴

SQL> !rm -f /data/temp3/redo03*

SQL> !ls /data/temp3/redo03*

ls: /data/temp3/redo03*: 그런 파일이나 디렉토리가 없음

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL>/ <-- hang 발생

[oracle@localhost ~]$ sqlplus scott/tiger <-- hang 상태로 다른 터미널 열어서 테스트

SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 17 17:33:32 2014

ERROR:

ORA-00257: archiver error. Connect internal only, until freed. <-- 접속이 안됨

Enter user-name: sys/oracle as sysdba <-- 관리자 계정으로는 접속이 가능

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

With the Partitioning option

SQL>

관리자 계정으로 에러를 해결해야 하기 때문에 다른 계정은 접속이 안됨

SQL> shutdown abort;

ORACLE instance shut down.

step3. 재시작 시키면 에러 발생하며 중단 됨

SQL> startup

ORACLE instance started.

Total System Global Area 422670336 bytes

Fixed Size 1344616 bytes

Variable Size 293604248 bytes

Database Buffers 121634816 bytes

Redo Buffers 6086656 bytes

Database mounted.

ORA-03113: end-of-file on communication channel <-- redo 문제로 강제 종료됨

Process ID: 2763

Session ID: 1 Serial number: 5

step4. 장애 확인후 복구

SQL> !

[oracle@localhost ~]$ vi /app/oracle/diag/rdbms/testdb/testdb/trace/alert_testdb.log <--alert log 열어서 장애확인

[oracle@localhost ~]$ sqlplus sys/oracle as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 17 17:35:53 2014

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 422670336 bytes

Fixed Size 1344616 bytes

Variable Size 293604248 bytes

Database Buffers 121634816 bytes

Redo Buffers 6086656 bytes

Database mounted.

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: '/data/temp3/redo03.log'

ORA-00312: online log 3 thread 1: '/data/temp3/redo03_b.log'

SQL> alter database clear unarchived logfile group 3; <-- redo log file 을 drop 하고 재생성

Database altered.

SQL> alter database open;

Database altered.

SQL> !ls /data/temp3/redo03*

/data/temp3/redo03_b.log /data/temp3/redo03.log

SQL> @log;

6 ;

GROUP# MEMBER MB seq STATUS arc

------ --------------------------------------------- ---- ---------- -------- -----

1 /data/temp3/redo01.log 5 37 INACTIVE YES

1 /data/temp3/redo01_b.log 5 37 INACTIVE YES

2 /data/temp3/redo02.log 5 36 INACTIVE YES

2 /data/temp3/redo02_b.log 5 36 INACTIVE YES

3 /data/temp3/redo03.log 50 38 CURRENT NO

3 /data/temp3/redo03_b.log 50 38 CURRENT NO

6 rows selected.

SQL>

Case 5. current 그룹이 삭제된 후 DB Open 상태

이 경우는 위에서 살펴본 case 3 과 같은 경우

Case 6. Current 그룹이 삭제된 경우 - Shut immediate 로 종료

[관련 에러 코드]

ORA-00313 : open failed for members of log group .....

ORA-03113 : end-of-file on communication channel.

Current 그룹이 삭제된 후 DB가 종료되었다면 정상 종료인지 비정상 종료인지에 따라 향후 재시작 할 때 문제가 될 수 있음

step1. 현재상태 확인

SQL> @log

6 ;

GROUP# MEMBER MB seq STATUS arc

------ --------------------------------------------- ---- ---------- -------- -----

1 /data/temp3/redo01.log 5 37 INACTIVE YES

1 /data/temp3/redo01_b.log 5 37 INACTIVE YES

2 /data/temp3/redo02.log 5 36 INACTIVE YES

2 /data/temp3/redo02_b.log 5 36 INACTIVE YES

3 /data/temp3/redo03.log 50 38 CURRENT NO

3 /data/temp3/redo03_b.log 50 38 CURRENT NO

6 rows selected.

step2. 데이터 입력 후 장애 발생

SQL> create table scott.tt200 (no number) tablespace users;

Table created.

SQL> insert into scott.tt200 values(1);

1 row created.

SQL> commit;

Commit complete.

테스트용 테이블 scott.tt200 을 생성한 후 1을 입력한 내용이 3번 그룹에 저장됨

step3. current 상태인 3번 그룹을 os 명령어로 삭제 한 후 재부팅

SQL> !rm -f /data/temp3/redo03*

SQL> !ls /data/temp3/redo03*

ls: /data/temp3/redo03*: No such file or directory

SQL> shut immediate; <-- 정상 종료시킴

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 422670336 bytes

Fixed Size 1344616 bytes

Variable Size 293604248 bytes

Database Buffers 121634816 bytes

Redo Buffers 6086656 bytes

Database mounted.

ORA-03113: end-of-file on communication channel <-- 강제로 종료 됨

Process ID: 3300

Session ID: 1 Serial number: 5

step4. alert log로 에러 메시지 확인

SQL> !vi $ORACLE_BASE/diag/rdbms/testdb/testdb/trace/alert_testdb.log

step5. 복구후 open

current 상태의 redo log group 이 삭제된 후 DB가 shutdown immediate로 종료 된 것입니다.

scott.tt200 을 생성후 1을 입력 후 commit 했기 때문에 shutdown immediate 했으면 해당 내용은 데이터 파일로 저장된 상태

그렇기 때문에 데이터는 복구 할 필요가 없고 redo log file 이 없어서 open 안되는 문제만 해결하면 됨

redo log 를 재생성 하는 resetlogs 옵션을 사용하기 위해 불완전 복구를 해야함

그래서 지금과 같은 상황에 불완전 복구 하기 위해 until cancel 옵션을 사용

SQL> startup mount

ORACLE instance started.

Total System Global Area 422670336 bytes

Fixed Size 1344616 bytes

Variable Size 293604248 bytes

Database Buffers 121634816 bytes

Redo Buffers 6086656 bytes

Database mounted.

SQL> recover database until cancel;

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select * from scott.tt200;

----------

Case 7. Current 그룹이 삭제된 후 shutdown abort 로 종료 (backup file 이 존재할 경우)

[관련 에러 코드]

ORA-00326: log begins at change ....

ORA-00283: recovery session canceled due to errors

step0. DB 종료후 전체 데이터베이스 백업

step1. Open 후 현재 상태 확인

SQL> @log

6 ;

GROUP# MEMBER MB seq STATUS arc

------ --------------------------------------------- ---- ---------- -------- -----

1 /data/temp3/redo01.log 5 4 INACTIVE YES

1 /data/temp3/redo01_b.log 5 4 INACTIVE YES

2 /data/temp3/redo02.log 5 5 CURRENT NO

2 /data/temp3/redo02_b.log 5 5 CURRENT NO

3 /data/temp3/redo03.log 50 3 INACTIVE YES

3 /data/temp3/redo03_b.log 50 3 INACTIVE YES

6 rows selected.

2번 그룹에 scott.tt600 테이블을 생성해서 데이터 1건을 입력

SQL> create table scott.tt600 (no number);

Table created.

SQL> insert into scott.tt600 values(1);

1 row created.

SQL> commit;

Commit complete. <-- 여기까지 2번 그룹에 저장

SQL> alter system switch logfile; <-- 로그 스위치 일으켜서 current 가 3번 그룹으로 바뀜

System altered.

SQL> @log

6 ;

GROUP# MEMBER MB seq STATUS arc

------ --------------------------------------------- ---- ---------- -------- -----

1 /data/temp3/redo01.log 5 4 INACTIVE YES

1 /data/temp3/redo01_b.log 5 4 INACTIVE YES

2 /data/temp3/redo02.log 5 5 ACTIVE YES

2 /data/temp3/redo02_b.log 5 5 ACTIVE YES

3 /data/temp3/redo03.log 50 6 CURRENT NO

3 /data/temp3/redo03_b.log 50 6 CURRENT NO

6 rows selected.

3번 그룹에 데이터 입력

SQL> insert into scott.tt600 values(2);

1 row created.

SQL> commit;

Commit complete. <-- 여기까지 3번 그룹에 저장

SQL> alter system switch logfile; <-- 로그스위치 일으켜서 1번 그룹이 current

System altered.

SQL> @log

6 ;

GROUP# MEMBER MB seq STATUS arc

------ --------------------------------------------- ---- ---------- -------- -----

1 /data/temp3/redo01.log 5 7 CURRENT NO

1 /data/temp3/redo01_b.log 5 7 CURRENT NO

2 /data/temp3/redo02.log 5 5 ACTIVE YES

2 /data/temp3/redo02_b.log 5 5 ACTIVE YES

3 /data/temp3/redo03.log 50 6 ACTIVE YES

3 /data/temp3/redo03_b.log 50 6 ACTIVE YES

6 rows selected.

SQL> insert into scott.tt600 values(3);

1 row created.

SQL> commit;

Commit complete. <-- 1번 그룹에 저장

SQL> select * from scott.tt600;

----------

1 <-- 2번 그룹에 저장됨

2 <-- 3번 그룹에 저장됨

3 <-- 1번 그룹에 저장됨

현재 current 상태인 1번 그룹 삭제하는 장애 만듬

SQL> @log

6 ;

GROUP# MEMBER MB seq STATUS arc

------ --------------------------------------------- ---- ---------- -------- -----

1 /data/temp3/redo01.log 5 7 CURRENT NO

1 /data/temp3/redo01_b.log 5 7 CURRENT NO

2 /data/temp3/redo02.log 5 5 ACTIVE YES

2 /data/temp3/redo02_b.log 5 5 ACTIVE YES

3 /data/temp3/redo03.log 50 6 ACTIVE YES

3 /data/temp3/redo03_b.log 50 6 ACTIVE YES

6 rows selected.

SQL> !rm -f /data/temp3/redo01*

SQL> !ls /data/temp3/redo01*

ls: /data/temp3/redo01*: No such file or directory

SQL> shut abort;

ORACLE instance shut down.

SQL> !cp /data/backup/close/*.dbf /data/temp3/

step3. 복구하기

SQL> startup mount

ORACLE instance started.

Total System Global Area 422670336 bytes

Fixed Size 1344616 bytes

Variable Size 293604248 bytes

Database Buffers 121634816 bytes

Redo Buffers 6086656 bytes

Database mounted.

SQL> recover database until cancel;

ORA-00279: change 1428687 generated at 10/17/2014 18:26:38 needed for thread 1

ORA-00289: suggestion : /data/arc2/1_1_861213720.arc

ORA-00280: change 1428687 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1429110 generated at 10/17/2014 18:45:57 needed for thread 1

ORA-00289: suggestion : /data/arc2/2_1_861213720.arc

ORA-00280: change 1429110 for thread 1 is in sequence #2

ORA-00278: log file '/data/arc2/1_1_861213720.arc' no longer needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1429113 generated at 10/17/2014 18:45:57 needed for thread 1

ORA-00289: suggestion : /data/arc2/3_1_861213720.arc

ORA-00280: change 1429113 for thread 1 is in sequence #3

ORA-00278: log file '/data/arc2/2_1_861213720.arc' no longer needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1429117 generated at 10/17/2014 18:46:00 needed for thread 1

ORA-00289: suggestion : /data/arc2/4_1_861213720.arc

ORA-00280: change 1429117 for thread 1 is in sequence #4

ORA-00278: log file '/data/arc2/3_1_861213720.arc' no longer needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1429120 generated at 10/17/2014 18:46:00 needed for thread 1

ORA-00289: suggestion : /data/arc2/5_1_861213720.arc

ORA-00280: change 1429120 for thread 1 is in sequence #5

ORA-00278: log file '/data/arc2/4_1_861213720.arc' no longer needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1429213 generated at 10/17/2014 18:46:51 needed for thread 1

ORA-00289: suggestion : /data/arc2/6_1_861213720.arc

ORA-00280: change 1429213 for thread 1 is in sequence #6

ORA-00278: log file '/data/arc2/5_1_861213720.arc' no longer needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1429232 generated at 10/17/2014 18:47:37 needed for thread 1

ORA-00289: suggestion : /data/arc2/7_1_861213720.arc

ORA-00280: change 1429232 for thread 1 is in sequence #7

ORA-00278: log file '/data/arc2/6_1_861213720.arc' no longer needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log '/data/arc2/7_1_861213720.arc'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

SQL> alter database open resetlogs;

Database altered.

SQL> select * from scott.tt600

2 ;

----------

<-- 3번그룹은 데이터가 복구 안되었음

Case 8. - Current 그룹이 삭제되고 Shutdown abort 로 종료됨 (backup file 이나 archive log file 이 없을 경우)

[관련 에러 코드]

ORA-01547 : warning:RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-00308 : cannot open archived log ...

ORA-01194 : file 1 needs more recovery to be consistent

상황 1. 데이터를 모두 복구해내는 경우

step 1. no archive log mode 로 변경하기

SQL> shut immediate

SQL> startup mount

SQL> archive log list <-- archive 모드 확인하고 archive log mode일경우 noarchive log mode 로 변경

SQL> alter database noarchivelog ;

SQL> alter database open ;

step 2. 데이터 입력

SQL>@log

6 ;

GROUP# MEMBER MB seq STATUS arc

------ --------------------------------------------- ---- ---------- -------- -----

1 /data/temp3/redo01.log 5 4 CURRENT NO

1 /data/temp3/redo01_b.log 5 4 CURRENT NO

2 /data/temp3/redo02.log 5 2 INACTIVE NO

2 /data/temp3/redo02_b.log 5 2 INACTIVE NO

3 /data/temp3/redo03.log 50 3 INACTIVE NO

3 /data/temp3/redo03_b.log 50 3 INACTIVE NO

6 rows selected.

SQL> create table scott.tt650 ( no number ) ;

Table created.

SQL> insert into scott.tt650 values(1);

1 row created.

SQL> commit;

Commit complete. <--여기까지 1번 그룹에 저장

SQL> alter system switch logfile;

System altered.

SQL> @log

6 ;

GROUP# MEMBER MB seq STATUS arc

------ --------------------------------------------- ---- ---------- -------- -----

1 /data/temp3/redo01.log 5 4 ACTIVE NO

1 /data/temp3/redo01_b.log 5 4 ACTIVE NO

2 /data/temp3/redo02.log 5 5 CURRENT NO

2 /data/temp3/redo02_b.log 5 5 CURRENT NO

3 /data/temp3/redo03.log 50 3 INACTIVE NO

3 /data/temp3/redo03_b.log 50 3 INACTIVE NO

6 rows selected.

SQL> insert into scott.tt650 values(2);

1 row created.

SQL> commit;

Commit complete. <--여기까지 2번 그룹에 저장

SQL> alter system switch logfile;

System altered.

SQL> @log

6 ;

GROUP# MEMBER MB seq STATUS arc

------ --------------------------------------------- ---- ---------- -------- -----

1 /data/temp3/redo01.log 5 4 ACTIVE NO

1 /data/temp3/redo01_b.log 5 4 ACTIVE NO

2 /data/temp3/redo02.log 5 5 ACTIVE NO

2 /data/temp3/redo02_b.log 5 5 ACTIVE NO

3 /data/temp3/redo03.log 50 6 CURRENT NO

3 /data/temp3/redo03_b.log 50 6 CURRENT NO

6 rows selected.

SQL> insert into scott.tt650 values(3);

1 row created.

SQL> commit;

Commit complete. <--여기까지 3번 그룹에 저장

SQL> select * from scott.tt650;

----------

1 <-- 1번 그룹에 저장

2 <-- 2번 그룹에 저장

3 <-- 3번 그룹에 저장

step 3. current 그룹이 삭제되는 장애 발생 후 강제 종료

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> @log

6 ;

GROUP# MEMBER MB seq STATUS arc

------ --------------------------------------------- ---- ---------- -------- -----

1 /data/temp3/redo01.log 5 7 INACTIVE NO

1 /data/temp3/redo01_b.log 5 7 INACTIVE NO

2 /data/temp3/redo02.log 5 8 INACTIVE NO

2 /data/temp3/redo02_b.log 5 8 INACTIVE NO

3 /data/temp3/redo03.log 50 9 CURRENT NO

3 /data/temp3/redo03_b.log 50 9 CURRENT NO

6 rows selected.

위에서 3번 그룹이 current 임이 확인 됨

SQL> !rm -f data/temp3/redo03*

SQL> !ls /data/temp3/redo03*

/data/temp3/redo03_b.log /data/temp3/redo03.log

SQL> !rm -f /data/temp3/redo03*

SQL> !ls /data/temp3/redo03*

ls: /data/temp3/redo03*: No such file or directory

SQL> shut abort

ORACLE instance shut down.

step 4. 복구

SQL> !vi $ORACLE_HOME/dbs/inittestdb.ora

기존 파라 미터 파일에 다음과 같은 파라미터 추가 : _allow_resetlogs_corruption=true

SQL> startup

ORACLE instance started.

Total System Global Area 422670336 bytes

Fixed Size 1344616 bytes

Variable Size 293604248 bytes

Database Buffers 121634816 bytes

Redo Buffers 6086656 bytes

Database mounted.

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

ORA-00312: online log 3 thread 1: '/data/temp3/redo03_b.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

ORA-00312: online log 3 thread 1: '/data/temp3/redo03.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

위와 같이 3번 그룹이 없어서 에러가 발생

SQL> recover database until cancel;

ORA-00279: change 1430366 generated at 10/17/2014 19:05:37 needed for thread 1

ORA-00289: suggestion : /data/arc2/9_1_861216772.arc

ORA-00280: change 1430366 for thread 1 is in sequence #9

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log '/data/arc2/9_1_861216772.arc'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/data/temp3/system01.dbf'

SQL> alter database open resetlogs;

Database altered.

SQL> select * from scott.tt650;

----------

복구가 된 이유는 로그스위치가 수차례 일어나면서 데이터가 데이터파일에 모두 저장이되어서 완전 복구 된 것

상황 2. current 에 저장된 데이터는 복구 못하는 경우

SQL> @log

6 ;

GROUP# MEMBER MB seq STATUS arc

------ --------------------------------------------- ---- ---------- -------- -----

1 /data/temp3/redo01.log 5 1 CURRENT NO

1 /data/temp3/redo01_b.log 5 1 CURRENT NO

2 /data/temp3/redo02.log 5 0 UNUSED YES

2 /data/temp3/redo02_b.log 5 0 UNUSED YES

3 /data/temp3/redo03.log 50 0 UNUSED YES

3 /data/temp3/redo03_b.log 50 0 UNUSED YES

6 rows selected.

SQL> create table scott.tt660 ( no number );

Table created.

SQL> insert into scott.tt660 values(1);

1 row created.

SQL> commit; <-- 여기까지 1번 그룹에 저장

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> insert into scott.tt660 values(2);

1 row created.

SQL> commit;

Commit complete. <-- 여기까지 2번 그룹에 저장

SQL> @log

6 ;

GROUP# MEMBER MB seq STATUS arc

------ --------------------------------------------- ---- ---------- -------- -----

1 /data/temp3/redo01.log 5 1 ACTIVE NO

1 /data/temp3/redo01_b.log 5 1 ACTIVE NO

2 /data/temp3/redo02.log 5 2 CURRENT NO

2 /data/temp3/redo02_b.log 5 2 CURRENT NO

3 /data/temp3/redo03.log 50 0 UNUSED YES

3 /data/temp3/redo03_b.log 50 0 UNUSED YES

6 rows selected.

SQL> alter system switch logfile;

System altered.

SQL> insert into scott.tt660 values(3);

1 row created.

SQL> commit;

Commit complete. <-- 여기까지 3번 그룹에 저장

SQL> @log

6 ;

GROUP# MEMBER MB seq STATUS arc

------ --------------------------------------------- ---- ---------- -------- -----

1 /data/temp3/redo01.log 5 1 ACTIVE NO

1 /data/temp3/redo01_b.log 5 1 ACTIVE NO

2 /data/temp3/redo02.log 5 2 ACTIVE NO

2 /data/temp3/redo02_b.log 5 2 ACTIVE NO

3 /data/temp3/redo03.log 50 3 CURRENT NO

3 /data/temp3/redo03_b.log 50 3 CURRENT NO

6 rows selected.

SQL> !rm /data/temp3/redo03*

SQL> !ls /data/temp3/redo03*

ls: /data/temp3/redo03*: No such file or directory

SQL> shut abort

ORACLE instance shut down.

SQL>! vi $ORACLE_HOME/dbs/inittestdb.ora

__allow_resetlogs_corruption=true 파라미터 추가

SQL> startup mount

ORACLE instance started.

Total System Global Area 422670336 bytes

Fixed Size 1344616 bytes

Variable Size 293604248 bytes

Database Buffers 121634816 bytes

Redo Buffers 6086656 bytes

Database mounted.

SQL> @log

6 ;

GROUP# MEMBER MB seq STATUS arc

------ --------------------------------------------- ---- ---------- -------- -----

1 /data/temp3/redo01.log 5 1 ACTIVE NO

1 /data/temp3/redo01_b.log 5 1 ACTIVE NO

2 /data/temp3/redo02.log 5 2 ACTIVE NO

2 /data/temp3/redo02_b.log 5 2 ACTIVE NO

3 /data/temp3/redo03.log 50 3 CURRENT NO

3 /data/temp3/redo03_b.log 50 3 CURRENT NO

6 rows selected.

1,2 번 그룹은 active 상태이므로 해당 로그 내용은 데이터파일에 저장 안되었음을 알 수 있다.

SQL> recover database until cancel;

ORA-00279: change 1430370 generated at 10/17/2014 19:07:48 needed for thread 1

ORA-00289: suggestion : /data/arc2/1_1_861217667.arc

ORA-00280: change 1430370 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/data/temp3/redo01.log <-- sequence 1 에 해당하는 로그파일 입력

ORA-00279: change 1430933 generated at 10/17/2014 19:16:41 needed for thread 1

ORA-00289: suggestion : /data/arc2/2_1_861217667.arc

ORA-00280: change 1430933 for thread 1 is in sequence #2

ORA-00278: log file '/data/temp3/redo01.log' no longer needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/data/temp3/redo02.log <-- sequence 2 에 해당하는 로그파일 입력

ORA-00279: change 1430944 generated at 10/17/2014 19:17:05 needed for thread 1

ORA-00289: suggestion : /data/arc2/3_1_861217667.arc

ORA-00280: change 1430944 for thread 1 is in sequence #3

ORA-00278: log file '/data/temp3/redo02.log' no longer needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel <-- sequence 3 에 해당하는 로그파일 삭제되어 cancel 입력

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/data/temp3/system01.dbf'

ORA-01112: media recovery not started <-- 리두로그파일3이 없어 제대로 복구되지않아 이렇게 뜸

SQL> alter database open resetlogs; <-- reset logs 옵션으로 오픈

Database altered.

SQL> select * from scott.tt660;

----------

resetlogs 와 _allow_resetlogs_corruption

DB가 OPEN 되기 위해서는 운영중인 Data file, Redo Log File , Controlfile 의 체크포인트 정보가 모두 동일해야 한다.

그러나 불완전 복구를 하게되면 3개의 파일의 체크포인트 정보가 달라지게 된다.

resetlogs 옵션은datafile끼리의 체크포인트 정보는 같지만 redo log file 과 controlfile 과의 체크포인트 정보가 다를 경우 사용

데이터 파일들 끼리 체크포인트 정보가 다를경우 사용하는 옵션이 _allow_resetlogs_corruption=true 사용.

Case 9. 전체 Redo log file이 삭제된 경우 DB OPEN 상태 (archive log mode 에서 작업)

step1. 현재 상태 확인

SQL> @log

6 ;

GROUP# MEMBER MB seq STATUS arc

------ --------------------------------------------- ---- ---------- -------- -----

1 /data/temp3/redo01.log 5 1 CURRENT NO

1 /data/temp3/redo01_b.log 5 1 CURRENT NO

2 /data/temp3/redo02.log 5 0 UNUSED YES

2 /data/temp3/redo02_b.log 5 0 UNUSED YES

3 /data/temp3/redo03.log 50 0 UNUSED YES

3 /data/temp3/redo03_b.log 50 0 UNUSED YES

6 rows selected.

SQL> !rm -f /data/temp3/*.log

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> / <-- hang 발생 ctrl+c 눌러 취소

alter system switch logfile

ERROR at line 1:

ORA-01013: user requested cancel of current operation

SQL> @log

6 ;

GROUP# MEMBER MB seq STATUS arc

------ --------------------------------------------- ---- ---------- -------- -----

1 /data/temp3/redo01.log 5 1 INACTIVE NO

1 /data/temp3/redo01_b.log 5 1 INACTIVE NO

2 /data/temp3/redo02.log 5 2 INACTIVE NO

2 /data/temp3/redo02_b.log 5 2 INACTIVE NO

3 /data/temp3/redo03.log 50 3 CURRENT NO

3 /data/temp3/redo03_b.log 50 3 CURRENT NO

6 rows selected.

1번 그룹이 seq 가 가장낮아서 1번 그룹 때문에 이후 모든 로그파일이 아카이빙 안되고 있어서 clear 해줌

SQL> alter database clear unarchived logfile group 1;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> / <-- hang 발생 ctrl+c 눌러 취소

alter system switch logfile

ERROR at line 1:

ORA-01013: user requested cancel of current operation

SQL> @log

6 ;

GROUP# MEMBER MB seq STATUS arc

------ --------------------------------------------- ---- ---------- -------- -----

1 /data/temp3/redo01.log 5 4 CURRENT NO

1 /data/temp3/redo01_b.log 5 4 CURRENT NO

2 /data/temp3/redo02.log 5 2 INACTIVE NO

2 /data/temp3/redo02_b.log 5 2 INACTIVE NO

3 /data/temp3/redo03.log 50 3 ACTIVE NO

3 /data/temp3/redo03_b.log 50 3 ACTIVE NO

6 rows selected.

2번 그룹이 seq 가 가장낮아서 1번 그룹 때문에 이후 모든 로그파일이 아카이빙 안되고 있어서 clear 해줌

SQL> alter database clear unarchived logfile group 2;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> / <-- hang 발생 ctrl+c 눌러 취소

alter system switch logfile

ERROR at line 1:

ORA-01013: user requested cancel of current operation

SQL> @log

6 ;

GROUP# MEMBER MB seq STATUS arc

------ --------------------------------------------- ---- ---------- -------- -----

1 /data/temp3/redo01.log 5 4 ACTIVE NO

1 /data/temp3/redo01_b.log 5 4 ACTIVE NO

2 /data/temp3/redo02.log 5 5 CURRENT NO

2 /data/temp3/redo02_b.log 5 5 CURRENT NO

3 /data/temp3/redo03.log 50 3 INACTIVE NO

3 /data/temp3/redo03_b.log 50 3 INACTIVE NO

6 rows selected.

3번 그룹이 seq 가 가장낮아서 1번 그룹 때문에 이후 모든 로그파일이 아카이빙 안되고 있어서 clear 해줌

SQL> alter database clear unarchived logfile group 3;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

아무 이상없이 작동 잘됨.

Case 10. 전체 Redo log file 이 삭제된 경우 DB Close 상태 - 백업 있는 상태

DB가 shut immediate 상태로 종료되었을 경우 앞에서 확인한 Case 6 번의 경우와 동일하게 해결하면 된다.

그러나 shut abort 상태로 비정상 종료되었을 경우는 Case 7 번의 경우와 동일하게 복구하면 된다.

Case 11. 전체 Redo log file 이 삭제된 경우 DB Close 상태 - 백업 없는 상태

Case 8 방법과 복구방법이 동일

저작자표시 변경금지

'오라클 > 백업/복구' 카테고리의 다른 글

Clone DB (0)	2014.11.10
sql loader (0)	2014.11.10
데이터 이동하기 (0)	2014.11.06
Log Miner 활용하기 (0)	2014.11.05
control file, data file 연습문제 (0)	2014.11.04
Control file 복구 (0)	2014.11.04
Data file Recovery (불완전 복구) (0)	2014.11.04

Log Miner 활용하기

김포춘 2014. 11. 5. 13:26

2014. 11. 5. 13:26

Log miner

-> redo log file 의 내용을 sql 문장으로 변환해 사용자가 sql 문장으로 조회해서 원하는 내용을 확인하는 것

log miner 조회 순서

-> 1. log miner 전용 Dictionary 생성

2. log miner 로 분석할 로그 보냄 (redo log buffer, archive log) = 등록

3. log miner 에서 분석

4. sql 로 분석 결과 조회

supplemental logging 기능

-> 데이터에 DML이 발생할 경우 redo log 에 추가적인 내용을 기록 ( 기본값 보다 자세한 내용 기록 )

- 최소 기록 (disable) : 기본 모드

- 자세히 기록 (enable)

supplemental logging 활성화 여부 조회

SYS> select supplemental_log_data_min from v$database ;

SUPPLEMENTAL_LOG

-----------------

NO <- 기본값

활성화 시키기

SYS> alter database add supplemental log data ;

SYS> select supplemental_log_data_min from v$database ;

SUPPLEMENTAL_LOG

-----------------

YES

비활성화 시키기

SYS> alter database drop supplemental log data ;

SYS> select supplemental_log_data_min from v$database ;

SUPPLEMENTAL_LOG

-----------------

특정 테이블만 supplemental log 적용

SYS> alter table scott.emp add supplemental log data (all) columns ;

Log Miner 실습하기 1. - drop table 정보 찾기

step 1. 현재 상태 확인

SYS> @log

GROUP# MEMBER MB seq STATUS arc

------ --------------------------------------------------------

1 /data/temp3/redo01.log 5 1 CURRENT NO

2 /data/temp3/redo02.log 5 0 UNUSED YES

3 /data/temp3/redo03.log 5 0 UNUSED YES

SYS> select supplemental_log_data_min from v$database ;

SUPPLEMENTAL_LOG

-----------------

YES <-- 활성화 시키고 실습 시작

step 2. 새로운 테이블 생성 후 데이터 입력하고 삭제

SQL> create table scott.test1 (no number);

Table created.

SQL> insert into scott.test1 values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> drop table scott.test1 purge;

Table dropped.

SQL> @log

6 ;

GROUP# MEMBER MB seq STATUS arc

------ --------------------------------------------------------

1 /data/temp3/redo01.log 5 1 CURRENT NO <-- 1번 그룹에서 진행 중

2 /data/temp3/redo02.log 5 0 UNUSED YES

3 /data/temp3/redo03.log 5 0 UNUSED YES

step 3. Log Miner 딕셔너리를 생성

-> log miner 가 redo log file 을 분석해 그 안에 있는 테이블 이름이나 컬럼 이름들이 있는 부분을 딕셔너리를 확인해서 사용자가 볼 수 있도록 변환해줌

( 여기서 딕셔너리는 SQL Parse 단계에서 사용하는 딕셔너리가 아니라 Log Miner 가 사용하는 별도의 딕셔너리를 말함 )

딕셔너리 생성방법 3가지

1. Online Catalog 사용

2. Redo Log File 자체에 저장하는 방법

3. Flat file 에 저장하는 방법

※ 1,2 의 방법은 잘 사용 안함

1) DB 전체를 종료한 후 Parameter file 에 딕셔너리 파일의 위치를 아래와 같이 지정

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> !

[oracle@localhost ~]$ vi /app/oracle/product/11g/dbs/inittestdb.ora

utl_file_dir=/app/oracle/logminer

2) DB 시작

[oracle@localhost ~]$ exit

exit

SQL> startup

ORACLE instance started.

Total System Global Area 422670336 bytes

Fixed Size 1344616 bytes

Variable Size 293604248 bytes

Database Buffers 121634816 bytes

Redo Buffers 6086656 bytes

Database mounted.

Database opened.

3) 아래와 같이 딕셔너리 생성

SQL> exec dbms_logmnr_d.build(dictionary_filename=>'dict.dat',-

> dictionary_location=>'/app/oracle/logminer');

PL/SQL procedure successfully completed.

4) Log Miner 에 분석할 로그 추가

SQL> exec dbms_logmnr.add_logfile('/data/temp3/redo01.log',1);

-- 위 명령어 마지막 숫자의 의미는 1= 신규등록 2= 파일삭제 3= 추가등록

PL/SQL procedure successfully completed.

2,3 번 그룹을 추가 등록

SQL> exec dbms_logmnr.add_logfile('/data/temp3/redo02.log',3);

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.add_logfile('/data/temp3/redo03.log',3);

BEGIN dbms_logmnr.add_logfile('/data/temp3/redo03.log',3); END;

ERROR at line 1:

ORA-01289: cannot add duplicate logfile /data/temp3/redo03.log

ORA-06512: at "SYS.DBMS_LOGMNR", line 68

ORA-06512: at line 1

3번 그룹을 추가할 때 위와 같은 에러가 나면 로그파일이 UNUSED 상태라서 그럼

alter system switch logfile 과 alter system checkpoint 를 실행시켜 Inactive 와 current 상태로 만들어주자

SQL> alter system switch logfile ;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> alter system checkpoint;

System altered.

SQL> @log

6 ;

GROUP# MEMBER MB seq STATUS arc

------ --------------------------------------------- ---- -----

1 /data/temp3/redo01.log 5 4 CURRENT NO

2 /data/temp3/redo02.log 5 2 INACTIVE YES

3 /data/temp3/redo03.log 5 3 INACTIVE YES

위의 에러를 해결하고 redo log file 을 다시 등록해주고 log miner 에 등록된 log file을 확인

SQL> select db_name,filename from v$logmnr_logs;

DB_NAME FILENAME

--------------- --------------------

TESTDB /data/temp3/redo01.log

TESTDB /data/temp3/redo02.log

TESTDB /data/temp3/redo03.log

5) Log Miner 를 시작해서 log 를 분석한 후 결과를 조회

SQL> exec dbms_logmnr.start_logmnr (dictfilename=>'/app/oracle/logminer/dict.dat',-

> options=>dbms_logmnr.ddl_dict_tracking+dbms_logmnr.committed_data_only);

PL/SQL procedure successfully completed.

SQL> col username for a10

SQL> col operation for a10

SQL> col sql_redo for a50

SQL> alter session set nls_date_format='YYYY-MM-DD:HH24:MI:SS' ;

Session altered.

Log Miner 실습하기 2 - Update table 시간 확인하기

step1. 현재 상태 조회하기

SQL> @log

6 ;

GROUP# MEMBER MB seq STATUS arc

------ --------------------------------------------- -----

1 /data/temp3/redo01.log 5 19 INACTIVE YES

2 /data/temp3/redo02.log 5 20 INACTIVE YES

3 /data/temp3/redo03.log 5 21 CURRENT NO <-- redo03 에서 작업

SQL> select supplemental_log_data_min from v$database;

SUPPLEME

--------

YES

step2. 신규 테이블 생성 후 데이터 입력하고 update

SQL> create table scott.test2(no number,name varchar2(10));

Table created.

SQL> insert into scott.test2 values(1,'aaa');

1 row created.

SQL> insert into scott.test2 values(2,'bbb');

1 row created.

SQL> insert into scott.test2 values(3,'ccc');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from scott.test2;

NO NAME

---------- ----------

1 aaa

2 bbb

3 ccc

SQL> update scott.test2 set name='ddd';

3 rows updated.

SQL> select * from scott.test2;

NO NAME

---------- ----------

1 ddd

2 ddd

3 ddd

SQL> commit;

Commit complete.

step3. Log Miner 딕셔너리를 생성

SQL> show parameter utl

NAME TYPE VALUE

---------------------------------------------

create_stored_outlines string

utl_file_dir string /app/oracle/logminer

SQL> exec dbms_logmnr_d.build(dictionary_filename =>'dict2.dat',-

> dictionary_location=>'/app/oracle/logminer');

PL/SQL procedure successfully completed.

step4. 분석을 해야 할 Redo Log File 을 등록한 후 분석

SQL> @log

6 ;

GROUP# MEMBER MB seq STATUS arc

------ --------------------------------------------- ----

1 /data/temp3/redo01.log 5 19 INACTIVE YES

2 /data/temp3/redo02.log 5 20 INACTIVE YES

3 /data/temp3/redo03.log 5 21 CURRENT NO

SQL> select db_name,filename from v$logmnr_logs; <--3번이 current 이기에 3번 등록

DB_NAME FILENAME

--------------- --------------------------------------------------

TESTDB /data/temp3/redo03.log

SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'/app/oracle/logminer/dict2.dat')

PL/SQL procedure successfully completed.

step5. 분석결과 조회

SQL> alter session set nls_date_format='yyyy-mm-dd:hh24:mi:ss';

Session altered.

SQL> select timestamp,username,operation,sql_redo

2 from v$logmnr_contents

3 where seg_name='TEST2'

4 ;

저작자표시 변경금지

'오라클 > 백업/복구' 카테고리의 다른 글

sql loader (0)	2014.11.10
데이터 이동하기 (0)	2014.11.06
Redo log flle 장애 처리방법 (0)	2014.11.05
control file, data file 연습문제 (0)	2014.11.04
Control file 복구 (0)	2014.11.04
Data file Recovery (불완전 복구) (0)	2014.11.04
Data File Recovery (0)	2014.10.30

control file, data file 연습문제

김포춘 2014. 11. 4. 18:19

2014. 11. 4. 18:19

Control file 연습문제

step 1. Closed Backup 으로 받은 데이터 파일을 사용해서 DB를 OPEN

작업 전 수행 사항

- 기존 백업 모두 삭제 rm -rf /data//backup/close/*

- control file 만 백업 cp /data/temp6/*.ctl /data/backup/close

- DB 오픈해서 스위치로그, 체크포인트 일으키기

SYS> startup

SYS> alter system switch logfile ;

SYS> /

SYS> alter system checkpoint ;

SYS> /

SYS> shut immediate ;

- 데이터 파일만 백업 cp /data/temp6/*.dbf /data/backup/close/

만일의 경우를 대비해 전체 백업을 해둠 cp /data/temp6/* /data/backup/close2/

- 운영중인 모든 파일 삭제해서 장애를 만듬 rm -rf /data/temp6/*

- 백업된 data file 복원 cp /data/backup/close/*.dbf /data/temp6/

- 백업된 control file 복원 cp /data/backup/close/*.ctl /data/temp6/

복구 시작

Redo log file 이 없기 때문에 resetlogs 모드로 control file 을 재생성 하기로 한다.

STARTUP 을 해주면 old control file 에러가 나옴

control file 을 재생성하는 스크립트를 만들자 .

스크립트를 생성하고 DB 종료후 vi editor 로 스크립트를 수정

스크립트 화면에서 필요없는 내용은 다지우고 아래화면 과같이 RESETLOGS 에 관련된 내용만 남김

반드시 아래 data file 경로에 해당 데이터파일이 들어있어야 한다.

스크립트를 실행하면 자동으로 mount 상태로 됨

resetlogs 옵션을 줘서 DB를 OPEN 과 동시에 redo log file 도 생성됨

끝!

Data file 연습 문제

step 1. truncate table 장애 복구하기

- 준비상황 -

1. 현재 상황 닫힌 백업 수행

2. OPEN 해서 아래와 같이 테스트용 테이블 스페이스와 테이블 생성

SYS> create tablespace test

2 datafile '/data/temp6/test01.dbf' to size 5m ;

SYS> create table scott.tt111 (no number) tablespace test ;

SYS> insert into scott.tt111 values(1) ;

SYS> commit ;

SYS> select to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS' ) from dual ; <-- 복구에 필요한 시간 확인하기 위해

SYS> select * from scott.tt111 ; <-- 데이터 잘 들어 있는지 확인

SYS> ! rm /data/test6/test01.dbf <-- 파일을 지움으로서 장애 발생

SYS> ! ls /data/test6/test01.dbf <-- 파일 있는지 확인 결과는 당연히 없음

SYS> truncate table scott.tt111 ; <-- truncate 로 table 삭제

SYS> select * from scott.tt111 ; <-- 지워져서 안나옴

- 복구시작 -

※ 임시경로 /data/temp 를 사용하여 복구하고 테스트용 테이블 스페이스 test의 백업파일이 없다는 상황 인지

SYS> shut immediate <--복구하기 위해 DB를 꺼준다

임시경로로 backup 된 data file , 운영중이던 control file, redo log file 을 복사

SYS> cp /data/backup/close/*.dbf /data/temp/

SYS> cp /data/temp6/*.ctl /data/temp/

SYS> cp /data/temp6/*.log /data/temp/

SYS> ! vi $ORACLE_HOME/dbs/inittestdb.ora <-- control file 경로 변경

SYS> startup mount <-- mount 로 open

SYS> alter database rename file '/data/temp6/system01.dbf'

2 to '/data/temp/system01.dbf' ; <-- data file 들 rename (삭제된 test01.dbf 는 rename하지않음)

SYS> alter database rename file '/data/temp6/redo01.log'

2 to '/data/temp/redo01.log' ; <-- redo log file 들 rename

SYS> alter database create datafile '/data/temp6/test01.dbf'

2 as '/data/temp/test01.dbf' ; <-- 삭제된 백업파일을 생성

SYS> recover database until time '2014-11-04:11:11:11' ; <-- 확인했던 복구 시간으로 복구

SYS> alter database open resetlogs ;

SYS> select * from scott.tt111 ;

----------

1 <-- 제대로 복구 잘됨

step 2. drop user 장애 복구하기 ( 삭제된 tuser 를 복구해서 tuser.test1 이 조회되도록 하시오 )

- 준비 상황 -

1. 현재 상태를 확인한 후 전체 파일 백업

2. 정상 open 한 후 test 용 계정 tuser 를 아래와 같이 생성 후 권한을 할당

SYS> create user tuser

2 identified by abc123

3 default tablespace users

4 temporary tablespace temp ;

SYS> grant connect, resource to tuser ;

3. 새로 생성한 tuser 로 로그인해서 테스트용 테이블을 생성

SYS> conn tuser/abc123

TUSER> create table test1 (no number) ;

TUSER> insert into test1 values (1) ;

TUSER> commit ;

TUSER> create table test2 (no number) ;

TUSER> insert into test2 (no number) ;

TUSER> commit ;

TUSER> select to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS') from dual ; <-- 복구할 시간 확인

4. sys 계정으로 로그인하여 tuser 를 삭제

TUSER> conn sys/oracle as sysdba

SYS> drop user tuser cascade ;

SYS> select * from tuser.test1 ; <-- 아무것도 조회되지 않음

- 복구 시작 -

[oracle@localhost data]$ mkdir temp2

[oracle@localhost data]$ cp /data/backup/close/*.dbf /data/temp2/

[oracle@localhost data]$ cp /data/temp/*.ctl /data/temp2/

[oracle@localhost data]$ cp /data/temp/*.log /data/temp2/

[oracle@localhost data]$ vi /app/oracle/product/11g/dbs/inittestdb.ora <--컨트롤 파일 경로 변경

[oracle@localhost data]$ exit

SQL> startup mount

ORACLE instance started.

Total System Global Area 422670336 bytes

Fixed Size 1344616 bytes

Variable Size 293604248 bytes

Database Buffers 121634816 bytes

Redo Buffers 6086656 bytes

Database mounted.

SQL> alter database rename file '/data/temp/system01.dbf' to '/data/temp2/system01.dbf' ; <--데이터파일 rename

Database altered.

SQL> alter database rename file '/data/temp/redo01.log' to '/data/temp2/redo01.log' ; < -- 로그파일 rename

Database altered.

SQL> recover database until time'2014-10-17:10:14:04';

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select * from tuser.test1;

----------

step 3. 아래에서 D시점에 삭제된 scott.tt890 table 을 복구하시오

A 단계

SYS> create tablespace test2

2 datafile '/data/temp2/test02.dbf' size 5m ;

B 단계

SYS> shut immediate ;

SYS> !cp /data/temp2/* /data/backup/close/

C 단계

SQL> startup

ORACLE instance started.

Total System Global Area 422670336 bytes

Fixed Size 1344616 bytes

Variable Size 293604248 bytes

Database Buffers 121634816 bytes

Redo Buffers 6086656 bytes

Database mounted.

Database opened.

SQL> create table scott.tt890 ( no number ) tablespace test2;

Table created.

SQL> insert into scott.tt890 values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from scott.tt890;

----------

SQL> select to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'YY

-------------------

2014-10-17:10:41:07

D 단계

SQL> drop table scott.tt890 purge;

Table dropped.

SQL> select * from scott.tt890;

select * from scott.tt890

ERROR at line 1:

ORA-00942: table or view does not exist

E 단계

SQL> drop tablespace test2 ;

Tablespace dropped.

SQL> @dd

TABLESPACE_NAME MB FILE_NAME

-------------------- ---------- ----------------------------------------

EXAMPLE 345.625 /data/temp2/example01.dbf

USERS 7.5 /data/temp2/users01.dbf

UNDOTBS1 265 /data/temp2/undotbs01.dbf

SYSAUX 580 /data/temp2/sysaux01.dbf

SYSTEM 710 /data/temp2/system01.dbf

- 복구 시작 -

SQL> shut immediate

SQL> !

[oracle@localhost data]$ cp /data/backup/close/*.dbf /data/temp3/ <-- 백업한 데이터파일 복사

[oracle@localhost ~]$ cp /data/temp2/*.log /data/temp3 <-- 운영중이던 리두로그파일 복사

[oracle@localhost ~]$ cp /data/backup/close/*.ctl /data/temp3/ <-- 백업한 컨트롤파일 복사

[oracle@localhost ~]$ vi /app/oracle/product/11g/dbs/inittestdb.ora <-- 컨트롤파일 경로 수정

[oracle@localhost ~]$ exit

SQL> startup mount

ORACLE instance started.

Total System Global Area 422670336 bytes

Fixed Size 1344616 bytes

Variable Size 293604248 bytes

Database Buffers 121634816 bytes

Redo Buffers 6086656 bytes

Database mounted.

SQL> alter database rename file '/data/temp2/system01.dbf' to '/data/temp3/system01.dbf';

<-- data file 들 모두 rename

Database altered.

SQL> alter database rename file '/data/temp2/redo01.log' to '/data/temp2/redo01.log' ;

<-- redo log file 들 모두 rename

Database altered.

SQL> @log

6 ;

GROUP# MEMBER MB seq STATUS arc

------ --------------------------------------------- ---- ---------- -------- -----

1 /data/temp3/redo01.log 5 1 CURRENT NO

2 /data/temp3/redo02.log 5 0 UNUSED YES

3 /data/temp3/redo03.log 5 0 UNUSED YES

SQL> recover database until time '2014-10-17:10:41:07' using backup controlfile;

<-- 컨트롤 파일을 백업파일로 썼기 때문에 using backup controlfile 옵션을 사용

ORA-00279: change 1390324 generated at 10/17/2014 10:36:13 needed for thread 1

ORA-00289: suggestion : /data/arc2/1_1_861185978.arc

ORA-00280: change 1390324 for thread 1 is in sequence #1 <--seq 넘버 1인 redo 파일 찾아서 입력

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/data/temp3/redo01.log

Log applied.

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select * from scott.tt890;

----------

복구 끝!

저작자표시 변경금지

'오라클 > 백업/복구' 카테고리의 다른 글

데이터 이동하기 (0)	2014.11.06
Redo log flle 장애 처리방법 (0)	2014.11.05
Log Miner 활용하기 (0)	2014.11.05
Control file 복구 (0)	2014.11.04
Data file Recovery (불완전 복구) (0)	2014.11.04
Data File Recovery (0)	2014.10.30
Backup (cold, hot) (0)	2014.10.30

Control file 복구

김포춘 2014. 11. 4. 16:20

2014. 11. 4. 16:20

Control file 장애 상황

1. Parameter file 의 경로와 실제 파일 경로가 다른 경우

2. Control file 끼리 내용이 서로 다른 경우

3. Old control file 또는 control file 이 삭제되었을 경우

1. Parameter file 의 경로와 실제 파일 경로가 다른 경우

해결 방법 : Parameter file 의 내용을 수정하거나 , Parameter file에 안들어가 있는 control file 을 추가해주면 됨

2. Control file 끼리 내용이 서로 다른 경우

해결 방법 : Control file 의 version 넘버가 큰 파일을 작은 파일로 덮어쓰면 됨

3. Old control file 또는 control file 이 삭제되었을 경우

- 백업 데이터 파일과 Archive redo log file 을 사용하여 using backup control file 옵션으로 복구

- 위와 같은 방법이 불가능할 경우 control file 을 재생성

- 현재 redo log file에 손상이 없을 경우 - noresetlogs 모드로 재생성

- 현재 redo log file에 손상이 있을 경우 - resetlogs 모드로 재생성

NORESETLOGS 옵션으로 CONTROL FILE 재생성

DB MOUNT 상태에서 Control file 을 재생성하는 스크립트 생성

-> alter database backup controlfile to trace '/app/oracle/back.sql' ; <-- 경로와 이름은 아무렇게나 해도 됨

DB 를 종료하고 생성한 스크립트를 vi editor 로 열기

-> vi /app/oracle/back.sql

스크립트를 열면 내용이 상당히 많은데 필요한 부분만 빼고 다 지워주자

스크립트의 내용중 -- 부분은 주석이라는 의미

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "TESTDB" NORESETLOGS ARCHIVELOG

......

CHARACTER SET KO16MSWIN949

;

스크립트 내용중 위와 같이 STARTUP NOMOUNT가 스크립트 실행되는 첫 부분 이다.

아래 NORESETLOGS 라고 써있는 부분이 RESETLOGS 모드와 NORESETLOGS모드를 구분하는 부분

여기선 NORESETLOGS 모드로 재생성 함으로써 나머지 다 지움.

DB가 꺼진 상태에서 스크립트 실행

SYS> @/app/oracle/back.sql

스크립트가 실행되면서 control file 이 정상적으로 생성되었고 자동으로 mount 상태로 됨

SYS> alter database open

Control file 이 재생성되어 정상적으로 DB가 OPEN 됨

※스크립트로 Control file 재생성시 주의사항

1. DB가 RESETLOGS Mode 로 OPEN 되는지 NORESETLOGS Mode로 OPEN 되는지 구분하고 생성하시오

2. 스크립트 안에 공백이 있으면 생성 도중에 에러가 생김

3. NORESETLOGS 모드일 경우는 스크립트 내부의 Redo log file 이 전부 해당 경로에 있어야 함

4. RESETLOGS 모드일 경우 스크립트 내부의 Redo log file 이 없어도 reset logs 로 open 될 때 재생성 됨

5. 두 모드 공통적으로 데이터파일은 반드시 스크립트 내부의 경로에 실제 존재해야 함

6. Control file 의 생성위치는 파라미터 파일에 지정된 경로

Redo log file 까지 손상이 생겨 RESETLOGS 옵션으로 CONTROL FILE 재생성

DB MOUNT 상태에서 Control file 을 재생성하는 스크립트 생성

-> alter database backup controlfile to trace '/app/oracle/back2.sql' ; <-- 경로와 이름은 아무렇게나 해도 됨

DB 를 종료하고 생성한 스크립트를 vi editor 로 열기

-> vi /app/oracle/back2.sql

스크립트를 열면 내용이 상당히 많은데 필요한 부분만 빼고 다 지워주자

스크립트의 내용중 -- 부분은 주석이라는 의미

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "TESTDB" RESETLOGS ARCHIVELOG

......

CHARACTER SET KO16MSWIN949

;

스크립트 내용중 위와 같이 STARTUP NOMOUNT가 스크립트 실행되는 첫 부분 이다.

아래 NORESETLOGS 라고 써있는 부분이 RESETLOGS 모드와 NORESETLOGS모드를 구분하는 부분

여기선 NORESETLOGS 모드로 재생성 함으로써 나머지 다 지움.

DB가 꺼진 상태에서 스크립트 실행

SYS> @/app/oracle/back2.sql

스크립트가 실행되면서 control file 이 정상적으로 생성되었고 자동으로 mount 상태로 됨

SYS> alter database open resetlogs; <-- 이때 redo log file 이 재생성됨

Control file 이 재생성되어 정상적으로 DB가 OPEN 됨

저작자표시 변경금지

'오라클 > 백업/복구' 카테고리의 다른 글

Redo log flle 장애 처리방법 (0)	2014.11.05
Log Miner 활용하기 (0)	2014.11.05
control file, data file 연습문제 (0)	2014.11.04
Data file Recovery (불완전 복구) (0)	2014.11.04
Data File Recovery (0)	2014.10.30
Backup (cold, hot) (0)	2014.10.30
사용자 암호설정/ Archive log mode, No Archive log mode (0)	2014.10.29

Data file Recovery (불완전 복구)

김포춘 2014. 11. 4. 11:18

2014. 11. 4. 11:18

불완전 복구 = 특정 시점으로 복구하는 방법

상황 1. 1시에 전체파일 백업, 그후 3시에 tt820 테이블에 1 , AAA / 2, BBB / 3, CCC 라는 데이터 삽입,

5시에 잘못된 update 를 수행해 모든 데이터가 DDD로 변경 , commit 까지 한 상태라 rollback 불가

다시 원래 3시의 데이터로 백업

복구 순서 : 1. DB종료후 백업 실행 -> 2. OPEN 후 테스트용 테이블 tt820, 데이터 생성 -> 3. 잘못된 업데이트 수행후 comit 하여 장애상황 만듬 -> 4. DB종료 -> 5. 임시 디렉토리 생성해서 백업한 datafile , 운영중이던 redo log file, control file 을 복사 -> 6. control file 경로 변경후 startup mount -> 7. rename 으로 data file 과 redo log file 경로 변경 -> 8.update가 일어나기 전 시간으로 복구 -> 9. DB OPEN

1. SYS> shutdown immediate ;

SYS> !cp /app/oracle/oradata/testdb/* /data/backup/close/

2. SYS> startup

SYS> create table scott.tt820 ( no number, name varchar2(10) ) tablespace users ;

SYS> insert into values (1, 'aaa')

SYS> insert into values (2, 'bbb')

SYS> insert into values (3, 'ccc')

SYS> commit ;

SYS> select to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS') from dual ;

TO_CHAR(SYSDATE,'

------------------

2014-11-04:03:00:00 < -- 테이블 생성했을때 시간이 3시임을 확인 이시간까지 데이터 존재

SYS> select * from scott.tt820

NUMBER NAME

----------------

1 AAA

2 BBB

3 CCC

3. SYS> update scott.tt820 set name = 'DDD' ; <-- where 절 하지않아 전체 데이터가 DDD로 업데이트

SYS> select * from scott.tt820

NUMBER NAME

----------------

1 DDD

2 DDD

3 DDD

SYS> commit ; <-- commit 을 해서 이제 rollback 안됨

4. SYS> shutdown immediate <-- 복구를 위해 DB 종료

5. SYS> !mkdir /data/tmp <-- 복구 경로로 /data/temp 사용하기위해 디렉토리 미리 생성

SYS> cp /data/backup/close/*.dbf /data/temp <-- 백업파일 복사

SYS> cp /app/oracle/oradata/testdb/*.ctl /data/temp <-- 운영중이던 파일 복사

SYS> cp /app/oracle/oradata/testdb/*.log /data/temp <-- 운영중이던 파일 복사

6. SYS>! vi $ORACLE_HOME/dbs/inittestdb.ora

...

control_files = '/data/temp/control01.ctl' <-- 컨트롤 파일 경로 변경

...

SYS> startup mount

7. SYS> alter database rename file '/app/oracle/oradata/testdb/system01.dbf'

2 to '/data/temp/system01.dbf' ; <-- 이런식으로 data file 의 경로 변경

SYS> alter database rename file '/app/oracle/oradata/testdb/redo01.log'

2 to '/data/temp/redo01.log' ; <-- 이런식으로 redo log file 의 경로 변경

8. SYS> recover database until time '2014-11-04:03:00:00' ; <-- until time 옵션을 줘서 해당 시간으로 복구

9. SYS> alter database open resetlogs <-- 파일들의 scn이 맞지않아 정상적으로는 open 되지 않아 resetlogs 옵션사용

SYS> select * from scott.tt820 ;

SYS> select * from scott.tt820

NUMBER NAME

----------------

1 AAA

2 BBB

3 CCC <-- 3시에 수행됬던 데이터로 복구 됨

상황 2. 잘못된 drop tablespace 복구하기 -backup 파일 있을 경우

순서 : 1. 새로운 tablespace 생성 -> 2. DB 종료후 백업실행 -> 3. DB 오픈후 Test용 tablespace 삭제 -> 4. DB 종료후 임시 복구 경로로 필요 파일 복사 -> 5. 파일들의 경로 변경 -> 6. 복구

1. SYS> create tablespace test

2 datafile '/app/oracle/oradata/testdb/test01.dbf' size 10M ;

2. SYS> shut immediate;

SYS>! cp /app/oracle/oradata/testdb/* /data/backup/close/

3. SYS> startup

SYS> create table scott.tt850 (no number) tablespace test;

SYS> insert into scott.tt850 values (1) ;

SYS> commit;

SYS> drop tablespace test including contents and datafiles; <--drop 해서 장애 발생

4. SYS> shut immediate ;

SYS>! mkdir /data/temp2 <-- 복구 하기 위한 임시경로

SYS>! cp /data/backup/close/*.ctl /data/temp2/ <-- 백업 파일 사용

SYS>! cp /data/backup/close/*.dbf /data/temp2/ <-- 백업 파일 사용

SYS>! cp /app/oracle/oradata/testdb/*.log /data/temp2/ <-- 운영중이던 파일 사용

5. SYS>! vi $ORACLE_HOME/dbs/inittestdb.ora <-- 컨트롤 파일 경로 변경

SYS> startup mount

SYS> alter database rename file '/app/oracle/oradata/testdb/system01.dbf'

2 to '/data/temp2/system01.dbf' ; <-- 데이터 파일 경로 변경

SYS> alter database rename file '/app/oracle/oradata/testdb/redo01.log'

2 to '/data/temp2/redo01.log' ; <-- 리두 로그 파일 경로 변경

6. drop tablespace 된 시점을 확인하기위해 alert log file 에서 찾기 (drop 된 시간을 2014-11-04:06:00:00 라고 가정)

SYS> recover database until time '2014-11-04:06:00:00' using backup controlfile ;

ORA-00279: change 1373367 generated at 11/04/2014 05:53:12 needed for thread 1

ORA-00289: suggestion : /data/arc2/1_1_861154526.arc

ORA-00280: change 1373367 for thread 1 is in sequence #1

※ 복구를 위해 백업된 controlfile 을 가져왔기 때문에 using backup controlfile 옵션을 사용

그 후 sequence #1 번인 archive log file 을 찾는데 파일이 없어서 위와 같은 에러가 나옴

redo log 상태 조회해서 seq #1 번인 리두로그 파일을 지정 해주면 됨

SYS> recover database until time '2014-11-04:06:00:00' using backup controlfile ;

ORA-00279: change 1373367 generated at 10/17/2014 02:43:12 needed for thread 1

ORA-00289: suggestion : /data/arc2/1_1_861154526.arc

ORA-00280: change 1373367 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/data/temp6/redo01.log

SYS> alter database open resetlogs ;

복구 끝!

상황 3. 상황 2. 잘못된 drop tablespace 복구하기 -backup 파일 없을 경우

순서 : 1. DB 종료후 백업실행 -> 2. DB 오픈 후 새로운 tablespace 생성 3. Test용 tablespace 삭제 -> 4. DB 종료후 임시 복구 경로로 필요 파일 복사 -> 5. 파일들의 경로 변경 -> 6. 복구

1. SYS> shut immediate;

SYS>! cp /app/oracle/oradata/testdb/* /data/backup/close/

2. SYS> startup

SYS> create tablespace test2

2 datafile '/app/oracle/oradata/testdb/test02.dbf' size 10M ;

SYS> create table scott.tt860 (no number) tablespace test2;

SYS> insert into scott.tt860 values (1) ;

SYS> commit;

3. SYS> drop tablespace test including contents and datafiles; <--drop 해서 장애 발생

4. SYS> shut immediate ;

SYS>! mkdir /data/temp3 <-- 복구 하기 위한 임시경로

SYS>! cp /data/backup/close/*.ctl /data/temp3/ <-- 백업 파일 사용

SYS>! cp /data/backup/close/*.dbf /data/temp3/ <-- 백업 파일 사용

SYS>! cp /app/oracle/oradata/testdb/*.log /data/temp3/ <-- 운영중이던 파일 사용

5. SYS>! vi $ORACLE_HOME/dbs/inittestdb.ora <-- 컨트롤 파일 경로 변경

SYS> startup mount

SYS> alter database rename file '/app/oracle/oradata/testdb/system01.dbf'

2 to '/data/temp3/system01.dbf' ; <-- 데이터 파일 경로 변경

- 경로를 변경하는데 복구 필요한 test2 tablespace는 없음

SYS> alter database rename file '/app/oracle/oradata/testdb/redo01.log'

2 to '/data/temp3/redo01.log' ; <-- 리두 로그 파일 경로 변경

6. drop tablespace 된 시점을 확인하기위해 alert log file 에서 찾기 (drop 된 시간을 2014-11-04:08:00:00 라고 가정)

SYS> recover database until time '2014-11-04:08:00:00' using backup controlfile ;

ORA-00279: change 1373367 generated at 11/04/2014 05:53:12 needed for thread 1

ORA-00289: suggestion : /data/arc2/1_1_861154526.arc

ORA-00280: change 1373367 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/data/temp6/redo01.log

ORA-00283: recovery session canceled due to errors

ORA-01244: unnamed datafile(s) added to control file by media recovery

ORA-01110: data file 8: '/data/temp/test03.dbf'

위 에러메시지를 확인하면 unnamed datafile 이 control file 에 추가되었다고 알려줌

SYS> select name from v$datafile ; <-- 데이터파일을 확인하면 추가된것을 확인할 수 있다.

NAME

-----------------------------------

/data/temp3/system01.dbf

/data/temp3/sysaux01.dbf

/data/temp3/undotbs01.dbf

/data/temp3/users01.dbf

/data/temp3/example01.dbf

/data/temp3/test.dbf

/app/oracle/product/11g/dbs/UNNAMED00006 <-- 생김

UNNAMED0006 파일을 새로운 경로에 생성 한 후 다시 복구 시도

SYS> alter database create datafile '/app/oracle/product/11g/dbs/UNNAMED00006'

2 as '/data/temp2/test02.dbf' ;

SYS> recover database un until time '2014-11-04:08:00:00' using backup controlfile ;

ORA-00279: change 1373367 generated at 11/04/2014 05:53:12 needed for thread 1

ORA-00289: suggestion : /data/arc2/1_1_861154526.arc

ORA-00280: change 1373367 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/data/temp6/redo01.log

SYS> alter database open resetlogs ;

복구 완료!

저작자표시 변경금지

'오라클 > 백업/복구' 카테고리의 다른 글

Redo log flle 장애 처리방법 (0)	2014.11.05
Log Miner 활용하기 (0)	2014.11.05
control file, data file 연습문제 (0)	2014.11.04
Control file 복구 (0)	2014.11.04
Data File Recovery (0)	2014.10.30
Backup (cold, hot) (0)	2014.10.30
사용자 암호설정/ Archive log mode, No Archive log mode (0)	2014.10.29

Data File Recovery

김포춘 2014. 10. 30. 17:18

2014. 10. 30. 17:18

1. Restore & Recovery

Restore : backup 받았던 파일을 복원 시키는 것 ( 백업된 후부터 망가지기 전까지의 데이터는 다 날아감 )

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

복구 원리 : 1. rccover 명령 -> control file 정보 확인 -> data file 정보 확인 -> control file 과 data file 정보가 다를 경우 redo log 파일 또는 archived log file 의 정보를 적용시켜 복구 완료함

복구 명령어 : 1. recover database ;

2. recover tablespace useres ;

3. recover datafile '/app/oracle/oradata/testdb/users01.dbf' ;

< No Archive Log Mode >

2. 데이터 파일 일부 삭제된 후 복구하기 ( 노 아카이브 모드에서 복구 가능할 때, 리두로그에 남아있어서 )

장애 상황 : 현재 운영중인 DB 서버에서 누군가가 /app/oracle/oradata/testdb/users01.dbf 파일을 지운것을 확인했다.

현재 운영중인 DB서버는 No Archive log mode이다. 이 경우 복구방법은 지워지기전 백업했던 데이터파일,

컨트롤파일, redo log file 까지 전부 복원 하는 것 뿐

- no archive mode , db open 상태 -

SYS> !rm /app/oracle/oradata/testdb/users01.dbf

SYS> create table scott.tt100(no number) tablespace users ;

Table created

SYS> insert into scott.tt100 value(1) ;

1 row created.

SYS> commit ;

SYS> select * from scott.tt100 ;

------

SYS> alter system switch logfile ;

SYS> /

ERROR at line 1:

ORA-03113:end-of-file on communication channel

Process ID:7520

Session ID : 1 Serial number: 5

SYS> exit

[oracle@localhost ~]$ sqlplus sys/oracle as sysdba

SYS> startup <-- 에러나서 mount 로 statup 됨

SYS> !cp /data/backup/close/users01.dbf /app/oracle/oradata/testdb/ <-- 백업파일을 다시 복원

SYS> recover tablespace users ; <- users 복구

Media recovery complete

SYS> alter database open ;

3. 삭제된 데이터파일을 복구하지않고 DB 시작하기 ( 노아카이브 모드에서 복구 불가능할 때, 아카이브 로그 없는 경우 )

장애상황 : No archive log mode 로 운영중인 데이터베이스에서 데이터파일이 하나 지워졌다.

때마침 checkpoint 가 발생되어 운영중인 DB가 갑자기 강제 종료가 되어 버렸다.

다시 접속해서 DB를 재 시작 시키려고 하니 지워진 파일 때문에 시작이 되지 않는다.

다행히 데이터파일이 지워진 테이블스페이스는 중요한 자료가 없어서 삭제되어도 괜찮고

빨리 DB 를 시작시켜야만 한다.

- no archive mode , db open 상태 -

SYS> alter system switch logfile ;

SYS> / <- 여러번 로그스위치 해줌

SYS> !rm -rf /app/oracle/oradata/testdb/user01.dbf

SYS> create table scott.tt200(no number) tablespace users ;

SYS> insert into scott.tt200 values (2) ;

SYS> commit ;

SYS> select * from scott.tt200 ;

------

SYS> alter system switch logfile ;

SYS> /

SYS> shutdown immediate ;

SYS> startup

SYS> !cp /data/backup/close/users01.dbf /app/oracle/oradata/testdb/

recover tablespace users;

ORA-00279: change 2206096 generated at 09/19/2013 05:07:44 needed for thread 1

ORA-00289: suggestion : /data/arc2/6_1_812624262.arc

ORA-00280: change 2206096 for thread 1 is in sequence #6

Specify log: {<RET>=suggested | filename | AUTO | CANCEL} 엔터

에러발생

SYS> alter database datafile '/app/oracle/oradata/testdb/users01.dbf' offline drop ;

SYS> alter database open ;

4. 데이터 파일의 경로를 변경해서 복구하기

장애 상황 : Data file 이 있는 디렉토리의 손상으로 긴급히 다른 임시 디렉토리를 만들고 그 곳으로

Data file 을 이동시키고 복원해야 합니다

파일 위치 : -data file : /data/temp 로 이동시키고 복구

-control file , redo log file : /app/oracle/oradata/testdb/

-backup file : /data/backup/close/

작업 순서 :

1. DB 종료

2. /data/temp 디렉토리 생성해서 /data/backup/close/*.dbf 파일을 복사

3. /data/backup/close/*.log , *.ctl 을 /app/oracle/oradata/testdb 로 이동

4. 컨트롤 파일 내용 수정

5. DB 오픈

SYS> shutdown immediate ;

oracle $ mkdir /data/temp

oracle $ cp /data/backup/close/*.dbf /data/temp 로 복사

oracle $ cp /data/backup/close/*.ctl /app/oracle/oradata/testdb/

oracle $ cp /data/backup/close/*.log /app/oracle/oradata/testdb/

SYS> startup mount

SYS> !cp /data/backup/close/*.dbf /app/oracle/oradata/testdb/ <-- data file 복원

SYS> alter database rename file '/app/oracle/oradata/testdb/system01.dbf‘ to '/data/temp/system01.dbf';

Database altered.

SYS> alter database rename file '/app/oracle/oradata/testdb/undotbs01.dbf‘ to '/data/temp/undotbs01.dbf';

Database altered.

SYS> alter database rename file '/app/oracle/oradata/testdb/sysaux01.dbf‘ to '/data/temp/sysaux01.dbf';

Database altered.

SYS> alter database rename file '/app/oracle/oradata/testdb/users01.dbf‘ to '/data/temp/users01.dbf';

Database altered.

SYS> alter database rename file '/app/oracle/oradata/testdb/example01.dbf‘ to '/data/temp/example01.dbf';

Database altered.

SYS> alter database open;

Database altered.

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

TABLESPACE MB FILE_NAME

---------- ---------- -----------------------

USERS 5 /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

< Archive Log Mode - 완전 복구>

아카이브 로그 파일을 이용해서 백업파일에서 장애 난 파일 까지의 데이터를 완전하게 복구 할 수 있다.

실습전 archive 모드로 변경 해주고 파일 다시 백업. 실습을 위해 test2 tablespace 생성해줌

SYS> alter database archivelog ;

SYS> create tablespace test2

2 datafile '/app/oracle/oradata/testdb/test02.dbf' size 5M ;

SYS> shutdown immediate ;

SYS> !cp /app/oracle/oradata/testdb/* /data/backup/close/

1. offline 되는 tablespace 장애 복구

SYS> startup

SYS> create table scott.tt750 (no number) tablespace test2 ;

SYS> insert into scott.tt750 values (1) ;

SYS> commit ;

SYS> select * from secott.tt750 ;

---

SYS> !rm /app/oracle/oradata/testdb/test02.dbf

SYS> alter tablespace test2 offline ;

SYS> alter tablespace test2 online ;

ERROR at line 1:

ORA-011547: cannot identify/loca data file 6 - see DBWR trace file

ORA-01110: data file 6 : '/app/oracle/oradata/testdb/test02.dbf'

SYS> !cp /data/backup/close/test02.dbf /app/oracle/oradata/testdb/

SYS> alter tablerspace test2 online ;

ERROR at line 1:

ORA-01113: file 6 needs media recovery

ORA-01110: data file 6: '/app/oracle/oradata/testdb/test02.dbf'

백업 파일을 복원했는데 online 안되고 복구하라는 에러 메세지가 나옴

SYS> recover tablespace test2;

Media recovery complete.

SYS> alter tablespace test2 online;

Tablespace altered.

SQL> select * from scott.tt750;

----------

OFFLINE 되는 Tablespace 는 DB 종료없이 해당 Tablespace 만 offline 하고 복구하면 됨

2. offline 안되는 tablespace 장애 복구

SQL> create table scott.tt800 (no number) tablespace system;

Table created.

SQL> insert into scott.tt800 values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from scott.tt800;

----------

SQL> !rm /app/oracle/oradata/testdb/system01.dbf

SQL> altert tablespace system offline;

SP2-0734: unknown command beginning "altert tab..." - rest of line ignored.

SQL> alter tablespace system offline;

alter tablespace system offline

ERROR at line 1:

ORA-01541: system tablespace cannot be brought offline; shut down if necessary

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 422670336 bytes

Fixed Size 1344616 bytes

Variable Size 293604248 bytes

Database Buffers 121634816 bytes

Redo Buffers 6086656 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/app/oracle/oradata/testdb/system01.dbf'

SQL> !cp /data/backup/close/system01.dbf /app/oracle/oradata/testdb/

SQL> recover tablespace system;

Media recovery complete.

SQL> alter database open;

Database altered.

SQL> select * from scott.tt800;

----------

3. 백업파일이 없는 상태에서 복구

SQL> @dd

TABLESPACE_NAME MB FILE_NAME

-------------------- ------ ----------------------------------------

TEST2 5 /app/oracle/oradata/testdb/test02.dbf <--파일삭제

EXAMPLE 345.625 /app/oracle/oradata/testdb/example01.dbf

USERS 7.5 /app/oracle/oradata/testdb/users01.dbf

UNDOTBS1 265 /app/oracle/oradata/testdb/undotbs01.dbf

SYSAUX 570 /app/oracle/oradata/testdb/sysaux01.dbf

SYSTEM 710 /app/oracle/oradata/testdb/system01.dbf

6 rows selected.

SQL> !rm -f /app/oracle/oradata/testdb/test02.dbf

SQL> create table scott.tt810 ( no number) tablespace test2;

Table created.

SQL> insert into scott.tt810 values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> alter tablespace test2 offline;

Tablespace altered.

SQL> alter tablespace test2 online;

alter tablespace test2 online

ERROR at line 1:

ORA-01157: cannot identify/lock data file 6 - see DBWR trace file

ORA-01110: data file 6: '/app/oracle/oradata/testdb/test02.dbf'

SQL> @dd

TABLESPACE_NAME MB FILE_NAME

-------------------- ------ ----------------------------------------

TEST2 /app/oracle/oradata/testdb/test02.dbf

EXAMPLE 345.625 /app/oracle/oradata/testdb/example01.dbf

USERS 7.5 /app/oracle/oradata/testdb/users01.dbf

UNDOTBS1 265 /app/oracle/oradata/testdb/undotbs01.dbf

SYSAUX 570 /app/oracle/oradata/testdb/sysaux01.dbf

SYSTEM 710 /app/oracle/oradata/testdb/system01.dbf

6 rows selected.

SQL> alter database create datafile

2 '/app/oracle/oradata/testdb/test02.dbf' <--삭제 전 원래 경로

3 as '/home/oracle/test01.dbf'; <-- 새로 만들어질 경로와 이름

Database altered.

SQL> recover tablespace test2;

Media recovery complete.

SQL> alter tablespace test2 open;

alter tablespace test2 open

ERROR at line 1:

ORA-02142: missing or invalid ALTER TABLESPACE option

SQL> alter tablespace test2 online;

Tablespace altered.

SQL> select * from scott.tt810;

----------

저작자표시 변경금지

'오라클 > 백업/복구' 카테고리의 다른 글

Redo log flle 장애 처리방법 (0)	2014.11.05
Log Miner 활용하기 (0)	2014.11.05
control file, data file 연습문제 (0)	2014.11.04
Control file 복구 (0)	2014.11.04
Data file Recovery (불완전 복구) (0)	2014.11.04
Backup (cold, hot) (0)	2014.10.30
사용자 암호설정/ Archive log mode, No Archive log mode (0)	2014.10.29

Backup (cold, hot)

김포춘 2014. 10. 30. 12:00

2014. 10. 30. 12:00

1 . 닫힌 백업 ( cold backup , close backup )

- Database가 close 된 상태에서 수행 하는 백업 ( 백업 시점이 동일해야함, 다르면 scn이 달라서 백업이 안됨 )

- data file, online redo log file , control file 백업 가능

- open 상태에서 백업 불가

- archive mode, no arcive mode 상관없음

백업 실습

순서 : 1. 백업받을 파일의 경로 확인후 db 셧다운 -> 2. 원하는 경로에 파일 복사 -> 3. db오픈

SQL> shutdown immediate

SQL> !

[oracle@testdb ~] $ cp -av /app/oracle/oradata/testdb/*.dbf /data/backup/close

[oracle@testdb ~] $ cp -av /app/oracle/oradata/testdb/*.ctl /data/backup/close

[oracle@testdb ~] $ cp -av /app/oracle/oradata/testdb/*.log /data/backup/close

[oracle@testdb ~] $ cp -av $ORACLE_HOME/dbs /data/backup/close

[oracle@testdb ~] $ ls /data/backup/close

ctrl01.ctl ctrl03.ctl example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf

ctrl02.ctl dbs redo01.log redo03.log system01.dbf undotbs01.dbf

2. 열린 백업 ( hot backup, begin backup , open backup )

- database 가 open 된 상태에서 수행하는 백업

- archive log mode 에서만 수행가능

- 많은 양의 redo log 가 발생

- data file , control file 만 백업 가능

백업 실습

순서 : 1. 백업 해야할 tablespace 를 백업모드로 변경 -> 2. os 명령어로 해당 tablespace의 datafile 을 백업장소로 복사 -> 3. 파일 복사 완료후 백업모드 종료

SQL> archive log list <-- archive log mode 인지 확인 아닐경우 archive log mode 로 변경

SQL> !vi /app/oracle/open_backup.sql

alter tablespace users begin backup; <-- 백업모드로 변경

! cp -av /app/oracle/oradata/testdb/users01.dbf /data/backup/open/ <-- 파일 백업장소로 복사

alter tablespace users end backup; <-- 백업모드 종료

alter tablespace sysaux begin backup;

!cp -av /app/oracle/oradata/testdb/sysaux01.dbf /data/backup/open/

alter tablespace sysaux end backup;

alter tablespace undotbs1 begin backup;

!cp -av /app/oracle/oradata/testdb/undotbs01.dbf /data/backup/open/

alter tablespace undotbs1 end backup;

alter tablespace system begin backup;

!cp -av /app/oracle/oradata/testdb/system01.dbf /data/backup/open/

alter tablespace system end backup;

alter tablespace example begin backup;

!cp -av /app/oracle/oradata/testdb/example01.dbf /data/backup/open/

alter tablespace example end backup;

alter database backup controlfile to ‘/data/backup/open/control01.ctl’;

:wq!

SQL> @/app/oracle/open_backup.sql

백업 상태여부 조회

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 /app/oracle/oradata/testdb/system01.dbf NOT ACTIVE 2014-10-16:01:21:27

2 /app/oracle/oradata/testdb/sysaux01.dbf NOT ACTIVE 2014-10-16:01:20:47

3 /app/oracle/oradata/testdb/undotbs01.dbf NOT ACTIVE 2014-10-16:01:21:11

4 /app/oracle/oradata/testdb/users01.dbf NOT ACTIVE 2014-10-16:01:20:43

5 /app/oracle/oradata/testdb/example01.dbf NOT ACTIVE 2014-10-16:01:21:55

위 상태에서 STATUS 가 NOT ACTIVE 면 end backup 상태 ACTIVE 면 begin backup 상태 이므로 백업이 끝나면 확인해서 반드시 NOT ACTIVE 상태로 만들어 줘야 함

TIME 은 가장 마지막에 begin backup 을 받은 시간

※ Hot backup 구동 원리

- scn3 까지의 데이터를 Hot backup 중인 상태 -

이때 DML 작업이 일어나 D,E 가 추가되는 상황에서 data file 이 백업중이니 변경되는 내용은 Redo Log File 에 저장된다.

이때 Redo Log File 에 필요한 데이터만 저장되는게 아니라 블록 단위로 저장되어서 많은 양의 Redo Log 가 발생한다.

그래서 백업이 오래 지속되면 그 동안의 변경내용이 모두 리두로그파일에 저장되었다가 백업이 끝난후 다시 데이터파일에 적용해야 하기 때문에 데이터 손실을 막기 위해 아카이브 모드로 작동해야 한다.

End backup 을 해야 리두로그 파일에 블록단위로 안들어가고 필요한 데이터만 들어가므로 다량의 리두로그를 쌓지 않기 위해 반드시 begin 백업후에 end 백업을 해줘야 한다.

저작자표시 변경금지

'오라클 > 백업/복구' 카테고리의 다른 글

Redo log flle 장애 처리방법 (0)	2014.11.05
Log Miner 활용하기 (0)	2014.11.05
control file, data file 연습문제 (0)	2014.11.04
Control file 복구 (0)	2014.11.04
Data file Recovery (불완전 복구) (0)	2014.11.04
Data File Recovery (0)	2014.10.30
사용자 암호설정/ Archive log mode, No Archive log mode (0)	2014.10.29

사용자 암호설정/ Archive log mode, No Archive log mode

김포춘 2014. 10. 29. 17:54

2014. 10. 29. 17:54

오라클 접속시 사용자에게 암호 묻게 설정

sqlnet.ora 파일 설정

$ cd $ORACLE_HOME/network/admin

$ vi sqlnet.ora ( 11g 처음 설치시 없으니 netca 를 이용해서 추가 )

sqlnet.authentication_services=(none) 추가한 후 저장.

orapw 파일 = sys 계정암호 저장되어 있는 파일 나머지 암호 정보는 dba_users 딕셔너리에 저장되어 있음

orapw 파일이 삭제되었을 경우 조치법

$ cd /app/oracle/product/11g/dbs

$ rm -rf orapwtestdb <-- 삭제

$ sqlplus / as sysdba 실행시 에러발생

ERROR

ORA-01031 : insufficient privileges

$ orapwd file = /app/oracle/product/11g/dbs/orapwtestdb

Enter password for SYS : ( sys 의 새 암호를 지정 )

※ SYS의 암호를 잊어버렸을 경우 orapwtestdb 파일을 지우고 위와 같은 방법으로 암호파일을 재생성 해주면 된다.

Archive Log Mode & No Archive Log Mode

Archive Log Mode - 혹시모를 에러를 대비해서 redo log file 을 다른 장소로 한번 더 archive 하는 방법

장애시 복구 가능

No Archive Log Mode - redo log file 을 archive 하지않고 운영하는 방법 ( 오라클 설치시 기본 모드 )

장애시 복구 불가능

No Archive Log Mode -> Archive Log Mode

1. DB 종료 -> 2. Parameter File 수정 -> 3. DB Mount 로 시작 -> 4. mode 변경 -> 5. DB Open

( DB Open 상태라고 가정 )

step 1. DB 종료

SQL> shutdown immediate ;

step 2. parameter file 수정

< pfile 을 사용할 경우 >

SQL> !

$ vi $ORACLE_HOME/dbs/inittestdb.ora

(내용추가)

log_archive_start=true <-- 9i 까지만 사용 10g 이상버전에서는 사용 안함

log_archive_dest_1='location=/data/arc1' <-- 첫번째 저장경로 지정

log_archive_dest_2='location=/data/arc2' <-- 두번째 저장경로 지정

log_archive_format=%s_%t_%r.arc <-- 아카이브 로그파일 이름 지정

< spfile 을 사용할 경우 >

(DB OPEN 상태에서)

SYS> alter system set log_archive_dest_1='location=/data/arc1' scope=spfile ;

SYS> alter system set log_archive_dest_2='location=/data/arc2' scope=spfile ;

SYS> alter system set log_archive_format="%s_%t%r.arc" scope=spfile ;

SYS> shutdown immediate ;

step 3. DB Mount 로 시작

SYS> startup mount ;

step 4. archive log mode 확인 및 변경

SYS> archive log list ;

Database log mode No Archive Mode <-- 현재 모드 상태가 나옴

....

SYS> alter database archivelog ; <-- 아카이브 로그 모드로 변경

step 5. DB 오픈

SYS> alter database open ;

step 6. log switch 수행하여 파일 생성 여부 확인

SYS> alter system switch logfile ;

SYS> /

SYS> !ls /data/arc1

43_1_798054423.arc 44_1_798054423.arc 45_1_798054423.arc

저작자표시 변경금지

'오라클 > 백업/복구' 카테고리의 다른 글

Redo log flle 장애 처리방법 (0)	2014.11.05
Log Miner 활용하기 (0)	2014.11.05
control file, data file 연습문제 (0)	2014.11.04
Control file 복구 (0)	2014.11.04
Data file Recovery (불완전 복구) (0)	2014.11.04
Data File Recovery (0)	2014.10.30
Backup (cold, hot) (0)	2014.10.30

DBMS_JOB & DBMS_SCHEDULER

김포춘 2014. 10. 27. 16:40

2014. 10. 27. 16:40

DBMS_JOB & DBMS_SCHEDULER

= 정해진 시간에 반복적으로 작업을 수행할수 있게 해주는 기능

1. DBMS_JOB

- SNP 백그라운드 프로세스가 이 패키지에 설정되어 있는 작업을 수행

패키지

job_queue_process = 1 : SNP 프로세스의 개수를 지정
job_queue_interval = 60 : SNP 프로세스가 Sleep 상태에서 깨어나는 간격을 초로 지정

패키지에 있는 프로시저

submit : 새로운 작업을 job quere 목록에 등록
remove : job queue 에 등록된 job 을 제거
change : job queue 에 등록된 job 을 변경
next_date : job queue 에 등록된 job 의 작동시간을 변경
interval : job queue 에 등록된 job 의 수행 주기를 변경
what : 수행할 procedure or package 변경
run : 등록되어 있는 특정 job 을 수동으로 수행

1) job 관리하기

- 기본 문법

DBMS_JOB.submit (

job out binary_integer,

what in varchar2,

next_date in date default sysdate,

interval in varchar2 default 'null' ,

no_parse in boolean default false )

주요 파라미터 의미

job : job 번호로 다른 프로시저에서 호출될 수 있음
what : 수행할 pl/sql or procedure or package 이름을 지정, 직접 수행하기를 원하는 sql문 적어도 됨
next_date : 다음에 수행될 시간을 지정
interval : 수행되는 주기를 지정, 초단위까지 지정가능
no_parse : parse 여부를 지정, 기본값은 false 로 parse를 수행

실습 1. 새로운 job 등록 테스트하기

job_test01 이라는 테이블을 만든 후 1분에 1번씩 해당 테이블에 데이터를 insert 하는 작업

step 1. 먼저 테스트용 테이블과 sequence , procedure 생성

SCOTT> create sequence seq_job_seq1 ;

Sequence created

SCOTT> create table job_test01

2 ( no number ,

3 name varchar2(5)) ;

Table created

SCOTT> create or replace procedure insert_job_test01

2 is

3 begin

4 insert into scott.job_test01

5 values(seq_job_seq1.nextval , dbms_random.string('a',3)) ;

6 end ;

7 /

Procedure created

step 2. job을 등록할 프로시저를 생성

SCOTT> !vi job1.sql

1 begin

2 dbms_job.submit (:jno ,

3 'scott.insert_job_test01;',

4 sysdate ,

5 'sysdate +1/24/60' , <--1 분에 한번씩 실행

6 false) ;

7 end;

8 /

※ 위 5행의 시간 설정 방법

- 10분에 한번씩 실행하도록 지정

sysdate +1/24/6 or sysdate +1/144

일/시간/분

1440 / 24 / 6 or 1440 / 144 분단위로 계산

- 1분에 한번씩 지정

sysdate +1/24/60 or sysdate +1/1440

- 5분에 한번씩 지정

sysdate +5/24/60

- 매일 새벽 2시로 지정

trunc (sysdate) +2/24 -> 다음날 새벽 2시를 지정

( trunc 함수를 지정하는 이유는 분:초 단위까지 나오지 않게 하기 위해서 사용 )

step 3. job 등록

SCOTT> variable jno number ;

SCOTT> @job1.sql

PL/SQL procedure sucessfully completed.

SCOTT/print jno ;

JNO

------------

25 <-- 현재 할당된 JOB 번호 확인, 이 번호는 random으로 지정됨

SCOTT> commit ; <-- 이 순간부터 job이 수행됨, 즉 commit 이 안되면 job 수행 안됨

RAC 환경에서 특정 노드에 job 을 할당해야 할 경우

SCOTT> exec dbms_job.instance(job=>25, instance=>1) ;

step 4. 수행되고 있는 job 내역 확인

SCOTT> select what, job, next_date, next_sec, failures, broken

2 from user_jobs

3 where what = 'scott.insert_job_test01 ;' ;

step 5. 실제 테이블에 데이터가 입력되는지 확인

SCOTT> SELECT * FROM job_test01 ;

실습을 제대로 따라 왔으면 데이터가 1분에 하나씩 늘어나는것을 확인 할 수 있음

실습 2. 등록되어 있는 job 삭제

SCOTT> exec dbms_job.remove(25) ; <-- 숫자부분에는 job 번호를 기입

SCOTT> select what, job, next_date, next_sec, failures, broken

2 from user_jobs

3 where job=25 ;

-> 사라져서 안나옴

실습 3. 등록되어 있는 job 수정

테스트를 위해 다시 job 등록

SCOTT> variable jno number ;

SCOTT> @job1.sql

SCOTT> print jno ;

JNO

-----------

26 <-- job no 가 26으로 변경되있음

SCOTT> commit ;

SCOTT> select what, job, next_date, next_sec, interval

2 from user_jobs

3 where job=26 ;

WHAT JOB NEXT_DATE NEXT_SEC INTERVAL

------------ ---- ------------- ---------- ------------------

scott.insert_job 4 15-OCT-14 21:51:30 sysdate +1/24/60

수행시간을 1분마다 -> 5분마다로 변경

SCOTT> exec dbms_job.change(:jno, 'scott.insert_job_test01;', sysdate, 'sysdate +5/24/60') ;

SCOTT> select what, job, next_date, next_sec, interval

2 from user_jobs

3 where job=26 ;

WHAT JOB NEXT_DATE NEXT_SEC INTERVAL

------------ ---- ------------- ---------- ------------------

scott.insert_job 4 15-OCT-14 21:51:30 sysdate +5/24/60

2. DBMS_SCHEDULER ( 10g이상에서만 사용가능 )

dbms_job 패키지보다 확장된 기능으로 dbms_job 에서 안되는 외부 스크립트를 실행 할 수 있음

(1) 주요특징

- 오라클에서 생성된 프로시저나 함수 외에 os에서 생성된 각종 유틸이나 프로그램까지도 실행가능

- 설정할 때 일반적으로 사용하는 자연어를 사용하여 편하게 설정 가능

- db 내부이벤트까지 추적가능하기 때문에 os나 dbms_job 보다 훨씬 다양하게 작업을 체크하고 수행가능

(2) 구성

- program : 이 부분에 dbms_scheduler가 수행할 프로그램이나 프로시저, 스크립트 등에 대한 정보를 저장해서 별도로 생성할 수 있음

- schedule : 이 부분에 dbms_scheduler 가 수행할 job 이 실제 수행할 시간이나 실행 주기등을 별도로 생성 할 수 있음

- job : 새로 생성할 job 부분을 정의

(3) dbms_scheduler 사용하기

실습 1. 신규 job 생성하기

job_tetst1 이라는 테이블에 3초에 1회씩 데이터를 insert 하는 작업을 설정

이 작업을 하기 위해선 해당 계정이 create any job 권한을 가지고 있어야 함

scott 계정에 권한 부여

SYS> grant create any job to scott ;

SYS> conn scott/tiger ;

SCOTT> create table job_test1

2 ( no number ,

3 name varchar2(5) ,

4 rdate date default sysdate ) ;

SCOTT> create sequence seq_job_test1_no ;

SCOTT> create or replace procedure insert_job_test

2 is

3 begin

4 insert into scott.job_test1 (no , name)

5 values(seq_job_test1_no.nextval, dbms_random.string('a',2)) ;

6 commit ;

7 end;

8 /

SCOTT> begin

2 dbms_scheduler.create_job( <-- 신규 JOB을 생성

3 job_name => 'insert_job_test1' , <-- dbms_scheduler 내에서 사용될 job 이름지정

4 job_type => 'plsql_block' , <-- 5번줄에 적은 프로그램의 타입을 적음

5 job_action => 'begin insert_job_test; end;' , <-- 실제 실행될 프로그램을 적는 부분

6 start_date => systimestamp , <-- 해당 job 이 처음 시작될 시간을 지정

7 repeat_interval => 'freq=secondly; interval=3' ); <-- 반복할 주기를 지정

8 end;

9 /

※ 5번줄에는 1) 실제 PL/SQL 블록

2) 프로시저 이름

3) OS에 있는 실행파일

4) program_name 으로 미리생성해둔 프로그램 이름

5) chained 값으로 생성된 체인

위 5가지가 들어갈 수 있음

※ 4번줄은 위에 pl/sql 형태이므로 PLSQL_BLOCK 로 기입

5번줄이 프로시저 이름을 적었다면 4번줄에는 STORED_PROCEDURE로,

실행프로그램을 적었다면 EXECUTABLE 로 적어야 함

※ 7줄의 주기 설정

repeat_interval => 'freq=hourly ; interval=1' <- 1시간 간격으로 수행하도록 설정

repeat_interval => 'freq=minutely ; interval=30' <- 30분 간격으로 수행하도록 설정

repeat_interval => 'freq=secondly ; interval=5' <- 5초 간격으로 수행하도록 설정

repeat_interval => 'freq=weekly ; interval=2' <- 2주 간격으로 수행

repeat_interval => 'freq=hourly ; interval=1' <- 매달 수행하도록 설정

repeat_interval => 'freq=hourly ; interval=1' <- 매년 수행하도록 설정

SCOTT> exec dbms_scheduler.enable('insert_job_test1') ;

SCOTT> exec dbms_scheduler.run_job('insert_job_test1') ;

SCOTT> alter session set nls_date_format='YYYY-MM-DD:HH24:MI:SS' ;

SCOTT> select * from job_test1 order by 3 ;

-> 확인해보면 3초마다 insert 되는것을 볼 수 있음

실습 2. 현재 작동중인 job 확인

SYS> select p.job_name, p.job_type, o.object_id, p.enabled, o.last_ddl_time, o.created,

2 cast(p.next_run_date as date) next_run_date, p.state, p.job_class, o.owner,

3 schedule_type

4 from dba_objects o , dba_scheduler_jobs p

5 where o.owner=p.owner

6 and o.object_name=p.job_name

7 and o.object_type='JOB'

8 and o.owner='SCOTT'

실습 3. 특정 job을 일시중지 또는 재시작 하기

SCOTT> exec dbms_scheduler.disable ('insert_job_test1') ; <-- 일시중지

SCOTT> exec dbms_scheduler.enable ('insert_job_test1') ; <-- 다시시작

실습 4. 특정 job 을 삭제

SCOTT> begin

2 dbms_scheduler.drrop_job('insert_job_test1') ;

3 end ;

4 /

실습 5. 외부 스크립트를 생성해서 실행

외부에서 생성된 스크립트를 오라클에서 실행하기 위해서는 해당 스크립트를 실행할 권한과 그룹이 지정되어 있는 externaljob.ora 라는 파일을 수정해야 함

step1. externaljob.ora 파일의 내용을 수정

SYS> !vi /app/oracle/product/11g/rdbms/admin/externaljob.ora

마지막줄의 내용을 아래와 같이 변경

#run_user = nobody -- 기존내용 주석처리

#run_group = nobody -- 기존 내용 주석처리

run_user = oracle

run_group = oinstall

oracle 계정의 소속 그룹을 모를 경우

->$ id oracle 조회

step2. 수정 완료 후 위 파일의 권한을 640 상태로 설정 ( 이 값이 기본적으로 설정되어 있음 )

[oracle@localhost ~] $ls- l $ORACLE_HOME /rdbms/admin/externaljob.ora

step3. 외부 스크립트를 실제로 수행하는 명령인 extjob 파일의 소유자를 변경

extjob 파일의 소유자 root -> oracle 로 변경

[oracle$localhost ~]$ ls -l $ORACLE_HOME/bin/extjob

[root$localhost ~]# chown oracle extjob

[root$localhost ~]# ls -l extjob

이 작업을 하지않을 경우 permission denied 라는 에러가 발생

저작자표시 변경금지

'오라클 > 관리 실무' 카테고리의 다른 글

사용자 관리 (0)	2014.10.27
Oracle 저장구조 (0)	2014.10.23
Tablespace 와 Data File 관리하기 (2)	2014.10.21
Redo Log 관리하기 (0)	2014.10.20
Control File 관리하기 (0)	2014.10.20
ORACLE 시작하기 & 종료하기 (0)	2014.10.20
Oracle Background Process (0)	2014.10.17

사용자 관리

김포춘 2014. 10. 27. 13:48

2014. 10. 27. 13:48

사용자 - 관리자 : sys , system DB처리, 장애처리 용도로 사용

- 사용자 : scott 등.. 작업을 수행하는 용도로 사용

새로운 사용자를 생성하는 순서

1) 생성할 사용자의 default tablespace 를 결정하고 해당 tablespace 를 생성

2) 생성할 사용자의 temporary tablespace 를 결정하고 해당 tablespace 를 생성

3) 사용자를 생성

4) 적절한 프로파일과 권한 , 롤 등을 생성한 후 할당

실습 1. 사용자 생성하기

웹하드 서비스를 위한 계정 생성

default tablespace 로 ts_webhard 라는 tablespace를 생성 ( 웹하드 관련 테이블은 모두 이곳에 저장 )

ts_web_idx tablespace 에 웹하드 관련 인덱스 저장

계정 명을 webuser , 암호는 webpwd

권한은 일반적인 사용자 권한 전부 부여

정렬 작업등을 위한 temporary tablespace 는 temp_web 으로 결정

실습시작

(1) webuser 의 default tablespace 생성

sqlplus / as sysdba

SYS> create tablespace ts_webhard

2 datafile '/app/oracle/oradata/testdb/ts_webhard01.dbf' size 100M ;

SYS> create tablespace ts_web_idx

2 datafile '/app/oracle/oradata/testdb/ts_web_idx' size 10M ;

SYS> select tablespace_name , bytes /1024/1024 MB , file_name

2 from dab_data_files ; <--생성 확인

(2) temporary tablespace 생성

SYS> create temporary tablespace temp_web

2 tempfile '/app/oracle/oradata/testdb/temp_web01.dbf' size 10M ;

(3) 사용자 생성

SYS> create user webuser

2 identified by webpwd <-- 암호 지정부분

3 default tablespace ts_webhard <-- default tablespace 를 지정하는 부분

4 temporary tablespace temp_web <-- temporary tablespace 를 지정하는 부분

5 quota unlimited on ts_webhard <-- 할당량을 지정하는데 무제한으로 설정

6 quota 0m on system ; <-- system tablespace 를 사용못하게 설정

(4) 권한 설정

SYS> grant resource , connect to webuser ;

SYS> conn webuser/webpwd <-- 접속 되는지 확인

사용자 정보 확인하기

SYS> select username , default_tablespace "Default TS" , temporary_tablespace "Temp TS"

2 from dba_users

3 where username='WEBUSER" ;

Profile 관리하기

Failed_login_attempts : 설정된 횟수만큼 login 을 시도한 후 실패 시 계정 잠금
Password_lock_time : 위 1번에서 계정이 잠기면 며칠 동안 잠글 것인지 기간을 정하는 파라미터
Password_life_time : 동일한 암호를 며칠간 사용할 것인지 설정하는 파라미터
Password_grace_time : 위 3번항목에서 만료되어도 이 파라미터에서 지정된 값 만큼 암호를 변경할 기간을 허용
Password_reuse_time : 동일한 암호를 다시 사용할 수 없도록 설정하는 기간, 같은 암호 사용 못하게 할때 쓰는 파라미터
Password_reuse_max : 동일한 암호를 위 5번 설정을 피해 재사용할 경우 최대 사용 가능한 횟수를 지정하는 파라미터
Password_verify_function : 암호를 복잡하게 만들기 위해 특정함수를 적용시켜 사용자가 입력한 암호가 암호로 적합한지 점검

- verify_function 함수 사용시 주의사항

암호는 최소 4글자 이상
암호는 사용자 계정과 달라야함
암호는 하나의 특수문자나 , 알파벳, 숫자가 포함되어야 함
암호는 이전 암호와 3글자 이상 달라야 함

실습 2. Password 관련 profile 생성

- 조건 1 : 로그인 시도 5회 실패시 계정을 5일동안 사용 못하게

- 조건 2 : 계정의 암호는 10일에 한 번씩 변경하게 할 것

- 조건 3 : 동일한 암호는 10일 동안 사용 못하게 할 것

SYS> create profile sample_prof limit

2 failed_login_attempts 5 <-- 로그인 실패 제한 5회

3 password_lock_time 5 <-- 로그인 실패시 5일동안 사용 제한

4 password_life_time 10 <-- 계정 암호 10일에 한번씩 변경

5 password_reuse_time 10 ; <-- 동일 암호 10일동안 사용 못함

Resource profile 관련 파라미터

CPU_PER_SESSION : SESSION 이란 1명의 접속을 의미, 하나의 SESSION 이 CPU를 연속적으로 사용할 수 있는 최대시간을 설정
SESSIONS_PER_USER : 하나의 사용자 계정으로 몇 명의 사용자가 동시에 접속할 수 있는지 설정하는 파라미터
CONNECT_TIME : 하루동안 DB Server 에 접속할 수 있는 총 시간
IDLE_TIME : 연속적으로 휴면시간이 이 값을 넘으면 접속을 해제, 접속 해제시 commit 안된 작업은 전부 rollback 됨
LOGICAL_READS_PER_SESSION : 한 session 에서 사용 가능한 최대 block수 지정
PRIVATE_SGA : MTS / shared server 일 경우 해당 session 의 SGA 사용량을 bytes 단위로 설정
CPU_PER_CALL : 하나의 call 당 cpu 를 점유할 수 있는 시간 1/100초 단위
LOGICAL_READS_PER_CALL : 하나의 call당 읽을 수 있는 block 의 개수 지정

실습 3. Resource 관련 profile 만들기

- 조건 1 : 1명당 연속적으로 cpu를 사용할 수 있는 시간 10초 제한

- 조건 2 : 하루 중 8시간만 db에 접속 가능하게 할 것

- 조건 3 : 10분 동안 사용하지 않으면 강제로 접속 끊음

SYS> create profile re_sample_prof limit

2 cpu_per_session 1000 <-- 1명당 사용할수 있는시간 10초 제한 1/100초 단위임

3 connect_time 480 <-- 60*8 8시간만 db에 접속 가능하게함

4 idle_time 10 ; <-- 10분 사용제한

사용자에게 profile 할당하기

1. 현재 사용자가 적용받고 있는 profile 확인하기

SYS> select username, profile

2 from dba_users

3 where username= "WEBUSER" ;

USERNAME PROFILE

---------- ---------

WERBUSER DEFAULT <--현재 아무profile 도 없음

2. 해당 profile 에 어떤 내용이 있는지 확인

- 위에서 만든 sample_prof 내용 조회

SYS> select * from dba_profiles

2 where profile="SAMPLE_PROF" ;

여러개 쭉 나오고 아래와 같은 옵션을 설정한 것도 나타남

failed_login_attempts 5

password_lock_time 5

password_life_time 10

password_reuse_time 10

3. 사용자에게 profile 적용시키고 확인하기

SYS> alter user webuser profile sample_prof ;

SYS> select username, profile

2 from dba_users

3 where username="WEBUSER" ;

USERNAME PROFILE

--------- --------------

WEBUSER SAMPLE_PROF

단 profile 을 적용시킬때 여러개의 profile 은 적용안되니 원하는 파라미터를 profile에 전부넣고 한번에 만들어야함

4. 사용안하는 profile 삭제하기

SYS> drop profile sample_prof ;

-> error 남 이유는 현재 사용자에게 할당된 profile 은 삭제가 안됨, 그러나 cascade 옵션으로 삭제하면 삭제가능

SYS> drop profile sample_prof cascade ;

-> 이렇게 삭제하고 나면 사용자는 default profile 을 사용하게 됨

privilege ( 권한 ) 관리하기

profile 은 사용자가 어떤 것들을 하지 못하게 제한하는 것이 목적

privilege 는 사용자에게 어떤 것들을 할 수 있게 허락해주는 것이 목적

- system 관련 privilege : system 전체에 영향을 주는 권한들을 모아놓은 것

- object 관련 privilege : 특정 object 에 관련된 privilege

SYSTEM 관련 주요 privilege
대분류	privilege	설명
Index	Create any index	소유자에 상관없이 모든 테이블에 인덱스를 생성할 수 있는 권한
	Drop any index	소유자에 상관없이 모든 인덱스를 삭제할 수 있는 권한
	Alter any index	소유자에 상관없이 모든 인덱스를 수정할 수 있는 권한
table	create table	자신의 테이블을 생성할 수 있는 권한
	create any table	소유자에 상관없이 다른 user 이름으로 테이블을 생성할 수 있는 권한
	alter any table	소유자에 상관없이 모든 테이블의 구조를 수정할 수 있는 권한
	drop any table	소유자에 상관없이 모든 사용자의 테이블을 삭제할 수 있는 권한
	update any table	소유자에 상관없이 모든 사용자의 테이블을 업데이트할 수 있는 권한
	delete any table	소유자에 상관없이 모든 사용자의 테이블의 데이터를 삭제 할 수 있는 권한
	insert any table	소유자에 상관없이 모든 사용자의 테이블의 데이터를 삽입 할 수 있는 권한
session	create session	서버에 접속할 수 있는 권한
	alter session	접속 상태에서 환경값을 변경할 수 있는 권한
	restricted session	restricted 모드로 open 된 db에 접속할 수 있는 권한
tablespace	create tablespace	tablespace를 만들 수 있는 권한
	alter tablespace	tablespace를 수정할 수 있는 권한
	drop tablespace	tablespace를 삭제할 수 있는 권한
	unlimited tablespace	tablespace 사용 용량을 무제한으로 허용하는 권한, 즉 quota 옵션적용을 받지않음

SYSOPER /SYSDBA privilege
privilege	할 수 있는 일
SYSOPER	startup / shutdown
	alter database mount / open
	alter database backup control file to ....
	recover database
	alter database archivelog
	restricted session
SYSDBA	SYSOPER privilege with admin option
	create database
	alter tablespace ... begin backup / end backup
	recover database until

SYSTEM 관련 권한 할당하기 / 해제하기

- scott 사용자에게 create table , create session 권한을 할당

SYS> grant create table, create session to scott ;

- scott 사용자에게 create table 권한을 해제

SYS> revoke create table from scott ;

사용자가 가지고 있는 권한 조회

- scott 사용자가 가지고 있는 모든 권한 조회

SYS> select * from dba_sys_privs

2 where grantee = "SCOTT" ;

Object 관련 Privilege

주로 DML 과 연관이 많음. 즉 object 를 select , insert , update , delete 등을 할 수 있는 권한

object 권한 할당하기 / 해제하기 ( 실습 전에 webuser 계정으로 webtest 테이블을 생성하고 해야함 )

- scott 사용자에게 webuser 가 만든 webtest 테이블을 select 할 수 있도록 허용

SYS> grant select on webuser.webtest to scott ;

- scott 사용자에게 webuser 가 만든 webtest 테이블을 update 할 수 있도록 허용, 그리고 scott 사용자가 이 권한을 다른 사람에게 줄 수 있는 권한도 부여

SYS> grant update on webuser.webtest to scott with grant option ;

- scott 사용자가 가진 webuser의 webtest 테이블을 select 하는 권한을 해제

SYS> revoke select on webuser.webtest from scott ;

Role 관리하기

role 속에 여러가지 권한을 넣어두고 사용자에게 role 을 부여하면 여러가지 권한을 갖게됨

(1) role 생성하기

SYS> create role trole ;

(2) role 에 create session , create table 권한 할당

SYS> grant create session , create table to role ;

(3) 사용자에게 trole 할당

SYS> grant trole to scott ;

(4) 어떤 사용자가 어떤 role 을 사용하는지 확인

SYS> select * from dba_role_privs where grantee='SCOTT' ; <-- SCOTT 계정에 어떤 role 이 있는지 확인

(5) 어떤 role 에 어떤 권한이 있는지 확인

SYS> select * from dba_sys_privs where grantee = 'CONNECT' ; <--CONNECT 에 어떤 권한이 있는지 확인

저작자표시 변경금지

'오라클 > 관리 실무' 카테고리의 다른 글

DBMS_JOB & DBMS_SCHEDULER (0)	2014.10.27
Oracle 저장구조 (0)	2014.10.23
Tablespace 와 Data File 관리하기 (2)	2014.10.21
Redo Log 관리하기 (0)	2014.10.20
Control File 관리하기 (0)	2014.10.20
ORACLE 시작하기 & 종료하기 (0)	2014.10.20
Oracle Background Process (0)	2014.10.17

PREV 이전 1 2 3 4 5 6 ···12 NEXT 다음

오라클 스터디

전체 글

Redo log flle 장애 처리방법

'오라클 > 백업/복구' 카테고리의 다른 글

Log Miner 활용하기

'오라클 > 백업/복구' 카테고리의 다른 글

control file, data file 연습문제

'오라클 > 백업/복구' 카테고리의 다른 글

Control file 복구

'오라클 > 백업/복구' 카테고리의 다른 글

Data file Recovery (불완전 복구)

'오라클 > 백업/복구' 카테고리의 다른 글

Data File Recovery

'오라클 > 백업/복구' 카테고리의 다른 글

Backup (cold, hot)

'오라클 > 백업/복구' 카테고리의 다른 글

사용자 암호설정/ Archive log mode, No Archive log mode

'오라클 > 백업/복구' 카테고리의 다른 글

DBMS_JOB & DBMS_SCHEDULER

'오라클 > 관리 실무' 카테고리의 다른 글

사용자 관리

'오라클 > 관리 실무' 카테고리의 다른 글

+ Recent posts

티스토리툴바