Backing Up the Oracle Reporting Database
As part of your initial installation, you were asked to perform an initial cold backup of your Reporting Database.
For a Cold Backup—stop Oracle and take a backup of the Oracle reporting database by performing a file system backup of /data0?/* for Linux or \oradata for Windows.
Perform a Cold Backup of the Database
Prior to deploying the Portal for operational use and periodically after installation (at least once a month is recommended), it is recommended that you perform a cold backup of the Oracle database. In addition to monthly cold backups, it is also recommended that you take a cold backup after a significant software upgrade to re-capture the updated database schema.
This off-line, cold backup means that you will physically copy or back up the Oracle database data files to another location. This cold backup will simplify the restore process, in the event of unanticipated data loss. With a cold backup, you simply have to restore the files and then import the most recent database export.
To perform a cold backup (Linux)
1. Shutdown the Oracle service:
/opt/aptare/bin/oracle stop
2. Using your organization’s file system backup software, back up all the data files from:
$ORACLE_HOME\dbs\initscdb.ora
/data0?/*
To perform a cold backup (Windows)
1. Shut down the Oracle service: OracleServicescdb in the Windows Administrative Tools, Component Services window.
2. Backup the following files:
$ORACLE_HOME\dbs\iniscdb.ora
C:\oradata
Note: During installation, you could choose a different drive for the oradata install, so verify its location before backing up the data.
Reporting Database Export Backups
This method is the preferred method of ensuring that your database is backed up on a regular basis. This is a two-step process:
Customizing the Export Script
A template is provided for automating the database backup process. It must be copied and then customized for your installation.
1. Locate the script template:
Linux: opt/aptare/database/tools/export_database_template.sh
Windows: C:\opt\oracle\database\tools\export_database_template.bat
2. Before customizing the backup script, export_database_template.sh|bat, first copy it and then rename it to something like: export_database.sh|bat
This preserves the template file, which gets overwritten during a Portal upgrade, and provides a file for customization and execution: export_database.sh|bat
3. Refer to the relevant steps to customize the script:
4. Refer to the relevant steps to schedule the task:
Important! It is critical that the database export file is included in your file system backup policy so that, in the event of a disaster, you can restore and then import the export file.
Linux: Customize the Script
In the export_database.sh script:
• Replace customername with your relevant company name.
• Replace /ora_exports with a directory that is included in your file system backup policy.
#!/bin/sh
# The following script is the APTARE IT Analytics database export script for customername
NOTIFY_LIST="support@customername.com"
FILENAME=customername_scdb.exp_`date +%m%d%g`
LOGFILENAME=ccustomername_scdb_`date +%m%d%g`.log
ORACLE_HOME=/opt/aptare/oracle
ORACLE_SID=scdb
export ORACLE_HOME ORACLE_SID
# Set the export directory
PATHNAME=/ora_exports
# Export the database
/opt/aptare/oracle/bin/exp parfile=/opt/aptare/database/tools/export_scdb.par file=$PATHNAME/$FILENAME log=$PATHNAME/$LOGFILENAME
if [ $? -eq 0 ]; then
if [ -f $PATHNAME/$FILENAME]; then
if [ -f "$PATHNAME/$FILENAME.gz" ]; then
rm -f "$PATHNAME/$FILENAME.gz"
fi
echo "Done with export"
fi
else
echo `date` "Problem with export on customername"
echo "Problem with export on customername" | mail -s "customername export problem" $NOTIFY_LIST
exit 1
fi
gzip -f "$PATHNAME/$FILENAME"
exit 0
Windows: Customize the Script
In the export_database.bat script:
• Replace customername with your relevant company name.
• Replace C:\tmp with a directory included in your file system backup policy.
• Customize the mailto line to be compatible with your environment.
• If you choose to use a tool other than 7-Zip, change the software used to zip the file.
@echo off
REM The following script is the APTARE IT Analytics database export script for customername
SET NOTIFY_LIST=support@customername.com
FOR /F "TOKENS=1* DELIMS= " %%A IN ('DATE/T') DO SET CDATE=%%B
FOR /F "TOKENS=1,2 eol=/ DELIMS=/ " %%A IN ('DATE/T') DO SET mm=%%B
FOR /F "TOKENS=1,2 DELIMS=/ eol=/" %%A IN ('echo %CDATE%') DO SET dd=%%B
FOR /F "TOKENS=2,3 DELIMS=/ " %%A IN ('echo %CDATE%') DO SET yyyy=%%B
SET date=%mm%%dd%%yyyy%
set date=%date: =%
REM Set the export directory
SET PATHNAME=C:\tmp
SET FILENAME=aptare_scdb.exp_%date%
SET LOGFILENAME=customername_scdb_`date +%m%d%g`.log
SET ZIP_FILENAME=%PATHNAME%\%FILENAME%.zip
REM Export the database
c:\opt\oracle\bin\exp parfile=c:\opt\oracle\database\tools\export_scdb.par file=%PATHNAME%\%FILENAME% log=$PATHNAME\$LOGFILENAME
ECHO %ZIP_FILENAME%
IF ERRORLEVEL 0 (
IF exist %PATHNAME%\%FILENAME% (
IF exist %ZIP_FILENAME% (
DEL %ZIP_FILENAME%
echo Done with export
)
)
)
IF NOT ERRORLEVEL 0 (
echo %date% "Problem with export on customername"
START mailto:%NOTIFY_LIST%?subject=20customername%%20export%%20problemˆ&body=Problem%%20with%%20export%%20on%%20customername
)
"c:\Program Files\7-Zip\7z.exe" a %ZIP_FILENAME% %PATHNAME%\%FILENAME%
IF exist %ZIP_FILENAME% (
IF exist %PATHNAME%\%FILENAME% (
DEL %PATHNAME%\%FILENAME%
)
)
Scheduling the Export Job (Linux)
1. Switch to user aptare.
su - aptare
2. Edit or create the crontab file.
crontab -e
3. Using the following diagram, specify the database export schedule.
Example:
04 15 * * * /opt/aptare/utils/export_database.sh 2>&1 >>/tmp/database_export.log
4. Verify the newly scheduled job:
crontab -l
Scheduling the Export Job (Windows)
1. Start > Programs > Accessories > System Tools > Task Scheduler
2. Create a new task in the Task Scheduler by specifying the schedule and the script to be run:
c:\opt\aptare\utils\export_database.bat
Back Up the Database On Demand
This method is useful for cases where an immediate backup/export is required; for example, when Customer Support requests a copy of your database for troubleshooting.
To back up the database tables, APTARE IT Analytics provides an Oracle utility,
exp. This utility exports the user
portal, which contains the database tables. See
Exporting the Oracle Database to create a full export file of all database objects.
For optimum performance, use this utility rather than your favorite backup solution’s backup utility (for example,
rman) because most backup solutions require archive logging. APTARE does not enable or expose this setting because archive logging can have a significant, negative impact on performance.
You will import this export in the event that you need to:
• Retrieve a data table that’s been corrupted or accidently deleted. Simply drop the portal user then import the export. See
Importing the Oracle Database.
Exporting the Oracle Database
To create a full export file of all objects in the Portal database:
1. Log on to the database server.
• In a Linux environment, log on to the database server as user aptare.
• In a Windows environment, log on to the database server as a user who is a member of the ORA_DBA group.
2. Edit the export_scdb.par file to specify a value for path_to_backup_directory. The export_scdb.par file contains the following parameters:
USERID=system/aptaresoftware
BUFFER=128000
GRANTS=Y
ROWS=Y
INDEXES=Y
CONSISTENT=Y
COMPRESS=Y
STATISTICS=NONE
OBJECT_CONSISTENT=Y
FULL=Y
FILE=/path_to_backup_directory/scdb.exp
3. Run the following commands:
• On Linux (as user, aptare):
/opt/aptare/oracle/bin/exp parfile=/opt/aptare/database/tools/export_scdb.par
• On Windows:
C:\opt\oracle\bin\exp parfile= C:\opt\oracle\database\tools\export_scdb.par
