Jan-14
24-Jan
ASM and Exadata
http://asmsupportguy.blogspot.in/2012/04/asm-in-exadata.html
Grid disks are not seen by operating System, rather only Oracle ASM can see the Grid disks via oracle proprietary protocol iDB.
Kfod : Grid disk discovery tools.
$ kfod disks =all è To list all Grid disks
CELL commands
http://www.oracle.com/technetwork/articles/oem/exadata-commands-part3-402445.html
http://www.oracle.com/technetwork/articles/oem/exadata-commands-intro-402431.html
21-Jan
To create a grid disk in all HARDDISKs
Cellcli> create griddisk all harddisk prefix=’DATA’ size 200M;
To create a grid disk on a specific hard disk
Cellcli> create griddisk DATA_DG_CD_00cell01 celldisk=’CD_00_cell01’, size 200m
Grid disks and its attributes can be listed
Cellcli> list griddisk attributes name,celldisk, disktype, size
Create a flash grid disk
Cellcli> create griddisk all flashdisk prefix=’FLASHDISK’
20-Jan
Apache Cassandra tutorials
http://www.datastax.com/resources/tutorials
ILOM: Exadata ILOM (Integrated Lights Out Manager) is an adaptor card in addition to the management network interface (NET0) in each storage server and database server. ILOM boots as soon as the server power is up and operates independently of OS allowing web and SSh access. The ILOM allows the DBA to perform many management tasks remotely otherwise require physical access to the server including Console access, power up, power down systems, re-booting servers etc. ILOM is capable to monitor the H/W functioning and report if there is a failure. ILOM is linked to the management switch using a Ethernet port.
Oracle Exadata documentation is available at /usr/share/doc/oracle/Exadata in all storage servers and not available publically.
To list all CELL physical disks
Cellcli –e list physicaldisk
Shutdown /startup all cell services
Cellcli> alter cell shutdown services all; Cellcli> alter cell startup services all;
16-Jan
Transaction guard – oracle 12c
Oracle 12c – Application continuity
http://www.oracle.com/technetwork/database/options/clustering/ac-overview-1967264.html
Some x$ table references..
http://yong321.freeshell.org/computer/x$table.html
x$ tables – from where do they comes from
Playing with baseline
http://oracleprof.blogspot.in/2011/07/how-to-find-sqlid-and-planhashvalue-in.html
10-Jan
In oracle 11g onwards, tracing 10046 is enhanced. You can trace a OS level process by
SQL> ALTER SESSION SET EVENTS 'SQL_TRACE {PROCESS:5729} ';
Session altered.
SQL> ALTER SESSION SET EVENTS 'SQL_TRACE {PROCESS:5729} OFF' ;
Session altered.
Where 5729 is an OS level process identifier.
You can also trace 10046 level for a specific SQL using SQL_ID
SQL> alter session set events 'sql_trace [SQL:5m6mu5pd9w028] off ' ; Session altered.
You can also concatenate multiple SQLs trace in single event.
SQL> alter session set events 'sql_trace [sql:5m6mu5pd9w028|1fkh93md0802n] ' ; Session altered.
Using a Oracle process ID you can trace a session
SQL> select paddr from v$session where username='THOMAS' ;
PADDR
----------------
00000000EC503F20
SQL> select pid from v$process where addr='00000000EC503F20';
PID
----------
28
SQL> alter session set events 'sql_trace {process:orapid=28 }' ;
Session altered.
SQL> alter session set events 'sql_trace {process:orapid=28 } off';
Session altered.
Use pname to trace session as,
SQL> alter session set events 'sql_trace {process:pname=PMON} ' ;
Session altered.
SQL> alter session set events 'sql_trace {process:pname=PMON} off ';
Session altered.
You can also specify to capture waits and binds
SQL> alter session set events 'sql_trace {process:pname=PMON} wait=true,bind=true,level=12';
Session altered.
09-Jan
Flash Cache can be created and listed using CELCLI command. First create cell disks in the PCI flash devices
CELLLCLI> create celldisk all flashdisk
To list all the cell disks
CELLCLI> LIST CELLDISK ATTRIBUTES name, disktype, size where name like ‘FD.*’;
To create a smart flash cache in all Cell disks
CELLCLI> CREATE FLASHCACHE ALL
To create flash cache on selected Cell Disks and size
CELLCLI> create flashcache celldisk =’cell_disk_1, cell_disk_2, cell_disk_3.. ‘, size 30G
To list out the flash cache details
CELLCLI> list flashcache detail
In each storage node there are 4 PCI solid state flash modules (Fmods – F20 cards) each sizing 32GB, but 22.875 GB usable storage. Each Fmods contain 64MB controller Ram (volatile) memory and eight 4GB SLC NAND components. The 64MB RAM is powered by a ESM (Energy Storage Module) using a capacitor to avoid data corruption by sudden power loss. This 64MB controller RAM buffers the data writes to the Flash module and will write directly to the SLC NAND if ESM is not functional which will cause reduced I/O performance. The IOLM (Integrated Lights Out manager) module monitors the ESM and send warnings if there are any issues
CellCLI> LIST CELLDISK ATTRIBUTES name, disktype, size where name like 'FD.*'; FD_00_ct01cel01 FlashDisk 22.875G FD_01_ct01cel01 FlashDisk 22.875G FD_02_ct01cel01 FlashDisk 22.875G FD_03_ct01cel01 FlashDisk 22.875G FD_04_ct01cel01 FlashDisk 22.875G FD_05_ct01cel01 FlashDisk 22.875G FD_06_ct01cel01 FlashDisk 22.875G FD_07_ct01cel01 FlashDisk 22.875G FD_08_ct01cel01 FlashDisk 22.875G FD_09_ct01cel01 FlashDisk 22.875G FD_10_ct01cel01 FlashDisk 22.875G FD_11_ct01cel01 FlashDisk 22.875G FD_12_ct01cel01 FlashDisk 22.875G FD_13_ct01cel01 FlashDisk 22.875G FD_14_ct01cel01 FlashDisk 22.875G FD_15_ct01cel01 FlashDisk 22.875G
Smart flash cache read statistics are available in V$SYSSTAT under “CELL FLASH CACHE READ HITS”.
The PCI Flash memory can be used in two ways – as intermediate cache to store data (Smart Flash Cache) to speed up the random access reads or define as SSD (Solid State Disks) under ASM to store the database files.
With write back flash cache feature, the Exadata smart flash cache buffers database blocks writes. Write caching eliminates disk bottlenecks in large OLTP systems and get constant write I/O performance. The Exadata write cache is transparent, persistent and fully redundant.
Exadata smart flash cache logging feature helps to reduce log I/O latency issues. Exadata flash ache logging combined with high speed disk controllers helps to fast response to a committing session completing the transaction faster.
You can use any of the flowing syntax based on the requirement.
CELLCLI> CREATE FALSHLOG ALL CELLCLI> CREATE FALSHLOG ALL SIZE = 1G CELLCLI> CRAETE FALSHLOG CELDISK=’<disk1>,<disk2>…. <disk n>’ CELCLI> CRAETE FALSHLOG CELDISK=’<disk1>,<disk2>…. <disk n>’ SIZE =1G
You can get information about flash logging using,
[ode]
CELLCLI> LIST FALSHLOG [attributes] DETAIL
CELLCLI> LIST FLASHLOG
CELLCLI> LIST FLASHLOG DETALS
[/code]
You can get cell storage level (individual cell storage) metric statistics querying
CELLCLI> LIST METRICDEFINITON attributes name, description where OBEJCTYPE=’FLASHCACHE’ CELLCLI> LIST METRICCURRENT where objecttype=’FLASHCACHE’
You can list the number ESFC content by querying,
CELLCLI> LIST FLASHCACHECONTENT WHERE dbuniquename=’<name>’ ATTRIBUTES <attr1, attr2.. attrn>’
You can list the attributes by
CELLCLI> describe flashcachecontent
07-Jan
Exadata Smart Flash Cache: Is ultra performance Flash Cache built on PCI flash cards directly attached to the high speed PCI bus. The Exadata smart flash cache automatically caches frequently accessed data in the flash cache. In addition to automatic caching, DBA can specify to cache tables and indexes explicitly. The table creation option KEEP will place the object in the cache while NONE will avoid using cache and DEFAULT will leads oracle to take a decision to cache the data or not. You can check the object status in cache using
Cellcli> list flashcachecontent where object_number = <object_id> details;
To list value of CacheKeepSize
Celcli –e list flashcachecontent attributes dbuniquename, objectnumber, cachedkeepsize, cachedsize, hitcount, misscount;
You can enable object level caching using
ALTER TABLE <table name> STORAGE (CELL_FLASH_CACHE KEEP/DEFAULT/NONE;
You can get the TABLE / INDEX level caching enabled by querying – DBA_TABLES/DBA_INDEXES
SQL> SELECT table_name, cell_flash_cache FROM DBA_TABLES;
06-Jan
While ADDing EXTRACT in a RAC environment, you should specify the number of THREADS in the RAC environment.
[Code]
GGSCI> ADD EXTRACT myext, TRANLOG, THREADS 2, BEGIN NOW
[/code]
While the REDO/ARCH files are in ASM environment, extract need to be configured access the ASM instance and can be done using TRANLOG OPTIONS. To configure ASM environment for extract,
TRANLOGOPTIONS ASMUSER “sys@asmdb”, ASMPASSWORD “<encrypted password> “ ENCRYPTKEY default
Use ARCHIVEDLOGFORMATS with TRANLOGOPTIONS to configure alternate location for archive logs for GG. You can configure multiple archive log locations; so that the extract will continue to mine even if the archive log files are moved to non-default secondary location. PRIMARY keyword will skip the DEFAULT log location and will look for log files in the specified path.
TRANLOGOPTIONS ALTARCHIVELOGDEST PRIMARY INSTANCE rac1 /disk1/node1/arch, ALTARCHIVELOGDEST INSTANCE rac1 /disk2/node1/arch, ALTARCHIVELOGDEST INSTANCE rac2 /disk1/node2/arch
Thomas Saviour's Blog 