Ask The Real Tom

I created a simple schema with one table and flashback archive.

exp test

exp file=test.dmp owner=TKRIM

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TKRIM
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TKRIM
About to export TKRIM's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export TKRIM's tables via Conventional Path ...
EXP-00008: ORACLE error 1455 encountered
ORA-01455: converting column overflows integer datatype
EXP-00000: Export terminated unsuccessfully

exp seem not be working with orcle flashback archive.

An export with datapump expdp works. But it does not export the history.

expdp schemas=TKRIM dumpfile=test.dp

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** schemas=TKRIM dumpfile=test.dp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "TKRIM"."DEMO":"E1" 5.421 KB 2 rows
. . exported "TKRIM"."DEMO":"E2" 5.429 KB 2 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u00/app/oracle/admin/DIGATE20/dpdump/test.dp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 09:19:26


the import look more difficutl already

impdp schemas=TKRIM dumpfile=test.dp TABLE_EXISTS_ACTION=replace

..
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39121: Table "TKRIM"."DEMO" can't be replaced, data will be skipped. Failing error is:
ORA-55610: Invalid DDL statement on history-tracked table
ORA-00955: name is already used by an existing object
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 2 error(s) at 09:22:03


disabling flashback archive on the table does the trick.

ALTER TABLE tkrim.demo NO FLASHBACK ARCHIVE;
drop table tkrim.demo;

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TKRIM"."DEMO":"E1" 5.421 KB 2 rows
. . imported "TKRIM"."DEMO":"E2" 5.429 KB 2 rows
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at 09:24:05

but the table stays without flashback archiving enable. a query to the flashback archive still works.


select * from tkrim.demo as of scn 827813;

connect tkrim/tkrim1
ALTER TABLE demo FLASHBACK ARCHIVE fla1;


It can be enabled again - but this behavior leaves room to do easy mistakes.

Oracle Flashback Archive offers simple archiving with simple administration.
During some testing on Oracle 11.2 with Flashback Archive I exprerianced some heavy problems.

The day began with a simple testcase:

create user tkrim identified by tkrim1;
grant connect to tkrim;
grant resource to tkrim;
grant FLASHBACK ARCHIVE administer to tkrim;

select * from demo as of scn 55710824751 ;


After a my UNDO tablespace was full I played a bit with undo_retention and changing the undo tablespace which case following error.


ORA-01555 caused by SQL statement below (SQL ID: 1p36ta7p3d7fu, Query Duration=0 sec, SCN: 0x000c.f7ca7c8f):
select file#, block#, ts#, blocks from seg$ where type# = 3
ORA-01555 caused by SQL statement below (SQL ID: 1p36ta7p3d7fu, Query Duration=1 sec, SCN: 0x000c.f7ca7d8f):
select file#, block#, ts#, blocks from seg$ where type# = 3
ORA-01555 caused by SQL statement below (SQL ID: 1p36ta7p3d7fu, Query Duration=0 sec, SCN: 0x000c.f7ca7ee1):
select file#, block#, ts#, blocks from seg$ where type# = 3


Somehow not really bulletprof, this oracle falshback archive.
Anyway the real trouble started when i tried to drop the user and the flashback archive.


ALTER TABLE demo NO FLASHBACK ARCHIVE;

Worked, but leafes some tables named like TKRIM.SYS_FBA_HIST_* and SYS_FBA_TCRV_*.

At this stage it’s impossible to drop the Flashback archive tablepspace or the user tkrim aswell this tables.

After doing some hacks

SELECT o.object_id, o. owner, o.object_name, t.property
FROM dba_objects o, tab$ t
WHERE o.object_type = 'TABLE'
AND o.object_id = t.obj#
AND t.property = 9126805504;

COMMIT;

I was able to drop the table SYS_FBA_TCRV_*.

But it was still impossible to drop the HIST Table.


SQL > drop table TKRIM.SYS_FBA_HIST_15517640;
drop table TKRIM.SYS_FBA_HIST_15517640
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktadrprc-1], [], [], [], [], [],
[], [], [], [], [], []

At the end I was not able to cleanup the flashback archive or the user.

Until 11.1 the oracle ASM binaries have been on the Oracle Database CD.

With 11.2 the ASM binaries are on the Oracle Grid Infrastructure CD. (Search about 2 houers to find that)
Does anyone know if its stated cleary on the oracle documentation? I could not find it.

The last tree weeks i was hounting a ora-60 deadlock.

The Oracle Supoort claimed it was an application problem.

The Developer stated that it’s impossible an application problem.

Verfrifiend the developers statment proved me to believe him. A row lock was impossibel.

The Trace looked like this:


information may aid in determining the deadlock:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-001c0049-000614c5 92 196 X 85 413 S
TX-00140014-0006def8 85 413 X 92 196 S
session 196: DID 0001-005C-0000000E session 413: DID 0001-0055-00000015
session 413: DID 0001-0055-00000015 session 196: DID 0001-005C-0000000E
Rows waited on:
Session 413: obj - rowid = 000D1ED1 - AADR7RABFAAAxTmAAA
(dictionary objn - 859857, file - 69, block - 201958, slot - 0)
Session 196: obj - rowid = 000D1ED5 - AADR7YABQAAAn43AAA
(dictionary objn - 859861, file - 80, block - 163383, slot - 0)


This deadlock was caused by two update statements.

But if would be a row deadlock the session should wait for an X excluive row lock, not an S sahred lock.

If it would be a classic ITL lock the block would be in the undo tablespace.

On metalink (or the new bad my oracle support) did not help, oracle support could not help.

The nessesary infomation if found here:
http://pjsrandom.wordpress.com/2006/02/28/hunting-deadlocks-part-2/
http://oracledoug.com/serendipity/index.php?/archives/1014-Being-Open-minded.html



Solution:
The table had a initrans of 2 and pct_free of 0
Since I changed that and moved the table the deadlocks are gone.

     SELECT t.OWNER, t.OBJECT_NAME, t.OBJECT_TYPE, t.STATISTIC_NAME, t.VALUE
     FROM v$segment_statistics t
     WHERE t.STATISTIC_NAME = 'ITL waits'
     AND t.VALUE > 0;

igate partitionionirung
=========================

1. Zeitliche partitionierung nach monat
2. partitonierung nach status zum abhängen

Variante 1
============
# list-range partitioning was introduced in 11gR1 as the "Extended Composite Partitioning" new feature.
Open
Zeit
Closed
Zeit

# Tablespace konzept
* managment aufwand
* duplicate / restore
* disk location speed
* compression / reuse space

->
TBSP_OPEN
TBSP_CLOSED_200903
TBSP_CLOSED_200904

Nachteile:
* schlechtere update perfomance wegen row movement

Vorteil
* bessere query perfomance wegen reduziertem i/o
* es können rows mit status closed abgehängt werden

Variante 2
============
Zeit
Open
Closed

Test 1
=========
create user test identified by test;
grant connect to test;
grant resource to test;
connect test/test

create sequence ptest_seq ;
CREATE TABLE ptest
( id             NUMBER
, name           VARCHAR2(60)
, status        VARCHAR2(10)
) PARTITION BY LIST (status)
SUBPARTITION BY RANGE (id)
( PARTITION p_open VALUES ('open')
( SUBPARTITION p_open_small VALUES LESS THAN (8)
, SUBPARTITION p_open_medium VALUES LESS THAN (80)
, SUBPARTITION p_open_high VALUES LESS THAN (MAXVALUE)
)
, PARTITION p_closed VALUES ('closed')
( SUBPARTITION p_closed_small VALUES LESS THAN (5)
, SUBPARTITION p_closed_medium VALUES LESS THAN (50)
, SUBPARTITION p_closed_high VALUES LESS THAN (MAXVALUE)
)
, PARTITION p_default VALUES (DEFAULT)
( SUBPARTITION p_default_small VALUES LESS THAN (10)
, SUBPARTITION p_default_medium VALUES LESS THAN (100)
, SUBPARTITION p_default_high VALUES LESS THAN (MAXVALUE)
)
) ENABLE ROW MOVEMENT;

begin for x in 1..20 loop
insert into ptest (id,status) values (ptest_seq.nextval,'open');
insert into ptest (id,status) values (ptest_seq.nextval,'closed');
end loop;
end;
/
commit;

select count(1) from ptest partition (p_open);
select count(1) from ptest partition (p_closed);

update ptest set status='open' where mod(id,2)=0;
commit;

select count(1) from ptest partition (p_open);
select count(1) from ptest partition (p_closed);

update ptest set status='closed' where mod(id,2)=0;
commit;

-- add subpartition
alter table ptest SPLIT SUBPARTITION p_closed_high AT (120) into (subpartition p_closed_medium2, subpartition p_closed_high);
select count(1) from ptest subpartition (p_closed_medium2);

-- drop subpartition
alter table ptest drop SUBPARTITION p_closed_small;

-- compress partition
alter table ptest move SUBPARTITION p_closed_medium compress;

If you a import a dump on a database without partitioning enabled  you get the error

ORA-00439: feature not enabled: Partitioning

To enable Partitioning shutdown everything in your home and do this.

$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk part_on
$ make -f ins_rdbms.mk ioracle

Check it with
 select * from v$option where parameter = 'Partitioning';

1. ORA$AUTOTASK_CLEAN
The job is created by the 11g script catmwin.sql which mentions that this job is an autotask repository data ageing job. It runs the procedure ora$age_autotask_data.

2. HM_CREATE_OFFLINE_DICTIONARY
The job is created by the 11g script catmwin.sql which mentions that this is a job for creation of offline dictionary for Database Repair Advisor.

The system job SYS.HM_CREATE_OFFLINE_DICTIONARY executes the dbms_hm.create_offline_dictionary package which creates a LogMiner offline dictionary in the ADR for DRA name translation service. The job for generating the logminer dictionary is scheduled during the maintenance window. This job can be disabled. ‘

3. DRA_REEVALUATE_OPEN_FAILURES
The job is created by the 11g script catmwin.sql which mentions that this is a job for reevaluate open failures for Database Repair Advisor.
The job executes the procedure dbms_ir.reevaluateopenfailures.

4. MGMT_CONFIG_JOB - comes with the OCM(Oracle Configuration Manager) installation - This is a configuration collection job.

The job is created by the script ocmjb10.sql by running procedure ‘ORACLE_OCM.MGMT_CONFIG.collect_config’.

5. MGMT_STATS_CONFIG_JOB
This is an OCM Statistics collection job created in ocmjb10.sql by running ‘ORACLE_OCM.MGMT_CONFIG.collect_stats’.

6. BSLN_MAINTAIN_STATS_JOB
This job replaces the old GATHER_STATS job. It is a compute statistics job. This job runs the  BSLN_MAINTAIN_STATS_PROG program on the BSLN_MAINTAIN_STATS_SCHED schedule. The program BSLN_MAINTAIN_STATS_PROG will keep the default baseline’s statistics up-to-date.

7. XMLDB_NFS_CLEANUP_JOB
The job is created in xdbu102.sql and runs the procedure dbms_xdbutil_int.cleanup_expired_nfsclients.

During my work as Oracle DBA with the focus on performance tuning I have to check often stuff on the system level.

Here I like to share my command list for checking importent stuff on solaris 10.

cpu count

psrinfo -v

cpu info

 mpstat  #report per-processor statistics

Memory

/usr/sbin/prtconf | grep Mem
Memory size: 16384 Megabytes

64 or 32 bit

isainfo -v # available
uname -a   # running
isainfo -kv #active

show system error codes

man -s2 intro

Edit / Show process priority

priocntl

Installed Software

 pkginfo | grep VERITAS

ACL (http://www.softpanorama.org/Solaris/ACL/index.shtml)

getfacl foo
setfacl -m user:jeff:rw- foo

Trace process

truss -p <pid>

Summary about system calls

truss -c -p <pid>

Follow forked childs

truss -f <cmd>

Prozess wait statistics

truss -c -p <pid>

DTrace dynamic tracing compiler and tracing utility

    dtrace

Examples dtrace

# New processes with arguments,
dtrace -n 'proc:::exec-success { trace(curpsinfo->pr_psargs); }'

# Files opened by process,
dtrace -n 'syscall::open*:entry { printf("%s %s",execname,copyinstr(arg0)); }'

# Syscall count by program,
dtrace -n 'syscall:::entry { @num[execname] = count(); }'

# Syscall count by syscall,
dtrace -n 'syscall:::entry { @num[probefunc] = count(); }'

# Syscall count by process,
dtrace -n 'syscall:::entry { @num[pid,execname] = count(); }'

# Disk size by process,
dtrace -n 'io:::start { printf("%d %s %d",pid,execname,args[0]->b_bcount); }'   

# Pages paged in by process,
dtrace -n 'vminfo:::pgpgin { @pg[execname] = sum(arg0); }'

Dtrace References

• http://www.sun.com/emrkt/expertexchange/DTracevLowRes.pdf
• http://docs.sun.com/app/docs/doc/817-6223

report user-level lock statistics

plockstat

report kernel lock and profiling statistics

lockstat

Print the /proc tracing flags, the pending and held signals, and other /proc status information for each lwp in each process.

pflags

process tree

ptree

sysdef - output system definition

sysdef -i

ex. max processe kernel parameter

user@abc:/tmp $ sysdef -i |grep proc
fs/procfs
fs/sparcv9/procfs
  30000        maximum number of processes (v.v_proc)
  29995        maximum processes per user id (v.v_maxup)
  100  max undo entries per process (SEMUME)
  250  max attached shm segments per process (SHMSEG)

% prtconf | grep Mem
Memory size: 32760 Megabytes

% id -p
uid=59008(oracle) gid=10001(dba) projid=3(default)

% prctl -n project.max-shm-memory -i project 3
project: 3: default
NAME    PRIVILEGE       VALUE    FLAG   ACTION                       RECIPIENT
project.max-shm-memory
        privileged      7.84GB      -   deny                                 -
        system          16.0EB    max   deny                                 -

 prctl -n project.max-shm-memory -i project oracle

solaris 10

kernel parameter check

Take a userprocess id (ps -ef |grep ..)

prctl  <pid>

all parameter

prctl -i project oracle

Management Commands

Commands for managing project attributes include the following:

   * projects: Displays project memberships for users, lists projects from the project database, prings information on given projects.
   * newtask: Executes the shell or command in a new task in the current project.
   * projadd: Adds a new entry to the /etc/project entry.
   * projmod: Modifies information for a project in /etc/project.
   * projdel: Deletes a project from /etc/project.
   * rctladm: Displays/modifies global state of active resource controls, sets logging or actions.
   * prctl: Displays/modifies local resource controls.
   * ipcs: Identifies which IPC objects are being used in a project.
   * rcapadm: Manages rcapd memory-capping daemon.
   * prstat -J: Displays resource consumption on a per-project basis
   * priocntl -i project-name: Sets/displays scheduling parameters of the project.
   * poolbind -i project-name: Assigns a project to a resource pool.

process info

Memory by process (rss in kb)

ps -e -o pid,vsz,rss,comm |grep <cmdname>

Sumarize memory of a set of processes

ps -e -o pid,vsz,rss,comm |grep java|awk '{sum = sum + $2} END {print sum}'

ps -ly

GUI - top

sdtprocess

The bad situation:

• You deleted a datafile containing a partition
• You are running 10.2.0.4 where you can not drop unused columns on compressed tables due a bug
• You can not drop the tablespace since it is containing the partition.
• Exchange partition does not work cause of the unused column

The good solution:


# get the creeate table sql (skip the partitions part)
set long 3000
SELECT dbms_metadata.get_ddl('TABLE','T_XXXXXX','SCHAMAX') from dual;

# drop your offline tablespace (with the lost datafiles)
drop tablespace T_XXXX_2009KW08 including contents and datafiles;


Conclusion:
Oracle is great -> but you have to be great as well!