Datewise File find Scripts

--------------------
Scripts Crontab
--------------------
00 04 4 * * /etc/monthly.sh
00 04 * * 2-6 /etc/scanlog.sh
00 05 * * 1 /etc/monday.sh

--------------------------------------------------------------------------------------------------
 Total Count Files on Monthly Basis with string Scripts with rename datewise
--------------------------------------------------------------------------------------------------

#!/bin/bash
rm -rf /home/Monthly.log
touch /home/Monthly.log
echo -------------- AB ------------ >> /home/Monthly.log
cd /home/ab/
echo "MONTHLY PDF: " $(find /home/ab/ -type f -name "*201511*.pdf" |wc -l) >> /home/Monthly.log
echo ------------------------------ >> /home/Monthly.log
printf "\n \n" >> /home/Monthly.log
sleep 10
echo ------------- CD ------------- >> /home/Monthly.log
echo "MONTHLY PDF: " $(find /home/cd/ -type f -name "*201511*.pdf" |wc -l) >> /home/Monthly.log
echo ------------------------------ >> /home/Monthly.log
printf "\n \n" >> /home/Monthly.log
sleep 10
cd /home/
DATE=$(date +"%Y%m%d%H%M")
mv Monthly.log Monthly$DATE.log
exit


------------------------------------------------------------------------------
 Daily basis DateWise  Total Dir/FIles/DateWise PDF Generate
------------------------------------------------------------------------------

#!/bin/bash
rm -rf /home/scanlog.log
touch /home/scanlog.log

echo -------------- AB------------ >> /home/scanlog.log
cd /home/ab/
echo "TOTAL DIR: " $(ls -l | grep ^d | wc -l) >> /home/scanlog.log
echo "TOTAL PDF: " $(find . -name "*.pdf" | wc -l) >> /home/scanlog.log
echo "TOTAL DATEWISE PDF: " $(ls -ltR  |grep  $(date --date="-1 days" +%Y%m%d.pdf) | wc -l) >> /home/scanlog.log
echo ------------------------------ >> /home/scanlog.log
printf "\n \n" >> /home/scanlog.log
echo ------------- CD ------------- >> /home/scanlog.log
cd ../cd/
echo "TOTAL DIR: " $(ls -l | grep ^d | wc -l) >> /home/scanlog.log
echo "TOTAL PDF: " $(find . -name "*.pdf" | wc -l) >> /home/scanlog.log
echo "TOTAL DATEWISE PDF: " $(ls -ltR  |grep  $(date --date="-1 days" +%Y%m%d.pdf) | wc -l) >> /home/scanlog.log
echo ------------------------------ >> /home/scanlog.log
printf "\n \n" >> /home/scanlog.log
cd /home/
DATE=$(date +"%Y%m%d%H%M")
mv scanlog.log scanlog$DATE.log
exit


-----------------------------------------
 This Script execute only Monday
-----------------------------------------

#!/bin/bash
rm -rf /home/Monday.log
touch /home/Monday.log
echo --------------AB ------------ >> /home/Monday.log
cd /home/ab/
echo "TOTAL DIR: " $(ls -l | grep ^d | wc -l) >> /home/Monday.log
echo "TOTAL PDF: " $(find . -name "*.pdf" | wc -l) >> /home/Monday.log
echo "TOTAL DATEWISE PDF: " $(ls -ltR  |grep  $(date --date="-2 days" +%Y%%m%d.pdf) | wc -l) >> /home/Monday.log
echo ------------------------------ >> /home/Monday.log
printf "\n \n" >> /home/Monday.log
echo ------------- CD ------------- >> /home/Monday.log
cd ../cd/
echo "TOTAL DIR: " $(ls -l | grep ^d | wc -l) >> /home/Monday.log
echo "TOTAL PDF: " $(find . -name "*.pdf" | wc -l) >> /home/Monday.log
echo "TOTAL DATEWISE PDF: " $(ls -ltR  |grep  $(date --date="-2 days" +%Y%m%d.pdf) | wc -l) >> /home/Monday.log
echo ------------------------------ >> /home/Monday.log
printf "\n \n" >> /home/Monday.log
cd /home/
DATE=$(date +"%Y%m%d%H%M")
mv Monday.log Monday$DATE.log
exit

Detect & Delete Empty Dir. / Files

---------------------------------------------------
 Lower case file name find & count in CS
---------------------------------------------------

ls | egrep [a-z] |wc -l
ls | egrep [a-z] >> /home/aru.out


------------------------------------------------------
 Find Empty / corrupted file and directories
------------------------------------------------------

cd /home/mr

find . -empty -type d >> /home/D_aru_empty.out
find . -empty -type d | wc -l

find . -empty -type f
find . -empty -type f | wc -l

----------------------------------------------
 Delete Empty File and directories
----------------------------------------------

cd /home/mr

find . -empty -type d -delete
find . -empty -type f -delete

find . -empty -type -d -delete
find . -empty -type -f -delete


------------------------------------------------------------
 find and delete all empty directories using xargs
------------------------------------------------------------

## secure and fast version ###
find /path/to/ -type d -empty -print0 | xargs -0 -I {} /bin/rmdir "{}"


## secure and fast version ###
find /path/to/ -type f -empty -print0 | xargs -0 -I {} /bin/rm "{}"

ORA-00204 ORA-00202 ORA-27091 ORA-27072

# Control file header/ block corrupted 

ORA-00204: error in reading (block 3, # blocks 8) of control file
ORA-00202: control file: '/u01/app/oracle/xxxx/xxxx/control02.ctl'
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Linux Error: 92: Protocol not available
Additional information: 4
Additional information: 3
Additional information: 4096

- Solution 

1. Delete -  /u01/app/oracle/xxxx/xxxx/control02.ctl

2. Copy & paste  control01.ctl to control02.ctl same location then try to startup mount db.

Resolved the issue Happy Keep enjoy..!!!!!!

Exact SCN Find

How to find exact SCN Number for Oracle Restore

The following was written by myself and published for the 2011 Oracle Open World Tips and Tricks guide.

TIP: How to find the SCN POINT to safely restore a database for a point-in-time recovery

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

I often get called in to assist in point-in-time recoveries where the dba keeps getting

errors on "alter database open resetlogs;".

This error condition can be hard to fix without another full restore, if we already rolled forward too

much, beyond where we intended and easy to NEVER EVER hit those errors again.

A successful recovery must begin with just a little bit of preperation to know your SCN number

that you would like to use to roll the database forward to the point in time needed.

Whether I am just doing disaster recovery testing, cloning, creation of a standby or in an actual

disaster, I use the following TIPS and recipe below.

1) Prepare and startup mount your instance.

   * Prepare your restore db by setting up your pfile, data directories, flash area,

   and physical nature of your database.

   * Prepare your pfile and make sure your db_name and db_unique_name are set appropriate.

            db_name=yourdbname

            db_unique_name=instance

           

   * startup nomount the database;

  

   * restore the controlfile

            If your current production is available, just create a new controlfile using alter database,

            or use rman with an autobackup controlfile, or catalog.

           

2) Now that you have a controlfile mounted, you can query the latest backup details, archivelog details and

find the exact SCN to use in your recovery script.

These preperation steps will allow you to ALWAYS open a db resetlogs WITHOUT Error.

TIP:

            1) Never use: "set until time" for your recovery needs.

               Always use: "set until scn" for your recovery needs.

              

               The "time" portion is a great feature, but problematic and last I

               read, oracle only guarantees a 5 minute window closest to your time.

              

               The SCN is exact and works if you do the preperation steps below.

              

            2) Get the greatest of either absolute_fuzzy_change# or checkpoint_change# for your datafile backups.

           

                        The quickest way to roll forward just beyond the backup to open resetlogs is

                        to query v$backup_datafile.

                        You then just need to choose the greatest of either the absolute_fuzzy_change# or checkpoint_change#

                        for your backupset.

                       

                        You can modify the query to your liking, and even add in many details from stamp and recid, but below

                        gets the job done quickly.  Just choose the day in which your level0 or level1 completed and modify appropriately.

           

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

            `                       col fuzz# format 99999999999999999999999999                

            `                       col chkpnt# format 99999999999999999999999999            

                                select max(absolute_fuzzy_change#) fuzz#, max(checkpoint_change#) chkpnt# from

                                    (select file#, completion_time, checkpoint_change#, absolute_fuzzy_change# from v$backup_datafile

                                    where incremental_level = 0

                                    and trunc(completion_time) = to_date('JUN-20-2010','MON-DD-YYYY')

                                    and file# <> 0

                                    order by completion_time desc

                                );

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

                        This will return 2 SCN numbers. Pick the greatest of the 2.

                       

                                                      FUZZ#                        CHKPNT#

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

                                                23138984359              23138974759

                       

                        2a) Feel free to correlate this with information directly from an rman "list backup".

                                    rman target /

                                                spool log to list_backup.log;

                                                list backup;

                                                exit;

                                               

                                    Edit list_backup.log and search for checkpoint change# (chkpnt#) you need. 

                                    You should see your SCN provided.

                                   

                                    This should be the last checkpoint_change# for your backupset that your

                                    interested in.

                       

               

            3) Or, since checkpoints occur at every log switch, you can then choose an SCN from the exact

                        log switch closest to your recovery needs.

                       

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

                                     col next_change# format 999999999999999;

                                     col first_change# format 999999999999999;

                                     select sequence#, to_date(first_time,'DD-MON-YYYY HH24:MI:SS') first_time,

                                                                          first_change#,

                                                                          to_date(next_time,'DD-MON-YYYY HH24:MI:SS') next_time,

                                                                          next_change# from v$archived_log

                                     where completion_time between to_date('JUN-22-2010','MON-DD-YYYY') and Sysdate

                                     ;

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

 

                       

            4) Or, if the EXACT SCN prior to crash, or logical errors is needed, use LogMiner.

           

                        To get closest, or exactly to your recovery needs, get the scn's from the archived logs

                        between the point in time that you need to restore to and mine the log itself (from step above).

                       

                        I suggest copying the archived log in question to /tmp or somewhere you can keep it.

                        Setup logminer and then query it using the following, replacing the scn's from your query above.

                       

                        Pick times that are as close as you can get to the errors that you need to avoid.

                       

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

                                    BEGIN

                                        DBMS_LOGMNR.add_logfile (

                                        options     => DBMS_LOGMNR.new,

                                        logfilename => '/tmp/a55050.arc');

                                    END;

                                    /

                                    BEGIN

                                      DBMS_LOGMNR.start_logmnr (

                                        starttime => to_date('10-MAR-2010 18:50:00','DD-MON-YYYY HH24:MI:SS'),

                                        endtime => to_date('10-MAR-2010 19:05:00','DD-MON-YYYY HH24:MI:SS'),

                                        options => Dbms_Logmnr.DDL_Dict_Tracking);

                                    END;

                                    /

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

                                   

                        Now Query the log, looking for your errors.

                        You can make multiple runs of this, narrowing or expanding your search as needed.

                       

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

                                    SELECT scn, to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') timest, operation, sql_redo FROM   v$logmnr_contents

                                    where scn between  21822207692 and 21822211410

                                    order by scn;

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

                                   

                                    You can look for, grep, search, find the operation in question.

                                    Then choose that EXACT SCN to roll forward to.

                                    You will want to subtract 1 from the result.

Finally:

5) Create your restore script using the found SCN#.

            Once you have chosen the SCN number, the rest is academic:

                        rman->

                                    run {

                                         set until scn=23138974759;

                                         restore database;

                                         recover database;

                                    }

                       

            Don't forget to add "set newnames" or channel allocations if needed.

           

And thats it.

If you follow these simple preperation steps, you will ALWAYS be able to "alter database open resetlogs"

without error.

TNS-12516 TNS-12519

TNS-12516 TNS:listener could not find instance with matching protocol stack
TNS-12519 TNS:no appropriate service handler found

Fix
~~~~
Increase the value for PROCESSES.
alter system set PROCESSES=200 scope=spfile ;
startup force;

RAC RUNTIME FAILOVER

RAC 11g/12c

One Of my Friend asked this question in oracle group,

Group Question:-

“I’m checking DML operation in RAC and found that transaction got rollback, if one of instance is crashed. User gets connected to other active instance.

Is it ok ?

or transaction should restart automatically or need to start manually,

But will it resume again.?”

G Discussion:-

In 11g After failover your entire transaction has failed, and will be rolled back using the same mechanism as for any aborted session, regardless of whether you reconnect using TAF or not.

Uncommited DML rollback and resubmitted after reconnect in 11g.

In 12c not rollback, but uncommitted transaction will be committed in case of any failure, Using the TAF parameter FAILOVER_TYPE TRANSATION, COMMIT_FAILOVER TRUE that’s new feature in 12c.

This feature not support in 11g.Failover auto resume work only SELECT statement on both version (11g,12c).  

NOTE:- In detail Follow 12c TAF parameter and syntax

Configure Services for Application Continuity (TAF NEW Feature in 12C)

» Set the service attributes using SRVCTL /GDSCTL to use Application Continuity,

» Set FAILOVER_TYPE to TRANSACTION to enable Application Continuity

» Set COMMIT_OUTCOMEto TRUE to enable Transaction Guard (mandatory)

» Also review the following service attributes:

» REPLAY_INITIATION_TIMEOUT : Set this to the duration in seconds after which replay is not  

    started (e.g. 180, 300, 1800 seconds –the override to cancel replay). This timer starts at

    beginRequest. (default 300 seconds)

» FAILOVER_RETRIES : Set this to specify the number of connection retries for each replay

   attempt. (default 30 retries, applied at replay driver)

» FAILOVER_DELAY : Set this to specify the delay in seconds between connection retries (default

   10 seconds, applied at replay driver)

» AQ_HA_NOTIFICATIONS: Set this to TRUE to enable FAN (default TRUE)

» Example

srvctl add service -db mts -service oltpworkload -role PRIMARY - notification TRUE -session_state dynamic -failovertype transaction –failovermethod basic -commit_outcome TRUE -failoverretry 30 –failoverdelay 10 -replay_init_time 900 -clbgoal SHORT -rlbgoal  

SERVICE_TIME -preferred mts1,mts2 -retention 3600 –verbose