oracle notes..

Oracle Audits Us

2009-12-31T00:48:00.000-08:00

I am now afraid to use some extra-cost features for testing purposes.
Oracle audits what features we have used in the database. You can query DBA_FEATURE_USAGE_STATISTICS view to see features you have used. DBA_HIGH_WATER_MARK_STATISTICS view shows max cpu count, maximum concurrent session count etc.

Reconfigure EM after changing hostname

2009-12-28T23:34:00.000-08:00

Change listener.ora and tnsnames.ora if necessary,

Change etc\hosts file,

emca -deconfig dbcontrol db -repos drop

emca -config dbcontrol db -repos create

this is it..

How To Delete a Service in Windows Server 2008

2009-12-03T00:14:00.001-08:00

Deleting a windows service in Windows Server 2008 is so easy,
Just write text below on command prompt, of course change the "service name" part:

sc delete "service name"

Oracle or Not

2009-11-08T08:51:00.000-08:00

Should i work as an oracle developer or not?

ORA-01839 When Using Interval

2009-07-29T08:47:00.001-07:00

I created a job that deletes 3 months past data.
During testing, i noticed that when using intervals, job fails in some days.


BEGIN
dbms_scheduler.create_job(
job_name        => 'purge_gps_archive_3_month',
job_type        => 'PLSQL_BLOCK',
job_action      => 'BEGIN delete from table_log where datetime_local<sysdate- interval "3" month; END;',
start_date=> to_date('28/07/2009 23:59','DD/MM/YYYY HH24:MI'),
repeat_interval => 'FREQ = DAILY; INTERVAL = 1',
enabled         => TRUE,
auto_drop       => FALSE,
comments        => 'This job deletes data from log table that are older than 3 months');
END;

After i use add_months instead of interval, problem solved.


BEGIN
dbms_scheduler.create_job(
job_name        => 'purge_gps_archive_3_month',
job_type        => 'PLSQL_BLOCK',
job_action      => 'BEGIN delete from table_log where datetime_local<add_months(sysdate,-3); END;',
start_date=""> to_date('28/07/2009 23:59','DD/MM/YYYY HH24:MI'),
repeat_interval => 'FREQ = DAILY; INTERVAL = 1',
enabled         => TRUE,
auto_drop       => FALSE,
comments        => 'This job deletes data from log table that are older than 3 months');
END;

Analytic 'First' and 'Last' Functions

2009-04-28T07:07:00.000-07:00

When you need a value from the first or last row of a sorted group, but the needed value is not the sort key, the FIRST and LAST functions eliminate the need for self-joins or views and enable better performance.(quoted from tahiti)

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/functions058.htm#sthref1414
http://www.psoug.org/reference/OLD/analytic_functions.html?PHPSESSID=26034dea1c0fb6e4ec0580b5f35f193f
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:12864646978683

Model Clause 1

2009-04-19T12:56:00.000-07:00

Here are some links for model clause, first two are from tahiti.
After i read and understand them, i will share some interesting parts.

http://www.oracle.com/technology/obe/obe10gdb/bidw/sqlmodel/sqlmodel.htm

http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/sqlmodel.htm#sthref1869

http://www.oreillynet.com/pub/a/network/2004/08/10/MODELclause.html

Automatic Workload Repository

2009-04-06T02:51:00.000-07:00

Creating Snapshots
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;

Dropping Snapshots
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 22,
high_snap_id => 32, dbid => 3310949047);
END;

Modifying Snapshot Settings
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 43200,
interval => 30, topnsql => 100, dbid => 3310949047);
END;

Run utlsyxsz.sql for sizing recommendations for the awr(in rdbms\admin).
http://www.ioug.org/performancetuning.pdf

SQL> @utlsyxsz.sql

This script estimates the space required for the SYSAUX tablespace.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
......

Run awrinfo.sql for awr components size.
SQL> @awrinfo.sql

http://forums.oracle.com/forums/thread.jspa?threadID=680095

Transport Awr Data to Test Environment
Run @$ORACLE_HOME/rdbms/admin/awrextr.sql in prod environment, move result into test environment and run @$ORACLE_HOME/rdbms/admin/awrload.sql to load into test environment.
After transporting awr data check dba_hist_snapshot for new awr data.Transported data has DBID of prod instance, so we can't use transported data in EM of test instance.
Reports for transported awr data can be produced via awrrpti.sql, awrsqrpi.sql , awrddrpi.sql or dbms_workload_repository package.

Generating AWR Reports

SQL> select t.snap_id,t.dbid,t.begin_interval_time from dba_hist_snapshot t order by t.snap_id desc;

SNAP_ID DBID BEGIN_INTERVAL_TIME
---------- ---------- -------------------------------------------------
9832 1055334377 06/04/2009 13:00:55,018
9831 1055334377 06/04/2009 12:01:04,018
.....
SQL> spool awr_rep_htm_03_04_09.html
SQL> select * from table(dbms_workload_repository.awr_report_html(1055334377 ,1,9795,9832 ));
......
SQL> spool off

Compare Period Report

SQL> spool awr_prod_01_apr_03_apr.html
SQL> select * from table(dbms_workload_repository.awr_diff_report_html(1055334377 ,1,9699 ,9722 ,1055334377,1,9747,9770));
SQL> spool off

Generating ASH Reports
Run ashrpt.sql or ashrpti.sql for reports.(in rdbms\admin)

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/autostat.htm#i27008

Performance Diagnosis Demystified: Best Practices for Oracle Database 10g

2009-04-06T02:09:00.000-07:00

I should check deeply:
http://www.oracle.com/technology/products/manageability/database/pdf/ow05/PS_S998_273998_106-1_FIN_v1.pdf

Power of Snapshots in WM

2009-04-04T15:32:00.000-07:00

Shrink operation decreased total virtual disk size to 40 GB, the gain was 14GB . Now first thing to do is take a snapshot of current state.
My main purpose is when i make irrevocable mistakes rolling back to previous snapshot; e.g: before and after database upgrade for test purposes. In addition, it can be usefull when i have different versions of database as snapshots on same virtual machine.
I upgraded my oracle instance from 10.2.0.1 to 10.0.2.3. And finally i upgraded to 11G R1.
Steps i followed:

Read upgrade docs. from tahiti,
Take snapshot of current version of oracle,
Install new oracle software,
Take snapshot of current state:previous version of instance with just new software ( before upgrade snashot)
Upgrade instance to new version.
Whenever irrevocable error occurs i roll back to previous snapshot.
When upgrade successfully finished, i uninstall previous oracle software.
Take snapshot of current state:new instance with new software only (after upgrade snapshot)

After all i have 5 snaphots so far. Now i can practice on 10.2.0.1 or 10.2.0.3 or 11.1.0.7.0.
My virtual machine now accoupies approximately 57 GB for all three versions.

Shrinking Disks in Vmware

2009-03-20T15:44:00.000-07:00

Today i am going to upgrade my mini database that is in vmware virtual machine.
I should do it step by step and in case of invalid situations i should go back to previous state.
Snapshot property of virtual machine is what i am looking for rolling back.
The virtual machine has size of approximately 54 GB and i don't think my harddisks can handle any more space for VM.
First thing i am going to do is shrink spaces on virtual disks, and only way to do is when the VM is on. Because after snapshot i am unable to shrink disks, I should do it before taking snapshots.
Right click the "VMware Tools" icon and click "Open VMware Tools". Select disks and start shrinking.
NOTE: You should have enough free space for shrink operation. Free space in host system should be bigger than virtual disk size.

Good afternoon world

2008-06-20T01:36:00.001-07:00

Never say never...