Running multiple instances of sendmail on one server

<myvirtualip1> = 192.168.0.205
<myvirtualip1> = 192.168.0.206
<myvirtualdomain1> = webmx1.snapdedo.com
<myvirtualdomain2> = webmx2.snapdedo.com

cd /etc/mail
cp sendmail.mc sendmail.mc.`date +%Y%m%d%H%M`
service sendmail stop

cp sendmail.mc instance1sendmail.mc
cp sendmail.mc instance2sendmail.mc
vi instance1sendmail.mc
-- Replace Find the line with daemon options, something like this
DAEMON_OPTIONS(`Port=smtp,Addr=127.0.0.1, Name=MTA')dnl
change to bind to your virtual ip
DAEMON_OPTIONS(`Port=smtp,Addr=<myvirtualip1>, Name=MTA')dnl

-- Add
define('QUEUE_DIR','/var/spool/mqueue/instance1sendmail/q*')dnl
define('confPID_FILE','/var/run/instance1sendmail.pid')dnl
define('confDOMAIN_NAME','<myvirtualdomain1>')dnl

m4 /etc/mail/instance1sendmail.mc > /etc/mail/instance1sendmail.cf

mkdir /var/spool/mqueue/instance1sendmail
mkdir /var/spool/mqueue/instance1sendmail/q{1,2,3,4,5,6,7,8}
chown -R root:mail /var/spool/mqueue/
chmod -R 777 /var/spool/mqueue/

sendmail -L instance1sendmail -C /etc/mail/instance1sendmail.cf -bd –q30m

 

vi instance2sendmail.mc
-- Replace Find the line with daemon options, something like this
DAEMON_OPTIONS(`Port=smtp,Addr=127.0.0.1, Name=MTA')dnl
change to bind to your virtual ip
DAEMON_OPTIONS(`Port=smtp,Addr=<myvirtualip2>, Name=MTA')dnl

-- Add
define('QUEUE_DIR','/var/spool/mqueue/instance2sendmail/q*')dnl
define('confPID_FILE','/var/run/instance2sendmail.pid')dnl
define('confDOMAIN_NAME','<myvirtualdomain2>')dnl

m4 /etc/mail/instance2sendmail.mc > /etc/mail/instance2sendmail.cf

mkdir /var/spool/mqueue/instance2sendmail
mkdir /var/spool/mqueue/instance2sendmail/q{1,2,3,4,5,6,7,8}
chown -R root:mail /var/spool/mqueue/
chmod -R 777 /var/spool/mqueue/

sendmail -L instance2sendmail -C /etc/mail/instance2sendmail.cf -bd –q30m

netstat -an | grep :25

 

Test the email

[/app_scripts/Perl]$ telnet webmx1.snapdedo.com 25
Trying 192.168.0.205 ...
Connected to webmx1.snapdedo.com (192.168.0.205 ).
Escape character is '^]'.
220 webmx1.snapdedo.com ESMTP Sendmail 8.14.4/8.14.4; Wed, 29 Aug 2012 16:23:04 -0400
helo palmcoastd.com
250 webmx1.snapdedo.com Hello [192.168.0.208], pleased to meet you
mail from:scott.t.jenkins@snapdedo.com
250 2.1.0 scott.t.jenkins@snapdedo.com... Sender ok
rcpt to:scott.t.jenkins@snapdedo.com
550 5.7.1 scott.t.jenkins@snapdedo.com... Relaying denied. IP name lookup failed [192.168.0.208]

 

vi /etc/mail/access
-- Add
192.168                            RELAY

makemap hash /etc/mail/access < /etc/mail/access

Copy stats from one database to another expdp, impdp 1 table

<schema>
<table1>
<table2>
<sysadmin_user>
<sysadmin_password>

 

exec dbms_stats.create_stat_table ( ownname => '<schema>' , stattab => 'temp_stat_<table1>' ) ;
exec dbms_stats.create_stat_table ( ownname => '<schema>' , stattab => 'temp_stat_<table2>' ) ;

 

exec dbms_stats.export_table_stats ( ownname => '<schema>' , stattab => 'temp_stat_<table1>', tabname => '<table1>', statid => 'stats03252011') ;
exec dbms_stats.export_table_stats ( ownname => '<schema>' , stattab => 'temp_stat_<table2>', tabname => '<table2>', statid => 'stats03252011') ;

expdp <sysadmin_user>/<sysadmin_password> tables=<schema>.temp_stat_<table1>,<schema>.temp_stat_<table2> directory=stjdump dumpfile=stats.dmp logfile=exp_stats.log

impdp <sysadmin_user>/<sysadmin_password> tables=<schema>.temp_stat_<table1>,<schema>.temp_stat_<table2> directory=stjdump dumpfile=stats.dmp logfile=imp_stats.log

exec dbms_stats.import_table_stats ( ownname => 'STJCONSOLIDATED' , stattab => 'temp_stat_<table1>', tabname => '<table1>' );
exec dbms_stats.import_table_stats ( ownname => 'STJCONSOLIDATED' , stattab => 'temp_stat_<table2>', tabname => '<table2>' );

Recompile synonyms after apex install in 10.2.0.4 database

 

 

select * from dba_synonyms where owner = 'APEX_040100';
select 'CREATE OR REPLACE SYNONYM ' || owner || '.' || synonym_name || ' FOR ' || table_owner || '.' || table_name ||';'
from dba_synonyms
where owner = 'APEX_040100'
and synonym_name in (select object_name
                       from dba_objects
                       where status = 'INVALID');
select 'CREATE OR REPLACE ' || owner || ' SYNONYM ' || synonym_name || ' FOR ' || table_owner || '.' || table_name ||';'
from dba_synonyms
where owner = 'PUBLIC'
and synonym_name in (select object_name
                       from dba_objects
                       where status = 'INVALID');

CREATE OR REPLACE SYNONYM APEX_040100.APEX_COLLECTIONS FOR APEX_040100.WWV_FLOW_COLLECTIONS;
CREATE OR REPLACE SYNONYM APEX_040100.APEX_INSTANCE_ADMIN FOR APEX_040100.WWV_FLOW_INSTANCE_ADMIN;
CREATE OR REPLACE SYNONYM APEX_040100.APEX_PLSQL_JOBS FOR APEX_040100.WWV_FLOW_PLSQL_JOBS;
CREATE OR REPLACE SYNONYM APEX_040100.APEX_PLSQL_JOB FOR APEX_040100.WWV_FLOW_PLSQL_JOB;
CREATE OR REPLACE SYNONYM APEX_040100.APEX_CSS FOR APEX_040100.WWV_FLOW_CSS;
CREATE OR REPLACE SYNONYM APEX_040100.APEX_LOGIN FOR APEX_040100.HTMLDB_LOGIN;
CREATE OR REPLACE SYNONYM APEX_040100.APEX_WEB_SERVICE FOR APEX_040100.WWV_FLOW_WEBSERVICES_API;
CREATE OR REPLACE SYNONYM APEX_040100.APEX_REST FOR APEX_040100.WWV_FLOW_REST;
CREATE OR REPLACE SYNONYM APEX_040100.APEX_ITEM FOR APEX_040100.HTMLDB_ITEM;
CREATE OR REPLACE SYNONYM APEX_040100.APEX_CUSTOM_AUTH FOR APEX_040100.HTMLDB_CUSTOM_AUTH;
CREATE OR REPLACE SYNONYM APEX_040100.APEX_LANG FOR APEX_040100.HTMLDB_LANG;
CREATE OR REPLACE SYNONYM APEX_040100.APEX_SITE_ADMIN_PRIVS FOR APEX_040100.HTMLDB_SITE_ADMIN_PRIVS;
CREATE OR REPLACE SYNONYM APEX_040100.APEX_APPLICATION_INSTALL FOR APEX_040100.WWV_FLOW_APPLICATION_INSTALL;
CREATE OR REPLACE SYNONYM APEX_040100.HTMLDB_LDAP FOR APEX_040100.WWV_FLOW_LDAP;
CREATE OR REPLACE SYNONYM APEX_040100.HTMLDB_COLLECTIONS FOR APEX_040100.WWV_FLOW_COLLECTIONS;
CREATE OR REPLACE SYNONYM APEX_040100.HTMLDB_PLSQL_JOBS FOR APEX_040100.WWV_FLOW_PLSQL_JOBS;
CREATE OR REPLACE SYNONYM APEX_040100.HTMLDB_PLSQL_JOB FOR APEX_040100.WWV_FLOW_PLSQL_JOB;
CREATE OR REPLACE SYNONYM APEX_040100.APEX_DATA_LOAD FOR APEX_040100.WWV_FLOW_DATA_UPLOAD;
CREATE OR REPLACE SYNONYM APEX_040100.APEX_UTIL FOR APEX_040100.HTMLDB_UTIL;
CREATE OR REPLACE SYNONYM APEX_040100.APEX_LDAP FOR APEX_040100.WWV_FLOW_LDAP;

CREATE OR REPLACE PUBLIC SYNONYM HTMLDB_SYSTEM FOR SYS.HTMLDB_SYSTEM;
CREATE OR REPLACE PUBLIC SYNONYM WWV_RENDER_CHART2 FOR APEX_040100.WWV_RENDER_CHART2;
CREATE OR REPLACE PUBLIC SYNONYM WS FOR APEX_040100.WS;
CREATE OR REPLACE PUBLIC SYNONYM P FOR APEX_040100.P;
CREATE OR REPLACE PUBLIC SYNONYM NV FOR APEX_040100.NV;
CREATE OR REPLACE PUBLIC SYNONYM WWV_FLOW_HOT_HTTP_LINKS FOR APEX_040100.WWV_FLOW_HOT_HTTP_LINKS;
CREATE OR REPLACE PUBLIC SYNONYM HTMLDB FOR APEX_040100.HTMLDB;
CREATE OR REPLACE PUBLIC SYNONYM HTMLDB_LOGIN FOR APEX_040100.HTMLDB_LOGIN;
CREATE OR REPLACE PUBLIC SYNONYM WWV_FLOW_PREFERENCES FOR APEX_040100.WWV_FLOW_PREFERENCES;
CREATE OR REPLACE PUBLIC SYNONYM WWV_FLOW_COLLECTIONS FOR APEX_040100.WWV_FLOW_COLLECTIONS;
CREATE OR REPLACE PUBLIC SYNONYM WWV_RENDER_CALENDAR2 FOR APEX_040100.WWV_RENDER_CALENDAR2;
CREATE OR REPLACE PUBLIC SYNONYM DEVELOPMENT_SERVICE_HOME FOR APEX_040100.DEVELOPMENT_SERVICE_HOME;
CREATE OR REPLACE PUBLIC SYNONYM DEVELOPMENT_SERVICE_HOME_LOGIN FOR APEX_040100.DEVELOPMENT_SERVICE_HOME_LOGIN;
CREATE OR REPLACE PUBLIC SYNONYM DEVELOPMENT_SERVICE_SIGNUP FOR APEX_040100.DEVELOPMENT_SERVICE_SIGNUP;
CREATE OR REPLACE PUBLIC SYNONYM WWV_FLOW_SVG FOR APEX_040100.WWV_FLOW_SVG;
CREATE OR REPLACE PUBLIC SYNONYM WWV_FLOW_CUSTOMIZE FOR APEX_040100.WWV_FLOW_CUSTOMIZE;
CREATE OR REPLACE PUBLIC SYNONYM WWV_FLOW_USERS FOR APEX_040100.WWV_FLOW_USERS;
CREATE OR REPLACE PUBLIC SYNONYM WWV_FLOW_GROUP_USERS FOR APEX_040100.WWV_FLOW_GROUP_USERS;
CREATE OR REPLACE PUBLIC SYNONYM WWV_FLOW_CSS FOR APEX_040100.WWV_FLOW_CSS;
CREATE OR REPLACE PUBLIC SYNONYM WWV_FLOW_AUTHENTICATION FOR APEX_040100.WWV_FLOW_AUTHENTICATION;
CREATE OR REPLACE PUBLIC SYNONYM HTMLDB_ADMIN FOR APEX_040100.HTMLDB_ADMIN;
CREATE OR REPLACE PUBLIC SYNONYM HTMLDB_UTIL FOR APEX_040100.HTMLDB_UTIL;
CREATE OR REPLACE PUBLIC SYNONYM HTMLDB_ITEM FOR APEX_040100.HTMLDB_ITEM;
CREATE OR REPLACE PUBLIC SYNONYM HTMLDB_CUSTOM_AUTH FOR APEX_040100.HTMLDB_CUSTOM_AUTH;
CREATE OR REPLACE PUBLIC SYNONYM HTMLDB_LANG FOR APEX_040100.HTMLDB_LANG;
CREATE OR REPLACE PUBLIC SYNONYM HTMLDB_COLLECTIONS FOR APEX_040100.HTMLDB_COLLECTIONS;
CREATE OR REPLACE PUBLIC SYNONYM HTMLDB_PLSQL_JOB FOR APEX_040100.HTMLDB_PLSQL_JOB;
CREATE OR REPLACE PUBLIC SYNONYM HTMLDB_PLSQL_JOBS FOR APEX_040100.HTMLDB_PLSQL_JOBS;
CREATE OR REPLACE PUBLIC SYNONYM WWV_FLOW_LDAP FOR APEX_040100.WWV_FLOW_LDAP;
CREATE OR REPLACE PUBLIC SYNONYM WWV_FLOW_CUSTOM_AUTH_LDAP FOR APEX_040100.WWV_FLOW_CUSTOM_AUTH_LDAP;
CREATE OR REPLACE PUBLIC SYNONYM HTMLDB_LDAP FOR APEX_040100.HTMLDB_LDAP;
CREATE OR REPLACE PUBLIC SYNONYM APEX_LOGIN FOR APEX_040100.APEX_LOGIN;
CREATE OR REPLACE PUBLIC SYNONYM APEX_UTIL FOR APEX_040100.APEX_UTIL;
CREATE OR REPLACE PUBLIC SYNONYM APEX_WEB_SERVICE FOR APEX_040100.APEX_WEB_SERVICE;
CREATE OR REPLACE PUBLIC SYNONYM APEX_REST FOR APEX_040100.APEX_REST;
CREATE OR REPLACE PUBLIC SYNONYM APEX_ITEM FOR APEX_040100.APEX_ITEM;
CREATE OR REPLACE PUBLIC SYNONYM APEX_CUSTOM_AUTH FOR APEX_040100.APEX_CUSTOM_AUTH;
CREATE OR REPLACE PUBLIC SYNONYM APEX_LANG FOR APEX_040100.APEX_LANG;
CREATE OR REPLACE PUBLIC SYNONYM APEX_COLLECTIONS FOR APEX_040100.APEX_COLLECTIONS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_INSTANCE_ADMIN FOR APEX_040100.APEX_INSTANCE_ADMIN;
CREATE OR REPLACE PUBLIC SYNONYM APEX_PLSQL_JOB FOR APEX_040100.APEX_PLSQL_JOB;
CREATE OR REPLACE PUBLIC SYNONYM APEX_PLSQL_JOBS FOR APEX_040100.APEX_PLSQL_JOBS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_LDAP FOR APEX_040100.APEX_LDAP;
CREATE OR REPLACE PUBLIC SYNONYM APEX_SITE_ADMIN_PRIVS FOR APEX_040100.APEX_SITE_ADMIN_PRIVS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_CSS FOR APEX_040100.APEX_CSS;
CREATE OR REPLACE PUBLIC SYNONYM WWV_FLOW_HTML_API FOR APEX_040100.WWV_FLOW_HTML_API;
CREATE OR REPLACE PUBLIC SYNONYM WWV_FLOW_IMAGE_API FOR APEX_040100.WWV_FLOW_IMAGE_API;
CREATE OR REPLACE PUBLIC SYNONYM WWV_FLOW_CSS_API FOR APEX_040100.WWV_FLOW_CSS_API;
CREATE OR REPLACE PUBLIC SYNONYM APEX_UI_DEFAULT_UPDATE FOR APEX_040100.APEX_UI_DEFAULT_UPDATE;
CREATE OR REPLACE PUBLIC SYNONYM APEX_WORKSPACES FOR APEX_040100.APEX_WORKSPACES;
CREATE OR REPLACE PUBLIC SYNONYM APEX_WORKSPACE_SESSIONS FOR APEX_040100.APEX_WORKSPACE_SESSIONS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_WORKSPACE_APEX_USERS FOR APEX_040100.APEX_WORKSPACE_APEX_USERS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_WORKSPACE_DEVELOPERS FOR APEX_040100.APEX_WORKSPACE_DEVELOPERS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_WORKSPACE_GROUPS FOR APEX_040100.APEX_WORKSPACE_GROUPS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_WORKSPACE_GROUP_USERS FOR APEX_040100.APEX_WORKSPACE_GROUP_USERS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_WORKSPACE_SCHEMAS FOR APEX_040100.APEX_WORKSPACE_SCHEMAS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_WORKSPACE_FILES FOR APEX_040100.APEX_WORKSPACE_FILES;
CREATE OR REPLACE PUBLIC SYNONYM APEX_WORKSPACE_SQL_SCRIPTS FOR APEX_040100.APEX_WORKSPACE_SQL_SCRIPTS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_WORKSPACE_ACTIVITY_LOG FOR APEX_040100.APEX_WORKSPACE_ACTIVITY_LOG;
CREATE OR REPLACE PUBLIC SYNONYM APEX_WORKSPACE_LOG_SUMMARY_USR FOR APEX_040100.APEX_WORKSPACE_LOG_SUMMARY_USR;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_PAGES FOR APEX_040100.APEX_APPLICATION_PAGES;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATIONS FOR APEX_040100.APEX_APPLICATIONS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_CACHING FOR APEX_040100.APEX_APPLICATION_CACHING;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_SUBSTITUTIONS FOR APEX_040100.APEX_APPLICATION_SUBSTITUTIONS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_PAGE_ITEMS FOR APEX_040100.APEX_APPLICATION_PAGE_ITEMS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_PAGE_DB_ITEMS FOR APEX_040100.APEX_APPLICATION_PAGE_DB_ITEMS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_PAGE_REGIONS FOR APEX_040100.APEX_APPLICATION_PAGE_REGIONS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_PAGE_RPT FOR APEX_040100.APEX_APPLICATION_PAGE_RPT;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_PAGE_RPT_COLS FOR APEX_040100.APEX_APPLICATION_PAGE_RPT_COLS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_PAGE_BRANCHES FOR APEX_040100.APEX_APPLICATION_PAGE_BRANCHES;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_PAGE_PROC FOR APEX_040100.APEX_APPLICATION_PAGE_PROC;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_PAGE_COMP FOR APEX_040100.APEX_APPLICATION_PAGE_COMP;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_PAGE_VAL FOR APEX_040100.APEX_APPLICATION_PAGE_VAL;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_PAGE_BUTTONS FOR APEX_040100.APEX_APPLICATION_PAGE_BUTTONS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_BUILD_OPTIONS FOR APEX_040100.APEX_APPLICATION_BUILD_OPTIONS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_TEMPLATES FOR APEX_040100.APEX_APPLICATION_TEMPLATES;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_TABS FOR APEX_040100.APEX_APPLICATION_TABS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_PARENT_TABS FOR APEX_040100.APEX_APPLICATION_PARENT_TABS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_ITEMS FOR APEX_040100.APEX_APPLICATION_ITEMS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_PROCESSES FOR APEX_040100.APEX_APPLICATION_PROCESSES;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_COMPUTATIONS FOR APEX_040100.APEX_APPLICATION_COMPUTATIONS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_SHORTCUTS FOR APEX_040100.APEX_APPLICATION_SHORTCUTS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_TREES FOR APEX_040100.APEX_APPLICATION_TREES;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_NAV_BAR FOR APEX_040100.APEX_APPLICATION_NAV_BAR;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_AUTHORIZATION FOR APEX_040100.APEX_APPLICATION_AUTHORIZATION;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_AUTH FOR APEX_040100.APEX_APPLICATION_AUTH;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_WEB_SERVICES FOR APEX_040100.APEX_APPLICATION_WEB_SERVICES;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_BREADCRUMBS FOR APEX_040100.APEX_APPLICATION_BREADCRUMBS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_BC_ENTRIES FOR APEX_040100.APEX_APPLICATION_BC_ENTRIES;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_LISTS FOR APEX_040100.APEX_APPLICATION_LISTS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_LIST_ENTRIES FOR APEX_040100.APEX_APPLICATION_LIST_ENTRIES;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_LOVS FOR APEX_040100.APEX_APPLICATION_LOVS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_LOV_ENTRIES FOR APEX_040100.APEX_APPLICATION_LOV_ENTRIES;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_THEMES FOR APEX_040100.APEX_APPLICATION_THEMES;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_PAGE_MAP FOR APEX_040100.APEX_APPLICATION_PAGE_MAP;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPL_PLUGINS FOR APEX_040100.APEX_APPL_PLUGINS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPL_PLUGIN_ATTRIBUTES FOR APEX_040100.APEX_APPL_PLUGIN_ATTRIBUTES;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPL_PLUGIN_ATTR_VALUES FOR APEX_040100.APEX_APPL_PLUGIN_ATTR_VALUES;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPL_PLUGIN_FILES FOR APEX_040100.APEX_APPL_PLUGIN_FILES;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPL_PLUGIN_EVENTS FOR APEX_040100.APEX_APPL_PLUGIN_EVENTS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_TRANSLATIONS FOR APEX_040100.APEX_APPLICATION_TRANSLATIONS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_SUPP_OBJECTS FOR APEX_040100.APEX_APPLICATION_SUPP_OBJECTS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_SUPP_OBJ_SCR FOR APEX_040100.APEX_APPLICATION_SUPP_OBJ_SCR;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_SUPP_OBJ_CHCK FOR APEX_040100.APEX_APPLICATION_SUPP_OBJ_CHCK;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_SUPP_OBJ_BOPT FOR APEX_040100.APEX_APPLICATION_SUPP_OBJ_BOPT;
CREATE OR REPLACE PUBLIC SYNONYM APEX_WORKSPACE_LOG_ARCHIVE FOR APEX_040100.APEX_WORKSPACE_LOG_ARCHIVE;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_ALL_AUTH FOR APEX_040100.APEX_APPLICATION_ALL_AUTH;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_TEMP_BC FOR APEX_040100.APEX_APPLICATION_TEMP_BC;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_TEMP_BUTTON FOR APEX_040100.APEX_APPLICATION_TEMP_BUTTON;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_TEMP_CALENDAR FOR APEX_040100.APEX_APPLICATION_TEMP_CALENDAR;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_TEMP_LABEL FOR APEX_040100.APEX_APPLICATION_TEMP_LABEL;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_TEMP_LIST FOR APEX_040100.APEX_APPLICATION_TEMP_LIST;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_TEMP_PAGE FOR APEX_040100.APEX_APPLICATION_TEMP_PAGE;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_TEMP_POPUPLOV FOR APEX_040100.APEX_APPLICATION_TEMP_POPUPLOV;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_TEMP_REGION FOR APEX_040100.APEX_APPLICATION_TEMP_REGION;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_TEMP_REPORT FOR APEX_040100.APEX_APPLICATION_TEMP_REPORT;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_TRANS_MAP FOR APEX_040100.APEX_APPLICATION_TRANS_MAP;
CREATE OR REPLACE PUBLIC SYNONYM APEX_WORKSPACE_LOG_SUMMARY FOR APEX_040100.APEX_WORKSPACE_LOG_SUMMARY;
CREATE OR REPLACE PUBLIC SYNONYM APEX_WORKSPACE_CLICKS FOR APEX_040100.APEX_WORKSPACE_CLICKS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_PAGE_FLASH_CH FOR APEX_040100.APEX_APPLICATION_PAGE_FLASH_CH;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_PAGE_FLASH_S FOR APEX_040100.APEX_APPLICATION_PAGE_FLASH_S;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_PAGE_FLASH5 FOR APEX_040100.APEX_APPLICATION_PAGE_FLASH5;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_PAGE_FLASH5_S FOR APEX_040100.APEX_APPLICATION_PAGE_FLASH5_S;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_PAGE_TREES FOR APEX_040100.APEX_APPLICATION_PAGE_TREES;
CREATE OR REPLACE PUBLIC SYNONYM APEX_WORKSPACE_ACCESS_LOG FOR APEX_040100.APEX_WORKSPACE_ACCESS_LOG;
CREATE OR REPLACE PUBLIC SYNONYM APEX_DEVELOPER_ACTIVITY_LOG FOR APEX_040100.APEX_DEVELOPER_ACTIVITY_LOG;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_PAGE_GROUPS FOR APEX_040100.APEX_APPLICATION_PAGE_GROUPS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_TEAM_BUGS FOR APEX_040100.APEX_TEAM_BUGS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_TEAM_MILESTONES FOR APEX_040100.APEX_TEAM_MILESTONES;
CREATE OR REPLACE PUBLIC SYNONYM APEX_TEAM_TODOS FOR APEX_040100.APEX_TEAM_TODOS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_TEAM_FEEDBACK FOR APEX_040100.APEX_TEAM_FEEDBACK;
CREATE OR REPLACE PUBLIC SYNONYM APEX_TEAM_FEEDBACK_FOLLOWUP FOR APEX_040100.APEX_TEAM_FEEDBACK_FOLLOWUP;
CREATE OR REPLACE PUBLIC SYNONYM APEX_TEAM_FEATURES FOR APEX_040100.APEX_TEAM_FEATURES;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_TRANS_DYNAMIC FOR APEX_040100.APEX_APPLICATION_TRANS_DYNAMIC;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_TRANS_REPOS FOR APEX_040100.APEX_APPLICATION_TRANS_REPOS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPL_LOAD_TABLES FOR APEX_040100.APEX_APPL_LOAD_TABLES;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPL_LOAD_TABLE_LOOKUPS FOR APEX_040100.APEX_APPL_LOAD_TABLE_LOOKUPS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPL_LOAD_TABLE_RULES FOR APEX_040100.APEX_APPL_LOAD_TABLE_RULES;
CREATE OR REPLACE PUBLIC SYNONYM WWV_MIG_ACC_LOAD FOR APEX_040100.WWV_MIG_ACC_LOAD;
CREATE OR REPLACE PUBLIC SYNONYM APEXWS FOR APEX_040100.APEXWS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_PAGE_IR FOR APEX_040100.APEX_APPLICATION_PAGE_IR;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_PAGE_IR_COL FOR APEX_040100.APEX_APPLICATION_PAGE_IR_COL;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_PAGE_IR_CGRPS FOR APEX_040100.APEX_APPLICATION_PAGE_IR_CGRPS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_PAGE_IR_RPT FOR APEX_040100.APEX_APPLICATION_PAGE_IR_RPT;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_PAGE_IR_COMP FOR APEX_040100.APEX_APPLICATION_PAGE_IR_COMP;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_PAGE_IR_COND FOR APEX_040100.APEX_APPLICATION_PAGE_IR_COND;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_PAGE_IR_CAT FOR APEX_040100.APEX_APPLICATION_PAGE_IR_CAT;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_PAGE_IR_SUB FOR APEX_040100.APEX_APPLICATION_PAGE_IR_SUB;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_PAGE_IR_GRPBY FOR APEX_040100.APEX_APPLICATION_PAGE_IR_GRPBY;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_GROUPS FOR APEX_040100.APEX_APPLICATION_GROUPS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_UI_DEFAULTS_ATTR_DICT FOR APEX_040100.APEX_UI_DEFAULTS_ATTR_DICT;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_PAGE_DA FOR APEX_040100.APEX_APPLICATION_PAGE_DA;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_PAGE_DA_ACTS FOR APEX_040100.APEX_APPLICATION_PAGE_DA_ACTS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_MIGRATION_PROJECTS FOR APEX_040100.APEX_MIGRATION_PROJECTS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_MIGRATION_ACC_PROJECTS FOR APEX_040100.APEX_MIGRATION_ACC_PROJECTS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_MIGRATION_ACC_TABLES FOR APEX_040100.APEX_MIGRATION_ACC_TABLES;
CREATE OR REPLACE PUBLIC SYNONYM APEX_MIGRATION_ACC_FORMS FOR APEX_040100.APEX_MIGRATION_ACC_FORMS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_MIGRATION_ACC_RPTS FOR APEX_040100.APEX_MIGRATION_ACC_RPTS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_MIGRATION_ACC_QUERIES FOR APEX_040100.APEX_MIGRATION_ACC_QUERIES;
CREATE OR REPLACE PUBLIC SYNONYM APEX_MIGRATION_REV_FORMS FOR APEX_040100.APEX_MIGRATION_REV_FORMS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_MIGRATION_REV_RPTS FOR APEX_040100.APEX_MIGRATION_REV_RPTS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_MIGRATION_REV_QUERIES FOR APEX_040100.APEX_MIGRATION_REV_QUERIES;
CREATE OR REPLACE PUBLIC SYNONYM APEX_MIGRATION_REV_TABLES FOR APEX_040100.APEX_MIGRATION_REV_TABLES;
CREATE OR REPLACE PUBLIC SYNONYM APEX_MIG_PROJECTS_UPDATE FOR APEX_040100.APEX_MIG_PROJECTS_UPDATE;
CREATE OR REPLACE PUBLIC SYNONYM APEX_THEMES FOR APEX_040100.APEX_THEMES;
CREATE OR REPLACE PUBLIC SYNONYM APEX_DEBUG_MESSAGES FOR APEX_040100.APEX_DEBUG_MESSAGES;
CREATE OR REPLACE PUBLIC SYNONYM APEX_APPLICATION_INSTALL FOR APEX_040100.APEX_APPLICATION_INSTALL;
CREATE OR REPLACE PUBLIC SYNONYM APEX_WS_DATA_GRID FOR APEX_040100.APEX_WS_DATA_GRID;
CREATE OR REPLACE PUBLIC SYNONYM APEX_WS_APPLICATIONS FOR APEX_040100.APEX_WS_APPLICATIONS;
CREATE OR REPLACE PUBLIC SYNONYM APEX_WS_APP_PAGES FOR APEX_040100.APEX_WS_APP_PAGES;
CREATE OR REPLACE PUBLIC SYNONYM APEX_DATA_LOAD FOR APEX_040100.APEX_DATA_LOAD;

Change theme on oracle apex app 4.1

Choose Application

Choose supporting objects

Choose themes

Choose create to add a new theme

Choose from repository to use one of oracle’s predefined themes.

Choose the theme and select next

Confirm creation

Now choose switch theme

Choose the new theme

Verify compatibility and choose next

Confirm

Retest app

Install apex on oracle 10.2.0.4 db new install

http://docs.oracle.com/cd/E23903_01/doc/doc.41/e21673/otn_install.htm#CHDJFIBG

sudo su - oracle

. oraenv

<mydb>

cd $ORACLE_HOME

cp /software/oracle11gappserver/apex/apex_4.1.zip .

unzip apex_4.1.zip

cd apex

sqlplus / as sysdba

Verify the tablespace names

select tablespace_name from dba_tablespaces where tablespace_name in ('SYSAUX','TEMP');

@apexins sysaux sysaux temp /i/

@apxchpwd

<mypassword>

alter user apex_public_user account unlock;

alter user apex_public_user identified by <mypassword>;

alter user apex_public_user profile oracle_restricted;

exit
tar -czvf apex_images.tgz images
scp apex_images.tgz <username>@webserver:~

-- on web server
sudo su - root
cd /
mkdir /www/apex4.1images
chmod 775 /www/apex4.1images
cd /www/apex4.1images
cp /home/<username>/apex_images.tgz .
tar -xzvf apex_images.tgz
cd ..
chown <webuser>.<webgroup> /www/apex4.1images

sudo su - oracle

backup_confs

cd /app/oracle/product/ofm11g/<ofmname>/instances/<instancename>/config/OHS/ohs1/mod_plsql/

vi dads.conf

Alias /i/ "/www/apex4.1images/images/"

<Location /pls/apex>
Order deny,allow
PlsqlDocumentPath docs
AllowOverride None
PlsqlDocumentProcedure         wwv_flow_file_mgr.process_download
PlsqlDatabaseConnectString     host:port:service_name ServiceNameFormat
PlsqlNLSLanguage               AMERICAN_AMERICA.AL32UTF8
PlsqlAuthenticationMode        Basic
SetHandler                     pls_handler
PlsqlDocumentTablename         wwv_flow_file_objects$
PlsqlDatabaseUsername          APEX_PUBLIC_USER
PlsqlDefaultPage               apex
PlsqlDatabasePassword          apex_public_user_password
PlsqlRequestValidationFunction wwv_flow_epg_include_modules.authorize
Allow from all
</Location>

Error log table for bulk insert oracle

BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 'tbladdress');
END;
/

Segment or table size in oracle database

select  s.owner,
        s.segment_name,
        s.segment_type,
        TO_CHAR(s.extents,'9,999') "Ext",
        TO_CHAR(s.max_extents,'999,999') "MaxExt",
        to_char(s.Blocks,'99,999,999') "Blocks",
        to_char((s.bytes/1024/1024),'999,999') "MBYTES",
        to_char(s.initial_extent, '999,999,999') "Initial",
        to_char(s.next_extent,  '999,999,999') "Next",
        to_char(t.PCT_used, '999') "Used",
        to_char(t.PCT_free, '999') "Free"
from sys.dba_segments s, sys.dba_tables t
where s.owner not in ('SYS','SYSTEM')
and   s.segment_type= 'TABLE'
and   s.segment_name = t.table_name
and t.owner = s.owner
order by mbytes desc, s.owner, s.segment_name, segment_type;

select  s.owner,
        s.segment_name,
        s.segment_type,
        TO_CHAR(s.extents,'9,999') "Ext",
        TO_CHAR(s.max_extents,'999,999') "MaxExt",
        to_char(s.Blocks,'99,999,999') "Blocks",
        to_char((s.bytes/1024/1024),'999,999') "MBYTES",
        to_char(s.initial_extent, '999,999,999') "Initial",
        to_char(s.next_extent,  '999,999,999') "Next"
from sys.dba_segments s
where s.owner not in ('SYS','SYSTEM')
and s.owner in ('X')
order by mbytes desc, s.owner, s.segment_name, segment_type;

select  s.owner,
        to_char((sum(s.bytes)/1024/1024/1024),'999,999') "GBYTES"
from sys.dba_segments s
where s.owner not in ('SYS','SYSTEM')
and s.owner in ('X')
group by s.owner;

pga usage per session

SELECT sid,  trunc((VALUE/1024/1024)) || ' MB' "TOTAL MEMORY FOR ALL SESSIONS", value
    FROM V$SESSTAT, V$STATNAME
    WHERE NAME = 'session pga memory'
    AND V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC#
    order by 3 desc;

db cache

SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor, estd_physical_reads
   FROM V$DB_CACHE_ADVICE
   WHERE name          = 'DEFAULT'
     AND block_size    = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size')
     AND advice_status = 'ON';

Checking Jobs in oracle job scheduler for status

select * from dba_scheduler_job_log  where job_name = 'X';

select * from dba_scheduler_job_run_details where log_id = X order by log_id desc;

 

select log_date,status,additional_info from dba_scheduler_job_run_details where log_id in (select log_id from dba_scheduler_job_log where log_date > sysdate-6/24 and status = 'FAILED' ) order by log_id desc;
select log_id from dba_scheduler_job_log where log_date > sysdate-4/24 and status = 'FAILED' ;
select * from dba_scheduler_job_log where log_date > sysdate-2 and job_name like 'FULL%ADD%' order by log_date desc;
select * from dba_scheduler_jobs;

Compress file dynamically using plsql and instant job.

This is very similar to the last function I demo’d, just this plsql app will take 3 arguments

1. Directory –> This is the directory of the current file that will be backed up.
2. FileName –> This is the current file name for the file we wish to compress and archive.
3. ArchiveDirectory –> This is the back up or archive directory where the file will sit when complete.

The application user running this plsql procedure will need to be able to schedule jobs since this is the method to run the os commands.
I’m opting to use os commands because there does not need to be impact on the database engine while this runs.
You may need to tweak this based on os since this is designed specifically for Linux os Testing on rh4, proofing on ol6

grant create job to <user>;
grant create any job to <user>;
grant CREATE EXTERNAL JOB to <user>;

-- Grant access to oracle home
chmod a+rx /opt
chmod a+rx /opt/oracle
chmod a+rx /opt/oracle/db
chmod a+rx /opt/oracle/db/bin

on os -- 391820.1

sudo su – root
cd <ORACLE_HOME>/rdbms/admin
chmod 640 externaljob.ora
vi externaljob.ora
run_user = oracle
run_group = dba

groups oracle
Verify oracle is in the group that owns the oracle home directory “maybe oinstall”

[/extjob]# ls -l <ORACLE_HOME>/rdbms/admin | grep externaljob.ora
-rw-r--r--  1 root   dba    1531 Aug  8 14:17 externaljob.ora

[/extjob]# ls -l /app/oracle/product/10.2.0.4/bin | grep extjob
-rwsr-x---  1 root   dba    622819 Oct 21  2008 extjob
-rwxr-xr-x  1 oracle dba    622819 Oct 21  2008 extjobo

 

Example archive script, install this on os
mkdir /extjob/archive –p
chown oracle.dba /extjob –R
chmod 775 /extjob –R

#Note Oracle is fairly touchy about the syntak and I had to play with it a little to set it to get no errors, things that work simply on the os may not work as expected from the oracle user.
#Also remember the user is coming from the oracle home and all references are from oracle home so you may need to declare paths for logic

vi archive.sh
#!/bin/bash
touch /extjob/stj_test3.log
tar -czf /extjob/stj4.tgz -C /extjob stj_test3.log
mv /extjob/stj4.tgz /extjob/archive/stj4.tgz

#Now you plsql application to call archive.sh
# This job is scheduled to run immediately and in the same session if you want it to run in a different session you’ll need to add a run_job with a use_current_session=>false
PROCEDURE ARCHIVE_FILE
AS
BEGIN
  begin
    dbms_scheduler.drop_job(job_name        => 'archivefile2');
  exception
    when others then
      null;
  end;
  dbms_scheduler.create_job(job_name        => 'archivefile2',
                            job_type        => 'executable',
                            job_action      => '/extjob/archive.sh',
                            enabled         => TRUE,
                            auto_drop       => TRUE
                            );
  commit;
exception
  when others then
    dbms_output.put_line('ERROR:' || sqlerrm);
END ARCHIVE_FILE;

Compress a file on linux os using plsql in realtime

grant create job to <user>;
grant create any job to <user>;
grant CREATE EXTERNAL JOB to <user>;

-- Grant access to oracle home

chmod a+rx /opt
chmod a+rx /opt/oracle
chmod a+rx /opt/oracle/db
chmod a+rx /opt/oracle/db/bin

on os -- 391820.1

sudo su – root
cd <ORACLE_HOME>/rdbms/admin
chmod 640 externaljob.ora
vi externaljob.ora
run_user = oracle
run_group = dba

groups oracle
Verify oracle is in the group that owns the oracle home directory “maybe oinstall”

[/extjob]# ls -l <ORACLE_HOME>/rdbms/admin | grep externaljob.ora
-rw-r--r--  1 root   dba    1531 Aug  8 14:17 externaljob.ora

[/extjob]# ls -l /app/oracle/product/10.2.0.4/bin | grep extjob
-rwsr-x---  1 root   dba    622819 Oct 21  2008 extjob
-rwxr-xr-x  1 oracle dba    622819 Oct 21  2008 extjobo

 

Example archive script, install this on os
mkdir /extjob/archive –p
chown oracle.dba /extjob –R
chmod 775 /extjob –R

#Note Oracle is fairly touchy about the syntak and I had to play with it a little to set it to get no errors, things that work simply on the os may not work as expected from the oracle user.
#Also remember the user is coming from the oracle home and all references are from oracle home so you may need to declare paths for logic

vi archive.sh
#!/bin/bash
touch /extjob/stj_test3.log
tar -czf /extjob/stj4.tgz -C /extjob stj_test3.log
mv /extjob/stj4.tgz /extjob/archive/stj4.tgz

#Now you plsql application to call archive.sh
# This job is scheduled to run immediately and in the same session if you want it to run in a different session you’ll need to add a run_job with a use_current_session=>false
PROCEDURE ARCHIVE_FILE
AS
BEGIN
  begin
    dbms_scheduler.drop_job(job_name        => 'archivefile2');
  exception
    when others then
      null;
  end;
  dbms_scheduler.create_job(job_name        => 'archivefile2',
                            job_type        => 'executable',
                            job_action      => '/extjob/archive.sh',
                            enabled         => TRUE,
                            auto_drop       => TRUE
                            );
  commit;
exception
  when others then
    dbms_output.put_line('ERROR:' || sqlerrm);
END ARCHIVE_FILE;

Install oracle sql developer on 64bit system on windows 7

Download the latest application at

http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

Download a java 1.6 jdk (6u33 as of this writing)

http://www.oracle.com/technetwork/java/javase/downloads/index.html

By default will install

C:\Program Files\Java\jdk1.6.0_33

C:\Program Files\Java\jre

Extract all sqldeveloper files to

C:\sqldeveloper

cd C:\sqldeveloper\sqldeveloper

right click the sqldeveloper and select send to desktop shortcut

right click on sqldeveloper icon and enter jre path

C:\Program Files\Java\jdk1.6.0_33\jre\bin

 

If you run into an issue like this “Cannot find a J2SE SDK installed at path:” change the full path name to the java.exe name

C:\Program Files\Java\jdk1.6.0_33\jre\bin\java.exe

 

Create tablespace oracle file system

create tablespace sysdocs datafile 'tbs_sysdocs.dbf' SIZE 100M ONLINE;

Import schema into 11g oracle database

create user and tablespace if does not already exist.

<logical dump name>
<dump directory>
<schema>
<db>
<user>
<password>

. oraenv
<db>

sqlplus / as sysdba
CREATE OR REPLACE DIRECTORY <logical dump name> AS '<dump directory>';
grant read, write on directory <logical dump name> to <user>;
exit;

impdp <user>/<password> schemas=<schema> directory=<logical dump name> dumpfile=<schema>_prod.dmp logfile=imp_<schema>.log

Example:
create tablespace sysdocs datafile 'tbs_sysdocs.dbf' SIZE 100M ONLINE;
create user sysdocs identified by <password> default tablespace sysdocs;

<logical dump name> = stjdump
<dump directory> =/home/oracle/stjdump
<schema> = sysdocs
<db> = db11g
<user> = jenkinss
<password> =

sqlplus / as sysdba
CREATE OR REPLACE DIRECTORY stjdump AS '/home/oracle/stjdump';
grant read, write on directory stjdump to jenkinss;
exit;

impdp jenkinss/<password> schemas=sysdocs REMAP_TABLESPACE=APEX_4454331745105896:sysdocs directory=stjdump dumpfile=sysdocs_prod.dmp logfile=imp_sysdocs.log

Export schema from database oracle 10g

<logical dump name>
<dump directory>
<schema>
<db>
<user>
<password>

. oraenv
<db>

sqlplus / as sysdba
CREATE OR REPLACE DIRECTORY <logical dump name> AS '<dump directory>';
grant read, write on directory <logical dump name> to <user>;
exit;

expdp <user>/<password> schemas=<schema> directory=<logical dump name> dumpfile=<schema>_prod.dmp logfile=<schema>_prod.log

Example:

<logical dump name> = stjdump
<dump directory> =/home/oracle/stjdump
<schema> = sysdocs
<db> = db11g
<user> = jenkinss
<password> =

. oraenv
<db>

sqlplus / as sysdba
CREATE OR REPLACE DIRECTORY stjdump AS '/home/oracle/stjdump';
grant read, write on directory stjdump to jenkinss;
exit;

expdp jenkinss/<password> schemas=sysdocs directory=stjdump dumpfile=sysdocs_prod.dmp logfile=sysdocs_prod.log

Apex admin change password

@apxchpwd.sql

 

check this file and make sure it’s the right version if it has apex3 and you upgrade to apex4 you probably have the software installed elsewhere you can just change the user account in the file to get the update to work.

Disable Network virtual device

ifconfig eth0:7 down

ifconfig

Apex upgrade on new install 11g using ohs standalone web server

Steps to upgrade from the base apex 3 to apex 4.1 on a new install of 11g database using ofm11g ohs as the web server.

sudo su – oracle

mkdir /home/stage –p

chmod 775 /home/stage

mv apex_4.1.1en.zip /home/stage

cd /home/stage

unzip apex_4.1.1en.zip

cd /home/stage/apex

sqlplus / as sysdba

@apexins SYSAUX SYSAUX TEMP /i/

@apxldimg.sql /home/stage

On the database server run the following

cd $ORACLE_HOME <- database home

cd apex

1. DRIVE_LETTER:\> sqlplus /nolog
   SQL> CONNECT SYS as SYSDBA
   Enter password: SYS_password 
   

   
   
2. 
   

   Run apxxepwd.sql. For example:

   @apxxepwd.sql
   

Set the password

unlock public account

ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK

change the public user password

sql> PASSWORD APEX_PUBLIC_USER;

<new password>

on ohs server

modify dads.conf

1. Alias /i/ "ORACLE_BASE\ORACLE_HTTPSERVER_HOME\images/"
   AddType text/xml       xbl
   AddType text/x-component       htc
   
   <Location /pls/apex>
    Order deny,allow
    PlsqlDocumentPath docs
    AllowOverride None
    PlsqlDocumentProcedure      wwv_flow_file_mgr.process_download
    PlsqlDatabaseConnectString  host:port:service_name ServiceNameFormat
    PlsqlNLSLanguage            AMERICAN_AMERICA.AL32UTF8
    PlsqlAuthenticationMode     Basic
    SetHandler                  pls_handler
    PlsqlDocumentTablename      wwv_flow_file_objects$
    PlsqlDatabaseUsername       APEX_PUBLIC_USER
    PlsqlDefaultPage            apex
    PlsqlDatabasePassword       apex_public_user_password
    Allow from all
   </Location>

   
   

2. copy images from apex images directory on database to location on web server where I is mapped

 

Allow access with acl

 

DECLARE
  ACL_PATH  VARCHAR2(4000);
  ACL_ID    RAW(16);
BEGIN
  
  SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
   WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

  SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID
    FROM XDB.XDB$ACL A, PATH_VIEW P
   WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND
         EQUALS_PATH(P.RES, ACL_PATH) = 1;

  DBMS_XDBZ.ValidateACL(ACL_ID);
   IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_040100', 
     'connect') IS NULL THEN 
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH, 
     'APEX_040100', TRUE, 'connect'); 
  END IF;

EXCEPTION
  
  WHEN NO_DATA_FOUND THEN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
    'ACL that lets power users to connect to everywhere',
    'APEX_040100', TRUE, 'connect');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

Modifying the jdk breaks weblogic

If you have installed weblogic using jdk7 and your enterprise manager resides on a different machine then the local host you may find that you need to downgrade the jdk to make certain features between the admin and the webtier work correctly many oracle bugs are known but not documented related to this.

 

 

 

cp /app/oracle/product/ofm11g/user_projects/domains/<yourdomain>bin/setDomainEnv.sh .

vi /app/oracle/product/ofm11g/user_projects/domains/<yourdomain>/bin/setDomainEnv.sh

 

Fix this setting

SUN_JAVA_HOME="/usr/java/jdk1.6.0_33"
JAVA_HOME="/usr/java/jdk1.6.0_33"

 

Error examples in adminserver logs may look like the following

oracle.sysman.emas.ohs

[ACTIVE].ExecuteThread: '3' for queue: 'weblogic.kernel.Default (self-tuning)'

ServerPerformanceModel instantiated

ReadFile failed  Received fatal alert: unexpected_message

oracle.adf.view.rich.change.FilteredPersistenceChangeManager
 

The DocumentChange is not configured to be allowed for the component: RichTable[org.apache.myfaces.trinidad.component.UIXTable$RowKeyFacesBeanWrapper@7e3b54af, id=messagesResult]

Downgrade jdk after rpm install linux 6

rpm -qa | grep jdk

Find your package

rpm -e jdk-1.7.0_05-fcs.x86_64

 

rpm -qa | grep jdk

 

reinstall jdk

jdk-6u33-linux-x64-rpm.bin

 

lrwxrwxrwx. 1 root root   21 Aug  6 11:39 latest -> /usr/java/jdk1.6.0_33
[root@oraappman java]# java -version
java version "1.6.0_33"
Java(TM) SE Runtime Environment (build 1.6.0_33-b04)
Java HotSpot(TM) 64-Bit Server VM (build 20.8-b03, mixed mode)

convert dos file to unix format

yum install dos2unix

dos2unix <filename>

Start and Stop scripts for Oracle Enterprise manager

 

mkdir /home/oracle/bin
mkdir /home/oracle/logs
chown oracle.oinstall /home/oracle/logs
chown oracle.oinstall /home/oracle/bin

cd /home/oracle/bin

[oracle@oraappman bin]$ cat set_mw_home.sh
export MW_HOME=/app/oracle/product/ofm11g
[oracle@oraappman bin]$ cat set_domain.sh
. ~/bin/set_mw_home.sh
export DOMAIN_HOME=/app/oracle/product/ofm11g/user_projects/domains/$1
[oracle@oraappman bin]$ cat start_domain.sh
. ~/bin/set_domain.sh $1
nohup $DOMAIN_HOME/startWebLogic.sh > ~/logs/start_$1_domain.`date +%y%m%d%H%M%S`.log 2>&1 &
[oracle@oraappman bin]$ cat stop_domain.sh
. ~/bin/set_domain.sh $1
nohup $DOMAIN_HOME/bin/stopWebLogic.sh > ~/logs/stop_$1_domain.`date +%y%m%d%H%M%S`.log &

cd ~
[oracle@oraappman ~]$ cat .bashrc
# .bashrc

# Source global definitions
if [ -f /etc/bashrc ]; then
        . /etc/bashrc
fi

# User specific aliases and functions

if [ -f ~/.bash_aliases ]; then
. ~/.bash_aliases
fi

[oracle@oraappman ~]$ cat .bash_aliases
alias set_<yourdomain>='. ~/bin/set_domain.sh <yourdomain>'
alias start_<yourdomain>='. ~/bin/start_domain.sh <yourdomain>'
alias stop_<yourdomain>='. ~/bin/stop_domain.sh <yourdomain>'

alias stop_all='stop_<yourdomain>; sleep 2m; stop_X; sleep 2m; stop_X'
alias start_all='start_<yourdomain>; sleep 3m; start_X; sleep 3m; start_X'

Set up Aliases to shorten work time

vi .bashrc

# User specific aliases and functions

if [ -f ~/.bash_aliases ]; then
. ~/.bash_aliases
fi

 

vi .bash_aliases

alias set_webprod='. ~/bin/set_domain.sh web_prod'
alias start_webprod='. ~/bin/start_domain.sh web_prod'
alias stop_webprod='. ~/bin/stop_domain.sh web_prod'

alias stop_all='stop_webprod; sleep 2m; stop_X; sleep 2m; stop_X’
alias start_all='start_webprod; sleep 3m; start_X; sleep 3m; start_X'

Test snmp

Make sure you have both the libraries installed

yum install net-snmp

yum install net-snmp-utils

snmpwalk -v1 –c <communitystring> localhost

Shared pool queries

-- Whos using pga

SELECT sid,  trunc((VALUE/1024/1024)) || ' MB' "TOTAL MEMORY FOR ALL SESSIONS", value
    FROM V$SESSTAT, V$STATNAME
    WHERE NAME = 'session pga memory'
    AND V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC#
    order by 3 desc;

SELECT count(*), trunc(SUM(VALUE/1024/1024)) || ' MB' "TOTAL MEMORY FOR ALL SESSIONS"
    FROM V$SESSTAT, V$STATNAME
    WHERE NAME = 'session pga memory'
    AND V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC#;

 

select * --count(distinct kghluidx) num_subpools
    from sys.x$kghlu
  where kghlushrpool = 1;

  select * --child#, gets
   from v$latch_children
   where name = 'shared pool'
  order by child#;

select child#, gets
   from v$latch_children
   where name = 'shared pool'
   order by child#;

select * from V$SGASTAT;

select * from v$sgastat
  where pool = 'shared pool'
  and name = 'free memory';

  select name, (bytes/1024/1024/1024), resizeable from v$sgainfo;

 

More Memory queries

SELECT sid, value/1024/1024 mb  -- || ' BYTES' "TOTAL MEMORY FOR ALL SESSIONS"
    FROM V$SESSTAT, V$STATNAME
    WHERE NAME = 'session uga memory'
    AND V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC#
    and value/1024/1024 > 3;
SELECT count(*), trunc(SUM(VALUE/1024/1024)) || ' MB' "TOTAL MAX MEM FOR ALL SESSIONS"
    FROM V$SESSTAT, V$STATNAME
    WHERE NAME = 'session uga memory max'
    AND V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC#;
SELECT count(*), trunc(SUM(VALUE/1024/1024)) || ' MB' "TOTAL MEMORY FOR ALL SESSIONS"
    FROM V$SESSTAT, V$STATNAME
    WHERE NAME = 'session uga memory'
    AND V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC#;
select * from v$session where sid = 2514;

 

More queries on sqlarea for binds

select plan_hash_value, count(*) from v$sqlarea group by plan_hash_value having count(*) > 2000 order by count(*) desc ;
select sql_text, count(*)  from v$sqlarea group by sql_text; where plan_hash_value = 0 order by first_load_time ;

select substr(sql_text,1,40), count(*)  from v$sqlarea group by substr(sql_text,1,40) order by 2 desc; where upper(sql_text) like '%%' and upper(sql_text) like '%%' and first_load_time > '2012-08-02/11:30:00';  where last_load_time between '07/30/2012 17:00:00' and  '07/30/2012 19:00:00';

select plan_hash_value, first_load_time , last_load_time, loads, sql_text  from v$sqlarea where plan_hash_value = 0 and upper(sql_text) like '%%' order by last_load_time
                                                                                                  ;
select *
from v$sqlarea
where plan_hash_value = 0
and substr(sql_text,1,50) = ‘%’;
select * from error table where err_date>sysdate-365   and instr(err_message,'ORA-04031')>0 order by err_id;

 

Stats

SELECT *
  FROM stats$sysstat a, stats$snapshot b
WHERE --a.name = 'logons cumulative'
a.snap_id = b.snap_id
and b.snap_time = '08/01/2012 09:55:56'
and name like '%ses%'
;