All apps dba Blog is the blog contributed by Doyensys Employees, With the view to share the knowledge out of their experience.
Doyensys Is a Fast Growing Oracle Technology Based Solutions Company Located in the US And Offshore Delivery Centers in India. With rich experience In Oracle E-Business, Oracle Database, Oracle Application Express (APEX), Oracle J Developer, Oracle OBIEE and E-Business Suite
Monitoring Parallism while doing export and import using datapump.
, x.owner_name,z.sql_text, p.message
, p.totalwork, p.sofar
, round((p.sofar/p.totalwork)*100,2) done
from dba_datapump_jobs b
left join dba_datapump_sessions x on (x.job_name = b.job_name)
left join v$session y on (y.saddr = x.saddr)
left join v$sql z on (y.sql_id = z.sql_id)
left join v$session_longops p ON (p.sql_id = y.sql_id)
WHERE y.module='Data Pump Worker'
AND p.time_remaining > 0;
set lines 220
set echo off
set feedb off
set termout off
set heading off
SET VERIFY off
set trimspool on
set long 32766
set longchunksize 200
col l1 format a90
col l2 format a90 newline
col l3 format a100 newline
col l6 format a200 newline word_wrapped
col l9 format a90 newline
I recently came across a problem the login page was throwing an error - 404 /OA_HTML/AppsLogin was not found on this server. All services of Database and Listener working fine and get connected. All Application services started successfully without any error/warning on status 0, multiple time restart the services of application and database and also restart the whole machine but still facing the same problem.
After some research work I have found the following solution:
The Problem occurs because the wsrp_service.wsdl was owned by root but the services were being started by applmgr.
Ensure that the permission of the file $ORA_CONFIG_HOME/10.1.3/j2ee/oacore/application-deployments/oacore/html/server-wsdl/wsrp_service.wsdl
Shows that it is owned by root user. Somebody started the services of Apps as root user. Changed the ownership of the file to Apps Owner. Restart the service, everything working fine now.
Casue: This issue may occur after an OS crash, reboot or services restart. If file $COMMON_TOP/cacheLock file exists before oacore service is started, the above thread will be locked and the oacore service will hang until timed out.
Solution: Remove $COMMON_TOP/cacheLock file which is a 0 bytes file created at startup time.
1)stop application services make sure no process are running. 2)go to common top
cd $COMMON_TOP $ mv cacheLock cacheLock_bkp
3)started the application services and when I checked the status,it was started successfully and no more issues.Below are the details.
$ adopmnctl.sh status
You are running adopmnctl.sh version 120.6.12010000.5
On occasion, it may be necessary to kill an Oracle session that is associated with a running job. The first step in the process is to identify the session to be killed.
Running jobs that were scheduled using the dbms_job package can be identified using the dba_jobs_running view.
The script listed below uses this view along with the v$session and v$process views to gather all information needed about the running jobs.
set feedback off
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
set feedback on
jr.sid = s.sid
s.paddr = p.addr
Running jobs that were scheduled using the dbms_scheduler package can be identified using the dba_scheduler_running_jobs view.
The jobs_running_10g.sql script listed below uses this view along with the v$session and v$process views to gather all information needed about the running jobs.
rj.session_id = s.sid
s.paddr = p.addr
Regardless of the job scheduling mechanism, the important thing to note is that there are sid, serial#, and spid values associated with the running jobs. The sid and serial# values are necessary in order to kill the session, while the spid value is necessary if the associated operating system process or thread must be killed directly.
To kill the session from within Oracle, the sid and serial# values of the relevant session can then be substituted into the following statement:
alter system kill session 'sid,serial#';
With reference to the job listed above by the jobs_running_10g.sql script, the statement would look like this.
RDBMS and listener log (xml) from SQL*Plus with V$DIAG_ALERT_EXt view
With the V$DIAG_ALERT_EXT it is possible to read the logs of all the databases and listeners from the ADR location Great for monitoring. Now only one connection to a database is needed to see all the database alert files and listener logs registered inside the ADR structure.Yes Multiple databases.
There is NOGV$DIAG_ALERT_EXT.
ADR is node, database depended and not global. Still we can use a shared ADR on a shared file system on Oracle CLusterware RAC and then is everthing from one place.
The parameter DIAGNOSTIC_DEST represents the root for the Automatic Diagnostic Repository (ADR), which includes the alertfile states. If this parameter is not set and the ORACLE_BASE is than it will use this information for the DIAGNOSTIC_DEST. If ORACLE_BASE is not set then the ADR root is in log Oracle release 11.2 has introduced new v$ diagnostic (diag) views.
select object_name from dba_objects where object_name like 'V$DIAG%' order by 1;
Exadata Storage Server layer includes some flash storage as a hardware
component, which has been implemented as a set of PCI flash cards. The main
benefit of that is of course faster access than standard, disk-based access. In
some cases you can order Exadata to KEEP whole table in Smart Flash Cache which
speeds up database layer access with Full Table Scans. On the other hand in
Write-Back mode you can empower DBWR or LGWR performance by putting written
data on flash cache first, and afterwards sync it with regular hard drives,
which of course is transparent to database engine.
2. Storage Indexes
absolutely unique to Exadata. Storage Indexes are an intelligent storage
implementation. Generally speaking classic database index by it definition is
created to efficiently provide a location of a certain data key. To be honest
Storage Indexes in Exadata are focused on eliminating areas on storage as
possible place where data might exist. Online data maps which are completely
transparent for database layer are stored on the Flash Cache of Exadata Storage
Servers. To make the story short, when Exadata Storage Server scans through the
Storage Index and identifies the regions where predicate value falls within the
MIN/MAX for the region, only for the identified regions physical I/O occurs.
And as a consequence, even Full Table Scan has been planned by CBO on Database
Server layer, limited I/O operations use to be proceed.
3. Smart Scans and Cell Offloading
use to be called as secret sauce of Oracle Exadata. The main concept of
Offloading is to move processing from DB Nodes (Database Servers) to the
intelligent storage layer. What is even more important Offloading means the
reduction in the volume of data that returns to database server, which is one of
the major bottlenecks in terabytes or even more bigger databases. To eliminate
the time spent on transferring completely unnecessary data between storage and
the database tier is the main issue that Oracle Exadata has been built to
solve. Keep in mind that Offloading and Smart Scan terms could be used somewhat
4. Hybrid Columnar Compression
known as HCC, Hybrid Columnar Compression is one of the key features of Oracle
Exadata, and it is only available on this engineered system. HCC format of
compression will be used only when data arrives with direct path loads. There
are four levels of compression QUERY LOW (LZO, 4x), QUERY HIGH (ZLIB, 6x),
ARCHIVE LOW (ZLIB, 7x) and ARCHIVE HIGH (Bzip2, 12x). Keep in mind that HCC is
not a good option for OLTP systems. In case of HCC mechanics, HCC store data in
nontraditional format. Even data resides in Oracle blocks, with block header
for every block, in HCC data storage has been organised in logical structures
called compression units (CUs). Each CU consists of multiple Oracle blocks.
Resource Manager (IORM) is a Oracle Exadata features which enriches Oracle
Resource Manager from Database layer. IORM only actively manages I/O requests
when needed and when Storage Server is not fully utilised, it provide data
immediately. But when a disk is under heavy utilisation, Storage Server
software redirects the I/O requests to the appropriate IORM queue and schedules
I/O from there according to the policies defined in your IORM plans. Generally IORM
policies open the way to prioritise databases on intelligent storage layer,
which enable workload optimisation.
Very important parameters to use major features of Exadata.
The first is CELL_OFFLOAD_PROCESSING, which relates to Smart Scans (cell scans). The default value is TRUE, which means that Smart Scans are turned ON for Exadata (if you are using it). You can set this value to FALSE to turn off Smart Scans and check other features or compare speeds with and without this feature.
The second parameter is undocumented and should only be used with the consent of Oracle Support and also only for testing purposes. The second is _KCFIS_STORAGEIDX_DISABLED, which is used to disable storage indexes. This tells Oracle not to use storage index optimization on storage cells if set to TRUE. The default is FALSE.
Another undocumented parameter (again check with Oracle Support) is also related to how the Smart Scan works and uses Bloom Filters. The parameter _BLOOM_FILTER_ENABLED is set to TRUE by default. With Exadata, bloom filters are used for join filtering with Smart Scans. _BLOOM_PRUNING_ENABLED also has a default of TRUE. Set these parameters to FALSE to disable them.