Network import with DataPump

With DataPump, there is no need to create an export file anymore, if the sole purpose is to import data.

 

Before proceeding with the import, the following needs to be configured:

 

• Create a streams pool (50Mb)
• Create a directory (Since there is no export file involved, it serves as the location of the datapump logfile, there is no export file involved)
• Grant read-write privileges on the datapump directory to the datapump user
• Create a public database link to the remote database

 

Import with DataPump

Generic Example: (in this case I connected to the remote schema directly)

 

1. alter system set streams_pool_size=50Mb scope=both;
2. create directory DATAPUMP_DIR as ‘/export/datapump’;
3. grant read, write on directory DATAPUMP_DIR to DPEDEV;
4. create public database link RDPEPRD connect to DPEPRD identified by DPEPRD using ‘rdpeprd’;
5. impdp dpedev/dpedev directory=datapump_dir network_link=RDPEPRD REMAP_SCHEMA=DPEPRD:DPEDEV

 Make sure the remote user has the EXP_FULL_DATABASE role

Pierre

10 Facts about Automatic Statistics Gathering

I regularly receive inquiries about automatic statistics gathering which is controlled by a default job (gather_stats_job) installed with any new 10G/11G database.

 

Here are some facts I’ve compiled so far:

 

• Job runs daily in 10g, weekly in 11g

 

• Job runs in the default maintenance window (opened from 10pm to 6am)

 

• Gather statistics on objects with missing or stale statistics

 

• Can be resource intensive depending on daily activity

 

• Objects that are in most need are processed first

 

• The default gathering parameters can be modified using DBMS_STATS.SET_PARAM

 

• Run DBMS_SCHEDULER.DISABLE(‘GATHER_STATS_JOB’) to disable the job

 

• For highly volatile tables (truncated once or many times during the day), it’s preferrable to set the statistics to NULL and let the dynamic sampling kick in, use DBMS_STATS.DELETE_STATS and LOCK_TABLE_STATS

 

• Statistics are not gathered on external tables, they must be gathered individually using DBMS_STATS.GATHER_TABLE_STATS

 

• Statistics are not gathered on fixed objects , use GATHER_FIXED_OBJECTS_STATS during a representative workload

Grid Control: Custom Alerts Notification Method with PL/SQL

We’ve been asked to generate automatic database incidents in our service desk (CA Unicenter). 

And through the Grid Control it’s a piece of cake.

 

The example below is generic. If you need the exact syntax to send to your CA unicenter server, send me an email at pierre.roussin@gmail.com.

 

For more flexibility, we created 2 procedures, one to get the notification from the console mechanism and one to build the message and email it to the recipient (service desk in our case).

 

Creating the incident

This procedure can be customized to send an email to the dba accounts

create or replace procedure sysman.create_incident(s1 in varchar2,s2 in varchar2, s3 in varchar2)
IS 
mailhost VARCHAR2(30) := ’smtp server’; 
mail_conn utl_smtp.connection;    
msg1 varchar2(2000);     
sender varchar2(30):=’address of sender‘; 

recipient varchar2(30):=’destination of email‘; 
subject varchar2(40):=‘customized subject’;  
finalstr varchar2(8000):=null;    /* Body of the email */
 
/* Wrapper function to write data to the mail server */
PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
BEGIN
        utl_smtp.write_data(mail_conn, name || ‘: ‘ || header || utl_tcp.CRLF);
END;
 
BEGIN
/* in the variable finalstr you can customized your notification email */ 

finalstr:= ‘customized text‘ || utl_tcp.CRLF;

…
 
 mail_conn := utl_smtp.open_connection(mailhost);
 utl_smtp.helo(mail_conn, mailhost);
 utl_smtp.mail(mail_conn,sender);    — sender
 utl_smtp.rcpt(mail_conn,recipient); — recipient
 utl_smtp.open_data(mail_conn);
 send_header(‘From’,    sender || ‘<’ || sender || ‘>’);
 send_header(‘To’,      ‘”Recipient” <’ || recipient || ‘>’);
 send_header(‘Subject’, subject);
 utl_smtp.write_data(mail_conn, finalstr);
 utl_smtp.close_data(mail_conn);
 utl_smtp.quit(mail_conn);
EXCEPTION
WHEN OTHERS THEN
 DBMS_OUTPUT.PUT_LINE(‘Send Mail Error: ‘ || substr(sqlerrm,1,100));
END;

 

Getting the console notification

Notice that we only want to be notified by critical alerts. consult the Oracle documentation for other status.

 

create or replace procedure sysman.get_notification(severity IN MGMT_NOTIFY_SEVERITY)
IS
BEGIN
   IF severity.severity_code = MGMT_GLOBAL.G_SEVERITY_CRITICAL
   THEN
        BEGIN

/* severity object attributes are passed to the procedure */

       create_incident(severity.target_name,severity.message,severity.collection_timestamp);
        EXCEPTION
        WHEN OTHERS
        THEN
               RAISE_APPLICATION_ERROR(-20000, ‘Procedure create_incident ended in error’);
        END;
        COMMIT;
   END IF;
END;

 

 Enabling the procedure in the grid control

• Navigate to setup - Notification Methods - Scripts and snmp traps – Add pl/sql procedure – Go
• Enter the name, description and the fully qualified name of the procedure (ex:sysman.get_notification)
• Navigate to Preferences – Notifications – Rules – Assign Methods to Multiple rules
• Choose a rule name (database availability and critical states in this case), check the box of your custom method

 

Pierre