Oracle Systems Administrator: 2012

cannot open `/usr/share/sendmail-cf/m4/cf.m4': No such file or directory

yum install sendmail-cf

switching sendmail from Host to Time on confQueue_SORT_ORDER

I have seen issues with Comcast greylisting then blocking large volume domains. I am going to try to make this configuration to see if this helps. What I believe happens is they greylist and then on retry all the messages are in order by host which makes Comcast see a much large queue all at once which then makes them block due to quantity. I believe this because our normal quantities are not an unacceptable range based on their site and our senderscore. I believe we just drive it higher based on hold messages all going out in order of host which seems to be the default of new versions of sendmail.

define(`confQUEUE_SORT_ORDER', `Host')dnl

define(`confQUEUE_SORT_ORDER', `Time')dnl

use impdp and expdp to import into another schema

<username>: A system admin user name
<password>: The system admin password
<exportschema>: The export schema name
<dba_directoryname>: A dba_directory name
<exportname>: A name to identify this export file
<date>: Today’s date

expdp <username>/<password> schemas=<exportschema> directory=<dba_directoryname> dumpfile=<exportname><date>.dmp logfile=exp_<exportname><date>.log

if needed create the user

create user <importschema> identified by <importschema_password> profile <userprofile> default tablespace <tablespace>;

impdp <username>/<password> remap_schema=<exportschema>:<importschema> directory=<dba_directoryname> dumpfile=<exportname><date>.dmp logfile=imp_<exportname><date>.log

Ctrl+c or Ctrl+v Dos

In a windows dos prompt the ctrl+c or ctrl+v options do not work. To support faster editing you can enable quickedit mode in the dos prompt to allow highlighting and pressing enter to perform the same thing as ctrl+c and crtl+v in windows.

Enable quick edit mode in dos

create and drop tablespace local filesystem

DROP TABLESPACE <tablespacename> INCLUDING CONTENTS AND DATAFILES;

create tablespace <tablespacename> datafile '<path><filename>' SIZE 10G ONLINE;

plsqldev ezconnect

Some versions of plsqldev do not like the ezconnect fully qualified syntak of connection “//<servername>/<servicename>”. These seem to work correctly when removing the first two slashes as “<servername>/<servicename>”.

This may be a difference in sql net rather then plsqldev but in either case this change seems to work correctly.

Stale NFS file handle remount drive

mount -o remount /appbackup

Adding access for relay to sendmail

vi /etc/mail/access

Add your allowed relay locationas

Connect:localhost.localdomain           RELAY
Connect:localhost                       RELAY
Connect:127.0.0.1                       RELAY
Connect:192.168.0                           RELAY

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

vi /etc/mail/instance1sendmail.mc

Add

FEATURE(`access_db', `hash -T<TMPF> -o /etc/mail/access.db')dnl

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

restart mail service

ps –ef | grep sendmail

kill –9 <processid>

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

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

netstat -lan | grep 25

Adding smtp records to mailertable for sendmail

vi /etc/mail/mailertable

Add your smtp record

snapdedo.com smtp:[192.168.0.220]

makemap hash /etc/mail/mailertable </etc/mail/mailertable

vi /etc/mail/instance1sendmail.mc

Add

FEATURE(`mailertable', `hash -o /etc/mail/mailertable.db')dnl

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

restart mail service

ps –ef | grep sendmail

kill –9 <processid>

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

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

netstat -lan | grep 25

ASM Disk usage check

create or replace
procedure Check_Disk_Size (vCC_List varChar2)

-- grant select on V$ASM_DISKGROUP to <user>;
-- grant select on v$database to <user>;
-- grant execute on utl_mail to <user>;
/*
execute DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => '/sys/acls/outbound_mail.xml',
                                       principal => '<user>',
                                       is_grant => true,
                                       privilege => 'connect');
*/

vMessage varChar2(32000) := '';
vSubject varChar2(80) := '';

vMail_List varChar2(32000) :=’<email>’;

cursor get_diskspace is
select name, trunc(total_mb/1024) "TOTAL", trunc(free_mb/1024) "FREE"
from V$ASM_DISKGROUP;
DiskSpace get_diskspace%ROWTYPE;

begin
    select 'Disk Usage ' || upper(name) || ' DB'
     into vSubject
     from v$database;
    vMessage := 'ASM Disk Usage' || chr(10) || chr(13);
   for DiskSpace in get_diskspace
    loop
     vMessage := vMessage || 'Disk Name: ' || DiskSpace.name || ' Total: ' || DiskSpace.TOTAL || ' FREE: ' || DiskSpace.FREE || chr(10) || chr(13);
    end loop;
   utl_mail.send(‘<email>’, vMail_List, vcc_List, NULL, vSubject,     vMessage, 'text/plain; charset=us-ascii', NULL);
   commit;
exception
when others then
dbms_output.put_line(substr(sqlerrm,1,255));
end;

Install utl mail for oracle email

sqlplus sys/<pwd>
SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql
SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb

export schema without data

“content=METADATA_ONLY“

script=$0
username=$1
password=$2
dbconnect=$3
mydt=$4
echo $mydt
sqlplus $username/$password@<dnsname>/<servicename> as sysdba << EOF1
CREATE OR REPLACE DIRECTORY stjdump AS '/app/oracle/oracledump';
exit;
EOF1
expdp $username/$password@web_prod/web_prod schemas=<schemaname1>,<schemaname2>content=METADATA_ONLY directory=stjdump dumpfile=audit.$mydt.dmp logfile=exp_audit.log

Move items indexes in tablespaces to different tablespace.

<current_tablespace> =
<new_tablespace1> =
<new_tablespace2> =

Select ' ALTER INDEX ' || owner || '.'||INDEX_NAME ||' rebuild tablespace <current_tablespace>;'
from DBA_indexes
where index_name in ( select segment_name from dba_segments where tablespace_name in (<new_tablespace1>,<new_tablespace2>));

Windows crashes with blue screen windows 7 64 bit running vm server

Checking system with win debugger

http://msdn.microsoft.com/en-us/windows/hardware/gg463009.aspx

This will install the latest windows 7 debugger.

Choose Debugger during install.

Run as Administrator

Navigate to “.dmp” could be located in several places depending on the dump type.

Memory dump is located at c:\windows\minidump

Found vmware crash.

Looks like multiple possibilities

Most common posts point to memory so memory test first.

http://communities.vmware.com/thread/20606

Generate an error log table for oracle bulk inserting or inserting

To save from having to rollback an entire bulk insert or multiple inserts you can add a err$ table

Example:

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

forall i in vOrder_id.first .. vOrder_id.last
INSERT /*+ APPEND */ INTO TBLORDERS (order_id)
values (vOrder_id(i));
LOG ERRORS INTO err$_TBLORDERS ('INSERT') REJECT LIMIT UNLIMITED;

select * from err$_TBLORDERS;

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

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

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

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

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.

. 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

. 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

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

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;

on ohs server

modify dads.conf

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>

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 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%'
;

Shut off sleep and hibernate on windows server

Shut off Sleep

>> System and security

>> Power Options

>> Change when the computer sleeps

>> Never

Shut off hibernate

Click Start, and then type cmd in the Start Search box.
In the search results list, right-click Command Prompt, and then click Run as Administrator.
When you are prompted by User Account Control, click Continue.
At the command prompt, type powercfg.exe /hibernate off, and then press ENTER.
Type exit and then press ENTER to close the Command Prompt window.

Secure ssl for pci fusion middleware

The default out of the box configuration for ssl in fusion middleware will most likely not meet your pci standards you’ll want to disable weak ciphers and backward compatibility for version 2 of ssl.

Go to

fusion middleware enterprise manager

select your ohs

right click and choose virtual hosts

click you host name and select configure ssl configurations

uncheck as below

-- ssl_rsa_with_3des_ede_cbc_sha

-- ssl_rsa_with_des_cbc_sha

in protocols unselect

v3_v2hello

syncing files to network drive

Windows has a nice way to keep network drives online when your working offline.

Right click the folder and “Always Available Offline”

This may not work well for large folders but it works great for source and documentation folders.

chksum check or failure invalid entry size oracle installer

When downloading an oracle patch file, it is highly important that you run a checksum test to verify the download file.
It is a common error to have a problem in the download and the zip work correctly and even a large portion of the install work before a failure occurs with an error like “invalid entry size”.

It’s easy to check the checksum of the file before working with it to be sure you have a good copy.

In linux

cksum <filename>

md5sum <filename>

sha1sum <filename>

Works well for an oracle file since an md5 is usually available right from the download location.

ofm11g mbeam problems debugging Fusion Middleware Control

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

[root@oraappman Desktop]# cd /app/oracle/product/ofm11g/user_projects/domains/web_dev/servers/AdminServer/upload/NonJ2EEManagement/11.1.1/app/
[root@oraappman app]# ls -l
total 4300
-rw-r----- 1 oracle oinstall 4401039 Jul 12 09:26 NonJ2EEManagement.ear
[root@oraappman app]# cp NonJ2EEManagement.ear NonJ2EEManagement.ear.orig
[root@oraappman app]# cp ~/Downloads/NonJ2EEManagement.ear .
cp: overwrite `./NonJ2EEManagement.ear'? y
[root@oraappman app]# ls -l
total 8600
-rw-r----- 1 oracle oinstall 4400595 Jul 12 09:44 NonJ2EEManagement.ear
-rw-r----- 1 root root 4401039 Jul 12 09:44 NonJ2EEManagement.ear.orig
[root@oraappman app]#

weblogic

Fusion Middeware Update

Restart the Adminserver

stop_webdev <- Use a created alias for ease of shortcuts (see my shortcut guide)
start_webdev

Restart the instance: opmnctl stopall/startall

Stop_web <- use and alias

start_web

Reproduce error

Using a standard port with oracle ofm11g ohs server

$INSTANCE_HOME/bin/opmnctl stopproc ias-component=ohs1

export ORACLE_HOME=/app/oracle/product/ofm11g/WebTier1

cd $ORACLE_HOME/ohs/bin
chown root .apachectl
chmod 6750 .apachectl

cp /app/oracle/product/ofm11g/WebTier1/instances/instance1/config/OHS/ohs1/httpd.conf /app/oracle/product/ofm11g/WebTier1/instances/instance1/config/OHS/ohs1/httpd.conf.20120712

vi /app/oracle/product/ofm11g/WebTier1/instances/instance1/config/OHS/ohs1/httpd.conf

verify User and Group = oracle oinstall

$INSTANCE_HOME/bin/opmnctl startproc ias-component=ohs1