SQLcode = 1008 When Executing RMAN Commands in 11.2.0.2

Jun 29, 2012 / By Vladimir Naumovski

Tags: , ,

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:

http://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.

7 Responses to “SQLcode = 1008 When Executing RMAN Commands in 11.2.0.2”

  • Bernie Ramirez says:

    Thanks , I got identical problem on a AIX 11G (11.2.3) and the ” alter system flush shared_pool;” fixed it.

  • balaji chandrasekaran says:

    Infact flushing the shared pool also did failed with same error

    • balaji chandrasekaran says:

      sql statement: alter system flush shared_pool

      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 18-MAR-14

      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-00571: ===========================================================
      RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
      RMAN-00571: ===========================================================
      RMAN-03002: failure of backup command at 03/18/2014 15:00:22
      RMAN-03014: implicit resync of recovery catalog failed
      ORA-01008: not all variables bound

      • Vladimir Naumovski says:

        Did you run the sql command through the RMAN client or you run it directly on the database through sqlplus? In case you run it through the RMAN client please try and run it through sqlplus client.

        Cheers,
        Vladimir

  • Mushtaq says:

    I came across same error and flushed the shared pool. The issue is resolved. Thanks for sharing.

  • Antonius Momac says:

    HOLLY MOLLY!!!

    Prefect solution. Thank you for sharing!!!! you saved me so much time.

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>