Auto create PS Records and Views

This code creates PS record object Record & Views:

Data Pump

source: http://arjudba.blogspot.com/2009/04/data-pump-process-architecture-master.html 

select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid,
    s.status, s.username, d.job_name, p.spid, s.serial#, p.pid
from v$session s, v$process p, dba_datapump_sessions d
where p.addr=s.paddr and s.saddr=d.saddr;

Kill, cancel and resume or restart datapump expdp and impdp jobs

http://blog.oracle48.nl/killing-and-resuming-datapump-expdp-and-impdp-jobs/

SET serveroutput on
SET lines 100
DECLARE
   h1 NUMBER;
BEGIN
  -- Format: DBMS_DATAPUMP.ATTACH('[job_name]','[owner_name]');
   h1 := DBMS_DATAPUMP.ATTACH('SYS_IMPORT_SCHEMA_01','SCHEMA_USER');
   DBMS_DATAPUMP.STOP_JOB (h1,1,0);
END;
/
 

--- quick data setup (http://www.oracle-developer.net/display.php?id=515)

cREATE TABLE t
    AS
       SELECT ROWNUM                     AS id
       ,      MOD(ROWNUM,2000)           AS grp
       ,      DBMS_RANDOM.STRING('u',5)  AS val
       ,      DBMS_RANDOM.STRING('u',30) AS pad
       FROM   dual
       CONNECT BY ROWNUM <= 100000;
       
exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'T'); 

check tables spaces 

SELECT t.tablespace_name "Tablespace",
       'Datafile' "File Type",
       t.status "Tablespace Status",
       d.status "File Status",
       ROUND((d.max_bytes - NVL(f.sum_bytes, 0))/1024/1024) "Used MB",
       ROUND(NVL(f.sum_bytes, 0)/1024/1024) "Free MB",
       t.initial_extent "Initial Extent",
       t.next_extent "Next Extent",
       t.min_extents "Min Extents",
       t.max_extents "Max Extents",
       t.pct_increase "Pct Increase",
       d.file_name "Datafile name",
       d.file_id
 FROM (SELECT tablespace_name, file_id, SUM(bytes) sum_bytes
       FROM   DBA_FREE_SPACE
       GROUP BY tablespace_name, file_id) f,
      (SELECT tablespace_name, file_name, file_id, MAX(bytes) max_bytes, status
       FROM DBA_DATA_FILES
       GROUP BY tablespace_name, file_name, file_id, status) d,
      DBA_TABLESPACES t
WHERE t.tablespace_name = d.tablespace_name
AND   f.tablespace_name(+) = d.tablespace_name
AND   f.file_id(+) = d.file_id
GROUP BY t.tablespace_name, d.file_name, d.file_id, t.initial_extent,
         t.next_extent, t.min_extents, t.max_extents,
         t.pct_increase, t.status, d.max_bytes, f.sum_bytes, d.status
UNION ALL
SELECT h.tablespace_name,
       'Tempfile',
       ts.status,
       t.status,
       ROUND(SUM(NVL(p.bytes_used, 0))/ 1048576),
       ROUND(SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / 1048576),
       -1, -- initial extent
       -1, -- initial extent
       -1, -- min extents
       -1, -- max extents
       -1, -- pct increase
       t.file_name,
       t.file_id
FROM   sys.V_$TEMP_SPACE_HEADER h, sys.V_$TEMP_EXTENT_POOL p, sys.DBA_TEMP_FILES t, sys.dba_tablespaces ts
WHERE  p.file_id(+) = h.file_id
AND    p.tablespace_name(+) = h.tablespace_name
AND    h.file_id = t.file_id
AND    h.tablespace_name = t.tablespace_name
and    ts.tablespace_name = h.tablespace_name
GROUP BY h.tablespace_name, t.status, t.file_name, t.file_id, ts.status
ORDER BY 1, 5 DESC

Cleanup (http://arjudba.blogspot.com/2009/05/how-to-cleanup-orphaned-datapump-jobs.html) 

SELECT * FROM dba_datapump_jobs;

DROP TABLE xxx.SYS_EXPORT_SCHEMA_01;

PURGE TABLE xxx.SYS_EXPORT_SCHEMA_01; (if table in recycle bin)

need imp/exp Privgs, to show all for user:

SELECT level,LPAD(' ', 2*level) || granted_role "USER PRIVS"
FROM (
  SELECT NULL grantee,  username granted_role
  FROM dba_users
  WHERE username LIKE UPPER('%XXX%')
  UNION
  SELECT grantee, granted_role
  FROM dba_role_privs
  UNION
  SELECT grantee, privilege
  FROM dba_sys_privs)
START WITH grantee IS NULL
CONNECT BY grantee = prior granted_role; 

SELECT  DBMS_METADATA.GET_DEPENDENT_DDL('TRIGGER','table','schema') from dual; 

SELECT  DBMS_METADATA.GET_DEPENDENT_DDL('INDEX','t','s') from dual;

SELECT  DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT','t','s') from dual 

select DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','XXX') from dual;

select DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','XXX') from dual;

select DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','XXX') from dual; 

Objects that can be data pumped with INCLUDE at levels:

DATABASE_EXPORT_OBJECTS,

SCHEMA_EXPORT_OBJECTS,

TABLE_EXPORT_OBJECTS. 

See how much space by a table: 

select owner, segment_name, bytes/(1024*1024) "MB"
from dba_segments
where owner = ''
and segment_type = 'TABLE'
and segment_name = '';

nmon

nmon is part of aix and can shared code with topas, toggle between nmon & topas with '~'.

report top processed in a file 2 sec X 2 cycles : nmon -F aaa -c 2 -s 2 -T

official page: http://www.ibm.com/developerworks/wikis/display/WikiPtype/nmon
utils: web report - http://www.ibm.com/developerworks/wikis/display/WikiPtype/nmon_web_report


 ----
Memory

• physical:

lsattr -El sys0 -a realmem | awk {'print $2'}  (in bytes)
lsattr -El mem0 (in MBs)
svmon -G | head -2|tail -1| awk {'print $2'} ( in pages)

• in use:

svmon -G | head -2|tail -1| awk {'print $3'} (in pages)
svmon -P pid

• VM

Check Installed Compoments

select * from product_component_version

http://stackoverflow.com/questions/7800027/how-do-i-load-os-variables-into-pl-sql

select * from user_errors

Run OS command from PL/SQL: http://www.toadworld.com/Blogs/tabid/67/EntryId/356/Run-an-OS-command-from-within-PL-SQL.aspx

Sendmail

• verify the user IDs: sendmail -bv
• verbose mode:
  

> (echo "From: xxx.yyy@`hostname`"
echo "To: aaa.bbb@host.com"
echo "Subject: test1"
echo $(date)
) | sendmail -v -t -f send@host.com

• lookup ns & mx:

> host -n google.com
google.com has address 74.125.159.99
google.com has address 74.125.159.103
google.com has address 74.125.159.104
google.com has address 74.125.159.105
google.com has address 74.125.159.106
google.com has address 74.125.159.147
google.com mail is handled (pri=40) by alt3.aspmx.l.google.com
google.com mail is handled (pri=50) by alt4.aspmx.l.google.com
google.com mail is handled (pri=10) by aspmx.l.google.com
google.com mail is handled (pri=20) by alt1.aspmx.l.google.com
google.com mail is handled (pri=30) by alt2.aspmx.l.google.com

• debug mode:

> sendmail -bt

ADDRESS TEST MODE (ruleset 3 NOT automatically invoked)
Enter  

> 3,0 xxx@yyy.com


Online Network Tools:  http://centralops.net/co/


/etc/hosts works with host or ping
nslookup looks at DNS server (default domain=Name is the value specified in the system's hostname, /etc/resolv.conf, or LOCALDOMAIN file)

What are MX Records? (source: http://mx-records.e-dns.org/)

Mail exchange (MX) records are resources maintained by a domain name system (DNS) that route email traffic to the correct mail server. Just as DNS helps point browsers to the correct IP address for retrieving web pages, MX records are the part of the DNS that help email messages reach their final destination. Remember from our article on Domain Name Systems that a domain name, such as example.com, is not an address in itself. Instead, a domain name is a reference to the IP address. As you know, email addresses include a username, or mailbox and a domain name separated by an @ sign, e.g. name@example.com. MX record ensures that incoming mail gets routed to the correct mail server that’s associated with the domain name portion of an email address.

When you send an email, your mail transfer agent sends a request to the DNS for the domain to look up the MX records. The MX records will relay the hostnames where mail is being accepted to the mail transfer agent. Once the hostname is determined, your mail transfer agent can make an SMTP connection with the recipient’s mail server in order to send the email.

Oftentimes, there will be multiple MX records for each domain. This is to provide backup mail exchange records in case the primary server gets overloaded or is temporarily down. Also, MX records are where spam filtering software is located. Before resolving the recipient’s domain name for your mail transfer agent, the spam software will analyze your message for signs of malicious content, viruses or evidence of spam. MX records will often reference blacklists, which is list of domains or hosts for known or suspected spammers. Emails from these sources will be rejected on the mail server level.

Occasionally, legitimate email users may be added to one or more blacklists. If this occurs, the IP blacklist lookup tool can be helpful for checking to see which, if any, blacklists your domain is on. If your domain or IP address appears on an DNS blacklist, you can usually contact the party that maintains the blacklist to have it removed.

If you want to check to see which mail server an incoming email is coming from, you can use the MX records lookup tool. Spammers often send spam from fake email addresses. If the MX records lookup tool cannot find any mail servers for an email address, it may mean that the email address is spoofed or invalid. You can also check to see if the mail is coming from a mail server that has been blacklisted by cross-referencing it on the IP blacklist lookup tool.

Files related....

fuser: Identifies processes using a file or file structure
whodo: Lists the jobs being performed by users on the system.
procfiles:list symbols from object files
lsof -p
ls -l /proc//fd

vmstat, mpstat, iostat, sar

ar: list library contents (.a)
nm: list symbols from object files (.a, .so, .o)
ldd: Lists dynamic dependencies (XCOFF) ($LD_LIBRARY_PATH)
what: Displays identifying information in files. (.a, .so, .o, XCOFF), work with GET/SCCS