Oracle Systems Administrator: September 2012

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;

Pages