오라클 기초
SQL> select instance_name
2 from v$instance;
INSTANCE_NAME
----------------
wdba172
SQL> select name, open_mode
2 from v$database;
NAME OPEN_MODE
--------- ----------
WDBA172 READ WRITE
OS 부팅시 오라클 자동실행 or 수동실행
관리도구 → 서비스 → OracleServerSID → (자동,수동,사용안함)
Oracle 설치된 홈경로 확인
SQL> host
Microsoft Windows 2000 [Version 5.00.2195]
(C) Copyright 1985-2000 Microsoft Corp.
C:\doc-uments and Settings\Administrator>echo $oracle_home
$oracle_home
-- windows 기반 오라클의 orahome 정보는 레지스트리 편집기에서 볼수있다.
STARTUP & SHUTDOWN
SQL> shutdown immediate
데이터베이스가 닫혔습니다.
데이터베이스가 마운트 해제되었습니다.
ORACLE 인스턴스가 종료되었습니다.
SQL> startup nomount -- spfile을 읽어 Oracle Instance를 구성하는 단계
ORACLE 인스턴스가 시작되었습니다.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL> select instance_name, startup_time, status -- 인스턴스 시작 시간, 상태를 보자
2 from v$instance;
INSTANCE_NAME STARTUP_ STATUS
-------------------------------- -------- ------------------------
wdba172 05/07/14 STARTED
SQL> alter database mount; --control 파일을 읽어서 database의 구조정보를 server에 올리는 단계
데이타베이스가 변경되었습니다.
SQL> select instance_name, startup_time, status
2 from v$instance;
INSTANCE_NAME STARTUP_ STATUS
---------------- -------- ------------------------
wdba172 05/07/14 MOUNTED
SQL> alter database open; -- smon 동작하여 automatic instance recovery 작업 수행
데이타베이스가 변경되었습니다.
SQL> select instance_name, startup_time, status
2 from v$instance;
INSTANCE_NAME STARTUP_ STATUS
---------------- -------- ------------
wdba172 05/07/14 OPEN
SQL> select * from v$pwfile_users; -- startup, shutdown 할수있는 사용자보기
USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
Shutdown 옵션
1. shutdown normal
: oracle server에 접속한 사용자가 한명이라도 있으면 shutdown명령이 대기상태에 빠졌다가,
접속한 사용자들이 모두 빠져나간 뒤에 자동으로 shutdown된다.
2. shutdown transactional
: 연결되어진 사용자와 상관없이 rock 걸려진 트랜잭션이 없어야한다.
scott...
SQL> update dept set loc='SONGPA' -- commit을 안해서 rock이 걸려진 상태
2 where deptno=40;
sys...
SQL> shutdown transactional;
scott...
SQL> rollback;
롤백이 완료되었습니다.
sys...
SQL> shutdown transactional;
데이터베이스가 닫혔습니다.
데이터베이스가 마운트 해제되었습니다.
ORACLE 인스턴스가 종료되었습니다.
3. shutdown immediate
: 연결되어진 사용자를 끊고, 진행중인 트랜잭션을 모두 rollback 시키고 강제로 shutdown 시킨다 ( PMON )
scott...
SQL> update dept set loc='SONGPA'
2 where deptno=40;
1 행이 갱신되었습니다.
sys...
SQL> shutdown immediate
데이터베이스가 닫혔습니다.
데이터베이스가 마운트 해제되었습니다.
ORACLE 인스턴스가 종료되었습니다.
4. shutdown abort
: 정전과 같은 비정상종료로서 CKPT 가 발생하지 않는다
: 다른 shutdown 명령이 안될때 강제로 사용한다.
scott...
SQL> update dept set loc='SONGPA'
2 where deptno=40;
1 행이 갱신되었습니다.
sys...
SQL> shutdown abort
ORACLE 인스턴스가 종료되었습니다.
SQL> startup
ORACLE 인스턴스가 시작되었습니다. -- smon 동작하여 automatic instance recovery 작업 수행
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Data file , Temp file 정보를 보기위한 뷰
select status, name, enabled, bytes
from v$datafile;
select * from v$datafile; --datfile tablespace
select * from dba_data_files; -- 정상적으로 데이터베이스가 open되어야 볼수있다
select * from v$tempfile; --temporary file tablespace
select * from dba_temp_files;
select * from v$instance;
※ v$~~ : 동적성능 뷰 (데이터베이스가 open 되지 않고 mount만 된 상태에도 보여진다)
※ 오라클 8i까지는 tempfile, datafile 정보가 분리되어있지 않다.
Data file 정보 확인
select * from v$datafile;
select * from v$tablespace;
위 둘을 조인시켜 데이터 파일명과 테이블스페이스명을 알아낸다.
(DB 오픈이 안되어지는 상태에서 복구시 사용하게 된다.)
select T.name, D.name
from
(select ts#, name
from v$tablespace) T,
(select ts#, name
from v$datafile) D
where T.TS# = D.TS#
select *
from dict_columns
where table_name='DBA_DATA_FILES';
Log file 정보 확인
select * from v$logfile;
select * from v$log;
SQL> archive log list
데이터베이스 로그 모드 아카이브 모드가 아님
자동 아카이브 사용 안함
아카이브 대상 C:\oracle\ora92\RDBMS
가장 오래된 온라인 로그 순서 39
현재 로그 순서 41
SQL> alter system switch logfile; -- 강제로 log switch 발생 -- CKPT발생
시스템이 변경되었습니다.
SQL> archive log list
데이터베이스 로그 모드 아카이브 모드가 아님
자동 아카이브 사용 안함
아카이브 대상 C:\oracle\ora92\RDBMS
가장 오래된 온라인 로그 순서 40
현재 로그 순서 42
select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
1 1 41 104857600 1 NO ACTIVE 3108311 2005-07-14 10:28:42
2 1 42 104857600 1 NO CURRENT 3118879 2005-07-14 11:29:40 ↘
3 1 40 104857600 1 NO INACTIVE 3077848 2005-07-14 09:18:28 아카이브 작업중임을 나타낸다~!
※ select * from v$thread; -- log file의 그외 정보를 볼수있다.
Control file 정보 확인
select * from v$controlfile; -- control file 정보
select value -- controlfile 백업시 스크립트가 생성되는 경로
from v$parameter
where name='user_dump_dest'
SQL>show parameter user_dump_dest; -- controlfile 백업시 스크립트가 생성되는 경로
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string C:\oracle\admin\wdba172\udump
alter database backup controlfile to trace; -- controlfile 을 백업 ( 스크립트 파일)
Oracle Instance 정보 확인
SQL> show sga
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
select * from v$sga;
NAME VALUE
Fixed Size 453492
Variable Size 109051904
Database Buffers 25165824
Redo Buffers 667648
select * from v$sgastat;
select * from v$bgprocess --현재 활성화되어진 백그라운드 프로세스
where paddr !='00';
select * from v$process
restrict startup
; 오라클 서버를 제한적인 모드로 변경하여 사용자의 접속을 제한한다
; 유지보수
SQL> startup open restrict -- 제한적인 시작 ( 사용자의 접근을 막으면서 시작....유지보수시 사용)
ORACLE 인스턴스가 시작되었습니다.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
데이터베이스가 마운트되었습니다.
데이터베이스가 열렸습니다.
SQL> grant restricted session to scott; -- scott만 접속할수 있도록 권한 부여
권한이 부여되었습니다.
SQL> revoke restricted session from scott;
권한이 취소되었습니다.
SQL> alter system disable restricted session; -- 제한된 시작을 해제하겠다. (사용자 모두 접속가능상태로)
시스템이 변경되었습니다.
(오라클 시작후 제한모드로 가보자)
SQL> alter system enable restricted session; -- db 시작후... 제한된 상태로 바꾸겠다
시스템이 변경되었습니다.
→ 이러한 상황에서 이미 접속되어 있는 사용자는 현세션까지만 사용이 가능하고, 재접속시에는 접속이 제한된다.
(접속한 사용자의 세션을 강제로 끊고, 제한 모드로 가보자...)
SQL> select SID,SERIAL#,USER#,USERNAME,STATUS -- 현제 접속한 세션 정보
from v$session
where serial# <> 1 -- 내부 세션은 뺀다
SID SERIAL# USER# USERNAME STATUS
9 3 0 SYS INACTIVE
10 10 59 SCOTT INACTIVE
12 31 0 SYS ACTIVE
alter system kill session '10,10'; -- 접속을 끊을 유저 sid,serial#
Control 파일의 복구
select value
from v$parameter
where name='control_files'; -- control 파일 정보 보기< /FONT>
alter system set control_files= -- control 파일 추가
"C:\oracle\oradata\wdba172\CONTROL01.CTL",
"C:\oracle\oradata\wdba172\CONTROL02.CTL",
"C:\oracle\oradata\wdba172\CONTROL03.CTL",
"c:\ctlfile\control04.ctl" scope=spfile; -- spfile로 설정 (껏다 켰을때 적용)< /FONT>
SQL> shutdown immediate
데이터베이스가 닫혔습니다.
데이터베이스가 마운트 해제되었습니다.
ORACLE 인스턴스가 종료되었습니다.
C:\oracle\oradata\wdba172\CONTROL01.CTL 파일을
C:\ctlfile\CONTROL04.CTL 로 복사를 한다
SQL> startup
ORACLE 인스턴스가 시작되었습니다.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
데이터베이스가 마운트되었습니다.
데이터베이스가 열렸습니다.
※ alter system set 등으로 변경되면 백업해두었던 pfile 등을 편집해주거나 재생성 해주어야한다
( 그래야만 spfile 손상시 복구가 가능하다)
SQL> create pfile= 'c:\parafile\initdba172.ora' from spfile;
파일이 생성되었습니다.
(control 파일 삭제)
SQL> shutdown immediate
데이터베이스가 닫혔습니다.
데이터베이스가 마운트 해제되었습니다.
ORACLE 인스턴스가 종료되었습니다.
C:\oracle\oradata\wdba172\CONTROL03.CTL 파일을 삭제
(복구)
SQL> startup
ORACLE 인스턴스가 시작되었습니다.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
ORA-00205: ????? ????? ??, ?? ??? ? ?? ??? ?? ??? ??????
SQL> show parameter control_files; -- 여기서 조회된 파일이 OS에 존재하는지를 조사한다.
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
control_files string
C:\oracle\oradata\wdba172\CONT
ROL01.CTL, C:\oracle\oradata\w
dba172\CONTROL02.CTL, C:\oracl
e\oradata\wdba172\CONTROL03.CT
L, c:\ctlfile\control04.ctl
SQL> alter system set control_files =
2 "C:\oracle\oradata\wdba172\CONTROL01.CTL",
3 "C:\oracle\oradata\wdba172\CONTROL02.CTL", -- 조사해서 불일치한 CONTROL03.CTL 을 뺀다.
4 "c:\ctlfile\control04.ctl" scope=spfile;
시스템이 변경되었습니다.
SQL> shutdown abort
ORACLE 인스턴스가 종료되었습니다.
SQL> startup
ORACLE 인스턴스가 시작되었습니다.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
데이터베이스가 마운트되었습니다.
데이터베이스가 열렸습니다.
Redo Log File의 생성 및 삭제
(실습 시나리오)
Redo log file group 3개
group 1 group 2 group 3 group 4 -- 그룹 추가.
멤버 □ □ □ □
2개 ■ ■ ■ ■
기존그룹에
멤버 추가 ( 추가된 멤버는 기존맴버와 같은 size로 생성된다)
→ 추가된 멤버에 같은 내용이 기록되어진다.
select * from v$log; -- 로그 그룹 정보
select * from v$logfile; -- 로그 멤버 정보
로그 그룹, 멤버 추가
alter database add logfile -- 그룹 추가
'C:\ORACLE\ORADATA\WDBA172\REDO04.LOG' size 20m;
alter database add logfile member --로그 멤버 추가
'c:\secondlog\redo01b.log' to group 1,
'c:\secondlog\redo02b.log' to group 2,
'c:\secondlog\redo03b.log' to group 3,
'c:\secondlog\redo04b.log' to group 4
로그 그룹 삭제 (inactive만 삭제됨)
alter database drop logfile group 2; --현재 status 가 current 인것은 삭제되어지지 않는다.
alter database drop logfile group 1; --현재 status 가 active 인것도 삭제되어지지 않는다.
(아카이브 모드라면 복구시 필요하기 때문에)
alter database drop logfile group 4; -- 속한 멤버도 함께 삭제된다.
→ 이후에 OS 상태에서도 파일을 찾아 지워주야한다 ( 9i부터는 한번의 작업으로 모두 지워질수 있다)
로그 멤버 삭제(active, inactive만 삭제됨)
alter database drop logfile member --현재 status 가 current 인것은 삭제되어지지 않는다.
'C:\SECONDLOG\REDO02B.LOG'
alter database drop logfile member --현재 status 가 active 인것은 삭제된다.
'C:\SECONDLOG\REDO01B.LOG'
alter database drop logfile member --현재 status 가 inactive 인것도 삭제된다.
'C:\SECONDLOG\REDO03B.LOG'
로그 그룹의 재생성 (그룹의 멤버가 1명일때만 가능~!)
alter database clear logfile
'C:\ORACLE\ORADATA\WDBA172\REDO01.LOG'; -- 그룹을 지웠다가 생성하는 효과......
로그 삭제시 OS 파일까지 삭제하기 (oracle 9i부터)
SQL> show parameter db_create_online_log_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string -- 그룹 추가시 최대 5개의 멤버를 만들수 있다
SQL> alter system set db_create_online_log_dest_1=
2 'c:\secondlog';
시스템이 변경되었습니다.
SQL> alter system set db_create_online_log_dest_2=
2 'c:\thirdlog';
시스템이 변경되었습니다.
SQL> show parameter db_create_online_log_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_online_log_dest_1 string c:\secondlog
db_create_online_log_dest_2 string c:\thirdlog
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
SQL> alter database add logfile; -- 그룹 추가
데이타베이스가 변경되었습니다.
→ 새로운 그룹이 추가되고 show parameter db_create_online_log_dest 의 value에 있는 경로로, 경로 갯수만큼
랜덤한 이름으로 멤버가 추가된다.
SQL> alter database drop logfile group 4;
데이타베이스가 변경되었습니다.
→ 그룹과 속한 멤버가 삭제되고, OS 파일까지 모두 삭제된다.
(원상복귀)
SQL> alter system set db_create_online_log_dest_1='';
시스템이 변경되었습니다.
SQL> alter system set db_create_online_log_dest_2='';
시스템이 변경되었습니다.
테이블 스페이스의 삭제
select tablespace_name
from dba_data_files
where file_name='C:\MARKETDATA\MARKETTBS01.DBF'
desc dba_tables;
select owner, table_name
from dba_tables
where tablespace_name='MARKETTBS';
select * from scott.marketinfo;
create index scott.idx_marketinfo_no
on scott.marketinfo(no) tablespace markettbs;
select *
from dba_segments
where tablespace_name='MARKETTBS';
테이블 스페이스 제거
drop tablespace markettbs; -- tablespace 내의 내용이 있으면 삭제가 안된다.
drop tablespace markettbs including contents; -- 내용까지 모두 삭제된다.
→ 그러나 OS의 파일은 삭제 되어지지 않는다
테이블스페이스 삭제시 OS 파일까지 삭제하기 (oracle 9i부터)
create tablespace newmarkettbs
datafile 'c:\marketdata\newmarkettbs01.dbf' size 10m
extent management local
segment space management auto;
create table scott.newmarket
(no number
,name varchar2(10)
) tablespace newmarkettbs;
insert into scott.newmarket values(1,'강남점');
commit;
select * from scott.newmarket;
drop tablespace newmarkettbs including contents and datafiles;
→ OS파일까지 삭제된다.
OMF(Oracle Management Files)
AUN 테이블 스페이스
select *
from dba_tablespaces
where contents='UNDO';
select *
from dba_data_files
where tablespace_name='UNDOTBS1'
SSISO Community