Copying partition statistics (external link)

I have found today a very fresh article on the subject of copying the partition statistics. When copying, one have to understand which statistics are actually copied. E.g. in case of partitions one of concerns is how would like like the copied low and high values. Here (http://orastory.wordpress.com/2012/06/14/copy_table_stats/) You may find some paper touching that issue.

And 2 more:

• http://dioncho.wordpress.com/2009/02/12/how-to-copy-partition-stats-we-got-easier-way/
• https://blogs.oracle.com/optimizer/entry/maintaining_statistics_on_large_partitioned_tables

DB2 backup howto

Disclaimer

I am absolute novice in the world of the DB2 database. Thus I incorporate both a complete ignorance and a fresh look at the same time ;-), so take it into consideration.

Preparations

# set log archives 
mkdir /db2arch
# the logarchmeth1 is a equivalent to the Oracle 
# log_archive_dest_1='LOCATION="/db2arch"'
db2 "update db cfg for BPMDB using logarchmeth1 disk:/db2arch/"
# this is about compression, but do not know the details
db2 "update db cfg for BPMDB using logarchcompr1 on"

# and here a check if all is set as expected
db2 "GET DATABASE CONFIGURATION FOR ${database}"
[..]
 First log archive method                 (LOGARCHMETH1) = DISK:/db2arch/
 Archive compression for logarchmeth1    (LOGARCHCOMPR1) = ON
 Options for logarchmeth1                  (LOGARCHOPT1) = 
 Second log archive method                (LOGARCHMETH2) = OFF
 Archive compression for logarchmeth2    (LOGARCHCOMPR2) = OFF
 Options for logarchmeth2                  (LOGARCHOPT2) = 
[..]

# after the preparations one have to perform once more a full backup offline
db2 "backup database ${database} to /db2backup"

Online backup operation

# load the profile 
. $HOME/sqllib/db2profile

# list databases
db2 list database directory 

# get database configuration
db2 "GET DATABASE CONFIGURATION FOR ${database}"

# the important parameters are:
# TRACKMOD, LOGARCHMETH1, LOGARCHMETH2, LOGARCHOPT1, LOGARCHOPT2
# I simply get them from the above command for configuration
# Because I want few of them, I call for all parameters, then grep, 
# but one may call only those of interest

# finally backup command
cmd="BACKUP DATABASE ${database} ONLINE"
if [ ${opt_tracking##*=} = 'YES' ]; then
  cmd="$cmd INCREMENTAL " ## here one may add also DELTA
fi
cmd="$cmd TO \"${BKP_DIR}\" "
cmd="$cmd WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 INCLUDE LOGS "
cmd="$cmd WITHOUT PROMPTING"
db2 "$cmd"

DBMS_REDEFINITION howto (external link)

http://www.ora-solutions.net/papers/Online_Redefinition_Oracle_10gR2.pdf - under this link one may find quite concise and thorough example of the DBMS_REDEFINITION package. Though a little bit old (version 10g R2), still it provides the MUST-KNOW content.

Other databases

Lately I had to look at databases other than the Oracle RDBMS. Thus will show up other things.

Calculating a segment growth in the Oracle RDBMS

There are few ways I can think of, which allow for monitoring or calculation of segments' growth - the easiest one is to snap whole the DBA_SEGMENTS table into some helper store and apply custom logic, which may be a focus on the biggest segments or the ones with biggest growth rate.
I would like however to describe a different way using the DBA_HIST_SNAPSHOT and DBA_HIST_SEG_STAT tables.

License

First things first - is one authorized to use it? As You certainly know there is nothing sure about the Oracle licensing or at least such is the common viewpoint.
I found in this regards the following excerpt (the source), but it can not be used as an excuse:

All data dictionary views beginning with the prefix DBA_HIST_ are part of this pack, along with their underlying tables.The only exception are the views: DBA_HIST_SNAPSHOT, DBA_HIST_DATABASE_INSTANCE, DBA_HIST_SNAP_ERROR, DBA_HIST_SEG_STAT, DBA_HIST_SEG_STAT_OBJ, and DBA_HIST_UNDOSTAT. They can be used without the Oracle Diagnostics Pack license.

Anyway we have got such license for our environments for other reasons, so do not care much for it.

The DBA_HIST_SNAPSHOT

The most important columns for me are:

• DBID, SNAP_ID, INSTANCE_NUMBER - those 3 I enlist at once because they construct the only index created on underlying table WRM$SNAPSHOT (the order important). Of course the column 3 is important only if one uses RAC and the column 1 only if the data are gathered in one place from many databases. In my simple scenario I need to provide despite the SNAP_ID only the DBID (select dbid from v$database) in order to allow the optimizer to use that index if reasonable.
• STARTUP_TIME - this is a timestamp of the current database startup, very important border - all the TOTAL* values are cumulated since this timestamp. One example here: the SPACE_USED_TOTAL column not necessarily will show You the current storage space used by the segment. In fact it will show You the sum of all the deltas (in this case the SPACE_USED_DELTA column) since the STARTUP_TIME.
• BEGIN_INTERVAL_TIME, END_INTERVAL_TIME - the ceasuras placing a snapshot in time. I use mostly the BEGIN_INTERVAL_TIME as means to choose only one month worth snapshots.

The DBA_HIST_SEG_STAT

The most important columns here are:

• DBID, SNAP_ID, INSTANCE_NUMBER, TS#, OBJ#, DATAOBJ# - again I list them as one. Again those columns form the only index on the underlying table WRH$_SEG_STAT. Despite that SNAP_ID provides the link to the DBA_HIST_SNAPSHOT, while TS# identifies the tablespace and the OBJ# and DATAOBJ# a particular segment
• SPACE_USED_TOTAL, SPACE_USED_DELTA, SPACE_ALLOCATED_TOTAL, SPACE_ALLOCATED_DELTA - DELTA shows the increment in used space for the current snapshot, TOTAL - aggregate of DELTAs (in this case the sum) since the startup.
• there are plenty of other counters, but they are irrelevant in the context of storage space, so I do not write about them

Finally the SQL

I will present here few possibilities. Here the first one:

with snaps as (select min(snap_id) min_snap, max(snap_id) max_snap 
from dba_hist_snapshot where begin_interval_time > ADD_MONTHS(sysdate,-1))
select 
O.TABLESPACE_NAME,
O.OWNER||'.'||O.OBJECT_NAME oid,
o.object_type,
--min(SNAP_ID) MIN_OBJ_SNAP,
--max(SNAP_ID) max_obj_snap,
--MIN(H.SPACE_USED_TOTAL) min_space_used, 
SUM(H.SPACE_USED_DELTA) SPACE_USED, 
SUM(H.SPACE_ALLOCATED_DELTA) space_alloc
from
DBA_HIST_SEG_STAT H join dba_hist_seg_stat_obj o 
on h.dbid=o.dbid and h.ts#=o.ts# and h.obj#=o.obj# and h.dataobj#=o.dataobj#
WHERE 1=1
AND H.SNAP_ID BETWEEN (SELECT MIN_SNAP FROM SNAPS) AND (SELECT MAX_SNAP FROM SNAPS)
and H.DBID = (select DBID from V$DATABASE)
and H.INSTANCE_NUMBER = (select INSTANCE_NUMBER from V$INSTANCE)
and O.OWNER != '** MISSING **' -- segments already gone
and O.OBJECT_NAME not like 'BIN$%' -- recycle-bin
and O.OBJECT_NAME not like 'SYS_%' -- LOBs, etc - not too representative
AND o.OWNER NOT IN ('APEX_030200','SCOTT','OWBSYS','PERFSTAT',
'FLOWS_FILES','PUBLIC','SYS','SYSTEM','OUTLN','DIP','DBSNMP','WMSYS', 
'EXFSYS','DMSYS','CTXSYS','XDB','ANONYMOUS','ORDSYS','ORDPLUGINS','SI_INFORMTN_SCHEMA',
'MDSYS','OLAPSYS','MDDATA','SYSMAN','MGMT_VIEW','VIP')
group by 
O.TABLESPACE_NAME,
O.OWNER||'.'||O.OBJECT_NAME,
o.OBJECT_TYPE
having SUM(H.SPACE_ALLOCATED_DELTA)>0

Duplicating with rman

The assumption is to create a copy of the database on the same host in order to run 2 independent database instances. It can be done in many ways:

• with RMAN 
• with expdp and standard dump
• with expdp and transportable tablespaces
• with cp command and dbnewid tool - look here

I present here one of the possible ways with the RMAN utility:

1. check if the database is set in ARCHIVELOG

   select log_mode from v$database;
   -- if the answer is NOARCHIVELOG then
   shutdown immediate; 
   startup mount
   alter database archivelog;
   alter database open;
   

2. prepare auxiliary instance
   • create directories for the clone database
     Here I am not sure entirely at the moment how it works. I provided explicite in the parameter file the value for the control_files and am quite certain that have to create the directory myself. In case of datafiles and redologs, if one first sets new names to new, then the RMAN creates directories according to the OMF methodology.

     mkdir -p /oracle/oradata/NEWSID/controlfile
     mkdir -p /oracle/oradata/NEWSID/datafile #optional
     mkdir -p /oracle/oradata/NEWSID/onlinelog #optional
     

   • create spfile for the clone database (if this is not a standby then the only requirement is DB_NAME)

     export ORACLE_SID=NEWSID
     sqlplus / as sysdba
     

     All the OMF settings indicate the same location - it does not make sense to set both db_create_online_log_dest_1 and db_create_online_log_dest_2 to the same value unless one works on some kind of enterprise class storage.

     create pfile='NEWSID.pfile' from spfile;
     

     *.audit_file_dest='/home/oracle/admin/NEWSID/adump'
     *.compatible='11.2.0.3.0'
     *.control_files='/oracle/oradata/NEWSID/controlfile/control01.ctl'
     *.db_block_size=8192
     *.db_create_file_dest='/oracle/oradata/'
     *.db_create_online_log_dest_1='/oracle/oradata/' 
     *.db_create_online_log_dest_2='/oracle/oradata/'
     *.db_domain=example.com
     *.db_file_multiblock_read_count=16
     *.db_files=800
     *.db_name=NEWSID
     *.diagnostic_dest='/oracle'
     *.job_queue_processes=1000
     *.nls_language='POLISH'
     *.nls_territory='POLAND'
     *.open_cursors=300
     *.pga_aggregate_target=512M
     *.processes=1000
     *.remote_login_passwordfile='EXCLUSIVE'
     *.service_names='NEWSID'
     *.sessions=1105
     *.sga_target=800M
     *.undo_management='AUTO'
     *.undo_tablespace='UNDOTBS1'
     

     create spfile from pfile='NEWSID.pfile';
     

   • copy password file

     cp orapwOLDSID orapwNEWSID

   • add static definition of the clone database to listener.ora

     # static definitions
     SID_LIST_LISTENER =
       (SID_LIST =
         ## this part is not necessary
         (SID_DESC =
           (GLOBAL_DBNAME = OLDSID.dev.example.com)
           (ORACLE_HOME = /oracle/product/11.2.0.2)
           (SID_NAME = OLDSID)
         )
         ## this part is mandatory
         (SID_DESC =
           (GLOBAL_DBNAME = NEWSID.dev.example.com)
           (ORACLE_HOME = /oracle/product/11.2.0.2)
           (SID_NAME = NEWSID)
         )
       )
     
     # listener definition prepared for dynamic registration
     LISTENER=
       (DESCRIPTION=
           (ADDRESS=(PROTOCOL=tcp)(HOST=test-dbcluster-01)(PORT=1521)))
     
     

     After such changes in the listener definition it is not enough simply reload. One have to stop and start the listener.
   • run the clone instance

     startup mount

3. run the duplicate command in RMAN

   connect target sys/sys01@OLDSID.example.com
   connect auxiliary sys/sys01@NEWSID.example.com
   run {
     ## this setting sets all the files to indicated directory
     ## and changes file names to %U format
     #SET NEWNAME FOR DATABASE TO '/oracle/oradata/%U';
     ## this setting is compatible with the OMF
     SET NEWNAME FOR DATABASE TO NEW; 
     DUPLICATE TARGET DATABASE to transpl
     FROM ACTIVE DATABASE;
   }
   

The pagination techniques with the Oracle RDBMS (external link)

Quite extensive overview of pagination techniques. And here an article from J. Lewis on the same subject.