D:\EXAMDATA
폴더 생성 후, sys로 접속
CREATE TABLESPACE EXAMDATABS
DATAFILE 'd:\examdata\examdatatbs01.dbf' SIZE 4M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
SELECT *
FROM DBA_DATA_FILES;
이제 위에서 생성한 DBF 파일을 삭제 시도를 해보면 날라가지 않는 것을 확인할 수 있다.
이는 UNIX/LINUX와 달리 윈도우에서는 실행중인 파일이 지워지지 않는다.
이제 sys로 접속해서 오라클 서버를 다운 시킨 후,
위 DBF 파일을 지운고, 다시 오라클 서버를 구동시켜보면,
위와 같이 오류메세지와 함께 오라클 서버에 접속이 안되게 된다.
이를 복구하기 위해
같이 실행한다. 위 명령어는 위 DBF 파일을 지금 사용하지 않겠다(OFFLINE)는 의미와 함께
다시는 사용하지 않겠다(DROP)는 뜻으로 복구목록에서 지우는 것이다.
그리고 아래와 같이 DATABASE 를 다시 열어주게 되면,
다시 접속이 되어진다.
이제 다시
SELECT *
FROM DBA_DATA_FILES;
을 실행하여 DATA딕셔너리에서 DBF파일의 목록을 확인해보면
정보가 남아있는 것을 확인할 수 있는데
아래와 같이 DROP명령을 통해 테이블스페이스를 지워주고 나서 다시 조회 해보면 아래와 같이 깨끗이 없어진 것을 확인할 수 있다.
- SHUTDOWN (NORMAL)
오라클 서버에 일반 사용자가 붙어 있는 상태에서
SQL>SHUTDOWN NORMAL 을 실행해보면 되지 않는다. (대기상태로 빠짐)
이제 SCOTT이 아래와 같이 오라클 서버에서 세션을 종료한다.
SQL> show user USER은 "SCOTT"입니다 SQL> disconnect Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options에서 분리되었습니다. SQL> exit
C:\Documents and Settings\Administrator>exit |
위와 같이 오라클에서 접속을 종료하게 되면 시간이 꽤~ 지나게 되면 (대기상태에서) 연결되어진
사용자가 없다는 것을 확인하게 되고, shutdown이 진행되게 된다.
- SHUTDOWN TRANSACTION
을 SCOTT에서 실행한 후 , SYS에서
을 하게되면 또 대기상태에 걸린다. 이는 scott이 COMMIT이나 ROLLBACK을 하지 않은체 변경된 정보가 있기 때문이다.
즉 SCOTT계정에서 lock이 걸려 있기 떄문이다. 이 LOCK 이 다 풀려야지만 SHUTDOWN이 이루어 지게 된다.
- SHUTDOWN IMMEDIATE
그림에서와 같이 SCOTT이 자료를 변경후, COMMIT/ROLLBACK을 하지 않은 상태에서
SYS가 'SHUTDOWN IMMEDIATE'를 실행하면 TCL과 관계없이 무조건 꺼진다.
추후, 다시 STARTUP 된 이 후, COMMIT 하지 않고 종료된 정보를 확인해보면
그림과 같이 변경되지 않은 것을 확인할 수 있다.
즉, SHUTDOWN IMMEDIATE는 변경된 내용들에 강제로 'ROLLBACK'을 하고 종료시킨다.
- SHUTDOWN ABORT
SQL> shutdown immediate 데이터베이스가 닫혔습니다. 데이터베이스가 마운트 해제되었습니다. ORACLE 인스턴스가 종료되었습니다. | SQL> shutdown abort ORACLE 인스턴스가 종료되었습니다. |
위와 같이 정상종료(좌측)와 달리 ABORT는 비정상적인 종료로 출력되어지는 메시지가 다른 것을 확인할 수 있다.
메모리만 종료됨.
- STARTUP의 단계
SQL> startup --1단계 : NOMOUNT 단계 (PARAMETER 파일을 읽어다가 오라클 서버 메모리 구성에 해당하는 INSTANCE를 구동시켜주는 단계.) ORACLE 인스턴스가 시작되었습니다.
Total System Global Area 171966464 bytes --ORACLE.EXE 프로세스가 잡아먹고 있는 용량 Fixed Size 787988 bytes Variable Size 145750508 bytes Database Buffers 25165824 bytes Redo Buffers 262144 bytes
--2단계 : MOUNT 단계 (control file을 읽어다가 Database 를 마운트시켜 주는 단계.) 데이터베이스가 마운트되었습니다.
--3단계 : Open 단계 (마운트되어진 DB를 실제로 열어 들이는 단계.) 데이터베이스가 열렸습니다. SQL> |
- Startup nomount
오라클을 nomount 단계로 시작하게되면, DB에서 정보를 읽어 올 수 없다.
오라클 사이트에서 해당 오류에 대한 정보를 조회해 보면 아래와 같다.
이제 아래와 같은 명령으로 상태를 확인해보면 started( = no mounted) 단계임을 확인할 수 있다.
- Startup mount
- 위와 같이 Startup 단계들을 나누어 놓은 것은 xNIX 시스템들에서와 같이 복구를 위해 사용된다.
#Parameter 파일의 사용처와 이용방법
Parameter File ?!
- C:\oracle\product\10.1.0\db_1\database에 접두어 'SPFILE' + SID 로 이루어진 파일 [SPFILEORACLE10.ORA]로
instance를구성하는 환경설정값이 들어있는 파일.
|
|
SQL >STARTUP
( NOMOUNT -> MOUNT -> OPEN )
=>NOMOUNT 단계에서 기본적으로 'SPFILE'을 LOAD하여 수행한다.
현재 상태에서의 PARAMETER FILE의 정보를 조회하려면,
SELECT * FROM V$PARAMETER; | <-TOAD SQLPLUS_COMMAND-> | SHOW PARAMETER; |
너무 많은 컬럼이 나오므로 필요한 정보만 출력해서 다시
select name, VALUE, description
from v$parameter
보도록 한다.
위 컬럼들에 대한 설명은
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2012.htm#REFRN30176
에서 살펴볼 수 있다.
Select * from Nls_session_parameters; | 현 세션이 연결되어있는 동안 변경됨 모든 사용자가 변경가능. Alter session 명령을 사용 |
Select * from Nls_instance_parameters; | 오라클이 shutdown 되기 전까지 변경됨. As sysdba 권한 필요 Alter system 명령을 사용 |
Select * from Nls_database_parameters; | 오라클이 shutdown되고 재구동되더라도 변경사항이 유지됨. As sysdba 권한 필요 Alter system 명령을 사용 |
실습1>
Parameter 변경하기
ALTER SESSION 변경 | ||||
SQL> select sysdate from dual;
SYSDATE -------- 09/04/15
===================================================
USER은 "SCOTT"입니다 SQL> desc nls_session_parameters 이름 널? 유형 ----------------------------------------- -------- ----------------------------
PARAMETER VARCHAR2(30) VALUE VARCHAR2(40)
SQL> select value 2 from nls_session_parameters 3 where parameter='NLS_DATE_FORMAT';
VALUE --------------------------------------------------------------------------------
RR/MM/DD
===================================================
SQL> alter session set nls_date_format ='yyyy-mm-dd hh24:mi:ss'; X 세션이 변경되었습니다.
| ||||
` ALTER SYSTEM 변경(SYS권한에서 실행) | ||||
select * from v$parameter where name='db_cache_size'; 를 통해 'db_cache_size' 를 변경하여 본다.
=========================================================
SQL> alter system set db_cache_size=50331648; alter system set db_cache_size=50331648 --50331648=1024*1024*48=48M * 1행에 오류: ORA-02097: 지정된 값이 부적당해서 매개변수를 수정할 수 없습니다 ORA-00384: 캐시를 늘리기 위한 메모리 부족 ========================================================= 위와 같이 오류가 난 것은 SGA_max_size를 늘려야한다. select * from v$parameter where name = 'sga_max_size';
이제 오라클을 재구동(shutdown Immediate)하게 되면 용량이 변경되어 진다.
이제 Db_cache_size를 변경(이번에는 Memory scope) SQL> alter system set db_cache_size=50331648 scope=memory;
위 변경사항은 memory scope 방식이므로 오라클을 재구동하게 되면 아래와 같이 기존 값으로 변경된다.
|
실습2 >
DISK 관련 |
select * from v$parameter where value like '%\%';
|
실습3
테이블스페이스 제거를 통한 (PFFILE à PFILE로 로딩 및 변환) |
--실습
d:\oracledata 폴더 생성
alter system set db_create_file_dest='d:\oracledata' scope=both;
select * from v$parameter where name ='db_create_file_dest';
create tablespace mytbs;
select * from dba_data_files where tablespace_name='MYTBS';
--D:\oracledata\ORACLE10\DATAFILE\O1_MF_MYTBS_4YBM090L_.DBF생성됨
--위에서 보듯이 테이블스페이스를 생성할 때 위와 같이 아무 조건도 주지 않으면 기본적으로 --d:\oracledata에 생성된다. 이때 생성기본용량은 100M이다
drop tablespace MYTBS;
--이제 d:\oracledata 폴더를 삭제해본다.
select * from v$parameter where name ='db_create_file_dest';
--오라클은 아직 위 테이블스페이스가 존재하는 것으로 인식 --하지만 오라클을 재구동하게 되면 오류메세지와 함께 구동되지 않는다.
또한
show parameter db_create_file_dest 을 해봐도 실행되지 않는다.
이를 해결하기 위해서는 PFFILE이 아닌 PFILE Parameter로 해결한다.
1. 임의의 D:\pfiledir 을 생성한다.
2. 이제 아래와 같이 sqlplus에서 입력한다.
--- SQL> show USER USER은 "SYS"입니다 SQL> create pfile='D:\pfiledir\init_oracle10.ora' from SPFILE;
파일이 생성되었습니다. --
3. 이제 해당 dir에 가서 해당 파일을 메모장으로 읽어들인다.
/* 파일 내용
oracle10.__db_cache_size=50331648 *.background_dump_dest='C:\oracle\product\10.1.0\admin\oracle10g\bdump' *.compatible='10.1.0.2.0' *.control_files='C:\oracle\product\10.1.0\oradata\oracle10\control01.ctl', 'C:\oracle\product\10.1.0\oradata\oracle10\control02.ctl', 'C:\oracle\product\10.1.0\oradata\oracle10\control03.ctl' *.core_dump_dest='C:\oracle\product\10.1.0\admin\oracle10g\cdump' *.db_block_size=8192 *.db_cache_size=222298112
*.db_create_file_dest='d:\oracledata'
*.db_domain='iei.or.kor' *.db_file_multiblock_read_count=16 *.db_name='oracle10' *.db_recovery_file_dest='C:\oracle\product\10.1.0\flash_recovery_area' *.db_recovery_file_dest_size=2147483648 *.db_unique_name='oracle10g' *.dispatchers='(PROTOCOL=TCP) (SERVICE=oracle10XDB)' *.java_pool_size=50331648 *.job_queue_processes=10 *.large_pool_size=8388608 *.open_cursors=300 *.pga_aggregate_target=25165824 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.shared_pool_size=83886080 *.sort_area_size=65536 *.undo_management='AUTO' *.undo_tablespace='UNDOTBS1' *.user_dump_dest='C:\oracle\product\10.1.0\admin\oracle10g\udump'
*/
이제 위에서
*.db_create_file_dest='d:\oracledata' 의 내용을 변경한다.
변경 방법에서 위 값을 NULL로 주기 위해서는 해당 행을 제거하고,저장한다.
4. 이제 다시 오라클을 (Pfile을 이용하여) 재구동한다.
SQL> startup pfile='D:\pfiledir\init_oracle10.ora'
--이방법을 사용하게 되면 계속 재구동할 떄 마다 위와 같이 PFILE을 인식시켜줘야한다. --즉 그냥 startup 을 누르게 되면
-- SQL> startup ORA-01261: Parameter db_create_file_dest destination string cannot be translated
ORA-01263: Name given for file destination directory is invalid OSD-04018: 지정된 디렉토리 또는 장치를 액세스할 수 없습니다. O/S-Error: (OS 2) 지정된 파일을 찾을 수 없습니다.
-- 위와 같이 오류가 난다. 매번 이렇게 구동하기에는 매우 번거로우므로
5.C:\oracle\product\10.1.0\db_1\databas\SPFILEORACLE10.ORA 파일을 지운다.
6.Pfile을 PFFILE로 업어 씌운다 . create spfile from pfile='d:\pfiledir\init_oracle10.ora'
/* SQL> create spfile from pfile='d:\pfiledir\init_oracle10.ora' 2 ;
파일이 생성되었습니다. */ |
(#Man page에 육박하는 상세한 정보이므로 추후 살펴볼만 함.) |
User process(오라클 클라이언트에 해당하는 sqlplus,sqlgate,toad….)
;
==============================================================================================
==============================================================================================
==============================================================================================
Shared Pool(Library Cache/Data Dictionary Cache)
|
Data dictionary cache : 개체정보, 권한정보를 캐쉬해두는 공간. |
DATA BUFFER CACHE
- 위 쿼리문에있는 table 정보가 있는 tablespace를 조회하여 얻은 값(=SELECT 되어진 결과물)을 캐쉬해두는 공간.
Database(disk)
Datafile controlfile redo logfile
Oracle data dictionary
(DBA_,ALL_,user_)
->System tablespace
** 위에서 쿼리문에 대소문자를 다르게 하여 다시 HASH 함수에 들어가게 되면, 기존 쿼리와 다른 쿼리로 인식하게 된다. 이는 ASCII값으로 변환되어 넘어가면서 서로 다른 값을 가지기 때문이다. 즉 이는 중복된 쿼리문이 SHARED pool에 불필요하게 캐쉬되어짐으로써 메모리공간의 낭비를 의미하게 된다.
고로 일관적인 query문의 대소문자 사용이 필요하게 된다. 다른 방법으로는 프로시저를 사용하는 방법도 있겠다.
USER PROCESS A USER PROCESS B
| |
| |
| |
SELECT EMPNO, ENAME,SAL Select eMpNo, ENAME,SAL
FROM SCOTT.EMP FROM SCOTT.Emp
WHERE EMPNO=7839; WheRe EMPNO=7839;
--ASCII code로 변환 --ASCII code로 변환
| |
| |
| |
| |
update emp set sal=6000
where empno=7839;
(update의 경우
Select와 다르게
cache되지 않는다.)
| |
| |
| |
select sal select sal
from emp from emp
where empno=7839; where empno=7839;
(결과 6000) (결과 5000)
| |
| |
| |
=> A에서 변경후 commit을 하지 않았기 때문에 둘 사이의 결과값이 달라보임
| |
| |
| |
-------------------------------------------------------------------------
-- instance
--
--DATA BUFFER CACHE(select 되어진 결과물을 캐쉬해둠)
--<Before image | After Image>
--변경전 데이터값 변경후 데이터값
--sal->6000 sal -> 5000
--
--REDO LOG BUFFER
--(update emp set sal=6000
-- where empno=7839;)
-------------------------------------------------------------------------
=>rollback이 이루어진다면 REDO LOG BUFFER에 입력된 값과 After Image는 삭제된다.
================================================================================
=>만일 Commit이 되어진다면,
1. Back Ground Process 중에 하나인 LGWR(LoG WRiter)가
REDO LOG BUFFER에 있던 (update emp set sal=6000
where empno=7839;)을
REDO LOGFILE에 (update emp set sal=6000
where empno=7839;)을 입력한다. 이때, CKPK Process에 의해 SCN(System Change Number)도 함께 기록된다.
그리고 Control FIle에 CKPK가 SCN을 기록한다.
2. 위 작업이 완료되면 DBWR(DataBase WRiter)가 LGWR이 REDO LOGFILE에 작업한 내용을 지우고,
DATAFILE에 해당 내용을 입력한다.
3. CKPT가 SCN 을 DATA FILE에 기입한다.
4. 만약 중간에 문제가 생길시 SMON Processor가 SCN을 참조하여 Automatic Instance Recovery를 실행하여 데이터의 무결성을 보장한다.
CKPT가 SCN 넘버를 동기화 시켜주게 되면 정상적인 종료가 된다.
| |
| |
| |
select sal select sal
from emp from emp
where empno=7839; where empno=7839;
(결과 6000) (결과 6000)
| |
| |
| |
-------------------------------------------------------------------------
-- instance
--
--DATA BUFFER CACHE(select 되어진 결과물을 캐쉬해둠)
--<Before image | After Image>
--변경전 데이터값 변경후 데이터값
--sal->6000 sal -> 5000
--
--REDO LOG BUFFER
--(update emp set sal=6000
-- where empno=7839;)
-------------------------------------------------------------------------
=> 만약 commit을 하면 REDO LOGFILE에 WRITE하지 않고 바로 DATA에 WRITE를 한다면,
백업/복구는 제대로 이뤄질 수 없다.
예)
4월 10일 : emp 테이블에 행이 14개이고, empno=8930의 sal이 5000이었다
-->(Data File) 백업을 한다.
4월 11일 : emp 테이블에 2개의 행을 추가.(total : 16개) 및 empno=7839의 sal을 6000으로 변경한다.
-->그 후, (Data file)이 분실되었다. 그러므로 복구를 해야한다.
이때 마지막으로 백업을 받아두었던 4월 10일의 DATA 파일로 돌아가야하는데,
이 값은 분실되어진 데이터 파일과 다른 값인 행이 14개에 empno=8930의 sal이 5000으로 바뀌게된다.
즉, 데이터의 무결성이 훼손되게 된다.
고로, 이러한 문제가 발생되지 않으려면 REDO LOG FILE이 존재해야 한다.
=======================================
PMON (Process MONitor)
하나의 단일 계정 (SCOTT)에서 2개의 세션을 만든다.
session1)
select *
from dept;
update dept set loc='서울'
where deptno=10;
session2)
update dept set loc='수원'
where deptno=10;
==> session1에서 COMMIT/ROLLBACK을 하지 않아 SESSION2가 LOCK이 걸린다.
이때, SESSION1을 그냥 종료한다.
그럼 LOCK이 걸려있던 SESSION2가 LOCK 해제되면서 update문이 적용된다.
이 상태에서 다시 SESSION1을 열어서
select *
from dept; 을 실행하게 되면
NEWYORK 값이 나온다.(SESSION2에서 commit을 하지 않았기 떄문)
이제 SESSION2에서 COMMIT을 실행한다.
그리고 SESSION1에서 다시 정보를 조회하면 '수원'으로 출력된다.
이는 SESSION2에서 COMMIT을 했기 때문이다.
위와 같은 결과값이 나오는 것은 PMON이 쭈욱 지켜보고 있다가 그냥 종료된 것은 ROLLBACK
COMMIT 된것은 제대로 된것이므로 ~* & LOCK의 설정 및 해제도 PMON이 한다.
==============================================================================================
로그 스위치 발생시키기 |
select *
from v$logfile;
select *
from v$log;
현재 위와 같고 강제로 로그 스위치를 시켜보자.
------------------------------------------------
alter system switch logfile;
------------------------------------------------
current 상태인 1번 그룹의 REDO01.LOG 파일이 현재 쓰이고 있다. 이 로그 파일이 꽉 차게 되면 다른 그룹으로 이동하는데,
이때 SEQ가 변수이다. 현 2번그룹이 81번이므로 다음은 SEQ81 인 2번 그룹이 CURRENT된다.
select *
from v$log;
위에서 살펴보듯이 로그 스위치를 하게 되면 상태(Status)가 변화하는 것을 볼 수 있다.
일반적인 기본값은 한 로그파일이 다 쓰이면 다음 로그 파일을 쓰고, 또 다른 파일에 쓰고하는데…
위의 3개 파일이 로테이션을 돌면서 쓰인다. 만약 입출입이 많아 덮어쓰기가 되는 상황이 오게 되면
복구시 문제가 생기는데 이러한 문제를 방지하기 위해 '아카이브로그파일'모드를 사용한다.
기본값 |
SQL> archive LOG list 데이터베이스 로그 모드 아카이브 모드가 아님 자동 아카이브 사용 안함 아카이브 대상 USE_DB_RECOVERY_FILE_DEST 가장 오래된 온라인 로그 순서 82 현재 로그 순서 84 |
DATAFILE복구하기
우선 Startup mount로 접속을 하고,
select * from v$datafile;
select * from v$tempfile;
로 복구할 정보를 확인
'V$' => 동적 성능 뷰 (Dynamic Performance View)
Control File 관련 정보 조회
select * from v$controlfile;
'Database' 카테고리의 다른 글
| REDO LOG 파일의 관리 (0) | 2009.04.17 |
|---|---|
| Undo TableSpace 관리(생성/제거) (0) | 2009.04.16 |
| Control File의 추가 및 삭제 그리고 복구 (0) | 2009.04.16 |
| 일반 USER가 임의적으로 LOCK을 발생시키고, 관리자가 LOCK을 찾아내는 법 (0) | 2009.04.16 |
| Oracle Structure 셋째날(Undo Table) (0) | 2009.04.16 |
| 총괄적 PL/SQL 실습-도서대여점 (0) | 2009.04.13 |
| 04월10일 이론 및 실습 Fuction-실습문제 (0) | 2009.04.13 |
| 04월10일 이론 및 실습 Trigger (0) | 2009.04.13 |
| 04월09일 PL_SQL 실습 및 이론 (2) (1) | 2009.04.09 |
| 04월09일 PL/SQL 실습 및 이론 (1) (0) | 2009.04.09 |
