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, i.name 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 i.name IS NULL and ps.avg_fragmentation_in_percent >= 30 AND ps.page_count > 1000

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

export PATH=$ORACLE_HOME/bin:$PATH 

before executing

sqlplus / as sysdba

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

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

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.

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

   STARTUP MOUNT;
   

4. and open it

   ALTER DATBASE OPEN;
   

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:

   SHUTDOWN ABORT;
   

2. cp /u20/oradata/controlfile02.ctl /u10/oradata/controlfile04.ctl
   

3. modify PFILE:

   CONTROL_FILE='/u20/oradata/controlfile02.ctl',
   '/u21/oradata/controlfile03.ctl',
   '/u30/oradata/controlfile04.ctl'
   

4. Then mount and open the database:

   STARTUP MOUNT;
   ALTER DATBASE OPEN;
   

Moving control files to a new location

1. Connect as sysdba
2. run

   SHOW PARAMETER CONTROL_FILES;
   

   to check the locations of the control files
3. Shutdown the database:

   SHUTDOWN IMMEDIATE;
   

4. Then run

   SHOW PARAMETER SPFILE;
   

   If the database running on pfile (the path to SPFILE will be null), change the init.ora file in $ORACLE_HOME/dbs directory

   CONTROL_FILES=(/new_loc1/control01.ctl,/new_loc2/control02.ctl,/new_loc3/control03.ctl)
   

   *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

   STARTUP;
   SHOW PARAMETER CONTROL_FILES;