RMAN recover table fails with error:
run
{
allocate channel t1 type sbt_tape FORMAT '%U' PARMS='ENV=(NB_ORA_CLIENT=..., NB_ORA_POLICY=..., NB_ORA_SCHED=...)';
allocate channel t2 type sbt_tape FORMAT '%U' PARMS='ENV=(NB_ORA_CLIENT=..., NB_ORA_POLICY=..., NB_ORA_SCHED=...)';
recover table ....;
}
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/21/2016 16:53:37
RMAN-03015: error occurred in stored script Memory Script
RMAN-06034: at least 1 channel must be allocated to execute this command
CHANGES
CAUSE
Bug 17080042 RMAN-6034 Point in Time PDB or Table Recovery Errors with RMAN Manual Channels - closed as not a bug
The table recovery fails when channels are allocated manually within a run block which is the same as reported within bug 17080042, thus justifying cause.
SOLUTION
You cannot manually allocate a channel when RMAN will implicitly create the auxiliary instance, the channel specification must be configured in the target database. This is done with:
configure channel device type sbt_tape FORMAT '%U' PARMS='ENV=(NB_ORA_CLIENT=......, NB_ORA_POLICY=............., NB_ORA_SCHED=.....)';
To allocate multiple channels, use the following:
CONFIGURE DEVICE TYPE sbt PARALLELISM ....
-----------------------PDB PITR恢复
The issue is caused because Manual channels have been allocated for PDB PITR .
PDB PITR creates creates auxiliary instance automatically which does the Point in time recovery using automatic channels based on the configuration information in Rman.
run {
allocate channel t1 type 'sbt_tape';
allocate channel t2 type 'sbt_tape';
set until time "to_date('JAN 12 2017 03:00:00','MON DD YYYY HH24:MI:SS')";
restore pluggable database <pdb_name>;
recover pluggable database <pdb_name>;
alter pluggable database <pdb_name> open resetlogs; }
SOLUTION
You cannot manually allocate a channel when RMAN will implicitly create the auxiliary instance.
Solution in this case :-
Disk backup
-----------------
If the backups are available on disk and your disk channels have been configured as default you can run the below
run { set until time "to_date('JAN 12 2017 03:00:00','MON DD YYYY HH24:MI:SS')";
restore pluggable database <pdb_name>;
recover pluggable database <pdb_name>;
alter pluggable database <pdb_name> open resetlogs; }
Sbt backup
--------------------
If the backups are on Tape and you have default channel in Rman configuration set to disk then change it temporarily to sbt
Rman> Connect target /
Rman>show device type;
Rman> CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' send 'NB_ORA_POLICY................<as per your configuration>.....' ;
Re-run the PDB PITR without any channels
run { set until time "to_date('JAN 12 2017 03:00:00','MON DD YYYY HH24:MI:SS')";
restore pluggable database <pdb_name>;
recover pluggable database <pdb_name>;
alter pluggable database <pdb_name> open resetlogs; }
----------------
SYMPTOMS
For the purposes of this document, the following fictitious environment is used as an example to describe the procedure:
Tablespace Name: TEST3
Database Name: DB11G
Directory Names: /share/tspitr/, /usr/oracle/,/u01/oradata and all sub-directories
************
Experienced the following error when running RMAN tablespace point-in-time recovery (TSPITR):
RMAN-06034: at least 1 channel must be allocated to execute this command
CAUSE
If an RMAN channel is explicitly allocated, we experienced the RMAN-06034 error, as shown in the example below.
$ rman target / << EOF
> set echo on
> list backup of tablespace TEST3;
> run {
allocate channel d1 type disk;
recover tablespace "TEST3" until time "to_date('08-NOV-2007 15:35:16','DD-MON-RRRR HH24:MI:SS')"
auxiliary destination '/share/tspitr';
> }
> exit
> EOF
Recovery Manager: Release 11.1.0.6.0 - Production on Thu Nov 8 15:46:44 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: DB11G (DBID=102990784)
RMAN>
echo set on
RMAN> list backup of tablespace TEST3;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
671 Full 56.00K DISK 00:00:18 08-NOV-2007 14:47:43
BP Key: 731 Status: AVAILABLE Compressed: NO Tag: TAG20071108T144724
Piece Name: /usr/oracle/flash_recovery_area/DB11G/backupset/2007_11_08/o1_mf_nnndf_TAG20071108T144724_3m6syzkh_.bkp
List of Datafiles in backup set 671
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- -------------------- ----
10 Full 13311441 08-NOV-2007 14:47:43
/usr/oracle/oradata/DB11G/datafile/o1_mf_test3_3m6sx0oz_.dbf
RMAN> run {
2> allocate channel d1 type disk;
3> recover tablespace "TEST3" until time "to_date('08-NOV-2007 15:35:16','DD-MON-RRRR HH24:MI:SS')"
4> auxiliary destination '/share/tspitr';
5> }
allocated channel: d1
channel d1: SID=67 device type=DISK
Starting recover at 08-NOV-2007 15:46:50
RMAN-05026: WARNING: presuming following set of tablespaces applies to
specified point in time
List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1
Creating automatic instance, with SID='bdAj'
initialization parameters used for automatic instance:
db_name=DB11G
compatible=11.1.0.0.0
db_block_size=8192
db_files=200
db_unique_name=tspitr_DB11G_bdAj
large_pool_size=1M
shared_pool_size=110M
processes=50
#No auxiliary parameter file used
db_create_file_dest=/share/tspitr
control_files=/share/tspitr/cntrl_tspitr_DB11G_bdAj.f
starting up automatic instance DB11G
Oracle instance started
Total System Global Area 200876032 bytes
Fixed Size 1298836 bytes
Variable Size 146804332 bytes
Database Buffers 50331648 bytes
Redo Buffers 2441216 bytes
Automatic instance created
contents of Memory Script:
{
# set the until clause
set until time "to_date('08-NOV-2007 15:35:16','DD-MON-RRRR HH24:MI:SS')";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log for tspitr to a resent until time
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script
executing command: SET until clause
Starting restore at 08-NOV-2007 15:46:57
Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed
released channel: d1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/08/2007 15:46:59
RMAN-03015: error occurred in stored script Memory Script
RMAN-06034: at least 1 channel must be allocated to execute this command
SOLUTION
Run the TSPITR command via RMAN command line without allocating an RMAN channel, as shown in the example below.
Recovery Manager10.2.0.3.0
RMAN>
echo set on
RMAN> connect target;
connected to target database: DB11G (DBID=3954742461)
RMAN> connect catalog *
connected to recovery catalog database
RMAN> recover tablespace users until time "to_date('26-oct-2007 11:10:00','dd-mon-yyyy hh24:mi:ss')" auxiliary destination '/u01/oradata/DB11G/aux';
Starting recover at 05-nov-2007 09:36:35
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=147 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.3.3.0
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=139 devtype=DISK
Creating automatic instance, with SID='ymyE'
initialization parameters used for automatic instance:
db_name=DB11G
compatible=10.2.0.3.0
db_block_size=8192
db_files=200
db_unique_name=tspitr_DB11G_ymyE
large_pool_size=1M
shared_pool_size=110M
#No auxiliary parameter file used
db_create_file_dest=/u01/oradata/DB11G/aux
control_files=/u01/oradata/DB11G/aux/cntrl_tspitr_DB11G_ymyE.f
starting up automatic instance DB11G
Oracle instance started
Total System Global Area 205520896 bytes
Fixed Size 2071648 bytes
Variable Size 146801568 bytes
Database Buffers 50331648 bytes
Redo Buffers 6316032 bytes
Automatic instance created
contents of Memory Script:
{
# set the until clause
set until time "to_date('26-oct-2007 11:10:00','dd-mon-yyyy hh24:mi:ss')";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log for tspitr to a resent until time
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
# resync catalog after controlfile restore
resync catalog;
}
executing Memory Script
executing command: SET until clause
Starting restore at 05-nov-2007 09:36:37
allocated channel: ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: sid=37 devtype=SBT_TAPE
channel ORA_AUX_SBT_TAPE_1: Data Protection for Oracle: version 5.3.3.0
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=36 devtype=DISK
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/oradata/DB11G/ctl_DB11Gc-3954742461-20071026-03
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/oradata/DB11G/ctl_DB11Gc-3954742461-20071026-03 tag=TAG20071026T110941
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output filename=/u01/oradata/DB11G/aux/cntrl_tspitr_DB11G_ymyE.f
Finished restore at 05-nov-2007 09:36:11
sql statement: alter database mount clone database
sql statement: alter system archive log current
sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
starting full resync of recovery catalog
full resync complete
released channel: ORA_SBT_TAPE_1
released channel: ORA_AUX_SBT_TAPE_1
released channel: ORA_DISK_1
released channel: ORA_AUX_DISK_1
contents of Memory Script:
{
# generated tablespace point-in-time recovery script
# set the until clause
set until time "to_date('26-oct-2007 11:10:00','dd-mon-yyyy hh24:mi:ss')";
plsql <<<-- tspitr_2
declare
sqlstatement varchar2(512);
offline_not_needed exception;
pragma exception_init(offline_not_needed, -01539);
begin
sqlstatement := 'alter tablespace '|| 'USERS' ||' offline for recover';
krmicd.writeMsg(6162, sqlstatement);
krmicd.execSql(sqlstatement);
exception
when offline_not_needed then
null;
end; >>>;
# set an omf destination filename for restore
set newname for clone datafile 1 to new;
# set an omf destination filename for restore
set newname for clone datafile 2 to new;
# set an omf destination tempfile
set newname for clone tempfile 1 to new;
# set a destination filename for restore
set newname for datafile 4 to
"/u01/oradata/DB11G/users01.dbf";
# rename all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile 1, 2, 4;
switch clone datafile all;
#online the datafiles restored or flipped
sql clone "alter database datafile 1 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 2 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 4 online";
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace "USERS", "SYSTEM", "UNDOTBS1" delete arc
hivelog;
alter clone database open resetlogs;
# PLUG HERE the creation of a temporary tablespace if export fails due to lack
# of temporary space.
# For example in Unix these two lines would do that:
#sql clone "create tablespace aux_tspitr_tmp
# datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";
}
executing Memory Script
executing command: SET until clause
sql statement: alter tablespace USERS offline for recover
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed temporary file 1 to
/u01/oradata/DB11G/aux/TSPITR_DB11G_YMYE/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 05-nov-2007 09:36:21
allocated channel: ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: sid=37 devtype=SBT_TAPE
channel ORA_AUX_SBT_TAPE_1: Data Protection for Oracle: version 5.3.3.0
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=39 devtype=DISK
channel ORA_AUX_SBT_TAPE_1: starting datafile backupset restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/oradata/DB11G/aux/TSPITR_DB11G_YMYE/datafile/o1_mf_system_%u_.dbf
restoring datafile 00002 to /u01/oradata/DB11G/aux/TSPITR_DB11G_YMYE/datafile/o1_mf_undotbs1_%u_.dbf
restoring datafile 00004 to /u01/oradata/DB11G/users01.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece 1civesfl_1_1
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
piece handle=1civesfl_1_1 tag=TAG20071026T090557
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:02:35
Finished restore at 05-nov-2007 09:38:58
datafile 1 switched to datafile copy
input datafile copy recid=12 stamp=637839538 filename=/u01/oradata/DB11G/aux/TSPITR_DB11G_YMYE/datafile/o1_mf_system_3lyg6m56_.dbf
datafile 2 switched to datafile copy
input datafile copy recid=13 stamp=637839538 filename=/u01/oradata/DB11G/aux/TSPITR_DB11G_YMYE/datafile/o1_mf_undotbs1_3lyg6m8r_.dbf
sql statement: alter database datafile 1 online
sql statement: alter database datafile 2 online
sql statement: alter database datafile 4 online
Starting recover at 05-nov-2007 09:38:58
using channel ORA_AUX_SBT_TAPE_1
using channel ORA_AUX_DISK_1
starting media recovery
archive log thread 1 sequence 2 is already on disk as file /u01/oradata/DB11G/archiving/1_2_636973468.dbf
archive log thread 1 sequence 3 is already on disk as file /u01/oradata/DB11G/archiving/1_3_636973468.dbf
archive log thread 1 sequence 4 is already on disk as file /u01/oradata/DB11G/archiving/1_4_636973468.dbf
archive log thread 1 sequence 5 is already on disk as file /u01/oradata/DB11G/archiving/1_5_636973468.dbf
archive log filename=/u01/oradata/DB11G/archiving/1_2_636973468.dbf thread=1 sequence=2
archive log filename=/u01/oradata/DB11G/archiving/1_3_636973468.dbf thread=1 sequence=3
archive log filename=/u01/oradata/DB11G/archiving/1_4_636973468.dbf thread=1 sequence=4
archive log filename=/u01/oradata/DB11G/archiving/1_5_636973468.dbf thread=1 sequence=5
media recovery complete, elapsed time: 00:00:04
Finished recover at 05-nov-2007 09:39:03
database opened
contents of Memory Script:
{
# export the tablespaces in the recovery set
host 'exp userid =\"/@\(DESCRIPTION=\(ADDRESS=\(PROTOCOL=beq\)\(PROGRAM=
/u01/app/oracle/product/10.2.0/db_1/bin/oracle\)\(ARGV0=oracleymyE\)\(ARGS=
^'\(DESCRIPTION=\(LOCAL=YES\)\(ADDRESS=\(PROTOCOL=beq\)\)\)^'\)\(ENVS=
^'ORACLE_SID=ymyE^'\)\)\(CONNECT_DATA=\(SID=ymyE\)\)\) as sysdba\" point_in_time_recover=y tablespaces=USERS file=tspitr_a.dmp';
# shutdown clone before import
shutdown clone immediate
# import the tablespaces in the recovery set
host 'imp userid =\"/@ as sysdba\" point_in_time_recover=y file=
tspitr_a.dmp';
# online/offline the tablespace imported
sql "alter tablespace USERS online";
sql "alter tablespace USERS offline";
# enable autobackups in case user does open resetlogs from RMAN after TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
# resync catalog after tspitr finished
resync catalog;
}
executing Memory Script
host command complete
database closed
database dismounted
Oracle instance shut down
host command complete
sql statement: alter tablespace USERS online
sql statement: alter tablespace USERS offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
starting full resync of recovery catalog
full resync complete
Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/oradata/DB11G/aux/cntrl_tspitr_DB11G_ymyE.f
deleted
auxiliary instance file /u01/oradata/DB11G/aux/TSPITR_DB11G_YMYE/datafile/o1_mf_system_3lyg6m56_.dbf deleted
auxiliary instance file /u01/oradata/DB11G/aux/TSPITR_DB11G_YMYE/datafile/o1_mf_undotbs1_3lyg6m8r_.dbf deleted
auxiliary instance file /u01/oradata/DB11G/aux/TSPITR_DB11G_YMYE/datafile/o1_mf_temp_3lyg8r80_.tmp deleted
auxiliary instance file /u01/oradata/DB11G/aux/TSPITR_DB11G_YMYE/onlinelog/o1_mf_1_3lyg8qkk_.log deleted
auxiliary instance file /u01/oradata/DB11G/aux/TSPITR_DB11G_YMYE/onlinelog/o1_mf_2_3lyg8qpr_.log deleted
auxiliary instance file /u01/oradata/DB11G/aux/TSPITR_DB11G_YMYE/onlinelog/o1_mf_3_3lyg8qw1_.log deleted
Finished recover at 05-nov-2007 09:39:3