Monday Oct 29, 2012

Exadata X3 Expandability Update

Exadata Database Machine X3-2 Data Sheet New 10/29/2012

Up to 18 racks can be connected without requiring additional InfiniBand switches.

Exadata Database Machine X3-8 Data Sheet New 10/24/2012

Scale by connecting multiple Exadata Database Machine X3-8 racks or Exadata Storage Expansion Racks. Up to 18 racks can be connected by simply connecting via InfiniBand cables. Larger configurations can be built with additional InfiniBand switches.


 

White Paper for SAP on Oracle Database Features & Options

SAP on Oracle Development Update New Oct2013

RAT for SAP

RAC for SAP

OCFS for SAP

DG for SAP

OEM for SAP

Partition for SAP

Security for SAP

ODV for SAP 

TDE for SAP 

AC for SAP

ASM for SAP

Monday Oct 15, 2012

Using CTAS & Exchange Partition Replace IAS for Copying Partition on Exadata

Usage Scenario:

Copy data&index from one partition to another partition in a partitioned table.

Solution:

  1. Create a partition definition
    Copy data from one partition to another partiton by 'Insert as select (IAS)'
  2. Create a nonpartitioned table by 'Create table as select (CTAS)'
    Convert a nonpartitioned table into a partition of partitoned table by exchangng their data segments.
    Rebuild unusable index

Exchange Partition Convertion

  1. Mutual convertion between a partition (or subpartition) and a nonpartitioned table
  2. Mutual convertion between a hash-partitioned table and a partition of a composite *-hash partitioned table
  3. Mutual convertiton a [range | list]-partitioned table into a partition of a composite *-[range | list] partitioned table.

Exchange Partition Usage Scenario

  1. High-speed data loading of new, incremental data into an existing partitioned table in DW environment
  2. Exchanging old data partitions out of a partitioned table, the data is purged from the partitioned table without actually being deleted and can be archived separately

Exchange Partition Syntax

ALTER TABLE schema.table EXCHANGE [PARTITION|SUBPARTITION] [partition|subprtition] WITH TABLE schema.table [INCLUDE|EXCLUDING] INDEX [WITH|WITHOUT] VALIDATION UPDATE [INDEXES|GLOBAL INDEXES]

INCLUDING | EXCLUDING INDEXES Specify INCLUDING INDEXES if you want local index partitions or subpartitions to be exchanged with the corresponding table index (for a nonpartitioned table) or local indexes (for a hash-partitioned table). Specify EXCLUDING INDEXES if you want all index partitions or subpartitions corresponding to the partition and all the regular indexes and index partitions on the exchanged table to be marked UNUSABLE. If you omit this clause, then the default is EXCLUDING INDEXES.

WITH | WITHOUT VALIDATION Specify WITH VALIDATION if you want Oracle Database to return an error if any rows in the exchanged table do not map into
partitions or subpartitions being exchanged. Specify WITHOUT VALIDATION if you do not want Oracle Database to check the proper mapping of rows in the exchanged table. If you omit this clause, then the default is WITH VALIDATION. 

UPADATE INDEX|GLOBAL INDEX Unless you specify UPDATE INDEXES, the database marks UNUSABLE the global indexes or all global index partitions on the table whose partition is being exchanged. Global indexes or global index partitions on the table being exchanged remain invalidated. (You cannot use UPDATE INDEXES for index-organized tables. Use UPDATE GLOBAL INDEXES instead.)

Exchanging Partitions&Subpartitions Notes

  1. Both tables involved in the exchange must have the same primary key, and no validated foreign keys can be referencing either of the tables unless the referenced
    table is empty.
  2.  When exchanging partitioned index-organized tables:
    – The source and target table or partition must have their primary key set on the same columns, in the same order.
    – If key compression is enabled, then it must be enabled for both the source and the target, and with the same prefix length.
    – Both the source and target must be index organized.
    – Both the source and target must have overflow segments, or neither can have overflow segments. Also, both the source and target must have mapping
    tables, or neither can have a mapping table.
    – Both the source and target must have identical storage attributes for any LOB columns. 

Sunday Oct 14, 2012

Partition Table and Exadata Hybrid Columnar Compression (EHCC)

  • Create EHCC table
    • CREATE TABLE ... COMPRESS FOR [QUERY LOW|QUERY HIGH|ARCHIVE LOW|ARCHIVE HIGH];

      select owner,table_name,compress_for DBA_TABLES where compression = ‘ENABLED';
  • Convert Table/Partition/Subpartition to EHCC
    • Compress Table&Partition&Subpartition to EHCC:
      ALTER TABLE table_name MOVE COMPRESS FOR [QUERY LOW|QUERY HIGH|ARCHIVE LOW|ARCHIVE HIGH] [PARALLEL <dop>];
      ALTER TABLE table_name MOVE PARATITION partition_name COMPRESS FOR [QUERY LOW|QUERY HIGH|ARCHIVE LOW|ARCHIVE HIGH] [PARALLEL <dop>];
      ALTER TABLE table_name MOVE SUBPARATITION subpartition_name COMPRESS FOR [QUERY LOW|QUERY HIGH|ARCHIVE LOW|ARCHIVE HIGH] [PARALLEL <dop>];

      select owner,table_name,compress_for DBA_TABLES where compression = ‘ENABLED';
      select table_owner,table_name,partition_name,compress_for DBA_TAB_PARTITIONS where compression = ‘ENABLED’;
      select table_owner,table_name,subpartition_name,compress_for DBA_TAB_SUBPARTITIONS where compression = ‘ENABLED’;

    • Rebuild Unusable Index:
      select index_name from dba_indexes where status = 'UNUSABLE';
      select index_name,partition_name from dba_ind_partitions where status = 'UNUSABLE';
      select index_name,subpartition_name from dba_ind_subpartitions where status = 'UNUSABLE';

      ALTER INDEX index_name REBUILD [PARALLEL <dop>];
      ALTER INDEX index_name REBUILD PARTITION partition_name [PARALLEL <dop>];
      ALTER INDEX index_name REBUILD SUBPARTITION subpartition_name [PARALLEL <dop>];
  • Convert Table/Partition/Subpartition from EHCC to OLTP compression or uncompressed format:
    • Uncompress EHCC Table&Partition&Subpartition:
      ALTER TABLE table_name MOVE [NOCOMPRESS|COMPRESS for OLTP] [PARALLEL <dop>];
      ALTER TABLE table_name MOVE PARTITION partition_name [NOCOMPRESS|COMPRESS for OLTP] [PARALLEL <dop>];
      ALTER TABLE table_name MOVE SUBPARTITION subpartition_name [NOCOMPRESS|COMPRESS for OLTP] [PARALLEL <dop>];

      select owner,table_name,compress_for DBA_TABLES where compression = '';
      select table_owner,table_name,partition_name,compress_for DBA_TAB_PARTITIONS where compression = '';
      select table_owner,table_name,subpartition_name,compress_for DBA_TAB_SUBPARTITIONS where compression = '';
    • Rebuild Unusable Index:
      select index_name from dba_indexes where status = 'UNUSABLE';
      select index_name,partition_name from dba_ind_partitions where status = 'UNUSABLE';
      select index_name,subpartition_name from dba_ind_subpartitions where status = 'UNUSABLE';

      ALTER INDEX index_name REBUILD [PARALLEL <dop>];
      ALTER INDEX index_name REBUILD PARTITION partition_name [PARALLEL <dop>];
      ALTER INDEX index_name REBUILD SUBPARTITION subpartition_name [PARALLEL <dop>];

Friday Oct 12, 2012

SAP Software on Oracle Exadata X3

Note 1590515 - SAP Software and Oracle Exadata New v17 04.10.2012

Oracle Exadata Database Machine can be used in SAP environments when the following conditions are met:

  • Supported Oracle Exadata Database Machine models:
    • X2-2
    • X2-8
    • X3-2
    • X3-8

Wednesday Oct 10, 2012

Document Link about Database Features on Exadata

DBFS(Database File System) on Exadata

DBRM/IORM(Database Resource Manager, I/O Resource Manager) on Exadata 

EHCC(Exadata Hybrid Columnar Compression) on Exadata 
 
PX(Paralle Execution) on Exadata

Partition on Exadata

FTP on Exadata

VNC on Exadata

  • Enable database hosts in Exadata Database Machine for using up2date or yum and vncserver [ID 1234710.1]  

XP(Exchange Partition) on Exadata

OGG(Oracle Golden Gate) on Exadata

BR(Backup & Recovery) on Exadata

OEM(Oracle Enterprise Manager) on Exadata

MAA(Maximum Availability Architecture) on Exadata

DG(Data Guard) on Exadata

Consolidation on Exadata

IB(InfiniBand) on Exadata

About

Bandari Huang | Exadata Solution Consultant Phone: +86-21-2302-3838 | Mobile: +86-156-9213-8783 Oracle Exadata & Strategic Solutions Consulting, Great China

Search

Categories
Archives
« October 2012 »
SunMonTueWedThuFriSat
 
1
2
3
4
5
6
7
8
9
11
13
16
17
18
19
20
21
22
23
24
25
26
27
28
30
31
   
       
Today