Script to recursively delete Oracle Log Files under Windows

Script to recursively delete Oracle Log Files under Windows

Set the number of days you want to keep data for with the following variable

dtmDate = Date - 20

Specify top level dir for your bdump , adump , cdump etc

Set Thefolder = objFSO.GetFolder("S:\ADMIN\DB_SID")

It will do a recursive search of all folders e.g. bdump , adump , cdump , udump
under the top level folder

Specify file types you wish to delete here

If UCase(objFSO.GetExtensionName (AFile)) = "TRC" Then   

            Killfile (Afile)
        ElseIf UCase(objFSO.GetExtensionName (AFile)) = "TMP" Then
            Killfile (Afile)
        ElseIf UCase(objFSO.GetExtensionName (AFile)) = "ERR" Then

            Killfile (Afile)
        ElseIf Ucase(objFSO.GetExtensionName (AFile)) = "DMP" Then
            Killfile (Afile)
        ElseIf Ucase(objFSO.GetExtensionName (AFile)) = "TRW" Then

            Killfile (Afile)
        End If

Here is the actual script , cut and paste the script with the relevant change into a text file
and rename it to a file name with a .vbs extension e.g. delete_oracle_log.vbs
You can schedule the script to run periodically using windows scheduler

'==========================================================================

'==========================================================================

'Current Date minus 20 days
Dim sMsg
dtmDate = Date - 20

strTargetDate = ConvDate(dtmDate)

FolderCount = 0
DeletedCount = 0

'strComputer = "."
'Set objWMIService = GetObject("winmgmts:\\"& strComputer & "\root\cimv2")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objWSH = CreateObject("WScript.Shell")

'folder dialog Box
Const WINDOW_HANDLE = 0
Const OPTIONS = 0

Set Thefolder = objFSO.GetFolder("J:\ADMIN\DB_SID")
StartTime = Now
WorkWithSubFolders Thefolder
EndTime = Now
sMsg = "Deleted: " & DeletedCount & " File(s) from " & FolderCount & " Directory(s)!!" & _
VbCrLf&VbCrLf & "Script Started: " & vbTab&StartTime & _
VbCrLf & "Script Ended: " & vbTab&EndTime
objWSH.LogEvent 0, sMsg

'Credit for this sub goes to S. Hussain Akbar
Sub WorkWithSubFolders (AFolder)
Dim MoreFolders, TempFolder
FolderCount = FolderCount + 1

CheckExt AFolder
Set Morefolders = AFolder.Subfolders
For Each TempFolder In MoreFolders
WorkWithSubFolders (TempFolder)
Next

End Sub

Sub CheckExt (AFolder)
Dim Afile, TheFiles

On Error Resume Next
Set TheFiles = AFolder.Files
For Each Afile In TheFiles
If UCase(objFSO.GetExtensionName (AFile)) = "TRC" Then
Killfile (Afile)
ElseIf UCase(objFSO.GetExtensionName (AFile)) = "TMP" Then
Killfile (Afile)
ElseIf UCase(objFSO.GetExtensionName (AFile)) = "ERR" Then
Killfile (Afile)
ElseIf Ucase(objFSO.GetExtensionName (AFile)) = "DMP" Then
Killfile (Afile)
ElseIf Ucase(objFSO.GetExtensionName (AFile)) = "TRW" Then
Killfile (Afile)
End If
Next
If (DeletedCount > 0) And (DeletedCount Mod 10 = 0) Then
sMsg = "Have worked so far with " & FolderCount & " folders." & VbCrLf&VbCrLf
sMsg = sMsg & "And have deleted " & DeletedCount & " files."
objWSH.LogEvent 1, sMsg
End If
End Sub

Sub Killfile (AFile)
On Error Resume Next
strDate = ConvDate(Afile.DatelastModified)
If strDate < strTargetDate Then
objFSO.DeleteFile (AFile)
DeletedCount = DeletedCount + 1
End If
End Sub

Function ConvDate (sDate) 'Converts MM/DD/YYYY HH:MM:SS to string YYYYMMDD
strModifyDay = day(sDate)
If len(strModifyDay) < 2 Then
strModifyDay = "0" & strModifyDay
End If
strModifyMonth = Month(sDate)
If len(strModifyMonth) < 2 Then
strModifyMonth = "0" & strModifyMonth
End If
strModifyYear = Year(sDate)
ConvDate = strModifyYear & strModifyMonth & strModifyDay

End Function

ORA-31633: unable to create master table ( expdp )

ORA-31633: unable to create master table ( expdp )

Example

K:\Partion\expdmp>expdp USER_PROD/USER_PROD@PROD_DB parfile=ExportParam.txt
 
Export: Release 10.2.0.3.0 - 64bit Production on Wednesday, 12 August, 2009 14:09:57
 
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SCHEMA_PROD.TABLE1"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-00955: name is already used by an existing object

Details

When performing and export using data pump you receive the error as above due to the
fact that the exp was previously executed and not completed due to the fact the it was
canceled or stopped for some reason. As a result the new expdp job has the same name
as the old expdp job.

Solution

Verify that the table associated with the expdp job exists:

SQL> select table_name from dba_tables where table_name like '%TABLE1%';

 

TABLE_NAME                                                                     

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

TABLE1 

Identified that data pump job exists and the status of the job is NOT RUNNING

SQL>SELECT owner_name, job_name, operation, job_mode, state, attached_sessions

FROM dba_datapump_jobs WHERE job_name NOT LIKE 'BIN$%' ORDER BY 1,2;

Drop the table                                                          

SQL> drop table SCHEMA_PROD.TABLE1;

 
Table dropped.
 

Re Run the export

Error 12514

Error 12514 attaching to destination LOG_ARCHIVE_DEST_X standby host

Example


Alert log shows the following when trying ship archive logs to Physical Standby Site:

    Error 12514 received logging on to the standby
    Fri Jul 31 09:33:44 2009
    Errors in file i:\admin\semdb\bdump\databasedb2_lns3_154572.trc:
    ORA-12514: Message 12514 not found; No message file for product=RDBMS, facility=
    LGWR: Error 12514 creating archivelog file 'databasedr'
    LNS: Failed to archive log 22 thread 2 sequence 47481 (12514)
    Fri Jul 31 09:33:46 2009
    Error 12514 received logging on to the standby

Details

Trace file from primary database server is unable to connect to physical standby database:

FAL[server]: Selected inactive destination; resetting temporarily
Redo shipping client performing standby login
OCIServerAttach failed -1
.. Detailed OCI error val is 12514 and errmsg is 'ORA-12514: Message 12514 not found; No message file for product=RDBMS, facility=ORA
*** 2009-07-31 09:33:49.490 61080 kcrr.c
Error 12514 received logging on to the standby
Error 12514 connecting to destination LOG_ARCHIVE_DEST_4 standby host 'databasedr'
Error 12514 attaching to destination LOG_ARCHIVE_DEST_4 standby host 'databasedr'
ORA-12514: Message 12514 not found; No message file for product=RDBMS, facility=ORA
*** 2009-07-31 09:33:49.505 59323 kcrr.c
kcrrfail: dest:4 err:12514 force:0 blast:1
kcrrwkx: unknown error:12514

Solution

On standby server check what services are registered by the listener

    > lsnrctl status

Check the SERVICE_NAME in the CONNECT data for the tnsnames.ora file on standby database server.