Home » RDBMS Server » Backup & Recovery » PITR with RMAN not working (Oracle 19c Windows Server)
| PITR with RMAN not working [message #687905] |
Wed, 19 July 2023 01:32  |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Hi DBAs,
I need to restore the database to a point in time - 14:00 29th of May 2023.
I got:
- 3 RMAN database backup files from that date taken at 11:00 in the morning
- All Archive logs created on the 29th of May 2023
The backups:
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 33.95G DISK 01:13:31 29-MAY-23
BP Key: 1 Status: AVAILABLE Compressed: YES Tag: TAG20230529T110012
Piece Name: F:\RMAN\FULL_DB_MYDB_DAY-20230529_DBID-*DBID...*_UNIQ-CU1TC25S_1_1_414.RMAN
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
5 Full 2462425437 18-MAY-23 NO D:\MY_RESTORE_LOCATION\RST1\DATA01.DBF
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 688.63M DISK 00:01:31 29-MAY-23
BP Key: 2 Status: AVAILABLE Compressed: YES Tag: TAG20230529T110012
Piece Name: F:\RMAN\FULL_DB_MYDB_DAY-20230529_DBID-*DBID...*_UNIQ-CV1TC25S_1_1_415.RMAN
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 2462425437 18-MAY-23 2557415533 NO D:\MY_RESTORE_LOCATION\RST1\SYSTEM01.DBF
2 Full 2462425437 18-MAY-23 2557408508 NO D:\MY_RESTORE_LOCATION\RST1\SYSAUX01.DBF
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 1.34G DISK 00:02:32 29-MAY-23
BP Key: 3 Status: AVAILABLE Compressed: YES Tag: TAG20230529T110012
Piece Name: F:\RMAN\FULL_DB_MYDB_DAY-20230529_DBID-*DBID...*_UNIQ-D01TC25S_1_1_416.RMAN
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
3 Full 2462425437 18-MAY-23 2557415533 NO D:\MY_RESTORE_LOCATION\RST1\UNDOTBS01.DBF
4 Full 2462425437 18-MAY-23 NO D:\MY_RESTORE_LOCATION\RST1\USERS01.DBF
RMAN>
Log information from the backup on the 29th ( taken on Standby database ):
Recovery Manager: Release 19.0.0.0.0 - Production on Mon May 29 11:00:04 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: MYDB (DBID=*DBID...*, not open)
RMAN>
RMAN>
RMAN> # BACKUP DATABASE
2> # BACKUP ARCHIVELOG
3> # BACKUP SPFILE
4> # BACKUP CONTROLFILE
5> #
6>
7> BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT 'F:\RMAN\FULL_DB_%d_day-%T_dbid-%I_uniq-%U_%s.rman';
8> BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG ALL not backed up 1 times FORMAT 'F:\RMAN\Archivelogs_%d_day-%T_dbid-%I_uniq-%U_%s.rman';
9> BACKUP CURRENT CONTROLFILE FORMAT 'F:\RMAN\CURRENT_CONTROLFILE_%d_day-%T_dbid-%I_uniq-%U_%s.rman';
10> BACKUP SPFILE FORMAT 'F:\RMAN\SPFILE_%d_day-%T_dbid-%I_uniq-%U_%s.rman' ;
11>
12> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS 'F:\RMAN\\controlfile.txt';
13>
14> CROSSCHECK BACKUP;
15> CROSSCHECK COPY;
16>
17> DELETE NOPROMPT EXPIRED BACKUP;
18> DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
19>
20>
21> DELETE NOPROMPT OBSOLETE;
22>
23>
24>
25>
26> EXIT
Starting backup at 29-MAY-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=497 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=744 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=866 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=E:\ORACLE\ORADATA\MYDB\DATA01.DBF
channel ORA_DISK_1: starting piece 1 at 29-MAY-23
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00002 name=E:\ORACLE\ORADATA\MYDB\SYSAUX01.DBF
input datafile file number=00001 name=E:\ORACLE\ORADATA\MYDB\SYSTEM01.DBF
channel ORA_DISK_2: starting piece 1 at 29-MAY-23
channel ORA_DISK_3: starting compressed full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00003 name=E:\ORACLE\ORADATA\MYDB\UNDOTBS01.DBF
input datafile file number=00004 name=E:\ORACLE\ORADATA\MYDB\USERS01.DBF
channel ORA_DISK_3: starting piece 1 at 29-MAY-23
channel ORA_DISK_2: finished piece 1 at 29-MAY-23
piece handle=F:\RMAN\FULL_DB_MYDB_DAY-20230529_DBID-*DBID...*_UNIQ-CV1TC25S_1_1_415.RMAN tag=TAG20230529T110012 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:01:36
channel ORA_DISK_3: finished piece 1 at 29-MAY-23
piece handle=F:\RMAN\FULL_DB_MYDB_DAY-20230529_DBID-*DBID...*_UNIQ-D01TC25S_1_1_416.RMAN tag=TAG20230529T110012 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:02:36
channel ORA_DISK_1: finished piece 1 at 29-MAY-23
piece handle=F:\RMAN\FULL_DB_MYDB_DAY-20230529_DBID-*DBID...*_UNIQ-CU1TC25S_1_1_414.RMAN tag=TAG20230529T110012 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 01:13:37
Finished backup at 29-MAY-23
Starting Control File and SPFILE Autobackup at 29-MAY-23
piece handle=E:\ORACLE\FAST_RECOVERY_AREA\MYDBSB\AUTOBACKUP\2023_05_29\O1_MF_S_.........._.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 29-MAY-23
Starting backup at 29-MAY-23
RMAN-06820: warning: failed to archive current log at primary database
cannot connect to remote database
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 05/29/2023 12:14:00
RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
ORA-19625: error identifying file E:\ORACLE\FAST_RECOVERY_AREA\MYDBSB\ARCHIVELOG\2023_05_21\O1_MF_1_XXXXX_XXXXXXX_.ARC
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.
Recovery Manager complete.
So I created a new instance with ORADIM:
C:\Windows\system32>oradim -NEW -SID RST1
Instance created.
Then created the controlfile from the backup to trace command specifying only the data file locations, adjusted paths
Opened RMAN and did RESTORE DATABASE
Then tried to recover and open with resetlogs, got the error:
SQL> alter database recover database until cancel using backup controlfile;
alter database recover database until cancel using backup controlfile
*
ERROR at line 1:
ORA-00279: change 2462425437 generated at 05/18/2023 13:43:39 needed for thread 1
ORA-00289: suggestion : :\...\19.3\DB_HOME\RDBMS\ARC0000015763_1107173529.0001
ORA-00280: change 2462425437 for thread 1 is in sequence #15763
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-10877: error signaled in parallel recovery slave
ORA-10877: error signaled in parallel recovery slave
ORA-10877: error signaled in parallel recovery slave
ORA-10877: error signaled in parallel recovery slave
ORA-10877: error signaled in parallel recovery slave
ORA-10877: error signaled in parallel recovery slave
ORA-10877: error signaled in parallel recovery slave
ORA-10877: error signaled in parallel recovery slave
ORA-10877: error signaled in parallel recovery slave
ORA-10877: error signaled in parallel recovery slave
ORA-01153: an incompatible media recovery is active
I tried to do the incomplete recovery in a block, same result:
connected to target database: MYDB (DBID=*****, not open)
RMAN>
RMAN>
RMAN> alter session set NLS_DATE_FORMAT='YYYY-MM-dd HH24:MI:SS';
using target database control file instead of recovery catalog
Statement processed
RMAN>
RMAN> run {
2> set until time '2023-05-29 14:00:00'
3> restore database;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "restore": expecting one of: "newline, ;"
RMAN-01007: at line 4 column 4 file: standard input
RMAN> recover database;
Starting recover at 19-JUL-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=648 device type=DISK
starting media recovery
archived log for thread 1 with sequence 16790 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16790_L77JGT14_.ARC
archived log for thread 1 with sequence 16791 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16791_L77KBY33_.ARC
archived log for thread 1 with sequence 16792 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16792_L77L7264_.ARC
archived log for thread 1 with sequence 16793 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16793_L77M36FG_.ARC
archived log for thread 1 with sequence 16794 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16794_L77MZBRL_.ARC
archived log for thread 1 with sequence 16795 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16795_L77NVH26_.ARC
archived log for thread 1 with sequence 16796 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16796_L77OQM3H_.ARC
archived log for thread 1 with sequence 16797 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16797_L77PMQBS_.ARC
archived log for thread 1 with sequence 16798 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16798_L77QHVGV_.ARC
archived log for thread 1 with sequence 16799 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16799_L77RCZPZ_.ARC
archived log for thread 1 with sequence 16800 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16800_L77S683L_.ARC
archived log for thread 1 with sequence 16801 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16801_L77T29K6_.ARC
archived log for thread 1 with sequence 16802 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16802_L77TYFLQ_.ARC
archived log for thread 1 with sequence 16803 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16803_L77VTKQH_.ARC
archived log for thread 1 with sequence 16804 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16804_L77WPOS3_.ARC
archived log for thread 1 with sequence 16805 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16805_L77XLSTN_.ARC
archived log for thread 1 with sequence 16806 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16806_L77YGY2S_.ARC
archived log for thread 1 with sequence 16807 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16807_L77ZC29V_.ARC
archived log for thread 1 with sequence 16808 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16808_L78076FD_.ARC
archived log for thread 1 with sequence 16809 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16809_L7813BGK_.ARC
archived log for thread 1 with sequence 16810 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16810_L781ZGMM_.ARC
archived log for thread 1 with sequence 16811 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16811_L782VLTW_.ARC
archived log for thread 1 with sequence 16812 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16812_L783QPYG_.ARC
archived log for thread 1 with sequence 16813 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16813_L784MV1L_.ARC
archived log for thread 1 with sequence 16814 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16814_L785HZ76_.ARC
archived log for thread 1 with sequence 16815 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16815_L786D3R7_.ARC
archived log for thread 1 with sequence 16816 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16816_L78787TM_.ARC
archived log for thread 1 with sequence 16817 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16817_L78848YC_.ARC
archived log for thread 1 with sequence 16818 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16818_L7890F3Q_.ARC
archived log for thread 1 with sequence 16819 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16819_L789WK7C_.ARC
archived log for thread 1 with sequence 16820 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16820_L78BROD1_.ARC
archived log for thread 1 with sequence 16821 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16821_L78CNSH3_.ARC
archived log for thread 1 with sequence 16822 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16822_L78DJXQK_.ARC
archived log for thread 1 with sequence 16823 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16823_L78FF240_.ARC
archived log for thread 1 with sequence 16824 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16824_L78G968V_.ARC
archived log for thread 1 with sequence 16825 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16825_L78H5BL9_.ARC
archived log for thread 1 with sequence 16826 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16826_L78J1GMV_.ARC
archived log for thread 1 with sequence 16827 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16827_L78JXLTG_.ARC
archived log for thread 1 with sequence 16828 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16828_L78KSQ24_.ARC
archived log for thread 1 with sequence 16829 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16829_L78LOVC2_.ARC
archived log for thread 1 with sequence 16830 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16830_L78MKZFV_.ARC
archived log for thread 1 with sequence 16831 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16831_L78NG3MR_.ARC
archived log for thread 1 with sequence 16832 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16832_L78OB7VN_.ARC
archived log for thread 1 with sequence 16833 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16833_L78P694K_.ARC
archived log for thread 1 with sequence 16834 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16834_L78Q2F92_.ARC
archived log for thread 1 with sequence 16835 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16835_L78QYKLC_.ARC
archived log for thread 1 with sequence 16836 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16836_L78RTOV4_.ARC
archived log for thread 1 with sequence 16837 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16837_L78SPSY6_.ARC
archived log for thread 1 with sequence 16838 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16838_L78TLY5C_.ARC
archived log for thread 1 with sequence 16839 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16839_L78VH2CX_.ARC
archived log for thread 1 with sequence 16840 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16840_L78WC6J1_.ARC
archived log for thread 1 with sequence 16841 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16841_L78X7BNX_.ARC
archived log for thread 1 with sequence 16842 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16842_L78Y3GTT_.ARC
archived log for thread 1 with sequence 16843 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16843_L78YZLVD_.ARC
archived log for thread 1 with sequence 16844 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16844_L78ZVPYZ_.ARC
archived log for thread 1 with sequence 16845 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16845_L790QV36_.ARC
archived log for thread 1 with sequence 16846 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16846_L791MZ8O_.ARC
archived log for thread 1 with sequence 16847 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16847_L792J3CN_.ARC
archived log for thread 1 with sequence 16848 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16848_L793D7FC_.ARC
archived log for thread 1 with sequence 16849 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16849_L7948CHH_.ARC
archived log for thread 1 with sequence 16850 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16850_L7954HJ1_.ARC
archived log for thread 1 with sequence 16851 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16851_L7960MMX_.ARC
archived log for thread 1 with sequence 16852 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16852_L796WO90_.ARC
archived log for thread 1 with sequence 16853 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16853_L797RSDR_.ARC
archived log for thread 1 with sequence 16854 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16854_L798NXJH_.ARC
archived log for thread 1 with sequence 16855 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16855_L799K1M8_.ARC
archived log for thread 1 with sequence 16856 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16856_L79BF5O5_.ARC
archived log for thread 1 with sequence 16857 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16857_L79C99S2_.ARC
archived log for thread 1 with sequence 16858 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16858_L79D5FXP_.ARC
archived log for thread 1 with sequence 16859 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16859_L79F1L6B_.ARC
archived log for thread 1 with sequence 16860 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16860_L79FXPDL_.ARC
archived log for thread 1 with sequence 16861 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16861_L79GSTNG_.ARC
archived log for thread 1 with sequence 16862 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16862_L79HOYSH_.ARC
archived log for thread 1 with sequence 16863 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16863_L79JL2WS_.ARC
archived log for thread 1 with sequence 16864 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16864_L79KG713_.ARC
archived log for thread 1 with sequence 16865 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16865_L79LBC7Y_.ARC
archived log for thread 1 with sequence 16866 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16866_L79M6HJW_.ARC
archived log for thread 1 with sequence 16867 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16867_L79N2MON_.ARC
archived log for thread 1 with sequence 16868 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16868_L79NYQSC_.ARC
archived log for thread 1 with sequence 16869 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16869_L79OTW10_.ARC
archived log for thread 1 with sequence 16870 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16870_L79PPX6R_.ARC
archived log for thread 1 with sequence 16871 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16871_L79QM1F1_.ARC
archived log for thread 1 with sequence 16872 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16872_L79RH5JJ_.ARC
archived log for thread 1 with sequence 16873 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16873_L79SC9OY_.ARC
archived log for thread 1 with sequence 16874 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16874_L79T7FWJ_.ARC
archived log for thread 1 with sequence 16875 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16875_L79V3L1V_.ARC
archived log for thread 1 with sequence 16876 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16876_L79VZP93_.ARC
archived log for thread 1 with sequence 16877 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16877_L79WVTKW_.ARC
archived log for thread 1 with sequence 16878 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16878_L79XLJOK_.ARC
archived log for thread 1 with sequence 16879 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16879_L79XR1QN_.ARC
archived log for thread 1 with sequence 16880 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16880_L79Y22RH_.ARC
archived log for thread 1 with sequence 16881 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16881_L79YY6YD_.ARC
archived log for thread 1 with sequence 16882 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16882_L79ZTC2O_.ARC
archived log for thread 1 with sequence 16883 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16883_L7B0PHDS_.ARC
archived log for thread 1 with sequence 16884 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16884_L7B1LMKQ_.ARC
archived log for thread 1 with sequence 16885 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16885_L7B2GQOF_.ARC
archived log for thread 1 with sequence 16886 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16886_L7B3BS2N_.ARC
archived log for thread 1 with sequence 16887 is already on disk as file *ARCHIVE_LOCATION*\2023_05_29\O1_MF_1_16887_L7B46X97_.ARC
unable to find archived log
archived log thread=1 sequence=15763
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/19/2023 09:20:50
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 15763 and starting SCN of 2462425437
RMAN> alter database open resetlogs;}
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 07/19/2023 09:20:51
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '...MYDB\SYSTEM01.DBF'
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "}": expecting one of: "advise, allocate, alter, analyze, associate statistics, audit, backup, begin, @, call, catalog, change, comment, commit, configure, connect, convert, copy, create, create catalog, create global, create or replace global, create or replace script, create script, crosscheck, declare, delete, delete from, describe, describe catalog, disassociate statistics, drop, drop catalog, drop database, duplicate, exit, explain plan, flashback, flashback table, grant, grant catalog, grant register, host, import, insert, list, lock, merge, mount, noaudit, open, print, purge, quit, recover, register, release"
RMAN-01007: at line 0 column 34 file: standard input
RMAN>
Is it something I am doing wrong in the recovery process, or is my backup + archive log files really impaired ?
If it's the second option, would be great to understand, what is wrong with it?
Coming from a perspective that I have data files backed up and archives taken before and after the backup..
Many thanks in advance
Andrey
|
|
|
|
| Re: PITR with RMAN not working [message #687906 is a reply to message #687905] |
Wed, 19 July 2023 03:03  |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
What document are you following? Your method is not what I would do. What is the reason for creating a controlfile from trace? THat will make it so much harder. Also, why are you using SQL*Plus to do the recovery? RMAN will do it better.
THe approach I would take is:
Start the instance off a dummy parameter file (you are OK there, though you could ask RMAN to do that for you).
Tell RMAN to restore the spfile from the autobackup, and restart off that.
Restore the controlfile from the autobackup.
Catalog all the backups and archivelog files into the controlfile and crosscheck
Set your UNTIL TIME (which you didn't do - didn't you see the error?)
Restore the database
Recover the database
If you aren't sure of the commands, at each stage you can use the Recovery Advisor: the LIST FAILURE and ADVISE FAILURE commands. Trust RMAN! Uncle Oracle is cleverer than we are.
|
|
|
|
| Re: PITR with RMAN not working [message #687907 is a reply to message #687906] |
Wed, 19 July 2023 09:28 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
John Watson wrote on Wed, 19 July 2023 11:03What document are you following? Your method is not what I would do. What is the reason for creating a controlfile from trace? THat will make it so much harder. Also, why are you using SQL*Plus to do the recovery? RMAN will do it better.
First of all, many thanks for the reply.
To be honest, I failed to find an official documentation that seemed as if was addressing my exact testcase specifics,
and I am working on a server where I already have a Standby instance ( from which the backup was taken ), and I don't have things like SPFILE/CTL backup from the date of the backup ( it got removed).
On top of that, as I showed from the log files above - my archive logs did not back up,so I had to find the archivelogs from that date and try to use them for the recovery.
Eventually, I tried to utilize a set of techniques from different sources on the WWW, addressing each step separately and trying to document what seemed to work and get me one step ahead.
Quote:
THe approach I would take is:
Start the instance off a dummy parameter file (you are OK there, though you could ask RMAN to do that for you).
Tell RMAN to restore the spfile from the autobackup, and restart off that.
Restore the controlfile from the autobackup.
Question about the restore of SPFILE:
Isn't it going to include the original db_name ( which I don't want to mix with my restored db name, so I try to do it with a separate namespace .. )
Which will require me to create a pfile out of it and change it, plus have to worry about the other DG and specific settings, folders there to not by mistake conflict with such of the running instance ?
Question about the Controlfile: would it make sense to restore it from a new backup ( 1.5 months later ) of a control file from the same database?
And isn't it dangerous as well, in terms of conflicting with the locations of the data/log files of the running database - instance ?
Regards,
Andrey
[Updated on: Wed, 19 July 2023 09:31] Report message to a moderator |
|
|
|
| Re: PITR with RMAN not working [message #687908 is a reply to message #687906] |
Wed, 19 July 2023 11:38 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
John Watson wrote on Wed, 19 July 2023 11:03
...
..
Restore the controlfile from the autobackup.
Catalog all the backups and archivelog files into the controlfile and crosscheck
If you aren't sure of the commands, at each stage you can use the Recovery Advisor: the LIST FAILURE and ADVISE FAILURE commands. Trust RMAN! Uncle Oracle is cleverer than we are.
I tried to test it on an environment where I have MYDB and I want to restore it onto RST which is the duplicate.
I couldn't mount the database due to "ORA-01102: cannot mount database in EXCLUSIVE mode"
If I tried to change my DB_NAME & DB_UNIQUE_NAME and then bounce the database and try to mount, I then got "ORA-01103: database name 'MYDB' in control file is not 'RST'"
I tried also to set NEWNAME for the files but seems like I got the errors upon mounting the database regardless
run
{
SET NEWNAME FOR DATABASE TO 'oradata\f%';
SET NEWNAME FOR DATAFILE 1 TO 'E:\oracle\oradata\RST\SYSTEM01.DBF';
SET NEWNAME FOR DATAFILE 2 TO 'E:\oracle\oradata\RST\SYSAUX01.DBF';
SET NEWNAME FOR DATAFILE 3 TO 'E:\oracle\oradata\RST\UNDOTBS01.DBF';
SET NEWNAME FOR DATAFILE 4 TO 'E:\oracle\oradata\RST\USERS01.DBF';
alter database mount;}
|
|
|
|
| Re: PITR with RMAN not working [message #687910 is a reply to message #687908] |
Thu, 20 July 2023 01:05 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
You can't have two databases (or instances) with the the same name running off the same Oracle Home. You can't create a controlfile with one name and restore a backup from a different name. If you are working on the same machine, you have to duplicate the database, not restore it. Look up how to
DUPLICATE TARGET DATABASE .... BACKUP LCATION .... ;
|
|
|
|
Goto Forum:
Current Time: Sat Apr 27 03:16:38 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
