Informix HADR - Add new Database
A user on IDS 11.5 (HP-UX) with HDR set up couldn't enable transaction logging on a new database via ontape/ondblog, getting ISAM error 188 ("Cannot add transaction logging on a DR primary with DR on"). Replies explained the restriction exists because unlogged changes never reach the secondary, so the database must be created logged from the start (CREATE DATABASE ... WITH LOG). For the follow-up worry about loading 10GB into a logged database with only 2GB of logs, suggestions were to use a loader that commits periodically (HPL, SQLCMD, Art Kagel's utilities) and to keep logical logs backed up continuously or discarded during the load; tables under 2GB should be fine.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello All,
I have IDS 11.5 on Hpunix, HADR has been setup. I want to add a new database
with unbuffered logging, whenever i try to use ontape or ondblog commands i
get the below error - Error changing logging status - 'test'. iserrno 188,
which means ISAM error: Cannot add transaction logging on a DR primary with DR
on.
Is there a way i can add a database without bringing HADR down, if not can you
please give me the steps to be run on primary/secondary server.
I think we need to make it to standard mode and create the database and then
again set it back.
Please advise.
Regards,
lloyd
Lloyd-
Create the database with logging already on: CREATE DATABASE
test WITH LOG. As the message says, you can't change the logging status
of an existing database without reinitializing your HADR.
--EEM
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Lloyd S
> Sent: Wednesday, August 12, 2009 11:03 AM
> To: ids@iiug.org
> Subject: Informix HADR - Add new Database [16664]
>
> Hello All,
>
> I have IDS 11.5 on Hpunix, HADR has been setup. I want to add a new
database
> with unbuffered logging, whenever i try to use ontape or ondblog
commands i
> get the below error - Error changing logging status - 'test'. iserrno
188,
> which means ISAM error: Cannot add transaction logging on a DR primary
with DR
> on.
> Is there a way i can add a database without bringing HADR down, if not
can you
> please give me the steps to be run on primary/secondary server.
> I think we need to make it to standard mode and create the database
and then
> again set it back.
> Please advise.
>
> Regards,
> lloyd
>
>
>
************************************************************************
******
> *
> Forum Note: Use "Reply" to post a response in the discussion forum.
Sorry to be pedantic, but DB2 has HADR. IDS has HDR.
The question I have for you is why did you not create the database with
logging in the first place? eg
Create database <new database name> with logging
Lloyd S wrote:
> Hello All,
>
> I have IDS 11.5 on Hpunix, HADR has been setup. I want to add a new database
> with unbuffered logging, whenever i try to use ontape or ondblog commands i
> get the below error - Error changing logging status - 'test'. iserrno 188,
> which means ISAM error: Cannot add transaction logging on a DR primary with
DR
> on.
> Is there a way i can add a database without bringing HADR down, if not can
you
> please give me the steps to be run on primary/secondary server.
> I think we need to make it to standard mode and create the database and then
> again set it back.
> Please advise.
>
> Regards,
> lloyd
Sorry to be pedantic, but DB2 has HADR. IDS has HDR (HADR being several years
behind HDR in terms of functionality).
The question I have for you is why did you not create the database with
logging in the first place? eg
create database <new database name> with log;
That would have done the trick for you quite happily.
This said there's a very good reason why the engine doesn't let you change
logging mode with HDR active: since the database is not logged, transactions
are not passed to the secondary and therefore the secondary is not consistent
with the primary. If you were allowed to turn logging on, the logical log
records pertaining to that database might be related to rows that had been
changed on the primary only - the net result would be that the secondary would
be unable to apply said log records, and spend it's time AF'ing.
The only clean way to do it is the way the engine is asking you to.
In a similar vein, even without hdr, if you turn on logging on a database, you
have to take an archive before you are allowed to write to it. If this
requirement wasn't enforced, you restored from a previous archive, and tried
to rollforward the logs, you'd get rollforward failures, which wouldn't leave
your database in a nice shape.
Have you considered reading the manual?
--
Ciao,
Marco
______________________________________________________________________________
Marco Greco /UK /IBM Standard disclaimers apply!
Structured Query Scripting Language http://www.4glworks.com/sqsl.htm
4glworks http://www.4glworks.com
Informix on Linux http://www.4glworks.com/ifmxlinux.htm
I am going to used dbexport/onunload copy to create the database, now if give
the -l option it will fill up the logical logs and go into long transaction. I
have a 10gb database and a 2gb logspace, will that hold without any errors.
On Wed, 12 Aug 2009 21:46:09 +0530 wrote
>Sorry to be pedantic, but DB2 has HADR. IDS has HDR.
The question I have for you is why did you not create the database with
logging in the first place? eg
Create database with logging
Lloyd S wrote:
> Hello All,
>
> I have IDS 11.5 on Hpunix, HADR has been setup. I want to add a new database
> with unbuffered logging, whenever i try to use ontape or ondblog commands i
> get the below error - Error changing logging status - 'test'. iserrno 188,
> which means ISAM error: Cannot add transaction logging on a DR primary with
DR
> on.
> Is there a way i can add a database without bringing HADR down, if not can
you
> please give me the steps to be run on primary/secondary server.
> I think we need to make it to standard mode and create the database and then
> again set it back.
> Please advise.
>
> Regards,
> lloyd
Sorry to be pedantic, but DB2 has HADR. IDS has HDR (HADR being several years
behind HDR in terms of functionality).
The question I have for you is why did you not create the database with
logging in the first place? eg
create database with log;
That would have done the trick for you quite happily.
This said there's a very good reason why the engine doesn't let you change
logging mode with HDR active: since the database is not logged, transactions
are not passed to the secondary and therefore the secondary is not consistent
with the primary. If you were allowed to turn logging on, the logical log
records pertaining to that database might be related to rows that had been
changed on the primary only - the net result would be that the secondary would
be unable to apply said log records, and spend it's time AF'ing.
The only clean way to do it is the way the engine is asking you to.
In a similar vein, even without hdr, if you turn on logging on a database, you
have to take an archive before you are allowed to write to it. If this
requirement wasn't enforced, you restored from a previous archive, and tried
to rollforward the logs, you'd get rollforward failures, which wouldn't leave
your database in a nice shape.
Have you considered reading the manual?
--
Ciao,
Marco
______________________________________________________________________________
Marco Greco /UK /IBM Standard disclaimers apply!
Structured Query Scripting Language http://www.4glworks.com/sqsl.htm
4glworks http://www.4glworks.com
Informix on Linux http://www.4glworks.com/ifmxlinux.htm
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Hello Everett,
I want to use dbexport /onunload copy and create a new database,
initially i will create it without the -l option, since it will eat up
logical logs and cause long transaction. If i have a 10gb databse to
restore using onload/dbexport, what would be the minimum logical log
required so it does not go into long transaction, I have a 2gb logical
logdbs.
Regards,
Lloyd
I see. So what you'll probably want to do is use a tool that commits
the inserts every few hundred rows. I know HPL has that, and I'm pretty
sure that SQLCMD has that. Art's myimport may as well. SQLCMD and
Art's utils2_ak are both available free from the IIUG website.
--EEM
On Wed, 12 Aug 2009 21:39:36 +0530 wrote
>Lloyd-
Create the database with logging already on: CREATE DATABASE
test WITH LOG. As the message says, you can't change the logging status
of an existing database without reinitializing your HADR.
--EEM
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Lloyd S
> Sent: Wednesday, August 12, 2009 11:03 AM
> To: ids@iiug.org
> Subject: Informix HADR - Add new Database [16664]
>
> Hello All,
>
> I have IDS 11.5 on Hpunix, HADR has been setup. I want to add a new
database
> with unbuffered logging, whenever i try to use ontape or ondblog
commands i
> get the below error - Error changing logging status - 'test'. iserrno
188,
> which means ISAM error: Cannot add transaction logging on a DR primary
with DR
> on.
> Is there a way i can add a database without bringing HADR down, if not
can you
> please give me the steps to be run on primary/secondary server.
> I think we need to make it to standard mode and create the database
and then
> again set it back.
> Please advise.
>
> Regards,
> lloyd
>
>
>
************************************************************************
******
> *
> Forum Note: Use "Reply" to post a response in the discussion forum.
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
IBM Informix Dynamic Server Administrator's Guide
Planning for HDR
Before you start setting up computers and database servers to use HDR, you
might want to do some initial planning. The following list contains planning
tasks to perform:
* Choose and acquire appropriate hardware.
* If you are using more than one database server to store data that you want
to replicate, migrate and redistribute this data so that it can be managed by
a single database server.
* Ensure that all databases you want to replicate use transaction logging. To
turn on transaction logging, see Managing the Database-Logging Mode.
* Develop client applications to make use of both database servers in the
replication pair. For a discussion of design considerations, refer to
Redirection and Connectivity for Data-Replication Clients and Designing
Clients to Use the Secondary Database Server.
* Create a schedule for starting HDR for the first time.
* Design a storage-space and logical-log backup schedule for the primary
database server.
* Produce a plan for how to handle failures of either database server and how
to restart HDR after a failure. Read Redirection and Connectivity for
Data-Replication Clients.
http://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp?topic=/com.ibm.s
qls.doc/sqls943.htm
________________________________________
De: ids-bounces@iiug.org [ids-bounces@iiug.org] En nombre de Everett Mills
[Everett.Mills@nationalbeef.com]
Enviado el: miércoles, 12 de agosto de 2009 11:23 a.m.
Para: ids@iiug.org
Asunto: FW: RE: Informix HADR - Add new Database [16668]
Hello Everett,
I want to use dbexport /onunload copy and create a new database,
initially i will create it without the -l option, since it will eat up
logical logs and cause long transaction. If i have a 10gb databse to
restore using onload/dbexport, what would be the minimum logical log
required so it does not go into long transaction, I have a 2gb logical
logdbs.
Regards,
Lloyd
I see. So what you'll probably want to do is use a tool that commits
the inserts every few hundred rows. I know HPL has that, and I'm pretty
sure that SQLCMD has that. Art's myimport may as well. SQLCMD and
Art's utils2_ak are both available free from the IIUG website.
--EEM
On Wed, 12 Aug 2009 21:39:36 +0530 wrote
>Lloyd-
Create the database with logging already on: CREATE DATABASE
test WITH LOG. As the message says, you can't change the logging status
of an existing database without reinitializing your HADR.
--EEM
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Lloyd S
> Sent: Wednesday, August 12, 2009 11:03 AM
> To: ids@iiug.org
> Subject: Informix HADR - Add new Database [16664]
>
> Hello All,
>
> I have IDS 11.5 on Hpunix, HADR has been setup. I want to add a new
database
> with unbuffered logging, whenever i try to use ontape or ondblog
commands i
> get the below error - Error changing logging status - 'test'. iserrno
188,
> which means ISAM error: Cannot add transaction logging on a DR primary
with DR
> on.
> Is there a way i can add a database without bringing HADR down, if not
can you
> please give me the steps to be run on primary/secondary server.
> I think we need to make it to standard mode and create the database
and then
> again set it back.
> Please advise.
>
> Regards,
> lloyd
>
>
>
************************************************************************
******
> *
> Forum Note: Use "Reply" to post a response in the discussion forum.
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
The secondary is kept in sync via log replay. If an operation doesn't
generate log records, then secondary will not see it.
Its not a good idea to use non-logged objects when you have HDR.
thanx
Prasad
From:
"Everett Mills" <Everett.Mills@nationalbeef.com>
To:
ids@iiug.org
Date:
08/12/2009 09:24 AM
Subject:
FW: RE: Informix HADR - Add new Database [16668]
Sent by:
ids-bounces@iiug.org
Hello Everett,
I want to use dbexport /onunload copy and create a new database,
initially i will create it without the -l option, since it will eat up
logical logs and cause long transaction. If i have a 10gb databse to
restore using onload/dbexport, what would be the minimum logical log
required so it does not go into long transaction, I have a 2gb logical
logdbs.
Regards,
Lloyd
I see. So what you'll probably want to do is use a tool that commits
the inserts every few hundred rows. I know HPL has that, and I'm pretty
sure that SQLCMD has that. Art's myimport may as well. SQLCMD and
Art's utils2_ak are both available free from the IIUG website.
--EEM
On Wed, 12 Aug 2009 21:39:36 +0530 wrote
>Lloyd-
Create the database with logging already on: CREATE DATABASE
test WITH LOG. As the message says, you can't change the logging status
of an existing database without reinitializing your HADR.
--EEM
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Lloyd S
> Sent: Wednesday, August 12, 2009 11:03 AM
> To: ids@iiug.org
> Subject: Informix HADR - Add new Database [16664]
>
> Hello All,
>
> I have IDS 11.5 on Hpunix, HADR has been setup. I want to add a new
database
> with unbuffered logging, whenever i try to use ontape or ondblog
commands i
> get the below error - Error changing logging status - 'test'. iserrno
188,
> which means ISAM error: Cannot add transaction logging on a DR primary
with DR
> on.
> Is there a way i can add a database without bringing HADR down, if not
can you
> please give me the steps to be run on primary/secondary server.
> I think we need to make it to standard mode and create the database
and then
> again set it back.
> Please advise.
>
> Regards,
> lloyd
>
>
>
************************************************************************
******
> *
> Forum Note: Use "Reply" to post a response in the discussion forum.
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Are any of your tables larger than 2GB? If not, then you should be OK if
you are discarding the logical logs during the load or have them backing up
continuously as they fill either using ontape -c or through the
ALARMPROGRAM.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Wed, Aug 12, 2009 at 12:22 PM, Lloyd S <lloyd_s@rediffmail.com> wrote:
> I am going to used dbexport/onunload copy to create the database, now if
> give
> the -l option it will fill up the logical logs and go into long
> transaction. I
> have a 10gb database and a 2gb logspace, will that hold without any errors.
>
> On Wed, 12 Aug 2009 21:46:09 +0530 wrote
> >Sorry to be pedantic, but DB2 has HADR. IDS has HDR.
>
> The question I have for you is why did you not create the database with
> logging in the first place? eg
>
> Create database with logging>
> Lloyd S wrote:
> > Hello All,
> >
> > I have IDS 11.5 on Hpunix, HADR has been setup. I want to add a new
> database
> > with unbuffered logging, whenever i try to use ontape or ondblog commands
> i
> > get the below error - Error changing logging status - 'test'. iserrno
> 188,
> > which means ISAM error: Cannot add transaction logging on a DR primary
> with
> DR
> > on.
> > Is there a way i can add a database without bringing HADR down, if not
> can
> you
> > please give me the steps to be run on primary/secondary server.
> > I think we need to make it to standard mode and create the database and
> then
> > again set it back.
> > Please advise.
> >
> > Regards,
> > lloyd
>
> Sorry to be pedantic, but DB2 has HADR. IDS has HDR (HADR being several
> years
> behind HDR in terms of functionality).
>
> The question I have for you is why did you not create the database with
> logging in the first place? eg
>
> create database with log;>
> That would have done the trick for you quite happily.
> This said there's a very good reason why the engine doesn't let you change
> logging mode with HDR active: since the database is not logged,
> transactions
> are not passed to the secondary and therefore the secondary is not
> consistent
> with the primary. If you were allowed to turn logging on, the logical log
> records pertaining to that database might be related to rows that had been
> changed on the primary only - the net result would be that the secondary
> would
> be unable to apply said log records, and spend it's time AF'ing.
> The only clean way to do it is the way the engine is asking you to.
>
> In a similar vein, even without hdr, if you turn on logging on a database,
> you
> have to take an archive before you are allowed to write to it. If this
> requirement wasn't enforced, you restored from a previous archive, and
> tried
> to rollforward the logs, you'd get rollforward failures, which wouldn't
> leave
> your database in a nice shape.
>
> Have you considered reading the manual?
>
> --
> Ciao,
> Marco
>
>
______________________________________________________________________________
> Marco Greco /UK /IBM Standard disclaimers apply!
>
> Structured Query Scripting Language http://www.4glworks.com/sqsl.htm
> 4glworks http://www.4glworks.com
> Informix on Linux http://www.4glworks.com/ifmxlinux.htm
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0023545bcfe080ae050471065554