Resolving Archive Log Gap on an Oracle Standby Database

If you are running an Oracle Standby Database sooner or later you will find yourself in the situation where your logs stop applying to the standby database due to a gap in the logs.

To check the logs on the standby database run the following SQL.

SELECT sequence#, applied, first_change# "Start SCN",
next_change# "End SCN", to_char(completion_time, 'hh24:mi:ss  DD_MON_YYYY')
FROM v$archived_log
WHERE completion_time > sysdate -1
ORDER BY sequence#

Running this SQL produces theoutput below:

——— — ———- ———- ——————–
12740 YES 1001281482 1003384098 08:32:12 05-NOV-2012
12742 NO 1003385234 1003388620 08:32:52 05-NOV-2012
12743 NO 1003388620 1003399424 08:42:07 05-NOV-2012
12744 NO 1003399424 1003402559 08:42:36 05-NOV-2012
12745 NO 1003402559 1003405620 08:43:03 05-NOV-2012
12746 NO 1003405620 1003408725 08:43:29 05-NOV-2012
12747 NO 1003408725 1003411778 08:43:54 05-NOV-2012
12748 NO 1003411778 1003414873 08:44:19 05-NOV-2012
12749 NO 1003414873 1003417952 08:44:44 05-NOV-2012
12750 NO 1003417952 1003421013 08:45:13 05-NOV-2012
12751 NO 1003421013 1003424066 08:45:43 05-NOV-2012
12752 NO 1003424066 1003427138 08:46:11 05-NOV-2012

You can see here that log 12740 has been applied by the database, log 12742 has been recieced but not applied.  The reason that log 12742 has not been applied is because log 12741 is missing.

To rectify this situation manualy copy the archive log from the primary database to the archive log location on the standby database.  Once the copy is complete register the  missing log with the standby database with the following SQL:

alter database register logfile ‘e:\oracle\oradata\mydb\archive\ARCH12741′

Once the logfile is registerd the recovery should resume automaticaly.

Automaticaly Delete Oracle Achivelog Files

Normally when you run backups through RMAN you would delete the archivelogs from your files system because once you have a backup there is no point having them hanging around filling up your disk space.

In my Dataguard environment the primary databases get backed up but the standby databases at the remote site don’t so I have a problem where I have to monitor the disk space on the remote site and delete the files manually when they become a problem.

Python is available in my environment so I set about writing a script that would delete the logfiles that were no longer required on a daily basis. To make a connection to the Oracle database we need to download and install the cx_oracle module so we can import it into our script.

import cx_Oracle, os

## ARC10816_794572399_1 : Archivelog format, yours may be different

## The location of the logs on disk
archive = "E:\\Oracle\\Oradata\\myDB\\archive"

## The Oracle database connection, I use OS authentication and connect as SYSDBA
## don't put passwords in scripts if you can help it

conn = cx_Oracle.connect('/@myDB', mode=cx_Oracle.SYSDBA)
curs = conn.cursor()
curs.arraysize = 50

## Execute a querry to determine the last log applied to the database
curs.execute('select max(sequence#) from v$archived_log where applied = \'YES\'')
maxlog = curs.fetchall()[0][0]

## For each file in the archive location, make sure its a logfile, determine the sequence
## number and if it's less than the last log applied, delete it.

## You will have to mess with this if your file format is different from mine

for dirpath, dnames, fnames in os.walk(archive):
    for f in fnames:
        if f[0:3]=="ARC"
            sequence = f[3:f.find("_")]
            if int(sequence) < maxlog:
                os.remove('E:\\Oracle\\Oradata\\myDB\\archive\\' + f)

Once the script has been tested you can schedule it to run every night. If you are using OS authentication make sure the user that runs the scheduled task has the correct permissions to connect to the database.

Manually Adding an Additional SDE Service

I have recently been doing a lot of database consolidation and as a result have found it necessary to create more than one SDE service on my database server.

First you must locate the directory that contains the default file location for your SDE service, on my server this is C:\ArcGIS\ArcSDE\ora10gexe from the folder name you can see that this service connects to an Oracle 10g database.

Copy the entire contents of this folder and paste it back into the same parent directory and rename the directory so it represents the new service you wish to create.

Under the new location C:\ArcGIS\ArcSDE\<newservice>  locate the ect folder and edit the dbinit.sde file.  This file contains the Oracle SID of the database you connect to, edit this so it connects to your new database.  In the same location edit the services.sde file.  This file contains the name of the new service and the port number it will use.  Change the service name from esri_sde to the name of your new service and the port number to a new free port.  I always tend to keep all the service ports close together.

You also need to edit the services file in C:\windows\system32\drivers\ect.  Add the name of your new service and port to the end of the file.

Once the above configuration is complete open the command line and run the following sdeservice -o create -d oracle10g,<SID> -p <sde password> -i <newservice> -H <path to the new folder> once complete you will see the new service under windows services.

Oracle Dataguard Broker – How to Fix it When it’s Broke

Oracle Dataguard is a fantastic product.  In my experience once it is configured correctly it is extremely reliable and provides for solid disaster recovery.

Dataguard is monitored by the Dataguard Broker and I usually configure this through the Enterprise Manager.  Every now and then during configuration the broker configuration breaks and hangs – you cant get to the configuration page in Enterprise Manager and you get no response issuing commands via the DGMGRL command line tool.

If you find yourself in this situation you need to remove the Broker configuration manually.  To do this follow the steps below.

1.  Navigate to the flash_recovery_area for you database and delete the Broker configuration files.  They look like ‘DR1<SID>.DAT’.

2.  Log into you database as sysdba and enter the following:

alter system set dg_broker_start = false

followed by

alter system set dg_broker_start = true

You should now be able to configure the Broker through Enterprise Manager.