Hi Gurus,
I am stuck in a situation and need your help.
We need to check if our backups are valid or not. So I was given a new server and backups/archives of a 2 TB database which i need to restore/recover. I followed below steps.
1) Copied the parameter file from prod to test.
2) Made necessary changes in the init file.
db_file_name_convert, etc.
3) start db in nomount mode.
4) Restore control file from backup
restore controlfile from '/oraback/ARCH/prod/backup_controlfiles/c-250795000-20141030-01';
5) Mount the database.
6) Created below rman script and executed.
run{
catalog start with '/oraback/RMAN/test' noprompt;
catalog start with '/oraback/ARCH/test' noprompt;
allocate channel C1 device type disk;
allocate channel C2 device type disk;
allocate channel C3 device type disk;
allocate channel C4 device type disk;
allocate channel C5 device type disk;
allocate channel C6 device type disk;
allocate channel C7 device type disk;
allocate channel C8 device type disk;
@rename_datafiles.lst
@rename_logfiles.lst
set until scn 144390584718;
restore database;
switch datafile all;
recover database
}
Issues -
-- Restore completed successfully but i found that few of the archives are missing.
-- I fired recover database until cancel using backup controlfile; it gave below error
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u02/oradata/testsystem01.dbf'
-- Then tried to restore archives from the archive backup available and few of the of the archives were restored.
i.e. sequence between 759529 and 759552.
Then again i fired recover database until cancel using backup controlfile and i got below errors.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 144185396330 generated at 10/26/2014 08:14:01 needed for
thread 1
ORA-00289: suggestion : /oraback/ARCH/arch_1_759541_535023608.arc
ORA-00280: change 144185396330 for thread 1 is in sequence #759541
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 144187549215 generated at 10/26/2014 08:22:05 needed for
thread 1
ORA-00289: suggestion : /oraback/ARCH/arch_1_759542_535023608.arc
ORA-00280: change 144187549215 for thread 1 is in sequence #759542
ORA-00278: log file '/oraback/ARCH/arch_1_759541_535023608.arc' no longer
needed for this recovery
ORA-00279: change 144187559176 generated at 10/26/2014 08:28:41 needed for
thread 1
ORA-00289: suggestion : /oraback/ARCH/arch_1_759543_535023608.arc
ORA-00280: change 144187559176 for thread 1 is in sequence #759543
ORA-00278: log file '/oraback/ARCH/arch_1_759542_535023608.arc' no longer
needed for this recovery
ORA-00279: change 144188388865 generated at 10/26/2014 08:36:51 needed for
thread 1
ORA-00289: suggestion : /oraback/ARCH/arch_1_759544_535023608.arc
ORA-00280: change 144188388865 for thread 1 is in sequence #759544
ORA-00278: log file '/oraback/ARCH/arch_1_759543_535023608.arc' no longer
needed for this recovery
ORA-00279: change 144188399424 generated at 10/26/2014 08:46:40 needed for
thread 1
ORA-00289: suggestion : /oraback/ARCH/arch_1_759545_535023608.arc
ORA-00280: change 144188399424 for thread 1 is in sequence #759545
ORA-00278: log file '/oraback/ARCH/arch_1_759544_535023608.arc' no longer
needed for this recovery
ORA-00279: change 144189462436 generated at 10/26/2014 08:54:04 needed for
thread 1
ORA-00289: suggestion : /oraback/ARCH/arch_1_759546_535023608.arc
ORA-00280: change 144189462436 for thread 1 is in sequence #759546
ORA-00278: log file '/oraback/ARCH/arch_1_759545_535023608.arc' no longer
needed for this recovery
ORA-00279: change 144189469985 generated at 10/26/2014 08:58:13 needed for
thread 1
ORA-00289: suggestion : /oraback/ARCH/arch_1_759547_535023608.arc
ORA-00280: change 144189469985 for thread 1 is in sequence #759547
ORA-00278: log file '/oraback/ARCH/arch_1_759546_535023608.arc' no longer
needed for this recovery
ORA-00279: change 144189577217 generated at 10/26/2014 09:01:38 needed for
thread 1
ORA-00289: suggestion : /oraback/ARCH/arch_1_759548_535023608.arc
ORA-00280: change 144189577217 for thread 1 is in sequence #759548
ORA-00278: log file '/oraback/ARCH/arch_1_759547_535023608.arc' no longer
needed for this recovery
ORA-00279: change 144189584836 generated at 10/26/2014 09:05:26 needed for
thread 1
ORA-00289: suggestion : /oraback/ARCH/arch_1_759549_535023608.arc
ORA-00280: change 144189584836 for thread 1 is in sequence #759549
ORA-00278: log file '/oraback/ARCH/arch_1_759548_535023608.arc' no longer
needed for this recovery
ORA-00279: change 144189699671 generated at 10/26/2014 09:09:23 needed for
thread 1
ORA-00289: suggestion : /oraback/ARCH/arch_1_759550_535023608.arc
ORA-00280: change 144189699671 for thread 1 is in sequence #759550
ORA-00278: log file '/oraback/ARCH/arch_1_759549_535023608.arc' no longer
needed for this recovery
ORA-00279: change 144189708674 generated at 10/26/2014 09:14:23 needed for
thread 1
ORA-00289: suggestion : /oraback/ARCH/arch_1_759551_535023608.arc
ORA-00280: change 144189708674 for thread 1 is in sequence #759551
ORA-00278: log file '/oraback/ARCH/arch_1_759550_535023608.arc' no longer
needed for this recovery
ORA-00279: change 144189715134 generated at 10/26/2014 09:21:51 needed for
thread 1
ORA-00289: suggestion : /oraback/ARCH/arch_1_759552_535023608.arc
ORA-00280: change 144189715134 for thread 1 is in sequence #759552
ORA-00278: log file '/oraback/ARCH/arch_1_759551_535023608.arc' no longer
needed for this recovery
ORA-00279: change 144189715148 generated at 10/26/2014 09:21:52 needed for
thread 1
ORA-00289: suggestion : /oraback/ARCH/arch_1_759553_535023608.arc
ORA-00280: change 144189715148 for thread 1 is in sequence #759553
ORA-00278: log file '/oraback/ARCH/arch_1_759552_535023608.arc' no longer
needed for this recovery
ORA-00308: cannot open archived log '/oraback/ARCH/arch_1_759553_535023608.arc'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
ORA-10879: error signaled in parallel recovery slave
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 6 needs more recovery to be consistent
ORA-01110: data file 6: '/u01/oradata/testchgtwy_data01.dbf'
-- Now when I tried to open the database with resetlogs it gives below error.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 6 needs more recovery to be consistent
ORA-01110: data file 6: '/u01/oradata/testchgtwy_data01.dbf'
-- I found that archives backup from sequence 759553 to 759599 are missing. its not present in Primary also.
-- I tried to check fuzziness of datafiles and got below results.
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
set feedback on
set heading on
set pagesize 1000
set linesize 175
column checkpoint_change# format 999999999999999999999
select status, checkpoint_change#, fuzzy,
to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
count(*)
from v$datafile_header
group by status, checkpoint_change#, fuzzy, checkpoint_time
order by status, checkpoint_change#, fuzzy, checkpoint_time;
STATUS CHECKPOINT_CHANGE# FUZZY CHECKPOINT_TIME COUNT(*)
--------------------- ---------------------- --------- -------------------- ----------
ONLINE 144189715148 NO 26-OCT-2014 09:21:52 123
ONLINE 144375495577 YES 29-OCT-2014 20:30:23 1
ONLINE 144375495581 YES 29-OCT-2014 20:30:23 1
ONLINE 144375891732 YES 29-OCT-2014 20:52:00 1
ONLINE 144375891955 YES 29-OCT-2014 20:52:03 1
ONLINE 144376850545 NO 29-OCT-2014 21:13:10 1
ONLINE 144376866610 YES 29-OCT-2014 21:13:36 1
ONLINE 144377230506 YES 29-OCT-2014 21:31:42 1
ONLINE 144377313738 YES 29-OCT-2014 21:35:47 1
ONLINE 144377571340 NO 29-OCT-2014 21:53:33 1
ONLINE 144377625895 YES 29-OCT-2014 21:57:39 1
ONLINE 144378445278 YES 29-OCT-2014 22:13:37 1
ONLINE 144378578727 YES 29-OCT-2014 22:20:43 1
ONLINE 144378877241 YES 29-OCT-2014 22:35:59 1
ONLINE 144378912130 YES 29-OCT-2014 22:42:25 1
ONLINE 144379435791 YES 29-OCT-2014 22:57:31 1
ONLINE 144379545905 YES 29-OCT-2014 23:03:17 1
ONLINE 144379631677 YES 29-OCT-2014 23:18:53 1
ONLINE 144379637933 YES 29-OCT-2014 23:24:18 1
ONLINE 144380039550 YES 29-OCT-2014 23:37:44 1
ONLINE 144380054601 YES 29-OCT-2014 23:41:40 1
ONLINE 144380099883 YES 29-OCT-2014 23:54:16 1
ONLINE 144380114063 YES 29-OCT-2014 23:59:41 1
ONLINE 144380676881 YES 30-OCT-2014 00:13:27 1
ONLINE 144380869308 YES 30-OCT-2014 00:20:33 1
ONLINE 144381101372 YES 30-OCT-2014 00:32:49 1
ONLINE 144381124605 YES 30-OCT-2014 00:40:54 1
ONLINE 144381393417 YES 30-OCT-2014 00:51:20 1
ONLINE 144382166578 YES 30-OCT-2014 01:00:46 1
ONLINE 144382500313 YES 30-OCT-2014 01:10:11 1
ONLINE 144382666697 YES 30-OCT-2014 01:18:47 1
ONLINE 144382861398 YES 30-OCT-2014 01:30:02 1
ONLINE 144383132266 YES 30-OCT-2014 01:37:38 1
ONLINE 144383283837 YES 30-OCT-2014 01:48:24 1
ONLINE 144383580087 YES 30-OCT-2014 01:55:49 1
ONLINE 144384682269 YES 30-OCT-2014 02:06:55 1
ONLINE 144384792040 YES 30-OCT-2014 02:12:50 1
ONLINE 144385726601 NO 30-OCT-2014 02:24:16 1
ONLINE 144385779382 NO 30-OCT-2014 02:29:51 1
ONLINE 144385956865 YES 30-OCT-2014 02:41:27 1
ONLINE 144386015770 YES 30-OCT-2014 02:46:22 1
ONLINE 144386151651 YES 30-OCT-2014 02:59:08 1
ONLINE 144386317681 YES 30-OCT-2014 03:04:13 1
ONLINE 144386563076 YES 30-OCT-2014 03:16:59 1
ONLINE 144386565235 YES 30-OCT-2014 03:18:44 1
ONLINE 144386699656 YES 30-OCT-2014 03:31:40 1
ONLINE 144386801438 NO 30-OCT-2014 03:36:15 1
ONLINE 144387128892 NO 30-OCT-2014 03:52:31 1
ONLINE 144387143873 YES 30-OCT-2014 03:53:56 1
ONLINE 144387490033 NO 30-OCT-2014 04:09:52 1
ONLINE 144387501235 YES 30-OCT-2014 04:11:27 1
ONLINE 144388180516 NO 30-OCT-2014 04:26:44 1
ONLINE 144388433270 YES 30-OCT-2014 04:31:19 1
ONLINE 144388902410 YES 30-OCT-2014 04:42:55 1
ONLINE 144388917251 YES 30-OCT-2014 04:53:11 1
ONLINE 144389534812 YES 30-OCT-2014 05:01:56 1
ONLINE 144389556024 YES 30-OCT-2014 05:08:42 1
ONLINE 144389674922 YES 30-OCT-2014 05:19:48 1
ONLINE 144389831920 YES 30-OCT-2014 05:26:13 1
ONLINE 144389957543 NO 30-OCT-2014 05:42:59 1
ONLINE 144389982414 YES 30-OCT-2014 05:46:24 1
ONLINE 144390137798 NO 30-OCT-2014 05:56:40 1
ONLINE 144390460229 YES 30-OCT-2014 06:12:36 1
ONLINE 144390474106 YES 30-OCT-2014 06:13:11 1
ONLINE 144390582132 NO 30-OCT-2014 06:27:27 1
ONLINE 144390582634 NO 30-OCT-2014 06:28:02 1
1) How to proceed from here.
2) Any changes i need to make to the RMAN script. As i need to automate the restore process.
3) I don't want to use below hidden parameters
_ALLOW_RESETLOGS_CORRUPTION = TRUE
_CORRUPTED_ROLLBACK_SEGMENTS =(comma separated list of Automatic Undo segments)
Thanks in advance for the support.