Pythian Blog: Technical Track

SQLcode = 1008 When Executing RMAN Commands in 11.2.0.2

by Vladimir Naumovski on Jun 29, 2012 12:00:00 AM

A while ago, I was paged by a client having backup job failure. Doing my routine as usual, I started the investigation by looking at the job execution log. Connected to the box was a strange output: . . . 
########################### 
 # Performing backup # 
 ###########################
 
 Recovery Manager: Release 11.2.0.2.0 - Production on Sat May 19 10:17:01 2012
 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
 connected to target database: TESTDB (DBID=xxxxxxxx)
  connected to recovery catalog database
 RMAN> SET COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
  2> run {
  3> allocate channel arch_backup_disk type disk format 'backup_location/%d_archlogs_%T%t_%U' maxpiecesize 4 G;
  4> backup as COMPRESSED BACKUPSET archivelog all not backed up;
  5>
  6> release channel arch_backup_disk;
  7>
  8> delete noprompt archivelog until time 'sysdate - 1' backed up 1 times to device type disk;
  9>
  10> }
  11>
  12>
  executing command: SET compression
 DBGSQL: TARGET> select nvl(max(al.recid), '0'),nvl(max(al.recid), 0) into :txtparmvalue, :parmvalue from v$archived_log al where al.status in ('X', 'A') and al.is_recovery_dest_file = 'YES' and al.creator = 'RMAN'
  DBGSQL: sqlcode = 1008
 DBGSQL: TARGET> select nvl(max(al.recid), '0'),nvl(max(al.recid), 0) into :txtparmvalue, :parmvalue from v$archived_log al where al.status in ('X', 'A') and al.is_recovery_dest_file = 'YES' and al.creator = 'RMAN'
  DBGSQL: sqlcode = 1008
 DBGSQL: TARGET> select nvl(max(al.recid), '0'),nvl(max(al.recid), 0) into :txtparmvalue, :parmvalue from v$archived_log al where al.status in ('X', 'A') and al.is_recovery_dest_file = 'YES' and al.creator = 'RMAN'
  DBGSQL: sqlcode = 1008
  allocated channel: arch_backup_disk
  channel arch_backup_disk: SID=2291 instance=nyiprd11 device type=DISK
 DBGSQL: TARGET> select nvl(max(al.recid), '0'),nvl(max(al.recid), 0) into :txtparmvalue, :parmvalue from v$archived_log al where al.status in ('X', 'A') and al.is_recovery_dest_file = 'YES' and al.creator = 'RMAN'
  DBGSQL: sqlcode = 1008
 Starting backup at 19-MAY-2012 10:17:03
  current log archived
 DBGSQL: TARGET> select nvl(max(al.recid), '0'),nvl(max(al.recid), 0) into :txtparmvalue, :parmvalue from v$archived_log al where al.status in ('X', 'A') and al.is_recovery_dest_file = 'YES' and al.creator = 'RMAN'
  DBGSQL: sqlcode = 1008
  released channel: arch_backup_disk
  RMAN-00571: ===========================================================
  RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
  RMAN-00571: ===========================================================
  RMAN-03002: failure of backup command at 05/19/2012 10:17:09
  RMAN-03014: implicit resync of recovery catalog failed
  ORA-01008: not all variables bound
. . . . The first thing that came to mind was that the backup script might have undergone some recent changes and there could be some syntax problems. When I reviewed the script, no changes had been made and all commands seemed perfectly normal. I compared the previous job executions as well, but all were successfully finished until the last execution. I connected to RMAN and tried executing the simple "show all" command. Strangely, I got exactly the same output as the backup script failure: . . . 
RMAN> show all;
 DBGSQL: TARGET> select nvl(max(al.recid), '0'),nvl(max(al.recid), 0) into :txtparmvalue, :parmvalue from v$archived_log al where al.status in ('X', 'A') and al.is_recovery_dest_file = 'YES' and al.creator = 'RMAN'
  DBGSQL: sqlcode = 1008
  RMAN configuration parameters for database with db_unique_name TESTDB are:
  CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;
  CONFIGURE BACKUP OPTIMIZATION OFF; # default
  CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
  CONFIGURE CONTROLFILE AUTOBACKUP ON;
  CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'backup_location/cfile/%F';
  CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
  CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
  CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
  CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'backup_location/%U' MAXPIECESIZE 4 G;
  CONFIGURE MAXSETSIZE TO UNLIMITED; # default
  CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
  CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
  CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
  CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
  CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'backup_location/snapshot_cfiles/snapcf_testdb.f';
 DBGSQL: TARGET> select nvl(max(al.recid), '0'),nvl(max(al.recid), 0) into :txtparmvalue, :parmvalue from v$archived_log al where al.status in ('X', 'A') and al.is_recovery_dest_file = 'YES' and al.creator = 'RMAN'
  DBGSQL: sqlcode = 1008
 RMAN> .
. . . I tried executing a couple of other commands, like crosscheck archivelog all and list backup summary, but I was constantly getting the same output and errors. I looked on metalink and Google as well, but the closest I could find were a couple of posts that were related enough, even though they did not have the exact output: https://www.freelists.org/post/oracle-l/RMAN-errors Rman: Ora-01008 When Connecting To Target in 11.2.0.2 [ID 1280447.1] The articles suggested that this issue might be related to Bug 10414993, which has been closed as a duplicate of (non-published) Bug# 9877980. The precondition for such an issue to take place is Upgrade/Install 11.2.0.2. That condition seemed to be met by our client, so I continued to investigate how to solve the issue. A couple of solutions were suggested: 1) Apply the patch Patch 9877980 if patches are available for the 11.2.0.2.0 release for the specific platform. 2) Flush the shared pool - in my case, shared pool is used by RMAN since Large Pool is not set. 3) Try to use a different version of RMAN client. 4) Set the cursor_sharing=exact. 5) Or simply try to solve the issue by restarting the database. Applying a patch was not considered as an option at this point since it requires scheduled downtime and too much effort for something that can be resolved with a workaround. Setting up cursor_sharing to exact is obviously not an advisable solution since it does not apply in case you explicitly need other settings to this parameter. One workaround that was excluded, and as I investigated for others helped only temporarily, was restarting the database. So, only two options were left to try: 1) Flush the shared pool. 2) Try to use a different version of RMAN client. Both options do not require any downtime, but the first option seemed the most convenient one. It is a faster approach and is not of any danger to any other processes. The shared pool will be automatically populated and, in meantime, should not dramatically affect the database performance. I simply executed the command: SQL> alter system flush shared_pool; And the problem was gone. All seemed to work properly again.

No Comments Yet

Let us know what you think

See all

See all

Subscribe by email
Follow us
Love Your Data
Pythian Logo

© 2024 All rights reserved.