Friday, November 30, 2012

CHARACTER SET MIGRATION in ORACLE

ISSUE:

Database is using an invalid (non-UTF8) character set: (NLS_CHARACTERSET = WE8MSWIN1252) was given when tried to install space walk application.

CAUSE:
database was created with WE8MSWIN1252 because host os is windows. 

SOLUTION:
user csscan, csalter, datapump to migrate character set to AL32UTF8


STEPS:
1. csscan full=Y tochar=AL32UTF8
2. export user with application data need be converted. drop them from database
3. run csalter to change dictinary table character set
4. import back the dropped user.

POINTS:
it is good to user AL32UTF8 as character set at beginning. 
datapump and origical exp/imp tools can do character conversion on fly.

TABLE/VIEW:
nls_session_parameters
nls_instance_parameters
nls_database_parameters
v$nls_valid_values
v$nls_parameters

CHECK database character set:
select * from nls_database_parameters where parameter like '%CHAR%';

NLS paramters:

PARAMETER
------------------------------
NLS_LANGUAGE
NLS_TERRITORY
NLS_CURRENCY
NLS_ISO_CURRENCY
NLS_NUMERIC_CHARACTERS
NLS_CHARACTERSET
NLS_CALENDAR
NLS_DATE_FORMAT
NLS_DATE_LANGUAGE
NLS_SORT
NLS_TIME_FORMAT
NLS_TIMESTAMP_FORMAT
NLS_TIME_TZ_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
NLS_DUAL_CURRENCY
NLS_COMP
NLS_LENGTH_SEMANTICS
NLS_NCHAR_CONV_EXCP
NLS_NCHAR_CHARACTERSET
NLS_RDBMS_VERSION

reference:
http://www.morganslibrary.org/reference/character_sets.html
http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch12scanner.htm#g1035448
http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch11charsetmig.htm#g1011430
https://forums.oracle.com/forums/thread.jspa?threadID=990007
http://docs.oracle.com/cd/B28359_01/server.111/b28298/ch1overview.htm#i1005900
https://forums.oracle.com/forums/thread.jspa?messageID=3471873
https://forums.oracle.com/forums/thread.jspa?threadID=2206301




Compress tablespace , table partition and ORA-04031

ISSUE:
ORA-04031 appears in alert log and shared memory is used up. most of sql can not  be executed due to ORA-04031.

CAUSE:
1. we use basic compress technology on partition tables.
2. new column need be added to the partition tables with basic compression. so below need be done.
   alter table p1 move;
   alter table p1 add column c1;
   alter table p1 move compress;
3. there are 200000 subpartitions on p1 because of old data.

SYMPTOM:
ORA-0431 error.
PRTMV portion in shared pool is extremely high. in may case PRTMV used 18G of 20G shared pool

SOLUTION:
reduce subpartition to reasonable number.




Monday, November 26, 2012

DEFERRED_SEGMENT_CREATION and ORA-04031

11g new feature:

segment will be created when first insert statment is issued for table. if DEFERRED_SEGMENT_CREATION is true.

ISSUE:
When partition table is used, it might cause ORA-04031 error.  The PRTMV part of shared pool will take most of shared pool memory.

Solution:
set DEFERRED_SEGMENT_CREATION  to false.


reference:
11.2 Database New Feature Deferred Segment Creation [Video] [ID 887962.1]
http://www.dba-oracle.com/t_shared_pool_scripts.htm
http://blog.tanelpoder.com/2009/06/04/ora-04031-errors-and-monitoring-shared-pool-subpool-memory-utilization-with-sgastatxsql/


Wednesday, November 14, 2012

Compress in Oracle 11g


basic compression (compress)-- free
advanced compression (compres for all operatons |compress for oltp) -- need license

compress can be configured on tablespace , table, partition and subpartition level

SQL COMMAND:
create tablespace YT datafile  size 10m autoextend on maxsize 31G
extent management local uniform size 128k
segment space management auto
default compress
;

alter tablespace yt default compress;

create tablespace YT datafile  size 10m autoextend on maxsize 31G
extent management local uniform size 128k
segment space management auto
default compress for all operations
;

create tablespace YT datafile  size 10m autoextend on maxsize 31G
extent management local uniform size 128k
segment space management auto
default compress for oltp
;

alter tablespace yt default compress for all operations;
alter tablespace yt default compress for oltp;


alter tablespace yt default nocompress;



create table ttt (
...
)
compress|compress for all operations | compress for oltp
;

alter table ttt nocompress;
alter table ttt compress;
alter table ttt compress for all operations;
alter table ttt compress for OLTP;
alter table ttt move compress;
alter table ttt move nocompress;
alter table ttt move compress for all operations;

COMPRESSION CHECK:

select tablespace_name, compress_for from dba_tablespaces where tablespace_name='YT';
select table_name, owner, compression, compress_for from dba_tables where table_name='TTt' and owner='TEST';

reference:
http://oracle-online-help.blogspot.com/2006/11/oracle-table-compression.html
http://docs.oracle.com/cd/E11882_01/server.112/e25789/tablecls.htm#i31718
http://www.oracle-base.com/articles/11g/table-compression-enhancements-11gr1.php

Tuesday, November 13, 2012

Slow Drop Schema and Datapump Export Schema in Oracle 11g

ISSUE:
it is very slow to drop a schema or export  the schema in oracle 11g database.

CAUSE:
there are too many objects in the schema. In my case the schema has 1.4 million subpartitions.

SOLUTION:
to Drop the schema:  drop at table level or subpartion level first and then drop the schema.
to EXport the schema: reduce object number by combine partitions or change table definition. Otherwise metadata export will cost too much time to be completed.


reference:
http://www.oaktable.net/content/dropped-tables-hiding-extents-and-slow-dbafreespace-queries

Friday, November 9, 2012

Session Trace in ORACLE with Logon Trigger

ISSUE:
it is required to trace user session, which is initiated from application. it is hard to issue 'alter session set events ... " statement.

SOLUTION:
create a logon trigger to set up session trace.

e.x:


prompt 'this need be run as sys to create logon trigger to trace user session.'
accept UNAME prompt 'please input shcema name like UAT_TEST :'

CREATE OR REPLACE TRIGGER LOGON_TRACE
AFTER LOGON ON DATABASE
WHEN ( USER = upper('&UNAME') )
BEGIN
execute immediate 'alter session set events ''10046 trace name context forever, level 16''';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/


reference:
http://newappsdba.blogspot.com/2007/09/tip-tracing-session-via-login-trigger.html

Thursday, November 8, 2012

Remove SQL Query from SQLPLUS Spool file ORACLE

ISSUE:
SQL query was spooled out by sqlplus spool command when only results need be in spool file

SOLUTION:
Condition 1: spool command was executed mannually through interactive sqlplus prompt
run below set in sqlplus

SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET MARKUP HTML OFF
set trimspool on

Condition2: spool command was executed through shell script automatically.
start sqlplus with S (capital s) option. 
and include below set in the script.
set pagesize 0
set linesize 180
set feedback off
set trimspool on


Spool file should include only results. 

reference:
https://forums.oracle.com/forums/thread.jspa?messageID=3961450
http://www.the-playground.de/joomla/index.php?option=com_content&view=article&id=252:hide-sql-query-in-sqlplus-spool-file&catid=18:tipsntricks&Itemid=37


RECREATE CENTRAL INVENTORY HOME ORACLE

ISSUE:
Central inventory home might be missing when oracle software was cloned to new server.

CAUSE:
Default central inventory home location is not included in oracle software home.

SOLUTION:
recreate central inventory
for Linux platform:

As root
echo “inventory_loc=/etc/oraInventory” >> /etc/oraInst.loc
echo “inst_grp=dba” >> /etc/oraInst.loc
mkdir /etc/oraInventory
chown grid:dba /etc/oraInventory
chmod 770 /etc/oraInventory
As all other oracle software owners

cd $ORACLE_HOME/oui
echo “inventory_loc=/etc/oraInventory” >> oraInst.loc
echo “inst_grp=dba” >> oraInst.loc
$ORACLE_HOME/oui/runInstaller -silent  -attachHome –invPtrLoc ./oraInst.loc ORACLE_HOME=$ORACLE_HOME


reference:

Remove duplicate line in text file in UNIX

# save uniq lines to a new file
sort file | uniq > newfile
sort -u file > newfile

#find repeated lines
sort file | uniq -d

#find unique lines
sort file | uniq -c

#if you don't want to sort the output
awk '!x[$0]++' file

reference:
http://stackoverflow.com/questions/6447473/linux-command-or-script-counting-duplicated-lines-in-a-text-file
http://www.liamdelahunty.com/tips/linux_remove_duplicate_lines_with_uniq.php
http://www.cyberciti.biz/faq/unix-linux-shell-removing-duplicate-lines/
http://unstableme.blogspot.com/2008/03/remove-duplicates-without-sorting-file.html


Wednesday, November 7, 2012

PIPELINED FUNCTION in ORCLE

WHAT IS PIPELINED FUNCTION:
Pipelined function is a function, which can be treated as a table.

WHY DO WE NEED IT:
As I know:
1. escape stage table in ETL process. DO ETL manipulation in piplined function and results can be extract directly from the function since the function is the result table.
2. provide different data according to parameter. Sometime you want to check data distributed in different scheams or tables. You need modify your query with different schema name and talbe name and other things,   which requires user know table structures column names bla bal. You can use pipelined function with simple parameter to provide a easy interface to pull out data.

e.x.

CREATE OR REPLACE FUNCTION GET_ERRSUM(SCHEMA_NAME VARCHAR2)
RETURN TEST_ERRSUM_TAB PIPELINED AS
TYPE REF0 IS REF CURSOR;
CUR0 REF0;
OUT_REC TEST_ERRSUM := TEST_ERRSUM(NULL,NULL,0);
DBNAME VARCHAR2(40);
BEGIN
SELECT DBNAME INTO OLAP_DB
FROM TESTAPPLICATION
WHERE TESTIDENTIFIER=SCHEMA_NAME;
OPEN CUR0 FOR 'select eh.pd, eh.cn, count(*) errcount
FROM '||DBNAME||'.eventerrorhistory eh where eh.pd = (select max(pd) from '||DBNAME||'.eventerrorhistory)
group by eh.pd, eh.cn order by pd desc, eh.cn desc';
LOOP
FETCH CUR0 INTO OUT_REC.pd, OUT_REC.cn, OUT_REC.errcount;
EXIT WHEN CUR0%NOTFOUND;
PIPE ROW(OUT_REC);
END LOOP;
CLOSE CUR0;
RETURN ;
END GET_ERRSUM;
/


reference:
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:19481671347143
http://www.akadia.com/services/ora_pipe_functions.html


Thursday, November 1, 2012

check SCREEN buffer in PUTTY terminal of linux

ISSUE:
scrollback on putty terminal of linux didn't display the output of screen command.

CAUSE:
the buffer from screen command is not synced with putty buffer.

SOLUTION:
type C-a (ALT-a ) [ to go to copy mode. Then move your cursor to scroll back to screen output buffer.
type escape to get out of copy mode.

DEFINE BUFFER SIZE:
screen -h 5000

reference:
http://www.samsarin.com/blog/2007/03/11/gnu-screen-working-with-the-scrollback-buffer/

RESET SEQUENCE in ORACLE

alter sequence test increment by 100;
select test.nextval from dual;

alter sequence test increment by -100;
select test.nextval from dual;

Through resetting increment number, sequence can be reset to what number you want for next value.


reference:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1119633817597
http://docs.oracle.com/cd/E14072_01/server.112/e10592/statements_2012.htm