how to change the character set from WE8MSWIN1252 to AL32UTF8 in oracle rac 11g.

Asian data in AL16UTF16 is more compact than UTF8 and save disk space and have less disk I/O with Asian data. But The maximum lengths for NCHAR and NVARCHAR2 are 1000 and 2000 characters, which is less than the lengths for NCHAR (2000) and NVARCHAR2 (4000) in UTF8.

When we need to use European data... UTF8 better...

Assume I created database with NATIONAL CHARACTER SET AL16UTF16. And need to use UTF8.
Anyway should backup before and Export/[Import] data about NCHAR and NVARCHAR2...
Because We Can not change NATIONAL CHARACTER SET when NCLOB, NCHAR or NVARCHAR2 data exists.

set lines 130
set pages 10
column PROPERTY_NAME format a35
column PROPERTY_VALUE format a35
column DESCRIPTION format a35
select * from database_properties where property_name='NLS_NCHAR_CHARACTERSET'

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
--------------------------------------------------------------
NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set

create table a (b nvarchar2(4000));
create table a (b nvarchar2(4000))
*
ERROR at line 1:
ORA-00910: specified length too long for its datatype

create table a (b nvarchar2(2001));
create table a (b nvarchar2(2001))
*
ERROR at line 1:
ORA-00910: specified length too long for its datatype

create table a (b nvarchar2(2000));

Table created.

Begin Step by Step...

- SHUTDOWN IMMEDIATE
- STARTUP MOUNT
- ALTER SYSTEM ENABLE RESTRICTED SESSION

Perhaps should:
alter system set job_queue_processes=0
alter system set aq_tm_processes=0

- ALTER DATABASE OPEN
- ALTER DATABASE NATIONAL CHARACTER SET UTF8

If =>
ALTER DATABASE NATIONAL CHARACTER SET UTF8
*
ERROR at line 1:
ORA-12717: Cannot issue ALTER DATABASE NATIONAL CHARACTER SET when NCLOB, NCHAR or NVARCHAR2 data exists

Check alert log and remove data from list (don't forget backup before), Example:

Mon Feb 09 11:52:45 2009
ALTER DATABASE NATIONAL CHARACTER SET UTF8
PM.PRINT_MEDIA (AD_FLTEXTN) - NCLOB populated
ORA-12717 signalled during: ALTER DATABASE NATIONAL CHARACTER SET UTF8....

remark:
ORA-12717: Cannot issue ALTER DATABASE NATIONAL CHARACTER SET when NCLOB, NCHAR or NVARCHAR2 data exists
Cause: NCLOB, NCHAR or NVARCHAR2 data changed the representation to Unicode when converting to a multibyte character set and must be migrated.
Action: Remove NCLOB, NCHAR or NVARCHAR2 data as listed in the alert file. The above type data can be migrated by methods such as import/export.

- SHUTDOWN IMMEDIATE
- STARTUP

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
---------------------------------------------------------------------
NLS_NCHAR_CHARACTERSET UTF8 NCHAR Character set

create table a (b nvarchar2(4000));

Table created.

2 Oracle and MySQL Compared

This chapter compares the MySQL database and the Oracle database. It includes the following sections:

• Database Security

• Schema Migration

• Data Types

• Data Storage Concepts

Data Pump (Master Table)

      1) External Table 확장
      2) expdp, impdp
      * DB Link를 이용한 Network Mode (NETWORK_LINK 옵션)
      * Directory Object가 반드시 필요(DIRECTORY 옵션)
      * expdp 옵션에 의한 객체 단위 포함/제외 가능(EXCLUDE/INCLUDE 옵션 및 향상된 QUERY 옵션)
      * 제한적이긴 하지만 Parallel 가능(PARALLEL 옵션)
      * Master Table에 의한 중지 작업 재시작 및 작업 변경(추가 및 삭제)이 가능
      * REMAP_SCHEMA, REMAP_DATAFILE, REMAP_TABLESPACE 등 새로운 옵션

## 실습 01 ######################################################################

sqlplus system/oracle  또는 conn system/oracle

host mkdir C:\oracle10g\EXP_PUMP1
host mkdir C:\oracle10g\EXP_PUMP2

CREATE OR REPLACE DIRECTORY exp_pump_dir1 AS 'C:\oracle10g\EXP_PUMP1' ;
CREATE OR REPLACE DIRECTORY exp_pump_dir2 AS 'C:\oracle10g\EXP_PUMP2' ;

grant read, write on directory exp_pump_dir1 to public ;
grant read, write on directory exp_pump_dir2 to public ;

exit

cd c:\oracle10g\exp_pump

1)
expdp hr/hr DUMPFILE=exp_pump_dir1:exp_hr03.dmp LOGFILE=exp_pump_dir2:exp_hr03.log

expdp hr/hr DUMPFILE=exp_pump_dir:expdp_hr01.dmp NOLOGFILE=y

2)                                                                 
expdp hr/hr DIRECTORY=exp_pump_dir1 DUMPFILE=exp_hr02.dmp LOGFILE=exp_pump_dir2:exp_hr02.log

3)
set DATA_PUMP_DIR=exp_pump_dir

expdp system/oracle DUMPFILE=exp_hr_emp01.dmp NOLOGFILE=y tables=hr.employees
expdp system/oracle DUMPFILE=exp_hr05.dmp LOGFILE=exp_hr04.log SCHEMAS=hr

## 실습 02 expdp impdp 기본 옵션 ################################################

(실습 준비)

mkdir c:\oracle10g
mkdir c:\oracle10g\dptest

cd c:\oracle10g\dptest
sqlplus /nolog
conn sys/oracle as sysdba

create directory dp_test_dir as 'c:\oracle10g\dptest' ;

grant read, write on directory dp_test_dir to hr ;

conn hr/hr

create table emps03_02 tablespace example
as
  select * from hr.employees ;

insert into emps03_02 select * from emps03_02 ; --> 12회 정도 실행

commit ;

select sum(bytes)/1024/1024 AS "SIZE_MB" from user_segments
where  segment_name = 'EMPS03_02' ;

conn system/oracle

alter system checkpoint ;

exec dbms_stats.gather_table_stats('HR','EMPS03_02' -
     ,estimate_percent => dbms_stats.auto_sample_size -
     ,method_opt => 'FOR ALL COLUMNS SIZE AUTO' , cascade => TRUE) ;

select sum(bytes)/1024/1024 AS "SIZE_MB" from dba_segments
where  owner='HR' and segment_name = 'EMPS03_02' ;

exit

------------------------------------------------------------------------
## exp_test01_par01.txt 파일 작성

userid=hr/hr
directory=dp_test_dir
job_name=exptest01
logfile=exp_test01.log
filesize=2M
tables=emps03_02
estimate=statistics
estimate_only=y           <- 실제로 익스포트 안받고 확인받하는거야? 사용공간 분석이야

- 아래 애는 절대경로를 사용한다..
expdp parfile=exp_test01_par01.txt

------------------------------------------------------------------------
## exp_test01_par02.txt 파일 작성

userid=hr/hr
directory=dp_test_dir
job_name=exptest0102
logfile=exp_test0102.log
filesize=5M
tables=emps03_02
dumpfile=exp_test02_%U.dmp

expdp parfile=exp_test01_par02.txt

------------------------------------------------------------------------
## imp_test01_par03.txt 파일 작성

userid=hr/hr
directory=dp_test_dir
job_name=imptest0103
logfile=imp_test0103.log
tables=emps03_02
dumpfile=exp_test02_%U.dmp
table_exists_action=append

sqlplus system/oracle
truncate table hr.emps03_02 ;
exit

impdp parfile=imp_test01_par03.txt

## exp_test01_par04.txt  파일 작성

userid=hr/hr
directory=dp_test_dir
job_name=exptest0104
logfile=exp_test0104.log
filesize=5M
dumpfile=exp_test04_%U.dmp
EXCLUDE=VIEW
EXCLUDE=PACKAGE
EXCLUDE=INDEX:"LIKE 'EMP_%'"
EXCLUDE=TABLE:"LIKE 'EMPS03%'"
EXCLUDE=TABLE:"LIKE 'JOB%'"
QUERY=HR.employees:"WHERE department_id IN (10,20) and salary<1600"

expdp parfile=exp_test01_par04.txt

(참고)
CONTENT=METADATA_ONLY
CONTENT=DATA_ONLY
CONTENT=ALL

## 실습 03 Attach & JOB STOP & JOB RESTART ######################################

(준비작업)

sqlplus hr/hr

select sum(bytes)/1024/1024 AS "SIZE_MB" from user_segments
where  segment_name = 'EMPS03_02' ;

insert into emps03_02 select * from emps03_02 ; <--2회 실행

commit ;

conn system/oracle

alter system checkpoint ;

exit

## 작업내용 확인(현재 진행중이거나, 에러 후 중단된 작업내역을 확인가능.)

sqlplus /nolog
conn sys/oracle as sysdba

grant select on dba_datapump_jobs to hr ; <--조회 권한할당

conn hr/hr

desc dba_datapump_jobs

col OWNER_NAME format a10
col JOB_NAME format a15
col OPERATION format a15
col JOB_MODE format a10
col STATE format a15
set linesize 120

select * from dba_datapump_jobs ;

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

(SESSION 1)--------------------------------------------------------

## exp_test02_par01.txt 파일 작성

userid=hr/hr
directory=dp_test_dir
job_name=exptest0201
logfile=exp_test0201.log
filesize=30M
tables=emps03_02
dumpfile=exp_test0201_%U.dmp

sqlplus hr/hr

(SESSION 2)--------------------------------------------------------
cd c:\oracle10g\dptest
expdp parfile=exp_test02_par01.txt

(SESSION 1) 작업확인-----------------------------------------------

select * from dba_datapump_jobs ;  <--JOB_NAME 필드 확인

(SESSION 3) <--현재 작업중인 export에 연결.------------------------

expdp hr/hr attach=exptest0201  <--job이름

Export>

(SESSION 1)--------------------------------------------------------

select * from dba_datapump_jobs ;  <--ATTACHED_SESSIONS 수가 증가

(SESSION 3) 현재 작업 중단-----------------------------------------

Export> stop_job

yes 입력 --> 시간걸림 그동안에 다음 수행

(SESSION 1)

select * from dba_datapump_jobs ;  <--ATTACHED_SESSIONS 수가 감소

그 동안에 (SESSION 3) 중단 작업 완료
   --> 원래 SESSION 2도 중단됨

(SESSION 2) 강제 종료

(SESSION 1)

select * from dba_datapump_jobs ;  <-- NOT RUNNING

===================================================================
## 중단작업에 attach 및 작업 재시작 실습
===================================================================

(SESSION 3)

expdp hr/hr attach=exptest0201  <--job이름

Export>

(SESSION 1)

select * from dba_datapump_jobs ;

(SESSION 3) 중단 작업 재개

Export> start_job

Export> status=10  <-- 매 10초간 상태 확인

Export> continue_client <-- Session 2에서 시작된 작업인데 클라이언트가 종료되었으므로
                            Session 3에서 진행 결과를 받도록 설정.

작업완료 후에

(SESSION 1)

select * from dba_datapump_jobs ;

DBA_DATAPUMP_JOBS

>DBA_DATAPUMP_JOBS identifies all active Data Pump jobs in the database, regardless of their state, on an instance (or on all instances for Real Application Clusters). It also show all Data Pump master tables not currently associated with an active job.

Related View

>USER_DATAPUMP_JOBS displays the Data Pump jobs owned by the current user. This view does not display the OWNER_NAME column.

Script.zip

오라클 데이터베이스를 생성할 때 보통은 DBCA를 이용한다.
그러나 DBCA만 사용해 DB를 생성하다보면, DBCA를 사용할 수 없는 상황에는 당황하게 된다.

SELECT 'EXEC DBMS_UTILITY.compile_schema(schema => '||owner||');'

FROM   dba_objects

WHERE  status = 'INVALID'

ORDER BY owner, object_type, object_name

/

set heading off

set feedback off

set linesize 80

select 'alter view "' || object_name || '" compile;'

from user_objects

where object_type = 'VIEW'

and status = 'INVALID'

/

alter view "EMP_PUBLIC_DATA" compile;

alter view "VIEW_T" compile;

alter view "DEPT_EMP_VIEW" compile;

alter view "DEPARTMENT_10" compile;

set heading on

set feedback on

select 'show errors view ' || object_name

from user_objects

where object_type = 'VIEW'

and status = 'INVALID'

/

'SHOWERRORSVIEW'||OBJECT_NAME

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

show errors view EMP_PUBLIC_DATA

show errors view VIEW_T

show errors view DEPT_EMP_VIEW

show errors view DEPARTMENT_10

4 rows selected.

아카이브 로그 모드(Archive Log Mode)란?

  우리가 오라클데이터베이스에 접속을해서 DML이나 DDL등의 명령어로 작업을 수행하면, 모든 작업의 기록이 리두로그 파일에 저장이 된다.

  작업의 양이 많아지면 리두로그파일에 기록하는 내용도 굉장히 많아지게 되겠죠. 그렇게 되면 데이터를 기록하기 위해서 리두로그파일을 늘려야 하는 일이 발생을 한다.

  그런데 오라클 리두로그파일은 계속 증가하는 것이 아니라 몇 개의 리두로그 파일을 만들어 놓고 번갈아 가면서 기록하는 구조로 되어 있다.

  이렇게 번갈아 가면서 기록을 하게 되면 새로운작업의 내용이 예전의 작업내용을 덮어쓰므로 예전의 작업한 내용을 잃게 된다는 단점이 있다. 그래서 예전의 작업한 내용에 데이터 손실이 발생하면 복구하기 어렵다는 단점이 있다.

  이런 단점을 해결하기 위한 방법이 리두로그파일의 내용을 다른 디렉토리에 자동으로 복사해서 저장하도록 운영하는 방법이다. 이렇게 운영하는 방법을 아카이브 로그 모드(Archive Log Mode)라고 한다.

  오라클데이터베이스는 기본적으로 No Archive Log Mode 이고, Archive Log Mode로 운영하기 위해서는 따로 설정을 해주어야 한다.

PFILE을 수정하여 데이타베이스를 archivelog mode로 설정하기

  NO ARCHIVE LOG 상태의 데이터베이스를 ARCHIVE LOG 모드 상태로 변경하기 위해서는 다음과 같은 순서로 작업해야 한다.

1) INIT.ORA 파라미터 파일을 수정한다.

2) 데이터베이스 인스턴스를 종료(SHUTDOWN)한다.

3) 데이터베이스 인스턴스를 MOUNT한다.(OPEN하지 않습니다)

4) 데이터베이스를 ARCHIVE LOG 모드로 변경한다.

5) 데이터베이스 인스턴스를 OPEN한다.

1) INIT.ORA파일의 parameter 수정

  INIT.ORA 파일에서 아래 부분을 수정하고, 주석(#)을 제거하고 저장합니다.

# 아카이브 프로세스를 오라클 시작과 함께 실행하도록 설정

# log switch 발생시 자동으로 archive를 수행 합니다

LOG_ARCHIVE_START = TRUE

# 아카이브 로그 파일을 저장할 디렉토리 설정

LOG_ARCHIVE_DEST = "C:\oracle\ora92\database\archive"  

# 아카이브 로그 파일의 이름 설정

LOG_ARCHIVE_FORMAT = %S.ARC

  ※ LOG_ARCHIVE_FORMAT 옵션

  - %S : redo 로그 시퀀스 번호를 표시하여 자동으로 왼쪽이 0으로 채워져 파일 이름 길이를 일정하게 만든다.

  - %s : redo 로그 시퀀스 번호를 표시하고, 파일 이름 길이를 일정하게 맞추지 않는다.

  - %T : redo 스레드 넘버를 표시하며, 자동으로 왼쪽이 0으로 채워져 파일 이름 길이를 일정하게 만든다.

  - %t : redo 스레드 넘버를 표시하며, 파일 이름 길이를 일정하게 맞추지 않는다.

2) 데이터베이스 인스턴스를 종료

-- SQLPLUS 실행

SQLPLUS /nolog

-- SYSDBA 권한으로 접속 합니다. 

SQL>CONN SYS/MANAGER AS SYSDBA

SQL> SHUTDOWN IMMEDIATE

데이터베이스가 닫혔습니다.

데이터베이스가 마운트 해제되었습니다.

ORACLE 인스턴스가 종료되었습니다.

3) 데이터베이스 인스턴스를 MOUNT

SQL> STARTUP MOUNT pfile=C:\oracle\ora92\database\INITORA9I.ORA

데이터베이스가 마운트되었습니다.

4) DATABASE를 ARCHIVE LOG MODE로 전환.

SQL> ALTER DATABASE ARCHIVELOG; 

데이타베이스가 변경되었습니다.

5) DATABASE OPEN

SQL> ALTER DATABASE OPEN; 

6) ARCHIVE LOG MODE가 정상적으로 설정되어 있는지 확인한다.

SQL> ARCHIVE LOG LIST

데이터베이스 로그 모드              아카이브 모드

자동 아카이브             사용

아카이브 대상            C:\oracle\ora92\database\archive

가장 오래된 온라인 로그 순서     16

아카이브할 다음 로그   18

현재 로그 순서           18

7) 강제로 로그 스위치를 발생시켜서 아카이브 로그 파일이 저장되는지 확인

  C:\oracle\ora92\database\archive 디렉토리에 파일이 생성되었는지 확인 한다.

SQL> ALTER SYSTEM SWITCH LOGFILE;

시스템이 변경되었습니다.     

ARCHIVELOG MODE에서 NO ARCHIVELOG MODE로 전환하기

  먼저, 위에서 setting 했던 INIT.ORA 파일에서 설정했던 부분을 (#)으로 주석처리 한다.

#LOG_ARCHIVE_START = TRUE

#LOG_ARCHIVE_DEST = "C:\oracle\ora92\database\archive"  

#LOG_ARCHIVE_FORMAT = %S.ARC

-- 데이터베이스 종료

SQL> SHUTDOWN IMMEDIATE

-- 데이터베이스 인스턴스를  mount 

SQL> STARTUP MOUNT pfile=C:\oracle\ora92\database\INITORA9I.ORA

-- 데이터베이스를  no archive log mode로 전환.

SQL> ALTER DATABASE NOARCHIVELOG;

--  database open

SQL> ALTER DATABASE OPEN; 

-- 아카이브 로그 모드 상태 확인

SQL> ARCHIVE LOG LIST 

데이터베이스 로그 모드              아카이브 모드가 아님

자동 아카이브             사용 안함

아카이브 대상            C:\oracle\ora92\RDBMS

가장 오래된 온라인 로그 순서     17

현재 로그 순서           19    

SPFILE(서버 파라미터 파일)을 수정하여 데이타베이스를 ARCHIVELOG MODE로 설정

  Oracle9i 이상의 경우 서버 파라미터 파일을 사용 할 경우 아래와 같은 과정을 거쳐서 아카이브 로그모드로 변경해야 한다.

1) 파라미터 설정

-- sqlplus 실행

SQLPLUS /nolog

-- SYSDBA 권한으로 접속 합니다. 

SQL> CONN / AS SYSDBA

-- LOG_ARCHIVE_START 파라미터 변경

SQL> ALTER SYSTEM SET 

     LOG_ARCHIVE_START=TRUE SCOPE=SPFILE;

-- LOG_ARCHIVE_DEST 파라미터 변경

SQL> ALTER SYSTEM SET 

     LOG_ARCHIVE_DEST='C:\oracle\ora92\database\archive' 

     SCOPE=SPFILE;

-- LOG_ARCHIVE_FORMAT 파라미터 변경

SQL> ALTER SYSTEM SET 

     LOG_ARCHIVE_FORMAT='%S.ARC' SCOPE=SPFILE;

2) DB Shutdown 

SQL> SHUTDOWN IMMEDIATE

3) Mount 상태로 Startup

SQL> STARTUP MOUNT

4) 아카이브 로그 모드 활성화

SQL>ALTER DATABASE ARCHIVELOG;

5) 데이타베이스 오픈

SQL> ALTER DATABASE OPEN; 

6) 아카이브 로그 모드가 정상적으로 설정되어 있는지 확인한다.

SQL> ARCHIVE LOG LIST;

데이터베이스 로그 모드              아카이브 모드

자동 아카이브             사용

아카이브 대상            C:\oracle\ora92\database\archive

가장 오래된 온라인 로그 순서     17

아카이브할 다음 로그   19

현재 로그 순서           19    

SPFILE(서버 파라미터 파일)에서 NO ARCHIVE LOG모드로 전환하기

1) 자동 아카이브 모드를 false로 변경 한.

SQL> ALTER SYSTEM SET 

     LOG_ARCHIVE_START=FALSE SCOPE=SPFILE;

2)DB shutdown 

SQL> SHUTDOWN IMMEDIATE

3) mount 상태로 startup

SQL> STARTUP MOUNT

4) 데이터베이스를  no archive log mode로 전환.

SQL> ALTER DATABASE NOARCHIVELOG;

5) 데이타베이스 오픈

SQL> ALTER DATABASE OPEN; 

6) 아카이브 로그 모드 상태 확인

SQL> ARCHIVE LOG LIST;

데이터베이스 로그 모드              아카이브 모드가 아님

자동 아카이브             사용 안함

아카이브 대상            C:\oracle\ora92\database\archive

가장 오래된 온라인 로그 순서     17

현재 로그 순서           19    

10g에서는 

ALTER SYSTEM SET LOG_ARCHIVE_START=TRUE, FALSE SCOPE=SPFILE; 

제외하고 archive mode or noarchive mode 로 설정하시면 됩니다. 

그렇지 않고 

ALTER SYSTEM SET LOG_ARCHIVE_START=TRUE, FALSE를 실행한다면 아래와 같은 에러가 발생합니다. 

"ORA-32004: obsolete and/or deprecated parameter(s) specified"

SQL> archive log list 

Database log mode Archive Mode 

Automatic archival Enabled 

Archive destination /u02/oracle/DB/arch/arch 

Oldest online log sequence 4 

Next log sequence to archive 6 

Current log sequence 6 

SQL> show parameters log_archive_dest_1 

NAME TYPE VALUE 

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

log_archive_dest_1 string LOCATION=/u02/oracle/DB/arch/a 

rch MANDATORY REOPEN 

log_archive_dest_10 string 

SQL> alter system set log_archive_dest_1='LOCATION=/u02/oracle/DB/arch2/arch MANDATORY REOPEN' scope=both ; 

System altered. 

SQL> !ls -lrt /u02/oracle/DB/arch2/ 

total 0 

SQL> alter system switch logfile ; 

System altered. 

SQL> !ls -lrt /u02/oracle/DB/arch2/ 

total 17356 

-rw-r----- 1 oracle oinstall 17746432 Dec 20 11:16 archTLO_1_6_735939596.dbf 

오라클에서 현재 사용하는 데이터 베이스에서

프로시저, 함수, USER_SOURCE 뷰를 보기 입니다.

SELECT * FROM USER_SOURCE;

TYPE : "TYPE", "TYPE BODY", "PROCEDURE", "FUNCTION", "PACKAGE", "PACKAGE BODY" or "JAVA SOURCE"

Recompiling Invalid Schema Objects

Operations such as upgrades, patches and DDL changes can invalidate schema objects. Provided these changes don't cause compilation failures the objects will be revalidated by on-demand automatic recompilation, but this can take an unacceptable time to complete, especially where complex dependencies are present. For this reason it makes sense to recompile invalid objects in advance of user calls. It also allows you to identify if any changes have broken your code base. This article presents several methods for recompiling invalid schema objects.

Identifying Invalid Objects

The Manual Approach

Custom Script

DBMS_UTILITY.compile_schema

UTL_RECOMP

utlrp.sql and utlprp.sql

Identifying Invalid Objects

The DBA_OBJECTS view can be used to identify invalid objects using the following query.

COLUMN object_name FORMAT A30

SELECT owner,

       object_type,

       object_name,

       status

FROM   dba_objects

WHERE  status = 'INVALID'

ORDER BY owner, object_type, object_name;

With this information you can decide which of the following recompilation methods is suitable for you.

The Manual Approach

For small numbers of objects you may decide that a manual recompilation is sufficient. The following example shows the compile syntax for several object types.

ALTER PACKAGE my_package COMPILE;

ALTER PACKAGE my_package COMPILE BODY;

ALTER PROCEDURE my_procedure COMPILE;

ALTER FUNCTION my_function COMPILE;

ALTER TRIGGER my_trigger COMPILE;

ALTER VIEW my_view COMPILE;

Notice that the package body is compiled in the same way as the package specification, with the addition of the word "BODY" at the end of the command.

An alternative approach is to use the DBMS_DDL package to perform the recompilations.

EXEC DBMS_DDL.alter_compile('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE');

EXEC DBMS_DDL.alter_compile('PACKAGE BODY', 'MY_SCHEMA', 'MY_PACKAGE');

EXEC DBMS_DDL.alter_compile('PROCEDURE', 'MY_SCHEMA', 'MY_PROCEDURE');

EXEC DBMS_DDL.alter_compile('FUNCTION', 'MY_SCHEMA', 'MY_FUNCTION');

EXEC DBMS_DDL.alter_compile('TRIGGER', 'MY_SCHEMA', 'MY_TRIGGER');

This method is limited to PL/SQL objects, so it is not applicable for views.

Custom Script

In some situations you may have to compile many invalid objects in one go. One approach is to write a custom script to identify and compile the invalid objects. The following example identifies and recompiles invalid packages and package bodies.

SET SERVEROUTPUT ON SIZE 1000000

BEGIN

  FOR cur_rec IN (SELECT owner,

                         object_name,

                         object_type,

                         DECODE(object_type, 'PACKAGE', 1,

                                             'PACKAGE BODY', 2, 2) AS recompile_order

                  FROM   dba_objects

                  WHERE  object_type IN ('PACKAGE', 'PACKAGE BODY')

                  AND    status != 'VALID'

                  ORDER BY 4)

  LOOP

    BEGIN

      IF cur_rec.object_type = 'PACKAGE' THEN

        EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type || 

            ' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';

      ElSE

        EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.owner || 

            '"."' || cur_rec.object_name || '" COMPILE BODY';

      END IF;

    EXCEPTION

      WHEN OTHERS THEN

        DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner || 

                             ' : ' || cur_rec.object_name);

    END;

  END LOOP;

END;

/

This approach is fine if you have a specific task in mind, but be aware that you may end up compiling some objects multiple times depending on the order they are compiled in. It is probably a better idea to use one of the methods provided by Oracle since they take the code dependencies into account.

DBMS_UTILITY.compile_schema

The COMPILE_SCHEMA procedure in the DBMS_UTILITY package compiles all procedures, functions, packages, and triggers in the specified schema. The example below shows how it is called from SQL*Plus.

EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT');

UTL_RECOMP

The UTL_RECOMP package contains two procedures used to recompile invalid objects. As the names suggest, the RECOMP_SERIAL procedure recompiles all the invalid objects one at a time, while the RECOMP_PARALLEL procedure performs the same task in parallel using the specified number of threads. Their definitions are listed below.

 PROCEDURE RECOMP_SERIAL(

   schema   IN   VARCHAR2    DEFAULT NULL,

   flags    IN   PLS_INTEGER DEFAULT 0);

PROCEDURE RECOMP_PARALLEL(

   threads  IN   PLS_INTEGER DEFAULT NULL,

   schema   IN   VARCHAR2    DEFAULT NULL,

   flags    IN   PLS_INTEGER DEFAULT 0);

The usage notes for the parameters are listed below.

schema - The schema whose invalid objects are to be recompiled. If NULL all invalid objects in the database are recompiled.

threads - The number of threads used in a parallel operation. If NULL the value of the "job_queue_processes" parameter is used. Matching the number of available CPUs is generally a good starting point for this value.

flags - Used for internal diagnostics and testing only.

The following examples show how these procedures are used.

-- Schema level.

EXEC UTL_RECOMP.recomp_serial('SCOTT');

EXEC UTL_RECOMP.recomp_parallel(4, 'SCOTT');

-- Database level.

EXEC UTL_RECOMP.recomp_serial();

EXEC UTL_RECOMP.recomp_parallel(4);

-- Using job_queue_processes value.

EXEC UTL_RECOMP.recomp_parallel();

EXEC UTL_RECOMP.recomp_parallel(NULL, 'SCOTT');

There are a number of restrictions associated with the use of this package including:

Parallel execution is perfomed using the job queue. All existing jobs are marked as disabled until the operation is complete.

The package must be run from SQL*Plus as the SYS user, or another user with SYSDBA.

The package expects the STANDARD, DBMS_STANDARD, DBMS_JOB and DBMS_RANDOM to be present and valid.

Runnig DDL operations at the same time as this package may result in deadlocks.

utlrp.sql and utlprp.sql

The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database. They are typically run after major database changes such as upgrades or patches. They are located in the $ORACLE_HOME/rdbms/admin directory and provide a wrapper on the UTL_RECOMP package. The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of "0". The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows.

0 - The level of parallelism is derived based on the CPU_COUNT parameter.

1 - The recompilation is run serially, one object at a time.

N - The recompilation is run in parallel with "N" number of threads.

Both scripts must be run as the SYS user, or another user with SYSDBA, to work correctly.

For more information see:

DBMS_UTILITY.compile_schema

UTL_RECOMP

1. SQL> Shutdown immediate

2. $ cp /oracle/maxprd1/system01.dbf to /oracle/maxprd1/maxlive/system01.dbf

3. SQL> startup mount

4. SQL> alter database rename file '/oracle/maxprd1/system01.dbf' to '/oracle/maxprd1/maxlive/system01.dbf';

5. SQL> alter database open.

6. $ rm /oracle/maxprd1/system01.dbf

오라클 COMMAND 창에서 

sql> show parameter undo; 

를 쳐보면 

undo_management 
undo_retention 
undo_tablespace 의 속성들에 대한 정보가 나오게 된다. 

그 중 undo_retention 은 delete, update 후에 ROLLBACK이 아닌 COMMIT을 하였을때 부터 

속성값의 초 까지는 오라클에서 임시로 저장을 하게끔 되어있다. 

DEFAULT 속성값은 '900' 으로 900/60초 = 15분 

COMMIT 후 15분 안에는 데이터를 복구 할 수 있게 된다. 

그 시간을 늘리거나 줄이려면 

'alter system set undo_retention = 1500 ; '          --1500초(25분) 

이렇게 늘릴수가 있다. 

지금까지는 복구를 위한 셋팅 방법이었으며, 이제는 복구 방법을 알아보자. 

복구를 하는 방법은 

DELETE FROM TEST WHERE USER_ID = 'ITDI'; 

COMMIT; 

위와 같은 방법으로 USER_ID = 'ITDI' 의 데이터를 TEST 테이블에서 삭제를 하고 COMMIT을 하였을 경우, 

SELECT * 
  FROM TEST 
 AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '15' MINUTE) 
WHERE USER_ID = 'ITDI' ; 

이렇게 하면 삭제를 한지 15분안의 데이터를 찾아서 조회를 할수 있다. 

복구하는 방법은 CTAS를 써서 임시테이블에 넣어서 차차 복구를 하여도 되고 

다이렉트로 

INSERT INTO TEST 
SELECT * 
  FROM TEST 
 AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '15' MINUTE) 
WHERE USER_ID = 'ITDI' ; 

이렇게 처리를 하여도 된다. 

일단 서버를 셋팅을 하게되고 중요한 운영DB일 경우에는 이와같은 방법으로 

데이타의 삭제를 예방할 수 있다. 

DMP 백업이나 ARCHIVE 백업등의 처리 또한 백업의 종류이기는 하나, 

위와 같은 방법이 백업복구의 대처 능력이 다른 복구작업보다 더 좋다고 생각한다.

As the listener.log file grows, the DBA will want to either remove or rename this log file. If you have ever tried to remove or rename the listener.log file on Windows while the TNS listener process was running you will quickly notice that Windows holds a lock on this file and returns an error: 

C:\> del C:\oracle\ora92\network\log\listener.log 
C:\oracle\ora92\network\log\listener.log 
The process cannot access the file because it is being used by another process.Most DBAs simply stop the TNS listener process, rename (or remove) the file, then restart the TNS listener process. This can, however, cause potention connection errors for users that are attempting to connect while the listener process is down. 
Even under UNIX, problems exist when attempting to rename the listener.log file while the TNS listener process is running. Just like under Windows, the Oracle TNS listener process holds an open handle to the file. Under UNIX, you CAN remove the file, but Oracle will not re-create the file when it attempts to write to it again. The TNS listener will need to be stopped and restarted in order for it to create the new listener.log file. 

Well, here is a solution for renaming (or removing) the listener.log file without having to stop and start the TNS listener process under either Windows or UNIX: 


Windows 
C:\> cd \oracle\ora92\network\log 
C:\oracle\ora92\network\log> lsnrctl set log_status off 
C:\oracle\ora92\network\log> rename listener.log listener.old 
C:\oracle\ora92\network\log> lsnrctl set log_status on 



UNIX 
% cd /u01/app/oracle/product/9.2.0/network/log 
% lsnrctl set log_status off 
% mv listener.log listener.old 
% lsnrctl set log_status on

한 데이터베이스에서 네트워크상의 다른 데이터베이스에 접속하기 위한 설정을 해주는 오라클 객체 입니다. 
DB Link 설정을 하면 한 DB에서 다른 DB의 내용을 볼 수 있게 되는 것이죠. 

쉽게 말하자면 'A' DB에서 'B' DB로 DB Link 를 걸고자 한다면 우선 'A' DB의 TNSNAMES.ORA파일에 'B' DB 접속정보를 추가해 줍니다. 

물론 그 전에 두 DB 간에 1521(기본 포트)가 열려 있어야 합니다. 
접속이 가능한지는 한 DB에서 다른 DB로 > telnet IP PORT 로 접속 테스트를 해주시면 됩니다. 
ex> telnet 192.168.0.1 1521 

실제 DB Link를 걸기 위해서는 'B' DB의 서비스 네임과 link걸 table의 user/passwd를 알아야 합니다. 
그리고 'A' DB에서 DB Link를 걸기 위해선 권한이 필요합니다. 

'A' DB 의 System 계정으로 들어가서 

 GRANT CREATE PUBLIC DATABASE LINK, DROP PUBLIC DATABASE LINK TO A DB_ID; 

 'A' DB에 권한을 주고, 아래와 같이 DB Link를 생성합니다. 

CREATE DATABASE LINK TEST_LINK CONNECT TO B DB_ID IDENTIFIED BY PASSWORD USING 'B DB' 

      TEST_LINK  -> Link 이름 
      B DB_ID -> B DB 접속 아이디 
      PASSWORD  -> B DB 접속 패스워드 
    'B DB'  -> B DB 의 TNSNAMES.ORA에 등록된 Name 

  
마지막으로 아래의 명령어로 DB Link 가 걸렸는지 확인해 줍니다. 

SELECT * FROM Table@TEST_LINK 

  

* 모든 DB Link 를 확인하는 SQL 
 select * from all_db_links; 

* DB Link 삭제 
drop database link TEST_LINK 

  

  

CREATE  DATABASE LINK 원하는 링크이름 

 CONNECT TO 계정 IDENTIFIED BY 비번 
 USING '연결할 DB의 TNS'; 


이래저래 안걸리면 직접넣기 

CREATE DATABASE LINK 링크이름 
 CONNECT TO 아이디 IDENTIFIED BY 비번 
 USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=호스트명)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=SID)))';  

1. DELETE 

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

  - DML(Data Manuplation Language) 
  - 사용 : DELETE FROM 테이블명 WHERE 조건; 

  - 데이터의 일부 또는 전부를 삭제 할 수 있음 
  - 삭제할 데이터들은 디스크에서 메모리로 로딩(loading)한 후 작업 
  - Disk I/O가 많이 발생하므로 메모리(Data buffer cache)에 있는 데이터를 삭제하기 전에 
    삭제할 데이터에 해당하는 Redo data, Undo Data를 생성 
  - COMMIT하기 전 ROLLBACK이 가능 
  - 삭제할 데이터를 해당 블록 내에서 하나씩 찾아서 삭제하기 때문에 시간이 오래 걸림 
  - 모두 삭제하였을 때도 사용된 블럭의 EXTENT 및 HWM을 그대로 유지  
  - 삭제(delete)하는 테이블의 데이터를 참조(reference)하는 테이블의 데이터에서 
    row단위로 체크해서 참조하는 테이블에 row가 없으면 삭제 가능 
  - 삭제한 row의 수를 반환함 
  - delete로 삭제된 데이터는 FLASHBACK QUERY로 복구 가능 

  - 통계정보(Analyze)를 변경하지 않음 

  

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

2. TRUNCATE 
------------------------------------- 

  - DDL(Data Definition Language) 
  - 사용 : TRUNCATE TABLE 테이블명; 

  - 데이터의 일부 삭제는 불가, 전부 삭제됨 
  - DDL(Data Definition Language) 이므로 데이터를 메모리로 로딩(Loading)하지 않음 
  - Undo, Redo log를 생성하지 않음 

    (테이블이나 인덱스가 차지하고 있던 저장공간 관리영역에 대한 적은 량의 redo log는 생성함) 
  - Undo, Redo log를 생성하지 않기 때문에 ROLLBACK 불가, 복구 불가 
  - 타 테이블에서 참조하고 있으면 Truncate 불가 
  - 모두 삭제되기 때문에 사용된 EXTENT 및 HWM을 초기화 
  - 참조(reference) 제약이 ENABLE인 경우 ORA-02266 오류 발생 
  - 실제 해당 데이터를 일일이 찾아서 지우지 않고, 

      데이터 딕셔너리(Data Dictionary)에 삭제 표시 및 
      빈 공간 표시만을 하기 때문에 빠른 시간 내에 시스템 부하없이 데이터를 삭제 가능 
  - 삭제한 row의 수를 반환하지 않음 
  - Truncate한 Table 하나만을 특정 시점으로 복구 시 Archive Log Mode로 운영 중인 경우에 

      Hot Backup 또는 Cold Backup을 이용한 별도 데이터베이스에서 Incomplete Recovery를 

      한 후 해당 테이블을 exp/imp해야 복구 가능 
  - truncate table로 삭제된 데이터는 FLASHBACK QUERY로도 복구 불가 

    (ORA-01466: unable to read data - table definition has changed) 

  - 통계정보(Analyze)를 변경함

Listener에 접속할 수 있는 Client 를 제한하는 방법입니다. 

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><?xml:namespace prefix = o /> 

① 환경설정 

$ORACLE_HOME/network/admin/protocol.ora 에 설정 (없을 경우 생성함) 

  

② 방법 1 : 특정 IP만 접속, 나머지 IP는 모두 거절 

tcp.validnode_checking = yes 
tcp.invited_nodes=( 192.168.18.7 ) 

이 경우는 오직 192.168.18.7 Node의 사용자만 접속을 할 수 있으며, 
그 외의 Node에서는 접속이 거절됩니다. (ora-12537 발생) 
여기서 조심할 사항은 반드시 자기 자신의 IP를 Invited_Nodes에 등록해줘야 합니다. 

  

③ 방법 2 : 특정 IP만 거절, 나머지 IP는 모두 접속 

tcp.validnode_checking = yes 
tcp.excluded_nodes=( 192.168.18.7 ) 

이 경우는 192.168.18.7 만 접속이 거절되며, 그 외의 다른 Machine에서는 
접속이 정상적입니다. 환경설정 후 리스너 재시작 필요합니다. 

※ 여러 IP를 등록할 때는 , 로 계속 붙여서 쓰면 됩니다. 

tcp.validnode_checking = yes 
tcp.invited_nodes = (192.168.18.7, 192.168.18.8, 192.168.18.9) 

테이블설계시고려해야할점을오라클기준으로나열해봤습니다. 

1.코드성필드는문자열타입으로정의하는게좋습니다.(CHAR,VARCHAR2) 

2.문자열타입인CHAR타입과VARCHAR2타입은반드시구분해서정의해야합니다. 
똑같은문자열타입이지만용도도조금다르고내부메카니즘도다릅니다. CHAR타입인경우는입력시무조건정의한자리수만큼공간을확보합니다. 예를들어testchar(10)필드에'a'라는문자를입력하면한글자만들어가는게아니라a에스페이스9자리가 딸려들어갑니다.데이터구조상으로볼때미리공간을확보하기때문에해당필드에UPDATE가걸릴때이미확보된 공간내에서I/O가일어나기에속도가빠릅니다.이론적으로조회속도도VARCHAR보다좋습니다만조회시 스페이스가붙어나온다는것과Where조건에서Trim을해야한다는단점이있습니다. VARCHAR타입은저장시입력된데이터의길이만큼저장합니다.리소스절약이나데이터조회시는편하지만 I/O측면에서볼때는CHAR타입보다떨어집니다. CHAR타입은길이가딱정해진필트에대해서정의하는게좋고...(예,남녀구분,정상,반품구분등등구분자타입) VARCHAR타입은길이가유동적인필드에적합합니다.(명칭류..) 

3.숫자형필드는NOTNULL로지정하는게좋습니다. 
숫자형필드는수치연산이나SUM()을낼때널값이들어가면연산오류나원하는결과가안나오는수가있음으로 입력시사람이입력을안하더라도디폴트값0을넣어주는게정신건강상좋습니다. 

4.날짜형태의필드는DATE타입으로지정하는게좋습니다. 
CHAR타입으로선언해서엉뚱한데이터가들어가는경우을미연에방지할수있고날짜계산등에DATE타입이 매우편리합니다.

5.조회시주로사용되는필드는NOTNULL로지정하는게좋습니다. 
조회조건에들어가는필드가널값인경우는데이터가조회가안되는걸미연에방지할수있습니다. 

6.특정필드에중요한제약조건이있다면테이블설계시해당필드에제약조건을걸어둡니다. 
DB단에서제약조건을걸어두면설령프로그램에데이터를잘못입력해도DB단에서오류를막아줄수 있음으로중요한제약조건이있다면해당필드에제약조건을걸어둡니다.(예,숫자필드에0이상만입력가능한 제약조건이라던지,특정값만들어오는제약조건이라던지,시스템날짜이하로는입력이안되는제약조건등등..) 

한글을 지원하는 캐릭터셋 
1.KO16KSC5601 
2.KO16MSWIN949 
3.UTF8 
4.AL32UTF8 

A. 작업 전 만약을 위해 DB전체를 백업 받아둔다. character set 을 잘못 변경하면 DB를 open 할수 없을 수 있다. 
B. 오라클 캐릭터셋 확인쿼리 
  SELECT parameter,value FROM nls_database_parameters WHERE parameter like '%CHAR%'; 
-------------------------------------- 
C. 아래는 OTN에서 발췌한 내용 
8i(AMERICAN_AMERICA.US7ASCII)는 version 8.1.6 이상이라고 가정하겠습니다. 아래의 내용 중의 일부분은 Oracle database release 8.1.6 이상에서만 적용될 수 있습니다. 
기본 작업 절차 
-------------- 
(1) CLOB data에 대한 unloading/exporting. 
(2) 8i(AMERICAN_AMERICA.US7ASCII)에서 "ALTER DATABASE CHARACTER SET" command set을 통한 8i(AMERICAN_AMERICA.KO16KSC5601)로의 변환. 
(3) 8i(AMERICAN_AMERICA.KO16KSC5601)에서 NLS_LANG 환경변수 변경(KOREAN_KOREA.KO16KSC5601)을 통한 8i(KOREAN_KOREA.KO16KSC5601)로의 변환. 
(4) 8i(KOREAN_KOREA.KO16KSC5601)에서 exporting 후 9i(KOREAN_KOREA.KO16KSC5601)에서 importing. 
(5) CLOB data에 대한 loading/importing. 
과정(2)의 세부 절차 
------------------ 
$ sqlplus /nolog 
SQL> conn sys/pw as sysdba 
SQL> shutdown immediate 
SQL> startup mount 
SQL> alter system enable restricted session; 
SQL> alter system set JOB_QUEUE_PROCESSES=0; 
SQL> alter system set AQ_TM_PROCESSES=0; 
SQL> alter database open; 
SQL> alter database character set KO16KSC5601; 
SQL> alter database national character set KO16KSC5601; 
SQL> shutdown immediate 
SQL> startup open 
  
* 
ERROR at line 1: 
ORA-12716: Cannot ALTER DATABASE CHARACTER SET when CLOB data exists 
## 에러가 발생할 경우 아래 스크립트로 실행한다. 
SQL> alter database  character set internal_use ko16ksc5601; 
  
CF) DB내의 CLOB 찾기 
CLOB 또는 NCHAR Datatype의 컬럼들을 export 받고 
CLOB, NCHAR이 포함된 Table을 Drop하고 
Alter database character set ... 하고 
다시 Import하라고 되어 있는데 별 효용없는것 같음. 
sys, system으로 접속하여 
SELECT * 
FROM dba_lobs 
Partitioning했다면 DBA_PART_LOBS까지 참조 

## export

형식 :  EXP KEYWORD=값 또는 KEYWORD=(값1,값2,...,값N)

예    :  EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
           또는 T1이 분할된 테이블인 경우 TABLES=(T1:P1,T1:P2)

예 ) exp scott/tiger file=emp.dmp log=emp.log tables=emp direct=y
예 ) exp system/oracle file=full.dmp full=y indexes=n triggers=n
예 ) exp system/oracle file=scott.dmp owner=scott rows=n

option

수많은 exp 옵션들... 다 외울순 없다..

exp help=y

하게 되면 친절히 설명해주니 기억이 나지 않을시 참고 하도록 하자

scott/tiger                           : db 유저/password
full                                       : 해당 db 전체 데이터 추출 여부(기본값 N)
buffer                                   : 작업 단위의 크기 설정
owner                                  :  db 유저별 오브젝트 추출 설정
file                                       : 추출한 데이터를 저장할 파일 이름 설정
tables                                  : 데이터를 추출할 대상 테이블 설정
compress                            :  익스텐트 통합 여부를 지정(기본값 Y)
grants                                  : 오브젝트 권한 설정에 대한 정보 추출 여부(기본값 Y)
indexes                                : 인덱스 스크립트 추출 여부(기본값 Y)
direct                                   : 직접 경로로 export 수행 여부(기본값 N)
triggers                                  : 트리거 정보 추출 여부(기본값 Y)
log                                       : 로그를 저장할 파일 지정
rows                                     : 테이블의 데이터 추출 여부(기본값 Y)
consistent                             : 대상 테이블의 읽기 일관성 지정(기본값 N)
constraints                            : 제약 조건의 추출 여부(기본값 Y)
parfile                                 :  파라메터 파일을 export시 적용
INCTYPE                               : 증분 엑스포트 유형
RECORD                               : 증분 엑스포트 추적(기본값 Y)

OBJECT_CONSISTENT            : 객체 엑스포트 동안 읽기 전용으로 설정된 트랜잭션(N)
FEEDBACK                            : 매 x행마다 진행 상황 표시(0)
FILESIZE                             : 각 덤프 파일의 최대 크기
FLASHBACK_SCN                   : 세션 스냅샷을 되돌리는 설정에 사용된 SCN
FLASHBACK_TIME                  : 지정된 시간에 가장 가까운 SCN을 가져오는 데 사용된 시간
QUERY                                 : 테이블의 부분 집합을 엑스포트하는 데 사용된 select 절
RESUMABLE                          : 공간 관련 오류가 발생하면 일시 중지됨(N)
RESUMABLE_NAME                : 재개 가능한 명령문을 식별하는 데 사용된 텍스트 문자열
RESUMABLE_TIMEOUT            : RESUMABLE 대기 시간
TTS_FULL_CHECK                   : TTS에 대한 전체 또는 부분 종속성 검사 수행
TABLESPACES                     : 엑스포트할 테이블스페이스 목록
TRANSPORT_TABLESPACE : 이동 가능한 테이블스페이스 메타 데이터 엑스포트(N)
TEMPLATE                             : iAS 모드 엑스포트를 호출하는 템플리트 이름

** compress 옵션을 Y로 설정 하여 사용하면 여러개의 익스텐트가 하나의 큰 익스텐트로 통합된다
   실제 운영에서는 익스텐트를 통합하는것은 좋지 않으므로 반드시 compress 옵션을 N로 설정 한다.
** direct 옵션을 사용하면 SGA를 사용하지 않고 export/import 하게 되며 
   추출 및 적재 작업시 보다 빠른 속도를 보장 받는다.
** comsistent 옵션을 Y로 설정하면 export를 수행한 시점의 데이터를 추출한다.
   export 중 변경된 데이터는 언두 데이터를 이용해서 이전 값을 추출한다.
   따라서 'snap shot too old' 에러가 발생하기 쉽다.

## export 작업시 주의 사항

** full, ower 및 tables 옵션은 동시 사용 불가
** direct=Y인 경우 consistent=y로 설정 불가
** full=Y로 expor를 수행하기 위해서는 exp_full_database 권한 필요

----- 추출 파일의 크기를 확인하는 SQL

SQL> select sum(bytes)/1024/1024
     from dba_segments
     where segment_name='TEST';

export 작업을 수행 하면 실제 테이블의 크기보다 작게 생성된다.
위 SQL을 수행하여 나온 크기만큼 디스크의 크기를 확보하면 export 작업을 수행하는데 충분하다.
또한 추출 파일에는 해당 테이블의 인덱스를 생성할 수 있는 정의만 저장하므로 별도의 디스크공간은 필요하지 않다.
하지만 import 시에는 실제 인덱스를 생성하므로 인덱스 테이블스페이스에 여유 공간을 확보해야한다.

## import

-- import 순서 : 테이블 생성 > 데이터 적재 > B*트리 인덱스 생성 > 제약조건 > 트리거 및 비트맵 인덱스 생성

예 ) imp scott/tiger file=emp.dmp log=emp.log tables=emp
예 ) system/manager file=full.dmp log=full.log full=y
예 ) system/manager file=scott.dmp fromuser=scott touser=mike

option

scott/tiger                         : db 유저 / password
full                                     : 전체 db에 대해 import 여부(기본값 N)
buffer                                 : 작업 단위의 크기 지정
fromuser                       : export한 테이블의 소유자 지정
touser                                : import되는 테이블의 소유자 지정
tables                                : import 대상 테이블 지정
ignore                                : import 대상 테이블이 존재할 경우 에러 발생 여부(기본값 N)
grants                                 : 권한 적재 여부 지정 (기본값 Y)
indexes                             : index 생성 여부(기본값 Y)
commit                               : import 수행중 커밋 수행여부, 
                                           Y로 지정할 경우 buffer 옵션 단위로 커밋 수행(기본값 N)
rows                                   : 테이블의 데이터 적재 여부(기본값 Y)
log                                     : 로그를 저장할 파일 지정
constrainsts                      : 제약 조건 적재 여부(기본값 Y)
parfile                                : 파라메터 파일을 지정하여 import 작업시 적용

SHOW                                 : 파일 목차만 목록 (기본값 N)
RECORDLENGTH IO               : 레코드의 길이
INCTYPE                              : 증분 임포트 유형
DESTROY                             : 테이블스페이스 데이터 파일 겹쳐쓰기(기본값 N)
INDEXFILE                          : 지정된 파일에 테이블/인덱스 정보 쓰기
SKIP_UNUSABLE_INDEXES     : 사용되지 않은 인덱스들의 유지를 건너뜁니다 (기본값 N)
FEEDBACK                           : 매 x행마다 진행 상황 표시(기본값 0)
TOID_NOVALIDATE                : 지정된 유형 ID 검증 건너뛰기
FILESIZE                            : 각 덤프 파일의 최대 크기
STATISTICS                          : 미리 계산된 통계 임포트(항상)
RESUMABLE                         : 공간 관련 오류가 발생하면 일시 중지됨(기본값 N)
RESUMABLE_NAME               : 재개 가능한 명령문을 식별하는 데 사용되는 텍스트 문자열
RESUMABLE_TIMEOUT          : RESUMABLE 대기 시간
COMPILE                              : 프로시저, 패키지 및 함수 컴파일(기본값 Y)
STREAMS_CONFIGURATION   : 일반 스트림 메타 데이터 임포트(기본값 Y)
STREAMS_INSTANTIATION      : 스트림 인스턴스화 메타 데이터 임포트(기본값 N)

아래의 키워드만이 이동가능한 테이블스페이스에 적용할 수 있다.

TRANSPORT_TABLESPACE  : 이동가능한 테이블스페이스 메타데이터을 임포트합니다 (기본값 N)
TABLESPACES                     : 데이터베이스안에 테이블스페이스가 이동되었습니다
DATAFILES                            : 데이터베이스안에 데이터 파일들이 이동되었습니다
TTS_OWNERS                          : 이동가능한 테이블스페이스 설정에 데이터를 소우하고 있는 사용자

## import 작업시 주의 사항

** full 옵션과 tables 옵션은 동시 사용 불가
** full=y 및 touser 옵션을 사용하기 위해서는 imp_fyll_database 권한 필요
** 적은 양의 데이터 import 경우를 제외하곤 commit 옵션을 Y로 설정 권장.
** export 시와 동일한 tablespace가 존재하지 않을 경우 유저 default tablespace로 import 된다
   system 유저로 import 하게 되면 system tablespace에 일반 유저 세그먼트가 생성 될수 있으므로
   반드시 export시 tablespace와 동일한 이름의 tablespace를 생성한 후 작업을 수행해야한다.

------------------------- exp/imp 실습 --------------------------

실습 준비 사항

SQL> create tablespace test datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST1.DBF' size 1m;

SQL> alter tablespace test add datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST2.DBF' size 1m;

SQL> alter tablespace test add datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST3.DBF' size 1m;

SQL> create user orcl identified by orcl
  2  default tablespace test;

SQL> grant connect, resource, select any table to orcl;

SQL> grant exp_full_database, imp_full_database to orcl;            // exp, imp database 사용시

SQL> create table orcl.test as select * from scott.emp;

------------------------- exp/imp database --------------------------

## exp 백업의 이름을 잘 정해야 한다. 해당 백업 파일을 언제 누가 보든 쉽게 이해할수있도록 룰을 잘 정해야한다.
## exp의 경우 datapump와 다르게 디렉토리 지정이 없다.
## exp 할 대상 경로로 이동해 실행하거나, 
## file=[ 경로명 ]\test.dmp , log=[ 경로명 ]\test.log 와 같이 직접 지정해야한다.

D:\test>exp orcl/orcl full=y log=test.log file=test.dmp

엑스포트가 경고 없이 정상적으로 종료되었습니다.

D:\test>dir

2009-04-15  오후 01:12        82,259,968 test.dmp
2009-04-15  오후 01:12            61,816 test.log

D:\test>imp orcl/orcl full=y file=testfull.dmp
.
.
IMP-00015: 객체가 이미 존재하므로 다음 명령이 실패했습니다
 "CREATE UNDO TABLESPACE "UNDOTBS1" BLOCKSIZE 8192 DATAFILE  'D:\ORACLE\PRODU"
 "CT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF' SIZE 62914560       AUTOEXTEND ON NEX"
 "T 5242880  MAXSIZE 32767M EXTENT MANAGEMENT LOCAL "
.
.

########## 완료시 경고성 완료가 될수 있다. 이유? 당연히 DB가 멀쩡히 있기 때문이다.
########## 작업중 err를 발생할 수도 있다.. 걱정마라 system 관련 parameter 또는 동적 뷰다.
########## system 관련 데이터까지 모두 날릴순 없지 않은가..
########## 그럼 어떨때 export/import database를 쓸까??
########## 마이그레이션 작업할때 쓴다!!!

D:\test>imp orcl/orcl full=y file=testfull.dmp ignore=y

엑스포트 파일은 직접 경로를 거쳐 EXPORT:V10.02.01 에 의해 생성되었습니다
KO16MSWIN949 문자집합과 AL16UTF16 NCHAR 문자 집합에 임포트가 완성되었습니다
. SYSTEM 객체를 SYSTEM(으)로 임포트하는 중입니다
. OLAPSYS 객체를 OLAPSYS(으)로 임포트하는 중입니다
.
.

-------------------------- exp/imp transport_tablespace --------------------------

같은 환경을 가진 두개 이상의 분산 DB 환경에서 어떤 DB가 가진 여러개의 데이터 파일을 다른 DB로 이동 시키는 기능.

------- 주의사항
## transport_tablespace를 사용하려면 해당 tablespace를 read only 시켜야 한다.
## tablespace내 하나의 특정 데이터 파일만 이동 할수 없다.
## 같은 환경을 가진 OS에서만 가능함.
## 오라클 8i 이상 사용 가능.
## DB_BLOCK_SIZE 파라메터의 값이 같아야 함.
## 같은 언어타입을 사용해야함.
## VARRAY 타입과 중첩 테이블 컬럼이 있는 테이블은 사용할수 없음.
## bit-map-index는 사용할수 없음.

## LOB 타입을 가진 테이블 또는 분할 테이블은 사용할수 있음.

SQL> conn orcl/orcl
연결되었습니다.

SQL> select count(*) from test;

  COUNT(*)
----------
        14

SQL> conn / as sysdba
연결되었습니다.

SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                                  ONLINE
UNDOTBS1                               ONLINE
SYSAUX                                  ONLINE
TEMP                                      ONLINE
USERS                                     ONLINE
EXAMPLE                                ONLINE
TEST                                       ONLINE

7 개의 행이 선택되었습니다.

SQL> alter tablespace test read only;         // export transport tablespace 하려면 
테이블스페이스가 변경되었습니다.              // tablespace를 read only로 변경해야한다

SQL> exit

-------------------- export transport tablespace 시작
D:\test>exp '/ as sysdba' file=aaa.dmp transport_tablespace=y tablespaces=test
.
.
엑스포트가 경고 없이 정상적으로 종료되었습니다.

D:\test>sqlplus / as sysdba

SQL> drop tablespace test including contents;

테이블스페이스가 삭제되었습니다.

SQL> select * from orcl.test;                   // tablespace가 삭제 되어서 table을 찾을수 없다.
select * from orcl.test
              *
1행에 오류:
ORA-00942: 테이블 또는 뷰가 존재하지 않습니다

SQL> exit

------------------------- aaa.dmp 파일을 이용한 tablespace import
D:\test>imp '/ as sysdba' file=aaa.dmp transport_tablespace=y tablespaces=test datafiles=D:\ORACLE\P
임포트가 경고 없이 정상적으로 종료되었습니다.

D:\test>sqlplus orcl/orcl

SQL> select count(*) from test;

  COUNT(*)
----------
        14

SQL> select tablespace_name, status from dba_tablespaces;          

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
USERS                          ONLINE
EXAMPLE                        ONLINE
TEST                           READ ONLY

7 개의 행이 선택되었습니다.

SQL> alter tablespace test read write;               // import 완료 했으므로 다시 online으로 변경

테이블스페이스가 변경되었습니다.

------------------------------------- exp/imp table--------------------------------------

예 ) exp orcl/orcl file=test.dmp log=test.log tables=test
예 ) exp orcl/orcl file=test.dmp log=test.log tables=scott.emp
예 ) exp orcl/orcl file=test.dmp log=test.log tables=(scott.emp, hr.employees, scott.dept)

SQL> select count(*) from test;

  COUNT(*)
----------
        14

D:\test>exp orcl/orcl file=test.dmp log=test.log tables=ss
.
.
엑스포트가 경고 없이 정상적으로 종료되었습니다.

####### case 1) table을 drop 한 경우 #######

SQL> drop table test;

SQL> select count(*) from test;
select count(*) from test
                     *
1행에 오류:
ORA-00942: 테이블 또는 뷰가 존재하지 않습니다

D:\test>imp orcl/orcl file=table.dmp tables=test
임포트가 경고 없이 정상적으로 종료되었습니다.

SQL> select count(*) from test;

  COUNT(*)
----------
        14

####### case 2) table을 delete 한 경우 #######

SQL> delete test;

14 행이 삭제되었습니다.

SQL> select * from test;

선택된 레코드가 없습니다.

SQL> commit;

커밋이 완료되었습니다.

D:\test>imp orcl/orcl file=table.dmp tables=test
.
.
 "CREATE TABLE "TEST" ("EMPNO" NUMBER(4, 0), "ENAME" VARCHAR2(10), "JOB" VARC"
 "HAR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE, "SAL" NUMBER(7, 2), "COMM" NU"
 "MBER(7, 2), "DEPTNO" NUMBER(2, 0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRA"
 "NS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFA"
 "ULT)                   LOGGING NOCOMPRESS"
경고와 함께 임포트가 정상 종료되었습니다.

// imp table을 할때 create table 구문이 들어가 있다.
// 즉, table의 row는 없더라도 같은 이름의 table이 있다면 import 되지 않는다.
// 이러한 경우 ignore=y 옵션을 사용한다.

D:\test>imp orcl/orcl file=table.dmp tables=test ignore=y

임포트가 경고 없이 정상적으로 종료되었습니다.

SQL> select count(*) from test;

  COUNT(*)
----------
        14

####### case 2) data가 들어있는 table에 ignore 옵션을 사용할 경우 #######
SQL> select count(*) from test;

  COUNT(*)
----------
        14

D:\test>imp orcl/orcl file=table.dmp tables=test ignore=y

임포트가 경고 없이 정상적으로 종료되었습니다.

SQL> select count(*) from test;

  COUNT(*)
----------
        28

// row수가 14개 더 늘어난걸 볼수있다.
// ignore 옵션은 해당 테이블에 중복 데이터가 있으면 해당 데이터를 덮어 쓰는게 아니다.

----------------------------------- exp/imp owner ---------------------------------------

D:\test>exp '/ as sysdba' file=user.dmp log=user.log owner=orcl

엑스포트가 경고 없이 정상적으로 종료되었습니다.

SQL> drop user orcl cascade;                    // 해당 유저와 유저의 데이터까지 모두 삭제.

사용자가 삭제되었습니다.

SQL> create user orcl identified by orcl
  2  default tablespace test;

사용자가 생성되었습니다.

SQL> grant connect, resource to orcl;

권한이 부여되었습니다.

D:\test>imp '/ as sysdba' file=user.dmp fromuser=orcl touser=orcl

경고와 함께 임포트가 정상 종료되었습니다.

SQL> select count(*) from test;

  COUNT(*)
----------
        14

------------------------------ exp/imp query ------------------------------------

## export시 쿼리 문을 사용하여 해당 table내 원하는 데이터만 추출할수 있다.
   - 테이블 레벨의 export 명령어에서만 가능하다.
   - Direct 옵션과 함께 사용될 수 없다.
   - Nested 테이블을 갖는 테이블에는 적용할 수 없다.
   - Partition 테이블에 대한 export에도 적용가능하다.
   - Import 명령에는 적용되지 않는다.

D:\test>exp orcl/orcl file=dump.emp log=dump.log tables=orcl.test query='where empno=7788'

엑스포트가 경고 없이 정상적으로 종료되었습니다.

------------------------------------ 참고 사항 ---------------------------------------------
## UNIX reserved characters( ", ', ,< .. 등)를 사용하는 경우에는 escape ('\') 을 반드시 사용해야 한다.

----- win
예 ) exp orcl/orcl file=dump.emp log=dump.log tables=orcl.test query=\"where ename like 'SCOT%'\"
----- UNIX
예 ) exp orcl/orcl file=dump.emp log=dump.log tables=orcl.test query=\"where ename like \'SCOT%\'\"

## command line에서 query 내에 '," 을사용하지 않는 다면 '나 " 으로 query option을 사용할수 있다
   query=\'where deptno = 20\'
   query=\"where deptno = 20\"
   query=\'where deptno \< 2\' 
   (이 경우  single 이나  double quotes 을 둘다 사용할수 있다.)

## command line에서 export option을 사용할때는 반드시 escape가 있어야 하나 
   parfile을 사용할때는 eacape이 불필요하다. 
  
parfile을 사용하는 경우에는 다음과 같이 단순하게  사용이 가능하다.
query='where deptno = 20'
query="where deptno = 20"
query='where deptno < 20'

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

########### case 1) ########### 
SQL> drop table test;

D:\test>imp orcl/orcl file=dump.emp tables=test

임포트가 경고 없이 정상적으로 종료되었습니다.

SQL> select count(*) from test;

  COUNT(*)
----------
         1

########### case 2) ########### 
SQL> delete from test where empno=7788;

SQL> commit;

SQL> select count(*) from test;

  COUNT(*)
----------
        13

D:\test>imp orcl/orcl file=table.dmp tables=test ignore=y

임포트가 경고 없이 정상적으로 종료되었습니다.

SQL> select count(*) from test;

  COUNT(*)
----------
        14

// 실제로 imp된 7788사원은 해당 테이블의 가장 마지막 로우에 import 된다.

## 그렇다면 table 단위로 emp 하고 query로 뽑아서 import 할수 있을까?
   -> 정답은 안된다. 궁금하면 해보시라.
   LRM-00101: 알 수 없는 'query' 매개변수 이름. 이런 메세지가 기다리고 있을것이다.

---------------------------------- exp/imp parfile 방식 -----------------------------------

## parfile 안에 해당 exp/imp 설정을 작성한후 parfile을 이용해서 exp/imp를 할수있다.

########### exp case 1
file=full.dmp
log=full.log
full=y
direct=y
.
.

########### exp case 2
file=emp.dmp
log=emp.log
tables=scott.emp
direct=y
.
.

########### exp case 3
file=scott.dmp
log=scott.log
owner=scott
direct=y
.
.
########### exp case 4
file=query.dmp
log=query.log
tables=scott.emp
query="where ename like 'SCOT%'"
direct=y
.
.

---------------------------
// 윈도우 이기 때문에 노트 패드로 pa.par 파일을 작성 한뒤 실습 내용임

D:\test>more pa.par
file=table.dmp
log=table.log
tables=orcl.test
direct=y

D:\test>exp test/test parfile=pa.par

엑스포트가 경고 없이 정상적으로 종료되었습니다.

D:\test>more impa.par
file=table.dmp
tables=test

D:\test>imp orcl/orcl parfile=impa.par

임포트가 경고 없이 정상적으로 종료되었습니다.

SQL> select count(*) from test;

  COUNT(*)
----------
        14

--------------------------------------- DIALOG 방식 -----------------------------------

D:\test>exp

Export: Release 10.2.0.1.0 - Production on 수 4월 15 15:54:52 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

사용자명: test
암호:

다음에 접속됨: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
배열 인출 버퍼 크기 입력: 4096 > 409

엑스포트 파일: EXPDAT.DMP > 111

(1)E(전체 데이터베이스), (2)U(사용자), 또는 (3)T(테이블): (2)U > 1

권한부여 엑스포트 (yes/no): yes > yes

테이블 데이터 엑스포트 (yes/no): yes > yes

확장 영역 압축 (yes/no): yes > yes

KO16MSWIN949 문자 설정과 AL16UTF16 NCHAR 문자 설정에서 엑스포트가 종료되었습니다

전체 데이터베이스를 엑스포트하려고 합니다 ...
테이블 정의 엑스포트 중
프로파일 엑스포트 중
사용자 정의 엑스포트 중
.
.
.

Closed 백업(=Cold 백업)

  - Closed 백업은 데이터베이스가 Shutdown된 상태에서 백업을 하는 방법을 의미합니다.

  - ARCHIVE LOG MODE와 NOARCHIVE LOG MODE 둘 다 가능합니다.

  - 모든 DATA FILE, CONTROL FILE, REDOLOG FILE이 대상입니다.(정상적인 종료(normal, transactional, immediate)는 REDO LOG FILE을 반드시 백업할 필요는 없으나 복구할 때 간단하게 하기 위하여 백업을 해 두는 것이 좋습니다.)

  - 초기화 파라미터 파일은 변경되었을 경우에는 백업해 놓습니다.

  - 개념적으로 단순하여, 백업 및 복구방법이 용이 합니다.

  - NOARCHIVE LOG MODE일 경우에는 백업받은 시험 이후의 데이터는 보장하지 않으므로 장애가 발생하였을 경우는 변경된 사항은 수동으로 입력해 주어야 합니다.

1. 데이터파일, 컨트롤 파일, redo 로그 파일의 위치와 이름을 확인한다.

 
-- sqlplus를 실행합니다.
C:\> SQLPLUS /NOLOG

-- SYSDBA권한으로 접속합니다.
SQL> CONN / AS SYSDBA

-- Controlfile의 위치 및 이름을 확인합니다.
SQL> SELECT name FROM V$CONTROLFILE;




-- 데이터 파일들의 위치 및 이름을 확인합니다.
SQL> SELECT name,status FROM V$DATAFILE;




--Redo Log 파일들의 위치 및 이름을 확인 합니다.
SQL> SELECT * FROM V$LOGFILE;


    

2. 데이터베이스를 shutdown 합니다.

3. 대상 파일들을 전부 백업(copy)합니다.

4. 데이터베이스를 오픈 합니다.

COLD BACKUP(오프라인 백업)

  - COLD BACKUP이란 오라클 SHUTDOWN후 DATAFILE, REDO LOG FILE, CONTROL FILE, PARAMETER FILE등을 OS의 복사명령으로 백업을 수행하는 방법을 말한다.

  - 백업받을 파일들의 목록은 V$DATAFILE, V$LOGFILE, V$CONTROLFILE에서 찾을 수 있다.

  - COLD BACKUP을 위해서 데이타베이스를 SHUTDOWN 할 때에는 NORMAL, IMMEDIATE 옵션을 사용해야 하며 ABORT 를 사용해서는 안 된다. ABORT 를 사용한 경우에는 SHUTDOWN 후에 다시 STARTUP 하고 NORMAL 로 SHUTDOWN 하도록 한다.

  - SHUTDOWN ABORT 옵션을 쓸 경우 Checkpoint 정보가 일치하지 않아 복구가 수행되지 않을 수 있으므로 이 옵션을 사용하지 말아야 한다.

  - SHUTDOWN 하지 않고 OPEN 된 상태에서 백업을 받으면 백업받은 내용을 나중에 사용할 수가 없으므로 유의해야 한다.

  - 콘트롤 화일과 데이타 화일 및 로그 화일의 위치를 확인하여 이들을 tar, cpio 등의 명령을 이용하여 백업 받도록 한다.

  - NT 에서는 COPY 명령이나 탐색기를 이용해서 백업을 받으면 된다.

  - 장점 : 읽기 일관성이 보장됨, 가장 간편하다.

  - 단점 : 데이터베이스 SHUTDOWN이 필요하므로 백업받는 동안 데이터베이스를 사용할 수 없으며, 백업이 수행된 시점까지만 복구가 가능하다

파일확인 방법

  오라클은 반드시 SHUTDOWN된 상태이어야 하며, 아래에서 확인한 세 종류의 파일들을 OS명령어(ex cp)로 백업받으면 된다. 필요에 따라서 파라미터(init.ora) 파일까지 받아두는 것이 좋다.

 
-- Oracle 8.1.7 sqlplus에서 system/manager로 접속

-- 콘트롤 파일 확인 
SQL> SELECT name FROM V$CONTROLFILE;

NAME
------------------------------------------
C:\ORACLE\ORADATA\ORACLE\CONTROL01.CTL
C:\ORACLE\ORADATA\ORACLE\CONTROL02.CTL
C:\ORACLE\ORADATA\ORACLE\CONTROL03.CTL


-- 데이타 파일 확인 
SQL> SELECT name FROM V$DATAFILE;

NAME
--------------------------------------------------
C:\ORACLE\ORADATA\ORACLE\SYSTEM01.DBF
C:\ORACLE\ORADATA\ORACLE\INDX01.DBF
C:\ORACLE\ORADATA\ORACLE\TOOLS01.DBF
C:\ORACLE\ORADATA\ORACLE\USERS01.DBF
C:\ORACLE\ORADATA\ORACLE\STORM.DBF
C:\ORACLE\ORADATA\ORACLE\STORMIDX.DBF
C:\ORACLE\ORADATA\ORACLE\TEST.DBF


-- 로그 파일 확인
SQL>SELECT member FROM V$LOGFILE; 

MEMBER
------------------------------------
C:\ORACLE\ORADATA\ORACLE\REDO03.LOG
C:\ORACLE\ORADATA\ORACLE\REDO02.LOG
C:\ORACLE\ORADATA\ORACLE\REDO01.LOG 
    

오프라인 복구 예제

  COLD BACKUP 백업에 대한 복구는 장애가 발생한 시점까지가 아니라, 가장 최근의 백업시점 까지만 복구가 가능 하며, 백업 시점에서 현 시점까지의 데이터는 모두 잃어 버리게 된다.

동일한 디스크에 위치한 데이터파일 복구

  1. DB를 SHUTDOWN ABORT로 닫는다.

  2. 백업해두었던 DATAFILE, REDO LOG FILE, CONTROL FILE, PARAMETER FILE(init.ora) 들을 현재의 DB 데이터파일들이 위치한 곳으로 이동 시킨다(copy 작업).

  3. STARTUP MOUNT를 수행 한다.

  4. ALTER DATABASE OPEN RESETLOS 수행

상이한 디스크에 위치한 데이터파일 복구

  만일 디스크가 교체될 수 없는 경우, 파일들을 다른 디스크로 이동시켜 복구한다.

  1. DB를 SHUTDOWN ABORT로 닫는다.

  2. 백업해두었던 DATAFILE, REDO LOG FILE, CONTROL FILE, PARAMETER FILE(init.ora), PASSWORD FILD(orapwSID) 들을 다른 디스크로 이동

  3. PARAMETER FILE에서 CONTROL_FILES 파라미터를 수정한다.

  4. STARTUP MOUNT EXCLUSIVE 수행

  5. 새로운 디스크에 위치한 데이터파일들의 위치를 control파일에 갱신

 
ALTER DATABASE RENAME 
FILE '/u01/oracle/data/data01.dbf' 
TO '/u02/oracle/data/data01.dbf'
    

  6. DB백업(형식적인 절차)

  7. ALTER DATABASE OPEN RESETLOGS 수행

ORA-00031: session marked for kill 의 해결책

[TroubleShootings]

테이블이 락이 걸려서 TOAD의 사용이 막힌 경우 테이블의 락을 풀어줘야 하는데, 보통은 SYSTEM 계정으로 접근하여 아래와 같은 쿼리를 입력하면 테이블의 락이 해제된다.
 
ALTER SYSTEM KILL SESSION '16,4558';
 (* ALTER SYSTEM KILL SESSION 'SID_no,SERIAL_no';)
 

하지만, 테이블 락을 해제 쿼리를 입력하여도 ORA-00031 : session marked for kill 의 메시지가 나오는 경우 직접적으로 아래의 쿼리를 이용하여 oracle계정으로 시스템에 접근하여 프로세스를 kill하는 방법이 있다.
 (kill을 하여도 락이 해제가 되지 않고, 앞서 session marked for kill이라는 메시지만 출력 되는 이유는 rollback을 하는 프로세스가 동작하여 kill을 해도 mark만 하고 rollback 중인 상태이므로 현재 락은 걸린 상태라고 볼 수 있을 것 같다.)
 

select vs.sid, vs.username, vs.osuser, vs.process fg_pid,
vp.spid bg_pid
from v$session vs, v$process vp
where vs.paddr = vp.addr;
위의 쿼리를 실행하여, PID(ProcessID)를 확인 후 오라클 계정으로 시스템에 접근하여, kill명령어를 이용하여 해당 프로세스를 종료한다.
 
$ kill -9 pid
 
물론 앞서 메시지가 나오고 방금 kill이 안되는 경우에도 어느정도 시간을 갖고 기다리면 rollback segment의 수치가 점점 작아져 결국 테이블 락이 해제가 된다.