A.6. Oracle backend

Table A-6. Oracle backend capabilities

NativeYes
MasterNo
SlaveNo
SuperslaveNo
AutoserialYes
Module nameoracle
Launch nameoracle

Oracle backend with easily configurable SQL statements, allowing you to graft PDNS on any Oracle database of your choosing.

PowerDNS is currently ascertaining if this backend can be distributed in binary form without violating Oracle licensing. In the meantime, the source code to the Oracle backend is available in the pdns distribution.

The following configuration settings are available:

oracle-debug-queries

Output all queries to disk for debugging purposes.

oracle-time-queries

Output all queries to disk for timing purposes.

oracle-uppercase-database

Change all domain names to uppercase before querying database.

oracle-database

Oracle database name to connect to.

oracle-home

PDNS can set the ORACLE_HOME environment variable from within the executable, allowing execution of the daemon from init.d scripts where ORACLE_HOME may not yet be set.

oracle-sid

PDNS can set the ORACLE_SID environment variable from within the executable, allowing execution of the daemon from init.d scripts where ORACLE_SID may not yet be set.

oracle-username

Oracle username to connect as.

oracle-password

Oracle password to connect with.

The generic Oracle backend can be configured to use user-specified queries. The following are the default queries and their names:

oracle-forward-query

select content, TimeToLive, Priority, type, ZoneId, nvl(ChangeDate,0) from Records where name = :name and type = :type

oracle-forward-query-by-zone

select content, TimeToLive, Priority, type, ZoneId, nvl(ChangeDate,0) from records where name = :name and type = :type and ZoneId = :id

oracle-forward-any-query

select content, TimeToLive, Priority, type, ZoneId, nvl(ChangeDate,0) from records where name = :name

oracle-list-query

select content, TimeToLive, Priority, type, ZoneId, nvl(ChangeDate, 0), name from records where ZoneId = :id

A.6.1. Setting up Oracle for use with PowerDNS

To setup a database that corresponds to these default queries, issue the following as Oracle user sys:

	    create user powerdns identified by YOURPASSWORD;
	    grant connect to powerdns;
	    
	    create tablespace powerdns datafile '/opt/oracle/oradata/oracle/powerdns.dbf'
	      size 256M extent management local autoallocate;
	    
	    alter user powerdns quota unlimited on powerdns; 
	  

As user 'powerdns' continue with:

create table Domains (
 ID		 number(11) NOT NULL,
 NAME		 VARCHAR(255) NOT NULL,
 MASTER		 VARCHAR(128) DEFAULT NULL,
 LAST_CHECK	 INT DEFAULT NULL,
 TYPE		 VARCHAR(6) NOT NULL,
 NOTIFIED_SERIAL INT DEFAULT NULL,
 ACCOUNT	 VARCHAR(40) DEFAULT NULL,
 primary key (ID)
)tablespace POWERDNS;

create index DOMAINS$NAME on Domains (NAME) tablespace POWERDNS;
create sequence DOMAINS_ID_SEQUENCE;

create table Records
(
  ID                number(11) NOT NULL,
  ZoneID            number(11) default NULL REFERENCES Domains(ID) ON DELETE CASCADE,
  NAME              varchar2(255) default NULL,
  TYPE              varchar2(6) default NULL,  
  CONTENT           varchar2(255) default NULL,
  TimeToLive        number(11) default NULL,   
  Priority          number(11) default NULL,
  CreateDate        number(11) default NULL,
  ChangeDate        number(11) default NULL,
  primary key (ID)
)tablespace POWERDNS;

create index RECORDS$NAME on RECORDS (NAME) tablespace POWERDNS;
create sequence RECORDS_ID_SEQUENCE;
	  

To insert records, either use zone2sql with the --oracle setting, or execute sql along the lines of:

insert into domains (id,name,type) values (domains_id_sequence.nextval,'netherlabs.nl','NATIVE');
insert into Records (id,ZoneId, name,type,content,TimeToLive,Priority) select RECORDS_ID_SEQUENCE.nextval,id ,'netherlabs.nl', 'SOA', 'ahu.casema.net. hostmaster.ds9a.nl. 2000081401 28800 7200 604800 86400', 3600, 0 from Domains where name='netherlabs.nl';
insert into Records (id,ZoneId, name,type,content,TimeToLive,Priority) select RECORDS_ID_SEQUENCE.nextval,id ,'netherlabs.nl', 'NS', 'ahu.casema.net', 3600, 0 from Domains where name='netherlabs.nl';
insert into Records (id,ZoneId, name,type,content,TimeToLive,Priority) select RECORDS_ID_SEQUENCE.nextval,id ,'netherlabs.nl', 'NS', 'ns1.pine.nl', 3600, 0 from Domains where name='netherlabs.nl';
insert into Records (id,ZoneId, name,type,content,TimeToLive,Priority) select RECORDS_ID_SEQUENCE.nextval,id ,'netherlabs.nl', 'NS', 'ns2.pine.nl', 3600, 0 from Domains where name='netherlabs.nl';
insert into Records (id,ZoneId, name,type,content,TimeToLive,Priority) select RECORDS_ID_SEQUENCE.nextval,id ,'netherlabs.nl', 'A', '213.244.168.210', 3600, 0 from Domains where name='netherlabs.nl';
insert into Records (id,ZoneId, name,type,content,TimeToLive,Priority) select RECORDS_ID_SEQUENCE.nextval,id ,'netherlabs.nl', 'MX', 'outpost.ds9a.nl', 3600, 10 from Domains where name='netherlabs.nl';

	  

For performance reasons it is best to specify --transactions too!