Weird phantom object... Can't create, but can't drop neither

After doing a full database export/import, it left me a "phantom" object for the public synonym in sys.obj$ in the target database, but nothing shows in dba_objects for that synonym. As a result, I can neither create such synonym (got ORA-00995: missing or invalid synonym identifier), nor dropping it (got ORA-01432: public synonym to be dropped does not exist). Eventually compiling the base object removed the row from sys.obj$.

But it's still kind of a mystery. According to this document (Public Synonym ST_GEOMETRY For SDE.ST_GEOMETRY Removed After Upgrade To 10.2.0.4 (Doc ID 577529.1)), these public synonyms shouldn't be exist even in 10g databases. But my source database is 11.1 and target database is 11.2.0.3.


SQL> CREATE PUBLIC SYNONYM ST_MULTISURFACE for sde.ST_MULTISURFACE
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00995: missing or invalid synonym identifier
ORA-06512: at line 48

SQL> drop public synonym ST_MULTISURFACE;
drop public synonym ST_MULTISURFACE
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> select obj#, owner#, name, type# from sys.obj$ where
name='ST_MULTISURFACE';

OBJ# OWNER# NAME TYPE#
---------- ---------- ------------------------------ ----------
92147 0 ST_MULTISURFACE 10
63690 57 ST_MULTISURFACE 13
64338 57 ST_MULTISURFACE 14
79560 87 ST_MULTISURFACE 13
89355 87 ST_MULTISURFACE 14

Note that dba_objects only returuns 4 rows, instead of 5.

SQL> select owner,object_name from dba_objects where object_name='ST_MULTISURFACE'

OWNER OBJECT_NAME
------------------------------ ------------------------------
MDSYS ST_MULTISURFACE
MDSYS ST_MULTISURFACE
SDE ST_MULTISURFACE
SDE ST_MULTISURFACE

Best practice of full database export/import (data pump)

Export/import, data pump or regular, is always my least favorite and last "choice" for database migration. I don't blame Oracle too much on this, as it's logical extract of the full database so there are so many things could go wrong, plus there are as many variables on the destination database as well.

Recently, I had to do this full database export/import as database migration because Oracle can't handle data files containing undo segments such as system for migrations between different Endian operation systems (http://tomasks.blogspot.ca/2012/10/oracle-cross-platform-migration-not.html).


Here are some of the objects I found went missing after full database export/import:

1. SYS/System object grants
Because we usually exclude SYS/SYSTEM and other system schema for full database export/import, the grants with those objects will not be exported. You can't blame Oracle for this one though. Generally run a statement similar to this on the original database to get a list of statements to be run in the destination database after the import - change the query criteria to suit your database:

select 'GRANT '||privilege||' on '||owner||'.'||table_name||' to '||grantee||';'
from dba_tab_privs
where grantee not in ('SQLTXPLAIN', 'PUBLIC','SYS', 'SYSTEM', 'OUTLN', 'MDSYS', 'ORDSYS', 'DBSNMP', 'WMSYS', 'APPQOSSYS', 'ORDDATA', 'CTXSYS', 'ANONYMOUS', 'XDB', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA', 'ORACLE_OCM', 'XS$NULL', 'MDDATA', 'DIP', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR')
and owner in ('SYS','SYSTEM')
and grantee not in (select role from dba_roles)
order by grantee
;

2. Materialized views if the "remote host" is not accessible on the target database. So it's better to make sure all TNSNAMES required by materialized views are valid on the target database before import. Otherwise, you need to check the log file and recreate the materialized views later.


CREATE MATERIALIZED VIEW ......
ORA-39083: Object type MATERIALIZED_VIEW failed to create with error:
ORA-04052: error occurred when looking up remote object xxxxxx
ORA-00604: error occurred at recursive SQL level 4
ORA-12154: TNS:could not resolve the connect identifier specified

3. Synonyms for SYS/SYSTEM etc, which were excluded from export/import. This is similar to item 1.

select 'create synonym '||owner||'.'||synonym_name||' for '||table_owner||'.'||table_name||';'
from dba_synonyms where table_owner in ('SYS','SYSTEM','CTXSYS','DBSNMP','MDSYS','XDB','ORDSYS','WMSYS','SQLTXPLAIN')
and owner not in ('PUBLIC','SYS','SYSTEM','CTXSYS','DBSNMP','MDSYS','XDB','ORDSYS','WMSYS','SQLTXPLAIN')
;

4. Materialized views and potentially other objects, if the object's DDL can't be extracted by DBMS_METADATA for whatever reason. I spent quite some time to figure this out as I found missing materialized views but found no error messages regarding them in the export and import log files.

DBMS_METADATA complains:
“-- Unable to render MATERIALIZED VIEW DDL for object xxx.xxxxxx with DBMS_METADATA”

5. Public synonyms. This is probably an extreme case related to Oracle Spatial. There are still some mysteries but I just don't want to pursue further after spending lots of time with Oracle Support. The full database export/import left me a "phantom" object for the public synonym in sys.obj$ in the target database, but nothing shows in dba_objects for that synonym. As a result, I can neither create such synonym, nor dropping it. Eventually compiling the base object removed the row from sys.obj$. But it's still kind of a mystery. Details here: http://lazydbashop.blogspot.ca/2012/11/weird-phantom-object-cant-create-but.html


So, as my best practice, what I do to make sure there's no missing objects, is to bring data in dba_objects from the original database to the target database, renamed to orig_dba_objects, then run this query after doing the import:

select owner,object_name,object_type from sys.orig_objects
where owner not in ('SYS','SYSTEM','CTXSYS','DBSNMP','MDSYS','XDB','ORDSYS','WMSYS')
and object_name not like 'SYS%'
minus
select owner,object_name,object_type from dba_objects;

I also use it to make sure there's no "new" invalid objects in the target database. One thing need to be noted is, sometimes the "valid" status of objects in the original database could be fake. For example, a materialized view won't become invalid if the "host" information changed like tnsnames changed or the target database not available etc. So generate a compile script for all the "new" invalid objects in the target database, then run it against original database to make sure they are really "valid" in the first place.

Scripts to check Oracle Options used in database

Finally, Oracle is (trying to) make licensing (a little bit) easier for DBAs alike, with this recent document that allows us to check which options/packs are used for databases:


http://docs.oracle.com/cd/E11882_01/license.112/e10594.pdf

There are two scripts to use to get an idea of what options are being used in the database, available at 

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=1317265.1

But even this one is still not officially official, it still has disclaimer saying:

“Kindly note the report generated is to be used for informational purposes only and this does not represent your license entitlement or requirement. for known issues with this check MOS DOC ID 1309070.1”

 



It says for 11.2 only, but as dba_feature_usage_statistics has been available since 10g, so you still can use this script to get a basic idea.

If you have no access to Oracle Support, basically, you need to create this table to match name column of dba_feature_usage_statistics to an option.

OPTIONS	NAME
Active Data Guard	Active Data Guard - Real-Time Query on Physical Standby
Advanced Compression	HeapCompression
Advanced Compression	Backup BZIP2 Compression
Advanced Compression	Backup DEFAULT Compression
Advanced Compression	Backup HIGH Compression
Advanced Compression	Backup LOW Compression
Advanced Compression	Backup MEDIUM Compression
Advanced Compression	Backup ZLIB, Compression
Advanced Compression	SecureFile Compression (user)
Advanced Compression	SecureFile Deduplication (user)
Advanced Compression	Data Guard
Advanced Compression	Oracle Utility Datapump (Export)
Advanced Compression	Oracle Utility Datapump (Import)
Advanced Security	ASO native encryption and checksumming
Advanced Security	Transparent Data Encryption
Advanced Security	Encrypted Tablespaces
Advanced Security	Backup Encryption
Advanced Security	SecureFile Encryption (user)
Change Management Pack (GC)	Change Management Pack (GC)
Data Masking Pack	Data Masking Pack (GC)
Data Mining	Data Mining
Diagnostic Pack	Diagnostic Pack
Diagnostic Pack	ADDM
Diagnostic Pack	AWR Baseline
Diagnostic Pack	AWR Baseline Template
Diagnostic Pack	AWR Report
Diagnostic Pack	Baseline Adaptive Thresholds
Diagnostic Pack	Baseline Static Computations
Tuning Pack	Tuning Pack
Tuning Pack	Real-Time SQL Monitoring
Tuning Pack	SQL Tuning Advisor
Tuning Pack	SQL Access Advisor
Tuning Pack	SQL Profile
Tuning Pack	Automatic SQL Tuning Advisor
Database Vault	Oracle Database Vault
WebLogic Server Management Pack Enterprise Edition	EM AS Provisioning and Patch Automation (GC)
Configuration Management Pack for Oracle Database	EM Config Management Pack (GC)
Provisioning and Patch Automation Pack for Database	EM Database Provisioning and Patch Automation (GC)
Provisioning and Patch Automation Pack	EM Standalone Provisioning and Patch Automation Pack (GC)
Exadata	Exadata
Label Security	Label Security
OLAP	OLAP - Analytic Workspaces
Partitioning	Partitioning (user)
Real Application Clusters	Real Application Clusters (RAC)
Real Application Testing	Database Replay: Workload Capture
Real Application Testing	Database Replay: Workload Replay
Real Application Testing	SQL Performance Analyzer
Spatial	Spatial (Not used because this does not differential usage of spatial over locator, which is free)
Total Recall	Flashback Data Archive

Oracle Enterprise Manager Licensing information

When it comes to licensing, Oracle has made it as complex and confusing as it could ever be!

Fortunately, Oracle made a little progress with OEM licensing with this document:


http://docs.oracle.com/cd/E24628_01/license.121/e24474.pdf


Finally, it's easier to identify and enable/disable Management Packs for OEM now:

“Beginning with Oracle Database 11g, the CONTROL_MANAGEMENT_PACK_ACCESS

initialization parameter controls access to the Diagnostic Pack and Tuning Pack. You

can set this parameter to one of the following values:

■ DIAGNOSTIC+TUNING — Diagnostic Pack and Tuning Pack functionally is

enabled in the database server.

■ DIAGNOSTIC — Only Diagnostic Pack functionality is enabled in the server.

■ NONE — Diagnostic Pack and Tuning Pack functionally is disabled in the database

server.“

“To determine which links are part of the Diagnostics Pack:

■ Click Setup (upper right corner of the page), then Management Pack Access.

■ On the Management Pack Access page, you can grant and remove access for all

management packs.

– For Enterprise Manager Database Control, remove access for the Database

Diagnostics Pack and click Apply.

– For Enterprise Manager Cloud Control, remove access for the Database

Diagnostics Pack and click Apply.

This disables all links and menu items associated with the Diagnostics Pack in

Enterprise Manager. All disabled links and menu are part of the Diagnostics Pack and

therefore require a pack license.”

Using perl modules without root privilege - local libraries

http://stackoverflow.com/questions/2980297/how-can-i-use-cpan-as-a-non-root-user

wget -O- http://cpanmin.us | perl - -l ~/perl5 App::cpanminus local::lib

echo 'eval `perl -I ~/perl5/lib/perl5 -Mlocal::lib`' >> .bash_profile

cpanm Module::<module>

How to test any database link for other schema or the whole database?

Even if you have "create any" and "select any" privileges, you still can't directly test or create database links owned by other schemas.
  

SQL> select * from dual@SCHEMA.DBLINK;
select * from dual@SCHEMA.DBLINK
                   *
ERROR at line 1:
ORA-02019: connection description for remote database not found

But, there are ways to get around it. 

Method 1: 

By creating an object within the schema like this:

  

SQL> create view SCHEMA.for_dblink_test_only as select * from dual@SCHEMA.DBLINK;

View created. 

If the database link doesn't work, we'll get the error message, in this example, the database link was pointing to the "old" server:
  

SQL> create view SCHEMA.for_dblink_test_only as select * from dual@SCHEMA.DBLINK_NOWORKING;

ERROR at line 1:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor 

So, we can generate a script to test all database links:

select 'CREATE VIEW '||owner||'.for_dblink_test_only as select * from dual@'||db_link||';',
'DROP VIEW '||owner||'.for_dblink_test_only;'
from dba_db_links where username is not null;

Or even better, build a procedure to do so.

Method 2: 

If you can get execute privilege on sys.dbms_sys_sql, you can also do it without creating any object:

create procedure execute_sql( p_cmd varchar2, p_user varchar2 )
authid current_user
is
    l_user_id number;
    l_cursor  number;
    l_result  number;
begin
    -- fetch userid
    select user_id into l_user_id
      from dba_users
     where username = p_user;
    -- parse and execute
    l_cursor := sys.dbms_sys_sql.open_cursor;
    sys.dbms_sys_sql.parse_as_user( l_cursor, p_cmd, dbms_sql.native, l_user_id, true );
    l_result := sys.dbms_sys_sql.execute( l_cursor );
    sys.dbms_sys_sql.close_cursor( l_cursor );
end;
/
 

If the database link doesn't work, you'll get:



execute execute_sql('select * from dual@db_link','SCHEMA');


ERROR at line 1:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1306
ORA-06512: at "SYS.EXECUTE_SQL", line 14
ORA-06512: at line 1

If the database link works, you'll get:

execute execute_sql('select * from dual@db_link','SCHEMA');


PL/SQL procedure successfully completed.

Network timeout causing ORA-03113: end-of-file on communication channel

If you have this error from your client connections due to network timeout, try to set



SQLNET.EXPIRE_TIME=10

in sqlnet.ora

Basically it "pings" client every 10 minutes, that small packet resets the network timeout clock.

Here's what Oracle says about this parameter:

http://docs.oracle.com/cd/B28359_01/network.111/b28317/sqlnet.htm#BIIEEGJH

5.2.28 SQLNET.EXPIRE_TIME

Purpose

Use parameter SQLNET.EXPIRE_TIME to specify a time interval, in minutes, to send a probe to verify that client/server connections are active. Setting a value greater than 0 ensures that connections are not left open indefinitely, due to an abnormal client termination. If the probe finds a terminated connection, or a connection that is no longer in use, it returns an error, causing the server process to exit. This parameter is primarily intended for the database server, which typically handles multiple connections at any one time.

Limitations on using this terminated connection detection feature are:

• It is not allowed on bequeathed connections.
• Though very small, a probe packet generates additional traffic that may downgrade network performance.
• Depending on which operating system is in use, the server may need to perform additional processing to distinguish the connection probing event from other events that occur. This can also result in degraded network performance.

Default

0

Minimum Value

0

Recommended Value

10

Example

SQLNET.EXPIRE_TIME=10