How to compare the 2 schemas table structures and refresh packages/procedures/functions from one schema to another

Jephe wu - http://linuxtechres.blogspot.com

Objective: to compare the 2 schemas to make sure the database structures are same. The data inside the tables might not be the same, but the table columns and data type must be the same. Also, all the programs such as packages/procedures/functions etc must be the same.

Environment
: Oracle 11g 64bit on RHEL 5


Steps:


1. exporting the packages/functions/procedures from jephe1 schema
You can use sql developer database export function (tick terminator, pretty print, and include drop statement) to export them from schema jephe1 then use 'run script(F5)' to run in another schema(jephe2).
But I have encountered some issues such as the sql developer doesn't seem to understand the following multiple lines comments:
/***************
....
...
****************/


and if I use sqlplus, I encountered error like this:

SP2-0027: Input is too long (> 2499 characters) - line ignored
=> solution to this
=>

Open the file in a text editor and then resave the file as a different file 
type.  In Microsoft Word, you click on File, Save As, then select file type 
'text only with line breaks' and save the file.  Once this is done, you can 
successfully run the script from SQL*Plus or Server Manager.

In this article, I am using expdp/impdp tool to sync them.

$ more jephe1.par
directory=oracle
schemas=jephe1
dumpfile=jephe1.packages.dumpdp
logfile=jephe1.packages.logdp
include=PACKAGE
include=PROCEDURE
include=FUNCTION



 $ sqlplus / as sysdba
create [ or replace ] directory oracle as '/home/oracle’
grant read,write on directory oracle to system
select * from dba_directories


$ expdp system parfile=jephe1.par


2. drop all packages/procedures/functions in jephe2 schema
We need to drop all of them before using impdp to import. You can use SQL developer 'database export' function to generate the drop statements, or use the following sql statements to generate it through sqlplus command line.

$ more generate_drop_obj.sql
set pagesize 0 feedback off
spool drop_obj.sql
select 'drop '||object_type||' '||object_name||';' from user_objects where object_type in ('PACKAGE','PROCEDURE','FUNCTION','VIEW') and object_name not like 'FB_%' order by object_type,object_name;
exit;
spool off;


$ sqlplus jephe1
@generate_drop_obj.sql
@drop_obj.sql


After that, you can use sql developer to check jephe2 schema to make sure all procedures/packages/functions are dropped.

3. importing the expdp dump file to jephe2 schema

$ impdp system directory=oracle dumpfile=jephe1.packages.dumpdp logfile=jephe2.packages.logdp remap_schema=jephe1:jephe2 remap_tablespace=jephe1:jephe2 transform=oid:n

4. compare table structures between jephe1 and jephe2.

$ more comparetables.sql
Set head off feedback off echo off term off pagesize 0  linesize 32767 trimsp on tab off;
Spool /tmp/jephe1;
Select table_name||','||column_name||','||data_type||','||char_length||','||data_precision||','||data_scale from user_tab_columns order by table_name||','||column_name;
Spool off;
exit;


$ sqlplus jephe1
@comparetables.sql


change spool /tmp/jephe1 line to spool /tmp/jephe2, then run it again, after that, use diff to compare:

$ diff -c /tmp/jephe1 /tmp/jephe2


5. compile the whole schema for jephe2

$ sqlplus / as sysdba
exec dbms_utility.compile_schema('JEPHE2');

Duplicate a db2 schema to another for a new client

Jephe Wu -  http://linuxtechres.blogspot.com

Objective: duplicate a db2 schema to another for a new client

Environment: RHEL 5 server, IBM db2 V9, duplicate from existing client user1 to user2

# db2level
DB21085I  Instance "db2inst1" uses "32" bits and DB2 code release "SQL09013"
with level identifier "01040107".
Informational tokens are "DB2 v9.1.0.3", "s070719", "MI00202", and Fix Pack
"3".
Product is installed at "/opt/ibm/db2/V9.1".


Concept:
create OS user and use db2look to duplicate all table structures to another schema

Steps:
1. Create OS user user2

login as root, run commands below:
useradd -c 'DB2 account for user2' -m user2
passwd user2
chage user2 (to change maximum password expiry to 99999 if you have defined default expiry days)
su - db2inst1
cd /db2/db2inst1/db/DB1  (go to the place where all the tablespace directory resides if any)
mkdir tb_user2


2. use db2 control center or command line to create tablespace for user2

preparing the following content for file create_tablespace.sql
# more create_tablespace.sql
--please login as root to create user and assign password first before creating tablespace
CONNECT TO DB1;
CREATE  REGULAR  TABLESPACE TB_USER2 PAGESIZE 16 K  MANAGED BY SYSTEM  USING ('/db2/db2inst1/db/DB1/tb_user2' ) EXTENTSIZE 8 OVERHEAD 10.67 PREFETCHSIZE 8 TRANSFERRATE 0.04 BUFFERPOOL  IBMDEFAULTBP  DROPPED TABLE RECOVERY ON;
GRANT  CREATETAB,CONNECT,IMPLICIT_SCHEMA ON DATABASE  TO USER user2;
GRANT USE OF TABLESPACE TB_USER2 TO USER user2;
CONNECT RESET;

su - db2inst1
db2 -tvf create_tablespace.sql -l create_tablespace.sql.log -s

3. use db2look to duplicate schema objects structures
db2look -d db1 -e -z user1 > user1.sql
vi user1.sql
%s#USER1#USER2#g

cat user1.sql | grep -i tb_ | grep -v tb_user1 (to check any other tablespace the user1 objects is residing in)

vi user1.sql
search 'DDL Statements for foreign keys' to separate file into 2 files as user1-1.sql and user1-2.sql so that you can import data into all tables without constrains, that will be faster if you can make sure all the data is in line with constrain already.

4. user db2 control center to give permission for tables and views for user2
open db2 control center - all databases, locate the db1, go to 'User and Group Objects' - 'DB Users', right click on user 'user2' - change,  go to 'table' column to add all tables for user2 schema then give 'yes' to all privileges except for 'control'. As well as views etc

changing Oracle global schema name and re-generate public synonyms

Jephe Wu - http://linuxtechres.blogspot.com

Environment:  global schema contains some database objects such as tables, procedures, packages etc which are shared by each individual client schemas, and now we are going to change global schema name from global1 to global2.

We are using public synonyms for global schema so that each individual client schema can directly use the global schema objects name.

Objective: changing global schema name from global1 to global2 and repointing all public synonyms from global1 to global2.


Steps:
1.  check with another article regarding how to duplicate a schema at
http://linuxtechres.blogspot.com/2009/12/how-to-copy-whole-schema-from-one.html

2. generating the existing public synonyms that are related to the schema global1

cat > /u01/cronjob/public_synonym_generation.sql << END
set head off feedback off echo off term off pagesize 0 linesize 32767 trimsp on tab off;
spool /u01/cronjob/public_synonym.sql;
select 'create or replace public synonym '||synonym_name||' for '||table_owner||'.'||table_name||';' from dba_synonyms where table_owner in ('GLOBAL1') and owner='PUBLIC';
spool off;
exit;
END
sqlplus / as sysdba @/u01/cronjob/public_synonym_generation.sql 

after that, vi /u01/cronjob/public_synonym.sql, to change global1 to global2 (:%s#GLOBAL1\.#GLOBAL2\.#g)

sqlplus / as sysdba @/u01/cronjob/public_synonym.sql

You can also use the following script to generate all public synonyms for global schema after schema copy

cat > /u01/cronjob/public_synonym_generation.sql << END
set head off feedback off echo off term off pagesize 0 linesize 32767 trimsp on tab off;
spool /u01/cronjob/public_synonym.sql;
select 'create or replace public synonym '||object_name||' for '||owner||'.'||object_name||';' from dba_objects where owner in
 ('GLOBAL2') and object_type in ('TABLE','PROCEDURE','FUNCTION','VIEW','PACKAGE','SEQUENCE');
select 'exit;' from dual;
spool off;
exit;
END

3. re-generating grant scripts from the global schema
cat > /u01/cronjob/grant_execute_generation.sql << END
set head off feedback off echo off term off pagesize 0 linesize 32767 trimsp on tab off;
spool /u01/cronjob/grant_execute.sql;
select 'grant execute on '||object_name||' to public;' from dba_objects where owner in ('GLOBAL1') and object_type in ('PROCEDURE ','PACKAGE','FUNCTION');
select 'exit;' from dual;
spool off;
exit;
END

sqlplus / as sysdba @/u01/cronjob/grant_execute_generation.sql
sqlplus / as sysdba @/u01/cronjob/grant_execute.sql

cat > /u01/cronjob/grant_all_generation.sql << END
set head off feedback off echo off term off pagesize 0 linesize 32767 trimsp on tab off;
spool /u01/cronjob/grant_all.sql;
select 'grant all on '||object_name||' to public;' from dba_objects where owner in ('GLOBAL1') and object_type in ('TABLE','VIEW' ,'SEQUENCE');
select 'exit;' from dual;
spool off;
exit;
END

sqlplus / as sysdba @/u01/cronjob/grant_all_generation.sql
sqlplus / as sysdba @/u01/cronjob/grant_all.sql




4. drop global1 schema
drop tablespace global1 including contents and datafiles;
drop user global1 cascade;


5. drop client schema synonyms that are related to global1 schema (otherwise, it will take priority so the same name of public synonyms will still be pointing to old schema global1)


set head off feedback off echo off term off pagesize 0 linesize 32767 trimsp on tab off;
spool /u01/cronjob/drop_user_synonyms.sql;
select 'drop synonym '||synonym_name||';' from user_synonyms where table_owner in ('GLOBAL1');
select 'exit;' from dual;
spool off;
exit;


note:
If you encountered error "ORA-00980:synonym translation is no longer valid" after re-pointing all public synonyms to the new global schema. You should check your each client schema synonyms, there might be some synonyms in client schema itself pointing to old global schema global1.


use gmail and thunderbird to overcome company mailbox quota issue

Jephe Wu  - http://linuxtechres.blogspot.com

Objective: due to small mailbox quota of company mailbox, so I'd like to use my gmail mailbox to store all my company emails also, whenever someone send me an email at gmail address or company email address, reply from the same address the message was sent to, from both browser and thunderbird.

We can import company ldap address book into google contacts and configure the local and google calendar in thunderbird.

Environment: gmail account, company users can set email forwarding by themselves, Thunderbird

Concepts:
I will configure my company email address jephe.wu@domain.com to forward to jephewu at gmail.com through whatever web interface provided by company. Then I can use web browser or thunderbird mail client to access both my gmail or company email.


Steps:
1. Enable IMAP access in your gmail account
You can login your gmail account from browser, go to account setting to enable IMAP access so that we can use thunderbird to access gmail later, search 'gmail imap' in google to access gmail help page at http://mail.google.com/support/bin/answer.py?hl=en&answer=75725  (Getting started with IMAP for Gmail)

2. configure your company mailbox forwarding function to forward jephe.wu@domain.com to jephewu at gmail.com.

3. Adding an external email account in gmail (setting - Accounts and Import) and set the company email as default, also, choose 'when receiving a message: Reply from the same address the message was sent to'. You can choose company email as default as most of time you will receive email from that address.

You can configure to send email through your company smtp server when replying those messages sent to your comany email address if your company smtp server has smtp auth enabled, otherwise, send it through google always.

4. Now when you use web browser to reply, it will reply from the same address the message was sent to.
You might need to import your company ldap address book into google contacts so that you can use it to compose email.  search 'gmail help' in google, go to gmail help page, search 'import csv files' to check how to import csv files to google contacts at http://mail.google.com/support/bin/answer.py?hl=en&answer=14024

5. Install Thunderbird, the latest version can automatically detect the gmail smtp/imap server name and configure it automatically. You will configure both your company address and gmail address.

6. In order to reply from the same the message was sent to in thunderbird, you can search 'thunderbird multiple accounts' in google, check the page at http://www.askdavetaylor.com/how_do_i_create_multiple_accounts_in_mozilla_thunderbird.html

7. You can now configure ldap address book in thunderbird.

8. You can also install lightning  - a integrated calendar for thunderbird 
You can also install 'provider for google calendar' to allow bidirectional access to Google calendar, please check howto at http://bfish.xaedalus.net/2007/04/stay-in-sync-with-gcal-and-thunderbird/

FAQ.

a. testing your company smtp server smtp auth through command line (assuming your username and password for smtp auth is jephe.wu and password)
$ echo -n "jephe.wu" | openssl enc -base64
amVwaGUud3U=
$ echo -n "password" | openssl enc -base64
cGFzc3dvcmQ=

telnet mailserver 25/587
ehlo abc
auth login
334 VXNlcm5hbWU6
amVwaGUud3U=
334 UGFzc3dvcmQ6
cGFzc3dvcmQ=