opmanager startup issue

if you get X11 connnection refused issue when you try to nohup start opmanager, the cause might you set up X11 forwording in PUTY connection.

Unselect X11 forwording and start opmanager. It should work fine.

ORA-00119: invalid specification for system parameter LOCAL_LISTENER

What are the symptoms?

The local_listener parameter has been set, the listener is running, but when attempting to start the instance an ORA-00119 is reported:

SQL> startup
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LIST_ANDY'

Why is this happening?

Oracle only checks for listeners running on the default port (1521). It would have to spend all day trying every possible port number otherwise. You'll need to give it some help to find your listener.

How do I fix it?

Simply add an entry to the servers tnsnames.ora pointing at the listener. For example my listener 'LIST_ANDY' is running on port 3801, so I would add the following entry to my tnsnames:

LIST_ANDY = (ADDRESS = (PROTOCOL = TCP)(HOST = fermat)(PORT = 3801))

Test it with tnsping:

tnsping

Then try starting your instance again - it should work now.

reference:
http://www.shutdownabort.com/errors/ORA-00119.php

dbms_stats

Table statistics

Number of rows

Number of blocks

Average row length

Column statistics

Number of distinct values (NDV) in column

Number of nulls in column

Data distribution (histogram)

Extended statistics

Index statistics

Number of leaf blocks

Levels

Clustering factor

System statistics

I/O performance and utilization

CPU performance and utilization

13.6.1 Statistics on Tables, Indexes and Columns

Statistics on tables, indexes, and columns are stored in the data dictionary. To view statistics in the data dictionary, query the appropriate data dictionary view (USER, ALL, or DBA). These DBA_* views include the following:

DBA_TABLES

DBA_OBJECT_TABLES

DBA_TAB_STATISTICS

DBA_TAB_COL_STATISTICS

DBA_TAB_HISTOGRAMS

DBA_TAB_COLS

DBA_COL_GROUP_COLUMNS

DBA_INDEXES

DBA_IND_STATISTICS

DBA_CLUSTERS

DBA_TAB_PARTITIONS

DBA_TAB_SUBPARTITIONS

DBA_IND_PARTITIONS

DBA_IND_SUBPARTITIONS

DBA_PART_COL_STATISTICS

DBA_PART_HISTOGRAMS

DBA_SUBPART_COL_STATISTICS

DBA_SUBPART_HISTOGRAMS


Procedure Collects

GATHER_INDEX_STATS


Index statistics

GATHER_TABLE_STATS


Table, column, and index statistics

GATHER_SCHEMA_STATS


Statistics for all objects in a schema

GATHER_DICTIONARY_STATS


Statistics for all dictionary objects

GATHER_DATABASE_STATS


Statistics for all objects in a database

afterlogin trigger

CREATE OR REPLACE TRIGGER afterlogon
AFTER LOGON on DATABASE
declare
userprofile varchar2(30);
os_user varchar2(30);
host varchar2(30);
user varchar2(30);
ip_addr varchar2(30);
BEGIN
select profile into userprofile from dba_users where username = sys_context('userenv','session_user');
select sys_context('userenv','os_user') into os_user from dual;
select sys_context('userenv','host') into host from dual;
select sys_context('userenv','session_user') into user from dual;
select sys_context('userenv','ip_address') into ip_addr from dual;

IF userprofile = 'XXCUST'
and ip_addr like '192.168.%' THEN
--insert into sys.tetb3 values(userprofile,os_user,user,host,ip_addr,'R');
--commit;
RAISE_APPLICATION_ERROR (-20001, 'You are not allowed to log into service account from this machine');
END IF;
END;
/

dbms_scheduler

#create a simple job

# plsql version

BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'job1',
job_type => 'PLSQL_BLOCK',
job_action => 'INSERT INTO employees VALUES (7935, ''SALLY'',
''DOGAN'', ''sally.dogan@xyzcorp.com'', NULL, SYSDATE,''AD_PRES'', NULL,
NULL, NULL, NULL);',
start_date => SYSDATE,
repeat_interval => 'FREQ = DAILY; INTERVAL =',
enabled => true);
END;
/

BEGIN
DBMS_SCHEDULER.DROP_JOB('job1');
END;
/


# procedure version.
create or replace procedure job_1
as
begin
DELETE FROM SYS.AUD$
WHERE NTIMESTAMP# < TO_TIMESTAMP (sysdate - 10 ); commit; end del_aud_tab; / BEGIN DBMS_SCHEDULER.CREATE_JOB( job_name => 'job_1',
job_type => 'PLSQL_BLOCK',
job_action => 'begin del_aud_tab; end;',
start_date => SYSDATE,
repeat_interval => 'FREQ = DAILY; INTERVAL =1',
enabled => true);
END;
/

install flashplayer plugin to firefox on centos linux

1. download rpm from adobe
adobe-release-x86_64-1.0-1.noarch.rpm
2. install it use rpm
rpm -i adobe-release-x86_64-1.0-1.noarch.rpm
3. install flash-plugin use yum
yum install flash-plugin.x86_64

4. restart friefox

you should be good to have flash player plugin on firefox.

reference:
http://digitizor.com/2009/05/19/how-to-get-flash-plugin-to-work-on-firefox-3-for-linux/

no session created in vncviewer

This issue may be caused by X environment setup on server side

1. check log file:
cat /home/oracle/.vnc/westoracle:1.log

Found below error:

error opening security policy file /usr/lib64/xserver/SecurityPolicy
/home/oracle/.vnc/xstartup: line 9: xsetroot: command not found
/home/oracle/.vnc/xstartup: line 11: xterm: command not found
/home/oracle/.vnc/xstartup: line 12: twm: command not found
Could not init font path element unix/:7100, removing from list!


the problem is missing of xsetroot xterm twm command.

2. install the missing command:

yum install xorg-x11-server-utils-7.1-4.fc6.x86_64 for xsetroot
yum install xterm-215-8.el5_4.1.x86_64 for xterm
yum install xorg-x11-twm-1.0.1-3.1.x86_64 for twm

3. restart vncserver

you should have opened session in vncviewer


reference:
http://www.linuxquestions.org/questions/linux-newbie-8/starting-gnome-session-over-vnc-268828/
http://www.linuxquestions.org/questions/linux-newbie-8/centos-5-3-vnc-problems-802176/
https://forums.oracle.com/forums/thread.jspa?threadID=870033
http://www.linuxquestions.org/questions/fedora-35/could-not-init-font-path-element-unix-7100-removing-from-list-156348/

ldd

NAME
ldd - print shared library dependencies
ldd `which svn`

reference
http://old.nabble.com/svn-error,-cannot-find-libsvn_client-1.so.0-td23407998.html

svn

--subversion is a tool for version control

svn --version #check version
svn help # find help for command

svn checkout (co)
svn update (up)

change password with harsh value in oracle 11g

1. get ddl to recreate the user.
select dbms_metadata.get_ddl('USER','COSKAN') from dual;

Attn: set long 200 if you read in sqlplus

example of results:
DBMS_METADATA.GET_DDL('USER','COSKAN') ------------------------------------------------------------------------------------------------------------------------------------

CREATE USER "COSKAN" IDENTIFIED BY
VALUES 'S:1F0648E7E665F0A0EE44B1E9BD4B626A77CA25B376A49177F9E97DF98BFA;26EB15F771A78542'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"

2. check hash value from sys.user$
select password, spare4 from sys.user$ where name = 'COSKAN';

alter system set sec_case_sensitive_logon=true;

S:1F0648E7E665F0A0EE44B1E9BD4B626A77CA25B376A49177F9E97DF98BFA -- for sensitive
26EB15F771A78542 -- for non-sensitive

if set up only non-sensitive, sec_case_sensitive_logon=true will not work.

3. change password
alter user "COSKAN" IDENTIFIED BY
VALUES 'S:1F0648E7E665F0A0EE44B1E9BD4B626A77CA25B376A49177F9E97DF98BFA;26EB15F771A78542';

4. practical use
if you need extend an expired account, the only way I know is to change user to default profile and reset password with harsh value (if you don't know what it is)
then change back to previous profile.



reference:
http://riaschissl.blogspot.com/2010/07/oracle-unexpire-and-unlock-accounts.html
http://coskan.wordpress.com/2009/03/11/alter-user-identified-by-values-on-11g-without-using-sysuser/

ps

SYNOPSIS
ps [options]

DESCRIPTION
ps displays information about a selection of the active processes. If you want a repetitive update of the
selection and the displayed information, use top(1) instead.

This version of ps accepts several kinds of options:
1 UNIX options, which may be grouped and must be preceded by a dash.
2 BSD options, which may be grouped and must not be used with a dash.
3 GNU long options, which are preceded by two dashes.

Options of different types may be freely mixed, but conflicts can appear. There are some synonymous options, which
are functionally identical, due to the many standards and ps implementations that this ps is compatible with.

Note that "ps -aux" is distinct from "ps aux". The POSIX and UNIX standards require that "ps -aux" print all
processes owned by a user named "x", as well as printing all processes that would be selected by the -a option. If
the user named "x" does not exist, this ps may interpret the command as "ps aux" instead and print a warning. This
behavior is intended to aid in transitioning old scripts and habits. It is fragile, subject to change, and thus
should not be relied upon.

By default, ps selects all processes with the same effective user ID (euid=EUID) as the current user and
associated with the same terminal as the invoker. It displays the process ID (pid=PID), the terminal associated
with the process (tname=TTY), the cumulated CPU time in [dd-]hh:mm:ss format (time=TIME), and the executable name
(ucmd=CMD). Output is unsorted by default.

The use of BSD-style options will add process state (stat=STAT) to the default display and show the command args
(args=COMMAND) instead of the executable name. You can override this with the PS_FORMAT environment variable. The
use of BSD-style options will also change the process selection to include processes on other terminals (TTYs)
that are owned by you; alternately, this may be described as setting the selection to be the set of all processes
filtered to exclude processes owned by other users or not on a terminal. These effects are not considered when
options are described as being "identical" below, so -M will be considered identical to Z and so on.

Except as described below, process selection options are additive. The default selection is discarded, and then
the selected processes are added to the set of processes to be displayed. A process will thus be shown if it meets
any of the given selection criteria.

EXAMPLES
To see every process on the system using standard syntax:
ps -e
ps -ef
ps -eF
ps -ely

To see every process on the system using BSD syntax:
ps ax
ps axu

To print a process tree:
ps -ejH
ps axjf

To get info about threads:
ps -eLf
ps axms

To get security info:
ps -eo euser,ruser,suser,fuser,f,comm,label
ps axZ
ps -eM

To see every process running as root (real & effective ID) in user format:
ps -U root -u root u

To see every process with a user-defined format:
ps -eo pid,tid,class,rtprio,ni,pri,psr,pcpu,stat,wchan:14,comm
ps axo stat,euid,ruid,tty,tpgid,sess,pgrp,ppid,pid,pcpu,comm
ps -eopid,tt,user,fname,tmout,f,wchan

Print only the process IDs of syslogd:
ps -C syslogd -o pid=

Print only the name of PID 42:
ps -p 42 -o comm=

nslookup

nslookup 192.168.1.2
nslookup name.col.com


Non-interactive mode is used when the name or Internet address of the host to be looked up is given as the first
argument. The optional second argument specifies the host name or address of a name server.

refresh materialized view in oracle

1. procedure to execute
REFRESH Procedures
This procedure refreshes a list of materialized views.

Syntax

DBMS_MVIEW.REFRESH (
{ list IN VARCHAR2,
| tab IN DBMS_UTILITY.UNCL_ARRAY,}
method IN VARCHAR2 := NULL,
rollback_seg IN VARCHAR2 := NULL,
push_deferred_rpc IN BOOLEAN := true,
refresh_after_errors IN BOOLEAN := false,
purge_option IN BINARY_INTEGER := 1,
parallelism IN BINARY_INTEGER := 0,
heap_size IN BINARY_INTEGER := 0,
atomic_refresh IN BOOLEAN := true,
nested IN BOOLEAN := false);



exec dbms_mview.refresh('schema_owner.mview_name,schema_owner.mview_name1')
exec dbms_mview.refresh('schema_owner.mview_name', atomic_refresh=> fails)
2. refresh methods
a. fast refresh as f
b. complete refresh as c
c. default value can be force ( which try fast if possible otherwise us complete)
3. atomic_refresh parameter.
when you do complete refresh, oracle trancate table and insert /* append */ if atomic_refresh was set false). Oracle will delete and insert if atomic_refresh was set true (which is the default value).

for quick refreseh set atomic_refresh to false.
for data availability , user default value.


reference:
http://askdba.org/weblog/2009/07/mview-complete-refresh-and-atomic_refresh-parameter/
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::p11_question_id:15695764787749
http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_mview.htm#i997194

oracleasm

oracleasm configure --display current configuration
oracleasm configure -i --change current configuration

oracleasm createdisk diskname /dev/sdb1

oracleasm init --load asm lib driver
oracleasm status -- check asm lib driver status.

active & deactive network interface

ifconfig

ifup

ifdown

network file
/etc/sysconfig/network

network interface file
/etc/sysconfig/network-scripts/ifcfg-eth0


reference: http://www.yolinux.com/TUTORIALS/LinuxTutorialNetworking.html

xclock

If xclock is missing from you system, install package xorg-x11-apps

yum install xorg-x11-apps

sudo

vi /etc/sudoers
--add
oracle ALL=(ALL) ALL

--to check what sudo privileges current user can run
sudo -l

ssh x11 forward remote display

if you use ssh x11 forwording , you don't need export DISPLAY=remoteserver:0.0.

if you get below error:
Xlib: connection to "localhost:13.0" refused by server
Xlib: PuTTY X11 proxy: MIT-MAGIC-COOKIE-1 data did not match
the cause is the auth line in .Xauthority file is wrong or missing.

if you get below error:
PuTTY X11 proxy: wrong authentication protocol attemptedError: Can't open display: localhost:12.0
the Cause is that you sudo su to the current user.

1. xauth list -- find out want auth line in the file
local/unix:1 MIT-MAGIC-COOKIE-1 8afedf4b9d02e29fff549f534fb8e495
local/unix:13 MIT-MAGIC-COOKIE-1 29230aecd972e2f64dd0973be473f8c2
local/unix:11 MIT-MAGIC-COOKIE-1 c22a518b8ae65a8e2411dda2d561e9ec
local/unix:12 MIT-MAGIC-COOKIE-1 4a3e6c97e72a98a7d5b934127e7c0559
local/unix:10 MIT-MAGIC-COOKIE-1 26ea6ffc0f2704e6df399caff0fccb26

2. xauth remove local/unix:13

3. exit current su user

4. xauth list -- find out initial user login x11 auth line
local/unix:13 MIT-MAGIC-COOKIE-1 4075c2721e69295e62187bb9c8b04b6b

5. su back to user like oracle

6. xauth add local/unix:13 MIT-MAGIC-COOKIE-1 4075c2721e69295e62187bb9c8b04b6b

7. run xclock -- it should appear in your laptop or desktop.
ATTN: if not, check if your laptop or desktop has X-mananger like xing running


reference:
http://froebe.net/blog/2008/11/14/getting-xlib-putty-x11-proxy-wrong-authentication-protocol-attempted-i-have-the-answer/

TNS-12547:TNS:lost contact

this error may caused by permission on /dev/null

if you get "-bash: /dev/null: Permission denied" warning when you login.

the permission of /dev/null may be changed.

it should be set 666

temp tablespace and usage

V$SORT_SEGMENT

V$TEMP_CACHE_TRANSFER
V$TEMP_EXTENT_MAP
V$TEMP_EXTENT_POOL
V$TEMP_SPACE_HEADER
V$TEMPFILE
V$TEMPORARY_LOBS
V$TEMPSEG_USAGE -- V$sort_usage
V$TEMPSTAT

--create temp tablespace
CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' SIZE 500M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE 15G
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

-- change default temporary tablespace
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

-- add more tempfile
alter tablespace temp add tempfile '/u03/oradata/datafile/temp03.dbf' size 100m autoextend on maxsize 15G ;

to find what is going on in a database, check v$sort_usage(v$tempseg_usage) and v$sort_segment

-- list sessoin temporary tablespace usage.

SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;



-- simplized sessoin temporary tablespace usage.

SELECT S.sid || ',' || S.serial# sid_serial, S.username, T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace, T.sql_id
FROM v$sort_usage T, v$session S, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.tablespace = TBS.tablespace_name
ORDER BY sid_serial;




-- list temporary tablespace usage.

SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total


reference:
http://www.idevelopment.info/data/Oracle/DBA_tips/Tablespaces/TBS_3.shtml
http://gavinsoorma.com/2009/06/temp-tablespace-usage/

oracle database 11.2.0.3 release patch set 2

Patch 10404530: 11.2.0.3.0 PATCH SET FOR ORACLE DATABASE SERVER

11.2.0.3 Patch Set - List of Bug Fixes by Problem Type [ID 1348303.1]

ALERT: Oracle 11g Release 2 (11.2) Support Status and Alerts [ID 880782.1]

Installation Types and Associated Zip Files
Installation Type Zip File

Oracle Database (includes Oracle Database and Oracle RAC) p10404530_112030_platform_1of7.zip

Note: you must download both zip files to install Oracle Database. p10404530_112030_platform_2of7.zip


Oracle Grid Infrastructure (includes Oracle ASM, Oracle Clusterware, and Oracle Restart) p10404530_112030_platform_3of7.zip

Oracle Database Client p10404530_112030_platform_4of7.zip

Oracle Gateways p10404530_112030_platform_5of7.zip

Oracle Examples p10404530_112030_platform_6of7.zip

Deinstall p10404530_112030_platform_7of7.zip

ASM

v$asm_diskgroup
v$asm_disk


create tablespace tbsname datafile size 10m autoextend on maxsize 10g;

alter tablespace tbsname add datafile size 10m autoectend on maxsize 10g;

View Description

V$ASM_ALIAS


In an ASM instance, contains one row for every alias present in every disk group mounted by the ASM instance.

In a DB instance, contains no rows.

V$ASM_ATTRIBUTE


Displays one row for each attribute defined. In addition to attributes specified by CREATE DISKGROUP and ALTER DISKGROUP statements, the view may show other attributes that are created automatically. Note that attributes are only displayed for disk groups where COMPATIBLE.ASM is set to 11.1 or higher.

V$ASM_CLIENT


In an ASM instance, identifies databases using disk groups managed by the ASM instance.

In a DB instance, contains information about the ASM instance if the database has any open ASM files.

V$ASM_DISK


In an ASM instance, contains one row for every disk discovered by the ASM instance, including disks that are not part of any disk group.

In a DB instance, contains rows only for disks in the disk groups in use by that DB instance.

This view performs disk discovery every time it is queried.

V$ASM_DISK_IOSTAT


Displays information about disk I/O statistics for each ASM client.

In a DB instance, only the rows for that instance are shown.

V$ASM_DISK_STAT


In an ASM instance, contains the same columns as V$ASM_DISK, but to reduce overhead, does not perform a discovery when it is queried. It does not return information about any disks that are new to the storage system. For the most accurate data, use V$ASM_DISK instead.

V$ASM_DISKGROUP


In an ASM instance, describes a disk group (number, name, size related info, state, and redundancy type).

In a DB instance, contains one row for every ASM disk group mounted by the local ASM instance.

This view performs disk discovery every time it is queried.

V$ASM_DISKGROUP_STAT


In an ASM instance, contains the same columns as V$ASM_DISKGROUP, but to reduce overhead, does not perform a discovery when it is queried. It does not return information about any disks that are new to the storage system. For the most accurate data, use V$ASM_DISKGROUP instead.

V$ASM_FILE


In an ASM instance, contains one row for every ASM file in every disk group mounted by the ASM instance.

In a DB instance, contains no rows.

V$ASM_OPERATION


In an ASM instance, contains one row for every active ASM long running operation executing in the ASM instance.

In a DB instance, contains no rows.

V$ASM_TEMPLATE


In an ASM or DB instance, contains one row for every template present in every disk group mounted by the ASM instance.

send email automatically in linux

1. mail
mail -s "test" test@test.com < messge.txt mail -s "test" test@test.com <test
>test
>test
>EOF

2. mutt
mutt -s "test" -a test.log test@test.com < message.txt mutt -s "test" -a test.log test@test.com <test
>test
>test
>EOF

ATTN: uuencode can be used with mail to attach file, but it was not installed by default. To install uuencode, install package: sharutils

reference:
http://www.cyberciti.biz/tips/sending-mail-with-attachment.html
http://www.thegeekstuff.com/2009/12/how-to-send-an-email-with-attachment-and-body-from-linux/

escape in sqlplus

set colsep ',' #used to generate csv file

set escape \ #used to escape substitution charactor in sql, not for sql processing.

for example select '\&1' from dual;

if you want to escape character in like clause

use .... like '\_sss' escape '\'

reference:
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:12520384680605

date format in oracle loader

start_date TIMESTAMP "Mon dd yyyy hh:mi:ss:ff3AM",

select to_char(systimestamp,'Mon-dd-yyyy hh:mi:ss:ff3AM') from dual;

reference:
http://www.dbforums.com/oracle/1118058-sql-loader-date-format-unrecognized.html

date in unix

date +%Y_%m_%d

%% a literal %

%a localeâs abbreviated weekday name (e.g., Sun)

%A localeâs full weekday name (e.g., Sunday)

%b localeâs abbreviated month name (e.g., Jan)

%B localeâs full month name (e.g., January)

%c localeâs date and time (e.g., Thu Mar 3 23:05:25 2005)

%C century; like %Y, except omit last two digits (e.g., 21)

%d day of month (e.g, 01)

%D date; same as %m/%d/%y

%e day of month, space padded; same as %_d

%F full date; same as %Y-%m-%d

%g last two digits of year of ISO week number (see %G)

%G year of ISO week number (see %V); normally useful only with %V

%h same as %b

%H hour (00..23)

%I hour (01..12)

%j day of year (001..366)

%k hour ( 0..23)

%l hour ( 1..12)

%m month (01..12)

%M minute (00..59)

%n a newline

%N nanoseconds (000000000..999999999)

%p localeâs equivalent of either AM or PM; blank if not known

%P like %p, but lower case

%r localeâs 12-hour clock time (e.g., 11:11:04 PM)

%R 24-hour hour and minute; same as %H:%M

%s seconds since 1970-01-01 00:00:00 UTC

%S second (00..60)

%t a tab

%T time; same as %H:%M:%S

%u day of week (1..7); 1 is Monday

%U week number of year, with Sunday as first day of week (00..53)

%V ISO week number, with Monday as first day of week (01..53)

%w day of week (0..6); 0 is Sunday

%W week number of year, with Monday as first day of week (00..53)

%x localeâs date representation (e.g., 12/31/99)

%X localeâs time representation (e.g., 23:13:48)

%y last two digits of year (00..99)

%Y year

%z +hhmm numeric timezone (e.g., -0400)

%:z +hh:mm numeric timezone (e.g., -04:00)

%::z +hh:mm:ss numeric time zone (e.g., -04:00:00)

%:::z numeric time zone with : to necessary precision (e.g., -04,
+05:30)

%Z alphabetic time zone abbreviation (e.g., EDT)

Informatica

1. startup
$INFA_HOME/tomcat/bin/infaservice.sh startup

2. shutdown
$INFA_HOME/tomcat/bin/infaservice.sh shutdwon

log file: $INFA_HOME/tomcat/logs/catalina.out


3. change database connection info

$INFA_HOME/server/infasetup.sh updateGatewayNode -da database_hostname:database_port -ds database_name

4. change database password
$INFA_HOME/server/infasetup.sh UpdateGatewayNode -databasepassword

./infasetup.sh UpdateGatewayNode -da [new database host]:[new database port] -du [new database user] -dp [new database password -dt [database type i.e. db2] -ds [new database name]


reference:
https://forums.oracle.com/forums/thread.jspa?threadID=2240006
https://community.informatica.com/thread/27876
http://www.tek-tips.com/viewthread.cfm?qid=1561513

vncserver

1. start a vncserver session
vncserver
2. kill a vncserver session
vncserver -kill :1 (display number)

reclaim disk space from oracle tablespace;

select 'alter database datafile ' || a.file_id || ' resize ' || round((a.bytes - b.free_space + 1024*1024)/1024/1024)|| 'm ;'
from dba_data_files a,
(select sum(bytes) free_space , file_id from dba_free_space group by file_id) b
where a.file_id = b.file_id
;

----------
----------

set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report

column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/

select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
/

column cmd format a75 word_wrapped

select 'alter database datafile '''||file_name||''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/


references:
http://dbaforums.org/oracle/index.php?showtopic=4444
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:54178027703899
http://asktom.oracle.com/pls/asktom/f?p=100:11:3259860865329305::::P11_QUESTION_ID:153612348067

Enterprise manager 11g (database control or grid control)

Check dbconsole web
emctl status dbconsole

https://192.168.1.1:1158/em

bin/rm: Argument list too long.

find . -name "*.aud" | xargs rm

Why is V$SESSION.SQL_ID null for an ACTIVE session

we may get sql_in from v$active_session_history(the top_level_sql column in V$ACTIVE_SESSION_HISTORY reports the sql_id )


reference:
https://forums.oracle.com/forums/thread.jspa?threadID=2244920

massive deletion solution

massive DELETE operation deletes millions of rows from a table with indexes and constraints. This operation is database intensive and time consuming, mainly because it forces the database to generate and save to disk significant amounts (possibly gigabytes) of redo and undo data.
You can perform massive DELETEs as bulk INSERT operations: instead of removing data you no longer need, you insert data you want to keep. The key to this option is performing it efficiently with minimum logging by using direct-path INSERT. Part 1 of this article series compares direct-path INSERT and regular INSERT. It also describes the technique of performing DELETEs as INSERTs. Part 2 will discuss the packaged APIs that implement the bulk INSERT technique.

Problem
How do I complete a massive DELETE operation in my Oracle database without having to pay the heavy performance overhead?

Solution.
Perform the massive DELETE operation as a direct-path (direct-load) INSERT (INSERT with APPEND hint) into a table whose logging parameter is set to NOLOGGING. This operation will complete significantly faster than DELETE and with minimum logging, but you have to take a backup afterwards to establish a new baseline.

Why INSERT Could Be Faster Than DELETE (or UPDATE)
Direct-path INSERT is a special database operation. Like SQL*Loader, it writes data directly to the database files, bypassing the buffer cache. It does this with minimum logging, recording only data dictionary changes. The logic behind this method is that because the data files are already up-to-date when an instance failure occurs, logging isn't necessary.

The two distinct cases in which direct-path INSERT is important are when:

1.The database is in noarchivelog mode. Media recovery is not possible, and you don't need redo data for that either.
2.The database is in archivelog mode. It logs redo blocks for media recovery by default. However, if you explicitly set a table to NOLOGGING mode, the database won't log redo blocks.
Therefore, with the direct-path INSERT when the database is in noarchivelog mode or when it is in archivelog mode and the table is in NOLOGGING mode, it performs only minimum redo logging—to protect the data dictionary.


reference:
http://www.devx.com/dbzone/10MinuteSolution/22191

last_call_et in v$session

this column give the running time of active session in seconds or waiting time of inactive session.

last call et is time in seconds that the backgrounds have been work -- roughly the same..

lock blocking

view name: v$lock

Note the BLOCK column. If a session holds a lock that's blocking another session, BLOCK=1. Further, you can tell which session is being blocked by comparing the values in ID1 and ID2. The blocked session will have the same values in ID1 and ID2 as the blocking session, and, since it is requesting a lock it's unable to get, it will have REQUEST > 0.

select l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2;

ROW_WAIT_OBJ# is the object_id but dbms_rowid.rowid_create expects the data_object_id
ROW_WAIT_FILE# is the absolute file_id but dbms_rowid.rowid_create expects a relative file number

So that will be wrong when object_id<>data_object_id. You can test that after truncating the table - as data_object_id will change
And it will be wrong as well when file_id<>relative_fno. You will have that case when having a big number of datafiles.


select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;

Note that ID1 and ID2 in Session 2, which is requesting the TX lock (LMODE=0, REQUEST=6), point back to the rollback and transaction entries for Session 1. That's what lets us determine the blocking session for Session 2.

find object_id and info from v$session;

select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
from v$session where sid=479 ;

generate rowid

select do.object_name,
row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
from v$session s, dba_objects do
where sid=543
and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;




reference
http://orafaq.com/node/854


comment
I came upon your article that is excellent.
However, there is a small mistake about dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# ):

ROW_WAIT_OBJ# is the object_id but dbms_rowid.rowid_create expects the data_object_id
ROW_WAIT_FILE# is the absolute file_id but dbms_rowid.rowid_create expects a relative file number

So that will be wrong when object_id<>data_object_id. You can test that after truncating the table - as data_object_id will change
And it will be wrong as well when file_id<>relative_fno. You will have that case when having a big number of datafiles.

That means that you need to join with dba_objects to get the data_object_id and with dba_data_files to get the absolute file_id.

Oracle Secure Backup

oracle Secure Backup is an individual product for enterprise backup solution.

It is like NetBackup, which need an infrastructure setup with tape machine and library.

Oracle secure backup have good interface to RMAN and provide oracle database backup facility. it can also backup file systems.

Oracle secure backup need server and client setup and provide quick tape location to speed restore time.

oracle promise oracle secure backup has best cost/performance than other enterprise backup software system.

explain plan

explain plan statement will generate execution plan for sql statement in oracle against current environment ( statistics, optimizer settings)

explain plan statement will populate the result to plan table
public synonym plan_table
real tale sys.plan_table$

any user can generate its one plan table by run below script
@?/rdbms/admin/utlxplan.sql


resulted execution plan can be pull out by run below script
@?/rdbms/admin/utlxpls.sql

1. UTLXPLS.SQL This script displays the plan table output for serial processing.

2. UTLXPLP.SQL This script displays the plan table output including parallel execution columns.

or use below statement

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());


the execution plan from explain plan statement may not be the exact running plan if environment changed when a statement is executed.

real execution plan info can be found in below dynamic views
v$sql_plan and V$SQL_PLAN_STATISTICS and V$SQL_PLAN_STATISTICS_ALL


explain plan statement examples
1. explain plan for STATEMENT
2. EXPLAIN PLAN
SET STATEMENT_ID = 'st1' FOR statement;
3. EXPLAIN PLAN
INTO my_plan_table
FOR statement ;
4. EXPLAIN PLAN
SET STATEMENT_ID = 'st1'
INTO my_plan_table
FOR statement;





###############
DBMS_XPLAN.DISPLAY procedure

This procedure accepts options for displaying the plan table output. You can specify:

*

A plan table name if you are using a table different than PLAN_TABLE
*

A statement Id if you have set a statement Id with the EXPLAIN PLAN
*

A format option that determines the level of detail: BASIC, SERIAL, and TYPICAL, ALL,

Some examples of the use of DBMS_XPLAN to display PLAN_TABLE output are:

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

SELECT PLAN_TABLE_OUTPUT
FROM TABLE(DBMS_XPLAN.DISPLAY('MY_PLAN_TABLE', 'st1','TYPICAL'));

add hard drive to linux

1. find the added disk
ls /dev/hd* for ide drive
or ls /dev/sd8 for scsi drive

2. fdisk -l /dev/sdb # find out the label of the new drive

3. fdisk /dev/sdb #create partition on new drive

4. mkfs -t ext3 /dev/sdb1 # create file system

5. mkdir /newdrive # create mount point

6. mount /dev/sdb1 /newdrive # mount the new drive

then you can use it

*** to add new drive to LVM group
1. pvdisplay # list physical volume

2. pvcreate /dev/sdb1 # create physical volume on sdb1

3. vgextend volumegroupname /dev/sdb1 #add new physical volume to exist volume group

4. lvextend -L+20G /dev/volumegroupname/root #extend logical volume

5. resize2fs /dev/volumegroupname/root #resize file system

You have space in new drive to user now.


To make access of disk block devices in RHL5/OEL5,

Step 1: Make an entry in /etc/udev/rules.d/60-raw.rules or create your own file for example ., /etc/udev/rules.d/-oracle-raw.rules (eg. 63-oracle-raw.rules)


#vi /etc/udev/rules.d/63-oracle-raw.rules
ACTION=="add", KERNEL=="sdb1", RUN+="/bin/raw /dev/raw/raw1 %N"
ACTION=="add", KERNEL=="sdc1", RUN+="/bin/raw /dev/raw/raw2 %N"
ACTION=="add", KERNEL=="sdd1", RUN+="/bin/raw /dev/raw/raw3 %N"
ACTION=="add", KERNEL=="sde1", RUN+="/bin/raw /dev/raw/raw4 %N"
ACTION=="add", KERNEL=="sdf1", RUN+="/bin/raw /dev/raw/raw5 %N"
KERNEL=="raw[1-2]*", OWNER="root", GROUP="oinstall", MODE="640"
KERNEL=="raw[3-5]*", OWNER="oracle", GROUP="oinstall", MODE="644"


So our sdb1 will be recognized as /dev/raw/raw1, sdb2 will be as /dev/raw/raw2 and so on.

Step 2: For the mapping to have immediate effect, run below command

#/bin/raw /dev/raw/raw1 /dev/sdb1
/bin/raw /dev/raw/raw2 /dev/sdc1
/bin/raw /dev/raw/raw3 /dev/sdd1
/bin/raw /dev/raw/raw4 /dev/sde1
/bin/raw /dev/raw/raw5 /dev/sdf1

Step3: Adjust the permission settings by,
chown root:oinstall /dev/raw/raw1
chown root:oinstall /dev/raw/raw2
chown oracle:oinstall /dev/raw/raw3
chown oracle:oinstall /dev/raw/raw4
chown oracle:oinstall /dev/raw/raw5
chmod 640 /dev/raw/raw1
chmod 640 /dev/raw/raw2
chmod 640 /dev/raw/raw3
chmod 640 /dev/raw/raw4
chmod 640 /dev/raw/raw5

Open /etc/sysconfig/rawdevices and add following lines:

/dev/raw/raw1 /dev/sdb1 /dev/raw/raw2 /dev/sdc1 /dev/raw/raw3 /dev/sdd1 /dev/raw/raw4 /dev/sde1

Restart the rwdevices service to make your changes effective.

# service rawdevices restart Assigning devices: /dev/raw/raw1 --> /dev/sdb1 /dev/raw/raw1: bound to major 8, minor 17 /dev/raw/raw2 --> /dev/sdc1 /dev/raw/raw2: bound to major 8, minor 33 /dev/raw/raw3 --> /dev/sdd1 /dev/raw/raw3: bound to major 8, minor 49 /dev/raw/raw4 --> /dev/sde1 /dev/raw/raw4: bound to major 8, minor 65 done


reference
http://www.turnkeylinux.org/blog/extending-lvm
http://arjudba.blogspot.com/2008/08/on-rhl5-rawdevices-unrecognized-service.html
http://oracleflash.com/11/How-to-Configure-ASM-disks-using-ASMLib-or-Raw-Disks.html

virtual console switch

alt-crl-fx

usually f7 is the gui console

yum and rpm

yum list package-name
yum install package-name or yum update package-name/rpm
yum erase package-name
yum search package-name # find package from repository
yum provides command-name # find package according to commmand

rpm -q package-name
rpm -i package-name
rpm -e package-name

yum will not install a package that public key is not imported, but rpm will install it with warning.

rlwrap

package: rlwrap-0.37-1.el5.i386.rpm

Now I could call sqlplus this way:
$ rlwrap sqlplus user/password@sid.

Finally I create an alias
alias sqlp =”rlwrap sqlplus”



alias sqlplus=”rlwrap sqlplus”

this I would avoid. rlwrap does not support non-interactive mode (echo xxx | sqlplus), is not an oracle support tool, and crashes occasionnaly.

Personnaly I do
alias q=”rlwrap sqlplus”
so that I can always use sqlplus to run important script, and keep rlwrap for not-productive stuff.


the first variant is ok:
$ alias sqlplus=”rlwrap sqlplus”

If you want history, then you run alias:
$ sqlplus

To run command instead of the alias you add backslash:
$ \sqlplus

du command

du -sh *
du -h --max-depth=1
du -k /u02 | sort -rn | less

screen command

$ screen
$ commands-here
(ctrl-a then ctrl-d) to detach
$ screen -r -ls (list screen session)
$ screen -r (to reattach to running screen session)

screen can be used to replace nohup

if you get error Cannot open your terminal '/dev/pts/1' - please check.
it is because you su to other user.

Try

sudo su - someuser
script /dev/null
screen

AWR failed

when you can not run awr report because of awr is not initialized.
if you don't need old statistics.


run @?/rdbms/admin/catnoawr.sql
run @?/rdbms/admin/catawr.sql

shutdown immediate
startup

set up sqlplus environment

set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 120
set pagesize 9999
column plan_plus_exp format a80
set termout off
define gname=idle
column global_name new_value gname
select lower(user)||'@'||substr(global_name,1,
decode(dot,0,length(global_name),dot-1) ) global_name
from (select global_name, instr(global_name,'.') dot from global_name);
set sqlprompt '&gname> '
set termout on

if you have database with the same sid and global_name is not setup, you can include hostname in sqlplus prompt.


define _editor=vi
set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 100
set pagesize 9999
column plan_plus_exp format a80
--column global_name new_value gname
set termout off
define gname=idle
define hostname=idle
column global_name new_value gname
column host_name new_value hostname
select lower(user) || '@' || substr(global_name, 1,
  decode(dot,0, length(global_name), dot - 1) ) global_name
  from (select global_name, instr(global_name, '.') dot from global_name );
select  substr(host_name, 1,
  decode(dot,0, length(host_name), dot - 1) ) host_name
  from (select host_name, instr(host_name, '.') dot from v$instance );
set sqlprompt '&gname..&hostname> '
set termout on

above lines need be put into login.sql file
and the path to the login.sql file need be defined as SQLPATH environment

invlalid number error

When column type is VARCHAR2()

sometimes you can compare the column with a number in one table and sometimes not in another table.

It depends on the data in the column. If all the data stored in the column has only digit characters, you will be able to compare otherwise you will fail.