'어플리케이션/데이터베이스일반'에 해당되는 글 8건

  1. 2011.01.07 Postgre sql 정리..
  2. 2009.12.17 SQL Injection Cheat Sheet
  3. 2009.12.16 oracle sql injection (2)
  4. 2009.09.10 SQL 관련 (xtype 정리)
  5. 2009.07.22 select 1;
  6. 2009.06.03 Oracle - Tunning
  7. 2009.06.03 Oracle - Backup
  8. 2009.04.10 SQL 서버 이름 변경하기
### postgresql password reset ###
Step 1. Edit PostgreSQL config file to establish trust relationship to login without password:
# vi /var/lib/pgsql/data/pg_hba.conf
Old Line:
local all postgres password
Change it to:
local all postgres trust

Step 2. Restart PostgreSQL Server:
# /etc/rc.d/init.d/postgresql restart

Step 3. Change password:
# psql -U postgres template1 -c alter user postgres with password ‘newpassword’;

Step 4. Password has been updated. Revert back the original settings of config file:
# vi /var/lib/pgsql/data/pg_hba.conf
Old Line:
local all postgres trust
Change it to:
local all postgres password

Step 5. Restart server and use your new password to access PostgreSQL Server.
# /etc/rc.d/init.d/postgresql start


0X00. database conn #############
# psql -U segio template1

0x01. database list #########
=# \l
# psql -U postgres -l
        List of databases
   Name    |  Owner   | Encoding
-----------+----------+----------
 cell_000  | user_000 | UTF8
 postgres  | postgres | UTF8
 template0 | postgres | UTF8
 template1 | postgres | UTF8
(4 rows)

0x02.  database access#########
# psql -U postgres cell_000

0x03.  database table#########
cell_000=# \dt

0x04. user table #############
cell_000=# select * from user_info;
 no | rname  | uname  |   uid    |    passwd     |        email        |   mobile    |  birth   | zipcode | zipcode2 | addr1 | addr2 | addr3 | addr4 |           quiz           | answer | polimg | uimg |  level   |   wdate    | udate | open | type | class | ext1 | ext2 | ext3 | ext4 | ext5 | ext6
----+--------+--------+----------+---------------+---------------------+-------------+----------+---------+----------+-------+-------+-------+-------+--------------------------+--------+--------+------+----------+------------+-------+------+------+-------+------+------+------+------+------+------
  1 | sysop  | sysop  | sysop    | ㅇㅇㅇㅇ |                     |             |          |         |          |       |       |       |       |                          |        |        |      | 9,관리자 | 1294742453 |       |      | 0    |       |      |      |      |      |      |
(1건 있음)

Posted by 김주일

더보기


출 처 : http://michaeldaw.org/sql-injection-cheat-sheet
Posted by 김주일

아~ 그대의 속살을 만져보고 싶구나.
오라클..


sql injection 관련해서 공격과 그에 대한 대응방법을 교육시켜주는 사이트.

잘 몰라서 어렵게 느껴지는거겠찌.

링크 : http://st-curriculum.oracle.com/tutorial/SQLInjection/index.htm
Posted by 김주일

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

TypeName TypeID

image 34

text 35

uniqueidentifier 36

date 40

time 41

datetime2 42

datetimeoffset 43

tinyint 48

smallint 52

int 56

smalldatetime 58

real 59

money 60

datetime 61

float 62

sql_variant 98

ntext 99

bit 104

decimal 106

numeric 108

smallmoney 122

bigint 127

varbinary 165

varchar 167

binary 173

char 175

timestamp 189

nvarchar 231

sysname 231

nchar 239

hierarchyid 240

geometry 240

geography 240

xml 241

Posted by 김주일

hi,

I think what you want is to print `TRUE`(1) if there exists at least one match else `FALSE`(0)
for that you can use :

select count(*) >= 1 from test where testdata = 5;

This will return `1` if there is one or more rows that satisfies the given condition.
else if no match is found it will return `0`


If you want the number of matching rows then simply execute the following.

select count(*) from test where testdata = 5;

hope that will solve the problem.

출 처 : http://www.webyog.com/forums//lofiversion/index.php/t3149.html
Posted by 김주일
TAG select 1, SQL

 

튜닝관련 Excel Tool

s_엑셀+DB+진단+툴+샘플화면.GIF

엑셀+DB+진단+툴+샘플화면.GIF

엑셀+DB+진단툴+설명서.txt

DB_Script.zip

DBMon.xls

아직 사용해보진 않음

 

관련 Query#

SELECT SUM(pins) "Executions",
SUM(reloads) "Cache Misses while Executing"
FROM v$librarycache;

 

SELECT SUM(GETS) " DATA DICTIONARY GETS"
SUM(GETMISSES) " DATA DICTIONARY GET MISSES"
FROM V$ROWCACHE;

 

SELECT SUM(value) || ' bytes' "Total memory for all sessions"
FROM v$sesstat, v$statname
WHERE NAME = 'session pga memory'
AND v$sesstat.statistic# = v$statname.statistic#;

 

SELECT name, value
FROM v$sysstat
WHERE name in ( 'db block gets','consistent gets','physical reads');

SELECT 250*"TRUNC(indx/250)+1||'  to '||250*(TRUNC(indx/250)+1)
"Interval", SUM(count) "Buffer Cache Hits"
FROM sys.x$kcbrbh
GROUP BY TRUNC(indx/250);

 

SELECT name, phyrds, phywrts
FROM v$datafile df , v$filestat fs
WHERE df.file#  = fs.file#

select sql_text,buffer_gets,disk_reads
from v$sqlarea
where buffer_gets > 10000
or disk_reads > 10000000
order by disk_reads desc

 

SELECT class, count
FROM v$waitstat
WHERE CLASS IN ( 'system undo header','system undo block','undo header','undo block');
  
SELECT ln.name, gets, misses, immediate_gets, immediate_misses
FROM v$latch l, v$latchname ln
WHERE ln.name IN ('redo allocation','redo copy')
AND ln.latch#=l.latch#;

 

SELECT name, value
FROM v$sysstat
WHERE name = 'redo log space requests';

 

SELECT name, value
FROM v$sysstat
WHERE name IN ('sorts(memory)','sorts(disk)');

 

SELECT class, count
FROM V$waitstat
WHERE class = 'free list'

 

Select name, optsize, shrinks, aveshrink, wraps, extends
From V$ROLLSTAT, V$ROLLNAME
where V$ROLLSTAT.usn = V$ROLLNAME.usn;

 select  a.tablespace_name, a.file_name, b.phyrds “Physical Read#”,
 b.phywrts “Physical Write#”
     from dba_data_files a, v$filestat b
 where a.file_id = b.file#  and a.tablespace_name like  ‘DATA%’;

 select  a.group#, a.member, b.status , b.bytes
    from v$logfile a, v$log b
 where a.group# = b.group#;

 

아래는 시스템이 시작된 후의 CPU사용량.(순간적인 측정이 아님)
select a.sid,spid,status,substr(a.program,1,10) prog,a.terminal, osuser, value/60/100 value
from v$session a, v$process b, v$sesstat c
where c.statistic# = 12
and c.sid = a.sid
and a.paddr = b.addr
order by  value desc;

 

select sid, event,p1,p2,p3
from v$session_wait
where event not like 'SQL%'
and event not like 'rdbms%'
and event not like '%timer'

 

UNIX의 PID로 SID를 찾아 내어 sql문장을 찾아냄
select sid
from v$session a, v$process b
where a.paddr = b.addr
and b.spid = 3179-PID;

 

select sql_text
from v$sqltext a, v$session b
where a.hash_value = b.sql_hash_value
and b.sid = 176


위의 것을 합치면 아래
select c.sql_text
from v$process a, v$session b, v$sqltext c
where a.addr = b.paddr and b.sql_hash_value = c.hash_value
and a.spid = <<PID>>;

select min(a.user_name),min(c.sql_text)
from v$open_cursor a, v$session b,v$sqltext c
where a.sid = b.sid  and b.sql_hash_value = c.hash_value
having count(a.sid) > 5
group by a.sid

SELECT b.file_name, b.bytes FILE_SIZE, sum(a.bytes) FREE_SPACE
FROM dba_free_space a, dba_data_files b
WHERE a.file_id = b.file_id
AND a.tablespace_name = 'PMSUSER'
GROUP BY b.file_name, b.bytes

SELECT sql_text,
       sorts,
       executions,
       users_executing,
       first_load_time,
       invalidations,
       parse_calls,
       disk_reads,
       s.buffer_gets,
       rows_processed,
       optimizer_mode,
       optimizer_cost,
       RAWTOHEX(address) address ,
       hash_value,
       u.username parsing_username,
       s.buffer_gets*100/total.buffer_gets pct_total_gets,
       s.buffer_gets/DECODE(s.rows_processed,0,1,s.rows_processed)
gets_per_row
  FROM v$sql s,
       all_users u,
       (SELECT SUM(buffer_gets) buffer_gets FROM v$sql) total
 WHERE u.user_id=s.parsing_user_id AND executions > 0 
  and u.username = 'PMSDB'
 ORDER BY s.buffer_gets desc

set verify off
clear break
break on column_name on constraint_type

select substr(a.column_name, 1, 15) column_name,
                decode(b.constraint_type, 'P', 'Primary key',
                                          'U', 'Unique key',
                                          'C', 'Check or Not null',
                                          'R', 'Foreign key') constraint_type,
                a.constraint_name constraint_name
  from user_cons_columns a, user_constraints b
 where a.table_name = upper('&1')
   and a.table_name = b.table_name
   and a.constraint_name = b.constraint_name
 order by 1

 

 

[2008.05.26] Oracle Dictionary 조회#

-- 접속한 User 보기
select username, count(sid) from V$SESSION
where STATUS = 'ACTIVE'
group by username;


-- 오라클 버젼보기
select * from v$version;

select * from product_component_version;

 

-- 오라클 서버의 인스턴스
select * from v$instance;

 

-- 오라클 서버의 Database 이름 알아내기
select * from v$database;

 

-- 오라클 서버의 파라미터
select * from v$parameter;

 

-- 오라클 서버의 테이블 스페이스 보기
select * from dba_tablespaces;


-- 캐릭터셋, 문자셋
select * from sys.props$ where name = 'NLS_CHARACTERSET';
select * from v$nls_parameters where parameter='NLS_CHARACTERSET';

 

-- 오라클의 리소스 상태
select * from v$resource_limit;

 

 

[2008.05.22] Table Space 정보 구하기 - Query#

select * from (
    select    TABLESPACE_NAME,
        INITIAL_EXTENT,
        NEXT_EXTENT,
        MIN_EXTENTS,
        MAX_EXTENTS,
        PCT_INCREASE,
        STATUS,
        CONTENTS
    from     dba_tablespaces
    order     by TABLESPACE_NAME
) a,
(
    select    a.TABLESPACE_NAME,
        a.BYTES bytes_used,
        b.BYTES bytes_free,
        b.largest,
        a.FILE_NAME,
        round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
    from    
        (
            select     TABLESPACE_NAME,
                FILE_NAME,
                sum(BYTES) BYTES
            from     dba_data_files
            group     by TABLESPACE_NAME, FILE_NAME
        )
        a,
        (
            select     TABLESPACE_NAME,
                sum(BYTES) BYTES ,
                max(BYTES) largest
            from     dba_free_space
            group     by TABLESPACE_NAME
        )
        b
    where     a.TABLESPACE_NAME=b.TABLESPACE_NAME
    order     by ((a.BYTES-b.BYTES)/a.BYTES) desc
) b
where a.TABLESPACE_NAME = b.TABLESPACE_NAME(+)

 

[2008.05.22] 사용자 정보 구하기 - Query#

select     USERNAME,
    CREATED,
    PROFILE,
    DEFAULT_TABLESPACE,
    TEMPORARY_TABLESPACE
from     dba_users
order     by USERNAME

 

[2008.05.20] - SPS (류재명) 테이블 스페이스별 프리영역을 체크 - Query#

SELECT a.tablespace_name,
a.total "Total(Mb)",
a.total - b.free "Used(Mb)",
nvl(b.free,0) "Free(Mb)",
round((a.total - nvl(b.free,0))*100/total,0) "Used(%)"
from ( select tablespace_name,
round((sum(bytes)/1024/1024),0) as total
from dba_data_files
group by tablespace_name) a,
( select tablespace_name,
round((sum(bytes)/1024/1024),0) as free
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)
order by a.tablespace_name;

[결과]

 

TABLESPACE_NAME Total(Mb) Used(Mb) Free(Mb) Used(%)
CWMLITE 20 9 11 45
DRSYS 20 10 10 50
EXAMPLE 149 148 1 99
INDX 25 0 25 0
ODM 20 9 11 45
SYSTEM 400 397 3 99
TOOLS 10 6 4 60
TS_SPSIM 34   0 100
UNDOTBS1 200 6 194 3
USERS 25 0 25 0
XDB 38 38 0 100

 

 

 

첨부자료#

오라클_개발자를_위한_튜닝_가이드_(9i_기준).pdf

출 처 : http://ebizdocs.springnote.com/pages/1110680


Posted by 김주일

출 처 : http://www.dbguide.net/blog/blog.jsp?urlid=pepera7

3장 노-아카이브와 아카이브 모드

  3-1 백업과 복구의 종류

오라클 데이터베이스에는 4 가지 백업 방법과 8 가지 복구 방법이 제공됩니다.

백업 방법은 다시 두 가지 방법으로 실행할 수 있는데, 첫 번째, 물리적 방법(PHYSICAL MODE)은 오라클 데아터베이스와 관련된 모든 파일들(데이터 파일, 컨트롤 파일, 리두로그 파일, 파라메터 파일)을 운영체계에서 제공하는 복사 명령어(도스-COPY,UNIX-cp, cpio, ufsdump 등)로 다른 물리적 장치에 복사하는 방법을 의미합니다.

두 번째 논리적 방법(LOGICAL MODE)은 오라클 사에서 제공하는 EXPORT 유틸리티를 통해 데이터베이스 내의 테이블, 뷰, 인덱스, PL/SQL 블록 등을 운영체제 상의 파일형태로 복사하는 방법을 의미합니다.

다음은 복구방법 입니다. 복구 방법도 두 가지 모드에서 실행할 수 있습니다. 첫 번째 방법은 노-아키이브 모드(NOARCHIVE MODE))입니다. 백업과 복구 원리에서 알아보았듯이 사용자의 모든 변경 정보는 기본적으로 로그버퍼 영역과 리두로그 파일에 저장되어 데이터베이스 복구 시 사용됩니다. 이러한 환경을 노-아카이브 모드라고 하며 오라클 데이터베이스를 설치하면 기본적으로 노-아카이브 모드로 설정됩니다. 이 모드는 사용자의 모든 변경정보가 리두로그 파일에 백업되기 때문에 리두로그 파일의 크기와 개수가 백업할 수 있는 데이터의 크기를 좌우하게 됩니다. 결론적으로 데이터베이스를 복구해야 할 때 리두로그 파일이 아주 오래 전의 복구 데이터를 가지고 있지 않다면 복구를 할 수 없는 단점을 가지고 있습니다.

두 번째 방법은 아카이브 모드(ARCHIVE MODE)입니다. 이 모드에는 완전복구(COMPLETE RECOVERY)와 불완전 복구(INCOMPLETE RECOVERY) 방법이 있습니다. 모든 복구 데이터를 가지고 있으며 데이터베이스에 문제가 발생했던 시점까지 복구할 수 있는데 이러한 방법을 완전복구(Complete Recovery)라고 하며, 반대로, 복구할 수 있는 데이터가 백업되어 있지 않다면 문제가 생겼던 시점까지 복구할 수 없는데 이러한 방법을 불완전 복구(In-Complete Recovery)라고 합니다. 사용자들은 이러한 다양한 백업과 복구방법을 통해 데이터베이스의 가용성을 높일 수 있습니다.

자~ 그럼 노-아카이브 모드와 아카이브 모드에 대해 자세히 알아 보도록 하겠습니다.


  3-2 노-아카이브 모드

오라클 서버는 사용자들이 입력, 수정, 삭제작업을 수행할 때 마다 발생하는 모든 변경 전 데이터와 변경 후 데이터들을 리두로그 버퍼 영역에 백업하게 됩니다. 이어, LGWR 백그라운드 프로세스는 리두로그 버퍼의 데이터들을 영구히 저장할 수 있는 리두로그 파일로 저장하게 됩니다. 이것이, 오라클 서버가 제공하는 백업 메커니즘입니다.

그런데, 기본적으로 오라클 데이터베이스를 설치하면 3개의 리두로그 파일이 제공됩니다.

최초, LGWR 프로세스는 첫번째 리두로그 파일에 데이터를 저장하며, 이 파일이 모두 사용되면, 두 번째 리두로그 파일로 이동하여 계속적으로 데이터를 저장하게 됩니다.
두 번째 리두로그 파일도 모두 저장되고 나면 세 번째 리두로그 파일에 쓰기 작업을 계속하게 됩니다. 그런데, 세 번째 리두로그 파일도 모두 사용되고 나면, 더 이상 제공되는 리두로그 파일이 없기 때문에 다시 첫 번째 리두로그 파일에 백업 데이터들을 저장하게 됩니다.
이때, 이전에 저장되어 있는 백업 데이터 위에 새로운 백업 데이터들을 저장하기 때문에 이전 백업 데이터들은 모두 유실되는 문제가 발생하게 됩니다.

이미 소개 드린 대로 리두로그 파일의 용도는 오라클 데이터베이스에 장애가 발생하는 경우 백업된 리두로그 파일의 데이터들을 통해 데이터베이스를 복구하기 위한 용도라고 배웠습니다. 그런데, 위 그림처럼, 백업될 충분한 공간이 확보되지 못한 경우에는 결국 이전에 사용된 리두로그 파일을 재사용할 수 밖에 없게 되는 문제가 발생하게 됩니다.

오라클 데이터베이스에서는 이러한 환경을 노-아카이브 모드(No-Archive Mode)라고 하며 기본적으로 오라클 데이터베이스 설치하면 노-아카이브 모드가 기본 환경입니다.

다음은 사용 중인 데이터베이스의 아카이브 모드 상태를 확인하는 방법입니다.

SQL> CONNECT /AS SYSDBA SQL> ARCHIVE LOG LIST Database log mode No Archive Mode ← 노-아카이브 모드 Automatic archival Disabled Archive destination C:\oracle\ora92\database\arch Oldest online log sequence 51 Current log sequence 53

이 결과를 참조했을 때 "Database log mode"의 결과가 "No Archive Mode"임을 확인할 수 있습니다.

SQL> SET LINESIZE 1000 SQL> SELECT GROUP#, SEQUENCE#, ARCHIVED, STATUS FROM V$LOG; GROUP# SEQUENCE# ARC STATUS ---------- ---------- ---- ----------- 1 52 NO INACTIVE 2 53 NO CURRENT 3 51 NO INACTIVE

이 결과를 참조했을 때 ARC 컬럼의 값이 "NO"는 노-아키이브 모드라는 것을 의미합니다.

SQL> SELECT ARCHIVER FROM V$INSTANCE; ARCHIVE ------- STOPPED

이 결과를 참조했을 때 "STOPPED"는 노-아키이브 모드라는 것을 의미합니다.


  3-3 오프라인 백업

오라클 사에서 제공하는 백업방법 중에 가장 대표적인 방법이며 또한 가장 쉽고 간단하게 수행할 수 있는 방법입니다. 하지만, 경우에 따라서는 가장 수행하기 어려운 방법 중에 하나이기도 합니다.

오프라인 백업 방법은 버전에 따라 콜드백업(Cold Backup), 전체백업(Whole Backup)이라고도 부르며 다음과 같은 주요 특징이 있습니다.

1) 반드시, 오라클 서버를 종료(Shutdown) 해야 합니다. [C:\] sqlplus "/as sysdba" SQL> SHUTDOWN SQL> EXIT

여기서, 한가지 주의해야 할 점은 오라클 서버를 종료할 때 반드시 정상적인 종료 옵션(NORMAL, TRANSACTIONAL, IMMEDIATE)을 사용하셔야 합니다. 만약, ABORT와 같은 비정상적인 옵션을 사용하여 데이터베이스를 종료한 후 백업작업을 수행한다면, 이 백업 데이터는 장애 발생 시 사용하지 못하게 됩니다. 즉, 데이터베이스를 복구할 수 없게 되는 치명적인 난관에 부딪히게 됩니다. 그 이유는 간단합니다. ABORT 옵션의 의미는 현재 오라클 서버에 할당된 메모리 공간을 즉시 종료하고 사용 중이던 모든 파일의 상태를 비정상적인 상태로 남긴 후 데이터베이스를 종료 시키기 때문입니다. 예를 들어, WINDOWS 환경에서 어떤 작업을 수행하다가 시스템을 종료해야 한다면 [시작] 버튼을 클릭한 후 [시스템 종료]를 선택합니다. 이것은 정상적인 방법으로 시스템을 종료하는 방법이지요.

하지만, 때로는, 갑작스런 정전으로 인해 미처 정상적인 방법으로 시스템을 종료하지 못하는 경우도 있게 마련입니다. 이런 경우, 다시 시스템을 부팅하게 되면 운영체계는 시스템을 복구모드로 전환하여 갑작스런 다운 시 정상적인 종료를 하지 못한 파일들을 정상적으로 종료 시킨 후 다시 오픈 시켜주는 작업을 수행하게 됩니다.

오라클 데이터베이스 환경에서 SHUTDOWN ABORT 옵션의 의미는 바로 갑작스런 정전으로 인해 데이터베이스가 비정상적으로 종료된 경우와 동일한 상태를 의미하게 됩니다.

결론적으로, 이런 경우, 데이터베이스를 다시 재 시작(Startup)하게 되면 오라클 서버는 내부적으로 복구 작업을 수행하게 됩니다. 이때 , 복구작업을 수행하는 백그라운드 프로세는 SMON(System Monitor) 입니다.

사용자의 순간적인 실수로 백업된 모든 데이터들이 사용되지 못하는 경우가 실제 기업 환경에서 종종 볼 수 있습니다. 이러한 백업 작업을 안전하게 수행할 때는 2번 ~ 3번 확인하신 후 절차에 의해 작업 하셔야 합니다.


2) 오라클 데이터베이스와 관련된 모든 파일 (데이터 파일, 컨트롤 파일, 리두로그 파일, 파라메터 파일)을 같은 시점에 운영체계 명령어(UNIX의 경우는 cp, mv, ufsdump/WINDOW의 경우 copy, move 등)를 사용하여 디스크 또는 테이프 장치에 복사합니다. [C:\] mkdir C:\BACKUP [C:\] CD C:\ORACLE\ORADATA\ORA92 [C:\] COPY *.DBF C:\BACKUP ← 데이터 파일의 백업 [C:\] COPY *.CTL C:\BACKUP ← 컨트롤 파일의 백업 [C:\] COPY *.LOG C:\BACKUP ← 라두로그 파일의 백업 [C:\] COPY C:\ORACLE\ORA92\DATABASE\*.ORA C:\BACKUP ← 파라메터 파일 백업

오라클 사에서 제공하는 오프라인 백업이라는 것은 결론적으로 운영체계 상에서 현재, 오라클 데이터베이스가 구성하고 있는 모든 파일(데이터 파일, 컨트롤 파일, 리두로그 파일, 파라메터 파일)들을 지정한 장치로 복사하는 것을 의미합니다. 이런 경우, 가장 주의해야 할 점은 백업 데이터가 저장될 디스크 또는 테이프 장치에 충분한 사용공간이 존재하는지를 확인하는 것 입니다.

또한, 한가지 더 주의해야 할 점은 반드시 관련된 모든 파일들을 복사하셔야 합니다.
예를 들어, 오늘은 데이터 파일 만 백업하고 내일은 컨트롤 파일을 백업하고 모레에는 리두로그 파일만 백업하는 방법은 정상적인 오프라인 백업 방법이 아니며, 이렇게 백업된 데이터들은 복구 시 사용할 수 없게 됩니다. 그 이유는 다음과 같습니다.

위 그림처럼, ① 현재시점 2001년 6월 30일 SCN(시스템 변경번호)이 100 번인 경우, 데이터베이스를 종료하고 오프라인 백업을 수행하였습니다. 당시, 모든 백업 파일들은 같은 시점의 SCN 100 번인 시점의 데이터들 이었습니다.

② 한 달의 시간이 흘렀고, 현재시점 2001년 7월 31일 SCN이 101 번인 경우, 데이터베이스를 종료한 후 오프라인 백업을 수행하였습니다. 모든 백업 파일들은 SCN이 101번인 시점의 데이터들 이었습니다.

③ 또다시, 한 달의 시간이 흘렀고 현재 시점 2001년 8월 31일에 컨트롤 파일이 저장되어 있는 디스크에 장애가 발생하면서 모든 컨트롤 파일이 사용될 수 없는 치명적인 상태가 되었습니다.

이 경우, 데이터베이스를 복구하기 위해서는 마지막으로 백업되었던 2001년 7월 31일의 백업 데이터를 이용해야 합니다. 위 그림처럼, SCN이 101번을 가지고 있는 7월 31일 백업된 컨트롤 파일을 SCN이 102번을 가지고 있는 현재시점의 데이터베이스 환경으로 재설치를 하고 있습니다. 하지만, 현재 시점의 데이터베이스 환경에서 장애가 발생하지 않은 데이터 파일, 리두로그 파일들은 SCN이 102번 인데, 조금 전에 재 설치된 컨트롤 파일은 SCN이 101번을 나타내는 문제점을 가지게 됩니다.

이 경우, 데이터베이스를 시작(Startup)하게 되면 컨트롤 파일과 다른 파일들이 같은 시점의 데이터들이 아니므로 정상적으로 시작되지 않습니다.

위 그림에서, ③의 경우 데이터베이스의 OPEN 단계는 현재 컨트롤 파일, 리두로그 파일, 데이터 파일들의 SCN을 확인하여 모두 같은 시점의 SCN을 가지고 있는지를 비교하게 되는데 이때, 같은 SCN 번호가 아닌 파일이 발견되면 오라클 데이터베이스에 장애가 발생한 것으로 확인하여 더 이상 OPEN 단계를 수행하지 않으며 다음과 같은 에러 메시지를 출력하게 됩니다.(다음 예제는 USERS01.DBF 데이터 파일의 경우입니다.)

[C:\] sqlplus "/as sysdba" SQL> STARTUP MOUNT; SQL> ALTER DATABASE OPEN; alter database open * ORA-01113: 9 파일이 매체 복구되어야 합니다. ORA-01110: 9 데이터 파일: C:\ORALCE\ORADATA\ORA92\USERS01.DBF

이 에러 메시지의 의미는 USERS01.DBF 데이터 파일의 SCN 번호와 다른 데이터 파일, 컨트롤 파일, 리두로그 파일의 SCN 번호가 일치하지 않는 것을 의미합니다.
즉, 데이터베이스에 장애가 발생한 것이며, USERS01.DBF 파일에 대한 복구가 요구 된다는 의미이기도 합니다.

결론적으로, 데이터베이스에 장애가 발생한 경우 오프라인 백업에 의해 데이터를 복구하기 위해서는 같은 시점에, 같은 SCN 번호를 가진 모든 파일들이 필요하다는 것 입니다.
즉, 오프라인 백업 시 모든 파일을 같은 시점에 백업해야 합니다.


3) 노-아카이브 모드 또는 아카이브 모드에 관계없이 사용할 수 있습니다.

오프라인 백업 방법은 노-아카이브 모드에서 수행할 수 있으며, 앞으로 소개할 아카이브 모드에서도 수행할 수 있는 방법입니다.


다음은 오프라인 백업의 단점에 대해서 알아 보도록 하겠습니다.

1) 주요 특징에서 설명 드린 대로 이 방법은 데이터베이스를 종료(Shutdown)한 후 관련된 모든 파일들을 운영체계 상에서 복사해야 합니다. 문제는 복사해야 할 파일들의 크기가 작은 경우에는 문제되지 않겠지만, 파일들의 크기가 매우 큰 경우(몇 십, 몇 백 기가바이트 또는 테라 바이트)에는 많은 시간이 소요될 수 있기 때문에 그 동안 데이터베이스를 계속적으로 종료상태로 두어야 한다는 것 입니다. 결국, 오프라인인 백업이 수행되는 동안에 어떤 사용자도 데이터베이스에 접속할 수 없으며 데이터를 참조할 수 도 없다는 것 입니다.


2) 오프라인 백업이 수행되기 위해서는 충분한 디스크 공간이 확보되어야 합니다. 현재 데이터 파일과 리구로그 파일의 크기 만큼 사용공간이 확보되어 있어야 만 오프라인 백업을 수행할 수 있기 때문입니다. 만약, 충분한 사용공간이 확보되지 않는다면 백업작업을 효과적으로 진행할 수 없게 됩니다.


사례-1

다음 사례는 노-아카이브 모드에서 사용자의 데이터 파일이 유실되는 경우 장애를 복구하는 방법입니다. 현재, 오라클 서버는 노-아카이브 모드이며 이미 오프라인 백업방법에 의해 데이터베이스 전체 백업이 수행되어 있는 상태입니다.

어느날, 오라클 데이터베이스에 장애가 발생하였고 관련 백업 파일들을 재 설치하려고 합니다. 그런데, 모든 백업 파일들은 정상적으로 재 설치 하였는데, USERS01.DBF 파일은 원래 디스크 경로에 재 설치할 수가 없었습니다. 왜냐하면, 해당 경로의 디스크에 베드 블록(Bad Block)이 발생하여 읽기, 쓰기 작업을 더 이상 수행할 수가 없기 때문입니다.

노-아카이브 모드에서 이런 경우가 발생하면 어떻게 오라클 데이터베이스를 복구할 수 있을까요?


1) 먼저, 오프라인 백업 경로에서 백업된 파일들을 확인한 후 관련 경로에 재설치 하십시오. [C:\]cd c:\backup [C:\] dir System01.dbf …………………. init.ora …………………. [C:\] copy *.ctl c:\oracle\oradata\ora92\*.ctl ← 컨트롤 파일을 재설치 [C:\] copy *.dbf c:\oracle\oradata\ora92\*.dbf ← 데이터 파일을 재설치 [C:\] copy *.log c:\oracle\oradata\ora92\*.log ← 리두로그 파일을 재설치 [C:\] copy *.ora c:\oracle\ora92\database ← 파라메터 파일을 재설치 [C:\] cd c:\oracle\oradata\ora92 [C:\] dir users01.dbf redo01b.log redo02b.log log3b.log ………………. [C:\] move users01.dbf d:\oracle\oradata\users01.dbf → USERS01.DBF 파일은 원래 디스크 경로에 재설치 할 수 없어 일단 다른 디스크 경로에 재설치합니다. [C:\]cd c:\oracle\ora92 [C:\] dir users*
2) USERS01.DBF 파일은 본래 경로에 재 설치하진 못했지만 기타 모든 파일들은 재설치 하였으므로 오라클 서버를 시작해 보십시오. 오픈 단계에서 USERS01.DBF 파일의 본래 경로로부터 관련 파일을 검색해 보지만 파일이 존재하지 않으므로 에러가 발생할 것 입니다. [C:\] sqlplus "/as sysdba" SQL> startup mount SQL> alter database open; alter database open * ORA-01157: cannot identify data file 3 - file not found ORA-01110: data file 3: 'c:\oracle\oradata\ora92\users01.dbf' SQL> select name from v$datafile; NAME ---------------------------------------------------……………………………………. c:\oracle\oradata\ora92\users01.dbf → 오라클 서버는 USERS01.DBF 파일이 처음에 있던 디렉토리 경로에 계속 있는 걸로 알고 있습니다.
3) 이런 경우, 오라클 서버에게 재 설치된 경로가 어디인지 알려주게 되면 해당 경로로부터 관련 파일을 검색하게 됩니다. 오라클 서버의 구조 중에 관련 파일의 경로와 파일명이 저장되어 있는 곳은 컨트롤 파일입니다. 다음 명령문을 수행하게 되면 컨트롤 파일 내의 해당 정보가 변경되며 오픈 단계에서 이 정보를 통해 데이터베이스를 오픈하게 됩니다. SQL> alter database rename file 'c:\oracle\oradata\ora92\users01.dbf' to 'd:\oracle\ora92\users01.dbf ';
4) 다시 오라클 서버를 시작해 보십시오. 오라클 서버는 컨트롤 파일 내에 변경된 경로로부터 USERS01.DBF 파일을 검색하여 정상적으로 오픈하게 됩니다. SQL> alter database open; → 정상적으로 오픈 됩니다.
3-4 아카이브 모드

지금까지, 노-아카이브 모드에 대한 개념과 데이터베이스에 장애가 발생한 경우 노-아카이브 모드에서 복구하는 방법과 절차에 대해서 자세히 알아 보았습니다.

이번에는 아카이브 모드에 대해서 자세히 알아 보도록 하겠습니다.

노-아카이브 모드에서 사용자가 실행한 DML문에 의해 발생하는 모든 데이터(변경 전 데이터와 변경 후 데이터)들은 리두로그 버퍼에 백업된 다음 LGWR 프로세스에 의해 리두로그 파일에 저장됩니다. 또한, 오라클 서버는 데이터베이스 생성 시 기본적으로 3개의 리두로그 파일을 제공하며 LGWR 프로세스는 첫 번째 파일에서 두 번째 파일로, 두 번째 파일에서 세 번째 파일로 쓰기 작업을 계속하게 됩니다. 이때, 마지막 세 번째 리두로그 파일도 모두 쓰여지고 나면 다시 첫 번째 리두로그 파일로 이동하여 쓰기 작업을 계속하게 됩니다.

문제는 첫 번째 리두로그 파일에 저장되어 있던 이전 백업 데이터는 새로운 백업 데이터에 의해 재작성(Rewrite) 되어 삭제된다는 것 입니다.

"1편 오라클 서버구조와 백업/복구원리"에서도 자세히 설명 되었던 대로 리두로그 파일에 백업되는 데이터들은 장애 발생 시 데이터베이스를 복구하기 위한 용도입니다. 그런데. 이 데이터들이 LGWR 프로세스에 의해 재 작성된다는 것은 만약, 데이터베이스에 장애가 발생하는 경우 복구해야 할 백업 데이터들이 유실된다는 것을 의미합니다. 즉, 복구작업을 수행할 수 없게 된다는 것 입니다. 바로 이런 문제점을 개선하기 위해 오라클 사는 아카이브 모드(Archive Mode)라는 백업 메커니즘을 제공합니다.

위 그림에서, LGWR 프로세스가 첫 번째 리두로그 파일에 모든 변경 데이터를 저장한 후, 두 번째 리두로그 파일로 이동하는 순간, ARCH 백그라운드 프로세스는 첫 번째 리두로그 파일을 사용자가 미리 지정한 경로로 복사하게 됩니다.
그리고, 두 번째 파일에서 세 번째 리두로그 파일로 이동할 때도 두 번째 리두로그 파일을 ARCH 프로세스가 지정한 경로로 복사합니다. 마지막으로, 세 번째 리두로그 파일에서 다시 첫 번째 리두로그 파일로 이동할 때도 세 번째 리두로그 파일을 지정한 경로로 복사하게 됩니다. 그런 후, 다시 첫 번째 파일에 백업 데이터들을 재 작성하여 저장하게 됩니다.

아카이브 모드에서는 다시 첫 번째 리두로그 파일에 백업 데이터를 저장하더라도 이전 백업데이터는 이미 사용자가 지정한 경로에 ARCH 프로세스에 의해 복사되어 있기 때문에 데이터베이스에 장애가 발생하더라도 아카이브 파일을 통해 복구작업을 수행할 수 있게 됩니다.

즉, 데이터베이스에서 발생하는 모든 데이터(변경 전 데이터와 변경 후 데이터)들이 ARCH 프로세스에 의해 항상 백업되는 메커니즘을 아카이브 모드(Archive Mode)라고 합니다.
기본적으로 오라클 데이터베이스는 노-아카이브 모드이며 설치 후 아카이브 모드로 전환하는 작업을 수행하셔야 합니다.

3-4-1 아카이브 모드의 환경설정 방법

다음은 데이터베이스를 노아카이브 모드에서 아카이브 모드로 전환하는 방법입니다.


1) 먼저, init.ora 파일에 아카이브 모드와 관련된 파라메터를 설정하십시오.

[LOG_ARCHIVE_START] 파라메터는 ARCH 백그라운드 프로세스에 의해 데이터베이스를 아카이브 모드로 전환하고 로그스위치가 발생하면 자동으로 아카이브를 실행합니다.

[LOG_ARCHIVE_DEST] 파라메터는 리두 로그 파일에 대한 아카이브 파일이 생성될 기본 저장 경로를 의미합니다.

LOG_ARCHIVE_DEST_n] 파라메터는 아카이브 경로를 여러 군데 지정할 때 사용합니다. 최대 10개의 경로지정이 가능합니다

[LOG_ARCHIVE_FORMAT] 파레메터는 [LOG_ARCHIVE_DEST] 또는 [LOG_ARCHIV E_DEST_n] 파라메터에 의해 지정된 경로에 생성될 아카이브 파일의 파일 포맷를 결정합니다.

파일의 포맷은 다음과 같습니다.

[ %s ] 자동으로 생성될 아카이브 파일의 일련번호를 결정해 줍니다.
(예) 1, 2, 3,,,

[ %S ] 자동으로 생성될 아카이브 파일의 일련번호를 0 값으로 채워서 결정해 줍니다.
(예) 00001, 00002, 00003 ……

[ %t ] 데이터베이스가 하나의 인스턴스인지 또는 여러 개의 인스턴스(OPS 환경)로 구성되어 있는지를 구분하여 표시해 줍니다.
(예) 1, 2, 3, ,,,

[ %T ] 데이터베이스가 하나의 인스턴스인지 또는 여러 개의 인스턴스(OPS 환경)로 구성되어 있는지를 0 값으로 채워서 결정해 줍니다.
(예) 001, 002, 003,,,

[ %r ] 오라클 데이터베이스의 백업과 복구작업을 수행하다 보면 백업되었던 시점은 다르지만 동일한 아카이브 파일명이 생성되는 경우가 있습니다. 이런 경우, 현재 생성된 아카이브 파일과 이전에 생성된 아카이브 파일명이 동일하여 아카이브 파일을 저장, 관리하는데 혼돈을 유발시키게 됩니다. 이런 문제를 해결하기 위해 ALTER DATABASE OPEN RESETLOGS 명령어에 의해 데이터베이스가 재 시작된 후 새롭게 생성되는 아카이브 파일의 번호를 초기화할 수 있습니다. 이 구분 값은 오라클 10g 버전부터 제공됩니다.

(예) 0533062963 , 0533062973

2) 데이터베이스를 아카이브 모드로 전환하기 위해서는 데이터베이스를 다시 시작해야 하며 마운트(mount) 단계에서 'ALTER DATABASE ARCHIVELOG' 명령어를 실행해야 합니다.
반대로, 아카이브 모드에서 노아카이브 모드로 전환할 때는 'ALTER DATABASE NOARCHIVELOG' 명령어를 실행하십시오. 아카이브 모드로 전환이 되었으면 데이터베이스를 오픈 시키십시오.



3) 전환작업이 완료되었으면 환경설정이 제대로 되었는지 확인해 보십시오.

다음은 사용 중인 데이터베이스에서 아카이브 모드 상태를 확인하는 방법입니다.

SQL> CONNECT /AS SYSDBA SQL> ARCHIVE LOG LIST Database log mode Archive Mode ← 아카이브 모드 Automatic archival Enabled Archive destination C:\oracle\ora92\database\arch Oldest online log sequence 51 Current log sequence 53

이 결과를 참조했을 때 "Database log mode"의 결과가 "Archive Mode"임을 확인할 수 있습니다.

SQL> SET LINESIZE 1000 SQL> SELECT GROUP#, SEQUENCE#, ARCHIVED, STATUS FROM V$LOG; GROUP# SEQUENCE# ARC STATUS ---------- ---------- ----- ---------------- 1 52 YES INACTIVE 2 53 NO CURRENT 3 51 NO INACTIVE

이 결과를 참조했을 때 ARC 컬럼의 값이 "YES"는 아키이브 모드라는 것을 의미합니다.

SQL> SELECT ARCHIVER FROM V$INSTANCE; ARCHIVE ------- STARTED

이 결과를 참조했을 때 "STARTED"는 아키이브 모드라는 것을 의미합니다.


Posted by 김주일
출 처 : http://www.volkit.com/502

--현재서버이름확인
select @@SERVERNAME

--현재서버이름삭제
exec sp_dropserver 'WIN-W5K3Z4VQN2M\SQL2005'

--변경한서버이름등록
exec sp_addserver @server ='WIN2008\SQL2005', @local ='local'

--SQL Server 재시작필요

--변경된서버이름확인
select @@SERVERNAME

Posted by 김주일
TAG mssql