Andrew Fraser DBA

This alert in Oracle Enterprise Manager Grid Control:

Service seeddata.regress.rdbms.dev.us.oracle.com is Down

Was brute-force fixed for me by running this on the target database:

SQL> exec dbms_service.delete_service('seeddata.regress.rdbms.dev.us.oracle.com')

In Oracle Enterprise Manager Grid Control, the Agents, Hosts, Listeners were all displaying correctly with status of ‘up’.

But the databases reported status of “Metric Collection Error”.

When I tried to configure a database, the configure database page displayed either one of these errors at top of page:

oracle.sysman.emSDK.emd.comm.CommException: java.io.IOException: Cannot establish proxy connection: 503 Service Unavailable

oracle.sysman.emSDK.emd.comm.CommException: java.net.NoRouteToHostException: No route to host

Problem was agent server firewall settings blocking ports.

Fix for me was:

1) on agent server as oracle

cd /software/oracle/product/agent10g/bin
./emctl status agent

Look for “Agent URL” in the output of that command, in my case that was

https://myagentserver.mydomain:3872/emd/main

2) On Grid Control server as oracle, paste that line into a wget command at the command prompt:

wget --no-check-certificate https://myagentserver.mydomain:3872/emd/main

That failed with: “failed: No route to host”, fix was as below:

3) On agent server again – as root

Make backup copy before changing settings:

cd /etc/sysconfig
cp -p iptables iptables.210510

vi /etc/sysconfig/iptables

Add a new line for port 3872 by copying one of the existing lines, e.g.:

-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 3872 -j ACCEPT

Restart with:

/etc/rc3.d/S08iptables restart

This worked for me with Oracle Enterprise Manager Grid Control and Agent both version 10.2.0.5 and both on 32 bit linux.

Comments are used much less often in spfile than they were in old init.ora’s. Syntax for them is like:

SQL> alter system set pga_aggregate_target=1g comment='Andrew Fraser 13-May-2010 was 390m'  scope=spfile sid='*' ;

Result cache functions are a good new feature in Oracle 11g, but they only work with normal functions. A function that returns multiple outputs using OUT parameters cannot be made a result cache function, at least not in 11gR1.

Attempting to do so fails with this error: “PLS-00999: implementation restriction (may be temporary) RESULT_CACHE is disallowed on subprograms with OUT or IN OUT parameters“.

It would be possible to work around this limitation by bundling up the output into a single large delimiter separated string, but, for me, performance issues would have to be significant before resorting to that. Good sample code for doing it that way is here.

More details at http://blog.mclaughlinsoftware.com/tag/result_cache/.

Here objects are moved from tablespace users to tablespace users1:

set pages 9999 lines 132
spool m2.sql
select 'alter table '||owner||'.'||table_name||' move lob('||column_name||') store as ( tablespace users1);'
from dba_lobs where tablespace_name = 'USERS' order by 1
/
select 'alter table '||owner||'.'||segment_name||' move tablespace users1;'
from dba_segments where tablespace_name = 'USERS' and segment_type = 'TABLE' order by 1
/
select 'alter index '||owner||'.'||index_name||' rebuild tablespace users1;'
from dba_indexes where tablespace_name = 'USERS' order by 1
/
spool off
ed m2

cat << EOF | /usr/sbin/sendmail -t
to:myemail@mysite.com
from:"no reply" (Automated Processor)
subject:My subject
My email body.
EOF

Find out what directories are using up disk space in unix with this command:

du -sk * | sort -n

I often put it into a function so I can call it quickly:

g () {
  du -sk * | sort -n
}

df -h (df -k on old machines) shows the filesystem disk utilisation.

See the SQL create definition of a materialized view by running this SQL:

set long 200000 pages 0 lines 131 doc off
column txt format a121 word_wrapped
spool recreate_sql
select dbms_metadata.get_ddl('MATERIALIZED_VIEW','my_mv_name','my_owner') txt from dual;
spool off
set pages 9999

Look especially for the refresh option, e.g.:

REFRESH FORCE ON DEMAND NEXT null

There are three normally used refresh options:

• Fast = use materialized view logs on source
• Complete = ignore materialized view logs, instead truncate the materialized view and repopulate it from the base tables of source.
• Force = exactly the same as Fast – except when there are no materialized view logs on source, in which case it does a complete refresh.

Examples of syntax to export/import dbms_stats:

exec dbms_stats.create_stat_table ( ownname => user , stattab => 'temp_stats' ) ;
exec dbms_stats.export_table_stats ( ownname => user , stattab => 'temp_stats', tabname => 'mytable', statid => 'Taken_7Apr2010') ;
exec dbms_stats.import_table_stats ( ownname => user , stattab => 'temp_stats', tabname => 'adm_fact' )

Similarly for schema:

exec DBMS_STATS.EXPORT_SCHEMA_STATS ( ownname => 'myowner' , stattab => 'temp_stats', statown => user )

A bug in 10g prevents spaces being passed into the “statid” variable, hence the underscores in the above example.

cp -pR logs logs`date +$d%m%y%T`

Creates a timestamped copy directory named like “logs041015:44:42″.