Data Pump

Question On Oracle Data Pump

Here are some question related to Data Pump which will help you to clear your doubt regarding Data Pump.

1.) What is Oracle Data Pump?

Oracle Data Pump is a new feature of Oracle  Database 10g  that provides  high speed, parallel,bulk data and metadata movement of Oracle database contents. A new public interface package, DBMS_DATAPUMP, provides a server-side infrastructure for fast data and metadata movement. In Oracle Database 10g, new Export (expdp) and Import (impdp) clients that use this interface have been provided. Oracle recommends that customers use these new Data Pump Export and  Import clients rather than the Original Export and Import clients, since the new utilities have vastly improved performance and greatly enhanced functionality.

2.)  Is Data Pump a feature or an option of Oracle 10g?

Data Pump is a fully integrated feature of Oracle Database 10g. Data Pump is installed automatically during database creation and database upgrade.

3.) What platforms is Data Pump provided on?

Data Pump is available on the Oracle Database 10g Standard Edition, Enterprise Edition, and Personal Edition. However, the  parallel capability  is only available on  Oracle10g   Enterprise Edition. Data Pump is  included on all the same platforms  supported  by Oracle 10g, including Unix, Linux, Windows NT, Windows 2000, and Windows XP.

4.) What are the system requirements for Data Pump?

The  Data  Pump  system requirements  are  the same  as  the  standard Oracle  Database 10g requirements. Data Pump doesn’t need a lot of additional system or database resources, but the time to extract and treat the information will be dependent on the CPU and memory available on each  machine. If  system  resource  consumption becomes  an issue  while a Data Pump job is executing,  the  job  can be  dynamically  throttled to  reduce the number  of  execution  threads.

5.) What is the performance gain of Data Pump Export versus Original Export?

Using  the  Direct  Path  method  of  unloading, a single  stream  of data  unload is about 2 times faster than  original  Export  because  the Direct  Path API  has been  modified to be  even  more efficient. Depending on the level of parallelism, the level of improvement can be much more.

6.) What is the performance gain of Data Pump Import versus Original Import?

A  single  stream  of data load is 15-45 times  faster  Original  Import. The reason it  is  so much faster  is that Conventional  Import uses only  conventional mode  inserts, whereas  Data Pump Import uses the Direct Path  method  of  loading. As  with Export, the  job can be parallelized for even more improvement.

7.) Does Data Pump require special tuning to attain performance gains?

No, Data Pump requires no special tuning. It runs optimally “out of the box”. Original Export and (especially) Import require careful tuning to achieve optimum results.

8.) Why are directory objects needed?

They are needed to ensure data security and integrity. Otherwise, users would be able to read data that they should not have access to and perform unwarranted operations on the server.

9.) What makes Data Pump faster than original Export and Import?

There are three main reasons that Data Pump is faster than original Export and Import. First,the Direct Path data access method (which permits the server to bypass SQL and go right to the data blocks on disk) has been rewritten to be much more efficient and now supports Data Pump Import and Export. Second, because Data Pump does its processing on the server rather than in the client, much less data has to be moved between client and server. Finally, Data Pump was designed from the ground up to take advantage of modern hardware and operating system architectures in ways that original Export/ and Import cannot. These factors combine to produce significant performance improvements for Data Pump over original Export and Import .

10.) How much faster is Data Pump than the original Export and Import utilities?

For a single stream, Data Pump Export is approximately 2 times faster than original Export and Data Pump Import is approximately 15 to 40 times faster than original Import. Speed can be dramatically improved using the PARALLEL parameter.

11.) Why is Data Pump slower on small jobs?

Data Pump was designed for big jobs with lots of data. Each Data Pump job has a master table that has all the information about the job and is needed for restartability. The overhead of creating this master table makes small jobs take longer, but the speed in processing large amounts of data gives Data Pump a significant advantage in medium and larger jobs.

12.) Are original Export and Import going away?

Original Export is being deprecated with the Oracle Database 11g release. Original Import will always be supported so that dump files from earlier releases (release 5.0 and later) will be able to be imported. Original and Data Pump dump file formats are not compatible.

13.) Are Data Pump dump files and original Export and Import dump files compatible?

No, the dump files are not compatible or interchangeable. If you have original Export dump  files, you must use original Import to load them.

14.) How can I monitor my Data Pump jobs to see what is going on?

In interactive mode, you can get a lot of detail through the STATUS command. In SQL, you can query the following views:

•        DBA_DATAPUMP_JOBS - all active Data Pump jobs and the state of each job
•        USER_DATAPUMP_JOBS – summary of the user’s active Data Pump jobs
•        DBA_DATAPUMP_SESSIONS – all active user sessions that are attached to a Data Pump Job
•        V$SESSION_LONGOPS – shows all progress on each active Data Pump job

 15.) Can you adjust the level of parallelism dynamically for more or less resource consumption?

Yes, you can dynamically  throttle the number of threads of execution  throughout the lifetime of the job. There is an interactive command mode where you can adjust the level of parallelism. So, for example, you can start up a job during the day with a PARALLEL=2, and then increase it at night to a higher level.

16.) Can I use gzip with Data Pump?

Because Data Pump uses parallel operations to achieve its high performance, you cannot pipe the output of Data Pump export through gzip. Starting in Oracle Database 11g, the  COMPRESSION parameter can be used to compress a Data Pump dump file as it is being created. The  COMPRESSION parameter is available as part of the Advanced Compression Option for Oracle Database 11g

17.)Does Data Pump support all data types?

Yes, all the Oracle database data types are supported via Data Pump’s two data movement mechanisms, Direct Path and External Tables.

18.) What kind of object selection capability is available with Data Pump?

With  Data Pump, there  is  much  more  flexibility  in  selecting  objects  for  unload  and  load operations . You can now unload any subset of database objects  (such as functions, packages, and procedures) and  reload them  on the target platform. Almost  all database object   types can  be excluded or  included in an  operation using  the new  Exclude and Include parameters.

19.) Is it necessary to use the Command line interface or is there a GUI that you can use?

You can either use the Command line interface or the Oracle Enterprise Manager web-based GUI interface.

20.) Can I move a dump file set across platforms, such as from Sun to HP?

Yes, Data Pump handles all the necessary compatibility issues between hardware platforms and operating systems.

21.) Can I take 1 dump file set from my source database and import it into multiple databases?

Yes, a single dump file set can be imported into multiple databases. You can also just import different subsets of the data out of that single dump file set.

22.) Is there a way to estimate the size of an export job before it gets underway?

Yes, you can use the “ESTIMATE ONLY” command to see how much disk space is required for the job’s dump file set before you start the operation.

23.) Can I monitor a Data Pump Export or Import job while the job is in progress?

Yes, jobs can be monitored from any location is going on. Clients may also detach from an executing job without affecting it.

24.) If a job is stopped either voluntarily or involuntarily, can I restart it?

Yes, every Data Pump job creates a Master Table in which the entire record of the job is maintained. The Master Table is the directory to the job, so if a job is stopped for any reason, it can be restarted at a later point in time, without losing any data.

25.) Does Data Pump give me the ability to manipulate the Data Definition Language (DDL)?

Yes, with  Data Pump, it  is  now  possible to change the definition of some  objects  as  they are Created  at  import time. For example, you  can remap  the  source  datafile  name  to the  target datafile name in all DDL statements where the source datafile is referenced. This is really useful if you are moving across platforms with different file system syntax.

26.) Is Network Mode supported on Data Pump?

Yes, Data Pump Export and Import both support a network mode in which the job’s source is a remote oracle instance. This is an overlap of unloading the data, using Export, and loading the data, using Import, so those processes don’t have to be serialized. A database  link  is used for the   network.  You  don’t  have  to worry  about  allocating  file  space  because  there  are  no intermediate dump files.

27.) Does Data Pump support Flashback?

Yes, Data Pump supports the Flashback infrastructure, so you can perform an export and get a dump file set that is consistent with a specified point in time or SCN.

28.) Can I still use Original Export? Do I have to convert to Data Pump Export?

An Oracle9i compatible Export that operates against Oracle Database 10g will ship with Oracle 10g, but   it  does  not export  Oracle Database  10g features. Also, Data Pump Export  has new Syntax   and a new    client executable, so Original  Export  scripts will  need to change.  Oracle recommends that customers convert to use the Oracle Data Pump Export.

29.) How do I import an old dump file into Oracle 10g? Can I use Original Import or do I have to  convert to Data Pump Import?

Original  Import  will be  maintained  and  shipped  forever, so  that  Oracle  Version 5.0  through Oracle9i  dump  files will  be able  to be loaded into Oracle 10g and later. Data Pump Import can only read Oracle Database 11g (and later) Data Pump Export dump files. Data Pump Import has new syntax and a new client executable, so Original Import scripts will need to change.

30.) When would I use SQL*Loader instead of Data Pump Export and Import?

You would use SQL*Loader to load data from external files into tables of an Oracle database.Many customers use SQL*Loader on a daily basis to load files (e.g. financial feeds) into their databases. Data Pump Export and Import may be used less frequently, but for very important tasks, such as migrating between platforms, moving data between development, test, and production databases, logical database backup, and for application deployment throughout a corporation.

31.)When would I use Transportable Tablespaces instead of Data Pump Export and Import?

You would use Transportable Tablespaces when you want to move an entire tablespace of data from one Oracle database to another. Transportable Tablespaces allows Oracle data files to be unplugged from a database, moved or copied to another location, and then plugged into another database. Moving data using Transportable Tablespaces can be much faster than performing either an export or import of the same data, because transporting a tablespace only requires the copying of datafiles and integrating the tablespace dictionary information. Even when transporting a tablespace, Data Pump Export and Import are still used to handle the extraction and recreation of the metadata for that tablespace.

Conclusion

Data Pump is fast and flexible. It replaces original Export and Import starting in Oracle Database 10g.Moving to Data Pump is easy, and opens up a world of new options and features.

INDEX_REBUILDING

INDEX_REBUILDING

·         Index plays crucial role in terms of performance of databases.

·         Index rebuilding is a mandatory activity in any oracle database environment.

·         Because oracle indexes are inadequate in its nature means they are not auto rebalanced whenever there is more number of inserts and deletes happens the free chunks will remain same

·         This leads to performance degradation in the real time

·         So all we need to do is rebuid them

·         There are certain conditions to rebuild the indexes as per the oracle standards

One is Height of the index

And the other is ration between leaf rows and deleted leaf rows

To find this all we need to do is analyze the structure of the index

Syntax to analyze:

SQL> ANALYZE INDEX INDEX_NAME VALIDATE STRUCTURE;

After validating the structure execute the below query to find the height and ratio

Syntax:

SELECT NAME, HEIGHT, LF_ROWS, DEL_LF_ROWS, (DEL_LF_ROWS/LF_ROWS)*100 AS RATIO FROM INDEX_STATS;

Well the conditions to rebuild them are

If Height > 3 and the Ratio >=20%

So now to rebuild them we have two options using online and without using online

What happens if we specify rebuild online ….?

You have the option of rebuilding the index online. Rebuilding online enables you to update base tables at the same time that you are rebuilding. The following statement rebuilds the index online:

Syntax:

ALTER INDEX INDEX_NAME REBUID;

ALTER INDEX INDEX_NAME REBUILD ONLINE;

DBUPGDIAG.SQL

-- - - - - - - - - - - - - - Script begins here - - - - - - - - - - - - - -
--  NAME:  DBUPGDIAG.SQL 
--  Version: 1.2
--  Executed as SYS as sysdba
-- ------------------------------------------------------------------------
-- AUTHOR: 
--    Raja Ganesh and Agrim Pandit - Oracle Support Services - DataServer Group
--    Copyright 2008, Oracle Corporation     
-- ------------------------------------------------------------------------
-- PURPOSE:
-- This script is intended to provide a user friendly output to diagonise
-- the status of the database before (or) after upgrade. The script will
-- create a file called db_upg_diag_<sid>_<timestamp>.log in your local
-- working directory. This does not make any DDL / DML modifications.
--
-- This script will work in both Windows and Unix platforms from database
-- version 9.2 or higher.
-- ------------------------------------------------------------------------
-- DISCLAIMER:
--    This script is provided for educational purposes only. It is NOT 
--    supported by Oracle World Wide Technical Support.
--    The script has been tested and appears to work as intended.
--    You should always run new scripts on a test instance initially.
-- ------------------------------------------------------------------------
--
--
col TODAY    NEW_VALUE    _DATE   
col VERSION NEW_VALUE _VERSION
set termout off
select to_char(SYSDATE,'fmMonth DD, YYYY') TODAY from DUAL;
select version from v$instance;
set termout on
set echo off
set feedback off
set head off
set verify off
Prompt
PROMPT Enter location for Spooled output:
Prompt
DEFINE log_path = &1
column timecol new_value timestamp
column spool_extension new_value suffix
SELECT to_char(sysdate,'dd_Mon_yyyy_hhmi') timecol,'.log' spool_extension FROM
sys.dual;
column output new_value dbname
SELECT value || '_' output FROM v$parameter WHERE name = 'db_name';
spool &log_path/db_upg_diag_&&dbname&&timestamp&&suffix
set linesize 150
set pages 100
set trim on
set trims on
col Compatible for a35
col comp_id for a12
col comp_name for a40
col org_version for a11
col prv_version for a11
col owner for a12
col object_name for a40
col object_type for a40
col Wordsize for a25
col Metadata for a8
col 'Initial DB Creation Info' for a35
col 'Total Invalid JAVA objects' for a45
col 'Role' for a30
col 'User Existence' for a27
col "JAVAVM TESTING" for a15
Prompt
Prompt
set feedback off head off
select LPAD('*** Start of LogFile ***',50) from dual;
select LPAD('Oracle Database Upgrade Diagnostic Utility',44)||
       LPAD(TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'),26) from dual;
Prompt
Prompt ===============
Prompt Hostname
Prompt ===============
select host_name from v$instance;
Prompt
Prompt ===============
Prompt Database Name
Prompt ===============
select name from v$database;
Prompt
Prompt ===============
Prompt Database Uptime
Prompt ===============
SELECT to_char(startup_time, 'HH24:MI DD-MON-YY') "Startup Time"
FROM v$instance;
Prompt
Prompt =================
Prompt Database Wordsize
Prompt =================
SELECT distinct('This is a ' || (length(addr)*4) || '-bit database') "WordSize"
FROM v$process;
Prompt
Prompt ================
Prompt Software Version
Prompt ================
SELECT * FROM v$version;
Prompt
Prompt =============
Prompt Compatibility
Prompt =============
SELECT 'Compatibility is set as '||value Compatible
FROM v$parameter WHERE name ='compatible';
Prompt
Prompt ================
Prompt Archive Log Mode
Prompt ================
Prompt
archive log list
Prompt
Prompt ================
Prompt Auditing Check
Prompt ================
Prompt
set head on
show parameter audit
Prompt
Prompt ================
Prompt Cluster Check
Prompt ================
show parameter cluster_database
Prompt
DOC
################################################################

 If CLUSTER_DATABASE is set to TRUE, change it to FALSE before
 upgrading the database

################################################################
#
Prompt
Prompt ===========================================
Prompt Tablespace and the owner of the aud$ table
Prompt ===========================================
select owner,tablespace_name from dba_extents where segment_name='AUD$' group by owner,tablespace_name;
Prompt
Prompt ============================================================================
Prompt count of records in the sys.aud$ table where dbid is null- Standard Auditing
Prompt ============================================================================
Prompt
set head off
select count(*) as Records  from sys.aud$ where dbid is null;
Prompt
Prompt
Prompt ============================================================================================
Prompt count of records in the system.aud$ when dbid is null, Std Auditing with OLS or DV installed
Prompt ============================================================================================
set head off
select count(*) from system.aud$ where dbid is null;
Prompt
Prompt
Prompt =============================================================================
Prompt count of records in the sys.fga_log$ when dbid is null, Fine Grained Auditing
Prompt =============================================================================
set head off
select count(*) from sys.fga_log$ where dbid is null;
Prompt
Prompt
prompt
Prompt ==========================================
Prompt Oracle Label Security is installed or not
Prompt ==========================================
set head off
SELECT case count(schema)
WHEN 0 THEN 'Oracle Label Security is NOT installed at database level'
ELSE 'Oracle Label Security is installed '
END  "Oracle Label Security Check"
FROM dba_registry
WHERE schema='LBACSYS';
Prompt
Prompt ================
Prompt Number of AQ Records in Message Queue Tables
Prompt ================
Prompt
SET SERVEROUTPUT ON SIZE 100000
declare
   V_COUNT NUMBER;
     cursor c1 is
         select owner,queue_table from dba_queue_tables where owner in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP','WMSYS');
 begin
    for c in c1
     loop
        execute immediate 'select count(1) from ' || c.owner || '.'  || c.queue_table into v_count;
        dbms_output.put_line(c.owner || ' - ' || c.queue_table  || ' - ' || v_count);
     end loop;
 END;
/
Prompt
Prompt ================
Prompt Time Zone version
Prompt ================
Prompt
SELECT version from v$timezone_file;
Prompt
Prompt ================
Prompt Local Listener
Prompt ================
Prompt
select substr(value,1,50) "Local Listener" from v$parameter where name='local_listener';
Prompt
Prompt ================
Prompt Default and Temporary Tablespaces By User
Prompt ================
Prompt
set head on
COLUMN USERNAME FORMAT A28
COLUMN TEMPORARY_TABLESPACE FORMAT A22
COLUMN DEFAULT_TABLESPACE FORMAT A22
SELECT username, temporary_tablespace,default_tablespace FROM DBA_USERS;
Prompt
Prompt
Prompt ================
Prompt Component Status
Prompt ================
Prompt
SET SERVEROUTPUT ON;
DECLARE

ORG_VERSION varchar2(12);
PRV_VERSION varchar2(12);
P_VERSION VARCHAR2(10);

BEGIN

SELECT version INTO p_version
FROM registry$ WHERE cid='CATPROC' ;

IF SUBSTR(p_version,1,5) = '9.2.0' THEN

DBMS_OUTPUT.PUT_LINE(RPAD('Comp ID', 8) ||RPAD('Component',35)||
   RPAD('Status',10) ||RPAD('Version', 15));

DBMS_OUTPUT.PUT_LINE(RPAD(' ',8,'-') ||RPAD(' ',35,'-')||
   RPAD(' ',10,'-') ||RPAD(' ',15,'-'));

FOR x in (SELECT SUBSTR(dr.comp_id,1,8) comp_id,
 SUBSTR(dr.comp_name,1,35) comp_name,
 dr.status Status,SUBSTR(dr.version,1,15) version
 FROM dba_registry dr,registry$ r
 WHERE dr.comp_id=r.cid and dr.comp_name=r.cname
 ORDER BY 1)

LOOP

DBMS_OUTPUT.PUT_LINE(RPAD(SUBSTR(x.comp_id,1,8),8) ||
   RPAD(SUBSTR(x.comp_name,1,35),35)||
   RPAD(x.status,10) || RPAD(x.version, 15));
END LOOP;

ELSIF SUBSTR(p_version,1,5) != '9.2.0' THEN

DBMS_OUTPUT.PUT_LINE(RPAD('Comp ID', 8) ||RPAD('Component',35)|| 
   RPAD('Status',10) ||RPAD('Version', 15)||
   RPAD('Org_Version',15)||RPAD('Prv_Version',15));

DBMS_OUTPUT.PUT_LINE(RPAD(' ',8,'-') ||RPAD(' ',35,'-')||
   RPAD(' ',10,'-')||RPAD(' ',15,'-')||RPAD(' ',15,'-')||
   RPAD(' ',15,'-'));

FOR y in (SELECT SUBSTR(dr.comp_id,1,8) comp_id,
 SUBSTR(dr.comp_name,1,35) comp_name, dr.status Status,
 SUBSTR(dr.version,1,11) version,org_version,prv_version
 FROM dba_registry dr,registry$ r
 WHERE dr.comp_id=r.cid and dr.comp_name=r.cname
 ORDER BY 1)

LOOP

DBMS_OUTPUT.PUT_LINE(RPAD(substr(y.comp_id,1,8), 8) ||
    RPAD(substr(y.comp_name,1,35),35)||RPAD(y.status,10) ||
    RPAD(y.version, 15)||RPAD(y.org_version,15)||RPAD(y.prv_version,15));

END LOOP;

END IF;
END;
/
SET SERVEROUTPUT OFF
Prompt
Prompt
Prompt
Prompt ======================================================
Prompt List of Invalid Database Objects Owned by SYS / SYSTEM
Prompt ======================================================
Prompt
set head on
SELECT case count(object_name)
WHEN 0 THEN 'There are no Invalid Objects'
ELSE 'There are '||count(object_name)||' Invalid objects'
END "Number of Invalid Objects"
FROM dba_objects
WHERE status='INVALID'
AND owner in ('SYS','SYSTEM');
Prompt
DOC
################################################################

 If there are no Invalid objects below will result in zero rows.

################################################################
#
Prompt
set feedback on
SELECT owner,object_name,object_type
FROM dba_objects
WHERE status='INVALID'
AND owner in ('SYS','SYSTEM')
ORDER BY owner,object_type;
set feedback off
Prompt
Prompt ================================
Prompt List of Invalid Database Objects
Prompt ================================
Prompt
set head on
SELECT case count(object_name)
WHEN 0 THEN 'There are no Invalid Objects'
ELSE 'There are '||count(object_name)||' Invalid objects'
END "Number of Invalid Objects"
FROM dba_objects
WHERE status='INVALID'
AND owner  in
('SYSMAN','CTXSYS','ORDSYS','MDSYS','EXFSYS','WKSYS','WKPROXY','WK_TEST','OLAPSYS','OUTLIN','TSMSYS',
'FLOWS_FILES','SI_INFORMATION_SCHMEA','ORACLE_OCM','ORDPLUGINS','DBSNMP');
Prompt
DOC
################################################################

 If there are no Invalid objects below will result in zero rows.

################################################################
#
Prompt
set feedback on
SELECT owner,object_name,object_type
FROM dba_objects
WHERE status='INVALID'
AND owner in ('SYSMAN','CTXSYS','ORDSYS','MDSYS','EXFSYS','WKSYS','WKPROXY','WK_TEST','OLAPSYS','OUTLIN','TSMSYS',
'FLOWS_FILES','SI_INFORMATION_SCHMEA','ORACLE_OCM','ORDPLUGINS','DBSNMP')
ORDER BY owner,object_type;
set feedback off
Prompt
Prompt ======================================================
Prompt Count of Invalids by Schema
Prompt ======================================================
Prompt
select owner,object_type,count(*) from dba_objects where status='INVALID'
group by owner,object_type order by owner,object_type ;
Prompt ==============================================================
Prompt Identifying whether a database was created as 32-bit or 64-bit
Prompt ==============================================================
Prompt
DOC
###########################################################################

 Result referencing the string 'B023' ==> Database was created as 32-bit
 Result referencing the string 'B047' ==> Database was created as 64-bit
 When String results in 'B023' and when upgrading database to 10.2.0.3.0
 (64-bit) , For known issue refer below articles
 
 Note 412271.1 ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While
               Upgrading Or Patching Databases To 10.2.0.3
 Note 579523.1 ORA-600 [22635], ORA-600 [KOKEIIX1], ORA-7445 [KOPESIZ] and
              OCI-21500 [KOXSIHREAD1] Reported While Upgrading To 11.1.0.6

###########################################################################
#
Prompt
SELECT SUBSTR(metadata,109,4) "Metadata",
CASE SUBSTR(metadata,109,4)
WHEN 'B023' THEN 'Database was created as 32-bit'
WHEN 'B047' THEN 'Database was created as 64-bit'
ELSE 'Metadata not Matching'
END "Initial DB Creation Info"
FROM sys.kopm$;
Prompt
Prompt ===================================================
Prompt Number of Duplicate Objects Owned by SYS and SYSTEM
Prompt ===================================================
Prompt
Prompt Counting duplicate objects ....
Prompt
SELECT count(1)
FROM dba_objects
WHERE object_name||object_type in
   (SELECT object_name||object_type 
    from dba_objects
    where owner = 'SYS')
and owner = 'SYSTEM';
Prompt
Prompt =========================================
Prompt Duplicate Objects Owned by SYS and SYSTEM
Prompt =========================================
Prompt
Prompt Querying duplicate objects ....
Prompt
SELECT object_name, object_type, subobject_name, object_id
FROM dba_objects
WHERE object_name||object_type in
   (SELECT object_name||object_type 
    FROM dba_objects
    WHERE owner = 'SYS')
AND owner = 'SYSTEM';
Prompt
DOC

################################################################################

 If any objects found please follow below article.
 Note 1030426.6 How to Clean Up Duplicate Objects Owned by SYS and SYSTEM schema
 Read the Exceptions carefully before taking actions.

################################################################################
#
Prompt
Prompt ========================
Prompt Password protected roles
Prompt ========================
Prompt
DOC

################################################################################

 In version 11.2 password protected roles are no longer enabled by default so if
 an application relies on such roles being enabled by default and no action is
 performed to allow the user to enter the password with the set role command, it
 is recommended to remove the password from those roles (to allow for existing
 privileges to remain available). For more information see:

 Note 745407.1 : What Roles Can Be Set as Default for a User?

################################################################################
#
Prompt
Prompt Querying for password protected roles ....
Prompt
break on "Password protected Role"
select r.ROLE "Password protected Role",
p.grantee "Assigned by default to user"
from dba_roles r, dba_role_privs p
where r.PASSWORD_REQUIRED = 'YES' and p.GRANTED_ROLE = r.role
and p.default_role = 'YES'
and p.grantee <> 'SYS' and r.role not in
(select role from dba_application_roles);

Prompt
Prompt ================
Prompt JVM Verification
Prompt ================
Prompt
SET SERVEROUTPUT ON
DECLARE

V_CT NUMBER;
P_VERSION VARCHAR2(10);

BEGIN

-- If so, get the version of the JAVAM component
EXECUTE IMMEDIATE 'SELECT version FROM registry$ WHERE cid=''JAVAVM''
           AND status <> 99' INTO p_version;

SELECT count(*) INTO v_ct FROM dba_objects
WHERE object_type LIKE '%JAVA%' AND owner='SYS';

IF SUBSTR(p_version,1,5) = '8.1.7' THEN
    IF v_ct>=6787 THEN
    DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly');
    ELSE
    DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly');
    END IF;
ELSIF SUBSTR(p_version,1,5) = '9.0.1' THEN
    IF v_ct>=8585 THEN
    DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly');
    ELSE
    DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly');
    END IF;
ELSIF SUBSTR(p_version,1,5) = '9.2.0' THEN
    IF v_ct>=8585 THEN
    DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly');
    ELSE
    DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly');
    END IF;
ELSIF SUBSTR(p_version,1,6) = '10.1.0' THEN
    IF v_ct>=13866 THEN
    DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly');
    ELSE
    DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly');
    END IF;
ELSIF SUBSTR(p_version,1,6) = '10.2.0' THEN
    IF v_ct>=14113 THEN
    DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly');
    ELSE
    DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly');
    END IF;
END IF;

EXCEPTION WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('JAVAVM - NOT Installed. Below results can be ignored');

END;
/
SET SERVEROUTPUT OFF
Prompt
Prompt ================================================
Prompt Checking Existence of Java-Based Users and Roles
Prompt ================================================
Prompt
DOC

################################################################################

 There should not be any Java Based users for database version 9.0.1 and above.
 If any users found, it is faulty JVM.

################################################################################
#

Prompt
SELECT CASE count(username)
WHEN 0 THEN 'No Java Based Users'
ELSE 'There are '||count(*)||' JAVA based users'
END "User Existence"
FROM dba_users WHERE username LIKE '%AURORA%' AND username LIKE '%OSE%';
Prompt
DOC

###############################################################

 Healthy JVM Should contain Six Roles.
 If there are more or less than six role, JVM is inconsistent.

###############################################################
#

Prompt
SELECT CASE count(role)
WHEN 0 THEN 'No JAVA related Roles'
ELSE 'There are '||count(role)||' JAVA related roles'
END "Role"
FROM dba_roles
WHERE role LIKE '%JAVA%';
Prompt
Prompt Roles
Prompt
SELECT role FROM dba_roles WHERE role LIKE '%JAVA%';
set head off
Prompt
Prompt =========================================
Prompt List of Invalid Java Objects owned by SYS
Prompt =========================================
SELECT CASE count(*)
       WHEN 0 THEN 'There are no SYS owned invalid JAVA objects'
       ELSE 'There are '||count(*)||' SYS owned invalid JAVA objects'
       END "Total Invalid JAVA objects"
FROM dba_objects
WHERE object_type LIKE '%JAVA%'
AND status='INVALID'
AND owner='SYS';
Prompt
DOC

#################################################################

 Check the status of the main JVM interface packages DBMS_JAVA
 and INITJVMAUX and make sure it is VALID.

 If there are no Invalid objects below will result in zero rows.

#################################################################
#
Prompt
set feedback on
SELECT owner,object_name,object_type
FROM dba_objects
WHERE object_type LIKE '%JAVA%'
AND status='INVALID'
AND owner='SYS';
set feedback off
Prompt
DOC

#################################################################

 If the JAVAVM component is not installed in the database (for
 example, after creating the database with custom scripts), the
 next query will report the following error:

   select dbms_java.longname('foo') "JAVAVM TESTING" from dual
   *
   ERROR at line 1:
   ORA-00904: "DBMS_JAVA"."LONGNAME": invalid identifier

 If the JAVAVM component is installed, the query should succeed
 with 'foo' as result.

#################################################################
#
Prompt
set heading on
select dbms_java.longname('foo') "JAVAVM TESTING" from dual;
set heading off
Prompt

set feedback off head off
select LPAD('*** End of LogFile ***',50) from dual;
set feedback on head on
Prompt
spool off
Prompt
set heading off
set heading off
set feedback off
select 'Upload db_upg_diag_&&dbname&&timestamp&&suffix from "&log_path" directory'
from dual;
set heading on
set feedback on
Prompt
-- - - - - - - - - - - - - - - - Script ends here - - - - - - - - - - - - - -

How to Clean Up Duplicate Objects Owned by SYS and SYSTEM Schema (Doc ID 1030426.6)

Purpose

Problem Description:

If the same data dictionary object has been created under both user SYS and SYSTEM schema then errors will often occur when trying to use the database features associated with these objects.


Problem Explanation:

During the installation of Oracle many scripts are run which create the underlying data dictionary objects. Most of these scripts are run at database creation time, but others are only run if specific database features (such as replication or shared pool management) are needed. These scripts are usually run manually after the database has been created.

Running SQL scripts manually increases the chance of error greatly. One such common problem is running the SQL script as the wrong Oracle user.

Most SQL scripts located in the $ORACLE_HOME/rdbms/admin directory should be run as SYS (or internal) and not SYSTEM.

If you happen to run a SQL as the wrong user it is very hard to clean up from this situation as the number of objects that a script creates can be very large as well as there are no delivered scripts to drop the incorrect objects.

Details

In order to clean up the duplicate objects you need to issue a SQL script to find out the names of the duplicate objects.
You can then manually drop the objects or use a 'SQL generating SQL' script to generate a list of drop commands. 

Below is a SQL*Plus script that will list all objects that have been created in both the SYS and SYSTEM schema:

column object_name format a30
select object_name, object_type
from dba_objects
where object_name||object_type in
   (select object_name||object_type
    from dba_objects
    where owner = 'SYS')
and owner = 'SYSTEM';


The output from this script will either be 'zero rows selected' or will look something like the following:
OBJECT_NAME                OBJECT_TYPE
------------------------------         -------------
ALL_DAYS                           VIEW
CHAINED_ROWS               TABLE
COLLECTION                       TABLE
COLLECTION_ID                 SEQUENCE
DBA_LOCKS                       SYNONYM
DBMS_DDL                        PACKAGE
DBMS_SESSION                PACKAGE
DBMS_SPACE                   PACKAGE
DBMS_SYSTEM                PACKAGE
DBMS_TRANSACTION  PACKAGE
DBMS_UTILITY                PACKAGE


If the select statement returns any rows then this is an indication that at least 1 script has been run as both SYS and SYSTEM.

Since most data dictionary objects should be owned by SYS (see exceptions below) you will want to drop the objects that are owned by SYSTEM in order to clear up this situation.


EXCEPTION TO THE RULE

THE REPLICATION SCRIPTS (XXX) CORRECTLY CREATES OBJECTS WITH THE SAME NAME IN THE SYS AND SYSTEM ACCOUNTS. LISTED BELOW ARE THE OBJECTS USED BY REPLICATION THAT SHOULD BE CREATED IN BOTH ACCOUNTS. DO NOT DROP THESE OBJECTS FROM THE SYSTEM ACCOUNT IF YOU ARE USING REPLICATION. DOING SO WILL CAUSE REPLICATION TO FAIL!

The following objects are duplicates that will show up (and should not be removed) when running this script in 8.1.x and higher.

Without replication installed:
INDEX           AQ$_SCHEDULES_PRIMARY
TABLE           AQ$_SCHEDULES

If replication is installed by running catrep.sql:
INDEX           AQ$_SCHEDULES_PRIMARY
PACKAGE         DBMS_REPCAT_AUTH
PACKAGE BODY    DBMS_REPCAT_AUTH
TABLE           AQ$_SCHEDULES

When database is upgraded to 11g using DBUA, following duplicate objects are also created
OBJECT_NAME                OBJECT_TYPE
------------------------------ -------------
Help                          TABLE
Help_Topic_Seq                  Index

The objects created by sqlplus/admin/help/hlpbld.sql must be owned by SYSTEM because when sqlplus retrieves the help information, it refers to the SYSTEM schema only. DBCA runs this script as SYSTEM user when it creates the database but DBUA runs this script as SYS user when upgrading the database (reported as an unpublished BUG 10022360).  You can drop the ones in SYS schema.

Now that you have a list of duplicate objects you will simply issue the appropriate DROP command to get rid of the object that is owned by the SYSTEM user.


If the list of objects is large then you may want to use the following SQL*Plus script to automatically generate an SQL script that contains the appropriate DROP commands:

set pause off
set heading off
set pagesize 0
set feedback off
set verify off
spool dropsys.sql
select 'DROP ' || object_type || ' SYSTEM.' || object_name || ';'
from dba_objects
where object_name||object_type in
   (select object_name||object_type
    from dba_objects
    where owner = 'SYS')
and owner = 'SYSTEM';
spool off
exit


You will now have a file in the current directory named dropsys.sql that contains all of the DROP commands. You will need to run this script as a normal SQL script as follows:

$ sqlplus
SQL*Plus: Release 3.3.2.0.0 - Production on Thu May  1 14:54:20 1997
Copyright (c) Oracle Corporation 1979, 1994.  All rights reserved.
Enter user-name: system
Enter password: manager
SQL&gt; @dropsys

Note: You may receive one or more of the following errors:
ORA-2266 (unique/primary keys in table referenced by enabled foreign keys):
If you encounter this error then some of the tables you are dropping have constrints that prevent the table from being dropped. To fix this problem you will have to manually drop the objects in a different order than the script does.
    
ORA-2429 (cannot drop index used for enforcement of unique/primary key):
 This is similar to the ORA-2266 error except that it points to an index. You will have to manually disable the constraint associated with the index and then drop the index.

ORA-1418 (specified index does not exist):
 This occurs because the table that the index was created on  has already been dropped which also drops the index. When the script tries to drop the index it is no longer there and thus the ORA-1418 error. You can safely ignore this error.