DCJS’s upgrade to the Spectrum Justice System’s (SJS) backup utility for Oracle 11 G Express users only. By Jeff Ellman NYSITS DCJS SJS ITS2P

Disclaimer: DCJS is not responsible for an agencies backup and recovery disaster plan or maintenance of their computer systems. This backup utility update is offered as a tool to assist SJS users in securing viable backup. You are free to enhance it as needed at your own risk.

Release notes: Implement time control for preventing file corruption Implement file comparison for preventing file corruption Implement archiving Implement backup and archiving retention Implement new naming convention for backup directories: YYYYMMDDHHMMSS The utilities root directory and the backup/archive directories will now contain two new log files

o sjs_coldbackup11xe.log Logging of events

o compare.log Logging of file comparisons of production database files to backup database files

Steps to recover from backup.

Outline1. Deploy2. Configure

a. Location Options3. Backup and recovery

1. Deploy Download the self-extracting zip file and extract to the root of the SJS installation drive; change

the drive letter in the “Unzip to folder” as needed to reflect your SJS installation.

http://www.criminaljustice.ny.gov/ojis/sjs/sjs70.htm SJS_ColdBackup11xe.exe Click “Unzip”:

1

Enter password obtained from DCJS and click “OK”:

Click “OK”:

In this example the following directory structure would have been extracted to your C drive:

2. Configure To configure you need to edit the file: \ SJS_ColdBackup11xe \sjs_coldbackup11xe.bat

o To edit right click on select “Edit”.

Perform the following edits: Set the Backup retention value to specify how many days to keep a backup:

o Edit the value assigned on line 7: set retain_backup=7

Set the Archive retention value to specify how many days to keep an archived backup:o Edit the value assigned on line 8:

set retain_archive=14 In this example, this value will keep 7 days of backups in the archive

location for seven days will have passed before being moved from the backup directory.

Set the correct drive letter for your SJS’s Oracle installation:o Edit the value on line 9:

set sjs_drive=c:\ Next, choose either a local, mapped, or network location for your backup and archiving locations.

There are two options:o Backup Location Option 1: Local Drive or Mapped Network Driveo Backup Location Option 2: UNC paths

2

Location Option 1: Local Drive or Mapped Network Driveo If using the default settings for the local drive option, create at the following folder structure

as depicted on 17-18 Line 17: set backup_location=%util_folder%backups

Create folder “backups” at: C:\SJS_ColdBackup11xe\backups Line 18: archive_location=%util_folder%archives

Create folder “archives” at: C:\SJS_ColdBackup11xe\archiveso If you using a mapped drive, edit line’s 17 and 18 to reflect the examples in lines 20-21; or

comment lines 17 and 18 and uncomment lines 20-21 and update to reflect your mapped drive (a comment is the command “rem” (without quotes)- add and remove as needed. Ex from above; what’s highlighted is what you need to edit to reflect your SJS:

Line 17: rem set backup_location=%util_folder%backups Line 18: rem set archive_location=%util_folder%archives Line 20: set backup_location=Y:\test\backups Line 21: set archive_location=Y:\test\archives

o Go to the steps for “Scheduled Task” Location Option 2: UNC paths

o Comment out lines 17 and 18 under “Option 1” by adding “rem”: Line 17: rem set backup_location=%util_folder%backups Line 18: rem set archive_location=%util_folder%archives

o Uncomment lines 26-27 by removing “rem” and editing to reflect your UNC Path as highlighted below:

Line 26: set backup_location=\\pvwap22\sjsdata\test\backups Line 27: set archive_location=\\pvwap22\sjsdata\test\archives

o Add the comment “rem” to lines 65 through 68, used with Option 1, exampled below: Line 65: rem ( Line 66: rem %util_tool%forfiles /p %backup_location% /d -%retain_backup% /c

"cmd /c move @file %archive_location%" Line 67: rem %util_tool%forfiles /p %archive_location% /m * /d -%retain_archive

% /c "cmd /c rmdir /s/q @file" Line 68: rem ) >> sjs_coldbackup11xe.log 2>&1

o Remove the comment “rem” from linkes 73-80, as show below: Line 73: ( Line 74: pushd %backup_location% Line 75: %util_tool%forfiles /d -%retain_backup% /c "cmd /c move @file

%archive_location%" Line 76: popd Line 77: pushd %archive_location% Line 78: %util_tool%forfiles /m * /d -%retain_archive% /c "cmd /c rmdir /s/q @file" Line 79: popd Line 80: ) >> sjs_coldbackup11xe.log 2>&1

o Proceed to next step, #3 Schedule Task

3

3. Backup and RecoveryDisclaimer: DCJS is not responsible for an agencies backup and recovery or maintenance of their computer systems. This backup utility update is offered as a tool to assist SJS users in this process. You are free to enhance it as needed at your own risk

Schedule Task Recover From Backup

Schedule Task: Go Start/Administrative Tools/Task Scheduler

Click Create Basic Task

Name must be: sjsbackup

4

Click Next; select “Daily”; click Next

Schedule the start time; click “Next”:

5

Select “Start a program”; click “Next”:

6

Browse to and select the batch file C:\SJS_ColdBackup11xe\sjs_coldbackup11xe.bat

7

Add a backslash \ in the Start input box; click “Next”:

Check “Open the Properties dialog box for this task when I click Finish”; click “Finish”:

Check “Run with highest privileges”. Select “Configure for” “Windows 7, Winders Server 2008 R2. Click “Ok”: Run the task, select from the list, right click and select “Run”:

8

Recover from Backup Stop the following services

o Apache Tomcato OracleServiceXE

Review of database files Backup existing database files Deploy backup

Stop the following services before backup recovery: Apache Tomcat OracleServicesXE

Review of database files 11 G Express database directories: \oraclexe\app\oracle\fast_recovery_area\XE\ONLINELOG

o 2 LOG files \oraclexe\app\oracle\oradata\XE

o 6 DBF files \oraclexe\app\oracle\product\11.2.0\server\database

o 5 Files: 2 ORA, 1 DAT, 1 LOG, 1 EXE

9

11 G Express database files directory path: \oraclexe\app\oracle\fast_recovery_area\XE\ONLINELOG\O1_MF_1_B3BJB33C_.LOG * \oraclexe\app\oracle\fast_recovery_area\XE\ONLINELOG\O1_MF_2_B3BJB4NO_.LOG * \oraclexe\app\oracle\oradata\XE\CONTROL.DBF \oraclexe\app\oracle\oradata\XE\SYSAUX.DBF \oraclexe\app\oracle\oradata\XE\SYSTEM.DBF \oraclexe\app\oracle\oradata\XE\TEMP.DBF \oraclexe\app\oracle\oradata\XE\UNDOTBS1.DBF \oraclexe\app\oracle\oradata\XE\USERS.DBF \oraclexe\app\oracle\product\11.2.0\server\database\hc_xe.dat \oraclexe\app\oracle\product\11.2.0\server\database\initXE.ora \oraclexe\app\oracle\product\11.2.0\server\database\oradba.exe \oraclexe\app\oracle\product\11.2.0\server\database\oradim.log \oraclexe\app\oracle\product\11.2.0\server\database\pwdxe.ora

Backup utilities database file directory path in either designated “backups” or “archives” locations \YYYYMMDDHHMMSS\oraclexe\app\oracle\fast_recovery_area\XE\ONLINELOG\

O1_MF_1_B3BJB33C_.LOG * \YYYYMMDDHHMMSS\oraclexe\app\oracle\fast_recovery_area\XE\ONLINELOG\

O1_MF_2_B3BJB4NO_.LOG * \YYYYMMDDHHMMSS\oraclexe\app\oracle\oradata\XE\CONTROL.DBF \YYYYMMDDHHMMSS\oraclexe\app\oracle\oradata\XE\SYSAUX.DBF \YYYYMMDDHHMMSS\oraclexe\app\oracle\oradata\XE\SYSTEM.DBF \YYYYMMDDHHMMSS\oraclexe\app\oracle\oradata\XE\TEMP.DBF \YYYYMMDDHHMMSS\oraclexe\app\oracle\oradata\XE\UNDOTBS1.DBF \YYYYMMDDHHMMSS\oraclexe\app\oracle\oradata\XE\USERS.DBF \YYYYMMDDHHMMSS\oraclexe\app\oracle\product\11.2.0\server\database\hc_xe.dat \YYYYMMDDHHMMSS\oraclexe\app\oracle\product\11.2.0\server\database\initXE.ora \YYYYMMDDHHMMSS\oraclexe\app\oracle\product\11.2.0\server\database\oradba.exe \YYYYMMDDHHMMSS\oraclexe\app\oracle\product\11.2.0\server\database\oradim.log \YYYYMMDDHHMMSS\oraclexe\app\oracle\product\11.2.0\server\database\pwdxe.ora

* File name will verify by installation.

Backup existing database files

To recover from backup- do not delete the existing database files; create a subdirectory and move them for temporary storage. OracleServiceXE MUST BE STOPPED.

\oraclexe\app\oracle\fast_recovery_area\XE\ONLINELOG\backup \oraclexe\app\oracle\oradata\XE\backup \oraclexe\app\oracle\product\11.2.0\server\database\backup

Move: \oraclexe\app\oracle\fast_recovery_area\XE\ONLINELOG\backup \O1_MF_1_B3BJB33C_.LOG

10

\oraclexe\app\oracle\fast_recovery_area\XE\ONLINELOG\backup \O1_MF_2_B3BJB4NO_.LOGTo: \oraclexe\app\oracle\fast_recovery_area\XE\ONLINELOG\O1_MF_1_B3BJB33C_.LOG \oraclexe\app\oracle\fast_recovery_area\XE\ONLINELOG\O1_MF_2_B3BJB4NO_.LOG

Move: \oraclexe\app\oracle\oradata\XE\CONTROL.DBF \oraclexe\app\oracle\oradata\XE\SYSAUX.DBF \oraclexe\app\oracle\oradata\XE\SYSTEM.DBF \oraclexe\app\oracle\oradata\XE\TEMP.DBF \oraclexe\app\oracle\oradata\XE\UNDOTBS1.DBF \oraclexe\app\oracle\oradata\XE\USERS.DBFTo: \oraclexe\app\oracle\oradata\XE\backup \CONTROL.DBF \oraclexe\app\oracle\oradata\XE\backup \SYSAUX.DBF \oraclexe\app\oracle\oradata\XE\backup \SYSTEM.DBF \oraclexe\app\oracle\oradata\XE\backup \TEMP.DBF \oraclexe\app\oracle\oradata\XE\backup \UNDOTBS1.DBF \oraclexe\app\oracle\oradata\XE\backup \USERS.DBF

Move: \oraclexe\app\oracle\product\11.2.0\server\database\hc_xe.dat \oraclexe\app\oracle\product\11.2.0\server\database\initXE.ora \oraclexe\app\oracle\product\11.2.0\server\database\oradba.exe \oraclexe\app\oracle\product\11.2.0\server\database\oradim.log \oraclexe\app\oracle\product\11.2.0\server\database\pwdxe.oraTo: \oraclexe\app\oracle\product\11.2.0\server\database\backup\hc_xe.dat \oraclexe\app\oracle\product\11.2.0\server\database\backup\initXE.ora \oraclexe\app\oracle\product\11.2.0\server\database\backup\oradba.exe \oraclexe\app\oracle\product\11.2.0\server\database\backup\oradim.log \oraclexe\app\oracle\product\11.2.0\server\database\backup\pwdxe.ora

To recover from backupMove: \YYYYMMDDHHMMSS\oraclexe\app\oracle\fast_recovery_area\XE\ONLINELOG\

O1_MF_1_B3BJB33C_.LOG * \YYYYMMDDHHMMSS\oraclexe\app\oracle\fast_recovery_area\XE\ONLINELOG\

O1_MF_2_B3BJB4NO_.LOG *To: \oraclexe\app\oracle\fast_recovery_area\XE\ONLINELOG\backup \O1_MF_1_B3BJB33C_.LOG \oraclexe\app\oracle\fast_recovery_area\XE\ONLINELOG\backup \O1_MF_2_B3BJB4NO_.LOG

Move: \YYYYMMDDHHMMSS\oraclexe\app\oracle\oradata\XE\CONTROL.DBF \YYYYMMDDHHMMSS\oraclexe\app\oracle\oradata\XE\SYSAUX.DBF

11

\YYYYMMDDHHMMSS\oraclexe\app\oracle\oradata\XE\SYSTEM.DBF \YYYYMMDDHHMMSS\oraclexe\app\oracle\oradata\XE\TEMP.DBF \YYYYMMDDHHMMSS\oraclexe\app\oracle\oradata\XE\UNDOTBS1.DBF \YYYYMMDDHHMMSS\oraclexe\app\oracle\oradata\XE\USERS.DBFTo: \oraclexe\app\oracle\oradata\XE\CONTROL.DBF \oraclexe\app\oracle\oradata\XE\SYSAUX.DBF \oraclexe\app\oracle\oradata\XE\SYSTEM.DBF \oraclexe\app\oracle\oradata\XE\TEMP.DBF \oraclexe\app\oracle\oradata\XE\UNDOTBS1.DBF \oraclexe\app\oracle\oradata\XE\USERS.DBF

Move: \YYYYMMDDHHMMSS\oraclexe\app\oracle\product\11.2.0\server\database\hc_xe.dat \YYYYMMDDHHMMSS\oraclexe\app\oracle\product\11.2.0\server\database\initXE.ora \YYYYMMDDHHMMSS\oraclexe\app\oracle\product\11.2.0\server\database\oradba.exe \YYYYMMDDHHMMSS\oraclexe\app\oracle\product\11.2.0\server\database\oradim.log \YYYYMMDDHHMMSS\oraclexe\app\oracle\product\11.2.0\server\database\pwdxe.oraTo: \oraclexe\app\oracle\product\11.2.0\server\database\hc_xe.dat \oraclexe\app\oracle\product\11.2.0\server\database\initXE.ora \oraclexe\app\oracle\product\11.2.0\server\database\oradba.exe \oraclexe\app\oracle\product\11.2.0\server\database\oradim.log \oraclexe\app\oracle\product\11.2.0\server\database\pwdxe.ora

Restart Services in the following order: OracleServiceXE Apache Tomcat

3 rd option for keeping the backup utility similar to the old but to allow for backup retention

Here's a third option, keep just a backup folder/ retention day only.

To start: Comment line 8 by adding a "rem" at the start:

o Line 8: rem set retain_archive=14

12

Comment line 18 by adding a "rem" at the start:o Line 18: rem set archive_location=%util_folder%archives

For Option 1 Local or mapped drives do the following: Comment line 66 by adding a "rem" at the start:

o Line 66: rem %util_tool%forfiles /p %backup_location% /d -%retain_backup% /c "cmd /c move @file %archive_location%"

Change line 67 from:o Line 67: %util_tool%forfiles /p %archive_location% /m * /d -%retain_archive% /c

"cmd /c rmdir /s/q @file" Change line 67 to:

o Line 67: %util_tool%forfiles /p %backup_location% /m * /d -%retain_backup% /c "cmd /c rmdir /s/q @file"

For Option 2 UNC Paths Be sure you've commented the Location Option 1 path assignments between lines 17-21.

o Comment out line 27 by adding a rem to the start: Line 27: rem set archive_location=\\pvwap22\sjsdata\test\archives

Comment lines 65-68 by adding a "rem" at the start:o Line 65: rem (o Line 66: rem %util_tool%forfiles /p %backup_location% /d -%retain_backup% /c "cmd /c

move @file %archive_location%"o Line 67: rem %util_tool%forfiles /p %archive_location% /m * /d -%retain_archive% /c

"cmd /c rmdir /s/q @file"o Line 68: rem ) >> sjs_coldbackup11xe.log 2>&1

Uncomment lines 73-76, and line 80. Edit line 75 as depicted in the before and after shots below:o Change from:

Line 73: rem ( Line 74: rem pushd %backup_location% Line 75: rem %util_tool%forfiles /d -%retain_backup% /c "cmd /c move @file

%archive_location%" Line 76: rem popd Line 77: rem pushd %archive_location% Line 78: rem %util_tool%forfiles /m * /d -%retain_archive% /c "cmd /c rmdir /s/q @file" Line 79: rem popd Line 80: rem ) >> sjs_coldbackup11xe.log 2>&

o Change to: Line 73: ( Line 74: pushd %backup_location% Line 75: %util_tool%forfiles /m /d -%retain_backup% /c "cmd /c rmdir /s/q @file%" Line 76: Popd Line 77: rem pushd %archive_location% Line 78: rem %util_tool%forfiles /m * /d -%retain_archive% /c "cmd /c rmdir /s/q

@file" Line 79: rem popd Line 80: ) >> sjs_coldbackup11xe.log 2>&

13