The Oracle Instructor

I was giving a 10g Data Guard course this week in Düsseldorf, demonstrating amongst others the possibility to configure Transparent Application Failover (TAF) for Data Guard. I always try to keep things as simple as seriously possible, in order to achieve an easy and good understanding of what I like to explain. Later on, things are getting complex by themselves soon enough

In my simple scenario, I have one Primary Database (prima) and one Physical Standby Database (physt). On my downloads page, you may find an installation guide for that scenario for 10g and for 11g. After a switchover or after a failover, the primary is going to be physt. The challenge is now to get the connect from the client side to the right (primary) database. That is called Connect Time Failover and is achieved as follows:

First, we make sure that the client uses a tnsnames.ora with a connect descriptor that uses a SERVICE_NAME instead of a SID

MYAPP =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = HostA)(PORT = 1521))
 (ADDRESS = (PROTOCOL = TCP)(HOST = HostB)(PORT = 1521))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = myapp)
 )
 )

HostA is the host on which prima runs, HostB has physt running.

Second, we take care that the service myapp is offered only at the right database – only on the primary.Notice that the PMON background processes of both databases must be able to communicate with the (local) listeners in order to register the service myapp. If you don’t use the listener port 1521, they can’t. You have to point to that listener port then with the initialization parameter LOCAL_LISTENER.

We create and start now the service myapp manually on the primary:

begin
 dbms_service.create_service('myapp','myapp');
end;
/
begin
 DBMS_SERVICE.START_SERVICE('myapp');
end;
/

Then we create a trigger, that ensures that this service is only offered, if the database is in the primary role:

create trigger myapptrigg after startup on database
declare
 v_role varchar(30);
begin
 select database_role into v_role from v$database;
 if v_role = 'PRIMARY' then
 DBMS_SERVICE.START_SERVICE('myapp');
 else
 DBMS_SERVICE.STOP_SERVICE('myapp');
 end if;
end;
/

The event after startup is fired, if an instance changes from status MOUNT to OPEN. If you use a logical standby, it is not fired, because the logical standby remains in status OPEN. You may use the event after db_role_change in this case. The creation of the trigger and of the service is accompanied with redo protocol (the Data Dictionary has changed) and therefore also present at physt without any additional work to do there for the DBA. With the present setup, we have already achieved Connect Time Failover: Clients can use the same connect descriptor (myapp) to get to the right (primary) database now, regardless of switchover or failover.

But sessions that are connected to prima are disconnected if a switchover or failover to physt takes place. They have got to connect again then. We can change that, so that a Runtime Failover is possible, under ideal circumstances, that failover is even completely transparent to the client and proceeds without error messages. To achieve that, you don’t have to touch the tnsnames.ora on the client side. Instead, you do the following on the primary database:

begin
 dbms_service.modify_service
 ('myapp',
 FAILOVER_METHOD => 'BASIC',
 FAILOVER_TYPE => 'SELECT',
 FAILOVER_RETRIES => 200,
 FAILOVER_DELAY => 1);
end;
/

Connections to the service myapp are now automatically failed over together with the service to the new primary. Should they have done nothing during the time of the failover/switchover, or even if they had run a select statement, they will not receive any error but only notice a short interruption (about 20 seconds, in a typical case). Only if sessions have open transactions during the failover/switchover, they will receive error messages (“transaction must roll back”) after they try commit then.

I use to demonstrate that with a select on a table with 100000 rows that starts on the primary. Then I kill the SMON of that primary and the select stops at row 30000 something, waits a couple of seconds (maximal 200, with the above settings) and then continues on the new primary after the failover, fetching exactly the 100000 rows! That is always quite impressing and shows how robust Oracle Databases – especially combined with Data Guard – are

This week, I am teaching another 11g Data Guard course in Kista (Sweden, near Stockholm). Our education center is there in a quiet area (Kronborgsgränd 17, 164 28 Kista) together with other buildings rented by mostly IT companies:

One nice little feature a came across during that course is the possibility to get DDL statements recorded in the alert.log file and in the DBA_LOGSTDBY_EVENTS view. If I recall that right, that was even the default with 10g Data Guard Logical Standby, but in 11g, you have to do the following at the Logical Standby for it:

SQL> exec dbms_logstdby.apply_set('RECORD_APPLIED_DDL','TRUE')

PL/SQL procedure successfully completed.

SQL> exec dbms_logstdby.apply_set('EVENT_LOG_DEST','DEST_ALL')

PL/SQL procedure successfully completed.
SQL> connect scott/tiger@prima
Connected.
SQL> create table test as select * from dept;
Table created.

The second exec lead to the additional recording of te DDL into the alert.log file, else it would only be visible in DBA_LOGSTDBY_EVENTS. Following is from the alert.log then:

Wed Jun 17 08:18:11 2009

LOGSTDBY: APPLY_SET: RECORD_APPLIED_DDL changed to TRUE

Wed Jun 17 08:18:43 2009

LOGSTDBY: APPLY_SET: EVENT_LOG_DEST changed to DEST_ALL

Wed Jun 17 08:18:43 2009

LOGSTDBY status: EVENT_LOG_DEST changed to DEST_ALL

Wed Jun 17 08:19:17 2009

LOGSTDBY stmt: create table test as select * from dept

LOGSTDBY status: ORA-16204: DDL successfully applied

This week, I was teaching an 11g Data Guard course in Duesseldorf – my home location. One question that arose in that course was, whether it is possible to run a Logical Standby Database not beeing in archive mode.

Brief explanation: Unlike Physical Standby Databases, the Logical Standby is a quite independent Database that tries to do the same SQL Statements that arose at the Primary to keep up to date with it. In order to be able to do so, it gets the redo protocol transmitted from the primary and analyzes it (with Logminer technique). That redo protocol is stored in Archive Logs on the Logical Standby, which can’t be turned off, of course.

But also, when the SQL Apply on the standby changes blocks, it writes its own redo protocol into its own Online Logfiles and (by default) archives them. Sometimes, this is not needed, because customers use their Logical Standby as kind of “cheap replication” and do not use it for HA purpose. So can we turn off Archive Logging on the Logical Standby? I saw that in place already at a (not reference, unfortunately) customer site, so the short answer was:  Yes, we can

I tested it then myself and it really works. We can turn off archivelogging on the Logical Standby even with usage of the Data Guard Broker and with no warnings from it. But it seems, as if that is only possible with archiver transmission from the Primary to the Logical Standby. Otherwise (with LogXptMode async or sync), the transmission to the Logical Standby stops.

Being in Norway this week, I have again the pleasure to teach an 11g Data Guard course. I always try to get a hotel close to the education center to be able to walk over there, but I didn’t get the right direction on the first day and had to use a taxi instead. Fortunately, the Norwegians are friendly and helpful and like to explain to a stranger where to find the next taxi stand

One of the student asked how we can handle the situation, if we have a Logical Standby Database and want to add datafiles to the primary, while the Logical Standby DB uses an other directory structure than the Primary. Putting the parameter standby_file_management to auto and also setting db_file_name_convert appropriately is not sufficient in this case, as it is for a Physical Standby Database. Well, I searched through the documentation and found the following remedy:

Connect to the Logical Standby DB as sys and then

create or replace procedure sys.handle_tbs_ddl
(
  old_stmt  in  varchar2,
  stmt_typ  in  varchar2,
  schema    in  varchar2,
  name      in  varchar2,
  xidusn    in  number,
  xidslt    in  number,
  xidsqn    in  number,
  action    out number,
  new_stmt  out varchar2
) as
begin
  new_stmt := replace(old_stmt,
                     '/home/oracle/prima',
                     '/home/oracle/logst');

  action := dbms_logstdby.skip_action_replace;

exception
  when others then
    action := dbms_logstdby.skip_action_error;
    new_stmt := null;
end;
/

--------------------------------------------------------------------

-- LSP has to be stopped before we can do the next step

--------------------------------------------------------------------

alter database stop logical standby apply;

begin
dbms_logstdby.skip (stmt=>'tablespace',proc_name=>'sys.handle_tbs_ddl');
end;
/

alter database start logical standby apply immediate;

You can now create Tablespaces and/or add Datafiles on the Primary and they are automatically created on the Logical Standby as well.

You could also take an reactive approach and let the create tablespace action on the Primary crash the SQl Apply on the Logical Standby and then skip that DDL and create the tablespace manually on the Logical SB. But this approach above seems to be more elegant and leaves you with less administrative tasks to do. Thanks to Cecilie Sander for asking that question!

This week, I am in Kista (Sweden, near Stockholm) teaching an 11g Data Guard course. Boy, I love my job! Not only does it give me the opportunity to talk to intelligent, open minded people, I can also travel through the world and visit interesting countries  -not to mention, that I can make a living from it

One of the students asked, whether a resize of a datafile on the primary database would also be reproduced at the standby database – and then simply tested it live to see that it is actually done. That is the good thing about having courses with practices: You can simply try out things and don’t have to speculate about it.

Thanks to Niklas Isaksson for pointing out, that a resize of datafiles on the primary is reproduced on the standby! To be honest, I wasn’t sure about that, but now I am