Admin I - II
Tablesapce create&management
[Create Syntax]
CREATE TABLESPACE 테이블스페이스이름
DATAFILE '파일 이름1‘ SIZE interge [M/K]
DATAFILE '파일 이름2‘ SIZE interge [M/K]
...... ⇒ [MAXDATAFILES]에 지정된 수만큼 지정 가능!
[MINIMUM EXTENT integer [M/K]]
[BLOCKSIZE integer [K]] ⇒ 2,4,8,16,32K
[DEFAULT STORAGE(
INITIAL integer [M/K]
NEXT integer [M/K]
MAXEXTENTS integer
PCTINCREASE integer)]
[ONLINE SQL> conn system/manager00 SQL> create user user01 identified by oracle 2 default tablespace users; SQL> create user user02 identified by oracle 2 default tablespace users; SQL> grant connect,resource to user01,user02; SQL> conn user01/oracle SQL> @demobld.sql >>> 첨부파일 사용. $ sqlplus "/as sysdba" SQL> analyze table user01.emp compute statistics; SQL> analyze table user01.dept compute statistics; SQL> grant select on user01.emp to user02; SQL> grant select on user01.dept to user02; SQL> conn system/manager00 -- user01 사용자의 emp table의 sal, mgr 컬럼을 조회할 때 auditing 정보를 생성 --조건) policy_name=명_emp_sal_mgr, deptno = 10,20 일때 감사 생성 감사정보 저장용 테이블 생성 -- system 관리자의 audit_trail_emp 테이블에 감사시에 정보가 insert 되도록 ins_emp_trail 이라는 프로시져를 작성하고 auditing시 사용하시오. SQL> execute dbms_fga.add_policy (object_schema=>'USER01', - > OBJECT_NAME=>'emp',policy_name=>'aud_emp_sal_mgr', - > audit_condition=>'deptno in(10,20)', audit_column=>'sal,mgr', - > handler_schema=>'system', handler_module=>'INS_EMP_TRAIL', - > enable=>TRUE); PL/SQL 처리가 정상적으로 완료되었습니다. SQL> create table aud_emp_trail 2 (object_schema varchar2(80) 3 ,object_name varchar2(80) 4 ,policy_name varchar2(80) 5 ); 테이블이 생성되었습니다. SQL> create or replace procedure system.INS_EMP_TRAIL ( 2 p_object_schema varchar2, 3 p_object_name varchar2, 4 p_policy_name varchar2) 5 AS 6 begin 7 insert into system.aud_emp_trail 8 values(p_object_schema,p_object_name,p_policy_name); 9 end; 10 / 프로시저가 생성되었습니다. SQL> conn user02/oracle 연결되었습니다. SQL> select * from user01.emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ------ --------- ------ -------- ------ ----- ---------- 7369 SMITH CLERK 7902 80/12/17 800 20 7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 7521 WARD SALESMAN 7698 81/02/22 1250 500 30 7566 JONES MANAGER 7839 81/04/02 2975 20 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30 7698 BLAKE MANAGER 7839 81/05/01 2850 30 7782 CLARK MANAGER 7839 81/06/09 2450 10 7788 SCOTT ANALYST 7566 82/12/09 3000 20 7839 KING PRESIDENT 81/11/17 5000 10 7844 TURNER SALESMAN 7698 81/09/08 1500 0 30 7876 ADAMS CLERK 7788 83/01/12 1100 20 7900 JAMES CLERK 7698 81/12/03 950 30 7902 FORD ANALYST 7566 81/12/03 3000 20 7934 MILLER CLERK 7782 82/01/23 1300 10 14 개의 행이 선택되었습니다. SQL> select empno, ename, sal from user01.emp; EMPNO ENAME SAL ---------- ------ ------ 7369 SMITH 800 7499 ALLEN 1600 7521 WARD 1250 7566 JONES 2975 7654 MARTIN 1250 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7839 KING 5000 7844 TURNER 1500 7876 ADAMS 1100 7900 JAMES 950 7902 FORD 3000 7934 MILLER 1300 14 개의 행이 선택되었습니다. SQL> conn system/manager00 SQL> select ename from user01.emp 2 where deptno=20; ENAME ------ SMITH JONES SCOTT ADAMS FORD SQL> select ename, sal, mgr from user01.emp where deptno=30; ENAME SAL MGR ------ ------ ------ ALLEN 1600 7698 WARD 1250 7698 MARTIN 1250 7698 BLAKE 2850 7839 TURNER 1500 7698 JAMES 950 7698 6 개의 행이 선택되었습니다. SQL> select * from user01.emp 2 where deptno in (10,20,30); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ------ --------- ------ -------- ------ ----- ---------- 7369 SMITH CLERK 7902 80/12/17 800 20 7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 7521 WARD SALESMAN 7698 81/02/22 1250 500 30 7566 JONES MANAGER 7839 81/04/02 2975 20 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30 7698 BLAKE MANAGER 7839 81/05/01 2850 30 7782 CLARK MANAGER 7839 81/06/09 2450 10 7788 SCOTT ANALYST 7566 82/12/09 3000 20 7839 KING PRESIDENT 81/11/17 5000 10 7844 TURNER SALESMAN 7698 81/09/08 1500 0 30 7876 ADAMS CLERK 7788 83/01/12 1100 20 7900 JAMES CLERK 7698 81/12/03 950 30 7902 FORD ANALYST 7566 81/12/03 3000 20 7934 MILLER CLERK 7782 82/01/23 1300 10 14 개의 행이 선택되었습니다. SQL> select to_char(timestamp,'YYMMDDHH24MI') as timestamp, 2 db_user,policy_name, sql_text 3 from dba_fga_audit_trail; TIMESTAMP DB_USER POLICY_NAME SQL_TEXT ---------- ---------- --------------- ------------------------------ 0706291834 SYSTEM AUD_EMP_SAL_MGR select * from user01.emp where deptno in (10,20,30) 0706291833 USER02 AUD_EMP_SAL_MGR select * from user01.emp 0706291833 USER02 AUD_EMP_SAL_MGR select empno, ename, sal from user01.emp SQL> select * from aud_emp_trail; OBJECT_SCHEMA OBJECT_NAME POLICY_NAME -------------------- -------------------- --------------- USER01 EMP AUD_EMP_SAL_MGR USER01 EMP AUD_EMP_SAL_MGR USER01 EMP AUD_EMP_SAL_MGR -- policy 삭제 SQL> execute dbms_fga.drop_policy (object_schema=>'USER01', - > OBJECT_NAME=>'emp',policy_name=>'aud_emp_sal_mgr'); [Oracle Database 10g FGA 예제1 – STATEMENT_TYPE, AUDIT_TRAIL] SQL> show parameter audit_trail NAME TYPE VALUE SQL> BEGIN SQL> SELECT object_schema, object_name, policy_name, policy_text, policy_column, enabled OBJECT_SCHEMA OBJECT_NAME POLICY_NAME POLICY_TEXT no rows selected SQL> conn scott/tiger SQL> var empno number; PL/SQL procedure successfully completed. SQL> insert into emp values (:empno, :ename, 'MANAGER', 7499, SYSDATE, 3100, '', 20); 1 row created. SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 16 rows selected. SQL> insert into emp values (7777, 'OCMKOREA2', 'SALESMAN', 7499, SYSDATE, 3200, 10, 30); 1 row created. SQL> delete from emp where empno = 7777; -- 정책에 포함되지 않은 구문이므로 기록되지 않음. 1 row deleted. SQL> conn /as sysdba TIMESTAMP OBJECT_NAME select * from emp insert into emp values (7777, 'OCMKOREA2', 'SALESMAN', 7499, SYSDATE, 3200, 10, 30) * 위 예제에서 보았듯이 statement_types 가 ‘SELECT’, ‘INSERT’이므로 두가지의 구문형식에 대해서만 Auditing 되며, ‘DELETE’는 Auditing되지 않았다. SQL> conn /as sysdba SQL> truncate table fga_log$; Table truncated. SQL> BEGIN SQL> BEGIN SQL> select timestamp, object_name, scn, sql_bind, sql_text no rows selected SQL> conn scott/tiger SQL> select empno, ename from emp where deptno = 20; EMPNO ENAME 6 rows selected. SQL> select empno, sal from emp where sal > 3000; EMPNO SAL SQL> select ename, job from emp where job > 'MANAGER'; ENAME JOB 6 rows selected. SQL> select empno, sal, comm from emp where deptno = 30; EMPNO SAL COMM 7 rows selected. SQL> conn /as sysdba SQL> select timestamp, object_name, scn, sql_bind, sql_text TIMESTAMP OBJECT_NAME SCN SQL_BIND SQL_TEXT SQL> -- audit_column_opts 값이 DBMS_FGA.ANY_COLUMNS으로 설정했을 경우 audit_column에 나열된 Column 중에 1개라도 포함이 되어 있다면 Audit Trail이 기록된다. 나열된 컬럼들 중에 없는 경우는 기록되지 않는다. Table truncated. SQL> BEGIN SQL> BEGIN SQL> conn scott/tiger SQL> select empno, ename from emp where deptno = 20; EMPNO ENAME 6 rows selected. SQL> select empno, sal from emp where sal > 3000; EMPNO SAL SQL> select ename, job from emp where job > 'MANAGER'; ENAME JOB 6 rows selected. SQL> select empno, sal, comm from emp where deptno = 30; -- policy에 부합되는 조회 EMPNO SAL COMM 7 rows selected. SQL> conn /as sysdba TIMESTAMP OBJECT_NAME SCN SQL_BIND SQL_TEXT SQL> -- 위와 같이 audit_column_opts 를 DBMS_FGA.ALL_COLUMN으로 설정한 경우, audit_column 에 나열한 컬럼이 모두 조회 되는 구문에서만 Audit Trail 이 기록된다. 1. 필수 초기화 parameters ( 필수로 해야 한다. ) (1) - 인스턴스가 시작될 때 생성할 server process의 수를 지정 SQL>alter system set shared_servers=6; 초기 공유 서버 프로세스 수는 낮게 산정하는 것이 가장 좋습니다. (1) - MAX_SHARED_SERVERS ORCL = >> sqlplus system/oracle@orcl은 이제부터 shared server mode로 접속 NAME STATUS SQL> select name, status from v$dispatcher; NAME STATUS SQL> select * from v$queue; PADDR TYPE QUEUED WAIT TOTALQ -- SH session SQL> select * from sales order by 1,2,3 desc; -- SYS USERNAME SID SERIAL# SERVER TYPE 관련 Dynamic Views - V$CIRCUIT : 사용자가 디스패처와 서버를 통해 데이터베이스에 연결하는 가상 회로에 대한 정보를 제공 select maximum_connections max_conn, select network Protocol, status, select decode(sum(totalq), 0, 'No Responses', sum(wait) / sum(totalq)) avg_wait_time AVG_WAIT_TIME select name, requests, busy *100/(busy + idle) "busy%", status select decode(totalq,0,'no requests', wait/totalq ||' hunderdths of seconds') "average wait time per requests" average wait time per requests select d.network, d.name disp, s.username oracle_user, s.sid, s.serial#, select rawtohex(c.circuit) "Address", Address Disp Serv SID SERIAL# STATUS QUEUE MESSAGES BYTES select sum(value) SELECT NAME, VALUE control file 다중화(2가지) ----pfile---- 1. shutdown immediate 상태에서 시작 5. startup 6. show parameter spfile 실행하면 value값이 없다 그러므로 pfile을 로드했다는걸 확인 7. create spfile from pfile pfile로 부터 spfile을 생성한다 8. shutdown immediate ---> startup 9. show parameter spfile 4번째 파일이 추가된값을 볼수있다. 10. show parameter control 역시 4번째 파일이 추가된것을 볼수있다. 11. select name from v$controlfile 확인가능 ----spfile---- SQL> select name from v$controlfile; NAME SQL> alter system set control_files='/u01/oradata/orcl/control01.ctl', 1. shutdown immediate startup NAME SQL> col member for a45 GROUP# STATUS TYPE MEMBER IS SQL> select * from V$log; --- 조회 SQL> alter database add logfile group 4 SQL> select * from V$log; GROUP# STATUS TYPE MEMBER IS_ ---2. add member (multiplex) SQL> select * from V$log; GROUP# STATUS TYPE MEMBER IS_ ----3. drop group SQL> select * from v$log; GROUP# STATUS TYPE MEMBER IS_ SQL> --4. drop member alter database drop logfile member alter database drop logfile member SQL> select * from v$log; SQL> select * from V$log; SQL> select * from v$log; SQL> select * from v$log;
FGA LABs2
Advanced Audit FGA by 10g
------------------------------------ ----------- ------------------------------
audit_trail string NONE
2 DBMS_FGA.ADD_POLICY(object_schema => 'SCOTT',
3 object_name => 'EMP',
4 policy_name => 'POL_SCOTT_EMP',
5 audit_condition => 'sal > 3000',
6 enable => TRUE,
7 statement_types => 'SELECT, INSERT',
8 audit_trail => DBMS_FGA.DB_EXTENDED);
9* END;
PL/SQL procedure successfully completed.
2 FROM dba_audit_policies;
------------------------------ ------------------------------ ------------------------------ ------------------------------
POLICY_COLUMN ENA
------------------------------ ---
SCOTT EMP POL_SCOTT_EMP sal > 3000
YES
SQL> SELECT timestamp, object_name, scn, sql_text, sql_bind
2 FROM dba_fga_audit_trail;
Connected.
SQL> var ename varchar2(10);
SQL> begin
2 :empno := 1234;
3 :ename := 'ocmkorea';
4 end;
5 /
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
8888 TESTER2 SALESMAN 7499 2008-12-26 13:41:40 3200 10 30
1234 ocmkorea MANAGER 7499 2008-12-26 13:45:36 3100 20
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
Connected.
SQL> select timestamp, object_name, scn, sql_bind, sql_text
2 from dba_fga_audit_trail;
------------------- --------------------------------------------------------------------------------------------------------------------------------
SCN
----------
SQL_BIND
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------
2008-12-26 13:45:37 EMP
0
#1(4):1234 #2(8):ocmkorea
insert into emp values (:empno, :ename, 'MANAGER', 7499, SYSDATE, 3100, '', 20)
TIMESTAMP OBJECT_NAME
------------------- --------------------------------------------------------------------------------------------------------------------------------
SCN
----------
SQL_BIND
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------
2008-12-26 13:45:41 EMP
599251
TIMESTAMP OBJECT_NAME
------------------- --------------------------------------------------------------------------------------------------------------------------------
SCN
----------
SQL_BIND
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------
2008-12-26 13:46:07 EMP
0
* Oracle Database 10g 부터 statement_types과 audit_trail이 추가되었다. Oracle 9i Database에서 SELECT 문만 auditing이 가능했지만 Oracle Database 10g 에서는
INSERT, DELETE, UPDATE, MERGE 에 대해서도 가능하다. 또한 audit_trail 파라메터에 DBMS_FGA.DB_EXTENDED로 설정하므로 init parameter 의 audit_trail 파라메터와 무관하게
FGA 가 설정만으로 수행된 구문과 바인드 정보를 볼 수 있다.
그리고 audit_trail 이 DB_EXTENEDED로 설정되어 sql_bind, 와 sql_text에 대해서도 audit trail이 작성되었다.
[Oracle Database 10g FGA 예제1 – AUDIT_COLUMN_OPTS]
Connected.
2 DBMS_FGA.DROP_POLICY(object_schema => 'SCOTT',
3 object_name => 'EMP',
4 policy_name => 'POL_SCOTT_EMP');
5* END;
PL/SQL procedure successfully completed.
2 DBMS_FGA.ADD_POLICY(object_schema => 'SCOTT',
3 object_name => 'EMP',
4 policy_name => 'POL_SCOTT_EMP1',
5 audit_column => 'empno, sal, comm',
6 enable => TRUE,
7 audit_column_opts => DBMS_FGA.ANY_COLUMNS);
8* END;
PL/SQL procedure successfully completed.
2 from dba_fga_audit_trail;
Connected.
---------- ----------
1234 ocmkorea
7369 SMITH
7566 JONES
7788 SCOTT
7876 ADAMS
7902 FORD
---------- ----------
8888 3200
1234 3100
7839 5000
---------- ---------
TESTER2 SALESMAN
ALLEN SALESMAN
WARD SALESMAN
MARTIN SALESMAN
KING PRESIDENT
TURNER SALESMAN
---------- ---------- ----------
8888 3200 10
7499 1600 300
7521 1250 500
7654 1250 1400
7698 2850
7844 1500 0
7900 950
Connected.
2 from dba_fga_audit_trail;
------------------- -------------------- ---------- -------------------- --------------------------------------------------
2008-12-26 13:56:38 EMP 599796 select empno, ename from emp where deptno = 20
2008-12-26 13:56:42 EMP 599800 select empno, sal from emp where sal > 3000
2008-12-26 13:56:50 EMP 599803 select empno, sal, comm from emp where deptno = 30
SQL> truncate table fga_log$;
2 DBMS_FGA.DROP_POLICY(object_schema => 'SCOTT',
3 object_name => 'EMP',
4 policy_name => 'POL_SCOTT_EMP1');
5* END;
PL/SQL procedure successfully completed.
2 DBMS_FGA.ADD_POLICY(object_schema => 'SCOTT',
3 object_name => 'EMP',
4 policy_name => 'POL_SCOTT_EMP2',
5 audit_column => 'empno, sal, comm',
6 enable => TRUE,
7 audit_column_opts => DBMS_FGA.ALL_COLUMNS);
8* END;
PL/SQL procedure successfully completed.
Connected.
---------- ----------
1234 ocmkorea
7369 SMITH
7566 JONES
7788 SCOTT
7876 ADAMS
7902 FORD
---------- ----------
8888 3200
1234 3100
7839 5000
---------- ---------
TESTER2 SALESMAN
ALLEN SALESMAN
WARD SALESMAN
MARTIN SALESMAN
KING PRESIDENT
TURNER SALESMAN
---------- ---------- ----------
8888 3200 10
7499 1600 300
7521 1250 500
7654 1250 1400
7698 2850
7844 1500 0
7900 950
Connected.
SQL> select timestamp, object_name, scn, sql_bind, sql_text
2 from dba_fga_audit_trail;
------------------- -------------------- ---------- -------------------- --------------------------------------------------
2008-12-26 13:59:02 EMP 599886 select empno, sal, comm from emp where deptno = 30
Shared server configure
SHARED SERVER 구성
- SHARED_SERVERS = 6
추가 공유 서버는 필요한 경우에 자동으로 시작되며 장기간 휴지 상태로 있으면
자동으로 할당이 해제됩니다
(2) - 지정된 프로토콜에 대해 처음에 시작된 디스패처 수를 지정
* DISPATCHERS
SQL>alter system set DISPATCHERS = "(PROTOCOL=TCP)(DISPATCHERS=2)\
(PROTOCOL=IPC)(DISPATCHERS=1)"
2. 선택 초기화 parameters ( 필요하면 할당한다. )
시작 가능한 최대 shared server 수를 지정
( request queue의 길이에 따라 shared server를 동적으로 할당한다. )
- MAX_SHARED_SERVERS = 10
SQL>alter system set max_shared_servers=10;
(2) - MAX_DISPATCHERS
- 동시에 실행될 수 있는 최대 디스패처 프로세스 수
- ALTER SYSTEM으로 처음 시작된 것보다 더 많은 디스패처를 추가
SQL>alter system set MAX_DISPATCHERS = 5
-------------------- 선택이라도 기본적으로 (1),(2)는 해주셔야 합니다.
(3) - CIRCUITS
- 수신 및 송신 네트워크 세션에 사용할 수 있는 가상 회로의 총 수를 지정
- 전체 SGA 크기에 영향을 줌
- CIRCUITS = 100
(4) - SHARED_SERVER_SESSIONS
- 허용되는 Oracle Shared Server 사용자 세션의 총 수를 지정
- 전용 서버에 대해 사용자 세션을 예약
- SHARED_SERVER_SESSIONS = 100
----------------------------------------------------------------------------
tnsnames.ora 파일을 수정해줍니다.
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocp.mycorpdomain.com )(PORT = 1522))
)
(CONNECT_DATA =
(SERVER = DEDICATED) -> 삭제 또는 SHARED 로 변경
(SERVICE_NAME = orcl)
)
)
----------------------------------------------------------------------------
SQL > SHUTDOWN IMMEDIATE - 종료합니다.
SQL > STARTUP - 오픈 합니다.
----------------------------------------------------------------------------
ocp@orcl : /> ps -aux | grep orcl; <- 명령어를 OS 커맨드상에서 구동해주시면
----------------------------------------------------------------------------
app/oracle/product/10.2.0/db_1/mesg/
oracle 10957 0.1 1.2 259652 12620 ? Ss 14:23 0:00 ora_pmon_orcl
oracle 10959 0.1 1.0 259064 10744 ? Ss 14:23 0:00 ora_psp0_orcl
oracle 10961 0.1 1.9 259064 20472 ? Ss 14:23 0:00 ora_mman_orcl
oracle 10963 0.1 1.3 261132 13836 ? Ss 14:23 0:00 ora_dbw0_orcl
oracle 10965 0.4 1.4 274640 15172 ? Ss 14:23 0:00 ora_lgwr_orcl
oracle 10967 0.2 1.6 259156 17516 ? Ss 14:23 0:00 ora_ckpt_orcl
oracle 10969 0.8 2.7 259660 28748 ? Ss 14:23 0:00 ora_smon_orcl
oracle 10971 0.1 1.9 259124 19876 ? Ss 14:23 0:00 ora_reco_orcl
oracle 10973 0.5 2.1 260732 22152 ? Ss 14:23 0:00 ora_cjq0_orcl
oracle 10975 1.8 4.2 261776 44460 ? Ss 14:23 0:01 ora_mmon_orcl
oracle 10977 0.1 1.2 259072 12552 ? Ss 14:23 0:00 ora_mmnl_orcl
oracle 10979 0.0 1.0 259572 10680 ? Ss 14:23 0:00 ora_d000_orcl
oracle 10981 1.0 1.1 259704 11944 ? Ss 14:23 0:01 ora_d001_orcl
oracle 10983 1.5 4.2 260300 44144 ? Ss 14:23 0:01 ora_s000_orcl
oracle 10985 3.0 5.3 260824 55864 ? Ss 14:23 0:02 ora_s001_orcl
oracle 10987 1.5 4.8 261328 50356 ? Ss 14:23 0:01 ora_s002_orcl
oracle 10989 0.7 3.8 261260 39672 ? Ss 14:23 0:00 ora_s003_orcl
oracle 10991 0.8 3.0 260264 31072 ? Ss 14:23 0:00 ora_s004_orcl
oracle 10998 0.2 1.1 259656 12228 ? Ss 14:23 0:00 ora_s005_orcl
하단에 Snnn 즉 s000~s005 부터 백그라운드 프로세스가 구동되있으면 성공입니다. ~
SQL> SELECT * FROM V$CIRCUIT로 조회해보시면 전용서버와 다르게 값이 나옵니다. ~
SQL> select name, status from v$shared_server;
---- ----------------
S000 WAIT(COMMON)
S001 WAIT(COMMON)
---- ----------------
D000 WAIT
D001 WAIT
-------- ---------- ---------- ---------- ----------
00 COMMON 0 0 0
5188D11C DISPATCHER 0 0 0
5188D4CC DISPATCHER 0 0 0
SQL> select username, sid, serial#, server, type from v$session;
------------------------------ ---------- ---------- --------- ----------
SH 134 340 SHARED USER > 작업이 없는 상태에서는 NONE 으로 됨.
SYS 138 420 DEDICATED USER
DBSNMP 140 148 DEDICATED USER
142 380 DEDICATED BACKGROUND
SYSMAN 144 15 DEDICATED USER
146 445 DEDICATED BACKGROUND
147 2 DEDICATED BACKGROUND
- V$SHARED_SERVER : shared server process에 대한 정보를 제공
- V$DISPATCHER : 디스패처 프로세스에 대한 정보를 제공
- V$SHARED_SERVER_MONITOR : shared server process를 튜닝하기 위한 정보를 제공
- V$QUEUE : request 및 response queue에 대한 정보를 제공
- V$SESSION : 현재 각 세션에 대한 세션 정보를 나열
maximum_sessions max_sess,
servers_started strt,
servers_terminated term,
servers_highwater hw
from v$shared_server_monitor;
MAX_CONN MAX_SESS STRT TERM HW
---------- ---------- ---------- ---------- ----------
335 320 10149 10149 30
sum(owned) Client,
sum(busy) * 100 / (sum(busy) + sum(idle)) Busy_Rate
from v$dispatcher
group by network, status;
PROTOCOL STATUS CLIENT BUSY_RATE
------------------------------------------------------------ ---------------- ---------- ----------
(ADDRESS=(PROTOCOL=tcp)(HOST=218.145.28.7)(PORT=33382)) WAIT 3 .281515755
(ADDRESS=(PROTOCOL=tcp)(HOST=218.145.28.7)(PORT=33387)) WAIT 6 .252156678
(ADDRESS=(PROTOCOL=tcp)(HOST=218.145.28.7)(PORT=33388)) WAIT 4 .251919353
(ADDRESS=(PROTOCOL=tcp)(HOST=218.145.28.7)(PORT=33389)) WAIT 3 .299103536
(ADDRESS=(PROTOCOL=tcp)(HOST=218.145.28.7)(PORT=33390)) WAIT 5 .283699882
from v$queue q, v$dispatcher d
where q.type = 'DISPATCHER'
and q.paddr = d.paddr;
----------------------------------------
-14.579853189264776235152468444287170899
from v$shared_server
where status !='QUIT';
NAME REQUESTS busy% STATUS
---- ---------- ---------- ----------------
S000 277577 62.2763783 EXEC
S001 40234 63.6475632 WAIT(COMMON)
S002 62301 61.3844034 WAIT(COMMON)
S003 162476 46.8848914 EXEC
S004 318669 40.1888031 WAIT(COMMON)
S005 16919 .001713668 WAIT(COMMON)
S006 14710 33.4804841 WAIT(COMMON)
S007 21085 29.9462006 WAIT(COMMON)
S008 2385 24.5642131 WAIT(COMMON)
S009 275 48.7618465 WAIT(COMMON)
from v$queue where type = 'COMMON';
--------------------------------------------------------------
.306186399935636827393766872252177093756 hunderdths of seconds
p.username os_user, p.terminal, s.program
from v$dispatcher d, v$circuit c, v$session s, v$process p
where d.paddr = c.dispatcher(+)
and c.saddr = s.saddr(+)
and s.paddr = p.addr(+)
order by d.network, d.name, s.username;
d.name "Dispatcher",ss.name "Server",
s.sid,s.serial#,
s.status,c.queue, c.messages, c.bytes
from v$circuit c, v$dispatcher d, v$session s, v$shared_server ss
where c.dispatcher = d.paddr(+)
and c.server = ss.paddr(+)
and c.saddr = s.saddr(+)
order by c.circuit
/
Address Disp Serv SID SERIAL# STATUS QUEUE MESSAGES BYTES
-------- ---- ---- ---------- ---------- -------- ---------------- ---------- ----------
6B46FC00 D002 74 11144 INACTIVE NONE 106 17992
6B4704B8 D003 258 33512 INACTIVE NONE 79 37237
6B473D64 D000 S000 332 11353 ACTIVE SERVER 11 2795
6B47461C D002 58 6945 INACTIVE NONE 625 61249
6B475330 D001 NONE 119 42629
6B475618 D004 112 29956 INACTIVE NONE 8802 1732905
6B476ECC D004 53 10790 INACTIVE NONE 48942 7155473
6B479320 D000 271 54721 INACTIVE NONE 9414 1912410
6B47E480 D001 187 13497 INACTIVE NONE 40 3188
6B47FD34 D003 292 9882 INACTIVE NONE 6112 284072
6B481EA0 D003 217 8188 INACTIVE NONE 1261 52797
-------- ---- ---- ---------- ---------- -------- ---------------- ---------- ----------
6B4828CC D000 S001 323 50470 ACTIVE SERVER 11 2797
6B483D24 D004 S003 289 7196 ACTIVE SERVER 11 2794
6B486BA4 D004 272 56995 INACTIVE NONE 36 3310
6B48A168 D001 S005 309 64638 ACTIVE SERVER 9 2029
6B48DA14 D000 S002 61 56845 ACTIVE SERVER 11 2792
from v$statname n,v$sesstat s
where n.statistic#=s.statistic#
and name='session uga memory max'
/
SUM(VALUE)
----------
12659128
FROM V$SYSSTAT
WHERE NAME IN ('sorts (memory)', 'sorts (disk)');
NAME VALUE
---------------------------------------------------------------- ----------
sorts (memory) 106379762
sorts (disk) 236
control file, redo log file multiplex
2. $ORACLE_HOME/dbs/initorcl.ora 파일에서 control_files 4번째 파일 추가
3. cp /u01/oradata/orcl/control01.ctl ./control04.ctl 복사
4. spfileorcl.ora 파일을 먼저 읽어드리므로 삭제
-------------------------------
/u01/oradata/orcl/control01.ctl
/u01/oradata/orcl/control02.ctl
/u01/oradata/orcl/control03.ctl
'/u01/oradata/orcl/control02.ctl','/u01/oradata/orcl/control03.ctl',
'/u01/oradata/orcl/control04.ctl' scope=spfile;
!cp /u01/oradata/orcl/control01.ctl ./control04.ctl
select name from v$controlfile;
----------------------------------------
/u01/oradata/orcl/control01.ctl
/u01/oradata/orcl/control02.ctl
/u01/oradata/orcl/control03.ctl
/u01/oradata/orcl/control04.ctl
# redo log 다중화
SQL> select * from v$logfile;
---------- ------- ------- ------------------------------ -----
3 ONLINE /u01/oradata/orcl/redo03.log NO
2 ONLINE /u01/oradata/orcl/redo02.log NO
1 ONLINE /u01/oradata/orcl/redo01.log NO
-------- add group
'/u01/oradata/orcl/redo04.log' size 10m;
SQL> select * from V$logfile;
---------- ------- ------- ----------------------------------- ---
3 ONLINE /u01/oradata/orcl/redo03.log NO
2 ONLINE /u01/oradata/orcl/redo02.log NO
1 ONLINE /u01/oradata/orcl/redo01.log NO
4 ONLINE /u01/oradata/orcl/redo04.log NO
SQL> alter database add logfile member
2 '/home/oracle/backup/redo01_b.log' to group 1,
3 '/home/oracle/backup/redo02_b.log' to group 2,
4 '/home/oracle/backup/redo03_b.log' to group 3,
5 '/home/oracle/backup/redo04_b.log' to group 4;
SQL> select * from V$logfile;
---------- ------- ------- --------------------------------------------- ---
3 ONLINE /u01/oradata/orcl/redo03.log NO
2 ONLINE /u01/oradata/orcl/redo02.log NO
1 ONLINE /u01/oradata/orcl/redo01.log NO
4 ONLINE /u01/oradata/orcl/redo04.log NO
1 INVALID ONLINE /home/oracle/backup/redo01_b.log NO
2 INVALID ONLINE /home/oracle/backup/redo02_b.log NO
3 INVALID ONLINE /home/oracle/backup/redo03_b.log NO
4 INVALID ONLINE /home/oracle/backup/redo04_b.log NO
SQL> alter database drop logfile group 4;
SQL> select * from V$logfile;
---------- ------- ------- --------------------------------------------- ---
3 ONLINE /u01/oradata/orcl/redo03.log NO
2 ONLINE /u01/oradata/orcl/redo02.log NO
1 ONLINE /u01/oradata/orcl/redo01.log NO
1 INVALID ONLINE /home/oracle/backup/redo01_b.log NO
2 INVALID ONLINE /home/oracle/backup/redo02_b.log NO
3 INVALID ONLINE /home/oracle/backup/redo03_b.log NO
SQL> alter database drop logfile member
'/home/oracle/backup/redo01_b.log'
'/home/oracle/backup/redo02_b.log'
'/home/oracle/backup/redo03_b.log'
SQL> select * from V$log;
SQL> alter system switch logfile;
SQL> alter system checkpoint;
SQL> alter database drop logfile member
'/home/oracle/backup/redo03_b.log'
SQL> alter database add logfile member
'/u01/oradata/orcl/redo01_b.log' to group 1,
'/u01/oradata/orcl/redo02_b.log' to group 2,
'/u01/oradata/orcl/redo03_b.log' to group 3;