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.
Purge and regather all optimizer stats with:
exec dbms_stats.delete_database_stats
exec dbms_stats.delete_system_stats
exec dbms_scheduler.run_job('GATHER_STATS_JOB')
That works provided statistics level has not been changed to none
show parameter statistics_level
You can gather more detailed system stats with:
exec dbms_stats.gather_system_stats('START')
exec dbms_stats.gather_system_stats('STOP')
You can see the system stats with:
set pages 9999 lines 112 col pval2 form a20 select * from sys.aux_stats$;
You can check automatic stats gathering job is scheduled with:
select state, last_start_date from dba_scheduler_jobs where job_name = 'GATHER_STATS_JOB';
This has changed for 11g.
To check if it is scheduled in 11g:
select status, max_duration_last_7_days from dba_autotask_client where client_name = 'auto optimizer stats collection' ;
And to run it in 11g:
exec dbms_stats.gather_database_stats_job_proc
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:
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.