Simple schema compare scripts

Assuming connected to "test" database and want to compare to "product" via database link "prod".


Object difference:

select p.object_name prod_obj_name, p.object_type prod_obj_type, t.object_name test_obj_name, t.object_type test_obj_type
from user_objects@prod p
full outer join user_objects t
on p.object_name=t.object_name
and t.object_type=p.object_type
order by decode(p.object_name,NULL, 0, 1), decode(t.object_name,NULL, 0, 1),
p.object_name;

The results are shown in 3 sections: NULL first 2 columns - objects exist in "test" not in "prod"; NULL last 2 columns - objects exist in "prod" not in "test"; no NULL columns - objects exist in both. "Where" clause can be added to show only the difference instead of thousands of identical objects.


Table column difference:

WITH X as (select p.table_name, p.column_name
from user_tab_columns@prod p
join user_tables tt
on tt.table_name=p.table_name
),
Y as
(select t.table_name, t.column_name
from user_tab_columns t
join user_tables@prod pt
on pt.table_name=t.table_name
)
select x.table_name prod_table_name, x.column_name prod_column_name, y.table_name test_table_name, y.column_name test_column_name
from x full outer join y
on x.table_name=y.table_name
and x.column_name=y.column_name
where x.table_name is null or y.table_name is null
;


Similar to object difference, but it has to be done in a more complex query.

Index columns or even procedures etc can also be checked similarly if needed.

Rollback when rasise application error?

Do we need to explicitly call rollback when we call RAISE_APPLICATION_ERROR? The answer is: Maybe.

Test case:

CREATE TABLE A (COL1 VARCHAR2(10));

INSERT INTO A VALUES ('1');

begin
    insert into a values('2');
    RAISE_APPLICATION_ERROR(-20005,'ERROR: ');
end;
/

begin
*
ERROR at line 1:
ORA-20005: ERROR:
ORA-06512: at line 4

SQL> select * from a;

COL1
----------
1
 

If we try rollback manually:

begin
    insert into a values('2');
    rollback;
    RAISE_APPLICATION_ERROR(-20005,'ERROR: ');
end;
/
 

begin
*
ERROR at line 1:
ORA-20005: ERROR:
ORA-06512: at line 4
 

SQL> select * from a;
no rows selected
 

Conclusion: RAISE_APPLICATION_ERROR implicits an rollback for the PL/SQL block ONLY. If you want to rollback the whole transaction, you still need to issue rollback explicitly.

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>