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

/

ADRCI

 

 -- purge trace, using 

      purge -age 8640 -type TRACE 

         1440 (minutes in one day) * 6 = 8640 minutes = 6 days

reconfig_ardci.sh

for i in `adrci exec="show homes"`;do

adrci << EOF

set home $i

       set control (SHORTP_POLICY = 168)

       set control (LONGP_POLICY = 168)

       purge -age 60 -type alert

       purge -age 60 -type incident

       purge -age 60 -type trace

       purge -age 60 -type cdump

       purge -age 60 -type hm

       purge -age 60 -type  UTSCDMP

show control

EOF

Done

purge_listener.sh

for i in `adrci exec="show homes"|grep listener`;do

echo $i

echo "adrci exec=\"set home $i;show control;purge\""

adrci exec="set home $i;show control;purge";

done

sheel scripting --- tree like directory structure

-

ls -R | grep ":$" | sed -e 's/:$//' -e 's/[^-][^\/]*\//--/g' -e 's/^/ /' -e 's/-/|/'

Top-N-SQL using RANK() and DENSE_RANK()DENSE_RANK()

RANK gives you the ranking within your ordered partition. Ties are assigned the same rank, with the next ranking(s) skipped. So, if you have 3 items at rank 2, the next rank listed would be ranked 5.

DENSE_RANK again gives you the ranking within your ordered partition, but the ranks are consecutive. No ranks are skipped if there are ranks with multiple items.

- using rank()

SELECT segment_name, bytes

  FROM ( SELECT segment_name, bytes, RANK() OVER (ORDER BY bytes DESC) sal_rank

           FROM user_segments )

 WHERE sal_rank <= 5;

 -- using dense_rank()

 SELECT segment_name, bytes

  FROM ( SELECT segment_name, bytes, DENSE_RANK() OVER (ORDER BY bytes DESC) sal_rank

           FROM user_segments )

 WHERE sal_rank <= 5;