Install Tora on Ubuntu 9.10

Tora is client for oracle like Toad. http://torasql.com

OS: Ubuntu 9.10
Tora version: 2.2.0
Oracle client version: 11.1.0.1


Download oracle client packages for linux from http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/linuxsoft.html
oracle-instantclient-basiclite-11.1.0.1-1.i386.rpm
oracle-instantclient-sqlplus-11.1.0.1-1.i386.rpm
oracle-instantclient-devel-11.1.0.1-1.i386.rpm

Install alien for rpm packages on Ubuntu
# sudo apt-get install alien

Install oracle client packages.
# sudo alien -i oracle-instantclient-basiclite-11.1.0.1-1.i386.rpm
# sudo alien -i oracle-instantclient-sqlplus-11.1.0.1-1.i386.rpm
# sudo alien -i oracle-instantclient-devel-11.1.0.1-1.i386.rpm


Edit /etc/ld.so.conf.d/oracle.conf
# sudo vi /etc/ld.so.conf.d/oracle.conf
add this line /usr/lib/oracle/11.1.0.1/client/lib
# sudo ldconfig
# sudo apt-get install libaio1


run sqlplus and see it is working.
# sqlplus
SQL*Plus: Release 11.1.0.6.0 - Production on Tue Dec 22 10:06:27 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Enter user-name


Building TORA
Continue as root
# sudo -i
Get packages for compiling.
# apt-get build-dep tora
# apt-get install libqt3-mt-dev libqt3-compat-headers libqscintilla-dev build-essential g++ gcc autoconf automake flex zlib1g-dev docbook-xsl debhelper alien libaio1 dpatch fakeroot xsltproc texi2html texinfo libqt3-mt-psql libqt3-mt-odbc config-package-dev cmake qt4-dev-tools
# cd tora-2.0.0
Environment variables also add this lines to your users .bashrc file.
# export ORACLE_HOME="/usr/lib/oracle/11.1.0.1/client"
# export LD_LIBRARY_PATH="${ORACLE_HOME}/lib"
# export TNS_ADMIN="${ORACLE_HOME}"
# ln -s /usr/include/oracle/11.1.0.1/client/ ${ORACLE_HOME}/include
# debian/rules binary
# dpkg -i ../tora_2.0.0-4build2_i386.deb

Now you can run Tora. Log out and log in to be sure you set environment variables. Also put your tnsnames.ora here /usr/lib/oracle/11.1.0.1/client

Export data from oracle via vbscript

I need to export data from oracle so it will be possible for me to generate alert from that log file. I do this because we do not have permission to change anything in the database.
Be carefull about fields section, you must know how many columns in the table.

'Parameters
path="c:\path.txt"
strHost="servername"
strPort="1529"
strSID="oraclesid"
strUsername="user"
strPassword="password"
dquerry="select * from table"


'Filesystem Object
Set objFSO = CreateObject("Scripting.FileSystemObject")

'Check if file exist 
If objFSO.FileExists(path) Then
  Set objFile = objFSO.OpenTextFile(path,8,True)
else
  wscript.echo "there is no file I am creating"
  set objFile = objFSO.createtextfile(path)
  'set objFile = objFSO.OpenTextFile(path,8, True)
end If


'open connection to database
Dim strCon
strCon = "Driver={Microsoft ODBC for Oracle}; " & _
         "CONNECTSTRING=(DESCRIPTION=" & _
         "(ADDRESS=(PROTOCOL=TCP)" & _
         "(HOST=" & strHost & ")(PORT=" & strPort  & "))" & _
         "(CONNECT_DATA=(SID = " & strSID  & "))); uid=" & strUsername  & ";pwd=" & strPassword  & ";"
Dim oCon: Set oCon = WScript.CreateObject("ADODB.Connection")
Dim oRs: Set oRs = WScript.CreateObject("ADODB.Recordset")
oCon.Open strCon


Set oRs = oCon.Execute(querry)
While Not oRs.EOF
 objFile.WriteLine(oRs.Fields(0).Value &"," & oRs.Fields(1).Value &"," & oRs.Fields(2).Value &"," & oRs.Fields(3).Value &"," & oRs.Fields(4).Value 

&"," & oRs.Fields(5).Value &"," & oRs.Fields(6).Value &"," & oRs.Fields(7).Value &"," & oRs.Fields(8).Value &"," & oRs.Fields(9).Value &"," & 

oRs.Fields(10).Value &"," & oRs.Fields(11).Value &"," & oRs.Fields(12).Value &"," & "Q3" &"," & "Q3" &"," & oRs.Fields(15).Value &"," & oRs.Fields(16).Value 

) 
 oRs.MoveNext
Wend
oCon.Close
Set oRs = Nothing
Set oCon = Nothing

Relocating Oracle Data Files

Check for location
SQL> SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'TABLESPACE';

FILE_NAME
--------------------------------------------------------------------------------
BYTES
----------
/dbfiles/oradata03/loc/FILE_DATA01.dbf
4294967296


Take tablespace offline
SQL> ALTER TABLESPACE FILE_DATA OFFLINE NORMAL;

Tablespace altered.

Copy file to the new location.
cp /dbfiles/oradata03/loc/FILE_DATA01.dbf /dbfiles/oradata05/loc/FILE_DATA01.dbf
Check for file permission and owner.

Do it for Oracle
SQL> ALTER TABLESPACE FILE_DATA RENAME DATAFILE '/dbfiles/oradata03/loc/FILE_DATA01.dbf' to '/dbfiles/oradata05/loc/FILE_DATA01.dbf';

Tablespace altered.


Take tablespace online
SQL> ALTER TABLESPACE FILE_DATA ONLINE;

Tablespace altered.

Rman settings

configure rman settings before starting backup
rman> connect target
rman> show all;
rman> configure retention policy to recovery window of 7 days;
RMAN> configure CONTROLFILE AUTOBACKUP on;

SQL> show parameter db_recovery_file;
look how to configure http://tlepsh.blogspot.com/2007/08/oracle-flash-recovery-area.html
move oracle archive mode http://tlepsh.blogspot.com/2007/08/moving-oracle-to-archive-mode.html


RMAN> configure default device type to disk;
RMAN> configure controlfile autobackup on;
RMAN> configure device type disk parallelism n; n=1,2,3...
RMAN> backup database plus archivelog;
RMAN> list backup summary;

Flash recovery space usage

delete backed up archivelog
rman> backup archivelog all delete all input;

reclaim archived log
delete archivelog
rman> delete archivelog until time 'sysdate-2';

delete old backup
rman> report obsolote;
http://tlepsh.blogspot.com/2007/08/oracle-deleting-old-backup-from-rman.html
rman> delete expired backup;
RMAN> report obsolete;
RMAN> delete obsolete;

Oracle point in time recovery RMAN Backup Restore

Backup database
RMAN> connect target
RMAN> backup database;
# sqlplus hr/hr
SQL> create table new as select * from employees;
SQL> insert into new select * from new;
SQL> commit;
SQL> select count(*) from new;
COUNT(*)
----------
214
SQL> alter session set nls_date_format = 'dd-mm-yyyy hh24:mi';
SQL> select sysdate from dual;

SYSDATE
----------------
24-10-2007 18:47

# sqlplus / as sysdba
SQL> select * from v$log;

get sequence number
Create new records
SQL> insert into new select * from new;
SQL> select count(*) from new;
COUNT(*)
----------
428


RMAN> shutdown immediate;

Restore to a time
RMAN> startup mount;
RMAN> run {
2> restore database until time "to_date('2007-10-24 18:48','YYYY-MM-DD HH24:MI:SS')";
3> recover database until time "to_date('2007-10-24 18:48','YYYY-MM-DD HH24:MI:SS')";}

Check that
# sqlplus / as sysdba
SQL> alter database open resetlogs;
# sqlplus hr/hr
SQL> select count(*) from new;

COUNT(*)
----------
214

Oracle RMAN backup/restore

Backup database
RMAN> connect target;
RMAN> backup database;
RMAN> shutdown immediate;

delete files
# mv *.dbf backup/

restore database
RMAN> startup;
RMAN> restore database;

Oracle tablespace Backup/Restore (OS backup)

I tested tablespace recovery.

Be sure your database in archive mode
SQL> archive log list;
SQL> select count(*) from hr.employees;

Begin backup.
SQL> alter tablespace example begin backup;
Check the tablespace in backup state
SQL> select * from v$backup;
Prepare hr account
SQL> alter user hr account unlock;
SQL> alter user hr identified by hr;
# sqlplus hr/hr
Make some changes
SQL> create table employees2 as select * from employees;

Backup your file (This means that if you put your database in backup state you can do filesystem backup for oracle)
# cp example01.dbf example01.dbf.backup
End backup mode
# sqlplus / as sysdba
SQL> alter tablespace example end backup;

Shutdown database, delete table and restore table
SQL> shutdown abort;
# rm example01.dbf
# sqlplus / as sysdba
SQL> startup
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/example01.dbf'

You want database to be open while you are restoring tablespace.
SQL> alter database datafile '/path/example01.dbf' offline;
SQL> alter database open;

Restore tablespace and recover it
# mv example01.dbf.backup example01.dbf
SQL> recover tablespace example;
SQL> alter tablespace example online;

Verify recovery
# sqlplus hr/hr
SQL> select count(*) from employees2;

Oracle Offline Database Backup/Restore (OS backup)

I did some tests. Here I backed up oracle and do some changes. Later I restored with copying files. You can just copy or use any backup software.

Backup
RMAN> connect target;
RMAN> shutdown immediate;
copy oracle datafiles to backup directory
#cp -R /oracle_datafiles_dir/* /backup_dir/
remove redolog files
#rm redo*.log /backdir

Break down your database
open database and do some changes
RMAN> startup;
shutdown database and delete datafiles
RMAN> shutdown immediate;
#rm /oracle_datafiles_dir/*.dbf

Restore
Move backup files to original directory
#cp -R /backup_dir/* /oracle_datafiles_dir/
start database
RMAN> startup mount;
RMAN> recover database until cancel;
RMAN> alter database open resetlogs;

You do not have to do last two steps if you backup redo logs and restore it.

Calculate DB size with sqlplus in Oracle

select sum(bytes)/1024/1024 "Meg" from dba_data_files;

Oracle deleting old backup from rman

rman
RMAN>connect catalog username/password@RMANSID;
connected to recovery catalog database
RMAN>DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 7 DAYS;

or
delete expired backup;

Oracle Flash Recovery Area configuration

Changing directory
alter system set db_recovery_file_dest = '/data01';

Changing size
alter system set DB_RECOVERY_FILE_DEST_SIZE= 5G;

Moving oracle to archive mode

shutdown immediate
startup nomount

alter database mount;
alter database archivelog;
alter database open;

Verifying archive mode
archive log list;
select log_mode from v$database;

Oracle Schema changes

I use this script to track schema changes for a schema on Oracle server.
(Select from top to bottom and copy,paste to any editor to get all)

user=username
password=password
DWH=schema
OWNERDWH=schemaowner
WORKDIR=/var/log/
OUTPUT=/var/log/aname.log
mv $WORKDIR/schema_$DWH.log $WORKDIR/schema_$DWH.log.1
sqlplus $user/$password < $WORKDIR/schema_$DWH.log.draft
set pages 10000
set hea off
set lines 500
set feedback off
column TABLE_NAME format a30
column COLUMN_NAME format a25
column DATA_TYPE format a8
column DATA_LENGTH format 9999
column DATA_PRECISION format 999
column DATA_SCALE format 999
column NULLABLE format a1
column COLUMN_ID format 999
SELECT ATC.TABLE_NAME
     ,ATC.COLUMN_NAME
     ,ATC.DATA_TYPE
     ,ATC.DATA_LENGTH
     ,ATC.DATA_PRECISION
     ,ATC.DATA_SCALE
     ,ATC.NULLABLE
     ,ATC.COLUMN_ID
FROM ALL_TABLES ATT, ALL_TAB_COLUMNS ATC
WHERE ATT.OWNER = '$OWNERDWH'
 AND ATC.OWNER = ATT.OWNER
 AND ATC.TABLE_NAME = ATT.TABLE_NAME;
exit;
EOF
cat $WORKDIR/schema_$DWH.log.draft |grep -v '^$'| egrep -v "10.1.0.5.0|Copyright|Connected to|OLAP and Data Mining|SQL\>" > $WORKDIR/schema_$DWH.log
#diff $WORKDIR/schema_$DWH.log $WORKDIR/schema_$DWH.log.1
cmp $WORKDIR/schema_$DWH.log $WORKDIR/schema_$DWH.log.1 >> $OUTPUT