script to find stale objects and run the stats

You can use below script to automate running stats when stale objects are found in the database...
here we are ignoring all the objects were their stats are locked.

In order to get updated  stale objects... it is better you flush the monitoring info... as this updates the changes occurred recently....and tells whether any objects got staled or not. we are doing this manually because oracle has it own way/time of updating... if you have hard hitting tables then they might be stale but the monitoring info will not capturing it...  by doing so we can have better stats for  the tables needed.

you can easily automate the below code with you environment set.

exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO;

Script:-

CHANNELS=`cat /proc/cpuinfo | grep processor | wc -l`


===================
sqlplus  -S sys /change@ABCD_SID as sysdba <<EOF >> $LOG
set linesize 200
set serveroutput on size 1000000
set feedback on echo off verify off

Prompt Running stats for Stale Table objects

declare
sql_stmt varchar2(256);
va_tab dba_tab_statistics.table_name%type;

cursor c_f is (select owner,table_name,global_stats,stale_stats from dba_tab_statistics
where stale_stats = 'YES' and table_name not like 'BIN$%' and STATTYPE_LOCKED is null);

begin
    for i in c_f
    loop
        begin
           dbms_stats.gather_table_stats(''||i.owner||'',''||i.table_name||'',
                estimate_percent => 100,
                degree => $CHANNELS,
                method_opt => 'for all columns size auto', cascade => true);
                dbms_output.put_line(sysdate || ': stats done for ' || ''||i.owner||'' ||'.' || ''||i.table_name||'');
            exception
            when others then
            continue;
            dbms_output.put_line(sysdate || ': failed to gather stats for ' || ''||i.owner||'' ||'.' || ''||i.table_name||'' || chr(10) || sqlerrm);
         end;
    end loop;
           dbms_output.put_line(sysdate || ': #######################' || ': Stats done for Above Found Stale objects ' || ': #######################');
END;
/

Prompt Running stats for Stale index objects

declare
sql_stmt varchar2(256);
va_tab dba_ind_statistics.table_name%type;

cursor c_f is (select owner,index_name,global_stats,stale_stats from dba_ind_statistics
where stale_stats = 'YES' and table_name not like 'BIN$%' and STATTYPE_LOCKED is null);

begin
    for i in c_f
    loop
        begin
           dbms_stats.gather_index_stats(''||i.owner||'',''||i.index_name||'',
                estimate_percent => 100,
                degree => $CHANNELS,
                no_invalidate => false);
                dbms_output.put_line(sysdate || ': stats done for ' || ''||i.owner||'' ||'.' || ''||i.index_name||'');
            exception
            when others then
            continue;
            dbms_output.put_line(sysdate || ': failed to gather stats for ' || ''||i.owner||'' ||'.' || ''||i.index_name||'' || chr(10) || sqlerrm);
         end;
    end loop;
           dbms_output.put_line(sysdate || ': #######################' || ': Stats done for Above Found Stale objects ' || ': #######################');
END;
/


EOF
echo "Done" >> $LOG

Hope this Helps......

how to mount the raw disks or nfs mounts that are listed in fstab

we can use this simple method to find what disks or mounts are missing before we bring the database up....

To understand this better.... initially the admins will put an entry in fstab for the disks and mounts that need to be mounted... but sometimes we dont see all disks come up.... it figure it out and make them available you can use this small script....

list=`awk '{print $2}' /etc/fstab |grep -i "/" | grep -v '^/etc/fstab' | grep -v '^/director' | grep -v '/abcdef_data' | egrep '/.'`         ----(you can exclude some more using grep -V)
for i in $list
do
grep -iw $i /proc/mounts > /dev/null
status=$?
if [ $status -eq 0 ]; then
echo " $i is mounted"
else
echo "$i is not mounted"                      ---------you can mount the missing one by adding (mount $i)                                                                      ... if you want it to mount directly)
fi
done

how to attach files to email in UNIX in shell scripts or command line

Use below command to send the files as attachment in Email. doing this also helps you to zip the files and email. also this reduces the email size too.
as we all know we send logfiles

Linux:-

echo body_text_you_want | mailx -a file_name.zip -s "Subject_line_you  want to specify" abcef@domain.com

for AIX:-

to attach a file

(echo "Subject:Test email\nTo:adbcef@domain.com\nFrom: adbcef@domain.com\n\nAttached log file";uuencode file_name file_name) |  /usr/sbin/sendmail abced@domain.com

to attach multiple files....

(echo "Subject:Test email\nTo:adbcef@domain.com\nFrom: adbcef@domain.com\n\nAttached log file";(uuencode file_name file_name; uuencode file_name_2 file_name_2)) |  /usr/sbin/sendmail abced@domain.com

Hope this helps.

create user without C## in the 12c container database

In oracle to create a normal user/profiles in oracle 12c container database. we need to set the session to below so that we can create a normal user...
alter session set "_ORACLE_SCRIPT"=true; 

then try to create a user. this time it will work...


Hope this helps....

generating large number of *.aud file under Grid_Home().. (12c and 11g)

Recently, what we observed was.. the audit files under grid_home were generating too many.

GRID_HOME/rdbms/audit....

By default in 11g the audit_sys_operations is "FALSE" but as you upgrade/go to 12C then this is "TRUE"

Having said that, this will be logging all the audit activity when ever something log's in... so we need to set this to "FALSE"

>   alter system set audit_sys_operations=FALSE" scope=spfile.  --- this need a bounce..

once this is done.... this will slow down the audit info writing to the files.... but it will still generate the files... 

 as per oracle..this is a BUG....

Large Number of Audit Files are getting generated in 12c Oracle Restart (Doc ID 2049448.1)

so we need to apply... this doesn't require any bounce of crs....

1     crsctl modify resource ora.asm -attr  START_DEPENDENCIES="hard(ora.cssd)" –unsupported    (for 12c)

1      crsctl modify resource ora.asm -attr  START_DEPENDENCIES="hard(ora.cssd)"  (for 11g)...

       monitor the directory for the change....

      

       Hope this Helps.....

   

   

    

Renaming or moving the controlfiles in oracle

1) sqlplus in to the database and get the location of the current control files...
     SQL> show parameter control_files;
2) make the desired directory structure where you want to move the controlfiles.
3) then in sqlplus, set the new location for the control files. AND Create a Pfile from existing SPFILE before making any CHANGES.
   SQL> ALTER SYSTEM SET control_files='+Apt/CONTROLFILE/control01.ctl','+TEMP/CONTROLFILE/control02.ctl' SCOPE=SPFILE;

4) shutdown the database;
5) start the database in nomount;
6) In another window, start rman ... rman target /
7) restore the control files to the above mentioned controlfiles from step 3 with a  current control file from old location from step 1.
  rman target /
restore controlfile to '+Apt/CONTROLFILE/control01.ctl'' from 'Apt/TESTDB/controlabc123';
restore controlfile to '+TEMP/CONTROLFILE/control02.ctl' fromn 'TEMP/TESTDB/controlabc123';

8) open the database in Mount.
9) open database and validate the changes.



Hope this Helps .....

Renaming or moving tempfile in oracle(ASM/Non-ASM)

1) sqplus to the database and make sure the Tempfiles that you want to rename or change the location.
   SQL> select name from v$tempfile;
2) create a pfile before making the changes.
3) shutdown the database.
4) make the directory structure where you want to move the temp files..
5) start the database in Mount.
    SQL> startup mount
6) alter database rename file '+APT/TESTDB/TEMPFILE/tempabc' to '+TEMP/TESTDB/TEMPFILE/temp01.dbf'

for filesysystem also you can follow the same procedure. just give the location that you want to.

7) open the database and make sure the temp file is created at the desired location.



In 12C:-

Below are the steps to move the Temp files… for container / PDB.

set linesize 200

col file_name for a60

SELECT v.file#, t.file_name, v.status FROM dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#;

1)

Create a tempfile of a decent size.

alter tablespace TEMP add tempfile '+TEMP/TESTDBC/TEMPFILE/temptest.dbf' size 2G;

2)

take the tempfile offline which you want to move.

alter database tempfile '+DATIND/TESTDBC/TEMPFILE/temp.441.943798245' offline;

SELECT v.file#, t.file_name, v.status FROM dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#;

3)

Copy the tempfile in the ‘+ASM’ to the desired location. Note create the folders in TEMP directory.

cp '+DATIND/TESTDBC/TEMPFILE/TEMP.441.943798245' '+TEMP/TESTDBC/TEMPFILE/temp01.dbf'

4)

Rename the tempfile you already moved.

alter database rename file '+DATIND/TESTDBC/TEMPFILE/temp.441.943798245' to '+TEMP/TESTDBC/TEMPFILE/temp01.dbf';

SELECT v.file#, t.file_name, v.status FROM dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#;

5)

Make the tempfile online again.

alter database tempfile '+TEMP/TESTDBC/TEMPFILE/temp01.dbf' online;

SELECT v.file#, t.file_name, v.status FROM dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#;

Note:- do these steps for other tempfiles in the temp(do 2/3/4/5 steps).

Once done drop the created tempfile in step 1.

alter database tempfile '+TEMP/TESTDBC/TEMPFILE/temptest.dbf' offline;

SELECT v.file#, t.file_name, v.status FROM dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#;

alter tablespace TEMP drop tempfile '+TEMP/TESTDBC/TEMPFILE/temptest.dbf';

SELECT v.file#, t.file_name, v.status FROM dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#;

Hope this helps......