Quick example on Oracle logmnr (Log Miner)

begin
dbms_logmnr.add_logfile(logfilename=>'+DATA/orcl/archivelog/2011_06_30/thread_1_seq_400.522.755203825',
options=>dbms_logmnr.new);
end;
/

begin
dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
end;
/
select substr(sql_redo,0,100), count(*) from v$logmnr_contents
group by substr(sql_redo,0,100);

begin
dbms_logmnr.end_logmnr();
end;
/

ORA-29701 raised in ASM I/O path; terminating process

[ID 1328629.1]

Symptoms
You have logged in to the system via an OS user other than Grid and RDBMS Home Onwer. You have made an local bequeath connection to
the database and running SQL like create tablespace, creating tables, creating indexes.

bash-3.00$ id
uid=4600(u500) gid=1527(u500)
bash-3.00$ export ORACLE_SID=HA112
bash-3.00$ export ORACLE_HOME=/refresh/oracle/app/oracle/product/11.2.0
bash-3.00$ export PATH=$PATH:$ORACLE_HOME/bin

bash-3.00$ sqlplus /nolog
SQL*Plus: Release 11.2.0.2.0 Production on Tue Jun 7 14:59:59 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL> conn u5/u5
Connected.
SQL> create tablespace ts5 datafile '+DATA';
create tablespace ts5 datafile '+DATA'
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 6291584
Session ID: 29 Serial number: 241


Database alert.log shows
Tue Jun 07 15:00:56 2011
create tablespace ts5 datafile '+DATA'
ERROR: unrecoverable error ORA-29701 raised in ASM I/O path; terminating process 6291584
The trace file shows
2011-06-07 15:00:57.349: [GIPCXCPT] gipcmodClsaAuthStart: failuring during clsaauthmsg ret clsaretOSD (8), endp 110e72290
[0000000000000018] { gipcEndpoint : localAddr 'clsc://(
ADDRESS=(PROTOCOL=ipc)(KEY=)(GIPCID=36cb13cc-0aeca9ae-6291584))', remoteAddr 'clsc://(ADDRESS=(PROTOCOL=ipc)
(KEY=OCSSD_LL_ceaixcb9_)(GIPCID=0aeca9ae-36cb13cc-9044020))', numPend
5, numReady 0, numDone 2, numDead 0, numTransfer 0, objFlags 0x0, pidPeer 9044020, flags 0x2ca712, usrFlags 0x34000 }
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&do...
1 of 2 6/29/2011 8:29 PM
2011-06-07 15:00:57.388: [GIPCXCPT] gipcmodClsaAuthStart: slos op : open
2011-06-07 15:00:57.388: [GIPCXCPT] gipcmodClsaAuthStart: slos dep : Permission denied (13)
2011-06-07 15:00:57.388: [GIPCXCPT] gipcmodClsaAuthStart: slos loc : authrespset3
2011-06-07 15:00:57.388: [GIPCXCPT] gipcmodClsaAuthStart: slos info: failed to open
2011-06-07 15:00:57.389: [ CSSCLNT]clssscConnect: gipc request failed with 22 (12)
2011-06-07 15:00:57.389: [ CSSCLNT]clsssInitNative: connect to (ADDRESS=(PROTOCOL=ipc)(KEY=OCSSD_LL_ceaixcb9_)) failed, rc
22
kgxgncin: CLSS init failed with status 3
kgxgncin: return status 3 (1311719766 SKGXN not av) from CLSS
NOTE: kfmsInit: ASM failed to initialize group services
Error ORA-29701 signaled at
ksedsts()+644<-ksf_short_stack()+88<-kge_snap_callstack()+56<-kge_sigtrace_dump()+56<-kgepop()+72<-kgeselv()+116<-ksesecl0()+80<-kfmsInit()+176<-SlvReg()+436<-kfmdSlvOpPriv()+4688<-kfmdWriteSubmitted()+1132<-kfk_process_an_ioq()+268<-kfk_submit_io()+80<-kfk_io1()+1004<-kfkRequest()+28<-)+5016<-kfioRequestPriv()+236<-kfioRequest()+620<-ksfd_kfioRequest()+576<-ksfd_osmcrt()+2500<-ksfd_create1()+1516<-ksfd_create()+224<-ksfdcre()+<-tbsafl()+1596<-ctsdrv1()+3972<-ctsdrv()+20<-opiexe()+14916<-opiosq0()+9116<-kpooprx()+400<-kpoal8()+1028<-opiodr()+3608<-ttcpip()+4628<-opitsk()+3608<-opidrv()+1200<-sou2o()+192<-opimai_real()+428<-ssthrdmain()+340<-main()+216<-__start()+112 ERROR: unrecoverable error ORA-29701 raised in ASM I/O path; terminating process 6291584

Cause

Bug 12536779 discusses this issue. On 11.2.0.2 SIHA there is removal of world read/write/execute permissions on a few directories and that is intended.

Solution

1] We recommend that the OS user that is making bequeath connection belong to oinstall group. Or you can have the connection through listener.

2] If we do not care about the security of OS user not belonging to oinstall group, modifying the following directories, then the following workaround can be used

# chmod 1777 $GRID_HOME/auth/css/
# chmod 1777 $GRID_HOME/auth/css/
# chmod 1777 $GRID_HOME/auth/
# chmod 0755 $GRID_HOME

References
BUG:12536779 - ORA-29701 OCCURRED DUE TO PERMISSION DENIED
BUG:12599647 - ORA-29701 RAISED IN ASM I/O PATH; TERMINATING PROCESS 1192124

Learn Rank(), Dense_rank() and Row_Number() functions by example

use testdb;
go
create table #test (dim char(1), v int);
insert into #test values('a',1);
insert into #test values('a',2);
insert into #test values('a',3);
insert into #test values('a',4);
insert into #test values('b',1);
insert into #test values('b',2);
insert into #test values('c',1);
go
select dim, v, ROW_NUMBER() over (order by v) as 'RowNumber'
from #test;

/*
dim  v           RowNumber
---- ----------- --------------------
a    1           1
b    1           2
c    1           3
b    2           4
a    2           5
a    3           6
a    4           7

(7 row(s) affected)
*/

select dim,v,ROW_NUMBER() over (partition by dim order by v desc) as 'RowNumber'
from #test;

/*
dim  v           RowNumber
---- ----------- --------------------
a    4           1
a    3           2
a    2           3
a    1           4
b    2           1
b    1           2
c    1           1

(7 row(s) affected)
*/

select dim,v,RANK() over (order by v) 'Rank'
from #test;

/*
dim  v           Rank
---- ----------- --------------------
a    1           1
b    1           1
c    1           1
b    2           4
a    2           4
a    3           6
a    4           7

(7 row(s) affected)
*/


select dim,v,RANK() over (partition by dim order by v) 'Rank'
from #test;

/*
dim  v           Rank
---- ----------- --------------------
a    1           1
a    2           2
a    3           3
a    4           4
b    1           1
b    2           2
c    1           1

(7 row(s) affected)

*/

select dim, v, DENSE_RANK() over (order by v) 'DenseRank'
from #test;

/*
dim  v           DenseRank
---- ----------- --------------------
a    1           1
b    1           1
c    1           1
b    2           2
a    2           2
a    3           3
a    4           4

(7 row(s) affected)
*/

select dim,v,NTILE(4) over (order by v) as 'QuarterGroup'
from #test;
/*
dim  v           QuarterGroup
---- ----------- --------------------
a    1           1
b    1           1
c    1           2
b    2           2
a    2           3
a    3           3
a    4           4

(7 row(s) affected)
*/

Examples on how to use "dbms_obfuscation_toolkit" package

drop table tbl_test purge;

create table tbl_test (
  source_passwd varchar2(64), 
  encrypted_string varchar2(2000),
  encrypted_raw raw(2000),
  decrypted_string1 varchar2(64), 
  decrypted_string2 varchar2(64))
/



insert into tbl_test (source_passwd) values('abc');
insert into tbl_test (source_passwd) values('this is long password');
insert into tbl_test (source_passwd) values('Something_Special*');
commit;

-- below program will encrypt the password and store them into both varchar2 and raw columns.
-- the decryption will decrypt twice, one using varchar2, one using raw column

set serveroutput on
execute dbms_output.enable(10000);
declare
  v_encrypted_raw               RAW(2048);
  v_encrypted_string            VARCHAR2(2048);
  v_decrypted_raw               RAW(2048);
  v_decrypted_string1            VARCHAR2(2048);
  v_decrypted_string2            VARCHAR2(2048);
begin
  -- encryption loop
  for c1 in (select * from tbl_test)
  loop
    dbms_output.put_line(c1.source_passwd);
    dbms_obfuscation_toolkit.DESEncrypt(input => UTL_RAW.CAST_TO_RAW(rpad(c1.source_passwd,64,' ')),
                   key =>  UTL_RAW.CAST_TO_RAW('abcdefgh'), 
                   encrypted_data => v_encrypted_raw);
    v_encrypted_string := UTL_RAW.CAST_TO_VARCHAR2(v_encrypted_raw);
    update tbl_test 
    set encrypted_string=v_encrypted_string,
        encrypted_raw=v_encrypted_raw
    where source_passwd = c1.source_passwd;
  end loop;
  commit;
  -- decrytion loop - using string column
  for c1 in (select * from tbl_test)
  loop
    dbms_output.put_line(c1.source_passwd);
    dbms_obfuscation_toolkit.DESDecrypt(input => UTL_RAW.CAST_TO_RAW(c1.encrypted_string),
                   key =>  UTL_RAW.CAST_TO_RAW('abcdefgh'), 
                   decrypted_data => v_decrypted_raw);
    v_decrypted_string1 := trim(UTL_RAW.CAST_TO_VARCHAR2(v_decrypted_raw));
    update tbl_test 
    set decrypted_string1=v_decrypted_string1
    where source_passwd = c1.source_passwd;
  end loop;
  commit;
  
  -- decrytion loop - using raw column
  for c1 in (select * from tbl_test)
  loop
    dbms_output.put_line(c1.source_passwd);
    dbms_obfuscation_toolkit.DESDecrypt(input => c1.encrypted_raw,
                   key =>  UTL_RAW.CAST_TO_RAW('abcdefgh'), 
                   decrypted_data => v_decrypted_raw);
    v_decrypted_string2 := trim(UTL_RAW.CAST_TO_VARCHAR2(v_decrypted_raw));
    update tbl_test 
    set decrypted_string2=v_decrypted_string2
    where source_passwd = c1.source_passwd;
  end loop;
  commit;
end;
/

select * from tbl_test where source_passwd=decrypted_string1;
select * from tbl_test where source_passwd=decrypted_string2;

Create Table with datatype examples

use master;
go
drop database TestDb;
GO
CREATE DATABASE TestDB;
GO
Use TestDB;
GO
CREATE SCHEMA TEST;
GO
CREATE TABLE TEST.Customers (
  CustomerId  int identity(100,1) NOT NULL,
  Name    nvarchar(70),
  CreatedDateTime DateTime2,
  CreditLimit decimal(13,5));
GO

create type Test.NAME from nvarchar(70);
create type Test.CURRENCYVALUE from decimal(14,5);
GO

alter table Test.Customers alter column name Test.Name not null;
alter table Test.Customers alter column CreditLimit Test.CURRENCYVALUE NULL;
alter table Test.Customers alter column CreatedDateTime datetime2 NOT NULL;
GO

insert Test.Customers (Name, CreatedDateTime, CreditLimit)
select top (100000)
so1.name, SYSDATETIME(),case when ABS(so1.object_id) > 10000000 then null else ABS(so1.object_id) end
from sys.all_objects so1 cross join sys.all_objects as sol2;

exec sp_spaceused @objname = 'Test.Customers',@updateusage='true';
Go

alter table Test.Customers rebuild with (data_compression=none);
GO

alter table Test.Customers rebuild with (data_compression=row);
GO

alter table Test.Customers rebuild with (data_compression=page);
GO

Applying a troubleshooting Methodology

All credit goes to Microsoft, I did not write below myself. They are taken from Microsoft materials.

==============================================

• Investigate

  • Clearing define the issue as perceived by the user

  • What works? What doesn't work?

  • Did it ever work? When did it last work? What elese changed?

  • How would you know if it is resolved?

• Analyze

  • Brainstorm all potential causes

  • Which potential causes are likely? How could they be tested for and eliminated?

• Implement

  • Eliminate potential causes in descending order of likelihood

• Validate

  • Ensure that the issue really resolved

Key Points

You have seen in the discussion in the last topic that a key characteristic of good trouble-shooter is that they follow a clear methodology in a logical manner. There are manay different methodologies that are often applied to troubleshooting but the following list describes four phases that are common to most methodologies.

Investigation Phases

This is a critical phase. Too many people shortcut this step and start to jump directly in to finding solutions. Before you can solve any problems, you need to be very clear in your understanding of the problem that you are solving.

One very important convept in this phase is that the issue needs to be defined from the point of view of the user that is affected, not from an assumed perspective of an IT person. For example, there is no point telling a user that a system is working if the user cannot use it, for any reason, regardless of how your IT-based perspective might tell you that the system is working.

You need to understand what works and what doesn't work. A common mistake in this phase is to assume that when a user complains that something doesn'twork, that it did ever work. Make sure that there was a time when the issue didn't exist and find out when that was. Also find out abot anything, not matter how unrelated it might seem at this point, that has changed since that time.

Finally, you need to know how the user would decide that the issue is resolved. A common troubleshoting error is to find a problem, to assume that it is the cause of the issue, to resolve that problem, and to assume that the original issue is now resolved.

Analysis Phase

In the analysis phase, you need to determine all possible causes of the issue that you are trying to resolve. At this point, it is important to avoid excluding any potential causes, no matter how unlikely you consider them to be.

A brainstorming session with another person is often useful in this phase, particularly if that person is capable of constantly providing alternative viewpoints during discussions. (In many contries, theis person would be decribed as being good at playing the Devil's advocate). The analysis phase often benefits from two types of people, one of whom has excellent technical knowledge of the product, and another that constantly requires the first person to justfy their throughts and to think both logically and laterally.

Implementation Phase

In the implementation phase, you need to eliminate each potential cause. This process of elimination usually retuens the best results when the potential causes are eliminated in order from the most likely causes to the least likely cause.

The critial aspect of the implementation phase is to make sure that your reasons for eliminating portential causes are logically valid.

If you reach the end of your list of potential causes and have not yet found a solution to the issue, you need to return to the analysis phase and recheck your thinking. If you cannot find a problem in your analysis, you might even need to to back to recheck your initial assumaptions in the investigation phase.

Validation Phase

Too many people, particluarly these that are new to troubleshooting, assume that problems are resolved when they are not. Do not assu,me that because you have found the resolved a problem that it was the original problem that you have solved.

In the investigation phase, you should have determined how the user would devide if the issue is resolved. In the validation phase, you need to apply that test to see if the issue really resolved.

Capture slow query in MySQL database

Dynamic change the setting without restarting MySQL Server

C:\mysql-advanced-5.5.13-win32\bin>mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.13-enterprise-commercial-advanced-log MySQL Enterprise Serve
r - Advanced Edition (Commercial)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | OFF |
+----------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'slow_query_log_file';
+---------------------+----------------------------------------------------------+
| Variable_name | Value |
+---------------------+----------------------------------------------------------+
| slow_query_log_file | C:\mysql-advanced-5.5.13-win32\data\B5-Donghua1-slow.log |
+---------------------+----------------------------------------------------------+
1 row in set (0.01 sec)

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

mysql> set global long_query_time=3;
Query OK, 0 rows affected (0.00 sec)

mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.03 sec)

mysql> -- testing query
mysql> select sleep(10) from mysql.db limit 1;
+-----------+
| sleep(10) |
+-----------+
| 0 |
+-----------+
1 row in set (10.00 sec)



---------------------------------------------
Permanent settings in my.ini (my.cnf in Unix/Linux)


# The MySQL server
[mysqld]

slow-query-log = 1
slow_query_log_file = C:\mysql-advanced-5.5.13-win32\data\Donghua1-slow.log
long_query_time = 3