| RMAN Restore failed [message #656395] |
Wed, 05 October 2016 04:27  |
morad_dba
Messages: 93
Registered: June 2008
|
Member |
|
|
Dear all,
I have RMAN zero level backup on November-13-2015 after that we could not take backup as our backup tape media was not functioning, but we took incremental backup on January-12-2016.
For business requirement, we need to restore data up to December-31-2015. But Restore has been failed with errors
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Oct 5 21:47:10 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
RMAN> run{ set until time "to_date('2015-12-31:22:00:00', 'yyyy-mm-dd:hh24:mi:ss')";
2> restore database; recover database;
3> alter database open resetlogs; }
executing command: SET until clause
Starting restore at 05-OCT-16
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=451 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 6.3.0.0
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=466 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 10/05/2016 01:22:15
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 20 found to restore
I am sharing backup information for you.
Full backup information :
SQL> select HANDLE, to_char(START_TIME,'dd/mm/rr hh24:mi')sdt, tag, BACKUP_TYPE, INCREMENTAL_LEVEL, BYTES/1024/1024/1024 from rc_backup_piece
2 where DB_KEY=204378 and trunc(START_TIME)=to_date('13/11/2015','dd/mm/rrrr') order by START_TIME;
HANDLE SDT TAG B INCREMENTAL_LEVEL BYTES/1024/1024/1024
------------------------------------------ ----------------- ------------------- - ----------------- --------------------
data_PBLMIG_20151113_7aqm6o4a_1_1_14570 13/11/15 23:00 TAG20151113T230026 D 0 141.007324
data_PBLMIG_20151113_7bqm6pqr_1_1_14571 13/11/15 23:29 TAG20151113T230026 D 0 .011230469
c-1614325108-20151113-00 13/11/15 23:30 TAG20151113T233017 D .011230469
arc_PBLMIG_20151113_7dqm6ptu_1_1 13/11/15 23:31 TAG20151113T233110 L .881347656
c-1614325108-20151113-01 13/11/15 23:31 TAG20151113T233157 D .011230469
SQL>
Incremental backup information :
SQL> select HANDLE,bytes/1024/1024 m,to_char(START_TIME,'dd/mm/rr hh24:mi')sdt, tag, BACKUP_TYPE, INCREMENTAL_LEVEL, BYTES/1024/1024/1024 from rc_backup_piece
2 where DB_KEY=204378 and trunc(START_TIME)=to_date('12/01/2016','dd/mm/rrrr') order by START_TIME;
HANDLE M SDT TAG B INCREMENTAL_LEVEL BYTES/1024/1024/1024
------------------------------------------ ---------- ----------------- ------------------- - ----------------- --------------------
data_PBLMIG_20160112_7iqr6j68_1_1_14578 103154.25 12/01/16 13:57 TAG20160112T135726 I 1 100.736572
data_PBLMIG_20160112_7jqr6k5n_1_1_14579 11.5 12/01/16 14:14 TAG20160112T135726 I 1 .011230469
c-1614325108-20160112-00 11.5 12/01/16 14:15 TAG20160112T141503 D .011230469
arc_PBLMIG_20160112_7lqr6k94_1_1 112447.75 12/01/16 14:16 TAG20160112T141558 L 109.812256
arc_PBLMIG_20160112_7mqr6m5f_1_1 112067.75 12/01/16 14:48 TAG20160112T141558 L 109.441162
arc_PBLMIG_20160112_7nqr6p5b_1_1 111978.5 12/01/16 15:39 TAG20160112T141558 L 109.354004
arc_PBLMIG_20160112_7oqr6s3g_1_1 111636.75 12/01/16 16:29 TAG20160112T141558 L 109.020264
arc_PBLMIG_20160112_7pqr6v3p_1_1 111502.5 12/01/16 17:20 TAG20160112T141558 L 108.88916
arc_PBLMIG_20160112_7qqr71et_1_1 110789 12/01/16 18:01 TAG20160112T141558 L 108.192383
arc_PBLMIG_20160112_7rqr73f7_1_1 110745 12/01/16 18:35 TAG20160112T141558 L 108.149414
arc_PBLMIG_20160112_7sqr75kg_1_1 104257.5 12/01/16 19:12 TAG20160112T141558 L 101.813965
c-1614325108-20160112-01 11.5 12/01/16 19:39 TAG20160112T193935 D .011230469
12 rows selected.
SQL>
AS RMAN tell that backup of datafile 20 was not found, I checked that Datafile 20 was added to tablespace on 21-November-2015.
SQL> select FILE#, CREATION_TIME from v$datafile;
FILE# CREATION_
---------- ---------
1 30-JUN-05
2 30-JUN-05
3 30-JUN-05
4 30-JUN-05
5 14-JAN-09
6 14-JAN-09
7 14-JAN-09
8 29-JAN-09
9 17-AUG-09
10 02-JAN-10
11 01-APR-10
12 31-JUL-10
13 31-JUL-10
14 02-JAN-11
15 09-JAN-11
16 07-MAR-12
17 21-JUL-15
18 05-JUN-13
19 21-NOV-13
20 16-NOV-15
We can see that all archive log (near about 800 GB ) have been backed up by RMAN.
We can see backup info of datafile 20
RMAN> list backup of datafile 20 summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
2208652 B 1 A SBT_TAPE 12-JAN-16 1 1 NO TAG20160112T135726
2215058 B 1 A SBT_TAPE 13-JAN-16 1 1 NO TAG20160113T201522
2216820 B 1 A SBT_TAPE 14-JAN-16 1 1 NO TAG20160114T230525
2217890 B 0 A SBT_TAPE 15-JAN-16 1 1 NO TAG20160115T230524
2218814 B 1 A SBT_TAPE 16-JAN-16 1 1 NO TAG20160116T230524
2219805 B 1 A SBT_TAPE 17-JAN-16 1 1 NO TAG20160117T230525
2221583 B 1 A SBT_TAPE 18-JAN-16 1 1 NO TAG20160118T220026
2223005 B 1 A SBT_TAPE 19-JAN-16 1 1 NO TAG20160119T220026
2224154 B 1 A SBT_TAPE 20-JAN-16 1 1 NO TAG20160120T220028
2225306 B 1 A SBT_TAPE 21-JAN-16 1 1 NO TAG20160121T220028
.........
So why RMAN Restoration fails even after all archive log file have been backed up by RMAN and incremental backup was performed.
Please help me in this regard.
Regards,
Morad.
[Updated on: Wed, 05 October 2016 04:50] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: RMAN Restore failed [message #656402 is a reply to message #656399] |
Wed, 05 October 2016 05:13  |
morad_dba
Messages: 93
Registered: June 2008
|
Member |
|
|
We maintain a recovery catalog database. Actually the requirement was to restore every month end data of 2015. As we have RMAN backup, We restored month end data from january to October one after another. We are facing problem as we could not take backup for 47 days. We took zero level backup on 13 nov 2016 and after 47 days we took incremental backup. In the mean time 800 GB archive log had been generated. We did not remove it manually. RMAN took archive log backup and removed it.
The tns are as follows
rmancat =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rmancat)
)
)
PBLMIGTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pblmig)
)
)
|
|
|
|
| Re: RMAN Restore failed [message #656403 is a reply to message #656402] |
Wed, 05 October 2016 05:17 |
John Watson
Messages: 8965
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Connect to the target and the catalog.
Shutdown the target, startup nomount.
Set your until time.
Restore and mount the controlfile.
Restore and recover the database.
Open resetlogs.
And if you want help, show what you are doing and answer questions.
|
|
|
|
| Re: RMAN Restore failed [message #656404 is a reply to message #656403] |
Wed, 05 October 2016 05:41 |
morad_dba
Messages: 93
Registered: June 2008
|
Member |
|
|
Actually We have performed these task as
rman target sys/pblmig@PBLMIGtest catalog rman/rman@rmancat
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Oct 3 17:17:20 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PBLMIG (DBID=1614325108, not open)
connected to recovery catalog database
RMAN> restore controlfile from 'c-1614325108-20160112-01';
RMAN> alter database mount;
RMAN> run{ set until time "to_date('2015-12-31:22:00:00', 'yyyy-mm-dd:hh24:mi:ss')";
2> restore database; recover database;
3> alter database open resetlogs; }
|
|
|
|
| Re: RMAN Restore failed [message #656405 is a reply to message #656404] |
Wed, 05 October 2016 05:47 |
John Watson
Messages: 8965
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
This is hopeless. You might want to consider changing your profession.
I said to set the until time, then restore the controlfie. So what did you do? You restored the controlfile, then you set the until time.
Why did you specify the January backup from which to restore the controlfile? Did I say to do that? No. You have a recovery catalog, you should trust Uncle Oracle to work out what to do. Don't try to be clever when you don't know what you are doing.
|
|
|
|
| Re: RMAN Restore failed [message #656408 is a reply to message #656405] |
Wed, 05 October 2016 06:50 |
morad_dba
Messages: 93
Registered: June 2008
|
Member |
|
|
Dear John,
I have performed the task as per your recommendation, but same result....
[oracle@inhousedb u01]$ export ORACLE_SID=pblmig
[oracle@inhousedb u01]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 5 22:58:59 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 3.1752E+10 bytes
Fixed Size 2528320 bytes
Variable Size 4575988672 bytes
Database Buffers 2.7137E+10 bytes
Redo Buffers 36237312 bytes
SQL> exit
[oracle@inhousedb u01]$ rman target sys/pblmig@PBLMIGtest catalog rman/rman@rmancat
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Oct 5 22:59:53 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PBLMIG (not mounted)
connected to recovery catalog database
RMAN> run{set until time "to_date('2015-12-31:22:00:00', 'yyyy-mm-dd:hh24:mi:ss')"; restore controlfile from 'c-1614325108-20160112-01';
2> alter database mount; }
executing command: SET until clause
Starting restore at 05-OCT-16
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=421 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 6.3.0.0
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=436 device type=DISK
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
channel ORA_SBT_TAPE_1: restoring control file
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:19
output file name=/u01/oracle/oradata/pblmig/control01.ctl
output file name=/u01/oracle/oradata/pblmig/control02.ctl
output file name=/oracle/pblmig/control03.ctl
output file name=/oracle/pblmig/control04.ctl
Finished restore at 05-OCT-16
database mounted
released channel: ORA_SBT_TAPE_1
released channel: ORA_DISK_1
RMAN> run{ set until time "to_date('2015-12-31:22:00:00', 'yyyy-mm-dd:hh24:mi:ss')";
2> restore database; recover database;
3> alter database open resetlogs; }
executing command: SET until clause
Starting restore at 05-OCT-16
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 10/05/2016 23:24:59
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 20 found to restore
|
|
|
|
| Re: RMAN Restore failed [message #656410 is a reply to message #656408] |
Wed, 05 October 2016 06:55 |
John Watson
Messages: 8965
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
It look to me as though you are trolling: being deliberately stupid in order to make people angry.
I shall not reply further until you show that you are trying to follow instructions, rather than trying to annoy.
[Updated on: Wed, 05 October 2016 06:56] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
