##################
# About: This file contains step by step installation and usage instructions
#        for "CoMinder SpaceStat" scripts collection 
#        Read carefully all the file before execution of any script
#
# Version: 0.55 (2013.06.16)
#
# Changes:
# 2012.06.27: 0.53: 
# 2012.10.16: 0.54: many changes compared with 0.53 - read carefull
# 2013.06.16: 0.55: installation almost the same as in 0.54, but instructions improved  

#
# Part 0 - Introduction
#
# * read README.txt for product description and general notes
# * this is Installation and Usage manual, NOT upgrade instructions
# * for UPGRADE - check UPGRADE.txt
# * Installation/upgrade actions in DB should be done by user with DBA role granted.
#

#
# Part I - Installation in OS and target Oracle DB
#
# In present version all data are kept per database, one-db-repository mode for all 
# may be introduced later.
 
# See also: create\sps_create.sql - use it as step by step scenario, 
# not as auto-installer, because untypical problems/exceptions not processed
# But if it looks OK (You had adjusted cre_tablespace.sql, cre_user.sql and cre_settings.sql)
# you can do ALL steps by running in sql*plus the create\sps_create.sql script  

#
# Here is what should be done in details for Linux/UNIX environment.
# For Windows - Just understand what should be done and do similar things.
# Later version will have manual for Windows too.
#
1.0) Download latest version available from http://www.cominder.eu/index.php?action=solutions
     to your local PC or upload on server.
     Decide were the software will be installed. We use some variables to point that location.
     You can use your own values according to deployment policy of your company/organisation.


COMINDER_BASE - base directory for CoMinder products and it's configuration and logs
COMINDER_PROD - root directory for CoMinder products binaries and scripts
                it will have sub-dirs for db, os and other tools
SOFT_HOME - directoiry where SpaceStat will be installed and run 
                 
# for example
COMINDER_BASE=/u01/app/cominder/
COMINDER_PROD=${COMINDER_BASE}/product
SOFT_HOME=${COMINDER_PROD}/db/space_stat
# optional
export COMINDER_BASE COMINDER_PROD SOFT_HOME  

mkdir -p ${COMINDER_PROD}/db
mkdir -p ${COMINDER_BASE}/LOG  # for logs if needed, can create in any place, 
                               # but remember to point output of schedules scripts calls to that dir 

# get archive and copy it to target location 
scp ./space_stat.0.55.tar.gz  ${COMINDER_PROD}/db/space_stat.0.55.tar.gz 
cd ${COMINDER_PROD}/db
    
tar -xzvf space_stat.0.55.tar.gz
space_stat.0.55/
space_stat.0.55/create/
space_stat.0.55/.............

# rename directory 
mv space_stat.0.55 space_stat
cd space_stat

# or make symb.link to latest version
# ln -s space_stat.0.55 space_stat
# or leave directory with version number if you wish

#
# Steps below should be done for every target database!
# Ignore DROP errors like:
ORA-02289: sequence does not exist
ORA-00942: table or view does not exist
#
# Steps 1.1 - 1.5 also listed in create/sps_create.sql

# Review it and if it looks OK (You had reviewed cre_tablespace.sql, cre_user.sql and cre_settings.sql)
# you can do ALL (1.1-1.5) steps by running in sql*plus the create\sps_create.sql script
export ORACLE_SID=DBSID
sqlplus /nolog
SQL>
connect / as sysdba
@create/sps_create.sql

# check sps_create.log for errors

# That steps will be listed here with some more explanations.  

1.1) create tablespace SPACESTAT
create/cre_tablespace.sql - is DDL example, adjust path and space management option

# tablespace name SPACESTAT is only supported for installation in present version
# but you can move the SpaceStat segments to any tablespace as you wish after installation done.
# If so, be carefull during future upgrades
# Later version(s) may include some customisation

1.2) create user SPACESTAT  
create\cre_user.sql
# quotas and grants also there
# ! note: some permissions a bit too much compared to required set of privilegies
# review the code and set more restrictions yourself or wait next version
# 

1.3) create tables, indexes, constraints
create\cre_tables.sql
# indexes now kept together with tables in one tablespace
# may be not the best practice, but StatsPack do in the same style :)
# It's a joke. Will be improved in future versions
#
1.4) load default settings into settings table. Adjust before run or update later
create\cre_settings.sql

1.5) install views and code: packages, procedures, functions 
create\cre_views.sql
  # 1 view for obsolete data
create\cre_code_snap.sql
  # creates 3 procedures
create\cre_code_purge.sql
  # creates 1 procedure to purge obsolete SPS statistic

If everything OK, update installation status:

update SPACESTAT.SPS_VERSION set status='VALID',upgrade_ts=sysdate 
 where module_name='SPS_ORADB' and module_version='0.55'
   and status='install';
commit;

# --  
#
# next steps to do after  @create/sps_create.sql too.


1.6) Test it is working
# You can do some snaps with DBA user or SPACESTAT user
# DBS - segments (in DB)
# DBF - files (in DB)
execute SPACESTAT.SPS_MAKE_SNAP('DBF');
execute SPACESTAT.SPS_MAKE_SNAP('DBS');
#
# or ALL - will make snapshots of all supported types
execute SPACESTAT.SPS_MAKE_SNAP('ALL'); 
#
# change something in db and run the procedure again to have 2nd snapshots with unique ID 
execute SPACESTAT.SPS_MAKE_SNAP('ALL');

1.7) check data is collected

set lines 400
set pages 100
col DB_NAME format a16
select * from SPACESTAT.SPS_SNAPS;
select count(1) from SPACESTAT.SPS_SNAP_DATA;
quit

# Note: sps_snap.sql will do the same

#
# Part II - Setup scripts in OS
#       
Check snapshoting scripts from OS session
2.1) set permissions
ls -l ${SOFT_HOME}/bin/*.* 
chmod o-rwx ${SOFT_HOME}/bin/*.*
chmod u+x ${SOFT_HOME}/bin/*.sh
ls -l ${SOFT_HOME}/bin/*.*
-rwxr-----  1 oracle oinstall 1803 May  8 11:33 /u01/app/cominder/product/db/space_stat/bin/sps_purge.sh
-rw-r-----  1 oracle oinstall 1341 Oct 16  2012 /u01/app/cominder/product/db/space_stat/bin/sps_purge.sql
-rwxr-----  1 oracle oinstall 2090 Apr 12 18:40 /u01/app/cominder/product/db/space_stat/bin/sps_snap.sh
-rw-r-----  1 oracle oinstall  344 Oct 16  2012 /u01/app/cominder/product/db/space_stat/bin/sps_snap.sql

2.2)
# Check snap scripts are working
# If You are on Windows write your own .bat that will call sps_snap.sql, sps_purge.sql
#                       or wait for some next version with Windows support
# If You are on Linux/UNIX - call Shell scripts:  
#
# check snapshoting is working
${SOFT_HOME}/bin/sps_snap.sh DBSID
...
SQL>
PL/SQL procedure successfully completed.
Commit complete.

  COUNT(1)
----------
       406
#


#
# Part III - generate reports
#
3.1) Check reports execution from sql*plus. Reports are in reports directory:
check reports/DIRINFO.txt for details
All reports are safe - running reports do SELECT ... only
So to find what best suits Your needs just try ALL of them and review output

#
# Part IV - setup old unnecessary snapshots information purging
#
4.1) First of all - set retention settings if not done before 
  edit and execute: 
  cfg/cfg_setting.sql
IMPORTANT: take a look inside script to understand settings

#
4.2) Make report of actual or obsolete snapshots with
reports/sps_report_snaps_actual.sql
reports/sps_report_snaps_obsolete.sql

#
4.3) Check purge is working
${SOFT_HOME}/bin/sps_purge.sh DBSID

#
# Part V - update scheduled execution
#

# If all OK with manual snapshoting and purge, schedule regular tasks with DB Scheduler or cron:
# Bat-file for Windows is missing, but hope You can write your own and use Task Scheduler
#
# 
5.1) 
# Daily SpaceStat DB segments and files used space statistic collection
15 01 * * * /u01/app/cominder/product/db/space_stat/bin/sps_snap.sh DBSID  1>/u01/app/cominder/LOG/sps_DBSID.snap.log 2>&1
# Purge too old DB segments and files used space statistic
15 02 * * * /u01/app/cominder/product/db/space_stat/bin/sps_purge.sh DBSID 1>/u01/app/cominder/LOG/sps_DBSID.purge.log 2>&1
#

# 
# Part VI - clean
#

If everything OK - clean or arhive the source package
rm -r ${COMINDER_PROD}/db/space_stat.0.55.tar.gz


#
# Part VII - Feedback
#
 
#
# Feedback is welcomed in any form to support((at))cominder.eu
#
# To subscribe/unsubscribe to SpaceStat news, just drop e-mail
#  with subject "Subscribe SpaceStat news" (or Unsubscribe)
# Name, Lastname, Company, position and anything more - are optional.
#


#
# Part VIII - UnInstall
#

# Use create/sps_drop.sql script to remove all SPACESTAT content from the database
# except SPACESTAT tablespace
