글
신입사원 직무역량 강화 08년11월7일 - Oracle 장애복구
서비스 운영
2008/11/07 16:01
더보기
08년11월7일-Oracle장애복구
목차
--------------------------------------------------------------------------------
사용자 백업 및 복구
Archivelog 모드로 운영시
완전복구
불완전 복구
Logical 백업 복구
export/import 개요
export 유틸리티
import 유틸리티
실습
RMAN을 이용한 백업 복구
RMAN 개요
RMAN 명령어
실습) 간단한 백업
RMAN을 이용한 백업
실습) RMAN을 이용한 장애 복구
Oracle Network 관리
OracleNetwork#
서버 설정
클라이언트 설정
공유 서버 설정
사용자 백업 및 복구
Archivelog 모드로 운영시
완전복구
장애난 파일을 컨트롤 파일의 기준시점과 일치 가능.
동작
a) 장애난 파일만 restore
b) recover database; 데이터베이스 전체를 복구하기를 원하는 경우
recover datafile 'filename';
recover tablespace 'tablespacename' :
불완전 복구
장애난 파일을 ctl과 동일한 시점으로 일치 불가능.
※ 모든 datafile 의 시점은 일치해야한다.
동작
컨트롤 파일 백업 미 이용시
a) 모든 datafile restore
b) recover database until time 'timestamp', cancel, change SCN#; 3가지의 옵션이 존재, MOUNT단계에서 가능
c) alter database open resetlogs; 강제로 시점 정보를 초기화함
d) 데이터 베이스 전체 복원 실시
컨트롤 파일 백업 이용시
a) 모든 datafile restore, *.ctl restore
b) recover database using backup controlfile until ...; *.rdo 장애시에는 반드시 until cancel 로 복구해야함
c) alter database open resetlogs;
d) 데이터 베이스 전체 복원 실시
Logical 백업 복구
[데이터베이스] --(export)--> [OS filetype] --(import)--> [데이터베이스]
이 기종 플랫폼 간에도 데이터의 변환이 가능하다.
테이블등의 객체가 함께 추출됨.
export/import 개요
시점정보를 제외한 논리적 백업을 제공
리두 로그 기록을 적용할 수 없기 때문에 데이터 손실이 발생 가능
명령 행 입력을 이용한 실행 (많이 수행하는 방식)
대화식 인터페이스를 지원
매개 변수 파일을 이용한 백업, 복구를 제공
Oracle Enterprise Manager를 이용해서 사용가능
export 유틸리티
특성
서버간의 데이터 migration 용도로 주로 사용함
테이블 재구성 및 블록의 손상 확인 가능
사용자 에러에 대한 복호 기능
수행모드
테이블, 사용자, 테이블스페이스, 전체 백업
파라미터
userid/pasasword
file : target filename
rows : 테이블 데이터를 백업 여부를 결정. rows=0 을 이용하면 DDL 만 생성된다.
full
owner
tables
tablespaces
consistent : 백업 진행중의 테이블 데이터가 변경중이라면 변경 이전의 데이터를 백업.
데이터의 백업이 상당히 오랜 시간이 걸리는 작업이기 때문에 백업중에 발생하는 데이터의 변경으로 인한 데이터 일치가 문제시 된다.
백업 작업의 수행 시간과는 무관하게 명령어가 실행되는 시점을 기준으로 데이터를 백업하는 방식이다.
(consistent=Y라는 옵션을 사용하면 db가 언두로그를 이용해서 백업을 하기 때문에 db에 부하를 많이 주기 때문에 운영중에는 잘 사용하지 않는다)
실재로 이 옵션을 주면 export failure도 발생.
direct : 데이터 버퍼 캐시 영역을 사용하지 않고 백업하기 때문에 빠른 백업 가능
db 버퍼 캐시를 통하지 않고 서버 프로세스 메모리 영역에서 작업하는 방식 (TTCTwoTaskCommon)
feedback : 백업하는 도중에 행의 수마다 점(.)을 찍는다.
dba25@sun02-zone:/oracle/dba/dba25] exp scott/tiger file=a1.dmp tables=emp, dept;
Export: Release 9.2.0.1.0 - Production on Fri Nov 7 08:54:07 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table EMP 14 rows exported
. . exporting table DEPT 64 rows exported
Export terminated successfully without warnings.
dba25@sun02-zone:/oracle/dba/dba25] strings a1.dmp | more
...
CREATE TABLE "EMP" ("EMPNO" NUMBER(4, 0) NOT NULL ENABLE, "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4, 0), "HI
REDATE" DATE, "SAL" NUMBER(7, 2), "COMM" NUMBER(7, 2), "DEPTNO" NUMBER(2, 0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 2
55 STORAGE(INITIAL 1048576 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "SAMPLE" LOGGING NOCOMPRESS
...
※ export 라는 툴은 시점에 대한 정보가 들어가는 것이 아니라 데이터에 대한 내용을 단순히 SQL 형태로 만들어서 차후 데이터를 넣을 수 있도록 만드는 목적을 가진 프로그램이다.
import 유틸리티
파라미터
userid
rows
ignore : 이미 존재하는 테이블에 대한 복구 작업을 실행하면 object already exists라는 에러를 발생하는데 이 에러를 무시하고 계속 복구 작업 수행
commit : Y를 설정하면 각 배열을 입력한 후 commit
커밋의 단위는 buffer 에 지정된 만큼씩이다.
수행모드
table, user, tablespace, full database
순서
테이블 생성
데이터 import
인덱스 생성
트리거 import
무결성 제약 조건 활성화
비트맵, 함수 기반 인덱스 생성
실습
dba25@sun02-zone:/oracle/dba/dba25] cd ~/DEMO
dba25@sun02-zone:/oracle/dba/dba25/DEMO] exp scott/tiger file=a1.dmp owner=scott;
Export: Release 9.2.0.1.0 - Production on Fri Nov 7 09:21:53 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table BONUS 0 rows exported
. . exporting table DEPT 64 rows exported
. . exporting table DUMMY 1 rows exported
. . exporting table EMP 14 rows exported
. . exporting table SALGRADE 5 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
dba25@sun02-zone:/oracle/dba/dba25/DEMO] sqlplus scott/tiger
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Nov 7 09:23:22 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> select count(*) from dept;
COUNT(*)
----------
64
SQL> truncate table dept;
Table truncated.
SQL> select * from dept;
no rows selected
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
dba25@sun02-zone:/oracle/dba/dba25/DEMO] imp scott/tiger file=a1.dmp tables=dept;
Import: Release 9.2.0.1.0 - Production on Fri Nov 7 09:24:25 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
IMP-00015: following statement failed because the object already exists:
"CREATE TABLE "DEPT" ("DEPTNO" NUMBER(2, 0), "DNAME" VARCHAR2(14), "LOC" VAR"
"CHAR2(13)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 1"
"048576 FREELISTS 1 FREELIST GROUPS 1) LOGGING NOCOMPRES"
"S"
Import terminated successfully with warnings.
dba25@sun02-zone:/oracle/dba/dba25/DEMO] imp scott/tiger file=a1.dmp tables=dept ignore=y;
Import: Release 9.2.0.1.0 - Production on Fri Nov 7 09:25:02 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. . importing table "DEPT" 64 rows imported
Import terminated successfully without warnings.
dba25@sun02-zone:/oracle/dba/dba25/DEMO] sqlplus scott/tiger
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Nov 7 09:25:33 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> select count(*) from dept;
COUNT(*)
----------
64
참고) 10g의 경우
drop table 명령 수행시 해당 테이블을 recyclebin 에서 확인 가능
> flashback table dept before drop;
expdp, impdp 데이터 펑프 기능 제공
> exp, imp 와 동일한 작업을 하고 성능이 올라간 녀석 (+20%)
RMAN을 이용한 백업 복구
RMAN 개요
Recovery Manager
DB, 테이블스페이스, 데이터 파일, 컨트롤 파일 및 아카이브 파일 로그의 백업
자주 수행되는 백업 및 복구 작업 저장
증분 블록 레벨 백업 수행(수정된 블록 백업) : RMAN의 가장 큰 특징. 블록단위로 작업 수행.
사용되지 않는 블록 건너뛰기 : 증분 백업을 통해서 백업 본의 파일이 작다.
백업 파일 사이즈 지정
백업 중 훼손 된 블록 감지
자동 병력화, 리두 생성 감소로 성능 향상
※ 온라인 백업을 실시중에도 백업을 위한 라킹 작업을 하지 않아도 된다는 장점
※ RMAN 실행의 대상이 되는 DB를 target DB라고 부른다.
[RMAN] --(channel : 작업용 프로세스)--> [Target DB]
a) backup database format '/DISK1/full.bak';
b) channel 기동
c) full.bak 통합 파일(aka backup set, backup piece)로 저장
d) controlfile에 기록(aka metadata)
※ 메타데이터의 기록 유지 기간은 파라메터 변수로 저장된다. 문제는 이 내용이 실재로는 계속 유지되어야하기 때문에 별도의 백업용 데이터베이스에 해당 정보를 넣는 것이 가능하다. (aka catalog database)
RMAN 저장소 : 대상 데이터베이스와 백업 및 복구 작업에 대한 멘타 데이터. 대상 데이터 베이스의 제어파일에 저장
control_file_record_keep_time 레코드가 겹쳐 쓰이기 전까지 저장되는 최소일자 수를 지정
※ recovery catalog 를 사용할 경우의 장점
한 catalog에 여러 target 대이터 베이스의 메타데이터 저장가능
rman의 저장 스크립트를 사용할 수 있다.
블록 단위의 증분 백업이 가능하다.
컨트롤 파일 분실시 유연하게 대처 가능함.
RMAN 명령어
- 조회용
show
list
report
- 수정용
catalog
change
delete
crosscheck
RMAN에서 타겟 데이터베이스의 컨트롤 파일 내의 정보를 조회 수정하는데 사용한다.
조회용 명령어
list : 백업 셋과 복사본을 보여준다.
report : 등록된 정보를 이용해서 한번 처리한뒤 보여준다.
RMAN> report need backup days 1;
using target database controlfile instead of recovery catalog
Report of files whose recovery needs more than 1 days of archived logs
File Days Name
---- ----- -----------------------------------------------------
1 1895 /oracle/dba/dba25/dbs/system01.dbf
2 1895 /oracle/dba/dba25/dbs/undotbs.dbf
3 1895 /oracle/dba/dba25/dbs/users01.dbf
4 1895 /oracle/dba/dba25/dbs/indx01.dbf
5 1895 /oracle/dba/dba25/dbs/sample01.dbf
6 1895 /oracle/dba/dba25/dbs/querydata01.dbf
RMAN> list copy of database;
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
2 5 A 07-NOV-08 420655 07-NOV-08 /oracle/dba/dba25/DEMO/sam.cpy
RMAN> exit
2. RMAN을 이용하지 않는 백업 실시
Recovery Manager complete.
dba25@sun02-zone:/oracle/dba/dba25/DEMO] sqlplus '/as sysdba'
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Nov 7 11:35:14 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> alter tablespace users begin backup;
Tablespace altered.
SQL> !cp $HOME/dbs/users01.dbf $HOME/DEMO/u.dbf
SQL> alter tablespace users end backup;
Tablespace altered.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
3. RMAN에 백업 파일 내역 등록
dba25@sun02-zone:/oracle/dba/dba25/DEMO] rman target /
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: DBA25 (DBID=392127005)
RMAN> list copy of database;
using target database controlfile instead of recovery catalog
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
2 5 A 07-NOV-08 420655 07-NOV-08 /oracle/dba/dba25/DEMO/sam.cpyRMAN으로 작업한 내용만이 자동 등록된다는 사실을 알자
RMAN> catalog datafilecopy '$HOME/DEMO/u.dbf';
cataloged datafile copy
datafile copy filename=/oracle/dba/dba25/DEMO/u.dbf recid=12 stamp=670160278
RMAN> list copy of database;
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
12 3 A 07-NOV-08 441980 07-NOV-08 /oracle/dba/dba25/DEMO/u.dbf
2 5 A 07-NOV-08 420655 07-NOV-08 /oracle/dba/dba25/DEMO/sam.cpy차후 RMAN이 해당 파일을 복구에 사용가능하록 등록하는 과정임.
4. RMAN에 등록된 파일 정보 상태를 변경
RMAN> change datafilecopy '$HOME/DEMO/u.dbf' uncatalog;
uncataloged datafile copy
datafile copy filename=/oracle/dba/dba25/DEMO/u.dbf recid=12 stamp=670160278
Uncataloged 1 objects
RMAN> list copy of database;
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
2 5 A 07-NOV-08 420655 07-NOV-08 /oracle/dba/dba25/DEMO/sam.cpy
RMAN> exit;
Recovery Manager complete.
dba25@sun02-zone:/oracle/dba/dba25/DEMO] ls -al $HOME/DEMO/u.dbf
-rwxr-x--- 1 dba25 dba 5246976 Nov 7 11:35 /oracle/dba/dba25/DEMO/u.dbf
dba25@sun02-zone:/oracle/dba/dba25/DEMO] rman target /
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: DBA25 (DBID=392127005)
RMAN> catalog datafilecopy '$HOME/DEMO/u.dbf';
using target database controlfile instead of recovery catalog
cataloged datafile copy
datafile copy filename=/oracle/dba/dba25/DEMO/u.dbf recid=13 stamp=670160666
RMAN> list copy of database;
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
13 3 A 07-NOV-08 441980 07-NOV-08 /oracle/dba/dba25/DEMO/u.dbf
2 5 A 07-NOV-08 420655 07-NOV-08 /oracle/dba/dba25/DEMO/sam.cpy
RMAN> delete datafilecopy '$HOME/DEMO/u.dbf';
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=9 devtype=DISK
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
13 3 A 07-NOV-08 441980 07-NOV-08 /oracle/dba/dba25/DEMO/u.dbf
Do you really want to delete the above objects (enter YES or NO)? YES
deleted datafile copy
datafile copy filename=/oracle/dba/dba25/DEMO/u.dbf recid=13 stamp=670160666
Deleted 1 objects
RMAN> list copy of database;
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
2 5 A 07-NOV-08 420655 07-NOV-08 /oracle/dba/dba25/DEMO/sam.cpy
RMAN> exit
Recovery Manager complete.
dba25@sun02-zone:/oracle/dba/dba25/DEMO] ls -al $HOME/DEMO/u.dbf
/oracle/dba/dba25/DEMO/u.dbf: No such file or directory
실습) 간단한 백업
SQL> exit Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
dba25@sun02-zone:/oracle/dba/dba25/DEMO] rman target /
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: DBA25 (DBID=392127005)
RMAN> list backup;
using target database controlfile instead of recovery catalog
RMAN> report need backup days 1 ;
RMAN을 이용해서 백업을 해야할 데이터베이스 파일을 보여준다. 하루를 기준으로 하는 옵션
Report of files whose recovery needs more than 1 days of archived logs
File Days Name
---- ----- -----------------------------------------------------
1 1895 /oracle/dba/dba25/dbs/system01.dbf
2 1895 /oracle/dba/dba25/dbs/undotbs.dbf
3 1895 /oracle/dba/dba25/dbs/users01.dbf
4 1895 /oracle/dba/dba25/dbs/indx01.dbf
5 1895 /oracle/dba/dba25/dbs/sample01.dbf
6 1895 /oracle/dba/dba25/dbs/querydata01.dbf
RMAN> backup datafile '~/dbs/sample01.dbf' format '~/DEMO/sam.bak';
Starting backup at 07-NOV-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=13 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 11/07/2008 09:59:15
RMAN-20201: datafile not found in the recovery catalog
RMAN-06010: error while looking up datafile: /oracle/dba/dba25/dbs/~/dbs/sample01.dbf
RMAN> backup datafile '$HOME/dbs/sample01.dbf' format '$HOME/DEMO/sam.bak';변경 분에 대한 백업만을 실시한다. (다:1 백업 가능)
Starting backup at 07-NOV-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00005 name=/oracle/dba/dba25/dbs/sample01.dbf
channel ORA_DISK_1: starting piece 1 at 07-NOV-08
channel ORA_DISK_1: finished piece 1 at 07-NOV-08
piece handle=/oracle/dba/dba25/DEMO/sam.bak comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-NOV-08
RMAN> copy datafile '$HOME/dbs/sample01.dbf' to '$HOME/DEMO/sam.cpy';유닉스 커맨드 상태의 cp와 유사하다. (1:1 백업 가능)
Starting copy at 07-NOV-08
using channel ORA_DISK_1
channel ORA_DISK_1: copied datafile 5
output filename=/oracle/dba/dba25/DEMO/sam.cpy recid=2 stamp=670154452
Finished copy at 07-NOV-08
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 100K DISK 00:00:01 07-NOV-08
BP Key: 1 Status: AVAILABLE Tag: TAG20081107T095958
Piece Name: /oracle/dba/dba25/DEMO/sam.bak
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
5 Full 420637 07-NOV-08 /oracle/dba/dba25/dbs/sample01.dbf
RMAN> report need backup days 1;
Report of files whose recovery needs more than 1 days of archived logs
File Days Name
---- ----- -----------------------------------------------------
1 1895 /oracle/dba/dba25/dbs/system01.dbf
2 1895 /oracle/dba/dba25/dbs/undotbs.dbf
3 1895 /oracle/dba/dba25/dbs/users01.dbf
4 1895 /oracle/dba/dba25/dbs/indx01.dbf
6 1895 /oracle/dba/dba25/dbs/querydata01.dbf
RMAN> exit;
Recovery Manager complete.
dba25@sun02-zone:/oracle/dba/dba25/DEMO] cd $HOME/DEMO
dba25@sun02-zone:/oracle/dba/dba25/DEMO] ls -al sam.*-rw-r----- 1 dba25 dba 118784 Nov 7 09:59 sam.bak
-rw-r----- 1 dba25 dba 10489856 Nov 7 10:00 sam.cpy
dba25@sun02-zone:/oracle/dba/dba25/DEMO] ls -al ~/dbs/sample01.dbf
-rwxr-x--- 1 dba25 dba 10489856 Nov 7 10:00 /oracle/dba/dba25/dbs/sample01.dbf
RMAN을 이용한 백업
1. 이미지 복사본
COPY명령어를 이용해서 백업한 것으로 1:1로 백업된다.
디스크에만 저장. restore 작업이 불필요. 모든 블록을 포함한다는 점에서 운영 체제 백업과 가장 유사하다.
2. 백업 셋
BACKUP 명령어를 이용해서 백업한 것으로 多:1로 백업
백업 셋은 대개 둘이상의 파일을 포함
디스크 또는 테이프에 백업 셋을 기록 가능
restore 작업이 필요
백업셋은 증분, 전체 백업이 될수 있다.
백업작업 수행시 조건
데이터 베이스는 마운트 되거나 열려 있어야 한다
온라인 리두 로그 백업은 지원하지 않는다
noarchivelog 모드에서는 offline 백업만 사용
archivelog 모드에서 백업 모드로 변경하지 않고 백업 작업 수행
※ 백업본을 가지고 원래 사이즈의 원본 파일로 복원하는 것을 restore라고 부른다.
실습) RMAN을 이용한 장애 복구
1.RMAN을 이용한 복구 시나리오 사전 작업
dba25@sun02-zone:/oracle/dba/dba25/DEMO] rman target /
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: DBA25 (DBID=392127005)
RMAN> backup database format '$HOME/DEMO/f4.bak';
Starting backup at 07-NOV-08
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=9 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current controlfile in backupset
input datafile fno=00001 name=/oracle/dba/dba25/dbs/system01.dbf
input datafile fno=00002 name=/oracle/dba/dba25/dbs/undotbs.dbf
input datafile fno=00005 name=/oracle/dba/dba25/dbs/sample01.dbf
input datafile fno=00003 name=/oracle/dba/dba25/dbs/users01.dbf
input datafile fno=00004 name=/oracle/dba/dba25/dbs/indx01.dbf
input datafile fno=00007 name=/oracle/dba/dba25/dbs/d.dbf
input datafile fno=00006 name=/oracle/dba/dba25/dbs/querydata01.dbf
channel ORA_DISK_1: starting piece 1 at 07-NOV-08
channel ORA_DISK_1: finished piece 1 at 07-NOV-08
piece handle=/oracle/dba/dba25/DEMO/f4.bak comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:48
Finished backup at 07-NOV-08
RMAN> report need backup days 1;
Report of files whose recovery needs more than 1 days of archived logs
File Days Name
---- ----- -----------------------------------------------------
RMAN> exit
Recovery Manager complete.
dba25@sun02-zone:/oracle/dba/dba25/DEMO] sqlplus '/as sysdba'
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Nov 7 10:37:02 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> alter system switch logfile;
System altered.
SQL> insert into scott.dept select * from scott.dept;
64 rows created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> select count(*) from scott.dept;
COUNT(*)
----------
128
SQL> alter system switch logfile;
System altered.
SQL> !rm $HOME/dbs/sample01.dbf
SQL> shutdown abort;
ORACLE instance shut down.
2. RMAN을 이용한 데이터베이스 복구 작업
SQL> startup
ORACLE instance started.
Total System Global Area 60379276 bytes
Fixed Size 454796 bytes
Variable Size 54525952 bytes
Database Buffers 4194304 bytes
Redo Buffers 1204224 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/oracle/dba/dba25/dbs/sample01.dbf'
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
dba25@sun02-zone:/oracle/dba/dba25/DEMO] rman target /
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: DBA25 (DBID=392127005)
RMAN> restore datafile '/oracle/dba/dba25/dbs/sample01.dbf';
Starting restore at 07-NOV-08
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=11 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to /oracle/dba/dba25/dbs/sample01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle/dba/dba25/DEMO/f4.bak tag=TAG20081107T103333 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 07-NOV-08
RMAN> run { sql 'alter database open'; }
sql statement: alter database open
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 11/07/2008 10:41:28
RMAN-11003: failure during parse/execution of SQL statement: alter database open
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/oracle/dba/dba25/dbs/sample01.dbf'데이터 베이스의 시점이 맞지 않기 때문에 DB가 열리지 않는다.
RMAN> recover datafile '/oracle/dba/dba25/dbs/sample01.dbf';
Starting recover at 07-NOV-08
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 2 is already on disk as file /oracle/dba/dba25/ORADATA/ARCHIVE1/arch_2.arc
archive log thread 1 sequence 3 is already on disk as file /oracle/dba/dba25/ORADATA/ARCHIVE1/arch_3.arc
archive log thread 1 sequence 4 is already on disk as file /oracle/dba/dba25/ORADATA/ARCHIVE1/arch_4.arc
archive log filename=/oracle/dba/dba25/ORADATA/ARCHIVE1/arch_2.arc thread=1 sequence=2
media recovery complete
Finished recover at 07-NOV-08
RMAN> run { sql 'alter database open' ;}
sql statement: alter database openRMAN> exit
Recovery Manager complete.
dba25@sun02-zone:/oracle/dba/dba25/DEMO] sqlplus '/as sysdba'
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Nov 7 10:44:47 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> select * from v$recover_file;
no rows selected
SQL> select count(*) from scott.dept;
COUNT(*)
----------
128
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 5 5242880 2 YES INACTIVE
421391 07-NOV-08
2 1 6 5242880 2 YES ACTIVE
441393 07-NOV-08
3 1 7 256000 2 NO CURRENT
441550 07-NOV-08현재 로그 시퀀스가 몇번인지 확인한다.
3. 모든 리두 로그 파일 삭재 오류 발생
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/oracle/dba/dba25/dbs/log01a.rdo
/oracle/dba/dba25/dbs/log02a.rdo
/oracle/dba/dba25/dbs/log1b.rdo
/oracle/dba/dba25/dbs/log2b.rdo
/oracle/dba/dba25/dbs/log3a.rdo
/oracle/dba/dba25/dbs/log3b.rdo
6 rows selected.
SQL> !rm ~/dbs/*.rdo
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 60379276 bytes
Fixed Size 454796 bytes
Variable Size 54525952 bytes
Database Buffers 4194304 bytes
Redo Buffers 1204224 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/oracle/dba/dba25/dbs/log2b.rdo'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 2 thread 1: '/oracle/dba/dba25/dbs/log02a.rdo'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
SQL> !rm $HOME/dbs/sample01.dbf
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 60379276 bytes
Fixed Size 454796 bytes
Variable Size 54525952 bytes
Database Buffers 4194304 bytes
Redo Buffers 1204224 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/oracle/dba/dba25/dbs/sample01.dbf'
4. 완전 복구 시나리오 시작 (실패)
SQL> exitDisconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
dba25@sun02-zone:/oracle/dba/dba25/DEMO] rman target /
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: DBA25 (DBID=392127005)
RMAN> restore datafile '/oracle/dba/dba25/dbs/sample01.dbf';
Starting restore at 07-NOV-08
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=11 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to /oracle/dba/dba25/dbs/sample01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle/dba/dba25/DEMO/f4.bak tag=TAG20081107T103333 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 07-NOV-08
RMAN> recover datafile '/oracle/dba/dba25/dbs/sample01.dbf';
Starting recover at 07-NOV-08
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 2 is already on disk as file /oracle/dba/dba25/ORADATA/ARCHIVE1/arch_2.arc
archive log thread 1 sequence 3 is already on disk as file /oracle/dba/dba25/ORADATA/ARCHIVE1/arch_3.arc
archive log thread 1 sequence 4 is already on disk as file /oracle/dba/dba25/ORADATA/ARCHIVE1/arch_4.arc
archive log thread 1 sequence 5 is already on disk as file /oracle/dba/dba25/ORADATA/ARCHIVE1/arch_5.arc
archive log thread 1 sequence 6 is already on disk as file /oracle/dba/dba25/ORADATA/ARCHIVE1/arch_6.arc
archive log filename=/oracle/dba/dba25/ORADATA/ARCHIVE1/arch_2.arc thread=1 sequence=2
archive log filename=/oracle/dba/dba25/ORADATA/ARCHIVE1/arch_3.arc thread=1 sequence=3
archive log filename=/oracle/dba/dba25/ORADATA/ARCHIVE1/arch_4.arc thread=1 sequence=4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/07/2008 10:51:10
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/oracle/dba/dba25/ORADATA/ARCHIVE1/arch_4.arc'
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/oracle/dba/dba25/dbs/log1b.rdo'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 1 thread 1: '/oracle/dba/dba25/dbs/log01a.rdo'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3문제는 sample01.dbf를 복구하여 시점정보를 다른 파일과 맞추려고 하지만, rdo파일이 없기 때문에 시점을 일치시켜서 데이터를 넣을 수 없다.
따라서 우선 전체 데이터 베이스를 모두 restore 하여 불완전 복구를 진행해야한다.
run {set until logsequence thread1;
recover database;}
202~207p 내용을 확인
5. 불완전 복구 시나리오 시작
SQL> quit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
dba25@sun02-zone:/oracle/dba/dba25] ls -ltr ~/ORADATA/ARCHIVE1
total 1002
-rw-r----- 1 dba25 dba 86528 Nov 6 11:12 arch_123.arc
-rw-r----- 1 dba25 dba 1024 Nov 6 11:13 arch_124.arc
-rw-r----- 1 dba25 dba 12288 Nov 6 11:15 arch_125.arc
-rw-r----- 1 dba25 dba 1024 Nov 6 18:52 arch_7.arc
-rw-r----- 1 dba25 dba 22016 Nov 6 18:52 arch_8.arc
-rw-r----- 1 dba25 dba 1024 Nov 6 18:52 arch_9.arc
-rw-r----- 1 dba25 dba 254464 Nov 7 09:21 arch_1.arc
-rw-r----- 1 dba25 dba 65536 Nov 7 10:37 arch_2.arc
-rw-r----- 1 dba25 dba 4096 Nov 7 10:37 arch_3.arc
-rw-r----- 1 dba25 dba 1536 Nov 7 10:37 arch_4.arc
-rw-r----- 1 dba25 dba 1024 Nov 7 10:44 arch_5.arc
-rw-r----- 1 dba25 dba 44544 Nov 7 10:46 arch_6.arc
dba25@sun02-zone:/oracle/dba/dba25] rman target /
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: DBA25 (DBID=392127005)
RMAN> restore database;
Starting restore at 07-NOV-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oracle/dba/dba25/dbs/system01.dbf
restoring datafile 00002 to /oracle/dba/dba25/dbs/undotbs.dbf
restoring datafile 00003 to /oracle/dba/dba25/dbs/users01.dbf
restoring datafile 00004 to /oracle/dba/dba25/dbs/indx01.dbf
restoring datafile 00005 to /oracle/dba/dba25/dbs/sample01.dbf
restoring datafile 00006 to /oracle/dba/dba25/dbs/querydata01.dbf
restoring datafile 00007 to /oracle/dba/dba25/dbs/d.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle/dba/dba25/DEMO/f4.bak tag=TAG20081107T103333 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 07-NOV-08
RMAN> run {
2> set until logseq 6 thread 1;
3> recover database;
4> sql 'alter database open resetlogs'; }
executing command: SET until clause
Starting recover at 07-NOV-08
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 2 is already on disk as file /oracle/dba/dba25/ORADATA/ARCHIVE1/arch_2.arc
archive log thread 1 sequence 3 is already on disk as file /oracle/dba/dba25/ORADATA/ARCHIVE1/arch_3.arc
archive log thread 1 sequence 4 is already on disk as file /oracle/dba/dba25/ORADATA/ARCHIVE1/arch_4.arc
archive log thread 1 sequence 5 is already on disk as file /oracle/dba/dba25/ORADATA/ARCHIVE1/arch_5.arc
archive log filename=/oracle/dba/dba25/ORADATA/ARCHIVE1/arch_2.arc thread=1 sequence=2
archive log filename=/oracle/dba/dba25/ORADATA/ARCHIVE1/arch_3.arc thread=1 sequence=3
archive log filename=/oracle/dba/dba25/ORADATA/ARCHIVE1/arch_4.arc thread=1 sequence=4
archive log filename=/oracle/dba/dba25/ORADATA/ARCHIVE1/arch_5.arc thread=1 sequence=5
media recovery complete
Finished recover at 07-NOV-08
sql statement: alter database open resetlogs
RMAN> report need backup days 1;
using target database controlfile instead of recovery catalog
Report of files whose recovery needs more than 1 days of archived logs
File Days Name
---- ----- -----------------------------------------------------
1 1895 /oracle/dba/dba25/dbs/system01.dbf
2 1895 /oracle/dba/dba25/dbs/undotbs.dbf
3 1895 /oracle/dba/dba25/dbs/users01.dbf
4 1895 /oracle/dba/dba25/dbs/indx01.dbf
5 1895 /oracle/dba/dba25/dbs/sample01.dbf
6 1895 /oracle/dba/dba25/dbs/querydata01.dbf
RMAN> exit
Recovery Manager complete.
dba25@sun02-zone:/oracle/dba/dba25/DEMO] sqlplus '/as sysdba'
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Nov 7 11:12:39 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> select count(*) from scott.dept;
COUNT(*)
----------
128
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 0 5242880 2 YES UNUSED
0
2 1 0 5242880 2 YES UNUSED
0
3 1 1 256000 2 NO CURRENT
441394 07-NOV-08
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> !ls ~/dbs/*.rdo/oracle/dba/dba25/dbs/log01a.rdo /oracle/dba/dba25/dbs/log1b.rdo /oracle/dba/dba25/dbs/log3a.rdo
/oracle/dba/dba25/dbs/log02a.rdo /oracle/dba/dba25/dbs/log2b.rdo /oracle/dba/dba25/dbs/log3b.rdo
Oracle Network 관리
OracleNetwork
OracleNet : TCP위에 올라가서 오라클 DB 네트워크 통신을 관리하는 서비스
장점
프로토콜 독립성
다양한 플랫폼 지원
보안 기능 지원
서버 설정
리스너 프로세스의 특성 (238~239)
리모트에서 사용자의 요청이 들어올때 이를 처리하기 위해서는 반드시 리스너 프로세스가 실행 중이어야 함
리스너는 최초 리모트 클라이언트가 DB에 접속을 요청할때 연결을 받고, 서버 프로세스를 생성하여 매핑해주는 역할까지만 하게됀다.
그뒤에는 클라이언트와 서버프로세스가 직접 통신을 통해서 작업을 수행함.
listener.ora (241p)
리스너의 기동을 위해서 설정하는 파일.
listener.ora sample
--------------------------------------------------------------------------------
L1= (protocol, host, port)
SID_LIST_L1 = (oracle home 위치, db 이름)
L2= (....)
SID_LIST_L2= (...)
lsnrctl
LSNRCTL> start L1
1. 리스너 설정하기
dba25@sun02-zone:/oracle/dba/dba25/network/admin] cp /oracle/dba/dba26/network/admin/lis*.ora .
dba25@sun02-zone:/oracle/dba/dba25/network/admin] vi listener.ora
"listener.ora" 20 lines, 425 characters
LISTENER.ORA Network Configuration File: /oracle/app/product/9.2.0/network/admin/listener.ora
Generated by Oracle configuration tools.
ABC25 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 70.12.200.152)(PORT = 2225))
)
)
)
SID_LIST_ABC25 =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /oracle/dba/dba25)
(SID_NAME = DBA25)
)
)
2. 리스너 기동
dba25@sun02-zone:/oracle/dba/dba25/network/admin] lsnrctl
LSNRCTL for Solaris: Version 9.2.0.1.0 - Production on 07-NOV-2008 13:42:50
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> start abc25
Starting /oracle/dba/dba25/bin/tnslsnr: please wait...
TNSLSNR for Solaris: Version 9.2.0.1.0 - Production
System parameter file is /oracle/dba/dba25/network/admin/listener.ora
Log messages written to /oracle/dba/dba25/network/log/abc25.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=70.12.200.152)(PORT=2225)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=70.12.200.152)(PORT=2225)))
STATUS of the LISTENER
------------------------
Alias abc25
Version TNSLSNR for Solaris: Version 9.2.0.1.0 - Production
Start Date 07-NOV-2008 13:42:55
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /oracle/dba/dba25/network/admin/listener.ora
Listener Log File /oracle/dba/dba25/network/log/abc25.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=70.12.200.152)(PORT=2225)))
Services Summary...
Service "DBA25" has 1 instance(s).
Instance "DBA25", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> exit
dba25@sun02-zone:/oracle/dba/dba25/network/admin] ps -ef | grep -i dba25
dba25 24401 8843 0 10:49:25 ? 0:00 ora_lgwr_DBA25
dba25 24407 8843 0 10:49:25 ? 0:00 ora_reco_DBA25
dba25 26048 22687 0 13:44:08 pts/22 0:00 grep -i dba25
dba25 24405 8843 0 10:49:25 ? 0:00 ora_smon_DBA25
dba25 24409 8843 0 10:49:25 ? 0:00 ora_arc0_DBA25
dba25 24403 8843 0 10:49:25 ? 0:02 ora_ckpt_DBA25
dba25 24771 24747 0 11:07:30 pts/4 0:00 -ksh
dba25 24397 8843 0 10:49:25 ? 0:01 ora_pmon_DBA25
dba25 24399 8843 0 10:49:25 ? 0:01 ora_dbw0_DBA25
dba25 22687 22684 0 08:53:12 pts/22 0:00 -ksh
dba25 24411 8843 0 10:49:25 ? 0:00 ora_arc1_DBA25
dba25 26008 8843 0 13:42:55 ? 0:00 /oracle/dba/dba25/bin/tnslsnr abc25 -inherit
dba25 26047 22687 0 13:44:08 pts/22 0:00 ps -ef
3. DB 기동 중지후 리스너 동작 확인
dba25@sun02-zone:/oracle/dba/dba25/network/admin] sqlplus '/as sysdba'
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Nov 7 13:47:12 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> shutdown abort;
ORACLE instance shut down.
SQL> !ps -ef | grep -i dba25
dba25 26180 26179 0 13:47:12 ? 0:00 oracleDBA25 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
dba25 24771 24747 0 11:07:30 pts/4 0:00 -ksh
dba25 22687 22684 0 08:53:12 pts/22 0:00 -ksh
dba25 26195 26179 0 13:47:23 pts/22 0:00 grep -i dba25
dba25 26196 26195 0 13:47:23 pts/22 0:00 ps -ef
dba25 26008 8843 0 13:42:55 ? 0:00 /oracle/dba/dba25/bin/tnslsnr abc25 -inherit
dba25 26179 22687 0 13:47:12 pts/22 0:00 sqlplus /as sysdbaSQL> startup
ORACLE instance started.
Total System Global Area 60379276 bytes
Fixed Size 454796 bytes
Variable Size 54525952 bytes
Database Buffers 4194304 bytes
Redo Buffers 1204224 bytes
Database mounted.
Database opened.
클라이언트 설정
클라이언트가 서버에 접속하기 위해서는 아래와 같은 내용이 지정되어 있어야한다.
a) 프로토콜 설정
b) 서버 주소 설정
c) 포트 번호 설정
d) DB 이름 설정
tnsnames.ora
--------------------------------------------------------------------------------
TEST=(a, b, c, d information)
실습내용 그림파일
--------------------------------------------------------------------------------
practice_flow.zip
dba25@sun02-zone:/oracle/dba/dba25/network/admin] lsnrctl serv abc25
LSNRCTL for Solaris: Version 9.2.0.1.0 - Production on 07-NOV-2008 14:22:32
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=70.12.200.152)(PORT=2225)))
Services Summary...
Service "DBA25" has 1 instance(s).
Instance "DBA25", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:7 refused:0
LOCAL SERVER
The command completed successfully
dba25@sun02-zone:/oracle/dba/dba25/network/admin] lsnrctl stop abc25
LSNRCTL for Solaris: Version 9.2.0.1.0 - Production on 07-NOV-2008 14:24:54
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=70.12.200.152)(PORT=2225)))
The command completed successfully
윈도우 시스템
Microsoft Windows 2000 [Version 5.00.2195]
(C) Copyright 1985-2000 Microsoft Corp.
C:\Documents and Settings\student>sqlplus scott/tiger@test
SQL*Plus: Release 9.2.0.1.0 - Production on 금 Nov 7 14:13:48 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
다음에 접속됨:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> select count(*) from scott.dept;
COUNT(*)
----------
128
SQL>
리스너를 중지시키더라도 중지되기 이전에 존재했던 커넥션은 리스너의 동작과는 무관하게 동작한다.
새로 들어오는 연결은 커넥션이 맺어지지 않는다.
C:\oracle\ora92\network\ADMIN>type sqlnet.ora
SQLNET.ORA Network Configuration File: C:\oracle\ora92\NETWORK\ADMIN\sqlnet.ora
Generated by Oracle configuration tools.
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (HOSTNAME, TNSNAMES)
아무리 리스너를 등록하더라도 호스트 이름을 찾는 디렉토리 설정이 되지 않으면 해당 호스트를 찾을 수 없다.
C:\oracle\ora92\network\ADMIN>edit c:\winnt\system32\drivers\etc\hosts
C:\oracle\ora92\network\ADMIN>sqlplus scott/tiger@db1
SQL*Plus: Release 9.2.0.1.0 - Production on 금 Nov 7 14:35:08 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
다음에 접속됨:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> select * from test
2 ;
ID NAME
---------- ------------------------------
1 aaa
2 bbb
3 ccc
SQL> select name from v$database;
NAME
---------
TESTDB호스트 이름을 기반으로 접속을 실시할 경우 해당 호스트에 리스너가 한개만 존재할 경우에만 가능하다.
SQL> select name from v$datafile;
NAME
---------
TESTDB
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
C:\ORACLE\ORA92\TESTDB\SYSTEM01.DBF
C:\ORACLE\ORA92\TESTDB\UNDOTBS01.DBF
C:\ORACLE\ORA92\TESTDB\DRSYS01.DBF
C:\ORACLE\ORA92\TESTDB\INDX01.DBF
C:\ORACLE\ORA92\TESTDB\TOOLS01.DBF
C:\ORACLE\ORA92\TESTDB\USERS01.DBF
C:\ORACLE\ORA92\TESTDB\XDB01.DBF
7 개의 행이 선택되었습니다.
클라이언트측
sqlnet.ora
names.directory_path=(hostname, tnsnames, onames ...)
tnsnames.ora (tnsnames 설정시)
TEST=(프로토콜, 서버주소, 포트, DB이름)
서버측
listener.ora
리스너이름=(주소)
SID_LIST_리스너이름=(DB정보)
※ 리스너까지 기동되어야 클라이언트와 연결 가능
오라클 넷으이 접속 방식
호스트 이름 지정
호스트 이름 지정 방식은 리스너의 포트가 1521 포트로 열린 상태여야 한다.
로컬 이름 지정 (TNSNAME.ORA)
주로 사용하는 방식
디렉토리 이름 지정 (LDAP)
오라클 네임즈 서버 이용 (ONAMES)
외부 이름 지정 방식
원격에서 관리자 모드로 접속하기
(Client Windows)
C:\oracle\ora92\network\ADMIN>sqlplus scott/tiger@test
SQL*Plus: Release 9.2.0.1.0 - Production on 금 Nov 7 15:08:28 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
다음에 접속됨:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> shutdown abort;
ORA-01031: insufficient privileges
SQL> exit
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production에서 분리되었습니다.
원격에서 관리자로 접속하기 위해서는 서버측에 관리자 접속용 패스워드 파일을 생성해야한다.
(orapwd 를 이용해서 작성, $ORACLE_HOME/dbs/ 에 위치, orapwSID 라는 이름으로 지정)
초기 패스워드 파일에는 SYS의 패스워드를 기본적으로 만들게 된다.
설사 파일을 만들었더라도 파라미터 파일에서 설정을 하지 않으면 사용하지 않는다.
remote_login_passwordfile=exclusive
(<>remote_login_passwordfile=none)
(Oracle Server)
1. 패스워드 파일 생성
dba25@sun02-zone:/oracle/dba/dba25] cd $ORACLE_HOME/dbs
dba25@sun02-zone:/oracle/dba/dba25/dbs] orapwd file=orapwDBA25 password=a1234
dba25@sun02-zone:/oracle/dba/dba25/dbs] ls -al orapwDBA25* -rwSr----- 1 dba25 dba 1536 Nov 7 15:32 orapwDBA25
dba25@sun02-zone:/oracle/dba/dba25/dbs] vi orapwDBA25 "orapwDBA25" [Incomplete last line] 1 line, 1536 characters (1453 null)
^B^BZ[\]ORACLE Remote Password file^[INTERNAL^HC69ECBDE2A2941D2^P^OSYS^C5FA9749932B55F87^P^O
~
...
2. 파라미터 설정하기
dba25@sun02-zone:/oracle/dba/dba25/dbs] vi init*.ora
Date: 28 Feb 2001
Updated: 24 Apr 2001
File Name: initsid.ora
File Version: 1.1.3
background_dump_dest=$ORACLE_HOME/ADMIN/BDUMP
compatible=9.2.0
control_files=($ORACLE_HOME/dbs/ctrl01.ctl, $ORACLE_HOME/dbs/ctrl02.ctl)
core_dump_dest=$ORACLE_HOME/ADMIN/CDUMP
db_block_size=4096
db_cache_size=4M
db_domain=world
db_name=DBA25
global_names=TRUE
instance_name=DBA25
java_pool_size=0
log_archive_dest_1="LOCATION=$ORACLE_HOME/ORADATA/ARCHIVE1/ MANDATORY"
log_archive_dest_2="LOCATION=$ORACLE_HOME/ORADATA/ARCHIVE2/ OPTIONAL"
log_archive_format=arch_%s.arc
#log_archive_max_processes=2
log_archive_start=true
max_dump_file_size=10240
remote_login_passwordfile=exclusive
service_names=DBA25
shared_pool_size=40M
undo_management=AUTO
undo_tablespace=UNDOTBS
user_dump_dest=$ORACLE_HOME/ADMIN/UDUMP
~
3. 데이터 베이스 재 기동
dba25@sun02-zone:/oracle/dba/dba25/dbs] sqlplus '/as sysdba'
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Nov 7 15:36:08 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> startup force
ORACLE instance started.
Total System Global Area 60379276 bytes
Fixed Size 454796 bytes
Variable Size 54525952 bytes
Database Buffers 4194304 bytes
Redo Buffers 1204224 bytes
Database mounted.
Database opened.
에러가 발생했다면 패스워드 파일의 이름을 확인해본다.
SQL> show parameter remote
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_archive_enable string true
remote_dependencies_mode string TIMESTAMP
remote_listener string
remote_login_passwordfile string EXCLUSIVE
remote_os_authent boolean FALSE
remote_os_roles boolean FALSE
(Client Windows)
C:\oracle\ora92\network\ADMIN>sqlplus "sys/a1234@test as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on 금 Nov 7 15:27:48 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
다음에 접속됨:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> startupORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown abort;
ORACLE 인스턴스가 종료되었습니다.
SQL> startup
ORACLE 인스턴스가 시작되었습니다.
Total System Global Area 60379276 bytes
Fixed Size 454796 bytes
Variable Size 54525952 bytes
Database Buffers 4194304 bytes
Redo Buffers 1204224 bytes
데이터베이스가 마운트되었습니다.
데이터베이스가 열렸습니다.
SQL>
공유 서버 설정
임시로 동시 유저가 늘어나는 경우에 사용하는 방식.
여기서 동시 유저라는 것은 말그대로 순간수간의 유저수를 말한다.
전용서버 구성 : 하나의 서버 프로세스가 하나의 사용자 프로세스만 처리
공유서버 구성 : 하나의 서버가 여러 사용자 프로세스를 처리
공유 서버의 장점
일정 개수의 공유 서버 프로세스가 번갈아 가면서 다수의 사용자 프로세스 요구를 처리하기 때문에 인스턴스당 프로세스 개수 감소
다수의 사용자 프로세스가 접속해도 서버 프로세스는 일정 개수만 생성 (리소스 사용양 감소)
허용 가능한 사용자 수 증가
로드 밸런싱 수행
메모리 사용량과 시스템 오버헤드 감소
※ 오라클에서는 불가피한 상황에서만 사용하는 것을 권장한다.
[SGA]
sharedpool = reqeust queue + result queue
shared server process, dispatcher process
--------------------------------------------------------------------------------
사용자 쿼리 처리 과정 260page
사용자 접속 후 쿼리 전송 -> 디스패처 프로세스 -> sharedpool.requestqueue 에 저장 ->
shared server process 에서 쿼리 처리 ->sharedpool.resultqueue에 결과 저장
-> 디스패처 프로세서 결과 로드 -> 사용자에게 결과 전송
※ 공유 서버 프로세스, 디스패처 프로세스의 생성수를 줄여서 메모리 사용양을 줄여준다는 장점이 존재하지만 서버의 reponse time 이 증가한다는 단점이 존재
실습파일
3일차.zip
목차
--------------------------------------------------------------------------------
사용자 백업 및 복구
Archivelog 모드로 운영시
완전복구
불완전 복구
Logical 백업 복구
export/import 개요
export 유틸리티
import 유틸리티
실습
RMAN을 이용한 백업 복구
RMAN 개요
RMAN 명령어
실습) 간단한 백업
RMAN을 이용한 백업
실습) RMAN을 이용한 장애 복구
Oracle Network 관리
OracleNetwork#
서버 설정
클라이언트 설정
공유 서버 설정
사용자 백업 및 복구
Archivelog 모드로 운영시
완전복구
장애난 파일을 컨트롤 파일의 기준시점과 일치 가능.
동작
a) 장애난 파일만 restore
b) recover database; 데이터베이스 전체를 복구하기를 원하는 경우
recover datafile 'filename';
recover tablespace 'tablespacename' :
불완전 복구
장애난 파일을 ctl과 동일한 시점으로 일치 불가능.
※ 모든 datafile 의 시점은 일치해야한다.
동작
컨트롤 파일 백업 미 이용시
a) 모든 datafile restore
b) recover database until time 'timestamp', cancel, change SCN#; 3가지의 옵션이 존재, MOUNT단계에서 가능
c) alter database open resetlogs; 강제로 시점 정보를 초기화함
d) 데이터 베이스 전체 복원 실시
컨트롤 파일 백업 이용시
a) 모든 datafile restore, *.ctl restore
b) recover database using backup controlfile until ...; *.rdo 장애시에는 반드시 until cancel 로 복구해야함
c) alter database open resetlogs;
d) 데이터 베이스 전체 복원 실시
Logical 백업 복구
[데이터베이스] --(export)--> [OS filetype] --(import)--> [데이터베이스]
이 기종 플랫폼 간에도 데이터의 변환이 가능하다.
테이블등의 객체가 함께 추출됨.
export/import 개요
시점정보를 제외한 논리적 백업을 제공
리두 로그 기록을 적용할 수 없기 때문에 데이터 손실이 발생 가능
명령 행 입력을 이용한 실행 (많이 수행하는 방식)
대화식 인터페이스를 지원
매개 변수 파일을 이용한 백업, 복구를 제공
Oracle Enterprise Manager를 이용해서 사용가능
export 유틸리티
특성
서버간의 데이터 migration 용도로 주로 사용함
테이블 재구성 및 블록의 손상 확인 가능
사용자 에러에 대한 복호 기능
수행모드
테이블, 사용자, 테이블스페이스, 전체 백업
파라미터
userid/pasasword
file : target filename
rows : 테이블 데이터를 백업 여부를 결정. rows=0 을 이용하면 DDL 만 생성된다.
full
owner
tables
tablespaces
consistent : 백업 진행중의 테이블 데이터가 변경중이라면 변경 이전의 데이터를 백업.
데이터의 백업이 상당히 오랜 시간이 걸리는 작업이기 때문에 백업중에 발생하는 데이터의 변경으로 인한 데이터 일치가 문제시 된다.
백업 작업의 수행 시간과는 무관하게 명령어가 실행되는 시점을 기준으로 데이터를 백업하는 방식이다.
(consistent=Y라는 옵션을 사용하면 db가 언두로그를 이용해서 백업을 하기 때문에 db에 부하를 많이 주기 때문에 운영중에는 잘 사용하지 않는다)
실재로 이 옵션을 주면 export failure도 발생.
direct : 데이터 버퍼 캐시 영역을 사용하지 않고 백업하기 때문에 빠른 백업 가능
db 버퍼 캐시를 통하지 않고 서버 프로세스 메모리 영역에서 작업하는 방식 (TTCTwoTaskCommon)
feedback : 백업하는 도중에 행의 수마다 점(.)을 찍는다.
dba25@sun02-zone:/oracle/dba/dba25] exp scott/tiger file=a1.dmp tables=emp, dept;
Export: Release 9.2.0.1.0 - Production on Fri Nov 7 08:54:07 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table EMP 14 rows exported
. . exporting table DEPT 64 rows exported
Export terminated successfully without warnings.
dba25@sun02-zone:/oracle/dba/dba25] strings a1.dmp | more
...
CREATE TABLE "EMP" ("EMPNO" NUMBER(4, 0) NOT NULL ENABLE, "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4, 0), "HI
REDATE" DATE, "SAL" NUMBER(7, 2), "COMM" NUMBER(7, 2), "DEPTNO" NUMBER(2, 0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 2
55 STORAGE(INITIAL 1048576 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "SAMPLE" LOGGING NOCOMPRESS
...
※ export 라는 툴은 시점에 대한 정보가 들어가는 것이 아니라 데이터에 대한 내용을 단순히 SQL 형태로 만들어서 차후 데이터를 넣을 수 있도록 만드는 목적을 가진 프로그램이다.
import 유틸리티
파라미터
userid
rows
ignore : 이미 존재하는 테이블에 대한 복구 작업을 실행하면 object already exists라는 에러를 발생하는데 이 에러를 무시하고 계속 복구 작업 수행
commit : Y를 설정하면 각 배열을 입력한 후 commit
커밋의 단위는 buffer 에 지정된 만큼씩이다.
수행모드
table, user, tablespace, full database
순서
테이블 생성
데이터 import
인덱스 생성
트리거 import
무결성 제약 조건 활성화
비트맵, 함수 기반 인덱스 생성
실습
dba25@sun02-zone:/oracle/dba/dba25] cd ~/DEMO
dba25@sun02-zone:/oracle/dba/dba25/DEMO] exp scott/tiger file=a1.dmp owner=scott;
Export: Release 9.2.0.1.0 - Production on Fri Nov 7 09:21:53 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table BONUS 0 rows exported
. . exporting table DEPT 64 rows exported
. . exporting table DUMMY 1 rows exported
. . exporting table EMP 14 rows exported
. . exporting table SALGRADE 5 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
dba25@sun02-zone:/oracle/dba/dba25/DEMO] sqlplus scott/tiger
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Nov 7 09:23:22 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> select count(*) from dept;
COUNT(*)
----------
64
SQL> truncate table dept;
Table truncated.
SQL> select * from dept;
no rows selected
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
dba25@sun02-zone:/oracle/dba/dba25/DEMO] imp scott/tiger file=a1.dmp tables=dept;
Import: Release 9.2.0.1.0 - Production on Fri Nov 7 09:24:25 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
IMP-00015: following statement failed because the object already exists:
"CREATE TABLE "DEPT" ("DEPTNO" NUMBER(2, 0), "DNAME" VARCHAR2(14), "LOC" VAR"
"CHAR2(13)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 1"
"048576 FREELISTS 1 FREELIST GROUPS 1) LOGGING NOCOMPRES"
"S"
Import terminated successfully with warnings.
dba25@sun02-zone:/oracle/dba/dba25/DEMO] imp scott/tiger file=a1.dmp tables=dept ignore=y;
Import: Release 9.2.0.1.0 - Production on Fri Nov 7 09:25:02 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. . importing table "DEPT" 64 rows imported
Import terminated successfully without warnings.
dba25@sun02-zone:/oracle/dba/dba25/DEMO] sqlplus scott/tiger
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Nov 7 09:25:33 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> select count(*) from dept;
COUNT(*)
----------
64
참고) 10g의 경우
drop table 명령 수행시 해당 테이블을 recyclebin 에서 확인 가능
> flashback table dept before drop;
expdp, impdp 데이터 펑프 기능 제공
> exp, imp 와 동일한 작업을 하고 성능이 올라간 녀석 (+20%)
RMAN을 이용한 백업 복구
RMAN 개요
Recovery Manager
DB, 테이블스페이스, 데이터 파일, 컨트롤 파일 및 아카이브 파일 로그의 백업
자주 수행되는 백업 및 복구 작업 저장
증분 블록 레벨 백업 수행(수정된 블록 백업) : RMAN의 가장 큰 특징. 블록단위로 작업 수행.
사용되지 않는 블록 건너뛰기 : 증분 백업을 통해서 백업 본의 파일이 작다.
백업 파일 사이즈 지정
백업 중 훼손 된 블록 감지
자동 병력화, 리두 생성 감소로 성능 향상
※ 온라인 백업을 실시중에도 백업을 위한 라킹 작업을 하지 않아도 된다는 장점
※ RMAN 실행의 대상이 되는 DB를 target DB라고 부른다.
[RMAN] --(channel : 작업용 프로세스)--> [Target DB]
a) backup database format '/DISK1/full.bak';
b) channel 기동
c) full.bak 통합 파일(aka backup set, backup piece)로 저장
d) controlfile에 기록(aka metadata)
※ 메타데이터의 기록 유지 기간은 파라메터 변수로 저장된다. 문제는 이 내용이 실재로는 계속 유지되어야하기 때문에 별도의 백업용 데이터베이스에 해당 정보를 넣는 것이 가능하다. (aka catalog database)
RMAN 저장소 : 대상 데이터베이스와 백업 및 복구 작업에 대한 멘타 데이터. 대상 데이터 베이스의 제어파일에 저장
control_file_record_keep_time 레코드가 겹쳐 쓰이기 전까지 저장되는 최소일자 수를 지정
※ recovery catalog 를 사용할 경우의 장점
한 catalog에 여러 target 대이터 베이스의 메타데이터 저장가능
rman의 저장 스크립트를 사용할 수 있다.
블록 단위의 증분 백업이 가능하다.
컨트롤 파일 분실시 유연하게 대처 가능함.
RMAN 명령어
- 조회용
show
list
report
- 수정용
catalog
change
delete
crosscheck
RMAN에서 타겟 데이터베이스의 컨트롤 파일 내의 정보를 조회 수정하는데 사용한다.
조회용 명령어
list : 백업 셋과 복사본을 보여준다.
report : 등록된 정보를 이용해서 한번 처리한뒤 보여준다.
RMAN> report need backup days 1;
using target database controlfile instead of recovery catalog
Report of files whose recovery needs more than 1 days of archived logs
File Days Name
---- ----- -----------------------------------------------------
1 1895 /oracle/dba/dba25/dbs/system01.dbf
2 1895 /oracle/dba/dba25/dbs/undotbs.dbf
3 1895 /oracle/dba/dba25/dbs/users01.dbf
4 1895 /oracle/dba/dba25/dbs/indx01.dbf
5 1895 /oracle/dba/dba25/dbs/sample01.dbf
6 1895 /oracle/dba/dba25/dbs/querydata01.dbf
RMAN> list copy of database;
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
2 5 A 07-NOV-08 420655 07-NOV-08 /oracle/dba/dba25/DEMO/sam.cpy
RMAN> exit
2. RMAN을 이용하지 않는 백업 실시
Recovery Manager complete.
dba25@sun02-zone:/oracle/dba/dba25/DEMO] sqlplus '/as sysdba'
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Nov 7 11:35:14 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> alter tablespace users begin backup;
Tablespace altered.
SQL> !cp $HOME/dbs/users01.dbf $HOME/DEMO/u.dbf
SQL> alter tablespace users end backup;
Tablespace altered.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
3. RMAN에 백업 파일 내역 등록
dba25@sun02-zone:/oracle/dba/dba25/DEMO] rman target /
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: DBA25 (DBID=392127005)
RMAN> list copy of database;
using target database controlfile instead of recovery catalog
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
2 5 A 07-NOV-08 420655 07-NOV-08 /oracle/dba/dba25/DEMO/sam.cpyRMAN으로 작업한 내용만이 자동 등록된다는 사실을 알자
RMAN> catalog datafilecopy '$HOME/DEMO/u.dbf';
cataloged datafile copy
datafile copy filename=/oracle/dba/dba25/DEMO/u.dbf recid=12 stamp=670160278
RMAN> list copy of database;
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
12 3 A 07-NOV-08 441980 07-NOV-08 /oracle/dba/dba25/DEMO/u.dbf
2 5 A 07-NOV-08 420655 07-NOV-08 /oracle/dba/dba25/DEMO/sam.cpy차후 RMAN이 해당 파일을 복구에 사용가능하록 등록하는 과정임.
4. RMAN에 등록된 파일 정보 상태를 변경
RMAN> change datafilecopy '$HOME/DEMO/u.dbf' uncatalog;
uncataloged datafile copy
datafile copy filename=/oracle/dba/dba25/DEMO/u.dbf recid=12 stamp=670160278
Uncataloged 1 objects
RMAN> list copy of database;
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
2 5 A 07-NOV-08 420655 07-NOV-08 /oracle/dba/dba25/DEMO/sam.cpy
RMAN> exit;
Recovery Manager complete.
dba25@sun02-zone:/oracle/dba/dba25/DEMO] ls -al $HOME/DEMO/u.dbf
-rwxr-x--- 1 dba25 dba 5246976 Nov 7 11:35 /oracle/dba/dba25/DEMO/u.dbf
dba25@sun02-zone:/oracle/dba/dba25/DEMO] rman target /
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: DBA25 (DBID=392127005)
RMAN> catalog datafilecopy '$HOME/DEMO/u.dbf';
using target database controlfile instead of recovery catalog
cataloged datafile copy
datafile copy filename=/oracle/dba/dba25/DEMO/u.dbf recid=13 stamp=670160666
RMAN> list copy of database;
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
13 3 A 07-NOV-08 441980 07-NOV-08 /oracle/dba/dba25/DEMO/u.dbf
2 5 A 07-NOV-08 420655 07-NOV-08 /oracle/dba/dba25/DEMO/sam.cpy
RMAN> delete datafilecopy '$HOME/DEMO/u.dbf';
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=9 devtype=DISK
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
13 3 A 07-NOV-08 441980 07-NOV-08 /oracle/dba/dba25/DEMO/u.dbf
Do you really want to delete the above objects (enter YES or NO)? YES
deleted datafile copy
datafile copy filename=/oracle/dba/dba25/DEMO/u.dbf recid=13 stamp=670160666
Deleted 1 objects
RMAN> list copy of database;
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
2 5 A 07-NOV-08 420655 07-NOV-08 /oracle/dba/dba25/DEMO/sam.cpy
RMAN> exit
Recovery Manager complete.
dba25@sun02-zone:/oracle/dba/dba25/DEMO] ls -al $HOME/DEMO/u.dbf
/oracle/dba/dba25/DEMO/u.dbf: No such file or directory
실습) 간단한 백업
SQL> exit Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
dba25@sun02-zone:/oracle/dba/dba25/DEMO] rman target /
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: DBA25 (DBID=392127005)
RMAN> list backup;
using target database controlfile instead of recovery catalog
RMAN> report need backup days 1 ;
RMAN을 이용해서 백업을 해야할 데이터베이스 파일을 보여준다. 하루를 기준으로 하는 옵션
Report of files whose recovery needs more than 1 days of archived logs
File Days Name
---- ----- -----------------------------------------------------
1 1895 /oracle/dba/dba25/dbs/system01.dbf
2 1895 /oracle/dba/dba25/dbs/undotbs.dbf
3 1895 /oracle/dba/dba25/dbs/users01.dbf
4 1895 /oracle/dba/dba25/dbs/indx01.dbf
5 1895 /oracle/dba/dba25/dbs/sample01.dbf
6 1895 /oracle/dba/dba25/dbs/querydata01.dbf
RMAN> backup datafile '~/dbs/sample01.dbf' format '~/DEMO/sam.bak';
Starting backup at 07-NOV-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=13 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 11/07/2008 09:59:15
RMAN-20201: datafile not found in the recovery catalog
RMAN-06010: error while looking up datafile: /oracle/dba/dba25/dbs/~/dbs/sample01.dbf
RMAN> backup datafile '$HOME/dbs/sample01.dbf' format '$HOME/DEMO/sam.bak';변경 분에 대한 백업만을 실시한다. (다:1 백업 가능)
Starting backup at 07-NOV-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00005 name=/oracle/dba/dba25/dbs/sample01.dbf
channel ORA_DISK_1: starting piece 1 at 07-NOV-08
channel ORA_DISK_1: finished piece 1 at 07-NOV-08
piece handle=/oracle/dba/dba25/DEMO/sam.bak comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-NOV-08
RMAN> copy datafile '$HOME/dbs/sample01.dbf' to '$HOME/DEMO/sam.cpy';유닉스 커맨드 상태의 cp와 유사하다. (1:1 백업 가능)
Starting copy at 07-NOV-08
using channel ORA_DISK_1
channel ORA_DISK_1: copied datafile 5
output filename=/oracle/dba/dba25/DEMO/sam.cpy recid=2 stamp=670154452
Finished copy at 07-NOV-08
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 100K DISK 00:00:01 07-NOV-08
BP Key: 1 Status: AVAILABLE Tag: TAG20081107T095958
Piece Name: /oracle/dba/dba25/DEMO/sam.bak
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
5 Full 420637 07-NOV-08 /oracle/dba/dba25/dbs/sample01.dbf
RMAN> report need backup days 1;
Report of files whose recovery needs more than 1 days of archived logs
File Days Name
---- ----- -----------------------------------------------------
1 1895 /oracle/dba/dba25/dbs/system01.dbf
2 1895 /oracle/dba/dba25/dbs/undotbs.dbf
3 1895 /oracle/dba/dba25/dbs/users01.dbf
4 1895 /oracle/dba/dba25/dbs/indx01.dbf
6 1895 /oracle/dba/dba25/dbs/querydata01.dbf
RMAN> exit;
Recovery Manager complete.
dba25@sun02-zone:/oracle/dba/dba25/DEMO] cd $HOME/DEMO
dba25@sun02-zone:/oracle/dba/dba25/DEMO] ls -al sam.*-rw-r----- 1 dba25 dba 118784 Nov 7 09:59 sam.bak
-rw-r----- 1 dba25 dba 10489856 Nov 7 10:00 sam.cpy
dba25@sun02-zone:/oracle/dba/dba25/DEMO] ls -al ~/dbs/sample01.dbf
-rwxr-x--- 1 dba25 dba 10489856 Nov 7 10:00 /oracle/dba/dba25/dbs/sample01.dbf
RMAN을 이용한 백업
1. 이미지 복사본
COPY명령어를 이용해서 백업한 것으로 1:1로 백업된다.
디스크에만 저장. restore 작업이 불필요. 모든 블록을 포함한다는 점에서 운영 체제 백업과 가장 유사하다.
2. 백업 셋
BACKUP 명령어를 이용해서 백업한 것으로 多:1로 백업
백업 셋은 대개 둘이상의 파일을 포함
디스크 또는 테이프에 백업 셋을 기록 가능
restore 작업이 필요
백업셋은 증분, 전체 백업이 될수 있다.
백업작업 수행시 조건
데이터 베이스는 마운트 되거나 열려 있어야 한다
온라인 리두 로그 백업은 지원하지 않는다
noarchivelog 모드에서는 offline 백업만 사용
archivelog 모드에서 백업 모드로 변경하지 않고 백업 작업 수행
※ 백업본을 가지고 원래 사이즈의 원본 파일로 복원하는 것을 restore라고 부른다.
실습) RMAN을 이용한 장애 복구
1.RMAN을 이용한 복구 시나리오 사전 작업
dba25@sun02-zone:/oracle/dba/dba25/DEMO] rman target /
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: DBA25 (DBID=392127005)
RMAN> backup database format '$HOME/DEMO/f4.bak';
Starting backup at 07-NOV-08
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=9 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current controlfile in backupset
input datafile fno=00001 name=/oracle/dba/dba25/dbs/system01.dbf
input datafile fno=00002 name=/oracle/dba/dba25/dbs/undotbs.dbf
input datafile fno=00005 name=/oracle/dba/dba25/dbs/sample01.dbf
input datafile fno=00003 name=/oracle/dba/dba25/dbs/users01.dbf
input datafile fno=00004 name=/oracle/dba/dba25/dbs/indx01.dbf
input datafile fno=00007 name=/oracle/dba/dba25/dbs/d.dbf
input datafile fno=00006 name=/oracle/dba/dba25/dbs/querydata01.dbf
channel ORA_DISK_1: starting piece 1 at 07-NOV-08
channel ORA_DISK_1: finished piece 1 at 07-NOV-08
piece handle=/oracle/dba/dba25/DEMO/f4.bak comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:48
Finished backup at 07-NOV-08
RMAN> report need backup days 1;
Report of files whose recovery needs more than 1 days of archived logs
File Days Name
---- ----- -----------------------------------------------------
RMAN> exit
Recovery Manager complete.
dba25@sun02-zone:/oracle/dba/dba25/DEMO] sqlplus '/as sysdba'
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Nov 7 10:37:02 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> alter system switch logfile;
System altered.
SQL> insert into scott.dept select * from scott.dept;
64 rows created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> select count(*) from scott.dept;
COUNT(*)
----------
128
SQL> alter system switch logfile;
System altered.
SQL> !rm $HOME/dbs/sample01.dbf
SQL> shutdown abort;
ORACLE instance shut down.
2. RMAN을 이용한 데이터베이스 복구 작업
SQL> startup
ORACLE instance started.
Total System Global Area 60379276 bytes
Fixed Size 454796 bytes
Variable Size 54525952 bytes
Database Buffers 4194304 bytes
Redo Buffers 1204224 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/oracle/dba/dba25/dbs/sample01.dbf'
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
dba25@sun02-zone:/oracle/dba/dba25/DEMO] rman target /
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: DBA25 (DBID=392127005)
RMAN> restore datafile '/oracle/dba/dba25/dbs/sample01.dbf';
Starting restore at 07-NOV-08
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=11 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to /oracle/dba/dba25/dbs/sample01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle/dba/dba25/DEMO/f4.bak tag=TAG20081107T103333 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 07-NOV-08
RMAN> run { sql 'alter database open'; }
sql statement: alter database open
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 11/07/2008 10:41:28
RMAN-11003: failure during parse/execution of SQL statement: alter database open
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/oracle/dba/dba25/dbs/sample01.dbf'데이터 베이스의 시점이 맞지 않기 때문에 DB가 열리지 않는다.
RMAN> recover datafile '/oracle/dba/dba25/dbs/sample01.dbf';
Starting recover at 07-NOV-08
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 2 is already on disk as file /oracle/dba/dba25/ORADATA/ARCHIVE1/arch_2.arc
archive log thread 1 sequence 3 is already on disk as file /oracle/dba/dba25/ORADATA/ARCHIVE1/arch_3.arc
archive log thread 1 sequence 4 is already on disk as file /oracle/dba/dba25/ORADATA/ARCHIVE1/arch_4.arc
archive log filename=/oracle/dba/dba25/ORADATA/ARCHIVE1/arch_2.arc thread=1 sequence=2
media recovery complete
Finished recover at 07-NOV-08
RMAN> run { sql 'alter database open' ;}
sql statement: alter database openRMAN> exit
Recovery Manager complete.
dba25@sun02-zone:/oracle/dba/dba25/DEMO] sqlplus '/as sysdba'
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Nov 7 10:44:47 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> select * from v$recover_file;
no rows selected
SQL> select count(*) from scott.dept;
COUNT(*)
----------
128
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 5 5242880 2 YES INACTIVE
421391 07-NOV-08
2 1 6 5242880 2 YES ACTIVE
441393 07-NOV-08
3 1 7 256000 2 NO CURRENT
441550 07-NOV-08현재 로그 시퀀스가 몇번인지 확인한다.
3. 모든 리두 로그 파일 삭재 오류 발생
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/oracle/dba/dba25/dbs/log01a.rdo
/oracle/dba/dba25/dbs/log02a.rdo
/oracle/dba/dba25/dbs/log1b.rdo
/oracle/dba/dba25/dbs/log2b.rdo
/oracle/dba/dba25/dbs/log3a.rdo
/oracle/dba/dba25/dbs/log3b.rdo
6 rows selected.
SQL> !rm ~/dbs/*.rdo
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 60379276 bytes
Fixed Size 454796 bytes
Variable Size 54525952 bytes
Database Buffers 4194304 bytes
Redo Buffers 1204224 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/oracle/dba/dba25/dbs/log2b.rdo'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 2 thread 1: '/oracle/dba/dba25/dbs/log02a.rdo'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
SQL> !rm $HOME/dbs/sample01.dbf
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 60379276 bytes
Fixed Size 454796 bytes
Variable Size 54525952 bytes
Database Buffers 4194304 bytes
Redo Buffers 1204224 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/oracle/dba/dba25/dbs/sample01.dbf'
4. 완전 복구 시나리오 시작 (실패)
SQL> exitDisconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
dba25@sun02-zone:/oracle/dba/dba25/DEMO] rman target /
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: DBA25 (DBID=392127005)
RMAN> restore datafile '/oracle/dba/dba25/dbs/sample01.dbf';
Starting restore at 07-NOV-08
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=11 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to /oracle/dba/dba25/dbs/sample01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle/dba/dba25/DEMO/f4.bak tag=TAG20081107T103333 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 07-NOV-08
RMAN> recover datafile '/oracle/dba/dba25/dbs/sample01.dbf';
Starting recover at 07-NOV-08
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 2 is already on disk as file /oracle/dba/dba25/ORADATA/ARCHIVE1/arch_2.arc
archive log thread 1 sequence 3 is already on disk as file /oracle/dba/dba25/ORADATA/ARCHIVE1/arch_3.arc
archive log thread 1 sequence 4 is already on disk as file /oracle/dba/dba25/ORADATA/ARCHIVE1/arch_4.arc
archive log thread 1 sequence 5 is already on disk as file /oracle/dba/dba25/ORADATA/ARCHIVE1/arch_5.arc
archive log thread 1 sequence 6 is already on disk as file /oracle/dba/dba25/ORADATA/ARCHIVE1/arch_6.arc
archive log filename=/oracle/dba/dba25/ORADATA/ARCHIVE1/arch_2.arc thread=1 sequence=2
archive log filename=/oracle/dba/dba25/ORADATA/ARCHIVE1/arch_3.arc thread=1 sequence=3
archive log filename=/oracle/dba/dba25/ORADATA/ARCHIVE1/arch_4.arc thread=1 sequence=4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/07/2008 10:51:10
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/oracle/dba/dba25/ORADATA/ARCHIVE1/arch_4.arc'
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/oracle/dba/dba25/dbs/log1b.rdo'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 1 thread 1: '/oracle/dba/dba25/dbs/log01a.rdo'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3문제는 sample01.dbf를 복구하여 시점정보를 다른 파일과 맞추려고 하지만, rdo파일이 없기 때문에 시점을 일치시켜서 데이터를 넣을 수 없다.
따라서 우선 전체 데이터 베이스를 모두 restore 하여 불완전 복구를 진행해야한다.
run {set until logsequence thread1;
recover database;}
202~207p 내용을 확인
5. 불완전 복구 시나리오 시작
SQL> quit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
dba25@sun02-zone:/oracle/dba/dba25] ls -ltr ~/ORADATA/ARCHIVE1
total 1002
-rw-r----- 1 dba25 dba 86528 Nov 6 11:12 arch_123.arc
-rw-r----- 1 dba25 dba 1024 Nov 6 11:13 arch_124.arc
-rw-r----- 1 dba25 dba 12288 Nov 6 11:15 arch_125.arc
-rw-r----- 1 dba25 dba 1024 Nov 6 18:52 arch_7.arc
-rw-r----- 1 dba25 dba 22016 Nov 6 18:52 arch_8.arc
-rw-r----- 1 dba25 dba 1024 Nov 6 18:52 arch_9.arc
-rw-r----- 1 dba25 dba 254464 Nov 7 09:21 arch_1.arc
-rw-r----- 1 dba25 dba 65536 Nov 7 10:37 arch_2.arc
-rw-r----- 1 dba25 dba 4096 Nov 7 10:37 arch_3.arc
-rw-r----- 1 dba25 dba 1536 Nov 7 10:37 arch_4.arc
-rw-r----- 1 dba25 dba 1024 Nov 7 10:44 arch_5.arc
-rw-r----- 1 dba25 dba 44544 Nov 7 10:46 arch_6.arc
dba25@sun02-zone:/oracle/dba/dba25] rman target /
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: DBA25 (DBID=392127005)
RMAN> restore database;
Starting restore at 07-NOV-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oracle/dba/dba25/dbs/system01.dbf
restoring datafile 00002 to /oracle/dba/dba25/dbs/undotbs.dbf
restoring datafile 00003 to /oracle/dba/dba25/dbs/users01.dbf
restoring datafile 00004 to /oracle/dba/dba25/dbs/indx01.dbf
restoring datafile 00005 to /oracle/dba/dba25/dbs/sample01.dbf
restoring datafile 00006 to /oracle/dba/dba25/dbs/querydata01.dbf
restoring datafile 00007 to /oracle/dba/dba25/dbs/d.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle/dba/dba25/DEMO/f4.bak tag=TAG20081107T103333 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 07-NOV-08
RMAN> run {
2> set until logseq 6 thread 1;
3> recover database;
4> sql 'alter database open resetlogs'; }
executing command: SET until clause
Starting recover at 07-NOV-08
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 2 is already on disk as file /oracle/dba/dba25/ORADATA/ARCHIVE1/arch_2.arc
archive log thread 1 sequence 3 is already on disk as file /oracle/dba/dba25/ORADATA/ARCHIVE1/arch_3.arc
archive log thread 1 sequence 4 is already on disk as file /oracle/dba/dba25/ORADATA/ARCHIVE1/arch_4.arc
archive log thread 1 sequence 5 is already on disk as file /oracle/dba/dba25/ORADATA/ARCHIVE1/arch_5.arc
archive log filename=/oracle/dba/dba25/ORADATA/ARCHIVE1/arch_2.arc thread=1 sequence=2
archive log filename=/oracle/dba/dba25/ORADATA/ARCHIVE1/arch_3.arc thread=1 sequence=3
archive log filename=/oracle/dba/dba25/ORADATA/ARCHIVE1/arch_4.arc thread=1 sequence=4
archive log filename=/oracle/dba/dba25/ORADATA/ARCHIVE1/arch_5.arc thread=1 sequence=5
media recovery complete
Finished recover at 07-NOV-08
sql statement: alter database open resetlogs
RMAN> report need backup days 1;
using target database controlfile instead of recovery catalog
Report of files whose recovery needs more than 1 days of archived logs
File Days Name
---- ----- -----------------------------------------------------
1 1895 /oracle/dba/dba25/dbs/system01.dbf
2 1895 /oracle/dba/dba25/dbs/undotbs.dbf
3 1895 /oracle/dba/dba25/dbs/users01.dbf
4 1895 /oracle/dba/dba25/dbs/indx01.dbf
5 1895 /oracle/dba/dba25/dbs/sample01.dbf
6 1895 /oracle/dba/dba25/dbs/querydata01.dbf
RMAN> exit
Recovery Manager complete.
dba25@sun02-zone:/oracle/dba/dba25/DEMO] sqlplus '/as sysdba'
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Nov 7 11:12:39 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> select count(*) from scott.dept;
COUNT(*)
----------
128
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 0 5242880 2 YES UNUSED
0
2 1 0 5242880 2 YES UNUSED
0
3 1 1 256000 2 NO CURRENT
441394 07-NOV-08
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> !ls ~/dbs/*.rdo/oracle/dba/dba25/dbs/log01a.rdo /oracle/dba/dba25/dbs/log1b.rdo /oracle/dba/dba25/dbs/log3a.rdo
/oracle/dba/dba25/dbs/log02a.rdo /oracle/dba/dba25/dbs/log2b.rdo /oracle/dba/dba25/dbs/log3b.rdo
Oracle Network 관리
OracleNetwork
OracleNet : TCP위에 올라가서 오라클 DB 네트워크 통신을 관리하는 서비스
장점
프로토콜 독립성
다양한 플랫폼 지원
보안 기능 지원
서버 설정
리스너 프로세스의 특성 (238~239)
리모트에서 사용자의 요청이 들어올때 이를 처리하기 위해서는 반드시 리스너 프로세스가 실행 중이어야 함
리스너는 최초 리모트 클라이언트가 DB에 접속을 요청할때 연결을 받고, 서버 프로세스를 생성하여 매핑해주는 역할까지만 하게됀다.
그뒤에는 클라이언트와 서버프로세스가 직접 통신을 통해서 작업을 수행함.
listener.ora (241p)
리스너의 기동을 위해서 설정하는 파일.
listener.ora sample
--------------------------------------------------------------------------------
L1= (protocol, host, port)
SID_LIST_L1 = (oracle home 위치, db 이름)
L2= (....)
SID_LIST_L2= (...)
lsnrctl
LSNRCTL> start L1
1. 리스너 설정하기
dba25@sun02-zone:/oracle/dba/dba25/network/admin] cp /oracle/dba/dba26/network/admin/lis*.ora .
dba25@sun02-zone:/oracle/dba/dba25/network/admin] vi listener.ora
"listener.ora" 20 lines, 425 characters
LISTENER.ORA Network Configuration File: /oracle/app/product/9.2.0/network/admin/listener.ora
Generated by Oracle configuration tools.
ABC25 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 70.12.200.152)(PORT = 2225))
)
)
)
SID_LIST_ABC25 =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /oracle/dba/dba25)
(SID_NAME = DBA25)
)
)
2. 리스너 기동
dba25@sun02-zone:/oracle/dba/dba25/network/admin] lsnrctl
LSNRCTL for Solaris: Version 9.2.0.1.0 - Production on 07-NOV-2008 13:42:50
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> start abc25
Starting /oracle/dba/dba25/bin/tnslsnr: please wait...
TNSLSNR for Solaris: Version 9.2.0.1.0 - Production
System parameter file is /oracle/dba/dba25/network/admin/listener.ora
Log messages written to /oracle/dba/dba25/network/log/abc25.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=70.12.200.152)(PORT=2225)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=70.12.200.152)(PORT=2225)))
STATUS of the LISTENER
------------------------
Alias abc25
Version TNSLSNR for Solaris: Version 9.2.0.1.0 - Production
Start Date 07-NOV-2008 13:42:55
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /oracle/dba/dba25/network/admin/listener.ora
Listener Log File /oracle/dba/dba25/network/log/abc25.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=70.12.200.152)(PORT=2225)))
Services Summary...
Service "DBA25" has 1 instance(s).
Instance "DBA25", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> exit
dba25@sun02-zone:/oracle/dba/dba25/network/admin] ps -ef | grep -i dba25
dba25 24401 8843 0 10:49:25 ? 0:00 ora_lgwr_DBA25
dba25 24407 8843 0 10:49:25 ? 0:00 ora_reco_DBA25
dba25 26048 22687 0 13:44:08 pts/22 0:00 grep -i dba25
dba25 24405 8843 0 10:49:25 ? 0:00 ora_smon_DBA25
dba25 24409 8843 0 10:49:25 ? 0:00 ora_arc0_DBA25
dba25 24403 8843 0 10:49:25 ? 0:02 ora_ckpt_DBA25
dba25 24771 24747 0 11:07:30 pts/4 0:00 -ksh
dba25 24397 8843 0 10:49:25 ? 0:01 ora_pmon_DBA25
dba25 24399 8843 0 10:49:25 ? 0:01 ora_dbw0_DBA25
dba25 22687 22684 0 08:53:12 pts/22 0:00 -ksh
dba25 24411 8843 0 10:49:25 ? 0:00 ora_arc1_DBA25
dba25 26008 8843 0 13:42:55 ? 0:00 /oracle/dba/dba25/bin/tnslsnr abc25 -inherit
dba25 26047 22687 0 13:44:08 pts/22 0:00 ps -ef
3. DB 기동 중지후 리스너 동작 확인
dba25@sun02-zone:/oracle/dba/dba25/network/admin] sqlplus '/as sysdba'
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Nov 7 13:47:12 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> shutdown abort;
ORACLE instance shut down.
SQL> !ps -ef | grep -i dba25
dba25 26180 26179 0 13:47:12 ? 0:00 oracleDBA25 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
dba25 24771 24747 0 11:07:30 pts/4 0:00 -ksh
dba25 22687 22684 0 08:53:12 pts/22 0:00 -ksh
dba25 26195 26179 0 13:47:23 pts/22 0:00 grep -i dba25
dba25 26196 26195 0 13:47:23 pts/22 0:00 ps -ef
dba25 26008 8843 0 13:42:55 ? 0:00 /oracle/dba/dba25/bin/tnslsnr abc25 -inherit
dba25 26179 22687 0 13:47:12 pts/22 0:00 sqlplus /as sysdbaSQL> startup
ORACLE instance started.
Total System Global Area 60379276 bytes
Fixed Size 454796 bytes
Variable Size 54525952 bytes
Database Buffers 4194304 bytes
Redo Buffers 1204224 bytes
Database mounted.
Database opened.
클라이언트 설정
클라이언트가 서버에 접속하기 위해서는 아래와 같은 내용이 지정되어 있어야한다.
a) 프로토콜 설정
b) 서버 주소 설정
c) 포트 번호 설정
d) DB 이름 설정
tnsnames.ora
--------------------------------------------------------------------------------
TEST=(a, b, c, d information)
실습내용 그림파일
--------------------------------------------------------------------------------
practice_flow.zip
dba25@sun02-zone:/oracle/dba/dba25/network/admin] lsnrctl serv abc25
LSNRCTL for Solaris: Version 9.2.0.1.0 - Production on 07-NOV-2008 14:22:32
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=70.12.200.152)(PORT=2225)))
Services Summary...
Service "DBA25" has 1 instance(s).
Instance "DBA25", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:7 refused:0
LOCAL SERVER
The command completed successfully
dba25@sun02-zone:/oracle/dba/dba25/network/admin] lsnrctl stop abc25
LSNRCTL for Solaris: Version 9.2.0.1.0 - Production on 07-NOV-2008 14:24:54
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=70.12.200.152)(PORT=2225)))
The command completed successfully
윈도우 시스템
Microsoft Windows 2000 [Version 5.00.2195]
(C) Copyright 1985-2000 Microsoft Corp.
C:\Documents and Settings\student>sqlplus scott/tiger@test
SQL*Plus: Release 9.2.0.1.0 - Production on 금 Nov 7 14:13:48 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
다음에 접속됨:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> select count(*) from scott.dept;
COUNT(*)
----------
128
SQL>
리스너를 중지시키더라도 중지되기 이전에 존재했던 커넥션은 리스너의 동작과는 무관하게 동작한다.
새로 들어오는 연결은 커넥션이 맺어지지 않는다.
C:\oracle\ora92\network\ADMIN>type sqlnet.ora
SQLNET.ORA Network Configuration File: C:\oracle\ora92\NETWORK\ADMIN\sqlnet.ora
Generated by Oracle configuration tools.
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (HOSTNAME, TNSNAMES)
아무리 리스너를 등록하더라도 호스트 이름을 찾는 디렉토리 설정이 되지 않으면 해당 호스트를 찾을 수 없다.
C:\oracle\ora92\network\ADMIN>edit c:\winnt\system32\drivers\etc\hosts
C:\oracle\ora92\network\ADMIN>sqlplus scott/tiger@db1
SQL*Plus: Release 9.2.0.1.0 - Production on 금 Nov 7 14:35:08 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
다음에 접속됨:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> select * from test
2 ;
ID NAME
---------- ------------------------------
1 aaa
2 bbb
3 ccc
SQL> select name from v$database;
NAME
---------
TESTDB호스트 이름을 기반으로 접속을 실시할 경우 해당 호스트에 리스너가 한개만 존재할 경우에만 가능하다.
SQL> select name from v$datafile;
NAME
---------
TESTDB
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
C:\ORACLE\ORA92\TESTDB\SYSTEM01.DBF
C:\ORACLE\ORA92\TESTDB\UNDOTBS01.DBF
C:\ORACLE\ORA92\TESTDB\DRSYS01.DBF
C:\ORACLE\ORA92\TESTDB\INDX01.DBF
C:\ORACLE\ORA92\TESTDB\TOOLS01.DBF
C:\ORACLE\ORA92\TESTDB\USERS01.DBF
C:\ORACLE\ORA92\TESTDB\XDB01.DBF
7 개의 행이 선택되었습니다.
클라이언트측
sqlnet.ora
names.directory_path=(hostname, tnsnames, onames ...)
tnsnames.ora (tnsnames 설정시)
TEST=(프로토콜, 서버주소, 포트, DB이름)
서버측
listener.ora
리스너이름=(주소)
SID_LIST_리스너이름=(DB정보)
※ 리스너까지 기동되어야 클라이언트와 연결 가능
오라클 넷으이 접속 방식
호스트 이름 지정
호스트 이름 지정 방식은 리스너의 포트가 1521 포트로 열린 상태여야 한다.
로컬 이름 지정 (TNSNAME.ORA)
주로 사용하는 방식
디렉토리 이름 지정 (LDAP)
오라클 네임즈 서버 이용 (ONAMES)
외부 이름 지정 방식
원격에서 관리자 모드로 접속하기
(Client Windows)
C:\oracle\ora92\network\ADMIN>sqlplus scott/tiger@test
SQL*Plus: Release 9.2.0.1.0 - Production on 금 Nov 7 15:08:28 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
다음에 접속됨:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> shutdown abort;
ORA-01031: insufficient privileges
SQL> exit
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production에서 분리되었습니다.
원격에서 관리자로 접속하기 위해서는 서버측에 관리자 접속용 패스워드 파일을 생성해야한다.
(orapwd 를 이용해서 작성, $ORACLE_HOME/dbs/ 에 위치, orapwSID 라는 이름으로 지정)
초기 패스워드 파일에는 SYS의 패스워드를 기본적으로 만들게 된다.
설사 파일을 만들었더라도 파라미터 파일에서 설정을 하지 않으면 사용하지 않는다.
remote_login_passwordfile=exclusive
(<>remote_login_passwordfile=none)
(Oracle Server)
1. 패스워드 파일 생성
dba25@sun02-zone:/oracle/dba/dba25] cd $ORACLE_HOME/dbs
dba25@sun02-zone:/oracle/dba/dba25/dbs] orapwd file=orapwDBA25 password=a1234
dba25@sun02-zone:/oracle/dba/dba25/dbs] ls -al orapwDBA25* -rwSr----- 1 dba25 dba 1536 Nov 7 15:32 orapwDBA25
dba25@sun02-zone:/oracle/dba/dba25/dbs] vi orapwDBA25 "orapwDBA25" [Incomplete last line] 1 line, 1536 characters (1453 null)
^B^BZ[\]ORACLE Remote Password file^[INTERNAL^HC69ECBDE2A2941D2^P^OSYS^C5FA9749932B55F87^P^O
~
...
2. 파라미터 설정하기
dba25@sun02-zone:/oracle/dba/dba25/dbs] vi init*.ora
Date: 28 Feb 2001
Updated: 24 Apr 2001
File Name: initsid.ora
File Version: 1.1.3
background_dump_dest=$ORACLE_HOME/ADMIN/BDUMP
compatible=9.2.0
control_files=($ORACLE_HOME/dbs/ctrl01.ctl, $ORACLE_HOME/dbs/ctrl02.ctl)
core_dump_dest=$ORACLE_HOME/ADMIN/CDUMP
db_block_size=4096
db_cache_size=4M
db_domain=world
db_name=DBA25
global_names=TRUE
instance_name=DBA25
java_pool_size=0
log_archive_dest_1="LOCATION=$ORACLE_HOME/ORADATA/ARCHIVE1/ MANDATORY"
log_archive_dest_2="LOCATION=$ORACLE_HOME/ORADATA/ARCHIVE2/ OPTIONAL"
log_archive_format=arch_%s.arc
#log_archive_max_processes=2
log_archive_start=true
max_dump_file_size=10240
remote_login_passwordfile=exclusive
service_names=DBA25
shared_pool_size=40M
undo_management=AUTO
undo_tablespace=UNDOTBS
user_dump_dest=$ORACLE_HOME/ADMIN/UDUMP
~
3. 데이터 베이스 재 기동
dba25@sun02-zone:/oracle/dba/dba25/dbs] sqlplus '/as sysdba'
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Nov 7 15:36:08 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> startup force
ORACLE instance started.
Total System Global Area 60379276 bytes
Fixed Size 454796 bytes
Variable Size 54525952 bytes
Database Buffers 4194304 bytes
Redo Buffers 1204224 bytes
Database mounted.
Database opened.
에러가 발생했다면 패스워드 파일의 이름을 확인해본다.
SQL> show parameter remote
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_archive_enable string true
remote_dependencies_mode string TIMESTAMP
remote_listener string
remote_login_passwordfile string EXCLUSIVE
remote_os_authent boolean FALSE
remote_os_roles boolean FALSE
(Client Windows)
C:\oracle\ora92\network\ADMIN>sqlplus "sys/a1234@test as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on 금 Nov 7 15:27:48 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
다음에 접속됨:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> startupORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown abort;
ORACLE 인스턴스가 종료되었습니다.
SQL> startup
ORACLE 인스턴스가 시작되었습니다.
Total System Global Area 60379276 bytes
Fixed Size 454796 bytes
Variable Size 54525952 bytes
Database Buffers 4194304 bytes
Redo Buffers 1204224 bytes
데이터베이스가 마운트되었습니다.
데이터베이스가 열렸습니다.
SQL>
공유 서버 설정
임시로 동시 유저가 늘어나는 경우에 사용하는 방식.
여기서 동시 유저라는 것은 말그대로 순간수간의 유저수를 말한다.
전용서버 구성 : 하나의 서버 프로세스가 하나의 사용자 프로세스만 처리
공유서버 구성 : 하나의 서버가 여러 사용자 프로세스를 처리
공유 서버의 장점
일정 개수의 공유 서버 프로세스가 번갈아 가면서 다수의 사용자 프로세스 요구를 처리하기 때문에 인스턴스당 프로세스 개수 감소
다수의 사용자 프로세스가 접속해도 서버 프로세스는 일정 개수만 생성 (리소스 사용양 감소)
허용 가능한 사용자 수 증가
로드 밸런싱 수행
메모리 사용량과 시스템 오버헤드 감소
※ 오라클에서는 불가피한 상황에서만 사용하는 것을 권장한다.
[SGA]
sharedpool = reqeust queue + result queue
shared server process, dispatcher process
--------------------------------------------------------------------------------
사용자 쿼리 처리 과정 260page
사용자 접속 후 쿼리 전송 -> 디스패처 프로세스 -> sharedpool.requestqueue 에 저장 ->
shared server process 에서 쿼리 처리 ->sharedpool.resultqueue에 결과 저장
-> 디스패처 프로세서 결과 로드 -> 사용자에게 결과 전송
※ 공유 서버 프로세스, 디스패처 프로세스의 생성수를 줄여서 메모리 사용양을 줄여준다는 장점이 존재하지만 서버의 reponse time 이 증가한다는 단점이 존재
실습파일
3일차.zip
소셜웹 반응글
접기▲
소셜웹 더보기▼
