Thursday, February 28, 2013

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-childitem -Path $initpath | where-object {$_.lastwritetime -lt (get-date).addDays(-45)} |
Foreach-Object { 
    if($_.Attributes -eq "Directory") {
        if (Test-Path $_.FullName) {
            Remove-Item -r $_.FullName


Tuesday, February 26, 2013


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.

Friday, February 22, 2013

Page count and fragmentation

I was working on writing a maintenance script for SQL Server that would perform a rebuild on indexes with fragmentation of 30% or above and re-organize indexes with fragmentation of below 30% and found that page count should also be taken into an account. if a page count is less than 1000 then rebuilding an index will not only have no effect of fragmentation but also rebuild procedure will be more costly than fragmentation of an index with 1000 pages or less.
As a result I changed my query slightly:
SELECT OBJECT_NAME(ps.object_id) As TableName, As IndexName,ps.index_type_desc As IndexType, ps.index_depth, ps.index_level, ps.avg_fragmentation_in_percent, ps.fragment_count, ps.page_count 
FROM sys.dm_db_index_physical_stats(DB_ID('DBName'), null, null, null, 'LIMITED') as ps
INNER JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id 
WHERE NOT IS NULL and ps.avg_fragmentation_in_percent >= 30 AND ps.page_count > 1000

Friday, February 15, 2013

ksh: sqlplus: not found

ksh: sqlplus:  not found
Occurred when running
sqlplus / as sysdba
Cause: PATH environmental variable was not set for the instance.

Solution: run
before executing
sqlplus / as sysdba

Wednesday, February 13, 2013

Generating comma-separated lists in Oracle

generating comma-separated lists in oracle is much simpler than in SQL Server. In Oracle 11g, LISTAGG function can be used:

SELECT authors.au_id, LISTAGG(titleauthor.title_id, ', ')
WITHIN GROUP (ORDER BY titleauthor.title_id) as TitleIds 
FROM titleauthor JOIN authors USING(au_id)  
GROUP BY authors.au_id

Tuesday, February 12, 2013

Some useful terminology

Term Definition
Patch Publicly released update to a known bug/issue
Hotfix An update, not always publicly released, used to fix a very specific issue
Maintenance Release An incremental update between software versions to fix multiple outstanding issues
Service Pack A large update that fixes many outstanding issues, normally includes all Patches, Hotfixes, Maintenance releases that precede the service pack

Saturday, February 9, 2013

Oracle certified!

As of this morning I am officially an Oracle Certified Associate DBA! The snow storm did not stop me and I passed my 1z0-052 exam with 90%. Only 75 days after I started learning Oracle and UNIX.

Tuesday, February 5, 2013

Control file recovery

Control file needs recovery when
  • Any one control file specified in a parameter file is lost due to media failure or corrupted
  • New control file is needed when changing the database

When one control file becomes corrupted, oracle database automatically stops running. One can determine which control file is causing a problem by looking into alert log file. Then remove the corrupted control file and copy good control file to the corrupted location
  1. rm -f /u10/oradata/controlfile01.ctl
  2. cp /u20/oradata/controlfile02.ctl /u10/oradata/controlfile01.ctl
  3. Then mount the database
  4. and open it
When a control file is lost, copy a good control file to the new location, modify control_file parameter by removing lost location and add the new location to it:

  1. Shutdown the database:
  2. cp /u20/oradata/controlfile02.ctl /u10/oradata/controlfile04.ctl
  3. modify PFILE:
  4. Then mount and open the database:

Monday, February 4, 2013

Moving control files to a new location

  1. Connect as sysdba
  2. run
    to check the locations of the control files
  3. Shutdown the database:
  4. Then run
    If the database running on pfile (the path to SPFILE will be null), change the init.ora file in $ORACLE_HOME/dbs directory
    *If database is running on spfile, then create PFILE from SPFILE, modify it, then re-create SPFILE from newly altered PFILE.
  5. Copy the control files to the new location using cp command:
    cp /old_loc1/control01.ctl /new_loc1/control01.ctl
    cp /old_loc1/control02.ctl /new_loc2/control02.ctl
    cp /old_loc1/control02.ctl /new_loc3/control03.ctl
  6. Then, while loged in as sysdba user, run