Thursday, December 20, 2012

run command as another user in linux using "sudo"

#this will run the command with another user's environment and account.
sudo -i -u username command

Reference
http://linux.die.net/man/8/sudo

compare strings in bash shell script

if [ $1 == $2 ] ; then
echo "Equal"
else
echo "not equal"
fi

if [ $1 != $2 ]; then
echo "not equal"
else
echo "equal"
fi


if [ $1 == "test" ] ; then
echo "Equal"
else
echo "not equal"
fi

reference:
http://www.tech-recipes.com/rx/209/bournebash-shell-scripts-string-comparison/

Thursday, December 13, 2012

ORA-14048: by Alter index unusable usable : IMPORT

PROBLEM:
when importing schema to database (version 11.2.0.3 linux 64bit) using data pump, Below errors appeared in log.

ORA-39083: Object type CONSTRAINT failed to create with error:
ORA-14048: a partition maintenance operation may not be combined with other operations
Failing sql is:
 ALTER INDEX "TEST"."SYS_C001906177"  UNUSABLE ENABLE

CAUSE:
This is unpublished bug 4919496.  When schema was exported, system generated index was put into unusable state. So export generate the "alter index .... unusable; enable" statement in dump file. this can be proved by generate SQLFILE using import tool. 

SOLUTION:
the error can be ignored during importing. To avoid this error, make system generated index usable when doing export.

reference:
Import Fails With Error ORA-14048 A Partition Maintenance Operation May Not Be Combined With Other Operations [ID 787407.1]




Tuesday, December 11, 2012

Password in Oracle 11G

ISSUE:

When we move schemas in one database or from it to another by datapump, we sometimes can not login to moved schemas with its password.

CAUSE:
Password has version (10G or 11G) in 11G database. Version usage is controlled by parameter.

HASH PROCESS:

a. in 10G
 passphrase + username are used to generate hash

b. in 11G
 passphrase + salt are userd to generate hash.

c. sec_case_sensitive_logon parameter is used to control password authentication method.
   sec_case_sensitive_logon -> TRUE   : 11G authentication
   sec_case_sensitive_logon -> FALSE  : 10G authentication

SYNOMINAL:

move schema TEST (11G) to TEST1 (10G) :  need reset pass
move schema TEST (11G) to TEST (10G) :  might need reset pass if 10G hash value is not correct
 move schema TEST (11G) to TEST1 (11G): no need to reset pass
move schema TEST (11G) to TEST (11G): no need to reset pass
move schema TEST (10G) to TEST (10G) : no need to reset pass
move schema TEST (10G) to TEST1 (10G) : need reset pass

move schema TEST (10G) to TEST (11G) : might need reset pass if 11G hash value is not null or correct
move schema TEST (10G) to TEST1 (11G) : need reset pass if 11G hash value is not correct

EXPLANATION:
Two hash values are stored in 11G database. One is for back-track to 10G pass authentication and the other one for new 11G authentication.

to check what hash was stored:
select username, password_versions from dba_users;
results can be:
10G
11G
10G 11G

to check the hash values
select name, password,spare4 from sys.user$;
password column is 10G hash value
spare4 column is 11G hash value(3-42) and its salt harsh value(43-63)


Reference:
http://www.notesbit.com/index.php/scripts-oracle/oracle-11g-new-password-algorithm-is-revealed-by-seclistsorg/
http://www.petefinnigan.com/sha1.sql
http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_1101.htm#sthref3208
http://askdba.org/weblog/2008/11/how-to-changerestore-user-password-in-11g/






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

Wednesday, October 24, 2012

configure new service on oracle database

ISSUE:
user like to use a specific tnsname entry to connect to new database

CAUSE:
It might require a lot effort or time to change tnsname configure on application side to connect. it is required to configure database server to accept request with old tnsname entry with hostname updated.

SOLUTION:
if the tnsname entry use SERVICE_NAME instead of SID_NAME, it can be resolved by configure a new service to match the one in tnsname entry.
if the tnsname entry use SID_NAME, rename your database is the only option as I know.

SOLUTION DETAILS OF CONFIGURE NEW SERVICE:
1. create new service
exec dbms_service.create_service('service_name','service_name');
2. start the service
exec dbms_service.start_service('service_name');
ATTN: manually do it -- alter system set service_names='service1, servcie2, service3'
4. dynamic register the service if local listerner parameter was configured
 alter system register
5. static register the service
add below inot sid_list

            (SID_DESC =
              (GLOBAL_DBNAME=service_name)
              (ORACLE_HOME = /u01/app/oracle/product/11.2.3.0/dbhome)
              (SID_NAME = sid)
              (SERVICE_NAME =srvice_name )
            )


reference:




Tuesday, October 23, 2012

recovery file dest in Oracle database

ISSUE:
Archiver might hang due to space usage in recovery area space.

CAUSE:
You can set up recovery area for recovery files such as archived log , control file to speed up database recovery.
two parameters to configure
db_recovery_file_dest -- where recovery area located e.g /u01/app/database/rflocation, +RFGRP
db_recovery-file_dest_size -- how many space can be used from the area

If size is too large, it will use too much space.
if size is too samll, it will hang your db if you save archvied log in the area.


CHECK:
v$recovery_file_dest
v$recovery_area_usage

reference:
http://www.pafumi.net/Flash_Recovery_Area.html

Monday, October 22, 2012

Encountered unrecognized patch ID: Z6MQ -- Oracle Weblogic Server Patch 10.3.5.0.5

ISSUE:
Message "Encountered unrecognized patch ID: Z6MQ" displayed when execute smartupdate (bsu) patch utility.

CAUSE: 
1. smartupdate (bsu) version is 3.2.1 or order
2. didn't put patch file into default direcotry

SOLUTION:
1. install smartupdate(bsu) version 3.3.0 - patch 12426828
2. unzip patch files to {MW_HOME}/utils/bsu/cache_dir
3. execute the patch command again.

command:
1. install
bsu.sh -install -patch_download_dir=/downlaod_dir -patchlist=Z6MQ -prod_dir={MW_HOME}/{WL_HOME}
2. view applied patch
bsu.sh -view -status=applied -prod_dir={MW_HOME}/{WL_HOME}
3. view downloaded patch
bsu.sh -view -status=downloaded -prod_dir={MW_HOME}/{WL_HOME} -patch_download_dir=/downlaod_dir
4. bsu version
bsu.sh -version
5. debug
bsu.sh -log=bsu.debug
6. report applied patch
bsu.sh -report

reference:

Tuesday, October 16, 2012

Run job in background and not terminated after logout in Linux

ISSUE:
We'd like to run a job in background and not terminated after logout after we already start the job or forget to use nohup or forget to include & at  the end.

SOLUTION:
we can use below command from shell prompt to fix the issue.
Cntl Z + bg
jobs
nohup
disown
disown -h   (will keep the process to current shell's process tree until you exit.)

e.g.
1. start a job and run foregroud
a. Crtl Z  -- stop the job to get back to shell prompt
b. jobs  -- list jobs with job_id
c. bg job_id -- start the job in backgroud
d. disown -h %job_id
2. start a job with nohup but forget to include & at the end
a. Crtl Z -- stop the job to get back to shell prompt
b. jobs -- list jobs with job_id
c. bg job_id --start the job in background
3. start a job without nohup in background
a. jobs -- list jobs with job_id
b. disown -h %job_id


reference:
http://unix.stackexchange.com/questions/3886/difference-between-nohup-disown-and
http://linux-quirks.blogspot.com/2011/04/nohup-or-disown.html
http://www.quantprinciple.com/invest/index.php/docs/tipsandtricks/unix/jobcontrol/
http://stackoverflow.com/questions/625409/how-do-i-put-an-already-running-process-under-nohup

Perl regular expression match

Simple Test Perl Script to test perl match

#!/usr/bin/perl

if ($ARGV[0] =~ m/.*ORA-0*(54|1142|1146|1234)(\D.*|$)/ ) {
print "match \n" ;
} else {
print "no match \n";
}

print "$ARGV[0] \n";

TEST:
[test@test shell]$ perl test.pl 'idfsdfsd ORA-1234'
match
idfsdfsd ORA-1234
[test@test shell]$ perl test.pl 'idfsdfsd ORA-123'
no match
idfsdfsd ORA-123
[test@test shell]$ perl test.pl 'idfsdfsd ORA-1234 ss'
match
idfsdfsd ORA-1234 ss
[test@test shell]$ perl test.pl 'idfsdfsd ORA-1234:'
match
idfsdfsd ORA-1234:
[test@test shell]$ perl test.pl 'idfsdfsd ORA-124:'
no match
idfsdfsd ORA-124:
[test@test shell]$ perl test.pl 'idfsdfsd OR-1234:'
no match
idfsdfsd OR-1234:
[test@test shell]$

Wild Character:
. - any character
.* - 0 or any repeated cahracter
a* - 0 or any repeated a
( a|b|c) - a or b or c
$ - end of sring
\D - none digit charactor
[0-9] -digit charactor
[^0-9] - none digit charactor

Reference:

Alertlog Error Filter in Oracle Cloud Control

ISSUE:
Some ORA- error in alert log file of oracle database instance is known and can be ignored for your system. You want to filter the error out so that no event will be generated when the error appears in the log

SOLUTION:
modify the filter expression
in all or any database instance target
Go to oracle database >monitoring> metric and collection setting>
Click edit for General Alter Log Error
change .*ORA-0*(54|1142|1146)\D.* to .*ORA-0*(54|1142|1146|???)(\D.*|$)

e. g.
to fileter ora-1234

new filter expression should be .*ORA-0*(54|1142|1146|1234)(\D.*|$)


reference:
http://docs.oracle.com/cd/E24628_01/em.121/e25160/osm_instance.htm
http://docs.oracle.com/cd/E24628_01/doc.121/e24473/toc.htm
http://docs.python.org/release/1.5.2p2/lib/re-syntax.html

Thursday, October 11, 2012

enable \n for echo linux command

echo -e " aaaa \n bb"
 aaaa
 bb

reference:
http://techteam.wordpress.com/2008/09/25/n-not-creating-new-line-in-bash-script/

Variable/Parameter Substitution in Shell Script

ISSUE:
You would like manipulate input argument in your shell script for many reasons.
for example
change input string to all upper case or lower case
create a log file according to prefix of script name

SOLUTION:
You can use various ways to do it (shell function, sed, awk).

PRACTICAL CASE:
1. convert string to lower case
${VARIABLE_NAME,,}

2. convert string to upper case
${VARIABLE_NAME^^}

3. get right of the .sh from script name
${0%.sh}

4.get string length
${#VARIABLE_NAME}

5. string position
${VARIABLE_NAME:postion} like ${1:0}

6. replacement
${VARIABLE_NAME/aa/bb/}

reference:
http://mintaka.sdsu.edu/GF/bibliog/latex/debian/bash.html
http://tldp.org/LDP/abs/html/parameter-substitution.html

Wednesday, October 10, 2012

STATISTICS ( STATS) collection job in oracle

10G:
job name - GATHER_STATS_JOB
 SELECT JOB_NAME, SCHEDULE_NAME, SCHEDULE_TYPE, ENABLED
FROM DBA_SCHEDULER_JOBS
WHERE PROGRAM_NAME = 'GATHER_STATS_PROG';


interface - scheduler



11G:
job is changed to Auto TASK  using DBMS_AUTO_TASK_ADMIN

dba_auto_task_client

select client_name, status from dba_autotask_client;
CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------------
auto optimizer stats collection                                  ENABLED
auto space advisor                                               ENABLED
sql tuning advisor                                               ENABLED



"auto optimizer stats collection" is the stats collection task.

 interface - dbms_auto_task_admin

DBMS_AUTO_TASK_ADMIN.DISABLE;
DBMS_AUTO_TASK_ADMIN.DISABLE (
   client_name       IN    VARCHAR2,
   operation         IN    VARCHAR2,
   window_name       IN    VARCHAR2);
DBMS_AUTO_TASK_ADMIN.ENABLE;
DBMS_AUTO_TASK_ADMIN.ENABLE (
   client_name       IN    VARCHAR2,
   operation         IN    VARCHAR2,
   window_name       IN    VARCHAR2); 


reference:  
http://askdba.org/weblog/2009/08/gather_stats_job-is-it-enabled/
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1133388300346992024
http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_autotaskadm.htm
http://psoug.org/reference/dbms_auto_task_admin.html

hostname of redhat linux in AMI

/etc/hosts:
this is the place linux system search for ip matching

/etc/sysconfig/network:
this is the file for hostname command

You can user hostname to reset hostname.

reference:
http://www.ducea.com/2006/08/07/how-to-change-the-hostname-of-a-linux-system/
http://www.howtogeek.com/wiki/Change_the_Hostname_on_a_Redhat_Linux_Machine



oracle s3 cloud module installation and check

DOWNLOAD LINK:
http://www.oracle.com/technetwork/products/secure-backup/secure-backup-s3-484709.html

INSTALLATION:
what you downloaded is the installer jar file
run below command as oracle user
/u01/app/oracle/database/product/11.2.0/jdk/bin/java  -jar osbws_install.jar -AWSID xxxxxxxxxxxxxxxxx  -AWSKey xxxxxxxxxxxxxxxxxxxxxxxxxxxxx  -otnUser test@yahoo.co.in  -otnPass test -walletDir /u01/app/oracle/database/product/11.2.0/dbs/osbws_wallet  -libDir /u01/app/oracle/database/product/11.2.0/lib

You must have s3 account to get AWSID and AWSKey
You must have oracle OTN account to do installation.
Java version need be 1.5 at the time of this post

JAVA VERION CHECK:
java -version

OSB LIB VERSION CHECK:
sbttest -libname /u01/app/oracle/database/product/11.2.0/lib/libosbws12.so
libosbws12.so  is the new version.
sbttest -libname /u01/app/oracle/database/product/11.2.0/lib/libosbws11.so
libosbws11.so is the old version.

E.X.
sbttest output -libname /u01/app/oracle/database/product/11.2.0/lib/libosbws12.so > output.log

CONFIGURATION FILE:
/u01/app/oracle/database/product/11.2.0/dbs/osbwsORCL.ora

RMAN COMMAND TO CALL OSB LIB:
1. restore control file
rman target /
    SET DBID=12345678
    STARTUP NOMOUNT;
    run {
    ALLOCATE CHANNEL CH1 DEVICE TYPE  SBT_TAPE parms='SBT_LIBRARY=libosbws12.so,SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/database/product/11.2.0/dbs/osbwsORCL.ora)'  FORMAT '%d_%I_%T_%U.backup';
    RESTORE until time "to_date('15-JUL-2012 14:00:00','DD-MON-YYYY HH24:MI:SS')" CONTROLFILE FROM AUTOBACKUP ;
    RELEASE CHANNEL CH1 ;
    }

run {
    ALLOCATE CHANNEL CH1 DEVICE TYPE  SBT_TAPE parms='SBT_LIBRARY=libosbws12.so,SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/database/product/11.2.0/dbs/osbwsORCL.ora)'  FORMAT '%d_%I_%T_%U.backup';
    RESTORE until time 'sysdate- 11' CONTROLFILE FROM AUTOBACKUP ;
    RELEASE CHANNEL CH1 ;
    }

run {
    ALLOCATE CHANNEL CH1 DEVICE TYPE  SBT_TAPE parms='SBT_LIBRARY=libosbws12.so,SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/database/product/11.2.0/dbs/osbwsORCL.ora)'  FORMAT '%d_%I_%T_%U.backup';
    RESTORE CONTROLFILE FROM AUTOBACKUP ;
    RELEASE CHANNEL CH1 ;
    }

2. restore database
rman target /
run {
set until time "to_date('15-JUL-2012 14:00:00','DD-MON-YYYY HH24:MI:SS')";
        ALLOCATE CHANNEL CH1 DEVICE TYPE  SBT_TAPE parms='SBT_LIBRARY=libosbws12.so,SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/database/product/11.2.0/dbs/osbwsORCL.ora)' FORMAT '%d_%I_%T_%U.backup' ;
        ALLOCATE CHANNEL CH2 DEVICE TYPE  SBT_TAPE parms='SBT_LIBRARY=libosbws12.so,SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/database/product/11.2.0/dbs/osbwsORCL.ora)' FORMAT '%d_%I_%T_%U.backup' ;
        ALLOCATE CHANNEL CH3 DEVICE TYPE  SBT_TAPE parms='SBT_LIBRARY=libosbws12.so,SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/database/product/11.2.0/dbs/osbwsORCL.ora)' FORMAT '%d_%I_%T_%U.backup' ;
        ALLOCATE CHANNEL CH4 DEVICE TYPE  SBT_TAPE parms='SBT_LIBRARY=libosbws12.so,SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/database/product/11.2.0/dbs/osbwsORCL.ora)' FORMAT '%d_%I_%T_%U.backup' ;
        RESTORE DATABASE  ;
        RECOVER DATABASE ;
        RELEASE CHANNEL CH1;
        RELEASE CHANNEL CH2;
        RELEASE CHANNEL CH3;
        RELEASE CHANNEL CH4;
        }

3. simple shell script to run rman
after database is mounted.

#!/bin/sh

source /home/oracle/.bash_profile

rman target / << EOF
run {
set until time "to_date('15-JUL-2012 14:00:00','DD-MON-YYYY HH24:MI:SS')";
        ALLOCATE CHANNEL CH1 DEVICE TYPE  SBT_TAPE parms='SBT_LIBRARY=libosbws12.so,SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/database/product/11.2.0/dbs/osbwsORCL.ora)' FORMAT '%d_%I_%T_%U.backup' ;
        ALLOCATE CHANNEL CH2 DEVICE TYPE  SBT_TAPE parms='SBT_LIBRARY=libosbws12.so,SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/database/product/11.2.0/dbs/osbwsORCL.ora)' FORMAT '%d_%I_%T_%U.backup' ;
        ALLOCATE CHANNEL CH3 DEVICE TYPE  SBT_TAPE parms='SBT_LIBRARY=libosbws12.so,SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/database/product/11.2.0/dbs/osbwsORCL.ora)' FORMAT '%d_%I_%T_%U.backup' ;
        ALLOCATE CHANNEL CH4 DEVICE TYPE  SBT_TAPE parms='SBT_LIBRARY=libosbws12.so,SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/database/product/11.2.0/dbs/osbwsORCL.ora)' FORMAT '%d_%I_%T_%U.backup' ;
        RESTORE DATABASE  ;
        RECOVER DATABASE ;
        RELEASE CHANNEL CH1;
        RELEASE CHANNEL CH2;
        RELEASE CHANNEL CH3;
        RELEASE CHANNEL CH4;
        }
exit
EOF


reference:
http://www.oracle.com/technetwork/products/secure-backup/secure-backup-s3-484709.html
http://www.oracle.com/technetwork/topics/cloud/osbws-readme-083624.html
https://jira.primaticsfinancial.com/jira/login.jsp?permissionViolation=true&os_destination=%2Fbrowse%2FEMSS-5817

Monday, October 8, 2012

ORA-01797: this operator must be followed by ANY or ALL

ISSUE:
sql statment didn't run with error ORA-01797

CAUSE:
equal operator = followed by multiple value like (1,2)

SOLUTION:
 a. change = to in
b. add any before multiple value like = any (1, 2)

reference:
 http://oraclequirks.blogspot.com/2008/06/ora-01797-this-operator-must-be.html

rman catalog view

RC_RMAN_BACKUP_JOB_DETAILS:

select  start_time, elapsed_seconds/3600 Hour, input_bytes,output_bytes, input_type, status
from rc_rman_backup_job_details


reference:
http://docs.oracle.com/cd/E11882_01/backup.112/e10643/rcviews001.htm

Friday, October 5, 2012

Useful linux command

system-config-network #network configuration graphic tool
strace # system call trace


shutdown linux command

shutdown -h now # turn off server now
halt   # turn off server now
reboot # reboot server
shutdown -r #reboot server

reference:
http://www.linuxforums.org/forum/slackware-linux/63551-halt-vs-shutdown.html

Wednesday, September 26, 2012

move putty sessions between computers.

ISSUE:
when a new compute was prepared, you want old putty session info loaded.

SOLUTION:
a. create a putty registry file from old computer
regedit /e putty.reg “HKEY_CURRENT_USER\Software\SimonTatham\PuTTY\Sessions”

b. copy putty.reg to new computer

c. double click putty.reg to merge.

reference:
http://downloadsquad.switched.com/2007/02/01/howto-transfer-your-putty-settings-between-computers/

single value comparsion from three tables: Oracle SQL

ISSUE:
NEED alert for tablespace difference between 3 databases.

SOLUTION:

select a.tablespace_name,
NVL(prod.tablespace_name, '**MISSING**') DB1,
NVL(uat.tablespace_name,'**MISSING**') DB2,
NVL(upg.tablespace_name, '**MISSING**') DB3
from
(
(
select tablespace_name from dba_tablespaces@DB1
union
select tablespace_name from dba_tablespaces@DB2
union
select tablespace_name from dba_tablespaces@DB3
)
minus
(
 select tablespace_name from dba_tablespaces@DB1
 where tablespace_name in  (select tablespace_name from dba_tablespaces@DB2)
 and tablespace_name in (select tablespace_name from dba_tablespaces@DB3)
 )
) a ,
dba_tablespaces@DB1 db1,
dba_tablespaces@DB2 db2,
dba_tablespaces@DB3 db3
where a.tablespace_name=db1.tablespace_name(+)
and a.tablespace_name=db2.tablespace_name(+)
and  a.tablespace_name=db3.tablespace_name(+)
and a.tablespace_name not in ('EXAMPLE','UNDOTBS')
order by a.tablespace_name


OUTPUT:

TABLESPACE_NAME |DB1            |DB2          |DB3                                                                                     
----------------|---------------|-------------|---------------                                                                         
AAAAAA          |**MISSING**    |**MISSING**  |AAAAAA                                                                                  
AAAAAAAAAB      |AAAAAAAAAB     |**MISSING**  |AAAAAAAAAB                                                                              
AAAAAAAAATBS    |AAAAAAAAATBS   |**MISSING**  |AAAAAAAAATBS                                                                            
AAAAAAAAAAATBS  |**MISSING**    |**MISSING**  |AAAAAAAAAAATBS                                                                          
AAAAAAAABTBS    |**MISSING**    |AAAAAAAABTBS |AAAAAAAABTBS                                                                            
AAAAAAAABTB     |AAAAAAAABTB    |AAAAAAAABTB  |**MISSING**                                                                             
AAAAAAAABTC     |**MISSING**    |AAAAAAAABTC  |**MISSING**                                                                             
AAAAAAAACTC     |**MISSING**    |AAAAAAAACTC  |**MISSING**                                                                             
AAAAAAACCTC     |**MISSING**    |AAAAAAACCTC  |**MISSING**                                                                             
AAADAAACCTC     |AAADAAACCTC    |**MISSING**  |AAADAAACCTC                                                                             
AAAAACCTC       |AAAAACCTC      |**MISSING**  |AAAAACCTC                                                                               
AAAACCTC        |**MISSING**    |AAAACCTC     |**MISSING**                                                                             
AABBCCTC        |AABBCCTC       |**MISSING**  |**MISSING**                                                                             
AADDCCTC        |**MISSING**    |AADDCCTC     |**MISSING**                                                                             
AADDEETC        |**MISSING**    |AADDEETC     |**MISSING**                  


Comment:
this sql can be expended to compare multiple tables.

Reference:

http://www.techonthenet.com/sql/union.php

Remove lines in one file from another file: UNIX

ISSUE:
some config file need be updated to remove no-need lines. the no-need lines were provided without any sort.

SOLUTION:
a. put the no-need lines to a file - noneedfile
b. make a backup file of original one-  originfile.bkp
c. run
grep -v -x -f noneedfile originfile.bkp > originfile

CHECK:
cat noneedfile | wc -l
+
cat originfile | wc -l
=
cat originalfile.bkp | wc -l

Reference:

http://stackoverflow.com/questions/4780203/deleting-lines-from-one-file-which-are-in-another-file
http://www.unix.com/shell-programming-scripting/89798-compare-two-files-remove-all-contents-one-file-another.html


Friday, September 21, 2012

oracle clusterware command

as grid infrastructure user

#start high availability infrastructure
crsctl start has 

#stop high avialability infrastructure
crsctl stop has

#check crs status
crs_stat -t

#check crs status
crsctl status resource

as  grid infrastructure user or database owner
#make a one node database start and stop automatic
# -a is required if you use ASM otherwise db startup will failed
srvctl add database -d <database name> [-a "diskgroup,diskgroup"]

#check database restart config
srvctl config database -d <database name>


reference:
http://docs.oracle.com/cd/E11882_01/server.112/e25494/restart005.htm

Wednesday, September 5, 2012

PL SQL: PLS-00382: expression is of wrong type

Cause:
 one of the cause is that implicit cursor is used as a variable

e.g:

-- wrong syntex
FOR x in ( select username from all_users)
LOOP
   dbms_output.put_line ( x);
END LOOP;

-- correct syntex
FOR x in ( select username from all_users)
LOOP
   dbms_output.put_line ( x.username);
END LOOP;

reference:
http://www.orafaq.com/forum/t/50755/2

PL SQL: read comma-separated input string

 select regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level) from dual
   connect by regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) is not null;


reference:
https://blogs.oracle.com/aramamoo/entry/how_to_split_comma_separated_string_and_pass_to_in_clause_of_select_statement

Tuesday, September 4, 2012

Monday, August 20, 2012

use sys as user to dump file in oracle database

impdp userid=\'/ as sysdba\' schemas=test dumpfile=test.dump logifle=test.log directory=data_pump_dir

in windows
expdp userid="'/ as sysdba'"  

reference:
http://mytechnicalthoughts.wordpress.com/2012/08/25/how-to-run-the-oracle-data-pump-command-as-sysdba/

Friday, August 10, 2012

Datapump exclude/include object types.

datapump object types can be found in following tables

databse_export_objects
schema_export_objects
table_export_objects.

schema_export_objects table content.
OBJECT_PATH COMMENTS NAMED
ALTER_FUNCTION Recompile functions Y
ALTER_PACKAGE_SPEC Recompile package specifications Y
ALTER_PROCEDURE Recompile procedures Y
ASSOCIATION Statistics type associations
AUDIT_OBJ Object audits on the selected tables
CLUSTER Clusters in the selected schemas and their indexes Y
COMMENT Table and column comments on the selected tables
COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC Recompile package specifications
CONSTRAINT Constraints (including referential constraints) Y
CONSTRAINT/REF_CONSTRAINT Referential constraints
DB_LINK Private database links in the selected schemas Y
DEFAULT_ROLE Default roles granted to users associated with the selected schemas
DIMENSION Dimensions in the selected schemas Y
FGA_POLICY Fine-grained auditing policies
FUNCTION Functions and their dependent grants and audits Y
FUNCTION/ALTER_FUNCTION Recompile functions
GRANT Object grants on the selected tables
GRANT/OWNER_GRANT/OBJECT_GRANT Object grants on the selected tables
GRANT/PROCDEPOBJ_GRANT Grants on instance procedural objects
GRANT/PROCOBJ_GRANT Schema procedural object grants in the selected schemas
INDEX Indexes Y
INDEX/STATISTICS Precomputed statistics
INDEXTYPE Indextypes and their dependent grants and audits Y
INSTANCE_CALLOUT Instance callouts
JAVA_CLASS Java classes and their dependent grants and audits Y
JAVA_RESOURCE Java resources and their dependent grants and audits Y
JAVA_SOURCE Java sources and their dependent grants and audits Y
JOB Jobs in the selected schemas
LIBRARY External procedure libraries in the selected schemas and their dependent grants and audits Y
MATERIALIZED_VIEW Materialized views Y
MATERIALIZED_VIEW_LOG Materialized view logs Y
OBJECT_GRANT Object grants on the selected tables
OPERATOR Operators and their dependent grants and audits Y
OWNER_GRANT/OBJECT_GRANT Object grants on the selected tables
PACKAGE Packages (both specification and body) and their dependent grants and audits Y
PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC Recompile package specifications
PACKAGE/PACKAGE_BODY Package bodies
PACKAGE/PACKAGE_SPEC Package specifications
PACKAGE_BODY Package bodies Y
PACKAGE_SPEC Package specifications Y
PACKAGE_SPEC/ALTER_PACKAGE_SPEC Recompile package specifications
PASSWORD_HISTORY The password history for users associated with the selected schemas
POST_INSTANCE/GRANT/PROCDEPOBJ_GRANT Grants on instance procedural objects
POST_INSTANCE/PROCDEPOBJ Instance procedural objects
POST_INSTANCE/PROCDEPOBJ_AUDIT Audits on instance procedural objects
POST_SCHEMA Post-schema procedural actions and objects and their dependent grants and audits
POST_TABLE_ACTION Post-table actions
PRE_SCHEMA Pre-schema procedural actions and objects in the selected schemas and their dependent grants and audits
PRE_SCHEMA/GRANT/PROCOBJ_GRANT Schema procedural object grants in the selected schemas
PRE_SCHEMA/PROCACT_SCHEMA Schema procedural actions in the selected schemas
PRE_SCHEMA/PROCOBJ Procedural objects in the selected schemas
PRE_SCHEMA/PROCOBJ_AUDIT Schema procedural object audits in the selected schemas
PRE_SCHEMA/SCHEMA_CALLOUT Schema callouts in the selected schemas
PRE_TABLE_ACTION Pre-table actions
PROCACT_INSTANCE Instance procedural actions
PROCACT_SCHEMA Schema procedural actions in the selected schemas
PROCDEPOBJ Instance procedural objects Y
PROCDEPOBJ_AUDIT Audits on instance procedural objects
PROCDEPOBJ_GRANT Grants on instance procedural objects
PROCEDURE Procedures and their dependent grants and audits Y
PROCEDURE/ALTER_PROCEDURE Recompile procedures
PROCOBJ Procedural objects in the selected schemas Y
PROCOBJ_AUDIT Schema procedural object audits in the selected schemas
PROCOBJ_GRANT Schema procedural object grants in the selected schemas
REFRESH_GROUP Refresh groups in the selected schemas Y
REF_CONSTRAINT Referential constraints Y
RLS_CONTEXT Fine-grained access control contexts
RLS_GROUP Fine-grained access control policy groups
RLS_POLICY Fine-grained access control policies
RLS_POLICY/RLS_POLICY Fine-grained access control policies
ROLE_GRANT Role grants to users associated with the selected schemas
SCHEMA_CALLOUT Schema callouts in the selected schemas
SCHEMA_EXPORT/ASSOCIATION Statistics type associations
SCHEMA_EXPORT/CLUSTER Clusters in the selected schemas and their indexes
SCHEMA_EXPORT/DB_LINK Private database links in the selected schemas
SCHEMA_EXPORT/DEFAULT_ROLE Default roles granted to users associated with the selected schemas
SCHEMA_EXPORT/DIMENSION Dimensions in the selected schemas
SCHEMA_EXPORT/FUNCTION Functions and their dependent grants and audits
SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION Recompile functions
SCHEMA_EXPORT/INDEXTYPE Indextypes and their dependent grants and audits
SCHEMA_EXPORT/JAVA_CLASS Java classes and their dependent grants and audits
SCHEMA_EXPORT/JAVA_RESOURCE Java resources and their dependent grants and audits
SCHEMA_EXPORT/JAVA_SOURCE Java sources and their dependent grants and audits
SCHEMA_EXPORT/JOB Jobs in the selected schemas
SCHEMA_EXPORT/LIBRARY External procedure libraries in the selected schemas and their dependent grants and audits
SCHEMA_EXPORT/MATERIALIZED_VIEW Materialized views
SCHEMA_EXPORT/OPERATOR Operators and their dependent grants and audits
SCHEMA_EXPORT/PACKAGE Packages (both specification and body) and their dependent grants and audits
SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC Recompile package specifications
SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY Package bodies
SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC Package specifications
SCHEMA_EXPORT/PASSWORD_HISTORY The password history for users associated with the selected schemas
SCHEMA_EXPORT/POST_SCHEMA Post-schema procedural actions and objects and their dependent grants and audits
SCHEMA_EXPORT/PRE_SCHEMA Pre-schema procedural actions and objects in the selected schemas and their dependent grants and audits
SCHEMA_EXPORT/PRE_SCHEMA/GRANT/PROCOBJ_GRANT Schema procedural object grants in the selected schemas
SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Schema procedural actions in the selected schemas
SCHEMA_EXPORT/PRE_SCHEMA/PROCOBJ Procedural objects in the selected schemas
SCHEMA_EXPORT/PRE_SCHEMA/PROCOBJ_AUDIT Schema procedural object audits in the selected schemas
SCHEMA_EXPORT/PRE_SCHEMA/SCHEMA_CALLOUT Schema callouts in the selected schemas
SCHEMA_EXPORT/PROCEDURE Procedures and their dependent grants and audits
SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Recompile procedures
SCHEMA_EXPORT/REFRESH_GROUP Refresh groups in the selected schemas
SCHEMA_EXPORT/ROLE_GRANT Role grants to users associated with the selected schemas
SCHEMA_EXPORT/SEQUENCE Sequences in the selected schemas and their dependent grants and audits
SCHEMA_EXPORT/SYNONYM Private synonyms in the selected schemas
SCHEMA_EXPORT/SYNONYM/FGA_POLICY Fine-grained auditing policies
SCHEMA_EXPORT/SYNONYM/RLS_CONTEXT Fine-grained access control contexts
SCHEMA_EXPORT/SYNONYM/RLS_GROUP Fine-grained access control policy groups
SCHEMA_EXPORT/SYNONYM/RLS_POLICY/RLS_POLICY Fine-grained access control policies
SCHEMA_EXPORT/SYSTEM_GRANT System privileges granted to users associated with the selected schemas
SCHEMA_EXPORT/TABLE Tables in the selected schemas and their dependent objects
SCHEMA_EXPORT/TABLE/AUDIT_OBJ Object audits on the selected tables
SCHEMA_EXPORT/TABLE/COMMENT Table and column comments on the selected tables
SCHEMA_EXPORT/TABLE/CONSTRAINT Constraints (including referential constraints)
SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Referential constraints
SCHEMA_EXPORT/TABLE/FGA_POLICY Fine-grained auditing policies
SCHEMA_EXPORT/TABLE/GRANT Object grants on the selected tables
SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Object grants on the selected tables
SCHEMA_EXPORT/TABLE/INDEX Indexes
SCHEMA_EXPORT/TABLE/INDEX/STATISTICS Precomputed statistics
SCHEMA_EXPORT/TABLE/INSTANCE_CALLOUT Instance callouts
SCHEMA_EXPORT/TABLE/MATERIALIZED_VIEW_LOG Materialized view logs
SCHEMA_EXPORT/TABLE/POST_INSTANCE/GRANT/PROCDEPOBJ_GRANT Grants on instance procedural objects
SCHEMA_EXPORT/TABLE/POST_INSTANCE/PROCDEPOBJ Instance procedural objects
SCHEMA_EXPORT/TABLE/POST_INSTANCE/PROCDEPOBJ_AUDIT Audits on instance procedural objects
SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION Post-table actions
SCHEMA_EXPORT/TABLE/PRE_TABLE_ACTION Pre-table actions
SCHEMA_EXPORT/TABLE/PROCACT_INSTANCE Instance procedural actions
SCHEMA_EXPORT/TABLE/RLS_CONTEXT Fine-grained access control contexts
SCHEMA_EXPORT/TABLE/RLS_GROUP Fine-grained access control policy groups
SCHEMA_EXPORT/TABLE/RLS_POLICY/RLS_POLICY Fine-grained access control policies
SCHEMA_EXPORT/TABLE/TRIGGER Triggers
SCHEMA_EXPORT/TABLESPACE_QUOTA Tablespace quotas granted to users associated with the selected schemas
SCHEMA_EXPORT/TYPE Types (both specification and body) and their dependent grants and audits
SCHEMA_EXPORT/TYPE/TYPE_BODY Type bodies
SCHEMA_EXPORT/TYPE/TYPE_SPEC Type specifications
SCHEMA_EXPORT/USER User definitions for users associated with the selected schemas
SCHEMA_EXPORT/VIEW Views and their dependent objects
SCHEMA_EXPORT/VIEW/RLS_POLICY/RLS_POLICY Fine-grained access control policies
SCHEMA_EXPORT/XMLSCHEMA XMLSCHEMAS
SEQUENCE Sequences in the selected schemas and their dependent grants and audits Y
STATISTICS Precomputed statistics
SYNONYM Private synonyms in the selected schemas Y
SYNONYM/FGA_POLICY Fine-grained auditing policies
SYNONYM/RLS_CONTEXT Fine-grained access control contexts
SYNONYM/RLS_GROUP Fine-grained access control policy groups
SYNONYM/RLS_POLICY/RLS_POLICY Fine-grained access control policies
SYSTEM_GRANT System privileges granted to users associated with the selected schemas
TABLE Tables in the selected schemas and their dependent objects Y
TABLE/AUDIT_OBJ Object audits on the selected tables
TABLE/COMMENT Table and column comments on the selected tables
TABLE/CONSTRAINT Constraints (including referential constraints)
TABLE/CONSTRAINT/REF_CONSTRAINT Referential constraints
TABLE/FGA_POLICY Fine-grained auditing policies
TABLE/GRANT Object grants on the selected tables
TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Object grants on the selected tables
TABLE/INDEX Indexes
TABLE/INDEX/STATISTICS Precomputed statistics
TABLE/INSTANCE_CALLOUT Instance callouts
TABLE/MATERIALIZED_VIEW_LOG Materialized view logs
TABLE/POST_INSTANCE/GRANT/PROCDEPOBJ_GRANT Grants on instance procedural objects
TABLE/POST_INSTANCE/PROCDEPOBJ Instance procedural objects
TABLE/POST_INSTANCE/PROCDEPOBJ_AUDIT Audits on instance procedural objects
TABLE/POST_TABLE_ACTION Post-table actions
TABLE/PRE_TABLE_ACTION Pre-table actions
TABLE/PROCACT_INSTANCE Instance procedural actions
TABLE/RLS_CONTEXT Fine-grained access control contexts
TABLE/RLS_GROUP Fine-grained access control policy groups
TABLE/RLS_POLICY/RLS_POLICY Fine-grained access control policies
TABLE/TRIGGER Triggers
TABLESPACE_QUOTA Tablespace quotas granted to users associated with the selected schemas
TRIGGER Triggers Y
TYPE Types (both specification and body) and their dependent grants and audits Y
TYPE/TYPE_BODY Type bodies
TYPE/TYPE_SPEC Type specifications
TYPE_BODY Type bodies Y
TYPE_SPEC Type specifications Y
USER User definitions for users associated with the selected schemas Y
VIEW Views and their dependent objects Y
VIEW/RLS_POLICY/RLS_POLICY Fine-grained access control policies
XMLSCHEMA XMLSCHEMAS Y

AWR Collection and Check.

AWR:
Automatic Workload Repository. it collect database statistics automatically ( every 1 hour as default).

CHECK AWR SETTING:
log in as sysdab:
select snap_interval, retention, topnsql from wrm$_wr_control;

CREATE SNAPSHOT MANUALLY:
exec dbms_workload_repository.create_snapshot();

TURN OFF SNAPHOT COLLECTION:
exec dbms_workload_repository.modify_snapshot_settings(interval => 0);

TURN ON SNAPHOT COLLECTION HOURLY:
exec dbms_workload_repository.modify_snapshot_settings(interval => 60);

SYNTEX:
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
   retention   IN  NUMBER    DEFAULT NULL,
   interval    IN  NUMBER    DEFAULT NULL,
   topnsql     IN  NUMBER    DEFAULT NULL,
   dbid        IN  NUMBER    DEFAULT NULL);

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
   retention   IN  NUMBER    DEFAULT NULL,
   interval    IN  NUMBER    DEFAULT NULL,
   topnsql     IN  VARCHAR2,
   dbid        IN  NUMBER    DEFAULT NULL);

CHECK LASTEST COLLECTION:
select systimestamp, most_recent_snap_time, snap_interval from wrm$_wr_control where dbid = (select dbid from v$database);

CHECK ERORR COLLECTION:
select * from wrm$_snap_error
 where dbid = (select dbid from v$database)
 order by snap_id;

select * from
(select snap_id,
instance_number,
 begin_interval_time,
 end_interval_time,
 flush_elapsed,
status,
 error_count,
 snap_flag
from wrm$_snapshot
 where dbid = (select dbid from v$database)
 order by snap_id desc)
 where rownum <= 10
 order by snap_id;

TRACE AUOT SNAPHOE COLLECTION:
snapshot was automatically collected by MMON background process
MMON action trace
alter session set "_swrf_test_action"=28;
snapshot flush trace
alter session set "_swrf_test_action"=10;

trun off trace
MMON action trace
alter session set "_swrf_test_action"=29;
snapshot flush trace
alter session set "_swrf_test_action"=11;

MORE TRACE INFO:
begin
dbms_monitor.serv_mod_act_trace_enable(service_name=>'SYS$BACKGROUND',
module_name=>'MMON_SLAVE',
action_name=>'Auto-Flush Slave Action');

dbms_monitor.serv_mod_act_trace_enable(service_name=>'SYS$BACKGROUND',
module_name=>'MMON_SLAVE',
action_name=>'Remote-Flush Slave Action');
end;

/
# trace AWR snapshots
alter session set events 'immediate trace name awr_test level 1';
# trace AWR purging
alter session set events 'immediate trace name awr_test level 2';
# trace AWR SQL
alter session set events 'immediate trace name awr_test level 3';
# turn off all of the above, if set
alter session set events 'immediate trace name awr_test level 4';







reference:
metalink document:




cloud control : page expired click ok turn off

problem:
You will get Page expired click ok message if you are not active for a while. it is annoying if you just need refresh a window.

solution:
turn expried page off

  1. Set the value for the OMS ORACLE_HOME environment variable and go to ‘OMS/bin’. For example:
    export ORACLE_HOME=/u01/app/oracle/product/Middleware/oms
    cd $ORACLE_HOME/bin
  2. increase timeout or disable this feature with ‘-1′ value:
  3. ./emctl set property -name oracle.sysman.eml.maxInactiveTime -value -1 -sysman_pwd sysman_password
  4. Restart OMS to reflect the new settings:
    ./emctl stop oms
    ./emctl start oms

referecne:
http://odenysenko.wordpress.com/2012/04/09/oem-12c-the-page-has-expired-click-ok-to-continue/

Thursday, August 9, 2012

oracle database login

what happened when logging into oracle datrabase ?:
  1. A new Oracle process is started (either by the listener or by local sqlplus if using the local BEQ connection)
  2. The new process attaches to SGA shared memory segments (so it could access all the needed SGA structures)
  3. The new process allocates process and session state objects and initializes new session structures in SGA
login as below if normal login stuck:

sqlplus -prelim "/ as sysdba"


 -prelim option stands for "preliminary connection;
it only do step 1 and 2 compared to normal login so it will not be stuck by oracle latch contention. However you can not query normal table and even v$ views but run oradebug command.

reference:

http://tech.e2sn.com/oracle/troubleshooting/hang/how-to-log-on-even-when-sysdba-can-t-do-so

oradebug / ora-00074 : oradebug dumpvar sga kcbatt_

issue: you got ora-00074 error when run oradebug in sqlplus

solution:
set up debug process

oradebug setmypid

example:

SQL> oradebug dumpvar sga kcbatt_
ORA-00074: no process has been specified
SQL> oradebug dumpvar sga kcbatt_
ORA-00074: no process has been specified
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dumpvar sga kcbatt_
ub4 kcbatt_ [06001BA38, 06001BA3C) = 00000003

reference:
http://tech.e2sn.com/oracle/troubleshooting/hang/how-to-log-on-even-when-sysdba-can-t-do-so
http://blog.tanelpoder.com/2009/08/27/latch-cache-buffers-chains-latch-contention-a-better-way-for-finding-the-hot-block/

Monday, August 6, 2012

UNDO tablespace Corruption

issue: database hang because unod tablespace is full. All sessions wait for undo segments.

Problem: rollbank segment was not released back because its status  become something other than online/offline.

Solution:
drop old undo tablespace and create new one.

1. find corrupted rollbank segment
  select segment_name from dba_rollback_segs where status not in ('ONLINE','OFFLINE');

2. startup database mount
alter system set undo_management='MANUAL' scope=spfile;
  alter system set _corrupted_rollback_segments='segment_name' scope=spfile;
alter system set _offline_rollback_segments='segment_name' scope=spfile;
alter system set undo_tablespace='newone';
startup;
drop undo tablespace oldone;

reference:
SR 3-6031218201

Thursday, August 2, 2012

find hidden paramter of oracle database

this should be run as sys

select a.ksppinm name,b.ksppstvl value,b.ksppstdf deflt,
decode(a.ksppity, 1,'boolean', 2,'string', 3,'number', 4,'file', a.ksppity) type,a.ksppdesc description
from sys.x$ksppi a, sys.x$ksppcv b
where a.indx = b.indx
and a.ksppinm like '_improved%'
order by name;

reference:
http://database.itags.org/oracle/122568/
https://forums.oracle.com/forums/thread.jspa?threadID=295697&messageID=3166221

tar command linux

1. create tar file
tar -cvf test.tar test/
tar -cvzf test.tar.gz test/

2. extract tar file
tar -xvf test.tar
tar -xvzf test.tar.gz

reference:
http://www.computerhope.com/unix/utar.htm

undo tablespace usage oracle 11g

1. UNDO tablespace is setup by below parameter
 show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS

the setting means database user auto unod management and retension is 900 seconds and default undo tablespace name UNDOTBS

2. active session's undo usage

SELECT  a.sid, a.username, b.used_urec, b.used_ublk
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr
ORDER BY b.used_ublk DESC

col sql_text format a40
set lines 130
select sq.sql_text sql_text, t.USED_UREC Records, t.USED_UBLK Blocks, (t.USED_UBLK*8192/1024) KBytes from v$transaction t,
v$session s,
v$sql sq
where t.addr = s.taddr
and s.sql_id = sq.sql_id

3. undo block usage

select count(*), status
 from dba_undo_extents
 group by status
 /

4. swith undo tablespace
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02;




reference:
http://oracledisect.blogspot.com/2008/05/who-is-using-your-undo-space.html
http://docs.oracle.com/cd/B28359_01/server.111/b28310/undo005.htm
http://oraclezone.wordpress.com/2007/12/08/how-much-undo-does-a-sql-use/

Friday, July 27, 2012

ORA-03113: end-of-file on communication channel

ERROR Message:
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 12556
Session ID: 96 Serial number: 12509

ERROR:
ORA-03114: not connected to ORACLE
Log file info:

Incident 757262 created, dump file: /u01/app/oracle/database/diag/rdbms/prod/ORCL/incident/incdir_757262/ORCL_ora_12556_i757262.trc
ORA-07445: exception encountered: core dump [evaopn3()+125] [SIGSEGV] [ADDR:0x4] [PC:0x8AA42D1] [Address not mapped to object] []

this is a bug. Manipulate the sql to remove order by or group by or chagne date format might be a work-around.



rman backup cloud moduel for s3 error

error message:
RMAN> BACKUP DEVICE TYPE SBT  AS COMPRESSED BACKUPSET CURRENT CONTROLFILE;
Starting backup at 27-JUL-12
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 07/27/2012 11:37:48
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27023: skgfqsbi: media manager protocol error
ORA-19511: Error received from media manager layer, error text:
   KBHS-00715: HTTP error occurred 'client-error'
KBHS-00703: unable to connect to HTTP server oracle-sbt-license.s3.amazonaws.com; received ORA-12535
LFI-00108: Open expect the file [ewallet][p12] to exist.

This error was cleared by itself after 3 hours.

I cannot find out the cause.

to find out cloud moduel parameter
strings $ORACLE_HOME/lib/libosbws12.so | grep '^OSB_' | sort
strings $ORACLE_HOME/lib/libosbws12.so | grep '^_OSB_' | sort


reference:
http://www.idevelopment.info/data/AWS/AWS_Tips/Oracle_on_AWS/ORAAWS_15.shtml

Wednesday, July 25, 2012

ORA-12514: TNS:listener does not currently know of service

issue:
database is tnspingable, but sqlplus will give below error when login:

ORA-12514: TNS:listener does not currently know of service


cause:
database service is not registered to the listener.

solution:
add service to listener and restart it.

listener entry:
in sid_list_listener:
add
    (SID_DESC =
     (GLOBAL_DBNAME=myfubardb)
      (ORACLE_HOME = /ora00/app/oracle/product/10.2.0/db_1)
      (SID_NAME = fubar)
    )
 
 
reference:

OALL8 is in an Inconsistent State

OALL8 is in an inconsistent state" is thrown when using the 10.2.0.3 JDBC thin driver to select non ascii characters from the database.

reference:
http://asanga-pradeep.blogspot.com/2008/06/oall8-is-in-inconsistent-state-with.html