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.