Problema
Tentativa de executar recover em um datafile sempre falha com o seguinte erro:
RMAN> run{
allocate channel c1 device type disk;
allocate channel t1 device type sbt PARMS 'SBT_LIBRARY=/opt/oracle/dcs/commonstore/pkgrepos/oss/odbcs/libopc.so ENV=(OPC_PFILE=/opt/oracle/dcs/commonstore/objectstore/opc_pfile/617173256/opc_PRD_gru12n.ora)';
restore datafile 37;
recover datafile 37;
}
allocated channel: c1
channel c1: SID=1596 device type=DISK
allocated channel: t1
channel t1: SID=1233 device type=SBT_TAPE
channel t1: Oracle Database Backup Service Library VER=12.2.0.2
Starting restore at 08-JUN-21
channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00037 to +DATA/prd_gru12n/datafile/teikotstbackup.311.1073570075
channel t1: reading from backup piece DBTRegular-L01623006154771cKE_df_PRD_617173256_ie00o0s6_1_1_20210606_1074529158_set31310
channel t1: piece handle=DBTRegular-L01623006154771cKE_df_PRD_617173256_ie00o0s6_1_1_20210606_1074529158_set31310 tag=DBTREGULAR-L01623006154771CKE
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:01
Finished restore at 08-JUN-21
Starting recover at 08-JUN-21
channel t1: starting incremental datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00037: +DATA/prd_gru12n/datafile/teikotstbackup.311.1074695687
channel t1: reading from backup piece DBTRegular-L11623092630393w3I_df_PRD_617173256_of00qkpa_1_1_20210607_1074615082_set31503
channel t1: piece handle=DBTRegular-L11623092630393w3I_df_PRD_617173256_of00qkpa_1_1_20210607_1074615082_set31503 tag=DBTREGULAR-L11623092630393W3I
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:01
starting media recovery
media recovery failed
released channel: c1
released channel: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/08/2021 14:34:53
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
datafile 37
ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error
ORA-00279: change 7721095385432 generated at 06/07/2021 16:11:23 needed for thread 1
ORA-00289: suggestion : +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14965.2220.1074616555
ORA-00280: change 7721095385432 for thread 1 is in sequence #14965
Mas o archivelog existe e está acessível no disco:
RMAN> list archivelog sequence 14965;
using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name PRD_GRU12N
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
424627 1 14965 A 07-JUN-21
Name: +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14965.2220.1074616555
Consultando no ASM Command Line:
$ asmcmd ls -l +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14965.2220.1074616555 Type Redund Striped Time Sys Name ARCHIVELOG UNPROT COARSE JUN 07 19:00:00 Y thread_1_seq_14965.2220.1074616555
Tentativa de recover via SQLPLUS falha com o mesmo erro:
SQL> recover datafile 37;
ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 14
ORA-00279: change 7721095385432 generated at 06/07/2021 16:11:23 needed for
thread 1
ORA-00289: suggestion :
+RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14965.2220.1074616555
ORA-00280: change 7721095385432 for thread 1 is in sequence #14965
Solução (alternativa)
Após testes e pesquisas na base de conhecimento do suporte Oracle, uma solução alternativa que funcionou, usar o comando “RECOVER AUTOMATIC DATAFILE” no SQLPLUS:
SQL> recover automatic datafile 37;
Media recovery complete.
SQL> ALTER DATAFILE 37 ONLINE;
Datafile altered.
Alert log
Quando emitido um RECOVER DATAFILE via RMAN:
Tue Jun 08 14:34:52 2021 alter database recover datafile list clear Completed: alter database recover datafile list clear alter database recover if needed datafile 37 Media Recovery Start Serial Media Recovery started ORA-279 signalled during: alter database recover if needed datafile 37 ... alter database recover cancel Media Recovery Canceled Completed: alter database recover cancel
Quando emitido um RECOVER DATAFILE via SQLPLUS:
Tue Jun 08 14:36:09 2021 ALTER DATABASE RECOVER datafile 37 Media Recovery Start Serial Media Recovery started ORA-279 signalled during: ALTER DATABASE RECOVER datafile 37 ... Tue Jun 08 14:36:21 2021 ALTER DATABASE RECOVER automatic datafile 37 Media Recovery Start ORA-275 signalled during: ALTER DATABASE RECOVER automatic datafile 37 ...
Quando emitido um RECOVER AUTOMATIC DATAFILE via SQLPLUS:
Tue Jun 08 14:36:30 2021 ALTER DATABASE RECOVER automatic datafile 37 Media Recovery Start Serial Media Recovery started Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14965.2220.1074616555 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14966.2219.1074617829 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14967.2218.1074618359 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14968.2217.1074619737 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14969.2216.1074620809 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14970.2215.1074621897 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14971.2214.1074621955 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14972.2213.1074623425 Tue Jun 08 14:36:42 2021 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14973.2212.1074624181 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14974.2211.1074625445 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14975.2210.1074625553 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14976.2209.1074625639 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14977.2208.1074625771 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14978.2207.1074626015 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14979.2206.1074626351 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14980.2205.1074627025 Tue Jun 08 14:36:52 2021 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14981.2204.1074627679 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14982.2203.1074628445 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14983.2202.1074629151 Tue Jun 08 14:36:56 2021 Checker run found 2 new persistent data failures Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14984.2201.1074629927 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14985.2200.1074630679 Tue Jun 08 14:37:03 2021 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14986.2199.1074631355 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14987.2198.1074632147 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14988.2197.1074632751 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14989.2196.1074634453 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14990.2195.1074635177 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14991.2194.1074636167 Tue Jun 08 14:37:14 2021 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14992.2193.1074636329 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14993.2192.1074636351 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14994.2191.1074638729 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14995.2190.1074639859 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14996.2189.1074639947 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14997.2188.1074641385 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14998.2187.1074641625 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_07/thread_1_seq_14999.2186.1074642555 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15000.2185.1074643551 Tue Jun 08 14:37:24 2021 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15001.2184.1074646433 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15002.2183.1074647157 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15003.2182.1074648303 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15004.2181.1074648473 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15005.2180.1074648695 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15006.2179.1074649725 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15007.2178.1074650749 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15008.2177.1074652959 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15009.2176.1074654353 Tue Jun 08 14:37:35 2021 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15010.2175.1074656619 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15011.2174.1074657947 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15012.2173.1074660197 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15013.2172.1074661549 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15014.2171.1074663479 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15015.2170.1074665035 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15016.2169.1074665149 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15017.2168.1074665353 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15018.2167.1074667405 Tue Jun 08 14:37:46 2021 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15019.2166.1074668751 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15020.2165.1074670597 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15021.2164.1074672021 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15022.2163.1074672353 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15023.2162.1074673549 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15024.2161.1074674653 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15025.2160.1074675733 Tue Jun 08 14:37:57 2021 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15026.2159.1074675953 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15027.2158.1074677363 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15028.2157.1074677737 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15029.2156.1074678665 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15030.2155.1074679549 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15031.2154.1074679553 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15032.2153.1074680275 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15033.2152.1074681241 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15034.2151.1074681523 Tue Jun 08 14:38:07 2021 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15035.2150.1074681827 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15036.2149.1074681961 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15037.2148.1074682505 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15038.2147.1074683147 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15039.2146.1074683677 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15040.2145.1074684065 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15041.2144.1074684353 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15042.2143.1074685351 Tue Jun 08 14:38:18 2021 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15043.2142.1074686147 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15044.2141.1074686757 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15045.2140.1074687233 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15046.2139.1074687917 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15047.2138.1074688907 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15048.2137.1074689885 Media Recovery Log +RECO/prd_gru12n/archivelog/2021_06_08/thread_1_seq_15049.2136.1074690349 Recovery of Online Redo Log: Thread 1 Group 8 Seq 15050 Reading mem 0 Mem# 0: +RECO/prd_gru12n/onlinelog/group_8.1469.1059559613 Tue Jun 08 14:38:28 2021 Recovery of Online Redo Log: Thread 1 Group 9 Seq 15051 Reading mem 0 Mem# 0: +RECO/prd_gru12n/onlinelog/group_9.623.1059559617 Recovery of Online Redo Log: Thread 1 Group 5 Seq 15052 Reading mem 0 Mem# 0: +RECO/prd_gru12n/onlinelog/group_5.1059.1059559597 Recovery of Online Redo Log: Thread 1 Group 6 Seq 15053 Reading mem 0 Mem# 0: +RECO/prd_gru12n/onlinelog/group_6.519.1059559607 Recovery of Online Redo Log: Thread 1 Group 7 Seq 15054 Reading mem 0 Mem# 0: +RECO/prd_gru12n/onlinelog/group_7.1061.1059559609 Media Recovery Complete (PRD) Completed: ALTER DATABASE RECOVER automatic datafile 37