Configure SQL Server Database Mirroring Using SSMS

Configure SQL Server Database Mirroring Using SSMS

Problem

I have a need to setup SQL Server Database Mirroring in my environment.  I understand it can be complicated to setup. Can you provide an example on setting up SQL Server Database Mirroring?  Check out this tip for a basic look at how to setup this SQL Server feature.

Solution

In this tip I am going to outline my environment and then walk through the process of setting up Database Mirroring.  This will include the configurations, backups, restores and verification process.  Let's jump in.

My test environment consists of two separate VM's running VM Workstation with Windows 2008 R2 Datacenter Edition and SQL Server 2008 R2 Enterprise named appropriately Principal and Mirror. The SQL Server and SQL Server Agent Services accounts are running as domain users (DOMAIN\User). Windows Firewall is OFF for the sake of this example.

I created a database on the Principal SQL Server instance and named it TestMirror. The recovery model is set to FULL RECOVERY.

1st step: Issue a full backup of the database.

BACKUP DATABASE TestMirror TO DISK = 'C:\Program Files\Microsoft SQL 
Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Backup.bak';

2nd step: Issue a transaction log backup of the database.

BACKUP LOG TestMirror TO DISK = 'C:\Program Files\Microsoft SQL 
Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Backup.trn'; 

Below are the two files in the file system:

3rd step: Assuming you have the backup folder shared on the Principal Server and you can access it from the Mirror Server, you will need to restore the full backup to the Mirror server with the NORECOVERY option.

RESTORE DATABASE TestMirror FROM DISK = N'\\Principal\Backup\Backup.bak' 
WITH FILE = 1, MOVE N'TestMirror_log' TO 
N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TestMirror_1.ldf', 
NORECOVERY, NOUNLOAD, STATS = 10;

4th step: Restore log backup also with the NORECOVERY option.

RESTORE LOG TestMirror FROM DISK = N'\\Principal\Backup\Backup.trn' 
WITH  FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10;

Now it's time to dig down and configure Database Mirroring. From the Principal server, right click the database and choose "Tasks" | "Mirror" or choose "Properties" | "Mirroring".

Click the "Configure Security" button and click "Next >" if the Configure Database Mirroring Security Wizard intro screen appears. The next screen should be the Include Witness Server screen:

This is where you would configure a witness server for your mirroring, but since we're just configuring a basic mirror we will skip this part. However, if you are configuring mirroring in an Enterprise environment it is recommended you configure a witness server because without one you will not have synchronous automatic failover option.

Select "No", then click "Next >" to continue the process.

The next screen will give you options to configure the Principal Server Instance:

Here we will be creating our endpoint, which is a SQL Server object that allows SQL Server to communicate over the network. We will name it Mirroring with a Listener Port of 5022.

Click the "Next >" button to continue.

The next screen will give you options to configure the Mirror Server Instance:

To connect to the Mirror server instance we will need to click the "Connect..." button then select the mirror server and provide the correct credentials:

Once connected, we also notice our endpoint name is Mirroring and we are listening on port 5022.

Click "Next >" and you'll see the Service Accounts screen.

When using Windows Authentication, if the server instances use different accounts, specify the service accounts for SQL Server. These service accounts must all be domain accounts (in the same or trusted domains).

If all the server instances use the same domain account or use certificate-based authentication, leave the fields blank.

Since my service accounts are using the same domain account, I'll leave this blank.

Click "Finish" and you'll see a Complete the Wizard screen that summarizes what we just configured. Click "Finish" one more time.

If you see the big green check mark that means Database Mirroring has been configured correctly. However, just because it is configured correctly doesn't mean that database mirroring is going to start...

Next screen that pops up should be the Start/Do Not Start Mirroring screen:

We're going to click Do Not Start Mirroring just so we can look at the Operating Modes we can use:

Since we didn't specify a witness server we will not get the High Safety with automatic failover option, but we still get the High Performance and High Safety without automatic failover options.

For this example, we'll stick with synchronous high safety without automatic failover so changes on both servers will be synchronized.

Next, click "Start Mirroring" as shown below.

If everything turned out right, Database Mirroring has been started successfully and we are fully synchronized.

 

If Database mirroring did not start successfully or you received an error here are a few scripts to troubleshoot the situation:

Both servers should be listening on the same port. To verify this, run the following command:

SELECT type_desc, port 
FROM sys.tcp_endpoints;

We are listening on port 5022. This should be the same on the Principal and Mirror servers:

Database mirroring should be started on both servers. To verify this, run the following command:

SELECT state_desc
FROM sys.database_mirroring_endpoints;

The state_desc column on both the Principal and Mirror server should be started:

To start an Endpoint, run the following:

ALTER ENDPOINT 
STATE = STARTED 
AS TCP (LISTENER_PORT = )
FOR database_mirroring (ROLE = ALL);

ROLES should be the same on both the Principal and Mirror Server, to verify this run:

SELECT role 
FROM sys.database_mirroring_endpoints;

To verify the login from the other server has CONNECT permissions run the following:

SELECT EP.name, SP.STATE,
CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))
AS GRANTOR,
SP.TYPE AS PERMISSION,
CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))
AS GRANTEE
FROM sys.server_permissions  SP , sys.endpoints EP
WHERE SP.major_id  = EP.endpoint_id
ORDER BY  Permission,grantor, grantee;

You can see here from the State and Permissions column that the user has been Granted Connect permissions.

How to add a database file to a mirrored SQL Server database

How to add a database file to a mirrored SQL Server database

Problem

When using Database Mirroring and if you have not placed your database files for your principal database and mirrored database on an identical path then adding a database file to the principal database is quite different than the normal process. In this case, when you add a database file to a database, your mirroring configuration will be suspended because SQL Server will not be able to create that file on the mirrored server. This will not allow the databases to be in sync and will force mirroring to go into a suspended state.

If the complete path (including drive letter and folder names) for the database files exists on both the principal and mirrored server then you can follow the normal process, but if the paths are different then you would have to follow this process to add a database file to a mirrored database.

In this tip I will describe step by step how to add a database file for a mirrored database that has different drives and/or paths.

Solution

At a high level these are the steps we will take to add a new database file for a mirrored database that has different file paths on the principal and mirror.  First remove the mirror partner, then create the database file on the principal server. After that I will take a log backup and restore it on the mirrored server using the WITH MOVE option. Once the restore is done, I will re-establish database mirroring.

NOTE: DO NOT MAKE ANY CHANGES IN PRODUCTION WITHOUT PROPER TESTING IN LOWER-LIFE CYCLE ENVIRNOMENTS

Steps to add new database file to mirrored database

Step 1

First, we should check the mirroring configuration and partner status. Run the below command on the principal server to get this information.  Here we have used database id 5 for our database, you can find your database id for your mirrored database using "sp_helpdb".

SELECT (SELECT DB_NAME(5))AS DBName,database_id,mirroring_state_desc,
mirroring_role_desc,mirroring_partner_name,mirroring_partner_instance
FROM sys.database_mirroring
WHERE database_id=5

Step 2

We can see the mirroring status is synchronized and its mirror partner name. Before moving ahead, we should disable any SQL Server backup jobs to reduce any futher complexity. Otherwise, if any log backup occurs in between you will need to restore all log backups on the mirrored server before establishing the database mirror.

Now we will remove the mirror partner to initiate this file creation process. Run the below command on the principal server to break the mirror.

ALTER DATABASE AdventureWorks2008R2 SET PARTNER OFF

Now you can again check the mirroring configuration with the help of the SQL code in step 1. Once you run step 1 again, the output should be like the screenshot below. You can see the status and partner names are showing NULL, meaning the mirroring configuration is broken. Also your database on the mirrored server will be in a restoring state.

Step 3

Next, create your database file on the principal server. I ran this statement to create a secondary database file on the principal server. You can use the GUI method as well to create this database file.

ALTER DATABASE AdventureWorks2008R2 ADD FILE (NAME = AdventureWorks2008R2_Data2, 
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008R2_Data2.ndf', 
SIZE = 1000MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%)

Step 4

Once you have added a new database file on your database, run a log backup that will be restored on the mirrored server.

BACKUP LOG AdventureWorks2008R2
TO DISK = 'C:\AdventureWork2008R2_25Nov20121229.trn'
WITH INIT

Step 5

Before restoring the log backup on the mirrored server, you can check whether your newly created file is captured in the log backup. Run the RESTORE FILELISTONLY statement to get this info.

RESTORE FILELISTONLY
FROM DISK = 'C:\AdventureWork2008R2_25Nov20121229.trn'

We can see that the newly created file is captured in the log backup file, so go ahead and restore this log backup on the mirrored server using the NORECOVERY and MOVE options. We are using the MOVE option to place the file in a different location on our mirrored server.

RESTORE DATABASE AdventureWorks2008R2 
FROM DISK = 'C:\AdventureWork2008R2_25Nov20121229.trn' 
 WITH NORECOVERY,
 MOVE 'AdventureWorks2008R2_Data2'
  TO 'F:\Program Files\Microsoft SQL Server\MSSQL10_50.MANVENDRA\MSSQL\DATA\AdventureWorks2008R2_Data2.ndf'

Step 6

Now the principal database and mirror database have the new database file. Now we should re-establish mirroring between the servers for this database. First, add the partner server on the mirror server. The below SQL code will add the principal server on the mirror server.

ALTER DATABASE [AdventureWorks2008R2] SET PARTNER = 
'TCP://PRINCIPALSERVERNAME.DOMAIN.com:5022'

Step 7

Now add the partner server on the principal. Run the below SQL code to add the mirror server to the principal server to resume mirroring.

ALTER DATABASE [AdventureWorks2008R2] SET PARTNER = 
'TCP://MIRRORSERVERNAME.DOMAIN.com:5023'

Step 8

Once the above commands are successful, you are done with your task to add a database file to a mirrored database.

We can check and verify whether the mirroring configuration is established between both databases. Run this SQL statement along with sp_heflpfile on the principal server.

SELECT (SELECT DB_NAME(5))AS DBName,database_id,mirroring_state_desc,
mirroring_role_desc,mirroring_partner_name,mirroring_partner_instance
FROM sys.database_mirroring
WHERE database_id=5
GO
USE AdventureWorks2008R2
go
sp_helpfile

You can also verify on your mirrored server after a failover to check the database properties for the mirrored database.

SQL VERSION INFORMATION

SQL Server 2012 version information

The following table lists the major releases of SQL Server 2012:

Release	Product Version
SQL Server 2012 Service Pack 1	11.00.3000.00
SQL Server 2012 RTM	11.00.2100.60

SQL Server 2008 R2 version information

The following table lists the major releases of SQL Server 2008 R2:

Release	Product version
SQL Server 2008 R2 Service Pack 2	10.50.4000.0
SQL Server 2008 R2 Service Pack 1	10.50.2500.0
SQL Server 2008 R2 RTM	10.50.1600.1

SQL Server 2008 version information

The following table lists the major releases of SQL Server 2008:

Release	Product version
SQL Server 2008 Service Pack 3	10.00.5500.00
SQL Server 2008 Service Pack 2	10.00.4000.00
SQL Server 2008 Service Pack 1	10.00.2531.00
SQL Server 2008 RTM	10.00.1600.22

SQL Server 2005 version information

The following table lists the major releases of SQL Server 2005:

Release	Product version
SQL Server 2005 Service Pack 4	9.00.5000.00
SQL Server 2005 Service Pack 3	9.00.4035
SQL Server 2005 Service Pack 2	9.00.3042
SQL Server 2005 Service Pack 1	9.00.2047
SQL Server 2005 RTM	9.00.1399

SQL Server 2000 version information

The following table lists version number of the Sqlservr.exe file: 

Release	Product version
SQL Server 2000 Service Pack 4	8.00.2039
SQL Server 2000 Service Pack 3	8.00.760
SQL Server 2000 Service Pack 3	8.00.760
SQL Server 2000 Service Pack 2	8.00.534
SQL Server 2000 Service Pack 1	8.00.384
SQL Server 2000 RTM	8.00.194

How to determine your SQL Server Version, Service Pack, and Edition

Query to determine your SQL Server Version, Service Pack, and Edition

Connect to the instance of SQL Server, and then run the following query:

Select @@version



SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

How to conduct a Oracle compliance audit

How to conduct a Oracle compliance audit

http://www.isaca.org/Groups/Professional-English/oracle-database/GroupDocuments/Sources_of_Assurance_for_an_Oracle_Database_Update_3.pdf

Managing Audit Trails - Oracle database auditing

Managing Oracle database Audit Trails

Relocate the audit trail to a different tablespace and set up an automatic purge process to keep its size under control.

One of the most significant aspects of database security involves setting up auditing to record user activities. The very knowledge that a user’s actions are being recorded can act as a significant deterrent to prevent wrongdoers from committing malicious acts.

When auditing is enabled, the audit output is recorded in an audit trail, which is usually stored in the database in a table under the SYS schema called AUD$. It can also reside as files in the file system, and the files can optionally be stored in XML format. For more-precise control, the Fine Grained Auditing feature of Oracle Database 11g provides granular control of what to audit, based on a more detailed set of policies. Fine Grained Auditing audits are usually stored in another table, FGA_LOG$, under the SYS schema.

These various audit trails can quickly grow out of control when database activity increases. As audit trails grow, two main challenges must be addressed: 

1. Trails need to be kept to a manageable size (and old records purged) if they are to be used effectively in forensic analysis.
2. Because database-resident trails are typically stored in the SYSTEM tablespace, they can potentially fill it up—bringing the database to a halt. 

Fortunately, the new auditing features in Oracle Database 11g Release 2 can help address these challenges. These capabilities, implemented in a package called DBMS_AUDIT_MGMT, enable you to move audit trails from the SYSTEM tablespace to one of your choice.

The new auditing features also let you set up one-time and automated purge processes for each of your audit trail types. Historically, to purge an audit trail, you were generally forced to stop auditing (which may have required bouncing the database), truncate, and then restart auditing (and bouncing the database again).

In this article, you will learn how to use the new features in Oracle Database 11g Release 2 to manage your audit trails. 

Relocating the Audit Trail Tables

Let’s first examine how to relocate an audit trail from the default SYSTEM tablespace to a new one. In case you don’t already have a suitable target tablespace, the code below shows how to create one: 

create tablespace audit_trail_ts
datafile '+DATA'
size 500M
segment space management auto
/
 

For moving an audit trail to the new tablespace, Oracle Database 11g Release 2 provides a procedure in DBMS_AUDIT_MGMT called SET_AUDIT_TRAIL_LOCATION. Listing 1 shows how to move a “standard” audit trail, which is the Oracle Database audit recorded in the AUD$ table.

Code Listing 1: Relocating a standard audit trail 

begin
 dbms_audit_mgmt.set_audit_trail_location(
  audit_trail_type            => dbms_audit_mgmt.audit_trail_aud_std,
  audit_trail_location_value  => 'AUDIT_TRAIL_TS');
end;
/
 

This move operation can be performed even when the database is up and an audit trail is being written. The target tablespace (AUDIT_TRAIL_TS in this case) must be available and online. If the tablespace is not available, auditing will stop, also stopping the database in the process. You should therefore be very careful about where you create the tablespace. The location should be permanent (and not on a temporary file system such as /tmp), and the underlying hardware should be resilient against failures (using RAID-1, for example).

The procedure can also be used for Fine Grained Auditing audit trails. To move a Fine Grained Auditing audit trail, simply replace the value of the audit_trail_type parameter in Listing 1 with dbms_audit_mgmt.audit_trail_fga_std. If you want to move both the standard and Fine Grained Auditing audit trails to the new tablespace, use the dbms_audit.audit_trail_db_std value as the audit_trail_type parameter. 

Purging Old Data

Next, let’s examine how to purge audit trails. The audit management package includes a procedure that automatically performs the purge for you. But before you can actually use it, you must call a one-time initialization procedure—INIT_CLEANUP—to set up the audit management infrastructure. Listing 2 shows how to perform the initialization. 

Code Listing 2: Initializing cleanup of audit entries 

begin
  dbms_audit_mgmt.init_cleanup(
    audit_trail_type            => dbms_audit_mgmt.audit_trail_db_std,
    default_cleanup_interval    => 24 );
end;
 

The INIT_CLEANUP procedure takes two parameters, neither of which takes a default value: 

• audit_trail_type—designates the type of audit trail being initialized. For instance, audit_trail_aud_std indicates the standard database audit trail (the AUD$ table). Table 1 lists the possible values for this parameter and the audit trail types they represent.
• default_cleanup_interval—designates the default interval in hours between executions of automatic purge jobs (to be discussed later in this article).

Parameter	Description
audit_trail_aud_std	The standard AUD$ audit trail in the database
audit_trail_fga_std	The FGA_LOG$ table, for Fine Grained Auditing
audit_trail_db_std	Both standard and FGA audit trails
audit_trail_os	The OS audit trail
audit_trail_xml	The XML audit trail
audit_trail_files	Both OS and XML audit trails
audit_trail_all	All of the above

 Table 1: Types of audit trails for audit_trail_type

In addition to setting the default cleanup frequency, the INIT_CLEANUP procedure moves the audit trail out of the SYSTEM tablespace. If the FGA_LOG$ and AUD$ tables are in the SYSTEM tablespace, the procedure will move them to the SYSAUX tablespace. Needless to say, you should ensure that the SYSAUX tablespace has sufficient space to hold both of these tables. The process of moving data from one tablespace to the other can have an impact on performance, so you should avoid calling the procedure during peak hours.

If you have already relocated these two tables to another tablespace (as described in the previous section), they will stay in the new location and the procedure will execute much more quickly.

After calling the initialization procedure, you can perform the actual audit trail cleanup, but you likely wouldn’t just remove an audit trail blindly. In most cases, you would archive the trail first before performing a permanent purge. When doing so, you can call another procedure—SET_LAST_ARCHIVE_TIMESTAMP—to let the purge process know the time stamp up to which an audit trail has been archived. This procedure accepts three parameters: 

• audit_trail_type—the type of audit trail you are about to purge.
• last_archive_time—the last time the audit trail was archived for this type.
• rac_instance_number—with an Oracle Real Application Clusters (Oracle RAC) database, OS audit trail files exist on more than one server. It’s possible to archive these files at different times, so this parameter tells the purge process the archive time of each node (or instance number) of the cluster. This parameter is applicable to Oracle RAC databases only; it has no significance for single-instance databases. Furthermore, this parameter is irrelevant for database audit trails, because they are common to all Oracle RAC instances. 

After you set the archive time stamp, you can check its value from a data dictionary view, DBA_AUDIT_MGMT_LAST_ARCH_TS. Listing 3 shows how to set the cutoff time stamp to September 30, 2009 at 10 a.m. and subsequently check its value from the view.

Code Listing 3: Setting the last archived time 

begin
   dbms_audit_mgmt.set_last_archive_timestamp(
     audit_trail_type  => dbms_audit_mgmt.audit_trail_aud_std,
     last_archive_time => 
        to_timestamp('2009-09-30 10:00:00','YYYY-MM-DD HH24:MI:SS'),
     rac_instance_number  => null
   );
end;
/

SQL> select * from DBA_AUDIT_MGMT_LAST_ARCH_TS;

AUDIT_TRAIL           RAC_INSTANCE
——————————————————————————————————
LAST_ARCHIVE_TS
——————————————————————————————————
STANDARD AUDIT TRAIL  0
30-SEP-09 10.00.00.000000 AM +00:00
 

Now you can execute the purge. To do so, run the code shown in Listing 4. The CLEAN_AUDIT_TRAIL procedure in the listing accepts two parameters. The first one is audit_trail_type. The second parameter—use_last_arch_timestamp—specifies whether the purge should be performed, depending on the last archive time stamp. If the parameter is set to TRUE (the default), the purge will delete the records generated before the time stamp (September 30, 2009 at 10 a.m. in this case). If it is set to FALSE, all audit trail records will be deleted.

Code Listing 4: Purging a standard database audit trail 

begin
  dbms_audit_mgmt.clean_audit_trail(
   audit_trail_type        =>  dbms_audit_mgmt.audit_trail_aud_std,
   use_last_arch_timestamp => TRUE
  );
end;
/
 

This same procedure is also used to purge file-based audit trails such as OS file audit trails and XML trails. To purge those trails, just specify the appropriate value for the audit_trail_type parameter (as shown in Table 1). However, note that for file-based audit trails, only the files in the current audit directory (as specified by the audit_file_dest initialization parameter) will be deleted. If you have audit trail files in a different directory from the one specified in audit_file_dest, those files will not be deleted.

Note that in Microsoft Windows, audit trails are entries in Windows Event Viewer and not actual OS files. So purging OS-based audit trails on that platform will not delete the trails. 

Setting Up Automatic Purge

The foregoing process is good for a one-time purge of audit trails. To ensure that audit trails do not overwhelm their tablespace, you may want to institute an automatic purge mechanism. The DBMS_AUDIT_MGMT package has another procedure—CREATE_PURGE_JOB—to do just that. This procedure takes four parameters: 

• audit_trail_type—the type of the audit trail
• audit_trail_purge_interval—the duration, in hours, between executions of the purge process
• audit_trail_purge_name—the name you assign to this job
• use_last_arch_timestamp—an indication of whether the job should delete audit trail records marked as archived. The default is TRUE. If the parameter is set to FALSE, the procedure will delete the entire trail. 

Listing 5 shows how to create a purge job that deletes standard audit trail records every 24 hours. As with one-time purges, you can create different jobs for each type of trail—such as standard, Fine Grained Auditing, OS files, and XML—simply by specifying different values for audit_trail_type when calling CREATE_PURGE_JOB. You can even set different purge intervals for each audit trail type to suit your archival needs. For instance, you can use a simple database-link-based script to pull database audit trail records to a different database while using a third-party tool to pull the OS audit trails. The execution time of each approach may be different, causing the database records to be pulled every day while the OS files are being pulled every hour. As a result, you might schedule purge jobs with an interval of 24 hours for database-based trails and with an interval of one hour for OS-file-based trails.

Code Listing 5: Creating a purge job for a standard audit trail 

begin
   dbms_audit_mgmt.create_purge_job (
   audit_trail_type            => dbms_audit_mgmt.audit_trail_aud_std,
   audit_trail_purge_interval  => 24,
   audit_trail_purge_name      => 'std_audit_trail_purge_job',
   use_last_arch_timestamp     => TRUE
   );
end;
/
 

You can view information about automatic purge jobs by accessing the DBA_AUDIT_MGMT_CLEANUP_JOBS data dictionary view. It shows all the important attributes of the job, such as the name, the type of audit trail being cleaned, and the frequency. 

Setting Audit Trail Properties

When setting up a purge job, you should always remember one very important fact. It performs a DELETE operation—not TRUNCATE—on database-based trails, so the purge operation generates redo and undo records, which may be quite significant, depending on the number of trail records deleted. A large deletion can potentially fill up the undo tablespace. To reduce the redo size of a transaction, the purge job deletes in batches of 1,000 and performs commits between them. If the database is very large, it may be able to handle much more redo easily. You can change the delete batch size by using the SET_AUDIT_TRAIL_PROPERTY procedure. Listing 6 shows how to set the delete batch size to 100,000. 

Code Listing 6: Setting the deletion batch size 

begin
 dbms_audit_mgmt.set_audit_trail_property(
  audit_trail_type            => dbms_audit_mgmt.audit_trail_aud_std,
  audit_trail_property        => dbms_audit_mgmt.db_delete_batch_size,
  audit_trail_property_value  => 100000);
end;
/
 

In addition to the db_delete_batch_size property referenced in Listing 6, you can use SET_AUDIT_TRAIL_PROPERTY to set several other important properties. They include the following: 

• file_delete_batch_size specifies how many OS audit trail files will be deleted by the purge job in one batch.
• cleanup_interval specifies the default interval, in hours, between executions of a purge job.
• os_file_max_age specifies how many days an OS file or an XML file can be left open before a new file is created.
• os_file_max_size specifies the maximum size of an audit trail file (in kilobytes). 

To find the current value of a property, you can check the data dictionary view DBA_AUDIT_MGMT_CONFIG_PARAMS. 

Conclusion

Audit trails establish accountability. In Oracle Database 11g, there are several types of audit trails—standard, fine-grained, OS-file-based, and XML. In this article, you learned how to relocate a database-based audit trail from its default tablespace—SYSTEM—to another one designated only for audit trails. You also learned how to purge audit trails of various types to keep them within a manageable limit, and you finished by establishing an automatic purge process.