No great genius has ever existed without some touch of madness.

Monday, 18 January 2016

Verify APEX installation

The primary purpose of this script is to verify the validity of an APEX installation after an installation has occurred or before an upgrade.  However, since it checks a number of APEX installation prerequisites, it may be useful to run prior to an APEX installation.

Doc ID : 1254932.1
For APEX install : http://www.oracledbasagar.blogspot.com/2012/05/apex-41-installation.html

APPLIES TO :
Oracle Application Express (formerly HTML DB) - Version 2.0.0.0.0 and later
Information in this document applies to any platform.
Oracle Application Express (APEX) versions 2.0 and above.


-- Oracle APEX Diagnostic Agent for APEX Installs
-- =====================================================

-- USAGE:
-- ======
-- Login to SQL*PLUS as the SYSTEM user and then execute this SQL script
-- as follows:
--  @<path>/apex_verify.sql
-- By default output is written to apex_verify_out.html in the current directory 

REM The formatting method used in this note is based on the formatting methods used in the following notes:
REM Oracle9iAS Portal Diagnostics Agent (PDA) (Doc ID 169490.1)
REM Capture Single Sign-On Configuration Tables to HTML Formatted File (Doc ID 244112.1)


clear buffer;

set serveroutput on
set arraysize 1
set trims on
set linesize 240
set pagesize 0
set sqlprefix off
set verify off
set feedback off
set heading off
set timing off
set define on
set escape off

--prompt V 3.3c - Added queries for FLOWS_FILES,  modified synonym query, changed colors,
--prompt
--prompt Enter output filename.  If file exists will be overwritten.
spool apex_verify_out.html
exec dbms_output.put_line('<!DOCTYPE html>');
exec dbms_output.put_line('<html>');
--select '<head><title>APEX Verification Script</title></head><body bgcolor="#ffffff">' from dual;
--select '<head><title>APEX Verification Script</title></head><body bgcolor="#fffff0">' from dual;
select '<head><title>APEX Verification Script</title></head><body bgcolor="#fdfdfd">' from dual;
--select '<head><title>APEX Verification Script</title></head><body bgcolor="#fafafa">' from dual;
--select '<head><title>APEX Verification Script</title></head><body bgcolor="#f6f6f6">' from dual;
--select '<head><title>APEX Verification Script</title></head><body bgcolor="#f2f2f2">' from dual;
--select '<head><title>APEX Verification Script</title></head><body bgcolor="#e6e6e6">' from dual;

select '<body><div align=left><b><font face="Arial,Helvetica"><font color="#ff0000">' ||
       '<font size=-2>' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') || ' Ver 3.3c ' ||
       '</font></font></font></b></div></body>' from dual;

--START Active version of APEX in the DB
define APEX = 'APEX IS NOT INSTALLED'
column APEX_VER new_val APEX NOPRINT

--use the following to get the apex schema for the version of apex registered in the dba_registry.
SELECT SCHEMA APEX_VER
FROM dba_registry
WHERE comp_id = 'APEX'; 
--WHERE (comp_id = 'APEX' or comp_id like 'HTML%');


define GET_VER ='APEX_RELEASE';
define VERSION = '&APEX..&GET_VER';

--Above notionally resolves to APEX_040200.APEX_RELEASE
--END determine Active version of APEX in the DB


--START Determine tablespace used by APEX schema
define APEX_TABLESPACE = 'NO TABLESPACE'
column APEX_TAB new_val APEX_TABLESPACE NOPRINT
select default_tablespace APEX_TAB from dba_users where username='&APEX';
--END Determine tablespace used by APEX schema

--START Determine IF tablespace used by APEX schema is autoextend or not
define APEX_TABLESPACE_AUTOEXTEND = 'NO'
column APEX_TAB_AE new_val APEX_TABLESPACE_AUTOEXTEND NOPRINT
select distinct(autoextensible)APEX_TAB_AE from dba_data_files where tablespace_name = '&APEX_TABLESPACE';
--END Determine IF tablespace used by APEX schema is autoextend or not

--START Determine tablespace used by FLOWS_FILES
define FLOWS_FILES_TABLESPACE = 'NO TABLESPACE'
column FLOWS_FILES_TAB new_val FLOWS_FILES_TABLESPACE NOPRINT
select default_tablespace FLOWS_FILES_TAB from dba_users where username='FLOWS_FILES';
--END Determine tablespace used by FLOWS_FILES

--START Determine IF tablespace used by FLOWS_FILES schema is autoextend or not
define FLOWS_FILES_TABLESPACE_AUTO = 'NO'
column FLOWS_FILES_TAB_AE new_val FLOWS_FILES_TABLESPACE_AUTO NOPRINT
select distinct(autoextensible)FLOWS_FILES_TAB_AE from dba_data_files where tablespace_name = '&FLOWS_FILES_TABLESPACE';
--END Determine IF tablespace used by FLOWS_FILES schema is autoetxend or not

--START Determine temporary tablespace used by APEX Installation
define TEMP_APEX = 'NO TABLESPACE'
column APEX_TEMP new_val TEMP_APEX NOPRINT
select temporary_tablespace APEX_TEMP from dba_users where username='&APEX';
--END Determine temporary tablespace used by APEX Installation

--START  DATABASE VERSION
-- select banner from v$version;
select '<h5><font face="VERDANA"><font color="#000000">APEX Database Information' ||
       '<font size=-2></font></font></font></h5>' FROM dual;
select '<TABLE BORDER  CELLPADDING=2>' FROM dual;
select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>' || 'DB Information </FONT></B></TH>' FROM dual;
select '<TR><TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || banner ||
       '</FONT></TD></TR>' from v$version;
select '</TABLE>' FROM dual;
--COMMENTS
--select '<body><i><font face="Arial,Helvetica"><font color="#FF0000"><font size=3> For APEX 3.2 and below, DB must be 9.2.0.3 or above.<BR>
select '<body><font face="Arial,Helvetica"><font color="#FF0000"><font size=3> For APEX 3.2 and below, DB must be 9.2.0.3 or above.<BR>
For APEX 4.0, DB must be 10.2.0.3 or above or 10g Express</font></font></font></body>'
 from dual;

--END DATABASE VERSION

--start Get exact version of APEX
select '<h5><font face="VERDANA"><font color="#000000">APEX ' ||
       'Version Registered in DBA Registry <font size=-2></font></font></font></h5>' FROM dual;
select '<TABLE BORDER  CELLPADDING=2>' FROM dual;
select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>' ||
       'Version </FONT></B></TH>' FROM dual;
select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>' ||
       'API Compatibility</FONT></B></TH>' FROM dual;
select '<TR><TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2>' || version_no ||
       '</FONT></TD>', '<TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2>' ||
       api_compatibility || '</FONT></TD></TR>' from &VERSION; 
select '</TABLE>' FROM dual;
--end --Get exact version of APEX

--Begin Get Number of Valids in the APEX Schema
select '<h5><font face="VERDANA"><font color="#000000"> Number of APEX Valids/Invalids in the &APEX and FLOWS_FILES schemas <font size=-2></font></font></font></h5>'
   FROM dual;
select '<TABLE BORDER  CELLPADDING=2>' FROM dual;
select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>Total APEX Valids </FONT></B></TH>' FROM dual;
select '<TR><TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || count(1) || '</FONT></TD></TR>'
from dba_objects
where owner = upper('&APEX') and status='VALID';
select '</TABLE>' FROM dual;

--End Get Number of Valids in the APEX Schema

--Begin Get number of Invalids in the APEX Schema
select '<TABLE BORDER  CELLPADDING=2>' FROM dual;
select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>Total APEX Invalids </FONT></B></TH>' FROM dual;
select '<TR><TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || count(1) || '</FONT></TD></TR>'
from dba_objects
where owner  = upper('&APEX') and status='INVALID';
select '</TABLE>' FROM dual;
--End Get Number of invalids in the APEX Schema

--Begin Get Number of Invalids in the flows_files schema

select '<TABLE BORDER  CELLPADDING=2>' FROM dual;
select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>Total FLOWS_FILES Invalids </FONT></B></TH>' FROM dual;
select '<TR><TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || count(1) || '</FONT></TD></TR>'  from dba_objects
where owner  = 'FLOWS_FILES' and status='INVALID';
select '</TABLE>' FROM dual;

--End Get Number of Invalids in the flows_files schema


--BEGIN Get information about Valids/Invalids in the APEX Schema
select '<h5><font face="VERDANA"><font color="#000000"> List of &APEX and FLOWS_FILES Invalid Objects <font size=-2></font></font></font></h5>'
   FROM dual;
select '<TABLE BORDER  CELLPADDING=2>' FROM dual;

select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>' ||
       'Object Name</FONT></B></TH>' FROM dual;
select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>' ||
       'Object Type</FONT></B></TH>' FROM dual;
select '<TR><TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || Object_name || '</FONT></TD>',
           '<TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || object_type || '</FONT></TD></TR>'
from dba_objects where owner in (UPPER('&APEX'),'FLOWS_FILES')  and status = 'INVALID' order by object_type;
select '</TABLE>' FROM dual;
--End Get information about Valids/Invalids in the APEX Schema

--Start Get images directory

exec dbms_output.put_line( '<h5><font face="VERDANA"><font color="#000000"> Virtual Image Directory (default and recommended -> /i/)<font size=-2></font></font></font></h5>' );
exec dbms_output.put_line( '<TABLE BORDER  CELLPADDING=2>' );
exec dbms_output.put_line( '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2> Virtual Directory </FONT></B></TH>' );

exec dbms_output.put_line( '<TR><TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> '||&APEX..wwv_flow_image_prefix.g_image_prefix  ||  '</FONT></TD></TR>');
--select '<TR><TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || flow_image_prefix ||  '</FONT></TD></TR>' from &APEX..wwv_flows where security_group_id = 10 and rownum=1;
exec dbms_output.put_line( '</TABLE>' );
--End Get images directory

--START APEX Related Schemas
select '<h5><font face="VERDANA"><font color="#000000">APEX Related Schemas ' ||
       ' <font size=-2></font></font></font></h5>' FROM dual;
select '<TABLE BORDER  CELLPADDING=2>' FROM dual;
select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>' || 'APEX Related Schemas </B></FONT></TH>' FROM dual;

select '<TR><TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || username ||'</FONT></TD></TR>' 
from dba_users
where (username like 'APEX%' or username = 'FLOWS_FILES') and
username not in (select  username from dba_users where (username like 'APEX\_0%' escape '\' or username like 'FLOWS\_0%' escape '\') and (username <> '&APEX'))order by username asc;
select '</TABLE>' FROM dual;
-- END APEX Related Schemas


--START Prior APEX Versions which May be Cleaned Up
exec dbms_output.put_line('<h5><font face="VERDANA"><font color="#000000">Prior APEX Versions which May be Cleaned Up<font size=-2></font></font></font></h5>');
exec dbms_output.put_line('<TABLE BORDER  CELLPADDING=2>');
exec dbms_output.put_line('<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>Consider Removing All Listed </B></FONT></TH>');

select '<TR><TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || username ||'</FONT></TD></TR>' 
       from dba_users where (username like 'APEX\_0%' escape '\' or username like 'FLOWS\_0%' escape '\') and (username <> '&APEX') order by username asc;
exec dbms_output.put_line('</TABLE>');
--Comment on APEX Version Cleanup
select '<body><font face="Arial,Helvetica"><font color="#FF0000">' ||
       '<font size=3>If your current APEX version is fully functional and backed up, consider removing earlier versions.<BR>
        See "How to Uninstall Oracle HTML DB / Application Express from the Database (Doc ID 558340.1)"</font></font></font></body>'
   from dual;
--END Prior APEX Versions which May be Cleaned Up




--START PL/SQL TOOLKIT VERSION
-- select owa_util.get_version from dual;
select '<h5><font face="VERDANA"><font color="#000000">PL/SQL Toolkit Version <font size=-2></font></font></font></h5>' FROM dual;
select '<TABLE BORDER  CELLPADDING=2>' FROM dual;
select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>' || 'Version </B></FONT></TH>' FROM dual;
select '<TR><TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || owa_util.get_version  ||  '</FONT></TD></TR>' from dual;
select '</TABLE>' FROM dual;
-- COMMENTS
select '<body><font face="Arial,Helvetica"><font color="#FF0000">' ||
       '<font size=3>Check the PL/SQL Web Toolkit version. If less than 10.1.2.0.6 then ' ||
       'upgrade (discuss with Oracle Support before upgrading)</font></font>' ||
       '</font></body>' from dual;
--END PL/SQL TOOLKIT VERSION

--start DUPLICATE OWA PACKAGES 
-- SELECT OWNER, OBJECT_TYPE FROM DBA_OBJECTS WHERE OBJECT_NAME = 'OWA';
select '<h5><font face="VERDANA"><font color="#000000">Duplicate OWA ' ||
       'packages <font size=-2></font></font></font></h5>' FROM dual;
select '<TABLE BORDER  CELLPADDING=2>' FROM dual;
select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>' || 'Owner</B></FONT></TH>' FROM dual;
select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>' || 'Object Type</FONT></B></TH>' FROM dual;
select '<TR><TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || owner ||'</FONT></TD>',
           '<TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || object_type || '</FONT></TD></TR>'
          FROM DBA_OBJECTS WHERE OBJECT_NAME = 'OWA';
select '</TABLE>' FROM dual;
-- COMMENTS

select '<body><font face="Arial,Helvetica"><font color="#FF0000">' ||
       '<font size=3>Make sure you do not have duplicate copies of OWA packages. You should see the output as below:<BR><BR>
        SYS..............PACKAGE<BR>
        SYS..............PACKAGE BODY<BR>
        PUBLIC........SYNONYM</font></font></font></body>'
   from dual;
--end DUPLICATE OWA PACKAGES 

--START Shared Pool Size
select '<h5><font face="VERDANA"><font color="#000000">Shared Pool Size - Please see the APEX Installation Guide for your APEX/DB version for required settings ' ||
       ' <font size=-2></font></font></font></h5>' FROM dual;
select '<TABLE BORDER  CELLPADDING=2>' FROM dual;

select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>Shared Pool Size (MB)</FONT></B></TH>'
 FROM dual;
select '<TR><TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' ||value/1024/1024|| '</FONT></TD></TR>'
from v$parameter where name = 'shared_pool_size';
select '</TABLE>' FROM dual;

--END Shared Pool Size

--START NLS Characterset Values

select '<h5><font face="VERDANA"><font color="#000000">NLS CHARACTER SET Information<font size=-2></font></font></font></h5>'
 FROM dual;
select '<TABLE BORDER  CELLPADDING=2>' FROM dual;

select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>Parameter</FONT></B></TH>' FROM dual;
select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>Parameter Value</FONT></B></TH>' FROM dual;


select '<TR><TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || parameter ||'</FONT></TD>',
           '<TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || value     ||'</FONT></TD></TR>'
   from NLS_DATABASE_PARAMETERS where parameter in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');

select '</TABLE>' FROM dual;

--END - NLS Characterset Values

--START Free Space in System 


select '<h5><font face="VERDANA"><font color="#000000">Free Space in System Tablespace ' ||
       ' <font size=-2></font></font></font></h5>' FROM dual;
select '<TABLE BORDER  CELLPADDING=2>' FROM dual;

select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>MB Free in System </FONT></B></TH>'
 FROM dual;
select '<TR><TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' ||sum(bytes)/1024/1024|| '</FONT></TD></TR>'
from dba_free_space
where tablespace_name ='SYSTEM';
select '</TABLE>' FROM dual;

--END  Free Space in System Tablespace


--START Free Space in APEX Tablespace

select '<h5><font face="VERDANA"><font color="#000000">Free Space in &APEX_TABLESPACE Tablespace (AUTOEXTEND=&APEX_TABLESPACE_AUTOEXTEND) used by &APEX<font size=-2></font></font></font></h5>'
FROM dual;
select '<TABLE BORDER  CELLPADDING=2>' FROM dual;

select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>Free Space in MB</FONT></B></TH>'
 FROM dual;
select '<TR><TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' ||sum(bytes)/1024/1024|| '</FONT></TD></TR>'
from dba_free_space
where tablespace_name ='&APEX_TABLESPACE';
select '</TABLE>' FROM dual;

--END   Free Space in APEX Tablespace

--START Free Space in FLOWS_FILES Tablespace

select '<h5><font face="VERDANA"><font color="#000000">Free Space in &FLOWS_FILES_TABLESPACE (AUTOEXTEND=&FLOWS_FILES_TABLESPACE_AUTO) Tablespace used by FLOWS_FILES<font size=-2></font></font></font></h5>'
FROM dual;
select '<TABLE BORDER  CELLPADDING=2>' FROM dual;

select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>Free Space in MB</FONT></B></TH>'
 FROM dual;
select '<TR><TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' ||sum(bytes)/1024/1024|| '</FONT></TD></TR>'
from dba_free_space
where tablespace_name ='&FLOWS_FILES_TABLESPACE';
select '</TABLE>' FROM dual;

--END   Free Space in FLOWS_FILES Tablespace

--START  Temporary Tablespace used by the APEX Installation (By default the APEX Schema and FLOWS_FILES use same temporary tablespace during installation)

select '<h5><font face="VERDANA"><font color="#000000">Default Temporary Tablespace used for &APEX is: &TEMP_APEX<font size=-2></font></font></font></h5>'
FROM dual;
--select '<TABLE BORDER  CELLPADDING=2>' FROM dual;

--END   Temporary Tablespace used by the APEX Installation (By default the APEX Schema and FLOWS_FILES use same temporary tablespace during installation)


-- Begin Get Job Queue Processes

select '<h5><font face="VERDANA"><font color="#000000"> Number of Job Queue Processes<font size=-2></font></font></font></h5>' FROM dual;
select '<TABLE BORDER  CELLPADDING=2>' FROM dual;
select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2> Number of Job Queue Processes</FONT></B></TH>' FROM dual;
select '<TR><TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || value  ||  '</FONT></TD></TR>' from v$parameter where name='job_queue_processes';
select '</TABLE>' FROM dual;


-- End Get Job Queue Processes 


--Start Get information about XML DB

select '<h5><font face="VERDANA"><font color="#000000">' ||
       'XDB STATUS <font size=-2></font></font></font></h5>'
   FROM dual;
select '<TABLE BORDER  CELLPADDING=2>' FROM dual;
select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>' ||
       'owner</FONT></B></TH>' FROM dual;
select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>' ||
       'object_name</FONT></B></TH>' FROM dual;
select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>' ||
       'object_type</FONT></B></TH>' FROM dual;
select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>' ||
       'Status</FONT></B></TH>' FROM dual;

col owner format a10
col object_name format a20
select '<TR><TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || owner       ||'</FONT></TD>',
           '<TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || object_name || '</FONT></TD>',
           '<TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || object_type || '</FONT></TD>',
           '<TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' ||      status || '</FONT></TD></TR>'
       from dba_objects where object_name = 'DBMS_XMLPARSER';
select '</TABLE>' FROM dual;
select '<body><font face="Arial,Helvetica"><font color="#FF0000">' ||
       '<font size=3>Make sure XML DB packages are installed and valid.  You should see the output as below:<BR><BR>
         PUBLIC....DBMS_XMLPARSER....SYNONYM..............VALID<BR>
         XDB..........DBMS_XMLPARSER....PACKAGE...............VALID<BR>
         XDB..........DBMS_XMLPARSER....PACKAGE BODY....VALID</font></font></font></body>'
from dual;

--END Get information about XML DB

--Start Determine if APEX is a Development or Runtime Installation
define WWV_FLOWS = 'WWV_FLOWS'
define INSTALL_TYPE = '&APEX..&WWV_FLOWS'

select '<h5><font face="VERDANA"><font color="#000000">' ||
       'APEX Install Type (1=Dev 0=Runtime) <font size=-2></font></font></font></h5>'
   FROM dual;
select '<TABLE BORDER  CELLPADDING=2>' FROM dual;
select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>' ||'Install Type</FONT></B></TH>' FROM dual;
select '<TR><TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || count(1)  ||
       '</FONT></TD></TR>' from &INSTALL_TYPE where id = 4000;
select '</TABLE>' FROM dual;
--End Determine if APEX is a Development or Runtime Installation


--BEGIN Determine if APEX has ever been used
exec dbms_output.put_line('<h5><font face="VERDANA"><font color="#000000">Has APEX Been Used? <font size=-2></font></font></font></h5>');
exec dbms_output.put_line('<TABLE BORDER  CELLPADDING=2>');
exec dbms_output.put_line('<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>Workspace Count</FONT></B></TH>');
select '<TR><TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || count(1)  ||
       '</FONT></TD></TR>' from apex_workspaces where  workspace_id > 100000;
exec dbms_output.put_line('</TABLE>');
--Comment on usage 
select '<body><font face="Arial,Helvetica"><font color="#FF0000">' ||
       '<font size=3>A value of 0 indicates APEX has never been configured for usage.<BR>
        A value greater than 0 means it has.</font></font></font></body>'
   from dual;
--END   Determine if APEX has ever been used


--Start Determine DB Service Name

select '<h5><font face="VERDANA"><font color="#000000">' ||
       'Database Service Name <font size=-2></font></font></font></h5>'
   FROM dual;
select '<TABLE BORDER  CELLPADDING=2>' FROM dual;
select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>' ||
       'DB Service Name</FONT></B></TH>' FROM dual;
select '<TR><TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || value  ||
       '</FONT></TD></TR>' from v$parameter where name='service_names';
select '</TABLE>' FROM dual;
--End  Determine DB Service Name


--START check for enabling of Network Services
select '<h5><font face="VERDANA"><font color="#000000">' ||
       'Enabling of Network Services (11g DBs and Later) <font size=-2></font></font></font></h5>'
   FROM dual;
select '<TABLE BORDER  CELLPADDING=2>' FROM dual;
select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>' ||
       'ACL</FONT></B></TH>' FROM dual;
select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>' ||
       'Principal</FONT></B></TH>' FROM dual;
select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>' ||
       'Privilege</FONT></B></TH>' FROM dual;
select '<TR><TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || acl ||
       '</FONT></TD>', '<TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' ||
       principal || '</FONT></TD>','<TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' ||
       privilege || '</FONT></TD></TR>'
       from dba_network_acl_privileges;
select '</TABLE>' FROM dual;
--END check for enabling of network services

--START Get DBA Registry Info

select '<h5><font face="VERDANA"><font color="#000000">' ||'DBA Registry Info <font size=-2></font></font></font></h5>'
   FROM dual;
select '<TABLE BORDER  CELLPADDING=2>' FROM dual;
select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>' ||'Component ID</FONT></B></TH>' FROM dual;
select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>' ||
       'Component Name</FONT></B></TH>' FROM dual;
select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>' ||
       'Version</FONT></B></TH>' FROM dual;
select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>' ||
       'Schema</FONT></B></TH>' FROM dual;
select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>' ||
       'Status</FONT></B></TH>' FROM dual;

col comp_name format a30
col version format a10
col status format a10
col comp_id format a15

select '<TR><TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || comp_id ||'</FONT></TD>',
           '<TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || comp_name || '</FONT></TD>',
           '<TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || version || '</FONT></TD>',
           '<TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || schema || '</FONT></TD>',
           '<TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || status || '</FONT></TD></TR>'
       from dba_registry; 
select '</TABLE>' FROM dual;

--END Get DBA Registry Info

--START Get APEX Instance Settings

exec dbms_output.put_line( '<h5><font face="VERDANA"><font color="#000000">'||' APEX Instance Settings <font size=-2></font></font></font></h5>' );

exec dbms_output.put_line('<TABLE BORDER  CELLPADDING=2>');
exec dbms_output.put_line('<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>' ||'Name</FONT></B></TH>');
exec dbms_output.put_line('<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>' ||'Value</FONT></B></TH>');
exec dbms_output.put_line('<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>' ||'Description</FONT></B></TH>');

select '<TR><TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' ||Name||'</FONT></TD>',
           '<TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' ||Value || '</FONT></TD>',
           '<TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' ||pref_desc|| '</FONT></TD></TR>'
from &APEX..wwv_flow_platform_prefs order by name;
-- where NAME in('AUTOEXTEND_TABLESPACES','BIGFILE_TABLESPACES_ENABLED','PRINT_BIB_LICENSED','PRINT_SVR_PROTOCOL','PRINT_SVR_HOST','PRINT_SVR_PORT','SMTP_HOST_ADDRESS','SMTP_HOST_PORT') order by name;
           --from &APEX..wwv_flow_platform_prefs order by name;
exec dbms_output.put_line('</TABLE>');


--END   Get APEX Instance Settings

--START  Show the number of objects granted to the APEX schema
exec dbms_output.put_line( '<h5><font face="VERDANA"><font color="#000000">'||' APEX Instance Grant Information. (Note that grant details may vary between APEX versions).<font size=-2></font></font></font></h5>' );
exec dbms_output.put_line('<h5><font face="VERDANA"><font color="#33ccff"> <font size=-2></font></font></font></h5>');
exec dbms_output.put_line('<TABLE BORDER  CELLPADDING=2>');
exec dbms_output.put_line('<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>Total Objects Granted to &APEX</FONT></B></TH>');
select '<TR><TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || count(1) ||'</FONT></TD></TR>'
from dba_tab_privs where grantee = '&APEX';
exec dbms_output.put_line('</TABLE>');
--END

--Start Get Grants given to APEX Schema

exec dbms_output.put_line('<h5><font face="VERDANA"><font color="#000000">The following displays all grants issued to the &APEX Schema.<font size=-2></font></font></font></h5>');
exec dbms_output.put_line('<TABLE BORDER  CELLPADDING=2>');
exec dbms_output.put_line('<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>Table Name</FONT></B></TH>');
exec dbms_output.put_line('<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>Privilege</FONT></B></TH>');

select '<TR><TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || table_name || '</FONT></TD>',
           '<TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || privilege || '</FONT></TD></TR>'
from dba_tab_privs where grantee = '&APEX'
order by table_name;

exec dbms_output.put_line('</TABLE>');
--END

--START TOTAL INVALID OBJECTS

select '<h5><font face="VERDANA"><font color="#000000">' ||
       'Number of Invalid Objects in the DB <font size=-2></font></font></font></h5>'
   FROM dual;

select '<TABLE BORDER  CELLPADDING=2>' FROM dual;
select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>' ||
       'Total Invalid Objects in DB</FONT></B></TH>' FROM dual;
select '<TR><TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || count(1) ||
       '</FONT></TD></TR>' from dba_objects where status = 'INVALID';
select '</TABLE>' FROM dual;
-- COMMENTS
select '<body><font face="Arial,Helvetica"><font color="#FF0000">' ||
       '<font size=3>There should be no invalid objects in the database ' ||
       'pertaining to the owners within APEX/FLOWS. If there ' ||
       'are any, recompile. Use the <b>utlrp.sql</b> script under the ' ||
       'database home to recompile.</font></font></font></body>' from dual;
--end TOTAL INVALID OBJECTS


--START LIST OF ALL INVALID OBJECTS IN THE DATABASE
select '<h5><font face="VERDANA"><font color="#000000">' ||
       'List of ALL Invalid Objects in the DB <font size=-2></font></font></font></h5>'
   FROM dual;
select '<TABLE BORDER  CELLPADDING=2>' FROM dual;
select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>Owner</FONT></B></TH>' FROM dual;
select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>Object Name</FONT></B></TH>' FROM dual;
select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>Object type</FONT></B></TH>' FROM dual;
select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>Status</FONT></B></TH>' FROM dual;

select '<TR><TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || OWNER       ||'</FONT></TD>',
           '<TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || object_name ||'</FONT></TD>',
           '<TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || object_type ||'</FONT></TD>',
           '<TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || status      ||'</FONT></TD></TR>'
   from DBA_OBJECTS where status = 'INVALID' order by owner;
select '</TABLE>' FROM dual;
--END LIST OF INVALID OBJECTS IN THE DATABASE

--START TOTAL INVALID SYNONYMS

select '<h5><font face="VERDANA"><font color="#000000">' ||
       'Number of Invalid Synonyms in the DB <font size=-2></font></font></font></h5>'
   FROM dual;

select '<TABLE BORDER  CELLPADDING=2>' FROM dual;
select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>' ||
       'Total Invalid Synonyms in DB</FONT></B></TH>' FROM dual;
select '<TR><TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || count(1) ||
       '</FONT></TD></TR>' from dba_objects where status = 'INVALID' and object_type='SYNONYM';
select '</TABLE>' FROM dual;
-- COMMENTS
select '<body><font face="Arial,Helvetica"><font color="#FF0000">' ||
       '<font size=3>There should be no invalid objects in the database ' ||
       'pertaining to the owners within APEX/FLOWS. If there ' ||
       'are any, recompile. Use the <b>utlrp.sql</b> script under the ' ||
       'database home to recompile.</font></font></font></body>' from dual;
--end TOTAL INVALID SYNONYMS


--START LIST OF INVALID SYNONYMS AND THEIR OWNERS IN THE DATABASE

select '<h5><font face="VERDANA"><font color="#000000">' ||
       'List of Invalid SYNONYMS in the DB <font size=-2></font></font></font></h5>'
   FROM dual;

select '<TABLE BORDER  CELLPADDING=2>' FROM dual;
select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>Synonym Owner</FONT></B></TH>' FROM dual;
select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>Synonym Name</FONT></B></TH>' FROM dual;
select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>Object Owner</FONT></B></TH>' FROM dual;
select '<TH BGCOLOR=#33ccff><B><FONT FACE="ARIAL" COLOR="#FFFFFF" SIZE=2>Object Name </FONT></B></TH>' FROM dual;

select '<TR><TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || A.OWNER        ||'</FONT></TD>',
           '<TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || A.SYNONYM_NAME ||'</FONT></TD>',
           '<TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || A.TABLE_OWNER  ||'</FONT></TD>',
           '<TD BGCOLOR=#FFFFFF><FONT FACE="ARIAL" SIZE=2> ' || A.TABLE_NAME   ||'</FONT></TD></TR>'
from DBA_SYNONYMS A, DBA_OBJECTS B
where A.SYNONYM_NAME=B.OBJECT_NAME AND A.OWNER=B.OWNER AND B.STATUS='INVALID'
order by A.OWNER;
select '</TABLE>' FROM dual;

--END LIST OF INVALID SYNONYMS AND THEIR OWNERS IN THE DATABASE
exec dbms_output.put_line('</html>');

spool off


Monday, 14 December 2015

DD Boost for RMAN


Oracle Administrators need more and more control over the different aspects of their environment as not only is the amount of information in their databases growing in the order of TBs, but they find they have less time to take backups, they have to satisfy retention requests for more information over the longer term and they want that data to be available online so they can get what they need when they need it and move on with their lives.

DD Boost for RMAN allows Oracle Administrators to manage the entire process of backup to, restore from and offsite DR with Data Domain all from their existing Oracle tools.

Boost integrating the RMAN client with the Data Domain appliance and bringing it’s unique advantages into play, such distributed segment processing which reduces the amount of data transmitted across a network link during the backup operation by checking with the Data Domain system that the data deduplicated by Boost isn’t already stored in the appliance and doesn’t need to be transmitted again. This can dramatically reduce the amount of time required to perform a full backup (Which Oracle recommend if the change rate is more than 20% between backups) and as such can really shrink the required backup window.

DD Boost for RMAN also provides application level automatic load balancing across multiple paths to a Data Domain appliance to ensure optimal information transfer performance during the backup operation. And it does this while also ensuring failover due to a path going down will not require a restart of the backup job. The job will just continue across the surviving interfaces.

And then we come to offsite replication. DD Boost for RMAN allows the Oracle Administrator to offsite their backups to additional Data Domain appliances and do so in a catalogue consistent manner. Their offsite copies, a product of replication between Data Domain appliances, will be fully visible to them from RMAN and can be recovered from directly.

The bottom line is Oracle Database Administrators can now use unique features of the dedicated protection infrastructure Backup Administrators can make available to them from Data Domain without having to write crazy scripts or learn new tools. This is infrastructure the Backup Administrator has visibility into and can report on.


In turn the unique advantages of this infrastructure in deduplicated storage capacity, replica management, encryption of data in flight/data at rest and the fact it’s online negate the need to use production storage to keep backups, which releases that storage to the Storage Administrator for other uses. There’s a very simple concept at work here.

Saturday, 21 November 2015


0509-036 Cannot load program 0712-001 0712-002 AIX Oracle DB 12.1.0.2




Cause:


IOCP module not enabled on the server.


To check if the IOCP module is enabled, run the lsdev command.

The following sample output shows the IOCP status is set to Defined and hence not enabled:



Solution:

Enable the "iocp" option and  relink the oracle binaries.

To enable IOCP, set IOCP to Available using the following procedure:

Log in as root and run the following command:

# smitty iocp
Select Change / Show Characteristics of I/O Completion Ports.

Change configured state at system restart from Defined to Available.

Run the lsdev command to confirm the IOCP status is set to Available:



Perform a system restart to make the changes permanent.

Relink the binaries using " relink all " and then root.sh as root user.

Oracle Rules Manager

Rules Manager is a feature of Oracle Database that uses the Expression Filter and object relational features to provide the features of a special-purpose rules engine with greater scalability and better operational characteristics.

Rules Manager and Expression Filter provide a SQL schema and PL/SQL and Java packages that store, retrieve, update, and query collections of expressions (rule conditions) in an Oracle Database. All these objects are created in a dedicated schema named EXFSYS.

Rules Manager and Expression Filter are installed automatically with Oracle Database Standard Edition and Oracle Database Enterprise Edition and higher.

Oracle Expression Filter, a feature beginning in Oracle Database 10g, is a component of Rules Manager that allows application developers to store, index, and evaluate conditional expressions (expressions) in one or more columns of a relational table. Expressions are a useful way to describe interests in expected data.

 



·        The script to install Rules Manager is named  :
catrul.sql under $ORACLE_HOME/rdbms/admin/directory.

·        Rules Manager can be uninstalled using :
catnorul.sql script in the same directory

·        The script to install the Expression Filter is named :
catexf.sql under $ORACLE_HOME/rdbms/admin/directory.

·        Expression Filter can be uninstalled using :
catnoexf.sql script in the same directory

·        Rules Manager  RUL and Expression Filter  EXF components :

SQL>select comp_id from dba_registry where comp_id in('EXF','RUL');
SQL> select comp_id, comp_name, version, status from dba_registry ;

NOTE: These scripts should be executed from a SQL*Plus session while connected as SYSDBA. Uninstalling Expression Filter implicitly uninstalls Rules Manager.


The Rules Manager and Expression Filter features are the same in the Standard and Enterprise Editions. Support for indexing expressions is available only in the Enterprise Edition because it requires bitmap index support.




OPatch failed with error code 73



If $ opatch apply fails due to below error:





Then check for /oradba/app/oracle/product/12102/cfgtoollogs/opatch/opatch2015-09-18_00-23-57AM_1.log and if you find below under logs:




       Find the process, kill it and run “opatch apply” again.




.




ERROR: Suspended on broken target (OEM 12c)



If you encounter such error for scheduled backup job of database into OEM:






SOLUTION:

If you get this error while running jobs via cloud control on a particular database – “suspended on broken target”

We just need to configure the target again – in case of this error.

No need to remove the target beforehand, simply click on targets/databases, choose the database and click configure – uses already saved XXXXXX password , simply click Next and proceed.

Run job again – it should go through.
ORA-20500 ORA-06512 ORA-06512 (Error while adding target into OEM)


ERROR:

testab1 (oracle_database)
ORA-20500: Target Already exists: testab1:oracle_database:oracle.linux.net:https://oracle.linux.net:3872/emd/main/:3:0
ORA-06512: at "SYSMAN.EM_TARGET", line 3998
ORA-06512: at line 1

SOLUTION:

Log in to the EM console.
Select Setup > Add targets > Auto Discovery Results.
Select the database (from the Ignored Targets tab)








Now, try to add database and at the last step while saving, you won’t be getting an error.






Reference: 

Enterprise Manager Cloud Control 12c - Manually adding a database target fails with ORA-20500 (Doc ID 1523394.1)

Monday, 23 March 2015

Oracle Multimedia , JAVAM and XML components , ora-04031

To install Oracle Multimedia, first you need to check for the existence of  JAVAM and XML components into database.


"Where to Find the Information to Install, Upgrade, Downgrade and Deinstall interMedia/Oracle Multimedia? (Doc ID 337415.1)"


"How to Reload the JVM in 11.2.0.x (Doc ID 1112983.1)" and use "Javavm Component Invalid and ORA-04031 Reported for "java pool" (Doc ID 1448032.1)" to troubleshoot error coming while installing JVM


-- Start of File full_rmjvm.sql
> spool full_rmjvm.log
> set echo on
> connect / as sysdba
> startup mount
> alter system set "_system_trig_enabled" = false scope=memory;
> alter system enable restricted session;
> alter database open;
> @?/rdbms/admin/catnoexf.sql
> @?/rdbms/admin/catnojav.sql
> @?/xdk/admin/rmxml.sql
> @?/javavm/install/rmjvm.sql
truncate table java$jvm$status;
select * from obj$ where obj#=0 and type#=0;
delete from obj$ where obj#=0 and type#=0;
commit;
select owner, count(*) from all_objects
where object_type like '%JAVA%' group by owner;
select obj#, name from obj$
where type#=28 or type#=29 or type#=30 or namespace=32;
select o1.name from obj$ o1,obj$ o2
where o1.type#=5 and o1.owner#=1 and o1.name=o2.name and o2.type#=29;
shutdown immediate
set echo off
spool off
exit
-- End of File full_rmjvm.sql





Please note that @?/javavm/install/initjvm.sql can fail if java_pool_size is small,so as per docs it has to be increased to atleast a value of 150M

-- Start of File full_jvminst.sql
> spool full_jvminst.log;
> set echo on
> connect / as sysdba
> startup mount
> alter system set "_system_trig_enabled" = false scope=memory;
> alter database open;
> select obj#, name from obj$
   where type#=28 or type#=29 or type#=30 or namespace=32;
> @?/javavm/install/initjvm.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
> @?/xdk/admin/initxml.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
> @?/xdk/admin/xmlja.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
> @?/rdbms/admin/catjava.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
> @?/rdbms/admin/catexf.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
> shutdown immediate
> set echo off
> spool off
> exit
-- End of File full_jvminst.sql


Please validate :
> select version, status from dba_registry where comp_id='JAVAVM';
VERSION                        STATUS
------------------------------ -----------
11.2.0.3.0                     VALID

> select version, status from dba_registry where comp_id='XDB';
VERSION                        STATUS
------------------------------ -----------
11.2.0.3.0                     VALID

> select version, status from dba_registry where comp_id='XML';
VERSION                        STATUS
------------------------------ -----------
11.2.0.3.0                     VALID






Now, proceeding to ORACLE MULTIMEDIA Installation steps :

Installation steps for Oracle Multimedia can be found at : $ORACLE_HOME/ord/im/admin/README.txt
"Verifying an Installed Version of Oracle Multimedia (Doc ID 458228.1)"

Installing Oracle Multimedia
============================
Oracle Multimedia is installed and configured with Oracle Database.
If you need to configure Oracle Multimedia manually, follow the
instructions in this section.
        1 Installation Decisions
        2 Pre-Installation Steps
        3 Installation Steps
1 Installation Decisions
------------------------
  The installation procedure creates the users ORDSYS, ORDPLUGINS,
  SI_INFORMTN_SCHEMA, ORDDATA, and MDSYS.  These user are created with
  the privileges required for Oracle Multimedia.
  Decision 1: Decide which tablespace to use for Oracle Multimedia users
  (ORDSYS, ORDPLUGINS, SI_INFORMTN_SCHEMA, and ORDDATA), and which
  tablespace to use for the Oracle Spatial/Oracle Multimedia Location
  Services user (MDSYS).  Oracle recommends using the SYSAUX tablespace
  for both.

2 Pre-Installation Steps
------------------------
  For more instructions on installing on your system and setting
  up your environment, see the Oracle Installation Guide for your
  operating system.
  Perform the following pre-installation tasks prior to manually
  installing and configuring Oracle Multimedia:
 
  2.1 Install Oracle Database, including PL/SQL, Oracle JVM, Oracle XML
      Database, and Oracle XDK.
  2.2 Create the database.
  2.3 Start the database.
  2.4 Verify that required software is installed and valid.
      To verify that required software is correctly installed, run SQL*Plus,
      connect AS SYSDBA, and enter the following queries:
      SQL> select version, status from dba_registry where comp_id='JAVAVM';
      SQL> select version, status from dba_registry where comp_id='XDB';
      SQL> select version, status from dba_registry where comp_id='XML';
      Ensure that the versions are correct and the status values are 'VALID'.

3 Installation Steps
--------------------
  Perform the following mandatory configuration steps. These steps are
  required ONLY if you are configuring Oracle Multimedia manually.
  You do NOT need to perform these steps if you use the Database
  Configuration Assistant.
  References to <ORACLE_HOME> in these instructions represent the Oracle
  home directory.

3.1 Use the Oracle Universal Installer to install the files that make
    up Oracle Multimedia on your system.
3.2 Create the users and grant the appropriate privileges.
    1) Start SQL*Plus and connect as SYSDBA.
    2) Invoke ordinst.sql with two parameters for the Oracle
       Multimedia tablespace and the Oracle Location Services
       tablespace.
       SQL> @<ORACLE_HOME>/ord/admin/ordinst.sql SYSAUX SYSAUX (on Linux/UNIX)
            @<ORACLE_HOME>\ord\admin\ordinst.sql SYSAUX SYSAUX (on Windows)
3.3 Install Oracle Multimedia types and packages.
    1) Start SQL*Plus and connect as SYSDBA.
    2) Invoke catim.sql
       SQL> @<ORACLE_HOME>/ord/im/admin/catim.sql (on Linux/UNIX)
            @<ORACLE_HOME>\ord\im\admin\catim.sql (on Windows)

After these mandatory installation steps have been completed, Oracle
Multimedia is ready for use.
                        =======================

Verifying the Oracle Multimedia Installation
============================================
After you have installed or upgraded Oracle Multimedia, you can verify the
Oracle Multimedia installation by invoking the Oracle Multimedia validation
procedure.
To run the Oracle Multimedia validation procedure, connect as SYSDBA and
execute the procedure validate_ordim as follows:
    1) Start SQL*Plus and connect AS SYSDBA.
    2) Execute SYS.validate_ordim
       SQL> execute SYS.validate_ordim;
   
    The validation procedure silently  sets the Oracle Multimedia registry
    entry to 'VALID' if Oracle Multimedia is valid.  If there are invalid
    objects detected, the validation procedure lists the first few invalid
    objects and sets the registry entry to 'INVALID'.
    To query the registry entry for Oracle Multimedia, run SQL*Plus, connect
    AS SYSDBA, and enter the following query:
       SQL> select version, status from dba_registry where comp_id='ORDIM';

Ensure that the version is correct and the status is 'VALID'.
 

Thursday, 27 November 2014

ORA-00313 ORA-00312 ORA-27037 Standby Database


PROBLEM :

 


ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: '/d02/oradata/XXXXXXXX/redog1m2.log'

ORA-27037: unable to obtain file status

IBM AIX RISC System/6000 Error: 2: No such file or directory

Additional information: 3

ORA-00312: online log 1 thread 1: '/d01/oradata/ XXXXXXXX/redog1m1.log'

ORA-27037: unable to obtain file status

IBM AIX RISC System/6000 Error: 2: No such file or directory

Additional information: 3

Clearing online redo logfile 1 /d01/oradata/ XXXXXXXX/redog1m1.log

Clearing online log 1 of thread 1 sequence number 120028

Errors in file /oradba/app/oracle/diag/rdbms/ XXXXXXXX/ XXXXXXXX/trace/ XXXXXXXX_mrp0_64356518.trc:


 

 



SOLUTION :

 


SQL> alter database recover managed standby database cancel;

SQL>alter system set log_file_name_convert=’junk,junk’ scope=spfile;

System altered.

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

Wednesday, 26 November 2014

System Workload Statistics (Gather,Export and Import)


As noworkload system statistics are initialized to default values at the first database startup, we need to first gather system WORKLOAD statistics. This will gather statistics during the current workload (which is supposed to be representative of actual system I/O and CPU workload on the DB and need to be gathered when there is heavy/good workload on database)

 

a)    Gather it :


SQL> execute dbms_stats.gather_system_stats('start');


Once the workload window ends after 3,4.. hours or whatever, system statistics gathering need to be stopped using below:

SQL> execute dbms_stats.gather_system_stats('stop');

 

OR


We can use time interval (minutes) instead of issuing start/stop command manually:


SQL> execute dbms_stats.gather_system_stats('interval',60);

 

Please note that gathering workload system statistics doesn't put additional load into system.

 

Check the system values collected:
--------------------------------------------
sql> col pname format a20
sql> col pval2 format a40
sql> select * from sys.aux_stats$;

 

b)    Create STAT table and export the system stats :

 

SQL> exec dbms_stats.create_stat_table(stattab=>'STATS_TABLE_NAME',ownname=>'schema',tblspace => 'tablespace_name');

SQL> EXEC dbms_stats.export_system_stats(stattab=>'STATS_TABLE_NAME',statown=>'OWNER');

 

Example –

SQL> exec dbms_stats.create_stat_table(ownname => 'SYS', stattab => 'prod_stats',tblspace => 'USERS');

SQL> exec dbms_stats.export_system_stats(statown => 'SYS', stattab => 'prod_stats');

 

 

c)     Move statistics from one database to another, copy the statistics table to the second database using datapump and finally import the statistics into the second database



d)    Import the statistics into data dictionary :



Note that the optimizer does not use statistics stored in a user-owned table. The only statistics used by the optimizer are the statistics stored in the data dictionary. In order to have the optimizer use the statistics in a user-owned tables, you must import those statistics into the data dictionary using the statistics import procedures.

 

SQL> exec dbms_stats.import_system_stats(stattab => 'prod_stats',statown => 'SYS');