Your DBA's Blog - DBAGlobe

Thursday, February 20, 2014

Using SERVERERROR Trigger to troubleshooting application error like ORA-00001

oracle@solaris:~$ sqlplus /

SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 20 22:57:52 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set serveroutput on
SQL> set echo on
SQL> @trigger.sql

SQL> CREATE TABLE servererror_log (
  2      error_datetime  TIMESTAMP,
  3      error_user      VARCHAR2(30),
  4      db_name         VARCHAR2(9),
  5          l_server_error  number,
  6      error_stack     VARCHAR2(2000),
  7      captured_sql    VARCHAR2(1000))
  8  /

Table created.

SQL>
SQL> CREATE OR REPLACE TRIGGER log_server_errors
  2  AFTER SERVERERROR
  3  ON DATABASE
  4  DECLARE
  5  sql_text ora_name_list_t;
  6  stmt clob;
  7  n number;
  8  l_server_error number;
  9  BEGIN
 10    n := ora_sql_txt(sql_text);
 11    if n > 1000 then n:= 1000; end if ;
 12    FOR i IN 1..n LOOP
 13      stmt := stmt || sql_text(i);
 14    END LOOP;
 15
 16    l_server_error := server_error(1);
 17
 18    INSERT INTO servererror_log
 19    (error_datetime, error_user, db_name,l_server_error,
 20     error_stack, captured_sql)
 21    VALUES
 22    (systimestamp, sys.login_user, sys.database_name,l_server_error,
 23    dbms_utility.format_error_stack, stmt);
 24    commit;
 25  END log_server_errors;
 26  /

Trigger created.

SQL>
SQL> drop table t1 purge;

Table dropped.

SQL> create table t1 (id number primary key);

Table created.

SQL> insert into t1 values (1);

1 row created.

SQL> insert into t1 values (1);
insert into t1 values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$ORACLE.SYS_C0011730) violated


SQL> commit;

Commit complete.

SQL> alter session set nls_date_format='YYYY-MON-DD HH24:MI:SS';

Session altered.

SQL> select * from servererror_log;

ERROR_DATETIME
---------------------------------------------------------------------------
ERROR_USER                     DB_NAME   L_SERVER_ERROR
------------------------------ --------- --------------
ERROR_STACK
--------------------------------------------------------------------------------
CAPTURED_SQL
--------------------------------------------------------------------------------
20-FEB-14 10.58.14.724928 PM
OPS$ORACLE                     ORCL                   1
ORA-00001: unique constraint (OPS$ORACLE.SYS_C0011730) violated
insert into t1 values (1)

Wednesday, February 12, 2014

Oracle Database 12.1 Upgrade New Features and Changes

New DBUA Interface

DBUA is now divided into 2 panels (initially introduced with 11.2 OUI), the left panel lists all the steps executed by DBUA and the right panel executes and shows the progress of all operations listed in the left panel.

New Pre-Upgrade Information Tool

Oracle Database 12c introduces the preupgrd.sql Pre-Upgrade Information Tool which replaces the utlu121i.sql script and earlier versions
With the new Pre-Upgrade Information Tool, the default behavior of the prerequisite upgrade checks has been enhanced in the following ways:
- A log file, preupgrade.log, is created containing the output of the Pre-Upgrade Information Tool.
- The preupgrade_fixups.sql script is created
- The postupgrade_fixups.sql script is created
- These files are created under $ORACLE_BASE/cfgtoollogs/<SID>/preupgrade

Enhanced Upgrade Automation

New parallel upgrade script which is a default feature in Oracle Database
SERVER ( catalog , catproc ) component upgrade scripts get executed in parallel, for independent sub-components
Parallel upgrade is driven by a PERL script:
$ catctl.pl

Command Line Upgrade

In this release, the new Parallel Upgrade Utility, catctl.pl, provides parallel upgrade options that reduce downtime.
cd $ORACLE_HOME%rdbms\admin
$ORACLE_HOME\perl\bin\perl catctl.pl catupgrd.sql

Sunday, February 9, 2014

what kinds of statistics oracle optimizer relying on

Table statistics

Number of rows

Number of blocks

Average row length

Column statistics

Number of distinct values (NDV) in a column

Number of nulls in a column

Data distribution (histogram)

Extended statistics

Index statistics

Number of leaf blocks

Number of levels

Index clustering factor

System statistics

I/O performance and utilization

CPU performance and utilization

Sql server post for this week

(SFTW) SQL Server Links 07/02/14 http://www.johnsansom.com/sftw-sql-server-links-070214/

Saturday, February 8, 2014

Log mining SQL server Transaction Log

use AdventureWorks2012
go
dbcc sqlperf(logspace)
/*-----------------------------
Database Name    Log Size (MB)    Log Space Used (%)    Status
master    2.242188    26.02352    0
tempdb    0.7421875    59.01316    0
model    0.4921875    90.77381    0
msdb    0.7421875    49.21053    0
AdventureWorks2012    0.9921875    55.56102    0
ReportServer    1.054688    44.02778    0
ReportServerTempDB    1.054688    43.84259    0
---------------------------*/s
go
select * from sys.dm_db_log_space_usage
/*-----------------------------
database_id    total_log_size_in_bytes    used_log_space_in_bytes    used_log_space_in_percent    log_space_in_bytes_since_last_backup
5    1040384    578048    55.56102    101888
---------------------------*/
go

select * into dbo.emplyee_new from HumanResources.Employee

go
select o.name, o.type,o.create_date,o.modify_date,m.definition from sys.all_objects o inner join sys.all_sql_modules m
on o.object_id=m.object_id
where name like '%dblog%'
/*---------------------------
name    type    create_date    modify_date
fn_dblog    IF    2011-11-04 21:26:15.210    2011-11-04 21:27:08.297
fn_dblog_xtp    IF    2013-10-03 18:03:58.533    2013-10-03 18:03:58.567
fn_dump_dblog    IF    2011-11-04 21:26:16.583    2011-11-04 21:27:08.300
fn_dump_dblog_xtp    IF    2013-10-03 18:03:58.580    2013-10-03 18:03:58.597
---------------------------*/
GO

select [Current LSN], Context, [Operation],AllocUnitName from sys.fn_dblog(null,null)
-- where [Current LSN]> '0000002d:0000017b:0002'
go

select [Current LSN],[Begin Time],[Transaction Name] from sys.fn_dblog(null,null) where [Transaction Name] is not null
go

select convert(char(30),AllocUnitName) as AllocUnitName,
sum(convert(numeric, [Log Record Length])) as log_record_bytes,
sum(convert(numeric, [Log Reserve])) as log_reserve_bytes from sys.fn_dblog('45:379:1',null)
group by AllocUnitName order by log_reserve_bytes desc
/*---------------------------
AllocUnitName                  log_record_bytes                        log_reserve_bytes
------------------------------ --------------------------------------- ---------------------------------------
dbo.emplyee_new                75956                                   136732
NULL                           1360                                    36966
sys.sysobjvalues.clst          1540                                    297
sys.sysprufiles.clst           244                                     209
sys.sysiscols.clst             128                                     74
sys.sysiscols.nc1              116                                     74
sys.sysidxstats.nc             164                                     74
sys.sysidxstats.clst           196                                     74
Unknown Alloc Unit             4216                                    0
sys.sysschobjs.clst            420                                     0
---------------------------*/
go

select * from fn_dblog_xtp(null,null)
go
select * from sys.database_files where type_desc='LOG'
GO

delete from dbo.emplyee_new where SickLeaveHours>20
go

BACKUP LOG [AdventureWorks2012] TO DISK = N'C:\SampleDB\AdventureWorks2012_log.bak'
WITH NOFORMAT, NOINIT,
NAME = N'AdventureWorks2012-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

select * from fn_dump_dblog(null,null,'DISK',1,'C:\SampleDB\AdventureWorks2012_log.ldf',
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL)

select * from fn_dump_dblog(null,null,'DISK',1,'C:\SampleDB\AdventureWorks2012_log.bak',
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL)
go

select * from fn_dump_dblog_xtp(null,null,'DISK',1,'C:\SampleDB\AdventureWorks2012_log.bak',
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL)
go

How to clean SQL Buffer and Plan Cache

checkpoint DBCC FreeProcCache DBCC FreeSystemCache DBCC FlushProcInDB() DBCC DropCleanBuffers

Thursday, February 6, 2014

T-SQL Index Internal & Statistics


use AdventureWorks2012   
GO

select object_id   
,name     
,index_id    
,type    
,type_desc    
,is_unique    
,data_space_id    
,ignore_dup_key    
,is_primary_key    
,is_unique_constraint    
,fill_factor    
,is_padded    
,is_disabled    
,is_hypothetical    
,allow_row_locks     
,allow_page_locks     
,has_filter     
,filter_definition    
from sys.indexes where object_id=object_id('HumanResources.Employee')    
GO

select object_name=object_name(i.object_id),    
    index_name=id.name,    
    index_type=id.type_desc,    
    id.index_id,    
    column_name=c.name,    
    c.column_id     
from sys.index_columns i join sys.columns c    
  on i.object_id=c.object_id    
  and i.column_id=c.column_id    
  join sys.indexes id    
  on i.object_id=id.object_id    
  and i.index_id=id.index_id    
    where i.object_id=object_id('HumanResources.Employee')    
    order by index_id,column_id    
GO

select database_id   
,object_id   
,index_id    
,partition_number     
,index_type_desc       
,alloc_unit_type_desc         
,index_depth     
,index_level     
,avg_fragmentation_in_percent     
,fragment_count       
,avg_fragment_size_in_pages     
,page_count           
,avg_page_space_used_in_percent     
,record_count         
,ghost_record_count   
,version_ghost_record_count     
,min_record_size_in_bytes     
,max_record_size_in_bytes     
,avg_record_size_in_bytes     
,forwarded_record_count     
,compressed_page_count    
from sys.dm_db_index_physical_stats(db_id(),object_id('HumanResources.Employee'),1,DEFAULT,'DETAILED' )    
GO

select database_id   
--,object_id    
--,index_id    
--,partition_id    
--,rowset_id    
--,allocation_unit_id    
--,allocation_unit_type    
,allocation_unit_type_desc    
--,data_clone_id    
--,clone_state    
--,clone_state_desc    
,extent_file_id    
,extent_page_id    
,allocated_page_iam_file_id    
,allocated_page_iam_page_id    
,allocated_page_file_id    
,allocated_page_page_id    
,is_allocated    
,is_iam_page    
,is_mixed_page_allocation    
,page_free_space_percent    
,page_type    
,page_type_desc    
,page_level    
,next_page_file_id    
,next_page_page_id    
--,previous_page_file_id    
--,previous_page_page_id    
--,is_page_compressed    
--,has_ghost_records     
from sys.dm_db_database_page_allocations(db_id(),object_id('HumanResources.Employee'),1,DEFAULT,'DETAILED' )    
GO


DBCC IND('AdventureWorks2012','HumanResources.Employee',1)    
GO

DBCC TRACEON(3604,-1)   
GO    
DBCC PAGE(AdventureWorks2012,1,1052,3) with tableresults

sp_helptext fn_PhysLocCracker   
GO

select [NationalIDNumber], 'Location(File:Page:Slot)'=sys.fn_physLocFormatter(%%physloc%%),   
KeyHashValue=%%lockres%%    
from [AdventureWorks2012].[HumanResources].[Employee]    
GO

DBCC SHOW_STATISTICS('HumanResources.Employee','PK_Employee_BusinessEntityID')   
WITH STAT_HEADER, HISTOGRAM    
GO

SELECT OBJECT_NAME(s.object_id) AS object_name,   
    COL_NAME(sc.object_id, sc.column_id) AS column_name,    
    s.name AS statistics_name,    
    STATS_DATE=STATS_DATE(s.object_id,s.stats_id),    
    auto_created,    
    user_created    
FROM sys.stats AS s JOIN sys.stats_columns AS sc    
    ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id    
WHERE --s.name like '_WA%'    
sc.object_id=object_id('HumanResources.Employee')    
ORDER BY OBJECT_NAME(s.object_id),stats_date    
GO

UPDATE STATISTICS HumanResources.Employee   
-- WITH FULLSCAN    
WITH SAMPLE 50 PERCENT     
GO

Your DBA's Blog - DBAGlobe

Thursday, February 20, 2014

Using SERVERERROR Trigger to troubleshooting application error like ORA-00001

Wednesday, February 12, 2014

Oracle Database 12.1 Upgrade New Features and Changes

Sunday, February 9, 2014

what kinds of statistics oracle optimizer relying on

Sql server post for this week

Saturday, February 8, 2014

Log mining SQL server Transaction Log

How to clean SQL Buffer and Plan Cache

Thursday, February 6, 2014

T-SQL Index Internal & Statistics

Labels

Blog Archive

Your DBA's Blog - DBAGlobe

Thursday, February 20, 2014

Using SERVERERROR Trigger to troubleshooting application error like ORA-00001

Wednesday, February 12, 2014

Oracle Database 12.1 Upgrade New Features and Changes

Sunday, February 9, 2014

what kinds of statistics oracle optimizer relying on

Sql server post for this week

Saturday, February 8, 2014

Log mining SQL server Transaction Log

How to clean SQL Buffer and Plan Cache

Thursday, February 6, 2014

T-SQL Index Internal & Statistics

Labels

Blog Archive

Subscribe To