Different Backup and Recovery Scenarios of an ArcSight Database Instance for ESM v3.x and v4.x

Summary:

This technical note provides a structured approach to managing and recovering from media failures in Oracle databases used by ArcSight Confidential. Below is a summary of the key steps involved in handling media failures specifically related to TEMP and ARC_TEMP tablespaces, as well as how to create scripts for offline operations. ### Handling Media Failures in TEMP and ARC_TEMP Tablespaces 1. **Identify Affected Files**: Note down the files belonging to TEMP or ARC_TEMP tablespaces that have media failure or are missing. 2. **Startup Database in Mount Mode**: Logged in as the “sys” user, start the database in mount mode using `arcdbutil sql`. Enter commands like: ```sql SQL> startup mount ``` 3. **Offline Affected Files**: Use `arcdbutil alter database tempfile` and `arcdbutil alter database arc_tempfile` to take the offline, specifying the affected files’ paths. 4. **Recreate Tablespaces**: Drop and recreate the TEMP and ARC_TEMP tablespaces as per the instructions in the technical note, adjusting file locations and sizes as needed. ### Creating Scripts for Offline Operations 1. **Create Datafile Offline Script**: Save a script named `CreateDatafileOffline.sql` that contains SQL statements to take datafiles offline based on tablespace names. Use the command: ```sql @CreateDatafileOffline arc_event_data ``` 2. **Run the Script**: Execute the created script file to perform the offline operations for specified tablespaces. ### Summary of Key Steps - **Shutdown ArcSight Manager** and save the provided `ResetPartitions.sql` script. - Log in as the arcsight user and run the script using `arcdbutil sql`. - Ensure that the script completes successfully without errors. - Identify affected files for TEMP or ARC_TEMP tablespaces. - Startup the database in mount mode and take offline specified datafiles. - Drop and recreate TEMP and ARC_TEMP tablespaces, adjusting file locations and sizes as needed. - Create a `CreateDatafileOffline.sql` script to manage offline operations for specified tablespaces. This structured approach helps maintain data integrity and operational continuity in case of media failures related to TEMP and ARC_TEMP tablespaces in the ArcSight Confidential Oracle database setup.

Details:

Oracle Backup and Recovery provides comprehensive facilities for performing database backup and recovery operations. If an experienced DBA is not available, ArcSight recommends implementing backup/recovery procedures using ArcSight Professional Services. This document addresses different backup and recovery scenarios for an ArcSight Database instance, including full database backup and recovery procedures. The ArcSight Confidential 1Oracle Backup and Recovery notes that Oracle Cold Backup involves bringing down the Oracle Database and backing up all its files until all the database files are backed up or copied, while Oracle Hot Backup is an image backup of datafiles taken when the Database is up and running, requiring a recovery from online logs and archive logs after being mounted. RMAN (Recovery Manager) is an Oracle utility for backup, restore, and recovery of database files. The document includes scripts and procedures such as recreating TEMP and ARC_TEMP tablespaces, taking tablespace datafiles offline, resetting the partition catalog, creating initial partitions, backing up the controlfile to trace, and viewing the DDL of ArcSight objects and tablespaces. This document outlines a comprehensive backup and recovery procedure for Oracle databases, focusing on hot backups of all tablespaces excluding TEMP and ARC_TEMP, control file backup, archiving redo logs, and specific actions to follow in case of media failures. Key points include: 1. **Hot Backup**: Perform end backup immediately after copying datafiles from the backed-up tablespace. After completing hot backups, execute SQL commands to switch logfiles and archive all logs. 2. **Archiving Redo Logs**: Ensure that online redo logs are available by archiving them periodically. This can be done in alignment with hot backup cycles using SQL commands: `alter system switch logfile;` and `alter system archive log all;`. 3. **Backup Control File**: Back up the control file to trace for potential media failures, which can be used for recovery if necessary. The command is `alter database backup controlfile to trace;`. 4. **Recovery Process**: In case of media failure:

• Restore datafiles from hot backups.

• Ensure available archive logs and online redo logs for recovery using SQL command: `recover datafile '/path/to/datafile';`.

5. **Control File Recovery**: Use the backed-up control file trace to recreate the control file and recover the database up to the latest log. 6. **Recreating TEMP and ARC_TEMP Tablespaces**: If there are issues with these tablespaces, refer to specific sections for recreation steps. 7. **Scenario 2** (for databases running in archive mode): Exclude ARC_EVENT_DATA and ARC_EVENT_INDEX from hot backup schedules and back up event side tables using `arcdbutil export`. 8. **Backup Set**: Includes hot backups of all non-TEMP, non-ARC_TEMP tablespaces, control file backup, and archived redo logs. 9. **Recovery Steps**: Follow similar steps as in Scenario 1 for complete recovery, adjusting for archive mode operations. 10. **Important Notes**: Ensure all actions are taken to prevent data loss, especially during database initialization or when opening with the RESETLOGS option if not all archive logs are available. Always take a full backup after resetting online redo logs for optimal database health. The provided text outlines a detailed procedure for backing up and recovering Oracle databases, specifically focusing on two tablespaces, ARC_EVENT_DATA and ARC_EVENT_INDEX, used in an ArcSight system. The steps include taking physical backups of these tablespaces using the Oracle Export utility, creating SQL scripts to offline specified datafiles, viewing DDLs for creation, and handling recovery scenarios, including disaster recovery. Key points: 1. **Backup**: Use Oracle Export utility to backup ARC_EVENT_DATA and ARC_EVENT_INDEX tablespaces. Create Datafile Offline scripts are used to generate SQL scripts for taking these tablespaces offline, which can then be modified if necessary. The DDL (Data Definition Language) scripts for the creation of these tablespaces are also generated using provided tools. 2. **Recovery**: In case of data loss due to lost datafiles, follow specific steps:

• Use Datafile Offline scripts from backup set to take affected tablespaces offline.

• Drop and recreate the tablespaces using DDL scripts from backup.

• Import the tables structures using the export file dump provided in the backup set.

3. **Disaster Recovery**: If archive logs are not available, perform an incomplete recovery with a RESETLOGS option, followed by taking a full database backup. This procedure is crucial for ensuring data integrity and quick recovery in case of system failures or disasters. The document outlines a disaster recovery strategy for ArcSight databases that are not running in ArchiveLog mode. It emphasizes taking cold backups of control files, datafiles, and redo logs at preset intervals (preferably daily), with the option to exclude TEMP, ARC_TEMP, ARC_EVENT_DATA, and ARC_EVENT_INDEX tablespaces from backup if desired. The scenario involves performing partial schema-level exports for ArcSight schema excluding the mentioned tablespaces, along with taking a direct export of event side tables using specific commands. For recovery: 1. Restore cold backups to their original locations. If TEMP and ARC_TEMP are excluded, refer to the document for steps on recreating these temporary tablespaces. 2. Offline all datafiles belonging to ARC_EVENT_DATA and ARC_EVENT_INDEX tablespaces using provided scripts. If TEMP and ARC_TEMP were excluded, recreate them as per the instructions in this document. 3. Drop and recreate ARC_EVENT_DATA and ARC_EVENT_INDEX tablespaces using DDL scripts from the backup set. 4. Import the exported structures of ARC_EVENT_DATA and ARC_EVENT_INDEX using provided import commands. 5. Initialize partitions with specific SQL scripts. Alternatively, if starting fresh: 1. Create new databases with default system tablespaces (ARC_SYSTEM_DATA and ARC_SYSTEM_INDEX). 2. Import the exported tablespaces (ARC_SYSTEM_DATA and ARC_SYSTEM_INDEX) from ArcSight schema. 3. Recreate ARC_EVENT_DATA and ARC_EVENT_INDEX tablespaces using provided DDL scripts, then import their structure exports. 4. Initialize partitions with provided scripts for new databases. This technical note provides a comprehensive guide on how to reset the ArcSight Confidential Oracle backup and recovery process, focusing on resetting the partition catalog and recreating TEMP and ARC_TEMP tablespaces in case of media failures. The steps include running specific scripts for resetting partitions, creating initial partitions, and managing temporary tablespace files. **Steps for Resetting the Partition Catalog:** 1. **Shutdown ArcSight Manager**: If it is running, shut it down to ensure no ongoing processes interfere with the reset process. 2. **Save the Script**: Save the provided `ResetPartitions.sql` script on your database machine. 3. **Run the Script**: Log in as the arcsight user and run the script using the command `arcdbutil sql`. The script will be executed, resetting the partition catalog to include only MAX partitions and their resources. 4. **Verify Success**: Ensure that the script completes successfully without errors. **Steps for Creating Initial Partitions:** 1. **Run the “ResetPartitions.sql” Script**: This resets the ArcSight schema partitioned objects and resources, leaving only the MAX partitions. 2. **Run the “CreateInitialPartitions.sh” Script**: Execute this script in the Database installation ARCSIGHT_HOME, specifying the initial retention period for the partitions. **Handling Media Failures:** 1. **Note Down Affected Files**: Identify and note down the files belonging to TEMP or ARC_TEMP tablespaces that have media failure or are missing. 2. **Startup the Database in Mount Mode**: Logged in as the “sys” user, start the database in mount mode using `arcdbutil sql`. Enter commands like: ```sql SQL> startup mount ``` 3. **Offline Affected Files**: Use `arcdbutil alter database tempfile` and `arcdbutil alter database arc_tempfile` to take the offline, specifying the affected files’ paths. 4. **Recreate Tablespaces**: Drop and recreate the TEMP and ARC_TEMP tablespaces as per the instructions in the technical note, adjusting file locations and sizes as needed. **Creating Scripts for Offline Operations:** 1. **Create Datafile Offline Script**: Save a script named `CreateDatafileOffline.sql` that contains SQL statements to take datafiles offline based on tablespace names. Use the command: ```sql @CreateDatafileOffline arc_event_data ``` 2. **Run the Script**: Execute the created script file to perform the offline operations for specified tablespaces. This technical note provides a structured approach to managing and recovering from media failures in Oracle databases used by ArcSight Confidential, ensuring data integrity and operational continuity. Summary failed for this part. Summary failed for this part. This script is used to generate a DDL (Data Definition Language) script that creates an ArcSight object or tablespace when run as sysdba on Oracle DB. It outputs the DDL required for creating the specified object or tablespace, allowing users to customize and execute it manually if needed. The script supports both database objects and tablespaces by querying `dba_objects` and `dba_tablespaces` views, respectively, based on the provided name.

Disclaimer:

The content in this post is for informational and educational purposes only. It may reference technologies, configurations, or products that are outdated or no longer supported. If there are any comments or feedback, kindly leave a message and will be responded.