에너쓰오라클

2009.11.06 update
벌써 exadata v2가 발표되었네요.
v1과는 달리 SUN 장비에 OLTP용 머신으로 2009 OOw에서 발표되었습니다.
자세한 내용은 아래에..

• Larry Ellison이 진행하는 Exadata V2 웹캐스트 보기
• 전체 발표 보도자료 읽기
• Oracle Exadata에 대한 자세한 정보
• Sun Oracle Database Machine에 대한 자세한 정보
• Sun Oracle Database Machine FAQ

- CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
Backup 보관 주기나 backup본의 갯수를 설정합니다.

- CONFIGURE BACKUP OPTIMIZATION OFF;
이미 backup 된 동일한(checkpoint SCN등) datafile, archived redolog, backup set이 있다면 skip 합니다.

- CONFIGURE DEFAULT DEVICE TYPE TO DISK; 
default backup device를 설정합니다.

- CONFIGURE CONTROLFILE AUTOBACKUP OFF; 
RMAN의 BACKUP이나 COPY 명령등의 수행후 자동으로 control file backup을 수행합니다.

- CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
autobackup되는 control file의 기본 format을 변경합니다.

- CONFIGURE DEVICE TYPE DISK PARALLELISM 1;
특정 device에 automatic channel allocation 될때 channel의 갯수를 지정합니다.

- CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
DATAFILE, CONTROL FILE의 backup set의 copy본 갯수를 지정합니다.

- CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; 
ARCHIVELOG FILE의 backup set의 copy본 갯수를 지정합니다.

- CONFIGURE MAXSETSIZE TO UNLIMITED; 
backupset의 maximum size를 설정합니다.

- CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; (10g only)
flash recovery area의 archived redo log에 대한 삭제 여부를 설정합니다.

- CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/ora10g/dbs/snapcf_db10g.f';
RMAN은 resync시 생성되는 임시 snapshot control file의 이름을 지정한다.

참고 :
oracle rman forum : Recovery Manager (RMAN)
oracle 10g, 11g new feature : Recovery Manager (RMAN)
Oracle Technical iSeminar : RMAN 개요와 문제 진단

[RMAN backup 내역 예]

RMAN> list backupset of database;
List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
21046   Full    957M       DISK        00:04:04     06-MAY-03  
        BP Key: 21047   Status: AVAILABLE   Tag: FULL_DB_SUNDAY_NIGHT
        Piece Name: /home1/kcshin/rman/backup/db_t493300635_s31_p1

  List of Datafiles in backup set 21046
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 7238179754301 06-MAY-03 /home2/oradata/ORA920/system01.dbf
  2       Full 7238179754301 06-MAY-03 /home2/oradata/ORA920/undotbs01.dbf
  3       Full 7238179754301 06-MAY-03 /home2/oradata/ORA920/example01.dbf
  4       Full 7238179754301 06-MAY-03 /home2/oradata/ORA920/indx01.dbf
  5       Full 7238179754301 06-MAY-03 /home2/oradata/ORA920/tools01.dbf
  6       Full 7238179754301 06-MAY-03 /home2/oradata/ORA920/users01.dbf
  7       Full 7238179754301 06-MAY-03 /home2/oradata/ORA920/oem_repository.dbf
  8       Full 7238179754301 06-MAY-03 /home2/oradata/ORA920/kcshin01.dbf

[RMAN backup 내역 예]

RMAN> list copy of datafile '/home2/oradata/ORA920/kcshin01.dbf';
List of Datafile Copies
Key     File S Completion Time Ckp SCN       Ckp Time        Name
------- ---- - --------------- ----------    --------------- ----
21070   8    A 06-MAY-03       7238179754301 06-MAY-03       /home1/kcshin/rman/backup/datafile8.f

[RMAN backup 내역 예]

RMAN> list backupset of database;
List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
21046   Full    957M       DISK        00:04:04     06-MAY-03  
        BP Key: 21047   Status: AVAILABLE   Tag: FULL_DB_SUNDAY_NIGHT
        Piece Name: /home1/kcshin/rman/backup/db_t493300635_s31_p1

  List of Datafiles in backup set 21046
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 7238179754301 06-MAY-03 /home2/oradata/ORA920/system01.dbf
  2       Full 7238179754301 06-MAY-03 /home2/oradata/ORA920/undotbs01.dbf

[RMAN backup 내역 예]

RMAN> list backupset of database;
List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
21229   Incr 0  276M       DISK        00:01:06     06-MAY-03
        BP Key: 21232   Status: AVAILABLE   Tag: TAG20030506T155613
        Piece Name: /home1/kcshin/rman/backup/sunday_level0_493314973
  List of Datafiles in backup set 21229
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1    0  Incr 7238179759787 06-MAY-03 /home2/oradata/ORA920/system01.dbf
  4    0  Incr 7238179759787 06-MAY-03 /home2/oradata/ORA920/indx01.dbf
  7    0  Incr 7238179759787 06-MAY-03 /home2/oradata/ORA920/oem_repository.dbf

참고 :
oracle rman forum : Recovery Manager (RMAN)
oracle 10g, 11g new feature : Recovery Manager (RMAN)
Oracle Technical iSeminar : RMAN 개요와 문제 진단

SMON: about to recover undo segment 4
SMON: mark undo segment 4 as available
                    :

select KTUXEUSN,KTUXESLT,KTUXESQN,KTUXERDBF,KTUXERDBB,KTUXESTA,KTUXESIZ
from x$ktuxe
where KTUXEUSN in (select segment_id from dba_rollback_segs where segment_name = '_SYSSMU156$')
and KTUXESTA = 'ACTIVE';

V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.
V$METRIC - Displays metric information.
V$METRICNAME - Displays the metrics associated with each metric group.
V$METRIC_HISTORY - Displays historical metrics.
V$METRICGROUP - Displays all metrics groups.

DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.
DBA_HIST_BASELINE - Displays baseline information.
DBA_HIST_DATABASE_INSTANCE - Displays database environment information.
DBA_HIST_SNAPSHOT - Displays snapshot information.
DBA_HIST_SQL_PLAN - Displays SQL execution plans.
DBA_HIST_WR_CONTROL - Displays AWR settings.

1)The awrrpt.sql SQL script generates an HTML or text report that displays
statistics for a range of snapshot Ids.

2)The awrrpti.sql SQL script generates an HTML or text report that displays
statistics for a range of snapshot Ids on a specified database and instance.

3) The awrsqrpt.sql SQL script generates an HTML or text report that displays
statistics of a particular SQL statement for a range of snapshot Ids. Run this report
to inspect or debug the performance of a SQL statement.

4) The awrsqrpi.sql SQL script generates an HTML or text report that displays
statistics of a particular SQL statement for a range of snapshot Ids on a specified SQL.

5) The awrddrpt.sql SQL script generates an HTML or text report that compares
detailed performance attributes and configuration settings between two selected
time periods.

6) The awrddrpi.sql SQL script generates an HTML or text report that compares
detailed performance attributes and configuration settings between two selected
time periods on a specific database and instance.

How to Modify the AWR SNAP SHOT SETTINGS:
=====================================

BEGIN
  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    retention => 43200,        -- Minutes (= 30 Days). Current value retained if NULL.
    interval  => 30);          -- Minutes. Current value retained if NULL.
END;
/

Creating the Baseline:
======================

BEGIN
  DBMS_WORKLOAD_REPOSITORY.create_baseline (
    start_snap_id => 10,
    end_snap_id   => 100,
    baseline_name => 'AWR First baseline');
END;
/

Dropping the AWR baseline:
==========================

BEGIN
  DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range(
(row_snap_id=>40,
High_snap_id=>80);
END;
/

Creating SNAPSHOT Manually:
===========================

BEGIN
  DBMS_WORKLOAD_REPOSITORY.create_snapshot();
END;
/

위의 내용은 metalink.oracle.com의 아래 문서를 참조했습니다.

note 748642.1 What is AWR( Automatic workload repository ) and How to generate the AWR report?

DBA_DATA_FILES, V$DATAFILE

V$DATAFILE_HEADER

V$BACKUP

v$logfile, v$log

v$archived_log, V$LOG_HISTORY

v$controlfile

v$tablespaces

V$RECOVERY_LOG

V$RECOVER_FILE

V$RECOVERY_FILE_STATUS

V$RECOVERY_PROGRESS

V$RECOVERY_STATUS

V$% views return data from memory structures. Data is lost when the instance is restarted.
DBA_% tables return data from the database's data dictionary (SYSTEM tablespace). Data persists across instance restarts.

V$ & DBA_

Static Data Dictionary Views and Dynamic Performance Views are the core of database administration. They are of crucial importance. Whoever wants to learn Oracle database administration has to get familiar with these views.

The Data Dictionary tables contain meta data on objects, privileges, roles, users etc.
Whenever you issue DML statements Oracle consults its data dictionary to make sure a table is there, it has the right columns and you have privileges on it. And whenever you issue DDL statements Oracle updates its data dictionary.
All information needed by an instance on its database is kept in the data dictionary. Except some storage related information which is in the datafile headers and some information in controlfile and spfile which is needed at instance startup.

At runtime the oracle instance stores information about itself, about its CPU, memory or I/O performance, wait statistics and everything else which is going on at the instance in dynamic performance tables. Dynamic performance tables are non persistent. You can’t see past instance startup. After shutdown or instance crash all information contained in dynamic performance tables is lost. *1)

Through static dictionary views users can get at information kept in data dictionary tables.
And through dynamic performance views users can look at non persistent runtime information contained in dynamic performance tables.

But why are those views that important, you might ask? All information necessary for database administration can be found in those static dictionary views. And basically all information necessary for instance diagnostic and tuning can be obtained through those dynamic performance views at runtime.

Of course, GUI tools like Enterprise Manager or SQL Developer offer the same information
in a more user friendly manner. But all those GUI tools rely on static dictionary views and dynamic performance views.

As a DBA you will likely get into situations where there is no GUI tools available. At times you will be alone with your SQL*Plus skills. Your mastery of data dictionary and performance views will make all the difference!

SQL> desc dbms_xplan
FUNCTION DISPLAY RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 TABLE_NAME                     VARCHAR2                IN     DEFAULT
 STATEMENT_ID                   VARCHAR2                IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT

FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_ID                         VARCHAR2                IN
 PLAN_HASH_VALUE                NUMBER(38)              IN     DEFAULT
 DB_ID                          NUMBER(38)              IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT

FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_ID                         VARCHAR2                IN     DEFAULT
 CURSOR_CHILD_NO                NUMBER(38)              IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT

FUNCTION PREPARE_RECORDS RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 PAR1                           VARCHAR2                IN
 PAR2                           VARCHAR2                IN

<내용>
set pagesize 250
col sid for 9999
col waiting_on for a90
col serial# for 999999
col prg for a19
col OraPid for 9999
col command for a9
col aa Heading "DB-User" for a8
col bb Heading "OS-Pid" for a7

set pau off
select to_char(sysdate,'MM/DD HH24:MI:SS') dat,s.sid SID,s.serial#,sql_hash_value,
decode(s.command,
        '0','NO',
        '1','Cr Tab',
        '2','Insert',
        '3','Select',
        '6','Update',
        '7','Delete',
        '9','Create Idx',
        '10','Drop Idx',
        '15','Alter Tbl',
        '24','Create Proc',
        '32','Create Link',
        '33','Drop Link',
        '36','Create RBS',
        '37','Alter RBS',
        '38','Drop RBS',
        '40','Alter TBS',
        '41','Drop TBS',
        '42','Alter Sess',
        '45','Rollback',
        '47','PL/SQL Exe',
        '62','Anal Table',
        '63','Anal Index',
        '85','Truncate') Command,
substr(s.machine,1,8)||'['|| substr(s.program, 1, 9)||']' prg,
round(q.buffer_gets/q.executions,2) getperexec,
substr(
rtrim(w.event) || ': ' ||
rtrim(p1text,' ') || ' ' || to_char(p1) || ',' ||
rtrim(p2text,' ') || ' ' || to_char(p2) || ',' ||
rtrim(p3text,' ') || ' ' || to_char(p3),1,75) ||',waiting:'||wait_time  as waiting_on
from  v$session s, v$session_wait w,v$sqlarea q
where w.wait_time = 0
and w.sid = s.sid
and s.sql_hash_value = q.hash_value
and event not like '%pmon timer%'
and event not like '%smon timer%'
and event not like '%rdbms ipc message%'
and event not like '%SQL*Net message%'
and event not like '%lock manager wait for%'
and event not like '%slave wait%'
and event not like '%io done%'
and event not like '%pipe get%'
and event not like '%wakeup time manager%'
and event not like '%queue messages%'
order by getperexec
/