ORACLE - RMAN-06019 :Tablespace Restore issue

Task : Recover dropped Tablesapce or Restore New Tablespace at another database

> rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Mar 19 10:56:40 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: DBNAME (DBID=1948148797)

RMAN> run
{
allocate channel c1 type disk format '/bkup/dbname/rman/rman_%d_%T_%u.bk';
allocate channel c2 type disk format '/bkup/dbname/rman/rman_%d_%T_%u.bk';
restore tablespace TABLESPACE_NAME;
recover tablespace TABLESPACE_NAME;
release channel c1;
release channel c2;
}

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=676 device type=DISK
allocated channel: c2
channel c2: SID=1060 device type=DISK
Starting restore at 19-MAR-13
released channel: c1
released channel: c2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ==============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/19/2013 10:57:05
RMAN-20202: Tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name "TABLESPACE_NAME"
RMAN>

Solution:
 Control file does not has record for dropped Tablspace or New Tablespace henace RMAN-06019.
[RMAN-06019: could not translate tablespace name "TABLESPACE_NAME"]
1)Create a clone database using backup and expdp the tables or tablespace to the existing database.
2)Do a Point In Time Recovery (TSPITR) of the whole database until the time the tablespace was dropped.

Oracle - Create Password File

orapwd file=orapwDBNAME entries=100 ignorecase=y password=PASSWORD
alter user sys identified by PASSWORD;
alter user system identified by PASSWORD;

ORACLE - Partitions

Oracle Partitions Overview:

INTERVAL Partition:

CREATE TABLE Schema.TableName
(
  TRANSACTIONTIME       TIMESTAMP(6),
  Col2                  CHAR(1 CHAR),
  col3                  VARCHAR2(50)
)
PARTITION BY RANGE (TRANSACTIONTIME)
INTERVAL (NUMTODSINTERVAL(15,'DAY'))
(partition part_data_0 values less than (TO_DATE('01-JAN-2011','DD-MON-YYYY'))
) TABLESPACE DATA;

Note:
1.Only one partitioning key column, and it must be of NUMBER or DATE type.
2.The optional STORE IN clause lets you specify one or more table-spaces into which the database stores interval partition data using a round-robin algorithm

List Partition:

CREATE TABLE Hotel_Region
      (ID number,
       quarterly_sales number(10, 2),
       state varchar2(2))
PARTITION BY LIST (state)
      (PARTITION p1 VALUES ('CA', 'WA'),
       PARTITION p2 VALUES ('AZ', 'NJ', 'NY'),
       PARTITION p3 VALUES  ('FL', 'SD', 'WI'),
       PARTITION p4 VALUES ('TX', 'GA'));

Local Index:
CREATE UNIQUE INDEX INDEX_NAME ON TableName (Col_Name)
TABLESPACE TBS01 LOCAL PARALLEL NOLOGGING;

Global Index Non-Partion Index:
CREATE INDEX GINDX ON Hotel_Region (ID);

Global Index Partion Index:CREATE INDEX GINDX ON Hotel_Region (ID)
PARTITION BY LIST (ID)
(PARTITION p1 VALUES ('CA', 'WA'),
PARTITION p2 VALUES ('AZ', 'NJ', 'NY'));

View Partiton and Indexes;
Select index_name,partitioned, index_type from user_indexes where table_name=<table_name>;
Select PARTITION_NAME, num_rows from dba_tab_partitions where TABLE_OWNER = 'Schema' and TABLE_NAME IN  (  'Table_Name');

ORACLE - ASM Fast Mirror Resync

ASM FAST MIRROR RESYNC :

                             ASM fast resync keeps track of pending changes to extents on an OFFLINE disk during an outage. The extents are resynced when the disk is brought back online.By default, ASM drops a disk in 3.6 hours after it is taken offline. You can set the DISK_REPAIR_TIME attribute to delay the drop operation by specifying a time interval to repair the disk and bring it back online.

Example:
ALTER DISKGROUP DG1 OFFLINE DISK DG1_01
ALTER DISKGROUP DG01 SET ATTRIBUTE 'DISK_REPAIR_YIME' = '4.5h';
SELECT REPAIR_TIMER FROM V$ASM_DISK;
SELECT GROUP_NUMBER,NAME,VALUES FROM V$ASM_ATTRIBUTE;
SELECT GROUP_NUMBER, OPERATION, STATE FROM V$ASM_OPERATION;
ALTER DISKGROUP DG1 ONLINE DISK DG1_01;

ORACLE - EXADATA - Storage Index

Exadata Storagne Index overview:
                                  A storage index is a memory-based structure that reduces the amount of physical I/O required by the cell.

                                 Storage Indexes consist of a minimum and a maximum value for up to 8 columns. This structure is maintained for 1MB chunks of storage (storage region). Storage Indexes are stored in memory only and are never written to disk. Storage index works transparently with no user input. There is no need to create/drop or tune the storage index. Storage index is kept in memory, it disappears when the cell is re-booted. The first queries that run after a cell rebooted automatically cause the storage index to rebuild.

The storage index works best when the following conditions are true:
1. Data is roughly ordered (so that same column values are cluster together)
2.Query has a predicate on a storage index column checking for =, <, > or combination of these.

Monitoring Storage Indexes:

1) Use v$sesstat, v$sysstat  and v$sql:
select name, values from v$mystat a, v$statname b
where b.statistic# = s.statistic# and name like '%storage%';

NAME                                                                                                VALUE
---------------------------------------------------------------------- --------------
cell physical IO bytes saved by storage index                                       0
Note : (after Query run)                                                                       5095009234

Cell physical IO bytes saved by storage index : Shows how many bytes of I/O were eliminated by the application of storage indexes at the storage cell level.

Cell physical IO interconnect bytes returned by smart scan : Shows how many bytes of I/O were returned by a smart scan to the database server.

2. Tracing:
Use _CELL_STROAGE_INDEX_DIAG_MODE=TRUE in cellinit.ora file (restart the cellsrv)

Use full comparison operator:
Storage Index can evaluate predicates like =, <, >, <=, >=, BETWEEN, IS NULL, IS NOT NULL

Limitation:
Storage Index DO NOT work with: !=, % (wildcards) and CLOBs

Ref. Oracle Docs./Metalink

ORACLE - EXADATA - Cellcli and dcli

Cellcli and dcli Overview: 
                               EXADATA storage software uses the CELLCLI utility as its command line interface. It is to a cell what SQL * PLUS is to a database. CELLCLI use for a storage cell and DCLI for all cells i.e. (multiple cells in parallel threads)

How to login to a cell ?
1. Using a ssh clinet (putty) open up a session and go to a assign IP address:
ssh  <cell.ip.address> -l cellmonitor
or
cellcli -e list celldisk

2.Cellcli> list celldisk
3.CellCLI>help
4.CellCLI>list cell detail
5.CellCLI>list lun
6.CellCLI>list phycicaldisk
7.CellCLI>list griddisk detail

How to monitor the cell mertics ?

1. describe metriccurrent
2. describe metrichistory

CellCLI> list metriccurrent attribute name, metricValue where name =  
CellCLI> list metrichistory attribute  where name =

How to display ALERTDEFINATION ?

CellCLI> list alertdefination
CellCLI> list alertdefinationHardwareAlert detail
CellCLI> list alertdefinationattributes name, metricName, description

DCLI : User celladmin or root (not permitted as cellmonitor user):
dcli -h
dcli -g mycells -l celladmin date
dcli -g mycells -l celladmin - cellcli -e list celldisk
dcli -g mycells -l celladmin --vmstat="-a 5 5" 

Startup/Shutdown the cell services:
CellCLI>alter cell startup service all
CellCLI>alter cell shutdown service all


Example:
list physicaldisk detail
name:                                  20:0
deviceId:                             31
diskType:                            HardDisk
enclosureDeviceId:              20
errMediaCount:                   0
errOtherCount:                    0
foreignState:                         false
luns:                                    0_0
makeModel:                       "SEAGATE ST32000SSSUN2.0T"
physicalFirmware:               061A
physicalInsertTime:             2011-06-16T08:07:59-04:00
physicalInterface:                sas
physicalSerial:                     L5LK38
physicalSize:                       1862.6559999994934G
slotNumber:                        0
status:                                 normal

name:                                 FLASH_1_0
diskType:                           FlashDisk
errCmdTimeoutCount:       0
errHardReadCount:           0
errHardWriteCount:          0
errMediaCount:                 0
errOtherCount:                  0
errSeekCount:                   0
luns:                                  1_0
makeModel:                      "Sun Flash Accelerator F20 PCIe Card"
physicalFirmware:             D21Y
physicalInsertTime:           2011-06-16T08:08:57-04:00
physicalSerial:                  1101M062HU
physicalSize:                    22.8880615234375G
sectorRemapCount:         0
slotNumber:                     "PCI Slot: 1; FDOM: 0"
status:                              normal

list celldisk detail
name:                             CD_00_<cell_node>
comment:
creationTime:                 2011-06-30T15:49:02-04:00
deviceName:                 /dev/sda
devicePartition:             /dev/sda3
diskType:                     HardDisk
errorCount:                  0
freeSpace:                   0
id:                                3aa2ddcc-0576-47e8-b1e5-c61ca64b3811
interleaving:                  none
lun:                              0_0
physicalDisk:               L5LK38
raidLevel:                    0
size:                            1832.59375G
status:                         normal

name:                          FD_00_<cell_node>
comment:
creationTime:              2011-06-30T15:49:13-04:00
deviceName:              /dev/sdr
devicePartition:           /dev/sdr
diskType:                   FlashDisk
errorCount:                0
freeSpace:                 0
id:                              fc092196-47d4-4836-86e4-7133d77dcc83
interleaving:                none
lun:                            1_0
physicalDisk:             1101M062HU
size:                           22.875G
status:                        normal

list physicaldisk attributes name, disktype, makemodel, physicalrpm, physicalport, status
20:0                   HardDisk        "SEAGATE ST32000SSSUN2.0T"              normal
20:1                   HardDisk        "SEAGATE ST32000SSSUN2.0T"              normal
20:2                   HardDisk        "SEAGATE ST32000SSSUN2.0T"              normal
FLASH_1_0     FlashDisk       "Sun Flash Accelerator F20 PCIe Card"       normal
FLASH_1_1     FlashDisk       "Sun Flash Accelerator F20 PCIe Card"       normal

describe physicaldisk
name
ctrlFirmware
ctrlHwVersion
deviceId
diskType
enclosureDeviceId
errCmdTimeoutCount
errHardReadCount
errHardWriteCount
errMediaCount
errOtherCount
errSeekCount
errorCount
foreignState
hotPlugCount
lastFailureReason
luns
makeModel
notPresentSince
physicalFirmware
physicalInsertTime
physicalInterface
physicalPort
physicalRPM
physicalSerial
physicalSize
physicalUseType
sectorRemapCount
slotNumber
status

checking error:
list physicaldisk attributes name,disktype,errCmdTimeoutCount,errHardReadCount,errHardWriteCount
20:0                   HardDisk
20:1                   HardDisk
FLASH_1_0     FlashDisk       0       0       0
FLASH_1_1     FlashDisk       0       0       0

Filtering :
list physicaldisk attributes name, physicalInterface, physicalInsertTime where disktype = 'HardDisk'
20:0          sas     2011-06-16T08:07:59-04:00
20:1          sas     2011-06-16T08:08:05-04:00
20:2          sas     2011-06-16T08:08:11-04:00

ORACLE - Exadata INTERLEAVED DISKS

 What is Exadata INTERLEAVED DISKS ?

                            By default, space for grid disks is allocated from the outer tracks to the inner tracks of a physical disk.However, space for grid disks can be allocated in an interleaved manner.Grid disks that use this type of space allocation are referred to as interleaved gid disks.

                            Interleaved grid disks are best used in situations where you want to create separate ASM disk groups that share cell disks without performance bias.

Note: ORACLE ACS setup griddisks without interleaving.

Example:
create celldisk all harddisk interleaving='normal_redundancy'
create griddisk all prefix=DATA, size=200M
create griddisk all prefix=RECO, size=200M
list celldisk CD_00_<cell_node> detail
name:                     CD_00_<cell_node>
comment:
creationTime:         2011-06-30T15:49:02-04:00
deviceName:           /dev/sda
devicePartition:       /dev/sda3
diskType:               HardDisk
errorCount:            0
freeSpace:              0
id:                          3aa2ddcc-0576-47e8-b1e5-c61ca64b3811
interleaving:         normal_redundancylun:                        0_0
physicalDisk:         L5LK38
raidLevel:              0
size:                      1832.59375G
status:                   normal