DB2 online back error

[Former Member]

Hi Guys,

We are running SAP with Db2 verion 9.1 on Hp-ux.

I want to take online backup.

i tried db2 backup db DEV online to /backup_db

i got error:Online backup is not allowed because the database is not recoverable or a backup pending condition is in effect.

i found this explanation.

SQL2413N

Online backup is not allowed because the database is not recoverable or a backup pending condition is in effect.

Explanation

Online backup cannot be performed while the database is not logging for forward recovery as forward recovery will be required at restore time. Forward recovery is placed into effect by setting either the database configuration LOGARCHMETH1 or LOGARCHMETH2 and then performing an offline backup of the database.

User response

Execute an offline backup or reconfigure the database for roll-forward recovery and issue an offline backup so that subsequent online backups will be allowed.

Now i have configure for forward recovery by setting LOGARCHMETH to ON in configuration.

When i excute Db2cc, i get error the command not found.

I think control center is no longer supported with verion 9.1

Please guide how to proceed.

Regards

Abhishek

[sunny_pahuja2]

Hi,

Since you have activated the LOGARCHMETH1 parameter. So, immediately after activating this parameter, database goes to backup pending state. So, now you have to take offline backup. Once offline backup will be finished, system will come out of backup pending state. After that you can take online backup.

Thanks

Sunny

[Former Member]

Hi Sunny,

Sorry for typo error.

I have to configure the LOGARCHMETH1 to ON.

what i understand is ,in earlier version control center could be used for configuring the Db parameters.

But in ver 9.1 control center is not supported.

So please guide me how to configure DB parameter LOGARCHMET1.

regards

Abhishek

[sunny_pahuja2]

Hi,

Quite Big Typo

You can use below command to set it:

db2 update database configuration for <SID> using LOGARCHMETH1 DISK:<path to log_archive folder>

Thanks

Sunny

[Former Member]

Hi,

I was able to execute that command.

I have a confusion. I have still not changed the LOGARCHMETH1 to value ON.

Where can i check the Database parameters.

Regards

Abhishek

[sunny_pahuja2]

Hi,

You can check with command:

db2 get dbm cfg or db2 get db cfg. And check for parameter LOGARCHMETH1.

Thanks

Sunny

[Former Member]

Hi,

After executing the above command i got this output.

Database Manager Configuration

Node type = Enterprise Server Edition with local and remote clients

Database manager configuration release level = 0x0b00

CPU speed (millisec/instruction) (CPUSPEED) = 2.361721e-07

Communications bandwidth (MB/sec) (COMM_BANDWIDTH) = 1.000000e+02

Max number of concurrently active databases (NUMDB) = 8

Federated Database System Support (FEDERATED) = NO

Transaction processor monitor name (TP_MON_NAME) =

Default charge-back account (DFT_ACCOUNT_STR) =

Java Development Kit installation path (JDK_PATH) = /db2/db2dev/sqllib/ja va/jdk64

Diagnostic error capture level (DIAGLEVEL) = 3

Notify Level (NOTIFYLEVEL) = 3

Diagnostic data directory path (DIAGPATH) = /db2/DEV/db2dump

Default database monitor switches

Buffer pool (DFT_MON_BUFPOOL) = ON

Lock (DFT_MON_LOCK) = ON

Sort (DFT_MON_SORT) = ON

Statement (DFT_MON_STMT) = ON

Table (DFT_MON_TABLE) = ON

Timestamp (DFT_MON_TIMESTAMP) = ON

Unit of work (DFT_MON_UOW) = ON

Monitor health of instance and databases (HEALTH_MON) = OFF

SYSADM group name (SYSADM_GROUP) = DBDEVADM

SYSCTRL group name (SYSCTRL_GROUP) = DBDEVCTL

SYSMAINT group name (SYSMAINT_GROUP) = DBDEVMNT

SYSMON group name (SYSMON_GROUP) =

Client Userid-Password Plugin (CLNT_PW_PLUGIN) =

Client Kerberos Plugin (CLNT_KRB_PLUGIN) =

Group Plugin (GROUP_PLUGIN) =

GSS Plugin for Local Authorization (LOCAL_GSSPLUGIN) =

Server Plugin Mode (SRV_PLUGIN_MODE) = UNFENCED

Server List of GSS Plugins (SRVCON_GSSPLUGIN_LIST) =

Server Userid-Password Plugin (SRVCON_PW_PLUGIN) =

Server Connection Authentication (SRVCON_AUTH) = NOT_SPECIFIED

Database manager authentication (AUTHENTICATION) = SERVER_ENCRYPT

Cataloging allowed without authority (CATALOG_NOAUTH) = NO

Trust all clients (TRUST_ALLCLNTS) = YES

Trusted client authentication (TRUST_CLNTAUTH) = CLIENT

Bypass federated authentication (FED_NOAUTH) = NO

Default database path (DFTDBPATH) = /db2/DEV

Database monitor heap size (4KB) (MON_HEAP_SZ) = 128

Java Virtual Machine heap size (4KB) (JAVA_HEAP_SZ) = 2048

Audit buffer size (4KB) (AUDIT_BUF_SZ) = 0

Size of instance shared memory (4KB) (INSTANCE_MEMORY) = AUTOMATIC

Backup buffer default size (4KB) (BACKBUFSZ) = 1024

Restore buffer default size (4KB) (RESTBUFSZ) = 1024

Sort heap threshold (4KB) (SHEAPTHRES) = 0

Directory cache support (DIR_CACHE) = NO

Application support layer heap size (4KB) (ASLHEAPSZ) = 16

Max requester I/O block size (bytes) (RQRIOBLK) = 65000

Query heap size (4KB) (QUERY_HEAP_SZ) = 2000

Workload impact by throttled utilities(UTIL_IMPACT_LIM) = 10

Priority of agents (AGENTPRI) = SYSTEM

Max number of existing agents (MAXAGENTS) = 1024

Agent pool size (NUM_POOLAGENTS) = 10

Initial number of agents in pool (NUM_INITAGENTS) = 5

Max number of coordinating agents (MAX_COORDAGENTS) = MAXAGENTS

Max no. of concurrent coordinating agents (MAXCAGENTS) = MAX_COORDAGENTS

Max number of client connections (MAX_CONNECTIONS) = MAX_COORDAGENTS

Keep fenced process (KEEPFENCED) = NO

Number of pooled fenced processes (FENCED_POOL) = MAX_COORDAGENTS

Initial number of fenced processes (NUM_INITFENCED) = 0

Index re-creation time and redo index build (INDEXREC) = RESTART

Transaction manager database name (TM_DATABASE) = 1ST_CONN

Transaction resync interval (sec) (RESYNC_INTERVAL) = 180

SPM name (SPM_NAME) =

SPM log size (SPM_LOG_FILE_SZ) = 256

SPM resync agent limit (SPM_MAX_RESYNC) = 20

SPM log path (SPM_LOG_PATH) =

TCP/IP Service name (SVCENAME) = sapdb2DEV

Discovery mode (DISCOVER) = SEARCH

Discover server instance (DISCOVER_INST) = ENABLE

Maximum query degree of parallelism (MAX_QUERYDEGREE) = ANY

Enable intra-partition parallelism (INTRA_PARALLEL) = NO

Maximum Asynchronous TQs per query (FEDERATED_ASYNC) = 0

No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = AUTOMATIC

No. of int. communication channels (FCM_NUM_CHANNELS) = AUTOMATIC

Node connection elapse time (sec) (CONN_ELAPSE) = 10

Max number of node connection retries (MAX_CONNRETRIES) = 5

Max time difference between nodes (min) (MAX_TIME_DIFF) = 60

db2start/db2stop timeout (min) (START_STOP_TIME) = 10

I cant find parameter LOGARCHMETH1.

How to define the parameter.

Is there any other parameter that i need to define as well?

Regards

Abhishek

Edited by: abhishek sharma on Aug 26, 2011 12:06 PM

[sunny_pahuja2]

Hi,

Now, I am getting confused.

1) Have you set the parameter as per command given earlier ? If yes, is database in backup pending state ? If yes, then it means this parameter is set. You can check at the location you defined whether archive files are getting generated or not.

2) which command output you have pasted ? You can use grep command to to locate this parameter. Output of both the command shared above is different. I am not able to remember which one shows LOGARCHMETH1 but one of the command will show you this parameter value.

Thanks

Sunny

[Former Member]

Hi sunny,

First i executed this command

db2 update database configuration for DEV using LOGARCHMETH1 /backup_db

I have checked backup_db mount point and nothing is generating here.

Then i executed the db2 get dbm configuration.

the output from this command is pasted above.

I can not find the parameter LOGARCHMETH1 anywhere after executing the command( db2 get DBM configuration).

I have to do following things.

1). Check whether LOGARCHMETH1 is set to ON.

2.) execute online backup.

Regards

Abhishek

Edited by: abhishek sharma on Aug 26, 2011 2:09 PM

[sunny_pahuja2]

Hi,

Why are you setting /backup_db. SAP recommends to have path like /db2/<SID>/log_archive and make sure that owner should be db2<sid> and should have read write permission on it.

Also, you have run wrong command to set this parameter and correct command is:

db2 update database configuration for DEV using LOGARCHMETH1 DISK:/db2/DEV/log_archive.

If you did not find this parameter in db2 get dbm cfg then check with command db2 get db cfg.

Thanks

Sunny

[Former Member]

Hi Sunny,

While issuing db2 get db cgf i am getting error message.

SQL100024N: Database connection does not exit. SQLState 08003.

Regards

Abhishek

[Former Member]

Hi,

The error " Database connection does not exit. SQLState 08003" is cause to " enable text indexing timeout" is selected."enable text indexing timeout" should not be selected in the DB2 Content Manager system administration client

un-check the enable text indexing timeout check box might solve the problem.

Regards

Bhuban

[Former Member]

Hi,

I am new to DB2 environment.

Please guide how to do the above mentioned step.

My OS is HP-UX.

Regards

Abhishek

[Former Member]

Hi,

Go to the DB2 Content Manager-DB2 CommonStore -then go to enterprise- then go to the setting enable text indexing timeout-Clear the enable text indexing timeout check box.

Also see the following link. Its same as your problem..

Regards

Bhuban

[Former Member]

Hi,

I have successfully set the LOGARHMETH1= DISK:/db2/DEV/log_archive.

When i try to execute the online backup using

db2 backup db DEV online to /backup_db.

I again get the same error:Online backup is not allowed because the database is not recoverable or a backup pending condition is in effect.

i have taken an offline backup 20 days ago.

Please guide how to proceed.

Regards

Abhishek Sharma

[Former Member]

Hi,

As i guess that you cyclically overwriting your log, and for this you not able to do online backup. you have to configure logging properly and then start offline backup. After that you can do the online backup.

Chek also the archive log mode is enable or not.

For more detials please see the link below..

https://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.messages.sql....

Regards

Bhuban

[Former Member]

Hi,

I have gone through this article already and have posted the same in my 1st post.

I have executed following commands:

To check the LOGARCHMETH1

db2 get db cfg.-> shows LOGARCHMETH1= DISK:/db2/DEV/log_archive

Please guide how can i check whether LOGARCHMETH1 is set to ON or not.

Regards

Abhishek

[marc_saulnier]

Hi you need to run the following command :

1) su - db2

2) db2 get db cfg for | grep LOGRETAIN LOGRETAIN should be set to RECOVERY to allow online backup Exemple : db2p01> db2 get db cfg for p01 | grep LOGRETAIN Log retain for recovery enabled (LOGRETAIN) = RECOVERY If the LOGRETAIN is already in Recovery mode, then do a offline backup. If LOGRETAIN if not in recovery mode, then enter this command and do a offline backup after. db2 update db cfg for using LOGRETAIN ON

Hope it's help

regards

Edited by: Marc Saulnier on Aug 30, 2011 10:12 AM

[Former Member]

Hi,

I have made the changes as per your suggestions.

Is it mandatory to take offline backup?

Why is required to take offline backup after changing these parameters?

Is there a way that i can schedule an online backup without taking an offline backup.

Regards

Abhishek

[markus_doehr2]

> Is it mandatory to take offline backup?

yes

> Why is required to take offline backup after changing these parameters?

> Is there a way that i can schedule an online backup without taking an offline backup.

Read the link already posted:

https://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.messages.sql....

Markus