|
1) 먼저 유닉스에서 오라클을 기동할 수 있는 구너한이 있는 사용자 ID 로 로그인한다.
대개 oracle User
2) 현재 오라클 SID 를 확인 한다.
env |grep ORACLE_SID
ORACLE_SID=ARTDOM
3) SVRMGRL 를 실행하여 오라클을 기동시킨다.
VLDB: 9i 이후엔 svrmgrl ( oracle server manager) 가 지원되지 않는다.
sqlplus ' /as sysdba'
SQL*Plus: Release 10.1.0.4.0 - Production on Tue Jun 24 16:22:44 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL>startup
ORACLE instance started.
Total System Global Area 1174405120 bytes
Fixed Size 1329584 bytes
Variable Size 316913232 bytes
Database Buffers 855638016 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.
SQL>
4) sqlplus 를 통한 접속 테스트
sqlplus scott/manager
SQL*Plus: Release 10.1.0.4.0 - Production on Tue Jun 24 16:24:36 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
scott@ARTDOM>select * from tab ;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
PLAN_TABLE TABLE
CHAINED_ROWS TABLE
CHECK_NEXT TABLE
BIG_TABLE TABLE
CHECK_PART TABLE
CHECK_TRAN TABLE
C_1 TABLE
CHECK_RENAME_FILES TABLE
CHECK_PART2 TABLE
CHECK_PART3 TABLE
DEPT_SAL TABLE
MV_DEPT TABLE
CHECK_DDL TABLE
SCOTT_DBA_SEGMENTS TABLE
SCOTT_TABLES TABLE
CHECK_VAR TABLE
CHECK_ORANGE TABLE
BIGEMP TABLE
22 rows selected.
5) 오라클 필수 백그라운드 프로세스 확인을 통한 확인 VLDB: smon
ps -ef|grep ora_ |grep sm
oracle 442430 1 0 16:22:46 - 0:00 ora_smon_ARTDOM
(1) 모든 데이터베이스 인스턴스가 기동되었는지 확인 한다.
(2) alert Log 를 확인 한다.
(3) 데이터베이스가 성공적으로 백업되었는지 확인 한다.
(4) 데이터베이스의 아카이브 로그 파일이 성공적으로 백업되었는지 확인한다.
(5) 데이터베이스 성능을 위해 충분한 리소스가 존재하는지 확인 한다.
현실에 맞지 않다 - 책 내용
more "space.sql"
select tablespace_name,
sum(blocks) as free_blk, trunc(sum(bytes)/(1024*1024)) AS FREE_M,
max(bytes)/(1024) as big_chunk_k, count(*) as num_chunks
from dba_free_space
group by tablespace_name ;
select AA.*, (AA."Allocation" - AA."Used" + BB."Free" ) "Free Size", BB."Free"
from
(select a.tablespace_name "TABLESPACE_NAME", sum(a.maxbytes)/1024/1024 "Allocation", sum(a.bytes)/1024/1024 "Used"
from dba_data_Files a
group by a.tablespace_name ) AA,
(select b.tablespace_name, sum(b.bytes)/1024/1024 "Free"
from dba_free_space b
group by b.tablespace_name ) BB
where AA.TABLESPACE_NAME = BB.TABLESPACE_NAME(+) ;
select a.tablespace_name , a.bytes - b.bytes "FREE(M)"
from
( select tablespace_name, sum(maxbytes)/1024/1024 bytes from dba_data_files group by tablespace_name ) a,
( select tablespace_name, sum(bytes)/1024/1024 bytes from dba_segments group by tablespace_name) b
where a.tablespace_name = b.tablespace_name;
select a.tablespace_name , a.bytes - b.bytes "FREE(M)"
from
( select tablespace_name, sum(maxbytes)/1024/1024 bytes from dba_data_files group by tablespace_name ) a,
( select tablespace_name, sum(bytes)/1024/1024 bytes from dba_extents group by tablespace_name) b
where a.tablespace_name = b.tablespace_name;
more "rbs_usage.sql"
col today format a20 new_value curr_time
col today noprint
select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') today from dual ;
prompt =========================================================
prompt == rollback segment storage and status ==
prompt => DAte : [ &CURR_TIME ]
prompt =========================================================
set linesize 120
col rb_seg for a10
col status for a10
col tbs_nm for a10
col ini_kb for 9,999,999
col nxt_kb for 9,999,999
col tot_kb for 9,999,999
col ext for 999
col xt for 99
select a.segment_name as rb_seg,
a.status, c.xacts as xt,
b.initial_extent/1024 as ini_kb,
b.next_extent/1024 as nxt_kb,
b.extents as ext,
b.bytes/1024 as tot_kb,
a.tablespace_name as tbs_nm
from dba_rollback_segs a, dba_segments b, v$rollstat c
where a.owner = b.owner
and a.segment_name = b.segment_name
and a.segment_id = c.usn
order by a.segment_name ;
sys@ARTDOM>begin
dbms_utility.analyze_schema('SCOTT','ESTIMATE',NULL,5);
end ;
/
PL/SQL procedure successfully completed.
select e.owner, e.segment_type, e.segment_name, count(*) as nr_extents,
s.max_extents, to_char(sum(e.bytes)/(1024*1024),'999,999.90') AS MB
from dba_extents e, dba_segments
where e.segment_name = s.segment_name
group by e.owner, e.segment_type, e.segment_name, s.max_extents
having count(*) > 10 or ( s.max_extents - COUNT(*) ) < 20 )
order by count(*) desc ;
SELECT A.TABLE_NAME, A.NEXT_EXTENT, A.TABLESPACE_NAME
FROM ALL_TABLES A,
( SELECT TABLESPACE_NAME, MAX(BYTES) AS BIG_CHUNK
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME ) F
WHERE F.TABLESPACE_NAME = A.TABLESPACE_NAME
AND A.NEXT_EXTENT > F.BIG_CHUNK ;
ALTER TABLE <owner>.table STORAGE ( MAXEXTENTS UNLIMITED);
more "check_next.sql"
select segment_name, segment_type, dt.tablespace_name, ds.next_extent
from dba_tablespaces dt, dba_segments ds
where dt.tablespace_name = ds.tablespace_name
and dt.next_extent != ds.next_extent
and ds.owner ='&OWNER';
more "check_pk_exist.sql"
select table_name from all_tables where owner='&OWNER'
minus
select table_name from all_constraints where owner='&&OWNER'
and constraint_type ='P';
(1) 데이터베이스의 비정상적인 현상이 발생하는지 확인한다.
(2) 튜닝해야할 부분이 있는지 확인한다.
(3) I/O 경합이 존재하는지 확인
(4) 단편화(Fragmentation)이 존재하는지 확인
RBS 사용 현황 체크 - User 별
select s.username, s.sid, s.serial#, rn.name, rs.curext
,rs.curblk, t.used_ublk, t.used_urec
from v$transaction t
,v$session s
,v$rollname rn
,v$rollstat rs
where t.addr = s.taddr
and t.xidusn = rn.usn
and rn.usn = rs.usn ;
CREATE ROLLBACK SEGMENT vldb_rbs
STORAGE( INITIAL 100M NEXT 5M )
TABLESPACE VLDB_RBS ;
2) 평상시에 OFFLINE 상태 유지
ALTER ROLLBACK SEGMENT Vldb_rbs OFFLINE ;
3) 배치작업 수행 시 ONLINE 상태로 변경
ALTER ROLLBACK SEGMENT vldb_rbs ONLINE ;
4) 각 작업문장이나 세션에서 롤백 세그먼트 지정
SET TRANSACTION USE ROLLBACK SEGMENT vldb_rbs ;
5) 작업 후 OFFLINE 으로 변경함
ALTER ROLLBACK SEGMENT vldb_rbs OFFLINE ;