Sql Script to read BLOB which contains multiple double type arrays data stored in it.

SET serveroutput ON size unlimited;
DECLARE
    v_raw_length     NUMBER := 0;
    v_length         NUMBER := 0;
    v_counter        NUMBER := 0;
TYPE REC_LONG_RAW
IS
  RECORD
  (
    V_LONG_RAW BLOB := empty_blob);
TYPE RAW_TABLE_TYPE
IS
  TABLE OF REC_LONG_RAW INDEX BY BINARY_INTEGER;
  RAW_table RAW_TABLE_TYPE;
BEGIN
 
    BEGIN
      BEGIN
        FOR j IN
        (

select  id,blob_Data raw_blob,
        dbms_lob.getlength(blob_Data) raw_length
        from Blob_Array
        where dbms_lob.getlength(Bulk_Data)>0
        and Blob_Array.Id = 180998
        )
        LOOP
          BEGIN

            v_raw_length := j.raw_length;
              V_LENGTH :=1;
              V_COUNTER:=1;
              WHILE V_RAW_LENGTH>0
              LOOP
              v_raw_length:=v_raw_length -8;
                dbms_output.put_line('v_counter ' || v_counter || '-----' || TO_CHAR(utl_raw.cast_to_binary_double(utl_raw.substr(j.raw_blob,v_length,8),2),'9999999999999999999999999999999999999.999') );
                V_LENGTH := V_LENGTH+8;
                V_COUNTER:=V_COUNTER+1;
              END LOOP;
          END;
        END LOOP;
     
      END;
    EXCEPTION
      WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE (CHR(13)||' ****** Other Error(s) ************ '|| SQLERRM || CHR(13)) ;
    END;

END;

Query to find data in Oracle tables.

SET SERVEROUTPUT ON

declare

     num_rows number;

     sql_text varchar2(250);

     sql_info varchar2(100);

     v_schema varchar2(100):= 'schema name that contains tables';

 begin

     dbms_output.enable(10000000);

     for x in (select table_name, column_name from dba_tab_columns

                where data_type in ('VARCHAR','VARCHAR2','CHAR')

                  and owner=v_schema

                  AND ( TABLE_NAME NOT LIKE 'BIN%'))

     loop

        begin

            sql_text:='select count(0) into :num_rows from '||v_schema||'.'||x.table_name||' where lower( '||x.column_name||' ) =''test data''';

            -- dbms_output.put_line (sql_text);

            execute immediate sql_text into num_rows;

            if num_rows>0

            then

              sql_info:='select '||x.column_name||'  from '||x.table_name||'

              union all';

              -- dbms_output.put_line (sql_info);

            end if;

        exception when others then

        dbms_output.put_line (sql_text);

        end;

     end loop;

 end;

Shell script to backup DB2 database even if applications are connected with group owner ID and schedule at specific time

In this document we will use two scripts.
First script backup the DB2 database and second script deletes backup older than 90 days.



1. backup.sh

# This script backup the DB2 database named mydb at the path /home/db2inst1/backups


#!/bin/sh

/home/db2inst1/sqllib/db2profile

/home/db2inst1/sqllib/bin/db2 force applications all
/home/db2inst1/sqllib/bin/db2 deactivate database moit
/home/db2inst1/sqllib/bin/db2 force applications all

/home/db2inst1/sqllib/bin/db2 backup database testdb to "/home/db2inst1/backups"

#db2set DB2COMM=TCPIP

# after creating backup.sh file execute following command to make the file executable.

# chmod -R 775 backup.sh

 2. housekppping.sh

# This script deletes backup files older than the 90 days 

#!/bin/sh

logfile= /home/db2inst1/backups/backup.log

rm -f $logfile

for file in `find  /home/db2inst1/backups  -mtime +90 -type f -name '*.backup' `

do

  echo "deleting: " $file >> $logfile

  rm $file

done

exit 0

# after creating backup.sh file execute following command to make the file executable.

# chmod -R 775 housekppping.sh

Execute following command to schedule your script to run at 1 pm and 10 pm daily 

[root]# crontab -e

0 13 * * * /home/db2inst1/backups/backup.sh

0 22 * * *  /home/db2inst1/backups/backup.sh

0 22 * * *  /home/db2inst1/backups/housekeeping.sh

Shell script to backup postgres database and schedule at specific time

In this document we will use two scripts.
First script backup the Postgresql database and second script deletes backup older than 90 days.



1. backup.sh

# This script backup the postgresql db mydb at the path /home/postgres/9.5/backups


#!/bin/sh

pg_dump  -U postgres   -p 5432 -F c -b -v -d mydb> /home/postgres/9.5/backups/mydb_`date +%d-%m-%Y"_"%H_%M_%S`.backup

# after creating backup.sh file execute following command to make the file executable.

# chmod -R 775 backup.sh

 2. housekppping.sh

# This script deletes backup files older than the 90 days 

#!/bin/sh

logfile=/home/postgres/9.5/backups/backup.log

rm -f $logfile

for file in `find /home/postgres/9.5/backups  -mtime +90 -type f -name '*.backup' `

do

  echo "deleting: " $file >> $logfile

  rm $file

done

exit 0

# after creating backup.sh file execute following command to make the file executable.

# chmod -R 775 housekppping.sh

Execute following command to schedule your script to run at 1 pm and 10 pm daily 

[root]# crontab -e

0 13 * * * /home/postgres/9.5/backups/backup.sh

0 22 * * * /home/postgres/9.5/backups/backup.sh

0 22 * * * /home/postgres/9.5/backups/housekeeping.sh





Use following query to restore the backup.


pg_restore -U postgres -F c  -v  -d mydb  < mydb_21-02-2017_13_00_04.backup

Batch Script to Create postgres backup and copy backup files from local drive to network also send email alert to user

Following document help to copy latest files from local drive and  paste on the network drive.

this document also delete files older than 10 days from the network drive where we are copying the backup files.

to copy backup files we will create following files on the  c:\users\sarzaidi\documents\ path .

we would also need to create a folder named log under  c:\users\sarzaidi\documents\ 

1. backup.bat.

this file will copy backup from local drive to mapped network drive.

drive will be mapped at runtime.

2. sendemail.bat

this file will use powershell and emailcommand.ps1 file to send email .

3. emailcommand.ps1

this file contains command to send email.

in this file we can specify sender ,receiver ,email subject and body we can also use this file to attach file to email.

4.main.bat

this file calls backup.bat and sendemail.bat to perform backup and send email.

we can execute this file from command prompt or we can schedule this file to execute the task whenever we need. 

 1. backup.bat

set backuplog=backup_db_%date:~10%%date:~4,2%%date:~7,2%.log

echo set batchscriptdir="c:\program files\postgresql\backups\"

echo set backupsourcedir="c:\program files\postgresql\backups\"

echo set backupdestinationdir=\\ccps-sql-01.ad.ccps.net\ccps_backups\db_backups\kp_dev

pg_dump  -u postgres   -p 5432 -f c -b -v -d eor4 > "c:\program files\postgresql\backups"\%backupname%

net use x: \\ccps-sql-01.ad.ccps.net\ccps_backups\db_backups\kp_dev

x:

echo " xcopy  /y /f /i /c:  %backupsourcedir%*.* x:\"

echo backup started from local:%backupsourcedir% to azure . >> %batchscriptdir%logs\%backuplog%

xcopy  /y /f /i /d:%date:~4,2%-%date:~7,2%-%date:~10% %backupsourcedir%*.* x: >> %batchscriptdir%logs\%backuplog%

echo backup completed at \\ccps-sql-01.ad.ccps.net\ccps_backups\db_backups\kp_dev >> %batchscriptdir%logs\%backuplog%

x:

echo delete older than 10 day  started. >> %batchscriptdir%logs\%backuplog%

forfiles /m *.bak /d -10 /c "cmd /c echo @path "

echo delete older than 10 day completed >> %batchscriptdir%logs\%backuplog%

c:

net use x: /delete /y

echo done 

2. sendemail.bat

set powershellscriptpath=%batchscriptdir%emailcommand.ps1

powershell -noprofile -executionpolicy bypass -command "& '%powershellscriptpath%'";

3. emailcommand.ps1

$from_ = "sarzaidi@mydomain.com"

$to_ = "sarzaidi@mydomain.com","arzaidi@lmkr.com"

$subject_ = "doa dev 168 backup"

$body_ = "backup completed logs at avaliable at \\infoportal-dev\logs "

$smtpserver_  =  "outlook.mydomain.com"

send-mailmessage -from $from_ -to $to_ -subject $subject_ -body $body_ –smtpserver $smtpserver_

4.main.bat

set batchscriptdir="c:\program files\postgresql\backups\"

set logdir=eor_backup_%date:~10%%date:~4,2%%date:~7,2%

set backuplog=backup_%date:~10%%date:~4,2%%date:~7,2%.log

set emaillog=sendemail_%date:~10%%date:~4,2%%date:~7,2%.log

set backupsourcedir="c:\program files\postgresql\backups\"

set backupdestinationdir=\\ccps-sql-01.ad.ccps.net\ccps_backups\db_backups\kp_dev

set backupname=vo8_%date:~10%%date:~4,2%%date:~7,2%.backup

c:

cd %batchscriptdir%logs

md %logdir%

move /y *.log %logdir%

 c:

cd %batchscriptdir%

echo backup.bat started. >> %batchscriptdir%logs\%backuplog%

call backup.bat >> %batchscriptdir%logs\%backuplog%

echo backup.bat ended. >> %batchscriptdir%logs\%backuplog%

c:

cd %batchscriptdir%

echo sendemail started. >> %batchscriptdir%logs\%emaillog%

call sendemail.bat >> %batchscriptdir%logs\%emaillog%

echo sendemail ended. >> %batchscriptdir%logs\%emaillog%

rem cd %batchscriptdir%logs

rem md unittestlogs_%date:~10%%date:~4,2%%date:~7,2%

rem mv %batchscriptdir%logs\*.log unittestlogs_%date:~10%%date:~4,2%%date:~7,2%

forfiles /p "%batchscriptdir%logs" /s /d -6 /c "cmd /c if @isdir == true rd /s /q @path"

cd %batchscriptdir%

echo done 

Recover Database when system.dbf is corrupted

SQL*Plus: Release 11.2.0.2.0 Production on Thu Mar 30 17:31:08 2017

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1388352 bytes
Variable Size             620757184 bytes
Database Buffers          444596224 bytes
Redo Buffers                4591616 bytes
Database mounted.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'D:\ORACLE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF'

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 4987181 generated at 03/19/2017 12:47:52 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ARCHIVELOG\2017_03_30\O1_MF_1_173_%U_.ARC
ORA-00280: change 4987181 for thread 1 is in sequence #173


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-10879: error signaled in parallel recovery slave
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'D:\ORACLE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF'



SQL> create pfile='D:\oracle\app\oracle\product\11.2.0\server\database\initXE.ora' from spfile;

File created.


Open pfile created at last setp at the follwoing path D:\oracle\app\oracle\product\11.2.0\server\database\initXE.ora

and at end of file add follwoing parameter.

_allow_resetlogs_corruption=true

and save the file.

Now shutdown database and start with this file as below.

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup pfile='D:\oracle\app\oracle\product\11.2.0\server\database\initXE.ora';
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1388352 bytes
Variable Size             620757184 bytes
Database Buffers          444596224 bytes
Redo Buffers                4591616 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.

SQL> create spfile from pfile;

File created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1388352 bytes
Variable Size             620757184 bytes
Database Buffers          444596224 bytes
Redo Buffers                4591616 bytes
Database mounted.
Database opened.
SQL>

Use Sql Developer to load formatted excel report data into Oracle

I this post we will learn to load formatted report data from excel or csv files into ORACLE database.

sample data in excel format.

Jan 2002- Dec 2002
St.Name	N	E	Jan_2002	Feb_2002	Mar_2002	Apr_2002	May_2002	June_2002	July_2002	Aug_2002	Sep_2002	Oct_2002	Nov_2002	Dec_2002
Bhakkar	31.61667	71.06667	0	0	0	0	0	0	0	0	0	0	0	0
Bahawal-Nagar	29.88099	73.35250	48	44	30	19	16	28	34	40	40	29	40	48
Bahawal-Pur	30.53333	71.78333	45	41	33	18	20	29	36	40	48	41	44	46
CHAKWAL	32.91667	72.85000	0	0	0	0	0	0	0	0	0	0	0	0
Jan 2003- Dec 2003
St.Name	N	E	Jan_2003	Feb_2003	Mar_2003	Apr_2003	May_2003	June_2003	July_2003	Aug_2003	Sep_2003	Oct_2003	Nov_2003	Dec_2003
Bhakkar	31.61667	71.06667	0	0	0	0	0	0	0	0	0	0	0	0
Bahawal-Nagar	29.88099	73.35250	58	51	37	20	14	31	55	53	49	28	29	46
Bahawal-Pur	30.53333	71.78333	53	52	35	19	17	31	53	55	51	35	44	45
CHAKWAL	32.91667	72.85000	0	0	0	0	0	0	0	0	0	0	0	0
D-G-KHAN	30.05000	70.63333	55	44	38	22	15	25	53	54	53	33	30	40
Jan 2004- Dec 2004
St.Name	N	E	Jan_2004	Feb_2004	Mar_2004	Apr_2004	May_2004	June_2004	July_2004	Aug_2004	Sep_2004	Oct_2004	Nov_2004	Dec_2004
Bhakkar	31.61667	71.06667	0	0	0	0	0	0	0	0	0	0	0	0
Bahawal-Nagar	29.88099	73.35250	63	40	26	19	19	32	36	49	39	37	35	45
Bahawal-Pur	30.53333	71.78333	59	46	32	21	22	30	38	56	48	45	43	48
CHAKWAL	32.91667	72.85000	0	0	0	0	0	0	0	0	0	0	0	0
D-G-KHAN	30.05000	70.63333	55	44	35	21	21	34	46	54	47	40	41	42

To load above data execute following scripts on your database with appropriate user and follow the procedure.

I have used scott user for this demo.


Following scrip will create table in which data would be loaded.


create table climate_data
(
city_Name varchar2(100) ,
North varchar2(100) ,
East varchar2(100) ,
Jan varchar2(100) ,
Feb varchar2(100) ,
Mar varchar2(100) ,
Apr varchar2(100) ,
May varchar2(100) ,
Jun varchar2(100) ,
Jul varchar2(100) ,
Aug varchar2(100) ,
Sep varchar2(100) ,
Oct varchar2(100) ,
Nov varchar2(100) ,
Dec varchar2(100),
year_name varchar2(100) )
/


This package will help to hold data temporarily.


CREATE OR REPLACE PACKAGE uldemo7_climate_data AS
    year_name  varchar2(100);
END uldemo7_climate_data;
/


This trigger will convert the formatted data in to our required column in my case I want to populate year_name column.


CREATE OR REPLACE TRIGGER uldemo7_climate_data_insert
  BEFORE INSERT ON climate_data
  FOR EACH ROW

  BEGIN
  IF substr(:new.Dec,1,3) ='Dec' THEN
     uldemo7_climate_data.year_name := substr(:new.Dec,5,4);   -- save value for later use
  ELSE
     :new.year_name := uldemo7_climate_data.year_name;   -- use last valid value
  END IF;

  END;
/


Following triggers is used to  delete extra/garbage  data form the table.


create or replace TRIGGER uldemo7_climate_data_aft_ins
  after INSERT ON climate_data

  BEGIN
 delete from climate_data where year_name is null or city_name like 'Jan%' or north is null;

  END;
  /


After executing above scripts we are ready to used sql developer or sqlloader to load data in our required format in the climate_data table.


To load data using sql developer start the sql developer with the your user in my case it is scott.

open the table tree in the object explorer and right click the climate_data table and click import.

at first screen click browse and provide the excel or csv file.

header checkbox must be unchecked click next

select import method to insert and click next
again click next on the choose column page
in the column definition page select match by position option and click next.
on finish page click finish.

A dialog box will appear to show the status if import .

click ok


now you can query climate_data table to view the loaded data .