WebLogic app to deploy

Run below command to create the war file (nidb-app.war):

jar cvf nidb-app.war test_app.jsp test_db.jsp test_driver.jsp includes/UTF-8.inc WEB-INF/web.xml

WEB-INF/web.xml
< ?xml version="1.0" encoding="UTF-8"?>
< !DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN" "http://java.sun.com/dtd/web-app_2_3.dtd">
<>
<>nidb_test
<>niDB Test App

<>
<>test_app.jsp
< /welcome-file-list>

< /web-app>



includes/UTF-8.inc
<% response.setHeader("Content-Type", "text/html; charset=UTF-8"); response.setContentType("text/html; charset=UTF-8"); request.setCharacterEncoding("UTF-8"); %>

Date time convert

select to_char(sysdate,'YYYYMMDD HH24:MI:SS') as "Eastern Daylight"
,to_char(new_time(sysdate,'EDT','GMT'),'YYYYMMDD HH24:MI:SS') dual

Adding data to CLOB fileds

Test table to try CLOBS

create table a (
id number primary key,
name varchar2(32),
info clob
);

insert into a values(1, 'Nazrul', 'Notest');
insert into a values(2 'Abu', 'You can add here upto 3999 char via sqlplus');

Good to use LOB function to do CLOB work so you can add upto 4gb of data.

DECLARE
v_text_loc CLOB;
v_offset INTEGER;
v_buffer VARCHAR2(100);
BEGIN
select info into v_text_loc from a where id = 15;
v_offset := DBMS_LOB.GETLENGTH (v_text_loc) + 1;
v_buffer := 'My txt The End.';
DBMS_LOB.WRITE (v_text_loc, length(v_buffer), v_offset, v_buffer);
END;
/

select '*'||info||'*' from a where id = 15;

About the Stars

Abu Qatadah mentioned Allah’s Statement "And We have adorned the nearest heaven with lamps," and said,
"The creation of these stars is for three purposes, i.e. as decoration of the (nearest) heaven, as missiles to hit the devils, and as signs to guide travellers. So if anybody tries to find a different interpretation, he is mistaken and just wastes his efforts, and troubles himself with what is beyond his limited knowledge"

Sahih Al Bukhari Vol 4, Chap 3, P 282.

MySQL groupby functions

Great fetaure of MySQL groupby function GROUP_CONCAT

select concat(DATE_FORMAT(date, '%a %d/%m/%Y'),' ',time,';',GROUP_CONCAT(total_gateways SEPARATOR ';'))
from tbl_concurrent_users where company_name = 'rabobank2'
group by date, time,company_server order by date,time

Good link to reference: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

Note on cursor

Note on open cursors
Good REF: http://orafaq.com/node/758

-- Total cursors open, by session
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current';
-- Total open cursors, by username & machine select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur, s.username, s.machine from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic# and s.sid=a.sid and b.name = 'opened cursors current' group by s.username, s.machine order by 1 desc;


Sample Oracle 9i network files (listener)

******************************************************************
* listener.ora
******************************************************************

# LISTENER.ORA Network Configuration File: /opt/oracle/product/9.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = plum)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/oracle/product/9.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = thenidb)
(ORACLE_HOME = /opt/oracle/product/9.2.0)
(SID_NAME = nidb)
)
)


My 9i sqlnet

******************************************************************
* sqlnet.ora
******************************************************************

# SQLNET.ORA Network Configuration File: /opt/oracle/product/9.2.0/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES)


9i tnsnames

******************************************************************
* tnsnames.ora
******************************************************************

# TNSNAMES.ORA Network Configuration File: /opt/oracle/product/9.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

NAZRULDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = plum)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = nazruldb)
)
)

INST1_HTTP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = plum)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = MODOSE)
(PRESENTATION = http://HRService)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)


10g Standby database

Creating a Physical Standby Database

On primary database

ALTER DATABASE FORCE LOGGING;

Did this on primary and then dropped, still not 100% sure where this goes.

ALTER DATABASE ADD STANDBY LOGFILE GROUP 10

('/u01/oracle/oradata/X1AR/redo01_stb.log') SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 11

('/u02/oracle/oradata/X1AR/redo02_stb.log') SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 12

('/u03/oracle/oradata/X1AR/redo03_stb.log') SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 13

('/u03/oracle/oradata/X1AR/redo04_stb.log') SIZE 50M;

SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

Create a password file if one does not already exist. Note password of sys needs to be same on both primary and standby.

For ease of modify parameters, I am making the primary database use pfile

create pfile from spfile;

cd /u01/oracle/product/10.2.0/dbs/

mv spfileX1DB.ora x_spfileX1DB.ora

shut immediate;

startup;

Parameters for primary database:

*.DB_NAME=X1DB

*.DB_UNIQUE_NAME=X1DB

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(X1DB,Z1DB)'

*.CONTROL_FILES='/u01/oracle/oradata/X1DB/control01.ctl','/u02/oracle/oradata/X1DB/control02.ctl','/u03/oracle/oradata/X1DB/control03.ctl'

*.LOG_ARCHIVE_DEST_1='LOCATION=/u04/oracle/arch_X1DB/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=X1DB'

*.LOG_ARCHIVE_DEST_2='SERVICE=Z1DB LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=Z1DB'

*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

*.LOG_ARCHIVE_MAX_PROCESSES=30

*.FAL_SERVER=Z1DB

*.FAL_CLIENT=X1DB

*.DB_FILE_NAME_CONVERT='Z1DB','X1DB'

*.LOG_FILE_NAME_CONVERT='/u01/oracle/oradata/Z1DB/','/u01/oracle/oradata/X1DB/','/u02/oracle/oradata/Z1DB/','/u02/oracle/oradata/X1DB/','/u03/oracle/oradata/Z1DB/','/u03/oracle/oradata/X1DB/'

*.STANDBY_FILE_MANAGEMENT=AUTO

Make sure primary database in ARCHIVELOG mode and restart

shut immediate;

startup mount;

ALTER DATABASE ARCHIVELOG;

alter database open;

mkdir /u01/oracle/oradata/Z1DB

mkdir /u02/oracle/oradata/Z1DB

mkdir /u03/oracle/oradata/Z1DB

mkdir /u04/oracle/ arch_Z1DB

Backup the primary database, I performed a cold backup, then startup priamy.

startup mount;

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/Z1DB.ctl';

alter database open;

Copy control file to standby box

cd /u01/oracle/oradata/Z1DB

scp oracle@lnx-suppdb1:/tmp/Z1DB.ctl .

mv Z1DB.ctl control01.ctl

cp control01.ctl /u02/oracle/oradata/Z1DB/control02.ctl

cp control01.ctl /u03/oracle/oradata/Z1DB/control03.ctl

Copy pfile from primary

cd $ORACLE_HOME/dbs

scp oracle@lnx-suppdb1:$ORACLE_HOME/dbs/initX1DB.ora .

mv initX1DB.ora initZ1DB.ora

Modify the standby parameters.

Make sure all the bdump, cdump… folder are in correct location.

Create password file on standby (same passwd as primary)

orapwd file=$ORACLE_HOME/dbs/orapwZ1DB password=oracle entries=4

Make sure Primary box password file there, or create new one:

orapwd file=$ORACLE_HOME/dbs/orapwX1DB password=oracle entries=4

Make sure entries in tnsnames files and listener know about the databases.

Startup standby database:

export ORACLE_SID=Z1DB

First time round gave error since pfile did not have primary db’s SID, it should have X1DB.

SQL> STARTUP MOUNT;

ORACLE instance started.

Total System Global Area 1879048192 bytes

Fixed Size 2021664 bytes

Variable Size 1308624608 bytes

Database Buffers 553648128 bytes

Redo Buffers 14753792 bytes

ORA-01103: database name 'X1DB' in control file is not 'Z1DB'

SQL> !

sqlplus /nolog

STARTUP MOUNT;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Error on primary database:

Getting error on primary (alert file), fix was to shutdown both primary and sandby then reviewed both init files and tnsnames files, corrected variables and removed extra spaces from end.

Sat Nov 24 02:40:11 2007

Error 12154 received logging on to the standby

Sat Nov 24 02:40:11 2007

Errors in file /u01/oracle/admin/X1DB/bdump/x1db_arcp_14281.trc:

ORA-12154: Message 12154 not found; No message file for product=RDBMS, facility=ORA

PING[ARCp]: Heartbeat failed to connect to standby 'Z1DB'. Error is 12154.

Test Scenario 1

On primary, switch log file.

ALTER SYSTEM SWITCH LOGFILE;

ALTER SYSTEM SWITCH LOGFILE;

Alert of standby shows:

Sat Nov 24 22:05:00 2007

RFS[15]: Archived Log: '/u04/oracle/arch_Z1DB/1_44_639415217.arc'

Primary database is in MAXIMUM PERFORMANCE mode

RFS[15]: No standby redo logfiles created

Sat Nov 24 22:05:05 2007

Media Recovery Log /u04/oracle/arch_Z1DB/1_44_639415217.arc

Media Recovery Waiting for thread 1 sequence 45 (in transit)

Test successful no the standby file system, can see:

/u04/oracle/arch_Z1DB

[oracle@lnx-qadb1 arch_Z1DB]$ ls -alrt

total 317556

..

-rw-r----- 1 oracle dba 4941824 Nov 24 22:05 1_44_639415217.arc

-rw-r----- 1 oracle dba 1024 Nov 24 22:29 1_46_639415217.arc

-rw-r----- 1 oracle dba 1398784 Nov 24 22:29 1_45_639415217.arc

..

Test Scenario 2

Execute below SQL on both primary and standby:

SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#

Test successful, saw data on both, similar numbers

Test Scenario 3

On primary, add new tablespace

create tablespace INDEXS datafile '/u01/oracle/oradata/X1DB/X1DB_INDEXS_01.dbf'

size 51264K autoextend off logging extent management local

uniform size 512K segment space management auto;

ALTER SYSTEM SWITCH LOGFILE;

ALTER SYSTEM SWITCH LOGFILE;

Alert of standby shows alert switches and adding of tablespace (test successful):

Sat Nov 24 21:48:48 2007

Media Recovery Log /u04/oracle/arch_Z1DB/1_41_639415217.arc

Recovery created file /u01/oracle/oradata/Z1DB/Z1DB_ INDEXS_01.dbf

Successfully added datafile 6 to media recovery

Datafile #6: '/u01/oracle/oradata/Z1DB/Z1DB_INDEXS_01.dbf'

Media Recovery Log /u04/oracle/arch_Z1DB/1_42_639415217.arc

Media Recovery Log /u04/oracle/arch_Z1DB/1_43_639415217.arc

Media Recovery Waiting for thread 1 sequence 44 (in transit)

Alhamdulillah saw Physical Standby 10g working successfully.

Did nothing more, for next test round, do more detail work post setup, e.g. how to everyday maintenance on standby.

SQL tracing

Session SQL trace:

alter session set sql_trace=true;

Other session SQL trace:

exec dbms_system.set_sql_trace_in_session(session_id, serial_id, true);

Auto trace

CONNECT / AS SYSDBA
@$ORACLE_HOME/SQLPLUS/ADMIN/PLUSTRCE.SQL

drop role plustrace;
Role dropped.
create role plustrace;
Role created.
.
grant plustrace to dba with admin option;
Grant succeeded.

GRANT PLUSTRACE TO HR;

Other session SQL trace:

SET AUTOTRACE ON

Eazy connect

Eazy connect, does not use TNSnames file
sqlplus system/oracle@192.168.1.0:1521/NIDB


TNS name files was there but spelling was wrong, 's' was missing tnsnames.ora file

[foglight5@lnx-quest admin]$ tnsping ACTIVE.LNX-ITDB1

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-NOV-2007 16:35:47

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:
/home/foglight5/oracle/product/10.2.0/network/admin/sqlnet.ora

TNS-03505: Failed to resolve name

Default Router Info

As a quick reference, this table shows the default addresses, usernames, and passwords for some common router manufacturers.

Username   Password
======               =======              ========   ========
3Com                 http://192.168.1.1   admin      admin
D-Link               http://192.168.0.1   admin     
Linksys              http://192.168.1.1   admin      admin
Microsoft Broadband  http://192.168.2.1   admin      admin
Netgear              http://192.168.0.1   admin      password

SqlPlus help

http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch2.htm

http://forums.oracle.com/forums/thread.jspa?threadID=585195&tstart=0

> Changing the default editor to Textpad for sqlplusw

In login.sql or glogin.sql put following:
DEFINE _EDITOR=yourFavouriteProgram.exe

start new sqlplus instance. It should now run your modified login/glogin.sql and use the new editor as default.

Tuning Shared Pool

select NAMESPACE,
GETS,
GETHITS,
round(GETHITRATIO*100,2) gethit_ratio,
PINS,
PINHITS,
round(PINHITRATIO*100,2) pinhit_ratio,
RELOADS,
INVALIDATIONS
from v$librarycache

SELECT shared_pool_size_for_estimate "Size of Shared Pool in MB",
shared_pool_size_factor "Size Factor",
estd_lc_time_saved "Time Saved in sec"
FROM v$shared_pool_advice;


Good reply bor Johthan Lewis
http://forums.oracle.com/forums/thread.jspa?threadID=587990&tstart=0


V$LIBRARYCACHE
http://www.fortunecity.com/skyscraper/oracle/699/orahtml/hayden/libcache.html

How to Calculate Your Shared Pool Size
Note: 1012046.6


Good doc on tuning shared pool
Metalink doc: 62143.1

Shared Pool Information
http://vsbabu.org/oracle/sect13.html

Unserstaing shared pool
http://download-uk.oracle.com/oowsf2005/003wp.pdf

Oracle Tuning from Uni
http://www.uaex.edu/srea/tuning.htm


Shared Pool Advisory
====================

SHOW PARAMETER statistics_level;

SELECT shared_pool_size_for_estimate,
shared_pool_size_factor,
estd_lc_time_saved
FROM v$shared_pool_advice;

http://www.dbasupport.com/oracle/ora9i/shared_pool.shtml

http://www.orafaq.com/scripts/performance/orayaps.txt

Tax office

0845 302 1455 - Craig
0845 300 3949 - Sape
0845 302 1409 - JP

Allah

Ar-Razzaq: The Provider
Al-Haqq: The Truth
Ar Rehman: The Beneficent
Ar-Raqeeb: The Watchful

Data Pump

Default locaion for dump and log:
/u01/oracle/product/10.2.0/rdbms/log/

SQL> select * from dba_directories WHERE directory_name = 'DATA_PUMP_DIR';

expdp userid=system/oracle schemas=nidb_test dumpfile=my_exp1.dmp logfile=log_exp1.log

create directory ni_dmp_dir as '/home/oracle/db_dumps';

grant read, write on directory ni_dmp_dir to nislam;

expdp userid=system/oracle schemas=nidb_test directory=ni_dmp_dir dumpfile=my_exp1.dmp logfile=log_exp1.log




select * from dba_datapump_jobs;

Date diff

You have table a with col t and f, to get diff of dates

select trunc( t-f ) "Day",
trunc( mod( (t-f)*24, 24 ) ) "Hr",
trunc( mod( (t-f)*24*60, 60 ) ) "Mi"
from a;

Clone db using RMAN & Point in time

I have performed another successful test with this on 26 Sept 2007 (this time clone point in time), worked like a dream on a staging env. See word document for more detail.

Good links:
http://users.telenet.be/oraguy.be/make_a_clone_of_your_database_1.htm
http://www.oracle-base.com/articles/9i/DuplicateDatabaseUsingRMAN9i.php

##############
# niDB's note on Restore as Clone db.
#
# Basic steps, please see docs for more detail. Run the steps with care.
# DO prepare a test on your staging evn before running these command on Prod
# 
# Tested success on 9i and 10g (Linux)
#
# [Source db]=NIDEV1, [Clone db]=NITST1, 
##############


# Make sure all the backup files are available on the source db box.
# If offline or on tape copy them back and make sure Oracle user can read them.
##############


# On clone box, create password file
##############
cd $ORACLE_HOME/dbs
orapwd file=orapwNITST1 password=oracle entries=5


# In the source database box, add following to your TNSNAMES file.
##############
NITST1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbtst1.nidb)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = NITST1)
    )
  )



# In the clone database box, add following to your LISTENER file & reload.
##############
    (SID_DESC =
      (GLOBAL_DBNAME = NITST1)
      (ORACLE_HOME = /opt/oracle/product/9.2.0)
      (SID_NAME = NITST1)
    )

$ lsnrctl reload



# Based on the source db, create pfile for Clone database (copy original database 
# pfile and modify below).
##############
*.background_dump_dest='/u01/oracle/admin/NITST1/bdump'
*.core_dump_dest='/u01/oracle/admin/NITST1/cdump'
*.user_dump_dest='/u01/oracle/admin/NITST1/udump'

*.db_name='NITST1'
*.instance_name='NITST1'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=NITST1XDB)'

# Added following to pfile so we use pfile to change location:
*.DB_FILE_NAME_CONVERT=(/u01/oracle/oradata/NIDEV1, /u01/oracle/oradata/NITST1)
*.LOG_FILE_NAME_CONVERT=(/u01/oracle/oradata/NIDEV1, /u01/oracle/oradata/NITST1)


# Copy the RMAN backup files from source database to clone server. 
# Kept the location of backup files identical to original database server.
# Start the clone database on nomount, make sure you exit:
##############
   sqlplus /nolog
   startup nomount
   exit


# To duplicate to clone database, on Souce connect to Source, rman catalog & clone 
# database
# Use following command to connect
##############
export ORACLE_SID=NIDEV1
export NLS_LANG=.UTF8
export NLS_DATE_FORMAT='Mon DD YYYY HH24:MI:SS'

$ rman target / catalog rman/rman@[SOURCE_RMAN] auxiliary sys/oracle@NITST1

RMAN> run {
set newname for datafile 1 to '/u02/oracle/oradata/NITST1/system01.dbf';
set newname for datafile 2 to '/u02/oracle/oradata/NITST1/undotbs01.dbf';
set newname for datafile 3 to '/u02/oracle/oradata/NITST1/sysaux01.dbf';
set newname for datafile 4 to '/u02/oracle/oradata/NITST1/users01.dbf';
set newname for datafile 5 to '/u02/oracle/oradata/NITST1/NITST1_APP_01.dbf';
set newname for datafile 6 to '/u01/oracle/oradata/NITST1/NITST1_INDEX_01.dbf';
set newname for datafile 7 to '/u01/oracle/oradata/NITST1/NITST1_LARGE_DATA_01.dbf';
set newname for datafile 8 to '/u01/oracle/oradata/NITST1/NITST1_tools_01.dbf';
set newname for tempfile 1 to '/u02/oracle/oradata/NITST1/temp01.dbf';
DUPLICATE TARGET DATABASE TO NITST1 UNTIL TIME 'Sep 14 2005 14:17:00'
LOGFILE
GROUP 1 ('/u01/oracle/oradata/NITST1/redo01.log') SIZE 50M,
GROUP 2 ('/u02/oracle/oradata/NITST1/redo02.log') SIZE 50M,
GROUP 3 ('/u03/oracle/oradata/NITST1/redo03.log') SIZE 50M;
}


# Shudown clone and startup clone with resetlogs options.
##############
startup mount;
alter database open resetlogs;

Lob package and error

User getting following error on the new 10g

ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual:
6254, maximum: 4000)

This works on 9i but givs error on 10g
select
TO_CHAR(detail_info_clob)
from nidb_tab where DBMS_LOB.GETLENGTH(detail_info_clob) = 6254 and rownum <>


Fix: Above works on 9i but result is cut off afte 4K data, for all 4K+ clob shuld be using DBMS_LOB pkg.

select
DBMS_LOB.GETLENGTH(detail_info_clob)
from nidb_ta;

select
DBMS_LOB.SUBSTR(detail_info_clob, 3998, 1)
from nidb_tab where DBMS_LOB.GETLENGTH(detail_info_clob) = 6254 and rownum <>

select
DBMS_LOB.SUBSTR(detail_info_clob, 3998, 3995)
from nidb_tab where DBMS_LOB.GETLENGTH(detail_info_clob) = 6254 and rownum <>

rda

Could you run a rda report and upload to metalink ?
Reference Note.314422.1 Remote Diagnostic Agent (RDA) 4 - Overview and User Guide

PERFSTAT (stats) tablespace cleanup

Freeing space on PERF_STAT tablespace, first purge and then move tablespaces for large objects. (tested on 9i and 10g)


@?/rdbms/admin/sppurge

==

conn system

grant resource to perfstat;

conn perfstat/perfstat

alter table STATS$SQL_SUMMARY move tablespace active_v2;
alter table STATS$SQL_SUMMARY move tablespace perf_stat;
alter table STATS$SQLTEXT move tablespace active_v2;
alter table STATS$SQLTEXT move tablespace perf_stat;
alter table STATS$EVENT_HISTOGRAM move tablespace active_v2;
alter table STATS$EVENT_HISTOGRAM move tablespace perf_stat;
alter table STATS$LATCH move tablespace active_v2;
alter table STATS$LATCH move tablespace perf_stat;

select index_name, status from user_indexes where status not like 'VALID';

alter index STATS$LATCH_PK rebuild;
alter index STATS$SQL_SUMMARY_PK rebuild;
alter index STATS$SQLTEXT_PK rebuild;
alter index STATS$EVENT_HISTOGRAM_PK rebuild;

select index_name, status from user_indexes where status not like 'VALID';

conn system
revoke resource from perfstat;

Apache and PHP

Apache2 and PHP4

1. Unzip php and copy to C:\Php (better to keep it in that location and same name to avoid issues)

2. Copy C:\Php\Php4ts.dll to your Windows system folder 'C:\WINDOWS'

3. Rename and Copy C:\Php\php.ini-dist to your Windows system folder 'C:\WINDOWS\php.ini'

4. Modify httpd.conf and add below (you can also add extra e.g. docroot change etc)
#NI: PHP version 4, add in around line 175
LoadModule php4_module "C:/php/sapi/php4apache2.dll"

#NI: AddType, add in around line 775
AddType application/x-httpd-php .php
AddType application/x-httpd-php-source .phps

#NI: Change default to UTF8, around 709
#AddDefaultCharset ISO-8859-1
AddDefaultCharset UTF-8


More help:
See net or below link
http://www.builderau.com.au/strategy/architecture/soa/Get-Apache-2-and-PHP-4-working-together-in-Windows/0,339028264,320273988,00.htm

CV indexes Intermewdi

Error:
ORA-20000: Oracle Text error:
DRG-11207: user filter command exited with status 1
DRG-11222: Third-party filter does not support this known document format.
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.CTX_DOC", line 1129
ORA-06512: at "AR_STATESTREET_CONSULT.CVPREVIEW", line 23


Fix was:
up2date compat-libstdc++-296

Doc ID: Note:301260.1

meta doc siad:
compat-libstdc++-7.3-2.96.128.i386.rpm

10g features related general notes

More notes: http://www.globusz.com/ebooks/Oracle/00000010.htm

To change Thresholds

BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
METRICS_ID => dbms_server_alert.tablespace_pct_full,
WARNING_OPERATOR => dbms_server_alert.operator_ge,
WARNING_VALUE => '55',
CRITICAL_OPERATOR => dbms_server_alert.operator_ge,
CRITICAL_VALUE => '80',
OBSERVATION_PERIOD => 1,
CONSECUTIVE_OCCURRENCES => 1,
INSTANCE_NAME => NULL,
OBJECT_TYPE => dbms_server_alert.object_type_tablespace,
OBJECT_NAME => 'USERS');
END;
/

To turn of alerts for USERS tablespace

BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
METRICS_ID => dbms_server_alert.tablespace_pct_full,
WARNING_OPERATOR => dbms_server_alert.operator_do_not_check,
WARNING_VALUE => '0',
CRITICAL_OPERATOR => dbms_server_alert.operator_do_not_check,
CRITICAL_VALUE => '0',
OBSERVATION_PERIOD => 1,
CONSECUTIVE_OCCURRENCES => 1,
INSTANCE_NAME => NULL,
OBJECT_TYPE => dbms_server_alert.object_type_tablespace,
OBJECT_NAME => 'USERS');
END;
/

select * from RECYCLEBIN;

PURGE RECYCLEBIN;
DROP TABLE emp PURGE;

Search OPDG in metalink for perf tuning guide

Oracle Patch: http://www.oracle.com/technology/support/patches.htm
Oracle Alert: http://www.oracle.com/technology/deploy/security/alerts.htm

Back to fixed size SGA (on the fly):
alter system set sga_max_size=2G scope=spfile;
alter system set sga_target=0M scope=both;
alter system set shared_pool_size=384M scope=both;
alter system set db_cache_size=416M scope=both;
alter system set java_pool_size=16M scope=both;
alter system set large_pool_size=16M scope=both;

show parameter sga_max_size;
show parameter sga_target;
show parameter shared_pool_size;
show parameter db_cache_size;
show parameter java_pool_size;
show parameter large_pool;

Clean delete of database on Linux (so next install uses the original ports and files)

Remove entires from:
/u01/oracle/product/10.2.0/install/portlist.ini


rm $ORACLE_HOME/plumtree.com_NIDB
rm $ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_plumtree.com_NIDB

BLOB's

To find the table the lob belongs to:
select table_name from user_lobs where SEGMENT_NAME = 'SYS_LOB033344496$$';

or chr(10)

set sqlblanklines on

DBVERIFY
Perfom while db is up and file is offline / online.
dbv file='/u22/oracle/oradata/NIDB/NIDB_BLOB_DATA_07.dbf' blocksize=8192

Ramadan 2007

The ninth month of the Muslim lunar calendar, month of fasting throughout hours of daylight. Fasing is to abstain from eating, drinking, smoking, and sex from dawn to sunset. ItiIs also the month in which the Koran was revealed to Muhammad


Personal notes:
Rajaab [15, 16]
Sahbaan [M, T, 15]

Complete on 11 Oct 2007 (29th Ramada) at 5.50pm (40 min before Ifter)
Taqobalallahu Minna Waminkum

Forgotten vi commands

Below are the vi commands I keep forgetting if I don't use them for a month or so.

To comment below 9 lines:
:.,+9 s/^/#/ (or can do :1,20)

To set line no:
set nu

Global search replease:
:g/foo/ s//bar/g (or put c for confim)

Unix topics

split from chunk and download
split -b 100m nidb.dmp nidb_

find text in all files
find . -type f -exec grep "\"select" {} \; -print | grep jsp

mount share via nfs (this is softshare, has a time)
mount -o soft lnx-nidb1.jhq:/u02/oracle /mnt/nidb1

Unix mail from command line
echo "myTest body" | mail -s "myTest subject" nislam@sch.com

Format disk with ext3
mkfs.ext3 /dev/sda1

Restarting netowrk
service network restart

Plug the USB then check dmesg to see device location e.g. sdg: sdg1 sdg2
mount /dev/sdg1 /mnt/usbdisk

List open file (fuser / lsof)
============
fuser system01.dbf
/usr/sbin/lsof system01.dbf

Find old files and delete (30 days)
find /u01/oracle/NIDB/arch -maxdepth 1 -mtime +30 -name "*.arc" -exec rm -rf {} \;

Change OS time
cd /etc

ln -sf /usr/share/zoneinfo/Etc/GMT localtime

But most things will need a restart before it takes affect, can we find some time to action a reboot?

Check Netowrk speed
ethtool eth0

Database Disk Layout
Good discussion on db disk and Raid
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:359617936136

New Database (2*74) (12*300)
Here are disk layout I plan to implement on new db box.

Internal (2x 74GB) Raid 1
/boot         150MB
/             10GB
/home         5GB
Swap          16384MB
/u01          15GB             Ora Software, admin
/u02          25GB             Redo 1, Undo

External (6x 300GB) Raid 1+0
Swap          8192MB
/u11          150GB            App Datafile
/u12          150GB            App Datafile
/u13          100GB            App Datafile
/dbbackup1    500GB (Or rest)  RMAN backup and Exp dmp

External (4x 300GB) Raid 1+0
Swap          8192MB
/u21          100GB            Index
/u22          100GB            Index
/u23          100GB            Ineex
/dbbackup2    300GB (Or rest)  RMAN backup

External (2x 300GB) Raid 1
/u31          10GB             Redo 2, Temp
/u32          40GB             Redo 3, Arc
/u33          250GB (Or rest)  Other

If I had more disks I would have moved Redo 1 from OS / Software disk.

AWR

Read:
Transparent Data Encryption
http://www.oracle.com/technology/oramag/oracle/05-sep/o55security.html

Whats in SYSAUX?

select occupant_name, schema_name, space_usage_kbytes from v$sysaux_occupants;

For more information, go to http://tahiti.oracle.com


To generate AWR reports:

@$ORACLE_HOME/rdbms/admin/awrrpt



Explain Plan
Note: 235530.1 Recommended Methods for Obtaining a Formatted Explain Plan
@?/rdbms/admin/utlxplan
@?/rdbms/admin/utlxpls
set lines 130
set head off
alter session set cursor_sharing=EXACT;
select plan_table_output from table(dbms_xplan.display('PLAN_TABLE',null,'ALL'));

-- Plan of last executed SQL
select * from table(dbms_xplan.display_cursor(null,null, 'ALL'));

Note: 215187.1 Enhanced Explain Plan and related diagnostic info for one SQL statement

~50th Oracle 10g install, still seeing error

This is approximately my 50th Oracle 10g install, was little surprised when I saw error during this install.

Don't be too shocked, this time my Sys Admin let me down, he did not install some basic packages required by Oracle (that we previously agreed will be in OS install list). My kind Sys Admin installed those packages, things started to work like a dream.

1. UnsatisfiedLinkError
$ xterm opens fine but dbca/netca does no work, gives error!

[oracle@lnx-field ~]$ dbca
UnsatisfiedLinkError exception loading native library: njni10
Exception in thread "main" java.lang.UnsatisfiedLinkError: get
at oracle.net.common.NetGetEnv.get(Native Method)
at oracle.net.config.Config.getNetDir(Unknown Source)

Fix was: Install libgcc (and libstdbc++-devel) RPM (make sure 64 bit and 32 are there)
e.g.
up2date --arch i386 expat fontconfig freetype glibc-devel libgcc xorg-x11-deprecated-libs xorg-x11-libs xorg-x11-Mesa-libGL zlib libaio

Fix 2 was:
Got this again on Aug 2008, this time after lib install make sure to reinstall the software (or maybe you can just relink), I did the full software re-install of Oracle, since it onlye takes 15 min.

2. TNS: Lost Contact
Got following error during db creation via dbca (afte hitting Create DB), error came up on pop up.

ORA-12547: TNS:lost contact

Fix was: Install libaio RPM


3. Error towards end of 10g install (20 Aug 2007)
Failed to allocate port(s) in the specified range for the following process(es): JMS [5540-5559], RMI [5520-5539], Database Control [5500-5519], EM agent [3938] [1830-1849]
Could not complete the configuration. Refer to the log file for details.

Fix was: fix the /etc/host file make sure IP and hostname is correct.



4. Error ORA-00600 during startup (24 Aug 2007)
open_cursors = 300
pga_aggregate_target = 402653184
Thu Aug 23 17:07:03 2007
Errors in file /u01/oracle/admin/P1AR/udump/p1ar_ora_5067.trc:
ORA-00600: internal error code, arguments: [keltnfy-ldmInit], [46], [1], [], [], [], [], []
USER: terminating instance due to error 600
Instance terminated by USER, pid = 5067


Fix was: Make sure /etc/sysconfig/network is correct (hostname was different).
Why error: Sys Admin changed hostname few days ago, only after OS restart error started happening.


5. Oracle Install logs (dbca)
/u01/oracle/product/10.2.0/cfgtoollogs/dbca/SID

Hadith of the Day

http://www.quranexplorer.com/quran/
http://www.geocities.com/mtaufiq.rm/quran.html
http://www.altafsir.com

Current Mark
13 Nov 2007 : Arabic | 6.132 (page 145) (wooow, shocking this same place as 13 Sep)

11 Oct 2007 (Ramadan 29) : Arabic | Katam and started again, (just 30 min before the last iftar)

13 Sep 2007 (Ramadan 1) : Arabic | 6.132 (page 145)

13 Aug 2007 : Arabic & Eng | 6.57

Non-System tablespace recovery (online)

This test shows how to restore your non-system tablespace while the database is running.

Scenario

1. Create a user with the USERS tablespace it's default. Then add some test table and data.

2. Perfom your normal database backup

3. Add more data to the new schema

4. Now move the USERS tablespace e.g. mv users_01.dbf users_01.dbf.TMP

5. As SYSTEM resize users datafile, this will cause error since can't find tablespace e.g.

 SQL> alter database datafile '/u01/oracle/oradata/D1AR/users01.dbf' resize  105m;
 alter database datafile '/u01/oracle/oradata/D1AR/users01.dbf' resize  105m
 *
 ERROR at line 1:
 ORA-01565: error in identifying file '/u01/oracle/oradata/D1AR/users01.dbf'
 ORA-27037: unable to obtain file status
 Linux Error: 2: No such file or directory
 Additional information: 3

Recovery

6. Offline the tablespace, also no file 4 is not in the list

   SQL> alter tablespace users offline; -- or offline immediate;

 Tablespace altered.

 SQL> select * from v$backup;

      FILE# STATUS                CHANGE# TIME
 ---------- ------------------ ---------- ---------------
      1 NOT ACTIVE                  0
      2 NOT ACTIVE                  0
      3 NOT ACTIVE                  0

7. Restore the tablespace

  $ rman catalog rman/rman@rmandev

 connect target /

 restore tablespace users;
 recover tablespace users;

8. Restore and recover of TS done, now online TS

 SQL> alter tablespace users online;

 Tablespace altered.

9. Restart the db to make sure all is fine.

SQL> conn / as sysdba
Connected.
SQL> shut immediate;

SQL> startup

10. Do select on test table so see, all data are there.

11. Now do a backup if you wish

Dua's in prayer

Glory to my Lord, the Great

Allah hears those who praise Him (Sami' allahu liman hamida)

Our Lord, praise be to You (Rabbana lakal hamd)

Glory to my Lord, the Highest

My Lord forgive me, have mercy upon me, guide me, give me health and grant me sustenance.


Sura 6.79: Verily, I have turned my face towards Him who has created the heavens and the earth Hanifa, and I am not of Al-Mushrikun.

RMAN Complete Recovery

Database Disaster Recovery

Scenario

Backup was successful Wednesday night, RMAN backup started 22:00 GMT, and Tape backup started 11.30 GMT. Thursday morning due to disaster whole database box was lost.

Restore procedure from RMAN

1. Build new box from tape backup
2. Restore the Oracle software from tape
3. Restore the last three days RMAN backups
4. Test to see Oracle Software are valid, see if Sqlplus, rman works
5. Start the listener
6. Make sure all the databases are down, they mightily be partly up since they auto start when the box is booted
7. Follow the below RMAN recovery procedure

Export the SID e.g. export ORACLE_SID=P1AR

rman

connect catalog rman/@RMANPRD.jpldbpr01

connect target /

startup nomount;

set dbid=22397860;

restore controlfile from autobackup;

restore database;

alter database mount;

recover database;

alter database open resetlogs;

8. Test the restore

Take a RMAN backup

Important RMAN commands

set newname for datafile '/u01/oracle/D1NI/tools_01.dbf' to '/u01/oracle/D1NI/tools_01.dbf'

backup (archivelog all);
backup format '' (archivelog all);

RMAN Incomplete Recovery

Below is the expple for a timebase recover for a distaer (delete dbf, redo and ctl files).
16:18 (12 July 2007)
create table t1 (a number);

16:45 (12 July 2007)
insert into t1 values (1);
insert into t1 values (1);
insert into t1 values (1);
insert into t1 values (1);

16:56 (12 July 2007)
insert into t1 values (222);
insert into t1 values (222);
insert into t1 values (222);
insert into t1 values (222);

16:59 (12 July 2007)
insert into t1 values (999);
insert into t1 values (999);
insert into t1 values (999);
insert into t1 values (999);

17:05 (12 July 2007) Delete all files form /u01 D1AR inc system 1 redo, 1 control
[D1AR]$ ls
control01.ctl D1AR_INDX_01.dbf redo01.log system01.dbf undotbs01.dbf
D1AR_DRSYS_01.dbf D1AR_INDX_02.dbf sysaux01.dbf temp01.dbf users01.dbf

[D1AR]$ rm *.dbf
[D1AR]$ rm *.log
[D1AR]$ rm *.ctl



$ export NLS_LANG=.UTF8
$ export NLS_DATE_FORMAT='Mon DD YYYY HH24:MI:SS'
$ rman catalog rman/rman@rmandev
RMAN> connect target /
run {
restore database;
restore controlfile;

set until time 'JUL 12 2007 16:57:00';
recover database;
}

sql "alter database open resetlogs";

shutdown immediate;
startup;

Successful TEST, database is usable and output is:

SQL> select * from t1;

A
----------
1
1
1
1
222
222
222
222

The Opening (Al-Fâtihah)

1.
In the Name of Allâh (God), the Most Beneficent, the Most Merciful.
2.
All the praises and thanks be to Allâh, the Lord of the 'Alamîn (mankind, jinns and all that exists).
3.
The Most Beneficent, the Most Merciful.
4.
The Only Owner (and the Only Ruling Judge) of the Day of Recompense (i.e. the Day of Resurrection)
5.
You (Alone) we worship, and You (Alone) we ask for help (for each and everything).
6.
Guide us to the Straight Way
7.
The Way of those on whom You have bestowed Your Grace, not (the way) of those who earned Your Anger, nor of those who went astray.

More reading: http://en.wikipedia.org/wiki/Al-Fatiha

How to find the current SCN

-- Works on 9i and 10g
select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;

-- Works on 10g
select CURRENT_SCN from v$database;

-- Other useful tables
select * from v$log_history
select * from v$log;

Temp tablespace usage Oracle 9i/10g

Below is the query that I would use to find out usage of temp tablespace:

select
  df.TABLESPACE_NAME,
  df.df_size/1048576 AS TOTAL,
  nvl(us.free_sum, 0)/1024/1024 AS USED,
    (df.df_size/1048576 - (nvl(us.free_sum, 0)/1024/1024)) AS FREE,
    df.FILE_COUNT
from
    ( select TABLESPACE_NAME,
            sum(bytes) as df_size,
            count(TABLESPACE_NAME) AS FILE_COUNT
     from dba_temp_files
     group by TABLESPACE_NAME ) df,
    ( select TABLESPACE,
            sum(BLOCKS)*(select value from v$parameter where upper(name) like '%DB_BLOCK_SIZE%') free_sum
     from v$sort_usage group by TABLESPACE ) us
where df.TABLESPACE_NAME = us.TABLESPACE(+)
order by TABLESPACE_NAME;

Sample output:

TABLESPACE_NAME                     TOTAL       USED       FREE FILE_COUNT
------------------------------ ---------- ---------- ---------- ----------
TEMP                                 1024          0       1024          1