Apr-13

April 28

==> Clone ORACLE_HOME

http://www.oraclealchemist.com/news/cloning-your-oracle-installation/

April 25

==> IPMI – Intelligent Platform Management Interface.

IPMI is an on board implementation using Baseboard Management Controllers (BMC), a way to communicate between sub-systems.   In Oracle cluster, if the node unresponsive and CRS decided to evict the node, CSSD daemon will communicate to the OS kernal to re-start the node.  This will create an external dependency for the prblme node eviction.     In 11gR2 Grid infrastructure, we can configure CRS to instruct IPMI on board device to re-start the problem node rather going through the OS kernal.  IPMI configuration information kept in the OLR or Oracle wallet.

To configure CRS

$ /u01/app/11.2.0.3/grid/bin/crsctl set css ipmiadmin radmin
$ IPMI BMC password:
CRS-4229: The IPMI information change was successful

Where radmin is the administrative user for IPMI.

Some points from http://www.vitalsofttech.com/grid-11gr2-ipmi-based-failure-isolation/

Tips from documentation

  • IPMI version 1.5 or later which supports IPMI over LAN and configured for remote control using LAN
  •  For windows 2008 – see the note http://support.microsoft.com/kb/950257
  • Oracle recommends to configure a dedicated network for IPMI or use Private  interconnect netrwork .  Either use static IP and DHCP
  • Each cluster member node’s Ethernet port used by BMC must be connected to the IPMI management network
  • Each cluster member must be connected to the management network
  • IPMI driver for microsoft windows 2008 or later is ipmidrv.sys.  Alternate driver from intel is imbdrv.sys which not tested for Oracle

Install IPMI in linux

# /sbin/modprobe ipmi_msghandler
# /sbin/modprobe ipmi_si
# /sbin/modprobe ipmi_devintf

To check IPMI module in linux

# /sbin/lsmod | grep ipmi
ipmi_devintf 12617 0
ipmi_si 33377 0
ipmi_msghandler 33701 2 ipmi_devintf,ipmi_si

BMC configuration details linix – ipmitool

http://ipmitool.sourceforge.net/

ipmiutil is available for both linux and windows, details are at

http://ipmiutil.sourceforge.net/

# ipmitool bmc info       –> To verify IPMI tool can communicate with BMC

# ipmitool lan print 1    –> to enable IPMI tool over lan

# ipmitool lan set 1 access on   –> Turn on lan access

# ipmitool lan set 1 ipsrc dhcp   –> To set IP address using DHCP

–> To set up static IP address

#ipmitool lan set 1 ipaddr 192.168.0.45

#ipmitool lan set 1 netmask 255.255.255.0

#ipmitool lan set 1 defgw ipaddr 192.168.0.1

# ipmitool lan set 1 auth admin md5, password   –> To set admin password for BMC

# ipmitool channel getaccess 1       –> To lsit BMC unused slots, note the USER_IS number

–> Setup account for messaging for the identified user ID

# ipmitool user set name <USER_ID> IPMIadm

# ipmitool user set password <USER_ID>  <myIPMIpwd>
# ipmitool user enable <USER_ID>
# ipmitool channel setaccess 1 <USER_ID> privilege=4   –> privilege=4  is the ADMINISTRATOR privilege for that slot.

# ipmitool lan print 1     –> To check  setup

# ipmitool -H node1 -U IPMIadm -P IPMIpwd bmc info    –> Check from the remote to confirm BMC is accessible from remote node

More configuration details at : http://docs.oracle.com/cd/E14795_01/doc/rac.112/e10717.pdf

==> Oracle magazine – May-June

http://www.oraclemagazine-digital.com/oraclemagazine/may_june_2013?sub_id=GALZ5xRNfPAP#pg2

April 24

==> V$DIAG_ALERT_EXT – External table for alert log (11gR2)

You can access and mine various log files (alert, listener etc) through the database.  Multiple database alert logs in the same ADR home can be accesses from a single database.


select distinct inst_id,FILENAME from V$DIAG_ALERT_EXT ;

–>  Get last one day alert log / listener log


set pagesize 200
set linesize 180

select MESSAGE_TEXT from V$DIAG_ALERT_EXT
where FILENAME='&file_name' and
ORIGINATING_TIMESTAMP > sysdate -1;


To see different ADR homes

select distinct adr_home  from v$diag_alert_ext;

==> Exadata diskgroup planning – though is an old post, but worth reading

http://blog.oracle-ninja.com/2011/12/exadata-diskgroup-planning/

==> To get linux release

$ cat /etc/redhat-release

==> OLR – Oracle Local registry

OLR is used to start the CRS when the Votedisk and OCR is in ASM.  Each node in the cluster will have its own OLR.  You cal see the OLR location

$ ocrcheck -local                                 –> To check the ORL location

$ ocrdump -local -stdout                –> To list OLR contents

$ ocrconfig -local -export <filename>  –> To export OLR contents

$ ocrconfig -local -import <filename>   –> To import OLR

$ocrconfig -local -repair olr <filename>   –> Repair OLR

$ ocrconfig -local -manualbackup                  –> default abckup path Grid_home/cdata/host_name.

$ ocrconfig -local -backuploc <new _OLR_backup_path>      –> to change the backup loc

Default location for OLR is $GRID_HOME/cdata/hostname.olr

http://docs.oracle.com/cd/E11882_01/rac.112/e16794/votocr.htm#CHDFDGGB

April 23

==> Session waits in RAC for all sessions


set linesize 200
 col event for a30
 col username for a20
 set pagesize 40
 col p1 for 999999999999
 col OSUSER for a12
 select v.inst_id,v.seq#,v.sid,username,v.event,v.p1,v.p2,v.p3,last_call_et,sql_hash_value,sql_id,SQL_CHILD_NUMBER
 from gv$session_wait v, gv$session s where v.event not like '%message%' and s.sid=v.sid and
 s.INST_ID=v.INST_ID and username is not null
 order by sql_id;

==> Waits for a session


set linesize 200
 col event for a30
 col username for a20
 set pagesize 40
 col p1 for 999999999999
 col OSUSER for a12
 select v.inst_id,v.seq#,v.sid,username,v.event,v.p1,v.p2,v.p3,last_call_et,sql_hash_value,sql_id,SQL_CHILD_NUMBER,ROW_WAIT_OBJ#
 from gv$session_wait v, gv$session s where s.sid=v.sid and
 s.INST_ID=v.INST_ID and s.sid=&sid;

==> Waits for a USERNAME


set linesize 200
 col event for a30
 col username for a20
 set pagesize 40
 col p1 for 999999999999
 col OSUSER for a12
 select v.inst_id,v.seq#,v.sid,username,v.event,v.p1,v.p2,v.p3,last_call_et,sql_hash_value,sql_id,SQL_CHILD_NUMBER,ROW_WAIT_OBJ#
 from gv$session_wait v, gv$session s where s.sid=v.sid and
 s.INST_ID=v.INST_ID and s.username='&username';

==> List of local and cluster resources in OCR

$ crsctl status resource -t

==> List of open files in Linux

/usr/sbin/lsos -p <pid>

==> An interesting read – recovering a data file from rm -f  without a restore!

http://jarneil.wordpress.com/2013/04/23/recovering-from-rm-rf-on-a-datafile/

==> “ORA-12537: TNS:connection closed” error due to $ORACLE_HOME/bin/oracle setuid bit is not set.

TNS-12518: TNS:listener could not hand off client connection
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
Linux Error: 32: Broken pipe

Run

$  chmod 6751 /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle

http://blog.oracle-ninja.com/2012/05/why-i-dont-like-role-separated-accounts-part-1/

==> If one of the disk in the ASM dikgroup fails or offline where the votingdisk existing, Oracle will automatically recreate voting disk in another existing disk immediately.  So, it is wise to keep voting disks in a diskgroup with NORMAL redundancy, but keep 5 disks in the diskgroup, even though it will create 3 votingdisks.  Read more

http://blog.oracle-ninja.com/2012/01/voting-disk-redundancy-in-asm/

April 22 

To get the SCAN (Single Client Access Name)name

$srvctl config scan

==> Location of OCR

$ cat /etc/oracle/ocr.loc

also use ocrcheck

April 10

A row in a block dump

tab 0, row 22, @0x1ece
tl: 6 fb: –H-FL– lb: 0x0 cc: 1
col 0: [ 2] c1 18

H –  ==> Block contains header

FL ==> Block contains First and Last row piece

In another block dump  example

tab 0, row 0, @0x1042
tl: 22 fb: —-FL– lb: 0x1 cc: 1
hrid: 0x01000592.c7

the flag ‘H’ is missing and there is only ‘FL’ means this is a migrated row and no row header in this block, means there are only ‘First and Last’ row piece.  ‘hrid’ is the pointer to the block where header is existing.

In the third example

tab 0, row 705, @0x5ba
tl: 9 fb: –H—– lb: 0x3 cc: 0
nrid: 0x010005bc.11

There is only ‘H’ flag means the row piece is migrated from the current block and there is no row piece existing.  The ‘nrid’ stores forward address of the block where row piece is existing.

Table compression and UPDATEs

Created a table Test with basic compression and inserted 1048576 rows.

SQL> create table test (c1 varchar2(50)) compress;
Table created.

My compress table TEST 2632 blocks.  My UPDATEs where went fine as the rows using regular inserted

SQL> update test set c1='AAAAAAAA';
1048576 rows updated.
SQL> commit ;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(user,'TEST');
PL/SQL procedure successfully completed.
SQL> select TABLE_NAME,BLOCKS,NUM_ROWS from user_tables;
TABLE_NAME BLOCKS NUM_ROWS
------------------------------ ---------- ----------
TEST 2732 1048576
SQL> update test set c1='BBBBBBBBBB';
1048576 rows updated.
SQL> commit ;
Commit complete.
SQL> select TABLE_NAME,BLOCKS,NUM_ROWS from user_tables;
TABLE_NAME BLOCKS NUM_ROWS
------------------------------ ---------- ----------
TEST 2732 1048576

To take effect the COMPRESSION, I have moved the table.

SQL> alter table test move ;
Table altered.
SQL> exec dbms_stats.gather_table_stats(user,'TEST');
PL/SQL procedure successfully completed.
SQL> select TABLE_NAME,BLOCKS,NUM_ROWS from user_tables;
TABLE_NAME BLOCKS NUM_ROWS
------------------------------ ---------- ----------
TEST 1439 1048576

And the number of used blocks come down to 1439.    Later I have updated the table with a ‘smaller’ value,

SQL> select * from test where rownum < 5;
C1
--------------------------------------------------
AAAAAAAAAAAAAAA
AAAAAAAAAAAAAAA
AAAAAAAAAAAAAAA
AAAAAAAAAAAAAAA
SQL> update test set c1='BBBBB';
1048576 rows updated.
SQL> commit ;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(user,'TEST');
PL/SQL procedure successfully completed.
SQL> select TABLE_NAME,BLOCKS,NUM_ROWS from user_tables;
TABLE_NAME BLOCKS NUM_ROWS
------------------------------ ---------- ----------
TEST 7719 1048576

The number of used table blocks gone to 7719 blocks!  That is the effect of UPDATE on compressed table.   So, compressed table should be used only a READ ONLY tables not for updates.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: