Skip to end of metadata
Go to start of metadata

Deleting the CRL data for a CA to allow the import of a fresh CRL

If a problem arises importing fresh CRLs for a particular CA it can sometimes require the existing CRL data to be deleted from the database to clear the error state. 
Once this has been done, the CRL Monitor process should be able to insert the fresh CRL data next time.
Note: Execute this query as it is, do not change any thing in it.

Oracle

Deleting all CRLs for a CA

Run the following SQL queries if you want to clear all CRLs for a CA:
Icon

Change the <CA Friendly Name> in above query with actual CA friendly name as registered in Trust Manager.

Checking the open connections on the database

Having adequate database connections available between ADSS Server and its database is important for normal functioning.  The following are the queries to review the current set of open connections:

SQL Server
Oracle


PostgreSQL

Queries to delete the sample data

Occasionally administrators include the sample data in a pre-production system and need to delete this prior to deployment.   The following SQL queries allow you to delete the sample data:

Deleting the CRLs for all CAs to import the fresh CRLs

 If a problem arises importing fresh CRLs for a particular CA it can be useful to delete the existing CRL data to clear the state.

Note: Execute this query as it is, do not change any thing in it.

Oracle

Identifying any duplicate serial numbers in issued certificates

This state should never arise but if you suspect a CA has done this then it can be checked: 

query

Update the machine name and IP addresses in ADSS Server database

query

Delete all OCSP transactions from database

Follow these instructions to quickly delete all OCSP transactions from the Database:

  1. Stop the ADSS Core, Console and Service instances from Windows NT Services panel or UNIX daemon
  2. Take the backup of the ADSS Server database
  3. Follow these instructions to delete the records permanently from the database e.g. Oracle database:
    • Open the Oracle database in any oracle client e.g SQL Developer
    • Open the relevant Oracle user
    • Right click the table OcspTransactionDetails
    • Select the option: Table > TRUNCATE and click Apply button to truncate the table
    • Now first you need to disable the related foreign keys constraint for the OcspTransactionLogs table and then truncate it. Follow these instructions:
      1. Right click the table OcspTransactionLogs
      2. Select the option: Constraint > Disable Related Foreign Keys and click the Apply button
      3. Right click the table OcspTransactionLogs again
      4. Select the option: Table > TRUNCATE and click the Apply button
      5. Right click the table OcspTransactionLogs again
      6. Select the option: Constraint > Enable Related Foreign Keys and click the Apply button
  4. Start the ADSS Server Core console and Service instances

Removing large data for a table from Oracle database without generating database log file 

Run the following SQL queries if you want to clear all data for an oracle table quickly without generating any database log file:

Restoring the backup of PostgresSQL database using command line

1. Launch postgres client e.g.PG Admin
2. Create an empty datbase e.g. ADSS-Backup
3. Launch cmd in administrator mode
4. Navigate to location: C:\Program Files\PostgreSQL\9.4\bin\
5. Execute the following command to restore the backup: [pgsql] --username=[User Name] [datbase name] [Path for the backup file]

Script to create a new table for Real-Time Full Certificate Status Checking

  1. Launch the Database Management Client Tool e.g.SQL Server Management Studio
  2. Login using appropriate user credentials
  3. Execute the following query on your relevant database by replacing the <TableName> with your desired name accordingly (Two occurrences in script except for MySQL):  
SQL Server
Oracle
Postgres SQL
MySQL

 

Script to upgrade ADSS Real-Time database for ADSS Server version v5.6 and above

Execute the following query on your relevant database by replacing the <TABLE_NAME> with your desired name accordingly;

SQL Server
Oracle
Postgres SQL
MySQL
  • No labels