ORA-00257 archiver error. Connect internal only, until freed.

Error: ORA-00257 archiver error. Connect internal only, until freed.

Details: This error occurs when attempting to login via sqlplus

Cause:One of the drives was out of space, the one that stored archive logs.

Solution:Removing old archive logs by starting RMAN and running the following script:

run {
delete noprompt archivelog all completed before 'sysdate-7';
}

The above will remove all archive logs older than 7 days

How to find archive logs location

Execute from sqlplus:

1) archive log list;

2) show parameter db_recovery_file_dest;

or query V$ARCHIVE_DEST

3) select dest_name, status, destination from v$archive_dest; 

UNIX - clearing out files that are older than 30 days

Useful command for clearing out old files:

find ./* -type f -mtime +30 -exec rm {} \;

The above will clear out files that are older than 30 days in the current directory. find ./* will look at the current directory, grabbing all the files (*), -mtime +30 will specify files that are older than 30 days, and -exec rm {} \ will remove all the files that match the condition.

Failed to lock <file> exclusively. Lock held by PID: xxxx

Error: Failed to lock <file> exclusively. Lock held by PID: xxxx

Details: This error occurs when attempting to startup database instance. The file in question usually named lk<SID>

Solution: Kill the process with PID xxxx (kill -9 xxxx)

SQL Server: Managing transaction logs

Today I learned something new - if truncating and shrinking transaction log does not seem to have any effect on transaction log's size, and the recovery mode is Full, one needs to change the mode to Simple, then shirnk the transaction log and change the mode back to Full.

The thing is that sometimes, when the database in Full recovery mode, the transactional log does not shrink, so this is the workaround.

This can also be done in production when necessary, but mirroring must be removed first and the reconfigured after everything is done.

Removing old folders with powershell

In attempt to automate mundane tasks I have put together some scripts to schedule on the server. Here is one of them I am planning on using to remove old snapshots of a SQL Server database that are more than 45 days old:

$initpath = "C:\Snapshots"
(get-date).addDays(-45)
get-childitem -Path $initpath | where-object {$_.lastwritetime -lt (get-date).addDays(-45)} |
Foreach-Object { 
    if($_.Attributes -eq "Directory") {
        $_.FullName 
        if (Test-Path $_.FullName) {
            Remove-Item -r $_.FullName
        }

    }
}

SELECT_CATALOG_ROLE vs. SELECT ANY DICTIONARY

SELECT_CATALOG_ROLE is more restrictive than SELECT ANY DICTIONARY. Although both have privileges to select from the dictionary views, SELECT ANY DICTIONARY allows the user to see the source code of package bodies and triggers which are normally avilable to the DBAs.

SELECT ANY DICTIONARY is a system privelege, while SELECT_CATALOG_ROLE is a role.

SELECT_CATALOG_ROLE allows the user to query V$SESSION but not to create a procedure. In order to create an object on the base object, the user must have the direct grant on the base object, not through a role.

So while both allow the users to query V$DATAFILE, the role does not allow the users to create objects; the system privilege does.

Note: In case where the user is granted SELECT ANY TABLE but when parameter O7_DICTIONARY_ACCESSIBILITY is set to false, the user can access tables in any schema, except SYS, in other words cannot access data dictionary tables, granting the user SELECT_CATALOG_ROLE will enable him to access dictionary objects. Another way is to set O7_DICTIONARY_ACCESSIBILITY to true.