Wednesday 8 April 2020

Script to create users in sql server after restoring database

copy below Script and  execute on the newly created database this will create all user in your instance to access data from new database.

SET NOCOUNT ON
DECLARE @loop INT
DECLARE @USER sysname

Declare @Orphaned TABLE  (UserName sysname, UserSID VARBINARY(85),IDENT INT IDENTITY(1,1))

INSERT INTO @Orphaned
EXEC SP_CHANGE_USERS_LOGIN 'report';

IF(SELECT COUNT(*) FROM @Orphaned) > 0
BEGIN
SET @loop = 1
WHILE @loop <= (SELECT MAX(IDENT) FROM @Orphaned)
  BEGIN
    SET @USER = (SELECT UserName FROM @Orphaned WHERE IDENT = @loop)
    IF(SELECT COUNT(*) FROM sys.server_principals WHERE [Name] = @USER) <= 0
     BEGIN
        EXEC SP_ADDLOGIN @USER,'Landmark1'  --- Change password what you need
             Print @user
     END
   
    EXEC SP_CHANGE_USERS_LOGIN 'update_one',@USER,@USER
    SET @loop = @loop + 1
  END
END
SET NOCOUNT OFF

Tuesday 3 July 2018

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;

Tuesday 16 January 2018

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;

Wednesday 10 May 2017

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






Monday 10 April 2017

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


Sunday 9 April 2017

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 




Thursday 30 March 2017

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>