tnsnames.ora setup

system@DEMO>
system@DEMO> show parameter name

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
cell_offloadgroup_name               string
db_file_name_convert                 string
db_name                              string                           DEMO
db_unique_name                       string                           DEMO
global_names                         boolean                          FALSE
instance_name                        string                           DEMO1
lock_name_space                      string
log_file_name_convert                string
processor_group_name                 string
service_names                        string                           DEMO
system@DEMO>
system@DEMO> show parameter domain

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
db_domain                            string


system@DEMO>  select * from global_name;

GLOBAL_NAME
----------------------------------------------------------------------------------------------------
DEMO


sun001@DEMO1:/ora01/oracle/admin/network $ cat sqlnet.ora
NAMES.DIRECTORY_PATH= (TNSNAMES, LDAP, EZCONNECT)
ADR_BASE = /oramisc01/oracle
DIAG_ADR_ENABLED=true
SQLNET.SEND_TIMEOUT=10

=============================================================================

Db_name=DEMO

Service name : DEMO and DEMO_REPORT

step 1

# In database

alter system set service_name= DEMO,  DEMO_REPORT scope=both sid='*';

step 2
check sqlnet.ora and comment parameter names.default_domain 

step 3
lsnrctl reload
lsnrctl status 


step 4

# in tnsnames

DEMO_REPORT =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST <SCANNAME>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DEMO)
    )
  )


DEMO =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST <SCANNAME>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DEMO)
    )
  )

# the end

listener file on 2 nodes cluster 11gR2

cluster with 2 nodes sun001 and sun002

sun001

VIP         sun001-VIP

HOST IP          HOST=x.x.x.xx 

sun001

VIP         sun002-VIP

HOST IP          HOST=x.x.x.yy

PORT = 1521

Directory for trace and logs /oramisc01/oracle

NOTE : location of listener file is <grid_home> not <db_home>

Starting with Oracle Grid Infrastructure 11g Release 2 the node listener run out of the Oracle Grid Infrastructure home and listens on the node-VIP using the specified port (default port is 1521). 

Ever since Oracle Database 11g Release 2, SCAN is an essential part of the Oracle RAC database configuration and therefore the REMOTE_LISTENER parameter is set to the SCAN per default, assuming that the database is created using standard Oracle tools (e.g. the formerly mentioned DBCA). This allows the instances to register with the SCAN Listeners as remote listeners to provide information on what services are being provided by the instance, the current load and a recommendation on how many incoming connections should be directed to the instance.

Single Client Access Name (SCAN) is a feature used in Oracle Real Application Clusters environments that provides a single name for clients to access any Oracle Database running in a cluster.

SCAN resolves to three IP addresses using a round-robin algorithm. 

You can check the SCAN configuration in DNS using “nslookup”

READ MORE http://www.oracle.com/technetwork/products/clustering/overview/scan-129069.pdf

listener.ora and endpoints_listener.ora are in <grid_home>/network/admin directory

$ cat listener.ora on both nodes

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent

LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3))))                # line added by Agent

LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2))))                # line added by Agent

LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON                # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON                # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

# custom parameters

ADR_BASE_LISTENER = /oramisc01/oracle

ADR_BASE_LISTENER_SCAN1 = /oramisc01/oracle

ADR_BASE_LISTENER_SCAN2 = /oramisc01/oracle

ADR_BASE_LISTENER_SCAN3 = /oramisc01/oracle

$ cat endpoints_listener.ora on node 1 sun001 (notice sun001–vip, ping sun001-vip to verify)

LISTENER_sun001=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)

(HOST=sun001-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=x.x.x.xx)(PORT=1521)(IP=FIRST))))                # line added by Agent

$ cat endpoints_listener.ora on node 2 lnx70177 (notice sun002–vip, ping sun002-vip to verify)

LISTENER_sun001=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)

(HOST=sun002-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=x.x.x.yy)(PORT=1521)(IP=FIRST))))              # line added by Agent

# start/stop sequence

srvctl start listener

srvctl start scan

srvctl start scan_listener

stop in the same order as above

# How to check config

srvctl config  listener

srvctl config  scan

srvctl config  scan_listener

# How to check status

srvctl status listener

srvctl status scan

srvctl status scan_listener

# sample 

set environment to grid home

sun001@grid $  srvctl config listener

Name: LISTENER

Network: 1, Owner: oracle

Home: <CRS home>

End points: TCP:1521

sun002@grid $ srvctl config listener

Name: LISTENER

Network: 1, Owner: oracle

Home: <CRS home>

End points: TCP:1521

sun002@grid $ srvctl status listener

Listener LISTENER is enabled

Listener LISTENER is running on node(s): sun001, sun002

# Database parameter settings :  local_listener / remote_listener

The LOCAL_LISTENER parameter should be set to the node-VIP.  

The  REMOTE_LISTENER parameter should be set to the SCAN:port
   

IP-SCAN1, IP-SCAN2, IP-SCAN3 are SCAN  IP addresses of SCAN NAME
IP_VIP   are VIP IP address of NODE 1 and NODE 2

Node 1 : 
in local_listener HOST=VIP IP address of node 1

Node 2: 
in local_listener HOST=VIP IP address of node2

remote_listener remans same on both

sys@DEMO> show parameter list

NAME                            VALUE
------------------------------- ------------------------------
listener_networks            
local_listener                   (ADDRESS=(PROTOCOL=TCP)(HOST=<IP-VIP)(PORT=1521))
                                
remote_listener        (ADDRESS_LIST=
                                (ADDRESS=(PROTOCOL=TCPS)(HOST=<IP-SCAN1>)(PORT=1521))
                                (ADDRESS=(PROTOCOL=TCPS)(HOST=<IP-SCAN2>)(PORT=1521))
                                (ADDRESS=(PROTOCOL=TCPS)(HOST=<IP-SCAN3>)(PORT=1521)))
                             
 # the end                      

Processing XML files and converting to Oracle Relational Format

# SAMPLE XML File content is shown below.

XMLFILE_AA_11.xml
   <?xml version="1.0" encoding="UTF-8"?>
   <EMPDATA TIME_STAMP="2006-08-07T15:00:42"
    TRANSACTION_ID="2006-08-07T15:00:42" VERSION="1.0">
    <HEADER>
     <SENDER>
      <SENDER_NAME>XYZ Corp</SENDER_NAME>
     </SENDER>
     <RECEIVER>
      <RECEIVER_NAME>ABC Bank</RECEIVER_NAME>
     </RECEIVER>
    </HEADER>
    <BODY>
   <EMP>
         <EMPNUM>12345678</EMPNUM>
         <DEPT>07I7</DEPT>
         <LOCATION>22</LOCATION>
         <HIRE_DATE>2006-08-07 00:12:00.0</HIRE_DATE>
         <FIRST_NAME>JOHN</FIRST_NAME>
         <LAST_NAME>SMITH</LAST_NAME>
    <PHONES>
    <PHONE type="Office">5676739</PHONE>
    <PHONE type="Office">9123412432</PHONE>
    </PHONES>
     <AC_NO>12313-123123-1233</AC_NO>
     <AMOUNT>2500.00</AMOUNT>
   </EMP>
   <EMP>
         <EMPNUM>12345679</EMPNUM>
        <DEPT>07I2</DEPT>
        <LOCATION>22</LOCATION>
        <HIRE_DATE>2006-08-17 00:12:00.0</HIRE_DATE>
        <FIRST_NAME>GREG</FIRST_NAME>
        <LAST_NAME>WINTER</LAST_NAME>
     <PHONES>
    <PHONE type="Office">5676733</PHONE>
    <PHONE type="Office">9123434231</PHONE>
     </PHONES>
   <AC_NO>12313-123144_4354</AC_NO>
   <AMOUNT>2800.00</AMOUNT>
       </EMP>
   </BODY>
  </EMPDATA>

step 1
# create directory (location of xml file)

sqlplus / as sysdba
create directory TEST_DIR  as '/ora01/oracle/admin/demo/temp/';
GRANT READ, WRITE ON DIRECTORY TEST_DIR TO scott;


step 2
# Check contents of file
SELECT xmltype(bfilename('TEST_DIR','XMLFILE_AA_11.xml'), nls_charset_id('WE8ISO8859P1')) xmlcol FROM dual;

step 3
# select few columns only - empnum, ac_no and amount. Remaining tags are not selected
SELECT EXTRACTVALUE (VALUE (ctba), '/EMP/EMPNUM') emp_num,
          EXTRACTVALUE (VALUE (ctba), '/EMP/AC_NO') acct_num,
          EXTRACTVALUE (VALUE (ctba), '/EMP/AMOUNT') amount
from (
    SELECT xmltype(bfilename('TEST_DIR','XMLFILE_AA_11.xml'), nls_charset_id('WE8ISO8859P1')) xmlcol FROM dual
) t
cross join table(XMLSequence(extract(t.xmlcol,'/EMPDATA/BODY/EMP'))) ctba
/

step 4

# insert selected few columns only - empnum, ac_no and amount in table named employee. Remaining tags are not selected

create table employee (
emp_num varchar2(10),
acct_num varchar2(10),
amount  number
)
/

insert into employee (emp_num, acct_num, amount) 
SELECT EXTRACTVALUE (VALUE (ctba), '/EMP/EMPNUM') emp_num,
          EXTRACTVALUE (VALUE (ctba), '/EMP/AC_NO') acct_num,
          EXTRACTVALUE (VALUE (ctba), '/EMP/AMOUNT') amount
from (
    SELECT xmltype(bfilename('TEST_DIR','XMLFILE_AA_11.xml'), nls_charset_id('WE8ISO8859P1')) xmlcol FROM dual
) t
cross join table(XMLSequence(extract(t.xmlcol,'/EMPDATA/BODY/EMP'))) ctba
/
commit;

GRANTS to USER

-- gen_drop_objects.sql

   set pages 0 feedb off
   accept inuser char prompt 'Enter Userid: '
   -- spool run_drop_objects_&&inuser..out
   spool run_drop_objects_MAXIMO.out
   SELECT 'DROP ' || OBJECT_TYPE || ' ' || owner||'.'||OBJECT_NAME ||' CASCADE CONSTRAINTS;'
   FROM   dba_objects
   WHERE OBJECT_TYPE = 'TABLE' and owner = upper('&inuser')
   union all
   select 'drop '||case
            when object_type='PACKAGE BODY' then 'PACKAGE'
            else object_type
          end
     ||' '||owner||'.'||object_name||' ;'
   from dba_objects
   where owner = upper('&inuser')
   /
   prompt purge dba_recyclebin
   prompt /
   
   spool off
   
   
-- gen_rdonly_grants_to_role.sql

set pages 0 lines 120 feedback off verify off
accept ToRole prompt 'Grants to What Role? '
accept ObjOwner prompt 'From What Owner? '
spool gen_rdonly_grants_to_role.out
select 'set echo on' from dual;
select 'create role &&ToRole;' from dual;
select 'connect &&ObjOwner' from dual;
select 'grant '||case object_type when 'TABLE' then 'select '
                                  when 'VIEW'  then 'select '
                 end
  ||'on '||object_name||' to &&ToRole;'
from dba_objects
where owner=upper('&&ObjOwner')
  and object_type in ('TABLE','VIEW')
/
spool off
set pages 30 feedback on

--  gen_grants_to_role.sql

set pages 0 lines 120 feedback off verify off
accept ToRole prompt 'Grants to What Role? '
accept ObjOwner prompt 'From What Owner? '
spool gen_grants_to_role.out
select 'set echo on' from dual;
select 'create role &&ToRole;' from dual;
select 'connect &&ObjOwner' from dual;
select 'grant '||case object_type when 'TABLE' then 'select, insert, update, delete '
                                  when 'VIEW'  then 'select '
                                  when 'SEQUENCE' then 'select '
                                  when 'PROCEDURE' then 'execute '
                                  when 'PACKAGE' then 'execute '
                 end
  ||'on '||object_name||' to &&ToRole;'
from dba_objects
where owner=upper('&&ObjOwner')
  and object_type in ('TABLE','VIEW','SEQUENCE','PROCEDURE','PACKAGE')
/
spool off
set pages 30 feedback on


-- gen_new_grants.sql

set feedback off pages 0 lines 100
spool run_new_grants.out
select 'create synonym SCOTT_USER.'||object_name||' for '||owner||'.'||object_name||';'
from dba_objects
where owner='SCOTT_OWN' and object_name in (
select object_name from dba_objects where owner='SCOTT_OWN' and object_type in ('PROCEDURE','PACKAGE','FUNCTION','TABLE')
minus
select synonym_name from dba_synonyms where table_owner='SCOTT_OWN')
/


select case
       when object_type in ('TABLE') then
       'grant select,insert,update,delete on '||owner||'.'||object_name||' to SCOTT_USER;'
       when object_type in ('VIEW','SEQUENCE') then
       'grant select on '||owner||'.'||object_name||' to SCOTT_USER;'
       else
       'grant execute on '||owner||'.'||object_name||' to SCOTT_USER;'
       end
from dba_objects
where owner='SCOTT_OWN' and object_name in (
select object_name from dba_objects where owner='SCOTT_OWN' and object_type in ('TABLE','VIEW','SEQUENCE','PROCEDURE','PACKAGE','FUNCTION')
minus
select distinct table_name from dba_tab_privs where owner='SCOTT_OWN' and grantee='CSX_SCOTT_USER_ROLE')
/
spool off
set feedback on pages 24 lines 140

TOP-N Schema List

SELECT owner, wsize  "Size in MB"
  FROM ( SELECT owner, sum(bytes/(1024*1024)) wsize, RANK() OVER (ORDER BY sum(bytes/(1024*1024)) DESC) sal_rank
           FROM dba_segments group by owner)
 WHERE sal_rank <= 5;

 SELECT owner, wsize  "Size in MB"
  FROM ( SELECT owner, sum(bytes/(1024*1024)) wsize, DENSE_RANK() OVER (ORDER BY sum(bytes/(1024*1024)) DESC) sal_rank
           FROM dba_segments group by owner)
 WHERE sal_rank <= 5;

User with Read only Access

CREATE USER scott_user IDENTIFIED BY scott_user 
DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT;
-- From Role Privileges
GRANT COMPANY_RDONLY_ROLE TO scott_user;
GRANT COMPANY_CONNECT TOscott_user;

CREATE OR REPLACE TRIGGER scott_user.after_logon_trg
AFTER LOGON ON scott_user.SCHEMA
BEGIN
  DBMS_APPLICATION_INFO.set_module(USER, 'Initialized');
  EXECUTE IMMEDIATE 'ALTER SESSION SET current_schema=SCOTT_OWN';
END;
/

expdp flashback_time tips

userid="sys/don as sysdba"
job_name=job1
# parallel=2
# Flashback_time at specific time
FLASHBACK_TIME="TO_TIMESTAMP('13-05-2010 13:20:00', 'DD-MM-YYYY HH24:MI:SS')"

# Flashback_time at current time
FLASHBACK_TIME="TO_TIMESTAMP(TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')"

# Flashback_time at current time
flashback_time=systimestamp

# Flashback_time at daily time example 11PM
flashback_time="to_timestamp(to_char(trunc(sysdate)+22/24,'YYYY-MM-DD'),'YYYY-MM-DD')"

directory=dpdump
filesize=20000000000
EXCLUDE=statistics
SCHEMAS=SCOTT
dumpfile=expdp_demo_scott_%U.dmp
logfile=expdp_demo_scott.log
COMPRESSION=ALL
CLUSTER=N
CONTENT=METADATA_ONLY

Moving Audit table AUD$ to its own tablespace

   select owner, tablespace_name from dba_tables where table_name='AUD$';
   
   CREATE SMALLFILE TABLESPACE SCOTT_AUDIT 
   DATAFILE SIZE 100M 
   AUTOEXTEND ON
   NEXT 100K 
   MAXSIZE UNLIMITED 
   LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
   SEGMENT SPACE MANAGEMENT AUTO  DEFAULT COMPRESS FOR OLTP;
   
   create table audx tablespace SCOTT_AUDIT
      storage (initial 50k next 50k pctincrease 0)
         as select * from aud$ where 1 = 2 ;
   
   rename AUD$ to AUD$_drop ;
   rename audx to aud$ ;
   create index i_aud1
     on aud$(sessionid, ses$tid)
       tablespace SCOTT_AUDIT storage(initial 50k next 50k pctincrease 0) ;
   
   drop table AUD$_drop ;
   purge dba_recyclebin;
   select owner, tablespace_name from dba_tables where table_name='AUD$';
   
   set audit_trail=NONE in init.ora and create spfile 
   
   -- production only
   sqlplus / as sysdba
   audit dba;
   
   select bytes from dba_segments where segment_name='AUD$'
   select bytes from dba_segments where segment_name='AUD$_drop';
   select count(1) from aud$;
   
   These views will show you what auditing is configured:
      Select * from dba_priv_audit_opts;
      Select * from dba_stmt_audit_opts;
      Select * from dba_obj_audit_opts;

Managing Aud$

show parameter audit

-- check tablespaces

SELECT table_name, tablespace_name

FROM   dba_tables

WHERE  table_name IN ('AUD$', 'FGA_LOG$')

ORDER BY table_name;

-- when last deleted

SELECT * FROM dba_audit_mgmt_last_arch_ts;

-- total # of records

SELECT COUNT(*) FROM aud$;

SCOTT_AUDIT is tablespace name

-- change for AUD$

BEGIN

  DBMS_AUDIT_MGMT.set_audit_trail_location(

    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,

    audit_trail_location_value => 'SCOTT_AUDIT');

END;

/

-- change for FGA_LOG$

BEGIN

  DBMS_AUDIT_MGMT.set_audit_trail_location(

    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,

    audit_trail_location_value => 'SCOTT_AUDIT');

END;

/

-- both in single step

BEGIN

  DBMS_AUDIT_MGMT.set_audit_trail_location(

    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,

    audit_trail_location_value => 'SCOTT_AUDIT');

END;

/

-- Controlling the Size and Age of the OS Audit Trail

COLUMN parameter_name FORMAT A30

COLUMN parameter_value FORMAT A20

COLUMN audit_trail FORMAT A20

SELECT * FROM   dba_audit_mgmt_config_params

-- Purging Audit Trail Records

COLUMN parameter_name FORMAT A30

COLUMN parameter_value FORMAT A20

COLUMN audit_trail FORMAT A20

SELECT * FROM dba_audit_mgmt_config_params;

BEGIN

  DBMS_AUDIT_MGMT.init_cleanup(

    audit_trail_type         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,

    default_cleanup_interval => 12 /* hours */);

END;

/

SET SERVEROUTPUT ON

BEGIN

  IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN

    DBMS_OUTPUT.put_line('YES');

  ELSE

    DBMS_OUTPUT.put_line('NO');

  END IF;

END;

/

-- how much data you wish to purge

COLUMN audit_trail FORMAT A20

COLUMN last_archive_ts FORMAT A40

SELECT * FROM dba_audit_mgmt_last_arch_ts;

-- to deleted manualy, must run DBMS_AUDIT_MGMT.init_cleanup before this

BEGIN

  DBMS_AUDIT_MGMT.clean_audit_trail(

   audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,

   use_last_arch_timestamp => FALSE);

END;

/

CATALOG missing ARCHIVE log file / RMAN

rman target /
RMAN> CATALOG ARCHIVELOG '/u01/scott/2_4553_621298904.dbf';
RMAN> list archivelog all;
exit;

Copy files from ASM to filesystem

sqlplus / as sysdba
CREATE OR REPLACE DIRECTORY log_files AS '+FRA1/demo/archive_logs' ;
CREATE DIRECTORY DSK_FILES AS '/u01/scott';
exec DBMS_FILE_TRANSFER.COPY_FILE ( 'log_files' , '2_4553_621298904.dbf' , 'dsk_files' , '2_4553_621298904.dbf' );

Recovering Standby Database - Archive log missing from Standby

on PRIMARY:
mkdir /u01/scott

rman target /
RMAN> list archivelog all;
List of Archived Log Copies
Key Thrd Seq S Low Time Name
9833 2 4553 A 07-NOV-11 +FRA1/demo/archive_logs/2_4553_621298904.dbf
exit


sqlplus / as sysdba
CREATE OR REPLACE DIRECTORY log_files AS '+FRA1/demo/archive_logs' ;
CREATE DIRECTORY DSK_FILES AS '/u01/scott';
exec DBMS_FILE_TRANSFER.COPY_FILE ( 'log_files' , '2_4553_621298904.dbf' , 'dsk_files' , '2_4553_621298904.dbf' );

on STANDBY:
mkdir /u01/scott
cd /u01/scott
scp PRIMARY_HOST_IP_ADDRESS:/u01/scott/2_4553_621298904.dbf .
rman target /
RMAN> CATALOG ARCHIVELOG '/u01/scott/2_4553_621298904.dbf';
RMAN> list archivelog all;
exit;

ora-1111 , ora-1110 RMAN Recovery

-- RMAN  recovery terminated on test server as new file was added on prod  and RMAN fails to create the new datafile  on test server

ora-1111
ora-1110

Metalink doc id 739618.1

alter database create datafile 

       '/ora01/oracle/product/11.2.0.4/db/dbs/UNNAMED00068'
as  

      '/oradata01/oracle/oradata/DEMO/SCOTT_DATA10.dbf';

Substring - Shell Command

$ export DUMP_PATH=/tmp/products/exp_full_DEMO_1_1.dmp

#delete matching pattern from beginning

$ export TMP=${DUMP_PATH#*/exp_}  $ echo $TMP

full_DEMO_1_1.dmp

#delete matching pattern from end

$ export EXPORT_FILE=${TMP%.dmp}  

$ echo $EXPORT_FILE

full_DEMO_1_1

Hidden init.ora parameter

select name, value from V$PARAMETER where name like '\_%' escape '\'  and ISDEFAULT='FALSE';

expdp full database export / Shell Script

expdp_full_db.ksh

NOTE : change DP1, ORACLE_HOME, DIRECTORY, MAIL and  adjust days before removing dump

#!/bin/ksh
export ORACLE_SID=$1
export ORACLE_HOME=/ora01/oracle/product/11.2.0.4/db
export PATH=$ORACLE_HOME/bin:$PATH
export EXPDATE=`date "+%m%d_%H%M"`
export DP1=/oramisc01/oracle/export/${ORACLE_SID}

$ORACLE_HOME/bin/expdp \'/ as sysdba\'  job_name=job1 directory=dpdump dumpfile=expdp_$ORACLE_SID}_full_${EXPDATE}.dmp full=y flashback_time=\"TO_TIMESTAMP\(TO_CHAR\(SYSDATE,\'YYYY-MM-DD HH24:MI:SS\'\),\'YYYY-MM-DD HH24:MI:SS\'\)\" logfile=expdp_{ORACLE_SID}_full_${EXPDATE}.log statistics=none COMPRESSION=ALL
CLUSTER=N  filesize=20000000000 parallel=1

/bin/gzip ${DP1}/expdp_$ORACLE_SID}_full_${EXPDATE}.dmp

find ${DP1} -name exp_$ORACLE_SID}\* -mtime +7 -exec rm {} \;

tail -15 ${DP1}/expdp_$ORACLE_SID}_full_${EXPDATE}.log | mailx -s "$ORACLE_SID} database export output" abx@gmail.com

# of partitions in schema

-- space used schema wise

select owner, sum(bytes/(1024*1024*1024))  from dba_segments group by rollup(owner)

-- find partitios, subpartition high level info along with # of partition

with

   temp_low as (

               select table_name, PARTITION_NAME from user_tab_partitions a where  PARTITION_POSITION in (

               select min(PARTITION_POSITION) from user_tab_partitions b where a.table_name=b.table_name)

              )

  ,temp_high as (

               select table_name, PARTITION_NAME from user_tab_partitions a where  PARTITION_POSITION in (

               select max(PARTITION_POSITION)-1 from user_tab_partitions b where a.table_name=b.table_name)

              )

select a.table_name, a.PARTITION_NAME, b.PARTITION_NAME, c.PARTITION_COUNT, decode(c.SUBPARTITIONING_KEY_COUNT,1,'YES','NO') as SUBPARTITION

from temp_low a, temp_high b, user_part_tables c

where a.table_name=b.table_name

and a.table_name=c.table_name

/

sample report

TABLE_NAME                     starting_partition     end_partition                        #         SUBP

------------------------------ ------------------ ------------------ ---------------- ------

BOS_MESSAGE_LOG                P_20130307         P_20140802                      514  NO

AWR, ADDM and ASH

# AWR, ADDM and ASH report for the period.

   sqlplus / as sysdba

   @?/rdbms/admin/ashrpt.sql    # activity during a period of time

   @?/rdbms/admin/awrrpt.sql    # database performance within a specified period of time

   @?/rdbms/admin/awrrpti.sql   # Workload Repository Report Instance (RAC)

   @?/rdbms/admin/addmrpt.sql   # database performance findings

   @?/rdbms/admin/awrsqrpt.sql  # historical performance of a single SQL statement (identified by SQL_ID)

   @?/rdbms/admin/sqltrpt.sql

# In 11gR2 there are two new scripts awrgrpt.sql AND awrgdrpt.sql for RAC

awrgrpt.sql -- AWR Global Report (RAC) (global report)

awrgdrpt.sql -- AWR Global Diff Report (RAC)

# Some other important scripts under $ORACLE_HOME/rdbms/admin

spawrrac.sql -- Server Performance RAC report

awrddrpt.sql -- Period diff on current instance

# AWR

  Create

     EXECUTE dbms_workload_repository.create_snapshot();

     

  Frequency

     begin DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(1440,15); end;

     # every 15 minutes in 1440 (one day)

   

  Drop

     exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 22,

                           high_snap_id => 32, dbid => 3310949047);

     

  Modify    interval and time                

     exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 43200,

                 interval => 30, dbid => 123456789); #retention is in mins

    Modify    interval and topnsql                

Exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 43200,

interval => 30, topnsql => 100, dbid => 123456789);

# awr views

select owner, view_name

from dba_views

where view_name like 'DBA\_HIST\_%' escape '\';

 

# awr cpu usage report

select a.metric_name, a.begin_time, a.intsize, a.num_interval,

a.minval, a.maxval, a.average, a.standard_deviation sd, b.metric_unit

from dba_hist_sysmetric_summary a, dba_hist_metric_name b

where a.metric_id = 2075

and a.metric_id=b.metric_id

order by 2

/