All 111 entries tagged Oracle
View all 116 entries tagged Oracle on Warwick Blogs | View entries tagged Oracle at Technorati | There are no images tagged Oracle on this blog
January 25, 2012
Use Timestamp type instead of Date type
Today I noticed another problem caused by Date type.
select page3_.* from sb2.NEWS_ITEM this_ inner join sb2.CONTENT_FETCHER cf1_ on this_.news_id=cf1_.ID inner join sb2.CONTENT content2_ on cf1_.content_id=content2_.id inner join sb2.PAGE page3_ on content2_.page_id=page3_.id where ( this_.discriminator = 'calendar_item') and page3_.LAST_UPDATED_DATE>:1 order by page3_.LAST_UPDATED_DATE asc, this_.publication_date asc
The SQL Tunning Advisor suggested that “page3_.LAST_UPDATED_DATE>:1 ” has an implicit data type conversion prevent the optimizer from selecting indices.
The LAST_UPDATED_DATE column is of “DATE” type.
From V$SQL_MONITOR, the bind variable is:<binds> <bind name=":1" pos="1" dty="180" dtystr="TIMESTAMP" maxlen="11" len="7" format="hexdump">78640101010101</bind> </binds>
After inspecting the source code, it is appear bind as java.util.Date.
I think it should be bound as java.sql.Date, not java.sql.Timestamp or java.util.Date. See http://databaseperformance.blogspot.com/2009/01/java-data-types-and-oracle-index-usage.html
Oracle saw the value it was getting was of type TIMESTAMP, which has sub-second values in it. And rather than lose precision by truncating its value, it was instead converting the DATE value in each data row to a TIMESTAMP value before comparing.
Better to use Timestamp type instead of Date type. The index should work properly whether you bind java.sql.Date or java.sql.Timestamp.
Data Type Conversion
Oracle will convert the value to most precise type:
Column type DATE, predicate value is TIMESTAMP => Right size is more precise => Convert every row to timestamp
Column type Timestap, predicate value is DATE => Left size is more precise => convert predicate to Timestap
Column type varchar, predicate value is number => Right size is more precise => convert every row to varchar
Column type is number, predicate value is varchar => Left size is more precise => convert predicate value to number
Hongfeng Sun
25 Jan 2012 16:12
| 
Tags: Oracle
| 
Comments (0)
| 
Report a problem
Please wait - comments are loading
January 12, 2012
Delete old rman backup piece manually
My Nagios checking reported there were an old backup dated back 2010. There is no such backup on disk. RMAN crosscheck and delete wont be able to remove it.
Tried to delete it with “nice” rman command:RMAN> delete backup tag = 'TAG20101029T101009'; allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=317 device type=DISK specification does not match any backup in the repository RMAN> delete backuppiece tag = 'TAG20101029T101009'; using channel ORA_DISK_1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of delete command at 01/12/2012 09:59:49 RMAN-06168: no backup pieces with this tag found: TAG20101029T101009Resort to manually deleting in the base table:
SELECT * FROM rc_backup_set where db_id='1320220071' order by start_time select * from bp where db_key=10492312 and tag = 'TAG20101029T101009' delete from bp where db_key=10492312 and tag = 'TAG20101029T101009'
Hongfeng Sun
12 Jan 2012 10:55
| Tags: Oracle
| Comments (0)
| Report a problem
January 10, 2012
Install Oracle Instant Client on Ubuntu
1. Download instantclient-basic-linux.x64-11.2.0.3.0.zip and instantclient-sqlplus-linux.x64-11.2.0.3.0.zip and unzip them into /apps/oracle/instantclient_11_2
2. sudo apt-get install libaio1
3. Append the following line into ~/.bashrc
export LD_LIBRARY_PATH=/apps/oracle/instantclient_11_2
export SQLPATH=~/code/oracle-scripts/scripts:/app/oracle/instantclient_11_2
PATH=$PATH:/apps/oracle/instantclient_11_2
export TNS_ADMIN=/apps/oracle/instantclient_11_2
No need the TNS_ADMIN if you just want to use easy connect.
4. (optional)
create tnsnames.ora
5. Now sqlplus should works.
sqlplus system/password@oracle_server
This doc help me a lot.
Hongfeng Sun
10 Jan 2012 17:38
| Tags: Oracle
| Comments (0)
| Report a problem
Use online redefinition to change a table into a partitioned table
Created a test user hsun
drop table log purge;
drop table children purge;
drop table parent purge;
drop view children_less_10_v;
create table parent (id number);
alter table parent add constraint pk_parent_id primary key (id);
create table log (msg varchar2(50));
create table children (id number,pid number, constraint pk_id primary key (id));
alter table children add constraint fk_pid foreign key (pid) references parent (id);
create view children_less_10_v as select * from children where id < 10;
create or replace trigger children_tri
before insert or update on children
for each row
begin
insert into log values('new' || :new.id);
end;
/
insert into parent select rownum from dual connect by level < 15;
insert into children select rownum, rownum from dual connect by level < 15;
COMMIT;
exit;
Run the script status.sql that contains the following SQL:
set lin 200;
col object_name format a20
select object_name, object_type, status from user_objects order by 2, 1 ;
select trigger_name , table_name from user_triggers order by 2;
select view_name, text from user_views;
select CONSTRAINT_NAME, table_name, status from user_constraints order by 2;
select table_name, PARTITION_NAME from user_tab_partitions order by 1;
SQL> @/tmp/status.sql
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
PK_ID INDEX VALID
PK_PARENT_ID INDEX VALID
CHILDREN TABLE VALID
LOG TABLE VALID
PARENT TABLE VALID
CHILDREN_TRI TRIGGER VALID
CHILDREN_LESS_10_V VIEW VALID
7 rows selected.
TRIGGER_NAME TABLE_NAME
------------------------------ ------------------------------
CHILDREN_TRI CHILDREN
VIEW_NAME TEXT
------------------------------ --------------------------------------------------------------------------------
CHILDREN_LESS_10_V select "ID","PID" from children where id < 10
CONSTRAINT_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
FK_PID CHILDREN ENABLED
PK_ID CHILDREN ENABLED
PK_PARENT_ID PARENT ENABLED
no rows selected
Run as SYSDBA in a different session:
-- -- Check table can be redefined
EXEC DBMS_REDEFINITION.can_redef_table('HSUN', 'CHILDREN');
drop table hsun.children_p purge;
create table hsun.children_p (id number,pid number)
partition by range (id)
( partition p1 values less than (5),
partition p2 values less than (10),
partition p3 values less than (maxvalue));
-- Start Redefinition
EXEC DBMS_REDEFINITION.start_redef_table('HSUN', 'CHILDREN', 'CHILDREN_P');
SET SERVEROUTPUT ON
DECLARE
l_num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname => 'HSUN',
orig_table => 'CHILDREN',
int_table => 'CHILDREN_P',
copy_indexes => DBMS_REDEFINITION.cons_orig_params,
num_errors => l_num_errors);
DBMS_OUTPUT.put_line('l_num_errors=' || l_num_errors);
END;
/
-- Optionally synchronize new table with interim data before index creation
EXEC DBMS_REDEFINITION.sync_interim_table('HSUN', 'CHILDREN', 'CHILDREN_P');
-- Complete redefinition
EXEC DBMS_REDEFINITION.finish_redef_table( 'HSUN', 'CHILDREN', 'CHILDREN_P');
drop table hsun.children_p purge;
SQL> @/tmp/sta
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
PK_ID INDEX VALID
PK_PARENT_ID INDEX VALID
CHILDREN TABLE VALID
LOG TABLE VALID
PARENT TABLE VALID
CHILDREN TABLE PARTITION VALID
CHILDREN TABLE PARTITION VALID
CHILDREN TABLE PARTITION VALID
CHILDREN_TRI TRIGGER INVALID
CHILDREN_LESS_10_V VIEW INVALID
10 rows selected.
TRIGGER_NAME TABLE_NAME
------------------------------ ------------------------------
CHILDREN_TRI CHILDREN
VIEW_NAME TEXT
------------------------------ --------------------------------------------------------------------------------
CHILDREN_LESS_10_V select "ID","PID" from children where id < 10
CONSTRAINT_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
FK_PID CHILDREN ENABLED
PK_ID CHILDREN ENABLED
PK_PARENT_ID PARENT ENABLED
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
CHILDREN P1
CHILDREN P3
CHILDREN P2
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Solaris: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
- The primary key, foreign key and triggers remains “ENABLED”
- View and trigger are “INVALID”
Hongfeng Sun
10 Jan 2012 15:22
| Tags: Oracle
| Comments (0)
| Report a problem
January 05, 2012
Solaris df reported disk usage differently to du
I dropped the hitlogging tablespace whose size was 200+ gb.
But:
bash-3.00# df -h
la-ora01-db 588G 31K 44G 1% /oradb
la-ora01-db/data 588G 489G 44G 92% /oradb/data
bash-3.00# du -s -h /oradb/data
217G /oradb/data
-bash-3.00# for pid in `ps -ef | grep ora_ | awk '{print $2}'`; do
pfiles $pid | grep log ; done | sort | uniq
...
/oradb/data/stats2/hitlog2010.dbf
/oradb/data/stats2/hitlog2011.dbf
/oradb/data/stats2/hitlog2012.dbf
/oradb/data/stats2/hitlogempty.dbf
/oradb/data/stats2/hitlogmview01.dbf
...
After 3-4 hours waiting to remove the possibility of cache effect, I restarted the database
SQL>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
-bash-3.00# df -h
Filesystem size used avail capacity Mounted on
/ 39G 8.8G 30G 23% /
/dev 39G 8.8G 30G 23% /dev
la-ora01-db 588G 31K 316G 1% /oradb
la-ora01-db/data 588G 217G 316G 41% /oradb/data
Maybe some other user process hold the files? I only inspected the oracle background process and usually the most likely suspect is db file writer (dbwn).
Hongfeng Sun
05 Jan 2012 16:26
| Tags: Oracle
| Comments (0)
| Report a problem
December 20, 2011
UKOUG 2011
Another year of UKOUG conference closed its curtain. I attended all of the four days session, including the oaktable Sunday afternoon. I lost trace of how many times I went to this year end festival.
I now have some time to reflect what’s good :
Good Presentations:
A Deep Dive into the SQL monitoring Report by Greg Rahn
Oracle Optimizer: Upgraing to 11g without Pain by Maria Colgan
Oracle optimizer: Best Practices for Managing Optimizer Stats by Maria Colgan
Implementing Effective Database Auditing in 3 steps
Not too bad
user Experiences with Data Guard Fas-start Failover
Net Services – Best Practices for Performance, Scalability & High Availability by Kussi Mensah
Round Tables
Oracle scurity
RAC and HA roundtables
Keynote by Cary Millsap started to become interesting when I planned to leave. I ended up finished it.
The last day of the conference is not the best day. So if you can only go to conference for one day, don’t choose the last day.
Hongfeng Sun
20 Dec 2011 17:08
| Tags: Oracle
| Comments (0)
| Report a problem
February 16, 2011
Oracle locks
Follow-up to oracle lock – look it again from Oracle/Java/Others
Step 1
create table test (id number)
insert into test values (1);
Before commit or rollback, check the v$lock table.
Two locks:
First one’s lock type is TM (DML enqueue), Lock mode is 3 – row-x(SX), Request is 0 (none)
and
Second one’s lock type is TX (Transaction enqueue), Lock mode is 6 – exclusive (X), Request is 0 (none).
Check v$locked_object table. It displays the similar information as well
Step 2In the same session
update test set id = 2
The locks in v$lock and v$locked_objects show same information as step 1.
Step 3Open a new session
update test set id = 3
Now check the v$lock table
The lock associated with the first session is still same: two locks.
But the “block” column in the “TX” lock is 1 , means it is blocking another session.
The “block” column in “TM” lock is still 0.
Session 2 has two locks now:
TM lock, Lmode is 3 (SX), request is 0
TX lock, LMode is 6 (X), request is 6 (X – exclusive X)
Check the v$locked_object
The row attached to session 1 is same.
A new row attached to session 2 has same (locked_mode, object_id), but XIDUSN, XIDSLOT, XIDSQN are all zero.
Conclusion
v$locked_object is very useful. It displays 1) user 2) object_id information 3) blocker & waiter
how to find out locked object in v$lock?
Other tables
- dba_waiters: including waiters and blocker, mode_held & mode_requested
- dba_blockers: just one column holding_session.
- dba_lock_internal: Very slow to access
- v$access : Very slow to access
Hongfeng Sun
16 Feb 2011 11:25
| Tags: Oracle
| Comments (0)
| Report a problem
November 01, 2010
Restore point test
set db_flashback_retention_target = 120
create restore point, then two days later the flashback log is not deleted.
Some archive log is not deleted since
“RMAN-08139: WARNING: archived redo log not deleted, needed for guaranteed restore point”
Drop restore point, the flashback log was dropped as well in this command.
Hongfeng Sun
01 Nov 2010 15:48
| Tags: Oracle
| Comments (0)
| Report a problem
August 02, 2010
oracle listener config
I puppetized Oracle listener.ora, then suddenly I cannot connect to Oracle
sqlplus system/xxx@db_service
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Solaris-AMD64 Error: 2: No such file or directory
I checked the configuration, everything seems fine.
ORACLE_HOME=/package/oracle/product/10.2.0
ORACLE_SID=argon
After some fruitless effort, I notice the value $oracle_home in of /var/opt/oracle/oratab is /usr/local/oracle. /usr/local/oracle is the symlink of /package/oracle. I changed the value of /var/opt/oracle/oratab to /package/oracle and reset the environment variable ORACLE_HOME to /package/oracle. Restarted the database and I can now connect to database.
Hongfeng Sun
02 Aug 2010 15:23
| Tags: Oracle Oracle-Availability
| Comments (0)
| Report a problem
February 26, 2008
Wake up Oracle RFS
Follow-up to Force Oracle primary DB to reset connection to logical standby from Oracle/Java/Others
After another short period network outage, the primary database y wont sent archivelog to standby server.
set log_archive_dest_state_n = defer| enable cannot wake up oracle this time.
Fixed one server using the following:
Changedalter system set log_archive_dest_2='service=boston valid_for=(online_logfiles,primary_role) db_unique_name=boston';
to
alter system set log_archive_dest_2='service=boston lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=boston';
Update:
The preceding method only works slight better. During nigthtly rman backup, several logs missing (Omit 100 words …). Eventaully set log_archive_max_processes to a higher value fixed the problem
Hongfeng Sun
26 Feb 2008 15:10
| Tags: Oracle Oracle-Availability
| Comments (0)
| Report a problem
