Create perfstat user, with permissions and jobs

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

Create perfstat user and jobs

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

set_statspack.sh

#!/bin/bash

. /etc/sh/orash/oracle_login.sh igt

sqlplus /nolog << EOF

connect / as sysdba

define perfstat_password=passwd

define default_tablespace=WORKAREA

define temporary_tablespace=TEMPORARY

@?/rdbms/admin/spcreate.sql

GRANT CREATE JOB TO PERFSTAT;

connect perfstat/&&perfstat_password

ALTER SESSION SET NLS_DATE_FORMAT='dd/mm/yyyy hh24:mi:ss';

SET SERVEROUTPUT ON;

SET ECHO ON;

INSERT INTO STATS\$IDLE_EVENT

SELECT name FROM V\$EVENT_NAME WHERE wait_class='Idle'

MINUS

SELECT event FROM STATS\$IDLE_EVENT;

commit;

DECLARE

  v_hourly_job   NUMBER;

  v_purge_job    NUMBER;

BEGIN

 DBMS_OUTPUT.put_line('Create Statspack Job');

 DBMS_JOB.submit

  (job=> v_hourly_job,

   what=>'DECLARE snap number; BEGIN snap := STATSPACK.snap (i_snap_level=>7); END;',

   next_date=>TRUNC(SYSDATE+1/24,'HH'),

   interval=>'TRUNC(SYSDATE+1/24,''HH'')'

  );

  commit;

  

  DBMS_OUTPUT.put_line('Run Job');

  DBMS_JOB.run(v_hourly_job);

  DBMS_OUTPUT.put_line('Create purge statspack data older than 14 days');  

  DBMS_JOB.submit  

  (job=> v_purge_job,

   what=>'STATSPACK.purge(i_purge_before_date=>sysdate-14,i_extended_purge=>true);',

   next_date => TRUNC(SYSDATE+1)+3/24, 

   interval => 'TRUNC(SYSDATE+1)+3/24'

  );

  commit; 

END;

/

EOF

exit

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

Note 1 : Drop user perfstat

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

DROP USER perfstat CASCADE;

DROP public synonym STATS$SNAPSHOT_ID;

DROP public synonym STATS$DATABASE_INSTANCE;

DROP public synonym STATS$LEVEL_DESCRIPTION;

DROP public synonym STATS$X$KCBFWAIT;

DROP public synonym STATS$X$KSPPSV;

DROP public synonym STATS$X$KSPPI;

DROP public synonym STATS$X$KSXPPING;

DROP public synonym STATS$V$FILESTATXS;

DROP public synonym STATS$V$TEMPSTATXS;

DROP public synonym STATS$V$SQLXS;

DROP public synonym STATS$V$SQLSTATS_SUMMARY;

DROP public synonym STATS$SNAPSHOT_ID;

DROP public synonym STATS$DATABASE_INSTANCE;

DROP public synonym STATS$LEVEL_DESCRIPTION;

DROP public synonym STATS$SNAPSHOT;

DROP public synonym STATS$DB_CACHE_ADVICE;

DROP public synonym STATS$FILESTATXS;

DROP public synonym STATS$TEMPSTATXS;

DROP public synonym STATS$LATCH;

DROP public synonym STATS$LATCH_CHILDREN;

DROP public synonym STATS$LATCH_PARENT;

DROP public synonym STATS$LATCH_MISSES_SUMMARY;

DROP public synonym STATS$LIBRARYCACHE;

DROP public synonym STATS$BUFFER_POOL_STATISTICS;

DROP public synonym STATS$ROLLSTAT;

DROP public synonym STATS$ROWCACHE_SUMMARY;

DROP public synonym STATS$SGA;

DROP public synonym STATS$SGASTAT;

DROP public synonym STATS$SYSSTAT;

DROP public synonym STATS$SESSTAT;

DROP public synonym STATS$SYSTEM_EVENT;

DROP public synonym STATS$SESSION_EVENT;

DROP public synonym STATS$WAITSTAT;

DROP public synonym STATS$ENQUEUE_STATISTICS;

DROP public synonym STATS$SQL_SUMMARY;

DROP public synonym STATS$SQLTEXT;

DROP public synonym STATS$SQL_STATISTICS;

DROP public synonym STATS$RESOURCE_LIMIT;

DROP public synonym STATS$DLM_MISC;

DROP public synonym STATS$CR_BLOCK_SERVER;

DROP public synonym STATS$CURRENT_BLOCK_SERVER;

DROP public synonym STATS$INSTANCE_CACHE_TRANSFER;

DROP public synonym STATS$UNDOSTAT;

DROP public synonym STATS$SQL_PLAN_USAGE;

DROP public synonym STATS$SQL_PLAN;

DROP public synonym STATS$SEG_STAT;

DROP public synonym STATS$SEG_STAT_OBJ;

DROP public synonym STATS$PGASTAT;

DROP public synonym STATS$PARAMETER;

DROP public synonym STATS$INSTANCE_RECOVERY;

DROP public synonym STATS$STATSPACK_PARAMETER;

DROP public synonym STATS$SHARED_POOL_ADVICE;

DROP public synonym STATS$SQL_WORKAREA_HISTOGRAM;

DROP public synonym STATS$PGA_TARGET_ADVICE;

DROP public synonym STATS$JAVA_POOL_ADVICE;

DROP public synonym STATS$THREAD;

DROP public synonym STATS$FILE_HISTOGRAM;

DROP public synonym STATS$EVENT_HISTOGRAM;

DROP public synonym STATS$TIME_MODEL_STATNAME;

DROP public synonym STATS$SYS_TIME_MODEL;

DROP public synonym STATS$SESS_TIME_MODEL;

DROP public synonym STATS$STREAMS_CAPTURE;

DROP public synonym STATS$STREAMS_APPLY_SUM;

DROP public synonym STATS$PROPAGATION_SENDER;

DROP public synonym STATS$PROPAGATION_RECEIVER;

DROP public synonym STATS$BUFFERED_QUEUES;

DROP public synonym STATS$BUFFERED_SUBSCRIBERS;

DROP public synonym STATS$RULE_SET;

DROP public synonym STATS$OSSTATNAME;

DROP public synonym STATS$OSSTAT;

DROP public synonym STATS$PROCESS_ROLLUP;

DROP public synonym STATS$PROCESS_MEMORY_ROLLUP;

DROP public synonym STATS$SGA_TARGET_ADVICE;

DROP public synonym STATS$STREAMS_POOL_ADVICE;

DROP public synonym STATS$MUTEX_SLEEP;

DROP public synonym STATS$DYNAMIC_REMASTER_STATS;

DROP public synonym STATS$TEMP_SQLSTATS;

DROP public synonym STATS$IOSTAT_FUNCTION_NAME;

DROP public synonym STATS$IOSTAT_FUNCTION;

DROP public synonym STATS$IOSTAT_FUNCTION_DETAIL;

DROP public synonym STATS$MEMORY_TARGET_ADVICE;

DROP public synonym STATS$MEMORY_DYNAMIC_COMPS;

DROP public synonym STATS$MEMORY_RESIZE_OPS;

DROP public synonym STATS$INTERCONNECT_PINGS;

DROP public synonym STATS$IDLE_EVENT;

DROP public synonym STATSPACK;

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

Note 2 - scripts

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

All perfstat objects are created from these 3 scripts, under $ORACLE_HOME

1. Create a dedicated Tablespace

CREATE TABLESPACE statspack_data 

DATAFILE '/oracle_db/db1/db_igt/statspack_data01.dbf' 

SIZE 500M AUTOEXTEND ON MAXSIZE 6000M;

Also - take the TEMPORARYS tablespace name
SELECT tablespace_name 

FROM DBA_TABLESPACES WHERE contents = 'TEMPORARY';

2. $ORACLE_HOME/rdbms/admin/spcusr.sql

SQL*PLUS command file which creates the STATSPACK user, tables and package for the performance diagnostic tool STATSPACK

must be run from INTERNAL connection

sqlplus / as sysdba

@$ORACLE_HOME/rdbms/admin/spcusr.sql

exit;

3.  $ORACLE_HOME/rdbms/admin/spctab.sql
SQL*PLUS command file to create tables to hold  start and end "snapshot" statistical information

Must be run as STATSPACK user, PERFSTAT

sqlplus perfstat/xxxxx@igt

$ORACLE_HOME/rdbms/admin/spctab.sql

exit;

4. $ORACLE_HOME/rdbms/admin/spcpkg.sql

SQL*PLUS command file to create statistics package

Must be run as the STATSPACK owner, PERFSTAT

sqlplus perfstat/xxxxx@igt

$ORACLE_HOME/rdbms/admin/spcpkg.sql

exit;

5. To fix the error
INVALID PUBLIC SYNONYM STATSPACK;

sqlplus perfstat/xxx@igt

DROP PUBLIC SYNONYM STATSPACK;

CREATE PUBLIC SYNONYM STATSPACK  FOR STATSPACK;

NLS Settings in SQL Developer

NLS Settings in SQL Developer can be out of sync with database defaults.

By default, in SQL Developer, the NLS Length is set to byte.

This can lead to unexpected behavior.

In database NLS Length is set to CHAR:

SELECT 'NLS_DATABASE_PARAMETERS' as param_source, parameter, value FROM NLS_DATABASE_PARAMETERS 

WHERE PARAMETER='NLS_LENGTH_SEMANTICS'

UNION ALL

SELECT 'NLS_INSTANCE_PARAMETERS' as param_source, parameter, value FROM NLS_INSTANCE_PARAMETERS 

WHERE PARAMETER='NLS_LENGTH_SEMANTICS'

UNION ALL

SELECT 'NLS_SESSION_PARAMETERS' as param_source, parameter, value FROM NLS_SESSION_PARAMETERS 

WHERE PARAMETER='NLS_LENGTH_SEMANTICS';

PARAM_SOURCE                   PARAMETER                VALUE

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

NLS_DATABASE_PARAMETERS        NLS_LENGTH_SEMANTICS     CHAR

NLS_INSTANCE_PARAMETERS        NLS_LENGTH_SEMANTICS     CHAR

NLS_SESSION_PARAMETERS         NLS_LENGTH_SEMANTICS     CHAR

Consider command 

ALTER TABLE MY_TABLE MODIFY MY_COLUMN VARCHAR2(400);

In SQL DEVELOPER it will be translated to: 

ALTER TABLE MY_TABLE MODIFY MY_COLUMN VARCHAR2(400 BYTE);

SELECT char_used, char_length, data_length 

  FROM USER_TAB_COLUMNS 

 WHERE table_name = 'MY_TABLE'

   AND column_name = 'MY_COLUMN'

char_used char_length data_length 

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

B           200          200

But, when running same command in sqlplus:

ALTER TABLE MY_TABLE MODIFY MY_COLUMN VARCHAR2(200);

SELECT char_used, char_length, data_length 

  FROM USER_TAB_COLUMNS 

 WHERE table_name = 'MY_TABLE'

   AND column_name = 'MY_COLUMN';

char_used char_length data_length 

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

C           200          800

To fix this behaviour:

Option 1. Add CHAR to ALTER TABLE statements:

ALTER TABLE MY_TABLE MODIFY MY_COLUMN VARCHAR2(400 CHAR);

Option 2. Change in SQL Developer NLS Length to CHAR.

Tools -> Preferences -> Database -> NLS -> Length -> CHAR

How to test a connection to SMTP server

CREATE OR REPLACE FUNCTION TEST_SMTP_MAIL RETURN TSTRINGS PIPELINED IS

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

-- Usage: SELECT column_value as LINE from table(TEST_SMTP_MAIL);  

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

  v_server         VARCHAR2(100);

  v_port           INTEGER;

  v_smtp           UTL_SMTP.connection;

  v_reply          UTL_SMTP.reply;

  

BEGIN

  v_server := '10.20.30.40';

  v_port   := 25;

  -- attempt to connect to mail server

  pipe row( 'connecting to server '||v_server||' on port: '||v_port||'/tcp' );

  v_reply := UTL_SMTP.open_connection( host=>v_server, port=>v_port, c=>v_smtp );

  pipe row( 'Reply Code: '||v_reply.code||'. 'Reply Text: '|| reply.text );

  

  -- if a successful connect, gracefully disconnect

  if v_reply.code < 400 then

    v_reply := UTL_SMTP.quit( smtp );

    pipe row( 'Reply Code: '||v_reply.code||'. 'Reply Text: '|| reply.text );

  end if;

END TEST_SMTP_MAIL;

/

Oracle PL/SQL send mail with attached File using UTL_SMTP

CREATE OR REPLACE PROCEDURE send_mail_with_attach_file

          (p_from        IN VARCHAR2,

           p_to          IN VARCHAR2,           

           p_subject     IN VARCHAR2,

           p_text_msg    IN VARCHAR2 DEFAULT NULL,

           p_attach_name IN VARCHAR2 DEFAULT NULL,

           p_attach_mime IN VARCHAR2 DEFAULT NULL,

           p_attach_blob IN BLOB DEFAULT NULL,

           p_directory IN VARCHAR2,

           p_file_name      IN VARCHAR2           

           )

AS

  v_mail_conn         UTL_SMTP.connection;

  v_boundary          VARCHAR2(50) := '----=abc1234321cba=';

  v_step              PLS_INTEGER  := 57;

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

  --Put here real IP!

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

  v_smtp_server       CONSTANT VARCHAR2(30) := '10.20.30.40'; 

  v_smtp_server_port  CONSTANT INTEGER := 25;

  

  --File Reading  

  c_max_line_width CONSTANT PLS_INTEGER DEFAULT 54;

  v_amt            BINARY_INTEGER := 672 * 3; /* ensures proper format; 2016 */

  v_bfile          BFILE;

  v_file_length    PLS_INTEGER;

  v_buf            RAW(2100);

  v_modulo         PLS_INTEGER;

  v_pieces         PLS_INTEGER;

  v_file_pos       pls_integer := 1;

  v_to_list        VARCHAR2(1000);

  v_to             VARCHAR2(1000);

  

BEGIN

  v_mail_conn := UTL_SMTP.open_connection(v_smtp_server, v_smtp_server_port);

  UTL_SMTP.helo(v_mail_conn, v_smtp_server);

  UTL_SMTP.mail(v_mail_conn, p_from);

  

  v_to_list := p_to;

  WHILE (INSTR(v_to_list, ',') > 0) LOOP    

    --get first from list

    v_to := SUBSTR(v_to_list, 1, INSTR(v_to_list,',')-1);

    --get remaining of list

    v_to_list := SUBSTR(v_to_list, INSTR(v_to_list, ',')+1);

    --call rcpt for mail

    UTL_SMTP.rcpt(v_mail_conn, v_to);    

  END LOOP;

  --last element does not have trailing ','

  UTL_SMTP.rcpt(v_mail_conn, v_to_list);

  

  UTL_SMTP.open_data(v_mail_conn);

  UTL_SMTP.write_data(v_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);

  UTL_SMTP.write_data(v_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);

  UTL_SMTP.write_data(v_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);

  UTL_SMTP.write_data(v_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);

  UTL_SMTP.write_data(v_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);

  UTL_SMTP.write_data(v_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);

  UTL_SMTP.write_data(v_mail_conn, 'Content-Type: multipart/mixed; boundary="' || v_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

  IF p_text_msg IS NOT NULL THEN

    UTL_SMTP.write_data(v_mail_conn, '--' || v_boundary || UTL_TCP.crlf);

    UTL_SMTP.write_data(v_mail_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);

    UTL_SMTP.write_data(v_mail_conn, p_text_msg);

    UTL_SMTP.write_data(v_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);

  END IF;

  IF p_attach_name IS NOT NULL THEN

    UTL_SMTP.write_data(v_mail_conn, '--' || v_boundary || UTL_TCP.crlf);

    UTL_SMTP.write_data(v_mail_conn, 'Content-Type: ' || p_attach_mime || '; name="' || p_attach_name || '"' || UTL_TCP.crlf);

    UTL_SMTP.write_data(v_mail_conn, 'Content-Transfer-Encoding: base64' || UTL_TCP.crlf);

    UTL_SMTP.write_data(v_mail_conn, 'Content-Disposition: attachment; filename="' || p_attach_name || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

    --Read blob

    IF p_attach_blob IS NOT NULL THEN

      FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_attach_blob) - 1 )/v_step) LOOP

        UTL_SMTP.write_data(v_mail_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_attach_blob, v_step, i * v_step + 1))) || UTL_TCP.crlf);

      END LOOP;

    ELSIF p_directory IS NOT NULL AND p_file_name IS NOT NULL THEN

      --Read File  

      v_bfile := BFILENAME(p_directory, p_file_name);

      -- Get the size of the file to be attached

      v_file_length := DBMS_LOB.GETLENGTH(v_bfile);

      -- Calculate the number of pieces the file will be split up into

      v_pieces := TRUNC(v_file_length / v_amt);

      -- Calculate the remainder after dividing the file into v_amt chunks

      v_modulo := MOD(v_file_length, v_amt);

      IF (v_modulo <> 0) THEN

        v_pieces := v_pieces + 1;

      END IF;

      DBMS_LOB.FILEOPEN(v_bfile, DBMS_LOB.FILE_READONLY);

      --FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_attach_blob) - 1 )/v_step) LOOP

      FOR i IN 1 .. v_pieces LOOP

        v_buf := NULL;

        DBMS_LOB.READ(v_bfile, v_amt, v_file_pos, v_buf);

        v_file_pos := I * v_amt + 1;

        UTL_SMTP.write_data(v_mail_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(v_buf))|| UTL_TCP.crlf);

        --Exxample

        --UTL_SMTP.write_data(v_mail_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_attach_blob, v_step, i * v_step + 1))) || UTL_TCP.crlf);

      END LOOP;

      

      DBMS_LOB.FILECLOSE(v_bfile);

      

    END IF;

  

    UTL_SMTP.write_data(v_mail_conn, UTL_TCP.crlf);

  END IF;

  UTL_SMTP.write_data(v_mail_conn, '--' || v_boundary || '--' || UTL_TCP.crlf);

  UTL_SMTP.close_data(v_mail_conn);

  UTL_SMTP.quit(v_mail_conn);

END send_mail_with_attach_file;

/

SYSAUX tablespace is full with Autostats Advisor related objects

==========

Issue:
==========

SYSAUX tablespace is full with Autostats Advisor related objects

==========

Solution:

==========

Clean up old tasks

Check Current Status
col TASK_NAME format a25
col parameter_name format a35
col parameter_value format a20
set lines 120
SELECT task_name,

       parameter_name, 

       parameter_value 

 FROM DBA_ADVISOR_PARAMETERS 

WHERE task_name='AUTO_STATS_ADVISOR_TASK' 

  AND parameter_name='EXECUTION_DAYS_TO_EXPIRE';

TASK_NAME                 PARAMETER_NAME           PARAMETER_VALUE
------------------------- ------------------------ ------------------
AUTO_STATS_ADVISOR_TASK   EXECUTION_DAYS_TO_EXPIRE UNLIMITED

Set limit for old tasks
BEGIN
  DBMS_ADVISOR.SET_TASK_PARAMETER(

     task_name=> 'AUTO_STATS_ADVISOR_TASK',                                parameter=> 'EXECUTION_DAYS_TO_EXPIRE', 
     value => 30);
END;
/

Check Status again

SELECT task_name,

       parameter_name, 

       parameter_value 

 FROM DBA_ADVISOR_PARAMETERS 

WHERE task_name='AUTO_STATS_ADVISOR_TASK' 

  AND parameter_name='EXECUTION_DAYS_TO_EXPIRE';

TASK_NAME                 PARAMETER_NAME           PARAMETER_VALUE
------------------------- ------------------------ ------------------
AUTO_STATS_ADVISOR_TASK   EXECUTION_DAYS_TO_EXPIRE 30

Check oldest task date

SELECT MIN(execution_start) 

FROM DBA_ADVISOR_EXECUTIONS WHERE task_name='AUTO_STATS_ADVISOR_TASK';

MIN(EXECUTION_STAR
------------------
27-AUG-19

Purge the expired tasks. 

This step might take time

BEGIN
  PRVT_ADVISOR.delete_expired_tasks;
END;
/

Check status in DBA_ADVISOR_EXECUTIONS 

SELECT task_id, task_name, execution_name, execution_start

  FROM DBA_ADVISOR_EXECUTIONS

 WHERE task_name='AUTO_STATS_ADVISOR_TASK'

 ORDER BY execution_start;

scp backup files to backup mng server

General

On oracle server
RMAN script running at 03:00

expdp script running at 02:00

scp scripts running at 05:00 and 05:30

On backup server
Clean old backups script running at 01:00

scripts on oracle server

00 02 * * * /etc/sh/backup/export_all_instances.sh
00 03 * * * /etc/sh/backup/jobs/run_rman_backup_cron.sh igt
00 05 * * * /home/user/scripts/send_to_mng.sh
30 05 * * * /home/user/scripts/send_to_mng_exp.sh

/home/user/scripts/send_to_mng.sh

#!/bin/bash

RUN_DATE=`date +"%Y%m%d"`

DIR_PREFIX=$RUN_DATE

REMOTE_USER=user

REMOTE_SERVER=mng_server-01

REMOTE_PATH=/backup/ipn_backup/ora_online

LOCAL_DIR=/backup/ora_online/for_backup

KEEP_DAYS=10

is_active_server=`ps -ef | grep ora_pmon_igt | grep -v grep | wc -l`

if [[ $is_active_server -eq 0 ]]; then

 echo "Oracle is not running on this server" 

 exit 0

fi

cd ${LOCAL_DIR}

last_backup_dir=`ls -ltr | grep ${DIR_PREFIX} | tail -1 | awk '{print $9}'`

if [[ -d $last_backup_dir ]]; then

 echo "tar -cvf ${last_backup_dir}.tar ${last_backup_dir}"

 tar -cvf ${last_backup_dir}.tar ${last_backup_dir}

 echo "scp -p  ${LOCAL_DIR}/${last_backup_dir}.tar ${REMOTE_USER}@${REMOTE_SERVER}:${REMOTE_PATH}/${last_backup_dir}.tar"

 scp -p  ${LOCAL_DIR}/${last_backup_dir}.tar ${REMOTE_USER}@${REMOTE_SERVER}:${REMOTE_PATH}/${last_backup_dir}.tar

 rm -f ${LOCAL_DIR}/${last_backup_dir}.tar

else

 echo "No Archive Directory to Send to mng"

fi

/home/user/scripts/send_to_mng_exp.sh

#!/bin/bash

RUN_DATE=`date +"%Y%m%d"`

FILE_PREFIX=export_igt

FILE_SUFFIX=dmp

FILE_SUFFIX_LOG=log

REMOTE_USER=user

REMOTE_SERVER=mng_server-01

REMOTE_PATH=/backup/ipn_backup/exp_backup

LOCAL_DIR=/backup/ora_exp/for_backup

LOCAL_LOG_DIR=/backup/ora_exp/for_backup/old_log

cd ${LOCAL_DIR}

last_backup_file=`ls -ltr | grep ${FILE_PREFIX} | grep ${FILE_SUFFIX} | tail -1 | awk '{print $9}'`

if [[ -f $last_backup_file ]]; then 

 #echo "scp ${REMOTE_USER}@${REMOTE_SERVER}:${REMOTE_PATH}/${last_backup_file} ${LOCAL_DIR}/${last_backup_file}"

 scp ${LOCAL_DIR}/${last_backup_file} ${REMOTE_USER}@${REMOTE_SERVER}:${REMOTE_PATH}/${last_backup_file} 

else

 echo "No export dmp file to send to mng"

fi

log_files=`ls -1 ${LOCAL_DIR}/${FILE_PREFIX}*${FILE_SUFFIX_LOG} 2>/dev/null | wc -l`

if [[ $log_files -gt 0 ]]; then

 mv -f ${LOCAL_DIR}/${FILE_PREFIX}*${FILE_SUFFIX_LOG} ${LOCAL_LOG_DIR}

fi

KEEP_BACKUPS=1

cd ${LOCAL_DIR}

#echo "expdp dmp files: ls -1 ${FILE_PREFIX}*${FILE_SUFFIX} | wc -l"

backups_number=`ls -1 ${FILE_PREFIX}*${FILE_SUFFIX} | wc -l`

#echo "backups_number = $backups_number"

while [[ $backups_number -gt $KEEP_BACKUPS ]]; do

 backup_name=`ls -ltr ${FILE_PREFIX}*${FILE_SUFFIX} | head -1 | awk '{print $9}'`

 #echo "rm -f $backup_name"

 rm -f $backup_name

 backups_number=`ls -1 ${FILE_PREFIX}*${FILE_SUFFIX} | wc -l`

done

scripts on mng server

00 01 * * * /home/user/scripts/del_old_backups.sh

/home/user/scripts/del_old_backups.sh

#!/bin/bash

LOCAL_DIR=/backup/ipn_backup/ora_online

RUN_DATE=`date +"%Y%m%d"`

BACKUP_SUFFIX=tar

KEEP_BACKUPS=4

cd ${LOCAL_DIR}

backups_number=`ls -1 *${BACKUP_SUFFIX} | wc -l`

while [[ $backups_number -gt $KEEP_BACKUPS ]]; do

 backup_name=`ls -ltr | grep ${BACKUP_SUFFIX} | head -1 | awk '{print $9}'`

 rm -f $backup_name

 backups_number=`ls -1 *${BACKUP_SUFFIX} | wc -l`

done

LOCAL_DIR=/backup/ipn_backup/exp_backup

FILE_PREFIX=export_igt

BACKUP_SUFFIX=dmp

KEEP_BACKUPS=2

cd ${LOCAL_DIR}

backups_number=`ls -1 ${FILE_PREFIX}*${BACKUP_SUFFIX} | wc -l`

while [[ $backups_number -gt $KEEP_BACKUPS ]]; do

 backup_name=`ls -ltr | grep ${FILE_PREFIX} | grep ${BACKUP_SUFFIX} | head -1 | awk '{print $9}'`

 rm -f $backup_name

 backups_number=`ls -1 ${FILE_PREFIX}*${BACKUP_SUFFIX} | wc -l`

done

ALTER INDEX REBUILD ONLINE in Partitioned Index

ORA-14086: a partitioned index may not be rebuilt as a whole

SET LINESIZE 140

COL sql_cmd FOR A120

SET PAGESIZE 0

SET HEADING OFF

SET FEEDBACK OFF

SET VERIFY OFF

spool rebuild_ix.sql

SELECT 'ALTER INDEX '||index_owner||'.'||index_name||' REBUILD PARTITION '||PARTITION_NAME||' ONLINE;' AS sql_cmd

  FROM DBA_IND_PARTITIONS

 WHERE index_name = 'MY_INDEX';

spool off

--Same for a user

SELECT 'ALTER INDEX '||index_name||' REBUILD PARTITION '||PARTITION_NAME||' ONLINE;' AS sql_cmd
  FROM USER_IND_PARTITIONS
 WHERE index_name = 'MY_INDEX';

SQL_CMD
------------------------------------------------------------------
ALTER INDEX MY_INDEX REBUILD PARTITION SYS_P392 ONLINE;
ALTER INDEX MY_INDEX REBUILD PARTITION SYS_P393 ONLINE;
ALTER INDEX MY_INDEX REBUILD PARTITION SYS_P394 ONLINE;
ALTER INDEX MY_INDEX REBUILD PARTITION SYS_P395 ONLINE;
ALTER INDEX MY_INDEX REBUILD PARTITION SYS_P396 ONLINE;
ALTER INDEX MY_INDEX REBUILD PARTITION SYS_P397 ONLINE;
ALTER INDEX MY_INDEX REBUILD PARTITION SYS_P398 ONLINE;
ALTER INDEX MY_INDEX REBUILD PARTITION SYS_P399 ONLINE;

Before rebuild:

SEGMENT_NAME                   USED_MB

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

MY_INDEX                                 3567

After rebuild

SEGMENT_NAME                   USED_MB

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

MY_INDEX                                  2301