'db' 태그의 글 목록

Oracle DB 기본 상태 조회 스크립트

1. IT Story/Scripts 2019. 1. 7. 21:05

Oracle DB 기본 상태 조회 스크립트

SELECT 'MEASURE TIME' FTYPE,

TO_CHAR (SYSDATE, 'YYYY.MM.DD HH24:MI:SS') NAME,

NULL VALUE,

NULL NOTE

FROM DUAL

UNION ALL

SELECT 'CURRENT SCN' FTYPE,

TO_CHAR (CURRENT_SCN, 'FM999,999,999,999,999,999,999') NAME,

NULL,

NULL

FROM GV$DATABASE

WHERE INST_ID = (select INST_ID from GV$DATABASE)

UNION ALL

SELECT 'DB_NAME' FTYPE,

DB_NAME,

NULL VALUE,

NULL VTYPE

FROM DBA_HIST_DATABASE_INSTANCE

WHERE INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)

AND STARTUP_TIME = TO_DATE (20180217091655, 'YYYYMMDDHH24MISS')

UNION ALL

SELECT 'DBID' FTYPE,

TO_CHAR (DBID) NAME,

NULL,

NULL

FROM DBA_HIST_DATABASE_INSTANCE

WHERE INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)

AND STARTUP_TIME = TO_DATE (20180217091655, 'YYYYMMDDHH24MISS')

UNION ALL

SELECT 'INSTANCE NUMBER' FTYPE,

TO_CHAR (INSTANCE_NUMBER) NAME,

NULL,

NULL

FROM DBA_HIST_DATABASE_INSTANCE

WHERE INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)

AND STARTUP_TIME = TO_DATE (20180217091655, 'YYYYMMDDHH24MISS')

UNION ALL

SELECT 'INSTANCE NAME' FTYPE,

INSTANCE_NAME NAME,

NULL,

NULL

FROM DBA_HIST_DATABASE_INSTANCE

WHERE INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)

AND STARTUP_TIME = TO_DATE (20180217091655, 'YYYYMMDDHH24MISS')

UNION ALL

SELECT 'HOST NAME' FTYPE,

HOST_NAME NAME,

NULL,

NULL

FROM DBA_HIST_DATABASE_INSTANCE

WHERE INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)

AND STARTUP_TIME = TO_DATE (20180217091655, 'YYYYMMDDHH24MISS')

UNION ALL

SELECT 'STARTUP TIME' FTYPE,

TO_CHAR (STARTUP_TIME, 'YYYY.MM.DD HH24:MI:SS') NAME,

NULL,

NULL

FROM DBA_HIST_DATABASE_INSTANCE

WHERE INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)

AND STARTUP_TIME = TO_DATE (20180217091655, 'YYYYMMDDHH24MISS')

UNION ALL

SELECT 'VERSION' FTYPE,

VERSION NAME,

NULL,

NULL

FROM DBA_HIST_DATABASE_INSTANCE

WHERE INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)

AND STARTUP_TIME = TO_DATE (20180217091655, 'YYYYMMDDHH24MISS')

UNION ALL

SELECT 'PARAM' FTYPE,

PARAMETER_NAME NAME,

TO_CHAR (VALUE) VALUE,

NULL

FROM DBA_HIST_PARAMETER

WHERE DBID = (select dbid from v$database)

AND INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)

AND SNAP_ID = 873

UNION ALL

SELECT 'SGA' FTYPE,

NAME,

ROUND (VALUE / 1024 / 1024, 1) || ' MB' VALUE,

NULL

FROM DBA_HIST_SGA

WHERE DBID = (select dbid from v$database)

AND INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)

AND SNAP_ID = 873

UNION ALL

SELECT 'PGA' FTYPE,

NAME,

ROUND (VALUE / 1024 / 1024, 1) || ' MB' VALUE,

NULL

FROM DBA_HIST_PGASTAT

WHERE DBID = (select dbid from v$database)

AND INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)

AND SNAP_ID = 873

UNION ALL

SELECT 'CURRENT SGA' FTYPE,

NAME,

ROUND (BYTES / 1024 / 1024, 1) || ' MB' VALUE,

RESIZEABLE VTYPE

FROM GV$SGAINFO

WHERE INST_ID = (select distinct INST_ID from GV$SGAINFO)

UNION ALL

SELECT 'CURRENT PGA' FTYPE,

NAME,

DECODE (

UNIT,

'bytes', TO_CHAR (ROUND (VALUE / 1024 / 1024, 1),

'FM999,999,999,999.9'),

TO_CHAR (VALUE))

VALUE,

DECODE (UNIT, 'bytes', 'MB', UNIT) VTYPE

FROM GV$PGASTAT

WHERE INST_ID = (select distinct INST_ID from GV$PGASTAT)

UNION ALL

SELECT 'CURRENT SIZE : SEGMENT' FTYPE,

TO_CHAR (SUM (BLOCKS), 'FM999,999,999,999') || ' Blocks',

TO_CHAR (ROUND ( (SUM (BYTES) / 1024 / 1024 / 1024), 1),

'FM999,999,999,999')

|| ' GB',

NULL VTYPE

FROM DBA_SEGMENTS

UNION ALL

SELECT 'CURRENT SIZE : DATAFILE' FTYPE,

TO_CHAR (SUM (BLOCKS), 'FM999,999,999,999') || ' Blocks',

TO_CHAR (ROUND ( (SUM (BYTES) / 1024 / 1024 / 1024), 1),

'FM999,999,999,999')

|| ' GB',

NULL VTYPE

FROM DBA_DATA_FILES

ORDER BY 1, 2;

저작자표시 동일조건

'1. IT Story > Scripts' 카테고리의 다른 글

Oracle DB 기본 점검 스크립트 (0)	2019.01.10
Oracle RMAN FULL BACKUP 스크립트 (0)	2019.01.09
Oracle SQL Stat 조회 스크립트 (0)	2019.01.08
Oracle Top 10 Event 추출 스크립트 (0)	2019.01.08
Oracle DB AWR 활용하여 IO 성능 조회 스크립트 (0)	2019.01.07
Oracle AWR을 활용한 주요 Stats 지표 추출 스크립트 (0)	2019.01.07
Oracle 테이블 사이즈 및 테이블별 카운트 출력 스크립트 (0)	2019.01.07
Oracle Cold(콜드) 백업 스크립트 (0)	2019.01.07

운명을바꾸는자

IT와 함께 살아가는 삶

,

Oracle VPD(Virtual Private Database)

1. IT Story/DB 2013. 3. 31. 00:23

ORACLE VPD란?
- Virtual Private Database(이하 VPD) 란 하나의 DATABASE내의 수 많은 data 에서 보안상 혹은 특정 서비스를 목적으로 필요한 유저에 해당하는 data만을 다룰 수 있게 하는 기술을 말한다.
- 일반적으로 보안레벨, 부서별 혹은 사이트별 등 특정 목적에 맞게 구분되지만, 사용자는 이러한 구분을 받고 있다는 것을 전혀 알지 못하며, 마치 자신만의 DATABASE 를 다루는 것처럼 보이게 되어 Virtual Private Database 라고 부른다.

스키마 단위 TEST Script 및 확인

User, table 생성

SYS> GRANT CREATE SESSION, CREATE ANY CONTEXT, CREATE PROCEDURE, CREATE TRIGGER, ADMINISTER DATABASE TRIGGER TO sysadmin_vpd IDENTIFIED BY test;

Grant succeeded.

SYS>

SYS> GRANT EXECUTE ON DBMS_SESSION TO sysadmin_vpd;

Grant succeeded.

SYS>

SYS> GRANT EXECUTE ON DBMS_RLS TO sysadmin_vpd;

Grant succeeded.

SYS>

SYS> GRANT CREATE SESSION TO tbrooke IDENTIFIED BY test;

Grant succeeded.

SYS>

SYS> GRANT CREATE SESSION TO owoods IDENTIFIED BY test;

Grant succeeded.

SYS>

SYS> ALTER USER SCOTT ACCOUNT UNLOCK IDENTIFIED BY tiger

2 ;

User altered.

SYS>

SYS> conn scott/tiger

Connected.

SCOTT>

SCOTT> create table customers (

cust_no number(4),

cust_email varchar2(20),

4 cust_name varchar2(20));

Table created.

SCOTT>

SCOTT> CREATE TABLE orders_tab (

2 cust_no NUMBER(4),

3 order_no NUMBER(4));

Table created.

SCOTT>

SCOTT> INSERT INTO customers VALUES (1234, 'TBROOKE', 'Thadeus Brooke');

1 row created.

SCOTT>

SCOTT> INSERT INTO customers VALUES (5678, 'OWOODS', 'Oberon Woods');

1 row created.

SCOTT>

SCOTT> GRANT SELECT ON customers TO sysadmin_vpd;

Grant succeeded.

SCOTT>

SCOTT> select * from orders_tab;

no rows selected

SCOTT>

SCOTT> INSERT INTO orders_tab VALUES (1234, 9876);

1 row created.

SCOTT>

SCOTT> INSERT INTO orders_tab VALUES (5678, 5432);

1 row created.

SCOTT>

SCOTT> INSERT INTO orders_tab VALUES (5678, 4592);

1 row created.

SCOTT>

SCOTT> GRANT SELECT ON orders_tab TO tbrooke;

Grant succeeded.

SCOTT>

SCOTT> GRANT SELECT ON orders_tab TO owoods;

Grant succeeded.

SCOTT>

application context 생성

SCOTT> conn sysadmin_vpd/test

Connected.

SYSADMIN_VPD>

SYSADMIN_VPD> CREATE OR REPLACE CONTEXT orders_ctx USING orders_ctx_pkg;

Context created.

SYSADMIN_VPD>

CREATE OR REPLACE PACKAGE orders_ctx_pkg

END; IS

IS

PROCEDURE set_custnum;

END;

5 /

Package created.

SYSADMIN_VPD>

CREATE OR REPLACE PACKAGE BODY orders_ctx_pkg

IS

PROCEDURE set_custnum

AS

custnum NUMBER;

BEGIN

SELECT cust_no INTO custnum FROM scott.customers

WHERE cust_email = SYS_CONTEXT('USERENV', 'SESSION_USER');

DBMS_SESSION.SET_CONTEXT('orders_ctx', 'cust_no', custnum);

10 EXCEPTION

WHEN NO_DATA_FOUND THEN NULL;

END set_custnum;

13 END;

14 /

Package body created.

Logon trigger 생성

SYSADMIN_VPD>

CREATE TRIGGER set_custno_ctx_trig AFTER LOGON ON DATABASE

BEGIN

sysadmin_vpd.orders_ctx_pkg.set_custnum;

END;

5 /

Trigger created.

SYSADMIN_VPD>

SYS> conn tbrooke/test

Connected.

TBROOKE>

TBROOKE> SELECT SYS_CONTEXT('orders_ctx', 'cust_no') custnum FROM DUAL;

CUSTNUM

----------------------------------------------------------------------------------------------------

1234

TBROOKE>

DBMS_RLS 에 사용할 policy function 생성

SYSADMIN_VPD>

CREATE OR REPLACE FUNCTION get_user_orders(

orders_pred VARCHAR2 (400);

schema_p IN VARCHAR2,

table_p IN VARCHAR2)

RETURN VARCHAR2

AS

orders_pred VARCHAR2 (400);

BEGIN

orders_pred := 'cust_no = SYS_CONTEXT(''orders_ctx'', ''cust_no'')';

RETURN orders_pred;

END;

/

Function created.

SYSADMIN_VPD>

DBMS_RLS.ADD_POLICY 설정

SYSADMIN_VPD> BEGIN

DBMS_RLS.ADD_POLICY (

object_schema => 'scott',

object_name => 'orders_tab',

function_schema => 'sysadmin_vpd',

policy_name => 'orders_policy',

function_schema => 'sysadmin_vpd',

policy_function => 'get_user_orders',

statement_types => 'select');

END;

/

PL/SQL procedure successfully completed.

SYSADMIN_VPD>

TEST

SYS> select * from scott.orders_tab;

CUST_NO ORDER_NO

---------- ----------

1234 9876

5678 5432

5678 4592

SYS>

TBROOKE>

TBROOKE> select * from scott.orders_tab;

CUST_NO ORDER_NO

---------- ----------

1234 9876

TBROOKE>

SYS> conn owoods/test

Connected.

OWOODS>

OWOODS> select * from scott.orders_tab;

CUST_NO ORDER_NO

---------- ----------

5678 5432

5678 4592

OWOODS>

확인

OWOODS> select * from scott.orders_tab where ORDER_NO=5432 and cust_no=5678;

CUST_NO ORDER_NO

---------- ----------

5678 5432

Execution Plan

----------------------------------------------------------

Plan hash value: 3940001695

---------------------------------------------------------------------------------

---------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 26 | 3 (0)| 00:00:01 |

|* 1 | FILTER | | | | | |

|* 2 | TABLE ACCESS FULL| ORDERS_TAB | 1 | 26 | 3 (0)| 00:00:01 |

---------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(5678=TO_NUMBER(SYS_CONTEXT('orders_ctx','cust_no')))

2 - filter("ORDER_NO"=5432 AND "CUST_NO"=5678)

==========================================

OWOODS> select * from scott.orders_tab;

CUST_NO ORDER_NO

---------- ----------

5678 5432

5678 4592

Execution Plan

----------------------------------------------------------

Plan hash value: 1302839296

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 2 | 52 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| ORDERS_TAB | 2 | 52 | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("CUST_NO"=TO_NUMBER(SYS_CONTEXT('orders_ctx','cust_no')))

Note

-----

- dynamic sampling used for this statement

Statistics

----------------------------------------------------------

2 recursive calls

0 db block gets

8 consistent gets

0 physical reads

0 redo size

513 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

2 rows processed

========================================================

OWOODS> select * from scott.orders_tab where order_no=5432;

CUST_NO ORDER_NO

---------- ----------

5678 5432

Execution Plan

----------------------------------------------------------

Plan hash value: 1302839296

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 26 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| ORDERS_TAB | 1 | 26 | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("ORDER_NO"=5432 AND "CUST_NO"=TO_NUMBER(SYS_CONTEXT('order

s_ctx','cust_no')))

Note

-----

- dynamic sampling used for this statement

Statistics

----------------------------------------------------------

2 recursive calls

0 db block gets

8 consistent gets

0 physical reads

0 redo size

473 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

컬럼단위 Script 및 확인

권한 부여

GRANT CREATE SESSION, CREATE ANY CONTEXT, CREATE PROCEDURE, CREATE TRIGGER, ADMINISTER DATABASE TRIGGER TO SCOTT;

권한이 부여되었습니다.

SQL> GRANT EXECUTE ON DBMS_SESSION TO scott;

권한이 부여되었습니다.

SQL> GRANT EXECUTE ON DBMS_RLS TO scott;

권한이 부여되었습니다.

Funtion 및 policy 추가

CREATE OR REPLACE FUNCTION hide_sal_comm (

v_schema IN VARCHAR2,

v_objname IN VARCHAR2)

RETURN VARCHAR2 AS

con VARCHAR2 (200);

begin

con := 'deptno=30';

RETURN (con);

END hide_sal_comm;

/

BEGIN

DBMS_RLS.ADD_POLICY (

object_schema => 'scott',

object_name => 'emp',a

policy_name => 'hide_sal_policy',

policy_function => 'hide_sal_comm',

sec_relevant_cols => 'sal,comm');

END;

/

SELECT d.deptno, ENAME, d.dname, JOB , SAL

FROM emp e, dept d

WHERE d.deptno = e.deptno;

SELECT d.deptno, ENAME, d.dname, JOB , SAL

FROM emp e, dept d

3 WHERE d.deptno = e.deptno;

DEPTNO ENAME DNAME JOB SAL

---------- ---------- -------------- --------- ----------

30 ALLEN SALES SALESMAN 1600

30 WARD SALES SALESMAN 1250

30 MARTIN SALES SALESMAN 1250

30 BLAKE SALES MANAGER 2850

30 TURNER SALES SALESMAN 1500

30 JAMES SALES CLERK 950

6 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 568005898

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 5 | 170 | 4 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 5 | 170 | 4 (0)| 00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |

|* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |

|* 4 | TABLE ACCESS FULL | EMP | 5 | 105 | 3 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("D"."DEPTNO"=30)

4 - filter("DEPTNO"=30)

Statistics

----------------------------------------------------------

5 recursive calls

0 db block gets

10 consistent gets

0 physical reads

0 redo size

789 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

6 rows processed

SELECT d.deptno, ENAME, d.dname, JOB

FROM emp e, dept d

WHERE d.deptno = e.deptno;

SELECT d.deptno, ENAME, d.dname, JOB

FROM emp e, dept d

WHERE d.deptno = e.deptno;

DEPTNO ENAME DNAME JOB

---------- ---------- -------------- ---------

20 SMITH RESEARCH CLERK

30 ALLEN SALES SALESMAN

30 WARD SALES SALESMAN

20 JONES RESEARCH MANAGER

30 MARTIN SALES SALESMAN

30 BLAKE SALES MANAGER

10 CLARK ACCOUNTING MANAGER

20 SCOTT RESEARCH ANALYST

10 KING ACCOUNTING PRESIDENT

30 TURNER SALES SALESMAN

20 ADAMS RESEARCH CLERK

30 JAMES SALES CLERK

20 FORD RESEARCH ANALYST

10 MILLER ACCOUNTING CLERK

14 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 351108634

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 14 | 420 | 4 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 14 | 420 | 4 (0)| 00:00:01 |

| 2 | TABLE ACCESS FULL | EMP | 14 | 238 | 3 (0)| 00:00:01 |

| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |

|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

4 - access("D"."DEPTNO"="DEPTNO")

Statistics

----------------------------------------------------------

4 recursive calls

0 db block gets

24 consistent gets

0 physical reads

0 redo size

980 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

14 rows processed

exec DBMS_RLS.DROP_POLICY('scott','emp','hide_sal_policy');

BEGIN

DBMS_RLS.ADD_POLICY(

object_schema => 'scott',

object_name => 'emp',

policy_name => 'hide_sal_policy',

policy_function => 'hide_sal_comm',

sec_relevant_cols =>' sal,comm',

sec_relevant_cols_opt => dbms_rls.ALL_ROWS);

END;

/

SELECT ENAME, d.dname, job, sal, comm

FROM emp e, dept d

WHERE d.deptno = e.deptno;

SCOTT>

SCOTT> select ENAME, d.dname, job, sal, comm, d.deptno

2 from emp e, dept d

3 where d.deptno = e.deptno;

ENAME DNAME JOB SAL COMM DEPTNO

---------- -------------- --------- ---------- ---------- ----------

SMITH RESEARCH CLERK 20

ALLEN SALES SALESMAN 1600 300 30

WARD SALES SALESMAN 1250 500 30

JONES RESEARCH MANAGER 20

MARTIN SALES SALESMAN 1250 1400 30

BLAKE SALES MANAGER 2850 30

CLARK ACCOUNTING MANAGER 10

SCOTT RESEARCH ANALYST 20

KING ACCOUNTING PRESIDENT 10

TURNER SALES SALESMAN 1500 0 30

ADAMS RESEARCH CLERK 20

JAMES SALES CLERK 950 30

FORD RESEARCH ANALYST 20

MILLER ACCOUNTING CLERK 10

14 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 351108634

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 14 | 504 | 4 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 14 | 504 | 4 (0)| 00:00:01 |

| 2 | TABLE ACCESS FULL | EMP | 14 | 322 | 3 (0)| 00:00:01 |

| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |

|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

4 - access("D"."DEPTNO"="DEPTNO")

Statistics

----------------------------------------------------------

3 recursive calls

0 db block gets

24 consistent gets

0 physical reads

0 redo size

1129 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

14 rows processed

'1. IT Story > DB' 카테고리의 다른 글

Oracle Golden Gate 개념 및 Installaton (2)	2013.04.08
Oracle 11gR2 TDE(Transparent Data Encryption) (0)	2013.03.31
Oracle 10g CRS 제거 (0)	2013.03.31
Oracle 11g RAC with ASM TEST (2)	2013.03.31
Splunk install-Linux (0)	2013.02.09
Big-Data Hive 환경구성(MySql) (0)	2013.02.09
Big-Data Hadoop 환경구성 (0)	2013.02.09
Big-Data Hadoop기초 (0)	2013.02.09

운명을바꾸는자

IT와 함께 살아가는 삶

,

백업하기전에 해야 되는 것들!!!

1. IT Story/DB 2011. 9. 4. 20:25

안녕하세요!!

비록 학생이지만 좋은 기회로 한O에서 Oracle Migration 작업에 참여하게 되었습니다.

그 곳에서 해던 DATABASE 백업 작업을

제가 가지고 있는 DB를 통하여 다시 재구성 해보았습니다.

-운명을 바꾸는 자-

데이터 베이스를 백업하기 전에 해야될 것들!!

<주의 사항>

-루트권한으로 백업 하지 않기.

-항상 스샷을 남겨서 작업을 기록

-Log Session 남기기

-안전한 저장 공간확보

-작업할 내용을 엔지니어와 담당자와 함께 미팅하기

1. DB 버전확인 및 백업할 전체 현재 DB 사용량 확인

1-1 Oracle DB 버전확인

sqlplus "/as sysdba"

명령어를 통하여 간단하게 현재 DB의 버전을 확인할 수 있다.

1-2 Oracle DB 데이터 사용량 확인

select sum (bytes)/1024/1024 from DBA_data_files;

//현재 DB의 전체 데이터 파일의 크기를 확인 할 수 있다.

<주의> DB가 1개 이상이라면, 각 SID를 통해 DB에 접근하여 확인하자.

(echo $ORACLE_SID / Oracle_SID= SID)

select sum (bytes)/1024/1024 from DBA_free_space;

//현재 DB의 남은 데이터 파일의 크기를 확인 할 수 있다.

※ 결론적으로 전체 크기 - 남은 크기 = 현재 DB사용량을 알 수 있다.

2. 현재 사용중인 DB의 갯수 확인

2-1 "cd $ORACLE_BASE/admin" 에서 확인

- cd $ORACLE_BASE/admin 에서 DB의 SID(디렉토리) 갯수를 통해서 현재 사용중인

DB의 갯수를 확인할 수 있다.

2-2 "cd $ORACLE_HOME/dbs/" 에서 확인

- "cd $ORACLE_HOME/dbs/" 에서 spfile<SID>를 통하여 DB의 갯수를 확인 할 수 있다.

3. 중요파일들에 대한 경로 저장해두기

3-1 Recovery 시에 중요 파일의 해당 위치가 중요하다.(따로 기록해 두는 것이 좋다.)
3-2 Cold 백업을 Recovery할 때 해당 파일의 위치가 중요하다.

※SID를 통하여 DB의 갯수를 확인한다.(SID는 oracle DB의 고유 이름을 말한다. 각 DB마다 다르다.)

'1. IT Story > DB' 카테고리의 다른 글

Oracle Structure 메모리 (0)	2012.02.29
Oracle 11g Install[RHEL 5] 3 (0)	2012.02.27
Oracle 11g Install[RHEL 5] 2 (0)	2012.02.27
Oracle 11g Install[RHEL 5] 1 (0)	2012.02.27
Oracle Engine백업 및 그외.. (0)	2011.09.11
Cold 백업하자!! (0)	2011.09.11
Export백업!! (0)	2011.09.11
Oracle 공부하기 위해 유용한 사이트 (0)	2011.08.14

운명을바꾸는자

IT와 함께 살아가는 삶

,

'db'에 해당되는 글 3건

Oracle DB 기본 상태 조회 스크립트

'1. IT Story > Scripts' 카테고리의 다른 글

Oracle VPD(Virtual Private Database)

'1. IT Story > DB' 카테고리의 다른 글

백업하기전에 해야 되는 것들!!!

'1. IT Story > DB' 카테고리의 다른 글

공지사항

카테고리

태그목록

글 보관함

달력

링크

운명을바꾸는자

LATEST FROM OUR BLOG

LATEST COMMENTS

티스토리툴바